შინაარსი
მონაცემთა ბაზების (DB) ხსენებისას, პირველი, რაც მახსენდება, რა თქმა უნდა, არის ყველა სახის ხმაურიანი სიტყვა, როგორიცაა SQL, Oracle, 1C, ან თუნდაც Access. რა თქმა უნდა, ეს არის ძალიან მძლავრი (და უმეტესწილად ძვირი) პროგრამები, რომლებსაც შეუძლიათ დიდი და რთული კომპანიის მუშაობის ავტომატიზაცია, ბევრი მონაცემებით. უბედურება ის არის, რომ ზოგჯერ ასეთი ძალა უბრალოდ არ არის საჭირო. თქვენი ბიზნესი შეიძლება იყოს მცირე და შედარებით მარტივი ბიზნეს პროცესებით, მაგრამ თქვენ ასევე გსურთ მისი ავტომატიზაცია. და სწორედ მცირე კომპანიებისთვის ეს ხშირად გადარჩენის საკითხია.
დასაწყისისთვის, მოდით ჩამოვაყალიბოთ TOR. უმეტეს შემთხვევაში, ბუღალტრული აღრიცხვის მონაცემთა ბაზა, მაგალითად, კლასიკური გაყიდვები უნდა შეეძლოს:
- შენარჩუნება ცხრილებში ინფორმაცია საქონლის (ფასის), დასრულებული ტრანზაქციების და მომხმარებლების შესახებ და ამ ცხრილების ერთმანეთთან დაკავშირება
- აქვს კომფორტული შეყვანის ფორმები მონაცემები (ჩასაშლელი სიებით და ა.შ.)
- ავტომატურად შეავსებს ზოგიერთ მონაცემს ნაბეჭდი ფორმები (გადახდები, გადასახადები და ა.შ.)
- გასცეს საჭირო რეპორტაჟი აკონტროლოს მთელი ბიზნეს პროცესი მენეჯერის თვალსაზრისით
Microsoft Excel ამ ყველაფერს მცირე ძალისხმევით უმკლავდება. შევეცადოთ ამის განხორციელება.
ნაბიჯი 1. საწყისი მონაცემები ცხრილების სახით
ჩვენ შევინახავთ ინფორმაციას პროდუქციის, გაყიდვებისა და მომხმარებლების შესახებ სამ ცხრილში (ერთსა და იმავე ფურცელზე თუ სხვადასხვა - არ აქვს მნიშვნელობა). ფუნდამენტურად მნიშვნელოვანია მათი გადაქცევა „ჭკვიან მაგიდებად“ ავტომატური ზომით, რათა მომავალში ამაზე არ ვიფიქროთ. ეს კეთდება ბრძანებით ფორმატირება, როგორც ცხრილი tab მთავარი (მთავარი - ფორმატირება როგორც ცხრილი). ჩანართზე, რომელიც შემდეგ გამოჩნდება კონსტრუქტორი (დიზაინი) მიეცით ცხრილების აღწერილობითი სახელები ველში მაგიდის სახელი შემდგომი გამოყენებისთვის:
საერთო ჯამში, სამი „ჭკვიანი მაგიდა“ უნდა მივიღოთ:
გთხოვთ გაითვალისწინოთ, რომ ცხრილები შეიძლება შეიცავდეს დამატებით დამაზუსტებელ მონაცემებს. ასე, მაგალითად, ჩვენი ფასიშეიცავს დამატებით ინფორმაციას თითოეული პროდუქტის კატეგორიის (პროდუქტის ჯგუფი, შეფუთვა, წონა და ა.შ.) და ცხრილის შესახებ კლიენტი — თითოეული მათგანის ქალაქი და რეგიონი (მისამართი, TIN, საბანკო რეკვიზიტები და ა.შ.).
მაგიდის გაყიდვების მოგვიანებით გამოყენებული იქნება მასში დასრულებული ტრანზაქციების შესატანად.
ნაბიჯი 2. შექმენით მონაცემთა შეყვანის ფორმა
რა თქმა უნდა, თქვენ შეგიძლიათ შეიყვანოთ გაყიდვების მონაცემები პირდაპირ მწვანე ცხრილში გაყიდვების, მაგრამ ეს ყოველთვის არ არის მოსახერხებელი და იწვევს შეცდომებისა და შეცდომების გამოჩენას "ადამიანური ფაქტორის" გამო. აქედან გამომდინარე, უკეთესი იქნება, რომ სპეციალური ფორმა შევიტანოთ მონაცემების ცალკეულ ფურცელზე მსგავსი რამ:
B3 უჯრედში, განახლებული მიმდინარე თარიღის დროის მისაღებად, გამოიყენეთ ფუნქცია TDATA (ახლავე). თუ დრო არ არის საჭირო, მაშინ ამის ნაცვლად TDATA ფუნქციის გამოყენება შესაძლებელია დღეს (დღეს).
B11 უჯრედში იპოვეთ არჩეული პროდუქტის ფასი ჭკვიანი ცხრილის მესამე სვეტში ფასი ფუნქციის გამოყენებით VPR (VLOOKUP). თუ აქამდე არ შეგხვედრიათ, ჯერ წაიკითხეთ და ნახეთ ვიდეო აქ.
B7 უჯრედში, ჩვენ გვჭირდება ჩამოსაშლელი სია ფასების სიიდან პროდუქტებით. ამისათვის შეგიძლიათ გამოიყენოთ ბრძანება მონაცემები – მონაცემთა დადასტურება (მონაცემები — დადასტურება), მიუთითეთ როგორც შეზღუდვა სია (სია) და შემდეგ შედით ველში წყარო (წყარო) სვეტის ბმული სახელი ჩვენი ჭკვიანი მაგიდიდან ფასი:
ანალოგიურად, იქმნება ჩამოსაშლელი სია კლიენტებთან, მაგრამ წყარო უფრო ვიწრო იქნება:
=INDIRECT(„მომხმარებლები[კლიენტი]“)
ფუნქცია არაპირდაპირი (არაპირდაპირი) საჭიროა, ამ შემთხვევაში, რადგან Excel, სამწუხაროდ, არ ესმის წყაროს ველში ჭკვიანი ცხრილების პირდაპირი ბმულები. მაგრამ იგივე ბმული "შეფუთულია" ფუნქციაში არაპირდაპირი ამავდროულად, ის მუშაობს ხმაურით (დაწვრილებით ამის შესახებ იყო სტატიაში შინაარსით ჩამოსაშლელი სიების შექმნის შესახებ).
ნაბიჯი 3. გაყიდვების შესვლის მაკრო დამატება
ფორმის შევსების შემდეგ, თქვენ უნდა დაამატოთ მასში შეყვანილი მონაცემები ცხრილის ბოლოს გაყიდვების. მარტივი ბმულების გამოყენებით, ჩვენ შევქმნით ხაზს, რომელიც უნდა დაემატოს ფორმის ქვემოთ:
იმათ. A20 უჯრედს ექნება ბმული =B3, B20 უჯრედს ექნება ბმული =B7 და ა.შ.
ახლა დავამატოთ 2 სტრიქონიანი ელემენტარული მაკრო, რომელიც დააკოპირებს გენერირებულ სტრიქონს და ამატებს გაყიდვების ცხრილში. ამისათვის დააჭირეთ კომბინაციას Alt + F11 ან ღილაკი Visual Basic tab დეველოპერი (დეველოპერი). თუ ეს ჩანართი არ ჩანს, ჯერ ჩართეთ ის პარამეტრებში ფაილი – ოფციები – ლენტის დაყენება (ფაილი - ოფციები - ლენტის მორგება). Visual Basic რედაქტორის ფანჯარაში, რომელიც იხსნება, მენიუში ჩადეთ ახალი ცარიელი მოდული ჩასმა – მოდული და შეიყვანეთ ჩვენი მაკრო კოდი იქ:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy the data line from form n = Worksheets("Sales").Range("A100000").End(xlUp) . მწკრივი 'განსაზღვრეთ ცხრილის ბოლო მწკრივის რაოდენობა. გაყიდვების სამუშაო ფურცლები ("გაყიდვები"). უჯრედები (n + 1, 1). PasteSpecial Paste:=xlPasteValues' ჩასვით მომდევნო ცარიელ ხაზში Worksheets ("შეყვანის ფორმა"). დიაპაზონი ("B5,B7,B9"). ClearContents 'გასუფთავება ბოლოს ქვე ფორმა ახლა ჩვენ შეგვიძლია დავამატოთ ღილაკი ჩვენს ფორმაში, რათა შეიქმნას მაკრო ჩამოსაშლელი სიის გამოყენებით ჩადეთ tab დეველოპერი (დეველოპერი - ჩასმა - ღილაკი):
დახატვის შემდეგ, მაუსის მარცხენა ღილაკზე დაჭერით, Excel გკითხავთ, რომელი მაკრო უნდა მივანიჭოთ მას – აირჩიეთ ჩვენი მაკრო დამატება_გაყიდვა. თქვენ შეგიძლიათ შეცვალოთ ტექსტი ღილაკზე მაუსის მარჯვენა ღილაკით და ბრძანების არჩევით ტექსტის შეცვლა.
ახლა, ფორმის შევსების შემდეგ, შეგიძლიათ უბრალოდ დააჭიროთ ჩვენს ღილაკს და შეყვანილი მონაცემები ავტომატურად დაემატება ცხრილში. გაყიდვებისდა შემდეგ ფორმა იწმინდება ახალი გარიგების შესატანად.
ნაბიჯი 4 ცხრილების დაკავშირება
ანგარიშის შექმნამდე, მოდით დავაკავშიროთ ჩვენი ცხრილები, რათა მოგვიანებით სწრაფად გამოვთვალოთ გაყიდვები რეგიონის, მომხმარებლის ან კატეგორიის მიხედვით. Excel-ის ძველ ვერსიებში ეს მოითხოვს რამდენიმე ფუნქციის გამოყენებას. VPR (VLOOKUP) ფასების, კატეგორიების, მომხმარებლების, ქალაქების და ა.შ. მაგიდაზე ჩანაცვლებისთვის გაყიდვების. ეს მოითხოვს ჩვენგან დროსა და ძალისხმევას და ასევე "ჭამს" უამრავ Excel რესურსს. Excel 2013-დან დაწყებული, ყველაფერი შეიძლება ბევრად უფრო მარტივად განხორციელდეს ცხრილებს შორის ურთიერთობების დაყენებით.
ამისათვის, ჩანართზე თარიღი (თარიღი) დააჭირეთ ურთიერთობები (ურთიერთობები). ფანჯარაში, რომელიც გამოჩნდება, დააჭირეთ ღილაკს შექმნა (ახალი) და ჩამოსაშლელი სიებიდან აირჩიეთ ცხრილები და სვეტების სახელები, რომლებითაც ისინი უნდა იყოს დაკავშირებული:
მნიშვნელოვანი პუნქტი: ცხრილები უნდა იყოს მითითებული ამ თანმიმდევრობით, ანუ მიბმული ცხრილი (ფასი) არ უნდა შეიცავდეს გასაღების სვეტში (სახელი) პროდუქტების დუბლიკატი, როგორც ეს ხდება ცხრილში გაყიდვების. სხვა სიტყვებით რომ ვთქვათ, ასოცირებული ცხრილი უნდა იყოს ის, რომელშიც თქვენ მოძებნით მონაცემებს გამოყენებით VPRთუ გამოიყენებოდა.
რა თქმა უნდა, ცხრილი დაკავშირებულია ანალოგიურად გაყიდვების მაგიდით კლიენტი საერთო სვეტით დამკვეთი:
ბმულების დაყენების შემდეგ შესაძლებელია ბმულების მართვის ფანჯრის დახურვა; თქვენ არ გჭირდებათ ამ პროცედურის გამეორება.
ნაბიჯი 5. ჩვენ ვაშენებთ ანგარიშებს შეჯამების გამოყენებით
ახლა, გაყიდვების გასაანალიზებლად და პროცესის დინამიკის თვალყურის დევნებისთვის, მოდით შევქმნათ, მაგალითად, რაიმე სახის ანგარიში კრებსითი ცხრილის გამოყენებით. აქტიური უჯრედის დაყენება ცხრილზე გაყიდვების და აირჩიეთ ჩანართი ლენტაზე ჩასმა – PivotTable (ჩასმა - კრებსითი ცხრილი). ფანჯარაში, რომელიც იხსნება, Excel გვკითხავს მონაცემთა წყაროს შესახებ (მაგ. ცხრილი გაყიდვების) და ანგარიშის ატვირთვის ადგილი (სასურველია ახალ ფურცელზე):
სასიცოცხლოდ მნიშვნელოვანი ის არის, რომ აუცილებელია ჩამრთველის ჩართვა დაამატეთ ეს მონაცემები მონაცემთა მოდელს (მონაცემების დამატება მონაცემთა მოდელში) ფანჯრის ბოლოში, რათა Excel-მა გააცნობიეროს, რომ ჩვენ გვინდა შევქმნათ ანგარიში არა მხოლოდ მიმდინარე ცხრილზე, არამედ გამოვიყენოთ ყველა ურთიერთობა.
დაწკაპვით OK პანელი გამოჩნდება ფანჯრის მარჯვენა ნახევარში კრებსითი ცხრილის ველებისადაც დააწკაპუნეთ ბმულზე ყველარომ ნახოთ არა მხოლოდ მიმდინარე, არამედ ყველა "ჭკვიანი ცხრილი", რომელიც წიგნშია ერთდროულად. და შემდეგ, როგორც კლასიკური კრებსითი ცხრილის მსგავსად, შეგიძლიათ უბრალოდ გადაიტანოთ ის ველები, რომლებიც გვჭირდება ნებისმიერი დაკავშირებული ცხრილიდან ზონაში. ფილტრი, რიგები, სტოლბცოვი or ფასეულობები – და Excel მყისიერად ააშენებს ფურცელზე ჩვენთვის საჭირო ნებისმიერ ანგარიშს:
არ დაგავიწყდეთ, რომ კრებსითი ცხრილი პერიოდულად უნდა განახლდეს (როდესაც წყაროს მონაცემები იცვლება) მასზე მაუსის მარჯვენა ღილაკით და ბრძანების არჩევით. განახლება და შენახვა (განახლება), რადგან მას ავტომატურად არ შეუძლია ამის გაკეთება.
ასევე, შეჯამებაში რომელიმე უჯრედის არჩევით და ღილაკზე დაჭერით Pivot Chart (საყრდენი დიაგრამა) tab ანალიზი (ანალიზი) or პარამეტრები (Პარამეტრები) თქვენ შეგიძლიათ სწრაფად წარმოიდგინოთ მასში გამოთვლილი შედეგები.
ნაბიჯი 6. შეავსეთ ამოსაბეჭდი ფაილები
ნებისმიერი მონაცემთა ბაზის კიდევ ერთი ტიპიური ამოცანაა სხვადასხვა დაბეჭდილი ფორმებისა და ფორმების (ინვოისები, ინვოისები, აქტები და ა.შ.) ავტომატური შევსება. მე უკვე დავწერე ამის გაკეთების ერთ-ერთი ხერხის შესახებ. აქ ჩვენ ვახორციელებთ, მაგალითად, ფორმის შევსებას ანგარიშის ნომრით:
ვარაუდობენ, რომ C2 უჯრედში მომხმარებელი შეიყვანს რიცხვს (სტრიქონის ნომერი ცხრილში გაყიდვებისფაქტობრივად), და შემდეგ ჩვენთვის საჭირო მონაცემები ამოღებულია უკვე ნაცნობი ფუნქციის გამოყენებით VPR (VLOOKUP) და თვისებები ინდექსი (INDEX).
- როგორ გამოვიყენოთ VLOOKUP ფუნქცია მნიშვნელობების მოსაძებნად და მოსაძიებლად
- როგორ შევცვალოთ VLOOKUP INDEX და MATCH ფუნქციებით
- ფორმებისა და ფორმების ავტომატური შევსება ცხრილის მონაცემებით
- ანგარიშების შექმნა PivotTables-ით