Удаление дубликатов
Удаление дубликатов. Поиск удаленных данных





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Удаление дубликатов. Поиск удаленных данных

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

Удаление дубликатов. Поиск удаленных данных

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

Делаем выборку по названиям

Макет документа в прикрепленном файле – по-прежнему реестр договоров. Правда, для реалистичности добавила в нем немного строк и столбцов, но сути это не меняет – есть большой реестр (а в жизни это может быть и 300 и 3.000 и более строк), на основании которого нужно решить пару задач

Задача № 1

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

И на этот случай у Excel есть инструмент. Чтобы увидеть его в работе, столбец таблицы, на основании которого будем делать список контрагентов, предлагаю скопировать в любое место справа от таблицы, потому что лишние записи будут удалены, а исходная таблица пострадать не должна. Я скопировала данные из столбца E в столбец K:

Далее выбираем любую заполненную ячейку нового скопированного столбца K и выполняем команду Данные – Работа с данными – Удалить дубликаты [DataData_ToolsRemove_Duplicates], должно появиться диалоговое окно в котором остается нажать ОК и список будет готов. При выполнении команды обратите внимание на флажок Мои данные содержат заголовки [My_data_has_headers] и на диапазон выделенных ячеек – если набор данные заголовков не содержал, то флажок нужно сбросить. В моем случае заголовки были, поэтому оставлю его на месте:

Excel обязательно отчитается о выполненном действии:

И далее вопрос техники – при помощи функции СУММЕСЛИ [SUMIF] нужно просуммировать значения. Я дописала заголовок для столбца, а общий итог посчитала при помощи автосуммы (после вычислений в ячейках L3:L5 встала на ячейку L6 и нажала Alt+”=”):

Задача № 2

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

Поступим вот так – лист со своей рабочей таблицей скопируйте и работайте в копии, так как позже получившуюся таблицу нужно будет сверить с оригиналом. У меня за оригинал принят лист Реестр. На листе с рабочей таблицей (в примере лист Дубликаты) выбираем любую заполненную ячейку реестра и выполняем команду Данные – Работа с данными – Удалить дубликаты [DataData_ToolsRemove_Duplicates] и в появившемся диалоговом окне (размеры его, кстати регулируются – наведите курсор мыши на рамку окна и курсор примет вид белой двунаправленной стрелки) в нижней его части отрегулируйте при помощи флажков, по каким столбцам искать совпадения. Если нажать ОК сейчас, то программа скажет, что не нашла дубликатов, потому что дубликатами считаются такие строки, в которых данные совпадают по всем столбцам одновременно. Поэтому логично будет сбросить флажки, которые не имеют отношения к документу как таковому, а именно:

Теперь можно нажимать ОК и Excel выведет сообщение о том, что 10 записей определены как дубликаты и были удалены:

После нажатия кнопки OК в окне отчета результаты в итоговой таблице (справа от основной) будут пересчитаны и общий итог составит 32.418.397

 

Сравнить две таблицы и найти удаленные строки

Теперь было бы неплохо понять, какие именно строчки были удалены. Для этого получившуюся таблицу нужно сверить с оригиналом. И тут на помощь можно привлечь любую из двух функций – ВПР [VLOOKUP] либо ПОИСКПОЗ [MATCH]. У меня в примере пусть будет ВПР [VLOOKUP]

Иду ла лист Реестр и добавляю в самом начале таблицы вспомогательный столбец, в нем пишу формулу, которая поищет каждый порядковый номер из основной таблицы в столбце с номерами по порядку получившейся таблицы и если не найдет, то очевидно, что строка с таким номером была удалено. То есть нужны значения ошибок формулы =ВПР(B3;'Готовый - Дубликаты'!A:A;1;0):

 

Аргумент Номер_столбца [Col_index_num] равен единице, так как Таблица [Table_array] состоит всего из одного столбца – в нем ищу порядковый номер и если нахожу, то его же и подставлю на лист с оригиналом таблицы. Если не нахожу, то вижу сообщение об ошибке – а это именно то, что мне нужно. Иногда и ошибки желанны :-)

 

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

Дальше можно чуток пофантазировать и выделить задвоенные строки цветом, для этого привлечем на помощь уже знакомый инструмент Условное форматирование [Conditional_Formatting]

Выделяем все данные таблицы (у меня диапазон ячеек B3:I32), в которых нужно произвести форматирование и выполняем команду Главная – Стили – Условное форматирование – Создать правило [HomeStylesConditional_Formatting], в открывшемся окне выбираем шестой тип правил и пишем следующую формулу для проверки условия =ЕОШИБКА($A3) – есть такая функция проверки значений и условий. Она узнает у указанного в скобках значения есть ли в нем ОШИБКА и возвращает слово ИСТИНА [TRUE], если ошибка и в самом деле есть (в нашем случае – результат ВПР [VLOOKUP] не может быть посчитан), а если нет ошибки (то есть функция может вычислить результат) – ЛОЖЬ [FALSE]. После чего не забываем нажать кнопку Формат [Format] и выбрать оформление таких строк:

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

Похожие материалы

Текстовые функции для отбора и исправления знаков в ячейке
Текстовые функции
Как взять из ячейки первые или последние несколько знаков? А из середины текста? Как правильно использовать функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР и другие?

Создание кнопок ПЛЮС и МИНУС для скрытия и отображения строк и столбцов
Группировка строк и столбцов
Как сделать кнопки плюс и минус для скрытия и отображения данных в Excel? Как настроить, чтобы они были в строках сверху или в столбах слева?

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

Примеры использования функции ЕСЛИ
ЕСЛИ логическая функция
Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

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



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