VI Функции Работа с Базой Данных

Эти функции дают возможность определения Итогов с учётом Критериев различного уровня сложности в Списках (Базах Данных). Наиболее часто используются функции:

БДСУММ (база_данных; поле; критерий)

БСЧЁТ (база_данных; поле; критерий)

ДМАКС (база_данных; поле; критерий)

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

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

поле – аргумент типа Текст, который должен содержать имя столбца Базы данных или типа Число – номер столбца Базы данных;

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

Пример

	А	В	С	D	Е
1	Факультет	Курс	Группа	ФИО	Сумма оплаты
2	Заочный	2			0
3
4	Факультет	Курс	Группа	ФИО	Сумма оплаты
5	Заочный	1	3	Стрижак Н.А.	1100
6	Заочный	2	4	Васильев Е. М.	2200
7	Заочный	2	2	Бердникова А. И.	0
8	Коммерческий	3	5	Шуров К. А.	2000
9	Коммерческий	3	1	Сукачёва А. В.	2000
10	Заочный	2	6	Параскевич Н.Г.	2200

Функция БДСУММ (А4:Е10; “Сумма оплаты”; А1:В2) выдаёт результат 4400

Функция БСЧЁТ (А4:Е10; “Сумма оплаты”; А1:В2) выдаёт результат 3

Функция БСЧЁТ (А4:Е10; “Сумма оплаты”; А1:Е2) выдаёт результат 1 (*)

Для определения количества студентов Заочного факультета, оплативших контракт, строка 2 должна иметь вид:

2

Заочный

>0

Тогда функция (*) выдаст результат 3

Функции Баз Данных имеет смысл применять для больших списков, а для удобства записи функций рекомендуется таблице присвоить имя (ВСТАВКА – ИМЯ – ПРИСВОИТЬ)

2. Порядок выполнения задания 1

Задание выполняется средствами системы EXCEL В каждом варианте предлагается создать одну или более таблиц указанной структуры. Таблица должна содержать не менее 10 строк, но в отдельных вариантах указано непосредственно количество строк. В некоторых таблицах под «шапкой» приводится строка с обозначениями данных, это необходимо для описания алгоритмов расчётов, но в готовой таблице такая строка может не присутствовать.

Знак ? в клетке таблицы означает необходимость ввода формулы. Функции, которые используются в расчётах, описаны в общей части Методических указаний

В контрольной работе студент должен:

• описать процесс создания таблицы, указав используемые средства Форматирования (данных и «шапки» таблицы;

• привести необходимые формулы и принципы использования в них Функций;

• представить результаты расчётов на компьютере в распечатанном виде.

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

На листе СПРАВОЧНИК введите таблицу

Процессор - ОП	Цены с учётом объёма винчестера.
	160GB	1000GB	1TB
Athlon XP – 512 MB	2 462	2 532	2 572
Dell Intel Xeon – 4 ГВ	2 492	2 562	2 602
АМD Athlon – 1024 MB	2 645	2 715	2 755
VIA8650-512 MB	2 675	2 745	2 785
WIN XP SP2 – 4ГВ	4 332	4 402	4 442
INTEL Core2 – 512MB	4 362	4 432	4 471
Pentium G6950-512MB	5 849	5 520	5 559
Athlon XP - 1024 MB	5 450	5 919	5 959

На листе ЗАКАЗ создайте таблицу приведенного ниже вида.

Выполните установку для автоматизации ввода данных в столбец Процессор-ОП

Алгоритм расчёта:

• выполните установку для автоматического заполнения типа Процессора в ЗАКАЗе на основании первого столбца в СПРАВОЧНИКЕ

• столбец Цена заполняется автоматически на основании таблицы на листе СПРАВОЧНИК в соответствии с заданными параметрами персонального компьютера (Процессор-ОП, Объём винчестера)

• В расчётах следует учесть Скидку на количество заказанных компьютеров, предоставляемую фирмой:

при количестве компьютеров одного вида от 3 до 4 скидка составляет 5%,

при количестве компьютеров одного вида от 5 до 9 скидка составляет 8%,

при количестве компьютеров одного вида от 10 и выше скидка составляет 10%,

Бланк-заказ

Название организации Школа №43  

Номер счёта 234/890  

Адрес ул. Гоголя  

Телефон 23-45-90  

ФИО исполнителя Привалова Г.А

Дата ?

Процессор - ОП	Объём винчест.	Кол -во	Цена	Сумма	Скидка %	Скидка грн.	Сумма к оплате
Pentium IV 1300-128K	30	2	?	?	?	?	?
Pentium III 850-64K	30	5	?	?	?	?	?
Селерон 850-128К	20	7	?	?	?	?	?
Селерон 633- 64К	20	12	?	?	?	?	?
Селерон 633-128К	30	5	?	?	?	?	?
Pentium IV 1300-256K	40	1	?	?	?	?	?
Pentium III 850-128K	30	3	?	?	?	?	?

. ИТОГО    

Решение:

1. Подготовка формы таблицы (лист Заказ) :

а) составим таблицу распределения колонок таблиц между столбцами документа и зададим тип и длину данных :

Название столбца	Название колонки документа	Тип	Длина
A	Процессор - ОП	текст	25
B	Объём винчестера	число	99
C	Кол-во	число	99
D	Цена	формула	9999
E	Сумма	формула	99999
F	Скидка %	формула	99
G	Скидка грн.	формула	999,99
H	Сумма к оплате	формула	99999,99

б) выделение строк таблицы для заголовка, “шапки” и строк документа:

Заголовок занимает 7 строк:1:7

“шапка” документа занимает 1 строку: 8-ю, значит, 20 строк данных занимают строки 9:28 таблицы

в) форматирование колонок таблицы :выполняется с помощью команд:

A ФОРМАТ - СТОЛБЕЦ - ШИРИНА -25

B F8 F ФОРМАТ - СТОЛБЕЦ - ШИРИНА -6

ФОРМАТ -ЯЧЕЙКИ - ФОРМАТ ЯЧЕЕК - ЧИСЛО - ДЕНЕЖНЫЙ

G F8 H ФОРМАТ - СТОЛБЕЦ - ШИРИНА – 8

ФОРМАТ -ЯЧЕЙКИ - ФОРМАТ ЯЧЕЕК - ЧИСЛО - ДЕНЕЖНЫЙ

D ФОРМАТ - ЯЧЕЙКИ - ФОРМАТ ЯЧЕЕК - ЧИСЛО - ДЕНЕЖНЫЙ

2. Набор заголовка и “ шапки” документа в строках 1:8

Ввод текущей даты в заголовке:  F7 =СЕГОДНЯ()

2. Для автоматического заполнения типа Процессора в ЗАКАЗе выполним действия:

Данным 1-го столбца СПРАВОЧНИКА присвоим имя ПРОЦЕССОР: ВСТАВКА – ИМЯ – ПРИСВОИТЬ

Выделим в таблице Заказ клетки столбца Процессор-ОП и выполниме команду ДАННЫЕ - ПРОВЕРКА. В окне диалога задайте:Тип данных – Список Установив курсор в поле Источник, выделим вставим имя = ПРОЦЕССОР интервал А6:А13 листа Справочник столбец Цена заполняется автоматически из таблицы на листе СПРАВОЧНИК

4. Ввод данных в таблицу: столбцы А:С

5. Запись формул в соответствующие клетки таблицы D9:Н9 :

D9 = ВПР(А9;СПРАВОЧНИК!А3:D10;ЕСЛИ(В9=20;2;

ЕСЛИ(В9=30;3;4));ЛОЖЬ)

 Е9 =В9* D9

 F9 = ЕСЛИ(С9<3;0%;ЕСЛИ(С9<=4;5%;ЕСЛИ(С9<=9;8%;10%)))

 G9 = Е9* F9  Н9 = Е9- G9

6. Копирование формул из клеток D9:Н9 в строки таблицы 10:28 :

 D9 F8 Н9 АВТОЗАПОЛНЕНИЕ Н10:Н28

7. Запись формул итоговых сумм в клетки таблицы в клетки С29,E29, G29:Н29 :

С29 =СУММ(С9:С28);

Е29 =СУММ(Е9:Е28);

 G29 =СУММ( G9: G 8)

 G29 АВТОЗАПОЛНЕНИЕ Н29

8. Защита формул . Формулы занимают блоки клеток D9:Н29

Поэтому для их защиты необходимо предварительно разблокировать остальные клетки, которые можно объединить в диапазоны: A1:Н8 (заголовок и “шапка”) , A9:С29 (данные)

SHIFT + F8 A1:Н8 A9:С29

ФОРМАТ - ЯЧЕЙКИ- ФОРМАТ ЯЧЕЕК- ЗАЩИТА -ЗАЩИЩАЕМАЯ ЯЧЕЙКА и выполнить команду защиты листа:

СЕРВИС - ЗАЩИТА - ЗАЩИТИТЬ ЛИСТ

9. Закрепление “шапки “ документа и левой колонки. “Шапка” документа заканчивается в строке 8 , потому для её закрепления устанавливаем курсор в клетке B9:

B9 ОКНО – ЗАКРЕПИТЬ ОБЛАСТИ

10. Сохранение таблицы во внешней памяти в виде файла ПРИМЕР 1: ФАЙЛ - СОХРАНИТЬ – ПРИМЕР 1