[[20190128180637]]　『範囲内に「休」と入力すると、両脇に文字が出る様』（なのれい）　ページの最後に飛ぶ

『範囲内に「休」と入力すると、両脇に文字が出る様にしたいです。』（なのれい）

初めての質問になります。
宜しくお願いします。

縦10、横31のセル内で「休」と記入した場合
、その左隣を「昼」、右隣を「夜」となる様にしたいです。

ifで次の通りやってみたんですが、循環のエラーが出て出来ませんでした。

右隣が「休」の場合「昼」、左隣が「休」の場合「夜」をifで作ったのですが、１つのセルでは問題ありませんでした。

しかし、縦10、横31に式を広げると循環のエラーが出て出来ませんでした。

どの様な方法でも構わない名ですが、
1～31日までのセル内に不定休で「休」と手入力をすると、両脇に「昼」「夜」と出て昼番、休み、夜番にしたいという感じです。

誰かご教授宜しく御願い致します。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

数式だと、循環参照にならないように工夫できたとしても「休」と記入することになるセルと、数式を入れておくセルが、同じになりそうな気がしますので、かなり難しいのでは？

そうなると、マクロのchangeイベントで処理という発想になりそうですが、質問者さんはマクロをさわったことはありますか？

（もこな２） 2019/01/28(月) 19:36

返答ありがとうございます。

やっぱり数式は難しいですか。
コピペしたものに手入力していくので、「休」など手入力するセルは数式は消えても問題ないのですが、
それでもやはり難しいでしょうか?

マクロ触った事ないですね。マクロで出来るならマクロ勉強します。

（なのれい） 2019/01/28(月) 19:48

とりあえず、、縦10、横31がどのセルを起点としてるのかわからないのでB1セルが起点だったとして、シートモジュールに、↓を貼付れば思ったような動きになると思います。

    Private Sub Worksheet_Change(ByVal Target As Range)

        Stop '←　ブレークポイントの代わり

        Dim tmp As Range

        If Intersect(Target, Range("B1").Resize(10, 31)) Is Nothing Then Exit Sub

        Application.EnableEvents = False 'イベント停止

        For Each tmp In Intersect(Target, Range("B1").Resize(10, 31))
            If tmp.Value = "休" Then
                tmp.Offset(, -1).Value = "昼"
                tmp.Offset(, 1).Value = "夜"
            End If
        Next tmp

        Application.EnableEvents = True 'イベント再開

    End Sub

※STOPのところで一時停止するようになっているので、ステップ実行して動きを観察してみてください。

【ステップ実行】
https://www.239-programing.com/excel-vba/basic/basic023.html

（もこな２） 2019/01/28(月) 20:37

＞手入力するセルは数式は消えても問題ない
なら簡単

数式なら=" "とでもします。（もちろん値でもＯＫ）
条件付き書式の表示形式で
　数式=OFFSET(※,0,1)="休" 　表示形式 ;;;"昼"
　数式=OFFSET(※,0,-1)="休" 　表示形式 ;;;"夜"
とします。

上記の※は自セルのアドレスです。
R1C1形式なら、OFFSET(RC,0,1)="休" です。

（チオチモリン） 2019/01/28(月) 20:45

もこな2さん、チオチモリンさん
返答頂きありがとうございます。

帰ったら挑戦してみます！！
（なのれい） 2019/01/28(月) 21:04

もこな2さんのコードで解決致しました。
ありがとうございます。

追加で質問したいのですが、10人×31日の『昼　休　夜』のパターンの下に、他で11人目12人目13人目と3人程『夜　休　夜』のパターンを作りたいです。

コードをマネして色々挑戦したのですが、上手くいきませんでした。
宜しくお願い致します。
（なのれい） 2019/01/29(火) 21:26

＞もこな2さんのコードで解決致しました。
提案しておいてなんですけど、マクロだといろいろ制限もでてくるので、使わずに済む方法があるなら、そちらを採用したほうがよさそうに思います。

それでもマクロで進めるという場合。
＞コードをマネして色々挑戦したのですが、上手くいきませんでした。
どのようにいろいろ挑戦して、どのように上手くいかなかったのか提示があるとアドバイスできることがあるかもしれません。

（もこな２） 2019/01/30(水) 05:59

もこな2さん返信ありがとうございます。
マクロを使わず、関数で解決出来るなら関数でやりたいです。

　数式=OFFSET(※,0,1)="休" 　表示形式 ;;;"昼"
　数式=OFFSET(※,0,-1)="休" 　表示形式 ;;;"夜"

マクロを使用せず、上記で出来るという認識でよろしいでしょうか？
（なのれい） 2019/01/30(水) 10:46

チオチモリンさんに聞かれたほうが丁寧に説明してくださるとおもいますが、
私がわかる範囲で説明すると

◆表示形式の説明

