Excel(エクセル)で一番大きい/小さい数字(最大値・最小値)を表示・強調する・色を付ける方法/Max・Min関数他の使い方

mushrooms IT

覚えてると意外と使うMax(Min)関数の使い方です。選択範囲の中で最も大きい(小さい)数字を表示することができます。
その他、条件付き書式で一番大きい数字を強調する、色を付ける方法も紹介します。

Advertisements

Max・Min関数の使い方

関数で最大値、最小値を求める方法です。構文は下記の通り、シンプルです。

最大値を表示
Max(範囲)

最小値を表示
Min(範囲)

下記の数字表を使って、最も大きな数字を計算してみましょう。
数字が入っているのはA2~A14なので、範囲にそれぞれA2:A14(A2からA14まで)を入力します。

max
=Max(A2:A14)
A2~A14の中で最も大きな数字を表示
と入力すると、範囲内の最大値が表示されました。
min
=Min(A2:A14)
A2~A14の中で最も小さな数字を表示
と入力すると、範囲内の最小値が表示されました。
maxmin
最小/最大の数字を更新すると、関数を入力したセルも更新されることが確認できました。

なお、Max関数では文字列・論理値は無視されますが、文字列は0、論理値は1と0として範囲に含めるMaxa関数というのもあります。

最大値/最小値に関連する項目を表示

たとえば点数を集計し、最大値/最小値およびそれが誰(何)なのかを知りたい場合は、一般的にVlookUp関数が使われます。集計する点数一覧の右隣に名前も書いてあれば、紐づけて表示が可能です。
VlookUpの記事内で「型番を打ちこむと商品名を表示」の解説をしていますが、型番入力欄をMax/Min関数にすればそのまま上記の用途に使えます。

vlookmax
実際に作った例。D2セルで=Max(A2:A6)を求め、=VlookUp(D2,A2:B6,2,FALSE)で最高点を取った人を表示している。点数を氏名より左に置かないといけない点に注意。

特定の値以上/以下/未満で一番大きい/小さい数字

条件付きのMax関数については、2016以降だとMaxIfs関数というのがあるそうです。
下記は旧来のやりかたです。

maxika1
やや特殊なケースですが、たとえばA1~A5の中で100未満の数字の中で一番大きな数字を求める場合、=Max(If(A1:A5<100,A1:A5,""))のように記述し、
maxika2
Ctrl+Shift+Enterを押して配列数式化してしまえば、1セルで計算可能です。ただちょっと上級者向けな感じです。
maxika3
または作業列を作り、A列右のB列に=If(A1<100,A1,"")のように記述し、
maxika4
これをオートフィルで延ばして100未満の数字だけを求め、
maxika5
B列をMax関数で集計すれば求められます。

オートカルクによる最大値/最小値の確認

こちらは関数ではなく、Excelがデフォルトで備えているオートカルクという機能を使った確認方法です。

autocalcmax1
表の中の最大値/最小値を確認したい範囲を選択します。
autocalcmax2
この状態でExcelの下部ステータスバーを見ると、自動で色々計算されています(初期設定では平均、個数、合計を表示)。この機能をオートカルクと呼びます。
autocalcmax3
ステータスバー上で右クリックすると、オートカルクの設定ができるリストが表示されます。
autocalcmax4
「最大値」をクリックしてチェックをつけます。
autocalcmax5
「最小値」にチェックをつけます。リストは適当な箇所をクリックして消します。
autocalcmax6
これで範囲を選択するだけで、簡易的に最大値/最小値を確認することができるようになりました。

条件付き書式による最大値/最小値の強調表示

kc1
ここまでは一番大きい/小さい数字を外側で確認する方法を見てきましたが、今度は表の中で大きい/小さい数字を強調する方法を試してみたいと思います。
まずは範囲を選択しておきます。
kc2
ホームタブ条件付き書式セルの強調表示ルールその他のルールとクリックしていきます。
kc3
ルールを「上位または下位に入る値だけを書式設定」に設定し、何位まで(あるいは何%まで)を強調するかを数字で指定します。一番大きい値であれば上位1位です。
kc4
小さい値を指定したければ「下位」を指定し、ルールが決まったら書式をクリックします。
kc5
セルの書式設定ダイアログが開くので、具体的にどうやって強調するかを指定します。
例えばスタイルを「太字」にすれば、文字が太字になりますし、
kc6
好きな色をクリックして、文字に任意の色をつけることもできます。もちろん、スタイルと色を両方指定してもOKです。
kc7
背景に色を付けたければ塗りつぶしタブに移動し、背景色を指定することもできます(他にも色んな書式がありますが、とりあえず一般的なのはこのあたりです)。
強調書式が決定したらOKを押します。
kc8
プレビューに指定した書式が表示されていることを確認して、もう一度OKを押します。
kc9
範囲に条件付き書式が適用され、上位1位の数字だけ強調されています。
kc10
一番大きい数字が変わると、その数字が強調されます。
kc16
あとから書式を変更したい、範囲を変更する場合は、まず書式が設定された範囲内でセルを挿入する方法があります。
kc17
挿入した分書式の適用範囲が拡張されます。
もしテーブルに条件付き書式を設定している場合は、表の下にデータを入力していくだけでテーブル及び書式の適用範囲が拡張されます。
kc11
または、条件付き書式ルールの管理をクリックします。
kc12
条件付き書式ルールの管理ダイアログが開くので、「適用先」で範囲を変更できます。
書式を変更したい場合は、変更したいルールを指定してルールの編集をクリックします。
kc14
ふたたび書式ルールの編集ダイアログが開くので、ルールや書式を変更します。
kc15
条件付き書式を解除したい場合は、解除したいセルを選択しておき、条件付き書式ルールのクリア選択したセルからルールをクリアをクリックします。
ワークシート全体からルールを排除したい場合は、特にセルの選択状態は問わず、シート全体からルールをクリアをクリックすれば全解除されます。

Max/Min+If関数で最大値/最小値の隣に○を付ける

maxck1
もし最大値の隣に○などの文字を付けるのが希望であれば、If関数を使います。
maxck2
A列の数字内で最大の値の右に○を付ける場合、B列に=If(A1=MAX(A:A),”○”,””)のように記述します。A1がA列の最大値と一致する(つまりA1が最大値)の場合は○を表示、そうでなければ空白を返すという意味の式です。
A:Aは「A列全て」という意味なので、それだとまずければ表に合わせてA1:A100など適宜調節してください。
maxck3
あとはこれをオートフィルで下に伸ばします。最大値の右にだけ○が表示されるはずです。
maxck4
最大値が更新されると、ちゃんと○の位置も変わります。
maxck5
もし最大値は○、最小値は×を付けたいとかであれば、たとえば=IF(A1=MAX(A:A),”○”,IF(A1=MIN(A:A),”×”,””))のように記述します。
赤字の部分はもとの式で””だった部分で、そこにもう一回If関数を入れてます。
①最大値かチェック②そうでなければ最小値かチェック③そうでなければ空白、という処理をしています。

またはExcelのバージョンが2016以降であればIfs関数というのが追加されていて、それだと=Ifs(A1=MAX(A:A),”○”,A1=MIN(A:A),”×”)のように書けばいいっぽいです(動作未確認)。

以上、範囲の中の一番大きい(小さい)数字を表示する方法でした。

やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント