エクセルで日報を作成しています。
今回、日報のデータを月ごと・年ごと、さらに人別・工事番号別に集計することになり、
どのように構成したらいいか考えており、アイデアをいただけないかと相談させていただきます。
日報の入力項目等は以下の通りです。
・日付
・社員No.(15名程)
・氏名
・工事番号(現在は30件程ですが日々増えます)
・件名
・作業No.(20項目:項目ごとに科目が振り分けられます→作業No.1~3と5は労務費といった具合です)
・作業内容
・作業時間
・科目
(今年度から単価が変わり、前年度の科目と今年度の科目(工事番号で区別しています)で単価が異なります)
・単価
現在は、これらの項目を入力し、人ごとに入力、工事番号と科目で合計時間を出しております。
このデータを月と年で集計をし、人ごと・工事番号ごとのデータとして見られるものにしたいのですが、
ブック内の構成を考えて行き詰っております。
一人ずつブックを作成したらいいのか、
一人1シートで作成し月と年のシートを作成したらいいのか・・・
取っ掛かりから行き詰っていますのでこの先が不安ですが、
こうしたらいいのでは?といったアイデアをいただきたいと思います。
どうぞよろしくお願いいたします。
No.10ベストアンサー
- 回答日時:
今までのことを整理しました
月別表 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
- 回答日時:
A~Jは質問にある項目を順にA列からです
A列・日付…日付のユーザ設定でGE.MM.DD(AAA)で和暦で曜日まで表示可能
B列・社員No
C列・氏名
D列・工事番号
E列・件名
F列・作業No
G列・作業内容
H列・作業時間
J列・科目
No.8
- 回答日時:
No.6のchayamatiです
違うものを添付してしまいました。
申し訳ありません
補足です
社員No.と社員No.入力規則を設定するように提案しましたが
この項目は2桁の数値なので手入力する方が早いですが
入力は手入力にするにしても設定することによりリストにない
値は受け付けませんので、誤入力を防げますので必ず設定して下さい
日付、No.の半角入力項目と氏名、工事名等全角入力項目が
入り乱れています。
全角入力、半角入力の切り替えが頻繁に起こりますね
個の切り替えを自動化する方法が入力規則の日本語入力タブ
で列毎に設定します。
社員Noと氏名、作業No.と作業内容Vlookupの式で対応させる
と思いますが前処理(入力伝票)の流れで問題あるかもわかりませんが
No.を外して氏名、作業内容を入力規則に設定すると項目数が減ります
No.7
- 回答日時:
自分であれば、データはひとつのテーブルとしてひとつのワークシートに記録していきます。
1レコード(行)に年、月、日、氏名、工事番号、件名、作業No.作業内容、作業時間、科目、単価を
記録します。
入力には”リスト”ワークシートを作成しておき年、月、日、氏名、工事番号、などリスト化出来るものは
リスト化して入力規則を使って作業を簡素化出来るようにします。
年、月、日、を3つのフィールド(列)にしておけば後からオートフィルターやピボットテーブルで簡単に集計出来ます。
ご回答ありがとうございました。
年月日は別々に設定した方が良かったのですね…
他の部分ではほぼ同じようなものが出来上がったのですが、
年月日のは1つのセルにしてしまいました。
不具合が出たらそのように変更したいと思います。
大変参考になりました。
ありがとうございました。
No.6
- 回答日時:
2~3提案します。
データ入力者は1~2名という認識でよいですね
・データシートは月毎に作成します
・年計シートを一つ
以上13のシートで設計します
データシートには データ項目の右に空白列を挟んで次の月計の列を追加しましす
社員毎の月計用
・社員No. (K列)
・社員名 (L列)
・時間計 (M列=SUMIF($B$2:$B$32,K2,$H$2:$H$32))
・金額
作業毎の月計用
・作業No.(O列)
・作業名(P列)
・時間計(Q列=SUMIF($F$2:$F$32,O2,$H$2:$H$32))
・金額
年計シートには
社員毎の年計用
・社員No.
・社員名
・…月時間計(12か月分)
・…月金額(12か月分)
・時間年計
・金額年計
作業毎の年計用
・作業No.
・作業名
・…月時間(12か月分)
・…月金額(12か月分)
・時間年計
・金額年計
概要は以上ですが
単価もこの年計シートのどこかに用意します
年計シートには社員No.、社員名、作業No.作業名を入力し
他は月別シートの合計を式で定義します
データシートのK列以降の社員No.、社員名、作業No.作業名
年計シートを式で参照します
B列、F列の社員No.、作業No.はデータ入力規則で
プルダウンメニューより選択入力するようにします
[データリボン]→[データの入力規則]→[設定]→[リスト]
→[元の値]→[=$K$2:$K$31]
完成すれば雛形としてテンプレートを登録して
毎年ファイル新規作成からこのテンプレートを呼び出します。
テンプレートの登録は名前を付けて登録するからファイルの種類を
テンプレートにして保存します。
工事番号、件名には触れていません
ご回答ありがとうございました。
アドバイスいただいたように作成してみたのですが、
A~Jはどの項目を設定していただいたのでしょうか?
ご教示いただけると助かります。
No.5
- 回答日時:
いやいや
>まずは一括で入力するシートと月、年のシートを作り、
>データを移してみたいと思います。
でなくて、一枚のシートにひたすら縦方向へデータを入力するだけで十分です。
必要なのは、データを抽出するシート
一例ですが、別途 月報というシートを作成したとします
A1に月の最初の B1に月の最後の日をいれると
以下に、データのシートからその期間のデータを抽出してくる機能を考えます。
フィルターオプションという機能がありますし、VBAが使えるのでほかの方法
でも可能でしょう。
必要なシートとしては
1、データシート
2、期間指定して、データを抽出するシート
3、社員名簿 社員Noと社員名など
4、社業項目 作業Noと科目、単価など (これで、科目、単価の入力が不要になる)
もし、旧年度、新年度で単価の変更があったのであれば、新しく作業番号を増やして科目、単価の
設定を行う方が良いでしょう。これからも単価変更など発生すると思いますし。
必要に応じて、社員別のデータ抽出、集計のためのシートなどが増える可能性があります。
月や年度でシートを増やしてはいけません。
発生する業務に対応するシートの必要性で考えます。
ご回答ありがとうございました。
うまく伝わらなかったようで申し訳ありません。
>まずは一括で入力するシートと月、年のシートを作り、
>データを移してみたいと思います。
についてですが、
一枚のシートにひたすら縦方向へデータを入力するだけ
というのは一括で入力するシートで、
データを抽出するシートというのは月・年のシートです。
基本となるDBシート、入力用のシート、
入力用のシートから月と年のシートに抽出しようと思った次第です。
おっしゃっていることと同じのような気がするのですが…
解釈違いでしたら申し訳ありません。
No.4
- 回答日時:
エクセルの特徴を最大限に生かすためにはどうすればよいかを考えます。
それにはできるだけブックの数は一つにして、ブックの中でも出来るだけシートの数を少なくする。しかも毎日入力するシートは一つにする。1つのシートでも15名程度を対象とする限りは何年間も使用することができますね。また、数年経過して改めたいということでしたら、そのブックのコピーを作ってブックの名前を変えて保存します。お示しのような項目を持つシートをすべての元帳として例えばシート1に作るとします。
そこで氏名や社員Noについてはデータベースとしてシート2に表を用意し、そこからシート1ではリスト機能(データの入力規則)やVLOOKUP関数などを使って半ば自動的に表示できるようにします。
作業No、科目、単価などについてもデータベースとしてシート2又はシート3に用意して、それらのデータがシート1で利用できるようにします。
1件のデータについて複数の行を使うといったことはできるだけ避けます。日付については2013/2/24といった入力にします。あるいは今年のの2月24日なら2/24のように入力してもよいでしょう。作業時間についても8:00や21:30のような形で入力します。これらのことはすべてこれらのデータを関数を使って処理する場合には特に重要ですね。
なお、データベースのデータがシート1の関数で参照先になっている場合には、データベースの値を追加することは良いのですが変更する場合にはシート1での過去のデータも変更されてしまいますので注意が必要ですね。
月のシートは別にシートを用意して関数を使ってシート1のデータから表示できるようにします。
年のシートについても別に用意したほうがよいでしょう。
15人程度ですから人事面のデータは別として上記のシート内で表示できるようにしてはどうでしょう。別に個々人のためのシートを用意することも必要ないと思いますが。
なお、シート1から月や年のデータを容易に利用できるようにするにはデータ数も多くなることから難しい配列式などを使用しないで出来るだけシート1に作業列を用意して対応することです。
どのような作業列にすればよいのかは月や年でまとめる条件項目によって違ってきますのである程度の構想ができた段階で相談されるとよいでしょう。
ご回答ありがとうございます。
個人個人のシートは必要ないのですね。
アドバイスいただいたような形で現在作成しておりますが、
月と年のシートへの抽出が難しいです。
また、改めて相談しなければいけないかなといったところです。
大変参考になりました。
ありがとうございました。
No.3
- 回答日時:
こんばんは!
人それぞれで色々やり方はあると思いますが、
個人的見解として、集計等々を行う必要がある場合は極力1Sheetにまとめた方がやりやすいと思います。
お使いのExcelのバージョンが判りませんが、
Excel2003以前でも65536行・Excel2007以降ですと104万以上の行数が使用できます。
どうしても別シートに分けたいという場合は1年程度を1Sheetとしてやってみてはどうでしょう。
やり方としては
一つのSheetの列方向にに社員・工事番号等々の項目を作りあとはデータを行方向(下方向)に
社員・工事名等気にせず入力していきます。
あとは必要条件でオートフィルタをかけます。
仮にH列に作業時間がありその合計を表示したい場合は
どこか使っていないセル(項目行の別列が良いと思います ←オートフィルタで非表示にならない行)に
=SUBTOTAL(9,H:H)
という数式を入れておきます。
これでオートフィルタで表示されている行だけの合計が表示されます。
この程度しか思いつきませんが
他に良い方法があればごめんなさいね。m(_ _)m
こんばんは!早々のご回答ありがとうございました。
現在の日報は入力したデータを前年度の工事番号か、今年度の工事番号かを関数で判断して、VBAで工事番号と科目で合計を出しておりまして、
オートフィルタをまったく思いつきませんでした。
早速、実験してみたいと思います。
また、バージョンを書き忘れてしまいましたが、
7になります。
何かまたいいアイデアがありましたらよろしくお願いいたします。
No.2
- 回答日時:
大変な仕事ですね。
この仕組み(システム設計)は当初の設計をはっきり立てないと、後々の拡張に対応できなくなります。
ざっくりと感じたことは、ひとつのシートに各項目をセルで並べ、ひとまとめに集計する方が良いのではと思います。
工事番号が増える事を見越して、「月ごとのシート」で集計し、年間に持っていく方が対応し易いと思います。
また、科目も単価が変わるなら「現単価」から単価が変わる「変更月」も指定出来る様に設計されては如何でしょうか。
ブックを別にするより、一つのブック内で対応した方が設計し易いと思います。
このシートで請求書等作成するなら詳細な設計が必要かと思いますが、EXcelでここまで管理出来れば最高のものが出来ると思いますよ。
あなたがお使いのExcelのバージョンは分かりませんし、これが完成すれば立派なシステム担当者に成れますよ。
少し大変な作業になりますが、シートをこまめに分けて一つ一つ集計する方をお勧めします。
こんばんは!早々のご回答ありがとうございます。
また、エクセルのバージョンを書き忘れて質問してしまいまして…
申し訳ありません。2007になります。
いつも思いついたままに作成してしまい、修正修正と繰り返してしまっているので、初めにご相談させていただいた次第です。
ブックは別けない方がいいのですね。
現在の日報はユーザーフォームを作成し入力を行い、工事番号で何番以降、新単価といった区切りになっておりまして、それを関数で判断させてVBAで工事番号、科目で合計時間を出しております。
そのデータから請求書を作成することはありませんが、労務費などお金に関わるデータですので、慎重にじっくり考えて作成していきたいと考えております。
まずは一括で入力するシートと月、年のシートを作り、
データを移してみたいと思います。
アドバイスいただきありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Visual Basic(VBA) Excel VBA マクロ ホルダー内にある複数のファイルを規則に従い格納をしたいです 4 2022/11/19 10:58
- その他(プログラミング・Web制作) 入力フォームへ、データを自動的に入力するプログラム。どうやって作る? 4 2023/01/16 10:24
- システム CSVファイルのマッピング処理の省力化 1 2022/11/24 00:01
- Visual Basic(VBA) VBAで自動集計(特定セルコピー月ごとに値貼り付け)したい。 6 2023/06/25 11:37
- Excel(エクセル) 非表示にしたい行をグループ化して折り畳み 4 2022/09/17 20:17
- リフォーム・リノベーション 汚水枡改修工事の見積額は妥当でしょうか? 2 2022/06/30 09:01
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- ソフトウェア データ入力のインターフェースについて 2 2022/09/27 09:07
このQ&Aを見た人はこんなQ&Aも見ています
-
賃貸で可能な古民家風レトロな部屋作りのコツ!改めて知る畳の高い機能性と魅力も紹介
畳の部屋を雰囲気のよい部屋に仕上げたい!賃貸住宅でもできる古民家風のレトロな部屋作りのコツを伺った。
-
エクセル 日報に入力すると月報に総合計金額が表示できるようにしたい
Excel(エクセル)
-
Excelで日報から月報へとデータ参照をさせたい。
Excel(エクセル)
-
エクセルで日報と月報を作成中です。日付と活動内容の件数を計算する関数に悩んでいます。この場合、cou
Excel(エクセル)
-
-
4
毎日の日計を別シートに自動で更新、反映させたい
PowerPoint(パワーポイント)
-
5
エクセル 日計表から月計表に自動入力
その他(Microsoft Office)
-
6
エクセル 日報売上を月報に展開するマクロで困っております。準備するシート(1)日報入力シート
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
下記マクロでMsgBox "空白です...
-
在庫管理表に使うエクセルの関...
-
エクセルでバーコード作成し使...
-
【マクロ】for next構文について
-
ユーザー定義関数をアドイン登...
-
職場の人から聞かれており、こ...
-
PDFの請求明細をエクセルにしたい
-
エクセルで表
-
Excel関数-文字列で自動作成さ...
-
Microsoft Officeの中古は信用...
-
Excelデータをコピペして、ペー...
-
Excelで50個のセルに同じ文字を...
-
エクセルで会社の従業員のデー...
-
エクセルの関数について教えて...
-
エクセルの表で1年間の曜日を...
-
スプレッドシート、Excelでの数...
-
Excelで、項目の種類ごとにカウ...
-
「問題が発生しました」ですと?
-
LOOKUP関数を使えばいいのでし...
-
Excel:一部のフォントでセルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報