0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро отсортировать строки для соответствия другому столбцу в Excel?

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Как отфильтровать список элементов из длинного-длинного списка

Фильтрация длинного списка-лого

Что делать, когда сталкиваешься с большой таблицей? В Excel есть как минимум два метода решения этой задачи. Во-первых, использовать функции поиска по частичному совпадению с формулами. Второй метод дает аналогичный результат без формул — его мы и рассмотрим в сегодняшнем выпуске Exceltip. Добро пожаловать в Advanced Filter, да, детка!

Расширенный фильтр

Команда находится на вкладке «Данные» рядом с обычным фильтром.

Задача — выделим из длинного списка только те строки, в которых встречается слово Фреза или Сверло

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

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

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

… по ключевым словам Excel сам находит именно то, что нужно. Если ввести в поле поиска “фреза“, Фильтр покажет только те элементы, в которых есть это слово. Также можно добавлять новые поисковые запросы к предыдущим, установив флажок «добавить выделенный фрагмент в фильтр“.

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

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

Перед выполнением команды должны быть выполнены несколько условий. Это отдельный список имен, которые должны быть включены в таблицу. В примере это ячейки E2:E4

Условная область должна содержать названия колонок!

диапазон условий

Нажмите вкладку Данные -> Сортировка и фильтр -> Расширенные

Читайте так же:
Как ВПР затем умножить в таблицах Excel?

Сортировка и фильтр

В этом примере я использую «Копировать в другое место». При желании можно использовать «Фильтровать список на месте», то есть в исходном месте.

Список должен быть длинее

Таким образом, Расширенный фильтр выбирает только строки, начинающиеся с «фреза» или «сверло».

Для включения всех строк, содержащих имя, используется подстановочный знак *. Если вы включите две «звездочки» (*), Excel интерпретирует их как все, где есть символы «резец» или «сверло».

Символ подстановки

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

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

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

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

  1. Имена, начинающиеся с «cutter»
  2. Имена, заканчивающиеся на «cutter».

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

E3- * фреза * ‘Содержит слово фреза (только отдельно и не в начале или конце)

E4 — *сверло* ‘Содержит слово сверло (в любом месте)

E5 — =»=фреза *» ‘Начинается со слова фреза

E6 — =»=*фреза» ‘Заканчивается словом фреза

Условия по полной

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

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

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

Вам также могут быть интересны следующие статьи

2 комментария

Ренат, приветствую!
Вот мне прям именно это и нужно — фильтровать по списку слов, но вот только не в Экселе, а в LibreOffice.
Там вроде все менюшки по аналогии с Экселем, но вот именно «Расширенный фильтр» выглядит по-другому, и у меня не получается перенести Ваш алгоритм туда =(

Читайте так же:
Как быстро преобразовать время в текст или количество часов / минут / секунд в Excel?

А почему вы не стали использовать: Фильтр — Текстовые фильтры — Содержит — «фраза»?
Вроде бы очевидный способ найти в столбце упоминания фразы

Как сравнить два столбца в Excel на совпадения

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

Как сравнить два столбца в Excel по строкам

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

Пример 1. Как сравнить два столбца на совпадения и различия в одной строке

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

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

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

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

Примером результата расчета может быть:

Поиск различий в двух столбцах Excel

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

Как сравнить несколько столбцов на совпадения в одной строке Excel

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

  • Найти строки с одинаковыми значениями во всех столбцах таблицы
  • Найти строки с одинаковыми значениями в двух столбцах
Читайте так же:
Как быстро изменить размер нескольких ячеек, чтобы они соответствовали изображениям в Excel?

Пример1. Как найти совпадения в одной строке в нескольких столбцах таблицы

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

Поиск различий в двух столбцах Excel

Если наша таблица содержит несколько столбцов, мы можем использовать функцию READ в сочетании с IF:

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

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

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

Поиск различий в двух столбцах Excel

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

=ЕСЛИ(СЧЁТЕСЛИ(B2:D2;A2)+СЧЁТЕСЛИ(C2:D2;B2)+(C2=D2)=0; «Уникальная строка»; «Не уникальная строка»)

Первая функция СЧЁТЕСЛИ вычисляет количество столбцов в строке со значением в ячейке А2 , вторая функция СЧЁТЕСЛИ вычисляет количество столбцов в таблице со значением из ячейки B2 . Если результат вычисления равен «0» — это означает, что в каждой ячейке, каждого столбца, этой строки находятся уникальные значения. В этом случае формула выдаст результат «Уникальная строка», если нет, то «Не уникальная строка».

Поиск различий в двух столбцах Excel

Как сравнить два столбца в Excel на совпадения

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

=ЕСЛИ(СЧЁТЕСЛИ($B:$B;$A5)=0; «Нет совпадений в столбце B»; «Есть совпадения в столбце В»)

Поиск различий в двух столбцах Excel

Эта формула сравнивает значения в столбце В с ячейками в столбце А.

Если таблица состоит из фиксированного количества строк, в формуле можно указать явный диапазон (например, $B2:$B10 ). Это ускорит работу формулы.

Как сравнить два столбца в Excel на совпадения и выделить цветом

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

Читайте так же:
Быстро создавать случайные группы для списка данных в Excel

Поиск и выделение совпадений цветом в нескольких столбцах в Эксель

В случаях, когда нам нужно найти соглашения в нескольких столбцах, нам необходимо

  • Выделить столбцы с данными, в которых нужно вычислить совпадения;
  • На вкладке «Главная» на Панели инструментов нажимаем на пункт меню «Условное форматирование» -> «Правила выделения ячеек» -> «Повторяющиеся значения»;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке пункт «Повторяющиеся», в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку «ОК»:
  • После этого в выделенной колонке будут подсвечены цветом совпадения:

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

Поиск и выделение цветом совпадающих строк в Excel

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

поиск и подсветка дублирующихся строк в Эксель - 1поиск и подсветка дублирующихся строк в Эксель

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

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

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

В дополнительном столбце приведены объединенные данные из таблицы:

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

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

  • Выделите область с данными во вспомогательной колонке (в нашем примере это диапазон ячеек E2:E15 );
  • На вкладке «Главная» на Панели инструментов нажимаем на пункт меню «Условное форматирование» -> «Правила выделения ячеек» -> «Повторяющиеся значения»;
  • Во всплывающем диалоговом окне выберите в левом выпадающем списке «Повторяющиеся», в правом выпадающем списке выберите каким цветом будут выделены повторяющиеся значения. Нажмите кнопку «ОК»:
  • После этого в выделенной колонке будут подсвечены дублирующиеся строки:
Читайте так же:
Как вставить водяной знак в Excel?

поиск дубликатов строк в Эксель

В примере выше мы выделили строки созданного вспомогательного столбца.

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

Для этого выполните следующее:

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

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

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

  • Теперь, выделим все данные таблицы (за исключением вспомогательного столбца). В нашем случае это ячейки диапазона A2:D15 ;
  • Затем, на вкладке «Главная» на Панели инструментов нажмем на пункт «Условное форматирование» -> «Создать правило»:

создать правило условия форматирования в excel

  • В диалоговом окне «Создание правила форматирования» кликните на пункт «Использовать формулу для определения форматируемых ячеек» и в поле «Форматировать значения, для которых следующая формула является истинной» вставьте формулу:

новая формула в условном форматировании

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

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

дубликаты строк в excel

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector