エクセルの参照結果を空欄を詰めて表示させたい

画像を添付しているのですが表示されているのかどうかよくわかりませんので、
下記にアップロードさせていただきました。「大きいサイズで見る」をクリックしてご参照いただけると幸いです。

http://yahoo.jp/box/W5RJJ9


貼付画像のように、

1. sheet1は3月１日から３１日までの横軸が「あ」～「つ」の項目で構成された表が配置されています。縦軸には氏名があります。添付画像ではスペースの都合上３月２日の部分まで表示しております。
2. sheet1の「く」欄にはその日によって「当」が表示される人がいたりいなかったりします。表示される人は最大で９名です。この「当」の部分のセルには計算式が入っており、別のsheet3の内容次第で見かけ上「当」と表示されたり空欄となります。
3. sheet2のN2欄に3月１日から３１日までの日付を入力します。
4. するとsheet2のB30:B38に指定された日付で「当」にあたる人のみ名前が順に表示されるようにしたいです。貼付画像の例では３月１日なら「AAA」「BBB」「EEE」「GGG」がsheet2のB30:B33に表示され、３月２日なら「BBB」「CCC」「EEE」と表示されるようになります。

私には難しくてこれを実現することができません。なお、マクロはさっぱり分かりませんのでマクロを使わない方法でお願い致します。よろしくお願いします。

No.1 回答者： tom04 回答日時： 2014/02/24 18:33

こんばんは！

アップされている画像を拝見しました。

質問では
＞3. sheet2のN2欄に3月１日から３１日までの日付を入力します。
となっていますが、画像ではN1セルに日付を入力するようですので、
N1に表示したい日付を入力すると表示するようにしてみました。

今回は「く」限定のようですので・・・

Sheet2のA30セルに
=IF(N$1="","",IFERROR(INDEX(Sheet1!B$6:B$22,SMALL(IF(OFFSET(Sheet1!A$6:A$22,,MATCH(N$1,Sheet1!$2:$2,0)+7,,1)="当",ROW(A$1:A$17)),ROW(A1))),""))
これは配列数式になりますので、Shift+Ctrl+Enterで確定！

この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のA30セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後の{ }マークが入り配列数式になります。
このA30セルをオートフィルで下へコピーしてみてください。

※　数式の細かい説明は割愛します。m(_ _)m

この回答へのお礼

素早いご回答誠にありがとうございました。
こんなのが簡単に出来てしまうのが本当に羨ましいです。
ありがとうございました。

お礼日時：2014/03/01 02:03

No.2 回答者： MackyNo1 回答日時： 2014/02/24 22:16

例示のレイアウトならSheet2に以下の式を入力して下方向にオートフィルしてください。

=INDEX(Sheet1!$B:$B,SMALL(INDEX((INDEX(Sheet1!$6:$100,0,MATCH($N$1,Sheet1!$4:$4,0)+8)<>"当")*1000+ROW($6:$100),),ROW(1:1)))&""

この回答へのお礼

ご回答誠にありがとうございました。
#N/Aと出てしまうのですが、私の環境の問題でしょうか？？？

お礼日時：2014/02/27 19:26

No.3 回答者： tom04 回答日時： 2014/02/24 23:10

No.1です。

たびたびごめんなさい。

前回の数式は1行ずれていました。
Sheet2のA30セルの数式を
=IF(N$1="","",IFERROR(INDEX(Sheet1!B$7:B$23,SMALL(IF(OFFSET(Sheet1!A$7:A$23,,MATCH(N$1,Sheet1!$3:$3,0)+7,,1)="当",ROW(A$1:A$17)),ROW(A1))),""))
に訂正してください。

※　前回同様、配列数式です。m(_ _)m

この回答へのお礼

再度のご投稿誠にありがとうございました。

お礼日時：2014/03/01 02:02

No.4 ベストアンサー 回答者： MackyNo1 回答日時： 2014/03/01 01:23

N02の回答者ですが、私の回答も行がずれていますね。

日付はSheet1の3行目に入力されていてデータは7行目からなので、以下のように修正してください。

=INDEX(Sheet1!$B:$B,SMALL(INDEX((INDEX(Sheet1!$7:$100,0,MATCH($N$1,Sheet1!$3:$3,0)+8)<>"当")*1000+ROW($7:$100),),ROW(1:1)))&""

この回答へのお礼

素早いご回答誠にありがとうございました。
こんなのが簡単に出来てしまうのが本当に羨ましいです。
心より感謝しております。ありがとうございました。

お礼日時：2014/03/01 02:02