Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.09.2025 18:34:36
Welches Datum gibt mein M-Code aus?
26.09.2025 10:29:46
Wert aus einer Zelle bei jeder Eingabe in neue Zelle
26.09.2025 08:51:00
Teilinhalt einer Zelle vergleichen
25.09.2025 23:04:12
Einen von drei möglichen Einträgen in einer Zeile suchen
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
988to992
988to992
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

x-tes Vorkommen

Forumthread: x-tes Vorkommen

x-tes Vorkommen
01.07.2008 22:59:00
MartinBorn
Hallo und guten Abend,
kann jemand von Euch mir helfen?
Es gibt doch sicher eine Formel mit Index und Vergleich,
mit der ich die Zeilenposition feststellen kann.
Das sieht so aus:
Userbild
Hier die Tabelle:
https://www.herber.de/bbs/user/53512.xls
Herzlichen Dank.
born

Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Ich versteh kein Wort...
01.07.2008 23:11:00
{Boris}
Hi,
...was soll genau wie berechnet werden?
Grüße Boris

Ich, erst auch nicht .
01.07.2008 23:47:00
Matthias
Hallo Boris
Ich hab mir das Sheet auch angesehen und erst Bahnhof verstanden ;o)
Jetzt, so denke ich habe ich den Hintergrund nachvollzogen und bin zu diesem Ergebnis gekommen.
Userbild
Allerdings bin ich noch nicht so richtig fit in MatrixFormeln und hab leider noch keine Lösung erarbeitet.
Gruß Matthias

Anzeige
AW: Zeilennummern der Vorkommen
02.07.2008 02:08:00
Erich
Hallo zusammen,
das wäre wohl eine Lösung (nach rechts und unten kopieren):
 ABCDEFGHIJ
11  1234567
25 13581217  
31 2       
46 37915    
51 41820     
67 52101114   
73 64      
81 76      
93 8131619    
105         
115         
121         
138         
145         
153         
168         
171         
184         
198         
204         

Formeln der Tabelle
ZelleFormel
D2{=WENN(SPALTE(A2)>ZÄHLENWENN($A$2:$A$20;$C2); ""; KKLEINSTE(WENN($A$2:$A$20=$C2;ZEILE($A$2:$A$20)); SPALTE(A2)))}
E2{=WENN(SPALTE(B2)>ZÄHLENWENN($A$2:$A$20;$C2); ""; KKLEINSTE(WENN($A$2:$A$20=$C2;ZEILE($A$2:$A$20)); SPALTE(B2)))}
D3{=WENN(SPALTE(A3)>ZÄHLENWENN($A$2:$A$20;$C3); ""; KKLEINSTE(WENN($A$2:$A$20=$C3;ZEILE($A$2:$A$20)); SPALTE(A3)))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Zeilennummern der Vorkommen
02.07.2008 12:20:32
MartinBorn
Hallo Erich,
vielen Dank für die Formel. Ich bin noch damit beschäftigt, Deine großartige
VBA-Lösung mit dem Array zu begreifen, die Du mir gestern gegeben hast.
Die Matrix-Formel aus diesem Thread sollte ich wahrscheinlich verstehen,
aber ich tu es nicht. Wäre es zu viel verlangt, wenn Du mir erklären würdest,
wie diese Formel zu interpretieren ist. Ich verstehe nicht die Sache mit Spalte
und zählenwenn und kkleinste sieht auch auf den ersten Blick logisch aus, ist
aber nicht so einfach zu durchschauen.
Reinhard hat das Ganze, weiter unten in eine Nicht-Matrix-Formel mit Vergleich
gepackt, was natürlich die noch elegantere und schnellere Lösung ist, aber
ich würde Deine Formel wirklcih gerne verstehen.
Also, vielen Dank nochmals,
Born

Anzeige
AW: Zeilennummern der Vorkommen
02.07.2008 19:49:20
Erich
Hallo Martin,
hier hab ich mal die Formel - nachdem ich SPALTE(A2) durch das (gleiche) D$1 ersetzt habe -
in drei Schritte aufgeteilt:
https://www.herber.de/bbs/user/53542.xls
Unter "Eins" steht (als gemeinsame Matrixformel im grünen Bereich L2:L20) das Matrix-WENN,
in dem die Zeilennummern der Zeilen, in denen in Sp. A C2 steht, übernommen werden.
Bei allen anderen Zeilen kommt da FALSCH raus - weil der Sonst-Zweig vom WENN fehlt.
Unter "Zwei" wird mit KKLEINSTE der drittkleinste (wg. F2=3) Wert des grünen Bereichs ausgegeben.
Das ist die 8, denn FALSCH ist größer als alle Zahlen.
Unter Drei4 bis Drei7 steht nur, ob ein Wert oder ein leerer String ausgegeben werden soll.
Dazu wird gezählt, wie oft der Wert von C2 in Spalte A steht.
Diese äußere WENN-Klammer hat nur den Sinn, Fehlerwerte zu vermieden.
Alles klar?
Reinhard hat dir ja schon den Tipp "Zauberbuch" gegeben. Genauso spannend (schau mal die Autoren...) ist
http://www.excelformeln.de/formeln.html
Noch ein Tipp:
Hast du schon mal im Excel-Menü Extras - Formelüberwachung - Formlauswertung ausprobert?
(Vorher den Cursor in eine Zelle mit der Formel stellen.)
Da kannst du schrittweise sehen, was bei der Formelberechnung so alles passiert.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
OK, so wird es sein...
02.07.2008 08:00:00
{Boris}
Hi Matthias,
...und Erich hat die passende Lösung geliefert :-)
Grüße Boris

AW: x-tes Vorkommen
02.07.2008 07:52:00
Reinhard
Hi Born,

Tabellenblatt: C:\DOKUME~1\ICHALS~1\LOKALE~1\Temp\[53512.xls]!Tabelle2
| A | B | C |  D  |  E  |  F  |  G  |  H  |  I  |  J  |
1 | 1 |   |   |  1  |  2  |  3  |  4  |  5  |  6  |  7  |
2 | 5 |   | 1 |  1  |  3  |  5  |  8  | 12  | 17  | #NV |
3 | 1 |   | 2 | #NV | #NV | #NV | #NV | #NV | #NV | #NV |
4 | 6 |   | 3 |  7  |  9  | 15  | #NV | #NV | #NV | #NV |
5 | 1 |   | 4 | 18  | 20  | #NV | #NV | #NV | #NV | #NV |
6 | 7 |   | 5 |  2  | 10  | 11  | 14  | #NV | #NV | #NV |
7 | 3 |   | 6 |  4  | #NV | #NV | #NV | #NV | #NV | #NV |
8 | 1 |   | 7 |  6  | #NV | #NV | #NV | #NV | #NV | #NV |
9 | 3 |   | 8 | 13  | 16  | 19  | #NV | #NV | #NV | #NV |
10 | 5 |   |   |     |     |     |     |     |     |     |
Benutzte Formeln:
D2 :  =VERGLEICH($C2;$A$1:$A$21;0)
in D nach unten kopieren
E2 :  =VERGLEICH($C2;INDIREKT("$A$"&D2+1&":$A$21");0)+D2
nach unten und rechts kopieren.
A1:J10
haben das Zahlenformat: Standard
Bedingte Formatierung(en):
D2 : 1.te Bedingung: Formel ist =ISTFEHLER(D2)
Bei erfüllter Bedingung wird die Zellenschrift in D2 mit der Schriftfarbe 2 eingefärbt
Dies bed. Formatierung in D2:J9 eintragen.


Gruß
Reinhard

Anzeige
AW: x-tes Vorkommen
02.07.2008 12:25:00
MartinBorn
Hallo Reinhard,
das ist wohl nicht zu übertreffen. Ich habe mich schon bei Erich für seine Matrix-Formel bedankt
und auf Deine Lösung hingewiesen. Ich wäre da nie drauf gekommen, obwohl ich alle Einzel-
teile kenne. Und wenn man Deins mal gelesen hat, kapiert man wie einfach sowas geht.
Merci totale,
Born

Anzeige
AW: x-tes Vorkommen
02.07.2008 19:10:58
Reinhard
Hi Martin,
mir gehts wie Matthias, mit den Matrixformeln hapert es noch gewaltig :-)
Matrixformeln sind schon chic wenn man sie beherrscht, ohne sie kann man ggfs, k.A., auch alles mit normalen Excelformeln lösen, aber dann meist nur mit Hilfsspalten usw. wenn überhaupt.
Wenn du dich dafür interessierst gibt es für dich nur eins, hol dir das Zauberbuch bei www.excelformeln.de.
Die paarundzwanzig Eiuronen sind sehr gut angelegt, viel besser als ein Buch von manchen Helden zu kaufen.
Und schmöker mal auf der Seite, pick dir beliebig paar Formeln raus, nihct grad die längsten *gg* und versuche die zu analysieren.
Letztlich benutzen "die" da (sorry Boris) meist nur 10-15 bekannte Excelfunktionen die ich einzeln in Excel auch benutze.
Aber zusammengestzt zu einem längeren Term und die eckigen Klammern drum habe ich leider keinen Plan mehr was denn nun da raus kommt bei der Formel :-)
Gruß
Reinhard

Anzeige
;-))) oT
02.07.2008 23:47:40
{Boris}
Grüße Boris
;
Anzeige
Anzeige

Infobox / Tutorial

x-tes Vorkommen in Excel: So findest du die n-te Position

Schritt-für-Schritt-Anleitung

Um das n-te Auftreten eines Wertes in Excel zu finden, kannst du eine Kombination aus den Funktionen WENN, KKLEINSTE und ZEILE verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

  1. Daten vorbereiten: Stelle sicher, dass deine Daten in einer Spalte (z.B. Spalte A) stehen.
  2. Eingabewert festlegen: Lege in einer Zelle (z.B. C2) den Wert fest, dessen n-tes Auftreten du finden möchtest.
  3. Formel eingeben: In der Zelle, in der das n-te Auftreten angezeigt werden soll (z.B. D2), gib folgende Formel ein: 
    =WENN(SPALTE(A2)>ZÄHLENWENN($A$2:$A$20;C2); ""; KKLEINSTE(WENN($A$2:$A$20=C2;ZEILE($A$2:$A$20)); SPALTE(A2)))
  4. Matrixformel aktivieren: Schließe die Eingabe mit STRG + SHIFT + ENTER ab, um die Formel als Matrixformel zu aktivieren.
  5. Nach unten und rechts kopieren: Ziehe die Formel nach unten und nach rechts, um weitere n-te Vorkommen zu finden.

Häufige Fehler und Lösungen

  • Fehler beim Kopieren der Formel: Achte darauf, dass du die Formel als Matrixformel eingibst. Viele Nutzer vergessen, STRG + SHIFT + ENTER zu verwenden.
  • Leere Zellen oder Fehlerwerte: Wenn du leere Zellen erhältst, überprüfe, ob der Suchwert in der angegebenen Range vorhanden ist. Möglicherweise gibt es nicht so viele Vorkommen des gesuchten Wertes.
  • Falsche Zellbezüge: Stelle sicher, dass die Zellbezüge in deiner Formel korrekt sind. Ein häufiger Fehler ist das falsche Angeben der Bereiche.

Alternative Methoden

  1. Verwendung von VERGLEICH: Du kannst die Funktion VERGLEICH nutzen, um den Index des n-ten Vorkommens zu finden.

    =VERGLEICH(C2;A:A;0)

    Diese Methode eignet sich, wenn du nur das erste Vorkommen benötigst.

  2. VBA-Makros: Wenn du häufig mit großen Datenmengen arbeitest, kann ein VBA-Makro effizienter sein. Hier ein einfaches Beispiel:

    Function NthOccurrence(rng As Range, value As Variant, n As Long) As Long
   Dim count As Long
   Dim cell As Range
   count = 0
   For Each cell In rng
       If cell.Value = value Then
           count = count + 1
           If count = n Then
               NthOccurrence = cell.Row
               Exit Function
           End If
       End If
   Next cell
   NthOccurrence = -1 ' Not found
End Function

Praktische Beispiele

  • Beispiel 1: Wenn deine Spalte A die Werte 1, 2, 3, 1, 2, 1 enthält und du das 2. Auftreten von 1 suchst, gib 1 in C2 ein und wende die oben genannte Formel an.
  • Beispiel 2: Verwende die bereitgestellte VBA-Funktion, um die Zeilennummer des 3. Auftretens von 2 in einem Bereich zu finden: 
    MsgBox NthOccurrence(Sheet1.Range("A1:A10"), 2, 3)

Tipps für Profis

  • Matrixformeln sind mächtig, aber oft schwer zu verstehen. Nutze die Formelüberwachung in Excel (Extras > Formelüberwachung), um die Berechnungsschritte zu verfolgen.
  • Hilfsspalten: Bei komplexen Datenanalysen kann es hilfreich sein, Hilfsspalten zu verwenden, um Zwischenergebnisse zu speichern.
  • Fehler vermeiden: Nutze WENNFEHLER, um Fehlerwerte in der Ausgabe zu vermeiden, z.B.: 
    =WENNFEHLER(DEINE_FORMEL; "Nicht gefunden")

FAQ: Häufige Fragen

1. Wie finde ich das n-te Auftreten eines Wertes in einer langen Liste? Du kannst die oben beschriebene Methode mit der Matrixformel verwenden, um das n-te Auftreten zu ermitteln. Achte darauf, dass du die Formel korrekt eingibst.

2. Was ist der Unterschied zwischen Matrixformeln und regulären Formeln in Excel? Matrixformeln können mehrere Werte gleichzeitig verarbeiten und sind in der Lage, komplexe Berechnungen zu erledigen, während reguläre Formeln in der Regel nur einen einzelnen Wert zurückgeben.

3. Wie kann ich die Leistung meiner Excel-Datei verbessern, wenn ich mit vielen Formeln arbeite? Verwende VBA für komplexe Berechnungen oder reduziere die Anzahl der Berechnungen, indem du Hilfsspalten einsetzt und unnötige Formeln vermeidest.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige