excel 特定の文字以降を抜粋する

質問

excel（2010）で特定の文字以降の範囲を抜粋して，作業をしたいです。
どのような関数を使えばできるか，教えてください。

だいたい，下記のようなデータです。

chapter1
1 1 25
2 1 20
3 2 20
4 1 24
5 3 25
6 4 19

chapter2
1 1 18
2 2 20
3 2 20

chapter3
1 1 25
2 1 20
3 2 20
4 1 24
5 3 25
6 4 19

英語の問題集のデータです。
左から順に，質問番号，難易度，得点です。

chapterＸというのは質問番号の上のセルに入っています。

ここから，chapter2の部分だけを抜粋して，難易度順に並べ直したいです。

なお，chapterにより質問数が違い，
人によって同じchapterでも問題数が違うので，
「chapter2の一つしたのセルから，chapter3のひとつ上区間を抜粋」
といった指定のしかたができると嬉しいです。

困ってます＞＜
どうぞよろしくお願いします。

回答No.5

kagakusuki

回答No.2です。
　元データーの行数分だけ数式をコピーせずとも、作業用のセル（作業列ではありません）を2つ使用するだけで済む方法です。

　今仮に、「chapter*」という形式の文字列が入力されているセルが存在している列がA列であり、「chapter2」という文字列は、A列中の複数のセルに重複して現れたりはしないものとします。
　又、検索する際のキーワードである「chapter2」という文字列を、E1セルに入力し、検索結果をE列～G列の3行目以下に表示するものとします。
　又、F1セルとG1セルを作業用のセルとして使用するものとします。（非表示にして見えない様にする事も出来ます）

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

=IF(OR($E$1="",COUNTIF($A:$A,$E$1)=0),"",MATCH($E$1,$A:$A,0)+1)

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

=IF(ISERROR(1/$F$1/COUNT(INDEX($B:$B,$F$1):INDEX($B:$B,ROWS($A:$A)))),"",IF(COUNTIF(INDEX($A:$A,$F$1):INDEX($A:$A,ROWS($A:$A)),"chapter*"),MATCH("chapter*",INDEX($A:$A,$F$1):INDEX($A:$A,ROWS($A:$A)),0)+$F$1-2,MATCH(9E+307,$B:$B)))

　次に、F1セルとG1セルの書式設定の表示形式を[ユーザー定義]の

;;;

として下さい。（これで、F1セルとG1セルが非表示になります。尚、下の添付画像では、解り易くするために、F1セルとG1セルを敢えて非表示にはしておりません）
　次に、F3セルに次の関数を入力して下さい。

=IF(ISERROR(1/$G$1/(ROWS($3:3)<=COUNT(INDEX($B:$B,$F$1):INDEX($B:$B,$G$1)))),"",SMALL(INDEX($B:$B,$F$1):INDEX($B:$B,$G$1),ROWS($3:3)))

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

=IF(ISNUMBER($F3),INDEX($A:$A,MATCH($F3,INDEX($B:$B,MATCH(IF($F3=$F2,E2,$E$1),INDEX($A:$A,$F$1-1):INDEX($A:$A,$G$1),0)+$F$1-1):INDEX($B:$B,$G$1),0)+MATCH(IF($F3=$F2,E2,$E$1),INDEX($A:$A,$F$1-1):INDEX($A:$A,$G$1),0)+$F$1-2),"")

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

=IF(OR($E4="",ISERROR(1/(VLOOKUP($E4,INDEX($A:$A,$F$1):INDEX(C:C,$G$1),COLUMNS($A:C),FALSE)<>""))),"",VLOOKUP($E4,INDEX($A:$A,$F$1):INDEX(C:C,$G$1),COLUMNS($A:C),FALSE))

　そして、F3～G3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

回答No.4

kagakusuki

回答No.2です。

>回答No2さんの方法ではchapter3についての表示や難易度順での表示にならないでしょう。

　申し訳御座いません、回答No.3様の仰る通りです。

>ここから，chapter2の部分だけを抜粋して，難易度順に並べ直したいです。

の一文を見落としておりました。

回答No.3

KURUMITO

回答No2さんの方法ではchapter3についての表示や難易度順での表示にならないでしょう。
次のように作業列を作って対応します。
お示しのデータがシート1のA列からC列で2行目から下方に入力されているとします。
作業列としてE2セルには次の式を入力して下方にどらっぶコピーします。

=IF(A2="","",IF(COUNTIF(A2,"chapter*")>0,ROUNDDOWN(MAX(E$1:E1),-2)+100,ROUNDDOWN(E1,-2)+B2))

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

=IF(E2="","",E2*10+COUNTIF(E$1:E1,E2))

そこで結果の表示ですが例えばシート2のA列からC列に表示させるとして
A1セルには選択したいチャプター名をchapter2のように入力します。
A2セルからC2セルには項目名などを入力することにしてA3セルには次の式を入力してC3セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT(INDEX(Sheet1!$A:$A,MATCH($A$1,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,IF(COUNTIF(Sheet1!$A:$A,"chapter"&MID($A$1,8,2)+1)=0,1000,MATCH("chapter"&MID($A$1,8,2)+1,Sheet1!$A:$A,2)))),"",INDEX(Sheet1!$A:$C,MATCH(SMALL(Sheet1!$F:$F,RANK(INDEX(Sheet1!$F:$F,MATCH($A$1,Sheet1!$A:$A,0)),Sheet1!$F:$F,1)+ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)))

これでシート2のA1セルにお望みのチャプター名を入力することで難易度順に表示されます。

補足

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

やってみたところ，sheet1には次のような値が示されました。
※E2，F2以下のデータです。
1 10
1 11
2 20
1 12
3
4

F列の値の意味はなんでしょうか？
また，shee2のA3には，「#N/A」と表示されます。

教えていただいたことをうまく理解できていないのかも
しれなくて申し訳ないのですが，
追加で教えていただけると嬉しいです。

回答No.2

kagakusuki

今仮に、「chapter*」という形式の文字列が入力されているセルが存在している列がA列であり、「chapter2」という文字列は、A列中の複数のセルに重複して現れたりはしないものとします。
　又、検索する際のキーワードである「chapter2」という文字列を、E1セルに入力し、検索結果をE列～G列の3行目以下に表示するものとします。

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

=IF(OR($E1="",ISERROR(1/(INDEX(A:A,MATCH($E$1,$A:$A,0)+ROWS($3:3))<>"")/(COUNTIF(INDEX($A:$A,MATCH($E$1,$A:$A,0)+1):INDEX($A:$A,MATCH($E$1,$A:$A,0)+ROWS($3:3)),"chapter*?")=0))),"",INDEX(A:A,MATCH($E$1,$A:$A,0)+ROWS($3:3)))

　次に、E3セルをコピーして、F3～G3のセル範囲に貼り付けて下さい。
　次に、E3～G3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　これで、「chapter2の一つしたのセルから，その次に現れるchapter*のひとつ上区間を抜粋」する事が出来ます。

回答No.1

suzuko

左端の列（A列の左側）を「chapter2の一つしたのセルから，chapter3のひとつ上区間」だけ選択。

データから「並び替え」を選択。

最優先されるキーを「難易度」の列に合わせて設定。OK.