Файл: Учебнометодическое пособие Работа со списками в среде Microsoft Excel 2007.pdf

15
Исходный список нужно отсортировать по полю Отдел. Установить При
каждом изменении в: Отдел. Поскольку список был отсортирован по полю
Отдел, то строки с одинаковым отделом располагаются непосредственно рядом друг с другом.
Как только происходит изменение в поле отдел, это значит, информация о сотрудниках одного отдела закончилась, и далее следуют строки, касающие- ся сотрудников другого отдела. В списке Операция: можно выбрать опера- цию, с помощью которой будут подводиться промежуточные и общие итоги.
В списке Добавить итоги по: нужно указать, по какому (каким) полю (по- лям) подводить итоги.
Рис. 2. Диалоговое окно Промежуточные итоги
Если в списке неоднократно подводятся итоги, то установка флажка Заме-
нить текущие итоги приведет к тому, что итоги полученные ранее будут заменены новыми. В том случае, если этот флажок сбросить, то каждый раз к предыдущим итогам будут добавляться новые (итоги, полученные ранее, удаляться не будут).

16
Пусть, например, нужно, чтобы каждая группа строк располагалась на от- дельной странице для последующей печати. Для этого нужно установить флажок Конец страницы между группами.
Если установлен флажок Итоги под данными, то промежуточные и общие итоги будут расположены под данными, а если этот флажок сброшен - то над данными.
В результате получим:
Рис. 3. Результат

17
Таблица 5
Подведение промежуточных итогов
Вариант Задание
0
Определить средний оклад и сумму всех окладов в каждом отделе
1
Определить количество и средний возраст сотрудников в каждом отде- ле
2
Определить количество мужчин и женщин на предприятии и средний оклад у мужчин и у женщин
3
Определить количество студентов в каждой группе и на каждом фа- культете
4
Определить среднюю оценку в каждой группе и по каждому предмету
5
Определить количество студентов и средний балл на каждом факульте- те
6
Определить по каждому менеджеру сколько товара поставлено, отпу- щено и возвращено
7
Определить для каждой страны –производителя общее количество и общую сумму поставленной продукции.
8
Определить для каждого менеджера сумму по каждому виду операций.
9
Определить по каждому виду товаров на какую сумму их всего постав- лено, возвращено и отпущено.
1.6. Функции баз данных
Обратите внимание! В этом задании необходимо выбрать подходящую по описанию функцию из категории «Работа с базой данных». (Мастер функ- ций f
x
→ категория: Работа с базой данных).
Функции баз данных имеют обобщенное название Д-функции. Д-функции оперируют только с элементами диапазона, которые удовлетворяют задан- ным условиям. У всех Д-функций один и тот же синтаксис:
=Дфункция(база_данных; поле; критерий).

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

19
Таблица 6
Функции из категории «Работа с БД»
Вариант Задание
0
Подсчитать средний оклад мужчин старше 40 лет
1
Подсчитать минимальный оклад у женщин, работающих в бух- галтерии
2
Подсчитать количество сотрудников в гараже
3
Подсчитать средний балл студентов экономического факультета
4
Подсчитать средний балл в группе Ю1
5
Подсчитать какое количество студентов получили по предмету
математикаоценкуотлично
6
Определить на какую сумму были поставлены товары от клиента
ЗАО Х
7
Определить на какую сумму осуществил продажи менеджер Ан- дрей
8
Определить на какую сумму был принят товар от пр-ля Уткина.
9
Определить максимальную сумму по продажам.
1.7. Проверка вводимых значений
Задание. MS Excel предлагает специальное средство, позволяющее прове- рять, удовлетворяют ли заданным условиям вводимые в список значения.
Можно задать допустимый тип данных (например, целые числа, даты, время или текст), диапазон разрешенных значений (например, целые числа от 1 до
31), указать перечень разрешенных значений (например, названия отделов предприятия).

20
Рис.5. Проверка вводимых значений
Чтобы задать условия проверки данных, нужно выделить диапазон ячеек, к которому должны применяться эти условия, затем воспользоваться коман- дой Данные-Проверка данных... На экране появится окно диалога Проверка
вводимых значений (рис.5), содержащее три вкладки: Параметры, Сообще-
ние для ввода, Сообщение об ошибке.
Вкладка Параметры позволяет задать тип и интервал значений, которые разрешается вводить. Тип значений выбирается из списка Тип данных. Ин- тервал значений задается с помощью списка Значение.
Чтобы задать список допустимых значений, его нужно сначала сформиро- вать на рабочем листе, а потом в раскрывающемся списке Тип данных вы- брать вариант Список и в поле Источник указать диапазон, в котором хра- нится список допустимых значений. Можно не создавать список на рабочем листе. Тогда значения для раскрывающего списка нужно указать в поле Ис-
точник, вводя их вручную и отделяя их друг от друга точкой с запятой.
Чтобы задать подсказку, которую Excel будет выводить при вводе значений в заданный диапазон, в окне диалога Проверка вводимых значений нужно воспользоваться вкладкой Сообщение для ввода. Здесь можно ввести заго-

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

22
Таблица 7
Данные- Проверка данных
Если в варианте предлагается создать список правильных значений (эта- лон), его необходимо создать до начала настройки проверки данных.
Вариант
Поле
Вид сообщения
об ошибке
0
Отдел: список значений
Останов
1
Пол: список значений
Предупреждение
2
Должность: список значений
Сообщение
3
Факультет: список значений
Предупреждение
4
Оценка: список значений
Останов
5
Дата сдачи экзамена: 2011год
Сообщение
6
Дата операции: 2011 год
Останов
7
Вид сделки: список значений
Останов
8
Количество: неотрицательное число
Предупреждение
9
Производитель: список значений.
Сообщение

23
Литература
1. Гельман В.Я. Решение математических задач средствами MS Excel:
Практикум – СПб.: Питер, 2003.
2. Маликова Л.В., Пылькин А.Н. Практический курс по электронным таб- лицам MS Excel: Учебное пособие для ВУЗов- М: Горячая линия-
ТЕЛЕКОМ, 2004.
3. Гарнаев А.Ю. Использование Excel и VBA в экономике и финансах.-
БХВ-Петербург, 2001.
4. Материалы сайта http://www.intuit.ru., О.В. Спиридонов, MS Excel 2007.
5. Материалы сайта http://office.microsoft.com/ru-ru/.