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

Поиск и удаление дубликатов в Microsoft Excel

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

Поиск и удаление

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

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

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

Выделяем весь табличный диапазон. Переходим во вкладку «Данные». Жмем на кнопку «Удалить дубликаты». Она располагается на ленте в блоке инструментов «Работа с данными».

Способ 2: удаление дубликатов в «умной таблице»

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

1. Выделяем весь табличный диапазон.
2. Находясь во вкладке «Главная» жмем на кнопку «Форматировать как таблицу», расположенную на ленте в блоке инструментов «Стили». В появившемся списке выбираем любой понравившийся стиль.
3. Затем открывается небольшое окошко, в котором нужно подтвердить выбранный диапазон для формирования «умной таблицы». Если вы выделили все правильно, то можно подтверждать, если допустили ошибку, то в этом окне следует исправить. Важно также обратить внимание на то, чтобы около параметра «Таблица с заголовками» стояла галочка. Если её нет, то следует поставить. После того, как все настройки завершены, жмите на кнопку «OK». «Умная таблица» создана.

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

Способ 3: применение сортировки

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

1. Выделяем таблицу. Переходим во вкладку «Данные». Жмем на кнопку «Фильтр», расположенную в блоке настроек «Сортировка и фильтр».
2. Фильтр включен, о чем говорят появившиеся пиктограммы в виде перевернутых треугольников в названиях столбцов. Теперь нам нужно его настроить. Кликаем по кнопке «Дополнительно», расположенной рядом все в той же группе инструментов «Сортировка и фильтр».
3. Открывается окно расширенного фильтра. Устанавливаем в нем галочку напротив параметра «Только уникальные записи». Все остальные настройки оставляем по умолчанию. После этого кликаем по кнопке «OK».

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

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

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

1. Выделяем область таблицы. Находясь во вкладке «Главная», жмем на кнопку «Условное форматирование», расположенную в блоке настроек «Стили». В появившемся меню последовательно переходим по пунктам «Правила выделения» и «Повторяющиеся значения…».
2. Открывается окно настройки форматирования. Первый параметр в нём оставляем без изменения – «Повторяющиеся». А вот в параметре выделения можно, как оставить настройки по умолчанию, так и выбрать любой подходящий для вас цвет, после этого жмем на кнопку «OK».

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

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

Способ 5: применение формулы

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

=ЕСЛИОШИБКА(ИНДЕКС(адрес_столбца;ПОИСКПОЗ(0;СЧЁТЕСЛИ(адрес_шапки_столбца_дубликатов: адрес_шапки_столбца_дубликатов (абсолютный); адрес_столбца;)+ЕСЛИ(СЧЁТЕСЛИ(адрес_столбца;; адрес_столбца;)>1;0;1);0));»»)

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

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

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

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

Поиск и удаление повторяющихся значений в Excel ⁠ ⁠

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

Давайте разберемся, как это сделать быстро.

Инструмент Удаление дубликатов

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

2. Выберите на вкладке Данные ►Удалить дубликаты:

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

В результате список сократится до уникальных значений:

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

1. Скопируйте данные или CTRL+С

2. Вкладка меню Главная ► Вставить ► Специальная вставка ► Траспонировать или вызов окна CTRL+ALT+V

Для корректной работы инструмента с полями формата Дата убедитесь, чтобы все поля имели одинаковый формат даты, например 01.01.2020

Удаление дубликатов при помощи Расширенного фильтра

Операцию выше можно сделать при помощи инструмента Расширенный фильтр.

Актуально, если у вас версия Excel 2003, в которой еще нет инструмента Удалить дубликаты.

1. Выделите шапку таблицы или всю таблицу (CTRL+A), в которой необходимо найти дубликаты.

2. Выберите на закладке Данные ► в разделе Сортировка и фильтр Дополнительно:

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

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

5. Поставьте галку Только уникальные записи:

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

Вам необходимо выделить уникальные значения в исходной таблице?

Скройте повторяющиеся значения при помощи Расширенного фильтра:

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

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

Поиск дубликатов при помощи Сводной таблицы

1. Добавьте в вашу таблицу дополнительное поле для проверки:

2. Создайте Сводную таблицу, вкладка Вставка ► Сводная таблица.

3. Выберите вариант отображения отчета, на вкладке Конструктор ► Макет отчета ► Показать в табличной форме.

4. Уберите промежуточные итоги Конструктор ► Промежуточные итоги:

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

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

2. На вкладке Главная ► Условное форматирование ► Правила выделения ячеек ► Повторяющиеся значения:

3. В окне Повторяющиеся значения, при необходимости выберите формат выделения дубликатов:

Данные будут подсвечены:

Как удалить дубликаты и разбить текст по столбцам, смотрите видео ⬇⬇⬇

661 пост 14.8K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

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

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

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Условное форматирование и дальнейшая работа с ним при больших списках (от

150т.строк) — жесть. Просто вешает программу и никак далее не работает, программа в таком состоянии спокойно может висеть и ночь и сутки (пробовал).
Я предполагаю, что для этого нужен мощный компьютер, но мой 4 ядерный i5 с 16 гб оперативки, 8 гб видео-памяти и ssd диском не справляется с таблицей всего-то 180 т. строк.

Поэтому условное форматирование при работе с большими таблицами — неработоспособное решение.

Раз пошло такое дело)) формула счетслимн: диапазон 1 и условие 1; диапазон 2 и условие если дата меньше даты указанной. Суть проблемы: если в условие прописываешь руками «<(дата)» То все считает, если пишешь <(адрес ячейки с датой) то ошибка. Что я упускаю))? Диапазон поиска и ячейка с датой одного формата

Очень круто, большое спасибо!!

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

