1321352424 エクセルで来客数をスタッフ別、曜日別、時間別に集計できる表を

作成したいと思っております。
以前、お力添えいただいた方、ご協力をお願いできれば幸いです。

<やりたいこと>
・来客数をスタッフ別、曜日別、時間別に集計できる表を作成し
 1週間毎(月~土)にスタッフ別に来客数合計を集計、
 2週間毎に(月~土×2)スタッフ別、曜日別、時間別に集計
 1カ月単位でスタッフ別に来客数の総合計を出したい
 
内容が書ききれないので、コメント欄に詳細を追記します。

現在進めている企画の一端で、進むと作業が捗るので
こちらの希望に近いもの、もしくは便利なものであれば
ポイントを弾みます。

わかりづらい点はコメント欄にて回答致しますので
御気軽にご質問いただければと思います。

どうぞお力添えください。よろしくお願い致します。 

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2011/11/15 19:20:26
  • 終了:2011/11/22 07:19:55

ベストアンサー

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/11/15 19:58:36

ポイント500pt

(1)データの作り方 - 集計し易いデータの基本形は縦一列
・毎日の作業は日付とスタッフ、時間帯の3つを必要分だけ追加していく事だけです
・1年分1シートも可能ですが1ヶ月1シートのほうが管理は楽だと思います
・午前/午後の列は無くてもかまいませんが見易さの点で入れてたりします
・日付列の曜日は、セルの表示形式で出せばいいので、入力するのは日付だけです

ABCD
1日付スタッフ時間帯午前/午後
22011/11/1(火)K9=IF(D1<=12,"午前",IF(D1>12,"午後",""))
22011/11/1(火)K10=IF(D2<=12,"午前",IF(D1>12,"午後",""))
22011/11/1(火)K12=IF(D2<=12,"午前",IF(D1>12,"午後",""))

 
(2)集計方法
画像で示しておられる集計シートのほうで、
SUMIF関数などを利用して必要な部分の集計結果を得ます
http://office.microsoft.com/ja-jp/excel-help/HP010342932.aspx
 
具体例を書きたかったのだけど画像がつぶれてしまっててよく見えないのです
フォトライフ(無料)のほうにアップロードして、URLを返信してみてください
http://f.hatena.ne.jp/guide

他5件のコメントを見る
id:windofjuly

>R列とはどこを指すのでしょうか?
 
「担当数」の列です
私が作った表には列名も付けてありますから、
今一度、あなたが作っている表と見比べながらやってみてくださいな
http://f.hatena.ne.jp/windofjuly/20111116071915
 

>1行目というのはどこを指すのでしょうか?
 
10/24の表の1行目
10/31の表の1行目
11/ 7の表の1行目
11/14の表の1行目
11/21の表の1行目・・・以下同様
こちらも私が作った表と見比べながらやってみてくださいな
http://f.hatena.ne.jp/windofjuly/20111116071916
 
 
>「1行目のみ書き換える」という意味がわからなくなってきてしまいます
>上記2点を変更すればよろしいのでしょうか?
10/24の週であれば、10/24の表の1行目左端のセルを示すようにする
10/31の週であれば、10/31の表の1行目左端のセルを示すようにする
11/ 7の週であれば、11/ 7の表の1行目左端のセルを示すようにする
11/14の週であれば、11/14の表の1行目左端のセルを示すようにする
11/21の週であれば、11/21の表の1行目左端のセルを示すようにする・・・以下同様
 
なお、
質問は一週間で勝手にポイント配分されて自動終了となります
時間を割いてなんとかお付き合いしておりますが、
ぎりぎりとなると、こちらの対応が間に合わない場合もありますので、ご注意ください
(評価が決まった質問に引き続き回答することはあまりありません)

2011/11/22 03:22:54
id:MAYARAN

ご回答ありがとうございました!
本日夜までPCを開けないので、先にポイント配分させていただきました。
お時間があったらでいいので、「(3)2週間集計 スタッフ毎、時間毎、曜日毎のSUMPRODUCTの集計範囲」の
公式例等、ご教示いただけるとありがたいです。

4週間については、1週の集計の公式をあてはめていけばいいのでしょうか?

2011/11/22 07:26:07

その他の回答(3件)

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/11/15 19:58:36ここでベストアンサー

ポイント500pt

(1)データの作り方 - 集計し易いデータの基本形は縦一列
・毎日の作業は日付とスタッフ、時間帯の3つを必要分だけ追加していく事だけです
・1年分1シートも可能ですが1ヶ月1シートのほうが管理は楽だと思います
・午前/午後の列は無くてもかまいませんが見易さの点で入れてたりします
・日付列の曜日は、セルの表示形式で出せばいいので、入力するのは日付だけです

