商品番号から商品名や価格を抽出するときによく利用されるのがVLOOKUP関数です。

商品一覧があり、商品番号を入力すると商品名が自動で表示されるようにします。価格も自動で表示します。（図はexcel2013）

VLOOKUP関数

セルの値を縦方向に検索し一致するセルの同一の行の値を返します。

VLOOKUP(検索値, 範囲, 列番号, [検索の型])

セルB2の数式は、セルA2の値とG列の商品番号の値を比較し一致したセルと同じ行にあるH列の商品名を返します。

検索値が商品番号、範囲が商品一覧、列番号が勝因一覧の何列目科の数値、検索の型は0（ゼロ）。

VLOOKUP関数の検索値は探したい商品を特定できるものです。

範囲の設定の仕方は少しルールがあります。検索値で指定した値は範囲の一番左の列から探されます。もし、商品名のバナナから120という値を得たいなら範囲はH列から始めなくてはいけません。範囲の左端に探したい値があるようにします。1行目の見出しは範囲に含める必要はありません。また、オートフィルでコピーすることが多いので絶対参照にすることが多いです。

列番号は範囲の左端列を1として何列目のセルの値を取得するかを指定します。

[検索の型]は、省略可能です。完全一致の場合は、0（ゼロ）またはfalseを指定します。trueを設定した場合は検索値未満の最大の値と一致します。（ただし範囲の検索列（左端列は）昇順で並べ替えておく必要があります。）

A列に商品番号を入れるとVLOOKUP関数でB列に自動商品名が入るようになりましたので、範囲を絶対参照にして、セルB10までオートフィルでコピーします。

セルB2ではうまく答えが出た数式ですがコピーするとエラーが表示されました。これは関数を実行したが該当する答えがないという表示です。今回はセルA3以降の値が入っていませんのでエラーが発生しました。セルA3に商品番号を入力すればエラーは表示されません。

しかし、請求書や見積書などで使う場合エラー表示は格好が悪いです。

いくつか解決方法があります。IF関数を使ってA列が空白かどうかの場合分けをします。空白はダブルクォテーションとダブルクォテーションを続けて入力します。

セルB3に『もし、セルA3の値が空白だったら、空白を表示、そうでない場合はVLOOKUP関数で商品名を表示』という数式を入力します。

=IF(A3=””,””,VLOOKUP(A3,$G$1:I8,2,0))

これで空白の場合でもエラー表示されなくなりました。この場合は存在しない商品番号を入力した場合同じエラーが表示されます。

範囲に存在しない商品番号の500を入力してみました。500番の商品はありませんのでエラー表示されます。商品が存在しないのでエラーが正しいのですが、好ましくない場合もあります。知らない人が数式の入っているエラー表示列を消してしまうかもしれません。

ISERROR(テストの対象)かISNA(テストの対象)をつかって(テストの対象)で#N/Aが発生しているかどうかを調べます。エラーが発生していればtrueを返します。

では、セルB3に『セルA2の値でVLOOKUP関数を実施してエラーが発生していれば空白を表示、エラーが発生していなければセルA2の値でVLOOKUP関数を実施する』という数式を入力します。下線部は同じことなので数式に同じものが出てきます。

=IF(ISERROR(VLOOKUP(A3,$G$1:I8,2,0)),””,VLOOKUP(A3,$G$1:I8,2,0))

これで空白の場合も存在しない商品番号でもエラー表示されなくなりました。

また、Excel2007からIFERROR 関数が使えます。

IFERROR 関数

数式がエラーの時は指定した値を表示し、エラーでないときは数式の結果を返す。

IFERROR(値, エラーの場合の値)

今回はVLOOKUP関数がエラーを返したら空白を返す式にします。

=IFERROR(VLOOKUP(A4,$G$1:I9,2,0),””)

IFERROR関数の方が数式も見やすく、VLOOKUP関数の修正時も1か所でいいので非常に便利です。

セルB5では、先ほどのセルの値が空白なら空白というIF関数と組み合わせて、『もし、セルA5の値が空白ならば空白を表示、空白でないならセルA5の値でVLOOKUP関数を実施を実施して、エラーだったら”商品番号が違います”と表示する』式を入力してみます。

=IF(A5=””,””,IFERROR(VLOOKUP(A5,$G$1:$I$7,3,0),”商品番号が違います。”))

セルA5に何も値が入力されていない場合はセルB5も何も表示されません。セルA5に範囲にない商品番号が入力されたら”商品番号が違います。”と表示され、正しい商品番号（範囲にある番号）が入力されたら、価格を返します。

PMT関数　ローン計算　元利均等払いでローンの支払額の計算が出来ます。

PPMT関数を使用すると元利均等払いの元金分の支払いがどれくらいかを計算することが可能です。また、IPMT関数で利息分の支払額がわかります。

PPMT関数もPMT関数同様、月払いの計算時には年利を1か月分に、支払回数を年から月に変換しますので以下の図のような表を作成します。百万円を年利5%で1年の分割払いで考えてみます。（図はexcel2013）

