Home »
エクセル練習問題:目次 »
行と列の検索

エクセル練習問題:行と列の検索

スポンサードリンク

(追記) (追記ここまで)

更新:2024年5月10日;作成:2013年6月13日

問題 topへ

問題1:桜島の月別の噴火回数のデータがあります。問題1の解答
- 年と月を入力して、その月の噴火回数を求めなさい。
  出典:鹿児島地方気象台ホームページ
  https://www.jma-net.go.jp/kagoshima/vol/data/skr_erp_num.html
  
  B C D E F G H I J K L M N O
  
  2 桜島の月別の噴火回数 ※(注記)桜島では噴火活動が活発なため、噴火のうち、
  爆発もしくは一定規模以上の噴火の回数を計数しています。
  
  3 年 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月年合計
  
  4 2024 3 10 13 1 1 - - - - - - - 28
  
  5 2023 14 30 30 2 28 11 8 5 4 69 11 3 215
  
  6 2022 7 2 0 0 3 3 37 71 36 39 17 20 235
  
  7 2021 21 22 31 42 11 1 1 0 4 1 4 7 145
  
  8 2020 104 129 26 51 51 28 0 1 7 2 8 25 432
  
  9 2019 8 15 29 10 15 5 9 0 32 62 137 71 393
  
  10 2018 13 7 44 69 96 35 29 64 44 8 14 56 479
  
  11 2017 0 0 2 21 49 14 7 98 170 38 6 1 406
  
  12 2016 0 55 14 52 25 5 2 0 0 0 0 0 153
  
  13 2015 113 154 272 241 244 103 21 6 98 0 0 0 1252
  
  14 2014 16 33 75 29 35 48 31 89 178 28 53 41 656
  
  15 2013 124 132 74 17 70 18 128 144 154 127 69 40 1097
  
  16 2012 190 103 128 127 89 73 77 59 73 61 71 56 1107
  
  17 2011 102 144 72 109 113 45 79 142 204 122 78 145 1355
  
  18 2010 149 154 135 105 35 107 87 72 47 14 59 62 1026
  
  19 2009 4 24 31 67 9 39 69 71 82 126 90 143 755
  
  20 2008 1 4 0 13 28 19 11 2 1 1 0 0 80
  
  21 2007 1 3 2 0 16 16 0 1 0 1 0 2 42
  
  22 2006 1 3 0 2 1 17 1 7 8 5 5 1 51
  
  23 2005 1 0 0 0 0 1 8 0 2 2 1 2 17
問題2
- 下図のような桜島の噴火回数、降灰量のデータがあります。
  年、月、データの種類を選択して、B20セルに表中の値を表示しなさい。問題2の解答
- 出典:鹿児島地方気象台ホームページ
  https://www.jma-net.go.jp/kagoshima/vol/data/skr_erp_num.html
  「-」は降灰なし、「0」は0.5g/m2未満を示しています。
  鹿児島市東郡元で計測した値です。
  
  B C D E F G H I J K L M N O P
  
  1 年区分 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月年合計
  
  2 2024 噴火回数 3 10 13 1 1 - - - - - - - 28
  
  3 2024 降灰量 - 0 0 1 - - - - - - - - 1
  
  4 2023 噴火回数 14 30 30 2 28 11 8 5 4 69 11 3 215
  
  5 2023 降灰量 2 6 9 3 10 3 - 7 3 61 3 0 107
  
  6 2022 噴火回数 7 2 0 0 3 3 37 71 36 39 17 20 235
  
  7 2022 降灰量 0 0 - - 0 - 28 - 53 24 9 12 126
  
  8 2021 噴火回数 21 22 31 42 11 1 1 0 4 1 4 7 145
  
  9 2021 降灰量 3 11 21 42 16 0 1 - 3 0 0 0 97
  
  10 2020 噴火回数 104 129 26 51 51 28 0 1 7 2 8 25 432
  
  11 2020 降灰量 75 21 3 0 19 71 - 0 2 2 5 14 212
  
  12 2019 噴火回数 8 15 29 10 15 5 9 0 32 62 137 71 393
  
  13 2019 降灰量 0 17 10 3 47 3 0 2 115 143 69 54 463
  
  14 2018 噴火回数 13 7 44 69 96 35 29 64 44 8 14 56 479
  
  15 2018 降灰量 3 0 20 39 173 803 62 79 19 2 8 10 1,218
  
  16 2017 噴火回数 0 0 2 21 49 14 7 98 170 38 6 1 406
  
  17 2017 降灰量 - - - - 11 2 - - 92 55 2 0 162
  
  18 2016 噴火回数 0 55 14 52 25 5 2 0 0 0 0 0 153
  
  19 2016 降灰量 - 4 0 5 10 22 74 0 - - - - 115
  
  20 2015 噴火回数 113 154 272 241 244 103 21 6 98 0 0 0 1,252
  
  21 2015 降灰量 16 13 72 107 309 18 6 0 4 1 - - 546
  
  22 2014 噴火回数 16 33 75 29 35 48 31 89 178 28 53 41 656
  
  23 2014 降灰量 15 4 5 47 21 51 3 6 548 141 139 15 995
  
  24 2013 噴火回数 124 132 74 17 70 18 128 144 154 127 69 40 1,097
  
  25 2013 降灰量 11 24 8 0 74 4 2 59 906 720 7 18 1,833
  
  26 2012 噴火回数 190 103 128 127 89 73 77 59 73 61 71 56 1,107
  
  27 2012 降灰量 4 26 12 19 1,658 951 293 142 194 153 137 13 3,602
  
  28 2011 噴火回数 102 144 72 109 113 45 79 142 204 122 78 145 1,355
  
  29 2011 降灰量 1 131 7 39 121 56 28 220 859 114 173 38 1,787

問題1の解答例 topへ

INDEX関数で求める VLOOKUP関数で求める

HLOOKUP関数で求める XLOOKUP関数で求める

INDEX関数で求める

INDEX関数とMATCH関数で一致する行と列を調べて求めることができます。
- 構文:=INDEX(範囲,行番号,列番号)
  INDEX関数に説明がありますので参照してください。
- 構文:=MATCH(検索値,範囲 [,照合の型])
  MATCH関数に説明がありますので参照してください。
- 構文:=XMATCH(検索値,検索範囲,[一致モード],[検索モード])
  XMATCH関数に説明がありますので参照してください。
- 「年」年が降順になっているので、MATCH関数では完全一致で検索します。
  よって、R4セルと一致する行は MATCH(R4,B4:B23,0) で求めます。
  XMATCH関数を使うと、XMATCH(R4,B4:B23) となります。
- 「月」R5セルと一致する列は MATCH(R5,C3:N3,0)
  または XMATCH(R5,C3:N3)で求めます。
- 噴火回数は =INDEX(C4:N23,XMATCH(R4,B4:B23),XMATCH(R5,C3:N3)) で求めることができます。

VLOOKUP関数で求める

VLOOKUP関数を主に使って求めることもできます。
構文:=VLOOKUP(検索値,範囲,列番号,[検索の型])
VLOOKUP関数に説明がありますので参照してください。
=VLOOKUP(R4,B4:N23,XMATCH(R5,B3:N3),FALSE) となります。
FALSE は 0 としてもOKです。
XMTCH関数の部分は MATCH(R5,B3:N3,0) でもOKです。
- VLOOKUP関数の範囲がB4:N23、XMATCH関数の範囲がB3:N3 になっていますのでご注意ください。

HLOOKUP関数で求める

HLOOKUP関数を主に使って求めることもできます。
- 構文:=HLOOKUP(検索値,範囲,行番号,[検索の型])
  HLOOKUP関数に説明がありますので参照してください。
=HLOOKUP(R5,C3:O23,XMATCH(R4,B3:B23),FALSE) となります。
XMATCH関数の部分は MATCH(R4,B3:B23,0) でもOKです。

XLOOKUP関数で求める

Excel for Microsoft365、Excel2021ではXLOOKUP関数が使えます。
構文:=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
XLOOKUP関数に説明がありますので参照してください。
R6セルに
=XLOOKUP(R4,B4:B23,
XLOOKUP(R5,C3:N3,C4:N23))
と入力します。

(追記) (追記ここまで)

問題2の解答例 topへ

INDEX、SUMPRODUCT関数などで求める

S6セルには
=INDEX(D3:P30,
SUMPRODUCT((B3:B30=S3)*(C3:C30=S5)*ROW(A1:A28)),
MATCH(S4,D2:P2,0))
と入力しました。
【解説】
- INDEX関数で行位置と列位置を求めることができれば、上記の問題1と同じように計算ができます。
  INDEX(D3:P30,行位置、列位置)
- 行位置に2つの条件が有るので、行位置を求めるためにはSUMPRPDUCT関数が利用できます。
  SUMPRODUCT((B3:B30=S3)*(C3:C30=S5)*ROW(A1:A28)) としています。
  (B3:B30=S3)*(C3:C30=S5)とすることで、一致したところがTRUEとなりますので、行位置の1～28を掛け合わせるようにして、
  SUMPRODUCT((B3:B30=S3)*(C3:C30=S5)*ROW(A1:A28))
  で求めることができます。
  - 行位置の1～28をROW(A1:A28)として計算できるようにするのがポイントです。
  S3セルが「2012年」、S5セルが「降灰量」と入力されている場合は、下表のように計算されます。
  行位置「9」が得られることになります。(ワークシートではTRUE=1、FALSE=0 として計算されます)
- 列位置はMATCH関数で MATCH(S4,D2:P2,0) として、求めることができます。
- (B3:B30=S3) (C3:C30=S5) ROW(A1:A28) SUMPRODUCT((B3:B30=S3)*
  (C3:C30=S5)*ROW(A1:A28))
  
  2015 降灰量
  
  2017 FALSE 降灰量 TRUE 16 FALSE*TRUE*16=0*1*16=0
  
  2016 FALSE 噴火回数 FALSE 17 FALSE*FALSE*17=0*0*17=0
  
  2016 FALSE 降灰量 TRUE 18 FALSE*TRUE*18=0*1*18=0
  
  2015 TRUE 噴火回数 FALSE 19 TRUE*FALSE*19=1*0*19=0
  
  2015 TRUE 降灰量 TRUE 20 TRUE*TRUE*20=1*1*20=20
  
  2014 FALSE 噴火回数 FALSE 21 FALSE*FALSE*21=0*0*21=0
  
  2014 FALSE 降灰量 TRUE 22 FALSE*TRUE*22=0*1*22=0
  
  2013 FALSE 噴火回数 FALSE 23 FALSE*FALSE*23=0*0*8=0
  
  2013 FALSE 降灰量 TRUE 24 FALSE*TRUE*24=1*0*9=0

XLOOKUP関数で求める

Excel for Microsoft365,Excel2021ではXLOOKUP関数やスピルの機能が使えるようになりました。
VLOOKUP関数で2つの条件で検索することができませんが、XLOOKUP関数では可能です。
XLOOKUP関数の使い方は XLOOKUP関数で範囲や配列を検索するをご覧ください。
S6セルに
=XLOOKUP(S4,D2:O2,
XLOOKUP(S3&S5,B3:B30&C3:C30,D3:O30))
と入力します。
XLOOKUP関数では引数にS3&S5 B3:B30&C3:C30 と指定できるのがポイントです。
【解説】
XLOOKUP(S3&S5,B3:B30&C3:C30,D3:O30)
で年と区分が一致するデータを配列で取り出しています。
次式のように、この配列から「月」が一致するデータを検索しています。
=XLOOKUP(S4,D2:O2,
XLOOKUP(S3&S5,B3:B30&C3:C30,D3:O30))

スポンサードリンク
(追記) (追記ここまで)

(追記) (追記ここまで)

Home|エクセル練習問題:目次|行と列の検索

PageViewCounter
Counter
Since2006/2/27