Excel・PowerPoint使いこなし術

ExcelとPowerPointに関連する使いこなしヒント集

厳選五つ! 実際の仕事に差を生み出す「即戦力のExcel関数」を解説

「関数」を使うと、Excelでできる仕事の幅が大きく広がる。今回はROUND関数、IF関数、SUMIF関数、VLOOKUP関数、WORKDAY関数の五つを中心に、関数の使い方を紹介する。

[2017年 8月10日公開]

関数を知ると何が便利になるのか?

「関数」とは、Excelで定義されている計算式のことだ。「計算式」と言うと、足し算や引き算、ルート、べき乗などの数値計算を思い浮かべるかもしれない。しかし、Excelの関数でできる処理は多岐にわたる。数値計算のほか、日付や時刻の計算、文字列の操作、条件判定、表引きなど、驚くほどたくさんの関数が用意されている。関数を使えるようになると、Excelの用途が何倍にも広がり、1時間かかっていた作業が1分で終わる…といった劇的な効率化が図れるものもある。仕事に差を生むExcel活用のために、マスターしておこう。

関数の基本を押さえる

まずは、関数の基本を知っておこう。関数の処理の材料になるデータのことを「引数」(ひきすう)と呼ぶ。セルに関数を入力するときは、「=関数名」を入力し、続けて丸カッコの中に引数をコンマで区切って入力する。「=」やカッコ、コンマはいずれも半角で入力すること。

関数の書き方
=関数名(引数1, 引数2, ……)

引数の数や、引数に何を指定するかは、関数ごとに決められている。関数の構文通りに入力しないと、正しい結果が出ないので注意しよう。

【ROUND関数】端数を処理する

割引計算や消費税計算をするときに小数点以下に端数が出ることがある。ROUND関数を使うと、数値の端数を四捨五入して、キリのよい値にすることができる。

そんなこと、わざわざ関数を使わなくとも、セルに桁区切りスタイルや通貨スタイルを設定すれば小数点以下を四捨五入できる…そう考える人もいるだろう。しかし、その場合の端数処理は「見た目上のもの」だ。図1を見てほしい。標準価格に割引を適用して販売価格を求めている。この販売価格に「桁区切りスタイル」を適用すると、図2のように販売価格の合計計算が合わなくなる。金額の計算が合わないのは、会社の信用にかかわる大問題だ。

図1 「標準価格×(1-割引率)」を計算して「販売価格」を求めた。

図2 「販売価格」のセルD3~D5に桁区切りスタイルを設定すると、「10,784.8」は「10,785」、「1,927.5」は「1,928」、「12,712.3」は「12,712」という具合に四捨五入できる。しかし、「10,785」と「1,928」の合計が「14,712」と表示され、計算が合わなくなる。

そこで、ROUND関数の出番だ。ROUND関数を使うと、「数値」を指定した「桁数」で四捨五入できる。小数点以下を四捨五入して整数にするには、2番目の引数「桁数」に「0」を指定する。

ROUND関数の書き方
=ROUND(数値, 桁数)

前述の例では、ROUND関数の1番目の引数「数値」に割引計算の式を入れ、2番目の引数「桁数」に「0」を入れて、「販売価格」を求める(図3)。小数点以下の端数がキッチリ四捨五入されるので、合計の計算もピッタリ合う。

ROUND関数の入力例
=ROUND(B3*(1-C3),0)

図3 「販売価格」を求めるときにROUND関数を使って小数点以下を四捨五入してから合計すると、合計値がピッタリ合う。

ROUND関数の仲間に、切り上げ用のROUNDUP関数と切り捨て用のROUNDDOWN関数がある。使い方はROUND関数と同じだ。次表は、各関数で「123.456」という数値を端数処理した場合の結果をまとめたものだ。引数「桁数」に正数を指定すると小数部分の端数処理、負数を指定すると整数部分の端数処理となる。

桁数210-1-2
四捨五入 ROUND(123.456,桁数)123.46123.5123120100
切り上げ ROUNDUP(123.456,桁数)123.46123.5124130200
切り捨て ROUNDDOWN(123.456,桁数)123.45123.4123120100

【IF関数】条件によって表示する値を切り替える

