들어가기 전에
Microsoft Excel은 숫자를 계산하고 통찰력 있는 차트, 데이터 분석 및 데이터 시각화를 만드는 데 꼭 필요한 솔루션입니다. 하지만 Excel을 처음 사용하는 경우, 방대한 기능과 수식이 압도적으로 느껴질 수 있습니다. 일상적인 Excel 작업을 쉽게 처리할 수 있도록 꼭 알아야 할 Excel 함수 중 일부를 소개합니다.
※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 12 Excel functions everyone should know about
- URL: https://www.xda-developers.com/excel-functions-everyone-should-know-how-to-use/
1. SUM, AVERAGE, MIN, MAX
간단한 함수부터 시작해서 점차 더 복잡한 함수로 넘어가겠습니다.
SUM
이름에서 알 수 있듯이 SUM 함수는 선택한 셀을 더하는 함수입니다. 기본적으로 =SUM(B2:B5)와 같은 셀 범위를 입력하면 B2에서 B5까지의 값을 계산하여 그 결과를 B6에 표시합니다.
AVERAGE
이 기능은 교육자에게 꼭 필요한 기능입니다. 학생의 최종 결과(성적)를 계산하든, 월 평균 비용을 계산하든 평균 기능을 사용하세요. 셀 범위를 선택하거나 특정 셀을 정의할 수 있습니다.
=AVERAGE(B2:B5)
=AVERAGE(B2,B5,C7)
앞의 수식은 B2에서 B5의 평균을 계산하고, 뒤의 것은 셀 B2, B5 및 C7의 값의 평균을 계산합니다.
MIN, MAX
MIN 함수는 정의된 셀 범위의 최소값을 표시합니다. MAX는 이와는 반대이며 셀 범위의 최대값을 표시합니다.
=MIN(D2:D6)
=MIN(B2,B5,C7)
=MAX(D2:D6)
2. SUMIF
SUMIF 함수를 사용하면 설정한 특정 기준을 충족하는 셀 범위의 값을 합산할 수 있습니다. 조건부 계산이나 특정 필터를 사용하여 데이터를 요약할 때 유용합니다. 다음은 함수가 어떻게 보이는지 보여주는 예시입니다.
=SUMIF(범위, 기준, [합계 범위])
예제를 통해 이해해 보겠습니다. 항목과 해당 비용을 포함한 비용 목록이 있다고 가정해 보겠습니다. 이제 특정 항목의 총 비용, 즉 총 연료비를 계산하려고 합니다.
=SUMIF(A2:A10,“fuel”,B2:B10)
여기서 A2:A10 범위의 해당 셀이 “fuel”과 같은 경우에만 B2:B10 범위의 값을 합산합니다.
3. SUBTOTAL
여러 가지 면에서 SUBTOTAL 함수는 SUM과 비슷하게 들릴 수 있습니다. 하지만 큰 차이가 있습니다. SUBTOTAL 함수는 데이터에서 필터링된 행을 계산에서 자동으로 제외하므로 데이터에 필터링된 행이 있을 때 특히 유용합니다. 필요에 따라 평균, 카운트, 합계, 최소, 최대 등 함수 내에서 다양한 옵션 중에서 선택할 수 있습니다. Excel에서 숫자 속기를 사용하는 몇 가지 일반적인 값을 살펴 보겠습니다.
- 1: 평균
- 2: COUNT
- 3: COUNTA
- 9: SUM
- 10: PRODUCT
=SUM(9,D2:D80)
9는 SUM을 의미하므로 필터링 후에도 표시되는 행에 대해서만 D2~D80 범위의 총 판매 금액을 계산합니다. 이 함수는 매우 강력한 함수이며, Excel에서 데이터를 조작할 수 있는 다양한 가능성을 열어줍니다.
4. DATE, TIME
날짜와 시간이 포함된 스프레드시트를 자주 작성하시나요? Excel은 연도, 월, 시, 분, 초와 같은 날짜와 시간으로 작업하는 데 도움이 되는 몇 가지 편리한 함수를 제공합니다. 몇 가지 예를 살펴보겠습니다.
- =TODAY()는 시스템 설정에 따라 현재 날짜를 반환합니다.
- =NOW()는 현재 날짜 및 시간을 반환합니다.
- =DATE(2024,8,24)는 2024-08-24를 반환합니다.
- =TIME(12,20,40)은 12:20:40을 반환합니다.
특정 셀에서 연도, 월, 일을 추출할 수도 있습니다. 예를 들어 셀 C3에 2024-08-24가 포함된 경우 아래 함수를 사용합니다.
- =YEAR(C3)는 2024를 표시합니다.
- =MONTH(C3)는 8을 표시합니다.
- =DAY(C3)는 24를 반환합니다.
5. MOD
MOD 함수는 매우 간단하며 연구자나 데이터 과학자에게 유용합니다. MOD 함수는 한 숫자를 다른 숫자로 나눌 때 나머지를 결정합니다.
=MOD(20,3)
위의 예에서 이 함수는 2를 반환합니다(20을 3으로 나누면 6이고 나머지는 2이므로). 직접 값 대신 함수에서 셀을 참조할 수도 있습니다. 예를 들어 =MOD(A2,3)을 사용할 수 있습니다(A2 셀의 값이 20인 경우에도 같은 의미).
6. CONCAT
CONCAT 함수는 여러 텍스트 문자열을 하나의 응집력 있는 텍스트 문자열로 병합하거나 연결하기 위한 것입니다. 두 개의 셀, 여러 개의 셀, 짝수 숫자와 날짜를 연결할 수 있습니다. 위 이미지에 표시된 예제를 살펴보겠습니다. 예를 들어 A5 셀에는 "First Name", B5 셀에는 "Last Name", C5 셀에는 "길동", D5 셀에는 "홍"이라는 값이 각각 들어 있다고 가정합니다.
=CONCAT(A5,": "C5,",",B5,": ",D5)
이 수식의 결과는 "First Name: 길동, Last Name: 홍" 이렇게 표시됩니다.
7. CEILING, FLOOR
Excel의 CEILING 및 FLOOR 함수는 숫자를 반올림하지만 반대 방향으로 반올림합니다. CEILING은 숫자를 반올림하고 FLOOR는 숫자를 내림합니다. 예를 들어 최종 결과가 8.7이고 배수가 2인 경우 10(2의 가장 가까운 배수)으로 반올림됩니다. 마찬가지로 FLOOR 함수는 8.7을 8로 내림하여 반환합니다.
=ceiling(8.7,2)
=floor(8.7,2)
일반 사용자는 이 함수들이 유용하지 않을 수 있습니다. 그러나 CEILING 및 FLOOR 함수는 청구, 재고 관리 또는 여러 차트에서 숫자 표시를 제어하는 데 매우 유용합니다. 특정 수준의 정밀도를 위해 측정값을 반올림하는 데 도움이 됩니다.
8. TRIM
TRIM 함수는 여분의 공백을 제거하여 텍스트 문자열을 단순화합니다. Excel에서 텍스트 데이터를 관리하기 위한 강력한 도구입니다. 예를 들어, A10 셀에 문자 사이에 공백이 있는 '내 이름은 홍길동'이 포함된 경우 =TRIM(A10)을 실행하면 정상 공백이 있는 '내 이름은 홍길동'이 반환됩니다.
Excel의 TRIM 함수는 불필요한 공백을 효과적으로 제거하여 텍스트 데이터 관리가 쉬워지고 스프레드시트의 정확성과 모양이 개선됩니다. Excel에서 데이터를 정리하고 일관된 텍스트 서식을 지정하는 데 꼭 필요한 함수입니다.
9. REPLACE, SUBSTITUTE
REPLACE 함수를 사용하면 텍스트의 특정 부분을 다른 내용으로 바꿀 수 있습니다. SUBSTITUTE 함수도 REPLACE와 매우 유사하지만 완전히 동일하지는 않습니다. SUBSTITUTE를 사용하면 텍스트 블록의 단어나 구를 다른 것으로 바꿀 수 있으며 한 번에 여러 단어를 대체할 수 있습니다.
REPLACE를 사용하여 텍스트 문자열 내의 특정 부분을 새 텍스트 문자열로 바꾸어 보겠습니다. 예제를 통해 간단하게 설명하겠습니다.
=REPLACE(old_text, start_num, num_chars, new_text)
위 이미지에서 A1 셀에는 'Chocolate cake'가 들어 있습니다. 이제 아래 함수를 사용하면 'Chocolate'을 'Vanilla'로 바꿀 수 있습니다.
=REPLACE(A1,1,9, “Vanilla”)
위의 함수는 처음 9개 문자(Chocolate)를 ' Vanilla'라는 단어로 바꿉니다. 문자나 숫자 몇 개만 부분적으로 변경할 수도 있습니다. 셀 내에서 교체가 필요한 문자의 위치와 개수를 지정하기만 하면 됩니다.
다음으로, 아래 예시 이미지에서 A4에는 문장이 있습니다. SUBSTITUTE 함수를 사용하여 Dell이라는 단어를 HP로 바꾸어 보겠습니다.
=SUBSTITUTE(A4,“Dell”,“HP”)
SUBSTITUTE(A4,“Dell”,“HP”,1)를 사용하면 첫 번째 Dell만 HP로 바뀝니다.
10. LEFT, RIGHT, MID
대용량 연락처 데이터베이스를 다룰 때 왼쪽, 가운데, 오른쪽 함수를 사용하면 셀 내에서 특정 부분을 추출할 수 있습니다. 큰 텍스트 문자열에서 이름, 중간 이름, 성, 지역 번호, 제품 코드 등을 추출할 수 있습니다. 수작업을 줄여주고 신속하게 작업을 완료할 수 있습니다. 예제를 통해 실제로 어떻게 작동하는지 확인해 보겠습니다.
위 이미지에서와 같이 셀 A6에 마이클 제임스 로스가 포함되어 있다고 가정해 보겠습니다. 이제 이 함수를 사용하여 그의 이름, 중간 이름, 성을 추출할 수 있습니다.
=LEFT(A6,7) ''' Michael
=MID(A6,9,5) ''' James
=RIGHT(A6,4) ''' Ross
LEFT 함수를 사용하면 Excel은 왼쪽(Michael)에서 문자를 계산하고, RIGHT 함수는 오른쪽(Ross)에서 문자를 계산하며, MID 함수를 사용하면 시작 위치(왼쪽에서 9)와 길이(James의 경우 5)를 입력해야 중간 문자를 추출할 수 있습니다.
11. VLOOKUP, HLOOKUP
테이블 내에서 특정 값을 추출하는 데는 VLOOKUP과 HLOOKUP이 사용됩니다. VLOOKUP은 수직 조회를 수행하고 HLOOKUP은 동일한 작업을 수평으로 수행합니다. 함수를 자세히 이해해 보겠습니다.
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
각 인수의 의미는 다음과 같습니다.
- lookup_value: 테이블에서 찾고자 하는 값을 입력합니다.
- table_array: 찾고자 하는 값이 포함된 첫 번째 열부터 시작하여 테이블에서 데이터가 포함된 셀의 범위를 입력합니다.
- col_index_num 또는 row_index_num: 함수가 값을 추출하여 반환할 열 또는 행 번호(Excel 시트 번호를 사용하지 않고 선택한 첫 번째 열/행에서 “1”로 시작)입니다.
- range_lookup: 여기에는 두 가지 옵션이 있습니다. 사용 용도에 따라 하나를 선택할 수 있습니다. TRUE는 대략적인 일치 항목을 찾는 것이고, FALSE는 정확히 일치하는 항목을 찾는 것입니다. 정확히 일치하는 항목을 찾을 수 없는 경우 Excel은 #N/A를 반환합니다.
98765 ID를 가진 제품의 가격을 찾고 싶다고 가정해 보겠습니다. 수식은 다음과 같습니다.
=VLOOKUP(98765, A6:B10,2,FALSE)
이 함수는 A6에서 B10 사이의 범위에서 98765를 검색합니다. 검색된 두 번째 열의 값을 반환합니다. HLOOKUP 함수에 동일한 수식을 사용하면 검색된 두 번째 행에서 동일한 값을 찾습니다.
12. INDEX & MATCH
INDEX와 MATCH 함수는 VLOOKUP이나 HLOOKUP 함수보다 더 많은 유연성을 제공합니다. 이를 사용하여 모든 방향으로 조회하고 여러 조건을 지원할 수 있습니다. INDEX는 테이블의 값 또는 범위를 반환하는 반면, MATCH는 범위 내 위치를 표시합니다.
=INDEX(B2:B10,MATCH(“XPS 14”,A2:A10,0))
대규모 데이터베이스에서 XPS 14의 가격을 찾고 싶다고 가정해 보겠습니다. 여기서 MATCH(“XPS 14”,A2:A10,0)는 A2~A10 범위에서 XPS 14를 검색하고 그 위치를 표시합니다(7번째 행에 있는 경우 결과는 7). 0은 조회 값과 정확히 일치하는 첫 번째 값(이 경우 XPS 14)을 찾습니다.
이제 =INDEX(B2:B10,7)은 7번째 행에 제품의 가격을 표시합니다. Excel 고급 사용자는 유연성, 견고성 및 효율성 때문에 INDEX & MATCH 함수를 더 선호합니다.
마치며
Excel의 핵심 함수를 활용하면 복잡성을 없애고 업무를 편리하게 처리할 수 있습니다. 이러한 함수 외에도 생산성을 높이기 위해 Excel의 매크로(VBA)를 활용하는 것도 좋습니다. 매크로로 Excel을 자동화하는 방법을 알아보려면 [여기]를 참고하세요.
'Excel' 카테고리의 다른 글
| Word를 Excel로(또는 그 반대로) 변환하는 쉬운 방법 (2) | 2024.10.10 |
|---|---|
| Excel로 할 수 있는 멋진 활용법 10가지 (11) | 2024.10.10 |
| Excel용 최고의 무료 프로젝트 관리 및 플래너 템플릿 8가지 (6) | 2024.10.06 |
| '확인란' 기능이 Excel에서 가장 과소평가된 이유와 활용법 (6) | 2024.10.05 |
| 전문가들이 사용하는 고급 Excel 데이터 정리 방법 (6) | 2024.10.04 |
