Excel で 時短。①

〇 Excelで担当者別の営業額を自動集計、UNIQUE関数とSUMIF関数の楽々コンボ。

　値が重複しているExcelデータから一意の一覧リストを作ってくれるUNIQUE関数はとても便利な関数だ。今回はこの関数にSUMIF関数を組み合わせ、担当者別の営業額を自動集計する、さらに便利な表を作成したい。

関連記事：ExcelでUNIQUE関数、重複データからダブリを取り除いた一覧リストを瞬時に作る

UNIQUE関数　「検索／行列」ボタン

＝UNIQUE(配列,［列の比較］,［回数指定］)

一覧または範囲内の一意の値を一覧で返す。
①配列　一意の行または列を返す範囲または配列を指定する。
②列の比較　省略可能。FALSEまたは省略で行同士を比較して一意の行を返す。TRUEで、列同士を比較して一意の列を返す。
③回数の指定　省略可能。FALSEまたは省略で配列から個別の行または列をすべて返す。TRUEで1回だけ出現する行または列を配列から返す。

SUMIF関数　「数学／三角」ボタン

＝SUMIF(範囲, 検索条件,［合計範囲］)

検索条件に一致する値の合計を返す。
①範囲　検索対象となる値を含む範囲を指定する。
②検索条件　検索条件を指定する。
③合計範囲　和を求める値を含む範囲を指定する。合計範囲が①と同じ場合は省略可能。

必ずテーブルと組み合わせる。

　まず確認しておきたいのは、UNIQUE関数が対象とする範囲を含む表をテーブルにしておくという点だ。こうしておけば表に新規データを追加しても、UNIQUE関数が対象とする範囲を自動的に拡大できる。

　下図は以前に紹介したその実例で、UNIQUE関数を用いてB2:B11の範囲からE2以下に一意の担当者名を書き出している。E2の数式は、

=UNIQUE(テーブル1[担当者])

である。

　表はテーブル表示のため、参照形式は「(テーブル1[担当者])」のように、構造化参照になった。ここでの「テーブル1」とはテーブルに設定したA1:C11に当たる。テーブルの番号は環境によって変わるので、仮に「テーブル24」だとしても気にしない。また「［担当者］」は、テーブルを設定した際についた列範囲の名前だ。該当する列範囲は「担当者」のB2:B11だ。

画1、B2:B11の範囲からE2以下に一意の担当者名を書き出した。E2の数式は「=UNIQUE(テーブル1[担当者])」になっている。

　続いてB12に新たな担当者を追加する。するとE2以下の一覧リストにもその担当者名を自動的に追加できる。

画2、12行目にデータを追加すると、担当者の一覧リストに新しい担当者の名前を自動的に追加できた。

　今回はこの表のF列に各担当者の営業額の合計を算出する。もちろん利用するのはSUMIF関数だ。それほど難しくないので、セルに直接数式を打ち込もう。F1に「営業額合計」と見出しをつけ、F2に「=SUMIF(」と入力した後、次の引数を設定する。

範囲　テーブル1[担当者]
検索条件　E2#
合計範囲　テーブル1[営業額]

　「=SUMIF(」に続けてB2:B12を選択すると自動的に「テーブル1［担当者］」と入る。さらに、「,（カンマ）」を入力して「検索条件」にはE2:E7を指定する。すると自動的に「E2#」と入る。「#」はスピル範囲演算子で、UNIQUE関数が書き出した一覧リストの範囲を示している。

　続いて「,（カンマ）」を入力した後C2:C12を選ぶ。すると今度は「テーブル1［営業額］」と入る。最後に「)」と「カッコ閉じる」を入力する。以上で数式は

=SUMIF(テーブル1[担当者],E2#,テーブル1[営業額])

になった。では、［Enter］キーを押して数式を確定しよう。はい、瞬時に担当者ごとの営業額合計を計算できた。これはかなり快適だ。

画3、F2の数式を「=SUMIF(テーブル1[担当者],E2#,テーブル1[営業額])」とした。［Enter］キーを押すと担当者ごとの営業額合計を計算できた。F列の書式はあらかじめ「￥」マーク付きに設定した。

データを追加したら営業額合計も自動的に変化。

　続いて表に新たなデータを追加してみよう。A13に「2月1日」、B13に「和田敦」、C13に「￥22,000」と入力する。すると、最初は「￥128,500」だった和田敦さんの「営業額合計」が「￥150,500」になった。

画4、A13:C13に新たなデータを入力した。B13の「和田敦」さんは既存の担当者だからE2:E7に変化はない。C13には「￥22,000」と入力したため、和田敦さんの「営業額合計」が「￥150,500」になった。

　最後にもう一つ、今度は新しい担当者を追加してみよう。A14に「2月1日」、B14に「山本健一」、C14に「￥55,000」と入力して［Enter］キーを押す。はい、一覧側の表に「山本健一」さんを自動的に追加でき、「営業額合計」も正しく示せた。この表、実務にも十分使えると思う。

画5、A14:C14に新たなデータを追加した。「山本健一」は新たな担当者のため、担当者の一覧リストに山本健一さんが自動的に追加表示された。併せて山本健一さんの「営業額合計」も自動的に表示できた。