3 formules Excel folles qui font des choses étonnantes

Microsoft Excel est l’un des outils de tableur les plus puissants, avec une collection impressionnante d’outils et de fonctionnalités intégrés. Dans cet article, vous découvrirez à quel point les formules Excel et la mise en forme conditionnelle peuvent être puissantes, à l'aide de trois exemples utiles..

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 les 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

L'un des outils que les utilisateurs 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 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 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 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’allumera. 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 passé bien au-dessus des 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 veux 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 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 verrez de nombreuses personnes dire que cela n'est pas possible avec VLookup et que 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)"

Ce que cette fonction signifie, c'est que vous souhaitez trouver 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 - affectation des dates au numéro 1 et des tâches au 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 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 que vous avez importé les donné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 en utilisant 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'à 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) "

Cela semble 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 ((DROITE ((DROITE (A2, LEN (A2) -FIND ("; "A2")))), "LEN ((DROITE (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 diviser 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 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 rester simple, et c'est aussi vrai avec les formules Excel.

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 améliorer votre productivité avec Excel.

Explorer plus sur: Microsoft Excel, Microsoft Office 2016, Spreadsheet.