Mundarija
Ma'lumotlar bazalari (MB) haqida gap ketganda, birinchi navbatda, SQL, Oracle, 1C yoki hech bo'lmaganda Access kabi turli xil so'zlar esga tushadi. Albatta, bu juda kuchli (va ko'pincha qimmat) dasturlar bo'lib, ular juda ko'p ma'lumotlarga ega bo'lgan yirik va murakkab kompaniyaning ishini avtomatlashtirishi mumkin. Muammo shundaki, ba'zida bunday kuch kerak emas. Sizning biznesingiz kichik va nisbatan oddiy biznes jarayonlariga ega bo'lishi mumkin, lekin siz uni avtomatlashtirishni ham xohlaysiz. Va bu kichik kompaniyalar uchun ko'pincha omon qolish masalasidir.
Boshlash uchun, keling, TORni shakllantiramiz. Ko'pgina hollarda, buxgalteriya hisobi uchun ma'lumotlar bazasi, masalan, klassik savdo:
- saqlamoq jadvallarda tovarlar (narx), tugallangan operatsiyalar va mijozlar to'g'risidagi ma'lumotlar va bu jadvallarni bir-biriga bog'lang
- qulay bo'lsin kiritish shakllari ma'lumotlar (ochiladigan ro'yxatlar va boshqalar bilan)
- avtomatik ravishda ba'zi ma'lumotlarni to'ldirish bosma shakllar (to'lovlar, hisoblar va boshqalar)
- zaruriyatni chiqaradi hisobotlar menejer nuqtai nazaridan butun biznes jarayonini nazorat qilish
Microsoft Excel bularning barchasini ozgina harakat bilan hal qila oladi. Keling, buni amalga oshirishga harakat qilaylik.
Qadam 1. Jadvallar ko'rinishidagi dastlabki ma'lumotlar
Biz mahsulotlar, sotuvlar va mijozlar to'g'risidagi ma'lumotlarni uchta jadvalda (bir varaqda yoki boshqasida - bu muhim emas) saqlaymiz. Kelajakda bu haqda o'ylamaslik uchun ularni avtomatik o'lchamli "aqlli jadvallar" ga aylantirish juda muhimdir. Bu buyruq bilan amalga oshiriladi Jadval sifatida formatlash tab Bosh sahifa (Uy — Jadval sifatida formatlash). Keyin paydo bo'ladigan yorliqda konstruktor (Dizayn) maydonda jadvallarga tavsiflovchi nomlar bering Jadval nomi keyinroq foydalanish uchun:
Hammasi bo'lib uchta "aqlli stol" olishimiz kerak:
Jadvallarda qo'shimcha aniqlovchi ma'lumotlar bo'lishi mumkinligini unutmang. Shunday qilib, masalan, bizning narxhar bir mahsulotning toifasi (mahsulot guruhi, qadoqlash, vazni va boshqalar) va jadval haqida qo'shimcha ma'lumotlarni o'z ichiga oladi mijoz — har birining shahar va viloyati (manzil, TIN, bank rekvizitlari va boshqalar).
stol savdo keyinchalik biz unga tugallangan tranzaktsiyalarni kiritish uchun foydalanamiz.
Qadam 2. Ma'lumotlarni kiritish shaklini yarating
Albatta, siz savdo ma'lumotlarini to'g'ridan-to'g'ri yashil jadvalga kiritishingiz mumkin savdo, lekin bu har doim ham qulay emas va "inson omili" tufayli xatolar va matn terish xatolarining paydo bo'lishiga olib keladi. Shuning uchun, ma'lumotlarni alohida varaqqa kiritish uchun maxsus shakl yaratish yaxshiroqdir:
B3 katakchasida yangilangan joriy sana-vaqtni olish uchun funksiyadan foydalaning TDATA (HOZIR). Vaqt kerak bo'lmasa, o'rniga TDATA funktsiyasini qo'llash mumkin BUGUN (BUGUN).
B11 katakchada tanlangan mahsulot narxini aqlli jadvalning uchinchi ustunidan toping narx funksiyasidan foydalanish VPR (KO'RISH). Agar siz ilgari duch kelmagan bo'lsangiz, avval bu erda videoni o'qing va tomosha qiling.
B7 katakchasida narxlar ro'yxatidagi mahsulotlar bilan ochiladigan ro'yxat kerak. Buning uchun siz buyruqdan foydalanishingiz mumkin Ma'lumotlar - ma'lumotlarni tekshirish (Ma'lumotlar - tasdiqlash), cheklov sifatida belgilang ro'yxat (Ro'yxat) va keyin maydonga kiring manba (Manba) ustunga havola Ism bizning aqlli stolimizdan narx:
Xuddi shunday, mijozlar bilan ochiladigan ro'yxat yaratiladi, ammo manba torroq bo'ladi:
=BILOSHIY(“Mijozlar[mijoz]”)
vazifa BILVOSIT (BOSHQA) Bu kerak, chunki Excel, afsuski, Manba maydonidagi aqlli jadvallarga to'g'ridan-to'g'ri havolalarni tushunmaydi. Ammo xuddi shu havola funktsiyaga "o'ralgan" BILVOSIT shu bilan birga, u portlash bilan ishlaydi (bu haqda batafsilroq kontent bilan ochiladigan ro'yxatlarni yaratish haqidagi maqolada bo'lgan).
Qadam 3. Savdoga kirish makrosini qo'shish
Shaklni to'ldirgandan so'ng, unga kiritilgan ma'lumotlarni jadval oxiriga qo'shishingiz kerak savdo. Oddiy havolalardan foydalanib, biz shakl ostida qo'shiladigan qator hosil qilamiz:
Bular. A20 yacheykasi =B3 ga, B20 katakchasi =B7 ga havolaga ega bo'ladi va hokazo.
Endi hosil qilingan satrdan nusxa ko'chiradigan va uni Savdo jadvaliga qo'shadigan 2 qatorli elementar makrosni qo'shamiz. Buning uchun kombinatsiyani bosing Alt + F11 yoki tugma Visual Basic tab dasturchi (ishlab chiquvchi). Agar ushbu yorliq ko'rinmasa, avval sozlamalarda uni yoqing Fayl - Variantlar - Tasmani o'rnatish (Fayl - Variantlar - Tasmani sozlash). Ochilgan Visual Basic muharriri oynasida menyu orqali yangi bo'sh modulni kiriting Qo'shish - modul va u yerga bizning makro kodimizni kiriting:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Ma'lumotlar qatorini shakldan nusxalash n = Worksheets("Sales").Range("A100000").End(xlUp) . Qator 'jadvaldagi oxirgi qatorning sonini aniqlang. Savdo ishchi varaqlari("Sotish").Yacheykalar(n + 1, 1).Maxsus Paste:=xlPasteValues' keyingi bo'sh qatorga qo'ying Worksheets("Kirish formasi").Range("B5,B7,B9"). ClearContents 'o'ngi pastki shaklini tozalash Endi ochiladigan ro'yxat yordamida yaratilgan makrosni ishga tushirish uchun formamizga tugma qo'shishimiz mumkin Insert tab dasturchi (Ishlab chiquvchi - Insert - tugmasi):
Uni chizganingizdan so'ng, sichqonchaning chap tugmachasini bosib ushlab turing, Excel sizdan unga qaysi makrosni belgilashingiz kerakligini so'raydi - bizning makromizni tanlang. Qo'shish_Sotish. Siz tugmachadagi matnni sichqonchaning o'ng tugmasi bilan bosish va buyruqni tanlash orqali o'zgartirishingiz mumkin Matnni o'zgartirish.
Endi, shaklni to'ldirgandan so'ng, siz shunchaki bizning tugmani bosishingiz mumkin va kiritilgan ma'lumotlar avtomatik ravishda jadvalga qo'shiladi. savdo, va keyin shakl yangi bitimni kiritish uchun tozalanadi.
4-qadam Jadvallarni ulash
Hisobotni tuzishdan oldin, keling, jadvallarimizni bir-biriga bog'laymiz, shunda keyinchalik mintaqa, mijoz yoki toifalar bo'yicha sotuvlarni tezda hisoblashimiz mumkin. Excelning eski versiyalarida bu bir nechta funksiyalardan foydalanishni talab qiladi. VPR (KO'RISH) jadvalga narxlar, toifalar, mijozlar, shaharlar va boshqalarni almashtirish uchun savdo. Bu bizdan vaqt va kuch talab qiladi, shuningdek, ko'plab Excel resurslarini "yeydi". Excel 2013 dan boshlab, jadvallar orasidagi munosabatlarni o'rnatish orqali hamma narsani ancha sodda tarzda amalga oshirish mumkin.
Buning uchun yorliqda ma'lumotlar (Sana) bosish munosabatlar (Munosabatlar). Ko'rsatilgan oynada tugmani bosing yaratish (yangi) va ochiladigan ro'yxatlardan ular bog'lanishi kerak bo'lgan jadvallar va ustun nomlarini tanlang:
Muhim nuqta: jadvallar ushbu tartibda ko'rsatilishi kerak, ya'ni bog'langan jadval (narx) kalit ustunida bo'lmasligi kerak (Ism) jadvalda ko'rsatilganidek, takroriy mahsulotlar savdo. Boshqacha qilib aytadigan bo'lsak, bog'langan jadval siz ma'lumotlarni qidiradigan jadval bo'lishi kerak VPRagar u ishlatilgan bo'lsa.
Albatta, stol shunga o'xshash tarzda bog'langan savdo stol bilan mijoz umumiy ustun bo'yicha Mijozlar:
Ulanishlarni o'rnatgandan so'ng, havolalarni boshqarish oynasi yopilishi mumkin; ushbu protsedurani takrorlashingiz shart emas.
Qadam 5. Xulosa yordamida hisobotlarni tuzamiz
Endi sotuvlarni tahlil qilish va jarayon dinamikasini kuzatish uchun, masalan, pivot jadval yordamida qandaydir hisobotni yarataylik. Faol hujayrani jadvalga o'rnating savdo va tasmadagi yorliqni tanlang Qo'shish - Pivot jadval (Qo'shish - umumiy jadval). Ochilgan oynada Excel bizdan ma'lumotlar manbai (ya'ni jadval savdo) va hisobotni yuklash joyi (yaxshisi yangi varaqda):
Muhim nuqta shundaki, tasdiqlash qutisini yoqish kerak Ushbu ma'lumotlarni ma'lumotlar modeliga qo'shing (Ma'lumotlar modeliga ma'lumotlarni qo'shing) Oynaning pastki qismida Excel biz nafaqat joriy jadvalda hisobot tuzmoqchi ekanligimizni, balki barcha munosabatlardan ham foydalanishni tushunishi uchun.
Tugmasini bosgandan so'ng OK oynaning o'ng yarmida panel paydo bo'ladi Pivot jadval maydonlarihavolani qayerda bosing hammanafaqat hozirgi, balki kitobdagi barcha "aqlli jadvallar" ni bir vaqtning o'zida ko'rish uchun. Va keyin, klassik pivot jadvalda bo'lgani kabi, biz kerakli maydonlarni istalgan tegishli jadvallardan maydonga sudrab olib borishingiz mumkin. filter, Satrlar, Stolbtsov or Qadriyatlar – va Excel varaqda bizga kerak bo'lgan har qanday hisobotni darhol tuzadi:
Shuni unutmangki, pivot jadvali vaqti-vaqti bilan yangilanishi kerak (manba ma'lumotlari o'zgarganda) ustiga sichqonchaning o'ng tugmachasini bosing va buyruqni tanlang. Yangilash va saqlash (yangilash), chunki u buni avtomatik ravishda qila olmaydi.
Shuningdek, xulosadagi istalgan katakchani tanlab, tugmani bosish orqali Pivot diagrammasi (Pivot diagrammasi) tab Tahlil (Tahlil) or Parameters (Tanlovlar) unda hisoblangan natijalarni tezda tasavvur qilishingiz mumkin.
6-qadam. Chop etish mumkin bo'lgan narsalarni to'ldiring
Har qanday ma'lumotlar bazasining yana bir tipik vazifasi turli bosma shakl va blankalarni (hisob-fakturalar, schyot-fakturalar, aktlar va boshqalar) avtomatik ravishda to'ldirishdir. Men buni qilish usullaridan biri haqida allaqachon yozganman. Bu erda biz, masalan, hisob raqami bo'yicha shaklni to'ldirishni amalga oshiramiz:
Foydalanuvchi C2 katakchaga raqam kiritadi deb taxmin qilinadi (jadvaldagi qator raqami). savdo, aslida) va keyin bizga kerak bo'lgan ma'lumotlar allaqachon tanish bo'lgan funksiya yordamida olinadi VPR (KO'RISH) va xususiyatlari INDEKS (INDEKS).
- Qiymatlarni qidirish va qidirish uchun VLOOKUP funksiyasidan qanday foydalanish kerak
- VLOOKUPni INDEX va MATCH funksiyalari bilan qanday almashtirish mumkin
- Shakllar va shakllarni jadval ma'lumotlari bilan avtomatik ravishda to'ldirish
- Pivot jadvallar yordamida hisobotlarni yaratish