Ich möchte einen Bereich in Excel erstellen

Question

Ich habe folgende Zellen:

    Number  Band    Color
1   10    A          A1
2   20    A          A1
3   30    A          A1
4   40    B          A1
5   50    B          A2
6   60    C          B1
7   70    C          B1
8   80    A          B1
9   90    A          A1
10  100   B          A1

Ich möchte einen „Bereich“/eine Zusammenfassung der Daten wie folgt erstellen:

Start  End  Band    Color
10     30   A        A1
40     40   B        A1
50     50   B        A2
60     70   C        B1

Ich kann das manuell machen, aber die Daten bestehen aus Tausenden von Zellen, daher erscheint es mühsam. Gibt es integrierte Funktionen, die das erleichtern?

Answer 1

Dies können wir mit zwei Hilfsspalten tun. Ich habe die folgenden Formeln in D2 bzw. E2 eingefügt:

=IF(OR(B2<>B1,C2<>C1),A2,"")
=IF(OR(B2<>B3,C2<>C3),A2,"")

Dann wird die Länge des Datensatzes nach unten kopiert.

Dadurch erhalte ich eine Liste, auf der ich mit der Formel SMALL() Start- und Zielwerte ermitteln kann.

In G2 habe ich eingegeben:

=IFERROR(SMALL(D:D,ROW(1:1)),"")

Dann habe ich eine Spalte nach unten kopiert, bis ich leere Zellen hatte.

Dann verwenden wir diese Zahlen in einem INDEX/MATCH, um die anderen Informationen zurückzugeben.

In I2 setze ich ein:

=IF($G2<>"",INDEX(B:B,MATCH($G2,$A:$A,0)),"")

Habe eine Spalte nach unten kopiert, bis ich Leerzeichen hatte.

Das Gleiche hätte ich auch mit SVERWEIS machen können:

=VLOOKUP($G2,$A:$C,COLUMN(B:B),FALSE)

Answer 2

Könnten Sie Ihre Frage etwas ausführlicher erklären, sowohl das Muster als auch die Tools, die Sie verwenden möchten? Ich gehe davon aus, dass Sie, wenn Sie „Funktion“ sagen, Tabellenformeln und keinen VBA-Code möchten. Suchen Sie nur nach der ersten Instanz jedes Band-/Farbpaars oder suchen Sie nach aufeinanderfolgenden übereinstimmenden Bändern und Farbsätzen ganz unten? Wenn Letzteres der Fall ist, würde Ihre Tabelle tatsächlich drei weitere Zeilen enthalten, wie folgt:

Start  End  Band    Color
10     30   A        A1
40     40   B        A1
50     50   B        A2
60     70   C        B1
80     80   A        B1
90     90   A        A1
100    100  B        A1

Wenn Sie diese längere Tabelle möchten, können Sie zwei neue Zeilen erstellen (entweder in diesem Blatt oder in einem neuen) und, vorausgesetzt, Sie haben „Number“ in A1, den folgenden Code verwenden:

Zelle D2=IF(NOT(AND($B1=$B2,$C1=$C2)),IFERROR(MAX(INDIRECT("D1:D"&ROW(D2)-1))+1,1),0)

Zelle E2=IF(NOT(AND($B2=$B3,$C2=$C3)),IFERROR(MAX(INDIRECT("E1:E"&ROW(E2)-1))+1,1),0)

Erweitern Sie den Code über die gesamte Spaltenlänge Ihrer Tabelle. Das Ergebnis sieht wie folgt aus.

Number Band Color Start End
10     A    A1    1     0
20     A    A1    0     0
30     A    A1    0     1
40     B    A1    2     2
50     B    A2    3     3
60     C    B1    4     0
70     C    B1    0     4
80     A    B1    5     5
90     A    A1    6     6
100    B    A1    7     7

Suchen Sie von hier aus einfach die Nummer jedes Satzes (1, 2, 3, ...) und suchen Sie die Zeilennummer, um die gewünschten Informationen zu erhalten. Wenn Sie die Start-/Endspalten an den Anfang setzen, können Sie sie vlookuphierfür verwenden.

Wenn Sie nur das erste Vorkommen jedes Paares wollen (aber warum wird dann Zeile 8 ausgelassen?), brauchen Sie etwas mehr Logik. Das kann eine zusätzliche Spalte erfordern, aber die zusätzliche Logik wird ziemlich einfach sein.