Home > Excel エクセルの使い方-関数/計算式-日付・時間
Excel エクセルの使い方-関数/計算式-日付・時間
「エクセル 時間計算 n分後」
という検索が行われていました。
セルに何らかの時間が入力されているようなときに、そのn分前の時刻を計算する方法を探している方による検索です。
「エクセル 7時間後の時間」
という検索が行われていました。
Excelで7時間後の時刻を計算する方法を探している方による検索です。
日付の計算で、n年後を計算する場合、元の日付を、年・月・日とバラバラにして、年の部分で足し算をして、DATE関数で日付に戻すのが基本的な考え方です。
このサイト・インストラクターのネタ帳で
「エクセル 1年前の日付表示 関数」
といった検索が行われていることがあります。
Excelで、何らかの日付データがあるときに、その1年前の日付を表示する方法を探している方による検索です。
この手の日付計算は、元の日付データを年・月・日にバラバラにして、前後を求めたい部分に加算・減算を行うのが基本です。
YEAR関数を使うと、日付データ・シリアル値から年だけを取り出すことができることをご紹介しました。
データとして年が必要な場合は、YEAR関数を使う必要がありますが、単純にセル上に年だけを表示したいということもあります。
このサイト・インストラクターのネタ帳でも
「エクセルで年だけを表示したい」
といった検索を見かけることがあります。
この場合、Excelをちゃんと使えている方にはお馴染みの［セルの書式設定］ダイアログで設定すれば、OKです。
［セルの書式設定］ダイアログ−［表示形式］タブの［分類］欄で「日付」を選択すると、さまざまな表示形式があり、年と月をだけを表示するような書式も用意されています。
このサイトで
「エクセル 日付データ 1文字ずつ 分解」
といった検索が行われていることがあります。
いずれかのセルに日付データが入力されているときに、1文字ずつバラバラに分解して別々のセルに表示する方法を探している方による検索です。
このサイトでは、文字列を1文字ずつ分解する方法をご紹介しています。
「Excel n週前 日付 関数」
「エクセルで4週間前の日付を表示する関数は？」
といった検索が、このサイト・インストラクターのネタ帳で行われていることがあります。
Excelで、n週間前の日付を表示する関数、n週前の日付を求める方法を探している方の検索です。
Excelで日付は、1900年1月1日を「1」という基準にして、1日経過するごとに「1」増えていくシリアル値と呼ばれる数値として扱われています。
ですから、n日後の日付を求める場合はnを足し算・加算すればOKです。関数を使う必要はありません。
セルの書式設定を行うことで、時間付きの日時データから時間部分を非表示にする方法をご紹介しました。
この方法はあくまでも書式で非表示にするものですから、データとしては残っています。データとしては残っているわけですから、時間部分が非表示になった日時データを、何らかの計算に使うような場合には、セルの書式設定だけで時間を見えなくする方法では問題があります。
計算に利用するのなら、実際に時間部分を削除する必要がありますので、その方法をご紹介しましょう。
日時データから時間部分を削除する具体的方法はいくつかありますが、まずは、Excelの日時データを理解するためにも、是非知っておいていただきたい操作からご紹介します。
このサイトで
「エクセル シリアル値から年だけ取り出す」
といった検索が行われていることがあります。
この検索で求められているものは、大きく2つの可能性があります。
［セルの書式設定］ダイアログ−［表示形式］タブ−［分類］欄で「標準」を選択したり、桁区切りカンマを表示するショートカットキー−［Ctrl］＋［Shift］＋［!］などで、シリアル値を確認してみると、例えば「2010年7月20日」なら「40379」です。
このシリアル値「40379」から「2010」という値を取得したいと考えているケースと、単純に年を意味する「2010」という数字が表示されさえすればいいというケースです。
「エクセル 60歳になる日を調べる方法」
「Excel 生年月日 65歳到達日 計算方法」
といった検索がこのサイトで行われていることがあります。
生年月日がわかっているときに、ある歳になる日付を計算する方法を調べている方の検索です。
この手の検索を注意してみていると、60歳とか65歳といった年齢で検索されていることが多いので、おそらく退職の日付などを計算しようとしている方なんじゃないかと想像しています。
生年月日から年齢を求めたり勤続年数を調べられるDATEDIF関数についてのネタは、このサイトでとても人気があります。
年齢や勤続年数関連では、これとは逆のことをしたいという要望もあるようです。
このサイトで
「エクセルで年齢から生まれ年を調べる関数は？」
といった検索が行われていることがあります。
基準となる年月が決まっていて、そこから年齢や勤続年数を求めるのではなく、年齢や勤続年数がわかっているときに、その基準となる年を求めたいという要望です。年齢なら生まれた年、勤続年数なら入社年を求めたいという要望です。
一週間毎の日付を入力する方法として、オートフィル機能を利用する方法と、［連続データ］ダイアログを利用する方法をご紹介しました。
もう一つ、簡単に更新できる一週間ごとの日付の入力もご紹介しておきます。
DATE関数・WEEKDAY関数を組み合わせて、年・月を入力したときに該当する年月のボックス型カレンダーが表示されるようにする方法をご紹介しました。
他の方法もありますのでご紹介しておきます。
先日ご紹介した方法は、いわば1日に該当する場所を探すということに着目する方法でした。
カレンダーの無料テンプレートネタは、特に年末、このサイトでアクセス数の増えるページです。
現在は2010年年間カレンダーの無料テンプレートネタに、結構なアクセスがあります。
カレンダーといえばこのサイトで
「Excel カレンダー 作成方法」
「エクセル カレンダー 作り方」
といった検索が行われていることがあります。
Excelでカレンダーを作成する方法を探している方の検索です。
「エクセルで翌月の○日を表示する方法」
といった検索がこのサイトで行われていることがあります。
どこかに日付を入力すると、翌月の、例えば10日とか20日とかいった日付が自動的に表示されるようにする方法を探している方の検索です。
「エクセル 勤怠表」
「エクセル 勤怠管理 無料テンプレート」
といった検索がこのサイトで行われていることがあります。
Excelで勤怠管理表を作成しようとしている方、あるいは勤怠管理に使えそうなフリーのテンプレートを探している方の検索です。
一口に「勤怠管理」といっても、要求されるものは、会社・勤務形態などによって実に様々です。
勤怠管理を行うソフトもありますが、コスト面で導入が厳しかったり、自社の勤務形態では使いにくいものだったりすることもあるようです。
そのため、Excelで勤怠管理表を内製しようとする会社も少なくないのでしょう。
ただその場合、残業時間・深夜残業・休日出勤・有給・欠勤といったいくつもの条件を考えると、どうしてもIF関数をネストした数式を作成せざるを得ません。
時間の管理単位も、例えば、30分刻み・15分刻み・5分刻みなどへの切り上げ処理を自動でやりたいという要望も出てきます。
残業・深夜残業・休日出勤・休日出勤残業時などの時間給計算も自動でやりたいという要望が出てくるのも、よくある話です。
そこに、勤務時間が24時間を超えるようなケースも考えなきゃならないとなると、、もう。。
「エクセル 月末までの日数 関数」
といった検索が行われていることがあります。
月末までの日数が何日あるのかを調べる方法を探している方の検索です。
Excelでは引き算をすれば日数を計算できます。
Excelで土日を除いた月初日・第一営業日を求める方法をご紹介しました。
WORKDAY関数を利用すれば、土曜日・日曜日だけでなく、祝日・休日も除外した月初の日・第一営業日を求められます。
WEEKNUM関数を利用すると、指定した日がその年の第何週なのかを調べられることをご紹介しました。
年ではなく、月のうちで第何週なのか、何週目なのかを調べたいという要望も実務では出てきます。
このサイトで
「エクセル 月初日 関数」
といった検索が行われているのを元に、Excelで日付が与えられたときに、月初の日（1日）を求める方法をご紹介しました。
日付から、YEAR関数を使って年、MONTH関数を使って月を取得し、その値をDATE関数に指定するという、Excelで日付関連の計算を行う基本といっていい方法です。
「エクセル 月初日 関数」
という検索を行った方は、おそらく土日を除いた月初日・第一営業日を求める方法などを調べたかったのだろうと推測していますが、YEAR・MONTH・DATE関数を使うのは、Excelの日付計算の重要な技の一つですから、あえてご紹介しました。
今回は、現実的にはこちらの要望のほうが多いと考えられる、土日を除外した月初の日付を求める方法をご紹介します。
このサイトの検索ログで、
「エクセル 月初日 関数」
といった検索が行われているのを見かけることがあります。
月初めの日を求める方法を調べている方の検索でしょう。
正直言えばこの検索キーワードだけからは、どのような
「月初日」
を取得したいのかははっきりしません。
一口に「月初日」といってもさまざまな「月初日」が考えられます。
例えば、
土曜日や日曜日を除いた月初の日
土日と祝祭日なども除いた月初の日
単純に指定された日付と同じ月の1日
などが考えられます。
このサイトで、
「DATEDIF 読み方」
「DATEDIF関数の読み方」
という検索が行われていることがあります。
年齢計算などに使われるワークシート関数：DATEDIFをどう読むのかを知りたいという方の検索です。
大きく分類するとDATEDIFは、
「デイティッドイフ」（デーティッドイフ）
「デイトイフ」（デートイフ）
「デイトディフ」（デートディフ）
といった3パターンの呼ばれ方が行われているようです。
Excelでスケジュール表を作成するときなどに必須となる、日付を自動的に表示させる方法ご紹介しています。
この方法をご存じの方から
「1か月ごとの日付を簡単に更新できる一覧にするにはどうしたらいいのでしょう？」
といったご質問をいただくことがあります。
例えば、
A2セルに「2009/8/1」という日付が入力されているときに、
A3セルに「2009/9/1」
A4セルに「2009/10/1」
A5セルに「2009/11/1」
と表示させ、
A2セルの日付を「2009/8/15」に修正したら、
A3セルが「2009/9/15」
A4セルが「2009/10/15」
A5セルが「2009/11/15」
と自動的に更新されるようにするにはどうしたらいいのか、というご質問です。
1日後の日付を得るには元の日付に「1」を加算すればOKです。
しかし、1か月後の日付は、「30」日後の場合もあれば、「31」日後の場合、「28」日後の場合、さらには「29」日後になる場合もあります。
日付データから、上期・下期の表示を行う方法をご紹介しました。
関連した内容で日付から
第1四半期
第2四半期
第3四半期
第4四半期
という表示を自動的に行う方法をご紹介しておきます。
上期・下期表示と同じような考えでIF関数を使ってももちろん表示可能ですが、別の方法をご紹介します。
「日付から上期下期の表示をするにはどうしたらいいのでしょうか？」
といったご質問をいただくことがあります。
例えば、
「4月1日」から「9月30日」の日付なら「上期」
「10月1日」から「3月31日」の日付なら「下期」
と表示されるようにしたいという要望です。
「年度を表示する関数はないのでしょうか？」
「日付から年度を表示するにはどうしたらいいのでしょうか？」
といったご質問をいただくことがあります。
例えば、
「2009年3月31日」なら「2008」年度
「2009年4月1日」なら「2009」年度
と自動的に表示させたいという要望です。
はてなブックマークで「第97回 これだけは押さえておきたいエクセルの知識 前編」という記事が注目を集めています。
Excelの非常に重要な部分が簡潔にまとめられている悪くない記事です。
ただ、ちょっと気になる点があり、2009年5月21日11時30分現在誰も指摘していないようなので、私からツッコミを。
「来年の成人の日が何日なのかを調べるにはどうしたらいいのでしょう？」
といったご質問をいただくことがあります。
1999年まで成人の日は1月15日に固定されていましたが、ハッピーマンデー制度導入によって2000年からは1月の第2月曜日と年によって変化するようになっています。
毎年日付が変わるため、先のようなご質問をいただくことになるわけです。
このサイトでは、カタカナではなくAlphabetで検索すべきということを何度も繰り返しネタにしています。
先日もこのサイトで
「エクセル イヤー関数」
という検索が行われていました。
シリアル値から年を取得する
「YEAR関数」
について調べたかったのでしょう。
このサイトではYEAR関数を使った以下のようなネタをご紹介しています。
コミュニケーションをする上で、質問をする能力ってとても大切だと常々感じています。
インストラクターな立場では、適切な質問をしてあげられるかどうかが、習得を簡単にするか難しくするかをわけることも多々あると思っています。
もちろんインストラクターは質問を受けることも多く、上手い質問をする方と、いまいちな質問をする方がいらっしゃって、質問がいまいちな方については注意が必要だとも感じます。
何らかの疑問・問題・課題があったときに、その疑問・問題・課題を適切なサイズに分解するという能力が必要になりますが、その分解の仕方が上手い人と、どうもいまいちな人というのが存在すると思うのです。
それによって質問の上手い下手も大きく左右されると感じています。
例えば、
「エクセルで指定した次の日を調べるにはどうしたらいいのでしょうか？」
といったご質問をいただくことがあります。
この質問は要注意だと思うのです。
本当の課題は、
「指定した次の日に何らかのマークを表示したい」
「指定した次の日以降の日になったらセルや文字の色を変えたい」
といったことだったりするときに、
「指定した次の日を調べるには？」
といったご質問になることが多いようです。
IF関数や条件付き書式機能のことはわかった上で、
「指定した次の日を調べるには？」
という質問になっているのならOKなのですが、
「指定した次の日を調べるには？」
といった質問をする方の多くは、IF関数や条件付き書式を理解していないケースが少なくないようなのです。
ですから、中途半端に
「指定した次の日を調べるには？」
という質問に分解するより、本来やりたいことをそのまま質問にしてしまうほうがいいのになあと感じるのです。
もちろん、疑問・課題・問題を適切に分解するという能力をどうやって獲得すればいいのかという大問題があり、その分解方法を上手く教えてあげられるにはどうしたらいいのかは、教えるという立場にある者にとって重要な課題と感じつつ、そのメソッドを見つけられないでいる私もいるわけですが。。。
西暦年から干支を表示する方法をご紹介しました。
非常に似ていることで、西暦ではなく、日付から干支を表示したい、という要望をいただくこともあります。
西暦年からの干支表示と、日付データからの干支表示とは、何が違うのかピンと来ない方もいらっしゃるかもしれません。
この違いがすぐにわかるかどうかで、Excelのシリアル値の概念理解できているかどうかを判定することもできると感じます。
WORKDAY関数を利用すると、土曜日・日曜日・祝祭日などの休日を除外した最終営業日を簡単に表示することができることをご紹介しました。
このサイトでは干支早見表を作成する方法をご紹介したことがありますが、
「西暦から干支を求めたいのですが・・・？」
「西暦の年から干支を表示するにはどうしたらいいのでしょうか？」
というご質問をいただくことがあります。
日付から曜日を表示するには関数を使う必要はありませんが、西暦年から干支を表示するには関数を使わなければなりません。
「シリアル値を月に変換したい」
という要望に対して、シリアル値の入力されているセルで書式設定をすれば月が表示されることをご紹介しました。
土日を除外した月末日関連のネタを何度かご紹介してきました。
EOMONTH関数とWEEKDAY関数を使って土日を除いた月末最終営業日を取得する方法、DATE関数・YEAR関数・MONTH関数を使って同様のことを行う方法、スケジュール表に月末最終営業日を表示する方法、土日を除外した月末最終営業日の前日を取得する方法です。
除外するのが土曜日・日曜日だけならこれまでご紹介した方法でいいわけですが、実務では祝日・祭日・振り替え休日・その他の休業日も除外したいという要望のほうが、むしろ多いんじゃないかと思います。
土日を除いた月末最終営業日を取得する方法をご紹介してきました。
EOMONTH関数とWEEKDAY関数を使った方法と、EOMONTH関数を使わずにDATE関数・YEAR関数・MONTH関数を使った方法です。
似たようなことですが、
「土曜日と日曜日を除外して、月末の最終営業日の、前日を求めるにはどうしたらいいのでしょうか？」
といったご質問をいただくこともあります。
▼操作方法：土日を除外した月末の最終営業日の前日を求める
※A1セルに日付を入力したときに、A6セルに月末最終営業日の前日を表示する例
A6セルに
「=IF(OR(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=7,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=1),DATE(YEAR(A1),MONTH(A1)+1,-3),IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=6,DATE(YEAR(A1),MONTH(A1)+1,-2),DATE(YEAR(A1),MONTH(A1)+1,-1)))」
という数式を入力する
EOMONTH関数を使わずにDATE関数・YEAR関数・MONTH関数を使って、土日を除外した最終営業日を取得する場合の数式が、
「=IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=7,DATE(YEAR(A1),MONTH(A1)+1,-2),IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=6,DATE(YEAR(A1),MONTH(A1)+1,-1),DATE(YEAR(A1),MONTH(A1)+1,0)))」
でしたが、土日を除外した前日になると、
「=IF(OR(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=7,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=1),DATE(YEAR(A1),MONTH(A1)+1,-3),IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)=6,DATE(YEAR(A1),MONTH(A1)+1,-2),DATE(YEAR(A1),MONTH(A1)+1,-1)))」
と、更に長い数式になってしまいます。
↑これを見た時点で、Excelの数式に慣れてない方はイヤになっちゃうでしょうけど。。。
EOMONTH関数・WEEKDAY関数・IF関数を使って、土曜日・日曜日を除いた月末の最終営業日を取得する方法をご紹介しました。
EOMONTH関数は分析ツールアドインに含まれる関数なので、利用を躊躇する方もいらっしゃいます。
「土日だけ表にしたいのですが・・・？」
「土曜日と日曜日だけのリストを作成するにはどうしたらいいのでしょう？」
といったご質問をいただくことがあります。
EOMONTH関数を使って、土日を除いた月末の最終営業日を表示する方法をご紹介しました。
Excelでスケジュール表を作成している方からは、土日を除外した月末最終営業日をスケジュール表上にわかりやすく表示したい、という要望をいただくことがあります。
月末の日を簡単に求められるEOMONTH関数についてご紹介しました。
このEOMONTH関数を知った方などから、
「土日をのぞいた月末最終日が出るようにしたいのですが・・・？」
「土曜日と日曜日を除外して、月末の最後の日を求めるにはどうしたらいいのでしょうか？」
といったご質問をいただくことがあります。
実務では、月末最終営業日に特定の業務を行うことがあったりするわけで、その日付を簡単に調べたいということです。
関数について調べるのに、カタカナで検索するよりAlphabetで検索すべきだということを、何度もネタにしてきています。
検索ログを眺めていたら
「エクセル ウィークデイ関数」
という検索が行われているのにきづきました。
「ウィークデイ関数」とカタカナで検索してらっしゃいますが、
「WEEKDAY関数」について調べたかったのでしょう。
Excelのワークシート関数には、カタカナ検索されやすい関数と、そうでない関数とが存在しています。
「勤務時間計算を行う関数は？」
といったご質問をいただくことはよくあります。
こういったご質問について、時間計算は基本的に関数を使う必要はなく、引き算でできることや、Excelで疑問が出てきたときによく考えずに「関数」というキーワードをつけて検索するのをやめるべきだ、ということをご案内しました。
単純に退社時刻から出社時刻を引く方法で勤務時間を計算しましたが、実務では休憩時間もあります。
「勤務時間計算をする関数は何ですか？」
Excelに関していただく代表的な質問の一つです。
そして、ヒトの学習課程について考えさせられる質問でもあります。
関数という単語を覚えたばかりの方、
Excelにちょっとだけ慣れた方は、
「○○する関数は何ですか？」
という質問をしてしまいがちです。
Excelに本当に慣れた人に対してこのような質問する場合はいいのですが、検索する場合は大きな問題があります。
この最たるものが曜日表示についての検索です。
同様のことが
「Excel 勤務時間計算 関数」
という検索でも言えます。
サイトの検索ログを眺めていると、多くのことを学ばされます。
パソコン講習会や実務の現場では、いただいたことのない疑問・質問を、検索ログで見かけることがあります。
その最たるものが
「DATEIF関数 エクセル」
という検索です。
「DATEIF関数ってどうやって使うのですか？」
というご質問を、パソコン講習会や実務の現場でいただいた記憶はありません。
そりゃあそうです。
「DATEIF関数」
なんて存在しませんから、Excelには。
「指定した日付がその年の何週目かを調べたいのですが．．．？」
というご質問をいただくことがあります。
このサイトの検索ログを眺めていて
「DATEDIFF関数 エクセル」
といった検索をしている方がいらっしゃるのに気付きました。
なんとも微妙な検索です。
この方は本当に、
「DATEDIFF関数」
を検索していたのでしょうか。
本当は、
「DATEDIF関数」
を検索したかったのではないでしょうか。
「Excelで、ある年がうるう年かどうかの判定をするには、どうしたらいいのでしょうか？」
というご質問をいただくことがあります。
年が4で割り切れるときはうるう年で、でも100で割り切れるときはうるう年ではなくて、でもでも400で割り切れるときはやっぱりうるう年というのが、2000年問題以後有名になったうるう年の判定ロジックですが、これを計算式にしようとすると結構面倒です。
nか月後の日付を求める方法と、2つの方法の違いについてご紹介しました。
同様の要望で、n年後の日付を求めたい、自動的に計算して表示したいということもあります。
nカ月後の日付を求める2種類の方法をご紹介しました。
nカ月後の日付を求めるという計算を大きく見れば同じなのですが、細かい部分で実は違っています。
日付データに単純に足し算をすれば、n日後の日付が求められることをご紹介しました。
実務ではnヵ月後の日付を求めたいということもよくあります。
この場合は単純に30や31をを足すというわけにはいきません。
１ヵ月は、28・29・30・31日の4パターンがあるためです。
そのためにこのサイトでは以前EDATE関数を使って、nヵ月後の日付が求められることをご紹介しました。
関数を使わなくても、引き算をすれば日数計算ができることをご紹介しました。
期間計算が引き算でできるのですから、ある日付のn日後がいつなのかを調べることも、関数を使わなくてもできてしまいます。
日数・日付関連の計算について、何度かご紹介しています。
日数・日付関連計算の一つ、DATEDIF関数を使った年齢計算は、このサイトの人気ページの一つです。
DATEDIF関数は、大人の事情で［関数の挿入］ダイアログなどから挿入できないこともあり、結構有名だったりします。
しかしそのことによって、Excelの日数計算はDATEDIF関数を使わないとできない、と思い込んでる方もいらっしゃるようですが、そんなことはありません。
「指定した月の日数を計算するにはどうしたらいいですか？」
「今月が何日あるのかを調べるにはどうしたらいいのでしょうか？」
というご質問をいただくことがあります。
Excelの日数計算の定番といってもいいご質問の一つです。
「TODAY関数とNOW関数の違いは何でしょうか？」
「TODAY関数とNOW関数は何が違うのでしょうか？」
というご質問をいただくことがあります。
例えば、
「m"月"d"日"」
というような表示形式が設定されているセルに、TODAY関数とNOW関数をそれぞれ挿入した場合、2007年6月27日にそのファイルを開けば
「6月27日」
という表示になります。
挿入されている関数が、TODAY関数でも、NOW関数でも同じ「6月27日」という結果になります。
このような状態をご覧になったから、先のようなご質問をいただくことがあるのです。
TODAY関数を使うことで、今日の日付を表示できることをご紹介しました。
このTODAY関数についてパソコン講習会でインストラクションすると、
「前日の日付を表示させるにはどうすればいいのですか？」
「昨日の日付を表示させる場合はどうしたらいいのでしょう？」
というご質問をいただくことがあります。
「今日の日付を必ず表示させるにはどうすればいいのでしょうか？」
「今日の日付を表示させるには何という関数を使えばいいのでしょう？」
Excelを使い始めた方からいただく定番のご質問です。
「年齢の早見表を作成したいのですが．．．？」
「年齢早見表を作成するにはどうしたらいいのでしょうか？」
というご質問をいただくことがあります。
年齢計算をするDATEDIF関数を利用すれば、年齢早見表の作成も簡単です。
DATEVALUE関数を使って西暦を和暦に変換する方法と、言語設定によってはエラーになるケースがあることをご紹介しました。
ご指摘いただいたようにDATEVALUE関数ではなく、DATE関数で西暦年を和暦年にすることもできます。
いえ、シリアル値についてよくわかっていて、DATE関数を使い慣れているのならば、DATE関数を利用する方が簡単と言えるでしょう。
DATEVALUE関数を使って、西暦年を和暦年に変換する方法をご紹介しました。
このネタに関して、言語設定によってはエラーになってしまうというご指摘をいただきました。ありがとうございます。
当然と言えば当然のことです。
年月日の表記スタイルは、国・言語・文化圏によって違うわけですから。
例えば、
「06/05/04」という表記を見た日本人の場合、
「2006年5月4日」と認識する方がほとんどです。
しかし同じ
「06/05/04」という表記をアメリカ人が見る場合、
「2004年6月5日」と認識することが少なくないはずです。
国・言語・文化圏が違えば年月日の表記は異なります。
Excelでも言語設定によって年月日の表示スタイルは異なります。
西暦表示になっている日付データを和暦表示にするのに、関数は不要だということをご紹介しました。
その際、「2006」という数値を「平成18年」と表示するには、関数が必要だということを示唆しましたが、その方法をご紹介します。
Excel内部で日付は、「1900年1月1日」を「1」として、1日増えるごとに「1」加算されるシリアル値と呼ばれる数値です。それを書式設定することによって様々な見た目にしています。
yyyymmdd形式のテキストデータを日付データにする方法をご紹介しました。
A1セルにyyyymmdd形式のテキストデータが入っているのならば
「=TEXT(A1,"0000!/00!/00")*1」
という計算式を入力すると日付データにすることができるという方法でした。
TEXT関数は、第1引数に指定された値を、第2引数の書式を設定した「文字列」に変換する関数なので、それを日付データにするために乗算「*1」を行いました。
この「*1」がいまいちピンとこないという方もいらっしゃるようです。
Excelでスケジュール表を作成する際に必要な、年・月が入力されたら日付部分が更新されるようなシートの作成方法をご紹介しました。
但しこの方法では、2・4・6・9・11月のときに翌月の日付が表示されてしまいますので、そうならないようにする方法を考える必要があります。
月末の日付は、28日・29日・30日・31日の4パターンがありますので、これをどう判断するかがポイントです。
Excelを使って
「スケジュール表を作成したい」
「出勤簿を作りたい」
「勤務時間計算表を作成したい」
という要望をいただくことは少なくありません。
その際にまず必要なのは、年と月を入力したら日付・曜日が自動的に更新されることでしょう。
業務システムによっては、日付データを「yyyymmdd」といった形式の8桁の数字として保存していることは少なくありません。
例えば「2006年3月25日」ならば、「20060325」という文字列でデータ格納しておくという方法です。
こういった形式で保存されているデータをExcelで利用する際に、これらのテキストデータを本来の日付データにしたいという要望をいただくことがあります。
Excelで関数を多少使えるようになった方から、
「曜日を求める関数は何ですか？」
「曜日を求めるには何という関数を使えばいいのでしょうか？」
というご質問をいただくことは少なくありません。
このサイトの検索ログでも
「エクセル 曜日 関数」
といった検索が行われているのを時折見かけます。
曜日を「求める」ワークシート関数としては、WEEKDAY関数が用意されていますが、曜日を「求めるには？」というご質問が、本当に曜日を「求めたい」のかどうかを見極める必要があります。
「曜日を求めるには？」という質問が実は、
「曜日を表示するには？」であることがほとんどである印象を持っています。
で、どこかのセルに日付データがあって曜日を表示させるだけならば、関数を使う必要は一切ありません。
「計算で使用できる最も古い日付」というネタをご紹介しましたが、Excelで古い日付を計算に使う可能性がある場合に、知っておくべきことがあります。
Excelでは存在しないはずの1900年2月29日が存在するということです。
Excelの仕様についてのネタで、Excel 2003のヘルプからの抜粋をご紹介しました。
この中で「計算で使用できる最も古い日付」が「1900年1月1日」となっているが、「1899年12月31日」ではないかとご指摘をいただきました。
その根拠は、OpenOfficeの「Microsoft Excel File Format」というドキュメントに、「0 = Base date is 1899-Dec-31」という記述があるためのようです。
n営業日後の日付を表示させたいということが、実務ではあります。
例えば、見積書の有効期限が発行日の15営業日後までなので、その日付を自動的に表示させたいというようなときです。
Excelで勤怠管理用ワークシートを作っているというケースは多いようです。出社時刻と退社時刻を入力してもらうと、勤務時間が計算されるように作ったワークシートのことです。
かく言う私もサラリーマン時代、会社に出社せずクライアントさんのところへ出勤してそこから退勤するような日々が続くような時期は、Excelで勤務時間の管理をしていました。
こういった勤怠管理は、15分か30分単位で時間を丸めて計算するのが一般的だと思います。丸めの処理を入力する人が行なうようなシートにする場合もありますが、Excelにやらせる方がミスも減っていいでしょう。
で、
時間を15分単位で切り捨てたり、切り上げするにはどうすればいいのか？
というご質問が出てきます。
ユーザー定義書式を使って、日付を漢数字で表示する方法をご紹介しました。
この中で「1989/1/8」を「［DBNum1］ggge"年"」と指定しても、「平成元年」となることはなく、「平成一年」にしかならないことを指摘しておいたのですが、どうしても「平成元年」と表示させたいのだがどうすればいいのか、というご質問をいただきました。
Excelで何らかの時間管理をしている方は少なくありません。
そんな方からよくいただくご質問があります。
「時間を60進法ではなく10進法で表示したいのですが・・？」
「時間を10進表記にするには何という関数を使えばいいのでしょうか？」
といったご質問です。
こういった疑問を持ったことのない方には意味がわかりにくい質問かもしれません。
具体的にいうなら、例えば「9時間30分」を「9.5時間」のように分の部分を小数を使って表示させるにはどうすればいいのかというご質問です。
Excelで日付計算に関連したご質問はよくいただきます。
たとえば、
１ヶ月後の日付を調べるにはどうすればいいのですか？
3ヶ月後の日にちを知るには何という関数を使えばいいでしょうか？
といったご質問です。
Excelでは日付を数値として管理していますから、10日後の日付を調べるのであれば、単純に「10」を元の日付に足し算すれば済んでしまいます。月によって日数が異なることで「1ヶ月後」の日付がほしいときに問題となります。
月の日数は31日・30日・29日・28日とあるわけで、2月以外の月は31日か30日固定ですが、2月は年によって29日になったり28日になったりします。単純に「30」を足すというわけにはいかなくなります。
（もちろん1ヶ月後の日付ではなく、単純に30日後の日付を調べるのならば「30」を足し算して構いません。）
EOMONTH関数を使って月末の日付を求める方法をご紹介しました。
EOMONTH関数は、月の最終日のシリアル値を求める関数ですから、頭を悩ませるまでもなく正しく引数を指定してやれば、月末の日付を取得できますが、［分析ツール］アドインに含まれる関数ですから、アドインの組み込みが行われていないパソコンでは「#NAME?」エラーが発生してしまうという難点があります。
Excelで業務用アプリを作っているような方から、来月末の日付を調べるにはどうすればいいのか、というご質問をいただくことがあります。
たとえば請求書で支払い条件欄などに「翌月末までに．．．」と記述するより、はっきり日付を表示させたいような場合にこのご質問をいただきます。
年齢や経過日数・勤続年数の計算というのは業務の内容によっては頻繁に出てくるので、計算方法を紹介しているWebサイトも少なくありません。
ところがメジャーなサイトでも、間違った方法を紹介していることがあるので注意が必要です。
単純に引き算をしてユーザー定義書式で「yy歳」と設定すればOK、みたいなことを書いてあるサイトがあるのですが、これではダメです。今年の自分の誕生日から生年月日を引いてみればすぐにわかることです。この計算式では誕生日の次の日にならないと年齢が加算されません。引き算をするときに調整を行う必要があります。
Home > Excel エクセルの使い方-関数/計算式-日付・時間
