【Excel】表の単語からラベルを探索する

今回使用した関数
SUMPRODUCT, ROW, COLUMN, INDEX, IFERROR

その他記事に出てくる関数
TOCOL, TRANSPOSE, ADDRESS

議題

ある日の質問にて。

キャラクター別の特性からステータスにスキルとレベルを入力したいとのこと。
特性表にある単語から一致するものを探し、そのスキルとレベルを入力する。

index、match では複数列にまたがる範囲を扱えなかったために質問へ至った。
面白そうやん。

アルゴリズムを考える

まずは、目的の結果を出力するまでの流れを考えてみる。

パターン1

▲このパターンは初めにやろうとしていた手順だが、知る限りの知識では前述の通り、複数列を一気に検索する術が無く。
index、match 等 + if、or でごり押しができなくもないだろうが、今回は考えないものとする。

パターン2

▲このパターンで途中まで進めていたが、後々の事を考えると頭が痛くなったので考えるのを止めた。

パターン3

今回実装したのはこのパターン。実際には少し違うかたちにはなったけど、ほぼこの考え方で進める。

ちなみに、質問してきた方は自力でこれとは別の解法を使って解決していた。目が悪すぎてあまり見えなかったのと私自身でもこの機能を実装してみたくて頭の中が解きたいで一杯だったためあまり覚えていないが、たしか、if と min を使ってかなり短く書いていたように思う。

今回の備忘録はその限りなく最適に近い解法とは全く関係無いので安心してほしい。(すまん)

一致する特性の探索と対応スキル･レベルの位置を特定する

模索パート

キャラ別の特性と特性一覧表で一致する文字列を探索、入力するスキル･レベル行列数を特定する。

とはいえ、どうやって何行目とか何列目とか特定すれば良いのか。
そこで SUMPRODUCT関数が登場する。

SUMPRODUCT とは
第一引数と第二引数で指定した範囲の積和を計算する関数。2つの範囲は同じ大きさの範囲である必要がある。
おそらく SUM + PRODUCT。

=SUMPRODUCT(範囲1, 範囲2)
例. =SUMPRODUCT(A1:A3, B1:B3)
 -> A1*B1 + A2*B2 + A3*B3
例2. =SUMPRODUCT(A1:A3, B1:B4)
 -> エラーになる。第一引数は 3つの要素なのに対し、第二引数は 4つの要素で範囲の大きさが合わない。

で、この関数もう一つ違う計算方法がある。
それが配列計算だ。範囲1 と範囲2 を * (アスタリスク) で繋げて記入する。
数学的に言うなら行列の計算なのだろう。

=SUMPRODUCT(A1:A3*B1:B3)
 -> SUMPRODUCT(A1:A3, B1:B3) と同じ値が返される。
=SUMPRODUCT(A1:B3*C1:C3)
 A1~B3の範囲と C1~C3の範囲をそれぞれ行列と捉えて計算し、総和を返す。

一つ目の範囲と二つ目の範囲の大きさが合わないように見えるかもしれないが、行列の計算なので計算は破綻しない。

掛けられる側の列数と掛ける側の行数が一致しない場合、または行列の整数倍ではない場合は計算が破綻してエラーになる。
行列の計算をしたあと、各値を足している。のかな、たぶん。
(2×2 の行列) * (2×2の行列) の計算でも整数値が返されたのでたぶんそう。

で、ここからが本筋。

行列の計算もとい、配列計算を使って行数と列数を特定していく。

=SUMPRODUCT((C3:I19=C23)*ROW(A3:A19))

掛けられる側に条件式を入れて、対応する行の行数を掛ける。
条件式の部分は、当てはまる時(trueの時) は 1、当てはまらない時(falseの時) は 0 の行列へと変換される (と思っている)。

一致する単語だけが 1 となり、その行数だけが返される値として残る。
結果的に、行数が特定できる。

同じように、列数も計算する。

=SUMPRODUCT((C3:I19=C23)*ROW(A3:A19))

この計算の弱点は、表の中に一致する値が複数ある場合である。
その場合は、0以外の値が複数出てしまい返される値がおかしくなってしまう。
つまり、表の中に同一の単語が複数存在するとこの方法は使えないのである。

結論パート

今回の SUMPRODUCT関数、端的に言うと “条件に合うセルを数える” 関数であり、そう覚えた方が楽そう。
条件が 1つの場合は ([条件1]*1) と書く。条件が 2つ以上で ([条件1]*[条件2]) と書いていく。
と、行列の計算だなんだと考えなくても良いらしい。