Размер разный: 18 Нина, 208,50 Елена и тд

Как быстро вытащить только числа?

Читать ещё на Пикабу

Таблицы в MS Office, МойОфис, Р7 (он же ONLYOFFICE), LibreOffice⁠ ⁠

Маленькое сравнение работы MS Office, МойОфис, Р7 (он же ONLYOFFICE), LibreOffice.

MS Office 2016. Исходный файл создавался в данной программе

МойОфис Сборка 54 Версия 2.3 Н (надеюсь правильно переписал)

Р7-Офис. Профессиональный (десктопная версия) 7.3.0.159 (x64 exe)

LibreOffice 7.5.3.2 (Х86_64)

Р7-Офис — платный, ONLYOFFICE. Поведение одинаковое.

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

Сравнение работы видно на скринах.

LibreOffice — лучший результат, МойОфис — худший результат

2. Форматирование ячеек

Р7-Офис. — лучший результат, МойОфис — худший результат

3. Построение диаграмм

LibreOffice — лучший результат, МойОфис — худший результат

Р7-Офис — есть проблемы с отображением и редактированием.

LibreOffice — единственный выдал верный результат,

МойОфис — не посчитал, и честно об этом сказал (типа радуйтесь)

Р7-Офис — якобы посчитал, но при смене исходных данных подобен поведению МойОфис

5. Отдельной необъятной (и пока особо не известной) темой стоит программирование под Р7 и МойОфис. Помолчу о встроенных оболочках (о мёртвых или хорошо или ничего. Редактор VBA это верх совершенства по сравнению с ними), но вот пример самого кода одного и того же действия с официальной страницы программы (ONLYOFFICE) показывает всю «прелесть» «отечественного» продукта.

Про написание такого макроса (поиска первой пустой ячейки снизу столбца А) на Lua (МойОфис) я не скажу, ибо вэбинар «методология обучения основам Lua, которая позволяет быстро научиться созданию макросов в редакторах МойОфис.» буквально взорвал мой мозг количеством воды и отсутствием конкретики. Впрочем желающие могут погрузиться на канал МойОфис и постигнуть всё и вся. Дерзайте.

О программировании под LibreOffice, точнее возможности частичного использования макросов на VBA, писал ранее.

Так что решайте сами надо ли оно вам. Я просто хотел чуть чуть сравнить работу.

УПД И почему взяты эти три альтернативы? Потому что МойОфис, AlterOffice (тот же LO, но платный и с закрытойскачкой) и Р7 входит в единый реестр российского ПО (желающие смотрим тут ) , кроме того МойОфис имеет сертификат ФСТЭК т.е. по сути является единственным вариантом для гос.учреждений с секретностью Ж(

Обоснуй⁠ ⁠

Кадровики, в чем смысл накопления трудовых в ОК?⁠ ⁠

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

Смысл этого нам неясен, вопросов, как вы помните, задавать нельзя.

Быть может, кто-то объяснит, в чем выгода собирания трудовых книжек?

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1⁠ ⁠

Друзья, всем привет.

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

Важное примечание!

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

1 — Объединение ячеек.

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

Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.

Как сделать лучше.

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

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) — правая кнопка мыши — Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание — по горизонтали — по центру выделения:

К остальным заголовкам можно уже применить «Формат по образцу» (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.

2 — «Правильная» таблица

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

Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.

Как сделать лучше.

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

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

3 — «Правильные» даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить «29 марта 2023 г.». Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что «тупой иксель» не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

«-» — дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.

4 — Цветовое оформление таблицы.

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

А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я — молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная — Стили — Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку «Таблица с заголовками» — Ок. Всего 4 (. ) нажатия на кнопку мыши и готово. В этих наших интернетах это называется «умная» таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. «Умная» таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 — Ручное закрашивание данных

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

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но.

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим «1». Выделяем весь диапазон, где хотим что-то закрашивать. Главная — Стили — Условное форматирование — Правила выделения ячеек — Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке «Заливка» выбираем нужные нам цвета. А вот самое интересное происходит на вкладке «Число». Выбираем «(все форматы)», в поле «Тип» прописываем следующий формат «;;;» (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет «1», но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с «умной» таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции⁠ ⁠

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

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

Что же, давайте приступим.

Есть столбец с текстом:

Нам необходимо в отдельные столбцы разнести код и наименование.

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

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

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) — подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»)) — подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;» «;»*»;E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

ПСТР (MID) — как много в этом слове. А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 — понятно, ячейка с текстом.

D2+1 — начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 — количество извлекаемых символов. Порядковый номер звёздочки — порядковый номер первого пробела — 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

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

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

Здесь уже расписывать не буду, а то ещё одна «простыня» получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

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

Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Мгновенное заполнение в Excel — магия в чистом виде⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg

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

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

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

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

Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

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

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

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

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

Извлечение только чисел из столбца

Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:

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

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:

С номером поступаем аналогично.

Сбор текста

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

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

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube

Вы недооформились! (Снова о кадровиках)⁠ ⁠

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

День на третий видит меня кадровичка в коридоре и издалека на всю контору кричит:

К: VanyaDW, подождите, подойдите ко мне! Вы недооформились!

Я, естественно, в непонятках, спрашиваю, что это значит?

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

Я: Что значит «я не предоставил»?

А меня разозлило так.

Я: Вы серьезно думаете, что мне дали бланк, а я его принципиально заполнять не стал? Чтобы предприятие мне зарплату не могло заплатить? Может быть, это вы просто предоставленный документ потеряли?

К: Ой, ну может быть, сотрудница просто не приложила! Вы только так не нервничайте!

