Excel タイガー バームのスクリーニング式「INDEX-SMALL-IF-ROW」の詳細な分析

この記事では、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 です。

3 番目に小さい数値を取得し続けたい場合、どうすればよいかは誰でも思いつくと思いますが、問題が発生します。2 番目のパラメータは手動でのみ変更でき、ドロップによってこのパラメータを達成することはできません。変更します。プルダウンできるようにしたい場合は、2 番目のパラメータで ROW 関数を使用する必要があり、これは次のように変更されます:

ROWこの関数は非常に単純で、得られるのはパラメータの行番号です。この数式を使用すると、列 A のデータを小さい順に並べ替えることができます。面白いと思いますか?

タイガー バームの公式に戻り、5、8、9、16 という 4 つの数字が何を意味するかを思い出してください。これら 4 つの数字を順番に取得するには、SMALL 関数を使用する必要があります。アイデアは、列 C かどうかを判断することです。 F2 と一致し、行番号が同じ場合は行番号が同じで、異なる場合は最大行番号より大きい番号が取得されます (目的は、行番号が見つからないようにすることです):

この目標を達成するには、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 サイトの他の関連記事を参照してください。