カテゴリー別アーカイブ: エクセル関数

最大公約数と最小公倍数 LCM関数 GCD関数

最大公約数も最大公倍数も関数があるので簡単に算出できます。

LCM関数

最大公約数を求める

LCM(数値 1,数値 2,…)

 

GCD関数

最小公倍数を求める。

GCD(数値 1,数値 2,…)

両関数とも共通で引数は1個から255個まで指定できます。数値を入れても、セル参照でもかまいません。また、セル範囲(参照演算子のコロンを使ったセル範囲)でもかまいません。

少数は切り捨てられます。

引数には負の数値は指定できません。

GCD

SUMPRODUCT関数2

SUMPRODUCT関数の基本的な使用法はSUMPRODUCT関数を参照してください。

下のような商品名、価格、数量がある表で価格×数量の合計を作業列を作成せず、SUMPRODUCT関数で求めることが出来ます。

sumproduct

 

セルC20に =SUMPRODUCT(B2:B19,C2:C19) と入力します。

sumproduct_2

というのをご説明しました。

今回はすべての価格×数量の合計ではなく特定の商品の価格×数量の合計をSUMPRODUCT関数で計算します。この表では同じ商品が3回ずつ登場しています。バナナだけの価格×数量の合計を計算してみます。

セルC20に =SUMPRODUCT((A2:A19=”バナナ”)*1,B2:B19,C2:C19) と入力します。

sumproduct_5

説明しているサイトによっては、 =SUMPRODUCT((A2:A19=”バナナ”)*B2:B19,C2:C19) や =SUMPRODUCT((A2:A19=”バナナ”)*B2:B19*C2:C19) のような式もあります。

SUMPRODUCT関数は引数の配列を対応する項目の積を計算し合計する関数でした。

=SUMPRODUCT((A2:A19=”バナナ”)*1,B2:B19,C2:C19)  は
(A2:A19=”バナナ”)*1 と
B2:B19 と
C2:C19 の3つ引数があります。セルA2、B2、C2の値の積を出して、次にセルA3、B3、C3の積を出してとなりますが、第1引数が(A2:A19=”バナナ”)*1と謎です。

計算式のセル参照という項目で説明しましたが、セルD2に =B2:B19*C2:C19 という式を入れた場合入力されたセルD3と同じ行のセルB3とC3の積が計算されます。

sumproduct_6

 

A列は数値ではありませんが、=A2:A19 という式をセルE2に入れると2行目のバナナが答えとして返ってきます。先ほどの計算式と同じ理屈ですね。

 

sumproduct_6-1

 

次に、 =(A2:A19=”バナナ”) という式をセルF2に入れてみます。カッコは無くても今回は大丈夫ですが、さらに先に行ったとき必要です。これも同じように2行目のセルA2の値は、”バナナ”という文字列と等しいですか?等しくないですか?。という式になります。何々と等しいですか、何々より大きいですか、などのIF関数の条件式に使うような数式はTRUEかFALSEが返ってきます。

sumproduct_7

そして、TUREとFALSEにも書きましたが、TRUEに四則計算をしてやると1という数値として扱うことができます。FALSEは0になります。=(A2:A19=”バナナ”)*1 はTRUE×1なので1が返ってきます。

sumproduct_8

 

=SUMPRODUCT((A2:A19=”バナナ”)*1,B2:B19,C2:C19)  は第一引数が1か0になります。A列がバナナの時は1、バナナでない時は0になりますので、1*12*3(セルA2、B2、C2の値の積)、0*250*5(セルA3、B3、C3の積)と順に積を計算し合計してくれるというわけです。

他のサイトで見たという、SUMPRODUCT関数の使い方も意味は同じです。*1とせずに次の配列を掛けている =SUMPRODUCT((A2:A19=”バナナ”)*B2:B19,C2:C19) は無駄もなくかっこいいですが、関数ボックスで見ると引数が2つなので、関数の意味をよく理解できていないうちは、なぜ範囲と範囲を掛けているのか理解が難しいかもしれません。

