Formula de calcul pentru ore:min:sec:cadre

Informatii despre cum se utilizeaza Microsoft Excel 2007. Calcule, Formule, Functii, Tabele pivot, Analiza datelor, etc
dan81076

Formula de calcul pentru ore:min:sec:cadre

Mesaj de dan81076 » Lun Noi 30, 2009 7:52 am

Buna ziua tuturor!
Nu am veleitati de profesionist in domeniu si nici nu vreau sa am pentru ca ma intilnesc cu acest program doar sporadic. Cu toate acestea caut o formula de caclul ce ma poate ajuta intr-un calcul aparent simplu. Am o coloana pe care doresc ca, prin selectare sau selectare aleatorie a celulelor sa aflu un total al timpilor inregistrati acolo. De precizat este faptul ca am reusit sa fac acest lucru dar nu in totalitate, celulele contin ORE:MIN:SEC:CADRE iar socoteala mea reuseste sa-mi calculeze doar pentru ore, minute si secunde.
În speranta ca ma puteti ajuta multumesc anticipat tuturor!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de gecs » Lun Noi 30, 2009 3:36 pm

1. Cum sunt formatate celulele respective si ce valoare e inscrisa intr-o asemenea celula (vezi in Formula Bar - nu ce valoare e afisata)?
2. Ce intelegi prin "selectare sau selectare aleatorie"?

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

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de Dr.Windows » Lun Noi 30, 2009 3:38 pm

Cred ca ar fi mai util daca ai atasa un fisier XLS si procedura/functia dupa care ai reusit sa calculezi partea de Ore/Min/Sec... si in plus sa ne spui regula dupa care cadrele se transforma in secunde...

Adica 1s = 24/30/60/100 cadre?... ca probabil aici ti s-au incurcat si tie socotelile... ;)

dan81076

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de dan81076 » Lun Noi 30, 2009 4:09 pm

Am atasat un exemplu fara cadre. Si, cu scuzele de rigoare... 1 secunda = 25 cadre.
Multumesc!
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de gecs » Lun Noi 30, 2009 4:39 pm

E ceva ciudat in valorile alea daca ai 25cadre/sec...

In fisierul atasat ai formulele cu care poti sa faci calculele si un calcul de numar de cadre pentru lista de timpi (prima lista).

Daca ai intrebari, sper sa le pot raspunde.
Ex. fara cadre_R1.xls
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

dan81076

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de dan81076 » Mie Dec 02, 2009 9:34 am

Pentru gecs:
Trebuie sa-ti multumesc, ai facut putina lumina in capul meu... incep sa inteleg ceva din formulele astea si chiar fac progrese!
In atasament este unul din exemplele tale care mi se pare cel mai apropiat de ceea ce-mi trebuie. Cu exceptia parantezelor care se deschid pe rind si se inchid toate odata si care mi-au spalat creerii (glumesc, binenteles!) la formula respectiva ar trebui adaugat "ceva" care sa transforme cadrele in secunde sau minute, dupa caz. Ca exemplu, in cazul de fata, rezultatul final sa se transforme din "00:04:42:109" in "00:04:46:09" (aici am transformat cele 109 cadre in 4 secunde si 9 cadre).
Si, tot pentru cultura mea generala, as mai vrea sa ma lamuresti care e diferenta intre confirmarea formuleleor cu Ctrl+Shift+Enter si cea folosita de mine pina acum.
Din pacate, nu sint un "scriitor" prea bun si nici acest fel de comunicare nu cred ca ma ajuta sa ma fac prea bine inteles. Cu toate astea sper ca nu deranjez cu insistentele mele.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

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

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de Dr.Windows » Mie Dec 02, 2009 12:34 pm

Vezi daca te ajuta formula urmatoare: :D

=TEXT(SUM(TIMEVALUE(MID(I7:I19,1,8)))+CONVERT(INT(SUM(VALUE(MID(I7:I19,10,2)))/25),"sec","day"),"hh:mm:ss")&":"&TEXT(MOD(SUM(VALUE(MID(I7:I19,10,2))),25),"00")

Mie mi-a rezultat pe fisierul tau 00:04:46:09 deci teoretic ar trebui sa mearga... dar atentie formula trebuie "confirmata" cu Control + Shift + Enter

Si ca sa explic cat de cat formula:

- SUM(TIMEVALUE(MID(I7:I19,1,8))) face adunarea partii de tip "hh:mm:ss" din intervalul specificat
- SUM(VALUE(MID(I7:I19,10,2))) face adunarea partii de cadre, din care ia doar partea intreaga a impartirii la 25 (adica doar secundele) pe care le converteste cu CONVERT la acelasi format "hh:mm"ss" pentru a putea fi adunate cu suma de mai sus (adica pentru a putea face operatiile matematice cu valori de tip ora)
- Cele 2 sume apoi sunt convertite la un text de forma "hh:mm:ss" la care se adauga "restul cadrelor" cu TEXT(MOD(SUM(VALUE(MID(I7:I19,10,2))),25),"00")
Ultima oară modificat Mie Dec 02, 2009 12:52 pm de către Dr.Windows, modificat de 2 ori în total.
Motiv: Explicare formula

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de gecs » Mie Dec 02, 2009 12:42 pm

Nu deranjezi cu nimic - asta e rostul acestui forum, sa ne ajutam intre noi si sa mai invatam una-alta inatreband, primind raspunsuri de la cei care le cunosc, citind ce se mai scrie prin celelalte subiecte...

Fiecare dintre noi a fost incepator intr-ale Excel-ului si poate ca multimea de paranteze ne-a derutat cel mai mult - pe unii i-a "alungat" pur si simplu. Daca in Excel sunt multe paranteze (si sunt), ar fi interesant sa vezi cat de multe paranteze sunt intr-un program scris in Lisp :)

Ca sa aprindem si mai bine "becul" ala care face putin lumina, trebuie spus mai intai ca in Excel, ca si in toate programele pe care le folosim, se lucreaza cu mai multe tipuri de date, doar ca in Excel lucrul asta e mai evident si trebuie avut in vedere atunci cand avem nevoie sa facem operatii cu aceste date. Sunt date de tip numeric, alfanumeric (siruri de caractere - le mai spune si de tip text), date calendaristice etc. De fapt sunt doua tipuri mari si late: numere si siruri de caractere (si astea sunt tot numere, de fapt ;) ) - e ca in zicerea aia cu "cei patru evanghelisti" :) Pentru ca utilizatorii sa aiba tot timpul o reprezentare mai aproape de semantica de zi cu zi a datelor, s-au definit ca tipuri de date si datele calendaristice si intervalele/momentele de timp, dar in esenta acestea sunt tot numere. De fapt si in viata de zi cu zi, daca avem nevoie sa facem un calcul, fara utilizarea computerului, bazat pe diferenta dintre doua date, tot o scadere de doua numere facem (sau mai multe scaderi si o adunare, sau... - oricum, operatii aritmetice). Ca sa te convingi, scrie o data calendaristica intr-o formula (ceva de genul: 4/12/2003) si dupa ce ai dat Enter, selecteaza celula respectiva, da un click-dreapta si din menu-ul contextual alege Format Cells..., iar din dialogul de formatare, la tab-ul Number, in loc de Date alege Number - vei vedea cum data care apare inscrisa in celula se transforma intr-un numar intreg. La fel e si cu timpul, doar ca formatata ca numar, o data de tip Time va fi un numar zecimal, intotdeauna subunitar. Devine, sper, evident acum in ce mod se pastreaza datele de tipul Date & Time - partea intreaga a numarului e interpretata ca data calendaristica, iar partea zecimala ca moment in timp. Daca pentru celula in care ai scris o data calendaristica alegi din acelasi dialog Format Cells... un tip de formatare care sa cuprinda si data si ora, pentru data respectiva vei obtine ora 12:00AM, pentru ca partea zecimala a numarului e 0. De curiozitate, intr-o alta celula inscrie o valoare de timp (11:32:45, de exemplu) si apoi, tot cu Format Cells... transforma data asta din Time in Date - vei obtine o data+ora inexistenta, adica: 1/0/1900 11:32:45 AM - 0 ianuarie 1900!!! Asta pentru ca partea intreaga a numarului care reprezenta valoarea de timp e 0.

Pentru ca datele numerice sa poata fi vizualizate ca date specifice semanticii noastre cotidiene se foloseste conceptul de Format. Se spune ca o data e formatata dupa un anume tip. In exemplele de mai sus niste numere sunt reprezentate ca data calendaristica si/sau timp folosindu-se mecanisme interne ale Excel-ului pentru aceste reprezentari. Datele pot fi formatate sa arate altfel si folosind mecanisme de formatare dupa dorinta utilizatorilor. De exemplu, un numar ca 23.15 poate aparea in celula scris ca 23 lei.15 bani, dar valoarea de 23.15 nu se schimba - doar forma de afisare a acestei valori in celula se modifica.

Dupa aceasta lunga explicatie, care totusi sper ca n-a fost inutila... urmeaza o alta explicatie legata de formule :D

O formula e o procedura pusa de Excel la dispozitia utilizatorului pentru a face diverse prelucrari ale datelor inscrise in celulele foilor de calcul. Declansarea procedurii respective se face prin inscrierea sumnului "=" ca prim caracter intr-o celula. Din momentul in care scrii "=" poti folosi resursele pe care Excel-ul le pune la dispozitie pentru a face diverse prelucrari ale datelor inscrise in celulele foilor de calcul dintr-un fisier. Procedura respectiva se numeste formula si e o combinatie de functii predefinite in Excel si explicate in help-ul programului. O functie e definita printr-un nume si o serie de argumente puse intre paranteze. Sintaxa unei functii e de forma: nume_functie(arg1,arg2,arg3...). Intotdeauna o functie returneaza un rezultat, fie ca e un rezultat de tip data (data de orice tip: numeric sau sir de caractere), fie ca e o eroare - tot rezultat e. Rezultatul returnat de o functie este bazat pe prelucrarea argumentelor functiei untr-un anume fel - explicat in help pentru fiecare functie. Tipul, ordinea si numarul argumentelor necesare fiecarei functii sunt si ele importante si explicate in help. Exista functii care nu necesita, sau pot sa nu aiba argumente: TODAY(), ROW(), COLUMN() etc. In locul unuia, mai multora sau tuturor argumentelor unei functii putem sa folosim tot functii - rezultatul returnat de funtiile folosite ca argumente sunt folosite ca argumente in functia initiala - asta se numeste imbricare si de aici deriva logica deschiderii si inchiderii parantezelor care se pot deschide pe rand si inchide toate deodata :). Dupa numele unei functii urmeaza obligatoriu o paranteza deschisa dupa care incepe sirul de argumente necesar functiei respective, iar incheierea sirului de argumente este marcata prin inchiderea parantezei deschisa initial.

Revenind, in sfirsit, la problema ridicata de tine trebuie spus de la bun inceput ca datele cu care vrei sa operezi sunt prezentate (formatate) ca siruri de caractere. Avand informatia care decodifica aceste siruri de caractere intelegem ca e vorba de o concatenare (concatenare inseamna unirea, "lipirea", a doua siruri de caractere) dintre o data de tip Time si un numar intreg care reprezinta numarul de cadre. Pentru a putea prelucra un asemenea tip de data, ea trebuie despartita, prin formule, in cele doua componente: timp si numar intreg. Chestia asta presupune un oarece efort de logica si o complexitate sporita a formulei utilizate si explicarea unei asemenea formule complexe n-ar face decat sa complice lucrurile. De aceea, cred ca cel mai bine ar fi ca de la inceput sa separam lucrurile.

In fisierul atasat ai o prelucrare a datelor din fisierul atasat de tine anterior, dupa cum urmeaza:
- pe coloana M ai valorile de timp extrase din sirurile de caractere de pe coloana I;
- pe coloana N ai numarul de cadre extrase din sirurile de caractere de pe coloana I;
- in randul 20, pe coloanele M:T ai calcule cu valorile de pe coloanele M si N pe care o sa le explic pe fiecare in parte.

In celula M20 e totalul timpilor de pe coloana M;
In celula N20 e totall cadrelor de pe coloana N;
In celula O20 e totalul cadrelor transformat in secunde intregi;
In celula P20 e numarul de cadre ramase dupa scaderea numarului de cadre transformate in secunde intregi;
In celula Q20 e numarul de ore calculat din numarul de secunde din O20;
In celula R20 e numarul de minute calculat din numarul de secunde din O20 dupa scaderea numarului de ore calculat in Q20;
In celula S20 e numarul de secunde calculat din numarul de secunde din O20 dupa scaderea numarului de ore calculat in Q20 si dupa scaderea numarului de minute calculat in R20;
In celula T20 e insumarea timpului din M20 cu timpul format din orele din Q20, minutele din R20 si secundele din S20.

Cam asta ar trebui sa faca o singura formula aplicata sirului tau de caractere din coloana I. Daca realizezi complexitatea unei asemenea formule si o raportezi la nivelul teu de "prietenie" cu Excel-ul, cred ca poti intelege si lungimea explicatiilor mele :)

Cat despre formulele care se confirma cu Ctrl+Shift+Enter, acum nu cred ca as putea sa-ti spun mai mult decat ca asta e o categorie speciala de formule care lucreaza cu date care nu exista fizic in foile de calcul. Aceste date sunt o prelucrare in memoria computerului a datelor din foile de calcul inainte de aplicarea efectiva a functiilor din formula respectiva si de aceea trebuie confirmate in acest mod, cu Ctrl+Shift+Enter.

Sfatul meu ar fi ca sa folosesti procedura din fisierul atasat, adica sa-ti desparti datele tale de tip sir de caractere in cele doua coloane (M si N in cazul descris) si in final sa faci totalurile ca pe randul 20 din acelasi fisier atasat. In felul asta ai avea o reprezentare mult mai clara a datelor tale, iar formulele ar fi si ele mai simple, chiar daca mai multe.
Nu aveţi permisiunea de a vizualiza fişierele ataşate acestui mesaj.

Avatar utilizator
gecs
Moderator
Moderator
Mesaje: 2311
Membru din: Sâm Aug 15, 2009 10:05 am
Localitate: Bucuresti

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de gecs » Mie Dec 02, 2009 1:13 pm

lucian scrie:Vezi daca te ajuta formula urmatoare: :D

=TEXT(SUM(TIMEVALUE(MID(I7:I19,1,8)))+CONVERT(INT(SUM(VALUE(MID(I7:I19,10,2)))/25),"sec","day"),"hh:mm:ss")&":"&TEXT(MOD(SUM(VALUE(MID(I7:I19,10,2))),25),"00")

Mie mi-a rezultat pe fisierul tau 00:04:46:09 deci teoretic ar trebui sa mearga... dar atentie formula trebuie "confirmata" cu Control + Shift + Enter

Si ca sa explic cat de cat formula:

- SUM(TIMEVALUE(MID(I7:I19,1,8))) face adunarea partii de tip "hh:mm:ss" din intervalul specificat
- SUM(VALUE(MID(I7:I19,10,2))) face adunarea partii de cadre, din care ia doar partea intreaga a impartirii la 25 (adica doar secundele) pe care le converteste cu CONVERT la acelasi format "hh:mm"ss" pentru a putea fi adunate cu suma de mai sus (adica pentru a putea face operatiile matematice cu valori de tip ora)
- Cele 2 sume apoi sunt convertite la un text de forma "hh:mm:ss" la care se adauga "restul cadrelor" cu TEXT(MOD(SUM(VALUE(MID(I7:I19,10,2))),25),"00")
Si daca numarul de cadre dintr-una dintre inregistrarile din I7:I19 depaseste 99?...

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

Re: Formula de calcul pentru ore:min:sec:cadre

Mesaj de Dr.Windows » Mie Dec 02, 2009 1:28 pm

gecs scrie: Si daca numarul de cadre dintr-una dintre inregistrarile din I7:I19 depaseste 99?...
Pai nu cred ca ar trebui sa existe numere mai mari de 25... pentru ca altfel se incrementeaza "automat" cu 1 sec. direct din valorile afisate in foaie...

Închis

Înapoi la “Intrebari despre Excel 2007”