一番上の入り口へ > excel > ちょっと上ゆく関数を極める!

ちょっと上ゆく関数を極める!

文字列関数
日付関数
場所を表す関数
情報関数

文字列関数


文字の長さをカウントしたい場合は
LEN関数を使います。 
LEN("帝釈天") = 3

LENBを使うと全角文字が2倍にカウントされるとおおざっぱに理解してください。

ちなみにカウント数分文字に置換したい場合 REPT関数が便利です。

=REPT("*-", 3) 指定した文字列を 3 回繰り返して表示します (*-*-*-)
=REPT("-",10) ダッシュ (-) を 10 回繰り返して表示します (----------)
進捗に対する使用例

日付関数

日付関数を使いこなすには
書式、シリアル値、日付の使いこなし(和暦などの表現方法)を理解していれば
問題なく使いこなせます。

シリアル値
excel は 1日間が シリアル値として 1の大きさに対応しています。1900/1/1 0:00:00 が1で、以後一日ごとに1ずつ増え、10年で約3652の値になります。
さらにいうと 12時間は 0.5 になります。秒などの単位の変換は 60倍したり 24で割ったりをすれば可能です。また下で紹介する関数でも計算できますし、書式を設定するだけで Excelが自動に変換してくれます。

今の時間に 3時間足したい場合 (今の時間) + 3/24 を足します。その後セルの表示書式で m/d h:mmなど指定します。
確かめるために、適当な 日付や時間をセルに入れてから、書式を数値に変更すると
シリアル値を見ることができます。

書式
書式については TEXT関数を利用して表現を変える方法と、セルの書式からかえる方法があります。
24時間を超える場合とか、曜日の書式とかあるので関数よりも先に書式をマスターしておいた方が楽できる気がします。

それほど複雑ではないので、日付を扱うときは
office のヘルプで 日付と時刻の関数 を調べてください。
また分析ツールのアドインを入れておく必要があると思いますので最初のインストール時に入れておいた方が良いと思います。

関数 説明
シリアル変換
(日付)
DATE 指定された日付に対応するシリアル値を返します。
DATEVALUE 日付を表す文字列をシリアル値に変換します。
TODAY 現在の日付に対応するシリアル値を返します。
YEAR シリアル値を年に変換します。
MONTH シリアル値を月に変換します。
DAY シリアル値を日付に変換します。
シリアル変換
(時間)
TIME 指定した時刻に対応するシリアル値を返します。
TIMEVALUE 時刻を表す文字列をシリアル値に変換します。
NOW 現在の日付と時刻に対応するシリアル値を返します。
HOUR シリアル値を時刻に変換します。
MINUTE シリアル値を時刻の分に変換します。
SECOND シリアル値を時刻の秒に変換します。
日付計算 NETWORKDAYS 開始日と終了日を指定して、その期間内の稼動日の日数を返します。
EOMONTH 開始日から起算して、指定した月数だけ前または後の月の最終日に対応するシリアル値を返します。
EDATE 開始日から起算して、指定した月数だけ前または後の日付に対応するシリアル値を返します。
DAYS360 1 年を 360 日 (30 日 × 12) として、支払いの計算などに使用される 2 つの日付の間の日数を返します。
WEEKDAY シリアル値を曜日に変換します。
WEEKNUM シリアル値をその年の何週目に当たるかを示す値に変換します。
WORKDAY 開始日から起算して、指定した稼動日数だけ前または後の日付に対応するシリアル値を返します。
YEARFRAC 開始日と終了日を指定して、その間の期間が 1 年間に対して占める割合を返します。



日付の差分を計算するときは
=DATEDIF(開始日,終了日,"単位")
の関数が便利です。

場所を表す関数

関数名 説明 備考
row,column セルの行、または列を返します。
rows,columns セル範囲の幅や高さを返します
areas 含まれている配列・要素の数を返します
index, offset,address セルの指定値+オフセット値で、実際のセルの値の場所を指定できます。
indirect 文字列で、実際のセルの値の場所を指定できます。


場所を示す関数を視覚的に整理してみました。


セル指定の使い分けのコツ

index,offset,indirectは 参照を返します。
相対的に場所を指定したい場合は index, offsetを使用し
文字列で参照場所の指定は indirect と address関数を使用します。

・セルの値を柔軟(間接的)に取得することが index,offset,address関数でできます。それぞれ、基軸のセルの指定方法が違うだけで、実質は同じです。 使用用途に合わせて使えば良いと思います。
・また文字列による ”$E$2” や"AX3"などの指定をしたい場合は、indirect関数が便利です。英字を使うときはchar関数を組み合わせると数字から数値に変換できるため柔軟性が高くなります。
char(1+ 64) → "A"
char(7+ 64) → "G"

ちょっとかっこ良く柔軟にすっきりと参照先シートを文字で指定するちょいわざ

情報を表す関数


cell関数や info関数でちょっとした情報を入手できます。

関数 入力値 出力例 関数入力例
シート名
cell関数
filename D:\***\***\[Excel.xlsm]Sheet3 =cell("filename",A1)

[]の範囲で抜き出せばシート名が抽出できます。
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)
ディレクトリ名
info関数
directory c:\directory\file.xls =info("directory")
numfile 45
osversion Windows (32-bit) NT 6.00
release 12.0
system pcdos

類似リンク

ちょっとかっこ良く柔軟にすっきりと参照先シートを文字で指定するちょいわざ

選択範囲シート名にハイパーリンクを追加するマクロ

整形データ、テストデータを作成する!(レッツトライExcel)

入力規則の対象リストを他のシートでまとめて、綺麗に表示させるちょいわざ

リストの過不足をチェック!(レッツトライExcel)

今まさに合計したいときベストな関数とやり方はこれ!活用図例付き(Excel関数使いこなし編)

セルの値や何番目のセルかを表の中から検索したい時ベストな関数の使い分けはこれ!(Excel関数使いこなし編)

小技(設定技、印刷技・その他)(Excel操作編)


ページトップへ↑

一番上の入り口へ > excel > ちょっと上ゆく関数を極める!