Suma numerelor din mijlocul unei coloane

Ce este nou in Microsoft Excel 2016?
Informatii despre cum se utilizeaza Microsoft Excel 2016
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
Liana
Mesaje: 46
Membru din: Mie Dec 14, 2016 6:07 pm

Suma numerelor din mijlocul unei coloane

Mesaj de Liana » Mie Apr 24, 2019 8:29 pm

Buna seara,

As dori un mic ajutor.
In col A incepand cu A1 primesc, dintr-un program, o serie de numere.
Numarul de numere primite va fi tot timpul impar, incepand cu 3 si pana la ...
deci pot primi 3 numere sau n numere cu n-impar
Am avea nevoie, sa calculam printr-o formula (sau cod VBA) suma celor 3 numere din mijlocul sirului de numere.
Prioritatea ar fi formula, iar daca nu este posibil, prin cod VBA.
Multumesc.

TudyBTH
Moderator
Moderator
Mesaje: 993
Membru din: Joi Feb 11, 2016 2:12 pm
Localitate: Cluj Napoca

Re: Suma numerelor din mijlocul unei coloane

Mesaj de TudyBTH » Mie Apr 24, 2019 9:06 pm

Cod: Selectaţi tot

=SUMPRODUCT($A$1:$A$11*(ROW($A$1:$A$11)>(ROWS($A$1:$A$11)/2-1))*(ROW($A$1:$A$11)<(ROWS($A$1:$A$11)/2+2)))
unde $A$1:$A$11 se ajusteaza corespunzator sirului de numere
Am invatat sa inotam in apa, ca pestii
Am invatat sa zburam in aer, ca pasarile
A ramas doar sa invatam sa traim pe Pamant, ca Oamenii.

Liana
Mesaje: 46
Membru din: Mie Dec 14, 2016 6:07 pm

Re: Suma numerelor din mijlocul unei coloane

Mesaj de Liana » Mie Apr 24, 2019 10:17 pm

Multumesc Tudy,

Formula functioneaza daca se introduce exact range cu numere (Ex A1:A11, sau A1: A145), asta presupune modificarea formulei functie de range cu numere. Aceste numere, vin dintr-un alt program si nu stim cu cate numere vom lucra.

Am incercat sa modific formula si sa pun un range mai mare decat range cu numere, dar formula nu mai functionaeza (rezultat eronat).
Poti te rog modifica formula, in asa fel, incat sa scriu un range asigurator - sa zicem A1:A2500 - evident daca range cu numere este mai mic.
Am vrea ca sa punem in formula A1:A2500 sau A5000 iar range de numere sa fie 700 celule.

Multumesc.

Dr.Windows
Moderator
Moderator
Mesaje: 4570
Membru din: Vin Iul 31, 2009 7:32 am

Re: Suma numerelor din mijlocul unei coloane

Mesaj de Dr.Windows » Mie Apr 24, 2019 10:26 pm

Atunci mai am eu o varianta mai putin eleganta decat a lui Tudy, dar cel putin in teorie ar trebui sa fie mai flexibila.
Presupunand ca nu se stie cate numere vor fi pe un rand, dar se respecta intotdeauna regula de imparitate si "peste 3", atunci ar trebui gasit "mijlocul" si luate in calcul si celulele din stanga si dreapta acelui "mijloc".

Astfel se poate genera un "interval" dinamic si cu ajutorul lui INDIRECT se poate calcula si suma:

Cod: Selectaţi tot

=SUM(INDIRECT("R"&ROW()&"C"&COLUMN($A:$A)+ROUNDUP(COUNT($B2:$H2)/2,0)-1&":R"&ROW()&"C"&COLUMN($A:$A)+ROUNDUP(COUNT($B2:$H2)/2,0)+1,FALSE))
Pentru mai multe detalii am atasat si un fisier ce ar fi fost util de la inceput ca sa se inteleaga mai bine cum arata seriile respective de date. ;)
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Indigo-ONE
Mesaje: 433
Membru din: Mar Dec 11, 2018 8:54 pm

Re: Suma numerelor din mijlocul unei coloane

Mesaj de Indigo-ONE » Mie Apr 24, 2019 11:06 pm

Salut,

Vin si eu cu o formula mai ergonomica decat a colegilor (mai putine functii si mai robusta), mda ... mi-au murit laudatorii :lol: ;)

Poti pune ce range doresti (inclusiv A:A, dar e bine sa folosesti un range putin mai mare decat cat crezi ca or sa fie datele)), deci:

