Apprentissage des fonctions Excel : parlons du roi des statistiques dynamiques OFFSET()

Cet article va vous présenter la fonction OFFSET, surnommée le « Roi des Statistiques Dynamiques » ! La fonction OFFSET est une fonction très pratique. Elle joue un rôle irremplaçable dans les menus déroulants, les graphiques dynamiques, les références dynamiques et autres opérations. Il n’est pas exagéré de dire qu’une part considérable de l’efficacité des tableaux Excel vient d’OFFSET.

【Avant-propos】

La fonction OFFSET est l'une des fonctions importantes pour déterminer si les utilisateurs de la fonction Excel ont avancé. Dans la pratique, si vous avez besoin de modéliser systématiquement et automatiquement les fichiers de données au travail, vous utiliserez inévitablement cette fonction.

【Fonction et syntaxe】

La fonction de la fonction OFFSET est d'utiliser la référence spécifiée comme système de référence et de renvoyer une nouvelle référence via le décalage donné.

Syntaxe : 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 est la original Le point fondamental

• rows est le nombre de lignes à décaler. Les nombres positifs diminuent, les nombres négatifs augmentent et zéro reste inchangé. 🎜
• 🎜cols est le nombre de colonnes à décaler, les nombres positifs sont à droite, les nombres négatifs sont à gauche et zéro reste inchangé. 🎜
• 🎜[hauteur] est l'expansion verticale de plusieurs lignes après que le point de base soit décalé, les nombres positifs se développent vers le bas et les nombres négatifs se développent vers le haut. 🎜
• 🎜[width] est l'expansion horizontale de plusieurs colonnes après le décalage du point de base. Les nombres positifs s'étendent vers la droite et les nombres négatifs s'étendent vers la gauche. 🎜

🎜Si les quatrième et cinquième paramètres ne sont pas utilisés (mais ne peuvent pas être nuls), la zone nouvellement référencée aura la même taille que le point de base d'origine. 🎜🎜Le point de base originel peut être une cellule ou une zone. 🎜🎜Pour les étudiants qui viennent d'entrer en contact avec la fonction OFFSET, il peut être difficile de comprendre les paramètres ci-dessus, alors laissez-nous vous l'expliquer avec un schéma. 🎜🎜🎜🎜Je te crois je peux le lire. Cette photo a pris beaucoup de temps. Nous pouvons d'abord remplir les données dans la fonction OFFSET selon les directives de l'image ci-dessus, et le faire en pratique pour voir si cela est cohérent avec l'adresse de la nouvelle zone ? 🎜🎜Testons d'abord le premier exemple et voyons les résultats du paramètre positif : 🎜🎜🎜🎜Après vérification, les valeurs dans la "nouvelle zone" jaune sont additionnées, ce qui est égal à 256, ce qui est cohérent avec la valeur de la cellule C15, et le résultat est correct. Si les étudiants souhaitent simuler ces données, ils peuvent également sélectionner la cellule C15, puis utiliser la fonction « Formule - Révision de formule - Évaluation de formule » dans la barre d'outils pour voir de manière plus intuitive la valeur de retour de OFFSET. (Il est également possible d'utiliser F9 dans les fonctions. Sélectionnez la partie fonction OFFSET de la formule et appuyez sur F9. Je n'entrerai pas dans les détails ici.) 🎜🎜🎜🎜Testons à nouveau le deuxième exemple et voyons les résultats en cours lorsque des nombres négatifs sont des paramètres :🎜🎜🎜🎜Vous pouvez utiliser la "formule pour évaluer la méthode ", testez-la vous-même et voyez la valeur de retour de la zone de fonction OFFSET. 🎜🎜Après avoir connu le principe de fonctionnement de base d'OFFSET, il peut nous aider à effectuer de nombreuses opérations et calculs dans le travail réel, et avec la participation de cette fonction, de nombreux effets automatisés dans Excel peuvent être obtenus. Jetons un coup d'œil au rôle puissant de la fonction OFFSET dans les opérations réelles ! 🎜🎜🎜1. Utilisation générale de base🎜🎜🎜En tant que référence régionale pour d'autres fonctions, il devrait s'agir de l'utilisation la plus basique de la fonction OFFSET. La fonction OFFSET ne déplace pas la plage de cellules, mais renvoie une adresse de plage étendue par décalage. Par conséquent, toutes les fonctions qui prennent une zone de référence en paramètre peuvent utiliser la valeur de retour de la fonction OFFSET, comme notre exemple ci-dessus Somme (OFFSET ()), ou l'exemple suivant : 🎜🎜🎜🎜Le principe de la fonction est le même que l'utilisation ci-dessus, donc nous n'entrerons pas plus dans les détails. Il utilise la zone renvoyée par la fonction OFFSET comme paramètre de la fonction MAX. 🎜🎜🎜2. Utilisation courante avancée🎜🎜🎜🎜Astuce ① : simuler la fonction TRANSPOSE de transposition🎜🎜🎜🎜🎜 Avant d'utiliser la fonction TRANSPOSE, nous devons d'abord sélectionner la taille correspondante Zone de transposition, et vous devez utiliser Ctrl+Shift+Enter pour terminer la formule, ce qui est assez fastidieux. 🎜🎜Ici, nous pouvons utiliser la fonction OFFSET pour simuler l'effet de cette transposition, comme le montre la figure ci-dessus. 🎜🎜Fonction de cellule A11 : 🎜🎜=OFFSET($A$1,COLUMN()-1,ROW()-11)🎜🎜Analyse de fonction : 🎜

