엑셀 2024 함수마스터 2일차

자료를 바탕으로 기억나게 정리

 

1. LET

- LET ( 이름1, 값1, [이름2], [값2], … , 계산식 )

 - 공식이 복잡하면 복잡할수록, 실전이 복잡하면 복잡할수록 속도 및 처리 개선

- 빠른채우기 : Ctrl + E

- TEXTBEFORE(전체범위 가능)

- LET(a,1,b,2,c,3,a+b+c)

- 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,합계)) : 중복방지

 

2. LAMBDA (★★★★★) - LET과 LAMBDA 함수는 엑셀 에이스로 가는 길에 반드시 필요

- LAMBDA(나만의인수)

- LAMBDA(값1,값2,값1+값2)(테스트인수)로 테스트 가능

- 수식 - 이름관리자 혹은 Ctrl + F3에 등록

- 인수인계에 좋음

- IF(ISODD(MID(G10,8,1)),"남성","여성")

- FINDGENDER(주민번호) = LAMBDA(주민번호,IF(ISODD(MID(주민번호,8,1)),"남성","여성"))

 

3. TEXT - 실무에서 텍스트 함수를 얼마나 잘 사용하느냐에 따라 자료의 질이 달라짐

- 날짜의 숫자는 1900년 1월 1일부터 1씩 증가

- B10&"("&TEXT(C10,"YYYY-MM-DD(AAA)")&")"

- B14&"("&TEXT(C14,"#,##0")&")"

- 동적배열도 사용 가능

- [DBNUM4] 숫자를 한글로 표시형식

- PHONENO(연락처) = LAMBDA(연락처,TEXT(연락처,"000-0000-0000"))

 

4. TEXTSPLIT - 데이터 전처리에 엄청난 효과를 가져올 수 있다.

1) 기초다지기

- TEXTJOIN("/",,B10:B15)

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

 - 동적배열함수의 제한이자 불편한점 : 더블클릭 자동채우기가 안됨, 따라서 아래처럼 하면됨

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

 

2) 실전활용

- TEXTJOIN("/",,G10:G15)

- TEXTSPLIT(TEXTJOIN("/",,G10:G15),":","/")

 - 위 데이터는 이대로 쓸수가 없음 WHY???? 텍스트라서

- SORT(TEXTSPLIT(TEXTJOIN("/",,G10:G15),":","/"),2,-1)로 확인해보면 정렬이 안됨

- 따라서 아래와 같이 바꾸자

- TEXTSPLIT(TEXTJOIN("/",,G10:G15),":","/")*1 : 숫자로 만들기

- IFERROR(TEXTSPLIT(TEXTJOIN("/",,G10:G15),":","/")*1,TEXTSPLIT(TEXTJOIN("/",,G10:G15),":","/"))

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

 

5. LAMBDA 미션

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

- STOCKCHART("005930")

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

 

 1) 미션 - CLEANDATA(F9:F14) = LAMBDA(데이터,TEXTSPLIT(TEXTJOIN(",",,데이터),"/",","))

 

 

6. 배열계산 - 배열의 원리만 이해하면, 실무에서 발생하는 어떠한 복잡한 과정도 함수로 처리할 수 있다.

- TRUE=1, FALSE=0

- 모든조건을 만족 : AND - 곱셈, 둘중하나라도 만족 : OR - 덧셈

- FILTER(K10:M20,K10:K20=O8)

 - FILTER(K10:M20,(K10:K20=O8)*(M10:M20>=Q8)) : 동시조건

 

7. TAKE - 어떤 범위에서 일정 값 몇 개를 추출

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

- TAKE(SORT(B10:D19,3,-1),-3) : 양수는 위부터 음수는 아래부터

- UNIQUE(L10:L39)

- FILTER(J10:K39,(J10:J39>=N8)*(L10:L39=O8))

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

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

 

8. DROP

- 전체 범위에서 불필요한 항목 제거

- DROP(B10:C20,-1)

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

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

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

- DROP은 하나하나 해야함

 

1) ONE MORE. 범위에서 점수의 평균을 구하고 싶다면?

- 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(DROP(DROP(DROP(SORT(DROP(B10:C20,-1),2,-1),2),-2),,1))

- 엑셀은 정답이 없다

 

9. HSTACK

- HSTACK(C10:C14,F10:F14,I10:I14)

- VSTACK처럼 시트도 가능 : HSTACK('1월:4월'!C1:C10)

- UNIQUE(K10:K24)

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

- HSTACK(O10#,P10#)

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

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

 

 - 오피스365 : GROUPBY(K10:K24,M10:M24,SUM) 가 있긴 함. - 내가 수식을 만든다는 의미에서 LAMBDA는 한계가 없음

 

10. 실시간 보고서

- UNIQUE(B2:B1187)

- FILTER(A2:G1187,B2:B1187=L1)

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

 

  1) TIP : 넓은 범위 선택 단축키

- 인접범위 선택 : Ctrl + Shift + 방향키

- 수식 편집셀로 이동 : Ctrl + Backspace

 

 2) 오류가 발생한 이유

- MYREPORT(CHOOSECOLS(N3#,3),CHOOSECOLS(N3#,7))

 - SUMIF는 함수의 인수로 범위가 들어간다. 배열이 아니다. CHOOSECOLS함수는 배열로 나타난다

- INDEX함수는 범위를 반환