Порядок выполнения.

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

Рис. 6.

Рис. 7.

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

Задание 3. Добавить, удалить несколько записей с помощью формы.

Порядок работы. Добавление и удаление записей с помощью формы – процедуры очень простые и быстрые. Чтобы добавить запись надо выполнить следующую последовательность действий:

1. Открыть окно формы.

2. Нажать кнопку Добавить. Excel создаст новую пустую запись – очистит поля редактирования и выведет на месте индикатора номера записи Новая запись.

3. Заполнить поля новой записи.

4. По окончании нажать кнопку Закрыть.

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

Для удаления записи с помощью формы нужно выполнить следующие шаги:

1. Открыть окно формы данных.

2. Найти запись для удаления.

3. Нажать кнопку Удалить. Excel предупреждает, что запись будет удалена окончательно.

4. Подтвердить удаление. Excel удалит запись.

5. Для возврата в экран листа нажать кнопку Закрыть.

При удалении записи таким образом Excel удаляет данные из строки и для заполнения образовавшегося промежутка смещает нижние записи вверх

.

Задание 4. С помощью формы вывести данные о сотрудниках старше 35 лет, у которых зарплата за 1 квартал < 40000.

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

Для поиска записей нужно:

1. Открыть окно формы.

2. Нажать кнопку Критерии. Excel выводит пустые поля записи и заменяет индикатор номера записи словом Критерии.

3. Зайти в поле, по которому будет выполняться поиск, и ввести критерий. В нашем задании в поле “Возраст” установим критерий < 35, а в поле “З/п 1 кв”: < 40000 (см. рис 8).

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

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

a) средний возраст женщин и средний возраст мужчин;

b) количество пенсионеров по возрасту (у мужчин – начиная с 60 лет, у женщин – с 55 лет);

c) среднюю зарплату секретарей в отделе КПО (за год);

d) количество мужчин в возрасте от 30 до 50 лет.

Рис. 8.

Порядок работы. Для списков в Excel существует набор функций, который носит название “функции баз данных” или Д-функции. Эти функции отличаются тем, что:

– подводят итоги для столбца таблицы;

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

Общий синтаксис функций баз данных:

Д-функция(список, поле, критерий).

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

С помощью мастера функций выбираем функции, относящиеся к разделу “Работа с базой данных” и затем функцию ДСРЗНАЧ(). Как было указано выше Д-функция имеет три аргумента. В качестве первого аргумента выбираем диапазон

Рис. 9.

ячеек, формирующих список. Вторым аргументом Поле задается заголовок столбца в двойных кавычках или число, представляющее номер столбца в списке. Третий аргумент – диапазон, содержащий задаваемые условия. Таким образом, Д-функция будет иметь следующий вид: ДСРЗНАЧ(A1: J11; D1; G21: G22). Аналогично надо поступить при вычислении среднего возраста мужчин, создав нужный критерий (см. рис.9).

Подсчитаем количество пенсионеров среди мужчин (пункт b)). Для этого нам понадобится критерий следующего вида:

В качестве Д-функции выбираем функцию БСЧЕТ() со следующими параметрами: БСЧЕТ(A1: J11; D1; B13: C14) (см. рис. 10).

Рис.10.

В пункте c) требуется подсчитать зарплату секретарей в отделе КПО (за год).

Здесь опять будет двойной критерий, но по полям Должность и Отдел. Чтобы подсчитать среднюю зарплату воспользуемся Д-функцией ДСРЗНАЧ(). В качестве второго параметра Д-функции выступает столбец J, в котором определена годовая зарплата сотрудника (см. рис.11).

Рис. 11.

Подсчитаем количество мужчин в возрасте от 30 до 50 лет (пункт d)). При создании критерия в данной задаче необходимо дважды использовать столбец Возраст, т.к. его значение должно быть > 30 и < 50. Поскольку определяется количество, то выбираем Д-функцию БСЧЕТ() (см. рис. 12).

Рис. 12.

Таким образом, мы привели несколько примеров работы с таблицей в Excel как с базой данных.

Контрольные вопросы.

1. Что называют списком в Excel?

2. Как получить форму для таблицы?

3. Как называются функции для работы с базой данных?

4. Можно ли при задании критерия выбирать значения нескольких столбцов?

5. Какие Д-функции вы знаете?

ЛИТЕРАТУРА

1. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. – СПб.: БХВ-Петербург, 2001. – 816 с.

2. Лавренов С.М. Сборник примеров и задач. – М.: Финансы и статистика, 2001. – 336 с.

3. Попов А.А. Excel: практическое руководство. – М.: ДЕСС КОМ, 2000. – 302 с.

СОДЕРЖАНИЕ

ВВЕДЕНИЕ………………………………………………………………………..... 3

Лабораторная работа №1……………………………………………...…….. 3

Лабораторная работа №2……………………………….………………….. 14

Лабораторная работа №3………………………………………………...... 22

ЛИТЕРАТУРА…………………………………………………………………….. 30