Выполнение работы. Задача №1 «Построение и заполнение таблицы»

Задача №1 «Построение и заполнение таблицы»

Сформировать БД на первом листе MS Excel (начинать построение таблицы с ячейки А1). База данных содержит следующую информацию: фамилия клиента, тип вклада, размер вклада (руб.), вклад (у.е.), банк, примечание.

	Столбец Примечание заполняется примерно следующими данными: · Сидоров – Временно выбыл · Каламеев – Сменил адрес Иванов – Переведен в другое отделение и т.д.
Рисунок 2.4 – Построение таблицы

Построение структуры таблицы:

1. Создайте шапку таблицы по образцу:

· Выделить 1-ю строку.

· Задать расположение текста в ячейках: Формат-Ячейки…-Выравнивание: Выравнивание по горизонтали и вертикали – по центру, Отображение – Переносить по словам (флажок).

2. Введите текст наименования столбцов полужирным шрифтом, задайте цвет заливки ячеек.

Для изменения шрифта: пункт главного меню Формат- Ячейки… - Шрифт. Либо с помощью кнопок Полужирный, Курсив, Подчеркивание на панели инструментов .

Изменить цвет шрифта и заливки: пункт контекстного меню Формат-Ячейки…-Шрифт (Вид), главного меню Формат-Ячейки…-Шрифт (Вид). Либо с помощью кнопок Цвет заливки, Цвет шрифта на панели инструментов .

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

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

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

o Щелкнуть левой кнопкой мыши по любой другой ячейке.

o Нажать клавишу Enter.

o Нажать одну из клавиш перемещения курсора.

Замечание 1. Текст в ячейках по умолчанию выравнивается по левому краю, а числа – по правому краю.

Для редактирования содержимого ячейки необходимо: нажать клавишу F2, или выполнить двойной щелчок по ячейке (для редактирования в самой ячейке), или щелкнуть мышью по ячейке (редактирование производится в строке формул); использовать команду Правка-Очистить, или клавиши DELETE и BACKSPAСE; нажать Enter.

4. Настройте ширину столбцов: с помощью мыши или Формат-Столбец-Ширина (Автоподбор ширины).

5. Добавьте к полученной таблице заголовок:

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

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

Установить курсор в ячейку А1 и выполнить команду Вставка- Строки или команду контекстного меню Добавить ячейки…, выполнение которой влечет за собой появление окна Добавление ячеек. В разделе Добавить установить флажок строку, нажать ОК.

· Расположить текст заголовка таблицы по центру относительно ее ширины:

o Выделить диапазон ячеек A1:F1.

o Выполнить объединение выделенных ячеек и при этом целесообразно задать расположение текста в ячейках: Формат-Ячейки…-Выравнивание-Объединение ячеек и Выравнивание по горизонтали и вертикали– по центру. Либо воспользоваться соответствующими кнопками на панели инструментов , .

Замечание 2. Для выравнивания заголовка относительно столбцов: выделить ячейку, содержащую текст, или интервал ячеек, в котором будет центрироваться текст; затем использовать . Для отмены объединения воспользуйтесь командой Правка-Очистить-Форматы.

Замечание 3. Количество столбцов в таблице определяется по строке таблицы с максимальным количеством ячеек, т.к. Excel «умеет» только объединять ячейки, но не разбивать.

o Ввести текст: Работу выполнил студент ФИО группы №****** (ФИО исполнителя).

o Выделить диапазон ячеек A2:С2. Выполнить объединение выделенных ячеек. Ввести текст: «Прайс-лист на».

o Аналогично выполнить действия с диапазоном ячеек D2:F2.

6. Выполните обрамление таблицы:

· выделить всю таблицу (диапазон A3:F4);

· в контекстном меню (правой клавишей мыши) выбрать пункт Формат ячеек…, а в открывшемся диалоговом окне вкладку Граница или аналогичная команда главного меню;

