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