うんこめも

自律分散うんこ製造システムについてのメモ

Excel 特定の倍数の行に色を付ける方法と条件付き書式でIndirectとAndで不具合がある話

前置き

Excelでちょっとした問題にぶつかって解決したのだけれど、日本語で整理されたWebページが見つからなかったので記録を残します。

Qiitaに書くかどうか迷ったのだけれど、Qiitaは「プログラミング知識を共有しよう。」と銘打っているのでブログに書くことにする。

ExcelPhotoshopなどの複雑な機能をもったツールは、公式のドキュメントでは初心者にやさしくなく知りたいことをすぐ知ることができない。ちょっと使い方を調べるとYahoo!知恵袋や2000年代前半のほんわかしたインターネット記事がでてきて日本語や信頼性に疑問を持ちつつ参考にせざるを得ないこともあって、情報の鮮度について考えさせられます。

英語だと、ツールやOSなどをおもな対象としたsuperuser.comという、stack overflowと同じStackExchangeのサービスがあるのだけれど、Excelのようにローカライズされたアプリだとこの文言は英語でどう表現されるか調べるところからはじまってしまい検索性が悪い。

superuser.com

1.問題 Excelで3の倍数の行に色をつけるには?

「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」

こんなふうに書いて、対象範囲を選択します。

=MOD(ROW(),3)=0

「=」が2か所あって気持ち悪いのですが、条件付き書式ではよくあること。 ちなみにMODは合同式です。1つ目の引数を、2つ目の引数で割ったあまりを表現しています。 行番号を3で割って余りが0、つまり3の倍数の行を対象にします。

2.条件付き書式で二重線にするには(応用編)

さて、これで3の倍数の行の色をかえることができました。 同じように、表を見やすくするために5行ごとに罫線を二重にしたい思いがあることはよくありますが、 これはすこし難しい。

なぜなら条件付き書式で選択できる書式には、直線や点線はあっても二重線はないから。

f:id:daaaaaai:20160421141438j:plain

ではどうするか。 無理矢理VBAで書くもありですが、逆に考えて、はじめから二重線にしておいて、それを一重線にかえる、という発想はありです。 特に、行があとあと挿入されたり別シートから抽出される場合には便利かもしれません。

3.もうちょっと複雑な条件式(そして本題)

条件式で複雑な条件をいれたいときああります。5の倍数で、かつ、ある列の値が特定の場合のみ、色を変える。 安直に考えると、MOD式で判定したものと、INDIRECT式とROW式をつかってその行の特定列の値を取得して判定したもののANDをとります。 が、これだとなぜか動きません。

いろいろ調べると、stack overflowやYahoo!知恵袋で困っている人がいたのですが、条件付き書式にて、AND式とINDIRECT式を同時に使うと不具合があるようです(WIndows8 32bit、Excel2013で発現)。

次の画像をみてください。 それぞれ、一行目の式で条件付き書式を指定し、その列の色を変えようとしています。ANDだけのC列やINDIRECTだけのD列はうまく動いていますが、両方をつかったE列はうまく動いていません(ちゃんと式を設定しているかどうか、ここからはわかりませんが、ちゃんと設定しているつもりです)

f:id:daaaaaai:20160421143136j:plain

同じようにORをつかっても動きません。 ではどうすればいいか。AND式ではなくそれぞれの条件式を*で乗じて論理積をとってやれば動きました。

これはバグのようですが、なんとも情報がわからないのでこのまま。

以上、なんというか、そんなに便利ノウハウでも深い知見というわけでもないのですが、ちょっとはまってそれを解決したけれどその問題と解決策が記述されている便利ドキュメントや記事がなかったので書いてみた次第です。

昨日の夢でみた啓示

Excelを殺すためにはそのExcelについて誰よりも深く知る必要がある