わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】空白セルの判定をして、IF関数で「空白文字列」を返す方法と注意点


空白のセルにはまったく文字を入力していない場合(未入力セル)と、あえて長さ0の文字列(空白文字列)を入力する場合があります。IF関数で空白を扱うには、これら2つの共通点と相違点を理解しなければなりません。

ところで、IF関数は通常、論理式を先に入力し、真の場合、偽の場合の順に入力しますが、実際には必ずしもその順序で入力するとは限りません。ある計算式を入力した後で、計算結果を返すのが適切でない場合やエラーになる場合に、IF関数を直接入力することによって計算を回避するといった使い方もあります。この場合は、論理式をあとで入力します。

そこで、今回は、IF関数で空白を判定もしくは空白を返す練習問題を出題します。

目次

1.空白文字列を返す

問題

A列の値が60以上なら合格、そうでなければ不合格とする数式を入力した。合格だけを表示し、不合格を空白にするにはどうすればよいか。また、不合格だけを表示するにはどうすればよいか。

f:id:waenavi:20200922141740j:plain

 

解説

60以上を合格、それ以外を不合格としています。

  • =IF(A1>=60,"合格","不合格")

ここで、不合格の場合に何も表示しないようにするために、カンマと不合格の部分を消したとします。

f:id:waenavi:20200922142521j:plain

 

消したとしても60以上であれば合格と表示されることに変わりはありません。それは、TRUEの代わりに「合格」と表示するからです(参考:IF関数は、判定しているのではなく「変換しているだけ」であることを理解せよ【Excel】)。

f:id:waenavi:20200922142524j:plain

 

オートフィルをします。不合格がFALSEになりました。

f:id:waenavi:20200922142527j:plain

 

確かに、比較演算だけをするとFALSEです(参考:【Excel】真と偽の意味と比較演算のメリットを知らずしてIF関数を語ってはいけない)。

f:id:waenavi:20200922142536j:plain

 

もともとIF関数は、比較演算をした結果であるTRUEとFALSEを、別の文字列に変換するものです。

f:id:waenavi:20200922142539j:plain

 

合格の後にカンマが無く、さらに、FALSEの代わりに表示するものを指定していない場合は、FALSEと表示されます。

f:id:waenavi:20200922142543j:plain

 

表示しないことを表すには、ダブルクォーテーションを2個入れます。これは不合格の部分だけを削除したのと同じです。このような表記のことを「空白文字列」といいます。

f:id:waenavi:20200922142547j:plain

 

不合格の部分だけを消して、ダブルクォーテーション2つを残します。

f:id:waenavi:20200922142550j:plain

 

これで判定結果が空白になります。

f:id:waenavi:20200922142555j:plain

 

逆に、合格の部分だけを消して、ダブルクォーテーション2つを残します。

f:id:waenavi:20200922142559j:plain

 

60以上で空白になります。60未満の場合だけ不合格と表示されます。

f:id:waenavi:20200922142603j:plain

 

TRUEの場合に空白にする場合も、ダブルクォーテーションを2個入れます。

f:id:waenavi:20200922142605j:plain

 

2.計算の回避

問題

セルA2とセルB2の値を比較して、セルA2の値が0より大きい場合に限り、伸び率を表示しなさい。

f:id:waenavi:20200922141824j:plain

 

解説

伸び率を求めます。伸び率は変化した後を変化する前で割って1を引けばよいです(参考:増減率をパーセントで求めるには、増減を表す比率から1を引くだけ)。

f:id:waenavi:20200922143242j:plain

f:id:waenavi:20200922143246j:plain

 

通常はこれで良いのですが、先月の値を0にするとエラーになります。

f:id:waenavi:20200922143250j:plain

 

先月比や伸び率は、変化する前の値から何倍に増えたかを表す割合であり、その比率で増え方が急であったか緩やかな伸びであったかを判断する数値です。

f:id:waenavi:20200922143253j:plain

 

変化する前の値が0の場合、何倍しても0なので伸び率を求めることは不可能です。

f:id:waenavi:20200922143258j:plain

 

0より大きい場合に限って計算する場合は、IF関数で囲みます。

f:id:waenavi:20200922143301j:plain

 

0より大きい場合は伸び率を計算しますが、そうでない場合は空白を返します。

f:id:waenavi:20200922143304j:plain

 

計算をするのが不都合な場合は、IF関数で囲み、計算をするための条件を論理式として入力します。

f:id:waenavi:20200922143309j:plain

 

計算ができない場合やエラーの場合に、計算を回避することができます。

f:id:waenavi:20200922143313j:plain

 

数値、セル参照または数式のときは原則としてダブルクォーテーションを付けませんが、空白を返すときはダブルクォーテーションが必要です。

f:id:waenavi:20200922143317j:plain

 

3.ある条件を満たす場合に印をつける

問題

A列の値が60未満の場合、右隣りのセルに「*」を表示しなさい。また、これを利用して、A列の値が60未満なら不合格、そうでなければ合格としなさい。

f:id:waenavi:20200922172652j:plain

 

解説

IF関数を記述します。A列の値が60未満であることを判定して、「*」を表示します。そうでない場合は空白を返します。

f:id:waenavi:20200922174416j:plain

 

60未満の場合だけ印をつけることができます。

f:id:waenavi:20200922174420j:plain

 

ある条件を満たしている値だけ印をつける場合、IF関数を使います。このとき、条件を満たさないほうは空白文字列にします。

f:id:waenavi:20200922174424j:plain

 

「*」を、「不」にします。

f:id:waenavi:20200922174426j:plain

 

この数式の後に合格を連結します。

f:id:waenavi:20200922174429j:plain

 

これで合格と不合格になります。

f:id:waenavi:20200922174433j:plain

 

4.未入力セルと空白文字列の違い

(1)参照した場合の違い

問題

セルA2に空白文字列「""」を入力しなさい。また、セルA1とセルA2をそれぞれ参照した時の違いを述べなさい。

f:id:waenavi:20201001102706j:plain

 

解説

ダブルクォーテーション2つを入力すると、そのままダブルクォーテーション2つが表示されます。これは空白文字列ではありません。

f:id:waenavi:20201001113458j:plain

 

空白文字列は先頭にイコールを付けて入力します。

f:id:waenavi:20201001113502j:plain

 

セルA1は何も入力していない未入力のセル、セルA2は空白文字列を数式で入力したセルですが、見た目は全く同じです。

f:id:waenavi:20201001113507j:plain

f:id:waenavi:20201001113510j:plain

 

セルA1を参照します。

f:id:waenavi:20201001113513j:plain

 

0になります。

f:id:waenavi:20201001113517j:plain

 

何も入力していないセルは数式の中で参照すると0と同じとみなされます。

f:id:waenavi:20201001113521j:plain

 

何も入力していないというのはデータが存在せず、数値なのか文字列なのかも全く分からないという状態です。

f:id:waenavi:20201001113524j:plain

 

原則として未入力のセルを参照すると数値の0として扱われます。ただし、例外はあります。

f:id:waenavi:20201001113528j:plain

 

未入力のセル同士を連結します。

f:id:waenavi:20201001113531j:plain

 

00ではなく空白になります。

f:id:waenavi:20201001113536j:plain

 

文字列連結演算のように、明らかに文字列として扱っている数式の場合は、0ではなく空白として扱います。

f:id:waenavi:20201001113542j:plain

 

今度はセルA2を参照します。

f:id:waenavi:20201001113544j:plain

 

空白文字列を入力したセルは0ではなく、空白文字列のままです。

f:id:waenavi:20201001113547j:plain

 

空白文字列を入力した場合は、数値ではなく文字列を入力しているので、参照しても数値の0にはなりません。

f:id:waenavi:20201001113551j:plain

 

数値ではないので、数値であることを前提とした計算はできません。例えば、足し算をするとエラーになります。

f:id:waenavi:20201001113555j:plain

 

(2)数値と比較した場合の違い

問題

セルA1の値が60未満の場合に空白を返す数式を入力した。セルA1を空白にするとどうなるか。また、空白文字列「""」を入力した場合はどうか。

f:id:waenavi:20201001102734j:plain

 

解説

いま、セルB1は空白になっています。セルA1の値が60未満のため空白文字列を返しているからです。

  • =IF(A1<60,"","合格")

f:id:waenavi:20201001150552j:plain

 

セルA1の値を削除します。空白のままです。

f:id:waenavi:20201001150556j:plain

 

何も入力していないセルは、数値との比較演算では0とみなされますから、60より小さいという判定となり空白文字列となります。

f:id:waenavi:20201001150559j:plain

 

セルA1に空白文字列を入力します。

f:id:waenavi:20201001150602j:plain

 

合格となりました。

f:id:waenavi:20201001150605j:plain

 

空白文字列を入力したセルは数値の0とはみなされず、文字列のまま判定します。

f:id:waenavi:20201001150608j:plain

 

いっぱんに、比較演算では文字列は数値よりも大きいと判定されるため、FALSE、つまり合格と表示されます。

f:id:waenavi:20201001150614j:plain

 

このように、空白のセルが必ずしも0になるとは限らないので注意が必要です。

f:id:waenavi:20201001150617j:plain

 

5.空白セルを合計するときの注意点

問題

10000円未満の場合は送料550円で、10000円以上の場合は空白としたらエラーになった。正しく合計を求めなさい。

f:id:waenavi:20201001151149j:plain

 

解説

いま、送料を空白にしています。

f:id:waenavi:20201001155804j:plain

 

商品代金と送料を足し算します。送料が空白であってもエラーにはなりません。

f:id:waenavi:20201001155807j:plain

 

完全な空白セル、つまり数式も何も入力していないセルの場合は、足し算や引き算などの四則演算をすると、数値の0とみなされるのでエラーにはなりません。

f:id:waenavi:20201001155811j:plain

 

10000円未満の場合に550円とし、それ以外を0円としてもエラーにはなりません。

f:id:waenavi:20201001155814j:plain

 

しかし、0円ではなく、空白を返した場合はエラーになります。

f:id:waenavi:20201001155817j:plain

f:id:waenavi:20201001155820j:plain

 

足し算や引き算などの四則演算はそのうちの1つが文字列の場合、計算できずエラーとなります。

f:id:waenavi:20201001155823j:plain

 

空白文字列は文字列の一種なので、0とはみなされません。したがって、四則演算をするとエラーになります。

f:id:waenavi:20201001155826j:plain

 

合計を足し算ではなく、オートSUMで求めます(参考:【Excel】合計はオートSUMボタンを押すだけ~!で済めば苦労はしない)。

f:id:waenavi:20201001155829j:plain

 

エラーにはなりません。

f:id:waenavi:20201001155834j:plain

 

SUM関数は、計算する範囲の中に文字列があった場合、文字列を除外して計算します。足し算はできないので、足し算をせず除外するという意味です。

f:id:waenavi:20201001155837j:plain

 

空白文字列も文字列の一種なので計算から除外されます。そのためエラーにはならないのです。

f:id:waenavi:20201001155840j:plain

 

6.空白を返したセルはジャンプで選択できない

問題

A列の数値がマイナスなら空白を返し、そうでなければA列の数値をそのまま表示する式を入力した。

f:id:waenavi:20201001204616j:plain

 

B列を選択して、ジャンプ機能を用いて空白セルを選択しようとしたところ、「該当するセルが見つかりません」というエラーが表示された。この理由を述べたうえで、空白と判定されたセルをすべて選択しなさい。

f:id:waenavi:20201001204645j:plain

 

解説

数式を何も入力していない状態で、範囲選択します。

f:id:waenavi:20201001223323j:plain

 

条件を選択してジャンプ、空白セルで選択するとエラーにはなりません(参考:【Excelジャンプ】空白セルを一括選択して一括コピーして一括削除する)。

f:id:waenavi:20201001223326j:plain

f:id:waenavi:20201001223329j:plain

 

今度は、A列が0より小さい場合は空白を返し、それ以外は元の数値をそのまま返す数式を入力します。

f:id:waenavi:20201001223334j:plain

 

これによって、マイナスの値の場合だけ空白になっています。B列を選択します。

f:id:waenavi:20201001223337j:plain

 

条件を選択してジャンプ、空白セルで選択しようとしてもエラーになります。

f:id:waenavi:20201001223340j:plain

f:id:waenavi:20201001223344j:plain

 

完全な空白セル、つまり数式も何も入力されていないセルは、ジャンプ機能の空白セルの選択の対象となります。

f:id:waenavi:20201001223347j:plain

 

しかし、数式が入っていて空白になっているセルは、ジャンプ機能の空白セルの対象外です。

f:id:waenavi:20201001223352j:plain

 

この場合は、検索の画面を表示します(参考:【Excel】特定の文字列を含むセルを全部選択するには[すべて検索]をして[すべて選択]をすればよい)。

f:id:waenavi:20201001223357j:plain

 

オプションをクリックして、検索対象を値にします。

f:id:waenavi:20201001223400j:plain

 

検索する文字列を空欄にしたまま、すべて検索します。ALTキー+Aですべて選択します。これで空白を返したセルだけを選択することができます。

f:id:waenavi:20201001223404j:plain

 

7.空白だったら空白にする

(1)空白を判定する

問題

セルA1の値を2倍しなさい。ただし、セルA1が空白であれば空白にしなさい。

f:id:waenavi:20201001223454j:plain

 

解説

セルA1の値を2倍します。

f:id:waenavi:20201001231110j:plain

 

セルA1を空白にします。0になります。

f:id:waenavi:20201001231115j:plain

 

また、空白文字列を入力するとエラーになります。

f:id:waenavi:20201001231119j:plain

f:id:waenavi:20201001231122j:plain

 

何も入力していない空白セルを参照すると数値の0とみなされますので、2倍すると0になります。

f:id:waenavi:20201001231126j:plain

 

また、空白文字列は四則演算ができないのでエラーになります。

f:id:waenavi:20201001231132j:plain

 

空白であることを判定するには空白文字列とイコールでつないで判定します。

f:id:waenavi:20201001231136j:plain

 

空白であればTRUE、何か入力すればFALSEとなります。

f:id:waenavi:20201001231139j:plain

f:id:waenavi:20201001231143j:plain

 

空白セルであることの判定は空白文字列と比較をします(参考:【Excel】一致の判定はイコール「=」、等しくないことの判定は不等号2つ「<>」)。

f:id:waenavi:20201001231146j:plain

 

何も入力していない空白セルも空白文字列もTRUEとなります。何らかの文字や数値を入力した時にFALSEとなります。

f:id:waenavi:20201001231149j:plain

 

IF関数でTRUEとFALSEを別の文字列に変換することも可能です。

f:id:waenavi:20201001231152j:plain

 

IF関数を挿入します。空白であることを判定します。空白であれば空白を返します。それ以外は2倍する計算をします。

f:id:waenavi:20201001231156j:plain

 

セルA1を空白にすると、連動して空白になります。

f:id:waenavi:20201001231200j:plain

 

あるセルが空白であれば、別のセルも連動して空白にする数式は暗記したほうが良いでしょう。

f:id:waenavi:20201001231203j:plain

 

(2)計算結果を表示しない

問題

セル範囲D2:D6に単価×数量を求めなさい。ただし、商品名が空白のときは空白としなさい。

f:id:waenavi:20201001225441j:plain

 

解説

単価と個数を掛け算します。このとき、単価と数量が入っていない行は0になります。それは、何も入力していないセルは参照すると数値の0とみなされるからです。

f:id:waenavi:20201001231759j:plain

 

この0を表示しないようにするには、商品名が入っているかどうかを判定して、空白を返せばよいです。

f:id:waenavi:20201001231803j:plain

 

商品名が空白であるかどうかを判定し、商品名が空白であれば空白を返し、そうでなければ単価と個数を掛け算します。

f:id:waenavi:20201001231806j:plain

 

IF関数を挿入します。商品名が空白であることを判定して、TRUEであれば空白を返します。そうでなければ計算をすればよいです。

f:id:waenavi:20201001231809j:plain

 

これで完成です。

f:id:waenavi:20201001231812j:plain

 

このように、はじめに計算式を入力したあとで、空白であれば空白を返すという判定を追加することが非常に多いです。空白であれば空白を返すという入力方法は何回も繰り返し練習しておいたほうが良いです。

f:id:waenavi:20201001231816j:plain

 

(3)累計を表示しない

問題

A列の累計を求めなさい。ただし、A列が空白の場合、累計を表示する必要はない。

f:id:waenavi:20201001231831j:plain

 

解説

上のセルと左のセルを足して累計を求めます(参考:【Excel数式】計算が苦手な人のための「累計」「残高」「繰越」)。

f:id:waenavi:20201002001124j:plain

 

オートフィルをします。空白セルは0とみなされるので、同じ残高が表示されます。

f:id:waenavi:20201002001128j:plain

 

そこでIF関数を挿入します。A列が空白の場合には空白にします。

f:id:waenavi:20201002001131j:plain

 

これで完成です。

f:id:waenavi:20201002001135j:plain

 

8.空白を埋める

問題

A列に文字が入力されていればその文字を表示し、空白の場合は上と同じ文字を表示しなさい。また、A列に文字が入力されていれば1とし、空白の場合は1ずつ増やして連番を表示しなさい。

f:id:waenavi:20201001231854j:plain

 

解説

空白の場合、上と同じ文字を入れます。

f:id:waenavi:20201002085124j:plain

 

A列が空白であることを判定して、上のセルを参照しますが、文字が入力されていればA列の文字を参照します。

f:id:waenavi:20201002085126j:plain

 

IF関数を挿入します。A列が空白の場合には上のセルを参照します。そうでなければA列を参照します。

f:id:waenavi:20201002085130j:plain

 

これで空白を埋めるように同じ文字が並びます。

f:id:waenavi:20201002085133j:plain

 

連続する数値を入力するには上のセルに1を足します(参考:【Excel】オートフィルは累積的に同じ計算を繰り返すときにも使う)。

f:id:waenavi:20201002085136j:plain

 

連番を入力して文字が入っていれば1にリセットします。

f:id:waenavi:20201002085139j:plain

 

A列が空白であることを判定して、上のセルの値に1を足しますが、文字が入力されていれば1にします。

f:id:waenavi:20201002085142j:plain

f:id:waenavi:20201002085146j:plain

 

IF関数を挿入します。A列が空白の場合には上のセルに1をたします。そうでなければ1にします。

f:id:waenavi:20201002085149j:plain

 

これで記号ごとに連番を入力することができます。

f:id:waenavi:20201002085152j:plain

 

9.何らかの文字や数値が入力されていることの判定

問題

商品名が入力されていれば、D列にTRUEと表示しなさい。また、商品名が入力されている場合に限り、D列に単価×個数を求めなさい。

f:id:waenavi:20200926020813j:plain

 

解説

何らかの文字や数値が入力されているということは、「空白ではない」ということです。

f:id:waenavi:20200926021445j:plain

 

ダブルクォーテーション2つの空白文字列とイコールで比較をすれば空白であることが判定できます。その逆で、空白文字列と不一致であることを判定すれば、何らかの文字や数値が入力されていることを判定することができます。

f:id:waenavi:20200926021448j:plain

 

商品名を参照します。空白文字列と不一致であることを判定します。TRUEとなります。

f:id:waenavi:20200926021451j:plain

 

オートフィルをします。空白の場合はFALSEとなります。

f:id:waenavi:20200926021454j:plain

 

文字が入力されていることの判定ができれば、その場合だけ計算をすればよいです。

f:id:waenavi:20200926021457j:plain

 

IF関数を挿入します。空白でなければ、単価と個数を掛け算します。それ以外は空白を返します。

f:id:waenavi:20200926021501j:plain

 

これで完成です。

f:id:waenavi:20200926021505j:plain

 

別解

単価と個数を掛け算します。

f:id:waenavi:20200926021509j:plain

 

IF関数を挿入して、商品名が空白の場合だけ空白を返します。

f:id:waenavi:20200926021512j:plain

 

空白でなければ計算するのと、空白だったら空白にするのは同じ結果になります。

f:id:waenavi:20200926021516j:plain

 

空白であることを判定するのと、文字列が入力されていることを判定するのは、真の場合と偽の場合が逆になります。

f:id:waenavi:20200926021520j:plain

 


解説は以上です。


10.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel13」Program 13-11、13-12、13-12-2、13-14、13-15(特訓問題のみ)のYoutube動画を書き起こして、加筆修正したものです。

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