このサイトに関して




  • 2017/12/14
    ブログを更新しました。


    ■特設コーナー

    DHCを応援しています。











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




    ■ご利用上の注意
    記事へのリンクはご自由になさって結構です。
    但し、画像等、記事内のコンテンツの無断転載、画像への直接リンクによるまとめサイト等他サイトへの転載は固くお断りします。(加計学園問題関連の画像はご自由にお使い下さい。)

    コメントにURLを貼る場合は、「http://」、「https://」を除いて貼り付けて下さい。

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



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


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





各種ノウハウ集



管理人ポータル

大師小同窓会関連








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


我が町!大師の情報

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

私家版スクラップブック

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

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

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

友達の輪・お薦めSHOP

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




    かわさき楽大師

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

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


【IT】 Excel条件付き書式

2015/07/17

【Excel2013】条件付き書式/特定の文字(文字列)を含むセルに色をつける【COUNTIF関数】

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

前述の記事で条件付き書式の基本を理解していることを前提としています。

サンプル

まずはサンプルです。

Joken14

上の表は野球の勝敗表で、D(横浜DeNAベイスターズ)が勝利なら○、敗けなら●と記入してあるので、それぞれ、○が記入されていればピンク、●が記入されていればブルーの背景色が自動的に着くよう条件付き書式が設定されています。○も●もない横浜戦の場合はグリーンの背景色になります。

このように、特定の文字列があれば書式を付けるという条件付き書式の場合、COUNTIF関数を使用します。

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

 

 

COUNTIF関数

COUNTIF関数とは、指定した範囲に検索条件として指定した文字(文字列)を含むセルの数をカウントしてくれる関数で、基本的な記述方法は下記の通り。

  COUNTIF(範囲,検索条件)

セルにCOUNTIF関数の計算結果を表示する場合、数式として最初に「=」(イコール)を記述してから「=COUNTIF(範囲,検索条件)」のように書く。

先に示した表ではC30、C31にCOUNTIF関数を使用して○(白星)と●(黒星)の数を数えているので、例としてC30に記述した数式を見ると、

Joken15

と、

  =COUNTIF($B$2:$G$28,"*○")

のように書かれている。

  • 範囲: ここではB2~G28の範囲(下のセル=C31にもこの範囲をずらさずにコピーしたいので「$」を付けて「$B$2:$G$28」のように記述している。)
  • 検索条件: 末尾に「○」があるものをカウントしたいので、ワイルドカード「*」(アスタリスク)を使用している。

ワイルドカード

ワイルドカード(wild card)とは元々トランプ用語で、他のカードの代用となるカードのこと、コンピューター用語としては、任意の文字に置換え可能な特殊文字で、「?」(疑問符) と「*」(アスタリスク ) をワイルドカード文字として使用できる。(他のアプリでも「?」と「*」を使用している場合が多い。)

「?」は任意の1文字「*」は1文字以上の任意の文字列字を表すので、「*○」は前に何文字あっても末尾に「○」があれば、検索条件に合致したとして1とカウントされる。(「??○?」なら4文字の3番目に○があれば条件に合致する。)

これにより、C30の数式の結果は10となる。

条件付き書式の設定方法

既に前述の記事で設定方法詳細は説明済みなので、ここでは簡単に記す。

Joken16

ここでは、上図のように3つの条件付き書式が設定してある。それぞれの条件は、

■ピンク

  =COUNTIF(B2,"*○*")>0

■ブルー

  =COUNTIF(B2,"*●*")>0

■グリーン

  =COUNTIF(B2,"*D*")>0

検索条件が異なるだけで、セル(ここでは範囲ではなく、単独のセル=B2)を調べて、条件に合致したら=1がカウントされたら=「0以上なら」という判定方法を行っている。

ここでB2を指定しているのは、条件付き書式の各ルールの検索対象の“最初”=“左上”のセル=B2から見て自分自身(B2)だからで、このように指定すると、自動的に他のセルでは式の中の対象セルをずらしてくれる。(列や行を固定したい場合は、「$B2」、「B$2」のように「$」を付ける。(「$」については前記事で解説済み。)

これが感覚的に理解できない人が多いが、下記のように考えれば良い。

Joken17

上の例では、B1~B5までの同じ列に条件付き書式を設定しているが、もしC列(C1~C5)も含めてB1~C5を範囲指定したとして、同じ数式を設定してしまうと、例えばC1のセルでは「=COUNTIF(A1,"*○*")>0」の「A1」の部分はC1の左隣、すなわちB1が参照され、「=COUNTIF(B1,"*○*")>0」と読み替えられてしまう。

これを避けるためには、「=COUNTIF($A1,"*○*")>0」と、A列を固定するよう記述する。

 

 


 

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行おき)ならば、セル自体が連続していなくても連続した日付を表示できる。


 

