☰

Я хочу создать диапазон в Excel

tag-icon • tag-icon microsoft-excel
Я хочу создать диапазон в Excel

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

    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

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

решение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)),"")

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

Затем мы используем эти числа в ИНДЕКСЕ/ПОИСКПОЗЕ для возврата другой информации.

В I2 я ставлю:

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

Скопировал один столбец и вниз, пока не получились пробелы.

Я мог бы сделать то же самое с помощью ВПР:

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

введите описание изображения здесь

решение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 пропущена?), вам понадобится немного больше логики. Это может потребовать дополнительного столбца, но дополнительная логика будет довольно простой.

Связанный контент