Excel life hacks pour les personnes impliquées dans le reporting et le traitement des données

2024 Auteur: Malcolm Clapton | [email protected]. Dernière modifié: 2024-01-13 01:31

Dans cet article, Renat Shagabutdinov, directeur général adjoint de la maison d'édition Mann, Ivanov et Ferber, partage quelques astuces de vie Excel intéressantes. Ces conseils seront utiles à toute personne impliquée dans divers rapports, traitement de données et création de présentations.

Cet article contient des techniques simples pour simplifier votre travail dans Excel. Ils sont particulièrement utiles pour ceux qui sont engagés dans le reporting de gestion, préparent divers rapports analytiques basés sur des téléchargements de 1C et d'autres rapports, forment des présentations et des diagrammes à partir de ceux-ci pour la gestion. Je ne prétends pas être une nouveauté absolue - sous une forme ou une autre, ces techniques ont probablement été discutées sur les forums ou mentionnées dans des articles.

Alternatives simples à RECHERCHEV et RECHERCHEH, si les valeurs souhaitées ne sont pas dans la première colonne du tableau: RECHERCHE, INDEX + RECHERCHE

Les fonctions RECHERCHEV et RECHERCHEH ne fonctionnent que si les valeurs souhaitées se trouvent dans la première colonne ou ligne du tableau à partir duquel vous prévoyez d'obtenir des données.

Sinon, il y a deux options:

1. Utilisez la fonction RECHERCHE.

   Il a la syntaxe suivante: LOOKUP (lookup_value; lookup_vector; result_vector). Mais pour que cela fonctionne correctement, les valeurs de la plage view_vector doivent être triées par ordre croissant:

   

2. Utilisez une combinaison de fonctions MATCH et INDEX.

   La fonction MATCH renvoie le numéro ordinal d'un élément du tableau (avec son aide, vous pouvez trouver dans quelle ligne du tableau se trouve l'élément recherché), et la fonction INDEX renvoie un élément du tableau avec un numéro donné (que nous allons découvrir en utilisant la fonction MATCH).

   

   Syntaxe de la fonction:

   • SEARCH (search_value; search_array; match_type) - pour notre cas, nous avons besoin d'un type de correspondance "exact match", il correspond au nombre 0.

   • INDEX (tableau; numéro_ligne; [numéro_colonne]). Dans ce cas, vous n'avez pas besoin de spécifier le numéro de colonne, car le tableau se compose d'une ligne.

Comment remplir rapidement les cellules vides d'une liste

La tâche consiste à remplir les cellules de la colonne avec les valeurs en haut (afin que le sujet soit dans chaque ligne du tableau, et pas seulement dans la première ligne du bloc de livres sur le sujet):

Sélectionnez la colonne "Sujet", cliquez sur le ruban dans le groupe "Accueil", le bouton "Rechercher et sélectionner" → "Sélectionner un groupe de cellules" → "Cellules vides" et commencer à saisir la formule (c'est-à-dire mettre un égal signe) et référez-vous à la cellule en haut, simplement en cliquant sur la flèche vers le haut de votre clavier. Après cela, appuyez sur Ctrl + Entrée. Après cela, vous pouvez enregistrer les données reçues en tant que valeurs, car les formules ne sont plus nécessaires:

Comment trouver des erreurs dans une formule

Calcul d'une partie distincte d'une formule

Pour comprendre une formule complexe (dans laquelle d'autres fonctions sont utilisées comme arguments de fonction, c'est-à-dire que certaines fonctions sont imbriquées dans d'autres) ou pour trouver la source d'erreurs, vous devez souvent en calculer une partie. Il existe deux manières simples:

1. Pour calculer une partie d'une formule directement dans la barre de formule, sélectionnez cette partie et appuyez sur F9:

   

   Dans cet exemple, il y avait un problème avec la fonction SEARCH - les arguments y ont été échangés. Il est important de se rappeler que si vous n'annulez pas le calcul de la partie de la fonction et appuyez sur Entrée, la partie calculée restera un nombre.

2. Cliquez sur le bouton Calculer la formule dans le groupe Formules du ruban:

   

   Dans la fenêtre qui apparaît, vous pouvez calculer la formule étape par étape et déterminer à quelle étape et dans quelle fonction une erreur se produit (le cas échéant):

   

Comment déterminer de quoi une formule dépend ou se réfère

Pour déterminer de quelles cellules dépend une formule, dans le groupe Formules du ruban, cliquez sur le bouton Affecter les cellules:

Des flèches apparaissent pour indiquer de quoi dépend le résultat du calcul.

Si le symbole surligné dans l'image en rouge est affiché, alors la formule dépend des cellules sur d'autres feuilles ou dans d'autres livres:

En cliquant dessus, nous pouvons voir exactement où se trouvent les cellules ou plages d'influence:

À côté du bouton « Influencer les cellules » se trouve le bouton « Cellules dépendantes », qui fonctionne de la même manière: il affiche des flèches de la cellule active avec une formule aux cellules qui en dépendent.

Le bouton "Supprimer les flèches", situé dans le même bloc, permet de supprimer les flèches vers les cellules d'influence, les flèches vers les cellules dépendantes, ou les deux types de flèches à la fois:

Comment trouver la somme (nombre, moyenne) des valeurs des cellules de plusieurs feuilles

Supposons que vous ayez plusieurs feuilles du même type avec des données que vous souhaitez ajouter, compter ou traiter d'une autre manière:

Pour ce faire, dans la cellule dans laquelle vous souhaitez voir le résultat, entrez une formule standard, par exemple SUM (SUM), et spécifiez le nom des première et dernière feuilles de la liste de ces feuilles que vous devez traiter dans l'argument, séparé par deux points:

Vous recevrez la somme des cellules avec l'adresse B3 des feuilles "Data1", "Data2", "Data3":

Cet adressage fonctionne pour les feuilles situées régulièrement … La syntaxe est la suivante: = FUNCTION (first_list: last_list ! Range reference).

Comment créer automatiquement des phrases modèles

En utilisant les principes de base du travail avec du texte dans Excel et quelques fonctions simples, vous pouvez préparer des phrases modèles pour les rapports. Plusieurs principes de travail avec du texte:

• Nous concaténons le texte en utilisant le signe & (vous pouvez le remplacer par la fonction CONCATENATE, mais cela n'a pas beaucoup de sens).
• Le texte est toujours écrit entre guillemets, les références aux cellules avec du texte sont toujours sans.
• Pour obtenir le caractère de service "guillemets", utilisez la fonction CHAR avec l'argument 32.

Exemple de création d'une phrase modèle à l'aide de formules:

Résultat:

Dans ce cas, en plus de la fonction CHAR (pour afficher les cotations), on utilise la fonction SI, qui permet de modifier le texte selon qu'il y a une tendance positive des ventes, et la fonction TEXTE, qui permet d'afficher le nombre dans n'importe quel format. Sa syntaxe est décrite ci-dessous:

TEXTE (valeur; format)

Le format est spécifié entre guillemets, comme si vous entriez un format personnalisé dans la fenêtre Format de cellule.

Des textes plus complexes peuvent également être automatisés. Dans ma pratique, il y avait l'automatisation de commentaires longs mais routiniers au rapport de gestion au format « L'INDICATEUR a baissé / augmenté de XX par rapport au plan, principalement en raison de la croissance / baisse de FACTOR1 de XX, croissance / baisse de FACTOR2 de YY … avec une liste changeante de facteurs. Si vous écrivez souvent de tels commentaires et que le processus de rédaction peut être algorithmisé, il vaut la peine de créer une formule ou une macro qui vous épargnera au moins une partie du travail.

Comment stocker des données dans chaque cellule après la concaténation

Lorsque vous fusionnez des cellules, une seule valeur est conservée. Excel met en garde à ce sujet lorsque vous essayez de fusionner des cellules:

En conséquence, si vous aviez une formule en fonction de chaque cellule, elle cessera de fonctionner après les avoir combinées (erreur # N/A aux lignes 3-4 de l'exemple):

Pour fusionner des cellules tout en conservant les données dans chacune d'elles (vous avez peut-être une formule comme dans cet exemple abstrait; peut-être souhaitez-vous fusionner des cellules, mais conserver toutes les données pour l'avenir ou les masquer intentionnellement), fusionnez toutes les cellules de la feuille, sélectionnez-les, puis utilisez la commande Reproduire la mise en forme pour transférer la mise en forme vers les cellules que vous devez combiner:

Comment construire un pivot à partir de plusieurs sources de données

Si vous devez créer un pivot à partir de plusieurs sources de données à la fois, vous devrez ajouter l'"Assistant Tableau croisé dynamique et graphique" au ruban ou au panneau d'accès rapide, qui dispose d'une telle option.

Vous pouvez le faire comme suit: "Fichier" → "Options" → "Barre d'outils d'accès rapide" → "Toutes les commandes" → "Assistant Tableau croisé dynamique et graphique" → "Ajouter":

Après cela, une icône correspondante apparaîtra sur le ruban, en cliquant sur ce qui appelle le même assistant:

Lorsque vous cliquez dessus, une boîte de dialogue apparaît:

Dans celui-ci, vous devez sélectionner l'élément "Dans plusieurs plages de consolidation" et cliquer sur "Suivant". À l'étape suivante, vous pouvez sélectionner « Créer un champ de page » ou « Créer des champs de page ». Si vous souhaitez créer indépendamment un nom pour chacune des sources de données, sélectionnez le deuxième élément:

Dans la fenêtre suivante, ajoutez toutes les plages sur la base desquelles le pivot sera construit, et nommez-les:

Après cela, dans la dernière boîte de dialogue, spécifiez où le rapport de tableau croisé dynamique sera placé - sur une feuille existante ou nouvelle:

Le rapport de tableau croisé dynamique est prêt. Dans le filtre "Page 1", vous ne pouvez sélectionner qu'une seule des sources de données, si nécessaire:

Comment calculer le nombre d'occurrences du texte A dans le texte B ("Tarif MTS SuperMTS" - deux occurrences de l'abréviation MTS)

Dans cet exemple, la colonne A contient plusieurs lignes de texte, et notre tâche consiste à savoir combien de fois chacune d'elles contient le texte recherché situé dans la cellule E1:

Pour résoudre ce problème, vous pouvez utiliser une formule complexe composée des fonctions suivantes:

1. DLSTR (LEN) - calcule la longueur du texte, le seul argument est le texte. Exemple: DLSTR ("machine") = 6.
2. SUBSTITUT - remplace un texte spécifique dans une chaîne de texte par un autre. Syntaxe: SUBSTITUTE (texte; ancien_texte; nouveau_texte). Exemple: REMPLACER (« voiture »; « auto; » » = « mobile ».
3. UPPER - remplace tous les caractères d'une chaîne par des majuscules. Le seul argument est le texte. Exemple: UPPER (« machine ») = « VOITURE ». Nous avons besoin de cette fonction pour effectuer des recherches insensibles à la casse. Après tout, UPPER ("voiture") = UPPER ("Machine")

Pour trouver l'occurrence d'une certaine chaîne de texte dans une autre, vous devez supprimer toutes ses occurrences dans celle d'origine et comparer la longueur de la chaîne résultante avec celle d'origine:

DLSTR (« Tarif MTS Super MTS ») - DLSTR (« Tarif Super ») = 6

Et puis divisez cette différence par la longueur de la chaîne que nous recherchions:

6 / DLSTR (« MTS ») = 2

C'est exactement deux fois que la ligne "MTS" est incluse dans celle d'origine.

Il reste à écrire cet algorithme dans le langage des formules (notons par « texte » le texte dans lequel on cherche des occurrences, et par « recherché » - celui dont le nombre d'occurrences nous intéresse):

= (DLSTR (texte) -LSTR (SUBSTITUTE (UPPER (texte); UPPER (recherche), ""))) / DLSTR (recherche)

Dans notre exemple, la formule ressemble à ceci:

= (DLSTR (A2) -LSTR (REMPLACEMENT (SUPÉRIEUR (A2), SUPÉRIEUR ($ E $ 1), « »))) / DLSTR ($ E $ 1)