最新情報

  • 2019/10/15
    ブログを更新しました。
    2018/04/30
    冷やし中華...じゃなかった、Twitterはじめました。

このサイトに関して



当サイトご利用上の注意

  • 記事へのリンクはご自由になさって結構です。
    但し、画像等、記事内のコンテンツの無断転載、画像への直接リンクによるまとめサイト等他サイトへの転載は固くお断りします。

    2019/04/14現在、コメント欄の認証方法やNGワード設定等、時々変更しています。メールアドレスの入力が求められる場合、とりあえずメールアドレスの形をしていれば(@の前は適当でも)OKです。

    上記に関し、詳しくは下記バナーから「当サイトについて」をお読み下さい。



    このブログの趣旨など。コメント欄はゲストブックとしてご利用下さい。


    記事についてご不明な点などはメールでは回答しかねますので、各記事のコメント欄にお願いします。




特設コーナー

  • Banner_kanan_fund


















外部サイト(その他)

  • 台湾の名前で東京オリンピック参加を!署名活動


    憲法改正を実現する1,000万人ネットワーク 美しい日本の憲法をつくる国民の会









管理人ポータル

大師小同窓会関連












  • 画像を提供、あるいはオリジナル写真を貸して下さる方は管理人までご連絡ください。


我が町!大師の情報

  • 大師の歴史・町名の由来

各種ノウハウ集



私家版スクラップブック

  • ブログ主の個人的スクラップブック集です。

    100円ショップの木箱やディスプレイケースなどを利用したドールハウスのまとめページです。

    メンテナンス中のため一時アクセスを制限しています。

友達の輪・お薦めSHOP

  • CHITIのボディデザイン
    魅せるボディデザイン/カラダ作りの本質とは!?
    川崎市在住のインストラクターが近隣のスポーツ施設や公園・ご自宅でのパーソナルトレーニング受け付けます。




    かわさき楽大師

    ファッション・雑貨の店 「+R」

    ヘアーサロンE'(イーダッシュ)


« 【ドールハウス工作】 No.187 ケチャップ瓶のミニチュア/100均で見つけたミニチュア素材 | トップページ | 【Excel2013】条件付き書式/特定の文字(文字列)を含むセルに色をつける【COUNTIF関数】 »

2015/07/10

【Excel2013】条件付き書式/カレンダーや予定表など、土曜日、日曜日、祝日の行に背景色をつける方法

この記事は『【Excel2013】条件付き書式:ある条件の時にセルや行に色を付けたり文字色を変える【覚書】』の補足です。

上記記事が“「土日や「祝日」でセルの色を変える方法”といった内容の検索が多いので新たに記事を作成するもので、前述の記事で条件付き書式の基本を理解していることを前提としています。

 

 

サンプル

Joken01

 

このようにA列(正しくは、A2~A23の各セル、これ以降、単純にA列のように表記)に日付を記入したらその行の背景色を土曜日ならブルー、日曜日か祝日ならピンクで塗りつぶす方法を説明します。

上の画像はごくシンプルな予定表ですが、「祝日判定」という列がポイントです。(祝日名が不要ならこの列は非表示※にします。)

 

※列の非表示

Joken10

列を非表示にするには非表示にしたい列の見出し(ここではC)の上でクリックして列全体を選択(①)→右クリックして表示されるメニューから「非表示」を選択(②)。

非表示にした列を再表示するには、非表示にした列の前後(ここではBとD)を選択→右クリックして表示されるメニューから「再表示」を選択。

 

「祝日判定」欄に表示されている「祝日1」や「祝日2」は別シートに作成した祝日の一覧表から表示しています。(下図/テストのために適当に2行のみ記入)

 

Joken02

 

後に予定表に条件付き書式を設定するときには「この欄(=予定表の「祝日判定」欄)がブランクでなければピンクの背景色をつける」という条件を設定するだけなので、「祝日1」や「祝日2」の代わりに正しい祝日名でもよいし、適当な文字や数字でも構いません。詳しくは後述のVLOOKUP(ブイ・ルックアップ)関数で説明します。

(以下、文体を変えます。)

 

手順概略

  1. 別のシートに祝日のテーブル(一覧表)を作成しておく。
    • 一覧表のデータ部(=見出し行を作った場合は見出しは含めない)に範囲名をつけておく。
  2. 予定表に「祝日判定」用の列(この例ではC列)を作り、1の一覧表を参照するVLOOKUP関数を記述する。
  3. 予定表に条件付き書式を設定する。
    • A列の日付が土曜日なら(=WEEKDAY関数で7が返されたら)セルの背景色をブルーに。
    • A列の日付が日曜日(=WEEKDAY関数で1が返されたら)かC列がブランクでなければセルの背景色をピンクに。

 

手順詳細

1.祝日テーブルを作成

別シート(※)に祝日の一覧表を作成する。

 

Joken02

※シートの追加

Excel2013では規定値として新規ファイルを開いたときに1つしかシートが表示されないが、タブの右側の「+」ボタンを押すと新規シートが追加される。

Joken03

シート名は規定値で「Sheet1」、「Sheet2」...だが、シート名の上でダブルクリックして選択した状態で上からタイプすると任意のシート名に変更できる。

今回の作業ではシート名を変更する必要は無いが、祝日テーブルを作成するシートには分かりやすいように「holiday」と命名した。

 

A列に日付の形式(※)で祝日を記入する。B列は予定表に表示させる文字(祝日名など)を入力する。

 

※日付

Joken04 Excelでは、例えば、「2015/04/29」のように年月日を「/」(スラッシュ)で区切って入力したり、「4/29」のように月日を「/」(スラッシュ)で区切って入力すると自動的に日付と判定して編集してくれる。

前者は「2015/4/29」、後者は「4月9日」などと編集されるが、これはあくまでも書式によってこのように表示されているだけで、内部的には1900年1月1日を1とする連番(シリアル値)を保持している
(「4/9」のように年を省略した場合は自動的に当年と見なされる。)

このように書式設定によって表示のされかたが異なるだけなので、予定表の日付とは書式が一致していなくても構わない。

 

ここで、テーブル全体(ここではA1~B2)に範囲名をつけておくと便利。(次項で説明するVLOOKUP関数では参照するテーブルの範囲を指定するが、セル名で指定する代わりに範囲名で指定できるので記述がシンプルになるため。)

方法はテーブル全体をドラッグで範囲指定し、「名前ボックス」欄に任意の名称を記入する。ここでは「holiday_table」と記入した。(日本語=全角文字でも可)

 

2.予定表の「祝日判定」欄にVLOOKUP関数を記述

まずは1行目(=C2セル)に設定してみる。

 

Joken01

 

ここでVLOOKUP関数の形式を確認しておく。

  =VLOOKUP(検索値,範囲,列番号,検索の型)

  • 検索値: A列のセル(=A2)
  • 範囲: holiday_table
  • 列番号: 2(取得したい値が記入されている列=「祝日1」や「祝日2」と記述されている列がholiday_tableの左から2列目なので)
  • 検索の型: FALSE ・・・説明省略

 

従って、下記のように記入する。

 

  =VLOOKUP(A2,holiday_table,2,FALSE)

 

ただし、この場合、A2の日付(2015/4/25)はholiday_tableには存在しないのでエラーとなり、「#N/A」と表示されてしまい見た目が良くない。

エラーのときにブランクを表示するためにはIFERROR関数と組み合わせて下記のように記述する。(分かりやすいようにIFERROR関数を赤字で提示。)

 

=IFERROR(VLOOKUP(A6,holiday_table,2,FALSE),"")

 

これにより、VLOOKUP関数の計算結果がエラーならブランク(””)が表示されるようになる。

C2セルの内容(数式のみ※)を他の行にもコピーし、holiday_tableの日付と一致する日付だけにテーブルの値が表示されていたら成功。

 

※数式のみコピー

Joken005

①コピー元(C2)をクリックしてCtrl+C(Ctrlキーを押しながらCキーを押す)。=コピー

②貼り付け先(C3~C23)をドラッグして、右クリックして

③「数式のコピー」を選択。=式の貼り付け 方法は上図のように何種類かある。

この時、単純な「貼り付け」を行うと全ての属性がコピーされるため、罫線や文字色などの書式も張り付いてしまい、書式を再設定しなくてはならないことがある。

 

3.条件付き書式を設定

既に前記事で条件付き書式の設定方法を説明しているので、ここでは簡単に記す。

 

Joken06

 

ここでは、表全体(A2~E23)をドラッグで範囲指定し、下図のように2つの条件付き書式を設定する。

