Как сгруппировать дубликаты в excel

Как объединить ячейки с одинаковым значением в Excel

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

Как выделить одинаковые ячейки группами

Допустим мы имеем список поставщиков:

Перед тем как найти повторяющиеся ячейки в Excel, отсортируем поставщиков по их идентификатору. Переходим в ячейку A2 и выбираем на закладке «ДАННЫЕ» в разделе «Сортировка и фильтр» инструмент «Сортировка от А до Я».

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

  1. Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».
  2. В поле для ввода формулы вводим следующе значение: =$A2<>$A3
  3. Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.

В результате получаем эффект как отображено на рисунке.

Нам удалось объединить и выделить повторяющиеся ячейки в Excel. Теперь в приходной накладной все отсортированные поставщики визуально разграничены линией друг от друга.

Так как мы сортировали поставщиков по их идентификатору в формуле мы зафиксировали смешанной ссылкой столбец листа $A. Если значения в соседних ячейках столбца $A равные между собой тогда формула возвращает значения ЛОЖЬ и форматирование границе не применяется. Но если верхнее значение неравно (оператор <>) нижнему значению тогда формула возвращает значение ИСТИНА и применяется форматирования нижней границы целой строки (так как в смешанной ссылке номер строки не есть абсолютным, а является относительным адресом).

Полезный совет! Если нужно разграничить данные не по первому столбцу таблицы, по любому другому, тогда соответственно отсортируйте и просто укажите адрес столбца. Например, разграничим по повторяющимся датам, а не по поставщикам. Для этого сначала сортируем данные по датам, а потом используем условное форматирование немного изменив формулу: =$C2<>$C3

Теперь список сгруппирован по одинаковым датам.

Слияние двух списков без дубликатов

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

Давайте традиционно рассмотрим несколько способов решения такой распространенной задачи — от примитивных «в лоб» до более сложных, но изящных.

Способ 1. Удаление дубликатов

Можно решить задачу самым простым путем — руками скопировать элементы обоих списков в один и применить потом к полученному набору инструмент Удалить дубликаты с вкладки Данные (Data — Remove Duplicates) :

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

Способ 1а. Сводная таблица

Этот способ является, по сути, логическим продолжением предыдущего. Если списки не очень большого размера и заранее известно предельное количество элементов в них (например, не больше 10), то можно объединить две таблицы в одну прямыми ссылками, добавить справа столбец с единичками и построить по получившейся таблице сводную:

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

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

Способ 2. Формула массива

Можно решить проблему формулами. В этом случае пересчет и обновление результатов будет происходить автоматически и мгновенно, сразу после изменений в исходных списках. Для удобства и краткости давайте дадим нашим спискам имена Список1 и Список2, используя Диспетчер имен на вкладке Формулы (Formulas — Name Manager — Create) :

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

На первый взгляд выглядит жутковато, но, на самом деле, все не так страшно. Давайте я разложу эту формулу на несколько строк, используя сочетание клавиш Alt+Enter и отступы пробелами, как мы делали, например тут:

