엑셀에서 많이 쓰는 10가지 주요기능

 

1. IF 기능

IF 함수는 지정한 조건이 TRUE이면 값을 반환하고, 그렇지 않으면 다른 값을 반환합니다. 

간단히 말해서 IF 함수는 조건을 먼저 테스트하고 해당 조건의 결과에 따라 값을 반환할 수 있습니다.

Syntax

IF(logical_test,value_if_true,value_if_false)

인수

• logical_test: 평가할 조건
• value_if_true: 해당 조건이 TRUE인 경우 가져오려는 값
• value_if_false: 해당 조건이 FALSE인 경우 가져오려는 값

노트

• 수행할 수 있는 중첩 조건의 최대 수는 64개입니다.
• 비교 연산자를 사용하여 조건을 평가할 수 있습니다.

예시

아래 예에서는 비교 연산자를 사용하여 다양한 조건을 평가했습니다.

1. 조건이 충족되는지 여부를 확인하기 위해 특정 텍스트를 사용했습니다.
2. TRUE 및 FALSE를 사용하여 결과를 얻을 수도 있습니다.
3. 조건이 TRUE인 경우 결과를 얻기 위해 값 지정을 건너뛰면 0을 반환합니다.
4. 그리고 조건이 FALSE인 경우 결과를 얻기 위해 값 지정을 건너뛰면 0을 반환합니다.

아래 예에서는 IF 함수를 사용하여 중첩 수식을 만들었습니다.

조건을 지정했으며 해당 조건이 거짓이면 다른 IF를 사용하여 다른 조건을 평가하고 작업을 수행했으며 해당 조건이 FALSE이면 다른 IF를 사용했습니다.

이런 식으로 IF를 다섯 번 사용하여 중첩 수식을 만들었습니다. 중첩 수식에 대해 동일한 값을 64번 사용할 수 있습니다.

2. IFERROR 기능

IFERROR 함수는 오류가 발생하면 특정 값을 반환합니다. 간단히 말해서 값을 테스트할 수 있으며 해당 값이 오류인 경우 지정한 값을 반환합니다.

Syntax

IFERROR(value, value_if_error)

인수

• 값: 오류를 테스트할 값입니다.
• value_if_error: 오류가 발생할 때 반환하려는 값입니다.

노트

• IFERROR 함수는 오류 유형이 아니라 오류 발생과 관련이 있습니다.
• value 또는 value_if_error 지정을 건너뛰면 결과에 0이 반환됩니다.
• #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, #NULL!를 테스트할 수 있습니다.
• 배열을 평가하는 경우 지정된 각 항목에 대한 결과 배열을 반환합니다.

예시

아래 예에서는 IFERROR 함수를 사용하여 #DIV/0을 대체했습니다! 의미있는 텍스트와 함께.

IFERROR는 2007 및 이전 버전과만 호환됩니다. 이 문제를 해결하기 위해 ISERROR를 사용할 수 있습니다.

 

3. TRUNC 기능

TRUNC 함수는 원래 숫자를 자른 후 정수를 반환합니다. 간단히 말해서 숫자에서 소수를 특정 정밀도로 제거한 다음 결과의 정수 부분을 반환합니다.

Syntax

TRUNC(number, [num_digits])

인수

• 수: 자르려는 숫자입니다.
• [num_digits]: 숫자를 자른 정밀도를 지정하는 숫자입니다.

노트

• 여러 개 지정을 건너뛰면 오류와 함께 반환됩니다.
• 0에서 반올림합니다.
• 같은 거리에 두 개의 배수가 있으면 숫자보다 높은 배수를 반환하고 반올림합니다.

예시

아래 예에서는 TRUNC를 사용하여 날짜에서 시간을 제거하기 위해 데이터를 자릅니다.

 

4. SUMIF 함수

SUMIF 함수는 지정한 조건을 충족하는 숫자의 합계를 반환합니다. 간단히 말해서 조건을 충족하는 값의 합만 고려하고 계산합니다.

Syntax

SUMIF(range, criteria, [sum_range])

인수

• 레인지: 조건을 확인할 셀 범위입니다.
• 기준: 숫자, 텍스트, 표현식, 셀 참조 또는 함수가 될 수 있는 기준입니다.
• [sum_range]: 합계를 계산할 값이 있는 셀 범위입니다.

