最寄りの番号を見つける – 近くの健康食品

内容

実際には、指定された数値に関連してセット (テーブル) 内の最も近い値を見つける必要がある場合が非常によくあります。たとえば、次のようになります。

ボリュームに応じた割引の計算。
プランの実施に応じたボーナス額の計算。
距離に応じた配送料の計算。
商品等に適した容器の選定

また、場合によっては上下の丸めが必要になる場合があります。

このような問題を解決するには、明らかな方法とそうでない方法がいくつかあります。それらを順番に見ていきましょう。

まず、卸売の割引を提供するサプライヤを想像してみましょう。割引の割合は、購入した商品の数量によって異なります。たとえば、5 個以上購入すると 2% の割引が適用され、20 個から購入するとすでに 6% 割引になります。

購入した商品の数量を入力するときに割引率をすばやく美しく計算するにはどうすればよいですか?

方法 1: ネストされた IF

シリーズからの方法「何を考える必要があるか - ジャンプする必要があります!」。ネストされた関数の使用 IF （IF） セル値が各間隔に該当するかどうかを順番に確認し、対応する範囲の割引を表示します。しかし、この場合の式は非常に扱いにくいものになる可能性があります。

最も近い番号を見つける

そんな「モンスター人形」をデバッグしたり、しばらくしてから新しい条件をいくつか追加してみたりするのが楽しいのは明らかだと思います。

さらに、Microsoft Excel には IF 関数の入れ子制限があります。古いバージョンでは 7 回、新しいバージョンでは 64 回です。さらに必要な場合はどうすればよいですか？

方法 2. インターバルビューを使用した VLOOKUP

この方法ははるかにコンパクトです。割引率を計算するには、伝説の関数を使用します VPR （VLOOKUP） 近似検索モード:

最も近い番号を見つける

コラボレー

B4 – 最初の取引における割引対象の商品の数量の値
$G$4:$H$8 – 割引表へのリンク – 「ヘッダー」なしで、アドレスは $ 記号で固定されています。
2 — 割引値を取得したい割引テーブルの列の序数
TRUE –これは「犬」が埋葬されている場所です。関数の最後の引数の場合 VPR 指定する 横たわっている（NS） または0の場合、関数は次を探します 厳密な一致 を数量列に入力します (この例では、割引テーブルに値 49 がないため、#N/A エラーが発生します)。しかし、代わりに 横たわっている 書きます TRUE （TRUE） または1、その場合、関数は正確なものを探しませんが、 最も近い最小 価値であり、必要な割引率を示します。

この方法の欠点は、割引テーブルを最初の列で昇順に並べ替える必要があることです。そのような並べ替えがない場合 (または逆の順序で行われる場合)、式は機能しません。

最も近い番号を見つける

したがって、このアプローチは、最も近い最小値を見つけるためにのみ使用できます。最も近い最大のものを見つける必要がある場合は、別のアプローチを使用する必要があります。

方法 3. INDEX 関数と MATCH 関数を使用して最も近い最大値を見つける

では、反対側から問題を見てみましょう。さまざまな容量の産業用ポンプのいくつかのモデルを販売しているとします。左の売上表は、お客様が必要とする電力を示しています。最も近い最大出力または同等の出力のポンプを選択する必要がありますが、プロジェクトで必要とされるものを下回ってはなりません。

ここでは VLOOKUP 関数は役に立たないので、類似の INDEX 関数を使用する必要があります。（索引）さらに露出（マッチ）:

最も近い番号を見つける

ここで、最後の引数が -1 の MATCH 関数は、最も近い最大値を見つけるモードで機能し、次に INDEX 関数が必要なモデル名を隣接する列から抽出します。

方法 4. 新機能 VIEW (XLOOKUP)

すべての更新プログラムがインストールされたバージョンの Office 365 を使用している場合は、VLOOKUP の代わりに（VLOOKUP）そのアナログを使用できます-VIEW機能 (XLOOKUP)、私はすでに詳細に分析しました：

最も近い番号を見つける

ここに：

B4 – 割引を探している製品の数量の初期値
$G$4:$G$8 – 一致を探している範囲
$H$4:$H$8 – 割引を返す結果の範囲
XNUMX 番目の引数 (-1) には、完全一致ではなく、必要な最も近い最小数の検索が含まれます。

この方法の利点は、割引テーブルを並べ替える必要がないことと、必要に応じて、最も近い最小値だけでなく最も近い最大値も検索できることです。この場合の最後の引数は 1 になります。

残念ながら、まだすべての人がこの機能を持っているわけではありません。Office 365 の幸せな所有者だけです。

方法 5. パワークエリ

Excel 用の強力で完全に無料の Power Query アドインにまだ慣れていない場合は、ここにいます。すでによく知っている場合は、それを使用して問題を解決してみましょう。

最初にいくつかの準備作業を行いましょう。

キーボードショートカットを使用してソーステーブルを動的 (スマート) に変換しましょう Ctrlキー+T またはチーム ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット).
わかりやすくするために、名前を付けましょう。営業 и 割引タブ コンストラクタ（設計）.
ボタンを使用して、各テーブルを順番に Power Query に読み込みます。 テーブル/範囲から タブ 且つ (データ — テーブル/範囲から). 最近のバージョンの Excel では、このボタンの名前がに変更されました。 葉付き（シートより）.
この例のように、テーブルに数量を含む異なる列名がある場合 (「商品の数量」と「…からの数量」)、Power Query で名前を変更し、同じ名前にする必要があります。
その後、Power Query エディターウィンドウでコマンドを選択して、Excel に戻ることができます。 ホーム — 閉じてロード — 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロード…) そしてオプション 接続を作成するだけ (接続のみ作成).
次に、最も興味深いことが始まります。 Power Query の経験がある場合は、前の方法の場合と同様に、VLOOKUP の結合クエリ (マージ) でこれら XNUMX つのテーブルをマージする方向にさらに考えるべきであると思います。実際、一見するとまったくわかりませんが、追加モードでマージする必要があります。 Excelタブで選択 データ – データの取得 – リクエストの結合 – 追加 (データ — データの取得 — クエリの結合 — 追加) そして私たちのテーブル営業 и 割引表示されるウィンドウで:
をクリックした後 OK 私たちのテーブルは、互いに下に接着されて XNUMX つの全体になります。これらのテーブルの商品の数量を含む列は、互いに下に落ちていることに注意してください。それらは同じ名前です:
sales テーブルの行の元のシーケンスが重要な場合は、その後のすべての変換後にそれを復元できるように、次のコマンドを使用して番号付きの列をテーブルに追加します。 列の追加 – インデックス列 (列の追加 — インデックス列). 行の順序が重要でない場合は、この手順を省略できます。
次に、テーブルのヘッダーにあるドロップダウンリストを使用して、列で並べ替えます数量上昇：
そして主なトリック：列ヘッダーを右クリックします お得な商品 チームを選ぶ フィル - ダウン (フィル - ダウン). セルを空にするヌル以前の割引値が自動的に入力されます。
列でソートして元の行の順序を復元する必要があります インデックス （後で安全に削除できます）フィルターで不要な行を取り除きますヌル列別 トランザクションコード: