엑셀 Excel 중급 <11. 함수설명 찾기/참조 column row Hlookup Vlookup indirect match>

엑셀 Excel

11. 함수설명 찾기/참조 column row Hlookup Vlookup indirect match

 

 

 

 

안녕하세요! 태마입니다.

엑셀 Excel 중급 강좌입니다.

 

 

강좌의 경우 

1. 주제 간단 정리 & 간단 개인 의견

2. 상세 주제 정리 & 상세 개인 의견

으로 이루어져 있습니다.

 

엑셀 Excel

 

포스팅 시작하겠습니다 :)

 

 

 

 

---

 

 

 

 

 

반응형

 

 

 

 

 

 

1.  주제 간단 정리 & 간단 개인 의견

 

 

 

 

 

 

함수중에서도 찾기/참조를 담당하는 함수중에 

실제로 제가 많이 사용하는 함수를 선정하였습니다.

 

column()()안의 셀 열의 값을 가져옵니다..

 

row()()안의 셀 행의 값을 가져옵니다..
Hlookup(목표,범위,행의순서,0)

범위에서 목표값을 찾아, 목표값으로부터 행의 순서만큼 떨어져있는 값을 가져옵니다.

 

Vlookup(목표,범위,열의순서,0)

범위에서 목표값을 찾아, 목표값으로부터 열의 순서만큼 떨어져있는 값을 가져옵니다.

 

indirect()

연결할 셀의 주소를 넣으면 셀의 값을 가져옵니다.

 

match(목표,범위,0)

범위에서 목표값을 찾아, 범위 첫번째부터 몇번째에 위치하는지 값을 찾습니다.

 

 

 

 

 

---

 

 

 

 

 

반응형

 

 

 

 

 

 

1.  주제 간단 정리 & 간단 개인 의견

 

다량의 데이터를 관리할 때,

그리고 각각의 그것을 엑셀 자동완성으로 해결 할 수 없을때,

column과 row 함수를 이용해 indirect에 넣어 다른 데이터 값을 끌어오게 할 수 있는 등

굉장히 무궁무진하게 활용도를 가진 함수입니다.

 

Vlookup의 경우에는, 데이터를 관리하는 직업 등,

엑셀을 주로 사용하는 사무직의 경우 거의 필수적으로 사용하는 함수입니다.

 

match함수의 경우 실 사용에서 많이 써본 기억은 없습니다.

 

 

 

 

 

 

---

 

 

 

 

 

 

반응형

 

 

 

 

 

 

2. 상세 주제 정리 & 상세 개인 의견

 

 

 

column 함수의 경우 행에는 상관 없이 열의 숫자를 가져옵니다.

 

 

row 함수의 경우 열에는 상관 없이 행의 숫자를 가져옵니다.

 

 

hlookup 함수의 경우, 찾으려고 하는 값의 열의 고유 값을 넣고

범위를 꼭 고유 값을 첫번째 행으로 하는 범위를 설정 후,

그 값이 몇칸이나 떨어져 있는지 입력하여야 함.

 

+ 위 함수에서 E2는 박머시기로, 이름은 다른 행과 겹치지 않습니다.(주민등록번호도 마찬가지)

꼭 hlookup을 할때 고유값으로 설정하여야 하는 이유는,

중복된 값이 있으면, 원하는 데이터 값을 불러올 수 없기 때문입니다.

중복된 값이 있다면, 데이터는 가장 왼쪽에 위치한 것으로 계산됩니다.

 

+범위 선택할때는, E2 = 박머시기를 포함한 첫 행으로 삼게,

그리고 불러오려는 값이 있는 행 이상의 범위를 설정해야합니다.

C2:H5까지 선택하였는데, 

앞에 C2는, 박머시기를 포함한 첫 행

A2~E2 사이에서 선택하면 되고,뒤에 H5는, 불러오려는 값이 3칸 띄어져 있는 값이므로,E4와 같거나 보다 큰 값 E4~ZZZZ9999 사이에서 선택하면 됩니다.

즉 범위를, A2:H5, D2:Z5, E2:ZZ5는 가능하지만,

A1:H5, F2:Z5, B3:C5는 잘못 설정한 것입니다.

 

 

vlookup 함수의 경우, 찾으려고 하는 값의 행의 고유 값을 넣고

범위를 꼭 고유 값을 첫번째 열으로 하는 범위를 설정 후,

그 값이 몇칸이나 떨어져 있는지 입력하여야 함.

 

+ 위 함수에서 G3 박머시기로, 이름은 다른 열과 겹치지 않습니다.(주민등록번호도 마찬가지)

꼭 vlookup을 할때 고유값으로 설정하여야 하는 이유는,

중복된 값이 있으면, 원하는 데이터 값을 불러올 수 없기 때문입니다.

중복된 값이 있다면, 데이터는 가장 위쪽에 위치한 것으로 계산됩니다.

 

+범위 선택할때는, B5 = 박머시기를 포함한 첫 행으로 삼게,

그리고 불러오려는 값이 있는 행 이상의 범위를 설정해야합니다.

B3:E8까지 선택하였는데, 

앞에 B3는, 박머시기를 포함한 첫 행

B1~B5 사이에서 선택하면 되고,뒤에 E8는, 불러오려는 값이 3칸 띄어져 있는 값이므로,D5와 같거나 보다 큰 값 D5~ZZZZ9999 사이에서 선택하면 됩니다.

즉 범위를, B1:H5, B4:Z5, B5:ZZ5는 가능하지만,

A1:H5, C2:Z5, B3:C5는 잘못 설정한 것입니다.

 

 

 

indirect는 안에 문자값이나, 함수가 들어가야합니다.

B3은 문자로 인식하지 않고 셀로 인식하기 떄문에 오류가 뜹니다.

 

 

""문자표시를 넣어주면, 제대로 인식합니다.

 

 

아직 char 함수에 대해 다루지는 않았지만,

이런식으로 함수를 섞어 사용 가능합니다.

&3 대신에 &row()를 넣어도 값은 동일하게 나왔을 겁니다.

 

 

match 함수의 경우, E2 박머시기가 범위에서 몇번째에 위치해 있는지를 나타냅니다.

따라서 열이나 E2가 포함된 열이나, 행 하나만 선택하여야 합니다.

 

 

 

 

 

 

---

 

 

 

 

 

 

반응형

 

 

 

 

 

 

2. 상세 주제 정리 & 상세 개인 의견

*개인 의견이므로 객관적인 정보는 아니라는 점 알려드립니다.

 

Vlookup은 처음에는 엄청 헷갈리고, 복잡해보일 수 있으나,

사실 가장 간단한 메커니즘을 가지고 있는 함수입니다.

따라서 실 사용하기도 정말 좋은 함수이기도 합니다.

 

indirect에서 예시로 보다시피, 

함수와 함수를 섞어서 쓰는 것이 응용 마지막 단계인 만큼,

함수 범위 안에 어떤 데이터가 들어가야 하는지,

기본형을 꼭꼭 알아두셔야 합니다.

 

  

엑셀 Excel 중급 <12. 함수설명 텍스트 char left right mid replace substitute>

으로 돌아오겠습니다.

 

 

 

 

 

---

 

 

 

반응형

 

<엑셀 Excel>

9. 함수설명 수학/삼각 round rounddown roundup sum sumif sumifs

http://te-ma.tistory.com/85

 

10. 함수설명 통계 count counta countblank countif countifs max min

http://te-ma.tistory.com/86

 

12. 함수설명 텍스트 char left right mid replace substitute len

http://te-ma.tistory.com/128

 

13. 함수설명 논리 and or false true if iferror

http://te-ma.tistory.com/129

 

14. 조건부 서식 넣기

 

http://te-ma.tistory.com/132