">
Информатика Программирование
Информация о работе

Тема: Электронные таблицы EXCEL

Описание: Ввод данных в базу данных. Ввод имен полей. Использование формы данных. Сортировка данных. По возрастанию, по убыванию, в особом порядке. по четырем и более полям. Поиск, фильтрация и редактирование в базах данных. Средняя оценку в каждой группе.
Предмет: Информатика.
Дисциплина: Программирование.
Тип: Методические рекомендации
Дата: 16.08.2012 г.
Язык: Русский
Скачиваний: 103
Поднять уникальность

Похожие работы:

ФЕДЕРАЛЬНОЕ АГЕНСТВО ПО ОБРАЗОВАНИЮ РФ

САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ



Электронные таблицы EXCEL

Методические указания

для выполнению лабораторных работ

Часть 1

Санкт-Петербург

2006

Содержание

Содержание2

2. Требования к использованию табличного процессора Excel2

3. Технология работы с базами данных средствами MS Excel3

3.1. База данных. Составляющие базы данных3

3.2. Ввод данных в базу данных. Ввод имен полей5

3.3. Использование формы данных5

3.4. Сортировка данных. Сортировка по возрастанию/по убыванию6

3.5. Сортировка в особом порядке7

3.6. Сортировка по четырем и более полям8

3.7. Поиск, фильтрация и редактирование в базах данных. Использование формы данных9

3.8. Автофильтр10

3.9. Расширенный фильтр11

3.10. Задание условий с использованием логической операции ИЛИ12

3.11. Задание условий с использованием логической операции И12

3.12. Задание условий с одновременным использованием логических операций И и ИЛИ14

3.13. Использование вычисляемых условий14

3.14. Анализ списка с помощью подведения промежуточных итогов15

3.15. Функции для анализа списка18

3.16. Функции баз данных19

3.17. Проверка вводимых значений20

3.18. Задание типа данных и допустимых значений20

3.19. Сообщение для ввода22

3.20. Задание сообщения об ошибке23

4. Рекомендуемая методика выполнения РАБОТ24

4.1. Варианты индивидуальных заданий по работе со списками в MS Excel24

4.2. Пример получения индивидуального задания32

Рекомендуемая литература32

2. Требования к использованию табличного процессора Excel

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

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

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

исследование влияния различных факторов на данные;

решение задач оптимизации;

получение выборки данных, удовлетворяющих определенным критериям;

построение графиков и диаграмм;

статистический анализ данных.

3. Технология работы с базами данных средствами MS Excel

3.1. База данных. Составляющие базы данных

Запустите MS Excel. Проще всего запустить MS Excel из главного меню (кнопка Пуск). В главном меню необходимо нажать на опцию Программы, после чего в появившемся выпадающем меню выбрать опцию Microsoft Excel.

Книга в Microsoft Excel представляет собой файл, используемый для обработки и хранения данных. Каждая книга может состоять из нескольких листов, поэтому в одном файле можно поместить разнообразные сведения и установить между ними необходимые связи. Начальное количество листов книги задается установками "по умолчанию". Их можно задать (изменить) воспользовавшись пунктом главного меню СервисПараметрыОбщиеЛистов в новой книге. Количество листов в уже созданной книге можно изменить через пункт главного меню ВставкаЛист. Для удаления ненужного листа предусмотрена команда ПравкаУдалить лист.

Листы служат для организации и анализа данных. Можно вводить и изменять данные одновременно на нескольких листах, а также выполнять вычисления на основе данных из нескольких листов.

Лист - основной документ, используемый в MS Excel для хранения и обработки данных. Он может также называться электронной таблицей. Кроме электронных таблиц могут существовать также специально организованные листы диаграмм, макросов, диалоговых окон и т.п. Такие листы, как правило, генерируются системой автоматически по мере возникновения потребности. Листы объединяются в книгу. Каждая книга Excel имеет собственное имя и рассматривается операционной системой как самостоятельный файл. Обычный лист MS Excel состоит из ячеек, образующих строки и столбцы. Листы объединены в книгу. В Microsoft Excel в качестве базы данных можно использовать список. Список - набор строк таблицы, содержащей связанные данные, например база данных счетов или набор адресов и телефонов клиентов. Список может использоваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов.

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

Выделяются следующие элементы списка (рис. 1):

запись (отдельная строка);

поле (отдельный столбец);

строка заголовков (первая строка списка);

имена полей (имя колонки в первой строке списка).

Рис.1. Элементы списка в MS Excel

Диапазон базы данных - это область, где хранятся данные списка. Связанные друг с другом данные записываются в отдельные строки. Каждому столбцу соответствует свое поле списка с уникальным именем поля. Правила для определения местоположения списка:

если, какому-либо диапазону ячеек на текущем листе присвоено имя "База_данных", Excel будет считать этот диапазон списком,

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

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

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

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

3.2. Ввод данных в базу данных. Ввод имен полей

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

длина имени не должна превышать 255 символов;

нельзя вставлять пустую строку между строкой заголовков или ячейками данных;

имена должны быть уникальными.

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

3.3. Использование формы данных

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

Чтобы можно было использовать формы для добавления записей в новый список, вверху каждого столбца списка должна быть расположена подпись (имя поля). Microsoft Excel использует подписи (имена полей) для создания полей на форме.

Для того, чтобы воспользоваться ей необходимо:

указать диапазон ячеек, где располагается список;

выбрать команду ДанныеФорма (рис.2);

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

нажать кнопку Закрыть, чтобы вернуться к рабочему листу.

Рис.2. Автоматическая форма для ввода данных в список Excel

3.4. Сортировка данных. Сортировка по возрастанию/по убыванию

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

Рис.3. Задание параметров сортировки

3.5. Сортировка в особом порядке

MS Excel позволяет сортировать данные в особом порядке. Для этого в диалоговом окне Сортировка диапазона нужно выбрать кнопку Параметры. В диалоговом окне Параметры сортировки по первому ключу выбрать подходящий вариант (рис.4).

Рис.4. Сортировка в особом порядке

Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать:

с помощью команды СервисПараметры вызвать окно Параметры;

перейти на вкладку Списки;

в поле Элементы списка ввести элементы, разделяя их нажатием клавиши Enter;

после ввода всех элементов нажать кнопку Добавить и закрыть окно Параметры.

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

3.6. Сортировка по четырем и более полям

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

Пример. Пусть необходимо отсортировать список по пяти полям: в первую очередь по Отделу, затем по Фамилии, Имени, Отчеству и по Дате рождения. Сортировку нужно производить в два этапа. Сначала отсортировать по Имени, Отчеству и Дате рождения (первый этап), затем - по Отделу и Фамилии (второй этап) (рис.5).

Рис.5. Пример сортировки по пяти полям

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

Форма данных - великолепное средство для поиска и редактирования отдельных записей.

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

В форме данных можно использовать критерии поиска точного соответствия, близкого соответствия, на основе множественного критерия (рис. 6).



а) поиска точного соответствия



б) поиск близкого соответствия



в) поиск на основе множественного критерия

Рис. 6. Задание критериев в форме данных

3.8. Автофильтр

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

Чтобы включить автофильтр, нужно воспользоваться командой ДанныеФильтрАвтофильтр. Excel выведет кнопки со стрелками рядом с каждым заголовком столбца (рис. 7).

Рис. 7. Вид списка при включенном автофильтре

Имеется три возможности фильтрации данных:

выбор значения поля для поиска точного соответствия;

пользовательский автофильтр;

первые 10.

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

При пользовательском автофильтре выбрать вариант Условие. Можно задать одно или два условия (рис. 8).

Рис. 8. Пользовательский автофильтр

Метод первые 10 имеет смысл только для полей с числовыми данными, в том числе и с датами. Чтобы задать этот метод, необходимо в списке выбрать вариант (Первые 10).

Чтобы снять фильтрацию с поля, нужно нажать на кнопку автофильтра и выбрать вариант (Все).

Чтобы выключить автофильтр, нужно снова воспользоваться командой ДанныеФильтрАвтофильтр.

3.9. Расширенный фильтр

Расширенный фильтр является более гибким средством отбора записей из БД, чем автофильтр и позволяет задавать:

условия, соединенные логическим оператором ИЛИ, для нескольких столбцов;

три и более условий для конкретного столбца;

вычисляемые условия.

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

Чтобы воспользоваться расширенным фильтром, нужно воспользоваться командой ДанныеФильтрРасширенный фильтр. На экране появится диалоговое окно (рис. 9).

Рис.9. Использование расширенного фильтра

