2008年12月アーカイブ

［書式］　TIME(＜時＞,＜分＞,＜秒＞)

［機能］　＜時＞、＜分＞、＜秒＞ の数値をもとに、その時間のシリアル値を返します。

　　　　　　返り値は、0 以上 1 未満の数値（すなわち24時間未満のシリアル値）となります。

●●●　時間の計算 ＝ 小数の計算　●●●

 

日付は、1900年1月1日を  「1」  とした連番、いわば整数でした。

これに対して時間は、24時間を 「1」  とした小数であらわされます。

 

どうでしょう、この仕組み、とても論理的だと思いませんか？

日付の１日分は 「1」。　時間の24時間分も 「1」 なのです。

 

ですから、たとえば

　　　　「2009年1月1日の正午」

という日時は、

　　　　「39814.5」

というシリアル値であらわすことができます。

 

実際にセルに 39814.5 という数値を入力し、表示形式を 「2001/3/14 13:00」 などにしてみ

れば、このことが確認できます。

39814.5 という数値が、「2009/1/1 12:00」 という表示に変わるはずです。

 

つまり、時間も日付と同様、シリアル値を表示形式で別の姿で見せているだけなのです。

 

 

さて、日時ではなく、単純に時間や時刻をあらわす場合には、整数部分はゼロ、すなわち

　　　　0.xxxx

といった小数であわらします。

 

たとえば１時間は、1/24 ということですから、

　　　　0.041666・・・

になります。

6時間なら 0.25 、12時間なら 0.5 となります。

 

時刻も同様です。午前1時は 0.041666・・・ 、6時は 0.25 、12時は 0.5 ということになります。

 

ここまでが、Excelで時間を扱う場合に知っておかなければならない、基本中の基本です。

 

●●●　TIME関数を試してみよう　●●●

 

TIME関数を使って、時間のシリアル値についてもう少し見ていきましょう。

 

TIME関数は、＜時＞、＜分＞、＜秒＞ の数値を計算元として、時間のシリアル値を求める

関数です。

次のシートのセルB1、B2、B3 に入力された時分秒の値から、TIME関数で時間のシリアル

値を求めてみます。 

 

(1) セルB6 に、次の計算式を入力する。

　　　=TIME(B1,B2,B3)

 

すると、セルには 「12:00 PM」 と表示されました。

 

先ほど、12時のシリアル値は 「0.5」 であると言いましたが、別のものが表示されています。

理由はもうわかりますね。　これも、「表示形式」 のしわざです。

 

確認のため、セルB6 の表示形式を「標準」にしてみましょう。

(1) セルB6 を選択。

(2) ［書式］メニュー から ［セル］ を選択。

(3) ［表示形式］タブ の ［分類］ から 「標準」 を選択。

(4) ［OK］をクリック。

 

すると、確かに 「0.5」 となります。 

 

つまりこのセルは、「0.5」 という計算結果に「h:mm AM/PM」という表示形式が設定され

たことで、「12:00 PM」 という時刻が表示がされていたのです。

 

こうしたの表示形式の設定は、Excelが自動的におこなっています。　セルに TIME関数が

入力された瞬間、「ここの表示は時間にすべきだ」とExcelが判断して、自動設定されたの

です。

もし別のスタイルの表示がよければ、他の表示形式を自分で設定するとよいでしょう。

 

 

●●●　時間の引き算、足し算　●●●

TIME関数は以上です。　ここからは、前回のシートを拡張した次のようなシートを作りながら、

時間の計算をしてみましょう。

 

このシートを作るには、時間関数は何も使いません。　必要なのは

　　・引き算

　　・足し算

　　・適切な表示形式の設定

だけです。

 

 

ではまず、計算元となるデータを入力していきます。

セルC6 には

　　　　12:00

と入力して［Enter］キーを押します。　セルD6 も同様に 18:00 ［Enter］ とします。

 

するとセルには、入力したとおりの時刻が表示されます。

でもこれも、実際に入力されているのはシリアル値です。　表示形式を標準にすると、それぞれ

0.5 と 0.75 であることがわかります。

データを入力した瞬間、セルにはシリアル値が入力され、同時に 「h:mm」 という表示形式が

自動設定されたのです。 

 

 

計算元のデータが入力できたら、E列に計算式を入力していきます。　時間の計算といっても、

難しいものではありません。　ただの引き算です。 

(1) セルE6 に、次の計算式を入力する。

　　　=D6-C6 

(2) セルE6  の計算式をセルE7～E15 までコピーする。

 

これだけで、「終業時刻」から「出勤時刻」を引いた、「勤務時間数」を計算することができます。

 

 

