엑셀 2024 함수 마스터, 특별 챌린지 2일차

=LET(a,1,b,2,a+b)

=LET(View1,10000,View3,25000,View3*(2+0.2*(View3/View1)+1.8*(View1/View3)+3*(View3/View1)))

=TEXTBEFORE(I10," ")

=LET(국가번호,TEXTBEFORE(I10:I17," "),
국가,XLOOKUP(국가번호,M10:M15,N10:N15),
국가번호&국가)

당초 =IF(SUM(Q10:S10)>=100,SUM(Q10:S10)*1.1,SUM(Q10:S10))

변경 =LET(합계,SUM(Q10:S10),IF(합계>=100,합계*1.1,합계))



=LAMBDA(값1,값2,값1+값2)

=MYSUM(B10,C10)

=SQ(B23

=IF(ISODD(MID(I10,8,1)),"남성","여성")

=FINDGENDER(I10:I16)



=TEXT(F10:F15,"000-0000-0000")

=PhoneNo(F10:F15)



=TEXTSPLIT(B10,">")

=TEXTSPLIT(TEXTJOIN("/",,B10:B15),">","/")

=IFERROR(TEXTSPLIT(TEXTJOIN("/",,B10:B15),">","/"),"")

=TEXTSPLIT(TEXTJOIN("/",,G10:G13),":","/")

=IFERROR(TEXTSPLIT(TEXTJOIN("/",,G10:G13),":","/")*1,TEXTSPLIT(TEXTJOIN("/",,G10:G13),":","/"))

=SORT(IFERROR(TEXTSPLIT(TEXTJOIN("/",,G10:G13),":","/")*1,TEXTSPLIT(TEXTJOIN("/",,G10:G13),":","/")),2,-1)



'=LAMBDA(종목코드,IMAGE("https://ssl.pstatic.net/imgfinance/chart/mobile/mini/"&TEXT(종목코드,"000000")&"_end_up_tablet.png"))



 

=TAKE(SORT(B10:D19,3,-1),3)

=TAKE(SORT(FILTER(J10:K39,(L10:L39=O8)*(J10:J39>=N8)),1,1),5)



=DROP(DROP(SORT(DROP(B10:C20,-1),2,-1),2),-2)

=AVERAGE(CHOOSECOLS(DROP(DROP(SORT(DROP(B10:C20,-1),2,-1),2),-2),2))

=AVERAGE(TAKE(DROP(DROP(SORT(DROP(B10:C20,-1),2,-1),2),-2),,-1))

=AVERAGE(DROP(DROP(DROP(SORT(DROP(B10:C20,-1),2,-1),2),-2),,1))

=AVERAGE(INDEX(DROP(DROP(SORT(DROP(B10:C20,-1),2,-1),2),-2),,2))

 



=UNIQUE(K10:K24)

=SUMIF(K10:K24,O10#,M10:M24)

=LET(구분범위,K10:K24,
금액범위,M10:M24,
구분목록,UNIQUE(구분범위),
집계범위,SUMIF(구분범위,구분목록,금액범위),
HSTACK(구분목록,집계범위))



=MYREPORT(INDEX(N3#,,3),INDEX(N3#,,7))