Résultats Auto-Format Excel Groupby et Pivotby avec formatage conditionnel

Apprenez à transformer vos données en rapports de résumé clairs et colorés à l'aide de formules GroupBy et Pivotby d'Excel, améliorées avec la mise en forme conditionnelle.

Vous avez probablement entendu parler des deux ajouts récents aux fonctions dynamiques d'Excel: Groupby et Pivotby. Ces fonctions excellent dans le regroupement et le résumé des données, mais les résultats semblent souvent clairs et difficiles à comprendre en raison d'un manque de formatage. Pour une meilleure lisibilité et une meilleure présentation, vous pouvez, bien sûr, formater manuellement les éléments clés, tels que les en-têtes, les sous-totaux et les totaux. Cependant, comme le tableau résultant est dynamique, ces éléments peuvent se déplacer pour s'adapter aux modifications de l'ensemble de données d'origine. Naturellement, vous ne voudriez pas reformater les données chaque fois qu'elle change. Heureusement, vous pouvez automatiser ce processus avec la mise en forme conditionnelle, qui met à jour dynamiquement en fonction des critères que vous définissez.

• Comment utiliser le formatage conditionnel avec les formules Groupby et Pivotby
• Résultats de groupe de groupes conditionnels
• Résultats de pivotby en format conditionnellement

Comment appliquer la mise en forme conditionnelle aux résultats de groupe et pivotby

Pour formater automatiquement les résultats de vos formules GroupBy ou Pivotby, vous devrez configurer plusieurs règles de mise en forme conditionnelle en fonction des formules. Les étapes sont universelles pour toutes les règles, que vous mettiez en évidence les en-têtes, les sous-totaux ou les totaux. La variation se trouve dans une formule particulière utilisée pour identifier chaque élément et les détails de formatage. Veuillez suivre ces recommandations de près pour garantir des résultats optimaux:

1. Sélectionnez la plage au format . Commencez par sélectionner tous les résultats renvoyés par votre formule GroupBy ou Pivotby. Si votre ensemble de données d'origine est susceptible de changer et que vous prévoyez des ajouts futurs, étendez votre sélection pour inclure des lignes vides supplémentaires ci-dessous. De cette façon, lorsque plus de données seront ajoutées, la mise en forme conditionnelle s'appliquera automatiquement.
2. Accéder à la mise en forme conditionnelle . Dans l'onglet Home , cliquez sur Formatage conditionnel > Nouvelle règle…
3. Créez une règle basée sur la formule . Dans la nouvelle fenêtre de règle de formatage , sélectionnez Utiliser une formule pour déterminer les cellules à formater . Ensuite, entrez la formule dans la zone correspondante.
4. Choisissez des formats . Appuyez sur le bouton Format pour ouvrir les options de formatage. Ici, vous pouvez sélectionner les formats souhaités qui seront appliqués en fonction de votre règle.
5. Enregistrer la règle . Confirmez vos paramètres en cliquant sur OK autant de fois que nécessaire, qui enregistrera et appliquera votre nouvelle règle de mise en forme conditionnelle.

Pour des instructions détaillées, voyez comment définir la règle de mise en forme conditionnelle Excel avec la formule.

Formule Excel Groupby avec formatage conditionnel

Cet exemple montre comment rendre la sortie de la fonction GroupBy plus lisible en mettant en évidence les éléments principaux, tels que la ligne d'en-tête, les sous-Totostaux et les totaux.

Sélectionnez la plage appliquée pour la mise en forme conditionnelle

Dans cet exemple, la formule GroupBy renvoie un tableau dynamique s'étendant de F3 à H16. Cependant, pour nos règles de formatage conditionnel, nous sélectionnons la plage F3 à H32. Cela inclut des lignes vides supplémentaires pour s'adapter à tous les futurs ajouts de données, en s'assurant que le formatage s'applique automatiquement à mesure que de nouvelles données sont ajoutées au tableau source.

Avec une plage appropriée sélectionnée, cliquez sur Formatage conditionnel > Nouvelle règle… Pour configurer la première règle.

Maintenant, voyons exactement les formules et les options de formatage que nous pouvons utiliser.

Formater la ligne d'en-tête

La mise en forme de la ligne d'en-tête de vos résultats groupby est vitale pour la distinguer du reste de vos données.

Pour identifier la ligne d'en-tête, vous pouvez utiliser une formule qui recherche un mot ou une phrase spécifique unique aux titres, qui n'apparaissent pas ailleurs dans l'ensemble de données. Voici deux approches:

Formule de correspondance exacte

Utilisez cette méthode si vos en-têtes contiennent des termes cohérents uniques. Par exemple, si "Type de projet" est le premier en-tête du tableau retourné, vous pouvez l'identifier en recherchant le texte "Type de projet" dans la colonne F:

=$F3="Project type"

Formule de correspondance partielle

Si votre en-tête peut varier (par exemple, "type de projet" ou "nom de projet"), utilisez la fonction de recherche pour une correspondance partielle:

=SEARCH("project", $F3)

Comme la fonction de recherche est insensible à la casse, vous pouvez taper votre texte dans n'importe quel cas de lettre.

Note. Quelle que soit la formule pour laquelle vous optez, n'oubliez pas d'utiliser une référence de cellules mixtes (comme $ f3). Cela verrouille la colonne et permet au numéro de ligne de s'ajuster, garantissant que le formatage est appliqué de manière cohérente sur toute la ligne.

Formatage

Une fois que vous avez configuré votre formule, procédez pour appliquer un formatage en gras aux en-têtes. Dans la boîte de dialogue des cellules de format , accédez à l'onglet Font et sélectionnez Bold sous les styles de police .

De plus, vous voudrez peut-être ajouter une ligne colorée sous la ligne d'en-tête . Pour cela, accédez à l'onglet Border , où vous choisissez le style de ligne et la couleur souhaités et l'appliquez-le à la bordure de la cellule inférieure.

Ce choix de formatage simple mais efficace améliore considérablement la lisibilité et l'apparence professionnelle de vos résultats groupby.

Mettre en surbrillance

L'amélioration de la visibilité des sous-totaux dans les résultats Groupby peut rendre votre analyse de données plus intuitive et accessible. Pour y parvenir, vous devrez identifier avec précision les lignes sous-totales. En règle générale, ces lignes contiennent des valeurs dans la colonne de regroupement de premier niveau ( type de projet dans cet exemple) et les colonnes agrégées (telles que les revenus ), tandis que les cellules de la colonne de regroupement secondaire (comme le statut ) sont vides.

Formule

Pour construire une formule appropriée, vous pouvez utiliser la fonction et la fonction qui vérifie les deux conditions - si la colonne F ( type de projet ) n'est pas vide et la colonne G ( statut ) est vide:

=AND($F3"", $G3="")

Sachez que la formule ci-dessus formara également la grande ligne totale car elle remplit les deux conditions. Pour affiner la formule pour mettre en évidence les sous-totaux tout en excluant le grand total, vous pouvez ajouter un autre test logique:

=AND($F3"", $G3="", $F3"grand total")

Formatage

Pour appliquer une couleur d'arrière-plan à ces lignes identifiées, accédez à l'onglet Remplir dans la boîte de dialogue des cellules du format . De là, vous pouvez sélectionner votre couleur préférée, qui sera appliquée aux lignes sous-totales.

Facultativement, vous pouvez également formater le texte des sous-totaux en italique en gras en choisissant cette option dans l'onglet Font .

Formater la grande rangée totale

Il est crucial de porter attention au grand total pour rendre le résumé clé de vos données immédiatement perceptibles.

Formule

Pour identifier la grande ligne totale, faites une formule similaire à celle utilisée pour la ligne d'en-tête, mais cette fois, vous recherchez le texte "Grand Total". La formule est insensible à la casse, vous pouvez donc taper le texte dans tous les cas:

=$F3="grand total"

Dans le formatage conditionnel Excel, les références cellulaires sont relatives à la cellule supérieure gauche dans la plage appliquée. Par conséquent, nous écrivons à nouveau une formule pour la cellule F3 en utilisant une référence mixte - colonne absolue et ligne relative.

Formatage

Pour mieux distinguer la grande rangée totale, nous allons changer deux éléments - police et bordure cellulaire:

• Pour souligner le grand texte total, accédez à l'onglet de police et sélectionnez Bold sous les styles de police .
• Pour ajouter une ligne au-dessus de la grande ligne totale, passez à l'onglet Border . Ici, sélectionnez votre style de ligne et votre couleur préférés, puis appliquez la bordure supérieure pour créer une séparation claire des autres données.

Résultats de groupe à formater conditionnellement

Les résultats de la formule GroupBy, améliorés avec la mise en forme conditionnelle, ne sont pas seulement bien organisés mais aussi esthétiquement agréables et intuitifs pour naviguer.

Note. Lors de la mise en forme de la sortie GroupBy dans vos feuilles de travail, n'oubliez pas d'ajuster correctement les références dans des formules de formatage conditionnel pour correspondre à la disposition de votre ensemble de données spécifique. Pour plus d'informations, voir les références cellulaires dans la mise en forme conditionnelle Excel.

Formule Excel Pivotby avec formatage conditionnel

Comme vous le savez, Excel Tivottables est automatiquement formaté pour mettre en évidence les en-têtes, les totaux et les sous-totaux. Pour cet exemple, nous avons créé une formule PIVOTBY et Ticottable ce groupe et agrégé les données source de la même manière. Et maintenant, nous allons essayer de reproduire le format du tableau pivot en utilisant la mise en forme conditionnelle pour obtenir un look similaire pour la sortie de la formule Pivotby.

Avant de créer une règle de mise en forme conditionnelle, sélectionnez les résultats de votre formule Pivotby. Facultativement, incluez quelques cellules vierges ci-dessous pour formater automatiquement toutes les nouvelles données qui pourraient être ajoutées plus tard. Dans notre cas, nous sélectionnons la plage G3: M32, puis cliquez sur Formatage conditionnel > Nouvelle règle… Pour commencer à créer une règle pour la ligne d'en-tête.

Mettre en surbrillance la ligne d'en-tête

La première étape consiste à faire ressortir la ligne d'en-tête.

Formule

Dans le réseau dynamique renvoyé par la formule Pivotby, les deux premières cellules de la ligne d'en-tête sont vides. Vous pouvez vérifier ces deux conditions en utilisant la formule suivante:

=AND($G3="", $H3="")

Pour que le formatage s'applique correctement à toute la ligne, n'oubliez pas de verrouiller les colonnes et de permettre aux numéros de ligne de s'ajuster en utilisant des références de cellules mixtes comme $ G3 et $ H3.

Formatage

Pour que les en-têtes puissent visuellement, envisagez d'appliquer trois éléments de formatage différents:

• Format texte en gras . Cela rend le texte d'en-tête proéminent.
• Ajoutez une ligne ci-dessous . Formatez la bordure de la cellule inférieure avec une couleur sélectionnée pour créer une séparation claire.
• Souligner . Réglez une couleur de remplissage pour la ligne d'en-tête pour la rendre visuellement distincte.

Mettre en surbrillance

Si votre formule Pivotby est configurée pour afficher des sous-totaux, il est logique de les formater pour une meilleure visibilité et organisation.

Formule

La formule pour identifier les lignes sous-totales vérifie si une cellule de la colonne G n'est pas vide et une cellule de la colonne H est vide. De plus, il garantit que ce n'est pas la grande rangée totale, qui est censée être formatée différemment.

=AND($G3"", $H3="", $G3"grand total")

Formatage

Pour le formatage des sous-totaux, vous pouvez appliquer une combinaison de styles:

• Texte audacieux . Appliquez une mise en forme en gras au texte sous-total pour le rendre plus visible.
• Ligne en dessous . Ajoutez une ligne sous la ligne sous-totale pour les séparer visuellement des données qui suit.

Formater la grande rangée totale

La grande ligne totale est le résumé clé de vos données, il doit donc être mis en évidence en évidence pour référence rapide.

Formule

Pour identifier la grande ligne totale, vérifiez simplement si une cellule contient exactement ce texte:

=$G3="grand total"

Notez que la formule fait référence à la cellule supérieure gauche dans la plage appliquée, même si le grand total est dans la dernière rangée du tableau de retour.

Formatage

Pour souligner la grande rangée totale, appliquez le formatage suivant:

• Texte audacieux . Rendre le grand texte total audacieux pour souligner son importance.
• Ligne au-dessus . Ajoutez une ligne au-dessus de la grande ligne totale pour la séparer des données ci-dessus.
• Couleur d'arrière-plan . Appliquez une couleur de remplissage distincte sur la grande rangée totale pour le faire ressortir.

Résultats de Pivotby à formater conditionnellement

Avec l'application de la mise en forme conditionnelle, la sortie de votre formule Pivotby devient visuellement comparable à un poivable, ce qui le rend vraiment agréable et informatif.

Alors que nous concluons notre article, j'espère que vous vous sentirez prêt à utiliser ces techniques de mise en forme conditionnelle pour faire de vos formules Groupby et Pivotby raconter une histoire convaincante et avoir l'air incroyable.

Classeur de pratique pour télécharger

Groupby et pivotby avec formatage conditionnel (fichier .xlsx)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!