はじめに
経理業務の自動化およびDXという仕事の都合柄、人様の作ったExcel1を作り直すという業務がちょいちょい発生します。
でこれをやる際にまず第一段階として
- シート設計の意図を推測
- 意図がブックの目的と合致しているか検証
- 意図と実装に矛盾がないか検証
という作業(読み解きと呼んでいます)を行うのですが、
当記事はこの作業中に「この関数が出てきたら要注意だな」と思っているものをまとめています。
つまり
- 意図と実装の矛盾(＝バグ)が出やすい
- 設計の意図を推測しにくい
関数たちですね。
読む側の人だけでなくExcelを作る側の方にも参考にしていただければ幸いです。
COUNT / COUNTA / COUNTBLANK
↓ それぞれの関数がどんなセルをカウントするか
| COUNT | COUNTA | COUNTBLANK | |
|---|---|---|---|
| 0 | 〇 | 〇 | × |
| 0以外の数値 | 〇 | 〇 | × |
| 空文字列("") | × | 〇 | 〇 |
| 空ではない文字列 | × | 〇 | × |
| 空白 | × | × | 〇 |
バグの温床筆頭格です。
見かけたら必ずそのCOUNTでいいのかどうか確認しましょう。
[追記]
なお=セル=""は空文字列と空白でTRUEが返ります。
よって=COUNTIF(範囲, "")は=COUNTBLANK(範囲)と等価です。
ISBLANK
COUNTBLANKに関連して。
Excelは空白というものに対する認識がガバなので関数ごとに何がBLANKに該当するのか覚える必要があります。
ISBLANKは空文字列をBLANKとして扱いません。
ROUND
世のroundを名乗る関数には通常の四捨五入を行うタイプと銀行丸めと呼ばれる丸め方をするタイプの2種類があり、ワークシート関数のROUNDは前者であるという認識は持っておくといつか使えます。なぜかと言うとVBAの関数Roundは後者なので…
=ROUND(4.5, 0)
→ 5
Round(4.5)
→ 4
NOW / TODAY
- PCの設定から現在日時を取得するため、正確性の担保がExcel内で完結しない
- Excelを介さずにxlsxを読み込む場合(Pythonのpandasなど)に再計算が行われない
- 同じxlsxを読み込んでいるのにExcelを使うか使わないかによって値が変わるのはデータとして扱いにくい
ちょっとしたユーティリティ関数のくせになかなかの問題児です。
そもそもこれらの関数ってワークシートを印刷(そして押印)する用以外に用途を見たことがないんですよね。
令和の時代に事務仕事でそれは業務フロー自体がおかしいのでExcel云々以前に抜本的な対策をおすすめします。
VLOOKUP
VLOOKUPがバグる原因はだいたい
- 検索方法に近似値を指定しているのに検索範囲の最左列が昇順でソートされていない
- 近似値検索の仕様(検索値以下の最大値)を理解していない
- 検索方法の指定が間違っている
のいずれかです。特に最後は深刻で、
●近似値で検索される書き方
=VLOOKUP(値, 範囲, 列)
=VLOOKUP(値, 範囲, 列, TRUE)
=VLOOKUP(値, 範囲, 列, 1)
●完全一致で検索される書き方
=VLOOKUP(値, 範囲, 列,) ←!?
=VLOOKUP(値, 範囲, 列, FALSE)
=VLOOKUP(値, 範囲, 列, 0)
こんな仕様でバグを出すなという方が無理筋です。
よくインターネッツでは「VLOOKUPが使えたらExcel中級者」という言説を見かけますが、随分と高いハードルを設けるんだなぁとしみじみ思います。
またVLOOKUPが仕様上抱える諸々の問題を解決するために以下で紹介する2つのテクニックが知られており、これらは便利である一方知らなければ設計者の意図を読み取ることが出来ないという新たな問題を生み出しました。
INDEX & MATCH
検索値は指定した範囲の最左列に存在しなければならないという縛りを突破するための記法です。
=VLOOKUP(値, 検索範囲, 列, TRUE)
↓
=INDEX(検索範囲, MATCH(値, 値が存在する列の範囲, 1), 列)
あるいは
=INDEX(抽出したい値が存在する列の範囲, MATCH(値, 値が存在する列の範囲, 1))
=VLOOKUP(値, 範囲, 列, FALSE)
↓
=INDEX(検索範囲, MATCH(値, 値が存在する列の範囲, 0), 列)
あるいは
=INDEX(抽出したい値が存在する列の範囲, MATCH(値, 値が存在する列の範囲, 0))
また検索範囲を降順でソートし、MATCHの第3引数に-1を指定することによりVLOOKUPでは不可能な「検索値以上の最小値」検索が可能になります。
2重VLOOKUP
VLOOKUPの完全一致検索はデータのソートを必要としない代わりに実行速度が遅いという問題を抱えています。
そこで高速な近似値検索(正確には近似値検索が速いのではなく完全一致の場合と近似値の場合とで配列に対する探索アルゴリズムが変わります)を2回重ねることにより、大量のデータから完全一致で高速に値を抽出しようという発想がこの記法です。
=IF(VLOOKUP(検索値, 範囲, 1, TRUE) = 検索値, VLOOKUP(検索値, 範囲, 列, TRUE), 任意の値)
「近似一致で範囲の最左列から抽出した値が検索値と等しい」＝「範囲の最左列に検索値が存在する」ということなので、事実上完全一致で値を抽出していると見なせます。
合体
さて聡明な皆様はお気付きかと思いますが、これら2つのテクニックは同時に使用されることがあります。
=IF(INDEX(検索値が存在する列の範囲, MATCH(検索値, 検索値が存在する列の範囲, 1), 1) = 検索値, INDEX(検索範囲, MATCH(検索値, 検索値が存在する列の範囲, 1), 列), 任意の値)
↓ 省略できる引数を省略
=IF(INDEX(検索値が存在する列の範囲, MATCH(検索値, 検索値が存在する列の範囲)) = 検索値, INDEX(抽出したい値が存在する列の範囲, MATCH(検索値, 検索値が存在する列の範囲)), 任意の値)
よく見る形
=IF(INDEX(hoge!$B:$B, MATCH(A1, hoge!$B:$B)) = A1, INDEX(hoge!$A:$A, MATCH(A1, hoge!$B:$B)), "")
自分で書いててこんなん分かるわけないだろって思いますが、実際見かけるので諦めて覚えてください。
救い
今まで書いたごちゃごちゃを全部吹き飛ばす上にワイルドカードを用いた検索まで出来るようになったXLOOKUPという関数が提供されるようになりました。実際に使ってみるとMSが今までどれだけVLOOKUPに対する苦情を受けてきたのかがよく分かります。本当に使いやすいです。
ただし使用できるのはOffice 365限定のためあと10年くらいはVLOOKUPも現役なんじゃないでしょうか。神よ…
IFERROR
見かけるたびにキレそうになります。
=IFERROR(値1, 値2)
↓ Pythonで書くと
try:
値1
except Exception:
値2
エラーの原因(そもそもエラーを吐いてるかどうかも)がぱっと見では分からないと思うので見かけたら必ずひっぺがしましょう。
よく見かけるのは完全一致のVLOOKUPがエラーを吐いた場合空文字列を返すやつとかですが
=IFERROR(VLOOKUP("hoge", A:C, 3, 0), "")
これって殆どの場合こういうことを想定してると思うんですよね。
=IF(COUNTIF(A:A, "hoge"), VLOOKUP("hoge", A:C, 3, 0), "")
ならそう書いて欲しい。
下の書き方ならエラーが出た時の原因究明が上に比べて1手楽になりますし、何より読み手にやさしいと思いませんか。
よろしくお願いします。
AND / OR / IFS
例えば
=AND(A1 <> 0, MOD(60, A1) = 0)
'A1は0ではなく、60はA1で割り切れる
という式があったとして、A1に0を入れると#DIV/0!(0では割れないよエラー)が出ます。
これはむしろプログラミングをやっている人の方が引っ掛かりやすいような気がしますが、うっかりA1 <> 0がFALSEならMOD(60, A1) = 0は評価されずにそのままFALSEが返るだろと思った人がA1 = 0の場合のテストを忘れて後に爆弾と化している光景をよく見ます。Excelの評価はショートサーキットではありません。
SUMPRODUCT
配列の積和演算を行う関数ですが、もっぱらSUMIFSやCOUNTIFSの代替として使われます。
=SUMPRODUCT((A:A="hoge")*(B:B="fuga"), C:C)
'C列のうちA列がhogeでB列がfugaである行の合計
標準機能で簡単にできるものをわざわざ関数で難しく書く必要は一切ありません。
ピボットテーブルで置換しましょう。
おわりに
VLOOKUPの項を書いてだいたい満足しました。
何か追加すべき関数があればコメントでお願いします。
-
伝わりやすさ重視のためExcelブックのことをExcelと呼びます。ご了承ください ↩