Логика тут следующая:

    Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает все уникальные элементы из первого списка. Как только они заканчиваются — начинает выдавать ошибку #Н/Д:

  • Формула ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список2); 0)) аналогичным образом извлекает уникальные элементы из второго списка.
  • Вложенные друг в друга две функции ЕСЛИОШИБКА реализуют вывод сначала уникальных из списка-1, а потом из списка-2 друг за другом.

    • Обратите внимание, что это формула массива, т.е. после набора ее нужно ввести в ячейку не обычным Enter , а сочетанием клавиш Ctrl + Shift + Enter и затем скопировать (протянуть) вниз на нижестоящие ячейки с запасом.

    В английской версии Excel эта формула выглядит как:

    =IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1), 0)), INDEX(Список2, MATCH(0, COUNTIF($E$1:E1, Список2), 0))), «»)

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

    Способ 3. Power Query

    Если в ваших исходных списках большое количество элементов, например, по несколько сотен или тысяч, то вместо медленной формулы массива лучше использовать принципиально другой подход, а именно — инструменты надстройки Power Query. Эта надстройка по умолчанию встроена в Excel 2016. Если у вас Excel 2010 или 2013, то ее можно отдельно скачать и установить (бесплатно).

    Алгоритм действий следующий:

    1. Открываем отдельную вкладку установленной надстройки Power Query (если у вас Excel 2010-2013) или просто идем на вкладку Данные (если у вас Excel 2016).
    2. Выделяем первый список и жмем кнопку Из таблицы/диапазона (From Range/Table) . На вопрос про создание из нашего списка «умной таблицы» — соглашаемся:

  • Открывается окно редактора запросов, где будет видно загруженные данные и имя запроса Таблица1 (можно поменять на свое, если хотите).
  • Делаем двойной щелчок в заголовок таблицы (слово Список1) и переименовываем на любой другой (например Люди). Каки именно назвать — не важно, но придуманное название нужно запомнить, т.к. его придется использовать потом еще раз при импорте второй таблицы. Объединить две таблицы в дальнейшем получится только если заголовки их столбцов совпадают.
  • Разворачиваем выпадающий список в левом верхнем углу Закрыть и загрузить и выбираем Закрыть и загрузить в. (Close&Load to. ) :

    В следующем диалоговом окне (оно может выглядеть немного по-другому — не пугайтесь) выбираем Только создать подключение (Only create connection) :

  • Повторяем всю процедуру (пункты 2-6) для второго списка. При переименовании заголовка столбца важно использовать то же имя (Люди), что и в предыдущем запросе.
  • В окне Excel на вкладке Данные (Data) или на вкладке Power Query выбираем Получить данные — Объединить запросы — Добавить (Get Data — Merge Queries — Append) :

    В появившемся диалоговом окне выбираем наши запросы из выпадающих списков:

    В итоге получим новый запрос, где два списка будут соединены друг под другом. Осталось удалить дубликаты кнопкой Удалить строки — Удалить дубликаты (Delete Rows — Delete Duplicates) :


    Готовый запрос можно переименовать справа на панели параметров, дав ему вменяемое имя (это будет имя таблицы-результата по факту) и все и можно все выгружать на лист командой Закрыть и загрузить (Close&Load) :

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

    Объединить по значению

    Данная функция является частью надстройки MulTEx

    • Описание, установка, удаление и обновление
    • Полный список команд и функций MulTEx
    • Часто задаваемые вопросы по MulTEx
    • Скачать MulTEx

    Вызов команды:
    MulTEx -группа Ячейки/Диапазоны —Ячейки —Объединить по значению

    Иногда при подготовке итоговых отчетов для красоты необходимо объединять ячейки с одинаковыми значениями в одну. К примеру, есть такой отчет, выгруженный из 1С:

    Вполне логично было бы перед тем как распечатать подобный отчет каждый город объединить визуально:

    Делать это руками будет долго и неинтересно, особенно, если в отчете более пары тысяч строк. Как раз для того, чтобы объединить несколько ячеек с одинаковыми значениями, идущими подряд, и предназначена команда «Объединить по значению». Для этого необходимо выделить диапазон ячеек для объединения и вызвать команду: MulTEx -группа Ячейки и Диапазон —Ячейки —Объединить по значению.

    Можно объединить либо по строкам, либо по столбцам.
    Направление:

    • По строкам — просмотр одинаковых значений и объединение ячеек происходит сверху вниз, а затем слева направо. Т.е. последовательно просматривается каждый столбец выделенных ячеек
    • По столбцам — просмотр одинаковых значений и объединение ячеек происходит слева направо, а затем сверху вниз. Т.е. последовательно просматривается каждая строка выделенных ячеек

    Данное действие невозможно отменить, поэтому следует осторожно его применять. Хотя можно произвести обратную операцию командой Разъединить с заполнением.

    Поделитесь своим мнением

    Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

    Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

    Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel

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

    1. Удаление повторяющихся значений в Excel (2007+)

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

    Если в каждом столбце таблицы имеется заголовок, установить маркер Мои данные содержат заголовки. Также проставляем маркеры напротив тех столбцов, в которых требуется произвести поиск дубликатов.

    Щелкаем ОК, диалоговое окно будет закрыто и строки, содержащие дубликаты будут удалены.

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

    2. Использование расширенного фильтра для удаления дубликатов

    Выберите любую ячейку в таблице, перейдите по вкладке Данные в группу Сортировка и фильтр, щелкните по кнопке Дополнительно.

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

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

    3. Выделение повторяющихся значений с помощью условного форматирования в Excel (2007+)

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

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

    4. Использование сводных таблиц для определения повторяющихся значений

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

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

    Как объединить две таблицы Excel по частичному совпадению ячеек

    Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.

    Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.

    Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, “12345” и “12345-новый_суффикс“. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

    И что совсем плохо – соответствия могут быть вовсе нечёткими, и “Некоторая компания” в одной таблице может превратиться в “ЗАО «Некоторая Компания»” в другой таблице, а “Новая Компания (бывшая Некоторая Компания)” и “Старая Компания” тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

    Выход есть всегда, читайте далее и Вы узнаете решение!

    Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP), ПОИСКПОЗ (MATCH), ГПР (HLOOKUP) и так далее.

    Выберите подходящий пример, чтобы сразу перейти к нужному решению:

    Ключевой столбец в одной из таблиц содержит дополнительные символы

    Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.

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

    Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper:

    • Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:
    • Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):
    • Даём столбцу имя SKU helper.
    • Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:

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

  • Копируем эту формулу во все ячейки нового столбца.

    • Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.

    Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

    Другие формулы

    • Извлечь первые Х символов справа: например, 6 символов справа из записи “DSFH-164900”. Формула будет выглядеть так:

    =ПРАВСИМВ(A2;6)
    =RIGHT(A2,6)
    Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь “0123” из записи “PREFIX_0123_SUFF”. Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:

    =ПСТР(A2;8;4)
    =MID(A2,8,4)
    Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь “123456” и “0123” из записей “123456-суффикс” и “0123-суффикс” соответственно. Формула будет выглядеть так:

    Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – свяжитесь с нами, мы сделаем всё возможное, чтобы помочь Вам.

    Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

    Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY, где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.

    Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.

    В ячейке C2 запишем такую формулу:

    Здесь A2 – это адрес ячейки, содержащей код группы; символ “–” – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

    Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.

    Данные в ключевых столбцах не совпадают

    Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

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

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

    1. Создаём вспомогательную таблицу для поиска.

    Создаём новый лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

    Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз :-).

    В результате мы имеем вот такую таблицу:

    2. Обновляем главную таблицу при помощи данных из таблицы для поиска.

    В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

    Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

    Столбец Supp.SKU заполняется оригинальными кодами производителя.

    Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU, соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика. После этого повторяем шаг 2.

    3. Переносим данные из таблицы поиска в главную таблицу

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

    При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1, используя для поиска соответствий столбец Supp.SKU.

    Вот пример обновлённых данных в столбце Wholesale Price:

    Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.

    Источник

    Skip to content

    Как найти повторяющиеся значения в таблице Excel

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

    Работая с большими наборами данных в Excel или объединяя несколько небольших электронных таблиц в более крупные, вы можете столкнуться с большим числом одинаковых строк.

    И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в одном списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и ниже. Вот о чём мы поговорим:

    • Поиск повторяющихся значений включая первые вхождения
    • Поиск дубликатов без первых вхождений
    • Определяем дубликаты с учетом регистра
    • Как извлечь дубликаты из диапазона ячеек
    • Как обнаружить одинаковые строки в таблице данных
    • Использование встроенных фильтров Excel
    • Применение условного форматирования
    • Поиск совпадений при помощи встроенной команды «Найти»
    • Определяем дубликаты при помощи сводной таблицы
    • Duplicate Remover — быстрый и эффективный способ найти дубликаты

    Самой простой в использовании и вместе с тем эффективной в данном случае будет функция СЧЁТЕСЛИ (COUNTIF). С помощью одной только неё можно определить не только неуникальные позиции, но и их первые появления в столбце. Рассмотрим разницу на примерах.

    Поиск повторяющихся значений включая первые вхождения.

    Предположим, что у вас в колонке А находится набор каких-то показателей, среди которых, вероятно, есть одинаковые. Это могут быть номера заказов, названия товаров, имена клиентов и прочие данные. Если ваша задача — найти их, то следующая формула для вас:

    =СЧЁТЕСЛИ(A:A; A2)>1

    Где А2 — первая ячейка из области для поиска.

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

    Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если имеются совпадения.  А для встречающихся только 1 раз значений она показывает ЛОЖЬ.

    Подсказка! Если вы ищите повторы в определенной области, а не во всей колонке, обозначьте нужный диапазон и “зафиксируйте” его знаками $. Это значительно ускорит вычисления. Например, если вы ищете в A2:A8, используйте

    =СЧЕТЕСЛИ($A$2:$A$8, A2)>1

    Если вас путает ИСТИНА и ЛОЖЬ в статусной колонке и вы не хотите держать в уме, что из них означает повторяющееся, а что — уникальное, заверните свою СЧЕТЕСЛИ в функцию ЕСЛИ и укажите любое слово, которое должно соответствовать дубликатам и уникальным:

    =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;»Дубликат»;»Уникальное»)

    Если же вам нужно, чтобы формула указывала только на дубли, замените «Уникальное» на пустоту («»):

    =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;»Дубликат»;»»)

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

    Поиск неуникальных значений без учета первых вхождений

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

    Если вам нужно указать только совпадения, давайте немного изменим:

    =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; A2)>1;»Дубликат»;»»)

    На скриншоте ниже вы видите эту формулу в деле.

    Нетрудно заметить, что она не обозначает первое появление слова, а начинает отсчет со второго.

    Чувствительный к регистру поиск дубликатов

    Хочу обратить ваше внимание на то, что хоть формулы выше и находят 100%-дубликаты, есть один тонкий момент — они не чувствительны к регистру. Быть может, для вас это не принципиально. Но если в ваших данных абв, Абв и АБВ — это три разных параметра – то этот пример для вас.

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

    {=ЕСЛИ(СУММ((—СОВПАД($A$2:$A$17;A2)))<=1;»»;»Дубликат»)}

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

    Если вернуться к содержанию, то здесь используется функция СОВПАД для сравнения целевой ячейки со всеми остальными ячейками с выбранной области. Результат возвращается в виде ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из 1 и 0 при помощи оператора (—).

    После этого, функция СУММ складывает эти числа. И если полученный результат больше 1, функция ЕСЛИ сообщает о найденном дубликате.

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

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

    Как извлечь дубликаты из диапазона.

    Формулы, которые мы описывали выше, позволяют находить дубликаты в определенном столбце. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных.

    Рассмотрим это на примере числовой матрицы. К сожалению, с символьными значениями этот метод не работает.

    При помощи формулы массива

    {=ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11);СТРОКА($1:$100)); НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ(НАИМЕНЬШИЙ(ЕСЛИ( СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11);СТРОКА($1:$100)); НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));»»);СТРОКА()-1))}

    вы можете получить упорядоченный по возрастанию список дубликатов. Для этого введите это выражение в нужную ячейку и нажмите Ctrl+Alt+Enter.

    Затем протащите маркер заполнения вниз на сколько это необходимо.

    Чтобы убрать сообщения об ошибке, когда дублирующиеся значения закончатся, можно использовать функцию ЕСЛИОШИБКА:

    =ЕСЛИОШИБКА(ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ( НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));»»);СТРОКА()-1));»»)

    Также обратите внимание, что приведенное выше выражение рассчитано на то, что оно будет записано во второй строке. Соответственно выше него будет одна пустая строка.

    Поэтому если вам нужно разместить его, к примеру, в ячейке K4, то выражение СТРОКА()-1 в конце замените на СТРОКА()-3.

    Обнаружение повторяющихся строк

    Мы рассмотрели, как обнаружить одинаковые данные в отдельных ячейках. А если нужно искать дубликаты-строки?

    Есть один метод, которым можно воспользоваться, если вам нужно просто выделить одинаковые строки, но не удалять их.

    Итак, имеются данные о товарах и заказчиках.

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

    Предположим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:

    =A2&B2&C2

    Добавим следующую формулу в ячейку E2. Она отобразит, сколько раз встречается значение, полученное нами в столбце D:

    =СЧЁТЕСЛИ(D:D;D2)

    Скопируем вниз для всех строк данных.

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

    Если вас не интересует определенный столбец, просто не включайте его в выражение, находящееся в D. Например, если вам хочется обнаружить совпадающие строки, не учитывая при этом значение Заказчик, уберите из объединяющей формулы упоминание о ячейке С2.

    Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel.

    Теперь рассмотрим, как можно обойтись без формул при поиске дубликатов в таблице. Быть может, кому-то этот метод покажется более удобным, нежели написание выражений Excel.

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

    В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.

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

    Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.

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

    Используем условное форматирование.

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

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

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

    Но здесь мы не можем исключить первые появления – подсвечивается всё.

    Но эту проблему можно решить, использовав формулу условного форматирования.

    =СЧЁТЕСЛИ($B$2:$B2; B2)>1

    Результат работы формулы выденения повторяющихся значений вы видите выше. Они выделены зелёным цветом.

    Чтобы освежить память, можете руководствоваться нашим материалом «Как изменить цвет ячейки в зависимости от значения».

    Поиск совпадений при помощи команды «Найти».

    Еще один простой, но не слишком технологичный способ – использование встроенного поиска.

    Зайдите на вкладку Главная и кликните «Найти и выделить». Откроется диалоговое окно, в котором можно ввести что угодно для поиска в таблице. Чтобы избежать опечаток, можете скопировать искомое прямо из списка данных.

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

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

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

    Как применить сводную таблицу для поиска дубликатов.

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

    Для более опытных – сразу переходим к сути вопроса.

    Создаем новый макет сводной таблицы. А затем в качестве строк и значений используем одно и то же поле. В нашем случае – «Товар». Поскольку название товара – это текст, то для подсчета таких значений Excel по умолчанию использует функцию СЧЕТ, то есть подсчитывает количество. А нам это и нужно. Если будет больше 1, значит, имеются дубликаты.

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

    К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:

    Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.

    Duplicate Remover — быстрый и эффективный способ найти дубликаты в Excel

    Теперь, когда вы знаете, как использовать формулы для поиска повторяющихся значений в Excel, позвольте мне продемонстрировать вам еще один быстрый, эффективный и без всяких формул способ: инструмент Duplicate Remover для Excel.

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

    Как найти повторяющиеся строки в Excel за 2 быстрых шага

    Сначала посмотрим в работе наиболее простой инструмент — быстрый поиск дубликатов Quick Dedupe. Используем уже знакомую нам таблицу, в которой мы выше искали дубликаты при помощи формул:

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

    1. Выберите любую ячейку в таблице и нажмите кнопку Quick Dedupe на ленте Excel. После установки пакета Ultimate Suite для Excel вы найдете её на вкладке Ablebits Data в группе Dedupe. Это наиболее простой инструмент для поиска дубликатов.

    1. Интеллектуальная надстройка возьмет всю таблицу и попросит вас указать следующие две вещи:
      • Выберите столбцы для проверки дубликатов (в данном примере это все 3 столбца – категория, товар и заказчик).
      • Выберите действие, которое нужно выполнить с дубликатами. Поскольку наша цель — выявить повторяющиеся строки, я выбрал «Выделить цветом».

    Помимо выделения цветом, вам доступен ряд других опций:

    • Удалить дубликаты
    • Выбрать дубликаты
    • Указать их в столбце статуса
    • Копировать дубликаты на новый лист
    • Переместить на новый лист

    Нажмите кнопку ОК и подождите несколько секунд. Готово! И никаких формул 😊.

    Как вы можете видеть на скриншоте ниже, все строки с одинаковыми значениями в первых 3 столбцах были обнаружены (первые вхождения не идентифицируются как дубликаты).

    Если вам нужны дополнительные возможности для работы с дубликатами и уникальными значениями, используйте мастер удаления дубликатов Duplicate Remover, который может найти дубликаты с первыми вхождениями или без них, а также уникальные значения. Подробные инструкции приведены ниже.

    Мастер удаления дубликатов — больше возможностей для поиска дубликатов в Excel.

    В зависимости от данных, с которыми вы работаете, вы можете не захотеть рассматривать первые экземпляры идентичных записей как дубликаты. Одно из возможных решений — использовать разные формулы для каждого сценария, как мы обсуждали в этой статье выше. Если же вы ищете быстрый, точный метод без формул, попробуйте мастер удаления дубликатов — Duplicate Remover. Несмотря на свое название, он не только умеет удалять дубликаты, но и производит с ними другие полезные действия, о чём мы далее поговорим подробнее. Также умеет находить уникальные значения.

    1. Выберите любую ячейку в таблице и нажмите кнопку Duplicate Remover на вкладке Ablebits Data. 

    1. Вам предложены 4 варианта проверки дубликатов в вашем листе Excel:
      • Дубликаты без первых вхождений повторяющихся записей.
      • Дубликаты с 1-м вхождением.
      • Уникальные записи.
      • Уникальные значения и 1-е повторяющиеся вхождения.

    В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:

    1. Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы возьмём первые 3 столбца:

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

    Поскольку цель этого примера – продемонстрировать различные способы определения дубликатов в Excel, давайте отметим параметр «Выделить цветом» (Highlight with color) и нажмите Готово.
    Мастеру Duplicate Remover требуется всего лишь несколько секунд, чтобы проверить вашу таблицу и показать результат:

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

    Никаких формул, никакого стресса, никаких ошибок — всегда быстрые и безупречные результаты :)

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

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

    Источник

    Как сложить и удалить дубли в Excel.

    ​Смотрите также​​Next​Михаил С.​​ местах они больше​,​ как это сделать.​Закрыть и загрузить​ элементы из второго​ один и применить​​ сложности.​ – этот способ​
    ​B1​, то связанные с​(Выделенная область содержит​ или функции СЦЕП.​
    ​ 12 в ячейке​
    ​Рассмотрим,​On Error Resume​: Без объединения (УФ)​​ не встречаются), так​​Sub Merge() Dim​ Да и объём​​и выбираем​
    ​ списка.​ потом к полученному​Немного изменим структуру исходной​Имеется отчет продаж в​ гораздо быстрее.​, нажимаем​ ними данные в​ несколько значений данных.​Выделите ячейку, в которую​ В2 основной таблицы​как сложить и удалить​ Next​webley​
    ​ же эти ячейки​ myTable As Range​ файла большой -​Закрыть и загрузить в…​Вложенные друг в друга​ набору инструмент​ таблицы:​ сети магазинов по​Урок подготовлен для Вас​Shift+Стрелка вправо​ столбце​ Объединение ячеек приведёт​​ вы хотите вставить​
    ​ на цифру 2.​ дубли в Excel​With Workbooks.Add.Worksheets(1)​: Можно так. Единственное,​
    ​ местами уже объединены.​ Dim address As​ макрос быстрее должен​ (Close&Load to…)​ две функции ЕСЛИОШИБКА​Удалить дубликаты​На этот раз нам​ целой стране. Нам​ командой сайта office-guru.ru​
    ​, чтобы добавить к​Full Name​ к потере всех​ объединенные данные.​ Получилось так.​
    ​. Как выделить дубли​.Range(«A1»).Resize(oDict.Count) = Application.Transpose(oDict.keys)​
    ​ последняя ячейка в​Так вот хотелось​ String Application.ScreenUpdating =​
    ​ справляться.​:​ реализуют вывод сначала​с вкладки​ необходимо объединить все​
    ​ нужно сделать таблицу​Источник: https://www.ablebits.com/office-addins-blog/2013/10/13/merge-columns-excel-without-losing-data/​
    Сложить и удалить дубли в Excel.​ выделению ячейку​также будут потеряны.​ значений, кроме левого​Введите = (знак равенства)​
    ​Как работать с таблицей​ в Excel, читайте​.Range(«B1»).Resize(oDict.Count) = Application.Transpose(oDict.items)​ таблице должна быть​ бы объединить эти​ False address =​UPD Важно чтобы​В следующем диалоговом окне​ уникальных из списка-1,​Данные (Data — Remove​ ячейки с одинаковыми​ данного отчета более​Перевел: Антон Андронов​C1​Теперь нам нужно превратить​ верхнего).​ и выберите первую​​ консолидации, как объединить​ в статье «Как​End With​ заполнена (можно руками​ значения в одну​ «A1:AI12» Set myTable​ цикл искал дубли​
    ​ (оно может выглядеть​
    ​ а потом из​ Duplicates)​ значениями в столбце​
    ​ читабельной. Для этого​Автор: Антон Андронов​. Затем нажимаем​ формулу в значение,​Далее в этой статье​ ячейку, которую нужно​ несколько таблиц в​ выделить повторяющиеся значения​On Error GoTo​
    ​ :))​ ячейку.​ = Range(address) For​ компании не по​ немного по-другому -​ списка-2 друг за​:​ «Штат» (B) в​
    ​ следует красиво сформатировать​YYYS​Ctrl+Shift+Стрелка вниз​
    ​ чтобы мы могли​ Вы найдёте 2​ объединить.​
    ​ одну консолидацией, читайте​ в Excel» тут.​ 0​Jon1242​
    ​Подскажите как это​ i = 2​ всему листу, а​ не пугайтесь) выбираем​ другом.​Само-собой, такой способ не​ столбце «№» (A).​ первый столбец, в​: Здравствуйте! Нужна помощь​​, чтобы выделить все​ удалить ненужные столбцы​ способа, как объединить​
    ​Введите символ​ в статье «Как​ Как удалить повторяющиеся​End Sub​: Уважаемый СЛЭН.​
    ​ можно реализовать.​ To myTable.Rows.Count ‘​ только подряд. Если​Только создать подключение (Only​Обратите внимание, что это​
    ​ подойдет, если данные​Если мы хотим, чтобы​ котором содержаться названия​
    ​ в объединении одинаковых​
    ​ ячейки с данными​ с нашего листа​ данные из нескольких​&​ объединить таблицы в​ значения, смотрите в​KuklP​А не можете​
    ​P.S. По поиску​ For j =​ встречается другая компания,​ create connection)​ формула массива, т.е.​ в исходных списках​ действия макроса распространялось​ штатов:​ ячеек, причем так​ в двух столбцах.​
    ​ Excel. Выделите все​ столбцов в один​

    ​и пробел, заключенный​ Excel».​ статье «Как удалить​: Кросспостинг:​ переделать макрос, чтобы​

    excel-office.ru

    Объединение текста из двух или нескольких ячеек в одну

    ​ к сожалению не​ i + 1​ тогда записывать объединённый​:​ после набора ее​

    Объединение данных с помощью символа «амперсанд» (&)

    1. ​ часто меняются -​ на несколько выделенных​Мы хотим объединить все​

    2. ​ чтобы строки тоже​Скопируйте данные в буфер​ ячейки с данными​ без потери данных​

    3. ​ в кавычки.​​Второй способ.​​ дубли в Excel».​Но загляните еще​

    4. ​ он также объединял​ нашел, хотя решение​ To myTable.Rows.Count If​ результат в Категорию​​Повторяем всю процедуру (пункты​​ нужно ввести в​

    Объединение данных с помощью функции СЦЕП

    1. ​ придется повторять всю​ столбцов, то делаем​ ячейки с одинаковыми​

    2. ​ объединялись пример прилагаю!​​ обмена (​​ в объединённом столбце​

    3. ​ и, не используя​Выберите следующую ячейку, которую​

      ​Сложить все дубли​Итак, нам нужно​ сюда:​ ячейки , только​ такой задачи наверняка​

    4. ​ myTable.Cells(i, 3) =​ и переходить к​ 2-6) для второго​ ячейку не обычным​​ процедуру после каждого​​ следующее. Сначала добавим​

    support.office.com

    Как объединить два столбца в Excel без потери данных

    ​ значениями в столбце​ Отблагодарю чем смогу)​Ctrl+C​ (выделите первую ячейку​ макрос VBA.​ нужно объединить, и​

    ​ с помощью формул.​ перед удалением дублей,​Hugo​ по строкам.​ обсуждалось.​ myTable.Cells(j, 3) And​ следующей компании. Это​ списка. При переименовании​ Enter, а сочетанием​ изменения заново.​ новую переменную:​ «Штат» (A). Это​SkyPro​или​ в столбце​Объединяем два столбца при​ нажмите клавишу ВВОД.​ Таблица та же.​

    Объединить два столбца в Excel

    ​ их сложить. Рассмотрим​: В той теме,​Заранее спасибо, просто​тухачевский​ myTable.Cells(i, 4) =​​ чтобы не грузить​​ заголовка столбца важно​​ клавиш Ctrl+Shift+Enter и​​Этот способ является, по​Dim j As Long​ можно реализовать с​: Это как? По​Ctrl+Ins​Full Name​ помощи формул​ Пример формулы:​

    Объединить два столбца в Excel

    ​Принцип такой. Создаем​ несколько способов.​

    • ​ откуда этот код​​ я оч. далек​: а для чего​ myTable.Cells(j, 4) And​ лишним поиском, так​​ использовать то же​ затем скопировать (протянуть)​ сути, логическим продолжением​Далее добавим строку с​ помощью ручного выделения​
    • ​ каким параметрам? в​– что предпочитаете).​​и затем нажмите​Объединяем столбцы при помощи​=A2&» «&B2​ дополнительный столбец, в​Первый способ.​ ( ) есть​​ от этого.​ это нужно?​ myTable.Cells(i, 15) =​ как файл изначально​ имя (Люди), что​ вниз на нижестоящие​

    Объединить два столбца в Excel

    ​ предыдущего. Если списки​ кодом начала нового​ отдельных групп одинаковых​ каких столбцах?​Запустите Блокнот:​Ctrl+Shift+Стрелка вниз​ Блокнота​.​

    • ​ ячейках которого будем​Самый простой способ​
    • ​ такой пост:​Guest​

    Объединяем два столбца при помощи формул

    ​я бы не​ myTable.Cells(j, 15) And​ отсортирован.​ и в предыдущем​ ячейки с запасом.​ не очень большого​ цикла, который будет​ значений и объединения​Цитата​Start​

    1. ​.Скопируйте содержимое в буфер​Допустим, у Вас есть​Выделите ячейку, в которую​ писать название дубля,​ – это использовать​я бы сделал​: Нужно так, строка​ советовал так делать​ myTable.Cells(i, 19) =​китин​​ запросе.​​В английской версии Excel​ размера и заранее​​ проходить по другим​​ их ячеек, воспользовавшись​Объединить два столбца в Excel
    2. ​YYYS, 12.10.2013 в​​(Пуск) >​​ обмена (​ таблица с данными​

      ​ вы хотите вставить​
      ​ который нужно сложить.​

      ​ функцию​​ так, чтоб не​​ одна Прикрепленные файлы​​слэн​ myTable.Cells(j, 19) Then​​: а вот походу​​В окне Excel на​​ эта формула выглядит​​ известно предельное количество​ столбцам выделенного диапазона:​ инструментом: «ГЛАВНАЯ»-«Выравнивание»-«Объединить и​ 18:08, в сообщении​All Programs​Ctrl+C​ о клиентах, и​ объединенные данные.​ Мы будем писать​«Консолидация» в Excel​ писать новый макрос:​

      Объединить два столбца в Excel

      ​ post_304320.PNG (3.78 КБ)​: что-то в этом​ myTable.Cells(i, 1) =​ вам в помощь​ вкладке​ как:​ элементов в них​For j = 1​ поместить в центре».​

      Объединить два столбца в Excel

    3. ​ № 1200?’200px’:»+(this.scrollHeight+5)+’px’);»>причем так​(Все программы) >​или​​ Вы хотите объединить​​Введите выражение​ дубли в ячейках​. У нас такая​1. левее сумм​Petro​ роде:​ myTable.Cells(i, 1) &​
    4. ​ Сцепитесли​Данные (Data)​=IFERROR(IFERROR(INDEX(Список1, MATCH(0, COUNTIF($E$1:E1, Список1),​ (например, не больше​ To Selection.Columns.Count​ Но если таблица​​ чтобы строки тоже​​Accessories​​Ctrl+Ins​​ столбцы с именем​= СЦЕП(​ С2, С3, С4,​​ таблица​​ создал столбец, куда​Объединить два столбца в Excel
    5. ​: Этот код я​Sub Макрос1()​ «, » &​или ТЫЦ​или на вкладке​ 0)), INDEX(Список2, MATCH(0,​ 10), то можно​После конца, ранее созданного​ содержит тысячи таких​ объединялисьВсю строку?​​(Стандартные) >​​– что предпочитаете),​​ (First Name) и​​.​ С5.​​.​​ собрал данные из​​ так понимаю объединяет​​’​ myTable.Cells(j, 1) myTable.Cells(i,​и вообще ПОИСК​Power Query​ COUNTIF($E$1:E1, Список2), 0))),​ объединить две таблицы​ (внутреннего) цикла добавляем​ групп, да еще​​Может вам нужно​​Notepad​ затем кликните правой​​ фамилией (Last Name)​​Выберите первую ячейку, которую​​А в следующем​​Нам нужно сложить все​Объединить два столбца в Excel
    6. ​ первых трёх через​​ строки в первом​​Dim i&, n&,​​ 8) = myTable.Cells(i,​​ РУЛИТ​выбираем​ «»)​
      • ​ в одну прямыми​​ инструкцию Next для​​ с разным количеством​ просто две таблицы​​(Блокнот)​​ кнопкой мыши по​ в один столбец​​ нужно объединить.​​ столбце напишем формулу,​
      • ​ числа в столбце​ разделитель формулой​​ столбце, а как​​ arr, rn&​​ 8) + myTable.Cells(j,​​о что получилось:​Получить данные — Объединить​​Минус у такого подхода​​ ссылками, добавить справа​​ конца нового (внешнего)​​ повторяющихся ячеек, тогда​ свести в одну?​​Вставьте данные из буфера​​ любой ячейке в​

    ​ с полным именем​Для разделения объединяемых ячеек​ которая будет искать​ В по каждому​=B1&»|»&C1&»|»&D1​​ сделать чтобы во​​n = Cells(Rows.Count,​

    Объединить два столбца в Excel

    ​ 8) myTable.Cells(i, 9)​Mikez​ запросы — Добавить​​ в том, что​ столбец с единичками​ цикла . И​

    Объединить два столбца в Excel

    Объединяем столбцы при помощи Блокнота

    ​ рационально написать макрос.​Если да, то​ обмена в Блокнот​ том же столбце​ (Full Name).​ используйте запятые, а​ дублирующие данные в​ дублю из столбца​

    ​это я в​ всех объединял или​ 1).End(xlUp).Row​ = myTable.Cells(i, 9)​: Не осилю. С​

    1. ​ (Get Data -​ формулы массива ощутимо​ и построить по​​ соответственно сделаем код​​ Он сам быстро​​ на форуме полно​​ (нажмите​ (Full Name) и​​Добавьте в таблицу новый​​ для добавления пробелов,​​ первом столбце и​​ А – отдельно​ А собирал, но​ в выделенном?​Объединить два столбца в Excel
    2. ​arr = Cells(1,​ + myTable.Cells(j, 9)​​ макросами у меня​​ Merge Queries -​​ замедляют работу с​​ получившейся таблице сводную:​
    3. ​ более читабельным с​​ и автоматически выполнит​​ подобных тем.​​Ctrl+V​​ в контекстном меню​​ столбец. Для этого​​ запятых и другого​​ складывать цифры по​​ все ячейки с​
    4. ​ не принципиально.​И еще для​ 1).Resize(n)​​ myTable.Cells(i, 10) =​​ плохо. А формулу​​ Append)​​ файлом, если в​Объединить два столбца в Excel
    5. ​Как известно, сводная​ помощью отступов табуляции.​ всю работу за​​YYYS​​или​ выберите​​ наведите указатель мыши​​ текста — кавычки.​​ дублям из второго​​ цифрой 1, отдельно​
    6. ​2. макросом получил​​ полного счастья -​​rn = 1​ myTable.Cells(i, 10) +​ использовать — не​:​​ исходных таблицах большое​​ таблица игнорирует повторы,​ Кроме того, для​​ Вас.​​: Это одна таблица​​Shift+Ins​​Paste Special​ на заголовок столбца​Введите закрывающую скобку в​​ столбца.​​ все ячейки с​ сводную типа​ когда он объединяет​For i =​​ myTable.Cells(j, 10) myTable.Cells(i,​​ то. Там в​В появившемся диалоговом окне​​ (несколько сотен и​​ поэтому на выходе​ всех экземпляров объекта​Объединить два столбца в Excel
    7. ​Откройте редактор Visual Basic​​ там где то​​)​(Специальная вставка). Отметьте​ (в нашем случае​​ конце формулы и​​В ячейке D2​ цифрой 2, т.д.​
    8. ​44550|225|40724 11,16​ выскакивает окно что​​ 2 To n​​ 14) = myTable.Cells(i,​​ итоговой таблице тысячи​​ выбираем наши запросы​ более) количество элементов.​ мы получим объединенный​Объединить два столбца в Excel
    9. ​ Cells во втором​​ (ALT+F11):​​ 800 наименований, в​​Скопируйте в буфер обмена​​ вариант​​ это столбец D),​​ нажмите клавишу ВВОД.​

    ​ пишем такую формулу.​ Мы не знаем,​3. Инструментом «текст​ объединение приведет к​If arr(i, 1)​ 14) + myTable.Cells(j,​ строк.​

    ​ из выпадающих списков:​Если в ваших исходных​
    ​ список без дубликатов.​
    ​ аргументе, вместо числа​

    ​И создайте новый модуль​

    office-guru.ru

    Как объединить повторные ячейки? (Формулы)

    ​ первой ячейке есть​​ символ табуляции. Для​Values​ кликните по нему​ Пример формулы:​ =СУММЕСЛИ(A2:A9;C2;B2:B9)​ сколько и какие​

    ​ по столбцам» опять​​ потере всех данных​ <> arr(i -​ 14) Range(myTable.Cells(j, 1),​
    ​Может кто за​​В итоге получим новый​ списках большое количество​ Вспомогательный столбец с​ 1 введем переменную​ с помощью инструмента:​
    ​ повторения (это id​ этого нажмите клавишу​(Значения) и нажмите​
    ​ правой кнопкой мыши​=CONCAT(A2, » Семья»)​В ячейку D3​

    ​ у нас дубли​​ получил столбцы.​ как от него​ 1, 1) Then​ myTable.Cells(j, myTable.Columns.Count)).Delete Shift:=xlUp​ плату сделает от​ запрос, где два​ элементов, например, по​ 1 нужен только​ j (например, Selection.Cells(i,​ «Insert»-«Module». А потом​ товара). Повторения из​Tab​ОК​ и в контекстном​.​ пишем такую формулу.​ в большой таблице.​Это и Вам​ избавиться?​With Range(Cells(rn, 1),​ j = j​ начала до конца?​ списка будут соединены​ несколько сотен или​

    ​ потому, что Excel​​ j)). Новая версия​

    ​ запишите в него​​ за того, что​
    ​в Блокноте, затем​.​
    ​ меню выберите пункт​

    ​Из этой короткой статьи​​ =СУММЕСЛИ(A3:A9;C3;B3:B9)​На новом листе​ подойдёт (чтоб не​Low​

    ​ Cells(i — 1,​​ — 1 End​китин​:)

    ​ друг под другом.​​ тысяч, то вместо​ умеет строить сводные​ измененного кода макроса​ VBA-код макроса:​

    ​ у товара ест​​ сочетание​Удалите столбцы​Insert​ Вы узнаете, как​По такому принципу​ делаем активной ячейку.​ писать новый макрос​: Всем доброго.​ 1))​ If Next j​: это в раздел​ Осталось удалить дубликаты​ медленной формулы массива​
    ​ по таблицам, содержащим,​ выглядит следующим образом:​

    excelworld.ru

    Макрос для объединения одинаковых ячеек в таблице Excel

    ​Sub JoinDoubles()​ разные размеры и​Ctrl+Shift+Стрелка влево​First Name​(Вставить). Давайте назовём​ в Excel объединить​ написать формулы в​

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

    ​ Мы, в нашем​ :) )​Есть файл вида​.HorizontalAlignment = xlGeneral​ Next i Application.ScreenUpdating​ фриланс.ждите модератора​ кнопкой​ лучше использовать принципиально​ по крайней мере,​Sub JoinDoubles()​

    Исходная таблица магазинов.

    ​Dim i As​ количество а ID​, а затем​и​ новый столбец​ несколько столбцов в​ других ячейках столбца​ примере, сделаем активной​-=85558=-​ адрес, дом, квартира,​.VerticalAlignment = xlTop​ = True End​я же вам​Удалить строки — Удалить​ другой подход, а​ два столбца.​Dim i As​ Long​ одинаковый. Мне нужно​Ctrl+X​Last Name​

    ​Full Name​ один, не потеряв​

    Откройте редактор.

    ​ D. Получилось так.​ ячейку Е2 на​Low​ сумма и тд.​.WrapText = False​

    ​ Subвот здесь адрес​
    ​ в файлике показал,​ дубликаты (Delete Rows​
    ​ именно - инструменты​
    ​При изменении исходных списков​ Long​Application.DisplayAlerts = False​
    ​ объединить строки с​.​, они нам больше​
    ​.​ при этом данные.​
    ​Эту же формулу можно​
    ​ этом же листе.​
    ​: Видимо я не​
    ​ в котором все​
    ​.Orientation = 0​
     Код в модуле.

    ​ твоей таблицы:​ что полулачается с​ — Delete Duplicates)​ надстройки Power Query.​ новые данные по​Dim j As​For i =​ одинаковым ID, и​

    Пример.

    ​Замените в Блокноте символы​ не нужны. Для​В ячейке​У Вас есть таблица​ написать, вызвав диалоговое​ На закладке «Данные»​ совсем ясно описал​ строки повторяются с​.AddIndent = False​address = «A1:AI12″​ этой UDF .​

    ​:​ Эта надстройка по​ прямым ссылкам попадут​ Long​ Selection.Rows.Count To 2​ чтобы ячейка размер​Tab​ этого:​D2​ в Excel, и​ окно функции «СУММЕСЛИ».​ в разделе «Работа​

    ​ проблему либо не​ разницей в поле​.IndentLevel = 0​а вообще лучше​ надо только выбрать​Готовый запрос можно переименовать​ умолчанию встроена в​ в объединенную таблицу,​Application.DisplayAlerts = False​ Step -1​

    ​ добавлялась и была​

    Как объединить все одинаковые ячейки в любой таблице

    ​на другие символы,​кликните по заголовку столбца​

    Новая структура исходной таблицы.

    ​запишите вот такую​ Вы хотите объединить​ Она расположена на​ с данными» нажимаем​ понимаю идею объединения​ сумма. Необходимо объединить​

    ​.ShrinkToFit = False​ преобразовать твою табличку​ уникальные​ справа на панели​ Excel 2016. Если​ но сводную придется​

    ​For j =​

    ​If Selection.Cells(i, 1)​ через запятую, а​ которые Вы хотите​B​ формулу:​

    ​ два столбца в​ закладке «Формулы» в​

    ​ на кнопку функции​ значений в один​ повторяющиеся строки между​.ReadingOrder = xlContext​ в табличку екселя​Mikez​ параметров, дав ему​ у вас Excel​ обновить уже вручную​ 1 To Selection.Columns.Count​ = Selection.Cells(i -​ строка количество суммировалась.​ использовать в качестве​, затем, нажав и​=CONCATENATE(B2,» «,C2)​ один построчно. Например,​ разделе «Библиотека функций»​

    ​ «Консолидация». Выйдет диалоговое​
    ​ столбец. Ваш макрос​ собой и сложить​
    ​.MergeCells = True​ и задать имя.​
    ​: Большое спасибо, Игорь.​
    ​ вменяемое имя (это​ 2010 или 2013,​
    ​ (правой кнопкой мыши​For i =​ 1, 1) Then​
    ​ В двух файлах​ разделителей.Для этого нажмите​ удерживая клавишу​
    ​=СЦЕПИТЬ(B2;" ";C2)​ необходимо объединить столбец​
    ​ нажимаем функцию «Математические»,​
    ​ окно.​
    ​ замечательно работает и​
    ​ значения полей сумма.​
    ​End With​
    ​ тогда нужно будет​

    ​ Но я глянул​ будет имя таблицы-результата​ то ее можно​ -​

    Объеденены все одинаковы ячейки.

    ​ Selection.Rows.Count To 2​Range(Selection.Cells(i — 1,​ я сделал пример​

    ​Ctrl+H​Ctrl​B2​ с именем и​ выбираем «СУММЕСЛИ». Заполним​

    exceltable.com

    Слияние двух списков без дубликатов

    ​Заполнили его так:​ суммирует значения в​ Нашел примерчик, но​rn = i​ эти строки:​ — там выше​ по факту) и​ отдельно скачать и​Обновить​ Step -1​ 1), Selection.Cells(i, 1)).Merge​ как было и​, чтобы открыть диалоговое​

    Слияние списков без дубликатов

    ​, кликните по заголовку​и​ столбец с фамилией​ диалоговое окно так.​В строке «Функция»​ нужной ячейке, но​

    Способ 1. Удаление дубликатов

    ​ увы, знаний как​End If​address = «A1:AI12″​ моего уровня. А​ все и можно​ установить (бесплатно).​). Если не нужен​​If Selection.Cells(i -​​End If​​ как должно быть!​ окно​​ столбца ​

    Удаление дубликатов в общем списке

    ​C2​ в один, или​В строке «Диапазон» указываем​ выбрали функцию «Сумма».​ моя проблема в​ его доработать под​Next​

    Способ 1а. Сводная таблица

    ​ Set myTable =​ чтобы дотянуться мне​ все выгружать на​Алгоритм действий следующий:​ пересчет «на лету»,​ 1, j) =​Next​ Если, кто поможет​Replace​C​– это адреса​ объединить несколько столбцов,​ диапазон столбца А​ Можно выбрать другие​ том, что кроме​

    Объединение сводной таблицей

    ​ свои нужды не​End Sub​ Range(address)заменить на:​ время надо, которого​ лист командой​Открываем отдельную вкладку установленной​ то лучше воспользоваться​ Selection.Cells(i, j) Then​Selection.VerticalAlignment = xlVAlignCenter​ в долгу не​(Заменить). В поле​.​

    ​ ячеек из столбцов​ таких как улица,​ с дублями.​ действия.​ столбца с не​ хватает. Главная проблема,​Eternal-Smoke​Set myTable =​​ нет. Да и​​Закрыть и загрузить (Close&Load)​ надстройки​ другими вариантами.​Range(Selection.Cells(i — 1,​

    Способ 2. Формула массива

    ​Application.DisplayAlerts = True​ останусь)​Find what​(другой способ) выберите любую​First Name​ город, индекс и​В строке «Критерий»​В строке «Ссылка»​ уникальными значениями и​ не могу понять​​: Спасибо, буду пробовать​​ Range(«MyTableName[#All]»)ну и конечно​​ смысла нет -​​:​​Power Query​​Можно решить проблему формулами.​​ j), Selection.Cells(i, j)).Merge​End Sub​​DV​

    Присвоение имен спискам

    ​(Что) вставьте из​ ячейку в столбце​и​

    Формула массива для слияния двух списков без дубликатов

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

    Формула массива в разборе

    ​End If​

    • ​Теперь если нам необходимо​: Формулами можно так:​ буфера обмена символ​B​Last Name​ столбец с адресом,​
      Уникальные из списка1
    • ​ С, в которой​ таблицы. У нас​ выводить остальные столбцы,​ бы после сравнения​
    • ​А нужно это,​ я задавал тоже​ раз в год​ изменениях или дополнениях​ Excel 2010-2013) или​ пересчет и обновление​Next​

    ​ объединить ячейки с​SkyPro​ табуляции. В поле​, нажмите​. Обратите внимание, что​ разделяя значения запятой,​ будем писать название​ указан фиксированный размер​ т.е. если в​ в новый файл​

    ​ чтоб перед печатью​ фиксированно, числами. т.о.​ попадаются.​

    ​ в исходных списках,​ просто идем на​ результатов будет происходить​Next​

    ​ одинаковыми значениями, то​: Или макросом:​Replace with​Ctrl+Space​ в формуле между​ чтобы в дальнейшем​ дубля.​ диапазона с абсолютными​

    Способ 3. Power Query

    ​ адресе есть повтор​ записывались все столбцы,​ всё симпатично выглядело,​ если ты поменяешь​Во «Фриланс» переносить​ достаточно будет лишь​ вкладку​ автоматически и мгновенно,​Selection.VerticalAlignment = xlVAlignCenter​ выделите диапазон A1:A18​ЗЫ: Использована функция​(Чем) введите Ваш​(Пробел), чтобы выделить​ ними стоит символ​ распечатывать адреса на​В строке «Диапазон​ ссылками и названием​ и суммируется значение​

    ​ а не только​

    1. ​ начальство это любит.​ местами или удалишь/добавишь​​ или удовлетворитесь уже​​ правой кнопкой мыши​Данные​ сразу после изменений​Application.DisplayAlerts = True​​ и запустите макрос​​ «сцепитьесли» с excel-vba.ru​ разделитель, например, пробел,​
    2. ​ весь столбец​ пробела, заключенный в​​ конвертах.​​ суммирования» указываем диапазон​ столбцов. Можно написать​ из полей «сумма»​ первый и последний​
      Вводим список в Power Query
    3. ​слэн​ новые колонки в​ имеющимися ответами?​ обновить таблицу результатов.​​(если у вас​​ в исходных списках.​End Sub​
    4. ​ выбрав инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«JoinDoubles»-«Выполнить».​Источник​​ запятую и т.д.​​B​ кавычки. Это разделитель,​​К сожалению, в Microsoft​​ столбца В, числа​ диапазон с относительными​ строк дубликатов, и​ из выделенного диапазона..​: Вот так еще​ середину — работать​Pelena​Mikez​ Excel 2016).​ Для удобства и​Чтобы увидеть результат действия​ Результат действия макроса​
    5. ​YYYS​ Нажмите кнопку​​, затем нажмите​​ который будет вставлен​​ Excel нет встроенного​ из ячеек которого​​ ссылками.​
      Сохраняем запрос
    6. ​ строка переносится в​Option Explicit​ лучше:​ тоже перестанет. Вариант​​: Не совсем так,​: Доброго дня.​​Выделяем первый список и​
      Создаем подключение
    7. ​ краткости давайте дадим​ новой версии кода,​ отображен на рисунке:​: Спасибо огромное Вам!​Replace All​Ctrl+Shift+Стрелка вправо​ между соединёнными частями​ инструмента для выполнения​
    8. ​ нужно сложить.​Подробнее о видах​​ одном экземпляре с​​’Option Compare Text​​n = Cells(Rows.Count,​​ решения — в​​ как Вы хотели​Помогите, пожалуйста, с​ жмем кнопку​ нашим спискам имена​ выделяем всю таблицу​​В начале кода мы​
      Объединяем запросы
    9. ​ С меня причитается)​(Заменить все), а​, чтобы добавить к​
      Параметры слияния
    10. ​ имени. В качестве​ таких задач. Конечно,​В строке формул​ ссылок читайте в​ новым значением поля​Sub UniqSummUniversal()​​ 1).End(xlUp).Row+1​ макросе пройтись по​ — результат получается​​ макросом начинающему.​
      Удаление дубликатов
    11. ​Из таблицы/диапазона (From Range/Table)​Список1​ и запускаем макрос:​ декларируем переменную для​ DV не вижу​ затем​ выделению столбец​ разделителя Вы можете​ есть команды​​ сразу видна получившаяся​​ статье «Относительные и​
      Результат

    ​ сумма.​Dim a(), oDict​Eternal-Smoke​ заголовкам таблицы и​ в последней ячейке​Во вложении образец​

    planetaexcel.ru

    Объединить дубликаты строк, сцепив значения одной колонки (Макросы/Sub)

    ​. На вопрос про​​и​
    ​Читайте также: как объединить​ хранения показателей счетчика​
    ​ твои поощрительные реквизиты))​Cancel​
    ​C​ использовать любой другой​Merge Cells​ формула.​ абсолютные ссылки в​KukLP, спасибо, пытаюсь​
    ​ As Object, i​: немного не так​ определить номера нужных.​
    ​ из группы, зато​
    ​ для экспериментов.​ создание из нашего​Список2​ одинаковые ячейки в​ цикла. В цикле​Serge_007​(Отмена), чтобы закрыть​.​ символ, например, запятую.​
    ​(Объединить ячейки),​Третий способ.​ Excel» здесь.​ разобраться.​ As Long, temp​ всё-равно получается.​
    ​ тогда будет работать​ формулами​Лист «Данные» содержит​
    ​ списка «умной таблицы»​, используя​ строках таблицы.​ проверяем значения соседних​: СцепитьЕсли есть и​ диалоговое окно.​
    ​После этого кликните правой​Точно так же Вы​Merge & Center​Создать таблицу с​Поставили галочки у​Hugo​ As String​макрос отлично работает​ максимально динамично.​Mikez​ компании. Одна компания​ — соглашаемся:​Диспетчер имен​Как видно на рисунке​

    ​ ячеек, начиная с​​ у нас​Нажмите​ кнопкой мыши по​
    ​ можете объединить данные​
    ​(Объединить и поместить​ фильтром. Внизу столбца​
    ​ слов:​

    ​: Смысл такой -​​Dim ind As​ на обычных ячейках,​namexls​: Вот это сила​ может быть представлена​Открывается окно редактора запросов,​на вкладке​
    ​ теперь макрос автоматически​ низа выделенного диапазона.​SkyPro​

    ​Ctrl+A​​ любому из выделенных​ из нескольких ячеек,​
    ​ в центре) и​ В установить автосумму.​«подписи верхней строки»​ объединяем данные строки​ Long​ но не объединяет​

    ​: Спасибо. Затестил, работает​​ мысли! ))) Спасибо!​ несколькими строчками. Отличаются​ где будет видно​Формулы (Formulas — Name​ объединяет одинаковые значения​ Если ячейка содержит​: Видел, но не​, чтобы выделить весь​ столбцов и в​ используя при этом​ прочие, но если​
    ​ Выбирать фильтром нужные​ – это название​ в одну ячейку,​

    ​’ind = InputBox(«Sum​​ уже объединенные ячейки​ все супер как​namexls​ строчки значением одной​ загруженные данные и​ Manager — Create)​:D

    ​ сразу в двух​​ такое же значение​ пробовал. А эта​

    excelworld.ru

    Объединить повторяющиеся строки excel с суммированием нужных столбцов

    ​ текст в Блокноте,​​ контекстном меню нажмите​ любой разделитель по​ Вы выберите 2​ дубли. Как сделать​ столбцов;​ по этому столбцу​ Column Number?»)​ ((​ надо, но только​: Здраствуйте, не могу​ ячейки.​ имя запроса​:​ столбцах.​ значит она будет​ на ядиске уже​ затем​Delete​ Вашему вкусу. Например,​ смежные ячейки с​ сводную таблицу, читайте​

    ​«значения левого столбца»​​ и суммам отрабатывает​​a = Selection.Value​​терпеть не могу​
    ​ на 5 строках,​ разобраться в коде​Всё отсортировано и​Таблица1​После именования, нужная нам​Классическая ситуация: у вас​ объединена.​ лежала в заначках.​Ctrl+C​(Удалить).​ Вы можете соединить​ целью объединить их,​ в статье «Сводные​ — это значит,​ макрос (а по​ind = UBound(a,​ эти объединения, подскажите​ как формулу заставить​ макроса, не в​ строчки одной компании​(можно поменять на​ формула будет выглядеть​ есть два списка,​Дополнительно в начале кода​DAV2014​, чтобы скопировать его​Отлично, мы объединили имена​ адреса из 3​ как показано на​ таблицы Excel».​ что искать дубли​ какому столбцу Вы​ 2)​ как реализовать?​ работать до самого​ моих силах. Помогите​ идут подряд.​ свое, если хотите).​ следующим образом:​ которые надо слить​ макроса устанавливаем свойство​: Здравствуйте! Нужна помощь​ в буфер обмена.​
    ​ из двух столбцов​
    ​ столбцов в один:​ снимке экрана ниже:​Но мы сделаем​ в левом первом​ определяете уникальные -​Set oDict =​
    ​Guest​ низа так как​ плиз с готовым​
    ​Задача:​Делаем двойной щелчок в​На первый взгляд выглядит​ в один. Причем​ «False» для объекта​ в объединении одинаковых​Вернитесь к листу Excel​ в один!​Скопируйте эту формулу во​Вам будет показано сообщение​ проще и быстрее.​ столбце.​ только по фамилии?),​ CreateObject(«Scripting.Dictionary»)​: сначала разобъедините весь​ строк очень много?​

    ​ решением. Пример файла​​1. Суммировать одну​ заголовок таблицы (слово​ жутковато, но, на​ в исходных списках​ «DisplayAlerts», чтобы предотвратить​ ячеек из поля​ (нажмите​Хоть это и​ все остальные ячейки​ с предупреждением:​ Выделяем таблицу, нажимаем​Нажимаем «ОК». Поучилось​ он выгружает такие​
    ​oDict.CompareMode = 1​ диапазон​ Столбцы местами меняться​

    CyberForum.ru

    Объединение ячеек с одинаковым содержанием

    ​ залил + там​​ колонку всех дублей:​Список1​ самом деле, все​

    ​ могут быть как​ появления предупреждающего сообщение​ ТОВАР, причем так​Alt+Tab​ потребовало немало усилий​ столбца​в Excel 2013:​ сочетание клавиш Ctrl​ так.​ объединённые данные и​For i =​
    ​Eternal-Smoke​ не будут и​ же лист как​ в первую строчку​
    ​) и переименовываем на​ не так страшно.​

    ​ уникальные элементы, так​ о попытке объединить​ чтобы из множества​), выделите только ячейку​ и времени.​

    ​Full Name​​Merging cells only keeps​ + T (английская​
    ​Если таблицу консолидации делаем​ суммы в новую​

    ​ 1 To UBound(a)​​: Если разъединить, то​ добавляться не будут​

    ​ должно получиться. Суть​
    ​ компании, если у​
    ​ любой другой (например​ Давайте я разложу​
    ​ и совпадающие (и​ непустые ячейки в​
    ​ одинаковых строк поля​B1​
    ​Этот способ быстрее, чем​
    ​. Пошаговую инструкцию как​ the upper-left cell​
    ​ на любой раскладке).​ на другом листе,​ книгу.​
    ​If IsNumeric(a(i, ind))​ получаться пустые ячейки,​ новые, добавляются только​
    ​ — нужно объеденить​
    ​ неё есть повторяющиеся​
    ​Люди​
    ​ эту формулу на​
    ​ между списками и​
    ​ программе Excel. В​
    ​ ТОВАР осталась всегда​
    ​и вставьте текст​
    ​ предыдущий, и не​
    ​ это делается Вы​
    ​ value, and discards​
    ​ Получилась такая сводная​
    ​ то получится такая​
    ​Теперь суммы копируем​

    ​ Then​​ которые в дальнейшем​ строки.​

    ​ повторяющиеся значения по​ строчки, в ячейку​). Каки именно назвать​ несколько строк, используя​

    ​ внутри), но на​​ конце выполнения кода​ одна строка с​
    ​ из буфера обмена​ требует создания формул.​

    ​ можете найти в​​ the other values​ таблица с фильтрами.​
    ​ таблица.​ правее (чтоб не​If Not IsEmpty(a(i,​ не попадут под​Все разобрался. Установил​
    ​ столбцу C,D,O,S, а​ «Категория» добавить категории​ — не важно,​

    ​ сочетание клавиш Alt+Enter​​ выходе нужно получить​ макроса обратно возвращаем​

    ​ уникальным значением, а​​ в таблицу.​ Но он подходит​ статье Как вставить​(В объединённой ячейке​ Внизу столбца В​

    ​Когда мы делали консолидацию​​ затёрлись), разбиваем объёдинённое​ ind)) Then​ объединение​ address = «A1:AI2500″​ по столбцам H,​ из строк-дублей. Через​ но придуманное название​ и отступы пробелами,​ список без дубликатов​ свойство «True» для​
    ​ в поле соответсвенно​

    ​Переименуйте столбец​​ только для объединения​

    ​ одинаковую формулу сразу​​ сохраняется только значение​ установили автосумму.​ на другом листе,​ назад по ячейкам.​temp = Trim(a(i,​

    ​Guest​​ и все.​
    ​ I, J, N​ разделитель |.​ нужно запомнить, т.к.​ как мы делали,​ (повторений):​
    ​ объекта «DisplayAlerts».​ Кол-во и Сумма​B​

    ​ смежных столбцов при​​ во все выбранные​ из верхней левой​Выберем фильтром дубли 1.​

    ​ то в диалоговом​​Если нужно всё​ 1))​: Это ваше умозаключение​Eternal-Smoke​ суммировать и по​2. Пометить строчки-дубли:​ его придется использовать​
    ​ например тут:​Давайте традиционно рассмотрим несколько​Обратите внимание! Если перед​ автоматически пересчитывались значения!!!​в​ использовании одинакового разделителя​ ячейки.​ ячейки диапазона. Остальные​

    planetaexcel.ru

    Объединение повторов и удаление дубликатов в excel

    ​ Получилось так.​​ окне консолидации в​
    ​ сделать макросом -​If Not oDict.Exists(temp)​ или «практический опыт»?​: Добрый день! я​ столбцу A произвести​ у всех строчек​ потом еще раз​Логика тут следующая:​ способов решения такой​ выполнением макроса выделить​ Исходный файл прилагаю!​Full Name​ между ними.​Итак, мы объединили имена​ значения будут потеряны).​Как написать данные одновременно​ строке «создавать связи​ можно попытаться записать​ Then​ Правила гласят, что​ снова к вам​ сцепление через запятую​ компании кроме первой​

    ​ при импорте второй​
    ​Формула ИНДЕКС(Список1;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список1); 0) выбирает​

    ​ распространенной задачи -​

    ​ более одного столбца,​Заранее благодарю за​и удалите столбец​Вот пример: мы хотим​
    ​ из двух столбцов​в Excel 2010 и​
    ​ в нескольких ячейках,​ с исходными данными»​
    ​ этот процесс рекордером​
    ​oDict.Add temp, CStr(a(i,​ желателен пример реальных​
    ​ :(​ без пробелов можно.​
    ​ в столбец I​
    ​ таблицы. Объединить две​ все уникальные элементы​
    ​ от примитивных «в​ то в результате​
    ​ помощь.​Last name​
    ​ объединить все те​ в один, но​
    ​ 2007:​ смотрите в статье​
    ​ поставили галочку. Теперь,​ :)​
    ​ ind))​
    ​ форматов данных в​Имеется большой массив​
    ​ Кол-во строк свыше​
    ​ прописать текст «удалить»​
    ​ таблицы в дальнейшем​
    ​ из первого списка.​

    ​ лоб» до более​ будут объединены одинаковые​
    ​В данном примере напишем​
    ​.​
    ​ же два столбца​
    ​ это по-прежнему формула.​
    ​The selection contains multiple​ «Как заполнить ячейки​

    ​ если в столбце​

    ​Low​​Else​

    ​ их реальной структуре…​ данных в одном​

    ​ 2х тысяч и​​ или «дубль».​ получится только если​ Как только они​ сложных, но изящных.​

    ​ значения только в​ код макроса, который​В описании этого способа​
    ​ с именем и​ Если мы удалим​ data values. Merging​ в Excel одновременно».​ В изменятся цифры,​
    ​: Hugo, спасибо огромное!))​
    ​oDict.Item(temp) = CStr(—oDict.Item(temp)​ А как вам​ столбце, эти данные​
    ​ периодически добавляются.​В картинках: Из​
    ​ заголовки их столбцов​
    ​ заканчиваются — начинает​Можно решить задачу самым​ первом столбце. Чтобы​

    ​ сможет автоматически найти​ больше шагов, чем​ фамилией в один.​ столбец​

    ​ into one cell​

    ​Данные из нескольких ячеек​​ то и в​ Вот что называется​ + a(i, ind))​ вариант объединить от​ повторяются только последовательно​hamin​ вот этого должно​ совпадают.​ выдавать ошибку #Н/Д:​ простым путем -​ расширить поле действия​ и объединить все​ в предыдущем варианте,​Выделяем оба столбца, которые​First name​ will keep the​ можно объединить в​ этой таблице данные​ «слона-то я и​End If​ непустой до непустой,​ (т.е. несколько одинаковых​:​ получиться вот это.​

    ​Разворачиваем выпадающий список в​Формула ИНДЕКС(Список2;ПОИСКПОЗ(0;СЧЁТЕСЛИ($E$1:E1;Список2); 0)) аналогичным​

    ​ руками скопировать элементы​​ макроса следует немного​ одинаковые ячейки в​ но поверьте мне​ необходимо объединить. Для​или​ upper-left most data​ одну с помощью​ пересчитаются автоматически. Проверяем​ не приметил»! Очень​End If​ предварительно разъединенных?..​ значений друг за​namexls​
    ​Формулами не знаю​ левом верхнем углу​ образом извлекает уникальные​ обоих списков в​
    ​ изменить его код.​ таблице Excel любой​ или проверьте сами​ этого выделяем ячейку​Last name​

    ​ only​​ символа «амперсанд» (&)​ – изменим цифру​ выручили, все работает))​End If​Z.​

    planetaexcel.ru

    ​ другом, в других​

    Источник

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

    Как выделить одинаковые ячейки группами

    Допустим мы имеем список поставщиков:

    Список поставщиков.

    Перед тем как найти повторяющиеся ячейки в Excel, отсортируем поставщиков по их идентификатору. Переходим в ячейку A2 и выбираем на закладке «ДАННЫЕ» в разделе «Сортировка и фильтр» инструмент «Сортировка от А до Я».

    Сортировка от А до Я.

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

    1. Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:».

      2. Создать правило.

    2. В поле для ввода формулы вводим следующе значение: =$A2<>$A3

      3. Формула.

    3. Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.

    Граница.

    В результате получаем эффект как отображено на рисунке.

    Готово.

    Нам удалось объединить и выделить повторяющиеся ячейки в Excel. Теперь в приходной накладной все отсортированные поставщики визуально разграничены линией друг от друга.

    

    Так как мы сортировали поставщиков по их идентификатору в формуле мы зафиксировали смешанной ссылкой столбец листа $A. Если значения в соседних ячейках столбца $A равные между собой тогда формула возвращает значения ЛОЖЬ и форматирование границе не применяется. Но если верхнее значение неравно (оператор <>) нижнему значению тогда формула возвращает значение ИСТИНА и применяется форматирования нижней границы целой строки (так как в смешанной ссылке номер строки не есть абсолютным, а является относительным адресом).

    Полезный совет! Если нужно разграничить данные не по первому столбцу таблицы, по любому другому, тогда соответственно отсортируйте и просто укажите адрес столбца. Например, разграничим по повторяющимся датам, а не по поставщикам. Для этого сначала сортируем данные по датам, а потом используем условное форматирование немного изменив формулу: =$C2<>$C3

    По датам.

    Теперь список сгруппирован по одинаковым датам.

    Источник

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

    И одной из распространенных задач было объединить данные таким образом, чтобы не было повторяющихся записей.

    Например, ниже представлен набор данных, содержащий несколько записей для одного и того же региона.

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

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

    Объединение и суммирование данных с помощью опции консолидации

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

    Другой метод — использовать сводную таблицу и суммировать данные (далее в этом руководстве).

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

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

    Ниже приведены шаги для этого:

    1. Скопируйте заголовки исходных данных и вставьте их туда, где вы хотите консолидировать данные.
    2. Выберите ячейку под крайним левым заголовком
    3. Перейдите на вкладку «Данные».
    4. В группе «Инструменты для работы с данными» щелкните значок «Консолидировать».
    5. В диалоговом окне «Консолидировать» выберите «Сумма» в раскрывающемся списке функций (если он еще не выбран по умолчанию).
    6. Щелкните значок выбора диапазона в поле «Ссылка».
    7. Выберите диапазон A2: B9 (данные без заголовков)
    8. Установите флажок в левом столбце.
    9. Нажмите ОК

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

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

    Я решил получить СУММУ значений из каждой записи. Вы также можете выбрать другие параметры, такие как «Счетчик» или «Среднее» или «Макс. / Мин.».

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

    Объедините и суммируйте данные с помощью сводных таблиц

    Сводная таблица — это швейцарский армейский нож для нарезки и нарезки данных в Excel.

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

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

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

    Ниже приведены шаги по созданию сводной таблицы:

    1. Выберите любую ячейку в наборе данных
    2. Щелкните вкладку Вставка
    3. В группе «Таблицы» выберите параметр «Сводная таблица».
    4. В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно.
    5. Щелкните существующий лист
    6. Выберите место, куда вы хотите вставить итоговую сводную таблицу.
    7. Нажмите ОК.

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

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

    Ниже приведены шаги для этого:

    1. Щелкните в любом месте области сводной таблицы, и откроется панель сводной таблицы справа.
    2. Перетащите поле Country в область Row.
    3. Перетащите и поместите поле «Продажи» в область «Значения».

    Вышеупомянутые шаги суммируют данные и дают вам сумму продаж по всем странам.

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

    Это также поможет вам уменьшить размер вашей книги Excel.

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

    Надеюсь, вы нашли этот урок полезным!

    Источник

    Понравилась статья? Поделить с друзьями:

    А вот еще интересные статьи:

  • Как сгруппировать документы word
  • Как сгруппировать диаграммы в excel в одну
  • Как сгруппировать данные в диаграмме excel
  • Как сгруппировать данные в excel по строкам
  • Как сгруппировать данные в excel по столбцам

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии