Excel에서 오프셋 기능 사용 [오프셋 – 일치 콤보, 동적 범위]

오늘은 엑셀의 OFFSET 기능을 소개하고자 합니다. 3가지 실제 사례와 함께.

먼저 수식 구문을 설명한 다음 OFFSET 함수를 사용하여 실생활에서 문제를 해결하는 방법에 대해 이야기하겠습니다.

소개

OFFSET 함수는 다른 셀(참조 셀) 또는 범위(참조 범위)에서 지정된 행 및 열 수만큼 떨어진 셀(대상 셀이라고 함) 또는 범위(대상 범위)에 대한 참조를 반환할 수 있습니다.

아래 그림은 OFFSET 함수를 사용하여 셀(왼쪽 부분) 또는 범위(오른쪽 부분)에 대한 참조를 반환하는 방법을 보여줍니다.

대상 세포와 참조 세포가 무엇인지 직관적으로 알 수 있습니다.

녹색으로 강조 표시된 셀은 대상 셀이고 노란색으로 강조 표시된 셀은 대상 범위로 구성됩니다.

파란색으로 강조 표시된 셀은 참조 셀입니다.

그림 1

Excel(구문)에서 OFFSET은 무엇을 의미합니까?

다음은 오프셋 함수의 구문입니다. OFFSET (참조, 행, 열, [높이], [너비])

참조	필수 사항입니다. 참조는 오프셋이 시작되는 셀 또는 셀 범위입니다. 셀 범위를 지정하는 경우 셀은 서로 인접해야 합니다.
행	필수 . 행 수(위 또는 아래, 참조 셀 또는 참조 범위의 왼쪽 상단 셀). 행 양수 또는 음수일 수 있습니다. 그림 1의 왼쪽 부분을 보면 함수를 OFFSET(C3, -1, -1)으로 변경하면 대상 셀이 B2가 됩니다. B2는 C3 위로 한 행입니다.
열	필수 사항입니다. 참조 셀 또는 참조 범위의 왼쪽 위 셀의 왼쪽 또는 오른쪽에 있는 열 수입니다. 행과 마찬가지로 인수, Cols 값 또한 긍정적이고 부정적일 수 있습니다. B4를 참조 셀로 설정하고 C3을 대상 셀로 설정하면 어떻게 OFFSET 함수를 작성할 수 있습니까? 정답은 오프셋(B4, -1, 1)입니다. 여기에서 Cols가 양수이고 C3이 B4의 오른쪽에 있는 한 열임을 알 수 있습니다.
높이	선택 사항. 대상이 범위인 경우에만 Height 인수를 사용하십시오. 대상 범위에 포함된 행 수를 알려줍니다. 높이는 양수여야 합니다. 그림 1의 오른쪽 부분에서 목표 범위에 두 개의 행이 있음을 알 수 있습니다. 따라서 이 경우 Height를 2로 설정합니다.
너비	선택 사항. 대상이 범위인 경우에만 Width 인수를 사용하십시오(그림 1의 오른쪽 부분 참조). 대상 범위에 포함된 열 수를 나타냅니다. 너비는 양수여야 합니다.

자, 이제 OFFSET 함수를 사용하여 실생활에서 문제를 해결하는 방법을 보여드리겠습니다.

사례 1:OFFSET 및 MATCH 함수를 결합하여 오른쪽에서 왼쪽으로 조회

VLOOKUP 기능으로만 왼쪽에서 오른쪽 조회를 수행할 수 있다는 것은 잘 알려져 있습니다.

검색할 값은 테이블 배열의 첫 번째 열에 있어야 합니다.

새 조회 값을 추가하려면 전체 테이블 범위를 오른쪽으로 한 열씩 이동해야 하고, 조회 값으로 다른 열을 사용하려면 데이터 구조를 변경해야 합니다.

그러나 OFFSET과 Match 기능을 결합하면 VLOOKUP 기능의 한계를 없앨 수 있습니다.

MATCH 함수는 무엇이며 조회를 수행하기 위해 OFFSET 함수와 Match 함수를 결합하는 방법은 무엇입니까?

음, Match 함수는 셀 범위에서 지정된 항목을 검색한 다음 범위에서 해당 항목의 상대 위치를 반환합니다.

그림 2.1의 범위 B3:B8을 예로 들어 보겠습니다.

수식 "=MATCH("USA", B3:B8, 0)"는 1을 반환합니다. USA가 범위의 첫 번째 항목이기 때문입니다(셀 B10 및 C10 참조).

다른 범위 C2:F2의 경우 "=MATCH (2015, C2:F2, 0)" 수식은 3 을 반환합니다. 2015년은 범위의 세 번째 항목이기 때문입니다(셀 B11 및 C11 참조).

OFFSET 기능으로 돌아갑니다.

B2 셀을 참조 셀로 설정하고 E3 셀을 대상 셀로 설정하면 OFFSET 수식을 어떻게 작성할 수 있습니까?

E3은 1입니다. B2 및 3 아래 행 열 오른쪽 B2.

따라서 수식은 "=OFFSET(B2, 1 , 3 )”. 빨간색으로 표시된 숫자를 자세히 보면 일치하는 것을 찾을 수 있나요?

이것이 질문에 대한 대답입니다 – OFFSET 함수와 Match 함수를 결합하는 방법 – Match 함수는 OFFSET 함수의 두 번째 또는 세 번째 인수 역할을 하기 위해 적용할 수 있습니다. (C13 셀 참조).

C14 셀은 VLOOKUP 함수를 사용하여 동일한 데이터를 검색하는 방법을 보여줍니다.

2015년 수익이 4 에 기록된다는 것을 알아야 합니다. VLOOKUP 함수를 작성하기 전에 테이블 배열 B2:F8의 열.

즉, VLOOKUP 함수를 사용할 때 데이터 구조에 대해 잘 알아야 합니다.

이것은 VLOOKUP의 또 다른 제한 사항입니다. 그러나 MATCH 함수를 OFFSET 함수의 인수로 사용하면 열 인덱스를 알 필요가 없습니다.

열이 많은 경우 매우 유용합니다.

그림 2.1

이제 더 복잡한 예를 살펴보겠습니다.

서로 다른 회사의 회사 이름, 연락처 이름 및 이메일 주소가 포함된 테이블이 있다고 가정합니다.

그리고 알려진 연락처 이름에서 회사 이름을 검색하거나 알려진 이메일 주소에서 연락처 이름을 얻으려고 합니다. 우리는 무엇을 할 수 있습니까?

그림 2.2 참조, 범위 B5:E8에는 회사 정보가 포함됩니다. 셀 C2와 셀 B3에 입력을 입력하면 빨간색 사각형의 수식을 사용하여 연락처 이름을 알고 있으면 회사 이름을 검색할 수 있습니다.

범위 D2:E4는 알려진 이메일 주소로 연락처 이름을 얻는 방법을 보여줍니다.

요약하면 이 두 가지 예는 오른쪽에서 왼쪽으로 조회를 수행할 수 있으며 검색 값을 맨 오른쪽 열에 배치할 필요가 없음을 보여줍니다. 테이블 배열의 모든 열에는 검색 값이 포함될 수 있습니다.

그림 2.2

사례 2:OFFSET 및 COUNT 함수를 결합한 계산 자동화

열에 새 숫자를 추가할 때마다 계산을 자동화하는 방법을 소개하기 전에 먼저 열의 마지막 숫자를 자동으로 반환하는 방법부터 시작하겠습니다.

인적 자원의 항목을 보여주는 아래 그림을 보십시오. B열의 마지막 숫자를 얻으려면 수식은 "=OFFSET(C2, 9 , 0)" OFFSET 기능을 적용하면

공식에서 9 키 번호입니다.

이 번호를 자동으로 반환할 수 있는 한 열의 마지막 번호를 자동으로 찾을 수 있습니다.

9 C열에 숫자가 포함된 셀의 수입니다.

COUNT 함수에 익숙하다면 COUNT 함수가 범위에 있는 숫자를 포함하는 셀의 수를 셀 수 있다는 것을 알 수 있습니다.

예를 들어 "=COUNT (C3:C11)" 수식은 C3에서 C11까지의 셀에 숫자가 포함된 셀의 수를 계산합니다.

우리의 경우 전체 열에 몇 개의 숫자가 있는지 알고 싶으므로 C 열의 모든 행을 포함하는 C:C와 같은 참조를 사용해야 합니다.

G4 및 H4 셀을 보십시오. "=COUNT(C:C)"에서 반환된 숫자는 9와 정확히 같습니다. .

따라서 위의 OFFSET 함수에서 9를 COUNT(C:C)로 바꾸면 새로운 공식 “=OFFSET (C2, COUNT(C:C) , 0)”(H5 셀).

반환되는 숫자는 정확히 C 열의 마지막 숫자인 87000입니다.

이제 자동 계산으로 넘어갑니다. C열에 있는 모든 숫자의 합계를 원한다고 가정합니다.

수식은 "=SUM(오프셋(C2, 1, 0, 9 , 1))” SUM을 OFFSET과 함께 사용하면

9 C3:C11 범위의 총 행 수이고 C 열의 숫자가 포함된 총 셀 수입니다.

따라서 "=SUM (OFFSET (C2,1, 0, COUNT (C:C), 1))"과 같은 새로운 방식으로 수식을 작성할 수 있습니다.

G10 및 H10 셀을 보면 이 9명의 직원에 대한 총 급여는 $521,700입니다.

이제 C12 셀에 $34,000와 같은 숫자를 입력하면 G5 및 G10 셀의 숫자가 각각 $34,000 및 $555,700로 변경됩니다.

이것은 셀 G5 또는 G10의 수식을 업데이트할 필요가 없기 때문에 자동화라고 부릅니다.

COUNT 함수는 숫자가 포함된 셀의 개수만 반환하므로 COUNT 함수를 사용할 때 주의해야 합니다.

예를 들어 "=COUNT (B:B)"는 B열에 숫자가 포함된 셀이 없기 때문에 9 대신 0을 반환합니다(셀 G3 및 H3 참조).

D열에는 숫자가 포함된 10개의 셀이 포함되어 있으며 "COUNT(D:D)"에서 반환된 숫자도 10입니다.

그러나 C 열에 대해 수행한 것처럼 D 열의 마지막 숫자를 검색하려면 숫자 0을 얻습니다(G8 및 H8 셀 참조).

분명히 0은 우리가 원하는 것이 아닙니다. 뭐가 문제 야? D13 셀은 D2 셀에서 10행이 아니라 11행 떨어져 있습니다.

이는 공식 "=OFFSET(D2, COUNT(D:D) + 1 , 0)" 셀 G7.

요약하자면, COUNT 함수와 OFFSET 함수를 함께 사용하여 계산의 자동화를 가능하게 하려면 숫자가 서로 인접해야 합니다.

그림 3

사례 3:OFFSET 함수를 사용하여 동적 범위 만들기

회사의 월간 단위 판매를 차트로 만들고 그림 4.1에 현재 데이터와 현재 데이터를 기반으로 만든 차트가 표시된다고 가정합니다.

매월 가장 최근 달의 판매량이 C열의 마지막 숫자 아래에 추가됩니다.

차트를 자동으로 업데이트하는 쉬운 방법이 있습니까?

차트 업데이트의 핵심은 OFFSET 함수를 사용하여 Units Sold 열에 대한 동적 범위 이름을 만드는 것입니다.

새 데이터가 입력되면 단위 판매의 동적 범위에 모든 판매 데이터가 자동으로 포함됩니다.

그림 4.1

동적 범위를 만들려면 수식을 클릭합니다. 탭을 클릭한 다음 이름 관리자를 선택합니다. 또는 이름 정의 .

새 이름 아래 이름 정의를 클릭하면 대화 상자가 표시됩니다. .

이름 관리자를 선택하는 경우 , 새로 만들기도 클릭해야 합니다. 아래 새 이름을 만들려면 대화 상자가 나타납니다.

그림 4.2

"이름: " 입력 상자에 동적 범위 이름을 입력해야 합니다. 그리고 "참조:" 입력 상자에 "=OFFSET (Figure4!$C$2, 1, 0, COUNT (!$C:$C), 1)"을 입력하면 Units Sold 값에 따라 값의 동적 범위가 생성됩니다. C열에 입력했습니다.

기본적으로 이름은 전체 통합 문서에 적용되며 통합 문서 내에서 고유해야 합니다.

그러나 범위를 특정 시트로 제한하고자 합니다.

따라서 여기 "범위: " 입력 상자. 확인을 클릭한 후 , 다이내믹 레인지가 생성됩니다.

새 데이터가 입력되면 모든 판매 데이터가 자동으로 포함됩니다.

이제 차트의 아무 지점이나 마우스 오른쪽 버튼으로 클릭한 다음 "데이터 선택"을 선택하십시오.