こんばんは。きわさです。
今日は備忘録Excel関数編です。
さて、この画像、D8に入るものはなんでしょう。
E1？
そうです。E1です。
手で入力すれば終わりですが、今回はこれを数式で、関数で表示させてみます。
範囲内(A1:E4)の指定した文字列(B8)のセル番地を取得します。
D8に入る式は、
=ADDRESS(SUMPRODUCT(($A$1:$E$4=B8)*(ROW($A$1:$E$4))),SUMPRODUCT(($A$1:$E$4=B8)*(COLUMN($A$1:$E$4))),4)
です。なにやら複雑な感じです。
順番に見ていきます。
ADDRESS関数
まずは、ADDRESS関数。
これは、ADDRESS(行番号、列番号、[参照の種類]、[参照形式]、[シート名]) となっています。
つまり、
=ADDRESS(SUMPRODUCT(($A$1:$E$4=B8)*(ROW($A$1:$E$4))),SUMPRODUCT(($A$1:$E$4=B8)*(COLUMN($A$1:$E$4))),4)
こういうことです。
SUMPRODUCT(($A$1:$E$4=B8)*(ROW($A$1:$E$4))) が行番号、
SUMPRODUCT(($A$1:$E$4=B8)*(COLUMN($A$1:$E$4))) が列番号、
4 が参照の種類 です。「$A$1」や「A$1」というようなやつです。4は相対参照なので 「A1」 のような形式を指します。
SUMPRODUCT関数
次にSUMPRODUCT関数。
これは、SUMPRODUCT(配列1, [配列2], [配列3], …) となっており、
計算式は、(配列1の1番目×配列2の1番目×配列3の1番目×…)＋(配列1の2番目×配列2の2番目×配列3の2番目×…)＋…といった感じです。
SUMPRODUCT(($A$1:$E$4=B8)*(ROW($A$1:$E$4))) の場合は引数は配列1のみで、$A$1:$E$4 が配列です。
つまり、
($A$1=B8)×(ROW($A$1))＋($B$1=B8)×(ROW($B$1))＋… ＋($E$4=B8)×(ROW($E$4)) となるわけです。
ここで、おや？と思うかもしれません。
($A$1=B8) これはなんだ、と。
これは代入ではなく論理式です。
例えば、IF関数で、IF($A$1=B8, “○”, “×”) のように書いたりするあれです。
$A$1=B8が真であれば、TRUE、すなわち、1 となり、
$A$1=B8が偽であれば、FALSE、すなわち、0 となるのです。
ROW関数 COLUMN関数
最後に、ROW関数とCOLUMN関数。
これはそれぞれ、ROW([参照])、COLUMN([参照]) となっており、参照の行や列を返します。
ROW($A$2)であれば2を、COLUMN($C$1)であれば3を返すということです。
範囲内の指定した文字列のセル番地を取得する
それでは、もう一度始めの式を見てみます。
これは、
=ADDRESS(SUMPRODUCT(($A$1:$E$4=B8)*(ROW($A$1:$E$4))),SUMPRODUCT(($A$1:$E$4=B8)*(COLUMN($A$1:$E$4))),4)
こうなります。
$A$1:$E$4=B8 は指定した文字列と等しいときに1、それ以外は0となるので、この式は、指定した文字列の行と列を取得し、参照を相対参照で表示する、ということなのです。
最後に、
ここまで読めば気づいた人もいるかもしれませんが、
範囲内の複数のセルに同じ文字列が存在してはいけない
という制約があります。