エクセル2010小数点計算＃３

質問

先日質問に対する答えをいただきました。
http://okwave.jp/qa/q7568065.html
セルA1「１．０９」B1「２．００」C1「１．０９」D1「２．０３」E1「２．０３」F1「２．００」の平均が
「２．１２」になってしまいます。「２．００」にする為にはどうしたら良いですか？
お願いします。

回答No.11

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

＃5です。
2010では計算方法が少し変わったようですね。
小数点誤差をもう少し考えてみました
G1セルに
=ROUNDDOWN(INT(SUMPRODUCT(INT(A1:F1)*12+MOD(INT(A1:F1*100),100))/COUNT(A1:F1)/12)
+MOD(SUMPRODUCT(INT(A1:F1)*12+MOD(INT(A1:F1*100),100))/COUNT(A1:F1),12)/100,2)

小数点誤差をなくすために100倍して整数化しましたが、これでは、完全ではないようです。(for XL2003)
よってINTを付加。
MOD(INT(A1:F1*100),100))

#ちなみに、AVERAGE関数の配列計算は、元の値(A1:F1)が一部入力されていない場合に異なる結果が出るので注意が必要です。

お礼

何度もありがとうございました。
おかげさまで解決しました。

回答No.10

MarcoRossiItaly

入力の方式としてお勧めではないとのご意見もあるようですが、算数の問題としてはちょっと楽しいですよね。

10進数と12進数が混ざっているので、そのまま全体を合計しようとしたりすると、訳分からんことになります。なので、整数部分と小数部分を別々に扱って、それぞれ月数に変換してから合計すれば間違いありませんね。

例えば「1.09」の場合、整数部分は「1」、小数部分は「0.09」です。整数部分の月数は「1×0.12×100＝12」、小数部分の月数は「0.09×100＝9」です。両者の合計は、「12＋9＝21」と出ますね。ですから、「1.09」が表す月数をExcelの数式で計算すると、こんな式になります。

=(a1-(1-0.12)*int(a1))*100

で、ここから6つのセルの平均に持っていくのですが、Ctrl+Shift+Enterを押して式を入力するなら、No.8さんの式。そうでないなら、次のようにSUMPRODUCT関数とCOLUMNS関数を使えばいいかと。

=int(sumproduct((a1:f1-(1-0.12)*int(a1:f1))*100)/columns(a1:f1)/12)+mod(sumproduct((a1:f1-(1-0.12)*int(a1:f1))*100)/columns(a1:f1),12)/100

この数式を入力したセルの書式を、小数点第2位まで表示するように設定してください。

お礼

回答ありがとうございました

回答No.9

moto_koukousei

既に多くの回答が出ていますので、これが参考になるか不明ですが、、、、

２ケ年11ケ月を2.11のように入力し、平均値の11.5ケ月を1ケ年＝1と表示するのであれば、一つのセルに数式をまとめるのではなくて、わかりやすく、簡単な方法をとられた方が良いのではないでしょうか。
私の使っているアプリの問題なのか、MOD関数と配列を組み合わせて、序数で割りきれるような場合、期待しない値が返ってきています。INT関数を組み込まないと戻り値が使えませんでした。
無理矢理次のような数式を作りました。一応動くようですが、年月日の値が数百ある場合や、位置を変更する場合、平均値の表示方法を変えたい場合など、後での変更や改良を考えると、お薦めできる数式とは言えません。

=INT((SUMPRODUCT((INT(A1;F1)*12))+INT(SUMPRODUCT(MOD(A1;F1,1)*100)))/COUNT(A1;F1)/12)
+IF(MOD((SUMPRODUCT((INT(A1;F1)*12))+INT(SUMPRODUCT(MOD(A1;F1,1)*100)))/COUNT(A1;F1),12)>=11.5,
1,ROUND(MOD((SUMPRODUCT((INT(A1;F1)*12))+INT(SUMPRODUCT(MOD(A1;F1,1)*100)))/COUNT(A1;F1),12))/100)

どういう理由なのかわかりませんが、INT(SUMPRODUCT(MOD(A1;F1,1)*100))の部分を、SUMPRODUCT(MOD(A1;F1,1)*100)とすると、私の場合、SUMPRODUCT(MOD(A1;F1,1)）が見えないところでおかしくなるようです。　（shift+enterかもしれませんが）
こんなことは、一つの式に組み込んでしまったら、何が悪いのかわからなくなります。
簡単な式を作り、計算過程も簡単にチェックできる方法が良いように思います。

お礼

回答ありがとうございました。

回答No.8

keithin

＞２０１０では「２．１２」となります。

なるほど、面白い現象ですね。

=INT(AVERAGE(INT(A1:F1*100)-INT(A1:F1)*88)/12)+MOD(AVERAGE(INT(A1:F1*100)-INT(A1:F1)*88),12)/100
と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力します。

お礼

回答ありがとうございます。
ROUNDDOWNをつけて２００７でも２０１０でも出来ました。

回答No.7

KEIS050162

再び＃２です。私が計算したシートの一部を添付します。

ちゃんと平均が2.00となっていますよ。（理屈的にも計算できない訳はないのですが）

１行目　A1～F1　には単に数値が入っている。（黄色のセル）
２行目　A2～F2　に、　=INT(A1)*12+(A1-INT(A1)*100　（緑のセル）
※もちろん計算式中のA1は、B1、C1…と列によって変える

G列　G1,G2には単にAVERAGE関数を使って、それぞれのAx～Fxの平均が計算されている。

３行目　A3～F3と、G3　に、 =INT(A2/12)+MOD(A2,12)/100　（水色のセル）
※これも、計算式中のA2は、B2、C2…と列によって変える。
G3で、G2の計算結果を単に変換戻ししているだけ。

これで、２行目のA2～F2には、月数２１ヶ月、２４ヶ月…　が計算され、G2には、平均の24ヶ月が計算される。

３行面のA3～F3は、単に変換を戻しただけなので、１行目のA1～F1の値と同じ数値になる。
で、結果のG3は、G2で計算した平均値の２４ヶ月を変換戻しした２．００という値になる。

これで出来なければ、なにか余計な計算処理が入っていると思えるので、確認してみてください。

補足

回答ありがとうございます。
エクセルのバージョンは何ですか？
２００７では「２．００」になるのを確認しましたが
２０１０では「２．１２」となります。

回答No.6

mt2008

年月をそんな風に入力するシステムは紛らわしいから辞めた方が良いと思いますが、とりあえず……

G1を作業セルとして使用します（A1:F1の合計月数）
=INT(INT(SUM(A1:F1))*12+MOD(SUM(A1:F1),1)*100)

H1に↓
=INT(G1/72)+MOD(G1/6,12)/100

これでどうでしょう

補足

回答ありがとうございます。
Ａ１からＦ１全てのセルに数値が入力されるわけではありません。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

基本的にそのようなシステムになっていることを改めたほうが良いと思う。
一部、割り算の順序を変更した(結果は同じ)。G1セルに
=ROUNDDOWN(INT(SUMPRODUCT(INT(A1:F1)*12+MOD(A1:F1*100,100))/COUNT(A1:F1)/12)
+MOD(SUMPRODUCT(INT(A1:F1)*12+MOD(A1:F1*100,100))/COUNT(A1:F1),12)/100,2)

ポイントは合計月数を出す数式
=SUMPRODUCT(INT(A1:F1)*12+MOD(A1:F1*100,100))

結果として2.12は計算上出ないはずだが、もう一度確認のこと
小数点誤差を多少は考えたが、原因は不明

添付図のようなことを一つのセルで行っている

お礼

回答ありがとうございます。
「２．１２」は「２．００」に出来ないのですね。

回答No.4

keithin

=INT(AVERAGE(A1:F1*100-INT(A1:F1)*88)/12)+MOD(AVERAGE(A1:F1*100-INT(A1:F1)*88),12)/100
と記入し，必ずコントロールキーとシフトキーを押しながらEnterで入力します。

お礼

回答ありがとうございました。

回答No.3

KEIS050162

＃２です。

最後の再変換が間違ってました。

F3　に　= INT(F2/12) + MOD(F2,12) / 100　　　 F2で計算した平均ｘｘヶ月を　x.xx歳に再変換する。

お礼

回答ありがとうございます。
やはり、セルA1「１．０９」B1「２．００」C1「１．０９」D1「２．０３」E1「２．０３」F1「２．００」の平均が
「２．１２」になってしまいます。「

回答No.2

KEIS050162

ちょっと前回の回答とやり方が違いますが、

１．０９　⇒　２１ヶ月　という変換と、
２１ヶ月　⇒　１．０９　才　という変換を組み合わせた方がすっきりするかも知れませんね。

A1: 1.09 B1: 2.00 C1:1.09 D1:2.03 E1:2.03

A2からE2に、　=INT(A1)+(A1-INT(A1))*100 ｘｘヶ月に変換する。A2～E2にｘｘヶ月の変換値を一旦計算しておく。

F2 に、　=AVERAGE(A2:E2) 平均　ｘｘヶ月という数値が出来る。

F3　に　= INT(F2/12) + MOD(F2,12) * 100 F2で計算した平均ｘｘヶ月を　x.xx歳に再変換する。

泥臭い方法ですが、確実で分かりやすいかと思います。

補足

回答ありがとうございます。
＞A2からE2に、　=INT(A1)+(A1-INT(A1))*100 ｘｘヶ月に変換する。A2～E2にｘｘヶ月の変換値を一旦計算しておく。
=INT(A1)*12+(A1-INT(A1))*100に直して出来ましたが、
セルA1「１．０９」B1「２．００」C1「１．０９」D1「２．０３」E1「２．０３」F1「２．００」の平均が
「２．１２」になってしまいます。

回答No.1

asuncion

＞セルA1「１．０９」B1「２．００」C1「１．０９」D1「２．０３」E1「２．０３」F1「２．００」の平均が
「２．１２」になってしまいます。

そもそも、これは本当でしょうか。
電卓で計算したところ、
1.7066666....
になりました。

＞「２．００」にする為にはどうしたら良いですか？

小数第一位で四捨五入したいのでしょうか？それとも、
小数第一位で切り捨てたいのでしょうか？

補足

回答ありがとうございます。
小数点第２位まで使用して、年齢の計算（アベレージ）を出したいのです。
１ヶ月を０．０１として入力し、１歳を１．００と入力します。
例Ａ：５．０３（５歳３ヶ月）＋５．０４（５歳４ヶ月）＋５．０５（５歳５ヶ月）＝アベレージ（５．０４）
例Ｂ：５．０３（５歳３ヶ月）＋４．１１（４歳１１ヶ月）＝アベレージ（５．０１）
それぞれのアベレージを計算したいです。
今使用している関数は（セルA1：C1としましょう）
=ROUNDDOWN(INT(SUMPRODUCT(INT(A1:C1)*12+MOD(A1:C1*100,100))/12/COUNT(A1:C1))
+MOD(SUMPRODUCT(INT(A1:C1)*12+MOD(A1:C1*100,100))/COUNT(A1:C1),12)/100,2)
です。たぶん小数点第２位が、ちょうど「．１２」の時だと思います。