Excel-Tabelle trotz Formeln richtig sortieren?

Hey Leute, ich stehe gerade bei meiner Excel-Tabelle etwas auf dem Schlauch…

Ich habe eine Arbeitsmappe „Funktionsliste“, in der ich arbeite, und eine Arbeitsmappe „Datenbank“, in der alle Bauteile mit entsprechenden Datenpunkten aufgelistet sind.

Nun ist die Mappe „Funktionsliste“ dafür da, dass ich in Spalte B mithilfe einer Dropdown-Liste alle Bauteile auswähle, die ich benötige und die in der Mappe „Datenbank“ eingepflegt sind. Mithilfe der SVERWEIS-Funktion übernehme ich die zum Bauteil dazugehörigen Datenpunkte, die ebenfalls in der „Datenbank“ von Spalte F bis Z eingepflegt sind.

Wenn ich nun in der „Funktionsliste“ in Zelle B9 das Bauteil Reparaturschalter auswähle, werden mir durch die SVERWEISE in den Spalten F bis Z alle Datenpunkte aus der „Datenbank“ übernommen. Mein SVERWEIS sieht dann in Spalte F z.B. folgendermaßen aus: =SVERWEIS(Funktionsliste!$B9;Datenbank!$B$9:G$33;6;FALSCH)

Auf die Weise übernimmt mir die Funktionsliste in Spalte F den Wert aus Spalte G aus der Datenbank, wenn das ausgewählte Bauteil in der Funktionsliste in der angegebenen Matrix in der Datenbank findet.

Wenn ich nun in Zelle B10 beispielsweise einen Drucksensor auswähle und das gleiche nun mit =SVERWEIS(Funktionsliste!$B10;Datenbank!$B$9:G$33;6;FALSCH) in Spalte F angebe, werden mir die Datenpunkte für den Drucksensor übernommen.

Jetzt kommt das Hauptproblem: Wenn ich Spalte B alphabetisch sortieren will, werden die Spalten mit den Datenpunkten nicht automatisch mit sortiert. Stattdessen würde durch die alphabetische Sortierung jetzt der Drucksensor in der B9 stehen und der Reparaturschalter in der B10. In der F9 würde dann aber der SVERWEIS nun den Wert aus der B10 suchen und nicht mehr aus der B9, weil die Formel nicht dynamisch mitgenommen wurde beim Sortieren…

Wie kann ich das Problem lösen? :)

Answer 1

[Gipfelstuermer]

Neue Antwort hinzugefügt, da auf Grund von alter Version scheinbar einige Dinge anders laufen, nun unter Excel 2019 folgendes entworfen. Leider konnte ich auch dort deine Sortierproblematik nicht reproduzieren, dennoch hier 2 Vorschläge.

Variante 1 setzt auf einer intelligenten Tabelle auf dazu in die "Tabelle" klicken Einfügen -> Tabelle dort wird man gefragt ob die Tabelle Überschriften enthält, was man in diesem Fall bejahen kann. Im Anschluss erhält die Tabelle ein anderes Design, dies kann man sofern gewünscht unter Formatvorlagen ändern.

Außerdem empfiehlt es sich der Tabelle unter Tabellenentwurf (dazu muss irgend eine Zelle innerhalb der Tabelle angeklickt sein) einen sinnigen Namen zu geben. Hier würde sich z.B. vielleicht FG_Liste empfehlen.

Im Anschluss kannst du die Formeln die unten für B3 und C3 gelistet sind nutzen (auf deinen Tabellennamen anpassen). Bei diesen beziehst du dich nicht mehr auf eine direkte Zelladresse sondern sagst in der gleiche Zeile die Spalte "Bez", nichts anderes heißt das @Bez

Auch wird diese Formel automatisch für alle folgenden Zeilen wiederholt, daher zuvor alle anderen Daten rauslöschen.

Alternative 2 wäre, falls es intelligente Tabellen in 2016 noch nicht gibt. Dir dies mittels indirekt zusammen zu bauen.

