Acest articol propune fanilor Excel, în special de nivel începător și mediu, diferite metode de rezolvare a problemelor Excel care implică diferențe între diferite date și ore. Dacă aveți și alte exemple, formule utile, sau probleme specifice, vă rog să folosiți secțiunea de comentarii. Fișierul cu exemplele de mai jos poate fi descărcat de aici: https://1drv.ms/x/s!ApGubfWFh8NurMYAwT-aM2MyatxMjw?e=Z0xA42

Câteva noțiuni introductive

Data în Excel este un număr care reprezintă numărul de zile trecute de la prima dată care se poate înregistra: 01.01.1900. Această dată este echivalentul numărului întreg 1.

Ora în Excel este un număr zecimal cuprins între 0,000001 și 0,999999. Cu cât folosim mai multe zecimale, cu atât timpul este mai precis. 6 zecimale sunt suficiente în opinia mea pentru a reprezenta cât mai exact timpul.

Pentru a transforma o dată într-o valoare, putem pur și simplu să schimbăm formatul celulei din Dată în Număr și se va afișa echivalentul numeric al datei. O alternativă este utilizarea funcției VALUE(celula cu data) și va rezulta un număr întreg.

Pentru a prelua data din sistem folosim funcția TODAY() iar pentru a prelua data și ora folosim funcția NOW()

Dacă dintr-o celulă care are înregistrată data și ora dorim să extragem doar data atunci vom folosi funcția INT(celula cu data) care va returna un număr întreg pe care îl putem formata ca Short Date. Dacă dorim să extragem doar timpul, atunci vom scădea din celula cu data și ora, întregul acelei celule, rezultând astfel doar partea zecimală, pe care o formatăm ca Time.

O observație de detaliu: Numărul maxim de afișare a zecimalelor în Excel este de 15, în cazul în care partea întreagă este 0. Dacă numărul din partea întreagă este mai mare ca 0 numărul de zecimale scade la 14.

În imagine, formule și reprezentări noțiuni introductive.

În cazul în care timpul este în format text, ca rezultat al unei extrageri sau import, obținerea valorii timpului se realizează cu funcția TIMEVALUE().

Diferența între două date calendaristice

Scăderea a două date calendaristice presupune obținerea unui număr întreg cu diferite semnificații prezentate în coloana de observații.

Funcția NETWORKDAYS(), implică definirea unui tabel separat cu zile festive. Aceste date vor fi scăzute din rezultatul final, dacă sunt regăsite în intervalul de date pentru care se calculează diferența.

Operațiuni de adunare

Operațiunea de adunare a două date, va returna un număr întreg fără nici o semnificație concretă.

Ca aplicabilitate economică, linia 6 din imagine poate fi utilizată în calcularea termenului de scadență al unei facturi, iar pentru linia 9, putem să ne calculăm care este ultima zi de concediu de 7 zile cu pornire de la data de 17.09.2021, excluzând zilele de sărbătoare națională din tabelul cu zile festive.

În acest exemplu funcția WORKDAY() este cea care ne ajută să adăugăm un număr de zile lucrătoare la o data specificată.

Diferența dintre două ore

Diferența între două ore este o operațiune aritmetică simplă prin care scădem ora mai mare din ora mai mică. Exemplu în C2.

Problema apare în momentul în care dorim să scădem două ore aflate în zile diferite. După cum observăm în celula C4, numărul de ore între două ore din două zile diferite este afișat 4:10. Din punct de vedere numeric timpul respectiv ca diferență dintre cele două date este 1,17. Acest 1 presupune că între cele două ore au trecut 24 de ore. Ca să putem vedea răspunsul complet al numărului de ore, putem proceda la formatarea celulei cu timpul în formatul Time – total ore. Detalii în imagine.

În cazul în care avem activități care încep într-o zi lucrătoare și se finalizează în timpul de lucru al celei de-a doua zi lucrătoare formula devine mult mai complexă, pentru că trebuie să eliminăm timpul din afara orelor de program. Având în vedere că acest timp de începere și final este variabil, propun o formulă care să se raporteze la aceste ore.

Ca să ajung la formula din C14, afișată în E14, am folosit tehnica coloanelor ajutătoare:

• C10: Am determinat diferența de zile lucrătoare între cele două date. Având în vedere că ambele sunt lucrătoare, dacă sunt consecutive sau consecutive peste weekend (vineri și luni) va rezulta valoarea 2.
• C11: Numărul de ore lucrate în prima zi ca diferență între ora de final a programului B20 și partea zecimală a valorii din prima zi. Funcția INT() ne permite scoaterea valorii întregi, iar prin diferență obținem partea zecimală, aferentă timpului.
• C12: Numărul de ore lucrate a doua zi ca diferență din ora din a doua zi minus ora de start a programului din A9.

Această formulă este funcțională atât pentru zile consecutive cât și pentru activități începute vineri și finalizate după weekend.

Dacă o activitate începe într-o zi și se termină peste două zile, formula trebuie îmbunătățită cu adăugarea numărului de zile lucrătoare înmulțit cu numărul de ore lucrătoare dintr-o zi. În cazul meu am specificat 8 ore lucrătoare pe o zi.

Cam atât pentru moment. Dacă aveți alte metode de rezolvare și doriți să le partajați cu noi puteți să le treceți în secțiunea comentarii.

Fișierul cu toate formulele și exemplele poate fi descărcat de la această adresă: https://1drv.ms/x/s!ApGubfWFh8NurMYAwT-aM2MyatxMjw?e=Z0xA42

Sper să fie util cuiva.

Odată cu introducerea formulelor de tip dynamic array în Excelul din Office 365, multe metode de lucru și formule se pot schimba prin simplificarea acestora sau obținerea mai rapidă a rezultatelor dorite. Excelul din O365 este disponibil doar celor care au o subscripție activă de Office 365 și sunt disponibile și funcționale atât în aplicația client, cât și în browser.

Din toate funcțiile noi, funcția Filter() atrage atenția prin simplitatea sa și varietatea de cazuri de utilizare. Pe parcursul acestui articol voi exemplifica diferite cazuri de utilizare, dar și comparația cu alte funcții de căutare.

Fișierul cu exemplele din acest articol, poate fi descărcat de la adresa: https://1drv.ms/x/s!ApGubfWFh8NurMUZaU_xz4CzE8f8wg?e=FMOtwo

Sintaxa funcției este foarte simplă:

Sau în format text: =FILTER(bloc de celule; conditii; dacă nu sunt înregistrări care să regăsească condiția)

Separatorul ; (punct și virgulă) apare din cauză că eu folosesc localizarea în română. Pentru cei care folosesc localizarea în Engleză, separatorul este virgula.

Parametrii funcției explicați:

• Array (bloc de celule) este parametrul obligatoriu al funcției care permite specificarea:
  • Numelui unui tabel – extrage date de pe toate coloanele tabelului în cazul în care condiția este îndeplinită;
  • Numele unei coloane din tabel: numetabel[numecoloana] – extrage datele doar de pe coloana specificată;
  • Numele mai multor coloane adiacente: Numetabel[colana1]:numetabel[Coloanan] – extrage datele de la coloana 1 pana la n
  • Un bloc de celule: echivalentul unei coloane (A1:A10) sau unui tabel (A1:D10) – Extrage datele doar din blocul de celule specificat.
• Include (condiție sau condiții cumulative) este un parametru obligatoriu care permite definirea:
  • Unei singure condiții sub forma de coloană de tabel: numetabel[numecoloana]=valoare sau bloc de celule A1:A10 = valoare;
  • Mai multor condiții în formatul: (conditia1)*(conditia2)*(conditia3)
• If_Empty (textul de afișat dacă nu sunt înregistrări în blocul specificat care să îndeplinească una din condițiile cerute): este un parametru opțional sub formă de text sau o altă formulă care să ofere un rezultat în cazul în care Filter() nu returnează valori. Exemplu: „Nu există înregistrări care să îndeplinească condiția”

Exemplificare comparativă

În exemplul din imagine avem un tabel de stocuri cu intrări pentru diferite date. Tabelul se numește tblStoc, iar produsele care au codul evidențiat au mai multe intrări.

Problema simplă este de a căuta denumirea unui produs. Aparent cea mai simplă formulă este VLOOKUP, dar pentru a putea returna textul Nu este trebuie folosită și funcția IFNA(). Filter și Xlookup sunt singurele funcții care au incorporat parametrul de tratare a rezultatului #N/A în cazul în care codul de căutare nu există în tabelul de date.

Vulnerabilitatea funcțiilor VLOOKUP și OFFSET este dată de specificarea în mod absolut a numărului coloanei de pe care trebuie să extragem informația: 2 la Vlookup și 1 la Offset (La Offset numerotarea liniilor și coloanelor începe de la 0 față de celula de referință. Vezi detalii aici). Ca să putem îmbunătăți puțin această experiență putem combina cele două funcții cu un Match. (Vezi foaia de calcul P1(2). Am înlocuit în cele două funcții valorile absolute cu funcția Match care permite căutare după numele coloanei din I4 (Nume produs) în capul de tabel inițial. În felul acesta cele două funcții returnează valori corecte chiar dacă se adaugă sau elimină coloane.

Dezavantajul funcțiilor clasice este acela de combinare a mai multor funcții pentru obținerea aceluiași rezultat.

Căutare în stânga

În problema 2, pe același set de date (Numele tabelului este schimbat în tblStocP2), ne propunem ca pe baza numelui produsului să extragem codul acestui, ceea ce înseamnă căutare în stânga. Această cerință „scoate din joc” funcția VLOOKUP care nu poate realiză căutare decât în dreapta coloanei pe care se află valoare de regăsit.

În celelalte doar schimbăm coloanele din tabel și rezultatul va fi returnat corect. La Offset trebuie să schimbăm numărul coloanei de pe care se vor extrage datele, în cazul nostru prima coloana va avea valoarea 0.

Căutarea pe bază de wildcard-uri

În problema 3 am schimbat cerința în a aduce prețul de înregistrare, pentru a permite și funcției VLOOKUP să „ia parte” la acțiune.

În prima variantă am introdus numele întreg al produsului pentru a verifica dacă toate funcțiile aduc date corect.

Ca să poată funcționa, VLOOKUP-ul în acest caz NU mai permite adresarea sub forma numelui de tabel, fiind necesară adresarea blocului de celule în format „clasic”: B1:E31, coloana B fiind coloana 1 în acest caz, cea pe care se găsește valoarea de căutare.

În cazul în care introducem înlocuitorul * (asterix) în numele produsului vom observa că funcția Filter și XLOOKUP nu mai funcționează corect.

Caracterele pentru Wildcard-uri în Excel sunt:

• * (asterix) înlocuiește oricare caracter dintr-o celulă; În Exemplul nostru: Paste* va regăsi toate produsele care încep cu Paste inclusiv Pastelini
• ? înlocuiește un singur caracter din celulă: De exemplu: Ia?i va regăsi toate valorile de tip: Iași sau Iasi.
• ~ (tilda) se folosește pentru a regăsi în celule cele două caractere. De exemplu dacă la un import diacriticele sunt înlocuite cu semnul întrebării, pentru a le regăsi pe toate va trebui să trecem la șirul de căutare expresia: *~?*

Pentru a putea face Filter să funcționeze cu Wildcard-uri trebuie să îl combinăm cu funcția SEARCH() în format dinamic array. Această funcție generează un tabel (array) cu poziția de start a șirului de căutare din tabelul de date. În acest caz, dacă va regăsi șirul de căutare va genera un număr sau eroarea #VALUE! dacă șirul nu apare în celulă. Acest artificiu ne permite să extragem prin combinare cu funcția ISNUMER() toate valorile care îndeplinesc criteriul de căutare specifificat. În felul acesta FILTER devine un instrument mult mai puternic decât toate celelalte funcții pentru că nu scoate doar prima valoare întâlnită ci toate valorile de căutare.

Extragerea mai multor coloane și linii

În cazul în care la tabelul de căutare (primul parametru din funcție) introducem numele tabelului (var 1) sau a adresarea mai multor coloane adiacente (var 2), filter extrage toate informațiile despre produsul cu codul specificat.

Pentru cei care se doresc la o comparație cu DGET() vă reamintesc că acesta poate extrage o singură valoare din tabel. În cazul în care sunt mai multe linii care îndeplinesc același criteriu vom obține eroarea #NUM!

O alternativă la această implementare de Filter() este Offset() cu precizarea că datele din tabel trebuie să fie ordonate după cod pentru a putea să extragă corect valorile. În acest fel va trebui să realizăm întâi sortarea într-o altă zonă, dacă nu vrem să modificăm tabelul inițial, prin utilizarea funcției SORT() apoi facem referire la această zonă în Offset().

Exemplificare cu Offset și Sort

Căutarea după criterii cumulative („AND”)

Filter permite în același timp căutarea după mai multe criterii.

În acest caz, cele două criterii sunt trecute separat în paranteze cu simbolul * între ele.

Căutarea cu „OR”

Operatorul OR nu poate fi folosit explicit în Filter. Dar în cazul în care avem două condiții sub formă de OR atunci putem adăuga simbolul + între cele două condiții pentru a realiza căutarea.

Fișierul cu exemplele din acest articol, poate fi descărcat de la adresa: https://1drv.ms/x/s!ApGubfWFh8NurMUZaU_xz4CzE8f8wg?e=FMOtwo

Reamintesc că ele funcționează doar în versiunea de Excel din Office 365.

Pentru utilizatorii de Google Spreadsheet, funcția Filter funcționează asemănător acestui articol și acolo cu diferența că nu este tratată componenta de #N/A dar cu plusul că aduce și capul de tabel în mod automat..

Sper să fie util cuiva.