最近ちょっと手の込んだ Excel を作ったのだが、集計範囲を可変にしたり、大文字と小文字を区別する関数での集計と条件付き書式の設定に手間取ったので、そのまとめ。
座標文字列を座標にする INDIRECT 関数
使うことが多いのだが、すぐ忘れるのでメモ。
=COLUMN(INDIRECT("Z20"))
は、
26
が返る。"Z20" のところは、セルの参照でもよい。A3 に、"Z20" が書いてあれば、
=COLUMN(INDIRECT(A3))
と書けばよい。
ちなみに、A3 が "あいうえお" という文字列なら、
=INDIRECT(A3)
は、
あいうえお
になる。
ヘルプでは、
指定される文字列への参照を返します。セル参照はすぐに計算され、結果としてセルの内容が表示されます。INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセル参照を変更することができます。
という言い方がしてある。
逆に座標文字列を返す ADDRESS 関数
=ADDRESS(15,33)
は、
$AG$15
が返ってくる。INDIRECT、ADDRESS ともに、式の引数を、別のセルで与えたいときに使える。
COUNTIF で大文字と小文字を区別する
A22:A26 に A と a が混在していて、a だけの数を調べたいときは
=SUMPRODUCT(EXACT(A22:A26,"a")*1)
のようにすれば、a の数だけ返ってくる。
ちなみに、EXACT は、大文字小文字を区別した比較で、
=IF("A"="a",TRUE,FALSE)
だと TRUE なのだが、
=EXACT("A","a")
ならちゃんと FALSE が返る。
ここで、もともとの式の EXACT の第1引数がひとつの値でない(つまり配列)であるのだが、この配列の計算を行う式として、SUMPRODUCT がある。
SUMPRODUCT は、ヘルプによれば、
引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。
とあり、解説としては、
SUMPRODUCT関数の使い方:Excel関数
がわかりよい。
A22:A26 のセルが上から順に、a,A,A,a,A であるなら、
=SUMPRODUCT(EXACT(A22:A26,"a")*1)
は、
=SUMPRODUCT(EXACT({"a","A","A","a","A"},"a")*1)
と同じこと(この式は概念的なものでなく計算できる)であり、SUMPRODUCT の第2引数以降は省略できるから、乗算は行われず、和だけ返る。
SUMIF で大文字と小文字を区別する
結局、SUMPRODUCT の第2引数に列に対応する配列の座標を入れてあげればよい。
上記の例で、B 列に集計したい値があるときは、
=SUMPRODUCT(EXACT(A22:A26,"a")*1,B22:B26)
のようにすれば、大文字と小文字を区別して SUMIF できる。
アスタリスクとクエスチョンマーク
IF でも、SUMIF でも、COUNTIF でも、VLOOKUP でも * と ? はワイルドカードの意味になってしまうので、検索対象になるキーワードには使わない。ちなみに、* は任意の文字列で、? は任意の1文字を示す。むかし、お客さんに、計算対象外になる項目にマークをつけてください、とお願いしたら、アスタリスクでマークされてしまって、VLOOKUP が正しく計算されなかったことがあったので、ぜひ気をつけたい。
大文字と小文字を区別して条件付き書式を設定する
セルに "R" という文字列が入っているときに、色を変えたい場合は、
こうすればよいが、これだと "r" の色も変わってしまう。"R" だけ変えたいときは、
のように、CODE 関数を使う。
CODE 関数のヘルプによると、
テキスト文字列内の先頭文字の数値コードを返します。 返されるコードは、コンピューターで使用されている文字セットに対応します。
であり、CODE("A") は 65 である。ようするに ascii コードだが、CODE("A") (全角)でも 9025 を返すので、ヘルプによれば、Windows では、ANSI 文字セットの数値コードを返すという言い方がしてある。
※9025 は &H2341 で、&H2341 が A であるのは JIS (ISO-2022-JP) ですね。
条件付き書式を VBA で定義する
シート全体の条件付き書式をクリアする
条件付き書式は、セル範囲とシート全体とに対応するものがあり、前者は Range オブジェクトにひもづくが、後者は Cells にひもづき、セル範囲に設定されているものも取得できるようである。つまり、シートのすべての条件付き書式をクリアするには以下のようにする。
Dim i As Integer Dim j As Integer j = main.Cells.FormatConditions.Count For i = 1 To j main.Cells.FormatConditions.Delete Next
条件付き書式を設定する
上で書いた "R" だけを 赤色にする場合はこのように書く。
Dim condition As FormatCondition Set condition = Range("F11:BA1000").FormatConditions.Add(xlExpression, xlEqual, _ "=CODE(F11)=CODE(" & Chr(34) & "R" & Chr(34) & ")") condition.Interior.color = RGB(255, 0, 0)
おわり。