Изучение средств работы с базами данных Base

Задания на выполнение

Запустить программу командой Пуск\Программы\Libre Office\LibreOffice Calc.

Задание 1. Создание связанных списков данных

1. Присвоить Листу1 имя База Данных, а Листу2 – Отчет.

2. Создать базу данных «Комплектация рабочих мест», представленную на рис. 7.1. Таблицу разместить, начиная с 6-й строки листа и заканчивая 19 строкой (строки 20 и 21 ввести позже, см. п.п. 6 задания 1). Первые несколько строк оставить пустыми для ввода дополнительной информации при решении задач и заголовка связанного списка данных.

3. Рассчитать столбец «Общая стоимость» по формуле: =Стоимость единицы*Потребность. Формула вводится в ячейку G7 и копируется во все ячейка столбца G с использованием Автозаполнения.

4. Применить к ячейкам столбцов E и G формат данных Денежный.

5. Выделить 6-ю строку (Имена столбцов) и применить команду Формат ► Ячейки. На вкладке Выравнивание соответствующего окна диалога установить флаг Переносить по словам и выбрать из списка По горизонтали выравнивание По центру.

Рис. 7.1. Связанный список данных

6. Используя команду Данные ► Форма и кнопки Последняя запись и Создать, добавить с помощью Формы в конце таблицы (строки 20-21) записи ещё о двух комплектующих, например:

61 Принтер Лазерный 1 5 000 10.05.11

62 Принтер Струйный 1 4 500 11.02.11

Закончив ввод данных, щёлкнуть в Форме на кнопке Закрыть.

7. С помощью Автозаполнения рассчитать значение Общей стоимости в ячейках G20:G21.

8. Применить к соответствующим ячейкам столбцов E и G формат данных Денежный.

9. Отформатировать таблицу с помощью команды Формат ► Автоформат. Формат подобрать под данные связанного списка или отформатировать по своему усмотрению

10. Сохранить файл под именем DBaseName.ods в своей папке на диске Stud:/Ngroup или D:/Temp/Ngroup.

Задание 2. Использование сортировки

1. Выделить диапазон A7:G21 и, используя команду Данные ► Сортировка, выполнить одноуровневую сортировку данных в порядке убывания Стоимости за единицу (столбец Е).

2. Результаты сортировки с помощью буфера обмена скопировать в свободные ячейки Листа Отчет.

3. Выделить диапазон A7:G21 и, используя команду Данные ► Сортировка, выполнить 2-х уровневую сортировку данных: по Наименованию (столбец B) в порядке возрастания, затем по Общей стоимости (столбец G) в порядке убывания.

4. Результаты сортировки скопировать в свободные ячейки Листа Отчет.

5. Выполнить 3-х уровневую сортировку данных (см. п.3 задания 2): по Наименованию (столбец B) в алфавитном порядке, затем по Дате заказа (столбец F) в порядке возрастания и, наконец, по Общей стоимости (столбец G) в порядке возрастания.

6. Результаты сортировки скопировать в свободные ячейки Листа Отчет.

7. Сделать выводы о допустимом количестве уровней сортировки в электронных таблицах.

Задание 3. Применение Формы

1. Установить курсор внутри базы данных. С помощью команды Данные ► Форма, просмотреть в окне Форма все записи в прямом и обратном порядке. При этом использовать кнопки Следующая запись и Последняя запись.

2. С помощью Формы (см. п. 1 задания 3) отыскать в списках запись Монитор MVA и изменить его потребность (например, на значение 2). Закрыть Форму и посмотреть результаты изменения в базе данных. Восстановить в таблице формулу расчета Общей стоимости монитора MVA.

3. Сделать выводы о назначении Формы при работе с базами данных.

Задание 4. Применение простого Фильтра

1. Выделить диапазон A6:G20 и, используя команду Данные ► Фильтр ► Автофильтр, отобразить на экране список комплектующих столов. Для этого в списке фильтра Наименование выбрать необходимое условие (Стол).

2. Скопировать полученные результаты в свободные ячейки Листа Отчет.

3. Восстановить исходное состояние ССД (команда Всё в списках фильтра используемых ранее полей).

4. Отобразить список оборудования по Стоимости единицы 5000 руб. Полученные результаты скопировать в свободные ячейки Листа Отчет. Восстановить исходное состояние ССД.

5. Вывести запись оборудования, заявка которого еще не произведена (строка пусто в списке Дата заказа). Результаты скопировать на Листа Отчет и восстановить все записи ССД.

6. Отобразить список мониторов по Общей стоимости от 5000 руб. и выше. Для этого в списке фильтра Общая стоимость выбрать пункт Стандартный фильтр. В одноименном окне (рис. 7.2) с помощью списков соответствующих полей выбрать необходимые условия и нажать на кнопку ОК.

Рис. 7.2. Окно стандартного фильтра

7. Полученные результаты скопировать на Листа Отчет и восстановить все записи ССД.

8. Отобразить записи комплектующих рабочих мест, имеющих самую малую и самую большую Стоимость за единицу оборудования (например, 300 ИЛИ 20000). Для этого в списке фильтра Стоимость единицы воспользоваться командой Стандартный фильтр и в открывшемся окне выбрать необходимые условия отбора. Полученные данные скопировать на Листа Отчет и восстановить все записи ССД.

9. Аналогично предыдущему заданию отобразить список оборудования по Общей с тоимости от 1000 до 20000 руб. (например, >1000 И <20000). Результат скопировать в свободные ячейки Листа Отчет. Восстановить исходное состояние БД.

10. Отобразить список комплектующего оборудования, заказанного в летние месяцы с Потребностью 1 (единица). Результат скопировать на Лист Отчет и восстановить все записи ССД.

11. Отобразить список оборудования ввода и вывода. Результат скопировать на Лист Отчет. Восстановить исходное состояние ССД и закрыть фильтр.

12. Повторным использованием команды Данные ► Фильтр ► Автофильтр, отказаться от применения фильтра к списку связанных данных.

13. Сохранить изменения в файле.

Задание 5. Применение расширенного Фильтра

1. С помощью расширенного фильтра определить и вывести в свободные ячейки Листа Отчет записи, удовлетворяющие заданиям, приведенным в таблице 7.1 в соответствии с вариантом.

2. Для работы с расширенным фильтром сначала необходимо сформировать критерии вывода записей в соответствии с условием задачи, а затем установить курсор внутри списка и выбрать команду Данные ► Фильтр ► Расширенный фильтр. Появится одноименное окно:

Рис. 7.3. Окно расширенного фильтра

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

1) копии названий столбцов, для которых выполняется подведение итогов в списке и

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

Таблица 7.1

N	Задания: отобразить записи
	мониторов стоимостью более 5000 и системных блоков стоимостью менее 18000 руб.
системных блоков и клавиатур с датой заказа за 10.05.11
оборудования после 1.05.11 с потребностью 1 шт.
	мониторов стоимостью более 10000 руб. и клавиатур стоимостью более 500 руб.
принтеров и мониторов с датой заказа после 10.02.11
оборудования до 1.05.11 с потребностью 1 шт.
	мониторов стоимостью менее 12000 руб. и принтеров стоимостью 5000 руб.
системных блоков и мышек с датой заказа после 3.05.11
оборудования до 1.09.11 с потребностью 2 шт.
	мониторов стоимостью менее 10000 руб. и клавиатур стоимостью менее 500 руб.
клавиатур и мышек с датой заказа после 1.07.11
оборудования после 10.09.11 с потребностью 2 шт.
	мониторов стоимостью более 6000 руб. и мышек стоимостью более 400 руб.
мониторов и принтеров с датой заказа до 1.06.11
оборудования до 10.06.11 с потребностью 3 шт.
	беспроводных клавиатур и мышек
мониторов и принтеров с датой заказа после 1.05.11
оборудования после 1.05.11 с потребностью 3 шт.
	мониторов стоимостью более 9000 руб. и мышек стоимостью менее 500 руб.
мониторов и системных блоков с датой заказа после 1.05.11
оборудования до 10.06.11 с потребностью 1-2 шт.
	мониторов стоимостью менее 9000 руб. и принтеров стоимостью 4500 руб.
мониторов и клавиатур с датой заказа до 15.05.11
оборудования после 10.06.11 с потребностью 1 или 3 шт.
	проводных клавиатур и мышек
мониторов и мышек с датой заказа за 27.09.11
оборудования до 10.06.11 с потребностью 2-3 шт.
	системных блоков стоимостью более 18000 руб. и мониторов стоимостью более 4000 руб.
системных блоков и принтеров с датой заказа за 10.05.11
оборудования за 11.02.11 с максимальной и минимальной потребностью.
	системных блоков стоимостью менее 18000 руб. и мониторов стоимостью менее 10000 руб.
системных блоков и столов с датой заказа до 1.04.11
оборудования до 1.06.11 со стоимостью более 5000 руб.
	системных блоков Intel и компьютерных столов
мониторов и принтеров с датой заказа после 10.03.11
оборудования после 20.06.11 со стоимостью менее 3000 руб.

Записи мужчин 2 курса старше 17 лет. Все условия отображены в одной строке. Таким образом, задается их взаимодействие по схеме «И» (одновременное их выполнение).

Записи либо мужчин, либо второкурсников, либо в возрасте 18 и старше лет. Все условия отображены в разных строках. Таким образом, задается их взаимодействие по схеме «ИЛИ» (отдельные строки диапазона критериев).

Рис. 7.4. Примеры решения задач со сложными критериями

Записи мужчин старше 18 лет или женщин старше 17 лет.

Условия по возрасту для групп мужчины и женщины отображены в разных строках. Таким образом, задается их взаимодействие по смешанной схеме «И - ИЛИ».

Записи с фамилиями Иванов или Тихонов или все фамилии, начинающиеся на букву А.

Три и более условия отбора накладываются на содержимое одного столбца. Таким образом, задается их взаимодействие по схеме «ИЛИ...ИЛИ». Символ * в шаблоне заменяет любое количество символов.

Задание 6. Использование Функций

1. Используя статистические функции, вычислить и вывести:

- количество комплектующего оборудования в ячейке D2 (функция COUNT);

- максимальную общую стоимость в ячейке G2 (=MAX());

- минимальную общую стоимость в ячейке H2 (=MIN());

- среднее значение стоимости единицы оборудования в ячейке Е2 (=AVERAGE());

- суммарную общую стоимость в ячейке G22 (=SUM()).

2. Скопировать в ячейки D1, E1, G1, H1 имена полей из таблицы связанных списков, соответствующих расчетным данным (см. рис. 7.1).

3. Используя соответствующие функции категории «База данных» (DSUM (БСУММ), DAVERAGE (ДСРЗНАЧ), DMAX (ДМАКС), DMIN (ДМИН), DCOUNT (БСЧЁТ), DGET (БИЗВЛЕЧЬ)), определить и вывести в свободные ячейки Листа База Данных параметры, указанные в таблице 7.2 по варианту.

4. DФункция возвращает данные из списка, которые удовлетворяют условиям, определённым диапазоном критериев. Поэтому перед вызовом DФункции сначала необходимо сформировать диапазон критериев в соответствии с условием задачи (как при работе с расширенным фильтром), а затем вызвать мастер Функций. Выбрать из списки нужную функцию и заполнить поля, соответствующие аргументам DФункции (база_данных; поле; критерий), где

- аргумент «база данных» представляет собой диапазон ячеек, содержащий исходные данные. В этот диапазон включаются: строка с названиями полей и ячейки записей, например, A6:G21;

- аргумент «поле» должен содержать адрес столбца, по которому следует вычислить итог, например G6, Е6);

- аргумент «критерий» – это ссылка на интервал ячеек, задающих условия для данной функции. Диапазон критериев содержит ячейки, включающие копию названия столбца, для которого выполняется подведение итогов в списке и само условие, по которому определяется функция. Ссылка на критерий может быть введена как интервал ячеек, например B1:B2 (в случае простого критерия) или B1:C2 (в случае сложного критерия).

Таблица 8.2

N	Искомый параметр
	суммарную общую стоимость мониторов
наименование оборудования с минимальной стоимостью
код системного блока, заказанного 10.05.11
	максимальную общую стоимость мышек
количество мониторов, заказанных в первом полугодии
тип системного блока с потребностью 3 шт.
	минимальную общую стоимость принтеров
количество оборудования с потребностью 3 шт.
дату заказа с максимальной суммарной стоимостью
	среднеарифметическую стоимость мониторов
количество оборудования стоимостью менее 1000 руб.
дату заказа с минимальной суммарной стоимостью
	суммарную общую стоимость системных блоков
количество оборудования, заказанного 11.02.11
тип монитора с потребностью 2 шт.
	максимальную стоимость принтера
тип стола со стоимостью 10000 руб.
дату заказа беспроводной мышки
	минимальную стоимость клавиатуры
количество оборудования, заказанного после 30.06.11
стоимость системного блока Intel
	среднеарифметическую стоимость системного блока
количество мониторов, заказанных во втором полугодии
дату заказа проводной мышки
	суммарную общую стоимость клавиатур
код оборудования с датой заказа 1.02.11
наименование оборудования стоимостью 500 руб.
	максимальную стоимость монитора
наименование оборудования с минимальной общей стоимостью
дату заказа беспроводной клавиатуры
	минимальную стоимость мышки
количество оборудования стоимостью более 5000 руб
тип монитора заказанного 01.02.11.
	среднеарифметическую стоимость принтеров
количество оборудования, заказанного 27.09.11
стоимость монитора MVA

5. Просмотреть все расчетные формулы. Для одновременного отображения формул на Листе необходимо выбрать команду Сервис ►Параметры. В открывшемся одноименном окне найти пункт LibreOffice Calc ► Вид, выделить его и в поле Показать установить флаг Формулы.

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

Пример контрольного теста «Связанные списки данных»

База данных - это средство:

хранения данных

поиска данных

хранения, поиска и упорядочения данных

сортировки данных

обработки информации

Укажите условие эффективной работы с БД:

упорядоченные данные по возрастанию

упорядоченные данные по убыванию

неструктурированные данные

представление данных в виде двумерной таблицы

Электронная таблица не является связанным списком, если:

имена полей указаны в верхней строке списка

каждая запись введена в отдельную строку

между записями отсутствуют пустые строки

данные одного столбца имеют разный тип

данные одной таблицы располагаются на одном листе

Максимальное число уровней сортировки в связанных списках данных =…

В функции Формы электронной таблицы не входит:

добавление данных к списку

удаление данных из списка

вывод несколько записей одновременно

просмотр следующей записи

просмотр последней записи

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

форма

запрос

сортировка

фильтр

DФункция

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

формы

БДФункции

фильтра

сортировки

все ответы правильные

Фильтр связанного списка данных позволяет:

изменять произвольно порядок записей в списке

скрывать записи, которые не требуется отображать

сортировать записи

форматировать записи в списке

строить диаграммы

К отфильтрованным записям нельзя применять:

форматирование

создание диаграмм

вывод на печать

сортировку по всем строкам

редактирование

Для описания поля не используют:

имя поля

размер данных, размещаемых в поле

тип данных, помещаемых в поле

точность вычислений

название таблицы

Стандартный фильтр используется, если:

два и более условия отбора одновременно применяются к ячейкам двух и более столбцов

к ячейкам одного столбца необходимо применить три и более условия отбора

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

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

все ответы правильны

Аргументом БДФункции не является:

<База_данных>

<Поле>

<Критерий>

<Запись>

При работе со связанными списками не рекомендуется:

располагать критерии над связанным списком

перекрывать диапазон критериев со списком

объединять ячейки в списке

располагать условие под названием столбца

сложный критерий формировать в смежных ячейках

Аргументом «Критерий» для БДФункций является:

одна ячейка с условием

диапазон ячеек с несколькими условиями

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

строка списка, содержащая названия всех столбцов

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

Укажите правильный синтаксис БДФункции():

DФункция(<БД>;<Критерий>;<Поле>)

DФункция(<Поле>;<Критерий>;<БД>)

DФункция(<Критерий>;<БД>;<Поле>)

DФункция(<БД>;<Поле>;<Критерий>)

DФункция(<Поле>;<БД>;<Критерий>)

DФункция(<Критерий>;<Поле>;<БД>)

Установить соответствие:

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

Сортировке записей по полю «Размер» по убыванию отвечает:

1, 2, 3, 4

4, 1, 3, 2

3, 1, 4, 2

2, 3, 1, 4

2, 4, 1, 3

Укажите результат сортировки по убыванию сначала по полю «Курс», затем по полю «Возраст»:

N	Фамилия	Возраст	Телефон	Курс	Пол
	Иванов				муж
	Сергеев				муж
	Тихонов				муж
	Абт				жен
	Аболенский				муж
	Петрова				жен
	Абрикосов				муж

5, 3,4, 2, 6, 1, 7

3, 5, 4, 2, 7, 1, 6

3, 5, 2, 4, 1, 6, 7

3, 2, 4, 5, 1, 6, 7

5, 3,4, 2, 1, 7, 6

Критерию расширенного фильтра отвечает условие:

Оценка	Пол	Курс
>=4
	муж

(Оценка>=4) И (Пол "муж") И (Курс=2)

(Оценка>=4) ИЛИ (Пол "муж") ИЛИ (Курс=2)

(Оценка>=4) ИЛИ (Пол "муж") И (Курс=2)

(Оценка>=4) И (Пол "муж") ИЛИ (Курс=2)

(Пол "муж") ИЛИ (Курс=2) ИЛИ (Оценка>=4)

Полученному связанному списку отвечает условие:

Фамилия	Год рождения	Курс	Пол	Оценка
Тихонов			муж
Арбузов			муж
Сергеев			муж
Абт			жен
Иванов			муж
Савинова			жен

(Курс=1) И (Год_рождения=1992) И (Оценка>=4)

(Оценка>=4) И (Год_рождения=1992) И (Курс=1)

(Курс=1) ИЛИ (Год_рождения=1992) И (Оценка>=4)

(Оценка>=4) ИЛИ (Год_рождения=1992) И (Курс=1)

(Год_рождения=1992) И (Курс=1) И (Оценка>=4)

Укажите DФункцию, возвращающую суммарный оклад инженеров в возрасте от 25 до 30 лет:

	A	B	C	D	E
	Возраст	Возраст	Категория	Возраст	Возраст
	25-30	>24	инженер	>=25	<=30
	Категория	<31		<=30
	инженер

=DSUM(<База_данных>;<Оклад>;A1:A4)

=DSUM(<База_данных>;<Оклад>;B1:C3)

=DSUM(<База_данных>;<Возраст>;C1:D3)

=DSUM(<База_данных>;<Оклад>;C1:E2)

=DSUM(<База_данных>;<Оклад>;D1:E2)

Дата добавления: 2014-12-27