Excel ZÄHLENWENN mit relativen Bezug auf Zelle oberhalb der aktuellen
Hallo,
Ich habe eine Tabelle, in der ich die erste Spalte auf Duplikate überprüfen möchte.
Dazu sorteiere ich diese Spalte (alles Zahlen) in aufsteigender Reihenfolge und möchte nun die "Zählenwenn"-Funktion verwenden, um die Duplikate zu zählen.
Da die Spalte ja sortiert ist kann ich sicher sein, dass gleiche Zahlen immer übereinander liegen, sodass ich in der "Zählenwenn"-Funktion folgendes haben möchte:
=ZÄHLENWENN(Bereich;Inhalt=Inhalt der Zelle oberhalb)
Die Zählenwennfunktion durchläuft ja alle Zellen im Bereich, weshalb (theoretisch) die Funktion "Adresse()" mit "Zeile()" und "Spalte()" als Parameter die aktuelle Zelle referenzieren/sich darauf beziehen.
Das würde bedeuten, dass ich in der "Zählenwenn"-Funktion als zweiten Parameter eigentlich nur folgendes eingeben müsste:
Adresse(Zeile()-1;Spalte())
Funktioniert nicht.
In einigen Beispielen habe ich gesehen, dass Bezüge auf andere Zellen in der "Zählenwenn"-Funktion mit einem "&" beginnen, also habe ich folgendes versucht:
=ZÄHLENWENN(A2:A3000;&ADRESSE(ZEILE()-1;SPALTE()))
Wieder kein Erfolg.
Deshalb die Frage: Wie kann ich in der "Zählenwenn"-Funktion in Excel mich auf eine Zelle, relativ zur aktuellen beziehen? Das hier genannte Beispiel wird sicher auch anders zu lösen gehen, aber den genannten relativen Bezug (also relativ zur Aktuellen Zelle) könnte man in der Zählenwenn schon öfters brauchen. Danke und lg, Springrbua
5 Antworten
So eine ähnliche Frage gab es gestern schon. Vielleicht helfen dir die antworten dort weiter: Excel - Zellen zählen
Der dort beschriebene Weg erspart dir das Sortieren.
Mir fällt momentan nichts ein. Wenn mir etwas einfällt, gebe ich bescheid.
Der Bezug, den man als 2ten Parameter angeben kann, wird sich vermutlich immer nur auf die Zelle der Formel selbst beziehen, nicht aber auf die Zelle, welche aktuell mit "Zählenwenn" überprüft wird... Vermute ich zumindest. Das würde dann den von mir gesuchten Lösungsweg unmöglich machen...
Eine VBA-Prozedur könnte so ähnlich wie folgende aussehen:
Sub ANZAHL_OHNE_DUPLIKATE()
' Test für gleichnamige Funktion
Dim oBereich As Range
Dim oZiel As Range
Set oBereich = Range("A16:A22")
Set oZiel = oBereich.Offset(0, 10) ' Zielbereich sollte unverwendet sein
oZiel.Clear
oBereich.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=oZiel, Unique:=True
Debug.Print Application.WorksheetFunction.CountA(oZiel)
oZiel.Clear
End SubIch habe noch eine Möglichkeit gefunden.
- Kopiere den Bereich mit den Duplikaten in einen freien Hilfsbereicht.
- (Excel 2010) Markiere den kopierten Bereich
- Daten → Duplikate entfernen
- Anschließend mit Anzahl2 zählen
Danke für die Vorschläge und Infos. Sieht so aus, als ob es tatsächlich mit der ZÄHLENWENN-Funktion nicht möglich ist. Ist wohl auch nicht möglich aus der die aktuell geprüfte Zelle herauszu bekommen. Danke trotzdem!
Ich würde es so machen http://www.excelformeln.de/formeln.html?welcher=79
Sortieren von Spalte A ist dabei nicht nötig.
Im übrigen, & wird meines Wissens nur für Verkettungen verwendet. Bei Bezügen findet man dagegen häufig ein $, damit sich der Bezug (Zelle, Zeile oder Spalte) beim kopieren der Formel nicht verschiebt.
Auch eine Möglichkeit. Danke dafür. Allerdings suche ich konkret eben nach einem Weg in der Zählenwenn-Funktion die mich auf die aktuell geprüfte Zelle zu beziehen.
Zählenwenn laüft ja alle Zellen im BEreich durch und führt die Inhaltsprüfung darauf durch. Ich möchte mich nun eben auf diese Zelle, die eben in diesem Moment geprüft wird beziehen.
Mein konkretes Problem der Frage habe ich bereits mit anderen Lösungswegen gelöst, allerdings suche ich eben nach einer Möglichkeit es so zu lösen (rein aus Neugier ob das möglich ist^^)
Lg
Alternative falls auch Texte vorkommen können:
=SUMME(WENN(HÄUFIGKEIT(VERGLEICH(WENN(ZÄHLENWENN(A1:A1000;A1:A1000)>1;A1:A1000;"");WENN(ZÄHLENWENN(A1:A1000;A1:A1000)>1;A1:A1000;"");0);VERGLEICH(WENN(ZÄHLENWENN(A1:A1000;A1:A1000)>1;A1:A1000;"");WENN(ZÄHLENWENN(A1:A1000;A1:A1000)>1;A1:A1000;"");0))>0;1))-WENN(SUMME(ISTLEER(A1:A1000)*1)>0;1;0)
sorry, natürlich als Matrixformel abschliessen:
{=SUMME(WENN(HÄUFIGKEIT(VERGLEICH(WENN(ZÄHLENWENN(A1:A1000;A1:A1000)>1;A1:A1000;"");WENN(ZÄHLENWENN(A1:A1000;A1:A1000)>1;A1:A1000;"");0);VERGLEICH(WENN(ZÄHLENWENN(A1:A1000;A1:A1000)>1;A1:A1000;"");WENN(ZÄHLENWENN(A1:A1000;A1:A1000)>1;A1:A1000;"");0))>0;1))-WENN(SUMME(ISTLEER(A1:A1000)*1)>0;1;0)}
Das nenne ich mal eine Formel^^ Danke! Hast dir ja ganz schön Mühe gegeben! Kann man die Hilfreichste Antwort rückgängig machen? Ansonsten krigst du halt nur nen Daumen^^
Lg
Es geht mir hier nicht darum die Auszeichnung "hilfreichste Antwort" zu bekommen, sondern darum die hilfreichste Antwort zu geben und um die Herausforderung, dass wie auch immer geartete Problem zu lösen. Und deines war, nachdem ich herausgefunden habe um was es geht, eine Herausforderung.
Kleiner Tip noch: Wie du siehst hat meine Formel mit deiner eigentlichen Fragestllung nicht mehr viel zu tun. Die Formel löst was du eigentlich wissen willst. Bitte gib das eigentliche Problem immer mit an. Oftmals ist man mit seinem Lösungsweg schon auf dem Holzweg. Das selbe passierte hier. Jeder stürzte sich auf die eigentliche Fragestellung (was auch verständlich ist). Die Lösung des eigentlichen Problems war aber mit der Fragestellung gar nicht lösbar. Oftmals gibt es dann (gerade in Excel) eine Alternative, die mit dem ersten gedachten Lösungsweg gar nichts zu tun hat.
In diesem Sinne eine schöne Restwoche.
Die Frage wurde bewusst so gestellt, weil ich ja eben eigentlich die Lösung mit dieser Formel wollte. Es hat sich ja dann, dank dir und anderen hier herausgestelt, das dies schlichtweg nicht möglich ist und damit ist die Frage ja dann auch beantwortet.
Die Lösung für das Problem, welches ich durch die Formel lösen wollte war mir ja schließlich auch klar (mit einer Hilfszeile kein Problem^^). Nur eben Interessehalber, wollte ich wissen, ob das geht und wenn ja, wie^^
Ich glaub ich verstehe dich nicht ganz: Wenn du in Zelle H2 folgende Formel schreibst: =ZÄHLENWENN($A:$A;H1) und diese dann nach unten ziehst oder nach Rechts dann passt sich doch H1 auf Grund des relativen Bezuges sowieso an.
Warum willst du da mit ADRESSE arbeiten?
Falls das mit ADRESSE funktionieren würde, wie ich geglaubt habe, könnte ich die Formel in einer Zelle haben. Bei deiner Version müsste ich das ganze nochmal Summieren um meine Lösung zu haben.
Worauf ich hinaus wollte:
Zählenwenn läuft jede Zelle im Bereich durch und überprüft diese auf den 2ten Parameter. Nun möchte ich, dass sich der 2te Parameter auf die aktuell überprüfte Zelle bezieht. Beispiel: Zelle A1-A3 sind gefüllt mit "1","2" und "1". Eine Zählenwenn Abfrage wie die folgende: ZÄhLENWENN(A1:A3; 1) Macht ja folgendes: 1. Nehme erste Zelle im Bereich: Aktuelle Zelle = A1. 2. Prüfe aktuelle Zelle auf Inahlt: A1=1, WAHR, Ergebnis = 1 3. Nehme nächste Zelle, Aktuelle Zelle = A2 4. Prüfe aktuelle Zelle auf Inahlt: A2=1, FALSCH, Ergebnis bleibt 1 5. Nehme nächste Zelle, Akteulle Zelle A3 6. Prüfe Zelle auf Inhalt: A3=1, WAHR, erhöhe Ergebnis um 1, Ergebnis = 2 7. Bereichende, Ergebnis = 2.
Nun möchte ich die von mir Fett gedruckte "Aktuelle Zelle" als Parameter verwenden können, bzw. einen Bezug relativ von dessen Position weg verwenden um somit eine Formel in einer Zelle auszudrücken, welche ansonsten eine ganze Spalte verwendet.
welches Ergebnis würdest du dann bei deiner Formel erwarten?
was du hier scheinbar möchtest ist eine Aufdröselung der Berechnung einer Formel. Die lässt sich aber nicht aufdröseln. Da hier ein Term quasi "am Stück" abegearbeitet wird. Woher weisst du genau in welchen Schritten der Algorithus hinter ZÄHLENWENN vor sich geht?
Wenn du {=ZÄHLENWENN(A1:A3;A1:A3} auswertest kommt ein Array der Form {2;1;2} raus. Aber auch das kannst du ja nicht in einer Zelle darstellen.
Du willst wissen wieviel Zahlen doppelt oder 3fach vorkommen?
Versuch mal:
{=SUMME(WENN(ZÄHLENWENN(A1:A3;A1:A3)>1;1;0))}
ACHTUNG!
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben und die Eingabe der Formel NICHT mit ENTER abschließen. Schliesse stattdessen die Formel mit STRG & SHIFT & ENTER (alle drei gleichzeitig) ab, das erzeugt die {} und macht die Formel zu einer Matrixformel.
Den Algorithmus selbst weiß ich nicht aber die Zählewenn wird wohl jede Zelle durchlaufen müssen um die "Wenn" Abfrage zu machen. Da ich beruflich mit Java zu tun habe, dafür aber nicht allzu viel mit Excel (das ganze brauchte ich nur für einen Vorher-Nachher-Vergleich bei der Arbeit um zu wissen ob das von mir Programmierte das tut was es soll^^) dachte ich dass man eben irgentwie auf "die Variable im inneren der Formel" zugreifen kann, wäre ja auch irgentwo sinnvoll.
Dass das mit "Adresse" nicht funktioniert kommt mir mittlerweile logisch vor, da sich diese Funktion ja auf die Zelle, in der sie steht bezieht und nicht auf den "Cursor" der Zählenwenn-Funktion. Aber wie gesagt kaum Erfahrung mit Excel und zu viel in Richtung Programmierung gedacht^^
Danke trotzdem (: Lg, Springrbua
Eigentlich wollte ich nur die gesammten Duplikate zählen. Deshalb die aufsteigende Sortierung. Somit bräuchte ich ja nur folgende Abfrage für jede Zelle durchführen:
Inhalt dieser Zelle=Inhalt nächster Zelle, Dann 1 sonst 0. Das ganze dann aufsummieren, was ja Zählenwenn macht. Nur eben kann ich mich nicht auf eine Zelle relativ zur aktuellen Beziehen.
Die Matrixformel funktioniert so leider nicht. Liefert ein anderes Ergebnis, als: 1. A1=A2 Vergleich, gespeichert in B1
2. A2=A3 Vergleich, gespeichert in B2
3. A3=A4 Vergleich, gespeichert in B3 (A4 ist leer daher immer Falsch)
4. ZÄHLENWENN(B1:B3;WAHR)
Was genau sollte die Formel machen und was ist der unterschied zwischen normaler und Matrixformel?
Eigentlich wollte ich nur die gesammten Duplikate zählen.
Genau das tut die Matrixformel! Und dabei ist es völlig egal ob die Liste sortiert ist oder nicht.
Mit einer Matrixformel kannst du dir die Hilfsspalte sparen. Weil die Hilfsspalte quasi in einer Zelle abgebildet wird.
Dein Ergebnis ist ja auch nicht 1. Sondern 2
{1;2;1} die Liste enthält 2 Einsen.
wenn du die Duplikate aber einzeln gezählt haben möchtest.
{1;2;3;4;5;1;2;1} Wenn hier das Ergebnis 2 sein soll, da es insgesamt 2 Zahlen gibt die mehrfach vorkommen, die Eins und die Zwei, dann brauchst du folgende Formel:
{=SUMME(WENN(HÄUFIGKEIT(WENN(ZÄHLENWENN(A1:A9;A1:A9)>1;A1:A9;"");WENN(ZÄHLENWENN(A1:A9;A1:A9)>1;A1:A9;""))>0;1))}
Achtung, Funktioniert nur für Zahlen. Für Texte muss man sie noch etwas umbauen
Danke für die Aufklärung^^
Gibt wohl ne Menge Möglichkeiten das von mir gewünschte zu erreichen^^
Zuerst musst du eine logische Abfrage machen.
=A2=A1 Das liefert einen Wahrheitswert WAHR oder FALSCH. Wahr wenn a1 = A2, Falsch wenn nicht gleich.
An diesen Wahrheitswerten kannst du dann WENN-Abfragen machen.
Das ist mir schon klar, das Problem ist aber, dass ich bei so einer Abfrage die "=A1=A2" Formel 3000 Zeilen runterziehen muss.
Ist in meinem Fall jetzt nicht das große Problem, aber so eine "dynamische" Formel, wie die von mir beschriebenen (nicht funktionierende) Formel wäre schon etwas interessanter, auch für andere Probleme...
Gibt es diese Möglichkeit nicht?
3000 mal runterziehen ist ein Problem? NEIN
Angenommen: zu kopierende Formel ist in B1
Letzter Wert in A3000
dann folgendes:
in B3001 irgend etwas eingeben (aa oder bb)
in B1 stellen und STRG+C (kopieren)
STRG und Shift und Pfeil abwärts drücken
Shift weiterhalten und einmal Pfeil aufwärts drücken
STRG und V drücken (einfügen)
und schon steht die Formel aus B1 angepasst in Spalte B bis Zeile 3000
Gruß aus Berlin
Man kann auch doppelklick machen^^ Falls die Spalte daneben mit Inhalt gefüllt ist wird die Formel automatisch bis zum Ende des Inhaltes erweitert.
Wie aber in der Frage erläutert suche ich nach einer Lösung das Ganze in EINER Formel, und zwar in der Zählenwenn-Funktion, auszudrücken.
ISt vielleicht in meinem speziellen Fall nicht notwendig, aber es ist sicher ein Vorteil, wenn man in der Zählenwenn-Funktion Zellen referenzieren kann, die der aktuellen Zelle der Zählenwennfunktion entsprechen.
Deine Antwort auf die dortige Frage funktioniert wieder mit einer Hilfsspalte.
Das Ganze funktioniert zwar, allerdings suche ich eher nach einer Lösung, in der ich das ganze in der Zählenwenn-Funktion, wie in der Frage erkärt ausdrücken kann.
Aber das ist wahrscheinlich nicht möglich oder?