Lernen von Excel-Funktionen: Sprechen wir über den König der dynamischen Statistiken OFFSET()

Dieser Artikel stellt Ihnen die OFFSET-Funktion vor, die den Spitznamen „König der dynamischen Statistiken“ trägt! Die OFFSET-Funktion ist eine sehr praktische Funktion. Sie spielt in Dropdown-Menüs, dynamischen Diagrammen, dynamischen Referenzen und anderen Operationen eine unersetzliche Rolle. Man kann ohne Übertreibung sagen, dass ein erheblicher Teil der Effizienz von Excel-Tabellen auf OFFSET zurückzuführen ist.

【Vorwort】

Die OFFSET-Funktion ist eine der wichtigen Funktionen, um festzustellen, ob Benutzer von Excel-Funktionen fortgeschritten sind. Wenn Sie in der tatsächlichen Arbeit die Datendateien bei der Arbeit systematisch und automatisch modellieren müssen, werden Sie diese Funktion zwangsläufig verwenden.

【Funktion und Syntax】

Die Funktion der OFFSET-Funktion besteht darin, die angegebene Referenz als Referenzsystem zu verwenden und über den angegebenen Offset eine neue Referenz zurückzugeben.

Syntax: 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 ist die Original Der grundlegende Punkt

• rows ist die Anzahl der zu versetzenden Zeilen. Positive Zahlen gehen nach unten, negative Zahlen nach oben und Null bleibt unverändert. 🎜
• 🎜cols ist die Anzahl der Spalten, die versetzt werden sollen, positive Zahlen stehen rechts, negative Zahlen stehen links und Null bleibt unverändert. 🎜
• 🎜[Höhe] ist die vertikale Ausdehnung mehrerer Linien nach dem Versetzen des Basispunkts, positive Zahlen erweitern sich nach unten und negative Zahlen erweitern sich nach oben. 🎜
• 🎜[Breite] ist die horizontale Erweiterung mehrerer Spalten nach dem Versetzen des Basispunkts. Positive Zahlen werden nach rechts und negative Zahlen nach links erweitert. 🎜

🎜Wenn der vierte und fünfte Parameter nicht verwendet werden (aber nicht Null sein dürfen), hat der neu referenzierte Bereich die gleiche Größe wie der ursprüngliche Basispunkt. 🎜🎜Der ursprüngliche Basispunkt kann eine Zelle oder ein Bereich sein. 🎜🎜Für Schüler, die gerade erst mit der OFFSET-Funktion in Berührung gekommen sind, kann es schwierig sein, die oben genannten Parameter zu verstehen. Lassen Sie sich dies daher anhand eines Diagramms erklären. 🎜🎜🎜🎜Ich glaube dir kann es lesen Dieses Bild hat viel Zeit in Anspruch genommen. Wir können die Daten zunächst gemäß den Richtlinien im Bild oben in die OFFSET-Funktion eingeben und dies in der Praxis durchführen, um zu prüfen, ob sie mit der Adresse des neuen Bereichs übereinstimmen. 🎜🎜Testen wir zunächst das erste Beispiel und sehen uns die Ergebnisse des positiven Parameters an: 🎜🎜🎜🎜Nach der Überprüfung werden die Werte im gelben „neuen Bereich“ summiert, was 256 entspricht, was mit dem Wert in Zelle C15 und dem Ergebnis übereinstimmt ist richtig. Wenn Schüler diese Daten simulieren möchten, können sie auch Zelle C15 auswählen und dann die Funktion „Formel – Formelüberprüfung – Formelauswertung“ in der Symbolleiste verwenden, um den Rückgabewert von OFFSET intuitiver anzuzeigen. (Es ist auch möglich, F9 in Funktionen zu verwenden. Wählen Sie den Funktionsteil von OFFSET in der Formel aus und drücken Sie F9. Ich werde hier nicht auf Details eingehen.) 🎜🎜🎜🎜Lassen Sie uns das zweite Beispiel noch einmal testen und die Ergebnisse sehen, wenn negative Zahlen als Parameter verwendet werden:🎜 🎜🎜🎜Sie können die verwenden Testen Sie die Methode „zu bewertende Formel“, testen Sie sie selbst und sehen Sie sich den Rückgabewert des OFFSET-Funktionsbereichs an. 🎜🎜Nachdem wir das grundlegende Funktionsprinzip von OFFSET kennen, kann es uns dabei helfen, viele Vorgänge und Berechnungen in der tatsächlichen Arbeit durchzuführen, und durch die Beteiligung dieser Funktion können viele automatisierte Effekte in Excel erzielt werden. Werfen wir einen Blick auf die wichtige Rolle der OFFSET-Funktion im tatsächlichen Betrieb! 🎜🎜🎜1. Grundlegende allgemeine Verwendung🎜🎜🎜Als regionale Referenz für andere Funktionen sollte es die grundlegendste Verwendung der OFFSET-Funktion sein. Die OFFSET-Funktion verschiebt den Zellbereich nicht, sondern gibt eine um den Offset erweiterte Bereichsadresse zurück. Daher können alle Funktionen, die einen Referenzbereich als Parameter verwenden, den Rückgabewert der OFFSET-Funktion verwenden, wie beispielsweise unser obiges Beispiel Sum (OFFSET()) oder das folgende Beispiel: 🎜🎜🎜🎜Das Prinzip der Funktion ist das gleiche wie bei der obigen Verwendung, also Wir werden nicht noch einmal auf Details eingehen. Der von der OFFSET-Funktion zurückgegebene Bereich wird als Parameter der MAX-Funktion verwendet. 🎜🎜🎜2. Erweiterte allgemeine Verwendung🎜🎜🎜🎜Trick ①: Transponieren-TRANSPOSE-Funktion simulieren🎜🎜🎜🎜🎜 Bevor wir die TRANSPOSE-Funktion verwenden, müssen wir zuerst die entsprechende Größe auswählen Transpositionsbereich, und Sie müssen Strg+Umschalt+Eingabetaste verwenden, um die Formel zu beenden, was ziemlich umständlich ist. 🎜🎜Hier können wir die OFFSET-Funktion verwenden, um den Effekt dieser Transposition zu simulieren, wie in der Abbildung oben gezeigt. 🎜🎜A11-Zellenfunktion: 🎜🎜=OFFSET($A$1,COLUMN()-1,ROW()-11)🎜🎜Funktionsanalyse: 🎜

Das Transponieren von Daten ist eigentlich ein Prozess von „Zeile zu Spalte“ und „Spalte zu Zeile“. Genauer gesagt geht es um den Austausch von Zeilennummern und Spaltennummern. Die Spalte „Name“, die erste Spalte in den Originaldaten, wird nach der Transposition zur ersten Zeile im neuen Bereich. Auf die gleiche Weise wird die Zeilennummer jeder Zeile in der Spalte „Name“ zur transponierten Spaltennummer. Das Prinzip der Verwendung von OFFSET besteht darin, den Anführungsbereich der Zeilen- und Spaltennummern zu ändern, wenn der Offsetwert verwendet wird.

★ Zum Beispiel ist in Zelle A11 COLUMN()=1, 1-1=0, dann ist der zweite Parameter von OFFSET 0, was angibt, dass die Anzahl der Zeilen des ursprünglichen Basispunkts nicht versetzt ist (der zweite Parameter von OFFSET stellt den Zeilenversatz dar. Wenn Sie damit nicht vertraut sind, lesen Sie bitte den vorherigen Inhalt!) ROW()=11, 11-11=0 und der dritte Parameter von OFFSET ist 0, was bedeutet, dass die Anzahl der Spalten nicht versetzt wird, sodass der Wert der ursprünglichen Basispunktzelle A1 in Anführungszeichen gesetzt wird.

★★ Ziehen Sie die Funktion nach rechts, um Zelle B11, COLUMN()=2, 2-1=1 zu füllen. Dann ist der zweite Parameter von OFFSET 1, was angibt, dass die Anzahl der Zeilen des ursprünglichen Basispunkts verschoben wird um eine Position nach unten. ROW()=11, 11-11=0 und der dritte Parameter von OFFSET ist 0, was darauf hinweist, dass die Anzahl der Spalten nicht versetzt ist, sodass sich Zelle B11 auf den Wert von Zelle A2 bezieht, nachdem der Basispunkt A1 nach unten verschoben wurde.

★★★ Ziehen Sie die Funktion von Zelle A11 herunter, um sie zu füllen. In Zelle A12 ist COLUMN()=1, 1-1=0 und die Anzahl der Zeilen wird nicht versetzt. ROW () = 12, 12-11 = 1, der dritte Parameter von OFFSET ist 1, was angibt, dass die Spaltennummer um eine Position nach rechts vom Basispunkt A1 versetzt ist und sich auf den Wert von Zelle B1 bezieht (den Grund dafür). A1 in unserer Formel lautet „Absolute Bezüge verwenden“, da alle unsere Zellen auf A1 basieren.

Analog dazu verwenden wir die Funktionen COLUMN und ROW, wenn wir mit der Maus nach unten und rechts ziehen, um die Formel einzugeben, um den Versatz jeder Zelle zu lokalisieren und so den Transpositionseffekt zu erzielen.

Tipps②: Simulieren Sie die umgekehrte Abfragefunktion der Vlookup-Funktion

Die umgekehrte Abfragefunktion der VLOOKUP-Funktion erfolgt meist mithilfe von Arrays, aber aufgrund des Arrays ist es bei großen Datenmengen erforderlich, Die Funktion kann einfrieren, sodass viele Schüler stattdessen auch die INDEX-Funktion verwenden. Lassen Sie uns heute das Wissen aller erweitern. Wir verwenden die OFFSET-Funktion, um diese Art von Problem zu lösen.

C12-Zellenfunktion:

=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)

Funktionsanalyse:

Wir verwenden Cell A1 dient als ursprünglicher Basispunkt und der zurückgegebene Wert muss in derselben Spalte wie der ursprüngliche Basispunkt liegen. Daher müssen wir nur den Zeilenversatz der OFFSET-Funktion berücksichtigen, nicht den Spaltenversatz. Da Mitarbeiternummern im Allgemeinen eindeutige Werte sind, verwenden wir die MATCH-Funktion, um die Seriennummer der Nummer „D2568“ im Bereich B2:B7 zu erhalten. Der Rückgabewert 4 wird als Zeilenoffset der OFFSET-Funktion verwendet und in die übernommen OFFSET-Funktion. =OFFSET($A$1,4,). Wenn der Spaltenversatz weggelassen wird, ist der Standardwert 0, und wenn die erweiterte Breite und die erweiterte Höhe weggelassen werden, ist der Standardwert 1 (d. h. eine Zelle). Handelt es sich nicht um eine A5-Zelle?

Stick ③: Aktualisierte Version des Daten-Resets – Datenstruktur neu anordnen

🎜Nachdem Sie die Formel in den Bereich F2:H2 eingegeben haben, ziehen Sie nach unten, um die Daten einzugeben, und Sie erhalten rechts die eindimensionale Datentabelle. Diese Art von Datenumordnungsproblem sollte in der Praxis keine Seltenheit sein! Welche Methode werden die Schüler also wählen, um das Problem zu lösen? Im Gegenteil, der Autor ist der Meinung, dass die Idee der OFFSET-Funktion prägnanter und klarer ist. 🎜🎜Funktionsanalyse: 🎜🎜Schritt 1: Aufeinanderfolgende Namen abrufen 🎜🎜F2-Zellenfunktion: 🎜🎜=OFFSET($A$1,INT((ROW(F1)-1)/3)+1 ,) Code>🎜🎜Da es insgesamt drei Subjekte gibt, kann festgestellt werden, dass derselbe Name dreimal vorkommen muss. Wenn wir dann die F2-Zellenfüllfunktion ablegen, müssen wir sicherstellen, dass der Zeilenversatz der OFFSET-Funktion alle ist 3 Die Zellparameterwerte sind alle gleich. Hier müssen Sie über eine mathematische Vorstellung von „Divisorrundung“ verfügen. Lassen Sie uns zur Erläuterung ein Bild auflisten: 🎜🎜<img style="max-width:90%" style="max-width:90%" src="https://img.php.cn/%20upload%20/article/000/000/024/82112bb5dc6d295ab311d81e75506d49-8.png" alt="Lernen von Excel-Funktionen: Sprechen wir über den König der dynamischen Statistiken OFFSET()" >🎜🎜Auf dem Bild sehen wir eine Reihe von Seriennummern. Nach der Konvertierung durch INT((serial number-1)/3)+1 können Sie Holen Sie sich die Reihenfolge auf der rechten Seite (wenn es 4 Themen gibt, ändern Sie 3 in 4 usw.). Geben Sie diese Seriennummer als Standard für den Zeilenoffset in den zweiten Parameter der OFFSET-Funktion ein, und Sie können den Effekt unserer Namensspalte erzielen. 🎜🎜Schritt 2: Weisen Sie der gleichen Person verschiedene Themen zu🎜🎜G2-Zellenfunktion: 🎜🎜<code>=OFFSET($A$1,,MOD(ROW(G1)-1,3)+1)🎜 🎜Da jeder Name in unserer Spalte F dreimal vorkommt, bedeutet dies, dass die drei Fächer Chinesisch, Mathematik und Englisch nacheinander und zyklisch aufgelistet werden müssen. Die gleiche Idee wie im ersten Schritt: Verwenden Sie die „Divisorberechnung“ „Mehr als“. mathematisches Denken, um den Effekt zu erzielen. 🎜🎜🎜🎜🎜Wie im Bild oben gezeigt, wird die Seriennummer durch die MOD-Funktion umgewandelt, um eine fortlaufende und zyklisch aufgelistete Seriennummer zu erhalten. Mithilfe dieser Seriennummer als dritten Parameterspaltenoffset der OFFSET-Funktion kann der Kontoinhalt der Originaldaten sequentiell und zyklisch abgeleitet werden. 🎜🎜Schritt 3: Simulieren Sie die INDEX-Funktion anhand von Namen und Themen, um Noten aus den Originaldaten abzuleiten🎜

H2-Zellenfunktion:

=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)

Verwenden Sie die MATCH-Funktion, um die Sequenznummer zu erhalten, die den Daten im relevanten Bereich entspricht, die als Offset von OFFSET verwendet und in den zweiten bzw. dritten Parameter eingefügt wird. Der Zellversatz von der Referenzpunktzelle A1 ist der von uns benötigte Neigungswert. 🎜🎜Anhand des obigen Inhalts können wir leicht feststellen, dass die OFFSET-Funktion häufig in Verbindung mit der MATCH-Funktion verwendet wird. Da die Match-Funktion die Sequenznummer des Schlüsselworts in einem Array finden kann, verwenden wir diese Funktion häufig, um den Offset der OFFSET-Funktion zu bestimmen. 🎜🎜3. Ideen für High-End-Anwendungen🎜🎜 (Prototyp einer dynamischen Berichtsvorlage) 🎜🎜Wir verwenden Excel für schnelle Analysieren Sie Daten geostatistisch und extrahieren Sie schnell, was wir brauchen. Gehen Sie nun von den folgenden zwei Szenarien aus: 🎜🎜Szenario 1:🎜🎜Der Leiter hat die Aufgabe, die Verkaufsdaten eines bestimmten Quartals zu zählen. Wir ergreifen sofort Maßnahmen und verwenden Funktionen, um schnell Berichte zu erstellen Szenario zwei:🎜🎜Der Leiter hat die Arbeit arrangiert, da die Verkaufsdaten vierteljährlich erfasst werden müssen. Deshalb haben wir die Vorlage im Voraus erstellt und es liegt an uns, wann wir sie herausgeben Bericht. Denken Sie daran: Lassen Sie „mittlere Führungskräfte“ nicht wissen, dass Sie sehr produktiv sind. 🎜🎜Zwei Szenarien, welchen Ansatz würden Sie wählen? Der Autor hofft, dass es das zweite ist. 🎜🎜🎜🎜Denken Dies bestimmt das Format unserer Tabellierung. Dies ist ein einfacher Fall. Wenn die Datenquelle geändert wird, werden die entsprechenden vierteljährlichen Daten automatisch angepasst. Nicht alle Positionen in komplexen Vorlagen verwenden die OFFSET-Funktion, aber für die Notwendigkeit, Datenbereiche dynamisch zu referenzieren, ist es absolut richtig, die OFFSET-Funktion zu verwenden, um damit umzugehen. 🎜🎜4. Typische Anwendungsbeispiele🎜🎜Stunt 4: Erstellen Sie ein dynamisches Dropdown-Menü 🎜🎜Im Prozess der Datenmodellierung verwenden wir häufig Dropdown-Menüs (oder Kombinationsfeld-Steuerelemente). Um die Einzigartigkeit des Dropdown-Inhalts sicherzustellen, verwenden wir die „Taiwan Balm“-Funktion von INDEX+SMALL+IF+ROW, um doppelte Daten aus dem Array zu extrahieren. Erinnern Sie sich noch an das Beispiel, bei dem die OFFSET-Funktion die INDEX-Funktion ersetzte, über die wir im vorherigen Artikel gesprochen haben? Wenn daher die OFFSET-Funktion anstelle der Indexfunktion verwendet werden kann, kann die OFFSET-Funktion auch den „one-size-fits-all“-Prozess erreichen. Werfen wir einen Blick auf den Erstellungsprozess eines komplexen „Dropdown-Menüs“. 🎜🎜Schritt 1: Verwenden Sie die OFFSET-Funktion, um den einzigartigen Wert der „Taiwan Balm“-Formel zu extrahieren🎜🎜🎜🎜Diese Formel ist relativ lang und wird wie folgt aufgeführt: 🎜🎜D2-Zellenfunktion: 🎜🎜=IFERROR(OFFSET($A$1,SMALL (IF(ROW ($A$2:$A$27)-1=MATCH($A$2:$A$27,$A$2:$A$27,0),ROW($1:$20),9^9),ROW (D1)),),"")🎜🎜Die Tigerbalsam-Formel ist nicht das Thema, über das wir heute sprechen werden, daher werden wir nicht näher darauf eingehen. Wichtig ist, alle wissen zu lassen, dass auch die OFFSET-Funktion einen solchen Duplizierungseffekt erzielen kann. 🎜🎜Schritt 2: Verwenden Sie die OFFSET-Funktion im Namensmanager, um eine Datenquelle einzurichten🎜🎜Wir können die Tastenkombination Strg+F3 verwenden, um das Fenster des Namensmanagers zu öffnen, dann einen neuen Namen erstellen und den Namen auf „Bereich“ setzen. und die Referenzposition auf „D2 :D15“, wie unten gezeigt: 🎜🎜🎜🎜Wählen Sie dann Zelle G1 aus, drücken Sie Alt+D+L, um das Feld mit den Datenvalidierungseinstellungen zu öffnen, wählen Sie „Sequenz“ unter „Zulassen“ und geben Sie „=Bereich“ unter „Quelle“ ein, wie in der Abbildung gezeigt Abbildung unten: 🎜 🎜🎜🎜 Klicken Sie auf die Schaltfläche „OK“. Anschließend wird das Dropdown-Menü unserer G1-Zelle erstellt. Aber wir werden auch feststellen, dass es viele leere Optionen gibt, die wir nicht brauchen. 🎜🎜🎜🎜Einige Schüler ich würde sagen, wählen Sie einfach D2:D5 im Namensmanager aus. Ja, aber wenn im Bereich von Spalte A neue Daten angezeigt werden, werden im Dropdown-Menü weniger Daten angezeigt. Daher verwenden wir derzeit immer noch die OFFSET-Funktion, um dieses Problem zu lösen. 🎜🎜Ändern Sie die Referenzposition von „area“ im Namensmanager: 🎜🎜🎜🎜=OFFSET(Dynamisches Dropdown-Menü!$D$1,1,,COUNTA(Dynamisches Dropdown-Menü!$D$2:$D$15)-COUNTBLANK( Dynamisches Dropdown-Menü! $D$2:$D$15),1)🎜

Da der einzige Wert unserer Spalte D mithilfe einer Formel ermittelt wird, ist die darin enthaltene „leere Zelle“ nicht nominell „leer“, sondern durch die Formel erhalten leer, sodass sie nicht direkt an COUNTIF(D2:D15,“) übergeben werden kann. Methode, um die Anzahl der Zellen mit Werten zu ermitteln. Daher haben wir zuerst die Funktion COUNTBLANK (Leerzellen zählen) verwendet, um die Anzahl der leeren Zellen zu zählen, dann die Funktion COUNTA, um die Anzahl der nicht leeren Zellen zu zählen, und schließlich beide subtrahiert, um die Anzahl der Zellen mit Werten zu erhalten. . Durch die Verwendung des erhaltenen Ergebnisses als vierter Parameter der OFFSET-Funktion (Anzahl der erweiterten Zeilen im neuen Bereich) wird der Effekt einer dynamischen Referenzierung gültiger Daten erreicht. Wie im Bild unten gezeigt:

Wenn ein neuer Regionsname zu Spalte A hinzugefügt wird, werden auch neue Optionen zum Dropdown-Menü von G1 hinzugefügt. Schauen wir uns den Effekt an was Sie brauchen.

Tipps 5: Verwendung der OFFSET-Funktion in Diagrammen

Ich glaube, jeder kennt das obige Diagramm. Studenten, die arbeiten, haben Erfahrung in der Erstellung von Diagrammen. Wählen Sie den Bereich A1:B10 im obigen Bild aus und gehen Sie zur Symbolleiste – „Einfügen“ – Säulendiagramm, um den Inhalt unserer Legende zu vervollständigen.

Wenn wir eine Datenzeile löschen, gibt es eine Reihenlegende weniger im Säulendiagramm. Wenn wir jedoch eine Datenzeile hinzufügen, müssen wir den Bereich der Diagrammdatenquelle ändern, um das richtige Diagramm anzuzeigen. Aber wir können es nicht jedes Mal ändern, sonst verlieren wir die ursprüngliche Absicht, Excel effizient und schnell zu nutzen.

Zu diesem Zeitpunkt können wir noch von der OFFSET-Funktion lernen, um das Problem zu lösen:

Schritt 1: Verwenden Sie die OFFSET-Funktion, um benutzerdefinierte Namen für die „Datumsspalte“ bzw. „Mengenspalte“ zu erstellen.

Namensmanager, der Wir haben es oben vorgestellt, aber dazu gibt es nichts mehr zu sagen. Wählen Sie „Datumsspalte“ und stellen Sie sie wie folgt ein:

Referenzpositionsfunktion:

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

Da es in den Originaldaten keine leeren Zellen gibt, die durch die Formel erhalten wurden, besteht hier keine Notwendigkeit, die Countblank-Funktion zu verwenden , und verwenden Sie die CountA-Funktion direkt für Statistiken. Rufen Sie die Anzahl der nicht leeren Zellen ab und verwenden Sie sie als vierten Parameter der OFFSET-Funktion (die Anzahl der Zeilen im neuen Bereich). A2:A1000 stellt hier einen absolut großen Bereich dar, sodass sichergestellt ist, dass der neu eingegebene Inhalt innerhalb dieses Bereichs liegt.

Wählen Sie „Mengenspalte“ und erstellen Sie auf die gleiche Weise wie folgt einen benutzerdefinierten Namen für die Menge:

Schritt 2: Verwenden Sie den Namen im Diagrammbereich

Dies ist der Schlüssel zum dynamischen OFFSET-Diagramm. Die Stelle, an der der Name hinzugefügt wird, ist sehr wichtig.

Wählen Sie im Zeichenbereich eine beliebige Spalte aus und in der Bearbeitungsleiste können Sie die Funktionsschrift des Symbols sehen (ist dies das erste Mal, dass Sie wissen, dass Diagramme auch Funktionen haben)? Lassen Sie uns hier den Umfang der Referenz ändern.

Wir müssen nur den Bereichsteil ändern.

• Chart-Serie!$B$2:$B$10

• Ersetzen Sie diese beiden roten Teile niemals durch „Chart-Serie!$“. A$2:$A$10“ wird als Ganzes ersetzt!

Nach dem Ersetzen und Drücken der Eingabetaste wird die Funktion wie oben gezeigt angezeigt. OFFSET.xlsx ist der Name unserer Arbeitsmappe. Der Effekt ist wie folgt:

Andere Diagrammtypen sind ebenfalls verfügbar. Sie können es ausprobieren, um Ihren Eindruck zu vertiefen.

【Anmerkung des Herausgebers】

Die fünf Parameter der OFFSET-Funktion sind nicht schwer zu merken, wenn Sie die Bedeutung verstehen. Sein Rückgabewert kann als Referenz für andere Funktionen verwendet werden. Ebenso können andere Funktionen, deren „Rückgabewert im numerischen Format vorliegt“, auch als Parameter der OFFSET-Funktion verwendet werden, sodass sich unsere Daten von selbst verschieben können. Diese Funktion spielt in Excel-Funktionen eine unverzichtbare Rolle. Diese Funktion wird häufig verwendet, um dynamische Bereiche zu referenzieren und Daten automatisch zu verarbeiten von großem Nutzen für Ihren zukünftigen Uhrmacherprozess.

Verwandte Lernempfehlungen:

Excel-Tutorial

Das obige ist der detaillierte Inhalt vonLernen von Excel-Funktionen: Sprechen wir über den König der dynamischen Statistiken OFFSET(). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!