안녕하세요. 행아아빠입니다.
오랜만에 엑셀 내용을 작성해 보는데 오늘 내용도 많은 분들에게 도움이 될 수 있기를 바라겠습니다.
참고로 많은 분들이 급여계산 양식, 시간계산 (근로시간), 소득세, 4대보험등의 2022년도 양식을 요청해 주셨으나 직장을 다니는 직장에서의 본업과 개인적인 사정으로 2022년도 급여 계산 양식은 별도로 만들지 못했습니다.
2023년도 양식은 최대한 시간내어 많은 분들이 편하게 이용할 수 있는 양식으로 만들어서 업로드 해 보도록 노력해 보겠습니다.
오늘 알아볼 offset함수는 작성 방법이 헷갈릴 수 있지만 알아두면 굉장히 유용하게 활용 할 수 있는 내용으로 offset함수로 동적 범위를 만들기 위한 수식 작성 방법 지루한 내용일지라도 한번쯤은 확인해 보시길 바라겠습니다.
■ 목차.
1. offset 함수 기본 수식 작성 방법.
2. 범위 조건의 형식으로의 OFFSET함수 활용.
3. 유동적 범위, 동적 범위로의 offset함수 활용.
1. offset함수 기본 수식 작성 방법.
offset함수는 범위 함수라고 할 수 있을것 같습니다.
수많은 엑셀 함수들 중의 수식 구문중에서 범위를 지정해야 하는 경우가 있는데 이러한 범위가 결과값을 구해야 하는 조건에 따라 범위가 달라지거나 행 또는 열을 삭제하더라도 offset함수로 정해 놓은 범위에 따라 유동적으로 바뀌어 수식을 계산할 수 없는 #REF! 오류를 방지 함으로써 어렵게 작성해 놓은 수식을 다시 작성하는 일을 방지 할 수 있을것 같습니다.
offset함수의 기본 수식 구문은 =offset(기준셀, 행(아래 또는 위)로 몇 셀 움직일것인지, 열(좌,우)로 몇 셀 움직일 것인지, 범위의 높이, 범위의 넓이)로 수식을 작성 할 수 있습니다.
왼쪽에 무작위 번호를 입력한 표를 예시로 D3셀에 위치한 15번을 결과값으로 얻기 위해 offset함수로 수식을 작성해 보면 =OFFSET($A$1,2,3)으로 작성하게 되면 결과값으로 D3셀의 15번을 결과값으로 얻을 수 있습니다.
여기서 A1셀이 기준셀이기 때문에 A1을 기준으로 행 방향으로 2셀 이동 후 열 방향으로 3셀을 이동하라는 수식 구문이 되겠습니다.
만약, 기준셀을 B1으로 작성할 경우 D3셀의 값을 얻기 위한 수식은 =OFFSET($B$1,2,2)으로 작성해야 D3셀의 15번을 결과값으로 반환 시킬 수 있을것입니다.
기준셀을 정하고 기준셀로부터 행 방향으로 몇셀 이동하고, 열 방향으로 몇셀 이동할지로 수식 구문을 작성하고 범위의 높이와 넓이는 생략하게 되면 간단하게 결과값을 구할 수 있게 됩니다.
주의점으로 기준셀이 변하지 않고 고정되어 있어야 한다면 기준셀에 꼭 F4키로 수식을 어느곳에나 입력하더라도 기준셀이 변하지 않아야 한다는점을 주의해 주시면 되겠습니다.
과거 가로 세로 카테고리로 작성된 표 형식에서 표안에 있는 내용의 결과값을 불러오기 위한 수식은 index, match함수로 작성할 수 있는 수식 작성 방법에 대해서도 글을 작성한 적이 있는데 index, match 함수로 D3셀에 작성된 15번을 결과값으로 불러오기 위한 수식은 =INDEX($B$2:$H$11,MATCH(1037,$A$2:$A$11,0),MATCH(3,$B$1:$H$1,0)) 이렇게 작성 할 수 있습니다.
그런데 만약 3행 또는 D열을 삭제 하게 된다면 작성된 수식의 결과값은 어떻게 될까요?
offset함수의 결과값은 22가 될것이고, index, match함수로 작성된 수식의 결과값은 #N/A으로 에러값이 반환될것입니다.
offset함수는 행이나 열이 삭제 되더라도 기준셀로부터 이동해야할 셀만큼만 이동하여 결과값을 반환하기 때문에 표 안에 작성된 셀이 삭제 되더라도 결과값을 반환하게 될 수 있고, index, match함수로 작성한 수식은 match의 조건값을 찾을 행 또는 열이 삭제 되었기 때문에 에러값이 반환되는것입니다.
즉, offset함수의 수식을 작성하더라도 에러값이 나오는 경우는 기준셀이 삭제 되었을때 어느 셀로부터 행과 열로 이동해야 할지 모르기 때문에 기준셀은 반듯이 삭제 되지 않을 셀을 기준셀로 정해 주는것이 좋겠고, 경우에따라 기준셀이 이동되거나 유동적이여야 한다면 기준셀을 고정시키지 않을 수 있는 방법도 있겠지만 추후 이러한 예제가 생기게 되면 예제로 작성해 보도록 하겠습니다.
2. 범위 조건의 형식으로의 OFFSET함수 활용.
offset함수 어떻게 사용해야 할지 느낌이 오시나요?
아주 간단하게 표 형태에서 offset함수로 결과값을 반환시키기 위한 수식을 설명 드렸는데, offset함수를 유동적으로 움직이는 범위의 조건으로 활용할때 그 활용도가 가장 높다고 할 수 있습니다.
offset함수의 기본 수식을 설명 드리기 위해서 offset함수를 범위 함수로 사용하기 위해서는 높이와 넓이의 값을 입력해 주어야 합니다.
그럼 offset함수의 수식을 높이와 넓이의 구문까지 작성한 예로 수식을 작성해 볼텐데, offset함수를 동적 범위 함수로 활용하기 위해서는 단일 함수로의 사용은 불가하고 범위를 조건으로 하는 함수와 함께 사용되어야 합니다.
예를들어 특정 번호의 개수를 세기 위한 수식을 작성한다고 하면 개수를 세기 위한 함수는 count, countif, countifs등의 함수를 사용할 수 있겠는데 개인적으로는 countifs 다중조건의 개수 세기 함수를 자주 사용하기 때문에 countifs함수와 offset함수를 조건 범위로 작성해 보도록 하겠습니다.
J1셀에는 countifs함수의 조건 범위를 offset함수로 작성했고, J2셀에는 countifs함수의 조건 범위를 $B$2:$H$11까지로 직접 지정해서 작성했을때 i1셀에 작성한 번호의 개수를 결과값으로 반환하는 수식을 작성했습니다.
조건 범위를 offset함수로 지정한 수식과 직접 범위를 지정해줬을때의 결과값은 조회할 조건의 어떤 번호를 입력하더라도 결과값은 동일하게 나올수밖에 없을것입니다.
개수를 세기 위한 countifs함수의 수식은 =countifs(조건을 찾을 범위, 조건)으로 작성 할 수 있는데COUNTIFS(OFFSET($A$1,1,1,10,7),I1)의 수식을 살펴 보면 조건을 찾을 범위는 OFFSET($A$1,1,1,10,7)으로 기준셀 A1셀로부터 행으로 1셀 이동하고 열 방향으로 1셀 이동 한 후 높이 10셀, 넓이 7셀을 범위로 지정하라는 의미로 A1셀을 기준으로 행과 열 방향으로 각각 1셀을 이동하면 B2셀이 되고 B2셀로부터 높이 10셀과 넓이 7셀만큼 이동하게 되면 H11셀이 되는것으로 B2을 기준으로 7x10만큼의 범위로 B2셀부터 H11셀까지 범위로 지정하게 되는것입니다.
3. 유동적 범위, 동적 범위로의 offset함수 활용.
동적 범위를 만들기 위해서 조건을 찾기 위한 번호와 A열에 작성된 회차를 조건으로 조건 회차 이전의 회차에서만 조건 번호를 찾기 위한것으로 즉, 다중 조건에서의 표 범위 내에서 개수를 세우기 위한 수식을 작성해 보면 다음과 같이 작성 할 수 있습니다.
그림에서 i1셀과 i2셀이 각각 찾을 조건이라고 한다면 B2셀부터 H11셀의 범위 안에서 1037회 미만의 회차에서만 27번의 개수를 세워보면 몇개인가? 라는 결과값을 구하기 위한 수식은 =COUNTIFS(OFFSET($A$1,3,1,10,7),I1) 또는 COUNTIFS($B$4:$H$11,I1) 2가지 방법으로 변경해서 작성 할 수 있을 것입니다.
여기까지는 두가지 모두 찾을 조건의 범위를 수동으로 직접 변경 작성한 수식으로 별다른 차이는 없습니다.
OFFSET함수의 수식을 이제 조건에 맞게 자동으로 범위를 변경할 수 있는 수식으로 변경을 해주게 되면 더이상 조건의 찾을 범위가 변경이 되더라도 작성된 수식의 범위를 일일이 변경하지 않아도 되는것이겠죠.
=COUNTIFS(OFFSET($A$1,3,1,10,7),I1) 이렇게 작성한 수식은 사실 OFFSET함수를 재대로 활용하지 못하는것으로 조건 범위가 변경될때마다 또다시 범위를 직접 수정해줘야 하는 번거로움이 발생하고, 이럴 경우 차라리 COUNTIFS($B$4:$H$11,I1)의 수식으로 COUNTIFS함수의 기본 수식 작성으로 작성하는것이 효과적인 수식이라고 할 수 있을것 입니다.
그럼 OFFSET함수의 범위가 조건에 맞게 유동적 범위로 바뀌게 하는 수식을 작성해 보면 다음과 같이 작성 할 수 있겠습니다. COUNTIFS(OFFSET($A$1,MATCH($I$2,$A$2:$A$11,0)+1,1,10,7),I1)
OFFSET($A$1,MATCH($I$2,$A$2:$A$11,0)+1,1,10,7) 이렇게 수식을 변경해서 작성을 해 주게 되면 전체적인 범위 7x10의 범위를 변동이 없고 범위의 시작 위치를 조건에 맞는 회차 이전의 회차에서부터 시작되는 셀로 이동 시킬 수 있게 되는것을 해당 수식의 범위는 B4셀부터 H13셀까지로 바뀌어 1037회차 이전회차인 1036회차 이전에 27번의 개수를 결과값으로 반환 시킬 수 있겠습니다.
오늘은 OFFSET함수의 기본 수식 작성 이해와 동적 범위로 활용하기 위한 아주 간단한 예제를 만들어서 설명을 드려봤습니다. 아마 오늘 내용으로도 충분히 자신이 이제껏 작성하고자 했던 서식에 사용할 분도 계실것이고, 아직 모르겠다는분들도 계실 수 있습니다.
앞으로도 동적 범위에 대한 예제가 생각이 나거나 문의 주시는 내용을 토대로 OFFSET함수를 범위 함수, 동적 범위 함수로 활용할 수 있는 내용들이 있다면 함께 설명해 보는 글을 작성해 보도록 하겠습니다.
예제에서 OFFSET함수를 동적 범위 함수로 활용하기 위해서 COUNTIFS함수와 MATCH함수와 함께 작성 할 수 있는 수식 예제로 설명을 드렸는데 ROW함수, COLUMN함수와도 함께 사용할 수 있는 부분들이 너무 많기 때문에 자신이 알고 있는 범위와 관련된 함수와 수식들을 결합하여 사용하시면 원하는 결과값을 반환 시키는 서식을 만들 수 있을것입니다.
그럼 오늘 내용도 많은 분들에게 도움이 될 수 있는 내용이 되었길 바라겠습니다.
'엑셀' 카테고리의 다른 글
| 2023년 엑셀 급여 계산 서식과 수식 작성. (1) | 2022.12.06 |
|---|---|
| 엑셀 가계부 자동 계산 완성 서식 만드는 방법. (1) | 2022.11.29 |
| Vlookup함수를 대체하는 Index, Match함수 활용. (0) | 2022.07.10 |
| Left, Right, Mid함수의 결과값을 숫자형식으로 변환하는 방법. (0) | 2021.09.23 |
| Vlookup 엑셀 함수 결과값 여러개일 때 순서대로 표기 하는 수식 작성 방법. (0) | 2021.06.24 |
댓글