○○IFという関数があると非常に検索などで捗りますが、問題はExcelのバージョンです。
AVERAGEIFやSUMIFなどはExcel2007から対応していますが、MINIFSやMAXIFSはExcel2016から対応などバージョンによって使える関数が異なってしまいます。今回はそんな問題を解決していきます。
また、SMALLやLARGE等の関数は(配列,順位)となっているので条件分岐が出来ないのが現状です。今回はこちらを解決していきます。
IF関数の基礎はこちらをご覧ください。
SMALL/LARGE関数のIFを作るには？
実際の作り方(配列数式の使い方)
RANKIF関数？
関数を組み合わせる事でバージョンの壁関係なく欲しい形にする事は可能なので、一つ一つ確認していきましょう。なお、今回はSMALL/LARGE関数やCOUNTIFS関数を使います。使い方については以下をご覧ください。
今回もサンプルを貼り付けておきますのでご活用ください。
SMALL/LARGE関数のIFを作るには？
Excel2016までではSMALLIFS/LARGEIFS関数というのは存在しません。
しかし「ジャンル別ランキング」「クラス別上位成績者」など条件別集計をする為には、SMALL/LARGE関数にもIF関数の条件分岐要素が必要になるのがわかるかと思います。
ちなみに今回は「ジャンル別映画ランキング」を作ってみます。
今回はネットでの口コミ5段階評価を上位から表示したいので、LARGE関数にIF要素を入れていきます。
F4セルだと、
=IFERROR(LARGE(IF($B$3:$B$22=F$3,$C$3:$C$22),$E4),””)
こうなります。
ジャンルがF3(怪談)と同じならば5段階評価の範囲の中でE4(1)番目に大きい値を出すという意味になります。
なお、IF関数の[値が偽の場合]の関数は省略して構いません。(範囲選択が行われないためエラーになり、IFERRORでエラー時は空欄になるようにしています。
ただ、実はこれでは機能しません。
実際の作り方(配列数式の使い方)
実際に先程の数式を実行するには配列数式という考えをしなければなりません。
配列とはExcelで言うならば複数のセルを指します。複数のセルを1つのセルと同様に扱う数式と考えてしまえばOKです。
なお、複数のセルとは1列or1行である必要があります。配列数式にするには
Ctrl+Shift+Enter
を押してください。
=IFERROR(LARGE(IF($B$3:$B$22=F$3,$C$3:$C$22),$E4),””)
この数式を選んだら、
Ctrl+Shift+Enter
を押すと 数式が
=IFERROR(LARGE(IF($B$3:$B$22=F$3,$C$3:$C$22),$E4),””)
から
{=IFERROR(LARGE(IF($B$3:$B$22=F$3,$C$3:$C$22),$E4),””)}
数式の両サイドに{ }がつけばOKです。
あとはINDEX関数+MATCH関数を使ってタイトルを引っ張ってきます。ただ、VLOOKUP関数は表の関係上使うことができません。
INDEX関数+MATCH関数の使い方とVLOOKUP関数が不可な理由はこちらをご覧ください。
また、今回は例題を難しくするために、同一検索条件が複数あるものにしてあります。こちらに対応方法を書いてあるので併せてご覧ください。
同一検索条件がある場合は、検索条件の部分を変えてしまえば検索値を個別に変更設定しやすくなります。今回は『5段階評価』の点数と『ジャンル』を演算子の「&」でくっつけてD列にでも配置してみましょう。
=INDEX($A$3:$A$22,MATCH(F4&INDEX($E2:$G6,2,2),$D$3:$D$22,0))
これでタイトルがうまく出せます。
あとは相対参照・絶対参照に気を付けながら関数式をコピーやオートフィルをして該当部分だけ変更すれば完了です。
RANKIF関数？
結論から言うとRANKIF関数は作る必要もありませんし、今回みたいに配列数式も使わずにCOUNTIFS関数だけで簡単にできます。COUNTIFS関数に関してはこちらをご覧ください。
=COUNTIFS($C$3:$C$22,”>”&$C3,$B$3:$B$22,B3)+1
これで終わります。
=COUNTIFS($C$3:$C$22,”>”&$C3,$B$3:$B$22,B3)+1
=COUNTIFS(検索条件1では自分より大きいものを探す、検索条件2では自分とジャンルが一致するものを検索する)となります。
ただし、これだけだと各ジャンル1位は検索条件１の自分より大きいものを探すで該当が0になってしまい0位になってしまうので数式の後に+1をして1位になります。それ以降の順位もすべて+1されるので1位から順番に表記される形になります。
