Загрузка...Как преобразовать текстовую строку в правильный регистр с исключениями в Excel?
Как преобразовать текстовую строку в правильный регистр с исключениями в Excel?
Расширенный фильтр и немного магии
У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные — Фильтр (Data — Filter) :
Такой фильтр — штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).
Основа
Сначала вставьте несколько пустых строк над таблицей данных и скопируйте в них заголовок таблицы, который будет диапазоном с условиями (выделен желтым цветом для наглядности):
Между желтыми ячейками и исходной таблицей должна быть как минимум одна пустая строка.
Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:
Чтобы выполнить фильтрацию, выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите Дополнительно (Данные — Дополнительно) . В открывшемся окне необходимо выбрать диапазон условий, автоматически введя диапазон данных. A1:I2:
Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка — как просьба вывести все данные без разбора.
С помощью переключателя Копировать результат в другую область вы не можете фильтровать список сразу на этом листе (как при обычном фильтре), но вы можете переместить выбранные строки в другую область, которую затем следует указать в поле Поместить результат в область. В данном случае мы не будем использовать эту функцию, оставим список фильтров на месте и нажмем OK. Выбранные строки отображаются на листе:
Добавляем макрос
"Ну и где же тут удобство?" — спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" — макросы!
Расширенный фильтр можно сделать намного быстрее и проще, используя простой макрос, который будет автоматически запускать расширенный фильтр при введении условий, т.е. при изменении желтой ячейки. Щелкните правой кнопкой мыши на ярлыке текущего листа и выберите Исходный код . В появившемся окне скопируйте и вставьте следующий код:
Эта процедура будет автоматически запускаться при изменении любой ячейки в текущем рабочем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), этот макрос удалит все фильтры (если таковые имеются) и применит расширенный фильтр к исходной таблице данных, начиная с A7, т.е. все будет отфильтровано мгновенно, сразу после ввода следующего условия:
Так намного лучше, не так ли? 🙂
Реализация сложных запросов
Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:
| Критерий | Результат |
| гр* или гр | все ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д. |
| =лук | все ячейки именно и только со словом Лук, т.е. точное совпадение |
| *лив* или *лив | ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д. |
| =п*в | слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д. |
| а*с | слова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д. |
| =*с | слова оканчивающиеся на С |
| =. | все ячейки с текстом из 4 символов (букв или цифр, включая пробелы) |
| =м. н | все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостини т.д. |
| =*н??а | все слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д. |
| >=э | все слова, начинающиеся с Э , Ю или Я |
| <>*о* | все слова, не содержащие букву О |
| <>*вич | все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству) |
| = | все пустые ячейки |
| <> | все непустые ячейки |
| >=5000 | все ячейки со значением больше или равно 5000 |
| 5 или =5 | все ячейки со значением 5 |
| >=3/18/2013 | все ячейки с датой позже 18 марта 2013 (включительно) |
- * — * обозначает любое количество всех символов, а ? — один из всех персонажей.
- Логика обработки текстовых и числовых запросов немного отличается. Например, ячейка условия с числом 5 не ищет все числа, начинающиеся с пяти, а ячейка условия с буквой B соответствует B*, то есть ищет любой текст, начинающийся с буквы B.
- Если текстовый запрос не начинается с =, вы можете мысленно поставить * в конце.
- Даты должны быть введены в формате месяц-день-год по штату и с дробями (даже если у вас русский Excel и региональные настройки).
Логические связки И-ИЛИ
Термины, записанные в разных ячейках, но в одном ряду, считаются связанными оператором AND:
Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".
Для этого нужно просто ввести условия в разные строки. Например, если нам нужно найти все заказы менеджера Волина на московские персики и все заказы на лук в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:
Если вы хотите наложить два или более условий на столбец, вы можете просто продублировать заголовок столбца в диапазоне критериев и написать под ним второе, третье и т.д. условие. Так, например, можно выбрать все транзакции с марта по май:
В общем и целом, после "доработки напильником" из расширенного фильтра выходит вполне себе приличный инструмент, местами не хуже классического автофильтра.
