Daptar eusi
Nalika nyebut database (DB), hal kahiji anu datang ka pikiran, tangtosna, nyaeta sagala sorts buzzwords kawas SQL, Oracle, 1C, atawa sahenteuna Aksés. Tangtosna, ieu mangrupikeun program anu kuat (sareng paling mahal) anu tiasa ngajadikeun otomatis padamelan perusahaan anu ageung sareng kompleks kalayan seueur data. Masalahna nyaéta sakapeung kakuatan sapertos kitu henteu diperyogikeun. Usaha anjeun tiasa leutik sareng prosés bisnis anu kawilang saderhana, tapi anjeun ogé hoyong ngajadikeun otomatis. Sareng pikeun perusahaan-perusahaan alit ieu sering janten masalah kasalametan.
Pikeun mimitian ku, hayu urang ngarumuskeun TOR. Dina kalolobaan kasus, database pikeun akuntansi, contona, penjualan klasik kedah tiasa:
- neundeun dina tabel informasi ngeunaan barang (harga), réngsé transaksi jeung konsumén jeung numbu tabel ieu ka silih
- gaduh nyaman formulir input data (kalayan daptar turun-handap, jsb.)
- otomatis eusian sababaraha data bentuk dicitak (pangmayaran, tagihan, jsb)
- ngaluarkeun nu diperlukeun laporan ngadalikeun sakabéh prosés bisnis ti sudut pandang manajer
Microsoft Excel tiasa ngadamel sadayana ieu kalayan sakedik usaha. Hayu urang coba nerapkeun ieu.
Lengkah 1. Data awal dina wangun tabél
Kami bakal nyimpen inpormasi ngeunaan produk, penjualan sareng palanggan dina tilu tabel (dina lambaran anu sami atanapi anu béda - henteu masalah). Dasarna penting pikeun ngarobahna kana "tabel pinter" kalayan ukuran otomatis, ku kituna henteu mikirkeun éta di hareup. Hal ieu dilakukeun ku paréntah Format salaku méja tab imah (Imah - Format salaku Méja). Dina tab nu lajeng nembongan Constructor (Desain) masihan tabel ngaran deskriptif dina widang Ngaran méja pikeun pamakéan engké:
Dina total, urang kedah nampi tilu "tabel pinter":
Punten dicatet yén tabél tiasa ngandung data klarifikasi tambahan. Ku kituna, contona, urang hargangandung émbaran tambahan ngeunaan kategori (grup produk, bungkusan, beurat, jeung sajabana) unggal produk, sarta tabél langganan - kota jeung wewengkon (alamat, TIN, rinci bank, jsb) unggal sahijina.
meja penjualan bakal dipaké ku urang engké pikeun ngasupkeun transaksi réngsé kana eta.
Lengkah 2. Jieun formulir Éntri data
Tangtosna, anjeun tiasa ngalebetkeun data penjualan langsung kana méja héjo penjualan, Tapi ieu teu salawasna merenah tur merlukeun penampilan kasalahan sarta typos alatan "faktor manusa". Ku alatan éta, leuwih sae pikeun nyieun formulir husus pikeun ngasupkeun data dina lambaran misah tina hal kawas kieu:
Dina sél B3, pikeun kéngingkeun tanggal-waktos anu diropéa ayeuna, paké fungsina The TDATA (AYEUNA). Lamun waktu teu diperlukeun, lajeng gantina The TDATA fungsi bisa dilarapkeun Dinten ieu (POE).
Dina sél B11, panggihan harga produk anu dipilih dina kolom katilu tabel pinter harga ngagunakeun fungsi VPR (VLOOKUP). Upami anjeun teu acan kantos mendakan éta, teras baca heula sareng tonton pidéo di dieu.
Dina sél B7, urang peryogi daptar turun sareng produk tina daptar harga. Pikeun ieu anjeun tiasa nganggo paréntah Data – Validasi Data (Data - Validasi), tangtukeun salaku konstrain daptar (Daptar) terus asup ka lapang sumber (Sumber) link kana kolom nami ti méja pinter urang harga:
Nya kitu, daptar turun-handap sareng klien didamel, tapi sumberna bakal langkung sempit:
=INDIRECT("Palanggan [Klién]")
fungsi TEU LANGSUNG (Henteu adil) diperlukeun, dina hal ieu, sabab Excel, hanjakalna, teu ngarti Tumbu langsung ka tabel pinter dina widang Sumber. Tapi link sarua "dibungkus" dina fungsi hiji TEU LANGSUNG dina waktos anu sareng, éta jalan kalawan bang a (langkung seueur ngeunaan ieu dina artikel ngeunaan nyieun daptar turun-handap kalawan eusi).
Lengkah 3. Nambahan makro éntri jualan
Saatos ngeusian formulir, anjeun kedah nambihan data anu diasupkeun kana tungtung méja penjualan. Nganggo tautan saderhana, kami bakal ngabentuk garis anu bakal ditambihan langsung di handap formulir:
Jelema. sél A20 bakal boga tumbu ka =B3, sél B20 bakal boga tumbu ka =B7, jeung saterusna.
Ayeuna hayu urang tambahkeun makro dasar 2-garis anu nyalin string anu dihasilkeun sareng nambihan kana tabel Penjualan. Jang ngalampahkeun ieu, pencét kombinasi Alt + F11 atawa tombol visual Basic tab pamekar (Pamekar). Upami tab ieu henteu katingali, teras aktipkeun heula dina setélan File - Pilihan - Setup Pita (File - Pilihan - Sesuaikeun Pita). Dina jandela editor Visual Basic nu muka, selapkeun modul kosong anyar ngaliwatan menu nu Selapkeun - Modul sareng lebetkeun kode makro kami di dinya:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Salin 'Salin garis data tina formulir n = Worksheets("Sales").Range("A100000").End(xlUp) . Baris 'nangtukeun jumlah baris panungtungan dina tabél. Lembar Kerja Penjualan("Penjualan").Sél(n + 1, 1).Tempelkeun Témpél Khusus:=xlPasteValues 'témpélkeun kana baris kosong Lembar Kerja salajengna("Bentuk Input").Range("B5,B7,B9"). ClearContents 'jelas subformulir tungtung Ayeuna urang tiasa nambihan tombol kana formulir urang pikeun ngajalankeun makro anu diciptakeun nganggo daptar lungsur Selapkeun tab pamekar (Pamekar - Selapkeun - Tombol):
Saatos anjeun ngagambar éta, nahan tombol beurit kénca, Excel bakal naroskeun anjeun makro anu anjeun peryogikeun - pilih makro kami. Tambah_Jual. Anjeun tiasa ngarobih téks dina tombol ku ngaklik katuhu kana éta sareng milih paréntahna Robah téks.
Ayeuna, saatos ngeusian formulir, anjeun ngan saukur tiasa klik tombol kami, sareng data anu diasupkeun bakal otomatis ditambahkeun kana tabél penjualan, lajeng formulir diberesihan pikeun ngasupkeun deal anyar.
Lengkah 4 Linking Tables
Saméméh nyieun laporan, hayu urang numbu tabel urang babarengan sangkan engké urang bisa gancang ngitung jualan dumasar wewengkon, konsumén, atawa kategori. Dina vérsi Excel anu langkung lami, ieu peryogi sababaraha fungsi. VPR (VLOOKUP) pikeun substituting harga, kategori, konsumén, kota, jeung sajabana kana tabél penjualan. Ieu merlukeun waktu jeung usaha ti kami, sarta ogé "dahar" loba sumber Excel. Dimimitian ku Excel 2013, sadayana tiasa dilaksanakeun langkung saderhana ku nyetél hubungan antara tabel.
Jang ngalampahkeun ieu, dina tab data (Kaping) malik Hubungan (Hubungan). Dina jandela nu nembongan, klik tombol nyiptakeun (anyar) sareng pilih tina daptar turun-handap tabel sareng nami kolom anu kedah aya hubunganana:
Hiji titik penting: tabel kudu dieusian dina urutan ieu, nyaéta tabel numbu (harga) teu kudu aya dina kolom konci (nami) duplikat produk, sakumaha eta kajadian dina tabél penjualan. Dina basa sejen, tabel pakait kedah salah sahiji nu bakal neangan data ngagunakeun VPRlamun éta dipaké.
Tangtu, tabel disambungkeun dina cara nu sarupa penjualan kalawan méja langganan ku kolom umum langganan:
Saatos nyetél tautan, jandela pikeun ngatur tautan tiasa ditutup; anjeun teu kudu ngulang prosedur ieu.
Lengkah 5. Urang ngawangun laporan ngagunakeun kasimpulan
Ayeuna, pikeun nganalisis penjualan sareng ngalacak dinamika prosés, hayu urang nyiptakeun, contona, sababaraha jinis laporan nganggo tabel pangsi. Setel sél aktip kana méja penjualan tur pilih tab dina pita Selapkeun - PivotTable (Selapkeun - Pivot Table). Dina jandela anu muka, Excel bakal naroskeun ka kami ngeunaan sumber data (ie table penjualan) jeung tempat pikeun unggah laporan (preferably dina lambaran anyar):
Hal penting nyaéta yén kotak centang kedah diaktipkeun Tambahkeun data ieu kana model data (Tambihkeun data kana Modél Data) di handap jandela supados Excel ngartos yen urang rék ngawangun laporan teu ukur dina tabel ayeuna, tapi ogé ngagunakeun sagala hubungan.
Sanggeus ngaklik OK panel bakal muncul dina satengah katuhu jandela Widang tabel pangsidimana klik link sadayaningali teu ngan hiji ayeuna, tapi sakabeh "tabel pinter" nu aya dina buku sakaligus. Teras, sapertos dina tabel pangsi klasik, anjeun ngan saukur tiasa nyered widang anu urang peryogikeun tina tabel anu aya hubunganana ka daérah éta. saringan, barisan, Stolbtsov or nilai - sareng Excel bakal langsung ngawangun laporan naon waé anu urang peryogikeun dina lambaran:
Tong hilap yén tabel pangsi kedah diropéa périodik (nalika data sumberna robih) ku ngaklik-leres sareng milih paréntahna. Apdet & Simpen (Refresh), sabab teu bisa ngalakukeun eta otomatis.
Ogé, ku milih sél mana waé dina kasimpulan sareng mencét tombol Bagan Pangsi (Bagan Pangsi) tab analisa (Analisis) or parameter (Pilihan) anjeun tiasa gancang visualize hasil diitung dina eta.
Lengkah 6. Eusian printables
Tugas has anu sanés tina pangkalan data naon waé nyaéta ngeusian otomatis tina rupa-rupa bentuk sareng bentuk anu dicitak (invoice, invoice, tindakan, jsb.). Kuring geus nulis ngeunaan salah sahiji cara pikeun ngalakukeun ieu. Di dieu urang nerapkeun, contona, ngeusian formulir ku nomer rekening:
Hal ieu dianggap yén dina sél C2 pamaké bakal ngasupkeun nomer (nomer baris dina tabel penjualan, kanyataanna), teras data anu urang peryogikeun ditarik nganggo fungsi anu parantos akrab VPR (VLOOKUP) sarta fitur Indéks (INDEKS).
- Kumaha ngagunakeun fungsi VLOOKUP pikeun milarian sareng milarian nilai
- Kumaha ngagentos VLOOKUP sareng fungsi INDEX sareng MATCH
- Ngeusian formulir sareng formulir otomatis sareng data tina tabél
- Nyiptakeun Laporan nganggo PivotTables