ΜΟΝΑΔΙΚΟ από στήλες που δεν γειτνιάζουν - Συμβουλές για το Excel

Τις προάλλες, επρόκειτο να δημιουργήσω έναν μοναδικό συνδυασμό δύο μη γειτονικών στηλών στο Excel. Συνήθως το κάνω αυτό με το Remove Duplicates ή το Advanced Filter, αλλά σκέφτηκα ότι θα προσπαθούσα να το κάνω με τη νέα ΜΟΝΑΔΙΚΗ συνάρτηση που έρχεται στο Office 365 το 2019. Δοκίμασα πολλές ιδέες και καμία δεν θα λειτουργούσε. Έτσι, πήγα στον πλοίαρχο των Dynamic Arrays, Joe McDaid, για βοήθεια. Η απάντηση είναι πολύ ωραία και είμαι βέβαιος ότι θα την ξεχάσω, γι 'αυτό την τεκμηριώνω για εσάς και για μένα. Είμαι βέβαιος, δύο χρόνια από τώρα, θα κάνω στο Google πώς να το κάνει αυτό και θα συνειδητοποιήσω "Ω, κοίτα! Είμαι αυτός που έγραψε το άρθρο για αυτό!"

Πριν φτάσετε στη λειτουργία ΜΟΝΑΔΙΚΗ, ρίξτε μια ματιά σε αυτό που προσπαθώ να κάνω. Θέλω κάθε μοναδικό συνδυασμό Πωλήσεων από τη στήλη Β και Προϊόν από τη στήλη Γ. Κανονικά, θα ακολουθούσα τα εξής βήματα:

Αντιγράψτε τις επικεφαλίδες από B1 και D1 σε μια κενή ενότητα του φύλλου εργασίας
Από το B1, επιλέξτε Data, Filter, Advanced
Στο παράθυρο διαλόγου Advanced Filter, επιλέξτε Αντιγραφή σε νέα θέση
Καθορίστε τους τίτλους από το Βήμα 1 ως το εύρος εξόδου
Επιλέξτε το πλαίσιο μόνο για μοναδικές τιμές
Κάντε κλικ στο OK

Αντιγράψτε τις δύο επικεφαλίδες σε μια κενή ενότητα που γίνεται το εύρος εξόδου

Το αποτέλεσμα είναι κάθε μοναδικός συνδυασμός των δύο πεδίων. Σημειώστε ότι το Advanced Filter δεν ταξινομεί τα στοιχεία - εμφανίζονται στην αρχική σειρά.

Η απόκτηση μιας μοναδικής λίστας είναι μια από τις αγαπημένες μου χρήσεις για το Advanced Filter

Αυτή η διαδικασία έγινε ευκολότερη στο Excel 2010 χάρη στην εντολή Remove Duplicates στην καρτέλα Δεδομένα της Κορδέλας. Ακολουθήστε αυτά τα βήματα:

Επιλέξτε B1: D227 και Ctrl + C για αντιγραφή
Επικόλληση σε μια κενή ενότητα του φύλλου εργασίας.

Δημιουργήστε ένα αντίγραφο των δεδομένων, καθώς το Remove Duplicates είναι καταστροφικό
Επιλέξτε Δεδομένα, Κατάργηση διπλότυπων
Στο παράθυρο διαλόγου Remove Duplicates, αποεπιλέξτε την ημερομηνία. Αυτό λέει στο Excel να κοιτάζει μόνο το Rep και το προϊόν.
Κάντε κλικ στο OK

Πείτε στο Κατάργηση διπλότυπων για να λάβετε υπόψη μόνο το Rep και την ημερομηνία

Τα αποτελέσματα είναι σχεδόν τέλεια - απλώς πρέπει να διαγράψετε τη στήλη Ημερομηνία.

Διαγράψτε την επιπλέον στήλη

Η ερώτηση: Υπάρχει κάποιος τρόπος η ΜΟΝΑΔΙΚΗ συνάρτηση να κοιτάζει μόνο τις στήλες B & D; (Εάν δεν έχετε δει τη νέα λειτουργία ΜΟΝΑΔΙΚΗ, διαβάστε: ΜΟΝΑΔΙΚΗ συνάρτηση στο Excel.)

Το να ζητάτε =UNIQUE(B2:D227)θα σας προσφέρει κάθε μοναδικό συνδυασμό Rep, Date και Product που δεν είναι αυτό που αναζητούμε.

Πώς μπορούμε να περάσουμε δύο μη γειτονικές στήλες στη λειτουργία ΜΟΝΑΔΙΚΗ;

Όταν παρουσιάστηκαν οι δυναμικές συστοιχίες τον Σεπτέμβριο, είπα ότι δεν θα χρειαζόμαστε ποτέ να ανησυχούμε για τις πολυπλοκότητες των τύπων Ctrl + Shift + Enter. Αλλά για να λύσετε αυτό το πρόβλημα, θα χρησιμοποιήσετε μια ιδέα που ονομάζεται Lifting. Ας ελπίσουμε ότι μέχρι τώρα, έχετε κατεβάσει το ηλεκτρονικό βιβλίο Excel Dynamic Arrays Straight To The Point Μεταβείτε στις σελίδες 31-33 για μια πλήρη εξήγηση του Lifting.

Δείτε το βιβλίο μου για μια πλήρη εξήγηση του Lifting (και αργότερα, όταν πηγαίνετε να ταξινομήσετε τα αποτελέσματα, Pairwise Lifting)

Πάρτε μια συνάρτηση Excel που αναμένει μία τιμή. Για παράδειγμα, =CHOOSE(Z1,"Apple","Banana")θα επιστρέψει είτε Apple είτε Μπανάνα ανάλογα με το αν το Z1 περιέχει 1 (για Apple) ή 2 (για Μπανάνα). Η συνάρτηση CHOOSE αναμένει μια κλιμάκωση ως το πρώτο όρισμα.

Αντίθετα, θα περάσετε μια σταθερά πίνακα (1,2) ως το πρώτο όρισμα για ΕΠΙΛΟΓΗ Το Excel θα εκτελέσει τη λειτουργία Lifting και θα υπολογίσει το CHOOSE δύο φορές. Για την τιμή του 1, θέλετε τους αντιπροσώπους πωλήσεων στο B2: B227. Για την τιμή των 2, θέλετε τα προϊόντα σε D2: D227.

Πείτε ΕΠΙΛΟΓΗ να επιστρέψετε δύο απαντήσεις

Κανονικά, στο παλιό Excel, η σιωπηρή διασταύρωση θα περιόριζε τα αποτελέσματα. Αλλά τώρα που το Excel μπορεί να χύσει αποτελέσματα σε πολλά κελιά, ο παραπάνω τύπος επιστρέφει με επιτυχία έναν πίνακα όλων των απαντήσεων σε B και D:

Επιτυχία! Είναι όλα κατηφορικά από εδώ

Νιώθω ότι θα προσβάλω τη νοημοσύνη σας για να γράψετε το υπόλοιπο άρθρο, γιατί από εδώ είναι εξαιρετικά απλό.

Τυλίξτε τον τύπο από το προηγούμενο στιγμιότυπο οθόνης στο ΜΟΝΑΔΙΚΟ και θα λάβετε μόνο τους μοναδικούς συνδυασμούς των πωλήσεων εκπροσώπων και προϊόντων =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Ακόμα δεν έχει ταξινομηθεί

Για να ελέγξετε την κατανόησή σας, δοκιμάστε να αλλάξετε τον παραπάνω τύπο για να επιστρέψετε όλους τους μοναδικούς συνδυασμούς τριών στηλών: Αντιπρόσωπος πωλήσεων, Προϊόν, Χρώμα.

Κατ 'αρχάς, αλλάξτε τη σταθερά του πίνακα για να αναφέρεται (1,2,3)

Στη συνέχεια, προσθέστε ένα τέταρτο επιχείρημα για να επιλέγουν να επιστρέψουν χρώμα από Ε2: Ε227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Επιστρέψτε τον μοναδικό συνδυασμό τριών στηλών

Θα ήταν ωραίο να ταξινομήσετε αυτά τα αποτελέσματα, οπότε γυρίζουμε στο Ταξινόμηση με έναν τύπο χρησιμοποιώντας SORT και SORTBY.

Κανονικά, η συνάρτηση ταξινόμησης κατά την πρώτη στήλη αύξουσα θα ήταν =SORT(Array)ή =SORT(Array,1,1).

Για να ταξινομήσετε κατά τρεις στήλες, πρέπει να κάνετε ανύψωση κατά ζεύγη =SORT(Array,(1,2,3),(1,1,1)). Σε αυτόν τον τύπο, όταν φτάσετε στο δεύτερο όρισμα του SORT, το Excel θέλει να μάθει ποια στήλη θα ταξινομήσει. Αντί για μια μεμονωμένη τιμή, στείλτε τρεις στήλες μέσα σε μια σταθερά πίνακα: (1,2,3). Όταν φτάσετε στο τρίτο όρισμα όπου καθορίσετε 1 για Αύξουσα ή -1 για Φθίνουσα, στείλτε μια σταθερά πίνακα με τρία 1 για να δείξετε Αύξουσα, Αύξουσα, Αύξουσα. Εμφανίζεται το παρακάτω στιγμιότυπο οθόνης =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Για περισσότερες πληροφορίες σχετικά με την ανύψωση κατά ζεύγη, ανατρέξτε στη σελίδα 34 του Excel Dynamic Arrays κατ 'ευθείαν στο σημείο.

Τουλάχιστον μέχρι το τέλος του 2018, μπορείτε να κατεβάσετε το βιβλίο Excel Dynamic Arrays δωρεάν χρησιμοποιώντας τον σύνδεσμο στο κάτω μέρος αυτής της σελίδας.

Με ενθαρρύνουν να διαπιστώσω ότι η απάντηση στη σημερινή ερώτηση είναι λίγο περίπλοκη. Όταν βγήκε το Dynamic Arrays, σκέφτηκα αμέσως όλους τους εκπληκτικούς τύπους που δημοσιεύτηκαν στον πίνακα μηνυμάτων από τον Aladin Akyurek και άλλους και πώς αυτοί οι τύποι θα γίνουν πολύ πιο απλοί στο νέο Excel. Ωστόσο, το σημερινό παράδειγμα δείχνει ότι θα εξακολουθεί να υπάρχει ανάγκη για ιδιοφυΐες τύπου να δημιουργούν νέους τρόπους για τη χρήση των δυναμικών συστοιχιών.