B列とE列とG列にランダムに数字が入っています。
A列にはア、イ、ウ、エ、オとカテゴリーの文字列が入っています。
このうちある条件をみたす(例えばA列にウと入っている)行のB列とE列とG列の数字の平均値をとりたいのですが、
どうすればよいでしょうか?
ifaverage関数を使おうと思いましたが、対象列が複数あり、対象列の間の列には
今回の計算の対象にはしたくない数字が入っているので、範囲指定で平均値をとることができません。
=sumif()/countif()でaverageifと同じ効果を出そうとしましたが、
数字には0があったり、セルにブランクが含まれていたりするので、やはりうまくいかないような気がします。
アドバイスお願いします。
No.3ベストアンサー
- 回答日時:
別にちょこっと手直すだけですけどね。
=SUMPRODUCT(SUMIF(A:A,"ウ",OFFSET(A:A,0,{1,4,6})))/SUMPRODUCT(COUNTIFS(A:A,"ウ",OFFSET(A:A,0,{1,4,6}),">0"))
#そーいうイレギュラーな事をしたいと、肝心な説明を手抜きするからこんな具合に二度手間になります。次回の反省点になさってください。(て言っとかないと、逆切れちゃう人が多いので。)
判りやすい方の数式は特に反応もありませんし、こちらは十分詳しくご説明したのでこれ以上解説は要らないと思いますので、もし興味があるなら同様に出来ますから自力で解明してください。
再度のご回答ありがとうございます!
質問が舌足らずだったようで申し訳ございませんでした。
実は会社で作業をしているのですが、今自宅なのですぐに試すことができないのです。
会社から掲示板サイトへの書き込みが原則禁止されているので
これから出社して早速ためしてみます!
No.2
- 回答日時:
たとえば
=SUMPRODUCT(SUMIF(A:A,"ウ",OFFSET(A:A,0,{1,4,6})))/SUMPRODUCT(COUNTIFS(A:A,"ウ",OFFSET(A:A,0,{1,4,6}),"<>"))
などのように。
#補足
AVERAGEIF系の関数では全くできません。
簡単に言えば
(ウであってB列のSUMIF、E列のSUMIF、G列のSUMIFの合計)÷(ウであってB列が空白じゃないCOUNTIFS、E列のCOUNTIFS、G列のCOUNTIFSの合計)
で計算できます。
前述のようにイミフメイな事をしなくても、ふつーに丁寧に
=(SUMIF(A:A,"ウ",B:B)+SUMIF(A:A,"ウ",E:E)+SUMIF(A:A,"ウ",G:G))/(COUNTIFS(A:A,"ウ",B:B,"<>")+COUNTIFS(A:A,"ウ",E:E,"<>")+COUNTIFS(A:A,"ウ",G:G,"<>"))
と計算するのなら判りますよね。
#参考
>数字には0があったり、セルにブランクが含まれていたりする
ブランクは当然ノーカウントですが、ゼロはふつーに計算対象です。何が言いたかったのでしょうね?
この回答への補足
ご回答ありがとうございます!
例えば、計算対象のセルに、6、8、0、2、12がそれぞれ入っていたとして、
計算したい結果は、平均値にゼロのセルをカウントしない28/4=7、なのですが、
0も分母にワンカウントされてしまうので、平均値が28/5=5.6、という結果が出てしまうのです。
No.1
- 回答日時:
averageif(excel2007以降)で出来ませんか? 平均値対象範囲は複数行が適用できます。
> 今回の計算の対象にはしたくない数字が入っているので…
これは具体的には何ですか? したくない(人の思考)? 関数が無視してくれない?
####
私も経験していますが、「数字として無効なセル」を
無効なのでエラーを返す関数 …したいことが出来ない
無効なので無視してくれる関数 …ありがたい
があって苦労します。
たとえば、「na()」はグラフでは無視するのにSUMではエラーになったり…
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル 関数について質問です。 2 2022/10/03 11:14
- Excel(エクセル) エクセルで文字列と数字が混在する列に書式設定したい。 3 2022/12/19 09:11
- Excel(エクセル) AVERAGE関数とINDIRECT関数を使ってのオートフィル 3 2023/01/12 12:00
- Excel(エクセル) B列に、A列の数字が偶数の場合は1減算した数字、奇数の場合はそのまま数字を自動表示したい 4 2022/04/16 12:01
- Excel(エクセル) Excel2019 列と列(2列)の数値の重複を調べたい 1 2023/05/11 13:35
- Excel(エクセル) 別シートに毎回異なるデータをコピーする 7 2022/06/24 09:02
- Excel(エクセル) Excelの文字列を数字に変換する方法について 6 2023/07/31 21:18
- Visual Basic(VBA) A列にある値をB列・C列にVBAで切り出し 3 2022/04/09 19:20
- Excel(エクセル) countif関数について質問 4 2022/06/14 12:11
- C言語・C++・C# [C言語] コメント文字列を無視して、数値データを読み込むプログラム部分について 5 2022/10/05 11:03
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
文字列になっている時間をVBAで...
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルで最初のスペースまで...
-
「B列が日曜の場合」C列に/...
-
2つのエクセルのデータを同じよ...
-
エクセル(勝手に太字になる)
-
エクセルの並び変えで、空白セ...
-
エクセル 文字数 多い順 並...
-
50人を数回、グループ分けする...
-
文字列に数字を含むセルを調べたい
-
エクセル 同じ値を探して隣の...
-
お店に入るために行列に並んで...
-
エクセルの表から正の数、負の...
-
Excel元に戻す方法を教えてくだ...
-
エクセルで文字が混じった数字...
-
Excel、市から登録している住所...
-
VBAで文字列を数値に変換したい
-
VBA 連続行データを5行ずつ隣の...
-
年が明けたら1年プラスされる...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
PowerPointで表の1つの列だけ...
-
エクセルで最初のスペースまで...
-
2つのエクセルのデータを同じよ...
-
エクセル(勝手に太字になる)
-
「B列が日曜の場合」C列に/...
-
エクセル 文字数 多い順 並...
-
EXCELで 一桁の数値を二桁に
-
エクセル 同じ値を探して隣の...
-
VBAで文字列を数値に変換したい
-
エクセルの並び変えで、空白セ...
-
Excelで半角の文字を含むセルを...
-
エクセルで文字が混じった数字...
-
Excel、市から登録している住所...
-
A列がない・・・A列が非表示に...
-
エクセルの表から正の数、負の...
-
[関数得意な方]教えて下さい・...
-
エクセルの項目軸を左寄せにしたい
-
エクセル 時間帯の重複の有無
-
Excelにてある膨大なデータを管...
おすすめ情報