数字をだすのが怖い？！（１）
さて、これだけfactfulnessだ、DDMだと話題になっても、いざ自分が数字を出すとなると、結構怖いもの。
恐れるのは間違った数字を出してしまうことですね。
私の経験から、以下の5つが代表的なミスの原因です。
4.と5.はすでに書いた通りです。1～3について順に見ていきたいと思います。
1.範囲指定の不足や誤り
2.項目要素の不足・重複
3.平均値の出し方
4.平均の平均は、全体の平均にならない
5.伸長率・達成率での注意点
1.範囲指定の不足や誤り
ビジネスレポートではカテゴリー毎の集計や平均値、比率などのKPIを出して、状況を認識します。
したがって、SUMIFSや、VLOOKUP・INDEXなど、範囲指定する関数を多用することになります。
エクセルではその範囲指定は目でみながら指定できるというのが、便利でとっつきやすいところですが、やはり人がマニュアルでやるとうっかりミスがでます。特に大きなテーブルに対して込み入った指定をする時などは。
「ビッグテーブルに挑む3つの基本技」で使った名字リストを例に見てみます。
↓にファイルがあるのでダウンロードしてください
今回は、名前で検索してその順位を世帯数を取得するのに3つのやり方が記されています。
①INDEX関数の中に、直接参照範囲を入れたケース
この数式を編集状態にする(このセルにカーソルをおいてF2を押す)と、このように参照範囲に色がつきます。
こうして参照範囲を視覚的に確認できます。
ただし、今回のように1万行以上の場合は、最後までカバーしているかどうかの確認がしずらいといえばしずらい。（右のスライダーで押し下げて最終行まで行く必要がある）
②列を指定（A:Aのように）
この場合は列の最後まで参照しているので、モレはありません。
このやり方が一番シンプルでしょう。
③I-Iメソッド（参照範囲外出メソッド）で紹介したINDIRECTを使う方法
参照範囲が外だしになっている。
しかし、最終行までカバーしているかは、パッとはわからない。
そこで、最終行を自動で検知し、それを参照して範囲を作ればモレはない。
ここではB2に最終行を示す数式を作っています。
最終行を表示する数式はやや高度なテクニックですが、
=MAX(INDEX((LEN(A:A)>0)*ROW(A:A),0))
LEN(A:A)はA列の各セル文字数をみています。空白行はこれが0なので、0ではない最大の行数を出す式となっています。
（まとめ）ビッグテーブルで確実に範囲指定する方法
毎回数式を作り直さずにもれなく範囲指定するには、
A） A:Aのように列指定する。
B） 自動で最終行を検知し、それで参照範囲を自動作成し、INDIRECTで参照する。
Aのやり方がシンプル。しかし列すべてを見に行くので条件や検索が多いと重くなるかもしれません。
この記事が気に入ったらサポートをしてみませんか？