[엑셀 2024년 함수] 2일차 마무리

수식에 이름 할당 함수

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

---

 

a+b? : =LET(a,1,b,2,a+b)
view30? : =LET(View1, 10000, View3, 25000, View3*(2+0.2*(View3/View1)+1.8*(View1/View3)+3*(View3/View1)))
국가번호: =LET(국가번호,TEXTBEFORE(110:117," "),
            국가,XLOOKUP(국가번호,M10:M15,N10:N15),
            국가번호 & 국가)
경헙치: =LET(합계,SUM(Q10:S10),IF(합계>=100,합계*1.1,합계))

 

스프레드 시트:

국가번호: =REGEXEXTRACT(I10, "\(\+?\d+\)")

=LET(국가번호, REGEXEXTRACT(I10, "\(\+?\d+\)"),국가, XLOOKUP(국가번호,$M$10:$M$15,$N$10:$N$15),국가번호)

=LET(국가번호, REGEXEXTRACT(I10, "\(\+?\d+\)"),국가, XLOOKUP(국가번호,$M$10:$M$15,$N$10:$N$15),국가)

 

---

재사용 가능 사용자 함수

= LAMBDA ( 인수1, [인수2], … , 수식 )

---

x+y?: =LAMBDA(값1,값2,값1+값2)
수식 > 이름관리자(ctrl + f3) > 수식등록 (ex. mysum)
제곱: =LAMBDA(값,값^2)
성별: =LAMBDA(주민번호,IF(ISODD(MID(주민번호,8,1)),"남성","여성"))

 

함수 그 자체로 오류 calc! 따라서, 인수가 필요함

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

 

데이터 > 이름이 지정된 함수 > 새로운 함수 만들기 > 아래처럼 등록

=FINDGENDER(G10)

---

셀서식 적용 함수

= TEXT ( 값, 표시형식 )

 

행사일자: =B10&"("&TEXT(C10,"yyyy-mm-dd(aaa)")&")"
제품명: =B14&"("&TEXT(C14,"#,##0원")&")"
연락처: =LAMBDA(연락처, TEXT(연락처, "000-0000-0000"))

cf) 셀서식 > [DBNum4]"원" (금액 문자 표시)

 

스프레드시트:

- 연락처: =ARRAYFORMULA(TEXT(F10:F15, "000-0000-0000"))

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

---

문장 분할 함수

= TEXTSPLIT ( 텍스트, 열구분자, [행구분자], [빈칸무시], [일치옵션], [기본값] )

카테고리분류: =IFERROR(TEXTSPLIT(TEXTJOIN("/",,B10:B16),">","/"),"")
판매데이터분류: =TEXTSPLIT(TEXTJOIN("/",,J10:J15),":","/")
오름차순 적용(문자.숫자 구분): =SORT(IFERROR(TEXTSPLIT(TEXTJOIN("/",,J10:J15),":","/")*1,
						TEXTSPLIT(TEXTJOIN("/",,]10:J15),":","/")),2,-1)

 

스프레드 시트: =SPLIT(B10, ">")

 

오름차순: =SORT(IFERROR(SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("/",TRUE,G10:G15),"/")),":")*1,SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("/",TRUE,G10:G15),"/")),":")),2,-1)

내림차순:

=SORT(IFERROR(SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("/",TRUE,G10:G15),"/")),":")*1,SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("/",TRUE,G10:G15),"/")),":")),2,0)

 

함수 단계별 분석표

 

=SORT(IFERROR(SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("/",TRUE,G10:G15),"/")),":")*1,SPLIT(TRANSPOSE(SPLIT(TEXTJOIN("/",TRUE,G10:G15),"/")),":")),2,0)

1. 1차 분할: "/"로 각 메뉴 항목 분리
2. 2차 분할: ":"로 메뉴명과 가격 분리

그리고 *1 트릭으로 가격의 쉼표를 자동으로 제거하면서 숫자로 변환하는 것이 핵심 포인트입니다.

단계함수설명예시 결과

1	G10:G15	원본 데이터 범위	된장찌개:165,700/파전:74,000/순두부찌개:39,000
2	TEXTJOIN("/",TRUE,G10:G15)	모든 셀을 "/"로 연결하여 하나의 문자열 생성	된장찌개:165,700/파전:74,000/순두부찌개:39,000/불고기:14,200/...
3	SPLIT(...,"/")	"/"를 기준으로 문자열을 분할하여 1차원 배열 생성	["된장찌개:165,700", "파전:74,000", "순두부찌개:39,000", ...]
4	TRANSPOSE(...)	1차원 배열을 세로 방향으로 변환 (행 단위로 처리하기 위함)	각 항목이 개별 행으로 배치
5	SPLIT(...,":")	각 항목을 ":"기준으로 분할하여 2차원 배열 생성	[["된장찌개", "165,700"], ["파전", "74,000"], ...]
6-A	... * 1	가격 부분을 숫자로 변환 시도 (IFERROR의 첫 번째 시도)	[["된장찌개", 165700], ["파전", 74000], ...]
6-B	IFERROR(..., SPLIT(...,":"))	숫자 변환 실패 시 원본 텍스트 유지	변환 실패한 항목은 문자열 그대로 유지
7	SORT(..., 2, 0)	2번째 열(가격) 기준으로 내림차순 정렬	가격이 높은 순서대로 정렬된 최종 결과

함수 구조 분석

구성 요소역할상세 설명

TEXTJOIN("/",TRUE,G10:G15)	데이터 통합	여러 셀의 내용을 하나의 긴 문자열로 합침
SPLIT(...,"/")	1차 분할	통합된 문자열을 개별 메뉴:가격 항목으로 분리
TRANSPOSE(...)	배열 변환	가로 배열을 세로 배열로 변환
SPLIT(...,":")	2차 분할	각 항목을 메뉴명과 가격으로 분리
... * 1	숫자 변환	가격 문자열을 숫자로 변환 (쉼표 자동 처리)
IFERROR(...)	오류 처리	변환 실패 시 원본 텍스트 유지
SORT(..., 2, 0)	정렬	2번째 열 기준 내림차순 정렬

매개변수 설명

매개변수값의미

TEXTJOIN 첫 번째	"/"	구분자로 사용할 문자
TEXTJOIN 두 번째	TRUE	빈 셀 무시 여부
SORT 두 번째	2	정렬 기준 열 (2번째 열 = 가격)
SORT 세 번째	0	정렬 순서 (0 = 내림차순, -1 = 오름차순)

---

 

람다 미션

---

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

 

최종함수: =CLEANDATA(A1:A)

=LAMBDA(data,
  LET(
    joined, TEXTJOIN(",", TRUE, data),
    parts, FLATTEN(SPLIT(joined, ",")),
    splitParts, BYROW(parts, LAMBDA(row, SPLIT(row, "/"))),
    splitParts
  )
)

 

1️⃣	TEXTJOIN(",", TRUE, data)	여러 셀(A1:A3)을 하나의 문자열로 결합 → 쉼표로 연결된 큰 문자열 생성
2️⃣	SPLIT(..., ",")	이 문자열을 , 기준으로 나눔 → 각각 "이메일/전화/부서" 단위로 분리
3️⃣	FLATTEN(...)	혹시 data가 2D라면 1D로 평탄화 (대개 생략 가능하지만 안전용)
4️⃣	BYROW(..., LAMBDA(row, SPLIT(row, "/")))	각 "이메일/전화/부서" 항목을 / 기준으로 다시 세 분류로 분리
✅	splitParts 반환	결과는 3열짜리 테이블 형태 (이메일, 전화, 부서)

---

배열이해

① 모든 조건을 만족(AND) → 곱셈 

② 둘 중 하나라도 만족(OR) → 덧셈

조건1: =B10:B17=E8
조건2: =C10:C17>=F8
결과: =E10#*F10#
실저: =FILTER(K10:M20,(K10:K20=O8)*(M10:M20>=Q8))

 

---

추출/ 제거 : TAKE/ DROP

= TAKE ( 범위, 추출할행수, [추출할열수] )

위3개: =TAKE(SORT(B10:D19,3,-1),3)
밑3개: =TAKE(SORT(B10:D19,3,-1),-3)
첫 열:  =TAKE(SORT(B10:D19,3,-1),,1)
끝 열: =TAKE(SORT(B10:D19,3,-1),,-1)

스프레드 시트:

1) ARRAY_CONSTRAIN 등을 조합

=ARRAY_CONSTRAIN(SORT(B10:D19, 3, FALSE), 3, COLUMNS(B10:D19))

 

• SORT(B10:D19, 3, FALSE)는 3번째 열 기준 내림차순 정렬.
• ARRAY_CONSTRAIN(..., 3, COLUMNS(...))는 그 결과의 3행, 전체 열만 추출.

 

 

회의실 목록: =UNIQUE(L10:L39)
데이터 유효성: =$Q$10#
필터링: =FILTER(J10:K39,(L10:L39=O8)*(J10:J39>=N8))
필터 정렬: =SORT(FILTER(J10:K39,(L10:L39=O8)*(J10:J39>=N8)),1,1j
상위 5개: =TAKE(SORT(FILTER(J10:K39,(L10:L39=O8)*(J10:J39>=N8)),1,1),5)

= DROP ( 범위, 제거할행수, [제거할열수] )

 

평균 제거 값만 : =DROP(B10:C20,-1)
상위 2개 제거: =DROP(SORT(DROP(B10:C20,-1),2,-1),2)
상위 2, 하위2 제거: =DROP(DROP(SORT(DROP(B10:C20,-1),2,-1),2),-2)

평균구하기

=AVERAGE(CHOOSECOLS(DROP(DROP(SORT(DROPfB10:C20,-1),2,-1),2),-2),2))
=AVERAGE(TAKE(DROP(DROP(SQRT(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))

---

HSTACK

= HSTACK ( 범위1, [범위2], … )

값 가로 결합: =HSTACK(C10:C14,F10:F14,I10:I14)
구분: =UNIQUE(K10:K24)
금액: =SUMIF(K10:K24,O10#,M10:M24)
결합: HSTACK(O10#,P10#)

 

level up: MYREPORT

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

를 사용자 함수화~

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

 

위 식이 스프레드 시트에서는 작동되지 않았음

 

• SUMIF(구분범위, 구분목록, 금액범위)는 배열(구분목록)을 criteria로 받으면 오류가 발생합니다.
• 해결하려면 배열 함수(MAP 또는 BYROW)를 사용해 각 조건별로 합계를 계산해야 합니다.

 

해결방안

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

 

UNIQUE(구분범위)	중복 제거한 카테고리 목록 생성
BYROW(...LAMBDA...)	각 구분 항목별로 SUMIF 실행
HSTACK(...)	구분 + 합계 나란히 출력

 

TIP

인접 범위 선택 : Ctrl + Shift + 방향키
수식 편집셀로 이동 : Ctrl + 백스페이스

 

SUMIF는 범위 , 배열 X / CHOOSECOLS는 배열 O

---

1) I2 cell : =UNIQUE(B2:B1187)
2) N3 cell : =FILTER(A2:G1187,B2:B1187=L1)
3) K3 cell : =LAMBDA(구분, 금액,
        LET(
            전체구분, 구분,
            전체금액, 금액,
            구분목록, UNIQUE(전체구분),
            집계범위, MAP(
                구분목록,
                LAMBDA(x, SUM(FILTER(전체금액, 전체구분=x)))
            ),
            HSTACK(구분목록, 집계범위)
        )
    )(FILTER(C2:C1187,B2:B1187=L1),FILTER(G2:G1187,B2:B1187=L1))

 

스프레드 시트가 동적 배열을 잘 지원하지 않는가 싶다...

SUMIF는 criteria_range와 sum_range의 크기가 정확히 같아야 합니다.

현재 구분 범위와 금액 범위는 FILTER 함수로 필터링한 후 세로 배열로 나오지만, SUMIF는 전체 범위(구분범위, 금액범위)와 동일한 크기를 기대하고 있어서 오류가 발생한다. 데이터를 출력하고 집계하는 과정에서 이해가 ... 정진할 부분이다.

 

단계설명함수 

1️⃣ LAMBDA 호출	FILTER로 조건에 맞는 데이터만 추출해서 LET으로 전달	FILTER(C2:C1187, B2:B1187=L1) (구분), FILTER(G2:G1187, B2:B1187=L1) (금액)
2️⃣ 구분목록 생성	중복 제거된 구분 항목만 추출	UNIQUE(전체구분)
3️⃣ MAP 함수로 조건별 합계 계산	각 구분 항목(x)별로 FILTER 후 SUM을 계산하여 배열로 반환	MAP(구분목록, LAMBDA(x, SUM(FILTER(전체금액, 전체구분=x))))
4️⃣ HSTACK으로 표 형태 결합	구분목록과 집계범위를 열 방향으로 결합	HSTACK(구분목록, 집계범위)

• MAP(구분목록, LAMBDA(x, ...))
  • MAP은 배열(구분목록)의 각 항목(x)을 순회하면서 LAMBDA 함수 내부를 실행합니다.
  • 구분목록의 항목이 잡화, 음료, 생활용품이라면:
    • 1회전: x = 잡화
    • 2회전: x = 음료
    • 3회전: x = 생활용품
  • 각 x마다 FILTER(전체금액, 전체구분=x)를 실행해 전체구분에서 x와 같은 행만 골라낸 금액 배열을 만든 뒤, SUM(...)으로 합계를 계산합니다.

 즉, 잡화에 대한 매출 합계, 음료에 대한 매출 합계 등을 각각 구해 배열로 반환합니다.

 

MAP 함수 추가 설명

✅ MAP(array, LAMBDA(parameter, calculation))

• array의 각 항목을 순회하며 LAMBDA(parameter, calculation)을 실행해 배열을 반환.
• 예를 들어:
•  
• MAP({1,2,3}, LAMBDA(x, x*2)) → {2,4,6}
• 엑셀의 동적 배열 함수를 이용한 반복 처리라고 생각하시면 됩니다.