Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

SVerweis mit mehreren Bedingungen

SVerweis mit mehreren Bedingungen
21.05.2013 19:12:18
Romi
Hallo,
stehe noch am Anfang in Excel und habe eine Frage.
Ich habe eine Tabelle in denen die Zeiträume der neuen Warenbestellung aufgeführt werden. Die Ware wird abgekürzt durch einen Code und die Monate, in denen sie bestellt werden müssen ebenfalls.
Beispiel:
Baumwolle BW
Monat März H3
Nun wäre es toll, wenn ich per Sverweis oder VBA Code in einem anderen Tab die Waren, die demnächst (habe ein Start und Enddatum festgelegt und will alle erfassen, die dazwischen liegen) bestellt werden müssen, erfassen könnte.
Beispiel:
21.06.2013 BWH3 Baumwolle
27.06.2013 CUM3 Kupfer
Die mittlere Kombination BWH3 wird aus der Ware und dem Monat gebildet.
Komme nicht so recht auf die Lösung, weiß nicht ob es besser per Sverweis oder VBA Code ist.
Könnte mir jemand bitte helfen?
Beispiel Datei habe ich hochgeladen.
https://www.herber.de/bbs/user/85431.xlsm
1000 Dank im Voraus

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
SVerweis mit mehreren Bedingungen = Summenprodukt
22.05.2013 07:19:03
Marc
Moin!
Einen Sverweis mit mehreren Bedingung hätte ich dir gern in Form von Summenprodukt geliefert.
=SUMMENPRODUKT(($C$9:$N$11=prStart))
Allerdings kommt hier nur erstmal die Zahl der zutreffenden Elemente raus, keine Auflistung. Daher hab ich mich für VBA entschieden.
Hier der Code:
Sub Test() Set rng = Sheets("Ware").Range("C9:N" & Rows.Count).SpecialCells(xlCellTypeConstants) Beginn = 3 Sheets("Ware_Neu").Range("a3:C" & Rows.Count).ClearContents For Each cl In rng If cl >= Range("prStart") And cl

Anzeige
Rückfragen
21.05.2013 20:15:42
Erich
Hi Romi,
bei deiner richtigen Mappe bleibt etwas im Dunkel:
Auf dem Blatt "Ware" gibt es sechs Bestellungen, die in den Zeitraum 01.03.-30.06. fallen (können):
- AUF3 in C11 (steht allerdings unter Januar - was auch immer das zu bedeuten hat)
- BWH3 in E9
- CUH3 in E10
und die drei, die du im Blatt "Ware_Neu" aufgeführt hast. Warum nur die letzteren drei?
Soll nur die jeweils letzte Bestellung einer Ware innerhalb des Zeitraums ausgegeben werden?
Was bedeutet es, wenn in F11 ein Juni-Datum steht, aber in der Spalte des April?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Rückfragen
21.05.2013 21:01:59
Romi
Danke für die Rückmeldung Erich,
die drei in "Ware_neu" aufgeführten Bestellungen waren nur als Beispiel gedacht, natürlich sollten hier auch die vor dem 30.06. und nach dem 01.03. erfasst werden.
Ein April Datum in der Januar Spalte bedeutet, dass die Ware, die im Januar bestellt wurde, vorraussichtlich bis zu dem April Datum aufgebraucht sein wird.

ein paar Formeln
22.05.2013 09:59:29
Erich
Hi Romi,
jetzt habe ich das hoffentlich richtig verstanden.
Damit man besser sehen kann, was passiert, habe ich einige Ausgangsdaten in C9:N11 verändert:
Ware

 ABCDEFGHIJKLMN
2 Year2013           
3Start01.03.2013            
4Ende30.06.2013            
5  123456789101112
6WarennameCodeJan 13Feb 13Mrz 13Apr 13Mai 13Jun 13Jul 13Aug 13Sep 13Okt 13Nov 13Dez 13
7  F3G3H3J3K3M3N3Q3U3V3X3Z3
8              
9BaumwolleBW  11.03.13  05.06.13  08.09.13  20.12.13
10KupferCU  22.03.13  21.06.13  20.09.13  22.12.13
11AluminiumAU27.03.13  22.06.13  22.09.13  31.12.13  


Ware_Neu

 ABCDEFG
1     63
2      6
311.03.2013BWH3Baumwolle 41344 15
405.06.2013BWM3Baumwolle   18
522.03.2013CUH3Kupfer   25
621.06.2013CUM3Kupfer 41430 28
727.03.2013AUF3Aluminium   0
822.06.2013AUJ3Aluminium   0
9    41525 0
10      0

Formeln der Tabelle
ZelleFormel
E1=WENN(INDEX(Ware!C$9:N$109;KÜRZEN((ZEILE()-1)/12)+1;REST(ZEILE()-1;12)+1); INDEX(Ware!C$9:N$109;KÜRZEN((ZEILE()-1)/12)+1;REST(ZEILE()-1;12)+1); "")
F1=ZÄHLENWENNS(E1:E101;">="&prStart;E1:E101;"<="&prEnd)
G1{=WENN(F$1<ZEILE(G1); 0;KKLEINSTE(WENN(ISTZAHL(E$1:E$101)*(E$1:E$101>=prStart)*(E$1:E$101<=prEnd); ZEILE(E$1:R$101)); ZEILE(G1)))}
A3=WENN($G1>0;INDEX(E$1:E$101;$G1); "")
B3=WENN($G1>0;INDEX(Ware!B$9:B$109;KÜRZEN($G1/12)+1)&INDEX(Ware!$C$7:$N$7;REST($G1;12)); "")
C3=WENN($G1>0;INDEX(Ware!A$9:A$109;KÜRZEN($G1/12)+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
Die Spalten E und G sind Hilfsspalten.
Die Formel in E1 musst du nach unten kopieren bis mind. E36 (Anz. Waren in Ware-Spalte A * 12 Monate),
die Formel in G1 musst du nach unten kopieren bis zur max. Zahl "Treffer".
F1 ist nur eine einzelne Hilfsszahl.
Auch die Formeln in A3:C3 kannst du nach unten kopieren.
Die Hilfsspalten kannst du in ein anderes Blatt auslagern, wenn sie hier stören.
Hilft das weiter?
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
etwas einfacher
22.05.2013 12:11:31
Erich
Hi Romi,
die Formel in E1 geht auch ohne das WENN:
 EFG
1063
20 6

Formeln der Tabelle
ZelleFormel
E1=INDEX(Ware!C$9:N$11;KÜRZEN((ZEILE()-1)/12)+1;REST(ZEILE()-1;12)+1)
F1=ZÄHLENWENNS(E1:E86;">="&prStart;E1:E86;"<="&prEnd)
G1{=WENN(F$1<ZEILE(G1); 0;KKLEINSTE(WENN(ISTZAHL(E$1:E$86)*(E$1:E$86>=prStart)*(E$1:E$86<=prEnd); ZEILE(E$1:R$86)); ZEILE(G1)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: SVerweis mit mehreren Bedingungen
22.05.2013 12:33:57
Romi
Hallo,
vielen Dank für eure schnelle Hilfe.
Funktioniert beides bestens, habe mich der Übersichthalber für das Makro entschieden!
Vielen Dank nochmal an Erich und Marc!
Gruß

384 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige