こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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


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


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


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

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

こんな具合です。


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

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


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

投稿日時 - 2010-09-05 11:20:27

QNo.6159971

すぐに回答ほしいです

質問者が選んだベストアンサー

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

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

とりあえず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

投稿日時 - 2010-09-05 19:08:50

お礼

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

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

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

宜しくお願いします。

投稿日時 - 2010-09-05 21:54:35

ANo.5

このQ&Aは役に立ちましたか?

2人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

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

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

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

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

投稿日時 - 2010-09-05 23:00:25

ANo.7

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

投稿日時 - 2010-09-05 22:54:44

ANo.6

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

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


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


 「(1)質問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 & "・"))))

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


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

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

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

投稿日時 - 2010-09-05 20:03:48

お礼

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

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

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

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

投稿日時 - 2010-09-11 00:34:10

ANo.4

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))
> 上記式になにか勘違いありますでしょうか。

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

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

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

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

=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」で良いと思って訂正してあります。

これで試してください。

投稿日時 - 2010-09-05 19:01:24

お礼

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

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

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

投稿日時 - 2010-09-11 00:21:13

ANo.3

>○月○日~○月●日の間の新規客数をカウントする方法はありますでしょうか。
複数の条件でカウントする ということですね。
配列関数で説明してみます。
仮に シート名が データ
 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 で決定してください。
式が 拝謁関数として認識されたい意味で {}でくくられます。

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

投稿日時 - 2010-09-05 16:06:29

お礼

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

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

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

投稿日時 - 2010-09-06 18:46:16

ANo.2

以下の形式として回答します。
 
【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 計算1  計算2

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

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

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

投稿日時 - 2010-09-05 14:35:55

お礼

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

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


=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"というコードを入れても同じ結果です。

宜しくお願いします。

投稿日時 - 2010-09-05 17:11:46

ANo.1

データAがシート1に有るとして、A2セルから下方に来場日が、B2セルから下方に新規またはリピータが、C2セルから下方に氏名が、D2セルからH2セルまでの下方に購買行動であるA,B,C,D,Eなどが入力されているとします。
データBは別のシートに作るとしてA2セルから下方には集計する際のスタートの日付が、B2セルから下方には集計する際のエンドの日付を入力するとします。そこでお求めの新規の客数ですがC2セルに次の式を入力して下方にオートフィルドラッグします。

=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="新規")))

また、新規やリピータを含めた購買行動の客数ですがD1セルから横のセルにA,B,C,D,EとH1セルまで入力します。

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

=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)))

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

=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)))

投稿日時 - 2010-09-05 14:19:13

お礼

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

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

投稿日時 - 2010-09-05 17:17:49

あなたにオススメの質問