複数条件のカウントの仕方を教えてください。

質問

複数条件のカウントの仕方を教えてください。
C列・D列で特定の文字だけカウントするようにしましたが
１日1カウントでいいんです。
午前と午後の場合2カウントされて困っています。
午前と午後連続の時だけカウント1にする数式を教えてください。

【今の状態】
　　　A　　　B 　　　　C　　　　D　　　E
1　　１日　　午前 　東　　　北西
2　　　　　　午後 　　　　　
3　　２日　　午後　　　南　　　西
4　　３日　　午後　　　南西　　北
5　　４日　　午前　　　北　　　東
6　　　　　　午後　　　西　　　
7　　合計　　　6　　　　5　　　4
B7の数式＝SAM(COUNTIF（B1: B6,｛"東","北","南","西","北東","南西"｝))です。
よろしくお願いします。

回答No.12

suzusan7 · ベストアンサー

午前の代わりに時間が入っていて、
午前中を示す行数は１行ということであることが前提です。

セルにただ、9:00や10:30と入力しているのであれば、
普通に計算できると思いますので、条件判断の部分を
$C3:$C9<12
とすればいかがでしょうか？

お礼

suzusan7様
今度こそ出来ました！！！
何度も試行錯誤して午前（9：00・10：30・11：00など）の表示形式をh:mmで
午後（14：00・13：00など）の表示形式を文字列にすることでやっとできました。なぜなのか？はわからないのですが・・・
こんなに長い間見捨てずお答え下さってありがとうございました。
本当に助かりました。
心から本当にありがとうございました<m(__)m>

補足

suzusan7様
大変お世話になっております。
=SUM(IF($C3:$C9<12,IF(ISERROR(MATCH(D3:D9,文字列,0))*1=0,1,IF(ISERROR(MATCH(D4:D10,文字列,0))*1=0,1,0))))}の場合、表示形式は標準でいいのでしょうか？カウント数がなかなか合いません。
どうかよろしくお願いします。

回答No.11

suzusan7

そのままだと、カウントしてるだけになると思います。

カウントの式は、No.8の回答の
{=SUM(IF($B3:$B9<>"",IF(ISERROR(MATCH(D3:D9,文字列,0))*1=0,1,IF(ISERROR(MATCH(D4:D10,文字列,0))*1=0,1,0))))}

の式がベースになってると思います。
この式では、午前の認識は日付が入力されている行かどうかとなっています。

でも、紹介した三番目の方法だと、午後の行にも日付が入るため、
午前・午後の判断ができないようになっています。

ですから、午前の判断の仕方をC列の午前が入力されている行、すなわち上の式の
$B3:$B9<>""
の部分を
$C3:$C9="午前"
としておくと、日付の列は式に影響を与えないので、問題はないと思います。

お礼

何度もありがとうございました<m(__)m>
午前（9:00・10:30・11:00など）の表示形式をh:mmで午後（14:00・13:00など）の表示形式を文字列にして出来たのですが、ちゃんとカウント出来たり
同じ設定でも出来なかった箇所があったりでなぜか不安定です。
もう一度いろいろやってみます。

補足

suzusan7様　お世話になっております。
何度も何度も申し訳ありません。
仮の表は簡単に午前午後としていますが元々の表が午前のところに9：00や10：30や11：00などその日に出勤する時間がはいっています。
それでsuzusan7さんの教えて下さったことを元に
{=SUM(IF($B3:$B9＝"9：00",IF(ISERROR(MATCH(D3:D9,文字列,0))*1=0,1,IF(ISERROR(MATCH(D4:D10,文字列,0))*1=0,1,0))))}として設定しました。それで出来たのですが、たまにしかないのですが10：30や11：00の場合もあるので{=SUM(IF($B3:$B9＝"9：00""10：30""11：00",IF(ISERROR(MATCH(D3:D9,文字列,0))*1=0,1,IF(ISERROR(MATCH(D4:D10,文字列,0))*1=0,1,0))))}にしてみたのですがそれではできませんでした。
式のどこかがおかしいのでしょうか？
お手数をおかけしてばかりで申しわけありません。
どうかよろしくお願いします。

回答No.10

suzusan7

もう一息ですね。
タイトルと内容は全然違ってますけど（笑）

ぱっと思いつく方法は三つですかねぇ

一つは、No.8で回答してますが、
午前、すなわち日付が入力された行を必ず表示するようにすることです。
該当なしの場合でも表示されてしまいますが、これが最初の仕様かと
思っていました。一番手間がかからないかと・・・

次の方法はセルの結合を使う方法です。

No.8の回答に添付した図をベースに説明すると、
まず、セルB3とセルB4、つまり日付の部分ですが、両方を選択した状態で
右クリックして、セルの書式設定からセルを結合してしまいましょう。
文字位置が上がよいのか中央が良いのかは好みもありますので、
自由に設定してください。

次に結合したセルを下にフィルドラッグ、コピー貼り付けでもいいです。
結合した状態も合わせてコピーされるはずです。

コピーしたセルの数式を１日のセル＋１にしておきます。
セルの表示形式を
d"日（"aaa"）"
としておくと、西暦の年月日を入れると自動的に曜日まで表示できます。
日付を変えるときは、最初のセルの年月日を変えれば自動的に曜日まで
全部修正することができるようになります。

設定をしたら、コピーしたセルをずーっと下までフィルドラッグしていきます。
セル結合をして日付が入力されたセルがコピーされていくはずです。

これでオートフィルタをしてみましょう。
結合した日付を表示しているセルは、午前か午後のどちらかが表示されていなくても
１行分の高さがあれば、日付を表示していると思います。

この方法を使用する場合は、一つ目の方法の午前を強制的に表示するような設定は
取り消しておいてくださいね。

欠点は、例えば、３日の午後と４日の午前が非表示となった場合、
その間の罫線も消えてしまうことです。日にちは表示されるので、
わからなくなることはなく、見栄えの問題は残りますが、一応仕様通りかと。
こんな場合はないよということであれば、採用できると思います。

最後は、条件付書式を使用する方法です。
またまたNo.8の回答の図を使用しますが、
セルB4に数式で
＝B3
と入力します。それで、セルB4を選択した状態で、ツールバーの「書式」から
条件付書式を選びます。
条件に、数式が、=A3=0
と設定し、条件付書式の書式で文字の色を黒に設定します。
あと、罫線タブから上に線を表示するように設定しておきます。
このとき、A3に$マークがついて固定することのないようにしておいてください。
で、セルB4のセルの書式設定で、文字の色を白としておきます。
設定はここまでです。このセルをB6、B8とコピーしていってください。

そうすると、午前が消える行、すなわち、午前のフィルタの数字が0のときは、
午後の行の日付が黒色になり、表示され、合わせて午前と午後の間の罫線が表示されます。
午前が消えない場合は、書式は白色ですから見えないし、罫線も設定されていないので
表示されません。

この設定はちょっとややこしいかも。ただ、先に書いた日付の設定と併せて
設定してしまうと、使うのは楽になると思います。

お礼

suzusan7様　
また問題が発生しました。すみません。
出来たと思っていたのですがカウント数が合わないんです。
午後の表示（日付）をブランクにしていれば合っているのですが、午前のフィルタの数字が0のときは、午後の行の日付が黒色になり表示されて、
午前が消えない場合は白色で設定し、午後勤務だけ入った場合のカウントが1のはずが2になってしまいます。
何度も試行錯誤はしているのですがどうしてでしょうか？
何度をお手数をおかけし申し訳ありません。
どうかよろしくお願いします。

補足

suzusan7様　お世話になります。
出来ました！！長い間お付き合い下さり本当にありがとうございました。
suzusan7さんが書いて下さったことをもう一度読み直し
ちゃんと勉強したいと思います。
私の的外れな質問に丁寧に答えて下さって、本当に本当にありがとうございました。

回答No.9

suzusan7

残念ながら、この文章ではどこまでできて、
どこからができていないのかがわかりません。

名前定義でうまくいってるかどうかがわからないのであれば、
どこかの空白セルに
=index(文字列,1)
とでも入力してみましょうか？

名前定義がうまくいっていれば、リストの最初の文字が帰ってくるはず。
1の部分はリストの順番を差していますので、リストの数以下であれば
その文字列が返ってくるはずです。

次に、集計の部分で0になって返ってくるのであれば、数式バーで
MATCH(D3:D9,文字列,0)
の部分を選択して、F9キーを押してみましょう。
計算式の途中経過を見ることができます。
これをキャンセルするときはEscキーで。
そのままEnterすると値に置き換わってしまいますよ。

うまくいっていれば、配列で結果を見ることができて、
リストにある場所はその番号が、ない場所は#N/Aのエラーが出てくるはずです。

次は、少し範囲を広げて
ISERROR(MATCH(D3:D9,文字列,0))*1
の部分で同じ事をしましょう。

カウントすべき箇所に1が、カウントしない箇所に0が返ってくるはずです。
こんな感じで少しずつ範囲を広げて何がうまくいっていないか確認してみましょう

関数の働きがよくわかると思いますし、何が原因かわかりやすくなります。

たぶん、範囲の設定ミスか、配列にできていないか、
iserror関数の結果に1をかけて数字にしていないか
あたりが原因ではないかと推測しますが。

先の回答にも書いていますが、iserror関数はtrue,falseを返しますので、
そのままでは、if文でうまく動作しません。そのため、数字の1を掛けて
数字にしてあげる必要があります。

うまく動作していない箇所を探してみましょう。

補足

suzusan7様　お世話になります。
出来ました！！！本当にありがとうございます！！！
sususann7様の書いてあることにそってしてもう一度してみましたら名前定義がうまくいっていなかったようでした。
次にオートフィルタの設定をしました。
表示に反映したいところに1です。
反映した場合に午前の時は日付がはいっているからいいのですが、午後だけ勤務の場合を反映すると日付がはいっていない為何日なのかがわからなくなっていまいます。
それで反映してから日付を入れるとカウントしてしまいます。
度々すみません。
どうかよろしくおねがいします。

回答No.8

suzusan7

こんにちわぁ

例と実際に行いたいことが違うのはよくあることですが・・・

文字列がいくつあるのかわからないので、それも含めて式の訂正です。
ただし、Excel 2003ですので、2007等ではコマンドの位置が異なるかもしれません。

match関数、iserror関数、配列を使用します。

match関数は指定範囲と同じ文字があるかをチェックする関数です。
例では、セルI3～I10に文字を設定し、ツールバーの「挿入」－「名前」ー「定義」
から“文字列”と名前定義しています。そうすると、
=match(D3,文字列,0）
と式を入れると、セルD3の内容が文字列の中にあれば、リストの順番を、
なければエラーを返します。

エラーが返ってくると都合が悪いので、エラーかどうかを確認するのがiserror関数です。
=Iserror((match(D3,文字列,0))
とすると、エラーの場合はtrueをエラーでなければFalseを返します。
true,falseに数字の1を乗じると、それぞれ1と0になります。

これを１つのセルごとに判定なんてしてられないので、配列関数を使います。
=Iserror((match(D3:D10,文字列,0))
として、セルの数式を確定する際に、shiftキーとctrlキーを押しながらEnterキーを押します。
セルの数式が{}で括られていたら成功です。

以上の式を利用して、D11のセルに次式を入力してます。
{=SUM(IF($B3:$B9<>"",IF(ISERROR(MATCH(D3:D9,文字列,0))*1=0,1,IF(ISERROR(MATCH(D4:D10,文字列,0))*1=0,1,0))))}
式の意味は、午前の行にリストと一致する文字列があればカウント、
午前の行にリストと一致する文字がなくて午後にリストと一致する文字列があればカウントし、
カウントしたものをsumで合計しています。

続いてオートフィルタです。準備として、D1～G1に表示に反映したい列は1を入力しています。
作業列としてK、L、M列を使用し、セルL3には
=IF(B3<>"",1,0)
を入力して下にコピーしています。つまり日にちが入力されていれば1を
なければ0を返します。別に午前と午後に分けても構いません。
次にセルM3は
{=SUMPRODUCT(IF(ISERROR(MATCH(D3:G3,文字列,0))*1=0,1,0)*D$1:G$1)}
と入力し、下へコピーです。配列関数ですのでshift＋ctrl＋Enterで確定してください。
式の意味は文字列と同じ文字があり、かつ1列目に1が入力されている、
すなわち、表示設定になっていればその個数を返します。
そして、セルk3は
=IF(L3+M3>0,1,0)
です。L列とM列の少なくともどちらかを満足していれば1としています。

A列は作業列を使わずにKLM列を1つの式にしているだけでL列と同じです。
ただ、最初に作業列を使わずに１つのセルに式をまとめただけです。
ですが、土、日の扱い等もあるので、作業列を使うほうが考えやすいと思います。

セルA1にカーソルを置いて、ツールバーから「データ」－「フィルタ」－「オートフィルタ」を
選ぶと、図の1行目のような三角が出てきます。

あとはA列のオートフィルタで1のものを表示するだけです。
なお、合計の行や氏名の行などは強制的にA列に1を入力しておく必要があります。
また、作業列は印刷範囲外にしておくと印刷時の見栄えもよくなると思います。

入力時はオートフィルタをはずしておいて、必要なときに印刷向けに設定すると良いかと思います。

補足

suzusan7様　お世話になります。
すごいです。すごいすぎます。感動しました！！
初心者の私にもとても詳しく分かりやすく説明してくださってありがとうございます。
書いてあることにそって
文字列いれて名前定義そして数式をいれるまでできました。
ところが値が0になります。
どこか私のやり方が間違っていると思うのですが・・・
名前定義がまちがっているのでしょうか？
何度も何度も申し訳ありません。
どうぞよろしくお願いします。

回答No.7

suzusan7

こんにちわぁ。

次の式でいかがでしょうか？
=SUMPRODUCT(($A1:$A6<>"")*(C1:C6<>""))+SUMPRODUCT(($A1:$A6<>"")*(C1:C6="")*($A2:$A7="")*(C2:C7<>""))

一つ目のsumproductは日付が入ってて、かつC列に文字が入っているものを
二つ目のsumproductは日付が入っててC列に文字が入っていなくてかつ、
日付が入っていなくてC列に文字が入っているものをカウントしています。

つまり、前半は午前に文字が入力されているものを、
後半は午前が空白で午後に入力されているものをカウントして足しています。

使用上の注意ですが、
午後の行をカウントするため、後半では範囲をずらした式を使用しています。
そのため、最終行（例でいう６行目）は空白行か午後の行とする方が確実だと思います。
日付を入れた行のすぐ下を合計行とすると場合によっては
何か違うものをカウントしてしまうことがあるかもしれません。

また、C列以降は文字が入っている行として式を組んでいます。
方向以外の文字も入力するという場合は、その辺を考慮して変形してください。

あとは余談なのですが、
午前と午後を必ず入れた書式にした方が便利ではないでしょうか？
もちろん、そうできない理由があることもありますけど。
午後の行を必ず入れて書式を決めてしまうと、午前の下は必ず午後となりますので
式が組みやすくなると思います。また、午後の入力がある度に行の挿入が不要になりますし
表示で邪魔になるのであれば、オートフィルターで非表示にすることもできると思いますよ。

補足

suzusan様　回答頂いてありがとうございます。
よろしくお願いします。
日曜日のC列・D列・E列に別の文字が入るためそれもカウントしてしまうんです。
その為、特定の文字だけカウントするようにして使っていたのですが午前勤務だけまたは午後勤務の時だけはいいのですが、月によっては午前・午後連続勤務のときがあって、その時カウントが合わなくて困っています。
例に出した表もわかりにくくすみません。

あとアドバイスして頂いた午前と午後必ずいれた表にした場合、オートフィルターで個別に非表示・表示ってできるのでしょうか？

回答No.6

KURUMITO

回答Ｎｏ３，４，５です。
Ｃ７のデータだけが合わなくて他のデータは正しい値が得られているのですね。その原因としてはＣ１，Ｃ３，Ｃ４，Ｃ４のセルに入力された文字の中で文字の後にスペースなどが入っているものがあると思われます。もう一度正しく文字を入力すれば問題はないでしょう。あるいは次の式をＣ７セルに入力してからＦ６セルまでオートフィルドラッグすればよいでしょう。
=SUMPRODUCT((TRIM($A1:$A6)<>"")*(TRIM(C1:C6)={"東","北","南","西","北東","南西","北西"}))
ところで、式の中で東とかの文字を入れて式を作っていますが、すべての方角を数えるだけでしたら次のような式でも十分ですね。
=SUMPRODUCT(($A1:$A6<>"")*(C1:C6<>""))
上の式でも空白のセルにスペースなどが入っていれば誤ったデータとなりますので次のような式にしてもよいでしょう。
=SUMPRODUCT((TRIM($A1:$A6)<>"")*(TRIM(C1:C6)<>""))

補足

KURUMITO様　何度もありがとうございます。
No5ではD7だけが正しい値でした。
=SUMPRODUCT((TRIM($A1:$A6)<>"")*(TRIM(C1:C6)= {"東","北","南","西","北東","南西","北西"}))ですとC7=4・D7=4で合っているのですが、E7・F7が値が2となってしまいます。
C列の場合文字は5入っていますが実質4日間なので求めたい値は4です。
D7＝4・E7＝4　F7＝3となってほしいのですが・・・
式の中で東など文字を入れているのは、表の途中に別の文字が入るため指定した文字だけカウントするためです。

回答No.5

KURUMITO

回答Ｎｏ３，Ｎｏ４です。
Ｃ７が４になってほしい場合にはＮｏ３の回答での式を入力する場合ですね。
=SUMPRODUCT(($A1:$A6<>"")*(C1:C6={"東","北","南","西","北東","南西"}))
この式の意味はＡ列では空白の行でなく、日付があり、Ｃ１セルからＣ６セルの範囲で、東、北、・・・が入力されているセルの数を数えるものです。
これでＣ７のデータは４になります。なお、式に"北西"が含まれていない式になっていますので注意が必要ですね。お示しの式では北西が含まれていませんでしたので。

補足

何度も申し訳ありません。
C7にいれてみたのですが値が3になってしまいます。
D7は4　E7は2　F7は2になりました。
式には北西もいれました。

回答No.4

KURUMITO

回答Ｎｏ３です。
日付が入っていない行でのデータの出し方をＮｏ３で示しましたが、午後で該当のデータがある場合にカウントされないとのことですが意味がよく理解できません。午前午後に関係なく日付が空の行についてのカウントの式を示したのです。もしも、反対に日付の空になっている行でのカウントをする場合でしたら次の式になりますね。
=SUMPRODUCT(($A1:$A6="")*(C1:C6={"東","北","南","西","北東","南西"}))
日付に関係なく午後のデータについてカウントされるのでしたら次の式になりますね。
=SUMPRODUCT(($B1:$B6="午後")*(C1:C6={"東","北","南","西","北東","南西"}))

補足

うまく説明できなくてすみません。
その上初心者の為、数式があまりよくわかりません。
いろいろやってみたのですが、C7が4なってほしいのですがなりません。
どこが違うのでしょうか？
お手数をおかけし申し訳ありません。
よろしくお願いします。

回答No.3

KURUMITO

午前と午後が連続している場合は日付の行が空白になっていることを利用して次のようにしてはどうでしょう。日付の入った行のみを数えるのでしたら次のようになります。
Ｃ７セルには次の式を入力して右横方向にオートフィルドラッグします。
=SUMPRODUCT(($A1:$A6<>"")*(C1:C6={"東","北","南","西","北東","南西"}))

補足

ありがとうございます。
私も日付の行が空白になっていることを利用したらと考えたのですが
その場合午後のみに指定の文字が入っている時がカウントされないんです。
いい方法はないでしょうか？
よろしくお願いします。

回答No.2

popuplt

>わかりにくくてすみません。書き間違いです。
本当に判りにくい。
SAM　って、どんなユーザー関数？

条件が明確でないので
=SUMPRODUCT((A1:A6<>"")*(C1:C6={"東","北","南","西","北東","南西"}))
とか

補足

ありがとうございます。
条件としては
・C列・D列は特定の文字だけカウントしたい。
・A列で日付が同じ場合はカウントが1になるようにです。
例えば今の状況から
A列が4日の場合→午前にも午後にも特定文字あり→カウントは設定のままだと2ですが同じ日なのでカウントは1になるようにです。
よろしくお願いします。