엑셀 함수학습: 동적통계의 왕 OFFSET()에 대해 이야기해보자

이번 글에서는 '동적통계의 제왕'이라 불리는 OFFSET 함수를 소개하겠습니다! OFFSET 기능은 드롭다운 메뉴, 동적 차트, 동적 참조 및 기타 작업에서 대체할 수 없는 역할을 하는 매우 실용적인 기능입니다. 엑셀 테이블의 효율성은 상당부분 OFFSET에서 나온다고 해도 과언이 아닙니다.

【서문】

OFFSET 함수는 엑셀 함수 사용자의 고급화 여부를 판단하는 중요한 함수 중 하나입니다. 실제 업무에서 업무 중 데이터 파일을 체계적이고 자동으로 모델링해야 한다면 이 기능을 필연적으로 사용하게 될 것입니다.

【함수 및 구문】

OFFSET 함수의 기능은 지정된 참조를 참조 시스템으로 사용하고 주어진 오프셋을 통해 새로운 참조를 반환하는 것입니다.

구문: ​​OFFSET(reference,rows,cols,[height],[width])OFFSET(reference,rows,cols,[height],[width])

• reference   是原基础点

• rows     是要偏移的行数，正数向下，负数向上，零不变。

• cols     是要偏移的列数，正数向右，负数向左，零不变。

• [height]    是基础点偏移后，纵向扩展几行，正数向下扩展，负数向上扩展。

• [width]   是基础点偏移后，横向扩展几列，正数向右扩展，负数向左扩展。

如果不使用第四个和第五个参数（但不可以为零），则新引用的区域和原基础点大小一致。

原基础点可以是一个单元格，也可以是一个区域。

刚刚接触OFFSET函数的同学，想要理解上面这些参数，可能存在一定的难度，那么我们用一个图解的方式来给大家说明一下吧。

相信大家看这个图都花费了不少时间吧。我们可以先按照上图的指引，将数据填入OFFSET函数中，实际操作一下，来看看是否和新区域的地址一致呢？

先来测试下第一个例子，看看正数为参量的运行结果：

通过验算，对黄色 “新区域”中的值进行求和，等于256，与单元格C15中的值一致，结果正确。如果同学们想模拟这个数据，也可以选中C15单元格，再通过工具栏中“公式——公式审核——公式求值”的功能，就能更加直观的看到OFFSET的返回值。（在函数中使用F9也是可以的，选中公式中OFFSET的函数部分，再按F9即可，这里就不多讲了。）

再来测试下第二个例子，看看负数为参量的运行结果：

大家可以用“公式求值”的方式，自己测试一下，看看OFFSET函数区域的返回值。

那么知道了OFFSET的基本运行原理之后，它在实际的工作中就可以帮助我们进行很多的操作和运算，而且有了这个函数的参与，可以实现excel中很多自动化的效果。下面让我们一起来看看OFFSET函数在实际操作中起到的强大作用！

一、初级常规用法

作为其他函数的区域引用，应该是OFFSET函数最基础的用途了。OFFSET函数并不是移动了单元格区域，而是返回了一个偏移扩展后的区域地址。因此所有将引用区域作为参数的函数，都可以利用OFFSET函数的返回值，例如我们上面的例子Sum（OFFSET（）），再比如下面这个例子：

函数原理和上面的用法相同，我们就不再赘述了，依然是利用OFFSET函数返回的区域作为MAX函数的参数。

二、进阶常规用法

绝技①：模拟转置TRANSPOSE函数

我们在使用TRANSPOSE函数前，需要先选择相应大小的转置区域，而且还需用Ctrl+Shift+Enter三键结束公式，比较繁琐。

这里我们可以使用OFFSET函数来模拟这个转置的效果，如上图所示。

A11单元格函数：

=OFFSET($A,COLUMN()-1,ROW()-11)

• reference는 원본 기본 포인트

