Google feuille de calcul de la somme qui se termine toujours sur la cellule ci-dessus
Comment créer une feuille de calcul Google somme() qui se termine toujours sur la cellule ci-dessus, quand bien même de nouvelles cellules sont ajoutées? J'ai plusieurs de ces calculs à faire sur chaque colonne, de sorte des solutions de ce type ne va pas aider.
Exemple:
Sur la colonne B, j'ai plusieurs plages dynamiques qui doit être additionnées. B1..B9 doivent être additionnées sur B10, B11 et..B19 doivent être additionnées sur le B20. J'en ai des dizaines de tels calculs à faire. Chaque maintenant et puis, j'ai ajouter les lignes ci-dessous le dernier résumé de ligne , et je veux qu'ils soient ajoutés à la somme. J'ai ajouter une nouvelle ligne (appel il 9.1) avant la ligne 10, et une nouvelle raw (appelons-19.1) avant de la ligne 20. Je veux B10 pour contenir la somme de B1 à travers B9.1 et B20 pour contenir la somme des B11:B19.1.
Sur excel, j'ai l' la fonction offset qui fait comme un charme. Mais comment le faire avec google feuille de calcul? J'ai essayé d'utiliser des formules comme ceci:
=SUM(B1:INDIRECT(address(row()-1,column(),false))) # Formula on B10
=SUM(B11:INDIRECT(address(row()-1,column(),false))) # Formula on B20
Mais sur Google feuille de calcul, tout ce qu'il donne est une #erreur de nom.
J'ai perdu des heures à essayer de trouver une solution, peut-être quelqu'un peut calp?
Veuillez informer
Amnon
- Ce que le réel? Pourquoi est-ce si dur!?
Vous devez vous connecter pour publier un commentaire.
Vous êtes probablement à la recherche pour la formule comme:
=SUM(INDIRECT("B1:"&ADDRESS(ROW()-1,COLUMN(),4)))Google feuille de calcul INDIRECT renvoie une référence à une cellule ou zone, tandis que - de ce que je me souviens - Excel INDIRECT renvoie toujours référence à une cellule.
Compte tenu de Google INDIRECTE, en effet, a certains moment difficile lorsque vous essayez de l'utiliser à l'intérieur de la SOMME que la cellule de référence, ce que vous voulez est de nourrir la SOMME de toute la gamme se résumer en par exemple a1 notation: "B1:BX".
Vous obtenez l'adresse que vous souhaitez dans la même manière que dans EXCEL (note "4" ici, pour la ligne/colonne relative, par défaut de Google INDIRECT renvoie absolu):
ADDRESS(ROW()-1,COLUMN(),4)et de l'utiliser pour préparer gamme de chaîne pour la fonction SOMME en concaténant avec la cellule de départ.
"B1:"&et l'envelopper avec INDIRECTES, qui sera de retour de la zone à résumer.
Se RÉFÉRANT AU-DESSOUS de la RÉPONSE de Druvision (je ne peux pas commenter pourtant, je ne voulais pas multiplier les réponses)
Au lieu de temps les formules de corrections à chaque fois que la ligne est insérée ou supprimée pour faire ressemblent toutes:
=SUM(INDIRECT(ADDRESS(ROW()-9,COLUMN(),4)&":"&ADDRESS(ROW()-1,COLUMN(),4)))Vous pouvez épargner une colonne dans la feuille séparée pour la tenue de variables (appelons le "def"), disons Z, afin de définir les points de départ, par exemple
en Z1 écrire "B1"
dans Z2 écrire "B11"
etc.
et de l'utiliser comme variable dans votre somme en utilisant l'INDICE:
SUM(INDIRECT(INDEX(def!Z:Z,1,1)&":"&ADDRESS(ROW()-1,COLUMN(),4)))- les sommes de B1 pour le calcul de ligne, puisque dans Z1, nous avons "B1" ( le1,1dansINDEX(...,1,1))SUM(INDIRECT(INDEX(def!Z:Z,2,1)&":"&ADDRESS(ROW()-1,COLUMN(),4)))- les sommes de B11 calculé ligne, puisque dans Z2, nous avons "B11" ( le2,1dansINDEX(...,2,1))veuillez noter:
Feuille séparée nommée 'def' - vous ne voulez pas de ligne insérer/supprimer l'influence que les données, donc de la garder sur le côté. Utile pour ajouter un peu de la validation des listes, d'autres choses dont vous avez besoin dans vos formules.
"Z:Z" notation - l'ensemble de la colonne. Vous avez dit que vous aviez beaucoup de ces formules 😉
Donc vous préserver la souplesse de la définition de la cellule de début de chacun de vos formules, qui n'est pas influencée par la feuille de calcul des variations.
Par la voie, ne serait-il pas plus facile d'écrire fonction personnalisée/script résumant toutes les lignes au-dessus de la cellule? Si vous vous sentez comme javascripting, à partir de ce que je me souviens, feuille de calcul google a maintenant belle éditeur de script. Vous pouvez faire un appel de fonction par exemple sumRowsAboveMe() et que juste l'utiliser dans votre feuille comme
=sumRowsAboveMe()dans la feuille de cellules.Note: vous pourriez avoir à remplacer les virgules par des points-virgules
=SUM(INDIRECT("B1:B"&ROW()-1))NOTE
Après l'essai de cette réponse, il ne fonctionnera que si la somme est dans un différents colonne en raison d'une dépendance circulaire d'erreur. Sinon, la solution est valide.
C'est un peu de l'algèbre, mais nous pouvons profiter de feuilles de calcul' coin inférieur droit de glisser.
Où X est la colonne que vous travaillez avec et X2 est votre fin point. Faites glisser la formule vers le bas et les Feuilles de incrémenter le X2, et donc de changer le point de fin.
*Vous avez mentionné que vous avez eu des dizaines de ces calculs à faire. Donc, afin de s'adapter exactement à vos besoins, nous soustraire de votre dernière sommation d'obtenir que le "milieu" de gamme que nous avons voulu.
par exemple
En raison de la dépendance circulaire d'erreur mentionné plus tôt, je ne peux pas résoudre exactement et mettre la somme sur la même ligne, mais cela pourrait fonctionner dans d'autres cas où la somme doit être stocké dans une colonne différente.
Ceci est basé sur @PsychoFish, voici la solution:
Il suffit de remplacer le "3:" pour la ligne de départ somme.
@PsychoFish est correct, mais ne peuvent pas être déplacés et copié depuis la colonne est littérale et codé en dur, et @Druvision était dans la bonne direction, mais a été mal... en fait retrouvé avec le même problème d'avoir à re-saisir les plages et puis en faisant glisser les formules plus et plus.
Syntaxe générale:
avec par exemple:
J'aime la façon dont @abernier décrit la solution générale. Jusqu'à présent, seuls alphabet à base A1 notation (Une première colonne, 1 étant la première ligne) sont utilisés. Il garde me confondre, surtout quand on pense au nombre de colonnes de gauche de l'autre colonne. Je voudrais le numéro de base de la notation L1C1 beaucoup mieux. Pour utiliser la notation L1C1 pour INDIRECT, vous avez besoin pour passer de FAUSSES comme suit:
J'espère que vous trouverez très utile, aussi.
Vous les gars font cela plus difficile que vous avez à. Je viens de quitter un couple de lignes vides ci-dessus par "somme" de ligne (vous pouvez les mettre en forme pour être rempli avec de la couleur ou quelque chose pour les garder d'être utilisés par inadvertance), puis il suffit d'ajouter vos nouvelles lignes juste au-dessus de ces lignes.
La réponse par @PsychoFish m'a conduit dans le bon sens.
Le seul problème que j'ai eu à réécrire la formule de nouveau à partir de chaque colonne et de chaque somme. Voici donc l'amélioration de la formule, qui résume le précédent 9 cellules sur la même colonne, sans coder en dur la colonne ou de la rangée des chiffres:
Le seul problème est que j'ai dû le réécrire les formules si quelqu'un ajoute ou supprime une ligne. Dans ce cas, je devrais changer de 9 à 10 ou 8 corrspondingly.