Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
868to872
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
868to872
868to872
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Summenprodukt Zählenwenn

Summenprodukt Zählenwenn
20.05.2007 16:08:05
Katja

Hallo,
ich habe mal wieder ein Problem mit einer Formel:
Die Formel soll mir ausgeben, wie oft das Material bei verschiedenen Maschinen verwendet wird.
=AUFRUNDEN(SUMMENPRODUKT(-(Master!$B$32:$B$95=A3)/ZÄHLENWENN(Master!$A$32:$A$95;Master!$A$32:$A$95)); 0)
Folgende Aufgabe hat die Formel:
Vergleichen von Wert A3 mit dem Datenbereich B32:B95. Zusammenzählen wie Häufigkeit der Wert vorkommt, wenn die Werte in Master!A32:A95 unterschiedlich sind.
Aber irgendwie kommt immer nur -1 heraus.
Grüße
Katja

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summenprodukt Zählenwenn
20.05.2007 17:37:27
Erich G.
Hallo Katja,
so ganz klar ist (mir) nicht, was wo steht und was du ermitteln möchtest.
Ist es die Anzahl Maschinen, bei denen ein Material auftaucht, wobei jede Mschine nur einmal gezählt werden soll?
Etwas so?
AW: Summenprodukt Zählenwenn
20.05.2007 18:53:39
Erich G.
Hallo Katja,
wenn du die (richtige) C2-Formel nach unten kopierst, klappt es doch schon.
Das Beispiel ist nicht besonders vielsagend - es kommt überall 1 raus.
Warum hast du ab C3 bei ZEILE(....) die Dollarzeichen weggelassen? Dadurch entstand der Fehler.
Noch eine Bemerkung:
In Spalte "Materialnummer" stehen Texte (hier erkennbar an den führenden Nullen und der linksbündigen Darstellung).
Warum haben die Zellen teilweise Standard-, teilweise Buchhaltungsformat?
Format "Text" wäre passender...
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Summenprodukt Zählenwenn
20.05.2007 18:37:55
Katja
Hallo Erich,
ich habe nun die Formel mit mit $ auf bestimmte Spalten festgelegt und bei Zeile auch $ eingefügt:
{=SUMME((VERGLEICH(Master!$A$32:$A$95&Master!$B$32:$B$95;Master!$A$32:$A$95&Master!$B$32:$B$95;0)=ZEILE($1:$64) *(Master!$B$32:$B$95=A2))*1) }
Aber wenn ich diese übertragen in meine eigentlich Tabelle erhalte ich den Fehler #N - vielleicht liegt das an "Zeile($1:$64)" ich verstehe ein bißchen was der Befehl "Zeile" macht, aber was macht er in diesem Fall?
Grüße
Katja

AW: Summenprodukt Zählenwenn
20.05.2007 19:07:43
Erich G.
Hallo Katja,
VERGLEICH(Master!$A$32:$A$95&Master!$B$32:$B$95;Master!$A$32:$A$95&Master!$B$32:$B$95;0)
liefert in einer Matrix (genauer einem 64-stelligen Vektor) die Nummer (ab 1 gezählt) der jeweils ersten Zeile,
in der A-Wert & B-Wert in der Matrix A-Werte & B-Werte auftaucht. Die Nummer wird mit der jeweiligen
Quell-Zeilennummer verglichen.
Wenn z. B. A35&B35 schon einmal in A33&B33 erscheint, ist Vergleich(...) = 2 (2. Zeile des Bereichs).
ZEILE(...) ist aber 4, also wird der Wert nicht gezählt.
Anders gesagt:
Gezählt wird nur, wenn das Auftrreten einer A&B-Kombination das erste Auftreten dieser Kombination
im betrachteten Bereich ist.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Summenprodukt Zählenwenn
20.05.2007 19:41:18
Katja
Hallo Erich,
also wenn ich es richtig verstehe, dann ermittelt "Vergleich(...)" die erste Position, in der das gesuchte in der Tabelle auftaucht.
Deine 64 verstehe ich nun auch, d.h. Bereich 32bis95 = 64 Zellen, die durchsucht werden.
Das mit Zeile(...) verstehe ich aber immer noch nicht. Ich verstehe den Rechenweg einfach nicht, hast du nicht eine Beispielaufgabe für mich? In der Excelhilfe steht zwar, dass ZEILE(A3) 3 ergibt (ich verstehe), aber ZEILE (A3:B5) den vertikalen Vektor von {3;4;5} (und was ist nun dieser Vektor ausgerechnet?)
Wie du siehst, hängt es einfach manchmal bei mir bei Kleinigkeiten, aber irgendwann kapiere ich es auch.
Habe versucht, die Vektorrechnung nachzuvollziehen, aber Excel rechnet anders als ich.
Für deinen Hinweis, auf meine Formatierung bedanke ich mich herzlich, das hatte ich noch gar nicht gemerkt. Die Formel habe ich nun korrekt runterkopiert und die Werte habe ich bei Stückzahl auch geändert, damit es interessanter wird.
Für deine Hilfe danke ich mich sehr. :)
Katja

Anzeige
AW: Summenprodukt Zählenwenn
20.05.2007 21:29:23
Erich G.
Hallo Katja,
schreib mal in eine Zelle die Formel
=ZEILE(1:5)
als Matrixformel, also mit Strg+Shift+Eingabe.
Wenn du dann in der Bearbeitungsleiste in die Formel klickst und F9 drückst, siehst du als Ergebnis
={1;2;3;4;5}, also einen Vektor mit den Zahlen 1 bis 5. Mehr verbirgt sich nicht dahinter.
Wenn der VERGLEICH-Wert gleich der Zeile ist, heißt das, dass eine Kombination der Werte
aus A und B einer Zeile in der Zeile erstmalig auftritt, also potentiell zu zählen ist (wenn dann auch = A3).
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Summenprodukt Zählenwenn
20.05.2007 22:13:48
Katja
Hallo Erich,
ich habe Probleme deiner Erklärung zu folgen:
Vektor-Matrix bei Zeile(1:5) heißt nun 5!/5! = 1*2*3*4*5/ (1*2*3*4*5) = 1
Also bei deiner Formel = Zeile(1:64) = 64!/64! =1 - Warum muss ich das in die Formel schreiben? Weil ich den Wert mindestens einmal brauche?
Ich möchte gerne die Formel verstehen, damit ich nächstesmal es selber lösen kann.
Ich bedanke mich herzlich dafür, dass Du es versuchst mir zu erklären.
Katja

AW: Summenprodukt Zählenwenn
20.05.2007 22:34:26
Erich G.
Hallo Katja,
hier habe ich noch mal anders dargestellt, was z. B. ZEILE(1:5) ist:
AW: Summenprodukt Zählenwenn
20.05.2007 23:04:02
Katja
Hallo Erich,
ich glaube, dass wir beide eine andere Definition von Vektoren haben.
Mein Problem ist: Warum kommt 1 heraus? Was unternimmt Excel, um auf diesen Wert zu kommen?
Ich kann nicht Rechnungsweg nicht nachvollziehen.
Grüße
Katja

Anzeige
Zeile Vektor
20.05.2007 23:12:18
Katja
Hallo,
wenn ich zum Beispiel in der Master Tabelle folgendes eingebe: =ZEILE(H11:H18) ~f~ bekomme ich den Wert 11, aber warum? Weil ich mit H11 der niedrigste Wert in meiner Formel ist?
Gebe ich ~f~ =ZEILE(1:1128) bekomme 1. Den Unterschied verstehe ich nicht.
Grüße
Katja

AW: Zeile Vektor
20.05.2007 23:48:11
Erich G.
Hallo Katja,
die Funktion ZEILE(1:5) hat den selben Wert wie ZEILE(A1:A5), ist nur kürzer hinzuschreiben.
(In ZEILE(1:5) bezeichnet 1 die ganze Zeile 1, 5 die ganze Zeile 5.)
Beide Funktionen haben als Wert den Vektor {1;2;3;4;5}.
Wenn mn die Formel (ob als normale oder als Matrixformel, ist egal) in eine einzelne Zelle stellt,
kann Excel nur einen - den ersten - Wert des Vektors in die Zelle schreiben.
(Dies habe ich im folgenden Beispiel in Zeile 1 getan.)
Wenn man zuerst einen Ergebnisbereich markiert und dann die Formel als Matrixformel reinschreibt,
sieht man als Werte alle Stellen des Vektors.
In der Tabelle habe ich die grün, orange bzw. blau gefärbten Bereiche markiert, die Formel einmal hingeschrieben
und dann mit Strg+Umschalt+Eingabe als Matrixformel in den jeweiligen Bereich gestellt.
Dabei kommt dann das heraus:
Anzeige
AW: Zeile Vektor
20.05.2007 23:55:45
Christian
Hallo Katja,
diese Formeln funkt. als Vektoren in Matrix-Formeln und ersparen einiges an Eingabe.
Bsp:
Du willst die letzte Zeile finden im Bereich A1:A9 der eine 4 enthält:
Übertrage mal das Bsp. in ein Tabelle und lass mal die Formelauswertung laufen.
Tabelle1

