エクセル 日報 月集計・年集計について

質問

エクセルで日報を作成しています。
今回、日報のデータを月ごと・年ごと、さらに人別・工事番号別に集計することになり、
どのように構成したらいいか考えており、アイデアをいただけないかと相談させていただきます。

日報の入力項目等は以下の通りです。
・日付
・社員No.（15名程）
・氏名
・工事番号（現在は30件程ですが日々増えます）
・件名
・作業No.（20項目：項目ごとに科目が振り分けられます→作業No.1～3と5は労務費といった具合です）
・作業内容
・作業時間
・科目
（今年度から単価が変わり、前年度の科目と今年度の科目（工事番号で区別しています）で単価が異なります）
・単価

現在は、これらの項目を入力し、人ごとに入力、工事番号と科目で合計時間を出しております。

このデータを月と年で集計をし、人ごと・工事番号ごとのデータとして見られるものにしたいのですが、
ブック内の構成を考えて行き詰っております。
一人ずつブックを作成したらいいのか、
一人１シートで作成し月と年のシートを作成したらいいのか・・・

取っ掛かりから行き詰っていますのでこの先が不安ですが、
こうしたらいいのでは？といったアイデアをいただきたいと思います。

どうぞよろしくお願いいたします。

回答No.10

chayamati · ベストアンサー

今までのことを整理しました

月別表 Sheet名は4月,5月,6月,7月,8月,9月,10月,11月,12月,1月,2月,3月
年計表のSheet名を年計とします

月別Sheet
A列・日付…日付のユーザ設定でGE.MM.DD(AAA)で和暦で曜日まで表示可能
B列・社員No
C列・氏名 式=VLOOKUP(B2,$K$2:$L$21,2,FALSE)
D列・工事番号
E列・件名
F列・作業No
G列・作業内容=VLOOKUP(F2,$O$2:$P$20,2,FALSE))
H列・作業時間
I列・科目
J列 空白
K列・社員No.=IF(年計!A3="","",年計!A3)
L列・社員名=IF(年計!B3="","",年計!B3)
M列・作業時間合計=SUMIF($B$2:$B$19,K2,$H$2:$H$20)
N列 空白
O列・作業No.=IF(年計!T3="","",年計!T3)
P列・作業内容=IF(年計!U3="","",年計!U3)
Q列・作業時間合計=SUMIF($F$2:$F$20,O2,$H$2:$H$21)
※工事番号、件名、科目にも入力規則により選択入力
　にし、作業時間も例えば30分単位の刻みリスト
　を入力規則に適用するとマウスだけの入力が可能になります。

年計シート
1～2行目を見出し行
セルA1「単価見出し」セルB1単価
セルC1～O1セル結合して「社員別時間集計」
セルR1～AF1をセル結合して「作業別時間集計」

A列・社員No.
B列・氏名
C列・4月(時間)='4月'!M2
D列・5月(時間)='5月'!M2
…
N列・3月(時間)='3月'!M2
O列・年合計=SUM(C3:N3)
P列・金額=$B$1*O3*24

Q空白
R列・作業No.
S列・作業内容
T列・4月
…
AF列・年合計=$B$1*AF3
AG列・金額=$B$1*AF3*24

お礼

何度もありがとうございます。
教えていただいた通りやってみました。
大変参考になりました。
ありがとうございました！

回答No.9

chayamati

A～Ｊは質問にある項目を順にＡ列からです
A列・日付…日付のユーザ設定でGE.MM.DD(AAA)で和暦で曜日まで表示可能
B列・社員No
C列・氏名
D列・工事番号
E列・件名
F列・作業No
G列・作業内容
H列・作業時間
J列・科目

回答No.8

chayamati

No.6のchayamatiです
違うものを添付してしまいました。
申し訳ありません

補足です
社員No.と社員No.入力規則を設定するように提案しましたが
　この項目は２桁の数値なので手入力する方が早いですが
　入力は手入力にするにしても設定することによりリストにない
　値は受け付けませんので、誤入力を防げますので必ず設定して下さい

日付、No.の半角入力項目と氏名、工事名等全角入力項目が
　入り乱れています。
　全角入力、半角入力の切り替えが頻繁に起こりますね
　個の切り替えを自動化する方法が入力規則の日本語入力タブ
　で列毎に設定します。

社員Noと氏名、作業No.と作業内容Vlookupの式で対応させる
と思いますが前処理(入力伝票)の流れで問題あるかもわかりませんが
No.を外して氏名、作業内容を入力規則に設定すると項目数が減ります

お礼

何度もアドバイスいただきまして、
ありがとうございました。

回答No.7

naozou2009

自分であれば、データはひとつのテーブルとしてひとつのワークシートに記録していきます。
1レコード（行）に年、月、日、氏名、工事番号、件名、作業No.作業内容、作業時間、科目、単価を
記録します。
入力には”リスト”ワークシートを作成しておき年、月、日、氏名、工事番号、などリスト化出来るものは
リスト化して入力規則を使って作業を簡素化出来るようにします。
年、月、日、を3つのフィールド（列）にしておけば後からオートフィルターやピボットテーブルで簡単に集計出来ます。

お礼

ご回答ありがとうございました。
年月日は別々に設定した方が良かったのですね…
他の部分ではほぼ同じようなものが出来上がったのですが、
年月日のは1つのセルにしてしまいました。
不具合が出たらそのように変更したいと思います。

大変参考になりました。
ありがとうございました。

回答No.6

chayamati

２～３提案します。
データ入力者は1～2名という認識でよいですね

・データシートは月毎に作成します
・年計シートを一つ
以上１３のシートで設計します

データシートには データ項目の右に空白列を挟んで次の月計の列を追加しましす
社員毎の月計用
・社員No. （Ｋ列）
・社員名 （Ｌ列）
・時間計 （Ｍ列=SUMIF($B$2:$B$32,K2,$H$2:$H$32)）
・金額

作業毎の月計用
・作業No.（Ｏ列）
・作業名（Ｐ列）
・時間計（Ｑ列=SUMIF($F$2:$F$32,O2,$H$2:$H$32)）
・金額

年計シートには
社員毎の年計用
・社員No.
・社員名
・…月時間計（12か月分）
・…月金額（12か月分）
・時間年計
・金額年計

作業毎の年計用
・作業No.
・作業名
・…月時間（12か月分）
・…月金額（12か月分）
・時間年計
・金額年計
概要は以上ですが
単価もこの年計シートのどこかに用意します
年計シートには社員No.、社員名、作業No.作業名を入力し
他は月別シートの合計を式で定義します
データシートのＫ列以降の社員No.、社員名、作業No.作業名
年計シートを式で参照します

Ｂ列、Ｆ列の社員No.、作業No.はデータ入力規則で
プルダウンメニューより選択入力するようにします
［データリボン］→［データの入力規則］→［設定］→［リスト］
→［元の値］→［=$K$2:$K$31］

完成すれば雛形としてテンプレートを登録して
毎年ファイル新規作成からこのテンプレートを呼び出します。

テンプレートの登録は名前を付けて登録するからファイルの種類を
テンプレートにして保存します。

工事番号、件名には触れていません

お礼

ご回答ありがとうございました。
アドバイスいただいたように作成してみたのですが、
A~Jはどの項目を設定していただいたのでしょうか？
ご教示いただけると助かります。

回答No.5

hallo-2007

いやいや
＞まずは一括で入力するシートと月、年のシートを作り、
＞データを移してみたいと思います。
でなくて、一枚のシートにひたすら縦方向へデータを入力するだけで十分です。
必要なのは、データを抽出するシート
一例ですが、別途　月報というシートを作成したとします
　Ａ１に月の最初の　　Ｂ１に月の最後の日をいれると
以下に、データのシートからその期間のデータを抽出してくる機能を考えます。
フィルターオプションという機能がありますし、ＶＢＡが使えるのでほかの方法
でも可能でしょう。

必要なシートとしては
１、データシート　
２、期間指定して、データを抽出するシート
３、社員名簿　社員Ｎｏと社員名など
４、社業項目　作業Ｎｏと科目、単価など　（これで、科目、単価の入力が不要になる）
もし、旧年度、新年度で単価の変更があったのであれば、新しく作業番号を増やして科目、単価の
設定を行う方が良いでしょう。これからも単価変更など発生すると思いますし。

必要に応じて、社員別のデータ抽出、集計のためのシートなどが増える可能性があります。

月や年度でシートを増やしてはいけません。
発生する業務に対応するシートの必要性で考えます。

お礼

ご回答ありがとうございました。
うまく伝わらなかったようで申し訳ありません。
＞まずは一括で入力するシートと月、年のシートを作り、
＞データを移してみたいと思います。
についてですが、
一枚のシートにひたすら縦方向へデータを入力するだけ
というのは一括で入力するシートで、
データを抽出するシートというのは月・年のシートです。
基本となるDBシート、入力用のシート、
入力用のシートから月と年のシートに抽出しようと思った次第です。

おっしゃっていることと同じのような気がするのですが…
解釈違いでしたら申し訳ありません。

回答No.4

KURUMITO

エクセルの特徴を最大限に生かすためにはどうすればよいかを考えます。それにはできるだけブックの数は一つにして、ブックの中でも出来るだけシートの数を少なくする。しかも毎日入力するシートは一つにする。1つのシートでも15名程度を対象とする限りは何年間も使用することができますね。また、数年経過して改めたいということでしたら、そのブックのコピーを作ってブックの名前を変えて保存します。
お示しのような項目を持つシートをすべての元帳として例えばシート1に作るとします。
そこで氏名や社員Ｎｏについてはデータベースとしてシート2に表を用意し、そこからシート1ではリスト機能（データの入力規則）やＶＬＯＯＫＵＰ関数などを使って半ば自動的に表示できるようにします。
作業Ｎｏ、科目、単価などについてもデータベースとしてシート2又はシート3に用意して、それらのデータがシート1で利用できるようにします。
1件のデータについて複数の行を使うといったことはできるだけ避けます。日付については2013/2/24といった入力にします。あるいは今年のの2月24日なら2/24のように入力してもよいでしょう。作業時間についても8:00や21:30のような形で入力します。これらのことはすべてこれらのデータを関数を使って処理する場合には特に重要ですね。
なお、データベースのデータがシート1の関数で参照先になっている場合には、データベースの値を追加することは良いのですが変更する場合にはシート1での過去のデータも変更されてしまいますので注意が必要ですね。
月のシートは別にシートを用意して関数を使ってシート1のデータから表示できるようにします。
年のシートについても別に用意したほうがよいでしょう。
15人程度ですから人事面のデータは別として上記のシート内で表示できるようにしてはどうでしょう。別に個々人のためのシートを用意することも必要ないと思いますが。
なお、シート1から月や年のデータを容易に利用できるようにするにはデータ数も多くなることから難しい配列式などを使用しないで出来るだけシート1に作業列を用意して対応することです。
どのような作業列にすればよいのかは月や年でまとめる条件項目によって違ってきますのである程度の構想ができた段階で相談されるとよいでしょう。

お礼

ご回答ありがとうございます。
個人個人のシートは必要ないのですね。
アドバイスいただいたような形で現在作成しておりますが、
月と年のシートへの抽出が難しいです。
また、改めて相談しなければいけないかなといったところです。

大変参考になりました。
ありがとうございました。

回答No.3

tom04

こんばんは！
人それぞれで色々やり方はあると思いますが、
個人的見解として、集計等々を行う必要がある場合は極力1Sheetにまとめた方がやりやすいと思います。

お使いのExcelのバージョンが判りませんが、
Excel2003以前でも65536行・Excel2007以降ですと104万以上の行数が使用できます。

どうしても別シートに分けたいという場合は1年程度を1Sheetとしてやってみてはどうでしょう。

やり方としては
一つのSheetの列方向にに社員・工事番号等々の項目を作りあとはデータを行方向（下方向）に
社員・工事名等気にせず入力していきます。

あとは必要条件でオートフィルタをかけます。

仮にH列に作業時間がありその合計を表示したい場合は
どこか使っていないセル（項目行の別列が良いと思います　←オートフィルタで非表示にならない行）に
=SUBTOTAL(9,H:H)
という数式を入れておきます。

これでオートフィルタで表示されている行だけの合計が表示されます。

この程度しか思いつきませんが
他に良い方法があればごめんなさいね。m(_ _)m

お礼

こんばんは！早々のご回答ありがとうございました。
現在の日報は入力したデータを前年度の工事番号か、今年度の工事番号かを関数で判断して、VBAで工事番号と科目で合計を出しておりまして、
オートフィルタをまったく思いつきませんでした。
早速、実験してみたいと思います。
また、バージョンを書き忘れてしまいましたが、
７になります。
何かまたいいアイデアがありましたらよろしくお願いいたします。

回答No.2

はる のパパ（@van62097）

大変な仕事ですね。
この仕組み(システム設計)は当初の設計をはっきり立てないと、後々の拡張に対応できなくなります。
ざっくりと感じたことは、ひとつのシートに各項目をセルで並べ、ひとまとめに集計する方が良いのではと思います。
工事番号が増える事を見越して、「月ごとのシート」で集計し、年間に持っていく方が対応し易いと思います。
また、科目も単価が変わるなら「現単価」から単価が変わる「変更月」も指定出来る様に設計されては如何でしょうか。
ブックを別にするより、一つのブック内で対応した方が設計し易いと思います。
このシートで請求書等作成するなら詳細な設計が必要かと思いますが、EXcelでここまで管理出来れば最高のものが出来ると思いますよ。
あなたがお使いのExcelのバージョンは分かりませんし、これが完成すれば立派なシステム担当者に成れますよ。
少し大変な作業になりますが、シートをこまめに分けて一つ一つ集計する方をお勧めします。

お礼

こんばんは！早々のご回答ありがとうございます。
また、エクセルのバージョンを書き忘れて質問してしまいまして…
申し訳ありません。2007になります。
いつも思いついたままに作成してしまい、修正修正と繰り返してしまっているので、初めにご相談させていただいた次第です。
ブックは別けない方がいいのですね。
現在の日報はユーザーフォームを作成し入力を行い、工事番号で何番以降、新単価といった区切りになっておりまして、それを関数で判断させてVBAで工事番号、科目で合計時間を出しております。
そのデータから請求書を作成することはありませんが、労務費などお金に関わるデータですので、慎重にじっくり考えて作成していきたいと考えております。
まずは一括で入力するシートと月、年のシートを作り、
データを移してみたいと思います。
アドバイスいただきありがとうございました。

回答No.1

tsubuyuki

実に難しい質問ですね。

> こうしたらいいのでは？といったアイデアをいただきたい

まずは「エクセルとはどういう事が出来るソフトか？」と言うのを覚えるために
虎の巻的な本を一冊購入し、学習されることを強くオススメします。

お礼

こんばんは！
ご回答ありがとうございます。
きちんと勉強したことがなかったので、みなさんはどういった具合に作り上げていくのか、お伺いした次第です。
これを機にきちんと勉強しないといけないですね。
ありがとうございました。