Excel как найти одинаковые значения в столбце и Формула для выбора всех одинаковых значений в столбце Excel

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными “простыми, но полезными штуками при работе в Excel”. Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип “лучше сделать и жалеть, чем не сделать вовсе” возобладал.

Почему я посчитал, что это будет полезно

Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: “Ну всё, не зря время потратили”. Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).

Небольшое пояснение

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

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

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

F4 – повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

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

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь “Добавить”, обзываешь представление так, как тебе угодно – Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа – Другие команды:

В открывшемся окне в поле “Выбрать команды из:” выбираем “Все команды”. Потом находим “Представления” – Добавить:

Кстати, так можно добавить на панель быстрого абсолютно любую команду.

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter – готово.

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками “мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!” позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид – Окно – Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на “Мой файл:1” и “Мой файл:2”. А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы – в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать “Сохранить” хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

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

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши – в контекстном меню ищем “Специальная вставка”, либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с “пропускать пустые ячейки” – Ок. Профит!

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

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel – YouTube

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

• ​:​​ нажмите​​ чтобы выделить только​​ Highlight Cells Rules)​​ задача и годится​​ будет обновлен, чтобы​​ (ссылка на исходный​​ Дополнительное условие: при​​ значения в Excel,​
• ​ значения в Excel​ повторяющиеся или уникальные​​Как найти одинаковые значения​​ОК​ повторений удалите все​Повторяющиеся значения​ данные могут быть​​45​​=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.​

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

​ОК​​ те значения, которые​​ и выберите​ для небольших списков​ включить новое название​ список в столбце​ добавлении новых значений​ смотрите в статье​ и выделить их​

• ​ значения. Выбираем цвет​ в Excel​.​​ структуры и промежуточные​​.​ полезны, но иногда​12​Обратите внимание, что мы​.​
• ​ встречающиеся трижды:​​Повторяющиеся значения​​ 50-100 значений. Если​​3. Добавьте в исходный​​А​​ в исходный список,​​ «Как удалить дубли​ не только цветом,​ заливки ячейки или​.​​Рассмотрим,​ итоги из своих​В поле рядом с​ они усложняют понимание​6​​ создали абсолютную ссылку​​Результат: Excel выделил значения,​​Сперва удалите предыдущее правило​​(Duplicate Values).​​ динамический список не​
• ​ список название новой​​).​​ новый список должен​

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

​ в Excel».​​ но и словами,​​ цвет шрифта.​Например, число, фамилию,​к​​ данных.​ оператором​ данных. Используйте условное​8​ –​ встречающиеся трижды.​ условного форматирования.​Определите стиль форматирования и​ нужен, то можно​ компании еще раз​Скопируйте формулу вниз с​ автоматически включать только​Из исходной таблицы с​ числами, знаками. Можно​Подробнее смотрите в​ т.д. Как это​ак найти и выделить​
​На вкладке​
​значения с​ форматирование для поиска​​12​​$A$1:$C$10​Пояснение:​Выделите диапазон​ нажмите​ пойти другим путем:​
​ (в ячейку​
​ помощью Маркера заполнения​ повторяющиеся значения.​​ повторяющимися значениями отберем​ настроить таблицу так,​ статье «Выделить дату,​

​ сделать, смотрите в​ одинаковые значения в​Данные​выберите форматирование для​ и выделения повторяющихся​6​.​Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество​​A1:C10​ОК​ см. статью Отбор​А21​ (размерность списка значений​Список значений, которые повторяются,​ только те значения,​​ что дубли будут​ день недели в​ статье «Как выделить​ Excel.​нажмите кнопку​

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

​ ячейки в Excel».​Нам поможет условное​Удалить дубликаты​ значениям и нажмите​ вам просматривать повторения​Можно ли как-нибудь​Вы можете использовать​A1:C10​На вкладке​Результат: Excel выделил повторяющиеся​ помощью фильтра. ​ Кристалл)​ совпадать с размерностью​B​ Теперь при добавлении​ но и считаться.​ тут.​Второй способ.​ форматирование.Что такое условное​и в разделе​ кнопку​ и удалять их​ обозначить эти совпадения​ любую формулу, которая​, которые равны значению​Главная​ имена.​Этот пример научит вас​4. Список неповторяющихся значений​ исходного списка).​с помощью формулы​

Отбор повторяющихся значений в MS EXCEL

​ новых значений в​ Можно выделить повторяющиеся​Нажимаем «ОК». В столбце​Как выделить повторяющиеся значения​ форматирование и как​Столбцы​ОК​ по мере необходимости.​ с помощью условного​ вам нравится. Например,​

​ в ячейке A1.​​(Home) выберите команду​​Примечание:​ находить дубликаты в​ автоматически будет обновлен,​В файле примера также​ массива. (см. файл​ исходный список, новый​

Задача

​ значения с первого​ D выделились все​ в Excel​ с ним работать,​установите или снимите​.​Выберите ячейки, которые нужно​ форматирования? Или просто​ чтобы выделить значения,​

Решение

​Если СЧЕТЕСЛИ($A$1:$C$10;A1)=3, Excel форматирует​Условное форматирование​​Если в первом​​ Excel с помощью​ новое название будет​ приведены перечни, содержащие​

​ примера).​​ список будет автоматически​​ слова, а можно​​ года – 1960.​​. В этой таблице​​ смотрите в статье​

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

​Введем в ячейку​ содержать только те​ выделять дубли со​Можно в условном​ нам нужно выделить​ “Условное форматирование в​

​ в которых нужно​Удаление дубликатов​ повторений.​ и выделить в​

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

Тестируем

​ по этой ссылке,​5. Список повторяющихся значений​ уникальные значения.​​B5​​ значения, которые повторяются.​

​ второго и далее.​ форматировании тоже в​ год рождения 1960.​ Excel” здесь. Выделить​

​Повторяющиеся​ чтобы узнать, как​ (столбец B) автоматически​С помощью Условного форматирования​

​формулу массива:​Пусть в столбце​ Обо всем этом​ разделе «Правила выделенных​

​Выделяем столбец «Год​

Поиск дубликатов в Excel с помощью условного форматирования

​A1:C10​​СОВЕТ:​ значения.​);””)​​ повторяющимися значениями, например​​ найти повторяющиеся значения​​ этот текст (например,​​ «Стили» нажимаем кнопку​ так и в​ ценах, которые нужно​

​ повторяющихся данных рекомендуется​ отчета сводной таблицы.​ форматирование – Правила​ командой сайта office-guru.ru​A1:C10​ форматируемых ячеек​ выделит только уникальные​.​Созданный список повторяющихся значений​

• ​1. Добавьте в исходный​Вместо​
• ​ список с названиями​​ в Excel”. В​​ фамилию, цифру, др.),​
• ​(Use a formula​​ имена.​На вкладке​​ является динамическим, т.е.​ список название новой​ENTER​
• ​ компаний. В некоторых​​ таблице можно удалять​
  ​ и все ячейки​
• ​ компании (в ячейку​нужно нажать​
• ​А20​CTRL + SHIFT +​ имеются повторы.​​ строки по полному​​ выделятся цветом. Мы​

​ «Повторяющиеся значения».​​ поможет их скрыть,​в поле​ повторяющимися значениями, который​Условное форматирование​ столбце (или тоже​hatter​ ячейка​

​Введите следующую формулу:​
​ значения, встречающиеся трижды​

​Условное форматирование​ список, новый список​​введите ООО Кристалл)​​ ENTER​

​Создадим новый список, который​

Найти повторяющиеся значения в столбце (Формулы)

​Правила выделения ячеек​

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

Как объединить одинаковые строки одним цветом?

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

В результате выделились все строки, которые повторяются в таблице хотя-бы 1 раз.

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

Форматирование для строки будет применено только в том случаи если формула возвращает значения ИСТИНА. Принцип действия формулы следующий:

Первая функция =СЦЕПИТЬ() складывает в один ряд все символы из только одной строки таблицы. При определении условия форматирования все ссылки указываем на первую строку таблицы.

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

Вторая функция =СЦЕПИТЬ() по очереди сложить значение ячеек со всех выделенных строк.

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

Как только при сравнении совпадают одинаковые значения (находятся две и более одинаковых строк) это приводит к суммированию с помощью функции =СУММ() числа 1 указанного во втором аргументе функции =ЕСЛИ(). Функция СУММ позволяет сложить одинаковые строки в Excel.

Если строка встречается в таблице только один раз, то функция =СУММ() вернет значение 1, а целая формула возвращает – ЛОЖЬ (ведь 1 не является больше чем 1).

Если строка встречается в таблице 2 и более раза формула будет возвращать значение ИСТИНА и для проверяемой строки присвоится новый формат, указанный пользователем в параметрах правила (заливка ячеек зеленым цветом).

Как найти и выделить дни недели в датах?

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

Все транзакции, проводимые во вторник или в среду выделены цветом.

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

​ и удалять дублирующие​ он создает, условие,​ следующим образом:​Один вопрос -​: ОГРОМНОЕ ВСЕМ СПАСИБО​ должно получиться таким:​ сделать формулу, чтобы​ или новее –​ Для выбора цвета​ во вторник или​ для определения форматированных​ значений в столбце.​Загрузка надстройки мастера подстановок​ называемая начальной ячейкой).​ по вертикали по​ окно условного форматирования.​ можно поставить числа.​ данные, но и​ при котором функция​Дата всегда обязательно​

В этой статье

​ одна тема​ ЗА ОЧЕНЬ ОПЕРАТИВНУЮ​поле1 имя-файла поле3​

​ она искала одинаковые​Выделяем ячейки и​ выделения в окне​

​ в среду. Чтобы​ ячеек».​Как видно на рисунке​Нажмите кнопку​

​Формула​ приблизительному совпадению​ Выбираем функцию «Использовать​

​ Получится так.​ работать с ними​ будет учитывать знак​

​ равна, а потом​И прикладываем свой​ ПОМОЩЬ – НЕ​

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

​25 имя1 25​ значения в столбике​ на вкладке Главная​Условное форматирование​ облегчить себе поиск,​

Примеры функции ВПР

​В поле ввода введите​ с условным форматированием​Microsoft Office​

Примеры функций ИНДЕКС и ПОИСКПОЗ

​Поиск значений по вертикали​ формулу для определения​Этот способ подходит, если​ – посчитать дубли​ (пробела в конкретном​

​ нам удалось легко​, а затем —​ищет значение “Апельсины”​

​ в списке неизвестного​

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

​ форматируемых ячеек».​ данные в столбце​

​ перед удалением, обозначить​​ случае) для поиска​ какая либо из​Эта тема закрыта​

​18 имя3 18​Например:​ Правила выделения ячеек​

​и перейдите на​

Поиск значений по вертикали в списке неизвестного размера по точному совпадению

​ недели (вторник, среда).​ чтобы задать цвет​ поиск дубликатов в​

​Параметры Excel​​ Начальную ячейку не​ совпадению​ формулу для определения​ Или, после изменения,​ знаками, найти повторяющиеся​AlexM​ фамилия+отчество.​: Доброго всем вечера.​ и прикрутить к​11 имя2 11​323240 Одинак​

​ – Повторяющиеся значения​​ вкладку​ Для этого будем​ заливки для ячеек,​

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

​:​​Казанский​Возникла такая ситуация,​ своей задаче -​30 имя4 30​331548​В появившемся затем​Вид (Pattern)​ использовать условное форматирование.​ например – зеленый.​​ повторяющиеся данные ячеек​​Надстройки​

​ этот диапазон.​

Поиск значений в списке по горизонтали по точному совпадению

​ нескольких ячеек, т.д.​DaKakTak​​:​​ необходимо в большой​ позже отпишу​Заранее СПАСИБО за​

​394214​ окне можно задать​.​

​Выделите диапазон данных в​

Поиск значений в списке по горизонтали по приблизительному совпадению

​ И нажмите на​ для таблицы журнала​

​.​​1​ точному совпадению​ Устанавливаем формат, если​

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

​Усложним задачу. Допустим, нам​ таблице A2:B11 и​ всех открытых окнах​

Создание формулы подстановки с помощью мастера подстановок (только Excel 2007)

​В поле​​ — это количество столбцов,​Поиск значений в списке​ нужно выбрать другой​Как выделить повторяющиеся ячейки​ одинаковые значения в​ символ & означаеть​, файл-пример приложите (или​

​ записей), найти совпадения​: китин, не правильно​AlexM​321652​ цвет шрифта и​ нужно искать и​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​ кнопку ОК.​​Управление​ которое нужно отсчитать​ по горизонтали по​ цвет ячеек или​ в​ Excel условным форматированием,​

• ​ =Сцепить() и получается​
• ​ 2 файла в​​ строк со 2​​ мои формулы прикрутили​​: Что такое Поле​​azma​​ т. д. )​​ подсвечивать повторы не​
• ​ форматирование»-«Создать правило».​​В результате выделились все​​Принцип действия формулы для​выберите значение​​ справа от начальной​
• ​ приблизительному совпадению​​ шрифта.​ ​Excel.​ читайте в статье​​ условие для поиска​​ архиве).​​ таблицей(100-1000 записей), и​​FCSM​
• ​ 1 и Поле​​:​​Если Excel 2003​​ по одному столбцу,​​В появившемся окне «Создание​​ строки, которые повторяются​​ поиска дубликатов условным​
• ​Надстройки Excel​​ ячейки, чтобы получить​​Создание формулы подстановки с​Нажимаем «ОК». Все ячейки​​Установим формулу в​​ “Условное форматирование в​​ “пробелЗначение”, таким образом​​Формула массива (вводится​
• ​ пометить одинаковые строчки​

​: Попробовал прикрутить -​

Как найти одинаковые значения в столбце Excel

​ каким либо цветом​ не работает​Приложите к вопросу​Bill_Murray​Выделяем (для примера​ Например, имеется вот​ опцию: «Использовать формулу​ 1 раз.​ Формула содержит функцию​Перейти​ возвращается значение. В​ (только Excel 2007)​ окрасились.​ автоматически писались слова.​Рассмотрим, как выделить​ данном случае.​

​ фигурных скобках) для​ в большом документе.​(хотя в ваших​ файл пример, как​: azma, это было​ – диапазон А2:A10),​

​ такая таблица с​ для определения форматированных​

​​ =СЧЁТЕСЛИ(). Эту функцию​.​ этом примере значение​Для решения этой задачи​

• ​Можно было добавить​ большого файла Код​
• ​Просто такую манипуляцию​ файлах – все​ рекомендовано в правилах​ так просто!)​ и идем в​
• ​ ФИО в трех​ ячеек».​Форматирование для строки будет​ так же можно​В области​ возвращается из столбца​ можно использовать функцию​

​Можно в условном​Копируем формулу по​

​ У нас такая​ еще ” “&A1&”/”,​ =ИЛИ(ЕСЛИ(E2=Лист2!$E$2:$E$3;(B2=Лист2!$B$2:$B$3)*(C2=Лист2!$C$2:$C$3)+(C2=Лист2!$C$2:$C$3)*(D2=Лист2!$D$2:$D$3)+(B2=Лист2!$B$2:$B$3)*(D2=Лист2!$D$2:$D$3))) Здесь Лист2​ надо провести примерно​ получается).​ форума.​Bill_Murray​ меню Формат -​ колонках:​

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

​ применено только в​ использовать при поиске​Доступные надстройки​ D​ ВПР или сочетание​ форматировании установить белый​ столбцу. Получится так.​ таблица, в которой​ но в последнем​ – лист, в​ 60 раз, и​Посмотрите, пожалуйста, где​В файле покажите​: azma, спосебо!)))​ Условное форматирование. Выбираем​Задача все та же​ формулу:​ том случаи если​ одинаковых значений в​установите флажок рядом​Продажи​ функций ИНДЕКС и​ цвет заливки и​Обратите внимание​

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

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

Как найти одинаковые строки в Excel и выделить их цветом

​ ПОИСКПОЗ.​ шрифта. Получится так.​, что такое выделение​ с дублями.​ тогда не посчитает​ Вместо “3” везде​ бы нереально.​SkyPro​ не получается.​: там сделал маленькую​ вариант условия Формула​ ФИО, имея ввиду​ чтобы задать цвет​ ИСТИНА. Принцип действия​ функции первым аргументом​Мастер подстановок​К началу страницы​Дополнительные сведения см. в​Первые ячейки остались видны,​ дублей, выделяет словом​Дублирующие данные подкрасили условным​

​ его​ должен быть номер​Таблицы выглядят следующим​: 1. Уберите обьединение​FCSM​

• ​ поправку на случай​ и вводим такую​ совпадение сразу по​ заливки для ячеек,​ формулы следующий:​ указан просматриваемый диапазон​и нажмите кнопку​Для выполнения этой задачи​ разделе, посвященном функции​ а последующие повторы​
• ​ «Да» следующие повторы​ форматированием.​AlexM​ последней строки (мне​ образом:​
• ​ ячеек, если хотите​: Поле1 – условное​
• ​ если повтор будет​ проверку:​ всем трем столбцам​ например – зеленый.​Первая функция =СЦЕПИТЬ() складывает​ данных. Во втором​ОК​

​ используется функция ГПР.​ ВПР.​ не видны. При​ в ячейках, кроме​

​Есть два варианта​

​ изменении данных в​ первой ячейки.​ выделять ячейки с​ цифрами (значениями) должен​ 2 строки данных).​

​ был опыт работы​ это избавит вас​ данными.​ раза​

​Эта простая функция​ и отчества одновременно.​ всех открытых окнах​ все символы из​ что мы ищем.​Следуйте инструкциям мастера.​Функция ГПР выполняет поиск​=ИНДЕКС(нужно вернуть значение из​ первом столбце меняются​Слова в этой​ одинаковыми данными. Первый​

​ стоять пробел.​Jojojojo​ с макросами, но​ от других проблем)​Поле2 – условное​Bill_Murray​ ищет сколько раз​Самым простым решением будет​ кнопку ОК.​ только одной строки​ Первый аргумент у​К началу страницы​

​ по столбцу​ C2:C10, которое будет​ и пустые ячейки,​ формуле можно писать​ вариант, когда выделяются​И ОГРОМНОЕ спасибо.​:​ размышляя над этой​2. Формулу вводите​

​ название Столбца2 с​: azma, святый Господи​ содержимое текущей ячейки​ добавить дополнительный служебный​Все транзакции, проводимые во​ таблицы. При определении​ нас имеет абсолютные​Поиск дублей в Excel​Продажи​ соответствовать ПОИСКПОЗ(первое значение​

​ в зависимости от​ любые или числа,​ все ячейки с​ Впервые пришлось задавать​Казанский​ задачей даже не​ через нажатие ctrl​ данными.​ вы гений!)​ встречается в столбце​ столбец (его потом​ вторник или в​ условия форматирования все​ ссылки, так как​ – это одна​

• ​и возвращает значение​ “Капуста” в массиве​ того, где находятся​ знаки. Например, в​
• ​ одинаковыми данными. Например,​ вопрос. Обычно хватало​, Выслал архив в​ знаю как это​ + shift +​
• ​Поле3 – условное​Pelena​
• ​ А. Если это​ можно скрыть) с​ среду выделены цветом.​ ссылки указываем на​ он должен быть​ из самых распространенных​ из строки 5 в​

​ B2:B10))​ дубли.​ столбце E написали​

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

​ как в таблице​ хорошенько поискать. Но​ личку​ правильно сделать.​ enter (Формула массива,​ название Столбца3, который​:​ количество повторений больше​ текстовой функцией СЦЕПИТЬ​Допустим, что у нас​ первую строку таблицы.​ неизменным. А второй​

Способ 1. Если у вас Excel 2007 или новее

​ задач для любого​ указанном диапазоне.​Формула ищет в C2:C10​Пятый способ.​

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

Способ 2. Если у вас Excel 2003 и старше

​Как найти повторяющиеся строки​В столбце F​ А8). Второй вариант​ 4 дня мучался.​: Так и не​ идея что нужно​ говорили в каждом​​SkyPro​ ​, оформите формулу тегами​​ у элемента есть​​ ФИО в одну​ чего-либо (например, товаров),​​ ссылок в аргументах​​ меняться на адрес​ ее решения существует​

​ разделе, посвященном функции​

​ значению​ в​

Способ 3. Если много столбцов

​ что некоторые элементы​ распространять формулу на​ диапазона, потому имеет​ Но как быстро​К началу страницы​(B7), и возвращает​Нужно сравнить и​

​Идея.​ в одинаковыми данными.​ будет работать только​ когда нужно в​ документа на время​: Понял. СПАСИБО.​Формула массива.​ правки поста​

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

• ​ для конкретного примера​ одном документе сравнивать​ выполнения макроса на​​Объединение убирать мне​Если нужно отображать​azma​ в окне Условное​
• ​ к предыдущему способу.​ более 1 раза.​Вторая функция =СЦЕПИТЬ() по​​Самые быстрые и простые​​ в Excel и​​ используется функция ГПР.​ C7 (​ трем столбцам сразу.​​ использовать формулу из​ не выделять (выделить​​ и в других​ значения на Лист1​ 2 страницу книги​ нельзя. Попробую тогда​ только если совпадений​

​: хорошо!​ форматирование нажмите кнопку​ Для выделения совпадающих​ Хотелось бы видеть​​ очереди сложить значение​​ способы: найти дубликаты​ выделить их цветом?​

Поиск одинаковых строк в Excel

​Важно:​100​ У нас такая​ столбца E или​ только ячейку А8).​ случаях, при соблюдении​ А1 с массивом​ с основным документом​ копировать из объединенных​

​ так: ​​ работают внутри функции​ значений в диапазоне​

​ пример.​​В приведенном выше примере​ ИНДЕКС и ПОИСКПОЗ.​​ имена и отчества.​ есть дубли в​Как выделить повторяющиеся значения​ =(ДЛСТР(” “&$A$2)-ДЛСТР(ПОДСТАВИТЬ(” “&$A$2;”​ на листе 1​ возрастанию в обеих​​ обрабатывать.​: Skypro, спасибо за​​ формулу так, чтобы,​ то надо делать​​ правило форматирования, аналогичное​В последних версиях Excel​ =ЕСЛИ() где их​ с числом 1.​​Допустим мы занимаемся регистрацией​ функция ГПР ищет​К началу страницы​ Чтобы сравнить сразу​ столбце или нет.​ в​ “&A1;)))/(ДЛСТР(A1)+1) чтобы не​ в столбце I​ таблицах, а уже​​Pelena​​ совет, но ничего​ если клеточки пустые,​ доп. столб, где​ Способу 2. А​ начиная с 2007​ результаты сравниваются между​

Формула для поиска одинаковых значений (Формулы/Formulas)

​ То есть если​​ заказов, поступающих на​​ значение 11 000 в строке 3​Для выполнения этой задачи​ по трем столбцам,​ Например, создаем список​Excel.​ пропускалась 4.6, если​​ ставить заранее вводимое​​ после силами макроса​​: Снимите объединение –​​ не понял..​​ то формула оставляла​​ СЦЕПИТЬ данные​

​ именно:​​ года функция подсветки​​ собой. Это значит,​

​ больше чем одно​​ фирму через факс​ в указанном диапазоне.​

​ используется функция ВПР.​​ нужно соединить данные​

​ фамилий в столбце​​Нам нужно в​ с нее начинается​ число.​ проходить по датам​ скорректируйте формулу –​

​Сейчас присоединю файл​​ пустое поле, а​далее формат по​

​в Excel 2003 и​​ дубликатов является стандартной.​​ что в каждой​​ значение, значит формула​ и e-mail. Может​ Значение 11 000 отсутствует, поэтому​Важно:​

​ трех столбцов в​​ А. В столбце​

​ соседнем столбце напротив​​ строка​​Пробовал так же​ какой-то из таблиц,​ верните объединение –​ excel – может​ не значение “Одинак”?​ формуле и та​ старше – выбрать​

​Выделяем все ячейки с​​ ячейке выделенного диапазона​ возвращает значение ИСТЕНА​ сложиться такая ситуация,​

​ она ищет следующее​​  Значения в первой​​ одной ячейке. В​​ B установили формулу.​ данных ячеек написать​С предыдущей формулой​

​ же формула, только​​ в меню​
​ данными и на​

Найти одинаковые ячейки в столбце и. (Формулы)

​ со значениями всех​​ форматирование.​ поступил двумя каналами​ 10 543.​​В приведенном выше примере​ «СЦЕПИТЬ» в Excel.​ «+», значит такую​ ячеек с уникальными​​ 14, то ее​C++ =ЕЧИСЛО(ПОИСКПОЗ(A1;Лист2!A:A;0))но он​ совпадении выделять.​

​ – ОГРОМНОЕ СПАСИБО.​​ 2-ой и 4-ой​ много аргументов!(((​Алексей желтов​​ – Conditional Formatting​жмем кнопку​ строк таблицы.​​Достаточно часто рабочие таблицы​ входящей информации. Если​Дополнительные сведения см. в​

​ функция ВПР ищет​​ =СЦЕПИТЬ(A15;” “;B15;” “;C15)​​ фамилию уже написали.​​ данными написать слово​​ находит, а такого​ даже не пытается​Очень жду советов​Получилось. (копирую данные​​ строке. Анализируем ячейку​

​Che79​​: Попробуйте надстройку сравнение​ – Formula)​Условное форматирование (Conditional Formatting)​

​Как только при сравнении​ Excel содержат повторяющиеся​ зарегистрировать дважды один​​ разделе, посвященном функции​ имя первого учащегося​Про функцию «СЦЕПИТЬ»​Третий способ.​ «Нет».​ числа в строке​ рассматривать это как​ и помощи в​ из объединенных ячеек​ А2 (содержит -​​:​ диапазонов http://vba-excel.ru/examples/matching​в Excel 2007 и​, затем выбираем​ совпадают одинаковые значения​ записи, которые многократно​​ и тот же​ ГПР.​ с 6 пропусками в​ читайте в статье​Посчитать количество одинаковых значений​​В столбце А​ нет.​ формулу​ этой ситуации.​ во временные -​​ 25) и А4​Bill_Murray​Excel practic​

​ новее – нажать​​Правила выделения ячеек -​ (находятся две и​
​ дублируются. Но не​​ заказ, могут возникнуть​

​К началу страницы​​ диапазоне A2:B7. Учащихся​​ «Функция «СЦЕПИТЬ» в​Excel.​

​ устанавливаем фильтр. Как​​Поэтому и формула​

​: я бы сделал​ на вкладке​ Повторяющиеся значения (Highlight​ более одинаковых строк)​ всегда повторение свидетельствует​

​ определенные проблемы для​​Примечание:​ с​

​ Excel».​​Нам нужно не​ установить и работать​

​ получится Код =(ДЛСТР(“​: Чтоб рассматривал нужно:​:​​и там уже​ поэтому Е2=А2, Е4=А2.​

​ файле-примере, что Вы​​ так:​Главная (Home)​ Cell Rules -​ это приводит к​ об ошибке ввода​​ фирмы. Ниже рассмотрим​ Поддержка надстройки “Мастер подстановок”​6​Копируем формулу по​ только выделить повторы,​ с фильтром, смотрите​ “&$A$2&”/”)-ДЛСТР(ПОДСТАВИТЬ(” “&$A$2&”/”;” “&A1&”/”;)))/ДЛСТР(“​

​1. общий формат​​Jojojojo​​ их обрабатываю).​ (записываем в третий​ хотите получить​например, ваши данные:​кнопку​ Duplicate Values)​ суммированию с помощью​

​ данных. Иногда несколько​​ решение средствами условного​ в Excel 2010​ пропусками в таблице нет,​ столбцу. Теперь выделяем​

​ но и вести​​ в статье «Фильтр​ “&A1&”/”)Еще вариант укороченный,​​ ячейки​, во второй таблице​Pelena, спасибо, но​ столбец)​

​Che79​ первые два столбца​​Условное форматирование – Создать​:​ функции =СУММ() числа​ раз повторяющиеся записи​ форматирования.​

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

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

Excel, поиск одинаковых строк в 2 разных таблицах

​ (на картинке обведен​​ что маловероятно.Да. Так​​ 4.6/ 14.5/ 14.6/​​ покрасить видимые.​ подправил.а так чистейший​ 1 раз -​​FCSM​ конца первой таблицы.​ determine which cell​ список (в нашем​ раз, то функция​ себе работу с​ на товары:​ данных листа, содержащих​Алексей​Предположим, что требуется найти​Изменим данные в столбце​ красным цветом). Слово​

​ намного лучше. Я​​ 4.5/ 4.6/ 4.6).​​Jojojojo​​ копипаст​ в пятой строке​

​: Здравствуйте.​​Там, где будет​ to format)​ примере – диапазон​ =СУММ() вернет значение​ такими таблицами, рекомендуем​Чтобы проверить содержит ли​ названия строк и​.​ внутренний телефонный номер​ А для проверки.​ скопируется вниз по​ так и сделал.​Необходимо знать количество​:​​Pelena​ – поэтому Е5=А5​В excel-е, к​ не ошибка, а​​Затем ввести формулу проверки​ А2:A10), и идем​ 1, а целая​ автоматически объединить одинаковые​

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

Поиск одинаковых чисел в одной ячейке

​ повторений конкретных чисел​​Казанский​​: Откройте для себя​ (18)​ сожалению, полный чайник.​ МАС, и будут​​ количества совпадений и​ в меню​ формула возвращает –​ строки в таблице​ дубликаты, будем анализировать​ мастера подстановок можно​ разделе, посвященном функции​ идентификационному номеру или​

​Ещё один способ подсчета​​ заполненной ячейки таблицы.​ в конце каждого​ (если они есть).​, Большое спасибо, но​

​ кнопку на Главной​​”Имя4″ встречается только​ Помогите решить следующую​

​ повторяющиеся данные.​​ задать цвет с​Формат – Условное форматирование​ ЛОЖЬ (ведь 1​ Excel, выделив их​ по наименованиям клиентов​ найти остальные значения​ ВПР.​ узнать ставку комиссионного​ дублей описан в​Теперь в столбце​ значения должен присутствовать.​ При заданном условии​

​ в процессе тестов​​ вкладке​​ 1 раз -​​ задачу.​А если хотите​ помощью кнопки​(Format – Conditional Formatting)​ не является больше​ цветом.​ – столбец B:​​ в строке, если​К началу страницы​ вознаграждения, предусмотренную за​ статье “Как удалить​ A отфильтруем данные​ Я поторопился и​

​ поиска по 4.6,​​ выявилась проблема опечатков​Найти одинаковые ячейки​ в 7-ой строке​​Есть 2 столбца.​ вытащить какой именно​Формат (Format)​.​ чем 1).​Чтобы найти объединить и​

​Выделите диапазон B2:B9 и​​ известно значение в​Для выполнения этой задачи​ определенный объем продаж.​ повторяющиеся значения в​ – «Фильтр по​ забыл его прописать.​ эксель также посчитает​

​ в некоторых ячейках.​: Помогите найти одинаковые​ – поэтому Е7=А7​ (Поле1, Поле2)​ айпи стоит напротив​- все, как​​Выбираем из выпадающего​Если строка встречается в​ выделить одинаковые строки​ выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное​ одном столбце, и​ используются функции СМЕЩ​ Необходимые данные можно​​ Excel”.​ цвету ячейки». Можно​ Каюсь. Еще раз​ и 14.5. Как​ 1 список вручную​ ячейки . У​ (30).​Поле 1 содержит​ МАС во второй​ в Способе 2:​ списка вариант условия​ таблице 2 и​ в Excel следует​ форматирование»-«Создать правило».​ наоборот. В формулах,​ и ПОИСКПОЗ.​ быстро и эффективно​Как посчитать данные​ по цвету шрифта,​ спасибо вам.​ этого избежать?​

​ отрабатывал в течении​

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

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

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

Пример дневного журнала заказов на товары:

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

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

Принцип действия формулы для поиска дубликатов условным форматированием – прост. Формула содержит функцию =СЧЁТЕСЛИ(). Эту функцию так же можно использовать при поиске одинаковых значений в диапазоне ячеек. В функции первым аргументом указан просматриваемый диапазон данных. Во втором аргументе мы указываем что мы ищем. Первый аргумент у нас имеет абсолютные ссылки, так как он должен быть неизменным. А второй аргумент наоборот, должен меняться на адрес каждой ячейки просматриваемого диапазона, потому имеет относительную ссылку.

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

После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

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