エクセル

Excel で得点幅ごとの人数を数える。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

M.K さん
エクセルで、得点の幅ごとに何人いるかを数えるやり方です。
「30点以上40点以下は何名で、41点以上50点以下は何名いて……」とか――いわゆる度数分布というものですね。

COUNTIFS関数を使えばいいかと思います。
(COUNTIFS関数が使えるのはエクセル2007以上)

オートフィルタを使ってもできますが、その場合は「指定の範囲内」を必要な回数だけ実行することになります。ちょっと面倒ですよね。

Shiteinohaninai_2

なもので、COUNTIFS関数を利用することにしました。

なお、Googleなんかで「エクセル 点数 人数」とか探すと、このような場合はFREQUENCY関数を使う説明も見つかります。
FREQUENCY関数でもかまいません。最終的に得られる結果は同じですから。

なぜ、FREQUENCY関数を今回使わないでCOUNTIFS関数を使ったかというと、「FREQUENCY関数のほうが理解しにくく、少し変えたいといった場合に面倒になるだろう」と考えたからです。

詳細を知りたければFREQUENCY関数を検索してみてください――。使い方をすんなり理解できますか。なんだかややこしいと感じませんか。

そんなややこしいと感じるものを仕事では使わないほうがいいと僕は思います。仕事で作るファイルは作った人のものではなく、皆のものです。
たとえば、「エクセルってほとんど初めてです」とかいう人に仕事を引き継ぐことだってあるかもしれません。
たとえ、 M.K さんがFREQUENCY関数を自在に使えるとしても、引き継いだ後の人が困ります。

でも、COUNTIFS関数なら、同じ初めてでも、FREQUENCY関数よりは理解しやすいと思います。
だとしたら、計算結果は同じなのだから、より理解しやすいであろうCOUNTIFS関数を使っておくのが、仕事としては正しいのではないかと思ったしだい。

仕事でのファイル作成では、「自分以外の人がファイルを使う場合もある」ことを考慮してください。
見やすい式を作ったり、理解の助けになるコメントを残したり、やり方はいろいろあるかと思います。

FREQUENCY関数を使ってはいけないといっているのではありません。
もし M.K さんも含めて周囲にいる人たちが「FREQUENCY関数を使っても何も困らない」という人ばかりなら、使うことに何の問題もないと思います。

■例:COUNTIFS関数を使って、得点ごとの人数を数える。

COUNTIFS関数の書式は次のようです。詳細はエクセルのヘルプでCOUNTIFS関数を調べてください。
・COUNTIFS(条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2]…)
出典: Excel ヘルプ

「条件範囲」には、人数を数えたい点数の一群を指定します。「検索条件」には >=41 (41以上)などの条件を指定します。

こういう表があるとします。

Countifs0_2
※氏名は「なんちゃって個人情報」のサイトを使って生成

C、D、E列に入っている得点を対象として人数を数えます。
(C、D、E列がそれぞれ「条件範囲」になります。)

得点の幅は、下図のようにG列に入れました。
(実際には計算のなかではG列の値は使っていません。だからなくてもいいのですが、あったほうが見やすいという気がしたので入れました。)

適当なセルに「以上」と「以下」を示す式を入力します。
下図では、H列に「以上」、I列に「以下」を示す式を入れています。
(H列とI列の値が「検索条件」になります。)

「45以下」については、「Aの人数」「Bの人数」「Cの人数」ともにCOUNTIFS関数の検索条件としてI2セルを指定します。
J2セルに、
=COUNTIFS(C$2:C$21,$I$2)
という式を入れて、L2セルまでコピーします。

Countifs1

C2からC21の範囲を対象にして、I2に入れた条件に合致する値の個数を数えます。
I2の条件は  <=45 なので、C2からC21の範囲で「45以下」の値を数えます。

J3セルには、
=COUNTIFS(C$2:C$21,$H3,C$2:C$21,$I3)
という式を入れます。

H3には >=46 が、I3には <=50 が入っているので、C2からC21の範囲で「46以上」かつ「50以下」の値を数えます。

それをJ12セルまでコピーします。
そのあとJ3からJ12を選択して、L列までコピーします。

Countifs2

なお、ここで入力した式について、
=COUNTIFS(C$2:C$21,"<=45")

=COUNTIFS(C$2:C$21,">=46",C$2:C$21,"<=50")
のように、COUNTIFS関数の条件として、点数の幅の下限や上限を示す文字列を入れても計算結果は同じです。
同じですが、このようにしないほうがよいと思います。

なぜかというと、やはり先にふれたように自分以外の人が見ても理解しやすいというのもありますが、それよりも「ちょっと変えたい」となったときにより簡単に修正できるようにしておくためです。

「以下」と「以上」の条件を変える必要が出てきたとき、図のように条件が別のセルに書かれているのと、関数の式のなかに条件が書かれているのとでは、どちらが修正しやすいですか。

たぶん、前者だと思います。
(後者のほうが修正しやすいのでしたら、そうしてください。そういう人はいないと思いますけど……)

こういうセルの使い方を作業列とか作業セルといいます。

FREQUENCY関数を使う説明したサイトでは、一つのセルにFREQUENCY関数で答えを求めれば、見た目がすっきりする――といった説明をしているサイトもあります。
それはそれで間違っていません。

ただ、一つのセルだけで計算の答えを得ようとすると、どうしても数式が長くなって見づらくなり、何をしているのか理解もしづらくなりがちです。理解しづらいということは、ちょっと変えたいときに面倒でもあるということです。

作業列を使って計算の過程を分けておけば、誰もが理解しやすくできますし、「ちょっと変えたい」にも対応しやすくなります。
上図でいえば、下限を「30以下」から初めて「15」ずつ区切っていく――に変えたい場合、H列とI列の式を書き換えるだけで済みます。

    <=30
>=31 <=45
>=46 <=60

というように。

数式のなかに数式を入れる――いわゆる「ネスト」する場合も、各数式を別の別のセルに入れておいて計算結果を参照すると、視認性も高まり、修正も容易になります。
(作業列の利用は、僕も先達から教えてもらった知恵です。作業列を使ったせいで困ったことは一度もありませんが、逆はあります。)

「エクセルのシートで作業列を使うと、列がどんどん増えていってシートが見づらくなるから作業列は使わない」とかいって、作業列を使うことを嫌う人もいますが、僕に言わせればそれはちょっとした勘違いです。

見づらければ、列を非表示にすればいいんです。

とはいえ、ここでも「そうしなければならない」という話ではありません。
職場では「作業列を使わないほうが仕事が円滑に進む」、「作業列を使わなくても数式の理解や修正は誰もが簡単にできる」というのであれば、使う必要はないと思います。

| | コメント (0) | トラックバック (0)

Excel でワークシート名を一覧にする VBA のコード

この記事は自分用の覚書です。

K.N さん
エクセルで、ブックのなかのワークシート名を一覧にするマクロのコードの件です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

ネットで見つけたものが役に立ってよかったですね。
まずはよしとして、「よくわからないけど、やりたいことはできた」ままでは応用ができません。

少しでも役立てられるようにコメントをつけてみました。わかりづらいところがあったら、今度会った時に聞いてください。
もっとも、私にわかる範囲でしか話せませんが。悲しいかな私も「VBAなんて、おちゃのこさいさい」というわけではないので。


Sub ワークシート名を一覧にする()
    Dim i As Long
    Dim Sheet_Name As Variant

    Worksheets("ワークシート名の一覧").Select
    For i = 1 To Worksheets.Count - 1
        Sheet_Name = Worksheets(i).Name
        Cells(i, "A").Offset(1, 0).Value = Sheet_Name
    Next i
'
'あらかじめシート「ワークシート名の一覧」を一番右端に作っておく。
'
'変数 Sheet_Name は Variant (バリアント)型で宣言。
'バリアント型にしておけば、ワークシート名が文字でも数字だけでもどんな値であっても格納できる。
'なお、変数の型をバリアント型にしたい場合は、型の指定を省略できる。
’    Dim Sheet_Name
'と書くと、宣言した変数をバリアント型で指定したことになる。
'
'左端のワークシートを 1 で表す。
'変数 i に 1 が代入されると、
'Sheet_Name = Worksheets(i).Name
'は、
'Sheet_Name = Worksheets(1).Name
'となって、これは一番左端のワークシートの名前を、変数 Sheet_Name に代入するということ。
'右端のワークシートまでの枚数を Worksheets.Count で数えている。
'-1 しているのは、ワークシート名の一覧を載せるシートの一つ手前までを指定するため。
'
'変数 Sheet_Name をA列のセルに代入していく。
'OffsetプロパティでA列のセルを一行ずつ下にずらしていく指定をしている。
'Offsetプロパティは、Offset(行, 列)、Cellsプロパティも Cells(行,列)という書式なので、
'
'Cells(i, "A").Offset(1, 0).Value
'
'のコードで、変数 i が 1 のときは、
'
'Cells(1, "A").Offset(1, 0).Value = Sheet_Name
'
'ということになって、A1セルの一行下のA2セルを、変数 Sheet_Name の代入先に指定したことになる。
'変数 Sheet_Name の代入先が、A2セルからなのは1行目に列見出しを入れるため。
'
End Sub


難しいのは、 Cells(i, "A").Offset(1, 0).Value のところでしょうか。
VBA を学びだした際に Offsetプロパティの動きをイメージするのは難しいかと思います。私もまだイメージしづらいです。

基準となるセルやセル範囲を、指定した行数や列数あるいは両方ともずらす・移動する――というものなんですが。

Offset(行,列) ということになっています。 Offset(1, 0) なら「(列はそのままで)1行下にずらす」ということです。
なので、変数 i が 1 の場合は、

Cells(1, "A").Offset(1, 0).Value

となって、 Cellsプロパティも Cells(行,列)なので、これは――A1のセルから1行下のセル―― A2 のセルの値を指していることになります。
なお、 Cells(i, "A") は、 Cells(i, 1) と書いてもどちらも同じ意味です。

Offsetプロパティって文字で書くとよけいにイメージしづらいですよね。

なので、ちょっと考え方を変えてみます。

Cells(i, "A") の変数 i が 1 のときは 、OffsetプロパティによってA2セルを、 i が2 のときはA3セルを、3 のときはA4セルを意味します。

Cells(1, "A").Offset(1, 0).Value なら、セルA2の値。
Cells(2, "A").Offset(1, 0).Value なら、セルA3の値。
Cells(3, "A").Offset(1, 0).Value なら、セルA4の値。

これって変数 i が「一つ増え」て1行下を示しているのと同じことですよね。
ならば、この場合は変数 i に 1 を足してやれば、Offsetプロパティを使わなくてもよくなるといえます。

Cells(i + 1, "A").Value = Sheet_Name

というように。

こちらのほうが「1行ずらしていく」イメージがしやすくないですか?
もちろん、「Offsetプロパティのほうがイメージがしやすい」ということでしたら、ぜんぜんそれで構わないと思います。

ようは、わかりやすいほうがいいよねという話ですから。
先のコードを変えてみました。


Sub ワークシート名を一覧にする()
    Dim NewWorkSheet As Worksheet
    Dim i As Long
    Dim Sheet_Name As Variant
   
    Set NewWorkSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    NewWorkSheet.Name = "ワークシート名の一覧"
        For i = 1 To Worksheets.Count - 1
            Sheet_Name = Worksheets(i).Name
            Cells(i + 1, "A").Value = Sheet_Name
        Next i
'
'変数 NewWorkSheet を ワークシート型で宣言。
'Addメソッドは、ワークシートを正常に挿入したあとWorksheetオブジェクトを返す。
'挿入したシートの名前を文字列で返すのではなく、挿入したシート自体を返す。
'したがって、返り値を受け取る変数はワークシート型かバリアント型を指定しておかなければならない。
'
'Set NewWorkSheet = Worksheets.Add(after:=Worksheets(Worksheets.Count))
'のコードで、今あるワークシートの一番右(後ろ)に新しくワークシートを追加し、
'変数 NewWorkSheet でそれを受け取る。
'
'Addメソッドのオプションを () で囲っているのは、VBAのルール。
'このコードのように返り値を変数に代入するなど、返り値を利用する場合は、括弧を付けなければならない。
'たとえば、
'
'Sub Sample01()
'    Worksheets.Add after:=Worksheets(Worksheets.Count)
'    ActiveSheet.Name = "新しいワークシート"
'End Sub
'
'のコードでも、現在あるワークシートの最後尾にワークシートを追加できる。
'この場合は、Add メソッドの返り値を利用していないので、Add メソッドの後ろに あるオプションを () で囲むことは不要。
'
'また、
'Worksheets.Add(after:=Worksheets(Worksheets.Count))
'の部分を
'Worksheets.Add(Before:=Worksheets(1))
'のように書くと、
'現在あるワークシートの一番左(先頭)に新しいワークシートを追加できる。
'
'変数 Sheet_Name は Variant (バリアント)型で宣言。
'バリアント型にしておけば、ワークシート名が文字でも数字だけでもどんな値であっても格納できる。
'なお、変数の型をバリアント型にしたい場合は、型の指定を省略できる。
’    Dim Sheet_Name
'と書くと、宣言した変数をバリアント型で指定したことになる。
'
'左端のワークシートを 1 で表す。
'変数 i に 1 が代入されると、
'Sheet_Name = Worksheets(i).Name
'は、
'Sheet_Name = Worksheets(1).Name
'となって、これは一番左端のワークシートの名前を、変数 Sheet_Name に代入するということ。
'右端のワークシートまでの枚数を Worksheets.Count で数えている。
'-1 しているのは、ワークシート名の一覧を載せるシートの一つ手前までを指定するため。
'
'変数 Sheet_Name をA列のセルに入れていく。
'変数 i に 1 を足すことでA列のセルを一行ずつ下にずらしていく指定をしている。
'Cellsプロパティは Cells(行,列)という書式なので、
'
'Cells(i + 1, "A").Value
'
'のコードで、変数 i が 1 のときは、
'
'Cells(2, "A").Value = Sheet_Name
'
'ということになって、A2セルを変数 Sheet_Name の代入先に指定したことになる。
'変数 Sheet_Name の代入先が、A2セルからなのは1行目に列見出しを入れるため。
'
End Sub


せっかくなので、新しいワークシートを追加するようにしてみました。
これで、ワークシートを最初に右端に作る必要がありません。

また、今回の場合は

        Sheet_Name = Worksheets(i).Name
        Cells(i + 1, "A").Value = Sheet_Name

のところは、

        Cells(i + 1, "A").Value = Worksheets(i).Name

