편리한 프로그램 배우자

엑셀 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열에 매칭 하였습니다. 

구글 프레젠테이션(파워포인트) 시작하기

구글을 활용해서 프레젠테이션(파워포인트)도 사용 가능합니다.

마이크로소프트 오피스를 구매해야 파워포인트를 사용 가능하지만

구글은 파워포인트를 무료로 사용할수 있게 하고 있습니다.

이름은 파워포인트가 아닌 프리젠테이션이라는 이름으로 무료로 공개 중입니다.

구글 프레젠테이션과 마이크로 소프트 파워포인트와 차이는 많이 있습니다.

제가 볼 때는 구글은 공유에 우위를 두겠어로 판단되고, 마이크로 소프트는 보고서 제작에 우위를 두것으로 판단됩니다.

최근에 보면 마이크로 소프트도 공유가 되면서 두 회사다 차이가 계속 감소를 보이고 있습니다.

프레젠테이션(파워포인트)를 준비하신다면 나에게 맞는 것을 선택해서 활용하시면 되겠습니다.

구글 프리젠테이션 시작하기는 쉬우니 따라 해 보세요 

1. 크롬을 통해서 구글 홈페이지에 들어갑니다.

   구글의 모든 프로그램은 로그인을 통해서 진행되니 로그인해주세요.

  1번 클릭 : 크롬으로 구글 화면 오른쪽 상단 9개점 클릭해주세요.

  2번 클릭 : 드라이브 클릭해주세요.

2. 새로 만들기 클릭해주세요.

3. 구글 프레젠테이션 클릭해주세요.

4. 구글 프레젠테이션(파워포인트) 사용 준비 완료

   1번 : 제목 입력으로 쉬제 파일명 입력 가능

   2번 : 공유를 클릭해서 이 메일을 통해서 프레젠테이션 공유 가능하면 공유자가 수정도 가능합니다.

   3번 : 구글 프레젠테이션의 서식 활용 가능합니다

5. 다양한 구글 프리젠테이션 양식 툴입니다.

   제목을 보면 무엇을 만들기에 용이한지 확인이 가능합니다. 

6. 레시피 소개 양식 툴입니다.

   기본적인 리포트를 작성하기에는 편리하게 되어 있습니다.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

엑셀 단축키 모음 _ F 단축키 모음

엑셀을 하다 보면 단축키를 많이 사용하는데.

F 단축키를 많이 사용합니다. 

F 단축키에 대해서 설명드리겠습니다 

■ 엑셀 F 단축키 

단축키는 시간을 단축을 위해서 만들어져서 잘 활용해보세요.

F4 기능 수식에는 절대 참조, 상대 참조를 할 수 있고 셀만잡을때는 마지막 작업을 반복 수행합니다. 

F 단축키에 유용한 기능을 담은 것들이 많은데 잘 활용해 보세요.

■ F 단축키 기능 설명

엑셀 조건부 서식 데이터 막대

엑셀로 데이터 분석을 하다 보면 숫자만 있을 때랑 그래픽이 들어갔을 때랑은 시각적으로 틀립니다

아래 두가지 파일의 폐업률을 보면 그래픽이 있고 없고입니다.

시각적인 요소가 들어가면 눈에 잘 보여 분석하기가 편해집니다. 

아래는 엑셀 조건부 서식 데이터 막대를 활용한 이미지입니다.  

엑셀 조건부 서식 중 데이터 막대에 대해서 알아보겠습니다.

엑셀 조건부 서식은 특정 범위 안에 조건에 따라 원하는 서식으로 표현하는 것입니다.

아래 엑셀 파일을 보면서 엑셀 조건부 서식 설명드리겠습니다. 

1. 엑셀 원하는 범위에 데이터 막대 색을 선택합니다. 

   1번 범위 설정, 서식을 통해서 강조하고 싶은 셀의 범위를 설정

   2번 홈으로 눌러 주세요

   3번 조건부 서식 아래 삼각형에 마우스를 가져가고 데이터 막대가 보입니다.

        데이터 막대에 오른쪽 삼각형에 마우스를 가져갑니다.

   4번 그러데이션 채우기 중 1개 선택 

2. 원하는 데이터 막대 색을 선택하면 폐업률에 그라데이션을 통해서 색이 입혀집니다.

   데이터 색이며 폐업율에 따라 셀 칸 안에서 그래프 형태로 색이 칠해집니다.

   예) 38%의 경우를 보면 현저히 낮은 그래프를 보이는 것을 확인할 수 있습니다. 

   색상과 같이 수정이 필요하면 조건부 서식에서 그러데이션 채우기에서 기타 규칙을 들어가 주세요

   또는 조건부 서식 -> 규칙 관리 -> 규칙 편집을 통해서 들어가도 됩니다. 

3. 그래프 형식을 내가 원하는 조건으로 변경합니다.

   1번에 최소값, 최대값에 값을 입력하면 보이는 형태는 달라집니다.

   2번에 색 형태도 변경 가능합니다.  

엑셀 빈칸 채우기 

데이터 분석을 하면서 피벗테이블을 하게 됩니다. 

피벗테이블을 하면 동일한 열의 조건은 빈칸으로 나타납니다.

빈칸으로 나타나면 데이터를 분석할 때 어려움이 발생하는데

데이터 양이 작을 경우에는 수작업으로 해도 가능하지만 양이 많을 때는 빈칸을 채우기가 힘듭니다.

간단한 방법으로 빈칸을 채울 수 있으니 따라 해 보세요.

1. Ctrl + A (전체 블록 설정) 후 Ctrl + C ( 복사 ) 마우스 오른쪽 클릭해서 값으로 붙여 넣기 

   아래처럼 강원도, 경기도 밑에는 비어 있습니다. 

   1차적으로 피벗테이블에서 복사해서 값으로 붙여 넣기를 통해 일반형태로 변경시켜주세요 

2. 비 어이 있는 열에 채워 넣을 셀부터 해서 드래그를 해서 셀을 잡아주세요. (강원도부터 클릭)

   그리고 F5를 클릭해서 이동창을 열어주시고 옵션을 클릭해주세요.

   비어 있는 칸에 바로 위에 있는 입력글로 채워 넣어 넣는 형태입니다 

3. 빈셀을 체크해주시고 확인

   블록 잡은 범위에서 빈칸만 잡은 것입니다. 

 아래처럼 빈칸만 잡힌 것을 볼 수 있습니다.

5 빈칸에 =기호를 입력하고 셀에 동일하게 채워 넣을 글자가 셀 선택

  Ctrl + Enter 클릭하면 끝

  빈셀에  = 기호 입력 후 방향키 ↑ 클릭해서 A5(강원도) 선택 후 Ctrl + Enter (컨트롤과 엔터를 동시 클릭)

Ctrl + Enter의 기능은 반복 실행입니다. 

빈칸을 잡고 앞에 실행한 명령을 반복 실행했다고 보시면 됩니다. 

아래처럼 7행은 A6, 8행은 A7.... 이렇게 변경됩니다.