La transposition de données est en fait un processus de "ligne à colonne" et de "colonne à ligne". Pour être plus précis, il s'agit de l'échange de numéros de ligne et de numéros de colonne. La colonne "Nom", première colonne des données d'origine, devient la première ligne de la nouvelle zone après transposition. De la même manière, le numéro de ligne de chaque ligne de la colonne "Nom" devient le numéro de colonne transposé. Le principe de l'utilisation de OFFSET est de modifier la plage de cotation des numéros de ligne et de colonne lors de la prise de la valeur de décalage.

★ Par exemple, dans la cellule A11, COLUMN()=1, 1-1=0, alors le deuxième paramètre de OFFSET est 0, indiquant que le nombre de lignes du point de base d'origine n'est pas décalé (le deuxième paramètre de OFFSET représente le décalage de ligne, si vous ne le connaissez pas, veuillez lire le contenu précédent !). ROW()=11, 11-11=0, le troisième paramètre de OFFSET est 0, indiquant que le nombre de colonnes n'est pas décalé, donc la valeur de la cellule A1 du point de base d'origine est citée.

★★ Tirez la fonction vers la droite pour remplir, cellule B11, COLUMN()=2, 2-1=1, puis le deuxième paramètre de OFFSET est 1, indiquant que le nombre de lignes du point de base d'origine est décalé vers le bas d’une position. ROW()=11, 11-11=0, et le troisième paramètre de OFFSET est 0, indiquant que le nombre de colonnes n'est pas décalé, donc la cellule B11 fait référence à la valeur de la cellule A2 après que le point de base A1 soit décalé vers le bas.

★★★ Déroulez la fonction de la cellule A11 pour la remplir. Dans la cellule A12, COLUMN()=1, 1-1=0, et le nombre de lignes n'est pas décalé. ROW()=12, 12-11=1, le troisième paramètre de OFFSET est 1, indiquant que le numéro de colonne est décalé d'une position vers la droite par rapport au point de base A1 et fait référence à la valeur de la cellule B1 (la raison pour laquelle A1 dans notre formule est Utiliser des références absolues car toutes nos cellules sont basées sur A1).

Par analogie, lorsque nous utilisons la souris pour dérouler vers le bas et vers la droite pour remplir la formule, nous utilisons les fonctions COLONNE et LIGNE pour nous aider à localiser le décalage de chaque cellule, obtenant ainsi l'effet de transposition.

Conseils② : Simulez la fonction de requête inversée de la fonction Vlookup

La requête inversée de la fonction VLOOKUP se fait principalement à l'aide de tableaux, mais à cause du tableau, lorsque la quantité de données est importante, la fonction peut se bloquer, donc de nombreux étudiants utiliseront également la fonction INDEX à la place. Alors aujourd’hui, enrichissons les connaissances de chacun. Nous utilisons la fonction OFFSET pour traiter ce type de problème.

Fonction de cellule 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)

Analyse de fonction :

Nous utilisons Cell A1 sert de point de base d'origine et la valeur renvoyée doit être dans la même colonne que le point de base d'origine, nous devons donc uniquement considérer le décalage de ligne de la fonction OFFSET, pas le décalage de colonne. Étant donné que les numéros d'employé sont généralement des valeurs uniques, nous utilisons la fonction MATCH pour obtenir le numéro de série du numéro "D2568" dans la zone B2:B7. La valeur de retour 4 est utilisée comme décalage de ligne de la fonction OFFSET et est introduite dans la zone B2:B7. Fonction DÉCALAGE = DÉCALAGE ($ A $ 1,4,). Si le décalage de colonne est omis, la valeur par défaut est 0, et si la largeur étendue et la hauteur étendue sont omises, la valeur par défaut est 1 (c'est-à-dire une cellule).

Stick ③ : version améliorée de la réinitialisation des données - réorganiser la structure des données

🎜Après avoir entré la formule dans la zone F2:H2, déroulez vers le bas pour remplir les données, et vous obtiendrez le tableau de données unidimensionnel sur la droite. Ce type de problème de réorganisation des données ne devrait pas être rare dans le travail réel ! Alors, quelle méthode les élèves choisiront-ils pour le résoudre ? Au contraire, l’auteur estime que l’idée de​​la fonction OFFSET est plus concise et claire. 🎜🎜Analyse des fonctions : 🎜🎜Étape 1 : Obtenez des noms consécutifs 🎜🎜Fonction de cellule F2 : 🎜🎜=OFFSET($A$1,INT((ROW(F1)-1)/3)+1 ,) code>🎜🎜Comme il y a trois sujets au total, il peut être déterminé que le même nom doit apparaître trois fois. Ensuite, lorsque nous déroulons la fonction de remplissage de cellule F2, nous devons nous assurer que le décalage de ligne de la fonction OFFSET est égal à chaque. 3 Les valeurs des paramètres de cellule sont toutes les mêmes. Ici, vous devez avoir une réflexion mathématique sur "l'arrondi des diviseurs". Listons une image pour aider à expliquer : 🎜🎜<img style="max-width:90%" style="max-width:90%" src="https://img.php.cn/%20upload%20/article/000/000/024/82112bb5dc6d295ab311d81e75506d49-8.png" alt="Apprentissage des fonctions Excel : parlons du roi des statistiques dynamiques OFFSET()" >🎜🎜Sur l'image, nous pouvons voir un ensemble de numéros de série. Après conversion par INT((numéro de série-1)/3)+1, vous pouvez Obtenez la séquence de droite (s'il y a 4 sujets, changez 3 en 4, et ainsi de suite). Mettez ce numéro de série dans le deuxième paramètre de la fonction OFFSET comme norme pour le décalage de ligne, et vous pourrez obtenir l'effet de notre colonne de nom. 🎜🎜Étape 2 : Attribuez différents sujets à la même personne🎜🎜Fonction de la cellule G2 : 🎜🎜<code>=OFFSET($A$1,,MOD(ROW(G1)-1,3)+1)🎜 🎜Étant donné que chaque nom dans notre colonne F apparaît trois fois, cela détermine que les trois matières que sont le chinois, les mathématiques et l'anglais doivent être répertoriées de manière séquentielle et cyclique. La même idée que la première étape, utilisez le « calcul du diviseur » « Plus que ». pensée mathématique pour obtenir l’effet. 🎜🎜🎜🎜🎜Comme le montre l'image ci-dessus, le numéro de série est converti par la fonction MOD pour obtenir un numéro de série répertorié de manière séquentielle et cyclique. En utilisant ce numéro de série comme troisième décalage de colonne de paramètres de la fonction OFFSET, le contenu du compte des données d'origine peut être dérivé de manière séquentielle et cyclique. 🎜🎜Étape 3 : Simulez la fonction INDEX à travers des noms et des sujets pour dériver des notes à partir des données originales🎜

Fonction de la cellule 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)

Utilisez la fonction MATCH pour obtenir le numéro de séquence correspondant aux données dans la zone concernée, qui est utilisé comme décalage de OFFSET et placé respectivement dans les deuxième et troisième paramètres. Le décalage de cellule par rapport à la cellule du point de référence A1 est la valeur de pente dont nous avons besoin. 🎜🎜Grâce au contenu ci-dessus, il ne nous est pas difficile de constater que la fonction OFFSET est souvent utilisée en conjonction avec la fonction MATCH. Étant donné que la fonction Match peut trouver le numéro de séquence du mot-clé dans un tableau, nous utilisons souvent cette fonction pour déterminer le décalage de la fonction OFFSET. 🎜🎜3. Idées d'applications haut de gamme🎜🎜 (Prototype de modèle de rapport dynamique) 🎜🎜Nous utilisons Excel pour une utilisation rapide Analysez géostatistiquement les données et extrayez rapidement ce dont nous avons besoin. Supposons maintenant les deux scénarios suivants : 🎜🎜Scénario 1 :🎜🎜Le leader a organisé un travail pour compter les données de ventes d'un certain trimestre. Nous agissons immédiatement et utilisons des fonctions pour créer rapidement des rapports ; Scénario deux :🎜🎜Le leader a organisé le travail, car les données de vente doivent être collectées chaque trimestre, nous avons donc créé le modèle à l'avance, et c'est à nous de décider quand émettre le rapport. N’oubliez pas : ne laissez pas les « dirigeants intermédiaires » savoir que vous êtes très productif. 🎜🎜Deux scénarios, quelle approche choisiriez-vous ? L'auteur espère que ce sera le deuxième. 🎜🎜🎜🎜Pensée Ceci détermine le format de notre tabulation. Il s'agit d'un cas simple. Lorsque la source de données est modifiée, les données trimestrielles correspondantes seront automatiquement ajustées. Toutes les positions dans des modèles complexes n'utiliseront pas la fonction OFFSET, mais pour la nécessité de référencer dynamiquement des zones de données, il est tout à fait correct d'utiliser la fonction OFFSET pour la gérer. 🎜🎜4. Exemples d'utilisation typiques🎜🎜Cascade 4 : Créer un menu déroulant dynamique 🎜🎜Dans le processus de modélisation des données, nous utilisons souvent des menus déroulants (ou des contrôles de zone de liste déroulante). Afin de garantir l'unicité du contenu de la liste déroulante, nous utiliserons la fonction « Taiwan Balm » de INDEX+SMALL+IF+ROW pour extraire les données en double du tableau. Vous souvenez-vous encore de l’exemple où la fonction OFFSET a remplacé la fonction INDEX dont nous parlions dans l’article précédent ? Par conséquent, si la fonction OFFSET peut être utilisée à la place de la fonction Index, alors la fonction OFFSET peut également réaliser le processus « taille unique ». Jetons un coup d'œil au processus de production d'un « menu déroulant » complexe. 🎜🎜Étape 1 : Utilisez la fonction OFFSET pour extraire la formule unique "Taiwan Balm"🎜🎜🎜🎜Cette formule est relativement longue et est répertoriée comme suit : 🎜🎜Fonction de la cellule D2 : 🎜🎜=IFERROR(OFFSET($A$1,SMALL(IF( LIGNE ($A$2 :$A$27)-1=MATCH($A$2 :$A$27,$A$2 :$A$27,0),ROW($1 :$20),9^9),<br> ROW (D1)),"")🎜🎜La formule du baume du tigre n'est pas le sujet dont nous allons parler aujourd'hui, nous n'entrerons donc pas dans les détails. L'important est de faire savoir à tout le monde que la fonction OFFSET peut également réaliser un tel effet de duplication. 🎜🎜Étape 2 : Utilisez la fonction OFFSET dans le gestionnaire de noms pour établir une source de données🎜🎜Nous pouvons utiliser la combinaison de touches Ctrl+F3 pour ouvrir la fenêtre du gestionnaire de noms, puis créer un nouveau nom. Le nom est défini sur "Zone". " et la position de référence est "D2 :D15", comme indiqué ci-dessous : 🎜🎜🎜🎜Puis sélectionnez la cellule G1, appuyez sur Alt+D+L pour ouvrir la boîte de paramètres de validation des données, sélectionnez "Séquence" dans Autoriser, entrez "=Zone" dans Source, comme indiqué dans la figure ci-dessous : 🎜 🎜🎜 🎜Cliquez sur le bouton OK, puis le menu déroulant de notre cellule G1 est créé. Mais le problème se pose également. Nous constaterons qu’il existe de nombreuses options vides, ce qui n’est pas ce dont nous avons besoin. 🎜🎜🎜🎜Certains étudiants que je dirais, sélectionnez simplement D2:D5 dans le gestionnaire de noms. Oui, mais si de nouvelles données apparaissent dans la zone de la colonne A, il y aura moins de données dans le menu déroulant, nous utilisons donc toujours la fonction OFFSET pour résoudre ce problème en ce moment. 🎜🎜Modifiez la position de référence de "area" dans le gestionnaire de noms : 🎜🎜🎜🎜=OFFSET(Menu déroulant dynamique !$D$1,1,,COUNTA(Menu déroulant dynamique !$D$2:$D$15)-COUNTBLANK( Menu déroulant dynamique ! $D$2 :$D$15),1)🎜

Étant donné que la seule valeur de notre colonne D est obtenue à l'aide d'une formule, la "cellule vide" à l'intérieur n'est pas nominalement "vide", mais vide obtenue par la formule, elle ne peut donc pas être transmise directement COUNTIF(D2:D15,"" ) méthode pour obtenir le nombre de cellules avec des valeurs. Par conséquent, nous avons d'abord utilisé la fonction COUNTBLANK (comptage des cellules vides) pour compter le nombre de cellules vides, puis la fonction COUNTA pour compter le nombre de cellules non vides, et enfin soustrait les deux pour obtenir le nombre de cellules avec des valeurs. . En utilisant le résultat obtenu comme quatrième paramètre de la fonction OFFSET (le nombre de lignes développées dans la nouvelle zone), l'effet de référencement dynamique de données valides est obtenu. Comme le montre l'image ci-dessous :

Si un nouveau nom de région est ajouté à la colonne A, de nouvelles options seront également ajoutées au menu déroulant de G1. Jetons un coup d'œil à l'effet, je crois. ce dont vous avez besoin.

Conseil 5 : Utilisation de la fonction OFFSET dans les graphiques

Je pense que tout le monde connaît le graphique ci-dessus. Les étudiants qui travaillent auront de l'expérience dans la création de graphiques. Sélectionnez la zone A1:B10 dans l'image ci-dessus, accédez à la barre d'outils - "Insérer" - Graphique à colonnes pour compléter le contenu de notre légende.

Si nous supprimons une ligne de données, il y aura une légende de série de moins dans le graphique à colonnes. Cependant, si nous ajoutons une ligne de données, nous devons modifier la plage de la source de données du graphique pour afficher le graphique correct. Mais nous ne pouvons pas le changer à chaque fois, sinon nous perdrons l’intention initiale d’utiliser Excel de manière efficace et rapide.

À l'heure actuelle, nous pouvons encore apprendre de la fonction OFFSET pour résoudre le problème :

Étape 1 : Utilisez la fonction OFFSET pour créer des noms personnalisés pour la "colonne de date" et la "colonne de quantité" respectivement

Gestionnaire de noms, qui nous avons présenté ci-dessus, juste Plus rien à dire. Sélectionnez "Colonne de date" et définissez-la comme suit :

Fonction de position de référence :

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

Comme il n'y a pas de cellules vides obtenues par la formule dans les données d'origine, il n'est pas nécessaire d'utiliser la fonction Countblank ici , et utilisez la fonction CountA directement pour les statistiques. Recherchez le nombre de cellules non vides et utilisez-le comme quatrième paramètre de la fonction OFFSET (le nombre de lignes dans la nouvelle zone). A2:A1000 représente ici une zone absolument vaste, garantissant que le contenu nouvellement saisi se situe dans cette plage.

Sélectionnez « Colonne de quantité » et créez un nom personnalisé pour la quantité de la même manière, comme suit :

Étape 2 : Utilisez le nom dans la zone du graphique

C'est la clé du graphique dynamique OFFSET. L'endroit où ajouter le nom est très important.

Dans la zone de dessin, sélectionnez n'importe quelle colonne, et dans la barre d'édition, vous pouvez voir la fonction d'écriture de l'icône (est-ce la première fois que vous savez que les graphiques ont aussi des fonctions). Modifions ici la portée de la référence.

Il suffit de changer la partie zone.

• Série de graphiques ! $A$2 :$A$10

• Série de graphiques !$B$2 :$B$10

Remplacez simplement ces deux parties rouges par des noms personnalisés. Ne les remplacez jamais "Série de graphiques !$. A$2:$A$10" est remplacé dans son ensemble !

Après l'avoir remplacé et appuyé sur Entrée, la fonction s'affichera comme indiqué ci-dessus. OFFSET.xlsx est le nom de notre classeur. L'effet est le suivant :

D'autres types de graphiques sont également disponibles. Vous pouvez l'essayer pour approfondir votre impression.

【Note de l'éditeur】

Les cinq paramètres de la fonction OFFSET ne sont pas difficiles à retenir si vous en comprenez le sens. Sa valeur de retour peut être utilisée comme référence à d'autres fonctions. De même, d'autres fonctions dont la « valeur de retour est au format numérique » peuvent également être utilisées comme paramètres de la fonction OFFSET, afin que nos données puissent se déplacer d'elles-mêmes.

Cette fonction joue un rôle indispensable dans les fonctions Excel. Surtout lorsque nous devons utiliser la modélisation Excel, cette fonction est souvent utilisée pour référencer des zones dynamiques et traiter automatiquement les données. Je recommande fortement aux étudiants d'en utiliser davantage. Prendre le temps d'apprendre ce sera. d'un grand bénéfice pour votre futur processus horloger.

Recommandations d'apprentissage associées : Tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!