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

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

【Excel】IF関数の共通部分を外に出す、ある条件のときだけ計算を追加する

IF関数を使うときは最初に論理式を考え、その後に真の場合と偽の場合を考えるという順序で数式を作ります。そのため、真の場合と偽の場合で共通する部分があっても、それに気が付かず同じ式を2回書いてしまうことがあります。そして、数式が長くなってしまいます。

IF関数の式が長くなるのは、真の場合と偽の場合で分けて考える必要が無い計算も重複して記述することが原因であることが多いのです。スマートな数式を書くには、真の場合と偽の場合で共通する部分を外に出し、条件によって異なる場合だけIF関数で記述するようにします。

そこで、今回は、真の場合と偽の場合で共通する部分を外に出す方法と、ある条件を満たした時だけ計算を追加する方法について出題します。

目次

1.共通する文字列連結を外に出す

問題

今月の獲得ポイントが3000ポイント以上であれば、「来月はゴールド会員となります」と表示し、そうでなければ「来月は通常会員となります」と表示しなさい。

f:id:waenavi:20200922131337j:plain

 

解説

IF関数を挿入します。

f:id:waenavi:20200922131756j:plain

 

セルB1が3000以上のときに、「来月はゴールド会員となります」、そうでなければ「来月は通常会員となります」とします。

f:id:waenavi:20200922131759j:plain

 

これで完成です。

f:id:waenavi:20200922131803j:plain

 

ところで、IF関数はもともと比較演算をした結果であるTRUEとFALSEを別の文字列に置き換えることによって、1つのセルの中で2つの結果を返す関数です。

f:id:waenavi:20200922131805j:plain

 

この場合、ゴールドと通常の部分だけ変えれば良いことが分かります。

f:id:waenavi:20200922131809j:plain

 

「来月は」の部分を削除します。

f:id:waenavi:20200922131813j:plain

 

その代わりに、IF関数の前に「来月は」を連結します。

f:id:waenavi:20200922131815j:plain

f:id:waenavi:20200922131819j:plain

 

IF関数の前後に、文字列の連結をすることができます。

f:id:waenavi:20200922131822j:plain

 

「会員となります」の部分を削除します。

f:id:waenavi:20200922131826j:plain

 

その代わりに、IF関数の最後に「会員となります」を連結します。

f:id:waenavi:20200922131829j:plain

 

これで共通する部分のダブりが消えて数式が短くなります。

f:id:waenavi:20200922131832j:plain

 

このように共通部分がある場合は、IF関数の外に出して、切り替える部分だけをIF関数で処理するようにしたほうがよいです。

f:id:waenavi:20200922131836j:plain

 

2.共通する計算を外に出す

問題

商品代金が8000円以上であれば10%引き、それ以外は5%引きにしたい。割引金額をマイナスで求め、合計を求めなさい。

f:id:waenavi:20200922131348j:plain

 

解説

商品代金が8000円以上の場合、商品代金の10%、そうでなければ商品代金の5%を求めます。

f:id:waenavi:20200922132359j:plain

 

ただし、答えをマイナスにしないといけないので、それぞれB1の前にマイナスを付けます。

  • =IF(B1>=8000,-B1*10%,-B1*5%)

f:id:waenavi:20200922132403j:plain

 

足し算で合計を求めます。

f:id:waenavi:20200922132406j:plain

 

これで完成です。

f:id:waenavi:20200922132409j:plain

 

ところで、この場合も、-B1と掛け算をするところは共通しています。共通部分は外に出すべきです。

f:id:waenavi:20200922132413j:plain

 

商品代金が8000円以上かどうかを判定して、変わるのは割引率だけです。

f:id:waenavi:20200922132416j:plain

 

-B1をかける部分を削除します。

f:id:waenavi:20200922132419j:plain

 

-B1の部分をIF関数の外に出します。

f:id:waenavi:20200922132424j:plain

f:id:waenavi:20200922132427j:plain

 

IF関数の前後に、足し算や掛け算などの演算を追加することができます。

f:id:waenavi:20200922132430j:plain

 

別解

ちなみに、パーセント演算子「%」は100で割る演算なので、これも外に出すことが可能です(参考:【Excel数式】表示形式のパーセントと演算子のパーセントの違いを理解しよう)。

f:id:waenavi:20200922132434j:plain

 

3.西暦下2桁だけを考える

問題

セルA2~A11に、月日を表す3桁または4桁の数値を入力した。1月から3月までの日付であれば2026、それ以外は2025と表示しなさい。

f:id:waenavi:20200922132938j:plain

 

解説

1月から3月までの日付は100台、200台、300台の数値なので、400未満であると言えます(331以下でもよい)。

f:id:waenavi:20200922141225j:plain

 

400未満であれば2026、それ以外は2025とします。

f:id:waenavi:20200922141228j:plain

 

これで完成です。

f:id:waenavi:20200922141231j:plain

 

別解

ところで、2026と2025では2000の部分が共通しています。

f:id:waenavi:20200922141234j:plain

 

そこで、2000の部分を外に出して、26と25だけをIF関数の中に残すといったことも可能です。

f:id:waenavi:20200922141238j:plain

f:id:waenavi:20200922141240j:plain

f:id:waenavi:20200922141244j:plain

 

4.年月日を8桁の数値で表す

問題

セルA4~A13に、月日を表す3桁または4桁の数値を入力した。1月から3月までの日付であればセルA1の値に1を足し、それ以外はセルA1に入力した年で、8桁の年月日を表す数値を求めなさい。

f:id:waenavi:20200922140821j:plain

 

解説

400未満の数値であれば、セルA1の値に1を足し、そうでなければセルA1のままとします。セルA1は絶対参照です。

f:id:waenavi:20200922141408j:plain

 

これで年を求めることができました。

f:id:waenavi:20200922141412j:plain

 

この後に月日をつなげるのですが、年を10000倍して、月日を足せばよいです。

f:id:waenavi:20200922141416j:plain

 

この計算は、400未満かどうかとは無関係なので、IF関数の外に記述すればよいです。

f:id:waenavi:20200922141419j:plain

 

これで完成です。

f:id:waenavi:20200922141424j:plain

 

5.条件を満たす場合だけ計算を追加する

(1)足し算・引き算を追加する

問題

注文を受けた個数と、現時点で不足している数を合わせて発注するため、セル範囲B2:B6の合計を求めた。セルD2がマイナスのときに限り、不足分を加算しなさい。

f:id:waenavi:20200922174821j:plain

 

解説

SUM関数を用いて注文数の合計を求めています。

f:id:waenavi:20200922180325j:plain

 

さらに、在庫として不足している分が5個あるので、5個増やして発注しようとしています。

f:id:waenavi:20200922180328j:plain

 

合計の後にIF関数を追加します。

f:id:waenavi:20200922180332j:plain

 

在庫数が0より小さいときに、マイナス在庫数とし、そうでなければ発注を増やさないので0とします。

f:id:waenavi:20200922180336j:plain

 

これで完成です。

f:id:waenavi:20200922180339j:plain

 

在庫があれば加算されません。

f:id:waenavi:20200922180343j:plain

 

別解

このようにマイナスの在庫数を引いても良いです。

f:id:waenavi:20200922180346j:plain

 

元の数値に0を足したり引いたりしても変わりません。

f:id:waenavi:20200922180351j:plain

 

条件を満たす場合だけ足したり引いたりするときは、条件を満たさない場合は、「0」とします。

f:id:waenavi:20200922180354j:plain

 

(2)掛け算・割り算・累乗を追加する

問題

5月と6月の売上金額から伸び率を計算し、7月の売上目標を6月の売上金額と同じとする数式を入力した。

f:id:waenavi:20200922175405j:plain

 

伸び率がプラスだった店舗については、6月からさらに同じ伸び率で増やした金額を目標としなさい。

f:id:waenavi:20200922175415j:plain

 

解説

5月と6月の売上金額から伸び率を求めています。

f:id:waenavi:20200922181256j:plain

 

7月の売上目標は6月を参照しています。

f:id:waenavi:20200922181259j:plain

 

伸び率がマイナスの店舗についてはこれ以上売り上げが下がらないように、前月の売上をそのまま目標としますが、伸び率がプラスの場合はその伸び率のまま売り上げを伸ばすことを目標としたと仮定します。

f:id:waenavi:20200922181302j:plain

f:id:waenavi:20200922181305j:plain

 

6月の売上にIF関数を掛けます。伸び率がプラスの場合は、「1+伸び率」を掛け算します。これで6月の売上に対して同じ伸び率で増やすことになります。

f:id:waenavi:20200922181309j:plain

 

そうでなければ1とします。

f:id:waenavi:20200922181313j:plain

 

これで完成です。伸び率がマイナスならば6月の売上のままです。

f:id:waenavi:20200922181317j:plain

 

元の数値に1を掛けたり割ったり累乗にしても変わりません。

f:id:waenavi:20200922181322j:plain

 

条件を満たす場合だけ掛けたり割ったり累乗にしたりするときは、条件を満たさない場合は、「1」または「100%」とします。

f:id:waenavi:20200922181325j:plain

 

(3)文字列連結を追加する

問題

A列の数値を小数点以下切り捨てて、「cm」を連結する数式を入力した。

f:id:waenavi:20200922175441j:plain

 

さらに、小数点以下を切り捨てたものについては「※小数点以下切り捨て」を連結しなさい。

f:id:waenavi:20200922175531j:plain

 

解説

INT関数で小数点以下を切り捨てて、「cm」を連結しています(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。

f:id:waenavi:20200922183345j:plain

 

さらに、元の数値が小数の場合に限り、さらに文字列を連結しようとしています。IF関数を連結します。

f:id:waenavi:20200922183357j:plain

 

元の数値A1と切り捨てた値INT(A1)を比較して、元の数値のほうが大きければ小数であることが分かります。

  • 小数点以下があることの判定 A1>INT(A1)

f:id:waenavi:20200922183401j:plain

 

小数であることを判定します。小数であれば、「※小数点以下切り捨て」を連結しますが、整数であれば何も連結しないので、空白文字列を返します。

f:id:waenavi:20200922183408j:plain

 

これで完成です。

f:id:waenavi:20200922183412j:plain

 

元の文字列に空白文字列を連結しても変わりません。

f:id:waenavi:20200922183416j:plain

 

条件を満たす場合だけ文字列を連結するときは、条件を満たさない場合は、空白を返します。

f:id:waenavi:20200922183419j:plain

 


解説は以上です。


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

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

 


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