3 Des formules Excel délirantes qui font des merveilles

Added 2019-01-01

Les formules Excel peuvent faire presque n'importe quoi. Dans cet article, vous découvrirez à quel point les formules Microsoft Excel et la mise en forme conditionnelle peuvent être puissantes, avec trois exemples utiles.

Creuser dans Microsoft Excel

Nous avons décrit différentes manières d’utiliser mieux Excel, comme l’utiliser pour créer votre propre modèle de calendrier.  ou en l'utilisant comme 10 puissants modèles de gestion de projet Excel pour le suivi 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. .

Avec l'utilisation 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 au formatage conditionnel, il suffit de cliquer sur le bouton Accueil  onglet, et cliquez sur le Mise en forme conditionnelle  icône de la barre d'outils.

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 foting conditionnel les moins utilisés est le Jeux d'icônes  Cette option offre un grand nombre d’icônes que vous pouvez utiliser pour transformer une cellule de données Excel en icône d’affichage du tableau de bord.

Lorsque vous cliquez sur Gérer les règles , ça vous amène 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 apparaît avec le jeu d’icônes que vous venez de choisir.

Lorsque vous cliquez sur Modifier la règle , vous verrez le 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 dépassez complètement votre budget, il deviendra rouge.

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

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

Temps de se recentrer et 7 Conseils Google Agenda pour une meilleure gestion du temps au bureau 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 d’une colonne et de renvoyer les données d’une colonne différente de 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 s’ils sont activés?

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 rencontrerez beaucoup de personnes, ce n'est pas possible avec VLookup et vous devez pour cela utiliser une combinaison des fonctions Index et Correspondance. 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), CHOISIR ({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 voir que la colonne dans le tableau ci-dessus est réellement 1, n’est-ce pas?

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

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

Ainsi, lorsque vous tapez «2» dans la fonction VLookup, vous vous référez au numéro d’index 2 dans la fonction CHOISIR. Cool, non?

Donc, maintenant le VLookup utilise le Rendez-vous amoureux  colonne et renvoie les données de la Tâche  colonne, même si la tâche est sur la 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 à consulter l’article complet de Dann sur 4 Fonctions de recherche dans Excel pour une recherche efficace dans les feuilles de calcul 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 en utilisant une formule Excel (voyez si vous pouvez suivre mentalement cette folie):

Dans 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 fonctionne cette logique:

• 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) "

Ça a l'air fou, mais ce n'est pas difficile à reconstituer. Tout ce que j'ai fait est de prendre cette fonction:

DROITE (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 l’équation folle «DROITE» que vous avez 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 originale de GAUCHE là où il y a un «A2».

La formule finale hallucinante ressemble à ceci:

"= GAUCHE ((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 ((DROITE (A2, LEN (A2) -FIND (";", A2)))) - FIND (";", (DROITE (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 le Les données  élément de menu, 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 le moyen le plus simple (ou le meilleur) d’accomplir quelque chose. La plupart des programmeurs vous diront de garder les choses simples, et cela est aussi vrai avec les formules Excel que n'importe quoi d'autre.

Aide-mémoire sur les formules et les fonctions Microsoft Excel essentielles Téléchargez cet aide-mémoire de formules Excel pour créer un raccourci vers le programme de feuille de calcul préféré du monde. Read Article