[컴활2급 올인원] 11 찾기참조 함수

첫번째 예제는 표 1에서 등급이 입력된 C3부터 C10 영역에서 골드 중에서 골드에 해당한 등급이 골드에 해당한 구입 총액이 가장 높은 총액에 고객명을 21셀에 표시하시오라고 되어 있습니다. 등급이 골드에 해당한 데이터를 찾고요. 그 중에 골드 중에서 구입 총액이 가장 높은 총액이 아마도 142만 5천원인 것 같아요. 그 금액에 해당한 고객명을 찾아서 21셀에 표시합니다. 함수는 인덱스와 매치와 디맥스 함수를 사용하라고 되어 있습니다. 그럼 먼저 디맥스 함수를 통해서 등급에 가서 골드에 해당한 구입 총액의 가장 높은 금액을 구하는 값을 먼저 작성해 보도록 하겠습니다. 커서를 21셀에 갖다 놓으시고요. 디맥스라고 입력하시고요. 함수 삽입 누르셔서요. 데이터베이스는 제목을 포함한 모든 데이터 보통은 함수는 필드명을 거의 포함하지 않는데요. 앞에 있는 디맥스, 데이터베이스, 조건에 만족한 이라는 디맥스의 함수는 필드명을 꼭 포함해서 범위를 지정합니다. 다음 필드는 우리는 실제 가져올 데이터는 구입 총액을 가져오고자 합니다. 다음 조건은 등급이 골드에 해당한 이라고 되어 있습니다. 등급이 골드라고 선택을 하시고요. 그리고 나서 확인 버튼 누르시면 142만 5천원이 구해져 있고요. 이 값에 해당한 데이터에 우리는 고객명을 찾고자 합니다. 실제 가져오실 데이터는 고객명을 가져오는 거고요. 그럼 인덱스라고 입력하시고요. 그 다음, 인덱스는 첫번째 실제 가져올 데이터를 선택하고 신표, 수식을 복사할 건 아니니까 f4 안하셔도 돼요. 물론 하셔도 되고요. 그 다음에 행의 위치는 디맥스 함수를 통해서 구한 값이 매치 함수를 통해서 몇번째 위치합니다 라고 위치하는 행을 구할 건데요. 매치 함수는 디맥스를 통해서 142만 5천원 금액이 신표, 구입 총액 영역에 가서 정확하게 일치하는 값이 어디 있다라고 찾고자 합니다 라고 0이라고 입력하고 그 다음 매치에 대한 가로를 닫고요. 인덱스에 대한 가로를 닫고요. 엔터 눌러주시면 네, 디맥스를 통해서 최대 값 구했고요. 매치 함수를 통해서 몇번째 위치한다라고 구했고요. 그 값의 위치 값을 고객명에 가서 찾는 작업을 하면 세번째 해당한 고객명은 이 수학입니다 라고 표시할 수가 있습니다. 다음은 두번째 예제를 보도록 하겠는데요. 표 2에서 수행평가 1번, 수행평가 2번, 수행평가 3번에 평균이 1이면, 아 평균 값을 구해서 1이면 f 이면 c 이렇게 쓸 거에요. 일단 평균 값을 구하겠습니다. 등호 입력하시고요. 에버레이즈라고 함수명 입력하시고요. 수행평가 1번부터 3번까지 가로 닫고 엔터. 평균 값이 구해졌고요. 아래까지 수식을 복사하시면 값이 구해집니다. 이 값의 정수로 표시하고자 합니다. int란 함수를 이용하면 현재 구하신 값의 소수 이하 자릿수 생략해서 정수로 표시된 걸 확인할 수가 있습니다. 이제는 1이면 f, 2면 c, 그 다음에 값을 나열하도록 하겠는데요. 커설을 등호 다음에 갖다 놓으시고요. 추주함수 이용하셔서 추주 안쪽에 커설을 둘까요? 함수 삽입 물론 나열을 직접 하셔도 되는데 우리는 큰 다운벨 처리하는 게 살짝 번거로울 수 있으니 추주함수 안쪽에 커설을 두고요. 함수 삽입에 가셔서 조금 전에 구해놓은 값이 첫번째라면 f 라고 하겠습니다. 그 다음에 평균 값이 1이면 f, 평균 값이 2라면 c 라고 하겠습니다. 평균 값이 3이면 b 라고 하겠습니다. 평균 값이 4면 a 라고 하겠습니다. 라고 값을 나열해 주시고요. 확인 버튼 아래로 수식을 복사하시면 평균 값에 따라서 비골 안에 값이 표시된 걸 확인할 수가 있습니다. 다음은 표 3에서요. 표 3에서 학점 b15 부터 b21 를 이용하여 순위를 구한 후에 순위에 따른 할인율을 할인율 표에서 찾아서 납부액을 계산하시오. 납부액을 계산하시는 방법은 등록금 곱하기 1 빼기 할인율 이라고 되어 있습니다. 일단 순위를 좀 구할게요. 등호 입력 하시고요. 랭크.eq 를 선택하시고요. 바로 왼쪽에 있는 등록금을 가지고요. 직접 하시는게 좀 어려울 수 있을 것 같아요. 그럼 함수 삽입 누르시면 되요. 바로 왼쪽에 있는 값을 가지고 그 값을 비교할 대상이 필요한데 바로 왼쪽 본인 등록금부터 전체 데이터들 중에서 수식을 복사할 건데요. 지금 선택한 영역은 수식을 복사해도 동일하게 비교가 필요합니다. f선 눌러서 절대 참조하시고요. 순위를 구할 때 큰 값을 1등으로 처리할 땐 내림차 순위라고 하는데요. 0이나 생략하시면 되요. 확인 버튼 누르시면 순위를 구할 수 있구요. 1등은 할인율이 100%, 2등은 할인율이 70%, 3등은 4등이 안되니까 70%, 4등은 50%, 4등부터 5등까지는 50%, 6등은 0% 라고 할인율을 구하고자 합니다. 참조하는 표가 아래쪽에 수평으로 작성이 되어 있습니다. h류컵 이란 함수를 이용하실 거구요. h류컵 안쪽에 커서를 두시고 함수 삽입에 가셔서 랭크 함수를 통해서 순위를 구해 놓으셨는데요. 어느 표에 가서 참조할 거냐면 할인율 표에 가서 참조하겠습니다. 단, 할인율 표는 수식을 복사하더라도 항상 같은 영역을 참조할 겁니다. f선 눌러서 절대 참조. 첫번째 행에서 등수를 찾구요. 두번째 행에서 할인율을 가져오고자 합니다. 그 다음 정확하게 일치하는 값은 아닙니다. 그럴 때는 비슷하게 일치하는 값을 찾을 때는 추루 또는 생략하시면 되구요. 추루라고 쓰셔도 되고 추루 대신 1이라고 쓰셔도 되구요. 확인 버튼 생략해서 확인 버튼 아래까지 수식을 복사하시면 다 일만 나오는데요. 물론 지금 일만 나오지만요. 서식이 회계 형식이라서 그렇습니다. 회계라고 지금 확인을 하시구요. 나중에 이제 물론 바꿔 주셔야 되기 때문에 회계였어요. 백분율 스타일로 바꿔보시면 이렇게 납부액에 대한 납부액의 할인율을 찾아서 표시된 걸 확인할 수가 있습니다. 셀 서식 때문에 그런 것 같구요. 그럼 일단 저는 셀 서식 바꾼 거는 실행 취소 한번 할게요. 이제 납부액에 대한 계산식을 수정하도록 하겠는데요. 등록금 바로 왼쪽에를 클릭해야 되는데 클릭이 안 돼요. 아래쪽을 클릭하셔서 방향키 이용하셔가구요. C15셀 선택하고 곱하기 가로 열고 1 빼기 할인율은 조금 전에 구해 놨으니까 맨 끝에 가서 맨 끝에 가서 가로 닫구요. 엔터 눌러서 아래까지 수식을 복사하시면 문제에 나와 있는 납부액을 구하는 계산식을 이용해서 등록금 곱하기 1 빼기 할인율로 계산한 결과를 납부액에 표시할 수가 있습니다. 다음은 네번째 예제를 볼게요. 표 4에서요 학번이 있어요. 학번이 G15부터 G21 있구요. 오른쪽에 학과표가 있습니다. 학과표를 이용하여 학과를 I15부터 I21 영역에다가 표시 예와 같이 표시 하시요 라고 되어 있습니다. 학번이 2 4 1 1 0 1 2 라고 되어 있으면 앞에 24라는 것은 24학번 그 다음에 앞에 일단 두 자리를 추출해서 학번이라고 붙이실 거구요. 그 다음 세번째와 네번째 글자를 이용해서요 오른쪽 학과표에 가서 찾아가지고 학과를 표시하도록 하겠습니다. 왼쪽에 두 글자를 추출하는 건 어렵지 않으니까 나중에 수정하는 걸로 하구요. 등록 갖다 놓구요. 미드함수부터 한번 해볼까요? 미드함수 fx 함수 삽입에 가서 학번에 가서요 세번째부터 시작해서 세번째 네번째 니까 두 글자를 추출하겠습니다. 확인 버튼 누르시면 학번에 세번째부터 시작해서 두 글자를 추출한 결과를 표시할 수 있구요. 그 다음 그 값을 V6컵 함수를 이용하셔서 V6컵 오른쪽에 학과표에 가서 찾아오겠습니다. V6컵 안쪽에 커서를 두시고 함수 삽입에 가셔서 미드함수를 통해서 두 글자 학과 번호를 찾아왔어요. 음 이제는 테이블 어레이 영역에는 참조할 표를 선택하시고 수식을 복사하더라도 항상 같은 자리니까 f4 눌러서 절대참조. 그 다음 첫번째 열에서 학과 번호를 찾고 두번째 열에서 학과를 찾아올 거구요. 정확한 학과에 해당한 데이터를 찾고자 합니다. 0 이라고 입력하고 확인. 아래까지 수식을 복사해 주시면 되겠습니다. 이제는 학과는 V6컵과 미드함수를 통해서 구해놨구요. 앞에다가 학범만 수정하도록 하겠습니다. 등호 다음에 커서를 갖다 놓구요. 레프트 함수를 이용하셔가구요. 학번에 가서 신표 두 글자를 추출한 값을 연결하겠습니다. n% 연산자 학번이라고 입력하고 한 칸의 스페이스를 떼시구요. 다시 연결하겠습니다 라고 작성합니다. 아까 구하셨던 학과에 대한 정보를 연결해 주시면 되겠죠. 그리고 나서 엔터 한번 눌러보시면 학번에 왼쪽에 두글자 추출해서 학번이라고 붙였구요. 그 다음 아까 V6컵을 통해서 구하셨던 학과 정보가 연결되어서 표시된 걸 확인할 수가 있습니다. 찾기참조 함수, 인덱스, 매치, 추주, H루컵, V루컵 함수들을 살펴 보셨구요. 결과 시트 클릭하셔서 실습한 내용이 맞게 되어 있나? 어 근데 결과가 좀 달라요. 그럼 제가 어떤 부분에서 잘못 작성한 게 있는 것 같습니다. 다시 한번 확인하겠습니다. 3번 문제를 잠깐 보도록 하겠습니다. 3번에 어 순위를 구하고자 합니다. 순위를, 순위를 구해서, 3번이죠? 순위를 구한 학점을 이용해서 순위를 구했구요. 그 순위를 가지고 아래쪽 표에 가서 첫번째 행에서 찾고 두번째 행에서 데이터를 가져왔습니다. 결과 시트가 뭐가 다를까 한번 봐볼게요. 아 이제 알겠습니다. 학점을 이용해서 순위를 구해야 되는데 제가 등록금을 가지고 순위를 구했네요. 어우 큰일날 뻔했습니다. 잘못된, 잘못된 수식이었어요. 순위를 구하는데 학점을 가지고 순위를 구해야 되는데요. 제가 문제를 잘 이해를 못하고 등록금을 가지고 순위를 구했습니다. 순위를 다시 한번 이 부분을 수정을 하겠습니다. 아 랭크 함수가 있는 곳에 커설 두시고 함수 삽입을 누르신 다음에 어 학점이 있는 B15가 B15부터 B21 영역 안에서 이어 비교할 대상은 F4 눌러서 절대 참조하셔야 되구요. 그리고 나서 확인 버튼 누르신 다음 아래까지 수식을 복사하시면 이제 정상적인 값을 구하실 수가 있습니다. 아 문제를 잘 읽었어야 되는데 제가 실수를 한 것 같습니다. 학점을 이용해서 순위 구하고요. 그 다음 그 순위를 가지고 등수표에 가서 첫번째 행에서 등수를 찾고 할인율의 값을 가져오는 작업을 했어야 되는데 등록금을 가지고 순위를 구한 실수를 해가지고 결과가 좀 달랐습니다. 찾기 참조 함수 예제를 살펴 보셨습니다.