Poldet1.ru

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

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

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

x

x

x

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

У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные — Фильтр (Data — Filter) :

advanced-filter1.png

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

Основа

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

advanced-filter2.png

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

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:

advanced-filter3.png

Чтобы выполнить фильтрацию, выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите Дополнительно (Данные — Дополнительно) . В открывшемся окне необходимо выбрать диапазон условий, автоматически введя диапазон данных. A1:I2:

advanced-filter5.png

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

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

Читайте так же:
Как разделить / разделить буквенно-цифровые строки на два столбца в Excel?

advanced-filter6.png

Добавляем макрос

"Ну и где же тут удобство?" — спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" — макросы!

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

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

Так намного лучше, не так ли? 🙂

Реализация сложных запросов

Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

КритерийРезультат
гр* или грвсе ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д.
=луквсе ячейки именно и только со словом Лук, т.е. точное совпадение
*лив* или *ливячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*вслова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*сслова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д.
=*сслова оканчивающиеся на С
=.все ячейки с текстом из 4 символов (букв или цифр, включая пробелы)
=м. нвсе ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостини т.д.
=*н??авсе слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д.
>=эвсе слова, начинающиеся с Э , Ю или Я
<>*о*все слова, не содержащие букву О
<>*вичвсе слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству)
=все пустые ячейки
<>все непустые ячейки
>=5000все ячейки со значением больше или равно 5000
5 или =5все ячейки со значением 5
>=3/18/2013все ячейки с датой позже 18 марта 2013 (включительно)
  • * — * обозначает любое количество всех символов, а ? — один из всех персонажей.
  • Логика обработки текстовых и числовых запросов немного отличается. Например, ячейка условия с числом 5 не ищет все числа, начинающиеся с пяти, а ячейка условия с буквой B соответствует B*, то есть ищет любой текст, начинающийся с буквы B.
  • Если текстовый запрос не начинается с =, вы можете мысленно поставить * в конце.
  • Даты должны быть введены в формате месяц-день-год по штату и с дробями (даже если у вас русский Excel и региональные настройки).
Читайте так же:
Как проверить, есть ли в ячейке изображение в Excel?

Логические связки И-ИЛИ

Термины, записанные в разных ячейках, но в одном ряду, считаются связанными оператором AND:

advanced-filter3.png

Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".

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

advanced-filter7.png

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

advanced-filter8.png

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

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