エクセル関数の質問

質問

いつも回答して頂きありがとうございます。

設定用シートの
A列　動作内容が入力されている 例）a b c d e...
B列　A列の動作内容に対応した動作時間が入力されている 例)0:250:00 0:60:00 ...
※セルの書式設定で分で表示
C列　A列の動作内容に対応した動作時間が入力されている 例)0:250:00 0:60:00
※セルの書式設定で分で表示

入力用シートのA列に動作内容を入力するとB6に動作内容に応じた動作時間の合計（設定用シートのB列を参照）が表示されるようにしたいと前回質問させて頂きましたが、この時、入力用シートのA列にaが含まれていた場合、設定用シートのC列を参照し合計をだす方法はあるのでしょうか？御教授お願い致します。

回答No.3

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

なんとなくわかりましたが、
>>A列　動作内容が入力されている 例）a b c d e...
のような書き方では 一つのセルに入っているのか、一つずつのセルに入っているのかわかりません。

=IF(論理式,真の場合,偽の場合)
「論理式」を先に計算し、真なら「真の場合」のみを計算するので、「偽の場合」に何を書いてもかまいません。
よって、提示された形式でも問題ないと思います

E6セルに
=SUM(SUMIF($A$2:$A$5,E2:E4,IF(ISNA(MATCH("a",E2:E5,0)),$B$2:$B$5,$C$2:$C$5)))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
または
=SUMPRODUCT(SUMIF($A$2:$A$5,E2:E4,IF(ISNA(MATCH("a",E2:E5,0)),$B$2:$B$5,$C$2:$C$5)))
[Enter] で確定

=SUMIF(範囲,検索条件,合計範囲)
検索条件が複数あるので配列として認識させる必要があります。ヘルプを超えたのテクニックを使っています。

ISNA(MATCH("a",E2:E5,0))
"a"が見つからなかったらTRUE(真)を返す
見つかったらFALSE(偽)を返す

ご提示された数式では
=COUNTIF(範囲,検索条件)
で"a"の数を数えて判断させることで有無を判断しています

#画像添付は質問を立ち上げたときにのみできます

お礼

あまり分からない私の為に、丁寧に解説までして頂いてありがとうございました。とてもシンプルで分かり易かったです。貴重な時間をこの質問の為に割いて頂きとても感謝しています。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

例を3個以上は提示されると、質問が伝わりやすくなります。
図も添付できるので、参考図として使うことによりもっと伝わりやすくなると思います。

以下、意図している内容になっているか、確認ください
F2セルに
=IF(SUBSTITUTE(E2,"a","")=E2,"a含まず",VLOOKUP("a",A:C,3,FALSE))
下へオートフィル
範囲のいちばん左で"a"を下方向に検索し、見つかったところで範囲の3列目

F2セルに
=IF(SUBSTITUTE(E2,"a","")=E2,"a含まず",SUMIF($A$2:$A$5,"a",$C$2:$C$5))
下へオートフィル
A列でaと一致するところでC列の集計
設定シートにおいて、A列に"a"が複数ある場合に動作が異なる

お礼

画像の添付方法がいまいち分からないので、いつも文字で説明しています。図を見る限り、自分の思った内容ではないです。こちらの質問の仕方が悪かったんですね。すみません。

図の入力の列に a b c d と入力していきます
仮に a b d　の時は 設定１の時間を参照し、a b d の合計を算出する。
b c d の時は　設定2の時間を参照し、b c d の合計を算出する。

という感じです。

で、ネットで調べてみた所、
CountIFが使えるっぽいので、使用して作成してみました。

=IF(COUNTIF(A:A,"上"),SUMPRODUCT(SUMIF(設定!A:A,A2:A100,設定!B:B))+SUM(設定!B1,設定!B5:B7),SUMPRODUCT(SUMIF(設定!A:A,A2:A100,設定!C:C))+SUM(設定!C1,設定!C5:C7))

※SUMPRODUCTの式はここで教えて頂いたものです。

他に短くできる方法はありませんか？

回答No.1

tsubuyuki

> 入力用シートのA列にaが含まれていた場合、
> 設定用シートのC列を参照し合計をだす方法はあるのでしょうか？

と、ありますが、これは
・Ａ列に一カ所でも"a"が有ったら合計
・Ａ列の"a"を含むセルだけを合計
どちらでしょうか？

前者であれば、IF関数とFIND関数やSEARCH関数を組み合わせると出来そうです。
後者であれば、SUMIF関数やSUMPRODUCT関数などが使えそうです。

どちらにしても「作業列」を作るとわかりやすくなりそうですね。

お礼

質問って難しいですね。
入力シートのA列に
A1セル a
A2セル b
A3セル c
みたいな感じに入力しています。

この a があった場合、C列の動作時間を選択するみたいな感じです。
　　 a がなかった場合、B列の動作時間を選択します。

で、ネットで調べてみた所、
CountIFが使えるっぽいので、使用して作成してみました。

=IF(COUNTIF(A:A,"上"),SUMPRODUCT(SUMIF(設定!A:A,A2:A100,設定!B:B))+SUM(設定!B1,設定!B5:B7),SUMPRODUCT(SUMIF(設定!A:A,A2:A100,設定!C:C))+SUM(設定!C1,設定!C5:C7))

※SUMPRODUCTの式はここで教えて頂いたものです。

他に短くできる方法はありませんか？