Determinarea numelor unice dintr-un tabel #Excel

Una din problemele des întâlnite în practica de zi cu zi, este legată de realizarea unei liste de nume sau valori unice din unul sau mai multe tabele sau coloane.

În exemplul pe care vi-l propun este vorba despre o listă de echipe din care fac parte în mod dinamic unul sau mai mulți oameni.

Ca să putem determina din câte echipe face parte un anumit om, avem nevoie de determina numele unic al angajatului. Presupunem că aceste prenume reprezintă fiecare câte un om.

Primul lucru pe care trebuie să-l facem este să unim toate coloanele într-una singură. Un fel de copiere a valorilor unele sub altele.

Pentru aceasta putem utiliza funcția INDEX cu o combinație dinamică de linii și coloane):

=INDEX(aNume;

1+INT((ROW(A1)-1)/COLUMNS(aNume));

MOD(ROW(A1)-1+COLUMNS(aNume);COLUMNS(aNume))+1)

 

În exemplul meu am denumit zona de date cu numele aNume ca să nu folosesc adresarea absolută de tipul: $A$2:$D$8.

 

1+INT((ROW(A1)-1)/COLUMNS(aNume)) – Determină seturi de numere repetitive și consecutive pentru parametrul row_num al lui Index . Exemplu: 1,1,1,1,2,2,2,2,3….

MOD(ROW(A1)-1+COLUMNS(aNume);COLUMNS(aNume))+1) – Determină seturi de numere consecutive echivalente numărului de coloane a blocului de celule analizat.

 

Ca să pot copia formula în jos cu fill down am generat întâi o coloană de numere de dimensiunea tabelului: număr de linii * număr coloane.

=SEQUENCE(ROWS(aNume)*COLUMNS(aNume))

După ce avem lista de nume, aplicăm pe o coloană adiacentă formula de unicitate:

=SORT(UNIQUE(FILTER(G2:G27;G2:G27<>0)))

Rezultatul final în coloana H:

Aceeași operațiune se poate realiza și prin scripturile de automatizare, dar momentan apelarea relativă în Automate din Excel Online este în preview.

Versiunea cu script editor din Excel pentru a consolida cele 4 coloane de nume:

Dacă aveți alte metode de unifica mai multe coloane în una singură, puteți folosi secțiunea de comentarii.

 

Sper să fie util cuiva!

 

 

Funcția LET() în Excel

Astăzi trebuia să calculez punctajele și mediile studenților pentru evaluarea pe parcurs. Exemplu în imagine, ordonat după punctajul obținut.

Întotdeauna mi-a plăcut să le ofer studenților opțiuni de îmbunătățire a rezultatelor, iar facultatea ne permite să acordăm bonusuri pentru activități extracuriculare. În exemplul acesta, studenții care au participat la un curs facultativ și au obținut calificative din partea unei companii partenere a facultății au obținut un bonus de un punct care se adaugă la rezultatele obținute în timpul semestrului. Având în vedere că de obicei studenții cei mai buni, sunt și cei care accesează toate oportunitățile care le sunt puse la dispoziție, avem un fenomen că aceștia au depășit nota 10.

Ca să calculez punctajul studenților am folosit media ponderată care le oferă un punctaj de maxim 5 puncte, care înmulțit cu 2 se transformă în nota finală, la care se adaugă punctul de la Club (celula F2). Ca să afișez doar două zecimale am folosit funcția TRUNC().

Formula inițială: =TRUNC((C2*0,1+D2*0,3+E2*0,1)*2+F2;2)

Pentru că nota care se trece în catalog NU poate depăși nota 10, avem mai multe metode, cea mai frecvent întâlnită și mai incorectă este de a „strica” formula și a completa automat cu nota 10. O altă metodă, folosită de cei care lucrează puțin mai bine cu Excelul este de a folosi o funcție IF() care să compare rezultatul obținut cu 10. Dacă nota este mai mare ca 10, rezultatul IF-ului va fi 10. Altfel va fi din nou media ponderată.

Exemplificare cu IF: =IF(TRUNC((C2*0,1+D2*0,3+E2*0,1)*2+F2;2)>10;10;TRUNC((C2*0,1+D2*0,3+E2*0,1)*2+F2;2))

Această metodă complică puțin actualizarea formulei și are o lungime de 84 de caractere. În ziua de astăzi, în abordarea Modern Excel este de a folosi tehnici de a reduce cât mai mult complexitatea formulelor.

O funcție suport este funcția LET() care are următoarea sintaxă:

LET() permite definirea uneia sau mai multor variabile și a valorilor acesteia după care se realizează calculul.

În exemplul meu, am nevoie de o singură variabilă care va lua valoarea din formula inițială. Apoi în partea finală de LET() folosesc funcția IF() pentru a determina rezultatul comparat cu nota 10.

Astfel formula mea din G2 devine:

=LET(Media; TRUNC((C2*0,1+D2*0,3+E2*0,1)*2+F2;2); IF(Media>10;10;Media))

În care Media ca nume de variabilă (name din sintaxă), ia în mod dinamic valoarea TRUNC((C2*0,1+D2*0,3+E2*0,1)*2+F2;2) (name_value), iar în partea treia a formulei am IF-ul de comparația cu nota 10 (IF(Media>10;10;Media)) (calculation).

Funcția LET() permite definirea până la 126 de variabile.

În exemplul prezentat în imaginea următoare am implementat 3 variabile, din care una pentru calcul punctaj curs (MedCurs), una pentru calcul punctaj laborator (MedLab), o medie finală prin însumarea celor două medii intermediare (MedFin) și la final formula de validare a notelor peste 10.

Exemplu cu trei variabile:

Pentru a scrie în formula bar pe mai multe linii eu folosesc combinația de taste Alt (Stanga)+Enter pentru trecere pe rând nou.

În Google Spreadsheets nu am identificat implementarea funcției LET().

Sper să fie util cuiva!

Referințe suplimentare:

Recuperare parola fisier Excel sau Word

De fapt titlul este unul de tip clickbait… articolul este despre eliminarea parolei de editare a unui fișier Excel sau Word.

În urmă cu câteva zile, un coleg de la o firmă pentru care lucrez, mi-a povestit că un fost coleg a plecat din firmă și nu a lăsat parola de la un fișier template de Word parolat la editare. Ca să poți edita/actualiza acel document ai nevoie de parola de editare, altfel nu prea ai cum… La fel se poate întâmpla și cu documentele Excel care au fost protejate prin parola la editare de către utilizatori. Îmi povestea că a încercat tot felul de soluții de pe Internet … dar nu prea a găsit ceva free, iar cele on-line nu fac mare lucru. Mai sunt metode de a malforma documentul prin WordPad… dar se modifică mult structura, mai ales în Word dacă ai casete de fill-in sau alte texte.

Dacă nu știți cum să protejați un fișier Excel la editare, după acest articol nici nu veți mai considera util să învățați. Dar dacă totuși vreți să protejați anumite zone dintr-o foaie de calcul, puteți urmări detaliile și metodele din acest filmuleț de pe Youtube.

Articolul nu se aplică pentru parola de deschidere a fișierului…(Encrypt with Password) care la versiunile mai noi de Office fac criptarea conținutului și nu mai pot fi deschise dacă nu ai parola. În imagine metodele de protecția pentru un document Word și Excel din meniul File, Info.

Revenind la problema editării fișierelor parolate, procedura pe scurt de eliminare este următoarea:

  1. Creați o copie a fișierului parolat.
  2. Redenumiți fișierul parolat și schimbați extensia acestuia în .ZIP
  3. Deschideți fișierul ZIP
  4. Accesați locația XL\Worksheets și localizați fișierul XML care are numele foii de calcul protejate la editare, în cazul meu sheet1.xml

  5. Editați fișierul XML cu un editor de text. Eu folosesc Notepad++
  6. Localizați în fișierul XML linia specifică marcajului <sheetProtection … /> (exemplificare în imagine) și ștergeți această linie.

    În Word găsim în folderul Word fișierul Settings.xml marcajul <w:documentProtection ..

  7. Salvați fișierul XML și închideți
  8. Ieșiți din arhivă, redenumiți fișierul cu extensia .XLSX
  9. Deschideți și lucrați.

 

O demonstrație video este disponibilă pe Youtube.

Recomandarea mea pentru protecția documentelor este să folosiți sisteme de management al documentelor dedicate, exemplu SharePoint, și să alocați corect seturi de permisiuni și versionare pe bibliotecile de documente. De asemenea, SharePoint prin intermediul listelor va permite crearea de versiuni granulare la nivel de „linie”, amintind în același timp de beneficiile formularelor PowerApps cu business logic implementat și a fluxurilor Power Automate.

Sper să vă fie util.

Blog la WordPress.com.

SUS ↑