Учебно-практическое пособие «офисные компьютерные технологии». Часть II (стр. 11 )

Для вычисления среднего значения курса используется функция ДСРЗНАЧ. Синтаксис функции:

ДСРЗНАЧ(база_данных; поле; критерий)

База_данных - это интервал ячеек, формирующих базу данных.

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

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

В ячейку Н5 вставлена формула:

= ДСРЗНАЧ( А1:D13; “Курс”;A16:D17) ;

в ячейку Н6 - формула:

= ДСРЗНАЧ( А1:D9; “Курс”;A18:D19).

Рассмотрим правила формирования таблицы критериев.

Таблица критериев записывается в виде списка. Верхнюю строку списка составляют имена полей из базы данных, для которых ставятся условия. Сами условия записываются в следующие строки в соответствующие столбцы. Условия для полей в одной строке объединяются по логике “И”.

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

Таким образом, критерий в интервале A16:D17 (для банка “Альфа”) задает следующее условие: для всех записей, содержимое поля Банк которых равно “Альфа”, и содержимое поля Дата находится в интервале от 1.02.2000 до 3.02.2000 включительно, и содержимое поля Валюта равно “марка”.

Для каждого банка в данном случае требуется сформировать свою таблицу критериев. Для банка “Бета” таблица критериев находится в интервале A18:D19.

Логика таблицы критериев следующая:

·  условия для полей в одной строке объединяются по логике “И”;

·  условия в строках объединяются по логике “ИЛИ”;

·  пустая ячейка в таблице критериев означает условие “все записи” для данного поля.

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

	A	B	C	D
15		Таблица	критериев
16	Банк	Дата	Дата	Валюта
17	Альфа	>=1.02.2000	<=3.02.2000	доллар
18	Бета	>=1.02.2000	<=3.02.2000	доллар
19

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

15		Таблица	критериев
16	Банк	Дата	Дата	Валюта
17	=G5	=“>=“&ТЕКСТ(H1;”ДД. ММ. ГГ”)	=“<=“&ТЕКСТ(J1;”ДД. ММ. ГГ”)	=H2
18	Банк	Дата	Дата	Валюта
19	=G6	=“>=“&ТЕКСТ(H1;”ДД. ММ. ГГ”)	=“<=“&ТЕКСТ(J1;”ДД. ММ. ГГ”)	=H2
20

Поясним формулу =“>=“&ТЕКСТ(H1;”ДД. ММ. ГГ”).

Это выражение формирует в текстовом виде условие, задающее ограничение для даты, путем слияния строки “>=“ и строки с датой из ячейки Н1. Функция ТЕКСТ(H1;”ДД. ММ. ГГ”) переводит дату, содержащуюся в ячейке Н1, в текстовый формат по заданному шаблону ”ДД. ММ. ГГ” (см. разделы “Функции даты и времени” и “Форматы “Дата и “Время”).

¿  Практикум

ü  Отработайте рассмотренный выше пример 9.5.1. с расчетом среднего курса. Поместите таблицы на рабочем листе в тех же ячейках, что и в примере. Это облегчит вам выполнение последующих заданий.

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

ü  Найдите банк с максимальным курсом заданной валюты.

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

64.  Что в Excel понимается под термином “база данных”? Приведите пример таблицы, являющейся базой данных, и таблицы, которая не может быть интерпретирована как база данных. Дайте определение таких понятий, как поле, запись, структура базы данных.

65.  Какие способы формирования базы данных вы знаете? Что такое форма данных?

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

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

68.  Что такое сортировка данных? Как она выполняется? Чем сложная сортировка отличается от простой сортировки? Как изменить порядок сортировки? Как установить нестандартный порядок сортировки? Что такое список и для чего он нужен, как создать пользовательский список?

69.  В чем состоит задача поиска данных? Что такое критерий поиска и как его задать?

70.  В чем состоит задача фильтрации данных? Какие виды фильтрации реализованы в Excel? Чем автофильтр отличается от расширенного фильтра?

71.  Какие новые возможности для обработки данных предоставляют функции баз данных? Каков их общий синтаксис? Приведите примеры одной из функций баз данных.

72.  Что такое таблица критериев и для чего она служит? По каким правилам формируется таблица критериев? Какова логика объединения условий в таблице критериев?

10.  ЭЛЕМЕНТЫ УПРАВЛЕНИЯ

10.1.  Общие сведения

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

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

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

Элемент управления можно редактировать (форматировать):

·  вызовите контекстное меню, щелкнув по элементу управления правой кнопкой мыши;

·  выполните Формат объекта...

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

Можно изменить размеры элемента управления, переместить \ скопировать его:

·  сделайте элемент управления активным, щелкнув по нему правой кнопкой мыши ;

·  уберите контекстное меню, нажав Escape;

·  перемещение \ копирование или изменение размеров осуществите стандартным для Windows-объектов способом;

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

Для удаления элемента управления в контекстнозависимом меню существует команда Вырезать.

Использование некоторых элементов управления рассмотрим на примере 9.5.1. (см. раздел “Функции работы с базой данных”) с расчетом среднего курса валют.

Для встраивания элементов управления в рабочие листы служит панель инструментов Формы:

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

·  выберите Формы.

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

10.2.  Метка

Используется для создания надписей на элементах управления и в диалоговых окнах. Формат шрифта текста метки нельзя изменить.

Для создания метки:

·  включите инструмент Надпись;

·  указателем-крестиком мыши отметьте зону надписи;

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

10.3.  Группа элементов

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

10.4.  Переключатель

Это элемент управления, который имеет два состояния: включено \ выключено. Переключатель должен быть связан с одной из ячеек таблицы. Если переключатель включен (включенное состояние отмечается появлением точки внутри переключателя), то в связанную ячейку будет выведено логическое значение ИСТИНА или число 1; если выключен, - то ЛОЖЬ или число 0. Изменение состояния переключателя происходит после щелчка по нему мышкой.

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

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

¿  Практикум

ü  Модифицируйте пример с расчетом среднего курса таким образом, чтобы название банка задавалось с использованием переключателей:

·  Создайте группу из двух переключателей с метками “Альфа” и “Бета” по названию банков.

·  Свяжите переключатели, например, с ячейкой М1.

·  В таблицу критериев A16:D17 в ячейку A17 поместите формулу =ЕСЛИ(М1=1; ”Альфа”; ”Бета”).

·  Уберите лишние элементы из модифицированной таблицы.

10.5.  Флажок

В группе флажков, в отличие от переключателей, может быть включен как один, так и несколько флажков одновременно. Флажок можно использовать как отдельный элемент. В этом случае в связанную ячейку будет выведено состояние флажка (1 или ИСТИНА, если флажок включен, и 0 или ЛОЖЬ, если флажок выключен). Изменение состояния флажка происходит после щелчка по нему мышкой (включенное состояние отмечается появлением "галочки" внутри флажка).

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

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

¿  Практикум

ü  Модифицируйте пример с расчетом среднего курса таким образом, чтобы можно было определить средний курс как по любому из банков отдельно, так и по обоим банкам сразу:

·  Создайте группу из двух флажков с метками “Альфа” и “Бета”.

·  Свяжите флажки соответственно с ячейками М1 и М2.

·  В ячейке Н5 модифицируйте в формуле функцию ДСРЗНАЧ, поставив на место аргумента, задающего таблицу критериев, функцию ЕСЛИ(М1;ЕСЛИ(М2;A16:D18;A16:D17);A19:D20)).

·  Модифицируйте таблицу критериев так, как это представлено ниже в таблице 10.1.

Таблица 10.1.

	A	B	C	D
116	Банк	Дата	Дата	Валюта
117	Альфа	>=01.02.2000	<=03.02.2000	марка
118	Бета	>=01.02.2000	<=03.02.2000	марка
119	Банк	Дата	Дата	Валюта
120	Бета	>=01.02.2000	<=03.02.2000	марка

10.6.  Списки

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

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

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

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

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

¿  Практикум

ü  Организуйте в примере с расчетом среднего курса выбор валюты из раскрывающегося списка.

10.7.  Счетчики

Счетчики предназначены для установки некоторого значения в диапазоне от минимального (минимум 0) до максимального (максимум 30000) с заданным шагом (минимум 1). Установленное число помещается в заданную ячейку. Параметры счетчика устанавливаются как обычно на панели Элемент управления диалога Формат элемента управления.

Чтобы расширить диапазон изменения числа, следует использовать дополнительную ячейку с формулой. Например, необходимо изменять число в диапазоне от -60000 до 60000 с шагом 4. Для этого в некоторую ячейку следует поместить формулу =А1*4-60000, где А1 - ячейка вывода результата, набранного в счетчике. При изменении значения в ячейке А1 от 0 дос шагом 1, значение в ячейке с формулой будет изменяться в диапазоне от -60000 до 60000 с шагом 4.

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

¿  Практикум

ü  Установите в примере с расчетом среднего курса валют ввод временных дат для расчета курса с использованием счетчика - полосы прокрутки:

·  создайте счетчик рядом с ячейкой Н1 (начальная дата);

·  задайте для счетчика начальное значение 0, максимальное значение - 365, шаг изменения - 1, ячейку для помещения результата - М4;

·  в ячейку Н1 поместите формулу =М4+36526 (число 36526 соответствует дате 1.01.2000);

·  аналогично автоматизируйте ввод конечной даты.

10.8.  Динамические диаграммы

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

Рассмотрим пример построения такой динамической диаграммы.

¿  Практикум

ü  Создайте рабочий лист "Графики".

ü  Поместите на него таблицу, состоящую из столбца Х, задающего значения аргумента Х, и трех столбцов, каждый из которых содержит значения следующих функций: Y1=EXP(x)/200; Y2=x^2+a; Y3=x^3/10. Здесь значения аргумента x подставляются из соответствующей ячейки столбца Х; коэффициент а представляет из себя некоторый переменный параметр фунции Y2, значение которого хранится в ячейке F1. Например, ячейка B2 содержит формулу = EXP(A2)/200.

Значения таблицы для а=2 представлены в таблице 10.2.

Таблица 10.2.

	A	B	C	D	E	F
1	X	Y1	Y2	Y3		2
2	-10	2,3E-07	102	-100
3	-9	6,2E-07	83	-72,9
4	-8	1,7E-06	66	-51,2
5	-7	4,6E-06	51	-34,3
6	-6	1,2E-05	38	-21,6
7	-5	3,4E-05	27	-12,5
8	-4	9,2E-05	18	-6,4
9	-3	0,00025	11	-2,7
10	-2	0,00068	6	-0,8
11	-1	0,00184	3	-0,1
12	0	0,005	2	0
13	1	0,01359	3	0,1
14	2	0,03695	6	0,8
15	3	0,10043	11	2,7
16	4	0,27299	18	6,4
17	5	0,74207	27	12,5
18	6	2,01714	38	21,6
19	7	5,48317	51	34,3
20	8	14,9048	66	51,2
21	9	40,5154	83	72,9
22	10	110,132	102	100

ü  Постройте на одной диаграмме графики трех функций Y1=EXP(X)/200; Y2=X^2+a и Y3=X^3/10.

Ÿ  Сделайте активной одну из ячеек интервала, содержащего таблицу данных.

Ÿ  Щелкните по инструменту Мастер Диаграмм.

Ÿ  На шаге 1 на панели Стандартные в поле Тип выберите тип диаграммы График.

Ÿ  На шаге 1 в поле Вид выберите первый по счету вид графика.

Ÿ  На шаге 2 Мастера Диаграмм проверьте, правильно ли Excel воспринял диапазон данных для построения диаграммы (должно стоять $A$1:$D$22).

Ÿ  На шаге 2 выберите ряды данных В столбцах.

Ÿ  На шаге 2 на панели Ряд в поле Ряд удалите ряд Х.

Ÿ  На шаге 2 на панели Ряд в поле Подписи оси Х установите адрес блока ячеек, задающих абсциссу Х, для этого щелкните по этому полю мышкой, затем выделите в таблице данных блок A2:A22.

Ÿ  На шаге 3 на панели Заголовки введите название диаграммы, например, "Кривые функций", и названия осей, например, "Х" и "Y".

Ÿ  Щелкните по Готово.

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

ü  Изменяя значение параметра а в ячейке F1, проследите за изменением положения графика функции Y2.

ü  Автоматизируйте процесс изменения параметра а функции Y2 с помощью элемента управления Счетчик. Установите диапазон изменения параметра от 0 до 100 с шагом 5.

·  Создайте элемент управления Счетчик рядом с ячейкой F1.

Ÿ  Выведите на экран панель инструментов Формы.

Ÿ  Включите инструмент Счетчик.

Ÿ  Указателем-крестиком мышки отметьте зону, которую будет занимать счетчик, рядом с ячейкой F1.

·  Задайте свойства Счетчика.

Ÿ  Вызовите контекстное меню, щелкнув правой кнопкой мыши по счетчику.

Ÿ  Выполните команду Формат объекта.

Ÿ  На панели Элемент управления задайте для счетчика: начальное значение - 30; максимальное значение - 200; шаг изменения - 5; ячейку для помещения результата - F1.

Ÿ  Щелкните по ОК

·  Изменяя с помощью счетчика значение параметра а в ячейке F1, проследите за изменением положения графика функции Y2.

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

73.  Какие элементы управления вы знаете? Как создается и редактируется элемент управления?

74.  Что такое группа элементов? Как ее создать? Какие новые свойства появляются у переключателей и флажков при их объединении в группу?

75.  Как создаются переключатели и флажки? Чем переключатель отличается от флажка? Что содержит связанная с переключателем \ флажком ячейка? Когда предпочтительнее использовать флажки, а когда переключатели?

76.  Каково назначение списка? Какие существуют разновидности списков? Как их создать?

77.  Для чего предназначены счетчики и как они создаются? Как изменить диапазон и шаг счетчика? Какие дополнительные возможности предоставляет полоса прокрутки по сравнению с просто счетчиком?

  ЛИТЕРАТУРА

1.  Информатика. Базовый курс / и др. – Спб: "Питер", 2001.

2.  М. Додж, К. Стинсон. Эффективная работа с Microsoft Excel 2000. – Спб: "Питер", 2000.

3.  Excel 97 для Windows для "чайников": Краткий справочник. – М., СПб.: "Диалектика", 1998.

4.  Microsoft Office 97: Справочник. – М., СПб., Харьков, Минск: "Притер", 1998.

5.  Р. Персон. MS Excel 97 в подлиннике: в 2 т.: пер. с англ. – Спб.: "BHV - Санкт-Петербург", 1997. Том 1.