Excel (一般機能)

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

 
(Windows 10 Pro : Excel 2021)
3行一組のデータからの値の取得
投稿日時: 23/10/10 16:06:44
投稿者: み-1108

よろしくお願いいたします。
データベースが1行ではなく3行で一組となっています。
 
   A列    B列    C列
1  =A2    =B2     愛媛
2  1234    みかん   和歌山
3  =A2    =B2     
――――――――――――――――――――――――――
4  =A5    =B5    静岡
5  1234   みかん   熊本
6  =A5    =B5 
――――――――――――――――――――――――――
 
 
別シートで
A1 に 1234 を入力することによって
D1 にみかん F1に愛媛・和歌山
D2 にみかん F2に静岡・熊本
と値を取得したいのです。
 
D1はvlookup F1はindexとmatchの組み合わせで出来たのですが、
D2とF2がわからずにいます。
 
どなたかご教授をお願いします。
 
 

回答
投稿日時: 23/10/10 18:09:06
投稿者: んなっと

●Sheet1
 
   A    B    C
1 1234 みかん  愛媛
2 1234 みかん 和歌山
3 1234 みかん    
4 1235 りんご  青森
5 1235 りんご  長野
6 1235 りんご    
7 1234 ばなな  沖縄
8 1234 ばなな 鹿児島
9 1234 ばなな  宮崎
 
 
●Sheet2
 
   A B C    D   E    F   G
1 1234    みかん 愛媛 和歌山   
2       ばなな 沖縄 鹿児島 宮崎
 
D1
=LET(a,FILTER(Sheet1!B1:C500,Sheet1!A1:A500=A1,""),j,{1,2,3,4},
INDEX(a,3*SEQUENCE(ROUNDUP(ROWS(a)/3,0))-2+(j>1)*(j-2),1+(j>1))&"")
 
 
※宮崎は入れないときは
 
=LET(a,FILTER(Sheet1!B1:C500,Sheet1!A1:A500=A1,""),j,{1,2,3},
INDEX(a,3*SEQUENCE(ROUNDUP(ROWS(a)/3,0))-2+(j>1)*(j-2),1+(j>1))&"")

回答
投稿日時: 23/10/10 18:24:57
投稿者: んなっと

こんなのも。
=INDEX(FILTER(Sheet1!B1:C500,Sheet1!A1:A500=A1,""),
3*SEQUENCE(ROUNDUP(COUNTIF(Sheet1!A:A,A1)/3,0))-2+{0,0,1,2},{1,2,2,2})&""

投稿日時: 23/10/11 09:34:50
投稿者: み-1108

ご回答いただきありがとうございます。
 
私のExcelバージョン選択ミスで、作成のExcelファイルですが、
Excelバージョン2016で使用することもあり
ご教授いただきました関数は使用できないようです。
大変申し訳ありません。
 
2016でも使える関数で教えていただくわけにはいかないでしょうか。
 
よろしくお願いいたします。

回答
投稿日時: 23/10/11 12:07:00
投稿者: んなっと

●Sheet1
  
   A    B    C
1 1234 みかん  愛媛
2 1234 みかん 和歌山
3 1234 みかん    
4 1235 りんご  青森
5 1235 りんご  長野
6 1235 りんご    
7 1234 ばなな  沖縄
8 1234 ばなな 鹿児島
9 1234 ばなな  宮崎
  
●Sheet2
  
右のほうあいているところ(下の例ではG列)に作業列
  
G1
=IFERROR(AGGREGATE(15,6,ROW(Sheet1!$A$1:$A$500)/(Sheet1!$A$1:$A$500=$A$1)/(MOD(ROW($A$1:$A$500),3)=1),ROW(G1)),"")
下方向・↓
  
   A B C    D E         F G
1 1234    みかん     愛媛,和歌山 1
2       ばなな   沖縄,鹿児島,宮崎 7
  
D1
=IF(G1="","",INDEX(Sheet1!B:B,G1))
下方向・↓
F1
=IF(G1="","",SUBSTITUTE(TRIM(INDEX(Sheet1!C:C,G1)&" "&INDEX(Sheet1!C:C,G1+1)&" "&INDEX(Sheet1!C:C,G1+2))," ",","))
下方向・↓

投稿日時: 23/10/18 12:08:07
投稿者: み-1108

お礼が大変遅くなり申し訳ありません。
実際のファイルで作業をした際うまくいかず、数式の内容を確認するため
分割しながらすすめたところ、本日ほしい値を拾うことができました。
 
本当にありがとうございました。