Spezialformel für Excel

  • hi

    kann mir jemand von euch weiterhelfen ???
    ich bin schon beim verzweifeln !!! :kp: :stocksauer:

    ich bin auf der suche nach einer speziellen formel für excel 2003.

    ich habe eine tabelle mit 4 spalten und mehreren zeilen. in diesem bereich stehen viele verschiedene werte. nun brauche ich eine formel, die mir diesen bestimmten bereich durchsucht und in einem anderen tabellenblatt die anzahl gleicher werte wieder ausgibt.

    also: bereich auf gleiche werte untersuchen und die anzahl in einem neuen tabellenblatt ausgeben.

    z.b.: 98 - 56 - 36 - 42
    42 - 54 - 33 - 78
    usw.

    es sollte im neuen tabellenblatt nun folgendes herauskommen:
    1x 98
    1x 56
    1x 36
    2x 42
    1x 54
    1x 33
    1x 78


    ich hoffe, dass ihr meine frage halbwegs verstehen und mir helfen könntet.

    danke bereits im voraus.[/url]

    • Anzeige

    Hallo!

    Wenn du gerade an deiner Website arbeitest oder dein aktuelles Hosting überdenkst: Wir betreiben mit NetzLiving eine Hosting-Plattform, die speziell auf Performance, Sicherheit und einfache Verwaltung ausgelegt ist.

    • ✔️ Schnelle Ladezeiten (optimiert für WordPress, WoltLab & Co.)
    • ✔️ Deutsche Server & DSGVO-konform
    • ✔️ Persönlicher Support (kein 0815-Ticket-System)

    Mehr erfahren

  • Das macht die Formel "Zählenwenn":

    Deine Zahlen stehen auf Blatt "Tabelle1" im Bereich A1 : D14

    Auswertung auf Blatt "Tabelle2":

    =ZÄHLENWENN(Tabelle1!$A$1:$D$14;Tabelle1!A1)

    Die Dollarzeichen legen den Suchbereich als absoluten Bezug fest, so kannst du die Formel nach unten erweitern. Um zu wissen welche Zahlen in der von Zählenwenn ermittelten Häufigkeit auftreten, musst du dann einfach die Formel um den verknüpften Wert der auszuwertenden Zelle erweitern:

    =Tabelle1!A1&"="&ZÄHLENWENN(Tabelle1!$A$1:$D$14;Tabelle1!A1)

    Du kannst dir ->hier<- eine Beispieldatei runterladen.

    Herzlichen Gruß,
    Tunarus

  • alles gut, damit hast du mir schon ein stück weiter geholfen.

    danke

    jedoch kommen in den vier spalten, wenn ich die spalte a aus der tabelle 2 erweitere auf die spalte b,c & d, dann kommen werte wie z.b.: 78=2 zweimal vor. ich weis, dass in der tabelle der wert 78 zweimal vorkommt, jedoch wie kann ich die zweite ausgabe von 78=2 umgehen.

    es sollen in etwa 4 spalten in 1 zusammengefasst werden. damit meine ich, wenn ich in 4 spalten und mehreren zeilen z.b.: gesamt 3x den wert 78 habe, dann soll in einem zweiten tabellenblatt einmal in einer zelle stehen 78=3.

    hoffe, dass ihr euch auskennt.

    danke bereits im voraus.

  • ja, ich hab das beispiel runtergeladen.

    aber z.b. der wert 33, der sich in c14 befindet (ích hab werte getauscht), kommt in der tabelle 2 in der spalte a nicht vor.
    das gleiche ist auch mit 44 aus der c8 der fall.

    kannst du mir weiterhelfen ???

    danke im voraus.

  • Mit Formeln ist das nicht letztendlich zu lösen, dazu müsste man ein Makro über deine Liste schicken, da man nur mit VBA die Schleifen produzieren könnte, die deine Matrix durchlaufen und die Werte zählen (...für alle VBA´ler: Loop-Schleife alle Werte untereinander schreiben lassen und dann Zählenwenn). Formeln scheitern an der Matrix, also an der Tatsache, dass deine Werte in mehr als einer Spalte stehen.

    Ich habe die Datei überarbeitet. Nun werden in der Matrix einfach die Werte an derselben Stelle wiederholt und ihre Häufigkeit angezeigt. Wenn dir das nicht reicht und du es genau so, wie in deinem Beispiel benötigst, dann schicke mir mal deine Datei an info1 (at) tunarus.de. Ich programmier´ dir das dann (...kann aber ein bisschen dauern, bin grad im Urlaub. :))

    Gruß,
    Tunarus

  • oooch nö, das lässt sich auch mit Excel-Formeln lösen.

    Ich bin mal davon ausgegangen, dass die Matrix maximal 14 Zeilen haben wird. (Natürlich ist das beliebig erweiterbar. Da allerdings nicht alle Formeln mit der automatischen Kopierfunktion vervielfältigbar sind, sondern etwas Handarbeit angesagt war, wollte ich Arbeit etwas begrenzen...)

    Außerdem habe ich, was ich meistens mache, Die Dateneingabe und Ergebnis-Ausgabe auf einem Blatt, (Tabelle 1), die Hintergrund-Berechnungen aber auf einem anderen (Tabelle 2).
    Die Matrix (Eingabe) ist auf Tabelle 1, A1 bis D14.

    Da ich später die Auswertung über "Sverweis" machen möchte, brauche ich die Daten zuerst in 1 Spalte untereinander.

    Dafür habe ich in Spalte B auf Tabelle 2 erst mal von 1 bis 56 (4 Spalten a 14 Zeilen = 56 Zellen) durchnummeriert (Spalte A brauche ich später noch!).

    In Spalte D auf Tabelle 2 Werden nun alle Zahlen in aufsteigender Reihenfolge untereinander geschrieben, beginnend bei D1.
    Das leistet die Formel:

    =WENN(ISTFEHLER(KKLEINSTE(Tabelle1!$A$1:$D$14;B1));"";KKLEINSTE(Tabelle1!$A$1:$D$14;B1))

    Spalte B Tabelle 2 fungiert dabei als Zähler... D2 lautet dann:

    =WENN(ISTFEHLER(KKLEINSTE(Tabelle1!$A$1:$D$14;B2));"";KKLEINSTE(Tabelle1!$A$1:$D$14;B2))

    Prima, nun stehen sie untereinander, aber mehrfach vorkommende leider eben mehrfach.

    In Spalte E Tabelle 2 kann ich das schon mal auszählen. Die Formel:

    =WENN(D1="";"";ZÄHLENWENN($D$1:$D$56;D1))

    (hier für E1) schreibt neben jede Zahl brav, wie oft die in der Liste vorkommt.

    Links davon, in Spalte C Tabelle 2, lasse ich mir auch noch den Rang jeder Ziffer anzeigen. Die Formel:

    =RANG(D1;$D$1:$D$56;1)

    in C1 für die Zahl in D1 (welche ja die kleinste der Liste ist), und ab dann, ab C2:

    =WENN(D2="";"";WENN(RANG(D2;$D$1:$D$56;1)=RANG(D1;$D$1:$D$56;1);"";RANG(D2;$D$1:$D$56;1)))

    (wobei mehrfach vorkommende Gleichränge gleich ausgeblendet werden), bildet die Rangfolge ab. Dumm nur, dass Excel dabei für die Zahlenreihe "3,9,9,22,34....so zählt:

    3 = Rang 1
    9 = Rang 2
    9 = Rang 2 (<ausgeblendet!)
    22 = Rang 4
    34 = Rang 5

    also den Rang 2 zweimal vergibt, dafür Rang 3 weg lässt. Das ist so also noch NICHT so verwendbar, wie wir's am Ende haben wollen.

    Also legen wir nach (jetzt kommts!) :D :

    In Spalte A bilde ich nun noch mal die Rangfolge der Rangfolge Spalte C:

    =WENN(ISTFEHLER(RANG(C1;$C$1:$C$56;1));"";RANG(C1;$C$1:$C$56;1))

    Cool was? Nun wird aus:

    1 > 1
    2 > 2
    (leer)
    4 > 3
    5 > 4 ... u.s.w...

    Tja, und damit ist mein Berechnungsblatt in Tabelle 2 vollständig. Ich fasse noch mal zusammen:

    Spalte A: Rangfolge in aufsteigender Reihe
    Spalte B: Hilfszahlen (Zähler 1 - 56)
    Spalte C: Rangfolge a la Excel
    Spalte D: Alle vorkommenden Zahlen aufsteigend geordnet
    Spalte E: Je vorkommender Zahl > Häufigkeit des Vorkommens

    BRAUCHEN tun wir Spalten A, D und E -
    B und C sind nur Hilfen.
    Weiter geht's nun wieder Auf Tabelle1, das Ergebnis wird ausgewertet, in der Form:

    Element - Anzahl, und natürlich NUR für die Elemente, die's gibt, ohne Lücken untereinander, so sollte es ja sein....

    Dabei benutze ich nun den SVERWEIS, und nun wird auch klar, warum die bereinigte, lückenfreie Rangfolge bei Tabelle 2 in Spalte A stehen MUSS: SVERWEIS kann nur nach rechts zählen!!!

    Die Ergebnisliste umfasst natürlich max. 56 Zeilen. Kann ja sein, dass alle 56 ausgefüllt sind und auch alle verschieden!

    Und da kommt das gemeine: Das Suchkriterium in der SVERWEIS Formel wird beim Kopieren durch Runter-Ziehen NICHT weitergezählt. Man muss für beide Spalten und für alle 56 Zeilen die Suchzahl von Hand eingeben,(ODER noch mal tricksen, das geht auch, aber das lass ich jetzt mal weg...)..

    In der Linken Spalte (A) der Ergebnisliste soll das Element stehen, also die vorkommende Zahl, in Spalte B daneben, die Anzahl, wie oft sie vorkommt!

    Beides wird anhand der Rangnummer (Spalte A Tabelle2!) gesucht, dann aber dort je Zeile für die Zahl(=Element) Spalte D, und für die Anzahl Spalte E ausgelesen (s.o.)...

    Für die Elemente lautet die Suchformel
    für Rang 1:

    =WENN(ISTFEHLER(SVERWEIS(1;Tabelle2!$A$1:$D$56;4;FALSCH));"";SVERWEIS(1;Tabelle2!$A$1:$D$56;4;FALSCH))

    Für Rang 2:

    =WENN(ISTFEHLER(SVERWEIS(2;Tabelle2!$A$1:$D$56;4;FALSCH));"";SVERWEIS(2;Tabelle2!$A$1:$D$56;4;FALSCH))

    u.s.w... Die Unterschiede (die man von Hand ändern muss!) sind rot gekennzeichnet...

    Für die Anzahl sieht es fast genau so aus:

    Für Rang 1:

    =WENN(ISTFEHLER(SVERWEIS(1;Tabelle2!$A$1:$E$56;5;FALSCH));"";SVERWEIS(1;Tabelle2!$A$1:$E$56;5;FALSCH))

    Für Rang 2:

    =WENN(ISTFEHLER(SVERWEIS(2;Tabelle2!$A$1:$E$56;5;FALSCH));"";SVERWEIS(2;Tabelle2!$A$1:$E$56;5;FALSCH))

    Der Einzige Unterschied zum Rang 1- Element ist die Auslesespalte in Tabelle 2 (siehe blaue Kennzeichnung!)...

    Tja, und das war's!

    So alle 56 Zeilen auf Tabelle 1 eingerichtet, und das Ergebnis ist perfekt.

    8-)

    Rabe


    Ich schick Euch beiden die Beispieltabelle heut Abend mal als Mail!

    Ausgangsdaten:

      3 8 56 3
      5 66 89 40
      789 34 3 88
      78 23 22 3
      333 444 333 555

    Ergebnis:

      Element..Anzahl
      3..4
      5..1
      8..1
      22..1
      23..1
      34..1
      40..1
      56..1
      66..1
      78..1
      88..1
      89..1
      333..2
      444..1
      555..1
      789..1

    Computer setzen logisches Denken fort!
    Unlogisches auch....

  • danke :danke2:

    hast mir wirklich sehr geholfen

    ich warte schon auf deine beispieldatei.

    achja, da hätte ich noch eine frage: kennst du dich auch mit vb bzw. vb.net aus ???
    wenn ja, könntest du mir das ganze auch für vb bzw. vb.net erklären ???

    wäre wirklich der hammer.

    danke bereits im voraus. :hut:

  • Zitat von insider161191


    ich warte schon auf deine beispieldatei.

    achja, da hätte ich noch eine frage: kennst du dich auch mit vb bzw. vb.net aus ???
    wenn ja, könntest du mir das ganze auch für vb bzw. vb.net erklären ???

    Is gestern Besuch dazwischen gekommen...
    Nu hat's aber geklappt.
    ( Tunarus: zu Dir leider nicht, weil keine Mail im Profil verfügbar, und die Button-Funktion funzt an diesem Rechner leider nicht!)

    vb is leider nicht so meine Welt

    Rabe

    Computer setzen logisches Denken fort!
    Unlogisches auch....

  • @ravernheart

    Zitat von Tunarus

    dann schicke mir mal deine Datei an post (at) tunarus.de.

  • hi

    jetzt würde ich das ganze gerne noch ein wenig erweitern.

    in spalte a steht steht ein typ: F1, F2, T1, T2
    in spalte e, f, g, h stehen FE-B, FE-H, FL-B, FL-H


    z.b:

    jetzt soll es beim ergebnis so geschehen, dass wenn in spalte a F1 steht, dann sollen alle werte aus e, f, g, h 2x gerechnet werden.

    wenn in spalte a F2 steht, dann solle die werte aus spalte e, f 2x gerechnet werden und die werte aus spalte g, h 4x gerechnet werden.

    wenn in spalte a T1 steht, dann solle die werte aus spalte e, f 2x gerechnet werden, die werte aus spalte g 3x und die werte auf f 2x gerechnet werden.

    wenn in spalte a T2 steht, dann solle die werte aus spalte e, f 2x gerechnet werden, die werte aus spalte g 6x und die werte auf f 4x gerechnet werden.


    kannst du mir dabei weiterhelfen ???

    danke bereits im voraus.

  • Ich verstehe nicht ganz, was da Name, was Spalte und was Wert ist, aber allgemein gesprochen:

    So was machst Du ganz einfach, indem Du die Berechnungsformel als gestaffelte WENN-Funktion machst. (Das geht bis zu 7 Verschachtelungen, allerdings wird das sehr monströs - ab 5 macht man besser ne SVSERWEIS - Tabelle....).

    Bei nur 3 oder 4 Möglichkeiten ist das mit WENN aber gut machbar!

    Angenommen, A1 könne nur F1 oder F2 sein.

    Ist A1="F1" wäre die Formel:
    =2*(E1+F1+G1+H1)

    Ist A1="F2" wäre die Formel:
    =2*(E1+F1)+4*(G1+H1)

    Dies würde man so verbinden:

    =WENN(A1="F1";2*(E1+F1+G1+H1);2*(E1+F1)+4*(G1+H1))
    (Übersetzt: Wenn A1 ="F1", rechne 2*(...), sonst rechne 2*(..)+4*(...))

    Das Schema ist also:
    WENN(Bedingung;Rechnung1;Rechnung2), also
    WENN(Bedingung;dann;sonst)

    Für eine weitere Bedingung wird das "sonst" durch eine weitere, komplette Wenn-Formel ersetzt, und das bisherige "sonst" rückt an das Ende:

    WENN(Bedingung;dann;WENN(Bedingung;dann;sonst))

    Wenn A1 ="T1" ist soll die Rechnung lauten:
    =2*(E1+F1)+3*G1+2*F1

    (z.B., bei dem, was Du geschrieben hast, kommt 2x f vor, aber egal, Prinzip bleibt ja gleich!) Die Formel wäre dann:

    =WENN(A1="F1";2*(E1+F1+G1+H1);WENN(A1="T1";=2*(E1+F1)+3*G1+2*F1;2*(E1+F1)+4*(G1+H1)))

    Und für noch eine 4. Bedingung, würde man NOCH MAL das "sonst" durch NOCH eine Wenn-Formel ersetzen:

    WENN(Bedingung;dann;WENN(Bedingung;dann;WENN(Bedingung;dann;sonst)))

    Also für A1 ="T2" mit der Formel =2*(E1+F1)+6*G1+4*F1 käme hinzu:

    =WENN(A1="F1";2*(E1+F1+G1+H1);WENN(A1="T1";=2*(E1+F1)+3*G1+2*F1;WENN(A1="T2";2*(E1+F1)+6*G1+4*F1;2*(E1+F1)+4*(G1+H1))))

    Die Letzte, die "sonst"-Bedingung, ist immer die, die übrig bleibt, im o.a. beispiel für A1 ="F2".
    Man KANN auch, falls das vorkommen kann, , als "sonst" den "0-Fall" einsetzen, also wenn A1 leer ist, soll die Zelle leer sein (macht man das nicht, würde er ja die "sonst" - Berechnung durchführen!)

    Das sähe dann so aus:

    =WENN(A1="F1";2*(E1+F1+G1+H1);WENN(A1="F2";2*(E1+F1)+4*(G1+H1);WENN(A1="T1";=2*(E1+F1)+3*G1+2*F1;WENN(A1="T2";2*(E1+F1)+6*G1+4*F1;""))))

    ("" heißt: "lass leer")

    Beachte, dass für jede eingefügte Wenn-Formel am Ende 1 Klammer hinzu kommt! Da kann man sich leicht verzählen, besonders, wenn die Teilformel selber mit einer Klammer endet! Es muss für jedes "WENN" eine Klammer sein!

    Rabe

    Computer setzen logisches Denken fort!
    Unlogisches auch....

  • :morse: :morse: :morse: :morse: :morse:

    das ganze soll etwas mit den bereits oben stehenden fragen zu tun haben.
    du hast mir doch eine beispieldatei geschickt.
    könntest du das nicht auf diese beispieldatei übertragen und mir das ganze noch einmal :hut:

  • Ääääähhhmmm :s24: Insidiiiiii :s24:

    Zitat von insider161191

    ich bin auf der suche nach einer speziellen formel für excel 2003.

    ich habe eine tabelle mit 4 spalten und ....


    Zitat von insider161191

    hi

    jetzt würde ich das ganze gerne noch ein wenig erweitern.

    in spalte a steht steht ein typ: F1, F2, T1, T2
    in spalte e, f, g, h stehen
    FE-B, FE-H, FL-B, FL-H

    Sehe ich da einen leichten Widerspruch? 4...5... :-?

    Was is mit Spalten B bis D??


    Außerdem:

    Zitat

    wenn in spalte a T2 steht, dann solle die werte aus spalte e, f 2x gerechnet werden, die werte aus spalte g 6x und die werte auf f 4x gerechnet werden.

    Wieso wird erst f 2x und dann noch mal f 4x gerechnet, wieso nicht gleich f 6x??

    Ich glaub ja, dass DU genau vor Augen hast, worauf das hinaus läuft, aber sorry - ich kann dir da nicht folgen!!

    Entweder, Du stellst die Fragen, also die Aufgabenstellung so, dass sie genau auf meine Beispieltabelle passt,

    Oder Du erläuterst Deine Tabelle erst noch mal näher.
    :)

    Rabe

    Computer setzen logisches Denken fort!
    Unlogisches auch....

  • Zitat von insider161191

    ich schicke dir die datei per mail....

    kannst du mir da weiterhelfen ???

    Fürchte, Du musst leider ne Weile drauf warten - Urlaub lacht...

    Rabe

    Computer setzen logisches Denken fort!
    Unlogisches auch....

Jetzt mitmachen!

Sie haben noch kein Benutzerkonto auf unserer Seite? Registrieren Sie sich kostenlos und nehmen Sie an unserer Community teil!