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

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

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

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

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

· выделить ячейку G4;

· нажать клавишу «=» (признак формулы);

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

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

· в «Категориях» выбрать ЛОГИЧЕСКИЕ; в Функциях выбрать ЕСЛИ. Нажать ОК. Появится окно функции ЕСЛИ;

· в строке «Логические выражения» щелкнуть по красной стрелке, окно преобразуется в строку. В таблице «Сведения о выполненных маршрутах» выделить ячейку E4. Снова нажать на красную стрелку. Адрес этой ячейки появится в строке «Логические выражения». Далее в этой же строке набрать =«» (т.е. знак равенства и открыть/закрыть кавычки). Таким образом указывается в соответствии с какими данными будет выводиться ответ;

· в строку «Значение_если_истина» необходимо ввести «» (т.е. открыть/закрыть кавычки);

· установить курсор в строку «Значение_если_ложно». В левой части Строки формул имеется список с функциями. Нажать кнопку раскрытия списка и выбрать функцию ПРОСМОТР. Появится диалоговое окно «Мастер функций – 1 из 2»;

· выбрать АРГУМЕНТ: искомое значение; вектор_просмотра; вектор_результата. Нажать ОК. Появится окно функции ПРОСМОТР;

· в строке «Искомое значение» (рис.9) щёлкнуть по красной стрелке, окно преобразуется в строку. В таблице выделить ячейку E4. Снова нажать на красную стрелку. Адрес этой ячейки появится в окне «Искомое значение». Таким образом мы указываем выражение, которое необходимо найти в таблице «Протяжённость рейсов»;

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

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

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

· нажать ОК;

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

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

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

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

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

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

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

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

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

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

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

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

=J4*F4

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

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

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

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

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

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

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

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

19. Добавить лист в книгу. Для этого установить курсор на наименование листа, в Строке меню выбрать Вставка, в раскрывшемся меню выберите Лист. Для перемещения перетащить лист правой кнопкой мыши в нужное место.

20. Лист 4 переименовать в лист с названием «Ведомость».

21. На рабочем столе «Ведомость» создать «Ведомость расхода горючего» и заполнить ячейки исходными данными (рис.11).

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

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

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

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

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

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

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

o выделить ячейку D10;

o нажать знак «=»;

o Нажать на панели инструментов «Стандартная» кнопку «Вставка функции». Появится диалоговое окно «Мастер функций – шаг 1 из 2»;

o В этом окне в «Категориях» выбрать ССЫЛКИ И МАССИВЫ; в функциях выбрать ВПР (). Нажать ОК. Появится окно функции «ВПР ()» (рис.12);

o В строке «Искомое значение» щёлкнуть по красной стрелке, окно преобразуется в строку. В таблице «Ведомость расхода горючего» выделить ячейку C10. Снова нажать красную стрелку. Адрес этой ячейки появится в строке «Искомое значение»;

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

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

o В строке «Номер столбца» указать номер столбца, из которого берутся данные;

o В строке «Интервальный просмотр» вводим слово ЛОЖЬ, чтобы поиск в столбце производился точно;

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

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

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

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

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

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

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

23. В результате должна получиться следующая таблица (рис.13).

24. Добавить лист.

25. Лист 5 переименовать в лист с названием «Диаграмма».

26. На рабочем листе «Диаграмма» MS Excel создать сводную таблицу. Чтобы создать сводную таблицу и диаграмму необходимо:

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

· Активизировать любую ячейку списка.

· Выбрать в строке меню команды Данные/Сводная таблица.

· В окне Мастер сводных таблиц и диаграмм шаг 1 выбрать в списке вид создаваемого отчёта Сводная диаграмма (со сводной таблицей) и нажмите Далее.

· В окне Мастер сводных таблиц и диаграмм шаг 2 укажите диапазон, содержащий исходные данные. Для этого:

o нажать красную стрелочку справа от вводимого диапазона;

o перейти на лист «Ведомость расхода горючего»;

o выделить таблицу «Ведомость расхода горючего»;

o нажать опять красную стрелочку и нажмите Далее.

· В окне Мастер сводных таблиц и диаграмм шаг 3 нажать кнопку Макет.

· В окне Мастер сводных таблиц и диаграмм – Макет перетащить поля, как показано на рис.14.

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

· Нажать кнопку OK.

· Нажать кнопку Готово.

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

· Чтобы придать диаграмме нужный вид, необходимо её отредактировать:

o Щелкнуть правой клавишей мыши на области диаграммы. В появившемся списке выберите Параметры диаграммы;

o Окно Заголовки заполнить, как показано на рис.15;

o В окне Легенда убрать флажок Добавить легенду;

o В области диаграммы щелкнуть правой клавишей мыши на поле ФИО Водителей. Выбрать из появившегося списка Скрыть кнопки полей сводной диаграммы;

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

o Щелкнуть кнопкой мыши в любой из точек на рамке области диаграммы и растянуть рамку диаграммы до нужного размера;[3]

o Далее необходимо отредактировать сводную таблицу. Для этого выбрать на панели инструментов Сводные таблицы клавишу Формат отчёта;

o Выбрать необходимый формат из списка и нажать ОК (рис.16);

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

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

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

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