змест
Пры згадванні баз даных (БД) у першую чаргу ўспамінаюцца, вядома ж, разнастайныя модныя слоўцы накшталт SQL, Oracle, 1C ці хаця б Access. Безумоўна, гэта вельмі магутныя (і ў большасці сваёй дарагія) праграмы, здольныя аўтаматызаваць працу вялікай і складанай кампаніі з вялікай колькасцю дадзеных. Бяда ў тым, што часам такая магутнасць проста не патрэбна. Ваш бізнес можа быць невялікім і з адносна простымі бізнес-працэсамі, але вы таксама хочаце яго аўтаматызаваць. І менавіта для невялікіх кампаній гэта часта пытанне выжывання.
Для пачатку сфармулюем ТЗ. У большасці выпадкаў база дадзеных для ўліку, напрыклад, класічных продажаў павінна мець магчымасць:
- трымаць у табліцах звесткі аб таварах (цане), здзейсненых аперацыях і кліентах і звязаць гэтыя табліцы паміж сабой
- мець зручныя формы ўводу даныя (з выпадальнымі спісамі і г.д.)
- аўтаматычна запоўніць некаторыя дадзеныя друкаваныя формы (плацяжы, рахункі і г.д.)
- выдаць неабходнае справаздачы кантраляваць увесь бізнес-працэс з пункту гледжання кіраўніка
Microsoft Excel справіцца з усім гэтым, прыклаўшы невялікія намаганні. Паспрабуем гэта рэалізаваць.
Крок 1. Зыходныя даныя ў выглядзе табліц
Мы будзем захоўваць інфармацыю аб таварах, продажах і кліентах у трох табліцах (на адным аркушы або на розных – не важна). Прынцыпова важна ператварыць іх у «разумныя табліцы» з аўтапамерам, каб не думаць пра гэта ў будучыні. Гэта робіцца з дапамогай каманды Фармат у выглядзе табліцы таб Галоўная (Галоўная — фармат у табліцу). На ўкладцы, якая з'явіцца Канструктар (Дызайн) дайце табліцам апісальныя назвы ў полі Назва табліцы для наступнага выкарыстання:
Усяго ў нас павінна атрымацца тры «разумных табліцы»:
Звярніце ўвагу, што ў табліцах могуць быць дадатковыя ўдакладняючыя даныя. Так, напрыклад, наш цаназмяшчае дадатковую інфармацыю аб катэгорыі (група тавараў, упакоўка, вага і інш.) кожнага тавару і табл Кліент — горад і рэгіён (адрас, ІНАЎ, банкаўскія рэквізіты і інш.) кожнага з іх.
табліца продажаў будзе выкарыстоўвацца намі пазней для ўводу ў яго завершаных аперацый.
Крок 2. Стварыце форму ўводу даных
Вядома, вы можаце ўвесці дадзеныя аб продажах непасрэдна ў зялёную табліцу продажаў, але гэта не заўсёды зручна і цягне за сабой з'яўленне памылак і памылак друку з-за «чалавечага фактару». Таму адмысловую форму для ўводу дадзеных лепш зрабіць на асобным лісце прыкладна так:
У ячэйцы B3, каб атрымаць абноўленую бягучую дату і час, выкарыстоўвайце функцыю TDATA (ЗАРАЗ). Калі час не патрэбны, то замест яго TDATA функцыя можа быць прыменена СЁННЯ (СЁННЯ).
У ячэйцы B11 знайдзіце цану абранага прадукту ў трэцім слупку разумнай табліцы цана выкарыстоўваючы функцыю ВПР (ВПР). Калі вы раней з гэтым не сутыкаліся, то спачатку прачытайце і паглядзіце відэа тут.
У вочку B7 нам спатрэбіцца выпадальны спіс з прадуктамі з прайс-ліста. Для гэтага вы можаце выкарыстоўваць каманду Дадзеныя – Праверка даных (Дадзеныя — праверка), укажыце ў якасці абмежавання спіс (Спіс) а затым увядзіце ў поле крыніца (Крыніца) спасылка на калонку Імя з нашага разумнага стала цана:
Аналагічна ствараецца выпадальны спіс з кліентамі, але крыніца будзе вузей:
=INDIRECT(“Кліенты[Кліент]”)
функцыя ускосна (УСКАСНЫ) неабходна, у дадзеным выпадку, таму што Excel, на жаль, не разумее прамых спасылак на смарт-табліцы ў поле Source. Але тая ж спасылка «загорнутая» ў функцыю ускосна пры гэтым працуе на ўра (падрабязней пра гэта было ў артыкуле аб стварэнні выпадальных спісаў з кантэнтам).
Крок 3. Даданне макраса запісу продажаў
Пасля запаўнення формы ў канец табліцы неабходна дадаць уведзеныя ў яе даныя продажаў. Выкарыстоўваючы простыя спасылкі, мы сфармуем радок, які будзе дададзены адразу пад формай:
Тыя. ячэйка A20 будзе мець спасылку на =B3, ячэйка B20 будзе мець спасылку на =B7, і гэтак далей.
Зараз давайце дадамо 2-радковы элементарны макрас, які капіюе згенераваны радок і дадае яго ў табліцу Sales. Для гэтага націсніце камбінацыю Alt + F11 альбо кнопка Візуальны Бейсик таб распрацоўшчык (Распрацоўшчык). Калі гэтая ўкладка не бачная, спачатку ўключыце яе ў наладах Файл – Параметры – Налада стужкі (Файл — Параметры — Наладзіць стужку). У акне рэдактара 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 гэта запатрабуе выкарыстання некалькіх функцый. ВПР (ВПР) для падстаноўкі ў табліцу цэн, катэгорый, кліентаў, гарадоў і г.д продажаў. Гэта патрабуе ад нас часу і намаганняў, а таксама «з'ядае» шмат рэсурсаў Excel. Пачынаючы з Excel 2013, усё можна рэалізаваць значна прасцей, усталяваўшы сувязі паміж табліцамі.
Для гэтага на таб Дата (Дата) пстрычка Relations (Адносіны). У якое з'явілася акне націсніце кнопку Ствараць (новы) і абярыце з выпадальных спісаў табліцы і назвы слупкоў, па якіх яны павінны быць звязаны:
Важны момант: табліцы павінны быць указаны ў такім парадку, гэта значыць звязаная табліца (цана) не павінна ўтрымлівацца ў ключавым слупку (Імя) паўтаральныя прадукты, як гэта адбываецца ў табліцы продажаў. Іншымі словамі, звязаная табліца павінна быць той, у якой вы б шукалі дадзеныя з дапамогай ВПРкалі б ён быў выкарыстаны.
Вядома, стол злучаецца падобным чынам продажаў са сталом Кліент агульным слупком Кліент:
Пасля наладжвання спасылак акно кіравання спасылкамі можна зачыніць; вам не трэба паўтараць гэтую працэдуру.
Крок 5. Будуем справаздачы з дапамогай зводкі
Цяпер для аналізу продажаў і адсочвання дынамікі працэсу створым, напрыклад, нейкі справаздачу з дапамогай зводнай табліцы. Усталяваць актыўную ячэйку ў табліцу продажаў і абярыце ўкладку на стужцы Устаўка – зводная табліца (Устаўка — зводная табліца). У якое адкрылася акне Excel спытае нас пра крыніцу даных (напрыклад, табліцу продажаў) і месца для загрузкі справаздачы (пажадана на новым аркушы):
Галоўнае, што неабходна ўключыць сцяжок Дадайце гэтыя даныя ў мадэль даных (Дадаць даныя ў мадэль даных) унізе акна, каб Excel зразумеў, што мы хочам стварыць справаздачу не толькі па бягучай табліцы, але і выкарыстоўваць усе сувязі.
Пасля націску на кнопку OK у правай палове акна з'явіцца панэль Палі зводнай табліцыдзе націснуць спасылку Усебачыць не толькі бягучую, але і ўсе «разумныя табліцы», якія ёсць у кнізе адразу. І тады, як і ў класічнай зводнай табліцы, вы можаце проста перацягнуць патрэбныя нам палі з любых звязаных табліц у вобласць фільтры, Радкі, Стаўбцоўшчына or каштоўнасці – і Excel імгненна створыць любую патрэбную справаздачу на аркушы:
Не забывайце, што зводную табліцу неабходна перыядычна абнаўляць (пры змене зыходных дадзеных), клікнуўшы па ёй правай кнопкай мышы і выбраўшы каманду Абнавіць і захаваць (Абнавіць), таму што ён не можа зрабіць гэта аўтаматычна.
Акрамя таго, выбраўшы любую ячэйку ў зводцы і націснуўшы кнопку Зводная дыяграма (Зводная дыяграма) таб Аналіз (Аналіз) or параметры (Параметры) вы можаце хутка візуалізаваць вынікі, разлічаныя ў ім.
Крок 6. Запоўніце раздрукаваныя файлы
Яшчэ адной тыповай задачай любой базы дадзеных з'яўляецца аўтаматычнае запаўненне разнастайных друкаваных бланкаў і бланкаў (рахункі-фактуры, рахункі-фактуры, акты і інш.). Пра адзін са спосабаў зрабіць гэта я ўжо пісаў. Тут мы рэалізуем, напрыклад, запаўненне формы па нумары рахунку:
Мяркуецца, што ў вочку С2 карыстальнік увядзе лік (нумар радка ў табліцы продажаў, на самай справе), а затым патрэбныя нам дадзеныя падцягваюцца з дапамогай ужо знаёмай функцыі ВПР (ВПР) і асаблівасці індэкс (ІНДЭКС).
- Як выкарыстоўваць функцыю VLOOKUP для пошуку значэнняў
- Як замяніць VLOOKUP функцыямі INDEX і MATCH
- Аўтаматычнае запаўненне бланкаў і бланкаў дадзенымі з табліцы
- Стварэнне справаздач з дапамогай зводных табліц