IF関数を使うと、条件が成立する場合と成立しない場合とで、セルに表示する値を切り替えることができる。引数は、「論理式」「真の場合」「偽の場合」の三つ。「論理式」には条件を指定し、「真の場合」には条件が成立する場合に表示する値を、「偽の場合」には条件が成立しない場合に表示する値を指定する。

IF関数の書き方
=IF(論理式, 真の場合, 偽の場合)

図4の表では、年間購入額が10万円以上の顧客の会員種別を「スター」、それ以外の会員種別を「一般」としたい。この場合、IF関数の引数「論理式」に「C3>=100000」、「真の場合」に「"スター"」、「偽の場合」に「"一般"」を指定する。「C3>=100000」は「セルC3の値が100,000以上」を意味する条件だ。「スター」「一般」などの文字列は半角のダブルクォーテーションで囲む。

IF関数の入力例
=IF(C3>=100000,"スター","一般")

図4 セルD3に「=IF(C3>=100000,"スター","一般")」と入力して、セルD8までコピーする。年間購入額が10万円以上の場合に「スター」、そうでない場合に「一般」と表示される。「>=」は、半角の「>」に続けて半角の「=」を入力すればよい。

引数の「論理式」に指定した「>=」は、「比較演算子」と呼ばれる。比較演算子には、次表の種類がある。

比較演算子意味
>=以上
>より大きい
<=以下
<より小さい
=等しい
<>等しくない

次に、年間購入額が10万円以上の場合に「スター」、5万円以上の場合に「A」、それ以外に「B」と表示してみよう(図5)。IF関数を使用して年間購入額が10万円以上かどうかを判定し、この条件が成立する場合に「スター」と表示する。成立しない場合は、もう一つIF関数を使い、年間購入額が5万円以上かどうかを判定すればよい。

IF関数の入力例
=IF(C3>=100000,"スター",IF(C3>=50000,"A","B"))

図5 セルD3に「=IF(C3>=100000,"スター",IF(C3>=50000,"A","B"))」と入力。年間購入額が10万円以上の場合に「スター」、5万円以上の場合に「A」、それ以外の場合に「B」と表示される。

図5では複数の条件を段階的に判定したが、AND関数やOR関数を使用すれば複数の条件を同時に判定することも可能だ。AND関数では、引数に指定した「論理式」が全て成立する場合に全体の条件が成立する。OR関数では、引数に指定した「論理式」の少なくとも一つが成立する場合に全体の条件が成立する。

AND関数の書き方
=AND(論理式1, 論理式2, …)
OR関数の書き方
=OR(論理式1, 論理式2, …)

例えば、図6の表で、検査項目Aが15点以上、かつ、検査項目Bが8点以上の場合に検査結果として「合格」と表示したい。IF関数の引数「論理式」の中にAND関数を組み込み、「AND(B3>=15,C3>=8)」という条件を指定すればよい。なお、「偽の場合」に指定する「""」は何も表示しないことを意味する。

AND関数の入力例
=IF(AND(B3>=15,C3>=8),"合格","")

図6 セルD3に「=IF(AND(B3>=15,C3>=8),"合格","")」と入力。検査項目Aが15点以上、かつ、検査項目Bが8点以上の場合にだけ「合格」と表示される。

反対に、検査項目Aが15点未満、または、検査項目Bが8点未満の場合に検査結果として「不合格」と表示するには、OR関数を組み込む。

OR関数の入力例
=IF(OR(B3<15,C3<8),"不合格","")

図7 セルD3に「=IF(OR(B3<15,C3<8),"不合格","")」と入力。検査項目Aが15点未満、または、検査項目Bが8点未満の場合に「不合格」と表示される。

【SUMIF関数】条件に当てはまるデータを集計する

売上高を合計するときなどに、SUM関数を使用している人は多いだろう。SUM関数は、引数に指定したセルの数値を合計する関数だ。例えば、セルに「=SUM(C3:C12)」と入力すると、セルC3~C12の合計が求められる(図8)。

図8 「=SUM(C3:C12)」と入力すると、セルC3~C12の合計が求められる。

一方、SUMIF関数を使用すると、条件に当てはまるデータだけを合計できる。引数は、「範囲」「検索条件」「合計範囲」の三つだ。引数「範囲」には、条件判定の対象となるセル範囲を指定する。引数「合計範囲」には合計対象の数値が入力されているセル範囲を指定する。

SUMIF関数の書き方
=SUMIF(範囲, 検索条件, 合計範囲)

実際の例を見てみよう(図9)。売上実績表の「社員」欄から「高橋」を検索して、「売上高」欄の数値を合計している。引数「範囲」に「社員」欄のセルB3~B12、「検索条件」に条件が入力されたセルE3、「合計範囲」に「売上高」欄のセルC3~C12を指定する。

SUMIF関数の入力例
=SUMIF(B3:B12,E3,C3:C12)

図9 「=SUMIF(B3:B12,E3,C3:C12)」と入力すると、セルB3~B12(ピンク枠)の範囲からセルE3の値(青枠)が検索され、セルC3~C12(緑枠)のうち、該当の数値が合計される。

通常、数式をコピーすると、数式の中のセル番号が自動でずれる。しかし、図9のSUMIF関数の引数「範囲」と「合計範囲」は、常に固定しておきたい。そのようなときは、セル番号に半角の「$」記号を付けておく(図10)。数式バーで「範囲」の「B3:B12」の部分を選択し、「F4」キーを押すと「$B$3:$B$12」になる。同様に「合計範囲」も「$C$3:$C$12」にしておく。このようなセルの指定方法を「絶対参照」と呼ぶ。絶対参照で指定したセル範囲はコピー時に変化しないので、常に引数「範囲」と「合計範囲」を固定しておける。なお、引数「検索条件」の「E3」は、数式をコピーしたときに順次ずらしたいので「E3」のままでOKだ。

SUMIF関数の入力例
=SUMIF($B$3:$B$12,E3,$C$3:$C$12)

図10 セルF3に入れたSUMIF関数の引数「範囲」と「合計範囲」を絶対参照にしてから、セルF5までコピーすると、各社員の売上合計を計算できる。

ちなみに、条件に合うデータの数を数えたい場合には、COUNTIF関数を使う(図11)。引数は「範囲」と「検索条件」の二つだ。

COUNTIF関数の書き方
=COUNTIF(範囲, 検索条件)
COUNTIF関数の入力例
=COUNTIF($B$3:$B$12,E3)

図11 「=COUNTIF($B$3:$B$12,E3)」と入力すると、セルB3~B12(ピンク枠)の範囲からセルE3の値(青枠)がカウントされる。「高橋」は3件、「野々村」は4件、「小川」は3件の売り上げがあることが分かる。

【VLOOKUP関数】条件に当てはまるデータを表から転記する

図12の表で、注文明細書の「品番」欄に品番を入力すると、商品リストから品番が検索され、対応する商品名が転記されるようにしたい。

図12 注文明細書の「品番」欄に「D102」と入力すると、商品リストから「D102」が検索され、それに対応する商品名が表示される仕組みを作りたい。

このような複雑な処理も、表引き用のVLOOKUP関数を使えば一発だ(図13)。引数は、「検索値」「範囲」「列番号」「検索方法」の四つある。

VLOOKUP関数の書き方
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

引数「検索値」には、検索する品番が入力されているセルE3を指定する。また、引数「範囲」には、検索対象の商品リストのセル範囲A3~C8を指定する。引数「列番号」には、商品リストの何列目からデータを取り出したいのかを数値で指定する。商品名を取り出す場合は「2」、単価を取り出す場合は「3」とすればよい。最後の引数「検索方法」では、完全一致検索する場合は「FALSE」、近似検索する場合は「TRUE」を指定する。今回の例では、「D102」と完全に一致するデータを検索したいので「FALSE」を指定。すると、商品リストの1列目から「D102」が検索され、対応する商品名「ペットシーツ」が転記される。

VLOOKUP関数の入力例
=VLOOKUP(E3,$A$3:$C$8,2,FALSE)

図13 セルF3とセルG3にそれぞれVLOOKUP関数を入力して、商品リストから商品名と単価を転記した。数式のコピーに備えて、2番目の引数「範囲」は絶対参照で指定しておく。

図13のVLOOKUP関数を下の行にコピーする。すると、品番が入力されている場合は商品名と単価が転記されるが、入力されていない場合にエラーとなる(図14)。VLOOKUP関数では、引数「検索値」が空欄の場合に検索ができずにエラーとなるのだ。

図14 セルF3とセルG3に入れたVLOOKUP関数をコピーした。品番を入力していない行に、「#N/A」というエラー記号が表示される。

「検索値」が空欄の場合にエラーが表示されないようにするには、エラー処理用のIFERROR関数を使う(図15)。引数「値」にVLOOKUP関数を指定し、引数「エラーの場合の値」に「""」を指定すると、VLOOKUP関数の結果がエラーの場合に何も表示されなくなる。

IFERROR関数の書き方
=IFERROR(値, エラーの場合の値)
IFERROR関数の入力例
=IFERROR(VLOOKUP(E3,$A$3:$C$8,2,FALSE),"")

図15 IFERROR関数とVLOOKUP関数を組み合わせると、VLOOKUP関数の結果がエラーの場合にエラー記号が表示されなくなる。

【WORKDAY関数】翌営業日を調べる

最後に、「〇日後の営業日」を求めるWORKDAY関数を紹介する。土曜日と日曜日、および指定した休日を除いた営業日を求める関数だ。引数は「開始日」「日数」「祭日」の三つ。引数「開始日」には計算の起点となる日付を指定。引数「日数」には数える日数を指定する。例えば「3」を指定すると「3営業日後」、「-3」を指定すると「3営業日前」を求めることができる。引数「祭日」には休業日の日付を指定する。「祭日」は省略可能で、省略した場合は土日のみを除いた営業日が求められる。

WORKDAY関数の書き方(1)
=WORKDAY(開始日, 日数, 祭日)

ここでは、注文日の3営業日後を納品日として、注文日から納品日を求めてみよう(図16)。WORKDAY関数の引数「開始日」に注文日のセルA3を、「日数」に「3」、「祭日」に休業日を入力したセルD10~D12を指定する。数式をコピーしたときに「祭日」が固定されるように、絶対参照にしておく。

WORKDAY関数の入力例(1)
=WORKDAY(A3,3,$D$10:$D$12)

図16 WORKDAY関数を使用して、「注文日」の3営業日後を求めた。「2017/8/1」の3営業日後は単純に3日後の「2017/8/4」になるが、「2017/8/2」の3営業日後は土曜日と日曜日が挟まれるので「2017/8/7」となる。なお、ここでは日付のセルに「yyyy/m/d(aaa)」というユーザー定義の表示形式を設定して、曜日が表示されるようにしている。

WORKDAY関数では定休日が土日に限定されるが、ほかの曜日を定休日としたいケースもあるだろう。Excel 2010で追加されたWORKDAY.INTL関数を使用すると、定休日の曜日を自由に指定できる(図17)。指定方法は簡単。引数「週末」に、営業日を0、定休日を1として、月曜日から日曜日までを7文字の文字列で指定すればよい。例えば、日曜日と水曜日を定休日とするなら、「"0010001"」と指定する。

WORKDAY関数の書き方(2)
=WORKDAY.INTL(開始日, 日数, 週末, 祭日)
WORKDAY関数の入力例(2)
=WORKDAY.INTL(A3,3,"0010001",$D$10:$D$12)

図17 WORKDAY.INTL関数を使い、日曜日と水曜日を定休日として「注文日」の3営業日後を求めた。図16のWORKDAY関数との違いは、引数「週末」で定休日を「"0010001"」と指定する点だ。

WORKDAY関数は、銀行の引落日などを求めるときにも活躍する。ここでは、引落日を「毎月5日、5日が休日の場合は翌営業日」として求めてみよう。まず、下準備として毎月の4日の日付を入力しておく(図18)。マウス操作で簡単に入力できる。

図18 セルB3に1月4日の日付を入力し、セルの右下角をマウスの右ボタンを押しながらセルB14までドラッグする。表示されるメニューから「連続データ(月単位)」を選ぶと、毎月4日の日付を入力できる。

毎月4日の日付が分かれば、後はWORKDAY関数を使用して、その1営業日後を求めるだけだ(図19)。

図19 WORKDAY関数を使ってセルB3の1営業日後を求め、12月のセルまでコピーする。1月の引落日は5日になるが、2月は5日が日曜日なので、引落日は翌営業日の6日となる。

今回は、実務に役立つ即戦力の関数を厳選して紹介した。Excelには、このほかにもたくさんの関数が用意されている。使える関数を徐々に増やすことで、Excelを真に使いこなすことができる。ぜひExcelの関数で、仕事に差を生み出してほしい。

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をオススメします

マイクロソフトの「Office 365」は、ExcelやPowerPointを始めとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る