엑셀 함수 정리: 기초부터 고급까지 100가지

엑셀에서 효율적으로 작업을 하기 위해서는 다양한 수식과 엑셀 함수의 사용법을 익히는 것이 중요합니다. 이번 포스트에서는 엑셀의 기본 함수부터 조금 더 고급 함수까지 알아보고, 실제 업무에서 어떻게 활용할 수 있는지 살펴보겠습니다.

엑셀 함수 100가지

통계 엑셀 함수

=SUM(A1:A10) – 지정 범위 내의 모든 수치의 합을 계산합니다.
=AVERAGE(B1:B10) – 지정 범위 내의 모든 수치의 평균을 계산합니다.
=MIN(C1:C10) – 지정 범위 내에서 가장 작은 값을 찾습니다.
=MAX(D1:D10) – 지정 범위 내에서 가장 큰 값을 찾습니다.
=COUNT(E1:E10) – 지정 범위 내의 숫자를 세는 작업을 수행합니다.
=COUNTA(F1:F10) – 지정 범위 내의 값이 있는 셀의 수를 세는 작업을 수행합니다.
=PRODUCT(A1:A10) – 지정 범위 내의 모든 수치의 곱을 계산합니다.
=STDEV.S(B1:B10) – 지정 범위 내의 표본 표준편차를 계산합니다.
=VAR.S(C1:C10) – 지정 범위 내의 표본 분산을 계산합니다.
=MEDIAN(D1:D10) – 지정 범위 내의 중간값을 찾습니다.
=MODE.SNGL(E1:E10) – 지정 범위 내에서 가장 자주 나타나는 값을 찾습니다.
=RANK.EQ(F1, F1:F10, 0) – 지정된 값의 순위를 계산합니다.
=PERCENTILE.EXC(G1:G10, 0.9) – 지정 범위의 90번째 백분위수를 계산합니다.
=QUARTILE.INC(H1:H10, 1) – 지정 범위의 첫 번째 사분위수를 계산합니다.
=CORREL(I1:I10, J1:J10) – 두 데이터 집합 사이의 상관 계수를 계산합니다.
=SLOPE(K1:K10, L1:L10) – 데이터 집합의 기울기를 계산합니다.

논리 엑셀 함수

=IF(G1>5, “High”, “Low”) – 조건에 따라 다른 값을 반환합니다.
=AND(H1>10, H2<20) – 모든 조건이 참인지 확인합니다.
=OR(I1=5, I2=10) – 주어진 조건 중 하나라도 참이면 참을 반환합니다.
=NOT(J1=1) – 조건이 거짓이면 참을 반환합니다.
=XOR(M1, N1) – 두 논리식 중 하나만 참일 때 TRUE를 반환합니다.
=SWITCH(O1, “case1”, “Result1”, “case2”, “Result2”) – 다중 조건을 기반으로 값을 선택합니다.

조회 및 참조 함수

=VLOOKUP(K1, K2:K100, 2, FALSE) – 특정 키 값에 대한 데이터를 찾아 반환합니다.
=HLOOKUP(L1, L2:L100, 2, FALSE) – 행에서 특정 키 값을 찾아 반환합니다.
=INDEX(M1:M100, 5) – 지정된 배열에서 특정 위치의 값을 반환합니다.
=MATCH(N1, N2:N100, 0) – 지정된 값의 위치를 찾습니다.
=LOOKUP(P1, P2:P100) – 배열에서 특정 값의 위치를 반환합니다.
=OFFSET(Q1, 5, 5) – 참조 셀에서 특정 위치에 있는 셀 참조를 반환합니다.
=ADDRESS(R1, R2) – 지정된 행과 열 번호에 해당하는 셀 주소를 반환합니다.
=INDIRECT(“S1”) – 텍스트 문자열로 지정된 셀 참조를 반환합니다.
=ROW(T1) – 지정된 셀의 행 번호를 반환합니다.
=COLUMN(U1) – 지정된 셀의 열 번호를 반환합니다.

날짜 및 시간 엑셀 함수

=NOW() – 현재 날짜와 시간을 반환합니다.
=TODAY() – 오늘 날짜를 반환합니다.
=DATE(YEAR(O1), MONTH(O1), DAY(O1)) – 지정된 연, 월, 일로 날짜를 만듭니다.
=DAY(P1) – 지정된 날짜의 일자를 반환합니다.
=EOMONTH(V1, 0) – 지정된 날짜의 월 말일을 반환합니다.
=WEEKDAY(W1) – 지정된 날짜의 요일 번호를 반환합니다.
=YEARFRAC(X1, Y1) – 두 날짜 사이의 연도 비율을 계산합니다.
=NETWORKDAYS(Z1, AA1) – 두 날짜 사이의 순 작업 일수를 계산합니다.

수학 및 삼각 엑셀 함수

=ROUND(Q1, 2) – 숫자를 지정된 소수점 자리까지 반올림합니다.
=ABS(R1) – 숫자의 절대값을 반환합니다.
=COS(S1) – 주어진 각도의 코사인 값을 계산합니다.
=SIN(T1) – 주어진 각도의 사인 값을 계산합니다.
=INT(AB1) – 숫자의 정수 부분을 반환합니다.
=MOD(AC1, 10) – 숫자를 10으로 나눈 나머지를 반환합니다.
=PI() – 파이(π) 값을 반환합니다.
=RADIANS(AD1) – 도에서 라디안으로 각도를 변환합니다.
=DEGREES(AE1) – 라디안에서 도로 각도를 변환합니다.
=LN(AF1) – 숫자의 자연 로그를 계산합니다.
=EXP(AG1) – e의 지수로 숫자를 계산합니다.
=SQRT(AH1) – 숫자의 제곱근을 계산합니다.
=POWER(AI1, 2) – 숫자를 지정된 거듭제곱으로 계산합니다.
=LOG10(AJ1) – 숫자의 상용 로그를 계산합니다.
=GCD(BQ1, BQ2) – 두 수의 최대공약수를 계산합니다.
=LCM(BR1, BR2) – 두 수의 최소공배수를 계산합니다.
=MROUND(BS1, 5) – 지정된 배수로 숫자를 반올림합니다.
=COMBIN(BT1, 2) – 주어진 수의 조합을 계산합니다.
=PERMUT(BU1, 2) – 주어진 수의 순열을 계산합니다.

금융 엑셀 함수

=PMT(U1/12, 24, V1) – 정기적으로 동일한 금액을 지불할 때의 각 할부금을 계산합니다.
=FV(W1/12, 12, X1) – 정기적인 저축으로 미래에 도달할 금액을 계산합니다.
=NPV(Y1, Z1:AA10) – 일련의 현금 흐름의 순 현재 가치를 계산합니다.
=RATE(AK1*12, AL1, AM1) – 정기적으로 지불하는 투자의 이자율을 계산합니다.
=IRR(AN1:AN10) – 내부수익률을 계산합니다.
=XNPV(AO1, AP1:AP10, AQ1:AQ10) – 서로 다른 지불 시기를 고려한 순 현재 가치를 계산합니다.

정보 함수

=ISNUMBER(AB1) – 셀에 숫자가 포함되어 있는지 여부를 확인합니다.
=ISTEXT(AC1) – 셀에 텍스트가 포함되어 있는지 여부를 확인합니다.
=ISBLANK(AD1) – 셀이 비어 있는지 확인합니다.
=ISNUMBER(AR1) – 셀에 숫자가 포함되어 있는지 확인합니다.
=ISTEXT(AS1) – 셀에 텍스트가 포함되어 있는지 확인합니다.
=ISBLANK(AT1) – 셀이 비어 있는지 확인합니다.
=ISERROR(AU1) – 셀에 오류가 있는지 확인합니다.
=ISNA(AV1) – 셀의 값이 NA인지 확인합니다.

텍스트 엑셀 함수

=LEN(AW1) – 문자열의 길이를 반환합니다.
=TRIM(AX1) – 문자열에서 앞뒤의 공백을 제거합니다.
=UPPER(AY1) – 문자열을 대문자로 변환합니다.
=LOWER(AZ1) – 문자열을 소문자로 변환합니다.
=PROPER(BA1) – 문자열에서 각 단어의 첫 글자를 대문자로 만듭니다.
=REPLACE(BB1, 1, 3, “New”) – 문자열의 일부를 다른 문자열로 대체합니다.
=SUBSTITUTE(BC1, “old”, “new”) – 문자열에서 지정된 텍스트를 다른 텍스트로 교체합니다.
=TEXT(BD1, “yyyy-mm-dd”) – 숫자를 지정된 형식의 텍스트로 변환합니다.
=CONCATENATE(BE1, ” “, BF1) – 여러 텍스트를 하나로 결합합니다.
=TEXTJOIN(“,”, TRUE, BG1:BG10) – 지정된 구분자로 여러 텍스트를 결합하며, 빈 셀은 무시합니다.

배열 엑셀 함수

=ARRAYFORMULA(BH1*BH2:BH10) – 하나의 수식을 여러 셀에 적용합니다.
=TRANSPOSE(BI1:BI10) – 배열의 행과 열을 서로 바꿉니다.
=FREQUENCY(BJ1:BJ10, BK1:BK5) – 데이터 세트의 빈도를 계산합니다.
=SORT(BW1:BW10) – 배열을 정렬합니다.
=FILTER(BX1:BX10, BY1:BY10 > 5) – 조건에 따라 배열에서 데이터를 필터링합니다.
=UNIQUE(BZ1:BZ10) – 배열에서 중복 없는 고유한 값들을 반환합니다.
=SEQUENCE(5) – 지정된 길이의 순차적 숫자 배열을 생성합니다.

데이터베이스 엑셀 함수

=DSUM(BL1:BL10, “Field”, Criteria) – 지정된 조건에 따라 필드의 합을 계산합니다.
=DCOUNT(BM1:BM10, “Field”, Criteria) – 지정된 조건을 충족하는 셀의 수를 세는 작업을 수행합니다.
=DAVERAGE(BN1:BN10, “Field”, Criteria) – 지정된 조건에 따라 필드의 평균을 계산합니다.

사용자 정의 함수

=MYFUNCTION(BO1, BP1) – 사용자가 만든 특정 기능을 실행합니다.

조건부 서식

=CELL(“color”, BV1) – 셀의 서식 정보를 반환합니다.
=INFO(“osversion”) – 운영 체제에 대한 정보를 반환합니다.

기타 유용한 엑셀 함수

=HYPERLINK(CA1, “Click Here”) – 셀에 하이퍼링크를 삽입합니다.
=RAND() – 0과 1 사이의 임의의 숫자를 생성합니다.
=RANDBETWEEN(CB1, 100) – 지정된 범위 내에서 임의의 정수를 생성합니다.
=ROMAN(CC1) – 숫자를 로마 숫자로 변환합니다.

자주 묻는 질문과 답변

1. 엑셀에서 통계 함수를 사용할 때 주의해야 할 점은 무엇인가요?

통계 함수를 사용할 때는 데이터가 정확하고, 누락된 값이 없어야 합니다. 예를 들어, STDEV.S 또는 VAR.S를 사용할 때는 표본 데이터가 전체 모집단을 대표할 수 있어야 합니다. 또한, 함수의 인수 범위가 올바르게 지정되었는지 확인해야 합니다.

2. VLOOKUP 함수와 HLOOKUP 함수의 차이점은 무엇인가요?

VLOOKUP은 수직으로 데이터를 검색하여 값을 반환하는 반면, HLOOKUP은 수평으로 데이터를 검색합니다. VLOOKUP은 열을 기준으로, HLOOKUP은 행을 기준으로 사용하므로, 찾고자 하는 데이터의 배열 구조에 따라 적절한 함수를 선택해야 합니다.

3. IF 함수의 사용 예를 들 수 있나요?

예를 들어, 어떤 숫자가 5보다 크면 “High”, 그렇지 않으면 “Low”라는 값을 반환하고 싶다면, IF 함수를 이렇게 사용할 수 있습니다: =IF(G1>5, “High”, “Low”). 이는 G1 셀의 값에 따라 다른 문자열을 셀에 표시합니다.

4. 엑셀에서 배열 함수의 유용성은 무엇인가요?

배열 함수는 여러 데이터에 동일한 연산을 적용할 때 매우 유용합니다. 예를 들어, ARRAYFORMULA는 하나의 수식으로 여러 셀에 걸쳐 함수를 적용할 수 있게 해줍니다. 이는 데이터 처리를 대량으로 빠르게 처리할 때 시간을 절약해 줍니다.

5. MATCH 함수와 INDEX 함수를 함께 사용하는 이유는 무엇인가요?

MATCH 함수는 특정 값이 위치한 인덱스를 반환하고, INDEX 함수는 이 인덱스를 사용하여 해당 위치의 값을 반환합니다. 두 함수를 조합하면, 대규모 데이터셋에서 원하는 데이터를 정확하고 효율적으로 찾을 수 있습니다. 예를 들어, 특정 이름을 찾고 그 이름에 해당하는 특정 데이터를 추출할 때 유용합니다.