Problemă de #Excel – Cine este plecat în delegație? [UPDATED]

De câteva zile am primit o problemă interesantă de Excel. Și cum mulți dintre cititorii mei sunt avizi după noi provocări vă invit să rezolvați această problemă de Excel, fără utilizare VBA sau coloane ajutătoate.

Cerința problemei:

Într-o instituție oamenii pleacă în delegații. Istoricul de delegații este în tabelul A1:D11 dar poate fi mult mai lung, pentru că acolo sunt păstrate detaliile tuturor deplasărilor. Se dorește să se afișeze un tabel dinamic în care să se afișeze persoanele aflate în delegație în ziua curentă.

Model tabel și raport sintetic. Datele rezultat sunt corespondente zilei de 04.11.2019.

După rezolvare, soluția Excel trebuie transmisă prin e-mail la adresa: valy.greavu@outlook.com până pe 06.11.2019 ora 10:00.

Dacă se poate să treceți în email și o scurtă descriere a rezolvării ar fi super! Dacă nu mă prind eu, cumva. :)

Premiu: 10 „flotări mentale” și recunoștința noastră!

UPDATE 06.11.2019

În urma consultării din public am primit mai multe soluții care de care mai ingenioase.

Soluția mea este o combinație de CSE (acronimul de la funcțiile array Ctrl+Shift+Enter) cu funcțiile INDEX și MATCH cu condiții multiple.

Formula folosită pentru nume este:

=IFNA(INDEX(tblD[Nume_Prenume];MATCH(1&0;IF(TODAY()>=tblD[Data_Plecarii];IF(TODAY()<=tblD[Data_Sosirii];1))&COUNTIF($F$2:F2;tblD[Nume_Prenume]);0));””)

În care tblD este numele tabelului de date iar condițiile cumulative 1&0 sunt corespondente IF-ului din prima parte a MATCH-ului (1) și COUNTIF-ului din partea a doua care determină unicitatea înregistrărilor.

Listingul de la PowerQuery pentru acest model de denumiri este:

let

Source = Excel.CurrentWorkbook(){[Name=”tblD”]}[Content],

#”Changed Type” = Table.TransformColumnTypes(Source,{{„Nr.Crt.”, Int64.Type}, {„Nume_Prenume”, type text}, {„Data_Plecarii”, type datetime}, {„Data_Sosirii”, type datetime}}),

#”Added Custom” = Table.AddColumn(#”Changed Type”, „Plecat”, each Logical.From(DateTime.Date(DateTime.LocalNow()) >= DateTime.Date([Data_Plecarii]) and DateTime.Date(DateTime.LocalNow()) <= DateTime.Date([Data_Sosirii]))),

#”Filtered Rows” = Table.SelectRows(#”Added Custom”, each ([Plecat] = true)),

#”Removed Columns” = Table.RemoveColumns(#”Filtered Rows”,{„Nr.Crt.”, „Data_Plecarii”, „Plecat”}),

#”Changed Type1″ = Table.TransformColumnTypes(#”Removed Columns”,{{„Data_Sosirii”, type date}})

in

#”Changed Type1″

Nu uitați că PQ este case sensitive. Sunt un mare fan al PQ dar acesta nu-și face autorefresh la date în momentul în care se schimbă ceva în sursă. Trebuie refresh manual.

Fișierul cu exemplul rezolvat cu CSE și PQ poate fi consultat sau descărcat de aici: https://1drv.ms/x/s!ApGubfWFh8NurJgyC1qAhvQ8bcjwuQ?e=0eo2dT. Fișierul nu conține macros.

 

Soluția CiprianS

Prima soluție a venit de la un prieten mai vechi Ciprian S.

Ciprian a propus o rezolvare cu PowerQuery care este relativ simplă și funcționează perfect și una cu CSE.

Formula utilizată de Ciprian pentru afișare nume este:

=IFERROR(INDEX(Table1[Nume_Prenume];SMALL(IF(–(TODAY()>=Table1[Data_plecării])–(TODAY()<=Table1[Data_Sosirii])=2;Table1[Nr.Crt.];””);ROWS($K$10:K11)-1));””)

Formula folosește SMALL în loc de MATCH-ul clasic propus de mine. În felul acesta soluția devine puțin mai rapidă pe seturi de date mari.

Soluția SilviuP

O soluție foarte simplă de rezolvare a venit de la SilviuP. Acesta folosește funcția IF imbricată pentru a afișa dacă un anumit om este plecat în delegație sau nu. Pentru cei care nu sunt plecați lasă liber rândul în raport. Din această cauză această soluție nu este una câștigătoare. :)

Proiecție soluție cu IF.

 

Soluția ClaudiaT

O altă soluție simplă a venit de la ClaudiaT. Ea a folosit un pivot table legat la un Timeline Tool pentru a selecta intervalele de date specifice plecării și sosirii. Aparent este o soluție simplă și uneori soluțiile simple sunt mai la îndemâna tuturor!

Proiecție soluția cu Timeline.

 

Soluția CristianA

CristianA a trimis mai multe rezolvări. Așa cum spunea el… prea simplă problema.

În prima soluție El a folosit formula IF asemănător cu soluția lui Silviu apoi a filtrat rezultatele. Eu cred că s-a grăbit puțin.

A doua soluție este asemănătoare ca formă și rezultat cu PowerQuery-ul trimis de Ciprian.

A treia soluție este în schimb destul de abstractă și folosește DAX pentru generarea tabelului de date.

Proiecție rezolvare cu DAX.

De asemenea Cristian mai anunță o metodă de rezolvare de ultimă oră și anume utilizarea Dynamic Arrays. Detalii aici: https://www.excelcampus.com/functions/dynamic-array-formulas-spill-ranges/ și aici: https://www.sumproduct.com/news/article/news/dynamic-arrays-becoming-generally-available

 

Pentru cei care doresc să învețe mai multe despre PowerQuery și formule CSE găsiți detalii în blog și pe canalul meu de Youtube!

 

Sper să fie util cuiva!

#Excel – Aplicare culori de fundal pentru rândurile alternative

De foarte multe ori în Excel trebuie să urmărim linii de date pentru a compara valori sau alte scopuri. Dacă totul este în negru pe alb, operațiunea devine obositoare. Cea mai comună operațiune este să delimitezi cumva liniile între ele prin aplicarea unei formatări de tip Table din meniul Home. Se dă click pe prima celulă din tabelul de date apoi se selectează opțiunea Format as Table. Sigur acest lucru ne va ajuta ulterior și la formule prin ușurarea modului de scriere a referințelor din acestea.

Ce facem în schimb când tabelul nostru nu permite formatarea ca un Table? Cum adică să nu meargă?

Iată un mesaj de eroare mai jos.

Sau atunci când datele sunt ținute în formatul de raport direct și sunt „îmbunătățite” cu multe operațiuni Merge sau alte formatări „faine”.

 

În cazul în care nu doriți să utilizați formatarea ca tabel, există opțiunea formatării manuale… Glumeam, desigur! :)

 

Pentru formatarea alternativă cu o culoare de fundal, putem încerca utilizarea formatării condiționate bazate pe linia pe care se află datele.

Ca să aflăm valoarea liniei pe care se află datele putem utiliza funcția =ROW(). Funcția respectivă returnează un număr par sau impar care împărțit la 2 va returna un număr cu zecimale xyz,5 pentru numerele impare și un număr fără zecimale pentru numerele pare. Folosind funcția de determinare a întregului (INT()) din împărțirea numărului liniei la 2 și scăzând această valoare din valoarea inițială vom obține alternativ pentru liniile din Excel valorile 0 (zero) sau 0,5. Pe baza acestei valori putem aplica formatarea condiționată și aceasta se păstrează pentru tot tabelul de date indiferent dacă facem sortare sau filtrare.

În final funcția de determinare a rândului par sau impar ar trebui să fie:

=ROW()/2-INT(ROW()/2)

Pas cu pas.

  1. Se selectează prima celulă din tabelul cu date;
  2. Se apasă combinația de taste Ctrl+A pentru selectarea întregului tabel
  3. Din meniul Home se alege Conditional Formatting, New Rule

  4. În fereastra New Formatting Rule se alege opțiunea Use a formula to determine which cells to format apoi în secțiunea de formulă se introduce formula:

    =ROW()/2-INT(ROW()/2)=0

  5. Se apasă apoi butonul Format și din secțiunea Fill se alege culoarea de fundal dorită după care se confirmă cu Ok.

     

    Observație: În cazul în care capul de tabel începe de la o linie pară și nu doriți ca acesta să fie formatat la fel cu restul liniilor atunci puteți folosi funcția: =ROW()/2-INT(ROW()/2)>0

Microsoft are pe site-ul său o metodă mai elegantă dar puțin mai abstractă a acestei formule, recomandând utilizarea funcției MOD. Conform articolului, funcția de la punctul 4 din etapele de mai sus devine:

=MOD(ROW();2)=0

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

 

Microsoft #Excel – Spreadsheet Compare

De foarte multe ori în activitățile noastre zilnice realizăm foi de calcul în Excel pe care le modificăm pe parcursul mai multor zile. În lipsa unui sistem de management al documentelor (gen SharePoint on-premises sau din Office 365), o practică defectuoasă, frecvent întâlnită, este aceea de a salva același fișier cu nume diferite și stadii diferite ale documentului: Listapreturi v1, Listapreturi final, Lista preturi final v2, și așa mai departe. Dacă trebuie să trimitem același fișier pentru modificări și revizii la alți colegi sau chiar la parteneri externi, manipularea datelor devine și mai greoaie, pentru că trebuie să identificăm modificările efectuate.

Nu știu câți dintre cititori folosesc utilitarul Spreadsheet Compare din edițiile 2013 și mai noi ale pachetelor Office. Acest articol își propune să prezinte sumar acest utilitar.

Dacă nu aveți instalat acest instrument pe calculator puteți opta pentru versiunea de control a versiunilor cu ajutorul funcției IF().

În exemplul din acest articol dispun de două versiuni ale aceluiași fișier, format din două foi de calcul: Lista stocuri V1.xlsx și Lista stocuri V2.xlsx salvate local în același director. Ele pot fi stocate și pe discuri diferite.

Pentru a deschide utilitarul dați click pe butonul Start și tastași numele utilitarului după care îl deschideți.

După deschidere apăsați butonul Compare Files și căutați cele două fișiere pe disc.

Puteți compara doar două fișiere odată, nu mai multe.

Apăsați apoi Ok și așteptați.

Fereastra de rezultat este împărțită în mai multe zone: conținutul celor două fișiere cu evidențierea modificărilor, zona de opțiuni pentru modificări, zona de detalii ale modificărilor și statistici cu numărul de modificări.

În imagine este specificat faptul că în a doua foaie de calcul a fost adăugată o nouă linie și apar valori diferite în fișierul al doilea prin introducerea lor (cele cu verde) sau valori modificate de formule în urma modificării valorilor introduse (cele cu turcoaz… – bărbații căsătoriți mă înțeleg ).

Opțiunile de afișare pot fi activate sau dezactivate din meniul de culori din partea stângă jos pentru a reflecta exact ceea ce dorim să evidențiem.

Operațiunile de Export a rezultatelor din meniu: Export Results și Copy Results to Clipboard pot fi utile în operațiunile de auditare a modificărilor și o evidențiere în timp a acestora.

În cazul în care fișierele Excel sunt protejate cu parole la deschidere, se pot gestiona parolele de acces cu ajutorul butonului Options, secțiunea Passwords.

Specificul secțiunii de parole este că ele nu sunt asociate unui fișier ci pur și simplu putem trece o listă de parole pe care le folosim la fișiere. Utilitarul încearcă toate parolele pe rând pentru a deschide fișierele.

 

Sper să vă fie util!

Blog la WordPress.com.

SUS ↑