Excel Problembehandlung

[Airlag]

Ich bin nun wahrlich kein Anfänger, was Excel an geht, aber heute habe ich eine Eigenschaft erleben dürfen, mit der habe ich im Lebtag nicht gerechnet.

 

Ich habe eine NSC-Tabelle. In jeder Zeile wird ein NSC kurz beschrieben, mit seinen wichtigsten Attributwerten und mit Verwandtschaftsverhältnissen. Da ich schreibfaul bin habe ich die Namen der Eltern nicht ausgeschrieben sondern eine Referenz verwendet (z.B. mit der Formel "=A4" wobei in A4 die Zelle mit dem Namen in der Datenzeile des Vaters ist)

 

Wenn ich Daten in dieser Tabelle manuell verschiebe, z.B. durch Zeilen einfügen, dann werden alle Referenzen selbstverständlich korrekt angepasst.

 

Wenn ich jedoch die Tabelle sortiere, dann zerlegt es mir alle Referenzen. Ich kann sogar nachvollziehen was da schief läuft.

Die Berechnung der neuen Zeile für den Bezug addiert nicht das Delta der Verschiebung der referenzierten Zeile auf den Bezug sondern das Delta der Verschiebung der Zeile, in der die Formel steht. Das ergibt für mich überhaupt keinen Sinn.

 

Und wenn ich Hyperlinks verwende um schneller in die Zeilen der Eltern springen zu können, dann werden bei der Sortierung diese Bezüge überhaupt nicht angepasst.

 

Das schärfste ist, dass andere Tabellenkalkulationen wohl genau denselben Mist machen. LibreOffice habe ich gerade auch ausprobiert.

 

Kann mir jemand erklären, welcher Sinn hinter diesem Verhalten steckt? Es scheint ja wohl ein erwünschtes Verhalten zu sein.

 

Weiß hier jemand, ob man das Verhalten beim Sortieren irgendwie umschalten kann, damit Referenzen so angepasst werden wie bei manuellen Änderungen?

Bearbeitet 16. August 2019 von Einskaldir

[Bernward]

In Deinem Fall müsstest Du wohl mit $A$2 arbeiten, d.h. keine relativen Referenzen (bei mir funktioniert es)

[Airlag]

In Deinem Fall müsstest Du wohl mit $A$2 arbeiten, d.h. keine relativen Referenzen (bei mir funktioniert es)

 

Wenn ich das mache werden überhaupt keine Veränderungen an den Referenzen vorgenommen. Sie zeigen dann noch auf dieselben Zellen wie vorher, nur dass durch die Sortierung dort inzwischen ein anderer Name steht.

[seamus]

Schau mal hier - da wird Fkt indirect erwähnt und zig andere Tipps werden gegeben.

Bearbeitet 14. Juli 2016 von seamus

[Bernward]

Stimmt, bei mir war es ein zu kleines Testset. Aber es schient ein bekanntes Problem zu sein. Mit "excel sort keep references" bekomme ich einige Treffer aber auch keine überzeugende Lösung.

[Airlag]

Lange bekannt und offensichtlich nie gelöst

Es kommt noch so weit dass ich ne Tabellenkalkulation schreibe...

[Slüram]

Lange bekannt und offensichtlich nie gelöst

Es kommt noch so weit dass ich ne Tabellenkalkulation schreibe...

Dann verkauf die aber auch für teures Geld an Winzigweich.....

[draco2111]

Ist ja nicht so, dass es keine anderen Tabellenkalkulationen gibt.

Hast du mal LibreOffice oder eine andere Alternative getestet?

[seamus]

Ist ja nicht so, dass es keine anderen Tabellenkalkulationen gibt.

Hast du mal LibreOffice oder eine andere Alternative getestet?

Mind. Libreoffice erwähnte er -6.absatz-"das schärfste..."

 

@Airlag:

Falls du es gelöst bekommst, meldest du dich hier mit der lösung?

Bearbeitet 15. Juli 2016 von seamus

[Ma Kai]

Das ist jetzt nicht das, was Du gern hören würdest, aber: nicht sortieren.

 

Oder: baue die Formeln um den Fehler herum, also z. B. SVerweis mit Bereich fixiert mit $$.

[Airlag]

Das ist jetzt nicht das, was Du gern hören würdest, aber: nicht sortieren.

 

Oder: baue die Formeln um den Fehler herum, also z. B. SVerweis mit Bereich fixiert mit $$.

 

Wo man Werte über SVerweis noch sauber hin bekäme (in meinem Fall reicht es eigentlich, den Namen ins Feld zu schreiben) das scheitert mit Hyperlinks komplett. Es gibt keine Möglichkeit, Referenzen innerhalb der Seite sinnvoll zu ersetzen. Die Funktion Hyperlink kann nur externe Links.

 

Eben gerade kam mir ein Verdacht, und auch der wurde im Test natürlich grausam bestätigt: Wenn man ein Label setzt (also einer Zelle einen Namen gibt) und die Tabelle, in der sich diese Zelle befindet, sortiert, dann wird das Label natürlich auch nicht angepasst.

 

P.S.: SVerweis verwende ich in meinem Charakterbogen wie ein Weltmeister, deshalb ist das Dokument inzwischen ca. 200kB groß. Ich dachte, in der NSC-Tabelle könnte ich vielleicht etwas sparsamer sein.

Bearbeitet 15. Juli 2016 von Airlag

[Lukarnam]

Hmm, der Funktion, die Referenzen beim Sortieren sortiert, würde ich ein hohes Risiko für Fehleranfälligkeit einräumen.

 

Der Bedarfsfall muss beliebig große Blätter (worksheets; max mögliche Reihen, max mögliche Zellen) berücksichtigen, gegebenenfalls sogar Referenzen auf ein anderes Blatt, welches sortiert wird.

Also mehr als nur "ich sortiere mal schnell eine Spalte".

Wenn dazu noch mehr- und vielfach Referenzen verwendet werden und diese eventuell sogar verkettet sind, wird es richtig lustig.

Dazu kommt die gängige Benutzer-Steuerung und das Verhalten des (ungeduldigen) Benutzers vor der Kiste ...

 

Sortieren, über "n Spalten, auf- oder ab" erfolgt in der Regel in vielen iterativen Schritten, diese müssten möglicherweise (ist zu analysieren!) auch berücksichtigt werden.

 

Ich würde in der ersten Annäherung des Verfahrens

- vor dem Sortieren allen Zellen einen versteckten Label geben, z.B. eine Kopie der Position, z.B. SxRyCz

- das Sortieren ausführen (beliebige iterationen), d.h. modal ausführen, keine Benutzerinteraktion zulassen

- für jeden vorhandenen Verweis aus der Zielposition das Label berechnen, dieses in der sortierten Tabelle finden und dem Verweis die neue Position zuweisen

 

Das könnte gegebenenfalls dauern, d.h. lange Sanduhr- Verweilzeiten, 

 

Fazit: (relativ) langsamer Algorithmus, viele Tests zur Stabilität nötig, lange Schulung <-- --> Benutzerungeduld; (Bedienungs-)Fehleranfällig

Also, wozu den Aufwand?

 

Und, und was habe ich wohl vergessen?

Bearbeitet 15. Juli 2016 von Lukarnam

[Solwac]

@Luki: Ich denke auch, ein kleines VBA-Script wäre hier sinnvoll. Es hätte den Vorteil, dass die Datenstruktur (zur Tippersparnis) sich individueller gestalten lässt.

[Lukarnam]

@Luki: Ich denke auch, ein kleines VBA-Script wäre hier sinnvoll. Es hätte den Vorteil, dass die Datenstruktur (zur Tippersparnis) sich individueller gestalten lässt.

 

Ja, so ist es.

Wenn das Prinzip erstmal verstanden (und getestet).

[Airlag]

@Lukarman alle Probleme und Fallstricke, die du beschreibst, sind eigentlich gelöst. Denn beim Einfügen einer Zeile und dem damit verbundenen Verrutschen von Zellen nach unten werden alle Referenzen auf verschobene Zellen korrekt angepasst. Selbst wenn man einen Bereich markiert und diesen beliebig verschiebt werden Referenzen auf Zellen in diesem Bereich korrekt angepasst. Deshalb ärgert es mich ja besonders, dass Sortieren hier so kläglich versagt.

 

Das Einzige, das ich bei manueller Bearbeitung auch nicht angepasst bekomme sind Hyperlinks auf Zellen des Arbeitsblattes. Hyperlinks kennen auch nicht die Unterscheidung zwischen relativen (B1) und absoluten ($B$1) Koordinaten. in Hyperlinks sind es immer absolute Koordinaten

[Ma Kai]

Was Luki meint, ist daß die schiere Menge an solchen - im Einzelschritt bekannten und gelösten - Anpassungen rasch die Leistungsfähigkeit des Computers an sich erschlagen kann. Wenn Du 65k Zeilen mit eh' schon vollem Speicher und richtig schön vielen Verweisen sortieren willst...

 

Du müßtest im Prinzip zu jeder Sortiereinheit (d.h. meist Zeile) die ursprüngliche Position mit speichern, dann wenn das Sortieren an sich abgeschlossen ist, jede Zeile (o. Äquivalent) komplett durchgehen und jeden Verweis innerhalb des Sortierbereichs finden und dann anhand der gespeicherten ursprünglichen Position (die nicht suchfreundlich angeordnet oder indexiert ist) den Verweis anpassen.

Der Speicherbedarf dürfte sich in Grenzen halten, aber das kann durchaus mal etwas länger dauern.

[Airlag]

@Ma Kai Sorry, deine Überlegungen sind in erster Näherung Mist. Mir fällt auf Anhieb eine Lösung ein, für die man keine Referenzlisten pro Zelle mit führen muss.

 

Bei einem Sortiervorgang habe ich einen Bereich und ich bekomme beim Sortieren innerhalb des Bereiches pro Zeile einen Integer-Wert, um den eine Referenz angepasst werden muss, wenn die Referenz in diese Zeile zeigt.

 

Natürlich ist es nicht performant, jede Formel von jeder Zelle einer Datei zur Laufzeit zu analysieren, ob da eine Referenz drin ist. Es wäre sinnvoll, zu jeder Formel eine Liste von Offset/Length Pärchen vorzuhalten, die innerhalb des Formel-Strings die Positionen von Referenzen innerhalb des Strings markieren. Wie üblich geht Geschwindigkeit zu Lasten des Speicherbedarfes.

 

Mit einer Excel-Tabelle mit 65k Zeilen sind die meisten Bürorechner jenseits ihrer Leistungsgrenze. Da spielt es keine Rolle, ob pro Zelle noch ein paar zusätzliche Werte vorgehalten werden oder ob man irgend etwas anderes damit macht. Bei Größenordnungen bis zu ein paar hundert Zeilen kann man Excel gut verwenden. Bei tausenden von Datenzeilen sollten man sich die Mühe machen und eine Datenbank benutzen.

 

Meine Kurzfrist-Lösung sieht so aus:

Ich erstelle die Tabelle aus Schreibfaulheit mit Referenz-Formeln.

Dann kopiere ich die Spalten mit Referenzen "by value" und werde so die Referenzen los.

Auf Hyperlinks verzichte ich ganz.

Dann kann ich die Tabelle nach Belieben sortieren.

 

Die Langfrist-Lösung besteht aus einer Kontaktaufnahme mit den Machern von Libre Office, denen ich ein paar Verbesserungsvorschläge machen werde. MS-Kompatibilität in allen ehren, aber man kann da sicher einen Schalter in die Einstellungen(pro Dokument) ein bauen mit dem man zwischen MS-Kompatibel und Fehlerbereinigt umschalten kann.

[Ma Kai]

Airlag, es könnte hilfreich sein, in meinem Beitrag den feinen Unterschied zwischen Zeile und Zelle zu verstehen und zwischen meinem Gedanken und Verständnishilfe zu Lukis.

[Bruder Buck]

Moin Schwarmintelligenz!

Ich sitze hier an einem "fremden" Rechner und habe eine Excel-Datei geöffnet, die ich vorher per Mail verschickt hatte, d.h. die Datei war "ganz normal". An diesem Rechner wird aber unterhalb der Menüleiste die komplette Leiste für die Formeln gar nicht angezeigt. Ich sehe also den tatsächlichen Inhalt einer Zelle nicht, nicht den Zellennamen, all die wichtigen Dinge.

Nun versuche ich schon seit einer Weile erfolglos über eine Internetsuche heraus zu finden, wie ich diese Leiste wieder einblenden kann. Ich habe noch nie gesehen, dass die ausgeblendet ist und komme nicht weiter...

Kann jemand helfen?

Euer

Bruder Buck

[Solwac]

Welches Excel ist es denn?

[Bruder Buck]

vor 4 Minuten schrieb Solwac:

Welches Excel ist es denn?

Versuche ich grad raus zu finden. Sieht nach einer neueren Version aus, 2013 oder 2016, denke ich?

Hab's, ist ein 2013er

Bearbeitet 4. August 2018 von Bruder Buck

[Solwac]

Wenn Du an die "Optionen" kommst, dann gibt es dort unter "Erweitert" etwas zur "Anzeige". Dort kann man dann die Bearbeitungszeile ein- und ausschalten.

[Bruder Buck]

Danke! Geht!

Da wäre ich nicht drauf gekommen, weil diese Leiste ja nie fehlte. Daher habe ich auch schlecht danach mit Suchbegriffen suchen können, weil ich ihren Namen nicht wusste.

[Bruder Buck]

Moin! Ich habe bei der Arbeit das Problem, dass mir zwei unterschiedliche Abzüge von Daten in Excel unterschiedliche Formate für unsere Teilenummern liefern.

Die Nummern sind aufgeteilt in Zwei-Buchstaben - Punkt - drei Zahlen - Punkt - vier Zahlen - Schrägstrich - eventuell nochmal zwei Zahlen. Beispiel: XX.000.0000/00 oder YY.011.0111 (ohne Schrägstrich)

Die eine Auswertung liefert mir die Teilenummern im Original. Die andere lässt die Punkte und den / - also die Trennzeichen - weg. Ich will aber beide Tabellen zusammen fahren, weil sie über die gleichen Teile unterschiedliche Informationen enthalten und ich das bereinigen will. 

Wie bringe ich Excel dazu, gezielt in der einen Spalte, in der die Teilenummern stehen, jeweils Spalte A, entweder die Trennzeichen rein zu nehmen, oder sie raus zu nehmen. Damit am Ende das Format gleich ist und die doppelten Zeilen schön sortierbar untereinander stehen. 

 

[daaavid]

Strg+H