リストからユニークなアイテムを抽出する

{= INDEX (list, MATCH (0, COUNTIF (uniques,list),0))}

リストまたは列から一意の値のみを抽出するには、INDEX、MATCH、およびCOUNTIFに基づく配列数式を使用できます。示されている例では、コピーされたD5の式は次のとおりです。

 
{= INDEX (list, MATCH (0, COUNTIF ($D:D4,list),0))}

ここで、「リスト」は 名前付き範囲 B5：B11。

注：これは 配列式 Ctrl + Shift + Enterを使用して入力する必要があります。

数式でセル値を使用するExcel

この式の中核は、INDEXを使用した基本的なルックアップです。

 
= INDEX (list,row)

つまり、INDEXにリストと行番号を指定すると、INDEXは値を取得して一意のリストに追加します。

大変な作業は、ROW番号を計算してINDEXを与えることです。これにより、一意の値のみを取得できます。これはMATCHとCOUNTIFを使用して実行され、主なトリックは次のとおりです。

 
 COUNTIF ($D:D4,list)

ここで、COUNTIFは、一意のリストにすでに含まれているアイテムがマスターリストに表示される回数をカウントします。 参照の拡張 範囲については、$ D $ 4：D4。

展開する参照は片側は絶対、反対側は相対的。この場合、数式がコピーされると、参照が展開され、一意のリストにさらに多くの行が含まれます。

注意してくださいリファレンスD4から始まり、1行 その上 一意のリストの最初の一意のエントリ。これは意図的なものです。一意のリストで*すでに*アイテムをカウントしたいので、循環参照を作成せずに現在のセルを含めることはできません。したがって、上の行から始めます。

重要：一意のリストの見出しがマスターリストに表示されていないことを確認してください。

COUNTIFの基準には、マスターリスト自体を使用しています。複数の基準が与えられた場合、COUNTIFは複数の結果を返します 配列 。新しい行ごとに、次のような異なる配列があります。

 
{0000000} // row 5 {1000100} // row 6 {1100101} // row 7 {1111101} // row 8

注：COUNTIFは、「OR」関係を持つ複数の基準を処理します（つまり、COUNTIF（range、{'red'、 'blue'、 'green'}）は、赤、青、または緑をカウントします。

選択したセル範囲に名前を付ける

これで、位置（行番号）を見つけるために必要な配列ができました。このために、完全一致に設定されたMATCHを使用して、ゼロ値を見つけます。上記のCOUNTIFで作成された配列をMATCHに入れると、次のようになります。

 
 MATCH (0,{0000000},0) // 1 (Joe)  MATCH (0,{1000100},0) // 2 (Bob)  MATCH (0,{1100101},0) // 3 (Sue)  MATCH (0,{1111101},0) // 6 (Aya)

MATCHは、ゼロのカウントを探すことによってアイテムを見つけます（つまり、一意のリストにまだ表示されていないアイテムを探します）。重複がある場合、MATCHは常に最初の一致を返すため、これは機能します。

最後に、位置は行番号としてINDEXに入力され、INDEXはその位置の名前を返します。

LOOKUPを使用した非アレイバージョン

柔軟なLOOKUP関数を使用して、配列以外の数式を作成し、一意のアイテムを抽出できます。

 
= LOOKUP (2,1/( COUNTIF ($D:D4,list)=0),list)

式の構成は上記のINDEXMATCH式に似ていますが、LOOKUPは配列操作をネイティブに処理できます。

• COUNTIFは、の「リスト」から各値のカウントを返します。 範囲の拡大 $ D $ 4：D4
• ゼロと比較すると、TRUE値とFALSE値の配列が作成されます
• 数値1は配列で除算され、1と＃DIV / 0エラーの配列が作成されます。
• この配列は、LOOKUP内のlookup_vectorになります
• ルックアップ値2は、lookup_vectorのどの値よりも大きい
• LOOKUPは、ルックアップ配列の最後の非エラー値と一致します
• LOOKUPは、対応する値をresult_vectorに返します。これは、名前付き範囲 'list'です。

一度だけ現れるアイテムを抽出する

上記のLOOKUP式は、次のように簡単に拡張できます。 ブール論理 。ソースデータに1回だけ表示される一意のアイテムのリストを抽出するには、次のような式を使用できます。

 
= LOOKUP (2,1/(( COUNTIF ($D:D4,list)=0)*( COUNTIF (list,list)=1)),list)

唯一の追加は、2番目のCOUNTIF式です。

セルに単語が含まれている場合

 
 COUNTIF (list,list)=1

ここで、COUNTIFは次のようなアイテムカウントの配列を返します。

 
{2222212}

これは1と比較され、TRUE / FALSE値の配列になります。

 
{FALSEFALSEFALSEFALSEFALSETRUEFALSE}

これは「フィルター」として機能し、ソースデータで1回だけ発生するアイテムに出力を制限します。

の Excel 365 、 NS UNIQUE機能 一意の値を抽出するための最良の方法です。