例えば、100点を超えないように加点するとか、マイナスになったら強制的に０にするとか、一律に上限や下限を設定することがあります。また、税金の計算では、上限10万円のことを「～の金額または10万円のいずれか小さいほう」というまわりくどい言い方をすることがあります。
Excelの場合、下限と上限の設定は MAX / MIN 関数を使うのが大原則です。オートSUMボタンを使えばよいです。そこで、今回は、MAX / MIN 関数を下限・上限として使う方法について出題します。
目次
問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。
- ０．最大MAX・最小MINの基本的な使い方
- １．上限の設定
- ２．下限の設定
- ３．上限と下限を両方設定する
- ４．計算結果の上限
- ５．マイナスにならないようにする
- ６．少ないほう、多いほう
- ７．フラグを用いた加算
- ８．合計の上限
- ９．合計の下限
- 10．動画版はこちら（無料）
０．最大MAX・最小MINの基本的な使い方
最大値MAX、最小値MIN、オートSUMボタンの説明はこちらの記事をご覧ください。
１．上限の設定
問題
A列の数値のうち、100を超えるものをすべて100にしなさい。
解説
オートSUMボタンで最小値を選びます（直接入力しても構いません）。「=MIN(A2)」と表示されます。
A2のあとに、半角で、カンマ100と入力します。「=MIN(A2,100)」となります。
確定して、オートフィルをします。100以上のものがすべて100になりました。
100を上限とするということは、100と比較したときに、小さいほうの数を採用するということです。これで、必ず100より小さくなります。
例えば、70の場合、100と比較すると70のほうが小さいので、70のほうが採用されます。100より小さければその数が採用されます。
逆に、110の場合、100と比較すると100のほうが小さいので、100のほうが採用されます。100より大きければ、すべて100になります。
Excelで、小さいほうを採用する計算は、最小値であるMIN関数を使います。セルA2と100を比較して小さいほうを採用するには、カンマでつなげます。これで100が上限になります。
２．下限の設定
問題
A列の数値のうち、80より小さいものをすべて80にしなさい。
解説
今度は最大値を選びます。
A2を選びます。「=MAX(A2)」となります。
A2のあとに、半角で、カンマ80と入力します。「=MAX(A2,80)」となります。
確定して、オートフィルをします。80以下のものがすべて80になりました。
80を下限とするということは、80と比較したときに、大きいほうの数を採用するということです。
例えば、70の場合、80と比較すると80のほうが大きいので、80のほうが採用されます。80より小さければすべて80になります。
逆に、100の場合、80と比較すると100のほうが大きいので、100のほうが採用されます。80より大きい数はそのまま採用されます。
Excelで、大きいほうを採用する計算は、最大値であるMAX関数を使います。セルA2と80を比較して大きいほうを採用するには、カンマでつなげます。これで80が下限になります。
３．上限と下限を両方設定する
問題
A列の数値のうち、100を超えるものをすべて100に、80より小さいものをすべて80にしなさい。
解説
上限を100とする場合にはMINで100と比較します。下限を80とする場合にはMAXで80と比較します。
80から100の間に収める場合には、80と比較して大きいほうを採用した後で、100と比較して小さいほうを採用します。つまり、2回比較することになります。
いったん80と比較します。
その結果と100を比較します。
最大値を選びます。
A2を選びます。「=MAX(A2)」となります。
A2のあとに、半角で、カンマ80と入力します。「=MAX(A2,80)」となります。これで下限が80になります。
さらに、イコールの後に、「MIN（」と入力します。
そして、式の最後に、カンマ100でかっこを閉じます。「=min(MAX(A2,80),100)」となります。
オートフィルをします。これで完成です。
慣れてきたら、すべてキーボードで入力するようにしましょう。
４．計算結果の上限
問題
A列の点数に3点を足しなさい。ただし、加点した結果、100点を超える場合はすべて100点としなさい。
解説
上限を設定するときはMIN、下限を設定するときはMAX、両方設定するときは、MINとMAXの両方を使います。
100点を上限とするので、MINです。
そして、点数に3を足します。「=MIN(A2+3)」となります。
これが、100を超えないようにするので、カンマ100と入力します。「=MIN(A2+3,100)」となります。
オートフィルをします。これで完成です。
５．マイナスにならないようにする
問題
出席点を10点満点として、1回欠席するごとに3点を減点する。マイナスの点数にならないように、出席点を計算しなさい。
解説
今度は０より小さくならないように計算するので、MAXです。
そして、10点から、欠席回数1回につき3点を引きます。
これが、0より下回らないようにするので、カンマ０とします。「=MAX(10-3*A2,0)」となります。
オートフィルをします。これで完成です。
６．少ないほう、多いほう
問題
次の図で、Yの５％と10万円のいずれか少ないほうを、Xから引きなさい。
解説
「10万円と比較していずれか少ないほう」といえば、文字通り、最小値のことでMINです。10万円を上限として設定しているのと同じです。
また、「いずれか多いほう」ならば、MAXです。下限を設定しているのと同じです。
最小値で、Yの５％とします。「=MIN(B2*5%)」となります。
これが、10万円を超えないようにするので、カンマ10万と入力します。「=MIN(B2*5%,100000)」となります。
これで少ないほうを選択したことになります。
そして、この計算式のイコールのあとに、B1マイナスと入力して、XからMINを引きます。「=B1-MIN(B2*5%,100000)」となります。
これで完成です。
７．フラグを用いた加算
問題
100点満点の試験を実施した。A列はその得点であるが、B列が「1」の者はボーナス問題に正解したので、さらに10点を加点したい。
合計が100点を超えないように加点するとき、ボーナス点を求めなさい。例えば、最初の者は、96点でボーナス問題に正解したので、ボーナス点はプラス4点である。２番目の者はボーナス問題不正解なのでボーナス点は０点である。
解説
100点を超えないようにボーナスを加点することを考えます。上限を設定するのでMINです。
素点に、ボーナスの10倍を足します。
ボーナスが正解の人は、１＊10で10点になりますが、不正解の人は、0＊10で０点になります。
これが、100を超えないようにするので、カンマ100と入力します。
オートフィルをします。これがボーナス点を加点した後の点数です。
最後に、この計算式を修正して、計算式の最後に、素点を引きます。
オートフィルをします。これで完成です。
別解
ボーナス問題に正解した時の点数を先に計算します。MINで100点から得点を引きます。
ボーナス点の上限は10点です。
これでボーナス点が出ます。
最後に不正解の人を０点にするため、B列の数字をかけます。
オートフィルをします。これで完成です。
８．合計の上限
問題
次の図で、（１）～（３）を合計したときの上限が３万円となるようにしたい。セルB3に「5000」と入力したときに合計が「30000」になるようにしなさい。
解説
合計はSUM関数で求めています。
セルB3を5,000円とすると、30,000円をオーバーします。
イコールのあとにMINを入力して、最後にカンマ 30000と入力します。これで完成です。
5,000円と入力しても30000を超えることはありません。
９．合計の下限
問題
次の図は、千羽鶴を10日間で折る計画で、残りの数を計算したものである。セルB11に「180」と入力したときに残りが「0」になるようにしなさい。また、累計を求めたときに1000を超えないようにしなさい。
解説
残りは1000から合計個数を引いています。
残りが160ですからそれより多く作った場合にはマイナスになります。目標を達成したらゼロになるようにします。
イコールのあとにMAXを入れます。最後にカンマ 0と入力します。
これで完成です。180と入力してもマイナスになりません。
累計を求めます（参考：【Excel数式】計算が苦手な人のための「累計」「残高」「繰越」）。
イコールの後にMINと入力します。最後に カンマ1000と入力します。
オートフィルをします。これで完成です。
解説は以上です。
10．動画版はこちら（無料）
この記事は、わえなび実力養成講座「ファンダメンタルExcel６」Program 6-7、6-8、6-8-2 の３本のYoutube動画を書き起こしたものです。
- ファンダメンタルExcel 6-7 上限と下限を設定する１（範囲内に収める）
- ファンダメンタルExcel 6-8 上限と下限を設定する２（応用事例）
- ファンダメンタルExcel 6-8-2 補講 合計の上限と下限
ちなみに、オートSUM関連の動画は全12回のシリーズです。すべて無料公開しています。ぜひご覧ください。
ファンダメンタルExcel 6 オートSUM（全12回）【わえなび】 - YouTube
https://www.youtube.com/playlist?list=PLRaY8kd5CoxMwUSAR-iY74jGRwV3M55s_
