Excel

UNIQUE関数とTRANSPOSE関数を組み合わせて表頭をつくる[Excelシート付]

この記事では、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」を入力します。

UNIQUE関数

 

第3引数「回数指定」

第3引数「回数指定」は、「省略」「FALSE」「TRUE」のいずれかで指定します。
範囲または配列にある重複しないすべてのデータを取り出したいときには、省略するか、「FALSE」を入力します。
1回だけ出現するデータを取り出したいときには、「TRUE」を入力します。

 

UNIQUE関数とSUMIF関数の組み合わせ

UNIQUE関数で重複のないリストを作成し、SUMIF関数で集計する例を紹介します。
#(スピル範囲演算子)」の使い方も紹介します。

今、A1セル~C9セルのようなデータがあるとします。
このとき、氏名ごとの売上を集計してみましょう。

 

UNIQUE関数を入力する

まずは、UNIQUE関数で重複のない氏名のリストを作成します。
E2セルに、=UNIQUE(B2:B9) と入力します。すると、重複のない氏名のリストができます。

UNIQUE関数(氏名リスト)

 

SUMIF関数を入力する

次は、F2セルにSUMIF関数を入力します。
F2セルには、=SUMIF(B2:B9,E2#,C2:C9) と入力します。
すると、F6セルまでスピル機能で式が入力され、売上が集計されます。

 

第2引数の「E2#」は、スピルの機能で求められた範囲(E2:E6は=UNIQUE(B2:B9) で求められたもの)を指定しています。
なお、「#」 はスピル範囲演算子と呼ばれています。

SUMIF関数(売上)

 

ここで、あとの説明を簡単にするために、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関数で横方向に変えます

表頭をつくる(UNIQUE関数)

 

TRANSPOSE関数を入力する

変え方は、今入力したUNIQUE関数の式をTRANSPOSE関数で囲むだけです。
F1セルに、=TRANSPOSE(UNIQUE(A2:A10)) と入力します。これで、「商品名」がF1セル、G1セル、H1セルと横方向に並びました。表頭部分の完成です。

表頭をつくる(TRANSPOSE関数)

 

UNIQUE関数で表側をつくる

今度は、E列に「都道府県名」を並べた表側をつくります。これは、UNIQUE関数を使えば簡単です。
E2セルに、=UNIQUE(B2:B10) と入力します。これで「都道府県名」の表側ができました。
なお、「あいうえお順」などに並べ替えたいときは、SORT関数を使って、=SORT(UNIQUE(B2:B10)) と入力します。

表側をつくる(UNIQUE関数)

 

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#) という式になります。これで、表が完成します。

表をつくる(SUMIFS関数)

 

まとめ

ここで紹介した表の作り方は、ピボットテーブルを使うともっと簡単にできますが、関数を使ったこの方法は「あとから見た人が解読しやすい」というメリットがあります。

UNIQUE関数で表側を、UNIQUE関数とTRANSPOSE関数を組み合わせて表頭をつくり、SUMIFS関数で集計するという方法は実務に応用できるはずですので、参考にしていただければと思います。

 

おすすめ記事

 

参考文献

-Excel
-, , ,

© 2022 まなびっと Powered by AFFINGER5