KE1

スケジュール表に予定が入力されている日付を、カレンダーに自動で色付けする方法について紹介していきます。

以下の例では、予定がある日付が黄色で色付けされています。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. 準備

以下のようなシートを用意します。

今回の設定を１度行えば、年月日を変更しても活用することができます。
日付の「年、月、日」が入力されているセルには、それぞれの値を表す数値のみが入力されています。

こちらでは、同じシート上に、カレンダーとスケジュール表を用意していますが、必要に応じて別のシートに分けても実現できます。

2. 色付けする条件

では次に、カレンダーを色付けする条件を具体的にしていきます。

まずは、１日を色付けするかどうかを判定する条件について考えていきます。
カレンダーの１日のセルH4を色付けするときは、J列の１日の行のL列の予定に何かしら値が入力されているときです。

カレンダーのセルやスケジュール表のセルは、日によって異なります。
１つ１つのセルに条件付き書式を個別に設定するのは大変なので、共通で使える条件を考える必要があります。

その条件は、以下になります。

スケジュール表の日付の中で、カレンダーの対象セルの値と一致し、尚且つ、スケジュール表の対象行の予定に何かしら値が入力されている場合

この内容を数式で表現すると、以下のようになります。

=COUNTIFS(J4:J34,B4,L4:L34,"<>")
// ★カレンダーのセルB4を色付けするかどうかの条件
// J4:J34の中でB4の値と一致した行のL列が空でない場合
// 上記の条件を満たしているデータの件数を返す
// → 対象がない場合は結果が「0」になる

3. 条件付き書式の設定

次に、先ほどの以下の条件式を元に、条件付き書式に設定していきます。

=COUNTIFS(J4:J34,B4,L4:L34,"<>")
// ★カレンダーのセルB4を色付けするかどうかの条件
// J4:J34の中でB4の値と一致した行のL列が空でない場合
// 上記の条件を満たしているデータの件数を返す
// → 対象がない場合は結果が「0」になる

上記の数式のように、結果が「TRUE/FALSE」でない数値の場合、結果が「0」ならFALSE、「0以外」ならTRUE扱いになります。

まずは、カレンダーの日付の範囲を全て選択し、[ホーム]タブの中から[条件付き書式]の[新しいルール]を選択します。

条件付き書式の設定画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]を選択します。

表示されたテキストボックスに、先ほどの条件式を入力するのですが、注意点があります。
入力した条件式は、選択基準のセルを軸に、他の範囲に相対参照で反映されます。

以下のように、セルB4を基準に選択している場合は、先ほどの条件式が使えるのですが、他の範囲には正しく反映されません。

=COUNTIFS(J4:J34,B4,L4:L34,"<>")

正しく反映させるためには、スケジュール表の範囲を以下のように固定する必要があります。

=COUNTIFS($J$4:$J$34,B4,$L$4:$L$34,"<>")
// $J$4:$J$34：スケジュール表の日付の範囲（絶対参照）
// B4：カレンダーの基準のセル（相対参照）
// $L$4:$L$34：スケジュール表の予定の範囲（絶対参照）

上記の条件式を設定画面に入力し、[書式]より「条件を満たしたときに反映させる書式」を指定します。
以下の画面では、[塗りつぶし]タブから黄色を指定しています。

上記の設定で確定することで、完成です。

4. 完成

以下のように、予定が入力されているカレンダーの日付のみ色付けされることが確認できます。

年月日に関しては、必要に応じて変更していただけます。

▼サンプルファイル▼

１人１つの役割で、役割ごとに必要な人数が決まっている場合、以下の表のようだと、どこに何人割り当てたのかが分かりづらく、誤った割り当てにも気づきにくいです。

そこで今回は、１つ１つ割り当てると同時に、重複入力や人数超過の可能性のある項目が自動で色付けされる仕組みの作り方について紹介します。

※こちらで作成したファイルは、記事の最後にて配布しています。

1. 実現方法

値を入力すると同時に、ある条件を満たしたセルを色付けするには『条件付き書式』を活用します。
単純な条件でない場合は「色付けする条件」を数式で表現して、条件付き書式に設定する必要があります。

2. 条件式の用意

まずは、特定のセルについて、色付けする条件を考えていきます。

例えば、セルC4の場合、

このセルを色付けする必要があるときは、以下の複数の条件を満たしたときになります。

・セルC4は空 ・C列（セルC4～C13）に入力されている値の数がセルC3の必要人数を満たしている、もしくは、４行目（セルC4～G4）のいずれかに値がすでに入力されている

この複数の条件を数式で表現すると、以下のようになります。

=AND(C4="",OR(COUNTA(C4:C13)>=C3,COUNTA(C4:G4)>=1))

他のセルについても考えると、以下のようになります。

