Formule numarare locatii si echipe, pe zile

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: 205
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Formule numarare locatii si echipe, pe zile

Mesaj de gh19612005 » Vin Mar 09, 2018 1:55 pm

Buna ziua
Avem un numar variabil de locatii L si un numar variabil de echipe E. Prin numar variabil de locatii as vrea sa intelem ca din numarul total de locatii doar unele sunt ""ale mele"" si dintre acestea doar unele sunt vizitate in cursul unei zile. Prin numar variabil de echipe intelegem ca din numarul total de echipe doar unele viziteaza in cursul unei zile locatiile ""mele"".
Avem urmatoarele situatii:
-o locatie poate fi vizitata de o echipa pentru o actiune lunga, pe durata uneia sau mai multor zile;
-o locatie poate fi vizitata de o echipa pentru o actiune scurta, pe durata unei fractiuni de zi, dupa care este vizitata alta locatie.
deci o echipa, in timpul zilei curente:
-poate fi la o locatie din ziua precedenta si ramane si ziua urmatoare;
-poate fi la o locatie din ziua precedenta si pleca la o locatie urmatoare, in timpul zilei curente;
-poate veni in cursul zilei curente si ramane si ziua urmatoare;
-poate veni in cursul zilei curente si pleca la o locatie urmatoare, in timpul zilei curente.
In foaia DB am un tabel cu numele echipelor si datele de sosire si de plecare de la o locatie.
In ziua curenta...
Daca echipa este deja intr-o locatie ""de la mine"", si nu pleaca...
Daca echipa respectiva se duce la alta locatie ""de la mine"", o vom gasi mai jos in tabel, cu data de sosire identica cu data de plecare de la prima locatie. Daca pleaca in alta parte, nu o vom mai gasi in tabel...""cu data de sosire identica cu data de plecare de la prima locatie""...
Daca o echipa vine de la o alta locatie ""de la mine"", o vom gasi mai sus in tabel, cu data de plecare identica cu data de sosire la prima locatie. Daca vine din alta parte, nu o vom mai gasi in tabel...""cu data de plecare identica cu data de sosire la prima locatie""...
Si acum problema mea: as vrea sa numar, folosind formule, pentru fiecare zi, cate echipe sunt in locatiile ""mele"" si cate locatii ""de-ale mele"" au fost vizitate...
In foaia Rezultat am incercat sa dau niste exemple, numarand ""manual""...sper sa nu fi gresit...
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
G.H.

H2SO4
Mesaje: 135
Membru din: Mar Apr 19, 2016 12:50 pm

Re: Formule numarare locatii si echipe, pe zile

Mesaj de H2SO4 » Vin Mar 09, 2018 5:45 pm

Cum ai ajuns la valorile trecute in fisier? cele cu rosu...
Daca am inteles bine si calculul meu este bun atunci apar neconcordante intre valorile date de tine si ale mele:

PS datele trecute in prima foaie sunt de forma zzllaaaa hh:mm:ss iar cele din foaia rezultat sunt doar zzllaaaa....
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
“Tell me and I forget, teach me and I may remember, involve me and I learn.”

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

Re: Formule numarare locatii si echipe, pe zile

Mesaj de gh19612005 » Sâm Mar 10, 2018 12:34 pm

In fisierul nou incarcat am incercat sa arat cum ar trebui sa fie numerate locatiile vizitate si echipele ramase pe locatii...
H2SO4 scrie:PS datele trecute in prima foaie sunt de forma zzllaaaa hh:mm:ss iar cele din foaia rezultat sunt doar zzllaaaa....
Asa este, datele din foaia DB sunt inregistrate folosind "DTPicker", care, acum vad, imi poate scrie data pe care o vreau, dar adauga ora si minutul din momentul in care fac inregistrarea. Nu asta voiam, celulele mele sunt formatate doar ca data.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
G.H.

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

Re: Formule numarare locatii si echipe, pe zile

Mesaj de TudyBTH » Sâm Mar 10, 2018 4:57 pm

Buna,

1. Cred ca ar fi nevoie sa precizati daca exista posibilitatea ca o locatie sa fie vizitata in aceasi zi de 2 sau mai multe echipe.
Din modul de calcul pe care l-ati exemplificat, rezulta ca nu exista o astfel de posibilitate, dar in practica este greu de crezut ca asa ar sta lucrurile.
2. Formulele sunt destul de complexe, de aceea ar fi util sa atasati un fisier care sa respecte INTOCMAI formatul si modul de aranjare a datelor din fisierul real. Altfel va trebui sa fiti foarte atent la adaptarea formulelor.
3. Ar fi util deasemenea sa specificati pe ce versiuni ale Office va trebui sa functioneze fisierul. In functie de aceasta, formulele pot deveni mult mai simple sau s-ar putea folosi si alte metode. Pentru formulele prezentate mai jos am luat in considerare posibilitatea ca fisierul sa poata fi folosit si cu Excel 2003 (nu am posibilitatea sa testez acum pe 2003 dar daca semnalati probleme le corectez).
4. In functie de volumul datelor inregistrate, daca acesta atinge un numar de linii de ordinul miilor sau mai mare, ar fi bine sa luati in calcul folosirea unor UDF (User Defined Function, VBA). Solutia foloseste formule array care pot incetini considerabil recalcularea fisierului la un volum mare de date.

Deci

- daca NU exista posibilitatea ca acesi locatie sa fie vizitata in aceasi zi de doua sau mai multe echipe (varianta 1 in exemplul atasat), puteti folosi urmatoarea formula pentru calcularea numarului de locatii:

Cod: Selectaţi tot

=SUMPRODUCT((INDEX(dataSource,0,2)<=$G4)*(INDEX(dataSource,0,3)>=$G4)*(INDEX(dataSource,0,3)>=0))
pentru a va usura adaptarea formulei la fisierul real am introdus un nume "dataSource" definit cu formula:

Cod: Selectaţi tot

=OFFSET(Foaie1!$A$2,1,0,COUNTA(Foaie1!$A$3:$A$10000),3)
(vedeti exemplul atasat)
in data de 5-1-2018 ati gresit numaratoarea (vedeti linia 86 din Foaie1)

- daca DA (exista posiibilitatea ca o locatie sa fie vizitata in aceasi zi de doua sau mai multe echipe - varianta 2 in exemplul atasat):
va trebui sa adaugati o coloana in care sa inregistrati numele locatiei vizitate (in fisierul atasat col D)
trebuie extrase listele echipelor si a locatiilor (denumiri unice - vezi foaia "Liste") cu formulele array:

Cod: Selectaţi tot

=IF(ISNA(INDEX(INDEX(dataSource2,0,1),MATCH(0,COUNTIF($A$1:A1,INDEX(dataSource2,0,1)),0))),"",INDEX(INDEX(dataSource2,0,1),MATCH(0,COUNTIF($A$1:A1,INDEX(dataSource2,0,1)),0)))
- pentru echipe
si

Cod: Selectaţi tot

=IF(ISNA(INDEX(INDEX(dataSource2,0,1),MATCH(0,COUNTIF($A$1:A1,INDEX(dataSource2,0,1)),0))),"",INDEX(INDEX(dataSource2,0,1),MATCH(0,COUNTIF($A$1:A1,INDEX(dataSource2,0,1)),0)))
- pentru locatii
ambele formule trebuiesc trase in jos suficient pentru acoperirea numarului maxim de echipe/locatii posibile

numele dataSource a fost adaptat deasemenea (dataSource2) pentru a include noua coloana "Locatie"

Cu acestea, formulele pentru numarare ar fi:

Cod: Selectaţi tot

=SUMPRODUCT(IF(MMULT(TRANSPOSE((INDEX(dataSource2,0,2)<=$G4)*(INDEX(dataSource2,0,3)>=$G4)*(INDEX(dataSource2,0,3)>=0)),--(TRANSPOSE(lista_echipe)=INDEX(dataSource2,0,1)))>0,1,0))
- pentru echipe (I4 in fisier)
si

Cod: Selectaţi tot

=SUMPRODUCT(IF(MMULT(TRANSPOSE((INDEX(dataSource2,0,2)<=$G4)*(INDEX(dataSource2,0,3)>=$G4)*(INDEX(dataSource2,0,3)>=0)),--(TRANSPOSE(lista_locatii)=INDEX(dataSource2,0,4)))>0,1,0))
- pentru locatii
Ambele sunt formule array (Ctrl+Shift+Enter)
In celula G1 se selecteaza luna pentru care vor fi afisate valorile
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: 205
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Formule numarare locatii si echipe, pe zile

Mesaj de gh19612005 » Dum Mar 11, 2018 1:05 pm

TudyBTH scrie:- daca NU exista posibilitatea ca acesi locatie sa fie vizitata in aceasi zi de doua sau mai multe echipe (varianta 1 in exemplul atasat), puteti folosi urmatoarea formula pentru calcularea numarului de locatii:
Cod: Selectaţi tot=SUMPRODUCT((INDEX(dataSource,0,2)<=$G4)*(INDEX(dataSource,0,3)>=$G4)*(INDEX(dataSource,0,3)>=0))
Am incercat aceasta formula, cred ca pentru numararea locatiilor e foarte buna...mi-ar mai trebui una si pentru numararea echipelor ramase pe locatie...
Cand am testat formula, m-am impiedicat de faptul ca in celule era stocata informatia in forma data+timp, cum o trece DTPicker...inteleg ca ar fi o modalitate de a-l seta sa treaca numai data, dar n-am reusit sa o gasesc, asa cum nu am mai gasit nici modalitatea de a insera obiectul "DTPicker" in fisier :oops: ...
TudyBTH scrie:2. Formulele sunt destul de complexe, de aceea ar fi util sa atasati un fisier care sa respecte INTOCMAI formatul si modul de aranjare a datelor din fisierul real. Altfel va trebui sa fiti foarte atent la adaptarea formulelor.

In fisierul atasat acum, foaia FOND e structurata ca in fisierul original.
Vom rula acest fisier pe Excel 2010.
Da, inregistrarile vor fi foarte multe, in plus, daca echipe pot fi in numar de 40-50, numarul locatiilor poate ajunge la 5000...

Voi incerca sa testez si varianta a 2a, dar inca de pe acum va multumesc pentru solicitudine...
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
G.H.

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

Re: Formule numarare locatii si echipe, pe zile

Mesaj de TudyBTH » Dum Mar 11, 2018 2:29 pm

Buna,

Aveti in fisierul atasat formulele adaptate pentru inregistrari care contin si timpul.
In foaia 'Liste' am ajustat formulele pentru extragerea a 200 de locatii. Daca se pune problema unui numar de peste 5000 locatii, va trebui sa renuntati la extragerea acestora prin formule si sa le definiti dv (timpul de recalculare in varianta cu formule ar face fisierul inutilizabil in timpi rezonabili).
Desi s-ar mai putea face unele optimizari, cred ca ar fi mai potrivit sa aveti in vedere crearea unei solutii VBA.
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: 205
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Formule numarare locatii si echipe, pe zile

Mesaj de gh19612005 » Mie Mar 14, 2018 10:09 am

Buna ziua
Atasez un nou fisier deoarece, desi am impresia ca am introdus corect formulele, rezultatele numararilor nu sunt cele pe care le-as considera eu corecte ...
Am mai introdus o coloana, Data terminarii; am considerat-o de folos pentru eliminarea ambiguitatii (lucrare terminata si echipa ramasa pe locatie vs lucrare terminata si echipa plecata la alta locatie care nu e la mine) in cazul Data iesirii completata si echipa nu mai apare in alta locatie la mine, dar n-am folosit-o in formule...
Daca nu abuzez de bunavointa voastra, va rog ajutor!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
G.H.

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

Re: Formule numarare locatii si echipe, pe zile

Mesaj de TudyBTH » Mie Mar 21, 2018 6:08 pm

Buna,

Ar fi util sa explicati mai detaliat ce anume doriti sa numarati.
In prezent, formulele numara cate echipe v-au vizitat intr-o anumita zi si numarul de locatii vizitate in ziua respectiva.
Daca asta ati solicitat dar ati observat rezultate incorecte va rog sa le marcati ca sa vedem ce anume e gresit.
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: 205
Membru din: Lun Dec 28, 2009 6:10 pm
Localitate: Pitesti

Re: Formule numarare locatii si echipe, pe zile

Mesaj de gh19612005 » Joi Mar 22, 2018 3:16 pm

Buna ziua

Echipa pe locatie - inteleg o echipa ajunsa (data intrarii) pe locatia respectiva inainte, cel mult in data de referinta; data terminarii necompletata SAU cel mult data de referinta, dar in acest caz data iesirii necompletata SAU mai mare decat data de referinta. Data iesirii egala cu data terminarii inseamna ca echipa a parasit locatia respectiva.

Locatii vizitate - inteleg locatiile la care sunt deja echipe sau ajung in data de referinta (data intrarii mai mica sau egala cu data de referinta), cu datele terminarii / iesirii necompletate SAU egale SAU mai mari decat data de referinta.

Situatia in care o locatie e vizitata in aceeasi zi de mai multe ori, de aceeasi echipa sau de echipe diferite, este posibila dar foarte putin probabila...de fapt eu n-am intalnit-o niciodata.

In fisier, Foaia1, am incercat (celulele cu fond verde) sa indic rezultatele pe care le consider corecte...sper sa nu fi gresit...
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.
G.H.

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

Re: Formule numarare locatii si echipe, pe zile

Mesaj de TudyBTH » Joi Mar 22, 2018 10:20 pm

Puteti sa explicati de ce ati trecut valoarea 8 la numararea echipelor pe data de 8 martie 2018?
Dupa numaratoarea mea sunt 9
1 Echipa 01
2 Echipa 02
3 Echipa 03
4 Echipa 04
5 Echipa 05
6 Echipa 08
7 Echipa 10
8 Echipa 15
9 Echipa 20
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.

Închis

Înapoi la “Intrebari despre Excel 2010”