Excel에서 OFFSET 함수를 사용하는 방법을 모를 수도 있습니다.

Microsoft Excel의 OFFSET 기능이 셀 참조뿐만 아니라 다른 많은 작업에도 도움이 될 수 있다는 것을 알고 계셨습니까 ? 다음은 여러분이 알지 못할 수도 있는 Excel에서 OFFSET을 사용하는 독특한 방법 입니다 .

Microsoft Excel의 OFFSET 기능은 유연하고 강력한 도구입니다. 불행히도 이 Excel 기능은 복잡성과 유사한 기능의 수로 인해 활용도가 낮습니다.

이 상대적으로 간단한 기능은 사용자가 시간과 노력을 절약하는 데 도움이 됩니다. 잘 정렬된 마스터 데이터 세트에서 정확한 데이터 포인트를 얻는 것이 단순화됩니다. 또한 상세한 보고서, 대화형 시각화 및 복잡한 대시보드를 간단하게 생성할 수 있습니다.

맞춤형 크기의 데이터 세트 만들기

Excel에서 OFFSET 함수의 가장 일반적인 용도 중 하나는 크기 조정 가능한 데이터 세트를 수집하는 것입니다. 대규모 데이터 세트의 경우 주어진 시간에 데이터의 작은 부분만 고려하고 싶을 수도 있습니다.

예를 들어, 직원 판매 총액에 대한 자동화된 보고서를 제공하려면 특정 주에 대한 총액을 가져와야 합니다.

각 합계에 대해 서로 다른 SUM 함수를 작성하는 것은 매우 쉽습니다. 그러나 OFFSET 기능을 사용하면 사용하기 쉽고 재사용도 더 빨라집니다.

먼저 B6 셀에 시작하려는 날짜를 입력하세요. 그런 다음 B7에서 보고서를 실행하려는 일수를 입력합니다. 다음으로, 간단한 SUM 및 AVERAGE 함수를 사용하여 선택한 기간 동안 각 직원에 대해 더 많은 데이터를 가져옵니다.

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

시작 날짜나 시간을 조정하는 것은 셀을 편집하는 것만큼 간단하지만 데이터 그룹을 훨씬 더 쉽게 끌어서 크기를 조정할 수 있습니다.

맞춤형 보고서 생성 가능

위와 일한 방법을 사용하면 매우 쉽게 변수를 사용자 정의하여 완전한 보고서를 만들 수 있습니다. OFFSET 함수의 첫 번째 인수가 다른 시트를 참조하도록 만들 수 있습니다. 이를 통해 데이터 입력과 보고를 분리할 수 있습니다.

위 작업을 사용하면 선택한 데이터 조각을 한 워크시트에서 다른 워크시트로 끌 수도 있습니다. 또한 현재 보고서와 관련된 정보만 표시할 수 있습니다. 이전과 동일한 데이터를 사용하여 일일 합계를 표시하는 보고서를 설정하는 것은 매우 간단합니다. 관련 없는 날짜에 대한 데이터를 검색하지 않고도 이 작업을 수행할 수 있습니다.

셀 B5에 있는 다음 오프셋 함수는 B2에 지정된 일수를 검색합니다. B1에서 선택한 주부터 시작되며 해당 기간의 데이터만 표시됩니다.

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

B1 및 B2의 항목을 제한하고 데이터 유효성 검사를 통해 사용 가능한 주를 Excel에서 드롭다운 목록으로 만들 수 있습니다.

스프레드시트 페이지 매김

OFFSET 기능을 사용하면 Excel 개발자 탭 사용과 관련된 작업인 간단한 스프레드시트 페이지 매김도 가능합니다. 이를 통해 대규모 데이터 그룹을 더 간단하고 효과적으로 처리할 수 있습니다.

최대 30,000행의 데이터가 포함된 스프레드시트로 작업할 때 모든 데이터를 한 번에 살펴보기 어려울 수 있습니다. 1부터 300까지의 숫자 범위를 허용하는 드롭다운 메뉴를 만들어 페이지당 100개의 결과로 페이지별로 데이터를 빠르게 분리할 수 있습니다.

OFFSET 함수의 기준점은 항상 최상위 데이터 셀에 위치합니다. 그러나 두 번째 인수는 수직 오프셋입니다. 이 값은 선택한 페이지 번호에서 100을 뺀 것과 같습니다. 오프셋은 가로이며 세 번째 인수는 0으로 설정할 수 있습니다.

네 번째 인수는 100으로 설정되고, 다섯 번째 인수는 데이터 세트의 열 수입니다. 이 방법을 사용하면 데이터 테이블을 매우 쉽게 분할하고 관리할 수 있습니다.

다음은 페이지를 매긴 데이터의 예입니다. 한 시트에는 센서에서 기록된 500개의 정보가 포함되어 있고 다른 시트에는 페이지가 매겨진 보고서가 포함되어 있습니다.

보고서는 셀 B1의 데이터 유효성 검사를 사용하여 내부에 페이지가 포함된 드롭다운 메뉴를 만듭니다. A3 셀의 다음 식은 보고서 본문을 만듭니다.

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

위에는 Excel에서 오프셋 기능을 사용하는 몇 가지 방법이 있지만 , 특히 많은 양의 데이터를 처리해야 할 때 매우 유용합니다.

이 기사가 당신에게 도움이 되기를 바랍니다.