エクセルの作業スピードを速めて、時短を実現させたい、とビジネスマンなら誰しもが考えているはず。投資銀行では、ものすごい速さでエクセルの計算をしており、日々、限られた時間の中で最大の成果を出すように努めています。

そこで労力を減らし、作業効率を上げてくれる味方が、自動的に計算や検索作業をしてくれる関数です。

エクセルでは非常にたくさんの関数を使えますが、ビジネスの現場で使うのはその中のごく限られたものだけ。関数の一覧を眺めて、公式を頭に叩き込む必要はありません。「このようなシチュエーションでは、この関数が使える」とケース別に理解し、いくつかの実践的な関数だけを確実に使いこなせればいいのです。

そして最後に強調したいのは、エクセルはスピードも大事ですが、それ以上に大事なのは計算ミスがないということ。もし関数の利用によって作業時間を軽減できたら、余った分の時間は計算のチェックに費やすのが理想です。

【自動で計算してくれる見積書を作りたい】

[1]VLOOKUP関数をマスターしよう

商品名を打ち込めば、自動的に単価が出てくる。そんな理想的な見積書は、VLOOKUP関数を使うことで可能になる。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関数。指定した範囲の中から、検索条件にあったセルの個数を数える関数だ。

COUNTIF関数=COUNTIF(範囲,検索条件)

(a)販売数にCOUNTIF関数を挿入。範囲には、販売履歴データの商品すべてを選択する。$をつけて、絶対参照にするのを忘れないようにしよう。

【完成】関数の検索条件には、販売数を数えたい商品(この場合PC)のセルを選択。一番上の行で計算式が完成したら、そのまま下にコピー。COUNTIF関数が範囲の中から数えてくれるので、各商品の販売数を一目で把握することができる。

[4]SUMIF関数を使いこなそう

指定範囲の数値を全部足してくれるメジャーな関数SUM関数に、条件を表すIFがついたのが、SUMIF関数。条件にあった数字だけを合計してくれるので、商品別の売り上げを計算するときなどに使える。

SUMIF関数=SUMIF(範囲,検索条件[, 合計範囲])

(a)商品別データの「売上」にSUMIF関数を挿入。範囲として販売履歴データの商品すべてを、検索条件に探したい商品のセルを入力する。

【完成】関数の[合計範囲]は、合計したい値が入力されているセルの範囲のこと。この場合、「売上」のセル全体を選択する。計算式を下へとコピーすると、商品別の売り上げが出てくる。

【月別に売り上げを比較したい】

[5]COUNTIFS関数をマスターしよう

COUNTIF関数の条件は1つだけだが、それに「S」のついたCOUNTIFS関数は、複数の条件指定が可能。複数条件を満たしたデータの個数だけを数えてくれる。それに指定した日付の月だけを表示するMONTH関数を組み合わせることで、さらに便利に。

MONTH関数=MONTH(シリアル値)
COUNTIFS関数=COUNTIFS(検索条件範囲1,検索条件1,検索範囲2,…)

(a)販売履歴データの中から、6月の商品別データだけを計算する。下準備として「売上日」の右に「売上月」の列を作る。

(b)「売上月」のセルにMONTH関数を挿入し、「売上日」のセルを指定。4月 日であれば、「4」の数字のみが表示される。

(c)そのまま下にコピーすると月だけが並ぶ。なおシリアル値とは日付や時刻を扱う数値で、「1月」は「1」と表示。

(d)商品別データの下に月数を入力し、PCの販売数にCOUNTIFS関数を挿入。最初の条件として、範囲に商品リストを、検索条件にPCのセルを選択。その次に2つ目の条件として、範囲に「売上月」を、検索条件に「6」を選択する。

【完成】「商品がPC」「売上月が6月」という2つの条件にあった「1」の数字が出てくる。計算式をそのまま下にコピーして完成。

[6]SUMIFS関数をマスターしよう

SUMIF関数を覚えたら、複数条件を満たすデータだけを合計してくれるSUMIFS関数も使ってみよう。左式にある「合計対象範囲」とは難しそうだが、売り上げに関する合計を出したいのであれば、売り上げ全体を指定すると考えればよい。

SUMIFS関数=SUMIFS(合計対象範囲,条件範囲1,条件1,…)

(a)「商品がPC」「売上月が6月」という2つの条件を満たす売り上げを計算する。

(b)PC売上のセルにCOUNTIFS関数を挿入。スキル24と同じ条件範囲と検索条件を選択し、合計対象範囲として「売上」を選ぶ。

【完成】2つの条件を満たした合計の値「150000」が計算される。このセルを下にコピーすれば、できあがり。

【顧客リストを都道府県別に集計したい】

[7]COUNTIF関数を部分一致で使おう

顧客リストの中から特定の住所を数えたいとき、使えるのがCOUNTIF関数。ただし番地や丁目が記されているセルを範囲に指定し、都道府県を検索条件にしても、完全一致しないのでうまく探し出すことができない。そこで覚えたいのが部分一致のテクニックだ。

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、資金調達案件に携わる。現在は、スマートニュースにて財務企画担当。全国でエクセルセミナーや企業研修を行う。著書に『外資系投資銀行のエクセル仕事術』。