• rows는 오프셋할 행 수입니다. 양수는 내려가고 음수는 올라가며 0은 변경되지 않습니다. 🎜
• 🎜cols는 오프셋할 열 수이며 양수는 오른쪽, 음수는 왼쪽, 0은 변경되지 않은 상태로 유지됩니다. 🎜
• 🎜[높이]는 기준점이 오프셋된 후 몇 줄의 수직 확장이며, 양수는 아래쪽으로, 음수는 위쪽으로 확장됩니다. 🎜
• 🎜[너비]는 기본점이 오프셋된 후 여러 열의 수평 확장입니다. 양수는 오른쪽으로 확장되고 음수는 왼쪽으로 확장됩니다. 🎜

🎜네 번째와 다섯 번째 매개변수를 사용하지 않는 경우(0일 수는 없음) 새로 참조되는 영역은 원래 기본점과 동일한 크기가 됩니다. 🎜🎜원래의 기본점은 셀이 될 수도 있고 영역이 될 수도 있습니다. 🎜🎜OFFSET 기능을 처음 접한 학생들의 경우 위의 매개변수를 이해하기 어려울 수 있으니 도표를 통해 설명해 보겠습니다. 🎜🎜🎜🎜나는 당신을 믿습니다 읽을 수 있어요 이 사진은 시간이 많이 걸렸어요. 먼저 위 그림의 가이드라인에 따라 OFFSET 함수에 데이터를 채워 넣고, 실제로 새로운 지역의 주소와 일치하는지 확인해 볼까요? 🎜🎜먼저 첫 번째 예를 테스트하고 양수 매개변수의 결과를 살펴보겠습니다. 🎜🎜🎜🎜확인 후 노란색 "새 영역"의 값을 합산하면 256과 같으며 이는 C15 셀의 값과 일치하며 결과는 맞다. 학생들이 이 데이터를 시뮬레이션하려는 경우 C15 셀을 선택한 다음 도구 모음에서 "수식 - 수식 검토 - 수식 평가" 기능을 사용하여 OFFSET의 반환 값을 보다 직관적으로 확인할 수도 있습니다. (함수에서 F9를 사용하는 것도 가능합니다. 수식에서 OFFSET의 함수 부분을 선택하고 F9를 누릅니다. 여기서는 자세히 설명하지 않겠습니다.) 🎜🎜🎜🎜두 번째 예를 다시 테스트하여 음수를 매개변수로 사용한 경우의 결과를 살펴보겠습니다.🎜 🎜🎜🎜다음을 사용할 수 있습니다. "수식 평가" 메서드를 직접 테스트하고 OFFSET 함수 영역의 반환 값을 확인하세요. 🎜🎜OFFSET의 기본 작동 원리를 알면 실제 업무에서 많은 연산과 계산을 수행하는데 도움이 되며, 이 기능을 활용하면 엑셀에서 많은 자동화 효과를 얻을 수 있습니다. 실제 연산에서 OFFSET 함수의 강력한 역할을 살펴보겠습니다! 🎜🎜🎜1. 기본 일반 사용법🎜🎜🎜다른 기능에 대한 지역별 참고로 OFFSET 기능의 가장 기본적인 사용법이 되어야 합니다. OFFSET 함수는 셀 범위를 이동하지 않고 오프셋 확장된 범위 주소를 반환합니다. 따라서 참조 영역을 매개변수로 사용하는 모든 함수는 Sum(OFFSET ()) 위의 예나 다음 예와 같이 OFFSET 함수의 반환 값을 사용할 수 있습니다. 🎜🎜🎜🎜함수 원리는 위 사용법과 동일하므로 여기서는 OFFSET 함수에 의해 반환된 영역을 MAX 함수의 매개 변수로 사용합니다. 🎜🎜🎜2. 고급 공통 사용법🎜🎜🎜🎜트릭 ①: 전치 TRANSPOSE 기능 시뮬레이션🎜🎜🎜🎜🎜 TRANSPOSE 기능을 사용하기 전에 먼저 해당 크기를 선택해야 합니다. 영역을 전치하고 수식을 끝내려면 Ctrl+Shift+Enter를 사용해야 하는데 이는 상당히 번거롭습니다. 🎜🎜여기서 위 그림과 같이 OFFSET 기능을 사용하여 이 전치 효과를 시뮬레이션할 수 있습니다. 🎜🎜A11 셀 함수: 🎜🎜=OFFSET($A$1,COLUMN()-1,ROW()-11)🎜🎜함수 분석: 🎜

