Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.04.2024 21:55:37
Preiserhöhung über mehrere Jahre berechnen
17.04.2024 20:19:32
Endzeitpunkt berechnen aus d:hh:mm errechnen
17.04.2024 18:57:33
Errinnerungsfunktion
17.04.2024 16:56:58
Stunden addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1148to1152
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1148to1152
1148to1152
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suchkriterium mit 2 Werten aus Tabellenblatt 1

Suchkriterium mit 2 Werten aus Tabellenblatt 1
RoB
Hiho!
Bin nun mit meiner gigantischen Exceldatei von heute Morgen Meilen weiter! Jedoch...
Gegeben sind:
TB1: "Stammdaten": Viele diverse Rubriken nach Datum, zzgl. Kalenderwoche (auf y-Achse) sortiert
TB2: "Scorecard": Eine fette Scorecard mit diversen Rubriken (auf y-Achse!) auf 53 Kalenderwochen sortiert
In TB1 wurden die Rubriken mit einer Hilfszeile NUMMERIERT.
In TB2 habe ich stattdessen in einer Hilfsspalte vor alle Rubriken genau die jeweilige NUMMER aus TB1 gepackt.
Ich möchte nun z.B., dass Excel in TB1 die 2 Suchkriterien "KW 1" (als "1" formatiert) und alle Werte der KW1 für die Rubrik "22" sucht, SUMMIERT und in der gewünschten Zelle in TB2 reinschreibt.
Frage ist: Wie verflucht mach ich das? :P
Beste Grüße und schonmal schönen dank!
RoB
AW: Suchkriterium mit 2 Werten aus Tabellenblatt 1
12.04.2010 18:11:20
Ramses
Hallo
Das geht mit Summenprodukt.
Wie die Formel aber aufgebaut sein muss kann ich dir nicht sagen, da ich deinen Datei nicht kenne.
Bei Excel-Gut solltest du die Formel aber anpassen können
Tabelle3

 ABCDEFGHIJ
1KWDatumRubrikWert  GesuchtRubrikErgebnis 
2112.04.2010221  1223 
3113.04.2010222      
4114.04.2010231      
5215.04.2010231      
6216.04.2010231      
7217.04.2010231      
8118.04.2010231      
9119.04.2010231      
10120.04.2010231      
11          
12          

Formeln der Tabelle
ZelleFormel
I2=SUMMENPRODUKT((A2:A10=G2)*(C2:C10=H2)*D2:D10)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruss Rainer
Anzeige
AW: Suchkriterium mit 2 Werten aus Tabellenblatt 1
12.04.2010 20:40:35
RoB
Hallo Rainer,
danke für die schnelle und ausgiebige Antwort! Mit Summenprodukt habe ich auch herum experimentiert. Hört sich alles plausibel und logisch an, aber irgendwie will Excel nicht!
Im Grunde ist meine Situation an Deinem Bsp. zu erklären, nur dass die Daten links eben im TB1 ("Produktionsstunden 2010") gesucht werden und die "Rubriken" dort in einer Zeile stehen.
Hier meine modifizierte Formel:
=SUMMENPRODUKT('Produktionsstunden 2010'!C15:C379=B10)*('Produktionsstunden 2010'!A14:DP14=E6) *('Produktionsstunden 2010'!AA15:AA380)
B10=29
E6=1
Als Ergebnis bekomme ich nur ein schönes "#WERT!" zu sehen. Was mach ich falsch?
RoB
Anzeige
AW: Suchkriterium mit 2 Werten aus Tabellenblatt 1
12.04.2010 21:15:18
RoB
Hier die Bsp.Datei:

Die Datei https://www.herber.de/bbs/user/69032.xls wurde aus Datenschutzgründen gelöscht


Tricky,.. aber es geht
12.04.2010 22:52:49
Ramses
Hallo
Summenprodukt etwas vergewaltigt
Tabelle1

 ABCDEFGHI
1Angenommen...        
2TB1:        
3         
4  RubrikRubrikRubrikRubrikRubrikRubrik 
5  112233445566 
6DatumKW1221234 
704.02.200911003541001221100 
807.03.200911445621232323 
904.02.200911003100197294100 
1007.03.20092533333 
1104.02.2009210044442 
1207.03.20092233215112233 
1304.02.200931226101418 
14 (...)       
15         
16         
17TB2:        
18  KWKWKWKW   
19 Hilfskennung1234   
20Rubrik X332021260   
21Rubrik Y228132820   
22Rubrik Z4433218100   
23Rubrik S5533829140   
24Rubrik R11214338120   
25         
26         
27         

Formeln der Tabelle
ZelleFormel
A7=DATUM(2009;1;35)
A8=DATUM(2009;2;35)
A9=DATUM(2009;1;35)
A10=DATUM(2009;2;35)
A11=DATUM(2009;1;35)
A12=DATUM(2009;2;35)
A13=DATUM(2009;1;35)
C20=SUMMENPRODUKT(($B$7:$B$13=C$19)*INDIREKT(ADRESSE(7;VERGLEICH($B20;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B20;$C$5:$H$5;0)+2)))
D20=SUMMENPRODUKT(($B$7:$B$13=D$19)*INDIREKT(ADRESSE(7;VERGLEICH($B20;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B20;$C$5:$H$5;0)+2)))
E20=SUMMENPRODUKT(($B$7:$B$13=E$19)*INDIREKT(ADRESSE(7;VERGLEICH($B20;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B20;$C$5:$H$5;0)+2)))
F20=SUMMENPRODUKT(($B$7:$B$13=F$19)*INDIREKT(ADRESSE(7;VERGLEICH($B20;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B20;$C$5:$H$5;0)+2)))
C21=SUMMENPRODUKT(($B$7:$B$13=C$19)*INDIREKT(ADRESSE(7;VERGLEICH($B21;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B21;$C$5:$H$5;0)+2)))
D21=SUMMENPRODUKT(($B$7:$B$13=D$19)*INDIREKT(ADRESSE(7;VERGLEICH($B21;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B21;$C$5:$H$5;0)+2)))
E21=SUMMENPRODUKT(($B$7:$B$13=E$19)*INDIREKT(ADRESSE(7;VERGLEICH($B21;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B21;$C$5:$H$5;0)+2)))
F21=SUMMENPRODUKT(($B$7:$B$13=F$19)*INDIREKT(ADRESSE(7;VERGLEICH($B21;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B21;$C$5:$H$5;0)+2)))
C22=SUMMENPRODUKT(($B$7:$B$13=C$19)*INDIREKT(ADRESSE(7;VERGLEICH($B22;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B22;$C$5:$H$5;0)+2)))
D22=SUMMENPRODUKT(($B$7:$B$13=D$19)*INDIREKT(ADRESSE(7;VERGLEICH($B22;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B22;$C$5:$H$5;0)+2)))
E22=SUMMENPRODUKT(($B$7:$B$13=E$19)*INDIREKT(ADRESSE(7;VERGLEICH($B22;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B22;$C$5:$H$5;0)+2)))
F22=SUMMENPRODUKT(($B$7:$B$13=F$19)*INDIREKT(ADRESSE(7;VERGLEICH($B22;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B22;$C$5:$H$5;0)+2)))
C23=SUMMENPRODUKT(($B$7:$B$13=C$19)*INDIREKT(ADRESSE(7;VERGLEICH($B23;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B23;$C$5:$H$5;0)+2)))
D23=SUMMENPRODUKT(($B$7:$B$13=D$19)*INDIREKT(ADRESSE(7;VERGLEICH($B23;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B23;$C$5:$H$5;0)+2)))
E23=SUMMENPRODUKT(($B$7:$B$13=E$19)*INDIREKT(ADRESSE(7;VERGLEICH($B23;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B23;$C$5:$H$5;0)+2)))
F23=SUMMENPRODUKT(($B$7:$B$13=F$19)*INDIREKT(ADRESSE(7;VERGLEICH($B23;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B23;$C$5:$H$5;0)+2)))
C24=SUMMENPRODUKT(($B$7:$B$13=C$19)*INDIREKT(ADRESSE(7;VERGLEICH($B24;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B24;$C$5:$H$5;0)+2)))
D24=SUMMENPRODUKT(($B$7:$B$13=D$19)*INDIREKT(ADRESSE(7;VERGLEICH($B24;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B24;$C$5:$H$5;0)+2)))
E24=SUMMENPRODUKT(($B$7:$B$13=E$19)*INDIREKT(ADRESSE(7;VERGLEICH($B24;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B24;$C$5:$H$5;0)+2)))
F24=SUMMENPRODUKT(($B$7:$B$13=F$19)*INDIREKT(ADRESSE(7;VERGLEICH($B24;$C$5:$H$5;0)+2)&":"&ADRESSE(13;VERGLEICH($B24;$C$5:$H$5;0)+2)))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
"+2" in der Formel addiert den Spaltenversatz, weil der Such/Vergleichsbereich ja erst in C beginnt anstelle von A
Gruss Rainer
Anzeige
AW: Tricky,.. aber es geht
13.04.2010 10:04:02
RoB
Guten Morgen Rainer!
Bin zurück im Büro und hab' mich wahnsinnig über Deinen Geistesblitz gefreut! Deine Formel sieht super aus!
Leider steht jetzt (hoffentlich zum letzten Mal) ein Hindernis im Weg: Die Umschreibung auf die TB1 "Produktionsliste 2010"!
Beim Umschreiben Deiner Formel wird diese natürlich etwas komplexer und Fehler konnten sich einschleichen, aber ich denke es liegt jetzt nur noch am Teil "adresse([Zeile];[Spalte];[Abs.];[A1];[Tabellenname])"... Wie schreibe ich es, damit er die Zeilen und Spalten aus TB1 nimmt?
Lieben Gruß,
RoB
P.s.: Hier die umgeschriebene Formel:
=SUMMENPRODUKT('Produktionsstunden 2010'!C15:C379=Scorecard!E6) *INDIREKT(ADRESSE('Produktionsstunden 2010'!15:15;VERGLEICH(Scorecard!B10; 'Produktionsstunden 2010'!E14:DP14;0)+3)&":"&ADRESSE('Produktionsstunden 2010'!379:379; VERGLEICH(Scorecard!B10;'Produktionsstunden 2010'!E14:DP14;O)+3))
(Beim herumexperimentieren kamen häufig die Fehlermeldungen "#Wert" und "#Name".)
Anzeige
AW: Tricky,.. aber es geht
13.04.2010 10:46:56
fcs
Hallo Rob,
du hast dich am Ende der Formel vertippt. E14:DP14;O)+3)) Das O (OOOOOOOOOhhhhh) muss eine 0 (Null) sein.
Gruß
Franz
AW: Tricky,.. aber es geht
13.04.2010 10:56:04
RoB
Moin Franz!
Danke - Ja, hast Recht! Habe es korregiert, aber der Fehler besteht weiterhin :D
Versuche bei "adresse" auch ganze Zeit Varianten wie...
"adresse(15;vergleich(Scorecard!B10; 'Produktionsstunden 2010'!E14:DP14;0)+3;1;wahr;'Produktionsstunden 2010'!)"
...aber irgendwie komme ich damit nirgends an... :(
AW: Tricky,.. aber es geht
13.04.2010 15:27:41
Ramses
Hallo
Du hast das doch in der Hilfe schon gelesen, warum machst du es nicht so
ADRESSE('Produktionsstunden 2010'!379:379; VERGLEICH(Scorecard!B10;'Produktionsstunden 2010'!E14:DP14;0)+3);A1;'Produktionsstunden 2010')
Ungetestet, sollte aber tun
Gruss Rainer
Anzeige
Korrektur...
13.04.2010 15:31:07
Ramses
Hallo
Gerade erst gesehen.
Die Zeile darf natürlich nur EINMAL geschrieben werden, also anstelle von
ADRESSE('Produktionsstunden 2010'!379:379; VERGLEICH(Scorecard!B10;'Produktionsstunden 2010'!E14:DP14;0)+3);A1;'Produktionsstunden 2010')
muss das so lauten
ADRESSE('Produktionsstunden 2010'!379; VERGLEICH(Scorecard!B10;'Produktionsstunden 2010'!E14:DP14;0)+3);A1;'Produktionsstunden 2010')
Die andere Adressenangabe muss gleich aufgebaut sein, mit dem Verweis halt auf Zeile 15,... auch nur einmal
Gruss Rainer
Korrektur der Korrektur...
13.04.2010 15:46:45
Ramses
Hallo
Ich habe die Formel nun mal nachgebaut, so muss die Syntax lauten
Beispiel:
=SUMMENPRODUKT((Tabelle1!A2:A10=17)*INDIREKT("Tabelle1!"&ADRESSE(2;VERGLEICH(C1;Tabelle1!B1:E1;0)+1) &":"&ADRESSE(10;VERGLEICH(C1;Tabelle1!B1:E1;0)+1)))
Auf deine Tabelle umgebaut sollte es dann so lauten
=SUMMENPRODUKT('Produktionsstunden 2010'!C15:C379=Scorecard!E6) *INDIREKT("('Produktionsstunden 2010'!"&ADRESSE(15;VERGLEICH(Scorecard!B10; 'Produktionsstunden 2010'!E14:DP14;0)+3)&":"&ADRESSE(379; VERGLEICH(Scorecard!B10; 'Produktionsstunden 2010'!E14:DP14;0)+3))
Gruss Rainer
Anzeige
AW: Suchkriterium mit 2 Werten aus Tabellenblatt 1
13.04.2010 13:51:02
Uppe
Hallo RoB,
da fehlt nur eine schöne Klammer um "alles"!
=SUMMENPRODUKT(('Produktionsstunden 2010'!C15:C379=B10) *('Produktionsstunden 2010'!A14:DP14=E6) *('Produktionsstunden 2010'!AA15:AA380))
Gruß Uppe
Das kann gar nicht funktionieren...
13.04.2010 15:24:26
Ramses
Hallo
Weil da nicht bloss eine Klammer fehlt, sondern die Zellbezüge unterschiedlich gross sind und auch nicht alle Daten in einer Spalte vorliegen, sondern teilweise in einer Zeile.
Lesen, nachdenken, evtl. testen, schreiben :-)
Gruss Rainer
wieso nicht!?
13.04.2010 16:15:09
Uppe
Hallo Ramses,
ich habe gelesen, nachgedacht, nachgebaut, getestet und dann geschrieben! Bin schon a bissle angepisst wegen Deines Postings!!
siehe Tabellenblatt2
https://www.herber.de/bbs/user/69058.xls
Gruß Uppe
Anzeige
Deshalb...
13.04.2010 18:25:13
Ramses
Hallo
In deiner Beispieldatei sehe ich keine Formel und auf Tabelle 2 ist gar nichts
Das Problem ist hier
*('Produktionsstunden 2010'!A14:DP14=E6)
Summenprodukt braucht gleich grosse Arrays zur Multiplikation und kann nicht zwischenzeitlich horizontal noch nach etwas suchen um dann mit Werten in einer anderen Spalte zu multiplizieren
Solltest du das mit der Funktion SUMMENPRODUKT hinbekommen ziehe ich meinen Hut und entschuldige mich bei Dir :-)
Gruss Rainer
das ist mir zu blöd
13.04.2010 20:28:48
Uppe
Hallo Rainer,
wenn ich die Datei öffne, habe ich auf Tabellenblatt2 die TB2, die unter TB1 in Tabellenblatt1 abgebildet ist.
In C4 bis F8 stehen die SUMMENPRODUKT-Formeln.
Wenn Du die nicht siehst, tut es mir leid für Dich.
Vielleicht sieht sie ja wenigstens der Threaderöffner und es hilft ihm.
Gruß Uppe
Anzeige
Entschuldigung...
13.04.2010 21:04:16
Ramses
Hallo
ich habe auf der Tabelle 2 effektiv nichts, sonst hätte ich das ja nicht geschrieben.
Um das ganze nun abzuschliessen und habe ich Deine Formel nachgebaut und gehe nun den Gang nach Canossa.
Wie ich geschrieben habe:
Ich entschuldige mich bei Dir.
Da hat die Funktion Summenprodukt offensichtlich keine Ahnung was sie selber kann
"...MS EXCEL Hilfe:
Die als Argumente angegebenen Matrizen müssen bezüglich der Zeilen- und Spaltenanzahl identisch sein. Ist dies nicht der Fall, gibt SUMMENPRODUKT den Fehlerwert #WERT! zurück.
..."
Gruss Rainer

