Microsoft Excel

Herbers Excel/VBA-Archiv

ADRESSE() als Bezug greift nicht

Betrifft: ADRESSE() als Bezug greift nicht von: Jörg
Geschrieben am: 07.04.2014 21:06:20

Hallo Forumsteilnehmer,

ich wende mich im Auftrag meiner Kollegen an Euch mit der Bitte um Unterstützung.
Wir haben ein kleines Problemchen, und zwar bekommen wir über Datenimport vier Spalten in ein Tabellenblatt kopiert, die ersten beiden sind Mitarbeiternamen, die zwei anderen die Umsatzzahlen.
Der Aufbau ist eher suboptimal, deswegen kopieren wir die Spalten dann untereinander für die Auswertung der Summe der letzten 3 Umsätze von Mitarbeiter x.
Jetzt sind wir, bzw. der Kollege, der sich etwas besser mit Excel auskennt, bei excelformeln auf eine Formel gestoßen, die uns eigentlich das ganze "Umbauen" ersparen könnte, aber die Formel akzeptiert die richtig erstellten Zellen (oder Bezüge??) in der Summenformel nicht.

Zwei Beispiele:

 ABCDEF
1aa3631 0
2xa4452 0
3ax1855 120
4xa1912  
5ax4641  
6xa1053  
7aa3017  
8xa2487  
9aa1216  
10aa1836  
11aa445  
12aa51999  
13aa5049  
14aa32100  

Formeln der Tabelle
ZelleFormel
F1{=SUMME(ADRESSE(REST(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($A:$B)*999); ZEILE($1:$3)); 999); KÜRZEN(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($C:$D)*999); ZEILE($1:$3))/999); 4))}
F2{=SUMME(ZEICHEN(KÜRZEN(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)*999); ZEILE(1:3))/999)+66)&REST(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)*999); ZEILE(1:3)); 999))}
F3=D5+D3+C8
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen




Oder auch so:

 ABCDEF
1aa3631 0
2aa4452 0
3ax1855 1140
4aa1912  
5ax4641  
6xa1053  
7aa3017  
8xa2487  
9aa1216  
10aa1836  
11xa445  
12ax51999  
13xa5049  
14xx32100  

Formeln der Tabelle
ZelleFormel
F1{=SUMME(ADRESSE(REST(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($A:$B)*999); ZEILE($1:$3)); 999); KÜRZEN(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($C:$D)*999); ZEILE($1:$3))/999); 4))}
F2{=SUMME(ZEICHEN(KÜRZEN(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)*999); ZEILE(1:3))/999)+66)&REST(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)*999); ZEILE(1:3)); 999))}
F3=D14+D12+D5
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen




Also es sollte immer die Summe in Spalte C:D errechnet werden, abhängig von den letzten Mitarbeiter x in Spalte A:B

Danke für Denkanstöße
Es grüßt Jörg und Kollegen...

  

Betrifft: Eine ADRESSE ist, wie der Name schon ... von: Luc:-?
Geschrieben am: 07.04.2014 22:00:05

…sagt, nur eine Adresse und Adressen sind bekanntlich Text, den man nicht summieren kann, Jörg;
addieren kann man nur den Inhalt von Zellen (Objekte), die echte Zahlen enthalten. Das muss hier erst noch geschehen. Seht euch dazu doch einfach mal einen diesbzgl Thread weiter unten auf dieser Seite an. Da hatte Hari ein analoges Problem.
Gruß Luc :-?


  

Betrifft: AW: ADRESSE() als Bezug greift nicht von: Tino
Geschrieben am: 07.04.2014 22:07:10

Hallo,
meinst Du so? (In A:B sind Namen keine Zahlen)

 ABCDEFG
1a 3631 xx
2aa4452 1201195
3ax1855   
4aa1912   
5ax4641   
6xa1053   
7aa3017   
8x 2487   
9aa1216   
10aa1836   
11xa445   
12ax51999   
13xa5049   
14xx32100   

Formeln der Tabelle
ZelleFormel
F1=INDEX(A:A;VERGLEICH("*";A:A;-1))
G1=INDEX(B:B;VERGLEICH("*";B:B;-1))
F2=SUMMEWENN(A:A;F1;C:C)
G2=SUMMEWENN(B:B;G1;D:D)


Gruß Tino


  

Betrifft: widersprüchlliche Angaben ... von: der neopa
Geschrieben am: 08.04.2014 08:15:56

Hallo Jörg und Kollegen,

... einerseits schreibst Du:
- "Summe der letzten 3 Umsätze von Mitarbeiter x"
- "immer die Summe in Spalte C:D"


Euer 2. Beispiel berücksichtigt die "x"-Mitarbeiter in Spalte A überhaupt nicht und im 1. Beispiel müsste nach Eurer Definition nicht =D5+D3+C8 sondern =C8+C6+D5 berechnet werden.

Was also gilt nun wirklich?

Gruß Werner
.. , - ...


  

Betrifft: AW: widersprüchlliche Angaben ... von: Jörg
Geschrieben am: 08.04.2014 11:26:03

Hallo,

erstmal Danke für die Antworten!

zum Verständnisproblem:

das ertse Beispiel Nach Import original:

 ABCD
1aa3631
2xa4452
3ax1855
4xa1912
5ax4641
6xa1053
7aa3017
8xa2487
9aa1216
10aa1836
11aa445
12aa51999
13aa5049
14aa32100




Und dann wird es von uns so untereinander kopiert:

 ABC
1a 36
2x 44
3a 18
4x 19
5a 46
6x 10
7a 30
8x 24
9a 12
10a 18
11a 4
12a 51
13a 50
14a 32
15a 31
16a 52
17x 55
18a 12
19x 41
20a 53
21a 17
22a 87
23a 16
24a 36
25a 45
26a 999
27a 49
28a 100




Somit können wir dann die letzten drei Werte des Mitarbeiter "x" auswerten!

Bsp. 2:

 ABCD
1aa3631
2aa4452
3ax1855
4aa1912
5ax4641
6xa1053
7aa3017
8xa2487
9aa1216
10aa1836
11xa445
12ax51999
13xa5049
14xx32100




wird zu diesem "zurecht kopiert":

 ABC
1a 36
2a 44
3a 18
4a 19
5a 46
6x 10
7a 30
8x 24
9a 12
10a 18
11x 4
12a 51
13x 50
14x 32
15a 31
16a 52
17x 55
18a 12
19x 41
20a 53
21a 17
22a 87
23a 16
24a 36
25a 45
26x 999
27a 49
28x 100




Es werden also die Spalten hintereinander "verkettet", da die Daten fortlaufend sind, und somit sind die letzten drei "x" eben manchmal verteilt, entweder in beiden Spalten, nur in der ersten oder nur in der zweiten!


Mit Indirekt um die Summe() haben wir das jetzt mal probiert, da gibt die Formel #Bezug! wieder, nur Indirekt() liefert die Formel aber immer nur den Wert vom letzten "x", und vernachlässigt die beiden vorletzten.

Da diese Auswertung mehrmals am Tag kommt, und immer unterschiedlich lang ist, wollten wir halt das manuelle kopieren ersparen und es mit einer von den zwei Formeln berechnen.

Mittlerweile haben wir ein bisschen gegoogelt, aber eine Lösung haben wir noch nicht, dass wir Excel "klarmachen können" die richtigen Textwerte der Formeln auch als Bezug anzunehmen! :-(

Hoffentlich ist jetzt unser Anliegen besser beschrieben, ansonsten versucht sich mal ein Kollege mit der Beschreibung!

Nochmals Danke für die Aufmerksamkeit und evtl. Lösungsansätze!
Jörg und Kollegen


  

Betrifft: Lösung gefunden!! von: Jörg
Geschrieben am: 08.04.2014 12:36:38

Hallo Helfer,

gegoogelt und unseren Excelspezi noch gefragt, damit sind wir auf die Lösung gekommen!!!
Mit Doppelsumme und Indirekt funktioniert das ganze so wie gewollt, obwohl in der Formelauswertung nur #WERT! erscheint, versteht das einer?

Also, Problem vorerst gelöst, Danke fürs Lesen und mithelfen!

Jörg und Kollegen!!

 ABCDEF
1aa3631 1140
2aa4452 1140
3aa1855 1140
4aa1912  
5ax4641  
6xa1053  
7aa3017  
8xa2487  
9aa1216  
10aa1836  
11xa445  
12ax51999  
13xa5049  
14xx32100  

Formeln der Tabelle
ZelleFormel
F1{=SUMME(SUMME(INDIREKT(ADRESSE(REST(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($A:$B)*999); ZEILE($1:$3)); 999); KÜRZEN(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($C:$D)*999); ZEILE($1:$3))/999); 4))))}
F2{=SUMME(SUMME(INDIREKT(ZEICHEN(KÜRZEN(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)*999); ZEILE(1:3))/999)+66)&REST(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)*999); ZEILE(1:3)); 999))))}
F3=D14+D12+D5
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen




 ABCDEF
1aa3631 120
2xa4452 120
3ax1855 120
4xa1912  
5ax4641  
6xa1053  
7aa3017  
8xa2487  
9aa1216  
10aa1836  
11aa445  
12aa51999  
13aa5049  
14aa32100  

Formeln der Tabelle
ZelleFormel
F1{=SUMME(SUMME(INDIREKT(ADRESSE(REST(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($A:$B)*999); ZEILE($1:$3)); 999); KÜRZEN(KGRÖSSTE(WENN($A$1:$B$14="x";ZEILE($1:$14)+SPALTE($C:$D)*999); ZEILE($1:$3))/999); 4))))}
F2{=SUMME(SUMME(INDIREKT(ZEICHEN(KÜRZEN(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)*999); ZEILE(1:3))/999)+66)&REST(KGRÖSSTE(WENN(A1:B14="x";ZEILE($1:$14)+SPALTE(A:B)*999); ZEILE(1:3)); 999))))}
F3=D5+D3+C8
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen





  

Betrifft: noch immer nicht ganz eindeutig ... von: der neopa
Geschrieben am: 08.04.2014 12:51:31

Hallo Jörg und Kollegen,

... bezogen auf die "zusammen kopierten" Daten reicht folgende MATRIX-Formel:

{=SUMME(WENN(ZEILE(1:999)=KGRÖSSTE(WENN(A1:A999="x";ZEILE(1:999));{1.2.3});C1:C999))}

bzw. in Bsp. 2 auch schon vor dem Kopieren

{=SUMME(WENN(ZEILE(1:999)=KGRÖSSTE(WENN(B1:B999="x";ZEILE(1:999));{1.2.3});D1:D999))}

Aber wenn ich es richtig verstehe, möchtet ihr das Ergebnis immer erzielen und ohne die Daten vorher zusammen zu kopieren. Auf Beispiel 1 würde also die angegebene 2. Formel noch nicht zutreffen, weil dort in Spalte B nur zwei "x" vorhanden sind.

Nach welcher verbal formulierten Regel sind dann die letzten drei "x" in Spalte A und B zu finden? Immer zuerst in Spalte C und danach in Spalte A oder wie?

Gruß Werner
.. , - ...


  

Betrifft: AW: noch immer nicht ganz eindeutig ... von: Jörg
Geschrieben am: 08.04.2014 13:06:44

Hallo Werner,

die Lösung haben wir!!

zu Deinem Verständnissproblem:

die Spalten A und B sin zusammenhängende Bereiche, es geht zb. bis A14 und der Bereich geht in B1 weiter bis B14, das gleiche in Spalte C und D

Bsp.:

 ABCDE
1ertses xa5579 
2aa1885 
3zweites xa9519 
4aa2618 
5aa9797 
6aa7492 
7aviertes x3469 
8aa4776 
9afünftes x2884 
10aa8781 
11aa9550 
12asechstes y6813 
13drittes xa8779 
14asiebentes x9265die letzten 3 x
15     
16oder    
17     
18ertses xa5579 
19aa1885 
20zweites xa9519 
21aa2618 
22aa9797 
23aa7492 
24aviertes x3469 
25aa4776 
26afünftes x2884 
27aa8781 
28aa9550 
29aa6813 
30drittes xa8779 
31aa9265die letzten 3 x
32     
33oder    
34     
35ertses xa5579 
36aa1885 
37zweites xa9519 
38aa2618 
39aa9797 
40aa7492 
41aa3469 
42aa4776 
43aa2884 
44aa8781 
45aa9550 
46aa6813 
47drittes xa8779 
48aa9265die letzten 3 x



Aber wir haben ja jetzt eine Lösung die rechnet, was wir wollen!!

Viele Grüße
Jörg und Kollegen


 

Beiträge aus den Excel-Beispielen zum Thema "ADRESSE() als Bezug greift nicht"