エクセル 名簿の人だけを関数で抜き出す方法

質問

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

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

　　　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　茨城　　岸本　　小計
・
・
・

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

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

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

回答No.2

kagakusuki · ベストアンサー

>もしよろしければ、式の意味？見たいなのも教えてもらえますでしょうか？

　まず、補助シートのA1セルに入力する

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

という関数ですが、その中の

ROW()

という部分はROW関数です。
　ROW関数は、括弧内に入力されている参照先のセルが、上から数えて何行目の行に存在しているのかを求める関数です。
　例えば、

=ROW(F4)

と入力しますと、F4セルが存在している

行番号である4が求められます。
　そして、

ROW()

の場合には、括弧内で、どのセルを参照するのかを何も指定していませんから、その様な場合には、その関数が入力されているセルの行番号を求める関数となります。
　そして、INDEX関数は、

INDEX(配列, 行番号, [列番号])

の形式で表され、配列の部分で指定した配列、或いはセル範囲の中から、上から数えて「行番号の部分でしていた数値」番目の行にある、左から数えて「列番号の部分でしていた数値」番目の列にある配列中の値、或いはセル範囲中のセルの値を返す関数です。
　つまり、

INDEX(名簿!$B:$B,ROW())

という部分は、名簿シートのB列の中で、上から数えてROW()番目にあるセルの値を返す関数という事です。
　前述の様に、ROW()はその関数が入力されているセルの行番号を返す関数ですから、結局、

INDEX(名簿!$B:$B,ROW())

という部分は、名簿シートのB列の中で、関数が入力されているセルと同じ行番号にあるセルの値を求める関数という事です。
　ですから、この関数をA1セルに入力した場合には、

INDEX(名簿!$B:$B,ROW())

という部分は、名簿シートのB1セルの値を求める関数となり、

名簿!$B1

と入力した場合と同じ値を返す事になります。
　それでは、なぜ簡単な

名簿!$B1

という記述にしないかと言いますと、例えば補助シートのA9セルに入力する関数の中で、

名簿!$B9

という形式で参照先を指定した場合には、もし、名簿シートのB9セルを切り取って、例えば名簿シートのB5セルに貼り付けた場合には、参照先が

名簿!$B5

に変わってしまい、同じ行のセルを参照しなくなります。
　又、例えば名簿シートのB7セルを削除した場合には、今までは名簿シートのB9セルであったセルが、１つ上に移動して、名簿シートのB8セルに変わってしまいますから、関数の参照先も、

名簿!$B8

に変わってしまい、同じ行のセルを参照しなくなります。
　この様に、通常はデータを編集する際に、セルの切取り、削除、挿入等の編集作業を行なうと、関数が正常に動作しなくなります。
　そこで、INDEX関数とROW()わ組み合わせる事で、元データのセルが削除されたり、セルの位置関係が変わった場合でも、間違いなく同じ行番号のセルを参照する様にしています。
　ですから、

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

という関数は、補助シートのA1セルに入力した場合には

=IF(名簿!$B1="","",名簿!$A1&":"&名簿!$B1)

という関数と同じ働きをする関数となります。
　IF関数は

IF(論理式, [真の場合], [偽の場合])

の形式で表され、論理式の部分に入力した数式が成り立っている場合には、真の場合の値を返し、論理式が成り立っていない場合には、偽の場合の値を返す関数です。
　ですから、

=IF(名簿!$B1="","",名簿!$A1&":"&名簿!$B1)

という関数は、

名簿!$B1=""

が成り立つ場合、即ち、名簿シートのB1セルが空欄の場合には、空欄のまま何も表示せず、それ以外の場合、即ち、名簿シートのB1セルに何らかの値が入力されている場合には、

名簿!$A1&":"&名簿!$B1

の関数によって返される値を表示する関数となります。
　Excelの関数の中では

&

という記号は、文字列を結合する働きをしています。
　名簿シートのA1セルには「東京」、B1セルには「山田」と入力されていますから、「東京」と「:」と「山田」を結合した

東京:山田

という値を返す関数という事です。
　従って、

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

という関数は、その関数が入力されているセルと同じ行番号の、名簿シートのB列のセルが空欄の場合には、何も表示せず、名簿シートのB列のセルに何らかの値が入力されている場合には、名簿シートのA列の値と「:」と名簿シートのB列の値を結合した文字列を返す関数となります。

　次に、補助シートのB1セルに入力する

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

という関数ですが、これは

=IF(AND(COUNTIF($A:$A,"="&元データ!$A1&":"&元データ!$B1),元データ!$C1="小計"),ROW(),"")

と同じ働きをする関数です。
　COUNTIF関数は、

COUNTIF(範囲, 検索条件)

の形式で表され、範囲内に、検索条件に合致するデータが幾つあるのかをカウントする関数です。
　ですから、

COUNTIF($A:$A,"="&元データ!$A1&":"&元データ!$B1),

の部分は、補助シートのA列の中に、元データシートのA1セルの値と「:」と元データシートのB１セルの値を結合した文字列と同じ値を持つセルが幾つあるのかを数える関数となります。
　元データシートのA1セルには「東京」、B1セルには「山田」と入力されていますから、「東京」と「:」と「山田」を結合した

東京:山田

という値が入っているセルが、補助シートのA列の中に幾つあるのかを数える関数となります。
　ExcelではIF関数の論理式の部分に入力されている関数の計算結果が、0の場合には、論理式が成り立っていないものとして扱われ、0以外の数値の場合には、論理式が成り立っているものとして扱われますから、

=IF(AND(COUNTIF($A:$A,"="&元データ!$A1&":"&元データ!$B1),元データ!$C1="小計"),ROW(),"")

の場合は、補助シートのA列の中に

東京:山田

という値が入っているセルが1つでも存在していて、且つ、元データのC1セルの値が「小計」である場合には、この関数が入力されているセルの行番号を返し、それ以外の場合には、何も表示しない働きをする関数という事です。

　次に、抽出データシートのA1セルに入力入力する

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

という関数ですが、その中の

ROWS($1:1)

の部分は、1行目から１行目までの範囲内に含まれている行数が何行あるかを数える関数です。
　これを、A2セルにコピーしますと、

$1:1

の部分の前半部分である

$1

の部分は絶対参照ですから変化せず、後半部分の

1

は相対参照ですから、

2

に変わり、

ROWS($1:2)

となりますから、1行目から2行目までの範囲内に含まれている行数が何行あるかを数える関数に変わります。
　つまり、この関数をコピーして行った場合、最初に入力したセルでは1を返し、その下へ向かって、2、3、4、・・・という具合に、最初に入力したセルから順番に1から始まる整数値を返す関数という事です。
　COUNT関数は、指定した範囲内に

、数値データが入力されているセルが幾つあるかを数える関数ですから、

COUNT(補助!$B:$B)

の部分は、補助シートのB列に、数値が入っているセル、即ち、名簿シートのA列とB列の何処かに、元データシートのA列とB列の値と同じ値となっている行が存在している場合の回数をカウントする働きをしています。
　SMALL関数は、

SMALL(範囲, 順位)

の形式で表され、指定範囲内に存在する数値データの中から、「順位」で指定した番目に小さな値を返す関数ですから、

SMALL(補助!$B:$B,ROWS($1:1))

の部分をコピーして、下方向に貼り付けて行きますと、補助シートのB列の中にある数値を、上から順番に小さい順に並べる関数となります。
　補助シートのB列には、元データシートの行の中で、名簿シートの中に同じデータがある、行の行番号のみが表示されていますから、

INDEX(元データ!A:A,SMALL(補助!$B:$B,ROWS($1:1)))

の部分は、元データシートのA列の中で、「元データシートの行の中で、名簿シートの中に同じデータがある」行にあるセルの値を、行数が若い順に並べて表示する関数となります。

この投稿のマルチメディアは削除されているためご覧いただけません。

お礼

先生と呼ばせてください!!笑

ん～何回読んでも私の頭では理解できません。
あと10回は読み直します。

もしよろしければもう一つ質問よろしいでしょうか？？

逆に名簿以外の人も表示させたいのですが、どうせればよろしいでしょうか？？

補助シートの計算式だけを変えればいけますか？
私なりにいじってみましたが、FALSEとでてしまい上手くいきませんでした・・・・

ここぞとばかりに、聞きまくってますが・・・
面倒でしたら無視してください((+_+))

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

回答No.3

kagakusuki

>このような難しい関数などはどのように覚えるのでしょうか？

　私の場合は、基本的に

Excelのヘルプで、覚えました。
　Excelの[関数の挿入]ボタン（数式バーの左にある「fx」と記されているボタン）をクリックしますと、「関数の挿入」ダイアログボックスが現れます。
　その「関数の挿入」ダイアログボックスで「関数の分類」を選択し、現れた関数名を選択する毎に、ダイアログボックスの下の方に、その関数の簡単な説明が現れますから、その説明を頼りに使えそうな関数が無いか探し、やりたいことに関連がありそうな関数があれば、「関数の挿入」ダイアログボックスの左下にある[この関数のヘルプ]というリンクをクリックします。
　すると、「Excelのヘルプ」ウィンドウが開き、その関数の詳しい説明が表示されます。
　例え選択した関数が、必要としているものとは違う関数であったとしても、「Excelのヘルプ」ウィンドウで１つ上の項目を開いたり、関連項目を表示させたりする事で、ある程度関連性のある関数には、どの様なものがあるのかを調べる事が出来ます。
　それから、私の覚え方とは異なりますが、Excelの参考書は世の中にたくさん存在しますから、それを読んで勉強するという方法も、御勧め致します。（Excelの参考書は大抵の図書館にも置いてあると思います）
　又、インターネット上にはExcelの使い方を解説したサイトが沢山ありますから、それらを利用するのも一つの手です。
　以下は、その様なサイトの一例です。

【参考URL】
　初心者のエクセル(Excel)学習・入門
　　http://excel.onushi.com/

　Excel（エクセル）学習室 / KENZO30
　　http://www.kenzo30.com/

　よねさんのWordとExcelの小部屋
　　http://www.eurus.dti.ne.jp/~yoneyama/

　エクセル技道場
　　http://www2.odn.ne.jp/excel/

　エクセル事典
　　http://www.excel-jiten.net/

お礼

ご回答ありがとうございます。
参考資料までつけて頂いて、ありがたいです。

エクセルのヘルプなど活用してみたいと思います。
難しい関数をすらすらと作ってしまうのってカッコいいですよね（*^_^*）

回答No.1

kagakusuki

関数と作業列を使えば可能です。
　今仮に、「補助」という名称のシートを作成し、そのA列とB列を作業列として使用すものとします。

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

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

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

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

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

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

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

　以上です。

お礼

ご回答ありがとうございました。
すごく助かりました。

もしよろしければ、式の意味？見たいなのも教えてもらえますでしょうか？

このような難しい関数などはどのように覚えるのでしょうか？
ちょっとしたものなら使えるのですが・・・
参考書などでも使えるようになりますか？？