Excelで大量のデータを扱っている際、
「どこか1つのセルだけ値が違うはずなのに、見つけられない…」という経験はありませんか？
前回の記事では、Excelの標準機能やVBAを使って、
膨大なデータの中からたった一つだけ異なる値を持つセルを見つけ出す方法をいくつかご紹介しました。
今回は、その続編として、Excelの「関数」だけを使って、この難題に挑みます！
VBAを使わず、元のデータを変更することもしません。
関数だけでスマートに異なるセルを発見する方法を解説します。
これらのテクニックは、特定のデータ抽出作業の効率化に役立ちます。
今回のミッション
現在の状態とゴール
- 現在の状態: Excelシートの300×300（合計90,000）のセル上の、89999セルに「d」という文字が入力されている。
しかし、どこか1つのセルだけ、「d」ではない異なる文字（何であるかは不明）が入力されている。 - 目指すゴール: その「d」ではない文字が入力されているセルの場所（例: FD56）と、そこに入力されている値（例: f）を特定する。
異なる値のセルの場所（例: FD56）さえ分かれば、数式に =FD56 と入力して値を表示できます。※ INDIRECT関数を使用することもできます。
つまり、異なる値のセルの場所を関数のみで求める、というミッションです。
ルール：関数以外使用禁止
VBA、検索＆置換、ジャンプ、条件付き書式などの機能は使用しない。
セルに入力する「数式（関数）」だけで解決を目指します。
データの準備
このタスクに挑戦するために、以下のいずれかの方法でデータを準備します。
準備の方法1：サンプルファイルのダウンロード
記事で解説されている操作を実際に試すために、
サンプルファイル excel_de_himatsubushi001.xlsx (227KB)をダウンロードします。
準備の方法2：手入力で作成
1.300×300のセル範囲を選択します。
300列目ってどこ？ 「列」って表記がアルファベットなので分からない！
1-1.まず、名前ボックスに「r300c300」と入力して[Enter]を押します。
要するに、「r」はROW(行)、「c」はCOLUMN(列)を表しています。
このようなセルの選択を覚えておくと便利です！
そうすると、「KN300」セルが選択されます。
300列目は「KN列」であることが分かります。
1-2.次に、[Ctrl]+[Shift]+[↑]でKN列をすべて選択。
続いて、[Ctrl]+[Shift]+[←]で行をすべて選択。
2.選択したセル範囲すべてに「d」を入力します。
2-1.300×300のセル範囲が選択されている状態で、「d」と入力します。
2-2.[Ctrl]+[Enter]を押すと、選択範囲すべてに「d」が入力されます。
3.最後に、好きな場所に「d」以外の文字を入力します。準備完了です！
方法：SUM関数とIF関数で位置特定→別セルにINDIRECT関数で値表示
考えかた
- この方法では、「SUM関数」と「IF関数」を組み合わせます。
はじめに、「d」ではない文字が入っているセルの行番号と列番号を探します。 - 次に「ADDRESS関数」でそのセルの番地（例えば FE212 のような形式）を文字列として表示します。
- 最後に、別のセルで「INDIRECT関数」を使い、実際にそのセルに入っている値を表示させる、という流れで進めます。
この方法は、Excel 2019など、それ以前のどのバージョンでも使うことができます。
手順
行/列番号を特定する仕組みを理解する
数式はまず、IF(A1:KN300<>”d”, ROW(A1:KN300)) の部分で、
範囲内のセルが「d」でなければ行番号、そうでなければFALSEというリスト（配列）を作ります。
次に SUM関数がこのリストを合計します。
数字（行番号）だけが合計され、FALSEは無視されるため、
条件に合う唯一のセルの行番号が特定できます。
つまり、212行目です。
列番号も COLUMN(A1:KN300) を使い SUM(IF(…)) で同様に特定します。
こちらは、161列目です。
セル番地を表示する数式(関数)を入力する
空いているセル（例: KP1）に、以下の数式をコピー＆ペーストします。
この数式は、ステップ1の仕組みで計算した行/列番号から、
「ADDRESS関数」でセル番地文字列を作成します。
=ADDRESS(SUM(IF(A1:KN300<>"d",ROW(A1:KN300))),SUM(IF(A1:KN300<>"d",COLUMN(A1:KN300))),4)
下の画像のように数式を入力しても問題ありません。
=ADDRESS(KO1,KO2,4)
別のセルで値を表示する
次に、隣のセル（例: KP3）に以下の数式を入力します。
=INDIRECT(KP2)
この「INDIRECT関数」は、
KP2セルの文字列（セル番地）を実際のセル参照に変え、
その中身の値を表示します。
これで、KP3セルに目的の値（例: q）が表示されます。
解説
SUM(IF(条件, ROW(…))) と SUM(IF(条件, COLUMN(…)))
これらは配列数式として機能し、
条件に合う一つのセルの行番号と列番号をそれぞれ探し出します。
ADDRESS(行番号, 列番号, 4)
見つけ出した行番号と列番号を使って、
セル番地を表す文字列（例: “FE212“）を作り出します。
最後の数字「4」は、
セル番地を「A1」のようなシンプルな形式（相対参照）で表示するための指定です。
INDIRECT(セル番地が入力されたセル)
指定したセル（この例ではKP2）に入力されている文字列を、
実際のセルへの参照場所として扱います。
そして、その参照先のセルに入っている値を画面に表示します。
メリット＆デメリット
- メリット
- Excel 2019やそれ以前のバージョンで使える関数だけで目的を達成できる。
- セル番地と値を別々のセルに表示するので、何をしているかが段階的に分かりやすい。
- 元のExcelシートのデータを書き換えることはない。
- デメリット
- 数式が長くなる。
- また、配列数式の考え方に慣れていないと、なぜこれで答えが出るのかが分かりづらい。
- INDIRECT関数は、Excelが何かを再計算するたびに計算し直されることがある。
なので、この関数を多用すると、Excelの動作が遅くなる可能性がある。（揮発性関数）
まとめ
この記事では、VBAや検索・置換、条件付き書式などの機能を使わずに、
Excel関数のみでこの課題を解決する具体的な方法を解説しました。
SUM関数とIF関数を組み合わせ、さらに配列数式の考え方を用いて、
対象セルの行番号と列番号を特定するテクニックです。
特定した行番号・列番号をADDRESS関数に渡すことで、
セルの番地を文字列として取得できます。
さらに、INDIRECT関数を使えば、
取得したセル番地の文字列を実際のセル参照に変換し、
そのセルに入力されている値を表示させることが可能です。
この手法は、元のデータを変更することなく、
Excelの、どのバージョンでも利用できるメリットがあります。
今回の関数テクニックが、
皆さんのデータ分析やチェック作業の一助となれば幸いです。
