065 検索値に数字を指定するVLOOKUP関数でよくあるエラーの対処法

　関数を入れた表とマスタに同じ数字を入力してあるのに、VLOOKUP関数がうまく作動せず、エラーが出ることがあります。特にCSVなどでダウンロードしたデータや、JANコードなどを使用する際にしばしば起こります。
　これは、どちらかの値が「数値」ではなく「文字列」になっているのが原因です。文字列のセルをダブルクリック、もしくは[F2]で編集状態にすると数値に変換されまが、そんな時間の余裕はもちろんありません。関数を使って一瞬で解決しましょう。
　下表では、C列の商品コードが文字列となっているためエラーが出ています。

1. E列に作業列を作り、E2セルに「=VALUE(C2)」と入力

2. D2セルのVLOOKUP関数の第一引数をC2からE2に変更し、D2:E2を選択し、2列まとめて最下端行までドラッグコピー

　空白セルを1つ選んで[Ctrl]+[C]でコピーし、「形式を選択して貼り付け」で「加算」をすると、作業列を作らずに文字列を数値化することができます。
　また、図とは逆にマスタが「文字列」、表が数値になっている場合はTEXT関数で変換ができます。E2セルに「=TEXT(C2,”@”)」と入力します。

1万人の業務効率を劇的に改善したExcel速技BEST100/PHP研究所

￥1,836
Amazon.co.jp

061 項目別件数カウントのスピードアップ関数~COUNTIF関数

　「イベントの出欠リストから、出席者の数を数えたい」。そんなときに目視で数えたりオートフィルタをかけたりして、時間をムダにしていませんか? COUNTIF関数 を使えば、出席者の数を随時、自動算出してくれます。
　COUNTIF関数は、第一引数に「数えたい範囲」を置き、第二引数に 「数える条件」を入力します。E1セルに入力する関数を文字化すると、「B列に はD1セルと同じ値(「出」)が何個ある?」の意になります。

　E1セルに「=COUNTIF(B:B,D1)」と入力し、E3セルまでドラッグコピー

　SUMIF関数で担当者別の売上を出した表に、今度は担当者別の売上件数 を入れてみると、


1. I2セルに「=COUNTIF(A:A,G2)」と入力

2. I6セルまでドラッグコピーして、I7セルに合計を出す
3. J2セルに「=H2/I2」と入力し、J7セルまでドラッグコピー

　ここでは、1件ごとの平均売上金額として、平均値を出しました。しかし、ビジネスの場で安易にAVERAGE関数などを用いて平均値を出しているのでしたら危険です。 なぜなら 、「平均値は噓をつく」からです。たとえば、 平均年収600万円という会社があったとして、その内訳は、1億円プレーヤーが数人と年収200万円の社員で構成されていて、実際に年収600万円の人は1人もいないことが起こり得るからです。惰性で平均値を出すのは避けたほうがよいでしょう。

1万人の業務効率を劇的に改善したExcel速技BEST100/PHP研究所

￥1,836
Amazon.co.jp

062 条件付き集計のスピードアップ関数 ~SUMIF関数

　分析の基本「分けて比べる」 に必須なのが、SUMIF関数。条件に一致し ている数値の合計を出します。SUMIF関数には3つの引数があります。
・第一引数:集計の基準となる範囲

・第二引数:検索条件
・第三引数:合計したい範囲

担当者別の売上を集計したいときは、

H2セルに「=SUMIF(A:A,G2,D:D)」と入力し、H6セルまでドラッグコピー

　少し複雑ですが、表のH2セルの関数を文字化すると、「合計したいのはD 列。でも全部は合計しません。A列がG2セルの値(吉田)と等しい時だけ合計します」となります。
　SUMIF関数を繰り返し使ううち、自然と要領を覚えられます。関数を使いこなすコツは文字化することです。

　数字を分けて集計したら、 次は構成比を出して比べます。構成比の公式は「部分/全体」。全体を示す分母のセルには絶対参照を付けるのを忘れずに。

　1.H7セルに「=SUM(H2:H6)」と入力
　2.I2セルに「=H2/$H$7」と入力し、I7セルまでドラッグコピー

　条件付き集計を行う際、以下のように、第二引数を文字列として直接入力していませんか? これでももちろん集計はできますが、1行ごとに式を打ち直す必要があり、時間のロスに。「=SUMIF(A:A,G2,D:D) 」のように第二引数をセル参照にすれば、1回でドラッグコピーでき、作業時間が格段に短くなります。元表と 集計表の表記を一致させておくことも時短ポイントです。

1万人の業務効率を劇的に改善したExcel速技BEST100/PHP研究所

￥1,836
Amazon.co.jp