- Home »
- エクセル練習問題：目次 »
- 列の検索・行の検索
更新:2024/5/7;作成:2012/8/9
- 問題1：B列〜D列に商品の単価リストがあります。このリストからデータを取り出して E列〜I列の販売実績の商品名と販売金額を埋めなさい。 問題1の解答
- 販売金額は 販売数×単価 で求めることとします。
|
B |
C |
D |
E |
F |
G |
H |
I |
| 2 |
商品単価リスト |
|
|
|
販売実績 |
|
|
|
| 3 |
コード |
商品名 |
単価 |
|
コード |
商品名 |
販売数 |
販売金額 |
| 4 |
果物-01 |
りんご |
180 |
|
果物-02 |
|
10 |
|
| 5 |
果物-02 |
みかん |
130 |
|
果物-01 |
|
10 |
|
| 6 |
果物-03 |
バナナ |
140 |
|
果物-03 |
|
10 |
|
- 問題2：B列〜D列に商品の単価リストがあります。このリストからデータを取り出して E列〜H列の販売実績の商品名と販売金額を埋めなさい。 問題2の解答
- 販売金額は 販売数×単価 で求めることとします。
|
B |
C |
D |
E |
F |
G |
H |
I |
| 2 |
商品単価リスト |
|
|
|
販売実績 |
|
|
|
| 3 |
コード |
商品名 |
単価 |
|
コード |
商品名 |
販売数 |
販売金額 |
| 4 |
野菜-01 |
大根 |
150 |
|
野菜-03 |
|
20 |
|
| 5 |
野菜-02 |
白菜 |
120 |
|
果物-02 |
|
15 |
|
| 6 |
野菜-03 |
ピーマン |
50 |
|
果物-01 |
|
18 |
|
| 7 |
果物-01 |
りんご |
180 |
|
野菜-02 |
|
21 |
|
| 8 |
果物-02 |
みかん |
130 |
|
|
|
|
|
| 9 |
果物-03 |
バナナ |
140 |
|
|
|
|
|
- 問題3：問題1と同じ「商品の単価リスト」があります。このリストからF列のコードを求めなさい。 問題3の解答
-
|
B |
C |
D |
E |
F |
G |
H |
| 2 |
商品単価リスト |
|
|
|
販売実績 |
|
|
| 3 |
コード |
商品名 |
単価 |
|
コード |
商品名 |
販売数 |
| 4 |
野菜-01 |
大根 |
150 |
|
|
ピーマン |
20 |
| 5 |
野菜-02 |
白菜 |
120 |
|
|
みかん |
15 |
| 6 |
野菜-03 |
ピーマン |
50 |
|
|
大根 |
18 |
| 7 |
果物-01 |
りんご |
180 |
|
|
バナナ |
21 |
| 8 |
果物-02 |
みかん |
130 |
|
|
|
|
| 9 |
果物-03 |
バナナ |
140 |
|
|
|
|
- 問題4：F列〜J列に判定基準の表があります。この基準でD列に得点の判定をしなさい。 問題4の解答
- 判定基準は0以上40未満は「不可」、40以上60未満は「可」、60以上80未満は「良」、80以上は「優」を意味します。
D列の得点を判定をしなさい。
|
B |
C |
D |
E |
F |
G |
H |
I |
J |
| 2 |
名前 |
得点 |
判定 |
|
判定基準 |
|
|
|
|
| 3 |
相沢一郎 |
52 |
|
|
得点(以上) |
0 |
40 |
60 |
80 |
| 4 |
井上浩二 |
74 |
|
|
判定 |
不可 |
可 |
良 |
優 |
| 5 |
上野有紀 |
84 |
|
|
|
|
|
|
|
| 6 |
内野武 |
32 |
|
|
|
|
|
|
|
| 7 |
上田祥子 |
68 |
|
|
|
|
|
|
|
| 8 |
江田早苗 |
59 |
|
|
|
|
|
|
|
| 9 |
榎本高貴 |
80 |
|
|
|
|
|
|
|
| 10 |
小田和樹 |
60 |
|
|
|
|
|
|
|
- 解答
この問題のポイントは商品単価リストのコードが昇順に並んでいるところです。
|
B |
C |
D |
E |
F |
G |
H |
I |
| 2 |
商品単価リスト |
|
|
|
販売実績 |
|
|
|
| 3 |
コード |
商品名 |
単価 |
|
コード |
商品名 |
販売数 |
販売金額 |
| 4 |
果物-01 |
りんご |
180 |
|
果物-02 |
みかん |
10 |
1,300 |
| 5 |
果物-02 |
みかん |
130 |
|
果物-01 |
りんご |
10 |
1,800 |
| 6 |
果物-03 |
バナナ |
140 |
|
果物-03 |
バナナ |
10 |
1,400 |
- Excel for Microsoft365,Excel2021ではXLOOKUP関数やスピルの機能が使えるようになりました。
G4セルには =XLOOKUP(F4:F6,B4:B6,C4:C6)
I4セルには =XLOOKUP(F4:F6,B4:B6,D4:D6)*H4:H6
と入力します。
- 検索される列が左端列にあるので、VLOOKUP関数は使うことができます。
商品単価リストの検索するコードは昇順に並んでいるので、引数の検索の型を省略することができます。(または TRUE とします)
- VLOOKUP関数の構文：=VLOOKUP(検索値,範囲,列番号,[検索の型])
詳しい説明は VLOOKUP関数 を参照してください。
- 商品の単価リストのコードは昇順に並んでいますので、検索の型は省略できます。
G4セルは =VLOOKUP(F4,$B$4:$D$6,2) としました。
I4セルは =VLOOKUP(F4,$B$4:$D$6,3)*H4 で単価を取り出して販売数を乗じます。
- G4セルでVLOOKUP関数の引数ダイアログボックスを使う場合は、検索値に F4 、範囲に $B$4:$D$6 、列番号に 2 と入力します。
- I4セルでは =VLOOKUP(F4,$B$4:$D$6,3)*H4 と列番号を 3 にし、販売数を *H4 と掛けます。
- INDEX関数を使うことができます。行位置を調べるのにMATCH関数を合わせて使います。
構文：=INDEX(範囲,行番号,[列番号],[領域番号])
INDEX関数 に説明がありますので参照してください。
構文：=MATCH(検査値,検査範囲,[照合の型])
MATCH関数 に説明がありますので参照してください。
- 商品の単価リストのコードは昇順に並んでいますので、MATCH関数の照合の型は省略できます。
G4セルは =INDEX($C$4:$C$6,MATCH(F4,$B$4:$B$6)) としました。
I4セルは =INDEX($D$4:$D$6,MATCH(F4,$B$4:$B$6))*H4 で単価を取り出して販売数を乗じます。
- 商品単価リストのコードが昇順に並んでいるので、LOOKUP関数を使うことができます。
- =LOOKUP(検査値,検査範囲,対応範囲) [ベクトル形式]
=LOOKUP(検査値,配列) [配列形式]
詳しい説明は LOOKUP関数 を参照してください。
-
LOOKUP関数のベクトル形式で数式を作成すると
G4セルは =LOOKUP(F4,$B$4:$B$6,$C$4:$C$6) としました。
I4セルは =LOOKUP(F4,$B$4:$B$6,$D$4:$D$6)*H4 としました。
-
LOOKUP関数の配列形式で数式を作成すると
G4セルは =LOOKUP(F4,$B$4:$C$6) としました。
I4セルは =LOOKUP(F4,$B$4:$D$6)*H4 としました。
- 解答
この問題のポイントは商品単価リストのコードが昇順に並んでいないところです。
|
B |
C |
D |
E |
F |
G |
H |
I |
| 2 |
商品単価リスト |
|
|
|
販売実績 |
|
|
|
| 3 |
コード |
商品名 |
単価 |
|
コード |
商品名 |
販売数 |
販売金額 |
| 4 |
野菜-01 |
大根 |
150 |
|
野菜-03 |
ピーマン |
20 |
1,000 |
| 5 |
野菜-02 |
白菜 |
120 |
|
果物-02 |
みかん |
15 |
1,950 |
| 6 |
野菜-03 |
ピーマン |
50 |
|
果物-01 |
りんご |
18 |
3,240 |
| 7 |
果物-01 |
りんご |
180 |
|
野菜-02 |
白菜 |
21 |
2,520 |
| 8 |
果物-02 |
みかん |
130 |
|
|
|
|
|
| 9 |
果物-03 |
バナナ |
140 |
|
|
|
|
|
- Excel for Microsoft365、Excel2021ではXLOOKUP関数やスピルの機能が使えるようになりました。
XLOOKUP関数の使い方は XLOOKUP関数で範囲や配列を検索する をご覧ください。
- XLOOKUP関数は完全一致で検索しますので、リストが昇順に並んでいなくても検索ができます。
- G4セルには =XLOOKUP(F4:F7,B4:B9,C4:C9) と入力します。
I4セルには =XLOOKUP(F4:F7,B4:B9,D4:D9)*H4:H7 と入力します。
- 商品単価リストのコードが昇順に並んでいませんので、LOOKUP関数は使えません。
- 検索される列が左端列にあるので、VLOOKUP関数は使うことができます。
VLOOKUP関数の構文：=VLOOKUP(検索値,範囲,列番号,[検索の型])
詳しい説明は VLOOKUP関数 を参照してください。
- 商品単価リストの検索するコードは昇順に並んでいないので、引数の検索の型をFALSE と指定する必要があります。(または 0 とします)
G4セルは =VLOOKUP(F4,$B$4:$D$9,2,FALSE) としました。
I4セルは =VLOOKUP(F4,$B$4:$D$9,3,FALSE)*H4 で単価を取り出して販売数を乗じます。
- G4セルでVLOOKUP関数の引数ダイアログボックスを使う場合は、検索値に F4 、範囲に $B$4:$D$9 、列番号に 2 、検索方法に
FALSE と入力します。
- INDEX関数を使うことができます。行位置を調べるのにMATCH関数を合わせて使います。
構文：=INDEX(範囲,行番号,[列番号],[領域番号])
INDEX関数 に説明がありますので参照してください。
構文：=MATCH(検査値,検査範囲,[照合の型])
MATCH関数 に説明がありますので参照してください。
- 商品の単価リストのコードは昇順に並んでいないので、MATCH関数の照合の型を 0 と指定する必要があります。
G4セルは =INDEX($C$4:$C$9,MATCH(F4,$B$4:$B$9,0)) としました。
I4セルは =INDEX($D$4:$D$9,MATCH(F4,$B$4:$B$9,0))*H4 で単価を取り出して販売数を乗じます。
- 解答
|
B |
C |
D |
E |
F |
G |
H |
| 2 |
商品単価リスト |
|
|
|
販売実績 |
|
|
| 3 |
コード |
商品名 |
単価 |
|
コード |
商品名 |
販売数 |
| 4 |
野菜-01 |
大根 |
150 |
|
野菜-03 |
ピーマン |
20 |
| 5 |
野菜-02 |
白菜 |
120 |
|
果物-02 |
みかん |
15 |
| 6 |
野菜-03 |
ピーマン |
50 |
|
野菜-01 |
大根 |
18 |
| 7 |
果物-01 |
りんご |
180 |
|
果物-03 |
バナナ |
21 |
| 8 |
果物-02 |
みかん |
130 |
|
|
|
|
| 9 |
果物-03 |
バナナ |
140 |
|
|
|
|
- Microsoft365ではXLOOKUP関数やスピルの機能が使えるようになりました。
XLOOKUP関数の使い方は XLOOKUP関数で範囲や配列を検索する をご覧ください。
- XLOOKUP関数は完全一致で検索しますので、リストが昇順に並んでいなくても検索ができます。
- G4セルには =XLOOKUP(G4:G7,C4:C9,B4:B9) と入力します。
- 商品名をキーに検索することになります。
商品名は昇順に並んでいないので、LOOKUP関数は使用できません。
また、商品単価リストで商品名はコードの右側の列にあるので、VLOOKUP関数は使えません。
- INDEX関数を使うことにします。
商品名は昇順に並んでいないので、MATCH関数の照合の型を 0 と指定する必要があります。
F4セルの数式は =INDEX($B$4:$B$9,MATCH(G4,$C$4:$C$9,0)) とします。
- OFFSET関数とMATCH関数を組み合わせることもできます。
OFFSET関数の構文：=OFFSET(基準,行数,列数 [,高さ,幅]）
詳細な使い方は OFFSET関数 をご覧ください。
- OFFSET関数で 基準を B3セルにして、MATCH関数と組み合わせて求めることができます。
F4セルの数式は =OFFSET($B$3,MATCH(G4,$C$4:$C$9,0),0) としました。
- 解答
|
B |
C |
D |
E |
F |
G |
H |
I |
J |
| 2 |
名前 |
得点 |
判定 |
|
判定基準 |
|
|
|
|
| 3 |
相沢一郎 |
52 |
可 |
|
得点(以上) |
0 |
40 |
60 |
80 |
| 4 |
井上浩二 |
74 |
良 |
|
判定 |
不可 |
可 |
良 |
優 |
| 5 |
上野有紀 |
84 |
優 |
|
|
|
|
|
|
| 6 |
内野武 |
32 |
不可 |
|
|
|
|
|
|
| 7 |
上田祥子 |
68 |
良 |
|
|
|
|
|
|
| 8 |
江田早苗 |
59 |
可 |
|
|
|
|
|
|
| 9 |
榎本高貴 |
80 |
優 |
|
|
|
|
|
|
| 10 |
小田和樹 |
60 |
良 |
|
|
|
|
|
|
- Microsoft365ではXLOOKUP関数やスピルの機能が使えるようになりました。
XLOOKUP関数の使い方は XLOOKUP関数で範囲や配列を検索する をご覧ください。
- =XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
XLOOKUP関数は[一致モード]を-1とすると、完全一致で検索しますが、見つからない場合は、次の小さなアイテムが返されます。
- G4セルには =XLOOKUP(C3:C10,G3:J3,G4:J4,"該当なし",-1) と入力します。
- 判定基準は上端行にあります。得点をキーとして検索するにはHLOOKUP関数が利用できます。
- 構文：=HLOOKUP(検索値,範囲,行番号,[検索の型])
HLOOKUP関数 に説明がありますので参照してください。
［検索値］未満の最大値を求めるので、HLOOKUP関数の引数の検索の型は TRUE または省略します。
D3セルの数式は =HLOOKUP(C3,$G$3:$J$4,2) としました。
- 判定基準の得点の行(G3:J3セル)の値は昇順に並んでいますので、LOOKUP関数を使うことができます。
- =LOOKUP(検索値,検索範囲,対応範囲)
LOOKUP関数は［検索値］と一致する値なないときは、［検索値］未満の最大値を検索します。
D3セルの数式は =LOOKUP(C3,$G$3:$J$4) としました。
- INDEX関数を使うことができます。列位置を調べるのにMATCH関数を合わせて使います。
構文：=INDEX(範囲,行番号,[列番号],[領域番号])
INDEX関数 に説明がありますので参照してください。
構文：=MATCH(検査値,検査範囲,[照合の型])
MATCH関数 に説明がありますので参照してください。
- D3セルの数式は =INDEX($G$4:$J$4,0,MATCH(C3,$G$3:$J$3)) としました。
INDEX関数の引数[行番号」は 0 とし、列番号を MATCH(C3,$G$3:$J$3) で求めています。
スポンサードリンク
Home｜エクセル練習問題：目次｜列の検索・行の検索
PageViewCounter
Since2006/2/27