Une fonctionnalité intéressante de la fonction SUMIF dans Google Sheets est la possibilité de retourner un tableau bidimensionnel. Cela peut être très utile pour créer un résumé par catégorie. Découvrez comment SUMIF peut retourner plusieurs colonnes dans Google Sheets.
Dans ce tutoriel, je vais utiliser SUMIF pour créer un résumé par catégorie et par date/mois/trimestre/année. Je sais que peu de personnes recherchent spécifiquement des informations sur SUMIF de cette façon, mais je tiens à partager cette solution dynamique avec mes visiteurs réguliers.
Résumé par catégorie et date dans Google Sheets
Voici un exemple de données et de résumé par catégorie et date utilisant SUMIF (résultat de tableau bidimensionnel).
Vous pouvez utiliser cette approche pour créer un résumé par mois/trimestre/année basé sur SUMIF. Retrouvez la formule et l’explication de la formule ci-dessous.
Note:
Mes données d’exemple pour tous les exemples ci-dessous sont dans l’onglet « Ventes ». Les critères et la formule se trouvent dans l’onglet « Résumé ».
Comment utiliser SUMIF pour retourner plusieurs colonnes dans Google Sheets
Jetez un coup d’œil au résumé bidimensionnel généré par SUMIF dans l’exemple de résumé par catégorie et date ci-dessus. Dans ce résumé, la colonne A et la ligne 1 contiennent les clés de recherche.
La clé pour utiliser SUMIF et obtenir plusieurs colonnes est d’inclure ces clés de recherche dans la formule SUMIF.
Je vais expliquer l’utilisation de SUMIF avec des critères horizontaux et verticaux dans les sections suivantes.
Résumé par date et catégorie
Mes données d’exemple se trouvent dans l’onglet « Ventes », colonnes A à C. La formule se trouve dans l’onglet « Résumé », cellule B2. Avant d’expliquer la formule, examinons la syntaxe de la fonction SUMIF.
SUMIF(plage, critère, plage_somme)Ma formule SUMIF dans la cellule B2 retourne un tableau bidimensionnel. Pour cela, nous devons utiliser la fonction ArrayFormula avec SUMIF. Ainsi, SUMIF peut s’étendre sur plusieurs colonnes.
La syntaxe de SUMIF pour un résultat de plusieurs colonnes est la suivante :
ArrayFormula(SUMIF(plage, critères, plage_somme))Voici ma première formule SUMIF pour un résumé par date et catégorie :
=ArrayFormula(SI(A2:A<>"",SUMIF(Ventes!$B$2:$B&Ventes!$A$2:$A,$B$1:$G$1&$A$2:$A,Ventes!$C$2:$C),""))Explication de la formule :
La plage :
Vérifiez mes données d’exemple. Vous pouvez voir que la première colonne contient la date et la deuxième colonne contient la catégorie. J’ai combiné ces deux colonnes (plage) dans la formule SUMIF de la manière suivante : Ventes!$B$2:$B&Ventes!$A$2:$A.
Le critère :
Étant donné que nous avons combiné deux colonnes dans la plage, le critère doit également être combiné. J’ai arrangé les critères pour SUMIF dans une ligne (horizontalement) et une colonne (verticalement). Ici, je combine également les critères (catégorie et date) de la manière suivante : $B$1:$G$1&$A$2:$A.
La plage de somme :
Il s’agit de la colonne C dans l’onglet « Ventes ».
De cette façon, vous pouvez utiliser SUMIF pour retourner un résultat bidimensionnel, c’est-à-dire un résumé par date et par catégorie dans Google Sheets.
Résumé par mois et catégorie
En utilisant la formule SUMIF précédente, vous pouvez également créer un résumé par mois et par catégorie. Il vous suffit d’ajouter la fonction MONTH à la plage de SUMIF.
Voici la deuxième formule SUMIF pour un résumé par mois et catégorie :
=ArrayFormula(SI(A2:A<>"",SUMIF(Ventes!$B$2:$B&MOIS(Ventes!$A$2:$A),$B$1:$G$1&$A$2:$A,Ventes!$C$2:$C),""))J’ai utilisé des numéros de mois dans la colonne A de l’onglet « Résumé ». C’est pourquoi j’ai utilisé la fonction MONTH pour convertir la date en mois dans la « plage » de la formule. La formule en cellule B2 retourne une sortie de tableau.
J’ai déjà partagé deux exemples pour vous aider à comprendre comment SUMIF peut retourner plusieurs colonnes dans Google Sheets.
Résumé par année et catégorie
Pour comprendre cette partie, veuillez d’abord vous familiariser avec le résumé mensuel ci-dessus. Ici, je vais partager les modifications à apporter à la formule précédente.
Formule pour un résumé par année et catégorie :
=ArrayFormula(SI(A2:A<>"",SUMIF(Ventes!$B$2:$B&ANNEE(Ventes!$A$2:$A),$B$1:$G$1&$A$2:$A,Ventes!$C$2:$C),""))Modifications de la formule :
- Remplacez la fonction MONTH par YEAR.
- Modifiez les critères dans l’onglet « Résumé » pour inclure les années au lieu des numéros de mois.
Résumé par trimestre et catégorie
Vous pouvez également générer un rapport par trimestre et par catégorie à l’aide de SUMIF. Cependant, vous ne pouvez pas utiliser la fonction QUARTER à la place de MONTH/YEAR, car elle fonctionne uniquement dans les requêtes.
Pour alimenter la plage de SUMIF avec les trimestres, j’ai une formule personnalisée pour cela. J’utiliserai cette formule dans la formule SUMIF bidimensionnelle ici.
Formule pour un résumé par trimestre et catégorie :
=ArrayFormula(SI(A2:A<>"",SUMIF(Ventes!$B$2:$B&ARRONDI(MOIS(Ventes!C2:C)/3,0),$B$1:$G$1&$A$2:$A,Ventes!$C$2:$C),""))Faites attention à la plage SUMIF dans cette formule. Vous devez entrer les trimestres 1, 2, 3 et 4 dans l’onglet « Résumé » en colonne A2:A comme critères.
Sauf pour la première et la troisième formule, les dates de la colonne A de l’onglet « Ventes » doivent se situer dans une même année.
Pour générer un résumé par mois/trimestre et par catégorie sur plusieurs années, utilisez les formules suivantes.
Résumé par mois/trimestre et catégorie sur plusieurs années
Pour un résumé par mois et par catégorie sur plusieurs années, modifiez la formule #2 comme suit :
Formule pour un résumé par mois et par catégorie sur plusieurs années :
=ArrayFormula(SI(A2:A<>"",SUMIF(Ventes!$B$2:$B&MOIS(Ventes!$A$2:$A)&"_"&ANNEE(Ventes!$A$2:$A),$B$1:$G$1&$A$2:$A,Ventes!$C$2:$C),""))Dans l’onglet « Résumé », en colonne A, entrez les critères comme suit.
Formule pour un résumé par trimestre et par catégorie sur plusieurs années :
=ArrayFormula(SI(A2:A<>"",SUMIF(Ventes!$B$2:$B&ARRONDI(MOIS(Ventes!C2:C)/3,0)&"_"&ANNEE(Ventes!$A$2:$A),$B$1:$G$1&$A$2:$A,Ventes!$C$2:$C),""))Entrez les critères dans l’onglet « Résumé », colonne A, comme 1_2018 (trimestre_année).
Voilà, c’est tout. Amusez-vous bien !
Pour en savoir plus sur l’utilisation de Google Sheets, rendez-vous sur Crawlan.com.