Joken08

 

個々の条件付き書式は下記の通り。

 

Joken07

左側は「日付」欄の該当セルの値をWEEKDAY関数で判定し、7(=土曜日)が返されたらブルーの背景色にするという条件付き書式。

 

  =WEEKDAY($A2)=7

 

右側は「祝日判定」欄がブランク以外、あるいは、1(=日曜日)が返されたらピンクの背景色にするという条件付き書式。(「$A2」の「$」は前記事で解説済み。)

 

  =OR($C2<>"",WEEKDAY($A2)=1)

なお、この例では土曜日の条件付き書式が日曜日・祝日の条件付き書式の上に記述されているので、土曜日と祝日が重なった場合、ブルーになる。(上に記述されている方が優先) 順番を変える方法は前記事にて解説。

 

念のため、WEEKDAY関数の形式は下記の通り。

 

  =WEEKDAY(シリアル値)

  • シリアル値: 日付または日付の入力されているセル

 

日、月、...土ならそれぞれ1、2、...7という数値が計算結果として返される。


補足-曜日の表示方法

今回のテーマとは直接関係ないが、「曜日」欄の説明を補足しておく。

「曜日」欄の各セルは実際には「日付」欄を参照しているだけ。つまり、例えばB2セルには「=A2」と記述しているだけである。

これが「土」と表示されるのは書式によって編集しているから。(下図参照)

 

Joken09

 

「セルの書式設定」ダイアログボックスの「分類」欄から「ユーザー定義」を選択し、「種類」欄に「aaa」と半角で入力し、[OK]で終了。これにより曜日が表示される。

この時「aaaa」とaを4文字入力すると「土曜日」と表示される。

繰り返しになるが、B2セルには「=A2」という数式が記述されているので、セルの値はシリアル値でしかない。これを書式によって「土」や「土曜日」に編集してあるだけである。

このような日付の書式は他にもあり、例えば、種類を「d」とすると日だけ、つまり「25」と表示され、「ddd」とすると英語の曜日(Sun~Sat)、「ge」なら元号で年のみ、つまり「H25」となる。

これらは組み合わして使うことも可能で、「ge.m.d」と指定すると、「H25.4.25」と表示される。

 

補足-日付の連続データの記入方法

カレンダーのように日付を連続して入力する場合は、最初のセル(縦に日付が並ぶ場合は一番上のセル)に開始日付を記入して、2行目以下にドラッグして「連続データ」のコピーを行うのが一番簡単である。(「オートフィル」機能/下図)

 

Joken12

 

別の方法として、2行目以降に「一つ上のセルの値+1」を計算する数式を記入方法もある。

 

Joken11

 

前述のように、日付は“内部的には1900年1月1日を1とする連番(シリアル値)を保持している”だけなので、1をプラスすれば翌日の日付が得られる。

上の例では1行目(A2)に開始日付(2015/4/25)を記入した後、2行目(A3)には「=A2+1」という計算式を記述。

このセルの計算式を3行目以降にコピーすると、式は行に応じて「=A3+1」、「=A4+1」...とずれていく。つまり、常に“一つ上のセルの値+1”のシリアル値になる。

この方法だと、一行目の日付を変更すると、それ以降も自動的に計算される。

また、数式を使う方法であれば、等間隔(ex. 1行おき)ならば、セル自体が連続していなくても連続した日付を表示できる。

 

 

 

 


 

« 【ドールハウス工作】 No.187 ケチャップ瓶のミニチュア/100均で見つけたミニチュア素材 | トップページ | 【Excel2013】条件付き書式/特定の文字(文字列)を含むセルに色をつける【COUNTIF関数】 »

コメント

コメントを書く

(ウェブ上には掲載しません)

« 【ドールハウス工作】 No.187 ケチャップ瓶のミニチュア/100均で見つけたミニチュア素材 | トップページ | 【Excel2013】条件付き書式/特定の文字(文字列)を含むセルに色をつける【COUNTIF関数】 »

counter since 2018/12/25

  • Flag Counter

    FLAG Counterのロゴをクリックすると、更に詳しい分析結果が表示されます。
    ココログのアクセス解析よりは少ない数字になるようです。
サイト内検索
ココログ最強検索 by 暴想
2019年11月
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

sponsored link


ドールハウス工作おすすめ参考図書

カテゴリー

無料ブログはココログ