Выделяем все ячейки с данными и на вкладке Главная (Home) жмем кнопку Условное форматирование (Conditional Formatting), затем выбираем Правила выделения ячеек — Повторяющиеся значения (Highlight Cell Rules — Duplicate Values):

В появившемся затем окне можно задать желаемое форматирование (заливку, цвет шрифта и т.д.)

Способ 2. Выделение всей строки

Если хочется выделить цветом не одиночные ячейки, а сразу строки целиком, то придется создавать правило условного форматирования с формулой. Для этого выделяем все данные в таблице и выбираем Главная — Условное форматирование — Создать правило — Использовать формулу для выделения форматируемых ячеек (Home — Conditional formatting — Create rule — Use a formula to determine which cells to format), а затем вводим формулу:

=СЧЁТЕСЛИ($A$2:$A$20;$A2)>1

=COUNTIF($A$2:$A$20;$A2)>1

где

• $A$2:$A$20 — столбец в данных, в котором мы проверяем уникальность
  
• $A2 — ссылка на первую ячейку столбца
  

Способ 3. Нет ключевого столбца

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

Задача все та же — подсветить совпадающие ФИО, имея ввиду совпадение сразу по всем трем столбцам — имени, фамилии и отчества одновременно.

Самым простым решением будет, конечно, добавить дополнительный служебный столбец (его потом можно скрыть) с текстовой функцией СЦЕПИТЬ (CONCATENATE), чтобы собрать ФИО в одну ячейку:

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

Если же хочется всё решить без дополнительного столбца, то формула для условного форматирования будет посложнее:

Ссылки по теме

Как сделать ячейки одинакового размера в Excel

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

Выравнивание размеров

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

Ширина столбца может варьироваться от 0 до 255 единиц (по умолчанию выставлено 8,43 пункта), высота строки – от 0 до 409 пунктов (по умолчанию 12,75 единицы). Один пункт высоты приблизительно составляет 0,035 сантиметров.

При желании единицы измерения высоты и ширины можно заменить на другие варианты.

1. Находясь во вкладке «Файл», жмем на пункт «Параметры».
2. В открывшемся окне параметров Excel делаем переход в пункт «Дополнительно». В центральной части окна находим блок параметров «Экран». Раскрываем список около параметра «Единицы на линейке» и выбираем один из четырех возможных вариантов:
   • Сантиметры;
   • Дюймы;
   • Миллиметры;
   • Единицы (установлено по умолчанию).

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

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

Способ 1: выравнивание ячеек выделенного диапазона

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

1. Выделяем диапазон на листе, в котором планируем размер ячеек сделать равным.
2. Находясь во вкладке «Главная», кликаем на ленте по иконке «Формат», которая размещена в блоке инструментов «Ячейки». Открывается перечень настроек. В блоке «Размер ячейки» выбираем пункт «Высота строки…».
3. Открывается небольшое окошко «Высота строки». Вписываем в то единственное поле, которое в нем есть, размер в единицах, желаемый для установки на все строки выделенного диапазона. Затем жмем на кнопку «OK».
4. Как видим, размер ячеек выделенного диапазона по высоте стал равным. Теперь нам нужно будет подравнять его по ширине. Для этого, не снимая выделение, опять вызываем меню через кнопку «Формат» на ленте. На этот раз в блоке «Размер ячейки» выбираем пункт «Ширина столбца…».
5. Запускается окошко в точности такое же, как было при назначении высоты строки. Вводим в поле ширину столбцов в единицах, которая будет применяться к выделенному диапазону. Жмем на кнопку «OK».

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

Существует альтернативный вариант данного способа. Можно выделить на горизонтальной панели координат те столбцы, ширину которых нужно сделать одинаковой. Затем кликаем по этой панели правой кнопкой мыши. В открывшемся меню выбираем пункт «Ширина столбца…». После этого открывается окошко для введения ширины столбцов выделенного диапазона, о котором мы говорили чуть выше.

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

Способ 2: выравнивание ячеек всего листа

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

1. Кликаем по прямоугольнику, расположенному между горизонтальной и вертикальной панелями координат. Как видим, после этого весь текущий лист выделяется полностью. Существует и альтернативный способ выделения всего листа. Для этого просто набираем на клавиатуре сочетание клавиш Ctrl+A.
2. После того, как вся область листа была выделена, изменяем ширину столбцов и высоту строк под единый размер по тому же алгоритму, который был описан при изучении первого способа.

Способ 3: перетягивание границ

Кроме того, выровнять размер ячеек можно вручную перетаскивая границы.

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

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

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

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

Способ 4: вставка таблицы

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

1. Выделяем таблицу, которую нужно скопировать. Кликаем по значку «Копировать», который размещен на ленте во вкладке «Главная» в блоке инструментов «Буфер обмена». Можно также вместо данных действий после выделения набрать на клавиатуре сочетание клавиш Ctrl+C.
2. Выделяем ячейку на том же листе, на другом листе или в другой книге. Эта ячейка должна будет стать верхним левым элементом вставленной таблицы. Кликаем правой кнопкой мыши по выделенному объекту. Появляется контекстное меню. В нем переходим по пункту «Специальная вставка…». В дополнительном меню, которое появится после этого, кликаем, опять же, по пункту с точно таким же наименованием.
3. Открывается окно специальной вставки. В блоке настроек «Вставить» переставляем переключатель в позицию «Ширины столбцов». Жмем на кнопку «OK».
4. После этого на плоскости листа произойдет вставка ячеек одинакового размера с теми, которые были у исходной таблицы.

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

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

Как в эксель сгруппировать одинаковые строки. Группировка в Excel.

Группировка данных

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

Организовать группировку достаточно просто. Выделите несколько строк и нажмите кнопку Группировать на вкладке Данные (см. рис. 1).

Рисунок 1 – Кнопка группировки

Затем укажите тип группировки – по строкам (см. рис. 2).

Рисунок 2 – Выбор типа группировки

В итоге мы получаем… не то, что нам нужно. Строки товаров объединились в группу, указанную под ними (см. рис. 3). В каталогах обычно сначала идёт заголовок, а потом содержимое.

Рисунок 3 – Группировка строк «вниз»

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

Чтобы группировать строки «вверх» нужно изменить одну настройку. На вкладке Данные нажмите на маленькую стрелочку в нижнем правом углу раздела Структура (см. рис. 4).

Рисунок 4 – Кнопка, отвечающая за вывод окна настроек структуры

В открывшемся окне настроек снимите флажок с пункта Итоги в строках под данными (см. рис. 5) и нажмите кнопку ОК .

Рисунок 5 – Окно настроек структуры

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

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

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

Рисунок 6 – Многоуровневая структура каталога в Excel

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

Чтобы вывести строки на более высокий уровень иерархии, воспользуйтесь кнопкой Разгруппировать вкладки Данные . Полностью избавиться от группировки можно при помощи пункта меню Удалить структуру (см. рис. 7). Будьте внимательны, отменить действие невозможно!

Рисунок 7 – Снимаем группировку строк

Закрепление областей листа

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

Если вы закрепляете первую строку или первый столбец, то всё очень просто. Откройте вкладку Вид и в раскрывающемся меню Закрепить области выберите соответственно пункты Закрепить верхнюю строку или Закрепить первый столбец (см. рис. 8). Однако одновременно и строку, и столбец таким образом «заморозить» не удастся.

Рисунок 8 – Закрепляем строку или столбец

Чтобы снять закрепление, выберите в том же меню пункт Снять закрепление областей (пункт заменяет собой строку Закрепить области , если на странице применена «заморозка»).

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

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

Рисунок 9 – Закрепляем область из строк и столбцов

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

Поворот листа (замена строк на столбцы и наоборот)

Представьте такую ситуацию: вы несколько часов работали над набором таблицы в Excel и вдруг поняли, что неверно спроектировали структуру – заголовки столбцов следовало бы расписать по строкам или строки по столбцам (это не имеет значения). Набирать всё вручную заново? Ни за что! В Excel предусмотрена функция, позволяющая осуществить «поворот» листа на 90 градусов, переместив таким образом содержимое строк в столбцы.

Рисунок 10 – Исходная таблица

Итак, мы имеем некоторую таблицу, которую нужно «повернуть» (см. рис. 10).

1. Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
2. Копируем их в буфер обмена сочетанием клавиш или любым другим способом.
3. Переходим к пустому листу или свободному пространству текущего листа. Важное замечание: вставлять поверх текущих данных нельзя!
4. Вставляем данные комбинацией клавиш и в меню параметров вставки выбираем вариант Транспонировать (см. рис. 11). Как вариант, вы можете использовать меню Вставить со вкладки Главная (см. рис. 12).

Рисунок 11 – Вставка с транспонированием

Рисунок 12 – Транспонирование из главного меню

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

Рисунок 13 – Результат после поворота

Показ формул

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

Нажмите кнопку Показать формулы на вкладке Формулы (см. рис. 14), чтобы изменить представление данных на листе (см. рис. 15).

Рисунок 14 – Кнопка «Показать формулы»

Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта

Если вам трудно сориентироваться по адресам ячеек, отображаемым в строке формул, нажмите кнопку Влияющие ячейки со вкладки Формулы (см. рис. 14). Зависимости будут показаны стрелками (см. рис. 16). Для использования этой функции сначала следует выделить одну ячейку.

Рисунок 16 – Зависимости ячейки показаны стрелками

Скрываются зависимости нажатием кнопки Убрать стрелки .

Перенос строк в ячейках

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

Рисунок 17 – Надписи не помещаются в ячейки

Выделите ячейки с длинными надписями и нажмите кнопку Перенос текста на Главной вкладке (см. рис. 18), чтобы перейти к многострочному отображению (см. рис. 19).

Рисунок 18 – Кнопка «Перенос текста»

Рисунок 19 – Многострочное отображение текста

Поворот текста в ячейке

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

В зависимости от своих предпочтений вы можете пойти двумя путями:

1. Сначала создать надпись, а потом её повернуть.
2. Настроить поворот надписи в ячейке, а потом ввести текст.

Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре на Главной вкладке (см. рис. 20) и ввёл обобщающую надпись (см. рис. 21).

Рисунок 20 – Кнопка объединения ячеек

Рисунок 21 – Сначала создаём горизонтальную подпись

Рисунок 22 – Кнопка поворота текста

Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!

Рисунок 23 – Вертикальный текст ячейки

Если есть такое желание, угол поворота текста вы можете задать вручную. В том же списке (см. рис. 22) выберите пункт Формат выравнивания ячейки и в открывшемся окошке задайте произвольный угол и выравнивание (см. рис. 24).

Рисунок 24 – Задаём произвольный угол поворота текста

Форматирование ячеек по условию

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

Первое, что нужно сделать – выделить ячейки. Далее, на Главной вкладке нажмите кнопку Условное форматирование и выберите одну из заготовок (см. рис. 25). Результат будет отображаться на листе сразу, поэтому вам не придётся долго перебирать варианты.

Рисунок 25 – Выбираем заготовку условного форматирования

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

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

Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым

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

Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств

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

Рисунок 28 – Удаляем правила условного форматирования

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

Использование фильтров

Фильтры позволяют быстро находить нужную информацию в большой таблице и представлять её в компактном виде. Например, из длинного списка книг можно выбрать произведения Гоголя, а из прайс-листа компьютерного магазина – процессоры Intel.

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

После того, как ячейки выделены, на вкладке Главная нажмите кнопку Сортировка и фильтр и выберите пункт Фильтр (см. рис. 29).

Рисунок 29 – Создаём фильтры

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

Рисунок 30 – Создаём текстовый фильтр

Рисунок 31 – Создаём фильтр по слову

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

Для числовых данных доступны другие параметры (см. рис. 32). Например, можно выбрать 10 наибольших или 7 наименьших значений (количество настраивается).

Рисунок 32 – Числовые фильтры

Фильтры Excel предоставляют достаточно богатые возможности, сравнимые с выборкой запросом SELECT в системах управления базами данных (СУБД).

Отображение информационных кривых

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

Рисунок 33 – Инфокривая Excel 2010

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

Рисунок 34 – Вставка инфокривой

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

Заключение

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

Скачать файл, используемый в видеоуроке:

ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1(Group1) для выбранных элементов или Кв-л1(Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

1. Выделить имя группы;
2. Нажать клавишу F2 ;
3. Ввести новое имя группы.

РАЗГРУППИРОВКА ЭЛЕМЕНТОВ

1. Выделить группу элементов, которые требуется разгруппировать;
2. На вкладке Параметры(Options) в группе Группировать(Group) нажать кнопку Разгруппировать(Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать(Ungroup) ).

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

Примечания:

1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры — Сервис — Средства OLAP ) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

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

Итак, приступим. Нам понадобиться MS Excel практически любой версии. Я буду использовать MS Excel 2013.

Открываем или создаем новую книгу Excel. Заполняем нашими данными. Затем выделяем нужные строки или столбцы. Находим в верхнем ряду вкладку Данные . Чуть ниже в поле Структура нажимаем Группировать .

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

После того как мы определились что будем группировать, нам следует, ниже выделенных строк, ввести название группы. Я назвал просто — Название группы.

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

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

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

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

Наверно вы заметили слева цифры 1 и 2 – это уровни вложенности. Эти кнопочки служат для быстрого перехода между уровнями. Так как Excel поддерживает группировку данных внутри самой группы, конечно же это очень удобно!

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

Группировка данных

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

Организовать группировку достаточно просто. Выделите несколько строк и нажмите кнопку Группировать на вкладке Данные (см. рис. 1).

Рисунок 1 – Кнопка группировки

Затем укажите тип группировки – по строкам (см. рис. 2).

Рисунок 2 – Выбор типа группировки

В итоге мы получаем… не то, что нам нужно. Строки товаров объединились в группу, указанную под ними (см. рис. 3). В каталогах обычно сначала идёт заголовок, а потом содержимое.

Рисунок 3 – Группировка строк «вниз»

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

Чтобы группировать строки «вверх» нужно изменить одну настройку. На вкладке Данные нажмите на маленькую стрелочку в нижнем правом углу раздела Структура (см. рис. 4).

Рисунок 4 – Кнопка, отвечающая за вывод окна настроек структуры

В открывшемся окне настроек снимите флажок с пункта Итоги в строках под данными (см. рис. 5) и нажмите кнопку ОК .

Рисунок 5 – Окно настроек структуры

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

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

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

Рисунок 6 – Многоуровневая структура каталога в Excel

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

Чтобы вывести строки на более высокий уровень иерархии, воспользуйтесь кнопкой Разгруппировать вкладки Данные . Полностью избавиться от группировки можно при помощи пункта меню Удалить структуру (см. рис. 7). Будьте внимательны, отменить действие невозможно!

Рисунок 7 – Снимаем группировку строк

Закрепление областей листа

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

Если вы закрепляете первую строку или первый столбец, то всё очень просто. Откройте вкладку Вид и в раскрывающемся меню Закрепить области выберите соответственно пункты Закрепить верхнюю строку или Закрепить первый столбец (см. рис. 8). Однако одновременно и строку, и столбец таким образом «заморозить» не удастся.

Рисунок 8 – Закрепляем строку или столбец

Чтобы снять закрепление, выберите в том же меню пункт Снять закрепление областей (пункт заменяет собой строку Закрепить области , если на странице применена «заморозка»).

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

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

Рисунок 9 – Закрепляем область из строк и столбцов

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

Поворот листа (замена строк на столбцы и наоборот)

Представьте такую ситуацию: вы несколько часов работали над набором таблицы в Excel и вдруг поняли, что неверно спроектировали структуру – заголовки столбцов следовало бы расписать по строкам или строки по столбцам (это не имеет значения). Набирать всё вручную заново? Ни за что! В Excel предусмотрена функция, позволяющая осуществить «поворот» листа на 90 градусов, переместив таким образом содержимое строк в столбцы.

Рисунок 10 – Исходная таблица

Итак, мы имеем некоторую таблицу, которую нужно «повернуть» (см. рис. 10).

1. Выделяем ячейки с данными. Выделяются именно ячейки, а не строки и столбцы, иначе ничего не получится.
2. Копируем их в буфер обмена сочетанием клавиш или любым другим способом.
3. Переходим к пустому листу или свободному пространству текущего листа. Важное замечание: вставлять поверх текущих данных нельзя!
4. Вставляем данные комбинацией клавиш и в меню параметров вставки выбираем вариант Транспонировать (см. рис. 11). Как вариант, вы можете использовать меню Вставить со вкладки Главная (см. рис. 12).

Рисунок 11 – Вставка с транспонированием

Рисунок 12 – Транспонирование из главного меню

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

Рисунок 13 – Результат после поворота

Показ формул

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

Нажмите кнопку Показать формулы на вкладке Формулы (см. рис. 14), чтобы изменить представление данных на листе (см. рис. 15).

Рисунок 14 – Кнопка «Показать формулы»

Рисунок 15 – Теперь на листе видны формулы, а не результаты расчёта

Если вам трудно сориентироваться по адресам ячеек, отображаемым в строке формул, нажмите кнопку Влияющие ячейки со вкладки Формулы (см. рис. 14). Зависимости будут показаны стрелками (см. рис. 16). Для использования этой функции сначала следует выделить одну ячейку.

Рисунок 16 – Зависимости ячейки показаны стрелками

Скрываются зависимости нажатием кнопки Убрать стрелки .

Перенос строк в ячейках

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

Рисунок 17 – Надписи не помещаются в ячейки

Выделите ячейки с длинными надписями и нажмите кнопку Перенос текста на Главной вкладке (см. рис. 18), чтобы перейти к многострочному отображению (см. рис. 19).

Рисунок 18 – Кнопка «Перенос текста»

Рисунок 19 – Многострочное отображение текста

Поворот текста в ячейке

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

В зависимости от своих предпочтений вы можете пойти двумя путями:

1. Сначала создать надпись, а потом её повернуть.
2. Настроить поворот надписи в ячейке, а потом ввести текст.

Варианты различаются незначительно, поэтому рассмотрим только один из них. Для начала я объединил шесть строк в одну при помощи кнопки Объединить и поместить в центре на Главной вкладке (см. рис. 20) и ввёл обобщающую надпись (см. рис. 21).

Рисунок 20 – Кнопка объединения ячеек

Рисунок 21 – Сначала создаём горизонтальную подпись

Рисунок 22 – Кнопка поворота текста

Можно дополнительно уменьшить ширину столбца (см. рис. 23). Готово!

Рисунок 23 – Вертикальный текст ячейки

Если есть такое желание, угол поворота текста вы можете задать вручную. В том же списке (см. рис. 22) выберите пункт Формат выравнивания ячейки и в открывшемся окошке задайте произвольный угол и выравнивание (см. рис. 24).

Рисунок 24 – Задаём произвольный угол поворота текста

Форматирование ячеек по условию

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

Первое, что нужно сделать – выделить ячейки. Далее, на Главной вкладке нажмите кнопку Условное форматирование и выберите одну из заготовок (см. рис. 25). Результат будет отображаться на листе сразу, поэтому вам не придётся долго перебирать варианты.

Рисунок 25 – Выбираем заготовку условного форматирования

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

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

Рисунок 26 – Цветовая шкала от красного к зелёному с промежуточным жёлтым

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

Рисунок 27 – Гистограмма и набор значков отражают производительность некоторых условных устройств

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

Рисунок 28 – Удаляем правила условного форматирования

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

Использование фильтров

Фильтры позволяют быстро находить нужную информацию в большой таблице и представлять её в компактном виде. Например, из длинного списка книг можно выбрать произведения Гоголя, а из прайс-листа компьютерного магазина – процессоры Intel.

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

После того, как ячейки выделены, на вкладке Главная нажмите кнопку Сортировка и фильтр и выберите пункт Фильтр (см. рис. 29).

Рисунок 29 – Создаём фильтры

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

Рисунок 30 – Создаём текстовый фильтр

Рисунок 31 – Создаём фильтр по слову

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

Для числовых данных доступны другие параметры (см. рис. 32). Например, можно выбрать 10 наибольших или 7 наименьших значений (количество настраивается).

Рисунок 32 – Числовые фильтры

Фильтры Excel предоставляют достаточно богатые возможности, сравнимые с выборкой запросом SELECT в системах управления базами данных (СУБД).

Отображение информационных кривых

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

Рисунок 33 – Инфокривая Excel 2010

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

Рисунок 34 – Вставка инфокривой

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

Заключение

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

Создание структуры листа с помощью групп

Подготовка данных к структурированию

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

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

Располагайте итоговые строки выше или ниже каждой группы детальных строк. Для этого после каждой группы вставьте по пустой строке и в них используйте итоговые функции, например СУММ или СРЗНАЧ . Для получения наилучших результатов итоговые строки должны содержать формулы, ссылающиеся на ячейки во всех строках. В показанном на рисунке 8.8 примере имеется строка под сведениями каждой категории товара. Например, данные по продажам лекарств в строках с 5 по 9 суммируются в строке 10. Также можно структурировать данные, в которых итоговые строки содержат описательный текст и иные данные.

Рис. 8.8. Фрагмент окна со структурированными данными

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

Если итоговые строки расположены над строками данных или итоговые столбцы расположены слева от столбцов данных, можно изменить параметры расположения. Для этого необходимо дать команду меню Данные =>Группа и структура => Настройка иснять флажок итоги в строках под данными , флажок итоги в столбцах справа от данных или оба эти флажка.

Создание структуры автоматически и вручную

Автоматическое создание структуры

Если данные на листе обобщены формулами, которые используют функции, например СУММА, Microsoft Excel позволяет автоматически структурировать данные. Итоговые данные должны располагаться рядом с подробными данными.

Для создания автоматической структуры нужно выделить диапазон ячеек, для которых необходимо создать структуру (для структурирования листа целиком указать любую ячейку) и дать команду меню Данные => Группа и структура => Создание структуры. При вставке автоматических промежуточных итогов в список, представленный в виде строк, Microsoft Excel автоматически структурирует лист, давая возможность отобразить ровно столько подробной информации, сколько необходимо.

Создание структуры вручную

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

Для создания структуры листа вручную, необходимо выделить строки или столбцы, которые нужно объединить в одну группу, и дать команду меню Данные => Группы и структура => Группировать (рис.8.9). После этого рядом с детальными данными Одежда появиться символ структуры. Продолжать выделение и группировку строк или столбцов сведений и выполнение команды Группировать нужно до тех пор, пока не будут созданы все необходимые уровни структуры.

Рис. 8.9. Фрагмент окна для создания группы Одежда

Настройка структурирования со стилями

Автоматические стили можно применить непосредственно при структурировании или после него. Для структурированных строк Microsoft Excel использует такие стили, как «Уровеньстрока_1» и «Уровеньстрока_2». Для структурированных столбцов Microsoft Excel использует такие стили как «Уровеньстолб_1» и «Уровеньстолб_2». Стили используют полужирное, курсивное и другое форматирование текста, чтобы облегчить различение итоговых строк в данных.

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

Если требуется задать стили структуры для автоматического применения к новой структуре, нужно дать команду меню Данные => Группа и структура => Настройка и установить флажок Автоматические стили . Если при создании структуры не требуется применять автоматические стили, их можно применить после создания структуры. Для этого необходимо выделить данные, к которыми применяются стили, дать команду меню Данные => Группа и структура => Настройка в открывшемся диалоговом окне Настройка (рис. 8.10) установить флажок Автоматические стили и нажать кнопку Применить стили . Кнопка Создать этого диалогового окна создает структуру листа. В результате к итоговым строкам будет применено форматирование (рис.8.11)

Рис.8.10. Применение стилей к структуре

Рис. 8.11. Результат применения автоматических стилей к структуре

Удаление структуры

При удалении структуры никакие данные не удаляются. Для выполнения удаления структуры на нужном листе необходимо дать команду меню Данные => Группа и структура => Удалить структуру. Если строки или столбцы все равно останутся скрытыми, то можно просто отобразить скрытые строки или столбцы средствами Excel (выделить видимые заголовки и дать команду меню Формат => Строка или Столбец => Отобразить) .

Выделенные элементы структуры также можно разгруппировать без удаления всей структуры. Для этого, удерживая нажатой SHIFT щелкнуть кнопку или группы, а затем дать команду меню Данные => Группа и структура => Разгруппировать . На рис. 8.12 и 8.13 приведен пример удаления структуры для группы Иванова.

Рис. 8.12 . Выделение элемента структуры Иванова и выбор команды для разгруппирования

Рис. 8.13. . Вид окна при разгруппированной группе Иванова

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

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

Редактор таблиц MS Excel применяют в разных целях — для составления отчетов и планов, хранения данных о товарах и ценах. Если документ одновременно редактируют несколько человек или он создавался из нескольких файлов, не исключена вероятность повторения и отдельных элементов, и целых строк. Из-за такого удвоения могут появляться проблемы — неправильно считаются суммы и количество предметов. 

Функция удаления дубликатов 

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

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

Порядок действия для устранения повторов в таблице следующий: 

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

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

Простое удаление не позволит устранить повторы, если стоимость будет другой. Избежать ошибки можно, выбрав при настройке удаления дубликатов только те столбцы, которые будут сравниваться. Например, «Название» и «Марка» — но без «Цены» и «Количества». 

Применение расширенного фильтра 

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

Чтобы использовать фильтр, необходимо: 

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

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

Выделение повторов 

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

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

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

Создание сводной таблицы 

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

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

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

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

Читайте также:

Найдите и выделите дубликаты — Easy Excel Tutorial

повторяющихся значений | В трех экземплярах | Повторяющиеся строки

В этом примере показано, как найти повторяющиеся значения (или трижды) и как найти повторяющиеся строки в Excel .

Повторяющиеся значения

Чтобы найти и выделить повторяющиеся значения в Excel, выполните следующие действия.

1. Выберите диапазон A1: C10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните «Выделить правила ячеек, повторяющиеся значения».

4. Выберите стиль форматирования и нажмите OK.

Результат. Excel выделяет повторяющиеся имена.

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

в трех экземплярах

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

1. Сначала удалите предыдущее правило условного форматирования.

2. Выберите диапазон A1: C10.

3. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

4. Щелкните Новое правило.

5. Выберите «Использовать формулу для определения ячеек для форматирования».

6. Введите формулу = СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A1) = 3

7. Выберите стиль форматирования и нажмите OK.

Результат. Excel выделяет тройные имена.

Объяснение: = COUNTIF ($ A $ 1: $ C $ 10, A1) подсчитывает количество имен в диапазоне A1: C10, равных имени в ячейке A1. Если СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A1) = 3, Excel форматирует ячейку A1. Всегда записывайте формулу для левой верхней ячейки в выбранном диапазоне (A1: C10). Excel автоматически копирует формулу в другие ячейки. Таким образом, ячейка A2 содержит формулу = СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A2) = 3, ячейка A3 = СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A3) = 3 и т. Д. Обратите внимание, как мы создали абсолютную ссылку. ($ A $ 1: $ C $ 10), чтобы исправить эту ссылку.

Примечание: вы можете использовать любую формулу, которая вам нравится. Например, используйте эту формулу = СЧЁТЕСЛИ ($ A $ 1: $ C $ 10, A1)> 3, чтобы выделить имена, которые встречаются более 3 раз.

Повторяющиеся строки

Чтобы найти и выделить повторяющиеся строки в Excel, используйте СЧЁТЕСЛИ (с буквой S в конце) вместо СЧЁТЕСЛИ.

1. Выберите диапазон A1: C10.

2. На вкладке «Главная» в группе «Стили» щелкните «Условное форматирование».

3. Щелкните Новое правило.

4. Выберите «Использовать формулу для определения ячеек для форматирования».

5. Введите формулу = СЧЁТЕСЛИ (Животные, $ A1, континенты, $ B1, страны, $ C1)> 1

6. Выберите стиль форматирования и нажмите OK.

Примечание: именованный ареал Животные относится к диапазону A1: A10, именованный диапазон «Континенты» относится к диапазону B1: B10, а именованный диапазон «Страны» относится к диапазону C1: C10. = СЧЁТЕСЛИМН (Животные, $ A1, Континенты, $ B1, Страны, $ C1) подсчитывает количество строк на основе нескольких критериев (Леопард, Африка, Замбия).

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

Объяснение: если СЧЁТЕСЛИ (Животные, $ A1, Континенты, $ B1, Страны, $ C1)> 1, другими словами, если имеется несколько строк (Леопард, Африка, Замбия), Excel форматирует ячейку A1. Всегда записывайте формулу для левой верхней ячейки в выбранном диапазоне (A1: C10). Excel автоматически копирует формулу в другие ячейки. Мы зафиксировали ссылку на каждый столбец, поместив символ $ перед буквой столбца ($ A1, $ B1 и $ C1).В результате ячейки A1, B1 и C1 содержат одну и ту же формулу, ячейки A2, B2 и C2 содержат формулу = СЧЁТЕСЛИМН (Животные, $ A2, Континенты, $ B2, Страны, $ C2)> 1 и т. Д.

7. Наконец, вы можете использовать инструмент «Удалить дубликаты» в Excel, чтобы быстро удалить повторяющиеся строки. На вкладке «Данные» в группе «Работа с данными» щелкните Удалить дубликаты.

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

Примечание. Посетите нашу страницу об удалении дубликатов, чтобы узнать больше об этом замечательном инструменте Excel.

Как находить дубликаты в Excel, управлять ими и удалять их — Office Mastery

Дубликаты могут вызвать много головной боли в Excel.

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

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

Рисунок 01: Пример повторяющейся записи.Здесь у нас есть 2 адреса для одного и того же человека. Какую запись мы должны вести?

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

Бывает.

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

Полезно знать, как эффективно работать с дубликатами.

Вот что освещено в этом посте.

Возьмите бесплатную шпаргалку по дубликатам

1. Как определить дубликаты

Первый инструмент — это одна из опций функции условного форматирования.

Как следует из названия, есть два компонента — условие и формат.

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

Например,

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

При проверке дубликатов тест:

Есть ли в этой ячейке идентичный близнец?

Итак, вот процесс:

1. Выделите ячейки, которые вы хотите проверить. (В таблице это обычно один конкретный столбец).

2. На вкладке Home щелкните значок Условное форматирование .

3. Выберите Выделить правила ячеек .

4. Выберите повторяющихся значений .

Рисунок 02: Значок условного форматирования

5. Выберите способ форматирования любых найденных повторяющихся ячеек.

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

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

Рисунок 04: Выделены повторяющиеся ячейки

2.Как удалить дубликаты

Чтобы удалить дубликаты из одного столбца:

1. Выберите диапазон ячеек (или весь столбец, если это проще)

2. Щелкните вкладку Data .

3. Щелкните значок Удалить дубликаты .

Рисунок 05: Удалить дубликаты

4. Отметьте поле с надписью Мои данные имеют заголовки , если у ваших данных есть строка заголовка, в противном случае не устанавливайте флажок.

Рисунок 06: Подтвердите, что вы хотите удалить дубликаты

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

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

В более крупной таблице первая повторяющаяся ячейка может находиться в строке 5, следующая — в строке 105, а следующая — в строке 505.

Итак, есть ли простой способ собрать их все вместе в одном месте?

Хороший вопрос.Я рада, что вы спросили.

Да, есть.

Excel позволяет сортировать по цвету.

Вот как это сделать:

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

Рисунок 07: Условное форматирование, примененное к столбцу «Идентификатор клиента»

2. Затем щелкните любую ячейку в таблице.

3. Выберите вкладку Data .

4.Нажмите большую кнопку «Сортировка» (не маленькие значки AZ или ZA)

5. В раскрывающемся списке Сортировать по выберите столбец, содержащий выделенные дубликаты.

6. Измените Сортировка на Цвет ячейки .

7. Задайте для заказа Order цвет, который вы использовали для выделения повторяющихся ячеек.

Рисунок 08: Отсортируйте список по цвету ячеек для столбца, который содержит дубликаты

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

Рисунок 09: Дублирующиеся значения сгруппированы вместе в верхней части таблицы

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

4. Как проверить 2 или более столбца на наличие дубликатов

Рассмотрим этот пример.Два столбца были выбраны и проверены на наличие повторяющихся значений.

Рисунок 10: Как вы можете проверить 2 столбца в комбинации на наличие дубликатов?

Как видите, Bloggs был выделен 3 раза — дважды для Fred и один раз для Del .

Кроме того, Smith был помечен как дубликат, хотя один — Joe , а другой — Fred .

И, наконец, Fred был выделен 3 раза — дважды для Bloggs и один раз для Smith .

Единственные 2 записи, которые следует отметить здесь, — это Fred Bloggs.

Чтобы обойти это, мы можем объединить имя и фамилию в одно полное имя с помощью CONCATENATE. В ячейке D3 запишите формулу:

= СЦЕПИТЬ (B3, «», C3)

Функция СЦЕПИТЬ объединяет все элементы, перечисленные в скобках. Как видите, имя и фамилия разделены пробелом.

Теперь просто проверьте столбец «Полное имя» на наличие повторяющихся значений.

Рисунок 10: Имя и фамилия объединяются с помощью CONCATENATE

5. Что дальше?

Надеюсь, это открыло вам глаза и дало вам несколько хороших идей.

Как вы думаете?

Объединить повторяющиеся строки в одну запись

Мастер слияния дубликатов — это надстройка для Microsoft Excel, специально разработанная для объединения данных из повторяющихся строк в одну.

Видео: Как работать с объединением дубликатов

Перед тем, как начать

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

Обратите внимание, что скрытые строки все еще обрабатываются надстройкой.

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

Как использовать слияние дубликатов

Начать объединение дубликатов

На вкладке Ablebits Data в группе Merge щелкните значок Merge Duplicates :

Шаг 1: Выберите свой стол

На первом этапе надстройка выбирает весь диапазон с вашими данными:

• Чтобы развернуть выделение в текущей таблице или выбрать другой диапазон, выберите соответствующий значок справа от поля Выберите таблицу .
• Вы также можете выбрать записи прямо на листе, надстройка подберет ваш выбор.
• Другой вариант — ввести адрес диапазона в поле Select your table вручную.

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

Примечание. Если в вашей таблице есть скрытые строки, они будут обработаны.

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

Щелкните Далее .

Шаг 2. Выберите ключевые столбцы с повторяющимися записями

На этом шаге вы можете увидеть список столбцов, содержащихся в вашем диапазоне:

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

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

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

Нажмите кнопку Далее .

Шаг 3. Выберите столбцы со значениями для объединения

На этом этапе выберите столбцы с записями для объединения:

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

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

Нажмите Готово , чтобы дубликаты в выбранных столбцах объединились:

найти, выделить, подсчитать, отфильтровать и т. Д.

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

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

И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в едином списке. Эти решения работают во всех версиях Excel 365, Excel 2019, Excel 2016, Excel 2013 и ниже.

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

Самый простой способ обнаружить дубликаты в Excel — использовать функцию СЧЁТЕСЛИ. В зависимости от того, хотите ли вы найти повторяющиеся значения с первыми вхождениями или без них, в формуле будет небольшое изменение, как показано в следующих примерах.

Как найти повторяющиеся записи, включая 1

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

Вот формула для поиска дубликатов в Excel, включая первые вхождения (где A2 — самая верхняя ячейка):

= СЧЁТЕСЛИ (A: A, A2)> 1

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

Как видно на скриншоте выше, формула возвращает ИСТИНА для повторяющихся значений и ЛОЖЬ для уникальных значений.

Примечание. Если вам нужно найти дубликаты в диапазоне ячеек , а не во всем столбце, не забудьте заблокировать этот диапазон знаком $. Например, для поиска дубликатов в ячейках A2: A8 используйте эту формулу:
= COUNTIF ( $ A $ 2: $ A $ 8 , A2)> 1

Чтобы повторяющаяся формула возвращала что-то более значимое, чем логические значения ИСТИНА и ЛОЖЬ, заключите ее в функцию ЕСЛИ и введите любые метки, которые вы хотите для повторяющихся и уникальных значений:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, $ A2)> 1, «Дублировать», «Уникальный»)

В случае, если вы хотите, чтобы формула Excel находила только дубликаты, замените «Уникальный» пустой строкой («»), например:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, $ A2)> 1, "Дублировать", "")

Формула вернет «Дубликаты» для повторяющихся записей и пустую ячейку для уникальных записей:

Как искать дубликаты в Excel без вхождений 1

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

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

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)> 1, "Дублировать", "")

Как видно на следующем снимке экрана, эта формула не идентифицирует первое вхождение « Яблоки » как дубликат:

Как найти дубликаты с учетом регистра в Excel

В ситуациях, когда вам нужно идентифицировать точные дубликаты, включая текстовый регистр, используйте эту общую формулу массива (вводимую нажатием Ctrl + Shift + Enter):

IF (SUM ((—EXACT ( диапазон , самый верхний _cell ))) <= 1, "", "Duplicate")

В основе формулы вы используете функцию EXACT для точного сравнения целевой ячейки с каждой ячейкой в ​​указанном диапазоне.Результатом этой операции является массив ИСТИНА (совпадение) и ЛОЖЬ (не совпадение), который приводится к массиву из единиц и нулей унарным оператором (-). После этого функция СУММ складывает числа, и если сумма больше 1, функция ЕСЛИ сообщает «Дубликат».

Для нашего образца набора данных формула выглядит следующим образом:

= ЕСЛИ (СУММ ((- ТОЧНО ($ A $ 2: $ A $ 8, A2))) <= 1, "", "Дублировать")

Как показано на скриншоте ниже, строчные и прописные буквы рассматриваются как разные символы (ЯБЛОКИ не идентифицируются как дубликаты):

Как найти повторяющиеся строки в Excel

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

Рассмотрим следующий пример. Предположим, у вас есть порядковые номера в столбце A, даты в столбце B и заказанные элементы в столбце C, и вы хотите найти повторяющиеся строки с тем же номером заказа, датой и элементом. Для этого мы собираемся создать повторяющуюся формулу на основе функции СЧЁТЕСЛИМН, которая позволяет проверять несколько критериев одновременно:

Для поиска повторяющихся строк с 1 ^st вхождений , используйте эту формулу:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, $ A2, $ B $ 2: $ B $ 8, $ B2, $ C $ 2: $ C $ 8, $ C2)> 1, "Повторяющаяся строка", "" )

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

Чтобы отобразить повторяющихся строк без 1 ^st вхождений , внесите небольшие изменения в приведенную выше формулу:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2, $ B $ 2: $ B2, $ B2, $ C $ 2: $ C2, $ C2,)> 1 , "Повторяющаяся строка", "")

Как подсчитать дубликаты в Excel

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

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

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

Чтобы узнать, сколько раз та или иная запись встречается в вашем листе Excel, используйте простую формулу СЧЁТЕСЛИ, где A2 - это первый, а A8 - последний элемент списка:

= СЧЁТЕСЛИ (2 доллара: 8 австралийских долларов, A2)

Как показано на следующем снимке экрана, формула подсчитывает количество вхождений каждого элемента: « Яблоки » встречается 3 раза, « Зеленые бананы » - 2 раза, « Бананы » и « Апельсины » только один раз.

Если вы хотите идентифицировать 1 ^st , 2 ^nd , 3 ^rd и т. Д. Вхождения каждого элемента, используйте следующую формулу:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)

Аналогичным образом вы можете подсчитать вхождений повторяющихся строк . Единственное отличие состоит в том, что вам нужно будет использовать функцию СЧЁТЕСЛИ вместо СЧЁТЕСЛИ. Например:

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 8, $ A2, $ B $ 2: $ B $ 8, $ B2)

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

Подсчитать общее количество дубликатов в столбце (ах)

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

= СЧЁТЕСЛИ (диапазон; "дубликат")

Где « дубликат » - это метка, которую вы использовали в формуле для поиска дубликатов.

В этом примере повторяющаяся формула принимает следующую форму:

= СЧЁТЕСЛИ (B2: B8, «дубликат»)

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

= СТРОКИ ($ A $ 2: $ A $ 8) -SUM (IF (COUNTIF ($ A $ 2: $ A $ 8, $ A $ 2: $ A $ 8) = 1,1,0))

Поскольку это формула массива, не забудьте нажать Ctrl + Shift + Enter, чтобы завершить ее.Также имейте в виду, что эта формула учитывает все повторяющиеся записи, , включая первые вхождения :

Чтобы найти общее количество повторяющихся строк , вставьте функцию СЧЁТЕСЛИ вместо СЧЁТЕСЛИ в приведенную выше формулу и укажите все столбцы, которые вы хотите проверить на наличие дубликатов. Например, чтобы подсчитать повторяющиеся строки на основе столбцов A и B, введите в лист Excel следующую формулу:

= СТРОКИ ($ A $ 2: $ A $ 8) -SUM (IF (COUNTIFS ($ A $ 2: $ A $ 8, $ A $ 2: $ A $ 8, $ B $ 2: $ B $ 8, $ B $ 2: $ B 8 $) = 1,1,0))

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

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

Как показать и скрыть дубликаты в Excel

Если вы хотите сразу увидеть все дубликаты, воспользуйтесь одной из формул, чтобы найти дубликаты в Excel, которые лучше подходят для ваших нужд. Затем выберите свою таблицу, перейдите на вкладку Data и нажмите кнопку Filter . Также можно щелкнуть Сортировка и фильтр > Фильтр на вкладке Домашняя страница в группе Редактирование .

Наконечник. Чтобы включить автоматическую фильтрацию, преобразуйте данные в полнофункциональную таблицу Excel. Просто выберите все данные и нажмите сочетание клавиш Ctrl + T.

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

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

Как отфильтровать дубликаты по их вхождению

Если вы хотите показать 2 ^nd , 3 ^rd или N-е вхождения повторяющихся значений, используйте формулу для подсчета повторяющихся экземпляров, которые мы обсуждали ранее:

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2)

Затем примените фильтрацию к своей таблице и выберите только вхождения, которые вы хотите просмотреть. Например, вы можете отфильтровать 2 вхождения ^и , как на следующем снимке экрана:

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

Выберите « больше, чем » в первом поле, введите 1 в поле рядом с ним и нажмите кнопку OK :

Аналогичным образом можно отобразить 2 ^nd , 3 ^rd и все последующие повторяющиеся вхождения.Просто введите необходимое число в поле рядом с « больше ».

Выделение, выбор, очистка, удаление, копирование или перемещение дубликатов

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы скопировать дубликатов, выберите их, нажмите Ctrl + C, затем откройте другой лист (новый или существующий), выберите верхнюю левую ячейку диапазона, в который вы хотите скопировать дубликаты, и нажмите Ctrl + V, чтобы наклеить их.

Чтобы переместить дубликатов на другой лист, выполните те же действия с той лишь разницей, что вы нажимаете Ctrl + X (вырезать) вместо Ctrl + C (копировать).

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

Duplicate Remover - быстрый и эффективный способ найти дубликаты в Excel

Теперь, когда вы знаете, как использовать повторяющиеся формулы в Excel, позвольте мне продемонстрировать вам еще один быстрый, эффективный и свободный от формул способ - Duplicate Remover для Excel.

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

Как найти повторяющиеся строки в Excel за 2 быстрых шага

Чтобы проверить возможности нашей надстройки Duplicate Remover, я создал таблицу с несколькими сотнями строк, которая выглядит следующим образом:

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

1. Выберите любую ячейку в таблице и нажмите кнопку Dedupe Table на ленте Excel. После установки Ultimate Suite for Excel вы найдете его на вкладке Ablebits Data в группе Dedupe .
   
2. Интеллектуальная надстройка возьмет всю таблицу и попросит вас указать следующие две вещи:
   • Выберите столбцы для проверки дубликатов (в данном примере это номер заказа ., Дата заказа и поз. столбца).
   • Выберите действие для выполнения с дубликатами . Поскольку наша цель - выявить повторяющиеся строки, я выбрал Добавить столбец статуса
     

   Помимо добавления столбца статуса, вам доступен ряд других опций:

   • Удалить дубликаты
   • Цвет (выделение) дубликатов
   • Выбрать дубликаты
   • Копировать дубликаты на новый лист
   • Переместить дубликаты на новый лист

   Нажмите кнопку OK и подождите несколько секунд.Готово!

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

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

Мастер удаления дубликатов - больше возможностей для поиска дубликатов в Excel

В зависимости от конкретного листа, с которым вы работаете, вы можете или не захотите рассматривать первые экземпляры идентичных записей как дубликаты.Одно из возможных решений - использовать разные формулы для каждого сценария, как мы обсуждали в разделе «Как определять дубликаты в Excel». Если вы ищете быстрый, точный метод без формул, попробуйте мастер Duplicate Remover :

1. Выберите любую ячейку в таблице и нажмите кнопку Duplicate Remover на вкладке Ablebits Data . Мастер запустится, и будет выделена вся таблица.
   
2. На следующем шаге вам будут предложены 4 варианта проверки дубликатов в вашем листе Excel:
   • Дубликаты без первых вхождений
   • Дубликаты с 1-м вхождением
   • Уникальные значения
   • Уникальные значения и первые повторяющиеся вхождения

   Для этого примера давайте выберем второй вариант, т.е.е. Дубликаты + 1-е вхождение :
   

3. Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы выбираем первые 3 столбца:
   
4. Наконец, выберите действие, которое вы хотите выполнить с дубликатами. Как и в случае с инструментом Dedupe Table, мастер Duplicate Remover может идентифицировать , выбрать , выделить , удалить , скопировать или переместить дубликатов.

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

Мастеру Duplicate Remover требуется всего лишь доля секунды, чтобы проверить сотни строк и получить следующий результат:

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

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

Доступные загрузки

Identify Duplicates - примеры формул (файл .xlsx)
Ultimate Suite - пробная версия (файл .zip)

Вас также может заинтересовать

Выделение повторяющихся строк - Учебники по Excel

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

Выделить повторяющиеся строки в одном столбце

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

Чтобы выделить повторяющиеся строки, сначала выберите ячейки от A1 до A9.

Перейдите на HOME >> Стили >> Условное форматирование >> Выделите правила ячеек и выберите Дублирующиеся значения .

Появится новое окно.

Убедитесь, что вы выбрали Дублировать , и нажмите ОК.

Теперь выделены все повторяющиеся строки.

Проверить несколько столбцов

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

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

Давайте создадим еще один столбец, в котором мы объединим оба столбца, и назовем его Merged.

Введите следующую формулу в ячейку C2.

 = СЦЕПИТЬ (A2; B2) 

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

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

Чтобы выделить все столбцы ( A , B и C ), мы должны использовать правила.

1. Выберите ячейки от A1 до C10.
2. Перейдите на ГЛАВНУЮ СТРАНИЦУ >> Стили >> Условное форматирование и щелкните Новое правило… .
3. Щелкните Используйте формулу, чтобы определить, какие ячейки нужно форматировать .
4. Нажмите кнопку Format… , чтобы выбрать цвет.
5. Введите следующую формулу: = СЧЁТЕСЛИ ($ C $ 1: $ C $ 10, $ C1)> 1 .
6. Щелкните ОК.

Это последний пример.

Анализ формул:

Функция СЧЁТЕСЛИ принимает два аргумента:

Диапазон:

 = СЧЁТЕСЛИ ($ C $ 1: $ C $ 10, $ C1)> 1 

Критерии:

 = СЧЁТЕСЛИ ($ C $ 1: $ C $ 10, $ C1)> 1 

Как видите, критерий $ C1 . Перед числом 1 нет знака доллара. Это означает, что номер строки не является абсолютным. Другими словами, мы проверяем каждый критерий ( C1 , C2, … , C10) и сравниваем его с диапазоном ( C1: C10 ).

Прочтите этот урок, если хотите узнать больше об абсолютных и относительных ссылках на ячейки .

Просмотры сообщений: 56 481

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

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

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

Как найти повторяющуюся строку или данные

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

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

1. Нажмите Control + A , чтобы выбрать свой список или таблицу.Или щелкните значок Select All в верхнем левом углу таблицы, чтобы выделить весь рабочий лист.

2. Перейдите на вкладку Home и нажмите кнопку раскрывающегося меню Conditional Formatting .

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

4. Убедитесь, что в первом раскрывающемся списке указано «Дублировать». Вы также можете выбрать желаемый цвет выделения во втором раскрывающемся меню.

5. Выберите OK , чтобы продолжить.

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

Этот метод идеально подходит для поиска строк с повторяющимися значениями во всех столбцах или ячейках. Во-первых, вам нужно использовать функцию Excel «Concatenate», чтобы объединить содержимое каждой строки. Затем выберите столбец, в котором вы хотите сохранить комбинированные значения, и выполните следующие действия.Мы рекомендуем объединять значения в столбце рядом с последним значением в первой строке.

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

В нашем образце рабочего листа (см. Изображение ниже) первая и последняя ячейки в первой строке имеют ссылки A2 и D2 соответственно. Следовательно, формула будет иметь следующий вид: = CONCAT (A2: D2 ) .

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

2. Скопируйте формулу вниз по столбцу, пока не дойдете до последней строки. Для этого выберите ячейку с формулой, переместите курсор мыши в нижний левый угол ячейки, удерживайте значок Fill handle (т.е. значок плюса) и перетащите его вниз по столбцу.

3. Выберите весь столбец - щелкните букву вверху столбца или щелкните ячейку в столбце и нажмите Control + Space .

4. Перейдите на вкладку Home и выберите Условное форматирование .

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

6. Выберите OK , чтобы продолжить.

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

Если вы внимательно посмотрите на изображение выше, то заметите, что инструмент «Условное форматирование» не выделил Строка 4 и Строка 13 . Обе строки имеют повторяющиеся значения в столбцах Names , Scores и ID , но разные значения в столбце Day .

Только 3 из 4 столбцов в обеих строках содержат повторяющуюся информацию. Это объясняет, почему инструмент условного форматирования не выделяет конкатенированные или комбинированные значения для обеих строк.Обе строки (строка 4 и строка 13) уникальны, поскольку в столбце «День» содержится различительная информация.

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

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

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

