La fonction OFFSET d'Excel ralentira le calcul de votre classeur. Il existe une meilleure alternative: une syntaxe inhabituelle d'INDEX.
Ceci est une astuce de niche. Il existe une fonction incroyablement flexible appelée OFFSET. Il est flexible car il peut pointer vers une plage de taille différente qui est calculée à la volée. Dans l'image ci-dessous, si quelqu'un change la liste déroulante # Qtrs dans H1 de 3 à 4, le quatrième argument de OFFSET s'assurera que la plage se développe pour inclure quatre colonnes.
Les gourous du tableur détestent OFFSET car c'est une fonction volatile. Si vous accédez à une cellule totalement indépendante et entrez un nombre, toutes les fonctions OFFSET seront calculées. Même si cette cellule n'a rien à voir avec H1 ou B2. La plupart du temps, Excel fait très attention à ne perdre du temps qu'à calculer les cellules à calculer. Mais une fois que vous avez introduit OFFSET, toutes les cellules OFFSET, plus tout ce qui est en aval de l'OFFSET, commence à calculer après chaque changement dans la feuille de calcul.
J'étais en train de juger la finale ModelOff 2013 à New York lorsque quelques-uns de mes amis australiens ont signalé une solution de contournement bizarre. Dans la formule ci-dessous, il y a deux-points avant la fonction INDEX. Normalement, la fonction INDEX ci-dessous renvoie le 1403 à partir de la cellule D2. Mais lorsque vous placez un signe deux-points de chaque côté de la fonction INDEX, il commence à renvoyer l'adresse de cellule D2 au lieu du contenu de D2. C'est sauvage que cela fonctionne.
Pourquoi est-ce important? INDEX n'est pas volatil. Vous obtenez tous les avantages flexibles d'OFFSET sans les recalculs chronophages encore et encore.
J'ai appris cette astuce pour la première fois de Dan Mayoh à Fintega. Merci à Access Analytic pour avoir suggéré cette fonctionnalité.
Regarder la vidéo
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2048 -: INDEX remplacera un OFFSET volatil!
Hé, je balado tous mes conseils de ce livre, cliquez sur ce «i» dans le coin supérieur droit pour accéder à la playlist!
D'accord, OFFSET est une fonction incroyable! OFFSET nous permet de spécifier une cellule dans le coin supérieur gauche, puis d'utiliser des variables pour définir à partir de là le nombre de lignes vers le bas, le nombre de lignes au-dessus, puis de définir une forme, d'accord. Donc, ici, si je veux additionner, ou faire une moyenne de, disons 3/4, cette formule ici examinera la formule. L'OFFSET dit que nous partons de B2, à partir de Q1, nous descendons de 0, au-dessus de 0, la forme fera 1 ligne de haut, et ce sera H1, en d'autres termes 3 cellules de large, d'accord. Donc, dans ce cas, tout ce que nous faisons vraiment est de changer le nombre de cellules que nous additionnons, nous recommençons toujours en B2, ou B3 ou B4 lorsque je copie vers le bas, puis il décide du nombre de cellules de large. D'accord, OFFSET est ce truc cool, il fait toutes sortes de fonctions incroyables, mais voici le tracas, il est volatile!Ce qui signifie que même si quelque chose ne se trouve pas dans la chaîne de calcul, Excel va prendre le temps de le recalculer, ce qui va ralentir les choses, d'accord.
Cette version étonnante d'INDEX, à droite, normalement si je demande l'INDEX de ces 4 cellules, 3, elle va renvoyer le numéro 1403. Cependant, lorsque je mets un deux-points soit avant ou après l'INDEX, quelque chose de très différent se produit, nous allons jeter un oeil à cette formule ici. Donc index des 4 cellules, laquelle je veux, je veux la 3ème, mais vous voyez qu'il y a un: juste là. Donc, nous allons toujours passer de B2: E2, et je vais vous montrer si nous allons à Formules, Evaluate Formula, d'accord, alors ici, il va calculer le nombre 3. Et ici, l'INDEX avec le: suivant à cela, au lieu de nous dire 1403, ce que INDEX calculerait normalement, il retournera $ D2, puis la MOYENNE fera la moyenne de ces 3. Absolument incroyable, la façon dont cela fonctionne et l'avantage supplémentaire, ce n'est pas volatile, d'accord,et cela peut même être utilisé pour remplacer un OFFSET incroyablement complexe.
Donc, ici, avec cet OFFSET, nous sommes basés sur ces valeurs. Si je choisis Q2 et Central, d'accord, ces formules utilisent MATCH et COUNTIF pour déterminer le nombre de lignes vers le bas, le nombre de colonnes, la hauteur, la largeur. Et puis l'OFFSET utilise ici toutes ces valeurs pour déterminer la médiane. Nous allons juste faire un test pour nous assurer que cela fonctionne, donc = MEDIAN de cette gamme bleue là-bas, mieux vaut 71, d'accord, et nous choisirons autre chose ici, Q3 et West, donc. Appuyez sur F2, je vais simplement faire glisser ceci et le redimensionner pour réécrire cette formule, appuyez sur Entrée, et cela fonctionne avec OFFSET.
Eh bien ici, en utilisant un INDEX, j'ai en fait un deux-points au milieu avec un INDEX sur le côté gauche et un INDEX sur le côté droit, regardez cette chose se calculer dans Evaluate Formula. Donc, cela commence, va évaluer, évaluer, et ici qu'INDEX est sur le point de le transformer en une adresse de cellule. Donc H16 est le 1er, Ouest, Q3, et sur le côté droit va évaluer, coupler plus, puis à droite, il passe à I20. Donc le cool, cool non volatile pour remplacer un OFFSET en utilisant la fonction INDEX. D'accord, cette astuce et bien d'autres dans ce livre, cliquez sur ce «i» dans le coin supérieur droit pour acheter le livre.
Bon récapitulatif: OFFSET, fonction géniale et flexible, une fois que vous maîtrisez, vous pouvez faire toutes sortes de choses. Pointez sur une cellule variable en haut à gauche, pointez sur une plage qui a une forme variable, mais qui est volatile, et donc ils calculent à chaque calcul. Excel effectue généralement un calcul intelligent ou recalcule les cellules à calculer, mais avec OFFSET, il sera toujours calculé. Au lieu de OFFSET, vous pouvez utiliser INDEX: ou: INDEX ou même INDEX: INDEX, chaque fois que INDEX a un deux-points à côté, il renverra une adresse de cellule au lieu de la valeur de cette cellule. Et l'avantage est que INDEX n'est pas volatile!
OK, je tiens à vous remercier d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2048.xlsm