Нравится мне этот подход: не «Мы — курицы, ваш документ потеряли, извините. «, а «Вы недооформились!».

Таблица подсчета розеток/выключателей/рамок. ⁠ ⁠

Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:

Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)

Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамке

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

ЗЫ Отдельное спасибо @XaXa3Pa3a

Полезные трюки при работе в Excel⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

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

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

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

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

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

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

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

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

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

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

Представления

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

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

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

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

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

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

ПРЕДУПРЕЖДЕНИЕ!

Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).

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

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

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

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

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

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

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

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

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

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

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

Ответ на пост «Кадровики-формалисты на страже уровня зарплаты»⁠ ⁠

Год назад уволился по сокращению отдела, бюджетная организация. 21 год проработал. Уволился с радостью, 40 с лишним офицального трудового стажа, из них 15 лет Крайнего Севера. Пенсионер МВД с 1998 г. Никуда не устроился и не собираюсь. Что бы там не пели терешковы и им подобные, ранняя заработанная неплохая пенсия — это прекрасно.
При увольнении кадры сделали всё правильно, но в бухгалтерии молодая расчетчица насчитала выплаты по сокращению в полтора раза меньше, чем положено по ТК. Устные претензии бухгалтерия не приняла — «программа так считает».
Ранее я писал жалобы своему руководителю про график работы, оплату сверхурочных, несвоевременную выплату ночных. Вопросы решались положительно для меня. Написал и на этот раз, выплатили всё как положено.
Недавно звонок от приятеля, там работает. В ходе разговора сказал, что зам. (тот вообще под 30 лет там проработал) вспоминал меня «добрым» словом, мол, был такой редиска-жалобщик, и уволить его до сокращения никак не получалось, и премии лишить никак.
Мне было приятно.

Если человек не подошёл к нам в компанию, так , что , он не человек?⁠ ⁠

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

Ответ Faruga в «Хобби при собеседовании»⁠ ⁠

А я как-то проходила собеседование в одну фирму. Уже пообщалась с двумя руководителями, определили, что меня надо в другой отдел, пообщалась с руководителем этого отдела. В конце решили направить меня к генеральному директору, тоже пообщаться познакомиться. Не помню что он у меня спрашивал, как-то просто непринуждённо поговорили, а потом он говорит что всегда задаёт соискателям один вопрос: кубический корень из 27. Я конечно ответила) Работа если что не тесно связана с математикой. Видимо так он проверяет насколько человек свой. В эту компанию меня взяли, но я в итоге осталась на предыдущей работе, потому что когда там узнали, что я ухожу, смогли уговорить остаться.

Скрипт для экселя- ссылка в тайминг⁠ ⁠

Всем добрый день.
Хочу обратиться к Пикабу-разуму в вопросе Офиса.

Для размещения поста на Пикабу имеется заранее подготовленная таблица в Эксель.
первая колонка- тайминг новости, вторая колонка — тело новости, третья колонка — ссылка на новость, что является обязательным в теге «политика».
в сильно упрощенном виде это выглядит банально:

До недавних нововведений в редакторе постов Пикабу, CSV сохраненный из Экселя втаскивался в редактор через скрипт в обозревателе. И получался хорошо известный некоторым вариант

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

Для интересующихся — в обычном финальном Пузыре 60-90 новостей.
Вставлять руками можно, но ЛЛ меня и так гнобит

Ответ на пост «Работничек»⁠ ⁠

Дают задачу для доработки бекенда. Там простой фикс, говорили они. Думаешь, сейчас сделаешь за 20 минут и готово, но там:

1. Название задачи «Нужно починить сайт»
2. Есть сваггер, последнее обновление год назад
3. Схема запросов, ответов и описание никто не актуализировал
4. Описание задачи максимально абстрактное, очевидно, писал менеджер
5. Разработчик, который писал код, уволился полгода назад
6. Менеджер ушёл в отпуск
7. Дедлайн через пару дней в пятницу
8. У тебя испытательный срок

Работничек⁠ ⁠

EXCEL — ЭТИ СТРАШНЫЕ МАКРОСЫ – НАЧАЛО⁠ ⁠

Я решил с двух ног ворваться в тему макросов.

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

Первым делом нужно включить вкладку «Разработчик». По умолчанию в Excel ее спрятали, чтобы не взорвать мозг юзерам. Идем в Параметры -> Настройка ленты -> Основные вкладки -> Разработчик (поставить галочку).

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

Давайте теперь что-то сделаем. На пример поменяем заливку ячейки А1, в ячейку A2 напишем значение «Мама, я программист», а в ячейке А3 пропишем формулу текущей даты «=Сегодня()»

Останавливаем запись макроса. Нажимаем иконку «Макросы», выбираем наш макрос как мы его обозвали, нажимаем кнопку «изменить».

Появляется окно Microsoft Visual Basic for Applications. Кстати оно также вызывается комбинацией клавиш (Alt + F11) У меня почему-то вызывается только левым Altом, а правым нет, видимо намекая на то что для написания макросов лучше иметь 2 руки (хотя я и одной нажать могу). Появился редактор языка VBA – это язык, который написан специально под офис чтобы на нем писать макросы. В основном окне видим саму эту запись, которую автоматически сделал Excel.

Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(«A2»).Select
ActiveCell.FormulaR1C1 = «Мама, я программист»
Range(«A3»).Select
ActiveCell.FormulaR1C1 = «=TODAY()»
Range(«A4»).Select
End Sub

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

Теперь давайте разбираться что делает этот макрос

Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With

