[컴활1급필기 절대족보] 찾기/참조 함수

룩업 우리가 룩업이라는 의미가 무슨 뜻이 있어요 찾다 검색하다 이런 뜻이 있잖아요 그래서 룩업 같은 경우는 찾을 값을 찾을 범위에서 찾아요 얘를 여기에서 찾습니다 그래놓고 갖고오는 값은 반환 범위를 따로 줘요 여기까지만 줘도 되고 그다음에 엑슬로 컵의 특징 찾을 값이 없을 때 텍스트를 줄 수가 있어요 그리고 일치 유형 정확하게 일치하는 값을 갖고 와라 0을 주면 되는데 기본값 생략 가능하고 정확하게 일치하는 값 없으면 not avilable # NA 5가 떨어지는 거고 검색 방법은 처음부터 찾아라 1 생략가능 기본 값이고 그래서 찾을 값 찾을 범위 반환 범위 여기까지만 줘도 돼요 그래서 찾을 값을 찾을 범위에서 찾아서 반환 범위의 값을 반환해 준다 그리고 반환 열이 찾는 열에 어느 쪽에 있는지 관계 없어요 이게 바로 x 루크의 특징입니다 그래서 관계 없이 한 열에서 검색으로 찾아서 다른 열에 동일한 행에서 결과를 반환한다요 바로 반환범위에 있는 거를 그리고이 x 컵은 V 루과 다르게이 V 루은 이제 열에서 찾아오잖아 버티컬 루크인데여 항목이 있는 배열이 반환이 돼요 요게 바로 x 루크의 또 장점입니다 직접 보기로 하고요 워크시트의 동아리 학번 학과명 성명이 입력되어 있어요 그리고 아래쪽에 내려오면 이름을 입력하게 되면 그 이름에 해당되는 동아리가 검색되게 하는 수식을 세워야 돼요 보세요 어떻게 세웠냐 로컵 로컵 a 10 a 10은 성명이 입력되는 거고 그 성명을 어디서 찾아요 d 2에서 d 7 여기서 찾습니다 성명이 들어가 있 는 뒤에 2에서 뒤에 7 여기서 찾아서 그 성명에 해당되는 동아리 같은 위치에 있는 동아리를 가져오는 역할이 바로 x 룩이에요 그럼 동아리는 A2 A7 이잖아요 A2 A7 바로 여기서 가져오겠다는 거죠 이게 바로 x 로컵 함수고정 있잖아요 근데 이상형 그러면은 뭐예요 골프로 바뀐다는 거죠 그리고 또 하나 지금 여기는 이제 생략을 했는데 만약에 찾고자 하는 성명이 없어요 그럼 여기다가 없음이라고 입력을 하고 못 찾았을 때 텍스트 그다음에 콤마 정확히 일치 0을 생각했거든요 정확히 일치하 그니까 이름 정확히 일치하는 걸 가져와라 옵션이죠 그다음에 -1을 주게 되면 정확히 일치하거나 다음으로 작은 항목 1 정확히 일치하거나 다음으로 큰 항목 그다음에 2 와일드카드 문자 일치 그래서 0 기본 값이고 그다음에 찾는 방법 1 오름차순 검색 처음부터 찾는 거죠 얘가 기본 값이에요 요렇게 확인 해서 생략 가능하다라는 거고 엔터를 치겠습니다 그러면 만약에 지금 성명에 없는 하하하 엔터치면 어떻게 돼요 없습니다라고 뜨잖아요 바로 이게 루요 또 갈까요 요번에는 번하고 가명을 같이 가지고 올 거예요 그러면 어떻게 하느냐 간단해요 x 로컵 a 10 그래놓고 성명 d 2에서에 7 여기서 찾아요 그래놓고 중요한 건 뭐냐면 아까는 동아리 A2 a 7이 있잖아요 근데 A2 c 7 A2 c 7 이렇게 범위를 확대해 버리면 번하고 학가이 같이 옵니다 바로 그 얘기예요 바로 학번 아고 학과명이 같이 온다라는 거죠 브루커 브루커 같은 경우는이 가요 버티컬의 약자예요 버티컬은 무슨 뜻이에요 열이란 뜻이거든요 그래서 얘는 범위에 범위에 중요한 거는 바로 얘요 첫 번째 열에서 첫 번째 열에서 찾을 값을 검색합니다 컵은 첫 번째 열 걸 명심하셔야 돼요 범위에서 첫 번째 열에서 찾을 값을 찾는다 이거 정말 중요한 거예요 브르 컵은 그래서 지정한 열 번호와 같은 행에 있는 데이터를 가져온다는 거 그래서 V 로컵는 버티컬 열을 뜻한다는 거 그래서이 찾을 값을 범위에서 특히 첫 번째 열에서 찾는다는 거 찾아 가지고 가져오는 거는 열 번호를 갖다가 이렇게 줘야 돼요 그리고 옵션 옵션 같은 경우 는 0을 주게 되면 정확하게 일치 않은 값을 갖고 온다라는 거예요 0이라는 거는 패스하고 같은 거고 0을 줘도 되고 패스라고 입력을 해도 되고 정확하게 일치 그다음 2을 주게 되면 추해요 tru 얘는 아래로 근사치 정확하게 일치하는 값을 가져와야 된다 그때는 0을 주면 되는 거예요 그래서 찾을 값 그다음에 범위 범위 첫 열에서 찾는다는 거 정말 중요해요 그다음에 열번호를 준다라는 거 직접 보도록 하겠습니다 지금 지역명 대전 요금이 15,000 원이에요 근데 여기 보면 지금 지역명이 서울 천안 대전 양산 부산 지금 입력되어 있는 형태가 얘가 지금 열 개념이 아아 이렇게 열 개념이자아요 이렇게 열 개념으로 데이터가 입력되어 있을 때 찾고자 한다 그럼 브루에 버티컬 여기서 첫 번째 열에서 찾습니다 첫 번째 열요 범위에서 첫 번째 열에서 찾는 거예요 이게 정말 중요한 거예요이 열에서 찾습니다 이게 브루 컵이에요 그러면 대전 그러 15,000원이 오는 거고 그다음에 여기다가 서울 그러면은 5,000원 렇게 바뀌는 예지요 브이컵 브이컵 브 버티컬 열에서 찾아라 여기서 첫 번째 열에서 찾아라 룩업 의원을 의원은 뭐예요 서울을 그러 서울을 여기서 찾는 거예요 의원에서 2의 6 D 1에서 이에요 범위 색깔 보시면 돼요 그럼 B 1에 서울이 명 돼 있잖아요 그러면 지역명 서울 아니죠 서울 찾았네요 서울 찾았어요 그리고 어떻게 하라고요 그다음에 뒤에다가 2이 2는 뭘 뜻하는 거냐면 지역명 여기가 1열이 되는 거고 요금이 2열이 되는 거예요 바로 이겁니다 저기서 얘기하는 2는이 2열을 가리키는 거예요 그러니까 서울을 찾았다 찾았어요 그러면 같은 행에 있는 2열의 값을 가져와라 5,000원을 가져와라 그 뜻이에요 그다음에 0은 반드시 서울하고 똑같은 일치 않은 경우 엔터 그러면 서울을 찾아서 서울을 찾았잖아요 같은 행에 있는 2열의 값을 가져와라 거 그다음에 여기다가 이제 대전이라고 입력하면 대전을 찾아서 15,000원을 가져온다는 거죠 이게 바로 브이 루이에 브루커 마찬가지로 여기도 추루 유사일치 방금 전에 말씀드린 대로 1 펠스 정확히 일치 필스는 0을 뜻하는 거예요 됐네요 그다음 또 갈까요 컵 요번에는 뭐냐면 평균 평가요 그래놓고이 평균이 0 60 70 80 90 올라가는 순서대로 정렬이 되어 있고 그다음에 90 이상일 때는 a 학점을 얘기하는 거죠 평가 80 이상이면 B 70 C 60 이상이면 D 그렇지 않으면 전부 다 F 그래요 어떻게 주냐 마찬가지야 얘도 브루 컵 브루 컵 얘도 마찬가지로 첫 번째 열에서 점수를 찾는 거예요 그래놓고 22를 여기서 찾습니다 그다음에 절대 참조로 준 이유는 체우기 핸들 드래그해서 을 복사할 거기 때문에 절대 참조 준 것뿐이고 그다음에 2열에 있는 값을 가져와요 그래놓고 마지막에 뭐예요 1 줬잖아요 1 추루 아래로 근 사치 값을 가져오는 경우 이런 예제에서 정확히 일치 않는 값이 아닌 요렇게 숫자 같은 경우 아래로 유사한 값 얘들 평균이 50이 아아 50이면 지금 정확히 일치하는 값을 갖는게 아니고 근사치 그러니까 50은 평균 60이 아니잖아요 그니까요 밑에 있는 거 아래로 근사치 0에 해당되는 F 갖고 와라 이게 바로에 2을 준 옵션을 2를 준 2에요 이렇게 숫자적인 개념에서 아래로 근사치를 갖고 와야 된다 그러면 옵션을 갖다 추로 2을 주면 된다라는 거죠 요번에는 h 루크인데요 여기서 얘기하는 h n 리젠 행을 뜻하는 거예요 그러니까 얘는 행에서 검색하라 이런 뜻입니다 범위의 첫 번째 행위에서 바로 이거예요 범위의 첫 번째 행위에서 찾을 값을 검색하라는 거고 지정한 행 번호에 같은 열에 있는 데이터를 가져와라 그래서 h 루커 찾을 값 범위 그다음에 얘는 행번호가 나와요 버티컬 행위기 때문에 행번호 그다음에 마찬가지로 옵션 0 이면은 정확하게 일치하는 값 1이면 유사일치 생략 가능하고 찾을 값 그다음에 범위 첫 행에서 찾는다는 거 조심하셔야 되고 직접 보겠습니다 지금 h 루은 지금요 지역명 요금이 요렇게 행 개념으로 생겼잖아요 바로 이겁니다 요렇게 행 개념으로 생긴 경우는 h 이다라는 거고 그다음에 이미 우리가 배웠지만 요렇게 개념으로 되 있는 거는 브루 컵이에요 요걸 잘 보셔야 됩니다 그래서 시험 문제에서도 요렇게 생긴 거는 브루 컵이고 그다음에 요렇게 생긴 거는 a 7이에요 열 개념 행 개념을 정확하게 하셔야 돼요 볼까요 h 루커 원을 B1 부산을 어디서 찾아라 a 사에서 fo a 사에서 F요 범위에서 찾는 거예요 그 대신 첫 번째 행위에서 찾습니다 h 컵은 V를 컵과요 차이점이에요 마찬가지로 그래놓고이 2는 뭘 뜻하는 거예요 여기서는 행 번호요 그러니까 지금 지역 지역명 여기가 행이 되는 거고 요금이 이행이 되는 거예요 여기서는 이제이 행입니다요 2가 바로 행을 뜻하는 거고 마찬가지로 0은 뭘 뜻한다 정확히 일치하는 값을 찾아라는 뜻이죠 그리고 엔터를 치게 되면 부산을 찾아서 부산을 찾아요 부산을 찾았잖아요 그래놓고 같은 열에 있는 이행의 값 3만 원을 가져온다 그거요 여기도 마찬가지로 대전 그러면 대전을 찾은 다음에 15,000원을 가져오는 경우고 또 갈까요 지금 얘 같은 경우도 보세요 평가가 어떻게 생겼어요 행 개념으로 생겼잖아요 그러니까 a 컵이에요 컵과 쓰는 방법은 똑같은 거고 h 로컵 22 평균 점수 그다음에 평가 달러 a 달러 9 달러 F 달러 10 체우기 들 드래그 할 거기 때문에요 범위를 절대 참조로 줘서 변하지 않게끔 한 거고 2는 뭐예요 이행 마찬가지로 그래놓고에 뭘 생각했어요 옵션을 생각했잖아 루 유사일치 TR라고 줘도 되고 1을 줘도 되고 생략해도 되고 여기서는 생략을 했다라는 거죠 이게 바로 h 루커이에요 chos 추수 함수 사전적인 의미가 고르다 선택하다 뜻이 있거든요 그래서 얘는 뭐냐면 주즈요 찾을 인수의 위치 번호이 번호가 1이면 인수 1로 가고요 2면 인수 2로 가고 간단해요 그래서 얘가 1이면 얘를 선택하라는 거고 이면은 인수 2를 선택하라 이런 식으로 주수 굉장히 간단한 함수예제 인수의 위치 번호에 따라 값이 선택된다 그 값은 이제 인수 1 인수 2 요게 되는 거고 그래 말씀드린 대로 인수 이지가 1이면 이제 인수 1이고 2가 되면 인수 2를 찾는다는 거 그다음에 찾을 인수의 위치 번호가 1보다 작거나 목록의 최대값보다 크게 되면 밸류에 떨어져 그러니까 숫자를 잘못 쓴 거죠 # 밸류 느낌표 류가 떨어지는 거고 그다음에 찾는 인수 번호를 갖다가 정수로 줘야 되는데 분수로 줬어요 소유 점으로 줬어요 그럼 소유 이하를 자기가 잘라 가지고 정수로 변환해 줍니다 얘도 직접 보도록 하겠습니다 지금 추주 함수 여기 보면요 우리가 주민 등록 번호 여덟 번째 자리가 여덟 번째 자리가 하나 둘 셋 넷 다여요대 까지 일곱 그다음에 여덟 번째 자리 여기요 여기가 1 남자 2 여자 2000년도부터 태어난 경우는요 3이 남자고 4가 여자예요 그러면 주민 등록번호 여덟 번째 짜리의 규칙이라는 건 뭐예요 1이면 남자 2면 여자 3이면 남자 사면 여자잖아요 바로 그겁니다 그 예를 주저 함수를 써서 사용하 되면 어마어마한 이런 좋은 수식이 돼요 추수는 추수 그다음에 주민 번호 여덟 번째 짜리에서 하나를 가져오려면 미드 함수를 써야 돼요 부득이하게 추수하고 미드로 섞어야 됩니다 미드 B2 주민번호 여덟 번째에서 하나를 가져와라 그러면이 번호가 1이면 남자 여기요 2면 여자 3이면 남자 4면 여자 요렇게 가져오는 수식이 바로 추수하고 미드를 섞어 버린 거예요이 추수하고 미드를 섞어서 성별을 구하는이 수식은이 수식은 시험을 떠나서라도 우리가 실생활에서 실무에서 요긴하게 사용될 수 있는 기능이에요 수식입니다 이걸 이문으로 하려고 그러면 굉장히 길어지거나 하지만 추즈 미드 함수로 섞어 버리면 요렇게 간단한 수식이 돼요 이게 바로 추주 함수다 거고 그래서 체우기 핸들을 갖다가 드래그 하게 되면 드래그 하게 되면 1이니까 남자 2 여자 2 여자 1 1 남자 남자 4까 여자 3 남자 구해진다 거죠 그리고 또 하나 지금 여기 3.3이 아아 추즈 추즈 J1이 3.3에 그러면요 자기가 알아서 3으로서 1 2 3 3을 가져온다는 거죠 요렇게 그다음에 또 하나 지금 추즈 3을 줬는데 하나 둘 세 개를 줘야 되는데 두 개만 줬어요 밸류 에다가 떨어진다는 거죠네 인덱스 함수 인덱스 함수는 범위에서이 범위에서 행과 열에 해당되는 값을 가져오는 함수예제 행번호 열 번호가 교차하는 곳에 값을 가져온다는 거고 범위에서 행위나 열이 하나만 있을 때 행 번호나 열번호 인수는 생략할 수 있다라는 거고 인덱스 함수도 직접 보도록 하겠습니다 지금 인덱스 A1 C2 A1 C2 여기 이제 색깔 나는 부분을 보시면 되는 거죠 여기가 범위가 되는 거고 마 2가 뭘 뜻한다 2행 2열을 뜻하는 거예요 이행 2열 그럼 여기가 1행 그죠 여기가 이행이 되는 거고 그다음에 1열 2열이 되는 거잖아요 그죠 그러면은 이행 2열에 해당되는 값은 뭐예요 고아요이 망고가 온다 그 얘기예요 엔터를 치게 되면 망고가 온다라는 거고 번 또 바꿀까요 요번에 일행 1렬 가겠습니다 일행 일렬 그럼 뭐가 와요 사과가 오는 거죠 일행에서 1이니까 과 이게 바로 인덱스 함수고정 보면 인덱스 인덱스 f 8에서 g 10 색깔 나는 부분 보시면 돼요 f 8에서 g 10 첫 번째 있는 영역이고 그다음에 마 F 12에서 g13요 바로요 부분이잖아요 색깔나는 부분 요렇게 두 개 영역을 줍니다 영역을 주고 난 다음에 괄로 닫고 2 2 2 이러면 요 앞에 있는 2마 2는 이행 2열을 뜻해요 이행 이열 행열 그다음에 맨 마지막에 있는이 2는 영역이에요 얘가 첫 번째 영역이고 얘가 두 번째 영역이 돼요 그러니까 여기 2는요 두 번째 영역에서 2행 이열 그러니까 1행 이행 그죠 그래놓고 2열이 1열 2열 그럼 뭐예요 만 원이 온다라는 거죠 바로 그거예요 엔터를 치게 되면 요렇게 만 원이 온다 그래서 영역형 인덱스 렇게 두 개를 줘 버리고 나서 몇행 몇열 그다음에 마지막에는 영역을 뜻한다는 거 그럼 여기로 영역을 1로 바꾸게 되면 여기서 이행 2열 1열 2열 1열 2열요 값을 가져온다는 거죠 바로 요렇게요 셋 함수 가겠습니다 오프라는 거는 이제 떨어져 있는 거리를 뜻하는 거고요 떨어진 거리에서의 세트 집합이다 개념이에요 그래서 지정한 셀 또는 지정한 범위에서 지정한 행가 열 수만큼 떨어진 곳에 있는 떨어진 곳에 있는 이게 바로 이제 오프 의미하는 거고 특정 높이와 너비의 범위에 입력된 데이터를 갖다가 표시해 준다 그리고 행의 개수가 양수이면 아래로 가요 그리고 열의 개수가 양수이면 오른쪽으로 갑니다 자 지간 행위든 열이는 양수인 경우 아래 오른쪽이에요 그다음에 음수인 경우에는 행이 음수이면 위로 올라갑니다 그리고 열이 음수이면 반대 방향 왼쪽으로 가요 그래서 오프셋 셀 또는 셀 범위 그다음에 행수 열수 그다음에 행높이 열높이 직접 보도록 하겠습니다 지금 a 1에서부터 c 5까지 데이터가 입력이 되어 있는데요는 오프셋 오프셋 떨어진 거리만큼의 어떤 집합 a 1을 기준으로요 a 1을 기준으로 a 1을 기준으로 3마 2 몰타는 거예요 3행 2열이 3행 2열 그럼 얘를 기준으로 3행 내려옵니다 1행 2행 3행 내려왔어요 그래놓고 2열 2열이 얘를 기준으로 여기가 1열 2열이 되겠죠 그럼 뭐예요 체육인이 온다라는 거죠 엔터를 치게 되면 체육인이 온다라는 거고 그다음에 요번에는요 오프셋 오프셋 A1 기준으로요 A1 기준으로 해서 A1 기준으로 해서 0마 0 안 움직여요 0행 렬 여기서 안 움직입니다 그래놓고 3 3열에 그러면 얘를 기준으로 안 움직여요 1행 2행 3행 그죠 그다음에 열이니 1열 2열 3열 그러면 번호 성명 별명 서터 해서 번호 1 2 성명 홍 영어 지의환 별명 홍 타시 탐험왕 얘가 그대로 온다라는 거죠 그럼 엔터 치게 되면 방문전에 말씀드린 대로 번호 1 2 성명 홍영호 지의환 별명 시 타왕 렇게 온다라는 거죠 이게 바로 오프셋 함수예제 의미가 무슨 뜻이 있어요 일치하다는 뜻이 있잖아요 그래서 원하는 값의 위치 상대 위치를 갖다가 값을 구하고자 할 때 사용하는 거고 그래서 매치 검색값 범위이 범위에서이 검색 값의 위치를 찾아오는 거예요 그다음에 옵션 옵션 같은 경우는 주로 0을 많이 써요 검색 값과 같은 첫 번째 값 을 찾는다는 거고 1을 주게 되면은 검색 값보다 작거나 같은 값 중에서 최댓값 얘는 오름차순 정렬돼 있어야 되고 -1 같은 경우는 크거나 같은 값 중에서 최소 값인데 내림차순으로 정렬돼 있어야 돼 그래서 일반적으로 0 옵션을 많이 씁니다 얘도 직접 보도록 하겠습니다 지금 보면은 매치 매치 2의원 이의원의 뭐예요 이에 원이라는 거는 고객명 김선 21에는 이제 김선이 고객명이 들어가 있고 그다음 A2 ES ao요 색깔 하는 부분 보시면 돼요 고객명이 들어 있는 A2 A6 첫 번째로 위치 그러면 지금 한 엑셀 1 김선 2이 매치 함수의 결과는 2가 되는 거죠 결론적으로 고객명 김선이 아는 고객 명이요 고객 명이 들어가 있는 범위에서 몇 번째 위치나 한 엑셀 1 김선 2 2가 되는 거예요 매치 함수는 요렇게 그냥 위치값은 가져옵니다 그런데 그 위치값을 가지고 뭘 하느냐 누적 포인트를 가져오는 수식을 인덱스라고 합쳐서 응용할 수 있는 경우예요 인덱스 A2 B요요 범위 A2 B 요요 색깔나는 부분 보시면 돼요 여기서 그래놓고 우리가 이미 인덱스 배웠을 때 여기는 뭐가 되는 거예요 굉장히 중요한 부분이에요 여기가 행이 되는 거잖아요 인덱스 함수에 의해서 인덱스 함수의 규칙에 의해서 여기는 행이 오는 거죠 행 그 방금 전에 김선 고객의요 결과가 뭐였어 2였던아요 그러면 2마 2 뭐가 돼요 인덱스 이행 2열 여기는 왜 2열이 누적 포인트가 들어 있는이 부분이 2열이아요 그러니까 이행 김선 고객의 이행 그다음에 2열 2열의 값 40 그러니까 김성 고객의 누조 포인트는 40이다 거죠 그래서 결과는 40이 온다라는 거죠 이게 매치 함수예제 x 매치 가겠습니다이 x 매치도 마찬가지로 상대 위치를 반환해 주는데요 x 매치는 매치 함수와 기본 기능은 같아요 단 매치 같은 경우는 와일드 카드 문자를 갖다 사용할 수 있더라는 거 그거 하나만 좀 조심하시 고 얘도 직접 한번 보도록 하겠습니다 지금 제품명 단가가 입력이 되어 있고요 지금 여기서 x 매치 x 매치 21 21은 이제 여기 제품명이 입력이 되는 거잖아요 모니터가 모니터가 A2 a A2 ao 모니터가 상대 위치가 몇 번째예요 1 2 3 그러니까이 x 매치의 결과는 3이 된다라는 거죠 됐네요 엔터 그래놓고 얘도 마찬가지로 인덱스 함수고정 였으니까 3 그래고 2열 1열 2열 그러니까 뭐예요 모니터의 단가 45만 원이 결과가 된다라는 거죠 바로 이거예요 그래서 여기서 데이터를 갖다 만약에 우리가 마이크로 바꿨다 그럼 마이크로 바꾸게 되면 마이크의 위치 1 2 3 4가 되는 거고 그죠 그럼 4행 2열이 된다 그래서 23만 원이 결과가 된다라는 거죠네 매치 마치도록 하겠습니다