ABCD
1日付スタッフ時間帯午前/午後
22011/11/1(火)K9=IF(D1<=12,"午前",IF(D1>12,"午後",""))
22011/11/1(火)K10=IF(D2<=12,"午前",IF(D1>12,"午後",""))
22011/11/1(火)K12=IF(D2<=12,"午前",IF(D1>12,"午後",""))

 
(2)集計方法
画像で示しておられる集計シートのほうで、
SUMIF関数などを利用して必要な部分の集計結果を得ます
http://office.microsoft.com/ja-jp/excel-help/HP010342932.aspx
 
具体例を書きたかったのだけど画像がつぶれてしまっててよく見えないのです
フォトライフ(無料)のほうにアップロードして、URLを返信してみてください
http://f.hatena.ne.jp/guide

他5件のコメントを見る
id:windofjuly

>R列とはどこを指すのでしょうか?
 
「担当数」の列です
私が作った表には列名も付けてありますから、
今一度、あなたが作っている表と見比べながらやってみてくださいな
http://f.hatena.ne.jp/windofjuly/20111116071915
 

>1行目というのはどこを指すのでしょうか?
 
10/24の表の1行目
10/31の表の1行目
11/ 7の表の1行目
11/14の表の1行目
11/21の表の1行目・・・以下同様
こちらも私が作った表と見比べながらやってみてくださいな
http://f.hatena.ne.jp/windofjuly/20111116071916
 
 
>「1行目のみ書き換える」という意味がわからなくなってきてしまいます
>上記2点を変更すればよろしいのでしょうか?
10/24の週であれば、10/24の表の1行目左端のセルを示すようにする
10/31の週であれば、10/31の表の1行目左端のセルを示すようにする
11/ 7の週であれば、11/ 7の表の1行目左端のセルを示すようにする
11/14の週であれば、11/14の表の1行目左端のセルを示すようにする
11/21の週であれば、11/21の表の1行目左端のセルを示すようにする・・・以下同様
 
なお、
質問は一週間で勝手にポイント配分されて自動終了となります
時間を割いてなんとかお付き合いしておりますが、
ぎりぎりとなると、こちらの対応が間に合わない場合もありますので、ご注意ください
(評価が決まった質問に引き続き回答することはあまりありません)

2011/11/22 03:22:54
id:MAYARAN

ご回答ありがとうございました!
本日夜までPCを開けないので、先にポイント配分させていただきました。
お時間があったらでいいので、「(3)2週間集計 スタッフ毎、時間毎、曜日毎のSUMPRODUCTの集計範囲」の
公式例等、ご教示いただけるとありがたいです。

4週間については、1週の集計の公式をあてはめていけばいいのでしょうか?

2011/11/22 07:26:07
id:taknt No.2

きゃづみぃ回答回数13539ベストアンサー獲得回数11982011/11/15 19:59:28

ポイント10pt

http://www11.plala.or.jp/koma_Excel/pivot_menu.html
http://allabout.co.jp/gm/gc/297727/

ピボットテーブルを使ってみませんか?

id:MAYARAN

ご回答ありがとうございます!

ピポットテーブルは以前どなたかにお勧めいただいたことがあるのですが
そのものの原理を理解するまでに時間がかかるのと、
そもそも原理を応用する程の知識がないので、以前チャレンジしましたが
挫折してしまいました。

具体的に質問内容の項目をカバーできるような作業順序がわかればいいのですが、
マニュアルを一から読んで組み立てていく程、企画に時間がかけられず・・・
正直、現在脳みその余裕がありませんw

参考書を読んで組み立てていく作業は割と好きなほうなんですが
それをやっていると物理的にも時間が足りなくなってしまいそうで。。。

今後の参考にさせていただこうと思います。
ありがとうございます。

2011/11/15 21:13:00
id:Baku7770 No.3

Baku7770回答回数2832ベストアンサー獲得回数1812011/11/16 01:22:38

ポイント50pt

 元のデータがどうなっているのかが記載されていないのと、スタッフ別の来客数という考えが業務的に理解しづらいのですが…。
 後半の方は例えばスーパーのレジで扱った件数なのか、指名制度の無いキャバクラのような接客業でホステスがいる時間帯と来客数を調べたいのかでかなり異なります。(客とスタッフが1対1、1対多、多対多)
 以下客とスタッフが1対1、データがレジ打ちのように、スタッフと処理日時の二つでできているとしします。
 1週間集計用、2週間集計用、1ヶ月集計用の3つの列を設けて
IF(1週間内,スタッフ名&(weekday(日時,1)*100+hour(日時),"")
IF(2週間内,スタッフ名&(weekday(日時,1)*100+hour(日時),"")
 そうすれば、K112といった記号が出来上がるので、countif関数で数えます。

id:MAYARAN

ご回答ありがとうございます!
元データの例をフォトライフにアップしておきました。
もしお手すきの時間がございましたらチェックしてみてください。

スタッフ別の来客数というのは、店内にスタッフが常駐していて
10分程個別に時間をとるような形で、1時間毎にそのスタッフが担当した
顧客数を出します。大体1時間に1人から6人くらいまでの数が入ります。

ご提案いただいた数式について、それぞれがどのような役割を果たしているのでしょうか?
IF(1週間内,スタッフ名&(weekday(日時,1)*100+hour(日時),"")
K112というのは何かの数式でしょうか?

ご提案いただいている3列を作成するという案は、
毎日更新していくデータ内にどのように反映すればよろしいでしょうか?
行に「担当別のデータ、日時」列に「時間軸、担当患者合計」を入れるとして
列の部分にその3列を、1週間分の行毎に追加する形になりますか?

毎日の元データを入力、その元データの数値を元に1週間、2週間、4週間の
計を出していこうと思っています。

全体像としてはwindofjulyさんにコメントしていただいているような
形式が近いです。

業務を行う合間に返信を行っているので、返信時間がまちまちになってしまい
大変お手数をおかけしてしまって申し訳ありません。
もしお時間が許すようでしたらお付き合いください。

2011/11/17 09:21:54
id:Baku7770 No.4

Baku7770回答回数2832ベストアンサー獲得回数1812011/11/18 02:11:46

ポイント50pt

 まず、私宛コメントに対する回答から。
>K112というのは何かの数式でしょうか?
 その上の式の計算結果です。詳細は後述。
>IF(1週間内,スタッフ名&(weekday(日時,1)*100+hour(日時),"")
 IF関数については省きます。一週間内というのは「日時>today()-7」で良いでしょう。1ヶ月以内の定義が書かれていなかったのでそうしたまでです。4週間内なのか、同月なのか、前月同日以降、1ヶ月以内だけでもこれだけの意味があります。
 まず「&」の意味ですが文字の結合です。よく使うのが姓と名前が別のセルに入力されているのを1つのセルにまとめたい。といった時に使います。
"山田"&"太郎"⇒"山田太郎"
といった具合です。
 weekday関数は日付から曜日を示す数字を求める関数です。詳細はリンクをご覧下さい。
 hour関数は日付データの何時台かを求める、h時m分のhを取り出す関数です。
 全体としては一週間以内なら、スタッフ名曜日を示す数字時間帯、一週間以内でないなら""(空値)とするといった関数です。
 因みにK112なら日曜の12時台といった意味になります。
 元々のデータが不明だったのでこうしましたが、元のデータがフォトライフのようであれば、時間は不要でしょう。
 これをつけてあげれば曜日毎の集計はSUMIF関数でできます。

id:MAYARAN

ご回答ありがとうございます!
「1週間以内でないなら""(空値)にする」というのはどこを指すのでしょうか?

 IF(1週間内,スタッフ名&(weekday(日時,1)*100+hour(日時),"")

の数式をいじるということですか?

申し訳ありません、正直頭がついていかず、
どのように進めたらいいのかわからなくなってしまいました・・・

時間のあるときに再度見直して、どういう意味なのかエクセルの勉強をする際に
参考にさせていただきます。

2011/11/21 22:56:56
  • id:MAYARAN
     ■1日の集計■
     スタッフ・・・仮にKNSの3人分とする
      
     時間・・・9時10時11時12時 15時16時17時18時19時の9項目
          +午前中の計 午後の計 1日の合計 を出す
     
     日付け、曜日・・・日付け、曜日をふる

     ※画像参照のこと

     ※可能であれば先週の同じ曜日と比べた増減をつける
    (患者数の下に小さく「-1」赤文字、等)
     
     ■1週間の集計■
     月曜日から土曜日まで、スタッフ毎の「1日の合計×6日」分の来客数を出す
     
     ※10/1が土曜日の場合・・・前の月の26日(月)からの1週間の合計をとる
     (可能であればで構いません)

     ■2週間の集計■
     ・スタッフ毎に月~土まで、9時10時11時12時 15時16時17時18時19時について
      2週間の平均数を出す(1週目の数値と2週目の数値を平均化)

     ■1カ月毎の集計■
     ・1ヶ月単位(10/1~10/31等、10/1が土曜日で1週間の集計が取れなくても      10/1は1カ月毎の集計に含める)にスタッフ毎の来客数総合計を出す

     ■上記の項目を月毎に更新しやすいようにする■
     ・基本のデータを、自分でも簡単に数値を打ちこめて作成できる形式で仕上げる
     (可能であればで構いません)
     
     ※10月が終わったので11月分のデータを追加、等

     ■1つのエクセルデータにつき、1年分12個のタグをつけて管理する■
     (可能であればで構いません) 
     
    あまり詳しくないので可能かどうかわかりませんが、
    途中で手を加えられる形式が望ましいです。
    (自分でも作れるレベルのシンプルな計算方法で追加項目がいくつか出てきそうです)

    また上記の内容を1ページ縦スクロールでデータを見やすくしたいと思っています。
    (横スクロールを作らなくても済むように)

    少々ややこしくて申し訳ありません。
    もしこのような形のほうがよい、といったご提案等ございましたら
    ご教示いただけるとうれしいです。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません