第5回Office基本講座

60分で学ぶExcel関数　日付編

◆基本の関数で日付を処理する関数として次のものがある。
　　　ＴODAY関数；TODAY()…今日の日付を求める。
　　　ＹEAR関数；YEAR(シリアル値)…日付のシリアル値から「年」を表す数値を求める。
　　　ＭONＴH関数；ＭONＴH（シリアル値）…日付のシリアル値から「月」を表す数値を求める。
　　　 DAY関数；DAY(シリアル値) …日付のシリアル値から「日」を表す数値を求める。
　　　DATE関数；DATE(年,月,日)…年月日で指定された日付（シリアル値）を求める。
◆Ｅxcelの日付データの実態は、1900年1月1日を「1」とし、1日毎に1づつ増加していく整数のデータである。この数値に日
　付の表示形式を設定することで、日付データとして処理されるようになる。日付データは2020/4/1の形式で表示されるの
　が標準であり、この日付データを「ホーム」タブの数値グループで「標準」にすると、シリアル値が表示される。
◆たとえば、B10セルにTODAY()を入力すると、本日の日付が表示され、他のセルにYEAR(B10)を入力するとB10の年を、
　他のセルにMONTH(B10)を入力するとB10の月を、他のセルにDAY(B10)を入力するとB10の日を示す。6/26と入力する　　本年と自動的に判断し、数式バーに2020/7/4と表示される。
◆日付の表示形式を、セルの書式から、ユーザー設定で「aaa」にすると曜日（月など1文字）、「aaaa」で月曜日と表示される。
◆特定の日付データを求める関数として下記の関数がある。
　　　WORKDAY関数；WORKDAY(開始日,日数,祭日)…開始日から「〇営業日後」の日付を返す。
　　　WORKDAY.INTL関数；WORKDAY.INTL（開始日,日数,週末,祭日）…週末を指定して「〇営業日後」の日付を返す。
　　　EDATE関数；EDATE（開始日,月）…開始日の「〇ケ月後」の日付を返す。
　　　EOMONTH関数；EOMONTH（開始日,月）…開始日の「〇ケ月後」の月末の日付を返す。
◆B3に日付データがある場合、10日後はB4に「=B3+10」、10日前な「=B3-10」とすればよい。単純な加減計算でよい。
◆WORKDAY(B3,10)は、土曜日、日曜日を除いた10日後の日付を返す。
◆WORKDAY(B3,10,D3:D8)は、土曜日、日曜日のほかにD3～D8に示す休日・祭日を除く10日後の日付を返す。
◆WORKDAY(B3,10,11,D3:D8)は、上記で週末を指定（この場合「11」を指定、週末休日は日曜日のみに）した場合。
◆週末の休日は1～17の数値で指定することができる。1～7は2日、11～17は1日の休日指定となる。
◆EDATE（B3,1）は、Ｂ3で指定した日付から1か月後の日付を求める。
◆WORKDAY(EDATE（B3,1）-1,1,D3:D8)は、EDATE関数で求めた日付が日曜日の場合、その翌営業日を表示したい場
　合は、EDATE関数の戻り値から１を引いてその前日を求め、WORKDAY関数でその1営業日後の日付を求めればよい。　
◆EOMONTH（B4,1）は、B4の日付から1か月後の月末の日付を求める。
◆EOMONTH（B4-20,0）+20は、Ｂ4の日付が20日以前なら前月の20日、20日以降ならば当月の20日を示す。
◆WORKDAY（EOMONTH（B5,1)+9,1,D4:D6）は、B5日付の翌々月の10日を設定したい場合、また。10日が休日の場合
　翌月の10日を指定する場合、まず目的月の9日を求め、WORKDAY関数でその1営業日後の日付をもとめる。
◆WORKDAY（EOMONTH（B5,1)+9,1,D4:D6）は、翌々月の10日が休日の場合、翌月ではなくその前の月を指定したい
　場合、まず目的月の11日を求め、WORKDAY関数の引数「日数」に「-1」を指定し、その1営業日前の日付を求める。
◆日付データから期間を求めるには、下記の関数が用いられる。
　　　TODAY関数；TODAY()…今日の日付を求める。
　　　DAYS関数；DAYS(終了日,開始日)…開始日から終了日までの日数を求める。
　　　NETWORKDAYS関数； NETWORKDAYS（開始日,終了日,祭日）…営業日の日数を求める。
　　　INT関数；INT（数値）…小数点以下を切り捨てる。
　　　NETWORKDAYS.INTL関数；NETWORKDAYS.INTL（開始日,終了日,週末,祭日）…週末を指定して営業日を求める
　　　ＤATEDIF関数；ＤATEDIF（開始日,終了日,単位）…指定した単位で期間を求める。
◆Ｂ1に今日の日付、B4に締め切り日などの日付が入力されている場合、今日からB4までの日数を求める場合、=B4-B1でも
　よいが、DAYS(B4,B1)でもよい。
◆NETWORKDAYS（B1,B5）は、土曜日。日曜日を除くB1からＢ5までの日数を求める。
◆NETWORKDAYS（B1,B5,F4:F8）は、土曜日、日曜日のほかにF4～Ｆ8に示す祭日を除いた日数を示す。
◆INT（(B6-B1)/7）は、B6-B1の日数を7で割って小数点以下を削除した週を表す。満の週を求めたことになる。
◆NETWORKDAYS.INTL（B1,B6,0111111）は、休日を7桁の数値で表したもので、この場合は月曜日のみ営業日で、B1
　からB6間での営業日数を求める。
◆ＤATEDIF（B1,B6,"M"）は、B1～Ｂ6までの月数を求めるもの。Yは満年数、Mは満月数、Dは日数、MDは1か月未満の
　日数、YMは1年以内の月数、YDは1年以内の日数を表す。
◆ＤATEDIF（B1,B6,"Y"）は、B1に生年月日、B6に本日の日付であれば、Yの指定で、満の年齢を示す。
◆上記のB6に本日の日付としているが、ＤATEDIF（B1,TODAY(),"M"）としても良い。
◆ＤATEDIF（B1,TODAY(),"YM"）とすれば、1年分の日数を12で割った時の余りの日数を表す。

60分で学ぶExcel関数　文字列編

◆文字の一部を取り出す関数として、下記のものがある。
　　　ＬＥFT関数；ＬＥFT（文字列,文字数）…文字列の先頭から文字数分だけ取り出す。
　　　 RIGHT関数；RIGHT（文字列,文字数）…文字列の末尾から文字数分だけ取り出す。
　　　 MID関数；MID(文字列,開始位置,文字数)…文字列の指示位置から文字数分だけ取り出す。
　　　ＩF関数；IF(論理式,値が真の場合,値が偽の場合)…条件の真偽に応じて異なる処理を行う。
　　　 LEN関数；LEN(文字列)…文字数を数える。　
◆B4に4文字の氏名がある場合（2文字が苗字、2文字が名前）、LEFT(B4、２)で苗字、RIGHT(B4、２)で名前を取り出せる。
◆苗字は2文字とは限らない。B4の氏名には、苗字と名前の間に半角の空白をいれて置き、苗字のセルC4には、
　 LEFT(B4,IF(MID(B4、3,1)="",2,3)) 、名前のセルD4には、RIGHT(B4,LEN(B4)-LEN(C4)-1)とすれば良い。
◆居住地を都道府県とその次以降に分ける方法はとして、都道府県の文字数が3文字と4文字があるので、4文字目が県か
　どうか調べて、県ならば4文字を、県でなければ3文字を抜き出せばよい。その次以降の部分は、今検索した都道府県の
　文字数に1を加えた場所から、20文字分を取り出しても良い。具体的には下記となる。
　　　都道府県を表示するセル（F4）　　　LEFT(E4,IF(MID(E4,4,1)="県",4,3))
　　　その次以降の部分のセル（F5）　　　MID(E4,LEN（F4）+1,20)
◆文字列を検索、置換する関数として下記のものがある。
　　　FIND関数；FIND(検査文字列,対象,開始位置)…対象文字列内での検査文字列の位置を調べる。
　　　SEARCH関数；SEARCH（検査文字列,対象,開始位置）…対象文字列内での検査文字列の位置を調べる。
　　　SUBSTITUTE関数；SUBSTITUTE（文字列,検査文字列,置換文字列,置換対象）…文字列中の検査文字列を置換文
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　字列と置き換える。
　　　ＲEPLACE関数；ＲEPLACE(文字列,開始位置,文字数,置換文字列)…文字列中の一部を置換文字列置き変える。
　　　ＩF関数；IF(論理式,値が真の場合,値が偽の場合)…条件の真偽によって異なる処理を行う。
　　　ISERROR関数；ISERROR（テストの対象）…テストの対象がエラー値ならTRUEを返す。
◆姓と名前の間に半角の空白のある「氏名」と、郵便番号・住所・建物や部屋番号の間に全角の空白のある「住所」の表で、
　姓と名前を分ける方法と、住所および建物を分ける方法は下記のようになる。
◆「氏名」から姓を示すセル（C4)と名前を示すセル（D4）とすると、
　　　C4は、LEFT(B4,FIND(" ",B4)-1)、
　　　D4は、RIGHT(B4,LEN(B4)-FIND(" ",B4))となる。
◆「住所」から住所を示すセル（E4）と建物を示すセル（F4）に分ける方法は下記となる。
　　　E4は、MID(B4,11,FIND(C4,11,FIND("　",C4,11)-11))
　　　F4は、RIGHT(C4,LEN(C4)-FIND("　",C4,11)) となる。
◆住所が「東京都〇区」の場合、○を表示させる場合、ワイルドカードを使用できるSEARCH関数と IF関数を使って、
　 IF(SERACH("東京都*区",C4)>0、"〇","") とすると、検索文字列が存在しない場合、「#VALUE!」のエラー表示になって
　しまう。これを表示させないようにするにはISERROR関数を使う。IF(ISERROR(SEARCH("東京都*区",C4)、"","○”))。
◆指名にある「沢」を「澤」に置換するには、SUBSTITUTE(B4,"沢","澤")となる。
◆Ｃ4にある住所で2番めの全角空白を半角空白に置換するには、SUBSTITUTE(C4,"　","".2)となる。
◆住所（C4）で都道府県を省略したい場合、REPLACE(C4，１１，３，””)、C4の11番目からの3文字を省略する。
◆氏名のセル（B4）で姓にある「沢」を「澤」に置換する場合、IF(MID(B4,2,1)="沢",REPLACE(B4,2,1,"澤"),B4)となる。
◆文字列の表示形式を指定する関数として、TEXT関数がある。
　 TEXT関数；TEXT(値,表示形式)…「値」を表示形式で表した文字列にする。表示形式には16の種類がある。
◆セルの表示形式は、「ホーム」タブの「数値」グループのその他をクリックすると、「セルの書式設定」が開く。「表示形式」タ
　ブの分類で例えば、通貨を選択すると、数値の前に「¥」が付く。右側のサンプルで小数点の桁数や負の場合の表示を選
　択する。分類で「ユーザー設定」を選び、2019/10/8の表示にするには、種類をyyyy/m/dにすればよい。
◆２2019/10/8を和暦で表示させるには、「セルの表示設定」の「表示形式」タブの「分類」を「日付」を、「カレンダーの種類
　」を「和暦」、「種類」を「平成24年3月14日」を選び、「表示形式」タブに戻り「ユーザー定義」にして右側の表示される「種
　類」に表示されている内容を参照する。そして、TEXT(B4,"[＄-ja-JP-gannen]ggge年ｍ月")にするとよい。
◆金額を「壱」のような漢数字にするには、「セルの表示設定」の「表示形式」タブの「分類」を「その他」に、「種類」を「太字」
　にして、「ユーザー定義」に切り替える。右側に表示される「種類」を参照し、TEXT（C4, "金[DBNUM2][＄-ja-JP]Ｇ/標
　準円也"）とすれば良い。
◆B3に10と記載されている月を、英語名に変換するには、TEXT(B3&"/1","mmmm")とすると「Octover」と表示される。
◆B3に11月と記載されている月を英語名に変換するには、TEXT(B3&"1日","mmmm")とすると「Novemver」と表示される。
◆B3に「昭和60年」という「和暦」で表示された日付を「西暦」に変換するには、TEXT(B3&"1月1日","ｙｙｙｙ")とすればよい。

60分で学ぶExcel関数 日付編

60分で学ぶExcel関数 文字列編

60分で学ぶExcel関数　日付編

60分で学ぶExcel関数　文字列編