3 formules Excel folles qui font des choses étonnantes

• Productivité

Publicité

Les formules Excel peuvent faire presque n'importe quoi. Dans cet article, vous découvrirez la puissance des formules Microsoft Excel et de la mise en forme conditionnelle, à l'aide de trois exemples utiles.

Déverrouillez la feuille de triche "Essential Excel Formulas" maintenant!

Cela vous inscrira à notre newsletter

Creuser dans Microsoft Excel

Nous avons présenté différentes méthodes pour mieux utiliser Excel, par exemple en l’utilisant pour créer votre propre modèle de calendrier ou en tant qu’outil de gestion de projet.

Une grande partie de la puissance réside dans les formules et règles Excel que vous pouvez écrire pour manipuler automatiquement les données et les informations, quelles que soient les données que vous insérez dans la feuille de calcul.

Voyons maintenant comment utiliser des formules et d'autres outils pour mieux utiliser Microsoft Excel.

Mise en forme conditionnelle avec des formules Excel

L'un des outils que les gens n'utilisent pas assez souvent est la mise en forme conditionnelle. Si vous recherchez des informations plus avancées sur la mise en forme conditionnelle dans Microsoft Excel, veillez à consulter l'article de Sandy sur la mise en forme des données dans Microsoft Excel avec mise en forme conditionnelle.

À l'aide de formules Excel, de règles ou de quelques paramètres très simples, vous pouvez transformer une feuille de calcul en un tableau de bord automatisé.

Pour accéder à la mise en forme conditionnelle, il vous suffit de cliquer sur l'onglet Accueil et de cliquer sur l'icône de la barre d'outils Mise en forme conditionnelle .

Sous Mise en forme conditionnelle, il y a beaucoup d'options. La plupart d'entre elles sortent du cadre de cet article, mais la majorité d'entre elles concernent la mise en évidence, la coloration ou l'ombrage de cellules en fonction des données contenues dans cette cellule.

Il s’agit probablement de l’utilisation la plus courante de la mise en forme conditionnelle, telle que la transformation d’une cellule en rouge à l’aide de formules inférieures ou supérieures à. En savoir plus sur l'utilisation des instructions IF dans Excel.

L'un des outils de mise en forme conditionnelle les moins utilisés est l'option Jeux d'icônes, qui offre un ensemble d'icônes très utile que vous pouvez utiliser pour transformer une cellule de données Excel en icône d'affichage dans le tableau de bord.

Lorsque vous cliquez sur Gérer les règles, vous accédez au Gestionnaire de règles de mise en forme conditionnelle .

En fonction des données que vous avez sélectionnées avant de choisir le jeu d'icônes, la cellule indiquée dans la fenêtre du gestionnaire s'affiche avec le jeu d'icônes que vous venez de choisir.

Lorsque vous cliquez sur Modifier la règle, vous verrez la boîte de dialogue où la magie se produit.

C’est là que vous pouvez créer la formule logique et les équations qui afficheront l’icône de tableau de bord souhaitée.

Cet exemple de tableau de bord indique le temps consacré à différentes tâches par rapport au temps budgétisé. Si vous dépassez la moitié du budget, une lumière jaune s’affiche. Si vous avez complètement dépassé votre budget, ça deviendra rouge.

Comme vous pouvez le constater, ce tableau de bord montre que la budgétisation par temps n’est pas un succès.

Près de la moitié du temps est largement dépassé les montants budgétés.

Il est temps de se recentrer et de mieux gérer votre temps!

1. Utilisation de la fonction VLookup

Si vous souhaitez utiliser des fonctions Microsoft Excel plus avancées, voici une autre solution pour vous.

Vous connaissez probablement la fonction VLookup, qui vous permet de rechercher dans une liste un élément particulier dans une colonne et de renvoyer les données d'une colonne différente dans la même ligne que cet élément.

Malheureusement, la fonction nécessite que l'élément que vous recherchez dans la liste se trouve dans la colonne de gauche et que les données que vous recherchez se trouvent à droite, mais que se passe-t-il si elles sont permutées?

Dans l'exemple ci-dessous, si je souhaite trouver la tâche que j'ai effectuée le 25/06/2018 à partir des données suivantes?

Dans ce cas, vous effectuez une recherche dans les valeurs à droite et souhaitez retourner la valeur correspondante à gauche - en face de la façon dont VLookup fonctionne normalement.

Si vous lisez des forums d'utilisateurs professionnels Microsoft Excel, vous constaterez que de nombreuses personnes disent que cela n'est pas possible avec VLookup et que vous devez utiliser une combinaison des fonctions Index et Correspondance pour le faire. Ce n'est pas tout à fait vrai.

Vous pouvez faire en sorte que VLookup fonctionne de cette manière en imbriquant une fonction CHOOSE dans celle-ci. Dans ce cas, la formule Excel ressemblerait à ceci:

 "=VLOOKUP(DATE(2018, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" 

Cette fonction signifie que vous souhaitez rechercher la date dans la liste de recherche le 25/06/2013, puis renvoyer la valeur correspondante à partir de l'index de la colonne.

Dans ce cas, vous remarquerez que l'index de la colonne est «2», mais comme vous pouvez le voir, la colonne dans le tableau ci-dessus est en réalité égale à 1, n'est-ce pas?

C'est vrai, mais ce que vous faites avec la fonction «CHOISIR», c'est de manipuler les deux champs.

Vous attribuez des numéros «d'index» de référence à des plages de données, vous affectez des dates au numéro 1 et les tâches au numéro 2.

Ainsi, lorsque vous tapez «2» dans la fonction VLookup, vous faites en fait référence à l'index n ° 2 de la fonction CHOISIR. Cool, non?

Donc, maintenant, le VLookup utilise la colonne Date et renvoie les données de la colonne Tâche, même si Tâche est à gauche.

Maintenant que vous connaissez cette petite friandise, imaginez ce que vous pouvez faire d'autre!

Si vous essayez d'effectuer d'autres tâches de recherche de données avancées, veillez à lire l'article complet de Dann sur la recherche de données dans Excel à l'aide de fonctions de recherche.

2. Formule imbriquée pour analyser les chaînes

Voici encore une formule excentrique pour vous.

Il peut arriver que vous importiez des données dans Microsoft Excel à partir d'une source externe constituée d'une chaîne de données délimitées.

Une fois les données importées, vous souhaitez les analyser dans les composants individuels. Voici un exemple d’informations sur le nom, l’adresse et le numéro de téléphone, délimitées par le caractère «;».

Voici comment vous pouvez analyser ces informations à l'aide d'une formule Excel (voyez si vous pouvez suivre mentalement cette folie):

Pour le premier champ, pour extraire l'élément le plus à gauche (le nom de la personne), vous utiliseriez simplement une fonction LEFT dans la formule.

 "=LEFT(A2, FIND(";", A2, 1)-1)" 

Voici comment cette logique fonctionne:

• Recherche dans la chaîne de texte de A2
• Trouve le symbole délimiteur “;”
• Soustrait un pour l'emplacement correct de la fin de cette section de chaîne
• Récupère le texte le plus à gauche jusqu'à ce point

Dans ce cas, le texte le plus à gauche est «Ryan». Mission accomplie.

3. Formule imbriquée dans Excel

Mais qu'en est-il des autres sections?

Il existe peut-être des moyens plus simples de le faire, mais puisque nous voulons essayer de créer la formule Excel imbriquée la plus folle possible (qui fonctionne réellement), nous allons utiliser une approche unique.

Pour extraire les parties à droite, vous devez imbriquer plusieurs fonctions RIGHT pour saisir la section de texte jusqu'à ce que le premier symbole «;» apparaisse, puis exécuter à nouveau la fonction LEFT. Voici à quoi cela ressemble pour extraire le numéro de rue de l’adresse.

 "=LEFT((RIGHT(A2, LEN(A2)-FIND(";", A2))), FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2))), 1)-1)" 

Cela semble fou, mais ce n'est pas difficile à reconstituer. Tout ce que j'ai fait est de prendre cette fonction:

 RIGHT(A2, LEN(A2)-FIND(";", A2)) 

Et inséré à chaque endroit dans la fonction GAUCHE ci-dessus où il y a un «A2».

Cela extrait correctement la deuxième section de la chaîne.

Chaque section suivante de la chaîne nécessite la création d'un autre nid. Alors maintenant, prenez simplement la folle équation «DROITE» que vous aviez créée pour la dernière section, puis transmettez-la dans une nouvelle formule DROITE avec la formule DROITE précédente collée sur elle-même partout où vous voyez «A2». Voici à quoi cela ressemble.

 (RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))) 

Ensuite, vous prenez CETTE formule et vous la placez dans la formule GAUCHE originale partout où il y a un «A2».

La formule finale hallucinante ressemble à ceci:

 "=LEFT((RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), FIND(";", (RIGHT((RIGHT(A2, LEN(A2)-FIND(";", A2))), LEN((RIGHT(A2, LEN(A2)-FIND(";", A2))))-FIND(";", (RIGHT(A2, LEN(A2)-FIND(";", A2)))))), 1)-1)" 

Cette formule extrait correctement «Portland, ME 04076» de la chaîne d'origine.

Pour extraire la section suivante, répétez le processus ci-dessus.

Vos formules Excel peuvent être très volumineuses, mais vous ne faites que couper et coller de longues formules, créer de longs nids qui fonctionnent réellement.

Oui, cela répond à l'exigence de «fou». Mais soyons honnêtes, il existe un moyen beaucoup plus simple de réaliser la même chose avec une seule fonction.

Il suffit de sélectionner la colonne avec les données délimitées, puis sous l'élément de menu Données, sélectionnez Texte en colonnes .

Cela ouvrira une fenêtre dans laquelle vous pourrez scinder la chaîne par le délimiteur de votre choix.

En quelques clics, vous pouvez faire la même chose que la formule folle ci-dessus… mais où est le plaisir?

Devenir fou avec les formules Microsoft Excel

Donc là vous l'avez. Les formules ci-dessus prouvent à quel point une personne peut se laisser avoir par le haut lors de la création de formules Microsoft Excel pour accomplir certaines tâches.

Parfois, ces formules Excel ne sont pas vraiment le moyen le plus simple (ou le meilleur) d’accomplir quelque chose. La plupart des programmeurs vous diront de garder les choses simples, et c'est aussi vrai avec les formules Excel que n'importe quoi d'autre. Vous pouvez même utiliser des fonctionnalités intégrées telles que Power Query.

Si vous voulez vraiment utiliser Excel avec sérieux, consultez notre guide du débutant sur l’utilisation de Microsoft Excel. Guide du débutant sur Microsoft Excel Guide du débutant sur Microsoft Excel Utilisez ce guide du débutant pour commencer à utiliser Microsoft Excel. Les conseils de base relatifs aux feuilles de calcul présentés ici vous aideront à apprendre Excel par vous-même. Lire la suite . Il contient tout ce dont vous avez besoin pour commencer à augmenter votre productivité avec Excel. Après cela, assurez-vous de consulter notre feuille de calcul des fonctions essentielles d'Excel. Feuille de calcul des formules et des fonctions Microsoft Excel essentielles. Lire la suite .

En savoir plus sur: Microsoft Excel, Microsoft Office 2016, Tableur.