Excel 関数 文字列の操作

Excelで文字列の処理は実に多いです。よく使ってるものを紹介します。

一部文字を返し
左から取る　LEFT(文字列, 文字数) 　
※LEFT(A1, 4)=「TEXT」
LEFT(A1, 2)=「TE」
LEFT(A1, 0)=空白
LEFT(A1, 5)=「TEXT」

右から取る　RIGHT(文字列, 文字数)　　LEFTに参照する
中から取る　MID(文字列, 開始位置 [, 取りたい文字列の長さ ] )

特定範囲内の検索

COUNTIF(範囲, 検索値)

LOOKUP(検索値,検索範囲,対応範囲)

検索範囲内で検索値を見つけて、対応範囲内同じロウの値を返します。

VLOOKUP(検索値,検索範囲,列番号,[検索方法])

検索値:検索範囲の第1列中で検索します。

列番号:範囲内の第*列を返します。カウントは1から。

検索方法は「TRUE」または「FALSE」 を指定します。「TRUE」は、指定した検索値と一部一致です、「FALSE」は、指定した検索値と完全一致です。

文字列の組み合わせと変換

CONCATENATE(文字列1, [文字列2], ...)

※例: CONCATENATE("A", "B", "C")=ABC

CHAR(CODE("A"))=A
CHAR(数字)は1〜255までの数字を入力できます。
Macintosh環境は文字セット、Window環境はANSI。
CHAR(11), CHAR(12)をやってみよう。

CODE(文字列)はCHAR(数字)の逆です。

EXACT(文字列1, 文字列2)は文字列の比較、大小文字が区別できます。

EXACT("A","a")=FALSE; EXACT("A","AB")

FIXEDは数字に桁数の区分けなど

続く

Read more

Excel関数 日付から曜日、祝日を求める

今日は何曜日ですか、明日は祝日ですか。
カレンダーで調べじゃなく、Excelの関数で判断しよう。

曜日の関数は「TEXT(日付,“aaa")」
または「CHOOSE(Weekday(日付,1),"日","月","火","水","木","金","土")」

また、「WEEKDAY(日付,1)」の代わりに「MOD(日付,7)」を利用します。

例え

判断式：「=IF(OR(MOD(日付,7)=1, TEXT(日付,"aaa")="土"),"TRUE","FALSE")」

Tureの場合は週末です。

祝日についての考え方は祝日リストの中で出る日は対象です。

まず今年の祝日リストを作ります。
例：2015年の祝日

日付　　　　　　　　　　　祝日
2015年1月01日　　(木)　　元日
2015年1月12日　　(月)　　成人の日
2015年2月11日　　(水)　　建国記念の日
2015年3月21日　　(土)　　春分の日
2015年4月29日　　(水)　　昭和の日
2015年5月03日　　(日)　　憲法記念日
2015年5月04日　　(月)　　振替休日
2015年5月04日　　(月)　　みどりの日
2015年5月05日　　(火)　　こどもの日
2015年7月20日　　(月)　　海の日
2015年9月21日　　(月)　　敬老の日
2015年9月23日　　(水)　　秋分の日
2015年10月12日　　(月)　　体育の日
2015年11月03日　　(火)　　文化の日
2015年11月23日　　(月)　　勤労感謝の日
2015年12月23日　　(水)　　天皇誕生日

日付を全部選択して、右クリークして、「名前ボックス」に名前を入力して［Enter］キーを押す
または、名前をつけたいセルを選択⇒メニュー［挿入］−［名前］−［定義］をクリック⇒［名前の定義］ダイアログ−［名前］欄に名前を入力し、［OK］ボタンを押す
※［名前の定義］ダイアログは、ショートカットキー［Ctrl］＋［F3］でも表示できます。

ここは「holiday」という名をつけます。

そして、判断式は「=IF(COUNTIF(holiday,日付)>0,"TRUE","FALSE")」
Tureの場合は祝日です。
※説明：COUNTIF(範囲, 検索条件)　範囲内もし検索条件を見つけたら+1、つまり「COUNTIF(holiday,日付)>0」は日付は一回「holiday」リストに出たら、[COUNTIF(holiday,日付=1]>0

Read more

EXCELで勤務表の作成(基本の機能 勤務時間の集計)

Excelでは勤務時間の集計は簡単です。

まず開始時間、結束時間、休憩時間を入力します。

例:

A1 開始時間  A2 9:00

B1 結束時間  B2 18:00

C1 休憩時間  C2 1:00

1日の稼働時間の計算は簡単です。B4に「=B2-A1-C3」を入力します。

でも、朝出勤の時は開始時間しか知らないので、B2に時間を入力すると、D4の表示は正しくないです。ですから、IF判断を追加します。B2が空白の時、D4は計算しなくて空白のままにして欲しい。

D2は「=IF(B2="","",B2-A2-C2」に修正します。

まずは一週間の稼働時間を全部入力して、稼働時間を計算します。

「=SUM(D2:D6)」で入力します。

でも、結果の表示は変です。これは関数の問題じゃなくて、このセルの書式の問題です。

「Ctrl+1」を押して、書式を修正します。

タブ「表示形式」を開いて、分類の「ユーザー定義」を選択して、右種類の「[h]:mm」を選択します。

Read more

EXCELで勤務表の作成(基本の機能 日付)

私はExcelで勤務表を作って、一ヶ月一回だけですが、月が変わると日数の修正する手作業が飽きました。

日数の計算

っと、関数 EOMONTH(開始日,月) を使って見よう。

Excelの例を見ると、簡単で説明しよう

EOMONTH(A1,1)はA1の日付から1ケ月後の最終日を調べる

EOMONTH(A1,0)はA1の今月の最終日を調べる

EOMONTH(A1,-1)はA1の日付から1ケ月前の最終日を調べる

まず、A1に計算したいの月の1日の日付を入力する。

例:2015/2/1

A2に「=A1+1」を入力して、Enterキーを押すと、A2は「2015/2/2」となります。

A3に「=A2+1」を入力して、Enterキーを押すと、A3は「2015/2/3」となります。
A4に「=A3+1」を入力して、Enterキーを押すと、A4は「2015/2/3」となります。
...

このようで、早速28日までしよう。

どころで2015年の2月は28日で大丈夫ですが、29日の年はどうだ？

直接、A29に「=A28+1」を入力するのはダメだね。でもやっぱり2月29日がある年も自動判断にしたいです。

ここからはEOMONTH(開始日,月)の出番だ。

A29に「=IF(A28=EOMONTH(A1,0),"",A28+1」を入力する。

※EOMONTH(A1,0)の値は2月の最終日となり、A28はもし2月の最終日なら、TUREとなって、「""」空白を表示します。最終日じゃないなら、1日をプラスします。

試しのため、A1に2月29日ある年の2月1日を入力して見なさい。

2月以外のは30日や31日やですから、「=A29+1」と「=A30+1」はどうやって無理です。
A30に「=IF(A29=EOMONTH(A1,0),"",A29+1)」を入力してみると、A29は値がないのため、A30はエラーを表示します。ですから、「=IF(AND(A29<>"",A29<>EOMONTH(A1,0)),A29+1,"")」に修正する。

※関数ORを使って、A29は空白または最終日の場合、A30も空白です。

A31で「=IF(AND(A30<>"",A30=EOMONTH(A1,0)),"",A30+1)」を入力します。

そして、A1の日付を変わってみると、月が変わると、日数も自動で変わります。

曜日の表示

月曜日、火曜日…日曜日で一々入力するなんでやっぱり嫌です。
次は曜日を自動表示するようにします。

意外と簡単の関数で実現できます。それはTEXT(指定した値,表示形式)です。

例え、「=TEXT(A1, "$0.00")」の場合、＄42036.00と表示します。

でも欲しいのは曜日ですね。

B1に「=TEXT(A1, "aaa")」を入力して見よう。Bingo！出ました。

このようで、私は最初日だけ入力します、日付は全部Excelにお任せ。

Read more

勤務表の作成(ポイントメモ)

勤務表はよく使ってるが、いい勤務表とはどうのようだろう。ポイントをメモしておく。

一、基本の機能

　これはないと、勤務表じゃない。
　　日付の表示
　　勤務時間の集計
　　他：氏名、IDなど

二、使いやすさ

　時間の書式は自動で統一する
　毎月の日付は自動入力したい、月は何日があるのは自動的に判断したい
　

三、見た目
　使い心地

Read more