Я хочу создать ассортимент в Excel

Question

У меня есть следующие клетки:

    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

Я хочу создать "диапазон"/ сводку данных следующим образом:

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

Я могу сделать это вручную, но данные находятся в тысячах ячеек, поэтому это кажется утомительным. Существуют ли встроенные функции, которые помогут облегчить это?

Answer 1

Мы можем сделать это с двумя столбцами Helper. Я поместил следующие формулы в D2 и E2 соответственно:

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

Затем скопировали всю длину набора данных.

Это дало мне список, в котором я могу использовать формулу SMALL(), чтобы получить начало и конец.

В G2 я положил:

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

Затем скопировал одну колонку вниз, пока я не получил пустые ячейки.

Затем мы используем эти числа в INDEX/MATCH для возврата другой информации.

В I2 я положил:

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

Скопировал по одной колонке и вниз, пока я не получил бланки.

Я мог бы сделать то же самое с VLOOKUP:

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

Answer 2

Ваш вопрос может дать вам немного больше объяснений, как шаблон, так и инструменты, которые вы хотите использовать. Я предполагаю, что когда вы говорите "функция", вам нужны листовые формулы, а не код VBA. Вы просматриваете только первый экземпляр каждой пары полос / цветов или ищете последовательно совпадающую полосу и цвет, заданные полностью? Если последнее, ваша таблица будет включать в себя еще три строки следующим образом:

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

Если вам нужна эта более длинная таблица, вы можете создать две новые строки (либо на этом листе, либо на новой) и, предположив, что у вас есть "Число" в A1, использовать следующий код:

Ячейка D2 =IF(NOT(AND($B1=$B2,$C1=$C2)),IFERROR(MAX(INDIRECT("D1:D"&ROW(D2)-1))+1,1),0)

Ячейка E2 =IF(NOT(AND($B2=$B3,$C2=$C3)),IFERROR(MAX(INDIRECT("E1:E"&ROW(E2)-1))+1,1),0)

Расширьте код до полной длины столбца вашей таблицы. Результат будет следующим.

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

Отсюда, просто посмотрите номер каждого набора (1,2,3, ...) и найдите номер строки, чтобы получить информацию после. Если вы сначала разместите столбцы Start/End, вы можете использовать vlookup для этого.

Если вы хотите только первое появление каждой пары (но почему пропущен ряд 8?), Вам понадобится немного больше логики. Для этого может потребоваться дополнительный столбец, но дополнительная логика будет довольно простой.