contingut
Quan parlem de bases de dades (DB), el primer que em ve al cap, és clar, són tota mena de paraules de moda com SQL, Oracle, 1C o almenys Access. Per descomptat, es tracta de programes molt potents (i cars en la seva majoria) que poden automatitzar el treball d'una empresa gran i complexa amb moltes dades. El problema és que de vegades aquest poder simplement no és necessari. El vostre negoci pot ser petit i amb processos empresarials relativament senzills, però també voleu automatitzar-lo. I és per a les petites empreses que sovint això és una qüestió de supervivència.
Per començar, formulem el TOR. En la majoria dels casos, una base de dades per a la comptabilitat, per exemple, les vendes clàssiques hauria de ser capaç de:
- mantenir a les taules informació sobre mercaderies (preu), transaccions realitzades i clients i enllaçar aquestes taules entre si
- tenir còmode formularis d'entrada dades (amb llistes desplegables, etc.)
- emplenar automàticament algunes dades formularis impresos (pagaments, factures, etc.)
- expedir el necessari informes per controlar tot el procés de negoci des del punt de vista del gerent
Microsoft Excel pot gestionar tot això amb una mica d'esforç. Intentem implementar això.
Pas 1. Dades inicials en forma de taules
Emmagatzemarem la informació sobre productes, vendes i clients en tres taules (en el mateix full o en diferents, no importa). És fonamental convertir-les en "taules intel·ligents" amb mida automàtica, per no pensar-hi en el futur. Això es fa amb l'ordre Format com a taula llengüeta Home (Inici — Format com a taula). A la pestanya que després apareix constructor (Disseny) donar noms descriptius a les taules al camp Nom de la taula per a un ús posterior:
En total, hauríem d'obtenir tres "taules intel·ligents":
Tingueu en compte que les taules poden contenir dades aclaridores addicionals. Així, per exemple, el nostre preuconté informació addicional sobre la categoria (grup de productes, embalatge, pes, etc.) de cada producte i la taula client — ciutat i regió (adreça, NIF, dades bancàries, etc.) de cadascun d'ells.
Taula Sales utilitzarem més endavant per introduir-hi transaccions completades.
Pas 2. Creeu un formulari d'entrada de dades
Per descomptat, podeu introduir les dades de vendes directament a la taula verda Sales, però això no sempre és convenient i comporta l'aparició d'errors i errors tipogràfics a causa del “factor humà”. Per tant, seria millor fer un formulari especial per introduir dades en un full separat d'alguna cosa com aquesta:
A la cel·la B3, per obtenir la data i hora actual actualitzada, utilitzeu la funció El TDATA (ARA). Si no es necessita temps, en canvi El TDATA es pot aplicar la funció AVUI (AVUI).
A la cel·la B11, cerqueu el preu del producte seleccionat a la tercera columna de la taula intel·ligent preu utilitzant la funció VPR (VISUALITZACIÓ). Si no l'heu trobat abans, primer llegiu i mireu el vídeo aquí.
A la cel·la B7, necessitem una llista desplegable amb productes de la llista de preus. Per a això podeu utilitzar l'ordre Dades – Validació de dades (Dades — Validació), especifiqueu com a restricció llista (Llista) i després entra al camp font (Font) enllaç a la columna Nom de la nostra taula intel·ligent preu:
De la mateixa manera, es crea una llista desplegable amb clients, però la font serà més estreta:
=INDIRECT("Clients[Client]")
function INDIRECTE (INDIRECTE) cal, en aquest cas, perquè Excel, malauradament, no entén els enllaços directes a taules intel·ligents al camp Font. Però el mateix enllaç "embolicat" en una funció INDIRECTE al mateix temps, funciona amb una explosió (més informació sobre això es troba a l'article sobre la creació de llistes desplegables amb contingut).
Pas 3. Afegir una macro d'entrada de vendes
Després d'omplir el formulari, cal afegir les dades introduïdes al final de la taula Sales. Mitjançant enllaços senzills, formarem una línia que s'afegirà just a sota del formulari:
Aquells. la cel·la A20 tindrà un enllaç a =B3, la cel·la B20 tindrà un enllaç a =B7, i així successivament.
Ara afegim una macro elemental de 2 línies que copia la cadena generada i l'afegeix a la taula de vendes. Per fer-ho, premeu la combinació Alt + F11 o botó Visual Basic llengüeta revelador (Desenvolupador). Si aquesta pestanya no és visible, activeu-la primer a la configuració Fitxer – Opcions – Configuració de la cinta (Fitxer — Opcions — Personalitza la cinta). A la finestra de l'editor de Visual Basic que s'obre, inseriu un nou mòdul buit a través del menú Inserir – Mòdul i introduïu el nostre codi macro allà:
Sub Add_Sell() Worksheets("Formulari d'entrada").Range("A20:E20").Copy'Copy the data line from the form n = Worksheets("Sales").Range("A100000").End(xlUp) . Fila 'determineu el número de l'última fila de la taula. Fulls de treball de vendes("Vendes").Cèl·lules(n + 1, 1).PasteSpecial Paste:=xlPasteValues enganxa a la següent línia buida Fulls de treball ("Formulari d'entrada").Range("B5,B7,B9"). ClearContents 'esborra el subformulari final Ara podem afegir un botó al nostre formulari per executar la macro creada mitjançant la llista desplegable Insereix llengüeta revelador (Desenvolupador - Insereix - Botó):
Després de dibuixar-lo, mantenint premut el botó esquerre del ratolí, Excel us demanarà quina macro li heu d'assignar: seleccioneu la nostra macro Afegeix_Ven. Podeu canviar el text d'un botó fent-hi clic amb el botó dret i seleccionant l'ordre Canvia el text.
Ara, després d'omplir el formulari, només podeu fer clic al nostre botó, i les dades introduïdes s'afegiran automàticament a la taula Sales, i després s'esborra el formulari per entrar en un nou acord.
Pas 4 Enllaç de taules
Abans de crear l'informe, enllacem les nostres taules perquè més endavant puguem calcular ràpidament les vendes per regió, client o categoria. En versions anteriors d'Excel, això requeriria l'ús de diverses funcions. VPR (VISUALITZACIÓ) per substituir preus, categories, clients, ciutats, etc. a la taula Sales. Això ens requereix temps i esforç, i també "menja" molts recursos d'Excel. A partir d'Excel 2013, tot es pot implementar molt més simplement establint relacions entre taules.
Per fer-ho, a la pestanya dades (Data) clic Relacions (Relacions). A la finestra que apareix, feu clic al botó Create (nou) i seleccioneu de les llistes desplegables les taules i els noms de columnes amb els quals haurien d'estar relacionats:
Un punt important: les taules s'han d'especificar en aquest ordre, és a dir, taula enllaçada (preu) no ha de contenir a la columna de claus (Nom) productes duplicats, tal com passa a la taula Sales. En altres paraules, la taula associada ha de ser aquella en la qual cerqueu dades utilitzant VPRsi fos utilitzat.
Per descomptat, la taula està connectada d'una manera similar Sales amb taula client per columna comuna Client:
Després de configurar els enllaços, es pot tancar la finestra per gestionar els enllaços; no heu de repetir aquest procediment.
Pas 5. Elaborem informes amb el resum
Ara, per analitzar les vendes i fer un seguiment de la dinàmica del procés, creem, per exemple, algun tipus d'informe mitjançant una taula dinàmica. Estableix la cel·la activa a la taula Sales i seleccioneu la pestanya de la cinta Insereix - Taula dinàmica (Insereix — Taula dinàmica). A la finestra que s'obre, Excel ens preguntarà sobre la font de dades (és a dir, la taula Sales) i un lloc per penjar l'informe (preferiblement en un full nou):
El punt vital és que cal activar la casella de selecció Afegiu aquestes dades al model de dades (Afegiu dades al model de dades) a la part inferior de la finestra perquè Excel entengui que volem crear un informe no només a la taula actual, sinó també utilitzar totes les relacions.
Després de fer clic OK apareixerà un plafó a la meitat dreta de la finestra Camps de la taula dinàmicaon fer clic a l'enllaç Allper veure no només l'actual, sinó totes les "taules intel·ligents" que hi ha al llibre alhora. I després, com a la taula dinàmica clàssica, simplement podeu arrossegar els camps que necessitem des de qualsevol taula relacionada cap a l'àrea filtre, Files, Stolbtsov or Valors – i Excel crearà instantàniament qualsevol informe que necessitem al full:
No oblideu que la taula dinàmica s'ha d'actualitzar periòdicament (quan canvien les dades d'origen) fent-hi clic amb el botó dret i seleccionant l'ordre. Actualitza i desa (Actualització), perquè no ho pot fer automàticament.
A més, seleccionant qualsevol cel·la del resum i prement el botó Gràfic de pivots (Gràfic dinàmic) llengüeta Anàlisi (Anàlisi) or Paràmetres (Opcions) podeu visualitzar ràpidament els resultats calculats en ell.
Pas 6. Ompliu els imprimibles
Una altra tasca típica de qualsevol base de dades és l'emplenament automàtic de diversos formularis i formularis impresos (factures, factures, actes, etc.). Ja vaig escriure sobre una de les maneres de fer-ho. Aquí implementem, per exemple, omplir el formulari per número de compte:
Se suposa que a la cel·la C2 l'usuari introduirà un número (número de fila a la taula Sales, de fet), i després les dades que necessitem s'obtenen mitjançant la funció ja coneguda VPR (VISUALITZACIÓ) i funcions ÍNDEX (ÍNDEX).
- Com utilitzar la funció BUSCARV per cercar i cercar valors
- Com substituir VLOOKUP per les funcions INDEX i MATCH
- Ompliment automàtic de formularis i formularis amb dades de la taula
- Creació d'informes amb taules dinàmiques