В элементе управления Исходный диапазон нужно указать диапазон, в котором размещается список. В элементе управления Диапазон условий - диапазон критериев.

Флажок Только уникальные записи позволяет исключить повторения.

Когда список фильтруется на месте, не удовлетворяющие критерию строки скрываются. Чтобы вновь их отобразить, необходимо воспользоваться командой ДанныеФильтрОтобразить все.

3.10. Задание условий с использованием логической операции ИЛИ

Чтобы связать условия в диапазоне критериев логической операцией ИЛИ, нужно эти условия расположить в разных строках (см. рис. 10 - 11).

Рис. 10. Пример использования операции ИЛИ в расширенном фильтре: отобрать записи о людях с именем Сергей или с отчеством Иванович

Рис. 11. Пример использования операции ИЛИ в расширенном фильтре: получить информацию о людях, чьи фамилии начинаются либо на букву А, либо на Б, либо на В

3.11. Задание условий с использованием логической операции И

Пусть необходимо создать критерий отбора записей с использованием оператора И. Для этого условия в диапазоне критериев нужно расположить в одной строке (см. рис. 12 - 14).

Рис. 12. Пример использования операции И в расширенном фильтре: отобразить информацию о сотрудниках с именем Сергей, работающих в ПФО

Рис. 13. Пример использования операции И в расширенном фильтре: найти информацию о сотрудниках ПФО, родившихся после 01.01.1965 года

Рис. 14. Пример использования операции И в расширенном фильтре: найти сотрудников, дата рождения которых находится в промежутке с 01.01.1965 года по 01.01.1975 года включительно

3.12. Задание условий с одновременным использованием логических операций И и ИЛИ

Расширенный фильтр позволяет задавать условия отбора записей с одновременным использованием логических операций И и ИЛИ. На рис. 15 диапазон критериев задает следующее условие: выбрать из списка записи о сотрудниках ПФО с фамилиями на А и о сотрудниках бухгалтерии с фамилиями на Б и на В.

Рис. 15. Одновременное использование логических операций И и ИЛИ в расширенном фильтре

3.13. Использование вычисляемых условий

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

Правила применения вычисляемых условий:

заголовок над вычисляемым условием должен отличаться от заголовка любого из столбцов списка;

ссылки на ячейки, находящиеся вне списка, должны быть абсолютными;

ссылки на ячейки в списке должны быть относительными (возможны исключения).

На рис. 16 приведен пример использования в расширенном фильтре вычисляемого условия. Необходимо получить записи о людях, родившихся в период с 01.01.1965 года по 01.01.1975 года.

Рис. 16. Пример использования вычисляемого условия в расширенном фильтре

3.14. Анализ списка с помощью подведения промежуточных итогов

Команда ДанныеИтоги может быть использована для получения различной итоговой информации. Прежде, чем подводить итоги, нужно обязательно отсортировать список соответствующим образом. Для подведения итогов можно использовать различные функции: Сумма, Количество знаний, Среднее, Максимум, Минимум, Произведение и другие. Команда ДанныеИтоги создает промежуточные и общие итоги. При выводе промежуточных итогов Excel всегда создает структуру списка; с помощью символов структуры можно отобразить список с нужным уровнем детализации данных.

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

Рис. 17. Исходный список

Во-первых, необходимо отсортировать исходный список по полю Отдел (рис.18).

Рис. 18. Сортировка списка по полю Отдел

Во-вторых, воспользоваться командой ДанныеИтоги. На экране появиться диалоговое окно Промежуточные итоги (рис.19).

Рис. 19. Подсчет сумм окладов сотрудников для каждого отдела

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

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

В списке Добавить итоги: нужно указать, по какому (каким) полю (полям) подводить итоги.

Если в списке неоднократно подводятся итоги, то установка флажка Заменить текущие итоги приведет к тому, что итоги полученные ранее будут заменены новыми. В том случае, если этот флажок сбросить, то каждый раз к предыдущим итогам будут добавляться новые (итоги, полученные ранее, удаляться не будут).

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

Если установлен флажок Итоги под данными, то промежуточные и общие итоги будут расположены под данными (рис. 20), а если этот флажок сброшен - то над данными (рис. 21).

Рис. 20. Промежуточные и общие итоги расположены под данными

Рис. 21. Промежуточные и общие итоги расположены над данными

Чтобы убрать все итоги, нужно вызвать окно Промежуточные итоги командой ДанныеИтоги и воспользоваться кнопкой Убрать все.

3.15. Функции для анализа списка

Функции для анализа списка - это функции, возвращающие информацию об элементах списка, которые удовлетворяют некоторым условиям.

Функции СЧЕТЕСЛИ и СУММЕСЛИ предназначены для работы со списками. Они проще в использовании, поскольку позволяют задавать условия непосредственно в формуле. Но в этих функциях можно использовать только простые условия сравнения.

Функции СЧЕТЕСЛИ имеет синтаксис

=СЧЕТЕСЛИ (диапазон; условие).

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

Функция СУММЕСЛИ имеет синтаксис

= СУММЕСЛИ (диапазон; условие; диапазон_суммирования).

Первые два аргумента диапазон и условие используются так же, как и в функции СЧЕТЕСЛИ. Аргумент диапазон_суммирования задает диапазон суммируемых значений. Функция относится к категории математических.

Пример. Пусть необходимо подсчитать сумму окладов всех сотрудников с именем Сергей (рис. 22).

Рис. 22. Использование функции СУММЕСЛИ

3.16. Функции баз данных

Функции баз данных имеют обобщенное название Д-функции. Д-функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям.

У всех Д-функций один и тот же синтаксис:

=Дфункция (база_данных; поле; критерий).

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

В табл. 1 приведено краткое описание функций баз данных.

Таблица 1. Функции баз данных

Функция Описание  ДСРЗНАЧ Вычисляет среднее значение в столбце списка или базы данных среди значений, удовлетворяющих заданным условиям  БСЧЕТ Подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных среди записей, удовлетворяющих заданным условиям  БСЧЕТА Подсчитывает все непустые ячейки, которые удовлетворяют заданным условиям  БИЗВЛЕЧЬ Извлекает отдельное значение, которое удовлетворяет заданным условиям  ДМАКС Возвращает наибольшее число, которое удовлетворяет заданным условиям  ДМИН Возвращает наименьшее число, которое удовлетворяет заданным условиям  БДПРОИЗВЕДЕН Перемножает значения, которые удовлетворяют заданным условиям  БДСУММ Суммирует числа, которые удовлетворяют заданным условиям  ДСТАНДОТКЛ Оценивает стандартное отклонение на основе выборки из генеральной совокупности  ДСТАНДОТКЛП Вычисляет стандартное отклонение генеральной совокупности  БДДИСП Оценивает дисперсию генеральной совокупности по выборке  БДДИСПП Вычисляет дисперсию генеральной совокупности  

Пример. Необходимо вычислить минимальный оклад у работников ПФО с именем Николай. На рис. 23 продемонстрировано решение этой задачи.

Рис. 23. Пример использования функции баз данных

3.17. Проверка вводимых значений

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

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

3.18. Задание типа данных и допустимых значений

Вкладка Параметры позволяет задать тип и интервал значений, которые разрешается вводить. На рис. 24 приведен пример определения типа и интервала вводимых значений.

Рис. 24. Пример определения типа и интервала вводимых значений

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

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

Рис. 25. Пример задания списка допустимых значений

3.19. Сообщение для ввода

Чтобы задать подсказку, которую Excel будет выводить при вводе значений в заданный диапазон, в окне Проверка вводимых значений нужно воспользоваться вкладкой Сообщение для ввода. Здесь можно ввести заголовок и текст сообщения (рис. 26). Когда проверяемая ячейка будет выделена, это сообщение появится рядом с ней как примечание.

Рис. 26. Пример задания сообщения для ввода

3.20. Задание сообщения об ошибке

Если в проверяемую ячейку введено неправильное значение, Excel выводит стандартное сообщение об ошибке и предлагает повторить или отменить ввод. Вместо стандартного сообщения можно задать пользовательское. Для этого на вкладке Сообщение об ошибке (рис. 27) диалогового окна Проверка вводимых значений нужно ввести заголовок и текст сообщения.

Кроме того, в раскрывающемся списке Вид можно выбрать тип сообщения об ошибке:

Останов;

Предупреждение;

Сообщение.

Эти три варианта отличаются значками, которые выводятся рядом с текстом сообщения, а также набором кнопок.

Рис. 27. Задание сообщения об ошибке