のように変数を使わないで1行で書いてもいいかなとも思います。
Worksheets(i).Name からなんとなく「ワークシート名を取得しているんだな」と VBA に不慣れな人でも想像がついて、何をしているかを理解しやすいと思うので。

変数を使ったのは私のクセです。
変数を使ったほうが何かと便利な場合のほうが多いので、日ごろから変数を使うクセをつけているというか、心がけているというかです。

でも、このコードの場合、1行あたりも短いし、全体的な行数も多くないので全体として何をやっているかを理解しやすいと思いますし、どこかで間違えてデバッグするのもそう大変ではないと思います。

そういう理由で今回は変数を使わないで1行に書くのもありかもと思ったしだい。
どちらがいい・悪いという話ではないです。自分や周りの人たちが見やすく、理解しやすいことが大事です。

それから変数を使う場合は、

    Dim i As Long
    Dim Sheet_Name As Variant

のように Dim を使って必ず変数を宣言してください。
「変数は宣言しなくてもいい」「宣言しなくてもちゃんとコードが実行できた」といった話を見たり聞いたりするかもしれませんが、「宣言しなくていい」を信じないでください。

変数を宣言をしたからといって害になることは何もありませんが、宣言していないと害を被ることがあります。だったら、宣言しておくのが賢い選択です。

ちなみに、変数の宣言は、

    Dim i As Long, Sheet_Name As Variant

というように , で区切って1行で書くこともできます。

なぜ変数は宣言しなければならないか、宣言しないとどんな目に遭うのか――。それについては、下記のサイトでとてもわかりやすく説明してくれています。
日本におけるExcelの第一人者とされている「田中亨」という方のサイトです。

変数って宣言しなくちゃいけないの?

| | コメント (0) | トラックバック (0)

Excel でB列に値が入っていたらA列に連番を入れる。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

K.M さん。
B列のセルに値が入っていたらA列のセルに連番を振っていくVBAのコードの試作です。
下記のような感じでどうでしょう。

B列に空欄がある場合にそれを数えるか、数えないかでコードが少しだけ違います。
N = N + 1 の位置が違っています。

B列の空欄セルは飛ばしてA列に連番を入れるか、B列の空欄セルも数えてA列に連番を入れるかの違いです。
前者だと、常に 1 から始まり、 2 3 4 5 と続きます。
後者だと、B列が空欄のセルを数えても、A列には入力しないので、1 3 5 というようになります。(2 と 4 がB列の空欄セルの場合)

ちなみに、B列に空欄がない場合は、どちらを実行してもA列に入るのは 1 2 3 4 5 という連番です。
なお、1行目に列見出しをつけるのでしたら、
For i = 1 To 5
の部分を For i = 2 To 5 に変えてください。(1 を 2 に変える。)

Sub B列を基準に連番_空欄セルは飛ばす()
    Dim i As Long
    Dim N As Long
    For i = 1 To 5
        If Cells(i, "B").Value <> "" Then
            N = N + 1
            Cells(i, "A").Value = N
        End If
    Next i
'
'Cells(i, "B").Value <> "" は、「B列のセルの値が空欄に等しくない(空欄ではない)」という意味。
'
'B列のセルが空欄でない場合、 if と End if の間にあるコードを実行する。
'変数 N に 1 を足したものを N に代入。さらに N をA列のセルに代入する。
'
'宣言した直後は変数は 0 なので、 N + 1 は 0 + 1 ということで、 N = N + 1 は  N = 1 ということになり、
'変数 N に 1 が代入される。
'この場合の = は、左辺と右辺が等しいことを意味する等号ではない。右辺を左辺に代入することを意味する代入記号。
'
'Cells(i, "A").Value = N のコードで、1 が代入された変数 N がA列のセルの値に代入される。
'つまり、A列のセルの値に 1 が入るということ。
'
'二度目に「B列のセルの値が空欄でない」という条件が満たされた場合は、一度目に変数 N に 1 が代入されているので、
'N = N + 1 は N = 1 + 1 ということで、変数 N に 2 が代入される。
'
'B列のセルが空欄の場合は、if と End if の間にあるコードは実行されないので、
'結果的に、A列のセルに入る値は常に 1 からの連番になる。(B列の空欄セルは飛ばしてA列のセルに連番を入れることになる。)
'
End Sub

空欄セルは飛ばす

Sub B列を基準に連番_空欄セルも数える()
    Dim i As Long
    Dim N As Long
    For i = 1 To 5
        N = N + 1
        If Cells(i, "B").Value <> "" Then
            Cells(i, "A").Value = N
        End If
    Next i
