エクセル

Excel で無作為にグループ分け(同じ部署の人は別グループになるよう検討する)

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur
バージョン 11.5.2

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.52 (21080801)

H.K さん。

エクセルでランダムにグループ分けする仕組みです。
共有したテンプレートは以下のように作っています。

こちらにもまとめておきます。

特別に難しいことはしていません。誰でもあとからメンテナンスができると思います。

当たり前ですが、これが唯一の正解ではないです。
「こうしたら、もっと誰でもメンテナンスがしやすくなる」というアイデアがあれば私にも教えてくださいね。

■課題

課題は次の2つ。

  • 社内研修参加者をランダムにグループ分けする作業を簡素化したい。
  • その際に、同じ部署の人は違うグループにしたい。

■使用する関数

使用する関数は次の5つです。

  • RAND関数
  • RAN.EQ関数
  • IFS関数
  • SORT関数
  • COUNTIF関数

IFS関数とSORT関数は、Microsoft 365 サブスクライバーが使える関数です。

RANK.EQ関数は、数値の一覧内における各数値の順位を返します。
従来からあるRANK関数と同じですが、マイクロソフトはRANK.EQ関数の利用を推奨していているのでそちらを使いました。

エクセルのヘルプでRANK関数を見ると、「Excel の将来のバージョンでは利用できなくなる可能性があるため、今後は新しい関数を使用することを検討してください。」と書かれています。

仕事で使う仕組みづくりですから、将来的に使えなくなる可能性が少しでもあるならその使用は避けたほうがよいと、私は考えます。

■ランダムにグループ分け

例として、20名の参加者を等分してグループ分けすることを考えてみました。
20名を等分する場合は、次の二通りの分け方になりますね。

  • 4名ずつ5グループ
  • 5名ずつ4グループ

ここでは、「4名ずつ5グループ」を取り上げます。
「4名ずつ5グループ」がわかれば、「5名ずつ4グループ」に分ける仕組みも簡単につくれます。
数式を書き換えるだけですから。

どんな人数で何グループに分ける場合でも、考え方は同じです。

次の3つのシートを作りました。
ファイルを使う人がわかりやすければ、シート名はなんでもよいです。
各シートの列名もわかりやすければなんでもよいです。

  • 「設定」
  • 「グループ分け」
  • 「並べ替え」

■シート「設定」

シート「設定」では次の2つを指定します。

  • いちグループ何名にするか(ここでは 4 名ずつ。)
  • グループ名(今回は最大で 5 つのグループ名が必要。)

C列「区分け」の値は、このあとで使います。

どのシートも基本的にさわるのは1行目に色を付けてある列です。
人数や名前、グループ名を入れたり、部署名を入れたり。

色を付けていない列のセルには数式を入れています。
必要によって数式を書き換えます。

011

■シート「グループ分け」

シート「グループ分け」で各人がどのグループになるか決めています。
こんな感じです。
(使用している氏名は、ネット上の無料サービスで作りました。)

021

A列のセルに入っているのは =RAND() という式です。
RAND関数で乱数を発生させています。

発生させた乱数を昇順に順位付けしているのが、B列です。
セル B2 には =RANK.EQ(A2,$A$2:$A$21,1) の数式を入力して、一番下までコピーします。

乱数の順位が、各人に対する固有の番号になります。
その番号をもとにすれば、無作為なグループ分けができます。

E列で、IFS関数を使ってグループを決めています。
セル E2 の数式は以下のようです。

03ifs

こうやって数式の途中でセル内改行しておくことで、IFS関数を使ってどんな条件で何をしているのかを把握しやすくなると思います。数式の頭の位置はスペースで合わせています。

IFS関数の一行目の数式の意味は以下のようです。

  • セル B2 の値( 18 )が、シート「設定」のセル C2 ( 4 ) 以下の場合、シート「設定」のセル B2 の値 ( G01 ) を返す。

シート「設定」のC列が、冒頭で「あとで使う」といった「区分け」です。
いちグループ 4 名ずつにするので、RANK.EQ関数の結果を使って「4以下ならG01」、「5以上8以下ならG02」というように4名ずつグループ分けするために使っています。