4. Рекомендуемая методика выполнения РАБОТ

4.1. Варианты индивидуальных заданий по работе со списками в MS Excel

В соответствии с вариантом выберите из табл. 2 предметную область. Создайте на отдельном листе список, который должен содержать не менее 60-80 записей. Затем над созданным списком необходимо выполнить следующие действия:

сортировку;

поиск информации с помощью автофильтра;

поиск информации с помощью расширенного фильтра;

подведение итогов;

анализ списка с помощью функций для анализа списка;

проверку вводимых значений.

Каждое задание выполнять на отдельном листе; листы именовать в соответствии с выполняемым заданием (например, "Автофильтр", "Сортировка в особом порядке" и т.п.). Для этого потребуется копировать список на нужное количество листов.

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

1. Сортировка (табл. 3). Это задание состоит из двух пунктов: 1) сортировка по 4-м и более полям и 2) сортировка в особом порядке. Во втором столбце таблицы указаны поля, по которым нужно осуществить сортировку. В третьем столбце указано поле, для которого нужно осуществлять сортировку в особом порядке. Порядок сортировки задать самостоятельно, но этот порядок должен отличаться от порядка "по убыванию" и "по возрастанию".

2. Автофильтр (табл. 4).

3. Расширенный фильтр (табл. 5). При формировании некоторых критериев отбора следует использовать вычисляемые условия.

4. Подведение промежуточных итогов (табл. 6). Итоги во многих вариантах нужно проводить в несколько этапов. При этом заменять текущие итоги не нужно.

5. Функции для анализа списка (табл. 7).

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

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

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

Таблица 2. Предметные области

Предметная область Пояснения  

1 - 4 

Отдел кадров (Фамилия, Имя, Отчество, Отдел, Оклад, Пол, Дата рождения, Возраст, Дата приема на работу)



Поле Возраст необходимо рассчитывать по формуле  

5 - 8 

Деканат (Фамилия, Имя, Отчество, Дата рождения, Группа, Факультет, Предмет, Дата сдачи экзамена, Оценка)



Значения поля Оценка: Отлично, Хорошо и т.д.  

9 - 12 

Нагрузка преподавателя (ФИО, Ученая степень, Должность, Кафедра, Название предмета, Специальность, Группа, Факультет, Вид занятия, Количество часов)



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

13 - 16 

Продажи (Менеджер, Клиент, Вид сделки, Товар, Количество, Цена, Сумма, Дата)



Значения поля Вид сделки: поставка, продажа  

17 - 20 

Поставки (Дата поставки, Поставщик, Количество поставленной продукции, Способ перевозки, Транспортные издержки на единицу товара, Цена единицы продукции без транспортных издержек, Стоимость перевозимого товара, Общие транспортные расходы)



Значения поля Способ перевозки: ж/д., самолет и т.п.

Поле Общие транспортные расходы необходимо рассчитывать по формуле

 

Таблица 3. Сортировка

Сортировка по 4-м и более полям Сортировка в особом порядке  1 Фамилия, Имя, Отчество, Дата рождения Отдел  2 Отдел, Фамилия, Имя, Отчество Фамилия  3 Дата рождения, Фамилия, Имя, Отчество Отдел  4 Оклад, Фамилия, Имя, Отчество, Отдел Возраст  5 Фамилия, Имя, Отчество, Дата рождения, Факультет Факультет  6 Предмет, Дата сдачи экзамена, Фамилия, Имя, Отчество Предмет  7 Предмет, Оценка, Фамилия, Имя, Отчество Группа  8 Факультет, Предмет, Оценка, Группа, Фамилия, Имя, Отчество Оценка  9 Кафедра, Должность, Ученая степень, ФИО Ученая степень  10 Кафедра, ФИО, Факультет, Группа Должность  11 Название предмета, Кафедра, Должность, Ученая степень, ФИО Вид занятия  12 Вид занятия, Название предмета, Факультет, Группа, ФИО Название предмета  13 Менеджер, Клиент, Товар, Количество Товар  14 Клиент, Менеджер, Товар, Дата Клиент  15 Товар, Менеджер, Клиент, Сумма Менеджер  16 Дата, Менеджер, Товар, Клиент, Количество Товар  17 Поставщик, Способ перевозки, Стоимость перевозимого товара, Дата поставки Способ перевозки  18 Способ перевозки, Поставщик, Дата поставки, Общие транспортные расходы Поставщик  19 Поставщик, Способ перевозки, Дата поставки, Транспортные издержки на единицу товара Способ перевозки  20 Дата поставки, Способ перевозки, Поставщик, Общие транспортные расходы, Количество поставленной продукции Поставщик  Таблица 4. Автофильтр

Запрос  1 Получить информацию о сотрудниках двух конкретных отделов, родившихся в период [Дата1; Дата2] и принятых на работу позднее даты Дата3  2 Получить информацию о мужчинах, имя которых начинается на букву Буква, отчество - "Иванович", с окладом ниже значения Оклад  3 Получить информацию о женщинах, фамилии которых заканчиваются на "их" или "ко", в возрасте от 35 до 40 лет, работающих либо в отделе Отдел1, либо в отделе Отдел2  4 Определить, есть ли в отделах Отдел1 и Отдел2 мужчины, размеры окладов которых относятся к пяти наибольшим на всем предприятии  5 Отобразить информацию о студентах групп Группа1 и Группа2 по предмету Предмет с оценками Хорошо и Отлично  6 Найти информацию о студентах, сдавших экзамены по предметам Предмет1 и Предмет2 на оценку Отлично либо раньше даты Дата1, либо позже даты Дата2  7 Найти студентов - отличников с двух факультетов Факультет1 и Факультет2, родившихся в период [Дата1; Дата2]  8 Найти информацию о студентах групп Группа1 и Группа2 , сдавших экзамен по предмету Предмет либо на оценку Неудовлетворительно, либо на оценку Отлично  9 Определить, читают ли лекции по предмету Предмет на факультетах Факультет1 и Факультет2 профессора  10 Определить, в каких группах читает лекции и ведет лабораторные работы преподаватель Преподаватель  11 Найти информацию о доцентах и ассистентах с фамилией Фамилия, которые проводят занятия по предмету Предмет на факультетах Факультет1 и Факультет2  12 Найти всех преподавателей с кафедры Кафедра, которые ведут лабораторные работы и практические занятия в группах Группа1 и Группа2  13 Найти информацию о деятельности менеджере Менеджер в период [Дата1; Дата2]  14 Определить клиентов, покупающих или поставляющих товары Товар1 и Товар2 в количестве больше Количество  15 Найти информацию, связанную с покупкой или продажей товаров Товар1 и Товар2 клиентом Клиент на сумму Сумма и выше  16 Определить 4 самые крупные сделки за последний месяц  17 Найти информацию о поставках от поставщика Поставщик в период с Дата1 по Дата2  18 Получить информацию о поставках от поставщика Поставщик способом перевозки Способ_перевозки после даты Дата  19 Определить, какими способами перевозки поставлялся товар от поставщиков Поставщик1 и Поставщик2 в период с Дата1 по Дата2  20 Определить, какие поставщики использовали способы перевозки Способ_перевозки1 и Способ_перевозки2 с общими транспортными расходами меньше Сумма  

Таблица 5. Расширенный фильтр

