Tout ce que vous devez savoir sur le déversement dans Excel

liens rapides

• Les définitions que vous devez connaître d'abord

• Exemples de déversement

• En référence à une plage renversée

• Correction de #Pill commun! Erreurs

La plupart des formules dans Excel renvoient un résultat unique. Cependant, si une formule Excel renvoie un ensemble de valeurs, le résultat déversera aux cellules voisines.

Les formules de répartition et de tableau dynamique ne sont pas prises en charge dans Excel 2019 ou plus tôt.

Les définitions que vous devez connaître d'abord

Avant de vous montrer des exemples de déversement en action, voici les définitions de certains termes Excel connexes dans ce guide:

• Spilling: lorsque les formules Excel renvoient un ensemble de valeurs (également appelé tableau).
• Array: un ensemble de données dans une gamme de cellules. Par exemple, si les cellules A1 à A20 contiennent des données, A1: A20 est la plage et que les données de cette plage sont le tableau.
• Fonction: une formule prédéfinie qui effectue un calcul lorsqu'il est utilisé dans une formule.
• Formule: une combinaison de fonctions, de références de cellules, de valeurs, d'opérateurs et de noms qui, lorsqu'ils sont utilisés ensemble, suivant le signe égal (=), renvoie un résultat
• Formule de tableau dynamique: une formule de formule qui est capable de retourner un tableau.
• Formule de tableau renversé: une formule qui renvoie actuellement un tableau renversé.

Exemples de déversement

Tout d'abord, regardons un exemple simple de déversement dans Excel. Cette feuille de calcul Excel contient six équipes de quiz et leurs scores pendant les semaines une à cinq.

parce que

=B2:B7

est une dynamique La formule du tableau, la tapant dans la cellule H2 renvoie un réseau renversé couvrant H2 à H7. Remarquez comment, lorsque je sélectionne la cellule H2, Excel place une bordure temporaire autour de ces cellules pour me rappeler qu'il s'agit d'un tableau renversé.

Si je devais taper des valeurs dans les cellules dans les cellules H3 à H7, cela interférerait et briserait le tableau renversé qu'Excel vient de créer, ce qui donne un #spill! erreur. Je vais parler plus de #spill! erreurs sous peu.

Maintenant, cependant, je veux utiliser plus pratique ce type de résultat en utilisant la fonction de décalage. Dans ce cas, je veux Excel pour me dire ce que chaque équipe a marqué au cours d'une semaine donnée, en fonction du numéro que je tape dans la cellule I1.

pour le faire, dans la cellule I2, je vais taper

=B2:B7

parce que je veux Excel pour commencer à la cellule A2, restez sur la même ligne, déplacez le nombre de colonnes que j'ai tapées dans la cellule I1 et retourne un résultat qui est à 6 lignes vers le bas et 1 colonne de diamètre. Étant donné qu'Excel retourne un tableau de 6 lignes de hauteur, même si je n'ai tapé la formule que dans la cellule I2, le résultat déborde des cellules I1 à I7.

à Tidy En haut de ma table de récupération de données, je souhaite également ajouter les noms d'équipe à la colonne H. Pour ce faire, je taperai

=OFFSET(A2,0,I1,6,1)

dans la cellule H2, qui produit également un tableau renversé . J'ai également appliqué un peu de formatage pour améliorer la lisibilité de la feuille de calcul.

Un avantage de l'utilisation de formules dynamiques telles que celles ci-dessus est que si les données devaient changer ou être réorganisée , les tableaux renversés s'ajusteraient en conséquence.

Dans un dernier exemple, je vais utiliser la fonction xlookup pour produire un tableau renversé. Mon objectif est d'afficher un ensemble de résultats d'une certaine équipe sur toute la période de cinq semaines, avec l'équipe en question en fonction de la valeur de la cellule I1. Tout d'abord, je taperai la formule dynamique du tableau

=A2:A7

dans la cellule H3 pour produire le tableau renversé des nombres de la semaine dans les cellules H3 à L3.

Maintenant, je vais créer une liste déroulante dans la cellule I1 contenant les noms d'équipe répertoriés dans les cellules A2 à A7. Pour ce faire, je cliquerai sur Data & GT; Validation des données, choisissez "Liste" dans le champ "Autoriser" et sélectionnez les cellules A2 à A7 pour définir la source de la liste, avant de cliquer sur "OK". , Je peux utiliser la fonction xlookup pour retourner les scores de mon équipe sélectionnée sur les cinq semaines. Dans la cellule H3, je taperai:

parce que je veux Excel pour prendre le nom de l'équipe dans la cellule I1, correspondre à un nom d'équipe dans la gamme A2 à A7, et Renvoyez le tableau correspondant des colonnes B à F. Étant donné que le résultat est de cinq colonnes de large, il est affiché sous forme de tableau renversé à travers les cellules H3 à L3. Dans ce cas, j'ai sélectionné l'équipe qui m'a interrogé dans ma liste déroulante dans la cellule I1.

=B2:B7

Les autres fonctions qui se traduisent généralement par un tableau renversé incluent le filtre, le tri, unique, et Randarray.

En référence à une plage renversée

Pour référencer une plage déversée, utilisez l'opérateur de plage renversée (#) après la plage dans la formule du tableau dynamique. En utilisant le même exemple que ci-dessus, je souhaite afficher un score total pour une équipe donnée basée sur le tableau renversé dans les cellules H3 à L3.

Ainsi, le typage

=B2:B7

dans la cellule L4 indique à Excel de résumer le tableau dans la plage déversée commençant à H3.

Correction de #Pill commun! Erreurs

Certaines situations entraîneront Excel à renvoyer le #spill! erreur. Voici ce qu'il faut rechercher si cela se produit dans votre feuille de calcul, et aussi ce que vous pouvez faire pour résoudre le problème:

Les formules de tableau dynamique ne fonctionnent pas avec des tableaux déversés de longueurs inconnues . Malheureusement, la seule façon de surmonter ce problème est d'éviter d'utiliser des formules de réseaux dynamiques qui créent des tableaux de longueurs variables.

Cause du #spill! Erreur	Façons de corriger l'erreur
Le tableau renversé traverse une cellule fusionnée.	Sélectionnez la cellule fusionnée et cliquez sur "Fusiter and Center" dans le groupe d'alignement de l'onglet Home pour dénoncer la cellule.
quelque chose bloque les cellules où le tableau renversé essaie de partir.	Supprimer ou déplacer les données dans la cellule qui bloque le tableau renversé.
La plage de la formule dynamique du tableau s'étend au-delà des bords de la feuille de travail.	Passez en revue la formule dynamique du tableau pour garantir que les références cellulaires sont exactes .
La formule de tableau dynamique est utilisée dans une table Excel formatée.	Déplacez la formule de tableau dynamique vers un emplacement à l'extérieur du tableau formaté. Alternativement, convertissez votre table Excel formatée en une plage non formatée en sélectionnant l'une des cellules de la table, et en cliquant sur "Convertir en gamme" dans le groupe d'outils de l'onglet de conception de la table.
La formule du tableau dynamique a provoqué la manège d'Excel.	La plage de renversement n'est pas connue, car la formule de tableau dynamique contient une fonction volatile.
	Si votre formule de tableau dynamique fait référence à une colonne entière , vous pouvez utiliser la fonction TrimRange pour indiquer à Excel de remettre les lignes vides, en réduisant essentiellement les données pour inclure uniquement les cellules nécessaires.

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!