Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : その他)
連動する入力規則(5段階)横から縦に連動するように変更したい
投稿日時: 23/10/18 04:36:37
投稿者: tako552101

長文ですので不快に思われる方は読み飛ばしてください。申し訳ありません。
Win10、ver2010です。
 
最終的にはsheet2のA2をリスト選択決定すると、次のB2にはA2によって絞られたリストから選択、次の…というように連動する5段階の入力規則の数式を何年か前に本サイトでご指導いただき作動中です(残念ながら数式全てを読解できるスキルに至っていません)。
この程、A2 → B2 → C2 → D2 → E2と横に連動するセル位置を、B1 → B2 → B3 → B4 → B5のように縦に連動するように変更したく、相当格闘したのですが、うまくいきませんでした。
お分かりになる方ご指導いただけないでしょうか。
 
●Sheet1  ※600行程度

基データ
	A	B	C	D	E
1
2	分類	性別	種類	Gr	長さ
3	A	男子	○	あ	100
4	A	男子	○	あ	200
5	A	男子	○	あ	300
6	A	男子	△	あ	110
7	A	男子	△	あ	210
8	A	男子	△	い	100
9	A	女子	○	あ	50
10	A	女子	○	あ	60
11	A	女子	×	あ	70
12	A	女子	×	い	105
13	A	女子	△	う	300
14	A	女子	□	う	310
15	B	男子	○	い	100
16	B	男子	×	い	200
17	B	男子	×	い	300
18	B	女子	□	あ	110
19	B	女子	□	い	210
20	B	女子	□	う	100
21	C	男子	○	あ	50
22	C	男子	○	あ	70
23	C	男子	○	い	60
24	C	男子	△	あ	105
25	C	男子	△	あ	300
26	C	女子	△	い	310
27	C	女子	○	う	50
28	C	女子	○	う	60
29	C	女子	×	う	70
30
31

作業列追加
	G	H	I	J	K	L	M	N	O	P
1						A	男子	○	あ	100
2	分類	性別	種類	Gr	長さ	B	女子	△	あ	200
3	1	1	1	1	1	C	男子	○	い	300
4	1	1	1	1	2	0	女子	×	あ	110
5	1	1	1	1	3		男子	△	あ	210
6	1	1	2	2	4		女子	□	い	100
7	1	1	2	2	5		0	○	う	50
8	1	1	2	3	6			×	う	60
9	1	2	3	4	7			□	い	70
10	1	2	3	4	8			○	い	105
11	1	2	4	5	9			△	あ	300
12	1	2	4	6	10			△	い	310
13	1	2	5	7	11			○	う	100
14	1	2	6	8	12			×	あ	200
15	2	3	7	9	13			0	い	300
16	2	3	8	10	14				あ	110
17	2	3	8	10	15				い	210
18	2	4	9	11	16				う	100
19	2	4	9	12	17				う	50
20	2	4	9	13	18				0	70
21	3	5	10	14	19					60
22	3	5	10	14	20					105
23	3	5	10	15	21					300
24	3	5	11	16	22					310
25	3	5	11	16	23					50
26	3	6	12	17	24					60
27	3	6	13	18	25					70
28	3	6	13	18	26					0
29	3	6	14	19	27					
30	4	7	15	20	28					
31	4	7	15	20	28					
					
G3
=N(G2)+IF(COLUMN(A:A)=1,A2<>A3,OR(INDEX($A2:A2<>$A3:A3,)))
右方向・→K3まで 下方向・↓(少し余裕を持って)

※後に以下に変更(正常に動作中)
=N(G2)+(SUMPRODUCT(1*($A2:A2<>$A3:A3))>0)

L1
=IFERROR(INDEX(A:A,MATCH(ROW(1:1),G:G,0)),"")
右方向・→P1まで 下方向・↓

※後に以下に変更(正常に動作中)
=IFERROR(INDEX(A$3:A$502,MATCH(ROW(1:1),G$3:$G$502,0)),"")

 
●Sheet2
	A	B	C	D	E
1	分類	性別	種類	Gr	長さ
2

 A2選択
→[データ]
→[データの入力規則]
→入力値の種類:
リスト▼
 元の値:
=OFFSET(Sheet1!L$1,,,COUNTIF(Sheet1!L:L,"?*"))
→OK
	A	B	C	D	E
1	分類	性別	種類	Gr	長さ
2	A

B2選択
→[数式]
→[名前の定義]
→名前:bango
参照範囲:
=MATCH(0,MMULT((Sheet1!$A$1:A$1000<>!$A2:A2)*1,ROW(INDIRECT("1:"&COLUMN(!A:A)))),0)
→OK  ※まずはここでうまくいきません。
 ※MMULT関数はデータ数が多いと実用に耐えないので、1000行に限定しています。

→名前:hanni
参照範囲:
=INDEX(Sheet1!M:M,INDEX(Sheet1!H:H,bango,0)):INDEX(Sheet1!M:M,VLOOKUP(INDEX(Sheet1!G:G,bango,0)+1,Sheet1!G:H,2,FALSE)-1)
→OK

[データ]
→[データの入力規則]
→入力値の種類:
リスト▼
 元の値:
=hanni
→OK
	A	B	C	D	E
1	分類	性別	種類	Gr	長さ
2	A	女子

 B2をE2までコピー
	A	B	C	D	E
1	分類	性別	種類	Gr	長さ
2	A	女子	×	い	105

 必要なだけ下方向にコピー
	A	B	C	D	E
1	分類	性別	種類	Gr	長さ
2	A	女子	×	い	105
3	A	男子	△	あ	110

 
おかげさまで以上のように連動する5段階の入力規則が完成し通常動作中ですが、事情があって入力規則位置を横位置から縦位置に変更したく試行錯誤していますが、うまくいきません。設定はB列のみです。
 
     A        B
1    分類  ※入力規則セル  ※ OK
2    性別  ※入力規則セル  ※ OK ※男子、女子の2種限定なので上記数式は未使用
3    種類  ※入力規則セル  ※ ×
4    Gr   ※入力規則セル  ※ ×
5    長さ  ※入力規則セル  ※ ×
 
ご指導いただけますと大変助かります。
追伸
おそらく前述のコードは1か所設定し、右、そして下にコピーできるよう組んであると思うのですが、個々に設定した場合にどのような数式になるのか解説いただけますと、なおありがたいです。
 
よろしくお願いします。

回答
投稿日時: 23/10/19 21:08:40
投稿者: んなっと

   A   B
1 分類   A
2 性別 女子 ★
3 種類  ×
4  Gr  い
5 長さ  105
  
 B2(★)を選択して
  
[1]数式
→名前の定義
→名前:
Arr
 参照範囲:
=MMULT((OFFSET(Sheet1!$A$1:$A$1000,,,,ROWS(!B$1:B1))<>TRANSPOSE(!B$1:B1))*1,ROW(INDIRECT("1:"&ROWS(!B$1:B1))))
→OK
  
[2]数式
→名前の定義
→名前:
Arry
 参照範囲:
=OFFSET(Sheet1!$L$1,OFFSET(Sheet1!$G$1,MATCH(0,Arr,0)-1,ROWS(!B$1:B1))-1,ROWS(!B$1:B1),OFFSET(Sheet1!$G$1,MATCH(0,Arr,0)+COUNT(0/(Arr=0))-2,ROWS(!B$1:B1))-OFFSET(Sheet1!$G$1,MATCH(0,Arr,0)-1,ROWS(!B$1:B1))+1)
→OK
  
[3]入力規則:リスト▼:元の値:
=Arry
  
[4]B2を下方向コピー

回答
投稿日時: 23/10/20 14:23:40
投稿者: Mike

tako552101 さんの引用:
A2 → B2 → C2 → D2 → E2と横に連動するセル位置を、B1 → B2 → B3 → B4 → B5のように縦に連動するように変更したく、…
》 A2 → B2 → C2 → D2 → E2と横に連動するセル位置を、
》 B1 → B2 → B3 → B4 → B5のように縦に連動するように変更したく、…
下記図表参照(Excel 2019)
               Sheet2
Sheet1             A B
  A  B  C  D  E    1  分類  ← Sheet2!B1: =OFFSET(Sheet1!A$2,,ROW(A1)-1)
2 分類 性別 種類 Gr 長さ   2  性別
               3  種類
               4  Gr
               5  長さ
 

投稿日時: 23/10/24 20:23:23
投稿者: tako552101

みなさんありがとうございます。
検証に時間がかかっていました。申し訳ありません。
結論から申し上げますとうまくいきませんでした。以下、情報を修正させてください。
 
わかりやすくするために、Sheet1のA列は、見出しが2行目、データの始まりが3行目〜600行程度と記載しましたが、実際にはA列以降は、AZ2が見出しでAZ3からBJ600までデータ行(以降列は右方向に連続しています)L列以降は見出しなしのBK1〜BO600です。
 
また、Sheet2は分類がB3から始まり、以下のレイアウトです。
   A   B    C    D     E    F
1                      ※表題など
2                      ※表題など
3 分類  [              ▼] ※B〜F結合セル
4 性別  [ ▼]              ※B4のみ
5 種類  [              ▼] ※B〜F結合セル
6 Gr   [              ▼] ※B〜F結合セル
7 長さ  [              ▼] ※B〜F結合セル 
 
Mikeさん
B1の入力規則をご指摘のように修正しましたが、見出し部分の1行目しかリストに表示されません。
=OFFSET(コースデータ!AZ$2,,ROW(A3)-1)
 
 
んなっとさん、ありがとうございます。
以下のように修正しましたが、リスト非表示でした。
 
B2を選択して以下名前定義
Arr
=MMULT((OFFSET(Sheet1!$AZ$1:$AZ$1000,,,,ROWS(!B$3:B3))<>TRANSPOSE(!B$3:B3))*1,ROW(INDIRECT("3:"&ROWS(!B$3:B3))))
Arry
=OFFSET(Sheet1!$BK$1,OFFSET(Sheet1!$BF$1,MATCH(0,Arr,0)-1,ROWS(!B$3:B3))-1,ROWS(!B$3:B3),OFFSET(Sheet1!$BF$1,MATCH(0,Arr,0)+COUNT(0/(Arr=0))-2,ROWS(!B$3:B3))-OFFSET(Sheet1!$BF$1,MATCH(0,Arr,0)-1,ROWS(!B$3:B3))+1)
 
よろしくお願いします。

回答
投稿日時: 23/10/24 21:41:37
投稿者: んなっと

   A   B
3 分類   A
4 性別 男子 ★
5 種類  ○
6  Gr  あ
7 長さ  200
 
 B4(★)を選択して
   
[1]数式
→名前の定義
→名前:
Arr
 参照範囲:
=MMULT((OFFSET(Sheet1!$A$1:$A$1000,,,,ROWS(!B$3:B3))<>TRANSPOSE(!B$3:B3))*1,ROW(INDIRECT("1:"&ROWS(!B$3:B3))))
→OK
   
[2]数式
→名前の定義
→名前:
Arry
 参照範囲:
=OFFSET(Sheet1!$L$1,OFFSET(Sheet1!$G$1,MATCH(0,Arr,0)-1,ROWS(!B$3:B3))-1,ROWS(!B$3:B3),OFFSET(Sheet1!$G$1,MATCH(0,Arr,0)+COUNT(0/(Arr=0))-2,ROWS(!B$3:B3))-OFFSET(Sheet1!$G$1,MATCH(0,Arr,0)-1,ROWS(!B$3:B3))+1)
→OK
   
[3]入力規則:リスト▼:元の値:
=Arry
   
[4]B4を下方向コピー
 
   A   B
3 分類   A
4 性別 男子 ★
5 種類  ○ ★
6  Gr  あ ★
7 長さ  200 ★

回答
投稿日時: 23/10/25 21:21:33
投稿者: Mike

tako552101 さんの引用:
Mikeさん
B1の入力規則をご指摘のように修正しましたが、見出し部分の1行目しかリストに表示されません。
=OFFSET(コースデータ!AZ$2,,ROW(A3)-1)
「ご指摘のように修正し」たら、セル B1 を下方にズズーッとオートフィルしましたか?
ところで、唐突に登場した「=OFFSET(コースデータ!AZ$2,,ROW(A3)-1)」の「コースデータ」とか「ROW(A3)」の「ROW(A3)-1)」って何の意味があるのですか?さては、私の式中の ROW(A1)-1 の意味を理解されていないようで…。OFFSET関数の使い方は難しそうなので、んなっとさんからの回答で頑張ってください。
サ・ヨ・ナ・ラ

投稿日時: 23/10/31 07:18:02
投稿者: tako552101

返信が大分遅くなり申し訳ありません。
 
結果、んなっとさんの式でうまくいきました。
ありがとうございました。