'
'変数 N に N + 1 を代入する。
'宣言した直後は変数は 0 なので、 N + 1 は 0 + 1 ということで、 N = N + 1 は  N = 1 ということになり、
'変数 N に 1 が代入される。
'この場合の = は、左辺と右辺が等しいことを意味する等号ではない。右辺を左辺に代入することを意味する代入記号。
'
'Cells(i, "B").Value <> "" は、「B列のセルの値が空欄に等しくない(空欄ではない)」という意味。
'
'B列のセルが空欄でない場合、 if と End if の間にあるコードを実行する。
'変数 N をA列のセルに代入する。
'
'For Next の繰り返しのなかに N = N + 1 のコードがあるので、繰り返されるごとに変数 N が 1 ずつ増える。
'最初は 1 、次は 2 、次は 3 というように。
'
'その繰り返しのなかで、「B列のセルの値が空欄ではない」条件が満たされた場合、
'Cells(i, "A").Value = N のコードで、変数 N がA列のセルの値に代入される。
'繰り返しの最初なら 1 が、二度目なら 2、三度目なら 3 が変数 N に入っているので、その数がA列のセルの値に入ることになる。
'
'B列のセルが空欄の場合は、if と End if の間にあるコードは実行されないので、
'結果的に、A列のセルに入る値は、B列の空欄のセルも数えた数になる。
'(B列の空欄セルも飛ばさずに数えた連番をA列のセルに入れることになる。)
'
End Sub

空欄セルも数える

| | コメント (0) | トラックバック (0)

Excel で連番の抜けをチェックする。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

重複のない連番について、途中で抜けている番号は何かをチェックする。

・B列のセルに入っている式
=VLOOKUP(ROW()-1,$A$2:$A$16,1,FALSE)

#N/A のエラーが表示されているセルが番号が抜けているところ。

Renbannukecheck

| | コメント (0) | トラックバック (0)

Excel の「アクティブ行との相違」、「アクティブ列との相違」

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

エクセルの「ジャンプ」機能の「セル選択」のなかにある「アクティブ行との相違」と「アクティブ列との相違」の動きの違い。

■アクティブ行との相違
アクティブセル領域のなかで、アクティブセルがある列の個々のセルに対して左右に向かって(つまり行のなかで)それぞれのセルの値と異なる値が入っているセルを見つけて選択する。

■アクティブ列との相違
アクティブセル領域のなかで、アクティブセルがある行の個々のセルに対して上下に向かって(つまり列のなかで)それぞれセルの値と異なる値が入っているセルを見つけて選択する。

■アクティブ行との相違
1.セル範囲を選択して、「ジャンプ」の機能を呼び出す。
「ジャンプ」はショートカット Ctrl + G で呼び出せる。
リボンからは、「ホーム」タブ-「編集」グループ-「検索と選択」のなかの「ジャンプ」

Gyousoui1s

※氏名は、「疑似個人情報データ生成サービス」を利用して生成。

2.「ジャンプ」のダイアログボックスが開いたら「セル選択」をクリック。

Jumpdialogbox

3.「アクティブ行との相違」をチェックして、OKをクリック。

Active_gyousouis

4.セルが選択される。

Gyousoui2s

この場合は最初に選択した状態のときは、セルA2の「浜崎穂乃香」がアクティブセルなので、A列のなかで次のような動きをする。

セルA2の左右に対して「浜崎穂乃香でないもの」を探す。
(つまり2行目のなかで「浜崎穂乃香でないもの」)

セルA3の左右に対して「小寺信彦」でないもの」を探す。
(3行目のなかで「小寺信彦でないもの」)

セルA4の左右に対して「小口美帆でないもの」を探す。
(4行目のなかで「小口美帆でないもの」)

セルA5の左右に対して「熊田好でないもの」を探す。
(5行目のなかで「熊田好でないもの」)

セルA6の左右に対して「石原千沙でないもの」を探す。
(6行目のなかで「石原千沙でないもの」)

イメージとしては次のよう。

Gyousoui3s

この場合は、最初にアクティブセルがあるA2の左にはセルがないので、セルの右方向だけで異なる値を探している。

A列にある各セルを基準にして、行ごとに相違を探しているので、A列のどのセルをアクティブセルにした場合も、最後に選択されるセルの結果はすべて同じになる。

最初に選択した状態から、アクティブセルを移動するには、Tabキーを押す。
Shift + Tab で逆方向へ戻る。

■アクティブ列との相違
1.セル範囲を選択して、「ジャンプ」の機能を呼び出す。
画像では、セル範囲 A2:C6 を選択してから、Tabキーでアクティブセルを A4 に移動している。

Retusoui1s

2.「ジャンプ」のダイアログボックスが開いたら「セル選択」をクリック。

3.「アクティブ列との相違」をチェックして、OKをクリック。

Activ_retusouis

4.セルが選択される。

Retusoui2s

最初に選択した状態のときは、セルA4の「小口美帆」がアクティブセルなので、次のような動きをする。

セルA4の上下に対して「小口美帆でないもの」を探す。
(つまりA列のなかで「小口美帆でないもの」)

セルB4の上下に対して「福井栄一」でないもの」を探す。
(B列のなかで「福井栄一でないもの」)

セルC4の上下に対して「出口博子でないもの」を探す。
(C列のなかで「出口博子でないもの」)

イメージとしては次のよう。

Retusoui3s

この場合は、最初にアクティブセルがA列の4行目にあるので、4行目にある各セルの上下で異なる値を探している。

4行目にある各セルを基準にして、列ごとに相違を探しているので、4行目のどのセルをアクティブセルにした場合でも、最後に選択されるセルの結果はすべて同じになる。

| | コメント (0) | トラックバック (0)

Excel2013で書式の検索ができない場合に確認すること。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

■現象
ある色で塗りつぶしてある複数のセルを検索で見つけたい。
「検索と置換」-「オプション」-「書式」で、「セルから書式を選択」を実行して、見つけたい塗りつぶしのセルから色を吸い取って検索してもその色になっているセルが見つからない。

検索対象が見つかりません。[オプション]をクリックして、検索方法を変えてみてください。

と表示される。

■対処
探している色で塗りつぶされているセルを選択し、セルの書式設定で「フォント」の「色」が「自動」になっていないかを確認する。

「フォント」の「色」が「自動」になっていると書式の検索で見つからない。「自動」になっていたなら、「自動」から別の色に設定する。

| | コメント (0) | トラックバック (0)

ロックされている(いない)セルを一括で選択する。

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

  1. Ctrl+F を押して「検索と置換」画面を出す。
  2. 「書式」ボタンの右側にある▼印から「書式」を選択
    「書式」ボタンをクリックしても同じ。
    「書式」ボタンが表示されていない場合は、「検索と置換」の「オプション」をクリック
  3. 「保護」タブの「ロック」にチェックが入っていることを確認。
    ロックされていないセルを見つける場合はチェックを外す。
  4. OKをクリック
  5. 「検索と置換」画面に戻るので「すべてを検索」ボタンをクリック
  6. 画面下部に、ロックされている(いない)セルの一覧が表示される。
    CtrlキーあるいはShiftキーを押しながら表示されたセルを選択する。

| | コメント (0) | トラックバック (0)

FLOOR関数とCEILING関数の考え方

この記事は自分用の覚書です。
(Windows 8.1 Pro 64ビット + Excel2013 Home & Business)

■関数の理解で大事なこと
その関数がどんな動きをするかをイメージすること。

■FLOOR関数の書式
FLOOR(数値, 基準値)
・Excelヘルプに載っている説明
指定された基準値の倍数のうち、最も近い値かつ 0 に近い値に数値を切り捨てます。

■CEILING関数の書式
CEILING(数値, 基準値)
・Excelヘルプに載っている説明
基準値の倍数のうち、絶対値に換算して最も近い値に切り上げられた数値を返します。

■2つの関数は内部で次のような動きをしている。
1. 書式で指定した「基準値」ずつ数を区切る。
式が

=CEILING(72,5)

だとすると基準値は 5 なので、5,10,15,20,25……となるように。

2. 区切ったなかで、「数値」がどこにあるかを調べる。
数値 72 は、 5,10,15……65,70,75,80……と続くなかで、 70 と 75 の間にある。

3. 式の中の「数値」が含まれる間隔の高いほう、あるいは低いほうの数を返す。
72 は 70 と 75 の間にあるので、この場合の答えは 70 か 75 になる。

・FLOOR関数:「数値」が含まれる間隔の低いほうを返す。
式が =FLOOR(72,5) なら 70 。

・CEILING関数:「数値」が含まれている間隔の高いほうを返す。
式が =CEILING(72,5) なら 75 。

FLOOR関数とCEILING関数のイメージ

■使用例
例1. 必要数75個を満たすには、1箱6個入りのものを何箱そろえればよいか。
必要数を満たせばよい(多めにあってよい)ので、式の中の「数値」が含まれている間隔の高いほうの数を答えとして返すCEILING関数を使う。

出た答えを 6 で割れば、何箱そろえればよいかが求められる。
(例では「基準値」が 6 なので、6,12,18,24,30……60,66,72,78……という区切りになる。 75 は 72 と 78 の間にある。)

例2. 必要数75個をちょうどそろえるには、1箱6個入りのものを何箱そろえて、単品で何個そろえればよいか。
必要数をちょうどそろえる(多めにあってはいけない)ので、低いほうを答えとして返すFLOOR関数をまず使う。

残りの個数は必要数から関数の答えを引けば求められる。

FLOOR関数の使用例1

例3. 年齢を年代別にわかりやすくする。
10から19はどの数もすべて「10」、20から29は「20」というようにしたいので、式の中の「数値」が含まれている間隔の低いほうを答えとして返すFLOOR関数を使う。

10,20,30……という区切りにしたいので「基準値」は 10 。

FLOOR関数の使用例2

例4. 100円未満を切り捨てて価格付けする。
「100円未満切り捨て」ということは、390円を300円に、1,254円を1,200円にというように何百円という価格にするということ。
100,200,300……という区切りのなかで「数値」を見つけて、低いほうにしたいのでFLOOR関数を使う。

「基準値」は 100 。

FLOOR関数の使用例3

| | コメント (0) | トラックバック (0)

Excelで、姓と名前の間に全角スペースを入れる。

この記事は自分用の覚書です。

F.K さん。
エクセルで、「姓と名前の間に全角スペースを入れる」件の例です。
( Excel2013 Home & Business )

自動的にというか一発でというかは無理です。
過程のどこかで人間が目で確認して整える作業が必要です。

姓って1文字・2文字・3文字、珍しいですが4文字もあります。どこまでが姓でどこからが名前なのかなんて人間にしかわからないですから。
全員2文字の姓とかだったら整える作業はしないで済むのですけどね。

■例:姓と名前の間に全角スペースを入れる。

姓と名前の間に全角スペースを入れる例

例ではA列に元になる氏名を入れて、B列でLEFT関数を使って氏名の左から2文字を取り出しています。
姓としては一番多いであろう2文字をまず抜き出しておくというわけです。

この時点で姓だけを抜き出せているか視認して、違っていたらLEFT関数を修正するしかないと思います。
色を付けているのが、うまく姓を抜き出せていないセルです。外国の方の場合はどう処理するかも決めておく必要がありますね。

B列で使っているLEFT関数は、
=LEFT(A2,2)
というような式になっています。
修正するのは右端の 2 ですね。3文字の姓だったら 3 にします。

C列では、SUBSTITUTE関数を使って氏名のうち姓の文字だけを、 姓+全角スペース に置換しています。
=SUBSTITUTE(A2,B2,B2&" ")
という式になっています。

A2:A2の値(氏名)を対象にしています。
B2:そのうちB2にある値(姓のみ)を置き換えます。
B2&" ":何に置き換えるかをこの部分で指定しています。B2の値(姓)に全角スペースを付けたものに置換します。

その結果、姓と名前の間に全角スペースが入ったものができあがります。

■この件でのREPLACE関数の使用
REPLACE関数の話ですが、答えは「仕事で使うファイルではこの件でREPLACE関数を使ってはいけない」です。

確かにB列で
=REPLACE(A2,3,," ")
という式を入力しても姓と名前の間に全角のスペースを入れることができますが、REPLACE関数はこういう使い方をしてはいけません。

なぜいけないかは次のようです。

REPLACE関数の書式は、
REPLACE(文字列, 開始位置, 文字数, 置換文字列)
です。

そしてヘルプを見ると、 REPLACE関数の4つの引数は「必ず指定します」となっています。

=REPLACE(A2,3,," ") の式では、3番目の引数(開始位置から何文字を置き換えるかの文字数)が省略されています。
それでもエラーも起こらず処理がされるのは、いわばエクセルが例外として気を利かせてくれているからといえます。

ヘルプに「必ず指定します」となっている引数を省力しているのですから、その式は "間違った式" です。
ですから、エクセルのバージョンアップや WindowsUpdate などで、ある日突然答えが得られないことが起きる恐れがあります。

それが明日起こるのか、5年後に起こるのか、あるいはいつまでもまったく起こらないかもしれません。
でも、突然起こっておかしくないんです。必ず指定することになっているのにしていないのですから。

いつ答えが得られなくなってもおかしくないファイルを、仕事で使うのは危険です。だから、この件では「REPLACE関数を使ってはいけない」ということになります。

| | コメント (0) | トラックバック (0)

Excel で、製品番号と品名を関数で分けてセルに入れる。

この記事は自分用の覚書です。

Y.S さん。
製品番号と品名がくっついているデータを一つのセルにコピー貼り付けしたら、それぞれが別のセルに入力されるようにする例です。
( Excel2013 Home & Business )

■例: MX100 - Hard Disk を MX100 と Hard Disk に分ける。

関数で番号と文字の分離の例

A2に MX100 - Hard Disk をコピー貼り付け。

B2 に =FIND("-",A2) を入力。

C2に =LEN(A2) を入力。

・ - より左側を抽出
=LEFT(A2,B2-2)
- (ハイホン)を目印にできるので、FIND関数でハイホンが何文字目に来るかを調べる。
LEFT関数で文字列の左端から文字を抜き出す。何文字目まで抜き出すかは「ハイホンの位置-2」で指定できる。
ハイホンの位置が 7 なので5文字まで抜き出すことになる。7 はスペースを含んで数えているので、2を引くことになる。

・ - より右側を抽出
=MID(A2,B2+2,C2)
上と同様に考えて、FIND関数でハイホンの位置を調べた値に 2 を足すと、MID関数で文字を抜き出し始める「開始位置」を指定できる。ハイホンの位置が 7 なので9文字目から抜き出すことになる。

何文字目まで抜き出すかの指定には、LEN関数で求めた元の文字列の全文字数を使っている。
当然ながら、ハイホンより右側は元の文字列よりも文字数が少ない。
ハイホンより右に書いてある文字数(スペース含む)以上であれば、抜き出す数にいくつを指定してもかまわないということ。書いてある文字以外は表示されるはずはないので。

