Microsoft Excel

Herbers Excel/VBA-Archiv

Tabelle in neues Blatt auswerten

Betrifft: Tabelle in neues Blatt auswerten von: Markus
Geschrieben am: 25.08.2004 11:41:41

Hallo,

auf der Suche nach der Lösung meines Problems hat mich Google in dieses sehr informative Forum geschickt. Die Lösung (oder den passenden Ansatz) hab ich dennoch nicht gefunden...

Meine "Aufgabe": Ich habe ein Tabellenblatt, in dem neben einigen anderen Daten die Spalten "Eingang", ErledigungA" und "ErledigungB" existieren. In diesen Spalten stehen "Datums" ("Daten" wäre jetzt wohl nicht eindeutig ;-) ).
Ist noch keine Erledigung erfolgt, so sind ErledigungA und ErledigungB beide nicht ausgefüllt, ist eine Erledigung erfolgt, so steht in einer der beiden Daten ein Datum.
Auf einem neuen Tabellenblatt sollen nun zwei Datums eingegeben werden, anhand derer die obige Tabelle ausgewertet wird. Ausgegeben werden soll z.B. die Anzahl der in dem Zeitraum zwischen den zwei Daten erledigten Fälle. Halt ne Statistik.

Leider bin ich in Excel nicht soo firm, ist für mich bisher halt ein besserer Taschenrechner. Wenn mir jemand sagen könnte, welche Funktionen man dafür brauchen kann bastel ich auch gerne erst noch was selber rum. Wie es logisch aussehen müßte weiß ich ja, nur wie sag ich's Excel?

  


Betrifft: AW: Tabelle in neues Blatt auswerten von: Wolfgang
Geschrieben am: 25.08.2004 11:56:56

Hallo Markus,

ich denke das dich eine PivotTabelle weiterbringen würde...


Gruß
Wolfgang


  


Betrifft: mit Summenprodukt von: Günther Oppold
Geschrieben am: 25.08.2004 12:05:21

Hi Markus,

Es kommt darauf an, ob in beiden Spalten (ErledungA; ErledigungB) die selben Datums stehen, und ob beide Termine im ausgewählten Zeitraum sein sollen
hier ein Beispiel:

Tabelle2
 ABC
1EingangErledigung AErledigung B
201.01.200405.01.200408.01.2004
302.01.200410.01.200428.01.2004
403.01.200415.01.200417.02.2004
504.01.200420.01.200408.03.2004
605.01.200425.01.200428.03.2004
7   
8   
9Zeitraum06.01.200420.02.2004
10nur ErlA im Zeitraum:4 
11nur ErlB im Zeitraum:3 
12beide im Zeitraum2 
Formeln der Tabelle
B10 : =SUMMENPRODUKT((B1:B6>=B9)*(B1:B6<=C9))
B11 : =SUMMENPRODUKT((C1:C6>=B9)*(C1:C6<=C9))
B12 : =SUMMENPRODUKT((B1:B6>=B9)*(C1:C6<=C9))
Excel Tabellen einfach im Web darstellen   Excel Jeanie HTML  2.0    Download  

Günther

  


Betrifft: AW: mit Summenprodukt von: Markus
Geschrieben am: 25.08.2004 13:54:00

Das klingt schon gut, jetzt habe ich nur noch das Problem, dass es entweder einen Eintrag bei ErledigungA, oder einen Eintrag bei ErledigungB, oder halt gar keinen Eintrag gibt (wenn's halt nicht erledigt ist).

Wie berücksichtige ich leere Felder?


  


Betrifft: AW: mit Summenprodukt von: Günther Oppold
Geschrieben am: 25.08.2004 14:21:42

Hallo Markus

das macht die Formel für dich:

Tabelle2
 ABC
1EingangErledigung AErledigung B
201.01.200405.01.200408.01.2004
302.01.200410.01.200428.01.2004
403.01.2004  
504.01.2004 17.02.2004
605.01.200420.01.200408.03.2004
706.01.200425.01.200428.03.2004
8   
9   
10Zeitraum06.01.200420.02.2004
11nur ErlA im Zeitraum:3 
12nur ErlB im Zeitraum:3 
13beide im Zeitraum1 
Formeln der Tabelle
B11 : =SUMMENPRODUKT((B1:B7>=B10)*(B1:B7<=C10))
B12 : =SUMMENPRODUKT((C1:C7>=B10)*(C1:C7<=C10))
B13 : =SUMMENPRODUKT((B1:B7>=B10)*(C1:C7<=C10))
Excel Tabellen einfach im Web darstellen   Excel Jeanie HTML  2.0    Download  

Günther

  


Betrifft: AW: mit Summenprodukt von: Markus
Geschrieben am: 25.08.2004 15:12:48

Sooo einfach... ;-) DANKE! Ich muss es zwar noch umsetzen, aber das sollte keine Probleme bereiten.

Trotzdem noch ne Frage: Die Tabelle, die die Daten enthält, wächst natürlich stetig. Gibt es eine Möglichkeit, dies so in der Formel zu integrieren, dass immer die komplette Spalte benutzt wird? Oder muß ich die Formel entsprechend der Anzahl der Zeilen anpassen?


  


Betrifft: nochmal ich von: Markus
Geschrieben am: 25.08.2004 16:01:13

... und nochmal danke. Funktioniert super. Allerdings...würde ich mich über eine kleine Erklärung freuen, warum das eigentlich geht.

SUMMENPRODUKT liefert doch das Produkt der Summen aus den beiden Klammern. In den Klammern werden Boolsche Werte addiert (also "1" wenn's zutrifft und "0" wenn nicht). Also nimmt Excel im Fall B11 das Produkt der Summe der Felder B1:B7, auf die die Bedingung ">=B10" zutrifft, mit der Summe der Felder B1:B7, auf die "<=C10" zutrifft. So hab ich das jedenfalls verstanden. Aber irgendwie muss das anders sein, sonst würde es ja nicht funktionieren... ;-)


  


Betrifft: Erklärungsversuch von: Günther Oppold
Geschrieben am: 25.08.2004 16:41:57

Hallo Markus

ich war auch verblüfft als ich zum ersten Mal diese Formel im Zusammenhang mit Zählenwenn und Summewenn sah.

Eigentlich ist die Formel gedacht um eine Summe von A1*B1 + A2*B2 + ... zu erstellen.

Sie kann jedoch auch verwendet werden anstelle der "Summewenn" und der "Zählenwenn" Funktionen, wenn mehrere Abfragen/Abgleiche getroffen werden sollen.

Summenprodukt((A1:A1000=xxx) - bewirkt, dass jede Zelle in der Matrix verglichen wird ob die Bedingung erfüllt ist.

Summenprodukt((A1:A1000=xxx)*(B1:B1000=yyy) - bewirkt dass zusätzlich jede Zelle von B verglichen wird ob die Bedingung erfüllt ist.
Für EXCEL sieht das z.B. so aus:
Ergebnis SpalteA: wahr;wahr;falsch;falsch;wahr;wahr ... oder 1;1;0;0;1;1;...
Ergebnis SpalteB: falsch;wahr;wahr;falsch;wahr;falsch;...oder 0;1;1;0;1;0;...

jetzt "multipliziert" EXCEL diese Ergebnisse 1*0=0+1*1=1+0*1=0.....
somit werden die "Zeilen" gezählt, in denen sowohl die Bedingung in A als auch die Bedingung in B übereinstimmen.

Willst Du eine Summe z.B. aus Spalte C haben, dann wird die Formel einfach ergänzt:
Summenprodukt((A1:A1000=xxx)*(B1:B1000=yyy)*(C1:C1000))

Diese Funktion ersetzt die Summewenn - Funktion als ARRAY siehe Beispiel B17

Tabelle4
 ABC
1xxxbbb1
2xxxyyy3
3aaayyy5
4aaabbb7
5xxxyyy11
6xxxbbb13
7   
8   
9   
10xxxyyy 
11 mit Zählen-/Summewennmit Summenprodukt
12Anzahl xxx44
13Anzahl yyy33
14Anzahl xxx+yyy22
15Summe xxx2828
16Summe yyy1919
17Summe xxx+yyy1414
18 B17: ARRAY-Formel: die geschweifte Klammer nicht eingeben sondern die Formeleingabe abschließen mit Strg+Umsch+Enter 
Formeln der Tabelle
B12 : =ZÄHLENWENN(A$1:A$6;A$10)
C12 : =SUMMENPRODUKT((A$1:A$6=A$10)*1)
B13 : =ZÄHLENWENN(B$1:B$6;B$10)
C13 : =SUMMENPRODUKT((B$1:B$6=B$10)*1)
B14 : {=SUMME(WENN((A$1:A$6=A$10)*(B$1:B$6=B$10);1))}
C14 : =SUMMENPRODUKT((A1:A6=A10)*(B1:B6=B10))
B15 : =SUMMEWENN(A$1:A$6;A$10;C$1:C$6)
C15 : =SUMMENPRODUKT((A$1:A$6=A$10)*(C$1:C$6))
B16 : =SUMMEWENN(B$1:B$6;B$10;C$1:C$6)
C16 : =SUMMENPRODUKT((B$1:B$6=B$10)*(C$1:C$6))
B17 : {=SUMME(WENN((A1:A6=A10)*(B1:B6=B10);C1:C6))}
C17 : =SUMMENPRODUKT((A1:A6=A10)*(B1:B6=B10)*(C1:C6))
Excel Tabellen einfach im Web darstellen   Excel Jeanie HTML  2.0    Download  

Günther

  


Betrifft: Größe der Tabelle von: Markus
Geschrieben am: 25.08.2004 18:39:09

Ich glaub ich hab's! Punkt vor Strich wird halt etwas anders ausgelegt, oder anders gesagt, das "*" wird durch die boole'sche Betrachtungsweise zu einem logischen "UND".

Aber eine Frage war noch offen: Kann ich das irgendwie hinbekommen, dass ich die ganze Spalte nehme? Meine Datentabelle wächst ständig, im Moment sind es 692 Zeilen. Kann Excel das irgendwie erkennen und für die auswertenden Formeln nutzen?

Danke nochmal!
Markus


  


Betrifft: AW: Größe der Tabelle von: Günther Oppold
Geschrieben am: 26.08.2004 12:04:36

Markus

2 Möglichkeiten:
1. entweder Du gibst in die Formel einfach gleich A1:A65000 ein (dann wird die Bearbeitung evtl. etwas länger dauern)
2. oder du gibst in eine leere Zelle ein: {=max(wenn(A1:A65000<>"";Zeile(1:65000)))} (ARRAY-Formel: die geschweifte Klammer nicht eingeben sondern die Eingabe abschließen mit Strg+Umsch+Enter)dies ermittelt die letzte gefüllte Zeile in Spalte A

und dann in der Summenprodukt-Formel:
=summenprodukt((indirekt("A1:A"&HILFSZELLE)="xxx")*(....
die indirekt verschachtelung dient dazu, den String mit der jeweils aktuellen Anzahl der gefüllten Zeilen zu füllen.


Günther


 

Beiträge aus den Excel-Beispielen zum Thema "Tabelle in neues Blatt auswerten"