2015/02/06

【Excel2013】条件付き書式:条件付き書式を他の行(セル)にコピーする

以前、『【Excel2013】条件付き書式:ある条件の時にセルや行に色を付けたり文字色を変える【覚書】』という記事で条件付き書式のサンプルと使い方を説明しましたが、「条件付き書式のコピー方法」を検索して訪問されることが多いので、追加します。(元記事は既に長いので、別記事にしました。)

 

条件付き書式のコピー(あるいは、範囲の拡張)は下記の2種類の方法があります。

 

  1. 「条件付き書式ルールの管理」で適用先の範囲を広げる
  2. 書式のコピー

 

 

 

 

1.「条件付き書式ルールの管理」で適用先の範囲を広げる

下図を参照して下さい。

 

Joken13

 

2.書式のコピー

先の記事で使用したサンプルで設定した条件付き書式を23行目にもコピーをしたいとします。

 

Excel_syo23

 

まず、コピー元となる行全体(どこでもいいのですが、6行目を使用するならA6~D6)をドラッグして選択(①)し、「書式のコピー」ボタンをクリック(②)します。

 

「書式のコピー」をよく使う場合は、いちいち「ホーム」タブをクリックしなくてもよいよう、クイックアクセスツールバーに追加しておくとワンクリックですむので便利です。

 

Excel_syo24

 

上の画像では、既にクイックアクセスツールバーに追加済みの状態で操作したので、グレイアウトして(=薄いグレーになって)選択できない状態です。
 

 

次にコピー先のセルをクリック(④)するだけです。

 

Excel_syo25

 

上の図のように「書式のコピー」が有効な間(④のクリックを行う前)は、マウスポインターが③のような形になっていますが、コピーが完了したら元のマウスポインターに戻ります。

 

④で「(先頭だけでいい)」と書いているのは、23行目の先頭(A23)だけをクリックすればいいという意味です。

 

もし同じ書式を連続してコピーしたい場合、②でクリックする際にシングルクリック(1回だけクリック)するのではなくダブルクリックをすると、④の作業を行った後でもマウスポインターは③の形のままになります。

解除する場合は[Esc]キー(エスケープ・キー)を押します。

 

念のため、条件付き書式のコピーが成功したかどうか、確認してみます。

 

Excel_syo26

 

C23の条件付き書式を確認(「ホーム」→「条件付き書式」→「ルールの管理」)してみると、正しく設定されているのが分かります。

なお、この方法は、“条件付き書式”だけでなく罫線や文字の属性(色、フォント、etc.)などの一般の書式もコピーされます。

 

尤も、サンプルのように未記入の行がたくさん残っている場合は、数行コピーして、表の一番下に貼り付け(または挿入)した方が簡単かも知れません。下は23行目の下(=24行目の上)に「挿入」する方法です。

 

Excel_syo27

 

Excel_syo28

 

 

 

 


 

2014/08/09

【Excel2013】条件付き書式:保有株式の損益を視覚的に表現する【覚書】

前回の記事『【Excel2013】条件付き書式:ある条件の時にセルや行に色を付けたり文字色を変える【覚書】』では、従来(Excel2003)からある、数式を使った条件付き書式を説明しましたが、ここではExcel2007以降に追加された、「アイコンセット」、「データバー」、「カラーチャート」を試してみたので、これを覚え書きとして記事にしておきます。

まずは簡単な例です。

A列とB列はそれぞれ「氏名」と「(テストの)点数」です。

Excel_jyouken01 図-1

IF関数〔C列〕

まず、C列の「評価」欄ですが、これは条件付き書式ではなくIF関数を使用して3段階(A、B、C)の評価をしたものです。C2に記述した数式を転記すると

=IF(B2>=30,IF(B2>70,"A","B"),"C")

で、意味をフローチャートで示すと次のようになります。

Excel_jyouken02図-2

なお、原さんの評価欄がピンクになっているのは、この列に、数式を使った条件付き書式が設定してあるためで、下のように「評価が『C』ならピンク」という設定です。(B列の条件付き書式は「30点未満ならピンク」という設定)

Excel_jyouken03_2 図-3

条件付き書式を更に2つ(30点以上70点以下、70点より大)設定すれば3色に色分けすることが可能です。

 

 

アイコンセット〔D列〕

Excel_jyouken01 (図-1)

IF関数で行ったような評価をアイコンセットで表現してみました。

Excel_jyouken04 図-4

「書式スタイル」は「アイコンセット」、「アイコンスタイル」は信号のようなスタイルを選択し、70点以上なら緑、70点未満・30点以上なら黄色、30点未満なら赤のアイコンが表示されるようにしています。(IF関数の例と全く同じ条件にするなら、緑のところの比較演算子は「>」とすべきでした。この違いで70点の谷繁さんが緑になってしまいました。)

なお、数値を表示せずにアイコンのみを表示するようにできます。図-4の「アイコンのみ表示」にチェックを入れると、E列のように表示されます。(実際には数字が記入されているが表示されない。)

アイコンセットは他に4色のもの(4段階評価)もありますが、このように、セットを選んだ時点で何段階評価か決定してしまい、増やしたり減らしたりすることはできないようです。
 

データバー〔F列〕

Excel_jyouken01 (図-1)

0点を最小値、100点を最大値とする範囲で、グラフのように点数をバーの長さで示しています。

書式ルールは以下の通りです。

Excel_jyouken05 図-5

カラーチャート〔G列〕

Excel_jyouken01 (図-1)

ルールは以下のように設定してます。

Excel_jyouken06 図-6

これも0点を最小値、100点を最大値とする範囲を使用していますが、3色スケールを使用して、中間値は平均点(B8セルの値=63.33/AVERAGE関数を使用)にしています。

したがって、平均値に近い和田さん(60点)はほぼ黄色、100点に近づくほど緑が濃くなり、0点に近づくほど赤が濃くなります。

なお、最小値と最大値をデータ内での最小値と最大値に設定すると、トップの中畑さんと最下位の原さんが緑と赤それぞれでマックスの濃さになります。(図-7)

Excel_jyouken07 図-7

データバーで損益を視覚的に表現する/「負の値」も表示する

下は、ブログ主が自作した保有株式管理表をアレンジしたもので、株価(時価)を「株価」欄(9)に入力すると、「評価金額」(10)を計算し、「取得金額」(8)に対する「評価金額」の割合を「%」欄に表示します。

これに、評価損益(「増減%」)が視覚的に把握できるよう、データバーを設定してみました。

Kabu_excel01 図-8

実際に記入してある式は、M2セルを例にすると、

=IF(G2=0,"",K2/H2)

です。「=K2/H2」(「損益」÷「取得金額」)だけでもいいのですが、IF関数を使って「#VALUE!」や「#DIV/0!」エラーを回避しています。(この説明が不要な場合は、下のコラムは読み飛ばして下さい。)

IF関数の括弧の中に記述した、「G2=0,""」は、“株数(G2)がゼロならブランクを表示”し、“計算は行わない”ためのものです。

何故このようなことをしているかというと、「=K2/H2」のままで、この式を同じ列の他のセルにコピーすると、未記入の行で“ゼロで割って”しまったり、“数字ではないデータで演算”してしまったりし、これがエラーとなります。前者のエラーが「#DIV/0!」エラー(ゼロ・ディバイド・エラー)で、後者が「#VALUE!」エラーです。

ここでは、未記入の行かどうかの判断を「株数」欄に記入されたかどうかで行っています。

エラーが起こらなくても、式が設定してあると、「0」(ゼロ)が表示されてしまう場合があるので、IF関数でブランクを表示する方法はよく使われます。

この列に設定した条件付き書式は下図の通りです。

Excel_jyouken08 図-9

最小値と最大値はテストの点数のように固定しているわけではないので、「自動」にしています。

また、「負の値」の場合の書式を設定するために[負の値と軸]ボタンを押し、下記のように設定しました。(デフォルト値から変更したのは軸の位置のみ。デフォルトは「自動」。)

Excel_jyouken09 図-10

これにより、セルの中間に黒い縦軸が表示され、益が出ている場合は青いバーが右方向に、損が出ている場合は赤いバーが左方向に表示されます。

条件付き書式に関する外部記事

 

web拍手 by FC2

(拍手ボタンは記事を読んで下さった方とのコミュニケーションツールとして設置しています。面白かった、役に立った、等々、「(・∀・)イイネ」と思われたらクリックしてください。)

 

 


2014/08/04

【Excel2013】条件付き書式:ある条件の時にセルや行に色を付けたり文字色を変える【覚書】

条件付き書式とは -条件付き書式で何ができるのか-

 

まずはサンプルです。

 

Excel_syo01
図-1

 

上はTo Doリストのようなものだと思って下さい。ここで設定してある条件付き書式とは、

 

  • 「完了日」欄に日付が記入されたら、完了ということが分かるように、その行全体をグレーに塗りつぶす。(=同じ行にあるセルA~Dをそれぞれグレーの背景色にする。)
  • 「期限」欄に指定した日付まで1週間(7日間)を切ったら目立つように、日付を赤く表示する。

 

といったものです。

 

これを見て分かるように、

 

『条件付き書式』とは、“ある”セルの値などの条件によって、文字色や背景色、罫線といった『書式』(※)を変えて表示する機能です。“自分自身の”セルの値とは限りません。

 

※ Excel2013では、こういった単純な書式だけでなく、評価をアイコンで表示したり、色の濃淡で表示したり(ex. 最大値に近づくほど濃い色、低いほど薄い色で表示)といった視覚的に効果がある書式が追加されています。(後述→『条件付き書式の拡張機能』)

 

以下、下記の順序で使い方を説明します。

 

条件付き書式は条件を判定するための関数がポイント

ある条件の時にだけ行(セル)に色を付ける

  • 条件式の記入方法
  • 「$」の意味 (絶対参照と複合参照)
  • 比較演算子の種類
  • 複合条件(1) ・・・ OR条件

ある条件の時にだけ文字に色を付ける

ある条件の時にだけ文字に色を付ける

  • 複合条件(1) ・・・ AND条件

条件付き書式を他の行(セル)にコピーするには

条件付き書式の拡張機能

 

 

 

条件付き書式は条件を判定するための関数がポイント

この記事は「○○だったらその行に色を付ける」といった具体的なキーワードで検索されることが多いのですが、「○○だったら」という部分は様々です。

この記事で条件付き書式とは何かということが理解したら、次に「『○○である』ことを判定するのはどうしたらいいか」という観点で調べてみて下さい。

たとえば、「ある文字列(ex. “ABC”)という文字が含まれているかどうか」なら、COUNTIF関数(その文字列が含まれているセルの数が返される)を使います。「曜日を判定する」のなら、「日付から曜日を取り出す」関数であるWEEKDAY関数を使います。WEEKDAY関数が返してくれる値(日曜なら"1"、月曜日なら"2"...)を条件に指定することになります。

 
このように、条件付き書式を使いこなすと言うことは、条件を判定するための関数がキーポイントです。

 

【追記】この、ある条件の時に行全体に色を付けるというテクニックは、カレンダーのような表で、「土曜日」ならその行を青に、「日曜日」や「祝日」ならその行をピンクに、といった使い方をすることができます。土日や祝日の判定に関する検索が多いので別途記事にしました。当記事を読んでいることを前提として記述しています。

Joken01

 

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

【追記3】COUNTIF関数を使用して特定の文字列が含まれているセルに色を付ける方法はこちらの記事をお読み下さい。

 

ある条件の時にだけ行(セル)に色を付ける

図-1の表の2行目の「完了日」欄(D3=D列3行目)に日付を記入した場合にその行(A3~D3)の背景色を全てグレーにするというのは、行とは言っているが、実際にはA3~D3それぞれのセルに条件付き書式が設定してあるために、このように表示されることに注意。(つまり、3行目で言うと、A3、B3、C3、D3、それぞれのセルに、「C3に日付が記入されたら(=ブランクでなければ)、セルをグレーにする」という条件付き書式が設定してあるため、結果としてその行全体が塗りつぶされる。他のセルも同様。)

 

1.この設定を表の全てのセルに行うので、下のように、ドラッグで範囲指定。

 

Excel_syo02
図-2

 

 

2.その状態で、「ホーム」→「条件付き書式」→「ルールの管理」を選択。

 

Excel_syo03
図-3

 

 

3.[新規ルール]ボタンを押す。

 

Excel_syo04
図-4

 

 

4.「新しい書式ルール」ウィンドウが開くので、「数式を使用して、書式設定をするセルを決定」を選択。

 

Excel_syo05
図-5

 

○条件式の記入方法

5.条件を数式で指定する。

 

Excel_syo06
図-6

 

まず「=」(イコール)を記述してから条件式を記入する。ここでは、下記のように記述。(全て半角で記述すること。)

  =$D2<>""

 

意味は、「D2のセルの値($D2)がブランク(""←比較する文字列はダブルクォーテーション「"」で囲むが、ブランクと比較する場合はダブルクォーテーションの間に何も書かなくてもよい)ではない(<>)」という条件を表す。つまり、「完了日欄がブランク以外なら」という条件になる。(「$」や比較演算子「<>」については後述。)

 

参照すべきセル=条件となる「完了日」は一番上のセル(D列の一番上のセル=D2/図-7)を指定。「$」をDの前に付けることで、参照先はD列が固定される。行番号(=2)の部分は可変。(つまり、どのセルから見ても参照先がD列で、行はそのセルに応じて変わらなくてはならないので、このように指定する。)

 

Excel_syo08
図-7

 

○「$」の意味 (絶対参照と複合参照)

Excelでは数式を他のセルにコピーして使うことがよくあるが、コピーすると数式に使った参照先がずれてしまう。これをずれないようにするには、「$」を付けて『絶対参照』(ex. 「$A$2」)の形式にする。

下の例では、E2セルに「=D2*A2」(額面×換算レート)という式を入力し、この計算式を下のセルE3にコピーすると、「=D3*A3」となってしまうが、他の行でも、レートの入力されているA2セルを必ず参照させたい。このような場合に絶対参照形式を使用する。

 

Excel_zettaisansyou

 

列だけ固定する場合は「$A3」、行番号だけ固定する場合は「A$3」のように片側の要素の前にだけ「$」マークを付ける。これを『複合参照』形式という。

なお、「$」は直接入力するか、「A2」セルをクリックして「=D2*A2」のように記述された状態で、[F4]キーを何度か押すと、「$A$2」→「A$2」→「$A2」と切り替わるので、「$A2」になるまで何度か[F4]キーを押す。

 

この場合は、「完了日」の列=「D列」に固定したいので、「$D2」と記述している。

 

○比較演算子の種類

  • ~(右辺)と等しい: = 
  • ~(右辺)と等しくない: <>  (≠の意味)
  • ~(右辺)より大きい: >
  • ~(右辺)より小さい: <
  • ~(右辺)以上: >=   (≧の意味)
  • ~(右辺)以下: <=  (≦の意味) 

 

○複合条件(1) ・・・ OR条件

なお、 条件式(=$D2<>"")の最初に「=」を記入したのは、条件が一つの場合で、複数の場合は、「OR=」として、下記のように括弧の中に条件式をカンマ「,」で区切って記述する。

  =OR(条件式,条件式)
  
   ex. =OR($C31="Sun",$C31="Sat") ・・・ $C31の値がSunまたはSatなら

 

6.図-6の[書式]ボタンを押し、書式を設定する。 

 

Excel_syo07 図-8

 

ここではセルの塗りつぶしをグレーにする。→[OK]で確定。

一覧以外の色を指定する場合は、[その他の色]ボタンを押して指定する。

 

7.前のウィンドウに戻り、今設定した条件付き書式ルールが表示されているのが分かる。

 

Excel_syo09
図-9

 

続けて次のルールを記述することもできるが、ここでは一旦[OK]ボタンを押して終了し、ルールが機能しているかどうか確認してみると、3行目全体がグレーになっているので成功。([OK]ボタンを押すと、作成したルールは保存される。保存する前にテストしたい場合は[適用]ボタンを押すとよい。)

 

Excel_syo10
図-10

 

 

ある条件の時にだけ文字に色を付ける

1.条件付き書式を設定するセル(C列)をドラッグで選択し、「ホーム」→「条件付き書式」→「ルールの管理」を選択。

 

Excel_syo11
図-11

 

 

2.このセルには既に一つの条件付き書式ルールが設定されているので、それが表示されている。ルールを追加するので[新規ルール]を押す。

 

Excel_syo12
図-12

 

 

3.「新しい書式ルール」ウィンドウが開く(図-5と同じ)ので、「数式を使用して、書式設定をするセルを決定」を選択。

 

Excel_syo13 図-13

 

ここで入力する条件は下記の通り。

  =C2-TODAY()<7
 
【訂正】 図-13で、「<」の左辺に書いた括弧は不要。つまり「(C2-TODAY())」ではなく、上のように「C2-TODAY()」だけでよい。

 

「C2」とは「期限」欄=C列 の一番上のセル。ここでは同じ列にしか条件を設定しないので、『複合参照』形式にする必要はない。

「TODAY()」とは今日の日付を取得する関数。つまり、この表を開いたときの日付が値として返されるので、「(C2-TODAY())」の演算は、「期限-今日の日付」を意味する。

この計算結果が「7(日)より小さい場合」というのが「<7」。

[書式]ボタンを押し、以下、この条件に合致した場合に適用する書式を設定する。(図-14、15)

 

3.

Excel_syo14 図-14

 

4.

Excel_syo21 図-15

 

 

5.ルールが追加された。

 

Excel_syo22
図-16

 

 

複数のルールが設定されている場合、上にある方が優先される。順番を変えるには[▼]または[▲]を押す。

 

[OK]で完了し、結果を検証する。(図-1再掲)

 

Excel_syo01
図-1

 

このテストを行ったのは2014年08月3日なので、8月4日と8月9日はルールが適用されて赤く表示されている。

 

○複合条件(1) ・・・ AND条件

ここでは「期限」欄の日付が7日を切って赤くするという条件付き書式を設定した。完了したら=完了日に日付が記入されたら赤くする必要がないのであれば、「AND条件」で複数の条件を設定する。即ち、「=AND(C2-TODAY()<7,D2="")」とする。形式としては、

  =AND(条件式,条件式)

意味は、『期限が7日を切っている〔C2-TODAY()<7〕、且つ、完了日欄がブランク〔D2=""〕である場合』となる。

「D2=""」の「D2」を「$D2」にしなくて良いのは、既に『$の意味』で説明したとおり。

 

条件付き書式を他の行(セル)にコピーするには

関連記事 『【Excel2013】条件付き書式:条件付き書式を他の行(セル)にコピーする』を作成しました。

ここでは、Excel2013を使用して条件付き書式の設定例を説明しましたが、途中の画面(ウィンドウ)が若干違うだけで、古いバージョンのExcelでも同様に操作することができます。

ている。

 

条件付き書式の拡張機能

また、この例は数式を使った条件付き書式ですが、Excel2013には、評価をアイコンで表示したりできるような機能など、従来のバージョンにはなかった表現方法も用意されています。(下図参照)

 

Excel_jyouken01

 

⇒アイコンを使った条件付き書式に関する関連記事『【Excel2013】条件付き書式:保有株式の損益を視覚的に表現する【覚書】

但し、こういった新機能を使ったExcelファイルは従来のバージョンのExcelでは機能しないこともあるので注意が必要です。(ひとにファイルをあげる時などは要注意。)

 

なお、下のように手作業でセル内の文字の一部の書式を変えるのは条件付き書式とは関係ありません。こちらの記事『【Excel2013】セル内の一部の文字だけに色を付けたり書式を変える/「数式バー」の使い方』で説明しています。

 

Excel_font07


 

web拍手 by FC2

(拍手ボタンは記事を読んで下さった方とのコミュニケーションツールとして設置しています。面白かった、役に立った、等々、「(・∀・)イイネ」と思われたらクリックしてください。)

 

 

 


その他のカテゴリー

同窓会・同窓生便り | 我が町 | 【cocolog】 cocologの楽しみ方 | 【cocolog】FC2拍手ボタンを記事に貼る方法 | 【cocolog】Request URI too Largeエラー | 【cocolog】webページの活用 | 【cocolog】アクセス解析・SEO | 【cocolog】カテゴリの管理 | 【cocolog】ココログにfaviconを設置する方法 | 【cocolog】マイフォトの活用 | 【cocolog】今更きけないココログ | 【cocolog】画像の管理 | 【cocolog】知っていると便利なHTMLテクニック | 【IT】 パソコン・インターネット一般 | 【IT】win8/win8.1 | 【IT】 Excel条件付き書式 | 【IT】 Excel/Word一般 | 【IT】Excel/Wordでお絵かき | 【IT】Excel/Wordで画像の背景を透過させる | 【IT】@niftyマイキャビ | 【IT】フリーソフト | 【IT】CANONプリンタ MG7130 | なんちゃってドールハウス1 | なんちゃってドールハウス2 | ┗ 【HowTo】塗料・塗装道具 | ┗【HowTo】クリヤーボンドの希釈 | ┗【HowTo】クリヤー系ボンドの違い | ┗【HowTo】ボンド水 | ┗ 【HowTo】テーブルの作り方 | ┗ 【HowTo】レンガ壁の作り方 | ┗ 【HowTo】椅子の作り方 | ┗【HowTo】100均フォトフレームで壁掛けドールハウスを作る | ┗【HowTo】100均フォトフレームで飾り台を作る | ┗【HowTo】template Maker | ┗【HowTo】ミニチュア製作に役立つWord/Exceのテクニック | ┗【HowTo】筆の洗浄 | ┗【HowTo】資材・塗料の整理 | ┗ 【テーマ別】『大草原の小さな家』シリーズ | ┗【テーマ別】『ローラの小さな家』を作る | ┗【テーマ別】ハロウィン | ┗【テーマ別】クリスマス | ┗【テーマ別】クラインガルテン | ┗【テーマ別】八百屋(greengrocer) | ┗【テーマ別】公園のカフェ | ┗【テーマ別】朝食・モーニングセット | ┗【テーマ別】木骨の家/ビアハウス | ┗【テーマ別】パン屋 | ┗【テーマ別】田舎のパン屋(1) | ┗【テーマ別】正月 | ┗【テーマ別】節分 | ┗【テーマ別】端午の節句 | ┗【テーマ別】夏の風物詩 | ┗【テーマ別】中秋の名月 | ┗ 【道具】ノコギリ・カッティングガイド | ┗【道具】おもちゃの機織り機 | ┗【道具】デジタルスケール | ┗【道具】パイプカッター | ┗【道具】ピンバイス、ミニドリル | ┗【道具】ヘッドルーペ | ┗【道具】リューター | ┗【道具】粘土工作の道具 | ┗【道具】粘土用ニス | ┗【道具】撮影ボックス | ┗【素材】スイーツデコソース | ┗【素材】スチレンペーパー、スチレンボード | ┗【素材】ダイソー樹脂風粘土、その他粘土 | ┗【素材】デコモール | ┗【素材】ドールハウスでよく使う木材 | ┗【素材】フローラルテープ | ┗【素材】ミラーシート | ┗【素材】モデナペースト | ┗【素材】レジン | ┗ 【ドールハウス関連書籍レビュー】 | ┗ 【分野別】家具 | ┗ 【分野別】キッチン用品 | ┗ 【分野別】食器・容器 | ┗ 【分野別】ソーイング・手芸 | ┗ 【分野別】本・雑誌 | ┗ 【分野別】 庭・ガーデン | ┗ 【分野別】ガーデン小物・ファニチャー | ┗ 【分野別】植木鉢・コンテナ | ┗ 【分野別】植物 | ┗ 【分野別】おせち料理・和食 | ┗ 【分野別】洋食・中華 | ┗ 【分野別】和菓子 | ┗ 【分野別】洋菓子・パン | ┗ 【分野別】飲み物 | ┗ 【分野別】野菜・果物 | ┗ 【分野別】その他食品 | 100円ショップで見つけたもの | 【プチ工作】100均アイテムリメイク | 【プチ工作】牛乳パック・日本酒パックで簡単カルトナージュ | 【プチ工作】未分類 | 外国語の学習 | 【生活】大掃除・中掃除・小掃除 | 【生活】地デジの対策 | 【生活】災害/東北関東大震災関連 | 【生活】金融・投資・税金 | 【生活】豆知識・その他覚書 | 【野鳥観察】バードフィーダー(鳥の餌台) | ガーデニングっぽい何か | (´-`).。oO(ブログ主のつぶやき) | 【マスコミ】メディア批判 | 【マスコミ】既存ニュースメディアとネット | 【政治】 テロ等準備罪 | 【政治】 加計学園問題に見るマスコミの捏造 | 【政治】朝鮮半島 | 【政治】その他 | 【社会問題】 龍田小・中のAmazon欲しい物事件 | test | スポーツ | 住まい・インテリア | 文化・芸術 | 旅行・地域 | 映画・テレビ | 書籍・雑誌

サイト内検索
ココログ最強検索 by 暴想
2017年12月
          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
31            

sponsored link


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

ドールハウス工作おすすめアイテム(材料・工具)

カテゴリー

無料ブログはココログ