В excel найти и удалить

Поиск и удаление повторений

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

Выберите ячейки, которые нужно проверить на наличие повторений.

Примечание: В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.

На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.

В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК.

Удаление повторяющихся значений

При использовании функции Удаление дубликатов повторяющиеся данные удаляются безвозвратно. Чтобы случайно не потерять необходимые сведения, перед удалением повторяющихся данных рекомендуется скопировать исходные данные на другой лист.

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

Совет: Перед попыткой удаления повторений удалите все структуры и промежуточные итоги из своих данных.

На вкладке Данные нажмите кнопку Удалить дубликаты и в разделе Столбцы установите или снимите флажки, соответствующие столбцам, в которых нужно удалить повторения.

Например, на данном листе в столбце «Январь» содержатся сведения о ценах, которые нужно сохранить.

Поэтому флажок Январь в поле Удаление дубликатов нужно снять.

Удаление одинаковых значений таблицы в Excel

Работая в самом популярном редакторе таблиц Microsoft Excel, пожалуй, каждый пользователь сталкивался с проблемой удаления повторяющихся значений. К счастью, в самом редакторе Excel предусмотрены средства и инструменты, позволяющие наиболее легко разобраться с подобной неприятностью. В этой статье подробно рассмотрим самые простые и эффективные пути решения этой задачи. Итак, давайте разбираться. Поехали!

Microsoft Excel — это одна из самых популярных программ для работы с числами и таблицами

К примеру, у вас имеется таблица с данными, в которой есть несколько совпадений. Чтобы исправить это, существует два способа:

• Кнопка «Удалить дубликаты»;
• С использованием расширенного фильтра.

Ниже разберём каждый из них более подробно.

Первый способ подойдёт для версии Microsoft Excel 2007 и более новых. Здесь разработчики предельно упростили жизнь пользователей, добавив специальный инструмент программы непосредственно для решения этой неприятности. В панели инструментов вы сможете обнаружить пункт «Удалить дубликаты», который находится в блоке «Работа с данными». Работает это так:

1. Сначала перейдите на нужную страницу.
2. Затем нажмите «Удалить дубликаты».
3. Далее, перед вами откроется окно, в котором необходимо отметить столбцы, где имеются совпадающие значения.

После этого Эксель удалит одинаковые элементы в тех столбцах, которые вы отметили.

Преимущество такого подхода в том, что можно избавиться от повторяющихся значений всего за пару кликов, за несколько секунд. Но есть и недостаток — в том случае, если приходится работать с очень большой таблицей, то все одинаковые элементы можно банально не найти. Поэтому перейдём к следующему способу.

Второй подход заключается в использовании расширенного фильтра и работает как на новых версиях Microsoft Excel, так и на старых, включая 2003. Тут придётся понажимать несколько больше, однако, такой подход в разы более эффективен. Итак, сначала нужно открыть таблицу, затем перейдите во вкладку «Данные», в блоке «Сортировка и фильтр» выберите пункт «Дополнительно». Перед вами откроется окно, в котором нужно будет задать диапазон (только в том случае, если у вас есть разрывы, в противном случае ничего задавать не нужно) и поставить птичку на пункте «Только уникальные записи». Если вы хотите просто скрыть дубликаты, чтобы иметь возможность ещё поработать с ними в дальнейшем, то выберите фильтрование списка на месте, если же вы укажете «скопировать результат в другое место», то повторяющиеся элементы будут удалены. После нажатия «ОК» таблица будет отфильтрована программой в соответствии с выбранными вами параметрами. Теперь выделите цветом уникальные элементы и нажмите «Очистить». В результате вы увидите все дубликаты, так как они не будут отмечены цветом. Воспользовавшись автофильтром, можно будет получить полный перечень неуникальных значений.

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

Как удалить строки по условию?

Предположу, что почти каждый сталкивался с ситуацией, когда необходимо удалить только определенные строки: имеется большая таблица и необходимо удалить из неё только те строки, которые содержат какое-то слово (цифру, фразу). Для выполнения подобной задачи можно воспользоваться несколькими способами.

Способ первый:
Использовать встроенное средство Excel — фильтр. Сначала его необходимо «установить» на листе:

• Выделяем таблицу с данными, включая заголовки. Если их нет — то выделяем с самой первой строки таблицы, в которой необходимо удалить данные
• устанавливаем фильтр:
  • для Excel 2003 : Данные—Фильтр—Автофильтр
  • для Excel 2007-2010 : вкладка Данные (Data) —Фильтр (Filter)(или вкладка Главная (Home) —Сортировка и фильтр (Sort&Filter) —Фильтр (Filter) )

Теперь выбираем условие для фильтра:

• в Excel 2003 надо выбрать Условие и в появившейся форме выбрать непосредственно условие(«равно», «содержит», «начинается с» и т.д.), а напротив значение в соответствии с условием.
• Для 2007-2010 Excel нужно выбрать Текстовые фильтры (Text Filters) и либо сразу выбрать одно из предлагаемых условий, либо нажать Настраиваемый фильтр (Custom Filter) и ввести значения для отбора в форме

После этого удалить отфильтрованные строки. В 2007 Excel могут возникнуть проблемы с удалением отфильтрованных строк, поэтому рекомендую сначала так же прочитать статью: Excel удаляет вместо отфильтрованных строк — все?! Как избежать.

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

Sub Del_SubStr() Dim sSubStr As String ‘искомое слово или фраза(может быть указанием на ячейку) Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim lMet As Long Dim arr sSubStr = InputBox(«Укажите значение, которое необходимо найти в строке», «Запрос параметра», «») If sSubStr = «» Then lMet = 0 Else lMet = 1 lCol = Val(InputBox(«Укажите номер столбца, в котором искать указанное значение», «Запрос параметра», 1)) If lCol = 0 Then Exit Sub lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count arr = Cells(1, lCol).Resize(lLastRow).Value Application.ScreenUpdating = 0 Dim rr As Range For li = 1 To lLastRow ‘цикл с первой строки до конца If -(InStr(arr(li, 1), sSubStr) > 0) = lMet Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub

Если значение sSubStr не будет указано, то будут удалены строки, ячейки указанного столбца которых, пустые.
Данный код необходимо поместить в стандартный модуль. Вызвать с листа его можно нажатием клавиш Alt + F8 , после чего выбрать Del_SubStr и нажать Выполнить. Если в данном коде в строке
If -(InStr(Cells(li, 1), sSubStr) > 0) = lMet Then
вместо = lMet указать <> lMet , то удаляться будут строки, не содержащие указанное для поиска значение. Иногда тоже удобно.
Но. Данный код просматривает строки на предмет частичного совпадения указанного значения. Например, если Вы укажете текст для поиска «отчет», то будут удалены все строки, в которых встречается это слово(«квартальный отчет», «отчет за месяц» и т.д.). Это не всегда нужно. Поэтому ниже приведен код, который будет удалять только строки, указанные ячейки которых равны конкретно указанному значению:

Sub Del_SubStr() Dim sSubStr As String ‘искомое слово или фраза(может быть указанием на ячейку) Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim arr sSubStr = InputBox(«Укажите значение, которое необходимо найти в строке», «Запрос параметра», «») lCol = Val(InputBox(«Укажите номер столбца, в котором искать указанное значение», «Запрос параметра», 1)) If lCol = 0 Then Exit Sub lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count arr = Cells(1, lCol).Resize(lLastRow).Value Application.ScreenUpdating = 0 Dim rr As Range For li = 1 To lLastRow ‘цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If Next li If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub

Здесь так же, как и в случае с предыдущим кодом можно заменить оператор сравнения( Cells(li, lCol) = sSubStr ) с равно на неравенство( Cells(li, lCol) <> sSubStr ) и тогда удаляться будут строки, значения ячеек которых не равно указанному.

