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

·  Ограничение диапазона вводимых данных.

·  Выбор вводимых данных из списка.

·  Ограничение количества вводимых в ячейку символов.

·  Определение правильности вводимых данных с помощью формулы.

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

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

¿  Практикум

ü  Задайте для столбца "Дата" таблицы курсов валют ограничение на диапазон вводимых значений: только даты 2000-ого года.

·  Выделите блок ячеек базы, содержащий даты (D2:D13).

·  Выполните команду Проверка меню Данные.

·  В диалоге Проверка вводимых значений перейдите на панель Параметры.

· в списке Тип данных выберите "Дата";

· в списке Значение установите "между";

· задайте начальную дату "1.01.00", а конечную – "31.12.00";

· щелкните по Ok.

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

ü  Задайте для столбца "Дата" подсказку оператору о допустимых значениях для этого столбца.

·  Выделите блок ячеек базы, содержащий даты (D2:D13).

·  Выполните команду Проверка меню Данные.

·  В диалоге Проверка вводимых значений перейдите на панель Сообщение для ввода.

· в поле Заголовок наберите "Диапазон допустимых значений";

· в поле Сообщение наберите "Только за 2000 год.";

· щелкните по Ok.

ü  Попробуйте ввести в одну из ячеек столбца "Дата" дату за 2001 год, последите за реакцией системы.

9.3.  Обработка данных

9.3.1.  Редактирование полей

Для редактирования полей:

сделайте текущей любую из ячеек списка;

выполните команду Форма меню Данные;

·  используя линейку прокрутки или команды Назад \ Далее, найдите требуемую запись;

·  измените содержимое полей;

·  после внесения всех изменений нажмите Enter или выполните команду Закрыть.

¿  Практикум

ü  Измените курсы валют в пятой и восьмой записях базы.

9.3.2.  Сортировка данных

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

Excel использует следующий порядок для сортировки по возрастанию:

·  числа;

·  текст;

·  логические значения;

·  значения ошибок;

·  пустые ячейки.

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

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

Восстановить прежнее расположение записей в списке после сортировки можно только если сразу после сортировки щелкнуть по инструменту Отменить.

Для проведения сортировки:

·  сделайте текущей одну из ячеек базы данных;

·  выполните команду Сортировка меню Данные (развернется диалог Сортировка диапазона);

·  в окне Сортировать по наберите имя поля базы данных, по которому будет проведена сортировка и установите флаг По возрастанию или По убыванию;

·  щелкните по Ок.

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

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

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

Пример сложной сортировки базы из таблицы 9.1 представлен в таблице 9.3.

Таблица 9.3.

	А	В	C	D
1		Список	студентов	факультета
2	Номер группы	Ф. И.О.	Год рождения	Адрес
3	М1		1977	Фрунзе, 15-31
4	М1		1978	Строителей, 25-3
5	М2		1977	Твардовского, 4-10
6	М2		1978	Багратиона, 22-43
7	М2		1977	Кирова, 4-26

9.3.3.  Изменение стандартного порядка сортировки

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

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

Можно создать свой пользовательский список для нестандартной сортировки. Сделать это можно на панели Списки диалога Параметры (команда Параметры меню Сервис).

¿  Практикум

ü  Отсортируйте базу:

·  в порядке курсов валют;

·  в алфавитном порядке названий банков;

·  используя сложную сортировку, в порядке: алфавитный порядок названий банков + алфавитный порядок названий валют + дата по возрастанию.

9.3.4.  Поиск данных

Задача поиска состоит в нахождении записей базы, поля которых содержат заданные при поиске значения (критерий поиска).

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

·  выполните команду Критерии;

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

·  используя кнопки Далее \ Назад просмотрите все записи, удовлетворяющие критерию поиска;

·  для выхода выполните команду Закрыть.

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

¿  Практикум

ü  Найдите записи, содержащие сведения по курсу валют для банка Альфа.

ü  Найдите записи, содержащие сведения по курсу марки банка Бета за заданное число.

9.3.5.  Фильтрация данных

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

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

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

Отфильтрованные автофильтром данные будут выведены на том же месте, где находится исходная база.

Для выполнения автофильтрации:

·  сделайте текущей одну из ячеек базы;

·  выполните команду Фильтр меню Данные, затем включите режим Автофильтр;

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

Раскрывающийся список содержит уникальные значения, встречающиеся в записях данного поля. Кроме того, список позволяет задать критерий фильтра в виде условия (пункт Условие... списка). Условие может быть сложным, из двух логических выражений, объединенных по логике “И” или “ИЛИ”.

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

Расширенный фильтр в отличие от автофильтра позволяет выводить отфильтрованные данные как на старом, так и на новом месте. Кроме того, в расширенном фильтре можно задать более сложный критерий фильтра, который реализуется с использованием так называемой таблицы критериев (см. раздел “Функции баз данных”).

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

¿  Практикум

ü  Используя автофильтр, выведите данные:

·  только по банку Альфа;

·  только по банку Бета и валюте марка;

·  только за заданный интервал времени;

·  только для курсов марки, находящихся в заданном диапазоне.

9.4.  Подведение промежуточных итогов

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

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

Затем, вычислить средний курс доллара, используя функцию ПРОМЕЖУТОЧНЫЕ. ИТОГИ.

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

ПРОМЕЖУТОЧНЫЕ. ИТОГИ(номер_функции; диапазон_ячеек)

Здесь:

Номер_функции - это число от 1 до 11, которое указывает, какую функцию использовать при подведении итогов.

Номера некоторых функций:

1	СРЗНАЧ	Подсчитывает среднее значение из диапазона ячеек
2	СЧЕТ	Подсчитывает количество чисел в диапазоне ячеек
4	МАКС	Находит максимальное значение в диапазоне ячеек
5	МИН	Находит минимальное значение в диапазоне ячеек
6	ПРОИЗВЕД	Подсчитывает произведение чисел в диапазоне ячеек
9	СУММ	Находит сумму чисел в диапазоне ячеек

Функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ игнорирует все скрытые строки, которые получаются в результате фильтрации списка. Т. е. из заданного диапазона_ячеек в подведении итогов участвуют только данные из видимых ячеек.

Для подсчета среднего курса функция будет иметь следующий вид:

ПРОМЕЖУТОЧНЫЕ. ИТОГИ(1;C2:C13),

Где С2:C13 – диапазон ячеек, содержащий данные по курсам валют.

¿  Практикум

ü  Определите средний курс марки в банке "Бета" в период с 1.02.2000 по 2.02.2000.

·  Используя автофильтрацию данных (см. предыдущий раздел) для таблицы на рабочем листе "Курсы валют" выведите только данные по банку Бета для марки за период с 1.02.2000 по 2.02.2000.

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

·  Щелкните по кнопке S (автосумма).

В ячейку автоматически будет вставлена функция ПРОМЕЖУТОЧНЫЕ. ИТОГИ

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

·  На месте первого аргумента функции исправьте число 9 (что задает операцию суммирования) на число 1 (что задает операцию усреднения).

·  Нажмите Enter.

9.5.  Функции работы с базой данных

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

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

Критерий может включать в себя условия для каждого поля базы, объединенные как по логике “И”, так и по логике “ИЛИ”. Функции баз данных, как и все остальные функции Excel, возвращают значение, т. е. какое-то число.

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

Большинство функций баз данных имеют однотипный синтаксис:

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

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

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

Критерий - это интервал ячеек, который содержит критерий для обработки базы данных. Ссылка на критерий может быть введена как интервал ячеек, или как имя интервала.

Набор функций баз данных включает следующие функции.

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

БДДИСП Оценивает дисперсию по выборке из выделенной части базы данных.

БДДИСПП Вычисляет дисперсию по генеральной совокупности из выделенной части базы данных.

БДПРОИЗВЕД Перемножает значения определенных полей записей базы данных, удовлетворяющих критерию.

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

БИЗВЛЕЧЬ Извлекает из базы данных одну запись, удовлетворяющую заданному критерию.

БСЧЁТ Подсчитывает количество числовых ячеек в выборке из заданной базы данных по заданному критерию.

БСЧЁТА Подсчитывает количество непустых ячеек в выборке из заданной базы данных по заданному критерию.

ДМАКС Возвращает максимальное значение среди выделенных фрагментов базы данных.

ДМИН Возвращает минимальное значение среди выделенных фрагментов базы данных.

ДСРЗНАЧ Возвращает среднее значение выбранных фрагментов базы данных.

ДСТАНДОТКЛ Оценивает стандартное отклонение по выборке из выделенной части базы данных.

ДСТАНДОТКЛП Вычисляет стандартное отклонение по генеральной совокупности из выделенной части базы данных.

ПРОМЕЖУТОЧНЫЕ.ИТОГИ Возвращает итог для списка или базы данных.

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

Пример 9.5.1. Дана таблица курсов валют. Требуется определить среднюю величину курса за заданный интервал времени для каждого банка. Количество валют - 2. В итоговой таблице интервал времени и валюта, средний курс которой подсчитывается, набирается оператором.

Решение задачи представлено в таблице 9.4.

Таблица 9.4.

	A	B	C	D	E	F	G	H	I	J
1	Банк	Валюта	Курс	Дата			Ср. курс с	1.02.2000	по	3.02.2000
2	Альфа	Доллар	29	1.02.2000			Валюта	марка
3	Альфа	Марка	13	1.02.2000
4	Бета	Доллар	29,3	1.02.2000			Банк	Ср. курс
5	Бета	Марка	13,5	1.02.2000			Альфа	13,07
6	Альфа	Доллар	29	2.02.2000			Бета	13,60
7	Альфа	Марка	12,9	2.02.2000
8	Бета	Доллар	29,4	2.02.2000
9	Бета	Марка	13,8	2.02.2000
10	Альфа	Доллар	29,2	3.02.2000
11	Альфа	Марка	13,3	3.02.2000
12	Бета	Доллар	29,5	3.02.2000
13	Бета	Марка	13,5	3.02.2000
14
15		Таблица	критериев
16	Банк	Дата	Дата	Валюта
17	Альфа	>=1.02.2000	<=3.02.2000	марка
18	Банк	Дата	Дата	Валюта
19	Бета	>=1.02.2000	<=3.02.2000	марка
20

Ячейки, в которые вставлены формулы, выделены в таблице серым фоном. Собственно база данных, содержащая сведения о курсах валют, находится в интервале А1:D13. Пользователю, чтобы определить средний курс валюты, необходимо набрать валюу в ячейке H2 и интервал времени: с какого числа ( в ячейке Н1) и по какое число ( в ячейке J1). Средний курс заданной валюты за заданный интервал времени выводится для банка “Альфа” в ячейку Н5, для банка “Бета” - в ячейку Н6.