Excel: Custom consolidation

Consolidarea sau centralizarea reprezintă una din problemele principale ale celor care lucrează cu mai multe foi de calcul din Excel, pe care la anumite perioade de timp trebuie să le centralizeze.

Prezentul articol dorește să prezinte o metodă de rezolvare a acestei probleme.

În mare parte la consolidare trebuie să încercăm să ne încadrăm în anumite restricții, mai ales în contextul în care lucrăm cu fișiere multiple:

Structura fișierelor să fie identică: același tip și număr de coloane; liniile să aibă coduri din același interval;
Respectarea convențiilor de nume: Ex: numele fișierelor de genul numeagent-luna.xlsx; numele foilor de calcul sau a tabelelor de date etc.

De cele mai multe ori teoria este atât de simplă pentru că poți să predai ce vrei, cum vrei, sau cum ai învățat acasă. În practică, practic cam câți oameni sunt care lucrează, cam atâtea metode sunt.

Mai jos avem un model de fișier de vânzări de la 3 agenți pe o perioadă de 5 zile. Datele despre vânzări sunt în cifre să presupunem.

https://r.office.microsoft.com/r/rlidExcelEmbed?su=7981371974048657041&Fi=SD6EC38785F56DAE91!2212&ak=t%3d0%26s%3d0%26v%3d!AAAU-nfKVpuMcbw&kip=1

Descărcare fișier Excel

Fiecare agent vinde o anumită gamă de sortimente iar la finalul săptămânii folosind o metodă sau alta un operator încearcă să facă centralizarea datelor.

În mare parte operațiunile manuale care nu au nici o fel de productivitate și care sunt sortite cel mai adesea greșelilor de operare, le-am numit eu generic: MDT (Metoda De la Țară).

În foaia MDT1

Datele din această foaie de calcul sunt preluate cu copy/paste din 3 foi de calcul cu date despre vânzările agenților.

După care am realizat sortarea după cod produs, și am realizat sub-totaluri cu sumă pentru fiecare zi, grupate după coduri.

Dezavantaje:

datele nu sunt dinamice
dacă sunt prea multe fișiere de centralizat operațiunile de deschidere închidere sunt consumatoare de timp.
nu mai cunoaștem sursa originală a datelor
riscuri de operare.

În foaia MDT2

Datele din această foaie de calcul sunt preluate cu copy/paste special din 3 foi de calcul cu date despre vânzările agenților.

Operațiunea este simplă dar dacă ordinea codurilor sau numărul de linii este diferit se pierde consistența datelor.

Dezavantaje:

datele nu sunt dinamice
dacă sunt prea multe fișiere de centralizat operațiunile de deschidere închidere sunt consumatoare de timp.
nu mai cunoaștem sursa originală a datelor
riscuri de operare
necesitatea respectării numărului de linii și consistență a datelor

În foaia MO

Datele din această foaie de calcul sunt preluate cu Data, Consolidate din 3 foi de calcul cu date despre vânzările agenților.

Avantaj:

– rapiditatea cu care sunt centralizate datele

Dezavantaje:

Nu este dinamic
Când zonele de date sunt formatate ca tabel, nu putem folosi opțiunea Top Row!
De fiecare data când se schimbă structura unui tabel (numărul de coloane sau linii), trebuie să refacem consolidarea!

Funcționează și cu date din fișiere diferite.

În foaia MP

Datele din această foaie de calcul sunt preluate cu VLOOKUP din 3 foi de calcul cu date despre vânzările agenților și se realizează însumarea datelor.

În același timp este realizată tratarea erorilor de tip #N/A folosind funcția ISNA()

Dezavantaje:

complexitatea formulei
modul greoi de extensibilitate a formulei

Avantaje:

complet dinamic
nu are importanță numărul de linii dintr-o parte sau alta
funcționează foarte bine din tabele de date

Funcționează și cu date din fișiere diferite.

Formula de calcul pentru primul cod de produs:

=IF(ISNA(VLOOKUP($A3;AgentA;B$1;FALSE))=TRUE;0;VLOOKUP($A3;AgentA;B$1;FALSE))+IF(ISNA(VLOOKUP($A3;AgentB;B$1;FALSE))=TRUE;0;VLOOKUP($A3;AgentB;B$1;FALSE))+IF(ISNA(VLOOKUP($A3;AgentC;B$1;FALSE))=TRUE;0;VLOOKUP($A3;AgentC;B$1;FALSE))

Sper să vă fie util.

PS. Dacă mai aveți idei de îmbunătățire sau tot felul de probleme ciudate din practică vă rog să le pasați aici cu încredere pentru că le dau la studenți să le rezolve/optimizeze.

25/04/2012

ACTA and anti-ACTA

De când s-a dat publicităţii chestia asta cu ACTA şi anti-ACTA am stat şi tot m-am gândit cum să mă poziţionez faţă de aceast curent.

Anti: Nu aş vrea să dispară site-urile de socializare şi nici YouTube sau altele.

Pro: Pe de altă parte autorii materialelor digitale trebuie să-şi primească bani pentru munca lor.

Anti: Nu vreau să fac puşcărie pentru un amărât de MP3 de pe calculator.

Pro: Anumite efecte negative ale libertăţii pe Internet ar trebui corectate prin contramăsuri.

Având în vedere că sunt un cosumator al libertăţii Internet, dar în acelaşi timp mă intersectez cu zona securităţii sistemelor informaţionale, în acelaşi timp aflat în chinurile facerii primei cărţi de computer forensic din România, am decis să mă poziţionez neutru faţă de acest subiect.

Totuşi…

Steganografia

Dacă ar fi să încalc regulile, primul lucru la care m-aş gândi este steganografia, tehnică veche de ascundere a informaţiilor. Dar fără a fi prea complicat pentru că apoi ar deveni greu să o facem reversibilă.

Apoi, dacă aş fi dincolo de baricadă, în calitate de apărător ACTA şi vine un civil cu laptopul la aeroport, care are toate manelele de pe disc cu extensia PDF în loc de MP3 şi aplicat şi un hidden pe toate, prin ce metodă va descoperi acele fişiere domnul poliţist? Face search pe disc? După extensie nu poate. Ar trebui să aibă o aplicaţie de căutare bazată pe headerul fişierului şi nu pe extensia acestuia. Bun, nu-i greu de găsit, nici scumpă o astfel de aplicaţie … nu vrem să fim răi să vorbim că la noi tre să faci un POSDRU ca să cumperi o aplicaţie gratis : )… dar problema se pune puţin altfel: dacă vin 100 de oameni pe oră la aeroport, fiecare cu câte un HDD de câţiva zeci de Gb pe care trebuie să-i scanezi? Să vină omu cu 7 ore mai devreme? Să-şi lase laptopul acasă? Ciudat de multe întrebări.

Şi cum din orice se poate face un business sau o asociaţie… nu se putea să nu existe şi o asociaţie orintată către detectarea steganografiei aşa că mă mai gândesc cum aş putea ascunde cât mai bine fişierele.

Nu se pune problema să le punem pe dropbox sau skydrive sau în contul de mail… pentru că şi alea intră sub incidenţa ACTA. Totul e să-ţi ei maneaua cu tine pe laptop când te duci în afară… de ţară.

VHD-urile

Dacă aş vrea să le fac viaţa puţin mai grea apărătorilor ACTA atunci aş utiliza VHD-uri în locul altor tipuri de fişiere, şi pe aceste VHD-uri aş pune conţinutul inciminatoriu, le-aş cripta puţin cu Bitlocker şi apoi …

Hai să vedem în câteva minute cum s-ar face asta.

Pas cu pas ascunderea unui MP3 intr-un VHD şi apoi încorporarea lui într-un document Word.

Dacă aş fi eu aplicaţia care ar trebui să scaneze HDD-ul călătorilor prin ţările ACTA, atunci clar că m-aş uita şi după astfel de fişiere… asa că un file puzzeler ar fi ideal pentru aşa ceva. Acest file puzzeler ar împărţi un fişier mai mare în fişiere mai mici de diferite tipuri şi în diferite locuri pe disc pentru a nu putea fi recompus fără algoritmul de împărţire a sa. Provoc programatorii la a scrie o astfel de aplicaţie.

În final… treaba nu se opreşte aici. Vrem nu vrem trebuie să plătim pentru ceea ce consumăm… iar dacă se întâmplă excepţii… eu cred că multe din acestea vor fi trecute cu vederea… vânaţi fiind profesioniştii domeniului nu cetăţeanul simplu care are şi el o manea de dor şi jale mare pe laptop.

Dacă aveţi metode mai bune de a ascunde conţinut digital… partajaţile cu noi. Încă se poate!

25/02/2012

Subiect Proba practică Rețele Locale de Calculatoare

Model de subiect, proba practică la disciplina Reţele Locale de Calculatoare, Informatică Economică, Anul 3.

Se poate susţine în Windows sau orice variantă de Linux, Suse, Unix. Studenţilor li se pun la dispoziţie maşini virtuale cu Windows. Pentru cei care doresc să susţină în Linux, Suse sau Unix trebuie să vină cu maşinile virtuale neconfigurate de acasă.

Studenții pot alege între această variantă și varianta cu teste grile.

Nota 1

Oficiu

Verificare: Prezența în sală

Nota 2

Crearea contextului de rețea. Stabilirea numelui serverelor și a configurației TCP IP inițiale conform configurației din schema următoare:

xp-sta-01, IP: 172.17.13.11, SM: 255.255.255.224

Win3k-srv-01, IP: 172.17.13.1, SM: 255.255.255.224

Win3k-srv-02, IP: 172.17.13.2, SM: 255.255.255.224

Verificare: se va da ping de la client către cele două servere și de la servere către client. Clientul NU trebuie să răspundă la ping.

Nota 3

Instalarea serviciu director pe server Win3K-srv-01. Numele de domeniu trebuie să fie iasi.abc.ro si trebuie să fie domeniu independent.

Verificare: Se deschide utilitarul de administrare

Nota 4

Instalarea serviciu director pe Win3K-srv-02 ca și controler de domeniu secundar pentru domeniul iasi.abc.ro. Trebuie instalat și serviciul de DNS-ul pe al doilea server.

Verificare: Se deschide utilitarul de administrare.

Nota 5

Crearea unui unități organizaționale cu numele AbcOrgUsr și în interiorul acesteia un cont de utilizator cu numele Maria Logout. Contul de utilizator trebuie să aibă menționate detalii legate de adresă, funcție, departament, adresa de e-mail și număr de telefon de contact.

Verificare: Verificare în serviciul director.

Nota 6

Adăugarea stației cl-winxp-01 în domeniul iasi.abc.ro. Și mutarea acesteia în unitatea organizațională AbcOrgComp.

Verificare: Se realizează conectarea cu utilizatorul Maria.logout pe stația client.

Nota 7

Crearea unei politici de securitate pentru utilizatorii din AbcOrgUsr cu numele polAbcOrgUsr care să stabilească următoarele restricții pentru utilizator:

– Utilizatorii nu trebuie să poată accesa discul C: din My Computer sau Windows Explorer

– Utilizatorii să nu poată rula aplicația WordPad

– Utilizatorii să aibă ca Home page în Internet Explorer adresa: http://intranet.iasi.abc.ro

Verificare: Se realizează conectarea pe client cu un utilizator din AbcOrgUsr. Se deschide My Computer, WordPad și Internet Explorer.

Nota 8

În DNS creați două noi alias-uri pentru Win3K-srv-02 cu numele intranet și www. Aceleași alias-uri vor fi create și pentru Win3K-srv-01.

Verificare: Se va da ping cu parametrul –t de pe stația client către numele de alias și se vor întrerupe pe rând comunicația celor două servere.

Nota 9

Instalați un server de Web pe Win3K-srv-02 și creați două site-uri cu numele intranet și www. Site-ul de intranet nu trebuie să suporte acces anonim.

Verificare: Se vor apela cele două site-uri de pe stația client.

Nota 10 – Subiect la alegere

S1. Creați un nou utilizator în AbcOrgUsr. Creați o nouă partiție (S: cu eticheta Students) pe Win3K-srv-01 și creați un director cu numele de logon al utilizatorului nou creat. Partajați directorul în rețea cu numele de logon al utilizatorului și configurați permisiunile pentru a avea numai utilizatorul și grupul Domain Admins drepturi depline în directorul partajat. Utilizatorul trebuie să se conecteze automat la logon pe directorul partajat în rețea.

Stabiliți pe discul U o cotă de utilizare de 10 MB pentru toți utilizatorii.

Verificare: Se realizează conectarea pe stația client cu utilizatorul nou creat și se verifică dacă apare discul mapat de pe server. Se verifică dacă dimensiunea discului partajat este de 10Mb.

S2. Asignați utilizatorului Maria.logout un script de logon cu numele logon.bat care să genereze într-un share pe server un fișier CSV care să conțină numele stației de pe care s-a conectat, data și ora.

Verificare: Se realizează conectarea cu utilizatorul Maria.logout și se verifică pe server înregistrarea în fișierul CSV.

Observatii generale:

1. Studenții au voie la examen cu cărți, sau foi fotocopiate din cărți sau documentații de specialitate. Au voie să folosească Help-ul din Windows dar NU au voie să folosească notițe personale, pagini Web, dispozitive de stocare externă sau telefoane mobile în timpul examenului.

2. Subiectele se execută obligatoriu în ordinea notelor.

3. Timp de lucru: 60 minute de la logarea pe staţia reală.

Succes!

09/01/2012