単一の検索条件のみを使用して、並べ替えられたデータの列から結果を出力する必要があります。同じ条件で複数の出現がある場合があります。LOOKUP は最初の出現のみを検索します。一致するセルに J8:J581 を入力し、表示される対応するデータは N8:N581 です。
J K L M N
bob RED
bob BLUE
Bob Green
Sue yellow
Sue white
fred grey
pete brown
。
input=bob
output= bob RED
BLUE
Green
答え1
上記のサンプルのような行を返すだけの場合は、Excel テーブルを使用します。
- データを選択してください
- テーブルを作成する
Insert>Tables>Table（「テーブルにヘッダーがある」ボックスをチェック） - テーブルが作成されたら、フィルター ボタン (テーブル ヘッダー行の下矢印) を選択し、フィルター値 (たとえば、サンプルでは Bob) を選択するだけで、"Bob" 行のみが返されます。
以前の画像はこちらです:
以降：
編集： 追加情報に基づいて、データ テーブルに基づくピボット テーブルの追加を検討します。これにより、情報の「アウトライン」ビューを作成でき、どのキャンプ場に ID が 1 つだけあるのか、複数の ID があるのかをよりわかりやすく確認できます。次のようになります。
また、@pnuts の注記によると、Excel ではドロップダウン (つまりフィルター) リストの項目は 10,000 個に制限されていますが、テーブル メンバーの制限は文書化されていません。10,000 行のテーブルがあったこともあります。
編集2:重複する値だけを簡単に見つけたい場合には、ピボット テーブルが最適です。
- 初期データ テーブルに「カウンター」行を追加します。
=IF([@Name]=D1,F1+1,1)これにより、同じ名前を持つ行の数が返されます。 Insert>Tables>Pivot Tableデータからピボット テーブルを作成します。- ピボットテーブルを次のようにフォーマットします。
- 行ラベル =
Name、Code、Counter、およびすべての値の小計と総計をオフにします。 - フィルター カウンター = 1 をクリアすると、複数の値を持つ名前のみが表示されます。
- フィルター名 = 興味のある特定のキャンプ場名。
- 行ラベル =
答え2
入力セルが A1 で、出力セルが名前の場合は A3、出力範囲の場合は B3:B.. であるとします (B 範囲の終わりは、表示されると予想される結果の最大数まで下がります)。
A3 には、式を入力します=A1。
B3には次の式を入れます
=IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")
これは配列数式なので、Control- Shift-Enterキーの組み合わせで入力する必要があります。その後、出力範囲の一番下までコピーできます。
数式は、最大 99 行の入力リスト用にハードコードされていることに注意してください。この長さは、必要に応じて変更できます。列全体 (J:J および N:N) を参照することは可能ですが、パフォーマンスが低下するため、避けた方がよいでしょう。
式の仕組み
内側から外側に向かって作業を進めると、数式はまず、検索を実行する名前 (セル A3) を名前の完全なリスト (範囲 J1:J99 内の最大 99 の名前) と比較します。この比較は、以下に示す関数のブレークアウトの 6 行目に示されています。
その比較の結果は、一致の場合は True 値、不一致の場合は False 値を含む配列です (例: {False、False、False、False、True、True、False、... など)。
次に、その配列と、名前のリストの「行番号」と考えられる配列（{1、2、3、4、5、6、... 99}）との比較が行われます。この比較は、数式チャートの 6 行目から 8 行目の IF ステートメントによって行われます。
比較は要素ごとに行われます。名前比較配列の要素が True に等しい場合、IF は対応する行番号を返します。要素が False に等しい場合、IF は FALSE を返します。上記の 2 つの例の配列を使用すると、IF ステートメントの結果は {False、False、False、False、4、5、False、...} になります。
続けて、SMALL 関数 (関数アウトラインの 8 行目から開始) を使用して、IF からこの新しい配列の k 番目に小さい要素を取得します。この場合の「k」は式 ROWS($N$1:$N1) によって提供され、式全体が行 1 から行 99 までコピーされると、1 から 99 までカウントアップされます (ROWS($N$1:$N1) = 1、ROWS($N$1:$N2) = 2 など)。
したがって、SMALLはまずIFによって生成された配列の最小の要素を見つけます。無視するFalse である要素。言い換えると、比較対象の名前が名前の参照リスト内の名前と一致する最初の行番号を返します。この例では、下の表の 6 列目に示すように、それは番号 4 です。
ラップアップ ステップでは、ルックアップ値に対して INDEX を使用して、計算された行番号に対応する要素を取得します。この場合、質問の色リストの例の 4 番目の項目は「黄色」です。(IFERROR により、数式で一致が見つからない場合に空白が表示されます。)
これは、完全な数式の最初のコピーによって生成された結果です。次のセルにコピーされると、計算で変更されるのは SMALL 関数の「k」の値のみで、これは 2 に進みます。また、見つかった 2 番目に小さい行番号は 5 で、値「white」が生成されます。
