エクセルで同じ様式の多数のシートを一つのシートにま

質問

どなたか教えていただけるとありがたいです。エクセルで全く同じ様式のシートが数十枚一つのブックにまとまっています。各シートにはシートの枚数分の社員の名前が縦列にあり、個々の社員の歩数とコメントが各社員の行に、シートごとに入力してあります。歩数については串刺しで頭のシートにまとめることができるのですが、文字列の部分を頭のシートにまとめることができません。CONCATENATEや、他の答えで探したINDIRECTも試しましたがうまくいきません。コピペすれば良いことですが、定期的に作業が必要なので簡素化できればと思います。よろしくお願いします。

回答No.7

Chiquilin · ベストアンサー

マクロでやる場合 社員シートのコメント範囲をコピーして
まとめシートの該当セルに 空白を無視して貼り付けすれば
いいんだから

Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "まとめ" Then
ws.Range("D2:D117").Copy
Worksheets("まとめ").Range("D2").PasteSpecial _
SkipBlanks:=True
End If
Next ws
Application.CutCopyMode = False

大体こんな感じでいいはずです。というか最初からこの形でシート
を分けずにまとめておけばよかったのでは？

お礼

空白を無視して貼り付けることができるのは驚きでした。この方法で無事解決しました。素人のことで歯がゆい思いをされているかと思いますが、サイトの趣旨に鑑みてご容赦いただきたいと思います。ありがとうございました。

回答No.11

chayamati

回答№10です
回答№10はSheet1枚での処理で1年分で処理で、締めは年報になります
月のサイクルで、月報とするには
各月のSheet12枚と社員名入力に入力規則を適用するための名簿Sheetの都合13枚の構成は如何ですか
構築に当り、日付入力と社員名入力に【入力規則】ツールを24回使用しますので、このツールをクイックアクセスツールバーに追加
【データリボン】→【入力規則】→【入力規則右クリック】
→【クイックアクセスツールバーに追加】
添付図左は名簿Sheet、右は7月分のSheetです
　・セルB3でウィンドウ枠の固定
　【セルB3】→【表示リボン】→【ウィンドウ枠を固定する】
！！右の図で3行目から19行目に飛んでいるのを確認ください
　　3行目迄は常に表示されていますが、残りは
　　スクロールアップして、空白行を表示し、新規情報を入力　　
　・A列（日付）に入力規制で最小値=開始日、最大日=終了日
　【列名A】→【データの入力規則】→【（入力の種類）日付】
　→【（開始日）セルF2】→【（終了日）セルG2】
　　※F4押下で絶対アドレス$F$2,G$2にします
　・B列（社員名）に入力規制で名簿を適用
　　【列名B】 →【データの入力規則】→【（入力の種類）リスト】
　→【（元の値）名簿Sheet】→【A2】→【:】→【G2】
　　※F4押下で絶対アドレス$A$2効行より数行余分の絶対アドレス
　　　に変更

お礼

ご丁寧な回答をありがとうございます。じっくり確認させていただきます。

回答No.10

chayamati

