Mini-ats102.ru

ООО “Мультилайн”
2 просмотров
Рейтинг статьи
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.

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

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

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

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

Читайте так же:
Как в excel создать таблицу умножения

Как включить фильтр в excel

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

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

  1. Скопируйте из списка заголовки фильтруемых столбцов в первую пустую строку диапазона условий отбора.
  2. Введите в строки под заголовками столбцов требуемые критерии отбора. Убедитесь, что между значениями условий и списком находится как минимум одна пустая строка.
  3. Укажите ячейку в списке и дайте команду Данные => Фильтр => Расширенный фильтр.
  4. В открывшемся окне установите переключатель Обработка в положение либо Фильтровать список на месте (чтобы показать результат фильтрации, скрыв ненужные строки), либо Скопировать результаты в другое место (чтобы скопировать отфильтрованные строки в другую область листа). Во втором случае перейдите в поле Поместить результат в диапазон, а затем укажите верхнюю левую ячейку области вставки.
  5. Введите в поле Диапазон условий ссылку на ячейки, содержащие условия отбора, включая заголовки столбцов. Чтобы убрать диалоговое окно Расширенный фильтр на время выделения диапазона условий отбора, нажмите кнопку свертывания диалогового окна.
  6. Щелкните по кнопке ОК.

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

Примеры условий отбора расширенного фильтра

Диапазон условий состоит, по крайней мере, из одной строки подписей условий и, по крайней мере, одной строки собственно условий.

Если на листе существует диапазон с именем Критерии, то в поле Диапазон условий автоматически появится ссылка на этот диапазон.

В условия отбора расширенного фильтра может входить

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

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

Несколько условий для одного столбца.

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

Одно условие для нескольких столбцов.

Чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения "Продукты" в столбце «Товар», "Белов" в столбце «Продавец» и объем продаж более 1000р.

ТоварПРОДАВЕЦПродажи
ПродуктыБелов>1000

Разные условия для разных столбцов.

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

Например, следующий диапазон условий отбора отображает все строки, содержащие значение "Продукты" в столбце «Товар», "Белов" в столбце «Продавец», либо объем продаж, превышающий 1 000р.

Читайте так же:
Метод левых прямоугольников в excel
ТоварПРОДАВЕЦПродажи
Продукты
Белов
>1000

Один из двух наборов условий для двух столбцов

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

Например, следующий диапазон условий отбора отображает строки, содержащие как значение "Белов" в столбце «Продавец», так и объем продаж, превышающий 3 000р., а также строки по продавцу Батурину с продажами более 1 500р.

ПРОДАВЕЦПродажи
Белов>3000
Батурин>1500

Таким образом можно сформулировать правила соединения условий следующим образом:

— условия, записанные на одной строке, считаются соединенными логическим оператором И (будут отобраны строки, для которых выполняются оба условия);

— условия, записанные на разных строках, считаются соединенными логическим оператором ИЛИ (будут отобраны строки, для которых выполняются хотя бы одно из условий).

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

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

Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце L значение, превышающее среднее значение ячеек диапазона L8: L24; заголовок условия отбора не используется.

=L8>СРЗНАЧ($L$8:$L$24)

Внимание! В этом случае условия отбора должны содержать ДВЕ ячейки.

Формула, используемая для создания условия отбора, должна использовать для ссылки на подпись столбца (например, «Продажи») или на соответствующее поле в первой записи относительную ссылку. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В примере формулы "L8" является ссылкой на поле (столбец L) первой записи списка.

При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Пример1.

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

  1. Добавьте новый столбец:
    • в ячейку N7 введите заголовок Стаж работы в фирме,
    • в ячейку N8 — формулу: = РАЗНДАТ(M8;СЕГОДНЯ();"Y") , которая вычисляет полное количество лет между датой приема на работу и датой сегодня.

Пример2

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

  1. Скопируйте в ячейку А1 заголовок столбца Пол, а ячейку В1 оставить пустой.
  2. Под заголовками столбцов введите условия:
    • в ячейку А2 – Ж,
    • в ячейку В2 — =(СЕГОДНЯ()-J8)/365,25>55 (т.е. формулу, сравнивающую возраст с 55); в ячейку А3 – М,
    • в ячейку В3 — =(СЕГОДНЯ()-J7)/365,25>60 (т.е. формулу, сравнивающую возраст с 60).
      В ячейках при этом отобразится результат сравнения для первой ячейки анализируемого столбца: в В2 — ИСТИНА, а в В3 — #ЗНАЧ!, что не влияет на дальнейшую работу фильтра
  3. Установите курсор в любую ячейку списка и дайте команду Данные =>Фильтр =>Расширенный фильтр. Остальные параметры оставьте по умолчанию.
  4. Щелкнув мышью в окне Диапазон условий, выделите ячейки от А1 до В3 (рис. 7.11).

Пример 3

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

  1. Скопируйте в ячейку В1 заголовок столбца Стаж работы в фирме, а ячейку А1 оставьте пустой.
  2. Под заголовками столбцов введите условия:
    • в ячейку В2 – >8
    • в ячейку А2 — =(СЕГОДНЯ()-J8)/365,25<40 (т.е. формулу, сравнивающую возраст с 40);
    • В ячейке А2 при этом отобразится результат сравнения для первой ячейки анализируемого столбца ЛОЖЬ, что не влияет на дальнейшую работу фильтра
  3. Установите курсор в любую ячейку списка и дайте команду Данные =>Фильтр =>Расширенный фильтр. Выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон и выделите первую ячейку диапазона для размещения результата фильтрации. Отобранные данные будут помещены в диапазон, начинающийся с указанной ячейки
  4. Щелкнув мышью в окне Диапазон условий, выделите ячейки от А1 до В2 (рис. 7.12).

Фильтр для уникальных записей

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

  1. Выберите столбец или ячейку в списке, который требуется отфильтровать. Убедитесь, что диапазон ячеек содержит заголовок столбца
  2. В меню Данные выберите команду Фильтр, а затем — команду Расширенный фильтр.
    Отобразится диалоговое окно Расширенный фильтр.
  3. Выполните одно из следующих действий:
    • Чтобы отфильтровать список на месте, как и при использовании функции Автофильтр, выберите вариант Фильтровать список на месте.
    • чтобы скопировать результат действия фильтра в другое место, выберите вариант скопировать результат в другое место и введите в поле Поместить результат в диапазон ссылку на ячейки. В противном случае нажмите кнопку Свернуть диалоговое окно для временного скрытия диалогового окна, выберите ячейку на листе, а затем нажмите кнопку Развернуть диалоговое окно .
  4. Установите флажок Только уникальные записи и нажмите кнопку ОК.

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

Пример

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

  1. Выделите столбец В с заголовком, т.е. диапазон В8:В24.
  2. Дайте команду Данные =>Фильтр =>Расширенный фильтр. Выберите вариант Скопировать результат в другое место, щелкните в поле Поместить результат в диапазон и выделите первую ячейку диапазона для размещения результата фильтрации. Отобранные данные будут помещены в диапазон, начинающийся с указанной ячейки . Поставьте флажок в поле Только уникальные записи (рис. 7.13) и нажмите кнопку ОК.

ms excel

Как сделать расширенный фильтр в Excel? Чтобы было понятно, каким образом происходит процедура и как она делается, рассмотрим пример.

Инструкция по расширенной фильтрации электронной таблицы:

  1. Необходимо создать место выше основной таблицы. Там и будут располагаться результаты фильтрации. Должно быть достаточное количества места для готовой таблицы. Также требуется еще одна строка. Она будет разделять отфильтрованную таблицу от основной.
  2. В самую первую строку освобожденного места скопировать всю шапку (названия колонок) основной таблицы.
  3. Ввести необходимые данные для фильтрации в нужный столбец. Отметим, что запись должна выглядеть следующим образом: = «= фильтруемое значение».
  4. Теперь необходимо пройти в раздел «Данные». В области фильтрации (значок в виде воронки) выбрать «Дополнительно» (находится в конце правого списка от соответствующего знака).
  5. Далее во всплывшем окошке нужно ввести параметры расширенного фильтра в Excel. «Диапазон условий» и «Исходный диапазон» заполняются автоматически, если была выделена ячейка начала рабочей таблицы. Иначе их придется вводить самостоятельно.
  6. Нажать на Ок. Произойдет выход из настроек параметров расширенной фильтрации.

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

работа с расширенным фильтром

Заключение

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

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

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

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

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

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

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

Параметры фильтра в автофильтре в Excel

Автофильтр в Excel позволяет нам просматривать определенные строки в Excel, скрывая при этом другие строки. Когда Excel AutoFilter добавляется к заголовку строки, он дает нам раскрывающееся меню в строке заголовка.

Автофильтр в Excel дает нам несколько вариантов фильтрации, таких как:

  1. Равно
  2. Лучше чем
  3. Меньше, чем
  4. Больше или равно
  5. Начинается с
  6. Заканчивается на
  7. Содержит
  8. Не содержит

Первые четыре — это количество фильтров, а остальные — текстовые фильтры.

2 способа включить автофильтр в Excel

Есть два способа использовать автоматический фильтр в Excel:

1. В данных перейдите на вкладку «Фильтр» в разделе «Сортировка и фильтрация».

пример автофильтра 1

2. Ярлык Excel — нажмите Ctrl + Shift + L.

пример автофильтра 1-1

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

Как использовать автоматические фильтры Excel?

Давайте изучим использование автоматических фильтров Excel на нескольких примерах:

Автофильтр в Excel — Пример №1

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

Автоматический фильтр в Excel, пример 1

Задача состоит в том, чтобы определить, в каком городе количество изображений 33, и у брокера Prateek есть это свойство.

  • Щелкните заголовок любой строки в строке 1.

Автоматический фильтр в Excel, пример 1-1

  • Теперь на вкладке «Данные» нажмите «Фильтры» в разделе сортировки и фильтрации.

Автоматический фильтр в Excel, пример 1-2

  • В заголовке строки применяется фильтр, и он дает нам раскрывающееся меню.

Автоматический фильтр в Excel, пример 1-3

  • Отмените выбор всего количества, сняв флажок «Выбрать все» и выберите 33 для количества изображений.
  • Теперь в столбце Брокер выберите брокера как Prateek, сняв отметку «Выбрать все» и выбрав Prateek.
  • Теперь у нас есть города с 33 фотографиями, и у брокера Prateek есть эти объекты.

Автоматический фильтр в Excel, пример 1-6

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

У нас есть желаемые данные, какими мы хотели их видеть.

Автофильтр в Excel — Пример №2

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

Автоматический фильтр в Excel, пример 2

  • На этот раз мы будем использовать другой подход к использованию фильтров.
  • В заголовке строки нажмите «Ctrl» + «Shift» + «L.»

Автоматический фильтр в Excel, пример 2-1

  • После применения фильтра в столбце математики снимите флажок «Выбрать все» и выберите значения выше 50.
  • Теперь выберите значения в сумме выше 200, сняв флажок «Выбрать все» и выбрав значения выше 300.
  • Теперь у нас есть те ученики, которые набрали больше 50 баллов по математике, а всего больше 300.

Автоматический фильтр в Excel, пример 2-4

Автофильтр в Excel — Пример №3

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

Но сначала, что такое настраиваемый фильтр?

Пользовательский фильтр — это опция в фильтрах Excel, которая позволяет нам устанавливать определенные условия для получения наших значений. Например, в примере 2 условие отмечено выше 50 по математике или всего выше 300 по общей сумме оценок.

Давайте изучим кастомный фильтр на примере 2.

  • Выберите любую ячейку в заголовке строки.
  • Нажмите Ctrl + Shift + L.
  • В математике фильтр нажмите на числовые фильтры. Поскольку данные представлены в цифрах, появляется диалоговое окно.

Пример 3

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

Пример 3-1

Напишите 50 в поле, где стоит текст «больше чем».

  • Excel автоматически фильтрует данные, в которых оценки по математике выше 50.

Пример 3-3

  • Теперь в столбце итогов используйте тот же подход и запишите итог больше 300.

Пример 3-4

У нас есть результат, в котором оценки по математике выше 50, а общая выше 300.

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