데이터를 바꾸는 것은 실제로 "행에서 열로", "열에서 행으로"의 과정입니다. 더 구체적으로 말하면 행 번호와 열 번호를 교환하는 문제입니다. 원본 데이터의 첫 번째 열인 "이름" 열은 전치 후 새 영역의 첫 번째 행이 됩니다. 같은 방법으로 "이름" 열에 있는 각 행의 행 번호가 전치된 열 번호가 됩니다. OFFSET을 사용하는 원리는 오프셋 값을 취할 때 행과 열 번호의 인용 범위를 변경하는 것입니다.

★ 예를 들어 셀 A11에서 COLUMN()=1, 1-1=0이면 OFFSET의 두 번째 매개변수는 0입니다. 이는 원래 기준점의 행 수가 오프셋이 아님을 나타냅니다. OFFSET은 행 오프셋을 나타냅니다. 익숙하지 않은 경우 이전 내용을 읽어보세요!). ROW()=11, 11-11=0이고 OFFSET의 세 번째 매개변수가 0으로 열 개수가 오프셋되지 않음을 나타내므로 원래 기준점 A1 셀의 값이 인용됩니다.

★★ 함수를 오른쪽으로 당겨서 B11 셀, COLUMN()=2, 2-1=1을 채우면 OFFSET의 두 번째 매개변수가 1이 되어 원래 기준점의 행 수가 이동되었음을 나타냅니다. 한 위치 아래로 내려갑니다. ROW()=11, 11-11=0이고 OFFSET의 세 번째 매개변수가 0으로 열 개수가 오프셋되지 않음을 나타내므로 셀 B11은 기준점 A1을 아래쪽으로 이동한 후 셀 A2의 값을 참조합니다.

★★★ 셀 A11의 함수를 당겨서 채웁니다. 셀 A12에서는 COLUMN()=1, 1-1=0이며 행 수는 오프셋되지 않습니다. ROW()=12, 12-11=1, OFFSET의 세 번째 매개변수는 1입니다. 이는 열 번호가 기준점 A1에서 오른쪽으로 한 위치 오프셋되었음을 나타내며 셀 B1의 값을 참조합니다. 수식의 A1은 모든 셀이 A1을 기반으로 하므로 절대 참조 사용입니다.

비유적으로 마우스를 사용하여 수식을 채울 때 COLUMN 및 ROW 함수를 사용하여 각 셀의 오프셋을 찾는 데 도움을 주어 전치 효과를 얻습니다.

Tips②: Vlookup 함수의 역방향 질의 기능 시뮬레이션

VLOOKUP 기능의 역방향 질의는 대부분 배열의 도움으로 이루어지는데, 배열 때문에 데이터 양이 많을 때에는, 기능이 정지될 수 있으므로 많은 학생들이 대신 INDEX 기능을 사용하게 될 것입니다. 오늘은 이러한 유형의 문제를 해결하기 위해 OFFSET 기능을 사용하여 모든 사람의 지식을 풍부하게 해보겠습니다.

C12 셀 기능:

=OFFSET($A$1,MATCH("D2568",$B$2:$B$7,0),)=OFFSET($A,MATCH("D2568",$B:$B,0),)

函数解析：

我们以单元格A1作为原基础点，需要返回的值与原基础点在同一列，所以我们只需要考虑OFFSET函数的行偏移量，不用考虑列偏移量。因为员工编号一般都是具有唯一性的值，所以我们采用MATCH函数得到编号“D2568”在区域B2:B7中的序号，返回值4作为OFFSET函数的行偏移量，带入到OFFSET函数中，=OFFSET($A,4,)。列偏移省略默认为0，扩展宽度和扩展高度省略默认为1 （即一个单元格），是不是就是A5单元格啦！

绝技③：数据重置升级版——重排数据结构

在F2:H2区域输入公式后，下拉填充数据，就得到了右面的一维数据表。这种重排数据的问题，在实际工作中应该不少见吧！那么同学们会选择什么方法解决呢？作者反而觉得OFFSET函数的思路更加的简洁清晰。

函数解析：

第一步：得到连续出现的姓名

F2单元格函数：

=OFFSET($A,INT((ROW(F1)-1)/3)+1,)

因为科目一共有三个，所以可以确定同一个姓名需要出现三次，那么当我们下拉F2单元格填充函数的时候，就要保证OFFSET函数的行偏移量每3个单元格的参数值都是一样的。这里就需要有一个“除数取整”的数学思维了，我们列个图来辅助说明：

从图中我们可以看出一组序号，通过INT((序号-1)/3)+1的转换后，就可以得到右侧的序列（如果有4个科目，那就把3改成4，依此类推）。将这个序列号放入OFFSET函数的第二参数，作为行偏移的标准，就可以得到我们姓名列的效果了。

第二步：给同一个人分配不同的科目

G2单元格函数：

=OFFSET($A,,MOD(ROW(G1)-1,3)+1)

기능 분석:

Cell을 사용합니다. A1은 원래 기준점 역할을 하고 반환값은 원래 기준점과 동일한 열에 있어야 하므로 열 오프셋이 아닌 OFFSET 함수의 행 오프셋만 고려하면 됩니다. 직원 번호는 일반적으로 고유한 값이므로 MATCH 함수를 사용하여 B2:B7 영역에서 "D2568"이라는 일련 번호를 가져옵니다. 반환 값 4는 OFFSET 함수의 행 오프셋으로 사용되며 OFFSET 함수 =OFFSET($A$1,4,). 열 오프셋을 생략하면 기본값은 0이고, 확장 너비와 확장 높이를 생략하면 기본값은 1(즉, 셀 1개)이 되는 것입니다.

스틱 ③: 데이터 재설정 업그레이드 버전 - 데이터 구조 재정렬

🎜F2:H2 영역에 수식을 입력한 후 아래로 당겨서 데이터를 채우면 오른쪽에 1차원 데이터 표가 나옵니다. 이런 종류의 데이터 재배열 문제는 실제 업무에서 드물지 않을 것입니다! 그렇다면 학생들은 이를 해결하기 위해 어떤 방법을 선택할 것인가? 오히려 저자는 OFFSET 함수의 개념이 더 간결하고 명확하다고 느낀다. 🎜🎜함수 분석: 🎜🎜1단계: 연속된 이름 가져오기 🎜🎜F2 셀 함수: 🎜🎜=OFFSET($A$1,INT((ROW(F1)-1)/3)+1 ,) code>🎜🎜총 3개의 주제가 있기 때문에 동일한 이름이 3번 나타나야 한다고 판단할 수 있습니다. 그러면 F2 셀 채우기 함수를 드롭다운할 때 OFFSET 함수의 행 오프셋이 모든 것을 보장해야 합니다. 3 셀 매개변수 값은 모두 동일합니다. 여기서는 "제수 반올림"에 대한 수학적 사고가 필요합니다. 설명을 돕기 위해 그림을 나열해 보겠습니다. 🎜🎜<img style="max-width:90%" style="max-width:90%" src="https://img.php.cn/%20upload%20/article/000/000/024/82112bb5dc6d295ab311d81e75506d49-8.png" alt="엑셀 함수학습: 동적통계의 왕 OFFSET()에 대해 이야기해보자" >🎜🎜그림에서 일련번호를 볼 수 있습니다. INT((일련번호-1)/3)+1로 변환한 후 다음을 수행할 수 있습니다. 오른쪽의 순서를 구합니다(과목이 4개인 경우 3을 4로 변경하는 등). 이 일련번호를 OFFSET 함수의 두 번째 매개변수에 행 오프셋의 기준으로 넣으면 이름 열의 효과를 얻을 수 있습니다. 🎜🎜2단계: 같은 사람에게 다른 과목 할당🎜🎜G2 셀 기능: 🎜🎜<code>=OFFSET($A$1,,MOD(ROW(G1)-1,3)+1)🎜 🎜F열의 각 이름이 3번 나타나기 때문에 이는 중국어, 수학, 영어의 세 과목을 순차적이고 순환적으로 나열해야 한다고 결정합니다. 첫 번째 단계와 동일한 아이디어로 "제수 계산" "이상"을 사용합니다. 효과를 달성하기 위해 수학적 사고. 🎜🎜🎜🎜🎜위 그림과 같이 일련번호를 MOD 함수로 변환하여 순차적이고 순환적으로 나열된 일련번호를 얻습니다. 이 일련번호를 OFFSET 함수의 세 번째 매개변수 열 오프셋으로 사용하면 원본 데이터의 계정 내용을 순차적, 순환적으로 파생할 수 있습니다. 🎜🎜3단계: 이름과 과목을 통해 INDEX 함수를 시뮬레이션하여 원본 데이터에서 성적 도출🎜

H2 셀 기능:

=OFFSET($A$1,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)) code ><code>=OFFSET($A,MATCH(F2,$A:$A,0),MATCH(G2,$B:$D,0))

分别用MATCH函数，得到数据在相关区域中所对应的序号，作为OFFSET的偏移量，分别放入第二、三参数中。从基准点A1单元格偏移后的单元格，就是我们需要的成绩值。

通过上面的内容，我们不难发现OFFSET函数，往往都是和MATCH函数连用。因为Match函数可以找到关键字在一个数列中的序号，所以我们经常利用这个函数来确定OFFSET函数的偏移量。

三、高阶应用的思路

（动态报表模板的原型）

我们使用Excel是为了快速地统计分析数据，快速地提取出我们需要的内容。现在假设以下两个场景：

场景一：

领导安排了工作，统计某季度的销售数据，我们马上行动，用函数快速的制作报表；

场景二：

领导安排了工作，因为每季度都需要统计销售数据，所以我们早就提前制作了模板，至于什么时候给出报表，就随我们的便了。切记，不要让“中层领导”知道你的工作效率很高。

两个场景，你会选择哪种处理方式呢？作者希望是第二个。

思路决定了我们制表的格局，这是一个简单的案例，当数据源被修改后，相对应的季度数据也会自动做出调整。在复杂的模版中并不是所有的位置都会使用OFFSET函数，但对于动态引用数据区域的需求，用OFFSET函数来处理是绝对不会错的。

四、典型用法举例

绝技4：制作动态下拉菜单

在数据建模的过程中，我们经常会使用到下拉菜单（或者是组合框控件）。为了确保下拉内容的唯一性，我们会使用INDEX+SMALL+IF+ROW的“万金油”函数来去重提取数列中的数据。还记得我们在上篇讲到的OFFSET函数替代INDEX函数的例子吗？所以说，如果OFFSET函数可以代替Index函数使用的话，那么OFFSET函数同样也可以实现“万金油”的过程。下面我们就一起来看看复杂的“下拉菜单”的制作过程。

步骤一：使用OFFSET函数去重提取唯一值的 “万金油”公式

这个公式比较长，列出如下：

D2单元格函数：

=IFERROR(OFFSET($A,SMALL(IF(ROW($A:$A)-1=MATCH($A:$A,$A:$A,0),ROW(:),9^9),<br>ROW(D1)),),"")

万金油公式不是我们今天要讲的主题，就不展开讲了。重要就是为了让大家知道OFFSET函数也是可以达到这样去重的效果。

步骤二：在名称管理器中使用OFFSET函数，建立数据源

我们可以用Ctrl+F3组合键，打开名称管理器窗口，然后新建名称，名称设置为“区域”，引用位置为“D2:D15”，如下图所示：

然后选择G1单元格，按Alt+D+L组合键可以打开数据验证设置框，在允许中选择“序列”，在来源中输入“=区域”，如下图所示：

点击确定按钮，那么我们G1单元格的下拉菜单就建立好了。但是问题也来了，我们会发现有好多的空选项，这不是我们需要的。

有的同学会说，名称管理器中选择D2:D5就可以了。是的，但是如果我们A列的区域中出现了新的数据，那下拉菜单中的数据可就少了，所以此时我们依然使用OFFSET函数来处理这个问题。

更改名称管理器中，“区域”的引用位置：

=OFFSET(动态下拉菜单!$D,1,,COUNTA(动态下拉菜单!$D:$D)-COUNTBLANK(动态下拉菜单!$D:$D),1)

MATCH 함수를 사용하여 해당 영역의 데이터에 해당하는 시퀀스 번호를 구하며, 이는 OFFSET의 오프셋으로 사용되며 각각 두 번째와 세 번째 매개변수에 입력됩니다. 참조점 A1 셀로부터의 셀 오프셋이 우리에게 필요한 등급 값입니다. 🎜🎜위 내용을 보면 OFFSET 함수가 MATCH 함수와 함께 사용되는 경우가 많다는 것을 쉽게 알 수 있습니다. Match 함수는 배열에서 키워드의 시퀀스 번호를 찾을 수 있기 때문에 이 함수를 사용하여 OFFSET 함수의 오프셋을 결정하는 경우가 많습니다. 🎜🎜3. 고급 애플리케이션을 위한 아이디어🎜🎜(동적 보고서 템플릿 프로토타입) 🎜🎜빠른 작업을 위해 Excel을 사용합니다. 지리통계학적으로 데이터를 분석하고 필요한 정보를 신속하게 추출합니다. 이제 다음 두 가지 시나리오를 가정해 보겠습니다. 🎜🎜시나리오 1:🎜🎜 리더가 특정 분기의 판매 데이터를 계산하는 작업을 준비했습니다. 우리는 즉시 조치를 취하고 보고서를 빠르게 생성하는 기능을 사용합니다. 시나리오 2:🎜🎜판매 데이터를 분기마다 수집해야 하기 때문에 리더가 작업을 준비했습니다. 그래서 미리 템플릿을 만들었고, 언제 발행할지는 우리에게 달려 있습니다. 보고서. 당신이 매우 생산적이라는 사실을 "중간 리더"에게 알리지 마십시오. 🎜🎜두 가지 시나리오 중 어떤 접근 방식을 선택하시겠습니까? 저자는 그것이 두 번째가 되기를 바란다. 🎜🎜🎜🎜생각하기 이는 표 형식을 결정하는 간단한 사례입니다. 데이터 소스가 수정되면 해당 분기별 데이터가 자동으로 조정됩니다. 복잡한 템플릿의 모든 위치에서 OFFSET 함수를 사용하는 것은 아니지만 데이터 영역을 동적으로 참조해야 하는 경우 OFFSET 함수를 사용하여 처리하는 것이 절대적으로 옳습니다. 🎜🎜4. 일반적인 사용 예🎜🎜스턴트 4: 동적 드롭다운 메뉴 만들기 🎜🎜데이터 모델링 과정에서 우리는 드롭다운 메뉴(또는 콤보 상자 컨트롤)를 자주 사용합니다. 드롭다운 콘텐츠의 고유성을 보장하기 위해 INDEX+SMALL+IF+ROW의 "Taiwan Balm" 기능을 사용하여 배열에서 중복 데이터를 추출하겠습니다. 이전 기사에서 이야기한 INDEX 함수를 OFFSET 함수로 대체한 예를 아직도 기억하시나요? 따라서 Index 함수 대신 OFFSET 함수를 사용할 수 있는 경우 OFFSET 함수를 사용하여 "모든 경우에 적용되는" 프로세스를 달성할 수도 있습니다. 복잡한 '드롭다운 메뉴'의 제작과정을 살펴보자. 🎜🎜1단계: OFFSET 기능을 사용하여 독특한 "대만 밤" 공식 추출🎜🎜🎜🎜이 수식은 비교적 길며 다음과 같이 나열됩니다. 🎜🎜D2 셀 함수: 🎜🎜=IFERROR(OFFSET($A$1,SMALL(IF( 행($A$2:$A$27)-1=MATCH($A$2:$A$27,$A$2:$A$27,0),ROW($1:$20),9^9),<br> ROW (D1)),),"")🎜🎜타이거밤 포뮬러는 오늘 다룰 주제가 아니므로 다루지 않겠습니다. 중요한 것은 OFFSET 기능으로도 이러한 복제 효과를 얻을 수 있다는 사실을 모든 사람에게 알리는 것입니다. 🎜🎜2단계: 이름 관리자의 OFFSET 기능을 사용하여 데이터 소스 설정🎜🎜Ctrl+F3 키 조합을 사용하여 이름 관리자 창을 연 다음 새 이름을 만들고 이름을 "Area"로 설정한 다음 아래와 같이 "D2 :D15"에 대한 참조 위치: 🎜🎜🎜🎜그런 다음 G1 셀을 선택하고 Alt+D+L을 눌러 데이터 유효성 검사 설정 상자를 열고 허용에서 "순서"를 선택한 다음 소스에 "=Area"를 입력합니다. 아래 그림: 🎜 🎜🎜🎜 확인 버튼을 클릭하면 G1 셀의 드롭다운 메뉴가 생성됩니다. 그러나 문제도 발생하는데, 이는 우리에게 필요한 것이 아닙니다. 🎜🎜🎜🎜어떤 학생들은 제가 이름 관리자에서 D2:D5를 선택하면 됩니다. 네, 하지만 A열 영역에 새로운 데이터가 나타나면 드롭다운 메뉴에 있는 데이터가 적어지므로 지금은 이 문제를 처리하기 위해 OFFSET 기능을 계속 사용합니다. 🎜🎜이름 관리자에서 "area"의 참조 위치 변경: 🎜🎜🎜🎜=OFFSET(동적 드롭다운 메뉴!$D$1,1,,COUNTA(동적 드롭다운 메뉴!$D$2:$D$15)-COUNTBLANK( 동적 드롭다운 메뉴 $D$2:$D$15),1)🎜

열 D의 유일한 값은 수식을 사용하여 얻어지기 때문에 내부의 "빈 셀"은 명목상 "비어 있음"이 아니라 수식으로 얻은 비어 있으므로 직접 전달할 수 없습니다. COUNTIF(D2:D15,"" ) 값이 있는 셀 수를 가져오는 방법입니다. 따라서 먼저 COUNTBLANK 함수(빈 셀 계산)를 사용하여 빈 셀 수를 계산한 다음 COUNTA 함수를 사용하여 비어 있지 않은 셀 수를 계산하고 마지막으로 두 값을 빼서 값이 있는 셀 수를 구했습니다. . 얻은 결과를 OFFSET 함수의 네 번째 매개변수(새 영역에서 확장된 행 수)로 사용하면 유효한 데이터를 동적으로 참조하는 효과를 얻을 수 있습니다. 아래 그림과 같이

A열에 새 지역 이름을 추가하면 G1의 드롭다운 메뉴에도 새 옵션이 추가되는 효과를 살펴보겠습니다. 당신이 필요로하는 것.

팁 5: 차트에서 OFFSET 기능 사용

위 차트는 다들 익숙하실 거라 믿습니다. 작업 중인 학생들은 위 그림에서 A1:B10 영역을 선택하고 도구 모음 - "삽입" - 기둥형 차트로 이동하여 범례의 내용을 완성합니다.

데이터 행을 삭제하면 세로 막대형 차트의 계열 범례가 하나 줄어들지만, 데이터 행을 추가하는 경우 올바른 차트를 표시하려면 차트 데이터 소스의 범위를 변경해야 합니다. 하지만 매번 변경할 수는 없습니다. 그렇지 않으면 Excel을 효율적이고 빠르게 사용하려는 원래 의도를 잃게 됩니다.

이제 문제 해결을 위해 OFFSET 함수를 배울 수 있습니다.

1단계: OFFSET 함수를 사용하여 각각 "날짜 열"과 "수량 열"에 대한 사용자 정의 이름을 만듭니다.

이름 관리자, 위에서 소개했으니 더 이상 할 말이 없습니다. "Date Column"을 선택하고 다음과 같이 설정합니다.

기준 위치 함수:

=OFFSET(图表系列!$A,1,0,COUNTA(图表系列!$A:$A00),1)

원본 데이터에는 수식으로 구한 빈 셀이 없으므로 여기서는 Countblank 함수를 사용할 필요가 없습니다. , 통계에는 CountA 함수를 직접 사용합니다. 비어 있지 않은 셀 수를 가져와 OFFSET 함수의 네 번째 매개변수(새 영역의 행 수)로 사용합니다. A2:A1000은 절대적으로 넓은 영역을 나타내며 새로 입력된 콘텐츠가 이 범위 내에 있음을 보장합니다.

"수량 열"을 선택하고 다음과 같이 같은 방식으로 수량에 대한 사용자 정의 이름을 만듭니다.

2단계: 차트 영역에서 이름 사용

이것이 OFFSET 동적 차트의 핵심입니다. 이름을 추가하는 위치는 매우 중요합니다.

그리기 영역에서 임의의 열을 선택하고 편집 막대에서 아이콘의 기능 쓰기를 볼 수 있습니다. (차트에도 기능이 있다는 것은 이번이 처음입니다.) 여기서 참조 범위를 수정해 보겠습니다.

지역 부분만 변경하면 됩니다.

• 차트 시리즈! $A$2:$A$10

• 차트 시리즈!$B$2:$B$10

이 두 개의 빨간색 부분을 사용자 정의 이름으로 바꾸지 마세요. "차트 시리즈!$ A$2:$A$10"이 전체적으로 교체됩니다!

교체 후 Enter를 누르면 위와 같이 함수가 표시되며 OFFSET.xlsx는 통합 문서의 이름입니다. 효과는 다음과 같습니다.

다른 차트 유형도 작동하여 인상을 깊게 할 수 있습니다.

【편집자 주】

OFFSET 함수의 5개 매개변수는 의미를 이해하면 기억하기 어렵지 않습니다. 반환 값은 다른 함수에 대한 참조로 사용될 수 있습니다. 마찬가지로 "반환 값이 숫자 형식"인 다른 함수도 OFFSET 함수의 매개 변수로 사용될 수 있으므로 데이터가 자체적으로 이동할 수 있습니다.

이 기능은 엑셀 기능에서 없어서는 안될 역할을 합니다. 특히 엑셀 모델링을 사용해야 할 경우 동적 영역을 참조하고 데이터를 자동으로 처리하는 데 자주 사용됩니다. 시간을 좀 더 들여서 학습해 보세요. 미래의 시계 제작 과정에 큰 도움이 될 것입니다.

관련 학습 권장 사항: excel 튜토리얼

위 내용은 엑셀 함수학습: 동적통계의 왕 OFFSET()에 대해 이야기해보자의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!