これだけでOK！エクセルの効率アップ「関数」＆入力ミスを防ぐ裏技
エクセルの作業スピードを速めて、時短を実現させたい、とビジネスマンなら誰しもが考えているはず。投資銀行では、ものすごい速さでエクセルの計算をしており、日々、限られた時間の中で最大の成果を出すように努めています。
そこで労力を減らし、作業効率を上げてくれる味方が、自動的に計算や検索作業をしてくれる関数です。
エクセルでは非常にたくさんの関数を使えますが、ビジネスの現場で使うのはその中のごく限られたものだけ。関数の一覧を眺めて、公式を頭に叩き込む必要はありません。「このようなシチュエーションでは、この関数が使える」とケース別に理解し、いくつかの実践的な関数だけを確実に使いこなせればいいのです。
そして最後に強調したいのは、エクセルはスピードも大事ですが、それ以上に大事なのは計算ミスがないということ。もし関数の利用によって作業時間を軽減できたら、余った分の時間は計算のチェックに費やすのが理想です。
■【自動で計算してくれる見積書を作りたい】
［1］VLOOKUP関数をマスターしよう
商品名を打ち込めば、自動的に単価が出てくる。そんな理想的な見積書は、VLOOKUP関数を使うことで可能になる。VLOOKUP関数は探したい値を見つけ出してくれる便利な関数。利用頻度も高いので、ぜひマスターしたい。
----------
----------
（a）商品・単価・個数の項目と、その合計を表示する見積書の表を作成。その下に商品と単価を記した一覧をくわえる。
（b）商品名にあわせて単価が自動的に表示されるよう、単価欄にVLOOKUP関数を挿入。検索値に商品名のセルを、範囲には商品単価一覧を指定。さらにF4キーを押し、絶対参照として＄マークをつける。列番号は「範囲から検索値を見つけ、そこから数えて右○列目の値を表示させる」意味なので「2」を入力し、FALSEを打って終了。
（c）作成した計算式をそのまま下へコピーしていくと、商品名にあわせてそれぞれの単価が自動的に表示される。
【完成】合計を表示するセルにSUM関数を挿入し、商品ごとの金額を範囲に指定。合計金額が表示されれば、完成だ。
■【複雑な条件の自動見積書を作りたい】
［2］VLOOKUPを応用して使いこなそう
VLOOKUPは便利な関数だが、検索値に入力できるのは1つのデータ。つまり検索できる条件は1つのみだ。もし検索したい条件が2つ以上ある場合は、2つの条件を組み合わせて、1つの条件を作ってみよう。このやり方を身につければ、見積書などを作るとき、対応しやすくなる。
（a）どの商品も白と黒の2種類があり、それぞれの価格も異なる。これを「商品」と「色」の2条件から検索し、価格を出すのが目的だ。
（b）色のセルの右に、「検索コード」という新しい列を作成。商品と色の2つのセルを組み合わせる式を「＆」を使って入力する。
（c）計算式をそのまま下へコピーすると、「ディスプレイ黒」「プリンター白」など、2つのセルを足した言葉が表示される。
（d）同様に、商品単価一覧にも「検索コード」の列を作成。商品と色を組み合わせた計算結果を表示させる。そして下にコピー。
（e）見積書の「価格」セルに、VLOOKUP関数を挿入。検索値は検索コード、範囲は商品単価一覧の検索コードと価格を指定。列番号は「2」に。
（f）「ディスプレイ黒」の価格が反映された見積書。下のセルにコピーしていくと、他の商品の価格も表示される。
（g）最後、合計のセルにSUM関数を挿入。商品価格を範囲に指定することで、合計金額が出てくる。
【完成】合計金額が表示されて、表が完成。3つ以上の条件でも、＆でつなげて1つの条件にまとめることが可能だ。
■【商品別に売り上げを比較したい】
［3］COUNTIF関数を使いこなそう
名簿などで、「男性は何人か」「20代は何人か」と条件別にカウントしたいときに役立つのが、COUNTIF関数。指定した範囲の中から、検索条件にあったセルの個数を数える関数だ。
----------
----------
（a）販売数にCOUNTIF関数を挿入。範囲には、販売履歴データの商品すべてを選択する。＄をつけて、絶対参照にするのを忘れないようにしよう。
【完成】関数の検索条件には、販売数を数えたい商品（この場合PC）のセルを選択。一番上の行で計算式が完成したら、そのまま下にコピー。COUNTIF関数が範囲の中から数えてくれるので、各商品の販売数を一目で把握することができる。
［4］SUMIF関数を使いこなそう
指定範囲の数値を全部足してくれるメジャーな関数SUM関数に、条件を表すIFがついたのが、SUMIF関数。条件にあった数字だけを合計してくれるので、商品別の売り上げを計算するときなどに使える。
----------
----------
（a）商品別データの「売上」にSUMIF関数を挿入。範囲として販売履歴データの商品すべてを、検索条件に探したい商品のセルを入力する。
【完成】関数の［合計範囲］は、合計したい値が入力されているセルの範囲のこと。この場合、「売上」のセル全体を選択する。計算式を下へとコピーすると、商品別の売り上げが出てくる。
■【月別に売り上げを比較したい】
［5］COUNTIFS関数をマスターしよう
COUNTIF関数の条件は1つだけだが、それに「S」のついたCOUNTIFS関数は、複数の条件指定が可能。複数条件を満たしたデータの個数だけを数えてくれる。それに指定した日付の月だけを表示するMONTH関数を組み合わせることで、さらに便利に。
----------
----------
----------
----------
（a）販売履歴データの中から、6月の商品別データだけを計算する。下準備として「売上日」の右に「売上月」の列を作る。
（b）「売上月」のセルにMONTH関数を挿入し、「売上日」のセルを指定。4月 日であれば、「4」の数字のみが表示される。
（c）そのまま下にコピーすると月だけが並ぶ。なおシリアル値とは日付や時刻を扱う数値で、「1月」は「1」と表示。
（d）商品別データの下に月数を入力し、PCの販売数にCOUNTIFS関数を挿入。最初の条件として、範囲に商品リストを、検索条件にPCのセルを選択。その次に2つ目の条件として、範囲に「売上月」を、検索条件に「6」を選択する。
【完成】「商品がPC」「売上月が6月」という2つの条件にあった「1」の数字が出てくる。計算式をそのまま下にコピーして完成。
［6］SUMIFS関数をマスターしよう
SUMIF関数を覚えたら、複数条件を満たすデータだけを合計してくれるSUMIFS関数も使ってみよう。左式にある「合計対象範囲」とは難しそうだが、売り上げに関する合計を出したいのであれば、売り上げ全体を指定すると考えればよい。
----------
----------
（a）「商品がPC」「売上月が6月」という2つの条件を満たす売り上げを計算する。
（b）PC売上のセルにCOUNTIFS関数を挿入。スキル24と同じ条件範囲と検索条件を選択し、合計対象範囲として「売上」を選ぶ。
【完成】2つの条件を満たした合計の値「150000」が計算される。このセルを下にコピーすれば、できあがり。
■【顧客リストを都道府県別に集計したい】
［7］COUNTIF関数を部分一致で使おう
顧客リストの中から特定の住所を数えたいとき、使えるのがCOUNTIF関数。ただし番地や丁目が記されているセルを範囲に指定し、都道府県を検索条件にしても、完全一致しないのでうまく探し出すことができない。そこで覚えたいのが部分一致のテクニックだ。
----------
----------
（a）COUNTIF関数を挿入し、範囲に顧客リストの「住所」を、検索条件に「都道府県」を指定する。
（b）そこで検索条件を部分一致に。東京都のセルを“*&と&*”で挟むと、範囲の中から東京都を含むセルを見つけ出してくれる。
【完成】そのまま下にコピー。都道府県ごとの顧客数が計算され、どの地域に顧客が集中しているか、すぐに理解できる。
［8］COUNTIF関数を応用しよう
［7］を応用して、都道府県からさらに市区町村まで掘り下げ、顧客数をカウントしてみよう。特別に新しい関数は必要なし。これまで出てきた関数を応用して、表を作成できる。
（a）基になる顧客リスト。下の表に、探したい都道府県と市区町村を入力する。
（b）住所がまとめて表示されるよう、2つのセルの右にそれぞれを足す計算式（この場合、＝B16&C16）を挿入。
（c）そのままセルを下にコピー。市区町村までの住所がずらりと表示される。
（d）表示された住所の右に、COUNTIF関数を挿入。範囲は顧客リストの住所を、検索条件は住所を部分一致で指定。
（e）計算式を下にコピーすると、部分一致で検索された市区町村レベルの顧客数が出てくる。
【完成】都道府県＋市区町村をあわせてひとつの条件を作るのではなく、COUNTIFS関数を使って複数条件を指定する方法も。
■【膨大なデータから必要な情報だけ欲しい】
［9］フィルタ機能をマスターしよう
リストが膨大なボリュームになったとき、見つけたい項目を探すのは非常に手間だ。そんなときに使いたいのが、フィルタ機能。指定した条件を満たす行だけが表示されるため、簡単に見つけることができる。
（a）これは顧客リストの中から、東京都港区を探すケース。まずは絞り込みたい地域を、住所の右セルに打ち込む。
（b）顧客の住所の右に、COUNTIF関数を挿入。範囲にその顧客の住所を、検索条件に東京都港区を部分一致で指定する。
（c）検索条件が含まれている場合は「1」、ない場合は「0」が表示される。そのまま下へコピー。
（d）東京都港区（1）を選択したあと、「データ」（2）タブにある「フィルター」（3）をクリック。もしくはショートカットで［shift］＋［ctrl］＋［L］。
（e）項目に▽マークがつくので、東京都港区▽（1）をクリック。「0」と「1」が表示されたら、東京都港区を示す「1」（2）だけを選択する。
【完成】COUNTIF関数とフィルタ機能を組み合わせることで、東京都港区の顧客だけが表示されるようになった。
■チーム共有で便利！ 入力ミスを防ぐ裏技
「パソコン」と打つか「PC」と打つかは人によって違うもの。しかしVLOOKUP 関数で「PC」と入力しても、「パソコン」は検索されない。そのようなトラブルを回避するため、ドロップダウンリストという機能を活用し、チームで共有しよう。
（a）まずは何も入力されていない商品エリアを選択する。
（b）「データ」（1）タブの「データの入力規則」（2）をクリック。「元の値」で商品単価一覧の商品（3）を範囲選択する。
（c）商品に出てくる▽マークをクリックして選択。これで入力ミスの心配なし。
----------
モルガン・スタンレーの投資銀行部門で、顧客企業のM＆A、資金調達案件に携わる。現在は、スマートニュースにて財務企画担当。全国でエクセルセミナーや企業研修を行う。著書に『外資系投資銀行のエクセル仕事術』。
----------
（エクセル指導＝熊野 整 文＝鈴木 工 撮影＝竹井俊晴 写真＝平地 勲）