· либо воспользоваться инструментом Границы на панели инструментов.

7. Задайте финансовый формат (Обозначение: нет; Число десятичных знаков: 0) для диапазона ячеек C4:D4 (смотрите рис. 4 – Построение таблицы).

8. Уточните курс белорусского рубля к американскому доллару на сегодняшний день с использованием Интернет-ресурсов: http://www.tut.by/ или www.nbrb.by (например, 27.04.2009 – 1$=2831 руб.=Х).

9. В ячейку D4 введите формулу для вычисления вклада в условных единицах: вклад (у.е.)= размер вклада (руб.)/X.

Рисунок 2.5 – Заголовок, шапка таблицы, пустая запись

Заполнение таблицы данными:

1. Заполните построенную таблицу данными.

· Выделить диапазон ячеек А3:F4.

· Вызвать форму для заполнения данными таблицы: Данные-Форма.

Кнопки: · Добавить – добавление новой записи. · Удалить – удаление текущей записи. · Назад – вернуться к предыдущей записи. · Далее – переходи на следующую запись. · Критерии – задание условия отбора. · Закрыть – завершение набора записей.

Рисунок 2.6 – Форма

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

· Введите данные через форму: фамилию клиента, тип вклада, размер вклада (руб.), банк, примечание.

· Подведите итоги после завершения ввода записей:

ИТОГО		*	*
Найти		СУММ(C4:C13)	СУММ(D4:D13)

2. Переименовать Лист1 в Построение таблицы.

Переименовать можно несколькими способами:

• с помощью пункта меню Формат- Лист - Переименовать;
• щелкнуть правой клавишей мыши на ярлыке листа, в появившемся контекстном меню выбрать пункт Переименовать;
• двойной щелчок левой клавишей мыши на ярлыке листа.

Задача №2 «Отображение формул, условное форматирование»

Отображение формул

1. Скопируйте таблицу на любой другой лист данной рабочей книги.

· Выделить диапазон ячеек А1:F14 или выделить содержимое листа:

	Щелкнуть по зоне в левом верхнем углу рабочей области таблицы (для выделения)
Рисунок 2.7 – Выделение рабочего листа

· Скопировать (переместить):

o с помощью пункта главного меню Правка-Копировать (Правка-Вырезать).

o с помощью пункта контекстного меню Копировать (Вырезать).

o воспользовавшись кнопкой Копировать (Вырезать ) на панели инструментов.

• Перейти на Лист2.
• Установить курсор в ячейку, в которой будет находиться левый верхний угол копируемой (перемещаемой) области (для вставки содержимого листа в ячейку А1).
• Вставить:

o пункт главного меню Правка- Вставить.

o пункт контекстного меню Вставить.

o воспользоваться кнопкой Вставить на панели инструментов.

2. Переименуйте лист2 в Формула.

3. Отобразите формулы в таблицах: Сервис-Параметры-…-Вид-Формулы (флажок).

4. На листе Формула настроить ширину столбцов (высоту строк).

Условное форматирование

1. Перейти на лист3.

2. Переименуйте лист3 в Условие.

3. Скопируйте на лист Условие содержимое листа Построение таблицы.

4. В столбце «Размер вклада (руб.)» залить голубым цветом ячейки, значение которых превосходит определенное значение, например 300 000.

o Выделить диапазон ячеек С4:С13.

Рисунок 2.8 – Условное форматирование

o Формат – Условное форматирование.

o В появившемся окне Условное форматирование задать условие (например, 300000) и нажать Формат.

o В появившемся окне Формат выбрать вид форматирования (заливку, шрифт, границы) – заливка: голубой цвет. ОК в окне Формат. ОК в окне Условное форматирование.

5. Для ячеек столбца «Вклад (у.е.)» установить дополнительное правило форматирования: если значение равно, например, 122, то цвет заливки будет желтый.

Замечание. В окне Условное форматирование нажать кнопку [А также>>]. После чего задать второе условие форматирования.

Если при выполнении заданий 4 и 5 значения 122, 300000 не совпадут с вашими значениями, то заменить их на имеющиеся данные из столбцов вашей таблицы.

Задача №3 «База данных»

1. Добавьте рабочий лист: Вставка-Лист.

2. Переименуйте добавленный лист в БД.

3. Скопируйте на лист БД содержимое листа Построение таблицы.

4. Отсортируйте данные на листе БД фамилии клиентов по алфавиту.

Рисунок 2.9 – Сортировка

o Выделить всю таблицу: столбцы и строки (например, А3:F13). oВыполнить команду [Данные-Сортировка]. o Появится окно Сортировка диапазона. В поле Сортировать по… выбрать поле Фамилия клиента. Установить флажок по возрастанию. o Нажать [ОК].

5. Подвести итоги в БД, используя функцию Итоги…: определить сумму вкладов каждого клиента и сколько в среднем денег хранится в каждом банке.

Посчитать сумму вкладов каждого клиента. Сумму вкладов клиентов можно определить при помощи функции СУММ(диапазон).

o Необходимо, чтобы данные БД были отсортированы по фамилии клиентов (что уже сделано).

o Выделить диапазон А3:F13.

o Данные – Итоги.

Рисунок 2.10 - Итоги

o В окне Промежуточные итоги: При каждом изменении в: – Фамилия клиента, Операция – Сумма, Добавить итоги по:– Размер вклада (руб.), флажки – Заменить текущие итоги, Итоги под данными. o Вернуть первоначальный вид списка: выделить список (столбцы и строки), Данные-Итоги…-Убрать все (кнопка).

Рабочий лист примет вид:

Посчитать, сколько в среднем денег хранится в каждом банке.

o Выполнить команду Данные-Итоги.

o В окне Промежуточные итоги: При каждом изменении в: – Банк, Операция – Среднее, Добавить итоги по: – Размер вклада (руб.), флажки – Заменить текущие итоги, Итоги под данными.

Рабочий лист примет вид:

ФИЛЬТР

1. Используя автофильтр, отобразить всех клиентов, хранящих деньги на депозите.

Для включения автофильтра необходимо:

§ Щелкнуть в любом месте базы данных, в нашем случае диапазон A3:F13.

§ Выполнить команду Данные- Фильтр - Автофильтр. Щелкнуть по кнопке списка справа от нужного поля (например, поле Тип вклада). В качестве условия отбора можно выбрать либо любое значение из списка, либо пункт Условие. Выберем в качестве условия значение Депозит. В результате в БД останется информация, касающаяся только клиентов, хранящих деньги на депозите.

Рисунок 2.11 – Автофильтр	Рисунок 2.12 – Пользовательский фильтр

§ Результат будет тем же, если из списка предложенных фильтров выбрать Условие…, и в открывшемся диалоговом окне ввести в качестве условия " равно Депозит ".

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

2. Используя расширенный фильтр, сформировать базу данных, куда занести информацию обо всех клиентах, хранящих деньги в банке «Золотой талер».

Для выделения из БД более сложных условий нужно воспользоваться командой Данные-Фильтр-Расширенный фильтр. Для выполнения данного задания рассмотрим выделение из БД строк клиентов банка «Золотой талер». Для этого необходимо определить область для хранения условий отбора. Каждое условие записывается в две ячейки: верхнее – имя столбца, нижнее – знак отношения (>, <, =, >=, <=,<>) и значение. В нашем случае в ячейку К1 введем фамилию клиента, в ячейку К2 – ничего (т.к. фамилия клиента неизвестна), в ячейку L1 – банк, в ячейку L2 –Золотой талер (см. рисунок 2.13).

Далее выполнить команду Данные-Фильтр-Расширенный фильтр (рисунок 2.14).

