Hilfe bei SVerweis Formeln

[graeserjan]

Hallo werte Calc-freaks,

ich habe eine (für mich ) komplizierte Tabelle erstellt,
es ist ein Personaleinsatzplan der bei eintragen in der Tabelle "Monatsübersicht" Z Bsp. "F" automatisch die Zeit von 8-14Uhr in "Woche 1" markieren soll.
Da ich aber es nicht mit Buchstaben hinbekommen habe, habe ich z.Bsp, für früh die "1" verwendet.
Das wäre mein erstes Problem.
Nur wenn ich z.Bsp die "4" eintrage nimmt er von alleine die "3" oder umgekehrt.
Das ist mein 2. Problem.

Es wäre toll wenn mir jemand die korrekte Formel mitteilen würde, oder Hinweise geben kann was ich verbessern kann (Z.Bsp. das in dem Bereich der ausgefüllt werden soll nicht in jeder Zelle eine Formel steht, das macht die Datei sehr groß.)
denn ich bin kein studierter Informatiker sondern ein im Handel arbeitender Mensch, dem mit dieser Tabelle die Arbeit etwas erleichtert werden soll.

Vielen Dank schon einmal für Eure Hilfe.

Einen schönen Tag wünscht Jan Gräser

P.S. da die Datei zu groß zum posten ist, maile ich die auch gerne zu

[Gert Seler]

Hallo Jan,
die folgende Adresse bietet sehr viele Vorlagen an :

http://www.live.prooo-box.org/templates/de/index.html

da dürfte auch für Deine Frage eine Vorlage zur Verfügung stehen.
(Urlaubspläne;Schichtdienste und ähnliches)
Mit "Bedingter-Formatierung" und "Formatvorlagen" werden diese gestaltet.
Siehe hier : http://www.ooowiki.de/ZellVorlagen?high ... edingte%29
und hier : http://www.ooowiki.de/CalcFormatBedingt ... edingte%29

mfg
Gert

[AhQ]

Hallo Jan,

ganz hab ich nicht verstanden, was Du willst. Du hast einen Monatsplan und wenn da wo "F" steht, soll wo die Uhrzeit eingetragen werden?

Für den SVerweis brauchst Du als erstes mal eine extra Tabelle irgendwo in Deiner Datei, in der in Spalte 1 der Suchbegriff steht (in Deinem Fall also wohl F usw) und in den folgenden Spalten die Werte oder Texte, die dann in der Zielzelle angezeigt werden sollen. Der SVerweis hat 4 Parameter, der erste ist die zelle mit dem Suchparameter, der Zweite gibt den Zellbereich der Tabelle mit den ganzen Ersetzungswerten an (wobei wichtig ist, das Spalte1 dieses Zellbereichs die Spalte mit den Suchkriterien ist), Spalte 3 gibt die Spalte an, in der die Ersetzungswerte oder -texte stehen (als Ziffer, also die Spalte mit den Suchbegriffen ist 1, die nächste ist die 2 etc.). Der letzte Parameter gibt an, ob die erste Spalte der Ersetzungstabelle sortiert vorliegt oder nicht. Wenn nicht, (was meist der Fall sein dürfte), muß da eine 0 (Null) stehen, damit der SVerweis Dir die richtigen Werte ausspuckt.

Genauer kann ich Dir die Formel gerade nicht schreiben, da dazu ein paar Infos fehlen.

Kannst Du nicht die Geschichte mit dem Monatsplan in eine eigene Datei packen und hier reinstellen? Dann müßten wir nicht so sehr raten, wie das Ganze aussehen soll.

Viele Grüße
AhQ

[graeserjan]

Hallo werte Helfer,

vielen Dank schon einmal für die Hilfe.

Ich habe die Datei gekürzt (nur eine Woche und ein Ma) um Sie hochzuladen. Deshalb sind da ein paar Referenzfehler drin und es geht nur Woche 1.
Aber es sollte so funktionieren:
ich hinterlege in der Tabelle "Kuerzel" was ich für Schichten in der Firma habe.
In der "Monatsübersicht" trage ich dann nur am jeweiligen Tag ein wer was für Schicht hat.
Durch die Referenz trägt er in der Tabelle Woche 1 dann die genauen Zeiten von-bis ein. Dort werden dann auch die Arbeitszeiten., Zuschläge, Pausen etc berechnet.

Mein Problem bis dahin:
ich kann keine Namen oder Buchstaben nehmen, sondern es geht nur mit Ziffern. Warum?
Wenn manche Zellen als "Zahl" formatiert sind geht es nicht sondern nur als Text. Warum?
Manchmal trag eich eine 4 ein und er überträgt eine 3.
Das verstehe ich nicht. Vielleicht habt Ihr noch einen Tip wenn Ihr mein "Chaos" vor Augen seht.

Schönen Feierabend und viele Grüße sendet Jan Gräser

Ich habe morgen Punkt 2 und 4 also arbeite ich Früh zur Warenanlieferung und dann weiter bis Feierabend.

[AhQ]

Hallo Jan,

ich denke, daß Problem Deiner Formel und dem Einsatz von Buchstaben ist ein kleiner Satz aus der Calc-Hilfe zur VERWEIS()-Formel:

"Der Suchvektor für VERWEIS muss geordnet sein, andernfalls liefert die Suche keine nutzbaren Ergebnisse."

Ich hab mal ein bißchen gespielt und das ganze auf SVERWEIS umgebaut. Folgendes hab ich gemacht:

- Tabelle Kuerzel hab ich zwei Bereiche wegen der Übersichtlichkeit definiert, einerseits "kuerzel" (B4:AK9), andererseits "Abk", was nur der Bereich der Kürzel in Spalte B ist
- in "Woche 1" hab ich in Zeile 2 Zahlen eingefügt, die der SVERWEIS als 3. Parameter hernimmt
- bislang nur im Samstag hab ich den SVerweis eingefügt, mit WENN und ISTNV fang ich eventuelle Fehlermeldungen ab
- in Spalte G hab ich beim Samstag mal über Daten - Gültigkeit ein Aufklappfeld eingebaut, damit die Sache bißchen komfortabler wird
- in dem Samstagsbereich hab ich die Bedingte Formatierung ein bißchen geändert. Unterschiedliche Farben kannst Du, denk ich, über verschiedene Länge()= und die entsprechenden Wortlängen der Kürzel bekommen. Da wurde vor kurzem Mal ein Link hier gepostet zu einem Makro, mit dem man wesentlich mehr bedingte Formatierungen als die drei Standard machen kann. Das solltest Du dir vielleicht dann noch installieren. Der Link zur OO-Wiki ist folgender:

http://www.ooowiki.de/KomplexeBedingteFormatierungen

Ich hoffe, ich konnte Dir etwas helfen und wünsch viel Spaß beim Angleichen der übrigen Blätter!

Viele Grüße
AhQ

[AhQ]

So, jetzt bin ich es grad nochmal.

Ich hab die Formel nochmal ein kleines bißchen umgebaut und hab anstelle der Bedingten Formatierung jetzt Vorlage() reingebaut. Dafür mußt Du bei den Formatvorlagen (F11) für jede Schicht, die es gibt, eine eigene Vorlage erstellen, die genau so heißt, wie die Schicht.

Schau Dir mal an, ob es gefällt.

Viele Grüße
AhQ

[Gert Seler]

Hallo Jan,
wer lesen kann, ist klar im Vorteil ! ! !

Siehe meinen ersten Beitrag.

Wenn Du dein Gehirnschmalz nich bemühen möchtest, dann bin ich draußen.

mfg
Gert

[AhQ]

Hallo Gert,

schraub doch mal den Tonfall ein bißchen runter. Wenn ich auf Deinen Link zur Prooo-Box klicke, bekomme ich eine Fehlermeldung, die anderen behandeln die bedingte Formatierung. Wenn Du dir die Datei von Jan angesehen hast, dann siehst Du, daß er mit VERWEIS gearbeitet hat, der eine Sortierung des Suchvektors verlangt und Calc deswegen nicht so gearbeitet hat, wie er sich das erhoffte.

Wieso Du ihm auf seine erste Beschreibung des Problems hin Links zur Bedingten Formatierung schickst, ist ein Geheimnis, welches wohl nur Du weißt, Jan jetzt aber schief von der Seite anzugehen, er möge hier sein "Gehirnschmalz" bemühen, finde ich mehr als daneben. Aus meiner Sicht der Dinge hat er vollkommen korrekt gehandelt.

In diesem Sinne

Komm wieder runter...

AhQ

[balu]

Hallo Gert,

manchmal ist es sinnvoll, das man auch die Links überprüft die man angibt.
Denn http://www.live.prooo-box.org/templates/de/index.html ist falsch.
Richtig ist http://live.prooo-box.org/templates/de/index.html.

Nicht jede Adresse fängt mit "www" an, wie unschwer hier am Forum zu erkennen ist.
Aber solche fehler können jeden mal passieren (will mich nicht davon ausschließen).

Noch ein allgemeiner Tipp an Jan, aber nicht nur an ihn.
Sollte mal eine Calc-Datei zu groß sein um sie hier anzuhängen, dann sollte man mal überprüfen ob in ihr Bilder und/oder Grafiken drin sind. Wenn dem so ist, löschen! Das macht sehr viel aus.

Gruß
balu

[graeserjan]

Hallo werte User,

vielen Dank für Eure Hilfe. Besonders AhQ natürlich.
Ich werde mich mal dran machen und das umsetzen was Du sagtest. Aber heute nicht mehr. Bin ausgepowert von der Arbeit.
Ich muß jetzt nur noch schauen, ob er am Ende auch die Stunden zusammenrechnet. Ich hatte das mit der Formel "Anzahl" geteilt duch 2 geregelt. Da wurden die Ziffern gezählt. Mal schauen ob er auch die Namen "früh" etc. zählt.

Bis dahin, schönen Gruß aus Coburg an alle die mir geholfen haben.

Natürlich auch an Gert. Es ist nicht so das ich nicht will. Leider fehlt mir für dieses Wissen was Du von mir erwartest (wenn ich mir die von AhQ "zusammengebastelten" Verweisformeln anschaue ist das kein Pappenstiel) die Zeit, die Ausbildung zum Informatiker einfach alles.
Ich denke Du fährst Auto, aber kannst die Einspritzpumpe sicher nicht selber einstellen.
Ich möchte keine Diskussion beginnen was "Grundwissen" sein sollte und was andere für "Fachwissen" halten.
Vielen Dank für die Hilfe. es ist super das es solche Foren gibt.

Jan Gräser

[AhQ]

Hallo Jan,

Ich hatte das mit der Formel "Anzahl" geteilt duch 2 geregelt. Da wurden die Ziffern gezählt. Mal schauen ob er auch die Namen "früh" etc. zählt.

ANZAHL2() heißt Dein Freund dafür, Anzahl zählt "nur" Zahlen, Anzahl2 auch Zeichenketten etc

Viele Grüße
AhQ

[graeserjan]

Hallo AhQ,

die Anzahl2 Formel zählt aber auch die Zellen mit in denen Formeln stehen. Also alle.
Gibt es da noch eine andere Möglichkeit? Sonst steht immer die selbe Stundenzahl drin....

Kannst Du mir bitte sagen wie Du Auswahlbox hinbekommen hast? Ist wirklich sehr komfortabel.....
Aber wenn ich in der Tabelle "Kuerzel" weitere Schichten einstellen will, erscheinen die nicht in der Auswahlbox...

Vielen Dank schon mal

Jan Gräser

[AhQ]

Hallo Jan,

stimmt, das mit Anzahl2 und Formeln zählen hab ich übersehen. Im Grunde bleibt ja die Stundenzahl pro Schicht immer gleich, oder? Kannst Du dann vielleicht in der Kuerzel-Tabelle hinter die Kürzel einfach noch eine Spalte machen mit der Anzahl der Stunden pro Schicht und dann später mit SVerweis darauf zugreifen? Der soll dann einfach in der Wochenübersicht schauen, welche Schicht in Spalte G steht und entsprechend dann aus der Kürzel-Tabelle die Stunden rausholen. Da mußt Du dann halt nur aufpassen, wenn sich während dem Jahr mal die Stundenzahl ändern sollte, daß es Dir dann nicht alle Stundenberechnungen mit verändert.

Die Auswahlbox ist ganz einfach zum Hinbekommen. Du gehst auf Daten - Gültigkeit (oder drückst Alt+t, dann e), in dem Menü, das sich dann öffnet, wählst Du bei "Zulassen" den Begriff "Zellbereich" aus und gibst in das Feld unten ("Quelle") "Abk" ein. Letzteres ist der absolute Verweis auf den Bereich, in dem die Schichten stehen, die ausgewählt werden sollen und wurde von mir einfach mal so genannt. Und das erklärt auch, warum Du keine anderen Schichten angeben kannst, weil Du diesen Bereich mit verändern mußt.

Das machst Du, indem Du Strg+F3 drückst, dann "Abk" suchst, darauf klickst, den Bereich entsprechend Deiner Bedürfnisse veränders, dann "Ändern" drückst und dann noch "OK". Wenn Du nicht beide drückst, tut sich nix und man wundert sich später. Bei mir eine beliebte Fehlerquelle...

Ist jetzt alles klar? Wenn nicht, steh ich gerne zur Verfügung!

Viele Grüße
AhQ

[graeserjan]

Hallo AhQ,

vielen Dank,
ich werde es probieren.
Warscheinlich werde ich aber dann bei den Ziffern bleiben müssen.
Häufig kommt es vor das, jemand eine halbe Stunde mehr oder weniger machen muß. Z.Bsp. erreichen der Wochenarbeitszeit bei Geringverdiener o.ä.
Vielleicht komme ich morgen früh dazu. Muß erst mittag anfangen, aber leider ist die kleine krank und kann nicht in den Kindergarten. Sie meint auch immer so wie Papa auf dem "Klavier" (Tastatur) rumhämmern zu müssen.

Ich hoffe Du hast weniger Streß als ich. Momentan ist absolut der Wurm drin.

MfG Jan Gräser

[AhQ]

Hallo Jan,

mir hat das jetzt keine Ruhe gelassen, daß hier keine sinnvolle Lösung dasteht. Ich hätte jetzt mal nen anderen Vorschlag. Ich bezieh mich jetzt mal auf die Datei DienstplanVorlage, da ich nicht weiß, wie es bei Dir letztlich aussieht. Wie wäre es in Spalte AR mit der Formel

=ZÄHLENWENN($H80:$AI80;G80)/2

Das Zählenwenn zählt in dem angegebenen Bereich, wie oft der Begriff aus dem Gültigkeitsfeld vorkommt und halbiert den.

Das ist doch das, was Du brauchst? Dann frag ich mich, warum wir beide da nicht eher drauf gekommen sind... aber besser spät als nie!

Gehts der Kleinen besser

Viele Grüße
AhQ