回答№9です。もしかして
社員様はスマホの歩行アプリの履歴をメール送信されるでは
だったら、1日1回となりますが、項目も日付、氏名、歩数、コメントに限られますね
Excelには分類、グループ化、集計機能がありますので
1枚のシート(添付図）に受信の都度登録します
以下は入力ミスを回避するためのものです
①開始日と終了日：日付の入力範囲
②日付：データの入力規則定義で①を適用
③名簿：社員様の一覧
④社員名：データの入力規則定義で③を摘要
⑤この列の表示形式をユーザ定義で　#,###歩とする
⑥セルB3：レコード数が増えるとスクロールアップして
　1行目が隠れてしまうのを防ぐ為「ウィンドウ枠を固定
・何故かEnterキーを押したらセルの移動方向が下が規定値と
　なっていますがこれを右に変更
・テーブルとして書式設定されているとテーブルの右端に
　来ると次の行の左端に移動する
・Excelファイルを新規作成するとSheet３つ規定値だが
　１つに変更
以上です。回答№9は無視して下さい

回答No.9

chayamati

補足有難うございます
★【数十枚のシート】のSheet名の補足下さい
→シート名はデフォルトの「Sheets1」を全員に送信しているので、
ほとんどそのまま返ってくると思いますので同じシート名です。
　【同じ様式】
　　例えばA列：連番　B列：社員名、C列：歩数、D列：コメントとなっています。
★ 連番は送信者任せと思いますが、日付、時間帯（朝、昼、夕）に置き換わりませんか
＞各シートにはシートの枚数分の社員の名前が縦列にあり、
＞個々の社員の歩数とコメントが各社員の行に、シートごとに入力してあります。
★【数十枚のシートに入力するのですか
　→各社員が入力したシートを一つのブックにまとめています。
＞定期的に作業が必要なので簡素化できればと思います。
　現在どのような作業をされていますか、具体的に補足願います
　→社員から自分の歩数と、自由なコメントをシートに入力させ、
　　そのシートをメールで回収して一つのブックにまとめ、
　　歩数は串刺しで、コメントは一つ一つコピペで先頭のシートにまとめています。
　これでご理解いただけるでしょうか。
--------------------------------------------------------------
★以下の通り提案します

1.各社員が入力する様式を統一する添付図
　・（A1;E110）をテーブルとして設定
　　※　1社員の１か月分の行数より余分にE110を変更する
　　！　テーブルは各項目にソート機能とフィルタ機能があります
　①G2；社員名　A列の日付が有効値のときE列の社員名を表示　E2=IF([@日付]="","",$G$2)
　②H2；基準日を含む月初日と月末日を求めA列の日付に入力規則を適用する
　⑥B列：入力規則で朝、昼.夕の3択を適用
　・総合Sheet
　　添付図のA列～E列のみ
　　全社員の１年分の行数を余分に定義
　（＝社員の人数×社員の行数×12）
　　行数が足りないときは、テーブル内で追加が可能
2.各社員からのメール受信は月1回の月報のみとする
3.社員から受信したSheetをマクロで総合Sheetに纏めるために
　マクロツールをクイックアクセスツールバーに追加する
　【開発リボン】→【マクロの記録を右クリック】
→【クイックアクセスツールバーに追加】
　→【マクロの表示を右クリック】→【クイックアクセスツールバーに追加】
4.次のマクロを総合Sheetに設定する
　【マクロの表示】→【マクロ名に「値貼付」と入力】→【編集】
→【次のコードを貼り付ける】
--------------------------------
Sub 値貼付()
Range("A2:E100").Select
Selection.Copy
Sheets("総合").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
----------------------------------
5.社員毎のSheetを総合Sheetに値貼付する
　【総合Sheetを選択】→【追加する行をA列をクリック】
　→【追加する社員のSheetを選択】
　→【クイックアクセスツールバー上の「マクロの表示」】→【実行】
----------------------------------------------------------------
以上ですが、説明不足が多々あると思います。
ご遠慮なく補足質問を

回答No.8

iyonohanamuko

たしか、シート名に半角スペースや記号が入っているとINDIRECT関数では拾えないのではなかったでしたっけ？

お礼

やはりそういうことなのですね。
他の方のマクロで無事解決しました。情報に感謝しています。ありがとうございました。

回答No.6

Chiquilin

小さなサンプルでシート名や番地は具体的に。
と書いたんですが「（シート名は）バラバラです」というよく分か
らない補足が返ってきてしまいました。

ひとまずマクロが入っているなら必要情報もマクロで集めればいい
と思います。入力のあるデータをまとめシートにコピーするだけで
いいので初心者レベルの知識でも作れるでしょう。

因みに私だったら最初の最初からクエリでやると思います。

数式で無理してやるのはお勧めはしませんが シート名リストを作っ
てから INDIRECT＆配列計算でやれないことはないでしょう。

回答No.5

chayamati

★以下について補足願います。
＞エクセルで全く同じ様式のシートが
　数十枚一つのブックにまとまっています。
★【数十枚のシート】のSheet名の補足下さい
　例えば社員名
　【同じ様式】の具体的に補足下さい
　　例えばA列：社員名、B列：歩数、C列：コメント
＞各シートにはシートの枚数分の社員の名前が縦列にあり、
★　社員が増えれば新規のSheetを１枚追加するのですか
＞個々の社員の歩数とコメントが各社員の行に、
　シートごとに入力してあります。
★【数十枚のシートに入力するのですか
＞歩数については串刺しで頭のシートにまとめることが
　できるのですが、
＞文字列の部分を頭のシートにまとめることができません。
　【文字列】とは社員名の事ですか
＞定期的に作業が必要なので簡素化できればと思います。
　現在どのような作業をされていますか、具体的に補足願います

補足

ありがとうございます。遅くなり申し訳ございません。
ご質問について補足させていただきます。
＞エクセルで全く同じ様式のシートが
　数十枚一つのブックにまとまっています。
★【数十枚のシート】のSheet名の補足下さい
　→シート名はデフォルトの「Sheets1」を全員に送信しているので、ほとんどそのまま返ってくると思いますので同じシート名です。それをVBAで一つのブックにまとめていますので、Sheets1(1)などとなっていますが、バラバラです。
　【同じ様式】の具体的に補足下さい
　　例えばA列：連番　B列：社員名、C列：歩数、D列：コメントとなっています。

＞各シートにはシートの枚数分の社員の名前が縦列にあり、
★　社員が増えれば新規のSheetを１枚追加するのですか
　→おっしゃる通りです。

＞個々の社員の歩数とコメントが各社員の行に、
　シートごとに入力してあります。
★【数十枚のシートに入力するのですか
　→各社員が入力したシートを一つのブックにまとめています。

＞歩数については串刺しで頭のシートにまとめることが
　できるのですが、
＞文字列の部分を頭のシートにまとめることができません。
　【文字列】とは社員名の事ですか
　→文字列は各社員が自由に記載したコメントです。

＞定期的に作業が必要なので簡素化できればと思います。
　現在どのような作業をされていますか、具体的に補足願います
　→社員から自分の歩数と、自由なコメントをシートに入力させ、そのシートをメールで回収して一つのブックにまとめ、歩数は串刺しで、コメントは一つ一つコピペで先頭のシートにまとめています。

　これでご理解いただけるでしょうか。

回答No.4

Chiquilin

追加で。「コメント」をどのようにまとめたいのか説明してください。
「文字列の部分」が「コメント」なのかどうかもよく分かりませんが……

Excelのバージョンは記載がなければ最新とみなします。

お礼

ご質問についてですが、「補足する」ボタン見当たらなくなったので「お礼する」で回答します。
文字列の部分が「コメント」であり、各自が入力したコメントをまとめシートの各自の欄にそっくりそのまま表示させたいのです。
エクセルのバージョンは2019です。
よろしくお願いします。

補足

ありがとうございます。遅くなり申し訳ございません。
ご質問について補足させていただきます。
＞エクセルで全く同じ様式のシートが
　数十枚一つのブックにまとまっています。
★【数十枚のシート】のSheet名の補足下さい
　→シート名はデフォルトの「Sheets1」を全員に送信しているので、ほとんどそのまま返ってくると思いますので同じシート名です。それをVBAで一つのブックにまとめていますので、Sheets1(1)などとなっていますが、バラバラです。
　【同じ様式】の具体的に補足下さい
　　例えばA列：連番　B列：社員名、C列：歩数、D列：コメントとなっています。

＞各シートにはシートの枚数分の社員の名前が縦列にあり、
★　社員が増えれば新規のSheetを１枚追加するのですか
　→おっしゃる通りです。

＞個々の社員の歩数とコメントが各社員の行に、
　シートごとに入力してあります。
★【数十枚のシートに入力するのですか
　→各社員が入力したシートを一つのブックにまとめています。

＞歩数については串刺しで頭のシートにまとめることが
　できるのですが、
＞文字列の部分を頭のシートにまとめることができません。
　【文字列】とは社員名の事ですか
　→文字列は各社員が自由に記載したコメントです。

＞定期的に作業が必要なので簡素化できればと思います。
　現在どのような作業をされていますか、具体的に補足願います
　→社員から自分の歩数と、自由なコメントをシートに入力させ、そのシートをメールで回収して一つのブックにまとめ、歩数は串刺しで、コメントは一つ一つコピペで先頭のシートにまとめています。

　これでご理解いただけるでしょうか。

回答No.3

Chiquilin

シート名とデータのセル番地を明確にしましょう。「どこに」「何
が」「どのように」がさっぱり分かりません。

実際のデータではなく小さなサンプルデータで質問すればいいので
条件や配置をあいまいにする意味がありません。

> 定期的に作業が必要なので簡素化できればと思います。
同じ形式のデータをシートで分けた時点で簡素化も何もありません。
必ず手間がかかります。

補足

申し訳ありません。
シート名は別の方の補足にお答えしたとおり、バラバラです。データのセル番地は、A列が連番、B列が社員名、C列が歩数、D列が各社員のコメントです。A2からD117まで入っています。

回答No.2

iyonohanamuko

以下ページの要領でシート名を取得してあとは列番号の番地表示で参照できるのではないかと存じます。

https://yaromai.jp/excel-func-sheetname/

お礼

すごい！。なるほどバラバラのシート名を先に取得しておいて、そこからINDIRECT関数を使うということですね。職場で早速やってみます。結果はまた書き込みます。ありがとうございます。

補足

リンク先ページの関数でほぼできたのですが、最後のINDIRECT関数で文字列を参照する際に、取得したシート名が、マクロで集めた状態の「Sheet1(?)」のままだとエラーとなるのはなぜでしょうか。シート名を手動で変えるとちゃんと反映されます。

回答No.1

w4330

>コピペすれば良いことですが
じゃぁ、コピペしましょ
例えばA1～A30まで社員の名があるなら
1）A1をマウスでクリック
2）CtrlとShiftを押しながら↓（下矢印）
3）Ctrl＋c
4）名前を入れたいシートの名前の先頭部分をクリックしてEnter
これでよいかと思います