SOLVED
Home

Excel benutzerdefinierte Sortierung gerundeter Zahlen funktioniert nicht

%3CLINGO-SUB%20id%3D%22lingo-sub-561705%22%20slang%3D%22de-DE%22%3EExcel%20custom%20sorting%20of%20rounded%20numbers%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561705%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EIn%20Excel%2C%20I%20have%20data%20from%20a%20*%20.csv%20file%20using%20data%2FAus%20text%20...%20Imported%20in%20standard%20format.%20At%20first%20glance%2C%20everything%20looks%20correct.%3C%2FP%3E%3CP%3EBoth%20columns%20contain%20decimal%20numbers%2C%20which%20I%20only%20want%20to%20edit%20rounded%20(as%20numbers%20formatted%20and%20decimal%20site%20column%200)%20further.%20That%2C%20too%2C%20works%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%3A%20If%20I%20want%20to%20sort%20both%20columns%20in%20a%20custom%20way%20(1.%20Criterion%20Column%20B%2C%202.%20Criterion%20Column%20A)%2C%20this%20does%20not%20work.%20If%20I%20directly%20wear%20the%20rounded%20number%20to%20the%20worksheet%20for%20a%20test%2C%20the%20sorting%20also%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan't%20you%20sort%20rounded%20numbers%20in%20Excel%20in%20multiple%20columns%3F%20Would%20be%20great%20if%20someone%20could%20help%20me-thank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-561705%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561723%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20custom%20sorting%20of%20rounded%20numbers%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561723%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340960%22%20target%3D%22_blank%22%3E%40Shirkano%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sorting%20runs%20according%20to%20the%20cell%20content-and%20this%20is%20not%20rounded.%20The%20number%20format%20doesn't%20matter.%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20sorting%20always%20works-whether%20rounded%20or%20not%20rounded.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561749%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20custom%20sorting%20of%20rounded%20numbers%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561749%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat's%20wrong%20with%20my%20table%20then%2C%20because%20I%20don't%20get%20the%20sorting%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EX%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E17.8395306972241%3C%2FTD%3E%3CTD%3E0.868110587058076%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%2C00300036096273%3C%2FTD%3E%3CTD%3E0.977659258926628%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3EAs%20a%20result%2C%20I%20would%20have%20liked%20to%20have%20sorted%201.%20Criterion%20Y%2C%202.%20Criterion%20X-incl.%20Rounding%3A%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EX%3C%2FTD%3E%3CTD%3Ey%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E18%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3EWhen%20I%20click%20on%20the%20above.%20Cell%20content%20applying%20custom%20sorting%2C%20the%20larger%20X%20value%20always%20remains%20at%20the%20top%2C%20i.e.%20It%20seems%20that%20it%20is%20not%20sorted.%20But%20I%20don't%20know%20where%20the%20error%20is%20in%20my%20data%20...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561767%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20custom%20sorting%20of%20rounded%20numbers%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561767%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340960%22%20target%3D%22_blank%22%3E%40Shirkano%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20said%2C%20the%20number%20format%20doesn't%20matter%20for%20sorting.%3C%2FP%3E%3CP%3EThe%20first%20Y%20is%200.868110587058076.%3C%2FP%3E%3CP%3EThe%20second%20Y%20value%20is%200.977698926628.%3C%2FP%3E%3CP%3EAnd%20since%20all%20Y%20values%20are%20different%2C%20sorting%20by%20X%20value%20no%20longer%20matters.%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20sort%20rounded%20values%20then%20you%20also%20have%20to%20round%20with%20RUNDEN%20().%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561769%22%20slang%3D%22de-DE%22%3ESubject%3A%20Excel%20custom%20sorting%20of%20rounded%20numbers%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561769%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK%2C%20I%20got%20it%20...%20Thank%20you%20for%20patient%20explaining!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561776%22%20slang%3D%22en-US%22%3EBetreff%3A%20Excel%20benutzerdefinierte%20Sortierung%20gerundeter%20Zahlen%20funktioniert%20nicht%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340960%22%20target%3D%22_blank%22%3E%40Shirkano%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20data%20is%20still%20being%20sorted%20but%20on%20Y%20first.%26nbsp%3B%20Although%20you%20have%20displayed%20the%20values%20without%20decimal%20places%20they%20are%20still%20used%20in%20the%20test.%26nbsp%3B%20To%20make%20the%20test%20work%20the%20way%20you%20suggest%2C%20you%20will%20need%20helper%20columns%20containing%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20ROUND(%20Y%2C%200%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eto%20sort%20on.%26nbsp%3B%20Once%20rounded%2C%20both%20Y%20values%20are%20given%20as%3C%2FP%3E%3CP%3E%3D%201.0000%3C%2FP%3E%3CP%3Eand%20the%20ordering%20will%20revert%20to%20the%20X%20values%20as%20you%20require.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Shirkano
New Contributor

Hallo,

ich habe in Excel Daten aus einer *.csv-Datei mittels Daten/Aus Text... im Standard-Format importiert. Auf den ersten Blick sieht alles korrekt aus.

Beide Spalten enthalten Dezimalzahlen, die ich aber nur gerundet (als Zahlen formatiert und Dezimalstellen=0) weiter bearbeiten möchte. Auch das funktioniert korrekt.

 

Mein Problem: wenn ich beide Spalten benutzerdefiniert sortieren möchte (1. Kriterium Spalte B, 2. Kriterium Spalte A), funktioniert das nicht. Trage ich für einen Test direkt die gerundete Zahl auf das Arbeitsblatt, funktioniert auch die Sortierung.

 

Kann man in Excel gerundete Zahlen in mehreren Spalten nicht abhängig sortieren? Wäre prima, wenn mir jemand helfen könnte - danke!

 

 

4 Replies

@Shirkano 

Die Sortierung läuft nach dem Zellinhalt - und der ist nicht gerundet. Das Zahlenformat spielt keine Rolle.

Im übrigen funktioniert die Sortierung immer - egal ob gerundet oder nicht gerundet.

 

@Detlef Lewin 

Was ist dann an meiner Tabelle falsch, denn ich bekomme die Sortierung nicht hin.

 

Beispiel:

 

XY
17,83953069722410,868110587058076
5,003000360962730,977659258926628
Als Ergebnis hätte ich gerne sortiert 1. Kriterium Y, 2. Kriterium X - inkl. Rundung:

 

XY
51
181
Wenn ich auf die o.g. Zelleninhalte die benutzerdefinierte Sortierung anwende, bleibt der größere X-Wert stets oben stehen, d.h. anscheinend wird nicht sortiert. Ich weiß aber nicht, wo der Fehler in meinen Daten steckt ...

@Shirkano 

Wie gesagt, das Zahlenformat spielt für die Sortierung keine Rolle.

Der erste Y-Wert ist 0,868110587058076.

Der zweite Y-Wert ist 0,977659258926628.

Und da alle Y-Werte unterschiedlich sind spielt die die Sortierung nach X-Wert keine Rolle mehr.

Wenn du gerundete Werte sortieren willst dann musst du auch runden mit RUNDEN().

 

Highlighted
Solution

@Detlef Lewin 

OK, ich hab's verstanden... Danke für's geduldige Erklären!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies