人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

エクセルで来客数をスタッフ別、曜日別、時間別に集計できる表を
作成したいと思っております。
以前、お力添えいただいた方、ご協力をお願いできれば幸いです。

<やりたいこと>
・来客数をスタッフ別、曜日別、時間別に集計できる表を作成し
1週間毎(月?土)にスタッフ別に来客数合計を集計、
2週間毎に(月?土×2)スタッフ別、曜日別、時間別に集計
1カ月単位でスタッフ別に来客数の総合計を出したい

内容が書ききれないので、コメント欄に詳細を追記します。

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

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

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


1321352424
●拡大する

●質問者: MAYARAN
●カテゴリ:ビジネス・経営 コンピュータ
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● うぃんど
●500ポイント ベストアンサー

(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


MAYARANさんのコメント
ご回答ありがとうございます! 申し訳ありません、キャプチャで画像をコピーしたら画素が粗かったようです。 エクセルをコピーしたものをイラレでJPEGにして フォトライフにアップしました。 お時間のあるときにご覧いただいてもよろしいでしょうか?

うぃんどさんのコメント
(1)元データの整理 左側の表を Sheet1 に1年分つらつらと縦長に記述するものとしますが、 集計しやすいように下記のように日付を全てのデータにつけるようにします http://f.hatena.ne.jp/windofjuly/20111116071915 上記にあるように人が増減してもOKです 最初から年末までの枠を作っておく必要は無く、 適当に数日分の枠をコピペして縦に増やしていけばいいです (2)週間集計 Sheet2 に1年分をつらつらと縦長に記述するものとします http://f.hatena.ne.jp/windofjuly/20111116071916 こちらも必要な分だけコピペして縦に増やしていけばいいです 式は下記のような具合になります >|| セルB2=SUMPRODUCT((Sheet1!$A$1:$A$10000>=Sheet2!$A$1)*(Sheet1!$A$1:$A$10000<Sheet2!$A$1+7)*(Sheet1!$B$1:$B$10000=Sheet2!$A2),(Sheet1!$R$1:$R$10000)) セルD2=SUMPRODUCT((Sheet1!$A$1:$A$10000>=Sheet2!$A$1)*(Sheet1!$A$1:$A$10000<Sheet2!$A$1+7)*(Sheet1!$B$1:$B$10000=Sheet2!$A2),(Sheet1!$S$1:$S$10000)) ||< 式の意味は(Sheet1のA1:A10000の日付がSheet2のA1以上)かつ(日付がA1+7未満)かつ(Sheet1のB1:B10000のスタッフがSheet2のA2と同じ)であれば、同じ行にあるR列の値を集計しろとなっています コピペしたらまずは左上に週の初めの日(例では10/24と10/31)を記入します 週が変わる場合は一行目の式を書き換える必要があります >|| セルB8=SUMPRODUCT((Sheet1!$A$1:$A$10000>=Sheet2!$A$7)*(Sheet1!$A$1:$A$10000<Sheet2!$A$7+7)*(Sheet1!$B$1:$B$10000=Sheet2!$A2),(Sheet1!$R$1:$R$10000)) セルD8=SUMPRODUCT((Sheet1!$A$1:$A$10000>=Sheet2!$A$7)*(Sheet1!$A$1:$A$10000<Sheet2!$A$7+7)*(Sheet1!$B$1:$B$10000=Sheet2!$A2),(Sheet1!$S$1:$S$10000)) ||< 前週との比較は引き算するだけなので割愛してます (3)2週間集計 上で用いたSUMPRODUCTの集計範囲にて日付とスタッフおよび曜日を指定することで行いますが、 まずは上記まできっちり理解してからにしましょう (関数の使い方を理解しないと、間違った結果を利用してしまうという最悪の事態に発展しますので・・・)

MAYARANさんのコメント
ご回答ありがとうございます! 具体的で非常に助かります! かいつまんでの質問になってしまうのですが・・・ <日付の付け方> ・「10/4 火」を一つのセルに入れる場合、10/5 水 10/6 木と 選択&ドラッグでセルに自動的に入力させたいのですが これはセルを2つ(1行目に日、2行目に曜日)を入れることで解決させればいいですか? 他によい方法はございますでしょうか?

うぃんどさんのコメント
>日付の付け方 わざわざ曜日を入力する必要はないですよ 日付の入っているセルの書式設定で、 「日付」ではなく「ユーザー定義」を選択して、 下記のようにすれば月/日 曜日が表示されるようになります m/d aaa

MAYARANさんのコメント
ご回答ありがとうございます! 日付の付け方について、試してみたらクリック&ドラッグで曜日指定までできました。 ---------------------------------------------------------------------- (2)週間集計 <同じ行にあるR列> →R列とはどこを指すのでしょうか? <コピペしたらまずは左上に週の初めの日(例では10/24と10/31)を記入します 週が変わる場合は一行目の式を書き換える必要があります セルB8=SUMPRODUCT((Sheet1!$A$1:$A$10000>=Sheet2!$A$7)*(Sheet1!$A$1:$A$10000<Sheet2!$A$7+7)*(Sheet1!$B$1:$B$10000=Sheet2!$A2),(Sheet1!$R$1:$R$10000)) セルD8=SUMPRODUCT((Sheet1!$A$1:$A$10000>=Sheet2!$A$7)*(Sheet1!$A$1:$A$10000<Sheet2!$A$7+7)*(Sheet1!$B$1:$B$10000=Sheet2!$A2),(Sheet1!$S$1:$S$10000)) 前週との比較は引き算するだけなので割愛してます> →ということですが、1行目というのはどこを指すのでしょうか? 最初に示していただいた数式ですが・・・ B2=SUMPRODUCT((Sheet1!$A$1:$A$10000>=Sheet2!$A$1)*(Sheet1!$A$1:$A$10000<Sheet2!$A$1+7)*(Sheet1!$B$1:$B$10000=Sheet2!$A2),(Sheet1!$R$1:$R$10000)) 回答文1行目の「$A$1」が「$A$7」に変わっている、 回答文2行目の「$A$1+7」が「$A$1+7」に変わっている、 ぐらいしかわかりません。 これだと「1行目のみ書き換える」という意味がわからなくなってきてしまいます。 上記2点を変更すればよろしいのでしょうか? また10/24と10/31の分については上記の変更ということでわかったのですが、 11/7の分等は具体的にどこの数値をどのように変更したらよろしいのでしょうか? 物分かりが悪く、大変お手数をおかけ致します。 よろしくお願い致します。

うぃんどさんのコメント
>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行目左端のセルを示すようにする・・・以下同様 なお、 質問は一週間で勝手にポイント配分されて自動終了となります 時間を割いてなんとかお付き合いしておりますが、 ぎりぎりとなると、こちらの対応が間に合わない場合もありますので、ご注意ください (評価が決まった質問に引き続き回答することはあまりありません)

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

2 ● きゃづみぃ
●10ポイント

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

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


MAYARANさんのコメント
ご回答ありがとうございます! ピポットテーブルは以前どなたかにお勧めいただいたことがあるのですが そのものの原理を理解するまでに時間がかかるのと、 そもそも原理を応用する程の知識がないので、以前チャレンジしましたが 挫折してしまいました。 具体的に質問内容の項目をカバーできるような作業順序がわかればいいのですが、 マニュアルを一から読んで組み立てていく程、企画に時間がかけられず・・・ 正直、現在脳みその余裕がありませんw 参考書を読んで組み立てていく作業は割と好きなほうなんですが それをやっていると物理的にも時間が足りなくなってしまいそうで。。。 今後の参考にさせていただこうと思います。 ありがとうございます。

3 ● Baku7770
●50ポイント

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


MAYARANさんのコメント
ご回答ありがとうございます! 元データの例をフォトライフにアップしておきました。 もしお手すきの時間がございましたらチェックしてみてください。 スタッフ別の来客数というのは、店内にスタッフが常駐していて 10分程個別に時間をとるような形で、1時間毎にそのスタッフが担当した 顧客数を出します。大体1時間に1人から6人くらいまでの数が入ります。 ご提案いただいた数式について、それぞれがどのような役割を果たしているのでしょうか? IF(1週間内,スタッフ名&(weekday(日時,1)*100+hour(日時),"") K112というのは何かの数式でしょうか? ご提案いただいている3列を作成するという案は、 毎日更新していくデータ内にどのように反映すればよろしいでしょうか? 行に「担当別のデータ、日時」列に「時間軸、担当患者合計」を入れるとして 列の部分にその3列を、1週間分の行毎に追加する形になりますか? 毎日の元データを入力、その元データの数値を元に1週間、2週間、4週間の 計を出していこうと思っています。 全体像としてはwindofjulyさんにコメントしていただいているような 形式が近いです。 業務を行う合間に返信を行っているので、返信時間がまちまちになってしまい 大変お手数をおかけしてしまって申し訳ありません。 もしお時間が許すようでしたらお付き合いください。

4 ● Baku7770
●50ポイント

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


MAYARANさんのコメント
ご回答ありがとうございます! 「1週間以内でないなら""(空値)にする」というのはどこを指すのでしょうか? IF(1週間内,スタッフ名&(weekday(日時,1)*100+hour(日時),"") の数式をいじるということですか? 申し訳ありません、正直頭がついていかず、 どのように進めたらいいのかわからなくなってしまいました・・・ 時間のあるときに再度見直して、どういう意味なのかエクセルの勉強をする際に 参考にさせていただきます。
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