タイトルではわかりずらいですが、エクセルでシフト表を作成しています。
横に1日~31日、縦に作業内容(仮に15項目ほど)として、
例えば”1日の作業A”を選択するとリストでスタッフの名前が20名出てくるとします。
そして「田中さん」を選択。次に”1日の作業B”も同じく選択するのですが、この時「田中さん」は作業Aで選択済みなので19名の中から選ぶようにしたいのです。
そして作業15項目全て選択したら5人あまりますので、作業内容の項目の下に「公休の人」として
表示させたい(できればですが…)と思ってます。
こちら http://www.officetanaka.net/excel/function/tips/ … が非常に参考になったのですが、2日~31日も同様の仕組みを望んでいます。
ヒントでもなんでも結構ですので、お解かりの方是非お願いいたします。
夕方から数時間ほどしかPCができないので、お返事等は翌日になってしまうかもしれませんが
よろしくお願いします。
No.7ベストアンサー
- 回答日時:
#1です。
マクロはあまり期待されていないと思いますが、もう少し汎用性を上げたコードを投稿しておきます。シフト表の入力する範囲に、あらかじめ「inputArea」という名前を付けておく設定とすることで、汎用性を上げたつもりです。
入力範囲の自動取得をしようとすると、一旦イベント禁止にするなど、面倒になりそうでした。
Sheet2のA列に入れた名前のリストの範囲は自動取得としてあります。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim targetRange As Range, targetColumn As Range, myCell As Range
Dim buf As Variant
Dim strList As String
Set targetRange = Range("inputArea")
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, targetRange) Is Nothing Then Exit Sub
With Sheets("Sheet2")
buf = Application.WorksheetFunction.Transpose(.Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)))
strList = Join(buf, ",")
End With
Set targetColumn = Intersect(targetRange, Target.EntireColumn)
strList = strList & ","
For Each myCell In targetColumn.Cells
If myCell.Value <> "" Then strList = Replace(strList, myCell.Value & ",", "")
Next myCell
strList = Left(strList, Len(strList) - 1)
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strList
End With
End Sub
No.6
- 回答日時:
回答番号:ANo.5です。
申し訳御座いません。
=IF(ROW()>COUNT(AG:AG),"",INDEX($A:$A,SMALL(AG:AG,ROW())))
という数式を入力するセルのセル番号を、Sheet2のAG2セルと書きましたが、それは間違いで、正しくはSheet2のB1セルです。
No.5
- 回答日時:
作業用Sheetを使用する方法です。
今仮に、Sheet1のB1からAF1にかけて1日~31日と入力されていて、Sheet1のA2以下に、作業内容が入力されていて、作業内容の下に
公休の人
と入力されているものとし、Sheet2を作業用Sheetとして使用するものとします。
まず、Sheet2のA2以下に全スタッフの名前を入力して下さい。
次に、Sheet2のAG2セルに次の数式を入力して下さい。
=IF(OR(INDEX($A:$A,ROW())="",COUNTIF(OFFSET(Sheet1!B$1,,,MATCH("公休の人",Sheet1!$A:$A,0)-1),INDEX($A:$A,ROW()))>0),"",ROW())
次に、Sheet2のAG2セルをコピーして、Sheet2のAH2~BK2の範囲に貼り付けて下さい。
次に、Sheet2のAH2~BK2の範囲コピーして、同じ列の3行目以下に、スタッフの人数を上回るのに充分な行数になるまで貼り付けて下さい。
次に、Sheet2のAG2セルに次の数式を入力して下さい。
=IF(ROW()>COUNT(AG:AG),"",INDEX($A:$A,SMALL(AG:AG,ROW())))
次に、Sheet2のB1セルをコピーして、Sheet2のC1~AF1の範囲に貼り付けて下さい。
次に、Sheet2のC1~AF1の範囲コピーして、同じ列の3行目以下に、スタッフの人数を上回るのに充分な行数になるまで貼り付けて下さい。
次に、以下の操作を行って下さい。
Sheet1のB2セルを選択
↓
メニューの[データ]をクリック
↓
現れた選択肢の中にある[入力規則]をクリック
↓
現れた「データの入力規則」ウィンドウの「設定」タグをクリック
↓
「入力値の種類」欄をクリックして、現れた選択肢の中にある「リスト」をクリック
↓
「元の値」欄をクリックしてから、「元の値」欄に次の数式を入力する
=OFFSET(INDIRECT("Sheet2!A1"),,COLUMNS($B:B),MATCH("゛",OFFSET(INDIRECT("Sheet2!A:A"),,COLUMNS($B:B)),-1))
↓
「データの入力規則」ウィンドウの[OK]ボタンをクリック
↓
Sheet1のB2セルにカーソルを合わせて、マウスを右クリック
↓
現れた選択肢の中にある[コピー]をクリック
↓
入力規則を設定するセル範囲を選択
↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスを右クリック
↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
↓
現れた「形式を選択して貼り付け」ウィンドウの中にある[入力規則]と記されている箇所をクリックして、チェックを入れる
↓
「形式を選択して貼り付け」ウィンドウの[OK]ボタンをクリック
次に、Sheet1において、A列で
公休の人
と入力されているセルの右隣のセルに、次の数式を入力して下さい。
=INDEX(Sheet2!B:B,ROW(1:1))
次に、そのセルをコピーして、同じ行のC列~AF列の範囲に貼り付けて下さい。
次に、 次に、Sheet1のA列に
公休の人
と入力されている行の、B列~AF列の範囲をコピーして、同じ列の下方に、スタッフの人数を上回るのに充分な行数だけ、貼り付けて下さい。
以上です。
一夜にして数件の回答ありがとうございます。
直ちに勉強しながら試してみたいと思います。
私のレベルでは多少時間がかかると思いますが
数日の後ご報告させてもらいます。
本当にありがとうございました。
No.4
- 回答日時:
全5人、作業項目3つ、5日までとして
0.1 A11:A15セルに 作業員名簿
1.1 B10セルに1
1.2 右へオートフィル(すべて1)
2.1 B11セルに =ISNA(MATCH($A11,B$2:B$4,0))+B10
2.2 下へオートフィル
2.3 右へオートフィル
3.1 B5セル =INDEX($A$11:$A$15,MATCH(ROW(A1),B$10:B$14))
3.2 下へオートフィル
4.1 B7セル(5は作業員全人数)
=IF(5-COUNTA(B$2:B$4)<ROW(A3),"",
INDEX($A$11:$A$15,MATCH(ROW(A3),B$10:B$14)))
4.2 下へオートフィル
5.1 B2:B5セルを選択して 入力規則
リスト 元の値: =B$5:INDEX(B$5:B$9,B$15-1)
6.1 B2:B9セルを右へオートフィル
考え方としては、入力前は全員公休。
ただし、リストのためにはみ出し部分を作る(B7:B9)
そのはみ出し部分も含め入力規則で参照する
一夜にして数件の回答ありがとうございます。
直ちに勉強しながら試してみたいと思います。
私のレベルでは多少時間がかかると思いますが
数日の後ご報告させてもらいます。
本当にありがとうございました。
No.3
- 回答日時:
#2です。
#2のままでは、ところ構わず入力規則を設定してしまうので、下記コードを
>Const startRow As Long = 2 'データは2行目から設定するとする
の後ろに付け加えてください。
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:AF16")) Is Nothing Then Exit Sub
なお、A2:AF16のところは、実際に入力規則を設定したい範囲に変更して下さい。
No.2
- 回答日時:
Sheet1のA列にA2から作業名が入っていて、一行目にB1から行方向に日付が入っているとします。
Sheet2のA列にA1から、スタッフの名前のリストがあるとします。名前のリストの範囲に、nameListという名前をつけておきます。
動的な名前にしておくと融通が利きます(未経験でしたら参考URLをご覧下さい)
Sheet1のシートモジュールに下記コードを記述します。
思いつきでこしらえたので、お気に召したら(うまく動いたら?)幸いです。当方xl2000です。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim strList As String
Dim buf As Variant
Dim targetRange As Range, myCell As Range
Const startRow As Long = 2 'データは2行目から設定するとする
buf = Application.WorksheetFunction.Transpose(Sheets("Sheet2").Range("nameList"))
strList = Join(buf, ",")
Set targetRange = Range(Cells(startRow, 1), Cells(Rows.Count, 1).End(xlUp)).Offset(0, Target.Column - 1)
strList = strList & ","
For Each myCell In targetRange.Cells
If myCell.Value <> "" Then strList = Replace(strList, myCell.Value & ",", "")
Next myCell
strList = Left(strList, Len(strList) - 1)
With Target.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strList
End With
End Sub
シートのイベントマクロについては下記をご参照下さい。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/v …
参考URL:http://office.microsoft.com/ja-jp/excel-help/HA0 …
一夜にして数件の回答ありがとうございます。
直ちに勉強しながら試してみたいと思います。
私のレベルでは多少時間がかかると思いますが
数日の後ご報告させてもらいます。
本当にありがとうございました。
No.1
- 回答日時:
こんばんは!
直接の回答とはいかないと思いますが・・・
一つのヒントになれば良いと思います。
↓の画像で説明します。
Sheet2に表を作成しておきます。
A列は単に全員(20名)の名前を羅列。
B2セルに
=IF(COUNTIF(Sheet1!$B$2:$B$16,A2),"",ROW(A1))
C2セルに
=IF(ISERROR(INDEX($A$2:$A$21,SMALL(IF($B$2:$B$21<>"",ROW($A$1:$A$20)),ROW(A1)))),"",INDEX($A$2:$A$21,SMALL(IF($B$2:$B$21<>"",ROW($A$1:$A$20)),ROW(A1))))
C2セルについては配列数式になってしまいますので、この画面からSheet2のC2セルにコピー&ペーストしてだけではちゃんと表示されないはずです。
C2セルに貼り付け後、F2キーを押す、またはC2セルをダブルクリック、または数式バー内で一度クリックし
編集可能にしたのちに、Shift+Ctrlキーを押しながらEnterキーで確定します。
そして、B2・C2セルを範囲指定しC2セルのフィルハンドルで下へ最終行までコピー
このC2~C21セルを範囲指定し名前定義しておきます。(範囲指定後、直接名前ボックスに入力してもOKです)
仮に リスト と名前定義したとします。
Sheet1のB2~B16セルを範囲指定 → データ → 入力規則 → リスト → 元の値の欄に
=リスト
としてOK
これで順にリスト表示すると残ったものがリスト候補として表示されます。
A19セルには
=IF(COUNTBLANK($B$2:$B$16),"",Sheet2!C2)
という数式を入れ、5行ほどオートフィルで下へコピーしています。
これで何とか希望に近い形にならないでしょうか?
ただ・・・
リスト候補が少なくなるにつれてリスト表示の欄が空白ばかりになりますので、そこが難点かもしれません。
以上、参考になれば良いのですが
他に良い方法があればごめんなさいね。m(__)m
一夜にして数件の回答ありがとうございます。
直ちに勉強しながら試してみたいと思います。
私のレベルでは多少時間がかかると思いますが
数日の後ご報告させてもらいます。
本当にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- JavaScript jsで診断コンテンツのページ内切り替えについて 1 2023/04/14 17:31
- Excel(エクセル) エクセルについて教えてください。 2 2023/06/14 11:11
- JavaScript セレクトを全て選択されていないと、文字によるエラーメッセージを表示させるコードを調べています 2 2023/06/22 15:48
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- 確定申告 e-taxで開業届を出す方法がわかりません 1 2022/06/01 19:00
- Excel(エクセル) エクセルの数式で教えてください。 1 2022/10/25 09:26
- Visual Basic(VBA) エクセルのマクロについて教えてください。 2 2023/07/06 17:46
- フリーソフト 使用期間切れのDVDfab12でDVDをコピーしようとした時のエラー表示について 1 2022/09/09 01:58
- JavaScript jquery 診断コンテンツにチェックボックスを付けたいです 3 2023/01/19 18:31
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
VLOOKUP関数について
-
1つのPCに「Excel 2010」「Exc...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
Office 2021 Professional Plus...
-
会社PCのメールが更新されない
-
マイクロソフト オフィスについて
-
vb.net オブジェクト指向につい...
-
【スプレッドシート】白色のセ...
-
Microsoft Formsの「個人情報や...
-
Excel VBA 日程表からスケジュ...
-
スプレッドシートにて、条件付...
-
【スプレッドシート】最初の契...
-
teams設定教えて下さい。 ①ビデ...
-
Windows 11で、IME言語バー(IM...
-
大学のレポート A4で1枚レポー...
-
outlookで宛先が異なるメールを...
-
エクセルで例えば、A1に㈱ベ...
-
Googleのスプレッドシートでシ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報