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を正しく押しているか確認してください。

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

例えば、目標値が18で偏差値が2であるとしましょう。これは、16（18-2）から20（18+2）までの範囲内のすべての値を選択したいことを意味します。以下は、これを段階的に達成する方法です：

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

2. Select Specific Cellsダイアログで：

• Selection typeの下で、Cellを選択します。
• 次に、 Specific type:
  − 最初のドロップダウンリストを 以上 に設定し、値を入力します。 16 ボックス内に入力します。
  − 2番目のドロップダウンを 以下 に設定し、値を入力します。 20.

3クリックします。 OK を実行すると、Kutoolsは基準を満たすセルの数を通知し、指定された偏差範囲内の最も近い値をすべてハイライト表示します：

この解決策は、特に広範囲の許容誤差を持つデータ群の中から多くの近くの値を素早く識別する場合に理想的です。選択の精度は、偏差の設定に依存しますので、偏差が狭すぎたり広すぎたりすると、関連データを見逃したり不要な値を含めてしまう可能性があります。

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

このアプローチは、特に手動操作では扱えないほど広範囲にわたるデータや繰り返し行うタスクに統合自動化が必要な場合に有利です。ただし、VBAマクロを使用するにはマクロの有効化とVBA環境に関する基本的な知識が必要です。どんなマクロを実行する前にも、常にデータのバックアップを取って、意図しないデータ損失を防ぐようにしてください。

1. Developer  > Visual Basicをクリックします。Microsoft Visual Basic for Applicationsウィンドウで、Insert > Moduleをクリックし、次のコードをモジュールにコピーします：

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の組み込みのConditional Formatting機能を使用すると、ターゲット値に最も近いセルを目立たせることができ、一目で簡単に見つけられるようになります。この方法は正確な値自体を返しませんが、迅速なデータ分析と視覚的な強調に効果的です。

この方法の主な利点は、データやターゲット値の変更に合わせて動的に調整可能な非破壊的なハイライト表示です。特にダッシュボードやプレゼンテーション、レビューの場面で有用です。ただし、「近さ」が同じ値が複数存在する場合には精度が落ちることがあり、また値自体を出力してさらなる処理に使用することはできません。

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

2. Homeタブで、Conditional Formatting > New Ruleをクリックします。

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

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

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

これにより、選択範囲内でターゲット値（E2）に最も近い値を持つすべてのセルがハイライト表示されます。

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