【Excel入門】【関数】IFERROR
この記事では、IFERROR（イフエラー）関数の使い方を学びます。
IFERROR関数の構文・解説
まず、IFERROR関数の構文を見ていきます。
構文
=IFERROR(値, エラー時の値)
引数
- 値：セルや数式を指定する
- エラー時の値：値がエラーだったときに表示する値を指定する
解説
IFERROR関数は、引数の『値』に指定したデータがエラーかどうかを判定して、エラーの場合に引数で指定した『エラー時の値』を返します。エラーでなければ、『値』そのままのデータを表示します。
※IFERROR関数はExcel2007年以降から利用できます。
ポイント
IFが付いているので、条件分岐をする関数。
ERRORが付いているので、エラーのときだけ条件分岐をする。
エラーの時は関数の処理をして、引数で指定した『エラー時の値』を返す。
エラー以外の時はそのままの値を返す。
IFERROR関数の使用例
実際にIFERROR関数を利用して、使い方を学んでいきましょう。
エラーの場合に特定の値を返すIFERROR関数
この表では達成率の行に売上/達成率の式を入れて、%の書式で達成率を表示するようにしています。
しかし、4月は「臨時休業」と文字列が入力されて、 エラー「#VALUE!」が表示されています。
エラーの場合に空白を返すには、『=IFERROR(E3/E2, "")』を入力すれば空白が返ります。
もしも、「計算不可」という文字列を返す場合は『=IFERROR(E3/E2, "計算不可")と入力すれば、「計算不可」を返します。
今回は、エラーが出たのでIFERROR関数を使って特定の値を返すように数式を変更しましたが、それならエラーをDeleteしたり、ベタ打ちで計算不可や0%などを入力したほうがいいですよね。
実際にIFERROR関数を使うケースは、その数式にエラーが出る可能性があり、エラーの場合にエラーを非表示にしたい、特定の値を返したいときです。
次はVLOOKUP関数との組み合わせで実務でも利用するケースを学びたいと思います。
VLOOKUP関数とIFERROR関数の組み合わせ
受注表の製品名と価格を、受注表に入力したコードを検索値としてコード一覧から取得するケースを考えてみましょう。
VLOOKUP関数で「コード」から「製品名」・「価格」を取得するようにしています。
そして、「合計」列も「=価格*数量」の式をあらかじめ入力しています。
コードが未入力の場合、#N/A(ノー・アサイン)のエラーが表示されてしまいます。
※「#N/A」は参照先のセルに値がない場合に表示されます。
このままでは見苦しいので、IFERROR関数を使ってエラー時は何も表示しないように設定したいと思います。
受注表の「製品名」列、「価格」列をIFERROR関数を使って、エラーのときは「""」何も表示しないように指定しました。
これでスッキリしましたね。
式が長くなり、難しそうに見えますが、引数に着目して考えると簡単です。
=IFERROR(VLOOKUP($A3,$G$3:$I$6,2),"")
IFERROR関数の引数は2つ。1つ目は「値」。もう一つは「エラー時の値」でした。
今回の場合は、『VLOOKUP($A3,$G$3:$I$6,2)』が「値」、『""』が「エラー時の値」です。
VLOOKUPの式でエラーが表示された場合、「""」を返す。という式になっています。
まだ、「合計」列に「#VALUE」(バリュー)エラーが表示されています。
※「#VALUE」は引数が正しくない場合に表示されます。
合計列もIFERROR関数を使って、エラー時は何も表示しないように変更したいと思います。
「合計」列については、IFERROR(価格*数量,"")としてました。
＝IFEEROR(C3*D3,"")
これでエラーのときは空白セルとして表示されるので、スッキリしましたね。
今回はIFERROR関数について紹介しました。
お読みいただきありがとうございました。
【Excel入門】【関数】VLOOKUP
この記事では、VLOOKUP関数の使い方を学びます。
VLOOKUP関数の構文・解説
まず、VLOOKUP関数の構文を見ていきます。
構文
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
引数
- 検索値：『範囲』から検索する値
- 範囲：検索する値と表示するデータを含むセル範囲
- 列番号：『範囲』の中で表示したい列
- 検索方法：検索値が範囲の中に見つからない場合にエラーと近似値のどちらを表示するかを指定
VLOOKUP関数の検索方法について
検索方法に「True」もしくは「1」もしくは「省略」を指定すると、検索値が見つからない場合に検索値未満で最も大きい値が返されます。
※範囲の左端の列のデータを昇順に並べ替える必要があります。
検索方法「False」もしくは「0」を指定すると、検索値に完全一致する値だけが検索されます。見つからない場合は、エラーの「#N/A」が返されます。
検索方法については、「True」「1」「省略」の近似値検索を使うことは、ほぼほぼありませんので、VLOOKUP関数では「False」「0」で完全一致検索をすると覚えておけばいいと思います。
また、「False」を指定するよりも「0」を指定する人の方が多いので、検索方法は「0」を指定して、完全一致検索と覚えておいてもいいかもしれません。
解説
VLOOKUP関数は、検索値が範囲にある場合は、範囲の中で表示したい列の値を表示します。
また、検索値が範囲に存在しているかどうか見ることもできます。
解説だけでは分かりずらいので、いくつかの使用例を書きますので、実際にVLOOKUP関数を使って覚えましょう。
VLOOKUP関数の使用例
VLOOKUP関数の引数を考える
受注表のコード列にコードを入力したら、品名をコード表から取得して返すパターンを考えたいと思います。
検索値は「受注表」のコード列のセルに入力される値です。「受注表」のコード列に入力された値をもとに検索をするので、「A4」、「A5」、「A6」・・・・となっています。
範囲は検索値を探して、値を返す範囲ですので「受注表」のコード列「101」が入力されている「G4」から「単価」列の「200」が入力されている「I7」までを範囲としました。
※この例の場合は、検索値のコードから品名を返すので、コードと品名が含まれる範囲「101」の「G4」から「消しゴム」の「H7」まででも良いです。
列番号は検索値が範囲の中にある場合、合致したデータから何列目の値を返すかを指定するので、「2」を指定します。
受注表は「コード」、「品名」と並んでいるのため、2列目の品名を返すために「2」を指定します。
検索方法は「0」を指定。間違えたコードを入力した場合に近似値の値を返さないために完全一致の検索を指定しています。
受注表のコード列にコードを入力すると、品名を返します。
検索するコードの品名・単価をコード表からVLOOKUP関数で返す
今回は品名だけでなく、単価も返すようにしています。
品名列には「=VLOOKUP($A4,Sheet2!$A$1:$C$6,2,0)」をコピー＆ペーストして入力しています。
コピー＆ペーストで参照先がずれないように、絶対参照(＄)を使っています。
単価列には「=VLOOKUP($A4,Sheet2!$A$1:$C$6,3,0)」をコピー＆ペーストして入力しています。
以下がSheet2のコード表です。
VLOOKUPの引数を見てみましょう。
品名は「=VLOOKUP($A4,Sheet2!$A$1:$C$6,2,0)」、単価は「=VLOOKUP($A4,Sheet2!$A$1:$C$6,3,0)」で値を返しています。
検索値は「$A4」。上図の手入力したコード「102」です。セルでいうと「A4セル」です。
範囲は「Sheet2!$A$1:$C$6」。コード表を記入したシート2のA1：C6セルです。
列番号は品名が「2」、単価は「3」です。検索値と一致する値がある行の品名は2列目、単価は3列目の値を返します。
検索方法は完全一致の「0」。
なので、A4セルの検索値と完全一致するコード表の行の2列目の値、3列目の値を返しています。
コードを入力していない「品名」、「単価」は「#N/A」のエラー値ですが、コードを入力すると品名と単価が表示されるようになっています。
ただし、「#N/A」が表示されていると不格好なので、IFERROR関数を使って、エラーが表示されているとき、エラーを表示しないようにすることができます。
IFERROR関数を使ってエラーを非表示にする
品名には「=IFERROR(VLOOKUP($A4,Sheet2!$A$1:$C$6,2,0),"")」
単価には「=IFERROR(VLOOKUP($A4,Sheet2!$A$1:$C$6,2,0),"")」をコピー&ペーストすることでエラーが表示されなくなりました。
VLOOKUP関数の式をIFERROR関数の()で囲んで、エラーになった時に何も表示させない「""」を指定しています。
IFERROR関数については、後日記事にしたいと思います。
VLOOKUP関数を使って「検索値」の存在確認をする
VLOOKUP関数は、検索値が存在するかどうかを調べるために使うこともあります。
※COUNTIF関数などでも存在確認はできます。
今回の例では、システムの移行PCの中に、すでにシステム移行済みPCが含まれているかどうかを確認したいと思います。
A列が移行対象のPC。B列にすでに移行済みPC。C列はVLOOKUP関数を使って、移行対象PCの中に移行済みPCが含まれるかどうかを調べています。
=VLOOKUP(B2,A:A,1,0)
検索値「B2」の「A102」が範囲「A：A」(A列)に存在すれば1列目の移行PC名を返すようにしています。
検索方法は「0」の完全一致です。
もし、存在しなければ「＃N/A」のエラーが返ってきます。
存在すればPC名が返ってきて、存在しなければエラーが返ってきます。
今回の例ではデータが少ないので一目瞭然ですが、データが膨大な時はVLOOKUP関数をコピー&ペーストしたあとにフィルタを使って、エラーを除外すればすでにシステム移行済みのPCを確認することができます。
注意としては、VLOOKUP関数を沢山使うとExcelが非常に重たくなります。
VLOOKUP関数で値を返した後は、コピー&ペーストの値貼り付けを使って、返ってきた結果をVLOOKUP関数の数式ではなく、値として貼り付けてフィルタなどを行うほうが良いです 。
【VBAエキスパートの勉強】ExcelVBAベーシック ～5.VBAの構文：オブジェクト式 オブジェクト.メソッド 引数 := 値～
前回の続きでVBAの構文 オブジェクト式について学びます。
前回は2つあるオブジェクト式の内、『対象.状態 ＝ 値』（オブジェクト.プロパティ = 値) について、勉強しました。
今回は『対象.命令』『対象.命令 オプション := 値』のオブジェクト式について、学びます。
オブジェクト式 『対象.命令』『対象.命令 オプション := 値』
前回の『オブジェクト.プロパティ = 値』では、対象のプロパティ(状態や様子)を設定、操作しました。
今回の『対象.命令』『対象.命令 オプション := 値』は対象に対しての命令。シートを追加するなどのアクションを起こさせます。
なお、この命令は『メソッド』と呼びます。
シートの追加・削除などのように、オブジェクトに対して、操作を起こさせる命令をVBAではメソッドと呼びます。
そして、メソッドの実行に指定するオプションを『引数(ひきすう)』と言います。
※引数の読みは、「ひきすう」です。
実際にVBAのコードを書いて、動かしてみたいと思います。
対象.命令（オブジェクト.メソッド）のコード例
Sub sampleAdd()
Worksheets.Add
Worksheets(1).Delete
End Sub
Worksheets.Addでは、Worksheetsがオブジェクト、Addがメソッド。
Worksheets(1).Deleteでは、Worksheets(1)がオブジェクト、Deleteがメソッド。
Worksheets.Addで一番左側にワークシートを追加し、Worksheets(1).Deleteで一番左側のワークシートを削除します。
対象.命令 オプション := 値（オブジェクト.メソッド 引数 := 値）のコード例
次に引数を与える場合の例です。
引数によって、メソッドに対してより詳細な指示を与えることができます。
例えば、複数のワークシートがある場合に右端(最後尾)にワークシートを追加する場合は以下のようにコードを記述します。
Sub sample2()
Worksheets.Add after:=Worksheets(Worksheets.Count)
End Sub
引数『after』にWorksheets(Worksheets(Worksheets.Count))を指定することで、常に最後尾にシートを追加します。
Worksheets(1)が左端のシートです。Worksheets.Count(プロパティ)でワークシートの数を取得して、Worksheets(Worksheets.Count)で最後尾のワークシートを意味します。
『after』ですので、最後尾のさらに後(左側)にシートを追加します。
次は、引数を使ってアクティブセルを削除した際に左方向・上方向にシフトするという詳細な指示を与えた例です。
Sub sample3()
ActiveCell.Delete
End Sub
引数無しの「オブジェクト.メソッド」の構文。セルを削除して、上方向にセルがシフトします。
Sub sample4()
ActiveCell.Delete Shift:=xlToLeft
End Sub
引数『Shift』に「xlToLeft」を指定することで、アクティブセルの削除後左方向にセルがシフトします。
Sub sample5()
ActiveCell.Delete Shift:=xlUp
End Sub
上方向にシフトさせるには、xlUpを指定します。
複数の引数を指定する場合
ひとつのメソッドで複数の引数を指定する場合は、引数を「,(カンマ」)」で区切って指定します。
オブジェクト.メソッド 引数1 := 値, 引数2 := 値, 引数3 := 値
引数名の省略
メソッドに引数を指定する場合、引数名を省略することが可能です。
Sub sample4()
ActiveCell.Delete Shift:=xlToLeft
End Sub
上記コードはしてのように引数名「Shift」を省略することができます。
Sub sample4()
ActiveCell.Delete xlToLeft
End Sub
ただし、複数の引数がある場合は、わかりやすいように引数名を記載する方が良いです。
後からコードをみて可読性が高いこと、わかりやすいことが重要です。
【Excel入門】【関数】IFの使い方
今回はExcelのIF関数についてです。
IF関数の構文・解説
構文
=IF(論理式,真の場合,偽の場合)
引数
- 論理式：結果を分けるための条件
- 真の場合：条件にあうときの処理
- 偽の場合：条件にあわないときの処理
解説
IF関数は条件によって異なる処理をおこないたいときに使います。
引数の論理式の条件で真の場合の処理、偽の場合の処理に分岐させます。
IF関数の使用例
IF関数の使用例をいくつか見ていきたいと思います。
2つに条件分岐するIF関数の例
以下の例は、合計の値が100を超える場合は「優」、100以下の場合は「劣」と
条件により、『評価』列に「優」・「劣」を表示させるものです。
3つに条件分岐するIF関数の例
上の例では優・劣の2つに条件分岐しましたが、今回は150以上が「A」、100以上が「B」、100未満が「C」と『評価』列に表示するようにIF関数を使いたいと思います。
=IF(E3>=150,"A",IF(E3>=100,"B","C"))
※山田二郎の評価はE4、山田三郎の評価はE5がIF文の条件に入ります。
今回はまず、「E3>=150」という条件で真の場合は「A」、
偽の場合はさらにIF関数で「E3>=100」という条件を使って、
真の場合は「B」、偽の場合は「C」というように分岐させて処理をしています。
上記のようにIF関数の引数にIF関数を使うことを「ネスト」や「入れ子」と言います。
もう少し詳しく言うと、IF関数だけでなくても、関数の引数に関数を利用することを「ネスト」・「入れ子」と言います。
【Excel入門】【関数】RANK.EQ (ランクイコール)の使い方
どんな企業でも使っている表計算ソフトの『Excel』を使いこなして、自分の価値を高めようというシリーズです。
本日は『RANK.EQ (ランクイコール)』の使い方。
順位は他の方法でも求めることができるので、こんな関数もあるのかぁと頭の片隅で覚えておけばいいと思います。
RANK.EQ関数の式と解説
関数式
=RANK.EQ(数値,参照,順序)
3つの引数
数値：順位を調べるセル
参照：順位を調べるセル範囲 ※絶対参照に気を付けましょう。
順序：降順の場合「0」、昇順の場合「1」
解説
RANK.EQ関数は順位を調べたいときに使う関数です。
Excel2007以前は順位を求める関数としてRANK関数がありましたが、Excel2010からRANK.EQ関数に変わりました。
Excel2007以前で順位を求める→RANK関数
Excel2010以降で順位を求める→RANK.EQ関数
Excel2007以前をこれから使うことは少ないので、RANK.EQ関数を覚えておけばいいかと思います。
RANK.EQ関数の使用例
以下のような営業成績表があった場合の使用例。
合計列はSUM関数で求めます。
順位列は、RANK.EQ関数で求めます。
山田太郎の順位のセルの関数式は
『=RANK.EQ(E19,$E$19:$E$23,0)』となっています。
一つ目の引数「数値」は「E19」。2800が入力されているセル。
2つ目の引数「参照」は「$E$19:$E$23」。山田太郎～山田五郎までの合計値が入力されたセル範囲です。注意点は絶対参照になっていること。
3つめの引数「順序」は「0」。数値が大きいほうが1位なので、降順の「0」になっています。
同率順位の処理・同率順位をなくしたい
例では山田太郎と山田次郎が同率2位となりました。
同じ数値なので同率順位になるのは仕方がないことです。
同率順位をなくしたい場合は、どこかの値の優先度を高めて順位を求める方法があります。
例の場合では、6月に全他の成績が下がっています。同率の場合は6月に良い成績を上げた方が、上位にすると決めます。
評価用として新しい列を作成。『合計×1000＋優先度の高い6月の営業成績』の値を入力。
順位についても、RANK.EQ関数で評価用の列を引数として求めました。
そうすることで、同率順位をなくすことができます。
「評価用」列にCONCATENATE関数を使う
評価用列に『合計×1000＋優先度の高い6月の営業成績』の計算式を入力する以外の方法をご紹介します。
CONCATENATE関数、文字列を結合する関数です。
関数式：＝CONCATENATE(文字列1,文字列2,文字列3,文字列4,・・・・・)
※今後は同じようなCONTACT関数を覚えておいた方がいいかもしれませんが、今回はCONCATENATE関数で案内します。
山田太郎の順位列には、「=RANK.EQ(G33,$G$33:$G$37,0)」と評価用の値から順位を求める式が入っています。
そして、山田太郎の評価用列には「=CONCATENATE(E33,D33)」と山田太郎の合計と6月の営業成績を文字列結合する式が入っています。
エラーとなっていますね。。。。。
それはCONCATENATE関数が文字列結合の関数だからです。
評価用の値を文字列から数値に変更する必要があります。
複数の文字列をまとめて数値に変更する方法
文字列→数値変換の方法はいろいろありますが、今回は「データ」タブの「区切り位置」を使った方法を案内します。
- 評価用の値を値貼り付けする。CONCATENATEの式でなく、値がセルに入ったが、それでも「#N/A」のエラー。
- 評価用のデータをすべて選択し、「区切り位置」を選択する
- 「区切り位置指定ウィザード 1/3」の「元のデータの形式」＞「データのファイル形式を選択してください」で「カンマやタブなど区切り文字によってフィールドごとに区切られたデータ」を選択する
- 「区切り位置指定ウィザード 2/3」の「区切り文字」に「タブ」にチェックが入っている状態で、「次へ」を選択する
- 「区切り位置指定ウィザード 3/3」の「列のデータ形式」が「G/標準(G)」であることを確認し、「完了」を選択する
上記手順で順位が正常に表示されました。
【VBAエキスパートの勉強】ExcelVBAベーシック ～4.VBAの構文：オブジェクト式 オブジェクト.プロパティ = 値～
今回はVBAの基本構文について学びます。
VBAの基本構文
VBAの構文には、大きく分けて以下の3種類があります。
- オブジェクト式
- ステートメント
- 関数
2種類のオブジェクト式
オブジェクト式には、以下の2種類の構文があります。
- 対象.様子 = 値
- 対象.命令 オプション := 値
「 1.」の構文は操作対象の様子や状態を設定するための式です。
対象は「オブジェクト」、様子は「プロパティ」ですので、以下のように解釈することができます。
オブジェクト.プロパティ = 値
オブジェクト.プロパティ = 値の構文を使って、プロパティを設定・変更する
試しに以下のコードを実行してみましょう。
Sub sample()
Range("A1").Value = "田中"
Range("A1").Font.Color = RGB(0, 0, 255)
End Sub
行っている処理内容は以下の通りです。
『Rangeオブジェクト』のセルA1の『Valueプロパティ』に田中を設定。次に『Rangeオブジェクト』は以下の『Font』オブジェクトの『Colorプロパティ』に青色を設定。
つまりは、セルA1に田中と文字を設定。その文字を青文字に設定しました。
結果
A1セル(オブジェクト)に田中が青文字で設定されました。
もう一つ別の例を試してみましょう。
Sub sample2()
Worksheets(1).Name = "VBAシート1"
End Sub
今回の処理では、『Worksheets(1)オブジェクト』の『Nameプロパティ』をVBAシート1に設定しました。
※Worksheets(1)とは、一番左側のシートのことです。
読み取り専用プロパティ
すべてのプロパティがユーザーの自由に値を設定できるわけではありません。
プロパティの中には、何が設定されているかを調べることだけができるものもあります。
例えば「ActiveCell.Address」のように、現在アクティブなセルのアドレスは、今選ばれているセルのアドレスなので設定することはできません。
ActiveCell.Address = "A1" のように設定することはできません。
設定はできませんが、以下のように現在のアクティブセルのアドレスを調べることはできます。
Sub sample3()
MsgBox ActiveCell.Address
End Sub
※現在選んでいるセルのアドレスをメッセージボックスで表示するVBAです。
設定はできない、調べることはできるプロパティを『読み取り専用のプロパティ』と呼びます。
プロパティの値を調べる
「 読み取り専用プロパティ」がでてきましたので、いくつかプロパティの値を調べる簡単な例を書きたいと思います。
Sub sample4()
Dim strCell As String, strWs As String
Range("A1").Value = "田中"
strCell = Range("A1").Value
strWs = Worksheets(1).Name
Range("C1").Value = "セルA1の値は" + strCell + "です。"
Range("C2").Value = "このブックの一番左のワークシート名は" + strWs + "です。"
End Sub
『Value』プロパティは省略することも可能です！
