壁A数式がうまくコピーできない ( No.3 ) |
- 日時: 2004/07/19 23:06
- 名前: 管理人
- 数式が入ったセルをコピーした時、計算ミスを起こしたり、エラー表示されるときがあります。
原因としては、式の設定で絶対参照していない為にこのようなミスが起こることが多い。 (絶対参照にたいするものとして相対参照がある)
<通常(相対参照)の例> =B4/C3
<絶対参照の例> =$B$4/$C$3 (相対参照の状態でF4キーを押すと絶対参照を表す$が付加し、Enterキーを押せば確定する)
また、絶対参照に無理があるなら複合参照すればうまくいけることが多い。複合参照とは、列番地か、行番地のいずれかだけを絶対参照にすることです。
<複合参照の例> =$B4/$C$3
|
壁B日付・時刻の計算がわからない。 ( No.4 ) |
- 日時: 2004/07/25 17:25
- 名前: 管理人
- エクセルで日付・時刻の計算をするととまどう事があります。
例えば「4/24」と入力して「4月24日」と表示されるのは、前記の壁Aで紹介した「表示形式」の仕業です。
ところが、日付・時刻の計算では、それ以外に注意しなければいけない根本の問題があります。
エクセルにおける日付・時刻の正体を理解しなければなりません。 実はエクセルでは日付や時刻は連続する数値(シリアル値)で管理されているのです。
例えば「2004/4/24」と入力すると自動的に日付データとして表示されるが、シリアル値では「38101」と言う数値になります。 これは、「表示形式」を「標準」にすると確認できます。
<日付のシリアル値> 1900/1/1 −> シリアル値は1 1900/1/2 −> シリアル値は2 2004/4/24 −> シリアル値は38101 2004/4/25 −> シリアル値は38102
<時刻のシリアル値> 0:00:00 −> シリアル値は0 12:00:00 −> シリアル値は0.5 24:00:00 −> シリアル値は1 (0:00:0)
<日付、時刻のシリアル値> 2004/4/24の12:00:00 −> シリアル値は38101.5
上記のように、日付・時刻の実際の数値はシリアル値で持っています。 この時、時刻の表示は24時間を越えたものは、翌日の時刻として、「0:00」にいったんもどって表示されます。 また、実際の数値はシリアル値なので「36:00」といった時刻であっても実際は「24時間+12時間」で「1+0.5」の「1.5」にしかなりません。 ですから、勤務時間×時間給のような計算を行いたいときは、勤務時間を計算に使える数値に変換する必要があります。 (通常、「36:00」は24時間を越えているので「12:00」と表示される。「36:00」と表示させるには、「表示形式」を「ユーザー定義」で「[h]:mm」のようにする。)
<給与の求め方> 1.9:00から17:00まで働いたとします。 2.この場合勤務時間を時分で求めると、17:00 − 9:00 で8:00になります。 3.時間給が1000円だったとき、給与を1000 × 8:00で求めようとしても正しい結果が得られません。 なぜなら、8:00というのは実際の数値はシリアル値で、0.333・・・だからです。 4.ですので、8:00を給与計算の時間として計算できる数値に変換しなければあなりません。(この場合、8:00は8という数値に変換できれば良い。) 5.変換方法として、次のいずれでもかまわない。 (24時間がシリアル値では1となるので下記のような計算式となる) a)時刻/”1:00” b)時刻/1/24 c)時刻*24
<日付の表示形式> 表示形式 表示例(シリアル値38113の場合) 1.yy/mm/dd → 04/05/06 2.m/d(ddd) → 5/6(Thu) 3.dddd,mmmm,d,yyyy → Thursday,May,6,2004 4.aaaa → 木曜日 5.ggge”年” → 平成16年 6.gge → 平16 7.ge/m/d → H16/5/6 8.e”年”m”月”d”日”(aaa) → 16年5月6日(木)
<”[]”を着けた時刻の表示例(”[]”を付けると表示が変わる)> 表示形式 表示例(シリアル値3.2389の場合) 1.[h]:mm:ss → 77:44:00 2.[h]”時間”mm”分”ss”秒” → 77時間44分00秒 3.[m]”分” → 4664分 4.[s]”秒” → 279840秒
 |
壁B日付・時刻の計算がわからない2 ( No.5 ) |
- 日時: 2004/07/20 01:39
- 名前: 管理人
- 日付を扱う関数には以下のようなものがあります。
1.曜日を求める。 =WEEKDAY(A7)
セルのA7に入った日付から曜日の番号を求めます。 1:日曜日 2:月曜日 3:火曜日 4:水曜日 5:木曜日 6:金曜日 | 7:土曜日
2.翌月の10日を求める。 =DATE(YEAR(C4),MONTH(C4)+1,10)
C4に入ったセルから翌月の10日を求めています。
YEARは日付から年を取り出す関数。 MONTHは日付から月を取り出す関数。 DAYは日付から日を取り出す関数。 DATEは年、月、日から日付を求める関数。
|
壁Cなぜだか計算違いが起こってしまう ( No.6 ) |
- 日時: 2004/07/22 03:42
- 名前: 管理人
- 計算が得意なはずのエクセルは、実は「計算間違い」を起こします。
これはコンピュータで計算を行う場合、二進数を使うことから起こる問題です。 整数では問題ないが、問題は少数です。
たとえば十進法の「0.1」も、二進法にすると「0.000110011001100〜」と延々と数値が続く形となり、「0.1」を正確にあらわすことができません。 あくまで、正確さを期すなら二進法でない計算法を採用すべきなのですが、二進法はコンピュータと相性が良く、高速で処理できる利点があります。エクセルの場合も正確さより、速度を優先したかたちとなりました。
もちろん、通常の処理ではこのような誤差はほとんど問題がありません。
エクセルでは、少数を二進法で変換する場合、ある桁数(最初の1がでてから54桁目)で切り捨てか、切り上げかを自動的に行っています。 そのため、「0.1」と表示されていても、実は0.1より少しだけ大きかったり、小さかったりします。通常の表示では、小数点以下の表示桁数がそれほど大きくないため、その違いが見えないまま計算され、知らない間に計算間違いを行っている可能性があるというわけです。
ですから、シリアル値自体が少数である「時刻」の計算の場合には特に注意が必要です。
<時間の計算時の対象法の例>
出社時間と退社時間から、勤務時間を求めるとします。 勤務時間は、退社時間から出社時間を引いた値です。 しかし、給与の計算上、勤務時間を15分単位で切り捨てることとします。
例) 出社時刻 20:00 退社時刻 22:00
勤務時間 2:00 = 22:00 − 20:00
FLOOR関数で、この勤務時間2:00がはいったD4のセルを15分ごとに切り捨てると(=FLOOR(D4,”0:15”))、1:45と言う値が求められました。 でも、実労は2:00のはずです。
このようなことが起こったのは、22:00 − 20:00の値が2:00と表示されているが、実際は2.00より少し小さい値のため1:45と表示されたと考えられます。
◆解決法1 給与計算は分単位に行うので、分に影響の与えない「0.1秒」を加算して、FLOOR関数を使う。「0.1秒」は誤差が生じる桁数より大きいので、この数値を加えることで、わずかにちいさかった数値が、表示時間(例では2:00)より大きくなるので問題を防げます。
例) 1.元の時刻に0.1秒を足す =D4+”0:00:01”
2.0.1秒足した時刻を15分単位で切り捨てる =FLOOR(G4,”0:15”)
◆解決法2 分の部分を整数化して計算する。 整数化する方法として、MINUTE関数で分を取り出す。
例) 1.時間データから「時」だけ取り出す =HOUR(D4)
2.「分」だけ取り出す =MINUTE(D4)
3.出した「分」をFLOOR関数で切り捨てる =FLOOR(F4,15)
4。時間データに戻す(シリアル値に戻す) =TIME(E4,G4、0)
 |
Re: EXCELの壁 ( No.7 ) |
- 日時: 2005/08/23 14:59
- 名前: 王 玉砕
- 01を数値として 入力したい
|