『3枚のシートから1つのデータの抽出のようなことがしたいのですが』（関数初心者） 介護施設のシフト表・利用者表・送迎時間表　の3枚のシートがあります。 シフト表の例えば3月15日をクリックするとその日のシフトや利用者名送迎時間などのタイムスケジュールがわかるように出来ないものでしょうか？本を参考にいろいろ考えてもみたのですが 関数に関しての知識があまりないのでどうかご指導いただけませんでしょうか？ シフト表 　　　１２３４５６７・・・・・ 田中　早日遅　早早日 伊藤　遅日早早　夜明 鈴木　日早夜明　日遅 利用表 　　　１２３４５６７・・・ 青木　○○○　○○○ 野田　○　○○○○○ 本田　　○○○○　○ 送迎表 青木　8:10 16:30 野田　8:25 16:50 本田　8:45 17:20 こんな表になっています。日ごとに利用者が違います。人によって送迎時間が違っており 毎日ドタバタになってしまうので日ごとに 　　　　7:00 8:00 9:00 10:00 11:00 12:00 田中　−−−−−−−−−−−−−−−−−−−−−− 伊藤　　　　　　　　　　　　　　　　−−−−−−− 鈴木　　　　　　　　　−−−−−−−−−−−−−− 　 青木　　　　　8:10 16:30 野田　　　　　　8:25 16:50 こんな感じのタイムスケジュールを作りたいのですがどうでしょうか。 　 　 　　 ---- これを見る限りでは…シフト表のシートは関係なさそうですね(^^ゞ まず日付は何処にあるのでしょう？もう少し詳しく書いてもらわないと回答つかないと 思います！！ (MJ12) ---- シフト表は 早・日・遅・夜 がそれぞれどの時間帯を表しているのか 知らない人には作れませんね。 送迎表は、日付が無いみたいですが ○○さんはいつでもこの時間 と言う事なのでしょうか？ (HANA) 説明足らずですいません。1〜7の数字が日付です。 シフト表の早・日・遅・夜は早番・日勤・遅番・夜勤となっています。 送迎表は毎回この時間と決まっています。 宜しくお願いいたします。 ---- ＞シフト表の早・日・遅・夜は早番・日勤・遅番・夜勤となっています。 それは。。。まぁ　そうでしょうね。 それで、 　早番は　何時から何時までで 　日勤は　何時から何時までで 　遅番は　何時から何時までで 　夜勤は　何時から何時まで なんでしょう？ ＞田中　−−−−−−−−−−−−−−−−−−−−−− これは、該当する時間のセルに「−」を入れる　とか 色を塗る　とか　したいのですよね？ 因みに、この掲示板の使い方に関してですが 下の方に「コメント欄」が有りますので 返信はそちらから行って頂ければと思います。 (HANA) ---- まだ有りました。 ・７時から何時までの表にしますか？ ・一セルを何分として扱いますか？ (HANA) ---- ありがとうございます。 赤字の早番は7：00〜16：00 青字の早番は7：30〜16：30 緑字の早番は8：00〜17：00 黒字の早番は8：30〜17：30 日勤は9：00〜18：00 黒字の遅番は9：30〜18：30 青字の遅番は10：30〜19：30 赤字の遅番は11：30〜20：30 表は7：00〜21：00の表で ーセルは３０分単位でお願いいたします。 どうかどうか宜しくお願いいたします。 ---- 数式でセルの値を参照、参照した物を比較　等は出来ますが 文字の色を取得する事等は困難です。 ＞赤字の早番は7：00〜16：00 青字の早番は7：30〜16：30 緑字の早番は8：00〜17：00 ・・・ の様に文字の色を変えるのではなく 早1,早2,早3 の様に出来ませんか？ (HANA) ---- はいできます。シフト表はその当に変更しますので どうか「数式でセルの値を参照、参照した物を比較」を教えていただけませんか。 宜しくお願いいたします。 ---- ご提示の表は、それぞれA1セルから始まっているとします。 一つに纏めるシートのシート名を「一覧」にします。 シフト表のシートに関してはそのまま。 　但し、シフトの入力はフォントの色ではなく　入力値自体を変更して下さい。 利用表はAF列が31日で、それ以降は空いていると思いますので AG1に　=DAY(一覧!A1) AG2に =IF(INDEX(B2:AF2,$AG$1)="○",ROW(A1),"") として、必要行（A列の名前が入っている行数＋α）フィルドラッグして下さい。 一覧シートのAH1:AJ9に、シフト時間の対応表を作製して下さい。 [AH] [AI] [AJ] [1] シフト時間対応表 [2] 早1 7:00 16:00 [3] 早2 7:30 16:30 [4] 早3 8:00 17:00 [5] 早4 8:30 17:30 [6] 日 9:00 18:00 [7] 遅1 9:30 18:30 [8] 遅2 10:30 19:30 [9] 遅3 11:30 20:30 一覧シートは A1セルに表示させる日にちを入力 A列に名前、B列にシフトを表示して C列から7:00〜 一セル30分とします。 [A] [B] [C] [D] [E] [F] [G] ・・・ [AH] [AI] [AJ] [ 1] 3月1日 7:00 8:00 9:00 シフト時間対応表 [ 2] 田中 早1 早1 7:00 16:00 [ 3] 伊藤 遅1 早2 7:30 16:30 [ 4] 鈴木 日 早3 8:00 17:00 [ 5] 早4 8:30 17:30 ： ： [20] 利用者 [21] 青木 8:10 [22] 野田 8:25 [23] [24] [25] A2=IF(シフト表!A2="","",シフト表!A2) B2=IF(A2="","",IF(INDEX(シフト表!$B2:$AF2,DAY($A$1))="","",INDEX(シフト表!$B2:$AF2,DAY($A$1)))) 必要行フィルドラッグ C2セルの条件付書式の設定で 数式が▼　 =AND((VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13)<=COLUMN(A1),COLUMN(A1)<(VLOOKUP($B2,$AH$2:$AJ$9,3,FALSE)*48-13)) お好みの書式を設定 C2セルをコピーして、AE列までの必要範囲に貼り付け A21=IF(COUNT(利用表!$AG$2:$AG$10) <2> <3> <4> <5> <6> <7> <8> <9> <10> <11> <12> [1] 0:00 0:30 1:00 1:30 2:00 2:30 3:00 3:30 4:00 4:30 5:00 5:30 [2] 0 1 2 3 4 5 6 7 8 9 10 11 ←「0:00」を0としてカウント [3] -13 -12 -11 -10 -9 -8 -7 -6 -5 -4 -3 -2 ←2行目の値-13 <13> <14> <15> <16> <17> <18> <19> <20> <21> <22> <23> <24> <25> [1] 6:00 6:30 7:00 7:30 8:00 8:30 9:00 9:30 10:00 10:30 11:00 11:30 12:00 [2] 12 13 14 15 16 17 18 19 20 21 22 23 24 ←「0:00」を0としてカウント [3] -1 0 1 2 3 4 5 6 7 8 9 10 11 ←2行目の値-13 ↑ 7:00の所が「1」になる (HANA) ---- ---- 　　こちらで続ける様なので 　　[20110312120204]『色付きセルのがいくつあるか出したい』（関数初心者） 　　を移動してきました。(HANA) ---- ---- 『色付きセルのがいくつあるか出したい』（関数初心者） つい先ほどまでいろいろ質問しておりました。それは解決したのですが・・・シフト表から違うシートの該当時間に色をつける（条件付書式を使い）様にしました。そのシートで11：00に何人勤務しているのか色付きセルを出したいのですがなぜか=UFClrCntccx(C2:C17)この関数は無効な関数となってしまいます。マクロとかアドインなど触ってみたのですがうまくいきません。どなたかご指導下さい。色付きセルが無理ならば条件付を色と数字にしたりできませんか？ -------------------------------------------------------------------------------- ↓と同じシートですか？ [20110310073828]　『3枚のシートから1つのデータの抽出のようなことが』（関数初心者） でしたら、条件付書式で色を付けるよりも セルに何か表示して、それを集計した方が良いと思います。 セルに入力が無い必要は、無いのですよね？ 例えば、現在条件付書式の設定の条件部分に使用している式取り出して IF関数の条件部分に使って　何か表示する様にします。 =IF(AND((VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13)<=COLUMN(A1),COLUMN(A1)<(VLOOKUP($B2,$AH$2:$AJ$9,3,FALSE)*48-13)),"●","") これで、該当のセルに「●」が表示されるので　COUNTIF関数で「●」の個数を数えます。 　=COUNTIF(C2:C17,"●") 条件付書式の設定の方は 　[セルの値が▼]　[次の値に等しい▼]　["●" ] に変更です。 (HANA) ---- ---- ---- (HANA)様 助けてください・・・ 色付きセルの個数を計算するのに●をつけて数えることは出来たのですが シフト表の空欄にエラーが出てしまいました。どうしたらよいのでしょうか？ A B C D 7:00 (1) 田中早1● (2) 佐藤早2 (3) 伊藤日 (4) #N/A (5) 木村遅2　　　　 ---- うっかりしてました。 B2セルが「""」の時にエラーに成っていると思うので B2セルが「""」の時は「""」を返す様にしてみて下さい。 =IF($B2="","",IF(AND((VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13)<=COLUMN(A1),COLUMN(A1)<(VLOOKUP($B2,$AH$2:$AJ$9,3,FALSE)*48-13)),"●","")) (HANA) ---- (HANA)様 =IF($B2="","",IF(AND((VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13)<=COLUMN(A1),COLUMN(A1)<(VLOOKUP ($B2,$AH$2:$AJ$9,3,FALSE)*48-13)),"●","")) この関数をフィルドラッグするとC5のセルには =IF($B5="","",IF(AND((VLOOKUP($B5,$AH$2:$AJ$9,2,FALSE)*48-13)<=COLUMN(A4),COLUMN(A4)<(VLOOKUP ($B5,$AH$2:$AJ$9,3,FALSE)*48-13)),"●","")) となりますが =IF($B5とかCOLUMN(A4),この数字が変わっても大丈夫なのでしょうか？ 関数の意味が理解できていないので申し訳ありません。 教えてください。 後、一列C6だけフィルドラックでエラーになるのですがどうしてかわからないのです。 ---- (HANA)様 度々すいません。 後、一列C6だけフィルドラックでエラーになるのですがどうしてかわからないのです。 これは解決できました。 こんなことをしたいのですが・・・ 一覧のCの前に１列挿入をしてCとDを結合して7:00としてEとFを結合 して8:00とする事は出来ませんか？その場合の関数はどうなるのか教えてください。 本当にお手数お掛けいたしますが 何卒お願いいたします。 ---- 済みません、ちょっとかり出されてました。。。 ＞=IF($B5とかCOLUMN(A4),この数字が変わっても大丈夫なのでしょうか？ 大丈夫です。 少し簡単な例を書いてみます。 C:L列に数式を入れて行きます。 [A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [1] 開始 終了 1 2 3 4 5 6 7 8 9 10 [2] 2 8 ● ● ● ● ● ● [3] 5 9 ● ● ● ● C1=COLUMN(A1)　・・・・・・・・・・・・・→L1までフィルドラッグ C2=IF(AND($A2<=C$1,C$1<$B2),"●","") ・・→L3までフィルドラッグ この状態で、各セルに「●」が付くのはわかりますか？ C1:L1の値と、A,Bの値を比べて A,Bの値の間に 1行目の値が有る場合に「●」を表示します。 例えば、H1セルに入っている式は =COLUMN(F1) H3セルに入っている式は H3=IF(AND($A3<=H$1,H$1<$B3),"●","") ですから、 ~~~ ~~~ この部分を、H1セルに入っている式と置き換えると =IF(AND($A3<=COLUMN(F1),COLUMN(F1)<$B3),"●","") に成りますね。 C2セルの式で、C1セルに入っている式と該当部分を置き換えた物は =IF(AND($A2<=COLUMN(A1),COLUMN(A1)<$B2),"●","") これになります。 $A2 とか、A1 の部分が変わっていますよね？ ここは寧ろ、変わってくれないといけない所です。 ＞一覧のCの前に１列挿入をしてCとDを結合して7:00 −−→C1:D1セルを結合して、そのセルに「7:00」と入力する と言う事ですよね？ 数式では、その位置のセルの値を見ている訳ではないので 1行目のセルの状態は変更して貰えば良いです。 7:00がC列以外の列に成る場合に、数式の変更が必要に成ります。 やってみて下さい。 (HANA) ---- (HANA)様 やってみました。 C1:D1セルを結合して、そのセルに「7:00」と入力したら A 　　 B 　　C D 　　7:00 (1) 田中早1　　● (2) 佐藤早2 (3) 伊藤日 この状態になってしまいました。 出来たらD1に●が出る様になりませんか？ CとDの間にある線を7:00と見た表にしたいのですが・・・ こんな説明でわかりますか？ 　　 ---- ん？D列が7:00のセルですか？ E列が7:30,F列が8:00・・・？ C列が6:30(もしも6:30が有ったら　ですが。。。） 数式をこちらからコピーして、C2に貼り付けて貰ったと思いますが D2に貼り付けてフィルドラッグするとどうですか？ C列に「●」が付くことは無いですよね？ (HANA) ---- 因みに、●の一を一つずらしたいだけなので C2に入っている↓の式の ＞=IF($B2="","",IF(AND((VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13)<=COLUMN(A1),COLUMN(A1)<(VLOOKUP($B2,$AH$2:$AJ$9,3,FALSE)*48-13)),"●","")) 「-13」部分を -1 とか +1 とかしてみて下さい。 (正解は +1 で、「-12」で良いハズです。） <13> <14> <15> <16> <17> <18> <19> <20> <21> <22> <23> <24> <25> [1] 6:00 6:30 7:00 7:30 8:00 8:30 9:00 9:30 10:00 10:30 11:00 11:30 12:00 [2] 12 13 14 15 16 17 18 19 20 21 22 23 24 ←「0:00」を0としてカウント [3] -1 0 1 2 3 4 5 6 7 8 9 10 11 ←2行目の値-13 ↑ 6:30の所を「1」にしたい 2行目の値 -12 をすると、6:30の所が「1」に成ります。 (HANA) ---- (HANA)様 本当に本当にありがとう御座いました。 上司から頼まれ関数が解からないので困り果てておりました・・・ とても助かりました。 上司にもこの「エクセルの学校」の（HANA)様のこと報告いたしました。 今後また助けていただく事もあろうかと思いますが またその際はよろしくお願いいたします。 今から出来上がった一覧を上司に提出してきます。 -------------------------------------------------------------------------------- ---- ＞今から出来上がった一覧を上司に提出してきます。 最後の関門ですね。 無事に突破出来ると良いですね。＾＾ もしもレイアウトが変更になった場合は 力業になりますが、以下の様にやってみて下さい。 １．各シート（レイアウトが変更になるシート）で行や列やセルの挿入・削除を行う。 ２．一覧シートの数式を確認して、「$」が付いている様なセル番地は、目的の場所が参照出来ているか確認 ３．C列の数式の最初のセルのCOLUMN関数の参照先を「A1」に変更してフィルドラッグ 　　1行目の時間と、関数の戻り値がずれている場合は　数式内の「-13」の部分で 　　ずれている分だけ調整 いくつか表が有りますが、表全体がどこかへ移動する場合は 上の様にやってもらえると良いと思います。 一つの表の中に列を挿入して・・・　とかに成ると 他の部分の変更も必要に成ってくる場合が有ります。 その時は、まじめに数式を読んでみて下さい。 (HANA)