Excel2007で他のシートを参照して自動で表を作成したいと思ってい

質問

Excel2007で他のシートを参照して自動で表を作成したいと思っています。

Sheet1には下記のようなシフトを作成しています。
　　A　　　　B　　　C　　　D　　　E　　　F
1　　　　　1日　　2日　　3日　　4日　　５日
2　Aさん　　1　　　2　　　1　　　4　　　1
3　Bさん　　2　　 休　　　4　　　1　　　4
4　Cさん　　1　　　4　　　2　　　1　　　1
5　Dさん　　4　　　1　　　1　　　2　　　2

Sheet2からはシート名が１日　２日となっています。
それぞれのシート毎にその日付のシフトを並び替えて作成したいと思っています。
【１日のシフト（Sheet2)】
1　Aさん
1　Cさん
2　Bさん
4　Dさん

【２日のシフト（Sheet3)】
1　Bさん
2　Aさん
4　Cさん

休みのスタッフは表示しないように作成したいと思っています。

エクセルでこの様な作業は可能でしょうか。

回答No.3

tom04 · ベストアンサー

何度もお邪魔します。

補足を読ませてもらいました。
数式の説明もしなければならないと思いますので、
長くなりますけどごめんなさい。

前回の数式はOFFSET関数とMATCH関数で1列目のA2～A100セルを基準に何列右側の列を参照するか？
というようにしていました。
MATCH関数で「参照の型」を完全一致にしていますので、Sheet2のA1セルの値と
Sheet1の1行目の日付の値が完全一致しなければ当然エラーになります。

というわけで、数式を変更してみたいと思います。
前回の表をそのまま使わせてもらいます。
条件として、AからAF列まであり、左から1日・2日・3日・・・31日　になっている。
そして、Sheet2のA1セルには日付の数値のみを入力。

Sheet2のA3セルに
=IF(COUNT(OFFSET(Sheet1!$A$2:$A$100,,$A$1))<ROW(A1),"",SMALL(OFFSET(Sheet1!$A$2:$A$100,,$A$1),ROW(A1)))
（配列数式ではありません。）

B3セルに
=IF(A3="","",INDEX(Sheet1!$A$2:$A$100,SMALL(IF(OFFSET(Sheet1!$A$2:$A$100,,$A$1)=A3,ROW($A$1:$A$99)),COUNTIF($A$3:A3,A3))))
（これは配列数式ですので、前回同様　Shift＋Ctrlキーを押しながら、Enterキーで確定です。）

そして、範囲指定の問題ですが、数式を参照し順を追って説明したほうが良いと思います。
まず、最初の数式
=IF(COUNT(OFFSET(Sheet1!$A$2:$A$100,,$A$1))<ROW(A1),"",SMALL(OFFSET(Sheet1!$A$2:$A$100,,$A$1),ROW(A1)))
についてですが
前半部分の　IF(COUNT(OFFSET(Sheet1!$A$2:$A$100,,$A$1))<ROW(A1),"",　は
Sheet1のA2～A100セルを基準に、Sheet2のA1セルに入力した値だけ右側の列内で、
数値の数がオートフィルで下へコピーした行数より少ない場合はその行以降は空白にしなさい！
という単にエラー処理の数式です。
ここで　ROW(A1)=1　のことですのでこれをオートフィルで下へコピーすると
ROW(A2)=2・ROW(A3)=3・・・という具合に数値が増えていきます。
（別にROW(A1)でなくてもROW(B1))としても同じことになります）

後半部分の　SMALL(OFFSET(Sheet1!$A$2:$A$100,,$A$1),ROW(A1)))　に関しては
OFFSET関数は先ほど説明した通りで、その中の小さい順に1行目から表示するようにしています。
SMALL（○○,ROW(A1))　は小さいものから1番目、これをオートフィルで下へコピーすると
ROW(A1)部分がROW(A2)となりますので、小さいものから2番目・・・という感じです。

これでSheet2のA列の数式に関しては理解いただけたと思います。

次に、=IF(A3="","",INDEX(Sheet1!$A$2:$A$100,SMALL(IF(OFFSET(Sheet1!$A$2:$A$100,,$A$1)=A3,ROW($A$1:$A$99)),COUNTIF($A$3:A3,A3))))
に関してですが、
INDEX関数の範囲指定はSheet1のA2～A100にしています。
そして、配列数式でOFFSET関数を利用し、その中のSheet2のA列と一致するものの行番号の小さい順に
表示させているのがB列の数式になります。
ここで配列数式の場合、気をつけないといけないのは
INDEX関数で範囲指定した行数と　SMALL(IF(OFFSET(Sheet1!$A$2:$A$100,,$A$1)=A3,ROW($A$1:$A$99))
部分の　($A$1:$A$99)　の行数を一致させるということです。
INDEX関数ではSheet1の名前列の2行目から範囲指定していますが、その中の何行目を表示するか？
ということになりますので、Sheet1のA2セルが1行目・A3セルが2行目・・・と1行ずつずれます。
もし数式を　SMALL(IF(OFFSET(Sheet1!$A$2:$A$100,,$A$1)=A3,ROW($A$2:$A$100))
としてしまうと、結果が1行ずれてしまいます。

最後にCOUNTIFの部分を説明しようと思ったのですが、入力文字数に限界が来ていますので、
この程度で終わりにします。

長々と失礼しましたm(__)m

お礼

とても詳しいご説明ありがとうございました。
すぐにご回答いただきありがとうございました。

補足いただいた内容で数式の内容がわかりました。

ありがとうございました。
また何かありましたら、ご教授よろしくお願いいたします。

回答No.2

tom04

No.1です！
たびたびごめんなさい。

前回の式ですと、仮に1が2個・2が2個・・・のように複数の数値が複数あった場合はエラーになりました。

B3セルの数式を↓のように訂正してください。

=IF(COUNT(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$AF$1,0)))<ROW(A1),"",INDEX(Sheet1!$A$2:$A$100,SMALL(IF(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$AF$1,0))=A3,ROW($A$1:$A$99)),COUNTIF($A$3:A3,A3))))

今回も配列数式になってしまいますので、
Shift＋Ctrlキーを押しながらEnterキーで確定です。

前回書き忘れましたが、数式はSheet1の100行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

検証もしないで投稿してごめんなさいね。m(__)m

補足

tom04さん、ありがとうございます。
思っていたものが作成できそうです。感謝します。

補足として教えていただきたいことが3つあります。
どうぞご教授いただけませんでしょうか。

(1)範囲指定は、現在１００になっていますが、名前の部分の範囲指定でいいのでしょうか。

(2)各日付に(1)や(4)といった特殊数字も入れていますが、反映可能でしょうか。

(3)１５日のみなぜか、「セル範囲が無効のエラー」になりただしく表示されません。何か対処方法ありませんか？

数式まで教えていただきありがとうございます。
上記の件、よろしければご教授ください。
どうぞよろしくお願いいたします。

回答No.1

tom04

こんばんは！
直接の回答にはならないかもしれませんが・・・

質問通りだとするとSheetの数が30Sheet以上になるわけですよね？

作業としてはかなり大変になりますので、一つの案として提案します。

↓の画像のようにSheet1が入力用のSheetで、Sheet2のA1セルに
日付（シリアル値にはしていません、単なる数値にしています。）を入力すると
その日のデータが番号の昇順に表示される方法です。

Sheet2のA3セルに
=IF(COUNT(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$AF$1,0),,1))<ROW(A1),"",SMALL(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$AF$1,0),,1),ROW(A1)))

B3セルに
=IF(COUNT(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$AF$1,0)))<ROW(A1),"",IF(COUNTIF(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$AF$1,0)),A3)>1,INDEX(Sheet1!$A$2:$A$100,SMALL(IF(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$AF$1,0))=A3,ROW($A$1:$A$99)),ROW(A1))),INDEX(Sheet1!$A$2:$A$100,MATCH(A3,OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$AF$1,0)),0))))

これは配列数式になってしまいますので、この画面から直接コピー＆ペーストしただけではエラーになると思います。

B3セルに貼り付け後、F2キーを押す、またはB3セルでダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。

最後のA3・B3セルを範囲指定し、B3セルのフィルハンドルで下へコピーすると
画像のような感じになります。

この程度しか思いつきませんが
他に良い方法があれば読み流してくださいね。m(__)m