엑셀 많이 사용한 단어 찾기, 값 구하기(INDEX, MODE, MATCH)

엑셀 범위에서 중복적으로 많이 사용한 단어를 찾고 해당 단어의 적용된 값의 합계를 구하는 방법으로 단일 함수를 적용하여서 사용을 할 수는 없고 INDEX, MODE, MATCH 세함수를 조합하여 적용을 하는 방법입니다.

엑셀 MATCH 함수 적용

범위 내 문자를 함수에 적용할 수 있도록 문자 값을 숫자로 변경하기 위해서 매치 함수를 적용합니다.

Match 함서 적용

매치 함수를 이용하여 해당 단어의 셀 위치 번호 값을 구합니다.

MATCH(lookup_value, lookup_array, [match_type])

lookup_value에 찾을 단어 등의 값을 지정하는데 위처럼 범위를 지정하면 해당 범위의 모든 값이 찾을 값으로 지정이 됩니다.

두 번째 lookup_array는 지정한 단어 등의 값으로 찾을 범위를 지정합니다.

[match_type]은 선택 옵션이지만 일치하는 값을 가져오기 위해 0을 입력합니다.

=MATCH(C2:C14,C2:C14,0)

참조값이 있는 범위와 검색 범위를 같게 하면 해당 값을 찾아서 보여주며 중복이 될 경우 상위 번호로 적용이 됩니다.

=MATCH("외식",C2:C14,0)

참조 범위를 지정하지 않고 직접 조건을 입력하면 5라는 값을 찾을 줍니다.

엑셀 MODE 함수 적용

숫자로 변경된 값의 빈도가 많은 숫자를 찾기 위해서 모드 함수를 이용합니다.

MODE 함수 적용

MODE(number1,[number2],...)

모드 함수를 이용하여 중복된 값을 찾을 수 있으며, 중복으로 열을 추가하여 사용할 수 있습니다.

숫자로 변경한 MATCH 값에서 반복적으로 나오는 숫자가 5이므로 5라는 값을 찾아줍니다.

매치와 모드 함수 적용

MODE,MATCH 복합함수 적용

=MODE(MATCH(C2:C14,C2:C14,0))

두 개의 셀을 차지하지 않도록 MODE 모드 함수 범위로 MATCH 함수를 적용하면 한 번에 값을 구합니다.

엑셀 INDEX 함수 적용

숫자로 변경한 값을 다 문자로 되돌리기 위해서 인덱스 함수를 적용합니다.

INDEX 함수 적용

매치 함수는 문자를 숫자로 변경하였다면 인덱스 함수는 숫자를 문자로 변경을 하는 역할입니다.

INDEX(array, row_num, [column_num])

array에는 대상이 있는 범위를 지정하고 row_num, column_num 둘 중 하나만 사용하거나 모두를 사용할 수 있습니다.

위 같은 경우 열을 대상으로 하기에 한 번만 범위 또는 값을 적용합니다.

=INDEX(C2:C14,2)

위와 같이 두 번째 적용 서식을 범위를 지정하지 않고 2라는 숫자를 입력하면 2번에 해당하는 값인 통신이라는 값을 찾아줍니다.

최종 조합 적용

=INDEX(C2:C14,MODE(MATCH(C2:C14,C2:C14,0)))

3가지 함수를 모두 적용하면 최종적으로 해당 범위에서 가장 많이 사용한 결과 값을 찾을 수 있으며, 결과적으로 함수의 범위는 한 곳이 지정 되게 됩니다.

SUMIF 함수로 적용한 함수 합계 구하기

구한 값의 합계 구하기

조건 합계 함수인 SUMIF를 이용하여 구한 값의 합계를 할 수 있습니다.

=SUMIF(C2:C14,H2,D2:D14)

첫 번쨰는 문자가 있는 범위를 지정하고 두 번째는 조합하여 찾은 단어가 있는 셀을 지정하고 세 번째는 합계를 할 숫자가 있는 범위를 지정하면 앞서 적용하여 찾은 단어의 해당하는 값의 합계를 구할 수 있습니다.

엑셀 함수 조건에 맞는 데이터 값 찾기

추가적인 IFERROR 활용

#N/A

지정한 범이 안에 빈 곳이 있을 경우 #N/A라는 오류나 나는데 서식을 완성해 놓고 사용을 한다면 빈칸 때문에 값을 구하지 못하게 됩니다.

=INDEX(C2:C14,MODE(IFERROR(MATCH(C2:C14,C2:C14,0),0)))

IFERROR 함수를 이용하여 에러가 나는 문제를 해결할 수도 있습니다.

엑셀 막대그래프 아이콘 조건부서식

엑셀 틀 고정 하는 방법

엑셀 문서 테두리 색 지정하는 방법