편리한 프로그램 배우자

엑셀 MATCH & INDEX 함수 함게 사용하기

엑셀 INDEX 함수와 MATCH 함수 2개를 개별로 설명드렸습니다. 

하지만 보통 2개를 통시에 자주 사용을 합니다. 

2개를 동시에 사용을 하면 더욱 편리하게 원하는 데이터 값을 구해 낼 수 있습니다.

함수 2개 사용 크게 어렵지 않으니 따라 해 보세요.

혹시 엑셀 MATCH 함수와 INDEX 함수에 대해서 자세히 알고 싶으시면 아래 링크에서 따라 해 보세요.

엑셀 MATCH 함수 사용하기

엑셀 INDEX 함수 사용하기

1. 폐업 순위에 맞는 지역을 엑셀 MATCH 함수와 INDEX 함수를 통해 찾기를 해보겠습니다.

2.  설정한 빨간 블록은 I5셀( 서울특별시)에 입력된 수식입니다. 

    기본 조건

   1) F열 폐업 순위 만들기 _ RANK함수로 E열을 기준하여 순위를 만듦

   2) H열 1부터 17까지 입력 
   

3. INDEX 함수에 셀 범위의 조건을 입력합니다.

   1번 참조 영역 설정 _ 찾아서 입력하고 싶은 참조 영역입니다. (A열의 구역을 선택)

   2번 참조 영역의 몇 번째 값을 선택 _ 

        셀 H5의 값은 폐업률 순위 1위입니다 F열에 보면 1위가 몇 번째에 있는지 값을 구해야 됩니다.

        MATCH 함수로 몇 번째에 있는지 값을 구하면 됩니다.

        2번에 입력된 MATCH 함수를 참조해주세요

   3번 0을 입력 

4. 완료되었습니다. 

   A열부터 F열까지 있는 데이터 중에서 필요한 값만 가져와서 
   폐업률이 높은 지역 어디 인지 알아보았습니다 

   (데이터는 정부에서 제공하는 업태 "제과점"영업의 데이터를 가공한 것입니다.)

엑셀 INDEX 함수

데이터 분석 시 특정 위치에 있는 조건에 맞는 값을 가져와야 하는 경우가 있습니다.

보통 INDEX함수를 통해서 사용을합니다.

엑셀 데이터에서 일치하는 데이터 위치의 값을 INDEX 함수에 대해서 알아보겠습니다.

F열 : 폐업률의 순위를 구한 값

I열 : 만들고자 하는 순위 입력

H열 : I열 순위에 맞게 F열의 위치 (MATCH 함수)

J열에 I열에 순위에 맞게 A열 매칭에 맞게 값 입력 / 단 H열에 조건에 맞는 위치의 값을 입력하는 것입니다.

보통 F열과 A열만 복사해서 값으로 변경 후 / F열을 내림차순으로 정렬하면 됩니다.

INDEX 함수를 통해서 J열을 입력 진행해보겠습니다. 

H열은 MATCH 함수를 통해서 값을 구했습니다. 

1. INDEX함수를 불러냅니다.

   1번을 클릭해서 함수 마법사를 불러옵니다.

   2번 함수 검색에서 INDEX를 검색하고 확인을 합니다.

2. INDEX 함수를 불러오면 2개를 중 하나를 선택합니다. 1번 선택해주세요.
   첫 번째가 INDEX 함수(배열형) _ 참조 영역 1개

   두 번째가 INDEX 함수(벡터형) _ 참조영역 2개 이상

3. 조건을 입력합니다. 

1번 참조 영역 설정 _ A열 구역 설정 / 예) A5 : A21 구역으로 범위 설정 

2번 H열 중 셀 1개 선택  / 예) H5선택 A5:A21범위 안에서 H5 셀(9번째)을 선택 

3번 0을 입력 

4. J열을 드래그합니다. 

  1)  H5 =  9 (F열 폐업 순위에서 9번째가 폐업률 1위라는 뜻임) / MATCH 함수로 9번째인 것을 구함
  2)  H5 9를 통해서 A5:A21범위 중에 9번째(서울특별시)를 J5에 입력 / INDEX 함수를 통해서 A열 범위 중 9번째를 구함 

엑셀 match 함수 사용하기

데이터 분석 시 찾기를 사용해야 하는 경우가 많이 있습니다.

데이터가 많으면 많을수록 찾기 기능은 더욱 필요합니다 

또한 원하는 값을 찾은 후 어떤 형태로 변형을 하는 경우도 많습니다.

엑셀 데이터에서 일치하는 데이터 값의 위치를 찾는 함수인

match 함수에 대해서 알아보겠습니다 

아래 데이터는 정부에서 제공해준 데이터중 업태 구분명에 '제과점영업'을 정리하였고

아래처럼 지역 중에서 폐업률이 높은 지역 순으로 순위를  정리해보겠습니다.

아래처럼 정리를 하려면 2개의 함수가 사용됩니다 

1차 match 함수를 통해서 폐업 1순위가 어디에 위치해있는지 파악

2차 indext 함수를 통해서 폐업 위치에 있는 구역 이름을 매칭 하는 것입니다. 

1차 match 함수를 사용해보겠습니다 

1. match 함수를 활용해서 폐업 지역 위치를 매칭 해보겠습니다. 

   1번은 폐업 순위입니다. 폐업률을 기준으로 폐업 순위를 구한 것입니다 (rank 함수)

   2번은 구하고자 하는 폐업률 순위  1위 ~ 17위 입력

           이후 "폐업 지역 위치"에 2번을 기준으로 해서 1번을 매칭 시키는 match함수를 사용하는 것입니다.

   3번 함수를 클릭해주세요 

   4번 함수 검색에 match를 검색 후 선택해주세요.

2. match 함수에 입력을 진행합니다.

  1번 찾고자 하는 값 _ 폐업률 순위 1을 선택

  2번 매칭 하고자 하는 범위 _ "폐업 순위" f열을 범위를 설정

  3번 0이면 같은 값 1이면 작거나 같은 값 중 최대값을 찾습니다. 

3. 폐업 순위 1위 ~ 17위까지 정리되어 있는 순위에 맞는 위치를 매칭 하였습니다.

   I열 기준해서 F열의 값을 H열에 매칭 하였습니다. 

엑셀 순위(RANK 함수) 구하기

데이터 분석을 하다 보면 순위를 구해야 하는 경우가 있습니다.

어느 지역이 높은지 순위를 구해서 확인해보고 원인이 무엇인지 점검해야 하는 경우입니다.

보통 순위를 구하는 함수로 RANK 함수를 사용합니다.

RANK 함수를 통해서 순위를 구해 구해보겠습니다.

아래 데이터는 정부제공해준 데이터중 업태 구분명에 '제과점영업'을 정리하였고

지역 중에서 폐업률이 높은 지역 순으로 순위를 구해보겠습니다. 

1. RANK 순위 함수로 순위를 넣어 보겠습니다.

   1) F5 셀을 클릭하고 1번 함수를 클릭해주세요

   2) 2번 함수검색에 "순위"를 넣어서 검색해주세요.

   3) RANK 함수를 선택하고 확인을 클릭해주세요.

2. RANK 함수에 인수를 설정하시면 됩니다. 

   1) 1번 Number에 순위를 넣을 셀을 선택 E5번 선택

   2) 2번 순위를 설정할 범위 선택 _ 절대 참조를 꼭 넣어주세요.

   3) 순위를 오름차순으로 할지 내림차순으로 할지 선택

3. F5셀에 함수 설정이 끝나면 드래그해서 전체에 함수 복사

엑셀 소수점 변환 함수 모음

엑셀 소수점 변환 함수를 개별로 하나씩 하나씩 소개해드렸습니다.

소수점 변환하는 함수를 한 번에 다시 총정리 해 드겠습니다.

INT 함수와 ROUND 함수의 변환이라는 공통점은 맞으나 INT 소수점을 없애는 함수입니다. 

무조건 정수로 나타내게 되어있습니다. 

각 함수 별로 사용한 모습을 보겠습니다. 

1.  INT 함수와 ROUNDUP함수입니다.

INT 함수 변환하는 법 알아보기

ROUNDUP함수 변환하는 법 알아보기

2. ROUND함수와  ROUNDDOWN 함수입니다. 

ROUND 함수 변환하는 법 알아보기

ROUNDDOWN함수 변환하는 법 알아보기

엑셀 소수점 반올림하기

엑셀 데이터를 만들고 보고서에 소수점이 나오는데

일반적으로 소수점을 반올림하여 데이터를 만들고 있습니다.

소수점 반올림과 몇 번째 자리에서 반올림 어떻게 하면 될까요?

ROUND 함수를 사용해서 특정 위치의 소수점에서 반올림하시면 됩니다. 

ROUND 함수를 사용해서 소수점을 반올림 변경해보겠습니다.

1. 아래처럼 소수점이 있는 데이터를 ROUND함수를 활용해서 변경해보겠습니다 

2. 1번에 있는 함수 로고를 클릭해서 함수 마법사를 실행해주세요

   2번에 함수 검색에 ROUND를 적고 검색을 눌러주세요, 범주 선택을 통해서 찾기도 가능합니다. 

   3번에 확인을 클릭해주세요.

3. 1번에 변경하고 싶은 셀을 클릭해주세요.

   2번에 반올림할 위치의 소수점 자리를 적어주세요.

   3번에 확인을 클릭해주세요.

   4번에 ROUND함수가 익숙해지시면 바로 입력하여 함수를 활용하셔도 됩니다. 

   예시) A2 숫자의 소수점 1자리에서 올림 하세요.

          실제 소수점 2번째 자리가 반올림되어 1번째 자리까지 표시됩니다. 

4. ROUND 함수를 변경해보았습니다.

  B열 수식을 보면 소수점 0번째 반올림, 1번째 반올림, 2번째 반올림... C열에 변경된 값이 나옵니다. 

  B열을 참고해서 소수점 몇 번째에서 반올림해야 되는 결정

하세요

엑셀 소수점 이하 강제 내림 하기

엑셀 데이터를 만들고 보고서에 소수점이 나오는데 

소수점을 강제로 내림으로 데이터를 만들어야 하는 경우가 있습니다. 

소수점을 강제로 버리기 또는 특정 위치의 소수점을 강제로 내림을 

어떻게 하면 될까요??

ROUNDDOWN 함수를 활용해서 특정 위치의 소수점을 내림하시면 됩니다. 

ROUNDDOWN 함수를 사용해서 소수점을 변경해보겠습니다. 

1. 아래처럼 소수점이 있는 데이터를 ROUNDDOWN함수를 활용해서 변경해보겠습니다. 

2. 1번에 있는 함수 로고를 클릭해서 함수 마법사를 실행해주세요

   2번에 함수 검색에 ROUNDDOWN을 적고 검색을 눌러주세요, 범주 선택을 통해서 찾기도 가능합니다.

   3번에 확인을 클릭해주세요.

3. 1번에 변경하고 싶은 셀을 클릭해주세요.

   2번에 내림할 위치의 소수점 자리를 적어주세요.

   3번에 확인을 클릭해주세요.

   4번에 ROUNDDOWN 함수가 익숙 해지시면 바로 입력하여 함수를 활용하셔도 됩니다. 

   예시) A2 숫자의 소수점 0자리에서 내림하세요

          1 단위의 숫자를 내림해야 되기 때문에 1로 표시됩니다. 

4. ROUNDDOWN함수를 변경해 보았습니다.

   B열 수식을 보면 소수점 0번째 내림, 1번째 내림, 2번째 내림..... C열에 변경된 값이 나옵니다.

   B열을 참고해서 소수점 몇 번째에서 내림해야 되는 결정 하세요 

엑셀 소수점 올림 변경

엑셀 데이터를 만들고 보고서에 소수점이 나오게 마련입니다.

소수점을 올림으로 데이터를 만들어야 하는 경우가 있습니다.

예를 들어 인원수를 뽑는데 소수점이 나오면 데이터를 강제로 올림으로 해야 되는데 

어떻게 하면 될까요??

ROUNDUP 함수를 활용해서 소수점을 변경을 하면 됩니다. 

ROUNDUP함수를 사용해서 소수점을 변경해보겠습니다.

1. 아래처럼 소수점이 있는 데이터를 ROUNDUP함수를 활용해서 변경해보겠습니다. 

2. 1번에 있는 함수 로고 클릭해서 함수 마법사를 실행 해 주세요.

   2번에 함수 검색에 ROUNDUP을 적고 검색을 눌러주세요, 범주 선택을 통해서 찾기도 가능합니다.

   3번에 확인을 클릭해주세요.

3. 1번에 변경하고 싶은 셀을 클릭해주세요.

   2번에 올리할 위치의 소수점 자리를 적어주세요.

   3번에 확인을 클릭해주세요.

   4번에 ROUNDUP 함수가 익숙해지시면 바로 입력하여 함수를 활용하셔도 됩니다.

  예시 ) A2 숫자의 소수점 0자리에서 올림 하세요

           1 단위의 숫자를 올림 해야 되기 때문에 2로 표시됩니다.

4. ROUNDUP함수를 변경해보았습니다. 

   B열 수식을 보면 소수점 0번째 올림, 1번째 올림, 2번째 올림.... C열에 변경된 모습이 나옵니다.

   수식 2번째를 무엇을 입력해야 되는지 알 수 있으니 참고해주세요.