322 Forumthreads zu ähnlichen Themen

Ausgabe zweier Zellen mit SVERWEIS
Andreas

Hallo
Ich möchte mittels SVERWEIS Werte aus Spalte 2 oder wenn sich in dieser Spalte keine Werte befinden aus Spalte 3 ausgeben...
Kann mir da jemand weiterhelfen?
Gruß
Andreas
14.08.2008 11:46:00
Thread-ID: 1001793
4 Beiträge
Anzeige
Summe mit 2 Bedingungen
TobiasM

Hallo Excel-Experten,
ich würde gerne eine Summe mit zwei Bedingungen berechnen:
Wenn Wert in Spalte H gleich "EUR" und Wert in Spalte I gleich ""2005", dann Summe der entsprechenden Werte in Spalte J.
Könnte mir jmd. bitte einen Hinweis zur Formel geben?
Vielen Dank!!!
Tobi...
28.08.2008 10:11:30
Thread-ID: 1005310
3 Beiträge
Daten sortiert aus anderem Tabellenblatt auslesen
Robert

Hallo,
bräuchte eure Hilfe bitte.
Aus einem Tabellenblatt ("FEHLZEITEN") sollen Daten ausgelesen und in einem anderen Tabellenblatt ("EINSATZPLANUNG") ab Zeile 100 eingefügt und bei Änderungen in "FEHLZEITEN" aktualisiert werden.
Habe da mal eine Beispieldatei zur besseren Veransch...
28.08.2008 11:32:00
Thread-ID: 1005356
3 Beiträge
Anzeige
ZÄHLENWENN mit 2 Bedingungen
oblivion

Hallo, hab mal eine Frage zur Funktion ZÄHLENWENN.
Die Funktion lautet ja allgemein so =ZÄHLENWENN(A1:A15;"=19").
Ich würde aber gerne diese Funktion so erweitern, dass er mir alle Werte zählt, die kleiner als 19 sind, aber größer als 0.
Ich bekomme es nicht hin. Gibt es da eine Lö...
04.09.2008 18:07:33
Thread-ID: 1007473
3 Beiträge
Sverweis und mehrere Dateien durchsuchen
Benjamin

Hallo,
ist es möglich mit sverweis oder einer anderen Funktion mehere gleich aufgebaute Dateien zu durchsuchen und ein bestimmten wert (z.b in der zweiten Spalte) auszugeben. Also keine sheets sondern, dass das Makro die dateien eigenständig öffnet z.b. all dateien in diesem Odner öfnnet un...
26.09.2008 02:00:36
Thread-ID: 1012468
2 Beiträge
Zwei Kriterien für Sverweis
sascha76er

Hallo,
ich habe folgende Liste:
https://www.herber.de/bbs/user/55778.xls
Hier möchte ich gerne anhand von zwei Kriterien einen SVERWEIS erstellen für die Spalte P , die Kriterien sollen aus den Werten der Spalten E und M gebildet werden. Der entsprechende Wert muss im Tabellenblatt...
01.10.2008 16:04:31
Thread-ID: 1013672
3 Beiträge
PIVOTDATENZUORDNEN mit zwei Kriterien
Andi

Hallo Experten,
Ich versuch zurzeit eine Auswertung aus einer Pivot Tabelle zu erstellen, komm jedoch nicht weiter da ich zwei Kriterien in der X-achse und eins in der Y-achse berücksichtigen muss. Das heisst ich kann nicht einfach X-Achse und Y-achse mit der Pivot-tabelle abgleichen, sonde...
14.10.2008 12:16:00
Thread-ID: 1016295
5 Beiträge
SVerweis mit 2 Kriterien über Index??
Bernd

Hallo Freaks,
ich habe es wirklich versucht....und auch ein einfaches Bsp. aufgebaut. Nur die Indexformel funktioniert nicht...
Ich bitte Euch um Hilfe.
https://www.herber.de/bbs/user/56079.xls
Danke, Bernd
17.10.2008 15:26:00
Thread-ID: 1017185
4 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
3
3
2
2
5
2
1
2
4
3
6
11
3
5
11
6
2
2
2
5
1
3
3
3
6