Excel - #BEZUG! vermeiden?
Hallo,
ich mache eine Schichtplanung mit Excel. Auf einem separaten Reiter (Stunden) ermittlte ich mit ZÄHLENWENN Funktion die Häufigkeit der Namen in einem Bereich um so die Einsatzstunden zu ermitteln. Ist ein Monat vorbei kopiere ich diesen in einen weiteren separaten Reiter. Dann schneide ich den restlichen Schichtplan ab dem neuen Monat aus und füge ihn an die Stelle ein wo der Vormonat war. Im Reiter (Stunden) steht aber dann in jeder Zelle in der vorher die Einsatzstunden ermittelt wurden #BEZUG! (in der Formel steht #BEZUG! an der Stelle wo vorher der Zellenbereich stand aus dem die Daten erhoben werden sollen).
Wie kann ich das verhindern? Ein Absolut setzen der Zellbezüge hilft auch nicht. Und zum googlen ist das Problem zu kompliziert, zumindest finde ich nichts gleichartiges. Als Excel-Laie bin ich nun mit meinem Latein am Ende.
Ich möchte, dass nach einfügen des neuen Monats die Werte übernommen werden. Der Bezug auf zB Zelle A1 ändert sich ja nicht, auch wenn ich den Inhalt durch Überschreiben verändert habe. Ich hoffe es ist klar was ich meine und wo mein Problem liegt.
Schonmal danke für jeglichen Hinweis.
1 Antwort
Du wirst die alten Werte drin lassen müssen.
Angenommen du hast ein Buch im Regal stehen. Der Bezug sagt dass dein Passwort in dem Buch auf Seite 53 in der 45 Zeile steht. Jedesmal wenn du dein Passwort brauchst guckst du dort nach. Jetzt schmeißt du das Buch weg. Jetzt weisst du zwar immer noch, dass dein Passwort auf Seite 53 Zeile 45 steht, du kannst es aber nicht mehr nachlesen weil das Buch ja weg ist.
Genau so geht es Excel hier.
Der Bereich aus dem die Daten kommen sollen darf sich hier nicht gelöscht werden.
Tut es auch mit der INDIREKT-Formel zum Beispiel. Leider kann ich mir deinen Tabellenaufbau noch nicht richtig vorstellen weshalb ich mich etwas schwer tue eine Lösung zu finden.
Puh okay, ich versuchs mal. In Tabelle 1 stehen im Zellbereich A1:D9 Namen. Sagen wir mal uA Müller. In Tabelle 2 weist die ZÄHLENWENN Funktion mit Bezug auf den Zellbereich in Tabelle 1 die Häufigkeit von Müller aus. Nun nehme ich in Tabelle 1 den Bereich E1:H9, schneide ihn aus und füge ihn auf A1 ein. Dann soll mir trotzdem in Tablle 2 die ZÄHLENWENN Funktion die Häufigkeit von Müller ausweisen für den Bereich A1:D9. Tut sie aber nicht. Statt des Zellbereiches A1:D9 steht hier nun #BEZUG!.
Also wenn ich den Bereich mit Strg X ausschneide und in Tabelle 2 Einfüge dann passt sich der Bereich bei mir automatisch an.
Tabelle1 Zelle F2: =ZÄHLENWENN(Tabelle1!A1:D9;"Müller")
Wenn ich jetzt in Tabelle1 den Bereich A1:D9 mit Strg+X ausschneide und in A1 auf Tabelle2! mit Strg+V wieder einfüge andert sich die Formel in F2 auf:
=ZÄHLENWENN(Tabelle2!A1:D9;"Müller")
Danke auf jeden Fall schonmal für deine Mühen, aber ich glaube wir schreiben ein wenig aneinander vorbei. Ich versuch nochmal anders.
Tabelle 1 Zelle A1 =Müller, B1 =MÜller, C1 = Müller, D2 = Müller, D3= Müller, etc.
Tabelle 2 Zelle A1 =ZÄHELENWENN(Tabelle1!A1-D9;"Müller")
Dann wirft mir Zelle A1 in Tablle 2 zB 8 aus, weil in dem Bereich A1-D9 in Tabelle 1 Müller 8 Mal vorkommt.
So nun ebenfalls in Tabelle 1;
Tabelle 1 Zelle E1 =Müller, F1 = Müller, G1 =Müller, H2 =Müller, H3 =Müller, etc.
nun schneide ich den Zellbereich E1-H9 in Tabelle 1 aus und setzte in in den Bereich A1-D9 in eben der selben Tabelle 1 ein.
Ergebnis:
Tabelle 2 Zelle A1 =ZÄHLENWENN(Tabelle1!#BEZUG!;"Müller")
warum behält die Formel nicht die Zellbereiche A1-D9 sondern ersetzt sie durhc #BEZUG!?
Ok hab es verstanden. Ersetze =ZÄHLENWENN(Tabelle1!A1:D9;"Müller")
durch =ZÄHLENWENN(INDIREKT("Tabelle1!A1:D9");"Müller") und dein Problem ist gelöst.
It worked! Vielen Herzlichen Dank. Das hat sich gelohnt ;-)
Ich will eigentlich das der Bezug sagt, guck da, oben erstes Brett drittes Buch von links ist Herr der Ringe. Wenn ich jetzt Herr der Ringe rausnehme und Harry Potter reinstelle soll der Bezug sagen; erstes Brett drittes Buch von links steht Harry Potter. Das muss doch gehen.