Excelで最も近い値または最も近い値を見つける方法は？

例えば、列Bに数字のリストがあり、その中から指定された数（例えば18）に最も近い値を見つけたい場合、Excelの配列数式を使うことで、リストを手動で確認することなく効率的に特定できます。

まず、空白セルを選択し、次の数式を入力します。数式を入力したら、EnterキーではなくCtrl + Shift + Enterを押すようにしてください。これにより数式が配列数式として動作し、正しく機能することが保証されます：

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

このアプローチは、連続的な範囲から単一の最も近い数値を取得する必要がある場合に最も適しています。数値の正確さや完全一致が重要なほとんどのケースでうまく機能します。ただし、配列数式は非常に大きなデータセットではリソースを多く消費することがあるのでご注意ください。パフォーマンスの問題が発生したり、#VALUE!のようなエラーメッセージを受け取った場合は、セル参照を再確認し、Ctrl + Shift + Enterを正しく押しているか確認してください。

時には単一の最も近い値ではなく、ターゲット値の一定範囲内にあるすべての数値を選択したい場合もあります。この範囲は通常、偏差範囲と呼ばれます。Kutools for Excelは、Select Special Cells機能を通じて実用的な解決策を提供し、ターゲット値からの指定された差分範囲内のすべての値を迅速に選択できます。

例えば、ターゲット値が18で、偏差値が2だとすると、これは16（18-2）から20（18+2）の間にあるすべての値を選択したいということです。以下のようにステップバイステップで行うことができます：

1. 検索したい範囲を選択します（例：B3:B22）、次にKutools > 選択 > Select Specific Cellsに移動します。

2. Select Specific Cellsダイアログボックスで：

• 選択タイプの下で、「セル」を選択します。
• In Specific type:
  - 最初のドロップダウンリストを 以上 に設定し、値を 16 ボックスに入力します。
  - 2番目のドロップダウンを 以下 に設定し、値を 20.

3入力します。次に OK をクリックして実行します。Kutoolsは、基準を満たすセルの数を通知し、指定された偏差範囲内の最も近い値をすべて強調表示します：

この解決策は、特に可変許容範囲を持つ広範囲のデータを扱う際に、大量の近接値を素早く特定するのに理想的です。選択の精度は、偏差を明確に設定することに依存します。偏差が狭すぎたり広すぎたりすると、関連データを見逃したり、不要な値を含めてしまう可能性があります。

自動化を求めるユーザー、または複数のシートや大規模なデータセット全体でカスタマイズされた最も近い値の検索（数値データだけでなくテキストデータにも対応）が必要なユーザーにとって、VBAマクロは効率的で柔軟な解決策となります。ターゲットとすべての候補との差を体系的にチェックするようExcelをプログラミングすることで、最も近い数値だけでなく、文字列もテキスト距離に基づいて見つけることができます。

このアプローチは、特に手動操作では対処できないほど大きな範囲での統合自動化が必要な場合や、繰り返しタスクを適用する場合に有利です。ただし、VBAマクロを使用する際には、マクロを有効にし、VBA環境に基本的な知識が必要であることに注意してください。マクロを実行する前に必ずデータをバックアップし、意図しないデータ損失を防ぎましょう。

1. 開発  > Visual Basicをクリックします。Microsoft Visual Basic for Applicationsウィンドウで、挿入 > モジュールをクリックし、以下のコードをモジュールにコピーします：

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. 次に、ワークシートに移動し、この数式を空白セルに入力します： =FindClosest(B3:B22, E2)。Enterキーを押して最も近い値を取得します。

データをレビューまたは提示する際、フィルタリングやデータの並べ替えを行わずに、ターゲットに最も近い値を目視で特定することが役立つことがよくあります。Excelの組み込みの条件付き書式機能を使用すると、ターゲット値に最も近いセルを強調表示でき、一目で簡単に見つけることができます。この方法では正確な値自体は返されませんが、迅速なデータ分析や視覚的な強調表示に効果的です。

この方法の主な利点は、データやターゲット値が変更されても適応できる非破壊的で動的な強調表示です。特にダッシュボード、プレゼンテーション、レビューなどの場面で視認性が重要となる場合に適しています。ただし、複数の値が同じ「近さ」を持つ場合には精度が低下し、さらに処理のために値自体が出力されることはありません。

1. 分析したいセルの範囲を選択します（例：B3:B22）。

2. ホームタブで、条件付き書式 > 新しいルールをクリックします。

3. ダイアログボックスで「数式を使用して書式設定するセルを決定」を選択し、数式ボックスに次の数式を入力します：

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. 書式をクリックし、強調色を選択して、OKをクリックし、もう一度OKをクリックしてルールを適用します。

これにより、選択した範囲の中でE2のターゲット値に最も近い値を持つすべてのセルが強調表示されます。

大規模な範囲で作業している場合や予期しない結果が得られた場合は、参照が正しいかどうか、絶対/相対参照が意図通りに設定されているかを再確認してください（ターゲットセルと範囲参照をロックするために$を使用します）。