Summenprodukt mit mehreren Bedingungen
Eisenhut
ich möchte in Tabelle 2 diejenigen Zeilen zählen, die in Tabelle1 in den
Spalten L, N, O, P bestimmte Werte enthalten, gleichzeitig aber zulassen, daß
bei der Kriterienauswahl Platzhalter zugelassen werden. Das Zählen der
Zeilen, wenn genaue Werte als Kriterium angegeben sind klappt mit der Formel
=SUMMENPRODUKT(((Tabelle1!$L$7:$L$176=B4)*(Tabelle1$N$7:$N$176=C4)*(Tabelle1$O$7:$O$176=D4)*(Tabelle1$P$7:$P$176=E4)*1))
In B4, C4, D4, E4 sind die jeweiligen Suchkriterien eingegeben.
Nun soll zusästzlich noch gezählt werden, wenn bei einer der Kategorien kein
konkreter Wert sondern ein Platzhalter ("0") angegeben ist.
Beispiel:
L N O P
1 2 1 1
1 1 2 4
3 2 4 1
1 2 2 1
1 2 1 1
Kriterienauswahl 1-2-1-1 wirft das Ergenbis 1 aus (Zeile 1)
Kriterienauswahl 1-2-0-1 ergibt 3, "0" ist hierbei ein Platzhalter (Zeilen
1, 4 und 5)
Kriterienauswahl 0-2-0-1 ergibt 4 (Zeilen 1, 3, 4, 5)
Kriterienauswahl 0-0-0-0 ergibt 5 (alle Zeilen)
Ich hoffe, ich konnte das Problem verständlich machen und hoffe auf
hilfreiche Antwort. Vielen Dank und freundleiche Grüße
Eisenhut
Bernd Plumhoff
eine wenig elegante Lösung:
Schreibe in Zelle B7:
="SUMMENPRODUKT("&WENN($B$4<>0;"--($B$4=$L$7:$L$176);";"")&WENN($C$4<>0;"--($C$4=$N$7:$N$176);";"")&WENN($D$4<>0;"--($D$4=$O$7:$O$176);";"")&WENN($E$4<>0;"--($E$4=$P$7:$P$176);";"") & "--($L$7:$L$176=$L$7:$L$176))"
Schreibe in Zelle B8:
=Auswerten
Definiere den Namen Auswerten mit dem Wert(Formel):
=AUSWERTEN(INDIREKT("Z(-1)S";FALSCH))
Wundere Dich nicht über die (korrekten!) Ergebnisse:
Bei Eingabe von 1 2 0 1 erhältst Du 3, bei 1 2 1 1 bekommst Du 2, und bei 0
0 0 0 lautet das Resultat 170.
HTH,
Bernd
Thomas Ramel
Eisenhut schrieb am 06.07.2005
> ich möchte in Tabelle 2 diejenigen Zeilen zählen, die in Tabelle1 in den
> Spalten L, N, O, P bestimmte Werte enthalten, gleichzeitig aber zulassen, daß
> bei der Kriterienauswahl Platzhalter zugelassen werden.
> In B4, C4, D4, E4 sind die jeweiligen Suchkriterien eingegeben.
> Nun soll zusästzlich noch gezählt werden, wenn bei einer der Kategorien kein
> konkreter Wert sondern ein Platzhalter ("0") angegeben ist.
>
> Beispiel:
> L N O P
> 1 2 1 1
> 1 1 2 4
> 3 2 4 1
> 1 2 2 1
> 1 2 1 1
>
> Kriterienauswahl 1-2-1-1 wirft das Ergenbis 1 aus (Zeile 1)
...hier müsste '2' ausgegeben werden, denn Zeile 5 trifft ebenfalls zu
> Kriterienauswahl 1-2-0-1 ergibt 3, "0" ist hierbei ein Platzhalter (Zeilen
> 1, 4 und 5)
> Kriterienauswahl 0-2-0-1 ergibt 4 (Zeilen 1, 3, 4, 5)
> Kriterienauswahl 0-0-0-0 ergibt 5 (alle Zeilen)
Mir scheint, dass Du das Ergebnis am ehesten mit dem Autofilter hinbekommen
wirst:
B5 =Tabelle1!L7
C5 =Tabelle1!N7
D5 =Tabelle1!O7
E5 =Tabelle1!P7
und nach unten kopieren bis Zeile 174
--> B4:E4 markieren
--> Menü: 'Daten'
--> Filter...
--> Autofilter
Und in irgend einer Zelle die folgende Formel:
=TEILERGEBNIS(2;B5:B174)
Nun kannst Du in B4:E4 deine Kriterien bequem im DropDown auswählen und
erhältst das gewünscht Ergebnis.
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]
Microsoft Excel - Die ExpertenTipps:
(http://tinyurl.com/9ov3l und http://tinyurl.com/cmned)
Thomas Ramel
...ich mach dann auch gleich mal die Ingrid
Thomas Ramel schrieb am 06.07.2005
>> In B4, C4, D4, E4 sind die jeweiligen Suchkriterien eingegeben.
>> Nun soll zusästzlich noch gezählt werden, wenn bei einer der Kategorien kein
>> konkreter Wert sondern ein Platzhalter ("0") angegeben ist.
>>
>> Beispiel:
>> L N O P
>> 1 2 1 1
>> 1 1 2 4
>> 3 2 4 1
>> 1 2 2 1
>> 1 2 1 1
>>
>> Kriterienauswahl 1-2-1-1 wirft das Ergenbis 1 aus (Zeile 1)
>
> ...hier müsste '2' ausgegeben werden, denn Zeile 5 trifft ebenfalls zu
>
>> Kriterienauswahl 1-2-0-1 ergibt 3, "0" ist hierbei ein Platzhalter (Zeilen
>> 1, 4 und 5)
>> Kriterienauswahl 0-2-0-1 ergibt 4 (Zeilen 1, 3, 4, 5)
>> Kriterienauswahl 0-0-0-0 ergibt 5 (alle Zeilen)
Du kannst anstelle des Autofilters auch die folgende Matrixformel verwenden
(Bedingung: A7:A176 sind leer - ansonsten einfach anpassen):
=SUMMENPRODUKT(
WENN(B4=0;($A$7:$A$176="");(Tabelle1!$L$7:$L$176=B4))*
WENN(C4=0;($A$7:$A$176="");(Tabelle1!$N$7:$N$176=C4))*
WENN(D4=0;($A$7:$A$176="");(Tabelle1!$O$7:$O$176=D4))*
WENN(E4=0;($A$7:$A$176="");(Tabelle1!$P$7:$P$176=E4)))
und mit Strg+Umschalt+Return abschliessen.
Eberhard Funke
Hallo Eisenhut,
nicht ausführlich getestet, aber probier's mal damit:
Der Einfachheit halber habe ich das alles in EINER Tabelle probiert. Deine
Testwerte stehen in A1:D5, die Kriterien in G1:J1. Musst Du entsrpechen
anpassen.
Anstelle der 0 (Null) verwende das Platzhalterzeichen *.
=SUMME(ISTZAHL(SUCHEN(G1&H1&I1&J1;A1:A5&B1:B5&C1:C5&D1:D5;1))*1)
Als Marixformel mit Shift+Strg+Enter abschließen.
--
Gruß Eberhard
XL 2000
Eberhard(punkt)Funke(at)t-online.de
Eisenhut
"Thomas Ramel" wrote:
Servus NG, lieber Herr Ramel,
das hat bei mir in einer Datei gut geklappt, nun aber setze ich diese Formel
leicht angepaßt wieder ein und erhalte stets die Fehlermeldung #WERT. Woran
kann das liegen? Die Formel sieht bei mir folgendermaßen aus:
{=SUMMENPRODUKT(WENN($D$4="-";($A$2:$A$50000="");(Tabelle1!$C$2:$C$50000))*WENN(E4="-";($A$2:$A$50000="");(Tabelle1!$D$2:$D$50000)))}
Ich habe also nur ein "-" statt der 0 als Platzhalter (das mache in der
"funktionierenden" Datei aber auch) gesetzt und den Datenbereich vergrößert.
Und dann hab ich noch ein Problem: Das ganze müsste weiter eingeschränkt
werden, und zwar zeitlich. Man kann also einen Zeitraum (im Format von
tt.mm.jj bis tt.mm.jj) angeben für den die oben erwähnte Auswertung gefahren
wird. Die Zeitdaten sind in der gleichen Tabelle wie die anderen Merkmale
hinterlegt. Das bekomme ich auch ohne die weiteren Einschränkungen nicht hin,
hier wird mir unter Anwendung der Formel
=SUMMENPRODUKT((tabelle1!$A$1:$A$50000>=b1)*(tabelle1!$A$1:$A$50000<=Y1)*$a$1:$a$50000)
wobei Anfang und Ende in b1 und c1 stehen,
stets 0 als Ergebnis.
Wie kann ich nun also zum einen einen Zeitraum wählen und gleichzeitg eine
Auswertung nach verschiedenen Kriterien zusammenbasteln? Ich hoffe, mein
Problem verständlich gemacht zu haben und wäre für Hilfe überaus dankbar!
Gruß
Eisenhut
Thomas Ramel
Eisenhut schrieb am 05.12.2005
> Servus NG, lieber Herr Ramel,
Die Original-Beiträge sind ja schon ein paar Tage her...
> das hat bei mir in einer Datei gut geklappt, nun aber setze ich diese Formel
> leicht angepaßt wieder ein und erhalte stets die Fehlermeldung #WERT. Woran
> kann das liegen?
>
> Wie kann ich nun also zum einen einen Zeitraum wählen und gleichzeitg eine
> Auswertung nach verschiedenen Kriterien zusammenbasteln? Ich hoffe, mein
> Problem verständlich gemacht zu haben und wäre für Hilfe überaus dankbar!
Ohne genauere Kenntnis des Aufbaus der Tabellen zu haben stochern wir hier
ein wenig im Trüben, zumal ein eigener Aufbau bestimmt von anderen
Gegebenheiten ausgeht als das Original.
Kannst Du daher bitte etwas ausführlicher beschreiben, welche Daten denn
nun wo stehen und wo sie ausgewertet werden sollen'
Ich könnte mir vorstellen, dass Du eventuell mit einer Pivot-Tabelle
*wesentlich* einfachere Auswertungen anstellen könntest; deren Handhabung
müsstest Du dann allerdings kurz erlernen - aber dabei helfen wir dir gerne
;-)
Eisenhut
"Thomas Ramel" wrote:
!
>
> Ohne genauere Kenntnis des Aufbaus der Tabellen zu haben stochern wir hier
> ein wenig im Trüben, zumal ein eigener Aufbau bestimmt von anderen
> Gegebenheiten ausgeht als das Original.
> Kannst Du daher bitte etwas ausführlicher beschreiben, welche Daten denn
> nun wo stehen und wo sie ausgewertet werden sollen'
>
> Ich könnte mir vorstellen, dass Du eventuell mit einer Pivot-Tabelle
> *wesentlich* einfachere Auswertungen anstellen könntest; deren Handhabung
> müsstest Du dann allerdings kurz erlernen - aber dabei helfen wir dir gerne
> ;-)
>
>
> Mit freundlichen Grüssen
> Thomas Ramel
>
Guten Morgen,
aalso, meine Datei schaut so aus:
In Tabelle2 sind folgende Daten vorhanden:
Spalte A ab Zeile 2: Anlieferungsdatum im Format xxx tt.mm.jjjj (xxx ist ein
Text aus drei Zeichen, übernommen aus einem Dispo-Programm)
Spalte B: =TEIL(A2;6;10); das ergibt das Datum im Format tt.mm.jjjj, also
ohne den Text.
Spalte C: =TEIL(A2;2;4), also der Text aus der Datumsangabe
Spalte D, E, F sind aus einer anderen Datei übernommene Datensätze, die
später als Auswahlkriterien dienen sollen.
Das Ganze geht dann eben ziemlich weit runter (deshalb die 50000 in meiner
Formel).
Nun müsste ich eben in Tabelle1 analog zu meinem alten Post eine Formel
zusammenschrauben, die ausgibt, auf wieviele Datensätze die vorher erfolgte
Kriterienauswahl zutrifft. Dazu soll eben noch ein Güligkeitszeitraum
festgelegt werden können. Beispiel:
B C D E F
von bis Partner Modell Ort
01.01.06 31.01.06 X Y Z
Im Auswertungsfeld müsste nun die Anzahl aller Datzensätze, die mit einem
Datum zwischen 01.01. und 31.01. versehen sind, und auf die die Kriterien X,
Y, Z zutreffen, erscheinen. Daneben muß noch die Möglichkeit eines
Platzhalters gegeben sein, es muß also möglich sein, "X, -, Z" und "X, -, -"
usw. auszuwählen. Die Auswahl der Suchkrieterien erfolgt natürlich per
Drop-Down.
Sollte das irgendwie ohne Pivot-Tabelle lösbar sein, wäre mir das deutlich
lieber (allein schon wegen der Übersichtlichkeit), ansonsten wäre ich auch
für die angbotene Unterstützung zur Erstellung einer solchen Tabelle sehr
dankbar!
Gruß
Eisenhut
Eberhard Funke
> In Tabelle2 sind folgende Daten vorhanden:
> Spalte A ab Zeile 2: Anlieferungsdatum im Format xxx tt.mm.jjjj (xxx ist ein
> Text aus drei Zeichen, übernommen aus einem Dispo-Programm)
> Spalte B: =TEIL(A2;6;10); das ergibt das Datum im Format tt.mm.jjjj, also
> ohne den Text.
> Spalte C: =TEIL(A2;2;4), also der Text aus der Datumsangabe
> Spalte D, E, F sind aus einer anderen Datei übernommene Datensätze, die
> später als Auswahlkriterien dienen sollen.
> Das Ganze geht dann eben ziemlich weit runter (deshalb die 50000 in meiner
> Formel).
>
> Nun müsste ich eben in Tabelle1 analog zu meinem alten Post eine Formel
> zusammenschrauben, die ausgibt, auf wieviele Datensätze die vorher erfolgte
> Kriterienauswahl zutrifft. Dazu soll eben noch ein Güligkeitszeitraum
> festgelegt werden können. Beispiel:
>
> B C D E F
> von bis Partner Modell Ort
> 01.01.06 31.01.06 X Y Z
>
> Im Auswertungsfeld müsste nun die Anzahl aller Datzensätze, die mit einem
> Datum zwischen 01.01. und 31.01. versehen sind, und auf die die Kriterien X,
> Y, Z zutreffen, erscheinen. Daneben muß noch die Möglichkeit eines
> Platzhalters gegeben sein, es muß also möglich sein, "X, -, Z" und "X, -, -"
> usw. auszuwählen. Die Auswahl der Suchkrieterien erfolgt natürlich per
> Drop-Down.
Hallo Eisenhut,
den letzten Satz verstehe ich so, dass Du für die Spalten D bis F schon
einen Autofilter eingerichtet hast.
Für den Fall "X, -, Z" würdest du also in D bis F "X", "Alle" und "Z"
auswählen.
Dasselbe kannst Du doch auch für "von" und "bis" machen, also
für Spalte B "ist größer oder gleich" und dann "01.01.06" und
für Spalte C "ist kleiner oder gleich" und dann "31.01.06"
und dann mit Menü Daten --> Teilergebnisse --> Anzahl die Zahl der
herausgefilterten Zeilen ermitteln.
Mit ist übrigens nicht klar, weshalb Du eingangs das mit der Tabelle 2
erwähnst. Für mich besteht da kein erkennbarer Zusammenhang mit der
Auswertung in der Tabelle 1.
--
Gruß Eberhard
XP home XL 2000
Eberhard(punkt)W(punkt)Funke(at)t-online.de
Eisenhut
"Eberhard Funke" wrote:
Habe ich zwar, Du verstehst den Satz trotzdem falsch. Ich meinte: Die
Auswahl der Kriterien, die die Suche einschränken, geschieht in Tabelle1,
also der Auswertung, mittels Drop-Down-Menüs.
>
> Für den Fall "X, -, Z" würdest du also in D bis F "X", "Alle" und "Z"
> auswählen.
>
> Dasselbe kannst Du doch auch für "von" und "bis" machen, also
> für Spalte B "ist größer oder gleich" und dann "01.01.06" und
> für Spalte C "ist kleiner oder gleich" und dann "31.01.06"
> und dann mit Menü Daten --> Teilergebnisse --> Anzahl die Zahl der
> herausgefilterten Zeilen ermitteln.
>
> Mit ist übrigens nicht klar, weshalb Du eingangs das mit der Tabelle 2
> erwähnst. Für mich besteht da kein erkennbarer Zusammenhang mit der
> Auswertung in der Tabelle 1.
Hallo Herr Funke,
Das wäre eine Möglichkeit, allerdings müsste dann der Autofilter bei jeder
Abfrage neu definiert werden und das wollte ich eigetlich vermeiden.
Ich habe aber mittlerweile den Verdacht, daß das Problem woanders liegt,
nämlcih darin, daß Excel die aus dem Dispo-Programm übernommenen und mit
=TEIL(A2;6:10) angepassten Daten nicht als Datumsformat erkennt. Kann das
sein? In den Zell-Eigenschaften ist allerdings das Format "Datum" angelegt.
Ich habe diesen Verdacht, weil der benutzerdefinierte Filter niemals Treffer
liefert. Kann das sein?
Danke für die Hilfe!
Claus Busch
Am Tue, 6 Dec 2005 07:44:03 -0800 schrieb Eisenhut:
> Das wäre eine Möglichkeit, allerdings müsste dann der Autofilter bei jeder
> Abfrage neu definiert werden und das wollte ich eigetlich vermeiden.
>
> Ich habe aber mittlerweile den Verdacht, daß das Problem woanders liegt,
> nämlcih darin, daß Excel die aus dem Dispo-Programm übernommenen und mit
> =TEIL(A2;6:10) angepassten Daten nicht als Datumsformat erkennt. Kann das
> sein? In den Zell-Eigenschaften ist allerdings das Format "Datum" angelegt.
> Ich habe diesen Verdacht, weil der benutzerdefinierte Filter niemals Treffer
> liefert. Kann das sein?
=TEIL(A2;6;10) liefert nur Text. = - -(TEIL(A2;6;10) liefert das Datum
--
Mit freundlichen Grüssen
Claus Busch
Win XP Prof SP2; Office 2000 SP3
claus_busch(at)t-online.de
Claus Busch
es fehlt noch eine Klammer:
= - -(TEIL(A2;6;10))
Und dann Zelle TT.MM.JJJJ formatieren
Eberhard Funke
[Ich erlaube mir, bei dem in der NG üblichen "Du" zu bleiben]
>>>> Die Auswahl der Suchkrieterien erfolgt natürlich per
>>> Drop-Down.
>> den letzten Satz verstehe ich so, dass Du für die Spalten D bis F schon
>> einen Autofilter eingerichtet hast.
>
> Habe ich zwar, Du verstehst den Satz trotzdem falsch. Ich meinte: Die
> Auswahl der Kriterien, die die Suche einschränken, geschieht in Tabelle1,
> also der Auswertung, mittels Drop-Down-Menüs.
Jetzt verstehe ich noch weniger: Du hast in der Tabelle ca. 5000 Zeilen wie
nachfolgend
B C D E F
von bis Partner Modell Ort
01.01.06 31.01.06 X Y Z
Auf "Partner", "Modell" und "Ort" hast Du einen Autofilter gesetzt. Wo
steht denn jetzt das Drop-Down-Menüs? Und wie/Wo werden denn die Ausgaben
des Drop-Down-Menüs weiter benutzt?
> Ich habe aber mittlerweile den Verdacht, daß das Problem woanders liegt,
> nämlcih darin, daß Excel die aus dem Dispo-Programm übernommenen und mit
> =TEIL(A2;6:10) angepassten Daten nicht als Datumsformat erkennt. Kann das
> sein? In den Zell-Eigenschaften ist allerdings das Format "Datum" angelegt.
> Ich habe diesen Verdacht, weil der benutzerdefinierte Filter niemals Treffer
> liefert. Kann das sein?
Das glaube ich weniger. Deine Formel =TEIL(A2;6;10) gibt ein auswertbares
Datum, wenn Dein String in Tab.2 A2 folgendermassen aussieht (die
Leerzeichen sind in Deinem Text nicht zweifelsfrei erkennbar):
LEERZEICHENxxxLEERZEICHEN13.04.1938
Überprüfe das mal in Tab. 2: =LÄNGE(A2) müsste 15 ergeben.
Zur Überprüfung des mit der TEIL-Formel herausgeholten Datums (Annahme: das
Datum steht in Tab.2 B2)
wenn =WOCHENTAG(B2;2) eine Zahl zwischen 1 und 7 gibt (1=Montag usw)., dann
wird Dein Datum richtig erkannt.
Der Zusammenhang zwischen Tabelle 1 und Tabelle 2 ist mir immer noch nciht
klar. Man kann offensichtlich jedes der Probleme ohne Kenntnis der jeweils
anderen Tabelle bearbeiten?
Eisenhut
"Eberhard Funke" wrote:
> Das glaube ich weniger. Deine Formel =TEIL(A2;6;10) gibt ein auswertbares
> Datum, wenn Dein String in Tab.2 A2 folgendermassen aussieht (die
> Leerzeichen sind in Deinem Text nicht zweifelsfrei erkennbar):
>
> LEERZEICHENxxxLEERZEICHEN13.04.1938
Ok, das Problem hat sich erledigt, mit Buschs Formel klappt das alles.
> Der Zusammenhang zwischen Tabelle 1 und Tabelle 2 ist mir immer noch nciht
> klar. Man kann offensichtlich jedes der Probleme ohne Kenntnis der jeweils
> anderen Tabelle bearbeiten?
Der Zusammenhang zwischen den Tabellen ist schlicht der, daß in Tabelle2 die
Daten vorhanden sind, die in Tabelle1 ausgewertet werden sollen. In Tabelle2
habe ich also die Daten in folgender Form:
A B C
D E
Datum (aus Dispo-Prog) =- -(TEIL(A2;6;10)) Mermal1 Merkmal2
Merkmal3
LEERXXXLEEER01.01.2005 01.01.05 R S
T
LEERXXXLEEER02.04.2006 02.04.06 U V
W
LEERXXXLEEER21.12.2005 21.12.05 X Y
Z
LEERXXXLEEER23.03.2005 23.03.05 R V
T
In Tabelle1 *soll* die Auswertung dieser Daten (aus Tabelle2) in folgender
Form möglich sein:
A B C D
E F
Zeitraum
von bis Merkmal1 Merkmal2 Merkmal3
Ergebnis
01.01.05 31.12.05 X Y Z
1
Die Zellen A4 bis E4 sind Eingabefelder, die Eingabe in C4 bis E4 erfolgt
per Drop-Down (das ist der Drop-Down von dem ich oben sprach), und für das
Feld F4 bin ich eben auf der Suche nach einer passenden Formel.
Beispiel für eine andere Abfrage, aufbauend auf o.g. Datensatz in Tabelle2:
01.01.05 31.12.05 R - T
2
Hier müsste das Ergebnis "2" sein, das "-" steht als Platzhalter, die
Kriterien "R,-,T" treffen auf den ersten und den letzten Datensatz zu, beide
Datensätz liegen im angegebenen Zeitraum. weiteres Beispiel:
01.01.05 31.12.05 - - -
3
Hoffentlich konnte ich jetzt anschaulich machen, was ich will und wo mein
Problem liegt. Herzlichen Dank für die Hilfe!
Daneben habe ich noch ein anderes Problem, dafür mach ich aber glaub ich
besser mal einen eigenen Faden auf...
Eisenhut
"Eisenhut" wrote:
Mist, da hats mir das Format verhauen, ich versuchs nochmal mit weniger
Spalten, am Prinzip ändert sich nix:
> Der Zusammenhang zwischen den Tabellen ist schlicht der, daß in Tabelle2 die
> Daten vorhanden sind, die in Tabelle1 ausgewertet werden sollen. In Tabelle2
> habe ich also die Daten in folgender Form:
Tabelle2 (Datensätze):
B C D
=- -(TEIL(A2;6;10)) Mermal1 Merkmal2
01.01.05 R S
02.04.06 U V
21.12.05 X Y
23.03.05 R V
>
> In Tabelle1 *soll* die Auswertung dieser Daten (aus Tabelle2) in folgender
> Form möglich sein:
>
> A B C D E
> Zeitraum
> von bis Merkmal1 Merkmal2 Ergebnis
> 1.1.05 31.12.05 X Y 1
>
>
> Die Zellen A4 bis F4 sind Eingabefelder, die Eingabe in C4 und D4 erfolgt
> per Drop-Down (das ist der Drop-Down von dem ich oben sprach), und für das
> Feld E4 bin ich eben auf der Suche nach einer passenden Formel.
>
> Beispiel für eine andere Abfrage, aufbauend auf o.g. Datensatz in Tabelle2:
>
> 1.1.05 31.12.05 R - 2
>
>
> Hier müsste das Ergebnis "2" sein, das "-" steht als Platzhalter, die
> Kriterien "R,-" treffen auf den ersten und den letzten Datensatz zu, beide
> Datensätz liegen im angegebenen Zeitraum. weiteres Beispiel:
>
> 1.1.05 31.12.05 - - 3
>
Ergebnis hier muß 3 sein, da kein Kriterium angegeben ist und alle Daten die
in den Zeitraum 1.1.05 bis 31.12.05 fallen gezählt werden sollen.
Eberhard Funke
Hallo Eisenhut,
Deine Korrektur irritiert mich insofern, als jetzt nur zwei Merkmale (statt
vorher drei) auftauchen. Ich konnte die Tabellen Deines vorhergenden Posts
trotz der Zeilenumbrüche verstehen und gehe mal davon aus, dass diese
ersten Tabellen die richtigen sind.
Zur Absicherung:
in Tab 1 F4 soll folgendes stehen:
suche in Tab.2 Spalte B die Datensätze, die in den Zeitraum Tab.1 A4 / B4
fallen. Unter diesen Datensätzen in Tab. 2 zähle diejenigen, die mit Tab.1
C4 bis E4 übereinstimmen.
Ich gehe davon aus, dass in Tab.2 Zeile 1 Überschriften stehen.
Eisenhut
"Eberhard Funke" wrote:
>
> Hallo Eisenhut,
>
> Deine Korrektur irritiert mich insofern, als jetzt nur zwei Merkmale (statt
> vorher drei) auftauchen. Ich konnte die Tabellen Deines vorhergenden Posts
> trotz der Zeilenumbrüche verstehen und gehe mal davon aus, dass diese
> ersten Tabellen die richtigen sind.
Hab eben nur ein Merkmal rausgenommen, um sicherzugehen, daß die ganze Zeie
in das Fenster paßt.
>
> Zur Absicherung:
> in Tab 1 F4 soll folgendes stehen:
> suche in Tab.2 Spalte B die Datensätze, die in den Zeitraum Tab.1 A4 / B4
> fallen. Unter diesen Datensätzen in Tab. 2 zähle diejenigen, die mit Tab.1
> C4 bis E4 übereinstimmen.
> Ich gehe davon aus, dass in Tab.2 Zeile 1 Überschriften stehen.
Jetzt kommen wir zusammen. Genauso hätte ich das gerne. Dazu soll es eben
noch möglich sein, eines oder mehrere der Auswahlkriterien freizulassen, so
daß in Tabelle1 F4 steht:
suche in Tab.2 Spalte B die Datensätze, die in den Zeitraum Tab.1 A4 / B4
fallen. Unter diesen Datensätzen in Tab. 2 zähle diejenigen, die mit Tab.1
C4 bis E4 übereinstimmen. Ist in Tab1 C4 bis E4 eine dieser Zellen mit einem
Platzhalter belegt, ignoriere die diesem Feld entsprechenden Daten aus
Tabelle2 und zähle diejenigen Datensätze aus Tabelle2, die den nicht mit
Platzhaltern belegten Zellen Tab1 C4 bis E4 entsprechen.
Ist jetzt hoffentlich etwas klarer...
Gruß, Eisenhut
Eberhard Funke
> Jetzt kommen wir zusammen.
"Schaumer mal"
Vorbereitung:
In Tabelle 1 A3:F3 stehen die Einträge
von bis Merkmal1 Merkmal2 Merkmal3 Ergebnis
und zwar genau so geschrieben wie vorstehend.
Markiere A3:F4, dann Menü Einfügen --> Name --> Erstellen.
Häkchen bei "Oberste Zeile".
Klick mit der rechten Maus unten links auf den Reiter "Tabelle1".
Kopiere den Code in das sich öffnende Fenster und kehre mit Alt+F11 in die
Tabelle zurück.
Als Platzhalter habe ich "§" (ohne die "") verwand. Es lässt ich im Code
gefahrloser durch ein anderes Zeichen ersetzen, als dies u. U. beim "-" der
Fall wäre.
Mit Doppelklick auf Tabelle 1 F4 erscheint dort das Ergebnis.
Ich habe nur stichprobenartig getestet. Solltest Du intensiver mit einer
überschaubar kleinen Tabelle 2 prüfen.
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
If Target <> Range("Ergebnis") Then Exit Sub
Dim rngZ As Range
Dim rngBer As Range
Dim lngLZ As Long
Dim blFlag As Boolean
Dim lngI As Long
Dim lngAnzahl As Long
On Error GoTo Errhandler
lngLZ = Worksheets(2).Range("A65536").End(xlUp).Row
Set rngBer = Worksheets(2).Range("B2:B" & lngLZ)
For Each rngZ In rngBer
If rngZ.Value >= Range("von").Value And _
rngZ.Value <= Range("bis").Value Then
blFlag = False
For lngI = 1 To 3
If Range("Merkmal" & lngI) <> "§" And _
Range("Merkmal" & lngI) <> rngZ.Offset(0, lngI) Then
blFlag = True
Exit For
End If
Next
If blFlag = False Then lngAnzahl = lngAnzahl + 1
End If
Next
Range("Ergebnis").Value = lngAnzahl
Errhandler:
Cancel = True
End Sub
Eberhard Funke
> Als Platzhalter habe ich "§" (ohne die "") verwand.
1. verwandt
2. Gefällt mir doch nicht so gut. Nimm das Minuszeichen und stelle bei
C4:E4 der Optik wegen Zentrierten Text ein (oben in der Menüleiste)
und ersetze in der folgenden Zeile das Paragraphenzeichen durch das
Minuszeichen:
> If Range("Merkmal" & lngI) <> "§" And _
> Range("Merkmal" & lngI) <> rngZ.Offset(0, lngI) Then
Eisenhut
"Claus Busch" wrote:
> Hallo Eisenhut,
>
> es fehlt noch eine Klammer:
> = - -(TEIL(A2;6;10))
> Und dann Zelle TT.MM.JJJJ formatieren
>
Okeeh, das hat sehr geholfen, vielen Dank.
Jetzt bräuchte ich noch die passende Formel, um den Textteil des Zellinhalts
darzustellen.
Also:
In A1 steht LeerxxxLeertt.mm.jj
In A3 soll xxx stehen. Mit =TEIL(A3;2;4) wird das zwar angezeigt, aber
offenbar nicht erkannt. (Vgl. obiges Problem). Wie muß ich die Klammern
setzen?
Vielen Dank!
Claus Busch
Am Thu, 8 Dec 2005 05:25:03 -0800 schrieb Eisenhut:
>> es fehlt noch eine Klammer:
>> = - -(TEIL(A2;6;10))
>> Und dann Zelle TT.MM.JJJJ formatieren
>>
>
> Okeeh, das hat sehr geholfen, vielen Dank.
>
> Jetzt bräuchte ich noch die passende Formel, um den Textteil des Zellinhalts
> darzustellen.
>
> Also:
>
> In A1 steht LeerxxxLeertt.mm.jj
>
> In A3 soll xxx stehen. Mit =TEIL(A3;2;4) wird das zwar angezeigt, aber
> offenbar nicht erkannt. (Vgl. obiges Problem). Wie muß ich die Klammern
> setzen?
einfach wieder die 2 Minuszeichen einfügen. Die machen aus einem Text
eine Zahl, so als wenn du mit 1multiplizieren würdest:
= - - (Teil(A3;2;4))
Eisenhut
"Claus Busch" wrote:
> Hallo Eisenhut,
> >
> > Okeeh, das hat sehr geholfen, vielen Dank.
> >
> > Jetzt bräuchte ich noch die passende Formel, um den Textteil des Zellinhalts
> > darzustellen.
> >
> > Also:
> >
> > In A1 steht LeerxxxLeertt.mm.jj
> >
> > In A3 soll xxx stehen. Mit =TEIL(A3;2;4) wird das zwar angezeigt, aber
> > offenbar nicht erkannt. (Vgl. obiges Problem). Wie muß ich die Klammern
> > setzen?
>
> einfach wieder die 2 Minuszeichen einfügen. Die machen aus einem Text
> eine Zahl, so als wenn du mit 1multiplizieren würdest:
> = - - (Teil(A3;2;4))
>
Hab mich wohl (mal wieder) nicht klar genug ausgedrückt. Das xxx oben steht
für einen Text mit drei Zeichen, also z. B. "SPE" oder "HDL". Ich möchte den
Text aber gar nicht in eine Zahl umwandeln, sondern den Text stehen lassen.
Und wenn ich das mit =TEIL(A1;2;4) mache, kann Excel mit dem Ergebnis warum
auch immer nicht weiter arbeiten. Die Formel mit den "- -" gibt die
Fehlermeldung #WERT!
Eisenhut
"Claus Busch" wrote:
> > Also:
> >
> > In A1 steht LeerxxxLeertt.mm.jj
> >
> > In A3 soll xxx stehen. Mit =TEIL(A3;2;4) wird das zwar angezeigt, aber
> > offenbar nicht erkannt. (Vgl. obiges Problem). Wie muß ich die Klammern
> > setzen?
>
> einfach wieder die 2 Minuszeichen einfügen. Die machen aus einem Text
> eine Zahl, so als wenn du mit 1multiplizieren würdest:
> = - - (Teil(A3;2;4))
Hab mich wohl niocht klar ausgedrückt. Das xxx steht für einen
Buchstaben-Text, also:
A1:
leerABCleer01.01.06
In A3 soll nun stehen: ABC
Das erreiche zwar mit =TEIL(A1;2;4), allerdings kann Excel damit offenbar
nicht weiterarbeiten. verwende ich = - - (TEIL(A1;2;4) ergibt das die
Fehlermeldung #WERT.
Claus Busch
dann solltest du deine Formel so belassen wie sie war. Die Ausgabe war
dann ja korrekt. Jetzt geht es nur um die Verwendung des Textes. Rechnen
kann Excel mit Text nicht. Wenn du aber diesen Text als Kriterium oder
ähnliches verwenden willst, schreibe den Text in der Formel in
Anführungszeichen, also z.B. "HDL", oder beziehe dich auf die Zelle.
Vielleicht kannst du hier mal ein Beispiel für deine Formel posten,
damit man es besser nachvollziehen kann.
Herbert Taferner
> Hab mich wohl niocht klar ausgedrückt. Das xxx steht für einen
> Buchstaben-Text, also:
> A1:
> leerABCleer01.01.06
> In A3 soll nun stehen: ABC
>
> Das erreiche zwar mit =TEIL(A1;2;4), allerdings kann Excel damit offenbar
> nicht weiterarbeiten. verwende ich = - - (TEIL(A1;2;4) ergibt das die
> Fehlermeldung #WERT.
wenn in A1 leerABCleer01.01.06 steht
dann erhältst du mit =TEIL(A1;2;4) nicht ABC sondern ABCleer
mfg Herbert
Claus Busch
danke für die Korrektur. Ich hätte besser hinsehen sollen, aber
Leerzeichen sieht man halt nicht. :-(
Eisenhut
"Claus Busch" wrote:
> dann solltest du deine Formel so belassen wie sie war. Die Ausgabe war
> dann ja korrekt. Jetzt geht es nur um die Verwendung des Textes. Rechnen
> kann Excel mit Text nicht.
klar
> Wenn du aber diesen Text als Kriterium oder
> ähnliches verwenden willst, schreibe den Text in der Formel in
> Anführungszeichen, also z.B. "HDL",
Wenn ich Dich richtig vertehe, müsste ich das in jedem Datensatz machen, das
wäre viel zu umfangreich (die Daten werden aus einem Dispoprogramm
übernommen).
> oder beziehe dich auf die Zelle.
Auf welche Zelle?
> Vielleicht kannst du hier mal ein Beispiel für deine Formel posten,
> damit man es besser nachvollziehen kann.
Ok, etwas kürzer als tatsächlich, damit sie nicht zu unübersichtlich wird:
=SUMMENPRODUKT(WENN($D$6="-";(A2:A50000="");(Tabelle2!C2:C50000=$D$6))*WENN($E$4="-";(A2:A50000="");(Tabelle2!D2:D50000=$E$6))
als Array-Formel
Meine Frage bezieht sich auf Tabelle2!C2:C50000, hierfür bräuchte ich die
richtige Formel bzw. Formatierung um aus Tabelle2!A2:A50000 (Format
LEERxxxLEERtt.mm.jj) "xxx" (Text) zu erhalten. Hab im vorherigen Beitrag
leider die Zellen vertauscht, öndert aber nichts an der Fragestellung.
D6 ist ein Eingabefeld, hier kann aus einer Auswahlliste ein Wert, der einer
der Möglichkeiten aus Tabelle2!C2:50000 entspricht selektiert werden. "-" Ist
ein Platzhalter. Nur bei Eingabe des Platzhalters arbeitet die Formel
zuverlässig, bei Angabe von "xxx", "yyy", "xyx" usw. ist das Ergebnis stets
0. Und das ist falsch.
Die letzten beiden Klammerausdrücke sind unproblematisch.
Claus Busch
Am Thu, 8 Dec 2005 06:49:04 -0800 schrieb Eisenhut:
[ausgeschnitten]
> Ok, etwas kürzer als tatsächlich, damit sie nicht zu unübersichtlich wird:
>
> =SUMMENPRODUKT(WENN($D$6="-";(A2:A50000="");(Tabelle2!C2:C50000=$D$6))*WENN($E$4="-";(A2:A50000="");(Tabelle2!D2:D50000=$E$6))
>
> als Array-Formel
>
> Meine Frage bezieht sich auf Tabelle2!C2:C50000, hierfür bräuchte ich die
> richtige Formel bzw. Formatierung um aus Tabelle2!A2:A50000 (Format
> LEERxxxLEERtt.mm.jj) "xxx" (Text) zu erhalten. Hab im vorherigen Beitrag
> leider die Zellen vertauscht, öndert aber nichts an der Fragestellung.
> D6 ist ein Eingabefeld, hier kann aus einer Auswahlliste ein Wert, der einer
> der Möglichkeiten aus Tabelle2!C2:50000 entspricht selektiert werden. "-" Ist
> ein Platzhalter. Nur bei Eingabe des Platzhalters arbeitet die Formel
> zuverlässig, bei Angabe von "xxx", "yyy", "xyx" usw. ist das Ergebnis stets
> 0. Und das ist falsch.
> Die letzten beiden Klammerausdrücke sind unproblematisch.
ich denke, das Probelm hat sich mit der Antwort von Herbert erledigt.
Du gibst bei deinen Kriterien z.B. "HDL" ein, aber deine TEIL-Formel
bringt dir das Ergebnis "HDLLeer". Deswegen gibt es keine
Übereinstimmung.
Eisenhut
"Claus Busch" wrote:
>> ich denke, das Probelm hat sich mit der Antwort von Herbert erledigt.
> Du gibst bei deinen Kriterien z.B. "HDL" ein, aber deine TEIL-Formel
> bringt dir das Ergebnis "HDLLeer". Deswegen gibt es keine
> Übereinstimmung.
So ist es. Nochmals herzlichen Dank an Alle, die hier geantwortet und
geholfen haben!
Gruß
Eisenhut
Herbert Taferner
Bitte gern !
mfg Herbert
Eisenhut
*SEUFZ*
ich hab mal wieder ein Problem: Ich verwende folgende Formel:
{=SUMMENPRODUKT((Datenübernahme!$D$2:$D$50000=D$4)*(Datenübernahme!$F$2:$F$50000=D$5)*(Datenübernahme!$E$2:$E$50000=Händlernummerchiffren!$B$2)*((Datenübernahme!$B$2:$B$50000>=$B$2)*(Datenübernahme!$B$2:$B$50000<=$B$2+6)))}
mit der ich bislang die gewollten Ergebnisse erzielt habe. Allerdings
bekomme ich seit heute als Ergebnis dieser Formel in allen Zellen in denen
ich sie verwende stets "0" als Ergebnis. Und das ist sicherlich falsch. Woran
kann das bitte liegen? In den Einstellungen habe ich jedenfalls wissentlich
nichts geändert, die Formel habe ich nicht geändert, die Bezüge in der Formel
sind die Richtigen, sogar die Formatierungen hab ich in meiner Verzweiflung
überprüft... Ich weiß nicht weiter. Hat jemand ein Idee, woran das liegen
könnte?
Herzlichen Dank und Gruß
Eisenhut
Claus Busch
da du deine Bereiche mit dem Tabellennamen referenzierst, nehme ich an, dass
deine Berechnung weder auf dem Blatt Datenübernahme noch auf
Händlernummerierchiffren durchgeführt wird.
Sind die Kriterien für deine Bereiche auf dem neuen Blatt? Falls nicht,
solltest du sie entsprechend referenzieren.
Eisenhut
"Claus Busch" wrote:
> da du deine Bereiche mit dem Tabellennamen referenzierst, nehme ich an, dass
> deine Berechnung weder auf dem Blatt Datenübernahme noch auf
> Händlernummerierchiffren durchgeführt wird.
> Sind die Kriterien für deine Bereiche auf dem neuen Blatt? Falls nicht,
> solltest du sie entsprechend referenzieren.
Sorry, ich verstehe nicht ganz. Meine Datei schaut etwa so aus: In
Datenübernahme (folgend: "DÜ") hol ich mir Daten aus einer anderen Datei,
jeder Datensatz hat dabei 5 Merkmale, das ist aber unproblematisch. In
Händlernummerchiffren (folgend: "HNC") sind eben Händlernummern gelistet. Die
Berechnung findet in einer eigenen Tabelle "Übersicht" statt, die Formel
hierfür (z. B. in Zelle D6):
{=SUMMENPRODUKT((Datenübernahme!$D$2:$D$50000=D$4)*(Datenübernahme!$F$2:$F$50000=D$5)*(Datenübernahme!$E$2:$E$50000=Händlernummerchiffren!$B$2)*((Datenübernahme!$B$2:$B$50000>=$B$2)*(Datenübernahme!$B$2:$B$50000<=$B$2+6)))}
Zeile4 steht ein Code, nach dem ich in Datenübernahme SpalteD suche usw.
In B2 habe ich die Formel =heute() die mir das aktuelle Datum gibt. In den
letzten beiden Klammerausdrücken der Formel in D6 grenze ich also einen
Zeitraum von einer Woche ab heute ein.
Mit dieser Formel konnte ich bis jetzt gut arbeiten (wenn auch die
berechnugn sehr lange dauert) und habe die richtigen Ergebnisse bekommen.
Seit gestern kommt aber eben in allen Zellen falscherweise eine "0".
(Natürlich ist die Formel der jeweiligen Zelle angepasse, das ist es also
nicht). Ich kann es mir einfach nicht erklären.
Eisenhut
mache das manuell mit F9 oder dem Button aus dem Menü Extras -> Optionen ->
Berechnung. Damit kann mein Problem ja aber eigentlich nix zu tun haben, oder?
Thomas Ramel
Eisenhut schrieb am 03.02.2006
> leider habe ich keine Antwort bzgl. meines letzte Woche aufgetretenen
> Problems bekommen.
>
> Würde sich vielleicht jemand meiner erbarmen und sich bereit erklären, sich
> eine solche Datei von mir mal zuschicken zu lassen um zu schauen, wo der
> Fehler liegt? Dafür wäre ich überaus dankbar!
Ich habe IMO schon ganz zu Beginn gesagt (oder zumindest gemeint ;-)), dass
dies eine gangbare Variante wäre, die ev auch mit Pivot-Tabellen zu lösen
ist.
Meine Adresse hier ist replyfähig; wenn Du die Datei also senden willst,
kann ich sie übers Wochenende mal näher in Augenschein nehmen.
Mit freundlichen Grüssen
Thomas Ramel (@work)
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-1]
Microsoft Excel - Die ExpertenTipps:
(http://tinyurl.com/9ov3l und http://tinyurl.com/cmned)