エクセル 名簿以外のを抽出するには

質問

エクセルについて教えてください。

元データシートがあります。データの行列は結構な数が入っています。

　　　A 　　 B 　　　 C 　 　　 D 　　　 E 　　　 F 　　　 G ・・・・・
1　東京　　山田　　小計　　・・・　　・・・　　・・・　　・・・　　・・・・・
2　埼玉　　菊池　　小計
3　栃木　　中山　　平均
4　茨城　　岸本　　小計
5　東京　　村井　　合計

といった具合にいくつものデータがあります。

抽出データシートに「名簿シート以外の人物」でなおかつ「元データのC列が"小計"と記載されているもの」だけを抜き出したいのです。

名簿シート（今後増減あり）　

　　A　　　　B　　　　　　
1　埼玉　　菊池　　
2　東京　　山田　　
3　・・・　　　・・・　　
・
・

抽出データシート

　　　A 　　 B 　　 　 C 　　　 D 　 　　 E 　　　 F 　　　 G ・・・・・
1　茨城　　岸本　　小計
2　・・・　　　・・・　　小計
3　・・・　　　・・・　　小計
・

関数で可能でしょうか？
難しいなら、せめて、名簿以外の人物だけを抽出したいです。

また、元データや名簿が更新されるごとに抽出データが自動的に「名簿以外の人物」と
「C列が小計」のものだけを抽出したいのですが、関数以外に何かありますでしょうか？

よろしくお願いいたします。

回答No.3

KURUMITO

元データシートをシート1とし、名簿シートをシート2、抽出データシートをシート3としてここでは説明します。また、いずれのシートもA1セルから右横方向に項目名が有り、データはシート3を除いて2行目から下方に入力されているものとします。
初めにシート2について作業列を設け、例えばD2セルには次の式を入力して下方にオートフィルドラッグします。

=A2&B2

そこでシート1では作業列としてJ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(COUNTIF(Sheet2!D:D,A2&B2)=0,C2="小計"),MAX(J$1:J1)+1,"")

お求めの表はシート3に表示するとしてA1セルからC1セルには項目名を入力したのちにA2セルには次の式を入力してC2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

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

これでシート1やシート2のデータが更新される毎にシート3での表が更新されます。

お礼

回答ありがとうございます。
最終的には同じになっても、色々な方法があるんですね。
勉強になります。

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

回答No.2

kagakusuki

>そして!!なんとなく式を変えてみました。

　補助シートのA1セルの式は、全く同一の式の様に思えますが、何処を変更されたのでしょうか？
　補助シートのB1セルの式

=IF(AND(COUNTIF($A:$A,"<>"&INDEX(RORランチ!$A:$A,ROW())&":"&INDEX(RORランチ!$B:$B,ROW())),INDEX(RORランチ!$C:$C,ROW())="小計"),ROW(),"")

に関しましては、元の式である

=IF(AND(COUNTIF($A:$A,"="&INDEX(元データ!$A:$A,ROW())&":"&INDEX(元データ!$B:$B,ROW()))=0,INDEX(元データ!$C:$C,ROW())="小計"),ROW(),"")

の中の

COUNTIF($A:$A,"="&INDEX(元データ!$A:$A,ROW())&":"&INDEX(元データ!$B:$B,ROW()))=0

の部分を

COUNTIF($A:$A,"<>"&INDEX(RORランチ!$A:$A,ROW())&":"&INDEX(RORランチ!$B:$B,ROW()))

に変更されておられる様ですが、それでは、
「住所と名前の両方が一致するデータが、名簿シートに存在しない、元データシートの行のデータを抽出する」のではなく、
「名簿シートの中の何処かに、住所か名前のどちらか一方でも異なっているデータが、1つでも存在している場合には、元データのその行のデータを表示する」という関数になってしまいますから、結局、
「名簿シートの中のA列～B列の何処かに、空欄以外のセルが存在すれば、C列が小計となっている全ての行のデータを表示する」関数と同じ事になります。

お礼

お恥ずかしい・・・
関数って勉強していくと面白そうですね。
まぁ、私の頭じゃついていけませんが・・・

毎回毎回、ご親切に詳しく答えて頂き感謝しています。
本当にありがとうございます。

回答No.1

kagakusuki

今仮に、「補助」という名称のシートを作成し、そのA列とC列を作業列として使用すものとします。

　まず、補助シートのA1セルに、次の数式を入力して下さい。（QNo.7099654の補助シートのA1セルに入力したのと同じ数式）

=IF(INDEX(名簿!$B:$B,ROW())="","",INDEX(名簿!$A:$A,ROW())&":"&INDEX(名簿!$B:$B,ROW()))

　次に、補助シートのC1セルに、次の数式を入力して下さい。

=IF(AND(COUNTIF($A:$A,"="&INDEX(元データ!$A:$A,ROW())&":"&INDEX(元データ!$B:$B,ROW()))=0,INDEX(元データ!$C:$C,ROW())="小計"),ROW(),"")

　次に、補助シートのA1～C1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。
　次に、抽出シートのA1セルに、次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT(補助!$C:$C),"",INDEX(元データ!A:A,SMALL(補助!$C:$C,ROWS($1:1))))

　次に、抽出シートのA1セルをコピーして、抽出シートの、データを表示させる全てのセルに貼り付けて下さい。

　以上です。

お礼

あっ!!先生!!
こちらでもご回答ありがとうございました。
本当に助かりました。

あれから、前回回答頂いた補足部分を何度も読み直し、なんとなくり理解出てきました。
そして!!なんとなく式を変えてみました。

補助シートA1

　＝IF(INDEX(名簿!$B:$B,ROW())="","",INDEX(名簿!$A:$A,ROW())&":"&INDEX(名簿!$B:$B,ROW()))

補助シートB1

　=IF(AND(COUNTIF($A:$A,"<>"&INDEX(RORランチ!$A:$A,ROW())&":"&INDEX(RORランチ!$B:$B,ROW())),INDEX(RORランチ!$C:$C,ROW())="小計"),ROW(),"")

と入力したのですが・・・

これは正解でしょうか？？