Cuprins
Când menționăm baze de date (DB), primul lucru care îmi vine în minte, desigur, sunt tot felul de cuvinte la modă precum SQL, Oracle, 1C sau cel puțin Access. Desigur, acestea sunt programe foarte puternice (și scumpe în cea mai mare parte) care pot automatiza munca unei companii mari și complexe cu o mulțime de date. Problema este că uneori o astfel de putere pur și simplu nu este necesară. Afacerea dvs. poate fi mică și cu procese de afaceri relativ simple, dar doriți și să o automatizați. Și pentru companiile mici aceasta este adesea o chestiune de supraviețuire.
Pentru început, să formulăm TOR. În cele mai multe cazuri, o bază de date pentru contabilitate, de exemplu, vânzările clasice ar trebui să poată:
- păstra în tabele informații despre mărfuri (preț), tranzacții finalizate și clienți și legați aceste tabele între ele
- au confortabil formulare de intrare date (cu liste derulante etc.)
- completați automat unele date formulare tipărite (plăți, facturi etc.)
- emite necesarul Rapoarte să controleze întregul proces de afaceri din punctul de vedere al managerului
Microsoft Excel poate gestiona toate acestea cu puțin efort. Să încercăm să punem în aplicare acest lucru.
Pasul 1. Datele inițiale sub formă de tabele
Vom stoca informații despre produse, vânzări și clienți în trei tabele (pe aceeași foaie sau pe altele diferite – nu contează). Este esențial important să le transformi în „mese inteligente” cu dimensiune automată, pentru a nu te gândi la asta în viitor. Acest lucru se face cu comanda Formatați ca tabel fila Acasă (Acasă — Format ca tabel). Pe fila care apare apoi Constructor (Proiecta) dați tabelelor nume descriptive în câmp Numele tabelului pentru utilizare ulterioară:
În total, ar trebui să obținem trei „mese inteligente”:
Vă rugăm să rețineți că tabelele pot conține date suplimentare clarificatoare. Deci, de exemplu, al nostru Prețconține informații suplimentare despre categoria (grup de produse, ambalaj, greutate etc.) a fiecărui produs și tabel Client — orașul și regiunea (adresa, TIN, detalii bancare etc.) ale fiecăruia dintre ele.
Tabel Vânzări va fi folosit de noi ulterior pentru a introduce tranzacții finalizate în acesta.
Pasul 2. Creați un formular de introducere a datelor
Desigur, puteți introduce datele vânzărilor direct în tabelul verde Vânzări, dar acest lucru nu este întotdeauna convenabil și implică apariția erorilor și greșelilor de tipar din cauza „factorului uman”. Prin urmare, ar fi mai bine să faceți un formular special pentru introducerea datelor pe o foaie separată de ceva de genul acesta:
În celula B3, pentru a obține data-ora actuală, utilizați funcția TDATA (ACUM). Dacă nu este nevoie de timp, atunci în schimb TDATA funcția poate fi aplicată AZI (ASTĂZI).
În celula B11, găsiți prețul produsului selectat în a treia coloană a tabelului inteligent Preț folosind funcția VPR (CĂUTARE V). Dacă nu ați mai întâlnit-o înainte, atunci mai întâi citiți și urmăriți videoclipul aici.
În celula B7, avem nevoie de o listă derulantă cu produse din lista de prețuri. Pentru aceasta puteți folosi comanda Date – Validarea datelor (Data validarii), specificați ca constrângere Listă (Listă) si apoi intra in camp Sursă (Sursă) link către coloană Nume si Prenume de la masa noastră inteligentă Preț:
În mod similar, este creată o listă derulantă cu clienți, dar sursa va fi mai restrânsă:
=INDIRECT(„Clienți[Client]”)
Funcţie INDIRECT (INDIRECT) este necesar, în acest caz, deoarece Excel, din păcate, nu înțelege legăturile directe către tabelele inteligente din câmpul Sursă. Dar aceeași legătură „înfășurată” într-o funcție INDIRECT în același timp, funcționează cu un bang (mai multe despre asta găsiți în articolul despre crearea listelor drop-down cu conținut).
Pasul 3. Adăugarea unei macrocomenzi de intrare de vânzări
După completarea formularului, trebuie să adăugați datele introduse în el la sfârșitul tabelului Vânzări. Folosind link-uri simple, vom forma o linie care va fi adăugată chiar sub formular:
Acestea. celula A20 va avea o legătură către =B3, celula B20 va avea o legătură către =B7 și așa mai departe.
Acum să adăugăm o macrocomandă elementară de 2 linii care copiază șirul generat și îl adaugă la tabelul Vânzări. Pentru a face acest lucru, apăsați combinația Alt + F11 sau butonul Visual Basic fila dezvoltator (Dezvoltator). Dacă această filă nu este vizibilă, activați-o mai întâi în setări Fișier – Opțiuni – Configurare panglică (Fișier — Opțiuni — Personalizare panglică). În fereastra editorului Visual Basic care se deschide, inserați un nou modul gol prin meniu Inserare – Modul și introduceți codul nostru macro acolo:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy the data line from the form n = Worksheets("Sales").Range("A100000").End(xlUp) . Rând „determină numărul ultimului rând din tabel. Fișe de lucru de vânzări ("Vânzări"). Celulele (n + 1, 1). PasteSpecial Paste:=xlPasteValues "inserați în următoarea linie goală Foi de lucru ("Formular de introducere"). Interval ("B5, B7, B9"). ClearContents „Șterge sub formă de sfârșit Acum putem adăuga un buton în formularul nostru pentru a rula macrocomanda creată folosind lista drop-down Insera fila dezvoltator (Dezvoltator - Inserare - Buton):
După ce îl desenați, ținând apăsat butonul stâng al mouse-ului, Excel vă va întreba ce macrocomandă trebuie să îi atribuiți - selectați macrocomandă Add_Sell. Puteți modifica textul unui buton făcând clic dreapta pe el și selectând comanda Schimbați textul.
Acum, după completarea formularului, puteți pur și simplu să faceți clic pe butonul nostru, iar datele introduse vor fi adăugate automat în tabel Vânzări, iar apoi formularul este șters pentru a introduce o nouă afacere.
Pasul 4 Conectarea tabelelor
Înainte de a crea raportul, să legăm tabelele noastre, astfel încât ulterior să putem calcula rapid vânzările în funcție de regiune, client sau categorie. În versiunile mai vechi de Excel, acest lucru ar necesita utilizarea mai multor funcții. VPR (CĂUTARE V) pentru înlocuirea prețurilor, categoriilor, clienților, orașelor etc. la tabel Vânzări. Acest lucru necesită timp și efort din partea noastră și, de asemenea, „mâncă” o mulțime de resurse Excel. Începând cu Excel 2013, totul poate fi implementat mult mai simplu prin stabilirea de relații între tabele.
Pentru a face acest lucru, pe fila Date (Data) clic relație (Relaţii). În fereastra care apare, faceți clic pe butonul Crea (nou) și selectați din listele derulante tabelele și numele coloanelor cu care ar trebui să fie legate:
Un punct important: tabelele trebuie specificate în această ordine, adică tabelul legat (Preț) nu trebuie să conțină în coloana cheie (Nume si Prenume) produse duplicate, așa cum se întâmplă în tabel Vânzări. Cu alte cuvinte, tabelul asociat trebuie să fie unul în care ați căuta date folosind VPRdacă ar fi folosit.
Desigur, masa este conectată într-un mod similar Vânzări cu masa Client prin coloană comună Client:
După configurarea legăturilor, fereastra de gestionare a legăturilor poate fi închisă; nu trebuie să repetați această procedură.
Pasul 5. Construim rapoarte folosind rezumatul
Acum, pentru a analiza vânzările și a urmări dinamica procesului, să creăm, de exemplu, un fel de raport folosind un tabel pivot. Setați celula activă la tabel Vânzări și selectați fila de pe panglică Insert – PivotTable (Inserați — Tabel Pivot). În fereastra care se deschide, Excel ne va întreba despre sursa de date (adică tabelul Vânzări) și un loc pentru a încărca raportul (de preferință pe o foaie nouă):
Punctul vital este că este necesar să activați caseta de selectare Adăugați aceste date la modelul de date (Adăugați date la modelul de date) în partea de jos a ferestrei, astfel încât Excel să înțeleagă că dorim să construim un raport nu numai pe tabelul curent, ci și să folosim toate relațiile.
După ce faceți clic pe OK va apărea un panou în jumătatea dreaptă a ferestrei Câmpuri din tabel pivotunde să faceți clic pe link TOATEpentru a vedea nu numai cea actuală, ci toate „mesele inteligente” care sunt în carte deodată. Și apoi, ca în tabelul pivot clasic, puteți pur și simplu să trageți câmpurile de care avem nevoie din orice tabel asociat în zonă Filtru, Rânduri, Stolbtsov or Valori – și Excel va construi instantaneu orice raport de care avem nevoie pe foaie:
Nu uitați că tabelul pivot trebuie actualizat periodic (când se modifică datele sursă) făcând clic dreapta pe el și selectând comanda Actualizați și salvați (Reîmprospăta), deoarece nu o poate face automat.
De asemenea, selectând orice celulă din rezumat și apăsând butonul Diagrama pivot (Diagrama pivot) fila Analiză (Analiză) or parametrii (Opțiuni) puteți vizualiza rapid rezultatele calculate în ea.
Pasul 6. Completați imprimabilele
O altă sarcină tipică a oricărei baze de date este completarea automată a diferitelor formulare și formulare tipărite (facturi, facturi, acte etc.). Am scris deja despre una dintre modalitățile de a face asta. Aici implementăm, de exemplu, completarea formularului după numărul de cont:
Se presupune că în celula C2 utilizatorul va introduce un număr (numărul rândului din tabel Vânzări, de fapt), iar apoi datele de care avem nevoie sunt extrase folosind funcția deja familiară VPR (CĂUTARE V) și caracteristici INDEX (INDEX).
- Cum să utilizați funcția VLOOKUP pentru a căuta și a căuta valori
- Cum să înlocuiți CĂUTARE V cu funcțiile INDEX și MATCH
- Completarea automată a formularelor și formularelor cu date din tabel
- Crearea de rapoarte cu PivotTables