가장 중요한 10가지 excel 기능
1. IF 함수
IF 함수는 지정한 조건이 TRUE이면 값을 반환하고, 그렇지 않으면 다른 값을 반환합니다. 간단히 말해서 IF 함수는 먼저 조건을 테스트하고 해당 조건의 결과에 따라 값을 반환할 수 있습니다.
통사론
IF(논리_테스트, 값_if_true, 값_if_false)
인수
- logic_test: 평가하려는 조건입니다.
- value_if_true: 이 조건이 TRUE인 경우 얻고자 하는 값입니다.
- value_if_false: 이 조건이 FALSE인 경우 얻고자 하는 값입니다.
코멘트
- 수행할 수 있는 최대 중첩 조건 수는 64개입니다.
- 비교 연산자를 사용하여 조건을 평가할 수 있습니다.
예
아래 예에서는 비교 연산자를 사용하여 다양한 조건을 평가했습니다.
- 조건 충족 여부에 따른 결과를 얻기 위해 특정 텍스트를 사용했습니다.
- TRUE 및 FALSE를 사용하여 결과를 얻을 수도 있습니다.
- 조건이 TRUE인 경우 결과를 얻기 위해 값 지정을 건너뛰면 0이 반환됩니다.
- 그리고 조건이 FALSE인 경우 결과를 얻기 위해 값 지정을 건너뛰면 0이 반환됩니다.
아래 예에서는 IF 함수를 사용하여 중첩 수식을 만들었습니다.
조건을 지정했고 해당 조건이 거짓이면 다른 IF를 사용하여 다른 조건을 평가하고 작업을 수행했으며 해당 조건이 FALSE이면 다른 IF를 사용했습니다.
이런 식으로 IF를 5번 사용하여 중첩 수식을 만들었습니다. 중첩 수식에는 동일한 내용을 64번 사용할 수 있습니다.
2.IFERROR 함수
IFERROR 함수는 오류가 발생하면 특정 값을 반환합니다. 간단히 말해서 값을 테스트할 수 있으며 해당 값이 오류인 경우 지정한 값을 반환합니다.
통사론
IFERROR(값, value_if_error)
인수
- 값: 오류를 테스트하려는 값입니다.
- value_if_error: 오류가 발생했을 때 반환받고 싶은 값입니다.
코멘트
- IFERROR 함수는 오류 유형이 아닌 오류 발생과 관련이 있습니다.
- 값이나 value_if_error를 무시하면 결과에 0이 반환됩니다.
- #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?을 테스트할 수 있습니다. 그리고 #NULL!.
- 배열을 평가하면 지정된 각 요소에 대한 결과 배열이 반환됩니다.
예
아래 예에서는 IFERROR 함수를 사용하여 #DIV/0! 의미있는 텍스트로.
IFERROR는 2007 이하 버전과만 호환됩니다. 이 문제를 해결하려면 ISERROR를 사용할 수 있습니다.
3.TRUNC 기능
TRUNC 함수는 원래 숫자를 잘라낸 후 정수를 반환합니다. 간단히 말해서 특정 정밀도를 가진 숫자에서 소수점 이하 자릿수를 제거한 다음 결과의 정수 부분을 반환합니다.
통사론
TRUNC(숫자, [숫자_숫자])
인수
- number: 잘라내려는 숫자입니다.
- [num_digits]: 숫자를 자르는 정밀도를 지정하는 숫자입니다.
코멘트
- 다중 지정을 무시하면 오류가 반환됩니다.
- 0부터 반올림됩니다.
- 같은 거리에 두 개의 배수가 있는 경우 반올림하는 숫자의 더 높은 배수가 반환됩니다.
예
아래 예에서는 TRUNC를 사용하여 데이터를 잘라 날짜에서 시간을 제거했습니다.
4. SUMIF 함수
SUMIF 함수는 지정한 조건을 충족하는 숫자의 합계를 반환합니다. 쉽게 말하면 조건을 만족하는 값들의 합만을 고려하여 계산하는 것입니다.
통사론
SUMIF(범위, 기준, [합계_범위])
인수
- 범위: 기준을 확인하려는 셀 범위입니다.
- 기준: 숫자, 텍스트, 표현식, 셀 참조 또는 함수일 수 있는 기준입니다.
- [sum_range]: 합산하려는 값이 포함된 셀 범위입니다.
코멘트
- sum_range를 생략하면 범위 내 셀의 합이 계산됩니다.
- 텍스트 기준을 지정하거나 큰따옴표로 묶어야 하는 수학 기호를 포함하려면 큰따옴표를 사용해야 합니다.
- 기준 범위와 합계 범위의 크기는 동일해야 합니다.
예
아래 예에서는 A1:A9를 기준 범위로, B1:B9를 합계 범위로 지정한 다음 값이 C인 A12에 기준을 지정했습니다.
함수에 기준을 직접 삽입할 수도 있습니다. 아래 예에서는 별표 와일드카드를 사용하여 알파벳 “S”가 있는 기준을 지정했습니다.
그리고 합계 범위를 무시하면 기준 범위의 합계가 나옵니다. 그러나 이는 기준 범위에 숫자 값이 있는 경우에만 가능합니다.
5. 인덱스 기능
INDEX 함수는 인덱스 번호를 기준으로 값 목록에서 값을 반환합니다. 간단히 말해서 INDEX는 값 목록에서 값을 반환하며 해당 값의 위치를 지정해야 합니다.
통사론
INDEX에는 두 가지 다른 구문이 있습니다. 처음 에는 인덱스의 배열 형식을 사용하여 해당 위치를 사용하여 목록에서 값을 간단히 가져올 수 있습니다.
INDEX(배열, 행_번호, [열_번호])
두 번째 로는 실생활에서 잘 사용되지 않는 후원양식을 활용하실 수 있으나, 홍보할 범위가 2개 이상인 경우에 활용하실 수 있습니다.
INDEX(참조, row_num, [column_num], [area_num])
인수
- 배열: 셀 범위 또는 배열 상수입니다.
- 참조: 셀 범위 또는 여러 범위.
- row_number: 값을 얻으려는 행 번호입니다.
- [col_number]: 값을 얻으려는 열의 번호입니다.
- [area_number]: 여러 셀 범위를 참조하는 경우(참조 구문 사용) 셀 전체 중에서 하나의 범위를 참조하는 숫자를 지정합니다.
코멘트
- row_num 및 column_num 인수가 지정되면 둘의 교차점에 있는 셀의 값을 반환합니다.
- row_num 또는 column_num을 0(영)으로 지정하면 각각 전체 열 또는 행에 대한 값의 배열을 반환합니다.
- row_num 및 column_num이 범위를 벗어나면 #REF를 반환합니다! 오류.
- Area_number가 지정한 숫자 범위보다 크면 #REF!가 반환됩니다.
예 1 – ARRAY를 사용하여 목록에서 값 가져오기
아래 예에서는 INDEX 함수를 사용하여 6월의 수량을 가져왔습니다. 목록에서 Jun은 6번째 위치(6번째 행)에 있으므로 row_number에 6을 지정했습니다. INDEX는 결과에 1904 값을 반환했습니다.
그리고 둘 이상의 열이 있는 범위를 참조하는 경우 열 번호를 지정해야 합니다.
예 2 – REFERENCE를 사용하여 여러 목록의 값 가져오기
아래 예에서는 전체 범위를 한 번에 선택하는 대신 세 가지 범위에서 선택했습니다. 마지막 인수에서 우리는 이러한 세 가지 다른 범위에서 사용할 범위를 정의하는area_number에 2를 지정했습니다.
이제 두 번째 행에서는 다섯 번째 행과 첫 번째 열을 참조합니다. INDEX는 두 번째 행의 다섯 번째 행에 있는 값 172를 반환했습니다.
6.VLOOKUP 기능
VLOOKUP 함수는 테이블의 첫 번째 열에서 값을 찾아 인덱스 번호를 사용하여 해당 값과 동일한 행의 값을 반환합니다. 간단히 말해서 수직 검색을 수행합니다.
통사론
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
인수
- lookup_value: 열에서 찾고자 하는 값입니다. 검색 값이 포함된 셀을 참조하거나 해당 값을 함수에 직접 입력할 수 있습니다.
- table_array: 셀 범위, 값을 찾으려는 명명된 범위입니다.
- col_index_num: 숫자는 값을 검색하려는 열 번호를 나타냅니다.
- range_lookup: 정확한 일치에는 false 또는 0을 사용하고 적절한 일치에는 true 또는 1을 사용합니다. 기본값은 True입니다.
코멘트
- VLOOKUP이 원하는 값을 찾지 못하면 #N/A를 반환합니다.
- VLOOKUP은 조회 값 오른쪽에 있는 값만 제공할 수 있습니다. 오른쪽에서 보고 싶다면 INDEX와 MATCH를 사용하면 됩니다.
- 정확히 일치를 사용하는 경우 열의 첫 번째 값만 일치합니다.
- VLOOKUP에 와일드카드를 사용할 수도 있습니다.
- 적절한 일치를 원하면 TRUE 또는 1을 사용하고 정확한 일치를 원하면 FALSE 또는 0을 사용할 수 있습니다.
- 적절한 일치를 사용하는 경우(True): 정확한 일치가 없으면 목록에서 다음으로 가장 작은 값을 반환합니다.
- 찾고 있는 값이 목록에서 가장 작은 값보다 작은 경우 VLOOKUP은 #N/A를 반환합니다.
- 찾고 있는 정확한 값이 있으면 해당 값이 제공됩니다.
- 목록을 오름차순으로 정렬했는지 확인하세요.
예
1. 카테고리에 VLOOKUP 사용
아래 예에는 받은 성적이 포함된 학생 목록이 있으며 비고 열에는 성적에 따른 성적이 필요합니다.
위의 브랜드 목록에 아래 카테고리 범위에 따른 비고를 추가하고자 합니다.
여기에는 두 가지 옵션을 사용할 수 있습니다.
첫 번째 는 IF를 사용하여 중첩 수식을 만드는 것인데 시간이 조금 걸립니다. 두 번째 옵션은 적절한 일치 항목이 있는 VLOOKUP을 사용하여 수식을 만드는 것입니다. 그리고 공식은 다음과 같습니다:
=VLOOKUP(B2,$E$2:$G$5,3,TRUE)
작동 원리
“MIN MARKS” 열을 사용하여 조회 값을 일치시키고 “Remarks” 열에서 값을 다시 가져옵니다.
TRUE를 사용하고 정확히 일치하는 검색 값이 없으면 검색 값에서 다음으로 가장 작은 값을 반환한다고 이미 언급했습니다. 예를 들어 카테고리 테이블에서 77이라는 값을 검색하면 65가 77 다음으로 가장 작은 값입니다.
이것이 우리가 발언에서 “좋음”을 얻은 이유입니다.
2. VLOOKUP 함수의 오류 처리
VLOOKUP을 사용할 때 발생하는 가장 일반적인 문제 중 하나는 일치하는 항목이 없을 때마다 #N/A가 발생한다는 것입니다. 하지만 이 문제에 대한 해결책은 간단하고 쉽습니다. 간단한 예를 들어 보여드리겠습니다.
아래 예에는 이름과 나이 목록이 있으며 셀 E6에는 VLOOKUP 함수를 사용하여 목록에서 이름을 검색합니다. 목록에 없는 이름을 입력할 때마다 #N/A가 표시됩니다.
하지만 여기서 내가 원하는 것은 오류 대신 의미 있는 메시지를 표시하는 것입니다. 수식은 다음과 같습니다. =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”찾을 수 없음”)
작동 방식 : IFNA는 #N/A 값을 테스트할 수 있으며 오류가 있는 경우 오류 대신 값을 지정할 수 있습니다.
7. IFNA 기능
IFNA 함수는 #N/A 오류가 발생하면 특정 값을 반환합니다. IFERROR와 달리 #N/A 오류만 평가하고 지정한 값을 반환합니다.
통사론
IFNA(값, value_if_na)
인수
- value: #N/A 오류를 테스트하려는 값입니다.
- value_if_na: 오류가 발생한 경우 반환할 값입니다.
코멘트
- 인수를 지정하지 않으면 IFNA는 이를 빈 문자열(“”)로 처리합니다.
- 값이 배열이면 결과가 배열로 반환됩니다.
- 다른 모든 오류 #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?을 무시합니다. 그리고 #NULL!.
예
VLOOKUP 함수에서는 조회 값이 조회 범위에 없을 때 #N/A가 발생하는데, 이에 대해 IFNA를 사용하여 의미 있는 메시지를 지정했습니다.
참고: IFNA는 Excel 2013에 도입되었으므로 이전 버전에서는 사용할 수 없습니다.
8. 랜드 함수
RAND 함수는 0과 1 사이의 난수를 반환합니다 . 간단히 말해서 0과 1 사이의 난수를 생성할 수 있습니다(워크시트를 변경할 때마다 해당 값이 업데이트됩니다).
통사론
랜드()
인수
- RAND 함수에 지정할 인수가 없습니다.
코멘트
- 배수에 0을 넣으면 결과에 0이 반환됩니다.
- 다중 지정을 무시하면 오류가 반환됩니다.
- 0부터 반올림됩니다.
- 같은 거리에 두 개의 배수가 있는 경우 반올림하는 숫자의 더 높은 배수가 반환됩니다.
예
0과 1 사이의 숫자 외에도 두 특정 숫자 사이의 난수에 대해 RAND를 사용할 수도 있습니다. 아래 예에서는 이를 사용하여 50에서 100 사이의 난수를 생성하는 수식을 만들었습니다.
이 수식을 셀에 입력하면 RAND에서 반환된 값에 우리가 사용한 방정식을 곱하여 100에서 50 사이의 숫자가 반환됩니다. 이 공식을 이해하려면 이를 세 부분으로 나누어야 합니다.
- 먼저, 높은 숫자에서 낮은 숫자를 감지하면 둘 사이의 차이를 알 수 있습니다.
- 그런 다음 이 차이에 뺄셈 후에 반환된 난수를 곱합니다.
- 셋째, 방정식의 세 번째 부분에 가장 낮은 남은 숫자와 함께 이 숫자를 추가합니다.
관련: Excel에서 임의의 문자를 빠르게 생성하는 방법
9. SUM 함수
SUM 함수는 제공된 값의 합계를 반환합니다. 간단히 말해서 SUM 함수를 사용하면 값 목록의 합계를 계산할 수 있습니다(함수에 값을 직접 입력하거나 셀 범위를 참조할 수 있습니다).
통사론
SUM(숫자1,[숫자2],…)
인수
- number1 : 숫자, 숫자가 포함된 셀 범위 또는 숫자가 포함된 단일 셀입니다.
- [number2] : 숫자, 숫자가 포함된 셀 범위 또는 숫자가 포함된 단일 셀입니다.
코멘트
- 텍스트 값은 무시됩니다.
예
아래 예에서는 숫자 사이에 쉼표를 사용하여 숫자를 함수에 직접 삽입할 수 있습니다.
범위를 참조하여 숫자의 합을 계산할 수도 있으며 텍스트, 논리값 또는 빈 셀이 있으면 무시됩니다.
참조하는 셀에 오류 값이 있으면 결과에 #N/A가 반환됩니다.
텍스트 형식의 숫자 값이 있으면 무시됩니다. SUM을 사용하기 전에 숫자로 변환하는 것이 좋습니다.
10. OR 기능
OR 함수는 지정한 조건을 테스트한 후 부울 값(TRUE 또는 FALSE)을 반환합니다. 간단히 말해서 AND 함수를 사용하여 여러 조건을 테스트할 수 있으며 이러한 조건 중 하나(또는 모두)가 TRUE이면 TRUE를 반환하고 모든 조건이 FALSE인 경우에만 FALSE를 반환합니다.
통사론
OR(논리1, [논리2], …)
인수
- logic1: 확인하려는 조건입니다.
- [논리2]: 확인하려는 추가 조건입니다.
코멘트
- 참조 셀이나 표에 빈 셀이나 텍스트가 포함되어 있으면 값이 무시됩니다.
- 조건의 결과는 논리값(TRUE 또는 FALSE)이어야 합니다.
- 논리값이 반환되지 않으면 오류가 반환됩니다.
예
아래 예에서는 IF 함수를 사용하여 학생이 두 과목 중 하나에서 60점 이상을 획득하면 수식이 TRUE를 반환하는 조건을 만들었습니다.
이제 아래 예에서는 수식에서 논리값을 얻기 위해 숫자를 사용했습니다. 위의 조건을 역순으로 수행할 수도 있습니다.
숫자 대신 TRUE 및 FALSE를 사용할 수 있습니다. OR 함수는 이러한 논리값을 숫자로 처리합니다.
더 많은 튜토리얼
통계 함수 / 날짜 함수 / 문자열 – 텍스트 함수 / 금융 함수