Formulaires et fonctions Excel Partie 8 : Formatage basé sur les formules

1. Introduction à la mise en forme basée sur les formules: tout le monde bienvenue à la section 8 formule formatant les sections un peu différent parce que plutôt que de couvrir une nouvelle catégorie de formules et d'apprendre de nouvelles fonctions et syntaxe, nous allons en fait utiliser les formules que nous avons déjà appris dans un nouveau contexte dans ce cas pour créer des règles de formatage basées sur des formules personnalisées. Nous allons donc commencer par une section rapide sur la création et la modification de vos règles de mise en forme . Ensuite, on va aller dans une série de démos. Demo One consiste à mettre en évidence toutes les autres lignes en utilisant la fonction mod. La deuxième démo va couvrir le formatage des cellules en fonction de la valeur d'une autre cellule, ce qui est très utile. La troisième démo va être sur le formatage rose en utilisant des fonctions statistiques. Et enfin, je vais vous montrer comment formater rose en utilisant les fonctions de texte sur. Et ce ne sont que quelques exemples. La beauté de ceci est que tout comme il y a un nombre infini de façons d'utiliser les formules que nous avons apprises, nous pouvons également appliquer ces formules à un nombre infini d'options de formatage personnalisées. Donc vraiment cool choses créatives que vous pouvez faire ici dernier mais pas moins comme toujours, aux fichiers à télécharger, a obtenu notre excellent pour les analystes Formule formatage. Fichier PDF. Allez-y et imprimez cela, puis la section huit formule formatage fichier Excel. Encore une fois, ce sera une section vraiment pratique. Ah, beaucoup de travail avec Excel. Alors allez-y et ouvrez ça, familiarisez-vous et allons-y. 2. Créer et gérer les règles de base aux formules: donc je veux prendre une minute pour parler de formatage conditionnel et Excel. C' est une de mes choses préférées. J' adore le formatage conditionnel parce qu'une fois que vous l'avez appris, c' est assez facile à faire, et ça souffle absolument les gens. Je le jure. Il suffit de gifler une échelle de couleurs sur une colonne et les gens penseront que vous êtes une rock star absolue . Beaucoup d'entre vous sont probablement familiers avec les règles de mise en forme conditionnelle de stock. Donc, mettre en évidence les règles de vos barres de données, échelles de couleur jeux d' icônes qui est si génial, si bien et bon. Mais laissons ça aux Enfants. Parlons de la façon d'aller voyous et de créer nos propres formats conditionnels. Et faisons-le en utilisant des règles basées sur des formules. C' est un peu l'un de ces trucs Excel de niveau supérieur qui ouvre vraiment la porte pour faire des choses assez incroyables. Donc, tout d'abord, voulez juste couvrir la création et l'édition de ces règles basées sur la formule essentiellement dans l'onglet Accueil . Si vous cliquez sur la liste déroulante de mise en forme conditionnelle, vous verrez ce menu à gauche. Les 5 premières options ici sont ce genre de règles de formatage conditionnel de base que nous allons laisser pour une autre fois. Ces trois options ici. C' est notre bac à sable. C' est là qu'on va jouer. Donc vous pouvez simplement créer une nouvelle règle ici, et cette petite boîte pop-up apparaîtra. Et ce qui nous intéresse, c'est cette dernière option Ici, sous la fenêtre Sélectionner un type de règle, utilisez une formule pour déterminer les ventes à mettre en forme. Donc, une fois que nous cliquons, cette fenêtre apparaît ici, et c'est là que nous écrivons notre formule, donc c'est un peu plus compliqué parce que nous n'avons pas la petite boîte d'aide pour nous guider à travers. Mais à ce stade, je veux dire, on est dans la section 8. Mais à ce stade, je veux dire, Vous devriez être des experts. Maintenant, nous allons devoir compter sur notre intellect, notre mémoire et notre instinct pur. Donc ça va être amusant. Donc c'est là que vous écrivez votre formule, qui veut dire essentiellement, si cette formule est vraie, alors je veux me former aux cellules. En tant que tel, vous cliquez sur le bouton de format, choisissez vos options, vous remplissez votre bordure, votre police, toutes ces bonnes choses, puis appuyez sur. OK, donc ici, nous avons présenté juste un exemple de règle de mise en forme basée sur une formule. Dans cet exemple, nous formons les cellules dans les colonnes par H avec un Phil vert et une police en gras, mais seulement lorsque le nom de l'état est égal à la valeur et à vendre. Tu vois deux, c'est quoi cette liste déroulante ? Vendre ici ? Eso. Une chose à noter est que lorsque vous travaillez avec des règles de mise en forme basées sur des formules, les types de référence deviennent incroyablement importants. Il est donc essentiel de comprendre quand utiliser un fixe par rapport à un pour utiliser des références relatives. Donc je vais passer beaucoup de temps à parler de ça. Alors nous allons sauter pour exceller et voir réellement à quoi cela ressemble. J' ai ouvert la section huit formule formatage Excel File. Allez-y et faites la même chose. Jetons un coup d'oeil à ce que nous travaillons avec deux onglets ici. Le premier devrait sembler familier. C' est notre exemple de vérificateur de prix que nous avons construit lors de la section de référence de recherche, en particulier lors de la récupération des fonctions d'indice et de correspondance. Le deuxième onglet est appelé Données d'état. ne s'agit que de données brutes au niveau de l'état et de l'année montrant la population totale et la population étudiante . Donc ce sont les deux onglets que nous allons utiliser pour les démos des prochaines conférences . Donc, si je veux créer une nouvelle règle de formatage basée sur la formule. La première chose que je veux faire est de sélectionner les cellules où je veux que la règle soit appliquée. Dans ce cas, je vais juste sélectionner siège 3 37 J'ai pris l'onglet Accueil. mise en forme conditionnelle a supprimé la nouvelle règle. Utilisez une formule pour déterminer les ventes à mettre en forme. Et voici où je vais taper ma formule. Et une fois que c'est écrit, je vais cliquer sur le format. Et voici où je vais sélectionner mon format réel à appliquer lorsque mes formules sont vraies. Donc, annulez de cela et puis une fois que j'ai des règles écrites juste aller dans le formatage conditionnel à nouveau pour gérer les règles. Et puis je peux soit regarder les règles pour la sélection actuelle, qui est en ce moment vendre l'âge de 10 ans. Ou je peux dire, montrez-moi toutes les règles de cette feuille de calcul actuelle. heure actuelle, aucune règle n'est appliquée, mais au fur et à mesure que nous commençons à les écrire, vous verrez cette liste commencer à remplir, et c'est là que vous pouvez modifier vos règles. Vous avez connecté les formules dans vos règles. Vous pouvez modifier les paramètres de mise en forme ou supprimer vos règles ensemble. Hum, donc tu y vas. Préparez-vous pour la démo numéro un 3. Mettre en évidence toute ligne à l'aide de la fonction MOD: Allons de l'avant et plongons dans notre première démo. Alors allez-y et ouvrez le fichier Excel Section 8. On va commencer sur l'onglet Données d'état sur. Notre défi sera de créer une règle de mise en forme basée sur une formule qui met en évidence toutes les autres lignes de ces données ici. Je suis donc j'ai peut-être menti dans la dernière conférence quand j'ai dit que vous avez appris toutes les formules que vous devez utiliser pour cela. On va en apprendre un de plus, et ça s'appelle mod M o D. Et tout ce que mod fait c'est retourne le reste quand on divise un nombre par un autre. Donc, par exemple, si un type est égal à mod, et alors vous verrez que c'est assez simple formule seulement deux composants, le nombre et votre diviseur. Donc si je mets un quatre et un pour le fermer, je vais obtenir un zéro parce que quatre divise uniformément en deux. Si je change ce 4 à 5 maintenant, je reçois un reste d'un parce que tout nombre impair divisé par deux retournera un reste d' un. C' est donc la fonction mod. Et ce sera un élément essentiel pour créer cette règle de formatage basée sur une formule. Donc, la première étape consiste à créer le tableau de données que je veux. Cette règle à appliquer dans ce cas sélectionnerait un pour contrôler la flèche de décalage vers le bas deux d 52 . Donc j'ai tout mon tableau sélectionné maintenant dans la ville natale. Je vais cliquer sur la liste déroulante de mise en forme conditionnelle, créer une nouvelle règle, utiliser une formule pour déterminer qui vend à mettre en forme. Ainsi, chaque règle de mise en forme basée sur une formule commence par le signe égal. J' allais commencer avec ma fonction de mod et vous pouvez voir comment excels ne pas vraiment m' aider ici, peu par moi-même. Assurez-vous donc de savoir exactement à quoi ressemble la syntaxe de votre formule avant de passer à cette étape . Donc, le premier morceau du mod est mon premier numéro, et ce nombre va juste être la fonction de ligne avec une parenthèse ouverte et fermée. Et ça va descendre et dire pour chaque rangée séquentielle, voyons quel est le numéro de route, et le deuxième numéro sera aussi. Donc, fondamentalement, ce que je fais est un simple problème de division où je divise le numéro de route actuel ligne par ligne, divisé par deux. Et la dernière partie de cette fonction sera égale à un. Encore une fois, en divisant le numéro de ligne actuel par deux et en vérifiant quand le reste est un. En d'autres termes, cela va avoir un impact sur les lignes de Lee Odd. Et quand c'est le cas, je vais choisir le formatage que je veux appliquer. Alors appuyez sur le format. Dans ce cas, tout ce que je veux vraiment, c'est un Phil vert clair. Alors je pourrais aussi, tu vas financer cette affaire à la frontière ? Juste un Phil vert va très bien. Dès que j'appuie sur OK, retournez ici une presse, OK, encore une fois. Et voilà. La règle a été appliquée, et comme vous pouvez le voir, elle applique la ligne impair de mise en forme personnalisée, car lorsque chaque nombre impair est divisé par deux, il renvoie un. Donc, cela semble fonctionner correctement et chacun voulait modifier cette règle. Revenons dans les règles de gestion de formatage conditionnel. Voilà ma règle. Cliquez dessus et maintenant si je change cela à zéro, je devrais voir le même formatage appliqué aux lignes paires parce que dans ces cas, la fonction mod retournera zéro parce qu'il n'y a pas de reste, alors appuyez à nouveau sur OK et je vais juste appuyer sur cette bouton appliquer ici. Et voilà. Vous voyez le formatage se déplacer vers le haut, seules les lignes paires. Um, dernière minute, au moins si je veux supprimer ça, tu pourrais appuyer, supprimer et appliquer. Et maintenant, il est parti. Donc, voilà, en mettant en évidence toutes les autres lignes en utilisant une combinaison des fonctions mod et ligne. 4. Formater les cellules en fonction de la valeur d'une autre cellule: Bon, Alors commençons sur la démo numéro deux formatage des cellules en fonction de la valeur d'une autre cellule . C' est amusant. Et c'est vraiment utile juste parce qu'il y a tellement de possibilités d'appliquer des règles similaires dans toutes sortes de contextes différents. Donc, dans cet exemple, nous allons utiliser le vérificateur de prix que nous avons construit lors de la recherche dans la section de référence . Et si vous vous souvenez, nous avons utilisé une fonction de correspondance d'index pour retourner un prix d'un certain tableau dans ce cas C trois G sept, basé sur des sélections pour le produit et la taille. Donc, ce que nous voulons faire ici est de créer une règle de formatage personnalisée qui recherche le prix sélectionné dans eat 10 et le met en évidence dans le tableau de C 33 g sept eso. Ce que nous allons faire est de commencer par simplement sélectionner le tableau que nous voulons que la règle soit appliquée à la maison mise en forme conditionnelle nouvelle règle. Nous allons utiliser une formule pour déterminer les ventes à formater. Et maintenant, comme toujours, nous allons commencer avec un égal et allons juste sélectionner la cellule supérieure gauche dans mon tableau . Donc vendre C trois set égal à vendre e 10 et je vais laisser ça comme ça pour l'instant juste pour vous montrer ce qui se passe si nous laissons les références fixes. Donc, dans le cas de cette forme, c'était vrai. Quel formatage voulons-nous appliquer ? Eh bien, nous allons choisir une belle couleur jaune vif et une police audacieuse juste pour faire le pock de sélection, donc impressionner. Ok, ça va appliquer la règle. Et comme vous pouvez le voir, rien ne s'est passé. Donc, en ce moment, nous avons extra petit pull sélectionné, qui retourne le prix de 30$. Donc, en théorie, nous nous attendrions à auto C sept à B, affichant les règles de formatage personnalisées que nous venons de définir. Que se passe-t-il si nous choisissons des chaussettes extra petites, qui est de 4$ qui ensembles vendent E 10 égal à C trois ? Maintenant, nous voyons la règle de formatage appliquée à l'ensemble du tableau. Et pour comprendre ce qui se passe, revenons à la mise en forme conditionnelle des règles de gestion. n'y a rien dans notre sélection actuelle, qui est Selby 10. Donc, je dois changer ceci à cette feuille de travail. Il y a ma règle, je vais la sélectionner et avoir à elle. Et maintenant, en pensant à ce que cette règle nous dit, c'est juste dire à chaque fois que la cellule C 3 et Onley siège 3 puisque c'est fixe, c'est égal à vendre, manger 10. Appliquez cette mise en forme et rappelez-vous que nous l'appliquons à l'ensemble du tableau que nous avions sélectionné. Donc Excel fait effectivement son travail parce que dans ce cas, quand j'ai des chaussettes extra petites sélectionnées, C trois fait, en fait, également 10. Cette règle de mise en forme est donc appliquée. Ça ne marche pas tout à fait comme on le veut. Et la clé est de supprimer les références fixes ici, ce qui permettra au C trois de déplacer n'importe quelle cellule, importe quelle ligne, n'importe quelle colonne dans le tableau que nous avons défini. Maintenant, c'est une référence relative. Donc maintenant, quand j'appuie sur OK et appuyez sur, appliquer Onley, les quatre sont sélectionnés, et maintenant pour tester cela, nous pouvons choisir un certain nombre de différents produits et tailles et regarder que surligné, donc déplacer en conséquence. Donc, voilà, juste un exemple de formatage et de cellule basé sur la valeur d'une autre cellule 5. Formater les cellules en utilisant les fonctions statistiques: D' accord. Donc, pour cette prochaine démo, je veux vous montrer quelques exemples de la façon dont vous vous conformez aux cellules basées sur des règles ou des fonctions statistiques . Revenons donc dans le classeur Section Eight Excel dans l'onglet données d'état. Hum, et le premier exemple que je veux vous montrer est de mettre en évidence des cellules dans une colonne spécifique qui répondent à une certaine valeur. Donc, dans ce cas, soulignons simplement les cellules où la population totale est supérieure à cinq millions. Pour ce faire, vous sélectionnez C two control shift vers le bas vers C 52. Je vais sélectionner une nouvelle règle de mise en forme conditionnelle. Utilisez une formule maintenant que je vais faire est de taper égal. Si deux est supérieur à cinq 1.000.000. Maintenant. La seule pièce que je dois changer avant de soumettre ceci est de convertir la référence de ligne de fixe en relative. Donc la mer est OK. En fait, il voit la seule colonne dans ce tableau. Mais la ligne deux doit être relative parce que j'ai besoin de vérifier la valeur de la population dans chaque ligne, pas seulement écrit à quelqu'un supprimer le dollar, signer là, puis sélectionner mon format. Et faisons juste une sorte de ah, ombre orangée légère ici Appuyez sur. OK, je vais appuyer. OK, tu y vas. Maintenant que nous voyons que juste dans cette colonne que j'ai appliqué la règle à toutes les valeurs environ cinq millions sont maintenant mis en évidence. Je peux également sélectionner la même plage, aller à la mise en forme conditionnelle. C' est à une nouvelle règle en utilisant une formule cette fois, je vais dire égale à C deux à nouveau Rendre la référence de ligne relative égale au maximum de la colonne C. Et ce que je veux faire ici est fondamentalement identifier quelle ligne et la colonne C est le maximum et le formater en conséquence. Choisissez donc une règle de mise en forme. Allons à la frontière. Et, euh, donnons-lui un aperçu complet. Presse frontalière. OK, je suis d'accord maintenant, Comme vous pouvez le voir, la valeur de 33,8 millions, qui est la population de la Californie, est la plus grande valeur dans la colonne C. je suis d'accord maintenant, Comme vous pouvez le voir, la valeur de 33,8 millions, qui est la population de la Californie, est la plus grande valeur dans la colonne C. qui reçoit cette salle de formatage. Hum, donc allons de l'avant et allons gérer les règles pour toute la feuille de calcul et supprimons ces deux-là . Et maintenant, ce que je veux vous montrer, c'est que nous prenons la même règle où la population totale est supérieure à 5 millions, mais plutôt que de simplement mettre en évidence les cellules. Dans la colonne C, je voudrais mettre en évidence toute la ligne dans laquelle la population totale est supérieure à cinq millions d'habitants. Ce que je peux faire est fondamentalement juste, la même règle. Mais avant de sélectionner le tableau entier de huitième ou D plutôt que seulement C deux à C 52 eso nouveau formatage conditionnel nouvelle règle utiliser la formule et la même formule exacte égale C deux avec une référence de ligne relative supérieure à cinq millions. Formatez-le avec un spectacle orange clair. Et maintenant, comme vous pouvez le voir, nous identifions toujours les routes. Les populations totales sont-elles supérieures à cinq millions ? La seule différence est que dans ce cas, la mise en forme est appliquée à toute la ligne. Parce que quand nous avons créé la salle de formatage basée sur la formule, notre sélection comprenait toutes les colonnes d'un à D. Um, et maintenant nous pouvons aller de l'avant et modifier très facilement cette règle, donc je vais aller de l'avant et la modifier. Donc, disons si nous le voulions, plutôt que de sélectionner Rose, où la population totale est grande dans cinq millions. Si vous vouliez sélectionner Rose où la population étudiante totale est supérieure à cinq millions, je voudrais juste changer la mer à une touche D OK et appliquer. Et comme vous pouvez le voir, il n'y a qu'un seul État où la population étudiante était supérieure à 5 millions et c' était la Californie. Donc, enfin, mais pas le moindre, je peux aussi créer des formules statistiques supplémentaires ici. Donc, plutôt que de simplement vérifier supérieur ou inférieur ou égal à, je peux éditer et enregistrer. Vérifions si le rapport des étudiants à la population totale donc d à diviser par C deux avec un parent ou une inférence de référence. La maladie est supérieure à 25% 250,25 Maintenant, j'appuie sur l'approvisionnement. Comme vous pouvez le voir, il est mis à jour pour inclure beaucoup plus de lignes. Et pour vérifier cela, vous savez, nous pouvons ajouter une nouvelle colonne intitulée Student Top Pourcentage, qui est juste égal à D. Divisé par C, formater en pourcentage et l'appliquer vers le bas. Et puis, comme vous pouvez le voir sur Lee, qui a augmenté mieux que 25 ont appliqué le formatage, sorte que la règle semble fonctionner correctement. Hum, donc voici quelques exemples de la façon dont vous pouvez utiliser les fonctions statistiques que nous avons couvertes dans le cadre des règles de formatage basées sur des formules 6. Formater les cellules en utilisant les fonctions de texte et les opérateurs logistiques: Donc, pour la démonstration finale, je veux vous montrer comment vous vous conformez aux cellules basées sur des fonctions de texte ainsi que sur des opérateurs logiques . Je suis donc qu'on va retourner à l'onglet Données de l'Etat. J' ai éliminé toutes mes règles existantes étaient de commencer à partir de zéro. Et la première chose que je veux faire est juste de mettre en évidence tous les noms d'état qui commencent par la lettre I. Donc, je vais sélectionner un pour contrôler le décalage vers le bas à un 52 mise en forme conditionnelle Nouvelle règle. Utilisez une formule. Et maintenant, puisque je veux retourner la valeur du premier caractère que j'ai besoin d'utiliser, ils ont quitté la fonction. Donc, tapez égal à gauche ouvert les parenthèses. Sélectionnez un pour, mais autorisez le déplacement de la référence de rôle. Alors rendez-le relatif. Et maintenant, une petite note de côté Ici, vous avez peut-être remarqué que lorsque je me suis déplacé à travers ces formules assistent à cliquer pour choisir différentes positions par opposition à l'utilisation d'un clavier flèches. C' est parce que si vous utilisez les flèches, il essaie automatiquement d'insérer des références pour vous, ce qui peut être vraiment frustrant si vous ne voulez pas de références. Eso Ma recommandation est quand vous avez besoin de faire des choses comme changer certains caractères et une fonction juste pour utiliser votre souris et votre curseur pour essayer de sauter directement dans ce morceau de la fonction et de l'éditer directement. Vous avez probablement déjà rencontré ce problème et vous vous êtes rendu compte que cela pourrait être vraiment frustrant. Parfois, c'est un petit pourboire là-bas. Dans tous les cas, nous disons que la gauche d'un vers et le nombre de caractères regardaient n'est qu'un. Quelqu' un fait une virgule. Un proche. La parenthèse est égale à la lettre I entre guillemets. Alors c'est tout. Donc, la fonction est laissée d'un caractère 21 égal à I. Et donc, lorsque cette formule est vraie, le format sera, disons, gras avec un sac à main de fond bleu. OK, et voilà. C' est mis en évidence. Idaho, Illinois, Indiana et Iowa. Donc fonctionne correctement maintenant, un autre conseil. Si vous appelez la dernière conférence, nous appliquons à la fonction ou au format deux colonnes C, puis afin de l'appliquer à l'ensemble de la ligne , nous l'avons supprimée, fait une nouvelle sélection, puis réécrit la formule. Cette fois-ci, je veux te montrer un moyen plus rapide de faire la même chose. Et pour le faire, je vais m'occuper de la gestion des règles. Vérifiez cette feuille de calcul pour que je puisse voir tous sélectionner ma règle. Et puis vous voyez où il est dit s'applique à un deux à un 52. vais juste choisir le sélecteur, et je peux soit faire glisser un nouveau tableau. Ou il pourrait simplement cliquer et changer le A à D, puis quand il appuie, appliquer applique la salle de formatage tout le chemin à travers la colonne de par opposition à juste colonne un moyen beaucoup plus rapide de faire la même chose. C' est la première règle. Et si on veut devenir un peu plus compliqué et insérer des opérateurs logiquesici ? Et si on veut devenir un peu plus compliqué et insérer des opérateurs logiques Donc ce que je veux faire maintenant est de dire, Mettons en évidence la Rose où le nom de l'état commence par un I et se termine par un A. Donc nous sommes tous les deux Ces conditions sont vraies, donc nous allons gérer. Mes règles. Allez dans éditer celui-ci. Et si vous vous souvenez qu'un critère un et deux doit être vrai, j'ai besoin d'utiliser une déclaration et. Je vais commencer juste après le signe égal, prendre mon et ouvert la parenthèse. La fonction gauche est conditionnée, une dans une virgule de presse pour obtenir la condition, aussi. Et donc, si ma première condition est que le caractère le plus gauche est I, La deuxième condition que je dois rencontrer est que le plus droit caractère est une utilisation d'une bonne fonction ici. Même référence ici. Je vais juste faire deux, et je peux soit aller et sélectionner le A pour puis supprimer le signe du dollar. Ou, tu sais, je pourrais juste taper ça à la main aussi. Assez simple à, puis un seul droit. La plupart du caractère, apprentis proches Bruce est égal, puis minuscule A entre guillemets Fermer la princesse et maintenant avoir et déclaration qui comprend deux composants à gauche. La plupart d'un caractère est égal à I, et le bon plus un caractère est égal à la minuscule A. J'ai appuyé sur OK et appliquer. Comme vous pouvez le voir, la mise en forme est maintenant sur Lee s'appliquant aux deux états qui répondent à ce critère Indiana et Iowa. Donc juste quelques exemples aléatoires, mais encore une fois, un nombre pratiquement infini d'applications pour ce genre de choses. Donc, cela enveloppe la section de mise en forme conditionnelle basée sur la formule. N' hésitez pas à contacter si vous avez des questions. Ensuite, nous avons un rayon fonctions, alors préparez-vous 7. HOMEWORK: TRAVAIL : Formater par formule de la forme de la formule: D' accord, tu as terminé le chapitre 8. Tu es en train de naviguer. Vous avez terminé la section de mise en forme de formule. Maintenant, c'est l'heure de votre prochain devoir. Ouvrez le fichier d'exercice de devoirs Excel, dirigez-vous vers l'onglet Formatage de la formule et jetez un oeil aux instructions. Nous avons six étapes ici qui vont vous permettre de transformer cette plage de données à partir d'un 12 G 25 en utilisant des outils comme le peintre de format, règles de formatage conditionnel, les échelles de couleur, jeux d' icônes et ensuite une formule avancée basées sur des règles de mise en forme basées sur des valeurs et du texte. Bonne chance.