エクセルで目次を抽出

質問

こんにちは、エクセル関数勉強中のものです。
エクセル作成中のタスク管理表についてご相談させてください。

現在、エクセルでC列に大項目、D列に小項目を入れた
以下のようなタスク表を作っています。
（エクセルはOffice2000です）
--------------------------------------------------
シート1（担当企業A社分）
　Ａ列　｜　Ｂ列　｜　Ｃ列　｜Ｄ列　
　空白　｜　空白　｜大項目１｜空白
　空白　｜　空白　｜　空白　｜小項目１a
　空白　｜　空白　｜　空白　｜小項目１ｂ
　空白　｜　空白　｜　空白　｜小項目１ｃ

　空白　｜　空白　｜大項目２｜空白
　空白　｜　空白　｜　空白　｜小項目２a
　空白　｜　空白　｜　空白　｜小項目２ｂ
　空白　｜　空白　｜　空白　｜小項目２ｃ
--------------------------------------------------

同様のシートが、各社ごとに５枚あります。
これらのシートのＣ列「大項目」だけを抜き出して、
新しい１つのシートにまとめたいのですが、よい方法はありますでしょうか？
イメージはこんな感じです。
--------------------------------------------------
　Ａ列　｜　Ｂ列　｜　Ｃ列　｜Ｄ列　
　空白　｜　空白　｜シート１の大項目１｜空白
　空白　｜　空白　｜シート１の大項目２｜空白
　空白　｜　空白　｜シート１の大項目３｜空白

　空白　｜　空白　｜シート２の大項目１｜空白
　空白　｜　空白　｜シート３の大項目２｜空白
　空白　｜　空白　｜シート４の大項目３｜空白
--------------------------------------------------

ちょっとわかりにくい説明ですみません。
まだ関数を勉強し始めたばかりで、別シートに抽出するのに
どんな方法が良いか調べたのですが、検討がつかずご相談させていただきました。

どうぞよろしくお願い申し上げます。

回答No.7

Nouble · ベストアンサー

より作業セルの少ないものも作ってみました

作業セルを参照ページ数+1下さい
今回は5シート参照なので仮にj1：j6まで頂きますが
必要に応じて式中の該当箇所を実際に作業セルを置く場所へ書き換えて下さいね

今回の参照先ページはシート1からシート5までとします

ではいきます
j1に0を
j2に=COUNTIF(Sheet1!$C:$C,"*")を
j3に=J2+COUNTIF(Sheet2!$C:$C,"*")を
j4に=J3+COUNTIF(Sheet3!$C:$C,"*")を
j5に=J4+COUNTIF(Sheet4!$C:$C,"*")を
j6に=J5+COUNTIF(Sheet5!$C:$C,"*")を
という感じに
参照先の枚数分累計カウントを作って下さい
累計カウントを作るだけ参照先が増える使用になってます

その後これらのセルにセルの書式設定のユーザー設定で
"";"";"";""
を設定して下さい

で、
=IF(ROW(A1)>$J$6,"",OFFSET(CHOOSE(LOOKUP(ROW(A1),$J$1:$J$6+1,COLUMN($A$1:$F$1)),Sheet1!$C$1,Sheet2!$C$1,Sheet3!$C$1,Sheet4!$C$1,Sheet5!$C$1,Sheet6!$C$1),SMALL(INDEX(ISBLANK(CHOOSE(LOOKUP(ROW(A1),$J$1:$J$6+1,COLUMN($A$1:$F$1)),Sheet1!$C:$C,Sheet2!$C:$C,Sheet3!$C:$C,Sheet4!$C:$C,Sheet5!$C:$C,Sheet6!$A:$A))*65535+ROW(Sheet6!$A:$A),0),ROW(A1)-LOOKUP(ROW(A1),$J$1:$J$6+1,$J$1:$J$6))-1,0,1,1))

後は必要と思われるだけ下にフィルして下さい

この式は前回よりのものよりも更に処理が重たいので
フィルする量は加減を見ながら増やした方が良いでしょう

今回は、各ページの各々C行全体を検索対象にしているので
検索開始位置を指定せずに済みます。

今回残念だったのが
本当はくし刺し演算を利用して
=IF(SUMPRODUCT(NOT(ISERROR(SEARCH("*",Sheet1:Sheet5!$C:$C)))+0)>=ROW(A1),SMALL(INDEX(ISERROR(SEARCH("*",Sheet1:Sheet5!$C:$C))*65535+ROW(Sheet1:Sheet5!$C:$C),0),ROW(A1)),"")
としたかったのですが

何故か受け入れられませんでした

あと、
Index(Sheet1:Sheet5!$C:$C,ROW(＄A：＄A),1,COLUMN($A$1:$E$1)

とかできたら此も可能性が広がるのですが、駄目でした。

残念です。ヾ(ーー;)

後書き
仕様変更に迫られたり運用法について解らないことがでた場合は
又ご相談下さい
説明させて頂きます。

お礼

こんにちは。2回も詳細のご案内を頂きありがとうございます！！！
こんな関数が作れるのですね。感動しました！！
さっそくこれから作業してご報告させていただきます。
ありがとうございました！

回答No.8

Nouble

失礼しました
蛇足な修正です
誤　=Index(Sheet1:Sheet5!$C:$C,ROW(＄A：＄A),1,COLUMN($A$1:$E$1)
正　=Index(Sheet1:Sheet5!$C:$C,ROW(＄A：＄A),1,COLUMN($A$1:$E$1))

回答No.6

Nouble

お待たせしました

作業セルを1列頂きますね
頂く作業セルがA列だと仮定します
《※　他の場所の方が良い場合はＡを全てその列名に書き換えて下さい》

では手順を説明します
まず結果を纏めるシートを開いて頂いて
《※　今回はシート1からシート5までを参照対象としシート6に纏めるようにしました》
Ａ5に参照先シート数《※　今回は5シートですね》の5を
Ａ6に参照開始セル行の位置から１つ引いた数《※　今回は7行目から参照ですね》の7-1で6を
Ａ7に
=CHOOSE((MOD(A6,1)>($A$5*0.01))*2+(COUNTIF(OFFSET(CHOOSE(MOD(A6,1)*100+1,Sheet1!$C$1,Sheet2!$C$1,Sheet3!$C$1,Sheet4!$C$1,Sheet5!$C$1,$A$1),Sheet6!A6,0,65535-Sheet6!A6,1),"*")=0)+1,A6+MATCH("*",OFFSET(CHOOSE(MOD(A6,1)*100+1,Sheet1!$C$1,Sheet2!$C$1,Sheet3!$C$1,Sheet4!$C$1,Sheet5!$C$1,$A$1),Sheet6!A6,0,65535-Sheet6!A6+0.0005,1),0),ROUNDDOWN(MOD(A6,1)+0.01,3)+IF(MOD(A6,1)*100+1>=$A$5,5,1)*0.0001+INT($A$6),ROUNDDOWN(A6,2)+0.0005,ROUNDDOWN(A6,2)+0.0005)
を
Ｃ7に
=IF(MOD(A7,0.01)*10000<2,OFFSET(CHOOSE(MOD(A6,1)*100+1,Sheet1!$C$1,Sheet2!$C$1,Sheet3!$C$1,Sheet4!$C$1,Sheet5!$C$1,$L$1),A7-1,0,1,1),"")
を
それぞれ入力します

その後
Ａ5：Ａ7にこのセルにセルの書式設定のユーザー設定で
"";"";"";""
を設定して下さい

後は必要分Ａ7とＣ7を下へフィルして下さい

此で完了です

いや～久々に大作になりました　(~ ~;)ヾ
参照先が1ページのみだとほんの数分できたのですが
参照先のデータエンドの処理と
参照先のページが全て処理済みになった場合の処理で此処まで膨らんでしまいました

お待たせして済みませんでしたがＥｘｃｅｌ2007で動作は確認済みですので大丈夫だと思いますが
何かあった場合は又お知らせ下さい。

では、

注意書き
Excel2007では循環の警告が出るのですが2003以前のものでは恐らく大丈夫だと思います

結構Ｅｘｃｅｌの反応が処理が超過して無くなるかも知れませんがご容赦下さい
※　家のＰｅｎ3デュアルでは結構軽かったです

回答No.5

Nouble

お急ぎでしょうがもしお望みなら
関数での解決法を作ります
「リストから入力」で使える可変リストを以前作ったことがあるので
それを紐解けば問題ないでしょう

極力避けますが
最悪の場合はブランク表示(データが入ってるように見えない)の作業列を頂く事になりますが
構わないですか？

作業列さえあれば至極簡単な話です
上手く行けばなしで行けます

お礼

こんにちは、ご回答ありがとうございました。
関数でもできるのですね！！！作業列を加えても問題ございませんので、
できましたら教えていただけますでしょうか。

回答No.4

mu2011

NO1です。
>関数やVBAでの自動化の方法がありましたら教えて頂ければ幸いです。
⇒関数では難しいので次のＶＢＡをお試し下さい。
　因みに目次シートは最後尾のシートとして下さい。
　以下のＶＢＡサンプルを目次シート（シートタブを右クリック→コード表示の右側の上段枠）に貼り付けて実行して下さい。
　Sub 目次一覧()
k = 1
For i = 1 To Sheets.Count - 1
For Each c In Worksheets(Sheets(i).Name).Range("C7:C100")
If c.Value <> "" Then
Worksheets("目次シート").Range("C7").Offset(k - 1).Value = c.Value
k = k + 1
End If
Next
Next
End Sub

尚、上記のRangeの範囲、目次シート名は調整して下さい。

お礼

詳しいご回答ありがとうございました！
2度もありがとうございます。昨日マクロの本を買い込みましたので、
それを見ながらこの式を入れ込もうと思います。

回答No.3

hiro001001

No.2で回答した者です。

Sheet2の大項目をSheet1へ抽出するなら、下に示すようなマクロでおおよそ実現できますが、

・抽出する前にSheet1をクリアしておく
・複数のシートから抽出した大項目を並べるため、Sheet1の貼り付け位置を調節する
・Sheet2に大項目が1つしかない場合、あるいは1つもない場合はうまく動作しないので、そのための処理を書く

など、まだいろいろ作りこむ必要があり、「関数を勉強し始めたばかり」という方に、この場で全てを説明するのは難しいかと思います。

'--------------------------------
Sub 大項目抽出()
'
' 大項目抽出 Macro
'
'シート2を選択
Sheets("Sheet2").Select

'C列を選択
Columns("C:C").Select

'オートフィルターON
Selection.AutoFilter

'「空白以外のセル」でフィルタリング
Range("C7").Select
Selection.AutoFilter Field:=1, Criteria1:="<>"

'コピー
Range("C7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

'シート1へ貼り付け
Sheets("Sheet1").Select
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=True, _
Transpose:=False

'シート2のオートフィルターOFF
Sheets("Sheet2").Select
Selection.AutoFilter Field:=1
Columns("C:C").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select

'シート1を選択
Sheets("Sheet1").Select

End Sub

お礼

ご回答ありがとうございました！
再度ご教示ありがとうございます。やはりある程度マクロが使えないと
厳しいのですね…。勉強してから出直します。

回答No.2

hiro001001

例えば Sheet1のセルにSheet2のC7セルの値を表示させたいなら、Sheet1のセルに

=Sheet2!C7

と書けばＯＫですが、それはすでにご存知ということでしょうか？

上記のような式を全ての大項目に対して適切に記述すれば、一応できることはできます。
しかし、大項目が追加/削除/移動された場合などはそれに合わせて抽出シートの式も修正しなければなりません。

大項目の追加/削除/移動を自動的に検知して適切に表示したいのであれば、関数だけでなくVBAを使わないと難しいと思われます。

お礼

ご回答ありがとうございました。
基本的な関数は使えるので、そのような方法はわかるのですが
おっしゃるように自動で検出して表示させたいと思っています。
手法としては関数ではなく、VBAになるのですね。
そちらの方法が分かりましたら、ご教示頂ければ幸いです。

回答No.1

mu2011

一例です。
（１）シートのＣ列を選択→データ→フィルタ→オートフィルタ→フィルタボタンを押下→空白セル以外を選択
（２）抽出範囲をコピー→別シートへ貼り付け
以上の操作を全シート分繰り返すで如何でしょうか。

お礼

ご回答ありがとうございます。
もちろん手作業でやるのでしたら簡単なのですが
自動で抽出するためのフォーマットを作りたいので、
関数やVBAでの自動化の方法がありましたら教えて頂ければ幸いです。