やりたいこと
- 製品A、製品Bなど、製品種別と金額がまとまった商品マスタを参照して、ある人が購入した製品の合計金額を【 vlookupを使わずに1行で 】計算する
上図のB2〜B18は、vlookupで右側のマスタから金額を参照。その合計をB19にSUMで返しています。このvlookupによる参照を使わないで、B19だけで合計を計算します。
関数
B19：SUMPRODUCT((TRANSPOSE(A2:A18)=F2:F6)*(G2:G6))
あくまで個人的な備忘録なのと面倒なので、詳細は割愛します（ひどい）。知人に頼まれて少し考えてみたのですが、わりと簡単にできました。もっとスマートにできるのかもですが（実際できそう）、今の自分にはこれが限界です。
個人的にvlookupが大嫌いなのと（とにかくシートが重くなる）、ミス撲滅の点からも関数は少ない・短いほうがいいので、自分の仕事にも応用してみようと思います。意外と使えそう。
……SUMPRODUCTもそこそこ重いですけどね。
（2022/08/20追記）
記事を見た知人から「詳しく書いて」と頼まれたので、ちょっとだけ詳しく書き残しておこうと思います。といっても、上の関数を一つ一つ分解して試してもらえれば、どんな挙動をしているのかは分かると思うので、蛇足な感じがすごいですが。
なお、どんな挙動をするかはシートに関数を入れれば一発なので、結果画像は割愛します。気になる人は、実際に手を動かしてみてください。
TRANSPOSE(A2:A18)
TRANSPOSE関数は、あまりメジャーではないですが、簡単にいえば指定した範囲の行列を入れ替えます。ひとつ注意としては、配列を返すという点です。
TRANSPOSE(A2:A18)=F2:F6
これは条件判定です。TRANSPOSEに格納した配列とF列で、一致したセルにTRUEを、しなかったセルにFALSEを返します。
なお、この挙動はArrayFormula((A2:A18)=F2:F6)でもだいたい同じことができますが（この場合、論理値が表形式ではなく、1列に並びます）、次の論理値を金額に置換するステップでエラーが返ります。
(TRANSPOSE(A2:A18)=F2:F6)*(G2:G6)
論理値TRUEを各製品の金額に置き換えます。TRUEは、かけ合わせた値（ここではG2:G6のうち、合致する製品種別に紐づく金額）に置換できます。FALSEは、どんな数字をかけ合わせても0を返します。
SUMPRODUCT((TRANSPOSE(A2:A18)=F2:F6)*(G2:G6))
表示された金額を合算します。配列を計算するのでSUMPRODUCTを使っていますが、SUMを配列数式に変換してもOKです。
とりあえず、そんなところです。
眠いので、寝ます。