04

シート「設定」の C2 の値は 4 なので、一行目は成り立ちません。
なので、二行目の判定が行われますが、二行目も成り立ちません。
(そうやって、四行目までが成り立ちません。)

成り立つのは五行目の数式なので、シート「設定」のセル B6 の値が返ります。

  • セル B2 の値( 18 )は、シート「設定」のセル C6 ( 20 ) 以下なので、シート「設定」のセル B6 の値( G05 )を返す。

そのようにして、各人がどのグループになるかが決まります。

シート「グループ分け」のセル E2 に入れたIFS関数の数式を一番下までコピーします。

セル B3 の値( 6 )の場合は、シート「設定」の C3 ( 8 ) 以下なので、シート「設定」の B3 の値(グループ名 G02)が返っています。

RAND関数で発生させた乱数を元にしているので、ここまでで各人を無作為にグループ分けできました。

次は、「同じ部署の人は同じグループにならないように」検討できる仕組みづくりです。

■同じ部署の人は同じグループにならないように検討する

一覧を見やすくする。

まず、各人が何グループなのをわかりやすくしておきます。

そのために、シート「並べ替え」のセル A2 にSORT関数の数式を入力しています。

051

セル A2 に入れた数式は以下のようです。
C列(「グループ」)を基準にして昇順に並べ替えています。

=SORT(グループ分け!C2:E21,3,1,FALSE)

Microsoft 365 サブスクライバーの場合は、このようにあるセルに数式を入れると他のセルに数式をコピーしないで結果を表示してくれる機能があります。
(Spill(スピル)という機能です。マイクロソフトのヘルプのページでは「こぼれた配列動作」と訳されてますが。「スピル」のほうが定着してほしいです。)

SORT関数の構文は次のようです。

=SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

第一引数の「配列」以外は省略が可能ですが、SORT関数の各引数の役割をわかりやすくするために、ここではあえてすべてを指定してみました。

シート「グループ分け」の C2 から E21 を並べ替えたいので、第一引数「配列」にはその範囲を指定しています。

第二引数 [並べ替えインデックス] は 3 になります。
第一引数で指定した範囲は C列からE列です。
E列のグループ名を基準にするので、指定した範囲の左端から C,D,E と数えて、 [並べ替えインデックス] は 3 になります。

第3引数 [並べ替え順序] は 1 にしました。G01 から順に並べたいですから。
1 は「昇順」を意味します。

061sort

第四引数 [並べ替え基準] は、FALSE です。
行を並べ替えるので。
(見た通り、SORT関数は列の並べ替えもできます。我々の仕事ではあまり使うことはないと思いますが。)

062sort

こうやって、指定範囲がグループ名を基準にして昇順で並べ替えされます。
これで誰が何グループなのかがわかりやすくなります。

さらに区別しやすくしてみます。
いちグループ 4 名なので、グループ名の固まりごと(4行ごと)にセルの下側に罫線をひいて区切ってみます。

この罫線をひく作業は無くてもよいです。必要なければ省いてください。

セル A5 から C5 を選んで =設定!$A$2=4 の数式を条件に、セル範囲の下側に罫線をひく条件付き書式を設定します。

0714

シート「設定」のセル A2 (「人数」)が 4 なら、こうなります。

0724

この設定をセル A9 から C9、A13 から C13、A17 から C17 に対して行います。
そうすると、こうなります。
誰が何グループなのかがずいぶんと見やすくできたと思います。

0743

同じ部署の人は同じグループにならないように検討できる仕組み作り

お待たせしました。ようやく、「同じ部署の人は同じグループにならないように」検討できる仕組み作りです。
やはり条件付き書式を使います。

グループ G01 のなかで部署が重複していないかを確認する設定です。
セル範囲 B2 から B5 を選択しておいて、条件付き書式では次のように入力します。

081

=COUNTIF($B$2:$B$5,B2)>=2 の数式で、セル B2 の値が、セル範囲 B2 から B5 のなかで2個以上あると TRUE が返り、設定した書式が実行されます。