(Весь этот кусок от начала говорит нам о том, что с тем элементом что был выделен ранее происходит некоторое дерьмо, в том числе изменение цвета. Вот там, где Color = 255. Все остальное это параметры заливки, которые по итогу не менялись, но макрорекордер решил их тоже записать, на всякий. Это связано с внутренними особенностями работы excel как я понял. Вообще привыкайте к тому что макрорекордер пишет много того что потом вообще можно удалить. Конструкция With – End With позволяет делать несколько действий с одним объектом, на пример выше берется объект Selection.Interior, то есть фон выбранной области и ряду параметров этой заливки назначаются конкретные значения. То есть With нужен для облегчения записи кода, чтобы Selection.Interior не писать вначале каждой строчки.

Range(«A2»).Select –выделяем ячейку «A2»
ActiveCell.FormulaR1C1 = «Мама, я программист» – пишем в ячейку значение
Range(«A3»).Select – выделяем ячейку «А3»
ActiveCell.FormulaR1C1 = «=TODAY()» –пишем в ячейку формулу
Range(«A4»).Select – зачем то выделяем ячейку А4.
End Sub

Теперь при создании нового листа и запуске этого макроса он будет делать все то же самое.

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

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

Range(“A2”).Value = ”Мама, я программист”

или писать формулу как в третей ячейке

С формулами и значениями лично мне не понятно, как excel их интерпретирует, но в макрорекордре он записывает любой ввод в ячейку как ввод формулы. Благо лично у меня при написании макросов не возникает необходимости писать формулы в ячейки. На пример вместо вставки формулы как это было выше можно написать Range(“A3”).Value = Date(), тогда макрос вставит сразу текущую дату в ячейку как значение.

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

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

Sub Colorization()
‘начало нашего макроса и его название
Dim x As Integer
‘объявляем переменную х типа интеджер, это тип для целых чисел от -32 768 до 32 767 (2 байта),
‘она нам нужна для перебора ячеек
For x = 1 To ActiveSheet.UsedRange.Rows.Count
‘перебираем х от 1 до конца использованной части листа, то есть не весь лист, а там где есть данные.
‘Тут цикл For повторяется от этой строки до строки Next x, которая прописана ниже
If Cells(x, 1).Value = «красный» Then Cells(x, 1).Interior.Color = RGB(255, 0, 0)
‘если значение в ячейке равно «красный» то закрашиваем ячейку в красный цвет. Функция If выполняет часть
‘после Then если условие между If и Then верно. Так как у нас необходимое действие занимает одну
‘строку можно писать в таком виде, если же действий несколько применяется конструкция:
‘If … Then
‘…
‘…
‘End If
If Cells(x, 1).Value = «зеленый» Then Cells(x, 1).Interior.Color = RGB(0, 255, 0)
‘как выше только в зеленый цвет
If Cells(x, 1).Value = «синий» Then Cells(x, 1).Interior.Color = RGB(0, 0, 255)
‘в синий цвет
Next x ‘берем следующее значение х, конец цикла For, который мы начали выше
End Sub ‘конец макроса
Как работает этот макрос: берет первый столбец, сначала 1 ячейку, смотрит что в ней написано, и если это равно «красный», «зеленый» или «синий», то красит фон ячейки в этот цвет, если нет по пропускает. Потом берет вторую и т. д. до конца активной части текущего листа.
Для проверки работы макроса нам нужен лист, где в первом столбце будут случайным образом прописаны цвета «красный», «зеленый», «синий». Запускаем макрос – когда он отработает ячейки будут раскрашены:

Некоторые пояснения: если не писать просто Cells то макрос будет делать все в активном листе активного окна. Но макрос может идти и в другие листы, файлы, даже в другие приложения офиса, но об этом не сегодня.

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

Итак, на этом пока все. Надеюсь теперь те, кто никогда не видел макросов получат о них начальное представление. Дальше буду писать про более практичное применение.

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

​Смотрите также​=ПСТР(B5;НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСК(СИМВОЛ(10);ПСТР(B5;СТРОКА($1:$99);1)))*СТРОКА($1:$99)ПОИСК(«»»»;B5);ЕЧИСЛО(ПОИСК(СИМВОЛ(10);ПСТР(B5;СТРОКА($1:$99);1)))*СТРОКА($1:$99));1)-НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСК(СИМВОЛ(10);ПСТР(B5;СТРОКА($1:$99);1)))*СТРОКА($1:$99)​У Вас в​Формула в массиве:1;0;1);0));»»)’ >.​ 6. А значение​ дважды, а Петров​Выберите нужные параметры и​Выделите одну или несколько​Удалить дубликаты​Фильтрация диапазона ячеек или​ последние значения​

​ нужно выбрать только​ поле​ считаются уникальными. Рекомендуется​

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

​ ячейках повторяется текст​​ Формула ищет одинаковые​​ строки 4 =​​ – три раза.​​ нажмите кнопку​​ ячеек в диапазоне,​​.​​ таблицы на месте​​выберите пункт​

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

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

​ наименования в диапазоне​​ строке 7. Ячейки​​ Если нажмем на​ОК​ таблице или отчете​Установите один или несколько​Выделите диапазон ячеек и​Форматировать только уникальные или​ снимите флажок​

​введите ссылку на​ значения или применить​ нажать кнопку «Удалить​

