Vous ne savez peut-être pas comment utiliser la fonction OFFSET dans Excel

Saviez-vous que la fonction OFFSET de Microsoft Excel peut vous aider à faire bien d'autres choses, pas seulement des références de cellules ? Vous trouverez ci-dessous des façons uniques d’utiliser OFFSET dans Excel que vous ne connaissez peut-être pas.

La fonction OFFSET de Microsoft Excel est un outil flexible et puissant. En raison de sa complexité et du nombre de fonctions similaires, cette fonction Excel est malheureusement sous-utilisée.

Cette fonction relativement simple peut aider les utilisateurs à économiser du temps et des efforts. Cela simplifie l’obtention de points de données précis à partir d’un ensemble de données maître bien trié. Il permet également de créer simplement des rapports détaillés, des visualisations interactives et des tableaux de bord complexes.

Créer un ensemble de données de taille personnalisée

L'une des utilisations les plus courantes de la fonction OFFSET dans Excel consiste à collecter des ensembles de données redimensionnables. Avec un grand ensemble de données, on peut vouloir considérer seulement une petite partie des données à un moment donné.

Par exemple, pour fournir un rapport automatisé sur le total des ventes des employés, vous devez obtenir le total pour une ou plusieurs semaines données.

Écrire une fonction SOMME différente pour chaque total peut être assez simple. Cependant, l’utilisation de la fonction OFFSET sera plus facile à utiliser et plus rapide à réutiliser.

Tout d’abord, entrez la date à laquelle vous souhaitez commencer dans la cellule B6. Ensuite, entrez le nombre de jours pendant lesquels vous souhaitez exécuter le rapport dans B7. Ensuite, utilisez les fonctions simples SOMME et MOYENNE pour obtenir plus de données pour chaque employé sur la période sélectionnée :

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

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

Ajuster la date ou l'heure de début est aussi simple que modifier une cellule, mais vous pouvez faire glisser et redimensionner des groupes de données beaucoup plus facilement.

Permet de créer des rapports personnalisés

Utilisez la même méthode que ci-dessus pour créer un rapport complet en personnalisant les variables assez facilement. Vous pouvez faire en sorte que le premier argument de la fonction OFFSET fasse référence à une autre feuille. Cela vous permet de séparer la saisie des données et le reporting.

L'action ci-dessus permet également de faire glisser des éléments de données sélectionnés d'une feuille de calcul à une autre. De plus, il vous permet d'afficher uniquement les informations pertinentes pour le rapport en cours. La configuration d'un rapport affichant les totaux quotidiens en utilisant les mêmes données qu'auparavant est assez simple. Vous pouvez le faire sans récupérer de données pour des dates sans rapport.

La fonction de décalage suivante placée dans la cellule B5 récupérera le nombre de jours spécifié en B2. Il commence la semaine sélectionnée en B1 et affiche uniquement les données de la période concernée :

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

Il est possible de restreindre les entrées en B1 et B2 et de créer une liste déroulante dans Excel avec les semaines disponibles grâce à la validation des données.

Pagination de la feuille de calcul

La fonction OFFSET peut également rendre possible une pagination simple d'une feuille de calcul, une tâche souvent associée à l'utilisation de l'onglet Développeur Excel. Cela peut rendre le traitement de grands groupes de données plus simple et plus efficace.

Lorsque vous travaillez avec des feuilles de calcul contenant jusqu'à 30 000 lignes de données, il peut être difficile de parcourir toutes les données en même temps. Créez un menu déroulant qui accepte une plage de nombres de 1 à 300, vous permettant de séparer rapidement les données par page avec 100 résultats par page.

La fonction OFFSET a toujours son point d'ancrage placé dans la cellule de données supérieure. Cependant, le deuxième argument est le décalage vertical. Cette valeur est égale au numéro de page sélectionné moins 100. Le décalage est horizontal, le troisième argument peut être mis à 0.

Le 4ème argument est défini sur 100, le 5ème argument est le nombre de colonnes dans l'ensemble de données. Cette méthode rend le fractionnement et la gestion des tableaux de données extrêmement simples.

Vous trouverez ci-dessous un exemple de données paginées. Il comprend 500 informations enregistrées à partir d'un capteur sur une feuille et un rapport paginé sur l'autre feuille.

Le rapport utilise la validation des données dans la cellule B1 pour créer un menu déroulant avec des pages à l'intérieur. L'expression suivante dans la cellule A3 crée le corps du rapport :

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

Ci-dessus, vous trouverez quelques façons d'utiliser la fonction de décalage dans Excel que peu de gens connaissent mais qui sont vraiment utiles, surtout lorsque vous devez traiter de grandes quantités de données.

J'espère que l'article vous sera utile.