では次に、セルE17 に、勤務時間数の合計を計算してみましょう。

 

(1) セルE6 に、次の計算式を入力する。

　　　=SUM(E6:E15) 

 

ところが・・・・、計算結果をよく見ると、間違っています。　いったいどこがいけないのでしょ

うか。　本来なら、「48:25」 となるはずなのですが。

 

さて、気づいた方もいらっしゃるかもしれませんね。　48:25 が 0:25 。もしかすると、24時間

を超えた部分が表示されてないのではないか・・・と。

 

計算式を入力した際、このセルには「h:mm」という表示形式が自動的に設定されたようです。

実は、 h という書式記号は、シリアル値の整数部分、すなわち24時間を超えた部を無視

する仕様なのです。

 

24時間を超えた部分も含めた時間を表示するには、 

　　[h]

という書式記号を使います。 

 

それでは、このセルに適切な表示形式を設定してみましょう。 

(1) セルE17 を選択。

(2) ［書式］メニュー から ［セル］ を選択。

(3) ［表示形式］タブ の ［分類］ から 「ユーザー定義」 を選択。

(4) ［種類］ のテキストボックスに、次のように入力する。

　　　[h]:mm

(5) ［OK］をクリック。

 

すると次のように、正しい結果が表示されます。 

 

 

さて、ここでも最後に、時間が表示されているセルの正体を見てみましょう。

C列～E列の表示形式を「標準」に設定してみると、次のようになります。

 

思いっきり小数ですね。　これらの数値が、セルが出力している実際の値なのです。

 

 

今回は、リファレンスといいつつも、TIME関数の解説はちょびっと。　時間のシリアル値

を理解するためのダシでしたね。。。

［書式］　DATE(＜年＞,＜月＞,＜日＞)

［機能］　＜年＞、＜月＞、＜日＞ であらわされた日付の、シリアル値を返します。

●●●　日付の正体を見破ろう　●●●

 

今回は、次のような簡単な日程表を作成しながら、DATE関数とシリアル値について理解を

深めていきましょう。　「西暦」、「月」、「日」を変更すると、「日付」と「曜日」が自動的に変化

するというワークシートです。

 

 

・・・・・しかし、

その前にどうしても正確に理解しておかなければならないことが、１つだけあります。

それは、「シリアル値」です。　

 

ということで、まず、日付のシリアル値とは何か。　簡単に言ってしまうと

 

　　「1900年1月1日を 1 とする連番」　（Macでは1904年1月1日を1）

 

です。　つまり、ただの整数なのです。

具体的にあらわすと、次のようになります。

 

「1900年1月1日」　は　「1」

「1900年1月2日」　は　「2」

　　　・

　　　・

　　　・

「2008年12月31日」  は　「39813」

「2009年1月1日」　   は　「39814」

 

では、なぜこれが日付になるのか　－－－　それには、「表示形式」 が深く関わっています。

 

 

このことを確認するために、実際にちょっとした実験をおこなってみましょう。

まず、適当なセルに　2009/1/1　と入力します。

(1) セルB2 に次のように入力する。

　　　　2009/1/1 

すると当然のように、セルには

　　「2009/1/1」

と表示されますね。 

 

次に、そのセルの表示形式を　［標準］　にしてみます。

 

(2) セルB2 を選択。

(3) ［書式］メニュー から ［セル］ を選択。

(4) ［表示形式］タブ の ［分類］ から 「標準」 を選択。

ここで、上図のように、ダイアログボックス内の［サンプル］の部分が「39814」となったのが

確認できたでしょうか。 

 

そして［OK］をクリックしてみます。

(5) ［OK］をクリック。

 

ワークシート上の値も、 「39814」 に変わりましたね。　

 

これがまさに日付の正体です。

 

つまり、「2009/1/1」 という日付の表示は、「39814」 という数値に 「yyyy/m/d」 という表示形式

を設定したものなのです。

 

実は、セルに「2009/1/1」 と入力した瞬間、Excel内部では次の２つのことがおこなわれています。

 

　・　「2009/1/1」 のシリアル値である 「39814」 という数値をセルに入力する。

　・　そのセルに「yyyy/m/d」という表示形式を設定する。

 

このことを理解した上で例題に取り組めば、日付に関する理解がぐっと深まるはずです。

  

 

●●●　日程表の作成　●●●

 

 では、実際にDATE関数を利用した計算式を入力していきましょう。

 

セルA6 には 「西暦」、「月」、「日」 で指定した日付を表示したいので、それらを計算元とした

式を入力します。

(1) セルA6に、次の計算式を入力する。

