Γνωρίζετε το τέλειο κόλπο της δημιουργίας μιας αναφοράς σε μια επικεφαλίδα στήλης στο τελευταίο μη κενό κελί μιας σειράς; Το καλύτερο: Δεν χρειάζεστε βοηθητικές γραμμές ή στήλες. Είναι τόσο εύκολο:
Συνδυάστε τις τέσσερις συναρτήσεις IFERROR (), INDEX (), MAX () και IF ()
Ο διευθύνων σύμβουλος στις πωλήσεις θα σας στείλει μια λίστα με τις συμβάσεις που συνάπτονται ανά μήνα για προϊόντα που καταργούνται σταδιακά. Θα πρέπει να χρησιμοποιήσετε έναν τύπο στη στήλη Ν για να καθορίσετε τον τελευταίο μήνα πωλήσεων για κάθε προϊόν - χωρίς βοηθητικές γραμμές ή στήλες. Εάν δεν έχουν συναφθεί άλλες συμβάσεις, εισαγάγετε ένα κενό κελί στη στήλη Ν.
Αυτό το παράδειγμα, απλό με την πρώτη ματιά, αποδεικνύεται ότι είναι ένα σκληρό περικόχλιο χωρίς τη χρήση βοηθητικών γραμμών ή στηλών. όπως και Excel για εξάσκηση-Οι αναγνώστες σπάνε το παξιμάδι! Έχουμε τον ακόλουθο τύπο πίνακα στο κελί για επίλυση προβλημάτων Ν2 δημιουργήθηκε ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; ΣΤΗΛΗ (B2: M2) -1; -1))); "")}
Ρίξτε μια ματιά στο πώς λειτουργεί βήμα προς βήμα
Ξεκινήστε με τη συνθήκη IF () ενσωματωμένη στον τύπο πίνακα: {= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (ΑΝ (B2: M2 "", ΣΤΗΛΗ (B2: M2) -1, -1)));"")}
Η συνθήκη IF () δημιουργεί μια πλασματική βοηθητική γραμμή στον τύπο πίνακα και ελέγχει για τα κελιά Β2 μέχρι Μ2είτε αυτά είναι άδεια είτε όχι. Εάν ένα κελί είναι κενό, η τιμή -1 επιστρέφεται διαφορετικά μέσω της συνάρτησης COLUMN (), ο αντίστοιχος αριθμός στήλης μείον την τιμή 1.
Η αφαίρεση του 1 απαιτείται στον τύπο επειδή η πρώτη στήλη στον πίνακα δεν περιέχει το όνομα του μήνα, αλλά το όνομα του προϊόντος. Παρακάτω θα μάθετε πώς να χρησιμοποιείτε τη συνάρτηση INDEX () για να εμφανίσετε το αντίστοιχο όνομα μήνα, το οποίο - αν δεν αφαιρέσατε το 1 - λόγω της πρόσθετης στήλης που χρησιμοποιήθηκε ΕΝΑ. θα ήταν λάθος κατά μία μόνο στήλη.
Εάν όλα τα κελιά στην περιοχή Β2: Μ2 είναι κενά, η τιμή -1 (χωρίς πωλήσεις προϊόντων) δημιουργεί ένα σφάλμα που χρησιμοποιούμε για να αντιπροσωπεύσουμε ένα κενό κελί. Η ενεργή βοηθητική γραμμή φαίνεται στο Σχ. ③ στη γραμμή 3.
Στο επόμενο βήμα διαβάζετε τη μεγαλύτερη τιμή με τη συνάρτηση MAX (), στην οποία είναι ένθετη η συνθήκη IF (). Αυτή είναι η τιμή 12 στη γραμμή 3 (στήλη 13 μείον 1 · βλέπε βοηθητική γραμμή στο σχήμα ③):
{= IFERROR (INDEX ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", ΣΤΗΛΗ (B2: M2) -1, -1)));"")}
Μεταβιβάζετε αυτήν την τιμή MAX στη συνάρτηση INDEX (). Ο αντίστοιχος ημερολογιακός μήνας διαβάζεται στη συνέχεια στη γραμμή 1. Η περιοχή δεδομένων της συνάρτησης INDEX () είναι η περιοχή $ B $ 1: $ M $ 1Το Η περασμένη τιμή MAX - στο παράδειγμα 12 - σημαίνει ότι η δωδέκατη τιμή στη λίστα, δηλ. Δεκ για τον Δεκέμβριο:
{= IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; ΣΤΗΛΗ (B2: M2) -1; -1)));"")}
Εάν όλα τα κελιά στο εύρος μηνών του πίνακα είναι κενά, τότε η μεγαλύτερη τιμή είναι -1 (βλ. Συνθήκη IF () στην αρχή). Εάν η τιμή -1 περάσει στη συνάρτηση INDEX (), αυτό οδηγεί αναπόφευκτα σε μια τιμή σφάλματος, καθώς η περιοχή λίστας της συνάρτησης INDEX () περιέχει μόνο δώδεκα καταχωρήσεις και επομένως δεν μπορεί να βρει την καταχώρηση -1. Πιάνετε αυτήν την τιμή σφάλματος με τη συνάρτηση IFERROR () και αντί αυτού επιστρέφετε μια κενή συμβολοσειρά. Στο παράδειγμα, αυτό ισχύει για το προϊόν C στη γραμμή 4, επειδή δεν θα μπορούσε να συναφθεί άλλη σύμβαση για αυτό το προϊόν:
{=IFERROR(INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", ΣΤΗΛΗ (B2: M2) -1, -1)));"")}
Δεδομένου ότι πρόκειται για έναν τύπο πίνακα, συμπληρώστε την καταχώριση του τύπου με τον συνδυασμό πλήκτρων Ctrl + Shift + Enter.