Adresse(Zeile();Spalte()-1;4) besagt nichts anderes als baue mir einen Zellebezug aus der aktuellen Zeile, der aktuellen Spalte-1 und stelle diesen relativ dar. daraus macht Adresse dann im og. Beispiel also Adresse(3;6-1;4) = E3

indirekt wird nun benötigt um diesen Zellbezug in den Wert in der Passenden Zelle umzuwandeln.

Ich hoffe dir damit geholfen zu haben.

Würde mich über eine Rückmeldung freuen.

Woher ich das weiß: Berufserfahrung – sowohl Beruf als auch Hobby

Comments

[Gojo63]

Mit Regelungstechnik liegst du ganz gut :D Ich habe mal versucht, die kleine Tabelle von dir zu reproduzieren. Leider funktioniert es bei mir mit dem SVERWEIS wieder nicht, dass sich beim alphabetischen Sortieren der Bezeichnungen die Datenpunkte und das Aggregat dynamisch passend zur Bezeichnung mit sortieren. Stattdessen geraten die SVERWEISE durcheinander und beziehen sich nicht mehr auf die Zeile, in der die sich befinden, sondern auf ganz andere Zellen in anderen Zeilen... Ich verwende Excel 2016 und bei mir gibt es die Funktion Spaltenwahl gar nicht erst ^^

[Gipfelstuermer]

@Gojo63

Okay mit 2016 gestaltet sich das schwieriger, da gibt es viele der neueren Komfort Funktionen nicht, genauso wie den Xverweis. ich hab noch 2019 vorliegen und schaue mal ob ich das dort reproduzieren kann .

Ich weiß gerade nicht wann die intelligenten Tabellen eingeführt wurden. Einfügen -> Tabelle. Die verhalten sich um einiges anders was Änderungen angeht. Kannst es mal damit probieren. Denn da kannst du dich direkt auf die gleiche Zeile beziehen anstatt auf einen festen Zellbezug.

Andernfalls index(vegleich()) Oder ggf. sogar indirekt. Da du dir dort den verlinkenden Zellbwzug auf Basis der aktuellen Zeile zusammenbauen kannst. Ich ergänze das mal später in meiner Antwort.

Tipp: Am besten immer die benutze Excel Version dazu schreiben von 2003 bis 365 wird hier fast alles verwendet.

[Gipfelstuermer]

@Gojo63

Ich hab dir mal einen neuen Vorschlag angehangen, ich würde Variante 1 vorziehen, da die sorgenfreier ist.

[Gojo63]

@Gipfelstuermer

Vielen Dank, probiere ich gleich aus! Und wenn du dich jetzt noch mit VBA auskennst, würde ich mich über eine Lösung zu meinem neuen Beitrag freuen :D

[Gipfelstuermer]

@Gojo63

Tut mir leid VBA, behandle ich total stiefmütterlich, da VBA für mich fast keinen Praxiseinsatz hat in den meisten Unternehmen die ich kenne ist VBA vollständig untersagt da sich darüber Fremdcode einschleusen lässt, und gerade als Techniker wo man stetig außerhalb der Firma unterwegs ist, wäre die Gefahr nochmal höher. Des Weiteren hängen wir ständig in den unterschiedlichsten Kundennetzwerken, da versucht man solche Schwachstelle auszumerzen und mir ist tatsächlich bisher auch noch kaum ein Fall untergekommen, wo ich das dringend gebraucht hätte.

2 Fälle wo wir es mal benutzt haben war den Pfad hunderter Excel Dokumente in einem Ordner in die Fußzeile zu schreiben.

Den Pfad zu bestimmten Text Datein auszulesen und diese je nach dem in welchen Ordnern Sie liegen unterschiedlich zu behandeln (der letzte Teil wurde dann aber wieder mit Formeln erledigt.)

Bei dem Code hat unser ITler das frei gegeben und stand währenddessen die ganze Zeit hinter mir.

Mein Code wäre daher vermutlich weder besonders schön noch besonders gut, da ich es kaum benutze.

Und privat hatte ich noch nie einen Anwendungsfall für VBA.

Answer 2

[AllwissendeBlob]

Ich verstehe, wo das Problem liegt. Wenn du die Spalte B sortierst, ändert sich die Position der Bauteile, aber die SVERWEIS-Formeln bleiben auf die ursprünglichen Zellen bezogen. Damit das Sortieren klappt und die SVERWEIS-Formeln nicht durcheinander geraten, könntest du die Daten in den Zellen "festschreiben". Das heißt, statt die SVERWEIS-Formel in den Zellen zu belassen, kopierst du die Werte und fügst sie als „Werte“ ein, bevor du sortierst.

So gehst du vor:

1. Werte statt Formeln: Markiere die Zellen mit den SVERWEIS-Formeln (also die Spalten F bis Z in deinem Beispiel), kopiere sie und füge sie dann als „Werte“ ein (Rechtsklick -> Inhalte einfügen -> Werte). Dadurch bleiben die angezeigten Werte in den Zellen bestehen, selbst wenn du die Tabelle sortierst.
2. Dynamische Bezugsmethode: Alternativ könntest du überlegen, auf eine andere Formel umzusteigen, die dynamischer ist, wie z.B. INDEX und VERGLEICH statt SVERWEIS, aber das würde den Aufwand deutlich erhöhen und ist für deinen aktuellen Fall vielleicht zu umständlich.

Die einfachste und schnellste Lösung ist also, die SVERWEIS-Formeln durch die Werte zu ersetzen, wenn du mit der Eingabe der Bauteile fertig bist, und dann kannst du die Tabelle ohne Probleme sortieren.

Damit bist du auf der sicheren Seite und vermeidest die Probleme, die durch das Sortieren entstehen könnten.

Comments

[mchawk777]

👍

Bleibt eigentlich nur zu ergänzen, dass für die beschriebene Aufgabe mir Excel prinzipiell das falsche Werkzeug genutzt wird.
Das hier wäre ein klassische Aufgabe für Access.

Merke: Excel ist keine Datenbank - Excel ist ein "Rechenschieber". 😉

Answer 3

[evtldocha]

Jetzt kommt das Hauptproblem: Wenn ich Spalte B alphabetisch sortieren will, werden die Spalten mit den Datenpunkten nicht automatisch mit sortiert.

... Du musst alle (belegten) Spalten in der Tabelle "Funktionsliste" auswählen und dann nach "Spalte B" sortieren. Damit bleiben die Referenzen korrekt.

Comments

[Gojo63]

Funktioniert leider immer noch nicht :/ Eventuell habe ich die Vorgehensweise missverstanden...

Answer 4

[Ortogonn]

Anhand deiner Beschreibung klingt es für mich nach folgendem Problem:

Du darfst nicht nur Spalte B markieren, sondern musst den gesamten Bereich markieren. Beim Sortieren wählst du dann aus, nach welcher Spalte sich Excel richten soll.

PS: (anonymisierte) Bsp-Tabellen auf einem freehoster oder (mehrere) Screenshots hier als Bild, sind oft zielführender als seitenlange Beschreibungen.

Edit: falls ichbes missverstanden habe und du Probleme beim referenzieren bekommst durch das Sortieren, kannst du diese vermutlich auch gut durch Nutzung von Namen vermeiden.

Auf dem Arbeitsblatt Datenbank die zusammen gehörenden Zellen markieren und links oben, wo die ausgewählten Zellen angezeigt werden, einfach einen Namen dafür eintippen. Den kannst du dann als Referenz angeben.

Also bspw. Datenbank!$B$9:G$33 markieren und sie Bauteilliste nennen. Dann kannst du diesen Namen immer anstelle des Bereichs angeben und Bezüge können sich nicht mehr verschieben.