УДАЛЕНИЕ СТРОК НА ОСНОВАНИИ СПИСКА ЗНАЧЕНИЙ(МНОЖЕСТВЕННЫЕ КРИТЕРИИ)
Иногда бывают ситуации, когда необходимо удалить строки не по одному значению, а по нескольким. Например, если строка содержит или Итог или Отчет. Ниже приведен код, при помощи которого можно удалить строки, указав в качестве критерия диапазон значений.
Значения, которые необходимо найти и удалить перечисляются на листе с именем «Лист2». Т.е. указав на «Лист2» в столбце А(начиная с первой строки) несколько значений — они все будут удалены. Если лист называется иначе(скажем «Соответствия») в коде необходимо будет «Лист2» заменить на «Соответствия». Удаление строк происходит на активном в момент запуска кода листе. Это значит, что перед запуском кода надо перейти на тот лист, строки в котором необходимо удалить.

Sub Del_Array_SubStr() Dim sSubStr As String ‘искомое слово или фраза Dim lCol As Long ‘номер столбца с просматриваемыми значениями Dim lLastRow As Long, li As Long Dim avArr, lr As Long Dim arr lCol = Val(InputBox(«Укажите номер столбца, в котором искать указанное значение», «Запрос параметра», 1)) If lCol = 0 Then Exit Sub Application.ScreenUpdating = 0 lLastRow = ActiveSheet.UsedRange.Row — 1 + ActiveSheet.UsedRange.Rows.Count ‘заносим в массив значения листа, в котором необходимо удалить строки arr = Cells(1, lCol).Resize(lLastRow).Value ‘Получаем с Лист2 значения, которые надо удалить в активном листе With Sheets(«Лист2») ‘Имя листа с диапазоном значений на удаление avArr = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With ‘удаляем Dim rr As Range For lr = 1 To UBound(avArr, 1) sSubStr = avArr(lr, 1) For li = 1 To lLastRow ‘цикл с первой строки до конца If CStr(arr(li, 1)) = sSubStr Then If rr Is Nothing Then Set rr = Cells(li, 1) Else Set rr = Union(rr, Cells(li, 1)) End If End If DoEvents Next li DoEvents Next lr If Not rr Is Nothing Then rr.EntireRow.Delete Application.ScreenUpdating = 1 End Sub

Чтобы код выше удалял строки не по точному совпадению слов, а по частичному(например, в ячейке записано «Привет, как дела?», а в списке есть слово «привет» — надо удалить, т.к. есть слово «привет»), то надо строку:

Поиск и удаление дубликатов в Microsoft Excel

При работе с таблицей или базой данных с большим количеством информации возможна ситуация, когда некоторые строки повторяются. Это ещё больше увеличивает массив данных. К тому же, при наличии дубликатов возможен некорректный подсчет результатов в формулах. Давайте разберемся, как в программе Microsoft Excel отыскать и удалить повторяющиеся строки.

Поиск и удаление

Найти и удалить значения таблицы, которые дублируются, возможно разными способами. В каждом из этих вариантов поиск и ликвидация дубликатов – это звенья одного процесса.

Способ 1: простое удаление повторяющихся строк

Проще всего удалить дубликаты – это воспользоваться специальной кнопкой на ленте, предназначенной для этих целей.

1. Выделяем весь табличный диапазон. Переходим во вкладку «Данные». Жмем на кнопку «Удалить дубликаты». Она располагается на ленте в блоке инструментов «Работа с данными».

Открывается окно удаление дубликатов. Если у вас таблица с шапкой (а в подавляющем большинстве всегда так и есть), то около параметра «Мои данные содержат заголовки» должна стоять галочка. В основном поле окна расположен список столбцов, по которым будет проводиться проверка. Строка будет считаться дублем только в случае, если данные всех столбцов, выделенных галочкой, совпадут. То есть, если вы снимете галочку с названия какого-то столбца, то тем самым расширяете вероятность признания записи повторной. После того, как все требуемые настройки произведены, жмем на кнопку «OK».

Excel выполняет процедуру поиска и удаления дубликатов. После её завершения появляется информационное окно, в котором сообщается, сколько повторных значений было удалено и количество оставшихся уникальных записей. Чтобы закрыть данное окно, жмем кнопку «OK».

Способ 2: удаление дубликатов в «умной таблице»

Дубликаты можно удалить из диапазона ячеек, создав умную таблицу.

Выделяем весь табличный диапазон.

Находясь во вкладке «Главная» жмем на кнопку «Форматировать как таблицу», расположенную на ленте в блоке инструментов «Стили». В появившемся списке выбираем любой понравившийся стиль.

Затем открывается небольшое окошко, в котором нужно подтвердить выбранный диапазон для формирования «умной таблицы». Если вы выделили все правильно, то можно подтверждать, если допустили ошибку, то в этом окне следует исправить. Важно также обратить внимание на то, чтобы около параметра «Таблица с заголовками» стояла галочка. Если её нет, то следует поставить. После того, как все настройки завершены, жмите на кнопку «OK». «Умная таблица» создана.

После этого, открывается окно удаления дубликатов, работа с которым была подробно расписана при описании первого способа. Все дальнейшие действия производятся в точно таком же порядке.

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

Способ 3: применение сортировки

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

Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».

Фильтр включен, о чем говорят появившиеся пиктограммы в виде перевернутых треугольников в названиях столбцов. Теперь нам нужно его настроить. Кликаем по кнопке «Дополнительно», расположенной рядом все в той же группе инструментов «Сортировка и фильтр».

Открывается окно расширенного фильтра. Устанавливаем в нем галочку напротив параметра «Только уникальные записи». Все остальные настройки оставляем по умолчанию. После этого кликаем по кнопке «OK».

После этого, повторяющиеся записи будут скрыты. Но их показ можно в любой момент включить повторным нажатием на кнопку «Фильтр».

Способ 4: условное форматирование

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

Выделяем область таблицы. Находясь во вкладке «Главная», жмем на кнопку «Условное форматирование», расположенную в блоке настроек «Стили». В появившемся меню последовательно переходим по пунктам «Правила выделения» и «Повторяющиеся значения…».

Открывается окно настройки форматирования. Первый параметр в нём оставляем без изменения – «Повторяющиеся». А вот в параметре выделения можно, как оставить настройки по умолчанию, так и выбрать любой подходящий для вас цвет, после этого жмем на кнопку «OK».

После этого произойдет выделение ячеек с повторяющимися значениями. Эти ячейки вы потом при желании сможете удалить вручную стандартным способом.

Внимание! Поиск дублей с применением условного форматирования производится не по строке в целом, а по каждой ячейке в частности, поэтому не для всех случаев он является подходящим.

Способ 5: применение формулы

Кроме того, найти дубликаты можно применив формулу с использованием сразу нескольких функций. С её помощью можно производить поиск дубликатов по конкретному столбцу. Общий вид данной формулы будет выглядеть следующим образом:

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));»»)

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

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

Выделяем весь столбец для дубликатов, кроме шапки. Устанавливаем курсор в конец строки формул. Нажимаем на клавиатуре кнопку F2. Затем набираем комбинацию клавиш Ctrl+Shift+Enter. Это обусловлено особенностями применения формул к массивам.

После этих действий в столбце «Дубликаты» отобразятся повторяющиеся значения.

Но, данный способ все-таки слишком сложен для большинства пользователей. К тому же, он предполагает только поиск дублей, но не их удаление. Поэтому рекомендуется применять более простые и функциональные решения, описанные ранее.

Как видим, в Экселе есть множество инструментов предназначенных для поиска и удаления дублей. У каждого из них есть свои особенности. Например, условное форматирование предполагает поиск дублей только по каждой ячейке в отдельности. К тому же, не все инструменты могут не только искать, но и удалять повторяющиеся значения. Наиболее универсальный вариант – это создание «умной таблицы». При использовании данного способа можно максимально точно и удобно настроить поиск дубликатов. К тому же, их удаление происходит моментально.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Дубликаты внутри ячейки

Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы):

Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать.

Способ 1. Ищем повторения: текст по столбцам и формула массива

Это не самый удобный и быстрый, но зато самый простой вариант решения задачи «на коленке». Выделим исходный список и разобъем его на столбцы по пробелам с помощью команды Данные — Текст по столбцам (Data — Text to columns) . В открывшемся окне трёхшагового Мастера выберем формат По разделителю (By delimiter) на первом шаге и поставим флажок Пробел (Space) на втором:

Если в исходных данных могут быть лишние пробелы, то лучше включить и опцию Считать последовательные разделители одним (Treat consecutive delimiters as one) — это избавит нас от лишних столбцов.

На третьем шаге в поле Поместить в зададим пустую ячейку рядом с таблицей, чтобы результаты не затёрли нам исходные данные и нажмём на Готово (Finish) :

Наши данные разделятся по ячейкам. Останется подсчитать количество повторов в каждой строке с помощью небольшой, но хитрой формулы массива:

В английской версии это будет =SUMPRODUCT(N(COUNTIF(B2:G2,B2:G2)>1))

Давайте разберём логику её работы на примере первой строки.

1. Сначала мы с помощью формулы СЧЁТЕСЛИ( B2:G2 ; B2:G2 ) вычисляем по очереди количество вхождений каждого имени в диапазон B2:G2 и получаем на выходе массив <1,2,1,2,1>, т.к. Иван встречается в первой строке 1 раз, Елена — 2 раза, Сергей — 1 и т.д.
2. Проверяем с помощью СЧЁТЕСЛИ(B2:G2;B2:G2) >1 какие из полученных чисел больше единицы, т.е. где у нас повторы. На выходе эта формула выдаст нам массив результатов проверки в виде .
3. Переводим логические значения ЛОЖЬ и ИСТИНА в более удобные для подсчета 0 и 1, соответственно, с помощью функции Ч. На выходе получаем массив <0,1,0,1,0>.
4. Суммируем все элементы получившегося массива функцией СУММПРОИЗВ. Можно было бы использовать и обычную функцию СУММ, но тогда пришлось бы жать вместо привычного Enter сочетание клавиш Ctrl + Shift + Enter , чтобы ввести формулу как формулу массива.

По получившемуся столбцу можно легко отфильтровать строки с повторами и работать потом с ними дальше уже вручную.

Минусы такого способа, впрочем, весьма очевидны: при изменении в исходных данных придётся повторять всю процедуру заново, дубликаты не очень заметны и удалять их тоже надо врукопашную. Поэтому идём дальше.

Способ 2. Выделение цветом повторов внутри ячейки макросом

Если дубликаты нужно именно наглядно показать, то удобнее будет использовать для этого специальный макрос. Откроем редактор Visual Basic одноимённой кнопкой на вкладке Разработчик (Developer — Visual Basic) или сочетанием клавиш Alt + F11 . Вставим в книгу новый пустой модуль через меню Insert — Module и скопируем туда вот такой код:

Теперь можно вернуться в главное окно Excel, выделить ячейки с текстом и запустить созданный макрос через кнопку Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt + F8 . Этот макрос проходит по всем выделенным ячейкам и помечает повторения красным цветом шрифта прямо внутри ячейки:

Если нужно, чтобы цветом выделялись только клоны, но не первые вхождения (т.е. только вторая и третья, но не первая Алиса, например), то достаточно будет просто убрать из кода строку 20.

Способ 3. Выводим повторы в соседний столбец

Если повторы внутри ячеек нужно не просто подсветить, а явным образом вывести, например, в соседний столбец, то удобнее будет использовать для этого макрофункцию, созданную по образу предыдущего макроса. Добавим в редакторе Visual Basic новый модуль и вставим туда код нашей функции GetDuplicates:

Эта функция, как легко догадаться, принимает в качестве единственного аргумента ячейку с текстом и выводит в качестве результата все повторы, которые там найдет:

Способ 4. Удаление повторов внутри ячейки макросом

Если нужно просто удалить дубликаты внутри ячейки, чтобы все оставшиеся там слова не повторялись, то макрос будет похож на предыдущий, но попроще:

Способ 5. Удаление повторов внутри ячейки через Power Query

Этот способ использует бесплатную надстройку Excel для обработки данных под названием Power Query. Для Excel 2010-2013 скачать её можно с сайта Microsoft, а в Excel 2016-2019 она уже встроена по умолчанию. Огромным плюсом этого варианта является возможность автоматического обновления — если в будущем исходные данные изменятся, то нам не придется заново проделывать всю обработку (как в Способе 1) или запускать макрос (как в Способе 4) — достаточно будет просто обновить созданный запрос.

Сначала наши данные нужно загрузить в Power Query. Проще всего для этого превратить нашу таблицу в «умную» сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home — Format as Table) , а затем нажать кнопку Из таблицы/диапазона (From table/range) на вкладке Power Query (если у вас Excel 2010-2013) или на вкладке Данные (если у вас Excel 2016 или новее):

Поверх окна Excel откроется окно редактора запросов Power Query с загруженными туда нашими данными:

Дальше делаем следующую цепочку действий:

Удаляем ненужный пока шаг Измененный тип (Changed Type) справа в панели применённых шагов с помощью крестика слева от шага.

Чтобы можно было потом идентифицировать принадлежность каждого имени к исходной строке — добавляем столбец с нумерацией строк на вкладке Добавление столбца — Столбец индекса — От 1 (Add Column — Index Column — From 1) :

Выделяем столбец с именами и жмём на вкладке Преобразование — Разделить столбец — По разделителю (Transform — Split Column — By delimiter) , а в открывшемся окне выбираем деление по каждому пробелу и — главное — деление на строки, а не на столбцы в расширенных параметрах:

После нажатия на ОК увидим следующее:

Теперь выделяем оба столбца (удерживая клавишу Ctrl или Shift ) и удаляем дубликаты через Главная — Удалить строки — Удалить дубликаты (Home — Remove Rows — Remove Duplicates) .

Осталось собрать всё обратно в ячейки 🙂 Для этого выделим столбец Индекс и используем команду Группировать по на вкладке Преобразование (Transform — Group By) со следующими параметрами:

После нажатия на ОК наши имена сгруппируются во вложенные таблицы, имитирующие начальные ячейки — только уже без повторов. Увидеть содержимое свёрнутых таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table (но не в слово Table!):

Осталось вытащить все имена из первой колонки каждой таблицы и склеить их через пробел. Это можно сделать с помощью небольшой формулы на встроенном в Power Query языке М. Выберем на вкладке Добавление столбца команду Настраиваемый столбец (Add Column — Custom Column) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):

Здесь выражение [Ячейки][Имена] извлекает содержимое столбца Имена из каждой таблицы в колонке Ячейки, а функция Text.Combine склеивает затем их все через заданный разделитель (пробел). После нажатия на ОК мы, наконец, увидим желаемое:

Осталось удалить ненужные более столбцы Индекс и Ячейки, щелкнув по их заголовкам правой кнопкой мыши и выбрав команду Удалить столбцы (Remove Columns) и выгрузить результаты на лист через Главная — Закрыть и загрузить — Закрыть и загрузить в (Home — Close & Load — Close & Load to..) :

Задача решена! Если в будущем данные в исходной «умной» таблице изменятся или к ней будут дописаны новые строки, то достаточно будет просто обновить запрос, щёлкнув по результирующей зелёной таблице правой кнопкой мыши и выбрав команду Обновить или нажав сочетание клавиш Ctrl + Alt + F5 .

Читать еще:  Вставить объект excel в excel