Microsoft Excel

Herbers Excel/VBA-Archiv

SVERWEIS - der unterste Wert soll gezogen werden | Herbers Excel-Forum


Betrifft: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 14:45:56

Hallo liebes Forum,

ich habe ein riesiges Problem und hoffe, dass mir hier geholfen werden kann. Ich erhalte wöchentlich aus unserer Zentrale eine riesige Datei mit knapp 1.000 Zeilen und sicherlich 35 Spalten. Vorne stehen die Vertriebseinheiten und dann kommen die entsprechenden Zahlen. Die Zahlen muss ich mir in eine neue Auswertung zur entsprechenden Vertriebseinheit ziehen, wobei die Zeile für eine bestimmte Vertriebseinheit in der Ursprungsdatei von Woche zu Woche eine andere sein kann. Vor den Vertriebseinheiten steht jeweils angegeben, welcher Region die Vertriebseinheit zugeordnet ist. Und das ist mein Problem: Unter den jeweiligen Vertriebseinheiten steht erneut die Region (in der Spalte davor) und dahinter dann die Regionssummen. Wie kann ich einen SVERWEIS basteln, der mir bei der Region auch den Wert der Region (und nicht den der ersten Vertriebseinheit, denn davor steht ja auch die Region) zurückspielt.

Hier ein Beispiel:

Region A Vertriebseinheit A 1.000
Region A Vertriebseinheit B 500
Region A Vertriebseinheit C 500
Region A 2.000
Region B Vertriebseinheit A ...

In meiner Zieldatei steht dann:

Region A (Und hier würde der Verweis mit dem Ergebnis 2.000 folgen)

Was ich schon versucht habe:

- Mit Index und Vergleich in Array-Formeln
- Mit Verweis und 1/, um von unten zu suchen

Beides würde funktionieren, allerdings streikt beim Aktualisieren mein Rechner und sagt, dass nicht genügend Ressourcen zur Verfügung stehen, obwohl ich schon alle anderen Programme vorher geschlossen habe. Ich muss halt recht viele Daten ziehen.

Wäre toll, wenn jemand eine Lösung für mich hätte und ich hoffe, ich habe mich verständlich ausgedrückt.

  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: ludicla
Geschrieben am: 21.01.2010 14:47:47

Hallo Dirk,

Deine Datendatei schreit nach der Pivot-Tabelle.

Gruss Ludicla.


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 14:50:54

Hallo Ludicla,

danke für die schnelle Antwort, allerdings würde das auch nicht funktionieren, weil

a) meine Zieldatei ein bestimmtes Layout haben muss und
b) die Pivottabelle ja ebenfalls die Summe der Region mitaddieren würde, denn so ohne weiteres ist für Excel ja nicht erkenntlich, dass hier die Summe gezogen wurde.


  

Betrifft: klingt relativ banal von: WF
Geschrieben am: 21.01.2010 15:16:45

Hi,

folgende Arrayformel:
{=INDEX(B:B;MAX((A1:A999="Region A")*ISTZAHL(B1:B999)*ZEILE(1:999)))}

ARRAY-Formel {=geschweifte Klammern} nicht eingeben;
Abschluß der Formel mit gleichzeitig Strg / Shift / Enter (statt Enter allein); - das erzeugt sie.

Salut WF



http://www.excelformeln.de/
die ultimative Formelseite


  

Betrifft: AW: klingt relativ banal von: Dirk
Geschrieben am: 21.01.2010 15:20:49

Danke für die Antwort! Da ich dann allerdings insgesamt 770 Array-Formeln in meinem Excel-Sheet habe, streikt mein Rechner mit dem o.g. Hinweis, dass nicht genügend Ressourcen zur Verfügung stehen. Gibt es nicht einen einfacheren Weg?


  

Betrifft: kapier ich nicht von: WF
Geschrieben am: 21.01.2010 15:26:41

35 Spalten sind 35 Formeln und nicht 770 ?

WF


  

Betrifft: AW: kapier ich nicht von: Dirk
Geschrieben am: 21.01.2010 15:37:38

Bei mir betroffen sind 35 Einheiten (Zeilen) und 22 Spalten (verschiedene Daten). Ds macht 770 Formeln.


  

