Excel 不規則な表からのデータ抽出

Excelで以下のようなことはできますか？

画像のSheet1の各表の、
「点数計」セル（B6,B12,B18）がゼロでない人の「名前」（B3,B9,B15）等のデータを、
Sheet2の表へ、上から詰めて自動的に表示させたいです。
自動的に、というのは、Sheet1の値が変わったときに自動でShhet2にも反映されるようにしたい、ということです。

他の質問を見ると配列数式？を用いていますが、
私のようにデータの入力されている行や列がバラバラでもできるのかと思い質問しました。

よろしくお願いします。

質問者からの補足コメント

• よく分からずにカテゴリ設定してしまいました…
  見つけていただきありがとうございます。
  そしてこんな丁寧な回答をしていただきありがとうございます。
  
  一つ目の数式を自分の表に当てはめてみましたが、
  分からない部分が何カ所かあるので、教えていただけますでしょうか。
  自分なりに考えた部分もあるのですが、どうでしょうか…。↓

  
  No.2の回答に寄せられた補足コメントです。 補足日時：2018/11/29 20:23

• とっっっても丁寧な回答、ありがとうございます。
  自分の作成した表と見比べながらやってみました！！
  
  …が、なぜか結果が「0」になってしまいます。
  数式バー左横の関数ボタンでエラーをチェックしても、
  画像のように「山田太郎」になっているのですが、結果は「0」です…
  なぜなのでしょうか。
  
  何度も何度も申し訳ありません。すごく急いでいるものではありません。
  どうぞお手すきの際にご教示くださいませm( _ _ )m

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2018/11/30 20:09

No.1 回答者： whtg001 回答日時： 2018/11/25 22:43

DB化してから抽出すれば楽かなと思います。

No.2 回答者： tom04 回答日時： 2018/11/28 19:02

こんばんは！

Excelのカテゴリの方が回答が付きやすいと思いますが・・・

↓の画像のような配置で元データはSheet1の1行目から始まっているとします。

Sheet2のA2セルに
=IF(SUMPRODUCT((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0))<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0),ROW(A$1:A$1000)-3),ROW(A1))))

配列数式なので、Ctrl＋Shift＋Enterで確定！（←必須★）

B2セル（配列数式ではありません）に
=IF($A2="","",INDEX(Sheet1!$A:$F,MATCH($A2,Sheet1!$B:$B,0)+1,COLUMN(A1)*2))

という数式を入れフィルハンドルで右へD2セルまでコピー！
最後にA2～D2セルを範囲指定　→　D2セルのフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

No.3 回答者： tom04 回答日時： 2018/11/29 21:38

No.2です。

前回投稿した数式のA2セルの方の数式の説明ですね。
あくまで当方がアップした画像の配置での数式なので、
お手元の配置と異なれば数式も変わってきます。

まず、数式の大きな流れを説明します。
前回の数式
=IF(SUMPRODUCT((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0))<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0),ROW(A$1:A$1000)-3),ROW(A1))))

を判りやすく
=IF(SUMPRODUCT((①*②)<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF(①*②,ROW(A$1:A$1000)-3),ROW(A1))))
としてみました。

前半の
＞IF(SUMPRODUCT((①*②)<ROW(A1),""
の部分はエラー処理でB列「点数計」行のデータ数が数式を入れるセルの行数より少ない場合は空白に！
という意味です。

①*②の掛け算は　数式①かつ数式②が成り立つ「AND」条件になります。

①　(MOD(ROW(A$1:A$1000),6)=5)
は
A1～A1000の行で「6」で割ったあまりが「5」の行番号　5・11・17・・・（←点数計の行）
が「TRUE」（＝1）で他の行は「FALSE」（＝０）です。

②　(Sheet1!B$1:B$1000<>0)
B1～B1000　が「0以外」　となり
(SUMPRODUCT((MOD(ROW(A$1:A$1000),6)=5)*(Sheet1!B$1:B$1000<>0))　で
①×②　→　「点数計」の行がTRUE、なおかつその行のB列が「0」以外のセル数が求められます。

＞(MOD(ROW(A$1:A$1000),6)=5)
の部分は
＞(MOD(ROW(Sheet1!A$1:A$1000),6)=5)
としても良いのですが、単に行番号の判定だけなので敢えてSheetは指定していません。
ただ、B列判定はSheetをちゃんと指定する必要があります。

前回の画像では「2」という結果になります。
数式を入れたセルを「<ROW(A1)」　「<1」になります。
それを下へフィル＆コピーすれば　「<ROW(A2)」 <2　→　「<ROW(A3)」 <3　・・・
IF関数で　該当セル数<ROW(A1),""
としていますので、3行目以降は空白になります。（該当データ数分の行数は表示される）

次にINDEX関数の
INDEX(Sheet1!B$1:B$1000
の部分は問題ないと思います。B列のどこの行を表示するか？だけです。

そのB列の
SMALL(IF(①*②,ROW(A$1:A$1000)-3),ROW(A1)
という配列数式で
(IF(①*②,ROW(A$1:A$1000)-3)
の部分は　①かつ②　が「TRUE」か「FALSE」かを行ごとに判定し、
（画像の配置では5・17行目が「TRUE」です。）そのすべての行に行番号を掛け合わせます。
すなわち　5・17　行目だけが　「5」・「17」という結果が返り、他の行はすべて「0」になります。
（「FALSE」にいくら大きな数値を掛けても結果は「0」（FALSE）です）
※　この部分が配列数式なので、Ctrl＋Shift＋Enterの操作が必要になります。

最後の「ROW(A1)」の部分はSMALL関数の「順位」の部分になりますので、
最初の行はTRUEの行の「1番目」→　5行目　次の行は「ROW(A2)」となり2番目　→　17行目・・・と順に表示させます。

ただ表示させたいのは「点数計」の行ではなくその3行上の「名前」の行なので「-3」としています。

以上長々と書きましたが
逆に判りにくくなりましたかね？m(_ _)m

No.4 ベストアンサー 回答者： tom04 回答日時： 2018/11/30 20:31

続けてお邪魔します。

まず思い当たるのが「配列数式」になっていないのでは？
数式バー内を確認してみてください。
数式の前後に　{ }　マークが入っていますか？ない場合は配列数式になっていないので
Ctrl＋Shiftキーを押しながらEnterキーで確定してください。

これで配列数式になります。

※　画像を拡大して気になる点が・・・
＞MOD(ROW(A$1:A$1000),45)=29
となっていますよね。
というコトは一人当たり45行の表で、「点数計」の行は29・74・119・・・
と45行おきにある！となるのでしょうか？

そしてINDEX関数の「行」のところが「-33」となっていますが
万一最初の行（29行目）の「点数計」が0以外の場合、その33行上はないのでエラーになってしまいますね。

配列数式はPCに相当の負担を掛けます。極端に広い範囲を指定してしまうと
計算速度がかなり落ちるので当方が示した数式は1000行までで抑えています。
おそらく3000とか4000行までであれば大丈夫だとは思います。

ただ、画像の数式が正しくてそんなに行が飛んでいたり、データ数が極端に多い場合は
VBAの方が簡単のような気がします。m(_ _)m

この回答へのお礼

データ量はたしかにとても多いです。
0になってしまう理由も分からないままですので、
VBAに挑戦してみようと思います。

とても丁寧な回答、ありがとうございました！

お礼日時：2018/12/02 21:58