COUNTIF関数の構文は次のようです。

COUTNIF(範囲,条件)

第一引数の「範囲」( $B$2:$B$5 )が絶対参照で、第二引数の「条件」( B2 )が相対参照になっているので、セル範囲 B2 から B5 のなかで B3、B4、B5 の値が 2 個以上あるかが判定されます。
そうすることで、グループ G01 のなかで部署が重複しているか、いないかがわかります。
(セルが色付けされなければ、同じ部署はない。)

082

なお、重複を見つけるような、どこかのセルを強調したい条件付き書式では、「セルの塗りつぶし」を活用するのがコツです。
白黒で印刷しても判別しやすいですから。

同様の設定をセル範囲 B6 から B9、B10 から B13、 B14 から B17、B18 から B21 にも行います。
(いちグループ 4 名なので 4 行分ずつ設定している)

=COUNTIF($B$6:$B$9,B6)>=2
=COUNTIF($B$10:$B$13,B10)>=2
=COUNTIF($B$14:$B$17,B14)>=2
=COUNTIF($B$18:$B$21,B118)>=2

オートフィルタでグループ名を絞り込むと、セル範囲が選択しやすいです。

これで、「4名ずつ5グループにランダムに分けて、同じ部署の人はいっしょにしない」ように検討できる仕組みの完成です。

ファイルができあがったら、何度でも使えるようにテンプレート形式で保存しておくことをお勧めします。

■結果の試行

F9 キーを押すたびにRAND関数が実行されて、グループ分けの結果が変わります。
F9 を何度か押して、B列で部署名が重複しない組み合わせを探せます。

■結果の確定

各グループのなかで部署名が重複しない結果が得られたら、表全体をコピーしてどこかに「値貼り付け」してください。
そうすることで、その結果を確定できます。
値貼り付けで結果を確定しないと、ファイルを開くたびにRAND関数が実行されるので結果が変わってしまいます。

■「5名ずつ4グループ」への対応

シート「設定」のセル A2 (人数)を 5 にします。
これでグループ分け自体はすぐにできます。

20名の参加者を4名ずつに分ける際にグループを5つ作っているので、5名ずつにすると4グループとなってグループが1つ多いですが、グループ数が余っているのは問題になりません。
シート「グループ分け」で使っているIFS関数の数式もそのままでエラーは起きないです。

シート「並べ替え」では次の2つの作業をします。
(先にもいったように、罫線で区切る作業は不要なら無視してください。)

・4名ずつのときのように、罫線でグループごとに区切る場合
セル A6 から C6 を選んで =設定!$A$2=5 の数式を条件に、セル範囲の下側に罫線をひく条件付き書式を設定。
同様の設定をセル A11 から C11、A16 から C16 に対して行います。(5行ごとにセル範囲の下側に罫線をひく。)

・「部署」の列で重複を見つける。
セル範囲 B2 から B6 を選択しておいて、条件付き書式の数式は次のように入力します。

=COUNTIF($B$2:$B$6,B2)>=2

同様の設定をセル範囲 B7 から B11、B12 から B16、B17 から B21 (各5行分)にも行います。

=COUNTIF($B$7:$B$11,B7)>=2
=COUNTIF($B$12:$B$16,B11)>=2
=COUNTIF($B$17:$B$21,B17)>=2

F9 を何度か押して、B列で部署名が重複しない組み合わせを探します。
値貼り付けで結果を確定することも同じです。

共有したテンプレートは、「4名ずつ5グループ」と「5名ずつ4グループ」の両方の設定をしています。

■総人数が変わったとき

今回の例では 20 名でしたが、もっと多くの人数が参加するときもありますよね。
次の7つの作業をします。

1.総人数を何名ずつ何グループに分けるかを決める。
例:30名
・3名ずつ10グループ
・5名ずつ6グループ
・6名ずつ5グループ

2.シート「設定」の「グループ名」と「区分け」をグループ数に合わせる。
想定される最大のグループ数分を作っておくのも "あり" 。
上の例なら、3名ずつで最大10グループになる。

3.シート「グループ分け」のRAND関数とRANK.EQ関数の列を、総人数に合わせる。

4.シート「グループ分け」の「グループ」の列のIFS関数の引数をグループ数に合わせる。
シート「設定」の「グループ名」と「区分け」の列の値を使うので、10グループに分けていたら10行分書く。

5.シート「並べ替え」で、SORT関数の引数を修正する。
第一引数の「配列」を、シート「グループ分け」のC列からE列のセル範囲に合わせる。

6.シート「並べ替え」で、グループごとに罫線で区切る条件付き書式を設定する。
(これは省いてよい)

・グループごとに罫線で区切る条件付き書式
いちグループが何名ずつかによって、書式を設定する行が変わってくる。
=設定!$A$2=3 (3名ずつ10グループの場合。3行ごとに設定)
=設定!$A$2=5 (5名ずつ6グループの場合。5行ごとに設定)
=設定!$A$2=6 (6名ずつ5グループ。6行ごとに設定)

7.シート「並べ替え」で、部署名の重複を見つける条件付き書式を設定する。

・部署名の重複を見つける条件付き書式
いちグループが何名ずつかによって、引数「範囲」の指定が変わってくる。
=COUNTIF($B$2:$B$4,B2)>=2
(3名ずつ10グループの場合。B2 から B4 の3行分に設定)

=COUNTIF($B$2:$B$6,B2)>=2
(5名ずつ6グループ。B2 から B6 の5行分に設定)

=COUNTIF($B$2:$B$7,B2)>=2
(6名ずつ5グループ。B2 から B7 の6行分に設定)

■人数が余る場合

総人数と何名ずつを一つのグループにするかで、人数に余りがでることもありますね。
たとえば、総人数が 30 名で 4 名ずつグループを分けたら、グループ数が 7 つで、2 名余ります。

そういうときは、もう仕方がないです。

グループ分けの結果を確定させたら、部署が重複してもその 2 名をどこかのグループに手作業で割り振るしかないです。

| | コメント (0)

Excel用2022年(令和4年)の祝日

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur
バージョン 11.5.2

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.52 (21080801)

下記の表をエクセルのシートにコピー貼り付けする。

2022/1/1 元日
2022/1/10 成人の日
2022/2/11 建国記念の日
2022/2/23 天皇誕生日
2022/3/21 春分の日
2022/4/29 昭和の日
2022/5/3 憲法記念日
2022/5/4 みどりの日
2022/5/5 こどもの日
2022/7/18 海の日
2022/8/11 山の日
2022/9/19 敬老の日
2022/9/23 秋分の日
2022/10/10 スポーツの日
2022/11/3 文化の日
2022/11/23 勤労感謝の日

■土日、祝日のセルに色を付ける例(条件付き書式で使う数式)

・祝日に対応
=COUNTIF(Sheet1!$A$1:$A$16,A$1)=1
Sheet1 のセル A1 から A16 の範囲が祝日の一覧である場合。
休暇など祝日以外の日付に色を付ける場合は、一覧にその日を追加する。
増えたセル範囲に合わせて、$A$1:$A$16 の部分を修正する。

・土曜日に対応
=TEXT(A$1,"aaa")="土"
1行目に日付が入っている場合。
A列に日付が入っている場合は、第一引数を $A1 にする。

・日曜日に対応
=TEXT(A$1,"aaa")="日"

| | コメント (0)

Excel で複数のシートを同時に再表示する。( VBA 不使用)

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur バージョン11.12.3
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.47.1(21032301)

H.M さん。

Excel で複数のシートを同時に再表示できるようになりました。
Shift キーと command キーを使って、どのシートでも自由に選択できます。

これでもう、VBA や「ユーザー設定のビュー」を利用することはないでしょうね。

どうやら、Microsoft 365 サブスクライバーに提供されている機能のようです。
リンク先に Excel for Microsoft 365 の記載があるので。

Excel の新機能を見つける(マイクロソフトのサポートのページ)

