Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как посчитать последовательные дубликаты в Excel?

Удаление повторяющихся строк текста, но имеющих исключения — Excel

У меня есть несколько строк текста, которые выглядят так, как показано ниже. Как вы можете видеть, существует дубликат «x1». В Excel я хочу удалить дубликаты (например, x1), но сохранить слово «заголовок», поскольку оно считается дубликатом и будет удалено, чего я не хочу.

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

2 ответа

  • Удалить повторяющиеся элементы массива — VBA

У меня есть вопрос в Excel VBA: Допустим,у меня есть массив,состоящий из: , где некоторые элементы повторяются. Я хотел бы получить список всех элементов без повторения одного: т. е. Я нашел код, который удаляет повторяющиеся элементы ( VBA удаление.

Я пробовал искать эту конкретную проблему, но все, что я получаю, это либо удаление повторяющихся строк, либо удаление повторяющихся строк, где они разделены разделителем. Моя проблема несколько иная. У меня есть строка следующего вида: comp name1, comp name2, comp name3.

Я предполагаю, что есть несколько способов справиться с этим, но если «Title» всегда каждый второй ряд, вы можете просто использовать цикл do с «+ 2» для определения дубликатов. Например, предположим, что все эти данные находятся в столбце A, начиная со строки 1:

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

  1. Скопируйте-вставьте содержимое из столбца а в Sublime text.
  2. Затем выберите повторяющееся содержимое, которое вы не хотите удалять, а затем ALT + F3. При этом будут выбраны все экземпляры этого содержимого.(HINT: добавьте один «space» перед этим текстом, чтобы избежать слияния строк при переходе к удалению дубликатов.
  3. Инвертируйте выделение, чтобы теперь выбрать все rest text/content.
  4. Затем перейдите в меню Правка > перестановка строк > уникальный. 5 . DONE!!
Читайте так же:
Как пакетно переименовать несколько имен диапазонов в Excel?
Похожие вопросы:

Я хотел бы выделить повторяющиеся строки в Excel VBA. Предположим, у меня есть следующая таблица со столбцами A, B, C и D для проверки: A B C D (Строки) 1 1 1 1 1 dsf 2 3 5 dgdgdgdg 1 1.

Мне нужно подсчитать количество повторных вхождений значения в столбце, используя excel, ТОЛЬКО для строк с определенным значением во втором столбце, который я представляю. Колонка1 Колонка2 Значение1.

У меня есть следующие данные в столбце: ABNANL2A 940 ABNANL2A :20:ABN AMRO BANK NV :25:233835613 :28:19901/1 :60F:C110717EUR1333,58 :61:1107160718D12,75N426NONREF :86:BEA NR:5FLM01 16.07.11/15.09.

У меня есть вопрос в Excel VBA: Допустим,у меня есть массив,состоящий из: , где некоторые элементы повторяются. Я хотел бы получить список всех элементов без.

Я искал и пытался найти решение этой проблемы, но нашел только удаление повторяющихся строк или удаление повторяющихся строк, когда они разделены разделителем. Мой вариант такой.

Возможное дублирование: Удаление дублирующихся строк Вот структура моей таблицы: Автор (varchar) | Баллы (integer) | Тело (text) Автор всегда один и тот же, а тело всегда одно и то же. Та же запись.

Я хочу выполнить шаг удаления соседних повторяющихся строк с условием исключения и без порядка в приведенном ниже примере VIM: До Regex a b c d 00 f b 00 c e 00 После Regex a b c d 00 f 00.

У меня есть таблица, в которой есть ID и EventDate. Есть повторяющиеся строки, потому что я использовал слияние двух таблиц. Теперь мне нужно иметь строки с минимальным значением eventDate и delete.

Как узнать количество строк и столбцов данных в Excel для JMeter? Было бы неплохо подсчитать количество строк, содержащих данные в Excel, и передать это число в поле threads в jmeter.

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

Читайте так же:
Как получить последний рабочий день предыдущего месяца в Excel?

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

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

Исходные данные

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

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

Несмотря на то, что это не самое простое и не самое быстрое решение, это единственный способ решить проблему «на лету». Выберите исходный список и разделите его на колонки по пробелам с помощью команды Данные — Текст в колонки. В качестве первого шага выберите формат By delimiter из трехшагового Мастера и установите флажок Space в качестве второго шага:

Делим текст по пробелам

Если в исходных данных могут быть лишние пробелы, лучше включить в настройки последовательные разделители Treat — это удалит лишние столбцы.

Затем нажмите кнопку 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 (Developer — Visual Basic) или Alt + F11. Вставьте новый пустой модуль в рабочую книгу через меню Вставка — Модуль и скопируйте в него следующий код:

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

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

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

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

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

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

Макрофункция для выявления повторов внутри ячейки

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

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

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

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

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

Загружаем таблицу в Power Query

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

Окно Power Query

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

Удалите шаг Измененный тип из правой части панели примененных шагов, добавив крестик к левой части ненужного шага.

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

Добавляем столбец индекса

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

Делим на строки по пробелу

После нажатия кнопки OK вы увидите следующее изображение:

Разделенный текст

Теперь выделите два столбца (нажмите Ctrl или Shift) и удалите дубликаты с помощью Главная — Удалить строки — Удалить дубликаты.

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

Группируем

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

Свернутые в таблицы исходные имена без повторов

Имена из каждой таблицы еще нужно вытащить из первого столбца и склеить вместе. Это можно сделать с помощью встроенного языка M языка Power Query. Введите имя нового столбца и формулу (с учетом регистра) в команде Добавить столбец на вкладке Добавить столбец. в открывшемся окне:

Извлекаем имена и склеиваем через пробел

[ Cells][Names] — это выражение, которое извлекает информацию в поле Names из каждой таблицы в столбце Cells, а также извлекает информацию в поле Text. Склеивает их все вместе через заданный разделитель (пробел). Нажав OK, мы, наконец, получим то, что хотим:

Развернутые списки

Остается только удалить столбцы Index и Cells, которые вам больше не нужны, щелкнув правой кнопкой мыши на их заголовках и выбрав пункт Delete Columns, а затем загрузить результаты в лист через Home — Close and Load — Close and Load to ….. :

Результаты

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector