Γενική φόρμουλα
=MAX(0,MIN(A1,1))
Περίληψη
Για να περιορίσετε μια ποσοστιαία τιμή έτσι ώστε να κυμαίνεται μεταξύ 0% και 100%, μπορείτε να χρησιμοποιήσετε έναν τύπο βασισμένο στις συναρτήσεις MIN και MAX. Στο παράδειγμα που εμφανίζεται, ο τύπος στο C5, αντιγράφεται παρακάτω, είναι:
=MAX(0,MIN(B5,1))
Το αποτέλεσμα είναι ότι οι αρνητικές τιμές εξαναγκάζονται στο μηδέν, οι τιμές άνω του 1 περιορίζονται στο 1 και οι τιμές μεταξύ 0 και 1 δεν επηρεάζονται.
Σημείωση: όλες οι τιμές μορφοποιούνται με μορφή αριθμού ποσοστού.
Εξήγηση
Για να κατανοήσετε αυτό το πρόβλημα, φροντίστε να καταλάβετε πώς λειτουργεί η μορφοποίηση ποσοστού αριθμού. Με λίγα λόγια, τα ποσοστά είναι δεκαδικές τιμές: 0,1 είναι 10%, 0,2 είναι 20% και ούτω καθεξής. Ο αριθμός 1, όταν έχει μορφοποιηθεί ως ποσοστό, είναι 100%. Περισσότερα για τις μορφές αριθμών εδώ.
Ο στόχος αυτού του παραδείγματος είναι να περιορίσει τις εισερχόμενες ποσοστιαίες τιμές έτσι ώστε να εμπίπτουν σε ανώτερο και κατώτατο όριο. Δεν επιτρέπονται αρνητικές τιμές και τιμές άνω του 100%, επομένως το τελικό αποτέλεσμα πρέπει να είναι ένας αριθμός μεταξύ μηδέν και 1 (0-100%).
Αν και η συνάρτηση IF μπορεί να χρησιμοποιηθεί για την επίλυση αυτού του προβλήματος (δείτε παρακάτω), το αποτέλεσμα θα είναι κάπως μεγαλύτερο και περιττό. Αντ 'αυτού, το παράδειγμα που εμφανίζεται χρησιμοποιεί έναν συνδυασμό των λειτουργιών MIN και MAX σε έναν πολύ συμπαγή τύπο:
=MAX(0,MIN(B5,1))
Αυτό είναι ένα παράδειγμα ένθεσης - η συνάρτηση MIN βρίσκεται μέσα στη συνάρτηση MAX. Το Nesting είναι ένα βασικό δομικό στοιχείο για πιο προηγμένους τύπους.
Δουλεύοντας από μέσα προς τα έξω, η συνάρτηση MIN χρησιμοποιείται για τον περιορισμό των εισερχόμενων τιμών σε 1 ως εξής:
MIN(B5,1) // get smaller value
Μετάφραση: επιστρέψτε το μικρότερο από B5 και 1. Για οποιαδήποτε τιμή πάνω από 1, επιστρέφεται η τιμή στο B5. Στο παράδειγμα, το B5 περιέχει -5% (-0.05), οπότε το MIN επιστρέφει -0,05. Αυτό το αποτέλεσμα επιστρέφεται απευθείας στη συνάρτηση MAX:
=MAX(0,-0.05) // get larger value
Εδώ, βλέπουμε τον τύπο να λειτουργεί. Επειδή το μηδέν είναι μεγαλύτερο (μεγαλύτερο) από -0.05, το MAX επιστρέφει μηδέν ως τελικό αποτέλεσμα. Η αρχική τιμή απορρίπτεται.
Λειτουργία IF
Όπως αναφέρθηκε παραπάνω, η συνάρτηση IF μπορεί επίσης να χρησιμοποιηθεί για την επίλυση αυτού του προβλήματος. Για να το κάνουμε αυτό, χρειαζόμαστε δύο ξεχωριστές συναρτήσεις IF. Ένα IF επιβάλλει μηδενικές αρνητικές τιμές:
IF(B5<0,0,B5) // cap at zero
Το δεύτερο IF περιορίζει μεγαλύτερες τιμές στο 1:
=IF(B5>1,1,B5) // cap at 1
Όταν τοποθετούμε το πρώτο IF μέσα στο δεύτερο, έχουμε τον τελικό τύπο:
=IF(B5>1,1,IF(B5<0,0,B5))
Αυτό είναι ένα παράδειγμα ένθετου IF. Επιστρέφει ακριβώς το ίδιο αποτέλεσμα με τον τύπο MIN και MAX παραπάνω, αλλά είναι ελαφρώς πιο περίπλοκος και περιττός Παρατηρήστε, για παράδειγμα, η αναφορά στο B5 πραγματοποιείται τρεις ξεχωριστές φορές.
Η κατώτατη γραμμή - όταν πρέπει να κάνετε μια επιλογή με βάση μικρότερες ή μεγαλύτερες τιμές, οι λειτουργίες MIN και MAX μπορούν να είναι ένας έξυπνος και κομψός τρόπος για να διατηρήσετε έναν τύπο απλό.
Λειτουργία MEDIAN
Εντάξει, τώρα που μιλήσαμε για ένθεση και μιλήσαμε για την κομψότητα του MIN με το MAX, θα πρέπει να αναφέρω ότι είναι δυνατό να λυθεί αυτό το πρόβλημα χωρίς καθόλου ένθεση με τη λειτουργία MEDIAN. Η γενική έκδοση του τύπου μοιάζει με αυτό:
=MEDIAN(0,1,A1)
Αυτό λειτουργεί επειδή η συνάρτηση MEDIAN επιστρέφει τον διάμεσο (μεσαίο αριθμό) σε μια ομάδα αριθμών. Όταν μια τιμή είναι αρνητική, το μηδέν γίνεται ο μεσαίος αριθμός. Όταν ένας αριθμός είναι μεγαλύτερος από 1, το 1 γίνεται ο μεσαίος αριθμός. Εξυπνος!
Ωστόσο, η σημείωση MEDIAN επιστρέφει τον μεσαίο αριθμό μόνο όταν ο συνολικός αριθμός τιμών είναι μονός. Εάν ο αριθμός των τιμών είναι ίσος, το MEDIAN επιστρέφει τον μέσο όρο των δύο αριθμών στη μέση. Κατά συνέπεια, εάν το κελί στόχος (A1) είναι κενό, το MEDIAN θα επιστρέψει τον μέσο όρο 1 και μηδέν, που είναι 0,5 ή 50% όταν μορφοποιηθεί ως ποσοστό.