sumproduct_9

 

TUREかFALSEかの真偽の値に、価格(数値)を掛けることで真偽のTRUE、FALSEを1と0の数値として扱っています。

慣れるまでは×1で数値にした物を別に分けておくと何をしているのか思い出しやすいと思います。

sumproduct_10

SUMPRODUCT関数

SUMPRODUCT関数はとっても便利ですが、ちょっと使い方が難しいです。

SUMPRODUCT関数

引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。
SUMPRODUCT(配列 1, [配列 2], [配列 3], …)

以上Excel2013 SUMPRODUCT関数のヘルプより

下のような商品名、価格、数量がある表で価格×数量の合計を出してみます。

sumproduct

 

D列に行ごとの価格と数量を掛けた数値をだして、SUM関数で合計することで計算できます。

sumproduct_1

うまくいきました。今回必要なのはセルD20の合計だけで、各商品の価格×数量は必要ないといった場合、D列自体(セルD20を除く)を作業列と言ったりします。

今回の合計を作業列なしで計算できるのがSUMPRODUCT関数です。セルC20に =SUMPRODUCT(B2:B19,C2:C19) と入力します。

sumproduct_2

 

引用したヘルプにもあるように(B2:B19,C2:C19) の要素間の積を計算します。セルB2*C2、セルB3*C3、セルB4*C4、と順に第1引数と第2引数のそれぞれを掛け合わせ、その和を返すので最後にすべてを加算してくれます。これなら作業列なしで計算できます。

また、引数の配列は2つ以上(1つは必須)指定できますので、以下のように利益率の列を追加したものでも価格×数量×利益率も同じ要領で計算できます。D列に利益率を記入します。セルD20に =SUMPRODUCT(B2:B19,C2:C19,D2:D19) と入力します。セルB2*C2*D2、セルB3*C3*D3、セルB4*C4*D4、と順に積を計算し、最後に加算します。

sumproduct_3

 

それ以外にも、引数の配列は2列でも大丈夫です。表の形が変わっていますのでご注意ください。価格が2種類あってそれぞれに売れた数量があります。B列とD列を掛けたものとC列とE列を掛けたものの合計を出す場合は =SUMPRODUCT(B2:C19,D2:E19) と入力します。sumproduct_4

 

これも同じようにセルB2*D2、セルC2*E2、セルB3*D3、セルC3*E3、と順に積を求めて和を算出してくれます。これに価格と、価格2の利益率も必要ならF列に価格の利益率G列に価格2の利益率を記入し、第3引数として F2:G19 を入れてあげれば計算できますので試してみてください。

SUMPRODUCT関数には、他にも便利な使い方がありますので別途ご紹介する予定です。

条件に合致する項目の合計 SUMIF関数

合計を出す関数オートサムを参照してください。

特定の条件に一致する項目のみを合計する方法をご説明します。(図はExcel2013)

sumif

お小遣い帳、または家計簿的な項目があります。発生順にどんどん下に追記していくと思ってください。費目ごとに合計を算出したいと思います。2行目と7行目に食費があります。手作業で拾うのはエクセルのかいがないですね。費目ごとのシートか、列を作っておけばいいですが、入力が面倒です。

D列に費目の一覧を作成し、E列に費目ごとの合計を求めます。

sumif_1

SUMIF関数

指定した条件を満たす範囲内の値を合計する

SUMIF(範囲, 検索条件, [合計範囲])

範囲には条件を満たすかどうかを調べるセルを指定します。今回の場合はセルA2からセルA16が範囲になります。

検索条件は、数値、式、セル範囲、文字列、または関数で指定できます。今回はD列の文字列と一致するかどうかを指定します。

合計範囲は金額の入っているセルB2からセルB16になります。

セルE1には =SUMIF(A2:A16,D1,B2:B16) という式で食費だけの合計が求まりますが、この式をコピーするとセル範囲 A2:A16 と B2:B16 が相対参照なのでずれてしまいます。その前に、このままではA列に新たに項目を追加するたびにセル範囲を拡張しなけばなりません。範囲と合計範囲は列すべてにしてしまいます。

sumif_2

セル範囲の指定をA:AとすればA列すべてになります。

sumif_3

あとはセルE1の数式をコピーしておけば完成です。

sumif_4

検索条件にはワイルドカードが使用できます。半角の疑問符 (?) は任意の 1 文字を表し、半角のアスタリスク (*) は 1 文字以上の任意の文字列を表します。

セルE15に費目の最後の文字が「費」で終わるものの合計を出してみます。検索条件は文字列なのでダブルクォテーションで前後を挟み、”*費”とします。*は1文字以上の文字なら該当しますので、「食費」も「交通費」も「なんちゃら費」もすべて検索の条件を満たします。

セルE15に =SUMIF(A:A,”*費”,B:B) と入力します。

sumif_5

末尾が「費」で終わる費目が合計されました。

キーから値を抽出 VLOOKUP 2

VLOOKUP関数は便利で使用頻度も高いと思います。完全一致で値を抽出する、キーから値を抽出 VLOOKUPを参照してください。

今回は、近似値を返す場合をご説明します。

vlookup1

お買い上げ金額により値引き率を変更する場合を考えます。上の図は、1000円買っていただいたら、2%値引き、5000円買っていただいたら、5%の値引きするという意味だと思ってください。A列にある金額と、ちょうど同じ金額をお買い上げなら隣のB列の値を参照すればよいですね。では、例えば3000円買っていただいたら何パーセントの割引率を適用すればよいでしょうか。1000円から5000円未満までは、2%の割引率にします。同様に5000円以上10000円未満は5%が割引率になります。

この時、VLOOKUP関数を使って、うまくB列の割引率が求められるでしょうか。キーから値を抽出 VLOOKUPでは完全一致でしたので、3000円の場合、3000という数値がA列にないのでエラーになってしまいます。1円刻みで表を作っておけば完全一致も可能でしょうが、とっても大変なことになります。

このような場合もVLOOKUPが使えます。A列に金額を入れるとB列に値引き率が返ってくるような計算式をセルB2に入力します。

vlookup1_1

セルB2に =VLOOKUP(A2,$E$1:$F$8,2,TRUE) と入力します。ポイントは第4引数がTRUEとなっているところです。第4引数がFALSEの場合は完全一致になりますが、TRUEの場合は、完全一致で検索し、一致するデータがない場合は、検索値未満の最大値が使用されます。

vlookup1_2

まず、セルA4に3000という値があったら、E1からE8で3000という値を探します。ありませんので近似値を求めます。検索値未満の最大値ですから、3000未満で最大値は1000です。次にVLOOKUP関数の第3引数の2を参照し、第2引数の範囲の2列目の値を返しますので2%という数値が返ってきます。(表に1行目の見出しを含む必要はありません。)

vlookup1_3

第4引数のTRUEを使うときは、参照する表(今回は、$E$1:$F$8)の左端列が昇順に並んでいないと正確な結果が返ってこないことがあります。($E$1:$F$8の絶対参照はコピーするときのためで必須ではありません。)

TRUEの場合は、第4引数を1としても同じです。また、TRUEの場合は第4引数を省略できます。

ふりがなの表示PHONETIC関数

エクセルにもワードと同じようにふりがなの表示があります。ワードのふりがなはルビ(ふりがな)を参照してください。

下のエクセルは法務省のページからデータをコピーしたものに少し加工をしたものです。(図はexcel2013)

phonetic

B列の管轄区域の各セルにふりがなを表示させます。

ふりがなを表示したいセルを選択します(今回は列選択でB列すべて)。ホームタブのフォントからふりがなの表示/非表示の隣の三角をクリックし、ふりがなの表示をクリックします。

phonetic_1

行の高さが高くなりふりがなが表示されるはずですが、今回は表示されませんでした。

phonetic_2

これはWebから文字をコピーしてきた場合(たぶんすべての場合だと思いますが)ふりがなは表示されません。

自分で入力したものやワードからコピーしたものはふりがなが表示されます。仕方がないので、データを打ち直しました。(本当は、別の方法で打ち直すより少し早くできます)

A列の局名にふりがなを表示してみます。A列を選択しふりがなの表示/非表示のボタンからふりがなの表示をクリックします。

phonetic_3

うまく、ふりがなが表示されました。ふりがなが不要になって、消したい場合はふりがなを消したいセルを選択し、表示の時と同じボタンをクリックします。

現在はふりがながひらがなですが、カタカナにしたい場合があるかもしれません。この場合はふりがなの表示のボタンの2つ下のふりがなの設定をクリックします。

phonetic_5

ふりがな設定で全角カタカナか半角カタカナを選択します。また、フォントタブでは文字色や文字の大きさなどが設定できます。

phonetic_4

全角カタカナで文字色青にしました。

phonetic_6

また、入力した時と違う読み方(ふりがな)の場合はふりがなの編集で自由にふりがなを設定できます。

葵絆(きずな)さんや姫星(きてぃ)さんなどの難読ネームの方も安心です。ふりがなを編集したいセルを選択し、ふりがなの編集をクリックします。

phonetic_7

もともとのふりがなが表示され、カーソルが表示されますので任意のふりがなに打ち直します。phonetic_8

これで難読ネームの読みのふりがなが設定できました。

phonetic_9

先ほどのWebからコピーしたデータでふりがなが表示されない場合も、ふりがな編集で表示されますので打ち変えずに確定すれば入力しなおすより楽だと思います。文字を選択して再変換でも可能です。

これ以外にふりがなを別のセルに表示した場合は全く別の方法になります。

PHONETIC関数

文字列からふりがなを抽出します。

PHONETIC(範囲)

A列とB列の間に1列挿入し(列見出しCの上で右クリックして挿入)ふりがな用の列を作ります。セルB2に=PHONETIC(A2)と入力しEnterキーをたたきます。ふりがなが別のセルに表示されました。

phonetic_10

セルB2の式をオートフィルでコピーします。

phonetic_11

カタカナで表示されていますが、ひらがな変更するためにはもともとの読み仮名があったセルのふりがなをひらがなに変更することで、カタカナからひらがなに変わります。ここはよく勘違いしてしまうところです。B列でごちょごちょしてもうまくいきません。

phonetic_12

A列のふりがなは不要だと思いますので非表示にしておきます。

phonetic_13

キーから値を抽出 VLOOKUP

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

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

vlookup

VLOOKUP関数

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

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

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

vlookup_1

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

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

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

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

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

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

vlookup_2

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

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

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

vlookup_3

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

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

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

vlookup_4

範囲に存在しない商品番号の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))

vlookup_5

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

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

IFERROR 関数

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

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

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

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

vlookup_6

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

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

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

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

vlookup_7

 

元利均等払いの内訳 元金と利息 PPMT関数

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

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

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

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

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

ppmt_1

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

PPMT関数

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

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

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

ppmt_2

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

ppmt_3

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

ppmt_4

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

ppmt_5

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

IPMT関数

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

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

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

ppmt_6

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

ppmt_7

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

ppmt_8

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

ppmt_9

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

ppmt_10

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

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

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

ローン計算 元利均等払い PMT関数

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

PMT関数

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

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

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

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

pmt

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

pmt_1

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

pmt_2

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

pmt_3

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

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

pmt_4

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

pmt_5

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

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

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

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

 

時間の計算 FLOOR関数

日付の計算は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が一桁下がっているので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.1の数値が表せません。0.25、0.125などそれ以下のものを組み合わせても10進数のちょうど0.1は作れません。

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

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

gosa

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

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

gosa_2

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

gosa_3

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

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

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

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時間なら)gosa_4

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

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

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

FLOOR関数

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

FLOOR(数値, 基準値)

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

gosa_5

しかし、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″)

で計算します。

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

これ以外にも

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

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

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

gosa_8

まとめ

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