Konten
Ketika menyebut database (DB), hal pertama yang terlintas dalam pikiran, tentu saja, adalah segala macam kata kunci seperti SQL, Oracle, 1C, atau setidaknya Access. Tentu saja, ini adalah program yang sangat kuat (dan sebagian besar mahal) yang dapat mengotomatiskan pekerjaan perusahaan besar dan kompleks dengan banyak data. Masalahnya adalah kadang-kadang kekuatan seperti itu tidak diperlukan. Bisnis Anda mungkin kecil dan dengan proses bisnis yang relatif sederhana, tetapi Anda juga ingin mengotomatiskannya. Dan bagi perusahaan kecil inilah yang sering menjadi masalah kelangsungan hidup.
Untuk memulainya, mari kita merumuskan TOR. Dalam kebanyakan kasus, database untuk akuntansi, misalnya, penjualan klasik harus dapat:
- menjaga di tabel informasi barang (harga), transaksi dan pelanggan yang diselesaikan dan hubungkan tabel ini satu sama lain
- nyaman formulir masukan data (dengan daftar drop-down, dll.)
- otomatis mengisi beberapa data formulir tercetak (pembayaran, tagihan, dll)
- mengeluarkan yang diperlukan laporan untuk mengontrol seluruh proses bisnis dari sudut pandang manajer
Microsoft Excel dapat menangani semua ini dengan sedikit usaha. Mari kita coba menerapkan ini.
Langkah 1. Data awal berupa tabel
Kami akan menyimpan informasi tentang produk, penjualan, dan pelanggan dalam tiga tabel (pada lembar yang sama atau berbeda – tidak masalah). Sangat penting untuk mengubahnya menjadi "tabel pintar" dengan ukuran otomatis, agar tidak memikirkannya di masa depan. Ini dilakukan dengan perintah Format sebagai tabel tab Beranda (Beranda — Format sebagai Tabel). Pada tab yang kemudian muncul Pembina (Rancangan) berikan nama deskriptif tabel di bidang Nama tabel untuk digunakan nanti:
Secara total, kita harus mendapatkan tiga "tabel pintar":
Harap dicatat bahwa tabel mungkin berisi data klarifikasi tambahan. Jadi, misalnya, kami Hargaberisi informasi tambahan tentang kategori (kelompok produk, kemasan, berat, dll.) dari setiap produk, dan tabel Pelanggan — kota dan wilayah (alamat, NPWP, detail bank, dll.) masing-masing.
tabel Penjualan akan digunakan oleh kami nanti untuk memasukkan transaksi yang telah selesai ke dalamnya.
Langkah 2. Buat formulir entri data
Tentu saja, Anda dapat memasukkan data penjualan langsung ke tabel hijau Penjualan, tetapi ini tidak selalu nyaman dan memerlukan munculnya kesalahan dan kesalahan ketik karena "faktor manusia". Oleh karena itu, akan lebih baik untuk membuat formulir khusus untuk memasukkan data pada lembar terpisah seperti ini:
Di sel B3, untuk mendapatkan tanggal-waktu terkini, gunakan fungsi TDATA (SEKARANG). Jika waktu tidak diperlukan, maka sebagai gantinya TDATA fungsi dapat diterapkan HARI INI (HARI INI).
Di sel B11, temukan harga produk yang dipilih di kolom ketiga tabel pintar Harga menggunakan fungsi VPR (VLOOKUP). Jika Anda belum pernah mengalaminya, maka baca dan tonton dulu videonya di sini.
Di sel B7, kita memerlukan daftar dropdown dengan produk dari daftar harga. Untuk ini, Anda dapat menggunakan perintah Data – Validasi Data (Validasi data), tentukan sebagai kendala Daftar (Daftar) dan kemudian masuk di lapangan sumber (Sumber) tautan ke kolom Nama dari meja pintar kami Harga:
Demikian pula, daftar tarik-turun dengan klien dibuat, tetapi sumbernya akan lebih sempit:
=INDIRECT("Pelanggan[Klien]")
fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) diperlukan, dalam hal ini, karena Excel, sayangnya, tidak memahami tautan langsung ke tabel cerdas di bidang Sumber. Tetapi tautan yang sama "dibungkus" dalam suatu fungsi TIDAK LANGSUNG pada saat yang sama, ini berfungsi dengan baik (lebih lanjut tentang ini ada di artikel tentang membuat daftar drop-down dengan konten).
Langkah 3. Menambahkan makro entri penjualan
Setelah mengisi formulir, Anda perlu menambahkan data yang dimasukkan ke dalamnya hingga akhir tabel Penjualan. Menggunakan tautan sederhana, kami akan membentuk garis yang akan ditambahkan tepat di bawah formulir:
Itu. sel A20 akan memiliki tautan ke =B3, sel B20 akan memiliki tautan ke =B7, dan seterusnya.
Sekarang mari tambahkan makro dasar 2 baris yang menyalin string yang dihasilkan dan menambahkannya ke tabel Penjualan. Untuk melakukan ini, tekan kombinasi Alt + F11 atau tombol Visual Basic tab pembangun (Pengembang). Jika tab ini tidak terlihat, maka aktifkan terlebih dahulu di pengaturan File – Opsi – Pengaturan Pita (File — Opsi — Kustomisasi Pita). Di jendela editor Visual Basic yang terbuka, masukkan modul kosong baru melalui menu Sisipkan – Modul dan masukkan kode makro kami di sana:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy baris data dari form n = Worksheets("Sales").Range("A100000").End(xlUp) . Baris 'menentukan jumlah baris terakhir dalam tabel. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues 'tempel ke baris kosong berikutnya Worksheets("Input Form").Range("B5,B7,B9"). ClearContents 'jelaskan subformulir akhir Sekarang kita dapat menambahkan tombol ke formulir kita untuk menjalankan makro yang dibuat menggunakan daftar dropdown Menyisipkan tab pembangun (Pengembang — Sisipkan — Tombol):
Setelah Anda menggambarnya, menahan tombol kiri mouse, Excel akan menanyakan makro mana yang perlu Anda tetapkan - pilih makro kami Tambah_Jual. Anda dapat mengubah teks pada tombol dengan mengklik kanan padanya dan memilih perintah Ubah teks.
Sekarang, setelah mengisi formulir, Anda cukup mengklik tombol kami, dan data yang dimasukkan akan secara otomatis ditambahkan ke tabel Penjualan, lalu formulir dihapus untuk memasukkan kesepakatan baru.
Langkah 4 Menautkan Tabel
Sebelum membuat laporan, mari kita hubungkan tabel-tabel kita bersama-sama sehingga nanti kita dapat dengan cepat menghitung penjualan berdasarkan wilayah, pelanggan, atau kategori. Di versi Excel yang lebih lama, ini akan membutuhkan penggunaan beberapa fungsi. VPR (VLOOKUP) untuk mengganti harga, kategori, pelanggan, kota, dll. ke tabel Penjualan. Ini membutuhkan waktu dan usaha dari kami, dan juga "memakan" banyak sumber daya Excel. Dimulai dengan Excel 2013, semuanya dapat diimplementasikan dengan lebih sederhana dengan mengatur hubungan antar tabel.
Untuk melakukan ini, pada tab Data (Tanggal) Klik Hubungan (Hubungan). Di jendela yang muncul, klik tombol membuat (baru) dan pilih dari daftar drop-down tabel dan nama kolom yang harus dikaitkan:
Poin penting: tabel harus ditentukan dalam urutan ini, yaitu tabel tertaut (Harga) tidak boleh berisi kolom kunci (Nama) duplikat produk, seperti yang terjadi di tabel Penjualan. Dengan kata lain, tabel terkait harus tabel di mana Anda akan mencari data menggunakan VPRjika itu digunakan.
Tentu saja, tabel terhubung dengan cara yang sama Penjualan dengan meja Pelanggan menurut kolom umum Pelanggan:
Setelah mengatur tautan, jendela untuk mengelola tautan dapat ditutup; Anda tidak perlu mengulangi prosedur ini.
Langkah 5. Kami membuat laporan menggunakan ringkasan
Sekarang, untuk menganalisis penjualan dan melacak dinamika proses, mari kita buat, misalnya, beberapa jenis laporan menggunakan tabel pivot. Setel sel aktif ke tabel Penjualan dan pilih tab pada pita Sisipkan – PivotTable (Sisipkan — Tabel Pivot). Di jendela yang terbuka, Excel akan bertanya kepada kami tentang sumber data (yaitu tabel Penjualan) dan tempat untuk mengunggah laporan (sebaiknya pada lembar baru):
Poin pentingnya adalah perlu mengaktifkan kotak centang Tambahkan data ini ke model data (Tambahkan data ke Model Data) di bagian bawah jendela sehingga Excel memahami bahwa kami ingin membuat laporan tidak hanya pada tabel saat ini, tetapi juga menggunakan semua hubungan.
Setelah mengklik OK sebuah panel akan muncul di bagian kanan jendela Bidang tabel pivotklik link nya dimana Semuauntuk melihat tidak hanya yang sekarang, tetapi semua "tabel pintar" yang ada di buku sekaligus. Dan kemudian, seperti pada tabel pivot klasik, Anda cukup menyeret bidang yang kita butuhkan dari tabel terkait ke area tersebut Filter, Baris, Stolbtsov or Nilai - Nilai – dan Excel akan langsung membuat laporan apa pun yang kita butuhkan di lembar:
Jangan lupa bahwa tabel pivot perlu diperbarui secara berkala (ketika data sumber berubah) dengan mengklik kanan padanya dan memilih perintah Perbarui & Simpan (Menyegarkan), karena tidak dapat melakukannya secara otomatis.
Juga, dengan memilih sel mana saja dalam ringkasan dan menekan tombol Bagan Pivot (Bagan Pivot) tab Analisis (Analisis) or parameter (Pilihan) Anda dapat dengan cepat memvisualisasikan hasil yang dihitung di dalamnya.
Langkah 6. Isi printables
Tugas khas lainnya dari basis data apa pun adalah pengisian otomatis berbagai formulir dan formulir tercetak (faktur, faktur, akta, dll.). Saya sudah menulis tentang salah satu cara untuk melakukan ini. Di sini kami menerapkan, misalnya, mengisi formulir dengan nomor rekening:
Diasumsikan bahwa di sel C2 pengguna akan memasukkan nomor (nomor baris dalam tabel Penjualan, sebenarnya), dan kemudian data yang kita butuhkan ditarik menggunakan fungsi yang sudah dikenal VPR (VLOOKUP) dan fitur INDEKS (INDEKS).
- Cara menggunakan fungsi VLOOKUP untuk mencari dan mencari nilai
- Bagaimana cara mengganti VLOOKUP dengan fungsi INDEX dan MATCH
- Pengisian formulir dan formulir otomatis dengan data dari tabel
- Membuat Laporan dengan PivotTable