Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1280to1284
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Bitte um Hilfe bei Formel gesucht

Bitte um Hilfe bei Formel gesucht
08.10.2012 22:26:14
Sibylle
Guten Abend,
eine Mappe besitzt 12 Tabellen mit den Namen Jan - Dez.
Der Übertragsbetrag von Jan soll in Feb übertragen werden, der von Feb in Mrz usw.
Die Übertragszellen der exportierenden Tabelle haben stets die gleiche Adresse Z102, die Zellenadressen der importierenden Tabellen stets B3.
Muß man dazu 11 mal eine Formel schreiben oder geht dies mit Hilfe der Gruppenverarbeitung oder auf anderem Weg einfacher? Neben dem Übertrag müssen weitere Werte weitergegeben werden, d.h. eine effizientere Lösung würde erheblich weniger Arbeitsaufwand bedeuten.
Für Hinweise besten Dank im Voraus.
Gruß
Sibylle

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Bezug auf das vorherige Blatt
09.10.2012 00:50:21
Erich
Hi Sibylle,
steht irgendwo auf jedem Blatt ein Datum, das zum jeweiligen Monat (Blatt) gehört?
Dann gehts einfacher.
Nehmen wir mal an, in Zelle A2 jedes Blatts steht ein Datum, das im Monat des Blatts liegt.
Dann könnte man in die Blätter Feb bis Dez jeweils in B3 eine Formel schreiben:
 AB
215.04.1999 
3 333

Formeln der Tabelle
ZelleFormel
B3=WENN(MONAT(A2)=1;0;INDIREKT(TEXT(A2-TAG(A2); "MMM")&"!Z102"))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Bezug auf das vorherige Blatt
09.10.2012 00:53:53
Erich
Hi Sibylle,
die Formel kann natürlich auch im Blatt Jan stehen - dafür das WENN.
Im Jan kommt eben 0 raus, weil es keinen Vormonat gibt.
Wenn du die Formel im Blatt Jan weglässt, gehts kürzer ohne das WENN:
=INDIREKT(TEXT(A2-TAG(A2);"MMM")&"!Z102")
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Zusatzfrage
11.10.2012 10:08:27
Sibylle
Hi Erich,
ich danke Dir für Deine Lösung,die perfekt funktioniert. Danke.
Sicher kannst Du mir eine Zusatzfrager beantworten, die ebenfalls mit Gruppenverarbeitung zu tun hat:
Wie bekommt man in die Tabellenblätter Feb - Dez das Datum des ersten Tages eines jeden Monats in der Form TT.MM.JJJJ, wenn im Tabellenblatt Jan in A2 01.01.2013 steht?
Besten Dank im Voraus.
Gruß
Sibylle

Anzeige
Monatserster aus Blattnamen
11.10.2012 17:55:28
Erich
Hi Sibylle,
der Monat ist durch den Blattnamen festgelegt.
Das Jahr könnte man sich jeweils immer aus dem Vormonat!A2 holen oder immer aus Jan!A2.
Hier kommt das Jahr immer aus dem Jan-Blatt:
Jan

 ABCDE
1Vorgabe    
201.01.2013   4711


Feb

 ABCDE
1Monatserster BlattVorblatt 
201.02.2013 FebJantesttext
3 111   
4     
5 4711   

Formeln der Tabelle
ZelleFormel
A2=0+(C2&-JAHR(Jan!A2))
C2=TEIL(ZELLE("Dateiname";A1); FINDEN("]";ZELLE("Dateiname";A1))+1;31)
D2=TEXT(A2-TAG(A2); "MMM")
B3=INDIREKT($D$2&"!Z102")
B5=INDIREKT($D$2&"!E2")


Mrz

 ABCDE
1Monatserster BlattVorblatt 
201.03.2013 MrzFebein Test
3 222   
4     
5 testtext   

Formeln der Tabelle
ZelleFormel
A2=0+(C2&-JAHR(Jan!A2))
C2=TEIL(ZELLE("Dateiname";A1); FINDEN("]";ZELLE("Dateiname";A1))+1;31)
D2=TEXT(A2-TAG(A2); "MMM")
B3=INDIREKT($D$2&"!Z102")
B5=INDIREKT($D$2&"!E2")