AW: Summenprodukt Zählenwenn
23.05.2007 13:03:16
Katja
Also das schlaue Buch sagt, dass die Funktion ZEILE() die Zeilennummer von Bezug zurück gibt.
Ich verstehe das nun so, dass wenn ich einen Bereich also Matrix angeben und dort noch eine zusätzliche Funktion wie WENN oder VERGLEICH habe, dass er mir die Zeile angibt, wo ich den Wert zuerst finde. Die zweiten Wert usw nicht.
Habe ich das nun richtig verstanden?
Grüße
Katja

Anzeige
AW: Summenprodukt Zählenwenn
20.05.2007 19:22:54
Erich G.
Hallo Katja,
hier noch der besagte Teilaspekt VERGLEICH zu ZEILE():
AW: Summenprodukt Zählenwenn
20.05.2007 19:33:18
Erich G.
Hallo Katja,
in meiner Formel waren die Klammern etwas verquer gesetzt, also nochmal, ohne "*1":
AW: Summenprodukt Zählenwenn
20.05.2007 20:32:04
Daniel
Hallo
wenn ichs richtig verstanden habe, soll gezählt werden, an wieviel verschiedenen Maschinen eine bestimmte Materialsorte verwendet wird.
da mir das mit den Formeln zu komplex ist, hab ich mal versucht, es als benutzerdefinierte Funktion zu schreiben:
hier das Beispiel:
https://www.herber.de/bbs/user/42588.xls
1. Parameter: die einzelne Material-Nr, nach der gesucht wird.
2. Parameter: der Zellbereich mit den Material-Nummern
3. Parameter: der Zellbereich mit den Maschinen-Benennungen
und hier der Code dazu:


Public Function Anz_Masch_pro_MatArt(MatArt, Material As Range, Maschinen As Range) As Variant
If Material.Cells.Count <> Maschinen.Cells.Count Then GoTo Fehler
If Material.Row <> Maschinen.Row Then GoTo Fehler
Dim Mat
Dim Masch
Dim Masch_Speicher()
Dim i As Long, j As Long
Dim Zähler As Long
Mat = Material
Masch = Maschinen
For i = 1 To UBound(Mat, 1)
If Mat(i, 1) = MatArt Then
Select Case Zähler
Case 0
Zähler = Zähler + 1
ReDim Masch_Speicher(Zähler)
Masch_Speicher(Zähler) = Masch(i, 1)
Case Else
For j = 1 To Zähler
If Masch(i, 1) = Masch_Speicher(j) Then Exit For
Next
Select Case j
Case Is > Zähler
Zähler = Zähler + 1
ReDim Preserve Masch_Speicher(Zähler)
Masch_Speicher(Zähler) = Masch(i, 1)
Case Else
End Select
End Select
End If
Next
Anz_Masch_pro_MatArt = Zähler
Exit Function
Fehler:
Anz_Masch_pro_MatArt = "Zellbereiche passen nicht"
End Function


Gruß, Daniel

Anzeige
AW: Summenprodukt Zählenwenn
20.05.2007 22:01:18
Katja
Wow, jetzt hast du mich getroffen.
Es klappt, aber warum, verstehe ich nicht. Mit Makros kenne ich mich nicht aus.
Ich müßte dieses Makro nun in meine Ursprungsdatei umschreiben und die ist nicht so schön übersichtlich wie die hochgeladene.
Werde nun mein Makro-Buch zur Hand nehmen und sehen, ob ich deinen Code nachvollziehen kann.
Vielen Dank.

AW: Summenprodukt Zählenwenn
20.05.2007 22:47:41
Daniel
Hallo
du brauchst nur den Code in deine Ursprungsdatei zu kopieren, dann kannst du die Formel auch dort ganz normal wie jede andere Excel-Formel verwenden. Die Zellbereiche der beiden Letzten Parameter kannst du ja beliebig angeben, Sie müssen nur gleich gross sein und in der gleichen Zeile beginnen
das kopieren geht relativ einfach, da du ja offensicht ich noch keine weiteren Makros verwendest, einfach
- die Beispiel-Datei mit dem Code und deine Ursprungsdatei öffnen
- in den VBA-Editor wechseln (über EXTRAS-MAKRO-VisualBasic Editor)
- dort dann im Projektexplorer (fenster oben links) das Element MODUL1 per Drag'n'Drop in die Baumstruktur der Anderen Datei ziehen.
Gruß, Daniel
Userbild

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige