☰

ルックアップ MIN が N/A を返すのはなぜですか?

tag-icon • tag-icon microsoft-excel microsoft-excel-2007
ルックアップ MIN が N/A を返すのはなぜですか?

私は教師で、クラスでテストを受けました。各質問に対する生徒のパフォーマンスを分析しました。列 B (最も良い質問) の最大値を検索し、その質問番号 (列 A) を返したいと思います。

それはうまくいきます。

次に、同じことを試して最悪の質問を見つけると、#N/A という応答が返されます。なぜかはよくわかりません。

データは次のとおりです。

    A   B
1   Q   Score
2   1   1.13
3   2   1.13
4   3   -0.94
5   4   -1.29
6   5   -1.34
7   6   -0.67
8   7   0.11
9   8   0.91
10  9a  -0.88
11  9b  -1
12  10ab    0.93
13  10c -1.21
14  11  -1
15  12  0.88
16  13  -0.83
17  14  0.94
18  15  -0.88
19  16  -0.13
20  17  0.72
21  18  -0.85
22  19  0.2
23  20  -1.32
24  21  1.19
25  22  -0.06
26  23  1.15
27  24  -0.48
28  25  0.31
29  26  1.15
30  27  0.5
31  28  1.41

クエリは=LOOKUP(MAX(B2:B31),B2:B31,A2:A31)28 を返しますが、実際には=LOOKUP(MIN(B2:B31),B2:B31,A2:A31)を返します#N/A。

何かアドバイス？

答え1

データが上記の例のようにソートされていない場合は、以下を使用できます。
=OFFSET(A1,MATCH(MIN(B2:B31),B2:B31,0),0)
=OFFSET(A1,MATCH(MAX(B2:B31),B2:B31,0),0)

最大値の場合でも、最大値が列の末尾にない場合は、数式の結果は N/A になります。

Teylyn は、データがソートされていない場合は正しいです。正しい結果は完全に運によるもので、False で Vlookup を使用すると、列 A (結果列) のデータは列 B (最初の列はスコア、その後に Q) の右側にあるはずです。

Match と Offset を使用すると、ソートする必要はなく、Match に 0 を書き込むだけで完全一致が得られます。
ここに画像の説明を入力してください

答え2

Lookup では、データを昇順に並べ替える必要があります。この数式の Max バリアントが正しい結果を返すのは、まったくの幸運です。

ソートされた範囲内の検索は次のように機能します。

  • データ範囲を半分にカットして、最後前半の価値
  • 参照値がそれより小さい場合は、データの前半を続行し、それ以外の場合はデータの後半を続行します。
  • 値が1つだけ残るまで前の手順を繰り返します

近似一致検索のより包括的な説明は、こちらでご覧いただけます。Vlookup – なぜ TRUE または FALSE が必要なのでしょうか?。

データがソートされていない場合、正しい結果が出るかどうかは単なる運次第です。

編集：

別の回答で提案されている Offset を使用したソリューションは揮発性があり、ワークブックの速度が遅くなる原因となる可能性があります。範囲の並べ替え順序を変更できない場合は、Index/Match が揮発性のない代替手段です。

=Index(A2:A31,MATCH(MIN(B2:B31),B2:B31,0))
=Index(A2:A31,MATCH(MAX(B2:B31),B2:B31,0))

関連情報