EXCEL:複数シートから特定行を一覧化したい

質問

【　EXCELで悩んでいます　】
複数のシートから特定の言葉の記入がある行だけ、
新規ワークシートへ自動で書き出したいのですが難儀しています。

当方エクセル初心者でして、
ピボットテーブルもマクロも組んだことがありません。
工程数がかかってもいいので、できるだけ優しい方法を教えて下さい。

仕事で使うデータで、これがあると非常に助かります。

【　準備しているデータ　】
・参照したいデータシートは8枚
・それぞれ、同じフォーマットで内容が異なる60行弱の文字データ
（画像参照ください。このようなデータシートが8枚あります）

【　やりたいこと　】
8枚にも渡るシートの内容のなかから、
特定の文字が入っているもの（画像でいうと、G列「進捗」の「作業中」になっている行）だけを自動的に抽出して
一枚の新規シートにまとめて一覧化したい

【　環境　】
EXCEL　2003
Windows

面倒かとは思いますが、
お優しいエクセルマスター様がいらっしゃると信じております。
どうかよろしくお願い申し上げます。

回答No.6

MackyNo1 · ベストアンサー

>なお、MackyNo1様がお考えの記録マクロの利用する方法もよろしければお知らせいただきたいのですがよろしいでしょうか？

以下のようなキー操作でマクロを記録してください。

まず、準備として、1行目に項目名を記載した集計シートを作成しておきます。

「ツール」「マクロ」「新しいマクロの記録」から、以下の操作をします。

集計シートのA2セルからデータ挿入範囲を大きめに選択し、Deleteキーで以前のデータを削除します（最初は何も入力されていませんが）。
次に1枚目のシートの項目行を選択し、データの入力されているA2セルからデータ範囲を大きめに選択し（予定最大データ数分まで選択します）、「データ」「フィルタ」「オートフィルタ」で必要なデータを表抽出し、Ctrl+Cでコピーして、集計シートのA2セルでCtrl+Vで貼り付けます。
2枚目のシートからは以下の操作を行います。
同様に、2枚目のシートのA2セルから、データ範囲を大きめに選択してからフィルタで抽出したデータをコピーし、集計シートのA1セルを選択して、Ctrl+↓のショートカット操作を行い、マクロの記録ツールバーの「記録終了」右にある「相対参照」のアイコンをクリックし、↓キーを押してから、相対参照ボタンをもう一度押して捜査隊参照を解除してCtrl+Vで貼り付けます。
この操作を８枚目のシートまで行い、最後にすべてのシートのフィルタモードを、「データ」「フィルタ」「オートフィルタ」で解除すれば完成です。

最終セルの選択にCtrl+↓のショートカット操作を利用すること、相対参照の記録ボタンをA列の一番下のデータの1つ下のセルを選択すること、各シートのデータ範囲を最初に選択しておいてからフィルタ操作するところが記録マクロ操作のポイントです。

お礼

私にもできました！！
PhotoShopでいうところの「アクションの記録」のような機能なんですね。
本当に助かりました。
これで仕事がはかどりそうです。

お答えいただいた方全員をベストアンサーとさせていただきたいところですが
二度もご丁寧にお教えいただいたこと、
ならびに初心者の私でも実現できたことを踏まえ、
こちらをベストアンサーとさせていただきます。

本当にありがとうございました。

回答No.5

MackyNo1

＞ピボットテーブルもマクロも組んだことがありません。

今回のように、シートが分離されているようなケースで簡便な操作を行うにはマクロを利用する必要があります。
エクセルには「マクロの記録」という機能が用意されていますので、これを利用してご希望の操作を行うことも可能ですが、エクセルの基本的操作を熟知している必要があります。
また、マクロを利用する場合はご自分でコードの修正ができる程度の最低限の知識は必要になります（例えばクロで実行したものは通常の入力と違い「元に戻す」という操作ができないので、重大なミスにつながる可能性があります）。

また、ピボットテーブルの機能はエクセルの基本機能の一つですが、この程度はご自分でできるレベルになるようにされたほうが、今後のためにも良いと思います。

＞工程数がかかってもいいので、できるだけ優しい方法を教えて下さい。

個人的には記録マクロを利用した方法が最も簡便と思われますが（記録マクロに挑戦する気持ちがあるのなら具体的な操作方法を提示します）、初心者でもわかりやすいという方法なら、リンク貼り付けとオートフィルタの機能を併用することをお勧めします。

作業用のシートを追加し、１行目に項目名を入力し、1枚目のシートのデータ範囲＋α（60行目まで）をコピーし、作業用シートのA2セルで右クリック「形式を選択して貼り付け」で「リンク貼り付け」します。
同様に2枚目のシートのデータ範囲＋αを選択して、作業用シートのA61セルにリンク貼り付けします。
すべてのシートのデータ範囲を1枚のシートにリンク貼り付けしたら、準備完了です。

必要なデータを抽出したい場合は「データ」「フィルタ」で抽出対象を絞り込みます。
この絞り込んだデータシートを新規シートにコピー貼り付けすれば完成です。

＃Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。

お礼

恥ずかしながら「リンク貼り付け」というコピペ方法を知りませんでした！
これなら、元データを修正してもちゃんと更新されますし、
なにより簡単でしたので、私でもできました。

本当にありがとうございました。

また、バージョンについても、ご指摘いただきましてありがとうございました。
しかもEXCEL2003と書いてしまっていましたが
実際は2000でして（大汗）。

Excel 2000 9.0.6926 SP-3
でした。

大変失礼致しました。

なお、MackyNo1様がお考えの
記録マクロの利用する方法も
よろしければお知らせいただきたいのですがよろしいでしょうか？

何卒よろしくお願い申し上げます。

回答No.4

noname#204879

「マクロも組んだことが」ない私や関数が苦手な人向けの方法を紹介します。
少々工数がかかるけど「優しい方法」だと思います。

少々見づらい添付図を判読しながら下記の手順をお読みください。
１．８枚（簡単のために、此処では Sheet1～Sheet3 の３枚）のシート（添付図では内容を簡略化）のデータ部分をそっくり別シート Sheet9 にコピーして、最上行に[オートフィルタ]を設定。その結果を Fig-1 に示す。
２．[進捗]の列において、[オートフィルタ]で“作業中”“と等しくない”の条件でデータを抽出した後、抽出されたレコードの全ての行全体を選択。その結果を Fig-2 に示す。
３．その選択範囲内でマウスの右クリックから[行の削除]を選択する。
４．[オートフィルタ]を解除する。その結果を Fig-3 に示す。

なお、添付図の中でどれかの拡大図を欲しければその旨をお知らせください。

お礼

ご丁寧に画像まで作成いただきまして、
本当に本当にありがとうございます！
ただ、この方法ですと、sheetの元データの内容が修正された際には
反映されないような気がしました。
お手間をいただいたのに、恐縮です。
ありがとうございました。

回答No.3

tom04

こんにちは！
一番簡単で操作しやすいのは、No.1さんが仰っているように各Sheetでオートフィルタをかけ、
表示されているものを新規Sheetにコピー＆ペーストしていく方法だと思います。

関数でやろうと思えば可能だとは思いますが、下準備とかなり長い数式が必要かと思います。

そこでお望みの方法ではないと思いますがVBAの一例を載せておきます。
騙されたと思って参考程度で試してみてください。

※　新規SheetはSheet見出しにある最後（9番目）のSheetとして、Sheet1～Sheet8で
G列が「作業中」だけのものをSheet9に表示するようにしてみました。

尚、すべてのSheetのフォーマットは同一としてタイトル行が2行まで、データは3行目からとします。
Sheet9のH列セルの表示形式は「日付」にしておきます。

