いまさら聞けないExcelの使い方講座

【Excel】「○以上△未満」のデータを数えるスマートな方法！関数を使わずに複雑な条件でデータを集計するエクセルテクニック

トップスタジオ

2019年12月16日 06:55

関数を使わずに「○以上△未満」のデータの数を数えたい……！

　年末は、1年間の振り返りや来年の目標設定などのために、データの集計を行う機会が多いですよね。Excelにはデータの集計に役立つ機能がたくさんあり、本連載でもこれまで集計に役立つ機能をいくつか解説してきました。

　次の「顧客別売上記録一覧表」（①）の右側に集計表を作成し、売上合計が「2,000万円以上」「1,000万円以上2,000万円未満」のように、指定した条件に一致する顧客の数を求めるとします。皆さんなら、どのような方法で集計しますか？

　このように「ある条件を満たすデータの数を数える」という場合に使う機能として、COUNTIF関数が思い浮かぶ読者は少なくないのではないでしょうか。例えばこの表の中で売上合計が2,000万円以上のデータの数を数えたい場合、表の中では数値が「千円」単位で表記されているので、「=COUNTIF(B4:B11,">=20000")」（②）という数式を使えば良いですね。

　また、「1,000万円以上2,000万円未満」のように複数の条件がある場合は、COUNTIFS関数を使って「=COUNTIFS(B4:B11,">=10000",B4:B11,"<20000")」（③）という数式でデータの個数を求めることができます。

　ここまでの解説を読んで、すんなりと「そうだね」と思える方は、これらの関数を使い慣れている方ではないでしょうか（COUNTIF関数、COUNTIFS関数について詳しく知りたい方は、過去の記事を参照してください）。でも、これらの関数は結構複雑なので「数式を作るのに時間をかけたくない！」と思う時はありますし、「どうしても関数は苦手……」「もっと簡単に集計する方法はないの？」と思う方も決して少なくはないと思います。

　この例のように、ある条件を満たすデータの数を集計したい時に便利なのがオートフィルターの「数値フィルター」機能です。さらに、表をテーブルに変換して「集計行」を表示させると、データの集計にとても役立ちます。今回は、表をテーブルに変換して「数値フィルター」を使う方法と、「集計行」でいろいろな集計を簡単に行う方法を解説します。

❶テーブルから「数値フィルター」で条件に合致するデータだけを表示させる

　まず、表をテーブルに変換し、「数値フィルター」を使って条件に合致するデータだけを表示させる方法を解説します。表の罫線や塗りつぶしなどの書式を解除した状態から、表にテーブルスタイルを設定してみます。なお、数値フィルターは、表をテーブルに変換しなくても、［オートフィルター］を設定すれば使用できます。本記事では、テーブルの集計行と組み合わせて使うため（ここがポイントになります！）、テーブルに変換しておきます。

　テーブルにしたいセル範囲に含まれるセル（ここではセルA3）（①）を選択した状態で、［ホーム］タブ→［テーブルとして書式設定］（②）をクリックし、表示されるテーブルスタイルの一覧から希望のスタイル（③）を選択します。表をテーブルに変換する場合は、シートの一番上の行からデータを入力するか、テーブルに変換したいセル範囲と表タイトルの間を1行空けると、テーブルに変換するセル範囲の指定がうまくいきます。

　［テーブルとして書式設定］ダイアログボックスが表示されます。［テーブルに変換するデータ範囲を指定してください］欄に、テーブルに変換したいセル範囲が正しく表示されている（④）ことを確認します（正しくない場合は、修正します）。また、ここではセル範囲の一番上の行は見出し行になるので、［先頭行をテーブルの見出しとして使用する］のチェックボックスがONになっていることも確認してください。ダイアログボックスの内容が正しければ、［OK］（⑤）をクリックします。

　指定したセル範囲が、テーブルに変換されました（⑥）。選択したテーブルスタイルが適用され、見出し行の各セルの右端に［▼］のボタン（これをフィルターボタンといいます）が表示されます。

　これで、表をテーブルに変換できたので、さっそく数値フィルターを使ってみましょう。ここでは、「売上合計が1,000万円以上2,000万円未満」のデータだけを抽出してみます。セルB3の「売上合計」の右側に表示されているフィルターボタン（⑦）をクリックし、表示されるメニューの［数値フィルター］（⑧）にマウスポインターを合わせます。すると、右側に数値フィルターでできる抽出方法の一覧が表示されます。今回の例のように「○以上△未満」や「○以上△以下」といった抽出を行いたい時は、この一覧から［指定の範囲内］（⑨）を選択します。

