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

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.

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

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é.

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

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.

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

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.

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

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.

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

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.


Excel 2016 - Leçon 6 : Modifier la taille des colonnes, des lignes et des cellules dans Excel

Excel 2016 - Leçon 6 : Modifier la taille des colonnes, des lignes et des cellules dans Excel

Découvrez comment modifier la taille des colonnes, des lignes et des cellules dans Excel 2016 pour afficher vos données efficacement.

Comment utiliser la fonction HLOOKUP dans Excel

Comment utiliser la fonction HLOOKUP dans Excel

Apprenez comment utiliser la <strong>fonction HLOOKUP</strong> dans Excel pour rechercher des données efficacement. Découvrez des conseils et des exemples utiles !

Comment utiliser la fonction WORKDAY dans Microsoft Excel

Comment utiliser la fonction WORKDAY dans Microsoft Excel

Découvrez comment utiliser la fonction WORKDAY dans Microsoft Excel pour calculer efficacement les jours ouvrables en excluant week-ends et jours fériés.

Comment utiliser les fonctions VSTACK et HSTACK dans Excel

Comment utiliser les fonctions VSTACK et HSTACK dans Excel

Découvrez comment empiler et ajouter des données efficacement dans Excel avec les fonctions VSTACK et HSTACK. Améliorez votre productivité !

Comment utiliser la fonction NORMDIST dans Excel

Comment utiliser la fonction NORMDIST dans Excel

Découvrez comment utiliser la fonction NORMDIST dans Excel pour analyser les données statistiques, en suivant nos étapes simples et efficaces.

Comment utiliser la boucle do-while dans Excel VBA

Comment utiliser la boucle do-while dans Excel VBA

Vous souhaitez automatiser des tâches répétitives dans Excel VBA ? Découvrez comment utiliser une boucle Do-While pour exécuter des actions jusqu

Comment accéder et ouvrir un fichier Excel 97/2003 .xls dans Excel 2016, 2019 et 365 ?

Comment accéder et ouvrir un fichier Excel 97/2003 .xls dans Excel 2016, 2019 et 365 ?

Découvrez comment ouvrir danciens fichiers Excel avec Excel 365 (2016/2019).

Comment utiliser la fonction DSUM pour calculer des sommes avec des conditions complexes dans Excel

Comment utiliser la fonction DSUM pour calculer des sommes avec des conditions complexes dans Excel

Découvrez comment utiliser la fonction DSUM dans Excel pour calculer des sommes conditionnelles complexes efficacement.

Fonction Match dans Excel : Comment utiliser la fonction Match avec des exemples

Fonction Match dans Excel : Comment utiliser la fonction Match avec des exemples

Découvrez comment utiliser la <strong>fonction Match</strong> dans Excel pour le traitement de données. Apprenez avec des exemples pratiques.

Jouez à puzzle sur Excel

Jouez à puzzle sur Excel

MehmetSalihKoten, un utilisateur de Reddit, a créé une version entièrement fonctionnelle de Tetris dans Microsoft Excel.