Seite 1 von 2 1 2 LetzteLetzte
Ergebnis 1 bis 10 von 11

Thema: Verweisformel

  1. #1
    Registriert seit
    29.08.2007
    Beiträge
    164

    Verweisformel

    Hallo zusammen,

    Als Anhang eine Musterdatei zur Generieung eines Aushanges für eine Buszuteilung.

    Bis anhin habe ich das so gemacht, dass ich in der Tabelle Busaushang alle möglichen Kabinen händisch aufgeführt, und die zwei restlichen Spalten PAX und BUS mit einer Verweisformel ausgefüllt habe. Das führt dazu, dass auch Kabinennummern aufgeführt werden deren Gäste keine Buszuteilung haben oder gar nicht belegt sind. Mit einer bedingten Formatierung wurde in diesen Fällen die Kabinenr in weiss gedruckt, war also nicht ersichtlich und führt zu leeren Zeilen in den drei Anzeigespalten. Und genau das möchte ich nicht.

    Ich möchte, dass in der Tabelle Busaushang die jeweils drei Zellen nur ausgefüllt werden, wenn in der Tabelle Teilnehmer die Zellwerte Spalte J leer sind, also keine Buszuteilung hat.

    Natürlich muss ich dann die Formeln für jede der drei Anzeigespalten für den Aushang entsprechen Anpassend. Effektive "Buszuteilungszeilen" sind es um die 60.

    In der Musterdatei funktionieren die Verweisformeln der Spalten Busaushang B+C, F+G sowie J+K allerdings auch nicht. Komischerweise jedoch in der "scharfen" Tabelle. Möglicherweise ist mir beim Kopieren und verändern für die Musterdatei ein - mir akutell nicht ersichtlicher - Fehler unterlaufen. Aber wenn die Spalten Kabine mit Formel gefüllt werden, müssen die Formeln für die übrigen Zellen wahrscheinlich ohnehin angepasst werden.

    Ich hoffe, dass ich zusammen mit der Mustertabelle das Problem klar genug geschildert habe und danke allen schon zum voraus für die Unterstützung.

    Beste Grüsse
    Smily44
    PS: Ich verwende EXCEL 365
    Angehängte Dateien Angehängte Dateien

  2. #2
    Registriert seit
    28.02.2010
    Ort
    Grossraum Aarau
    Beiträge
    614
    Hallo smily44

    Ich habe die Muster-Datei angesehen. Das Tabellenblatt "Teilnehmer" ist offenbar Grundlage, dieses Tabellenblatt hat einen grundsätzlichen Fehler (oder besser einen Mangel)!

    So sind z.B. die Daten eines Ehepaares in einer Zeile enthalten, also die Vornamen des Ehemannes und der Ehefrau sind in einer Zelle (mit Zeilenumbruch), das gilt auch für das Geburtsdatum, die Nationalität usw. usw.
    Mit derart "schräg" strukturierten Daten, lässt sich keine vernünftige Auswertung (Busaushang) erstellen.

    Wenn Du die Datenstruktur so wählst > ein Teilnehmer = eine Zeile (=Datensatz), stehen Dir für das Auswerten mit einer Pivot-Tabelle oder mit Power Query einige transparente Möglichkeiten offen und das gute an der Sache es braucht keine Formeln und das ganze ist IT-like.
    Freundliche Grüsse
    Turakos

    Das Forum lebt von den Erfahrungen und den Beiträgen der User – ein Feedback ist wünschenswert.

  3. #3
    Registriert seit
    29.08.2007
    Beiträge
    164
    Hallo Turakos,

    Nun, für mich ist Excel ein Arbeitsinstrument und ich brauche das halt in dieser Struktur - ob das nun "schräg" ist oder nicht. Auch ist mir natürlich klar dass ich bei eine Verweisformel nicht auf Zellen refernzieren kann, in denen eigentlich zwei Daten stehen.
    Eine Pivottabelle nützt mir überhaupt nichts, da die zwei Tabellen im Musterfile nur ein ganz kleiner Teil der ganzen Mappe ist. Ich habe zwar mit Pivottabellen keine grosse Erfahrung, aber ich sehe nicht, wie mir eine solche helfen soll eine ansprechende Tabelle zu erhalten für einen Ausdruck einer Liste für die Info an die Gäste.

    In der Tabelle Busaushang refernziere ich ja genau auf Zellen in denen nur eine Info drinsteht. Und wenn es die richtige Formel ist, ist es genau das was ich für meine Arbeit an der Front brauche.

    Beste Grüsse
    Smily44

  4. #4
    Registriert seit
    28.02.2010
    Ort
    Grossraum Aarau
    Beiträge
    614
    Guten Abend smily44

    In der Anlage findest Du bitte 3 Varianten die 3 letzten Tabellenblätter sind "Original", jedoch mit korrekt angepasstem Format (keine Änderung an Deiner Formel) der Kabine im Tabellenblatt 2Teilnehmer.o".

    Ich hoffe Du bist jetz zufrieden.
    Angehängte Dateien Angehängte Dateien
    Freundliche Grüsse
    Turakos

    Das Forum lebt von den Erfahrungen und den Beiträgen der User – ein Feedback ist wünschenswert.

  5. #5
    Registriert seit
    29.08.2007
    Beiträge
    164
    Hallo Turakos,
    Vielen Dank für deinen Lösungsansatz - aber leider ist das nicht das was ich erwarte. Offenbar habe ich mein Problem zu wenig klar geschildert.

    In meiner Originaldatei hat es im Tabellenblatt Teilnehmer bei den Kabinen 101, 102, 104 und 106 keinen Eintrag in der Spalte J, Bus. Das ist kein Irrtum, das muss so sein, es handelt sich um "Hilfspersonal" das keine Buseinteilung hat, weil sie immer mal in einem anderen Bus sind. Aber es sind natürlich nicht bei jeder Reise die gleichen Kabinen die keine Buszuteilung haben.

    Was ich nun möchte ist, dass die Zeilen mit den Kabinen ohne Buszuteilung (in Teilnehmer), also in diesem Fall 101, 102, 104 und 106 gar nicht erst in der Tabelle Busaushang erscheinen und nicht wie aktuell, dass in der Spalte Kabine die Kabinennr. erscheint, die dazugehörenden Zellen in den Spalten Personen und Bus jedoch leer sind.

    Auch empfinde ich die Darstellung mit der Pivottabelle nicht so übersichtlich. Du kannst dir nicht vorstellen wie dumm sich manche Gäste anstellen wenn sie solche Listen interpretieren müssen, d.h. um herauszufinden welchem Bus sie zugeteilt sind.

    Nochmals vielen Dank und e schöns Wuchenend!
    Gruss Smily44

  6. #6
    Registriert seit
    28.02.2010
    Ort
    Grossraum Aarau
    Beiträge
    614
    Hallo smily44

    In der Anlage findest du eine Möglichkeit mit der Kabinen ohne Buszuordnung gefiltert sind.
    Alle drei Tabellen mit der Buszuordnung sind als formatierte Tabellen gebildet für die Anwendung mit Filter, ebenfalls sind die Summenformel angepasst auf die Funktion TEILERGEBNIS.
    Ich habe die Tabellen mit der roten Reiterfarbe angepasst.
    Angehängte Dateien Angehängte Dateien
    Freundliche Grüsse
    Turakos

    Das Forum lebt von den Erfahrungen und den Beiträgen der User – ein Feedback ist wünschenswert.

  7. #7
    Registriert seit
    29.08.2007
    Beiträge
    164
    Hallo Turakos,
    Vielen Dank für deine Bemühungen. Aber leider funktioniert dein Ansatz auch nicht.

    Das Problem mit dem Filter in Spalte "Bus" ist, dass ein gesetzter Filter z,B. in Block 1, die ganze Zeile ausblendet, also auch in den Blöcken 1 + 2 was in diesen beiden Blöcken zu falschen Ausblendungen führt.

    Oder mache ich bei der Formatierung der drei Blöcke als je eine Tabelle einen Fehler, bzw. kann man den Filter so setzen, dass er sich nur über einen bestimmtern Spaltenbereich auswirkt? Das würde dann konkret heisen, dass in den drei Spalten "Bus" je ein individueller Filter mit Spaltenbreich gesetzt werden müsste. Aber ob das überhaupt geht in Excel?

    Ich glaube, wir kommen nicht darum herum schon in der Verweisformel einen Filter zu setze so im Sinne "nur ausführen wenn wenn Wert in Spalte "J" leer ist.

    Tut mir leid, dass du nun solche Mühe hast mit meinem Problem.

    Liebe Grüsse
    Smily44

  8. #8
    Registriert seit
    28.02.2010
    Ort
    Grossraum Aarau
    Beiträge
    614
    Hallo smily44

    Vielleicht passt das? Im Anhang eingetragen - siehe Spalte A Kabine.

    Mit der Matrix-Formel in Spalte B werden nur Kabinen mit einer Buszuweisung in der Tabelle "Teilnehmer.o" eingetragen.

    Die Matrix-Formel eintragen wie eine normale Formel und abschliessen mit den Tasten:

    Ctrl + Shift + Enter
    Angehängte Dateien Angehängte Dateien
    Freundliche Grüsse
    Turakos

    Das Forum lebt von den Erfahrungen und den Beiträgen der User – ein Feedback ist wünschenswert.

  9. #9
    Registriert seit
    29.08.2007
    Beiträge
    164
    Hallo Turakos,
    Ganz herzlichen Dank für diese tolle Unterstützung. Es funktioniert wie ich mir das vorgestellt habe.

    Allerdings habe ich nun noch eine kleine Baustelle.

    Da ja die Tabelle 3 Blöcke à 3 Spalten hat, muss ich den Range in den Verweisformeln jeweils dem Range in den Blöcken anpassen. D.h. in Block 1 werden aus Teilnehmer die Zeilen 2-26, in Block 2 27-51 und in Block 3 52-67 übertragen.

    Bei den ersten zwei Blöcken stimmt das so auch. Im Block 3 muss ich aber den Range mit 51 - 67 in die Formel eintragen, da sonst die Kabine in Teilnehmer/Zeile 51 verloren geht. Ich meine, mit dem Range 27 - 51 in Block 2 sollte doch die Teilnehmer/Zeile 51 im Block 2 erscheinen, genauso wie die die Teilnehmer/Zeile 26 in Block 1.

    Mach ich da ein Überlegungsfehler?

    Nochmals besten Dank für eine wirklich grosse Hilfe und liebe Grüsse
    Smilly44
    PS Im Anhang ein neues, grösseres Musterfile das punkto Umfang der Original-Arbeitsdatei entspricht.
    Angehängte Dateien Angehängte Dateien

  10. #10
    Registriert seit
    28.02.2010
    Ort
    Grossraum Aarau
    Beiträge
    614
    Guten Abend smily44

    Die Datei ...dd.zip habe ich entpackt und das Excel-File ..dd.xlsm angeschaut.

    Ich habe zu meiner Hilfe die Farben der 3 Bus-Blöcke eingetragen, als weiteres Merkmal habe ich den Zeilenbereich von ... bis der 3 Bus-Blöcke eingetragen - die Schriftformatierung habe ich zu meiner besseren Übersicht verkleinert.

    In den drei Bus-Blöcken des Tabellenblattes "Busaushang.o" habe ich bei allen Formeln die Zeilenbereiche korrekt eingetragen, das entsprechende angepasste File findest du bitte im Anhang ..ee.zip.

    Ich denke die Reise kann jetzt beginnen und jeder PAX findet seinen Bus!
    Angehängte Dateien Angehängte Dateien
    Freundliche Grüsse
    Turakos

    Das Forum lebt von den Erfahrungen und den Beiträgen der User – ein Feedback ist wünschenswert.

Seite 1 von 2 1 2 LetzteLetzte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •