Multiple CSV Import in Excel

Una din acțiunile repetitive cel mai des întâlnită în Excel este legată de importul fișierelor multiple în Excel. Acest articol are drept scop prezentarea unei metode de semiautomatizare a importului de fișiere CSV cu structură comună în același fișier Excel.

Datele problemei

În locația C:\Import dispunem de 11 fișiere CSV cu structură asemănătoare.

În locația C:\Import avem un fișier de import cu structură asemănătoare fișierelor de importat.

Pasul 1. – Pregătirea importului

Deschideți Microsoft Excel (în cazul meu versiunea 2013)

Din meniul Data alegeți opțiunea From Text

Selectați fișierul Import.csv și apăsați Import.

În fereastra Text Import Wizard Step 1 of 3 selectați opțiunea My data has headers si asigurați ă este selctată metoda de separare Delimited.

Apăsați apoi butonul Next.

În pasul 2 identificați correct separatorii și selectații din secțiunea Delimiters. În exemplul nostru sunt delimitatori virgule și punct și virgula.

Apăsați apoi Next

În pasul 3 specificați tipul datelor. Pentru numere lungi recomandăm utilizarea formatului de tip text pentru a nu se transforma la import în numere științifice.

Apăsați apoi Finish.

În fereastra care apare apăsați Properties.

Apoi selectați opțiunea Refresh data when opening the file pentru Refresh automat sau deselectati pentru Refresh manual.

Apoi apăsați de două ori Ok.

Datele in Excel

Pasul 2 – Unificarea CSV-urilor.

Folosind un editor de text (ex. Notepad) deschideți fișierul Inport.csv și stergeți tot conținutul și salvați.

Deschideți command prompt și navigați în directorul cu fișierele CSV de important.

Succesiunea comenzilor în imagine:

Scrieți apoi comanda:

Copy *.CSV C:\Import\Import.csv

Comanda și execuția

Fișierul Import.csv după unificare

Pasul 3 – Refresh in Excel

Deschideți fișierul Excel creat la pasul 1.

Navigați în meniul Data și apăsați butonul Refresh All

Selectați din nou fișierul Import.csv și apăsați Import.

Verificați consistența datelor.

Sper să vă fie util!

12/07/2017

Filtre dinamice sau interdependente în Excel cu Data Validation

Acest articol este un fel de continuare a articolului în care a fost explicată funcția OFFSET(). Pentru a înțelege mai ușor acest articol este recomandat să descărcați fișierele din imaginile prezentate.

Una din problemele interesante în elaborarea rapoartelor economice dinamice constă în realizarea de filtre dinamice pentru date. Aplicabilitatea filtrelor dinamice are sens doar în momentul în care datele sunt clasificate pe diferite categorii, categorii care pot fi uneori clasificate pe subcategorii.

Cerința problemei

Extragerea stocului din tabelul de stocuri pe baza categoriei și a denumirii produsului. Denumirea produsului trebuie să poată fi selectată doar în raport cu categoria selectată.

Pașii de rezolvare

Având în vedere modul complex de funcționare a funcțiilor următoare, pentru rezultate corecte se recomandă ca tabelul de date să fie sortat după Categorie. În cazul în care avem mai multe valori interdependente acestea trebuie sortate pe niveluri de subordonare corecte semnificației acelor date.

În tabel categoria de produs poate apărea pe mai multe linii. În acest context, pentru a putea implementa un filtru dinamic, avem nevoie de valoarea unică a categoriei.

Pentru a obține valoarea unică a categoriei, am creat zona categorii K3:K25 care conține o funcție INDEX() de extragere a valorii unice.

În celula K4 am inserat formula:

=IFNA(INDEX(tblStoc[Categorie];MATCH(0;COUNTIF($K$3:K3;tblStoc[Categorie]);0));””)

care permite indexarea valorilor de pe tblStoc, coloana Categorie în funcție de poziția unică a unui element, determinat pe egalitatea lui CountIf față de categoriile de mai sus cu 0. Este în fapt un artificiu prin care se păstrează doar unicatele din listă. IFNA() afișează un spațiu liber, în celulă, dacă în tabel sunt mai puține categorii decât formulele copiate în jos. Formula din K4 este o funcție array, deci trebuie introdusă cu CTRL+SHIFT+ENTER.

Pasul următor a fost definirea zonei de celule de categorii. Se selectează o zonă mai mare de celule de pe coloana K după care se apasă combinația de taste Ctrl+Shift+F3

După care se apasă Ok și vom putea apela blocul de celule după numele Categorii.

Pentru a ne asigura că numele este creat corect putem apăsa combinația Ctrl+F3 pentru afișarea numelor blocurilor de celule din fișierul curent.

Pentru definirea listei pentru celula de categorie (I3), se accesează meniul Data, DataValidation, Allow: List și se trece la sursă numele blocului de celule:

Pentru afișarea doar a produselor din categoria selectată la I3 va trebui să folosim o formulă OFFSET care să indexeze conținutul tabelului de date pe baza valorilor din categoria selectată.

Una din limitele Excel-ului este dată de faptul că în casetele de validare a datelor nu se pot folosi nume de tabele și coloane din tabelele de date. Poate în edițiile viitoare va fi rezolvată această problemă. De exemplu în versiunea Excel 2016 se poate scrie la sursa în Data Validation numele unui tabel și a unei coloane asociate, se selectează corelat zona de date, dar nu este permisă validarea formulei introduse în acest format.

Revenind, pașii pe care trebuie să-i parcurgem pentru a putea selecta doar produsele din categoria selectată, accesăm celula I4, meniul Data, Data Validation, Allow, List iar la sursă se va scrie formula:

=OFFSET($A$2;MATCH(I3;$C$3:$C$10;0);1;COUNTIF($C$3:$C$10;I3);1)

în care:

– $A$2 – reprezintă celula de start a tabelului cu date care va fi indexat;

– MATCH(I3;$C$3:$C$10;0) – reprezintă linia din tabel în care se regăsește prima înregistrare din categoria specificată în I3. (Reamintesc faptul că tabelul trebuie să fie sortat după categorie);

– 1 – reprezintă numărul coloanei față de $A$2 de pe care se vor extrage datele. În cazul prezentat denumirea produsului este pe a doua coloană ceea ce înseamnă prima coloană față de referință;

– COUNTIF($C$3:$C$10;I3) – reprezintă numărul de linii aferente produselor din categoria selectată. Acest număr specifică parametrul opțional height al funcției Offset. În cazul tabelului prezentat pentru produsele din categoria Panificație vom obține un vector (array) cu 3 înregistrări;

– 1 de final reprezintă lățimea vectorului, în cazul nostru o coloană.

În cazul în care doriți să studiați mai în amănunt modul în care a fost creat acest exemplu, puteți descărca fișierul de aici.

Un exemplu mai elaborat

Un alt exemplu de problemă este aceea în care dorim mai multe filtre interdependente.

Cerința este de a realiza un raport dinamic pentru o sursă de date de vânzări care să permită pe baza mai multor selecții, afișarea dinamică a unui tabel cu date.

Pentru câteva explicații, deschideți fișierul Filtre Dinamice. Atenție nu puteți lucra cu acest fișier on-lie. Trebuie descărcat local. Fișierul nu conține macros.

În foaia de calcul DateVanzari este stocată sursa de date. Această sursă este sortată după Continent, Tară, Categorie și nume Produs.

În foaia de calcul DateUnice, care în practică poate fi ascunsă, am implementat un mecanism de identificare a valorilor unice, specifice filtrelor selectate. De exemplu, dacă se va selecta un anumit continent pe coloana de Țări, vor apărea toate țările specifice acelui continent, corespondent fiecărei înregistrări din tabelul sursă. Pentru a stabili unicitatea țărilor pe coloana Tara am extras valorile unice înregistrărilor de pe coloana Țări. La fel am procedat și cu coloanele Categorii/Categorie.

Pasul următor a fost atribuirea numelor blocurilor de celule pentru a le folosi pentru filtrele din foaia de calcul Raport.

Formulele din DateUnice sunt combinații de Offset și Index/Match, asemănătoare celor explicate anterior. Limitele acestui model sunt date de faptul că nu putem folosi mai mult de 3 coloane imbricate. De exemplu dacă dorim să introducem și o coloană de Produse/Produs vom observa că Excelul oferă și alte valori decât cele specifice categoriei de produs. Aceeași problemă apare și în versiunea Excel 2016.

În zona de raport am implementat Data Validation pentru celulele corespondente continentului, țării și categoriei, după care am indexat dinamic datele din tabel pentru afișarea lor și am folosit formatarea condițională pentru afișarea dinamică a liniilor de raport și un simplu IF pentru determinarea poziției liniei de total.

Un must have pentru un raport ar fi acela de generare a unor grafice dinamice, genul celui din imagine.

Astfel de grafice pot fi fezabile doar pentru același număr de linii rezultat.

O soluție alternativă, mai simplă, dar cu neajunsurile sale, la această problemă, este utilizarea tehnicilor de Advanced filter.

Sper să fie util cuiva. Vă rog să nu ezitați să dați comentarii acestui articol în cazul în care aveți metode mult mai utile sau ușoare de filtrare avansată sau dacă ați descoperit erori în acest articol. Vă mulțumesc.

16/05/2017

#Excel – Funcția OFFSET()

OFFSET() este o funcție complexă care poate fi folosită în căutare în mod singular (extrage o singură valoare) sau în mod array pentru extragerea mai multor valori dintr-un bloc de celule.

Sintaxa: OFFSET(reference, rows, cols, [height], [width])

Referința reprezintă locul din foaia de calcul de raportare la indexul de căutare.

În cazul foii de calcul date, putem alege celula A2 ca referință, aceasta devenind în modelul prezentat coloana 0 (zero) linia 0 (zero). Comparativ VLOOKUP() folosește numerotarea de la 1 a coloanelor la fel ca și INDEX() și MATCH().

Parametrii obligatorii rows și cols specifică numărul de linii, respectiv coloane de la care se realizează extragerea datelor în raport cu referința specificată.

Exemplu:

=OFFSET(A2;0;0) va extrage valoarea 1, valorile 0;0 precizând că se va extrage valoarea exact din celula de referință.

=OFFSET(A2;1;1) va extrage valoarea 7, iar

=OFFSET(A2;4;4) valoarea 25.

În contextul în care dorim extragerea ultimei valori din cadrul unui bloc de celule putem folosi uneori și funcțiile ROWS() și COLUMNS() din care trebuie să scădem valoarea 1.

În exemplul nostru se poate înlocui =OFFSET(A2;4;4) cu:

=OFFSET(A2;ROWS($A$2:$E$6)-1;COLUMNS($A$2:$E$6)-1)

În care ROWS($A$2:$E$6) și COLUMNS($A$2:$E$6) vor returna valoarea 5.

Parametrii height și width sunt opționali și presupun definirea unui bloc dinamic de căsuțe, perimetru, pentru care dorim să efectuăm operațiuni sau din care să extragem date.

De exemplu, dacă dorim să însumăm datele din primele 4 celule ale tabelului nostru de date putem folosi formula:

=SUM(OFFSET(A2;0;0;2;2)) rezultatul acesteia fiind 16 = 1+2+6+7.

Dacă folosim formula:

=SUM(OFFSET(A2;1;1;2;2)) vom obține rezultatul 40 pentru că se face referire la blocul de celule B3:C4.

Pentru extragerea de date cu utilizarea parametrilor opționali este obligatoriu să scriem OFFSET() în mod array.

De exemplu, dacă dorim să extragem într-o zonă distinctă valorile dintr-un tabel sub forma altui tabel de alt perimetru, va trebui să:

1) Selectăm blocul de celule de destinație, în cazul nostru dorim să extragem un bloc de valori de 3 coloane și 2 linii începând cu valoarea din celula A2:

2) Tastăm formula: =OFFSET(A2;0;0;2;3)

În care: 0;0; reprezintă poziția de start față de A2, 2 numărul de linii și 3 numărul de coloane.

3) Apăsăm combinația de taste CTRL+SHIFT+ENTER.

Dacă vom selecta o singură celulă sau un bloc de celule mai mic decât perimetrul 2×3 vom obține valori corespondente doar pentru blocul selectat. Dacă selectăm un număr mai mare de căsuțe decât blocul destinație vom obține în celulele din afara perimetrului 2×3 eroarea #N/A.

Cunoscând astfel structura parametrilor obligatorii putem specifica în mod dinamic numărul liniei și a coloanei de la intersecția cărora dorim să extragem o valoare.

În articolul Excel IF() – Insane Level utilizatorul Radu G. propusese o formulă de rezolvare matricială a problemei de calcul a adaosului comercial pentru două intervale de valori, prin identificarea valorii procentului prin combinare cu două formule MATCH().

Un exemplu mai simplu față de cel prezentat: dacă din tabelul cu tabla înmulțirii dorim să extragem valoarea calculată de la intersecția liniilor cu coloanelor, folosind o zonă distinctă de introducere a datelor, va trebui să folosim două formule MATCH() pentru parametrii rows și cols.

Pentru a extrage în mod dinamic toate valorile de pe o linie pe baza unei valori date, va trebui să folosim din nou tehnica array: selectăm 10 celule în zona de extragere, scriem formula cu MATCH() pentru parametrul rows, și apăsăm combinația CTRL+SHIFT+ENTER.

În care:

– MATCH(O7;A3:A12;0) va returna numărul liniei pe care se află numărul introdus în celula O7,

– Prima valoare 1, reprezintă numărul coloanei de la care să înceapă extragerea în raport cu referința A2,

– A doua valoare 1 reprezintă înălțimea blocului de extragere, în cazul nostru dorim valori doar de pe o singură linie,

– 10 reprezintă numărul ce cifre/celule rezultat.

Aplicația 1 – Determinarea numărului de ore alocate activităților dintr-o etapă

Foarte multe aplicații economice în Excel conțin date denormalizate, dar care au o logică concretă pentru autorul datelor.

Una din aplicațiile comune mai multor domenii de activitate este aceea de înregistrare a efortului pentru diferite etape de activitate.

Zona de activități este denumită ca un tabel cu numele tblEtape. Nu știm câte activități pot fi într-o etapă și în același timp aplicația permite adăugarea de etape noi în lista de activități.

Raportul de ore pe etape presupune să însumăm orele de pe coloana Număr ore, specific fiecărei etape.

Soluții alternative

Probabil principala metodă de calcul ar fi aceea de a introduce o nouă linie în tabel sub fiecare etapa. Acest lucru presupune alterarea structurii normale ale tabelului și în același timp nu este o operațiune care poate fi replicată și pentru alte fișiere cu aceeași structură/conținut, la fiecare dintre ele fiind nevoie de introducerea unei linii noi.

Utilizarea unui tabel pivot nu este posibilă din cauză că avem linii care nu conțin valori la Etapa.

Specific pentru numele activităților din tabelul de exemplu s-ar putea forța o soluție cu SUMIF() cu criteriul de căutare cu wildcard. Pentru etapa de analiză formula ar fi: =SUMIF(tblEtape[Activitate];”Activitate A*”;tblEtape[Număr ore])

Acestă funcție funcționează doar în acest caz particular și se bazează pe faptul că toate numele de activități din Analiză încep cu șirul “Activitate A”, simbolul asterix (*) fiind utilizat pentru tot ce este scris după acest șir.

Soluția propusă pentru raportul de ore

Constrângerea aplicației este ca etapele trecute în raport să fie în aceeași ordine ca cele din tabelul de activități.

Provocarea este de a identifica numărul de linii de activități pentru fiecare etapă. Ele pot varia și în acest context nu avem cum să specificăm concret înălțimea blocului de cellule de pe care se realizează suma. De asemenea, trebuie să identificăm exact dacă o etapă este ultima sau nu, formula de calcul fiind diferită pentru etapele intermediare față de ultima etapă din raport.

Folosind tehnica coloanelor intermediare vom calcula numărul de linii dintre două etape, iar dacă etapa este ultima în listă vom calcula numărul de linii totale ale tabelului de activități:

Pe coloana Match din tabelul de rezultat avem formula: =MATCH(F3;tblEtape[Etapa];0) care va determina poziția numelui etapei în lista de Etape. Funcția de pe coloana Câte linii? Începe cu IFNA() care verifică faptul dacă etapa specificată în raport este ultima în listă sau nu. Dacă rezultatul funcției MATCH() este #N/A se calculează numărul de linii prin numărarea liniilor totale din table minus poziția ultimei etape din raport în tabelul de activități.

Cumulând totul într-o singură celulă vom obține suma totală a orelor pentru fiecare etapă.

Formula pentru celula G3 devine astfel:

=SUM(OFFSET($A$2;MATCH(F3;tblEtape[Etapa];0);2;IFNA(MATCH(F4;tblEtape[Etapa];0);ROWS(tblEtape[Activitate])+1)-MATCH(F3;tblEtape[Etapa];0);1))

OFFSET-ul determină un număr de celule relative la numele etapei specificată în raport.

Tehnica de a obține automat numele etapelor în raport nu este posibilă din cauza faptului că nu sunt completate numele etapelor pe fiecare linie (tabel denormalizat).

Notă: Pentru ușurința realizării rapoartelor recomandăm întotdeauna completarea tuturor celulelor din carul unui tabel și reducerea cât mai mare a numărului de celule merged.

Pentru a testa și/sau completa modelul puteți să descărcați fișierul de aici.

Aplicația 2 – Identificarea unui număr pe două intervale de valori cu condiție suplimentară

Problemele de determinare a anumitor valori specifice pe baza combinării unor de intervale date de valori, reprezintă un model de aplicații economice cu utilizare foarte largă. Majoritatea utilizatorilor de Excel folosesc funcția IF() pentru determinarea acestor valori. Din punct de vedere tehnic apar o serie de neajunsuri legate de dimensiunea intervalelor, plus probleme de complexitate în contextul introducerii altor constrângeri.

În exemplul de mai jos avem un tabel de discount-uri determinat pe stocul existent de produse precum și pe cantitatea solicitată prin comandă. Valorile sunt defalcate pe fiecare categorie de produs. Valorile de discount sunt pur orientative dar au în vedere că pentru stocuri mai mari acordăm discount-uri mai mari. De asemenea, pentru cantități comandate mai mari avem discount-uri crescute.

În exemplul nostru, la un stoc de până la 500 de bucăți din categoria altele, acordăm un discount de 19 pentru cantități între 10-15 produse comandate.

Singura soluție alternativă pe care o identific este aceea de a realiza IF-uri pe intervale pe categorii de produse. Optim în acest model de rezolvare este să pornim de la tipul categoriei de produs: Dulciuri sau altele, după care să replicăm IF-urile pe celelalte două intervale (Stoc și Cantitate).

Celulele B9:B11 sunt celule de input pentru valori: Stocul existent, cantitatea comandată și categoria de produs.

Celula D10 conține valoarea indexată din tabel determinată prin aplicarea unei funcții OFFSET.

Provocarea este de identifica exact poziția de căutare din tabel, iar pentru a explica modul de formare a formulei finale folosim 3 funcții MATCH() intermediare:

Formulele pentru intermediar sunt:

G9: =MATCH($B$9; $A$4:$A$7; 1) – determină numărul liniei din tabelul de date în care se încadrează stocul. Valoarea 101 va oferi rezultatul 1 pentru că este echivalent linei 1 din tabel, parametrul 1 din MATCH() semnificând valoare mai mică decât valoarea de pe linie.

G10: =MATCH($B$11;$B$2:$I$2;0) – determină poziția categoriei în tabelul de date. Chiar dacă celulele sunt merged, valoarea rezultat va fi 1 pentru Dulciuri și 5 pentru Altele. Această valoare ne ajută să specificăm OFFSET-ului care este numărul coloanei din tabelul cu date de la care se va executa extragerea discount-ului.

G11: =MATCH(B10;$B$3:$E$3;1) – determină coloana din tabelul cu date de la care se va face extragerea prin combinare cu valoarea determinată de valoarea categoriei.

Simplificat, funcția specifică din celula D10 va fi: =OFFSET(A3;G9;G10+G11-1)

În mod combinat fără, coloane intermediare, funcția din D10 este:

=OFFSET(A3;MATCH($B$9; $A$4:$A$7; 1);MATCH($B$11;$B$2:$I$2;0)+MATCH(B10;$B$3:$E$3;1) -1)

Pentru a testa și/sau completa modelul puteți să descărcați fișierul de aici.

Sper să vă fie util!

Referințe suplimentare:

08/05/2017