■ 목차
1. 함수 설명
2. Offset 함수의 구문 / 사용방법
3. Offset 함수 사용해 보기
4. Offset 함수 응용 - 마지막 값 구하기
5. Offset 함수 응용 - 1차원 데이터를 2차원 데이터로 변환하기
6. Offset 함수 응용 - 동적 목록 만들기
7. Offset 함수 응용 - Match, Offset 함수 활용
** 핵심 요약
1. 함수 설명
- 오늘은 Offset 함수에 대해서 알아보도록 하겠습니다.
- Offset 함수의 뜻은 영어로는 "상쇄하다. 벌충하다"이지만
- 컴퓨터에서는 "떨어진 거리"라는 변위차를 의미하고 일정한 거리가 떨어져 있다는 의미로 사용이 됩니다.
- 그리고 엑셀에서도 특정지점에서 일정거리가 떨어진 셀 혹은 여러 셀을 가져올 수 있는 "범위"함수에 해당합니다.
- 떨어진 거리를 정확하게 알 수 있고 지정할 수 있을 때 사용할 수 있는 함수로
- 1차원으로 나열된 반복되는 데이터를 Row 함수와 수식으로 2차원 데이터로 가져오거나
- Counta 함수를 응용하여 데이터 개수를 확인하고 필요한 데이터를 가져오는 기능으로 많이들 사용하고 있습니다.
- 사용방법이 조금 복잡해 보일 수도 있으나 떨어진 곳의 셀을 가져오기 위해 꼭 필요한 인수로 구성이 되어 있어
- 함수의 기능을 이해하기에는 그렇게 어려움이 없으니 차근차근 기본적인 개념을 이해하시고 응용해 보시기 바랍니다.
2. Offset 함수의 구문 / 사용방법
- OFFSET(reference, rows, cols, [height], [width])
- reference 필수 요소입니다. 오프셋을 기본으로 할 참조입니다.
- rows 필수 요소입니다. 왼쪽 위에 있는 셀이 참조할 위 또는 아래에 있는 행의 수입니다.
- cols 필수 요소입니다. 결과의 왼쪽 위 셀이 참조할 왼쪽 또는 오른쪽에 있는 열의 수입니다.
- height 선택 요소입니다. 반환되는 참조의 높이(단위: 행 수)입니다.
- width 선택 요소입니다. 반환되는 참조의 너비(단위: 열 수)입니다.
- 첫 번째 인수는 "reference(참조)"로 기준이 되는 셀이 됩니다. 이 셀에서 일정거리 떨어진 셀을 가져오게 됩니다.
- 두 번째, 세 번째 인수는 떨어진 거리로 "rows"는 행방향(세로)으로 "cols"는 columns로 열방향(가로)으로 떨어진 거리를 정수로 지정해 줍니다.
- 세 번째, 네 번째 인수는 가져올 셀의 개수를 height(높이, 세로, 행방향), width(너비, 가로, 열방향)으로 지정하게 됩니다.
- 두 번째 ~ 네 번째 인수는 숫자 양수, 음수 등 0을 포함해서 정수로 입력이 가능하며 기준이 되는 referenc로 지정한 셀의 위치를 0으로 기준합니다.
- 가져올 셀의 개수를 지정하는 height, width 인수를 생략하게 되면 보통 1개의 셀 주소를 가져오게 되는데
- 정확하게는 "reference"에서 지정해 준 셀의 개수 및 모양으로 가져오게 됩니다.
- 하지만 보통 우리는 기준이 되는 셀을 1개 지정하는 경우가 많기 때문에 그냥 1개의 셀을 가져온다고 보시면 됩니다.
- 여기서 개념상 중요한 것은 "reference"로 지정한 셀의 위치는 0이라는 것입니다.
- 이 부분만 정확하게 이해를 하시면 나머지는 그렇게 어렵지는 안습니다.
- 위치가 0인 기준 셀에서 "행/열(세로/가로)" → "행/열(세로/가로)"로 가져온다고 이해를 하시면 되는데
- 첫 번째 "행/열(세로/가로)"은 떨어진 위치, 두 번째 "행/열(세로/가로)"은 가져올 셀의 크기입니다. ^^
3. Offset 함수 사용해 보기
- 지정한 행, 열 만큼 떨어진 셀에서 한 개의 값을 가져오는 예제입니다.
- 이런 경우 보통 기준이 되는 셀 한 개를 설정하고 떨어진 위치를 지정하는 rows, cols 인수만 지정하고
- height, width 인수는 생략을 하는 방식으로 간단하게 사용을 할 수 있습니다.
- reference 인수로 셀 한 개를 지정해 주었기 때문에 height, width 인수를 생략하게 되면 해당 위치의 한 개의 셀만 가져오게 됩니다.
- 첫 번째 인수는 기준이 되는 셀 "B2"입니다. 그리고 항상 기준이 되는 셀은 기준값으로 위치가 0이 됩니다.
- 혹시 셀의 수식을 복사해서 사용할 일이 있다면 기준이 되는 셀은 보통 "절대참조 형식($B$2)"으로 F4 단축키를 사용해서 고정하는 방식으로 입력해 줍니다.
- 두 번째, 세 번째 인수는 떨어진 거리인데 항상 순서는 행/열(세로/가로) 순서로 입력을 해주시면 됩니다.
- 여기서는 두 번째 인수 행의 떨어진 거리는 B8 셀의 값을 세 번째 열의 떨어진 거리는 C8 셀의 값을 참조할 수 있도록 입력합니다.
- 세 번째, 네 번째 인수는 가져 올 셀의 범위를 지정하는 인수인데 생략을 하고 한 개의 인수만을 가져옵니다.
4. Offset 함수 응용 - 마지막 값 구하기
- 매일매일 입력되는 데이터에서 가장 최근의 값 (마지막 입력 값)을 가져오는 예제입니다.
- 가장 최근 값(마지막 값)을 가져오기 위해서는 먼저 전체 데이터 개수가 몇 개인지를 알아야 합니다.
- 그래야 Offset 함수를 이용해서 전체 데이터 개수만큼 떨어진 마지막 입력 값을 가져올 수 있습니다.
- 이렇게 전체 데이터 개수를 알기 위해서 사용하는 함수가 바로 Counta 함수입니다. 입력된 데이터의 개수를 헤아려 주는 함수입니다.
- Counta 함수로 전체 데이터 개수를 헤아려서 마지막 데이터 위치 셀의 값을 가져오는 방식으로 해주시면 됩니다.
- 첫 번째 인수는 기준이 되는 셀로 "B4"셀을 지정하고
- 두 번째 인수는 행으로 떨어진 위치를 지정해야 하는데 Counta 함수를 사용해서 입력된 데이터의 개수를 구합니다.
- 그리고 이때 Counta 함수의 범위는 충분히 크게 지정을 해줘서 추가로 입력되는 값이 있는 경우에도 동일하게 사용이 가능하도록 해줘야 합니다.
- 그리고 기준이 되는 셀의 위치에 따라서 마지막 셀의 정확한 위치를 지정해 주기 위해서 금번과 같은 경우는 B4:B99의 입력된 데이터 개수에서 빼기 1을 해줍니다. (Counta 함수 개수 -1 = 3 번째)
- 세 번째 인수는 열방향으로 떨어진 위치로 우리는 "가격" 정보를 찾아오기 위해 열방향으로 한 칸 이동할 수 있도록 숫자 1을 입력합니다.
- 이렇게 마지막 값을 가져오기 위해서는 ① 마지막 값이 입력된 위치를 Counta 함수로 확인을 하고
- 추가로 데이터가 입력되는 경우를 고려하여 ② Counta 함수의 범위를 충분히 크게 정해주시면 됩니다.
- 그리고 ③ Offset 함수로 마지막 값에 해당하는 값을 가져오시면 됩니다.
- 이런 방식으로 마지막 값 혹은 마지막 전 값 등의 특정 위치의 데이터를 찾아올 수 있게 됩니다.
5. Offset 함수 응용 - 1차원 데이터를 2차원 데이터로 변환하기
- 인터넷이나 다른 응용 프로그램에서 데이터를 다운로드한 경우에 반복되는 데이터가 한 줄로 쭉 일렬로 받아지는 경우가 있습니다.
- 예를 들면 3줄씩 한 세트로 데이터의 행열을 변경해야 하는 경우에 사용할 수 있는 방법입니다.
- 이 때는 Offset 함수와 Row, Column 함수를 사용해서 3줄마다 반복해서 데이터를 가져오는 수식을 만들어 주게 됩니다.
- 먼저 우리가 만들어 줘야 하는 Offset의 rows 인수의 값은 아래 이미지와 같습니다.
- 변환하고자 하는 셀에서 Row, Column 함수를 이용하여 아래와 같이 Offset 함수에 필요한 rows 인수 값을 만들어 주는데
- 어떻게 보면 수학문제에 가까운 풀이 방식입니다. ^^
- Row, Column 함수는 인수 없이 ( =row(), =column() ) 사용을 하게 되면 입력하는 현재 셀의 행번호, 열 번호 숫자를 반환하는 함수로 현재 셀의 행, 열 번호를 이용하여 아래 수식처럼 입력하여
- 한 개의 수식으로 전체 데이터를 1차원에서 2차원으로 전환하여 가져올 수 있게 됩니다.
- 이때 만약 예제와 다른 셀 위치에서 데이터를 가져온다면 순서대로 0, 1, 2, … 숫자를 만들어 주기 위해서 수식이 조금씩 달라집니다.
- 이렇게 rows 데이터값을 찾게 되면 Offset 함수를 이용해서 쉽게 원하는 데이터를 가져올 수 있게 되는 것입니다. ^^
- 수식의 핵심은 3개의 데이터를 가져오는 경우는 "*3"을 4개의 데이터를 가져오는 경우는 "*4"을 활용하여 적절히 더하기, 빼기를 해주는 것입니다. ^^
6. Offset 함수 응용 - 동적 목록 만들기
- 우리가 입력 데이터의 정확성을 위해서 "데이터 유효성 검사" - "목록"을 적용하는 경우가 있는 데 사용하다 보면 이 목록이 하나씩 추가가 될 수가 있습니다.
- 이렇게 목록이 추가가 되는 경우에도 자동으로 새로 추가된 목록을 포함하여 목록 리스트를 최신으로 업데이트해주는 방법으로 "동적 목록"이라고 하는데
- 일종의 엑셀을 이용한 "사무 자동화(?)"하는 방식입니다.
- 이렇게 Offset 함수는 "자동화"를 위한 용도로 사용이 될 수 있으며 이때 자동화, 즉 추가되는 목록 리스트를 자동으로 반영하는 수식에서
- 핵심이 되는 함수는 Counta 함수입니다. 목록 리스트가 추가가 되는 경우에도 현재 목록에서 리스트가 몇 개인지를 Counta 함수로 확인해서
- 확인된 목록 리스트 개수만큼 "목록"의 범위를 자동으로 지정해서 업데이트해주는 방식입니다.
- 이 작업을 하기 위해서는 "데이터 유효성" - "목록"을 사용할 것입니다. 경험이 있으면 좋겠지만 없으신 분들도 차근차근 따라 해 보시기 바랍니다.
- ① 먼저 "데이터 유효성 검사" - "목록"이 적용될 셀의 범위(D3:D5)를 마우스로 선택합니다.
- ② 메뉴 패스 (리본 메뉴) : "데이터" 탭 > "데이터 도구" 그룹 > "데이터 유효성 검사"
- ③ "설정" 탭에서 ④ 제한 대상으로 "목록"을 선택해 줍니다.
- ⑤ 옵션으로 "공백 무시", "드롭다운 표시"를 설정하고
- ⑥ "원본"에 "=OFFSET($B$3,0,0,COUNTA($B$3:$B$99),1)" 수식을 입력해 주는데
- 이때 기준이 되는 셀(reference 인수)은 "$B$3"을 설정하고 가져오는 행방향 셀의 개수(height 인수)를 품목 리스트에서
- 품목이 추가될 것을 고려하여 충분히 크게 범위를 잡아서 설정을 합니다. COUNTA($B$3:$B$99)
- 이렇게 설정을 하게 되면 추가되는 목록을 포함하여 전체 리스트 개수만큼 범위가 설정이 되고 목록을 가져오게 됩니다.
- 조금 복잡해 보이기도 하지만 Offset 함수를 이런 용도로도 사용을 할 수 있다고 참조해 주시면 되겠습니다.
- 그리고 사실 "데이터 유효성 검사"에서 동적 목록을 이렇게 복잡하게 설정할 필요는 없습니다. ^^
- "목록" 옵션에 "공백 무시"라는 기능이 있기 때문에 Offset 함수 없이 그냥 "품명 List"의 범위를 충분히 잡아 주시면
- 엑셀이 자동으로 입력이 되지 않은 공백을 무시하고 입력된 데이터에 대해서만 목록을 만들어 줄 수 있습니다. ^^
7. Offset 함수 응용 - Match, Offset 함수 활용
- 고속도로 목적지별 등급별 가격을 Match, Offset 함수를 활용해서 찾아오는 예제입니다.
- Match 함수로 행, 열의 위치를 찾고 Offset 함수로 해당 요금을 가져올 수 있습니다.
- 행, 열의 검색대상의 위치는 일치하는 값의 위치를 찾아주는 Match 함수를 활용하여 확인을 합니다.
- 이때 Match 함수의 일치여부의 옵션은 숫자 0으로 정확하게 일치하는 값을 찾는 것으로 설정합니다.
- 이렇게 행방향, 열방향 위치를 찾았다면 우리는 Offset 함수로 간단하게 해당 위치의 가격 정보를 찾아오는 것이 가능하게 됩니다.
- Offset 함수는 Row 함수, Counta 함수, Match 함수 등과 다양하게 응용하여 우리가 알 수 있는 위치의 값을 찾아올 수 있는 함수입니다.
- 조금 창의력이 필요한 경우도 있지만 사무 자동화를 하고자 한다면 한 번씩 사용하게 되는 함수이니
- 다양한 예제를 경험하시고 많은 응용과 활용을 해보시기 바랍니다.
- 수고 많으셨습니다. 즐거운 하루 보내세요.
** 핵심 요약 : Offset 함수 사용법 - 지정된 셀 가져오기, 동적범위, 마지막 값 구하기
1. 함수 설명
- Offset 이란 "떨어진 거리"라는 변위차를 의미하고 일정한 거리가 떨어져 있다는 의미로 사용된다.
- 특정지점에서 일정거리 떨어진 셀 혹은 여러 셀을 가져올 수 있는 "범위"함수로 사용된다.
2. Offset 함수의 구문 / 사용방법
- OFFSET(reference, rows, cols, [height], [width])
- 첫 번째 인수는 "reference(참조)"로 기준이 되는 셀이 된다. 이 셀에서 일정거리 떨어진 셀을 가져오게 된다.
- 두 번째, 세 번째 인수는 떨어진 거리로 "rows"는 행방향(세로)으로 "cols"는 columns로 열방향(가로)으로 떨어진 거리를 정수로 지정해 준다.
- 세 번째, 네 번째 인수는 가져올 셀의 개수를 height(높이, 세로, 행방향), width(너비, 가로, 열방향)으로 지정하게 되다.
- "reference"로 지정한 셀의 위치는 0이 되고 이를 기준으로 떨어진 거리를 찾게 된다.
3. Offset 함수 사용해 보기
- 첫 번째 인수는 기준이 되는 셀을 지정하고 항상 기준이 되는 셀은 위치가 0이 된다.
- 두 번째, 세 번째 인수는 떨어진 거리인데 항상 순서는 행/열(세로/가로) 순서로 입력을 한다.
- 세 번째, 네 번째 인수는 가져 올 셀의 범위를 지정하는 인수인데 생략을 하고 한 개의 인수만을 가져올 수 있다.
4. Offset 함수 응용 - 마지막 값 구하기
- ① 마지막 값이 입력된 위치를 Counta 함수로 확인을 하고
- ② 추가로 데이터가 입력되는 경우를 고려하여 Counta 함수의 범위를 충분히 크게 정해준다.
- ③ 그리고 Offset 함수로 마지막 값에 해당하는 값을 가져오면 된다.
- 이런 방식으로 마지막 값 혹은 마지막 전 값 등의 특정 위치의 데이터를 찾아올 수 있다.
5. Offset 함수 응용 - 1차원 데이터를 2차원 데이터로 변환하기
- Offset 함수와 Row, Column 함수를 사용해서 3줄마다 반복해서 데이터를 가져오는 수식을 만들어 주어야 한다.
- 수학문제로 셀 위치에 따라서 Row, Column 함수를 사용해서 rows 인수의 값이 0, 1, 2, 3, … 이 되도록 만들어 보자
- 핵심은 3개의 데이터를 가져오는 경우는 "*3"을 4개의 데이터를 가져오는 경우는 "*4"을 활용하여 적절히 더하기, 빼기를 해주는 것이다.
6. Offset 함수 응용 - 동적 목록 만들기
- 엑셀의 "데이터 유효성" - "목록" 기능을 사용한다.
- Counta 함수로 현재 입력된 셀의 개수를 카운트하여 Offset 함수로 필요한 개수의 데이터를 가져온다.
- "목록" 옵션에 "공백 무시"라는 기능이 있기 때문에 Offset 함수 없이 그냥 해당 범위를 충분히 설정해 줘도 된다.
7. Offset 함수 응용 - Match, Offset 함수 활용
- 행, 열의 검색대상의 위치는 일치하는 값의 위치를 찾아주는 Match 함수를 활용하여 확인한다.
- 행방향, 열방향 위치를 찾았다면 Offset 함수로 간단하게 해당 위치의 가격 정보를 찾아오는 것이 가능하다.
[엑셀 함수 강좌-플러스] - 999. 엑셀 함수 Plus 목차
999. 엑셀 함수 Plus 목차
[엑셀 함수 강좌-플러스] - 001. (엑셀 함수 Plus) Text 함수 사용법 - 원하는 서식(포맷, 날짜/시간, 소수점, 퍼센트, 시간, 요일)을 텍스로 만들어 보자 [엑셀 함수 강좌-플러스] - 002. (엑셀 함수 Plus) Tr
nextmeok.tistory.com
* 엑셀 관련 궁금하신 거나 어려운 점이 있으시면 자유롭게 질문을 해주세요.
* 가능한 도움을 드릴 수 있도록 하겠습니다.
* 저도 많이 알지는 못하지만 누구나 그렇듯 시작이란 게 있고 경험이란 게 다를 것 같습니다.
* 편하게 문의하시면 됩니다. 저도 모를 수 있다. 감안해 주시고.
- ILU, SH -
댓글