Poanta zilei de astăzi, la cursul de Excel, lecția despre Conditional Formatting
Eu: “Ce culoare să alegem?”
O cursanta: “Fucsia!”
Altă cursantă: “Kaki!”
Eu: “Dacă alegem una din aceste opțiuni îi punem pe băieți în încurcătura!”
Un cursant: “Ba deloc! Eu prefer Nude!”
Se întâmplă, uneori, să uitați parola de deblocare unui sheet dintr-un fișier Excel. Ca să deblocați foaia de calcul, va trebui să urmați câțiva pași simpli:
Descărcați un program de dezarhivare care permite editarea arhivei (Eu folosesc, în exemplul curent, 7-Zip)
Salvați o copie a fișierului (în caz că nu vă iese din prima încercare!)
Schimbați setările de vizualizare a fișierelor în Windows Explorer pentru a vizualiza și extensia:
Windows7:
Windows 10:
Folosind comanda Rename, schimbați extensia fișierului din .xlsx în .zip și alegeți ”yes” în fereastra de atenționare.
Deschideți arhiva în 7-Zip.
În folder-ul XL se regăsesc schemele xml aferente fiecărei foi de calcul din fișier. Deschideți folder-ul Worksheets.
Deschideți fiecare schemă în notepad și ștergeți tot textul cuprins între semnele < și > ca în imagine:
Salvați fișierul xml.
Redenumiți arhiva schimbând extensia din .zip în .xlsx și gata!
Să considerăm un tabel cu r rânduri și c coloane. Se dorește identificarea rândurilor identice și semnalizarea, pe o coloană separată a listei de rânduri cu informații identice, ca în exemplul:
Pentru aceasta, am definit funcția dubluri(rand, tabel)
Funcția are două argumente de tip Range(zona de date)
Primul argument este rândul din tabel pentru care faceți analiza (de exemplu: C3:F3), cu adrese relative.
Al doilea argument este tot de tip Range dar, pentru el, selectați tot tabelul, cu referințe absolute (în cazul exemplului din fișier: $C$3:$F$7)
Pentru a o utiliza, deschideți editorul VBA, inserați un nou modul în care copiați codul funcției(de la option base 1 până la end function, inclusiv).
Funcția va fi plasată în categoria User Defined în cazul în care veți dori să o utilizați cu opțiunea Insert Function (butonul fx).
Option Base 1
Function dubluri(rng As Range, myregion As Range)
‘
‘Diana Tanase MCT officetraining.ro
‘pentru primul argument selectati randul curent pentru al doilea argument selectati toate randurile tabelului cu adrese absolute
‘
‘
Dim i, j As Long
Dim k As String
Dim comp As String, nr As String
c = rng.Columns.Count
r = myregion.Rows.Count
k = “”
For i = 1 To r
nr = “”
comp = “”
For j = 1 To c
nr = nr & “-” & rng(1, j)
comp = comp & “-” & myregion(i, j)
Next
If nr = comp Then k = k & ” ” & i
Next
dubluri = k
End Function
In multe organizatii, se executa simultan mai multe proiecte. Daca resursele sunt intr-un numar limitat, proiectele sunt asignate acelorasi persoane, de unde si problemele legate de ajustarea calendarului, de gradul de incarcare al resurselor s.a.
Avem nevoie de o modalitate de gestionare in ansamblu a activitatilor din mai multe proiecte pentru a putea determina cea mai potrivita cale de alocare a resurselor si de stabilire a prioritatilor.
MSProject permite consolidarea mai multor proiecte intr-un singur fisier si utilizarea in comun a resurselor.
Un proiect consolidat nu este altceva decat un fisier .mpp normal in care se insereaza mai multe proiecte ca summary task-uri.
Pentru a realiza un master plan alegeti locul in care se va insera in lista task-urilor sale subproiectul si, din fila Project alegeti optiunea Subproject.
Initial, task-urile subproiectului sunt ascunse dar le puteti afisa cu un clic pe butonul Outline semnalizat cu semnul +.
Numarul maxim de subproiecte care vor putea fi inserate este 1000.
Daca nu se doreste posibilitatea de a interveni din master in subproiect, alegeti optiunea Insert Read – Only. Atentie, insa, ca aceasta optiune nu va permite actualizarea automata la modificarea informatiilor din subproiect.
Referintele, “adresele” celulelor din Excel sunt compuse dintr-o litera(identificatorul coloanei) si o cifra(identidicatorul randului) si se intalnesc in componanta formulelor astfel: =A1+C2
Formula de mai sus s interpreteaza ca suma valorilor continute de celulele specificate.
Daca valoarea oricarei celule se modifica atunci, valoarea formulei se va modifica de asemenea.
Ce se intampla cand copiem formula?
Se copiaza operatia si pozitia celulelor implicate in calcul fata de formula.
De exemplu, daca formula =A1+B1 se va copia din C1 in D2, celula D2 va contine suma vecinilor sai din stanga, si anume: =B2+C2
Cu alte cuvinte, daca voi copia formula cu un rand ma jos, toate argumentele “coboara” si ele cu un rand si daca voi copia formula cu o coloana la dreapta, toate argumentele se deplaseaza si ele cu o coloana la dreapta.
Sa presupunem ca avem o serie de valori (in EURO) scrise pe o coloana si dorim sa calculam o serie de procente (scrise pe rand), dar exprimate in lei.
Organizam datele ca in imagine:
In celula A1 avem cursul de schimb, ca antet avem valorile procentelor si, pe prima coloana, lista valorilor in Euro.
In celula B2 introducem formula: =A2*B1*A1
Ne dorim copierea formulei in zona de date a tabelului dar, intrucat adresele celulelor sunt relative, acestea isi vor pastra pozitia initiala fata de formula.
Ne dorim ca datele inscrise pe coloana A “sa ramana” pe coloana A si vom “bloca” identificatorul coloanei, introsucand semnul $ in fata literei A.
Analog, pentru procente, vom bloca cifra corespunzatoare randului, adaugand semnul $ in fata cifrei.
In concluzie:
Daca semnul $ se afla doar in fata literei se permite deplasarea argumentului doar pe verticala relativ la pozitia formulei in care este implicat, iar “blocarea” cifrei permite deplasarea pe orizontala.
Daca blocam atat coloana cat si randul ($A$1), la copierea formulei, adresa celulei A1 ramane neschimbata. Vom spune, in acest caz, ca $A$1 este o adresa absoluta.
A1 este referinta relativa, iar $A1 si A$1 sunt referinte mixte.
O zona compacta de date se adreseaza, in Excel, prin doua elemente: adresa celulei corespunzatoare coltului din stanga sus si cea corespunzatoare coltului din dreapta, jos.
Astfel, A1:C7 reprezinta zona formata din celulele cuprinse intre coloanele A si C si randurile de la 1 la 7.
In adresarea unei zone se poate bloca fie adresa frimai celule($A$1:C7), fie a celei de a doua (A1:$C$7), fie a amandurora ($A$1:$C$7) sau, se pot utiliza combinatii de referinte mixte, in functie de cerintele modelului pe care il prelucram.
O adresare de tipul $A$1:C7, permite extinderea zonei pe diagonala principala a matricii,
iar o adresa de tipul A1:$C$7 se foloseste daca dorim “micsorarea” zonei prin compresie pe diagonala principala.
Daca avem de redactat un document in care dorim sa inseram mai multe imagini pe care le-am pus deja intr-un folder, altul decat My Pictures, este destul de neplacut sa cautam locatia imaginilor de fiecare data cand actionam butonul Insert Picture.
Putem sa modificam locatia implicita(My Pictures) astfel:
File>Options> Advanced, grupul General, butonul File Locations si modificati locatia cum doriti.
Nu de mult, am vazut o imagine pe Linkedin, cu comentarii si aprecieri si m-am gandit la cat de creativi sunt utilizatorii de Excel in organizarea datelor.
Imaginatia umana nu are limite! Dupa ce isi pun imaginatia la lucru si organizeaza datele in varianta 3 din imagine, striga dupa ajutor, ca nu mai stiu cum sa le aduca in varianta a doua. Si apeleaza atunci la ajutorul meu si, al altora ca mine. In acel moment, orice analist de date îi roaga sa le trimita varianta bruta a datelor (varianta 1). Si atunci, ce-i de facut? Nu poti sa pui stavila imaginatiei! Le explici, pas cu pas cam cat timp se economiseste daca intelegi “filozofia” din spatele datelor, si faptul ca, modul in care percepem noi o informatie nu coincide cu modul in care este “citita” de o aplicatie.
Iata si cateva exemple:
1. Se da urmatoarea lista(sursa: ModelOff):
Se cere sa se analizeze consumul mediu lunar pe intervale orare(pe 4 ore consecutive)
sau:
2. Sa se aduca informatia din imagine la forma corecta:
Daca in prima situatie, au fost introduse informatii din mai multe surse sau de catre utilizatori diferiti, in al doilea caz, dupa cum arata datele, sunt “importate” dintr-o aplicatie externa cu clasica metoda “copy-paste”. Si sunt doar doua exemple din sutele de situatii similare cu care m-am confruntat de-a lungul timpului. Dar, sa nu punem stavila imaginatiei ci, doar sa intelegem ca “masina” din fata noastra nu are personalitate, nici imaginatie, nici constiinta proprie si, niciun computer, oricat ar fi de performant, nu inlocuieste (inca) gandirea umana si ca nu “face” nimic de unul singur ci doar executa o comanda (buna, rea, dupa caz)
Avem câteva coduri de produse de genul:
123abc
abcd1234
12389aaaaaaa
Dorim să extragem doar cifrele. Cu funcţii de tip text e o întreaga nebunie. Iată însă două funcţii, una care extrage cifrele iar cealaltă literele dintr-o expresie de genul celor de mai sus.
Function NumberOut(rng As Range)
Dim i As Integer
For i = 1 To Len(rng)
Select Case Asc(Mid(rng.Value, i, 1))
Case 0 To 64, 123 To 197
Case Else
NumberOut = NumberOut & _
Mid(rng.Value, i, 1)
End Select
Next i
End Function
Function LetterOut(rng As Range)
Dim i As Integer
For i = 1 To Len(rng)
Select Case Asc(Mid(rng.Value, i, 1))
Case 0 To 64, 123 To 197
LetterOut = LetterOut & Mid(rng.Value, i, 1)
End Select
Next i
End Function
Ce trebuie să faceţi?
Copiaţi cele două coduri, deschideţi fereastra Visual Basic Editor (ALT+F11), în ferastra VBE, din meniul INSERT inseraţi un modul nou şi daţi Paste.
Ne întoarcem în Excel, şi cu Insert Function (butonul fx) introducem din categoria User Defined, funcția dorită.
Introducem celula care contine codul alfanumeric și…gata
La unul din cursurile la care prezentam instrumentul Conditional Formatting, un participant m-a întrebat cum se poate afișa pictograma lângă setul de date pe care le semnalizează.
În primul rând, cum se utilizează pictogramele în formatul condiționat?
Selectăm setul de date, și din meniul Conditional Formatting alegem opțiunea Icon Sets cu o variantă oarecare.
Fiecărei valori i se va atașa o pictogramă. De ce aceea și nu alta?
Evaluarea logică are 4 variante, cea implicită fiind procent din valarea maximă a setului respectiv.
Cele 4 variante sunt: Number, Percent, Formula și Percentile.
Prima opțiune o alegem când dorim să stabilim o serie de praguri numerice, spre exemplu, clasificarea angajaților în funcție de vechime sau a persoanelor, pe categorii de vârsta.
A doua este cea implicită. Procent din valoarea maximă.
Formula este o formulă logică(returnează TRUE sau FALSE) pe care o construim noi.
Percentile, clasifică informația după valori și după numărul de elemente din set. ( cele mai mici 20% valori , între 20% și 40%, între 40% și 60%, între 60% și 80% și peste 80%, pentru un set de 5 pictograme, spre exemplu).
Cum afișăm setul de pictograme lângă valorile pe care le reprezintă?
Duplicăm coloana cu valori (eu folosesc de obicei o formulă de genul ”=A2” ca să păstrez legătura cu valoarea sursă). Pe coloana nou creată aplicăm formatul condiționat dorit. Deschidem fereastra Conditional Formatting Rules Manager ( din meniul Conditional Formatting- Manage Rules) , selectăm regula pe care dorim să o edităm și apăsăm butonul Edit Rule. Putem modifica regula de formatare, stilul șu numărul de pictograme, putem alege pentru fiecare categorie ce pictogramă dorim.
Schimbăm regula în number pentru fiecare categorie, introducem pragurile dorite (cele mici jos și crescător în sus) și bifăm caseta ”Show Icon Only”, ca în imagine:
Și obținem o coloană doar cu pictograme.
Ca să introducem o legendă, vom scrie pragurile (valoarea minimă trebuie introdusă separat, și apoi se continuă cu pragurile numerice stabilite, în cazul de față voi introduce lista 0, 5000, 10000, 15000 și 2000). Duplicăm această coloană și, ca să nu mai reluăm pașii de formatare condiționată, vom copia regula cu Format painter, ca în animația de mai jos:
Voi răspunde aici solicitării unei cititoare (Anca).
Să se întocmească un formular de comandă către departamentul Aprovizionare,
pentru piesele de schimb la care stocurile existente au scăzut sub cele normate.
Formularul de comandă va cuprinde: Cod piesă, Denumire piesă, UM, Cantitate necesară.
Tabelul sursă arată așa
:
Am realizat două variante de rezolvare a cerinței propuse:
Pasul 1:
Identificăm rândurile din tabel care corespund stocurilor sub normă și le numerotăm.
Pentru aceasta, în foaia cu tabelul susă(Sheet1) vom atașa o nouă coloană (Status) cu următoarea formulă:
=IF(G6<D6,MAX($H$4:H5)+1,””)
Care se ”traduce” așa: Dacă stocul este mai mic decât norma, atunci afișează maximul dintre celulele de deasupra+1, dacă nu, nu afișa nimic.
Vom obține o numerotare a rândurilor din tabel care au stocuri sub normă.
Nu mai rămâne decât să extragem aceste rânduri, în fila Formular comanda.
Scriem în C6 (pentru denumirea piesei) următoarea formulă:
=IFERROR(INDEX(Sheet1!$B$6:$B$24,MATCH(ROWS($A$1:A1),Sheet1!$H$6:$H$24,0)),””)
Care se traduce astfel:
Dacă la căutarea numerelor 1,2,3,4……. în coloana H (cea cu numerotarea) obții eroare, atunci nu afișa nimic, dacă nu , caută rândul cu numărul respectiv(returnat de match) pe coloana cu denumiri (B6:B24) și scrie-mi ce găsești acolo.
După care ”tragem” până obținem prima celulă goală.
În mod analog, în D6 scriem aceași formula cu diferența că la INDEX nu mai căutăm pe coloana B din Sheet 1 (Denumirea) ci pe C (UM)
=IFERROR(INDEX(Sheet1!$C$6:$C$24,MATCH(ROWS($A$1:A1),Sheet1!$H$6:$H$24,0)),””)
Iar pentru cantitatea necesară (în E6) vom folosi două funcții de căutare (INDEX), una care să ne dea norma și cealaltă să ne dea stocul, facem diferența între ele și aplicăm iferror ca să nu avem afișate erori (dacă INDEX nu găsește informația)
=IFERROR(INDEX(Sheet1!$D$6:$D$24,MATCH(ROWS($A$1:A1),Sheet1!$H$6:$H$24,0))–
INDEX(Sheet1!$G$6:$G$24,MATCH(ROWS($A$1:A1),Sheet1!$H$6:$H$24,0)),””)
Este varianta pe care o prefer, este mai rapidă, poate fi înregistrată într-un macro și nu încarcă fișierul cu formule care în cantitate foarte mare îngreunează funcționalitatea aplicației când modul de calcul este cel automat.
Pentru această variantă va trebui să facem câteva modificări.
Vom atașa tabelului sursă coloana Cantitate Necesară în care vom face diferența între normă și stoc, lăsând blank celulele pentru care diferența este pozitivă.
Selectăm apoi din antetul tabelului (cu CTRL) celulele cu Denumire, UM și Cantitate Necesară în foaia Formular comanda. Acestea sunt coloanele pe care le extragem din tabelul sursă dacă stocul trebuie actualizat.
În fila Formular comanda, în A2, vom scrie condiția pentru care facem filtrarea, pentru primul rând din tabelul sursă (=Sheet1!G6<Sheet1!D6). Celulele A1:A2 reprezintă criteriul pentru filtrarea avansată (pentru mai multe detalii despre filtrul avansat și tipuri de criterii, vezi subiectele Filtru avansat și Advanced Filter Criteria )
Celula A2 va afișa TRUE sau FALSE, după caz.
Acum, nu mai rămâne decât să aplicăm Advanced Filter.
Selectăm cel puțin două celule pe verticală (așa vrea utilitarul, deoarece filtrarea necesită un tabel ca sursă și măcar două rânduri să aibă!) ca să putem iniția filtrarea avansată.
Eu am selectat zona C5:E6 din fila Formular comanda.
Din tab-ul Data, acționăm comanda Advanced care va deschide fereastra Advanced Filter în care completăm câmpurile, ca în imagine. (La mesajul de atenționare apăsați OK)
După care apăsați OK și…. gata!
Puteti ”colora” cu alb textul din celula A2 dacă e inestetic acel True.
------ Excel VBA Products ------
Daca rezolvarea unei probleme dureaza prea mult, sigur am luat-o pe un drum gresit!
Energia electrica o afacere rentabila si pentru tine
It's nice to be important, but it's more important to be nice
Despre Excel, Project, Access si alte programe Office 