はじめての親子ハイキングに挑戦!! >>

職場のシフト表を作るにあたり、下図のF列以降のA~Gの職員に、休日の出勤日はセルを青色にし、その代休日を黄色でで配色しています。また、各職員の出勤希望日を緑、休日希望日を赤で表示して、見た目に分かり易くして、シフトを作成しています。
 問題は、色を入れると同時に、青と緑で表現した休日の出勤シフトをD列に、平日の代休をE列に、A,B等の職員名を表示させたいのですが方法がわかりません。VBA等で上手くできないでしょうか?
よろしくお願いします!

「EXCELでのシフト表作成」の質問画像

A 回答 (1件)

こんばんは!



お示しの画像のF~L列の色付けは手動で行うのですよね。

VBAでの一例です。
↓の画像のように少し配置を変えてみました。
D1~E2にF~L列に塗りつぶす色での各色分けをしておきます。
そしてその列の色と同じ色のセル ↓の画像では3行目 の氏名?を表示するようにしてみました。

Sub Sample1()
 Dim i As Long, j As Long
 Dim c As Range

  For i = 4 To Cells(Rows.Count, "B").End(xlUp).Row
   Cells(i, "D").Resize(, 2).ClearContents
    For j = 6 To Cells(3, Columns.Count).End(xlToLeft).Column '//←F列~3行目最終列まで//
     If Cells(i, j).Interior.ColorIndex <> xlNone Then
      For Each c In Range("D1:E2")
       If Cells(i, j).Interior.Color = c.Interior.Color Then
        With Cells(i, c.Column)
         If .Value = "" Then
          .Value = Cells(3, j)
         Else
          .Value = .Value & "," & Cells(3, j)
         End If
        End With
       End If
      Next c
     End If
    Next j
  Next i
   Range("D:E").Columns.AutoFit
End Sub

※ データ変更があるたびにマクロを実行する必要があります。

※ お示しの画像と↓の画像は1行ずれていますので、注意してください。m(_ _)m
「EXCELでのシフト表作成」の回答画像1
    • good
    • 1
この回答へのお礼

お礼が遅くなり、失礼いたしました。
大変参考になりました。ありがとうございました。

お礼日時:2019/04/24 23:35

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Qマクロ無しで時間自動で記入をしていきたい

すごく贅沢な話なのですが・・・
マクロを使わず、通常のエクセルで時間を手入力でなく何かしらしたら(例:「1」と入力)自動で別セルに現在時刻ほ記載してくれる

マクロだと下記のような内容でやってます(一部)
Range("H3:K33").Select
Selection.Copy
Sheets("結果").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

マクロを使わずやれる方法はないでしょうか・・・

Aベストアンサー

論より 証拠、

作ってみました。


式、
=TEXT(IF(ISBLANK(B1),IF(ISBLANK(A10),B10,TODAY()),"此処が 変わります。"),"rr/mm/dd hh:mm")

ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3GnS4Ko-q3mWU9fP

尚、
ファイルは 1度、
ローカルに 別名保存して、
其の保存ファイルを 扱うように、
してくださいね、

別名保存でないと、
意味が 無いですよ。


さすれば、
閲覧も、編集も、
叶うものと 思いますよ。

Q大きいポイント順にデータを抽出したい

いつもお世話になっております。

元データのポイントの高い順に抽出先のC列、D列へデータを抽出したいです。
VLOOKUPとLARGE関数を使用したのですがうまくいきません。

何卒、ご教授願います。

★元データ

A列:支店コード
B列:支店名 
C列:ポイント

A列 B列   C列
1  ①支店  5
6  ②支店  6
5  ④支店  10

★抽出先

A列:作業列(元データの支店コード)
B列:順位(1位~30位まで)
C列:支店
D列:ポイント

A列 B列 C列  D列
1  1  ④支店 10
2  2  ②支店 6
3  3  ①支店 5

Aベストアンサー

No.5です。

重複データがあり、その合計で降順に表示したい!というコトですね。

一つの数式で出来るかどうか判りませんが、
↓の画像のように作業用の列を2列設けるのが一番簡単だと思います。

作業列1のE2セルに
=IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B:B,B2,C:C),"")

作業列2のF2セルに
=IF(E2="","",COUNTIF(E:E,">"&E2)+COUNTIF(E$2:E2,E2))

という数式を入れ下へずぃ~~~!っとフィル&コピーしておきます。

Sheet2のB2セルに
=IFERROR(INDEX(Sheet1!B:B,MATCH(ROW(A1),Sheet1!$F:$F,0)),"")

C2セルに
=IF(B2="","",SUMIF(Sheet1!B:B,B2,Sheet1!C:C))

という数式を入れフィルハンドルで下へコピー!

これで画像のような感じになります。

※ Sheet2のC2セルは色々やり方があると思います。
例えば
=IFERROR(INDEX(Sheet1!E:E,MATCH(B2,Sheet1!B:B,0)),"")

でも大丈夫だと思います。m(_ _)m

No.5です。

重複データがあり、その合計で降順に表示したい!というコトですね。

一つの数式で出来るかどうか判りませんが、
↓の画像のように作業用の列を2列設けるのが一番簡単だと思います。

作業列1のE2セルに
=IF(COUNTIF(B$2:B2,B2)=1,SUMIF(B:B,B2,C:C),"")

作業列2のF2セルに
=IF(E2="","",COUNTIF(E:E,">"&E2)+COUNTIF(E$2:E2,E2))

という数式を入れ下へずぃ~~~!っとフィル&コピーしておきます。

Sheet2のB2セルに
=IFERROR(INDEX(Sheet1!B:B,MATCH(ROW(A1),Sheet1!$F:$F,0)),"")

C2セルに
=I...続きを読む

Qエクセルでセルのデータがカタカナかどうか調べたい

セルA1の内容が、全角カタカナか半角カタカナか知りたいです。

エクセル関数又はマクロコマンドがあるはずだと信じて探していますが、見つかりません。TYPE関数では、セルの内容が数値か文字列かを調べられますが、その文字列がカタカナか英数字かなどを調べられません。ご存知の方はお教えください。

Aベストアンサー

元の 文字列が、
セルA6に あるとして、
セル内が 全て、
全角片仮名かは、
=SUMPRODUCT((CODE(MID($A$6,COLUMN(OFFSET($A$1,0,0,1,LEN($A$6))),1))=ROW($A$9506:$A$9590))+0)=LEN($A$6)

半角片仮名かは、
=SUMPRODUCT((CODE(MID($A$6,COLUMN(OFFSET($A$1,0,0,1,LEN($A$6))),1))=ROW($A$177:$A$211))+0)=LEN(A6)

で 判りますよ。

Qエクセルについて

自分では何をしてもお手上げなので詳しい方に教えていただきたいです。
画像の様な表(①)があったとして、表(②)の条件を満たす場所ごとに人数を数えたいです。表(②)は上から順に優先順位が高い順です。
しかしその時に条件があり、1つの条件に当てはまった人は他の条件には当てはめてはならないというものです。
例えば優先順位が最も高いA31の場所一はAさん、Hさん、Oさんの3人が当てはまります。
そうなると場所一の残りはVさん1人ということになり、次に優先順位が高いA32には当てはまらないのでA33に入って残りは0ということになります。

上記の例えのように集計するにはどうすれば良いでしょうか。毎日変動する何百とあるデータを集計しなければならないため、知恵をお貸しいただきたいです。

拙い文章で伝わりにくいかもしれませんが、回答よろしくお願いします。

Aベストアンサー

◆概要
I列~L列を作業列として、31~34行目の条件および重複防止から抽出します。
作業列に抽出した表からピボットテーブルを使って、②の表にする。
添付図参照です。データの配置も添付図のとおりです。

◆手順
1.I1~L1セルに条件の見出しを記入する。
2.I2セルに『=IF(AND(H2>=3,E2>=3),1,0)』を記入する。→31行目の条件
3.J2セルに『=IF(AND(G2>=2,E2>=3,I2=0),1,0)』を記入する。→32行目の条件と重複防止
4.K2セルに『=IF(AND(F2>=4,C2>=3,I2=0,J2=0),1,0)』を記入する。
5.L2セルに『=IF(AND(C2>=1,I2=0,J2=0,K2=0),1,0)』を記入する。
6.I2~L2を下方向にコピペする。
7.メニュー→挿入→ピボットテーブル とする。
8.データ範囲にB1~L27を選択して、OK。
9.『ピボットテーブルのフィールドリスト』のウインドウが表示される。
10.大きい箱にある『場所』『31』『32』『33』『34』にチェックを入れる。
11.行ラベルにある『場所』を列ラベルにドラッグして移動させる。
12.Σ値にある31~34をクリックして『値フィールドの設定』→『合計』に変更する。
13.ピボットテーブルで作った表を必要な場所にコピペして完成。


※当方が試したところ、手順13の表の『場所』(一~七)の並び順がずれました。
 同じことが起これば、必要に応じて並べ替えください。

※添付図のデータは、場所 一と二のみサンプルどおりに入力。三以降はでたらめです。

◆概要
I列~L列を作業列として、31~34行目の条件および重複防止から抽出します。
作業列に抽出した表からピボットテーブルを使って、②の表にする。
添付図参照です。データの配置も添付図のとおりです。

◆手順
1.I1~L1セルに条件の見出しを記入する。
2.I2セルに『=IF(AND(H2>=3,E2>=3),1,0)』を記入する。→31行目の条件
3.J2セルに『=IF(AND(G2>=2,E2>=3,I2=0),1,0)』を記入する。→32行目の条件と重複防止
4.K2セルに『=IF(AND(F2>=4,C2>=3,I2=0,J2=0),1,0)』を記入する。
5.L2セルに『=IF(AND(C2>=...続きを読む

QCSVデータ(Test.csv)に A1,B1 A1,B1 A1,C1 A2,B2 A2,B2 A3

CSVデータ(Test.csv)に
A1,B1
A1,B1
A1,C1
A2,B2
A2,B2
A3,B3
A3,B3
というデータが1万レコード入っていたとします。(A1~A100まで100種類)

本来は
A1のペアはB1
A2のペアはB2
A3のペアはB3
となるはずが、上記のように誤ってC1が混在していることに気づく手段はございますでしょうか。

Excelの機能、プログラムなどどんな方法でも構いませんので、Excelフィルタ機能で100回確認する以外の方法がございましたら教えて頂けないでしょうか。

Aベストアンサー

No.5です。

>A列とB列を結合(=A1&"_"&B1)したものを『データ→重複を削除』で重複を削除し、A1で重複しているものをcountifでカウントして2以上のものがおかしい行と判断できる気がしてきました。

具体的なデータがどのようになっているのか不明なので、
C列に A列とB列を連結したデータがいくつあるか?を表示するコードにしてみました。

Sub Sample2()
 Dim myDic As Object
 Dim i As Long, lastRow As Long
 Dim myStr As String
 Dim myR

  Set myDic = CreateObject("Scripting.Dictionary")
   lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    myR = Range(Cells(1, "A"), Cells(lastRow, "C"))
     For i = 1 To UBound(myR, 1)
      myStr = myR(i, 1) & "_" & myR(i, 2)
       If Not myDic.exists(myStr) Then
        myDic.Add myStr, 1
       Else
        myDic(myStr) = myDic(myStr) + 1
       End If
     Next i
     For i = 1 To UBound(myR, 1)
      myStr = myR(i, 1) & "_" & myR(i, 2)
      myR(i, 3) = myDic(myStr)
     Next i
    Range(Cells(1, "A"), Cells(lastRow, "C")) = myR
   Set myDic = Nothing
   MsgBox "完了"
End Sub

これで重複がある場合はC列に2以上の数値が表示されます。m(_ _)m

No.5です。

>A列とB列を結合(=A1&"_"&B1)したものを『データ→重複を削除』で重複を削除し、A1で重複しているものをcountifでカウントして2以上のものがおかしい行と判断できる気がしてきました。

具体的なデータがどのようになっているのか不明なので、
C列に A列とB列を連結したデータがいくつあるか?を表示するコードにしてみました。

Sub Sample2()
 Dim myDic As Object
 Dim i As Long, lastRow As Long
 Dim myStr As String
 Dim myR

  Set myDic = CreateObject("Scripting.Dictionary")
 ...続きを読む

Q急ぎ教えてくださいDの区分に数字をいれたら倍率、支給額まで表示する計算式ありましか? エクセルは我流

急ぎ教えてくださいDの区分に数字をいれたら倍率、支給額まで表示する計算式ありましか?
エクセルは我流で生きてきたので、分かりません。
よろしくお願い申し上げます。

Aベストアンサー

私もNo.2さんのようなことを考えたのですが、基準額とか関係なく区分で金額が固定ならVLOOKUP関数でいいんじゃないですか?

Q「最小化」したファイルが元のサイズに戻ってしまう。

よろしくお願いします。

Excel 2010におきまして、例えば「A」という名前のファイルを開いていて、そのファイルを閉じずに「最小化」して、別の「B」というファイルを開いた際、「A」のファイルが自動で「最小化」が解除されてしまいますが、それを自動にて解除されないように設定することはできますでしょうか?

教えて下さい。

Aベストアンサー

Aファイルの最小化でExcelそのものを最小化したのでしょうか?

この場合だと、Bファイルをエクスプローラなどから開くと。同じ
インスタンスで開くので、Aファイルを開いたExcelでファイルが
立ち上がるので、別インスタンスとしてのExcelを立ち上げてから
Bファイルを開くようにしないと、最小化したものも同じExcel上で
立ち上がるので、元のAファイルも戻ります。
https://www.atmarkit.co.jp/ait/articles/0708/10/news133.html
これはExcel2013以降でも基本的に同じ動作をします。
(No.1の回答者さんが書いているのは、このことを簡潔に回答)

別インスタンスで立ち上げる方法は幾つかあります。以下のサイト
での[送る]を使った方法が一番簡単ではないかと思うので、試して
みてはいかが。。
https://www.atmarkit.co.jp/ait/articles/0708/10/news133_2.html

他に、[ファイル名を指定して実行]でコマンドラインとして /x を
付け、別インスタンスでファイルを起動する方法などもあります。
https://support.office.com/ja-jp/article/Microsoft-Office-%E8%A3%BD%E5%93%81%E3%81%AE%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89-%E3%83%A9%E3%82%A4%E3%83%B3-%E3%82%B9%E3%82%A4%E3%83%83%E3%83%81-079164cd-4ef5-4178-b235-441737deb3a6#ID0EAABAAA=Excel

Aファイルの最小化でExcelそのものを最小化したのでしょうか?

この場合だと、Bファイルをエクスプローラなどから開くと。同じ
インスタンスで開くので、Aファイルを開いたExcelでファイルが
立ち上がるので、別インスタンスとしてのExcelを立ち上げてから
Bファイルを開くようにしないと、最小化したものも同じExcel上で
立ち上がるので、元のAファイルも戻ります。
https://www.atmarkit.co.jp/ait/articles/0708/10/news133.html
これはExcel2013以降でも基本的に同じ動作をします。
(No.1の回答者さんが書い...続きを読む

Qエクセル データの入力規制「リスト」でこんな事できますか?

エクセルでこんな事できますか?
並列したQ列 R列があり、
また、添付には表記されていませんが、
その隣にS列 T列があり、
Q列、S列は、「コード」という事で、
Q列は1,2,3,4,5,6,7,8,9,10
S列は11,12,13,14,15,16,17,18,19,20
です。
R列は、「材質」でQ列に対応し、添付ファイルのように10項目
T列も「材質」でS列に対応し、10項目あります。

R列 T列「材質」を見て Q37セルにコードを入力したく、
その時、ドロップダウンリストとして、R列、続けて、T列の項目を表示したいです。

データの入力規制「リスト」で、できそうかと思い、頑張っていたのですが、
うまくいかず困っています。
データの入力規制「リスト」にはこだわりませんが、
マクロはできるだけ使わず完成したく思います。
エクセル詳しいかたご教授よろしくお願いします。

Aベストアンサー

あー…
入力規則はあくまでも、データ入力を補助するための機能ですからリストと違う文字を反映させることはできませんよ。

例えばリストデータは、01りんご・02プリンのように作って置いて、コードを出す時に
=LEFT(Q37,2)*1
と入れておくとか?

QIF関数とMODで、例えば入れた数値の倍数の時に◯が表示される……という数式を作りましたが、1.1の

IF関数とMODで、例えば入れた数値の倍数の時に◯が表示される……という数式を作りましたが、1.1の時に本当だったら1.1、2.2、3.3、4.4、5.5、6.6、7.7……で◯になるはずなのですが、10以下の数値を入れてみると、3.3、5.5、6.6、7.7、9.9は◯の表示がでてきません。
1.1、2.2、4.4、8.8しか◯が出ません。
どうしてでしょうか?

Aベストアンサー

幼稚な言い方だけど、Excel(に限らないので、PC全般的なことと理解してネ)は“小数点の計算には弱い”と理解しておきませう。
理屈っぽい人はフドーショースーテン(浮動小数点)問題などど言い始めますが・・・
それはさておき、対策を教えます。カンタンなことで、小数点抜きの整数に換算して、Excel に計算させることです。
貴方が提示した式(実はナッチョランでしたが)、を
=IF(MOD($A2*10,B$1*10)=0,"◯","")
に変更するだけで解決します。ゴチャゴチャ考えずに先ずは実行してみること!
どうなりましたか?

Q【エクセル】指定した日付に一番近い日付を抽出する関数について

エクセルの関数について質問です。


A____B_______
佐藤 2011/5/2
佐藤 2011/8/25
佐藤 2012/1/8
山田 2011/6/7
山田 2012/2/13
高橋 2010/7/10
高橋 2010/11/1
高橋 2010/12/5
高橋 2011/2/10

というデータから、




A____B________
佐藤 2011/5/1
佐藤 2011/9/1
山田 2011/6/1
山田 2011/8/1
高橋 2011/1/1
高橋 2011/2/1


のデータの日付に一番近い日付を
①のデータから抽出する関数を教えて頂きたいです。
指定日に一番近い日付を、
未来と過去から抽出する関数です。

分かりづらくて申し訳ございません。
ご教示願います。
よろしくお願いいたします。



iPhoneから送信

Aベストアンサー

もし、
D1に 入力された、
日と 同じ日を、
抜き出したいのなら、
此ですかね?


ファイル、
https://1drv.ms/x/s!AjviygfJDgV_3CyTu_uDNnR5Xlwk


尚、
ファイルは 必ず、
エクセルで 一度、
開き、

ローカルに 別名保存を、
してくださいね、

別名保存でないと、
意味が 無いですよ。


そうすれば、
閲覧も、編集も、
可能に なると、
思います。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング