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

Anzahl unterschiedlicher Zeilen

Anzahl unterschiedlicher Zeilen
04.01.2008 14:50:00
Helena
Hallo ihr Excel-Profis,
ich beiße mir die Zähne aus an folgendem Problem:
Spalte A__Spalte B___Spalte C
x________Jenny_____S
x________Jenny_____S
x________Paul______S
x________Paul______A
y________Laura_____S
  • Gesucht wird die Anzahl unterschiedlicher Zeilen, die "x" in der Spalte A und "S" in der Spalte C haben.

  • Die mehrfach vorkommenden Zeilen, die diesen Bedingungen entsprechen, sollten nicht mitgezählt werden.

  • In diesem Fall soll die die Formel den Wert 1 liefern:
    Jenny wird gar nicht gezählt, da mehrfach,
    Paul wird 1 mal gezählt,
    Laura wird nicht gezählt, da nicht "x"
    Das ganze soll ohne Hilfsspalte und ohne Makro funktionieren.
    Vielen Dank und viele Grüße
    verzweifelte Excel-Programmiererin

    16
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    04.01.2008 20:05:00
    Regina
    Hallo Helena,
    probier es doch mal so,
    In d1 = WENN(UND(A1="x";C1="s";B1=B2);0;1)
    in d2 = WENN(UND(A2="x";C2="s";B2=B1);0;1)
    Nur mal so eine Idee.
    Gruß Regina

    AW: Anzahl unterschiedlicher Zeilen
    04.01.2008 20:34:43
    Helena
    Danke für die schnellen Antworten!
    Wie man die Anzahl unterschiedlicher Zeilen ermittelt, habe ich mit Müh und Not bereits rausgefunden.
    Die Aufgabenstellung ist noch etwas schwieriger:
    Die mehrfach vorkommenden Zeilen, die die o.g. Bedingungen erfüllen, sollten nicht mitgezählt werden.
    Hat jemand eine Idee?
    Liebe Grüße
    H

    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    05.01.2008 15:47:04
    Bibo
    Hallo Helena,
    ich hatte Dich gestern schon auf die Lösung unter diesem Link http://www.excelformeln.de/formeln.html?welcher=418 aufmerksam gemacht, Du aber warst der Auffassung, die Aufgabenstellung sie noch etwas schwieriger, da
    
    "die mehrfach vorkommenden Zeilen, die die o.g. Bedingungen erfüllen, nicht mitgezählt werden sollten ."
    

    .
    Ich habe mir jetzt die Mühe gemacht, die dortige Lösung auf Deine Problematik anzupassen und siehe da, es funktioniert.
    Die Matrixformel lautet:
    {=SUMME(N(WENN((A1:A100="x")*(C1:C100="S");VERGLEICH(WENN((A1:A100="x")*(C1:C100="S");B1:B100); WENN((A1:A100="x")*(C1:C100="S");B1:B100);)=ZEILE(1:100)))) }.
    Die Eingabe erfolgt ohne geschweifte Klammern und ist mit der Tastenkombination "strg+Umschalt+enter" abzuschliessen.
    Mit freundlichem Gruß
    Bibo

    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    04.01.2008 22:24:00
    Daniel
    Hi
    also so ganz ohne Hilfsspalten seh ich noch keine Möglichkeit, das zu lösen.
    wenn du eine Hilfsspalte einfügen könntest, in der die 3 Spalten zu einer vereinigt werden (dh. "xJennyS"), dann könnte man es mit dieser Formel auswerten:
    
    {=SUMME(1*(1=WENN(D1:D7="x"&B1:B7&"S";ZÄHLENWENN($D$1:$D$7;"x"&B1:B7&"S");0)))}
    


    (Matrix-Formel, dh. ohne geschweifte Klammern eingeben und STRG+SHIFT+ENTER zur Eingabe drücken.)
    die Hilfsspalte steht in Spalte D und hat die Formel
    =A1&B1&C1
    ich las die Frage mal offen, weils deine Anforderungen ja nicht ganz erfüllt, allerdings würde mich interessieren, warum keine Hilfsspalten zulässig sein sollen.
    Falls es ne prüfungsaufgabe ist und eine Lösung existiert, würde mich die Lösung interessieren.
    Gruß, Daniel

    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    05.01.2008 00:39:00
    Helena
    Die Hilfspalten sind in diesem Fall nicht zulässig, weil es eine beträchtliche Anzahl von "Spalten C" gibt, also den Spalten, in denen nach "S" gesucht wird. Man bräuchte für jede dieser Spalten eine Hilfsspalte.
    Außerdem soll die Tabelle durch Einfügen weiterer Zeilen und Spalten durch den Endanwender erweiterbar sein, ohne dass danach an vielen Stellen Anpasungen erforderlich werden.
    Der Auftraggeber hat sich deshalb eine Lösung ohne Hilfsspalten gewünscht.

    AW: Anzahl unterschiedlicher Zeilen
    05.01.2008 12:16:58
    Daniel
    Hi
    dann wirst du wohl um eine UDF (UserDefiniedFunction), also um eine selbst geschriebene Funktion nicht herumkommen.
    ist zwar auch ne Lösung mit VBA, aber der Endanwender kann dann diese Funtktion wie eine normale Excelfunktion einestzen.
    Gruß, Daniel

    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    05.01.2008 16:28:00
    Bibo
    Hallo Helena,
    ich hatte Dich gestern schon auf die Lösung unter diesem Link http://www.excelformeln.de/formeln.html?welcher=418 aufmerksam gemacht, Du aber warst der Auffassung, die Aufgabenstellung sie noch etwas schwieriger, da
    "die mehrfach vorkommenden Zeilen, die die o.g. Bedingungen erfüllen, nicht mitgezählt werden sollten ."
    .
    Ich habe mir jetzt die Mühe gemacht, die dortige Lösung auf Deine Problematik anzupassen und siehe da, es funktioniert.
    Die Matrixformel lautet:
    {=SUMME(N(WENN((A1:A100="x")*(C1:C100="S");VERGLEICH(WENN((A1:A100="x")*(C1:C100="S");B1:B100); WENN((A1:A100="x")*(C1:C100="S");B1:B100);)=ZEILE(1:100)))) }.
    Die Eingabe erfolgt ohne geschweifte Klammern und ist mit der Tastenkombination "strg+Umschalt+enter" abzuschliessen.
    Mit freundlichem Gruß
    Bibo

    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    05.01.2008 20:21:00
    Helena
    Hallo Bibo,
    vielen Dank für Deine Mühe!
    Ich habe die von Dir vorgeschlagene Formel getestet.
    Leider liefert sie ein falsches Ergebnis: die doppelten Zeilen werden (1x) mitgezählt, es wird also die Anzahl unterschiedlicher Zeilen berechnet. Ich brauche aber die Anzahl der Zeilen, die genau einmal vorkommen.
    Oder mache ich etwas falsch?
    Vielen Dank
    und herzliche Grüße
    Helena

    AW: Anzahl unterschiedlicher Zeilen
    05.01.2008 21:32:00
    Daniel
    Hi
    ich denke, ohne VBA oder Hilfsspalten wird das Problem nicht zu lösen sein.
    und wenn ja, dann musst du deinem Auftraggeber eine Monsterformel präsentieren, mit der er wahrscheinlich auch nicht glücklich wird, wenn er Zeilen und Spalten einfügt und die Formel anpassen muss.
    Beser wärs, du schreibst ihm ne benutzerdefinierte Funktion (UDF), mit den 5 Parametern Spalte1, Spalte 2, Spalte 3, Kennbuchstabe für Spalte1, Kennbuchstabe für Spalte 3.
    diese Benutzerdefinierte Funktion kann er dann ganz normal wie eine Standard-Excelformel in der Tabelle verwenden und pflegen.
    Gruß, Daniel

    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    07.01.2008 06:39:00
    Helena
    Hilfe!
    Gibt es denn keine Möglichkeit, das VBA zu umgehen?
    Und wenn doch mit VBA: kann mir jemand helfen? Bin langsam am verzweifeln...

    AW: Anzahl unterschiedlicher Zeilen
    07.01.2008 14:16:58
    fcs
    Hallo Helena,
    Die Lösung ohne VBA erfordert meines Wissens eine Hilfsspalte. In dieser Spalte wird berechnet, wie oft die Kombination der Werte in den 3 Spalten vorkommt. Geht super mit Funktion SUMMENPRODUKT und WAHRHEITS-Bedingungen für die 3 Spalten.
    Formel in Spalte D entsprechend für alle Zeilen nach unten kopieren
    
    =SUMMENPRODUKT((A2=$A$2:$A$1000)*(B2=$B$2:$B$1000)*(C2=$C$2:$C$1000))
    


    Für die Berechnung der Anzahl der nichtdoppelten Einträge, die die Kriterien erfüllen kann man dann nochmals die Funktion SUMMENPRODUKT benutzen unter Verwendung der Hilfsspalte.

    
    =SUMMENPRODUKT(($A$2:$A$1000=F3)*($C$2:$C$1000=G3)*($D$2:$D$1000=1))
    


    Die Kriterien für Spalte A bzw. C stehen in Zellen F3 bzw. G3
    Nachfolgende benutzerdefierte Funktion ermittelt ohne Hilfspalte die Anzahl. Die Hilfsspalte wird dabei in der Funktion als Feldvariable erzeugt.
    In der Beispieldatei hab ich beide Lösungen eingebaut.
    https://www.herber.de/bbs/user/48851.xls
    Gruß
    Franz

    
    Public Function AnzahlNichtDoppelt(Spalte1 As Range, Spalte2 As Range, Spalte3 As Range, _
    Kriterium1 As String, Kriterium3 As String, Optional GrossKlein As Boolean = False) As Long
    ' Ermittelt die Anzahl nicht doppelter Einträge, die die Kriterien erfüllen
    'Spalte1 = Zellbereich mit dem 1. Kriterium
    'Spalte2 = Zellbereich mit den Werten
    'Spalte3 = Zellbereich mit dem 2. Kriterium
    'Kriterium1 und Kriterium2 sind die Kriterien für Spalte1 und Spalte3, _
    'GrossKlein = Option ob  Groß-Kleinschreibung beachtet werden soll, Standard = False (Nein)
    'Formelbeispiele:
    ' =AnzahlNichtDoppelt(A:A;B:B;C:C;"x";"S")
    ' =AnzahlNichtDoppelt(A2:A1000;B2:B1000;C2:C1000;"x";"S";WAHR)
    'Prüfung ob die 3 Bereiche die gleiche Zeilenzahl haben
    If Not (Spalte1.Rows.Count = Spalte2.Rows.Count And Spalte2.Rows.Count = Spalte3.Rows.Count)  _
    Then
    MsgBox "Die 3 Spaltenbereiche müssen die gleiche Zeilenzahl haben!"
    Exit Function
    End If
    Dim Anzahl As Long, Zeile As Long, Zeile2 As Long, wks As Worksheet, ZeileL As Long
    Dim arrZusammen() As String, arrGezaehlt() As Boolean, strZusammen As String
    Set wks = Spalte1.Parent
    'Letzte Zeile mit Daten
    With wks
    ZeileL = .Cells(.Rows.Count, Spalte1.Column).End(xlUp).Row
    End With
    ReDim arrGezaehlt(1 To ZeileL)
    ReDim arrZusammen(1 To ZeileL)
    If GrossKlein = True Then
    For Zeile = 1 To ZeileL
    arrZusammen(Zeile) = Spalte1(Zeile, 1) & Spalte2(Zeile, 1) & Spalte3(Zeile, 1)
    Next
    For Zeile = 1 To ZeileL
    If arrGezaehlt(Zeile) = False Then
    arrGezaehlt(Zeile) = True
    If Kriterium1 = Spalte1(Zeile, 1) And Kriterium3 = Spalte3(Zeile, 1) Then
    strZusammen = Spalte1(Zeile, 1) & Spalte2(Zeile, 1) & Spalte3(Zeile, 1)
    Anzahl = 1
    For Zeile2 = Zeile + 1 To ZeileL
    If strZusammen = Spalte1(Zeile2, 1) & Spalte2(Zeile2, 1) & Spalte3(Zeile2, 1) Then
    arrGezaehlt(Zeile2) = True
    Anzahl = Anzahl + 1
    End If
    Next
    If Anzahl = 1 Then
    AnzahlNichtDoppelt = AnzahlNichtDoppelt + 1
    End If
    End If
    End If
    Next
    Else
    For Zeile = 1 To ZeileL
    arrZusammen(Zeile) = UCase(Spalte1(Zeile, 1) & Spalte2(Zeile, 1) & Spalte3(Zeile, 1))
    Next
    For Zeile = 1 To ZeileL
    If arrGezaehlt(Zeile) = False Then
    arrGezaehlt(Zeile) = True
    If UCase(Kriterium1) = UCase(Spalte1(Zeile, 1)) _
    And UCase(Kriterium3) = UCase(Spalte3(Zeile, 1)) Then
    strZusammen = UCase(Spalte1(Zeile, 1) & Spalte2(Zeile, 1) & Spalte3(Zeile, 1))
    Anzahl = 1
    For Zeile2 = Zeile + 1 To ZeileL
    If strZusammen = UCase(Spalte1(Zeile2, 1) & Spalte2(Zeile2, 1) & Spalte3(Zeile2, 1)) _
    Then
    arrGezaehlt(Zeile2) = True
    Anzahl = Anzahl + 1
    End If
    Next
    If Anzahl = 1 Then
    AnzahlNichtDoppelt = AnzahlNichtDoppelt + 1
    End If
    End If
    End If
    Next
    End If
    End Function
    


    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    07.01.2008 14:54:00
    Rainer
    Probiers mal damit:
    
    Function ZähleSpezial(b1 As Range, b1_w, b2 As Range, b3 As Range, b3_w)
    Dim c1, c2
    Dim kombi1
    ZähleSpezial = 0 ' Zähler auf Null setzten
    For Each c1 In b1 ' Äussere Schleife: Kombination festlegen
    kombi1 = c1.Value & Cells(c1.Row, b2.Column).Value & Cells(c1.Row, b3.Column).Value
    For Each c2 In b1 ' Innere Schleife: Kombination nochmal vorhanden?
    If c1.Row  c2.Row Then
    If kombi1 = c2.Value & Cells(c2.Row, b2.Column).Value & Cells(c2.Row, b3.Column) _
    .Value Then
    GoTo Next_c1 ' Kombination nicht gefunden -> nicht zählen
    End If
    End If
    Next c2
    If c1.Value = b1_w And Cells(c1.Row, b3.Column).Value = b3_w Then ' nur zählen, wenn  _
    Bedingungen stimmen
    ZähleSpezial = ZähleSpezial + 1
    End If
    Next_c1:
    Next c1
    End Function
    


    Diese Funktion muss in einem Modul stehen.
    Aufgerufen wird sie dann in Excel Beispielsweise so: =ZähleSpezial(A1:A16;"x";B1:B16;C1:C16;"S")
    Gruss
    Rainer

    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    07.01.2008 16:33:00
    Danke!!
    Vielen Dank!!
    habe die Funktion von fsc getestet, funktioniert prima! Besten Dank!
    Muss aber leider noch einige Anpassungen vornehmen, und da stoße ich an meine Grenzen :(
    Bin halt kein VBA-Profi...
    Folgende Anpassungen sind noch notwendig:
    1. Die Spalte1 ist eigentlich eine Hilfsspalte, die weg soll. Die Inhalte dieser Spalte sollen direkt mit VBA berechnet werden. Es handelt sich um ein Verweis auf eine andere Tabelle:
    =INDEX($C$2:$C$24;VERGLEICH($D137;$D$2:$D$24;0)), runterkopiert.
    Wie kriege ich das hin?
    2. Mit der Suche soll nicht in Zeile 1 angefangen werden, sondern in ZeileA.
    Ich habe es damit versucht:
    ZeileE = Range("AnfangProjekte").Row; "AnfangProjekte" ist ein Bereichsname. Korrekt so?
    https://www.herber.de/bbs/user/48858.xls
    PS: Kam leider noch nicht dazu, die Fkt von Rainer Pliskat zu testen, sorry..

    Anzeige
    AW: Anzahl unterschiedlicher Zeilen
    08.01.2008 12:44:00
    fcs
    Hallo Helena,
    zu 1.:
    Ich hab die Function jetzt mal so modifiziert, dass die Hilfswerte aus Spalte A auch innerhalb der Funktion berechnet werden. Leider funktionierte die INEDX-Funktion innerhalb der Prozedur nicht korrekt, so dass ich hierfür eine For-Next Schleifen-Konstruktion wählen musste, um für die Projekte den Projektmitarbeitern den entsprechenden GB zuzuweisen. Die Bereiche, die vorher in der INDEX-Funktion enthalten waren, sind jetzt als Parameter in der Function enthalten.
    Die Namen der Parameter hab ich an die Bezeichnungen in der Tabelle angepasst. So liest sich das ganze etwas einfacher.
    zu 2.:
    Die Syntax ist so korrekt.
    Es ist aber meistens besser, Werte als Parameter an Funktionen zu übergeben statt sie erst in der Funktion auszulesen/zu berechnen.
    In diesem Fall ist dies aber nicht nötig, wenn die Bereiche für die Formeln entsprechend angegeben werden. Also die Zeilenzahl in der Formel gleich so festlegen, dass beim Einfügen weiterer Daten-Zeilen die Bereiche in den Formeln automatisch angepasst werden oder die letzte Zeile entsprechend hoch festlegen um zukünftige Zeilen zu erfassen.
    https://www.herber.de/bbs/user/48878.xls
    Gruß
    Franz

    Es klappt!
    08.01.2008 14:57:57
    Helena
    Danke Franz, hast mich gerettet :)
    Funktioniert prima!
    Danke für die sehr kompetente Unterstützung!
    Viele Grüße
    Helena

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige