Главная страница
Навигация по странице:

  • Решение задачи: Введите следующую таблицу (рис.7). В ячейку В4 введите значение 0,15 и установите тип Процент

  • Форматирование

  • Сервис – Подбор параметра

  • Изменяя ячейку

  • Решение задачи: Создайте на листе Транспортные расходы

  • Транспортные расходы

  • Поиск решения . Выполнить поиск решения ( Сервис

  • Лаб_раб_EXCEL. Методические указания для выполнения лабораторных работ по Ms Excel по дисциплине оп. 09 Информационные технологии в профессиональной деятельности


    Скачать 1.28 Mb.
    НазваниеМетодические указания для выполнения лабораторных работ по Ms Excel по дисциплине оп. 09 Информационные технологии в профессиональной деятельности
    Дата11.10.2020
    Размер1.28 Mb.
    Формат файлаdoc
    Имя файлаЛаб_раб_EXCEL.doc
    ТипМетодические указания
    #142214
    страница3 из 7
    1   2   3   4   5   6   7

    ЛАБОРАТОРНАЯ РАБОТА 4

    Подбор параметров.



    ЗАДАЧА. Определить величину ежемесячной выплаты займа в 100000 р., если он взят на 36 месяцев при процентной ставке, равной 15%.

    Решение задачи:

    1. Введите следующую таблицу (рис.7). В ячейку В4 введите значение 0,15 и установите тип Процент, с помощью кнопки Процентный стиль на панели инструментов Форматирование. В ячейку В5 введите 36. Изучите по Справке назначение и формат функции ПЛТ (или ППЛАТ).




    Рисунок 7 – Пример таблицы


    1. В ячейку В6 введите формулу:

    =ПЛТ(В4/12;B5;-B3)

    В ней получите размер ежемесячного платежа при сроке ссуды в 36 месяцев (количество ежемесячных выплат равно 36).

    1. С помощью команды Сервис – Подбор параметра осуществите подбор такого количества ежемесячных выплат, при котором каждая выплата составит 5000 р.:

      • Выделите ячейку В6;

      • Выбрать команду Сервис – Подбор параметра, появится диалоговое окно;

      • В поле Установить в ячейке введите $B$6;

      • В поле Значение введите 5000;

      • В поле Изменяя ячейку введите $B$5.

    Нажав ОК, получите в ячейке В5 значение.


    ЛАБОРАТОРНАЯ РАБОТА 5

    Поиск решения. Уменьшение затрат на перевозку грузов.



    ЗАДАЧА. Допустим, что ваша фирма занимается переработкой мяса на нескольких заводах, расположенных в разных районах Москвы. Мясо поставляется объединениями фермеров со складов, расположенных в нескольких городах Московской области. Стоимость мяса одинаковая, однако перевозка со склада на завод зависит от расстояния и отличается для каждого склада и завода. Потребность заводов в мясе различна, и запасы на каждом складе ограничены. Требуется определить: с какого склада, на какой завод поставлять, сколько мяса для минимизации общих затрат на перевозку.
    Решение задачи:

    Создайте на листе Транспортные расходы таблицу (рис. 8).

    Для этого:

    • В ячейку А1 введите текст «Оптимизация транспортных потоков»;

    • В ячейку В2 введите текст «Потребители->»;

    • В ячейки С2:F2 введите названия мясоперерабатывающих заводов;

    • В ячейку А3 введите текст «Поставщики»

    • В ячейки А4:А8 названия складов.

    • Установите курсор в ячейку В4 и нажмите кнопку , после чего выделите ячейки с С4 по Е4. В строке формул появится формула =СУММ(С4:Е4). Нажмите кнопку , расположенную справа в строке формул, и формула будет введена.

    • Скопируйте содержимое ячейки В4 в ячейки В5:В8.

    • Выделите ячейки с С4 до F8. Введите цифру 1 и нажмите кнопку , Нажмите комбинацию клавиш Ctrl+D (автозаполнение столбцов в выделенной области), а затем нажмите Ctrl+R (автозаполнение строк в выделенной области). Все выделенные ячейки будут заполнены единицами. Установите формат ячеек выделенной области Числовой.

    • В ячейку A9 введите текст «Факт->».

    • В ячейку С9 введите формулу =СУММ(С4:С8). Скопируйте формулу в ячейки D9:F9.




    Рисунок 8 – Пример таблицы
    Подготовка первой части таблицы закончена. Каждое значение в ячейках на пересечении столбца конкретного завода и строки склада означает количество тонн, поставляемых в месяц с этого склада на данный завод. В нижней строке суммируется общее количество мяса, поставляемого на определенный завод, во втором столбце суммируется общее количество закупленного у конкретного склада мяса.

    Введите требуемые объемы поставок и цены поставок. Для этого:

    • Введите в ячейку A10 текст «Запросы ->». В десятой строке вводятся значения потребляемого каждым из заводов мяса в тоннах.

    • В ячейки этой строки введите соответственно:

    B11

    3

    C10

    240

    D10

    115

    E10

    280

    F10

    370



    00

    B12

    240

    B13

    170

    B14

    120

    B15

    320




    • Выделите ячейки с A4 по A8. Нажмите клавишу Ctrl и, не отпуская ее, подведите курсор мыши к краю выделенного интервала, нажмите левую клавишу мыши и двигайте мышь. Появится серый прямоугольник размером с выделенную область. Расположите его в ячейки с А11 по А15, затем отпустите клавишу мыши и клавишу Ctrl. Названия складов будут скопированы.

    • В ячейки второго столбца занесите объемы месячных запасов на различных складах в тоннах соответственно.

    • В ячейки с С11 по F15 занесите стоимость перевозки тонны мяса с конкретного склада на конкретный завод. Для этого введите в ячейки с С11 по F15 следующие данные:

    47000

    41500

    45000

    32650

    39000

    32300

    38000

    41000

    23650

    27300

    21000

    18000

    19500

    19400

    9000

    24000

    39000

    36000

    27500

    44000




    • В ячейку А16 введите текст «Всего».

    • В ячейку С16 введите формулу

    =С4*С11+С5*С12+С6*С13+ С7*С14+С8*С15.
    В ячейке С4 находится количество мяса, перевозимого со склада в Наро-Фоминске на завод в Лужниках, а в ячейке С11 — цена перевозки тонны груза по этому маршруту. Соответственно, первое слагаемое в формуле означает полную стоимость перевозок по данному маршруту. Вся же формула вычисляет полную стоимость перевозок мяса на завод в Лужниках.


    • Скопируйте формулу из ячейки С16 в ячейки D16:F16

    • В ячейку В16 введите формулу =СУММ(С16:F16). В данной ячейке будет вычисляться общая стоимость перевозки мяса.

    • В ячейку А18 введите текст «Всего на перевозки требуется», а в ячейку Е18 —«млн.руб.».

    • Для вычисления суммы в миллионах в ячейку D18 введите формулу =В16/1000000.

    Выполните форматирование таблицы в соответствии с рис. 8. Скопируйте лист Транспортные расходы (Правка — Переместить/Скопировать лист) для возможного восстановления начального вида таблицы. Переименуйте скопированный лист, дав ему название Поиск решения.

    Выполнить поиск решения (Сервис Поиск решения) с целью определения минимальных затрат на перевозки при соблюдении следующих условий (рис. 9):

    • Объем поставок с конкретного склада должен быть меньше или равен запасам на складе.

    • Объем перевозок не должен быть отрицательным.

    • Запросы заводов должны быть выполнены полностью. Перевыполнение поставок допустимо, а недовыполнение нет:



    Рисунок 9 – Поиск решения
    Сохраните результаты поиска решения. Проверьте правильность полученных результатов (рис. 10).



    Рисунок 10 – Результаты поиска решения
    1   2   3   4   5   6   7


    написать администратору сайта