[[20150112102639]]　『3つの複数条件カウント』（RYO）　ページの最後に飛ぶ

『3つの複数条件カウント』（RYO）

よろしくお願いします。

下図のとおり、Sheet1の
A列に"A"または"B"または"C"または"D"・・・の文字列
B列以降に"○"または"▲"または"×"の文字列が入っています。

=====
(Sheet1)

	A列	B列	C列	D列	E列	F列	G列
1行目	-	1日	2日	3日	4日	5日	6日
2行目	B	×	○	○	▲	×	○
3行目	C	○	○	○	○	○	○
4行目	A	○	○	▲	×	○	○
5行目	A	▲	○	○	×	○	○
6行目	B	▲	○	○	▲	○	○
7行目	C	○	×	○	○	○	▲
8行目	A	○	○	×	○	○	○
・
・
・
=====

そこで、
それぞれの日付けにおいて、その日が"○"かつ3日間以上勤務可能なAさんが何人いるか、
"○"かつ2日間のみ勤務可能なAさんが何人いるか・・・
というのを下図のようにSheet2にカウントしたいです。

=====
(Sheet2)

	A列		B列	C列	D列　・・・
1行目	-		1日	2日	3日
2行目	A/○/3日以上	1	0	0
3行目	A/○/2日のみ	1	2	0
4行目	A/○/1日のみ	0	1	1
2行目	A/▲/3日以上	1	0	0
3行目	A/▲/2日のみ	0	0	0
4行目	A/▲/1日のみ	0	0	1
5行目	B/○/3日以上	0	2	1
6行目	B/○/2日のみ	0	0	1
7行目	B/○/1日のみ	0	0	0
8行目	B/▲/3日以上	1	0	0
・
・
・
=====

どのような関数になるでしょうか。

＜使用 Excel：Excel2010、使用 OS：Win8 ＞

 ◆RYOさんの質問の、Sheet1とSheet2はマッチングしていますか？

（maron） 2015/01/12(月) 14:57

	A列		B列	C列	D列　・・・
1行目	-		1日	2日	3日
2行目	A/○/3日以上	1	0	0
3行目	A/○/2日	1	2	0
4行目	A/○/1日	0	1	1

 4行目の「1日」はなんで「0」なの？
 2日以上勤務できてるのに。
　
（GobGob） 2015/01/13(火) 08:00

maronさん

マッチングしています！関数では難しいでしょうか。。

GobGobさん

わかりづらく申し訳ございません。
3日以上以外は、2日のみ、1日のみとしてカウントしたいのです。。
（RYO） 2015/01/13(火) 11:07

皆さま

=COUNTIFS(Sheet1!A:A,"A",Sheet1!B:B,"○")
=COUNTIFS(Sheet1!A:A,"A",Sheet1!B:B,"▲")

で"A"の人かつ"〇"の人というような2つの条件はカウントすることが出来るのですが、
勤務可能日数別にカウントする方法が分からず困っております…。。

どうか助けて頂けないでしょうか？
よろしくお願い致します。
（RYO） 2015/01/14(水) 12:55

 同じシートで例。

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O		
1		1日	2日	3日	4日	5日	6日					1日	2日	3日	4日	5日	6日
2	B	×	○	○	▲	×	○		A	○	3日以上	2	3	1	1	0	0
3	C	○	○	○	○	○	○		A	○	2日のみ	0	0	0	0	3	0
4	A	○	○	▲	×	○	○		A	○	1日のみ	0	0	0	0	0	3
5	A	▲	○	○	×	○	○		A	▲	3日以上	0	0	0	0	0	0
6	B	▲	○	○	▲	○	○		A	▲	2日のみ	0	0	0	0	0	0
7	C	○	×	○	○	○	▲		A	▲	1日のみ	1	0	1	0	0	0
8	A	○	○	×	○	○	○		B	○	3日以上	0	2	1	0	0	0
9									B	○	2日のみ	0	0	1	0	1	0
10									B	○	1日のみ	0	0	0	0	0	2
11									B	▲	3日以上	0	0	0	0	0	0
12									B	▲	2日のみ	1	0	0	0	0	0
13									B	▲	1日のみ	0	0	0	2	0	0
14

 L2 =SUM((MMULT(($A$2:$A$100=$I2)*(B$2:$G$100=$J2)*(B$2:B$100=$J2),(ROW($A$1:INDEX($A:$A,7-COLUMN(A1)))>0)*1)>2)*1)
 L3 =SUM((MMULT(($A$2:$A$100=$I3)*(B$2:$G$100=$J3)*(B$2:B$100=$J3),(ROW($A$1:INDEX($A:$A,7-COLUMN(A1)))>0)*1)=2)*1)
 L4 =SUM((MMULT(($A$2:$A$100=$I4)*(B$2:$G$100=$J4)*(B$2:B$100=$J4),(ROW($A$1:INDEX($A:$A,7-COLUMN(A1)))>0)*1)=1)*1)

 L2:L4を選択して下へコピー。 L列を右へコピー。
　
（GobGob） 2015/01/14(水) 13:13

GobGobさん

ご回答本当にありがとうございます！！

A列が"A"かつB列が"○"かつ"3日以上"の人は、1人だけなのでL2セルの答えが『1』になってほしいのですが…。
初心者なのものでどこを変更すれば正しく出るか分からず困っております。。

再度助けて頂けないでしょうか？
（RYO） 2015/01/14(水) 18:21

 どう考えても2だと思うんだけど。
 １）3日以上の定義って何なんですか？
 　　G列までの○の数じゃないの？
 ２）勤務可能の定義って○？▲？
 　　それとも○は▲を含むの？
 ３）普通に考えてAさんは特定の一人だと思うんだけど、
 　　「Aさんが何人いるか」って分身できるんですか？
（稲葉） 2015/01/14(水) 19:01

 >A列が"A"かつB列が"○"かつ"3日以上"の人は、1人だけなので

 ・A列「A」
 ・B列「○」
 ・3日以上 ⇒ B列以降が「○」 3つ以上ある。

 として。

	A	B	C	D	E	F	G
1		1日	2日	3日	4日	5日	6日
2	B	×	○	○	▲	×	○       Bで×で○3つ ⇒ ×
3	C	○	○	○	○	○	○       Cで○で○6つ ⇒ ×
4	A	○	○	▲	×	○	○       Aで○で○4つ ⇒ ◎
5	A	▲	○	○	×	○	○       Aで▲で○4つ ⇒ ×
6	B	▲	○	○	▲	○	○       Bで▲で○4つ ⇒ ×
7	C	○	×	○	○	○	▲       Cで○で○4つ ⇒ ×
8	A	○	○	×	○	○	○       Aで○で○5つ ⇒ ◎

 ・・・ ◎対象 ⇒ 2つ。

 じゃないの？ よーわからん。。。。
　
（GobGob） 2015/01/14(水) 19:05

 もしかして。。。

 「3日以上」 でなくて 「連続して3日以上」なんじゃないの？
　
（GobGob） 2015/01/14(水) 19:07

稲葉さん

コメントありがとうございます。説明が足りなすぎました…。申し訳ありません。。

 １）3日以上の定義って何なんですか？
 　　⇒その日から次の×の日まで○or▲が3日以上連続で続く場合を3日以上とします。
　　　 その日が〇や▲で次の日が×だと1日のみとしてカウントしたいのです。
　　　 G列までの○の数という意味ではなかったのです。。
 ２）勤務可能の定義って○？▲？
 　　⇒○も▲も勤務可能日としています。
 ３）普通に考えてAさんは特定の一人だと思うんだけど、
 　　「Aさんが何人いるか」って分身できるんですか？
　　⇒便宜上「Aさん」と書いてしまいましたが、Aグループに所属している人というイメージです。。

GobGobさん

何度も確認して頂き、貴重なお時間ありがとうございます＞＜！

	A	B	C	D	E	F	G
1		1日	2日	3日	4日	5日	6日
2	B	×	○	○	▲	×	○    Bで× ⇒ その日が〇や▲ではないのでカウント対象外。
3	C	○	○	○	○	○	○    Cで○でその日を含め後ろ3日間に×がない
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　⇒ C/○/3日以上としてカウント
4	A	○	○	▲	×	○	○    Aで○でその日を含め後ろ3日間に×がない
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　⇒ A/○/3日以上としてカウント
5	A	▲	○	○	×	○	○    Aで▲でその日を含め後ろ3日間に×がない
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　⇒ A/▲/3日以上としてカウント
6	B	▲	○	○	▲	○	○    Bで▲でその日を含め後ろ3日間に×がない
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　⇒ B/▲/3日以上としてカウント
7	C	○	×	○	○	○	▲    Cで○で次の日が× ⇒ C/○/1日のみとしてカウント
8	A	○	○	×	○	○	○    Aで○で翌々日が× ⇒ A/○/2日のみとしてカウント

というように考えたいです。
その日が〇or▲だったときに、次の×までの日数別にカウントしたいです。

どうかよろしくお願い致します＞＜！
（RYO） 2015/01/14(水) 19:31

	A	B	C	D	E	F	G	H	I
1				1日	2日	3日	4日	5日	6日
2	A	○	3日	1	0	0	1	3	3
3	A	○	2日	1	2	0	0	0	0
4	A	○	1日	0	1	1	0	0	0
5	A	▲	3日	1	0	0	0	0	0
6	A	▲	2日	0	0	0	0	0	0
7	A	▲	1日	0	0	1	0	0	0
8	B	○	3日	0	2	1	0	1	2
9	B	○	2日	0	0	1	0	0	0
10	B	○	1日	0	0	0	0	0	0
11	B	▲	3日	1	0	0	1	0	0
12	B	▲	2日	0	0	0	0	0	0
13	B	▲	1日	0	0	0	1	0	0

 D2 =SUM((MMULT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!B$2:B$100=$B2)*(Sheet1!B$2:D$100<>"×"),{100;10;1})=111)*1)
 D3 =SUM((MMULT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!B$2:B$100=$B2)*(Sheet1!B$2:D$100<>"×"),{100;10;1})=110)*1)
 D4 =SUM((TEXT(MMULT((Sheet1!$A$2:$A$100=$A4)*(Sheet1!B$2:B$100=$B4)*(Sheet1!B$2:D$100<>"×"),{100;10;1}),"［<100］1000;0")*1<110)*1)

 D2:D4を下へコピー。 D列右へコピー。

 ※ Sheet1の表の列最後から右2つはデータなしのこと。
　
（GobGob） 2015/01/15(木) 08:19

 >※ Sheet1の表の列最後から右2つはデータなしのこと。

 Sheet1の表の列最後から右2つに「×」入れてくださいなｗ。

 ※やりようがあるだろうけどめんどくさいんでｗ
　
（GobGob） 2015/01/15(木) 08:25

 結局修正ｗ。

 5日目以降が駄目なので

 D2 =SUM((MMULT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!B$2:B$100=$B2)*((Sheet1!B$2:D$100="○")+(Sheet1!B$2:D$100="▲")),{100;10;1})=111)*1)
 D3 =SUM((MMULT((Sheet1!$A$2:$A$100=$A3)*(Sheet1!B$2:B$100=$B3)*((Sheet1!B$2:D$100="○")+(Sheet1!B$2:D$100="▲")),{100;10;1})=110)*1)
 D4 =SUM((TEXT(MMULT((Sheet1!$A$2:$A$100=$A4)*(Sheet1!B$2:B$100=$B4)*((Sheet1!B$2:D$100="○")+(Sheet1!B$2:D$100="▲")),{100;10;1}),"[<100]1000;0")*1<110)*1)

 ※今度こそ 「Sheet1の表の列最後から右2つはデータなしのこと。」でよろしく。
　
（GobGob） 2015/01/15(木) 08:40

GobGobさん

回答ありがとうございます！
本当に助かりました＞＜！

かなり複雑な関数になってしまうのですね。。
すぐに勉強して理解できるレベルではないとは思いますが頑張ります。。

最後に怒られることを覚悟で質問させて下さい＞＜

1）もし勤務可能日として扱う文字列が〇と▲の2種類ではなく、
　 ★や◆など種類が増えていった場合は、

 　((Sheet1!B$2:D$100="○")+(Sheet1!B$2:D$100="▲")+(Sheet1!B$2:D$100="★")+(Sheet1!B$2:D$100="◆"))
　 のように変更していけばよろしいでしょうか？

2) 現在勤務可能日数を「3日以上」「2日」「1日」でカウントを分けておりますが、
　これが「4日以上」「3日」「2日」「1日」でカウントしたい場合の関数はどうなりますでしょうか？

3）関数ではなくVBAマクロで記述した方が、1）や2）の内容を反映しやすいでしょうか？
　 "×"という一定の文字列で途切れることは変わりないので、VBAマクロの方がやりやすいでしょうか…
　もし可能であれば、どなたかマクロ版をお教え頂けないでしょうか？

初心者で本当に困っております･･･。何度も申し訳ありませんが、何卒よろしくお願い致します。。
（RYO） 2015/01/16(金) 01:21

 D2 =SUM((MMULT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!B$2:B$100=$B2)*(Sheet1!B$2:E$100<>"×")*(Sheet1!B$2:E$100<>""),{1000;100;10;1})=1111)*1)
 D3 =SUM((MMULT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!B$2:B$100=$B2)*(Sheet1!B$2:E$100<>"×")*(Sheet1!B$2:E$100<>""),{1000;100;10;1})=1110)*1)
 D4 =SUM((MMULT((Sheet1!$A$2:$A$100=$A4)*(Sheet1!B$2:B$100=$B4)*(Sheet1!B$2:D$100<>"×")*(Sheet1!B$2:D$100<>""),{100;10;1})=110)*1)
 D5 =SUM((MMULT((Sheet1!$A$2:$A$100=$A5)*(Sheet1!B$2:B$100=$B5)*(Sheet1!B$2:C$100<>"×")*(Sheet1!B$2:C$100<>""),{10;1})=10)*1)
（GobGob） 2015/01/16(金) 08:21

GobGobさん

ほぼ完ぺきに思った通りにできました！本当にありがとうございます！！！
最後の最後に1点だけ教えて下さい…。

D3,D4,D5は願った通りカウントできているのですが、

D2のところは、5日や6日、7日など"4日以上"連続して×がない人をすべてカウントしたく、
例えば通月○の人（×が一つもないような人）も含めてカウントするように
したいのです。。

この部分だけ今一度ご教示頂けないでしょうか？
どうかよろしくお願いいたします＞＜
（RYO） 2015/01/16(金) 12:16

 四列見て×が無いから4日『以上』になるんでないの？
 ×が5日目でも6日目でも25日目でも、はてまた×無しでも
 4日『以上』だよね？
（GobGob） 2015/01/18(日) 00:35

GobGobさん

お礼が遅くなり大変申し訳ございません！
投稿したつもりが更新ボタンを押していなかったようです。。

おっしゃるとおりでした！
私が数式を誤っていたようです。。
完璧にカウント出来ております！
本当に感謝しております！！最後までありがとうございました！
（COLOR） 2015/01/28(水) 13:07

コメント返信：

[ 一覧(最新更新順) ]

[[20150112102639]] 『3つの複数条件カウント』（RYO） ページの最後に飛ぶ

[[20150112102639]]　『3つの複数条件カウント』（RYO）　ページの最後に飛ぶ