☰

Excelでシート1のデータからシート3のデータを検索する必要がある

tag-icon • tag-icon microsoft-excel worksheet-function
Excelでシート1のデータからシート3のデータを検索する必要がある

複数のシートを含む Excel ブックがあります。列'SheetA'!W:Wには、行の列ヘッダー内にあるテキストがリストされます'Sheet3'!1:1。たとえば、SheetA'!W42「B16」が含まれます。セルにはSheet3'!CB1ヘッダー テキスト「B16: サンプル 40」があるため、これがターゲット列になります。

例: サンプル'SheetA'!W42

ここに画像の説明を入力してください

には、'SheetA'!CD42に配置される必要のあるサンプル番号を計算する数式があります'Sheet3'!A:A。以下のサンプルでは、CD42​​ に計算された値「30」が表示され、これは に配置されます'Sheet3'!A32。

例: 列ヘッダー (B16) と対象のサンプル番号を含む行のヘッダーと交差を表示する Sheet3 のサンプル データ:

ここに画像の説明を入力してください

目標は、検索した列と行が交差する Sheet3 のセルの内容を取得し、それを SheetA のセルに表示することです。提供されているサンプルでは、'Sheet3'!CB:CB​​交差する行 32 と sheetA に値「0.1950581843」が表示されます。

これを実現する方法を知っている人はいますか?

答え1

上記のデータに基づいて、いくつかの仮定を立ててみます。これらすべてが当てはまる場合、HLOOKUP問題を簡単に解決できます。そうでない場合は、より複雑な関数のチェーンが必要になる場合があります。

前提:

  1. 関連するすべてのヘッダーテキスト'Sheet3'!1:1は絶対にユニークその行内。
  2. 関連するすべてのヘッダー テキストは'Sheet3'!1:1"[X]: [Y]" という形式です。
    • [X]は にある値です'SheetA'!W:W。
    • 'SheetA'!X:X[Y]は[X]と同じ行にある値です。
  3. のすべての可能な値は'SheetA'!CD:CDで検索可能であり'Sheet3'!A:A、取得対象のデータと同じ行に配置されます。
    • これを入力したのは、Sheet3 にこれらの値が重複しているように見えることに気付き、'SheetA'!CD:CD別の列と照合することを意図した値を で検索する条件がないことを確認したかったからです (そのため、 で照合すると不正確な結果が生成される場合があります'Sheet3'!A:A)。
    • 繰り返しが読みやすさのみを目的としている場合は、代わりに「ウィンドウの固定」機能を使用することをお勧めします。
  4. のすべての関連値は'Sheet3!A:A'完全に連続しており、 の「1」から始まり'Sheet3'!A3、その列内で重複することはありません。いつも昇順に並べます。

上記の仮定に基づいて、シナリオを大まかに表すサンプル データを使用して独自のシートを作成しました。

以下は私の「Sheet3」の部分的なスクリーンショットです。

ここに画像の説明を入力してください

そして私の「SheetA」の部分的なショット。

ここに画像の説明を入力してください

類似品のリスト:

  • シート3
    • 私の A:A = あなたの A:A、BY:BY:、CA:CA:、...
    • 私の B:B、C:C、D:D、... = あなたの B:B、BZ:BZ、CB:CB、...
  • シートA
    • 私の A:A = あなたの W:W
    • 私の B:B = あなたの X:X
    • 私の C:C = あなたの CD:CD
    • My D:D = 見つかったデータをドロップする任意の列。

2 番目のスクリーンショットでわかるように、D2 の式は次のとおりです。

=HLOOKUP(CONCATENATE(A2,": ",B2),Sheet3!A:Y,C2+2,FALSE)

式のステップバイステップのウォークスルー:

HLOOKUPセル範囲を水平方向に調べて値を探し、相対的な行位置に基づいて同じ列のセルの値を返します。4 つの引数を取りますが、そのうち 3 つは必須です。参照値、テーブル・アレイ、行インデックス番号、[範囲検索]これは、Sheet3 で必要なデータを検索し、それを SheetA のセルに取り込むという作業を実行する数式です。

  • 参照値最初に見つけたい値ですHLOOKUP。この値しなければならない最前列に並ぶテーブル・アレイは、検索対象となる唯一の行であるためです。HLOOKUPまた、最初の一致のみが返されるため、これらの値も一意である必要があります。ここでは、を使用してCONCATENATE検索文字列を構築しています。
    • 連結さまざまな文字列と値を 1 つの文字列にまとめることができます。結果の文字列に配置する順序に従って並べられた一連の引数を受け入れます。
      • A2は の最初の引数ですCONCATENATE。文字列の最初の部分は A2 の「サンプル名」になります。
      • ": "は の 2 番目の引数ですCONCATENATE。これにより、 の値の形式に合わせて、コロンとスペースが文字列内に配置されます'Sheet3'!1:1。
      • B2は の最後の引数ですCONCATENATE。B2 から「サンプル ID」を取得し、 のヘッダーに使用される構文を完成させます'Sheet3'!1:1。
  • テーブル・アレイは、操作するセル範囲への参照ですHLOOKUP。最初の行には、次の内容が含まれる必要があります。参照値この範囲には、行インデックス番号。
    • シート3!A:Yは、Sheet3のA列からY列（Sheet3に入力されている唯一の列）のすべてのセルへの参照です。これにより、後で新しい行に追加されたデータも検索範囲に含まれるようになります。データが行ではなく新しい列に追加される場合は、参照を使用しますSheet3!1:32（現在、32はSheet3に入力されている最後の行です）。新しい列によってデータが追加される可能性がある場合は、そしてSheet3!1:1048576新しい行を追加する場合は、または を使用してシート全体を参照しますSheet3!A:XFD。(注: 「シート全体」参照は Excel 2013 で有効です。以前のバージョンでは行/列の制限が小さい場合があります。必要に 応じて1048576または を調整してください。)XFD
  • 行インデックス番号は、最上行からの相対的な行位置を表す正の整数です。テーブル・アレイ. これはHLOOKUP、一致する列からどのセルを返すかをHLOOKUP指定します。参照値一番上の行のテーブル・アレイ、 そして行インデックス番号負の値は使えないので、HLOOKUP（少なくとも単独では）上のセルの情報を返すことはできない。参照値。
    • C2+2- 内のすべての値は'Sheet3'!A:A完全に連続しており、整数がスキップされることはなく、常に昇順で並べられるため、それらの値 ( にも表されます'SheetA'!C:C) を検索対象のデータの行番号の指標として使用できます。 は、+2Sheet3 の行 3 で番号が「1」から始まるという事実を説明するためにあります。
  • [範囲検索]は のオプション引数ですHLOOKUP。オプションは TRUE または FALSE で、近似一致を有効にするか、完全一致のみを許可するかを指定します。この引数を省略すると、Excel はデフォルトで TRUE (近似一致) に設定され、シートが特定の方法で並べ替えられていない場合など、望ましくない動作になることがあります。そのため、次のように指定します。間違いHLOOKUPここでは、完全一致のみを取得すること を確認します。

上記をシートのレイアウトに適応させると、セルに必要な数式は次のようになると思います'SheetA'!CE42(そこにデータをドロップすると仮定)。

=HLOOKUP(CONCATENATE(W42,": ",X42),Sheet3!A:CB,CD42+2,FALSE)

シート3のデータが列CBよりも右にずれている場合や、データがさらに列に追加される場合は、調整する必要があります。テーブル・アレイそれに応じて。

関連情報