それならば、抜き出す文字数として元の全文字数を使っておけば、「ハイホンより右側」が何文字になっても、常に右側全部を抜き出せる。

■使っている関数について
使っている関数は4種類。
FIND関数、LEN関数、LEFT関数、MID関数。

FIND関数は、指定された文字列を他の文字列の中で検索し、その文字列が最初に現れる位置を左端から数え、その番号を返す。

LEN関数は、文字列の文字数を返す。

LEFT関数は、文字列の先頭から指定された数の文字を返す。

MID関数は、文字列の指定された位置から指定された文字数の文字を返す。

■留意点1(入力するときは、1セル1データ・1件1行で。)
例の画像では、ブログの横幅内におさめるためにわざと表を重ねています。

エクセルでデータを入力するときは画像のような表の作り方をしてはだめです。
このような表の場合、これから2行目以降にどんどんデータを入れていくはずです。エクセルでそんな表を作るときは、1セル1データ・1件1行になるように作ってください。
これから計算したり分析したりするデータを入力するときは「セルの結合」も使いません。入力のときは、とにかく1セル1データ・1件1行になるようにします。

「文字列」「-の位置」「A列の文字数」「-より左側」「-より右側」……

と列見出しが並ぶように。
「価格」や「重量」「寸法」といったほかの項目がまだあるなら、それらも列として並べます。
列が増えて困るのでしたら非表示にすればいいだけです。

■留意点2(自分以外の人が見てもわかりやすいように。)
仕事でファイルを作るときは、「自分以外の人もそのファイルを使う」、「そのファイルは他者に引き継ぐもの」ということを忘れないようにしてください。
この例では4種類の関数を使っていますが、次にファイルを使う人が自分と同等の理解があるとは限りません。仕事で使うファイルは、誰が見てもわかりやすいように作ってください。

例のような場合、一つのセルに式を入れてしまいたくなりがちですが、一つのセルで、

=LEFT(A2,FIND("-",A2)-2)

=MID(A2,FIND("-",A2)+2,LEN(A2))

と入力すると、関数に不慣れな人はぱっと見て、何をどうやっているのかイメージできません。
計算の段階を分けておくことで、どういうことをやっているのかがイメージしやすくなります。

個々の関数がどんな関数なのかは、後任の人が学んで理解するしかありません。そこは本人の努力が必要なところです。
その際に関数に不慣れな人にとって難しいのは、関数がどんな働きをするかをイメージすることかと思います。
不慣れな人でもイメージしやすいように、理解を助けるようにファイルを作っておけば、次にそのファイルを使う人の学びを円滑にできます。

一つひとつはわずかなことですが、そのことは組織全体の生産性の向上にもつながっていきます。
5秒迷うことが20回あると1分迷ったことになります。それを60人がやっていたら1時間になります。

そのように考えると、「そのぐらいわかっておけ」「それぐらいググれ」というのは、自分がやったことの結果に対する考えが浅いというものです。あとでそんなことを言わなくてもよいように最初からしておくべきです。それが効率の良い仕事というものでしょう。

また、関数は単体ではなく「組み合わせて使うもの」ですが、「一つの長い式にして使うもの」ではありません。
(例では関数の結果を別の関数内で使う値として「組み合わせ」ています。)

「一つの長い式が作れることはいいことだ・そうするべきだ」と考えるのは勘違いです。

長い式を作れること自体は、いけないことではありません。ですが、「自分以外の人が見てわかりやすくする」ことは、仕事で使うファイルにはもっと大事なことです。

一つの長い式で処理したくなったら、「そうすることが、他の人にとって見やすいだろうか、わかりやすいだろうか」、「どうやったら自分以外の人も見やすいのか、わかりやすいのか」と考えるようにしてください。

また、「仕事を引き継ぐ」ということを考えると、「なんだかよくわからないファイル」を作らないようにしてください。

  • インターネットに書いてあったことをコピペしたら、なんだかよくわからないけれどできた。
  • 掲示板で教えてもらったとおりにしたら、なんだかよくわからないけれどできた。

とかいうのが「なんだかよくわからないファイル」が職場で生まれる主な原因です。

インターネットを使うなということではありません。使ってより便利になるなら使うべきです。

重要なのは、その際に「なんだかよくわからないことはなくす」、「なにをどうやっているのかを、他人にわかりやすく説明できるぐらいにわかっておく」ということです。

そうでないと、ファイルを引き継いだ人が迷惑します。少し変えたいと思っても何もできません。前任者がなんだかよくわかってないんですから、後任者に説明のしようがないので。

よくわからないので後任者はインターネットで情報を探します。そして、見つけたものをコピペしたり、掲示板で教えてもらったことをやってみたりします。

そしてまた、なんだかよくわからないけれどやりたいことができたファイルが生まれます。

| | コメント (0) | トラックバック (0)

より以前の記事一覧