この記事では、Excel が数式フィルタリングを使用して 1 対多の検索を完了する方法について説明します。これは、数式データを自動的に検索する古典的な Excel フィルタリング関数の数式です。
専門家がタイガー バームのフォーミュラがあると言っているのをよく聞きますが、タイガー バームのフォーミュラとは一体何ですか。また、この Excel のフォーミュラで何ができるのでしょうか?まず、次のレンダリングを見てみましょう:
この例は、典型的な 1 対多の検索です。検索条件は部門であり、各部門は対応しています。すべては複数のデータであり、Tiger Balm 式の主な目的は、1 対多の検索などの比較的複雑な問題を解決することです。上のアニメーションの数式は次のとおりです。
=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1: $20 ),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")
おそらく多くの友人がこれを見たら驚くでしょう。この式: そんな長い式は理解できません!
今日は、この理解できないが非常に強力な数式ルーチンを解読するために協力します。辛抱強く読み続けてください...
上記の数式では、IFERROR、INDEX の合計 6 つの関数が使用されています。 、SMALL、IF、ROW、および MATCH です。この例では、そのうち IFERROR と MATCH が 2 つの補助関数であり、残りの 4 つの INDEX-SMALL-IF-ROW はスネークオイルの式です。
それでは、最初にこのコア部分の原理を学びましょう:
セル F4 の数式は次のとおりです:
=INDEX($A$2:$A$21, SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)))
INDEXから始めましょう。この関数の基本的な機能は、領域を与え、対応する行と列の位置に従って検索結果を返すことです。上図では、INDEXで検索されるデータ領域は領域$ですA$2:$A$21 名前が記載されている場所。
INDEX 関数の基本構造は次のとおりです: INDEX (検索領域、行、列) 領域が単一の行または列の場合、最後の 2 つのパラメーターのいずれかを省略できます。平たく言えば、映画のチケットを持って座席を見つけるとき、ホール全体の座席が面積であり、列と座席が式の最後の 2 つのパラメーターであるため、目的の場所を正確に見つけることができます。 。
上記の例では、領域は 1 列にあるため、各データがどの行にあるかを判断するだけで済みます。
これを理解すると、INDEX の 2 番目のパラメータに注目する必要があります:
SMALL(IF($C$2:$C$21=$F$2 ,ROW($1:$20) ),99),ROW(A1))
上の図に注目してください。営業部門には合計 4 つのレコードがあります。それぞれデータ領域の行 5、8、9、16 にあります (データ領域は 2 行目から始まります)。
したがって、数式がプルダウンされるとき、INDEX の 2 番目のパラメーターが 4 つの数字 5、8、9、16 になることを願っています (これは理解する必要があります)。
注意、これからタイガーバームの核心部分に触れますので、高い集中力を維持してください...
SMALL 関数の基本構造: SMALL (一連の数値、最小の数値)
この機能を完全に理解するには、単純なデータをシミュレートすることをお勧めします。方法は次のとおりです。
In 列 A に数値を入力します。数式は、この列の最小の数値を意味します。結果は 2 です。理解するのは簡単です。数式の 2 番目のパラメーターを 2 に変更して、見てください。結果:
最後から 2 番目は 4 です。
ROWこの関数は非常に単純で、得られるのはパラメータの行番号です。この数式を使用すると、列 A のデータを小さい順に並べ替えることができます。面白いと思いますか?
この目標を達成するには、IF 関数の介入が必要です。したがって、次のようになります。
IF($C$2:$C$21=$F$2,ROW($1:$20),99)
、この段落を SMALL の最初のパラメータとして使用します。
この IF については、より理解しやすいため、F9 を使用してこの数式の結果を確認できます:
because we There areデータが20個しかないので、IFの第3引数に99を使えば十分です データ量が比較的多い場合は、9の9乗である9^9を使うこともできます とにかく大きいです十分。
この IF を理解したら、この段落をもう一度見てください。SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1) )
とてもめまいがしませんか?
SMALL の部分に関しては、数式をプルダウンすると、取得したい数値が 1 つずつ取得され、これらの数値を INDEX の 2 番目のパラメーターとして使用して、最終的に取得するものであることを理解する必要があります。必要な結果。
タイガーバームの核心はINDEX、SMALL、IF、ROWですので、この部分の原理を理解するために必ず何度も考えてください。もう 1 つ強調すべき重要な点があります。タイガー バームの数式は配列数式であるため、Ctrl キーと Shift キーを押しながら Enter キーを押す必要があります。
最初の式は、複数の列の内容を検索する必要性を考慮して、INDEX のデータ領域は $A$2:$D$21 を使用します。ターゲット値を見つけるには列の位置が必要なので、MATCH(F$3,$A$1:$D$1,0) を使用してデータがどの列にあるかを判断します。
各部門のデータは異なります。数式をさらに数行プルダウンする必要があります。このとき、いくつかのエラー値が生成されます。数式の最外層で IFERROR 関数を使用しますエラー値をマスクして、クエリ結果が非常にきれいに見えるようにします。
今日は、タイガー バーム フォーミュラの原理を説明するために、1 対多の検索の例を使用しました。実際、タイガー バームには多くのフォーミュラがあります。気に入ったら、関連する例を引き続き共有してください。もちろん、この記事を読み終えたら、複雑な数式を自分で解釈できるようになるとさらに良いでしょう。
関連する学習の推奨事項: Excel チュートリアル
以上がExcel タイガー バームのスクリーニング式「INDEX-SMALL-IF-ROW」の詳細な分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。