| |
OFFSET($P$3,(ROW()-15)/16+COLUMN()-9,0)
OFFSET 이 참조함수고 row()가 행위치 반환 함수라는건 알겠는데
row()-15/16의 의미는 뭔가요?
==============[가을소고님 글에 대한 답변입니다]==============
OFFSET 함수의 문법
OFFSET(reference, rows, cols, [height], [width])
기능:
첫째 인수 reference로 지정한 셀을 기준으로
rows만큼 아래로, cols만큼 오른쪽으로 이동한 셀로부터
(음수로 지정된 경우에는 반대 방향이 되겠지요)
height만큼 아래쪽(반드시 양수여야 함)
width만큼 오른쪽(역시 반드시 양수)으로 간 만큼의 범위를
반환합니다.
만일 height나 width를 생략하면 1로 간주되어
둘 다 생략한 경우에는 딱 그 셀만 참조하게 됩니다.
예1) OFFSET($P$3,2,0,3,2)
$P$3을 기준으로 아래로 2칸, 오른쪽 0칸을 가면
$P$5가 되겠지요.
거기서부터 다시 아래로 3줄, 오른쪽 2칸의 직사각형을 그리면
$P$5:$Q$8 범위를 반환합니다.
이렇게 범위를 반환하는 경우는
데이터유효성검사 목록이나 또는 다른 함수의 인수로 사용할 수 있습니다.
즉, =SUM(OFFSET($P$3,2,0,3,2))과 같이 사용하면
$P$5:$Q$8 범위의 합계를 구할 수 있지요.
예2) =OFFSET($O$3,1,2)
$O$3을 기준으로 아래로 1칸, 오른쪽 2칸을 가면
$Q$4가 되겠지요.
height와 width를 모두 생략했기 때문에
이 경우에는 $Q$4 셀에 있는 수식의 계산결과 값이 바로 반환됩니다.
질문하신 파일을 살펴보니
$O$3:$R$6 범위의 데이터를 원본으로
(현재 올려진 파일은 6행의 자료가 깨져 있네요)
선수별 신상카드에 각 항목을 참조해 올 목적인 것 같은데
수식 자체가 논리적으로 오류가 있는 것 같습니다.
여유~님께서 댓글로 지적하신 것처럼
16행마다 선수카드가 있는 것으로 짐작할 수 있는데
그렇다면 각 항목별로 '/16'이 공통으로 들어 있어야 할텐데
어느 항목은 '/29'가 있는 등 뒤죽박죽이네요.
그럼에도 불구하고 현재 김새다 선수의 참조 값들이 나타나 있는 것은
실제로 논리오류가 있는 복잡한 수식의 계산 결과가 모두 0이기 때문입니다.
즉, =OFFSET($O$3,(ROW()-15)/16+COLUMN()-2,0) 수식에서
(ROW()-15)/16+COLUMN()-2의 실제 계산 결과는 0.25인데
이 위치의 rows 인수 값은 정수만 받아들이기 때문에
=OFFSET($O$3,0,0)으로 계산되어
결국은 =$O$3 와 같은, 아무 의미 없는 사족같은 수식이 되어버린 꼴입니다.
사실 정수 값만 받아들이는 인수의 위치에
0.25가 계산되는 수식을 설계했다는 것 자체가
그게 논리오류라는 것을 반증하는 것이겠지요.
==========================
만약 16행마다 선수카드를 복사해서 사용하고
선수카드의 순서와 데이터 참조표의 순서가 동일한 조건이라면
수식을 다음과 같이 고쳐야 합니다.
C3(선수): =OFFSET($O$2,QUOTIENT(ROW(),16)+1,0)
C4(종목): =OFFSET($O$2,QUOTIENT(ROW(),16)+1,2)
C5(번호): =OFFSET($O$2,QUOTIENT(ROW(),16)+1,3)
G5(고유번호): =OFFSET($O$2,QUOTIENT(ROW(),16)+1,1)
J3(생년월일): 이 항목은 $O:$R의 원본데이터 표에 자료가 없네요
원본 데이터 표를 고쳐서 적용해야 할 것 같습니다.
위와 같이 기준 셀은 항상 $O$2로 고정해주고(참조표의 시작위치)
거기서부터 몇 번째 행인가는 몇 번째 선수카드인가의 문제가 되겠죠.
선수카드가 16행 단위로 된다면 현재 행번호가 16의 배수 간격이 될 것이므로
현재 행을 16으로 나눈 자연수 나눗셈의 몫(QUOTIENT 함수)을 취하면
첫번째 선수카드는 0, 두번째 선수카드는 1, ... 이렇게 됩니다.
여기에 1을 더한 값이 데이터참조표에서 참조할 행번호가 되겠지요.
그리고 열번호는 데이터참조표에서 선수명, 종목, 번호, 고유번호 등이
몇 번째 열에 있는가에 해당합니다.
그런 다음에 A1:M16을 복사해서
A17, A33 등에 붙여넣기 하면 됩니다.
20행 간격으로 복사하려면
위 식에서 QUOTIENT(ROW(),16) 부분을
QUOTIENT(ROW(),20)으로 수정하면 되겠지요.
==========================
오히려 이런 목적이라면
OFFSET보다는 VLOOKUP 함수를 쓰는 것이 훨씬 효율적일 겁니다.
1) 이름은 그냥 직접 입력하거나 또는 데이터유효성검사 목록을 사용하고
- 제한대상: 목록
- 원본: =OFFSET($O$3,0,0,COUNTA($O:$O)-1)
이럴 때 OFFSET함수를 쓰는 겁니다.
$O$3을 기준으로 아래로 0칸, 오른쪽 0칸을 가면 도로 그자리
COUNTA($O:$O)는 0열 전체에서 데이터가 입력된 개수를 세고
거기서 1을 빼 주는 이유는 표 제목 "성명"이 카운트된 것을 빼니까
실제로 이름이 입력된 개수가 계산되겠지요.
그게 행 수가 되고
width는 생략했으니 1이 되겠지요.
따라서 데이터 유효성 목록이 딱 원본 표에 입력된 선수만큼
나타나게 됩니다.
2) 각 참조값은 VLOOKUP 사용
C4(종목): =VLOOKUP(C3,$O:$R,3,0)
C5(번호): =VLOOKUP(C3,$O:$R,4,0)
G5(고유번호): =VLOOKUP(C3,$O:$R,2,0)
J3(생년월일): 이 항목은 $O:$R의 원본데이터 표에 자료가 없네요
원본 데이터 표를 고쳐서 적용해야 할 것 같습니다.
이 경우에는 선수카드를 16행 간격이든, 20행 간격이든
알아서 복사해 사용할 수 있고
선수카드의 순서와 데이터참조표의 순서가 달라도
또 선수 명단 중 일부 선수만 출전하는 경우에도 상관 없습니다.
=================================
VLOOKUP 함수의 문법
=VLOOKUP(조회하려는 값, 값을 조회하려는 범위, 반환 값이 들어 있는 열 번호, 정확히 일치 또는 유사 일치(0/FALSE 또는 1/TRUE로 표시됨)).
예1) =VLOOKUP($C$3,$O$3:$R$6,3,0)
$O$3:$R$6에 있는 참조표의 맨 왼쪽 열('인덱스 키'라고 함)에서
$C$3에 있는 값과 일치하는 값(행)을 찾아
그 줄의 3번째 열에 있는 값을 가져옴
예2) =VLOOKUP($C$3,$O:$R,3,0)
참조표의 주소를 지정하는 방식에서 이처럼 행번호를 생략하면
O열부터 R열까지 열 전체를 참조표로 인식
즉, $O:$R 은 $O$1:$R$1048576 으로 해석됨
이렇게 함으로써 O열~R열의 원본 참조표에
선수가 몇명이든 자료만 얼마든지 입력하면 되겠습니다.
예3) =VLOOKUP($C$3,$O$3:$R$6,2) 또는
=VLOOKUP($C$3,$O$3:$R$6,2,1)
$O$3:$R$6에 있는 참조표의 맨 왼쪽 열에서
$C$3에 있는 값보다 작거나 같은 값 중 제일 큰 값을 찾아
그 줄의 2번째 열에 있는 값을 가져옴
단, 이렇게 네 번째 인수를 생략하거나 1, TRUE 등으로 지정하여
유사한 값을 참조할 목적이라면
데이터 참조표는 반드시
첫째 열(인덱스 키)를 기준으로 오름차순 정렬되어 있어야 합니다.
특히 $O:$R과 같이 열 전체를 참조표 범위로 지정할 경우
표 헤드(이 파일의 경우 '성명') 데이터까지도
인덱스키로 인식해 버리기 때문에 주의해야 합니다.
|
|
엑셀에 대한 질문과
답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