　　　=DATE(B1,B2,B3)

 

すると、きちんと

　　「2009/1/1」

と表示されましたね。

ここでも、Excelの判断で、自動的に  「yyyy/m/d」 という表示形式が設定されています。

実際に出力されている値は 「39814」 であるということに留意してください。

 

さて、数値ですから足し算ができます。翌日の日付をあらわすには、1 を足せばいいですね。 

(1) セルA7に、次の計算式を入力する。

　　　=A6+1

(2) セルA7  の計算式をセルA8～A15 までコピーする。

 

つまり、１つ上のセルにどんどん 1 を足していくわけです。　こうすることで、計算式を使って連続

した日付を作成することができます。

ちなみにここでも、日付の書式が自動的に設定されています。

 

では次に、曜日の列に取りかかりましょう。 

(1) セルB6 に、次の計算式を入力する。

　　　=A6

(2) セルB6  の計算式をセルB7～A15 までコピーする。

 

すると、A列と同じ日付が表示されてしまいました。　これも、「yyyy/m/d」 という表示形式が

自動的に設定されたことによります。 

では、表示形式を適切なものに変えてみましょう。　今回は、カッコ付きで曜日を表示してみ

ます。 

(1) セルB6～B15 を選択。

(2) ［書式］メニュー から ［セル］ を選択。

(3) ［表示形式］タブ の ［分類］ から 「ユーザー定義」 を選択。

(4) ［種類］ のテキストボックスに、次のように入力する。

　　　(aaa)

(5) ［OK］をクリック。

 

これで、セルの表示がきちんと曜日に変わります。 

あとは配置等を整えれば、冒頭のようなシートが完成します。

 

 

最後に、それぞれのセルが実際に出力している値を、確認してみましょう。

はじめにおこなったのと同様に、セルA6～B15 の範囲の表示形式を「標準」にしてみましょう。

すると、次のようになります。 

 

ただの、数字の連番ですね。

 

このように、

 

　　　セルに表示されている値と、セルが出力する値とは、必ずしも等しくありません。

 

セル上の表示は、表示形式によっていかようにも変化するのです。　ですから、

 

　　　セルの表示にとらわれずに、

　　　　　　　「実際に出力しいる値は何か」ということに気を配る

 

ことが、Excelではとても大切なのです。 

［書式］　WEEKDAY(＜シリアル値＞,［出力方法］)

［機能］　＜シリアル値＞ であらわされた日付を、曜日をあらわす 1～7 もしくは 0～6 の数値に変換します。)

●●●　曜日の表示だけなら、WEEKDAY関数は不要　●●●

 

Excelで日付を扱う場合、まず一番に悩むのが曜日の表示かもしれません。しかし、もし曜日を

表示たいだけなら、実は WEEKDAY関数は必要ありません。 

特定の日付に対応する曜日を表示したいだけなら、次のようにおこないます。

 

まずサンプルとなる日付を入力してみましょう。

(1) 項目名を準備する。

(2) セルA2 に 2008/12/1 と入力する。

(3) セルA2 を選択し、形が ＋ になるようにセルの右下隅にマウスポインタ合わせる。

(4) セルA16 までドラッグ。

 

これで、日付を簡単に入力することができました。

それでは、この日付に対応する曜日を、Ｂ列に表示してみましょう。

(1) セルB2 に次の計算式を入力する。

　　　　=A2

(2) セルB2  の計算式をセルB3～B16までコピーする。

 

この計算式は、A列の内容をそのまま出力するだけの計算式です。つまり、B列の表示は

A列とまったく同じになります。

 

では、いよいよ曜日を表示してみましょう。

(1) セルB2～B16 を選択。

(2) ［書式］メニュー から ［セル］ を選択。

(3) ［表示形式］タブ の ［分類］ から 「ユーザー定義」 を選択。

(4) ［種類］ のテキストボックスに、次のように入力する。

　　　aaa

(5) ［OK］をクリック。

 

これで、セルの表示は曜日に変わります。

 

つまり曜日を表示するには、

 

　　「日付のシリアル値が入力されたセルに aaa という表示形式を設定」

 

するだけなのです。

 

実は、セルA2 も セルB2 も 「39783」 という数値を出力しています。この数値を、

　　yyyy/m/d

という表示形式で表示したのが、セルA2 の 「2008/12/1」 という表示、

　　aaa

という表示形式で表示したのが、セルB2の 「月」 という表示なのです。　　

 

●●●　WEEKDAY関数の使い方　●●●

 

さて、では WEEKDAY関数 はいったいどんな時に使うのでしょうか。

それは、

　　「計算式内で、セルに入力されている日付（シリアル値）に対応する曜日」