【セルC5】

=AND(C5="",OR(COUNTA(C4:C13)>=C3,COUNTA(C5:G5)>=1))

【セルC6】

=AND(C6="",OR(COUNTA(C4:C13)>=C3,COUNTA(C6:G6)>=1))

【セルD4】

=AND(D4="",OR(COUNTA(D4:D13)>=D3,COUNTA(C4:G4)>=1))

【セルD5】

=AND(D5="",OR(COUNTA(D4:D13)>=D3,COUNTA(C5:G5)>=1))

ここから分かることは、必要人数を確認する範囲の行番号、各行の値の有無を確認する範囲の列名は、以下のように「$」で固定する必要があるということです。

=AND(C4="",OR(COUNTA(C$4:C$13)>=C$3,COUNTA($C4: $G4)>=1))

3. 条件付き書式の設定

作成した以下の数式は、セルC4を基準にした条件式です。

=AND(C4="",OR(COUNTA(C$4:C$13)>=C$3,COUNTA($C4: $G4)>=1))

そのため、セルC4を基準にした色付けする可能性のある範囲（入力欄）を選択します。

次に、[ホーム]タブから[条件付き書式]の[新しいルール]を選択します。

条件付き書式の設定画面にて、[ルールの種類]を[数式を使用して、書式設定するセルを決定]にし、テキストボックス内に先ほどの条件式を入力します。

次に、[書式]の[塗りつぶし]タブから色付けする色を指定します。

上記のように設定して、確定することで完成です。

4. 完成

以下のように、役割を割り当てると同時に、割り当て済みの範囲が自動で色付けされます。

必要に応じて、ドロップダウンリストなどで値を入力しやすくすると良いです。

▼サンプルファイル▼

特定の条件を満たしたときにメッセージを表示する例として、こちらでは以下のように、金曜日の勤怠を登録するときに「終業時刻」を入力したときのみ、メッセージを表示する方法について紹介します。

※こちらで作成したファイルは、記事の最後にて配布しています。

メッセージの表示方法

メッセージを表示するには「データの入力規則」を活用します。
データの入力規則というと、セルへの入力制限を設ける機能というイメージがありますが、実は単純にメッセージを表示したいときにも活用できます。

今回の場合、以下の表の「終業時刻」の項目がメッセージを表示する可能性のある対象になります。

そのため、「終業時刻」の範囲を選択して、[データ]タブから[データの入力規則]を選択します。

表示される以下の設定画面にて、まずは入力制限を行うのではなく、メッセージを表示する設定をします。

特定の条件を満たしたときに、メッセージを表示するには、[エラー メッセージ]タブにて、[スタイル]を[情報]にします。
そして、表示したいメッセージを[タイトル]と[エラー メッセージ]のテキストボックスに入力します。

これだけで、メッセージの設定は完了です。
後は、[設定]タブにて、メッセージを表示する条件を設定するだけです。

条件の設定

今回、メッセージを表示する条件は「金曜日の勤怠を登録したとき」になります。
具体的には、「入力したセルと同じ行のA列の日付が金曜日のとき」です。
それを、以下の[設定]タブにて設定していきます。

今回のような、他のセルの値を参照した条件を指定するには、[入力値の種類]を[ユーザー設定]にし、[数式]に条件を指定します。

[数式]に指定する条件は、選択基準のセルに対しての条件になります。
上記の場合は、セルC2が選択基準なので、「A2に入力されている日付が金曜日の場合」という条件になります。

ただ注意しないといけないのは、[数式]に設定した条件は、正しくは「メッセージを表示しないとき」の条件になります。
そのため、設定する条件は、「A2に入力されている日付が金曜日の場合」ではなく「A2に入力されている日付が金曜日でない場合」になります。
この条件を数式で表現すると、以下のようになります。

=WEEKDAY(A2)<>6
// WEEKDAY(A2)：指定した日付の曜日を1（日）～7（土）の数値で返す
// <>6：上記の結果が6でないとき＝金曜日でないとき

この条件式を[数式]に入力します。

他の選択範囲には、相対参照で反映されます。
セルC3には「A3に入力されている日付が金曜日でない場合」といった感じです。

これで確定することで完成です。

完成

A列の「日付」の項目に入力した日付が「金曜日以外」の場合は、C列の「終業時刻」の項目に値を入力しても、メッセージは表示されません。

A列の「日付」の項目に入力した日付が「金曜日」の場合は、C列の「終業時刻」の項目に値を入力して確定すると同時に、以下のようにメッセージが表示されます。

メッセージの[OK]を選択すると、メッセージが消えて入力が確定します。

▼サンプルファイル▼

以下のように、メールなどで送られてきた文章（B2:D16）から特定の値を表形式で抽出（F3:G7）する方法について紹介していきます。

