Formule calcul celule vizibile in filtru

[gh19612005]

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!

[TudyBTH]

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

[gh19612005]

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 ...

[TudyBTH]

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.

[gh19612005]

Imi pare rau ca nu ma fac inteles si va irosesc timpul
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...

[TudyBTH]

Ok,
sa le luam pe rand:

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.

@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.

[gh19612005]

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.

[TudyBTH]

Incercati acum (Solutia 2)

[gh19612005]

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!

[TudyBTH]

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