コンピュータサービス技能評価試験
表計算２級 課題２ 関数式による 表の完成
設問３
順序（10） 対象範囲 M23：O25
｢金額｣が 高額な 上位3位 の ｢タイトル名｣と
その ｢会員区分｣ 及び ｢金額｣ を 求める
問題文を検討する
① 「上位３位」とあるので、使用するのは、LARGE 関数
引数としては LARGE（配列, 順位）
配列は、｢金額｣のセル範囲 I8:I32
順位は、「◆金額上位3位」表の「順位」列を利用する L23
=LARGE(I8:I32,L23)
これで、求めることが出来るのは、その順位の「金額」です
② 求めるもの まず「タイトル名」
一覧から 求める
分かっているのは、「金額」
「タイトル名」の列から 「金額」と同じ行の「タイトル名」を求める
LOOKUP 関数 でも 求めることが出来ますが
出来れば、同じ表で、関数を再利用したい
この場合 INDEX 関数 を 使う
書 式
INDEX(配列, 行番号, [列番号])
INDEX 関数と言えば、MATCH 関数 セットで覚えましょう
MATCH 関数は、
範囲 のセルの範囲で、指定した項目を 検索
その範囲内の項目の相対的な位置を返します
その範囲で何番目にあるか求める
INDEX 関数
配列
一覧表の「データ部分」
※ 「データ部分」というのも一つのポイント
列見出しなどは含めない データ部分だけ の セル範囲 C8:I32
行番号
LARGE 関数で、求めた「金額」が 一覧表のデータ部分で 何番目なのか求める
これを MATCH 関数で、求める
MATCH(検査値, 検査範囲, [照合の型])
「検査値」欄
ここに、LARGE 関数 を 設定する
=LARGE(I8:I32,L23)
「検査範囲」欄
「金額（円）」列 ＝ セル範囲 I8:I32
「照合の型」欄
完全一致を求めるので、「0」
列番号
行番号 と 同じように考える
「◆金額上位3位」表の「列見出し」を利用する
同じように、MATCH 関数 を 使う
MATCH(検査値, 検査範囲, [照合の型])
「検査値」欄
「列見出し」の M22 （＝タイトル名）
「検査範囲」欄
一覧表の ７行目 C7:I7
では、実際に作成してみる
１．セル M23 を クリック・選択
２．fx （関数の挿入）
［関数の挿入］で、INDEX を 選択する
INDEX の場合 下のような画面が表示されます
今回の場合
上の「配列、行番号、列番号」が 選択された状態で、［ OK ］クリック
３．［関数の引数］ INDEX が、表示されたら
「配列」欄
最初に 検討したように、一覧のデータ範囲を ドラッグ・選択する
４．この関数は、右や下で、利用するので それを考慮して 絶対参照にする
セル範囲を選択したら、すぐに［F4］キー を 押す
$C$8:$I$32
５．「行番号」欄に、カーソルを 移動
「名前ボックス」欄の ▼ を クリック
表示される関数に「MATCH」があれば、クリック
無い場合は、一番下の「その他の関数」をクリック
６．MATCH 関数
「検査値」欄に、カーソルがある状態で
「名前ボックス」欄の ▼ を クリック
表示される関数に「LARGE」があれば、クリック
無い場合は、一番下の「その他の関数」をクリック
７．LARGE 関数
「配列」欄 「金額（円）」のデータ範囲のセル範囲 I8:I32
同じように、他の位置でも利用するので、絶対参照 に する
「順位」欄 「◆金額上位3位」の「順位」列のセル L23 を クリック・選択
こちらは、絶対参照 ではなく
行が移動したら 行参照 は、移動して欲しい
なので、相対参照（列固定）にする
セル L23 を クリック選択したら ［F4］キーを ３回押し $L23 にする
数式バー内の MATCH 文字列内を クリックする
８．［関数の引数］が、MATCH に戻る
「検査範囲」欄には
「金額（円）」のデータ範囲 I8:I32 を ドラッグ・選択
絶対参照 にする
「照合の種類」欄には、完全一致を求めるので、「0」
９．数式バー内の INDEX 文字列内を クリックする
10．INDEX の 関数の引数になったら
「列番号」欄
「行番号」と 同じように MATCH 関数
11．今度は、列を決める
基準 ＝ 検査値は、
「◆金額上位3位」表の「列見出し」＝ M22
このセル参照は、常に 同じ 22行を 参照したいので、相対参照（列固定）
［F4］キーを ２回押し M$22
「検査範囲」欄 一覧表の７行目 C7:I7
こちらは、絶対参照 $I$7:$I$7
「照合の種類」欄は、完全一致 なので、0
これで、「数式の結果」に ジャバダンの野心 とあるので、［ OK ］ クリック
=INDEX($C$8:$I$32,MATCH(LARGE($I$8:$I$32,$L23),$I$8:$I$31,0),MATCH(M$22,$C$7:$I$7,0))
まずは、下へ オートフィルしてみる
次は、右へ フィルする
残念ながら、「金額（円）」の列は、#N/A に、なってしまう
エラー値 #N/A は、数式で 参照の対象が 見つからないことを示しています
実は、こうなることは、分かっていました
何故ならば
INDEX 関数 の「列番号」欄の MATCH 関数 で
「検査範囲」欄に、設定したセル範囲 $C$7:$I$7
このセル範囲のデータは
最後の I列 ですが、セル I7 のデータは、（円）だけなのです
ところが、
「◆金額上位3位」の列見出しでは、「金額（円）」なので
エラー#N/A に なるのです
つまり O23・O24・O25 の数式は、変更する必要がある
O23 の 数式
=INDEX($C$8:$I$32,MATCH(LARGE($I$8:$I$32,$L23),$I$8:$I$31,0),MATCH(O$22,$C$7:$I$7,0))
修正するのは、INDEX 関数の「列番号」欄内の MATCH 関数
MATCH(O$22,$C$7:$I$7,0)
このように、既に 設定されている数式（＝関数）を 修正する場合
修正する関数がわかっている場合
数式バーで、その関数名内を クリックし カーソルが入ったら
fx を クリックする
すると その関数（MATCH）の ［関数の引数］が、表示されます
「検査値」欄内を Delete し
「名前ボックス」の ▼ を クリック
「RIGHT」を 選択し 「RIGHT」の「関数の引数」を表示する
「文字列」欄には、「◆金額上位3位」の列見出し O22
セル参照は、行固定の「複合参照」 O$22
「文字数」欄には、3 にして
一覧表の７行目にある「（円）」と 同じ文字列に変更する
もう この［関数の引数］の 左下 「数式の結果」に、値が表示されている
エラーでは、なくなっているので、［ OK ］ クリックする
下へ、オートフィルする
=INDEX($C$8:$I$32,MATCH(LARGE($I$8:$I$32,$L23),$I$8:$I$31,0),MATCH(RIGHT(O$22,3),$C$7:$I$7,0))
ここまでやって
令和４年度 コンピュータサービス技能評価試験 （表計算部門）
２級 試験問題 概要 PDF
１９ページを 確認してみたら
なんと
INDEX 関数の「列番号」欄は、
自分で数えた、数値で 入力していました
でも 今回書いたことも ２級を受けるようならば 理解できると思います