Mis à jour par Ryan Dube en octobre 2017.
J'ai toujours cru que Microsoft Excel est l'un des outils logiciels les plus puissants sur le marché. Ce n'est pas seulement le fait que c'est une application de feuille de calcul puissante. Microsoft Excel lui-même possède une impressionnante collection d'outils et de fonctionnalités intégrés. Dans cet article, je prévois de vous montrer comment les formules puissantes et la mise en forme conditionnelle peuvent être, avec trois exemples utiles.
Nous avons couvert un certain nombre de façons différentes de faire un meilleur usage d'Excel, comme l'utiliser pour créer votre propre modèle de calendrier Comment faire un modèle de calendrier dans Excel Comment créer un modèle de calendrier dans Excel Vous aurez toujours besoin d'un calendrier. Nous vous montrons comment créer votre propre modèle personnalisé gratuitement avec Microsoft Excel. Lire la suite, l'utiliser comme un outil de gestion des objectifs 10+ Modèles Excel utiles pour la gestion de projet et le suivi 10+ Modèles Excel utiles pour la gestion de projet et les gabarits de gestion sont indispensables aux chefs de projet. Nous vous montrons comment vous pouvez répliquer des projets réussis en utilisant des modèles prêts à l'emploi dans Microsoft Excel et au-delà. Lire la suite, et d'autres façons uniques que vous pouvez utiliser pour gérer votre vie. Il suffit de lire ces articles et vous verrez à quel point Microsoft Excel peut être puissant.
Une grande partie de la puissance réside vraiment derrière les formules et les règles que vous pouvez écrire pour manipuler les données et les informations automatiquement, quelles que soient les données que vous insérez dans la feuille de calcul.
Creuser dans Microsoft Excel
Avec les bonnes informations, vous pouvez créer un système qui calcule et recalcule automatiquement les résultats et les rapports à partir de ces données brutes. Aujourd'hui, j'aimerais creuser un peu plus loin et vous montrer comment utiliser certaines des formules sous-jacentes et d'autres outils pour mieux utiliser Microsoft Excel.
Refroidir la mise en forme conditionnelle avec des formules
L'un des outils que les gens n'utilisent pas assez souvent est le formatage conditionnel. Si vous recherchez des informations plus avancées sur la mise en forme conditionnelle dans Microsoft Excel, vérifiez l'article de Sandy sur le formatage des données dans Microsoft Excel avec mise en forme conditionnelle Formatage automatique des données dans des feuilles Excel avec formatage automatique Formatage automatique des données dans des feuilles Excel avec formatage conditionnel La fonction de mise en forme conditionnelle d'Excel vous permet de mettre en forme des cellules individuelles dans une feuille de calcul Excel en fonction de leur valeur. Nous vous montrons comment l'utiliser pour diverses tâches quotidiennes. Lire la suite .
Avec l'utilisation de formules, de règles ou de quelques paramètres très simples, vous pouvez transformer une feuille de calcul en un tableau de bord automatisé qui vous donne un aperçu rapide des informations contenues dans la feuille de calcul.
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 formatage conditionnel, il y a un tas d'options. La plupart d'entre elles sortent du cadre de cet article particulier, mais la majorité d'entre elles concernent la mise en évidence, la coloration ou l'ombrage de cellules en fonction des données de cette cellule. C'est probablement l'utilisation la plus courante du formatage conditionnel - faire des choses comme rendre une cellule rouge en utilisant des formules logiques inférieures ou supérieures aux formules. Brad Jones décrit comment utiliser beaucoup de ces derniers dans son article sur la création d'un tableau de bord Excel Visualisez vos données et faites vos feuilles de calcul conviviales avec un tableau de bord Excel Visualisez vos données et faites vos feuilles de calcul conviviales avec un tableau de bord Excel Parfois, un simple format de feuille de calcul ne s'engage pas assez pour rendre vos données accessibles. Un tableau de bord vous permet de présenter vos données les plus importantes dans un format facile à digérer. Lire la suite - vaut la peine de vérifier si c'est votre objectif.
L'un des outils de mise en forme conditionnelle les moins utilisés est l'option des ensembles d'icônes, qui offre un grand nombre d'icônes que vous pouvez utiliser pour transformer une cellule de données Excel en une icône d'affichage de tableau de bord.
J'ai découvert cela une fois que j'ai mis à jour Microsoft Office 13+ Raisons pour lesquelles vous devez effectuer une mise à niveau vers Microsoft Office 2016 13+ Raisons pour lesquelles vous devez effectuer une mise à niveau vers Microsoft Office 2016 Microsoft Office 2016 est là et il est temps de prendre une décision. La question de productivité est - devriez-vous mettre à niveau? Nous vous donnons les nouvelles fonctionnalités et les nouvelles raisons de vous aider ... Lire la suite et utilisé la mise en forme conditionnelle dans Microsoft Excel pour la première fois.
J'ai vérifié les jeux d'icônes et j'ai vu ces témoins lumineux à DEL que je n'avais vraiment vus que dans certains des écrans d'automatisation d'usine que j'avais programmés par le passé. Lorsque vous cliquez sur "Gérer les règles", vous accédez au Gestionnaire de règles de mise en forme conditionnelle. Selon les données que vous avez sélectionnées avant de choisir le jeu d'icônes, vous verrez la cellule indiquée dans la fenêtre Gestionnaire, avec le jeu d'icônes que vous venez de choisir.
Lorsque vous cliquez sur "Modifier la règle ...", vous voyez 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 du tableau de bord que vous voulez. Dans mon exemple, je surveille le temps passé sur différentes tâches par rapport à mon temps budgété. Si j'ai dépassé la moitié de mon budget, je veux un feu jaune et si le budget est dépassé, je veux qu'il devienne rouge.
Comme vous pouvez le voir, je ne budgétise pas trop mon temps. Près de la moitié de mon temps est consacré à ce que j'ai budgétisé.
Il est temps de recentrer et de mieux gérer mon temps Utilisez la règle de gestion du temps 80/20 pour hiérarchiser vos tâches Utilisez la règle de gestion du temps 80/20 pour hiérarchiser vos tâches Comment maximiser votre temps? Si vous avez déjà priorisé, délégué et encore lutté pour tout faire, vous devriez essayer la règle 80/20, également connue sous le nom de principe de Pareto. Lire la suite !
Rechercher des éléments avec la fonction VLookup
D'accord, peut-être que ce n'est pas assez fou pour toi. Peut-être que vous n'êtes pas si excité par les formules logiques simples allumer et éteindre les lumières. Si vous aimez utiliser des fonctions Microsoft Excel plus avancées, j'en suis allé un autre pour vous.
Vous connaissez probablement la fonction VLookup, qui vous permet de rechercher dans une liste pour 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 soit dans la colonne de gauche, et les données que vous recherchez sont sur la droite, mais que se passe-t-il si elles sont commutées?
Dans l'exemple ci-dessous, que se passe-t-il si je veux trouver la tâche que j'ai effectuée le 25/06/2013 à partir des données suivantes?
Dans ce cas, vous recherchez les valeurs sur la droite, et vous voulez retourner la valeur correspondante sur la gauche - à l'opposé de la façon dont VLookup fonctionne normalement Recherche Excel Spreadsheets Faster: Remplacez VLOOKUP avec INDEX et MATCH Search Excel Spreadsheets Faster: Replace VLOOKUP Avec INDEX et MATCH Vous utilisez toujours VLOOKUP pour rechercher des informations dans votre feuille de calcul? Voici comment INDEX et MATCH peuvent fournir une meilleure solution. Lire la suite . Si vous lisez les forums pro-utilisateur de Microsoft Excel, vous constaterez que beaucoup de gens disent que ce n'est pas possible avec VLookup et que vous devez utiliser une combinaison de fonctions Index et Match pour ce faire. Ce n'est pas entièrement vrai.
Vous pouvez obtenir VLookup de cette façon en imbriquant une fonction CHOOSE dans celui-ci. Dans ce cas, la formule ressemblerait à ceci:
"=VLOOKUP(DATE(2013, 6, 25), CHOOSE({1, 2}, E2:E8, A2:A8), 2, 0)" Cette fonction signifie que vous souhaitez trouver la date 25/06/2013 dans la liste de recherche, 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 fait 1, non?
C'est vrai, mais ce que vous faites avec la fonction "CHOISIR", c'est manipuler les deux champs. Vous affectez des numéros d'index de référence à des plages de données - en attribuant les dates à l'index numéro 1 et les tâches à l'index numéro deux. Ainsi, lorsque vous tapez "2" dans la fonction VLookup, vous faites référence à l'index numéro 2 dans la fonction CHOOSE. Fou, hein?
Alors maintenant, le VLookup utilise la colonne Date et renvoie les données de la colonne Task, même si Task est sur la gauche. Maintenant que vous connaissez cette petite friandise, imaginez ce que vous pouvez faire!
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 Trouver n'importe quoi dans votre feuille de calcul Excel avec des fonctions de recherche Feuille de calcul Excel, CTRL + F ne vous obtiendra que si loin. Soyez intelligent et laissez les formules faire le travail dur. Les formules de recherche gagnent du temps et sont faciles à appliquer. Lire la suite .
Formules imbriquées insensibles pour analyser les chaînes
Comme vous pouvez le voir, j'essaie d'être un peu plus fou quand je pars, parce que je sais que certains d'entre vous pensent: «Eh bien, ce n'est pas fou du tout !!» Je sais que vos standards sont élevés. J'essaie de vivre à leur hauteur. Voici une formule plus folle pour vous.
Dans certains cas, vous pouvez importer 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 introduit les données, vous voulez analyser ces données dans les composants individuels. Voici un exemple d'informations de nom, d'adresse et de numéro de téléphone délimitées par le caractère ";".
Voici comment vous pouvez analyser cette information en utilisant une formule Excel (voir si vous pouvez suivre mentalement avec cette folie):
Pour le premier champ, pour extraire l'élément le plus à gauche (le nom de la personne), vous utiliseriez simplement une fonction GAUCHE dans la formule.
"=LEFT(A2, FIND(";", A2, 1)-1)" Cela recherche la chaîne de texte à partir de A2, trouve le symbole délimiteur ";", soustrait un pour l'emplacement correct de la fin de cette section de chaîne, puis saisit le texte le plus à gauche à ce point. Dans ce cas, c'est "Ryan". Mission accomplie.
Imbrication de formules Excel
Mais qu'en est-il des autres sections? Eh bien, pour extraire les parties sur la droite, vous devez imbriquer plusieurs fonctions DROITE pour saisir la section de texte jusqu'à ce que le premier symbole ";", et exécutez à 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 a l'air fou, mais ce n'est pas difficile à assembler. Tout ce que j'ai fait est de prendre cette fonction:
"RIGHT(A2, LEN(A2)-FIND(";", A2))" Et inséré dans 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, vous prenez juste l'équation "DROIT" fou que vous aviez créé pour la dernière section, et passez ensuite cela 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 ç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 placez-le dans la formule originale gauche partout où il y a un "A2". La formule finale de l'esprit-flexion 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 à nouveau. Vos formules peuvent devenir vraiment farfelues, mais tout ce que vous faites est de couper et de coller de longues formules, de faire de longs nids qui fonctionnent vraiment bien!
Oui, cela répond à l'exigence de "fou", mais soyons honnêtes ... il y a un moyen beaucoup plus simple d'accomplir la même chose avec une seule fonction. Sélectionnez simplement la colonne avec les données délimitées, puis sous l'élément de menu Données, sélectionnez Texte pour les colonnes . Cela fera apparaître une fenêtre où vous pouvez diviser la chaîne par n'importe quel délimiteur que vous voulez.
En quelques clics, vous pouvez faire la même chose que la formule ci-dessus ... mais qu'est-ce qui vous amuse?
Obtenir fou avec Microsoft Excel
Donc là vous l'avez. Les formules ci-dessus prouvent à quel point une personne peut obtenir une over-the-top lors de la création de formules Microsoft Excel pour accomplir certaines tâches. Parfois, ces formules ne sont pas réellement le moyen le plus facile (ou le meilleur) d'accomplir les choses. La plupart des programmeurs vous diront de rester simple, et c'est aussi vrai avec les formules Excel 16 Formules Excel qui vous aideront à résoudre des problèmes réels 16 Formules Excel qui vous aideront à résoudre les problèmes de la vie réelle Le bon outil est la moitié du travail. Excel peut résoudre les calculs et traiter les données plus rapidement que vous ne pouvez trouver votre calculatrice. Nous vous montrons les principales formules Excel et démontrons comment les utiliser. Lire plus comme toute autre chose.
Ces formules et techniques étaient-elles assez sauvages? Avez-vous des trucs de formule étonnants qui composent votre boîte à outils de super-utilisateur Microsoft Excel? Partagez vos commentaires et commentaires dans la section des commentaires ci-dessous!
Crédit d'image: kues / Depositphotos