Betrifft: Exklusivität .. SVERWEIS von: Mike
Geschrieben am: 21.01.2010 15:33:57


Hey Dirk,

der Sverweis funktioniert schon, Du musst ihm nur die Exklusivität aufzwingen.


Tabelle1

 ABCDEFGHI
9                 
10              TOTALE 
11    Region AVertriebseinheit A1000    Region A2000
12    Region AVertriebseinheit B500       
13    Region AVertriebseinheit C500       
14Region A  Region A2000         
150               
16                 
17                 

verwendete Formeln
Zelle Formel
A11 =WENN(LINKS(D11; 1)="V"; ""; C11)
I11 =SVERWEIS(H11; A11:D14; 4; FALSCH)
A12 =WENN(LINKS(D12; 1)="V"; ""; C12)
A13 =WENN(LINKS(D13; 1)="V"; ""; C13)
A14 =WENN(LINKS(D14; 1)="V"; ""; C14)
D14 =SUMME(E11:E13)
A15 =WENN(LINKS(D15; 1)="V"; ""; C15)

Tabellendarstellung in Foren Version 4.28



Gruss
Mike


  

Betrifft: AW: Exklusivität .. SVERWEIS von: Dirk
Geschrieben am: 21.01.2010 15:42:59

Vielen Dank! Das verstehe ich, allerdings müsste ich dann Woche für Woche die Ursprungsdatei unserer Zentrale anpassen. Genau das will ich ja umgehen. Früher war in diesen Dateien die Regionsbezeichnung in der Summe um ein Leerzeichen ergänzt, was für mich als Anwender problemlos umzusetzen war. Leider machen die das nicht mehr.


  

Betrifft: AW: Exklusivität .. SVERWEIS von: Mike
Geschrieben am: 21.01.2010 15:49:42


Hey Dirk,

es gibt nichts ärgerlicheres als sich mit immer wieder ändernden Formularen rumzuschlagen.
Entweder dauernd anpassen oder mit der Zentrale den Dialog für "nervenberuhigenderes
Arbeiten" suchen .. ;-)

Gruss
Mike


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 16:08:42

Hier mal ein etwas übersichtlicheres Beispiel:

https://www.herber.de/bbs/user/67388.xls


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Mike
Geschrieben am: 21.01.2010 16:17:54


Hey Dirk,

dann setzt Du den Sverweis so auf, dass er zum Tragen kommt, wenn in B nichts
drin steht, ergibt dann die 3'900.

Und wenn Du noch die Totale von MR A, MR B und MR C benötigst, dann gleiches Vorgehen
(wenn in A nix steht oder in C).

Gruss
Mike


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 16:24:04

Hört sich toll UND einfach an! Auch wenn die Frage eher eine Anfängerfrage ist, wie verknüpfe ich die beiden Bedingungen? Mit der WennDann-Formel kann ich doch nicht anfangen, oder?


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Josef Ehrensberger
Geschrieben am: 21.01.2010 16:22:17

Hallo Dirk,

in diesem Fall würde ich mit einer Hilfspalte arbeiten, dann brauchst du nur für
diese Spalte eine Marixformel, allse ander geht über eine einfache Indexformel.

Tabelle1

 ABCDEFGHIJKL
1RegionMRZahl 1Zahl 2Zahl 3Zahl 4Zahl 5Zahl 6Zahl 7Zahl 8  
2Region AMR A1200<- Diese Daten machen mir die Probleme000000 13
3Region AMR B1500<- Diese Daten machen mir die Probleme000000 29
4Region AMR C1200<- Diese Daten machen mir die Probleme000000 42

Formeln der Tabelle
ZelleFormel
C2=INDEX(Beispiel!D$7:D$50;$L2)
D2=INDEX(Beispiel!E$7:E$50;$L2)
E2=INDEX(Beispiel!F$7:F$50;$L2)
F2=INDEX(Beispiel!G$7:G$50;$L2)
G2=INDEX(Beispiel!H$7:H$50;$L2)
H2=INDEX(Beispiel!I$7:I$50;$L2)
I2=INDEX(Beispiel!J$7:J$50;$L2)
J2=INDEX(Beispiel!K$7:K$50;$L2)
L2{=KKLEINSTE(WENN((Beispiel!$A$7:$A$50="")*(Beispiel!$B$7:$B$50=B2); ZEILE($1:$44)); 1)}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4




Gruß Sepp



  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 16:25:20

Auch vielen Dank für diese Antwort, aber leider hilft mir das nicht, weil ich dann jede Woche die Quelldatei anpassen müsste. Genau das will ich umgehen.


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Josef Ehrensberger
Geschrieben am: 21.01.2010 16:26:56

Hallo Dirk,
wieso musst du die Quelldaten anpassen? Die Formel sucht sich genau das gewünschte raus,
egal wo es steht.


Gruß Sepp



  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 16:37:09

Oh, vielen Dank und sorry für meine (vor-) schnelle Antwort. Da ich mit dieser Art der Formeln noch nicht so vertraut bin, versuche ich mich mal reinzudenken.


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 16:59:44

Hallo Josef, leider kapier ich's nicht. Aber vielleicht sind meine Excel-Kenntnisse für diese Aufgabe zu beschränkt. Ich werde wohl den manuellen Weg wählen und in der Quelldatei hinter die entsprechenden Bezeichnungen vor den Summen eine Leertaste einbauen. Danke trotzdem für die nette Hilfe!

Dirk


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Josef Ehrensberger
Geschrieben am: 21.01.2010 17:01:04

Hallo Dirk,

warum so schnell aufgeben? Was verstehst du den nicht?


Gruß Sepp



  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 17:09:52

So langsam dämmerts mir. Ich muss mit der Matrixformel beginnen, also die KKLEINSTE-Formel. Damit ermittle ich die entsprechende Zeilennummer. Dann ziehe ich mir in den einzelnen Feldern die entsprechenden Daten. Das die Theorie! Jetzt werde ich mich noch einmal mit der Funktion INDEX auseinandersetzen. Vielen lieben Dank schon einmal, ich glaube ich fange an zu verstehen.

Liebe Grüße
Dirk


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Josef Ehrensberger
Geschrieben am: 21.01.2010 17:12:09

Hallo Dirk,

na siehst du.

Wenn du weitere Fragen hast, oder irgendwo nicht weiter kommst,
dann nur zu, wie helfen dir gerne.


Gruß Sepp



  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 17:34:17

Lieber Sepp,

ich hab's verstanden und so umgesetzt. Trotzdem sagt mein Rechner "Microsoft Excel kann diesen Vorgang mit den verfügbaren Ressourcen nicht ausführen. Zwecks Vollständigkeit: Ich habe die Bereiche von $1:$1000 gezogen, weil die Quelldatei ca. 900 Zeilen hat.


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Josef Ehrensberger
Geschrieben am: 21.01.2010 17:38:40

Hallo Dirk,

also wegen der 35 Matrixformeln geht kein halbwegs ausgestatteter Rechner in die Knie.

Hast du in der Mappe sonst auch noch Matrixformeln?


Gruß Sepp



  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 17:41:02

Nein, nur 35 SVERWEISE und ein paar Direktzellbezüge in andere Arbeitsmappen. Es ist echt zum Verzweifeln habe sogar schon meinen Rechner neu gestartet. Vielleicht sollte ich morgen mal schauen, ob das Problem immer noch besteht. Deine Formeln waren aber der Knaller !!! Die helfen mir auch für künftige Projekte.

Liebe Grüße
Dirk


  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Josef Ehrensberger
Geschrieben am: 21.01.2010 17:43:49

Hallo Dirk,

ok, wenn's gar nicht geht, dann haben wir immer noch die Option,
die Daten per Makro "herauszuziehen".


Gruß Sepp



  

Betrifft: AW: SVERWEIS - der unterste Wert soll gezogen werden von: Dirk
Geschrieben am: 21.01.2010 17:45:22

Noch einmal: Vielen Dank! Melde mich wieder, wenn's morgen auch nicht funzt.


Beiträge aus den Excel-Beispielen zum Thema "SVERWEIS - der unterste Wert soll gezogen werden"