を知りたい時です。

 

たとえば次のようなケースです。

Ｃ列に計算式を入力して、日曜日のところには「休日」と表示させるようにしたいとします。

こんな時には、WEEKDAY関数 が必要になります。 

 

この時、セルC2 に次のような計算式を入力し、下方向にコピーします。

　　=IF(WEEKDAY(A2)=1,"休日","")

 

この計算式は、

　　セルA2のWEEKDAY関数の結果が1なら、「休日」を出力しなさい

という意味になります。

 

もしかすると、同じことをするのに、

　　「もし セルB2 が "日" なら」

 という意味で、

　　=IF(B2="日","休日","")

という計算式を考えた方もいるかもしれません。

でも、これは誤りです。

なぜなら、前述のとおり、実際にセルB2が出力している値は、「日」という文字列ではなく、

「39783」という数値だからです。

　　「この数値（シリアル値）が、何曜日か？」

を調べるのが、まさにこの WEEKDAY関数 の役割なのです。 

 

●●●　曜日によって色を変えるのにも WEEKDAY関数　●●●

 

曜日によって文字の色や、セルのパターンの色を変えたいことがありますね。

そんな時には、この WEEKDAY関数 と、前回もやった条件付き書式を利用します。

 

たとえば先ほどのシートで、日曜日の行が ピンク色 になるようにしてみましょう。

(1) セルA2～C16 を選択。

(2) ［書式］メニュー から ［条件付き書式］ を実行。

(3) 「数式が」 を選択。

(4) テキストボックスに、次の計算式を入力する。

　　　=WEEKDAY($A2)=1

(5) ［書式］をクリック。

(6) ［パターン］タブを選択し、［色］から「ローズ」 を選択。

(7) ［OK］をクリック。

(8) ［OK］をクリック。

 

これで、次図のように、日曜日の行がピンク色になりました。もちろんA列の日付を変えれば、

それに合わせて、ピンク色になる行も変化します。

 

 

ここで少し難しいのは、

　　$A2

という表現かもしれません。

Aの前にだけ「$」が付いているのがポイントです。

これは、列だけを絶対参照にした複合参照です。この表現を計算式内で使うと、コピーした際に、

列部分の参照だけ変化させないようにできます。その結果、コピー後のセル参照は、

 

　　　　A　　　　Ｂ　　　　Ｃ

１

２　　$A2　　　$A2　　　$A2　　

３　　$A3　　　$A3　　　$A3　　

４　　$A4　　　$A4　　　$A4　　

　　　　　　　　　・

　　　　　　　　　・

 

といった様子になります。

条件付き書式で入力する条件式でも、このはたらきが継承されます。

逆にこれを知らないと、１列ずつ別々に条件付き書式を設定しなければならないので、かなり

不便です。

 

WEEKDAY関数で重要なのは、実はこの関数そのものではなく、シリアル値と表示形式の関係

をきちんと理解することです。

 

ということで、次回も日付関連の関数をピックアップして、理解を深めていきたいと思います。

 

［書式］　COUNTIF(＜対象範囲＞,＜条件＞)

［機能］　＜対象範囲＞ のセルのうち、＜条件＞ に一致するセルの個数をカウントします。

●●●　２つのリストを照合する　●●●

 

COUNTIF関数は、リストの照合にも活用できます。

 

たとえば次のシートで、

 

　　　　「製品名リスト２」の中で、「製品名リスト１」に存在しないものを探したい 

 

といったケースがあるかと思います。

 

もちろんこれくらいのデータ量であれば、ソートして目で見ていく方法で十分ですが、それぞれ

のデータ量が数百行、数千行にわたるような場合には、ちょっとつらすぎます。

 

 

 

こんな時には、COUNTIF関数を次のように活用できます。 

(1) カウント用に B列を準備する。

(2) セルB2 に、次の計算式を入力する。

　　　=COUNTIF(E:E,A2)

(3) セルB2 の計算式を セルB13～B16 の範囲にコピーする。

 

これで、B列の値が 0 のものは、「製品名リスト２」に存在していないということがわかります。

（厳密には、この計算式では「E列に存在していない」ということを調べています）

 

計算式の意味は・・・、もう説明するまでもないですね。あまりにシンプルですし。

 

あとは、B列でソートしたり、オートフィルタで絞り込んだりすれば、存在しない製品名だけを簡単

に取り出したりすることができますね。

 

 

●●●　リストに存在するデータだけ入力したい　●●●

 

さて、このことが分かれば、さらに活用方法が浮かんでくるのではないでしょうか。

 

たとえば、条件付き書式との併用すれば、何かのデータを入力する際に、

 

　　　　「あらかじめ決められたデータ以外が入力されたら、知らせてくれる」

 

なんてこともできるわけです。

 

では次の表を例に、実際にやってみましょう。

 

　　　　B列には、「製品名リスト１」にある型番だけを入力したい

 

といったケースです。 

 

(1) B列を選択。

(2) ［書式］メニュー から ［条件付き書式］ を実行。

(3) 「数式が」 を選択。

(4) テキストボックスに、次の計算式を入力する。

　　　=COUNTIF($E:$E,B1)=0

(5) ［書式］をクリック。

(6) ［色］から赤色を選択。

(7) ［OK］をクリック。

(8) ［OK］をクリック。

 

ちなみに計算式で、「$E:$E」というように絶対参照になっているのは特に意味はありません。

列ごと指定していますから、このケースでは「E:E」でもかまいません。

 

それではデータを入力してみましょう。

  

 

このように、「製品名リスト１」にない型番を入力すると、文字が赤くなって知らせてくれます。

 

あっ、ちなみにB1のセルが赤くなっているのは副作用。面倒なのでB列全体に条件付き書式

を設定したためです。「使用製品」て文字列は、E列には存在しないですからね。律儀に赤い

色で表示してくれています。

［書式］　COUNTIF(＜対象範囲＞,＜条件＞)

［機能］　＜対象範囲＞ のセルのうち、＜条件＞ に一致するセルの個数をカウントします。

●●●　データの重複を避ける２通りの方法　●●●

 

データの重複を避けるには、

 

　　（１） 重複をチェックして、知らせてもらう

　　（２） 重複を禁止して、入力できないようにする

 

の２通りの方法が考えられます。

 

（１）は、COUNTIF関数と一緒に、「条件付き書式」を利用します。

（２）は、COUNTIF関数と一緒に、「入力規則」を使います。

 

さっそく、それぞれのやり方を見ていきましょう。

 

  

●●●　重複をチェックする　●●●

ではまず、次の例題で、

　　「電話番号が重複すると、赤い文字で知らせてくれる」

ようにしてみましょう。 

 

 

(1) A列を選択。

(2) ［書式］メニュー から ［条件付き書式］ を実行。

(3) 「数式が」 を選択。

(4) テキストボックスに、次の計算式を入力する。

　　　=COUNTIF($A:$A,A1)>1

(5) ［書式］をクリック。

 

(6) ［色］から赤色を選択。

(7) ［OK］をクリック。

(8) ［OK］をクリック。

 

これで準備ができました。

 

実際にデータを入力してみます。

 

 

Ａ列に同じ内容が入力されると、このように文字が赤色で表示されます。

 

さて、この計算式ですが、

　　「Ａ列に、セルA1の値が１個より多くあるかどうか」

という意味の条件式です。

これが、TRUE になる場合には、赤色で表示されるわけです。

 

なお、このように、セルA1を相対参照で指定した計算式にすることで、

　　セルA2　　=COUNTIF($A:$A,A2)>1

　　セルA3　　=COUNTIF($A:$A,A3)>1

　　セルA4　　=COUNTIF($A:$A,A4)>1

　　　　・
　　　　・
　　　　・

といったように、それぞれのセルに適切な計算式で条件付き書式が設定されます。

 

 

 

●●●　重複を禁止する　●●● 

 

次に、同様の例題で、

　　「電話番号が重複すると、エラーメッセージが表示されて入力できない」

ようにしてみましょう。 

 

(1) A列を選択。

(2) ［データ］メニュー から ［入力規則］ を実行。

(3) ［入力値の種類］ から 「ユーザー設定」 を選択。

(4) ［数式］ に、次の計算式を入力する。

　　　COUNTIF($A:$A,A1)>1

(5) ［OK］をクリック。

 

計算式は同様ですが、入力規則では「=」が付かないので注意してください。 

 

さてこれで準備完了です。

 

今度も同じようにデータを入力してみると、次のようになります。

 

 

このようにエラーメッセージが表示されて、入力が禁止されます。

 

 

COUNTIF関数は、集計ばかりでなく、このように他の機能と併用した活用方法があります。

色々な局面で役に立つ機能なので、覚えておくといいと思います。

ところで、「条件付き書式」は、あまり時間のかかる計算をさせると、入力のレスポンスが悪くなる

場合があります。ここでは A列全体を＜対象範囲＞にしましたが、状況に応じて工夫すると良い

でしょう。

また、入力規則は、「セルにデータが入力された瞬間」だけに動作します。コピーして貼り付けた

ような場合、動作しないので注意が必要です。