コンピュータサービス技能評価試験
表計算２級　課題２　関数式による表の完成

設問３

　順序（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 関数の「列番号」欄は、

自分で数えた、数値で入力していました

でも　今回書いたことも　２級を受けるようならば　理解できると思います

CS試験表２級課題２＿11