Как сортировать компании и электронные адреса в Excel: пошаговое руководство

Как оптимизировать таблицы Excel: объединение повторяющихся названий компаний и адресов электронной почты

В повседневной работе с данными часто возникает необходимость обрабатывать большие объемы информации. Одной из распространенных задач является оптимизация таблиц, где одно и то же название компании может повторяться несколько раз. В этой статье мы рассмотрим, как преобразовать таблицы Excel, чтобы каждое название компании отображалось лишь один раз, а все связанные адреса электронной почты были помещены в одном ряду.

Пример задачи

Представьте, что у вас есть следующая таблица, в которой имена компаний дублируются, а адреса электронной почты представлены в соседних строках:

company1  email1
company1  email2
company1  email3
company2  email1
company3  email1
company3  email2

Ваша цель состоит в том, чтобы получить результат в следующем виде:

company1   email1   email2   email3
company2   email1   
company3   email1   email2

Решения для современных версий Excel

С помощью современных версий Excel, таких как Microsoft 365, можно воспользоваться новыми функциями для достижения желаемого результата. Вот несколько подходов, которые вы можете использовать:

Вариант первый: Использование функции PIVOTBY()

Для начала воспользуйтесь следующей формулой, чтобы организовать данные:

=PIVOTBY(A2:A7, MAP(A2:A7, LAMBDA(x, COUNTIF(A2:x, x))), B2:B7, SINGLE, , 0, , 0)

Эта формула позволит вам создать сводную таблицу с необходимой структурой.

Вариант второй: Использование функции MAKEARRAY()

Если вы предпочитаете другой подход, попробуйте следующую формулу:

=LET(
     a, A2:A7,
     b, UNIQUE(a),
     c, IFERROR(MAKEARRAY(ROWS(b), MAX(COUNTIF(a, b)), LAMBDA(x, y, INDEX(FILTER(B2:B7, a = INDEX(b, x), ""), y))), ""),
     HSTACK(b, c)
)

Эта формула делает то же самое, но с применением других функций для вакансий.

Вариант третий: Использование функции REDUCE()

Еще один вариант – использовать функцию REDUCE():

=LET(
     a, A2:A7,
     b, UNIQUE(a),
     c, IFNA(REDUCE("", b, LAMBDA(x, y, VSTACK(x, TOROW(FILTER(B2:B7, a = y))))), ""),
     HSTACK(b, DROP(c, 1))
)

Этот способ также эффективно организует данные.

Решения для старых версий Excel

Если вы работаете с более старыми версиями Excel, используйте следующие формулы:

1. Для получения уникальных названий компаний:

=IFERROR(INDEX(A$2:A$7, MATCH(0, COUNTIF(D$1:D1, A$2:A$7), 0)), "")

1. Для получения связанных адресов электронной почты:

=IFERROR(INDEX($B$2:$B$7, AGGREGATE(15, 7, (ROW($A$2:$A$7) - ROW($A$2) + 1) / ($D2 = $A$2:$A$7), COLUMNS($E$2:E2)), "")

Не забудьте использовать сочетание клавиш Ctrl + Shift + Enter, чтобы активировать формулы массивов в более старых версиях.

Альтернативный метод: динамические формулы массива

Вы также можете использовать динамические формулы массива для достижения нужного результата. В ячейке C1 используйте следующую формулу:

=UNIQUE(FILTER(A:A, A:A <> ""))

Примечание: Данная формула требует Excel 2021 или новее для полноценной работы и автоматически заполняет нужные ячейки.

Затем, в столбце D (ячейка D1), используйте следующую формулу и перетащите её вниз:

=TRANSPOSE(FILTER(B:B, A:A = C1))

Заключение

Благодаря вышеописанным методам, вы можете эффективно оптимизировать свои таблицы Excel, устраняя дубликаты названий компаний и группируя адреса электронной почты. Используйте новейшие функции Excel для максимальной эффективности или адаптируйтесь под старые версии, чтобы получать нужные данные в удобном формате. Надеемся, эта статья была полезной и поможет вам в работе!

Источник