ROUND関数の動作

round関数と表示桁数の書式設定を使うと、小数点以下もきちんと揃って、実際の計算も見た目通り行ってくれる、ということでこれは必須のテクニック。

ところが今日こんな事がありました。

10数回の給油のデータから燃費の平均をもとめるところで、round関数を使いました。
小数点以下1桁で表示ということで
　ROUND(AVERAGE(M4:M51),1)
などとして、セルの書式設定も「数値」小数点以下１桁。にちゃんと設定したにもかかわらず、結果が整数の表示。

そこで
　ROUND(AVERAGE(M4:M51),2)
としてみましたが、それでも表示結果は整数のまま。

　ROUND(AVERAGE(M4:M51),3)としてはじめて、22.998などと表示されました。（表示桁数を増やして確認）

どういう条件でこんなことになるのかわかりませんが、ROUND関数と表示桁数の書式の２つをちゃんと使ったからと言って安心できません。

round関数なんて、初期のバージョンから仕込まれているのに、いまだにこういうユーザー泣かせのものが解決されていないというのはよほど難しい問題なのでしょうか。

さらに目が回りそうなこと発見 '行'方向→'列'集計→親'行'集計???

前回、Excel2010になって、ピボットテーブルの計算の種類の呼称が変わったことを書いたが、その後さらに目が回りそうなことを発見。

2010では、計算の種類に新たな項目が加わっていて、それもウリの一つらしいが、たとえば「親行集計に対する比率」。
多重クロス集計の時の、内訳部分についての構成比を求めてくれるようだ。

クロス集計の、下の合計を元にするか、右の合計を元にするかで
下の合計を元にする場合→「列集計に対する比率」
右の合計を元にする場合→「行集計に対する比率」
（この段階で、2003と列と行の文字の使い方が逆転→前回報告）

そして、多重クロス集計で2010でいうところ「列集計に対する比率」の計算での中間の小計の範囲での内訳の比率を取るときは、なんと「親行集計に対する比率」を選択することになる。

全体としては列集計に対する比率なんだけど、内訳を計算したい時は親行集計らしい。
（このへん、ネットでぐぐってもほとんど情報が出こない、というのもすごい）

さらに！
この「親なんとか集計に対する比率」機能は複雑なクロス集計のために導入されたのだろうが、実際出来上がった集計表をみて、それが、単なる列集計に対する比率、なのか「親業集計に対する比率」を計算したのかは、集計結果の数値を一つひとつチェックしてみないと判別がつきにくいのだ。
せめてどこかにマークのようなものを表示してくれればいいのだが。

盛んにユーザエクスペリエンスを強調して新バージョンを投入している某社の製品とは思えない。というか、言葉だけ目新しくして、体質はやっぱり以前と同じなんだな。

列方向の比率が、行集計に対する比率、と同じ意味だなんて？？

Excelのピボットテーブル。
以前、Excel2007へのバージョンアップの際には、メニューの考えたが根本から変わって「リボン」なんて呼称になり、それ以上に欲しい機能がどこにいったのか、あれこれ探しまわる毎日だった。

それに比べると、Excel2010へのバージョンアップは、見た目は殆ど変わらない。ピボットテーブルの操作性も特に大きな変更はなし、ということで安心して使っていた。

と　こ　ろ　が　！！！
クロス集計して、カテゴリー間の違いを比率（％ベース）で比べるときに使う、『フィールドの編集』の「計算の種類」のところに、とんでもない変更があったのだった。

（旧）標準→（新）計算しない
（旧）列方向の比率→（新）行集計に対する比率
（旧）行方向の比率→（新）列集計に対する比率

こんな、人を馬鹿にしたような、現場を混乱に陥れるような変更は、さぞかしネットでも話題になっているだろうと、Google神様にたずねてみたら…
ほとんど見つからない。まさかどこかの国のようにフタをしているわけではあらまい。これはいったいどういうことなのか……

皆さん、お仕事でピボットテーブルはあまり使っていないのか、それとも、こんな言葉に悩まなくても済む、もっとスマートな方法を使っているのか。だったら教えて。
本来2010へのバージョンアップの主眼は、そういったユーザに適切なガイドをするように改善したのではなかったのか。

こんなことで騒ぐ事自体が実はすごく恥ずかしいことだったりして…

検索置換が異常に遅くなった！

アンケート集計で、空欄のままにしておいたところを一括して０に置換した。
ところが異常に遅い！40000個位のセルを対象にしていたが20分くらいかかった。
おかしい！

ふとステータスバーをみてみたら、個数や平均が表示されている。そうした記憶はなかったのだが。

そこでステータスバーの表示項目のチェックを軒並み外してみた。

やはり、これだった。

置換はもとのようにスッとできるようになった。

リスト領域を認識しない！

フィルタ機能や、ピボットテーブルを作るときに、リスト領域を自動的に認識してくれる機能をよく使う。

たまたま今日ピボットテーブルを作ろうとしたら、項目行の一つ下、つまりデータの行からしか認識してくれなくなった。

リスト領域を予め指定してからやれば大丈夫だと思い、再度ピボットテーブルをつくろうとしたら、今度はフィールドエリアに不正な値がある、とかいうメッセージが出てしまった。

具体的にどんなデータだと不正となるのか、Microsoftのサイトで検索してもよくわからない。

で、はたと気づいたのが、項目行(フィールド名)に同じものがあるとだめかもしれない、ということ。
確かに同じものがあったので、それを直したらちゃんと認識した。


これで一件落着。

ということで、このことをブログに書く前に、Excelを広げて、再現実験することに。
わざと同じ項目名をつかってリストデータを作ってやってみた。

と、こ、ろ、が
同じフィールド名の部分は、自動的に添字がついて、何もエラーにならずにすんなりとピボットテーブルが作られてしまった。

一体何なんだ！

ワークシートのコピーでだまされる

Excel2007

同じブック内でワークシートのコピーをする方法として、ワークシート見出しのタブをCtrlキーを押しながらドラッグする方法が使える。

だまされるのは、Ctrlキーを押しながら先頭のワークシートのさらに手前、最も左寄りにドラッグしたときだ。右にドラッグしたときと同様、小さな▼マークが表示されるので、そこでドロップすれば当然コピーができると期待してしまう。しかし現実はまんまとだまされてしまう。

数式バーのフォント

数式のエラー発見に手間取ってしまった。
結局、数式バーではほとんど括弧とカンマが合体しているようにみえるため、余計なカンマが発見できなかったというのが原因（たとえば下図の最後の部分、ややぼけぎみのCRTディスプレイだともっと識別しにくい）。

数式バーの数式をもう少し見やすくできないものか?
ツール→オプション→全般　標準フォントで変えることができる。で、変えてみた。

どうだろう、関数の引き数の区切りのカンマがはっきり分かるようになったのではないだろうか。

しかし、ワークシートの標準のフォントと連動するというのが難点。

あんだけおせっかいなオプションてんこ盛りなのだから、基本的な数式バーのフォントくらいは独立して見やすいフォントに設定できて欲しいよな。

ところで
2つの図を見て、たった今発見!!!　UIゴシックとMS明朝でダブルクウォテーションのヒゲの向きが逆なんだ！
これって、どっちかが間違っているということ？

 

 

 

勝手に書式変えないでよね

学内で行なった模擬試験解答をマークシートで読み取って集計。とある事情で中間処理をExcelで行うことに。

エクスポートされた認識結果をExcelで読み込み、正解データ（選択枝番号）と一致するかどうかをIF関数でチェックして1か0を与えることにした。

ところがどうも得点の様子がおかしい。よく見ると、Excelで読み込んだときに、一部の列が文字列形式で読み込まれている。なので文字列と数値を比較しても一致せず、得点に計上されていないことが判明。

さらに酷いことに、これらのデータは単にセル書式で文字列に指定されているのと違い、数字の前にアポストロフィーをつけて入力したと同じ状況になっている。こうなると範囲を指定してセルの書式を変更してもだめ。

結局Excel上のデータをコピーしてテキストエディタにタブ付き文字列として貼り付け、さらに全面文字列のセル書式を設定したワークシートに貼り付け直す。という変な方法で、すべて文字列で比較して採点するようにして何とかなった。

【教訓】アポストロフィーをつけて文字列として入力したデータと、数値として入力したセルの書式を文字列に直したデータは同じではない。（セル書式の文字列のところの説明「数値も文字列として扱います」は、ウソを書いているか誤解を与える表現だ。文字列として扱うといいながら、アポストロフィーをつけて文字列として入力したものとは一致しないようだ。）

さらに凄いのは

セルに123と数値入力→セル書式を文字列に設定。この段階で「'123」と入力したセルとは不一致

123の前にアポストロフィーを挿入→当然「'123」と入力したセルとは一致（if関数で判定・確認）

挿入したアポストロフィーを数式バーで削除

もとの「不一致」に戻るかと思いきや、IF関数の判定は「一致」。ところが数式バーを見ると片方にはアポストロフィーがなく、もう片方にはついている。

一体全体どうなってんだ？

これって、入試などでマークシート読み取りで採点しているところで問題になっていないのだろうか?（調べてみてはいないが）Excelで入試の採点なんて怖くて出来ないのでは。

 

危険な状態にいつから？

きょういつものようにExcelを使っていて、

ワークシートを削除する時に、何の確認メッセージもいきなり削除されてしまった。複数のワークシートを選択してたからか？まだ保存していないワークシートだったから？いろいろやったが、どうも何らかの設定が変わってしまったようだ。

そしてこの設定がどこなのかを探すのが大変。結局みつからず。

こまった。

ワークシートの削除は、Undoが効かない極めて危険なもの。それがなんの確認もなく行われてしまうなんて‥‥

怖くて使っていられない。（といいつつ使わざるをえない、いつかドボンをやってしまいそうだ）

時間の計算

就業時間と時給から時間給を計算するような場合

「6:30」などとセルに記入すると自動的に時刻の書式に変換してくれる。

こうやっていくつかセルに入れて合計を取ると一見ちゃんと計算してくれるようだが、24時間を超えると0に戻る（というか１日分の桁上がりがあって、日の部分が表示されない）

問題は、Excelのヘルプを検索して解決しようとしても適切な情報が得られないことだ。読んでいくと望まない方向に引きずられるようなヘルプ。24時間を超える時には24を掛けろとまで書いてある。これで誤解してしまうユーザも多いはず。（ヘルプですんなり解決というのはむしろ珍しいくらい）

Be cool userの中のhttp://kokoro.kir.jp/excel/hyouji_time.html を見たらセルの表示書式でhh:mmではなく[h]:mmと指定するらしい。これで解決！

しかしこんなによく使うと思われる書式がExcelであらかじめ用意されていないというのもおかしな気がする。ユーザ定義の中に[h]:mm:ssはあるのだが‥‥‥