[[20151127161415]] 『複数条件の関数について』(Lila) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『複数条件の関数について』(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


> 新しい列の290行目に =K290-L290 と入力して下にコピーし、
> その列をSUMIFSの第一引数に指定すれば、一つのSUMIFSで足ります。

計算する必要が無くなるので、最初の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


{}の方はなかなか扱いが難しいのですかね・・・^^;
<>の方で、一つ一つ並べた方を使用します^^;
(Lila) 2015/11/30(月) 15:02

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.