エクセル抽出関数の使い方、教えてください!!

質問

投稿日時 - 2012-09-17 16:19:49のtom04さんに回答いただいた内容につきまして、改めて質問です。前回の場合、入力は2つの数字となっておりましたが、今回は1つの文字データでの抽出となっております。

添付図をご覧いただけてばと思います。

度々の質問ですみません。よろしくお願いいたします。

回答No.2

tom04 · ベストアンサー

こんばんは！

（1）点数範囲で検索
（2）学期毎に検索したい　
（3）学期をプルダウンで・・・

上記の3点のご要望があるというコトなので、
（1）に関しては　平均を検索値とします。
（2）（3）に関しては、各学期のSheetが存在するという前提です。
（1～3学期の3Sheetがあり、検索用のSheetを含めて全部で4Sheetある）
という前提での一例です。

↓の画像で「検索用」SheetのA1セルがプルダウンで　1～3学期を選べるようにしています。
そしてB2セル・C2セルに検索範囲の数値を入力するとします。

A4セルに

=IF(OR($A$1="",COUNTBLANK($B$2:$C$2)),"",IF(SUMPRODUCT((INDIRECT($A$1&"!$E$1:$E$100")>=$B$2)*(INDIRECT($A$1&"!$E$1:$E$100")<=$C$2))<COLUMN(A1),"",INDEX(INDIRECT($A$1&"!$A$1:$A$100"),SMALL(IF(INDIRECT($A$1&"!$E$1:$E$100")>=$B$2,IF(INDIRECT($A$1&"!$E$1:$E$100")<=$C$2,ROW($A$1:$A$100))),COLUMN(A1)))))

これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
これをオートフィルで列方向にコピーすると
画像のような感じになります。

こんな感じをご希望だったのでしょうか？
外していたらごめんなさいね。m(_ _)m

お礼

tom04さん
おかげさまで、無事DBが完成しました。只々感謝の一言です。本当に短期間ではありましたが、ありがとうございました。

来週から、また、別の課題を課せられております。更なる磨きをかけるべく、今後ともよろしくお願いいたします。

PS：教科選択の所をプルダウン式にしたのですが、表全体が大きすぎて縮小しているため、プルダウンした文字が非常に小さくて困っています。セル内のフォントは大きくできますが、プルダウンリストの文字はどうすれば大きくなるがご存じでいたらお教えください。

回答No.5

KURUMITO

お求めの表は学期や点数範囲をドロップダウンリストで選択できるようにして、該当する氏名を表示させたいとのことですね。
次のように作業列を作って対応するのがよいでしょう。
シート1にはお示しのデータが有るとします。
A1セルには1学期と入力し、A2セルからF2セルまでには氏名から点数範囲までの項目名を入力します。
3行目から下方のある行までに1学期のデータが入力されるとします。
その行のさらに下方にはA列に２学期を、その次の下の行には2行目と同じ項目名を入力し、その下行には2学期のデータを入力します。
3学期についても同様に下方の行に入力します。
そこで作業列としてG3セルには次の式を入力して下方にドラッグコピーします。

=IF(F3="","",IF(COUNTIF(INDEX(F:F,MATCH("ー",E$1:E2)):F3,F3)=1,MOD(ROUNDDOWN(MAX(INDEX(G:G,MATCH("ー",E$1:E2)):G2),-3),10000)+1000+COUNTIF(A$1:A2,"*学期")*10000,INDEX(INDEX(G:G,MATCH("ー",E$1:E2)):G2,MATCH(F3,INDEX(F:F,MATCH("ー",E$1:E2)):F2,0))+COUNTIF(INDEX(F:F,MATCH("ー",E$1:E2)):F2,F3)))

この式で注意が必要なのは"ー"の部分です。通常入力しますと"-"のようになってーが半角モードの-になってしまいます。一旦式を入力した後で-の部分をひらがなモードのーで書き変えることがよいでしょう。

H3セルには次の式を入力して下方にドラッグコピーします。

=IF(F3="点数範囲","",IF(COUNTIF(F$3:F3,F3)=1,MAX(H$2:H2)+1,""))

I3セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(H:H),"",INDEX(F:F,MATCH(ROW(A1),H:H,0)))

そこでお求めの表ですがシート2に表示させるとして例えば次のようにします。

A2セルには学期と入力します。
B2セルから例えばJ2セルまでを範囲として選択してから「データ」タブの「データの入力規則」で入力値の種類に「リスト」を選択し、元の値には1学期,2学期,3学期と入力します。シート1で使われた文字と同じことが必要ですし、カンマは半角モードで,と入力することが必要です。
A3セルには点数範囲と入力します。
B3セルからJ3セルまでを範囲として選択してから上と同じように「リスト」を選択し元の値の窓には
　=Sheet1!$I$2:$I$10　と入力してOKします。
A4セルには氏名と入力します。
B4セルには次の式を入力しJ4セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IFERROR(INDEX(Sheet1!$A:$A,MATCH(LEFT(B$2,1)*10000+INDEX(Sheet1!$H:$H,MATCH(B$3,Sheet1!$I:$I,0))*1000+ROW(A1)-1,Sheet1!$G:$G,0)),"")

これで学期と点数範囲を指定すれば該当する方の氏名が4行目以降に表示されますね。
なお、シート1で作業列が目障りでしたらそれらの列を選択して右クリックし「非表示」を選択すればよいでしょう。

お礼

KURUMITOさん、ご指導ありがとうございました。少々小生の説明が悪く、今回の意図とは異なる結果となりましたが、反対にこの抽出方法は別のところで早速活用させていただきました。とても参考になりました。ありがとうございました。

回答No.4

kagakusuki

>２．学期毎に検索を行いたい
>３．操作としては、"学期"をプルダウンにて

との事ですが、添付画像には1学期の表しか写ってはおらず、2学期や３学期のデータがありません。
　存在していないデータから抽出する事は出来ませんので、2学期や３学期のデータが何処に、どの様な形で存在しているのかを御教え願います。
　取り敢えずの話としまして、元データである１学期の表はSheet1に存在していて、２学期の表は１学期の表の最下段の行（9行目）から１行空けた、11行目のA列に「2学期」と入力されていて、12行目には2行目と同様に項目名が並び、2学期の各生徒のデータは13行目から始まっていて、3学期の表も、2学期の表の最下段の行から１行空けた所から始まっているものとします。
　又、Sheet3のA列とB列を作業列として使用して、Sheet2のA1セルにおいてドロップダウンリストを使用して学期の指定を行い、Sheet2の3行目以下に各抽出結果を表示するものとします。（但し、Sheet2のA列は点数範囲を表示）

　まず、Sheet3のA1セルに次の関数を入力して下さい。

=IF(OR(COUNTIF(INDEX(Sheet1!$A:$A,ROW()),"*学期"),ROW()=MATCH(999,Sheet1!$D:$D)+1),ROW(),"")

　次に、Sheet3のB1セルに次の関数を入力して下さい。

=IF(ISNUMBER(INDEX(Sheet1!$D:$D,ROW())),INDEX(Sheet1!$A:$A,VLOOKUP(9E+99,$A$1:$A1,1))&"#"&COUNTIF(INDEX(Sheet1!$D:$D,VLOOKUP(9E+99,$A$1:$A1,1)):INDEX(Sheet1!$D:$D,SMALL($A:$A,COUNTIF($A:$A,"<"&ROW())+1)),">"&INDEX(Sheet1!$D:$D,ROW()))+COUNTIF(INDEX(Sheet1!$D:$D,VLOOKUP(9E+99,$A$1:$A1,1)):INDEX(Sheet1!$D:$D,ROW()),INDEX(Sheet1!$D:$D,ROW())),"")

　次に、Sheet3のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、以下の様な操作を行って、Sheet2のA1セルに入力規則のドロップダウンリストを設定して下さい。

【Excel2007よりも前のバージョンのExcelの場合】
Sheet2のA1セルを選択
　　↓
メニューの[データ]ボタンをクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの「設定」タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
現れた「元の値」欄に以下の通りに入力

1学期,2学期,3学期

　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック

【Excel2007以降のバージョンのExcelの場合】
Sheet2のA1セルを選択
　　↓
Excelウィンドウの[データ]タブをクリック
　　↓
現れた「データツール」グループの中にある[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの「設定」タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
現れた「元の値」欄に以下の通りに入力

1学期,2学期,3学期

　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック

　次に、Sheet2のB3セルに次の関数を入力して下さい。

=IF(ROWS($3:3)>COUNTIF(Sheet3!$B:$B,$A$1&"#*"),"",INDEX(Sheet1!A:A,MATCH($A$1&"#"&ROWS($3:3),Sheet3!$B:$B,0)))

　次に、Sheet2のA3セルに次の関数を入力して下さい。

=IF(ISNUMBER($E3),IF($E2<CEILING($E3+1,20)+($E3>=80),"",IF($E3<80,FLOOR($E3,20)&"以上"&CEILING($E3+1,20)&"未満","80以上")),"")

　次に、Sheet2のB3セルをコピーして、Sheet2のC3～F3の範囲に貼り付けて下さい。
　次に以下の操作を行って、Sheet2のA3セルに条件付き書式を設定して下さい。

【Excel2007よりも前のバージョンのExcelの場合】
Sheet2のA3セルを選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄に次の数式を入力

=OR(INDEX(A:A,ROW()+1)<>"",AND(INDEX($B:$B,ROW())<>"",INDEX($B:$B,ROW()+1)=""))

　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「スタイル」欄の中にある実線をクリック
　　↓
「罫線」欄の中の四角形の下辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック

【Excel2007以降のバージョンのExcelの場合】
Sheet2のA3セルを選択
　　↓
[ホーム]タブ内の「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[新しいルール]をクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=OR(INDEX(A:A,ROW()+1)<>"",AND(INDEX($B:$B,ROW())<>"",INDEX($B:$B,ROW()+1)=""))

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「スタイル」欄の中にある実線をクリック
　　↓
「罫線」欄の中の四角形の下辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック

　次に、Sheet2のA3～F3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。
　次に以下の操作を行って、Sheet2のA列に罫線を設定して下さい。

Sheet2のA2～「A列における表中の最下段の行のセル」の範囲をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてからマウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「プリセット」欄の中にある[なし]ボタンをクリック
　　↓
「スタイル」欄の中にある実線をクリック
　　↓
「プリセット」欄の中にある[外枠]ボタンをクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　これで、Sheet2のA1セルに、ドロップダウンリストを使用して学期の指定を行うだけで、指定した学期のデータが、Sheet2のA列には点数範囲が、Sheet2のB～F列には各項目ごとのデータが、点数の高い順に並べ替えられた上で、点数範囲毎に纏められて表示されます。

回答No.3

tom04

No.2です！
たびたびごめんなさい。

No.2の
＞1～3学期の3Sheetがあり・・・
の部分は　1学期・2学期・3学期　というSheet名のSheetがある！
という前提です。
Sheet名が異なる場合はデータは表示されません。

それから余談ですが、
今回の質問を読むと他の方々は何をしたいのか？全く理解できないと思います。

私目のアドバイスがベストではありません。
この質問を初めて読まれる方にも理解できるような内容の方が良いと思います。

当方の方法よりもっと良い回答があると思いますよ。m(_ _)m

お礼

tom04さん、いつもいつもありがとうございます。本日より急遽出張のため作業が進んでおりませんが、是非明日帰宅後作業に取り掛かりたいと思います。

また、ご指摘ありがとうございます。tom4さんにも、他にご意見をいただいている方も失礼な問いかけでした。

夜遅くまで本当にありがとうございます。作業の結果をまた、ご報告いたいしますので、今後ともよろしくお願いします。

回答No.1

某HN クロメート(Chromate)（@CoalTar）

http://okwave.jp/qa/q7702306.html
http://okwave.jp/qa/q7702504.html

前回書き忘れましたが、「抽出関数」というものはありません。
関数の組み合わせである程度までなら数式として抽出っぽくできあがります。

基本は、一般機能としてオートフィルターがあります。
複雑な抽出の場合も、「フィルタオプションの設定」で抽出可能です。

#添付画像に質問を書くのは控えていただきたいです。アップされる画像は解像度が低く、何が書いてあるか解読不可能になるからです。