日時
時刻のシリアル値は小数なので、誤差に注意
日付のシリアル値は1990/1/1からの日数ですが、時刻は0から1までの小数になります。00:00:00を0として、そこから1秒毎に86400分の1を足します。
1日は60＊60＊24＝86400秒だからです。
コンピュータでの小数点以下の計算では必ず誤差が出るので、時刻のシリアル値をそのまま使う計算では問題が発生してしまいます。
例）次のようなデータがある時
A1が8:25、B1が9:26
A2が10:25、B2が11:26
C1に=B1-A1、C2に=B2-A2と入力すると表示上は共に1:01となる。
しかし、2つのセルを比較するとFALSEになる。
TIME関数を使って=TIME(9,30,10)のように時刻データを作れば、それを計算に利用しても誤差は発生しません。
また、A1が時刻のシリアル値であったとしても、=HOUR(A1)、=MINUTE(A1)、=SECOND(A1)のように時・分・秒を整数で取得すれば、誤差なく計算することができます。
文字列が勝手に日付に変換されないようにする
1-11や1/21と入力すると勝手に1月11日のように日付データに変換されてしまいます。
以下のいずれかで回避することができます。
- セルの表示形式を文字列にする
- 先頭にシングルクォーテーションを入力する
分数は以下のいずれかで回避できます。
- セルの表示形式を分数にする
- 先頭に0と半角スペースを入力する
例）0 1/2
年・月・日が別のセルに入力されている場合
計算を行うには、3つの値から日付データ（シリアル値）を作るDATE関数を使います。
例）=DATE(A1,A2,A3)
- 1つのセルに文字列として20150220のように入力されている場合
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)) - 逆に日付データから年・月・日を取り出す
=YEAR(A1)、=MONTH(A1)、=DAY(A1)
土日祝日を除いた日数を取得
NETWORKDAYS(開始日,終了日,除外する日が入力されたセル範囲)
日本の祝日は一覧表を用意する必要がありますが、ネットから簡単に入手できます。
例）NETWORKDAYS(TODAY(),A2,祝日マスタ!A2:A195)
今日からA2までの日数。
ただし、土日および祝日マスタのA2からA195の日付は除いた日数。
年齢の自動計算
DATEDIF(開始日,終了日,単位)
例）=DATEDIF(B2,TODAY(),"Y")
第3引数はY、M、D、YM、MD
YMは「○年○ヶ月○日」の○ヶ月
MDは「○年○ヶ月○日」の○日
日付から曜日を出す
TEXT(セル、フォーマット)
例）TEXT(A2,"aaa")
aaa：火
aaaa：火曜日
WEEKDAY関数というものもあるが、TEXT関数の方が簡単です。
文字列
文字列の一部を取り出す
- LEFT(セル,文字数)
- MID(セル,開始位置,文字数)
- RIGHT(セル,,文字数)
住所から都道府県を抜き出す
4文字なのは和歌山県、神奈川県、鹿児島県の3つだけ。
残りはすべて3文字。これを利用する。
例）=IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3))
さらに都道府県以降を抜き出すには、=RIGHT(A2,LEN(A2)-LEN(B2))
（B2に都道府県名を抜き出した場合）
LEN(セル)
文字数を返す
氏名を苗字と名前に分ける
半角スペースで区切られている場合、
・苗字=LEFT(A2,FIND(" ",A2)-1)
・名前=RIGHT(A2,LEN(A2)-FIND(" ",A2))
FIND(検索する文字,セル)
文字列比較
EXACT(セル,セル)
TRUE/FALSEを返す。
＝による比較の場合、アルファベットの大文字と小文字は区別されない。
全角文字を半角に変換
ASC(セル)
置換関数
SUBSTITUTE(セル,置換前,置換後)
例）電話番号の-を削除=SUBSTITUTE(A2,"-","")
アルファベッドを連続入力
オートフィルはアルファベッドには対応していないので、CHAR関数で実現するCHAR(文字コード)
例）「A」を表示=CHAR(65)
ROW関数を使って以下のようにするとよい。=CHAR(ROW()+63)
Zの次はAA,ABと続ける場合は、SUBSTITUE関数とADDRESS関数を使う
例）=SUBSTITUTE(ADDRESS(1,ROW()-1,4),1,"")
ADDRESS(行番号,列番号,参照の型)
セルを文字列として返す(A2など)
第3引数
1：絶対参照
2：行は絶対参照となり、列は相対参照
3：行は相対参照となり、列は絶対参照
4：相対参照
ある文字がいくつ入っているか
例）URLの中のスラッシュの数=LEN(A2)-LEN(SUBSTITUE(A2,"/",""))
フリガナの自動入力
PHONETIC(セル)
入力したとおりのカナが出るので、実際のふりがなとは異なる場合は修正が必要。
コメント