Применение расширенного фильтра

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

Команда выполняет следующие действия:

1. Отбор данных по одному и более критериям (полям).

2. Отфильтрованные данные переносит на другое место вне таблицы или при желании пользователя оставляет в таблице.

Перед вызовом команды Расширенный фильтр необходимо создать три блока с данными: базу данных, блок критериев и блок вывода.

Рассмотрим подготовку блоков критериев и вывода.

Блок критериев

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

Блок вывода

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

Пример 4.Создать список семейных мужчин.

Критерий по двум полям: Пол и Семейное положение, причем они расположены на одной строке, так как требуется одновременное выполнение условий и связь функцией “ И“.

Выполняемые действия.

1. Создать блок критериев на любом пустом месте вне таблицы, например в ячейках B21:C22. При этом в ячейки B21 и С21 поместить копированием из таблицы имена полей Пол и Семейное положение, по которым создаются критерии. В ячейки B22 и С22 занести условия отбора (критерии) - «м» и «б» (Рисунок 16).

2. Создать блок вывода также на любом пустом месте вне таблицы. Нужно только указать имена выводимых полей. т. е. шапку будущей таблицы. Для этого, например, в ячейки E21:H21 копированием из таблицы перенести имена произвольно выбранных полей ФИО, Пол, Семейное положение, Средняя зарплата (для контроля включены имена полей « Пол, Семейное положение»).

3. Курсор установить в любую ячейку таблицы.

4. Выполнить команду Данные, Дополнительно.

5. В появившемся окне установить флажок Скопировать результат в другое место.

6. В нижней части окна выделить мышью блоки с таблицей A1:H11, блок критериев B21:С22,блок вывода E21:H21(Рисунок15).

7. Нажать кнопку ОК

Рисунок 15. Задание диапазонов

Рисунок16. Критерии и блок вывода по двум полям с помощью функции “И”

Пример 5. Требуются на работу либо уроженцы города Курска, либо холостые.

Критерий отбора – по полям Место рождения и Семейное положение, причем они располагаются на разных строках, так как достаточно выполнения хотя бы одного условия, следовательно, осуществляется связь функцией "ИЛИ". На рисунке 17 даны критерии и отобранные данные, а на рисунке 18 – задание диапазонов. Блок критериев, блок вывода создать самостоятельно согласно рисунку 17. Выполнить команду Данные, Дополнительно и задать диапазоны также самостоятельно.

Рисунок 17. Критерии и отобранные данные с применением функции «ИЛИ»

Рисунок 18. Задание диапазонов.

Варианты контрольных заданий по темам «Создание и вставка стандартных формул. Построение диаграмм. Технологии обработки структурированных данных»

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

ВАРИАНТ 1

Для группы продуктов из 10 наименований известны:

· сорт (высший, первый, второй);

· плановый объем выпуска каждого вида продукта в денежном выражении;

· стоимость единицы каждого вида продукта;

· фактический объем каждого вида продукта в натуральном выражении.

Определить:

1. Фактический объем каждого вида продукта в денежном выражении.

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

3. Суммарный, средний, минимальный и максимальный фактический объем в денежном выражении.

4. Указать для каждого наименования в отдельном столбце сообщение вида «выполнен план», «перевыполнен план» или «не выполнен план» с помощью функции ЕСЛИ.

5. Определить количество наименований продуктов с выполненным, невыполненным и перевыполненным планом.

6. Суммарный фактический объем продуктов в денежном выражении с выполненным планом.

7. Построить: a) сравнительную диаграмму плана и факта в денежном выражении;

b) диаграмму с указанной процентной долей объема каждого продукта.

8. Из созданной таблицы, считая ее базой данных, вывести:

· список наименований с невыполненнымпланом;

· список наименований высшего сорта и с перевыполненным планом с указанием планового и фактического объема в денежном выражении;

· список продуктов с выполненным или перевыполненным планом.

ВАРИАНТ 2

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

Определить:

1. Объем на конец дня в натуральном и денежном выражении.

2. Количество наименований продуктов, у которых расход превысил приход.

3. Суммарный объем на конец дня товаров высшего сорта.

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

5. Суммарную, среднюю, максимальную и минимальную величину объема на начало дня.

6. Построить: a) сравнительную диаграмму величины объема на начало дня, прихода и расхода; b) диаграмму с указанием доли объема каждого товара на конец дня.

7. Из созданной таблицы, считая ее базой данных, вывести:

· список товаров, для которых приход превысилрасход;

· список товаров высшего сорта, для которыхрасход превысил приход;

· список товаров первого сорта или товаров, для которых расход равен приходу.

ВАРИАНТ 3

Дан список работников с указанием ФИО, места рождения, года рождения, пола, зарплаты.

Определить:

1. Возраст каждого работника.