　［オートフィルターオプション］ダイアログボックスが表示されます。ここに、抽出したいデータの条件を入力していきます。「1,000万円以上2,000万円未満」としたい場合、この表では数値をすべて「千円」単位で表示しているため、［売上合計］の入力欄に「10000」（⑩）、「20000」（⑪）と入力します。また、数値の入力欄の右にある「以上」などの選択欄では、「10000」の右は「以上」、「20000」の右は「より小さい」（⑫）を選択します。このダイアログボックスで選択できる条件の一覧に「未満」という選択肢はありませんが、「より小さい」と同じ意味なので、それを選択します。ここまで入力できたら、［OK］（⑬）をクリックします。

　すると、「売上合計」が1,000万円以上2,000万円未満のデータだけが抽出されます（⑭）。特定の範囲に含まれるデータだけを簡単に表示できましたね。

　テーブルでデータが絞り込まれている場合、抽出条件に指定した列の見出しにあるフィルターボタンの形が変わります。抽出を行う前の状態に戻す時は、この形が変わったボタンをクリックして［～からフィルターをクリア］を選択すると、絞り込みを解除できます。

❷テーブルでの集計にとても役立つ「集計行」

　次に、テーブルでのデータ集計にとても役立つ「集計行」を使ってみましょう。先ほどの「顧客別売上記録一覧表」のテーブルを、データの抽出を行っていない状態に戻しておきます。テーブル内のセルをクリックすると表示される［テーブルデザイン］タブ（①）の［テーブルスタイルのオプション］グループにある［集計行］のチェックマークをON（②）にします。すると、テーブルの一番下（ここでは12行目）に「集計」行（③）が表示されます。

　ここで自動的に表示された集計行は、一番右の「前年比」の数値が合計になっていて、何かちょっと変だなと思うかもしれませんが、慌てなくても大丈夫です。この集計行にあるセルはどのセルでもさまざまな集計を自動で行うことができ、どのような形で集計するかは表を使う人が指定できるのです。「集計」行に含まれるセル（ここではセルB12）をクリックすると、セルの右側に［▼］のボタン（④）が表示されます。これをクリックすると、行える集計の一覧が表示されます。例えばここでは［合計］（⑤）を選択してみましょう。

　すると、「売上合計」の列に入力されている数値がセルB12に自動で合計されます（⑥）。SUM関数などを入力しなくても、合計を簡単に求めることができて便利ですよね。次の画像では、その右にある「前年同期」の列の集計行のセルC12でも「合計」を選択し、さらにその右の「前年比」の列の集計行のセルD12では「なし」を選択しています。

　この集計行が便利なのは、データを特定の条件で抽出した場合、その抽出されたデータのみに関して集計を行える点です。例えば、前項に記述した手順で「売上合計が1,000万円以上2,000万円未満」のデータだけを抽出してみると、集計行に表示されている「合計」の値が、抽出されたデータだけの合計の値に自動で変わります（⑦）。

　集計行における集計方法の変更は、一部のデータを抽出した状態でも行えます。「売上合計」の合計が表示されているセルB12をクリックし、右側に表示される［▼］のボタン（⑧）をクリックして、集計方法の一覧から［個数］（⑨）を選択してみましょう。

　すると、データの個数が「2」（⑩）と表示されました。

　これなら、COUNTIF関数やCOUNTIFS関数を使わなくても、直感的な操作で特定の範囲のデータの個数を数えることができますね。2つ程度であれば目視で数えることもできますが、もっとデータの数の多い表では、このように個数を自動で数えてくれる機能はとても役立ちます。

集計が必要な表はテーブルに変換するのがGOOD

　今回は、表をテーブルに変換して、「数値フィルター」を使って特定の範囲のデータを抽出する方法と、「集計行」を挿入していろいろな方法で集計を行う方法を解説しました。テーブルから集計を行うと、クリック操作や直感的に入力できるダイアログボックスの操作だけで、関数を使わずにいろいろな集計を効率よく行うことができます。

　集計作業が多くなるこの時期に、ぜひテーブルを使った集計を業務で試してみてくださいね。