Alt＋F11キー　→　VBE画面が出ますので、画面左下にある「This Workbook」をダブルクリック！　→
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です。）

Sub 一覧表示() 'この行から
Dim i, j, k As Long
i = Worksheets(9).Cells(Rows.Count, 1).End(xlUp).Row
If i > 2 Then
Range(Worksheets(9).Cells(3, 1), Worksheets(9).Cells(i, 8)).ClearContents
End If
Application.ScreenUpdating = False
For k = 1 To 8 'Sheet1～Sheet8まで
For i = 3 To Worksheets(k).Cells(Rows.Count, 1).End(xlUp).Row '3行目～最終行まで
For j = 1 To 8 'A列～H列まで
If Worksheets(k).Cells(i, 7) = "作業中" Then
Worksheets(9).Cells(Rows.Count, j).End(xlUp).Offset(1) = _
Worksheets(k).Cells(i, j)
End If
Next j
Next i
Next k
Application.ScreenUpdating = True
End Sub 'この行まで

※　Sheet1～Sheet8のデータ変更があった場合、最終作業としてマクロを実行します。

以上、参考になれば良いのですが・・・m(__)m

お礼

ご丁寧に、マクロ内容の詳細まで記入いただきまして
誠にありがとうございました。

がんばってマクロに挑戦してみましたが、
撃沈しました。
エラーが出たり、そもそもまったく反応しなかったりで・・・。
素人のため、どこが悪いのかも分からずただ時間だけが過ぎていく感じでしたのであきらめました。
ご丁寧にご回答いただいたにもかかわらず
大変申し訳ありませんでした。

回答No.2

KURUMITO

ちょっと初めの作業は面倒ですが次のようにしてはどうでしょう。
8枚のシートではないですが仮に3枚のシートの場合について取り上げます。
それぞれのシート名がこだわりませんがここではシート1からシート3までのデータがあり別のシートにお求めの表を作成するための方法を示します。
シート1のJ列は作業列としてJ3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(G3="","",G3&COUNTIF(G$3:G3,G3))

シート2のJ列は作業列としてJ3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(G3="","",G3&COUNTIF(G$3:G3,G3)+COUNTIF(Sheet1!G:G,G3))

シート3のJ列には次の式を入力して下方にオートフィルドラッグします。

=IF(G3="","",G3&COUNTIF(G$3:G3,G3)+COUNTIF(Sheet1!G:G,G3)+COUNTIF(Sheet2!G:G,G3))

そこでまとめのシートですがA1セルには抽出ワードとかの文字列を入力しA2せるには仮に作業中を表示させたい場合には作業中と入力します。
3行目、4行目はシート1などと同じに項目名を並べます。
A5セルには次の式を入力してH5セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF(Sheet1!$J:$J,$A$2&ROW(A1)),INDEX(Sheet1!$A:$H,MATCH($A$2&ROW(A1),Sheet1!$J:$J,0),COLUMN(A1)),"")&IF(COUNTIF(Sheet2!$J:$J,$A$2&ROW(A1)),INDEX(Sheet2!$A:$H,MATCH($A$2&ROW(A1),Sheet2!$J:$J,0),COLUMN(A1)),"")&IF(COUNTIF(Sheet3!$J:$J,$A$2&ROW(A1)),INDEX(Sheet3!$A:$H,MATCH($A$2&ROW(A1),Sheet3!$J:$J,0),COLUMN(A1)),"")

これでお望みの表を得ることができます。
シートの数が8ですから式は長くなってきますがここで示した例を参考にして挑戦してみてください。

回答No.1

aokii

ツールバーの、データのフィルタのオートフィルターの（オプション）で、進捗と作業中をORで抽出し、それをコピーして新規シートに貼り付けて、8枚のデータシートをまとめてはいかがでしょう。

お礼

確かに…一番簡単ですね。
やっぱりこれがいいのでしょうかねえ・・・。