[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数条件の関数について』(Lila)
いつもお世話になっております。
再び関数の質問をさせて頂きに参りました。
現行の関数に更に条件を追加したいのです。
どのような関数を使用すればいいのか、アドバイスお願い致します。
下記のような表があり、【K】列にツールを使用した個数、【L】列にそれによって回復した個数、【M】列に治らなかった事由があるとします。
現在の関数では、
=COUNTIFS($M$290:$M$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,"<>UhV",$O$290:$O$976,"<>L1-20",$O$290:$O$976,"<>LHG0",$O$290:$O$976,"<>LBBG50",$O$290:$O$976,"<>DDU100")
【M】列にあるそれぞれの事由の個数のみしか拾って来れません。(排他しているのは、【O】列の対象外品です)
これを、例えば上から2つめのような場合や、下から2つめのような場合の計算をしたいのです。
前者の場合でしたら、【K】列-【L】列(2-1=1)で【その他の原因】に「1」を追加を、後者の場合も同様に(3-0=3)で【その他の原因】に「3」を追加し、後述の【欲しい結果】のように記載できるようにしたいのです。
上記の関数に(K列-L列)を追加するには、どのような関数がありますでしょうか?
ご教示よろしくお願い致します。
【表】
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K]|[L]|[M] |[N]|[O] |[P] [301]|SR20150819-301408|JV5482111-365 |L12AD599 |00337702|承認済み|盛岡G |○○△×FsnauobeeeeFggggg |2015/11/20|はい |使用した| 1 | 0 |その他の原因| |OD21 |4c [337]|SR20151120-319590|JV33JCCsah156 |L12CD744 |00351943|申請中 |西東京G |××○BatubatuMaru |2015/11/20|はい |使用した| 2 | 1 |その他の原因| |OD21 |4c [382]|SR20151119-318691|C0000659444444881125dsal |N14CE424 |00351077|承認済み|北関東G |××○BatubatuMaru |2015/11/19|はい |使用した| 1 | 1 | | |OD21 |6c+2W [383]|SR20151119-318737|HGho-130 |G006B593 |00351119|承認済み|仙台G |○△△××○BatubatuMaru |2015/11/19|はい |使用した| 1 | 0 |劣化 | |OD21 |4c [416]|SR20150819-301191|HOHOHOFAEBBBBBBDSDWRGTTT |M938E433 |00337485|承認済み|北関東G |×○○×○BatubatuMaru |2015/11/19|はい |使用した| 1 | 0 |傷 | |OD21 |6c [440]|SR20151118-318451|VDAEHG35 |G009B723 |00350898|申請中 |阪和G |××△○×○BatubatuMarue |2015/11/19|はい |使用した| 3 | 0 |その他の原因| |OD21 |6c [441]|SR20151118-318447|CCCCDEG9 |H016B715 |00350899|申請中 |城南G |××○BatubatuMaru |2015/11/19|はい |使用した| 1 | 0 |Wh(白) | |OD21 |6c+2W
【現在の結果】
傷 劣化 Wh(白) その他の原因
1 1 1 3
【欲しい結果】
傷 劣化 Wh(白) その他の原因
1 1 1 5
< 使用 Excel:Excel2013、使用 OS:Windows7 >
COUNTIFS()ではなく、SUMIFS()を使って(条件は同じ)、K列計からL列計を差し引くくらいじゃないですか?
=SUMIFS($K$290:$K$976,$M$290:$M$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,"<>UhV",$O$290:$O$976,"<>L1-20",$O$290:$O$976,"<>LHG0",$O$290:$O$976,"<>LBBG50",$O$290:$O$976,"<>DDU100")-SUMIFS($L$290:$L$976,$M$290:$M$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,"<>UhV",$O$290:$O$976,"<>L1-20",$O$290:$O$976,"<>LHG0",$O$290:$O$976,"<>LBBG50",$O$290:$O$976,"<>DDU100")
えらく長い式になりますけど・・
(半平太) 2015/11/27(金) 19:16
SUM系だと数値のみしかカウントしないと思っていたのですが、文字列でも大丈夫なのですね!
(Lila) 2015/11/28(土) 18:24
>SUM系だと数値のみしかカウントしないと思っていたのですが、文字列でも大丈夫なのですね!
勘違いされているといけないので、念のため書いて置きます。
SUMIFSですから、条件部分に文字列を使うのは一向に問題ないです。
しかし、合計する対象は数値じゃないとならないです。
(条件は同じ)と上述しましたけど、 SUMIFSの第一引数(合計対象範囲)は書き加える必要があります。
(半平太) 2015/11/28(土) 19:36
> SUMIFSですから、条件部分に文字列を使うのは一向に問題ないです。
あ、はい。
こちらの書き方が悪かったですね^^;
条件に乗せる事も不可能だと思っていたので、条件に使用できると聞いて、なるほど、となりました。
実行してみて、結果、しっかりと反映させることができました^o^!
ありがとうございます!
もうひとつすみません。
この条件式をなるべく短くしようと調べていて、"<>"の含めない部分を"{}"で纏める事が出来る、というのを聞いて、書いて見たのですが、どうもうまく反映されないようなのですが、ここは何か書き方が間違っていたりしますか・・・??
=SUMIFS($K$290:$K$976,$M$290:$M$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,"<>"&{"UhV","L1-20","LHG0","LBBG50","DDU100"})-SUMIFS($L$290:$L$976,$M$290:$M$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,"<>"&{"UhV","L1-20","LHG0","LBBG50","DDU100"}) (Lila) 2015/11/30(月) 09:34
>"<>"の含めない部分を"{}"で纏める事が出来る
ははぁー、よく見ると同じ範囲を何回も使っている数式だったんですね。
でも、SUMIFSは「且つ」条件ですよ。
一方、{}を使った場合、各配列の要素ごとにカウントされますので、「又は」条件にしかならないです。
そうなると、(無条件) − SUM(={}) と云う形にする必要があります。
※何故なら、「AでもなくBでもない」数 = 「全ての」数-「AまたはBである」数
すると、こんなのになって大差ないです。 ↓ =SUMIFS($K$290:$K$976,$M$290:$M$976,G4098,$I$290:$I$976,$H$4097)-SUM(SUMIFS($K$290:$K$976,$M$290:$M$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,{"UhV","L1-20","LHG0","LBBG50","DDU100"}))-SUMIFS($L$290:$L$976,$M$290:$M$976,G4098,$I$290:$I$976,$H$4097)+SUM(SUMIFS($L$290:$L$976,$M$290:$M$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,{"UhV","L1-20","LHG0","LBBG50","DDU100"}))
それより「K列 − L列」、つまり、未回復数の列を創設する方が簡明ではないですか? それで数式は半分にできます。
(半平太) 2015/11/30(月) 10:58
> それより「K列 − L列」、つまり、未回復数の列を創設する方が簡明ではないですか? それで数式は半分にできます。
表に列を追加して、「K-L」の計算式を無くして関数式を短くする、という事でしょうか?
(Lila) 2015/11/30(月) 11:12
>> それより「K列 − L列」、つまり、未回復数の列を創設する方が簡明ではないですか? それで数式は半分にできます。 > 表に列を追加して、「K-L」の計算式を無くして関数式を短くする、という事でしょうか? ↑ K列の計−L列の計 の意味ならその通りです。
新しい列の290行目に =K290-L290 と入力して下にコピーし、 その列をSUMIFSの第一引数に指定すれば、一つのSUMIFSで足ります。
(半平太) 2015/11/30(月) 12:13
計算する必要が無くなるので、最初のSUMIF関数式のみで良くなるのですね。
(例えば、M列に計算列を作成した場合
=SUMIFS($M$290:$M$976,$L$290:$L$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,"<>UhV",$O$290:$O$976,"<>L1-20",$O$290:$O$976,"<>LHG0",$O$290:$O$976,"<>LBBG50",$O$290:$O$976,"<>DDU100")
又は
SUMIFS($M$290:$M$976,$L$290:$L$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,{"UhV","L1-20","LHG0","LBBG50","DDU100"})
とすれば良いのでしょうか?)
レイアウト変更は相談しないと一存では決められませんので、その辺りは聞いてみたいと思います。
とりあえずは最初にご教示くださった式で対応して行きますが、変更できる場合は、しようかな、と思います。
(Lila) 2015/11/30(月) 13:01
>又は >SUMIFS($M$290:$M$976,$L$290:$L$976,G4098,$I$290:$I$976,$H$4097,$O$290:$O$976,{"UhV","L1-20","LHG0","LBBG50","DDU100"}) >とすれば良いのでしょうか?)
こっちはダメなんですぅ。 何故かは、その筋の専門家にお任せすることにします。
(半平太) 2015/11/30(月) 14:47
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.