Бакалавры экономики

2.2. Описание реализации алгоритма решения задачи

Запустить табличный процессор MS Excel.
Создать книгу с названием «Расчёт расхода горючего» (Файл – Сохранить как – Расчёт расхода горючего – выбрать необходимое место сохранения - Сохранить).
Лист 1 переименовать в лист «Протяжённость рейсов»^{^*}.
На рабочем листе «Протяжённость рейсов» MS Excel создать таблицу «Протяжённость рейсов».
Заполнить таблицу о выполненных рейсах исходными данными (рис.5).
Лист 2 переименовать в лист с названием «Техническая характеристика автомобилей».
На рабочем столе «Техническая характеристика автомобилей» MS Excel создать таблицу, в которой будут содержаться данные о технических характеристиках автомобилей.

Рисунок 5. Протяжённость рейсов

Рисунок 6. Техническая характеристика автомобилей

Лист 3 переименовать в лист с названием «Сведения о выполненных маршрутах».
На рабочем столе «Сведения о выполненных маршрутах» MS Excel создать таблицу, в которой будут содержаться данные о выполненных маршрутах.
Заполнить таблицу «Сведения о выполненных маршрутах» исходными данными (рис.7).
Заполнить графу Протяжённость рейсов, находящуюся на листе Сведения о выполненных маршрутах следующим образом:

Занести в ячейку G4 формулу, которая позволит вывести на экран значение протяжённости рейсов. При этом соответствующее значение протяжённости рейсов берётся из таблицы «Протяженность рейсов». Связь между таблицами «Сведения о выполненных маршрутах» (куда должно быть помещено значение) и «Протяжённость рейсов» (откуда должно быть взято значение) осуществляется на основе общего элемента таблиц.

Рисунок7. Сведения о выполненных маршрутах

В нашем случае этим элементом является номер рейса (в формуле – ячейка Е4). Значение этого общего элемента должно быть найдено в определённом интервале таблицы «Протяженность рейсов» (в формуле этот интервал – ячейки с С4 по С7) и в соответствии с ним в этой же таблице, также в определённом интервале (в формуле этот интервал – ячейки с D4 по D7), находится необходимое нам значение, которое должно появиться в таблице «Сведения о выполненных маршрутах».

Таким образом, создаётся следующая формула:

=ЕСЛИ(E4="";"";ПРОСМОТР(E4;Протяжённость!$С$4:$С$7;Протяжённость!$D$4:$D$7)).^¹

Размножить введённую в ячейку G4 формулу для остальных ячеек (с G5 по G10) данной графы.^²

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

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

При использовании Мастера функций для заполнения графы необходимо проделать следующие действия:

выделить ячейку G4;
нажать клавишу «=» (признак формулы);
нажать на панели инструментов «Стандартная» кнопку «Вставка функции». В диалоговом окне «Мастер функций – шаг 1 из 2» (рис.8);

Рисунок 8. Мастер функций

в «Категориях» выбрать ЛОГИЧЕСКИЕ; в Функциях выбрать ЕСЛИ. Нажать ОК. Появится окно функции ЕСЛИ;
в строке «Логические выражения» щелкнуть по красной стрелке, окно преобразуется в строку. В таблице «Сведения о выполненных маршрутах» выделить ячейку E4. Снова нажать на красную стрелку. Адрес этой ячейки появится в строке «Логические выражения». Далее в этой же строке набрать =«» (т.е. знак равенства и открыть/закрыть кавычки). Таким образом указывается в соответствии с какими данными будет выводиться ответ;
в строку «Значение_если_истина» необходимо ввести «» (т.е. открыть/закрыть кавычки);
установить курсор в строку «Значение_если_ложно». В левой части Строки формул имеется список с функциями. Нажать кнопку раскрытия списка и выбрать функцию ПРОСМОТР. Появится диалоговое окно «Мастер функций – 1 из 2»;
выбрать АРГУМЕНТ: искомое значение; вектор_просмотра; вектор_результата. Нажать ОК. Появится окно функции ПРОСМОТР;
в строке «Искомое значение» (рис.9) щёлкнуть по красной стрелке, окно преобразуется в строку. В таблице выделить ячейку E4. Снова нажать на красную стрелку. Адрес этой ячейки появится в окне «Искомое значение». Таким образом мы указываем выражение, которое необходимо найти в таблице «Протяжённость рейсов»;

Рисунок 9. Функция ПРОСМОТР ()

в строку «Вектор просмотра» установить курсор, щелкнуть по красной стрелке, окно преобразуется в строку. Перейти на лист Протяжённость рейсов, щёлкнув по названию листа. В таблице Протяжённость рейсов выделить ячейки C4:C7. Снова нажать на красную стрелку. Адреса этих ячеек появятся в строке «Вектор просмотра». Таким образом указывается диапазон данных в которых мы должны найти выражение из строки «Искомое значение» ;
в строку «Вектор результата» установить курсор, щёлкнуть по красной стрелке, окно преобразуется в строку. Перейти на лист Протяжённость рейсов, щёлкнув по названию листа. В таблице Протяжённость рейсов выделить ячейки D4:D7. Снова нажать на красную стрелку. Адреса этих ячеек появятся в строке «Вектор результата». Таким образом указывается диапазон данных из которых необходимо выбрать ответ, в соответствии с искомым значением.
нажать ОК;

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

Занести в ячейку H4 формулу:

=ЕСЛИ(D4="";"";ПРОСМОТР(D4;'Технические характеристики'!$C$4:$C$6;'Технические характеристики'!$D$4:$D$6))

Размножить введённую в ячейку H4 формулу для остальных ячеек данной графы (с H5 по H10).

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

Занести в ячейку I4 формулу:

=ПРОСМОТР(D4;'Технические характеристики'!$C$4:$C$6;'Технические характеристики'!$D$4:$D$6)*F4*ПРОСМОТР(E4;Протяжённость!$C$4:$C$7;Протяжённость!$D$4:$D$7)/100

Размножить введённую в ячейку I4 формулу для остальных ячеек (с I5 по I10) данной графы.

В ячейку J4 ввести формулу:

=ЕСЛИ(D4="";"";ПРОСМОТР(D4;'Технические характеристики'!$C$4:$C$6;'Технические характеристики'!$E$4:$E$6))

Размножить формулу на остальные ячейки (с J5 по J10)

В ячейку К4 занести формулу, которая позволит рассчитать вес перевезённого груза:

=J4*F4

Размножить получившуюся формулу на остальные ячейки (с К5 по К10).

В ячейки I11 и К11 занести формулы, для нахождения, соответственно, общего количества израсходованного топлива и веса перевезённого груза:

=СУММ(I4:I10) и =СУММ(K4:K10)

Данную формулу можно занести вручную или с помощью Мастера функций. Для этого необходимо выбрать в меню «Функции» функцию СУММ и в появившимся окне в строку Число 1 ввести необходимый диапазон данных.

В ячейки I12 и К12 занести соответственно формулы, для нахождения средних значений количества израсходованного топлива и веса перевезённого груза:

=СУММ(I4:I10)/7 и =СУММ(K4:K10)/7

В результате должна появиться таблица со следующими данными (рис.10).

Рисунок10. Сведения о выполненных маршрутах

Добавить лист в книгу. Для этого установить курсор на наименование листа, в Строке меню выбрать Вставка, в раскрывшемся меню выберите Лист. Для перемещения перетащить лист правой кнопкой мыши в нужное место.
Лист 4 переименовать в лист с названием «Ведомость».
На рабочем столе «Ведомость» создать «Ведомость расхода горючего» и заполнить ячейки исходными данными (рис.11).

Рисунок 11. Ведомость расхода горючего

Оставшиеся ячейки заполнить следующим образом:

Для того чтобы заполнить графу «№ рейса» путём создания межтабличных связей, необходимо ввести в ячейку D10 таблицы «Ведомость расхода горючего» формулу, которая позволит отобразить в ячейке «№ рейса» этой таблицы значение из ячейки «№ рейса» из таблицы «Сведения о выполненных маршрутах». Чтобы осуществить связь между таблицами необходимо найти элемент, который входит в обе таблицы. В нашем случае это ФИО водителя (в формуле – ячейка С10). Этот общий элемент должен быть найден в таблице «Сведения о выполненных маршрутах» (в формуле - Сведения!$C$4:$K$12) и в соответствии с ним из столбца, указанного в формуле (в нашем случае это столбец 3) выводится нужное значение номера рейса в таблицу «Ведомость расхода горючего».

Таким образом, получается следующая формула:

=ВПР(C10;Сведения!$C$4:$K$12;3;ЛОЖЬ)

Данную графу можно заполнить как вручную, так и при помощи Мастера функций. При заполнении графы «№ рейса» вручную все используемые в формуле символы вводятся с клавиатуры.

Про использовании Мастера функций для заполнения графы «№ рейса» необходимо сделать следующие действия:

выделить ячейку D10;
нажать знак «=»;
Нажать на панели инструментов «Стандартная» кнопку «Вставка функции». Появится диалоговое окно «Мастер функций – шаг 1 из 2»;
В этом окне в «Категориях» выбрать ССЫЛКИ И МАССИВЫ; в функциях выбрать ВПР (). Нажать ОК. Появится окно функции «ВПР ()» (рис.12);
В строке «Искомое значение» щёлкнуть по красной стрелке, окно преобразуется в строку. В таблице «Ведомость расхода горючего» выделить ячейку C10. Снова нажать красную стрелку. Адрес этой ячейки появится в строке «Искомое значение»;

Рисунок12. Функция ВПР ()

В строке «Таблица» щёлкнуть по красной стрелке, окно преобразуется в строку. В таблице «Ведомость расхода горючего» выделить ячейки C4:K12. Снова нажать красную стрелку. Адрес этой ячейки появится в строке «Искомое значение». Таким образом указывается диапазон поиска нужных данных;
В строке «Номер столбца» указать номер столбца, из которого берутся данные;
В строке «Интервальный просмотр» вводим слово ЛОЖЬ, чтобы поиск в столбце производился точно;

Размножить введённую в ячейку D10 формулу для остальных ячеек (с D11 по D16) данной графы.

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

=ВПР(C10;Сведения!$C$4:$K$11;4;ЛОЖЬ)

Размножить введённую в ячейку E10 формулу для остальных ячеек (с E11 по E16) данной графы.

Для того чтобы заполнить графу «Израсходовано топлива, л.» путём создания межтабличных связей, необходимо ввести в ячейку F10 таблицы «Ведомость расхода горючего» формулу:

=ВПР(C10;Сведения!$C$4:$K$11;7;ЛОЖЬ)

Размножить введённую в ячейку F10 формулу для остальных ячеек (с F11 по F17) данной графы.

В результате должна получиться следующая таблица (рис.13).
Добавить лист.
Лист 5 переименовать в лист с названием «Диаграмма».
На рабочем листе «Диаграмма» MS Excel создать сводную таблицу. Чтобы создать сводную таблицу и диаграмму необходимо:

Рисунок13. Ведомость расхода горючего (с результатами вычислений)

Активизировать любую ячейку списка.
Выбрать в строке меню команды Данные/Сводная таблица.
В окне Мастер сводных таблиц и диаграмм шаг 1 выбрать в списке вид создаваемого отчёта Сводная диаграмма (со сводной таблицей) и нажмите Далее.
В окне Мастер сводных таблиц и диаграмм шаг 2 укажите диапазон, содержащий исходные данные. Для этого:
- нажать красную стрелочку справа от вводимого диапазона;
- перейти на лист «Ведомость расхода горючего»;
- выделить таблицу «Ведомость расхода горючего»;
- нажать опять красную стрелочку и нажмите Далее.
В окне Мастер сводных таблиц и диаграмм шаг 3 нажать кнопку Макет.
В окне Мастер сводных таблиц и диаграмм – Макет перетащить поля, как показано на рис.14.

Рисунок 14. Мастер сводных таблиц и диаграмм – Макет

Нажать кнопку OK.
Нажать кнопку Готово.
Появится ещё один лист на котором будет размещена диаграмма. Чтобы сводная таблица и диаграмма располагались на одном листе, щёлкнуть правой кнопкой мыши на области диаграммы. Выбрать из списка пункт Размещение. В списке листов в диалоговом окне Размещение выбрать Диаграмма.
Чтобы придать диаграмме нужный вид, необходимо её отредактировать:

Щелкнуть правой клавишей мыши на области диаграммы. В появившемся списке выберите Параметры диаграммы;
Окно Заголовки заполнить, как показано на рис.15;
В окне Легенда убрать флажок Добавить легенду;
В области диаграммы щелкнуть правой клавишей мыши на поле ФИО Водителей. Выбрать из появившегося списка Скрыть кнопки полей сводной диаграммы;

Рисунок15. Параметры диаграммы

Щелкнуть кнопкой мыши в любой из точек на рамке области диаграммы и растянуть рамку диаграммы до нужного размера;^³
Далее необходимо отредактировать сводную таблицу. Для этого выбрать на панели инструментов Сводные таблицы клавишу Формат отчёта;
Выбрать необходимый формат из списка и нажать ОК (рис.16);

Рисунок 16. Автоформат

Результаты вычислений представить графически (рис.17)

Рисунок 17. Ведомость расхода горючего за неделю

Таким образом, диаграмма (рис.17) завершает выполнение задания практической части.

Литература

1. Партыка Т.Л., Попов И.И. Информационная безопасность. Учебное пособие для студентов учреждений среднего профессионального образования.- М.: ИНФРА-М, 2004.

2. Панасенко С.П. Защита информации в компьютерных сетях // http://www.panasenko.ru (20.11.2006).

3. Бунин О.Н. Занимательное шифрование // Мир ПК, 2003, №7.

4. Панасенко С. П. Чтобы понять язык криптографов // Мир ПК, 2002, № 6.

5. Информатика: Методические указания по выполнению курсовой работы для самостоятельной работы студентов II курса (первое высшее образование). – М.: Вузовский учебник, 2006.

6. Информатика: Лабораторный практикум для студентов II курса всех специальностей. – М.: Вузовский учебник, 2006.

*В данной задаче листы названы следующим образом: Лист 1 - «Протяжённость»; лист 2 - «Технические характеристики», лист 3 - «Сведения»

1Мышкой выделяются необходимые значения и нажимается клавиша F4, появляются знаки $ - признак абсолютного адреса.

2 Установите стрелочку мыши в правый нижний угол активной ячейки (маркер заполнения), стрелочка должна измениться на «+», удерживая нажатой левую клавишу мыши, протяните за маркер вниз до нужной ячейки. Строки должны заполниться соответствующими значениями.

3Чтобы убрать диаграмму (в случае необходимости) следует щелкнуть кнопкой мыши внутри диаграммы и нажать Delete.

Содержание