ROW(A3:A19) の事を考えると、配列計算、行列の計算と本質は同じと思っていた方が理解が速そうだが。

特定の値の行数を知るためには 1行目のものを。
特定の値の列数を知るためには 2行目のものを使用する。
この場合、特定の値は C23 を指す。

=SUMPRODUCT(($C$3:$I$19=C23)*ROW($A$3:$A$19))
=SUMPRODUCT(($C$3:$I$19=C23)*COLUMN($C$2:$I$2))

列数からスキルを、行数からレベルを特定する

模索パート

レベルの行数とスキルの列数はわかったけど、ここからどうすれば。
レベルの列数とスキルの行数は固定な訳だから、これらを組み合わせて何とかできないか ?

うーん

せや
こんな時は ADDRESS関数の出番でしょ。

ADDRESS関数は第一引数に行数、第二引数に列数を指定することで指定された座標のセルのアドレスを返す。

=ADDRESS(1, 2)
 -> $B$1

これに前段階で考えた行数と列数をそれぞれ入れてやれば、スキルとレベルの位置が導き出せるな !

第一引数を C3からI19の範囲から特定の値(C23) を探索し、取得した行数を指定する。
第二引数を 1 とすることで表の先頭列(A列) とする。
そうすると、該当する表側の位置が返ってくる。

=ADDRESS(SUMPRODUCT(($C$3:$I$19=C23)*ROW($A$3:$A$19)), 1)
 -> 特定の値に対応する該当スキルの場所を返す。
=ADDRESS(2, SUMPRODUCT(($C$3:$I$19=C23)*COLUMN($C$2:$I$2)))
 -> 特定の値に対応する該当レベルの場所を返す。

さて、これでスキルとレベル共に探索したい値の位置を割り出せた。
あとはこの位置を元に値を取り出せれば…。

いや待てよ

これ INDEX関数で文字列を取得できるのでは ??

INDEX関数は第一引数に探索する範囲を指定し、第二引数に範囲内から何行目の値かを指定する。
すると、指定された位置の値が返されるというものだ。
列数を指定したい場合は、第三引数に列数を指定すると良い。第三は省略可能。

=INDEX($A$3:$A$19, SUMPRODUCT(($C$3:$I$19=C23)*ROW($A$3:$A$19)) - 2)
=INDEX($C$2:$I$2, 1, SUMPRODUCT(($C$3:$I$19=C23)*COLUMN($C$2:$I$2)) - 2)

こう。
SUMPRODUCT関数の後ろにくっついている -2 は座標の基準が異なっているため、必要となってくる。
第一引数で指定している範囲は A3からA19。つまり、先頭の基準は A3 となっている。
だが、第二引数の SUMPRODUCT関数から返される値は A1 を基準とした座標である。
そのため、ずれた座標分を引いておく必要があるということ。

少々、当初の想定とは外れたがむしろ良くなったから万事オーケー。

結論パート

前段階で取得した行数･列数を INDEX関数に利用して該当の表側と表頭の値を取得する。

特定の値(C23) の先頭列(表側) を参照して値を返すには 1行目のものを。
特定の値(C23) の先頭行(表頭) を参照して値を返すには 2行目のものを使用する。

=INDEX($A$3:$A$19, SUMPRODUCT(($C$3:$I$19=C23)*ROW($A$3:$A$19)) - 2)
=INDEX($C$2:$I$2, 1, SUMPRODUCT(($C$3:$I$19=C23)*COLUMN($C$2:$I$2)) - 2)

スキルとレベルを結合して表示する

模索パート

ここから綺麗にまとめる方法ねぇ。

うーん
あーもう模索パートとか要らん。
文字列なんだから & で結合しちゃえばええねん !

値と値の間に & を忍ばせるとその値達が合体して 1つのセルに表示されるぞ !

= 1 & 2
 -> 12
= "a" & "b"
 -> ab
= a & b
 -> エラーになる。
= A1 & B3
 -> A1とB3の値が結合されて返される。

前段階までで必要な値は取得できたから、あとは脳筋で行く。たぶんこれが一番短い。

つまりこうです。

