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

ワードとエクセルの練習問題の動画を「Youtube」で無料公開しています

【Excel関数】最大なのに下限のMAX、最小なのに上限のMIN

例えば、100点を超えないように加点するとか、マイナスになったら強制的に0にするとか、一律に上限や下限を設定することがあります。また、税金の計算では、上限10万円のことを「~の金額または10万円のいずれか小さいほう」というまわりくどい言い方をすることがあります。

Excelの場合、下限と上限の設定は MAX / MIN 関数を使うのが大原則です。オートSUMボタンを使えばよいです。そこで、今回は、MAX / MIN 関数を下限・上限として使う方法について出題します。

f:id:waenavi:20181014123747j:plain

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.上限の設定

問題
A列の数値のうち、100を超えるものをすべて100にしなさい。

f:id:waenavi:20181014102117j:plain

 

解説

オートSUMボタンで最小値を選びます(直接入力しても構いません)。「=MIN(A2)」と表示されます。

f:id:waenavi:20181014102424j:plain


A2のあとに、半角で、カンマ100と入力します。「=MIN(A2,100)」となります。

f:id:waenavi:20181014102427j:plain


確定して、オートフィルをします。100以上のものがすべて100になりました。

f:id:waenavi:20181014102629j:plain

 

100を上限とするということは、100と比較したときに、小さいほうの数を採用するということです。これで、必ず100より小さくなります。

f:id:waenavi:20181014103041j:plain


例えば、70の場合、100と比較すると70のほうが小さいので、70のほうが採用されます。100より小さければその数が採用されます。

f:id:waenavi:20181014103043j:plain


逆に、110の場合、100と比較すると100のほうが小さいので、100のほうが採用されます。100より大きければ、すべて100になります。

f:id:waenavi:20181014103046j:plain

 

Excelで、小さいほうを採用する計算は、最小値であるMIN関数を使います。セルA2と100を比較して小さいほうを採用するには、カンマでつなげます。これで100が上限になります。

f:id:waenavi:20181014103049j:plain

 

2.下限の設定

問題

A列の数値のうち、80より小さいものをすべて80にしなさい。

f:id:waenavi:20181014102246j:plain

 

解説

今度は最大値を選びます。

f:id:waenavi:20181014103052j:plain


A2を選びます。「=MAX(A2)」となります。

f:id:waenavi:20181014103054j:plain


A2のあとに、半角で、カンマ80と入力します。「=MAX(A2,80)」となります。

f:id:waenavi:20181014103057j:plain


確定して、オートフィルをします。80以下のものがすべて80になりました。

f:id:waenavi:20181014103059j:plain

 

80を下限とするということは、80と比較したときに、大きいほうの数を採用するということです。

f:id:waenavi:20181014104147j:plain

 

例えば、70の場合、80と比較すると80のほうが大きいので、80のほうが採用されます。80より小さければすべて80になります。

f:id:waenavi:20181014104151j:plain


逆に、100の場合、80と比較すると100のほうが大きいので、100のほうが採用されます。80より大きい数はそのまま採用されます。

f:id:waenavi:20181014104155j:plain

 

Excelで、大きいほうを採用する計算は、最大値であるMAX関数を使います。セルA2と80を比較して大きいほうを採用するには、カンマでつなげます。これで80が下限になります。

f:id:waenavi:20181014104159j:plain

  

3.上限と下限を両方設定する

問題

A列の数値のうち、100を超えるものをすべて100に、80より小さいものをすべて80にしなさい。

f:id:waenavi:20181014104202j:plain 

 

解説

上限を100とする場合にはMINで100と比較します。下限を80とする場合にはMAXで80と比較します。

f:id:waenavi:20181014104206j:plain

 

80から100の間に収める場合には、80と比較して大きいほうを採用した後で、100と比較して小さいほうを採用します。つまり、2回比較することになります。

f:id:waenavi:20181014104208j:plain

 

いったん80と比較します。

f:id:waenavi:20181014104210j:plain

 

その結果と100を比較します。

f:id:waenavi:20181014104212j:plain

 

最大値を選びます。

f:id:waenavi:20181014104215j:plain


A2を選びます。「=MAX(A2)」となります。

f:id:waenavi:20181014104217j:plain


A2のあとに、半角で、カンマ80と入力します。「=MAX(A2,80)」となります。これで下限が80になります。

f:id:waenavi:20181014104221j:plain


さらに、イコールの後に、「MIN(」と入力します。

f:id:waenavi:20181014104226j:plain


そして、式の最後に、カンマ100でかっこを閉じます。「=min(MAX(A2,80),100)」となります。

f:id:waenavi:20181014104229j:plain


オートフィルをします。これで完成です。

f:id:waenavi:20181014104233j:plain

 

慣れてきたら、すべてキーボードで入力するようにしましょう。

f:id:waenavi:20181014104237j:plain

 

4.計算結果の上限

問題

A列の点数に3点を足しなさい。ただし、加点した結果、100点を超える場合はすべて100点としなさい。

f:id:waenavi:20181014104948j:plain

 

解説

上限を設定するときはMIN、下限を設定するときはMAX、両方設定するときは、MINとMAXの両方を使います。

f:id:waenavi:20181014123638j:plain

 

100点を上限とするので、MINです。

f:id:waenavi:20181014105627j:plain


そして、点数に3を足します。「=MIN(A2+3)」となります。

f:id:waenavi:20181014105630j:plain


これが、100を超えないようにするので、カンマ100と入力します。「=MIN(A2+3,100)」となります。

f:id:waenavi:20181014105633j:plain


オートフィルをします。これで完成です。

f:id:waenavi:20181014105635j:plain

  

5.マイナスにならないようにする

問題

出席点を10点満点として、1回欠席するごとに3点を減点する。マイナスの点数にならないように、出席点を計算しなさい。

f:id:waenavi:20181014104952j:plain

 

解説

今度は0より小さくならないように計算するので、MAXです。

f:id:waenavi:20181014105637j:plain


そして、10点から、欠席回数1回につき3点を引きます。

f:id:waenavi:20181014105639j:plain


これが、0より下回らないようにするので、カンマ0とします。「=MAX(10-3*A2,0)」となります。

f:id:waenavi:20181014105642j:plain


オートフィルをします。これで完成です。

f:id:waenavi:20181014105646j:plain

 

6.少ないほう、多いほう

問題

次の図で、Yの5%と10万円のいずれか少ないほうを、Xから引きなさい。

f:id:waenavi:20181014104955j:plain

 

解説

「10万円と比較していずれか少ないほう」といえば、文字通り、最小値のことでMINです。10万円を上限として設定しているのと同じです。

f:id:waenavi:20181014105651j:plain


また、「いずれか多いほう」ならば、MAXです。下限を設定しているのと同じです。

f:id:waenavi:20181014105655j:plain

 

最小値で、Yの5%とします。「=MIN(B2*5%)」となります。

f:id:waenavi:20181014105657j:plain

f:id:waenavi:20181014105659j:plain


これが、10万円を超えないようにするので、カンマ10万と入力します。「=MIN(B2*5%,100000)」となります。

f:id:waenavi:20181014105702j:plain


これで少ないほうを選択したことになります。

f:id:waenavi:20181014105705j:plain


そして、この計算式のイコールのあとに、B1マイナスと入力して、XからMINを引きます。「=B1-MIN(B2*5%,100000)」となります。

f:id:waenavi:20181014105709j:plain

 

これで完成です。

f:id:waenavi:20181014105714j:plain

 

7.フラグを用いた加算

問題

100点満点の試験を実施した。A列はその得点であるが、B列が「1」の者はボーナス問題に正解したので、さらに10点を加点したい。

f:id:waenavi:20181014120258j:plain


合計が100点を超えないように加点するとき、ボーナス点を求めなさい。
例えば、最初の者は、96点でボーナス問題に正解したので、ボーナス点はプラス4点である。2番目の者はボーナス問題不正解なのでボーナス点は0点である。

f:id:waenavi:20181014120228j:plain


解説

100点を超えないようにボーナスを加点することを考えます。上限を設定するのでMINです。

f:id:waenavi:20181014121217j:plain


素点に、ボーナスの10倍を足します。

f:id:waenavi:20181014121221j:plain


ボーナスが正解の人は、1*10で10点になりますが、不正解の人は、0*10で0点になります。

f:id:waenavi:20181014121225j:plain

 

これが、100を超えないようにするので、カンマ100と入力します。

f:id:waenavi:20181014121230j:plain


オートフィルをします。これがボーナス点を加点した後の点数です。

f:id:waenavi:20181014121235j:plain

 

最後に、この計算式を修正して、計算式の最後に、素点を引きます。

f:id:waenavi:20181014121237j:plain


オートフィルをします。これで完成です。

f:id:waenavi:20181014121242j:plain

 

別解

ボーナス問題に正解した時の点数を先に計算します。MINで100点から得点を引きます。

f:id:waenavi:20181014121244j:plain

 

ボーナス点の上限は10点です。

f:id:waenavi:20181014121246j:plain


これでボーナス点が出ます。

f:id:waenavi:20181014121250j:plain


最後に不正解の人を0点にするため、B列の数字をかけます。

f:id:waenavi:20181014121255j:plain


オートフィルをします。これで完成です。

f:id:waenavi:20181014121301j:plain

 

8.合計の上限

問題

次の図で、(1)~(3)を合計したときの上限が3万円となるようにしたい。セルB3に「5000」と入力したときに合計が「30000」になるようにしなさい。

f:id:waenavi:20181014121309j:plain 

解説

合計はSUM関数で求めています。

f:id:waenavi:20181014122230j:plain


セルB3を5,000円とすると、30,000円をオーバーします。

f:id:waenavi:20181014122231j:plain


イコールのあとにMINを入力して、最後にカンマ 30000と入力します。これで完成です。

f:id:waenavi:20181014122234j:plain


5,000円と入力しても30000を超えることはありません。

f:id:waenavi:20181014122238j:plain

 

9.合計の下限

問題

次の図は、千羽鶴を10日間で折る計画で、残りの数を計算したものである。
セルB11に「180」と入力したときに残りが「0」になるようにしなさい。
また、累計を求めたときに1000を超えないようにしなさい。

f:id:waenavi:20181014121336j:plain

 

解説

残りは1000から合計個数を引いています。

f:id:waenavi:20181014122243j:plain


残りが160ですからそれより多く作った場合にはマイナスになります。目標を達成したらゼロになるようにします。

f:id:waenavi:20181014122244j:plain

 

イコールのあとにMAXを入れます。最後にカンマ 0と入力します。

f:id:waenavi:20181014122247j:plain

 

これで完成です。180と入力してもマイナスになりません。

f:id:waenavi:20181014122600j:plain

 

累計を求めます。(累計の求め方について詳しくはExcel新演習Program2-5の動画参照)

f:id:waenavi:20181014122254j:plain


イコールの後にMINと入力します。最後に カンマ1000と入力します。

f:id:waenavi:20181014122258j:plain


オートフィルをします。これで完成です。

f:id:waenavi:20181014122303j:plain

 

解説は以上です。 


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

この記事は、わえなび実力養成講座「ファンダメンタルExcel6」Program 6-7、6-8、6-8-2 の3本のYoutube動画を書き起こしたものです。

ちなみに、オートSUM関連の動画は全12回のシリーズです。すべて無料公開しています。ぜひご覧ください。

www.youtube.com

 


Youtube わえなび公式チャンネル
http://www.youtube.com/c/waenavijp

チャンネル登録(無料)をすると最新の動画をチェックできます。
初心者向けの動画が大量にあります。

 

 

 

 

 

Copyright(C)2018 waenavi, All rights reserved. [www.waenavi.com 定礎 平成30年8月]