Zählen-Formel

[Luffy]

Hallo @ all,

Wie in der Überschrift beschrieben habe ich ein Problem mit einer Zählformel, bzw weis nicht ob sich das Problem ohne VBA überhaupt lösen lässt.

Das ist meine Ausgangssituation:


Seite 1

	A	B	C	D	E
1
2	AB	1,00		Abteilung
3
4	VK	1.01		Einzelposition
5	'01	1.01.01
6	'02	1.01.02
7	'03	1.01.03
8	'04	1.01.04

verwendete Formeln
Zelle	Formel	Bereich	N/A
B2	=ZÄHLENWENN($A$1:A2;$A$2)
B4	=ZÄHLENWENN($A$1:A2;$A$2)&".0"&ZÄHLENWENN($A$1:A4;$A$4)
B5:B8	=VERWEIS(2;1/($A$1:$A5=$A$4);$B$1:$B5)&"."&A5

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.15 einschl. 64 Bit

Wir haben hier in Orange die Abteilung zB Sushi.
In Grau kommen dann einzelne Artikel die in diesen Bereich kommen zB Stühle, Tische, Theke ect.
Jede Abteilung hat eine Abteilungsnummer 1,2,3...
Jede Artikelteile hat auch eine Nummer, diese bezieht sich auf die Abteilungsnummer 1.01,1.02,1.03...
die anderen Nummern könnt ihr ignorieren, diese Funktionieren gut.

Wenn ich nun die Artikelzeilen kopiere funktioniert alles noch gut wie folgt im Beispiel:


Seite 2

	A	B	C	D	E
1
2	AB	1,00		Abteilung
3
4	VK	1.01		Einzelposition
5	'01	1.01.01
6	'02	1.01.02
7	'03	1.01.03
8	'04	1.01.04
9
10	VK	1.02		Einzelposition
11	'01	1.02.01
12	'02	1.02.02
13	'03	1.02.03
14	'04	1.02.04
15
16	VK	1.03		Einzelposition
17	'01	1.03.01
18	'02	1.03.02
19	'03	1.03.03
20	'04	1.03.04

verwendete Formeln
Zelle	Formel	Bereich	N/A
B2	=ZÄHLENWENN($A$1:A2;$A$2)
B4,B10,B16	=ZÄHLENWENN($A$1:A2;$A$2)&".0"&ZÄHLENWENN($A$1:A4;$A$4)
B5:B8,B11:B14,B17:B20	=VERWEIS(2;1/($A$1:$A5=$A$4);$B$1:$B5)&"."&A5

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.15 einschl. 64 Bit

Diese zählen auch noch einwandfrei, das Problem habe ich erst sobald ich die 2. bzw mehrere Abteilungen hinzufüge 
Bsp: ich füge Abteilung 2 hinzu und wird auch mit 2 beschrieben das passt. wenn ich dann aber eine Artikelebene hinzufüge passiert das:


Seite 3

	A	B	C	D	E
1
2	AB	1,00		Abteilung
3
4	VK	1.01		Einzelposition
5	'01	1.01.01
6	'02	1.01.02
7	'03	1.01.03
8	'04	1.01.04
9
10	VK	1.02		Einzelposition
11	'01	1.02.01
12	'02	1.02.02
13	'03	1.02.03
14	'04	1.02.04
15
16	VK	1.03		Einzelposition
17	'01	1.03.01
18	'02	1.03.02
19	'03	1.03.03
20	'04	1.03.04
21
22	AB	2,00		Abteilung
23
24	VK	2.04		Einzelposition
25	'01	2.04.01
26	'02	2.04.02
27	'03	2.04.03
28	'04	2.04.04

verwendete Formeln
Zelle	Formel	Bereich	N/A
B2,B22	=ZÄHLENWENN($A$1:A2;$A$2)
B4,B10,B16,B24	=ZÄHLENWENN($A$1:A2;$A$2)&".0"&ZÄHLENWENN($A$1:A4;$A$4)
B5:B8,B11:B14,B17:B20,B25:B28	=VERWEIS(2;1/($A$1:$A5=$A$4);$B$1:$B5)&"."&A5

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.15 einschl. 64 Bit

Eigentlich sollte hier "2.01" in B24 stehen... 

Kann man das i.wie mit einer Formel machen oder benötigt man hierfür VBA ?

[Luffy]

Hallo, 

anbei noch die Datei falls jemand Probieren möchte :)


  Bsp Datei Zählenformel..xlsx (Größe: 11,65 KB / Downloads: 5)

[shift-del]

Hallo Luffy

Etwas anders: Statt viele verschiedene Formeln in einer Spalte lieber ein paar Hilfsspalten mit jeweils einer Formel.

Arbeitsblatt mit dem Namen 'Seite 3'
	A	B	C	D	E	F	G
1
2	AB	1,00	1	1			0
3							0
4	VK	1.01	1.01		1.01		1
5	01	1.01.01	1.01.01			1.01.01	1
6	02	1.01.02	1.01.02			1.01.02	1
7	03	1.01.03	1.01.03			1.01.03	1
8	04	1.01.04	1.01.04			1.01.04	1
9							1
10	VK	1.02	1.02		1.02		2
11	01	1.02.01	1.02.01			1.02.01	2
12	02	1.02.02	1.02.02			1.02.02	2
13	03	1.02.03	1.02.03			1.02.03	2
14	04	1.02.04	1.02.04			1.02.04	2
15							2
16	VK	1.03	1.03		1.03		3
17	01	1.03.01	1.03.01			1.03.01	3
18	02	1.03.02	1.03.02			1.03.02	3
19	03	1.03.03	1.03.03			1.03.03	3
20	04	1.03.04	1.03.04			1.03.04	3
21							3
22	AB	2,00	2	2			0
23							0
24	VK	2.04	2.01		2.01		1
25	01	2.04.01	2.01.01			2.01.01	1
26	02	2.04.02	2.01.02			2.01.02	1
27	03	2.04.03	2.01.03			2.01.03	1
28	04	2.04.04	2.01.04			2.01.04	1

Zelle	Formel
C2	=D2&E2&F2
D2	=WENN(A2="AB";ZÄHLENWENN($A$1:A2;"AB");"")
E2	=WENN(A2="VK";VERWEIS(2;1/(D$1:D2<>"");D$1:D2)&"."&TEXT(WENN(A2="AB";0;G1)+WENN(A2="VK";1;0);"00");"")
F2	=WENN(A2="";"";WENN(ISTZAHL(--A2);WENNFEHLER(VERWEIS(2;1/(E$1:E2<>"");E$1:E2)&"."&A2;"");""))
G2	=WENN(A2="AB";0;G1)+WENN(A2="VK";1;0)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Castor]

Das geht auch ohne Hilfszellen mit nur einer einzigen Formel für alle relevanten Zellen, Luffy:

	A	B	C	D	E
2	AB	1		Abteilung
3
4	VK	1.01		Einzelposition
5	01	1.01.01
6	02	1.01.02
7	03	1.01.03
8	04	1.01.04
9
10	VK	1.02		Einzelposition
11	01	1.02.01
12	02	1.02.02
13	03	1.02.03
14	04	1.02.04
15
16	AB	2		Abteilung
17
18	VK	2.01		Einzelposition
19	01	2.01.01
20	02	2.01.02
21	03	2.01.03
22	04	2.01.04
23
24	VK	2.02		Einzelposition
25	01	2.02.01
26	02	2.02.02
27	03	2.02.03
28	04	2.02.04
29
30	AB	3		Abteilung
31
32	VK	3.01		Einzelposition
33	01	3.01.01
34	02	3.01.02
35	03	3.01.03
36	04	3.01.04
37
38	AB	4		Abteilung
39
40	VK	4.01		Einzelposition
41	01	4.01.01
42	02	4.01.02
43	03	4.01.03
44	04	4.01.04
45
46	VK	4.02		Einzelposition
47	01	4.02.01
48	02	4.02.02
49	03	4.02.03
50	04	4.02.04

Diese Formel lautet:
B2[:B50]:=WAHL(VERGLEICH(LINKS(A2);{"A"."V"."0"};0);"   "&ZÄHLENWENN(A$1:A2;A$2);VERWEIS(2;1/(A$1:A1=A$2);B$1:B1)&".0"&ZÄHLENWENN(INDEX(A$1:A2;VERWEIS(2;1/(A$1:A2=A$2);ZEILE(A$1:A2))):A2;A$4);VERWEIS(2;1/(A$1:A1=A$4);B$1:B1)&"."&A2)
Gruß, Castor

[Luffy]

Hallo euch beiden!

Vielen vielen dank für die Antworten, beide Varianten funktionieren perfekt!

Thema erledigt.

[Luffy]

Hallo nochmal an alle,

die anderen Nummern könnt ihr ignorieren, diese Funktionieren gut.

da hab ich mich wohl zu früh gefreut... bräuchte nochmal eure Hilfe...

Es geht um die selbe Datei, nicht mehr relevante Daten gelöscht und erweitert.

Für mich wieder fast unlösbar trotz viel rum probieren... es will einfach nicht klappen :/

Problem:

Ich füge unter der Artikelüberschrift (Zeile 8) immer wieder Zeilen ein und lösche auch manche wieder, 
wenn ich das mache sollten sich die Zahlen in B9:B15 auch aktualisieren (bzw wenn ich eine Zeile einfüge B9:B16 usw..), 
sowie die Summe in G8 sollte alle Zahlen unter sich addieren bis die nächste leere Zeile kommt. 

Sobald ich eine neue Artikelüberschrift erstelle sollen sie Zahlen wieder mit 01 beginnen.
Hier ein Ausschnitt:


-1-

	A	B	C	D	E	F	G	H
3
4		Nr.	Beschreibung	Stück	Einheit	Einzelpreis	Prei x Stück
5
6		AB	Sushi
7
8		VK	Mittelmöbel	1,00	Stk	VK	630
9		01	Holzteile	1,00	Stk	10	10
10		02	Metallgestell	1,00	Stk	20	20
11		03	Stellfüße	4,00	Stk	30	120
12		04	Stellfüße	4,00	Stk	30	120
13		05	Led-Beleuchtung	1,00	Stk	50	50
14		06	Scannerschiene	4,00	Stk	60	240
15		07	Preisschilder	1,00	Stk	70	70
16

verwendete Formeln
Zelle	Formel	Bereich	N/A
G8	=SUMME(G9:G15)
B9:B15	=0&ZÄHLENWENN(F:F;$F$8)+WENN(B8=$B$8;0;B8)
G9:G15	=F9*D9

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 19.15 einschl. 64 Bit

Hoffe ich konnte dieses Problem einigermaßen gut erläutern, und schonmal vielen dank an alle die sich nochmal Mühe geben :)

anbei noch die neue Test Datei:


  Kalkulation 2016 NEU - Kopie.xlsx (Größe: 19,13 KB / Downloads: 3)

[schauan]

Hallöchen,

das könnte so funktionieren- Formel in B9:
=0&ZÄHLENWENN(F:F;$F$8)+WENN(INDEX(B:B;ZEILE()-1;1)=$B$8;0;INDEX(B:B;ZEILE()-1;1))

[Luffy]

Hallo Schaaun,

die Formel klappt super wenn da nur eine Tabelle ist, wenn ich den Artikelbereich aber kopiere für den nächsten zählt er wie folgt:
02
04
06
08
010...

also wenn es nach den kopieren des Blocks auch funktionieren würde wäre es perfekt. :)

[schauan]

Hallo Luffy,

ich hab hier mal eine etwas andere Variante, die mit benannten Bereichen arbeitet. Wenn die Anzeige der zahlen nicht erwünscht ist, kann man das ja von einem Eintrag in Spalte C abhängig machen.

Arbeitsblatt mit dem Namen '-1- (2)'
	B	C
4	Nr.	Beschreibung
5
6	AB	Sushi
7
8	VK	Mittelmöbel
9	001	Holzteile
10	002	Metallgestell
11	003	Scannerschiene
12	004	Preisschilder
13
14	VK	Hochkantleisten
15	001	Holzteile
16	002	Metallgestell
17	003
18	004

Name	Bezug
Hochkantleisten	='-1- (2)'!$B$14:$G$18
Mittelmöbel	='-1- (2)'!$B$8:$G$12

Zelle	Formel
B9	=TEXT(ZEILE()-ZEILE(Mittelmöbel);"000")
B10	=TEXT(ZEILE()-ZEILE(Mittelmöbel);"000")
B11	=TEXT(ZEILE()-ZEILE(Mittelmöbel);"000")
B12	=TEXT(ZEILE()-ZEILE(Mittelmöbel);"000")
B15	=TEXT(ZEILE()-ZEILE(Hochkantleisten);"000")
B16	=TEXT(ZEILE()-ZEILE(Hochkantleisten);"000")
B17	=TEXT(ZEILE()-ZEILE(Hochkantleisten);"000")
B18	=TEXT(ZEILE()-ZEILE(Hochkantleisten);"000")

[Luffy]

Hallo Schaaun,

Vielen dank für deine Antwort.

Die Formel hab ich jetzt nicht ganz verstanden wie du in die Formel die Namen der Artikel ("Mittelmöbel") rein bekommst oder Anführungszeichen.. :/

Habe die Formel jetzt so verstanden:

Code:

=TEXT(ZEILE()-ZEILE($B$8:$G$18);"00")

(in Zelle B9)

Ist aber wahrscheinlich falsch, weil wenn ich den Block wieder kopiere bleibt der Bereich ($B$8:$G$18) ja fixiert...