노트

• sum_range 생략하면 범위의 셀이 합산됩니다.
• 큰따옴표를 사용하여 텍스트 조건 또는 수학 기호를 포함하는 기준을 지정해야 하며, 이 기준은 큰따옴표로 묶어야 합니다.
• 기준 범위와 합계 범위의 크기는 같아야 합니다.

예시

아래 예에서 A1 : A9를 기준 범위로, B1 : B9를 합계 범위로 지정한 후 A12에 값이 C 인 기준을 지정했습니다.

함수를 직접 조건에 삽입할 수도 있습니다. 아래 예에서는 별표 와일드카드를 사용하여 알파벳 "S"가 있는 기준을 지정했습니다.

그리고 합계 범위 지정을 건너 뛰면 기준 범위의 합계가 제공됩니다. 그러나 이는 기준 범위에 숫자 값이 있는 경우에만 가능합니다.

5. INDEX 함수

INDEX 함수는 인덱스 번호를 기반으로 값 목록에서 값을 반환합니다. 간단히 말해서 INDEX는 값 목록에서 값을 반환하며 해당 값의 위치를 지정해야 합니다.

Syntax

INDEX에는 두 가지 구문이 있습니다. 

첫 번째로, 인덱스의 배열 형식을 사용하여 해당 위치를 사용하여 목록에서 값을 가져올 수 있습니다.

INDEX(array, row_num, [column_num]) 

 

두 번째로, 실생활에서 덜 사용되는 추천 양식을 사용할 수 있지만 가치를 얻을 수 있는 범위가 두 개 이상인 경우 사용할 수 있습니다.

INDEX(reference, 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를 지정했습니다.

이제 두 번째 범위에서 5 번째 행과 1 번째 열을 참조합니다. INDEX는 172번째 범위의 5번째 행에 있는 값 2를 반환했습니다.

 

6. VLOOKUP 기능

VLOOKUP 함수는 테이블의 첫 번째 열에 있는 값을 조회하고 인덱스 번호를 사용하여 일치하는 값의 동일한 행에서 값을 반환합니다. 간단히 말해서 수직 조회를 수행합니다.

Syntax

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,참)

동작 원리

조회 값과 일치시키기 위해 "MIN MARKS" 열을 사용하고 있으며 "비고" 열에서 대가로 값을 얻고 있습니다.

TRUE를 사용하고 정확히 일치하는 조회 값이 없으면 조회 값에서 다음으로 작은 값을 반환한다고 이미 언급했습니다. 예를 들어 범주 테이블에서 값 77을 찾을 때 65는 77 다음으로 작은 값입니다.

 

2. VLOOKUP 함수의 오류 처리

VLOOKUP을 사용할 때 발생하는 가장 일반적인 문제 중 하나는 일치하는 항목이 없을 때마다 #N/A를 받게 된다는 것입니다. 그러나이 문제에 대한 해결책은 간단하고 쉽습니다. 쉬운 예를 들어 보여 드리겠습니다.

아래 예에는 이름과 나이 목록이 있으며 E6 셀에는 VLOOKUP 함수를 사용하여 목록에서 이름을 조회합니다. 목록에없는 이름을 입력 할 때마다 #N / A가 표시됩니다.

그러나 여기서 원하는 것은 오류 대신 의미있는 메시지를 보여주는 것입니다.

공식은 =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)입니다.

IFNA는 #N/A에 대한 값을 테스트할 수 있으며 오류가 있는 경우 오류 대신 값을 지정할 수 있습니다.

 

7. IFNA 기능

IFNA 함수는 #N/A 오류가 발생하면 특정 값을 반환합니다. IFERROR와 달리 #N/A 오류만 평가하고 지정한 값을 반환합니다.

Syntax

IFNA(value, value_if_na)

인수

• 값: #N/A 오류를 테스트할 값입니다.
• value_if_na: 오류가 발생한 경우 반환할 값입니다.

노트

• 인수 지정을 건너뛰면 IFNA는 이를 빈 문자열("")로 처리합니다.
• 값이 배열이면 결과를 배열로 반환합니다.
• 다른 모든 오류#REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? 및 #NULL!는 무시합니다.

예시

VLOOKUP 함수에서 #N/A는 조회 값이 조회 범위에 없고 IFNA를 사용하여 의미 있는 메시지를 지정했을 때 발생합니다.

IFNA는 Excel 2013에서 도입되었으므로 이전 버전에서는 사용할 수 없습니다.

 

8. RAND 기능

RAND 함수는 0에서 1 사이의 난수를 반환합니다. 간단히 말해서 0에서 1 사이의 난수를 생성할 수 있습니다(워크시트를 변경할 때마다 값이 업데이트됨).

Syntax

RAND()

인수

• RAND 함수에 지정할 인수가 없습니다

노트

• 배수에 0을 넣으면 결과에 0이 반환됩니다.
• 여러 개 지정을 건너뛰면 오류와 함께 반환됩니다.
• 0에서 반올림합니다.
• 같은 거리에 두 개의 배수가 있으면 반올림하는 숫자보다 높은 배수를 반환합니다.

예시

0과 1 사이의 숫자를 갖는 것 외에도 두 개의 특정 숫자 사이의 난수에 RAND를 사용할 수도 있습니다. 아래 예에서는 50에서 100 사이의 난수를 생성하는 수식을 만드는 데 사용했습니다.

셀에 이 수식을 입력하면 RAND에서 반환한 값에 우리가 사용한 방정식을 곱하여 100에서 50 사이의 숫자를 반환합니다. 이 공식을 이해하려면 세 부분으로 나눠야 합니다.

1. 우선, 가장 높은 숫자에서 가장 낮은 숫자를 감지하면 둘 사이의 차이를 얻습니다.
2. 그런 다음 두 번째로 그 차이를 뺄셈 후에 반환된 난수와 곱합니다.
3. 셋째, 방정식의 세 번째 부분에 가장 낮은 숫자가 남아 있는 숫자를 더합니다.

 

9. SUM 기능

SUM 함수는 제공된 값의 합계를 반환합니다. 간단히 말해서 SUM 함수를 사용하면 값 목록의 합계를 계산할 수 있습니다 (함수에 값을 직접 입력하거나 셀 범위를 참조 할 수 있습니다.

Syntax

SUM(number1,[number2],…)

인수

• number1: 숫자, 숫자가 포함된 셀 범위 또는 숫자가 포함된 단일 셀입니다.
• [number2]: 숫자, 숫자가 포함된 셀 범위 또는 숫자가 포함된 단일 셀입니다.

노트

• 텍스트 값을 무시합니다.

예시

아래 예제에서는 숫자 사이에 쉼표를 사용하여 함수에 직접 숫자를 삽입할 수 있습니다.

또한 숫자의 합을 계산하기 위한 범위를 간단히 참조할 수 있으며 텍스트, 논리 값 또는 빈 셀이 있는 경우 이를 무시합니다.

참조하는 셀에 오류 값이 있으면 결과에 #N/A가 반환됩니다.

텍스트 형식의 숫자 값이 있으면 무시됩니다. SUM을 사용하기 전에 숫자로 변환하는 것이 좋습니다.

 

10. OR 기능

OR 함수는 지정한 조건을 테스트한 후 부울 값(TRUE 또는 FALSE)을 반환합니다. 간단히 말해서 AND 함수를 사용하여 여러 조건을 테스트 할 수 있으며 해당 조건 중 하나 (또는 모든) 조건이 TRUE이면 TRUE를 반환하고 모든 조건이 FALSE 인 경우에만 FALSE를 반환합니다.

Syntax

OR(logical1, [logical2], …)

인수

• 논리1: 확인하려는 조건입니다.
• [논리2]: 확인하려는 추가 조건입니다.

노트

• 참조 셀 또는 배열에 빈 셀 또는 텍스트가 포함된 경우 값은 무시됩니다.
• 조건의 결과는 논리 값(TRUE 또는 FALSE)이어야 합니다.
• 논리 값이 반환되지 않으면 오류를 반환합니다.

예시

아래 예에서는 IF 함수를 사용하여 학생이 두 과목 중 하나에서 60점 이상을 받으면 수식이 TRUE를 반환하는 조건을 만들었습니다.

이제 아래 예에서 숫자를 사용하여 수식에서 논리 값을 얻었습니다. 위의 조건을 역순으로 수행할 수도 있습니다.

숫자 대신 TRUE 및 FALSE를 사용할 수 있습니다. OR 함수는 이러한 논리값을 숫자로 처리합니다.