この記事では、Microsoft365のExcelで使えるUNIQUE関数と、TRANSPOSE関数やSUMIFS関数、SORT関数を組み合わせてピボットテーブルのような表をつくる方法などを紹介します。
→記事で紹介した例題をそのまま掲載した［練習用Excelシート］
UNIQUE関数とは
UNIQUE関数の書式
UNIQUE（ユニーク）関数は、指定した範囲や配列の中から重複しないデータを返してリストをつくる関数です。
第1引数に「範囲・配列」を、第2引数に「省略またはFALSE」か「TRUE」で示す「列の比較」を、第3引数に「省略またはFALSE」か「TRUE」で示す「回数指定」を指定します。第2引数、第3引数は省略可能です。
| 書式 |
UNIQUE( 範囲・配列 [, 列の比較 ] [, 回数指定 ] ) |
第2引数「列の比較」
第2引数「列の比較」は、「省略」「FALSE」「TRUE」のいずれかで指定します。
取り出したいデータが行方向（縦）に並んでいるときには、省略するか、「FALSE」を入力します。
取り出したいデータが列方向（横）に並んでいるときには、「TRUE」を入力します。
第3引数「回数指定」
第3引数「回数指定」は、「省略」「FALSE」「TRUE」のいずれかで指定します。
範囲または配列にある重複しないすべてのデータを取り出したいときには、省略するか、「FALSE」を入力します。
1回だけ出現するデータを取り出したいときには、「TRUE」を入力します。
UNIQUE関数とSUMIF関数の組み合わせ
UNIQUE関数で重複のないリストを作成し、SUMIF関数で集計する例を紹介します。
「#（スピル範囲演算子）」の使い方も紹介します。
今、A1セル～C9セルのようなデータがあるとします。
このとき、氏名ごとの売上を集計してみましょう。
UNIQUE関数を入力する
まずは、UNIQUE関数で重複のない氏名のリストを作成します。
E2セルに、=UNIQUE(B2:B9) と入力します。すると、重複のない氏名のリストができます。
SUMIF関数を入力する
次は、F2セルにSUMIF関数を入力します。
F2セルには、=SUMIF(B2:B9,E2#,C2:C9) と入力します。
すると、F6セルまでスピル機能で式が入力され、売上が集計されます。
第2引数の「E2#」は、スピルの機能で求められた範囲（E2:E6は=UNIQUE(B2:B9) で求められたもの）を指定しています。
なお、「#」 はスピル範囲演算子と呼ばれています。
ここで、あとの説明を簡単にするために、A1セル～C9セルの表を「テーブル」に変換します。A1セル～C9セルを選んで、［Ctrl ＋ T］と押すとテーブルに変換されます。
データを追加してみる
ここで、10行目に新しいデータを追加します。
すると、E7セルにUNIQUE関数、F7セルにSUMIF関数のスピルが伸びました。
表をテーブルにしておき、SUMIF関数の第2引数に「E2#」のようなスピル範囲演算子を使っておくと、スピル機能により数式が伸縮します。
表をテーブルにしておくと、データを追加した際に、数式の範囲が自動で伸びてくれるのでおすすめです。
複数の列から重複しないデータを抽出する
UNIQUE関数の第1引数「範囲・配列」には、複数の列を指定することもできます。
今、A列に「商品名」が、B列に「都道府県名」が入っている表があったとします。
このとき、E2セルに、=UNIQUE(A2:B10) と複数列を指定すると、「商品名」と「都道府県名」が重ならないデータが抽出できます。
TRANSPOSE関数で表頭をつくる
さて今度は、UNIQUE関数と、行と列の位置を入れ替える関数であるTRANSPOSE関数を組み合わせて、表頭部分をつくる方法を紹介します。
TRANSPOSE関数の書式
TRANSPOSE（トランスポーズ）関数は、指定した範囲や配列の行と列の位置を入れ替える関数です。第1引数に「範囲・配列」を指定します。
| 書式 |
TRANSPOSE( 範囲・配列 ) |
UNIQUE関数を入力する
まずは、UNIQUE関数を入力します。ここでは、「商品名」を表頭部分にしますので、F1セルに、=UNIQUE(A2:A10) と入力します。すると、「商品名」が縦方向に並びます。これをTRANSPOSE関数で横方向に変えます。
TRANSPOSE関数を入力する
変え方は、今入力したUNIQUE関数の式をTRANSPOSE関数で囲むだけです。
F1セルに、=TRANSPOSE(UNIQUE(A2:A10)) と入力します。これで、「商品名」がF1セル、G1セル、H1セルと横方向に並びました。表頭部分の完成です。
UNIQUE関数で表側をつくる
今度は、E列に「都道府県名」を並べた表側をつくります。これは、UNIQUE関数を使えば簡単です。
E2セルに、=UNIQUE(B2:B10) と入力します。これで「都道府県名」の表側ができました。
なお、「あいうえお順」などに並べ替えたいときは、SORT関数を使って、=SORT(UNIQUE(B2:B10)) と入力します。
SUMIFS関数で集計する
表頭と表側の部分ができあがり、表の形になってきました。最後は、SUMIFS関数を使って完成させます。
SUMIFS関数の第1引数「合計対象範囲」には、C2セル～C10セルを指定します。
第2引数「条件範囲1」には、A2セル～A20セルを、第3引数「条件1」には、「F1#」とスピル範囲演算子を使います。
第4引数「条件範囲2」には、B2セル～B10セルを、第5引数「条件2」には、「E2#」とスピル範囲演算子を使います。
したがって、F2セルは、=SUMIFS(C2:C10,A2:A10,F1#,B2:B10,E2#) という式になります。これで、表が完成します。
まとめ
ここで紹介した表の作り方は、ピボットテーブルを使うともっと簡単にできますが、関数を使ったこの方法は「あとから見た人が解読しやすい」というメリットがあります。
UNIQUE関数で表側を、UNIQUE関数とTRANSPOSE関数を組み合わせて表頭をつくり、SUMIFS関数で集計するという方法は実務に応用できるはずですので、参考にしていただければと思います。
おすすめ記事
- エラー値や非表示の行を無視して集計できるAGGREGATEの使い方［Excelシート付］
- SWITCH（スイッチ）関数とIFS関数の違い［Excelシート付］
- すぐできる！重複データ（複数条件も）をチェック・抽出するExcelテクニック