前回、企業が入社テストとして出題した「Excel問題」のその1をやってみました。
基本的な関数や数式、参照方法などがありましたね。
今回も基本的な関数を使った問題となります。
一緒にやっていきましょう。
スポンサーリンク
スポンサーリンク
問題2を確認
出題されていたエクセルの画面と同じものを作成し、以下からダウンロードできるようにしてあります。
今回は、条件に合うデータ件数を数えたり、条件に合うデータの合計を計算したりとなりそうですね。
ポイントは前回と同様2つとなるでしょう。
- 回答を効率よく求める
- 黄色い枠(セル)だけを変更できる
では、まずデータ件数から数えてみましょう。
名称別件数を数える
各名称(あ～く)のデータが入力された表があります。
この名称ごとの件数を数えていきます。
最初に「名称あ」の件数(セルG5)を求めてみたいと思います。
「条件に当てはまる個数を調べる」ためには、「countif関数」を使います。
引数は「範囲」と「検索条件」になり、次のように指定します。
■範囲
B5:B19→表内の名称列(B列)に対して、個数を数える検索範囲に指定します。
■条件
あ→名称のデータが「あ」だけを数えるために、条件として「あ」を指定します。
関数は、上の画面の状態で「OK」ボタンをクリックすると、検索条件の文字列には自動的に「""(ダブルクォーテーション)」が付きます。
すると結果として、「2」という答えが返ってきます。
この表内には、名称が「あ」のデータは2件ある、という意味になります。
さて、後は「名称い」から「名称く」までをフィルハンドルでコピーして・・・、と思ったら上手くいきませんよね。
フィルハンドルで数式などの連続データをコピーする方法を「オートフィル」と言います。今後はオートフィルに統一していきます。
coutif関数を使っている点は間違いないのです。
しかし、効率よく回答しようと思ったら、以下の2点において間違いがあります。
- 表を絶対参照していない
- 検索条件を「あ」と直接、文字で指定している
フィルハンドルでコピーするのは、数式であると前回も書きました。
その上で、セル参照には「相対参照」と「絶対参照」があるという点もお伝えしてきました。
今回も名称別件数を1件表示した後に、下方向へオートフィルします。
その場合であっても、表のデータは「5行目から19行目まで」と固定されていなければなりません。
さらに、検索条件に「あ」と直接、文字を指定しています。
この場合、下方向へオートフィルしても検索条件は常に「あ」となってしまいます。
それでは、この2点を踏まえて関数を修正していきましょう。
絶対参照に切り替える
「名称別件数」を効率よく計算するために、表のデータを絶対参照にして、常に同じ範囲を検索対象とします。
「セルG5」から下方向へオートフィルしても表のデータは、「B5:B19」のままにするため、行番号に「$」を付けました。
検索条件をセルで指定する
先ほど、検索条件には「あ」と直接、文字を入力しました。
ただ、これでは数式がコピーされるフィルハンドルにおいても、検索条件が常に「あ」という指定のままになってしまいます。
「あ」という文字は検索したいけれども、それを数式で指定するには・・・。
そうですね。
F列の5行目から12行目まで「あ」から「く」の文字列を並べてくれているので、これを利用しましょう。
そこで検索条件の指定を「あ」と直接、文字を入力するのではなく「セルF5」を指定します。
最終的に「countif関数」は以下のようになりました。
COUNTIF(B5:B19,F5)
検索範囲は、表の「5行目から19行目」(B$5:B$19)となり、検索条件は「あ」(F5)となります。
結果は、同じく「2」が返ります。
「い」から「く」はオートフィルで
では、この関数の入った数式をオートフィルで下方向に12行目までコピーしてみましょう。
一気に各名称の件数が表示されました。
試しに「え」(セルG8)の数式を確認してみましょう。
検索範囲の表データは、同じく「B$5:B$19」と「5行目から19行目」までが絶対参照で指定されています。
検索条件は「セルF5」から「セルF8」へと参照先も同時に動いていますね。
これは「え」の文字を指定しているわけです。
条件別合計を計算する
さて、まずは名称別の件数を「coutif関数」を使って表示させました。
続いては、以下2つを表示していきます。
- 各名称別のAの合計
- 各名称別のBの合計
またしても、条件ごとの計算となり、今度は足し算となります。
条件ごとの足し算は、「sumif関数」を使いますね。
最初に「名称あ」の「A合計」(セルH5)を求めてみましょう。
今回は最初から絶対参照を指定していきます。
さて、今回は絶対参照の指定が格段に増えました(笑)
それはなぜかと言うと、表の位置(B列)や検索条件の位置(F列)は固定とさせながら、A合計とB合計の両方の列を一気に計算しようとしているからです。
そして「sumif関数」には、「範囲」と「検索条件」の他に、「合計範囲」という引数も1つ増えました。
それらを踏まえて、順番に見ていきましょう。
「範囲」の参照は？
「sumif関数」は、範囲から条件に合うデータだけを合計する関数となります。
まず最初の「範囲」ですが、これは以下のように指定しています。
$B$5:$B$19
今回は「範囲」の引数の列番号にも絶対参照を指定しました。
範囲指定(B列)は、名称が「あ(5行目)」でも「い(6行目)」でも「う(7行目)」でも常に「5行目から19行目」に固定です。
同じく「A合計(H列)」でも「B合計(I列)」でも常に「B列」に固定です。
そのため、引数「範囲」には、行列どちらにも「絶対参照」を指定しています。
検索条件の参照は？
次の「検索条件」は以下のように指定しました。
$F5
「検索条件」は、列番号だけに絶対参照を指定しています。
これは、条件の指定列である「F列」は、「A合計(H列)」でも「B合計(I列)」でも常に「F列」に固定だからですね。
一方、行はオートフィルで下方向へコピーした際に一緒に下方向へ参照を変えます。
そのため、行番号は相対参照のまま、となるわけです。
合計範囲の参照は？
最後に「合計範囲」の参照です。以下のように指定しました。
C$5:C$19
合計範囲(A合計はC列、B合計はD列)は、オートフィルで1列右にコピーする際に、一緒に参照も動いていいですよね。
一方、合計範囲の行は常に固定されていなければなりません。
そのため、列は相対参照、行は絶対参照となります。
2列を同時にオートフィル
「セルH5」に「sumif関数」をセットして、「名称あ」の合計が以下のように出ました。
では、このセルの数式をオートフィルで「名称あ」の「B合計」(セルI6)にコピーしましょう。
「セルH5」にセットした関数と見比べてみてください。
相対参照だけ、きちんと参照先のセルが変わっているのが分かりますね。
それでは、H5とI5を選択して、一気に12行目までオートフィルしてください。
「名称あ」から「名称く」までのA合計とB合計がすべて計算できました。
全体件数とA・Bの件数を求める
最後に「全体件数」と「A件数」、「B件数」を求めます。
これ、ちょっと問題の意図が分かりづらいかもしれません。
「count関数」で表の件数を数えればいいだけなのでは？
と思うでしょう。
ただ15行目の「名称か」のAのデータが空白となっていますね。
Aの数値が0ではなく、「空白」というのはおそらく「この行のAデータは、件数としては存在していない」と捉えた方がいいでしょう。
そして最も効率よく回答しなければならない、という点も踏まえます。
「全体件数」を求めた後に「A件数」と「B件数」にオートフィルで数式をコピーする方法が最も効率が良くなります。
しかし15行目の「名称か」のデータにおいて、Aの件数はない・・。
つまり空白を除外して、「全体件数」、「A件数」、「B件数」のそれぞれの個数(件数)を数える関数を使わなければならない、という意味になります。
そこで使用するのが「counta関数」ですね。
この関数は、データ範囲の中で空白のセルの個数を無視します。
まずは、「全体件数」を数えてみましょう。
この表の全体の件数をあらかじめ「空白があった場合にそれを無視する」数え方で数えます。
「counta関数」の引数には、B列の5行目から19行目までを指定します。
この列には空白がないので「15」という数値が返ります。
では、オートフィルで「A件数」、「B件数」にコピーしてみましょう。
「セルG15」をオートフィルで「セルH15」、「セルI15」とコピーすると、参照範囲も合わせて「B5:B19」から「C5:C19」、「D5:D19」へと移ります。
あらかじめ「counta関数」で計算しているので、「A件数」は下のように空白以外の件数が返ります。
「空白がある」前提で、その空白を無視して個数を数える必要があるなら、「count関数」ではなく「counta関数」を使用しなければならない。
この問題では、それが理解できているかどうかを問われているわけですね。
問題2まとめ
今回もすべて良問でしたね(笑)
では簡単におさらいをしておきたいと思います。
- 条件付きで個数(件数)を調べる「countif関数」を使えているか
- オートフィルを前提にし、引数の指定をセル番地で指定できているか
- 条件付きの合計を計算する「sumif関数」を使えているか
- 複数行や複数列のオートフィルを前提にし、相対参照と絶対参照の使い分けが正しくできているか
- 空白データを無視する「counta関数」を使えているか
実務ではこのあたりの関数は非常に良く使いますので、使いこなしたいところですね。
次回は、問題3に進んでいきます。