“Biblioteca 52” – Problemă de #Excel [Update 21.09.2022]

De ceva vreme mă tot chinuie un gând legat de amestecarea unui pachet de cărți în mod aleatoriu în Excel. Așa cum probabil (puțini din) cititorii acestui blog știu, operațiunea ar trebui să se desfășoare fără Macro.

Care este utilitatea acestui exemplu? Cred că din punct de vedere economic nu prea are aplicabilitate. Este mai mult vorba despre exercițiul mental și de a vedea că putem să facem și altfel de calcule în Excel decât cele cu numere, produse și facturi. Având la bază principiul KISS, știm că la baza în orice problemă de Excel sunt numerele.

După cum puteți vedea în Gif-ul de mai jos, am reușit o rezolvare a problemei dar sunt curios în legătură cu alte variante de rezolvare, inclusiv cu Macro.

 

Fișierul poate fi descărcat pentru rezolvare la adresa: Biblioteca 52.xlsx

În cazul în care „vă prindeți în joc” vă rog să îmi trimiteți soluțiile voastre la adresa valy.greavu@feaa.uaic.ro până în data de 21.09.2022, ora 13:00. După acea oră voi face un update la articol cu propunerea mea de rezolvare și cele mai bune soluții. Repet, este doar un exercițiu, dar dacă cineva poate găsi o utilitate lăsați mesaj în comentarii.

[UPDATE 21.09.2022]

Din păcate nu am primit decât o singură soluție la problemă din partea lui CiprianS care rezolvă într-un fel aproape identic cu propunerea mea de rezolvare.

Aici soluția mea:

Spuneam că la bază totul înseamnă numere în Excel. Ca să amesteci niște numere trebuie doar să le generezi aleatoriu (random) iar după să sortezi acele numere după randomizare. Nu poți folosi doar funcția Random pentru că ea poate genera numere duplicat din aceeași plajă.

Aplicabilitate practică

Un coleg mi-a sugerat și o aplicabilitate practică: ai un anumit număr de oameni și trebuie să-i așezi într-o sală, un rând aleatoriu. Nu le poți aloca doar numere random ci trebuie să-i și ordonezi random.

[/UPDATE]

Sper să fie interesant pentru cineva!

2

CRUD pe listele SharePoint On-line din Excel

Listele SharePoint reprezintă un mecanism avansat de colectare și stocare a datelor din cadrul proceselor economice curente. De multe ori recomandăm renunțarea la colectarea datelor prin Excel și implementarea de liste de colectare date și raportarea acestora în Excel. Pentru cei care au mai lucrat cu liste indiferent de format, acestea au capacitatea de a exporta în timp real datele din liste în fișiere Excel pentru consultare și raportare centralizată. Doar că integrarea listelor SharePoint cu Excel este unidirecțională, datele pot fi doar citite fără actualizarea acestora în sensul Excel-SharePoint. Acest articol, propune cititorilor un model de implementare a operațiilor CRUD (Create-Read-Update-Delete) din Excel asupra unei lista SharePoint on-line prin utilizarea fluxurilor de lucru Power Automate.

Menționăm că această metodă este valabilă doar în subscripțiile de Office 365. Pentru cei care doresc să încerce varianta cu soluții locale vă reamintesc un articol din urmă cu ceva timp: Sincronizare date între Excel 2013 și SharePoint 2013.

Din punct de vedere tehnic o variantă de lucru cu Excel și cu fluxurile de lucru este descrisă în documentația Microsoft la aceste adrese:

Din păcate (sau fericire) mulți administratori au dezactivat opțiunea de instalare a add-in-urilor din Office Online Store ceea ce nu le permite prea facil utilizatorilor să aibă acces la acest instrument.

În sursa video: Trigger a Flow from an Excel Button apare o versiune ingenioasă de a utiliza lansarea unui flux de lucru Power Automate de pe un „buton” din Excel. Pornind de la acest video am adăugat câteva funcționalități de actualizare a listelor direct din Excel.

În continuarea articolului voi prezenta fiecare operațiune așa cum apare în Excel și particularitățile fluxului de lucru specific. Voi reveni zilele următoare cu un update Video pe canalul https://www.youtube.com/valygreavu

1. Sincronizarea listei SharePoint cu Excel (READ)

Datele în listele SharePoint pot ajunge prin Export din tabele Excel sau prin completarea element cu element sau prin formulare sau pot fi scrise de fluxuri.

În imagine avem un tabel cu produs, care prin funcția de Export to Excel poate fi consultată și prelucrată tabelar. În același timp, fișierul iqy care se descarcă la export are și funcție de „legare” a listei cu Excel ceea ce permite ca la un simplu Refresh în Excel să se importe automat noile înregistrări.

Lista exportată în Excel și buton de Refresh înregistrări prin preluare unidirecțională din SharePoint:

În această demonstrație coloana ID este foarte importantă pentru că ea este utilizată de fluxuri pentru a actualiza sau șterge înregistrări. ID-ul este o metadată de sistem de tip autonumber și nu poate fi refolosită de alte înregistrări.

2. Crearea de înregistrări din Excel în SharePoint (CREATE)

Pentru crearea de înregistrări din Excel, am creat o nouă foaie de calcul, am definit aceeași structură a capului de tabel cu cea a listei, am definit ca table cu numele tblCreate după care am creat fluxul în Power Automate de tip HTTP request ca să pot invoca fluxul ca link de pe butonul din Excel.

Fișierul Excel l-am salvat în biblioteca Documente din SharePoint ca să poate fi accesibil de flux pe baza acelorași credențiale. Restul actualizărilor le fac direct pe fișierul salvat Online.

În imagine: Definirea unui flux de tip HTTP request

Pașii fluxului:

Prima acțiune folosește metoda GET iar în momentul în care se salvează fluxul generează HTTP GET URL care se folosește ca link în Excel pe „butonul” Adăugare produse.

În pasul 2 este definit fișierul Excel și tabelul de date:

În pasul 3 creez un tabel HTML care va fi trimis în ultimul pas prin email în vederea confirmării finalizării fluxului. Am preluat în tabel doar trei coloane.

În pasul 4 am folosit o acțiune de tip Create item care să genereze pentru fiecare linie din tabelul Excel (value) câte o nouă înregistrare în lista Produse din SharePoint.

La finalul fluxului am folosit opțiunea de trimitere a unui mesaj de email cu tabelul generat în pasul 3.

Pentru a verifica dacă datele au ajuns corect în listă, o putem accesa din Browser sau putem da refresh în Excel.

În cazul în care nu există un mecanism de control (cheie primară pe coduri) înregistrările pot fi dublate la fiecare rulare a fluxului.

Acest model de flux poate fi folosit în cazul în care doriți să faceți bulk insert într-o listă deja existentă de SharePoint.

3. Actualizarea înregistrărilor SharePoint prin invocare din Excel (UPDATE)

Pentru operațiunile de actualizare a datelor avem nevoie de un tabel Excel nou, în cazul meu denumit tblUpdate, care să permită introducerea valorilor din tabel, esențial fiind în acest model coloana ID din SharePoint.

În tabelul Excel am introdus și două coloane de căutare (VLOOKUP) pentru a prelua din tabelul de stocuri actual din SharePoint valorile vechi ale stocului și prețului, limitând exemplu la faptul că doar cele două se schimbă mai des.

Modelul de actualizare se bazează pe completarea ID-ului de SharePoint și datelor în formatul lor de tabel.

Fluxul de actualizare este aproape identic cu cel de creare, cu mențiunea că la pasul 4 din flux pentru fiecare linie din Excel trebuie introdusă o acțiune de preluare a datelor din lista Produse din SharePoint pe baza ID-ului din Excel (Filter Query).

Pentru fiecare linie din SharePoint identificată, se execută acțiunea de Update item cu valorile preluate din Excel, fără a afecta ID-ul. Restul valorilor pot fi schimbate.

La operațiunile de update și creare apar mici probleme la crearea fluxului legate de tipul datelor. În constructorul de flux, unele valori numerice preluate din Excel nu sunt preluate în celula aferentă. Un mic detaliu sâcâitor care se poare rezolva prin trecerea valorii numerice dinamice într-o casetă text și Cut/Paste a câmpului în celula corectă, în cazul meu: CodProd, Stoc, PretInregistrare.

La finalizarea fluxului acesta trimite email cu tabelul cu valorile noi și vechi.

4. Ștergerea înregistrărilor SharePoint prin invocare din Excel (DELETE)

Pentru ștergere am procedat prin crearea unui alt tabel care să conțină ID-ul înregistrării. Fluxul este foarte asemănător cu UPDATE doar că în secțiunea a doua din pasul 4 înlocuim operațiunea de update cu operațiunea de ștergere.

În imagine, pasul de ștergere din fluxul dedicat.

În cazul în care eliminăm din greșeală anumite elemente de listă se pot recupera ușor din Recycle Bin în perioada de timp configurată de administrator.

Cam atât. Sper să fie util cuiva!

Voi reveni zilele următoare cu un update Video pe canalul https://www.youtube.com/valygreavu

Clip video demonstrații articol: https://youtu.be/tgolXR5foxk

Deduplicarea în #Excel

De multe ori la importul datelor din diferite surse, consolidarea acestora, sau pur si simplu prin colectare, pot apărea în tabelele de date, valori duplicat. Uneori pot fi duplicate doar anumite părți dintr-o înregistrare, alteori, întreaga înregistrare, sau în cele mai rele cazuri doar o coloană poate fi diferită.

Tabelul din acest articol este un demo care se referă la operațiunile de înregistrare cronologică a stocurilor de produse prin inventariere. La final de lună ne interesează să scoatem din acel tabel doar ultima valoare a stocului, corespondentă ultimei date calendaristice în care s-a efectuat operațiunea.

Pentru identificarea duplicatelor de pe o coloană, folosim formatarea condiționată, apoi filtrul pe culoare.


Ca să eliminăm înregistrările dublate, sau chiar triplate, treaba este destul de simplă. Folosim meniul Data, Remove Duplicates. În acest exemplu, o singură înregistrare era duplicată complet.


În cazul în care, este necesar, putem deselecta una sau mai multe coloane din tabel, după care putem obține rezultate diferite de deduplicare (*).

(*) – Deduplicarea este un neologism nerecunoscut în Dex, dar care se folosește destul de frecvent în domeniul stocării volumelor mari de date, în special în domeniul bazelor de date.

Uneori în anumite cazuri ca să pot identifica înregistrările duplicat pe toate valorile, folosesc o coloană suplimentară de concatenare după care aplic formatarea condiționată pe această coloană. Concatenarea se poate face clasic cu simbolul & (ampersand) sau cu ajutorul funcției ARRAYTOTEXT().

Cum funcționează Remove Duplicates?

Excelul identifică tuplul următor de combinații unice și-l elimină, păstrând pe ecran doar prima înregistrare întâlnită în tabel pe ordinea de sus în jos. Ca să putem avea un minim control pe ceea ce se elimină, în cazul în care nu selectăm toate coloanele, atunci putem să facem artificii din sortare.

De exemplu, din tabelul de produse, ne interesează doar codul și ultima dată cronologică de înregistrare a stocului cu valoarea cea mai mică.

În acest caz ar trebui să sortăm tabelul după codul produsului ascendent și după data înregistrării descendent, după care să aplicăm deduplicarea doar după primele două coloane.

În exemplul meu, am făcut ordonare și după stoc. Deduplicarea o realizez doar după Cod produs rămânând pe ecran doar valorile aferente fiecărui cod, ultimei date și celui mai mic Stoc, așa cum a fost declarat în sortare.


Această tehnică în schimb este riscantă în cazul volumelor mari de date, cu multe înregistrări și coloane.

De asemenea, eu diseminez permanent principiul nealterării sursei de date. În cazul în care facem o copie a datelor, tot ne va fi destul de greu să verificăm datele care s-au eliminat.

Deduplicarea cu funcții dynamic array

În urmă cu ceva timp scriam un articol despre determinarea numărului de înregistrări unice dintr-un tabel. Funcțiile dynamic array ne ajută în schimb să rezolvăm mai rapid problemele de duplicate. Articolul menționat rămâne valabil în contextul în care nu dispuneți de versiunile de Excel din Office 365 sau Office 2021.

Primul pas în deduplicare este să determinăm valorile unice ale codurilor și (opțional) sortarea ascendentă a acestora.

Celula G2: =SORT(UNIQUE(A2:A26)) în care Unique() extrage codurile unice de pe coloana A (Cod Produs) iar Sort() le aranjează crescător.

Celula H2: =MAX(FILTER($B$2:$B$26;$A$2:$A$26=G2)) reprezintă un filtru clasic pe baza codului de produs din G2. De pe coloana B se extrag toate datele calendaristice aferente acelui cod. Ca să obținem o singură valoare avem nevoie de funcția Max(). Mai multe detalii despre funcția Filter() puteți găsi în articolul: Funcția Filter() din #Excel 365. Reamintesc că ea este disponibilă în Excel din Office 365 și Excel 2021.

Celula I2: =MIN(FILTER($C$2:$C$26;($A$2:$A$26=G2)*($B$2:$B$26=H2))) reprezintă un minim aplicat pe un filtru cu două condiții: Prima condiție se referă la codul produsului din G2 iar a doua la data obținută în H2.

Această tehnică ne permite să păstrăm sursa de date nealterată, pentru asigurarea trasabilității și în același timp putem replica mult mai rapid problema pe seturi diferite de date de la o perioadă la alta sau putem identifica raportul în timp real. Eu prefer tehnica Tables pentru sursele de date ceea ce mă ajută să nu mai refac formulele în momentul în care apar noi date în tabel.

Operațiunile descrise în acest articol sunt disponibile și în Google Sheets. Link aici la fișierul din Google Sheet.

 

Sper să vă fie util!

 

Blog la WordPress.com.

SUS ↑