せっかくExcelを使うなら、表を作ったり、並べ替えをしたりといった使い方だけではもったいないと思いませんか。関数を利用すれば、手動で行っていた作業を自動化できます。今回は「MAX」「INDIRECT」「COUNTIFS」という3つの関数を使い、アンケートの調査結果を分析する別表を作ってみましょう。
例として、Googleフォームを使ってアンケートを行い、回答をExcelに貼り付けてみます。アンケートの回答は毎日増えていくので、確認時点までに何件（何行）の回答が集まっているかを知るには、いちいち目で調べないと、集計範囲がわかりません。これでは不便です。
日々追加されるデータについて、今までに入力されたデータの最終行番号を取得できれば、作業を自動化できます。
目次
関数「MAX」を使い、データの最終行番号を調べる
「MAX」は引数リストに含まれる数値の中で最大値を返す関数です。日々追加されるようなデータについては、この関数を使い、今までに入力されたデータの最終行番号を取得すれば、作業を自動化できます。
まず最初に式を示します。
＝MAX(IF(A:A<>””,1,0)*ROW(A:A))
以下で詳しく解説していきます。
この数式はいくつかのパーツから成り立っています。
①「IF(A:A<>””,1,0)」の部分は、A列の全各セルについて、空でなければ1を、そうでなければ0を返します。ちなみに、A:Aは列全体を、””は空のセルを示します。<>は不一致を確認する演算子です。
②「ROW(A:A)」部分はA列のすべてのセルの行番号を返す関数です。
①*②の結果は、A列が”空”でない限り、行番号を返すことになります。
ここに「MAX」を加えてみましょう。
MAX(①*②)とすると、①*②で返されてきた行番号の中で最も大きい数値ひとつを返します。
つまり、このケースでは「最終行」を示してくれるというわけです。
▲G列に「最終行」というセルを作り、その下に「＝MAX(IF(A:A<>””,1,0)*ROW(A:A))」を入力すると、最終行の番号を表示する
COUNTIFS関数で該当データ数をカウントする
「COUNTIFS」は条件に一致するデータを数える関数です。データが記録された範囲がわかると、その中に含まれる文字列や数値をカウントできるようになります。たとえば、COUNTIFS関数を使うと「とてもよい」のカウントだけでなく「男性」かつ「とてもよい」といった、いわゆるクロス集計が可能になります。
「男性で「とてもよい」を選択した数」を出すには、こんな式を入力します。
＝COUNTIFS(D2:D120,”男性”,B2:B120,”とてもよい”)
以下で詳しく解説しましょう。
「D2:D120」部分は性別の回答が記された範囲（列記号Dと最初の行番2：列記号Dと最語の行番120、つまりD2からD120までの範囲)です。数字の部分は、手入力していますが、この問題はあとで解決します。
次に「”男性”」の部分ですが、条件が文字列の場合は””（ダブルクォーテーション）でくくります。「B2:B120」の部分は接客評価の回答が記された範囲（列記号Bと最初の行番2：列記号Bと最語の行番120)です。条件である「とてもよい」も「男性」の場合と同様””でくくります。
「性別と接客の関係」を数値化する表を作り、左上のセルに上記の数式を入力すると、「男性」と「とてもよい」がクロスする数字を導き出すことができます。同様にしてほかのセルにも計算式を入力しましょう。
▲I列からN列にかけて「性別と接客の関係」を数値化する表を作り、「＝COUNTIFS(D2:D120,”男性”,B2:B120,”とてもよい”)」という数式で「男性」「とてもよい」がクロスする数字を導き出す
INDIRECT関数を使い最データ範囲を自動で変更する
「性別と接客の関係」を示す表で、自動化できない部分がありました。最新の最終行を手入力する部分です。この部分を自動化するために、セルの参照を文字列で指定するINDIRECT関数を使います。INDIRECT関数は主に別の関数の中で使われます。
=COUNTIFS(D2:D120,I2,B2:B120,J1)
という式は「＝COUNTIFS(D2:D120,”男性”,B2:B120,”とてもよい”」の文字列”男性””をI2、とてもよい”の部分をJ1と、セル番地の指定であらわしたものです。
ただし、「D2:D120」と「B2:B120」の最終行を指定する「120」の部分は手入力しています。この部分をG列の「最終行」が入ったG2セルから代入できれば、範囲を自動的に設定できます。
「＝”D2:D”＆”120”」を最終行から参照して相対参照で記載すると「=”D2:D”＆G2」になり、絶対参照で記載すると「＝”D2:D”&$G$2」になります。（絶対参照で記載する場合は$を付けます）
ですので、INDIRECT関数を使い
=COUNTIFS(INDIRECT(“D2:D”&$G$2),$I3,INDIRECT(“B2:B”&$G$2),J$1)と書くことができます。
この式をオートフィルで縦方向、横方向に伸ばすことで、「性別と接客の関係」の表を完成させましょう。以上で集計表の完成です。
▲「=COUNTIFS(INDIRECT(“D2:D”&$G$2),$I3,INDIRECT(“B2:B”&$G$2),J$1)」と入力し、この式をオートフィルで縦方向、横方向に伸ばして表を完成させる
この集計表では、B列「接客」とD列「性別」を組み合わせて集計していますが、他の列を指定して、「性別」×「年代」、「接客」×「品揃え」などさまざまな集計表を作ることができます。
以上、「MAX」「INDIRECT」「COUNTIFS」という3つの関数を組み合わせて、アンケートの集計表を自動化する数式を作ってみました。アンケートの集計はよくある作業だけに自動化できれば、効率化の効果が高いと思われます。
ぜひ活用してみてください。
インターネットを最大限楽しむには「ビッグローブ光」の光回線がオススメ！
「インターネット回線が遅い」
「インターネット料金が高い」
などのお悩みはありませんか？
安定した速度とリーズナブルな光回線なら、
IPv6接続(IPoE方式)*1で最大10Gbps*2の「ビッグローブ光」がオススメです。
「ビッグローブ光」なら他社の光回線（フレッツ光・コラボ光）をお使いでもお乗り換えはかんたんです。工事費実質無料、おトクな特典もご用意しています。
*1 IPv6に対応していないサービス/サイトはIPv4接続となります。
*2 最大通信速度は光回線タイプによって異なります。最大通信速度はお客さまのご利用機器、宅内配線、回線の混雑状況などにより低下します。
光回線についてもっと詳しく知りたいという方は、
以下の記事も併せてご覧ください。
