컴퓨터
구글스프레드시트) 열마다 흩어져 있는 값을 불러오는 법?
조회 1,522 · 공감 0 · 댓글 15
질문의 요지
여러 열에 랜덤하게언급된 기관명을 찾아, 그 기관명에 해당되는 상세사유를 불러오는 방법이 있을까요?
샘플 파일은 위 링크에 있습니다
구글설문을 통해, 1차부터 10차 순서대로 기관명과 관련 내용을 받고 있는데
이걸 하나로 모아서, 각 기관별 리스트를 만드는게 취지입니다.
A기관이 1차에 언급될 수 도있고, 7차에 언급될 수도 있고... 랜덤으로
U2셀
=BYROW($B$2:$S,lambda(a,countif(a,U1)))
A기관이 몇번 언급됐는지? 보려는 목적입니다. (1 이상 되면 응답자 실수라서 조건부 서식으로 표기할 예정)
W2셀
=BYROW($B$2:$S,lambda(a,xlookup(U1,a,a)))
A기관이 언급 되었다면 A기관이라고 명칭을 불러오라는 의미 (#N/A 값에 iferror 이 안먹혀서 못 없애는 중 ㅜㅜ)
주된질문
1. 일시: V2, AA2, AG2, AL2 등... (분홍색 표기)
2. 상세내용: X2, AC2, AI2 등... (노랑색 표기)
에 어떤 수식을 넣으면 불러올 수 있을까요?
댓글 15
이 수식은 문제가 있습니다.
원래부터 상세내용까지 동시에 체크하려는 것이 목적이라면 몰라도, 기관명 안에서만 카운트하려는 것이라면 오작동의 우려가 있네요.
=BYROW($B$2:$S,lambda(a,countif(MAP(a,SEQUENCE(1,COLUMNS(a)),LAMBDA(r,idx,IF(MOD(idx,2)=1,r,""))),U1)))
이렇게 고치는 것이 좋을 듯 합니다.
그리고 NA 걸러내는 함수로는 IFNA 가 있습니다. 이거 쓰세요.
동시에, 기관명을 불러올 때 상세내용을 걸러내려면, 위와 같은 방식으로
=BYROW($B$2:$S,LAMBDA(a,LET(rdata,MAP(a,SEQUENCE(1,COLUMNS(a)),LAMBDA(r,idx,IF(MOD(idx,2)=1,r,""))),IFNA(XLOOKUP(U1,rdata,rdata),"-"))))
이러면 될 것 같고..
상세내용은
=BYROW($B$2:$S,LAMBDA(a,LET(data1,HSTACK("",MAP(a,SEQUENCE(1,COLUMNS(a)),LAMBDA(r,idx,IF(MOD(idx,2)=1,r,"")))),data2,HSTACK(MAP(a,SEQUENCE(1,COLUMNS(a)),LAMBDA(r,idx,IF(MOD(idx,2)=0,r,""))),""),IFNA(XLOOKUP(U1,data1,data2),"-"))))
이렇게 값을 가져오면 되겠네요.
그리고 날짜는 그냥
=ARRAYFORMULA($A$2:$A)
이렇게만 해도 됩니다..
안녕하세요~ 알려주신 수식을 이용해서 잘 사용하다 설문 항목이 추가되면서
홀수만 반환하고 짝수는 반환하지 못하는문제가 있습니다.
IF(MOD(idx,2)=1,r,"")
이 수식이 적절하지 않은 상황이 되버렸는데 머리를 싸매다가 도저히 안되어 또 도움을 구해봅니다 ㅜㅜ
어떻게 응용하면 좋을까요?
https://docs.google.com/spreadsheets/d/1urTf_250qY-GuYEbDF-lOLMXk3MG8gpVMxXsy2-B-6w/edit#gid=1755696079
규칙성이 없이 불규칙한 패턴으로 데이터가 나타난다면, 홀짝이나 n번째 데이터를 취하는 방법으론 안되고,
라벨을 읽어서 하는 방법을 쓰면 됩니다.
=BYROW($B$2:$S,LAMBDA(a, LET(rdata,MAP(a,$B$1:$S$1,LAMBDA(r,la,IF(la="기관명",r,""))),IFNA(XLOOKUP(U1,rdata,rdata),"-"))
1열 라벨에 '기관명'이 있는 데이터만 모으도록 바꾼 수식입니다.
=BYROW($B$2:$S,LAMBDA(a,LET(data1,HSTACK("",MAP(a,$B$1:$S$1,LAMBDA(r,la,IF(la="기관명",r,"")))),data2,HSTACK(MAP(a,$B$1:$S1,LAMBDA(r,la,IF(la="상세내용",r,""))),""),IFNA(XLOOKUP(U1,data1,data2),"-"))))
같은 방법으로 상세내용도 모으면 됩니다.
정말죄송하지만, 위스프레드 시트 링크에서 알려주신 수식대로 해도 안되길래 범위를 바꾸고, aa1 로 수정해봤지만 잘 안되는 것 같습니다. ㅜㅜ
ac2 =
BYROW($B$2:$Y,LAMBDA(a, LET(rdata,MAP(a,$B$1:$Y$1,LAMBDA(r,la,IF(la="기관명",r,""))),IFNA(XLOOKUP(AA1,rdata,rdata),"-"))))
ad2 =
=BYROW($B$2:$Y,LAMBDA(a,LET(data1,HSTACK("",MAP(a,$B$1:$Y$1,LAMBDA(r,la,IF(la="기관명",r,"")))),data2,HSTACK(MAP(a,$B$1:$Y1,LAMBDA(r,la,IF(la="상세내용",r,""))),""),IFNA(XLOOKUP(AA1,data1,data2),"-"))))
이렇게 했는데 안되더라고요
라벨을 기관명(숫자)로 써야한다면
la="기관명" 을 LEFT(la,3)="기관명" 로 바꾸세요.
BYROW($B$2:$Y,LAMBDA(a, LET(rdata,MAP(a,$B$1:$Y$1,LAMBDA(r,la,IF(la="기관명",r,""))),IFNA(XLOOKUP(AA1,rdata,rdata),"-"))))
를
BYROW($B$2:$Y,LAMBDA(a, LET(rdata,MAP(a,$B$1:$Y$1,LAMBDA(r,la,IF(LEFT(la,3)="기관명",r,""))),IFNA(XLOOKUP(AA1,rdata,rdata),"-"))))
이렇게..
알려주신 내용을 토대로 실제로 2개의 스프레드시트에서 사용중인 함수입니다.
함수 내용은 보시면 바로 아시겠지만, (제가 메모하는 차원에서 굳이 설명하자면)
3,4번은 기존에 알려주신 방법대로 IF(MOD(idx,2)=1,r,"") 방식으로 홀짝이 맞아서 잘 사용중인 함수이며
1,2번은 홀짝으로 해결되지 않아 변형한 함수 입니다.
2번은 잘 되지만 1번은 안되는데 원인을 알 수 있을까요?
1
=BYROW('R'!$R$2:$AO,LAMBDA(a,countif(MAP(a,$R$1:$AO$1,LAMBDA(r,la,IF( right(la,5)="거부 기관" ,r,""))),P1)))
2
=BYROW('R'!$R$2:$AO,LAMBDA(a,LET(rdata,MAP(a,$R$1:$AO$1,LAMBDA(r,la,IF( right(la,5)="거부 기관" ,r,""))),IFNA(XLOOKUP(P1,rdata,rdata),"-"))))
3
=BYROW('R'!$T$2:$BF,LAMBDA(a,countif(MAP(a,SEQUENCE(1,COLUMNS(a)),LAMBDA(r,idx,IF(MOD(idx,2)=1,r,""))),P1)))
4
=BYROW('R'!$T$2:$BF,LAMBDA(a,LET(rdata,MAP(a,SEQUENCE(1,COLUMNS(a)),LAMBDA(r,idx,IF(MOD(idx,2)=1,r,""))),IFNA(XLOOKUP(P1,rdata,rdata),"-"))))
또한 5번 함수는
"거부 기관" 바로 다음 열에"거부 사유"가 이어지지 않으면 작동되지 않아 애를 먹다가 결국은 구글 설문 응답의 열 순서를 강제로 바꿔버려서 해결했습니다 (거부기관 바로 우측열에 거부사유가 오도록)
5
=BYROW('R'!$R$2:$AP,LAMBDA(a,LET(data1,HSTACK("",MAP(a,'R'!$R$1:$AP$1,LAMBDA(r,la,IF( right(la,5)="거부 기관" ,r,"")))),data2,HSTACK(MAP(a,'R'!$R$1:$AP$1,LAMBDA(r,la,IF( RIGHT(la,5)="거부 사유" ,r,""))),""),IFNA(XLOOKUP(P1,data1,data2),"-"))))
6
=BYROW('R'!$T$2:$BF,LAMBDA(a,LET(data1,HSTACK("",MAP(a,SEQUENCE(1,COLUMNS(a)),LAMBDA(r,idx,IF(MOD(idx,2)=1,r,"")))),data2,HSTACK(MAP(a,SEQUENCE(1,COLUMNS(a)),LAMBDA(r,idx,IF(MOD(idx,2)=0,r,""))),""),IFNA(XLOOKUP(P1,data1,data2),"-"))))
=BYROW('R'!$R$2:$AO,LAMBDA(a,countif(MAP(a,'R'!$R$1:$AO$1,LAMBDA(r,la,IF( right(la,5)="거부 기관" ,r,""))),P1)))
시트명을 안넣어서 생긴 문제였습니다 ㅜㅜ
해결했습니다. 늘 감사합니다.
안녕하세요 오랫만에 또 문의드립니다. 기존에 알려주신 방법을 활용해서 아래와 같이 수식을 사용하고 있습니다.
설문지 형식으로, 답변이 기록되는 시트 즉, R 시트에는 T열 부터 BF열 까지 열(칼럼)로 구분되어있습니다.
(1차부터 10차수 까지 거부기관과 그사유가 표기되는 구조)
T열: 1차 거부 기관
U열: 1차 거부 사유
V열 1차 기타 사유
W열: 2차 거부기관이 있습니까?
X열: 2차 거부 기관
Y열: 2차 거부 사유
Z열: 2차 기타 사유
AA열: 3차 거부기관이 있습니까?
.
.
BF열: 10차 기타 사유
아래수식을 사용하면, 현재 시트의 P1에 있는 기관명을 찾아, 거부 사유를 출력할 수 있는데, 거부 사유가 "기타" 인 경우, 그 다음 칼럼에 있는 "기타 사유" (V열, Y열...BF열)를 출력하는 수식으로 바꿔보고 싶은데, 도저히 안되어 문의드려봅니다.
=BYROW('R'!$T$2:$BF, LAMBDA(a,
LET(data1, HSTACK("", MAP(a, SEQUENCE(1, COLUMNS(a)), LAMBDA(r, idx, IF(MOD(idx, 2) = 1, r, "")))),
data2, HSTACK(MAP(a, SEQUENCE(1, COLUMNS(a)), LAMBDA(r, idx, IF(MOD(idx, 2) = 0, r, ""))),""),
IFNA(XLOOKUP(P1, data1, data2), "-"))
)
)
=BYROW($T$2:$BF100,
LAMBDA(a,LET(targetname,P1,larange,$T$1:$BF1,
larex,"^\d+차거부기관$",
data1,MAP(a,larange,LAMBDA(r,la,IF(AND(REGEXMATCH(la,larex),targetname=r),OFFSET(r,0,1),""))),data2,MAP(a,larange,LAMBDA(r,la,IF(AND(REGEXMATCH(la,larex),targetname=r),OFFSET(r,0,2),""))),data1f,CHOOSECOLS(IFNA(FILTER(data1,data1<>""),"-"),1),data2f,CHOOSECOLS(IFNA(FILTER(data2,data2<>""),"-"),1),IF(data1f="기타",data2f,data1f))))
첫 범위에 데이터 범위
targetname은 찾을데이터..
larange는 제목의 범위
larex는 n차거부기관 만 추출해내는 정규식
이런식으로 제목값을 찾아서 1번째, 1번째가 기타면 2번째 오른쪽 값 가져오는 식으로 짜야겠네요.
안되네요 혹시 어디가 잘못 됐을까요? (p1에 있던 기관명은 열을 추가하면서 q1으로 바꿨습니다. )
=BYROW('R'!$T$2:$BF,
LAMBDA(a,LET(targetname,Q1,larange,'R'!$T$1:$BF1,
larex,"^\d+차거부기관$",
data1,MAP(a,larange,LAMBDA(r,la,IF(AND(REGEXMATCH(la,larex),targetname=r),OFFSET(r,0,1),""))),data2,MAP(a,larange,LAMBDA(r,la,IF(AND(REGEXMATCH(la,larex),targetname=r),OFFSET(r,0,2),""))),data1f,CHOOSECOLS(IFNA(FILTER(data1,data1<>""),"-"),1),data2f,CHOOSECOLS(IFNA(FILTER(data2,data2<>""),"-"),1),IF(data1f="기타",data2f,data1f))))
원인을 찾았습니다.
larex,"^\d+차거부기관$",
-> 단순 띄어쓰기 오류였습니다.
larex,"^\d+차 거부 기관$",
정말 감사합니다^^
=LET(
targetname,P1,
larange,$T$1:$BF1,
larex,"^\d+차거부기관$",
datarange,$T$2:$BF100,
ladata,MAP(larange,LAMBDA(la,IF(REGEXMATCH(la,larex),true, false))),BYROW(datarange,LAMBDA(a, LET(data1,MAP(a,ladata,LAMBDA(r,latf,IF(AND(latf,targetname=r),if(OFFSET(r,0,1)="기타",OFFSET(r,0,2),OFFSET(r,0,1)),""))),CHOOSECOLS(IFNA(FILTER(data1,data1<>""),"-"),1)))))
이 수식으로 하면, 연산량이 줄어서 좀 더 많은 데이터가 빨리 처리 될거예요.