一覧から表へ転記するには 2

質問

先ほど画像を載せたところ１つしか載せれないことがわかり、削除して入れ直そうとしましたが画像を載せることができませんので、同じ内容を掲示して申し訳ありません。画像を付け直して出させてください。エクセルでこんな感じに入力した一覧表から自動的に別シートの表へ落とし込みをしたいと思いますがどうしたらできるのでしょうか？
Sheet1：入力一覧表
　　　A　 　：B　　　　　：C　　　：D
１：受注月　：営業ランク ：物件名 ：台数
２：2009/8　：確定　　　 ：○×▲ ：100
３：2009/7　：AAA　　　　：□◇　 ：250
４：2009/7 ：確定　　　 ：◎□▲ ：350
５：2009/7　：確定　　　 ：○◇× ：200
６：2009/8　：AA 　　　　：◎×▲ ：500
７：2010/1　：A　　　　　：×□○ ：120
～～～以後同じように続く。

Sheet２：山積一覧表
A 　：B　　 ：C　　 ：D　 　：E ：F　　 ：G 　：H～
１：月 　：確定　：確定　：AAA 　：AAA ：AA　　：AA　　：～B　
２：月 　：物件名：台数　：物件名：台数 ：物件名：確定　：～　　
３：７月 ：　　　：　　　：　　　： ：　　　：　　　：～
４：　　 ：　　　：　　　：　　　：　　 ： ：　　　：～
５：　　 ：　　　：　　　：　　　：　　 ：　　　：　　　：～
７：８月 ： 　　 ：　　　：　　　：　　 ： ：　　　：～
８
～以下同じ様に空欄がいくつか作ってあります。
これを
A 　：B　　 ：C　　 ：D　 　：E ：F　　 ：G 　 ：H～
１：月 　：確定　：確定　：AAA 　：AAA ：AA　　：AA　　：～B　
２：月 　：物件名：台数　：物件名：台数 ：物件名：確定　：～　　
３：７月 ：○◇×：200 　：□◇　：250　：　　　：　　　：～
４：　　 ：◎□▲：350 　：　　　：　　 ： ：　　　：～
５：　　 ：　　　：　　　：　　　：　　 ：　　　：　　　：～
７：８月 ：○×▲：100 　：　　　：　　 ：◎×▲：500 　：～
８～
という形にしたいと思います。どのようにしたらいいでしょうか？
空欄は各月７行～８行空けています。
一覧表の月は会社の年度で2009/７月～2010/６月までとなっており
ます。また１ページに上半期を入力しています。

もう１つ質問があります。例えばBとCでワンセットのセルが７行
作ってありますがそれ以上の受注物件が上がった場合DとFの行にも
実際は枠があります。BとCの空欄が埋め尽くされた場合、CとDの
空欄を埋めていくといったことができるのでしょうか？
これ以上列は現実的に増えないです。

何卒回答をお願いします。

回答No.3

MackyNo1

>$A3を$A11に書き換えたのですが「Ctrl+Shift+Enter」で確定できません。そのまま数式が入ってしまいます。

一度、セルの書式を「標準」にして「Ctrl+Shift+Enter」で確定してみてください（後でユーザー定義を設定）。

上記の回答だけでは、完全な操作を提示できていませんので、うまくいかない部分は自分で数式内容を考えて、数式の修正を適宜行ってください。

お礼

ありがとうございました。しばらく自分で研究してみます。本当にありがとうございました。

回答No.2

MackyNo1

入力シートとまとめシートの月の欄を私が提示したように設定しているなら以下のような手順で一覧表を作成することができます。

まず下準備としてまとめシートのランクと月が記入された結合セルにすべて同じ値を挿入する操作をしてください。

現在何も記入されていないB4セルに「=B3」と入力し、B10セルまでオートフィルしてください。
次に、B3セル（空白セル）からB10セルまでを選択し、右クリック「コピー」し、結合したA3セル以下を選択して、右クリック「形式を選択して貼り付け」で「数式」と「空白セルを無視する」にチェックを入れてOKします（これでA4セル以下にもA3セルの値が入力されています）。

同様に空白行のC列にその左のB列を参照する数式を入力し、右方向に３つオートフィルして、B列からE列までを選択肢、1行目のランクの結合セルにも同じ値を挿入します。

これで準備が完了しましたので、次は該当データを参照する数式を入力します。

物件名のB3セルに以下の式を入力して、Ctrl+Shift+Enterで確定し右に１つオートフィルしてからこの2つのセルを下方向に10行目までオートフィルします。

=INDEX(Sheet1!C:C,SMALL(IF((Sheet1!$A$2:$A$2000=$A3)*(Sheet1!$B$2:$B$2000=B$1),ROW($2:$2000),10000),ROW(A1)))

B3からC10セルを選択して、セルの書式設定で表示形式ををユーザー定義にして、「G/標準;;;@」と入力します。

B3セルを選択し、セルの周囲にカーソルを置いて、B11セルにドラッグアンドドロップし（これで数式が変化せずそのままセルの移動が行われる）、B11セルで同様のオートフィル操作を行います。
元の空白となったB3セルはB4セルを上方向にオートフィルしてデータを復活させます。

次に、B3セルをD3セルにコピーし、その数式の「Sheet1!E:E」を「Sheet1!C:C」にROW(C1)の部分をROW(C8）に修正して同様にオートフィルコピーします。

ちなみに、ROWの部分やINDEXの範囲を指定する部分をもっと複雑な数式にすれば1つの数式で表示することも可能ですが、数式のメンテナンスの観点からできる限り簡潔な数式を提示しました。

お礼

すばやい回答ありがとうございます。早速作成してみます。ありがとうございました。

補足

いろいろ試しているのですが申し訳ありません。
１．上記「B3セルを選択し、セルの周囲にカーソルを置いて、B11セルにドラッグアンドドロップし・・・・」
でドラッグアンドドロップを行いました。すると「７月」「確定」の物件名が出ますので=INDEX(Sheet1!C:C,SMALL(IF((Sheet1!$A$2:$A$2000=$A3)*(Sheet1!$B$2:$B$2000=B$1),ROW($2:$2000),10000),ROW(A1)))
の$A3を$A11に書き換えたのですが「Ctrl+Shift+Enter」で確定できません。そのまま数式が入ってしまいます。
２．次の「B3セルをD3セルにコピーし、その数式の「Sheet1!E:E」を「Sheet1!C:C」に・・・」も「Ctrl+Shift+Enter」で確定できずに
困っています。申し訳ないですがお答えいただけないでしょうか？

回答No.1

MackyNo1

まず重要な点ですが、最初の（文章で）例示したデータと実際の図で表示したデータは入力した形式や、結合セルなどの条件が異なっています。

とりわけ、今回のようなご質問では、数式が非常に複雑になるため、これらの条件を正確に伝えないと、せっかくの回答が無駄になってしまいます。

まず確認ですが入力一覧表の年月は実際どのように入力されているのでしょうか？
一番おすすめなのは、「2009/9」のように入力し（データは2009/9/1）、セルの書式で「2009年9月」のようにしておいて、まとめシートのA列の「9月」も、同じ値を同様にセルの書式設定で「9月」と表示しているのが最も数式が簡略になります。

また、結合セルも4つのセルすべてに同じ値を入れるようにするのが最も数式が簡略化できます（裏ワザでこのようなことが可能ですがエクセルのバージョンによって操作が異なるのでバージョンを明記してください）。

まずこれらの条件を確定してから回答したほうが無駄な回答とならないように思いますので、条件を明示してください。

なお、ご希望の一覧データは、関数だけでも表示できますが、最終的には配列を使う数式となりますので、シートの動きが重くなります。

したがって、古いデータ（確定した変更のないデータ）は値に変更する、あるいは計算方法を手動にするなどの対応をする必要があると思います。

補足

回答ありがとうございます。確認内容に対し、答えさせていただきます。
１．入力一覧表の年月入力⇒お勧めの通りにいたしました。
２．エクセルのバージョンはExsel2002 SP3で作業しております。
３．シートの動きが遅くなっても全く問題ありません。
４．確認事項にないですが「まとめ表」の「営業ランク」は確定,AAA、AA、A、Bとなっております。また月は1ページに7月から12月まで2ページ目に1月から6月となっております。

素人の為、質問・回答の仕方等に失礼があると思いますが、何卒よろしくお願いします。