Excelの関数の使い方と便利な関数紹介

• ナイス！：

  14

• 閲覧数：29737
• 付箋（アドバイス）指数中→

難しい関数を見て見ぬふりしてきた方のための知恵ノートです

Excelにはたくさんの関数があり、使いこなせれば何でもできちゃいます。

使いこなせれば。使いこなせねぇよ…。

知恵袋でもよく回答で使われている関数を解説します。

これからのExcel生活に役立ててください。

※Excelのバージョンなどによって使用できる関数が異なります

関数の引数(ひきすう)と戻り値(もどりち)

いきなり聞き慣れない言葉ですが簡単なので諦めないでください。

関数は「関数名(第1引数,第2引数,…)」と書くと引数に応じて戻り値を返します。

例.

NOW():引数なし、現在時刻を返す関数

SUM(1,2,3,4,5):引数たくさん。引数の合計を返す関数

引数にはセル番地(A1、C1:C3)や関数を書く事ができます。「SUM(1,SUM(2,3))」の場合内側の関数から処理していき1+(2+3)の結果の6を返します。

TRUEとFALSE

関数がTRUE(はい)、FALSE(いいえ)という戻り値を返すことがあります。

例.ISBLANK():指定したセルが空白ならTRUE、空白でないとFALSEを返す関数

便利な関数紹介

これから紹介する関数を一通り使えるようになると、やりたいことが大体何でもできるようになります。Excel関数マスターを名乗っていいと思う。

基本

四則演算(しそくえんざん)

=1+1

=2-1

=2*2

=4/2

上から順に足し算、引き算、掛け算、割り算。それだけ。

文字列

=LEFT("abcde",3)

""で囲むと文字列(0個以上の文字の集まり)として扱われます。囲わないと「～という名前のセル」として扱われます。例の""を外すと「第1引数がセルabcdeの内容、第2引数が3のLEFT関数」という意味になります。

文字列連結(れんけつ)

= "a" & "b"

&は左側と右側の文字列をくっつけます。それだけ。

比較

それぞれ条件を満たすとTRUE、満たさないとFALSEを返します。

= 1 > 2 :左側の方が大きい

= 1 < 2 :右側の方が大きい

= 1 >= 2 :左側が右側以上

= 1 <= 2 :左側が右側以下

= 1 = 2 :左側と右側が同じ

= 1 <> 2 :左側と右側が違う

優先

=1+2*3

=(1+2)*3

基本的に足し算や引き算より掛け算や割り算が先に行われます。1つ目の例だと2*3が先に計算され1+6=7になります。2つ目のように()で囲むと優先的に計算します。1+2が先に計算され3*3=9になります。

セルの参照

「=A1」と書くとセルA1のデータを表示します。「A1」という文字列が表示されるわけではありません。文字列として表示したい場合は「="A1"」または「A1」と書きます。セルを指定する書き方はこの他にもいくつかあります。

範囲を指定する

=COUNT(A1:B2)

引数に範囲を指定する関数ではこのように書きます。

行・列全体を範囲として指定する

=COUNT(1:1)

=COUNT(A:C)

で「1～1行目(つまり1行目)」「A～C列」という書き方になります。

別シートのセルを指定する

=Sheet2!A1

のように「シート名!セル名」と書くことで別シートのセルを参照します。

別ブックのセルを指定する

='C:\Users\excelkansuu\Desktop\[Book1.xlsx]Sheet1'!A1

のように「'ブックのある場所\[ブックの名前]シート名'!」を付けてセルを指定します。ちょっとしたセル参照でも長くなって読みづらくなります。

絶対参照

オートフィルなどを行うと移動量に応じてセル番地も増減します。

=$A$1

のように左に$のつけると移動しても増減しなくなります。

B1を「=A1+A2」としてC1にオートフィルすると右に1移動したため列が1増えてC1は「=B1+B2」になります。B1を「=$A1+A2」としていた場合、「A1」の「A」がオートフィルによって増減せず「=$A1+B2」になります。

後で表を修正する場合に数式が崩れないよう適切に設定しておきましょう。

TRUEとFALSEを扱う関数

IF関数

=IF(条件,条件がTRUEの場合,FALSEの場合)

条件がTRUEの場合は第2引数、FALSEの第3引数を返します。

=IF(条件1,IF(条件2,"成功","失敗A"),"失敗B")

のように複数条件も検査できます。

AND関数、OR関数

=AND(第1引数,第2引数,…)

=OR(第1引数,第2引数,…)

AND関数は引数が全てTRUEだとTRUE、それ以外はFALSEを返します。

OR関数は引数が1つでもTRUEだとTRUE、それ以外はFALSEを返します。

=AND(1,0,1):FALSE

=AND(1,1):TRUE

=OR(1,0,1):TRUE

=OR(0,0,0,0):FALSE

