定義されている数値を参照し、平均値を出す数式を組みたい

Question

実現したいこと

Googleスプレッドシートにて、
事前に定義されている、ステータスに応じた進捗率（G2:H5）を参照し、
B7:B9に各項目のステータスに応じた進捗率を自動で表示する数式を組みたいと考えています。

発生している問題・分からないこと

最初から数値を参照する形であればAVERAGE関数が使えるのですが、
定義された文字を数値に変換してから平均を出すという方法がわかっておりません。
もし数式を用いて要件を満たすことが可能でしたら、ご協力いただけますと幸いです。

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

AVERAGEIFなども試しましたが、用途がやや異なるように思います。

補足

数式を入れるのはB7セルだけで、ARRAYFORMULA関数等を使い、縦方向に自動で計算されることが望ましいですが、
難しければ1つずつのセルに入れる形でも問題ありません。

Comments

[kikukiku]

B7セルに下記を設定。
=(IFERROR(VLOOKUP(C7,$G$2:$H$4,2,FALSE),0)+IFERROR(VLOOKUP(D7,$G$2:$H$4,2,FALSE),0)+IFERROR(VLOOKUP(E7,$G$2:$H$4,2,FALSE),0))/3

[thre]

ご回答誠にありがとうございます。
試させていただいたところ、目的を満たしていることが確認できました。
空欄(エラー)を0扱いにしつつ、VLOOKUPの使う方法が勉強になりました。
活用させていただきたいと思います。ご教示ありがとうございました。

Answer 1

たとえば以下だったします

前準備

*	A	B	C	D
1		完了	完了	完了
2		完了	対応中
3		未対応	対応中
4

この場合、A1には以下を記載します。
=AVERAGE(ARRAYFORMULA(SWITCH(B1:D1,"完了",100,"対応中",50,"未対応",0,"",0)))
そしてA1をA2からA4にコピペすると結果以下のようになります。

結果

*	A	B	C	D
1	100	完了	完了	完了
2	50	完了	対応中
3	16.67	未対応	対応中
4	0

追記

命題に合わせてG:H列を元表にしたいのであればB7はこう

=AVERAGE(ARRAYFORMULA(SUMIF(G:G,C7:E7,H:H)))

Comments

[thre]

ご回答誠にありがとうございます。
いずれも目的を満たせる式であることを確認しました。
複雑な式をイメージしていましたが、SUMIF関数を用いることでコンパクトに実現できる点が参考になりました。
別欄で参照場所を用意しない際は、SWITCH関数も試してみたいと思います。
ご教示ありがとうございました。

Answer 2

テーブルを参照して数値に変更したいなら、XLOOKUP が使えそうです(VLOOKUPでも)。
ARRAYFORMULA でまとめて XLOOKUP して、それのAVERAGE をとればいいのではないでしょうか。

B7 のセルに入れる式としては以下のものでどうでしょう。あとはコピーしてください。
XLOOKUP で空セルを検索するのはうまくいかなかったので、IFで場合分けしています。

=AVERAGE(ARRAYFORMULA(IF(C7:E7="",0,XLOOKUP(C7:E7,$G$2:$G$5,$H$2:$H$5))))

エラー値をゼロにするのでもいいならもう少しシンプルにできます。

=AVERAGE(ARRAYFORMULA(XLOOKUP(C7:E7,$G$2:$G$5,$H$2:$H$5,0)))

ARRAYFORMULA関数等を使い、縦方向に自動で計算されることが望ましい

AVERAGE が配列を受け取る関数なので、ARRAYFORMULA(AVERAGE(...)) という形にはできないです。AVERAGE を使わなければいいので、

=ARRAYFORMULA((IF(C7:C9="",0,XLOOKUP(C7:C9,G2:G5,H2:H5))+IF(D7:D9="",0,XLOOKUP(D7:D9,G2:G5,H2:H5))+IF(E7:E9="",0,XLOOKUP(E7:E9,G2:G5,H2:H5)))/3)

もしくは

=ARRAYFORMULA((XLOOKUP(C7:C9,G2:G5,H2:H5,0)+XLOOKUP(D7:D9,G2:G5,H2:H5,0)+XLOOKUP(E7:E9,G2:G5,H2:H5,0))/3)

みたいにすればできますが、ちょっとわかりにくいように思います。

Comments

[thre]

ご回答誠にありがとうございます。
いずれの形でも目的を満たせることを確認しました。

参照範囲が不変であれば2点目のXLOOKUPを形式を使ってみたいと思います。

1点目のIFを使う形式は、運用の過程で空欄=0以外にもイレギュラーなルールを追加する、
となった場合にもIFSに変えることで適応できる点が良いと感じました。

ARRAYFORMULA関数を用いる場合もご丁寧に解説していただきありがとうございます。
もし今後シートの行が膨大化するようであればこちらを参考にさせていただきたいと思います。

ご教示ありがとうございました。