質問: excelで、2つの配列(水平方向と垂直方向)の間で一致するデータを見つける必要があります excel 2010(または2007-両方ありますが、実行する一部のレガシーアプリケーションの制限としてosはwin7 32ビットのみです)では、2つのデータ配列から一致する値を見つけて返す方法を見つける必要があります。2つのスプレッドシートがあります。 1つは階層からの巨大なフラットファイルolapキューブディメンション(sap bpcからの37,000行)。もう1つは、照合する必要のある値のテーブルです。一致する値を返す必要があります。 2番目のスプレッドシートから最初のシートのcolumnaへ-フラットファイル。課題は、それが階層構造であるため、一致するsheet1から単一の列を選択できないことです。一致は各行のどの列でも発生する可能性があります。したがって、基本的には、シート1の単一行を配列として、シート2の列を配列として一致するものをすべて取得する必要があると考えています(私は思います)。英語で、excelに次のことを実行してもらいます。データがあるsheet1の各行について、行全体を確認します(たとえば、範囲b2:r2-数式/一致値の列aを空白のままにしました)。レポートカテゴリリスト(シート2の列a、範囲a1:a42)に一致するものがある場合は、sheet2の値をsheet1!a2(一致のために作成した空白の列)に返します。これは、食べ物の寓話を含むデータのサンプルです。空白のcolumnaを作成し、各行のデータが分類の階層を上っていくことに注意してください。ここで、colbは基本レベルであり、ターミナルの親がcolfにあるために必要に応じて繰り返されます。さて、この次の画像には、私が使用したいレポート形式があります。いくつかの階層レベルからのデータが必要な場合もあれば、他のレベルからのデータが必要な場合もあります。最終的に、スプレッドシートには、必要なカスタマイズされたレポートカテゴリが入力されます(その後、集計データ用にそれらのカテゴリをピボットできます)。私はこれをモンスターのvlookup式で実現してきましたが、vlookupステートメントが8つの深さでネストされた37,000行でexcelが頻繁にクラッシュするため、別の、より簡単な、または少なくともリソースをあまり消費しない方法があるかどうか疑問に思いました。したがって、実際のレポートカテゴリ(sheet2はall_budget_unitsと呼ばれます)を使用して、現在使用しているものは次のとおりです。= iferror(vlookup(iferror(vlookup(iferror(vlookup(iferror(vlookup(iferror(vlookup(iferror(vlookup(c2、all_budget_units!$ a $ 1:$ a $ 39,1、false)、d2)、all_budget_units!$ a $ 1 :$ a $ 39,1、false)、e2)、all_budget_units!$ a $ 1:$ a $ 39,1、false)、f2)、all_budget_units!$ a $ 1:$ a $ 39,1、false)、g2)、all_budget_units !$ a $ 1:$ a $ 39,1、false)、h2)、all_budget_units!$ a $ 1:$ a $ 39,1、false)、i2)
回答: これにはマクロソリューションは必要ありません。配列数式を使用できます。{=index(all_budget_units!$a$1:$a$39, max(iferror(match(c2:i2, all_budget_units!$a$1:$a$39, 0), 0)))} これは、i2の値がマスターリストにもあることを前提としていますが、元の式では想定されていません。存在しない、または存在しない可能性がある場合は、代わりにこれを使用してください。{=iferror(index(all_budget_units!$a$1:$a$39, max(iferror(match(c2:h2, all_budget_units!$a$1:$a$39, 0), -1))), i2)} 以前に配列数式を使用したことがない場合は、中括弧{}を自分で入力しないでください。数式の残りの部分を入力し、ctrl + shift + enterを押して配列数式として入力します。正しく行うと、中括弧が数式ボックスに表示されます。これが機能する方法は、match関数を使用して一致結果の配列を作成することです。これらの1つを除くすべてが#n/aなので、iferrorでラップして、これらを0(または2番目のバージョンでは-1)に変換します。実際の一致は正の数になるため、配列のmaxを取得すると、単一の一致が見つかります。次に、index関数を使用してこれを値に変換します。 2番目のバージョンでは、一致するものがまったくない場合、位置が-1のindexを使用しようとするとエラーが発生するため、iferrorを使用してデフォルトを返します。値。