Hukusu_sheet_saihyouji

Windows版Excel でも同じことができました。

・Windows10 Pro バージョン20H2
・Excel バージョン 2102 (ビルド 13801.20360)

Windows版Excel の場合に使うキーは、Shift キーとControl キーですね。

| | コメント (0)

Excel で 0 は表示するが単位は表示しない。

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur バージョン11.12.1
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.46(21021202)

K.M さん。

セルの値について、数字の 0 は表示するだけで、単位は付けない例を以下にまとめておきます。

課題

人数が入ったセルに単位として「名」を付ける。
ただし、0 の場合は単位は付けないで 0 だけを表示する。

0ni_tani_hyoujisinai1

を、次のようにする。

0ni_tani_hyoujisinai2

やり方

『ユーザー定義の表示形式』を使う。
(いわゆる、「ユーザー定義書式」です。)

今回の場合にユーザー定義として指定する書き方。

G/標準"名";;0;

半角の ; (セミコロン)で区切ることで、最大4つの表示形式を指定できる。

0ni_tani_hyoujisinai3

User_teigi_syosiki1

慣れないとわかりづらいですが、落ち着いて見ていけばわかると思います。

G/標準"名";;0; を、

正の値の指定;

負の値の指定;

0の場合;

文字列の場合

で分けて見ると次のようです。

User_teigi_syosiki2

 

| | コメント (0)

"Excel の表全体を対象とした重複の探し方" を見つけるのが面倒なのよ。

アクセス解析を見ると、「excel 表全体 重複」というキーワードで、うちのブログにやってくる人がぽつぽついるよう。

エクセルの表から重複データを見つけようするのは、大昔からある「エクセルあるある」だろうと思うし、値の重複を見つけるやり方は検索すればいくらでも見つかると思う。

けど、検索でも意外に見つけ出すのが難しいと感じたのが、列や行内での重複ではなくて「表全体でどこかにある重複を見つけるやり方」。

こういうやつ。

Hyou_ataityouhuku1

範囲のなかで重複している値は……

Hyou_ataityouhuku2

……です。

検索して「あっ! これかな?」なんて期待でページを開くと、「これじゃないなあ……」となったりして。

だから、前に覚書を作った。

先述の方たちがお探しなのも、たぶんこれだと思う。

ということで、

Excel の表全体でどこかにある重複する値を見つける。

リンク先は Windows での話だけど、Excel for Mac でもそんなに変わんない。

| | コメント (0)

テーブル名の先頭にアルファベットを付けておくと便利

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur バージョン11.12.1
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.46(21021202)

H.K さん。

お伝えしたエクセルのテーブル名の件ですが、下記にまとめておきます。

・テーブル名の先頭にアルファベットを付けておくと、数式のなかで呼び出しやすくて便利

特にアルファベットでなくてもよい。
テーブル名の先頭に使えるのは、文字、アンダーバー (_)、またはバックスラッシュ (\) 。

先頭以外で使えるのは、文字、数字、ピリオド、アンダーバー。(ハイホン、スペースは使えない。)

詳細は、次のリンク先(マイクロソフトのサポートページ)を参照。

Excel テーブルの名前を変更する

テーブル名の先頭にアルファベットを付けている例

数式のなかで、そのアルファベットを打つとテーブル名を候補に表示させられる。
次図では、「t_一覧」というテーブル名を付けている。

Table_name1

別のセルでテーブルの見出し部分を呼び出す例

イコールに続いて t を打つとテーブル名が表示される。

Table_name2

なお、t の次に「_(アンダーバー)」を付けているのは、Windows版Excelでの利用も考えてのこと。

テーブル名の先頭が t だけだと、Windows版Excelの場合は、テーブル名があるのは表示される候補の下の方になる。
スクロールバーを下に動かしてそこまで見ないとテーブル名が出てこない。

Windows版Excelでは、t に続いてアンダーバーを打つと、テーブル名が上の方に表示されてくるので選択しやすくなる。

テーブル名が選ばれていることを確認して、Tabキーを押すとテーブル名が数式のなかに挿入される。

続いて [ (ブラケットまたは角括弧)を打つと、入力候補が表示されるので、↓キーで選んでTabキーを押す。
次図では、 #見出し を選んでいる。

Table_name3

Tabキーを押すと数式のなかに挿入される。

Table_name4

右端に ] を打って、Enterキーで確定したところ。

テーブルの「見出し」が入力される。

Table_name5

このように、入力していないセルまで数式が自動的に入力されていくのは、Microsoft365サブスクライバーのエクセルでだけ。

「Spill(スピル)」という仕組み。

スピルで埋まった隣のセルを見てみると、セルの中で数式がうっすらとグレーになって表示されている。
これを「ゴースト」という。

ゴーストのセルをダブルクリックするとわかるが、セルのなかには実態としては何も入っていない。
(スピールは、コピーするのではなくて結果を表示している。)

Table_name6

関数のなかでテーブル名を呼び出している例

XLOOKUP関数のなかでテーブル名を呼び出しているところ。

t を入力するとテーブル名が表示されるので選択しやすい。
(XLOOKUP関数は、永続ライセンス版のエクセルでは使えない関数)

Table_name7

先と同様に [ を打って、↓キーで選んでTabキーを押すと候補を挿入していける。

Table_name8

] を打って、Enterキーを押して確定したところ。

Table_name9

「名前」でも同じことができる。

セルやセル範囲に付ける「名前」についても、同じ使い方ができる。

名前の先頭にアルファベットを付けておいて、それを打つことで数式のなかで「名前」を簡単に呼び出せる。

| | コメント (0)

Excel で同じ値を続けて入れる。

この記事は自分用の覚書です。

■macOS Catalina バージョン10.15.7
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.45(21011103)

H.K さん。

「エクセルで同じ値を続けて入れる」件について、お伝えした考え方を下記にまとめておきます。

課題

こういうやつから、

01onajiatai_20210210132701

こういうのを作りたい。

02onajiatai

A
A
B
B
とセルに入力してドラッグしても、狙い通りにならない。

03onajiatai

04onajiatai

考え方

作業列を使う。

隣の列に連番を入れておいて、それも含めてコピーして増やす。
セル B1 から B5 に連番を。A1 から B5 をコピーして、 A6 を選択して貼り付ける。

05onajiatai

全体を選択して、セル B1 をアクティブにする。
(Tabキーでアクティブセルを変えられる。行きすぎたら shift+Tabで戻る。)

06onajiatai

B1 がアクティブになっていることを確認して、「昇順」で並べ替える。
これで同じ値が連続する状態になっている。

07onajiatai

B列の値を削除する。

08onajiatai

これで完了。

ちなみに、同様の考え方で1行ごとに空白行を入れたりもできます。

ようするに、発想しだいというわけですね。

09onajiatai

10onajiatai

| | コメント (0)

Excel の「参照元」と「参照先」の違い

この記事は自分用の覚書です。

■macOS Catalina バージョン10.15.7
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.45(21011103)

参照元とは

数式の中でセル参照によって指し示されているセル。

参照先とは

数式が書き込まれているセル。

Sansyoumoto_sannsyousaki_tigai_20210125114101

参照元を選択するショートカットキー

参照先を選択して、control + [ を押す。
参照元が別シートにある場合も選択できる。

Sannsyoumoto_sentaku

Sannsyoumoto_sentaku_spill

参照先を選択するショートカットキー

参照元のセルを選択して、control + ] を押す。
参照先が別シートにある場合は、このショートカットは効かない。

| | コメント (0)

Excel で土日、祝日のセルに色を付ける。(TEXT関数を使用)

この記事は自分用の覚書です。

■macOS Catalina バージョン10.15.7
■Microsoft 365 Business Standard サブスクリプション
Excel for Mac バージョン16.44(20121301)

S.R さん。

Excel で土日と祝日のセルに色を付けるやり方ですが、TEXT関数を使うやり方を教わったので共有です。

ネット上では、条件付き書式とWEKKDAY関数を組みわせる例をよく見ます。

私もこれまでWEEKDAY関数を使っていましたが、わかってみると、TEXT関数を使ったほうがよりよいと感じました。
というのは、条件付き書式のなかで数式を見て何をしているかを理解しやすいのは、TEXT関数のほうだからです。

仕事でつくるファイルは、誰のものでもなく皆のものです。
誰がいつ見ても理解しやすくつくっておくほうがよいと、私は思います。

■使用する機能
名前の定義
条件付き書式

■使用する関数
TEXT関数
COUNTIF関数

祝日の一覧をつくっておく。

祝日のセルに色を付けるには、元になる祝日の一覧を用意しておかなければなりません。
これは地道に手作業ですね。エクセルには、日本の祝日を一覧で出力してくれる機能や関数はありません。

祝日の一覧ができたら、それらのセル範囲に名前を付けておいてください。
下記では、セル A1 から A17 までを選択して、「祝日一覧」という名前を付けています。

B列にある祝日の呼称は、あったほうが見やすいかなと思って添えただけです。
このあとの作業ではB列は使わないので、無くてもかまいません。

01namae_syukujituitiran

土日の日付が入ったセルに色を付ける。

次図は色を付けたいセル範囲( A1 から A24 )です。
曜日も見えたほうがわかりやすいと思ったので、そうしてあります。
曜日はユーザー定義書式を使って表示しています。

02hiduke

ユーザー定義書式では、 yyyy/m/d(aaa) と指定しています。
これで日付の右隣に、カッコで囲んだ曜日が表示されます。

aaa で 土、日といった漢字一文字での表示になります。

"土曜日" というように "曜日" まで表示したければ、aaaa と a を4つ打ってください。

03cell_userteigi_youbi

小文字の a を使った曜日の表示の指定は、TEXT関数の引数でも使います。
TEXT関数の書式は、 TEXT(値,表示形式) です。

図では、TEXT(A1,"aaa") の式で「金」という文字列が返っています。
TEXT関数が返す答えはすべて文字列です。

04text_kansu

このTEXT関数を使って、「TEXT関数の答えが "土" なら、セルに色を付ける」、「"日" ならセルに色を付ける」という条件を、条件付き書式で設定します。

次図での数式は、 =TEXT(A1,"aaa")="土" となっています。
「土曜日を条件にしている」と、ぱっと見て理解しやすいと思います。

色は適当に選びました。

Joukentukisyosiki_text_kansu_doyou

さらに、右端の "" のなかを 日 に変えた条件を追加します。

Joukentukisyosiki_text_kansu_nitiyou

これで土日の色付けはできました。次は祝日です。

なお、上記では条件付き書式を設定している列が一列だけなので、数式のなかのセル参照は相対参照のままにしています。
複数列に対して条件付き書式を設定するなら、列を絶対参照にしてください。

たとえば、上記の数式では、=TEXT($A1,"aaa")="土" のようにA列を絶対参照にします。
A列にある日付の曜日が条件なので、A列を絶対参照にしておかないといけません。

あるいは、1行目に日付が入力されていて、複数行に条件付き書式を設定するなら、=TEXT(A$1,"aaa")="土" ですね。

祝日の日付が入ったセルに色を付ける。

ここで COUNTIF関数を使います。
指定した日付が、祝日の一覧のなかに「いくつあるか」を調べます。

当然ですが、答えは常に「1」です。
ある日付を祝日の一覧のなかで探すのですから。

ということで、「COUNTIF関数の答えが 1 である」という数式を、条件付き書式で設定します。
条件付き書式で設定する数式は次のようです。

Joukentukisyosiki_counif_iro

COUNTIF関数の書式は、COUNTIF(範囲,検索条件) です。

引数「範囲」には、さきに名前を付けた「祝日一覧」を入力します。
(Excel for Mac には Windows版Excel のように F3 キーで名前を呼び出す機能は今のところありません。)

引数「検索条件」には、色付けしたい日付が入ったセルを指定します。

これで、土日と祝日のセルに色をつけることができます。

数式内のセル参照を相対参照にするか、絶対参照にするかはTEXT関数と同じです。

複数列に条件付き書式を設定するなら、COUNTIF(祝日一覧,$A1)=1 になります。
1行目に日付を入力している場合は、COUNTIF(祝日一覧,A$1)=1 になります。

祝日以外の休日への対応。

有給休暇とか祝日以外の休日にも色を付けたい場合は、祝日の一覧のなかにその日付を追加して、名前を付けたセル範囲を変えてください。
「数式」タブの「名前の定義」からできます。

WEEKDAY関数について。

WEEKDAY関数が返す答えは、常に数字です。

たとえば、引数「種類」で 2 を指定すると、指定した日付が月曜なら 1 を返します。火曜日なら 2 返します。
土曜日なら 6 で、日曜日なら 7 が返ります。

05weekday_kansu

下図では、「WEEKDAY関数の答えは 6 である(答えが土曜日である)」という条件を指定しています。

Joukentukisyosiki_weekday_kansu_doyou

引数「種類」で 2 を指定しているので、日付が土曜日なら 6 が返ってきます。
"2 を指定すると土曜日なら 6 が返る" と記憶していなければ、数式を見て何をしているかを理解できません。

それだけではありません。そのことを記憶していないと、そもそも数式を入力することもできません。
条件付き書式の入力欄では、関数ヒントが表示されないですから。

TEXT関数でも書式を記憶しておくことは変わりませんが、記憶にかかる負担が少ないです。
「第二引数を "aaa" とすれば漢字一文字の曜日が返る」と覚えておくだけです。

さらに、"2 を指定すると土曜日なら 6 が返る" というのは、WEEKDAY関数の引数「種類」で 2 を指定した場合です。

他の数字を指定したら、同じ土曜日でも返ってくる数字が異なります。
「種類」のところで 1 を指定したら、土曜日の場合は 7 が返ってきます。

こう考えると、TEXT関数を使うほうが誰がいつ見ても何をしているかを理解しやすので、この件ではTEXT関数を使うべきだと思いました。

| | コメント (0)

Excel用2021年(令和3年)の祝日(2021年の祝日移動対応済)

この記事は自分用の覚書です。

■MacBook Pro macOS Catalina
バージョン 10.15.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.40 (20081000)

下記のままエクセルのシートにコピー貼り付けする。
(NETWORKDAYS 関数などで、引数の [祭日] を指定する際に使う。)

・NETWORKDAYS 関数の書式(Excel for Mac ヘルプ より)
NETWORKDAYS(開始日, 終了日, [祭日])

NETWORKDAYS 関数は、引数「開始日」から「終了日」までの期間で、土曜、日曜、「祭日」を除く日が何日あるかを返す。「何営業日あるか」ということ。

日付と曜日を表示する場合、セルの書式の「日付」のところを変えるか、ユーザー定義書式で、

yy年m月d日(aaa)

といったように打つ。
この場合は、21年1月1日(金) といった表示になる。

a を4個打つと、

21年1月1日(金曜日)

となる。

日付のセルの中に曜日を入れてはいけない。
ただの文字列になってしまうので、日付を使った計算ができなくなる。日付はシリアル値のまま扱う。

2021/1/1 元日
2021/1/11 成人の日
2021/2/11 建国記念の日
2021/2/23 天皇誕生日
2021/3/20 春分の日
2021/4/29 昭和の日
2021/5/3 憲法記念日
2021/5/4 みどりの日
2021/5/5 こどもの日
2021/7/22 海の日
2021/7/23 スポーツの日
2021/8/8 山の日
2021/8/9 山の日の振替休日
2021/9/20 敬老の日
2021/9/23 秋分の日
2021/11/3 文化の日
2021/11/23 勤労感謝の日

2021年の祝日移動

2021年のみの特例措置。

・「海の日」
2021/7/22へ。
例年は、7月の第3月曜日。

・「スポーツの日」
2021/7/23へ。
例年は、10月の第2月曜日。

・「山の日」
2021/8/8へ。
例年は、8月11日。

| | コメント (0)

より以前の記事一覧