Мазмұны
Мәліметтер қоры (ДБ) туралы сөз қозғағанда, ең алдымен, SQL, Oracle, 1C немесе ең болмағанда Access сияқты кең таралған сөздердің бәрі еске түседі. Әрине, бұл үлкен және күрделі компанияның жұмысын көптеген деректермен автоматтандыруға болатын өте қуатты (және көбіне қымбат) бағдарламалар. Мәселе мынада, кейде мұндай күш қажет емес. Сіздің бизнесіңіз шағын және салыстырмалы түрде қарапайым бизнес процестері бар болуы мүмкін, бірақ сіз оны автоматтандырғыңыз келеді. Ал шағын компаниялар үшін бұл көбінесе өмір сүру мәселесі.
Алдымен TOR тұжырымдап алайық. Көп жағдайда бухгалтерлік есепке арналған деректер базасы, мысалы, классикалық сатылымдар:
- сақтау кестелерде тауарлар (бағасы), аяқталған транзакциялар және тұтынушылар туралы ақпарат және осы кестелерді бір-бірімен байланыстыру
- ыңғайлы болсын енгізу формалары деректер (ашылатын тізімдермен және т.б.)
- кейбір деректерді автоматты түрде толтырады басылған пішіндер (төлемдер, шоттар және т.б.)
- қажеттісін шығару есептер менеджердің көзқарасы бойынша бүкіл бизнес процесін бақылау
Microsoft Excel мұның бәрін аздап күш жұмсай алады. Осыны жүзеге асыруға тырысайық.
Қадам 1. Кесте түріндегі бастапқы деректер
Біз өнімдер, сатылымдар және тұтынушылар туралы ақпаратты үш кестеде сақтаймыз (бір парақта немесе әртүрлі парақта – маңызды емес). Болашақта бұл туралы ойламау үшін оларды автоматты өлшемі бар «ақылды үстелдерге» айналдыру өте маңызды. Бұл пәрмен арқылы орындалады Кесте ретінде пішімдеу қойындысы Home (Үй — Кесте ретінде пішімдеу). Содан кейін пайда болатын қойындыда Конструктор (Дизайн) өрісте кестелерге сипаттама атауларын беріңіз Кесте атауы кейінірек пайдалану үшін:
Барлығы үш «ақылды үстелді» алуымыз керек:
Кестелерде қосымша нақтылау деректері болуы мүмкін екенін ескеріңіз. Мәселен, мысалы, біздің бағаәрбір өнімнің санаты (өнім тобы, орамы, салмағы және т.б.) және кесте туралы қосымша ақпаратты қамтиды клиент — олардың әрқайсысының қала және облыс (мекен-жайы, СТН, банктік деректемелері және т.б.).
үстел Сату Біз оны кейінірек оған аяқталған транзакцияларды енгізу үшін пайдаланамыз.
2-қадам. Деректерді енгізу пішінін жасаңыз
Әрине, сату деректерін тікелей жасыл кестеге енгізуге болады Сату, бірақ бұл әрқашан ыңғайлы бола бермейді және «адам факторына» байланысты қателер мен қателердің пайда болуына әкеледі. Сондықтан, келесідей нәрсенің жеке парағына деректерді енгізу үшін арнайы пішінді жасаған дұрыс:
B3 ұяшығында жаңартылған ағымдағы күн уақытын алу үшін функцияны пайдаланыңыз TDATA (ҚАЗІР). Уақыт қажет болмаса, оның орнына TDATA функциясын қолдануға болады БҮГІН (БҮГІН).
В11 ұяшығында смарт кестенің үшінші бағанында таңдалған өнімнің бағасын табыңыз баға функциясын пайдалану VPR (КӨРУ). Егер сіз оны бұрын кездестірмеген болсаңыз, алдымен мына жерден бейнені оқып, қараңыз.
B7 ұяшығында бізге бағалар тізіміндегі өнімдері бар ашылмалы тізім қажет. Ол үшін пәрменді пайдалануға болады Деректер – деректерді тексеру (Деректер — Валидация), шектеу ретінде көрсетіңіз тізім (Тізім) содан кейін өріске кіріңіз қайнар көз (Көз) бағанға сілтеме Толық аты-жөніңіз біздің ақылды үстелімізден баға:
Сол сияқты, клиенттері бар ашылмалы тізім жасалады, бірақ дереккөз тарырақ болады:
=ЖАНА («Тұтынушылар [Клиент]»)
функция ТӘУЕЛСІЗДІК (ТІКЕСІЗ) бұл жағдайда қажет, себебі Excel, өкінішке орай, Source өрісіндегі смарт кестелерге тікелей сілтемелерді түсінбейді. Бірақ сол сілтеме функцияға «оралған». ТӘУЕЛСІЗДІК сонымен бірге ол жарылыспен жұмыс істейді (бұл туралы көбірек мазмұнмен ашылмалы тізімдерді жасау туралы мақалада айтылған).
3-қадам. Сатылым жазбасының макросын қосу
Пішінді толтырғаннан кейін оған енгізілген деректерді кестенің соңына қосу керек Сату. Қарапайым сілтемелерді пайдалана отырып, біз пішіннің астына қосылатын жолды қалыптастырамыз:
Анау. A20 ұяшығында =B3, В20 ұяшығында =B7 сілтемесі болады және т.б.
Енді құрылған жолды көшіретін және оны Сатылымдар кестесіне қосатын 2 жолды элементар макросты қосамыз. Мұны істеу үшін комбинацияны басыңыз Alt + F11 немесе батырма Visual Basic қойындысы әзірлеуші (Әзірлеуші). Бұл қойынды көрінбесе, алдымен параметрлерде оны қосыңыз Файл – Параметрлер – Таспаны орнату (Файл — Параметрлер — таспаны теңшеу). Ашылған Visual Basic редакторының терезесінде мәзір арқылы жаңа бос модульді кірістіріңіз Кірістіру – модуль және сол жерге біздің макрокодты енгізіңіз:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Дерек жолын пішімнен көшіріңіз n = Worksheets("Sales").Range("A100000").End(xlUp) . 'жолы кестедегі соңғы жолдың нөмірін анықтайды. Сату жұмыс парақтары("Сатулар").Ұяшықтар(n + 1, 1).PasteSpecial Paste:=xlPasteValues' келесі бос жолға қою Жұмыс парақтары("Енгізу пішіні").Ауқым("B5,B7,B9"). ClearContents 'соңының ішкі пішінін тазалау Енді ашылмалы тізім арқылы жасалған макросты іске қосу үшін пішінге түймені қосуға болады салу қойындысы әзірлеуші (Әзірлеуші — Кірістіру — Түйме):
Оны салғаннан кейін тінтуірдің сол жақ батырмасын басып тұрып, Excel сізден оған қандай макрос тағайындау керектігін сұрайды - біздің макросты таңдаңыз. Қосу_сату. Түймедегі мәтінді тінтуірдің оң жақ түймешігімен басып, пәрменді таңдау арқылы өзгертуге болады Мәтінді өзгерту.
Енді пішінді толтырғаннан кейін сіз жай ғана біздің түймені баса аласыз, енгізілген деректер кестеге автоматты түрде қосылады Сату, содан кейін пішін жаңа мәміле жасау үшін тазартылады.
4-қадам Кестелерді байланыстыру
Есепті құрастырмас бұрын, кейінірек аймақ, тұтынушы немесе санат бойынша сатылымдарды жылдам есептей алу үшін кестелерімізді біріктірейік. Excel бағдарламасының ескі нұсқаларында бұл бірнеше функцияларды пайдалануды қажет етеді. VPR (КӨРУ) кестеге бағаларды, санаттарды, тұтынушыларды, қалаларды және т.б. ауыстыру үшін Сату. Бұл бізден уақыт пен күш-жігерді талап етеді, сонымен қатар көптеген Excel ресурстарын «жейді». Excel 2013 нұсқасынан бастап кестелер арасындағы қарым-қатынастарды орнату арқылы барлығын оңайырақ орындауға болады.
Мұны істеу үшін қойындыда мәліметтер (Күні) басыңыз Қарым-қатынастар (Қарым-қатынастар). Пайда болған терезеде түймені басыңыз жасау (жаңа) және ашылмалы тізімдерден олар байланысты болуы керек кестелер мен баған атауларын таңдаңыз:
Маңызды сәт: кестелер осы ретпен көрсетілуі керек, яғни байланыстырылған кесте (баға) кілт бағанында болмауы керек (Толық аты-жөніңіз) кестеде көрсетілгендей қайталанатын өнімдер Сату. Басқаша айтқанда, байланысты кесте деректерді пайдаланып іздейтін кесте болуы керек VPRегер ол қолданылған болса.
Әрине, кесте ұқсас жолмен қосылған Сату кестемен клиент ортақ баған бойынша сатып алушы:
Сілтемелерді орнатқаннан кейін сілтемелерді басқару терезесін жабуға болады; бұл процедураны қайталаудың қажеті жоқ.
Қадам 5. Қорытындыны пайдаланып есептерді құрастырамыз
Енді сатылымдарды талдау және процестің динамикасын қадағалау үшін, мысалы, жиынтық кестені пайдаланып есептің қандай да бір түрін жасайық. Белсенді ұяшықты кестеге орнату Сату және таспадағы қойындыны таңдаңыз Кірістіру – жиынтық кесте (Кірістіру — жиынтық кесте). Ашылған терезеде Excel бағдарламасы бізден деректер көзі туралы сұрайды (яғни кесте Сату) және есепті жүктеп салу орны (жаңа парақта жақсырақ):
Ең бастысы, құсбелгіні қосу қажет Бұл деректерді деректер үлгісіне қосыңыз (Деректер үлгісіне деректерді қосу) Терезенің төменгі жағында Excel бағдарламасы есепті тек ағымдағы кестеде ғана емес, сонымен қатар барлық қатынастарды қолданғымыз келетінін түсінеді.
Басқаннан кейін OK терезенің оң жақ жартысында панель пайда болады Жиынтық кесте өрістерісілтемені қайда басуға болады Барлықтек ағымдағыны ғана емес, кітаптағы барлық «ақылды үстелдерді» бірден көру. Содан кейін, классикалық жиынтық кестедегідей, кез келген қатысты кестелерден бізге қажет өрістерді аймаққа жай ғана сүйреп апаруға болады. фильтр, Жолдар, Столбцов or Құндылықтар – және Excel парақта бізге қажет кез келген есепті лезде құрастырады:
Жиынтық кестені тінтуірдің оң жақ түймешігімен нұқып, пәрменді таңдау арқылы (бастапқы деректер өзгерген кезде) мерзімді түрде жаңартылуы керек екенін ұмытпаңыз. Жаңарту және сақтау (Жаңарту), себебі ол мұны автоматты түрде жасай алмайды.
Сондай-ақ, жиынтықта кез келген ұяшықты таңдап, түймені басу арқылы Жиынтық диаграмма (жиынтық диаграмма) қойындысы талдау (Талдау) or Параметрлер (Опциялар) онда есептелген нәтижелерді тез елестетуге болады.
6-қадам. Басып шығарылатын заттарды толтырыңыз
Кез келген деректер қорының тағы бір типтік міндеті әртүрлі баспа бланкілері мен бланкілерін (шот-фактуралар, шот-фактуралар, актілер және т.б.) автоматты түрде толтыру болып табылады. Мен мұны істеудің бір жолы туралы жаздым. Мұнда біз, мысалы, шот нөмірі бойынша пішінді толтыруды жүзеге асырамыз:
С2 ұяшығына пайдаланушы санды (кестедегі жол нөмірін) енгізеді деп болжанады Сату, шын мәнінде), содан кейін бізге қажет деректер бұрыннан таныс функция арқылы шығарылады VPR (КӨРУ) және ерекшеліктері INDEX (ИНДЕКС).
- Мәндерді іздеу және іздеу үшін VLOOKUP функциясын пайдалану жолы
- VLOOKUP функциясын INDEX және MATCH функцияларымен қалай ауыстыруға болады
- Кесте деректерімен пішіндер мен пішіндерді автоматты түрде толтыру
- Жиынтық кестелермен есептерді құру