![]() リプライセル 効果なし トリム とはこの例では、名前はセルD2:G2に入力されます。 名の下で、一致可能な最大数以上の空のセルを選択し、数式バーに次のいずれかの配列数式を入力して、Ctrl + Shift + Enterキーを押して入力を完了します(この例ではその場合は、数式が入力されている範囲全体でのみ数式を編集できます。. または、最初のセルに数式を入力し、Ctrl + Shift + Enterを押して、その数式を列の下のさらにいくつかのセルにコピーすることもできます(この場合は、各セルの数式を個別に編集できます)。. = IFERROR(INDEX($ B $ 3:$ B $ 13、小さい(IF(D $ 2 = $ A $ 3:$ A $ 13、ROW($ B $ 3:$ B $ 13)-2、 ")、ROW() - 2 )、「」) または = IFERROR(INDEX($ B $ 3:$ B $ 13、小さい(IF(D $ 2 = $ A $ 3:$ A $ 13)、ROW($ A $ 3:$ A $ 13) - MIN(ROW($ A $ 3:$ A $ 13) ))+ 1、 "")、ROW() - 2))、 "") ご覧のとおり、最初の式はもう少しコンパクトですが、2番目の式はより普遍的で、必要な変更が少なくて済みます(構文とロジックについてもう少し詳しく説明します)。. これには、数式を入力したばかりのセル範囲を選択し、塗りつぶしハンドル(選択した範囲の右下にある小さな四角形)を右にドラッグします。. 結果は次のようになります。 この式のしくみ これは、Excelの中級から上級向けの使用例で、配列数式とExcel関数の基本的な知識を暗示しています。. 裏から作業をしています。 IF機能 式の中核では、IF関数を使用して、検索範囲内のすべての検索値の位置を取得します。IF(D $ 2 = $ A $ 3:$ A $ 13、ROW($ B $ 3:$ B $ 13 )-2、 "") IFはルックアップ値(D2)をルックアップ範囲(A3:A13)内の各値と比較し、一致が見つかった場合はその行の相対位置を返します。それ以外の場合は空の文字列( ""). 行の相対位置は、最初の行の位置が1になるように、ROWから2を引いて($ B $ 3:$ B $ 13)計算されます。. この操作の結果は配列{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}となり、これはIF関数のvalue_if_true引数に渡されます。. 上記の計算の代わりに、次の式を使用できます。ROW(lookup_column) - MIN(ROW(lookup_column))+ 1これは同じ結果を返しますが、戻り列の位置に関係なく変更を必要としません。. この例では、ROW($ A $ 3:$ A $ 13) - MIN(ROW($ A $ 3:$ A $ 13))+ 1となります。. ですから、この時点であなたは数(マッチの位置)と空の文字列(マッチしない)からなる配列を持っています。. この例のセルD3には、次の配列があります。 ソースデータを確認すると、 "Adam"(D2のルックアップ値)がルックアップ範囲の3、8、10番目の位置に表示されます(A3:A13)。. SMALL関数次に、SMALL(array、k)関数がステップインして、どの一致を特定のセルに返すべきかを決定します。. このためには、一種の "増分カウンタ" ROW() - nを作成します。ここで、 "n"は最初の数式セルの行番号から1を引いたものです。.リプライセル 効果なし トリム やり方この例では、セルD3:D7に式を入力したので、ROW() - 2はセルD3に "1"(行3マイナス2)、セルD4に "2"(行4マイナス2)などを返します。. その結果、SMALL関数は、セルD3内の配列の1番目に小さい要素、セルD4内の2番目に小さい要素などをプルします。.そしてこれは最初の長く複雑な式を次のように非常に単純な式に変換します。 先端. 数式の特定の部分の背後にある計算値を確認するには、数式バーでその部分を選択してF9キーを押します。. IFERROR関数 そして最後に、IFERROR関数で式をラップして考えられるエラーを処理します。これは、このルックアップ値に対して返される一致数がわからないためです。したがって、式は、またはに等しい数のセルにコピーします。可能な一致数よりも大きい. ユーザーを一連のエラーで怖がらせるのではなく、単に空の文字列(空白のセル)に置き換えます。. 参照値(D $ 2)内の相対列参照を除いて、すべての参照は固定されています。これは、他の参照値に対する一致を返すために式がコピーされる列の相対位置に基づいて変更されます。. これらすべてをまとめると、ExcelでVlookupの複数の値に次の一般式が得られます。 式1: IFERROR(INDEX(return_range、SMALL(IF(lookup_value = lookup_range、ROW(return_range) - m、 "")、ROW() - n))、 "") 式2 IFERROR(INDEX(return_range、SMALL(IF(lookup_value = lookup_range) - MIN(ROW(lookup_range))+ 1、 "")、ROW() - n)) "") どこで: mは、戻り範囲内の最初のセルの行番号から1を引いたものです。. 上記の例では、戻り範囲と式の範囲が両方とも3行目で始まるため、nとmの両方が "2"に等しくなります。. 列ではなく行に複数の値を返す場合は、上記の式を次のように変更します。 = IFERROR(インデックス($ B $ 3:$ B $ 13、小(IF($ D3 = $ A $ 3:$ A $ 13、行($ B $ 3:$ B $ 13)-2)、 ")、COLUMN() - 4 )、「」) または = IFERROR(INDEX($ B $ 3:$ B $ 13、小さい(IF($ D3 = $ A $ 3:$ A $ 13)、ROW($ A $ 3:$ A $ 13) - MIN(ROW($ A $ 3:$ A $ 13) ))+ 1、 "")、COLUMN() - 4))、 "") 前の例のように、どちらも配列式なので、Ctrl + Shift + Enterショートカットを押して正しく完成させることを忘れないでください。. 式は前の例と同じロジックで機能しますが、ROWの代わりにCOLUM関数を使用して、どの一致値を特定のセルに返す必要があるかを判断します。COLUMN() - n. 式が他の行に正しくコピーされるようにするには、$ D3のように、参照値の参照、絶対列、および相対行に注意してください。. まとめると、Vlookupの一般式は次のとおりです。複数の結果が行に返されます。 式1: IFERROR(INDEX(return_range、SMALL(IF(lookup_value = lookup_range、ROW(return_range) - m、 "")、COLUMN() - n))、 "") 式2 IFERROR(INDEX(return_range、SMALL(IF(lookup_value = lookup_range) - MIN(ROW(lookup_range))+ 1、 "")、COLUMN() - n)) "") どこで: mは、戻り範囲内の最初のセルの行番号から1を引いたものです。. 複数の条件に基づいた複数の一致のVlookup 1つの条件に基づいてExcelで複数の値をVlookupする方法はすでに知っています. しかし、2つ以上の基準に基づいて複数の一致を返したい場合はどうしますか?前述の例をさらに進めて、追加の[月]列があり、特定の月に特定の販売者によって販売されたすべての商品のリストを取得したい場合はどうしますか。 あなたが配列の公式に慣れているならば、あなたはそれらがAND演算子としてアスタリスク(*)を使うことを許すことを覚えている. そのため、前述の2つの例で説明した式を使用して、以下に示すように複数の条件をチェックするようにすることができます。. リプライセル 効果なし トリム やり方列内の複数の一致を返す IFERROR(INDEX(return_range、SMALL(IF(1 =( - (lookup_value1 = lookup_range1))*( - (lookup_value2 = lookup_range2))))、ROW(return_range)-m、 "")、ROW() - n )、「」) どこで: mは、戻り範囲内の最初のセルの行番号から1を引いたものです。. 売り手リスト(lookup_range1)がA3:A30にあり、月リスト(lookup_range2)がB3:B30にあり、関心のある売り手(lookup_value1)がセルE3にあり、関心のある月(lookup_value2)がセルF3にあるとします。計算式は次のようになります。 = IFERROR(INDEX($ C $ 3:$ C $ 30、小さい(IF(1 =( - ($ E $ 3 = $ A $ 3:$ A $ 30)))*( - ($ F $ 3 = $ B $ 3: $ B $ 30)))、ROW($ C $ 3:$ C $ 30)-2、 "")、ROW() - 2))、 "") このレイアウトはダッシュボードを作成するのに役立ちます。. ユーザーはE3に名前、F3に月を入力し、G列に商品のリストを取得できます。 複数の結果を連続して返す 複数の基準セットに基づいて複数の値を取得したい場合は、結果が行で返される水平レイアウトをお勧めします。. この場合は、次の一般式を使用してください。 IFERROR(INDEX(return_range、SMALL(IF(1 =( - (lookup_value1 = lookup_range1))*( - (lookup_value2 = lookup_range2)))、)ROW(return_range) - m、 ""、COLUMN() - n )、「」) どこで: mは、戻り範囲内の最初のセルの行番号から1を引いたものです。. サンプルデータセットの場合、式は次のようになります。 = IFERROR(インデックス($ C $ 3:$ C $ 30、小さい(IF(1 =( - ($ E3 = $ A $ 3:$ A $ 30)))*( - ($ F3 = $ B $ 3:$ B $ 30)))、ROW($ C $ 3:$ C $ 30)-2、 "")、COLUMN() - 6))、 "") そして、結果はこれに似ることができます: 同様に、3つ、4つ、またはそれ以上の条件で複数のVlookupを実行できます。. これらの式の仕組み 基本的に、最初の例で説明したように、複数の条件を持つ複数の値を持つVlookupの公式は、既によく知られているロジックで機能します。. 唯一の違いは、IF関数が複数の条件をテストするようになったことです。 1 =(( - (lookup_value1 = lookup_range1))*( - (lookup_value2 = lookup_range2))*) 各lookup_value = lookup_range比較の結果は、論理値の配列TRUE(条件が満たされている)およびFALSE(条件が満たされていない)です。. また、ゼロで乗算すると常にゼロになるため、結果の配列では、指定したすべての条件を満たす要素に対してのみ1が与えられます。. ここで、ROW関数がすべての条件を満たす行数を返すように、最後の配列と1を比較するだけです。それ以外の場合は空の文字列. そのため、各数式は、ソースデータが変更されるかワークシートが再計算されるたびに、配列のすべての要素を繰り返し処理します。. 何百または何千もの行を含む大きなワークシートでは、これはあなたのExcelをかなり遅くするかもしれません. 例を詳しく見て理解を深めるために数式をリバースエンジニアリングするには、Excel Vlookup Multiple Valuesサンプルワークシートをダウンロードしてください。. 1つのセルに複数の値を返すようにVlookupをする方法 私は先を見越して - 私はVlookupの複製に対して、1回の販売で複数の一致を出力する式を知りません.リプライセル 効果なし トリム ろだただし、これを行うには、Ultimate Suite for Excelに含まれている2つのアドインを使用して、数式を使用しない( "ストレスフリー" :)読み取り方法を知っています。. まだExcelに持っていない場合は、14日間の無料試用版をここからダウンロードしてから、次の手順に従ってください。. 元データと期待される結果 スクリーンショットに示すように、前の例で使用したデータセットを引き続き使用します。. しかし今回は別の方法で達成したい - 別々のセルで複数の一致を抽出するのではなく、コンマ、スペース、または選択した他の区切り文字で区切って、それらを単一のセルに表示します。. メインテーブルに対して複数の一致がある行をプルする メインテーブルで、最初の列に一意の名前のリストを、2番目の列に月を入力し、下のスクリーンショットに示すようにそれらを並べ替えます。. その後、以下のステップを実行してください。 メインテーブルを選択するか、メインテーブル内の任意のセルをクリックしてから、リボンの[Merge Two Tables]ボタンをクリックします。 アドインはテーブル全体を識別して選択するのに十分スマートなので、次へをクリックするだけです。 先端. 初めてこのツールを使用するときは、問題が発生した場合に備えて[ワークシートのバックアップコピーを作成する]ボックスを選択するのが理にかなっています。. メインテーブルとルックアップテーブル(この例ではSeller列とMonth列)で比較する列のペアを1つ以上選択して、[次へ]をクリックします。. 一致する値を取得する列(この例ではProduct)を選択して、[次へ]をクリックします。. この例では、次のオプションが必要です。同じ値を持つ行の後に、重複する一致する値を持つ行を挿入します。. この時点で、次のような結果になります。すべての一致する行がメインテーブルにプルされ、ルックアップ列の値によってグループ化されます - 最初にSeller、次に月ごとにグループ化されます。 結果の表は既に見栄えがよくなっていますが、それは私たちが望んでいたものとは異なりますね。あなたが覚えているように、私たちはVlookupの複数のマッチを探していて、それらを単一のセル、コンマまたは別の方法で分離して返すようにしています. 重複行を1行に結合する 「重複行」を単一行にマージするには、別のツールである「行結合ウィザード」を使用します。. [テーブルの結合]ツールによって作成されたテーブル(上のスクリーンショットを参照)またはテーブル内の任意のセルを選択して、リボンの[行の結合]ボタンをクリックします。 アドインがテーブルを正しく作成したかどうかを確認し、[次へ]をクリックします。 キー列(この例ではSellerとMonth)を選択し、Nextをクリックします。 複数の一致を含む列(この例ではProduct)を選択し、必要な区切り文字(セミコロン、コンマ、スペース、または改行)を選択し、[完了]をクリックします。.リプライセル 効果なし トリム とは必要に応じて、追加機能の1つ、またはその両方を有効にできます。 重複した値を削除する - マージされる列に同じ値がいくつか含まれている場合、最初の出現が保持され、重複した一致は削除されます。. この例では、このオプションはチェックされておらず、アドインは見つかったすべての一致を返します。. たとえば、セルC2には、Lemons、Bananas、Apple、Lemons、Bananasという文字列があります(以下の手順5の結果を参照してください)。. 空のセルをスキップする - 説明不要:) アドインを数秒で処理できるようにすれば、すべて完了です。 これが、Googleのツールを使用してExcelで複数の値を検索して返す方法です。. あなたがそれらを試してみることに興味があるなら、Ultimate Suiteの完全に機能的な評価版はここからダウンロードされることができます. アドインがあなたの仕事に役立つことが証明されたら、私達が私達のブログの読者に排他的に提供する15%割引クーポンコードをつかむようにしてください:AB14-BlogSpo.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
February 2019
Categories |