Краткие сведения из теории. 3 страница

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

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

Скопируйте данные таблицы 6.2 на лист «Фильтр». Вызовите команду Данные – Фильтр – Автофильтр. С помощью появившихся в заголовках полей кнопок задайте условия фильтрации. Отфильтруйте записи о студентах, имеющих определенное количество баллов по одному из тестов. Можно ли фильтровать по двум полям одновременно? По трем полям? Выполните фильтрацию по критериям: средний балл больше 7; средний балл меньше 3 (строка Условие из раскрывающегося списка). Отфильтруйте студентов, фамилия которых начинается на заданную букву.

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

Расширенный фильтр позволяет:

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

При расширенной фильтрации для отбора строк таблицы используется вспомогательная таблица (фильтр), где указываются некоторые из столбцов и строк исходной таблицы. Например, для фильтрации таблицы 6.10 можно создать таблицу 6.11 (диапазон условий). В результате фильтрации будут отобраны те записи таблицы КОНТРАКТ, которые содержат записи о поставках фирмой ООО «Павлин» мороженого в количестве большем, чем 100 единиц, и о поставках фирмой ЗАО «Энергия» моторов в количествах от 120 до 300 штук.

Таблица 6.10. Контракт

Поставщик	Потребитель	Товар	Количество

Таблица 6.11. Условия

Поставщик	Количество	Количество	Товар
ООО «Павлин»		>100	Мороженое
ЗАО «Энергия»	<300	>120	Моторы

В общем случае в фильтре каждое поле исходной (фильтруемой) таблицы можно представить один раз или дважды, а в строке под ней могут быть использованы числовые значения поля со знаками неравенства (<, >, <=, <=) или символьные значения без знака. Это дает возможность записать неравенство 150<количество<250 в виде таблицы:

Количество	Количество
>150	<250

С помощью диапазона условий

Количество	Количество	Товар
>150	<250	Мороженое

можно выделить записи о поставках мороженого в количестве, удовлетворяющем неравенству 150<количество<250. Это условие можно записать также с помощью следующего логического выражения (товар = «мороженое») И (150<количество<250).

Допускается создать диапазон условий фильтра, содержащий несколько строк. Каждой строке соответствуют элементарные условия, объединяемые союзом «И», а условия, соответствующие различным строкам, объединяются союзом «ИЛИ». Например, диапазон условий

Количество	Количество	Товар
>150	<250	Мороженое
>200		Вафли

определяет все записи, удовлетворяющие логическому условию ((150<количество<250) И (товар = «мороженое»)) ИЛИ ((количество>200) И (товар = «вафли»)), то есть те записи, которые относятся к сделкам по поставке мороженого в количестве от 150 до 250 единиц или поставкам вафель в количестве, превосходящем 200 единиц.

Задание 6.40. Помимо таблиц существует другой основной способ визуального представления данных – формы.

Форма – прямоугольный участок экрана, на котором помещаются различные элементы управления: кнопки действий, поля и списки ввода, поля выбора и т.п. В Excel возможности создания и использования форм существенно уже, чем в системах управления базами данных, например MS Access, но они достаточно удобны в использовании. Форма в Excel создается на основе заголовка таблицы, т.е. на основе списка имен полей. С каждым вводимым столбцом связывается поле ввода, а значения вычисляемых полей выводятся на форму. Одновременно можно увидеть только одну запись таблицы. Для перемещения по таблице имеются специальные кнопки. Текущую запись можно удалить или изменить (отредактировать). Для ввода новых данных используется кнопка Добавить. Кнопка Критерий позволяет задать простой критерий фильтрации (отбора) записей. После ввода критерия следует нажать Назад или Далее; становятся доступны только записи удовлетворяющие критерию. Это полезно, если записей много.

Скопируйте данные таблицы 6.2 на лист «Форма». Добавьте еще одно поле «Средний балл» и вычислите в нем средний балл для каждого студента. Создайте форму для заполнения таблицы. Используя форму, добавьте 5 записей. Попробуйте удалить запись. Используйте критерий, выделив все записи о студентах, имеющих определенное количество баллов по одному из тестов. Можно ли фильтровать по двум полям одновременно? По трем полям? Выполните фильтрацию по критериям: средний балл больше 7; средний балл меньше 3. Выделите всех студентов, фамилия которых начинается на заданную букву.

Замечание. Используемые формы создаются автоматически на основе заголовков полей таблицы (списка) и не могут быть изменены. Эти недостатки преодолеваются, если воспользоваться возможностью создания пользовательских форм.

Задание 6.41. Поиск в таблице.

Стандартное средство поиска в документах MS Office – команда Правка – Найти, – находит требуемый набор символов, но не позволяет манипулировать с выбранными данными. При работе с таблицей может возникнуть необходимость использовать адрес, по которому размещается определенная информация. В этом случае применяются специальные функции, встроенные в MS Excel.

Простейшая задача поиска в таблице может быть описана следующим образом:

1. Дана таблица, состоящая из двух или большего числа полей (столбцов);

2. Дано значение ключевого поля;

3. Требуется найти запись с соответствующим значением ключевого поля;

4. Требуется вернуть значение заданного поля в найденной записи.

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

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

значение 1ключ<значение 2.

В этом случае соответствующим будет значение «значение 1».

Если ключ больше всех значений поля, то выбирается последнее из значений поля, т.е. самое большое. Если, наоборот, ключ меньше всех значений поля, то возвращается ошибка.

Для упорядоченных значений поля способ сравнения определяется аналогично.

Например, имеется телефонный справочник или, точнее, таблица с 4 полями:

№ телефона;

Фамилия;

Имя;

Отчество.

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

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

Функция ВПР()

Синтаксис функции:

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

Функция имеет следующие особенности применения:

Поиск происходит в первом столбце таблицы.

Если «тип сравнения» = 0 (ЛОЖЬ), то сравнение подразумевает полное совпадение.

Если «тип сравнения» = 1 (ИСТИНА), то первый столбец должен быть отсортирован по возрастанию.

Если «тип сравнения» не указан, подразумевается, что он равен 1.

Задание 6.41.1. Дана таблица с именем «Результат»

№ зачетной книжки	ФИО	Курс	Код группы
	Иванов А.А.		И61д
	Кузнецов В.В.		И63д

По номеру зачетной книжки (№ зачетной книжки) требуется найти код группы, в которой учится владелец этой книжки.

Функция ВПР(1061; «Результат»; 4; 0) вернет значение И63д.

В данном случае требуется точное совпадение при сравнении.

Задание 6.41.2. Имеются данные по результатам экзаменационной сессии.

№ зачетной книжки	ФИО	Математика	Информатика
	Иванов А.А.	отлично	хорошо
	Амбалов Г.Г.	неудовлетворительно	хорошо
	Симонов С.С.	хорошо	удовлетворительно

Добавить в таблицу еще 10 записей.

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

Задание 6.41.3. Результаты тестирования студентов представлены таблицей из 4 столбцов, три из которых заполнены

№ зачетной книжки	ФИО	Курс	Число баллов	Оценка
	Ли С.Ю.

Необходимо;

§ дополнить таблицу еще десятью строками;

§ автоматизировать заполнение поля «Оценка», используя для этого справочник

Минимальное число баллов	Оценка
	неудовлетворительно
	удовлетворительно
	хорошо
	отлично

То есть, если получено меньше 30 баллов, то оценка «неудовлетворительно», если набрано от 30 до 70, то оценка «удовлетворительно», если число баллов больше 70 и меньше 90, то оценка «хорошо». Наконец, если студент набрал не меньше 90 баллов, то он получает оценку «отлично».

Задание 6.41.4. В условиях предыдущего задания вычисление оценки производится по двум справочникам, выбор которых зависит от курса.

Указание. Присвоить имена столбцу «Курс» и справочникам, например, спр1, спр2. Второй аргумент функции ВПР запишите в виде функции ЕСЛИ(Курс=1; спр1;спр2).

Задание 6.41.5. Функция ПРОСМОТР().

Синтаксис функции:

ПРОСМОТР (данное значение элемента массива; массив поиска; массив результата)

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

Дана таблица

Номер	ФИО	Доход
	Вано И.П.
	Сергеев А.А.

Впишите в таблицу еще 7 строк. Используйте функцию ПРОСМОТР() для поиска размера доходов у людей с номерами 5 и 4.

Задание 6.41.6. Функция ПОИСКПОЗ().

Синтаксис функции:

ПОИСКПОЗ(данное значение элемента массива; массив поиска; тип сопоставления)

Здесь массивы поиска и сопоставления совпадают, но можно указать тип сопоставления. Функция возвращает позицию найденного элемента в массиве.

Дан массив возрастов в годах для респондентов и справочник

Граница возрастной группы	Возрастная группа
	ребенок
	подросток
	молодой
	зрелый
	пожилой
	престарелый

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

Задание 6.41.7. Функция ИНДЕКС().

Синтаксис функции:

ИНДЕКС(массив; номер строки; номер столбца)

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

Возможная укороченная форма этой функции ИНДЕКС(массив; номер строки).

Выполните повторно задание 6.41.2, используя вместо функции ВПР() функцию ИНДЕКС(массив результатов; ПОИСКПОЗ(данное значение; массив поиска;0))

Задание 6.42. Функции анализа таблиц.

Функции СЧЕТЕСЛИ() и СУММЕСЛИ() позволяют на основе данной таблицы (таблицы сырых данных) создать производную таблицу (обработанных данных).

Функция СЧЕТЕСЛИ() имеет следующий синтаксис:

СЧЕТЕСЛИ(массив поиска; условие)

У функции СУММЕСЛИ() похожий синтаксис:

СУММЕСЛИ(массив поиска; условие; массив суммирования)

Функция СЧЕТЕСЛИ() возвращает число элементов, удовлетворяющих условию; функция СУММЕСЛИ() возвращает сумму элементов, удовлетворяющих условию.

Задание 6.42.1. Дана таблица оценок

ФИО	Факультет	Пропущено_часов	Математика	Информатика
Иванов	Экономики
Петров	Права
Федоров	Права
Пухов	Экономики
Троева	Экономики
Котова	Права

На листе «БД» введите таблицу.

При использовании формулы СЧЕТЕСЛИ(Факультет; «Права») получится результат 3. Формула СУММЕСЛИ (Факультет; «Экономики»; Пропущено_часов) вернет сумму часов, пропущенных студентами экономического факультета – 28.

Задание 6.42.2. Дополните таблицу предыдущего упражнения десятью новыми записями и получите производную таблицу:

Математика	Факультет	Факультет
	Экономики	Права
Сумма
Среднее
Отклонение

Производные таблицы создают с помощью встроенных функций категории Работа с базой данных. Значение, возвращаемое функциями, разъясняется в Мастере функций.

Указание: исходной таблице на листе «БД» присвойте имя «База_данных»; полю, содержащему слово «Математика», присвойте имя «М»; диапазоны условий расширенной фильтрации – ячейки со словами Факультет и Экономики (Права). Для заполнения ячеек производной таблицы используйте функции работы с базой данных БДСУММ, ДСРЗНАЧ и ДСТАНДОТКЛ.

Составьте аналогичную таблицу для результатов по информатике.

Задание 6.43. Сводные таблицы.

Если данные, необходимые для решения задачи, представлены в виде большой и сложной таблицы, то

1. Непосредственное восприятие данных затруднено;

2. Для решения конкретной задачи всех данных не требуется.

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

Все сказанное можно описать схемой:

Создание простой сводной таблицы.

Имеются данные о ежемесячных поставках товара:

№ контракта	Поставщик	Товар	Потребитель	Месяц	Стоимость

Каждому поставщику необходимо сообщить: коды потребителей и суммарную стоимость поставляемых товаров.

Для каждого поставщика создадим таблицу (страницу):

	Потребители
Товары	Суммарная стоимость

Вся совокупность таблиц будет называться «Сводная таблица».

Сводная таблица создается Мастером Данные – Сводная таблица.

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

В данном случае буксировать поля:

Поставщик – в область страниц;
Товары – в область столбцов;
Потребитель – в область строк;
Стоимость – в область данных.

Задание 6.43.1. Создайте исходную таблицу из 20 записей. Постройте описанную выше сводную таблицу.

Обновите исходные данные и сводную таблицу.

Задание 6.43.2. Данные о поставках печатной продукции со складов в киоски представлены в виде таблицы:

Склад	Киоск	Тип продукции	Количество

Необходимо предоставить каждому складу информацию об отгрузке продукции каждого типа каждому киоску. Можно для этого создать сводную таблицу с макетом

Склад (область страниц)
	Тип продукции (область столбцов)
Киоск (область строк)	Количество (область данных)

Задание 6.43.3. Задача представления данных в различных разрезах.

Пусть данные о поставках представлены в виде таблицы:

Поставщик

Получатель

Товар

Количество

Цена

Сравните первые три поля и два последующие. Они играют различную роль; первые три значения однозначно идентифицируют контракт на поставку, а два последующих значения дают контракту численную характеристику. Можно представить таблицу как пространственную, выбрав в качестве осей декартовой системы координат оси «Поставщик», «Получатель», «Количество». Выбирая значения на этих осях из таблицы, получим прямоугольный параллелепипед, который заполним значениями из столбцов «Количество» и «Цена», сделав таблицу пространственной. Хотя такое представление достаточно компактно и, для людей обладающих достаточно развитым пространственным воображением, наглядно, обычно требуется представить данные в виде серии плоских таблиц. Это можно сделать с помощью сводных таблиц, причем тремя способами, поскольку любая серия порождается с помощью сечений параллелепипеда плоскостями перпендикулярными одной из осей. При построении сводной таблицы для этого достаточно поместить соответствующее поле в область страниц. Два других идентифицирующих поля помещаем в области строк и столбцов. В область данных помещаем поля с количественной характеристикой.

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

	Товары
Потребители	Количество товара

Задание 6.43.4. Используя исходную таблицу предыдущего задания, создать сводную таблицу

	Товары
Потребители	Стоимость товара

Указание. Используйте панель Сводные таблицы, команда Сводные таблицы – Формулы.

Задание 6.44. Финансовые функции Excel предназначены для выполнения финансовых расчетов. Для удобства работы рекомендуется предварительно подготовить значения аргументов функции. В палитре каждой функции имеется кнопка Справка по этой функции, предоставляющая справочную информацию с примерами использования.

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

Задание 6.44.1. Рассчитать, какая сумма окажется на счете, если 100 рублей положить под 12 % годовых на 10 лет с ежеквартальным начислением сложных процентов.

Замечание. При начислении сложных процентов на наращенные в предыдущем периоде суммы вновь начисляются проценты, то есть происходит многоразовое наращение.

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

Назовите лист «Накопление». Введите исходные данные.

А1: Сумма накопления без изменения ставки

А2: 2

В2: Количество периодов в год

А3: 12 (установите в этой ячейке процентный формат с двумя знаками после запятой)

В3: Годовая процентная ставка

А4: 100

В4: Взнос

А5: 10

В5: Срок

В ячейке А6 вставьте функцию БС с аргументами:

- Ставка: А4 / А2

- Кпер: А5 * А2

- Пс: А3

Нажмите ОК. Должен появиться результат: 326,20 р.

Здесь ставка делится на количество периодов в год, а общее число периодов выплат вычисляется как произведение количества лет на количество периодов.

Решите ту же задачу, при условии, что проценты начисляются ежемесячно (330,04 р.); один раз в год (310,58 р.).

Задание 6.44.2. Решите обратную задачу. За сколько лет наращенная сумма составит 1000 рублей, если положить 100 рублей под 12 % годовых с ежеквартальным начислением сложных процентов.

Для этого введите в А11: За сколько лет определенная сумма.

Скопируйте содержимое ячеек А2:В6 в А12:В16.

Установите курсор на А16. Выберите команду Сервис – Подбор параметра. В диалоговом окне укажите:

- Установить в ячейке: А16

- Значение: 1000

- Изменяя значение ячейки: А15

Должно получиться 19,47 лет.

Задание 6.44.3. Самостоятельно решите следующую задачу с использованием Подбора параметра. Какую сумму следует положить на счет под 12 % годовых с ежеквартальным начислением сложных процентов, чтобы через 10 лет накопить 1000 рублей? (306,56 р.).

Задание 6.44.4. Пусть имеется возможность инвестировать средства в течение пяти лет ежегодно по 100 тысяч рублей под 15 % годовых. Какая сумма окажется на счете в конце пятого года, если выплаты производить в начале года? В конце года?

Назовите лист «Инвестиции». Введите исходные данные.

А1: Сумма накопления при выплатах в начале года

А2: 1

В2: Количество периодов в год

А3: 15 (процентный формат с двумя знаками после запятой)

В3: Годовая процентная ставка

А4: 100

В4: Выплата (тысяч руб.)

А5: 5

В5: Срок

А6: 1

В6: Тип выплат