適当なセル（例えばB1セル）で右クリックをして

 (1)セルの書式設定
 (2)「表示形式」タブ
 (3)「分類」のなかから「ユーザー定義」を選択
 (4)「種類」のところに【#,###;△#,###:"":"あ"】
と入れてから、書式設定したセル（例ではB1セル）に
  1234  と入力すると   1,234
 -5678 と入力すると  △5,678
     0 と入力すると　見た目はブランクセル（実際には０文字の文字が表示されている）
    い と入力すると  あ

とそれぞれ【表示】されるとおもいます。
これは、

 [正の数の場合];[負の数の場合];[ゼロの場合];[文字列の場合]

をそれぞれで設定しているからです。

 ※私も昨日しらべて分かったことなので間違っているかも・・・
   識者のフォローをお願いします。　＞All

上記が理解できれば、たとえば

 表示形式 ;;;"昼"

となっているときに、セルの値が文字列であればどうなるかわかりますよね？
答えは、どんな文字列であろうと、「昼」と【表示】されます

◆マクロでも関数（数式）でもない方法
チオチモリンさんが投稿されたものは、この表示形式の切り替えをマクロでも数式でもなく、一般機能の「条件付き書式」で行ってしまおうというアイデアなのです。

◆条件付き書式の説明
やり方は
　(1)条件付き書式を設定したい範囲を選択（例　A1:G10）
　(2)ホームタブ　-　条件付き書式　をクリック

  (3)新しいルール　-　「数式を使用して、書式設定するセルを決定」
  (4)「次の数式を満たす場合に～」のところに、【=OFFSET(A1,0,1)="休"】
  (5)書式 - 表示形式タブ - ユーザー定義 のところで【;;;"昼"】を入力（選択）
  (6)セルに何らかの文字が入っている必要がありますから、書式設定したセル全体に
     「=""」なり、半角スペースなりを入力
  (7)適当なセルに「休」と入れたら、右側セルに「昼」と出れば成功

となります。「夜」のほうは同じ感じでルールを追加すればよいはずです。

留意点として、複数セルの数式を使った条件付き書式をいっぺんに設定するときは、$をつけない状態（相対参照）で書くようにしてください。絶対参照で書いて市宇都、どのセルからみても基準セルが固定されるようになってしまいます。

ちなみに、条件がTrueになればよい（Falseにならなければよい）ので、OFFSET関数がよくわからなければ、
【=OFFSET(A1,0,1)="休"】　→　【=B1="休"】
としても多分大丈夫です。（Excel2010では問題なし）

（もこな２） 2019/01/30(水) 22:09

すみません。↑誤字りました。

 ×　絶対参照で書いて市宇都

 ○　絶対参照で書いてしまうと

です。

（もこな２） 2019/01/30(水) 22:12

もこな2さんありがとうございます。

解決致しました。
記載している他にも、特定の人の上が夜なら特定の人を昼にするなど上手く活用する事が出来ました。

ありがとうございます。

一つ確認したい事があるのですが、セル内に入力されている「=""」の状態から「休」を手入力していくわけなんですけども、

シフト作り終えた後のセル内は

セル内は「=""」　表示は「昼」
セル内は「=""」　表示は「夜」
手入力した「休」

の3パターンになっています。

各列の「休」の数を関数で数えれると思うですが、「昼」と「夜」は関数で数える事は出来ませんよね？

宜しくお願い致します。

（なのれい） 2019/01/31(木) 08:22

ウ～ン。やっぱりそういうことしたかったんですねぇ

お気づきのとおり、【表示】されているだけで、値は変わっていませんからね…
マクロであれば、textブロパティをみればよさそうに思うのですが、数式あるいは一般操作となると私には思い付かないです。

ただ、休を1つ飛ばしか、並べて書かない限りないは、「休」、「昼」、「夜」の数って同じになりません？

（もこな２） 2019/01/31(木) 20:22

もこな2さん返信ありがとうございます。

「休」は数えれるので問題ないのですが、「昼」「夜」に関しては昼専門の人、
夜が多めの人、休みが多い人、夜休夜のパターンの人、昼よりも夜に従業員を多めにしないといけない

など条件が多い為、数えながら作る必要があったんです。
先に伝えておかなければならなかったですね。
申し訳ありません。

やはりマクロを勉強するしかないでしょうか？
（なのれい） 2019/02/01(金) 01:46

ということは、手入力の「昼」と表示上の「昼」が混在してるって状況になっているんですよね。

表示上の「昼」は「休」と同数で、手入力の「昼：はCOUNTIF関数で数えられるでしょうから、「休」あるいは「昼」となっているセルの数を数えてみてはどうでしょうか。
（別々に求めて足してもいいですけど）

（もこな２） 2019/02/01(金) 09:12

もこな2さん返信ありがとうございます。

「昼」と「夜」は2種類ずつになっております。

1箇所もミスらずシフトを作る事が出来れば上記の数え方でも問題ないかもしれませんが、

ミスってしまった場合や、急なシフト変更、他人（部下）が使用する場合などの為に分かりやすくしておく必要がございます。

提案して頂き本当に感謝しておりますが、その方法は取り入れる事が出来ません。
申し訳ありません。
（なのれい） 2019/02/01(金) 13:26

現在、もこな2さんのコードを使用させて頂きつつ挑戦しております。

   Private Sub Worksheet_Change(ByVal Target As Range)
        Stop '←　ブレークポイントの代わり
        Dim tmp As Range
        If Intersect(Target, Range("B1").Resize(10, 31)) Is Nothing Then Exit Sub
        Application.EnableEvents = False 'イベント停止
        For Each tmp In Intersect(Target, Range("B1").Resize(10, 31))
            If tmp.Value = "休" Then
                tmp.Offset(, -1).Value = "昼"
                tmp.Offset(, 1).Value = "夜"
            End If
        Next tmp
        Application.EnableEvents = True 'イベント再開
    End Sub

   Private Sub Worksheet_Change(ByVal Target As Range)
        Stop '←　ブレークポイントの代わり
        Dim tmp As Range
        If Intersect(Target, Range("B11").Resize(3, 31)) Is Nothing Then Exit Sub
        Application.EnableEvents = False 'イベント停止
        For Each tmp In Intersect(Target, Range("B11").Resize(3, 31))
            If tmp.Value = "休" Then
                tmp.Offset(, -1).Value = "夜"
                tmp.Offset(, 1).Value = "夜"
            End If
        Next tmp
        Application.EnableEvents = True 'イベント再開
    End Sub

上記の様にシートモジュールに入力しました。
私は知識が乏しいので、マクロボタンを２つ作り１つは上の「昼休夜」のプログラム、
もう１つは「夜休夜」のプログラムというが良いんじゃないかと思いました。

しかし、そもそもマクロボタンに登録すら出来ない様な状態です。

アドバイス頂けないでしょうか？
宜しくお願い致します。

（なのれい） 2019/02/02(土) 11:54

とりあえず、 Worksheet_Changeなどは、イベントといって、特定の操作をしたときに、自動的に発動するものなので、マクロボタンに登録するものじゃないです。

ちなみに、Worksheet_Changeはシート上のいずれか（複数でもOK）のセルの値が変更されたときに発動します。
そして、記述すべき場所もきまっていて、シートのイベントであれば【シート】モジュールというところに記述します。

モジュールとは何ぞやとはこちらが参考になりそうです
http://officetanaka.net/excel/vba/beginner/10.htm

（もこな２） 2019/02/02(土) 12:09

↑で書き忘れ。

同じモジュール内に同じイベントは１つしか記述できません。

 例；Sheet1モジュールのなかに
   Sub Worksheet_Change(ByVal Target As Range)
      ...
   End Sub

   Sub Worksheet_Change(ByVal Target As Range)
      ...
   End Sub

↑のようにChangeイベントを2つ書くことはできません。

（もこな２） 2019/02/02(土) 12:14

もこな2さん返信ありがとうございます。

そうだったんですね。了解致しました。
何度も回答頂き本当にありがとうございます。
（なのれい） 2019/02/02(土) 13:04

ボタンに登録したければ、↓みたいなのを標準モジュールに書いて、登録すればよいですが

    Sub さんぷる()
        Dim MyRNG As Range

        For Each MyRNG In ActiveSheet.Range("B1:AF10")
            If MyRNG.Value = "休" Then
                MyRNG.Offset(, -1).Value = "昼"
                MyRNG.Offset(, 1).Value = "夜"
            Else
        Next MyRNG

        For Each MyRNG In ActiveSheet.Range("B11:AF13")
            If MyRNG.Value = "休" Then
                MyRNG.Offset(, -1).Value = "夜"
                MyRNG.Offset(, 1).Value = "夜"
            Else
        Next MyRNG

    End Sub

追加で提示があった条件の

ミスってしまった場合や、急なシフト変更、他人（部下）が使用する場合などの為に分かりやすくしておく必要がございます。

に照らせば分かりやすいかと言われればマクロを使ってる時点で分かりづらいとおもうし、マクロ動かし忘れたらそのままミスにつながるから、意味ないんじゃない？とおもいます。

（もこな２） 2019/02/02(土) 14:32

もこな2さん返信ありがとうございます。

了解致しました。
２つのやり方で何ヶ月間かやってみて、作業しやすい方にしようと思います。

コード書いて頂きありがとうございます。

（なのれい） 2019/02/02(土) 14:55

コメント返信：

[ 一覧(最新更新順) ]

[[20190128180637]] 『範囲内に「休」と入力すると、両脇に文字が出る様』（なのれい） ページの最後に飛ぶ

[[20190128180637]]　『範囲内に「休」と入力すると、両脇に文字が出る様』（なのれい）　ページの最後に飛ぶ