☰

Excel で 2 つのセルを参照して 3 番目のセルを見つけることは可能ですか?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function
Excel で 2 つのセルを参照して 3 番目のセルを見つけることは可能ですか?

かなりの量のデータが入っているスプレッドシートがあります。そのデータの一部を特定のセルに返す必要があります。返す必要のあるデータは、常に「添付コンポーネント」を含むセルの近くにあります。問題は、「添付コンポーネント」セルが複数あることです。たとえば、「パーツ 1」と「パーツ 2」という 2 つのパーツがあり、それぞれのパーツに比較的近い位置に「添付コンポーネント」セクションがあります。これらが配置されているセルも同じではありません。そうでなければ、それらのセルを参照するだけです。現在、1 つのパーツの「添付コンポーネント」付近のデータを返すために使用している数式は次のとおりです。

=IFNA(INDEX(L15:R46,MATCH("Attached Components",M15:M46,0)+2,3),"0")

要約すると、「添付コンポーネント」を参照し、次に「部品番号_」を参照するセルからデータを返す数式が必要です。

「添付コンポーネント」の位置がどのように変化するか、またそれが「部品番号 1」に対してどこに位置するかを示すサンプルを次に示します。

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

これはかなり特殊な問題であり、私の説明があまり明確でないことは承知しています。ご協力に感謝いたします。より具体的な詳細を知りたい場合は、遠慮なくお問い合わせください。

答え1

私は以下の仮定の下でそれを機能させようとしました:

  • 「添付コンポーネント」は常に同じ列にあります。
  • 実際には、各「Material #」の説明を検索しようとしています。

そして、私はこのシートを使って次の作業を行います:

例

これはまさにあなたが必要としているものではないかもしれませんが、あなたのコメントを参考にして私の回答を改善してみたいと思います。

数式を再利用して列内の「添付コンポーネント」がどこにあるかを特定し、2 を追加すると、マテリアルの説明が始まる相対行が示されます。

=MATCH("Attached Components",B1:B32,0)+2

結果は例の「7」のようになります。

次に、説明がある最後の行を特定する必要があります。正しい範囲で検索するには、「添付コンポーネント」が配置されている行に応じて数式を変更する必要があります。MATCH、ADDRESS、CONCATENATE の組み合わせにより、範囲が再作成されます。

MATCH は相対行を返し、ADDRESS は文字列内の行番号と列番号をセル名 (ADDRESS(1,1)="$A$1") に変換し、CONCATENATE は文字列を結合して範囲を作成します。

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

これは、「$C$7:$C$25」のような文字列を返します。つまり、説明列をカバーし、値がある行から 18 行下までカバーします。カバーする行を増やすか減らすには、数式の「+20」を適切な値に変更するだけです。

最後の行を見つけるには、IF と MIN を使用して最初の空のセルを検索するだけです。

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

この数式は配列数式です。そのため、数式の周りに括弧が付いています (括弧は入力しないでください。数式を入力して Ctrl+Shift+Enter キーを押すと表示されます)

INDIRECT は、セル参照に組み込んだ文字列を変換します。ROW は結果として行番号を返します。MIN は返された範囲内の最小値を取得します。末尾の「-1」は、最初の空白行ではなく、最後の説明の行番号を取得するためです。

この例では、この数式は「9」を返します。

これで、最初の説明と最後の説明の行番号 7 から 9 がわかりました。ADDRESS、CONCATENATE、INDIRECT を使用してこれらの番号を自由に組み合わせ、必要な操作を実行できます。ただし、今回は特定のセル参照を使用します。

たとえば、Material # の検索:

VLOOKUPの例

この最後の例では、セルには

E2:

=MATCH("Attached Components",B1:B32,0)+2

F2 (Ctrl+Shift+Enter を使って入力する場合):

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

F7:

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

この方法では、セル E7 にマテリアル番号を入力すると、セル F7 に説明が表示されます。

編集:

コメントに従って、解決策は次のようになります。

より複雑な例を挙げてみましょう。

複雑な例

行のマッチングは、2 つの MATCH 関数のカスケードです。最初の MATCH 関数を使用して部品番号を検索し、次に 2 番目の MATCH 関数を使用して対象のセクションを検索します。

2つの一致する

F3: 探している部分の文字列

F4: 最初の列の「部品番号」を検索する数式。

=MATCH($F$3,A1:A32,0)

F6: 探しているセクションの名前

F7: 前に特定した部品のセクションを検索する数式。一致は、「部品番号」(セル F4 に格納) の行から始まる範囲で行われます。範囲は、INDIRECT、CONCATENATE、ADDRESS を使用するのと同じ種類の数式を使用して構築されます。次に、MATCH によって返される相対行は、絶対行番号を取得するために F4-1 でオフセットされます。

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

ここで、説明の最初の行と最後の行を識別するために、以前と同じ数式を再利用できます。

説明の最初と最後の行

F9: 「添付コンポーネント」行の行番号に 2 を加算して、最初の説明行を取得します。

=F7+2

F10: 説明範囲の最初の空白行を検索します（F9に格納されている行から始まります）。これは、次のように入力する必要がある配列数式です。CTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

次に、説明を表示するには、INDIRECT とインデックス列を使用します。

表示マトリックス

F15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

G15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

これらの数式は、E 列のインデックスで識別される行の品目番号と説明を表示します。IF ステートメントは、最後の行より下の行が表示されないようにするためのものです。例では 5 行しか表示されませんが、最初の行を下にドラッグして新しいインデックスを追加することで、この数式をコピーして行数を増やすことができます。

関連情報