Как сложить повторяющиеся строки в excel • как найти и выделить

Автоматическое заполнение ячеек датами

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

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

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

Автоматическое заполнение ячеек актуальными датами

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

Как работает формула для автоматической генерации уходящих месяцев?

На рисунке формула возвращает период уходящего времени начиная даты написания статьи: 17.09.2017. В первом аргументе в функции DATA – вложена формула, которая всегда возвращает текущий год на сегодняшнюю дату благодаря функциям: ГОД и СЕГОНЯ. Во втором аргументе указан номер месяца (-1). Отрицательное число значит, что нас интересует какой был месяц в прошлом времени. Пример условий для второго аргумента со значением:

• 1 – значит первый месяц (январь) в году указанном в первом аргументе;
• 0 – это 1 месяца назад;
• -1 – это 2 мес. назад от начала текущего года (то есть: 01.10.2016).

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

Далее перейдите в ячейку C1 и введите следующую формулу:

Как видно теперь функция ДАТА использует значение из ячейки B1 и увеличивает номер месяца на 1 по отношению к предыдущей ячейки. В результате получаем 1 – число следующего месяца.

Теперь скопируйте эту формулу из ячейки C1 в остальные заголовки столбцов диапазона D1:L1.

Выделите диапазон ячеек B1:L1 и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Формат ячеек» или просто нажмите комбинацию клавиш CTRL+1. В появившемся диалоговом окне, на вкладке «Число», в разделе «Числовые форматы:» выберите опцию «(все форматы)». В поле «Тип:» введите значение: МММ.ГГ (обязательно буквы в верхнем регистре). Благодаря этому мы получим укороченное отображение значения дат в заголовках регистра, что упростит визуальный анализ и сделает его более комфортным за счет лучшей читабельности.

Обратите внимание! При наступлении января месяца (D1), формула автоматически меняет в дате год на следующий

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

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

В данном примере будут выделены все ячейки в диапазоне B2:B5, числа в которых больше, чем число, записанное в ячейке C1. То есть вы сможете оперативно поменять число в C1, если решите изменить критерий выделения ячеек, и не придется менять само правило условного форматирования.

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

Для начала их нужно отобразить:
● откройте лист, с которым будете работать
● выделите все ячейки, выберите любую ячейку и нажмите сочетание клавиш CTRL+A (иногда CTRL+A потребуется нажать несколько раз), либо просто нажмите в левый верхний угол листа

● ячейки выделены, теперь откройте правила условного форматирования, либо правой кнопкой мыши на любой ячейке, пункт “условное форматирование” либо “формат” -> “условное форматирование” в панеле меню.

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

Для удаления — просто кликните на корзину.

Как найти в диапазоне дубли ячеек, включая первый

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

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

Как найти дубли ячеек, не учитывая лишние пробелы

Как сделать чтобы excel выделял повторы?
Теперь нужно настроить в этом окне, каким образом будет произведена фильтровка. Можно скопировать результаты фильтра в другое место (ставим галочку и указываем место, куда скопируется результат), либо результат оставить в том же месте. И, обязательно, ставим галочку «Только уникальные значения».

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

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

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

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

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

2. В меню редактора перейдите на вкладку «Данные».

3. В блоке «Работа с данными» нажмите кнопку «Удалить дубликаты».

4. В появившейся панели выберите столбцы, в которых нужно убрать одинаковые значения посредством установки меток. Если элементов много, используйте для быстрой настройки удаления опции «Выделить всё» и «Снять выделение». Нажмите «OK».

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

Совет! Повторы сразу после удаления можно восстановить, щёлкнув в левом верхнем углу Excel значок «стрелочку влево».

1. Кликните по редактируемой таблице.

2. На вкладке «Данные», в блоке «Сортировка и фильтр», щёлкните подраздел «Дополнительно».

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

1. В панели «Расширенный фильтр» клацните радиокнопку «Скопировать результат в другое место».

2. Нажмите кнопку, расположенную в правой части поля «Поместить результат в диапазон».

3. Клацните на свободном рабочем пространстве Excel, куда необходимо будет поместить отфильтрованную таблицу. После щелчка в поле появится код ячейки. Закройте его и перейдите в опции фильтра.

4. Кликните окошко «Только уникальные записи» и нажмите «OK».

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

Чтобы отредактировать документ без создания копий:

• в панели «Расширенный фильтр» установите режим обработки «Фильтровать список на месте»;
• кликом мышки включите надстройку «Только уникальные записи»;
• клацните «OK».

1. Кликните вкладку «Главная».

2. В разделе «Стили» откройте выпадающее подменю «Условное форматирование».

3. Откройте: Правила выделения ячеек → Повторяющиеся значения…

4. В ниспадающем меню «значения с» выберите наиболее удобный для вас вариант цветового подсвечивания повторов в таблице. Кликните «OK».

5. Удалите выборочно или группами обозначенные дубли через контекстное меню Excel (клик правой кнопкой → Удалить).

Комфортной вам работы в Excel!

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

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

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

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

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

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

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

При использовании этого инструмента имейте в виду следующие две вещи:

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

Управление правилами

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

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

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

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

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

Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Помогла ли вам эта статья?

Да Нет

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

К нам поступил вопрос:

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

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

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

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

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

Мы прописываем формулу:

 =И(B2>75;C2="Да") 

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

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

Не забудьте выбрать формат, в который необходимо закрашивать наши ячейки. Нажимаем «Ок» и проверяем.

Были закрашены Петров и Михайлов, у обоих результат выше 75 и они являются льготниками, что нам и требуется.

Надеюсь, что ответили на ваш вопрос по условному форматирования. Ставьте лайки и подписывайтесь на нашу группу в ВК.

Как в Excel удалить дубликаты данных

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

Поиск повторяющихся значений

​ убрать повторяющиеся элементы​ одну из них​ «Данные» в блоке​ список команд, из​Управление правилами​ полезно, когда в​ таблицы, и нажмите​, а затем в​ правило, чтобы изменить​, наведите указатель на​ или таблицу на​ убедитесь в том,​ ходе удаления повторяющихся​ кнопку​

​Nicko​Abram pupkin​ столбцу ни одна​ столбцов с помощью​ – «Дополнительно».​ операций «Работа с​ которых нас интересует​.​ данных содержится несколько​ кнопку​ поле​ условное форматирование, применяемое​ пункт​ другой лист или​ что активная ячейка​ значений они удаляются​ОК​: AleksSid, еще более​: У вас будет​ строка не будет​ формул, но этот​В открывшемся окне вводим​

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

Поиск уникальных значений

​ немного другая картинка,​ удалена, так как​ метод достаточно трудоемкий​ параметры выбора полей.​ на кнопку открывается​ ячеек».​Показать правила форматирования для​Выделите одну или несколько​.​введите ссылку на​ повторяющимся данным.​и выберите​Примечание:​На вкладке​

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

Удаление дубликатов

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

​ сохранять исходную таблицу,​ перед вызовом команды​ «Повторяющиеся значения». В​ или таблица.​ таблице или отчете​ том, сколько повторяющихся​Примечание:​ ячеек в диапазоне,​.​ если выделенные фрагмент​в группе​ в одной строке​повторяющиеся данные удаляются​Nicko​Данные — удалить​ «Цена» считаются разными.​ в современных версиях​

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

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

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

Отбор уникальных записей

​ — только уникальные?​: выделяем столбец​ другие средства для​Возможности функций авто-таблицы позволяют​ с ним не​ предлагает выделить столбцы,​ диапазона. После закрашивания​.​в разделе​ о том, что​ значения из выбранного​

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

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

Дополнительные рекомендации

​ОК​ структуру и промежуточные​Выполните одно из указанных​ в ячейке, а​ скопировать исходные данные​ есть возражения?!.​ «условное форматирование» -​ значениями. Например:​ устранять их дубликаты.​ результаты фильтра в​ найти и убрать​ фильтра можно отобрать​ нажмите кнопку​щелкните стрелку рядом​ значения не было​ при этом не​

​Стили​.​ итоги.​ ниже действий.​ не тем, которое​ на другой лист.​Pelena​

​ «правила выделения ячеек»​«Данные»-«Удалить дубликаты» — Инструмент​ Сразу стоит отметить,​

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

​Выделите диапазон ячеек или​

fb.ru>

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

​ что равно 2​: Еще вопрос, в​ не так делаю. ​ ячейки на дубль​ 100 столбцов на​: Спасибо. Я возможно​ последовательности до конца​ промежуточные итоги, необходимо​ убрать повторяющиеся значения,​ Excel, существует соответствующая​ список команд, из​ который уже существует​.​ есть еще 3-й​ дубликатов.​

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

​ Обо всем этом​Как выделить повторяющиеся значения​ но для вашей​ Вашем файле есть​ Все по образцу,​(B2=$E$13) проверка значения​ 100 строк спец​ плохо объяснил условие.​ таблицы. Для этого:​ убрать их перед​ и открываем в​

​ команда меню. Она​ которых нас интересует​ в списке, обе​Преобразовав обычный диапазон в​ пол), и этот​Хотите ли Вы, чтобы​ и другом читайте​ в Excel​ таблицы это первая​ пояснение «задаем столбцы»​ не работает. Что-то​ ячейки на условие​ вставка форматы ОК​

​ В каждой колонке​

