De ceva vreme încoace se tot anunță apariția metafuncției Lambda() cea care permite crearea de funcții personalizate în Excel fără utilizarea de VBA.

Personal, odată cu limitările și riscurile care apar în utilizarea macrourilor VBA am evitat constant să le utilizez sau predau, chiar dacă mulți clienți de Excel au solicitat constant cunoștințe suplimentare în acest domeniu. Pentru cei care nu știu prea multe, VBA era utilizat pentru a crearea funcțiilor personalizate sau pentru manipularea datelor în foile de calcul.

Manipularea datelor este rezolvată de ceva vreme prin utilizarea pachetelor Power Query care permit: importul, manipularea, transformarea și încărcarea datelor din diferite surse.

De câteva zile a apărut în versiunea de Excel din Office 365 funcția Lambda() alături de alte funcții corelate:

• REDUCE – Reduce o matrice (bloc de celule) la o valoare acumulată prin aplicarea unei funcții LAMBDA fiecărei valori și returnând valoarea totală în celula rezultat;
• SCAN – Scanează o matrice (bloc de celule) aplicând un LAMBDA fiecărei valori și returnează o matrice care are fiecare valoare intermediară;
• MAKEARRAY – Returnează o matrice calculată (bloc de celule) cu dimensiunea de rânduri și coloane specificate, prin aplicarea unei funcții LAMBDA;
• BYCOL – Aplică o funcție LAMBDA fiecărei coloane dintr-un bloc de celule (matrice) și returnează o matrice a rezultatelor cu același număr de coloane dar cu o singură linie cu rezultatele cumulate ale execuției LAMBDA;
• BYROW – Aplică o funcție LAMBDA fiecărei linii dintr-un bloc de celule (matrice) și returnează o matrice a rezultatelor cu același număr de linii dar cu o singură coloană cu rezultatele cumulate ale execuției LAMBDA;
• ISOMITTED – funcție de testare a parametrilor dintr-o funcție LAMBDA;
• MAP – Returnează o matrice formată prin maparea fiecărei valori din matrice(e) (blocul de valori) la o nouă valoare prin aplicarea unei funcții LAMBDA pentru a crea o nouă matrice de valoari.

Versiunea mea de Excel: Microsoft® Excel® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20186) 64-bit. Nu sunt înrolat în Office Insider, deci mesajul din paginile de help nu mai este de actualitate.

Structura funcției Lambda() de pe pagina oficială:

În continuare voi prezenta un exemplu simplu de creare a unei funcții personalizate pentru calculul prețului cu TVA pentru anumite produse.

În prima parte se creează și testează funcția cu parametrii dedicați:

În care:

• Valoare și Cotatva – sunt parametrii
• Valoare+valoare*cotatva este formula de calcul
• (B4;C4) – celulele în care sunt valorile parametrilor pentru testarea formulei.

Metoda aceasta o folosim doar pentru a compune și testa funcția. După validare funcției și rezultatului adăugăm funcția fără parametrii de testare în Name Manager din Formulas.

După definirea numelui fTVA putem folosi acum funcția fTVA() cu cei doi parametri.

În acest exemplu am folosit o contantă (19%) ca parametru al funcție fTVA()

 

Sper să fie util cuiva!

Una din problemele frecvent întâlnite în domeniul economic este aceea de calculare a salariului. Într-un caz particular calculare salariu pentru zilieri. După cum mulți știu, zilierii nu au un contract de muncă care să includă un salariu de bază ci sunt plătiți la ziua de muncă.

Chiar dacă pare aparent simplu de rezolvat, problema este: ce combinații de funcții putem folosi pentru a calcula într-o singură celulă valoarea salariului cuvenit.

Vă propun în continuare un model de problemă și rezolvare.

 

=SUM(FILTER(B3:H3;WEEKDAY($B$2:$H$2;2)<6))/8*$L$2+SUM(FILTER(B3:H3;WEEKDAY($B$2:$H$2;2)>5))/8*$L$2*2

Soluția propusă este valabilă doar în pachetul Microsoft Office 365 sau în Google Sheets.

Formula este compusă din două însumări ale unei filtrări condiționate. În prima parte a formulei se realizează o filtrare pe orele lucrate (funcția Filter()) cu condiția ca numărul zilei din săptămână (funcția Weekday()) să fie mai mică decât 6.

Dacă aveți alte metode de rezolvare mai simple, dați un email sau comentariu.

Dacă doriți să accesați modelul din Google, link aici.

Sper să fie util cuiva!

 

Articole corelate:

• Funcția Filter()
• Funcții pentru dată și timp

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.