Excel 複数シートの集計を一覧したい

質問

給料の明細を個人ごとの名前のついた同じひな型のシートに記入しており、総支給額がA1に出ます。
集計シートを作り、各シートの総支給額を行に並べた支給額の一覧表を作りたいと思っています。
串刺し集計の合計額ではなく、下の例のような形です。

（例）
氏名　支給額
あ　　　300,000
い　　　350,000
う　　　400,000

単純に、集計シートに「あ」さんの支給額を持って来るには、　=あ!A1
とすればよいのですが、数十人分これを行うのは骨が折れます。
何かよい方法があれば、皆さんの知恵を拝借したいと思っています。
自分の検索能力では串刺し集計のやり方しか見つけられませんでした・・・

そもそも表の構成を変えた方がやりやすいのでしょうかね？

回答No.7

MarcoRossiItaly

＞そもそも表の構成を変えた方がやりやすいのでしょうかね？

方法は人それぞれですが。私が同じようなものを作るとしたら次のようにしますね。文字ばかりの説明ですみませんが、よかったらご参考になさってください。

「氏名　あ、い、う、…」の載っている表（表1）でデータ入力・計算をし、表1のシートまたは別の印刷データ入力用シート（表1から特定の月などのデータをコピペするシート）を参照元として、給料明細（表2）のシートに各種データを表示させるように、私ならするでしょうね。つまり質問者さんとは、参照の方向が逆です。

表1は小規模なデータベースとします。支給額のほかにも、いろいろなデータの列が用意されています。多数の計算式も含みます。セルの結合は用いません。同じ氏名が複数の行（レコード）に存在しているような表にします。日付とか、どこかの列の値は互いに異なっていますが、名前が同じレコードが複数あっても構いません。データが存在しないセル以外は、必ず何かしらの値を入力します。例えば氏名が同じなのであれば、他のレコードから氏名をコピペしておき、空欄にはしません。

印刷データ入力用シートを用意する場合、表1にオートフィルタを取り付けておいて、ある月のみなどに絞り込んだ状態の一覧を印刷データ入力用シートに毎月コピペ・上書きするなどします。

表2は印刷用のシートにするので、そのシート内で計算などは極力しないようにします。シート内の特定のセルのみ、日付とか氏名とかを入力できるようにしておきます。リストの機能を併用し、リストにないデータはそのセルにも入力できないようにしてもいいです。入力すると、総支給額その他のデータを表1のシートまたは印刷データ入力用シートからVLOOKUP関数（上の日付や氏名のセルを引数にしたもの）などにより拾ってきて、自動的に表示されるようにしておきます。給料明細の様式は決まっていて毎月同じなので、シートを保護し、前述の特定のセル以外は、編集できないようにしておきます。

つまり、用意するシート枚数は、表1が1つ、印刷データ入力用シートを設ける場合はそれが1つ、表2も社員の人数分ではなくやはり1つのみとなります。

回答No.6

K Kazz（@JazzCorp）

'全部同じブック、だよ～ん
'一覧用のシート１枚ご用意ください
'結果はそこに出るよ～（創造的破壊！）
Option Explicit
Sub ListUpPayment()
Const xName = "秘密の社員情報"
Const xHeader = "No.,社員番号,氏名(シート名),支給額,ブック名"
Const xPos_From = "A1"
Const xPos_To = "A2"
Dim xNoData As Boolean
Dim zSheet As Worksheet
Dim xSheet As Worksheet
'Dim xSum As Double
Dim xSum As Long
Dim xNum As Long
Dim xLast As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xNoData = True
Set xSheet = ActiveSheet
xSheet.Cells.Clear
xSheet.Cells(1, Range(xPos_To).Column).Resize(1, 4).Value = Split(xHeader, ",")
xSum = 0
xNum = 0
xLast = 1
For Each zSheet In Worksheets
If zSheet.Name <> xSheet.Name Then
xNoData = False
xSheet.Cells(xLast + 1, Range(xPos_To).Column).Value = xLast
xLast = xLast + 1
xSheet.Cells(xLast, Range(xPos_To).Column + 2).Value = zSheet.Name
xSheet.Cells(xLast, Range(xPos_To).Column + 3).Value = zSheet.Range(xPos_From).Value
If (IsNumeric(zSheet.Range(xPos_From).Value)) Then
xSum = xSum + zSheet.Range(xPos_From).Value
End If
End If
Next zSheet
'オマケ
xSheet.Cells(xLast + 1, Range(xPos_To).Column + 2).Value = "支給額合計"
xSheet.Cells(xLast + 1, Range(xPos_To).Column + 3).Value = Int(xSum)
If xNoData = True Then
MsgBox ("No Data Found!!")
End If
Epilogue:
xSheet.Rows(1).EntireColumn.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

回答No.5

kagakusuki

=INDIRECT(A2&"!A1")

だけですと、A列のセルが空欄となっていたり、A列に入力されている名前と同じシート名を持つ「個人ごとの名前のついた同じひな型のシート」が存在していなかったりする場合にはエラーが表示されてしまい、見苦しくなります。
　又、一般的にはExcelの関数では、給料支給対象者の人事が変更されて、集計シートの「氏名」欄を変更しなければならなくなった際に、
例えば抜けが出来た部分のセルを削除して、その下にあるセルを上にシフトする事で、リスト中の空欄を詰めたり、
セルの切り取りと貼り付けを使用して、氏名の並びを変更したり、
給料支給対象者が増えた際に、「氏名」欄に対してセルの挿入を行ったりしますと、
セルの位置関係がずれるため、関数が正常に動作しなくなります。

　ANo.1の関数は、上記の短所に対する対策を施しているために、多少、複雑な関数となっております。

回答No.4

tsubuyuki

おっしゃる構成
> 個人ごとの名前のついた
> 同じひな型のシート
> 総支給額がA1
の三点を私なりに都合良く解釈して。

例えば、
集計シートのＡ２セルに氏名（一字一句、シート名と合致）を入力しておき、
Ｂ２セルに「=INDIRECT(A2&"!A1")」としてやる。

これだけで集計シートのＡ列に入力された「氏名」と同じ名前のシートのＡ１セルを見に行きます。
余程の大人数でなく、入れ替わりも頻繁ではないのであれば、素直に見に行く方が早いです。

ＩＮＤＩＲＥＣＴ関数については、別途お調べくださいませ。

> そもそも表の構成を変えた方がやりやすいのでしょうかね？
そうとも言えますし、そうではないとも言えます。
作りやすい・使いやすい・見やすいの判断は人それぞれですから、
ご自身なりに工夫が必要だとは思います。

回答No.3

K Kazz（@JazzCorp）

明細は同じブックにある？
一覧したいシートも同じブック？
ブックが違う場合、フォルダは１つ？？
これはマクロの出番だと思います。

回答No.2

kagakusuki

後、御質問の内容とは関係御座いませんが、「個人ごとの名前のついた同じひな型のシート」には、各個人名を入力する欄もあるかと思いますが、各シートを一々開いて氏名を入力して行くのも大変かと思います。
　その様な場合には、シート名を個人名そのものとしておいた上で、関数を使ってシート名を表示させるという方法を使う事も出来ます。

　今仮に、各「個人ごとの名前のついた同じひな型のシート」のB1セルに個人名を入力しなければならないものとした場合、まず、雛型となるシートのB1セルに、次の関数を入力して下さい。

=REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1)),)

　次に、雛型となるシートのコピーシートを、給料支給対象者の人数分だけ複製して下さい。
　そして、それらのコピーシートのシート名を各給料支給対象者の氏名と同一の名称に変更して下さい。
　これで、B1セルに各給料支給対象者の氏名（シート名）が自動的に表示されます。
　但し、同姓同名の人物が複数いる場合は、話がややこしくなりますが…

回答No.1

kagakusuki

集計シートのA列に氏名が並んでいて、「個人ごとの名前のついた同じひな型のシート」のシート名が、集計シートのA列に並べられている氏名と、全く同一の名称である場合には、次の様にされると良いと思います。

　まず、集計シートのB2セル（「支給額」と入力されているセルのすぐ下のセル）に次の関数を入力して下さい。

=IF(ISNUMBER(INDIRECT("'"&INDEX($A:$A,ROW())&"'!A1")),INDIRECT("'"&INDEX($A:$A,ROW())&"'!A1"),"")

　そして、集計シートのB2セル（「支給額」と入力されているセルのすぐ下のセル）をコピーして、集計シートのB3（「支給額」と入力されているセルの2行下のセル）以下に（給料支給対象者の人数を上回るのに十分な行数となるまで）貼り付けて下さい。

　以上です。