학습노트
엑셀 2024 함수 마스터, 특별 챌린지 - 2일차
잼 엑기스 2-1 강의노트
LET 함수
긴 수식을 간소화하고, 처리속도를 높인다. 방정식이라고 생각하면 편할 듯.
=LET(a,1,b,2,a+b)
a=1, b=2일 때, a+b를 구하는 방정식 !
값에 해당하는 부분을 셀로 연결하면 됨.
=LET(이름1, 값1, 이름2, 값2, 이름3, 값3, .... ,이름을 이용한 수식)
국가번호를 구하는 방법은 여러가지가 있지.
국가 구할 때도, 일반적인 xlookup으로 하나의 값을 찾지 말고, 동적배열 활용해라.
=XLOOKUP(J10:J17,M10:M15,N10:N15)
=LET(국가번호,TEXTBEFORE(I10:I17," "),
국가,XLOOKUP(국가번호,M10:M15,N10:N15),
국가)
국가번호,TEXTBEFORE(I10:I17," ") -->국가번호에 대한 정의
국가,XLOOKUP(국가번호,M10:M15,N10:N15) -->국가에 대한 정의
주의할 점은 정의할 이름 (국가번호 및 국가 등)입력 시, "국가번호"처럼 따옴표 묶으면 안됨.
Total 계산할 때, If로 작성하면, 동일한 함수가 여러번 들어가야 하니, 간단히 하기 위해 LET을 활용하면 좋음.
일반적인 if함수를 활용할 경우, 수식이 중복됨 // =IF(SUM(Q10:S10)>=100,SUM(Q10:S10)*1.1,SUM(Q10:S10))LET을 사용하면, 단순화할 수 있음 // =LET(합계,SUM(Q10:S10),IF(합계>=100,합계*1.1,합계))
LAMBDA 함수
: LET처럼 복잡한 수식을 나만의 간단한 함수를 만들어주는 도우미 함
일반적인 상황 =SUM(B10:C10)
LAMBDA사용 = LAMBDA(x,y,x+y)(B14,C14)
LAMBDA함수만 넣으면, CALC오류 뜸. 하지만, 끝에 괄호 ()로 원하는 값을 넣으면 이상유무 테스트가능
이 LAMBDA함수를 이름관리자를 통해, 정의해 주면, 나만의 수식이 생성됨.이름관리자 단축키 --> Ctrl + F3
=IF(ISODD(MID(G10,8,1)),"남성","여성")
--> MID(G10,8,1) : G10셀의 8번째 글자부터 1개만
--> isodd : 홀수인가? (Is odd ?)
이 수식을 나만의수식 (FindGender)로 만들기 위해 LAMBDA함수를 사용.
=LAMBDA(주민번호,IF(ISODD(MID(주민번호,8,1)),"남성","여성"))
여기서 주민번호는 정의하지 않은 부분임. 값에 해당하는 것이 하나가 될 수도... 여럿이 될 수도 있는데, 지금 상황은 '주민번호' 하나만 사용하는 것임. LET의 함수처럼 '주민번호'라는 것을 IF(ISODD(MID(G10,8,1)),"남성","여성") 로 정의하는 것이 아니니, 헷갈리지 말자.
TEXT 함수
=B10&"("&C10&")"
엑셀은 날짜를 숫자로 관리한다.
엑셀은 1900년에서 1씩 증가하는 것으로 날짜를 관리한다. 그래서, 45752는 1900/1/1로부터 45752번째 날짜라는 뜻
= TEXT ( 값, 표시형식 )여기서 표시형식을 큰따옴표로 묶어줘라. 안그럼, 분산에러 나더라.
=B10&"("&TEXT(C10,"YYYY-MM-DD")&")"
=B10&"("&TEXT(C10,"YYYY-MM-DD (aaa)")&")" ==> 요일표시
=B14&" ("&TEXT(C14,"#,##0 원")&")"
여기서 좀 궁금했던 것이, 제품명만 굵은글씨에 파란색으로 표현할 수 있는가였다. 이 부분은 좀 찾아 공부해봐야 할 듯.
=TEXT(F10:F15,"000-0000-0000")
숫자를 문자로 표현하는 방법
이것도 비슷한 과정의 반복이다.
TEXTSPLIT함수
동적배열로 반환한 함수들은 자동채우기할때, 더블클릭으로 자동채움 안됨.
=TEXTSPLIT(TEXTJOIN("/",,B10:B15),">","/")
=IFERROR(TEXTSPLIT(TEXTJOIN("/",,B10:B19),">","/"),"")
TEXTJOIN("/",,B10:B19) : 정리하고자 하는 모든 셀을 규칙성 있는 기호로 묶는다
TEXTSPLIT(TEXTJOIN("/",,B10:B19),">","/") : Text의 분리순서는 "가로방향분리", "세로방향분리"
=IFERROR(TEXTSPLIT(TEXTJOIN("/",,B10:B19),">","/"),"") : 에러 반환 안되게
=TEXTSPLIT(TEXTJOIN("/",,J10:J15),":","/")
가로방향 구분방법
세로방향 구분방법
이게 끝이 아닙니다엑셀은 기본적으로 문자는 왼쪽정렬, 숫자는 오른쪽정렬 함
TEXT함수를 사용할 경우, 문자는 문자로, 숫자는 숫자로 하는 트릭 필요
*1하면 됨
=IFERROR(TEXTSPLIT(TEXTJOIN("/",,J10:J15),":","/")*1,TEXTSPLIT(TEXTJOIN("/",,J10:J15),":","/"))
Ctrl + Shift + 1 : 숫자로 변환
=LAMBDA(종목코드,IMAGE("https://ssl.pstatic.net/imgfinance/chart/mobile/mini/"&종목코드&"_end_up_tablet.png"))
=LAMBDA(종목코드,IMAGE("https://ssl.pstatic.net/imgfinance/chart/mobile/mini/"&text(종목코드,"000000")&"_end_up_tablet.png"))("000660")
2-2교시
True = 1
False = 0
모든 조건 만족 : AND (곱셈)
둘 중 하나라도 만족 : OR (덧셈)
해시기호#을 이용해서 한번에 배열계산 가능 !!
다중조건 필터 (다중조건이라서, *기호로 묶은것임)
=FILTER(K10:M20,(K10:K20=O8)*(M10:M20>=Q8))
=FILTER(테이블,조건))
TAKE함수: 필요한 것만 추출= 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)
조건에 맞게 찾기=FILTER(J10:K39,(L10:L39=O8)*(J10:J39>=N8))
첫째랑 둘째열의 오름차순 =SORT(FILTER(J10:K39,(L10:L39=O8)*(J10:J39>=N8)),1,1)
상위 5개만 =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))
=SUMIF(K10:K24,O10,M10:M24)
=SUMIF(K10:K24,O10#,M10:M24)
=LAMBDA(구분,금액,LET(구분범위,구분,
금액범위,금액,
구분목록,UNIQUE(구분범위),
집계범위,SUMIF(구분범위,구분목록,금액범위),
HSTACK(구분목록,집계범위)))(K10:K24,M10:M24)
=GROUPBY(K10:K24,M10:M24,SUM)
M365의 Groupby함수로 간단히 쓸수 있지만, 나만의 함수를 만들수 있다는 중요한 점!!
넓은범위 쉽게 선택하는 단축키
인접범위 Ctrl Shift 방향키
이후, Ctrl 백스페이스
sumif함수는 배열이 아니라, 범위를 불러옮
choosecols는 배열임
그래서, sumif를 사용한 자동화공식이 있고, 범위를 불러와야 하는 경우, 오류남.
그래서 index를 사용해야 함.
=Myreport(CHOOSECOLS(N3#,3),CHOOSECOLS(N3#,7)) 는 오류 발생.
=Myreport(INDEX(N3#,,3),INDEX(N3#,,7))
LET 함수
긴 수식을 간소화하고, 처리속도를 높인다. 방정식이라고 생각하면 편할 듯.
=LET(a,1,b,2,a+b)
a=1, b=2일 때, a+b를 구하는 방정식 !
값에 해당하는 부분을 셀로 연결하면 됨.
=LET(이름1, 값1, 이름2, 값2, 이름3, 값3, .... ,이름을 이용한 수식)
국가번호를 구하는 방법은 여러가지가 있지.
- (+82)를 입력하고, 아래 셀에 Ctrl+E 누르면 자동채움 활용
- 새로운 기능인 textbefore활용해도 됨.
=TEXTBEFORE(I10," ") - 단 ! 어제 배운 동적배열 기능을 활용해라.
=TEXTBEFORE(I10:I17," ")
국가 구할 때도, 일반적인 xlookup으로 하나의 값을 찾지 말고, 동적배열 활용해라.
=XLOOKUP(J10:J17,M10:M15,N10:N15)
=LET(국가번호,TEXTBEFORE(I10:I17," "),
국가,XLOOKUP(국가번호,M10:M15,N10:N15),
국가)
국가번호,TEXTBEFORE(I10:I17," ") -->국가번호에 대한 정의
국가,XLOOKUP(국가번호,M10:M15,N10:N15) -->국가에 대한 정의
주의할 점은 정의할 이름 (국가번호 및 국가 등)입력 시, "국가번호"처럼 따옴표 묶으면 안됨.
Total 계산할 때, If로 작성하면, 동일한 함수가 여러번 들어가야 하니, 간단히 하기 위해 LET을 활용하면 좋음.
일반적인 if함수를 활용할 경우, 수식이 중복됨 // =IF(SUM(Q10:S10)>=100,SUM(Q10:S10)*1.1,SUM(Q10:S10))LET을 사용하면, 단순화할 수 있음 // =LET(합계,SUM(Q10:S10),IF(합계>=100,합계*1.1,합계))
LAMBDA 함수
: LET처럼 복잡한 수식을 나만의 간단한 함수를 만들어주는 도우미 함
일반적인 상황 =SUM(B10:C10)
LAMBDA사용 = LAMBDA(x,y,x+y)(B14,C14)
LAMBDA함수만 넣으면, CALC오류 뜸. 하지만, 끝에 괄호 ()로 원하는 값을 넣으면 이상유무 테스트가능
이 LAMBDA함수를 이름관리자를 통해, 정의해 주면, 나만의 수식이 생성됨.이름관리자 단축키 --> Ctrl + F3
=IF(ISODD(MID(G10,8,1)),"남성","여성")
--> MID(G10,8,1) : G10셀의 8번째 글자부터 1개만
--> isodd : 홀수인가? (Is odd ?)
이 수식을 나만의수식 (FindGender)로 만들기 위해 LAMBDA함수를 사용.
=LAMBDA(주민번호,IF(ISODD(MID(주민번호,8,1)),"남성","여성"))
여기서 주민번호는 정의하지 않은 부분임. 값에 해당하는 것이 하나가 될 수도... 여럿이 될 수도 있는데, 지금 상황은 '주민번호' 하나만 사용하는 것임. LET의 함수처럼 '주민번호'라는 것을 IF(ISODD(MID(G10,8,1)),"남성","여성") 로 정의하는 것이 아니니, 헷갈리지 말자.
TEXT 함수
=B10&"("&C10&")"
엑셀은 날짜를 숫자로 관리한다.
엑셀은 1900년에서 1씩 증가하는 것으로 날짜를 관리한다. 그래서, 45752는 1900/1/1로부터 45752번째 날짜라는 뜻
= TEXT ( 값, 표시형식 )여기서 표시형식을 큰따옴표로 묶어줘라. 안그럼, 분산에러 나더라.
=B10&"("&TEXT(C10,"YYYY-MM-DD")&")"
=B10&"("&TEXT(C10,"YYYY-MM-DD (aaa)")&")" ==> 요일표시
=B14&" ("&TEXT(C14,"#,##0 원")&")"
여기서 좀 궁금했던 것이, 제품명만 굵은글씨에 파란색으로 표현할 수 있는가였다. 이 부분은 좀 찾아 공부해봐야 할 듯.
=TEXT(F10:F15,"000-0000-0000")
숫자를 문자로 표현하는 방법
이것도 비슷한 과정의 반복이다.
- 일반적인 기본 수식을 만들어라.
- 계산할 값을 정의해라.
- LAMBDA함수에서 정의된 이름을 나열하고, 1번의 수식을 넣어라
- LAMBDA함수 끝에 ()괄호로 계산할값을 넣어보고 이상유무 테스트
- 이름관리자에 추가
TEXTSPLIT함수
동적배열로 반환한 함수들은 자동채우기할때, 더블클릭으로 자동채움 안됨.
=TEXTSPLIT(TEXTJOIN("/",,B10:B15),">","/")
=IFERROR(TEXTSPLIT(TEXTJOIN("/",,B10:B19),">","/"),"")
TEXTJOIN("/",,B10:B19) : 정리하고자 하는 모든 셀을 규칙성 있는 기호로 묶는다
TEXTSPLIT(TEXTJOIN("/",,B10:B19),">","/") : Text의 분리순서는 "가로방향분리", "세로방향분리"
=IFERROR(TEXTSPLIT(TEXTJOIN("/",,B10:B19),">","/"),"") : 에러 반환 안되게
=TEXTSPLIT(TEXTJOIN("/",,J10:J15),":","/")
가로방향 구분방법
세로방향 구분방법
이게 끝이 아닙니다엑셀은 기본적으로 문자는 왼쪽정렬, 숫자는 오른쪽정렬 함
TEXT함수를 사용할 경우, 문자는 문자로, 숫자는 숫자로 하는 트릭 필요
*1하면 됨
=IFERROR(TEXTSPLIT(TEXTJOIN("/",,J10:J15),":","/")*1,TEXTSPLIT(TEXTJOIN("/",,J10:J15),":","/"))
Ctrl + Shift + 1 : 숫자로 변환
=LAMBDA(종목코드,IMAGE("https://ssl.pstatic.net/imgfinance/chart/mobile/mini/"&종목코드&"_end_up_tablet.png"))
=LAMBDA(종목코드,IMAGE("https://ssl.pstatic.net/imgfinance/chart/mobile/mini/"&text(종목코드,"000000")&"_end_up_tablet.png"))("000660")
2-2교시
True = 1
False = 0
모든 조건 만족 : AND (곱셈)
둘 중 하나라도 만족 : OR (덧셈)
해시기호#을 이용해서 한번에 배열계산 가능 !!
다중조건 필터 (다중조건이라서, *기호로 묶은것임)
=FILTER(K10:M20,(K10:K20=O8)*(M10:M20>=Q8))
=FILTER(테이블,조건))
TAKE함수: 필요한 것만 추출= 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)
조건에 맞게 찾기=FILTER(J10:K39,(L10:L39=O8)*(J10:J39>=N8))
첫째랑 둘째열의 오름차순 =SORT(FILTER(J10:K39,(L10:L39=O8)*(J10:J39>=N8)),1,1)
상위 5개만 =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))
=SUMIF(K10:K24,O10,M10:M24)
=SUMIF(K10:K24,O10#,M10:M24)
=LAMBDA(구분,금액,LET(구분범위,구분,
금액범위,금액,
구분목록,UNIQUE(구분범위),
집계범위,SUMIF(구분범위,구분목록,금액범위),
HSTACK(구분목록,집계범위)))(K10:K24,M10:M24)
=GROUPBY(K10:K24,M10:M24,SUM)
M365의 Groupby함수로 간단히 쓸수 있지만, 나만의 함수를 만들수 있다는 중요한 점!!
넓은범위 쉽게 선택하는 단축키
인접범위 Ctrl Shift 방향키
이후, Ctrl 백스페이스
sumif함수는 배열이 아니라, 범위를 불러옮
choosecols는 배열임
그래서, sumif를 사용한 자동화공식이 있고, 범위를 불러와야 하는 경우, 오류남.
그래서 index를 사용해야 함.
=Myreport(CHOOSECOLS(N3#,3),CHOOSECOLS(N3#,7)) 는 오류 발생.
=Myreport(INDEX(N3#,,3),INDEX(N3#,,7))
최
Y
김
인
새
댓글 0