抽出したデータを別のシートに転記する方法

質問

親データ(700行程度)を収納したシートからある属性のデータを抽出し、別シートに転記する方法として、以下のようにしています。まず親データからオートフィルターを使用してある属性データを抽出します。表示されたデータをコピーして別のシートに貼り付け、属性ごとのデータシートを作成していますが、親データシートに変更を加えたとき、別シートにその変更内容が反映されるようにしたいのです。実は自治会の会員のデータ管理をしていますが、人の移動が頻繁なので、会費徴収などの定期的な事務作業については、それぞれのシートに変更した部分を手作業で修正入力していますが、親データとの整合性が取れずに、データの訂正もれや重複などがあり、手続きに不備がでてしまっています。
具体的な作業としては、まず親データシート(会員台帳)の基本データの最新化をします。その結果が自動的に分割した別シート1、シート2のデータに反映されることが不可欠。この分割したデータシートは案内書や請求書などワードで作成した文書の差し込み印刷データに使用していますので、それぞれ別々のブックとして属性ごと、たとえば1区、2区などの名前を付けて保存しています。解決策としてエクセルのオートフィルタや関数を使ってできればいいと思っています。マクロはまだ未経験で少しかじりかけていますが、すぐには使いこなせないので、何とか簡便なほうほうが望ましいのです。また自治会役員は年度ごとに交代するので、あまり専門的なこった内容にしたくないのです(前任者が作ったものを利用していますが、不備や差し込みデータとしての利用ができず、基本データのみを利用して再度整理しなおしているところです)。初歩的な質問で恐縮ですが、よろしくお願いします。

回答No.1

MASUKUBO · ベストアンサー

オートフィルターを利用する方法は元のデータに新たなデータが加わったり元のデータが修正されるたびにオートフィルタの操作をやり直してそれをコピーし貼り付けるという操作になりますね。オートフィルタなどの操作がわからない人にはできませんし、操作もいちいち面倒です。
関数を使って属性を抽出しそれを別のシートに自動的に表示させるのがよいでしょう。次は一例ですが参考にしてお望みのシートを作られるのがよいでしょう。ぜひお試しください。
例えば元の表がシート1に有り、A1セルからH1セルまでには項目名が有るとします。ここでは仮にA1セルに氏名、B1セルに性別、C1セルに年齢とあり、それぞれのデータが下方に入力されているとします。適当にデータを入力してください。
そこで例えば男のみのデータをシート2に20歳以上で30歳未満のデータをシート3に表示させるとします。そのためには分かり易くするためにシート1には作業列を作って対応します。例えば男のみのデータを抽出するためにJ1セルには男とでも入力し、J2セルには次の式を入力して下方にオートフィルドラッグします。

=IF($B2<>"男","",COUNTIF($B$2:$B2,"男"))

これで男の行のみに上から順に番号が表示されます。

K1セルには20歳以上30歳未満とでも入力し、K2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(C2>=20,C2<30),COUNTIF($C$2:$C2,">=20")-COUNTIF($C$2:$C2,">=30"),"")

これで該当の年齢の行に上から番号が表示されます。

このように抽出したい属性について作業列を設けて、その該当の行を上から順に表示させるようにすることで、元の表にデータが新たに追加されても、データが修正されることが有っても即座に対応したデータが表示されます。この作業列のデータを利用してシート2や3を次の様に作成します。

シート2の1行目にはシート1の1行目をコピーして貼り付けます。
シート2のA2セルには次の式を入力して例えばC2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$J:$J),"",INDEX(Sheet1!$A:$H,MATCH(ROW(A1),Sheet1!$J:$J,0),COLUMN(A1)))

同じようにシート3に1行目には項目名を並べ、A2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$K:$K),"",INDEX(Sheet1!$A:$H,MATCH(ROW(A1),Sheet1!$K:$K,0),COLUMN(A1)))

このように元のデータシートにそれぞれの属性について作業列を設け、それを利用して属性ごとのデータを別のシートに表示させるようにすることで、オートフィルタとコピー貼り付けの操作をする必要はなくなります。上記の例を充分試験されて目的が達成できますように願っています。何かわからないことがありましたら追加ご質問ください。

お礼

さっそく教えていただきありがとうございました。やってみましたら、なんと素晴らしい結果が得られました。エクセルの関数については本を読んで、その意味を理解しようとしていましたが、組み合わせて複雑な操作ができるものだなと感心しました。教えていただいた関数式の意味を一つづつ理解していきたいと思っています。エクセルの達人から見れば簡単なことかもしれませんが、私のようにオートフィルタで抽出したデータをみたり、せいぜい合計金額を求めたりする程度しかできない者にとっては、大変感すばらしいですね。作業列を作るということが理解できなかったのですが、やっみてなんとなくわかった気がします。合計10くらいのシートにデータを抽出できました。関数式のなかの(Sheet1!$J:$J)のJのところを作業列に合わせて変えていくだけでうまくいきました。差し込み印刷もこのシートを差し込みデータとして使用することで、うまくいきました。ありがとうござました。少しは進歩したと思います。

回答No.2

MackyNo1

>実は自治会の会員のデータ管理をしていますが、人の移動が頻繁なので、会費徴収などの定期的な事務作業については、それぞれのシートに変更した部分を手作業で修正入力していますが、親データとの整合性が取れずに、データの訂正もれや重複などがあり、手続きに不備がでてしまっています。

基本的に親データシートにのみ入力作業を行い、必要なデータはそのファイルから参照するような手順をとることをお勧めします。

＞具体的な作業としては、まず親データシート(会員台帳)の基本データの最新化をします。その結果が自動的に分割した別シート1、シート2のデータに反映されることが不可欠。

例えば、別シートに区分（例：データベースシートのC列にある場合）を選択する欄（入力規則のリストでドロップダウンリストから選択できるようにしておく）を作成し、その欄（例えば表示用シートのH2セル）に書いてある区分データを別シートに抽出するなら以下のような関数でデータベースのA列のデータが表示できます。

A2セル（下方向にオートフィル）
=INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$C$2:$C$1000<>$H$2)*1000+ROW($C$2:$C$1000),),ROW(A1)))&""

B2セル（右方向および下方向にオートフィル）
=IF($A2="","",VLOOKUP($A2,Sheet1!$A:$E,COLUMN(B2),0))

＞この分割したデータシートは案内書や請求書などワードで作成した文書の差し込み印刷データに使用していますので、それぞれ別々のブックとして属性ごと、たとえば1区、2区などの名前を付けて保存しています。

このようなブックを分ける管理は、データの整合性を考慮すると最も不適切な管理です。

このようなケースでは、データの変更はデータベースシートのみで行い、上記の回答のように、必要に応じて表示用シートのH2セルの値を変更して必要なデータを表示させるようにします（このシートを差し込み印刷に使用します）

お礼

丁寧な解説ありがとうございました。確かに言われてみると別のブックにして管理していたために、子データをいじった時に、親データの修正をわすれていたので、それぞれの会費の合計が合わず、一晩かかって不整合箇所を発見しました。仕事が手につかず、大変な時間の浪費をしたもんだと、自分ながらいやになっていたところです。教えていただいたやり方を参考に、トライしてみたいと思います。関数式の文法のようなものは、見ただけでどういうことかを理解するまでには、もう少し時間がかかりそうです。