Как рассчитать расход топлива в excel

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

Порядок работы:

1.      
Открыть книгу Excel одним из
способов:

1 способ  Пуск, Программы, Microsoft Excel, Файл, Отрыть, устанавливаем в окне Папка Личную
папку, выделяем одну из рабочих книг и нажимаем клавишу Открыть,
переходим на чистый лист

2-способ  Мой компьютер, Личная папка, Выделить один из файлов Exсel, нажать клавишу Открыть и перейти на
чистый лист.

2.      
Переименовать листы таблицы как Табл7, Табл8,
Табл9. Для этого:

—        
поставить   указатель мыши на наименование листа;

—        
вызвать контекстное меню щёлкнув правой клавишей
мыши;

—        
 переименовать;

—        
удаляем старую надпись;

—        
набираем новую;

—        
ENTER

3.      
Выполните названия и шапки таблиц с учётом
действующего стандарта. При этом нужно пользоваться пунктом меню Формат,
вкладкой Выравнивание. Например, для Табл7:

Выделить
блок А1:Е1, нажать клавишу “Объединить и поместить в центре” на панели Главная

Поставить указатель мышки между
обозначениями 1 и 2 строк так, чтобы он изменился в двунаправленную черную
стрелку, нажать левую клавишу мыши и протащить её до нужной для заголовка
ширины строки.

Так
же увеличить ширину столбцов A, B, C, D, E и строки 2.

Выделить
блок А1:Е2.

Выполнить
команду: Формат, Ячейки, выбираем  вкладку Выравнивание и 
устанавливаем  флажки  (по горизонтали —  по центру,  по вертикали тоже —  по
центру,   переносить по словам), Ок.

Заносим
название таблицы в ячейку A1

Заносим
названия граф в ячейки A2, B2, C2, D2, E2 соответственно.

4.      
Заполняем таблицы с учётом того, что приведённые в
таблицах величины, являются исходными, остальные графы – производными:

Табл7   
Годовая потребность в топливе = Норма расхода * Суммарное планируемое время

Табл8    Планируемая
потребность в дизельном топливе — берём из Табл7 с помощью ссылки

Сезонная потребность в бензине = Норма бензина *
Планируемая потребность / 100

Табл9    Планируемая
потребность в дизельном топливе — берём из Табл7 с помощью ссылки

Потребность в смазочных материалах = Норма * Планируемую
потребность в дизельном топливе  /  100

5.      
Сохраняем книгу в Личной папке. Выходим из Excel.

6.      
Оформляем отчёт. В отчёте должны быть приведены
таблицы с указанием формул и ссылок. Чтобы в
таблицах вместо результатов высвечивались формулы необходимо выполнить команду Формулы,
Отобразить формулы.

ТРЕНИНГИ

   Быстрый старт
   Расширенный Excel
   Мастер Формул
   Прогнозирование
   Визуализация
   Макросы на VBA

КНИГИ

   Готовые решения
   Мастер Формул
   Скульптор данных

ВИДЕОУРОКИ

ОНЛАЙН-КУРСЫ

ФОРУМ

   Excel
   Работа
   PLEX

© Николай Павлов, Planetaexcel, 2006-2022
info@planetaexcel.ru

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

Техническая поддержка сайта

Задание 2. Табличный процессор MS Excel

2.1. Постановка задачи

1. Создать в MS Excel следующую
таблицу и заполнить ее данными:

Ведомость учета расхода бензина в
автохозяйстве

2. Рассчитать
следующие характеристики

1) расход бензина по норме и перерасход (экономию) бензина
для каждого автомобиля;

2) суммарные значения фактического расхода бензина, расхода
бензина по норме, перерасход (экономию) бензина для всего автохозяйства
(функция СУММ), среднее значение пробега для всех автомобилей (функция СРЗНАЧ);

3) суммарные значения фактического расхода бензина для
каждой марки автомобиля (функция СУММЕСЛИ);

4) количество автомобилей каждой марки в автохозяйстве
(функция СЧЕТЕСЛИ).

3. С помощью
фильтрации получить следующую информацию:

1) марку автомобиля, для которого норма расхода бензина
составляет 33,4л / 100 км;

2) номер автомобиля, имеющего максимальный фактический
расход бензина;

3) номер автомобиля ЗИЛ-130, имеющего пробег от 1000 до 2000
км;

4) номера автомобилей ЗИЛ-130 или ГАЗ-66, имеющих пробег
более 1500 км.

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

2.2. Создание таблицы и заполнение ее исходными данными

В объединение диапазона ячеек А1 – Н1 введем заголовок
таблицы «Ведомость учета расхода бензина в автохозяйстве».

В ячейки А3 – Н3 введем заголовки столбцов таблицы: «Марка
автомобиля», «Номер», «Гараж», «Пробег, км», «Фактический расход бензина, л»,
«Норма расхода бензина л/100 км», «Расход бензина по норме, л», «Перерасход
(экономия) бензина, л».

Заполним таблицу исходными данными, после чего она примет
вид:

2.3. Расчет суммарных значений фактического расхода и перерасхода бензина и
среднего значения пробега

Для вычисления Расхода
бензина по норме для каждой строки нужно Пробег разделить на 100 и умножить на Норму расхода бензина л/100 км. В результате для первой строки
данных таблицы получим следующую формулу вычисления Расхода бензина по норме.

В результате формула для расчета Расхода бензина по норме для первого автомобиля будет следующая: «D4/100*F4». Аналогичная формула будет для всех
остальных строк. Поэтому, созданную формулу в ячейке G4 скопируем во все ячейки диапазона G5 – G13. Ссылки на ячейки, используемые в
формулах, будут обновляться автоматически.

Для расчета Перерасхода
(экономии) бензина, нужно из значения
Расход бензина по норме вычесть Фактический
расход бензина. Например, для первой строки данных в ячейку H4 нужно ввести формулу «=G4-E4». Расчет Перерасхода бензина для остальных автомобилей аналогичен.

Для расчета суммарных значений фактического расхода бензина сначала объединим ячейки A15 – D15 и введем текст «Итого:». Зададим
выравнивание по правому краю и полужирное
начертание.

Перейдем в ячейку Е15 и нажмем кнопку Автосумма на панели инструментов.
При помощи появившегося прямоугольника выделения диапазона выделим диапазон
ячеек, данные из которых нужно просуммировать.

Аналогичным образом вычислим суммарные значение Расхода бензина по норме и Перерасход (экономию) бензина.

Теперь вычислим среднее значение пробега для всех
автомобилей. Сначала в объединение ячеек А16 – С16 введем поясняющий текст «Среднее
значение пробега».

Перейдем в ячейку D16, на панели инструментов нажмем стрелочку, расположенную
справа от кнопки Автосумма.
Из открывшегося списка выберем пункт «Среднее».
Укажем диапазон D4 – D13.

После проведения описанных выше действий, таблица примет
вид:

2.4. Расчет суммарных значений фактического расхода бензина для каждой
марки автомобиля

Введем поясняющую «Суммарный расход бензина для ГАЗ-66»
запись в объединение ячеек А17 – D17.

Перейдем в ячейку Е17 и нажмем стрелочку около кнопки Автосумма. Из
списка выберем пункт Другие функции и
выберем функцию СУММЕСЛИ.

В появившемся окне ввода аргументов функции СУММЕСЛИ зададим диапазон А4:А13,
критерий «ГАЗ-66» и диапазон суммирования Е4:Е13.

Аналогичным образом вычислим Суммарный фактический расход бензина для ГАЗ-66. Только изменим
значение критерия.

2.5. Подсчет количества автомобилей каждой марки

В объединение ячеек A19 – D19
введем «Количество ГАЗ-66», а в ячейки A20 – D20
введем «Количество ЗИЛ-130».

Перейдем в ячейку Е19 и нажмем кнопку выбора функции. Из
списка выберем функцию СЧЕТЕСЛИ.

Зададим диапазон
А4:А13 и Критерий «ГАЗ-66».

Аналогичным образом подсчитаем количества ЗИЛ-130.

В результате получим следующее:

2.6. Вид окна Microsoft Excel при просмотре формул

В режиме просмотре формул окно Microsoft Excel будет
иметь вид:

2.7. Использование фильтрации

2.7.1. Отбор автомобилей, для которых норма расхода бензина составляет 33,4
л/100 км.

Отобразим при помощи фильтрации автомобили, для которого
норма расхода бензина составляет 33,4 л/100 км.

Выберем из главного меню Microsoft Excel пункт Данные > Фильтр > Автофильтр. После
этого в каждом из заголовков таблицы появятся стрелочки.

Для отбора автомобилей, у которых Норма расхода бензина составляет 33,4 л/100 км, в столбце Норма расхода бензина нажмем на
стрелочку в заголовке столбца и выберем 33,4.

В результате применения данного фильтра, будут отображаться
только следующие строки, в которых Норма
расхода бензина составляет 33,4 л/100 км:

2.7.2. Отбор автомобиля с максимальным фактическим расходом бензина

Из списка для поля Фактический
расход бензина выберем максимальное значение. Так как в этом списке
значения упорядочены от меньшего к большему, то в этом списке нужно выбрать
последнее значение.

В результате применения фильтра получим строку  с максимальным значением Фактического расхода бензина:

2.7.3. Отбор автомобиля ЗИЛ-130 с пробегом от 1000 до 2000 км

Для поля Марка
автомобиля из выпадающего списка автофильтра
выберем «ЗИЛ-130», а для поля Пробег
выберем пункт Условие.

В первом списке появившейся формы выберем Больше и зададим значение 1000, зададим логическую связку И и во втором
списке выберем Меньше и введем
значение 2000.

Форма пользовательского автофильтра
будет иметь вид:

В результате применения этого фильтра будут отображены
данные, удовлетворяющие поставленному условию:

2.7.4. Отбор автомобилей ЗИЛ-130 или ГАЗ-66 с пробегом более 1500 км.

Для поля Пробег
зададим условие больше 1500.

Будут выданы данные об автомобилях с пробегом более 1500 км:

2.8. Создание сводной таблицы

Из главного меню Microsoft Excel выберем
пункт Данные > Сводная таблица. На
первом шаге запустившегося мастера Создание
таблицы на основе данных, находящихся в списке или базе данных Microsoft Excel, а Вид создаваемого отчета – сводная таблица.

На втором шаге мастера зададим диапазон A3:H13, содержащих исходные данные.

На третьем шаге зададим расположение сводной таблицы на новом листе.

Перетащим в Поля строк
поле Марка автомобиля, а Поля столбцов – Гараж. В раздел данных переместим поле Перерасход (экономия) бензина.

Получим следующую сводную
таблицу:

Задание 3. СУБД MS Access

3.1. Создание структуры таблиц

Создание таблиц будем выполнять при помощи Конструктора.

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

Подчиненная таблица Объем
ремонтных работ в режиме Конструктора
будет выглядеть так:

3.2. Создание связи между таблицами

Для возможности отбора и анализа данных одновременно из
обоих таблиц, между ними нужно установить связь.

Установка связи выполняется в окне Схема данных.

Установим связь между полем ТС таблицы ОбъемРемонтныхРабот
и полем Код таблицы ТранспортныеСредства.
При установке связи необходимо установить параметры Обеспечение целостности данных, Каскадное
обновление связанных полей, Каскадное
удаление связанных записей.

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

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

В главной таблице Транспортные
средства будут храниться следующие данные:

В подчиненной таблице Объем
ремонтных работ будут храниться данные о проведенных ремонтных работах:

3.4. Создание запросов

3.4.1. Отбор автомобилей, отремонтированных бригадой № 1

В бланк Конструктора
запроса перенесем поля Номер и НомерБригады из
таблицы ОбъемРемонтныхРабот.

Для поля НомерБрагады зададим условие
отбора – 1.

В режиме Конструктора
созданный запрос будет иметь вид:

При выполнении этого запроса будут выданы следующие данные о
номерах автомобилей, отремонтированных 1 бригадой:

3.4.2. Отбор автомобилей, отремонтированных 2 бригадой с объемом ремонтных
работ от 3 до 6 тысяч рублей

Зададим Условия отбора
для поля НомерБригады – 2, а для поля ОбъемРаботТысРуб — >3000 And <6000.

В режиме конструктора
созданный запрос будет иметь вид:

Этот запрос выдаст следующие данные об автомобилях,
отремонтированных 2 бригадой, с объемом работ от 3 до 6 тысяч рублей:

3.4.3. Отбор автомобилей, отремонтированные 1 и 3 бригадой после 20 марта
2003 г.

Для поля НомерБригады зададим условие
отбора 1 Or 3, а для поля ДатаПриемки — >#20.03.2003#.

При выполнении, запрос выдаст данные:

3.4.4. Суммарный объем работ, выполненный каждой бригадой

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

На первом шаге мастера выберем в качестве источника для перекрестного запроса, выберем таблицу ОбъемРемонтныхРабот.
В качестве заголовков строк выберем НомерБригады, а заголовков столбцов – ТС.

Для поля ОбъемРаботТысРуб зададим функцию Сумма.

Созданный перекрестный
запрос выдаст следующий результат:

3.4.5. Отбор автомобилей Toyota и АЗЛК, отремонтированных до 16 марта 2003 г

В бланк запроса
перенесем поля Номер и ДатаПриемки из
таблицы ОбъемРемонтныхРабот,
а также поле Производитель из таблицы
ТранспортныеСредства.

Для поля ДатаПриемки зададим условие
отбора <#16.03.2003#, а для поля Производитель
– Toyota или АЗЛК.

В режиме Конструктора
созданный запрос будет иметь вид:

При выполнении созданного запроса будут выданы данные об
автомобилях Toyota и
АЗЛК, отремонтированных до 16 марта 2003 г.:

3.5. Создание отчета «Транспортные средства»

Для создания этого отчета, сначала создадим простой запрос, который будет содержать
поля Производитель и Марка из таблицы Тран, а также поля ДатаПриемки, ОбъемРаботТысРуб,
ДатаСдачи и
НомерБригады
из таблицы ОбъемРемонтныхРабот.

В режиме Конструктора
созданный простой запрос будет иметь
вид:

Этот запрос выдаст следующий результат:

Теперь по только что созданному простому запросу создадим отчет.

Для этого на вкладке Отчеты
главного окна базы данных выберем Создание
отчета при помощи мастера.

На первом шаге запустившегося мастера, в качестве источника,
выберем созданный запрос.

Выберем из него все поля. Для вида представления данных выберем ОбъемРемонтныхРабот.

Добавим уровень
группировки по полю НомерБригады.

На следующем шаге мастера зададим сортировку по возрастанию поля ДатаПриемки.

Зададим вычисление суммарного
объема работ для каждой бригады, нажав кнопку Итоги и задав функцию Sum.

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

В режиме Конструктора
созданный отчет будет иметь вид:

При печати созданный отчет выдаст следующее:

Объем ремонтных работ

НомерБригады   Дата приемки Производитель Марка               Объем работ   Дата сдачи

         1

                            13.03.2003             Toyota          Corona            2 400,00р.               16.03.2003

                            14.03.2003              АЗЛК         Москвич-            800,00р.                15.03.2003

                                                                       412

                            21.03.2003               ВАЗ           Жигули            4 280,00р.               27.03.2003

Итоги для ‘НомерБригады’ =  1 (3
записей)

Сумма по бригаде                                                                             7 480,00р.

         2

                            13.03.2003              АЗЛК         Москвич-           1 300,00р.               15.03.2003

                                                                       412

                            17.03.2003             Toyota          Corona            5 500,00р.               22.03.2003

                            24.03.2004               ГАЗ            Волга-             7 525,00р.               29.03.2003

                                                                      3110

                            27.03.2004               ВАЗ           Жигули            6 345,00р.               03.04.2004

Итоги для ‘НомерБригады’ =  2 (4
записей)

Сумма по бригаде                                                                            20 670,00р.

         3

                            16.03.2003             Toyota          Corona            3 000,00р.               20.03.2003

                            19.03.2003               ГАЗ            Волга-             2 700,00р.               24.03.2004

                                                                      3110

                            25.03.2994               ГАЗ            Волга-             1 890,00р.               01.04.2003

                                                                      3110

Итоги для ‘НомерБригады’ =  3 (3 записей)

Сумма по бригаде                                                                             7 590,00р.

ИТОГО                                                                            35
740,00р.