EXCEL関数 週計テクニック

質問

sheet1に売上日計表があります。
A列＝日付（yyyy/mm/dd)
B列＝商品名
C列＝売上金額
D列＝顧客名

となっていて、縦に１年間の売上データが並んでいます。

sheet2に下記の週計表を作りたいのですが。各行に適した関数を教えてください。

A列＝１回以上購入した顧客名の一覧（１顧客１行）
B列＝１月にA列の顧客が来店した日数
C列＝１月のA列の顧客の売上合計

以降右に、D、E列＝２月　F、G列＝３月、という具合に月別週計をしたいです。

宜しくお願い致します。

回答No.6

tom04 · ベストアンサー

No.3です。

＞顧客が1日の中で2回以上来店することはありませんが、
＞１日の内に複数品目商品購入をした場合はカウント「１」と数えるようにしたいのですが

とありましたので・・・
前回同様作業用の列を2列設けます。
↓の画像で上側がSheet1とします。
1行目が項目行でデータは2行目以降にあるという前提です。

Sheet1の作業列1は前回同様の数式そのままにします。

作業列2は
=IF(OR(COUNTBLANK(A2:D2),COUNTIF(F$1:F1,MONTH(A2)&"_"&DAY(A2)&D2)),"",MONTH(A2)&"_"&DAY(A2)&D2)
という数式にしてオートフィルでしっかり下へコピーしておいてください。

そしてSheet2のB3セルに
=IF($A3="","",COUNTIF(Sheet1!$F:$F,COLUMN(B1)/2&"*"&$A3))
D3セルに
=IF($A3="","",SUMPRODUCT((MONTH(Sheet1!$A$2:$A$2000)=COLUMN(B1)/2)*(Sheet1!$D$2:$D$2000=$A3),Sheet1!$C$2:$C$2000))
という数式を入れ、前回同様C3・D3を範囲指定　→　そのまま2列ずつ右へオートフィルでコピー！
そのまま下にオートフィルでコピー！

※　今回はSUMPRODUCT関数を使用していますので、極端にデータ量が多い場合はおススメできません。
とりあえず2000行目まで対応できる数式としています。

※　作業用の列をもう1列設ければ行すべてを範囲指定し、COUNTIF関数とSUMIF関数だけでできますが
敢えて作業列は増やさずにやってみました。

こんなんではどうでしょうか？m(_ _)m

お礼

完璧にイメージ通りのものが出来ました。

たいへんありがとうございました。

回答No.5

tsubuyuki

> VLOOKUPなどの複数の関数を使っていて、行数も500行ぐらいはありますが、これが原因でしょうか？

ほぼ関係ないと言えます。
式を使っていようが、ピボットテーブルは作成できますし、
行数に制限があるならプログラムとして成り立ちませんから。

さて、
> そのピッポットテーブルのフィールド名は正しくありません。

から（私が）連想できる原因は二つです。
一つ目は例えば１行目に項目名があるとして、
「１行目に空白セルがある場合」です。
空白セルに「適当な名前（Dummy1など）」をつけてやれば解消されます。

もうひとつは＃４さんのご指摘どおり、
「項目名に結合セルが含まれる場合」です。
不要な結合は使わないのが鉄則です。

番外として「項目行が複数に渡る」場合もありえます。
これはこれ、項目行が１行に収まるようにレイアウト変更が必要です。

ちなみに、表の途中に結合されたセルがあってもピボットテーブル作成までは出来ます。
ただし、集計結果が異なる場合がありますので注意が必要です。

お礼

同じくその通りです。

結合セルと、タイトルでブランクのセルがありました。
解消して、ウイザードは完了しました。

あと、項目の設定が実際の表だとすこし複雑になっていますので。
このあたりもう少し頑張ってみます。

ありがとうございます！

回答No.4

chonami

私もピボットお勧めですね。是非活用していただきたいと思います。

>VLOOKUPなどの複数の関数を使っていて、行数も500行ぐらいはありますが、これが原因でしょうか？

↑は関係ないと思います。
表のタイトルは全列入ってますか？
結合セルなどはないでしょうか？

お礼

その通り！
結合セルと、タイトルでブランクのセルがありました。

解消して、ウイザードは完了しました。

あと、項目の設定が実際の表だとすこし複雑になっていますので。
このあたりもう少し頑張ってみます。

ありがとうございます！

回答No.3

tom04

こんにちは！
一例です。

↓の画像で上側がSheet1・下側がSheet2とします。
Sheet1に作業用の列を2列設けます。

作業列1のE2セルに
=IF(COUNTIF(D$2:D2,D2)=1,ROW(),"")
作業列2のF2セルに
=IF(COUNTBLANK(A2:D2),"",MONTH(A2)&D2)
という数式を入れ、E2・F2を範囲指定　→　F2のフィルハンドルでずぃ～～～！っと下へコピーしておきます。

そしてSheet2のA3セルに
=IF(COUNT(Sheet1!E:E)<ROW(A1),"",INDEX(Sheet1!D:D,SMALL(Sheet1!E:E,ROW(A1))))
という数式を入れ下へしっかりコピー！
これでSheet1の顧客名が重複なしで表示されます。

次にSheet2のB3セルに
=IF($A3="","",COUNTIF(Sheet1!$F:$F,COLUMN(B1)/2&$A3))
C3セルに
=IF($A3="","",SUMIF(Sheet1!$F:$F,COLUMN(B1)/2&$A3,Sheet1!$C:$C))
という数式を入れ、C3・D3セルを範囲指定　→　D3セルのフィルハンドルで2列ずつ右側に12月までオートフィルでコピー！
最後にそのまま（C3セルから3行目が範囲指定されたまま）下へコピー！
これで画像のような感じになります。

※　セルの左上隅の三角が目障りであれば↓のURLを参考にしてみてください。

http://www.excel.studio-kazu.jp/tips/0045/

こんな感じではどうでしょうか？m（_ _）m

補足

私の説明不足で申し訳ありません。。

実際の売上データでは、1人の顧客が1日にいくつかの商品を購入します。
１商品1行になっていますので、顧客名の作業列のところも、複数商品購入した場合、顧客名が複数回出現します。このためご回答いただいた方法では、Sheet2のA行の重複のない顧客リストはうまく行ったのですが、月別の来店回数のところは結果的に購入商品品目数のような数字で出てきてしまいます。

顧客が1日の中で2回以上来店することはありませんが、１日の内に複数品目商品購入をした場合はカウント「１」と数えるようにしたいのですが、さらにご教授をお願い致します。

回答No.2

KURUMITO

シート1のA1セルからD1セルに項目名が有って、下方にそれぞれのデータが入力されているとします。
作業列を作って対応するのがデータ数が多い場合には特に有用です。
E2セルには次の式を入力して下方jにドラッグコピーします。

=IF(D2="","",IF(COUNTIF(D$2:D2,D2)=1,MAX(E$1:E1)+1,INDEX(E$1:E1,MATCH(D2,D$1:D1,0))))

F2セルには次の式を入力して下方にドラッグコピーします。

=IF(E2="","",E2*100000+DATE(YEAR(A2),MONTH(A2),1))

そこでお求めの表をシート2に作るとしてA1セルには顧客名の文字を入力し、A2セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$E:$E),"",INDEX(Sheet1!$D:$D,MATCH(ROW(A1),Sheet1!$E:$E,0)))

B1セルから横の列には1月からの月を表示させるのですが次のようにします。
例えば2013年1月からの月を並べるのでしたらB1セルには2013/1/1と入力します。C1セルは空のセルとしてD1セルには2013/2/1と入力します。以下左側の列は同様にします。
B1セルなどについてはセルの表示形式で「ユーザー定義」でm"月"のようにすることで1月と表示させます。また、B1セルとC1セルは結合セルとすればよいでしょう。
その後にB2セルには次の式を入力して横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A2="","",IF(MOD(COLUMN(A1),2)=1,COUNTIF(Sheet1!$F:$F,ROW(A1)*100000+B$1),SUMIF(Sheet1!$F:$F,ROW(A1)*100000+A$1,Sheet1!$C:$C)))

補足

同じく

実際の売上データでは、1人の顧客が1日にいくつかの商品を購入します。
１商品1行になっていますので、顧客名の作業列のところも、複数商品購入した場合、顧客名が複数回出現します。このためご回答いただいた方法では、Sheet2のA行の重複のない顧客リストはうまく行ったのですが、月別の来店回数のところは結果的に購入商品品目数のような数字で出てきてしまいます。

顧客が1日の中で2回以上来店することはありませんが、１日の内に複数品目商品購入をした場合はカウント「１」と数えるようにしたいのですが、さらにご教授をお願い致します。

回答No.1

tsubuyuki

関数を使わなくても出来ますよ。

ピボットテーブルについて調べてみましょう。
https://www.google.co.jp/#safe=off&output=search&sclient=psy-ab&q=%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB+%E3%83%94%E3%83%9C%E3%83%83%E3%83%88%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB&oq=%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB+%E3%83%94%E3%83%9C%E3%83%83%E3%83%88%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB&gs_l=hp.3..0i4l8.2691.8526.0.8964.24.19.0.0.0.3.777.5274.2-1j2j0j5j2.10.0...0.0...1c.1j4.17.psy-ab.50qnMbr_2rg&pbx=1&bav=on.2,or.r_qf.&bvm=bv.47883778,d.dGI&fp=70989c7ad1b914a&biw=1920&bih=962

ところで、週計⇒集計の間違いと思って良いですよね。
まぁ、週計もピボットテーブルで出せますけどね。

補足

ありがとうございます。

週計⇒集計の間違いです。失礼いたしました。

確かにピポットテーブルは使ったことなかったです。

早速やってみたのですが。
データのシートからウイザードを立ち上げて進めて行くと、
「そのピッポットテーブルのフィールド名は正しくありません。ピッポットテーブル　レポートを作成するには、ラベルの付いた列でリストとして編成されたデータを使用する必要があります。ピッポットテーブルのフィールド名を変更する場合は、フィールドの新しい名前を入力する必要があります。」

というエラーメッセージが出てきます。
因みに別のシンプルな価格表などのsheetで同じことをするとウイザードは完了したのですが、sheet1との違いといえば、実際のsheet1はVLOOKUPなどの複数の関数を使っていて、行数も500行ぐらいはありますが、これが原因でしょうか？