1. ​​ тем, как удалять​ программе вкладку «Данные».​
2. ​ находится на вкладке​ пункт «Правила выделения​ ячейки будут выделяться​ таблицу, Ваш список​ набор значений определен.​ данные автоматически проверялись​ в статье «Как​
3. ​. В этой таблице​ колонка по этому​ и напротив цифры​ упускаю.​ по которому ищем​ ( но вначале​

​ должны искаться дубли​Выделите диапазон ячеек A2:C19​ дубликаты в столбце​ Здесь находим блок​ «Данные» в блоке​

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

Как сделать условное форматирование в Excel?

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

• Каким ячейкам вы хотите задать формат;
• По каким условиям будет присвоен формат.

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

В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

• Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
  • Правила выделения
  • Правила отбора первых и последних значений
  • Гистограммы
  • Цветовые шкалы
  • Наборы значков
• В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:

Также, доступны следующие условия:

1. Значения больше или равны какому-либо значению;
2. Выделять текст, содержащий определенные буквы или слова;
3. Выделять цветом дубликаты;
4. Выделять определенные даты.

Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

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

• Во всплывающем окне формата укажите:
  • цвет заливки
  • цвет шрифта
  • шрифт
  • границы ячеек

По завершении настроек нажмите кнопку “ОК”.

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

Как в MS Excel убрать дубликаты?

Вопрос может быть поставлен так:

• Как отфильтровать уникальные значения в Excel?
• Как удалить повторы в Excel?
• Как выделить одинаковые значения в Excel?

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

Удаление повторов. На вкладке «Данные» используйте функцию «Удалить» в разделе «Работа с данными»

Фильтр уникальных строк. На вкладке «Данные» используйте кнопку «Дополнительно» из раздела «Сортировка и фильтр».

Выделение уникальных значений. На вкладке «Главная» используйте команду «Условное форматирование» в разделе «Стиль»

1. УДАЛЕНИЕ СТРОК С ПОВТОРЯЮЩИМСЯ СОДЕРЖИМЫМ

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

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

На вкладке «Данные» нажмите кнопку «Удалить», которая находится в разделе «Работа с данными», как показано на рисунке ниже.

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

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

ПРИМЕЧАНИЕ: невозможность удалить строки с повторяющимся содержимым из диапазона, который содержит структурированные данные или промежуточные итоги. Для выполнения этой операции придется сначала удалить структуру документа также, как и промежуточные итоги.

2, ФИЛЬТР ЯЧЕЕК С УНИКАЛЬНЫМ СОДЕРЖИМЫМ

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

На вкладке «Данные» нажмите кнопку «Дополнительно», которая находится в разделе «Сортировка и фильтр», как показано на рисунке ниже.

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

Установите флаг «Только уникальные записи» и нажмите кнопку «ОК» для выполнения операции.

3. УСЛОВНОЕ ФОРМАТИРОВАНИЕ ЯЧЕЕК С УНИКАЛЬНЫМ СОДЕРЖИМЫМ

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

На вкладке «Главная» в разделе «Стили» нажмите кнопку в виде стрелки рядом с кнопкой «Условное форматирование» и в выпавшем списке меню «Правила выделения ячеек» — «Повторяющиеся значения», как показано на рисунке ниже.

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

Копирование условного форматирования с помощью специальной вставки

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

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

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

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

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

Это легко сделать!

Ниже приведены шаги по копированию условного форматирования из одной ячейки в другую:

1. Выберите ячейку B2
2. Щелкните правой кнопкой мыши и скопируйте его (или используйте сочетание клавиш Control + C)
3. Выберите весь диапазон, в который вы хотите скопировать условное форматирование (C2: C11 в этом примере)
4. Щелкните правой кнопкой мыши в любом месте выделения
5. Нажмите на опцию Специальная вставка. Это откроет диалоговое окно Специальная вставка.
6. В диалоговом окне Специальная вставка выберите Форматы.
7. Нажмите ОК.

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

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

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

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

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

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

Выделяем строки с одинаковыми наименованиями товаров (столбец B)

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

Здесь B2 — первая ячейка, а B17 — последняя, которую вы хотите проверить на наличие дублей. 

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

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

Другой способ удалить дубликаты в Excel — это разделение уникальных значений и копирование их на другой лист или в выбранный диапазон на текущем листе. Разберем этот способ.

1. Выберите диапазон или всю таблицу, которую вы хотите удалить дубликаты.
2. Перейдите во вкладку «ДАННЫЕ» —> группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно».

Удалить дубликаты в Excel – Использование дополнительного фильтра для удаления дубликатов

3. В диалоговом окне «Расширенный фильтр» выполните следующие действия:

• Выберите пункт «скопировать результат в другое место».
• Проверьте, отображается ли правильный диапазон в Исходном диапазоне. Это должен быть диапазон, выбранный на шаге 1.
• В поле Поместить результат в диапазон введите диапазон, в котором вы хотите скопировать уникальные значения (на самом деле достаточно выбрать верхнюю левую ячейку диапазона назначения).
• Выберите Только уникальные записи

Удалить дубликаты в Excel – Фильтр дубликатов

4. Наконец, нажмите «ОК». Excel удалит дубликаты и скопирует уникальные значения в новое указанное место:

Удалить дубликаты в Excel – Уникальные записи, скопированные из другого места

Таким образом вы получаете новые данные, на основе указанных, но с удаленными дубликатами.

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