Si vous choisissez des critères dans plusieurs lignes dans des fonctions telles que Vlookup, Sumif, etc., vous devez vouloir savoir comment supprimer les cellules vides supplémentaires dans la sortie de ArrayFormula. En effet, lorsqu’on utilise des critères dans une plage infinie, cela peut entraîner l’apparition de cellules vides supplémentaires en bas de votre formule de sortie.
Cela pose un problème majeur. Même si les cellules semblent vides en dessous de la sortie, et non en dessous de l’ensemble de la ligne, lorsque vous entrez une valeur dans l’une de ces cellules, la formule renverra une erreur #REF!.
Vous pourriez être intéressé par : Les différents types d’erreurs dans Google Sheets et comment les corriger.
Dans la formule SUMIF ci-dessous en G2, j’ai les critères dans la plage F2:F3. Mais dans la formule SUMIF, j’ai choisi F2:F. Voyez le problème mentionné précédemment quand j’entre une valeur en dessous.
Avant de résoudre le problème des lignes vides supplémentaires dans la sortie de la formule ArrayFormula, nous devons comprendre comment cela se produit.
La raison des cellules vides supplémentaires dans la sortie de la formule ArrayFormula en bas
Je vais expliquer cela en utilisant la fonction SUMIF. Cela s’appliquera également à d’autres formules. Voyez la formule SUMIF qui est utilisée dans la cellule G2.
Il est important de vérifier le Guide des fonctions Google Sheets.
=ArrayFormula(if(len(F2:F),(sumif(B2:B,F2:F,C2:C)),))Pourquoi ai-je utilisé la formule ci-dessus dans SUMIF?
Voyons ce qui se passe lorsque je n’utilise pas cela dans la formule SUMIF.
En réalité, il n’y a pas de critères dans F4:F. Mais le SUMIF considère que cette plage contient des critères vides et additionne la colonne C en conséquence.
Comment supprimer les cellules vides dans la plage de critères
Pour supprimer les cellules vides dans la plage de critères (et non dans la sortie de la formule), vous pouvez utiliser la combinaison IF+LEN ou la combinaison IF+ISBLANK.
Vous avez déjà vu la combinaison IF+LEN dans SUMIF. Voici l’équivalent avec IF+ISBLANK.
=ArrayFormula(if(isblank(F2:F),"",(sumif(B2:B,F2:F,C2:C))))La cause première du problème mentionné dans le titre est associée à cette combinaison IF+LEN ou IF+ISBLANK. Cette combinaison nous permet de limiter la sortie visuelle de la formule dans les cellules qui contiennent les critères. Mais malheureusement, cela renvoie des cellules vides en dessous de la sortie.
Si vous me demandez comment supprimer les cellules vides supplémentaires dans la sortie de la formule ArrayFormula, ma réponse est que c’est spécifique à la formule.
Voici quelques exemples de formules à partir de la SUMIF ci-dessus.
Comment supprimer les cellules vides dans la sortie de la formule ArrayFormula
Je vais inclure quelques exemples de formules qui couvrent certaines des fonctions populaires.
Supprimer les cellules vides supplémentaires dans SUMIF dans Google Sheets
J’utilise la fonction Filter pour filtrer les cellules vides dans les critères qui entraînent les zéros supplémentaires et finalement les cellules vides.
=ArrayFormula(sumif(B2:B,filter(F2:F,F2:F<>""),C2:C))Assurez-vous de ne laisser aucune cellule vide entre la plage de critères sélectionnée.
Supprimer les cellules vides supplémentaires dans Vlookup dans Google Sheets
Vlookup renvoie #N/A au lieu des cellules vides en dessous de la sortie de la formule. Habituellement, nous utilisons IFERROR pour convertir ces erreurs #N/A en valeurs vides.
=iferror(ArrayFormula(vlookup(E2:E9,A2:C9,2,FALSE)))Ici encore, nous pouvons utiliser Filter pour supprimer les cellules vides en dessous de la sortie de la formule.
=ArrayFormula(vlookup(FILTER(E2:E,E2:E<>""),A1:C,2,FALSE))La fonction Filter ne fonctionnera pas dans tous les scénarios. Voici quelques exemples et solutions.
Supprimer les cellules vides supplémentaires dans la sortie de la formule ArrayFormula en bas de la rangée
Comme vous le savez peut-être, la fonction ROW peut remplir une colonne avec des nombres séquentiels.
Par exemple :
=ArrayFormula(row(A1:A10))Cette formule renverrait les nombres séquentiels de 1 à 10 dans une colonne.
Vous pouvez également incorporer la combinaison IF+LEN avec cette formule.
Cette formule ROW renverrait également des cellules vides supplémentaires à partir de la cellule A8. Ici, nous pouvons utiliser Query ou Sortn pour supprimer les cellules vides supplémentaires en dessous de la sortie.
=Query(ArrayFormula(if(len(B2:B),row(B1:B),)),"Select * where Col1 is not null",0)L’utilisation de SORTN est délicate pour supprimer les cellules vides supplémentaires. C’est facile à utiliser, mais cela trie la sortie de la formule. Donc, si vous ne vous souciez pas du tri, utilisez SORTN pour supprimer les cellules vides supplémentaires en dessous de la sortie.
Voici la formule basée sur SORTN.
=sortn(if(len(B2:B),row(B1:B),),max(if(len(B2:B),row(B1:B),),0))Ce Sortn limitera le nombre de lignes à la sortie maximale de la fonction Row, qui est de 6. Cela signifie qu’il y aura des valeurs dans 6 cellules dans la sortie de la fonction Row et cela remplira son objectif.
Conseils pour éliminer les lignes supplémentaires dans la plage de données elle-même
Les fonctions telles que SORT, UNIQUE ne prennent pas de critères. Pour éliminer les cellules ou les lignes vides en dessous de telles sorties de formule, vous pouvez suivre le conseil suivant.
Je vais prendre l’exemple de la fonction UNIQUE.
Étapes :
- Filtrer les données source pour éliminer les cellules ou les lignes vides en bas.
=filter(A1:A,len(A1:A))- Utilisez ensuite cela comme données source dans la fonction Unique.
=unique(filter(A1:A,len(A1:A)))En conclusion, il n’y a pas de formule unique sur laquelle nous pouvons nous appuyer pour supprimer les cellules vides supplémentaires dans la sortie de la formule ArrayFormula dans Google Sheets.
Dans certains cas, nous pouvons utiliser Filter, et dans d’autres cas, nous pouvons utiliser Query ou SORTN. Ce qui est important, c’est que dans toutes les combinaisons, la fonction LEN joue un rôle crucial !