=IF(条件1,"",IF(条件2,"","失敗"))

が

=IF(OR(条件1,条件2),"","失敗")

と書ける素敵関数。条件がたくさんあってもIF1回で済みます。

NOT関数

=NOT(TRUE)

引数のTRUE/FALSEを反転します。「～でない場合」を書くのに使います。

数値を扱う関数

RAND関数

=RAND()

0～1までのランダムな値を返します。くじ引きなどで使います。といっても出てくるのが0.2372だとか0.567みたいな小数なので工夫しないと使えません。

RANDBETWEEN関数というこれより使い勝手のいい関数もあります。指定した上限と下限の範囲でランダムな値を返します。
http://office.microsoft.com/ja-jp/excel-help/HP005209230.aspx

ROUND関数、ROUNDUP関数、ROUNDDOWN関数

=ROUND(1.4,0)

=ROUND(1.5,0)

=ROUNDUP(1.4,0)

=ROUNDDOWN(1.7,0)

第1引数の値を第2引数の桁で四捨五入したり切り上げたり切り捨てたりします。

ROW関数、COLUMN関数

=ROW(A3)
=COLUMN()

ROW関数は何行目か、COLUMN関数は何列目かを数値で返します。セルを指定するとそのセルについての値を返し、引数がない場合は現在地の値を返します。ROW(A3)だと3です。これ単体では「そんなの端に書いてあるだろ」って話ですが。

MOD関数

=MOD(10,3)

第1引数を第2引数で割った余りを返します。パッと見では使い道がよくわかりませんね。次のようにして使います。

=IF(MOD(ROW(),3),"","a")

この数式をA列にびっしり書くと2行毎にaを表示します。このように「～回毎に」という書き方をする場合などに使います。

COUNT関数、COUNTA関数、COUNTBLANK関数

=COUNT(A1:A5)

=COUNTA(A1:A5)

=COUNTBLANK(A1:A5)

COUNT関数は範囲内の数値データの数を数えます。COUNTA関数は範囲内のデータの数を数えます。COUNTBLANK関数は範囲内の空白のセルの数を数えます。

※""は空白ではなく長さ0の文字列として扱われます

COUNTIF関数

=COUNTIF(A1:A5,"男性")

=COUNTIF(A1:A5,"<2")

COUNTIF関数は第1引数の範囲から第2引数の条件に合致するデータの数を数えます。上の書き方だと="男性"という意味になり、下の書き方のように単純な=以外の比較もできます。

文字列を扱う関数

LEN関数、LENB関数

=LEN("あいう")

=LENB("あいう")

LEN関数は文字列の長さ、LENB関数は文字列のバイト数を返します。メガバイトとかテラバイトとかのあのバイト。半角文字が1バイト、全角文字が2バイト。

=LENB("あいaう") - LEN("あいaう")

とすれば文字列の中の全角文字の数を数えられます。

LEFT関数、RIGHT関数、MID関数

=LEFT("abcde",3)

=RIGHT(A1,2)

LEFT関数は第1引数の左側から第2引数の文字数分文字を取り出します。RIGHTは右側から取り出します。例のRIGHT関数はセルA1の内容の右から2文字です。

=MID("abcde",3,2)

MID関数は第1引数の左から数えて第2引数文字目から第3引数の文字数文字を取り出して返します。左から3文字目はcでそこから2文字なので戻り値はcdです。

=LEFT(RIGHT("abcde",3),2)

と書いてもいいです。「「abcdeの右側3文字」の左から2文字」です。「後ろから～文字目」という書き方ができるのでときどき使います。

SEARCH関数

=SEARCH("検索ワード","あいうえお検索ワードかきくけこ",2)

第2引数の文字列の中から第1引数の検索ワードを探し出し、出現位置を返します。第3引数は省略可能で、検索開始位置を指定できます。例では第3引数に6などと書くと見つからなくなります。

日付を扱う関数

TODAY関数、NOW関数

=TODAY()

=NOW()

今日が1900/1/1から何日目かを返します。セルの書式設定(Ctrlキーを押しながら1キーを押す)の表示形式タブで「日付」にすると日付形式で表示できます。「標準」形式だと数万の数値が表示されます。昨日はTODAY()-1、明日はTODAY()+1。

NOW関数はTODAY()に加えて時分秒まで返します。

YEAR関数、MONTH関数、DAY関数

=YEAR(TODAY())

=MONTH(TODAY())

=DAY(TODAY())

引数で指定された値を年月日に計算し直します。TODAY関数で今日を表す数万の数値が返ってきて、それを年月日に直しています。

HOUR関数、MINUTE関数、SECOND関数

=HOUR(NOW())

=MINUTE(NOW())

=SECOND(NOW())

時分秒への計算もできます。

Excelの日付計算については以下の知恵ノートにまとめています。

http://note.chiebukuro.yahoo.co.jp/detail/n186989

使いこなせるとかっこいい関数

VLOOKUP関数

=VLOOKUP("a",A6:C8,3,FALSE)

第2引数の範囲の一番左の列から第1引数の値を探し、見つけたらその行の第3引数列目の値を返します。

の表で
=VLOOKUP("山田",E3:H5,2,FALSE)
はE3:E5の中で"山田"を発見してその行の2列目「16」を返します。第3引数が1なら「山田」、3なら「女」を返します。第4引数がTRUEだと、第1引数が数値で、見つからなかった場合に一番近い値を探します。FALSEの場合完全一致の行が見つからないとエラーを返します。

※見つからなかったら、という書き方は
=IF(ISERROR(VLOOKUP(～)),"見つからなかった",VLOOKUP(～))
です。

DATEDIF関数

=DATEDIF(開始日,終了日,"Y")

開始日から終了日までの期間を第3引数の単位で返します。第3引数は
Y:年数 M:月数 D:日数
の他に
YM:1年未満の月数 MD:1月未満の日数 YD:1年未満の日数
も指定できます。

=DATEDIF(A1,B1,"Y") & "年と" & DATEDIF(A1,B1,"YD") & "日"

第3引数は文字列で指定するため""で囲みます。他のセルの内容に応じて変えることも可能で「=DATEDIF(A1,B1,C1)」とすればセルC1にYと入力すれば年数が、Mを入力すれば月数がわかる、というような使い方も可能。誕生日計算や期間計算が簡単にできて便利です。

SUMPRODUCT関数

=SUMPRODUCT(配列1,配列2,…)

配列同士を掛けて、その合計を返します。たとえば以下の表で「=SUMPRODUCT(A1:A3,B1:B3)」を実行すると、(100×3)+(200×3)+(300×1)の結果1200を返します。配列は2個に限らずたくさん指定できます。

SUMPRODUCTの魅力はこれでまだ半分。同じ表で「=SUMPRODUCT(A1:A3=200,B1:B3=3)」を実行すると、配列1と200を比べて同じならTRUEを返します。A2だけTRUEでB列はB1とB2がTRUEです。他はFALSEです。TRUEは1でFALSEは0だと少し前にお話ししました。つまりこんな表になります。

(0×1)+(1×1)+(0×0)=1ということで「A列が200でB列が3」という複数条件に合致したデータの個数を数えられます。「国語が合格で算数が不合格の人数」などといった集計で使えます。

練習・確認問題

何となくわかった気になったらやってみてください。紹介した関数だけでできます。パズルです。

Q1.RAND関数を使って20%の確率で○を、80%の確率で×を表示してください。

Q2.セルA1に「abああcい」のように全角文字と半角文字が混ざった文字列が入力されています。この中の半角文字の個数を表示してください。

Q3.来月が何月か数字で表示してください。

Q4.セルA1が4以上9未満なら○、マイナスなら×、他は□を表示してください。

Q5.以下の表でセルA1に会員番号を入力したとき、会員が男性なら"さん"付けの名字を、その他の場合は空白を表示してください(名前と名字の間には必ず半角スペースが1つはさまっています) ※この調子で1000件の会員データがある

回答例

Q1.=IF(RAND()<0.2,"○","×")

Q2.=(LEN(A1)*2)-LENB(A1)

Q3.=IF(MONTH(TODAY())<>12,MONTH(TODAY())+1,1)

Q4.=IF(AND(3<A1,A1<9),"○",IF(A1<0,"×","□"))

Q5.=IF(VLOOKUP(A1,B2:D1001,3,FALSE)="男",LEFT(VLOOKUP(A1,B2:D1001,2,FALSE),SEARCH(" ",VLOOKUP(A1,B2:D1001,2,FALSE))-1)&"さん","")

Q5になって突然長くなりましたが

VLOOKUP(A1,B2:D1001,3,FALSE)で性別

VLOOKUP(A1,B2:D1001,2,FALSE)で名前

を取り出しているだけなので

=IF(性別="男",LEFT(名前,SEARCH(" ",名前)-1)&"さん","")ということです。

終わり

本当にただのパズルですね。紹介した関数だけでも色々できちゃいます。

もっと専門的な関数ないの?と思ったらYahoo!やGoogleで検索すればいいです。

「Excel2007 数える」のように「使っているExcelのバージョン やりたいこと」を検索すれば大体何とかなります。

このノートのライターが設定した関連知恵ノート

• Excelを使いやすくする方法
• 使いやすいExcelファイルの作り方
• パソコンのトラブルを"自力で"解決する方法

この知恵ノートのライター

グレード：4-3

その他の知恵ノート（53件）

ファイル名をメモとして使う方法

Excelの日付計算

楽して授業を簡単にする方法

Yahoo!知恵袋で回答をもらいや...

無駄な買い物の減らし方

[ノート一覧]