[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『3枚のシートから1つのデータの抽出のようなことがしたいのですが』（関数初心者）
シフト表
１２３４５６７・・・・・ 田中 早日遅 早早日 伊藤 遅日早早 夜明 鈴木 日早夜明 日遅
利用表
１２３４５６７・・・ 青木 ○○○ ○○○ 野田 ○ ○○○○○ 本田 ○○○○ ○
送迎表
青木 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～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)<ROW(A1),"",INDEX(利用表!$A$2:$A$10,SMALL(利用表!$AG$2:$AG$10,ROW(A1)))) 必要行フィルドラッグ
C21=IF($A21="","",IF(INT(VLOOKUP($A21,送迎表!$A$1:$C$10,2,FALSE)*48-13)=COLUMN(A1),VLOOKUP($A21,送迎表!$A$1:$C$10,2,FALSE),
IF(INT(VLOOKUP($A21,送迎表!$A$1:$C$10,3,FALSE)*48-13)=COLUMN(A1),VLOOKUP($A21,送迎表!$A$1:$C$10,3,FALSE),"")))
AE列までの必要範囲にフィルドラッグ
(HANA)
あれ？本当に「－－－」を表示ですか？
＞C2セルの条件付書式の設定で～ の所をやってもらうと、セルに色がつくと思いますが つかないですか？
＞利用者の送迎時間 も出ませんか？
レイアウトは同じにして、数式もこちらからコピペしてもらっていますか？
もしもデータの配置が違う場合は、私が載せたものに合わせたデータを用意し 数式の戻り値の確認をしてみてください。
B2セルに「早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)) を入れてフィルドラッグした時に、目的のセル（－を表示したいセル）に TRUE それ以外のセルに FALSE が表示されるか？
２． C3セルに =VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13 C4セルに =VLOOKUP($B2,$AH$2:$AJ$9,3,FALSE)*48-13 を入れた時、戻り値はどうなっているか？
３． C22セルに =VLOOKUP($A21,送迎表!$A$1:$C$10,2,FALSE) C23セルに =INT(C22*48-13) C24セルに =VLOOKUP($A21,送迎表!$A$1:$C$10,3,FALSE) C25セルに =INT(C24*48-13) の戻り値はどうなっているか？
教えてください。
(HANA)
お手数お掛けして申し訳ありません。 利用者の時間は出来ましたがシフトの時間が条件付書式の設定が出来ないのですが 色も付きません。 条件付書式に入力した関数を見ていただこうかと思ったのですが コピーが出来ず・・・なので教えていただいた数式をコピペも出来ないので 教えていただいた通りに入力しては見たのですが他に書式設定など何か設定しなくては いけないのでしょうか？ 本当にお手数お掛けいたしますが何卒よろしくお願いいたします。
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)) を入れてフィルドラッグした時に、目的のセル（－を表示したいセル）に TRUE それ以外のセルに FALSE が表示されるか？ これはC2のセルはTRUEでそれ以外のセルもTRUEです。
２．
C3セルに =VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13 C4セルに =VLOOKUP($B2,$AH$2:$AJ$9,3,FALSE)*48-13 を入れた時、戻り値はどうなっているか？ これは全てのセルに１と表示されました。シフトの該当時間以外のセルにもです。
条件付書式に入力した数式がコピペできたので見ていただけませんか。
=AND((VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13)<=COLUMN(A1),COLUMN(A1)<(VLOOKUP ($B2,$AH$2:$AJ$9,3,FALSE)*18-13))
よろしくお願いいたします。
(HANA)様 いろいろご指導頂きありがとう御座いました。 ｃtrl+ｃ ctrl+ｖを使いコピペが出来ました。 色もつきました。 何が間違っていたのか見直してみます。 本当に長い間お付き合い頂き何から何まで ありがとう御座いました。 （関数初心者）
どちらも解決出来た様で良かったです。
そう言えば、条件付書式の設定の部分では 右クリック→貼り付け が出来なかったですね。。。 基本的に私は Ctrl + V でやっているので うっかりしてました。
次の質問が有るみたいですね。 [20110312120204]『色付きセルのがいくつあるか出したい』（関数初心者）
割と密接に関係しているので 続きに書いて貰っても良かったと思いますが。。。
＞何が間違っていたのか見直してみます。 条件付書式に設定された式が =AND((VLOOKUP($B2,$AH$2:$AJ$9,2,FALSE)*48-13)<=COLUMN(A1),COLUMN(A1)<(VLOOKUP ($B2,$AH$2:$AJ$9,3,FALSE)*18-13)) なら、2つ目のVLOOKUP関数に掛け算しているのか 18 に成ってます。 ここは、48を掛けます。
この48の出所は・・・・ エクセルは日付・時刻をシリアル値で管理しています シリアル値は 一日を 1 として計算します。 12時 は、1日の半分なので、シリアル値で 0.5 です 0.5に24を掛けると（0.5*24) 12 が求められます。
今回は、一列が30分なので さらに *2 をして 12*2 = 24 0:00→0番目 0:30→1番目 1:00→2番目 1:30→3番目と数えて行くと 24番目が 12:00 のセルになります。
今回は7:00が0番目なので -14 をすれば良いのですが 7:00→1番目 と数えたいので -13 に成っています。 <1> <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)
助けてください・・・ 色付きセルの個数を計算するのに●をつけて数えることは出来たのですが シフト表の空欄にエラーが出てしまいました。どうしたらよいのでしょうか？
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)
=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だけフィルドラックでエラーになるのですがどうしてかわからないのです。
度々すいません。 後、一列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)
やってみました。 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)様のこと報告いたしました。
今後また助けていただく事もあろうかと思いますが
またその際はよろしくお願いいたします。
今から出来上がった一覧を上司に提出してきます。
＞今から出来上がった一覧を上司に提出してきます。 最後の関門ですね。 無事に突破出来ると良いですね。＾＾
もしもレイアウトが変更になった場合は 力業になりますが、以下の様にやってみて下さい。
１．各シート（レイアウトが変更になるシート）で行や列やセルの挿入・削除を行う。 ２．一覧シートの数式を確認して、「$」が付いている様なセル番地は、目的の場所が参照出来ているか確認 ３．C列の数式の最初のセルのCOLUMN関数の参照先を「A1」に変更してフィルドラッグ 1行目の時間と、関数の戻り値がずれている場合は 数式内の「-13」の部分で ずれている分だけ調整
いくつか表が有りますが、表全体がどこかへ移動する場合は 上の様にやってもらえると良いと思います。
一つの表の中に列を挿入して・・・ とかに成ると 他の部分の変更も必要に成ってくる場合が有ります。 その時は、まじめに数式を読んでみて下さい。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.