Die Reihenfolge der Berechnung ist: C2 - A2 - D2 - (B3, B5 und beliebige weitere Vormonatsdaten).
Die Formeln funktionieren auch, wenn die Blätter nicht zeitlich sortiert sind.
Es muss nur zu jedem Monat außer Jan ein Vormonatsblatt geben.
Die Formel in C2 (Blattname) ist von http://www.excelformeln.de/formeln.html?welcher=95
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Monatserster aus Blattnamen
11.10.2012 18:23:39
Sibylle
Hallo Erich,
ich danke Dir für Deine Lösung, mit der ich leider nicht klar komme.
Ausgangspunkt ist eine Mappe mit 12 Tabellen mit den Namen Jan - Dez.
Im Blatt Jan steht in A2 01.01.2013.
Danach werden die Tabellen Feb - Dez als Gruppenverarbeitung markiert.
Wie ist dann fortzufahren, wenn man in den weiteren 11 Tabellen in A2 das Datum haben möchte, das zum Tabellenblattnamen passt?
Ich würde mich freuen, wenn Du mir die einzelnen Schritte aufzeigen würdest.
Besten Dank im Voraus.
Gruß
Sibylle

Formeln in Blattgruppe eintragen
11.10.2012 18:52:09
Erich
Hi Sibylle,
els erstes solltest du nachsehen, ob in allen Blätter die Zellen C2 und D2 frei sind,
denn die werden jetzt als Hilfszellen gebraucht.
Wenn sie nicht frei sind, wähle dafür andere Zellen, die du dann natürlich anstelle von C2 und D2
in die anderen Formeln schreiben musst.
Wenn die 11 Blätter zusammen markiert sind, kannst du die Formeln für A2, C2 und D2 in die Zellen kopieren,
dazu weitere Formeln wie z. B. die in B3.
Ich war davon ausgegangen, dass du schon Erfahrung hast mit dem Eintragen von Formeln
oder Werten in mehrere Blätter gleichzeitig.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Formeln in Blattgruppe eintragen
11.10.2012 19:26:16
Sibylle
Hallo Erich,
es klappt nun vorzüglich. Besten Dank für Deine Tipps. Klasse.
Wünschenswert wäre eine Lösung ohne die Hilfszellen C2 und D2.
In den Tabellenreitern sind die Monate als Text gespeichert.
Gibt es da keine Möglichkeiten diese in einer Zelle A2 in der Form TT.MM.JJJJ auszugeben?
Ist da Excel an den Grenzen und VBA gefragt?
Gruß
Sibylle

Nicht verstanden?
12.10.2012 01:07:23
Erich
Hi Sibylle,
"Wünschenswert wäre eine Lösung ohne die Hilfszellen C2 und D2."
Nun ja, ich hatte versucht, die Formeln für dich ("Kaum Excel/VBA-Kenntnisse") möglichst einfach zu halten.
Bist du wirklich nicht in der Lage, aus den beiden Formeln
C2: =TEIL(ZELLE("Dateiname";A1); FINDEN("]";ZELLE("Dateiname";A1))+1;31)
A2: =0+(C2&-JAHR(Jan!A2))
die Formel
A2: =0+(=TEIL(ZELLE("Dateiname";A1); FINDEN("]";ZELLE("Dateiname";A1))+1;31)&-JAHR(Jan!A2))
herzustellen?
Dann solltest du doch besser mit den Hilfszellen arbeiten...
"In den Tabellenreitern sind die Monate als Text gespeichert.
Gibt es da keine Möglichkeiten diese in einer Zelle A2 in der Form TT.MM.JJJJ auszugeben?
Ist da Excel an den Grenzen und VBA gefragt?"

Das verstehe ich nicht. Die obigen Formeln tun genau das. (Abgesehen cavon, dass sie das Jahr JJJJ
dem im Jan vorgegebenen Datum entnehmen, weil das Jahr ja wohl kaum aus den Blattnamen zu holen ist.)
Woher kamen den die Monatsersten in meinem Beispiel, wenn sie nicht durch die Formeln berechnet wurden.
Das hatte ich dazu gechrieben:
"der Monat ist durch den Blattnamen festgelegt.
Das Jahr könnte man sich jeweils immer aus dem Vormonat!A2 holen oder immer aus Jan!A2.
Hier kommt das Jahr immer aus dem Jan-Blatt:"
Noch eine Bemerkung zur zweiten Hilfszelle "Vormonat" in D2. Du musst sie natürlich nicht nutzen.
Es würde dir nur das Schreiben aller Formeln erleichtern, in denen Werte aus dem Vormonat vorkommen.
Wie möchtest du Übertragungen wie z. B.
B3: =INDIREKT($D$2&"!Z102")
ohne den Vormonat in Hilfszelle D2 formulieren?
Eine Frage: Hast du meine beiden Beiträge mit dem Titel "noch ein Thread zum Thema" nicht zur Kenntnis genommen?
Könnte sein, dass ich künftig keine Arbeit mehr in diese beiden Threads investiere.
Im anderen Thread habe ich gesehen, dass du ohnehin schon VBA einsetzt.
Dann kannst du dir die Bestimmung des Datums in A2 aus dem Blattnamen völlig ersparen.
(Und ich hätte mir die Mühe, die Formeln für C2 und A2 fürdich aufzuschreiben, auch erspart.)
Wenn das Blatt mit VBA angelegt und benannt wird, kann das Datum doch einfach in A2 geschrieben werden.
Und auch Formeln, die in alles Blättern (evtl. außer Jan) identisch sind, kann VBA gleich schreiben.
Probier mal das hier aus - es erstellt eine neue Mappe mit 12 Blättern: Option Explicit Sub Makro2() Dim mm As Byte Const iJahr As Integer = 2013 Workbooks.Add xlWBATWorksheet ' neue Mappe mit 1 Blatt With ActiveWorkbook For mm = 1 To 12 Sheets.Add After:=Worksheets(Worksheets.Count) ' neues Blatt With ActiveSheet .Cells(2, 1) = DateSerial(iJahr, mm, 1) ' Monatserster in A2 .Name = Format(.Cells(2, 1), "MMM") ' Blattname aus A2 If mm = 1 Then .Range("A7") = 10 ' Beispielwert Else ' Hilfszelle D2=Vormonat .Cells(2, 4) = Format(DateSerial(iJahr, mm - 1, 1), "MMM") ' Übernahmen aus Vormonat (sinnlose Beispiele) .Range("A7").FormulaLocal = "=2*INDIREKT(D2&""!A7"")" .Range("B8").FormulaLocal = "=INDIREKT(D2&""!A2"")+INDIREKT(D2&""!A7"")" End If End With Next mm Application.DisplayAlerts = False Sheets(1).Delete Application.DisplayAlerts = True End With End Sub Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
Doch, jetzt. Tausend Dank
12.10.2012 10:34:37
Sibylle
Hallo Erich,
jetzt ist die Sache klar.
Deine Erläuterungen sind angekommen.
Ich danke Dir ganz herzlich für Deine Geduld, mir die Problematik näher zu bringen.
Ich freue mich sehr, dass diese Frage nun geklärt, verstanden ist.
Diese Formel wird in Zukunft sicher häufig verwendet werden.
Entschuldigen möchte ich mich dafür, dass ein zweiter thread eröffnet wurde, ein Verstoß gegen die Regeln, der in Zukunft nicht wieder vorkommen wird.
Dir wünsche ich einen guten Tag. Du hast mir wirklich sehr geholfen.
Viele Grüße
Sibylle

Danke für deine Rückmeldung, und ...
12.10.2012 14:02:12
Erich
Hi Sibylle,
"Diese Formel wird in Zukunft sicher häufig verwendet werden."
Welche der Formeln meinst du?
Zu meinem Makro2 hast du gar nichts gesagt. Hast du das ausprobiert?
Reicht dass nicht völlig aus - und kommst du beim Datum in A2 dann nicht ganz ohne Formel aus?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Danke für deine Rückmeldung, und ...
12.10.2012 15:49:35
Sibylle
Hallo Erich,
ich meine die von Dir zusammengesetzte Formel.
Das Makro und die Formeln habe ich getestet, beides läuft perfekt.
Die Lösung hat mein Wissensstand erweitert, die Lösung selbst gilt einer Bekannten, die noch nie mit Makros zu tun hatte.
Schön, dass ich nun mehrere Lösungen habe, die sorgfältig gespeichert wurden.
Dank für Deine Arbeit.
Viele Grüße
Sibylle

noch ein Thread zum Thema
11.10.2012 18:16:06
Erich
Hi Sibylle,
warum stellst du eine Frage in zwei Threads? Das verursacht leicht vermeidbare Doppelarbeit!
Hier der andere Thread:
2 Fragen zur Gruppenverarbeitung vom 11.10.2012 13:54:21
Grüße aus Kamp-Lintfort von Erich
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige