- ホーム >
- 基本操作 >
- エクセル(Excel)編 >
その他の基本的な関数 ~ 日数計算・日付・曜日・ふりがな等 ~
関数についてはこれまでに、SUM関数、AVERAGE関数やIF関数、ROUND関数、LOOKUP関数、データベース関数など、すでに様々な関数を紹介してきました。
これまでの学習で、Excelを使って何ができるのか、という入門者のレベルから、Excelを使ってこういう表を作成しようとか、ああいう使い方もできるな、といった「具体的なビジョン」を描くことのできる脱初級者もしくは中級者レベルに達していると思います。
本項では、これまでに盛り込めなかったその他の基礎的な関数をまとめて学習します。すでに関数の挿入の基礎は身に付いているはずですので、スムーズに取得できると思います。
例題14 | いろいろな関数の例題 |
---|
では、No.1の例題から順に取り組んでいきましょう。まず、「常に現在の日付」というのは、いつそのシート(Excelファイル)を開いても、そのときの日付が表示されるということです。
つまり、2012年1月1日に開いたら「2012年1月1日」を、2012年5月7日に開いたら「2012年5月7日」という具合に、参照する日付によって流動的に変化するわけです。
このように常に現在の日付を表示させるには、
TODAY(トゥデイ)関数
を使います。TODAY関数は引数を必要としませんので、とても簡単に使うことができます。「関数の挿入」ウィンドウの「日付/時刻」より「TODAY」を選択するだけです。
これまでに学習した関数であれば、このあとに「関数の引数」ウィザードで引数を入力しますが、TODAY関数では下図のように引数が必要ないというメッセージが表示されます。このまま「OK」ボタンで終了です。
すると、下図のように関数式は「=TODAY()」で、現在(作成時点2012年8月29日)の日付が表示されます。
したがって、セルに直接「=today()」(大文字でも小文字でも可)と記述してもかまいません。タイピングが早い人は直接記述したほうが早いと思います。
そして、セルの書式設定と表示形式 で解説のとおり、任意の日付の表示形式を適用させて完成です。「TODAY」の名のとおり、常に「当日」を表示させてくれます。
次にNo.2は、「曜日」を表示させる関数です。この曜日を表示させるという機能は案外多くの場面で必要とされる機能だと思います。
たとえば、カレンダー形式の予定表を作成する場合、勤務表を作成する場合、単にある日付の曜日を知りたい場合など、案外あるものです。
しかし、この曜日を表示させる関数は、TODAY関数のように単純ではありません。正しく仕組みを理解していないとすぐに使い方を忘れてしまうくらいに少し特殊なやり方をします。
まず、任意の日付の曜日を返す関数は、
WEEKDAY(ウェイークデイ)関数
という関数を使います。(WEEKDAY以外の関数も利用できますが本項では割愛します)一見、文字どおりではありますが、WEEKDAY関数には重要な約束事があります。それは、
曜日に対応する数値しか返すことができない
という制約です。つまり、WEEKDAY関数では「曜日」として「日~月」という文字列を返す機能はありません。代わりに「0~7」の数値を返すのみになります。
実際にWEEKDAY関数のみで、No.2の「C16」セル「1945/8/15」を引数とした場合をみてみましょう。WEEEKDAY関数は、同様に「日付/時刻」より選択します。
上図画面で、「シリアル値」には表示させたい日付である「C16」を記述(クリック)します。(「シリアル値」という概念については、在庫管理表と請求書 を参照してください)
そして重要なのが、次の「種類」です。重要といっても通常は何も入力する必要はありません。省略可能な引数になります。ただし、その概念についてはしっかりとおさえておく必要があります。
なぜなら「種類」に入力する値によって返される値も変わってくるからです。通常、省略した場合には「1」を入力したものとして扱われます。「C16」セルの「1945/8/15」の場合、「種類」を省略もしくは「1」を入力した場合には、下図のとおり、「4」という値が算出されます。
では、「1」以外の引数を入力するとどうなるでしょうか。実際にやってみてください。「2」を入力した場合には「3」、「3」を入力した場合には「2」の結果が返されます。
これがどのような関連になっているのかをあらわしたのが下の表になります。
曜 日 | 日 | 月 | 火 | 水 | 木 | 金 | 土 |
---|---|---|---|---|---|---|---|
種類の番号が「1」のとき または省略したとき |
1 | 2 | 3 | 4 | 5 | 6 | 7 |
種類の番号が「2」のとき | 7 | 1 | 2 | 3 | 4 | 5 | 6 |
種類の番号が「3」のとき | 6 | 0 | 1 | 2 | 3 | 4 | 5 |
通常は省略しますので、「日曜日」から順に「1~7」を対応させていると覚えておけばよいでしょう。このようにWEEKDAY関数単独では数値を返すことしかできません。したがって、
WEEKDAY関数は、他の関数と組み合わせなければならない
ということです。ではどのような関数を使えばよいかというと、数値と文字列を対応させ、変換してくれる関数です。このような機能をもつ代表的な関数として、
CHOOSE(チューズ)関数
があります。このCHOOSE関数とWEEKDAY関数を組み合わせてみましょう。まず、CHOOSE関数の仕組みを理解するために、「関数の引数」ウィンドウをみてみましょう。CHOOSE関数は、「検索/行列」から選択します。
上図のとおり、「インデックス」は値の元となるセルまたは関数を記述します。したがって、この「インデックス」にWEEKDAY関数を記述することになります。
そして、インデックスの数値(WEEKDAY関数によって算出された数値)に対応する値(文字列など)を、その下の「値1~」に記述していきます。
つまり、インデックスが「1」のときは「値1」に入力されている値を返す、インデックスが「2」のときは「値2」に入力されている値を返す、という仕組みの関数です。
たとえば、「値1="超サイヤ人"」「値2="スタープラチナ"」「値3="キングボンビー"」とすると、インデックスに記述する値によって、返される結果が変化していくことが確認できます。下図のようインデックスを「2」とした場合は、「スタープラチナ」が結果として返されます。
値に入力されている「""」は文字列であることをあらわしています。(詳しくは、IF関数 を参照してください)これは自動的に付記されます。また「値」は「254個」まで指定することができます。
では、例題の曜日を返す関数式を記述してみましょう。
インデックスには、先述のとおりWEEKDAY関数を記述します。(関数式のなかに関数式を追加する方法は、IF関数 を参照してください)
そして「値1」から順に、WEEKDAY関数の「種類番号」に従って記述していきます。種類番号が「1」なら「値1="日"」「値2="月"」・・・「値7="土"」となります。
すると、「C16」セル「1945/8/15」の値は、WEEKDAY関数によって「4」が返され、さらにCHOOSE関数によって、それに対応させた値である「水」が返されます。
こうした一連の仕組みを理解しておけば迷うことはありませんが、あいまいに理解したままでいると、WEEKDAY関数の引数にCHOOSE関数をいれてみたりして混乱することがありますので、しっかり覚えておきましょう。
ところで、曜日を表示させる場合、このように「WEEKDAY関数」と「CHOSE関数」を組み合わせて表示することができますが、「TEXT(テキスト)関数」を用いる場合もあります。TEXT関数を使うとTEXT関数単独で曜日を表示させることができますが、TEXT関数は少々汎用的で利用幅も大きいので、本項では割愛し、「応用操作」にて解説することにします。
No.3は、No.2のようにセルを参照するのではなく、特定の日付である「2012/3/11」の曜日を表示させるということです。
この場合は、WEEKDAY関数の引数の「シリアル値」に、セルではなく「1945/8/15」を直接記述すれば、あとはNo.2と同じです。したがって、引数は下図のとおりで、結果は「火」が返されます。
No.4は、フリガナを表示する関数で、
PHONETIC(フォネティック)関数
を使います。PHONETIC関数は「情報」分類から選択します。PHONETIC関数は、下図のとおり、フリガナを表示したいセル「D16」を引数に記述するだけです。
すると、「D16」セルの「鴛鴦鸚哥丸鬱男」のフリガナである「エンオウインコマルウツオ」が結果として返されます。
ただ、PHONETIC関数で知っておくべきことは、フリガナの正否や全角および半角の別などは関数で指定するわけではない ということです。それらを指定するのは、引数に指定したセル、つまり「漢字を入力しているセル」の書式設定に依存します。
この場合、「エンオウインコマルウツオ」は全角カタカナで表示されていますが、それはなぜかというと、PHONTEC関数で全角カタカナを指定しているわけではなく、「D16」セルのフリガナの書式設定が全角になっているからです。
フリガナの編集または書式設定を確認するには、下図のように「ホーム」タブの「ふりがなの表示/非表示」ボタンより選択します。
フリガナを表示させるには「ふりがなの表示」、フリガナを編集するには「ふりがなの編集」を選択します。「ふりがなの設定」は下図のように、全角や半角等を設定することができます。したがって、PHONETIC関数はこの「ふりがなの設定」の設定にしたがうことになります。
No.5とNo.6は、全角カタカナと半角カタカナを相互に変換する関数を使います。No.5は、全角から半角に変換する関数で、
ASC(アスキー)関数
を使います。No.6は、半角を全角に変換する関数で、
JIS(ジス)関数
を使います。両関数とも単純に、引数に対象のセルを記述するだけです。
上図がASC関数、下図がJIS関数の引数の記述になります。
これらはそれほど多用する関数ではありませんが、これらの関数を利用して、入力されている文字列を一括変換する場合などがあります。
注意事項としては、例題の出し方も悪いのですが、ふりがなの書式設定とは関係ないので注意してください。PHONETIC関数は入力されている文字列からフリガナを抽出する関数で、ASC関数とJIS関数は、セルに入力されている文字列そのものに対して適応させる関数になります。
No.7は、セルの値(文字列など)を数値に変換する関数です。数値に変換するという意味は、計算することのできる値である「数値」や「シリアル値」に変換するという意味です。使う関数は、
VALUE(バリュー)関数
を使います。「C17」セルの「555013」は大文字の文字列であり、計算等にあつかうことはできません。(厳密には可能ですが基本的理解として)こういう場合に、VALUE関数で数値に変換してやると、Excelで扱いやすい形式となります。
VALUE関数の使い方自体は、ASC関数やJIS関数と同じで、引数にセルを指定するだけです。
VALUE関数の使い道は、「日付」や「時刻」、「%」などを「シリアル値」に変換して計算する場合や、文字列の数字を数値に変換して計算する場合などです。
例として、CSVというカンマ区切りのファイル形式のデータをExcelに取り込んだときに、数値データが文字列となって取り込まれてしまうことがあります。(数値の先頭に「'」が付加され「'」が先頭につくと文字列になります)
その文字列は、下図のように計算することができません。(下図のように関数で計算した場合)
こういう場合に、VALUE関数で数値に変換すると計算することができます。
実際にはVALUE関数を使わなくても計算できる場合がありますが、重要なことは関数の意味と使い方を理解しておくことですので、計算ができないと思ったときには、文字列をうたがってVALUE関数を適用させてみると良いでしょう。
No.8からは、実は同じ関数を使います。No.8~No.11に共通しているは、「日付間の差を求める」ということです。No.8の「年齢」は日付間の年数の差であり、No.9の「経過月数」は日付間の月数の差、No.10は日数の差になります。
このように、日付間の差を求めることのできる関数として、
DATEDIF(デイトディフ)関数
があります。DATEDIF関数を使うと、年数、単純月数、経過月数、単純日数、経過日数などを簡単に表示させることができます。
ただし、この関数は比較的よく使う関数であるにもかかわらず、Excelの 関数分類の一覧の中に入っていません。
なぜかというと、Microsoftは、他の表計算アプリケーションとの互換を保つために用意された関数のためとしていますが、とにかく入っていないので直接関数式を記述する必要があります。DATEDIF関数の記述方式は、
=DATEDIF(開始日,終了日,"単位")
になります。No.8、No.9、No.10の場合は、「D17」セルが現在で、「E17」セルが生年月日なので、記述式は「E17」セルが「開始日」となり、「D17」セルが「終了日」となります。したがって、
「=DATEDIF(E17,D17,"単位")」(大文字でも小文字でも可)
が関数式となります。最後の第3の引数「単位」は、下記の表のとおりとなります。「""」は自動付加されませんので、直接記述しなければなりません。
単位 | 説明 |
---|---|
"Y" | Yearの頭文字で、期間内の年数を返す |
"M" | Monthの頭文字で、期間内の月数を返す |
"D" | Dayの頭文字で、期間内の日数を返す |
"YM" | Year/Monthの頭文字で、経過した年数を除く月数(1年未満の月数)を返す |
"YD" | Year/Dayの頭文字で、経過した年数を除く日数(1年未満の日数)を返す |
"MD" | Month/Dayの頭文字で、経過した月数を除く日数(1月未満の日数)を返す |
上図の、"Y"、"M"、"D" は単純に経過した年数、月数、日数を返しますが、"YM"、"YD"、"MD" は、No.11のように「●年●ヶ月●日」という表示をする場合に使用します。
したがって、No.8の単位は「"Y"」、No.9の単位は「"M"」、No.10の単位は「"D"」となり、それぞれ「23」、「283」、「8613」が解となります。
最後にNo.11ですが、No.11はこれらを組み合わせて使います。「●歳●ヶ月●日」と表示させるように関数式を記述します。
関数を同列で組み合わせるには「&」を使います。すると、「DATEDIF("Y")」&「DATEDIF("YM")」&「DATEDIF("MD")」というふうに3つのDATEDIF関数を結合することができます。
また、このなかで「歳」、「ヶ月」、「日」は固定の文字列で、「●」はDATEDIF関数の変動値になります。固定の文字列は、関数式から外して「&」と、文字列をあらわす「""」で囲むことによって表示させることができます。
これらを踏まえて関数式を記述すると、下記のようになります。
「=DATEDIF(E17,F4,"Y")&"歳"&DATEDIF(E17,F4,"YM")&"ヶ月"&DATEDIF(E17,F4,"MD")&"日"」
となります。作成時点(2012/8/29)では、「23歳7ヵ月28日」が結果として返されます。例題の回答シートを参照して、その結果と同じであれば正しく記述されています。
DATEDIF関数は直接記述しなければならないので、最初は難しく思えますが、仕組みは単純なので何回かやっているうちに容易に覚えることができます。
ただし、DATEDIF関数に関して注意が必要なことは、
日数の計算に当日を含める場合には、結果に「1」を足さなければならない
ので、このことはよく知っておいてください。つまり、上記の関数式では当日は日数に含まれず、翌日から1日のカウントとなります。当日を含む場合は、「DATEDIF(E17,F4,"md")+1」としなければなりません。
以上で、本項の内容は終了です。本項でも特に課題はありません。例題の引数を変更するなどして練習してみてください。
更新履歴
- 2012年8月29日
- ページを公開。
- 2017年12月29日
- ページをSSL化によりHTTPSに対応。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- ウェブサイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします!