#EPARS! (#SPILL! dans un Excel anglais) signifie qu'une formule renvoie plusieurs valeurs (une liste ou un tableau) et qu'Excel n'a pas la place de les écrire : au moins une cellule de la plage dont le résultat a besoin n'est pas vide. Le tableau ci-dessous affiche les noms d'erreur anglais, donc #SPILL!. =UNIQUE(A2:A6) ci-dessous a besoin de trois cellules, de C2 à C4, et C4 contient un x. Supprimez C4 et la liste apparaît. Le tableau affiche les formules sous leur forme anglaise, avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =NBVAL(UNIQUE(A2:A7)).
| A | B | C | |
|---|---|---|---|
| 1 | Product | Unique list | |
| 2 | Apple | #SPILL! | |
| 3 | Pear | ||
| 4 | Apple | x | |
| 5 | Plum | ||
| 6 | Pear |
#SPILL! Le résultat a besoin de plus de cellules libres. Videz les cellules qui le bloquent.Dans Excel en français : =UNIQUE(A2:A6)Cliquez sur C2 : la note sous la grille indique ce qui ne va pas. Cliquez ensuite sur C4 et appuyez sur Suppr. Les trois produits se répandent dans C2:C4, et un contour marque la plage de propagation. Tapez quelque chose en C3 et l'erreur revient. Excel fonctionne de la même façon : les fonctions qui renvoient des tableaux, comme FILTRE, UNIQUE, TRIER, SEQUENCE et FRACTIONNER.TEXTE, ne fonctionnent que si toute leur plage de propagation est libre. Elles exigent Excel 2021 ou une version ultérieure (FRACTIONNER.TEXTE : Microsoft 365 ou Excel 2024) ; les versions plus anciennes affichent #NOM? pour elles, donc #EPARS! n'y apparaît jamais (voir UNIQUE pour la fonction elle-même).
Corriger l'erreur #EPARS!
- Cliquez sur la cellule qui affiche
#EPARS!. Dans Excel, une bordure en pointillés montre la plage que le résultat veut remplir. - Cliquez sur l'icône d'avertissement à côté de la cellule et choisissez Sélectionner les cellules obstruantes. Excel sélectionne chaque cellule qui gêne.
- Appuyez sur Suppr, ou déplacez ces cellules ailleurs (coupez-les et collez-les).
Si les cellules qui gênent contiennent des données dont vous avez besoin, déplacez plutôt la formule : placez-la dans une colonne ou une ligne où tout ce qui se trouve en dessous et à côté est vide.
#EPARS! alors que les cellules semblent vides
Le cas le plus déroutant : la plage de propagation semble vide, mais Excel affiche quand même #EPARS!. Une cellule qui contient une seule espace, ou une formule qui renvoie une chaîne vide "", n'est pas vide, et elle bloque la propagation comme une valeur.
| A | B | C | |
|---|---|---|---|
| 1 | Numbers | Numbers | |
| 2 | #SPILL! | #SPILL! | |
| 3 | |||
| 4 |
#SPILL! Le résultat a besoin de plus de cellules libres. Videz les cellules qui le bloquent.Dans Excel en français : =SEQUENCE(3)A2 veut A2:A4, et A4 contient une espace. C2 veut C2:C3, et C3 contient ="". Cliquez sur A4 ou C3 pour voir ce qu'elles contiennent, supprimez-le, et les nombres se répandent. Dans Excel, Sélectionner les cellules obstruantes trouve ces cellules même quand rien n'y est visible. Un texte blanc sur un fond blanc se cache de la même façon.
#EPARS! quand une formule se propage dans une autre
Deux formules qui se propagent peuvent se bloquer l'une l'autre, ou une formule tapée plus bas peut se trouver dans la plage de propagation de celle du dessus.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Dept | IT | Sales | |
| 2 | Ana | IT | #SPILL! | Ben | |
| 3 | Ben | Sales | Dee | ||
| 4 | Cy | IT | 5 | ||
| 5 | Dee | Sales | |||
| 6 | Eve | IT |
#SPILL! Le résultat a besoin de plus de cellules libres. Videz les cellules qui le bloquent.Dans Excel en français : =FILTRE(A2:A6;B2:B6="IT")La liste IT a besoin de trois cellules, D2:D4, et D4 contient une formule NBVAL. La liste Sales en E2 a les deux cellules dont elle a besoin, donc elle fonctionne. Déplacez la formule NBVAL en D6 (ou dans n'importe quelle cellule sous la liste) et les noms IT se répandent. Laissez de la place pour qu'une liste grandisse : si un quatrième employé IT est ajouté plus tard, le résultat a besoin d'une cellule de plus.
#EPARS! avec RECHERCHEV et des colonnes entières
Une cause fréquente dans des formules écrites pour d'anciennes versions d'Excel est une valeur cherchée qui est une colonne entière :
=VLOOKUP(A:A,Prices!A:B,2,FALSE) #SPILL! (one result for every row of the sheet)
=VLOOKUP(A2,Prices!A:B,2,FALSE) one result, fill it down
=VLOOKUP(A2:A100,Prices!A:B,2,FALSE) 100 results that spill
=VLOOKUP(@A:A,Prices!A:B,2,FALSE) one result, the value on the formula's own row
A:A compte 1 048 576 cellules, donc la première formule demande 1 048 576 résultats, et à partir de la ligne 2 il ne reste pas assez de lignes : le menu d'avertissement indique que la plage de propagation dépasse le bord de la feuille. Ce modèle vient des anciennes versions d'Excel, qui n'utilisaient discrètement que la valeur de la ligne de la formule. Excel 365 conserve ce comportement dans les anciens classeurs en affichant la formule sous la forme =RECHERCHEV(@A:A;...), mais la même formule tapée aujourd'hui répand toute la colonne. La même chose arrive avec =A:A*2 et toute autre formule qui calcule sur une colonne entière. Utilisez une seule cellule et recopiez vers le bas, une plage de la taille réelle, ou @. Pour la recherche elle-même, voir la page RECHERCHEV. Dans un Excel français, la version recopiée s'écrit =RECHERCHEV(A2;Prices!A:B;2;FAUX).
Un résultat par ligne au lieu d'une propagation
Une formule qui calcule sur une plage se propage aussi. C'est souvent ce que vous voulez, et parfois vous voulez plutôt une formule par ligne.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Sales | Spilled +10% | Filled +10% |
| 2 | Jan | 100 | 110 | 110 |
| 3 | Feb | 120 | 132 | 132 |
| 4 | Mar | 90 | 99 | 99 |
| 5 | Apr | 140 | 154 | 154 |
=B2:B5*1,1Les deux colonnes affichent 110, 132, 99 et 154. C2 contient une seule formule, et C3:C5 sont sa propagation : cliquez sur C3 et la note sous la grille indique qu'elle provient de C2. Tapez quelque chose en C4 et C2 devient #SPILL!. D2:D5 sont quatre formules séparées, donc chaque cellule peut être modifiée seule et rien ne peut les bloquer. Utilisez la seconde forme quand des personnes vont écrire par-dessus certains résultats.
#EPARS! dans un tableau, avec des cellules fusionnées ou une taille inconnue
Ces causes dépendent du classeur, pas de la formule :
- Dans un tableau Excel (Insertion > Tableau) : les tableaux n'acceptent pas les résultats propagés, donc un FILTRE ou un UNIQUE dans une colonne de tableau affiche
#EPARS!. Placez la formule hors du tableau, ou sélectionnez le tableau et choisissez Création de tableau > Convertir en plage. - Des cellules fusionnées dans la plage de propagation : sélectionnez-les et choisissez Accueil > Fusionner et centrer > Annuler Fusionner cellules, ou déplacez la formule.
- Plage de propagation inconnue : la taille du résultat change à chaque recalcul, comme dans
=SEQUENCE(ALEA.ENTRE.BORNES(1;10)). Excel refuse de propager un résultat dont la taille est volatile. Donnez-lui une taille fixe. - Plage de propagation trop grande ou qui dépasse le bord de la feuille : le résultat irait au-delà de la dernière ligne ou colonne. Mémoire insuffisante : le tableau est trop grand pour être calculé. Dans les trois cas, réduisez les plages, en général de colonnes entières aux données réelles.
Renvoyer une seule valeur pour que rien ne puisse la bloquer
Quand vous n'avez besoin que d'un nombre tiré d'une liste, par exemple le nombre de produits différents, entourez la fonction qui se propage d'une fonction qui renvoie une seule valeur. Une valeur unique ne se propage jamais, donc aucune cellule ne peut la gêner.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Different products | |||
| 2 | Apple | ||||
| 3 | Pear | ||||
| 4 | Apple | x | |||
| 5 | Plum | ||||
| 6 | Pear | ||||
| 7 | Apple |
À vous : E2 doit indiquer combien de produits différents se trouvent dans A2:A7. Un simple =UNIQUE(A2:A7) se propagerait jusqu'au x de E4. Écrivez en E2 une seule formule qui renvoie le nombre.
NBVAL compte les valeurs que UNIQUE renvoie et rend un seul nombre. La même idée fonctionne avec =INDEX(TRIER(A2:A7);1) pour la première valeur d'une liste triée, =INDEX(FILTRE(...);1) pour la première correspondance, ou =SOMME(FILTRE(...)) pour un total.
Questions fréquentes
Que signifie #EPARS! dans Excel ?
Une formule a renvoyé plus d'une valeur (un tableau dynamique) et Excel n'a pas pu les écrire dans les cellules en dessous ou à côté, parce qu'au moins une de ces cellules n'est pas vide, est fusionnée ou se trouve dans un tableau. Videz ou déplacez ce qui gêne et les résultats apparaissent.
Pourquoi ai-je #EPARS! alors que les cellules semblent vides ?
Une cellule qui semble vide peut contenir une espace, une formule qui renvoie "" ou du texte en blanc. Chacun de ces cas bloque la propagation. Cliquez sur l'icône d'avertissement à côté de l'erreur et choisissez Sélectionner les cellules obstruantes, puis appuyez sur Suppr.
Comment corriger #EPARS! avec RECHERCHEV ?
La valeur cherchée est une colonne ou une plage entière, comme =RECHERCHEV(A:A;D:E;2;FAUX), donc Excel essaie de renvoyer un résultat par ligne de la feuille. Utilisez une seule cellule et recopiez vers le bas, =RECHERCHEV(A2;D:E;2;FAUX), ou une plage de la taille réelle, =RECHERCHEV(A2:A100;D:E;2;FAUX).
Comment empêcher une formule de se propager dans Excel ?
Faites-lui renvoyer une seule valeur. Mettez @ devant une plage pour ne prendre que la valeur de la ligne de la formule (=@A2:A10*2), ou entourez le résultat d'une fonction qui renvoie une seule valeur, comme =NBVAL(UNIQUE(A2:A10)) ou =INDEX(TRIER(A2:A10);1).
Une formule qui se propage peut-elle aller dans un tableau Excel ?
Non. Une formule qui se propage affiche #EPARS! dans un tableau. Placez-la dans une cellule hors du tableau, ou convertissez le tableau en plage normale avec Création de tableau > Convertir en plage.
