데이터 분석을 위한 10가지 필수 Microsoft Excel 함수

1. 사슬 같이 잇다

=CONCATENATE는 텍스트, 숫자, 날짜 등을 결합할 수 있으므로 데이터 분석에 가장 중요한 기능 중 하나입니다. 여러 셀에서 하나로. 이 기능은 다른 셀의 데이터를 단일 셀로 결합하는 데 특히 유용합니다. 예를 들어 마케팅 캠페인을 위한 추적 매개변수 생성, API 쿼리 작성, 숫자 형식에 텍스트 추가 및 기타 여러 작업에 편리합니다.

위의 예에서는 월과 매출을 단일 열에 함께 표시하려고 했습니다. 이를 위해 C2 셀에서 =CONCATENATE(A2, B2) 수식을 사용하여 Jan$700을 결과로 얻었습니다.

공식: =연결(결합하려는 세포)

2. 렌

=LEN은 기본적으로 주어진 셀의 문자 수를 출력하는 데이터 분석을 위한 또 다른 편리한 기능입니다. 이 기능은 글자 수 제한이 있는 제목 태그나 설명을 만들 때 주로 사용할 수 있습니다. 또한 길이가 길고 올바른 순서가 아닌 다른 고유 식별자 간의 차이점을 찾으려고 할 때도 유용할 수 있습니다.

위의 예에서 나는 매달 얻고 있는 조회수에 대한 수치를 계산하고 싶었습니다. 이를 위해 D2 셀에서 =LEN(C2) 수식을 사용하여 결과로 5를 얻었습니다.

공식: =렌(셀)

3. 조회

=VLOOKUP은 아마도 데이터 분석에 익숙한 사람이라면 가장 잘 알 수 있는 기능 중 하나일 것입니다. 이를 사용하여 테이블의 데이터를 입력 값과 일치시킬 수 있습니다. 이 기능은 검색 범위에 의해 제어되는 정확한 일치 및 근사치의 두 가지 일치 모드를 제공합니다. 범위를 FALSE로 설정하면 정확히 일치하는 항목을 찾고 TRUE로 설정하면 대략적인 일치 항목을 찾습니다.

위의 예에서 특정 월의 조회수를 조회하고 싶었습니다. 이를 위해 G4 셀에서 =VLOOKUP("Jun", A2:C13, 3) 수식을 사용했고 결과로 74992를 얻었습니다. 여기서 "Jun"은 조회 값이고 A2:C13은 "Jun"을 찾고 있는 테이블 배열이며 3은 수식이 6월에 해당하는 보기를 찾을 열 번호입니다.

이 기능을 사용할 때의 유일한 단점은 열로 정렬된 데이터에서만 작동하므로 이름이 수직 조회라는 것입니다. 따라서 데이터를 행으로 정렬한 경우 먼저 다음을 수행해야 합니다. 행을 열로 바꾸다.

공식: = 조회(lookup_value, table_array, col_index_num, [range_lookup])

4. 인덱스/매치

VLOOKUP 함수와 마찬가지로 INDEX 및 MATCH 함수는 입력 값을 기반으로 특정 데이터를 검색하는 데 유용합니다. INDEX와 MATCH를 함께 사용하면 잘못된 결과를 제공하는 VLOOKUP의 한계를 극복할 수 있습니다(주의하지 않는 경우). 따라서 이 두 함수를 결합하면 데이터 참조를 정확히 찾아내고 단일 차원 배열에서 값을 검색할 수 있습니다. 데이터의 좌표를 숫자로 반환합니다.

위의 예에서 1월의 조회수를 조회하고 싶었습니다. 이를 위해 =INDEX(A2:C13, MATCH("Jan", A2:A13,0), 3) 수식을 사용했습니다. 여기서 A2:C13은 수식이 반환할 데이터 열이고 "Jan"은 일치시키려는 값이고 A2:A13은 수식이 "Jan"을 찾고 0은 수식이 다음과 정확히 일치하는 항목을 찾기를 원함을 의미하는 열 값.

대략적인 일치를 찾으려면 0을 1 또는 -1로 대체해야 합니다. 따라서 1은 조회 값보다 작거나 같은 가장 큰 값을 찾고 -1은 조회 값보다 작거나 같은 가장 작은 값을 찾습니다. 0, 1 또는 -1을 사용하지 않으면 수식은 1, by를 사용합니다.

이제 월 이름을 하드코딩하지 않으려면 셀 번호로 바꿀 수 있습니다. 따라서 위에서 언급한 공식의 "Jan"을 F3 또는 A2로 대체하여 동일한 결과를 얻을 수 있습니다.

공식: = 인덱스(반환하려는 데이터의 열, MATCH(일치하려는 공통 데이터 포인트, 공통 데이터 포인트가 있는 다른 데이터 소스의 열, 0))

5. MINIFS/MAXIFS

=MINIFS 및 =MAXIFS는 최소/최대 값 집합을 가져와 특정 기준에서도 일치시킬 수 있다는 점을 제외하면 =MIN 및 =MAX 함수와 매우 유사합니다. 따라서 본질적으로 이 함수는 최소/최대 값을 찾아 입력 기준과 일치시킵니다.

