データSheet１から条件で分類したデータをSheet2に展開する方法を教えてください

シート１に未整理のデータで３００行くらいあります
シート２に1a,2a・・・と区分された表が連続してあります
オートフィルターや並び替えではない方法でSheet1のデータをSheet２の1a部分　2a部分・・と連続して反映する方法で四苦八苦しています
Sheet１D列の”休”はデータをSheet2には反映させません
シート１には空白の行もありそれもSheet2には反映させません
どなたかお助け下さい

　Sheet1
　 A 　 B 　　C 　 D E　　　　　　　　　
　　　　区分　　名前　　　時間　　 休 備考
　　1 　１a　　阿部 8:00 単独
　　2　　2a　　安藤 9:00　　　　　　
　　3　　1a　　伊藤 7:00　　　　　　出張
　　４　 3a　　 加藤 6:00　　休　　
　　５　（ブランク行）
　　６　 1a 佐藤　　 8:00　　　　　　二重
　　
Sheet2
　　　　 A 　 B 　C 　D
　　 1 1a　　名前　　 時間　　 備考　―　この行は入力済み　　　　　　　　　　　　　　　
　　 2 　　　　阿部　　 8:00 　　 単独
　　 3　　　　 伊藤　　 7:00 　 出張
　　 4　　　　 佐藤 　8:00 　 二重
　 5 　
　６　
　　 ７ 2a 名前　　時間　　　　備考　―　この行は入力済み
　　 ８　　　　 安藤　　　9:00　　　　
　　 9
　 10
　　以下　3a 4a と続いています

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

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

• 表になっていなくてすみません

    補足日時：2017/11/10 00:55

No.1 回答者： 銀鱗 回答日時： 2017/11/10 02:26

表を丸ごと「ピボットテーブル」に入れてはどうでしょう。

あるいは ”Sheet1” を複製し ”Sheet1 (2)” を作り、”Sheet1 (2)” に対してフィルタを掛けるなど。


・・・余談・・・
教えて!gooでは
「連続した半角スペースは一つにまとめられる」
「行頭の半角スペースは省略される」
「本文の初めのスペースは省略される」
を念頭にしてレイアウトを考えると良いです。

位置を揃えたいときは、
　IIIIIIII｜←
のように半角のスペースを並べるのではなく
　ＩＩＩI｜←
のように全角を続けて最後に半角を付けるか、
　ＩＩIＩ｜←
全角と全角の間に半角を挟むなど工夫してみましょう。
（スペースの代わりにアルファベットの「I」を使って表現してみました）

ちなみにExcelで半角スペースを続けた文字列を扱う数式は正しく表示できないので、REPT関数を使って回答することがあります。

この回答へのお礼

有難うございました　ビボットテーブルですか　やってみます

お礼日時：2017/11/10 23:15

No.2 回答者： tom04 回答日時： 2017/11/15 21:59

こんばんは！

一例です。
↓の画像のようにSheet2の項目行は入力済みなのですね？

画像ではSheet2のB2セルに
=IFERROR(INDEX(Sheet1!$A$1:$E$1000,SMALL(IF(Sheet1!$A$1:$A$1000=INDEX($A$1:$A1,MAX(IF($A$1:$A1<>"",ROW($A$1:$A1)))),ROW($A$1:$A$1000)),ROW(A1)),MATCH(B$1,Sheet1!$A$1:$E$1,0)),"")

配列数式なのでCtrl＋Shift＋Enterで確定！
※　この画面から上記数式をそのままドラッグ＆コピー　→　B2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
B2セルのフィルハンドルで列・行方向にコピー！

B2セルを選択　→　B2セルの四辺にマウスポインタを移動させ上下左右の小さな矢印になったところで
Ctrlキーを押しながらB8セルまでドラッグ！
数式バー内に同じような数式が入りますが、
＞ROW(A1)　の部分が　ROW(A7)　に変わっていると思いますので、
その部分だけを　「A1」に変更　→　Ctrl＋Shift＋Enterで確定　→　列・行方向にフィル＆コピー！

3aのB14にも同じ操作をし、数式バー内の「A13」を「A1」に訂正し、配列数式の操作！　→　フィル＆コピー！

という具合にすべての行で行ってみてください。
尚、時間の列は最後に好みの表示形式に変えます。

※　参照元が空白セルの場合、「0」が表示されてしまいます。
もちろん数式で処理はできますが、数式がかなり長くなりますので、
メニュー　→　ファイル　→　オプション　→　詳細設定　→　「次のシートで作業するときの表示設定」の
「ゼロ値のセルにゼロを表示する」のチェックを外しておきます。

※　この「ゼロ値・・・」の操作をした場合、「0」表示が必要なセルの「0」がある場合
その「0」も非表示になります。m(_ _)m

この回答へのお礼

有難うございます
Sheet2のB2に関数入力して配列数式にしたのですがB2セルが”＃NAME?”の表示で
”数式に認識できないテキストが含まれています”
となってしまっています。よく分かりません。

お礼日時：2017/11/19 23:39

No.3 回答者： tom04 回答日時： 2017/11/20 14:16

No.2です。

＞B2セルが”＃NAME?”の表示で・・・

お使いのExcelのバージョンは何になるのでしょうか？
前回の「IFERROR関数」はExcel2007以降のバージョンで使える関数になります。
Excel2003以前だと使えませんので、別の数式にする必要があります。

前回アップした画像通りの配置だとすると
Sheet2のB2セルに
=IF(COUNTIF(Sheet1!$A:$A,$A$1)<ROW(A1),"",INDEX(Sheet1!$B$1:$E$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1)),MATCH(B$1,Sheet1!$B$1:$E$1,0)))

配列数式なのでCtrl＋Shift＋Enterで確定！　→　列・行方向にフィル＆コピー！

B8セルは
=IF(COUNTIF(Sheet1!$A:$A,$A$7)<ROW(A1),"",INDEX(Sheet1!$B$1:$E$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A$7,ROW($A$1:$A$1000)),ROW(A1)),MATCH(B$1,Sheet1!$B$1:$E$1,0)))

※　数式バー内で再編集する個所が4か所必要になります。
同じく配列数式！　→　列・行方向にフィル＆コピー！

B14セルは
=IF(COUNTIF(Sheet1!$A:$A,$A$13)<ROW(A1),"",INDEX(Sheet1!$B$1:$E$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$A$13,ROW($A$1:$A$1000)),ROW(A1)),MATCH(B$1,Sheet1!$B$1:$E$1,0)))

B8セル同様、4か所再編集　→　配列数式　→　列・行方向にフィル＆コピー！


こんな感じになると思います。

※　画像通りの配置だとして、まずはこの画面から数式をコピー＆ペーストし、
数式バー内を一度クリック　→　Ctrl＋Shift＋Enterで確定してみてください。m(_ _)m

この回答へのお礼

有難うございます
エクセル２００３の古いバージョンです　お手数をおかけしました　”＃NAME?"は解消できました
少しずつ出来始めてきました
４箇所の編集は＞ROW(A1)がROW（A７）になっている部分のことでしょうか
何度もすみません

お礼日時：2017/11/21 01:23

No.4 回答者： tom04 回答日時： 2017/11/21 13:44

続けてお邪魔します。

＞４箇所の編集は・・・
B2セルの数式で順に説明します。

①　COUNTIF(Sheet1!$A:$A,$A$1)　の「$A$1」部分　→　1a・2a・３a　のセル番地（絶対参照）
②　SMALL(IF(Sheet1!$A$1:$A$1000=$A$1)　の「$A$1」部分　→　1a・2a・3a　のセル番地（絶対参照）
③　ROW(A1)　部分（2か所）　→　各行にコピー＆ペーストした場合は　A7・A13　のようにセル番地が変わってきていると思います。
これを必ず「A1」から始めます。（B1やC1、Z1でもどの列でも構いません、必要なのは行番号です）

この「ROW(A1)」は「SMALL関数」の「順位」に当たる部分なので、必ず「1」から設定しなければなりません。
下へフィル＆コピーするたびに　ROW(A2)　→　ROW(A3)　→　ROW(A4)　と変化するので
INDEX関数で範囲指定した、「1a」の1番目　→　2番目　・・・　と順に表示させるためです。

他の「2a」なども同じ理屈になります。INDEX関数で範囲指定した「2a」の1番目　→　2番目　・・・

要するに今回の配列数式の説明をすると
B2セルの数式は
＞IF(Sheet1!$A$1:$A$1000=$A$1)で
Sheet1A列が「1a」の行のみ「TRUE」になり、それ以外は「FALSE」になります。
「TRUE」の小さい順に表示！という数式になります。

他の「2a」や「3a」も同様の意味です。m(_ _)m

この回答へのお礼

何度も有難うございます
C列の表示が出来ました　Sheet１のデータ変更が反映されました
B・D列は＃NAで”値が数式または関数に対して無効です”となってしまっています
何とか試行錯誤してやります
本当に有難うございました

お礼日時：2017/11/21 16:34

No.5 ベストアンサー 回答者： tom04 回答日時： 2017/11/21 16:49

＞B・D列は＃NAで”値が数式または関数に対して無効です”となってしまっています・・・

Sheet2の1行目の返したい項目名はSheet1と同じ項目名になっていますか？

列参照はその項目名の列を参照するようにしていますので、
1行目の項目名が異なればエラーになります。m(_ _)m

この回答へのお礼

本当に有難うございます
出来ました！！
ご指摘のとおり項目名の表示が異なっていました
全く基本的な事柄でお恥ずかしい限りです
何度も有難うございました

お礼日時：2017/11/21 17:13

No.6 回答者： tom04 回答日時： 2017/11/21 16:51

何度もごめんなさい。

前回書き忘れましたが、ちゃんと配列数式の操作になっているでしょうか？
数式バー内で再編集後必ずCtrl＋Shiftキーを押しながらEnterで確定する必要があります。

こちらで考えられるとすればこの程度でしょうか。m(_ _)m