2. Средний, максимальный, минимальный возраст.

3. Количество работников старше указанного возраста.

4. Суммарную зарплату по всему списку.

5. Суммарную зарплату отдельно для мужчин и для женщин.

6. В отдельном столбце вывести сообщение о принадлежности каждого работника к одной из групп: «Несовершеннолетний», «Совершеннолетний», «Пенсионер» с помощью функции ЕСЛИ.

7. Построить: a) диаграмму с указанием доли зарплаты каждого работника; b) график изменения возраста.

8. Из созданной таблицы, считая ее базой данных, вывести:

· уроженцев одной местности;

· список пенсионеров и несовершеннолетних;

· списокмужчин старше 50 летс указанием зарплаты.

ВАРИАНТ 4

Дан список семей с указанием ФИО главы семьи, социального положения, количества членов в семье, общего дохода семьи.

Определить:

1. Доход на одного человека в каждой семье.

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

3. Суммарный доход семей, принадлежащих к указанной социальной принадлежности.

4. В отдельном столбце указать, к какой группе относится каждая семья: «Малообеспеченная», «Среднеобеспеченная», «Высокообеспеченная» с помощью функции ЕСЛИ.

5. Суммарную, среднюю, максимальную и минимальную величину общего дохода.

6. Построить: a) диаграмму с указанием доли общего дохода каждой семьи; b) график изменения дохода на одного человека.

7. Из созданной таблицы, считая ее базой данных, вывести:

· список семей одной социальнойпринадлежности;

· список малообеспеченных и среднеобеспеченныхсемей с указанием суммарного дохода;

· список высокообеспеченныхсемей и относящихся к одной социальной группе.

ВАРИАНТ 5

Дан перечень продуктов с указанием наименования, сорта, объема, цены и себестоимости за единицу каждого вида продукта.

Определить:

1. Прибыль по каждому виду продукта.

2. Суммарную, среднюю, максимальную и минимальную величину прибыли.

3. В отдельном столбце указать по каждому продукту: прибыльна или убыточна эта продукция с помощью функции ЕСЛИ.

4. Количество прибыльных и убыточных продуктов.

5. Суммарный объем прибыльных продуктов.

6. Построить: a) диаграмму распределения значений прибыли; b) сравнительную диаграмму цены, себестоимости и прибыли.

7. Из созданной таблицы, считая ее базой данных, вывести:

· список прибыльных продуктов;

· список убыточныхпродуктов первого сорта;

· список продуктов высшего сорта или с ценой ниже заданной величины.

ВАРИАНТ 6

Для работников некоторого отдела известны: ФИО, однодневный заработок, стаж работы, количество отработанных дней в месяце, количество дней нетрудоспособности в случае болезни. Допустим, что пособие по нетрудоспособности рассчитывается в зависимости от стажа по следующему алгоритму: при стаже менее 3 лет пособие равно 50% от начисления за дни нетрудоспособности; при стаже от 3 до 5 лет – 60%; от 5 до 8 лет – 80%; свыше 8 лет – 100%.

Определить:

1. Для каждого работника сумму начисления за отработанное время и за дни нетрудоспособности.

2. Пособие за дни нетрудоспособности.

3. Сумму налога как 13% от суммы начисления за отработанное время.

4. Сумму к выдаче с учетом пособия и суммы налога.

5. Суммарное, среднее, максимальное и минимальное значение по сумме к выдаче и пособию.

6. Суммарную зарплату работников, не болевших в течение месяца.

7. Количество работников, болевших в течение месяца.

8. Построить: a) сравнительную диаграмму начисленных сумм; b) диаграмму с указанной процентной долей суммы к выдаче.

9. Из созданной таблицы, считая ее базой данных, вывести:

· список работников, не болевших в течение месяца;

· список не болевших работников со стажем свыше 10 лет;

· список болевших работников или с суммой к выдаче менее заданной величины.

ВАРИАНТ 7

Дан перечень материалов с указанием наименования, сорта, объема, цены за единицу.

Определить:

1.Общую стоимость материала каждого вида с учетом следующих условий: к цене материала 1 сорта прибавляется наценка 50 рублей; если же материал 2 сорта и цена не превышает 150 рублей, то начальная цена увеличивается на 20%.

2.Количество видов материала, объем которых превышает заданную величину.

3.Суммарную стоимость материала высшего сорта.

4. Суммарную, среднюю, максимальную и минимальную величины объема.

5.Построить: a) сравнительную диаграмму первоначальной и измененной величин цены; b) диаграмму с указанием доли стоимостикаждого материала.

6. Из созданной таблицы, считая ее базой данных, вывести:

· список материаловвторого сорта;

· список материаловвысшего сортас ценойменее заданной величины;

· список материаловпервого иливторого сортаилис объемом свыше заданной величины.

ВАРИАНТ 8

Допустим, что работники некоторого предприятия имеют возможность приобрести дорогостоящий товар в рассрочку, т.е. с оплатой стоимости товара по частям ежемесячно. Ежемесячная сумма оплаты представляет определенный процент от стоимости товара по следующей схеме: при стоимости товара ниже 10000рублей процент ежемесячной оплаты составляет 30%; при стоимости от 10000 до 25000рублей – 25%; свыше 250000 рублей – 15 %. Для расчета известны: однодневный заработок (тариф), количество отработанных дней в месяце, наименование купленного товара и его стоимость.

Определить:

1. Ежемесячную сумму оплаты товара.

2. Сумму начисления за отработанное время.

3. Налог как 13% от начисления.

4. Сумму к выдаче с вычетом налога и взноса за товар.

5. Количество работников, купивших товар одинакового наименования.

6. Сумму взносов за товар, стоимость которых превышает 16000 рублей.

7. Суммарную,среднюю, максимальную и минимальную величины суммы к выдаче.

8. Построить: a) сравнительную диаграмму суммы начисления и суммы к выдаче; b) диаграмму с указанием доли взноса каждого работника.

9. Из созданной таблицы, считая ее базой данных, вывести:

· список работников, приобретших товар одного наименования;

· список работников, сумма к выдаче которых входит в заданный предел;

· список работников, которые приобрели товар стоимостью свыше 6000 рублей или имеют сумму начисления свыше 9000 рублей.

ВАРИАНТ 9

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

Определить для каждого отдела:

1. Суммарный фактический товарооборот за квартал.

2.Процент выполнения плана.

3. Сумму премии по следующей схеме: если план не выполнен, то премия отсутствует; при перевыполнении менее чем на 120%, премия равна 25% от величины фактического объема за квартал; при перевыполнении более, чем на 125%, сумма премии равна 35% от факта.

4. Отделы с невыполненным планом пометить символом «****»с помощью функции ЕСЛИ.

5. Количество отделов, которые перевыполнили план более, чем на 125 %.

6. Суммарный фактический товарооборот отделов с невыполненным планом.

7. Суммарный, средний, максимальный и минимальный фактический товарооборот.

8. Построить: a) сравнительную диаграмму планового и фактического товарооборота; b) диаграмму с указанием доли премии каждого отдела.

9. Из созданной таблицы, считая ее базой данных, вывести:

· список отделов, перевыполнивших план;

· список отделов с фактическим товарооборотом за квартал, входящих в заданный предел;

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

ВАРИАНТ 10

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

В зависимости от срока хранения продукта производится уценка по следующей схеме: до 10 дней хранения уценка не производится; от 10 до 20 дней хранения первоначальная цена уменьшается на 10%; от 20 до 30 дней – на 20%; свыше 30 дней – списывается (цена равна 0).

Определить:

1. Цену единицы после уценки.
2. Стоимость всего объема продукции каждого вида до уценки и после уценки.
3. Суммарное, среднее, максимальное и минимальное значение по объему и по стоимости.
4. Суммарную стоимость после уценки продукции первого сорта.
5. Количество не уцененной продукции.
6. Построить: a) сравнительную диаграмму значений стоимостей; b) диаграмму с указанной процентной долей величин объема.

7. Из созданной таблицы, считая ее базой данных, вывести:

· список не уцененных наименований;

· список наименований высшего сорта с объемом выше указанной величины;

· список уцененных или списанных наименований.

Список рекомендуемой литературы

1.Советов, Борис Яковлевич. Информационные технологии : Учеб. для вузов по напр. "Информатика и вычисл. техника", "Информ. системы" / Б.Я. Советов, В.В. Цехановский. - Изд.2-е,стер. - М.: Высшая школа, 2005. - 263 с.: ил.

2.Ясенев, Вячеслав Николаевич. Информационные системы и технологии в экономике : учеб. пособие для вузов по спец. экономики и упр. (080100) / В.Н. Ясенев. - 3-е изд., перераб. и доп. - М. : ЮНИТИ, 2008. - 560 с

3.Бордоева, Анна Евдокимовна. Табличный процессор Excel в примерах и задачах : учеб. пособие (перераб.) для вузов по спец. "Прикл. информатика (по обл.)" и "Мат. обеспечение и администрирование информ. систем" / А.Е. Бордоева ; ВСГТУ. - Улан-Удэ : Изд-во ВСГТУ, 2007. - 224 с.: ил

4.Макарова, Таисья Васильевна. Основы информационных технологий в рекламе : учеб. пособие для вузов по спец. 032401 "Реклама" / Т.В. Макарова, О.Н. Ткаченко, О.Г. Капустина ; Под ред. Л.М. Дмитриевой. - М. : ЮНИТИ-ДАНА, 2009. - 269 с. : ил