（後ほどB列C列D列と使用する予定ですので、数値はD列から入力しています。）

セルE3には年利を12で割った結果です。=D3/12という数式が入力済み。セルE4は総支払回数になるよう年数×12の=D4*12という数式を入力しました。また、セルD5にはE列の数値を使って毎月の返済額が計算済みです。=PMT(E3,E4,E2)*-1

では、セルB8に支払第1回の元金分はいくらかPPMT関数で計算します。

PPMT関数

一定の利率で定期的に支払するローンの1回の元金分を計算する

PPMT(利率, 期, 期間, 現在価値, [将来価値], [支払期日])

利率が、3行目の利率です。期は何回目の支払かです。期間は返済期間、現在価値が元金に相当します。元利均等払いは後になるほど支払額に含まれる元金の割合が増えてきます。よってPPMT関数は期の値が何回目の支払かを指定して、都度支払元金を求めます。数式タブの関数ライブラリの財務をクリックし、PPMTをクリックします。

PPM関数の関数の引数のダイアログボックスが表示されますので適切なセル番地を入力します。利率も期間もE列側の12か月換算の方を使います。期はセルA8です。また、後ほど支払回ごとの計算をしますので期以外は絶対参照にします。

PPMT関数も結果がマイナスで返ってきますので-1をかけて正の数にしておきます。

A列の回数を全期間分の12回に増やし、元金の計算式セルB8 もコピーします。

これで毎月の支払額の中の元金額が作成できました。次に利息分を計算します。セルD5の毎月支払額から元金を引いてもいいですが、利息分がわかる関数がありますので使用して計算してみます。

IPMT関数

一定の利率で定期的に支払するローンの1回の利息分を計算する

IPMT(利率, 期, 期間, 現在価値, [将来価値], [支払期日])

セルC8にIPMT関数をつかって利息を計算しますので先ほどと同じように数式タブの関数ライブラリの財務をクリックし、IPMTをクリックします。

IPMT関数の関数の引数のダイアログボックスにセル番地を入力します。参照するセルは先ほどのPPMT関数と全く同じになります。

また、負の値が返ってきますので-1をかけておきます。

同様にコピーしてD列に元金と利息を足した( =B8+C8) 返済額を出しておきます。

そのほか少し手を加えて積み上げ縦棒のグラフなんかも追加してみました。支払期間に合わせて表が伸び縮みする工夫なんかがあるといいかもしれませんね。

今回ローン残高は元金から支払済み元金を引いて計算していますがIMPT関数から算出することも可能です。

http://support.microsoft.com/kb/214091/ja

PMT関数の記事もそうですが、小数点以下の端数や借入日から初回返済日までの期間、月単位ではなく日割りで計算する、などいろいろな要素で実際の数値とは異なることがりますのであくまでシミレーションとしてご利用ください。

自動車や家を購入する際ローンを組むことがあるかもしれません。住宅ローンや自動車ローンなどの支払額がエクセルの関数で簡単に計算できますので実際に購入するにあたって、シミュレーションしておくことが可能です。

PMT関数

一定の利率で定期的に支払するローンの1回の返済金額を計算する

PMT(利率, 期間, 現在価値, [将来価値], [支払期日])

百万円を年利5%、1年で返済する場合の月額返済額を計算してみます。

セルB1に金額として1000000、セルB2に利率として5%（パーセント表示にしています。）、セルB3に期間1年として1、セルB4に毎月の返済額をPMT関数を使って算出します。（図はExcel2013）

PMT関数を呼び出します。数式タブの財務をクリックします。その中からPMTを選択しクリックします。

関数の引数のダイアログボックスのそれぞれにセルを当てはめていきます。利率、期間、現在価値がそれぞれ金利、期間、元金に相当します。OKボタンをクリックします。

百万円の12回払いなので金利を考えない場合、おおよそ、1回84000円くらいになります。PPM関数で出てきた数値は-1050000とマイナスで百万を超えています。

これは関数の使い方が間違っています。直感で使うとおかしなことになりました。

C列で修正します。期間は実際に支払う回数になります。セルC3に=B3*12 と入力し月の数にします。金利は年利を12で割って、1か月分に変更します。セルC2に =B2/12 と入力します。元金はそのまま横セルC1にコピーします（=B1）。支払額はマイナスで出てくるのが正しい関数の振舞なのでマイナス1を掛けます。

月額は85607円になりました。今回はセルB1からB3までの数値を変更したら色々と使えますのでC列で計算しなおしましたが、PMT関数の引数内で12で割ったり、12をかけてもかまいません。

念のため月額に12をかけてローンの総支払額を計算してみました。一括払いと比べてかなり安くなりました。コツコツと元金を返していくと支払利息も減りますね。

百万円の5パーセント分の金利5万円を貸し付け時に天引きして95万円を渡して、元金百万円を12等分で返済させるあこぎな金融屋さんがいるとか、いないとか。

自分でしっかり計算できるといいですね。

