Excel – Un alt VLOOKUP()

Cum semestrul acesta predau Excel la facultate o să vă mai scriu tot felul de funcții și probleme mai interesante.

Ieri am primit de la un prieten o problemă de clasa a IX-a. Parcă aș vrea să mai fiu eu elev în clasa a IX-a și să am profi care să mă învețe așa ceva.

„Să scrie o formulă în care să utilizeze o singură funcție, o singură dată, dar care să dea rezultatele de mai jos atunci când parametrul ia valorile:

0-99 lei    0%

100-299    2%

300-999    5%

1000-oricât    10.00%”

 

După mintea mea, aceasta este o problemă care se rezolvă simplu cu IF()-uri imbricate așa cum am explicat în acest articol.

Doar că cerința problemei este o singură funcție, o singură dată!

Soluția în acest caz poate fi:

 

Formula din B2:

=VLOOKUP(A2;{0\0;100\0,02;300\0,05;1000\0,1};2;TRUE)

în care:

{0\0;100\0,02;300\0,05;1000\0,1} este o matrice cu două coloane și 4 linii.

 

Varianta pentru Excel locale engleză: =VLOOKUP(A2,{0,0;100,0.02;300,0.05;1000,0.1},2,TRUE)

 

De ce (încă) VLOOKUP odată cu apariția magicului XLOOKUP() ? Pentru că XLOOKUP este disponibil doar în variantele MS Office 365 și mobile, tablete nu și pentru versiunile Retail sau mai vechi. De asemenea, folosește mai mulți parametri ceea ce „complică viața” utilizatorului începător.

Mai multe despre formulele matrice în acest articol: https://support.microsoft.com/ro-ro/office/reguli-%c8%99i-exemple-pentru-formulele-matrice-7d94a64e-3ff3-4686-9372-ecfd5caa57c7?ui=ro-RO&rs=ro-RO&ad=RO#ID0EAAFAAA=Office_2010_-_Office_2019

 

Sper să vă fie util.

Trebuie?

Republicare pe pe platforma Scrisul face bine (2017)

Trebuie să merg la muncă, … să-mi termin prezentarea, … să merg la ore. Trebuie să învăț! Să mănânc! Să dorm! Acest „Trebuie” ne ține permanent ocupați exercitând o presiune constantă pe noi, pe mintea noastră, pe gândurile private de un cuget prelung, pentru că tot timpul trebuie… altceva. „Trebuie” devine mult mai important decât ceea ce vrem sau dorim. Devine uneori singura motivație a existenței și a mersului înainte, pentru că toți din jurul nostru trebuie să facă ceva.

Dar niciodată nu trebuie să visăm. Să contemplăm. Să zâmbim. O facem spontan și natural ca un reziduu romantic al rămășițelor de umanitate, aflate într-o inerție precară a evoluției sentimentale. Dar, o facem și artificial… dacă trebuie.

Suntem captivi în vârtejul unei societăți cu o dinamică uluitoare, în fața unei idei distorsionate despre ceea ce suntem sau am putea fi în social media, a manipulării fără perdea din fața micului ecran și, tot mai des, a compresiei acțiunii unui film de 90-100 de minute, ai cărui eroii, se nasc, cresc, iubesc și salvează lumea. Toate acestea ne induc senzația că nu facem lucrurile suficient de repede sau de bine. Secolul Vitezei, pe care cu unele accidente, l-am depășit cu bine, a scris probabil cele mai multe pagini ale istoriei, dar nu ne-a învățat să trăim mai repede. Am învățat în schimb să adoptăm visele altora sau modul lor de a fi. „Trebuie” ne învață că este mai bine să fim ca ei decât să fim noi înșine.

Într-o mare de „Trebuie”, sala de curs rămâne unul din puținele locuri, în afara căminului, în care îmi permit să fiu idealist fără a cădea în derizoriu. Știu că este plină de oameni care trebuie să fie acolo, așa că singurele acțiuni realizabile, dincolo de motivul lor, în tentativa de a aprinde scântei de speranță și înțelegere, este să-i provoc la contemplare, la vis (în mod figurat) și să-i trezesc cu zâmbete, chiar ironice, la o realitate pe care ar putea să o construiască altfel decât „Trebuie”.

Dincolo de a oferi cunoaștere și de a forma competențe, sala de curs rămâne spațiul sacru în care studentul învăță să formuleze întrebări, pentru că răspunsul pe care îl caută este mai valoros decât cel pe care îl primește. A devenit o modă să „ne luăm” înțelepciunea din citate, uneori obscure, fără a analiza amplitudinea faptelor, evenimentelor și succesiunii de idei deterministe. Zâmbim în acord și trecem mai departe. Dar căutarea, nevoia de întrebare și răspuns rămâne închisă în noi, în lipsa unei metode de a păși pe o cale sau alta. Superficialitatea omniprezentă este ca marțipanul de pe tort: dă culoare dar ascunde adevărata savoare. Dorința de profunzime, de adevăratul gust al cunoașterii se naște doar dacă reușești să formulezi întrebări graduale implicând răspunsurile care pot construi propria formă a orizontului de cunoaștere.

Familia, Școala, Biserica devin din ce în ce mai mult convenții sociale pe care le urmăm pentru că așa trebuie, neînțelegând, în majoritatea cazurilor, adevărata lor semnificație. Educația creatoare de valoare (nu eficiență) este cea transmisă individual. Restul se cheamă învățământ. Metodele acestuia se schimbă și ele, dar profesorul are menirea de a rămâne un actor ancorat în realitatea contemporană, proliferând cunoașterea „acolo” și cu instrumentele cu care interacționează discipolii săi. Asta, dacă nu ne-am pierdut misiunea de a deschide noi canale de comunicare în detrimentul simbriei.

Evoluăm! Și poate că am ajuns acolo unde ne este locul. O cerere continuă de bunuri și servicii naște o cerere de forță de muncă eficientă și ieftină, constrângând sistemul educațional în a se adapta și a oferi cât mai multe produse pe piață, uneori la capacități de producție epuizate sau insuficiente. Ne apărăm adesea, cu expresia: Lucrăm cu materialul clientului, uitând că în fapt clientul final este societatea în care ne creștem proprii copii, iar o societate se dezvoltă continuu prin responsabilizarea individului privind viitorul mai bun al urmașilor săi. Gradul de inteligență al unei colectivități, comunități, regiuni sau nații este dat de splendoarea prostiei celui din urmă părtaș. Patri(di)oții spun că suntem un neam inteligent. Pentru că ne adaptăm vicisitudinilor ca individ, dar evoluția noastră ca societate sau comunitate este încă ancorată în idea supremă a nefericitului final al caprei vecinului.

Direcția de evoluție a educației, pe care noi înșine o proliferăm, nu pare a fi cea mai bună, pentru că nu este în conformitate cu metodele, instrumentele, valorile deceniilor apuse. Suntem parte a unui sistem care se adaptează holistic vremurilor, generând noi și noi tendințe, cerințe și percepții. Nu! Educația nu este ca un computer care poate fi restartat dacă nu merge prea bine sau este blocat de mult într-o buclă care generează aceleași erori. Dar nici ca motorul unui avion care trebuie reparat din mers, așa cum încearcă să o facă factorii politici. Educația este mai mult ca un dans (vezi Soha Mil Pasos) pe care unii performează din plăcerea mișcării, iar alții cu teama de a nu fi călcați pe picior.

Privind catedra ca pe o mică scenă profesorul poate juca rolul major în schimbarea lui „Trebuie” cu a Dori, a Visa, a Reuși. Asta nu pentru că trebuie ci pentru că, aparent, ne-o dorim cu toții.

Un VLOOKUP aproximativ

Am primit o întrebare de la un cunoscut, legată de un fel de „VLOOKUP” aproximativ. Știu că nu există așa ceva, dar regăsirea datelor după coduri parțiale este posibilă.

În exemplul de mai jos avem un tabel cu date cu coduri alfanumerice, iar în zona de căutare doar prima parte din cod. În G5 avem rezultatul căutării.

Pentru rezolvare am folosit o combinație de funcții INDEX, MATCH și FIND.

=INDEX($B$3:$B$8;MATCH(1;FIND(F5;$A$3:$A$8);0))

în care:

  • $B$3:$B$8 reprezintă blocul de celule cu denumirea care trebuie extrasă în funcție de codul din F5;
  • Funcția MATCH identifică numărul liniei pe care se află codul regăsit prin Find;
  • Funcția FIND, caută după textul din F5 pe blocul de celule $A$3:$A$8 returnând valoarea 1 după care se face MATCH-ul;

Valoarea 1 din FIND este echivalentă începutului de cod din blocul de căutare $A$3:$A$8. Dacă acel cod parțial se regăsește în interiorul coloanei ID și nu începe cu prima literă atunci FIND-ul returnează altă valoare decât 1, și formula nu mai funcționează ca în model.

În cazul în care cele două coduri sunt scrise cu caractere diferite, trebuie menționat că FIND este o funcție case-sesitive. Contează dacă este scris cu litere mari sau nu. Dacă aveți cazuri cu diferite caractere, atunci se recomandă utilizarea funcției SEARCH().

 

În cazul în care dorim să căutăm informații pe baza unor date parțiale din interiorul codului problema se complică puțin, pentru că trebuie să pornim căutarea dintr-o locație diferită de 1, iar funcția MATCH() folosește în căutare maximul valorilor de căutare din șirul de pe care se realizează căutarea.

În continuare un exemplu de problemă și funcția utilizată.

Pentru rezolvare s-a utilizat funcția:

=INDEX($B$17:$B$22;MATCH(MAX(IFERROR(SEARCH(F18;$A$17:$A$22;IFERROR(SEARCH(F18;$A$17:$A$22);));));SEARCH(F18;$A$17:$A$22);0))

În care:

  • MAX(IFERROR(SEARCH(F18;$A$17:$A$22;IFERROR(SEARCH(F18;$A$17:$A$22);));)) este echivalentul valorii 1 din funcția prezentată în exemplul anterior.

 

Aceste modele de implementare funcționează în acest fel doar în versiunile de Excel Online din Office 365.

Pentru alte versiuni, funcția FIND nu mai funcționează ca un Dynamic Array și trebuie scrisă ca o formulă CSE (Ctrl+Shift+Enter).

Sper să vă fie util!

Blog la WordPress.com.

SUS ↑