Περιεχόμενα
Οι συγκεντρωτικοί πίνακες είναι καλοί για όλους – υπολογίζουν γρήγορα και διαμορφώνονται με ευελιξία και η σχεδίαση μπορεί να περιτυλιχθεί κομψά σε αυτά, εάν απαιτείται. Υπάρχουν όμως και μερικές μύγες στην αλοιφή, συγκεκριμένα, η αδυναμία δημιουργίας μιας περίληψης, όπου η περιοχή τιμών δεν πρέπει να περιέχει αριθμούς, αλλά κείμενο.
Ας προσπαθήσουμε να ξεπεράσουμε αυτόν τον περιορισμό και να καταλήξουμε σε ένα «δυο δεκανίκια» σε μια παρόμοια κατάσταση.
Ας υποθέσουμε ότι η εταιρεία μας μεταφέρει τα προϊόντα της σε κοντέινερ σε πολλές πόλεις της Χώρας μας και του Καζακστάν. Τα κοντέινερ αποστέλλονται όχι περισσότερο από μία φορά το μήνα. Κάθε δοχείο έχει έναν αλφαριθμητικό αριθμό. Ως αρχικά δεδομένα, υπάρχει ένας τυπικός πίνακας με παραδόσεις, από τον οποίο πρέπει να κάνετε κάποιο είδος περίληψης για να δείτε ξεκάθαρα τον αριθμό των κοντέινερ που αποστέλλονται σε κάθε πόλη και κάθε μήνα:
Για ευκολία, ας κάνουμε τον πίνακα με τα αρχικά δεδομένα "έξυπνο" εκ των προτέρων χρησιμοποιώντας την εντολή Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα) και δώσε της ένα όνομα Παραδόσεις αυτί Κατασκευαστής (Σχέδιο). Στο μέλλον, αυτό θα απλοποιήσει τη ζωή, γιατί. θα είναι δυνατή η χρήση του ονόματος του πίνακα και των στηλών του απευθείας στους τύπους.
Μέθοδος 1. Ο ευκολότερος – χρησιμοποιήστε το Power Query
Το Power Query είναι ένα εξαιρετικά ισχυρό εργαλείο για τη φόρτωση και τη μετατροπή δεδομένων στο Excel. Αυτό το πρόσθετο έχει ενσωματωθεί στο Excel από προεπιλογή από το 2016. Εάν έχετε Excel 2010 ή 2013, μπορείτε να το κατεβάσετε και να το εγκαταστήσετε ξεχωριστά (εντελώς δωρεάν).
Την όλη διαδικασία, για λόγους σαφήνειας, την ανέλυσα βήμα βήμα στο παρακάτω βίντεο:
Εάν δεν είναι δυνατό να χρησιμοποιήσετε το Power Query, τότε μπορείτε να ακολουθήσετε άλλους τρόπους – μέσω ενός συγκεντρωτικού πίνακα ή τύπων.
Μέθοδος 2. Βοηθητική περίληψη
Ας προσθέσουμε μια ακόμη στήλη στον αρχικό μας πίνακα, όπου χρησιμοποιώντας έναν απλό τύπο υπολογίζουμε τον αριθμό κάθε γραμμής στον πίνακα:
Προφανώς, χρειάζεται -1, γιατί έχουμε μια κεφαλίδα μιας γραμμής στον πίνακα μας. Εάν ο πίνακάς σας δεν βρίσκεται στην αρχή του φύλλου, τότε μπορείτε να χρησιμοποιήσετε έναν ελαφρώς πιο περίπλοκο, αλλά καθολικό τύπο που υπολογίζει τη διαφορά στους αριθμούς της τρέχουσας σειράς και της κεφαλίδας του πίνακα:
Τώρα, με τυπικό τρόπο, θα δημιουργήσουμε έναν πίνακα περιστροφής του επιθυμητού τύπου με βάση τα δεδομένα μας, αλλά στο πεδίο τιμής θα αποθέσουμε το πεδίο Αριθμός γραμμής αντί για αυτό που θέλουμε δοχείο:
Δεδομένου ότι δεν έχουμε πολλά κοντέινερ στην ίδια πόλη τον ίδιο μήνα, η σύνοψή μας, στην πραγματικότητα, δεν θα αναφέρει την ποσότητα, αλλά τους αριθμούς γραμμής των κοντέινερ που χρειαζόμαστε.
Επιπλέον, μπορείτε να απενεργοποιήσετε τα γενικά και τα υποσύνολα στην καρτέλα Κατασκευαστής – Γενικά σύνολα и Υποσύνολα (Σχεδίαση — Μεγάλα σύνολα, Υποσύνολα) και στο ίδιο σημείο αλλάξτε τη σύνοψη σε μια πιο βολική διάταξη πίνακα με το κουμπί Αναφορά μακέτα (Διάταξη αναφοράς).
Έτσι, βρισκόμαστε ήδη στα μισά του δρόμου προς το αποτέλεσμα: έχουμε έναν πίνακα όπου, στη διασταύρωση της πόλης και του μήνα, υπάρχει ένας αριθμός σειράς στον πίνακα πηγής, όπου βρίσκεται ο κωδικός κοντέινερ που χρειαζόμαστε.
Τώρα ας αντιγράψουμε τη σύνοψη (στο ίδιο φύλλο ή σε άλλο) και ας την επικολλήσουμε ως τιμές και, στη συνέχεια, εισάγουμε τον τύπο μας στην περιοχή τιμών, η οποία θα εξαγάγει τον κωδικό του κοντέινερ με βάση τον αριθμό γραμμής που βρίσκεται στη σύνοψη:
Λειτουργία IF (ΑΝ), σε αυτήν την περίπτωση, ελέγχει ότι το επόμενο κελί στη σύνοψη δεν είναι κενό. Εάν είναι κενό, τότε βγάζετε μια κενή συμβολοσειρά κειμένου "", δηλαδή αφήστε το κελί κενό. Εάν δεν είναι κενό, τότε εξαγάγετε από τη στήλη Δοχείο πίνακας πηγής Παραδόσεις περιεχόμενο κελιού ανά αριθμό σειράς χρησιμοποιώντας τη λειτουργία ΔΕΊΚΤΗΣ (ΔΕΙΚΤΗΣ).
Ίσως το μόνο όχι πολύ προφανές σημείο εδώ είναι η διπλή λέξη Δοχείο στον τύπο. Μια τόσο περίεργη μορφή γραφής:
Προμήθειες[[Δοχείο]:[Δοχείο]]
… χρειάζεται μόνο για αναφορά στη στήλη Δοχείο ήταν απόλυτη (όπως μια αναφορά με σύμβολα $ για συνηθισμένους «μη έξυπνους» πίνακες) και δεν γλίστρησε σε γειτονικές στήλες κατά την αντιγραφή του τύπου μας προς τα δεξιά.
Στο μέλλον, κατά την αλλαγή των δεδομένων στον πίνακα προέλευσης Παραδόσεις, πρέπει να θυμόμαστε να ενημερώσουμε τη βοηθητική μας σύνοψη με αριθμούς γραμμών κάνοντας δεξί κλικ πάνω της και επιλέγοντας την εντολή Ενημέρωση & Αποθήκευση (Φρεσκάρω).
Μέθοδος 3. Φόρμουλες
Αυτή η μέθοδος δεν απαιτεί τη δημιουργία ενδιάμεσου πίνακα περιστροφής και μη αυτόματη ενημέρωση, αλλά χρησιμοποιεί το «βαρύ όπλο» του Excel - τη συνάρτηση SUMMESLIMN (SUMIFS). Αντί να αναζητάτε τους αριθμούς σειρών σε μια περίληψη, μπορείτε να τους υπολογίσετε χρησιμοποιώντας αυτόν τον τύπο:
Με κάποιο εξωτερικό όγκο, στην πραγματικότητα, αυτή είναι μια τυπική περίπτωση χρήσης για τη λειτουργία επιλεκτικής άθροισης SUMMESLIMNA που αθροίζει τους αριθμούς σειρών για τη δεδομένη πόλη και μήνα. Και πάλι, δεδομένου ότι δεν έχουμε πολλά κοντέινερ στην ίδια πόλη τον ίδιο μήνα, η λειτουργία μας στην πραγματικότητα δεν θα δώσει το ποσό, αλλά τον ίδιο τον αριθμό γραμμής. Και μετά η συνάρτηση που είναι ήδη γνωστή από την προηγούμενη μέθοδο ΔΕΊΚΤΗΣ Μπορείτε επίσης να εξαγάγετε κωδικούς κοντέινερ:
Φυσικά, σε αυτήν την περίπτωση, δεν χρειάζεται πλέον να σκέφτεστε την ενημέρωση της περίληψης, αλλά σε μεγάλους πίνακες, η συνάρτηση ΣΟΥΜΜΕΣΛΙ μπορεί να είναι αισθητά αργή. Στη συνέχεια, θα πρέπει να απενεργοποιήσετε την αυτόματη ενημέρωση τύπων ή να χρησιμοποιήσετε την πρώτη μέθοδο - έναν συγκεντρωτικό πίνακα.
Εάν η εμφάνιση της σύνοψης δεν είναι πολύ κατάλληλη για την αναφορά σας, τότε μπορείτε να εξαγάγετε τους αριθμούς σειρών από αυτήν στον τελικό πίνακα όχι απευθείας, όπως κάναμε, αλλά χρησιμοποιώντας τη συνάρτηση GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Πώς να το κάνετε αυτό μπορείτε να βρείτε εδώ.
- Πώς να δημιουργήσετε μια αναφορά χρησιμοποιώντας έναν συγκεντρωτικό πίνακα
- Πώς να ρυθμίσετε τους υπολογισμούς σε συγκεντρωτικούς πίνακες
- Επιλεκτική καταμέτρηση με SUMIFS, COUNTIFS κ.λπ.