『条件付き書式設定、色のカウント』（和代） [[20141023183124]] http://www.excel.studio-kazu.jp/wiki/kazuwiki/201410/20141023183124.txt ↑ 私が閲覧している最中にインターネットエクスプローラーの更新ボタンを 押したタイミングで急に消えてしまいました。 特に変な操作はしていないので、全く原因は分かりません。 以下、取りあえず復元できる部分を貼っておきます。(半平太) ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー 『条件付き書式設定、色のカウント』（和代） いつもお世話になります。 以前アルバイトのシフトでお世話になり有難うございました。 完成しましたフォームを担当社員(当初お互いに相談し合って作成したフォームですが）に使って頂いていますが、担当社員から下記質問がありました。 現行のセルの位置をお伝えしその後にセルの位置の意味、質問を提示致しますので よろしくお願いいたします。 Ｑ Ｒ S T U V Ｗ Ｘ Ｙ Ｚ AA 〜 ＢＺ６ 5 任意の手入力↓ 実際の人数→ 1 1 6 休憩時間 休憩のスタート 名前 出勤 退出 8:00 8.25 8.5 18:00 9 9.25 〜 22:00 7 1 12 上田 8:00 17:00 8 0.75 11 山本 9:00 16:00 9 浜本 50 塩谷 10:45 17:25 Ｓ７からＳ５０迄全員の名前が表示されています。 ?@Ｖ５からＢＺ５迄、各時刻の黄色の数をカウントする下記の関数を入れています。 「=SUMPRODUCT(((0&$T$7:$T$50)-0.00002=$T7,V$6<$U7)」 ?B例えば、Ｒ７に１２と入力すればＱ７の１が１２：00のセルに入るように下記関数を 入れています。 「=IFERROR(INDEX($Q$7:$Q$50,MATCH($S15&Z$6,INDEX($F$7:$F$50&$R$7:$R$50,0),0)),"")」 F7より下には、別シートから転送された当日の出勤者の人の名前が表示されています。 ここから質問です。 １、上記?AでＲ７に１２と入力すればＱ７の１が１２：００のセルから0.25づつ右側のセルに４つ分黄色以外の色を付ける方法？ 　（Ｒ列の入力時間に修正があって他の時間に変更すれば変更前のセルには黄色の色に 　　戻ります） ２、上記?@の式を黄色の合計から黄色以外の色をひいた残を計算したいのです。 　　（各時刻で休憩を除けば本来何人のアルバイトが出勤しているのかを計算 　　　するためなのです） 　以上　宜しくお願い致します。 ＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞ ---- １．時間・時刻の単位がハチャメチャな気がするんですけど。 　休憩スタートの 12 とか 11 って生データなのですか？　 　それとも 12:00 とか 11:00 なのですか（もしくはそれをセルの書式で12や11に見せているのですか？） 　6行目の例にある8.25とか8.5ってなんですか？ 生データなのですか？　8:15とか8:30の間違いじゃないのですか？ 実体は24倍も違うものなのでハッキリさせないと先に進めません。 ＞Ｒ７に１２と入力すればＱ７の１が１２：００のセルから0.25づつ右側のセルに４つ分黄色以外の色を付ける方法？ ２．黄色以外の色のセルが4つになるのであろうと思いますが、 　「1が」はどうなっちゃうのですか？（4つのセルに1が入るのですか？） ３．この表は、上述の休憩情報以外は何もなく、ただ色が付いているだけの表なんですか？ （半平太） 2014/10/23(木) 22:06 ---- 半平太様 おはようございます。 お返事有難うございました。 質問の答えをお伝え致します。 1,時間・時刻の単位がハチャメチャな気がするんですけど。 −−私の表示が間違っていました。すみません。 時間の表示は、すべて生で８：２５ではなく８．２５です。（８時１５分の意味） セル書式は、数値で小数点２桁にしているだけです 休憩スタートの 12 とか 11 って生データなのですか？　 ‐‐‐ 生データです。その人が何時から休憩をとるかがわかるように表示するだけです。 １２：００　でもいいのですが、セル幅を狭くして全体を見やすくするために１２とか１１に しているだけです。 6行目の例にある8.25とか8.5ってなんですか？ ‐‐‐時間帯で生データです。 ８．２５は８．２５時間という意味です、８．５は８.５時間という意味で、８：１５とか８：３０（８時１５分、８時３０分）にはしていません。 勤務時間＝退出時刻−出勤時刻　の計算は　単に数値で計算しています。 ８時からの出勤で退出が８．２５とかは実際にありえませんが、０．２５時間刻みにしているだけです。 契約では、勤務時間は、３０分単位にしていますが、勤務時間が６時間以上になりますと 休憩時間を与えなければなりません。０．７５時間の休憩ですと０．５時間単位での表では合わない為に、０．２５時間区切りで表示しています ２．黄色以外の色のセルが4つになるのであろうと思いますが、 　「1が」はどうなっちゃうのですか？（4つのセルに1が入るのですか？） 当初の私からの質問：上記AでＲ７に１２と入力すればＱ７の１が１２：００のセルから0.25づつ右側の 　　　　　　　　　　セルに４つ分黄色以外の色を付ける方法？ これを下記の質問と同時に説明しなおします。 　黄色以外の色は一色です。 「1が」はどうなっちゃうのですか？（4つのセルに1が入るのですか？） −−−質問以前には休憩時間のスタート時刻に１を表示するだけでしたが、担当者から休憩時間の範囲に色を 塗って欲しいとの要望でしたので、１の表示は不要になります。 ｑ７の１は、休憩時間が１時間を意味します、セルが０．２５時間単位ですから４倍のセル分に黄色以外の色（一色）をつけると言う意味です。 ３．この表は、上述の休憩情報以外は何もなく、ただ色が付いているだけの表なんですか？ 　はい、そうです。ただし色は条件付きです。 宜しくお願い致します。 （和代） 2014/10/24(金) 06:07 ---- ＞Ｖ６からＢＺ６迄、8：00から22：00迄0.25刻みで表示されています。 すると、V6セルは「8:00」じゃなく、単なる「8」ですね。 また　出勤・退出の時刻データも、「17:00」とかじゃなく、単なる「17」ですね。 ＞F7より下には、別シートから転送された当日の出勤者の人の名前が表示されています。 F列とS列の名前は順番が違うのですよね？ なぜ、休憩時間データをF列の順に合わせるのですか？ S列の名前の順に合わせて入力すれば、後の処理が簡単になると思うのですけど、。。 (F列に合わせる方が、上から詰めて入力が出来るので楽なんでしょうか？） ＞＞３．この表は、上述の休憩情報以外は何もなく、ただ色が付いているだけの表なんですか？ ＞ 　はい、そうです。ただし色は条件付きです。 数式で意味のある値を出し、セルの書式でそのデータを見えないようにすれば、 条件付きで色付けするとか、実際の人数の算出なんかが楽になると思うのですが、 そう云うアイデアはどんなもんでしょうか？ ついでですけど、時間データが0.25刻みなら、小数演算誤差対策は不要です。 （半平太） 2014/10/24(金) 08:30 ---- 半平太様 おはようございます。 ＞Ｖ６からＢＺ６迄、8：00から22：00迄0.25刻みで表示されています。 すると、V6セルは「8:00」じゃなく、単なる「8」ですね。---はい また　出勤・退出の時刻データも、「17:00」とかじゃなく、単なる「17」ですね。---はい ＞F7より下には、別シートから転送された当日の出勤者の人の名前が表示されています。 F列とS列の名前は順番が違うのですよね？---はい、Ｆ列は当日の出勤者の名前です、Ｓ列は全員の 名前です。 なぜ、休憩時間データをF列の順に合わせるのですか？ S列の名前の順に合わせて入力すれば、後の処理が簡単になると思うのですけど、。。 　---違うシート（シート名：入力表）から転送されたデータは実際には、Ｂ列からＲ列まである為です。 Ｂ列(月）Ｃ（日）Ｄ（曜日）Ｅ（コードＮｏ）Ｆ(名前）Ｇ（出勤）Ｈ（退出）Ｉ（労働時間）Ｊ（普通時間）Ｋ（残業時間）Ｌ（普通時給）Ｍ（残業時給）Ｎ（普通金額）Ｏ（残業金額）Ｐ（合計金額） Ｑ（休憩時間）ここまでがシート名：入力表のｲﾝﾌﾟｯﾄデータを横列にならべたものです。 Ｒ列(休憩のスタート時刻）は、出勤者全員が決まった後に手で入力します。 （出勤者全員の勤務体制を見た後で休憩時間の取る時刻をバランスよくするため） 数式で意味のある値を出し、セルの書式でそのデータを見えないようにすれば、---どういうイメージか つかめません。具体的にお教え願えないでしょうか？ 条件付きで色付けするとか、実際の人数の算出なんかが楽になると思うのですが、---楽になるのは賛成 ですが、実際にどのようにすれば、どのような結果になるのでしょうか？ 出来うるものであれば、簡単にしたい事が一番ですが、固守しているのではなく、知恵が出ないだけです。 色を塗ると出勤者がいない所、休憩がダブっているところ等が解りやすいと思ったからです。 以上よろしくお願いいたします。 （和代） 2014/10/24(金) 12:14 ---- ＞転送されたデータは実際には、Ｂ列からＲ列まである為です。 そう云う状況でしたか、納得です。 ーーーーーーーー ＞ 数式で意味のある値を出し、セルの書式でそのデータを見えないようにすれば、---どういうイメージか ＞つかめません。具体的にお教え願えないでしょうか？ あくまで例えばですけど(本当にやっておりません。実例は後述します。 　勤務時間帯には1、休憩時間帯には2、その他は0とすれば、 　実際の人数は、Countif関数で１のセルを数えればいいし、 　条件付き書式は、そのセルが１なら黄色、2なら別の色を設定すればよくなります。 　そこで問題となるのが、その数値が見えるのが鬱陶しいと云うことなんですが、 　これには、セルの書式(表示形式）をユーザー定義で　;;;;　と設定して見えなくさせればいい。 ーーーーーーーー 以下の実例は 　勤務時間帯には半角スペース1つ、休憩時間帯には半角スペース2つ、その他は空白文字にする案です。 　これならセルの書式(表示形式）を設定する手間はないです。どれも目に見えない文字なので。 　実際の人数は、Countif関数で「半角スペース１個」のセルを数えればいいですし、 　条件付き書式は、そのセルが半角スペースが１個なら黄色、半角スペースが2個ならなら別の色を設定します。 　あと、毎回休憩時間を考慮するのは煩雑な為、レイアウトのV,W列に休憩開始時刻、休憩終了時刻を挿入します。 　その2列が目障りでしたら、あとで非表示にしてください。 　※非表示にすると数式をクリアされても認識できなくなるので、シートの保護を利用して消されない工夫をしてください。 １．シートの数式 　(1) V7セル =IFERROR(INDEX($R$7:$R$50,MATCH($S7,$F$7:$F$50,0)),"") 　(2) W7セル =IFERROR(V7+INDEX($Q$7:$Q$50,MATCH($S7,$F$7:$F$50,0)),"") 　(3) X7セル =IFERROR(IF(AND(X$6>=$V7,X$6<$W7)," ",IF(AND(X$6>=$T7,X$6<$U7)," ","")),"") 　　　これを右方(CB7セルまで）コピー 　(4) V7:CB7を一括選択して、下方(56行目まで）コピー 　(5) X5セル =COUNTIF(X$7:X$50," ") 　　　これを右方(CB5セルまで）コピー ２．条件付き書式の数式（順位はどっちが先でも構いません） 　　黄色設定　=X7=" "　　 ←半角スペースが1個 　　休色設定　=X7=" "　　←半角スペースが2個 <結果図> 行 __F__ _P_ ____Q____ _R_ __S__ __T__ __U__ __V__ __W__ _X_ __Y__ _Z_ _AA_ _AB_ _AC_ _AD_ _AE_ _AF_ __AG__ _AH_ __AI__ 5 1 1 1 1 2 2 2 2 1 1 1 3 6 休憩時間 名前 出勤 退出 休始 休終 8 8.25 8.5 8.75 9 9.25 9.5 9.75 10 10.25 10.5 10.75 7 上田 1 12 上田 8 17 12 13 8 塩谷1 0.75 14 山本 9 16 10 10.75 9 山本 0.75 10 浜本 0 0 10 塩谷2 1 14 塩谷1 10.75 17.15 14 14.75 11 浜本 塩谷2 13 15 14 15 （半平太） 2014/10/24(金) 13:49 ---- 半平太様 私のつたない伝え方で、このような充分満足出来る立派なものを作成して頂き有難く思います。 下記データもデータ範囲に貼り付けをするのですよね（もうしましたが---） (1) V7セル =IFERROR(INDEX($R$7:$R$50,MATCH($S7,$F$7:$F$50,0)),"") (2) W7セル =IFERROR(V7+INDEX($Q$7:$Q$50,MATCH($S7,$F$7:$F$50,0)),"") ２．条件付き書式の数式（順位はどっちが先でも構いません） 　　黄色設定　=X7=" "　　 ←半角スペースが1個 　　休色設定　=X7=" "　　←半角スペースが2個 ・ＶＷ列はうまく行きました。 ・5行目のカウントと出勤の色、休憩の色は下記を除いてうまく行きました。 本当に嬉しく思いましたと同時に、その関数はまだ自分のものになっていませんが、覚えて活かしたいと 思います。色々なやり方がありますね。 　 下記２件がうまく行きませんでした。 １、5行目のカウントでうまくいかない列がありました。 　　それは、ＡＪ〜ＡＭ迄（正４、誤３）ＡＮ、ＡＯ（正４、誤１）ＡＰ（正４、誤２）ＡＱ（正４、誤３） 　　その他は、うまく行きました。 ２、名前、山本　出勤　９　　退出　１６　 　９時から出勤の色と休憩の色はうまく出ていますが、８時のところ（一つのセル）に出勤の色、黄色が 　ついています。なぜか解りません？ 以上お願い致します。 （和代） 2014/10/24(金) 17:00 ---- 半平太様 下記１、はうまく行っていました。私の勘違いでした。すみません。 １、5行目のカウントでうまくいかない列がありました。 　　それは、ＡＪ〜ＡＭ迄（正４、誤３）ＡＮ、ＡＯ（正４、誤１）ＡＰ（正４、誤２） 　　　ＡＱ（正４、誤３） 　 （和代） 2014/10/24(金) 17:29 ---- 半平太さま Ｓ７：Ｓ５０迄、全員の名前です。Ｓ７：ＣＢ５０の範囲で出勤（Ｔ７：Ｔ５０）と退出（Ｕ７：Ｕ５０）の データがない行を一挙に削除する方法を教えて頂けますか？ＶＢＡを使ってもいいですので−−− （出勤者を見やすくする為にです） この件は、上記が解決してから別件で質問をしようと思っていました。 上記内容も最初は、回答者の方に対して余分な事を書いてまどわさないようにと、結論の質問だけを伝えようと思っていましたが、こちらの説明不足と勘違いで色々説明をしていましたら、半平太様の理解の元、 私の思いにかなった表が出来ましたので、 別件で行の削除を色々考えて質門しようとするよりも、 理解して頂いている今の方がお伝えしやすいと思い質問しました。 すみませんが宜しくお願い致します。 （和代） 2014/10/24(金) 17:57 ---- 「さま」は「さん」でお願いします。私に「さま」は重すぎます。自分から言い出すのもなんですけど、、 現在、旨く行かないのはこの１点ですか？ ＞２、名前、山本　出勤　９　　退出　１６　 ＞ 　９時から出勤の色と休憩の色はうまく出ていますが、８時のところ（一つのセル）に出勤の色、黄色が ＞　ついています。なぜか解りません？ 9時からチャンと黄色になっていて、8時の１セルだけ黄色になることは普通ないので、 そのセルは元々黄色になっているか、数式が正しくないのでは？　と推測されます。 推測が正しければ、X7セルとX8セルの２セルをドラッグ選択して、 Ctrlキーを押しながら、Dキーを押すと上のセルが下にコピーされて 一件落着となる(ハズです）。 それで直らなければ、以前設定した条件付き書式が削除されていなくて、悪さをしている・・・かもです。 その場合は、X8セルの条件付き書式の設定状況を確認して、余計な設定があれば削除してください。 もし、これがピンポンなら、他のセルも心配なので、いったん条件付き書式を全て削除してから、 正しく再設定してください。 ＞Ｓ７：Ｓ５０迄、全員の名前です。Ｓ７：ＣＢ５０の範囲で出勤（Ｔ７：Ｔ５０）と退出（Ｕ７：Ｕ５０）の ＞ データがない行を一挙に削除する方法を教えて頂けますか？ＶＢＡを使ってもいいですので−−− ＞ （出勤者を見やすくする為にです） 削除したら、出勤していない人の名前が永久に消えちゃって、次回このシートを利用するとき困らないのですか？ 単にマクロで、出勤者を上の方に持って行き、その他の人はその下に並べればいいんじゃないですか？ 「S列の出勤者名」と「F列の名前」は完全に同じものになるのですか？ ※そうだとするとコード順に並べれば、同一名で行が一致するので、後の処理も楽になりますよね？ 　もっとも、出勤者を抽出するときは、まだF:Q列にデータが転送されていない段階だとそうも云えないですか？ 翻って「データが転送される」とは具体的にどうデータを持って来るのですか？　 そんなこともこちらには未だ分かっていないです。 （半平太） 2014/10/24(金) 19:10 ---- 半平太さんへ ８時のところ（一つのセル）に出勤の色、黄色がついています。なぜか解りません？ X7セルとX8セルの２セルをドラッグ選択して、 Ctrlキーを押しながら、Dキーを押すと上のセルが下に コピーされて 一件落着となる(ハズです）。−−−明日午後に会社に行ってから修正します。 削除したら、出勤していない人の名前が永久に消えちゃって、次回このシートを利用するとき困らないの ですか？−−−言われますように、このシート（シート名：給与集計）で削除すれば次回には消えた人の 名前が使えません。 これも私が後で自分でするつもりでしたが、このシートを基盤に、シート名：１日から３１日迄を作って います。シート名：給与集計で、ＶＢＡで何日のシートに貼り付けますか？とインプットボックスで問い かけをして、例えば１を入力してＯＫボタンをクリックすると１日のシートに飛ぶようにしています。 そこでシート名：１日のシート上で時刻が入っていない名前を削除しようと思ったのです。 後の質問は、会社に行ってから具体的に表を見てお伝えします。少しでも早く返答したかったので 記載しました。 （和代） 2014/10/24(金) 23:23 ---- 半平太さん こんにちは、 １、８時のところ（一つのセル）に出勤の色、黄色がついています。なぜか解りません？ について 　　---うまく行きました。有難うございます。 　　 　　式は隣のセルと同じ式（当然列のセル番地は変わりますが）で条件書式も同じでしたが、 　　言われます様に、いったん条件付き書式を全て削除してから、コピーし直しました。 　　 ２、削除したら、出勤していない人の名前が永久に消えちゃって、次回このシートを利用するとき困らない 　　　のですか？−−−上記でご説明しましたように、 　　　 　　　このシート(シート名：給与集計)を基盤にして、シート名：１日〜シート名：３１日迄を作って 　　　います。マクロボタンを押しますと、ＶＢＡで何日のシートに貼り付けますか？とインプット 　　　ボックスで問いかけをしています。、 　　　例えば１を入力してＯＫボタンをクリックするとシート名：１日　のシートに飛び貼り付けをします。 　　　（給与集計と同じ表の貼り付けです）　３１を入力すればシート名：３１日に貼り付けされます。 　　　 　　　その貼り付け先のシート上でデータが入っていない名前を削除しようと思ったのです。 　　　 　　　今、担当者に意向を聞きましたら、データが入っていない名前を削除してほしいとの依頼が 　　　ありましたので、 　質問：マクロで出勤、退出のデータが無い人を削除するマクロを教えて頂けないで 　　　　しょうか？出来れば下記に記載するコード（１日から３１日迄任意で飛ぶマクロのコード）の 　　　　続きに記載して頂けないでしょうか？ 　Sub copy() Const cancel As Variant = False Dim dd As Variant dd = Application.InputBox("何日のシートに貼り付けしますか？", "シート選択", , , , , , 1) If dd <> cancel Then If 1 <= dd And dd <= 31 Then If MsgBox(StrConv(dd, vbWide) & "日　ですか？", vbOKCancel) = vbOKCancel Then Worksheets("給与集計").Range("a1:ax110").copy Worksheets(dd & "日").Range("A1").PasteSpecial End If End If End If End Sub 「S列の出勤者名」と「F列の名前」は完全に同じものになるのですか？---Ｓ列は全員の名前でＦ列が出勤者 の名前です。なのでＳ列の名前の中にＦ列の名前が必ずあります。 翻って「データが転送される」とは具体的にどうデータを持って来るのですか？--- シート名：入力表　でｲﾝﾌﾟｯﾄデータを入力しています。（範囲は、Ａ２からＩ１６です。） 項目名：　 Ｂ列(月）Ｃ（日）Ｄ（曜日）Ｅ（コードＮｏ）Ｆ(名前）Ｇ（出勤）Ｈ（退出）Ｉ（労働時間）Ｊ（普通時間）Ｋ（残業時間）Ｌ（普通時給）Ｍ（残業時給）Ｎ（普通金額）Ｏ（残業金額）Ｐ（合計金額） Ｑ（休憩時間） ４行目に入力箇所：勤務日付、名前をリストボックスで選択、出勤時間と退出時間の項目があります。 後の項目は、１３行目、１５行目、１６行目にあり自動計算をしています。 ＊重要：シート名："入力表" の何十行も下の行に、各項目を横列に並べ、その一つ下の行に 　　　　各項目の入力箇所に、＝入力箇所　とし　横列に並んだ入力箇所を　シート名："給与集計"　に そのまま横列に、値として貼り付けをしています。 項目：　 Ｂ列(月）Ｃ（日）Ｄ（曜日）Ｅ（コードＮｏ）Ｆ(名前）Ｇ（出勤）Ｈ（退出）Ｉ（労働時間）Ｊ（普通時間）Ｋ（残業時間）Ｌ（普通時給）Ｍ（残業時給）Ｎ（普通金額）Ｏ（残業金額）Ｐ（合計金額） Ｑ（休憩時間） 以上です。人に説明をするまとめは、非常に難しく時間がかかります。遅くなって申し訳ありませんでした。 （和代） 2014/10/25(土) 14:03 ---- ＞その貼り付け先のシート上でデータが入っていない名前を削除しようと思ったのです。 成程、貼り付け先でのお話なら理解できます。 ＞ If MsgBox(StrConv(dd, vbWide) & "日　ですか？", vbOKCancel) = vbOKCancel Then 返り値の判定にvbOKCancel(ボタンの種類番号)を使うのはおかしくないですか？ 実体が同じ値だとしても、式の右辺は「vbOK」を使うべきだと思います。 仕様は、貼り付け先でこれ↓だったですよね。 ＞Ｓ７：ＣＢ５０の範囲で出勤（Ｔ７：Ｔ５０）と退出（Ｕ７：Ｕ５０）の ＞データがない行を一挙に削除する その仕様ですと「右端はCB列」ですけど、ご提示のコードは「右端がAX列」になっています。 なのでそこはCB列までに変更すると云うことで試作しております。 ※名前の並びについては何も考慮しておりません。(元ある順番のままとなります） Sub copy() Const cancel As Variant = False Dim dd As Variant Dim eachRow As Range Dim rowsToDel As Range dd = Application.InputBox("何日のシートに貼り付けしますか？", "シート選択", , , , , , 1) If dd <> cancel Then If 1 <= dd And dd <= 31 Then If MsgBox(StrConv(dd, vbWide) & "日　ですか？", vbOKCancel) = vbOK Then Application.ScreenUpdating = False Worksheets("給与集計").Range("A1:CB110").copy Worksheets(dd & "日").Range("A1").PasteSpecial For Each eachRow In Worksheets(dd & "日").Range("S7:CB50").Rows If Application.CountBlank(eachRow.EntireRow.Range("T1:U1")) = 2 Then If rowsToDel Is Nothing Then Set rowsToDel = eachRow Else Set rowsToDel = Application.Union(rowsToDel, eachRow) End If End If Next If Not rowsToDel Is Nothing Then rowsToDel.Delete Shift:=xlUp End If Application.ScreenUpdating = True End If End If End If End Sub ＞人に説明をするまとめは、非常に難しく時間がかかります。 仰る通りです。 私自身も質問しようと思う時があるのですが、質問作りがすごく大変で、 こんな苦労をするなら自力解決した方が楽だと思って止めたことが何度かあります。 ＞遅くなって申し訳ありませんでした。 こちらは暇を見てやっているので、そちらも気長に取り組んでください。 （半平太） 2014/10/25(土) 19:59 ---- 半平太さん 今晩は、 実体が同じ値だとしても、式の右辺は「vbOK」を使うべきだと思います。−−−わかりました。 変更していただき有難うございます。 仕様は、貼り付け先でこれ↓だったですよね。−−−はい、 ＞Ｓ７：ＣＢ５０の範囲で出勤（Ｔ７：Ｔ５０）と退出（Ｕ７：Ｕ５０）の ＞データがない行を一挙に削除する その仕様ですと「右端はCB列」ですけど、ご提示のコードは「右端がAX列」になっています。 なのでそこはCB列までに変更すると云うことで試作しております。−−−有難うございます。 最初、０．５時間単位で横列に配置していました。その時の最終列を変更していませんでした。 ＞人に説明をするまとめは、非常に難しく時間がかかります。 仰る通りです。 私自身も質問しようと思う時があるのですが、質問作りがすごく大変で、 こんな苦労をするなら自力解決した方が楽だと思って止めたことが何度かあります。 ＞遅くなって申し訳ありませんでした。 こちらは暇を見てやっているので、そちらも気長に取り組んでください。−−−こちらの気持ちに 　合わせて頂きうれしいです。 明日午後から会社に行ってＶＢＡのコードを動かすのが楽しみです。明日の午後に連絡致します。 （和代） 2014/10/25(土) 21:59 ---- 半平太さん おはようございます。 出来ました。嬉しいです、有難うございました。 担当者も喜んでいます。 下記追加のコードはなんとなくわかるのですが、意味を教えて頂けますか？ 今後の参考にしたいと思いますので--- 今回と同じように、飛びとびのを不要な行、あるいは列を削除するコードを覚えたいのです。 下記のコードであれば("T1:U1")がブランクであれば範囲の中で削除するという事ですね。 For Each eachRow In Worksheets(dd & "日").Range("S7:CB50").Rows If Application.CountBlank(eachRow.EntireRow.Range("T1:U1")) = 2 Then If rowsToDel Is Nothing Then Set rowsToDel = eachRow Else Set rowsToDel = Application.Union(rowsToDel, eachRow) End If End If Next If Not rowsToDel Is Nothing Then rowsToDel.Delete Shift:=xlUp 以上、宜しくお願い致します。 （和代） 2014/10/26(日) 11:54 ---- 半平太さん 担当者からすみません、色々テストしてみたら一つ依頼があります。と 言って来ました。もうこれ以上の依頼は受けませんが--- 担当者（Ａ部門）の質問は、色がつく範囲（Ｘ７：ＣＢ５０）の中で、応援で他の部署に行く人の（数人） 時間帯に他の部署と解る表示を出して頂きたいという事です。 私はデータ入力でリスト（Ｂ部門、Ｃ部門、Ｄ部門）を入れました、▼ボタンで試しますとうまく行きましたが、よく見ると関数が消えています。考えてみると文字に入れ替わっているので当たり前ですよね、 何か違う方法で表示されるように出来ないでしょうか？表示は（Ｘ７：ＣＢ５０）の一つ一つのセルに表示します。 半平太さんには、コードを教えて頂いて終りだったのに申し訳なく思います。 よろしくお願いします。 （和代） 2014/10/26(日) 12:28 ---- 半平太さん 上記質問で、 　「何か違う方法で表示されるように出来ないでしょうか？表示は（Ｘ７：ＣＢ５０）の一つ一つのセルに 　　表示します。」と言いましたが、よく考えてみれば今回の目的は、担当者が手書きでしていたのを少しで 　も簡略する事が　主旨ですから、一つ一つのセルを選択するのは時間がかかりすぎるので何をしているか 　解りませんから、 　先程、担当者にそれを説明して納得して頂き、一つ一つのセルにリストボタンを入れるのを止めました。 シフトを組む時に、他の部門へ応援する時間帯ががわかると聞きだしました。 そして一日の時間帯の中で、ある時間帯にＢ部門に行って、再度Ａ部門に戻り、違う時間帯にＣ部門、 、またはＤ部門に行くとの事です、頻繁には一日の中でころころ部署を移動する事は少ないらしいのですが、 追加して頂いた休始、休終等を利用して色がついているところに他の部署に移動する時だけ他の部署名が 表示される方法を教えて頂けますか？ 自分では関数の組み合わせのアイデアがもう一つ出来ませんので、 よろしくお願いします。 （和代） 2014/10/26(日) 13:03 ---- 半平太さんへ 本当に申し訳なく思いますが、 結論からですが、追加質問があります。 シート名：入力表　の項目で、以前お知らせしました項目の中で、休憩時間（自動計算）があります。 休憩時間の自動入力箇所の上のセルに、コード１を入れたら休憩時間入力箇所に"休憩不可"と表示されます。 そして給与集計のＱ列に休憩不可と貼り付けされます。 そうすれば、８：００から２２：００迄、全て黄色（休憩時間の意味）が塗られます。 休憩不可は、めったにありませんからＶＷ列の追加後チェックをするのを忘れていました。 そこで質問です： すみませんが、Ｑ列に休憩不可が表示されたならば、出勤、退出の時間帯をブルー色で塗りつぶし（条件付き 書式で設定済み）黄色（条件付き書式で設定済み）の休憩時間帯の色をはずすようにして頂けないでしょうか？　本当に重ねがさね申し訳なく思っています。 （和代） 2014/10/26(日) 14:01 ---- 半平太さんへ 先程、（和代） 2014/10/26(日) 14:01で質問致しました、 Ｑ列に休憩不可が表示されたならば、出勤、退出の時間帯をブルー色で塗りつぶし（条件付き 書式で設定済み）黄色（条件付き書式で設定済み）の休憩時間帯の色をはずすようにして頂けないでしょうか？ は、試行錯誤の上、解決しました。 苦肉の策かもしれませんが、Ｒ列のスタート時間の箇所に２３と入れましたら黄色が全部消え　勤務時間帯に応じたブルー色がつきました。自分で出来た事が嬉しいです。 故に残された質問は、 追加して頂いた休始、休終等を利用して色がついているところに他の部署に移動する時だけ他の部署名が 表示される方法を教えて頂けますか？ になります。宜しくお願いします。 （和代） 2014/10/26(日) 14:20 ---- あれ？　また追加のレスがありましたが、2014/10/26(日) 14:01　までのを前提に書いております。m(__)m ＞一日の時間帯の中で、ある時間帯にＢ部門に行って、再度Ａ部門に戻り、違う時間帯にＣ部門、 ＞またはＤ部門に行くとの事です、 そのデータをどこにどう入力するかを決めないと始まりません。 (1)F列の名前に合わせるのか、S列の名前に合わせるのか 　 F列ならR列の横に新しい列を挿入する必要がありますし、 　 S列ならW列の横に新しい列を挿入する必要があります。 多分、休憩の時間帯と同じような処理になるだろうなぁとは思います（それだとF列のケース)。 　「一つ一つのセルに表示します」と云うような構想だったのなら、その延長で考えるとS列のケース。 　 いずれにしても、和代さんに(担当者に？）どっちか決めて頂く必要があります。 (2)『頻繁には一日の中でころころ部署を移動する事は少ないらしい』とのことですが、 最多で何回なのか決めないとレイアウトが確定せず、具体的な関数を書くことは出来ません。 <休憩時間と同じような体裁にした場合で、3部門までの例> 行 __X__ _____X_____ __X__ ____X____ __X__ ____X____ __X__ ____X____ 1 名前 ・・・・・ 時間 他部1(始) 時間 他部2(始) 時間 他部3(始) 2 上田 ・・・・・ 1 10 　　0.75 13 　2 15 (3)部門別に色を変えるのか、他部門に行っている間さえ分かればいいので他部門は全て同じ色なのか。 (4)「実際の人数」の計算はどうなるのか。 　　引き続き休憩だけ除くのか、他部門に行っている間も除くのかどうか。 ＞全て黄色（休憩時間の意味）が塗られます。 ちょっと、流れが不明なんですけど、休憩は黄色以外にすると云うのが当初のお話じゃなかったですか？ まぁ、どんな色にするかはそちらの決定することで、私がとやかくいう問題ではないですけど。 いずれにしても以下の変更で直ると思います。 ＞W7セル =IFERROR(V7+INDEX($Q$7:$Q$50,MATCH($S7,$F$7:$F$50,0)),"") 　へ変更 　↓ W7セル =IFERROR(V7+N(INDEX($Q$7:$Q$50,MATCH($S7,$F$7:$F$50,0))),"") （半平太） 2014/10/26(日) 14:39 ---- １、まず下記に変更しましてうまく行きました。有難うございました。只、N(をつける事で何故黄色が 　　無くなるのでしょうか？ｎはどういう意味を持つのでしょうか？ 　＞W7セル =IFERROR(V7+INDEX($Q$7:$Q$50,MATCH($S7,$F$7:$F$50,0)),"") 　へ変更 　↓ W7セル =IFERROR(V7+N(INDEX($Q$7:$Q$50,MATCH($S7,$F$7:$F$50,0))),"") ２、休憩は黄色以外にすると云うのが当初のお話じゃなかったですか？ 　　その通りで当初は休憩を黄色以外にしていました。休憩を黄色以外の薄さですると、全体がぼけるし、 　　かといって休憩を黄色より濃い色でするとメインの勤務時間帯が薄れるような感じに見えて 　　途中で色を変えていました。すみません。 ３、上記（１）から（４）迄で、今解っているのは、 　　（１）Ｓ列の名前に合わします 　　（２）（３）（４）は担当者が時間ある時に相談して後ほど決めます 　　 お待ちください。 　　 （和代） 2014/10/26(日) 15:09 ---- 「一つ一つのセルに表示します」と云うような構想だったのなら、その延長で考えるとS列のケース。---と 言う事でＳ列を選択しましたが、 多分、休憩の時間帯と同じような処理になるだろうなぁとは思います（それだとF列のケース)。--このように Ｆ列に選択した場合には、移動部門の表示は勤務時間帯の箇所に部門名"ｘｘ"と表示されるのでしょうか？ 上記（２）（３）（４）の回答を出しました時にご返答して頂ければ結構です。 （和代） 2014/10/26(日) 15:24 ＜ 使用 Excel：Excel2004(Mac)、使用 OS：WindowsVista ＞ ---- ＞N(をつける事で何故黄色が無くなるのでしょうか？ｎはどういう意味を持つのでしょうか？ N関数は数値はそのまま数値を返しますが、文字だと0を返します。 それで何時に休憩を開始しようとも、休憩終了はそれより「0」時間後、 つまり同じ時刻に終了するので、休憩を取っていないのと同じことになります。 ＞Ｆ列に選択した場合には、移動部門の表示は勤務時間帯の箇所に部門名"ｘｘ"と表示されるのでしょうか？ F列タイプに限らず、そんな表示にしたいのかどうかに掛かっています。 表示したいなら、どの部門なのかが分かる(何らかの）工夫をしなければならない、 と云う現実問題につながるだけです。 担当者の意向をお確かめください。 （半平太） 2014/10/26(日) 17:14 ---- 半平太さん 　「私が閲覧している最中にインターネットエクスプローラーの更新ボタンを 　押したタイミングで急に消えてしまいました。」私もだいぶ前にこのサイトを開いたのですが 　私のデータが無くなっていましたので驚きました。有難うございました。 　私のデータが見れて良かったです。 　表示したいなら、どの部門なのかが分かる(何らかの）工夫をしなければならない、 　と云う現実問題につながるだけです。--- 担当者に確認後、一旦私もチャレンジしてどうしてもわからない時にご相談致しますので、 よろしくお願いいたします。 （和代） 2014/10/26(日) 17:54 ---- 「N関数は数値はそのまま数値を返しますが、文字だと0を返します。 それで何時に休憩を開始しようとも、休憩終了はそれより「0」時間後、 つまり同じ時刻に終了するので、休憩を取っていないのと同じことになります。」---理解しました。 　「最多で何回なのか決めないとレイアウトが確定せず、具体的な関数を書くことは出来ません。」 　---３回です 「(3)部門別に色を変えるのか、他部門に行っている間さえ分かればいいので他部門は全て同じ色なのか。」 　---部門別に色を変えます。部門名：仮に　Ａ　　Ｂ　　Ｃ　部門です。Ａ＝緑　Ｂ＝オレンジ　 　　Ｃ＝グレー　に決めました。 「(4)「実際の人数」の計算はどうなるのか。 　　引き続き休憩だけ除くのか、他部門に行っている間も除くのかどうか。」---他部門に行っている間 　　も休憩を含めて除きます。（この件は、気づきませんでした、有難うございます。） 先程、給与集計の表を部門への移動という事で下記挿入をし休始、休終の関数を利用して作成しました。 まずＳ列に移動先時間、Ｔ列に移動時刻　、Ｚ列に移動開始時刻（略して移始）ＡＡ列に移動終了時刻 （略して移終）を挿入（いずれも7行目からです）しました。 ＳＴにテスト的に数字を入れたら　Ｚ　ＡＡ　に結果がうまく行きました。 只、ＡＢ７に元ある関数に追加の関数（Ｚ　ＡＡ列の大なり小なり）を入れました下記関数が =IFERROR(IF(AND(AB$6>=$z7,AB$6<$aa7)," ",IF(AND(AB$6>=$z7,AB$6<$aa7)," ",IF(AND(AB$6>=$z7,AB$6<$aa7)," エラーメッセージで「多すぎる引数が入力されています」とで出ます。 質問は、上記関数の作成、関数の関連で 　３つの部門を識別する方法（条件付き書式利用で””の間が、半角３つが部門　Ａ　半角４つがＢ部門　 　半角５つがＣ部門　）を決めましたので、それらを含めた関数と条件付き書式 等　教えて頂けます 　でしょうか？ （和代） 2014/10/26(日) 19:58 ---- 「＞Ｆ列に選択した場合には、移動部門の表示は勤務時間帯の箇所に部門名"ｘｘ"と表示されるので 　　しょうか？ 　F列タイプに限らず、そんな表示にしたいのかどうかに掛かっています。」 　−−−勤務時間帯の箇所に部門名の文字を入れたら関数が消えると思いますので、 　移動部門の時間帯を色で表示したいと思っています。 　余白に、移動部門の色の説明をしたいと思っています。(Ａ＝緑　Ｂ＝オレンジ　Ｃ＝グレー) 　 （和代） 2014/10/27(月) 05:32 ---- ＞まずＳ列に移動先時間、Ｔ列に移動時刻　、Ｚ列に移動開始時刻（略して移始）ＡＡ列に移動終了時刻 ＞ （略して移終）を挿入（いずれも7行目からです）しました。 他部移動データを書込む列の位置なんですけど、名前の右隣にすると出勤・退出欄が名前欄(S列)から離れすぎになり、 使いにくくなると思いますので、休終の右からにしてみます。 あと「どの部門へ」なのか分からないとスペースを何個にするか決定出来ないのでそのデータ列も必要です。 先ず、旧W列と旧X列の間に12列挿入してください。(新X列からAI列の挿入です。レイアウトは下図をご参照) (1) AG7セル =Y7+Z7 (2) AH7セル =AB7+AC7 (3) AI7セル =AE7+AF7 (4) AJ7セル (長いので数回改行します。実際は一行で入力してください） =REPT(" ",MAX( AND(AJ$6>=$T7,AJ$6<$U7)*1, AND(AJ$6>=$V7,AJ$6<$W7)*2, AND(AJ$6>=$Z7,AJ$6<$AG7)*(MATCH($X7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1), AND(AJ$6>=$AC7,AJ$6<$AH7)*(MATCH($AA7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1), AND(AJ$6>=$AF7,AJ$6<$AI7)*(MATCH($AD7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1))) ※配列定数内の部門名("A3","B4","C5","経理")は、実際の部署名に変更してください。 (5) AJ7の式をCN7セルまで右へコピー <給与集計>　　　　　　　　　　　　←――――――――――――――新規列挿入ーーーーーーーーーーーーーーーーーー→ 行 __S__ __T__ __U__ __V__ __W__ __X__ __Y__ __Z__ __AA__ __AB__ __AC__ __AD__ __AE__ __AF__ __AG__ __AH__ AI AJ AK AL　・・CN 5 6 5 6 6 名前 出勤 退出 休始 休終 他部1 時間1 移始1 他部2 時間2 移始2 他部3 時間3 移始3 移終1 移終2 移終3 8 8.25 8.5 7 上田 8 17 12 13 A3 0.25 8.25 B4 0.5 8.75 経理 0.75 11 8.5 9.25 12 8 山本 8 16 10 10.75 B4 0.75 8 B4 1 11 8.75 12 0 ※部門名(X,AA,AD列の「A3,B4,C5,経理」) は実際の部署名に変更してください。 条件付き書式の数式は、分かり易い様に文字長での判定に変えた方がいいと思います。(現時点では、文字長で区別可能なので) （優先順位はどれが先でも問題ありません。） 勤務中　　=LEN(AJ7)=1 ? 休憩中　　=LEN(AJ7)=2 黄色 A3部門　　=LEN(AJ7)=3 緑 B4部門　　=LEN(AJ7)=4 オレンジ C5部門　　=LEN(AJ7)=5 グレー 経理部門　=LEN(AJ7)=6 ？ ＞「(4)「実際の人数」の計算はどうなるのか。」---他部門に行っている間も休憩を含めて除きます。 既存の「実際の人数」の数式は、半角スペースが1つのセルしか数えないものなので、今のままでOKです。 ＞ 余白に、移動部門の色の説明をしたいと思っています。(Ａ＝緑　Ｂ＝オレンジ　Ｃ＝グレー) 参考程度の話です・・・・・・ 余白が利用できるのでしたら、どこの範囲でもいいですけど 下図のように上に3つ空けたレイアウトにしていただくと、 数式の配列定数に代えて利用できるので数式が少し短く出来ます。(※1) 行 __A__ 1 ←1行目は空白文字列("") 2 3 4 A3 5 B4 6 C5 7 経理 ※1 上図のA1セルは　=""　として空白文字列にする。 検索値は文字タイプにすべく「&""」を追加 　　例→　　(旧)　AND(AJ$6>=$Z7, AJ$6<$AG7)*(MATCH($X7, {0;0;0;"A3";"B4";"C5";"経理"},0)-1), 　　　　(変更後)　AND(AJ$6>=$Z7, AJ$6<$AG7)*(MATCH($X7&"",$A$1:$A$7,0)-1), 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑ ここが実際の説明範囲 （半平太） 2014/10/27(月) 08:43 ---- 半平太さん 今晩は、 まずは、本当に私のエクセルシートを隣で見ているかの様に、手の行きとどく細やかな所まで、 書いて頂き感謝です、有難うございます。勿論時間もかけて頂いて-ーー。 （半平太） 2014/10/27(月) 08:43の書かれてある内容を順に見て行きながら、果たして私が、 うまく出来るのかと不安でした。１２列の挿入、（１）から（４）迄の式等を入れ、とりあえず給与集計の レイアウトを作成していこうと思いました。 試行錯誤したところもありましたが、色々試すうちに出来ました。只、条件付き書式で色を正しく塗って いるのに、データでは色が出なかったりして再度条件付き書式を削除してやり直しを繰り返しようやく 適宜適正な色がつきました。 　下図のように上に3つ空けたレイアウトにしていただくと、 数式の配列定数に代えて利用できるので数式が少し短く出来ます。(※1)---下記質問で回答を得てスムーズ に行ってから修正し直そうと思っています。 下記１、からの質問を教えて頂けますか？ １、下記関数で＊以降の意味（＊１、＊２、３から６行目の｝、０）-1等、教えて頂けますか？　--- AND(AJ$6>=$T7,AJ$6<$U7)*1, AND(AJ$6>=$V7,AJ$6<$W7)*2, AND(AJ$6>=$Z7,AJ$6<$AG7)*(MATCH($X7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1), AND(AJ$6>=$AC7,AJ$6<$AH7)*(MATCH($AA7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1), AND(AJ$6>=$AF7,AJ$6<$AI7)*(MATCH($AD7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1))) ２、勤務中　　=LEN(AJ7)=1 ? 休憩中　　=LEN(AJ7)=2 黄色 A3部門　　=LEN(AJ7)=3 緑 B4部門　　=LEN(AJ7)=4 オレンジ C5部門　　=LEN(AJ7)=5 グレー 経理部門　=LEN(AJ7)=6 ？　　　　 　 　・上記＝１２３４５６の意味が解りません、関数の結果になっているのでしょうか？ 　 　・経理部門がレイアウトの６行になかったのですが、何か意味があるかと思いまして、お聞きしました。 　　もし失礼ながら、何かの間違いで有れば、担当者から３つの部門で良いと言われていましたから、 　　このままで結構です。 ３、 一つお願いがあります。出来れば、 見やすくする為に ＡＧ列移終１をＺ列移始１の右隣に移動、 ＡＨ列移終２をＡＣ列移始の右隣に移動、 ＡＩ列移終３をＡＦ列移始の右側に移動、を私がしますから、 ＡＪ７からの関数を書いて頂けないでしょうか？私は、目が悪いものですから入力途中に間違ってばかり、 関数をかいて頂ければ、貼り付けをしますので、移動が可能であればよろしくお願いします。 以上です。 よろしくお願いします。 （和代） 2014/10/27(月) 19:51 ---- ＞１、下記関数で＊以降の意味（＊１、＊２、３から６行目の｝、０）-1等、教えて頂けますか？　--- *1 と *2 は該当する時間帯に入って来た場合、所要の半角スペースの数を既定するものです。 それ以降も同じこと、つまりA部門は「3」、B部門は「4」・・・を目指すのですけど、 Match関数の検索値となるセルには常に他部門情報が入力されているとは限りません。 もし、他部門のセルが未入力だった場合、Match関数はエラーになってしまいます。 このエラーを出させない工夫として、配列定数の1番目に0を用意しておき、 そこにヒットさせます。つまり、１(個）を返させる訳です。 これでエラーは回避できましたが、本当は0なので、最後に1を引く必要があります。 上述の工夫をした数式は常に１をマイナスするので、A部門、B部門・・も Match関数の返り値としては１つ大きく出てくるように仕組んでおかないとなりません。 そこで、A部門なら配列定数の3番目ではなく、4番目に位置させている訳です。 ＞２ ＞ 勤務中　　=LEN(AJ7)=1 ? ＞ 休憩中　　=LEN(AJ7)=2 黄色 ＞ A3部門　　=LEN(AJ7)=3 緑 ＞ B4部門　　=LEN(AJ7)=4 オレンジ ＞ C5部門　　=LEN(AJ7)=5 グレー ＞ 経理部門　=LEN(AJ7)=6 ＞　・上記＝１２３４５６の意味が解りません、関数の結果になっているのでしょうか？ 今のところ、半角スペースしか表示させていないので、 文字の長さで色の違を規定できるのでそんな条件式にしました。 その方が分かり易いと思ったのですが、却って分かりづらくなったのであれば、 和代さんの理解しやすい条件式に変更して頂いて全然かまいません。 ＞経理部門がレイアウトの６行になかったのですが、何か意味があるかと思いまして、お聞きしました。 ＞もし失礼ながら、何かの間違いで有れば、担当者から３つの部門で良いと言われていましたから、 ＞このままで結構です。 部門名の数だけ列を用意するのではなく、他部門に出向く回数分だけ用意するものです。 以前のご返信で「最多で3回の移動」とのことだったので、3種類準備したものです。 他部1の列に入力するのはA部門とは限らず、最初に経理部門になることもあるハズです。 他部門が何種類あろうとも、3回の移動で済むので、その3回の部門が入力できるようにしてあります。 (同じ部門に1日2回に分けることだってあるはずですよね？） ＞３、 ＞ＡＧ列移終１をＺ列移始１の右隣に移動、 ＞ＡＨ列移終２をＡＣ列移始の右隣に移動、 ＞ＡＩ列移終３をＡＦ列移始の右側に移動、を私がしますから、 ＞ＡＪ７からの関数を書いて頂けないでしょうか？私は、目が悪いものですから入力途中に間違ってばかり、 ＞関数をかいて頂ければ、貼り付けをしますので、移動が可能であればよろしくお願いします。 レイアウト(列の順番）が気に食わなければ、当該列全体を選択して、 シフトキーを押しながらマウスでドラッグ移動できます。 その時、各数式は「自動的」に変更されます(数式に手を入れる必要は全くありません） ※移終の３列を一カ所に集めたのは、一括して非表示になるかもしれないと思った為です。 　(既に表が横長になり過ぎているキライがあると感じております） 念のため、新レイアウトでの数式を掲示します。 (1) AJ5セル =COUNTIF(AJ$7:AJ$14," ") (2) V7セル =IFERROR(INDEX($R$7:$R$50,MATCH($S7,$F$7:$F$50,0)),"") (3) W7セル =IFERROR(V7+N(INDEX($Q$7:$Q$50,MATCH($S7,$F$7:$F$50,0))),"") (4) AA7セル =Y7+Z7 (5) AE7セル =AC7+AD7 (6) AI7セル =AG7+AH7 (7) AJ7セル =REPT(" ",MAX(AND(AJ$6>=$T7,AJ$6<$U7)*1,AND(AJ$6>=$V7,AJ$6<$W7)*2,AND(AJ$6>=$Z7,AJ$6<$AA7)*(MATCH($X7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1),AND(AJ$6>=$AD7,AJ$6<$AE7)*(MATCH($AB7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1),AND(AJ$6>=$AH7,AJ$6<$AI7)*(MATCH($AF7,{0;0;0;"A3";"B4";"C5";"経理"},0)-1))) <給与集計> 行 __T__ __U__ __V__ __W__ __X__ __Y__ __Z__ __AA__ __AB__ __AC__ __AD__ __AE__ __AF__ __AG__ __AH__ __AI__ _AJ_ _AK_ _AL_ 5 6 5 6 6 出勤 退出 休始 休終 他部1 時間1 移始1 移終1 他部2 時間2 移始2 移終2 他部3 時間3 移始3 移終3 8 8.25 8.5 7 8 17 12 13 A3 0.25 8.25 8.5 B4 0.5 8.75 9.25 経理 0.75 11 12 8 8 16 10 10.75 B4 0.75 8 8.75 B4 1 11 12 0 （半平太） 2014/10/27(月) 21:18 ---- 半平太さん 今晩は、 上記（半平太） 2014/10/27(月) 21:18　の各説明はよーく理解できました。 細かい説明に時間をかけて頂き有難うございます。 　　 　「※移終の３列を一カ所に集めたのは、一括して非表示になるかもしれないと思った為です。 　　(既に表が横長になり過ぎているキライがあると感じております）」 　−−−そう意図があったのですね、そうとは知らずすみませんでした、有難うございます。 　私も横長になりすぎるので、自動化のところは、非表示にして見やすくするつもりでいました。 質問： 　１、「当該列全体を選択して、 　シフトキーを押しながらマウスでドラッグ移動できます。」−−−該当列の番号にマウスをおいた 　状態でシフトキーを押しながらマウスでドラッグ移動しても移動している範囲に影が出来るだけで 　列の移動は出来ません。私のやり方が悪いと思いますが、どこに問題があるのでしょうか？ 　教えて下さい。 　２、他部署　Ｘ　ＡＡ　ＡＤ列には該当する部門名を手で入力するという事になるのですね。 　（関数はありませんから、入力規定でリストで選択するようにも出来ますが） 　以上です （和代） 2014/10/27(月) 22:43 ---- ＞ 　１、「当該列全体を選択して、 ＞ ＞ 　シフトキーを押しながらマウスでドラッグ移動できます。」−−−該当列の番号にマウスをおいた ＞　状態でシフトキーを押しながらマウスでドラッグ移動しても移動している範囲に影が出来るだけで ＞　列の移動は出来ません。私のやり方が悪いと思いますが、どこに問題があるのでしょうか？ 移動したい列の上部(列番号）をクリックすると列全体が選択されます。 その状態で、当該列の左端とか右端にマウスを持って行きます。 すると、マウス形状が矢印十字に変化します。 そこでシフトキーを押しながら、マウスの左ボタンを押し下げて掴み 移動したい場所(列と列の間）にドラッグしてボタンを放します。 ＞　２、他部署　Ｘ　ＡＡ　ＡＤ列には該当する部門名を手で入力するという事になるのですね。 ＞ 　（関数はありませんから、入力規定でリストで選択するようにも出来ますが） その通りです。 どこの部署かをセルに入力しなければ、半角スペースの数を決定することはできません。 ※個人的には細かすぎる仕様だなぁ、と思っております。 　そんなに沢山の色があると却って煩わしくなるだけだと思います。 　数式も複雑になりますし、ますます横長になるし・・ 　１色でいいんじゃないですかねぇ・・・ 　 （半平太） 2014/10/27(月) 23:21 ---- 「移動したい列の上部(列番号）をクリックすると列全体が選択されます。 その状態で、当該列の左端とか右端にマウスを持って行きます。 すると、マウス形状が矢印十字に変化します。 そこでシフトキーを押しながら、マウスの左ボタンを押し下げて掴み 移動したい場所(列と列の間）にドラッグしてボタンを放します。」−−−移始の右側に移動するときに、 矢印十字に変化して「　」内のようにドラッグするのですが、移終２から右側の列が一緒に移始の右側に 移動されます。？（他部２　時間２ 　移始２ 　他部3 　時間３ 　移始３が隠れます） 押し下げて掴み‐‐とは、シフトキーを押しながらマウスの左ボタンを押すだけですよね、 「※個人的には細かすぎる仕様だなぁ、と思っております。 　そんなに沢山の色があると却って煩わしくなるだけだと思います。 　数式も複雑になりますし、ますます横長になるし・・ 　１色でいいんじゃないですかねぇ・・」−−−私も本当にそれを感じています、担当者と話し合います。 　申し訳ありませんが、今か会議での為に外出します。帰りは午後３時頃になります。 （和代） 2014/10/28(火) 08:32 ---- ここをご参照ください。 　↓ 列を入れ替えましょう (Excel) http://blog.goo.ne.jp/pc_college/e/aff10425b0a3a3eead5233cc268bfca6 （半平太） 2014/10/28(火) 16:25 ---- 半平太さん 私の帰宅時間に合わせて頂き有難うございました。 参照のhttp:有難うございます。 http:の説明を見て何回しても移動されないので、色々している間にわかりました。　 最初に、項目名の上にセルの結合で見出しをつけていました。 セルの結合を外しますとうまく行きました。 どんくさい自分が嫌になります。 そんな私におつきあいをして頂き、無駄な時間を費やした事にお詫びいたします。 明後日会社に行き担当者と話し合い完成フォームが出来たところで報告致します。 有難うございました。 もうこの件では、質問は出ないと思いますが‐‐‐ （和代） 2014/10/28(火) 17:22 ---- 担当者に確認中、これで解決と思いきや　色々ありまして、申し訳ありませんが、各部署への移動の箇所の データ入力をシート名：入力　に移項しました。 （最後に質問があります。） 手入力のＸ列(他部１）からＡＩ列（移終）列の入力（移終１、移終２、移終３は残します）を シート名：入力表で入力する事となりました。 （現時点では、シート名：入力表で入力シート名：給与集計に転記、ついでＸ列からＡＨ列迄 （移終１、移終２、移終３は計算式）を また同じ人達を１人１人入力して行きます. それならシート名：入力表で１人１人を全ての入力項目に入力させ、シート名：給与集計のシートに転記 させた方が一度で済むし、名前と色を見る場合に非表示しやすいと思いましたので。） そこで、シート名：給与集計　Ｓ列からＡＡ列迄挿入してＸ列(他部１）からＡＩ（時間３）の列を移動 しました。（移終１、移終２、移終３は残します） 次に当初Ｖ列とＷ列にあった休憩の休始(変更はＡＥ列）休終（変更後はＡＦ列）の式をまねて　 ＡＧからＡＬ迄を下記のように式を入れました。 q r s t u v w x y z aa ab 5 休憩の 休憩 他部 他部１ 他部１ 他部 他部２ 他部２ 他部 他部３ 他部３ 全 員の名前 6 スタート 時間 1 スタート 勤務時間 2 スタート 勤務時間 3 スタート 勤務時間 7 ac ad ae af ag ah ai aj ak al 6 出勤 退出 休始 休終 移始１ 移終１ 移始２ 移終２ 移始３ 移終３ 7 =IFERROR(VLOOKUP(AB7,F$7:H$54,2,FALSE),"") =IFERROR(VLOOKUP(AB7,F$7:H$54,3,FALSE),"") =IFERROR(INDEX($Q$7:$Q$50,MATCH($AB7,$F$7:$F$50,0)),"") =IFERROR(AE7+INDEX($R$7:$R$50,MATCH($AB7,$F$7:$F$50,0)),"") =IFERROR(INDEX($T$7:$T$50,MATCH($AB7,$F$7:$F$50,0)),"") =IFERROR(AG7+INDEX($U$7:$U$50,MATCH ($AB7,$F$7:$F$50,0)),"") =IFERROR(INDEX($W$7:$W$50,MATCH ($AB7,$F$7:$F$50,0)),"") =IFERROR(AI7+INDEX ($X$7:$X$50,MATCH($AB7,$F$7:$F$50,0)),"") =IFERROR(INDEX ($Z$7:$Z$50,MATCH($AB7,$F$7:$F$50,0)),"") =IFERROR(AK7+INDEX ($AA$7:$AA$50,MATCH($AB7,$F$7:$F$50,0)),"") am an ao ap aq ar as at au cq6 6 8 8.25 8.5 8.75 9 9.25 9.5 9.75 10 ---- 22 7 =REPT(" ",MAX(AND(AM$6>=$AC7,AM$6<$AD7)*1, AND(AM$6>=$AE7,AM$6<$AF7)*2, AND (AM$6>=$T7,AM$6<$AH7)*(MATCH($S7,{0;0;0;"ショップ";"地下エ";"３Fエ";"その他"},0)-1), AND(AM$6>=$W7,AM$6<$AJ7)*(MATCH($V7,{0;0;0;"ショップ";"地下エ";"３Fエ";"その他"},0)-1), AND(AM$6>=$Z7,AM$6<$AL7)*(MATCH($Y7,{0;0;0;"ショップ";"地下エ";"３Fエ";"その他"},0)-1))) ＡＭ６から右列に８：００　８．２５　−−−−（ＣＱ６列　２２：００迄）ならんでいます。 Ｓ列からＡＡ迄の他部１　移始１　時間１、他部２　移始２　時間２、他部３　移始３　時間３　 のデータとＡＧ列からＡＬ迄の式は正しく繋がっています。チェック済みです。 条件付き書式の式、色は変更していません。 質問です：ところが、ＡＭ７(=REPT の関数が入る）から色がつくデータ範囲で、出勤が１０時からなのに ９時から色がついたり勤務時間内で、移動の色がつくはずなのがつかなかったりします。 （付いている人もいます.） 関数の修正はしていません。（移動をしても式が変わらないからと言っておられたので） 関数を見ているんですが、誤りが何処にあるかが解りませんので、 確認の程、よろしく願い致します。 （和代） 2014/10/30(木) 20:14 ---- ＞質問です：ところが、ＡＭ７(=REPT の関数が入る）から色がつくデータ範囲で、出勤が１０時からなのに ＞９時から色がついたり勤務時間内で、移動の色がつくはずなのがつかなかったりします。 ＞ （付いている人もいます.） ＞ 関数の修正はしていません。（移動をしても式が変わらないからと言っておられたので） ＞ 関数を見ているんですが、誤りが何処にあるかが解りません 他部名(ショップなど）のデータが「全員の名前(AB列)」より右にないからです。 左の方にはありますが、右側の移始のデータに対応する様な他部名ではありません。 （AMの式は同じ行に「対応する他部名」がある場合の数式です。） （半平太） 2014/10/30(木) 22:13 ---- 「他部名(ショップなど）のデータが「全員の名前(AB列)」より右にないからです。 左の方にはありますが、右側の移始のデータに対応する様な他部名ではありません。 （AMの式は同じ行に「対応する他部名」がある場合の数式です。）」−−−下記テストをして理解致しました。 　Ｆ列（出勤者の名前）をＡＢ列（全員の名前）のＦ列と同じ名前の行に手で貼り付けをし 　合わせましたら各部署の移動時間の色が、うまく行きました。 という事は、Ｆ列の名前とＡＢ列の名前と同じ行に並べることをしない限り、シート名：入力　に 移項できないという事でしょうか？　かなり煩雑な仕様になりますね、申し訳ありません。 ＡＭ７の関数の中に、Ｆ列の名前がＡＢ列の名前と同じであれば、 同じ名前の、Ｂ列からＰ、Ｑ列（休憩のスタート）からＡＡ列（勤務時間３）のデータを読み取る 事は出来ませんでしょうか？ あるいは、その他のアイデアで叶えられませんでしょうか？ よろしくお願い致します。 （和代） 2014/10/31(金) 00:15 ---- ＞Ｆ列の名前とＡＢ列の名前と同じ行に並べることをしない限り、シート名：入力　に ＞移項できないという事でしょうか？ そんな事はないです AB列の右に「休憩や他部関係のデータ」を表示することが出来たんですから、 「他部名」も同じ様にAB列の右に追加挿入して、他部の移始などと同様の数式を作れば、 AB列の名前と同じ順で表示することができ、AMの数式はそっちの列の他部名を見るよう変更すれば 同一行にあるので対処することができます。 （全体的に冗長な気はしますが、結果は正常に得られます） （半平太） 2014/10/31(金) 10:07 ---- ＞結果は正常に得られます と書きましたが、AB列より右に挿入した他部名は、今度は数式で出したデータになるので S列の他部名が未入力の場合、 これではまずいので、1つ目は空白文字列に変更してください。 　　　↓ ＞MATCH($Y7,{0;0;0;"ショップ";"地下エ";"３Fエ";"その他"},0) ↓ MATCH($Y7,{"";0;0;"ショップ";"地下エ";"３Fエ";"その他"},0) （半平太） 2014/10/31(金) 11:35 ---- 「結果は正常に得られます」---お陰さまで、出勤（ＡＣ列）から　移終（ＡＯ列）迄の関数に基づき 　適切な各色が出ました。教えて頂いた事を変更したのですが、うまく行った時は感激しました。 　 　色々チェックをしてOKでしたが、今まで出なかった＃Ｎ/Ａが出ました。関数の最初にカッコを入れ最後 　に””）とすれば＃Ｎ/Ａは消えると思いましたが消えませんでした。 　どうしても解らないので、このページで質問をしようと思いましたら半平太さんからの同じ内容が記載 　されていましたので驚きました。本当に色々ご迷惑をかけ、また今みたいにいつも一生懸命に考えて頂き 　嬉しく思います。(MATCH($Y7,{"";0;0;"を３回変更しましてうまく行きました。有難うございます。 　今からシート名：入力表　に各部署名　移始、時間、移終等の　項目を追加して　シート名：　給与集計に 　値を転記するような作業をして行きます。 質問です： 　　　　　「他部名(ショップなど）のデータが「全員の名前(AB列)」より右にないからです。 　　　　　左の方にはありますが、右側の移始のデータに対応する様な他部名ではありません。 　　　　　（AMの式は同じ行に「対応する他部名」がある場合の数式です。）」---全員の名前があるＡＢ列 　　　　　の右、左の区別は関数のどこを見て判断すればいいのですか？ 　　　　　以上です。 　 　 （和代） 2014/10/31(金) 15:14 ---- ＞全員の名前があるＡＢ列の右、左の区別は関数のどこを見て判断すればいいのですか？ 厳密に云うと、左側とか右側とかの区別ではありません。 AB列の順になっている「右側の列」をそのまま左側に持って行けば、 「左側」にあってもAB列の順なんですから「半角スペース算出用の数式」は 正常に値を返します。 でも実際は項目移行に伴って、他部名は「F列の名前の順」に表示させることにしたのですよね？ そうなったら、正しい値を返す訳がないです。 「関数のどこを見る」とは、「数式の意味を理解する」こと以外にはありません。 数式が参照したセルで正しい計算されるかどうか、それが判断基準です。 とは言っても、数式を自分で考えない限り、まぁ簡単に判断できないでしょうけど。 ーーーーーーーーーーーーーーーーーーーーーーー 話は少し逸れますが、そもそものトラぶりの元は、 F列の名前とAB列の名前(全員の名前)の順番が違うと云うことなんだと思いますよ。 初っ端の質問では、休憩の時間を入れるとその人の休憩時間帯の色が変わる仕様でしたけど、 入力行とは違う行の色が変化するんですよね？（名前の順番が違うんですから） それって担当者(ユーザー）は、イラつかないですか？ そんな仕様で「本当にいいのぉ」って感じます。 「そんなこと言ったって、」その表は全員の名前が決められた順に 表示された表になる必要があるんだ、てな反論がありそうですけど、 私に言わせれば、日にち別のシートでは全員の名前なんて要らない と云うお話もありましたので「そんな必要ないんじゃない？」と疑いたくなります。 F列に存在しない名前だけ、さらにその下に表示する様にして、 全員が分かるようにすれば大勢に影響ないんじゃないですか？ もし名前がF列と同じ順で作っていいなら、作表がもっと簡明にできる気がします。 そして当然、この質問の出番もなくなります。 　　　　　　　↓ 「全員の名前があるＡＢ列の右、左の区別は関数のどこを見て判断すればいいのですか？」 （半平太） 2014/10/31(金) 16:37 ---- 「入力行とは違う行の色が変化するんですよね？（名前の順番が違うんですから）、 それって担当者(ユーザー）は、イラつかないですか？」−−−私もチェックするときに名前の順が違う 　為にイラつきます。 　担当者がその表を見て今日の出勤者のシフトは人数が少ない、誰を入れようか？ 　と言うときに、他の名前（出勤していない人）が入っていたら、では誰々に連絡しよううか　と 　わかりやすい為だと言われましたからです。 「もし名前がF列と同じ順で作っていいなら、作表がもっと簡明にできる気がします。」−−− 　 提案：私も、結論的には全員の名前のところをＦ列と同じ順に作り、さらにその下に出勤していない名前を 　　　表示する様にして、全員が分かるようにすれば煩雑な表を作らなくていいと思います。 　 　　「もし名前がF列と同じ順で作っていいなら、作表がもっと簡明にできる気がします。」−−− 　　　 　　「さらにその下に出勤していない名前を表示する様にして・・・」等、そのような事を 　　私が作ろうとしたいのですが、時間がかかる反面うまく行きませんので、今まで変更変更で誠に申し 　　訳ありませんが、半平太さんに作って頂けませんか？　ご無理ばかり言って申し訳ありません。 　　よろしくお願い致します。 　　 （和代） 2014/10/31(金) 22:12 ---- 前提が余りクリアじゃないのですけど、 １．F列の名前は上から詰めてありますね？ 　　つまり、名前と名前の中間に空白行がないですね？ ２．AB列の右隣の列に(引き続き）出勤と退出の列は必要ですか？ 　　G・H列にも同じデータがあるので計算上は不要なのですが、 　　担当者の作業上、そこにも有った方がいいのでしょうか？ 　　もし要らなければ、「条件付き着色エリア」はAB列(名前列）のすぐ右隣からが始まる、 　　と云うことになります。 　　この話は後述3の疑問と絡みます。 ３． ＞　担当者がその表を見て今日の出勤者のシフトは人数が少ない、誰を入れようか？ ＞ 　と言うときに、他の名前（出勤していない人）が入っていたら、では誰々に連絡しよううか　と ＞　わかりやすい為だと言われましたからです。 これは、候補名さえ出ていればそれで用が足りるのでしょうか？ それなら今回の対策だけでいい事になります。 しかし、AB列(候補名）の右に出勤・退社時刻を入力して、条件付の着色状況がどう変わるか見たい、 と云うことになるんでしょうか？ 　（その場合、休憩時刻とか他部移動のデータはどうするんでしょうか？） ※追加で決まったシフトは、一旦入力表に戻ってデータを入れて貰い、 　もう一度給与集計に貼り付けて貰えれば、結局今回の対策の範囲内に 　収まることになるんですけど、そんなフローに出来るんでしょうか？ （半平太） 2014/11/01(土) 11:33 ---- １、F列の名前は上から詰めてありますね？ 　　つまり、名前と名前の中間に空白行がないですね？ 　　ー−−名前は、Ｆ７から下にあり名前と名前の間は空白行はありません・ ２、AB列の右隣の列に(引き続き）出勤と退出の列は必要ですか？ 　−−−ここに来て結論は、出勤と退出の列は不要です。 ３、これは、候補名さえ出ていればそれで用が足りるのでしょうか？ 　 −−−ここに来て結論は、候補名さえ出ていればそれで用が足ります。 　　 　しかし、AB列(候補名）の右に出勤・退社時刻を入力して、条件付の着色状況がどう変わるか見たい、 　 と云うことになるんでしょうか？ー−−いいえ、候補者の名前が見れるだけでいいです。 ※追加で決まったシフトは、一旦入力表に戻ってデータを入れて貰い、 　もう一度給与集計に貼り付けて貰えれば、結局今回の対策の範囲内に 　収まることになるんですけど、そんなフローに出来るんでしょうか？ 　　ー−−上記＊の中で「結局今回の対策の範囲内に収まることになるんですけど」の 　　今回の対策の範囲ないとはどの範囲でしょうか？ 　・一旦入力表に戻ってデータを入れて、もう一度給与集計に貼り付ける追加範囲は、休憩のスタート（Ｑ 　列から　他部３　時間（ＡＡ列）迄です。そしてＡＢ列の名前になります。 （和代） 2014/11/01(土) 14:17 ---- ＞上記＊の中で「結局今回の対策の範囲内に収まることになるんですけど」の ＞今回の対策の範囲ないとはどの範囲でしょうか？ 「今回の対策の範囲内に収まる」とは 「今回の対策に吸収されるので特別な手当は必要ない」と云う程度の意味です。 １．全員の氏名をどこかに書出してください。 　　サンプルでは、邪魔にならない場所として、CI列に書き出すことにしてあります。 　　項目名は「総員」とします。 　　※「全員の名前」と同義ですが、AB列とは並びが違うので項目名は変えました。(深い意味はありません） ２．数式の入力 　(1) CJ6セル =COUNTIF(CJ7:CJ50,0) 　(2) CJ7セル =IF(COUNTIF(F$7:F$50,CI7),0,ROW()) 　　　これを下方へCJ50までコピー 　(3) AB7セル =IF(F7<>"",F7,INDEX(CI:CI,SMALL(CJ$7:CJ$50,ROW()-6))) 　　　これを下方へAB50までコピー 　(4) AC7セル =REPT(" ",MAX(AND(AC$6>=$G7,AC$6<$H7)*1,AND(AC$6>=$Q7,AC$6=$T7,AC$6=$W7,AC$6=$Z7,AC$6結果図　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　 行 __F__ __G__ __H__: __Q__ ____R____ ____S____ __T__ __U__ ___V___ __W__ __X__ __Y__ __Z__ __AA__ __AB__ _AC_ _AD_ _AE_ __AF__ __AG__: _CE_ __CF__ _CG_ _CH_ __CI__ _CJ_ 6 名前 出勤 退出 : 休始 休憩時間 他部1 移始1 時間1 他部2 移始2 時間2 他部3 移始3 時間3 全員 8 8.25 8.5 8.75 9: 21.5 21.75 22 総員 5 7 上田 8 17 : 12 1 ショップ 8.25 0.25 その他 8.75 0.5 ３Fエ 10 0.75 上田 : 上田 0 8 塩谷4 8 16 : ３Fエ 11 1 塩谷4 : 山本 0 9 山本 8 22 : 10 0.75 地下エ 8.5 0.75 地下エ 9.5 0.3 山本 : 浜本 0 10 塩谷2 9 14 : 10 休憩不可 塩谷2 : 塩谷1 10 11 浜本 8 21 : 21.5 0.25 浜本 : 塩谷2 0 12 : 塩谷1 : 塩谷3 12 13 : 塩谷3 : 塩谷4 0 14 : 塩谷5 : 塩谷5 14 ４．日にちシートへの転記マクロ Sub copy() Const cancel As Variant = False Dim dd As Variant Dim eachRow As Range Dim rowsToDel As Range dd = Application.InputBox("何日のシートに貼り付けしますか？", "シート選択", , , , , , 1) If dd <> cancel Then If 1 <= dd And dd <= 31 Then If MsgBox(StrConv(dd, vbWide) & "日　ですか？", vbOKCancel) = vbOK Then Application.ScreenUpdating = False Worksheets("給与集計").Range("A1:CG110").copy With Worksheets(dd & "日") .Range("A1").PasteSpecial .Range("AB7:AB50").Value = .Range("AB7:AB50").Value '値に変換 .Range("CL6:CL50").Value = .Range("CJ6:CJ50").Value '値に変換 For Each eachRow In .Range("S7:CG50").Rows If Application.CountBlank(eachRow.EntireRow.Range("G1:H1")) = 2 Then If rowsToDel Is Nothing Then Set rowsToDel = eachRow Else Set rowsToDel = Application.Union(rowsToDel, eachRow) End If End If Next ' .Select 'マクロ実行後、日にちシートに移動したい場合は行頭のコメントマーク(')を外す ' .Range("A1").Select End With If Not rowsToDel Is Nothing Then rowsToDel.Delete Shift:=xlUp End If Application.ScreenUpdating = True End If End If End If End Sub （半平太） 2014/11/01(土) 15:39 ---- 半平太さん 今晩は、 さっそく作って頂き有難うございました。 全て入力しましたがマクロを走らせると色が出ません、 勤務する人の名前を転記すれば、ＡＢ列には転記したままで名前が出ます。 只色が全然出ません。 こんな事をここで書くようなことではないと思うのですが、 今、私はおなかが痛く体がだるいので、明日会社に行ってから式等の確認をしますので しばらくお待ちください。 （以前に返答はゆっくりしてもらっていいですよとかの意味合いを言って頂いた事がありましたが‐‐） 明日会社に行ってからのお返事では、あまりにも長い時間がかかりますので、どこかでつまづいて いるのではないかと、気にされていたら申し訳ないと思い余分な事を書きました。すみません。 （和代） 2014/11/01(土) 18:32 ---- 再度書きますが、 返信のタイミングはそちらの都合で構いません。レスの予告も要りません。 まぁ、実際どうするか、これまたそちらの自由ですけど。 常連回答者は一週間くらい放っとかれても気にしないです。 レスがなくてもあれこれ心配しません。 問題があれば、質問者から何か言ってくるハズ、と思って 他の面白そうな質問の回答案を練っています。 まぁ、あまり長く空くと思い出すのが大変になってレスしない、 なんてことも無い訳ではないですが、基本、暇なので 他に面白い質問がなければ、思い出しモードに入るのが普通です。 ＞只色が全然出ません。 直接の原因を調べてみてください。 (1)当該セルの文字列の長さが正しいか？ 　（何個であるべきものが、0個になっているのか） (2)当該セルの条件付き書式の数式が正しいか？ （半平太） 2014/11/01(土) 20:03 ---- 色がつかない件ですが、再度コピーしなおしたら下記、休憩とショップの色だけが付きませんでした。 データは４人分あります。その中で、休憩・ショップ共４人分とも色はありません。 (2)当該セルの条件付き書式の数式が正しいか？ 条件付き書式の数式(設定範囲→ $AC$7:$CG$50 ） 休憩中　 =LEN(AC7)=2 黄色 ショップ =LEN(AC7)=3 緑　 にしています。 (1)当該セルの文字列の長さが正しいか？ 　（何個であるべきものが、0個になっているのか）ーー何個であるべきものとは、どこを数えるのですか？ 以上　よろしくお願い致します。 （和代） 2014/11/01(土) 23:48 ---- テストデータとして7行目に以下のデータを入れてみてください。 G7(出勤)　　　 8 H7(退出)　　　17 Q7(休憩開始) 8.5 R7(休憩時間) 0.25 S7(他部1) ショップ T7(移始) 9 U7(時間1) 0.25 以上で、AE7セルが黄色、AG7セルが緑に(本当に）変わらないか、先ずご確認ください。 変わらない場合、以下の数式と入れ替えて、どんな値が出てくるか教えてください。（それぞれ、2、3　となるのが正しいのですけど） AE7セル =MAX(AND(AE$6>=$G7,AE$6<$H7)*1,AND(AE$6>=$Q7,AE$6=$T7,AE$6=$W7,AE$6=$Z7,AE$6=$G7,AG$6<$H7)*1,AND(AG$6>=$Q7,AG$6=$T7,AG$6=$W7,AG$6=$Z7,AG$6=9,"1",IF(I4>6.5,"0.75",IF(I4>4.5,"0.5",""))))　 　　その式をシート名：給与集計　に転記する為に　シート名：入力表のＲ９１に＝Ｊ１３　としています。 　　Ｒ９１は他の項目（Ｐ９１--Ｆ１６の合計を導きます）と同じく数字になっています。　 　　（転記項目先の中で"休憩無し"は現在削除しています。） 　　 　　 （和代） 2014/11/02(日) 14:24 ---- ＞=IF(J11=1,"休憩無し",IF(I4>=9,"1",IF(I4>6.5,"0.75",IF(I4>4.5,"0.5",""))))　 　↑　　　　　　↑　　　　　　　　↑ ""で囲ったら数値じゃありません。 数字(文字の一種）になります。 SUM関数の引数に入れると「0」と同じ扱いになります。 なので、""を外してください。つまり↓ =IF(J11=1,"休憩無し",IF(I4>=9,1,IF(I4>6.5,0.75,IF(I4>4.5,0.5,""))))　 （半平太） 2014/11/02(日) 14:46 ---- 上記コメントで黄色が適切な時間帯に出ました。良かったです、すみませんでした。 緑もどこかにミスがあるのですね。 探しているのですが---？？ （和代） 2014/11/02(日) 15:21 ---- ＞緑もどこかにミスがあるのですね。 引き続き、テスト環境(転記したデータが表示されている状態）で、 以下の確認をしてください。 どこか空いているセル(CK6セルがいいかも知れません）に 下記数式を入力して右へCM6までコピーしてください。 どんな値が返るか教えてください。以下と完全に同じならば正しい。(ほんの少しでも違ったら問題ありです） 行 _______CK_______ _____CL_____ _____CM_____ 6 文字 #ショップ# 数値 #9.000# 数値 #0.250# （半平太） 2014/11/02(日) 16:14 ---- 肝心の数式をアップしていなかった　m(__)m CK6セル =IF(TYPE(S7)=1,"数値 #","文字 #")&TEXT(S7&"","0.000")&"#" （半平太） 2014/11/02(日) 16:28 ---- 行 ck cl cm 6 文字 #ショップ# 数値 #8.000# 数値 #0.250#　←CK6セル =IF(TYPE(S7)=1,"数値 #","文字 #") 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　&TEXT(S7&"","0.000")&"#"をＣＭ６迄コピー しました。 結果 cl6だけが違います。　 数値 #9.000#　　 （和代） 2014/11/02(日) 16:55 ---- ＞結果 cl6だけが違います えー、テストではT7セルに「9」を入れて頂いていると思うのですけど、実際は「8」にしたのですか？ ＞ S7(他部1) ショップ ＞ T7(移始) 9 ←　ここはテストで「9」なんですけど、 ＞ U7(時間1) 0.25 前提が変わると話が食い違うので、テストデータ通りに転記データを作ってください。 １．「9」にするとCL6セルはどうなりますか？ 　　「数値 #9.000#」が出ますか？ ２．「数値 #9.000#」が出るとして、 　再度、AG7セルに下記数式を入れて、「3」が出るか確認してください。 =MAX(AND(AG$6>=$G7,AG$6<$H7)*1,AND(AG$6>=$Q7,AG$6=$T7,AG$6=$W7,AG$6=$Z7,AG$6=$G7,AC$6<$H7)*1,AND(AC$6>=$Q7,AC$6=$T7,AC$6=$W7,AC$6=$Z7,AC$6=$G7,AG$6<$H7)*1,AND(AG$6>=$Q7,AG$6=$T7,AG$6=$W7,AG$6=$Z7,AG$6"",F7,INDEX(CI:CI,SMALL(CJ$7:CJ$50,ROW()-6))) 新たな質問です。 今までの質疑応答が長すぎましたので、下記事項を新たな質問としたかったのですが、--- 関連していますのでここに記載させて頂きました。都合が悪ければ新たな質問として出させて頂きますので おっしゃって下さい。判読して書き直した点もありましたが、順序がうまくまとまっていないような気が します。 １、色関係で夢中になっていたので忘れていましたが、下記の別シート："就業入力用"　のＡ列に、 　　他部を入れ替える迄のシート名：給与集計　旧ＡＣ列の全員の名前の順番通りに並べていました。 　　（実際はＡ列の名前は＝基本事項！Ｂ５から下に５０行並んでいます） 　　 　　そして、シート名：給与集計　のＡＤ（出勤時間）ＡＥ(退出時間）のデータと 　　シート名："マスターコード結合"のマスターコード表と合致させて　シート名：就業入力表　の 　　データに表示するようにしています。 　　 　　　一旦　シート名："出勤者のコード表"（縦Ａ列に名前、Ｂ列から横列に１日から３１日迄の表）の 　　　　Ｂ列のデータをコピー、そしてシート名："就業入力表"（縦Ａ列に１日から３１日迄、Ｂ列から 　　　　横列に名前）のＢ３に形式を選択して貼り付け、→行列を入れ替える　後は、他の範囲に式をコピー 　　　　しています。　コピー、貼り付けは実際は、なかなかうまく行きませんでした。 　　　　（１列分の縦を横への形式を選択、行列を入れ替えてコピー、そのあと２列目から式のコピーを 　　　　しても４割程がデータの取得がうまく行きませんでした） ＊＊質問：ところが、他部を入れ替えた後、シート名：給与集計　のＡＢ列の名前は、データがある人は 　　　　　上段にデータが無い人はその次から全員が並んでいますので、シート名：就業入力表のＡ列の 　　　　　名前の導き方が違いますので、どのように導けばよいかわかりません。下記の事例に基づきま 　　　　　してシート名：就業入力表のＡ列の名前の導き方と時間帯とマスターコードのデータを導く 　　　　　方法を教えて下さい。 　　シート名："マスターコード結合"について 　　 　　違うシート名："マスターコード結合"に、出勤時間と退出時間の表示とコード表を組み合わせて 　　下記の表（１）を作成しています。（その表の必要性は、パートのシフト時間帯が決まった場合に、 　　実際の給与計算をする時に使う就業ソフトにいち早くｲﾝﾌﾟｯﾄする為 です。 　　・そのマスターコード表は６０行程の組み合わせがあります。 　　 　　まず就業ソフトの従業員項目に従業員番号を入れます。次に、勤務体系コード項目を選びます。 　　コード表でリストボックスからその人に合った時間帯のコード番号を選びます。 　　 　　例えば、勤務が８時から１７時迄の場合は、時間に合ったコードＡＦを探しクリックすると、 　　勤務時間帯のデータの表には　ＡＦ２４　８　１７　が表示されます。 　　担当者は、そのコード番号を一人一人選ぶのが大変な為に、勤務体系の一覧表を印刷物として手元に用意 　　し、それを見ながらコード番号を一人一人入力しています。 　　その作業をしている担当者がもう少し早く正確に、入力出来ないかと思い作成したのが 　　（実際は、教えて頂いたのがほとんどですが、）下記の２つのシートのデータです。 　（１）　シート名：マスターコード結合　 　　 　 　　　　　　Ｆ　　　　　　　　　　Ｇ 　　　行　時間結合 　　　勤務体系結合 　　　２　　8_17 　　　　　　　　24 AF 　　　３　　8.5_13 　　　　25 P1 　　　４　　8.5_14 　　　　26 P2　 　　　以降、続きます。 　（２）　シート名：就業入力用 　　　　　　Ａ　　　　Ｂ　　　　　　　Ｃ　　−−−−　ＡＹ２　　　　　 　　　行　　　　　　 　　　１　　　　　従業員Ｎｏ.ｘｘ　従業員Ｎｏ.ｘｘ 　　　２　　　　　名前Ａ　　　　　　名前Ｂ 　　　３　１日　　 　　　４　２日 　　　５　３日 　　　：　　： 　　３３　３１日 　 　　シート名："就業入力表"Ｂ３(Ａさんの１日目のデータ）の式=(IFERROR(VLOOKUP('1日'!$G7&"_"&'1日'! 　　　　　　　　　　　　　$H7,マスターコード結合!2:$G$66,2,FALSE),""))&" "&　 　　　　　　　　　　　　　('1日'!$G7&"― "&'1日'!$H7)　 以上です。勝手ばかり言ってすみません、宜しくお願い致します。 　 （和代） 2014/11/03(月) 17:37 ---- 色んな表が必要なんですね。 以前、こんなお話がありましたが、そう云うことが原因の一つだと思います。 　　　　↓ ＞直接関係ないですが、シート名：給与集計　でコピー、貼り付けとか　数字を入力した後エンターキーを ＞押した後は終わるのが遅く、かなり長い間、丸印が動いています。早く終わる方法はないものでしょうか？ つまり、計算式が多すぎると云うことです。 仕方がない面もあるんでしょうが、 ある段階で、もう計算する必要なくなったものは、 値に変換して置く様にすれば随分軽くなると思います。 数式をいつ値に変えるのかと云えば、給与集計を日にちシートに移す時です。 そこはマクロでやっているんですから、ついでにやらせれば、ユーザーは手間いらずです。 あと、これを給与集計シートの段階で算出して置けば、上の対策と相まって効率的だと思います。 　　　↓ ＞シート名："就業入力表"Ｂ３(Ａさんの１日目のデータ）の式=(IFERROR(VLOOKUP('1日'!$G7&"_"&'1日'! ＞　　　　　$H7,マスターコード結合!2:$G$66,2,FALSE),""))&" "&　 ＞　　　　　('1日'!$G7&"― "&'1日'!$H7) １．<給与集計> (1) CK7セル =MATCH(CI7,AB$7:AB$50,0) (2) CL7セル =IFERROR(VLOOKUP(INDEX(G$7:G$50,CK7)&"_"&INDEX(H$7:H$50,CK7),マスターコード結合!$F$2:$G$66,2,0),"")&" "&INDEX(G$7:G$50,CK7)&"― "&INDEX(H$7:H$50,CK7) 行 __F__ __G__ __H__:: _CH_ __CI__ _CJ_ __CK__ ____ CL ____ 6 名前 出勤 退出 :: 総員 5 出勤行 早見用 7 上田 8 17 :: 上田 0 1 　24 AF 8― 17 8 塩谷4 8 16 :: 山本 0 3 　　　 8― 22 9 山本 8 22 :: 浜本 0 5 8― 21 10 塩谷2 9 14 :: 塩谷1 10 6 ― 11 浜本 8 21 :: 塩谷2 0 4 9― 14 12 :: 塩谷3 12 7 ― ２．日にちシートに転記（マクロ名「COPY｝) 数式の値化も一緒に実行する。 ３．<就業入力表> (1) B3セル =INDEX('1日'!$CL$7:$CL$50,COLUMN(A1)) 行 __A__ ______B______ ___C___ ___D___ __E__ ___F___ __G__ ___H___ __I__ 1 NO 5012 5012 5012 5012 5012 5012 5012 5012 2 名前 上田 山本 浜本 塩谷1 塩谷2 塩谷3 塩谷4 塩谷5 3 1 24 AF 8― 17 8― 22 8― 21 ― 9― 14 ― 8― 16 ― 修正後マクロ(ついでに値に変える) Sub copy() '転記後 AB7:CL50 を値に変える Const cancel As Variant = False Dim dd As Variant Dim eachRow As Range Dim rowsToDel As Range dd = Application.InputBox("何日のシートに貼り付けしますか？", "シート選択", , , , , , 1) If dd <> cancel Then If 1 <= dd And dd <= 31 Then If MsgBox(StrConv(dd, vbWide) & "日　ですか？", vbOKCancel) = vbOK Then Application.ScreenUpdating = False Worksheets("給与集計").Range("A1:CL110").copy With Worksheets(dd & "日") .Range("A1").PasteSpecial .Range("AB7:CL50").Value = .Range("AB7:CL50").Value '値に変換 For Each eachRow In .Range("S7:CG50").Rows If Application.CountBlank(eachRow.EntireRow.Range("G1:H1")) = 2 Then If rowsToDel Is Nothing Then Set rowsToDel = eachRow Else Set rowsToDel = Application.Union(rowsToDel, eachRow) End If End If Next ' .Select 'マクロ実行後、日にちシートが現れる様にするには、行頭のコメントマークを外す ' .Range("A1").Select End With If Not rowsToDel Is Nothing Then rowsToDel.Delete Shift:=xlUp End If Application.ScreenUpdating = True End If End If End If End Sub （半平太） 2014/11/03(月) 23:10 ---- つたない私の説明を隣で見てたかのように、私が望んでいる事を提供して頂き有難うございます。 各関数の組み合わせを勉強させて頂きます。 質問です。 １、「修正後マクロ(ついでに値に変える)」−−−マクロを走らせますと例えば１をクリックすれば 　１日のシートに貼り付けされますが、データのある範囲は値になっていますが、データのないところは 　関数が入ったままです。データの入っていない範囲も値（ブランクになります）にならないでしょうか？ 　半平太さんが言われるように少しは軽くなると思いますがーーー ２、シート名："就業入力表"　の　Ｂ３セルの式を=INDEX('1日'!$CL$7:$CL$50,COLUMN(A1))横列にコピー 　　しました。次にＢ３を含む横列最後までをコピー元とし、下５０行迄を貼り付けました。 　　しかしＢ３セルのCOLUMN(A1)のＡ１はＢ４セルではＡ２と変わりＢ５ではＡ３と変わるのですが、 　　日にちは３１日迄、１日のままです。　２日から３１日迄、関数の’ｘｘ日を手で修正して行かないと 　　いけないのでしょうか？ ３、ＣＫ７=MATCH(CI7,AB$7:AB$50,0)の答えが３になるのはＣＩ７の名前とＡＢ列の名前が同じ場合は、 　　その名前がＭＡＴＣＨ関数でＡＢ列７番目から何番目かを探すのですね、それが３番目の３になる 　　ことですね。 ４、ＡＢ７の式=IF(F7<>"",F7,INDEX(CI:CI,SMALL(CJ$7:CJ$50,ROW()-6)))の意味　とＪＫＬ列の関連性の 　　意味合いを教えて頂けますか？ ５、ＣＪ７の式＝IF(COUNTIF(F$7:F$50,CI7),0,ROW())の意味は、Ｆ列の名前とＣ１７の名前が同じならば。 　　）、０、の意味を教えて下さい。 　　式の答えから見てその行番号という事はわかるのですが‐‐‐ ＊ＣＬは、理解できますが、関数を書けと言われたら難しすぎます。他の関数も結果がそうなるとは 　わかるのですが、関数の組み合わせと関数自体知恵が働きません。 以上よろしくお願い致します。 （和代） 2014/11/04(火) 14:38 ---- ＞ １、「修正後マクロ(ついでに値に変える)」−−−マクロを走らせますと例えば１をクリックすれば ＞　１日のシートに貼り付けされますが、データのある範囲は値になっていますが、データのないところは ＞　関数が入ったままです。データの入っていない範囲も値（ブランクになります）にならないでしょうか？ データの入っていない範囲とはどこですか？ 私は給与集計シートの50行より下がどうなっているのか分かっていないので、 そこには近づいていないですが、そのエリアですか？ ＞２、シート名："就業入力表"　の　Ｂ３セルの式を=INDEX('1日'!$CL$7:$CL$50,COLUMN(A1))横列にコピー ＞　　しました。次にＢ３を含む横列最後までをコピー元とし、下５０行迄を貼り付けました。 ＞ 　　しかしＢ３セルのCOLUMN(A1)のＡ１はＢ４セルではＡ２と変わりＢ５ではＡ３と変わるのですが、 ＞ 　　日にちは３１日迄、１日のままです。　２日から３１日迄、関数の’ｘｘ日を手で修正して行かないと ＞　　いけないのでしょうか？ 提示した数式は、「１日シート」用です。 ２日シート以降は、B列の数式をあと30個修正してしまえば済む話なので特別に配慮しておりません。 INDIRECT関数を使えばコピーで済ませることはできますが、揮発性関数なのでやりません。 （便利さと引き換えに再計算の頻度を上げるのは今回得策ではないと判断します。） ＞３、ＣＫ７=MATCH(CI7,AB$7:AB$50,0)の答えが３になるのはＣＩ７の名前とＡＢ列の名前が同じ場合は、 ＞ 　　その名前がＭＡＴＣＨ関数でＡＢ列７番目から何番目かを探すのですね、それが３番目の３になる ＞　　ことですね。 その通りです。 ＞４、ＡＢ７の式=IF(F7<>"",F7,INDEX(CI:CI,SMALL(CJ$7:CJ$50,ROW()-6)))の意味　とＪＫＬ列の関連性の ＞　　意味合いを教えて頂けますか？ CJ6セル =COUNTIF(CJ7:CJ50,0) の数式は不要でした。　m(__)m CJ7より下は、基本は行番号だが、F列に名前が書いてあるときは「0」にする。 つまり、CJの中で一番小さい値にする。 こうしておくと、(あとでSmall関数を使って）CJ列の値を小さい順に並べたら、 F列に名前がある方が先に来て、F列に名前がない行番号はその後ろに並んでくれることになります。 　　 ＞AB7セル =IF(F7<>"",F7,INDEX(CI:CI,SMALL(CJ$7:CJ$50,ROW()-6))) F列に名前があるうちは、F列の名前をそのまま表示するだけ。 F列に名前がない人の最初は、F列の名前が切れた次の行に表示しなければならない。 その行位置(正確にはそこから6マイナスした値)を、Small関数の指定順位にすると、 0の個数(F列に名前がある数と同じ）の次の順にある行番号を返してくる。 その返り値(行番号）に相当するCI列の名前をINDEX関数を使ってとってくる。 それ以降、F列に名前の無い人は上から順に表示される。(0はさけてである） （半平太） 2014/11/04(火) 16:38 ---- １、「データの入っていない範囲とはどこですか？ 　私は給与集計シートの50行より下がどうなっているのか分かっていないので、」−−−５０行より下では 　なく、ＡＣ（８：００の列）７：ＣＧ（２２：００の列）５０迄の範囲でデータがなく色が塗られていない 　範囲に関数が入っています。（色が塗られている行は関数は入っていません） ２、「２日シート以降は、B列の数式をあと30個修正してしまえば済む話なので特別に配慮しておりま 　　せん。」−−− 　 　　Ｂ列の日付を３１日迄変更していきます。 ４、理解できました。有難うございます。 （和代） 2014/11/04(火) 20:36 ---- ＞ＡＣ（８：００の列）７：ＣＧ（２２：００の列）５０迄の範囲でデータがなく色が塗られていない ＞範囲に関数が入っています。（色が塗られている行は関数は入っていません） あれ？　 日にちシートのその範囲なら、色なしのセルは空白になるハズですよ。(最新版のマクロ"Copy"では） 給与集計シートのその範囲なら、そこは常時数式で残しておかないと不便過ぎます。 ただ、F列に名前がない行は、まともに計算させるのは確かに無駄ですね。 直ちに空白文字を出させてしまう数式に変更した方が計算量が少なくていいです。 以下に変更 AC7セル =IF($F7<>"",REPT(" ",MAX(AND(AC$6>=$G7,AC$6<$H7)*1,AND(AC$6>=$Q7,AC$6=$T7,AC$6=$W7,AC$6=$Z7,AC$6"",REPT(" ",MAX(AND 　　　　　　(AC$6>=$G11,AC$6<$H11)*1,AND(AC$6>=$Q11,AC$6=$T11,AC$6=$W11,AC$6=$Z11,AC$6"",REPT(" ",MAX(AND(AC$6>=$G51,AC$6<$H51)*1,AND(AC$6>=$Q51,AC$6=$T11,AC$6=$W11,AC$6=$Z11,AC$6 cancel Then If 1 <= dd And dd <= 31 Then If MsgBox(StrConv(dd, vbWide) & "日　ですか？", vbOKCancel) = vbOK Then Application.ScreenUpdating = False Worksheets("給与集計").Range("A1:CL110").copy With Worksheets(dd & "日") .Range("A1").PasteSpecial .Range("AB7:CL50").Value = .Range("AB7:CL50").Value '値に変換 For Each eachRow In .Range("S7:CG50").Rows If Application.CountBlank(eachRow.EntireRow.Range("G1:H1")) = 2 Then If rowsToDel Is Nothing Then Set rowsToDel = eachRow Else Set rowsToDel = Application.Union(rowsToDel, eachRow) End If End If Next ' .Select 'マクロ実行後、日にちシートが現れる様にするには、行頭のコメントマークを外す ' .Range("A1").Select End With If Not rowsToDel Is Nothing Then rowsToDel.Delete Shift:=xlUp End If Application.ScreenUpdating = True End If End If End If End Sub 以上です。よろしくお願いします。 　　 　　　 （和代） 2014/11/05(水) 09:46 ---- 給与集計の50行より下がどうなっているのか私は知らされていないので、 そのエリアが絡むお話を急に出されても理解出来ません。 私はちょっと疲れて来ましたので、あとは他の回答者にお任せします。 このトピは長くなり過ぎで、多分、他の回答者のレスが付きにくく なっていると思いますので、新規にトピックをお立てになることをお勧めいたします。 （半平太） 2014/11/05(水) 10:21 ---- 私はちょっと疲れて来ましたので、---本当に迷惑をおかけして申し訳ございませんでした。 そして　今まで、頭の悪い私に、長い時間をおつきあい下さいまして有難うございました。 （和代） 2014/11/05(水) 16:39