![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
給料の明細を個人ごとの名前のついた同じひな型のシートに記入しており、総支給額がA1に出ます。
集計シートを作り、各シートの総支給額を行に並べた支給額の一覧表を作りたいと思っています。
串刺し集計の合計額ではなく、下の例のような形です。
(例)
氏名 支給額
あ 300,000
い 350,000
う 400,000
単純に、集計シートに「あ」さんの支給額を持って来るには、 =あ!A1
とすればよいのですが、数十人分これを行うのは骨が折れます。
何かよい方法があれば、皆さんの知恵を拝借したいと思っています。
自分の検索能力では串刺し集計のやり方しか見つけられませんでした・・・
そもそも表の構成を変えた方がやりやすいのでしょうかね?
A 回答 (7件)
- 最新から表示
- 回答順に表示
No.7
- 回答日時:
>そもそも表の構成を変えた方がやりやすいのでしょうかね?
方法は人それぞれですが。私が同じようなものを作るとしたら次のようにしますね。文字ばかりの説明ですみませんが、よかったらご参考になさってください。
「氏名 あ、い、う、…」の載っている表(表1)でデータ入力・計算をし、表1のシートまたは別の印刷データ入力用シート(表1から特定の月などのデータをコピペするシート)を参照元として、給料明細(表2)のシートに各種データを表示させるように、私ならするでしょうね。つまり質問者さんとは、参照の方向が逆です。
表1は小規模なデータベースとします。支給額のほかにも、いろいろなデータの列が用意されています。多数の計算式も含みます。セルの結合は用いません。同じ氏名が複数の行(レコード)に存在しているような表にします。日付とか、どこかの列の値は互いに異なっていますが、名前が同じレコードが複数あっても構いません。データが存在しないセル以外は、必ず何かしらの値を入力します。例えば氏名が同じなのであれば、他のレコードから氏名をコピペしておき、空欄にはしません。
印刷データ入力用シートを用意する場合、表1にオートフィルタを取り付けておいて、ある月のみなどに絞り込んだ状態の一覧を印刷データ入力用シートに毎月コピペ・上書きするなどします。
表2は印刷用のシートにするので、そのシート内で計算などは極力しないようにします。シート内の特定のセルのみ、日付とか氏名とかを入力できるようにしておきます。リストの機能を併用し、リストにないデータはそのセルにも入力できないようにしてもいいです。入力すると、総支給額その他のデータを表1のシートまたは印刷データ入力用シートからVLOOKUP関数(上の日付や氏名のセルを引数にしたもの)などにより拾ってきて、自動的に表示されるようにしておきます。給料明細の様式は決まっていて毎月同じなので、シートを保護し、前述の特定のセル以外は、編集できないようにしておきます。
つまり、用意するシート枚数は、表1が1つ、印刷データ入力用シートを設ける場合はそれが1つ、表2も社員の人数分ではなくやはり1つのみとなります。
No.6
- 回答日時:
'全部同じブック、だよ~ん
'一覧用のシート1枚ご用意ください
'結果はそこに出るよ~(創造的破壊!)
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
- 回答日時:
=INDIRECT(A2&"!A1")
だけですと、A列のセルが空欄となっていたり、A列に入力されている名前と同じシート名を持つ「個人ごとの名前のついた同じひな型のシート」が存在していなかったりする場合にはエラーが表示されてしまい、見苦しくなります。
又、一般的にはExcelの関数では、給料支給対象者の人事が変更されて、集計シートの「氏名」欄を変更しなければならなくなった際に、
例えば抜けが出来た部分のセルを削除して、その下にあるセルを上にシフトする事で、リスト中の空欄を詰めたり、
セルの切り取りと貼り付けを使用して、氏名の並びを変更したり、
給料支給対象者が増えた際に、「氏名」欄に対してセルの挿入を行ったりしますと、
セルの位置関係がずれるため、関数が正常に動作しなくなります。
ANo.1の関数は、上記の短所に対する対策を施しているために、多少、複雑な関数となっております。
No.4
- 回答日時:
おっしゃる構成
> 個人ごとの名前のついた
> 同じひな型のシート
> 総支給額がA1
の三点を私なりに都合良く解釈して。
例えば、
集計シートのA2セルに氏名(一字一句、シート名と合致)を入力しておき、
B2セルに「=INDIRECT(A2&"!A1")」としてやる。
これだけで集計シートのA列に入力された「氏名」と同じ名前のシートのA1セルを見に行きます。
余程の大人数でなく、入れ替わりも頻繁ではないのであれば、素直に見に行く方が早いです。
INDIRECT関数については、別途お調べくださいませ。
> そもそも表の構成を変えた方がやりやすいのでしょうかね?
そうとも言えますし、そうではないとも言えます。
作りやすい・使いやすい・見やすいの判断は人それぞれですから、
ご自身なりに工夫が必要だとは思います。
No.2
- 回答日時:
後、御質問の内容とは関係御座いませんが、「個人ごとの名前のついた同じひな型のシート」には、各個人名を入力する欄もあるかと思いますが、各シートを一々開いて氏名を入力して行くのも大変かと思います。
その様な場合には、シート名を個人名そのものとしておいた上で、関数を使ってシート名を表示させるという方法を使う事も出来ます。
今仮に、各「個人ごとの名前のついた同じひな型のシート」のB1セルに個人名を入力しなければならないものとした場合、まず、雛型となるシートのB1セルに、次の関数を入力して下さい。
=REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1)),)
次に、雛型となるシートのコピーシートを、給料支給対象者の人数分だけ複製して下さい。
そして、それらのコピーシートのシート名を各給料支給対象者の氏名と同一の名称に変更して下さい。
これで、B1セルに各給料支給対象者の氏名(シート名)が自動的に表示されます。
但し、同姓同名の人物が複数いる場合は、話がややこしくなりますが…
No.1
- 回答日時:
集計シートのA列に氏名が並んでいて、「個人ごとの名前のついた同じひな型のシート」のシート名が、集計シートのA列に並べられている氏名と、全く同一の名称である場合には、次の様にされると良いと思います。
まず、集計シートのB2セル(「支給額」と入力されているセルのすぐ下のセル)に次の関数を入力して下さい。
=IF(ISNUMBER(INDIRECT("'"&INDEX($A:$A,ROW())&"'!A1")),INDIRECT("'"&INDEX($A:$A,ROW())&"'!A1"),"")
そして、集計シートのB2セル(「支給額」と入力されているセルのすぐ下のセル)をコピーして、集計シートのB3(「支給額」と入力されているセルの2行下のセル)以下に(給料支給対象者の人数を上回るのに十分な行数となるまで)貼り付けて下さい。
以上です。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel、同じフォルダ内のExcelファイルの特定シートのみを1つのファイルに集約したい 8 2022/09/07 15:12
- Excel(エクセル) 別シートに毎回異なるデータをコピーする 7 2022/06/24 09:02
- Excel(エクセル) シート参照を含む数式を連続コピー 3 2022/12/10 11:42
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) 【マクロ】同じフォルダ内にある複数ブックから1つのブック内の1シートにデータを集めたい 6 2022/09/28 18:16
- Excel(エクセル) Excelの複数ファイルの複数行を別ファイル1つのシートにVBA、マクロで集約する方法 5 2022/09/13 06:30
- Excel(エクセル) 【VBA】元のシート内の文字列を別シートと比較し、一致したら元のシートの別のセルへ転記する方法。 3 2023/03/23 17:30
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) EXCEL マクロで 同じフォルダ内の複数ファイルの複数行全体を選択して1つのファイルに集約 4 2022/09/27 18:41
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの「0」だけ非表示、小数...
-
日付が未入力の際はゼロか、空...
-
シート参照で変数を使いたい(EX...
-
エクセルで1月0日と表示される!!
-
エクセルで条件に一致したセル...
-
(Excel)あるセルに文字を入力...
-
エクセルで別シートからの最大...
-
別シートのセルを絶対参照にする
-
複数シートの同じセル内容を1シ...
-
Excelシートの保護時にデータの...
-
エクセルで複写のように自動入...
-
ExcelでTODAY関数を更新させな...
-
エクセルで、勤務表から 日付...
-
excelでハイパーリンクになって...
-
ワードからエクセルのシートに...
-
EXCEL関数でシート名が変わる可...
-
Excelで複数シートの選択セルを...
-
Excelでスクロールすると文字が...
-
エクセルで、加筆修正したセル...
-
Rangeメソッドは失敗しました。...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの「0」だけ非表示、小数...
-
日付が未入力の際はゼロか、空...
-
エクセルで1月0日と表示される!!
-
エクセルで条件に一致したセル...
-
Rangeメソッドは失敗しました。...
-
(Excel)あるセルに文字を入力...
-
Excelで複数シートの選択セルを...
-
エクセルで複写のように自動入...
-
Excelシートの保護時にデータの...
-
シート参照で変数を使いたい(EX...
-
別シートのセルを絶対参照にする
-
複数シートの同じセル内容を1シ...
-
Excelでスクロールすると文字が...
-
Aというブックの1というシート...
-
エクセルで20万行あるシート...
-
エクセルの文字
-
エクセルで、加筆修正したセル...
-
VBAで変数に関数式の結果をセッ...
-
ExcelでTODAY関数を更新させな...
-
VBAで、セル(Range)のオブジ...
おすすめ情報