Excel - SOMME à la fin de la liste
J'ai une base de données de la table dans Excel 2003, avec une seule ligne d'en-tête contenant les filtres automatiques à la ligne 6, et les données à partir de la ligne 7 à-dire - ligne 160. Les lignes 1 - 5 sont le titre de la fiche, la colonne groupe de titres et les instructions, je ne peut pas se débarrasser de.
J'ai aussi afficher le SUM(X6:X160)
et SUBTOTAL(109,X6:X160)
dans ce domaine pour les colonnes numériques. Je ne veux pas afficher cette au bas de la table parce que plusieurs utilisateurs d'ajouter des lignes à la table fréquemment et ils ont détruit les formules sur une base régulière.
Problème: chaque fois qu'un utilisateur ajoute des données à la fin de la table, on a besoin de mise à jour de la SOMME et de la sous-total des formules pour étendre la gamme qui - cela va sans dire - est la plupart du temps oublié.
Je pourrais m'étendre les formules pour couvrir toutes les lignes jusqu'à -dire - ligne 500 et masquer les lignes restantes, si un utilisateur a besoin pour "insérer" lignes lorsque le visible à la fin de la table est atteinte, ce qui à son tour permettrait de mettre à jour les formules, mais je ne considère pas cela comme un moyen très sûr ... sachant que mes utilisateurs.
Question: Est-il un moyen de créer de la SOMME et de la sous-total de X6 "à la fin de la colonne X, partout où cela est"?
CONCLUSION
J'ai utilisé de la SOMME(DECALER(....)), prendre de la "hauteur" de paramètre à partir d'un nouveau champ dans l'en-tête affiche le nombre d'enregistrements" par un =COUNTA($A:$A)-1
(-1 pour le titre de la colonne de texte qui est compté comme bien), comme il est clair dans le contexte de l'entreprise qu'une valeur de la clé doit exister dans la colonne A, pour tout enregistrement de données et pas de lignes vides sont autorisés (je peux former les utilisateurs qui en fait au moins) - plus l'avantage à l'utilisateur non seulement de voir la SOMME mais maintenant ainsi le nombre d'enregistrements dans l'en-tête volet figé.
OriginalL'auteur MikeD | 2011-07-13
Vous devez vous connecter pour publier un commentaire.
À l'aide de Offset() et Count() semble être la plus populaire, et je suis sûr que le plus efficace, à utiliser.
Personnellement, j'ai tendance à utiliser Indirects() beaucoup de choses. Il sera probablement plus lent mais ça fonctionne. Il m'aide à voir la plage qui est en cours de création. Attention cependant, car une partie de la plage est tenue dans le texte, il ne sera pas mise à jour lorsque vous déplacez la formule autour. Qui peut vous passionner. Ici, vous êtes de toute façon.
Vous pouvez utiliser la dynamique des plages qui iDevlop points ou mettre le Offset() ou Indirects() à l'intérieur de la Somme() comme je viens de le faire.
Soit la façon dont vous voulez être prudent, car Indirects() et Offset() sont Fonctions Volatiles. Qui généralement vous voulez éviter.
J'ai aussi trouvé ce mec, qui utilise Index() et Match(), qui ne sont pas des fonctions volatiles.
EDIT:
La pensée de celui-ci (car je viens de le faire sur une feuille de calcul de la mienne)..
À condition que vous sachiez que vos données ont une limite raisonnable, disons 1000, vous pouvez simplement utiliser
=SUM($X$6:$X$1000)
et il va sauter les blancs, même pour les sous-total de la méthode 1 ou 101 (en moyenne).DÉCALAGE tombe dessus si vous avez toutes les cellules vides comme vous perdez 1 ligne de Résumé de la gamme pour chaque cellule vide
OriginalL'auteur
Eh bien, je pense que vous pouvez utiliser quelque chose comme =SUM(C:C) à la somme de toutes les cellules de la colonne C et il exclura automatiquement un texte, je l'ai essayé et cela a fonctionné
OriginalL'auteur
Sûr ! Et je trouve ça beaucoup mieux d'avoir le total en haut, puisque vous pouvez aussi congeler la première ligne(s) à garder ces totaux visible.
Il suffit de demander à Google "Excel gamme dynamique". Voici quelques liens:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://support.microsoft.com/kb/830287
Tout est fait avec une combinaison de l'Offset() et Count ().
OriginalL'auteur