[[20150926114633]] 『2つの条件(文字)を満たしたものをカウントしたax(RinRinRin) ページの最後に飛ぶ

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

 

『2つの条件(文字)を満たしたものをカウントしたい。(結合セルがあり、セルを指定したい)』(RinRinRin)

初めまして。
仕事で使っているシフト表があります。
その表から2つの条件を満たしたものをカウントしたいのですが
上手くいかなくて悩んでおります。
COUNTIFS関数では出来ず、SUMPRODUCT関数でもカウントされませんでした。
結合セルがあり、条件の入っているセルの場所も連続していません。
こういう場合どうすればカウント出来るのか教えてください。


使用中のシフト表です。
 A B C      D E         FG〜 HIJKLMN繰り返し
1場所(A〜Cセル結合) 内容(D,Eセル結合) 品物
 場所は10か所からリスト形式になっています。内容は4種類です。
2 氏名
3時間
4場所(A〜Cセル結合) 内容(D,Eセル結合) 品物 
 を4回繰り返す 

別シートに
場所1 内容1 カウント数
場所1 内容2 カウント数
場所1 内容3 カウント数
場所1 内容4 カウント数
場所2 内容1 カウント数
場所2 内容2 カウント数

場所10 内容4 カウント数
を作ってありますが、別シートでなくても
良い方で作成いたします。

一日の集計をしたいためです。
場所と内容の一致したものの合計をカウントしたいのです。
何か良い方法を教えてください。

< 使用 Excel:Excel2007、使用 OS:Windows7 >


 場所と内容だけでいいんですかね?
 内容は1字違うと別カウントとか?
 あとHIJKKLMN繰り返し、と有りますが、それは場所と内容の組み合わせが繰り返されると
 いうことですか?
(稲葉) 2015/09/26(土) 12:59

早速、ありがとうございます!!

はい。
場所と内容の一致したものを件数としてあげたいんです。
内容は4種類に分類されています。アルファベットでの種類わけです(例 SO,NN,MW,K)
場所は営業先の名前です。

はい。HIJKLMNは場所と内容と品物が5回繰り返されています。
(RinRinRin) 2015/09/26(土) 13:05


 ちょっと見難いですが、だいたいこんなイメージですか?
 例は列方向に3回しか繰り返していませんが、これがあと2回続くということですよね?
 これがずれていると話にならんので、ご確認ください。

     |[A]   |[B]|[C]|[D]   |[E]|[F]   |[G]|[H]   |[I]|[J]|[K]   |[L]|[M]   |[N]|[O]   |[P]|[Q]|[R]   |[S]|[T]   |[U]
 [1] |場所1____|内容1__|品物1__|場所2____|内容1__|品物1__|場所3____|内容1__|品物1__
 [2] |氏名  |   |   |      |   |      |   |氏名  |   |   |      |   |      |   |氏名  |   |   |      |   |      |   
 [3] |時間  |   |   |      |   |      |   |時間  |   |   |      |   |      |   |時間  |   |   |      |   |      |   
 [4] |場所1____|内容2__|品物1__|場所2____|内容2__|品物1__|場所3____|内容1__|品物1__
 [5] |氏名  |   |   |      |   |      |   |氏名  |   |   |      |   |      |   |氏名  |   |   |      |   |      |   
 [6] |時間  |   |   |      |   |      |   |時間  |   |   |      |   |      |   |時間  |   |   |      |   |      |   
 [7] |場所1____|内容2__|品物1__|場所2____|内容4__|品物1__|場所3____|内容1__|品物1__
 [8] |氏名  |   |   |      |   |      |   |氏名  |   |   |      |   |      |   |氏名  |   |   |      |   |      |   
 [9] |時間  |   |   |      |   |      |   |時間  |   |   |      |   |      |   |時間  |   |   |      |   |      |   
 [10]|場所1____|内容1__|品物1__|場所2____|内容4__|品物1__|場所3____|内容4__|品物1__ 
 [11]|氏名  |   |   |      |   |      |   |氏名  |   |   |      |   |      |   |氏名  |   |   |      |   |      |   
 [12]|時間  |   |   |      |   |      |   |時間  |   |   |      |   |      |   |時間  |   |   |      |   |      |   

 そんでもって、例が上の通りだったら、出力イメージは以下の通りですか?
     |[A]   |[B]   |[C]
 [1] |場所1|内容1|  2
 [2] |場所1|内容2|  2
 [3] |場所1|内容3|  0
 [4] |場所1|内容4|  0
 [5] |場所2|内容1|  1
 [6] |場所2|内容2|  1
 [7] |場所2|内容3|  0
 [8] |場所2|内容4|  2
 [9] |場所3|内容1|  3
 [10]|場所3|内容2|  0
 [11]|場所3|内容3|  0
 [12]|場所3|内容4|  1

 ちなみに、私には関数でできません。
 マクロでも良ければ回答続けます。
 関数を希望でしたら、その旨お伝えください。
(稲葉) 2015/09/26(土) 13:15

ありがとうございます。
回答お願い致します。
ただマクロは本当に初心者です(簡単なものしか組んだ事がありません・・・)

出来れば関数でと思っていましたが
どうしても表を作らなければならないので
がんばります。
なるべく詳しく教えて頂けますでしょうか?
よろしくお願いいたします。

(RinRinRin) 2015/09/26(土) 13:24


 関数でできない、というのはエキスパートさんのように、スマートにできないだけで
 決め打ちの力技なら可能です。
 =COUNTIFS(Sheet1!$A$1:$A$12,$A1,Sheet1!$D$1:$D$12,$B1)+COUNTIFS(Sheet1!$H$1:$H$12,$A1,Sheet1!$K$1:$K$12,$B1)+COUNTIFS(Sheet1!$O$1:$O$12,$A1,Sheet1!$R$1:$R$12,$B1)+COUNTIFS(Sheet1!$V$1:$V$12,$A1,Sheet1!$Y$1:$Y$12,$B1)+COUNTIFS(Sheet1!$AC$1:$AC$12,$A1,Sheet1!$AF$1:$AF$12,$B1)

 これでも良ければ・・?
(稲葉) 2015/09/26(土) 13:40


 急に音信が途絶えたけど、、、解決したってことかな?

 同一シートで集計

	A	B	C
21	場所1	内容1	
22	場所1	内容2	
23	場所1	内容3	
24	場所1	内容4	
・	・	・	
・	・	・	

 セル総当たりでよければ

 C21 =SUMPRODUCT(($A$1:$AC$12=A21)*($D$1:$AF$12=B21))

 ところで、なんで列を分けてるの?
(笑) 2015/09/26(土) 16:56

遅くなってしまいすみません。
仕事で外出しておりました。
今から入力してみます。

列を分けているのは、A4で印刷のためのようです。(笑い
全営業所で使用しているシフト表のめシフト表自体の変更が出来ないのです。。

(RinRinRin) 2015/09/26(土) 17:18


昨日はありがとうございました。
早速入力してみましたが
COUNTIFSを使用する方は
#VALUE!のエラーが出てしまい、、、

SUMPRODUCTの方では、
#N/Aが出てしまいます。

場所と内容は手入力ではなくリストから選ぶ形式のため
エラー表示となるのでしょうか?

(RinRinRin) 2015/09/27(日) 10:01


 こちらでテストした結果は問題ありませんでした
 つまり前提条件が異なっています
 もう一度仕様を明確にしてください
(稲葉) 2015/09/27(日) 11:42

何回もすみません。
明日は休みの為仕様を明後日作成致します。
遅くなりますがよろしくお願い致します。

(RinRinRin) 2015/09/27(日) 18:13


もう少し詳しい表にしました。

 [AB]AC[AD]AE[AF]AG[AH]AI[AJ]AK[AL]AM[AN]AO[AP]AQ[AR]AS[AT]AU[AV]AW[AX]〜[FI]							
4                                   |							
5                                   |  							
6                                   |							
7                                   |							
8                                   |							
9                                   |							
10                                  | 							
11__________________________________|__							
 [AB]AC[AD]AE[AF]AG[AH]AI[AJ]AK[AL]AM[AN]AO[AP]AQ[AR]AS[AT]AU[AV]AW[AX]〜[FI]							
13__________________________________|__							
14(AB14〜AG17)	 | 	|(AH14〜AI17)				
15場所(ドロップ  |	|←内容(ドロップダウンリスト)	 				
16ダウンリスト	 |	|				
17________|_ _|  ______________________|							
18							
19							
20__________________________________|__							
21							
22__________________________________|__							
23							
24__________________________________|__							
↓
64
場所と内容の入るセルは結合されてドロップダウンリスト表示になってます。
 場所      内容   場所    内容   場所  内容  場所 内容     場所 内容 場所内容
(AB14-AG17(AH14-AI17(AY-BD)(BE-BF)(BV-CA) (CB-CC)(CS-CX)(CY-CZ)(DP-DU)(DV-DW)(EM-ER)(ES-ET 
(AB27-AG30)(AH27-AI30)(AY
(AB40-AG43)   (AH40-AI43)
 (AB53-AG56)   (AH53-AI56)							
(AB40-AG43)   (AH40-AI43)
 (AB53-AG56)   (AH53-AI56)							

この場所と内容の一致したものをカウントしたいのです。
よろしくお願い致します。

(RinRinRin) 2015/09/30(水) 11:40


 >(AB40-AG43)   (AH40-AI43)
 > (AB53-AG56)   (AH53-AI56)							
 >(AB40-AG43)   (AH40-AI43) ← リフレイン
 > (AB53-AG56)   (AH53-AI56) ← リフレイン

 下2行、リフレインしてるけど間違い?
 タテに4ヶ所ってこと?

 #VALUE! やら #N/A やらのエラーになったという数式のことが書いてないけど、
 どんな式を入れたのかな?

 それと別に提案ですけど、その印刷用の表とリンクさせて、
 新たに集計用の表を作ったらどうですか?

 参照するセルの数は、タテ4個として、場所・内容それぞれ24個、
 そんな手間でもないでしょう。
 アレコレ考えてる間にできますよ、
 ・・・と言ってる間にできた。
 
=AB14&""	=AH14&""
=AB27&""	=AH27&""
=AB40&""	=AH40&""
=AB53&""	=AH53&""
=AY14&""	=BE14&""
=AY27&""	=BE27&""
=AY40&""	=BE40&""
=AY53&""	=BE53&""
=BV14&""	=CB14&""
=BV27&""	=CB27&""
=BV40&""	=CB40&""
=BV53&""	=CB53&""
=CS14&""	=CY14&""
=CS27&""	=CY27&""
=CS40&""	=CY40&""
=CS53&""	=CY53&""
=DP14&""	=DV14&""
=DP27&""	=DV27&""
=DP40&""	=DV40&""
=DP53&""	=DV53&""
=EM14&""	=ES14&""
=EM27&""	=ES27&""
=EM40&""	=ES40&""
=EM53&""	=ES53&""

 ↑ をコピーして、形式を選択して貼り付け〜テキストでExcelシートに貼り付け。

 あとは COUNTIFS で集計。

 ところで、それホントに印刷してA4用紙1枚で収まるの?
(笑) 2015/09/30(水) 16:28

ありがとうございます。
はい。縦に4ヶ所です。
2か所コピー消し忘れてました。

稲葉様が教えてくださった、式のセルだけかえて入力してみたのですが
T44 =SUMPRODUCT(($AB$14:$ER$56=R44)*($AH$14:$ET$56=S44))
というふうに変換してみたのですが、、、

別の表を作成してリンク致しました
上手くできました。
本当にありがとうございます!!!

縮小して印刷です。(笑)

大変助かりました。ありがとうございました!!!

(RinRinRin) 2015/09/30(水) 18:11


 SUMPRODUCTは私じゃない
(稲葉) 2015/10/01(木) 06:56

 オススメするわけじゃないけど、一応参考まで。

 > T44 =SUMPRODUCT(($AB$14:$ER$56=R44)*($AH$14:$ET$56=S44)) 

 =SUMPRODUCT(($AB$14:$EM$53=R44)*($AH$14:$ES$53=S44))
           ~~~~~~       ~~~~~~
 
 先に書いたように、無関係な列も含めて全セルを対象にしています。

 それでも計算結果に支障はない表だとしても、
 範囲が広くなればそれだけ参照するセルの数が多くなるので効率は悪い。

 最初に前提にしていた表よりも、今回の表は
 セルの数にして13倍ぐらい増えてますからね!
 前回696個 → 今回9,280個かな、たぶん。
(笑) 2015/10/01(木) 14:02

コメント返信:

[ 一覧(最新更新順) ]


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