LibreOffice-Forum

Liebe ExpertInnen,

ich bastele gerade an einem Haushaltsbuch.
Ich möchte die Ausgaben nicht nur Posten für Posten festhalten, sondern sie hinterher auch nach verschiedenen Ausgabengruppen unterteilt statistisch erfassen können.
Dazu habe ich in der Datei eine eigene Tabelle Kategorien angelegt, in der in jeder Spalte Posten unter einer Rubrik wie z.B. "Nahrungsmittel", "Rad/Sport", "Politik" usw. untereinander aufgelistet sind und wo ich in jeder Spalte auch weitere Einzelposten nachtragen kann. Die Spaltenüberschriften enthalten Buchstabenkürzel, die die jeweilige Kategorie kennzeichnen, "nm", "rs", "po" usw..
In die Tabelle Ausgaben will ich nun eine Spalte einfügen, die bei jedem Posten, den ich links eintrage, das zutreffende Kategorienkürzel anzeigt, so dass ich das nicht jedes Mal von Hand eintragen muss. Die entsprechende Funktion müsste also ermitteln, in welcher Spalte der Tabelle Kategorien der eingetragene Posten aufgelistet ist und wie das dazugehörige Buchstabenkürzel heißt.
Möglicherweise muss man dafür ein Array anlegen, aber ich kenne mich damit nicht aus.

Gruß,
Mau

Hallo,
anbei mal eine Variante.
Ich habe die Kategorien mal waagerecht angeordnet und dem ganzen Bereich Kategorien.A1:K20 den Namen "posten" vergeben. Das hat den Effekt, dass ich den Bereich Posten als Gültigkeitsbedingung nutzen kann und mir so in Tabelle1.B2 ein Popup-Menü mit den Kategorien (abgegrenzt durch -------------; die natürlich nicht ausgewählt werden sollen) und den darin enthaltenen Posten erscheint. Per Formel wird dann in Tabelle1.C2 die Kategorie ermittelt.
Bei Bedarf kann der Bereich "posten" beliebig vergrößert werden.

Sehr interessant, mikele, danke.

Das sind gleich mehrere Funktionen, die ich noch nie verwendet habe.
Ich werde mich heute Abend mal dransetzen, mich mit ihrer Arbeitsweise und Syntax zu beschäftigen und dann in meiner Datei auszuprobieren.
Bis dann,
Mau

Lieber mikele,

ich habe mit Hilfe des Buches Tabellenkalkulation mit OpenOffice.org 3 von Jörg Schmidt (Galileo Press, 2009) versucht, deine Formel für die Ermittlung der Kategorie zu verstehen. Es ist mir nicht so recht gelungen.

Zunächst habe ich mich mit Schmidts Erklärungen und Beispielen zu der Frage, was eine Matrix ist und wie sie funktioniert, befasst. Leider nimmt er fast nur Beispiele, in denen Matrizes miteinander multipliziert werden. Ein Beispiel, das meinem Fall nahe kommt, habe ich nicht gefunden.

Für das Folgende habe ich deine Beispieldatei etwas erweitert und dabei in Tabelle1 die einzelnen Funktionen mit ihren Parametern und deren Inhalten auseinandergedröselt.
Einigermaßen klar ist mir, wie und mit welchen Parametern die Funktion INDEX arbeitet.
Sie braucht laut Schmidt die Parameter Bezug, Zeile, Spalte und Bereich.
posten als Bezug ist klar.
Als Parameter für Spalte hast du eine 2 gesetzt. Warum?

Vor allem aber ist mir unklar, was in deiner Formel beim Parameter Zeile genau passiert.
SUMMENPRODUKT ist eine Matrixfunktion, die, wie der Name sagt, Summe(n) aus mehreren Produkten bildet. Aber in meinem Fall wird doch gar nichts multipliziert, oder sehe ich das falsch? Was genau macht sie hier dann mit den verschiedenen Matrizes?
Matrix 1 lautet posten=B2. Was passiert da? posten ist doch ein Bereich und keine Einzelzelle!
Vor Matrix 2 stehe ich wie der Ochs vorm Berg. Warum addierst du Zeile und Spalte miteinander? Warum die Multiplikation mit 0?

Und: Wie legt man den Bereich posten an?

Zu deiner Tabelle Kategorien: Du hast die einzelnen Kategorien mit ihren Einzelelementen waagerecht angeordnet, jeweils in einer Zeile. Ich habe sie, wie du in der Datei sehen kannst, vertikal angeordnet, also jeweils in einer Spalte. Wie müsste ich deine Formel dann abändern?

Gruß,
Mau

Hallo mau,

