Περιεχόμενα
Όταν αναφέρουμε βάσεις δεδομένων (DB), το πρώτο πράγμα που έρχεται στο μυαλό, φυσικά, είναι κάθε είδους τσιτάτα όπως SQL, Oracle, 1C ή τουλάχιστον Access. Φυσικά, πρόκειται για πολύ ισχυρά (και ακριβά ως επί το πλείστον) προγράμματα που μπορούν να αυτοματοποιήσουν τη δουλειά μιας μεγάλης και πολύπλοκης εταιρείας με πολλά δεδομένα. Το πρόβλημα είναι ότι μερικές φορές τέτοια δύναμη απλά δεν χρειάζεται. Η επιχείρησή σας μπορεί να είναι μικρή και με σχετικά απλές επιχειρηματικές διαδικασίες, αλλά θέλετε επίσης να την αυτοματοποιήσετε. Και είναι για τις μικρές εταιρείες που αυτό είναι συχνά θέμα επιβίωσης.
Αρχικά, ας διατυπώσουμε το TOR. Στις περισσότερες περιπτώσεις, μια βάση δεδομένων για τη λογιστική, για παράδειγμα, τις κλασικές πωλήσεις θα πρέπει να είναι σε θέση:
- διατήρηση στους πίνακες πληροφορίες για αγαθά (τιμή), ολοκληρωμένες συναλλαγές και πελάτες και συνδέστε αυτούς τους πίνακες μεταξύ τους
- έχουν άνετα φόρμες εισαγωγής δεδομένα (με αναπτυσσόμενες λίστες κ.λπ.)
- συμπληρώνει αυτόματα κάποια δεδομένα έντυπα έντυπα (πληρωμές, λογαριασμοί κ.λπ.)
- εκδώσει τα απαραίτητα εκθέσεις να ελέγχει ολόκληρη την επιχειρηματική διαδικασία από τη σκοπιά του διαχειριστή
Το Microsoft Excel μπορεί να τα χειριστεί όλα αυτά με λίγη προσπάθεια. Ας προσπαθήσουμε να το εφαρμόσουμε αυτό.
Βήμα 1. Αρχικά δεδομένα με τη μορφή πινάκων
Θα αποθηκεύσουμε πληροφορίες σχετικά με προϊόντα, πωλήσεις και πελάτες σε τρεις πίνακες (στο ίδιο φύλλο ή σε διαφορετικούς - δεν έχει σημασία). Είναι θεμελιωδώς σημαντικό να τα μετατρέψετε σε «έξυπνα τραπέζια» με αυτόματο μέγεθος, ώστε να μην το σκέφτεστε στο μέλλον. Αυτό γίνεται με την εντολή Μορφοποίηση ως πίνακα αυτί Αρχική (Αρχική σελίδα — Μορφοποίηση ως πίνακα). Στην καρτέλα που εμφανίζεται στη συνέχεια Κατασκευαστής (Σχέδιο) δώστε στους πίνακες περιγραφικά ονόματα στο πεδίο Όνομα πίνακα για μεταγενέστερη χρήση:
Συνολικά, θα πρέπει να έχουμε τρία «έξυπνα τραπέζια»:
Λάβετε υπόψη ότι οι πίνακες ενδέχεται να περιέχουν πρόσθετα διευκρινιστικά δεδομένα. Έτσι, για παράδειγμα, το δικό μας Τιμήπεριέχει πρόσθετες πληροφορίες για την κατηγορία (ομάδα προϊόντων, συσκευασία, βάρος κ.λπ.) κάθε προϊόντος και τον πίνακα Πελάτης — πόλη και περιοχή (διεύθυνση, ΑΦΜ, τραπεζικά στοιχεία κ.λπ.) καθενός από αυτά.
Τραπέζι ΕΚΠΤΩΣΕΙΣ θα χρησιμοποιηθεί αργότερα από εμάς για την εισαγωγή ολοκληρωμένων συναλλαγών σε αυτό.
Βήμα 2. Δημιουργήστε μια φόρμα εισαγωγής δεδομένων
Φυσικά, μπορείτε να εισάγετε δεδομένα πωλήσεων απευθείας στον πράσινο πίνακα ΕΚΠΤΩΣΕΙΣ, αλλά αυτό δεν είναι πάντα βολικό και συνεπάγεται την εμφάνιση λαθών και τυπογραφικών σφαλμάτων λόγω του «ανθρώπινου παράγοντα». Επομένως, θα ήταν καλύτερο να δημιουργήσετε μια ειδική φόρμα για την εισαγωγή δεδομένων σε ένα ξεχωριστό φύλλο με κάτι σαν αυτό:
Στο κελί B3, για να λάβετε την ενημερωμένη τρέχουσα ημερομηνία-ώρα, χρησιμοποιήστε τη συνάρτηση Τα TDATA (ΤΩΡΑ). Εάν δεν χρειάζεται χρόνος, τότε αντ' αυτού Τα TDATA μπορεί να εφαρμοστεί η λειτουργία ΣΉΜΕΡΑ (ΣΗΜΕΡΑ).
Στο κελί B11, βρείτε την τιμή του επιλεγμένου προϊόντος στην τρίτη στήλη του έξυπνου πίνακα Τιμή χρησιμοποιώντας τη συνάρτηση VPR (VLOOKUP). Αν δεν το έχετε ξανασυναντήσει, τότε πρώτα διαβάστε και δείτε το βίντεο εδώ.
Στο κελί B7, χρειαζόμαστε μια αναπτυσσόμενη λίστα με προϊόντα από τον τιμοκατάλογο. Για αυτό μπορείτε να χρησιμοποιήσετε την εντολή Δεδομένα – Επικύρωση δεδομένων (Δεδομένα — Επικύρωση), καθορίστε ως περιορισμό Λίστα (Λίστα) και μετά εισάγετε στο πεδίο Πηγή (Πηγή) σύνδεσμος προς στήλη Όνομα από το έξυπνο τραπέζι μας Τιμή:
Ομοίως, δημιουργείται μια αναπτυσσόμενη λίστα με πελάτες, αλλά η πηγή θα είναι στενότερη:
=INDIRECT("Πελάτες[Πελάτης]")
Λειτουργία ΕΜΜΕΣΟΣ (ΕΜΜΕΣΟΣ) χρειάζεται, σε αυτήν την περίπτωση, επειδή το Excel, δυστυχώς, δεν κατανοεί απευθείας συνδέσμους προς έξυπνους πίνακες στο πεδίο Πηγή. Αλλά ο ίδιος σύνδεσμος «τυλιγμένος» σε μια συνάρτηση ΕΜΜΕΣΟΣ Ταυτόχρονα, λειτουργεί με μεγάλη ταχύτητα (περισσότερα σχετικά με αυτό ήταν στο άρθρο σχετικά με τη δημιουργία αναπτυσσόμενων λιστών με περιεχόμενο).
Βήμα 3. Προσθήκη μακροεντολής εισαγωγής πωλήσεων
Αφού συμπληρώσετε τη φόρμα, πρέπει να προσθέσετε τα δεδομένα που εισαγάγατε σε αυτήν στο τέλος του πίνακα ΕΚΠΤΩΣΕΙΣ. Χρησιμοποιώντας απλούς συνδέσμους, θα σχηματίσουμε μια γραμμή που θα προστεθεί ακριβώς κάτω από τη φόρμα:
Εκείνοι. Το κελί A20 θα έχει έναν σύνδεσμο προς =B3, το κελί B20 θα έχει έναν σύνδεσμο προς το =B7 και ούτω καθεξής.
Τώρα ας προσθέσουμε μια στοιχειώδη μακροεντολή 2 γραμμών που αντιγράφει τη συμβολοσειρά που δημιουργείται και την προσθέτει στον πίνακα Πωλήσεις. Για να το κάνετε αυτό, πατήστε το συνδυασμό Alt + F11 ή κουμπί Visual Basic αυτί προγραμματιστή (Προγραμματιστής). Εάν αυτή η καρτέλα δεν είναι ορατή, ενεργοποιήστε την πρώτα στις ρυθμίσεις Αρχείο – Επιλογές – Ρύθμιση κορδέλας (Αρχείο — Επιλογές — Προσαρμογή κορδέλας). Στο παράθυρο του προγράμματος επεξεργασίας της Visual Basic που ανοίγει, εισαγάγετε μια νέα κενή μονάδα μέσω του μενού Εισαγωγή – Ενότητα και εισάγετε τον κωδικό μακροεντολής μας εκεί:
Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy the data line from the form n = Worksheets("Sales").Range("A100000").End(xlUp) . Η σειρά καθορίζει τον αριθμό της τελευταίας σειράς στον πίνακα. Φύλλα εργασίας πωλήσεων ("Πωλήσεις"). Κελιά (n + 1, 1).Επικόλληση Ειδικής Επικόλλησης:=xlPasteValues 'επικόλληση στην επόμενη κενή γραμμή Φύλλα εργασίας ("Φόρμα εισαγωγής"). Εύρος ("B5,B7,B9"). ClearContents υποφόρμα καθαρού τέλους Τώρα μπορούμε να προσθέσουμε ένα κουμπί στη φόρμα μας για να εκτελέσουμε τη μακροεντολή που δημιουργήσαμε χρησιμοποιώντας την αναπτυσσόμενη λίστα Κύριο θέμα αυτί προγραμματιστή (Προγραμματιστής — Εισαγωγή — Κουμπί):
Αφού τη σχεδιάσετε, κρατώντας πατημένο το αριστερό κουμπί του ποντικιού, το Excel θα σας ρωτήσει ποια μακροεντολή πρέπει να της αντιστοιχίσετε - επιλέξτε τη μακροεντολή μας Προσθήκη_Πώλησης. Μπορείτε να αλλάξετε το κείμενο σε ένα κουμπί κάνοντας δεξί κλικ πάνω του και επιλέγοντας την εντολή Αλλαγή κειμένου.
Τώρα, αφού συμπληρώσετε τη φόρμα, μπορείτε απλά να κάνετε κλικ στο κουμπί μας και τα δεδομένα που εισάγατε θα προστεθούν αυτόματα στον πίνακα ΕΚΠΤΩΣΕΙΣ, και, στη συνέχεια, η φόρμα διαγράφεται για να εισαχθεί μια νέα συμφωνία.
Βήμα 4 Σύνδεση πινάκων
Πριν δημιουργήσουμε την αναφορά, ας συνδέσουμε τους πίνακές μας, ώστε αργότερα να μπορούμε να υπολογίσουμε γρήγορα τις πωλήσεις ανά περιοχή, πελάτη ή κατηγορία. Σε παλαιότερες εκδόσεις του Excel, αυτό θα απαιτούσε τη χρήση πολλών συναρτήσεων. VPR (VLOOKUP) για την αντικατάσταση τιμών, κατηγοριών, πελατών, πόλεων κ.λπ. στον πίνακα ΕΚΠΤΩΣΕΙΣ. Αυτό απαιτεί χρόνο και προσπάθεια από εμάς, και επίσης «τρώει» πολλούς πόρους του Excel. Ξεκινώντας με το Excel 2013, τα πάντα μπορούν να υλοποιηθούν πολύ πιο απλά με τη δημιουργία σχέσεων μεταξύ πινάκων.
Για να το κάνετε αυτό, στην καρτέλα ημερομηνία (Ημερομηνία) κλικ Σχέσεις (Συγγένειες). Στο παράθυρο που εμφανίζεται, κάντε κλικ στο κουμπί Δημιουργία (νέος) και επιλέξτε από τις αναπτυσσόμενες λίστες τους πίνακες και τα ονόματα στηλών με τα οποία θα πρέπει να συσχετιστούν:
Ένα σημαντικό σημείο: οι πίνακες πρέπει να προσδιορίζονται με αυτή τη σειρά, δηλαδή συνδεδεμένο πίνακα (Τιμή) δεν πρέπει να περιέχει στη στήλη κλειδιού (Όνομα) διπλότυπα προϊόντα, όπως συμβαίνει στον πίνακα ΕΚΠΤΩΣΕΙΣ. Με άλλα λόγια, ο συσχετισμένος πίνακας πρέπει να είναι αυτός στον οποίο θα αναζητούσατε δεδομένα χρησιμοποιώντας VPRαν χρησιμοποιούνταν.
Φυσικά, ο πίνακας συνδέεται με παρόμοιο τρόπο ΕΚΠΤΩΣΕΙΣ με τραπέζι Πελάτης κατά κοινή στήλη Πελάτης:
Μετά τη ρύθμιση των συνδέσμων, το παράθυρο διαχείρισης συνδέσμων μπορεί να κλείσει. δεν χρειάζεται να επαναλάβετε αυτή τη διαδικασία.
Βήμα 5. Δημιουργούμε αναφορές χρησιμοποιώντας τη σύνοψη
Τώρα, για να αναλύσουμε τις πωλήσεις και να παρακολουθήσουμε τη δυναμική της διαδικασίας, ας δημιουργήσουμε, για παράδειγμα, κάποιο είδος αναφοράς χρησιμοποιώντας έναν συγκεντρωτικό πίνακα. Ορισμός ενεργού κελιού σε πίνακα ΕΚΠΤΩΣΕΙΣ και επιλέξτε την καρτέλα στην κορδέλα Εισαγωγή – Συγκεντρωτικός Πίνακας (Εισαγωγή — Συγκεντρωτικός πίνακας). Στο παράθυρο που ανοίγει, το Excel θα μας ρωτήσει για την πηγή δεδομένων (π.χ. πίνακα ΕΚΠΤΩΣΕΙΣ) και ένα μέρος για τη μεταφόρτωση της αναφοράς (κατά προτίμηση σε νέο φύλλο):
Το ζωτικό σημείο είναι ότι είναι απαραίτητο να ενεργοποιήσετε το πλαίσιο ελέγχου Προσθέστε αυτά τα δεδομένα στο μοντέλο δεδομένων (Προσθήκη δεδομένων στο μοντέλο δεδομένων) στο κάτω μέρος του παραθύρου, ώστε το Excel να καταλάβει ότι θέλουμε να δημιουργήσουμε μια αναφορά όχι μόνο στον τρέχοντα πίνακα, αλλά και να χρησιμοποιήσουμε όλες τις σχέσεις.
Μετά το κλικ OK θα εμφανιστεί ένας πίνακας στο δεξί μισό του παραθύρου Πεδία συγκεντρωτικού πίνακαπού να κάνετε κλικ στον σύνδεσμο Όλαγια να δείτε όχι μόνο το τρέχον, αλλά όλα τα «έξυπνα τραπέζια» που υπάρχουν στο βιβλίο ταυτόχρονα. Και στη συνέχεια, όπως στον κλασικό πίνακα περιστροφής, μπορείτε απλά να σύρετε τα πεδία που χρειαζόμαστε από οποιονδήποτε σχετικό πίνακα στην περιοχή Φίλτρα, Γραμμές, Στολμπτσόφ or Αξίες – και το Excel θα δημιουργήσει αμέσως οποιαδήποτε αναφορά χρειαζόμαστε στο φύλλο:
Μην ξεχνάτε ότι ο συγκεντρωτικός πίνακας πρέπει να ενημερώνεται περιοδικά (όταν αλλάζουν τα δεδομένα προέλευσης) κάνοντας δεξί κλικ πάνω του και επιλέγοντας την εντολή Ενημέρωση & Αποθήκευση (Φρεσκάρω), γιατί δεν μπορεί να το κάνει αυτόματα.
Επίσης, επιλέγοντας οποιοδήποτε κελί στη σύνοψη και πατώντας το κουμπί Συγκεντρωτικός πίνακας (Συγκεντρωτικό γράφημα) αυτί Ανάλυση (Ανάλυση) or παράμετροι (Επιλογές) μπορείτε να οπτικοποιήσετε γρήγορα τα αποτελέσματα που υπολογίζονται σε αυτό.
Βήμα 6. Συμπληρώστε τα εκτυπώσιμα
Μια άλλη τυπική εργασία κάθε βάσης δεδομένων είναι η αυτόματη συμπλήρωση διαφόρων έντυπων εντύπων και εντύπων (τιμολόγια, τιμολόγια, πράξεις κ.λπ.). Έχω ήδη γράψει για έναν από τους τρόπους για να γίνει αυτό. Εδώ υλοποιούμε, για παράδειγμα, τη συμπλήρωση της φόρμας με αριθμό λογαριασμού:
Υποτίθεται ότι στο κελί C2 ο χρήστης θα εισαγάγει έναν αριθμό (αριθμός σειράς στον πίνακα ΕΚΠΤΩΣΕΙΣ, στην πραγματικότητα), και στη συνέχεια ανασύρονται τα δεδομένα που χρειαζόμαστε χρησιμοποιώντας την ήδη γνωστή λειτουργία VPR (VLOOKUP) και χαρακτηριστικά ΔΕΊΚΤΗΣ (ΔΕΙΚΤΗΣ).
- Πώς να χρησιμοποιήσετε τη συνάρτηση VLOOKUP για αναζήτηση και αναζήτηση τιμών
- Πώς να αντικαταστήσετε το VLOOKUP με τις συναρτήσεις INDEX και MATCH
- Αυτόματη συμπλήρωση εντύπων και εντύπων με δεδομένα από τον πίνακα
- Δημιουργία αναφορών με Συγκεντρωτικούς Πίνακες