William Charles
0 
5161
410
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..
Creuser dans Microsoft Excel
Nous avons traité de différentes manières pour mieux utiliser Excel, par exemple en l’utilisant pour créer votre propre modèle de calendrier ou en l’utilisant comme outil de gestion de projet..
Une grande partie de la puissance réside dans les formules et les règles Excel que vous pouvez écrire pour manipuler automatiquement les données et les informations, quelles que soient les données insérées 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 mise au rouge d’une cellule à 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 le Jeux d'icônes option, qui offre un grand nombre d'icônes que vous pouvez utiliser pour transformer une cellule de données Excel en icône d'affichage de tableau de bord.
Lorsque vous cliquez sur Gérer les règles, ça va vous emmener à la 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 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 de votre choix..
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 va devenir rouge.
Comme vous pouvez le constater, ce tableau de bord montre que la budgétisation du 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 activé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 recherchez des valeurs à droite et vous souhaitez renvoyer 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), 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 la colonne dans le tableau ci-dessus est en réalité 1, à droite?
C'est vrai, mais ce que vous faites avec le “CHOISIR” la fonction manipule les deux champs.
Vous assignez une référence “indice” nombres en plages de données - attribution des dates à l'index numéro 1 et des tâches à l'index numéro 2.
Alors, quand vous tapez “2” dans la fonction VLookup, vous vous référez à l'index numéro 2 de la fonction CHOISIR. Cool, droit?
Donc, maintenant le VLookup utilise le Rendez-vous amoureux colonne et renvoie les données de la Tâche colonne, même si Task 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, consultez l'article complet de Dann sur la recherche de données dans Excel à l'aide des fonctions de recherche..
2. Formule imbriquée pour analyser les chaînes
Voici encore une formule excentrique pour vous.
Il peut y avoir des cas où vous importez des données dans Microsoft Excel à partir d’une source externe consistant en 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 “;” personnage.
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 de 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'à la première. “;” et exécutez à nouveau la fonction GAUCHE. 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”.
Ceci 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, vous prenez juste le fou “DROITE” l’équation que vous avez créée pour la dernière section, puis transmettez-la dans une nouvelle formule RIGHT avec la formule RIGHT précédente collée sur elle-même partout où vous la voyez “A2”. Voici à quoi ça 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 le placez dans la formule originale de GAUCHE partout 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” hors de la chaîne d'origine.
Pour extraire la section suivante, répétez le processus ci-dessus à nouveau.
Vos formules Excel peuvent devenir 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 fera apparaître une fenêtre où vous pourrez scinder la chaîne par le délimiteur de votre choix..
En quelques clics, vous pouvez faire la même chose que cette formule folle ci-dessus… mais où est le plaisir dans cela?
Devenir fou avec les formules Microsoft Excel
Donc là vous l'avez. Les formules ci-dessus prouvent à quel point une personne peut obtenir beaucoup de panique lorsqu'elle crée des 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.
Si vous voulez vraiment utiliser Excel avec sérieux, consultez notre guide du débutant pour utiliser Microsoft Excel. Guide du débutant pour Microsoft Excel Guide du débutant pour Microsoft Excel Utilisez ce guide pour 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. . Il contient tout ce dont vous avez besoin pour améliorer votre productivité avec Excel. Après cela, assurez-vous de consulter notre feuille de calcul des fonctions essentielles Excel. Formule et fonctions essentielles de Microsoft Excel Feuille de triche. Feuille de calcul des fonctions essentielles Microsoft Excel. Téléchargez cette feuille de calcul de formules Excel pour créer un raccourci vers le tableur préféré du monde.. .