2014年9月15日月曜日

EXCELの動作がおかしい

人からEXCELの動きがおかしい、と尋ねられて以下の様な画面を見せられました。

(本物はもっと違う数字ですが、例なので単純にしています)最後の合計のところが関数になっていません、あきらかに文字列になってます。セルの書式設定を「標準」にしてやるだけだろうと思い、設定を変えようとしてみると、以下の状態になってました。

あれ?ちゃんと「標準」になってるよ?これ実は「オプション」→「詳細設定」の中にある以下の設定によるものです。

デフォルトではこの「データ範囲の形式および数式を拡張する」がOnになっています。これ何のことかといいますと、直上の連続するセル(今回の例では黄色で示してあるセルがそれにあたります)3つ以上が同じ書式を持っている場合、それを自動的に引き継ぐ機能なんです。
種を明かすと、この黄色のセルは実は「文字列」にしてあります。そのため合計を求めようとSUM関数を入れたのですが、自動的に文字列の書式に設定されてしまいます。

でも2枚目のSSで「標準」になっていることを確認しましたよね。これはある操作を行うと、上記な様なおかしな状況になってしまうことを確認しました。(注:毎回必ずなるわけではないようです。もう少しトリッキーな操作が必要かもしれません。)

①デフォルトのままオートSUMを入れる。(EXCELがここで困ったことに文字列の数字でも、変に「気をきかして」数値として計算してくれるのが間違いの始まり)
②SUMが計算され、結果が文字列として表示される。(従ってこの時点ではおかしなことになっているのに気付かない)
③ここで詳細設定の、上記設定をオフにしてやる。
④合計を行うセルの内容をいったん消して、再度オートSUMを入力する。
⑤②のときにこのセルの書式が文字列になってしまっているので、最初のSSの状態になる。
⑥ここで③の操作を取消、「データ範囲の形式および数式を拡張する」をOnにする。
⑦オートSUMが文字列として表示されているままなので、書式を「標準」にしてやる。

おそらく、書式のフラグがユーザーが操作するものと「データ範囲の形式および数式を拡張する」と別個に内部で持っており、そこに矛盾が発生するようです。
また、できる限りユーザのために思ったような動きをしようと、以下の様な状態の時もあります。

オートSUMを入力したんですが、自動的に上のセルの書式が移ってしまい「文字列」なんですが、計算はしてくれて、結果出力の段階で「文字列」として表示しているようです。

明らかに複数の異なる要求仕様の間に矛盾が発生しているようです。

とりあえずこれを直そうと思ったら、一度「データ範囲の形式および数式を拡張する」をOffにして、当該セル、および直上のセル書式を数値にしてやり、再度「データ範囲の形式および数式を拡張する」をOnにしてやるのが早そうです。


PS
早速、直しに行ってきましたが、うまくいきません(;_;) オートSUMのセルの書式を見てみると、「数値」になっていました(@o@) なんだこりゃ?よくよくファイル名を見てみると(自分は拡張子を必ず表示にしてますが、普通の人は表示しないか、登録してあるものは表示しないになってますね)、「*.xls」になっており、どうも2003で最初は作成したもののご様子。うーん、2003のデータを2007/2010で操作すると色々問題が報告されているから、もっと奥が深いかも。結局、「数値だけ」と指定して内容を新しいファイルにコピーして、必要なオートSUMのところだけ手作業で同じ様なファイルを作り直してしまいました。

後から思い返すと、一回2007の形式に変換保存して、上記手順をやってみるという方法もあったな~と思います。おそらくこの機能、2007からだよね。

0 件のコメント:

コメントを投稿