=SUM(SMALL(A1:A5000;INT(COUNT(A1:A5000)/2)+{0;1;2}))

este formula normala.


@ Dr.Windows

Eu am inteles ca datele vor fi DOAR pe coloana A (deci nu pe randuri).


"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."
Albert Einstein

Liana
Mesaje: 46
Membru din: Mie Dec 14, 2016 6:07 pm

Re: Suma numerelor din mijlocul unei coloane

Mesaj de Liana » Joi Apr 25, 2019 9:11 am

Va multumesc pentru solutii.

Dr. Windows,

Datele sunt doar pe coloana A (am crezut ca se va intelege din titlu si explicatii). Oricum multumim pentru solutia oferita, poate vom avea nevoie.


Dl. Indigo-ONE,

Excelenta solutie. Am testat formula si functioneaza perfect. Multumim din suflet.

PS Domnilor, de unde scoateti asemenea formule? :lol:

Dr.Windows
Moderator
Moderator
Mesaje: 4570
Membru din: Vin Iul 31, 2009 7:32 am

Re: Suma numerelor din mijlocul unei coloane

Mesaj de Dr.Windows » Joi Apr 25, 2019 10:19 am

Indigo-ONE scrie:
Mie Apr 24, 2019 11:06 pm
@ Dr.Windows
Eu am inteles ca datele vor fi DOAR pe coloana A (deci nu pe randuri).
Se pare ca eu sunt singurul care a inteles gresit... doar sunt Dr.Windows nu Dr.Excel... :lol:
Dar a meritat incercarea. :D

Si super formula Indogo! "Scurt si la obiect!" ;)

Indigo-ONE
Mesaje: 433
Membru din: Mar Dec 11, 2018 8:54 pm

Re: Suma numerelor din mijlocul unei coloane

Mesaj de Indigo-ONE » Joi Apr 25, 2019 3:09 pm

Liana scrie:
Joi Apr 25, 2019 9:11 am

Datele sunt doar pe coloana A (am crezut ca se va intelege din titlu si explicatii). Oricum multumim pentru solutia oferita, poate vom avea nevoie.
Formula data de mine, functioneaza si pe randuri nu numai coloane, fara sa faci nici o modificare. ;)
Dr.Windows scrie:
Joi Apr 25, 2019 10:19 am
Si super formula Indigo! "Scurt si la obiect!" ;)
Multumesc.


"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."
Albert Einstein

Catalin B.
Moderator
Moderator
Mesaje: 813
Membru din: Vin Sep 09, 2011 4:05 pm
Localitate: Iaşi

Re: Suma numerelor din mijlocul unei coloane

Mesaj de Catalin B. » Vin Apr 26, 2019 6:17 am

@ Liana:
Contribui si eu cu o solutie in 2 variante , bazata pe OFFSET(de multa vreme nu am mai scris formule):
=SUM(OFFSET(A1,INT(COUNTA(A1:A1000)/2)-1,0,3))
=SUM(OFFSET(INDEX(A1:A1000,INT(COUNTA(A1:A1000)/2)),0,0,3))

Nu e clar daca in sirul initial sunt si valori lipsa, in cazul asta se schimba complet toata socoteala. In cazul in care acel program livreaza numere formatate ca text sau exista valori lipsa, nici una din formule nu returneaza suma corecta.
Solutia in Power Query anexata tine cont de aceste situatii. Poti sa te conectezi cu Power Query direct la sursa de date, fara sa mai fie nevoie de copy-paste, tot ce este nevoie este un click dreapta pe tabelul rezultat-Refresh, ai rezultatul imediat, inclusiv pozitia si numerele ce au fost luate in calcul.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
Probleme să fie, că soluţii se găsesc...

Dr.Windows
Moderator
Moderator
Mesaje: 4570
Membru din: Vin Iul 31, 2009 7:32 am

Re: Suma numerelor din mijlocul unei coloane

Mesaj de Dr.Windows » Vin Apr 26, 2019 3:22 pm

Liana scrie:
Joi Apr 25, 2019 9:11 am
Dl. Indigo-ONE,

Excelenta solutie. Am testat formula si functioneaza perfect. Multumim din suflet.
Atentie, solutia data de Indigo-ONE a functionat numai in situatia in care sirul de numere este ordonat crescator! In situatia in care valorile au fost nesortate, varianta data de Catalin B. a fost cea care a calculat corect. ;)

Scrie răspuns

Înapoi la “Intrebari despre Excel 2016”