В данном случае два условия соединены логическим условием "И". Для объединения с помощью "ИЛИ" необходимо между именем столбца и условием пропустить столбец (рисунок 2.15). При копировании отфильтрованных данных в другое место необходимо, чтобы копируемый диапазон начинался со строки, в которой указываются имена столбцов БД.

Рисунок 2.13 – Область для хранения условий отбора (действие "И")		Рисунок 2.15 – Область для хранения условий отбора (действие "ИЛИ")
Рисунок 2.14 – Расширенный фильтр

После выполнения расширенного фильтра рабочий лист примет вид:

РАЗДЕЛ 3. CОЗДАНИЕ БД КАК КОМПЛЕКСА

ВЗАИМОСВЯЗАННЫХ ТАБЛИЦ В СУБД ACCESS.

ЭКСПОРТ ДАННЫХ. СВЯЗЫВАНИЕ ТАБЛИЦ

ЦЕЛЬ: Получить практические навыки создания БД как комплекса взаимосвязанных таблиц в СУБД Access.

Краткие теоретические сведения:

Основные понятия

Одним из важнейших понятий в теории баз данных является понятие информации.

Под информацией понимаются любые сведения о каком-либо событии, процессе, объекте.

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

База данных (БД) – это совокупность взаимосвязанных данных при такой минимальной избыточности, которая допускает их использование оптимальным образом для одного или нескольких приложений в определенной предметной области. То есть, БД – это только «информационный склад».

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

На уровне физической модели электронная БД представляет собой файл или их набор в формате TXT, CSV, XLS, DBF, XML либо в специализированном формате конкретной СУБД.

В 1970 году доктором Коддом из компании IBM было предложено для обработки данных использовать аппарат теории множеств. Он показал, что любое представление данных сводится к совокупности двумерных таблиц особого вида, известного в математике как отношение. Слово «отношение» происходит от английского слово «relation» (отсюда произошло прилагательное реляционный). База данных с табличной формой организации называется реляционной. Для работы с реляционными БД применяют Реляционные СУБД.

Реляционная БД – это конечный (ограниченный) набор двумерных таблиц. Таблицы используются для определения объектов, а также для представления связей между объектами.

Каждая реляционная таблица представляет собой двумерный массив и обладает следующими свойствами:

· каждый элемент таблицы – один элемент данных;

· все столбцы в таблице однородные, то есть все элементы в столбце имеют одинаковый тип (числовой, символьный и т. д.);

· каждый столбец имеет уникальное имя;

· одинаковые строки в таблице отсутствуют;

· порядок следования строк и столбцов может быть произвольным.

Базовыми понятиями реляционных СУБД являются: 1) атрибут – заголовок столбца таблицы, поле; 2) отношения – таблицы; 3) кортеж – строка таблицы.

При проектировании реальных БД информацию сложно разместить в одной таблице. Поэтому логические структуры описываются в нескольких таблицах. Таблицы при этом связаны определенным типом информации (обозначающим одну тему). В реляционных СУБД для указания связей между таблицами производят операцию их связывания. Логическое связывание таблиц производится с помощью ключа связи. Ключ связи состоит из одного или нескольких полей.

Суть связывания состоит в установлении соответствия полей связи главной и подчиненной таблиц.

Связь между таблицами устанавливает отношения между совпадающими значениями в ключевых полях, обычно между полями, имеющими одинаковые имена в обеих таблицах. В большинстве случаев с ключевым полем одной таблицы, являющимся уникальным идентификатором каждой записи, связывается другой таблицы. Например, для связывания сотрудников с заказами, за которые они отвечают, следует создать отношение между полями «КодСотрудника».

	Поле «Код сотрудника» отображается в двух таблицах – как первичный ключ... ... и как внешний ключ.
Рисунок 3.1 - Связь между таблицами

Поле связи главной таблицы может быть обычным и ключевым. В качестве полей связи подчиненной таблицы чаще всего используют ключевые поля.

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

Совокупность связей между таблицами в БД называется схемой данных.

Дата добавления: 2014-11-16