Deprecated: The each() function is deprecated. This message will be suppressed on further calls in /home/zhenxiangba/zhenxiangba.com/public_html/phproxy-improved-master/index.php on line 456
行と列の検索:エクセル練習問題

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

スポンサードリンク

更新: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		降灰量	ROW(A1:A28)	SUMPRODUCT((B3:B30=S3)* (C3:C30=S5)*ROW(A1:A28))
2017	FALSE	降灰量	TRUE	16	FALSETRUE16=0116=0
2016	FALSE	噴火回数	FALSE	17	FALSEFALSE17=0017=0
2016	FALSE	降灰量	TRUE	18	FALSETRUE18=0118=0
2015	TRUE	噴火回数	FALSE	19	TRUEFALSE19=1019=0
2015	TRUE	降灰量	TRUE	20	TRUETRUE20=1120=20
2014	FALSE	噴火回数	FALSE	21	FALSEFALSE21=0021=0
2014	FALSE	降灰量	TRUE	22	FALSETRUE22=0122=0
2013	FALSE	噴火回数	FALSE	23	FALSEFALSE23=008=0
2013	FALSE	降灰量	TRUE	24	FALSETRUE24=109=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