Περίληψη
Για να βρείτε τη χαμηλότερη παλίρροια τη Δευτέρα, δεδομένου ενός συνόλου δεδομένων με πολλές ημέρες υψηλής και χαμηλής παλίρροιας, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα που βασίζεται στις συναρτήσεις IF και MIN. Στο παράδειγμα που παρουσιάζεται, ο τύπος στο I6 είναι:
(=MIN(IF(day=I5,IF(tide="L",pred))))
που επιστρέφει τη χαμηλότερη παλίρροια της Δευτέρας στα δεδομένα, -0,64
Για να ανακτήσετε την ημερομηνία της χαμηλότερης παλίρροιας της Δευτέρας, ο τύπος στο I7 είναι:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Όπου το φύλλο εργασίας περιέχει τα ακόλουθα εύρη τιμών: ημερομηνία (B5: B124), ημέρα (C5: C124), ώρα (D5: D124), pred (E5: E124), παλίρροια (F5: F124).
Και οι δύο είναι τύποι συστοιχιών και πρέπει να εισαχθούν με έλεγχο + shift + enter.
Δεδομένα από το tidesandcurrents.noaa.gov για το Santa Cruz της Καλιφόρνια.
Εξήγηση
Σε υψηλό επίπεδο, αυτό το παράδειγμα αφορά την εύρεση μιας ελάχιστης τιμής βάσει πολλαπλών κριτηρίων. Για να το κάνουμε αυτό, χρησιμοποιούμε τη συνάρτηση MIN μαζί με δύο ένθετες συναρτήσεις IF:
(=MIN(IF(day=I5,IF(tide="L",pred))))
δουλεύοντας από μέσα προς τα έξω, το πρώτο IF ελέγχει αν η ημέρα είναι "Δευ", με βάση την τιμή στο I5:
IF(day=I5 // is day "Mon"
Εάν το αποτέλεσμα είναι ΑΛΗΘΕΙ, εκτελούμε ένα άλλο IF:
IF(tide="L",pred) // if tide is "L" return prediction
Με άλλα λόγια, εάν η ημέρα είναι "Δευ", ελέγχεται αν η παλίρροια είναι "Λ". Εάν ναι, επιστρέφουμε το προβλεπόμενο επίπεδο παλίρροιας, χρησιμοποιώντας το όνομα εύρους pred .
Σημειώστε ότι δεν παρέχουμε "τιμή εάν ψευδής" ούτε για IF. Αυτό σημαίνει ότι εάν οποιαδήποτε λογική δοκιμή είναι FALSE, η εξωτερική IF θα επιστρέψει FALSE. Για περισσότερες πληροφορίες σχετικά με ένθετα IF, ανατρέξτε σε αυτό το άρθρο.
Είναι σημαντικό να κατανοήσετε ότι το σύνολο δεδομένων περιλαμβάνει 120 σειρές, επομένως κάθε ένα από τα ονομαζόμενα εύρη στον τύπο περιέχει 120 τιμές. Αυτό το καθιστά έναν τύπο πίνακα - επεξεργαζόμαστε πολλές τιμές ταυτόχρονα. Μετά την αξιολόγηση και των δύο IF, το εξωτερικό IF θα επιστρέψει έναν πίνακα που περιέχει 120 τιμές όπως αυτή:
(FALSE;FALSE;FALSE;FALSE;FALSE;3.27;FALSE;0.3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;2.02;FALSE;0.17;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3.04;FALSE;-0.55;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;1.96;FALSE;-0.64;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;3;FALSE;-0.02;FALSE;FALSE;FALSE;FALSE)
Το βασικό πράγμα που πρέπει να παρατηρήσετε εδώ είναι μόνο οι τιμές που σχετίζονται με τη Δευτέρα και η χαμηλή παλίρροια επιβιώνουν από το ταξίδι μέσω των ένθετων IF. Οι άλλες τιμές έχουν αντικατασταθεί με FALSE. Με άλλα λόγια, χρησιμοποιούμε τη διπλή δομή IF για να "πετάξουμε" τιμές που δεν μας ενδιαφέρουν.
Ο παραπάνω πίνακας επιστρέφεται απευθείας στη συνάρτηση MIN. Η συνάρτηση MIN αγνοεί αυτόματα τις τιμές FALSE και επιστρέφει την ελάχιστη τιμή εκείνων που παραμένουν, -0,64.
Αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter.
Ελάχιστο με MINIFS
Εάν έχετε το Office 365 ή το Excel 2019, μπορείτε να χρησιμοποιήσετε τη συνάρτηση MINIFS για να πάρετε τη χαμηλότερη παλίρροια της Δευτέρας ως εξής:
=MINIFS(pred,day,"Mon",tide,"L")
Το αποτέλεσμα είναι το ίδιο και αυτός ο τύπος δεν απαιτεί έλεγχο + shift + enter.
Λάβετε την ημερομηνία
Μόλις βρείτε το ελάχιστο επίπεδο παλίρροιας της Δευτέρας, αναμφίβολα θα θέλετε να μάθετε την ημερομηνία και την ώρα. Αυτό μπορεί να γίνει με έναν τύπο INDEX και MATCH. Ο τύπος στο I7 είναι:
(=INDEX(date,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
Δουλεύοντας από μέσα προς τα έξω, πρέπει πρώτα να εντοπίσουμε τη θέση της χαμηλότερης παλίρροιας της Δευτέρας με τη συνάρτηση MATCH:
MATCH(1,(day=I5)*(tide="L")*(pred=I6),0))
Εδώ, δοκιμάζουμε τις ίδιες δοκιμές υπό όρους που εφαρμόσαμε παραπάνω για να περιορίσουμε την επεξεργασία μόνο στη χαμηλή παλίρροια της Δευτέρας. Ωστόσο, εφαρμόζουμε ένα ακόμη τεστ για να περιορίσουμε τα αποτελέσματα στην ελάχιστη τιμή τώρα στο I6 και χρησιμοποιούμε μια ελαφρώς απλούστερη σύνταξη βασισμένη σε λογική boolean για την εφαρμογή κριτηρίων. Έχουμε τρεις ξεχωριστές εκφράσεις, κάθε μία δοκιμάζει μια συνθήκη:
(day=I5)* // day is "Mon" (tide="L")* // tide is "L" (pred=I6) // prediction is min value
Each of these expressions runs on 120 values and returns an array of 120 TRUE FALSE results. When these arrays are multiplied by one another, the TRUE FALSE values are coerced to 1s and 0s. The result is a single array like this:
(0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0)
Because there is only one value in the entire data set that meets all three conditions, there is only a single 1 in the array.
Now you can see why we have configured the MATCH function to look for the number 1 in exact match mode. MATCH locates the 1, and returns a position of 88 directly to the INDEX function. We can now rewrite the formula like this:
=INDEX(date,88) // returns 23-Dec-19
The INDEX function then returns the 88th value in the named range date, which is 23-Dec-19. This is the date that corresponds to the lowest Monday tide level.
This is an array formulas and must be entered with control + shift + enter.
Get the time
The formula to retrieve the time of the lowest Monday tide is almost the same as the formula to get the date. The only difference is that the named range time is provided to INDEX instead of date. The formula in I8 is:
(=INDEX(time,MATCH(1,(day=I5)*(tide="L")*(pred=I6),0)))
In other respects the behavior of the formula is the same, so we end up with a similar result:
=INDEX(time,88) // returns 2:44 PM
As before, INDEX returns the 88th item in the array, which is 2:44 PM.
This is an array formulas and must be entered with control + shift + enter.
Note: in the event of a tie (two Monday low tides with the same value), the INDEX and MATCH formulas above will return the first match.
Date and time with XLOOKUP
With the XLOOKUP function, you can simplify the formulas used to get the date and time associated with the lowest tide:
=XLOOKUP(1,(day=I5)*(tide="L")*(pred=I6),date) // get date =XLOOKUP(1,(day=I5)*(tide="L")*(pred=I6),time) // get time
Αυτό είναι ένα παράδειγμα που δείχνει όμορφα την ευελιξία του XLOOKUP. Μπορούμε να χρησιμοποιήσουμε ακριβώς την ίδια λογική από τους παραπάνω τύπους INDEX και MATCH, σε έναν απλό και κομψό τύπο.
