患者中心の保健医療を支える
処方情報分析のリーディングカンパニー

 03-3239-6840

出番の多い日時の関数

time 2017/05/22

※解説記事内の画像はExcel 2010のものです。

こんにちは。JMIRIの独身貴族「ひろきち」です。

みなさんはExcelをどのような時に使っていますか?「表計算」というくらいですからデータの集計に使うことは基本ですが、ちょっとした作業記録やスケジュール、さらにはセルを正方形にして方眼紙のように使っている人もいるかと思います

今回は、このような時に何かと重宝する「日時の関数」についてお話します。

毎月第○営業日に納品!

弊社ではお客様にレポート等を納品していますが、その際に

”毎月第15営業日に納品”

といった感じの「営業日ベース」で指定のあるケースが多々あります。

この場合、土日もあれば祝日もありますので、第15営業日が何日になるのか、年や月によって替わってきますし、1月のように営業開始が特殊なケースもあります。

このようなことをExcelで管理する場合に便利な関数として「NETWORKDAYS」があります。
以下の画面にあるのが、基本的な構成になります。
第○営業日

A列 営業開始日 この日を第1営業日として計算するイメージです
B列 カレンダー この日が第○営業日にあたるかを計算します
C列 第○営業日 B列の日付が第○営業日にあたるかを表示します
D列 祝日一覧 日曜日を除いた祝日を並べます

 

これを見ますと、C14のセルに「5」とありますので、「2017年1月12日は第5営業日」という意味になります。「2017/1/1~2017/1/4」がマイナスになっているのは、「2017/1/5」が第1営業日で、それより前の日付だからです。

では、この表を関数表記に切り替えてみましょう。
第○営業日_関数表記

C14のセルの「5」という数値は

=NETWORKDAYS($A$3,B14,$D$3:$D$10)

という関数によって計算されています。

この「NETWORKDAYS」という関数には、3つの情報を指定することで計算しているのですが、これを日本語で表現すると

=NETWORKDAYS(営業開始日,判定したい日付,祝日リスト)

となります。

「$」がついたりつかなかったりするのは、C列をマウスでドラッグし、下に伸ばす際に「営業開始日」「休日リスト」の場所指定がずれてしまわないようにしているからです。

「2017/1/6」~「2017/1/9」の営業日がすべて「2」になっていますが、これは

「2017/1/6」は第2営業日、「2017/1/7」~「2017/1/9」は営業日ではないので営業日をプラスしない

というこの関数の性質によるものです。

この数値を使った「条件付き書式」を指定することで、「第15営業日は黄色のセルにする」ということも可能になります。

作業開始から○営業日の日に提出すること

今度は上記ケースの似たようなパターンで、「○営業日」が決まっている

”このレポートは3営業日で仕上げること”

といった感じのケースで、指示する側、指示される側での進捗管理のイメージです。

これをExcelで実現するには「WORKDAY」という関数があります。
以下の画面にあるのが、基本的な構成になります。
○営業日後に提出

A列 作業開始日 この日を第1営業日として計算するイメージです
B列 日数 ○営業日で完了させるといった予定日数になります
C列 提出日 「A列」の日付の「B列」営業日後の日付が表示されます
D列 祝日一覧 日曜日を除いた祝日を並べます

 

これを見ますと、C5のセルに「2017/2/17」とありますので、「2017年2月3日の10営業日後は2017年2月17日」という意味になり、単純に10日後の「2017/2/13」になっていないところがポイントです。

では、この表も関数表記に切り替えてみましょう。
○営業日後に提出_関数表記

C5のセルの「2017/2/17」という日付は

=WORKDAY($A5,$B5,$D$3:$D$12)

という関数によって計算されています。

この「WORKDAY」という関数も、3つの情報を指定することで計算しているのですが、これを日本語で表現すると

=WORKDAY(開始日,○営業日後,祝日リスト)

となり、「NETWORKDAYS」がわかればイメージしやすいと思います。

注意点は、この「○日営業日後」の数値で、ここを「1」とすると、次の営業日が表示されます。これがどうして注意点かというと、「作業開始日当日に完成させろ!(開始日=終了日)」という場合はここに「0(営業日)」を指定する必要があるためです。1日作業しているのに日数が0というのに少し違和感を感じると思いますが、「○日後」という点に注目すると、この動きも納得です。

 

最後に

今回の関数を駆使すると、毎月のルーチンワークで

”各作業の着手が第○営業日という形で決まっている”

”作業にかかる日数が毎月決まっている”

という場合に、第1営業日を指定すれば、その月のスケジュール(目安)を自動で計算してくれるExcelを作ることも可能で、これをチェックすることで、作業忘れをなくすという方法もありだと思います。
それではこの辺でお開きに。

 

タグ

このブログについて

このブログ(JMIRI Office部)は、製薬企業のマーケティング部門の方やアナリティクス/レポーティングを担当している方に向け、Excel / PowerPointなどのOfficeソフトの使い方、分析の考え方、グラフの使い方、美しい資料の作り方、効率的な作業の進め方など、仕事に役立つ様々な情報を少しゆるめに発信していきます。

Author:医療情報総合研究所(JMIRI)について

医療情報総合研究所 JMIRIは、まだビックデータという言葉が無いころに日本で初めて処方情報データベースの運用・分析を開始した、処方情報分析のリーディングカンパニー。これまでに累計数十万ページ規模の分析レポート/グラフを製薬企業に向けて作成/提供してきました。

[詳細]


お問い合わせ

arrowup

mail