Formula COUNTIF foarte lenta

Ce este nou in Microsoft Excel 2019?
Informatii despre cum se utilizeaza Microsoft Excel 2019
Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
RAMBO
Mesaje: 451
Membru din: Mie Noi 25, 2009 2:17 pm
Localitate: Pitesti

Formula COUNTIF foarte lenta

Mesaj de RAMBO » Mie Ian 13, 2021 10:49 am

Buna ziua.
Intampun urmatoarea situatie. Am un fisier in care folosesc o formula pe baza de COUNTIF pentru returnarea a n-a aparitie a unui numar. In acest sens am atasat fisierul Exemplu unde in coloana "varianta mea" este formula respectiva care face exact ce trebuie sa faca. Problema este ca daca numarul de randuri creste, atunci lucrul este foarte mult ingreunat. In acest sens am atasat fisierul original, unde in sheet baza de date, coloana ID este o formula pe baza de COUNTIF. Am experimentat si am ajuns la concluzia ca aceasta formula ingreuneaza foarte mult lucrul. In principiu in coloana lot se pun diverse numere in nici o ordine aparenta.
Exista vreo alta varianta de formula mai rapida?
Multumesc,
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

IPP
Moderator
Moderator
Mesaje: 4054
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Formula COUNTIF foarte lenta

Mesaj de IPP » Mie Ian 13, 2021 1:35 pm

Buna ziua

Eu nu stiu o formula mai simpla/mai buna fata de ceea ce deja folositi
Daca doar acea formula ingreuneaza lucrul, o varianta ar fi ca acea numarare sa se faca doar "la cerere" ca urmare a rularii unui macro

Altfel, din experienta mea:
-Microsoft s-a laudat ca in versiunile noi de Excel a regandit si imbunatatit modul (si viteza) de recalculare in fisier. Nu sunt foarte convins (ok, noi folosim doar versiuni pe 32 bit). Am facut proiecte in Excel 2007 care implicau filtrari/reafisari informatii. Acelasi fisier avea timp de executie mai mari daca era deschis in Excel 2016 sau 365 decat in 2007.

-Am avut situatii in care deschideri cu versiuni de Excel diferite + altele (nu stiu daca e strict de la Excel sau de la Windows (incepand cu versiunea 8) in fisiere Excel care contineau conexiune de date sau linkuri-poze (adica la destinatie se confirma dupa stabilirea referintelor cu Ctrl+Shift+Enter), daca necesita mai mult timp de actualizare, aparea mesajul cu "stop working" si restartare cu recovery. Uneori direct, fara alt avertisment (Chestii care nu mi s-au intamplat niciodata cu Excel 2007 si win Xp, win 7). Dupa un timp unele dintre fisierele respective se corupeau definitiv. A trebuit sa regandesc solutii dar mai ales sa refac unele fisiere de la zero, adica total de la zero. Doar informatia veche adusa cu lipire speciala ca valori.

-Daca va uitati in Name Manager aveti tot felul de referinte, exista cel putin 2 care au ceva eroare #REF! (de ex. pentru "consumabile"); plus ceva GetPivotData aparent cu referinte externe. Si acelea, cu sau fara erori, pot genera intarzieri pentru ca in esenta sunt formule

In concluzie, pentru cata informatie aveti si formulele existente, in mod normal nu ar trebui sa aveti incetiniri prea mari atunci cand lucrati daca aveti un calculator decent. Mi se pare interesant ca, daca am scris informatia in afara tabelelor existente a fost ok.

Sugestia mea ar fi sa refaceti fisierul de la zero si sa vedeti daca exista diferente. Poate acum va va consuma ceva timp dar macar veti trage o concluzie folositoare pentru viitor.

IP

RAMBO
Mesaje: 451
Membru din: Mie Noi 25, 2009 2:17 pm
Localitate: Pitesti

Re: Formula COUNTIF foarte lenta

Mesaj de RAMBO » Joi Ian 14, 2021 1:22 pm

Multumesc. Si eu m-am gandit ca trebuie refacut complet. Exact de ce ma temeam. Ideea este ca am experimentat stergand diverse portiuni din fisier si incarcand de fiecare data, si asa am ajuns la concluzia ca aceasta formula este "de vina". Asta e.

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

Re: Formula COUNTIF foarte lenta

Mesaj de Indigo-ONE » Vin Ian 15, 2021 3:54 pm

Salut,

Am si eu o intrebare.
Care este sensul sa ai acele zeci de tabele, aproape goale (cu acele zeci de mii de formule)???
Daca tot lucrezi cu tabele atunci folosestele ca tabele, fara randuri goale...
Ai, cu aproximatie, 6.400x14 adica 86.900 formule si te intrebi de ce "merge" greu.
Dai vina pe COUNTIF dar puteai pune orice functie, si rezultatul ar fi fost acelasi.

Poate ar trebui sa regandesti structura datelor, poate cu coloane ajutatoare, poate cu ajutorul unui cod VBA care sa calculeze in memorie acele date si sa le scrie apoi ca valori...


Stiu ca stii, dar nu stiu ce stii...
THINKING DOESN'T HURT ----- TRY IT !!!

RAMBO
Mesaje: 451
Membru din: Mie Noi 25, 2009 2:17 pm
Localitate: Pitesti

Re: Formula COUNTIF foarte lenta

Mesaj de RAMBO » Mie Ian 20, 2021 1:23 pm

Pentru mine nu prea are nici un sens sa am n tabele INDIVIDUALE. Daca ar fi fost dupa mine as fi rezolvat chestiunea instant cu tabel pivot. Dar tabelul pivot arata intr-un anume fel, nu toata lumea il intelege si il accepta, cu atat mai putin cand e vorba de persoane din ministerul de finante etc. Aceste tabele (CLASICE) vor ajunge si pe acolo avand in vedere ca este un caiet de sarcini. Asa mi s-a spus foarte clar (de la comp achizitii): fiecare lot cu tabelul lui + un spatiu deasupra pentru note/comentarii. Evident ca puteam sa fac (si de fapt asa face toata lumea) cu 0 formule. Mai mult, mai toata lumea il concepe in Word de genul n tabele individuale aferente fiecarui lot in care scrii de mana ce vrei. O mana de oameni folosesc Excel strict pentru calculul automat al valorii de contract functie de pretul declarat al produselor. Dar asta presupune sa stii dinainte si foarte exact ce lot atribui fiecarui produs de interes, pentru ca in acel tabel il vei scrie. Daca dupa ce ai scris tot caietul de sarcini (sa zicem 20 de loturi) se gandeste cineva (din persoanele care aproba caietul de sarcini) ca ..... mmmeeaa... nu-mi convine asta si ailalta si ca de fapt produsul x vreu sa fie asociat lotului 2 nu 3 de ex, atunci trebuie sa refaci in intregime minim 2 tabele=> nervi + reprint intregul caiet de sarcini. Modelul propus de mine rezolva aceasta "problema": am o baza de date cu toate produsele si preturi. Dupa ce declar cantitatile necesare trec in coloana "lot" ce lot vreau EU pentru produsul respectiv, moment in care produsele vor fii automat regasite fiecare in tabelul corespunzator cu toate datele aferente; iar daca dupa ce se consulta acest "draft" de catre comp achizitii si are modificari de facut, pur si simplu declar alt lot la produsele de interes si gata. Insa "pretul" platit pentru aceasta idee consta in: fisierul va avea un numar fix de loturi (tabele) "prefabricate". Numarul lor trebuie un pic supraestimat sa nu ajungi in situatia ca vrei sa faci un CS cu 70 de loturi iar tu ai doar 65 de tabele. Se pot adauga, dar ai si alte treburi. De aici si rezida numarul urias de formule. Daca e sa pastrez acest model, pe viitor m-am gandit sa fac sterg minim 30 tabele si sa-l pastrez ca versiune light avand in vedere ca sunt foarte putine cazurile in care sa ma apropii de limita maxima de acomodare (numar loturi/tabele).
Avand in vedere tabloul de ansamblu descris si limitarile impuse, as fii curios daca cineva are o idee altfel. O varianta cu VBA cred ca ar fii ucigator de complexa, cel putin pentru nivelul meu.
Optional: sunt curios cum a facut Indigo-ONE sa afle numarul de formule? Interesant.

IPP
Moderator
Moderator
Mesaje: 4054
Membru din: Mie Iul 29, 2009 7:26 am
Localitate: Cluj-Napoca

Re: Formula COUNTIF foarte lenta

Mesaj de IPP » Mie Ian 20, 2021 3:11 pm

Buna ziua

O varianta pentru a vedea numarul de formule din foaia activa:
-Ctrl+G pentru a afisa fereastra Go To;
-Special..., bifat Formulas, Ok
Ca urmare vor fi selectate toate celule din foaie care contin formule; In bara de stare (Status bar) ar trebui sa vedeti rezultate agregate (suma, medie, numarare), in functie de ce ati configurat sau este deja configurat. Daca lipseste agregarea de tip count(a) atunci click dreapta pe bara de stare si din meniul contextual bifat Count(a).

Altfel, la prima vedere, daca aveti deja informatia corect structurata si completata poate ati avea nevoie doar de un macro care sa genereze cele n liste (practic operatie de filtrare + copiere ca valori in alta parte) si ceva instructiuni de sortare, inserare nr.crt si ceva sub/totaluri. Vedeti in acest sens (generare liste liste individuale pornind de la filtrari succesive intr-o lista mai mare, sortari liste) daca va ajuta exemplul de aici

IP

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

Re: Formula COUNTIF foarte lenta

Mesaj de Indigo-ONE » Mie Ian 20, 2021 6:13 pm

Desigur numararea formulelor se poate face asa cum a mentionat IPP, sau cu formule, sau cod VBA... etc

Eu am spus, ca daca folosesti tabele atunci, sa nu aiba decat numarul de randuri cu date. Nu are sens sa pun si randuri goale.
Desigur, nu stiu exact cerintele beneficiarului, dar as face - daca se poate - cam asa:
1. As face o foaie TEMPLATE (sablon) in care as scrie/copia datele de interes apoi cu ajutorul unui macro, simplu, as copia in alta foaie, datele cu denumirea LOT x. Copiere valori, nu formule.

sau asa

2. In foaia baza date as adauga 2 coloane cu DV. In prima coloana cu DV as pune DA si NU, iar in cea dea doua coloana aspune DV cu numarul LOT-ului. La aparitia "DA" se vor copia automat, datele dorite, in "tabelul" LOT x (cel ales in a doua coloana ajutatoare) - doar valorile - in locatia dorita din foaia cu acele loturi. Se poate folosi eventul change ptr una din col cu DV, sau un buton cu cod VBA.
In felul acesta nu vei mai avea celule goale (si cu formule) si "tabelul" LOT x va avea doar atatea randuri cate date sunt copiate.


Stiu ca stii, dar nu stiu ce stii...
THINKING DOESN'T HURT ----- TRY IT !!!

RAMBO
Mesaje: 451
Membru din: Mie Noi 25, 2009 2:17 pm
Localitate: Pitesti

Re: Formula COUNTIF foarte lenta

Mesaj de RAMBO » Joi Ian 21, 2021 11:03 am

Interesant & multumesc. Varianta a doua ma atrage mai mult. Clar o sa incerc sa experimentez in acest sens in viitorul pe termen mediu. Nu stiu daca am inteles bine, dar ceea ce propui tu (in varianta 2) presupune un tabel/sheet, ceea ce interfera cu cerintele/limitarile impuse. Nu am pomenit mai inainte, dar o alta cerinta este ca toate tabelele/loturile trebuie sa fie in acelasi sheet, deoarece se va da un print as pdf la tot caietul de sarcini, care pdf trebuie suit pe nu stiu ce site. Nu stiu sa fie posibil un save as pdf/print as pdf consolidat cu info din mai multe sheeturi. Dar ideea cu VBA + coloane helper + DV pare sa promita, desi presimt niste limitari ale flexibilitatii: sa presupunem ca am rulat macro & toate informatiile s-au aranjat frumos in tabele individuale cu fix atatea randuri cate este nevoie. Insa daca ulterior mai adaug itemuri/produse intr-un lot (implicit tabel) deja existent, asta presupune relocarea tuturor tabelelor de sub tabelul care tocmai s-a extins (trebuie extins pentru a acomoda noile produse declarate). Dar m-as gandi la o varianta cu 1 tabel prefabricat/sheet in care se vor popula cu info via vba, iar mai apoi intr-un sheet separat, cu un alt macro, sa se ruleze un soi de copy/paste as values toata informatia consolidata.

RAMBO
Mesaje: 451
Membru din: Mie Noi 25, 2009 2:17 pm
Localitate: Pitesti

Re: Formula COUNTIF foarte lenta

Mesaj de RAMBO » Joi Ian 21, 2021 11:12 am

Am uitat sa precizez in postarea anterioara ca si exemplul lui IPP (linkul oferit) este foarte interesant. Foloseste la greu VBA, dar face ce trebuie. Clar o sa ma gandesc la o varianta bazata pe VBA in viitor.

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

Re: Formula COUNTIF foarte lenta

Mesaj de Indigo-ONE » Vin Ian 22, 2021 12:17 pm

Nu stiu daca am inteles bine, dar ceea ce propui tu (in varianta 2) presupune un tabel/sheet, ceea ce interfera cu cerintele/limitarile impuse. Nu am pomenit mai inainte, dar o alta cerinta este ca toate tabelele/loturile trebuie sa fie in acelasi sheet,
Se poate face oricum.
In varianta tabel/sheet informatia ar fi mai clara si mai usor de modificat daca este cazul. Apoi cand ai terminat, poti sa le pui (acele tabele LOT) intro singura foaie, daca asa se doreste
deoarece se va da un print as pdf la tot caietul de sarcini, care pdf trebuie suit pe nu stiu ce site. Nu stiu sa fie posibil un save as pdf/print as pdf consolidat cu info din mai multe sheeturi.
Este posibil.
Insa daca ulterior mai adaug itemuri/produse intr-un lot (implicit tabel) deja existent, asta presupune relocarea tuturor tabelelor de sub tabelul care tocmai s-a extins (trebuie extins pentru a acomoda noile produse declarate).
Si nu ar fi nicio problema,
relocarea tuturor tabelelor
chiar daca vei prelucra ulterior aceste date (dar ai spus ca acea pagina trebuie doar printata.) pentru ca formulele se vor updata singure...

Eu as proceda asa:

La tabelul cu baza de date as adauga o coloana cu DV - cu LOT 1, LOT 2....LOT 100 - apoi voi selecta lotul respectiv, pentru fiecare rand dorit sa apara in foaia unica LOT x (sau/si in foaia care va contine toate acele loturi dorite a fi printate in aceeasi foaie.)
Rulare macro care sa puna datele in fiecare lot. La fiecare apasare va copia lotul respectiv in foaia cu numele lotului sau in foaia comuna.
Apoi combinare foi (daca alegi varianta cu foi ptr fiecare LOT) si printare.


Stiu ca stii, dar nu stiu ce stii...
THINKING DOESN'T HURT ----- TRY IT !!!

Scrie răspuns

Înapoi la “Intrebari despre Excel 2019”