 壁①思ったとおりの入力ができない ( No.1 ) |
- 日時： 2004/07/24 01:11
- 名前： 管理人
- エクセルのセルには単に、数値だけでなく、金額や日付などのさまざまな種類のデータを入力します。
通貨記号をつけたり、日付をスラッシュで区切ったりしてなんとなく入力できているかもしれません。
しかし、思ったように表示されないときがあります。
それは、エクセルのセルに表示されているのは、たんに記録されたデータでなく、データをどんなふうに表示するかと言う「表示形式」を組み合わせた結果だからです。
エクセルはまず、キーボードから入力されたデータを解析し、「数値」か「日付」か「文字列」かなどの判断をし、さらに判断にあわせて形式を設定し表示すると言う複雑なことを行っています。
しかし、ユーザーが入力するあらゆるパターンを正しく判断してくれるわけではありません。
そのためいろいろのことがおこるわけです。
例えば下記のようなことがおこります。
＜症状＞
例１．セルに商品コードとして、「０５－０１」と入力したのに、「五月１日」となる。
例２．セルに番号として、「（１）」と入力したのに、「－１」となる。
例３．セルに、「１０９．００」と入力したのに、「１０９」となる。
＜原因＞
例１の場合は、エクセルが入力された「０５－０１」を勝手に日付と判断し、「２００４／０５／０１」と言うデータとして記録されたためです。
＜対策＞
このように自分が思い描く結果と違う場合は、セルの「表示形式」を設定することで解決できます。
例１の場合は、セルの「表示形式」を文字列にし、「０５－０１」と入力すれば、入力された通り表示される。
例２の場合は、セルの「表示形式」を文字列にし、「（１）」と入力すれば、入力された通り表示される。
例３の場合は、セルの「表示形式」を数値にし、少数点以下の桁数を「２」とすれば「１０９．００」と入力すれば、入力された通り表示される。
|
| 壁①思ったとおりの入力ができない２ ( No.2 ) |
- 日時： 2004/07/24 01:11
- 名前： 管理人
- また、表に数値を入力したはずなのに、計算にも、グラフにも使えないことがあります。
これは、簡単な理由です。セルに数値を入力をしたつもりが文字列として入力してしまっていたためです。
例えば、数値として単価をセルに入力したとき、「１２００」、または「￥１２００」と言うふうに入力すれば問題はないのですが、「１２００円」と言うふうに入力してしまうと、そのセルは文字列としてデータを登録してしまい、そのセルは計算には使えなくなるのです。
＜対策＞
数値として、そのセルを利用するが、「１２００円」とかいう風に表示させたいなら、表示形式を変えることで対応します。
この場合では、表示形式のユーザー定義に新しく「Ｇ／標準円」を作り「１２００円」と表示させる。
＜ユーザー定義で使われる記号の意味＞
記号
＃ 意味－＞数字１桁。余分な０は表示しない。
表示形式の例ー＞＃＃＃０
セルに入っているデーター＞１２３４５
表示結果の例ー＞１２３４５
０ 意味－＞数字１桁。余分な０も含めて必ず数字を表示。
表示形式の例ー＞００００
セルに入っているデーター＞１２３
表示結果の例ー＞０１２３
， 意味－＞３桁ごとにカンマで区切る。
表示形式の例ー＞＃，＃＃０
セルに入っているデーター＞１２３４５
表示結果の例ー＞１２，３４５
， 意味－＞書式設定の末尾に「，」があると千の位以上を表示、百の位で四捨五入。
表示形式の例ー＞＃，
セルに入っているデーター＞１００００
表示結果の例ー＞１０
，， 意味－＞書式設定の末尾に「，，」があると百万の位以上を表示、十万の位で四捨五入。
表示形式の例ー＞＃，，
セルに入っているデーター＞２００００００
表示結果の例ー＞２
”文字” 意味－＞ダブルクォーテーション内の文字そのものを表示。
表示形式の例ー＞＃，”千円”
セルに入っているデーター＞１００００
表示結果の例ー＞１０千円
|
| 壁②数式がうまくコピーできない ( No.3 ) |
- 日時： 2004/07/19 23:06
- 名前： 管理人
- 数式が入ったセルをコピーした時、計算ミスを起こしたり、エラー表示されるときがあります。
原因としては、式の設定で絶対参照していない為にこのようなミスが起こることが多い。
（絶対参照にたいするものとして相対参照がある）
＜通常（相対参照）の例＞
＝Ｂ４／Ｃ３
＜絶対参照の例＞
＝＄Ｂ＄４／＄Ｃ＄３
（相対参照の状態でＦ４キーを押すと絶対参照を表す＄が付加し、Ｅｎｔｅｒキーを押せば確定する）
また、絶対参照に無理があるなら複合参照すればうまくいけることが多い。複合参照とは、列番地か、行番地のいずれかだけを絶対参照にすることです。
＜複合参照の例＞
＝＄Ｂ４／＄Ｃ＄３
|
| 壁③日付・時刻の計算がわからない。 ( No.4 ) |
- 日時： 2004/07/25 17:25
- 名前： 管理人
- エクセルで日付・時刻の計算をするととまどう事があります。
例えば「４／２４」と入力して「４月２４日」と表示されるのは、前記の壁②で紹介した「表示形式」の仕業です。
ところが、日付・時刻の計算では、それ以外に注意しなければいけない根本の問題があります。
エクセルにおける日付・時刻の正体を理解しなければなりません。
実はエクセルでは日付や時刻は連続する数値（シリアル値）で管理されているのです。
例えば「２００４／４／２４」と入力すると自動的に日付データとして表示されるが、シリアル値では「３８１０１」と言う数値になります。
これは、「表示形式」を「標準」にすると確認できます。
＜日付のシリアル値＞
１９００／１／１ －＞ シリアル値は１
１９００／１／２ －＞ シリアル値は２
２００４／４／２４ －＞ シリアル値は３８１０１
２００４／４／２５ －＞ シリアル値は３８１０２
＜時刻のシリアル値＞
０：００：００ －＞ シリアル値は０
１２：００：００ －＞ シリアル値は０．５
２４：００：００ －＞ シリアル値は１
（０：００：０）
＜日付、時刻のシリアル値＞
２００４／４／２４の１２：００：００ －＞ シリアル値は３８１０１．５
上記のように、日付・時刻の実際の数値はシリアル値で持っています。
この時、時刻の表示は２４時間を越えたものは、翌日の時刻として、「０：００」にいったんもどって表示されます。
また、実際の数値はシリアル値なので「３６：００」といった時刻であっても実際は「２４時間＋１２時間」で「１＋０．５」の「１．５」にしかなりません。
ですから、勤務時間×時間給のような計算を行いたいときは、勤務時間を計算に使える数値に変換する必要があります。
（通常、「３６：００」は２４時間を越えているので「１２：００」と表示される。「３６：００」と表示させるには、「表示形式」を「ユーザー定義」で「[ｈ]：ｍｍ」のようにする。）
＜給与の求め方＞
１．９：００から１７：００まで働いたとします。
２．この場合勤務時間を時分で求めると、１７：００ － ９：００ で８：００になります。
３．時間給が１０００円だったとき、給与を１０００ × ８：００で求めようとしても正しい結果が得られません。
なぜなら、８：００というのは実際の数値はシリアル値で、０．３３３・・・だからです。
４．ですので、８：００を給与計算の時間として計算できる数値に変換しなければあなりません。（この場合、８：００は８という数値に変換できれば良い。）
５．変換方法として、次のいずれでもかまわない。
（２４時間がシリアル値では１となるので下記のような計算式となる）
a)時刻／”１：００”
b)時刻／１／２４
c)時刻＊２４
＜日付の表示形式＞
表示形式 表示例（シリアル値３８１１３の場合）
１．ｙｙ／ｍｍ／ｄｄ → ０４／０５／０６
２．ｍ／ｄ（ｄｄｄ） → ５／６（Ｔｈｕ）
３．ｄｄｄｄ，ｍｍｍｍ，ｄ，ｙｙｙｙ → Ｔｈｕｒｓｄａｙ，Ｍａｙ，６，２００４
４．ａａａａ → 木曜日
５．ｇｇｇｅ”年” → 平成１６年
６．ｇｇｅ → 平１６
７．ｇｅ／ｍ／ｄ → Ｈ１６／５／６
８．ｅ”年”ｍ”月”ｄ”日”（ａａａ） → １６年５月６日（木）
＜”［］”を着けた時刻の表示例（”［］”を付けると表示が変わる）＞
表示形式 表示例（シリアル値３．２３８９の場合）
１．［ｈ］：ｍｍ：ｓｓ → ７７：４４：００
２．［ｈ］”時間”ｍｍ”分”ｓｓ”秒” → ７７時間４４分００秒
３．［ｍ］”分” → ４６６４分
４．［ｓ］”秒” → ２７９８４０秒
 |
| 壁③日付・時刻の計算がわからない２ ( No.5 ) |
- 日時： 2004/07/20 01:39
- 名前： 管理人
- 日付を扱う関数には以下のようなものがあります。
１．曜日を求める。
＝ＷＥＥＫＤＡＹ（Ａ７）
セルのＡ７に入った日付から曜日の番号を求めます。
１：日曜日
２：月曜日
３：火曜日
４：水曜日
５：木曜日
６：金曜日 ｜
７：土曜日
２．翌月の１０日を求める。
＝ＤＡＴＥ（ＹＥＡＲ（Ｃ４），ＭＯＮＴＨ（Ｃ４）＋１，１０）
Ｃ４に入ったセルから翌月の１０日を求めています。
ＹＥＡＲは日付から年を取り出す関数。
ＭＯＮＴＨは日付から月を取り出す関数。
ＤＡＹは日付から日を取り出す関数。
ＤＡＴＥは年、月、日から日付を求める関数。
|
| 壁④なぜだか計算違いが起こってしまう ( No.6 ) |
- 日時： 2004/07/22 03:42
- 名前： 管理人
- 計算が得意なはずのエクセルは、実は「計算間違い」を起こします。
これはコンピュータで計算を行う場合、二進数を使うことから起こる問題です。
整数では問題ないが、問題は少数です。
たとえば十進法の「０．１」も、二進法にすると「０．０００１１００１１００１１００～」と延々と数値が続く形となり、「０．１」を正確にあらわすことができません。
あくまで、正確さを期すなら二進法でない計算法を採用すべきなのですが、二進法はコンピュータと相性が良く、高速で処理できる利点があります。エクセルの場合も正確さより、速度を優先したかたちとなりました。
もちろん、通常の処理ではこのような誤差はほとんど問題がありません。
エクセルでは、少数を二進法で変換する場合、ある桁数（最初の１がでてから５４桁目）で切り捨てか、切り上げかを自動的に行っています。
そのため、「０．１」と表示されていても、実は０．１より少しだけ大きかったり、小さかったりします。通常の表示では、小数点以下の表示桁数がそれほど大きくないため、その違いが見えないまま計算され、知らない間に計算間違いを行っている可能性があるというわけです。
ですから、シリアル値自体が少数である「時刻」の計算の場合には特に注意が必要です。
＜時間の計算時の対象法の例＞
出社時間と退社時間から、勤務時間を求めるとします。
勤務時間は、退社時間から出社時間を引いた値です。
しかし、給与の計算上、勤務時間を１５分単位で切り捨てることとします。
例）
出社時刻 ２０：００
退社時刻 ２２：００
勤務時間 ２:００ ＝ ２２：００ － ２０：００
ＦＬＯＯＲ関数で、この勤務時間２：００がはいったＤ４のセルを１５分ごとに切り捨てると（＝ＦＬＯＯＲ（Ｄ４，”０：１５”））、１：４５と言う値が求められました。
でも、実労は２：００のはずです。
このようなことが起こったのは、２２：００ － ２０：００の値が２：００と表示されているが、実際は２．００より少し小さい値のため１：４５と表示されたと考えられます。
◆解決法１
給与計算は分単位に行うので、分に影響の与えない「０．１秒」を加算して、ＦＬＯＯＲ関数を使う。「０．１秒」は誤差が生じる桁数より大きいので、この数値を加えることで、わずかにちいさかった数値が、表示時間（例では２：００）より大きくなるので問題を防げます。
例）
１．元の時刻に０．１秒を足す
＝Ｄ４＋”０：００：０１”
２．０．１秒足した時刻を１５分単位で切り捨てる
＝ＦＬＯＯＲ（Ｇ４，”０：１５”）
◆解決法２
分の部分を整数化して計算する。
整数化する方法として、ＭＩＮＵＴＥ関数で分を取り出す。
例）
１．時間データから「時」だけ取り出す
＝ＨＯＵＲ（Ｄ４）
２．「分」だけ取り出す
＝ＭＩＮＵＴＥ（Ｄ４）
３．出した「分」をＦＬＯＯＲ関数で切り捨てる
＝ＦＬＯＯＲ（Ｆ４，１５）
４。時間データに戻す（シリアル値に戻す）
＝ＴＩＭＥ（Ｅ４，Ｇ４、０）
|
| Re: ＥＸＣＥＬの壁 ( No.7 ) |
- 日時： 2005/08/23 14:59
- 名前： 王 玉砕
- 01を数値として 入力したい
|
[18] ＥＸＣＥＬの壁