Съдържание
Когато споменаваме бази данни (DB), първото нещо, което идва на ум, разбира се, са всякакви модни думи като SQL, Oracle, 1C или поне Access. Разбира се, това са много мощни (и скъпи в по-голямата си част) програми, които могат да автоматизират работата на голяма и сложна компания с много данни. Проблемът е, че понякога такава мощност просто не е необходима. Вашият бизнес може да е малък и със сравнително прости бизнес процеси, но вие също искате да го автоматизирате. И именно за малките компании това често е въпрос на оцеляване.
Като начало, нека формулираме ТЗ. В повечето случаи база данни за счетоводство, например класически продажби, трябва да може да:
- запази в таблиците информация за стоки (цена), извършени транзакции и клиенти и свържете тези таблици една с друга
- имайте удобни форми за въвеждане данни (с падащи списъци и др.)
- автоматично попълване на някои данни печатни форми (плащания, сметки и др.)
- издават необходимите доклади да контролира целия бизнес процес от гледна точка на мениджъра
Microsoft Excel може да се справи с всичко това с малко усилия. Нека се опитаме да приложим това.
Стъпка 1. Първоначални данни под формата на таблици
Ще съхраняваме информация за продукти, продажби и клиенти в три таблици (на един лист или на различни – няма значение). Фундаментално е важно да ги превърнете в „умни таблици“ с автоматичен размер, за да не мислите за това в бъдеще. Това става с командата Форматирайте като таблица етикет Начало (Начало — Форматиране като таблица). В раздела, който след това се появява конструктор (Дизайн) дайте на таблиците описателни имена в полето Име на таблица за по-късна употреба:
Общо трябва да получим три „умни таблици“:
Моля, имайте предвид, че таблиците може да съдържат допълнителни поясняващи данни. Така например нашият Ценасъдържа допълнителна информация за категорията (продуктова група, опаковка, грамаж и др.) на всеки продукт и таблицата Удовлетвореност — град и регион (адрес, TIN, банкови данни и др.) на всеки от тях.
Маса Продажби ще се използва от нас по-късно, за да въведем завършени транзакции в него.
Стъпка 2. Създайте формуляр за въвеждане на данни
Разбира се, можете да въведете данни за продажби директно в зелената таблица Продажби, но това не винаги е удобно и води до появата на грешки и печатни грешки поради „човешкия фактор“. Затова би било по-добре да направите специален формуляр за въвеждане на данни на отделен лист от нещо подобно:
В клетка B3, за да получите актуализираната текуща дата-час, използвайте функцията TDATA (СЕГА). Ако не е необходимо време, тогава вместо това TDATA може да се приложи функция ДНЕС (ДНЕС).
В клетка B11 намерете цената на избрания продукт в третата колона на интелигентната таблица Цена използване на функцията ВПР (VLOOKUP). Ако не сте го срещали преди, тогава първо прочетете и гледайте видеото тук.
В клетка B7 се нуждаем от падащ списък с продукти от ценовата листа. За целта можете да използвате командата Данни – Валидиране на данни (Потвърждаване на данни), посочете като ограничение списък (Списък) и след това въведете в полето източник (Източник) връзка към колона Име от нашата умна маса Цена:
По същия начин се създава падащ списък с клиенти, но източникът ще бъде по-тесен:
=INDIRECT(“Клиенти[Клиент]”)
функция НЕПРЯК (НЕПРЯК) е необходимо в този случай, защото Excel, за съжаление, не разбира директни връзки към интелигентни таблици в полето Източник. Но същата връзка е „опакована“ във функция НЕПРЯК в същото време работи с гръм и трясък (повече за това беше в статията за създаване на падащи списъци със съдържание).
Стъпка 3. Добавяне на макрос за въвеждане на продажби
След като попълните формуляра, трябва да добавите въведените в него данни в края на таблицата Продажби. Използвайки прости връзки, ще формираме ред, който да се добави точно под формуляра:
Тези. клетка A20 ще има връзка към =B3, клетка B20 ще има връзка към =B7 и т.н.
Сега нека добавим 2-редов елементарен макрос, който копира генерирания низ и го добавя към таблицата Sales. За да направите това, натиснете комбинацията Alt + F11 или бутон Visual Basic етикет предприемач (Разработчик). Ако този раздел не се вижда, първо го активирайте в настройките Файл – Опции – Настройка на лентата (Файл — Опции — Персонализиране на лентата). В прозореца на редактора на Visual Basic, който се отваря, вмъкнете нов празен модул през менюто Вмъкване – Модул и въведете нашия макро код там:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Копирайте реда с данни от формуляра n = Worksheets("Sales").Range("A100000").End(xlUp) . Ред 'определя номера на последния ред в таблицата. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'поставете в следващия празен ред Worksheets("Input Form").Range("B5,B7,B9"). Подформуляр за изчистване на края на ClearContents Сега можем да добавим бутон към нашата форма, за да изпълним създадения макрос с помощта на падащия списък Поставете етикет предприемач (Програмист — Вмъкване — Бутон):
След като го начертаете, като задържите левия бутон на мишката, Excel ще ви попита кой макрос трябва да му зададете – изберете нашия макрос Добавяне_Продаване. Можете да промените текста на бутон, като щракнете с десния бутон върху него и изберете командата Промяна на текста.
Сега, след като попълните формата, можете просто да кликнете върху нашия бутон и въведените данни ще бъдат автоматично добавени в таблицата Продажби, след което формулярът се изчиства, за да въведете нова сделка.
Стъпка 4 Свързване на таблици
Преди да изградим отчета, нека свържем нашите таблици, така че по-късно да можем бързо да изчислим продажбите по регион, клиент или категория. В по-старите версии на Excel това би изисквало използването на няколко функции. ВПР (VLOOKUP) за заместване на цени, категории, клиенти, градове и др. в таблицата Продажби. Това изисква време и усилия от нас, а също така „изяжда“ много ресурси на Excel. Започвайки с Excel 2013, всичко може да се реализира много по-просто чрез създаване на релации между таблиците.
За да направите това, в раздела Дата (Дата) кликване отношения (Връзки). В прозореца, който се показва, щракнете върху бутона Създаване на (Нова) и изберете от падащите списъци имената на таблиците и колоните, с които трябва да бъдат свързани:
Важен момент: таблиците трябва да бъдат посочени в този ред, т.е. свързана таблица (Цена) не трябва да съдържа в ключовата колона (Име) дублирани продукти, както се случва в таблицата Продажби. С други думи, свързаната таблица трябва да е тази, в която бихте търсили данни, използвайки ВПРако беше използвано.
Разбира се, масата е свързана по подобен начин Продажби с маса Удовлетвореност по обща колона Клиентски:
След настройка на връзките прозорецът за управление на връзки може да бъде затворен; не е необходимо да повтаряте тази процедура.
Стъпка 5. Изграждаме отчети, използвайки резюмето
Сега, за да анализираме продажбите и да проследим динамиката на процеса, нека създадем например някакъв вид отчет с помощта на обобщена таблица. Задаване на активна клетка на таблица Продажби и изберете раздела на лентата Вмъкване – обобщена таблица (Вмъкване — осева таблица). В прозореца, който се отваря, Excel ще ни попита за източника на данни (т.е. таблица Продажби) и място за качване на отчета (за предпочитане на нов лист):
Важното е, че е необходимо да активирате квадратчето за отметка Добавете тези данни към модела на данни (Добавяне на данни към модела на данни) в долната част на прозореца, така че Excel да разбере, че искаме да изградим отчет не само върху текущата таблица, но и да използваме всички релации.
След като кликнете върху OK в дясната половина на прозореца ще се появи панел Полета на обобщена таблицакъде да щракнете върху връзката Всичкиза да видите не само текущата, но и всички „умни таблици“, които са в книгата наведнъж. И след това, както в класическата обобщена таблица, можете просто да плъзнете полетата, от които се нуждаем, от всички свързани таблици в областта филтър, Редове, Столбцов or Ценности – и Excel незабавно ще изгради всеки отчет, от който се нуждаем, върху листа:
Не забравяйте, че обобщената таблица трябва да се актуализира периодично (когато изходните данни се променят), като щракнете с десния бутон върху нея и изберете командата Актуализиране и запазване (Опресняване), защото не може да го направи автоматично.
Също така, като изберете произволна клетка в резюмето и натиснете бутона Осева диаграма (Осева диаграма) етикет Анализ (Анализ) or параметри (Настроики) можете бързо да визуализирате изчислените в него резултати.
Стъпка 6. Попълнете печатните материали
Друга типична задача на всяка база данни е автоматичното попълване на различни печатни форми и формуляри (фактури, фактури, актове и др.). Вече писах за един от начините да направите това. Тук прилагаме, например, попълване на формуляра по номер на сметка:
Предполага се, че в клетка C2 потребителят ще въведе число (номер на ред в таблицата Продажби, всъщност) и след това данните, от които се нуждаем, се изтеглят с помощта на вече познатата функция ВПР (VLOOKUP) и функции INDEX (ИНДЕКС).
- Как да използвате функцията VLOOKUP за търсене и търсене на стойности
- Как да замените VLOOKUP с функции INDEX и MATCH
- Автоматично попълване на форми и форми с данни от таблицата
- Създаване на отчети с обобщени таблици