はじめに
『Excelで10✕10セルのセル範囲に入っている整数から検索値を探して、見つけたセルの行番号と列番号を返す』
こんな数式ありませんか?というのをTwitterで見かけて数式を考えてみました
1列、1行だと簡単でも、範囲になるといきなり難しくなりますね
この記事ではセル範囲から検索値を探し座標を返す数式について図を使って解説します!
ただしセル範囲に入っている整数に重複がないことなど前提条件がありますので参考にする際には注意してくださいね
セル範囲の整数に重複が無い(複数一致しない)場合の数式
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/f595d401aeda3a93d98ca9c1470c8611.jpeg)
それでは、早速数式を見ていきましょう!
![](https://aka-aca.com/wp-content/uploads/2022/10/tips039-target_rc-01-1024x399.png)
<例で使用している数式>
検索範囲の整数に重複が無いこと(検索値と一致する整数が複数ないこと)が前提
=SUMPRODUCT((L2=A1:J10)*ROW(A1:J10))&"行目 "&SUMPRODUCT((L2=A1:J10)*COLUMN(A1:J10))&"列目"
![キュウ](https://aka-aca.com/wp-content/uploads/2021/09/8b5fda063dd94491f28401d0c8298c45.png)
ちゃんと『38』が入っている『4行目 5列目』が返っていますね!
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/2dcd16dc9aa8d69405b3dcb8170cc7ba.png)
ちょっと複雑なので、図を追加して説明していきますね
![](https://aka-aca.com/wp-content/uploads/2022/10/tips039-target_rc-02-1024x438.png)
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
まず『(L2=A1:J10)』の部分ですね
セル範囲の各セルに対して、検索値と一致しているかどうかの真偽を返しています
返るのはTRUE(真)かFALSE(偽)です
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/0e784255a5d755fe14512f17caac0960.png)
右下の表は0と1に置き換わってますね
FALSEが0、TRUEが1のようです
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
TRUEとFALSEは数値に変換するとそれぞれ1と0になります
これは後に説明する行番号、列番号の配列との掛け算の過程で
自動的に変換されているので数式には表れていません
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/0e784255a5d755fe14512f17caac0960.png)
これで4行目 5列目の位置に1、それ以外は0でできた
10✕10の配列が得られるんですね
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/f595d401aeda3a93d98ca9c1470c8611.jpeg)
そのとおりです!
次はこの配列と行番号と列番号の掛け算について見ていきましょう
![](https://aka-aca.com/wp-content/uploads/2022/10/tips039-target_rc-03-1024x446.png)
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/0e784255a5d755fe14512f17caac0960.png)
なるほど、4行目 5列目にある1とROW関数が返す行番号4が掛け算されて
4行目 5列目に4、それ以外は0の配列になるんですね
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
あとはたくさんの0と4の合計値を
SUMPRODUCTの関数で計算します
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/e79d1bb00491fdc6b940dc393885424f.png)
検索値の入っている行番号の4になる…!
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
列番号も同じ流れですね
COLUMN関数を使って列番号を返します
![](https://aka-aca.com/wp-content/uploads/2022/10/tips039-target_rc-04-1024x444.png)
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/c430a42a956415bde8b280b08bd7a12d.png)
今度は列番号なので左から右へと数字が増加していますね
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
同じように4行目 5列目の位置に列番号5が入り、
それ以外は0の10✕10の配列が返って合計を計算します
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/c430a42a956415bde8b280b08bd7a12d.png)
あとは&で文字列結合して好きな表示をさせれば良いのですね
行番号や列番号の配列に0と1の配列を掛け算するところは、面白いですね!
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/ef90dc148e8e93333493c45883aa9cfa.png)
この数式ではセル範囲に一致するセルが無い場合
『0行目 0列目』が返ります ご了承下さいね
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/2dcd16dc9aa8d69405b3dcb8170cc7ba.png)
そしてこれも重要なことなのですが
この数式はセル範囲内の整数に重複がある場合…と言うより、
検索値が複数見つかる場合にとんでもない結果が返ってしまいます
![キュウ](https://aka-aca.com/wp-content/uploads/2021/09/8b5fda063dd94491f28401d0c8298c45.png)
そうなんですか!?
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
行番号と列番号を配列内の数字の合計で出してましたよね
1行目と3行目に検索値と一致する整数が合った場合、
足し算して4行目というデタラメな行番号を返してしまいます
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/bb293d12cfdd11fc4ba43458534c99d0.png)
使うには前提をしっかり確認する必要があるということですね
気をつけます
セル範囲の整数に重複がある場合に左上の行番号、列番号を返す数式
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
重複する場合に一番左上(行を優先)のセルの
行番号、列番号を返す数式を紹介しますね
![](https://aka-aca.com/wp-content/uploads/2022/10/tips039-target_rc-05-1024x337.png)
<例で使用している数式>
重複がある場合に一番左上の行番号と列番号を返す数式
=SUMPRODUCT(SMALL((A1:J10=L2)*ROW(A1:J10),SUM(((A1:J10=L2)*ROW(A1:J10)=0)*1)+1))&"行目 "
&MATCH(L2,OFFSET(A1:J1,SUMPRODUCT(SMALL((A1:J10=L2)*ROW(A1:J10),SUM(((A1:J10=L2)*ROW(A1:J10)=0)*1)+1))-1,0),0)&"列目"
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/bb293d12cfdd11fc4ba43458534c99d0.png)
これはまた長い数式ですね…
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/9bd9f77e8c40424fa49abb659009be19.png)
複雑なので大まかに説明しますね
まず前半の部分で行番号を割り出しています
先ほどと違うのは、SMALL関数で0を除いた
1番小さい整数1つを抜き出しているところです
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/0e784255a5d755fe14512f17caac0960.png)
足し算でデタラメになるのを回避しているんですね
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/6868d232b58d25d679e91dcc4be613ea-2.png)
OFFSET関数を使ってセル範囲の1行目を、
見つけた行番号から1を引いた数分だけ下にずらした範囲を
MATCH関数に渡して検索します
MATCH関数は1番初めに一致した位置の番号を返すので、
一番左側の列番号になります
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/d3d663d3de78b3ea928c55252b63672c.png)
計算過程が入り組んでいて頭が追いつきません…
列の方はMATCH関数を使うことで複数見つかっても
一番小さい数字だけを返させているんですね
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/f595d401aeda3a93d98ca9c1470c8611.jpeg)
はい、その通りですよ
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/0e784255a5d755fe14512f17caac0960.png)
ちょっと疑問に思ったのですが、
行番号と列番号、それぞれ一番小さい数字を
抜き出すのではいけなかったんですか?
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
良い質問ですね!
たとえば1行目 5列目と5行目 1列目(セル番地で言うとE1とA5)に
それぞれ検索値に一致する数字が入っていたとしたら…?
![キュウ](https://aka-aca.com/wp-content/uploads/2022/01/700d2fdc72cf8f780b916a06fd93be1d.png)
あ…それぞれの最小をとると1行目 1列目になっちゃいますね
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
そうなんです、それでまず1行目に一致する数字があることを割り出し、
1行目をMATCH関数で探し一番小さい数字(列番号)を得る必要があるんですね
![キュウ](https://aka-aca.com/wp-content/uploads/2021/08/e79d1bb00491fdc6b940dc393885424f.png)
ややこしいものですね…
![シノ](https://aka-aca.com/wp-content/uploads/2021/09/05fee9487e3f07a4ba35214d52d5c496.png)
補足しておきますと、
この数式は検索値が見つからないときはエラーになります
エラー表示を避けるためにはIFERROR関数などでの対処が必要です
おわりに
今回は配列数式を使うのにうってつけの良い例だと思いました。
行番号と列番号の配列と、真偽判定を重ねるところは初めは理解しにくいかもしれません。
重複がある場合の数式はもっとやりようがあったように思います。
繰り返しになりますが、もし使用する際には条件などをしっかり確認して下さい。
【外部リンク】SUMPRODUCT関数|Microsoftサポート
最後までお付き合い頂き、ありがとうございました!
当ブログはエクセル関数の擬人化キャラクター記事をメインコンテンツとしています!
よろしければこちらの記事もどうぞ~!
コメント