Youtubezilla.ru

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

Финансы в Excel

Содержание
Файл
Как это работает
Ограничения
Вложения:

multilevelgroups.xls[Многоуровневые группы]44 kB

Еще один пример, расширяющий грани возможного в Excel.

Требуется создать фильтр, отбирающий записи в таблице сгруппированных по определенному полю. Главное условие — группы могут быть вложены одна в другую. Задача по описанию проста, встречается повсеместно, но реализовать нормальную работу в электронных таблицах на практике достаточно проблематично. В программных комплексах для решения этой задачи используются служебные таблицы соответствий «родитель-потомок» (все ко всем), либо аналогичные по функциональности структуры в оперативной памяти. В Excel можно использовать автофильтр с множественной выборкой подчиненных групп вручную. Этот же алгоритм можно попытаться автоматизировать, используя программную проверку элементов фильтра. Решение вполне реализуемо, но по факту смотрится не очень красиво, так как в Excel не реализован вызов события на изменение автофильтра. Придется добавлять специальную кнопку или искать еще какое-то не самое красивое решение с точки зрения пользовательского интерфейса. Можно также попытаться совсем не использовать встроенные возможности фильтрации Excel, вместо этого полностью реализовать проверку, скрытие и показ нужных строк с данными средствами VBA. Но это не наш метод — мы не используем программирование, там где без него можно обойтись.

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

В приложении к статье файл multilevelgroups.xls с решением задачи. Работает во всех версиях Excel, начиная с 2000 (10.0) (по идее Excel 2011 for Mac тоже должно работать, пока нет возможности проверить). Для автоматического обновления таблицы данных по выбору из списка необходимо подключить макросы Excel.

Читайте так же:
Нумерация страниц в Excel: как сделать или убрать

В файле 2 таблицы, первая — справочник многоуровневых групп, вторая — пример таблицы с данными (Группа-Продукт). Для фильтрации групп использовано поле со списком.

Можно добавлять данные в середину справочника групп через копирование строк целиком. Также можно менять названия и подчиненность. Помните, что при переименовании родительской группы, значения в подчиненных группах (поле Parent) автоматически не меняется. Для переименования можно использовать замену данных, либо привязать подчиненную группу через формулу со ссылкой на название родителя. Справочник групп не содержит никаких дополнительных полей для ввода, кроме названия и ссылки на родительсий элемент. В скрытых столбцах содержатся формулы, позволяющие решить задачу фильтрации данных без программирования. Для показа скрытых столбцов проще всего нажать на кнопку [+] структуры документа.

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

В таблице с условными данными в заголовке поля Group располагается элемент управления типа раскрывающийся список. Взят за основу контрол из коллекции Forms, а не ActiveX Combobox — для возможности использования в Excel for Mac. Переделать пример на ActiveX Combobox не представляет особого труда. В свойствах контрола указан источник значений списка ($D$2:$D$19) и служебная ячейка записи номера выбранного элемента ($H$1)

Стандартный фильтр

Стандартный фильтр предназначен для более сложных выборок из таблицы. С помощью предыдущего фильтра (автофильтра) можно отбирать только из предлагаемых значений. Но что делать, если нужно отобрать записи в заданном диапазоне? Автофильтр этого сделать не может, а стандартный фильтр может. Для включения стандартного фильтра выполните команду ДанныеФильтрСтандартный фильтр. После этого открывается окно Стандартный фильтр (Рис. 4), а вся таблица с данными автоматически выделяется.

Читайте так же:
Элементы Яндекса для Internet Explorer

Рис. 4. Окно Стандартный фильтр

Для настройки фильтра откройте список Имя поля и выберите столбец, по которому необходимо выполнить выборку. В нашем примере использован столбец Цена. В столбце Условие выбирается логическая операция, с помощью которой будет выполняться фильтрация. В столбце Значение указывается значение, участвующее в расчете. В этом списке предлагаются все значения выбранного столбца (в нашем примере Цена), но пользователь может ввести любое значение с клавиатуры. Например, в нашем примере мы собираемся найти все записи, в которых Цена больше 300. В таблице нет такой цены, поэтому мы вводим ее вручную.

Заполнив одно условие можно его усложнить во второй (третьей. и т. д.) записях отбора. Для этого в столбце Оператор (самый крайний левый столбец) нужно выбрать логическое условие, соединяющее новую строку условия с созданной выше строкой условия. В качестве оператора предлагаются операторы «И» и «ИЛИ». Только после этого становятся доступными параметры этой строки условия. В нашем примере мы хотим найти записи, в которых Цена больше 300, а процент скидки меньше 5%.

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

Нажав на кнопку Детали можно открыть дополнительные параметры отбора. Обязательно установите флажок в опцию Поместить результат в. Если этого не сделать, то таблица с выборкой затрет исходную таблицу и можно потерять данные. После этого становятся доступными нижележащие параметры. Нажмите на кнопку Свернуть/Развернуть (). Окно Стандартный фильтр сворачивается в строку, чтобы не мешать выделению ячеек. Под таблицей с данными выделите несколько строк (любое число) по ширине таблицы. Это будет выглядеть, как на рисунке (Рис. 5).

Читайте так же:
Как настроить звуки Яндекс навигатора включить звук

Рис. 5. Выделение ячеек для результата выборки

После выделения ячеек для результата нажмите на кнопку Свернуть/Развернуть (). После этого программа снова возвращается в полномасштабное окно Стандартный фильтр. Если все параметры фильтрации определены, то нажмите на кнопку ОК. Если все было выполнено правильно, то исходная и выходная таблицы выглядят примерно так (Рис. 6).

Рис. 6. Исходная и выходная таблицы

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

Некорректная структура таблицы

Постарайтесь «причесать» свою табличку:

  • Удалите пустые строки. Система их воспринимает, как разрыв таблицы, что сбивает сортировку;
  • Уберите объединенные ячейки (сведите их количество к предельно допустимому минимуму). Если фильтрация была настроена, когда клеточки «жили» по отдельности, после их слияния она может работать некорректно;
  • Приведите структуру в четкий вид.

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

  • Выключите фильтр («Главная» – «Сортировка и Фильтр» или «Ctrl+Shift+L»);
  • Выделите весь диапазон ячеек (всю таблицу, вместе с шапкой);
  • Снова поставьте фильтрацию, не снимая выделение;
  • Готово. Должно работать, даже с пустыми строчками.

AutoFilter Data Range with Multiple Criteria

If you want to filter a field with multiple criteria, you have to use Criteria1 and Criteria2 parameters, but also the Operator xlAnd.

In the next example, we will filter the first column (Date) for dates in December 2018. Therefore, we have two criteria: a date greater than 12/01/18 and less than 12/31/18. This is the code:

Читайте так же:
Как удалить свою группу или сообщество в ВК

When we execute the code, you can see that only dates in December are displayed in the data range:

vba autofilter multiple criteria

Image 5. AutoFilter with multiple criteria for the field

Где в Excel фильтры — их виды

Найти фильтры в этой программе легко — нужно открыть главное меню или просто зажать клавиши Ctrl+Shift+L.

Где в Excel фильтры

Основные функции фильтрации в Excel:

  • отбор по цвету: дает возможность отсортировать данные по цвету шрифта или заливки,
  • текстовые фильтры в excel: позволяют задать те или иные условия для строк, например: меньше, больше, равно, не равно и другие, а также задать логические условия — и, или,
  • числовые фильтры: отсортировывают по числовым условиям, например, ниже среднего, первые 10 и другие,
  • ручной: отбор можно выполнять по выбранным самостоятельно критериям.

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

Как использовать расширенный фильтр в Excel

Как задать несколько параметров

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

Например, мы уже отфильтровали таблицу по условию «Страна» и оставили только товары российского происхождения.

Теперь необходимо дополнительно отделить товары стоимостью 100 рублей, для этого применяем отсев в разделе «Цена».

Нажимаем «Ок» и получаем только товары стоимостью 100 рублей, произведенные в России.

Сортировка в Excel

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

Читайте так же:
0xc0000098 Windows 7 при запуске как исправить?

сортировка

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

сортировка по возрастанию

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

Одна мысль про “Фильтр и сортировка в Excel”

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

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