EXCELで○日以降のデータをカウントする方法。

質問

EXCELで○日以降のデータをカウントする方法。

EXCELで週報を作るとします。

累積のデータ（A)があり、別シート（B)で○月○日～○月●日の内容としてレポートを作成しようとしています。
それで、（B)の日付のセル２か所を変えることで、その日付の間のデータを各項目別に集計する数式を組もうとしています。
乏しい知識でIF関数をネストしようとしたのですが、エラーが出て進みません。
２つ教えてください。
(1)質問１
データ（A)には来場されたお客様単位で来場日・属性・購買行動等を一つの行に入力してあります。
また、来場日の時系列順に入力してあり、顧客の属性（新規,リピータ）があります。

○月○日～○月●日の間の新規客数をカウントする方法はありますでしょうか。

(2)質問2
購買行動をコードで入力する欄があります。
複数のサービスを選ばれる場合があるので、５つの欄が用意されています。

8/1 新規　○○様　A・B・D・E
8/2 新規　●●様　A・D
8/3 リピータ ●●様　A・C・D

こんな具合です。

○月○日～○月●日の間のAのサービスが提供された回数を数えたいのですが、
どのようにしたらよいでしょうか。

参照する範囲の式を変更していままで対応していたのですが、
職務の引き継ぎがあり、その人はExcelチンプンカンプンなので
できれば週報のシートの日付を変えるだけで完成するように作り変えようとしています。

皆さまのお知恵を貸してください。宜しくお願いします。

回答No.5

tom04 · ベストアンサー

こんばんは！
横からお邪魔します。

↓の画像のような感じで良いのですかね？

とりあえずSheet1の1000行目まで対応できる数式にしてみました。

Sheet2のB2セルに
=IF(OR(A2="",COUNTBLANK(B3:B4)),"",SUMPRODUCT((Sheet1!A2:A1000>=B3)*(Sheet1!A2:A1000<=B4)*(Sheet1!B2:B1000=A2)))

E2セルに
=IF(OR(D2="",COUNTBLANK(E3:E4)),"",SUMPRODUCT((Sheet1!A2:A1000>=E3)*(Sheet1!A2:A1000<=E4)*(Sheet1!D2:H1000=D2)))

という数式を入れています。
これで質問1に関しては、A2・B3・B4　セルに
質問2に関しては、D2・E3・E4　セルに希望の条件を入力すれば
それぞれに結果が表示されると思います。

以上、参考になればよいのですが
外していたらごめんなさいね。m(__)m

お礼

ありがとうございました！

求めたかった結果がでました！

あの、もうひとつ・・・お客さんの購入金額の欄もあるのですが、
その週の最高値と平均って出せます・・・？

宜しくお願いします。

回答No.8

tom04

No.7です！
何度もごめんなさい。
投稿した後で気づきました。

本文の数式は問題ないと思いますが、画像の中の文章に間違いがあります。

画像の「H2」の部分はすべて「H1」が本当です。

何度も失礼しました。m(__)m

回答No.7

tom04

No.5です！

＞あの、もうひとつ・・・お客さんの購入金額の欄もあるのですが、
その週の最高値と平均って出せます・・・？

とありましたので再び顔を出しました。

↓の画像のように勝手に表を作って見ました。今回も下側がSheet2になります。

ここで一つ問題になるのが、「その週」という言葉です。
前回の方法の場合は　始まり～終わり　の日付を入力してその結果を表示するようにしていましたので、
二通り考えてみました。
Sheet2の　H1・H2　に期間を入力し、その期間の最大値と平均を出す方法です。
とりあえずその方法の数式になります。
尚、余計なお世話かも知れませんが、J1・J2セルはH1セルにある日付を入力すると
J1セルにH1セルの直前の日曜日・J2セルのH1セルの直後の土曜日を表示するようにしています。

J1セルの数式は
=IF(H1="","",H1-WEEKDAY(H1)+1)
J2セルの数式は
=IF(J1="","",J1+6)
としています。
もし、こちらのデータが必要でしたら、↓の数式のH1・H2部分をJ1・J2に変更すればOKだと思います。