簡易的な文章から表形式で抽出

元々の文章が以下の内容の場合、抽出対象（商品名：数量）以外にも不要な文章が含まれています。（赤文字）

〇〇様 お世話になっております。 今月の弊社商品在庫状況を以下の通りご報告いたします。 サウンドビー：5 タイムウォッチ：8 タブレットプロ：15 ビートスタジオ：6 ペンマスター：18 ご確認のほど、よろしくお願いいたします。 何かご不明点や追加のご要望がございましたら、お気軽にお知らせください。

そのため、まずは抽出対象の文章のみをシートに貼り付けて、そこから表形式になるように数式で抽出していきます。

文章を表形式で抽出する場合、抽出するルールを決める必要があります。
今回の場合は、以下のようなルールになります。

・「：」で列を区切る ・「（改行）」で行を区切る

このルール通りに抽出するためには、TEXTSPLIT関数を活用します。
実際に活用した数式は、以下になります。

=TEXTSPLIT(B2,"：",CHAR(10))
// B2：抽出元の文章が入力されたセル
// "："：列の区切り文字
// CHAR(10)：行の区切り文字（改行はCHAR関数を用いて表現）

上記の数式を抽出先の表の先頭セル（E3）に入力することで、以下のように表形式で抽出することができます。

とはいえ、メールなどの文書から特定の文章を毎回抽出するのは面倒です。
そのため、文章を全て貼り付けるだけで、対象の値を表形式で抽出する方法についても紹介していきます。

本文全体から対象のみを表形式で抽出

本文全体が以下のようにパターン化している場合（赤文字）は、簡単な数式を加えるだけで、対象の範囲の抽出までを自動で行うことができます。

〇〇様 お世話になっております。 今月の弊社商品在庫状況を以下の通りご報告いたします。 サウンドビー：5 タイムウォッチ：8 タブレットプロ：15 ビートスタジオ：6 ペンマスター：18 ご確認のほど、よろしくお願いいたします。 何かご不明点や追加のご要望がございましたら、お気軽にお知らせください。

まずは、この本文のままシートに貼り付けて、先ほどの数式で抽出してみます。

=TEXTSPLIT(B2,"：",CHAR(10))

そうすると、以下の赤枠の箇所が不要になります。

抽出された表の上から５行、下から３行が不要になるので、この不要な行を数式で除外します。

表から不要な行を除外する場合、DROP関数を活用すると良いです。
実際に活用した数式は、以下になります。

=DROP(DROP(TEXTSPLIT(B2,"：",CHAR(10)),5),-3)
// DROP(…,-3)：「…」の末尾から3行を除外
// DROP(…,5)：「…」の先頭から5行を除外

上記の数式を抽出先の表の先頭セル（E3）に入力することで、以下のように不要な行を除外した表形式で抽出することができます。

以下のように、特定の項目の、要素の順番を回転させる方法について、２通りで解説していきます。

※こちらで作成したファイルは、記事の最後にて配布しています。

方法１

１つ目は、末尾のデータを切り取りして、先頭に挿入するという方法です。

まずは、末尾のデータを選択します。

次に、緑の枠の上にカーソルを移動させ、Shiftを押しながら先頭までドラッグすることで、簡単に切り取りと挿入を行うことができます。

しかし、この方法の場合、行数が多い表や複数項目を回転させる必要がある場合に、若干面倒になります。
そんな時は、次の方法が便利です。

方法２

２つ目は、並べ替え用の数値が入力された項目を用意し、その列を基準に並べ替えるという方法です。
具体的には以下のように、各行の行番号を常に表示し、末尾のみ「１」と表示されるような項目を数式で用意して、対象の項目のみを並べ替えます。

そのような特殊な項目は、以下の数式を入力することで実現できます。

=IF(ROW()=COUNTA($C$3:$C$17)+2,1,ROW())
// ROW()=COUNTA(…)+2：「数式を入力したセルの行番号」と「データの件数+2」が等しい
// 1：先ほどの条件を満たしたときに「1」を表示
// ROW()：先ほどの条件を満たさない時に「数式を入力したセルの行番号」を表示

この数式を表の末尾までコピーすることで、各行の行番号、末尾のみ「１」と表示させることができます。

最後に、並べ替える対象の範囲（見出しを含む）のみを選択し、フィルターを設定します。

以上で、準備完了です。
番号が入力された列を基準に、フィルターボタンから昇順にすることで、データを回転することができます。

番号以外の項目に罫線を設定すると、表が見やすくなります。

▼サンプルファイル▼

方法２の注意点

フィルターを設定せずに、[データ]タブから並べ替えしてしまうと、以下のように、先頭列も並べ替えられてしまいます。