Разработка реляционных информационных моделей и их анализы в приложении Microsoft Excel

 

 

 

 

 

 

«Разработка реляционных информационных моделей и их анализы в приложении Microsoft Excel»

 

Расчетно-графическая работа

 

 

 

                                                                                      «К защите допускаю»

                                                                            Руководитель

                                                                                         

                                                                                                                  (ученая степень, звание Ф.И.О.)

                                                                                                             _________________________________

                                                                                                                                (подпись)

                                                                                            “ ___” _____________20__г.

 

 

Оценка при защите

__________________

_____________________

(подпись)

“___” ________20__г.

 

 

 

 

 

.

 

 

ОГЛАВЛЕНИЕ

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ВВЕДЕНИЕ

 

Целью данной работы является закрепление практических навыков работы при изучении дисциплины «Информатика», умения работать в MS Excel, MS Word.

Основной задачей расчетно-графической работы является создание и оформление исходных таблиц; связывание таблиц; расчеты в таблицах с применением «Мастера функций»; обработка данных таблиц посредством  сортировки и выборки (фильтрации); построение диаграмм; оформление отчета в виде комплексного текстового документа.

Выбор варианта задания:

• № зачетной книжки – 280208;
• № варианта  выбора исходных таблиц – 8;
• (Таблица 1- Расчет стоимости перевозок груза; Таблица 2 - Расчет общей стоимости перевозок за год);
• № варианта критерия выборки – 8
• Итоговая логическая функция- если суммарное количество рейсов за год превышает 100 рейсов , то вычислить среднюю стоимость перевозок  за год, иначе  найти суммарную стоимость перевозок за год.

1  КОНСТРУИРОВАНИЕ ТАБЛИЦ

1.  Создание и  оформление исходных таблиц

Загрузила MS Excel. Сформировала на первом листе («Лист 1») рабочей книги таблицу  1 ( рисунок  1)  согласно варианту  задания.

Для оформления заголовка таблицы установила курсор в ячейку A1 и ввела название таблицы " Расчет стоимости перевозок груза ". Для размещения названия таблицы по ширине нашей таблицы объединила ячейки A1:D1, выбрала в строке меню команду Формат/ Ячейки/ Выравнивание/ Объединение ячеек.

Для длинных названий, используемых в заголовках таблицы 1, применила форматирование ячеек. Выделила диапазон ячеек A2:D2 и выбрала в меню команду Формат/ Ячейки/Выравнивание/Переносить по словам. 

 

 

 

Расчет стоимости перевозок груза
Ф.И.О шафера	Перевезено груза за 1 рейс, т	Стоимость перевозки 1 тонны груза, руб.	Стоимость перевозки за 1 рейс, руб.
Иванов П.Р.	4	200	50
Зимин О.Н.	5	200	40
Коротков О.П.	1	200	200
Петухов А.У.	2	200	100
Шандыин П.Г.	3	200	67
Соров Б.Р.	6	200	33
Сидоров А.В.	4	200	50
Рыбин Р.Е.	2	200	100
Козлов Б.Л.	5	200	40
Игошов Л.Д.	2	200	100
Итого:	34	2000	780

     

Рисунок 1 Вид таблицы «Расчет стоимости перевозок груза» (таблица 1)

 

Переименовала текущий лист «Лист1» на «Таблица1». Для этого, указав на название листа в рабочей книге MS Excel, вызвала контекстно-зависимое меню (нажатием правой кнопки мыши) и выбрала команду Переименовать.

Рамки таблицы создала следующим образом: сначала  выделила данные в таблице - это интервала ячеек A2:D11 Далее выбрала в строке меню команду Формат/ Ячейки/ Граница/…, выбрала тип линии, и к какой части выделенных ячеек эту линию применить: внешней границе и всем внутренним. Можно было  использовать на панели инструментов пиктограмму  .

Выделила цветом вычисляемые столбцы в таблице, для этого определила диапазон ячеек D2:D12, выбрала команду Формат/Ячейки/Вид и указала в появившемся окне на нужный цвет.

Затем перешила на второй лист «Лист 2» рабочей книги MS Excel, так же переименовала его на «Таблица 2» и создала аналогично вторую, заданную в нашем варианте таблицу 2 (рисунок 2).

Кроме оформления таблиц, производила необходимые расчеты: для столбца D («Стоимость перевозки за 1 рейс, руб.») первой таблицы и для столбца D («Стоимость перевозки за год, руб.») второй таблицы. Расчеты описаны в разделе 1.2

Расчет общей стоимости перевозок за год
Ф.И.О шафера	Количество рейсов за год	Стоимость перевозок за 1 рейс, руб.	Стоимость перевозок за год, руб.
Иванов П.Р.	60	50	3000
Зимин О.Н.	90	40	3600
Коротков О.П.	80	200	16000
Петухов А.У.	70	100	7000
Шандыин П.Г.	88	67	5867
Соров Б.Р.	69	33	2300
Сидоров А.В.	79	50	3950
Рыбин Р.Е.	99	100	9900
Козлов Б.Л.	56	40	2240
Игошов Л.Д.	76	100	7600
Итого:	767	780	61457
Итоговая логическая функция			6452

 

Рисунок 2. Вид таблице «Расчет общей стоимости перевозок за год» (таблица 2)

 

Во второй таблице интервала ячеек  А2:В12 скопировала из первой таблицы с помощью команды Специальная вставка, для того, чтобы установить связь между таблицами (более подробно пояснено в разделе 1.3).

1.2  Расчеты в таблицах

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

Сначала рассчитала столбец «Стоимость перевозок за год, руб.». Для этого ввела в ячейку D3 следующую формулу  «=С3*В3», а остальные ячейки заполнила используя прием автозаполнения:  выделила ячейку, в которой производила расчет (D3), подвела курсор к нижнему правому уголку ячейки и захватив ее, переместила курсор на необходимое количество ячеек (D3:D12).

Для вычисления итоговой суммы по числовым значениям в смежном интервале ячеек использовала инструмент автосуммирование (значок на панели инструментов).  Установила курсор в ячейку В13 таблицы 1, нажала кнопку автосуммирования (в самой ячейке и в строке формул появляется функция «=СУММ (В2:В10)»), нажала клавишу «Enter».  Остальные рядом стоящие ячейки С13 и D13, заполнила автозаполнением.

В таблице 2 нашла значение ячеек столбца D («Стоимость перевозок за год, руб.») по формуле: «=C3*B3», а остальные ячейки заполнила  автозаполнением.

Логическую функцию согласно заданию находила в таблице 2 следующим образом: если выражение «D13<100» истинно (т.е. если  количество рейсов за год превышает 100 рейсов.), то в ячейке D14 появится запись «вычислить среднюю стоимость перевозок за год». Если выражение «D13<100» ложно, то в ячейке D14 рассчитывается суммарную стоимость перевозок за год по функции «СРЗНАЧ(D3:D13)»(рисунок 3).

Рисунок 3 . Вид окна мастера функций на 2 шаге.

При нахождении логической функции «ЕСЛИ» воспользовался логической формулой, для этого выбрала команду Вставка/Функция. После вызова мастера функций появляется первое окно диалога, в котором отображаются категории и имена функций. Нашла категорию функции – «Логические» и имя функции – «ЕСЛИ». 

На втором шаге работы мастера функций появляется новое диалоговое окно, в котором необходимо ввести значения для расчета логической функции «ЕСЛИ». Окно имеет три строки. В первой строке указал логическое выражение («D13<100»), во второй строке – значение, при котором логическое выражение истинно («количество рейсов за год»), и в третьей строке – значение, при котором значение логического выражения ложно («СРЗНАЧ(D3:D13)»).

1.3  Связывание таблиц

В данном подразделе осуществляется связывание двух таблиц с целью связывания данных таблицы 1 с данными таблицы 2.

При связывании таблиц применил команду ПРАВКА/ СПЕЦИАЛЬНАЯ ВСТАВКА.

Наши таблицы связаны по полю «Стоимость перевозок за 1 рейс, руб.», так как этот столбец имеется и в таблице 1 (лист-источник) и в таблице 2 (лист-приемник).

Выделил ячейки А2:А12 и D2:D12 (удерживая клавишу «CTRL») из таблицы 1 и выбрала команду ПРАВКА/КОПИРОВАТЬ. Установила курсор в таблице 2 на ту ячейку, куда нужно копировать (ячейка А2, лист «Таблица 2»), и выполнила команду ПРАВКА/СПЕЦИАЛЬНАЯ ВСТАВКА/ВСТАВИТЬ СВЯЗЬ. В ячейках B2:В12 таблицы 2 появились указания на ячейки исходной первой таблицы  (такие как, например « =Таблица 1!D2»).

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

2 УПРАВЛЕНИЕ И ГРАФИЧЕСКОЕ ОТОБРАЖЕНИЕ ДАННЫХ

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

Так как при сортировке данные таблицы будут изменены, то создала новый лист «Сортировка» и в нем вставила копию таблицы 1. Для этого выделила всю таблицу 1 из исходных данных (А1:D13) из листа «Лист1», выбрала в меню команду ПРАВКА/КОПИРОВАТЬ и вставила выделенный фрагмент на новый лист «Сортировка», используя команду ПРАВКА/ ВСТАВИТЬ.

Для сортировки, выделила мышкой интервал ячеек А2:D12. В строке меню выбрала команду  Данные/Сортировка, в появившемся окне диалога указала: Сортировать по - «Ф.И.О. шафера» и тип сортировки – «по возрастанию». В итоге получила отсортированную таблицу (рисунок 4)

Расчет стоимости перевозок груза
Ф.И.О шафера	Перевезено груза за 1 рейс, т	Стоимость перевозки 1 тонны груза, руб.	Стоимость перевозки за 1 рейс, руб.
Зимин О.Н.	5	200	40
Иванов П.Р.	4	200	50
Игошов Л.Д.	2	200	100
Козлов Б.Л.	5	200	40
Коротков О.П.	1	200	200
Петухов А.У.	2	200	100
Рыбин Р.Е.	2	200	100
Сидоров А.В.	4	200	50
Соров Б.Р.	6	200	33
Шандыин П.Г.	3	200	67
Итого:	34	2000	780