会社で出勤簿を作成しています。
下記画像のような形でA列のセルには個人名が入ります。
シートで月々に分けており、最後に「今年度」として集計をしたいと考えています。
この集計をする際に、始めは
「='4月'!B6+'5月'!B6+'6月'!B6+'7月'!B6+'8月'!B6+'9月'!B6+'10月'!B6+'11月'!B6+'12月'!B6+'1月'!B6+'2月'!B6+'3月'!B6」
このような式を入れて、Aさんの1月、2月と全て足し算をしていたのですが
表計算で出すことはできませんか?
ちなみに細かく記入しており、大まかに説明すると、
B例は「電車遅延の遅刻回数」
C列は「電車遅延の遅刻時間」
D列は「バス遅延の遅刻回数」
E列は「バス遅延の遅刻時間」
その他には「病気(病院)」・「その他」「早退」があります。
1人ずつ足していると、根気がいる作業になり、1つ間違えるとやり直しになってしまいます。
次年度もこの出勤簿を使用したいと考えているので、社員が辞めた・入った場合に
また式を足して変えなきゃいけないのかと思うと・・・・
なにかいい方法があれば教えて下さい。
No.4ベストアンサー
- 回答日時:
こんばんは!
横からお邪魔します。
本来であれば串刺し計算で大丈夫だろうと思いますが、
Sheetによっては、行削除している場合があるというコトですので、関数では厄介になります。
そこでVBAになってしまいますが、一例です。
アップされている画像通り、Sheet見出しの2番目~13番目が集計対象Sheetで(←必須です★)
「今年度」Sheetにすべてのセルの集計をするという解釈です。
すべてのSheetは6行目以降にデータがあり、列方向の並びは同じだとします。
A列はバラバラでも構いませんが、
「今年度」SheetのA列にはすべての氏名が入力されているという前提です。
Alt+F11キー → メニュー → 挿入 → 標準モジュール → VBE画面のカーソルが点滅しているところに
↓のコードをコピー&ペースト → Excel画面に戻り(VBE画面を閉じて)マクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub Sample1() 'この行から
Dim k As Long, lastRow As Long
Dim wS As Worksheet, wSs As Worksheet
Application.ScreenUpdating = False
With Worksheets("今年度")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'▼ 今年度SheetのB6以降のデータを一旦消去
If lastRow > 5 Then
Range(.Cells(6, "B"), .Cells(lastRow, "S")).ClearContents
End If
'▼ 「作業用」SheetをSheet見出しの最後に挿入し、4月~3月Sheetのデータをすべてコピー&ペースト
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "作業用"
Set wSs = Worksheets("作業用")
'▼ Sheet見出しの2番目~13番目のSheetまで
For k = 2 To 13
Set wS = Worksheets(k)
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
Range(wS.Cells(6, "A"), wS.Cells(lastRow, "S")).Copy wSs.Cells(Rows.Count, "A").End(xlUp).Offset(1)
Next k
'▼ 「今年度」SheetのすべてB6~S列最終行セルにSUMIF関数で集計
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
With Range(.Cells(6, "B"), .Cells(lastRow, "S"))
.Formula = "=SUMIF(作業用!$A:$A,$A6,作業用!B:B)"
.Value = .Value
End With
'▼ 「作業用」Sheetを削除
Application.DisplayAlerts = False
wSs.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
.Activate
End With
MsgBox "処理完了"
End Sub 'この行まで
※ 一旦マクロを実行すると元に戻せませんので、
別Bookにデータをコピー&ペーストしてマクロを試してみてください。
※ 関数でないので、データ変更があるたびにマクロを実行する必要があります。
こんな感じではどうでしょうか?m(_ _)m
No.3
- 回答日時:
>エクセルの基本は知っています。
それは失礼をしました。
集計に特に影響が出ない方法としてもう一つ、9月のCさんの行を非表示にする事で対処できるかと思います。
>シートで月々に分けており、最後に「今年度」として集計をしたいと考えています。
この集計をする際に、始めは
「='4月'!B6+'5月'!B6+'6月'!B6+'7月'!B6+'8月'!B6+'9月'!B6+'10月'!B6+'11月'!B6+'12月'!B6+'1月'!B6+'2月'!B6+'3月'!B6」
このような式を入れて、Aさんの1月、2月と全て足し算をしていたのですが
表計算で出すことはできませんか?
質問文には『抽出』と言う言葉は一言も書かれていませんが?
No.2
- 回答日時:
>出勤簿なので、9月にCさんが辞めた場合10月からCさんの行はなくなりますのでDさんが1つ上がります。
エクセルの基本を覚えておくといいです。
セル参照で結果を出している以上、参照元のセル、あるいは行や列などを削除すると計算式も全てくるってきます。
定数は「数値を直接入力」しますが、セル参照は「セル番地」を参照しますので、削除をした場合、全く違う結果が出たり、参照エラーが結果として返って来ます。
よって、9月にCさんが退社したとしてもCさんの行はそのまま残した状態が得策です。
実際に練習がてら別シートにデータを作ってどういう結果が返ってくるか試してみるといいです。
「=Sheet1!B4+'Sheet1 (2)'!B4+'Sheet1 (3)'!B4・・・・・・」と普通に足していく計算式でCさんの行を削除すると返り値は「#VALUE!」となります。
「=SUM('4月:3月'!A6)」の計算結果は、Cさんの行を削除した際に結果が狂います。
「統合」と言う手段もありますが、このコマンドを使ったとしてもCさんの行を削除すると「#REF!」となり、これも参照エラーの結果が返って来ます。
「今年度」のCさんの行の9月のセルには、コメントを使って「9月退社」とでも入力しておくと分かりやすいです。(コメントを入れたいセルを右クリックし、「コメントの挿入」を選択して入力します)
エクセルのセル参照式で列や行を削除した場合・・・↓
http://officetanaka.net/excel/function/error/07. …
No.1
- 回答日時:
>「='4月'!B6+'5月'!B6+'6月'!B6+'7月'!B6+'8月'!B6+'9月'!B6+'10月'!B6+'11月'!B6+'12月'!B6+'1月'!B6+'2月'!B6+'3月'!B6」
この長~い式をもっと簡単に素早く処理したいという事ですね?
3月~来年4月までの表とセルの位置関係が全て同じ条件で作られているのでしたら次の式で素早く解決できます。
計算式の作り方手順
① 今年度シートのセル「B6」をクリックしておき、数式バーの窓に「=s」と入力(半角入力)すると「s」から始まる関数が出ますので、「SUM」を探し、見つけたらダブルクリックします。
② 次に「Ctrl」+「A」を押すとSUM関数のダイアログボックスが現れると同時に「数値1」の窓内でカーソルが点滅していることを確認。
③ シート見出し「4月」をクリックしてセル「B6」をクリックします。
④ 「Shift」キーを押したままシート見出し「3月」をクリックしたら「OK」を押します。
「=SUM('4月:3月'!B6)」←こういう式が出来ますので、後は合計を出したいセル位置まで
オートフィル機能を使って数式を右へコピー、下へコピーして出来上がりです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 週毎の集計 2 2023/08/04 16:56
- 会社・職場 遅延証明書出しても遅刻?! うちの会社は月1回の欠勤=遅刻or早退2回 という規則があり、カウントが 3 2022/08/03 23:18
- Excel(エクセル) Excelについて A1からA12まで、1月〜12月と入力し、 B1からB12の範囲に、C1とD1に 4 2022/05/26 22:48
- Excel(エクセル) TEXT関数(負の値)を集計のため数値に変換したい 5 2022/05/15 23:04
- Excel(エクセル) エクセルの祝日に色が反映しない 4 2022/05/18 09:58
- 食生活・栄養管理 ビタミンB6(サプリメント)について 体調が優れないので一ヶ月ほどサプリメントを飲んでみようかと思う 2 2022/04/24 16:59
- その他(Microsoft Office) ある表(10桝程度)の中に数字が入っています。ダブっている数字を除く数字の合計数の計算方法 5 2023/02/15 11:33
- Excel(エクセル) エクセルで日付が入っているセルを一定の法則に従って違うセルに表示したい 2 2022/04/04 17:16
- その他(お金・保険・資産運用) 遅延損害金の利息と計算方法 2 2022/07/28 20:19
- Visual Basic(VBA) エクセル VBA 難しいです 1 2023/02/21 15:39
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
10月をもって辞めるって、10...
-
確認ですが普通5月までに決める...
-
自治会の通常総会の年度について
-
総会の年度表記について
-
カレンダーの日付 5/Bの意味に...
-
「6月まで」というのは6月以内...
-
8月までっていつまでのことでし...
-
月の最後の週の呼び名は?
-
ゴールデンウィークが大嫌いな...
-
「おじろく」は長男が死んだら...
-
満何歳の意味
-
エクセルのフィルターを複数シ...
-
○月第○週 の数え方について
-
昔の愛称?「~の字」
-
今は何年度ですか?
-
入社が2月1日とした場合、3ヶ月...
-
一ヶ月前、一ヶ月後
-
○年後の3月末日を関数で出したい
-
『無線LANが切れる時、何かイベ...
-
きょうだいは誕生月が近いの法...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
自治会の通常総会の年度について
-
総会の年度表記について
-
満何歳の意味
-
ひな祭りに外食するならどこで...
-
月の最後の週の呼び名は?
-
確認ですが普通5月までに決める...
-
10月をもって辞めるって、10...
-
カレンダーの日付 5/Bの意味に...
-
「6月まで」というのは6月以内...
-
学校の在籍機関
-
8月までっていつまでのことでし...
-
エクセルのフィルターを複数シ...
-
入社が2月1日とした場合、3ヶ月...
-
昔の愛称?「~の字」
-
「1年以上」の定義について
-
一ヶ月前、一ヶ月後
-
卒業式に着る袴をお店でレンタ...
-
○年後の3月末日を関数で出したい
-
「陽気」という言葉の使い方
-
VBA ユーザーフォームのChange...
おすすめ情報
No,1にご回答頂いた方ありがとうございます。
私の説明が間違っていたので補足します。
4月~3月までの集計をしたいというはあっているんですが、
全部が同じセルにあるかというと違うんです‼‼
出勤簿なので、9月にCさんが辞めた場合10月からCさんの行は
なくなりますのでDさんが1つ上がります。
なので、「Cさんの1年間(在籍中)の電車遅延での遅刻が何回か。」
「Dさんの1年間の電車遅延での遅刻が何回か。」
これを関数で抽出することはできないのでしょうか。
ご回答頂いてありがとうございます。
エクセルの基本は知っています。
自分がわからない応用を知っている人がいれば、
と思って書き込みをしています。
出勤簿なのに、退社した人のセルをずっと作っておく必要はないと思いますので
ただ足し算をするのではなく、抽出をしたのち、足し算をしたのです。
退社した人のセルをそのまま作っておくのであれば、
自分で数式は組めますので、それとは違う方法があれば教えて頂きたいんです。
最初の質問では説明不足ということで、
後日、補足させて頂いていると思います。
そちらをお読み頂き、わからないのであれば
大丈夫です。
他にわかる方がいらっしゃれば、教えて下さい。