[컴활1급실기] 08 찾기참조 함수

찾기 참조 함수를 같이 실습하도록 하겠는데요 첫 번째 문제를 보도록 하겠습니다 표 1에서 결제 방법과 그다음 할부 기간을 어 이용해서 표 2의 할부 기간별 수수료를 어 참조해서 우리는 f3 f13 영역에 지불 수수료를 계산하라고 되어 있습니다 그래서 표 1에 결제 방법과 그다음 할부 기간이 데이터를 표 2의 영역을 참조해서 어 수수료율을 찾아다가 우리는 F 열에 지불 수수료를 계산하실 건데요 지불 수수료는 매출액 곱하기 아래쪽 표 위에서 찾아오신 값을 곱해 가지고음 결과로 표시하라고 되어 있습니다 일단 주어진 함수는 h 루 컵과 그다음 매치 함수가 문제 제시가 되어 있습니다 h 루커 할부 기간의 데이터를 첫 탱에 가서음 3개월 6개월 12개월 해당한 데이터의 할부 기간을 찾아서 첫 번째 행에서 데이터를 찾고요 아래쪽에 세 번째나네 번째 행에 가서 한국 카드 또는 한국 카드가 아닌 나머지 카드에 대해서는 기타 카드를 참조해서 어 수수료율을 찾아다가 표시하고자 합니다음 데이터가 참조하는 표 가 결제 방법인 한국 카드 나라 카드 대양 카드 세 개가 다 존재한다고 하면 매치 함수를 통해서 쉽게 어 영역을 참조해서 값을 찾아올 수 있는데 현재 예제에서는 한국 카드는 첫 번째 데이터를 참조할 거고 나머지 대항 카드와 나라 카드는 아래쪽에 기타 카드를 참조할 겁니다 그렇다면 잠시 결제 방법을 입력을 좀 해 볼게요 현재 결제 방법은 한국 카드 그다음에 대한 카드 그다음에 나라 카드라고음 제시가 되어 있습니다 한국 카드는 우리 한국 카드에서 어 데이터를 참조할 건데 대양 카드와 나라 카드는 기타 카드에서 데이터를 참조할 겁니다 그러면 어 매체 함수를 통해서 일단 참조하는 행에 어 참조하는 데이터의 행의 위치값을 좀 구하도록 할게요 등호 입력하시고 매치라고 입력 하시고요 그다음 어 결제 방법을 신표 아래쪽 영역에 가서 표 2에서 데이터를 참조하겠습니다 F4 눌러서 절대 참조 정확하게 일치하는 데이터는 아니에요 한국 카드는 정확하게 일치하지만 나라 카드와 대양 카드는 정확하게 일치하지 않아요 그러면 한국 카드와 기타 카드 그럼 히읗이 먼저 와 있으니까 아 내림차 순이구라고 할 때는 보다 큼을 참조합니다 그래서 - 1이라고 입력하고 괄로 닫고 엔터 누르시면 어 나라 카드가 첫 번째 한국 카드를 참조하게 되어 있네요이 영역 두 개 영역 중에 숫자 1이라고 구해집니다 어 나라 카드는 두 번째에서 구해야 되는데 어 그러면 어 아까 작성하신이 -1이 아니라 어 1이지 않나요네 물론 1 해도 결과는 다를 거예요 어이 1이라고 했더니음 정확한 데이터가 없는데요라고 우류가 발생 합니다 그렇다면 지금과 같은 방법으로 수식을 작성하지 않고요 현재 한국 카드는 첫 번째 1이라고 나올 거고요 대항 카드와 나라 카드는 숫자 2라는 값이 반환이 되어야 합니다 그렇다면 집합 기호를 이용해서 우리는 한국 카드는 첫 번째 그다음 큰따옴표 닫고 두 번째 나 대항 카드와 나라 카드는 기타 카드를 참조하겠습니다 기타카드 를 두 번째 입력하시고 집합 기호를 닫습니다 한국 카드와 기타 카드는 어 히읗이 먼저이기 때문에 내림차순 있니다 그냥 매치 함수만 사용할 때는 보다 작음을 참조합니다 할 때 오름차순 보다 큼을 참조합니다라고 하면 내림차순 있니다 - 1이라고 입력하고 엔터 누르시면 이제 아래로 수식을 복사했을 때 어 나라 카드는 1이라고 구해지고 그다음에 한국카드 1이라고 구해 지네요 그렇다면 또 마찬가지로 지금 구하신 값이음 잘못됐다는 얘기죠 그냥 기타 카드로 넣으시면 안 되고 여기에 대한 기타 카드 대신에 우리는 대한 카드라고 입력하시고 그리고 엔터 누르시면 2라는 값이 구해져 아까 한국 카드 대양 카드 나라 카드라고 있잖아요 한국 카드 대양 카드 나라 카드 그래서 한국 카드는 숫자 1이 나오면 되고요 대항 카드는 숫자 2가 나오면 돼요 기타 카드를 참조할 거라서 나라 카드도 숫자 2가 나와야 돼요 그래서 어 대항 카드라고 하면 디귿이 니보다 더 위에 있어요 그렇죠 기억 니은 디귿 리을이 그 내림차순 하게 되면 디귿이 먼저잖아요 그래서 나라 카드는 보다 큼을 참조하기 때문에 대한 카드를 참조해서 값을 참조하겠습니다라고 2라고 구해진 걸 확인할 수 있습니다 아래까지 수식을 복사하시면 이제 한국 카드는 어 아까 나이랑 갑의 첫 번째를 참조할 거고요 그다음 나라 카드는 한국 카드가 아니니까음 대항 카드보다 아래 있기 때문에 보다 큼을 참조해서 대항 카드를 참조하겠습니다라고 숫자 2가 구해진 겁니다 이제는이 숫자 1과 2는 현재 한국 카드가 숫자 1 기타 카드가 숫자 2입니다 저희는 할부 기간을 가서 첫 번째 행에서 데이터를 찾고요 한국 카드는 세 번째 행에 있습니다 조금 전에 구하셨던 데이터 뒤에다가 더하기 2를 하셔야만 어 나라 카드는 기타 카드에서 참조할 거고 한국 카드는 세 번째 행에서 데이터를 참조하겠습니다라고 참조할 행의 번호를 구하셨습니다 이제 행의 번호를 구하셨으면 h 루커 함수를 통해서 우리는 수수료율을 찾아오도록 하겠습니다 h 룩이라고 입력하시고 할부 기간을 클릭할 건데 바로 왼쪽에 걸 클릭할 수가 없다 그러면 어 위에서 클릭 하신 다음에 방향키 이용하셔도 되고요 또는 직접 2 3이라고 입력하셔도 되고요 참조할 영역은 표 2에 가서 데이터를 참조할 건데요이 영역은 f3 눌러서 절대 참조하셔야 수식을 복사하더라도 항상 같은 영역을 참조합니다 그다음 심표 아까 몇 번째 행은 어 매체 함수를 통해서 어렵게 구해 놓으셨나요 맨 끝에 가셔서 가로 닫고 엔터 눌러 주시면 일단 수술이 다 0이 나오는 이유는 현재 회계 형식이 되어 있어서 그런 거고요이 값 뒤에다가 물론 어 확인하고 싶을 때는 100분율 누르시면 어 해당된 나라 카드의 6개월 할분은 4%고 잘 구해진 걸 확인할 수 있어요 다시 회계 형식으로 바꿀게요 아니면 실행 취소하셔도 되고 그다음 구하셨던 값 뒤에다가 곱하기 저희는 매출액을 곱해서 계산하겠습니다라고 매출액을 클릭하고 하고 엔터 눌러 주시면 아래까지 수술 어 복 수식을 복사했을 때 매출액에 할부 기간과 결제 방법에 따른 어 수수료율을 곱한 결과가 표시된 걸 확인할 수가 있습니다 결과 시트 클릭하셔서 여러분이 실습한 내용이 맞게 작성이 되어 있는지 확인해 주시면 되겠습니다 그래서 문제에 미리 서식이 좀 적용이 되어 있어요 그래서 문제마다 선택하시면 현재는 일반 서식이음 그래서 서식이 좀 아까처럼 회계 서식인 셀 서식에 가서 어 만약에 백분율로 바꾸실 거면 미리 적용된 서식을 좀 확인하시고 그리고 나서 서식을 100분율 바꾸신 다음에 어 맞게 값을 찾아왔나 확인하신 다음에 다시 한번 어 원래 문제 제시된 서식으로 바꿔 주시고 소식을 작성해 주셔야 합니다 첫 번째 찾기 참조 예제를 어 작성해 보셨고 다음은 두 번째 문제를 보도록 하겠습니다 두 번 문제는 표 5에서 지역과 전역 면적을 이용해서 표 3에 청약 가능하게 표에 가서 값을 찾아다가 표시하는 겁니다 인덱스와 S 매치 함수를 사용하라고 되어 있는데요 아까 매치 함수를 사용해 보셨어요 매치 함수는 찾을 값 그다음 어디에 가서 또는 직접 집합 기호를 이용해서 값을 열 나열해 주시면 되고요 그다음 어 정확하게 일치하는지 오름차 순인 내림차 순인 이제에 따라서 다음 인수가 달라지는데요 일단 매치 함수부터 한번 구해 보도록 하겠습니다 첫 번째 등호 입력하고 지역의 데이터 서울 부산은 표 3에 가서 행의 위치를 지정할 건데 서울 부산이면 첫 번째 기타 강역시는 두 번째 기타 지역은 3이란 숫자가 구해지면 됩니다 x 매치라는 함수를 이용해서요 x 매치 찾고자 하는 값은 지역의 데이터를 가지고 그다음 표 3에 가서 표 3 영역은 F4 눌러서 절대참조 그다음 어 아까 하고 좀 옵션이 좀 다릅니다 정확하게 일치하는 거를 찾을 때는 0이라고 넣으시면 되고요 만약에 어 정확히 정확히 일치하거나 또는 다음으로 작은 항목을 참조하고자 할 때는 - 1이라고 되어 있어요 이게 오름차 순이고 그다음 어 다음으로 큰 항목을 참조하겠습니다라고 할 때는 내림차 순이에요 아까 쓰셨던 매치하고 x 매치의 세 번째 인수가 조금 다르기 때문에 실습하게 좀 구분 작성해 주시면 되고요 지금처럼 정확하게 일치는 어 매치 함수나 x 매치나 둘 다 0을 입력합니다 괄로 닫고 엔터 누르시면 어 서울 부산은 조금 전에 범위 정한 데이터에 첫 번째 있습니다 기타 강역시는 두 번째입니다 기타 지역은 세 번째입니다라고 행의 위치를 구할 수 있고요 일단 지워 볼게요 다시 한번 x 매치를 이용해서 전역 면적의 데이터를 가지고요 신표 열의 위치를 구하기 위해서 표 3에 가서 B 22부터이 22 영역을 선택한 다음에 F4 눌러서 절대 참조합니다 그다음 심표 현재 데이터는 50부터 136이라고 오름차순이 되어 있습니다 오름차순이 되었을 때는요 정확하게 일치하거나 다음으로 작은 항목을 참조하겠습니다라고 -1을 입력하시고 그리고 나서 닫고 엔터 누르시면 85는 85는 86을 넘지 않아요 보다 작음을 참조할 거라서 50을 참조합니다 그래서 1이라는 값이 구해졌다 수식을 복사해 보시면 108은 어 습니다 안쪽에 커서를 갖다 놓으시고요 이제 인덱스 함수를 통해서 우리가 값을 찾아올 건데요 표 3에 가서 F4 눌러서 절대 참조 현재 범위를 지정한 데이터는 실제 값을 가져올 영역이고 그다음 신표 인덱스에는 첫 번째 범위 두 번째 행의 위치입니다 행의 위치는 아까 하셨던 것처럼 x 매치를 통해서 몇 번째 위치합니다는 값을 구할 건데요 h 3의 값이 3에 가서 서울 부산부터 기타 지역에 F4 눌러서 절대참조 정확하게 치니까 0이라고 입력하시고 신표 열의 위치를 구하기 해서 x 매치 함수 입력하신 다음 전용 면적의 데이터를 가지고 신표 우리는 지역에 가서 어 표 3에 가서 F4 눌러서 절대 참조 오름 차순 되어 있기 때문에 보다 작은 다음으로 작은 항목을 참조하겠습니다라고 -1을 입력하시고 X 매치에 대한 괄호를 닫고 인덱스에 대한 괄호를 닫고 엔터 눌러서 아래까지 수식을 복사해 주시면 되겠습니다 실습하고 나서 결과가 맞게 작성이 되어 있는지 확인해 보시면 될 거 같고요 다음은 세 번째 문제를 보도록 하겠습니다 표 4를 참조하여 표 6의 I 25부터 표 6에 어 표 4를 참조하여 표 4 아 여기 세 번째 문제가 여기네요 표 4를 참조하여 표 6에 I 25부터 i29 영역에 타이틀 명을 구하여 표시하시오라고 되어 있습니다 현재음 DVD 코드는 현재 표 4에 11부터 55라는 숫자 아 DVD 코드에 오른쪽에 두 글자를 추출하실 다음 그 값을 어 표사 아래쪽 영역에 가서 참조할 거고요 그다음 실제 가져올 데이터는 위쪽에 행에 있는 데이터를 가져오고자 합니다 물론 어 x 매치란 x 루이란 함수가 제시가 되어 있다면 어 사용할 수 있는데 현재는 x 루이 제시가 되어 있지 않고 V 컵과 h 루과 루이 제시가 되어 있습니다 V 루은 첫 번째 열이고 h 루은 첫 번째 행입니다 그럼 두 개는 해당되지 않아서 룩업 함수를 사용하실 거고요 오른쪽에 두 글자를 추출하기 해서 라이트 함수를 이용합니다 그럼 커서를 갖다 놓고요 오른쪽에 두 글자를 추출하게 우리는 DVD 코드에 가서 신표 2라고 입력하시고 엔터를 누르시면 라이트 함수는 텍스트 함수입니다 어 추출된 숫자 11 22 33 44이 데이터는 현재 표 4에 갔을 때 형식이 좀 다를 수 있습니다 물론 어 곱하기 2을 하시면 되는데 2을 입력하지 않고 룩업 함수를 통해서 구한 다음에 다시 한번 수정하도록 하겠습니다 업이라고 입력하시고 차값 은 조금 전에 구해 놨고요 그다음 어디에서 찾을 거냐면 표 4에 가서 숫자 영역을 참조하겠습니다 F4 눌러서 절대참조 심표 실제 가져올 결과는 바로 위쪽에 있는 횡의 데이터를 가져오겠습니다 F 4 눌러서 절대참조 괄로 닫고 엔터 누르시면 이렇게 오류가 표시가 됩니다 그 이유는 어 라이트 함수를 통해서 구한 데이터 형식과 표 사에 작성된 데이터 형식이 달라서 정확하게 일치한 데이터가 없는데요라는 오류입니다 이럴 때는 조금 전에 라이트 함수를 통해서 구한 값을 숫자로 바꿔 주기 위해서 밸류 함수를 사용할 수 있는데음 문제 제시가 되어 있지 않으니까 숫자 1을 입력하시면 구하신 값을 곱하기 1 하면 11 * 1 하면 11 하고 동일하고요 그 결과는 숫자로 변환이 됩니다 그래서 숫자 11은 반지 재앙입니다 아래까지 수식을 복사하시면 각각에 해당한 타이틀 명을 찾아다가 표시할 수가 있습니다 세 번째 문제를 살펴 보셨고 다음은네 번째 오른쪽에 커서를 갖다 놓으시고요 Q 3에 클릭하신 다음 문제를 보도록 하겠습니다 토익과 컴퓨터와 전공음 데이터를 이용해서 8에 가서 평가를 찾아올 건데요 토익 점수는 0.3 컴퓨터는 0.2 그다음에 전공은 0.5를 곱한 점수를 가지고 아래쪽 평가표에서 찾아오라고 되어 있습니다 일단 썸 프로덕트 함수를 통해서 값을 곱한 결과에 더한음 토익 곱 0.3 + 컴퓨터 곱하 0.2 + 전공 곱하 0.5 한 결과의 합을 구하고자 합니다 그래서 첫 번째 영역음 토익 전공 2를 선택하고 쉼표 두 번째 곱할 값들을 입력하는데 0.3 0.3 신표 0.2 신표 0.5고 입력하시고 집합 기호를 닫으시고 그리고 나서 가로 닫고 엔터 눌러 주시면 평가에 어 일단 점수가 구해집니다이 점수를 가지고요 저희는 아래쪽에 표에 가셔서 평가를 찾아오고 합니다 x 룩업을 이용해서 값을 찾아오고 하는데요 등호 다음에 x 루비라고 입력하시고 그다음 썸 프로덕트 함수 맨 끝에 가셔서 신표 조금 전에 평가 점수는 표 8에 가서 첫 번째 행에 가서 데이터를 찾 겠습니다라고 범위 정하신 다음에 F4 눌러서 절대 참조 신표 가져오실 데이터는 평가를 가져오겠습니다 선택하고 F4 눌러서 절대참조 그다음 신표 그다음 만약에 발견하지 못했을 때라고 되어 있는데요 저희는이 범위 안에서 값을 찾아오실 거예요 그래서 생략하시고 그다음 0 60 70 이렇게 오름차순으로 되어 있습니다 아까 x 매치도 오름차순 되어 있을 때 보다 작음을 값을 참조할 거라서 -1 입력하신 것처럼 -1을 입력하시고 가로 닫고 엔터 눌러 주시면 70 몇 점이었고 때문에 평가에 C고 표시가 되어 있고요 아래까지 수식을 복사하시면 각각의 평균값을 가지고 평가에 가서 어 값을 찾아다가 표시된 걸 확인할 수가 있습니다 결과 시트 클릭하셔서 실습한 내용이 맞게 작성이 되어 있는지 확인해 보시면 되고요 다시 한번 수식을 확인해 보시면 선 프로덕트 함수를 통해서 각각의 점수를 곱한 값을 더한 점수를 가지고 우리는 표 8에 가서 어 평균값을 찾을 거고요 그다음 결과값을 가져올 거고요 참조하는 표가 오름차순 되어 있기 때문에 우리는음 - 1이라는 옵션을 이용해서 값을 찾아오는 작업을 하셨습니다네 번째 문제까지 살펴 보셨고 다음은 다섯 번째 문제를 보도 하겠습니다 화면에 오른쪽으로 이동하셔서 t 3세를 클릭하시고 표 5에 회원 코드와 표 10을 이용해서 t 3부터 t20 영역에음 직업과 지역을 계산하여 표시하시고 되어 있습니다 그래서 먼저음 잘 직업을 값을 찾아다가 표시하도록 하겠는데요 문제에서음 직업은 회용 코드 왼쪽에 두 자와 표을 이용해서 계산하라고 되어 있습니다음 주어진 표가 수직으로 작성이 되어 있습니다 그래서음 x 루이란 함수가 제시가 되어 있다면 수직이 수평이 상관없이 값을 찾아올 수 있는데 현재 예제에서 x 루이 없습니다 V 루이니 V 룩업 함수를 통해서 값을 찾아오도록 하겠습니다 등호 입력하셔서 왼쪽에 두 글자를 추출하도록 하겠는데요 S 3에 가서 왼쪽에 두 글 자를 추출하고 입력하시고 다음 등호 다음에 커설 갖다 놓고요 브루 컵이라고 입력합니다 브루 컵 브루 컵은 레프트 함수를 통해서 구한 값을 신표 아래쪽 표에 가서 첫 번째 열에서 데이터를 찾고요 두 번째 열에서 값을 가져오겠습니다 F4 눌러서 절대 참조 그다음 두 번째 열 신표 정확하게 일치하는 포스라고 입력하셔도 되고요 숫자 0을 입력하셔도 됩니다 가로 닫고 엔터 누르시면 해당된 어 코드를 가지고요 직업을 찾아다 표시할 수 있고요이 값 뒤에다가 가로 열고 그다음음 지역 어 회원 코드에 뒤에 있는 세 글자를 4로 나눈 나머지를 이용해서 동부 서부 남부 부부라고 표시하고자 합니다 그럼 조금 전에 구하셨던 뒤에다가 % 연산자 이용해서 가로 열고요 그다음 다시 한번 % 연산자 이용해서 어 오른쪽에 세 글자를 세 글자를 나머지를 어 4로 나눈 나머지를 이용해서 값을 구하고자 합니다 주어진 함수는 추주 함수를 사용하라고 되어 있습니다 그래서 추주 함수 입력하시고 나머지를 구하겠습니다 m 함수 입력합니다 오른쪽에 세 글자를 추출하였습니다 라고요 라이트 함수를 통해서 J 3을 주시고 세 글자를 추출한 값을 4로 나눴을 때 4로 나눴을 때 나머지가 나머지가 0이면 근데 0은 어 추주 함수는 0부터 시작하지 않아요 1부터 시작해요 그래서 나머지를 구한 값에다가 더하기 1 하셔야만 1부터 시작할 수 있고요 1이면 동부 그다음 2면 서부 3이면 남부 4면 북부하나센터 그리고 나서 % 연산자 이용하셔서 가로 닫고 엔터 눌러 주시면 어 4로 나눴을 때 나머지가 0은 동부 1이면 서부 2면 남부 3이면 북부하이부시 den 걸 확인할 수가 있습니다 결과 시트 클릭하셔서 작성하신 직업 내용까지 확인해 보시면 되겠습니다 찾기 참조함수 두 번째 시트 예제를 보도록 하겠습니다 커서를 B 3에 갖다 놓으시고요 첫 번째 예제입니다 표 1의 제품 코드와 표 2를 이용하여 상품명을 표시하시고 되어 있습니다 상품명은 제품 코드와 그다음 표 2의 작성된 어 가일 코드와 사이즈에 어 교차하는 곳에음 상품명이 작성이 되어 있습니다 실제 B 188부 어 d21 영역에 참조해서 저희는 상품명을 찾아올 건데 상품 코드가 BM 시작하면 1행에서 사이즈가 L 자면 세 번째 열에서 범위 정한 영역에 행과 열에 교차하는 값을 찾아올 겁니다 인덱스 함수를 통해서 값을 찾아올 건데요 행열의 위치는 매체 함수를 통해서 몇 번째 위치라고 구하시면 됩니다 문제 제시된 함수에 어 제품 코드의 왼쪽에 한 글자라고 레프트 함수가 제시가 되어 있지 않습니다 그러면음 지금 른쪽 쪽에 한 글자는 라이트 함수를 통해서 사이즈는 찾을 수 있는데 가일 코드의 왼쪽에 한 글자는 제품 코드 왼쪽에 하나만 추출하지 않고 제품 코드를 가지고 어 행의 위치 몇 번째 위치한다고 찾 값을 찾아와야 합니다 현재 자행해 작성된 표는 B hny 오름차순이 되어 있습니다 그럼 매체 함수를 통해서 몇 번째 위치하는지를 구하도록 하겠습니다 커서를 B 3에 갖다 놓으시고요 행의 위치부터 한번 구하겠습니다 m이라고 입력하시고 매치 함수 더블 클릭하신 다음 찾고자 하는 값은 바로 왼쪽에 있습니다 방향키 이용하셔도 되고요 a 3에 있는 값을 가지고요 아래쪽에 표 2에 가서 값 몇 번째 위치하지 상대적인 위치값을 구할 건데 a 18부터 a21 영역은 수식을 복사하더라도 항상 같은 영역을 참조할 겁니다 f3 눌러서 절대 참조 그다음 현재 표 2에 작성된 데이터는 오름차순이 되 되어 있습니다 오른 차순이 되어 있을 때는 매치 함수에서 보다 작음에 해당한 데이터를 참조하겠습니다라고 1이라고 입력하시면 되고요 0은 정확하게 일치하는 값의 위치 -1은 어 내림차순으로 값의 위치값을 찾고자 합니다 하실 때 사용하는 값입니다 1이라고 입력하시고 가로 닫고 엔터 누르시면라고 시작하는 제품 코드는 첫 번째입니다 아래로 수식을 한번 복사해 보시면요 어 h 세 번째입니다 h 두 번째입니다은 세 번째입니다라고 상대적인 위치값을 구해 왔습니다 지금과 같은 방법으로 행과 열 열은 매체 함수를 통해서 구할 건데 오른쪽에 한 글자를 추출해서 사이즈에 해당한 영역에서 정확하게 s 있니까 m 있니까 l 있니까라고 어 값 옵션을 넣어 주시면 되겠습니다 커서를 B 3에 갖다 놓으시고요 인덱스라고 입력하시고 저희는음 상품명이 있는 영역에 가서 F4 눌러서 절대 참조 쉼표 행의 위치는 조금 전에 미리 구해 놨고요 마지막에 가서 신표 열의 위치도 매치 함수를 통해서 구할 건데 오른쪽에 한 글자 라이트 함수를 통해서 제품 코드에 오른쪽에 한 글자를 추출한 값을 가지고 어디에서 사이즈에 가서 사이즈 영역은 F4 눌러서 절대 참조하시고요 그리고 정확하게 일치라고 입력하 하신 다음 0이라고 입력하신 다음 매체에 대한 괄호를 닫고 인덱스에 대한 괄호 닫고 엔터 눌러서 아래까지 수 아래까지 수식을 복사해 주시면 행과 열에 교차한 위치에 상품명을 찾아다가 표시할 수가 있습니다 그럼 첫 번째 예제를 살펴 보셨고 다음은 두 번째 문제를 보겠습니다 표기의 제품 상태 맛 포장 상태를 이용하여 만족도를 계산하라고 되어 있는데요 만족도는 제품 상태부터 포장 상태의 값과 표 3에 있는 가중치 값을 어 각각 곱한 다음에 더하라고 되어 있습니다 5 * 0.3 + 3 * 0.5 + 2 * 0.2를 할 겁니다 근데 가중치의 값이 현재 각각 곱할 배열의 형식이 다릅니다 얘는 가로 얘는 세로입니다 둘 다 동일하게 세팅을 할 수 있도록 행가 열을 바꿔 주는 함수가 문제에 제시가 되어 있습니다 포즈라는 함수를 이용해서 행과 열을 바꿔서음 가중치를 바꾸셔도 되고 또는 각각 제품 상태부터 포장 상태의 값을 바꾸셔도 돼요 그러니까 편하신 어떤 걸 선택해도 상관없는데 둘 다 가로면 가로 세로면 세로 동일하게 맞춰 주신 다음에 곱하고 도할 수 있도록 소식을 작성해 주시면 됩니다 선 프로덕트 함수를 통해서 값을 한번 구하도록 하겠습니다 썸 프로덕트 제품 상태 맛 그다음에 어 포장 상태까지 c 3부터 23을 선택하시고요 c 3부터 23까지 이제 아래쪽에 가중치를 참조하는데 동일한 가로 배열로 표시될 수 있도록 함수 TR 입력하시고 트랜스포즈 함수 선택하신 다음 가중치 값이 현재 세로로 작성이 되어 있는데 가로로 바꿔서 표시하겠습니다 탄 가중치 값은 수식을 복사하더라도 항상 같은 자리니까 F4 눌러서 절대 참조 트랜스포즈 대한 가호 다고요 프로덕트에 대한 괄호를 고요 엔터 눌러 주시면 3.4라인 곱하고 더한 결과가 표시가 되어 있습니다 이제음 어 만족도는 계산하셨죠 뺀만큼음 하트를 이제 하얀색 하트로 표시하라고 되어 있습니다 3.4 3.9 이렇게 표시가 되어 있는데요 문제 제시된 INT 함수를 통해서 정수값을 구해서 반복해서 표시하겠습니다라고 리피트 함수를 통해서 하트 모양을 반복해서 표시해 보도록 하겠습니다 정수로 표시하겠습니다 INT고 입력하고 맨 끝에 가서 가로 닫고 엔터 누르시면 3.4 3.95 아래 내용이 정수로 표시가 되어 있고요 3 2 4 5 이렇게 표시가 되어 있습니다 3 세 번만큼 세 번 하트를 검정색 하트를 표시하겠습니다 라고요 앞에다가 커서 등호 다음에다 커서 갖다 놓고요 리트라 입력합니다 리피트 어 리피트 안쪽에 커설 두셔도 되고 아니면 직접 입력하셔도 되는데요 직접 텍스트는 미음 누르고 한짝 키 눌러서 미움 누르고 한자키 눌러서 저희는 하트 모양의 검정색 하트를 표시하겠습니다 큰 따옴표 다고요 신표 몇 번 조금 전에 썸 프로덕트와 인트 함수를 통해서 정수를 구해 놨던 3이라는 숫자 세 번이라는 값을 구해 놨습니다 맨 끝에 가셔서 가로 닫고 엔터 누르시면 어 3이란 숫자가 세 번이 반복되어 있고요 아래 소식을 복사하면 각각의 숫자만큼 검정색 하트가 표시가 되어 있습니다 이제 어 검정색 하트 표시 뒤에다가 5에서 만족도를 뺀만큼 하얀색으로 표시하시고 되어 있습니다 그럼 마찬가지로 리피트 맨 끝에까지 범위 정해서 컨트롤 c 마이너스 컨트롤 V 하시고요 이제는 검정색이 아니라 미음 누르고 한자키 눌러서 우리는 하얀색 하트를 선택하고요 인트 함수를 통해서 구하게 아까 3이었다 5 마이너스라고 수정해 주시고 엔터 눌러 주시면 어 저 제가 수정한 부분에음 잘못된 부분이 있었나 봐요 값의 오류가 있죠 다시 한번 볼게요 아 마이너스가 아니죠 여기 앞에다 제가 마이너스를 리피트 앞에다 마이너스를 붙여 붙였네요 % 연산자를 이용해서 연결하겠습니다라고 했어야 되는데 퍼가 아니라 마이너스를 입력했습니다 5 빼라고 생각하고 마이너스를 입력한 거 같습니다 앞에 검정색 하트와 뒤쪽에 하얀색 하트를 연결하겠습니다라고 퍼라 입력하고 수정하 다음에 아래로 수정 수식을 복사해 보시면 처음에 만족도의 숫자만큼 검정색 카트 뒤에는 5에서음 만족도의 숫자를 뺀 간만큼 하얀색 하트로 표시된 걸 확인할 수가 있습니다 두 번째 예제까지 살펴 보셨고 다음은 세 번째 예제를 보도록 하겠습니다 세 번째는 표 4에 가셔서 출석 발표 보고서 수행 교수 평가 동료 평가 값이 있습니다 그래서 평가와 표 5에 있는 표 5와 표 6을 참조하여 평가 결과를 표시하라고 되어 있습니다 평가 점수는음 표 5를 참조하여 각 과목별 출석은 68 * 10% 발표는 77 * 20% 각각의 각 반별 어 참 좋아요 어 각 평가 반별 음반 b c 반에 또 따라서 어 비중이 좀 다르네요 그래서 각 반별 항목별 비중을 곱한 값을 더하 곱한 값을 더하여 계산하고 평가 결과는 계산된 평가 점수를 표 6에서 찾아 가지고 탈락 통과 우수 성적이라고 표시하라고 되어 있습니다 그래서 표 6을 찾아오는 건 조금 있다 하고요 우선 값을 평가 점수를 구하도록 하겠습니다 평가 점수는 어 선 프로덕트 함수를 통해서 곱아서 더하도록 하겠는데요 예를 들어서 반 A 반 B 반 C 반이 나눠지지 않았다면 선 프로덕트 아고 입력하고 저희는 출석부 동료 평가까지 신표 아래쪽에 항목에 가서 F4 눌러서 절대 참주 가로 닫고 엔터 눌러 주시면 이렇게 프로덕트 함수를 통해서 곱하고 도한 결과값을 표시할 수 있는데 현재 예제 다행히 a 반이니까 74.5 맞지만 C 반일 경우는 조금 다르죠 계산 어 곱하 값들이 좀 달라집니다 그래서 반별로 어 각각에 해당된 값을 찾아 가지고 곱할 수 있도록 어 수식을 다시 한번 수정하도록 하겠습니다 문제 제시된 h 루커 마지막에 표 6에서 찾아올 때 사용하도록 하겠고요 썸 프로덕트는 평가 어 점수를 구하기 해서 사용하는데 옆 옆에 보시면 오색 까 매치 함수가 제시가 되어 있습니다 오프셋 함수를 통해서 몇 번째 위치로 이동하고 그다음 어 매치 함수를 통해서 어 찾고자 한 A 반 B 반 C 반에 해당한 값을 어 몇 번째 위치합니다라고 값을 찾아 가지고 옵셋에 넣도록 하겠습니다 이제 커서를 5 3의 커설 갖다 놓으시고요 그럼 선 프로덕트 함수가 하는 역할은 알겠고요 매치가 함 하는 함수 역할은 매치는 m tch 저희는 평가 반을 가지고 아래쪽 표 5에 가서 평가 항목 반에 가서 찾겠습니다 F4 눌러서 절대참조 신표 정확하게 일치라고 0이라고 입력하고 가로 닫고 엔터 누르시면 a 반은 1번 B 반은 두 번째 C 반은 세 번째라고 상대적인 위치값을 1 2 3으로서 있습니다 1 2 3 매치 함수 하는 역할이고 이제 어 어 오셋 함수를 통해서 오셋 함수를 통해서음 a 반일 때 반일 때 첫 번째에 가서 옆으로음 다섯 개 해당한 영역을 가져다가 이따가 프로덕트 함수 안에다가 넣어 주도록 하겠습니다 어디서부터 작성하면 좋을까음 썸 프로덕트 터 작성을 해 볼게요 썸 프로덕트 우리는 출석부 어 N3 아지 그래서 J 3부터 N3 영역을 을 선택하고 신표 그다음 어 아래쪽에 표 5에 가서 각각의 가져을 10% 20% 40% 가중치 값을 가져오도록 하겠는데 그 값은 문제에 나와 있는 오셋 함수를 통해서 구하도록 하겠습니다 오셋 시작점을 시작점을 어 h 16을 선택하시고 h 16세를 선택하시고 기준이 되는 거예요 H1 셀에서 F4 눌러서 절대 참조 수식을 복사하더라도 모든 어 기준점은 h16 신표 행의 위치는 h 16세에서 시작해서 아까 매치 함수를 통해서 1 2 3이라는 숫자를 구해 놨잖아 아래로 한 칸 아래로 두 칸 아래로 세 칸 a 반이면 h 16세에서 아래로 한 칸 갈 거고요 B 3이면 H 16세에서 아래로 두 칸을 갈 겁니다 한 칸 두 칸 세 칸은 매치 함수를 통해서 구해 놨습니다 그다음 표 이동을 할 건데 아래로 한 칸 또는 두 칸 세 칸을 이동하고 옆으로 한 칸을 이동할 겁니다 옆으로 반이면 다시 옆으로 한 칸을 갈 겁니다 한 칸을 이동해서 신표 몇 개의 행과 열의 너비와 높이값을 가져오실 거냐면요음 그다음 현재 어 오셋 함수에서 높이 값은 높이 값은 1행 행은 높이는 하나고요 그다음에 너이 값은 출석부 동료 평가까지 세보면 다섯 개입니다 다섯 개의 열에 해당한 값을 가져와서가 높이는 1 너비는 다섯 개 해당한 10% 10% 10% 10%에서 출석부 동료 평가에 해당한 한 줄이 올 수 있도록 어 1마 5라고 입력하시고 옵셋에 대한 괄호를 닫습니다 그래서 옵셋은 기준이 있는 h 16세에 가서 아래로 몇 칸 또 위로도 갈 수 있어요 마이너스가 구해지면 위로 가겠지만 지금은 매치 함수를 통해서 1 2 3 구했나요 그래서 아래로 하나 또는 둘 세 개 갈 거고요 옆으로 한 칸을 가서 어 행은 하나 행은 하나 높 그니까 행은 하나 높이 아 열은 다섯 개 예 행 열 그죠 열은 다섯 개에서 1 그다음에 열은 계수가 다섯 개입니다 5라고 넣어 주시면 되겠습니다 이제 세대 한 가호를 탔고요 선 프로덕트에 대한 괄호를 탔고요 그리고 나서 그리고 나서 우리가 어 엔터를 눌러 보시면 74.5 아고 구해집니다 아까 간단하게이 계산했을 때 74.5 반은 당연히 첫 번째 데이터를 참조했던 때문에 구해 졌고요 아래로 수식을 복사해 보시면 C 반의 데이터를 가지고 반의 데이터를 가지고 어 평가 결과 점수가 표시가 되어 있습니다 이제는이 점수를 가지고 표 6에 가서음 기준에 따라서 탈락 또는 통과 우수 성적이라고 표시하고자 합니다 등호 다의 컷을 갖다 놓고요 h 루커이라고 입력하시고 h 루커 안쪽에 커설 두신 다음 함수 삽입에 가셔서 조금 전에 구한 값을 표 6에 가서 F4 눌러서 절대 참조 어 두 번째 첫 번째 행에서 값을 찾고 두 번째 행에서 값을 가져오겠습니다 정확하게 일치하는 값은 아니죠 0부터 70 미만 70 이상부터 90 미만 90 이상에 해당하니까 정확하게 일치하지는 않고요 어 비슷하게 일치하는 값 추루 있니다 출루는 생략하실수 있어요 확인 버튼 누르시고요 아래까지 수식을 복사해 보시면 각각의 평가 점수를 가지고 표 6을 참조해서 평가 결과를 표시할 수가 있습니다 아까음 선 프로덕트 함수도 앞에서 해 보셨는데 좀 새로운 함수가 오프셋 함수가 조금 어 처음 이렇게 접하실 수 있을 것 같습니다 셋은 상대적인 위치값 어 선택할 수 있고요음 시작을 h 16세를 시작해서 어 행 그다음에 열만큼 이동하고 그다음에 높이와 너비의 값만큼 숫자를 넣어서 그 영역을 영역을 프로덕트 함수에 넣어 가지고 계산한 결과입니다 그래 세 번째 예제까지 살펴 보셨고 다음은네 번째 예제를 보도록 하겠습니다 네번째 예제는 표 7에서 표 7에서 출석 일수와 1차 2차 3차 점수를 이용하여 수강 평가를 계산하시오고 되어 있습니다 출석 일수가 17 이상이면서 출석일수가 17 이상이면서 1차 2차 3차가 모두 65점을 초과하면 패스 그외는 공백으로 표시하시오 함수를 통해서 조건을 주시고 조건에 만족하면 패스 그렇지 않으면 공백으로 표시할 겁니다 어 그다음 드는 조건이 하나가 아니기 때문에음 두 가지 조건에 모두 만족해야 되기 때문에 드 함수를 사용한 거고요 카운트 F 함수를 통해서 조건에 만족한 데이터의 계수를 구할 수가 있습니다 일단 카운트부터 작성을 해 볼게요 카운트 if 1차부터 3차까지 영역에 가서요 직접하는게 어려우시면 마법사 이용하셔도 돼요 그래서 1차부터 3차 영역을 선택하고 그 점수가 65점을 초과합니다 큽니까 65라 입력합니다 크거나 같다가 아니라 큽니까라고 조건을 주신 겁니다 그리고 확인 버튼 누르시면 어 다 65점 다 큰데요 그래서 만족한 데이터가 세 건입니다 한 건입니다라고 숫자가 구해져 있습니다네 문제에서 모두 어 만족한다면 결과가 3이란 숫자가 나와야 되겠죠이 값이 어 카운트 입 함수를 통해서 구한 값이 모두 만족한다면 3하고 같습니까네 3하고 같아요 그러면 트루 3하고 같지 않은 데이터는 폴스 이렇게 결과가 표시가 되어 있습니다 다시 등호 다음의 커서를 갖다 놓고요 드 함수를 통해서 드 함수를 통해서 가로 열고 첫 번째 조건은 출석 일수가 크거나 같습니까 17보다 크거나 같습니까 17 이상이라고 되어 있으니까 크거나 같습니까 두 번째 조건은 카운트 함수를 통해서 작성한 소식 맨 끝에 가서 가로 닫고 엔터 두 가지 조건에 모두 만족하면 추루 하나라도 만족하지 않은 데이터가 있다면 포스라고 표시가 되어 있습니다 이제 등호 다음에 커서를 갖다 놓고요 if 만약에 트루는 저희는 뭐라고 표시할 거냐면요 문제에 나와 있는음 패스라고 표시하겠습니다 패스라고 입력하시고 그렇지 않다면 나머지는 공백으로 표시하겠습니다라고 큰 다운 열고 닫고에 대한 괄호를 닫고 아래까지 수식을 복사해 주시면네 출루에 있었던 곳에는 패스라고 표시가 되어 있고요 나머지는 공백으로 표시된 걸 확인할 수가 있습니다 다음은 다섯 번째 예제를 보도록 하겠는데요 표 7에서 출석 일수와 아니 결석 일수와 그다음 1차 2차 3차의 점수를 이용하여 수강료 할인률을 계산하시오고 되어 있어 어 수강률 할인율은 평균에 따른 평균에 따른음 표 8을 참조하라고 되어 있고요 표 파을 참조하여 계산하시오고 되어 있습니다 그럼 평균값을 구할 거고요 평균값을 아래표에 가서 첫 번째 열에서 찾고 세 번째 열에는 할인율을 가져올 겁니다 그래서 V 컵이라 함수를 사용하실 거고요 또한 결속 일수가 0이면 어 할인율 0.5를 더 추가해 달라고 더해 달라고 되어 있습니다 일단음 2 함수를 먼저 쓰셔도 되고 뒤에 쓰셔도 돼요 평균값을 일단 구해 보도록 하겠습니다 에버레이 1차부터 3차까지 평균을 구하겠습니다 가로 닫고 엔터 아래 지금 퍼센트가 나오는 이유는 미리 100분율 서식이 적용이 되어 있어서 그런 겁니다 어 84 그러니까 곱 100분은 곱하기 100하는법 그러니까 곱하기 100을 나눠 준다 그러니까 100을 나눠 준다면 84고정 이고요 83.6 이고요이 값을 브류 컵을 통해서 평가표에 가서 할인율을 찾아오고 합니다 등호 다음에 커서를 갖다 놓고요 브 6컵 안쪽에 커설 두신 다음 차을 값은 에버리지 함수를 통해서 구해 놨고요 참조할 표는 표 8에 있습니다 F4 눌러서 절대 참조 첫 번째 열에 가서 찾고 세 번째 열에서 값을 가져올 거고요 정확하게 일치하는 값은 아니니까 생략하시고 확인 그러면 어 각각의 평균값을 가지고 수각류 할인률을 찾아왔습니다이 값에다가 추가하고자 되 추가하라고 되어 있어요 더하기 if 만약에요 결석 일수가 0이라면 0이라면 우리는 문제에서 0이면 0.5를 더 추가해 달라고 되어 있습니다 0.5% 추가하겠습니다 라고요 0.5% 그렇지 않다면 0% 있니다 0이라고 입력하셔도 되고 0% 입력하셔도 되고요에 대한 괄호 닫고 엔터 눌러 주신 다음 아래까지 수식을 복사하시면 결석 일수가 0인 경우는 0.5를 더해서 결과로 표시가 되어 있습니다 그래서네 번 어 다섯 번째 예제까지 살펴 보셨고 다음은 여섯 번째 예제를 보겠습니다 오른쪽에 표구를 참조하시면 될 거 같고요 표 9에 목표와 실적을 이용하여 표 10의 달성률에 따른 달성률에 따른 PS 지급률을 참조하여 어 참조하여 최종 PS 지급률의 영역에 표시하시오고 되어 있습니다 그럼 PS 어 최종 PS 지급률은 어 PS 지급률 더하기 추가 지급이라고 되어 있습니다 어 그러면 어 PS 지급률은 찾아오시면 돼요 함수를 브 6컵이나 지금 제시되어 있는 룩업 함수를 통해서 달성률에 값을 가지고 찾고 결과는 PS 지급률을 찾아오겠습니다라고 소식을 작성하시면 되고요 추가 지급률이 있습니다 추가 지급률은 PS 어 지급률 어 PS 지급률은 어 실정 나누기 목표로 계산해서 값을 찾아오면 될 거 같고요 추가 지급률은 달성률 어 달성률이 120% 이상이면서 또 조건이 하나 더 있네요 그러면 엔드 함수가 필요합니다 간접 경비 나누기 직접 경비가 50% 이상이면 7% 그렇지 5% 80% 미만일 경우는 -5% 그외는 0% 계산하라고 되어 있습니다 어 계산할식이 좀 많은 거지 함수식 자체로는 어려운 거 같지 않습니다 어떤 먼저 어 할까요 추가 지급률이 편 씌우시면 추가 지급률을 먼저 하셔도 되고요 아니면 찾아오는 작업을 먼저 하셔도 됩니다 PS 지급률을 찾아오시는 작업을 하셔도 돼요 그럼 PS 지급률이 먼저 제시가 되어 있으니까 그 첫 번째 PS 지급부터 계산해 보겠습니다 PS 지급률은 찾아올 건데요 문제에 나와 있는 브루커의 함수가 아니라 룩업 함수가 제시가 되어 있습니다 룩업 값 룩업 함수 입력하시고 찾을 값은 어 PS 지급률은 실정 나누기 / 목표 그래서 어 실정 나누기 목표 계산식을 넣어 주시고 그 값을 어디에서 찾을 거냐면음 표 10에 가서 찾겠습니다 그래서 첫 번째 영역을 달성률 선택하셔도 되고요 F4 눌러서 절대참조 그다음 신표 결과는 PS 지급률에 가져오겠습니다라고 선택하고 f3 눌러서 절대 참조 하셔도 됩니다 가로 닫고 엔터 눌러서 아래까지 수식을 복사해 보시면 지급률을 찾아왔는데요이식이 맞는지 한번 볼까요 문제 나와 있는 실정 나누기 목표 엔터 아래까지 수식을 복 소식을 복사해 보시고요 이렇게 보면 좀 어려울 수 있잖아요 백분율로 바꿔 보시고요 108이 108은 150보다 작은 데이터 20% 찾아 왔고요 125도 마찬가지로 150보다 작 은 20% 83은 100% 안 되니까 0%고 값을 잘 찾아왔습니다 그래서 룩업 함수를 통해서 달성률 값을 구한 값을 첫 번째에 가서 찾고 PS 지급률을 가져오겠습니다라고 값을 찾아왔는데요 루거 함수는 지금 작성하실 때 좀 인수를 또 다르게 쓸 수 있습니다 안쪽에 값을음 실정 나누기 목표로 계산하신 값을 두 번째 범위를음 찾을 값과 결과값을 따로 주지 않고요 한 번의 범위를 지정하면 첫 번째 열에서는 찾고 두 번째 열에서 값을 가져옵니다 자 F F4 눌러서 절대 참조 가로 닫고 엔터 그리고 아래로 수식을 복사해 보시면 아까 구하셨던 결과와 동일한 결과로 표시된 걸 확인할 수가 있습니다 그래서 둘 다 여러분이 쓰셔도 틀리지 않단 얘기고요 그다음 뒤에다가 더하기 추가 지급률을 계산할 건데요 함수를 통해서 조건을 작성하고자 합니다 조건이 하나가 아니라 두 개니까 드 함수가 필요하고요 첫 번째 조건은음 추가 지급률은 달성률이 아까 계산한 달성률 실정 나누기 어 목표가 120 이상입니까 라고요음 실정 나누기 목표가 120 이상입니까 크거나 같습니까 아 아 120 이상이면서라고 돼 있으니까 크거나 같습니까라고 입력하시고 그다음 120 이상이면서라고 되어 있으니까 120% 아고 입력하셔도 되고요 또는 1.2라인 셔도 돼요 그다음 신표 두 번째 어 간접 경비 나누기 직접 경비가 간접 경비 나누기 직접 경비가 50% 이상입니까 크거나 같습니까 50% 넣으셔도 되고요 0.5 넣으셔도 됩니다 그리고 가로 다고요이 조건에 만족하면 저희는 7% 더 집을 추가 할 겁니다 7%고 입력하시고 숫자이 큰 다표 처리하지 않았습니다 다시 한번 어 달성률이 80% 미만이면 두 번째 조건이 있습니다 if 만약에요음 달성률이 달성률에 대한 계산식은 실정 나누기 목표 그 값이 80% 미만이라고 돼 있어요 작다 80% 또는 0.8이다 그렇다면 저희는 -5% 그렇지 않다면 나머지는 0% 입력하겠습니다라고에 대한 괄호를 닫고 이프를 두 번 사용하셨으니이 가로를 두 번 닫고 엔터 눌러서 아래까지 식을 복사해 주시면 복사해 주시면 어 두 번 추가 지급률에 대한 계산식에 따라서 결과가 플러스 되거나 마이너스 된 결과로 표시된 걸 확인할 수가 있습니다 자 오른쪽에 여러분이 시험 보실 때음 참조를 이용 어 위해서 어 작성해 놓으셨던 수식이 있다면 지워 주시면 되겠고요 실습하게 작성하셨나요이 실습한 내용을 확인 보시면 되겠습니다 그래서 찾기 참조 예제를 같이 살펴보셨습니다