위의 예에서는 학생의 성별을 기준으로 최소 점수를 찾고 싶었습니다. 이를 위해 공식 =MINIFS(C2:C10, B2:B10, "M")를 사용했고 결과는 27입니다. 여기서 C2:C10은 수식이 점수를 찾는 열이고 B2:B10은 수식이 기준(성별)을 찾는 열이며 "M"은 기준입니다.

마찬가지로 최대 점수의 경우 =MAXIFS(C2:C10, B2:B10, "M") 공식을 사용하여 결과 100을 얻었습니다.

MINIFS 공식: =미니프(최소 범위, 기준_범위1, 기준1,...)

MAXIFS에 대한 공식: =MAXIFS(최대 범위, 기준_범위1, 기준1,...)

6. 평균

=AVERAGEIFS 함수를 사용하면 하나 이상의 기준에 따라 특정 데이터 세트의 평균을 찾을 수 있습니다. 이 기능을 사용하는 동안 각 기준과 평균 범위가 다를 수 있음을 명심해야 합니다. 그러나 =AVERAGEIF 함수에서 기준 범위와 합계 범위는 모두 동일한 크기 범위를 가져야 합니다. 이 함수들 사이의 단수와 복수의 차이점을 알아차리셨습니까? 글쎄, 당신이 조심해야 할 곳입니다.

이 예에서는 학생의 성별을 기준으로 평균 점수를 찾고 싶었습니다. 이를 위해 =AVERAGEIFS(C2:C10, B2:B10, "M") 공식을 사용했고 결과로 56.8을 얻었습니다. 여기서 C2:C10은 수식이 평균을 찾는 범위이고 B2:B10은 기준 범위이며 "M"은 기준입니다.

공식: =평균IFS(평균 범위, 기준 범위1, 기준1,...)

7. 카운티프

이제 데이터 세트가 특정 기준을 충족하는 인스턴스 수를 계산하려면 =COUNTIFS 함수를 사용해야 합니다. 이 기능을 사용하면 쿼리에 무제한 기준을 추가할 수 있으므로 입력 기준을 기반으로 카운트를 찾는 가장 쉬운 방법이 됩니다.

이 예에서는 합격 점수(예: >=40)를 받은 남학생 또는 여학생의 수를 찾고 싶었습니다. 이를 위해 =COUNTIFS(B2:B10, "M", C2:C10, ">=40") 수식을 사용했습니다. 여기서 B2:B10은 수식이 첫 번째 기준(성별)을 찾는 범위이고 "M"은 첫 번째 기준, C2:C10은 수식이 두 번째 기준(표시)을 찾는 범위이고 ">=40"은 두 번째 기준입니다. 기준.

공식: =COUNTIFS(기준_범위1, 기준1,...)

8. 합산

=SUMPRODUCT 함수는 범위 또는 배열을 함께 곱한 다음 곱의 합계를 반환하는 데 도움이 됩니다. 상당히 다재다능한 기능이며 COUNTIFS 또는 SUMIFS와 같은 배열을 계산하고 합산하는 데 사용할 수 있지만 유연성이 추가되었습니다. SUMPRODUCT 내에서 다른 기능을 사용하여 기능을 더욱 확장할 수도 있습니다.

이 예에서는 판매된 모든 제품의 합계를 찾고 싶었습니다. 이를 위해 =SUMPRODUCT(B2:B8, C2:C8) 공식을 사용했습니다. 여기서 B2:B8은 첫 번째 배열(판매된 제품 수량)이고 C2:C8은 두 번째 배열(각 제품의 가격)입니다. 그런 다음 공식은 판매된 각 제품의 수량을 가격과 곱한 다음 모두 더하여 총 판매액을 제공합니다.

공식: =SUMPRODUCT(배열1, [배열2], [배열3],...)

9. 손질

=TRIM 함수는 여러 개의 공백이나 원하지 않는 문자가 있는 데이터 세트로 작업할 때 특히 유용합니다. 이 기능을 사용하면 데이터에서 이러한 공백이나 문자를 쉽게 제거할 수 있으므로 다른 기능을 사용하면서 정확한 결과를 얻을 수 있습니다.

이 예에서는 A7에서 Mouse와 pad라는 단어 사이의 모든 여분의 공백을 제거하고 싶었습니다. 이를 위해 공식 =TRIM(A7)을 사용했습니다.

수식은 단순히 여분의 공백을 제거하고 단일 공백으로 결과 마우스 패드를 전달했습니다.

공식: =트림(텍스트)

10. 찾기/검색

반올림은 데이터 세트 내에서 특정 텍스트를 분리하는 데 도움이 되는 FIND/SEARCH 기능입니다. 두 함수 모두 한 가지 주요 차이점을 제외하고는 수행하는 작업에서 매우 유사합니다. =FIND 함수는 대소문자를 구분하는 일치 항목만 반환합니다. 한편 =SEARCH 기능에는 이러한 제한이 없습니다. 이러한 함수는 비정상 또는 고유 식별자를 찾을 때 특히 유용합니다.

이 예에서 나는 =FIND(A2, B2) 공식을 사용하여 결과 1을 제공한 Guiding Tech 내에서 'Gui'가 나타난 횟수를 찾고 싶었습니다. 이제 Guiding Tech 내에서 'gui'가 표시된 횟수를 찾으려면 대소문자를 구분하지 않기 때문에 =SEARCH 공식을 사용해야 합니다.

찾기 공식: =찾기(find_text, within_text, [start_num])

검색 공식: =검색(find_text, within_text, [start_num])