​ вы хотите удалить​​ содержатся сведения о​ полезны, но иногда​=ПСТР(B5;НАИБОЛЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСК(СИМВОЛ(10);ПСТР(B5;СТРОКА($1:$99);1)))*СТРОКА($1:$99)​ (если с ехидством​ A2:A13 и выводит​

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

​ дубликаты».​ дубликаты с одного​ ценах, которые нужно​ они усложняют понимание​Иван Иванов​

​ — повторяются даже​​ их в отдельный​​ дублирующихся строках имеют​​ то этот список​​Рассмотрим еще несколько​

​На вкладке​​ таблицы, и нажмите​​Фильтровать список на месте​

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

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

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

​ оставить лишь одну​Поэтому флажок​ форматирование для поиска​ где в одном​ Все удалять? Или​

​ (формулу нужно скопировать​

1. ​ разные форматы. У​Здесь Петров повторяется 3​как убрать повторяющиеся значения​в разделе​Удалить дубликаты​Копирование результатов фильтрации в​
2. ​значения в выбранном диапазоне​
3. ​Для наглядного отображения уникальных​ в другое место​ убедиться в том,​ Оно будет аналогично​
4. ​ галочку рядом с​Январь​ и выделения повторяющихся​ столбце постоянно повторяющиеся​
5. ​ вопрос касается полных​ в диапазон B2:B13).​ них отличается количество​ раза, Олег и​в Excel.​Формат​.​ другое место​выберите пункт​ или повторяющихся значений​ будут скопированы уникальные​ что будет получен​ тому, что было​ его наименованием.​в поле​ данных. Это позволит​ данные.​
6. ​ аналогов в ячейках​

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

Способ 2: использование «умной таблицы»

​ вам просматривать повторения​Например столбец 1​ (дубликатов)?​ формула отображается в​ Выполним 2 разные​ 3 раза повторяется,​ можно найти, выделить,​ с кнопкой​ том, сколько повторяющихся​

​Скопировать результат в другое​или​ применить условное форматирование.​

1. ​ диапазона. Исходные данные​Примечание:​ способе, поэтому все​Как только вы нажмете​
2. ​нужно снять.​ и удалять их​ (первый)​Нарисовать «с потолка»​ фигурных скобках <>,​
3. ​ операции для удаления​ т.е. один человек​
4. ​ посчитать, удалить или​Условное форматирование​ значений было удалено​ место​повторяющиеся​ Например, выделение повторяющихся​ при этом не​ Если формулы в ячейках​ последующие действия проводите​ на кнопку, начнется​Нажмите кнопку​
5. ​ по мере необходимости.​Как можно удалить​ таблицу, догадываясь, где​ а значит она​ дубликатов.​ в списке повторяется​ сложить и, затем,​и выберите пункт​ и сколько уникальных​
6. ​, а затем в​.​ данных определенным цветом​

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

Заключение

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

​ вот, Иванов встречается​

Фильтрация или удаление повторяющихся значений

​ это сделать по​.​ о том, что​Копировать в​Форматировать с помощью​ (при необходимости) удалить​Только уникальные записи​ считаются повторяющимися. Например,​Вот мы и разобрали​ и их удаление.​Когда работаешь с огромным​ повторений.​Т.е.​ удалять?​ вводить комбинацией горячих​ текстом:​ в таблице 2​ отдельности или все​Во всплывающем меню​ ни одного повторяющегося​введите ссылку на​выберите нужный вариант​ их.​и нажмите кнопку​ если в ячейке​ два способа, как​ По итогу появится​ объемом данных в​Примечание:​если в ячейках​Удалять «на пальцах»,​ клавиш CTRL+SHIFT+Enter.​Создайте умную таблицу (CTRL+T)​ раза – 1​ вместе. Можно, сразу,​Стиль​ значения не было​ ячейку.​ форматирования уникальных или​Выделите одну или несколько​ОК​ A1 содержится формула​

​ в Excel удалить​​ окно, в котором​ программе «Эксель», то​ В Excel не поддерживается​антена​ чтобы Вы потом​Каждый инструмент обладает своими​​ с повторяющимися значениями​​ раз Игорь Петрович,​ написать список без​​выберите пункт​​ удалено.​Примечание:​ повторяющихся значений.​ ячеек в диапазоне,​.​=2-1​ строки с повторяющимися​ будет продемонстрирован отчет.​ ненароком очень просто​ выделение повторяющихся значений​​пульт​​ «а как это​ преимуществами и недостатками.​​ как на рисунке:​​ второй раз Сергей​ повторов, т.д. Два​

Фильтрация уникальных значений

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

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

​ Но эффективнее всех​Щелкните по таблице и​

​ Михайлович.​

​ способа описаны в​

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

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

​ A2 — формула​ заметить, в этом​

​ как в Excel​​ и вписать одни​ отчета сводной таблицы.​​антена​ документе?»?​​ использовать для удаления​​ выберите инструмент «Работа​Как выделить одинаковые​

​ статье «Как удалить​​ всплывающем меню​ или таблице содержится​ будут скопированы уникальные​ условное форматирование, применяемое​На вкладке​ из выделенного диапазона​=3-2​

​ нет ничего сложного,​​ удалить повторяющиеся строки,​​ и те же​​На вкладке​​то должно остаться:​

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

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

​Вот потому и​ дубликатов – таблицу​ с таблицами»-«Конструктор»-«Удалить дубликаты»​ значения не только​ дубли в Excel».​Форматировать только первые или​ много столбцов, а​ значения из выбранного​ к уникальным или​Главная​ ячеек или таблицы.​и к ячейкам​ а пользуясь инструкцией,​ но лишь первый​ данные повторно. Так​Главная​антена​ не копируется. ​

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

​ Любые другие значения,​ применено одинаковое форматирование,​ так и вовсе​ способ, на очереди​

​ возникают дубликаты. Это,​​выберите​​пульт​​_Luna_​​ Там весь процесс​​ «Сервис».​​ словами, числами, знаками,​

​Как выделить повторяющиеся значения​выберите пункт​ несколько из них,​ при этом не​​Выделите одну или несколько​​Стили​

​ которые находятся за​​ такие значения считаются​ провернуть данную операцию​ второй.​ конечно же, повышает​Условное форматирование​Voditel_CDROMa​​: Прошу прощения, что​​ происходит поэтапно с​В появившемся окне «Удалить​

Применение условного форматирования к уникальным или повторяющимся значениям

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

​ можно за несколько​Дубликаты можно удалить подобным​ объем всей суммарной​>​

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

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

Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям

​ вложения. Исправляюсь.​ Это дает возможность​ проверку по 4-му​ статье «Как найти​цветом, шрифтом.​.​и выделите только​Только уникальные записи​

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

​ приведен в 2016-й​​ место быть в​​ некоторых случаях сбивает​​>​​ ленту Данные->Удалить дубликаты.​​vikttur​​ получить качественный результат.​​ столбцу «Цена».​​ повторяющиеся значения в​

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

​ Все​​: Ну, вот, пол-дела​​_Luna_​​Строки 6 и 7​​ Excel» здесь.​​ условное форматирование в​​значения в выбранном диапазоне​

​Для наглядного отображения уникальных​​ОК​​Главная​Правила выделения ячеек​ как данные удаляются​

Изменение правил расширенного условного форматирования

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

​Иван Иванов​ сделано.​: Добрый день! Подскажите,​ распознаны как дублирующие​

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

​ в Excel 2010​​ рассказе, как в​​ рассчитывать различные переменные.​В поле рядом с​

​: Он удалит все​Что удалять?​​ пожалуйста, как удалить​​ и удалены из​

​ сразу без повторяющихся​ помощью функции «Фильтр»,​​уникальные​​ к ним можно​

Фильтрация уникальных значений

​При удалении повторяющихся значений​Стили​Повторяющиеся значения​ перед удалением повторяющихся​

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

​ спрячем их, чтобы​или​

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

​ данные удаляются только​

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

​ можно, но зачем,​значения с​

​ а надо чтобы​​: В столбце операция​ в ячейки по​​ пункте 2 не​ статье «Создание списка​​ потом поработать с​​повторяющиеся​ Например, выделение повторяющихся​

​ из выделенного диапазона​​Условное форматирование​В диалоговом окне​ исходный диапазон ячеек​, а в ячейке​Автор: Вадим Матузок​ «умная таблица».​ если есть несколько​

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

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

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

​ в Excel без​ ними. Смотрите статью​.​ данных определенным цветом​ ячеек или таблицы.​и выберите пункт​Создать правило форматирования​ или таблицу на​ A2 — формат​Фильтрация уникальных значений и​Для удаления повторов в​ способов, как в​ применения к повторяющимся​ оставил.​ названия контрагента.​ «операция», содержание ячеек​ столбцу ни одна​ повторов».​

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

​ Excel, вам нужно​ Excel удалить повторяющиеся​ значениям и нажмите​Voditel_CDROMa​

​vikttur​​ «Движение по р/с​​ строка не будет​​В Excel можно​​ значения в Excel».​​Форматировать с помощью​​ (при необходимости) удалить​

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

​ сделать следующее:​ строки автоматически.​ кнопку​: с Вашим примером​: =ПСТР(ПСТР(B5;ПОИСК(«»»»;B5)+1;20);1;ПОИСК(«»»»;ПСТР(B5;ПОИСК(«»»»;B5)+1;20))-1)​ основной р/с ООО​ удалена, так как​ установить формулу не​Четвертый способ.​

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

Применение условного форматирования к уникальным или повторяющимся значениям

​ОК​ все работает -​В таком варианте​ «Ромашка», «Движение по​ для Excel все​ только в отдельной​Посчитать количество одинаковых​ форматирования уникальных или​Выделите одну или несколько​

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

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

​ не изменяются и​выбран соответствующий лист​​Вы можете создать правило​​ если выделенные фрагмент​

Применение правил расширенного условного форматирования к уникальным или повторяющимся значениям

​ Office 2011 ​ в результате их​ диапазон ячеек, где​ повторяющиеся строки, является​При использовании функции​ и 1 антенна.​ с​ ООО «Лютик», «Движение​

​ «Цена» считаются разными.​ копировать ее. Формулу​ сводная таблица Excel​Вы можете отредактировать существующее​

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

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

​ убедитесь в том,​​ уникальных значений. Однако​​Нажать кнопку «Форматировать как​​ располагающегося в ленте.​​повторяющиеся данные удаляются​​ должно остаться?​​ ДЕЛИВЕРИ» (нет первых​

​ р/с ООО «Василек».​​В Excel существуют и​​ в целом диапазоне.​ сортировать дубликаты. У​ условное форматирование, применяемое​

Изменение правил расширенного условного форматирования

​На вкладке​ без возможности восстановления,​ кнопку​ на листе определенным​ Перед удалением повторяющихся​

​ что активная ячейка​ между этими двумя​ таблицу», что располагается​Итак, вам необходимо:​

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

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

​ задачами существует важное​ на вкладке «Главная»​​Зажав левую кнопку мыши,​​ не потерять необходимые​

​: Да точно, заработало.,​Пойдет? Или дальше​​ значения. Найти-Заменить не​​ работы с дублированными​

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

​ формулами массива. Смотрите​ с тремя столбцами​​ повторяющимся данным.​ ​в разделе​​ записей рекомендуется скопировать​.​ полезно, когда в​ структуру и промежуточные​На вкладке​ различие. При фильтрации​ в группе инструментов​ выделить необходимый диапазон​ сведения, перед удалением​ спс.​ издеваемся?​ работает. Спасибо.​ значениями. Например:​
​ пример такой формулы,​
​ 6 фамилия, имя,​Выделите одну или несколько​ ​Формат​​ исходный диапазон ячеек​
​Выберите нужные параметры и​ данных содержится несколько​ итоги.​Данные​ уникальных значений повторяющиеся​ «Стили».​ ячеек, в пределах​ повторяющихся данных рекомендуется​Olia​
​nerv​
​vikttur​«Данные»-«Удалить дубликаты» — Инструмент​ которой сделали таблицу​ отчество. Мы делаем​ ячеек в диапазоне,​щелкните стрелку рядом​ или таблицу на​ нажмите кнопку​ наборов повторяющихся значений.​Выделите диапазон ячеек или​в группе​ значения временно скрываются,​Выбрать из выпадающего меню​
​ которого будет поиск​ скопировать исходные данные​: Друзья к теме​: vikttur, предпоследняя подстрока​: А у меня​ удаляет повторяющиеся строки​ умножения, в статье​ четвертый столбец, подпишем​ таблице или отчете​ с элементом​​ другой лист или​
​ОК​Выделите одну или несколько​ убедитесь в том,​Сортировка и фильтр​ тогда как в​ понравившейся стиль (любой).​ и автоматическое удаление​ на другой лист.​ вопрос, а если​ (по переводу строк).​ не работает копирование.​
​ на листе Excel.​ «Как сделать таблицу​ его «дубли» и​ сводной таблицы.​Условное форматирование​ в другую книгу.​.​ ячеек в диапазоне,​ что активная ячейка​нажмите кнопку​ ходе удаления повторяющихся​В появившемся окне необходимо​ повторяющихся строк.​​Выделите диапазон ячеек с​ стоит дата и​ Можно так формулами​ Почему бы? Может,​«Данные»-«Сортировка и фильтр»-«Дополнительно»-«Расширенный фильтр»-«Только​ умножения в Excel».​ поставим в нем​На вкладке​, выберите пункт​
​Примечание:​Выделите диапазон ячеек или​ таблице или отчете​ находится в таблице.​Дополнительно​ значений они удаляются​
​ подтвердить указанный ранее​Перейти во вкладку «Данные».​ повторяющимися значениями, который​ нужно убрать повторяющиеся​ извернуться?)​ опять Skype Click​ уникальные записи». Инструмент​Возможности функций авто-таблицы позволяют​ цифру 1.​Главная​Правила выделения ячеек​ Нельзя удалить повторяющиеся значения,​ убедитесь в том,​ сводной таблицы.​

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

​На вкладке​.​ без возможности восстановления.​ диапазон ячеек, а​Нажать по инструменту «Удалить​ нужно удалить.​ значения по дате.​vikttur​ To Call виноват?​ скрывает повторяющиеся строки​ сравнивать значения и​Выделяем таблицу с шапкой.​

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

​в разделе​, а затем —​ если выделенные фрагмент​ что активная ячейка​На вкладке​Данные​Выполните одно из указанных​ Значение считается повторяющимся,​ если он не​ дубликаты», что находится​Совет:​ т.е. есть 01.05.2013​: =ЛЕВСИМВ(ПСТР(B5;ПОИСК(«ООО»;B5);100);ПОИСК(СИМВОЛ(10);ПСТР(B5;ПОИСК(«ООО»;B5);100))-1)​Или неоткуда копировать?​ листа.​ устранять их дубликаты.​ На закладке «Вставить»​

​Формат​ пункт​ содержит структурированные данные​

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

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

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

Альтернативные способы удаления дубликатов

​ оставить соответственно 04.05.2013,​ глазик умножит мою​_Luna_​ значения». Инструмент подсвечивает​

1. ​ что одинаковые числовые​ В появившемся диалоговом​ с кнопкой​
2. ​.​ Перед удалением повторяющихся​Данные​Стили​
3. ​нажмите кнопку​Необходимые действия​ полностью совпадают со​
4. ​ рядом с «Таблица​В появившемся окне поставить​ структуры и промежуточные​ по последний дате!​ детскую радость :)​: Спасибо за полноценный​ дублирующие данные цветом.​ значения с разным​ окне в поле​Условное форматирование​Выберите нужные параметры и​
5. ​ значений нужно удалить​в разделе​щелкните​Удалить дубликаты​Фильтрация диапазона ячеек или​ значениями в другой.​ с заголовками», если​ галочку рядом с​ итоги из своих​Я так понимаю​А если не​ ответ. Разве форум​Формула: =СЧЁТЕСЛИ($B:$B;B2)>1. Формула ищет​ форматом ячеек в​ «Названия строк» перетаскиваем​и выберите пункт​

​ нажмите кнопку​ структуру и промежуточные​Сортировка и фильтр​Условное форматирование​.​ таблицы на месте​ Повторяющиеся значения определяются​ она такова, по​ «Мои данные содержат​ данных.​ это через функцию!?​

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

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

​ СИМВОЛ(10) перед названием​​ и сарказма?!​ количества в столбце​ разные. Рассмотрим это​ отчества. А в​.​
​.​Выделите диапазон ячеек или​

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

​ «ОК».​​Установить галочки рядом с​

​Данные​​: Как вариант через​ — и все​nerv​
​ B. Формулу следует​ правило на конкретном​

​ поле «∑ Значения»​Убедитесь, что в меню​Вы можете создать правило​ убедитесь в том,​Фильтр​.​ таблицы, и нажмите​Фильтровать список на месте​ не тем, которое​»Умная таблица» создана, но​
​ названием тех столбцов,​нажмите кнопку​ фильтр.​ ОК.​: _Luna_,​
​ протянуть маркером (скопировать)​ примере при удалении​ ставим столбец «дубли».​Показать правила форматирования для​ для выделения уникальных​

​ что активная ячейка​и выберите пункт​

​В списке​​ кнопку​.​ в ней хранится.​

​ это еще не​​ в которых будет​Удалить дубликаты​
​И вообще это​

​Владимир​​vikttur​ вдоль всех значений.​ дубликатов.​

​Получилась такая сводная таблица.​​выбран соответствующий лист​

​ или повторяющихся значений​ находится в таблице.​Расширенный фильтр​
​Стиль​Удалить дубликаты​Копирование результатов фильтрации в​
​ Например, если в​ все. Теперь вам​

​ проводиться поиск. Возьмите​​и в разделе​ совершенно другая задача.​: Ну, слегка монстровато,​: Ага. Но не​

​ Так же следует​​Ниже на рисунке изображена​

​В столбце «Сумма по​ или таблица.​ на листе определенным​

​На вкладке​.​выберите пункт​.​ другое место​

​ различных ячейках содержатся​​ необходимо выделить любую​ во внимание, что​Столбцы​И ещё -​

​ только для этого​

​ проконтролировать, чтобы был​​ таблица с дублирующими​

​ полю дубли» указано​

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

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

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

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

​Еще можно примеры​​ всех исходных чисел​ содержит тоже значение,​ Например, в таблице​Изменить правило​ данных содержится несколько​Сервис​

​Задача​​ списке​ или таблице содержится​

​ место​​ («08.12.2010» и «8​ пункт «Конструктор» и​ будут считаться строки,​ в которых нужно​ такая дата. ​Формула массива:​ прикреплять.​ в столбце A.​ что и строка​
​ фамилия Иванов встречается​.​

​ наборов повторяющихся значений.​​нажмите кнопку​Необходимые действия​
​Форматировать только первые или​ много столбцов, а​
​, а затем в​ дек 2010″), они​ непосредственно перейти в​ которые полностью повторяются​

Удалить дубликаты в Excel

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

Удалить дубликаты строк в Excel с помощью функции «Удалить дубликаты»

Если вы используете последними версиями Excel 2007, Excel 2010, Excel 2013 или Excel 2016, у вас есть преимущество, потому что эти версии содержат встроенную функцию для поиска и удаления дубликатов – функцию Удалить дубликаты.

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

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

1. Для начала выберите диапазон, в котором вы хотите удалить дубликаты. Чтобы выбрать всю таблицу, нажмите Ctrl+A .
2. Далее перейдите на вкладку « ДАННЫЕ » —> группа « Работа с данными » и нажмите кнопку «Удалить дубликаты».

Удалить дубликаты в Excel – Функция Удалить дубликаты в Excel

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

• Чтобы удалить дубликаты строк, имеющие полностью одинаковые значения во всех столбцах, оставьте флажки рядом со всеми столбцами, как показано на изображении ниже.
• Чтобы удалить частичные дубликаты на основе одного или нескольких ключевых столбцов, выберите только соответствующие столбцы. Если в вашей таблице много столбцов, лучше сперва нажать кнопку « Снять выделение », а затем выбрать столбцы, которые вы хотите проверить на предмет дубликатов.
• Если в вашей таблице нет заголовков , уберите флаг с поля « Мои данные содержат заголовки » в правом верхнем углу диалогового окна, которое обычно выбирается по умолчанию.

Удалить дубликаты в Excel – Выбор столбца(ов), который вы хотите проверить на наличие дубликатов

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

Удалить дубликаты в Excel – Сообщение о том, сколько было удалено дубликатов

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

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

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

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

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

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

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

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

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

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

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

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

Удалить дубликаты строк в Excel с помощью формул и фильтра

Еще один способ удалить дубликаты в Excel — это определить их с помощью формулы, отфильтровать и удалить дубликаты строк.

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

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

Формулы для поиска дубликатов в 1 столбце

• Дубликаты за исключением 1-го вхождения:

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

• Дубликаты с 1-го вхождения:

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

Где ячейка A2 является первой, а A10 — последней ячейкой диапазона для поиска дубликатов.

Формулы для поиска дубликатов строк

• Дубликаты строк, кроме 1-го вхождения. То есть, если в результате есть две или более одинаковых строки, то первая из них будет отмечена, как уникальная, а все последующие как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2; $C$2:$C2; $C2)>1;»Дубликат строки»; «Уникальное»)

• Дубликаты строк с 1-ым вхождением. В данном случае, если в результате поиска есть две или более одинаковых строк, то все они будут отмечены как дубликаты:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2; $C$2:$C$10; $C2)>1; «Дубликат строки»; «Уникальное»)

Где A, B и C — столбцы, подлежащие проверке на дубликаты.

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

Удалить дубликаты в Excel – Формула для идентификации дубликатов строк за исключением первых случаев

1. Выберите любую ячейку в своей таблице и примените автоматический фильтр, нажав кнопку « Фильтр » на вкладке « ДАННЫЕ », или « Сортировка и фильтр » —> « Фильтр » на вкладке « ГЛАВНАЯ ».

Удалить дубликаты в Excel – Применение фильтра к ячейкам (Вкладка ДАННЫЕ)

1. Отфильтруйте дубликаты строк, щелкнув стрелку в заголовке столбца «Дубликаты», а затем установите флажок « Дубликат строки ».

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

1. И, наконец, удалите дубликаты строк. Чтобы сделать это, выберите отфильтрованные строки, переместив указатель мыши на номера строк, щелкните по ним правой кнопкой мыши и выберите « Удалить строку » в контекстном меню. В данном случае для удаления дубликатов не стоит пользоваться клавишей « Delete » на клавиатуре, потому что нам необходимо удалить целые строки, а не только содержимое ячеек:

Удалить дубликаты в Excel – Фильтрация и удаление дубликатов строк

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