2
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

UNIQUE関数の代替手段(Office 365やピボットテーブルを使わない)

Abstract

UNIQUE関数はOffice 365でしか使えない。
→Office 2019以下で使えるExcel関数だけを用いた代替方法

// 1行目
=元のリストの一番上

// 1行目以降(配列数式にするため、{}以外を入力し、Ctrl+Shift+Enterで決定すること)
{=IFNA(INDEX(元のリストの絶対参照,MATCH(0,COUNTIF(一番上のセルの絶対参照:1つ上のセル,元のリストの絶対参照),0)),"")}

具体例や詳細は後述。

Introduction

  • UNIQUE関数とは
    Office 365から採用された関数で、Google Spreadsheetなどにも実装されている。
    重複した内容を含むリストから、重複のないリストを作成することが可能。
    以下に例を示す。
元のリスト 重複しているか UNIQUE関数の出力
AAA AAA
BBB BBB
CCC CCC
BBB 重複 DDD
DDD
DDD 重複

以上のように重複しているBBB, DDDが1つずつ表示されるようになる。

この関数は非常に便利だが、前述の通り、Office 365でのみ利用可能で、
Office 2019以前のバージョンでは同様のことを行うためにピボットテーブルを使う必要があった。
ピボットテーブルはグラフや標準誤差を算出したい場合などに不自由なため、
今回は、Excel関数のみでUNIQUE関数の代替手段を示す。

Materials and Methods

Windows版Office 2013以降で動作を確認。

Abstractに示した関数の再掲

// 1行目
=元のリストの一番上

// 1行目以降(配列数式にするため、{}以外を入力し、Ctrl+Shift+Enterで決定すること)
{=IFNA(INDEX(元のリストの絶対参照,MATCH(0,COUNTIF(一番上のセルの絶対参照:1つ上のセル,元のリストの絶対参照),0)),"")}

Results and Dicussion

以下、実際にExcelで入力した結果。
unique2019.png

左(B, C列)に入力されている家計簿から、それぞれの項目を取得し、その合計金額を出した表。
例として、E6の内容を以下にテキストでも示した(Ctrl+Shift+Enterで配列数式として入力)。

{=IFNA(INDEX($B$3:$B$10,MATCH(0,COUNTIF(E$3:E5,"="&$B$3:$B$10),0)),"")}

詳細を解説する。
1行目は必ず重複しないため、そのままリストの内容を入力している。
2行目以降は、まず、COUNTIF関数(配列を返すことが可能)を用いることで、
「対象のセルより上のセル」と重複する値は1, 重複しない値は0に「元のリスト」を変換して配列として取得している。
次に、MATCH関数を用いて、初めて現れる0 (つまり、重複しない一番上の値)が「元のリスト」の何番目か特定している。
最後に、INDEX関数を用い、「元のリスト」の配列からMATCH関数で特定した順番の値を抜き出している。
IFNA関数は重複しない値がそれ以上ない場合に、空文字を返すために利用している。

Conclusion

UNIQUE関数の代替手段としては完璧に動作する。
欠点としては、SORT関数を実装できないため(単体実装は可能だが、上記方法と併用不可)、
フィルターなどの併用が必要になる事が挙げられる。

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
2
Help us understand the problem. What are the problem?