Remkomplekty.ru

IT Новости из мира ПК
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как объединить повторяющиеся ячейки в excel

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

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

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

Способ 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) :

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

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

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

    Сначала расскажем что такое объединенные ячейки в Excel и как их сделать. Тут нет ничего сложно, достаточно выделить две или более ячеек и выбрать команду на ленте Главная -> Выравнивание -> Объединить и поместить в центре.

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

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

    Способы объединения ячеек

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

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

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

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

    • Если диапазон содержит объединенные ячейки, то пользоваться сортировкой и фильтрацией в этом диапазоне будет невозможно.
    • Также невозможно будет преобразовать такой диапазон в таблицу (форматировать как таблицу).
    • Также можно забыть об автоматическом выравнивании ширины или высоты ячейки. Например если имеется объединенная ячейка A1:B1, то выравнять ширину столбца A уже не получится.
    • Если Вы пользуетесь горячими клавишами для навигации, например переходите в начало и конец таблицы путем сочетания клавиш Ctrl + стрелка вверх и вниз, то переход не удастся, и курсор будет «упираться» в объединенные ячейки.
    • Если вы выделяете столбцы (или строки) с помощью горячих клавиш Ctrl (Shift) +Пробел, то при наличии объединенных ячеек, вы не сможете выделить 1 столбец (или строку).

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

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

    1. Выделить все ячейки на листе. Это можно сделать путем нажатия сочетания клавиш Ctrl + A или кликнуть на черный треугольник между заголовками строк и столбцов на листе.
    2. Нажать на команду Главная ->Выравнивание ->Объединить и поместить в центре если она выделена. Если же она не выделена, значит выбранный лист не содержит объединенных ячеек.

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

    1. Откройте окно Найти и заменить. Сделать это можно сочетанием клавиш Ctrl + F.
    2. Поле Найти оставьте пустым и нажмите на кнопку Параметры.
    3. Должна отобразиться кнопка Формат. Нажмите левой кнопкой мыши по ней.
    4. В диалоговом окне Найти формат на вкладке Выравнивание выберите опцию Объединение ячеек. Далее нажмите Ok.
    5. В диалоговом окне Найти и заменить нажмите Найти далее или Найти все в зависимости от вашей дальнейшей задачи.

    Альтернативы использования объединенных ячеек

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

    1. Выделите диапазон ячеек, который хотите отцентрировать. При этом сам текст должен содержаться в левой верхней ячейке.
    2. Выберите команду формат ячеек или нажмите сочетание клавиш Ctrl + 1.
    3. В диалоговом окне Формат ячеек перейдите на вкладку Выравнивание.
    4. В выпадающем списке выравнивания по горизонтали выберите значение по центру выделения и нажмите OK.

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

    Все способы можно скачать нажав кнопку вначале статьи.

    Повторяющиеся строки в столбцах MS Excel и как с ними бороться

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

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

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

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

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

    ИТОГ: Данная функция предназначена для удаления записей, которые полностью дублируют строки в таблице.

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

    2. Расширенный (дополнительный) фильтр для удаления дубликатов

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

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

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

    3. Условное форматирование в Excel

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

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

    ИТОГ: Для больших массивов информации данный метод не дает четкой картины. Все фамилии авторов книг, у которых есть повторения, выделены одинаковым цветом.

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

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

    Не забудьте нажать на командную кнопку ОК и продолжить формирование сводной таблицы на новом листе. В окне Список полей сводной таблицы установите флажки во всех полях, так как указано на рисунке. При этом выбранные наименования полей появятся в окнах Название строк и å Значения. Перетащите поле п/п в столбец Названия строк.

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

    Материал подготовлен Л.А. Шутилиной, методистом ГМЦ ДОгМ

    Exceltip

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Как в Excel найти повторяющиеся и одинаковые значения

    При совместной работе с таблицами Excel или большом числе записей накапливаются дубли строк. Ста.

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

    Поиск одинаковых значений в Excel

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

    На рисунке – списки писателей. Алгоритм действий следующий:

    • Выбрать ячейку I3 с записью «С. А. Есенин».
    • Поставить задачу – выделить цветом ячейки с такими же записями.
    • Выделить область поисков.
    • Нажать вкладку «Главная».
    • Далее группа «Стили».
    • Затем «Условное форматирование»;
    • Нажать команду «Равно».

    • Появится диалоговое окно:

    • В левом поле указать ячейку с I2, в которой записано «С. А. Есенин».
    • В правом поле можно выбрать цвет шрифта.
    • Нажать «ОК».

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

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

    Ищем в таблицах Excel все повторяющиеся значения

    Отметим все неуникальные записи в выделенной области. Для этого нужно:

    • Зайти в группу «Стили».
    • Далее «Условное форматирование».
    • Теперь в выпадающем меню выбрать «Правила выделения ячеек».
    • Затем «Повторяющиеся значения».

    • Появится диалоговое окно:

    • Нажать «ОК».

    Программа ищет повторения во всех столбцах.

    Если в таблице много неуникальных записей, то информативность такого поиска сомнительна.

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

    Способ удаления неуникальных записей:

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

    В результате получаем список, в котором каждое имя фигурирует только один раз.

    Список с уникальными значениями:

    Расширенный фильтр: оставляем только уникальные записи

    Расширенный фильтр – это инструмент для получения упорядоченного списка с уникальными записями.

    • Выбрать вкладку «Данные».
    • Перейти в раздел «Сортировка и фильтр».
    • Нажать команду «Дополнительно»:

    • В появившемся диалоговом окне ставим флажок «Только уникальные записи».
    • Нажать «OK» – уникальный список готов.

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

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

    Пункт «Сводная таблица».

    В диалоговом окне выбрать размещение сводной таблицы на новом листе.

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

    Получаем упорядоченный список уникальных строк.

    Читать еще:  Vba excel текущий лист
  • Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector
    ×
    ×