• Выберите ячейку в таблице и нажмите Control + A на клавиатуре, чтобы выделить таблицу.

• Перейдите на вкладку Data и щелкните значок Remove Duplicates в разделе «Инструменты для работы с данными».

Если на вашем ПК маленький экран или окно Excel свернуто, нажмите кнопку раскрывающегося списка Data Tools и выберите Удалить дубликаты .

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

Совет: Чтобы сделать первую строку листа Excel заголовком, перейдите на вкладку View , выберите Freeze Panes и выберите Freeze Top Row .

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

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

• Выберите любую ячейку в таблице и нажмите Control + A , чтобы выделить всю таблицу.

• Перейдите на вкладку Data и выберите Advanced в разделе «Сортировка и фильтр».

• Установите флажок Только уникальные записи и щелкните ОК .

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

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

Итак, если на вашем листе или таблице Excel есть заголовок, лучше всего использовать инструмент «Удалить дубликаты», чтобы удалить повторяющиеся строки.

Подсказка: Случайно удалили повторяющиеся строки или значения? Нажмите Control + Z , чтобы отменить изменение и получить повторяющиеся данные.

Удаление дубликатов в Excel: ограничения

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

7 способов найти и удалить повторяющиеся значения в Microsoft Excel

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

Но найти и удалить их из данных в Excel на самом деле довольно просто.

В этом руководстве мы рассмотрим 7 различных методов поиска и удаления повторяющихся значений из ваших данных.

Видеоурок

Что такое повторяющееся значение?

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

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

В приведенном выше примере есть простой набор данных с 3 столбцами для марки, модели и года для списка автомобилей.

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

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

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

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

Выберите ячейку внутри данных, из которой вы хотите удалить дубликаты, перейдите на вкладку Data и щелкните команду Remove Duplicates .

Excel выберет весь набор данных и откроет окно «Удалить дубликаты».

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

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

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

Найдите и удалите повторяющиеся значения с помощью расширенных фильтров

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

Выберите ячейку внутри данных, перейдите на вкладку Data и щелкните команду Advanced filter.

Откроется окно расширенного фильтра.

1. Вы можете выбрать Фильтровать список на месте или Копировать в другое место . Фильтрация списка на месте скроет строки, содержащие любые дубликаты, а при копировании в другое место будет создана копия данных.
2. Excel угадывает диапазон данных, но вы можете изменить его в диапазоне Список . Диапазон критериев можно оставить пустым, а поле Копировать в необходимо будет заполнить, если была выбрана опция Копировать в другое место .
3. Установите флажок Только уникальные записи .

Нажмите OK , и вы удалите повторяющиеся значения.

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

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

Сводные таблицы предназначены только для анализа ваших данных, верно?

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

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

Сначала создайте сводную таблицу на основе ваших данных.Выберите ячейку внутри ваших данных или весь диапазон данных ➜ перейдите на вкладку Insert ➜ выберите PivotTable ➜ нажмите OK в диалоговом окне Create PivotTable.

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

Затем вам нужно будет изменить макет итоговой сводной таблицы, чтобы она имела табличный формат. Выбрав сводную таблицу, перейдите на вкладку Design и выберите Report Layout .Здесь вам нужно изменить два параметра.

1. Выберите опцию Показать в табличной форме .
2. Выберите параметр «Повторить все метки элементов ».

Вам также потребуется удалить все промежуточные итоги из сводной таблицы. Перейдите на вкладку Design ➜ выберите Промежуточные итоги ➜ выберите Не показывать промежуточные итоги .

Теперь у вас есть сводная таблица, имитирующая набор данных в виде таблицы!

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

Найдите и удалите повторяющиеся значения с помощью Power Query

Power Query - это преобразование данных, поэтому вы можете быть уверены, что он может находить и удалять повторяющиеся значения.

Выберите таблицу значений, из которой вы хотите удалить дубликаты ➜ перейдите на вкладку Data ➜ выберите запрос From Table / Range .

Удалить дубликаты на основе одного или нескольких столбцов

С помощью Power Query вы можете удалять дубликаты на основе одного или нескольких столбцов в таблице.

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

Щелкните правой кнопкой мыши заголовок выбранного столбца и выберите в меню Удалить дубликаты .

Вы также можете получить доступ к этой команде на вкладке Home ➜ Remove Rows ➜ Remove Duplicates .

  = Table.Distinct (# "Предыдущий шаг", {"Марка", "Модель"})  

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

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

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

В верхнем левом углу окна предварительного просмотра данных есть кнопка с набором команд, которые можно применить ко всей таблице.

Щелкните кнопку таблицы в верхнем левом углу ➜ затем выберите Удалить дубликаты .

  = Table.Distinct (# "Предыдущий шаг")  

Если вы посмотрите на созданную формулу, она использует ту же функцию Table.Distinct без второго параметра. Без второго параметра функция будет действовать для всей таблицы.

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

В Power Query также есть команды для сохранения дубликатов для выбранных столбцов или для всей таблицы.

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

Найдите и удалите повторяющиеся значения с помощью формулы

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

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

  = [@Make] & [@Model] & [@Year]  

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

  = TEXTJOIN ("", FALSE, CarList [@ [Make]: [Year]])  

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

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

  = СЧЁТЕСЛИМН ($ E $ 3: E3, E3)  

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

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

Добавьте фильтры к списку данных.

• Перейдите на вкладку Data и выберите команду Filter .
• Используйте сочетание клавиш Ctrl + Shift + L.

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

Затем вы можете выбрать видимые ячейки из полученного фильтра для копирования и вставки в другое место. Используйте сочетание клавиш Alt +; для выбора только видимых ячеек.

Найти и удалить повторяющиеся значения с условным форматированием

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

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

Затем нужно выбрать столбец объединенных данных.

Чтобы создать условное форматирование, перейдите на вкладку Home ➜ выберите Conditional Formatting ➜ Highlight Cells Rules ➜ Duplicate Values ​​.

Откроется окно условного форматирования повторяющихся значений.

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

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

Теперь, когда значения выделены, вы можете фильтровать повторяющиеся или уникальные значения с помощью параметра фильтра по цвету. Обязательно добавьте фильтры к своим данным. Перейдите на вкладку Data и выберите команду Filter или используйте сочетание клавиш Ctrl + Shift + L.

1. Щелкните переключатель фильтра.
2. Выберите в меню Фильтр по цвету .
3. Отфильтруйте цвет, используемый в условном форматировании, чтобы выбрать повторяющиеся значения, или отфильтруйте параметр «Нет заливки» для выбора уникальных значений.

Затем вы можете выбрать только видимые ячейки с помощью сочетания клавиш Alt +;.

Найдите и удалите повторяющиеся значения с помощью VBA

В VBA есть встроенная команда для удаления дубликатов в объектах списка.

  Sub RemoveDuplicates ()   Dim DuplicateValues ​​As Range   Установить DuplicateValues ​​= ActiveSheet.ListObjects ("CarList"). Диапазон   DuplicateValues.RemoveDuplicates Столбцы: = Массив (1, 2, 3), Заголовок:  Концевой переходник  

Приведенная выше процедура удалит дубликаты из таблицы Excel с именем CarList .