今回はSheet1に作業用の列を設けています。
作業列J2セルに
=IF(AND(A2>=Sheet2!$H$1,A2<=Sheet2!$H$2),RANK(D2,$D$2:$D$1000),"")
という数式を入れ、オートフィルで下へずぃ～～～！っとコピーします。
これでSheet1の全体に対するSheet2に入力したH1～H2セルの期間内だけのものの順位（大きい順）が表示されます。

そして、Sheet2のH3セルに
=IF(COUNTBLANK(H1:H2),"",INDEX(Sheet1!D2:D1000,MATCH(MIN(Sheet1!J2:J1000),Sheet1!J2:J1000,0)))

H4セルに
=IF(COUNTBLANK(H1:H2),"",SUMPRODUCT((Sheet1!A2:A1000>=H1)*(Sheet1!A2:A1000<=H2)*(Sheet1!D2:D1000))/COUNT(Sheet1!J2:J1000))

という数式を入れています。
これで何とか希望に近い形にならないでしょうか？

以上、長々と失礼しました。
参考になればよいのですが・・・m(__)m

回答No.6

DOUGLAS_

＞職務の引き継ぎがあり、
＞その人はExcelチンプンカンプンなので
＞できれば週報のシートの日付を変えるだけで
＞完成するように作り変えようとしています。

　ん～。。。
　torepandeka さんご自身がお分かりにならないような計算式を立てて、ワークブック をお渡ししても、「その人」が使いこなせるかどうか、ちょっと心配ですが。。。

　Sheet1 の Ａ列に「日付」、Ｂ列 に「新規・リピータ」、Ｄ列に「コード」が入力され、これが、100行目 まで入っていて、Sheet2 の Ａ列に「集計開始日付」、Ｂ列に「集計終了日付」が入力されているものとします。

　「(1)質問１」の計算式は、

=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=B1)*(Sheet1!B1:B100="新規"))

でいけるかと存じますが、「(2)質問2」の計算式は、「A・B・D・E」という データ が入力されているのでしたら、

=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=B1)*NOT(ISERR(FIND("A",Sheet1!D1:D100))))

でいけるのですが、

＞"101"というコードを入れて

というようなことになりますと、「コード」の区切り文字が半角の「・」として、

=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=B1)*NOT(ISERR(FIND("・" & 101 & "・","・" & Sheet1!D1:D100 & "・"))))

というような計算式になろうかと存じます。

　ただし、
＞複数のサービスを選ばれる場合があるので、５つの欄が用意されています。
の意味が、No5 tom04 さんがお書きのように、５つ の列に分かれて コード が記入されているという意味でしたら、例えば「Ａ」または「101」を記入する欄が Sheet1 の Ｄ列とすると、

=SUMPRODUCT((Sheet1!A1:A100>=A1)*(Sheet1!A1:A100<=B1)*(Sheet1!D1:D100 <> ""))

というような式になります。

お礼

>　torepandeka さんご自身がお分かりにならないような計算式を立てて、ワークブック をお渡ししても、「その人」が使いこなせるかどうか、ちょっと心配ですが。。。

おっしゃる通りなので、誰にでも使いこなせるものを設計する必要があると思ってます。
そこで、元となるデータは入力規則等を設定して、エラーを防止するようにしたりして、
週報のページは日付を変えるだけにする必要があると思い、ここで質問させていただきました。

今のところうまく回っているようです。

sumproductが式がすっきりしていいようですね。
ありがとうございました。

回答No.4

jcctaira

torepandekaさん

＞　=SUMPRODUCT(($G$2<='Master Sheet'!$E$4:$E999)*('Master Sheet'!$E$4:$E999<=Sheet1!$I$2)*(SUBSTITUTE('Master Sheet'!$J$4:$O999,D13,"")<>'Master Sheet'!$J$4:$O$999))
＞　上記式になにか勘違いありますでしょうか。

私の回答は以下の様式だと思っておりました。
Ｄ列の中にサービスがすべて入っていると…

【Sheet1】データ
　A　　B　　　　　C　　　　D
8/1　新規　　　○○様　A・B・D・E
8/2　新規　　　●●様　A・D
8/3　リピータ　●●様　A・C・D

torepandekaさんはJ～O列の６列にサービスコードが入っているとのようなので、上記の式ではできないです。

式が長くなりますが、以下のようになります。

=SUMPRODUCT(($G$2<='Master Sheet'!$E$4:$E999)*('Master Sheet'!$E$4:$E999<=$I$2)*(SUBSTITUTE(CONCATENATE('Master Sheet'!$J$4:$J$999,'Master Sheet'!$K$4:$K$999,'Master Sheet'!$L$4:$L$999,'Master Sheet'!$M$4:$M$999,'Master Sheet'!$N$4:$N$999,'Master Sheet'!$O$4:$O$999),D13,"")<>CONCATENATE('Master Sheet'!$J$4:$J$999,'Master Sheet'!$K$4:$K$999,'Master Sheet'!$L$4:$L$999,'Master Sheet'!$M$4:$M$999,'Master Sheet'!$N$4:$N$999,'Master Sheet'!$O$4:$O$999)))

注）torepandekaさんの式に「Sheet1!$I$2」とSheet1が使われていますが、多分「$I$2」で良いと思って訂正してあります。

これで試してください。

お礼

なるほど、前提が違ったのですね。

ちなみに元の式はどういう発想の式なのでしょうか。
無知なので、電車の中でSubstituteの事を調べたのですが、
置換の数式のようですね。

空白と置換して、数を数えることで
割り出すという考え方でしたか？

回答No.3

hallo-2007

>○月○日～○月●日の間の新規客数をカウントする方法はありますでしょうか。
複数の条件でカウントする　ということですね。
配列関数で説明してみます。
仮に　シート名が　データ
　A　　B　　C　・・・・　　　　空いている列に
8/1 新規　○○様　A・B・D・E
8/2 新規　●●様　A・D
8/3 リピータ ●●様　A・C・D
と入っているとして
空いている列を作業列として考えます。
=IF(AND(A2>=DATE(2010,8,1),A2<=DATE(2010,8,31),B2="新規"),1,0)
と入れて下までコピィしたら、カウントしたい行が　1　それ以外が 0　になりますよね。
この式をもう少し短くします。
=AND(A2>=DATE(2010,8,1),A2<=DATE(2010,8,31),B2="新規")*1
とか
＝(A2>=DATE(2010,8,1))*(A2<=DATE(2010,8,31))*(B2="新規")
でも同じ結果になるはずです。

この列を合計（SUM)すれば　希望の値になると思います。
そこで、作業列を使わずにこの計算を行ってみます。
別シートの
　B1セルに　2010/8/1
　B2セルに　2010/8/31
　B3セルに　新規　　　　と集計したい日付と項目が入っているとして
　=SUM((データ!A2:A1000<=B2)*(データ!A2:A1000>=B1)*(データ!B2:B1000=B3))
とデータのシートの範囲を指定して式を入れます。
その式を確定するときに
Ctrl+Shift+Enter　で決定してください。
式が　拝謁関数として認識されたい意味で　｛｝でくくられます。

同様にして（２）の質問も出来るはずです。
詳しくは　エクセル　配列関数　などで検索してみるとか　Helpで探してみてください。

お礼

丁寧な回答ありがとうございます。
Excelは今年の初めに自力で少しずつ覚えてきたので
最初書き込みをされたときは、咀嚼しきれなかったのですが、
他の方が「作業列」を実例付きで見せていただいたことで
halloさんの書き込みの意味がわかりました。

Excelって関数で一発で答えを出せるものだと思っていたのですが、
(CountIFとANDを組み合わせた式で一発で出るものと思ってました＾＾；)
そういうわけではないんですね。

そして「作業列」等を活用することで、式の中身が視覚的になったり
全体の式の量を減らしたりできるのですね。
大変勉強になりました。

回答No.2

jcctaira

以下の形式として回答します。

【Sheet1】データ
　A　　B　　　　　C　　　　D
8/1　新規　　　○○様　A・B・D・E
8/2　新規　　　●●様　A・D
8/3　リピータ　●●様　A・C・D

【Sheet2】データ
　A　　B　　　C　　　　　D
8/1　8/4 計算１　　計算２

【計算１】・・・　質問１：新規の人数を求める式
　=SUMPRODUCT((A1<=Sheet1!A1:A999)*(Sheet1!A1:A999<=B1)*(Sheet1!B1:B999="新規"))

【計算２】・・・　質問２：Aのサービス人数を求める式
=SUMPRODUCT((A1<=Sheet1!A1:A999)*(Sheet1!A1:A999<=B1)*(SUBSTITUTE(Sheet1!D1:D999,"A","")<>Sheet1!D1:D999))

※データの行は1～999で計算していますので、行が増える場合は訂正してください。

お礼

簡明な表現での回答ありがとうございます。

計算１は、求める結果が得られましたが、
計算２では、「総来場者数」と思われる結果が返ってきます。
試しに１つも購買されていないサービスコードを検索させてみたのですが、
総来場者数と同じ数になります。
列を増やすとその数だけの倍数になりましたので総来場者数のカウントになっているようです。

=SUMPRODUCT(($G$2<='Master Sheet'!$E$4:$E999)*('Master Sheet'!$E$4:$E999<=Sheet1!$I$2)*(SUBSTITUTE('Master Sheet'!$J$4:$O999,D13,"")<>'Master Sheet'!$J$4:$O$999))

上記式になにか勘違いありますでしょうか。

*D13のセルに商品コードを入れております。"101"というコードを入れても同じ結果です。

宜しくお願いします。

回答No.1

KURUMITO

データＡがシート1に有るとして、Ａ2セルから下方に来場日が、Ｂ2セルから下方に新規またはリピータが、Ｃ2セルから下方に氏名が、Ｄ2セルからＨ2セルまでの下方に購買行動であるＡ，Ｂ，Ｃ，Ｄ，Ｅなどが入力されているとします。
データＢは別のシートに作るとしてＡ2セルから下方には集計する際のスタートの日付が、Ｂ2セルから下方には集計する際のエンドの日付を入力するとします。そこでお求めの新規の客数ですがＣ2セルに次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT($A2:$B2)<>2,"",SUMPRODUCT((Sheet1!$A$2:$A$1000>=$A2)*(Sheet1!$B$2:$B$1000="新規"))-SUMPRODUCT((Sheet1!$A$2:$A$1000>$B2)*(Sheet1!$B$2:$B$1000="新規")))

また、新規やリピータを含めた購買行動の客数ですがＤ1セルから横のセルにＡ，Ｂ,Ｃ，Ｄ，ＥとＨ1セルまで入力します。

その後にＤ2セルには次の式を入力してＨ2セルまでオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(COUNT($A2:$B2)<>2,"",SUMPRODUCT((Sheet1!$A$2:$A$1000>=$A2)*(Sheet1!$D$2:$H$1000=D$1))-SUMPRODUCT((Sheet1!$A$2:$A$1000>$B2)*(Sheet1!$D$2:$H$1000=D$1)))

なお、新規だけの購買行動を表示させるのでしたらＤ2セルには次の式を入力します。

=IF(COUNT($A2:$B2)<>2,"",SUMPRODUCT((Sheet1!$A$2:$A$1000>=$A2)*(Sheet1!$B$2:$B$1000="新規")*(Sheet1!$D$2:$H$1000=D$1))-SUMPRODUCT((Sheet1!$A$2:$A$1000>$B2)*(Sheet1!$B$2:$B$1000="新規")*(Sheet1!$D$2:$H$1000=D$1)))

お礼

早速の回答ありがとうございます。

データAの構造がKURUMITOさんの前提とされているものと違いましたので、
本データでなく、仮のデータを使ってやってました。
こういうやり方もあるのかと大変勉強になりました。
購買行動の記入欄が６つに対し、商品が１５ありますので、
商品ごとに欄を作る構造に変えるか悩みどころです。。。