Excel関数学習：LOOKUP関数の二分法則

前回の記事「Excel関数学習：LOOKUP関数の5つの使い方」でLOOKUP関数の5つの使い方を学びましたが、理解していない友達も多いと予想されます。今日は詳しく説明します LOOKUP の二分法則を説明しましょう 原理を理解したら、昨日のチュートリアルに戻って読んでください。LOOKUP についての理解が変わると思います。

前回の記事では、LOOKUP 関数のさまざまなルーチンを学習しました。また、LOOKUP 関数の検索は二分法に基づいていることも何度も述べました。 the end 二分法とは何ですか? 今日はこの問題について話しましょう。

昨日の例を引き続き使用します: シリアル番号で結果を検索します。シリアル番号は昇順に並べられています。数式 =LOOKUP(J2,A2:D19) の結果は正しいです。

1. 二分探索の原理

二分探索とは、検索範囲内のデータを以下の条件に従って検索することです。数値を 2 つに分割して中央のデータ、つまり中央の値を見つけ、それを検索値と中央の値と比較します。中央の値が検索値と等しい場合は、結果が直接取得されます。中央の値が検索値より小さい場合は、二分検索の比較が下方向に継続されます (つまり、二分検索は下位半分で継続されます)。メソッド検索); 中間値が検索値より大きい場合、二分探索比較は上方向に継続します (つまり、中間値を除いたデータの上半分で二分探索が継続されます)。最後のデータまで二分して検索値に等しいデータが見つからなかった場合: 最後のデータが検索値より小さい場合は、最後のデータの位置を使用して結果の値を取得し、最後のデータが検索値より大きい場合は、結果の値を取得します。検索値以下、上方向に検索 最後のデータに最も近い位置のデータが検索値以下であり、そのデータの位置を基に結果を取得します。

この説明では多くの友人が混乱すると思いますが、上の例を見て、シリアル番号 5 から中国語のスコア 79 を見つける方法を見てみましょう。

1回目の比較: 検索範囲A2~A19には18個のデータがあり、その中間位置は18÷2=9、つまりセルA10の9になります。明らかに、検索値 5 は 9 より小さいので、引き続き A2 ～ A9 を上方向に検索します。

ヒント: 検索範囲内のデータ数が奇数の場合、中央の位置は (番号 1) になります。 ÷ 2 例えば11行目であれば、中間位置は(11 1) ÷ 2 = 6、データ数が偶数の場合、中間位置は(数値) ÷ 2となります。

2 番目の比較: データは 8 つだけです。中央の位置は 8÷2=4、セル A5 の中央の値は 4、検索値 5 はより大きいです。 4、A6～A9まで下を見ていきます。現時点では以下の数字は 4 つだけであり、9 より下のデータは最初の検索時に直接除外されることに注意してください。

#3 番目の比較: 4 つのデータ、中央の値は A7 の 6、検索値 5 は 6 未満なので、上方向に検索します。このとき、検索値と一致するデータはセルA6の5の1つだけなので、5に対応するD列のデータ79が得られます。

このような例だけでは二分法を理解するのは非常に難しいので、別の例を見てみましょう。上の表のデータをスコアの降順に並べるか、通し番号 5 で中国語のスコアを検索します。式を変更する必要はありません。シリアル番号の列の順序がバラバラで昇順に並んでいないためエラーが発生し、実際の番号は 79 ですが、計算式では 94 が得られます。何が起こっているのでしょうか。二分法で見てみましょう。

最初の検索: 中央の値 (9 番目のデータ) は 18 で、検索値 5 は 18 未満なので、A2 ～ A9 を上方向に検索します。

2 回目の検索: 上記 8 個のデータ、中央の値 (4 番目のデータ) が 8、検索値 5 が 8 未満、引き続き A2 ～ A4 を上方向に検索します。

#3 番目の検索: 上の 3 つのデータ、中央の値は 1、検索値 5 は 1 より大きい、下方向に検索:

4 番目の検索:セルA4にはデータ7が1つだけあります。検索値5は7未満です。そこで、7を基準として、7に最も近い位置で値が5未満または5に等しいデータを検索します。 , 1 をセル A3 に入力し、対応する中国語の値 94 を取得します。

これら 2 つの例を通して、皆さんは二項対立についてある程度の理解が得られると思いますが、この原理に関しては、関数の説明には次の 1 つの文しかありません:

#

実際のアプリケーションでは、二分法が何であるか、中間位置が何であるか、下を見るか上を見るかなどについて心配する必要はありません。これはすべて関数の作業であり、覚えておく必要があるのは 1 つのことだけです : データは昇順に並べる必要がありますが、昇順に並べることができない場合は、LOOKUP の精密検索ルーチンに従って式 を設計してください。

2. LOOKUP はデータの四捨五入を実装します

二分法原理の紹介はこれですべてです。次に、残っている 2 つの問題を解決する必要があります。前に。

5 月 12 日の記事では、四捨五入の問題を解決するために LOOKUP を使用しました。その結果、誰もが説明を求めるメッセージを残しました。何が原因でしょうか? みんなが話題にしているこの公式は何ですか?下の図を見てください:

#この数式は LOOKUP 関数を使用して 100 の位以下のすべての数値を削除し、それによってパーセンタイルの四捨五入を実現していることがわかります。

二分法の原理を理解したら、LOOKUP についてみんなに説明します。まずはROW(A:A)*100の部分について説明します。実際には一連の数値を取得するだけです。誰にでもわかりやすくするために、範囲 A:A を小さくしてみましょう。説明として =ROW(A1:A31)*100 を使用します。 100 は実際には 31 個の数字にしか見えません。f9 ファンクション キーを使用して特定の内容を確認できます:

ROW 関数はセルの行番号を取得するために使用されます. ROW(A1:A31)*100 は、セル A1 ～ A31 の行番号を 100 倍して、百の位に四捨五入されたデータのセット {100;200;300;...3000;3100} を取得します。

続いてLOOKUPがステージに登場。上記で取得した、百の位に四捨五入された配列内で A1 を検索します。配列は昇順に配置されているため、A1 を見つけることの本質は、配列内で A1 以下の最大値を見つけることです。

2517.32 を例にとると、それより小さい最大値は 2500 だけなので、結果は 2500 になります。興味のある友人は、二分法則を使って自分で試してみて、それが正しいかどうかを確認してください。

他の数値についても同様です。この公式が賢い理由は、丸めの問題を参照を見つける問題に変えるためです。これは本当に素晴らしいです。

3. データ抽出のための LOOKUP

データ抽出にも LOOKUP を使用するため、5000 単語の予定があります:

今回の問題は何ですか?元の質問では、図に示すように学生番号が抽出されます。

上の図の最初のデータを使用して説明しましょう。セル N1 に「10 Zhang Yong a」と入力し、セル O1 に数式 =-LOOKUP(1,-LEFT(N1,ROW(1:9))) を入力して学生番号を抽出します。

# LOOKUP は参照関数ではありませんか?数値を再度抽出するにはどうすればよいですか?

LEFT 関数の 2 番目のパラメーターは配列を使用しており、ROW(1:9) は {1;2;3;4;5;6;7;8;9} と同等です。 LEFT は、最初のパラメータで指定されたデータを左から抽出します。抽出の長さは 2 番目のパラメータで決まります。 LEFT は配列 {1;2;3;4;5;6;7;8;9} に従って抽出し、9 つの結果を取得します:

つまり、抽出します。左から1桁目、2桁目…9桁目まで。 LEFT で抽出された結果はすべてテキスト型であるため、LEFT の前にマイナス記号を追加すると、テキスト番号が数値に変換され、テキストがエラー値になります:

エラー値は LOOKUP では無視され、{-1; -10} で 1 になります。1 はこのデータセットよりも大きいです。二分探索の原理によると、二分探索の後は下方向にのみ探索できます。最後の桁、1 未満のデータ。したがって、検索値が検索範囲内のすべてのデータ（昇順かどうかに関係なく）より大きい場合、最後のデータを見つけることが LOOKUP の本質であることが簡単に理解できます。

実際、0 はすべての負の数よりも大きいため、数式の 1 を 0 に変更することもできます。数値は -10 なので、LOOKUP の前に負号を追加すると、抽出する必要がある数値 10 になります。

初心者にとって、上記の 2 つの場合の LOOKUP の使用法は高度すぎます。これらの入門を通しても、まだ部分的にしか理解していないと推定されます。実際、関数の学習はプロセスです。理解から理解まで、から理解から習得までには、多くの練習と思考が必要です。誰もが前向きで楽観的な姿勢を維持し、機能を学ぶ楽しさを体験できれば、成功は遠くありません。

関連する学習の推奨事項: Excel チュートリアル

以上がExcel関数学習：LOOKUP関数の二分法則の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。