Υπάρχει ένα άσχημο σφάλμα υπολογισμού που εμφανίζεται στο Excel. Φαίνεται ότι το πρόβλημα πηγαίνει βαθιά στη μηχανή υπολογισμού του Excel και δεν θα είναι εύκολο να διορθωθεί.
Ο πυρήνας του προβλήματος είναι ένα απλό γεγονός: το Excel αποθηκεύει 15 ψηφία ακρίβειας σε ένα κελί. Μπορείτε να έχετε αριθμούς που έχουν 20 ψηφία, αλλά τυχόν ψηφία μεταξύ του τελευταίου σημαντικού ψηφίου και του δεκαδικού ψηφίου πρέπει να είναι μηδέν.
Πρόσφατα έχω δει δύο περιπτώσεις όπου η μηχανή υπολογισμού του Excel επέστρεψε λάθος αποτελέσματα. Όταν μπήκα στο πρόβλημα και κοίταξα την υποκείμενη XML, με εξέπληξε το γεγονός ότι το Excel αποθηκεύει κρυφά 17 ψηφία στο XML.
Το πρόβλημα είναι ότι το Excel θα εμφανίζει μόνο 15 ψηφία. Πιστεύετε λοιπόν ότι έχετε έναν αριθμό αποθηκευμένο ως 0.123456789012345 αλλά είναι πραγματικά αποθηκευμένος ως 0.12345678901234567.
Δεν μπορείτε να δείτε αυτά τα δύο τελευταία ψηφία. Και οι περισσότερες από τις λειτουργίες του Excel αγνοούν αυτά τα δύο τελευταία ψηφία. Εάν * όλες * οι συναρτήσεις αγνοούσαν τα δύο τελευταία ψηφία, δεν θα είχαμε πρόβλημα. Αλλά μέχρι στιγμής, η ταξινόμηση, το RANK και το FREQUENCY χρησιμοποιούν και τα 17 ψηφία.
Παρακάτω είναι ένα γνωστό τέχνασμα για την κατάταξη κελιών. Εάν χρειάζεστε κάθε κατάταξη για να εμφανίζεται ακριβώς μία φορά, μπορείτε να συνδυάσετε RANK και COUNTIF. Στην παρακάτω εικόνα, η Claire, η Flo, η Ivana και η Lucy είναι ισοπαλία στο 115%. Χρησιμοποιώντας τον τύπο RANK + COUNTIF, θα πρέπει να κατατάσσονται 5, 6, 7 και 8.
Αλλά ο τύπος αποτυγχάνει. Δύο σειρές κατατάσσονται ως 7. Αυτό δεν συμβαίνει ποτέ. Τέσσερις τύποι στη στήλη D βεβαιωθείτε ότι το 115% σε B6, B9, B12 και B15 είναι τα ίδια. Ο =B6=B15τύπος αναφέρει ότι και τα δύο κελιά περιέχουν τα ίδια δεδομένα.
Καθώς προσπάθησα να απομονώσω το πρόβλημα, ρίξτε μια ματιά στη λειτουργία RANK. Θα πρέπει να αναφέρει ισοπαλία 4 δρόμων στην 4η θέση για άτομα με 115%. Αλλά κατά κάποιο τρόπο, η Λούσι στη σειρά 15 κατατάσσεται μπροστά από τα άλλα τρία.
Για να το καταλάβω, έστειλα ένα αίτημα για βοήθεια στα άλλα MVP του Excel. Ο Jan Karel Pieterse άνοιξε το αρχείο Excel και κοίταξε το XML. Στο XML, μπορείτε να δείτε ότι αποθηκεύουν 17 ψηφία ακριβείας. Τα τέσσερα κελιά που μοιάζουν με γραβάτα στο Excel δεν είναι δεμένα στο XML. Ένα από το 115% αποθηκεύεται ως 1.1500000000000001 και το άλλο είναι 1,149999999999999999.
Μέχρι στιγμής, η ταξινόμηση, η κατάταξη και η συνάρτηση FREQUENCY χρησιμοποιούν τα επιπλέον ψηφία. Γιατί είναι αυτό πρόβλημα; Επειδή βασίζουμε το RANK και το COUNTIF και οι δύο χρησιμοποιούν τον ίδιο αριθμό ψηφίων. Με τη μία λειτουργία να χρησιμοποιεί 15 ψηφία και η άλλη να χρησιμοποιεί 17 ψηφία, έχετε πρόβλημα.
Προς το παρόν, η λύση φαίνεται να μετατρέπει όλες τις απαντήσεις σας χρησιμοποιώντας =ROUND(A4,15).
Κάθε Παρασκευή, εξετάζω ένα σφάλμα ή άλλη συμπεριφορά ψαριών στο Excel. Αυτό το σφάλμα υπολογισμού είναι δύσκολο να εντοπιστεί και χαρακτηρίζεται ως μεγάλο ψάρι.
Excel Thought Of the Day
Ζήτησα από τους φίλους μου στο Excel Master τις συμβουλές τους σχετικά με το Excel. Η σημερινή σκέψη να σκεφτούμε:
"Κάθε φορά που συγχωνεύεις κελιά σκοτώνεις ένα γατάκι"
Szilvia Juhasz