Запрос  1 Найти работников отделов Отдел1 и Отдел2 с фамилиями, начинающимися на буквы Буква1 и Буква2, и окладами выше среднего оклада на предприятии  2 Найти информацию о мужчинах из отдела Отдел1 в возрасте от Возраст1 и Возраст2 и о женщинах из отдела Отдел2 в возрасте от Возраст3 до Возраст4  3 Определить, принимались ли на работу в отделы Отдел1 и Отдел2 несовершеннолетние  4 Найти женщин из отдела Отдел1, родившихся в период [Дата1; Дата2], и мужчины из отдела Отдел2, родившихся в период [Дата3; Дата4]  5 Найти информацию о студентах факультетов Факультет1 и Факультет1, сдавших экзамены в период с Дата1 по Дата2  6 Определить студентов факультетов Факультет1 и Факультет2, сдавших экзамены по предмету Предмет на оценки Удовлетворительно или Хорошо  7 Найти информацию о студентах в возрасте от Возраст1 до Возраст2, сдавших экзамены по предметам Предмет1 и Предмет2 на оценку Отлично  8 Найти информацию о студенте Фамилия, сдавшим экзамен по предмету Предмет на оценку выше средней оценки по этому предмету по вузу  9 Отобразить лекционные курсы, которые обеспечивает кафедра Кафедра, на которые отводится количество часов больше среднего количества часов, отводимых на лекционный курс  10 Найти информацию о доцентах и ассистентах кафедр Кафедра1 и Кафедра2, которые проводят практические занятия и лабораторные работы на факультетах Факультет1 и Факультет2  11 Найти дисциплины, изучаемые на факультете Факультет с минимальным количеством часов, отводимых на практические задания  12 Найти дисциплины, изучаемые на факультетах Факультет1 и Факультет2 с максимальным количеством часов, отводимых на практические задания  13 Отобразить информацию о сделках, проведенных менеджером Менеджер, с суммой, превышающей среднюю сумму сделки  14 Найти информацию о деятельности менеджера Менеджер1 по товару Товар1 и Менеджера2 по товару Товар2 в период [Дата1; Дата2]  15 Найти поставки от клиентов Клиент1 и Клиент2 на суммы, равные средней сумме поставки +N рублей или -N рублей  16 Отобразить информацию о сделках за период с Даты1 по Дата2, проведенных менеджерами Менеджер1, Менеджер2 и Менеджер3 по товарам Товар1, Товар2 и Товар3 на сумму, превышающую Сумма  17 Найти поставки от поставщиков Поставшик1, Поставшик2 и Поставшик3 в период от Даты1 до Дата2 на суммы, превышающие среднюю сумму поставки в 1,2 раза  18 Найти поставки способами перевозки Способ_первозки1 и Способ_перевозки2 от поставщиков Поставщик1, Поставщик2 и Поставщик3 со стоимостью перевозимого товара от Сумма1 до Сумма2 рублей  19 Пусть самые крупными поставки являются те, у которых количество поставленной продукции находятся в пределах: максимальное количество поставленной продукции минус минимальное количество поставленной продукции. Определить, производились ли крупные поставки в период с Дата1 по Дата2 способами перевозки Способ_перевозки1 и Способ_перевозки2  20 Для каждого способа перевозки в период с Дата1 по Дата2 найти поставки для соответствующего способа перевозки  

Таблица 6. Подведение промежуточных итогов

Задание  1 Определить средний оклад и сумму всех окладов в каждом отделе  2 Определить количество и средний возраст сотрудников в каждом отделе  3 Определить количество мужчин и женщин на предприятии и средний оклад мужчин и женщин  4 Определить минимальный и максимальный оклад в каждом отделе  5 Определить среднюю оценку в каждой группе по каждому предмету  6 Определить количество студентов в каждой группе и на каждом факультете  7 Определить количество экзаменов, сданных каждым студентом, и средний балл студента  8 Определить, сколько оценок Отлично, Хорошо, Удовлетворительно и Неудовлетворительно в каждой групп по каждому предмету  9 Определить, сколько часов отводится на каждый предмет в каждой группе  10 Определить, сколько сотрудников на каждой кафедре и сколько на каждой кафедре ассистентов, доцентов и профессоров  11 Определить общую нагрузку в часах и нагрузку по видам занятий для каждого преподавателя  12 Определить, сколько предметов ведет каждый преподаватель, и подсчитать его общую нагрузку в часах  13 Определить, на какую сумму каждый менеджер провел сделок и на какую сумму каждый менеджер провел сделок с каждым клиентом  14 Определить общую сумму сделок каждого менеджера, а также сумму поставок и продаж, проведенных каждым менеджером  15 Определить, сколько каждого товара поставлено и отпущено  16 Определить, какое количество товара поставил и закупил каждый клиент  17 Определить общее количество поставленной продукции от каждого поставщика, а также количество поставленной продукции каждым способом перевозки  18 Определить количество поставленной продукции каждым способом перевозки и среднюю стоимость транспортных расходов  19 Определить транспортные расходы для каждого способа перевозки, а также транспортные расходы каждого поставщика  20 Определить общую стоимость перевозимого товара от каждого поставщика и стоимость перевозимого товара каждым способом перевозки  

Таблица 7. Функции для анализа списка

Задание  1 Подсчитать средний оклад мужчин старше 50 лет  2 Подсчитать минимальный оклад у женщин, работающих в отделе Отдел  3 Подсчитать количество человек, принятых на работу после даты Дата  4 Подсчитать количество сотрудников отдела Отдел  5 Подсчитать количество студентов, обучающихся на факультете Факультет  6 Подсчитать, сколько студентов группы Группа по предмету Предмет оценку Оценка  7 Подсчитать средний балл в группе Группа по предмету Предмет  8 Подсчитать средний балл студента Фамилия по всем предметам  9 Подсчитать, сколько курсовых работ у группы Группа  10 Подсчитать общую нагрузку преподавателя Преподаватель  11 Определить, сколько лекционных курсов у преподавателя Преподаватель  12 Подсчитать, какой объем времени отводится преподавателю Преподаватель на проведение курсовых работ  13 Определить, на какую сумму был поставлен товар Товар от клиента Клиент  14 Определить, на какую сумму был отпущен товар Товар клиенту Клиент  15 Определить среднюю цену, по которой поставлялся товар Товар  16 Определить максимальную цену, по которой был продан товар Товар  17 Определить общую стоимость товара, перевозимого от поставщика Поставщик способом перевозки Способ_перевозки  18 Определить среднюю стоимость транспортных расходов для поставщика Поставщик  19 Определить среднюю стоимость транспортных расходов для способа перевозки Способ_перевозки  20 Определить максимальную стоимость товара, перевозимого от поставщика Поставщик  

Таблица 8. Проверка вводимых значений

Поле Вид сообщения об ошибке  1 Отдел: список значений Останов  2 Пол: список значений Предупреждение  3 Дата рождения Сообщение  4 Оклад: неотрицательное число Останов  5 Факультет: список значений Предупреждение  6 Оценка: список значений Сообщение  7 Дата сдачи экзамена Останов  8 Дата рождения Предупреждение  9 Ученая степень: список значений Сообщение  10 Должность: список значений Останов  11 Факультет: список значений Предупреждение  12 Вид занятий: список значений Сообщение  13 Менеджер: список значений Останов  14 Вид сделки: список значений Предупреждение  15 Количество Сообщение  16 Дата Останов  17 Способ перевозки: список значений Предупреждение  18 Количество поставленной продукции Сообщение  19 Дата поставки Останов  20 Общие транспортные расходы Предупреждение  

4.2. Пример получения индивидуального задания

Предположим, вы записаны под номером 5 в списке преподавателя, тогда из табл. 2 вы должны выбрать: №5; Деканат (Фамилия, Имя, Отчество, Дата рождения, Группа, Факультет, Предмет, Дата сдачи экзамена, Оценка); Значения поля Оценка: Отлично, Хорошо и т.д.

Сортировку следует проводить в два этапа, поэтому из табл. 3 сначала выбирать задание №5 для сортировки по 4-м и более полям: Фамилия, Имя, Отчество, Дата рождения, Факультет, а затем задание №5 для сортировки в особом порядке: Факультет.

Для поиска информации с помощью автофильтра из табл. 4 выбрать запрос №5: Отобразить информацию о студентах групп Группа1 и Группа2 по предмету Предмет с оценками Хорошо и Отлично.

Для поиска информации с помощью расширенного фильтра из табл. 5 выбрать запрос №5: Найти информацию о студентах факультетов Факультет1 и Факультет1, сдавших экзамены в период с Дата1 по Дата2.

Для подведения промежуточных итогов из табл. 6 выбрать задание №5: Определить среднюю оценку в каждой группе по каждому предмету.

Для анализа списка с помощью функций из табл. 7 выбрать задание №5: Подсчитать количество студентов, обучающихся на факультете Факультет.

Для проверки вводимых значений из табл. 8 выбрать поле и вид сообщения об ошибке №5: Факультет: список значений; Предупреждение.

Рекомендуемая литература

Гарнаев А. Ю. Использование MS Excel и VBA в экономике и финансах. - СПб: БХВ, 1999.

Маликова Л. В., Пылькин А. Н. Практический курс по электронным таблицам MS Excel. - М.: Горячая линия - Телеком, 2004.

Каганов В. И. Компьютерные вычисления в средах Excel и MathCad. - М.: Горячая линия - Телеком, 2003.

Столяров А. М., Столярова Е. С. Excel 2002. - М.: ДКМ Пресс, 2002.

Уокенбах Д. Подробное руководство по созданию формул в Excel 2002.: Пер. с англ. - М.:Издательский дом "Вильямс",2002.

1