Formule calcul celule vizibile in filtru

Ce este nou in Microsoft Excel 2010?
Informatii despre cum se utilizeaza Microsoft Excel 2010
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
gh19612005
Mesaje: 104
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Formule calcul celule vizibile in filtru

Mesaj de gh19612005 » Mar Mar 13, 2018 4:56 pm

Buna ziua
In fisierul atasat ca exemplu, in coloanele col1 si col2 pot fi informatii dupa care s-ar putea face filtrarea Tabelului 1, in coloanele col3 si col4 sunt valori care contribuie in formulele din coloanele cum1 si cum2. Pe baza valorilor din aceste ultime coloane s-a "construit" primul grafic.
Problema mea este ca daca se fac filtrari in Tabelul 1, graficul urmareste cumva aceste filtrari...totusi nu este precum graficul al doilea...
Intrebare: exista vreo posibilitate de a omite din calcule celulele ascunse de filtre...ceva care sa aiba rezultatul de genul
F3=C3
F7=C7+F3
F9=C9+F7 ?
Multumesc!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Formule calcul celule vizibile in filtru

Mesaj de TudyBTH » Joi Mar 15, 2018 4:37 pm

Buna,

Ar fi doua solutii:
1. Inainte de a face filtrarea, sortati datele crescator dupa valoarea din coloana in care aplicati filtrul (col1)
sau
2. creati un alt range in care aduceti doar elementele necesare pt grafic (val "a" in col1) si atasati acest range la grafic

Ideea este sa obtineti un range compact pt datele ce intra in grafic
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.

gh19612005
Mesaje: 104
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Formule calcul celule vizibile in filtru

Mesaj de gh19612005 » Joi Mar 15, 2018 9:01 pm

Ideea de "creaming curve" ar fi reprezentarea grafica a unei serii de "proiecte" prioritizate dupa rezultate, de aceea tabelul meu e deja sortat descrescator pe col3. Daca dupa aceea fac si sortare pe col1 sau col2, nu mai rezulta "cc". (a se vedea fisier atasat, Foaia1, Tabel 1 si graficul aferent)
Tabelul meu ar reprezenta "proiectele" pe intreaga firma, cu "cc" specifica, iar filtrarile, "cc" pe departamente...
Ceea ce incerc eu sa obtin mai lesne, pentru ca acum procedez ca in fisierul atasat, Foaia1, Tabelele 2.

Cred ca Solutia 2 ar putea fi Solutia, dar eu nu stiu sa setez un domeniu dinamic ...
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Formule calcul celule vizibile in filtru

Mesaj de TudyBTH » Vin Mar 16, 2018 1:52 am

Scopul este sa obtineti datele pentru grafic intr-un range compact.
Daca datele obtinute vor fi organizate ca Tabel (Tabel ca obiect Excel, Insert/Table, vezi Soutia 1 din fisierul atasat), orice grafic ce va avea referire la datele din tabel va fi un grafic dinamic.
Daca datele obtinute vor fi intr-un range ordinar (asa ca in exemplul dv, vezi Solutia 2) va trebui sa definiti cate un nume (definit dinamic) pentru datele fiecarei axe sau fiecarei serii. In Select Data... Chart veti face referire la aceste nume, precedate de numele fisierului si graficul va fi dinamic.

In fisierul atasat aveti cele doaua solutii discutate.
La Solutia 1, in locul filtrarii am folosit un Query pentru obtinerea datelor. Dezavantajul este ca trebuie sa faceti cate un "Refresh" dupa fiecare selectare a valorii de filtrare (J2). Avantajul este ca permite filtrarea unui volum mare de date.

La Solutia 2, datele sunt obtinute prin formule (coloanele M,N) intr-un range ordinar (nu este tabel), deci au fost definite dinamic doua nume (cum1_ si cum2_). Dezavantajul este ca trebuiesc completate cu formule un numar suficient de linii pentru a acoperii toate elementele posibile din datele sursa, deci nu se preteaza daca aveti in Tabel1 (Date Sursa) un numar mare de linii. Avantajul este ca datele rezultate se actualizeaza automat dupa efectuarea fiecarei selectari.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
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.

gh19612005
Mesaje: 104
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Formule calcul celule vizibile in filtru

Mesaj de gh19612005 » Vin Mar 16, 2018 12:08 pm

Imi pare rau ca nu ma fac inteles si va irosesc timpul :oops:
Mai incerc o data...

Tabelul meu, in intregime, ar contine informatii pentru construirea unei "cc" pe toate proiectele "firmei". De aceea trebuie sa fie sortat descrescator pe col3.
Daca se mai sorteaza apoi si dupa col1...nu mai e "cc" (in fisierul atasat, foaia "Solutia 1 initiala")
Dorinta mea, pentru care am deschis acest topic, era de a "transforma" formulele evidentiate in foile "Solutia 1a" si "Solutia 1b" (ramase vizibile in urma filtrarilor Tabelului initial) in formulele evidentiate in foile "Solutia 1 (a)" si "Solutia 1 (b)". Toate acestea pentru a construi "cc" pentru proiectele departamentelor "a"..."d".

@Mesajul de eroare de mai jos mi-a aparut cand am vrut sa reimprospatez tabelul pentru o noua filtrare. Nu stiu ce poate insemna...
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Formule calcul celule vizibile in filtru

Mesaj de TudyBTH » Vin Mar 16, 2018 12:37 pm

Ok,
sa le luam pe rand:
gh19612005 scrie:Tabelul meu, in intregime, ar contine informatii pentru construirea unei "cc" pe toate proiectele "firmei". De aceea trebuie sa fie sortat descrescator pe col3.Daca se mai sorteaza apoi si dupa col1...nu mai e "cc" (in fisierul atasat, foaia "Solutia 1 initiala")
Corect doar daca sortarile se fac separat, individual. Aveti insa la dispozitie "Custom sort..." care va permite sa efectuati aceasta sorare pe doua niveluri. Col1 nivel 1 si col3 (descrescator) pe nivelul 2.
Capture.JPG
In felul acesta, curba ramane "cc" si puteti face filtrarea in acelasi tabel pentru a obtine un range compact.
gh19612005 scrie:@Mesajul de eroare de mai jos mi-a aparut cand am vrut sa reimprospatez tabelul pentru o noua filtrare. Nu stiu ce poate insemna...
Da, in fisierul atasat am folosit un querry pentru efectuarea sortarii si a filtrarii dar am uitat sa va intreb daca aveti instalat PowerQuery (aveti versiunea 2010 a Office). Deci, fie il instalati, fie executati sortarea si filtrarea manual fie cu alte mijloace dsponibile (Pivot, Advanced Filter)

Am pus in fisier si o "Solutia2"! Aceasta ar trebui sa functioneze. ??
Dorinta mea, pentru care am deschis acest topic, era de a "transforma" formulele evidentiate in foile "Solutia 1a" si "Solutia 1b" (ramase vizibile in urma filtrarilor Tabelului initial) in formulele evidentiate in foile "Solutia 1 (a)" si "Solutia 1 (b)". Toate acestea pentru a construi "cc" pentru proiectele departamentelor "a"..."d".
Aici nu imi este prea clar ce anume doriti sa modificati la aceste formule. Din fisierul atasat rezulta ca in coloanele com adunati toate valorile antrioare, indiferent de proiect!!! Poate, daca ati da niste explicatii mai clare fata de ceeace doriti sa obtineti in aceste coloane sau sa inlocuiti formulele cu valorile pe care doriti sa le obtineti, m-ar ajuta sa inteleg mai clar ce doriti.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
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.

gh19612005
Mesaje: 104
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Formule calcul celule vizibile in filtru

Mesaj de gh19612005 » Vin Mar 16, 2018 1:19 pm

In fisierul atasat, foaia "Solutia 1 (2)" am facut aceasta filtrare pe doua nivele.
Obiectii:
-Tabelul, in totalitate, nu mai determina o "cc";
-filtrate pe departamente, ar rezulta "aluri"...dar deformate, intrucat contin date de la elementele de deasupra...

Pentru Solutia 2:
-Nu pot anula filrarea, pentru a obtine intreaga "cc"
-filtrate pe departamente, rezultatele sunt "deformate", intrucat contin date de la elementele de deasupra. Am atasat pe coloanele P-T, foaia Solutia 2, valorile care mi-ar fi convenit sa rezulte.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Formule calcul celule vizibile in filtru

Mesaj de TudyBTH » Vin Mar 16, 2018 2:16 pm

Incercati acum (Solutia 2)
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
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.

gh19612005
Mesaje: 104
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Formule calcul celule vizibile in filtru

Mesaj de gh19612005 » Vin Mar 16, 2018 3:16 pm

DA!!!
Acum merge excelent!

@Nu vreau sa fiu carcotas, dar s-ar mai putea schimba ceva in formula pentru a fi si coloana col4 din Date grafic sortata crescator?

Oricum, iti multumesc din inima pentru ca m-ai ajutat enorm!
Toate cele bune!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Formule calcul celule vizibile in filtru

Mesaj de TudyBTH » Vin Mar 16, 2018 5:33 pm

Imposibil. Nu se poate sorta un tabel astfel incat valorile din doua coloane diferite sa fie - AMBELE - sortate (crescator sau descrescator, indiferent cum) simultan.
Sau poate nu am inteles eu ce doriti
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.

Scrie răspuns

Înapoi la “Intrebari despre Excel 2010”