INDEX($A$3:$A$19, SUMPRODUCT(($C$3:$I$19=C23)*ROW($A$3:$A$19)) - 2) & INDEX($C$2:$I$2, 1, SUMPRODUCT(($C$3:$I$19=C23)*COLUMN($C$2:$I$2)) - 2)

結論パート

前段階で書いた関数を & で繋げる。

INDEX($A$3:$A$19, SUMPRODUCT(($C$3:$I$19=C23)*ROW($A$3:$A$19)) - 2) & INDEX($C$2:$I$2, 1, SUMPRODUCT(($C$3:$I$19=C23)*COLUMN($C$2:$I$2)) - 2)

エラー時処理を追加する

模索パート

別にここまでしなくても良い気もするが、エラーになった時の処理を追加する。

現状は、特定の値(C23) が空白である場合や特定の値が表の探索範囲に存在しない場合にエラーとなる。
ので、IFERROR関数を使う。

IFERROR関数は第一引数に指定された処理でエラーが返された時に。第二引数に指定された値を返す。

=IFERROR(SUM(), "エラーです。")
 -> 第一引数のSUMに引数が指定されておらず、エラーのため、エラーです。という文字列が代わりに返される。
=IFERROR(SUM(A1:A3), "エラーです。")
 -> A1からA3が数字であればエラーにならないので、SUMの処理結果がそのまま返される。

今回はエラーになった時、何も表示しないようにしたいので、下記のようになる。

=IFERROR(INDEX($A$3:$A$19, SUMPRODUCT(($C$3:$I$19=C23)*ROW($A$3:$A$19)) - 2) & INDEX($C$2:$I$2, 1, SUMPRODUCT(($C$3:$I$19=C23)*COLUMN($C$2:$I$2)) - 2), "")

結論パート

IFERROR関数を使用し、第一引数に前段階の処理を丸々入れる。第二引数に “” を指定し、処理のエラー時は空白を返すようにした。

=IFERROR(INDEX($A$3:$A$19, SUMPRODUCT(($C$3:$I$19=C23)*ROW($A$3:$A$19)) - 2) & INDEX($C$2:$I$2, 1, SUMPRODUCT(($C$3:$I$19=C23)*COLUMN($C$2:$I$2)) - 2), "")

今回の解法

IFERROR(INDEX($A$3:$A$19, SUMPRODUCT(($C$3:$I$19=F23)*ROW($A$3:$A$19)) - 2) & INDEX($C$2:$I$2, 1, SUMPRODUCT(($C$3:$I$19=F23)*COLUMN($C$2:$I$2)) - 2), "")

今回は特にsumproduct関数の理解に苦しんだ。
結果、正しい解釈かはわからないまでも、自分なりに理解はできた。

【おまけ】他のパターンで齧った関数達

いつか使うかもしれないので、解法には関係しないが学んだ関数の記憶を片隅に留めておきたい。

ADDRESS関数は前述したからいいか。

TOCOL関数

パターン2 において、複数列の表を 1列になおすために使用しようとした。

引数に指定された範囲を 1列に並べて表示する。並べ方は一番上の行の左から右まで並べて、端まで行ったら次の行へ進む並べ方 (説明力不足)
元の表の行優先で並べるということです。

=TOCOL(B2:D4)
 -> B2からD4の範囲を 1列に羅列する。並び方は B2, C2, D2, B3, C3, D3, B4, C4, D4

TRANSPOSE関数

パターン2 において、複数列の表を 1列になおすために使用しようとした。

範囲内の列と行を入れ替える。
数学の行列で考えた方がわかりやすい。要は転置行列を取得する関数。

=TRANSPOSE(B2:D4)
 -> 範囲内の列と行が入れ替わる。1列目が 1行目に、2列目が 2行目に...となる。

で前述の TOCOL関数と、TRANSPOSE関数を組み合わせることで元の表を縦から並べて 1列にすることができる。

=TOCOL(TRANSPOSE(B2:D4))

ROW関数

セルの行数を取得する関数。
行番号付ける時によく使う。

=ROW()
 -> この関数を書いたセルの行数を返す。A2 に書いた場合は、2 を返す。
=ROW() - 2
 -> A3 に書いた場合は 1 を返す。A1 に書いた場合は -1 を返す。

COLUMN関数

ROW関数の列数版。
セルの列数を取得する関数。私の場合こっちはあまり使わない。

=COLUMN()
 -> この関数を書いたセルの列数を返す。B1 に書いた場合は、2 を返す。