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

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

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

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

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

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

Выделите спектр ячеек A2:C19 и изберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Сделать правило».
В показавшемся диалоговом окне выделите опцию: «Употреблять формулу для определения форматируемых ячеек», а в поле ввода введите последующую формулу: $C3:$C20)+0);2)’ >
Нажмите на клавишу «Формат» и на закладке заливка укажите зеленоватый цвет. И нажмите ОК на всех открытых окнах.

В итоге мы выделили целые строчки условным форматированием и получаем эффект как изображено на рисунке:

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

Поиск и удаление дубликатов в Excel: 5 способов

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

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

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

1-ый способ очень прост и подразумевает удаление дублированных строк с помощью специального инструмента на ленте вкладки “Данные”.

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

Способ 2: удаление повторений с помощью “умной таблицы”

Очередной метод удаления циклических строк – внедрение “умной таблицы“. Давайте разглядим метод пошагово.

Удаление дубликатов в умной таблице Excel

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

Способ 3: внедрение фильтра

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

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

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

Выделяем все ячейки нашей таблицы.
Во вкладке “Основная” кликаем по кнопочке “Условное форматирование“, которая находится в разделе инструментов “Стили“.
Раскроется список, в котором избираем группу “Правила выделения ячеек“, а снутри нее – пункт “Повторяющиеся значения“.
Окно опций форматирования оставляем без конфигураций. Единственный его параметр, который можно поменять в согласовании с своими цветовыми предпочтениями – это применяемая для заливки выделяемых строк цветовая схема. По готовности жмем клавишу ОК.
Сейчас все повторяющиеся ячейки в таблице “подсвечены”, и с ними можно работать – редактировать содержимое либо удалить строчки полностью хоть каким комфортным методом.

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

Способ 5: формула для удаления циклических строк

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

Формула, позволяющая находить пересечения в границах определенного столбца в общем виде смотрится так:

Давайте поглядим, как с ней работать на примере нашей таблицы:

Добавляем в конце таблицы новейший столбец, специально созданный для отображения циклических значений (дубликаты).
В верхнюю ячейку новейшего столбца (не считая шапки) вводим формулу, которая для данного определенного примера будет иметь вид ниже, и нажимаем Enter:
=ЕСЛИОШИБКА(ИНДЕКС(A2:A90;ПОИСКПОЗ(0;СЧЁТЕСЛИ(E1:$E$1;A2:A90)+ЕСЛИ(СЧЁТЕСЛИ(A2:A90;A2:A90)>1;0;1);0));"") .
Выделяем до конца новейший столбец для задвоенных данных, шапку при всем этом не трогаем. Дальше действуем строго по аннотации:
ставим курсор в конец строчки формул (необходимо убедиться, что это, вправду, конец строчки, потому что в неких вариантах длинноватая формула не помещается в границах одной строчки);
нажимаем служебную кнопку F2 на клавиатуре;
потом жмем сочетание кнопок Ctrl+SHIFT+Enter.
Эти деяния разрешают корректно заполнить формулой, содержащей ссылки на массивы, все ячейки столбца. Проверяем итог.

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

Интересно почитать: Как в excel отсортировать по дате

Заключение

Excel дает несколько инструментов для нахождения и удаления строк либо ячеек с схожими данными. Любой из обрисованных способов специфичен и имеет свои ограничения. К всепригодным варианту мы, пожалуй, отнесем внедрение “умной таблицы” и функции “Удалить дубликаты”. В целом, для выполнения поставленной задачки нужно управляться как чертами структуры таблицы, так и преследуемыми целями и видением конечного результата.

Как отыскать дубликаты в Excel & Numbers

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

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

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

Поиск дубликатов с внедрением условного форматирования

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

1-ый шаг — избрать все данные, которые мы желаем отыскать для дубликатов. Потом перейдите в раздел « Стили » вкладки « Основная » и перейдите к « Условное форматирование» > « Выделить правила ячеек» > « Дублировать значения» .

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

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

Поиск дубликатов при помощи COUNTIF

Время от времени для вас может потребоваться проверить наличие дубликатов на нескольких листах, для что требуется сочетание условного форматирования и формулы COUNTIF. Этот способ также будет работать с наиболее старенькыми версиями Excel и его аналогами для Mac — Numbers.

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

Мы собираемся применять COUNTIF, который употребляет синтаксис COUNTIF (спектр, аспекты). Для моего примера функция будет читать:

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

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

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

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

Выделите весь столбец, щелкнув его заголовок, потом перейдите на вкладку «Основная» и перейдите к « Условное форматирование» > « Правила выделения ячеек» > « Больше, чем» .

Введите 0 во всплывающем окне и занесите все нужные конфигурации в стили, используя раскрывающийся перечень. Нажмите OK, и вы должны узреть что-то вроде результатов ниже.

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

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

Идем далее с VBA

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

VBA является ответвлением от языка программирования Microsoft Visual Basic, который употребляется в пакете Office и за его пределами, но он в особенности эффективен при использовании в сочетании с Excel. VBA дозволяет юзерам создавать макросы, которые автоматизируют задачки, а в остальном делают больше с Excel.

Как разъяснялось выше, естественно, можно отыскать дубликаты в Excel, не изучая VBA. Тем не наименее, если для вас необходимо делать такие задачки на постоянной базе, может быть, для вас стоит о их выяснить. Это доступный язык, который быть может отлично применен как часть пакета Microsoft Office, потому проверьте его, если желаете расширить свои познания.

И помните, что поиск дубликатов — это всего только один сценарий, когда условное форматирование оказывается полезным. Вы также сможете применять его для выделения остальных типов данных в электрической таблице. Узнайте, как применять условное форматирование в Excel и условное выделение в Numbers на Mac .

Интересно почитать: В excel поставить галочку

Как в Excel отыскать повторяющиеся и схожие значения

1-ый способ очень прост и подразумевает удаление дублированных строк с помощью специального инструмента на ленте вкладки “Данные”.

На сто процентов выделяем все ячейки таблицы с данными, воспользовавшись, к примеру, зажатой левой клавишей мыши.
Во вкладке “Данные” в разделе инструментов “Работа с данными” находим клавишу “Удалить дубликаты” и кликаем на нее.

Если обрабатываемая таблица содержит шапку, то проверяем пункт “Мои данные содержат заглавия” – он должен быть отмечен галочкой.

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

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

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

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

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

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

Способ 2: удаление повторений с помощью “умной таблицы”

Очередной метод удаления циклических строк – внедрение “умной таблицы“. Давайте разглядим метод пошагово.

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

Способ 3: внедрение фильтра

Как обычно, выделяем все ячейки таблицы.
Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” отыскиваем клавишу “Фильтр” (иконка припоминает воронку) и кликаем на нее.

как и в прошлом методе, проверяем адресок спектр ячеек таблицы;

Подсветка поочередных дубликатов ячеек.

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

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

Чтоб выделить два идущих вереницей заказа 1-го и такого же менеджера:

Чтоб выделить 2-ой заказ менеджера, если у него 2 заказа идут попорядку: =$G1=$G2

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

при выделении лишь второго повтора

при выделении обоих дублей:

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

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

Смотрите также: “Пример использования функции ВПР в Эксель: пошаговая {инструкция}”

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

Как выделить повторы без 1-го вхождения

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

Изберите «Условное форматирование» > « Новое правило»> «Употреблять формулу», чтоб найти ячейки для форматирования .
В поле « Форматировать значения», где эта формула возвращает ИСТИНА , введите:

Где B2 — самая верхняя ячейка избранного спектра.

Нажмите клавишу «Формат» изберите подходящий цвет заливки и/либо шрифта.
В конце концов, нажмите клавишу ОК, чтоб сохранить и применить сделанное.

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

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

Способ 5: формула для удаления циклических строк

Формула, позволяющая находить пересечения в границах определенного столбца в общем виде смотрится так:

Давайте поглядим, как с ней работать на примере нашей таблицы:

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

Как подсветить дубликаты, используя интегрированный инструмент (с 1-м возникновением)

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

Изберите данные, которые вы желаете проверить. Это быть может колонка, строчка либо спектр ячеек.
На вкладке « Основная » изберите « Условное форматирование» > «Правила выделения ячеек» > «Повторяющиеся значения».

Раскроется диалоговое окно с форматом «Красная заливка» и «Мрачно-красный текст», избранными по дефлоту. Чтоб применить формат по дефлоту, просто нажмите ОК.

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

И вот что мы получили в итоге:

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

Запрос на предоставление документов

Запрос о предоставлении документов и инфы — это вид деловой переписки, официальное воззвание к юридическому лицу:

физического лица либо его представителя;
юридического лица либо его представителя.

Оформление и эталон рекомендованы ГОСТ Р 7.0.97-2016.

Когда пишется письмо о предоставлении инфы либо документов

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

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

Письмо-запрос — документ, имеющий юридическую силу, потому оно:

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

сведения из Одного муниципального реестра юридических лиц (ЕГРЮЛ);
о дисквалифицированных по решению суда лицах;
сведения о обладателях объектов недвижимости, земляных участках и строениях;
сведения из информационной системы обеспечения градостроительной деятельности.

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

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

Кто и по какой форме составляет

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

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

Кто обычно готовит воззвание:

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

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

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

Более буквально и много описаны требования и сформулировано, какие пункты содержит бланк запроса, в ГОСТ Р 7.0.97-2016 (дата актуализации ‎01.06.2019).

Эталоны

Переписка с исполнительными органами гос власти подчиняется общим требованиям.

Для коммерческого документооборота понадобится эталон письма-запроса документов у контрагента в случае их утраты и необходимости получения дубликатов:

ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ «ФИРМА»

Волгоградский просп., д. 18, Москва, 110213, Тел. (321) 321-11-22; тел./факс (495) 321-01-02 ОКПО 02112332; ОГРН 1022000230321; ИНН/КПП (Контрольно-пропускной пункт — пункт, предназначенный для контроля за проходом (посещением) и пропуска на территорию какого-либо объекта) 1103021120/338022002

Генеральному директору ООО «Холод»

Холодная ул., 9

Почетаемый Сергей Сергеевич!

ООО «Компания» закупало у вас кондюки модели LG P09EP в количестве 150 шт. в течение марта — июня 2020 г. Расчеты произведены стопроцентно.

В связи с неожиданными обстоятельствами (пожар в кабинете) нами утрачены:

Подлинник контракта поставки №123 от 23.02.2020.
Акты приема-передачи №123-1, №123-2, №123-3.

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

Заместитель директора ООО «Компания» Р. Р. Разумов

Как оформить и кому выслать

В согласовании с ГОСТ Р 7.0.97-2016 лучший метод, как написать официальный запрос о предоставлении документов, смотрится последующим образом:

о предприятии: почтовый и электрический адреса, банковские реквизиты, номера контактных телефонов;
о гражданине: прописка, адресок проживания, номер телефона, электрический адресок.

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

Сроки ответа

Срок ответа госорганов на запросы регламентируется ФЗ-8 от 09.02.2009 (ред. 28.12.2017). Он составляет:

30 дней со денька регистрации;
45 дней, если для подготовки ответа требуется больше времени.

Нужно уведомить обратившегося о увеличении срока (с указанием предпосылки отсрочки) в течение 7 дней со денька регистрации запроса (ч. 6 ст. 18 ФЗ-8).