das Ganze ist schon nicht so einfach zu verstehen.
Der Reihe nach:
1) Festlegen eines Bereichnamens:
Am einfachsten ist es (vielleicht), wenn du den gewünschten Bereich (Kategorie.A1:K20) zunächst markierst. Oben links steht dann in dem Bereichs-/Namensfeld "A1:K20". Wenn du dort hineinklickst, kannst du den von dir gewünschten Namen vergeben. Alternativ klickst du auf "Namen verwalten" in der Dropdownliste. Du kannst natürlich auch über das Menü ->Tabelle->Benannte Bereiche gehen.
2) Warum habe ich die Kategorien waagerecht angeordnet?
Der einzige Grund ist, dass ich den Bereich für die Gültigkeitsbedingung in Tabelle1, Spalte B nutze (damit dort ein Auswahlmenü erscheint).
Wenn ich den Bereich posten (also Kategorie.A1:K20) als Gültigkeitsbedingung nehme, dann werden mir alle Einträge aus dem Bereich aufgelistet. Leider aber zeilenweise, sodass erst alle Einträge der ersten, dann der zweiten Zeile etc erscheinen.
Da eine senkrechte Verwaltung der Kategorien natürlicher ist, müssen für die Gültigkeitsbedingung Zeilen und Spalten vertauscht werden. Dafür gibt es (glücklicherweise) eine Funktion: MTRANS(). Ich habe es mal so umgebaut. Die erste Zeile (Klartext der Kategorie) gehört nicht dazu! (sonst würde der auch noch in der Dropdownliste erscheinen, was eher verwirrend ist)
Allerdings: da die Kategorie sowieso automatisch ermittelt wird - wozu die Kürzel?
3) Ermitteln der Kategorie anhand des eingetragen Postens (Erläuterung für die neue, senkrechte Variante)
Nun wird es etwas knifflig. Was muss passieren? Der in B2 eingetragen Posten muss in der Matrix "posten" durchsucht werden. Dort wo er gefunden wird, steht die Kategorie in der zweiten Zeile (in der ersten steht "-------------------").
Wie lässt sich das in Formeln umsetzen? (siehe auch die Einzelschritte im Anhang)
Zunächst vergleichen wir jede Zelle des Bereiches mit dem gesuchten Text: Diese (Matrix-)Formel liefert überall 0 und nur in der Zelle mit Übereinstimmung eine 1.
Jetzt müssen wir die Spalte ermitteln, in der die 1 steht. Dazu multiplizieren wir einfach jede Zelle (in der jetzt nur 0 oder 1 steht) mit ihrer Spaltennummer. Im Ergebnis haben wir eine Matrix mit lauter Nullen und nur an einer Stelle steht jetzt die Spaltennummer.
Die Funktion SPALTE() liefert mir die die aktuelle Spalte oder die Spalte des Bezuges. Man sollte nun annehmen, das mir eine Matrix mit den Spaltennummern der Zellen liefert - leider nicht Die Funktion Spalte() angewendet auf eine Matrix liefert nur eine Zeile mit den Spaltennummern (alle weiteren Zeilen würden ja auch nur dieselben Zahlen liefern). Wie also zu einer Matrix kommen? Die Funktion Zeile() liefert interessanterweise auf eine Matrix angewendet nur eine Spalte mit den Zeilennummern! Wenn ich nun in einer Formel beide Funktionen verwende, wird eine Matrix erstellt. Da ich aber die Zeile gar nicht brauche multipliziere ich sie einfach mit 0 und weg ist sie.
Diese Multiplikation der beiden Matrizen macht die Funktion Summenprodukt() für mich. Gleichzeitig summiert sie auch alle Ergebnisse (es sind ja eh' fast nur Nullen) und ich habe die Spalte, in der die Übereinstimmung ist.
In dieser Spalte muss nun "nur" noch das Element in der 2. Zeile (des Bereiches posten!) ausgewählt werden -> Index().
---------------
Ich hoffe, die Erläuterung zusammen mit der Schritt-für-Schritt-Aufdröselung der Funktion im Anhang ist halbwegs verständlich - trivial ist es nicht!

Lieber mikele,

grandios!!
Und ja wahrlich:

mikele hat geschrieben: ↑

Sa 4. Aug 2018, 14:32

...
das Ganze ist schon nicht so einfach zu verstehen. Trivial ist es nicht!

Aber deine Erläuterungen haben mich schon viel näher an den Punkt der "Erleuchtung" gebracht. Ich habe mir deine Aufdröselung in Einzelschritte durchgesehen und bin direkt bei Schritt 1 hängen geblieben. [Änderung_1 Anfang:] Du hast in der Datei "Kategorie ermitteln erweitert2" Spalten und Zeilen nach meinem Modell umgetauscht. Etwas ist mir noch nicht klar: Du sagst

mikele hat geschrieben: ↑

Sa 4. Aug 2018, 14:32

Die erste Zeile (Klartext der Kategorie) gehört nicht dazu!

, markierst sie aber in der Tabelle Kategorien mit, als hättest du sie in den Bereich mit hineingenommen. Aber du hast das offenbar nicht getan, denn sonst hätte die 1 in 6/2 ausgegeben werden müssen.

Zur Frage, warum die Kürzel?: Dass "Wein" unter die Kategorie "Genussmittel" fällt, weiß ich auch so. Es geht ja nur um die statistische Erfassbarkeit, und da würden die Klarnamen unnötig viel Platz beanspruchen. Die Klarnamen über den Kürzeln dienen nur als "Knoten im Taschentuch" für die Frage "Was habe ich nochmal wie abgekürzt?" D.h. ich denke, der Bereich sollte erst bei Zeile 2 beginnen, wie du es ja offenbar gemacht hast - siehe oben.[Änderung_1 Ende]

[Änderung_2 Anfang:] Erfolgsmeldung: Ich habe in der Tabelle Kategorien einen Bereich [A4:K43], also 11*40 Zellen markiert und deinen Namen "Posten" vergeben. Wenn ich jetzt in der Tabelle Barausgaben die Matrix {(=Posten(Bn))} erzeuge (n steht für die jeweilige Zeile), bildet sie alle Bereichszellen der Tabelle Kategorien als neue Tabelle mit lauter FALSCH-Einträgen ab, und nur in der Zelle, in der Bn in den Kategorien eingetragen ist, steht dann WAHR (was ja beides das Gleiche ist wie deine Nullen und die Eins).[Änderung_2 Ende]

Soweit erstmal. Ich glaube nicht, dass das schon alles war und ich danach schon vollen Durchblick habe. Mal sehen.

Noch etwas. Ich weiß nicht, ob du Kontakt zu Programmierteams von LO Calc hast. Aber eigentlich ist das, was ich da vorhabe, doch ein ziemlich gewöhnlicher Vorgang, wenn man z.B. die Zusammensetzung und Veränderung von Lagerbeständen statistisch erfassen will. Deshalb bin ich etwas erstaunt, dass es dafür keine Funktion gibt und man so viel Aufwand dafür treiben muss. Ich schlage vor, dass das in Angriff genommen wird.
Wenn du genauso wenig wie Kontakte zu den Programmierern hast: In welches Forum muss man so einen Vorschlag einbringen?

Gruß,
Mau

Hallo,
das mit dem Bereich in der Tabelle Kategorien hast du richtig erkannt. Während ich dir antwortete habe ich die Änderung (horizontal zu vertikal) vorgenommen und dann den Text nicht mehr geprüft. hast du jetzt nachvollzogen - der Aufwand ist hier nötig, da du eine Matrix durchuchen musst und so die üblichen Funktionen (Vergleich (), Verweis (), ...) icht greifen.
Meistens findet man die umgekehrte Herangehensweise: zunächst wählt man die Kategorie und dann den Posten daraus. Das ist dann formeltechnisch einfacher.

Lieber mikele,

ich habe deine Testreihen studiert und leidlich verstanden.
Aber wohl nicht gut genug.
Denn bei mir wird in der Tabelle Barausgaben statt des zugehörigen Kategoriekürzels einfach der Posten noch einmal ausgegeben. Manchmal auch eine 0.
Ich schließe daraus, dass die ausgegebenen Werte nicht einfach den Inhalt von Spalte B ausgeben, sondern schon aus der Tabelle Kategorien kommen. Ich habe aber mal testweise vor die Bereichsnamen bzw. Zellbezüge jeweils $Kategorien. vorgeschaltet, aber da bekam ich - warum auch immer - überall nur die Fehlerausgabe #NAME?.
Ich hänge die Testdatei (ohne die vorgeschalteten Tabellennamen) an.

Gruß,
Mau

Hallo,
bei dir stehen die Kategoriebezeichnungen in der 1. Zeile. Daher: Ist existiert aber noch ein weiteres Problem: Was passiert, wenn du einen Gegenstand eingibst, der in der Kategorieübersicht nicht existiert?
Der Formelteil liefert dann Null und die Formel Index() liefert Unfug (nämlich die gesamte erste Zeile der Matrix posten - damit kannst du aber nichts anfangen.
Um das abzufangen kannst du folgende Variante nehmen: Ich hatte in meinem ursprünglichen Vorschlag darauf verzichten können, da ich für die Spalte B eine Gültigkeitsbedingung (mit Dropdownauswahl) definiert hatte, sodass nur auf der Kategorietabelle definierte Gegenstände eingetragen werden können. Hätte den weiteren Vorteil, dass du die Gegenstände nicht eintippen muss, sondern aus einer Liste auswählst.

Lieber mikele,
absolut perfekt!!

Für alle, die hier mitlesen und die's interessiert, habe ich mikeles letzte (=ultimative ) Formel in der Art von Programmcodes aufgedröselt, weil sie sehr lang ist und man leicht den Überblick verlieren kann. Hängt hier an.

Gruß,
Mau