借入日からの初回返済日までの日割り計算や、1円未満の端数処理などで実際の返済額とは一致しないかもしれませんのでそのあたりは気を付けてお使いください。

日付の計算はDATEDIFを参照してください。概ね、時間は通常の四則計算やオートサムで計算可能です。以下おかしなことが起こる一例と対策を挙げてみます。

エクセルで時間を計算するときに気を付けていただきたいことがあります。コンピュータは内部で2進数が使われているのはよくご存じだと思います。0と1の組み合わせで数字も文字も処理しています。

2進数は1の次が10です。10は10進数で2です。1桁上がると数値が倍になります。10進数は1桁上がると10倍ですね。逆に1桁下がると2進数は2分の1になります。10進数は10分の1です。2進数の0.1は１が一桁下がっているので1の2分の1で10進数でいうところの0.5です。

2進数　　　　10進数
10　　　　　　　　2
1　　　　　　　　 1
0.1　　　　　　　0.5
0.01　　　　　　0.25
0.001　　　　　0.125

2進数では10進数の0.１の数値が表せません。0.25、0.125などそれ以下のものを組み合わせても10進数のちょうど0.１は作れません。

エクセルでは計算時に小数点以下は近似値が使われることがあります。小数点以下の計算を組み合わせると誤差が発生し正しい答えが出ないことがあります。

セルA1に1.2セルB2に1.1セルC1に=A1-B1と入力するします。1.2-1.1は0.1なので0.1と表示されます。（図はexcel2013）

続いてセルD1に0.1と入力しセルE1に=C1-D1と入力します。セルC1は0.1、セルD1も0.1なので答えは0になるはずですが、とっても小さい負の数と対数表現されています。とにかく0になっていません。

セルA1からセルE1までを小数点以下の表示桁数を増やすボタンで小数点以下をどんどん表示させます。

どんどん、0が増えてきますが、あるところから1.2-11の答えのセルC1が、0.1ではなく0.09999999という表示に変わります。エクセルはセルに表示されていない桁で四捨五入されてい表示されます。表示桁数を増やすとと四捨五入されない数値が表示されます。

1.2-1.1は0.01よりもほんの少しだけ少ない数だったということがわかります。なので0.1よりほんのわずかに少ない数から0.1を引くと0よりほんの少しだけ小さいマイナスの数値になります。

エクセルでの小数点以下の数値の計算はわずかな誤差により思わぬ端数が発生することがあります。

エクセルの日付は1900年の1月1日を１として1日ごとに1増えていきます。時間は1日を24で割ったものが1時間になります。

１時間　 0.0416666666666667000　（1÷24）
1分　　　0.0006944444444444440　（1÷24÷60）
1秒　　　0.0000115740740740741　（1÷24÷60÷60）

すべて割り切りず循環小数です。これにより時間の計算は場合によりおかしな結果になることがあります。とはいえ、小数点以下十数桁のことなので見た目不都合が起きることは少ないです。（またどうしたわけか都合のいい結果になるようです。）

エクセルで1秒の0.0000115740740740741に1日の86400秒を掛けると全く端数のない1になる（見える？）のは不思議です。ウィンドウズの電卓で計算すると1.00000000000000224になりますが、内部で切り捨てするか何かの処理がされているのかもしれません。

しかし、時間の計算結果の比較や単位時間での丸めで誤差が発生することがあります。

誤差とは関係なしに下のような時間計算をしてみます。労働時間を求めるのには退勤時間から出勤時間と休憩時間を引けば求まります。（0時から0時までの24時間なら）

次に基本時間を8時間として8時間を超過したものについては残業時間とする計算式を入れます。これはあくまでもエクセルで時間計算でうまくいかないことがある例のためのものです。日々の残業時間を日ごとに切り捨てることは違法です。

実労時間から基本時間の8時間を引いて端数を15分単位で切り捨てます。（労働基準法等関連法令をあたって違法でない範囲で使用してください。）

四捨五入や桁数指定での切り上げ切り捨ての関数では15分単位での切り捨てが出来ません。

FLOOR関数

基準値の倍数未満を切り捨てます。

FLOOR(数値, 基準値)

FLOOR（実労時間-基本時間、15分）で残業時間の15分単位の丸めが出来ます。3行目の残業時間は=FLOOR(D3-$E$1,”0:15″)という式で求まります。

しかし、4行目の計算結果がおかしくなっています。実労時間計算と基本時間の差の計算時に生じた誤差が15分単位の倍数の30分よりほんの少し小さくなってしまったからです。エクセルの画面上では時間:分で表示されているものも実際は小数点以下の値を持った数値です。表示上、端数が見えなくなっているだけです。

あくまで1例として次の方法があります。FLOOR関数で丸める際に見えている8:30や9:30などの文字列で計算してみます。TEXT関数を使用して8:30に見えている0.354166666666667 という数値ではなくて8:30という文字列で計算します。

=FLOOR(TEXT(D3,”hh:mm”)-TEXT($E$1,”hh:mm”),”0:15″)

で計算します。

で4行目の計算も合いました。

これ以外にも

• エクセルの設定で『表示桁数で計算する』にチェックする
• 誤差のありそうなところに微小な少数（0.0000001など）を加算、減算しておく
• 少数が出ないように千倍や1万倍して計算し後ほど千分の一や１万分の一に戻す

があります。ほかにもあると思いますし、先ほどの計算式も含めすべての場合に正しい結果が出るかは確認しきれていません。

累積時間を計算すると24時間以上の部分が表示されないことがある（40:00が16:00と表示されたりする）のでセルの書式設定で時間の部分を[h]とすると24時間以上も表示されます。

まとめ

• エクセルは小数点以下の数値の計算は微小な誤差が生じることがある。
• 通常の時間の計算は通常の四則計算やオートサムで問題ありません。
• 時間は内部で少数として計算されるの思わぬ結果になることがある。計算結果と手入力の時間の比較や丸めなど。
• 単位で切り捨てのFLOOR関数を時間計算で使用すると誤差が表面化することがある。（切り上げはCEILING関数がある）
• TEXT関数で文字列としてから処理するとうまくいくことがある。理屈上うまくいくと思うが、何かの原因でうまく計算できないかもしれません。使用時はよく確認してください。
• もし残業代の計算をする場合は計算結果に間違いがないかよく確認し関係法令に基づいて処理してください。未払い請求で莫大な負担がかかるようになるかもしれません

エクセルで月間のスケジュール表を作ってみます。もともと時給計算のために作ったものの日付部分だけご紹介します。

年と月を入力すれば自動で月末まで日付と曜日を表示してくれるタイプをを作成します。一度作成しておくと万年カレンダーならぬ万年スケジュールが可能です。（図はexcel2013）

セルA3とセルC4に年と月を入力すれば月末の該当日までとその曜日が表示されるようにします。2月なら28日まで表示し29、30、31は表示しません。大の月、小の月、うるう年に対応します。DATE関数とMONTH関数を使いますので関数の説明です。

DATE関数

日付のシリアル値を返します。

DATE(年,月,日)

=DATE(1900,1,1)は1を返しますが、通常セルの表示形式が自動で日付になり1900/1/1 と表示されます。=DATE(2014,5,19)だったらシリアル値は41778と表示されます。1900/1/1から41778日経っているということですね（厳密には1引く？）。2014の代わりにA3とセル参照すれば2014のことですしC4とすれば上の図では3月ですね。2014年3月1日はDATE関数ではDADATE(A4,C4,1)とする事で表せます。セルA4の値を変えれば今年でも来年でも使えます。また、セルC4の値で何月でも表すことが可能です。ここで次の式はいつを表しているかお考えください。

=DATE(2014,2,29)

2014年はうるう年ではありませんので2月29日はありません。2月末の1日次の日なので2014/3/1になります。月末の辻褄が合わなければエクセルの方でよろしく来月にしてくれます。

MONTH関数

月を1から12で返します。

MONTH(シリアル値)

=MONTH(“2014/5/1”)は五月ですから5が返ってきます。=MONTH(DATE(2014,5,1))の場合はMONTHの中がDATE関数で2014年5月1日ですからもちろん5が返ります。

では=MONTH(DATE(2014,2,29))とうるう年ではない年の2月の29日を指定すると何月とみなすのでしょうか。DATE関数は月末の辻褄が合わないと自動で調整してくれるのでDATE関数内は2014年の3月1日としてくれます。するとMONTH関数は3月とみなし3を返します。

では、実際についたちから作成していきます。セルA5にセルA3の年とセルC3の月の1日をDATE関数で表示します。=DATE($A$3,$C$3,1)と入力します。

自動で日付の形式で表示されます。

セルB5はセルA2の値をそのまま表示するように=A2と入力します。セルA5もセルB5も3月1日と表示されます。

A列は日だけB列は曜日を表示するようにセルの表示形式を設定します。セルA5からセルA35まではユーザー定義でdとします。

同様にセルB5からセルB35まではユーザー定義で省略された曜日のaaaとします。

セルA6は2日なので上のセルに1足せば1日増えます。=A5+1と入力します。セルB6も=A6として隣の値を参照します。この2つのセルをフィルハンドルで28日までコピーします。

29日30日31日はない月とある月があるので少し工夫します。

セルA33は通常の2月はありませんがうるう年はあります。

=DATE(A3,C3,29)にすればセルA3年セルC3月の29日を日付形式で表示します。セルC3が2月なら29日はありませんので3が1日になります。うるう年なら2月29日になります。この関数の答えに対してMONTH関数で月を取得します。

=MONTH(DATE(A3,C3,29))

セルC3が2ならセルA3年の2月の29日は今月か来月かを知りたいという式になります。3が返ったら2月の29日は来月の3月1日と同じことですよということです。この式の答えとセルC2が同じだったら29日は今月で、違う場合は来月となります。IF関数でどちらかに場合分けします。

=IF(MONTH(DATE(A3,C3,29))=C3,”今月”,”来月”)

同じだったら今月なのでセルの値はその日付を表示します。同じでなければ来月ですからなにも表示しません。上の式の今月の代わりにDATE(A3,C3,29)を、来月の代わりに””で何も表示しなければ月末処理完成です。

=IF(MONTH(DATE(A3,C3,29))=C3,DATE(A3,C3,29),””)

たった3行ですがコピーするときのためにセル参照は絶対参照を使います。そしてそれぞれ29を30と31に変更しておきます。

セルA33　=IF(MONTH(DATE($A$3,$C$3,29))=$C$3,DATE($A$3,$C$3,29),””)
セルA34　=IF(MONTH(DATE($A$3,$C$3,30))=$C$3,DATE($A$3,$C$3,30),””)
セルA35 　=IF(MONTH(DATE($A$3,$C$3,31))=$C$3,DATE($A$3,$C$3,31),””)

と数式を入れてください。これで出来上がりです。年月により月末がきちんと表示されます。

もう一つのパターンは条件付き書式に月の比較を使って来月なら白文字にして見えなくするという方法です。

28日までは同じ手順です。29,30,31も同じ手順で日付を入れてしまいます。

条件付き書式（何かの条件によって書式―見え方を変更する）を設定するセルを選択します。月末29,30,31とその曜日のセルA33からセルB35までを範囲選択します。ホームタブのスタイルの条件付き書式をクリックし新しいルールをクリックします。

新しい書式ルールの数式を選択して、書式設定するセルを決定をクリックします。

次の数式を満たす場合に値を書式設定のボックス以下の式を入力します。

=MONTH($A33)<>$C$3

今6つのセルが選択されていますが、アクティブセル（白抜きで表示されている）はA33です。このセルA33にこの数式が適用されます、残りの5つのセルにもこの式をオートフィルでコピーした数式が当てはまります。セルB33にオートフィルでコピーした場合、横向きにドラッグになるので行番号の数値は各々33と3で変わりません。$aと$cは絶対参照なのでこれも変わりません。したがってセルB33にあてはめられる数式は

=MONTH($A33)<>$C$3

全く同じです。そのまま29日の日付により条件が変わります。

セルA34なら下にドラッグなので数値だけが変わります。$A、$C、$3は絶対参照なので変わりません

=MONTH($A34)<>$C$3

セルA34の30日の日付により条件が変わります。あとは同じ要領で考えてみてください。

<>は等しくないという論理演算子です。

セルC3に入力した月と計算で求まった29,30,31の月が等しくなければ文字が見えないように文字の色を白にします。書式をクリックします。

フォントタブの色を白にします。OKボタンで戻っていきます。

どちらも少し面倒かもしれません。条件付き書式がわかっていれば後の方法が簡単かもしれません。条件付き書式で文字色白で見えなくするのよく使うかもしれませんが、後で見直したとき、数式バーに出てきませんので思い出すのが大変かも。また、見えていないだけで存在しますので、計算とかにセルが使用されたら思わぬことになるかもしれません。

私が何度か作ってみて今のところこの2つが比較的簡単な方法かと思います。もちろんほかの方法もたくさんあるでしょう。一度参考に作成してみてください。

エクセルで曜日を表示する方法です。セルの日付自体に曜日を表示させたり、セルの日付を参照して曜日を表示させたりできます。

セルに日付を入力します。5/15のように月/日と入力すると日付として扱われます。頭に西暦を付けて2014/5/15とすれば年が指定できます。省略時は今年（入力した時の）になります。 （図はexcel2013）

セルA1に5/15と入力しセルB1に =A1 とセル参照する式を入力します。そして、そのB1のセルに書式設定をし曜日を表示します。セルB2で右クリックし、セルの書式設定をクリックします。表示形式のタブをクリックしユーザー定義を選択します。種類のボックスに aaa と入力しOKボタンをクリックします。（セルの書式設定はCtrlキー + 1（いち）で素早く表示できます。）

aaaなどの曜日の表示形式は

• aaaは和暦の省略形で表示 (“水” など)
• aaaaは和暦の曜日を省略せずに表示 (“水曜日” など)
• dddは英語の省略形で表示(Sun ～ Sat)
• ddddは英語の完全な名前で表示 (Sunday ～ Saturday)

になります。

2014年05月15日(木)のように、年月日の後に曜日を表示したい場合は以下の表示形式を組み合わせます。

yyyyが年の西暦4ケタ表示
mmが月の2ケタ表示
ddが日の2ケタ表示
aaaが曜日の省略形

これらと年、月、日、（）の文字を組み合わせます。文字はダブルクォテーション（Shiftキー + 2）で挟みます。

yyyy”年”mm”月”dd”日””(“aaa”)”

とセルの書式設定に入力します。

OKボタンをクリックすると2014年05月15日(木)と表示されます。

セル自体に書式設定する以外にもTEXT関数を使って曜日を表示する方法もあります。

TEXT関数

数値を文字列に変換し、特別な書式文字列を使用して書式設定を指定できるようにします。

TEXT(値, 表示形式)

セルc1に =TEXT（A1,”aaa”)と入力します。

2015年05月15日(木)とする場合は先ほどのダブルクォテーションを外して
=TEXT(A1,”yyyy年mm月dd日(aaa)”)
と入力します。

また、CHOOSE関数とWEEKDAY関数を使う方法もあります。

WEEKDAY関数

日付に対応する曜日を返します。（数値で返ってきます。）

WEEKDAY(シリアル値,[週の基準])

第2引数数は省略可能で省略時は日曜から土曜が1から7の数に対応します。A1のセルが2014年5月15日だと木曜日なので =WEEKDAY(A1) だと5という値が返ってきます。

CHOOSE関数

インデックスを使用して、値引数のリストから値を返します。

CHOOSE(インデックス, 値 1, [値 2], …)

第1引数のインデックスの値が1なら第2引数の値が返されます。インデックスの数値に合わせて関数内の引数のどれかが選択されます。

=CHOOSE(WEEKDAY(A1),”日”,”月”,”火”,”水”,”木”,”金”,”土”)

と入力することでセルA1の日付に応じた曜日が返ってきます。

セルに入力するときに、リスト（選択肢）の中からクリックで入力する方法です。

入力するセルをクリックすると入力候補が表示されクリックで選択肢が入力されます。（図はexcel2013）

リストからの入力を使うと文字入力の手間が省けますし、入力候補以外は入力できませんので間違いが少なるなると思います。

リストからの入力を行いたいセルを選択します。複数のセルでも構いません。

データタブのデータツールのデータの入力規則をクリックします。データの入力規則のダイアログボックスが開きますので設定タブをクリックします。

条件の設定の入力値の種類からリストを選択します。

新たに元の値という項目が現れますのでクリックでカーソルをたてます。入力候補を直接カンマ区切りで入力します。

または、入力候補がすでに入力されていればセル範囲をドラッグします。

既に入力されているセルの範囲からリストを作成する場合ですが、別のシートからシートを越えて参照することが可能です。

Excel2013、Excel2010は元の値のボックスにカーソルがある状態で別のシートのシート見出しをクリックするとシートを越えられますのでそのまま別のシートのセル範囲をドラッグして選択します。

sheet2からsheet１のセル範囲をリストにすると、元の値に =sheet1!$a$2:$a$4 と表示されました。sheet1! はシートを越えて参照するときにどのシートを参照しているかを表しています。また、$ は絶対参照ですね。

Excel2013、Excel2010はこのように簡単なのですが、Excel2007はシート見出しをクリックしてもシートが変わってくれません。この場合は =sheet1!$a$2:$a$4 のようにどのシートのどのセルからどこまでのセルとキーボードで手入力してください。

Excel2003は手入力しても受け付けてくれませんでした。

このような場合はセルに名前を付けて =名前 のように手入力する方法もあります。セルA2からセルA4にコースと名前を付けました。

次に名前を使った方法でリストで選択された結果に基づいて次のリストを選択できるようにしてみます。セルA7でマイクロソフトを選択すればセルB7ではWord、Excel、PowerPointのどれかが選択できて、ホームページ作成を選択したらセルB3からセルD3までの値が選択できるというようなものです。

これを実現するためにはセル範囲に名前を付ける必要があります。今回はセルB2からセルD2までをセルA2のマイクロソフトオフィスという名前にします。この時セルA7で選択される名前、すなわちセルA2からセルA4までの値と同じ名前にする必要があります。

セルB2からセルD2を範囲選択し名前ボックスをクリックします。

そのままマイクロソフトオフィスと入力しエンターキーをたたきます。

以降、セルB3からセルD3を範囲選択し名前ボックスにホームページ作成と入力しエンター。セルB4からセルD4を範囲選択し名前ボックスにプログラムと入力しエンターします。

名前の修正、削除、新規作成は数式タブの定義された名前の名前の管理で可能です。

セルB7でリストから入力したいのですが元の範囲のところは何を入れればよいでしょうか

=マイクロソフトオフィス （名前）と入力すればセルB2からセルD2までが入力候補になりますが、セルA7でホームページ作成が選択されてもセルB2からセルD2までのWord、Excel、PowerPointが表示されてしまいます。

この元の値が常にセルA7の値が入れば解決です。元の値に =A7 としたいのですがこれだと文字通りセルA7の値しかリストに表示されません。

INDIRECT関数を使います。

INDIRECT(参照文字列, [参照形式])

INDIRECT関数は文字列の参照を返します。少しわかりにくいですが、参照を返すというのがポイントです。

セルB2には =A1 という式が入っています。当然セルA1の値のb1という文字列が返ってきます。セルB3には =indirect(a1) という式が入っています。セルA1のb1という文字ではなくセルA1の参照ということでB1の値が返ってきます。

=indirect(a7)　と手入力してください。（関数名の大文字小文字は関係ありません）

途中、絶対参照の $ がついていたり、ついていなかったりしますが、オートフィルでコピーしない場合は $ があってもなくても同じです。セルをクリックすると自動でつきます。ただし、複数のセルで同じように使うため、オートフィルでコピーした場合、絶対参照の $ がついていると思ったようにならないことがあるので気をつけてください。

2つの日付の間の日数や年数、月数を計算します。

DATEDIF(開始日,終了日,単位)

DATEDIF関数は関数の挿入ダイアログにありません。また、この関数に関するヘルプもありません。入力の際は手入力となります。（図はexcel2013）

開始日、終了日は日付に対するセル参照かDATE関数などの日付が返ってくるものとします。=DATEDIF(A2,DATE(2014,5,14),”d”)のようにしてください。
=DATEDIF(“2014/1/1″,”2014/5/14”,”d”)
など日付を文字列で入力すると、問題が生じることがあるそうです。

開始日は1900年1月1日以降です。

単位は

単位    戻り値
“Y”    期間内の満年数
“M”    期間内の満月数
“D”    期間内の満日数
“MD”    開始日から終了日までの日数。この場合、月と年は考慮されません。
“YM”    開始日から終了日までの月数。この場合、日と年は考慮されません。
“YD”    開始日から終了日までの日数。この場合、年は考慮されません。

http://office.microsoft.com/ja-jp/mac-excel-help/HA102927601.aspxから引用

が利用できます。単位の”Y”、”M”、”D”はそれぞれ年数と月数と日数です。

”MD”は何ヶ月と何日の何ヶ月を無視して日数のみ算出します。
”YM”は何年何ヶ月の何年を無視して何ヶ月かのみを算出します。
”YD”は何年と何日の何年を無視して日数のみを算出します。

2月の末日から翌年の2月の末日までで確認してみます。

2月28日からということは3月1日で1日、3月2日で2日と計算していきます。

単位がｙの場合は1年です。mは12か月、dは365日です。md、ym、ydはちょうど1年0か月と0日になりますのですべて0です。

ところが、うるう年の月末の場合少し結果がおかしいです。開始日の2012年はうるう年です。2012年2月29日は月末です。翌年の2月末は2月28日です。先ほどと同じように3月1日で1日、3月2日で2日とカウントすると日数はどちらも365日ですが、0年と11か月と30日になってしまいます。（もしくは0年と365日です。）

うるう日を挟むだけでしたら問題ありません。（うるう年なのでdは366日です）

開始日か終了日のどちらかが2月末日で期間中にうるう年が絡むと計算結果がおかしくなることがあるようです。

sum関数

SUM(数値 1,[数値 2],…)

数値１、は必須項目です。

エクセルでよく使われる関数としてオートサムがあります。合計をする関数です。

下の図は、何の表かはわかりませんがG列に百田さんの合計を出そうとしています。（エクセルの図はexcel2013）

まさか、エクセル以外で計算し（下の図は電卓ソフト）答えを数値として入力するという手はないと思います。

次の方法としてはG列に数式を入れるのですがすべての値を手入力という方法です（=98+59+84+59+76 ）。これは後で数値が変わったら、計算式も入れ直ししないと合計が合いません。この方法もないです。

また、ほかの方法として数値の代わりにセル番地を入力する方法があります。=B2+C2+D2+E+F2 とします。これは後で数値が変わっても参照しているセルは同じですから計算式を入力しなおす必要はありません。ただ、沢山のセルを参照するときに途中で参照場所を間違ってしまう可能性があります。

ここでオートサムを活用しましょう。答えを出したいセルG2を選択しホームタブの編集のオートサムのボタン（Σのアイコン）をクリックします。=SUM(B2:F2)と表示され計算対象のセルが囲まれます。

Enterキーをたたいて確定してください。B2:F2というのはB2からF2までの範囲を表します。

同様に高城さんの合計もセルG3を選択しオートサムで計算します。佐々木さんも同様に計算したいのですが、セルG3でオードサムをクリックしますと様子が少し違います。計算範囲がセルG2からセルG3となって、縦の合計を計算しようとします。

エクセルが自動で選択した計算範囲と自分が計算してほしい計算範囲が異なることはよくあります。このような場合は自分で正しい計算範囲を指定してあげる必要があります。今回はセルB4からセルF4までなのでその範囲をドラッグしてからEnterキーをたたいてください。

今回は3名分だったので3回オートサムをしましたが10名100名分となると大変です。しかも3名以上のところはすべて選択範囲を変更しなくてはなりません。ですから、セルG2で正しい計算式が1つ入ったらこの計算式を必要なところまでコピーします。

オートサムが完成したら再度セルG2をクリックします。セルG2の右下の角にマウスポインターを重ねるとマウスポインターが黒の十字の形になります。マウスポインターが黒十字の形になってからドラッグしてください。

答えを出したいセルまでドラッグしたら左ボタンを放します。

数式がコピーされて正しい計算結果が表示されました。

先ほどの表の右側にまた数値が入っています。G列でBCDEFと5列分の合計を計算し、M列にHIJKLの5列分の合計を計算します。G2は既に計算式が入っていますので、M2のセルでオートサムをクリックしてみます。先ほど合計を出したG2のセルの1つ右隣H列からL列まで自動で範囲選択しました。今回はこの計算結果が欲しかったのでEnterキーをたたいておきます。

次にN列に総計（合計の合計）を出そうと思います。セルN2でオートサムを実行します。そうするとセルG2とセルM2の2つのセルを自動で選択します。=SUM(M2,G2)という式があいります。今回はM2,G2とセル番地とセル番地の間が，（カンマ）で区切られています。これはM2とG2のそれぞれを合計するということです。

オートサムは計算したいセルに隣接している場合は自動で範囲選択しますが（正しいセルの範囲選択化は別として）全く関係のないセルに計算式を置いて範囲を自分で指定することも可能です。別のシードでも別のファイルで可能です。セル参照、シートを越えてセル参照、ブックを越えてセル参照

百田の総合計と佐々木の総合計の合計をセルA7に出します。百田の総合計はセルN2、佐々木の総合はセルN4なので加算してあげればいいのですが今回オートサムので複数のセルを手動で設定する方法の練習のためオートサムを使います。

答えを出したいセルA7をクリックして選択します。

オートサムのボタンをクリックします。すると隣接するセルに数値がないので=SUM()とどこのセルも自動で範囲選択してくれません。

この状態でまず百田の総合計のセルN2をクリックします。

次に佐々木の総合計セルN4をクリックすする前にカンマをキーボードから入力するか、もしくはctrlキーを押さえたままセルN4をクリックします。すると=SUM(N2,N4)となりますのでEnterキーをたたいておいてください。

今回は合計や総合計が計算されていますので意味はありませんが、複数の範囲をオートサムの引数（カッコの中のに指定する値）とすることが出来ます。

下は、オートサムのボタンをクリックののちセルB2からセルF2までドラッグしたのちctrlキーを押したままセルH2からセルL2までドラッグ、セルB4からセルF4までをドラッグ、セルH4からセルL4までをドラッグしたものです。

消費税率が5%から8%に変更ということでエクセルで消費税を再計算するかもしれません。

100円の5%は5円で8%は8円です。

120円の5%は6円で8%は9.6円です。

この時9.6円の1円以下の0.6円は通常切り捨てされて9円として計算されますね。エクセルで切り捨ての計算にはrounddown関数を使用します。

ROUNDDOWN関数

ROUNDDOWN(数値, 桁数)

数値には切り捨てをしたい数値、桁数は0（ゼロ）にすると小数部分がゼロ桁で整数部分だけになります。1（イチ）にすると小数点以下2桁目で切り捨て小数点以下1桁目までを表示します。120円の8%を小数点以下で切り捨てする場合は

=ROUNDDOWN(120*0.08,0)

という式になります。120*0.08がまだ切り捨てされていない部分で第2引数の0（ゼロ）で小数点以下キリストとなります。

上図は、2行目が商品の値段でG1のセルが消費税率。その2つを掛けたものをROUNDDOWN関数で切り捨てします。その時A列の値をROUNDDOWN関数の第2引数に指定したものがセルB3からE9です。第二引数に0(ゼロ)を指定した6行目は小数点以下がありません。その1行上の1をしてしてある5行目は小数点以下1行目が表示されています。（もともと小数点以下のないところは表示されない）また、第2引数に負の数値を指定すると-1で一の位-2で十の位-3で百の位が切り捨てられます。

ROUNDDOWN関数と同じように使える関数としてTRUNC関数があります。

TRUNC(数値, [桁数])

TRUNCも数値を切り捨てますが第2引数を省略することが可能です。省略した場合は0（ゼロ）が設定されたのと同じく小数点以下すべてが切り捨てられます。消費税のように小数点以下切り捨てと決まっている場合はTRUNC関数のほうが簡単かもしれません。

切り上げはROUNDDOWN関数の代わりにROUNDUP関数を使います。使い方は全く同じです。

四捨五入はROUND関数がROUNDDOWN関数と全く同じ感じで使用できます。

切り上げ　ROUNDUP関数
切り捨て    ROUNDDOWN関数またはTRUNC関数（第2引数省略可）
四捨五入  ROUND関数

となります。ちなみに『指定された数値を最も近い整数に切り捨てます。』というINT関数というものがあります。

INT(数値)

INT(数値)は第2引数が指定できないので必ず整数になります。しかし、数値が姓の値の間はROUNDDOWN関数またはTRUNC関数で小数点を切り捨てたときと同じ結果になりますが数値が負の値だと結果が違います。

TRUNC関数（第2引数省略）は0.8（1未満）から-0.9（マイナス1より大きい）の間はすべて0（ゼロ）になりますが、INT関数は規則的（上図では）に数値が変化していきます。

消費税の計算などでINT関数を使用すると返品などの場合に誤差が生じる可能性があります。