Как ставить задачи в excel

Решение простых задач с помощью Excel

Классы: 6 , 7

Ключевые слова: Excel , функции в Excel , ячейка , адрес ячейки , таблица

Цель урока: продолжить формирование навыков работы с электронными таблицами.

Задачи:

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

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

  • воспитательные:
  • формировать и воспитывать познавательный интерес; прививать навыки самостоятельности в работе.

План урока.

  1. Организационный момент.
  2. Актуализация знаний учащихся.
  3. Проверка домашнего задания.
  4. Решение задач.
  5. Самостоятельное решение задачи.
  6. Подведение итогов. Оценки.
  7. Домашнее задание.

1. Организационный момент.

Сообщить тему урока, сформулировать цели и задачи урока.

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

Сможете ли вы помочь Васе? Сейчас проверим!

2. Актуализация знаний учащихся.

1) Устно ответить на вопросы.

A B C D
1 2 1 =A1+3*B1 =A1^2+B1
2 4 6 =A2+3*B2 =A2^2+B2
  • Что такое электронная таблица?
  • Какие основные элементы электронной таблицы вам известны?
  • Как задается имя ячейки (строки, столбца) в электронной таблице?
  • Что может быть содержимым ячейки?
  • Число 1 находится в столбце . в строке . в ячейке с адресом .
  • Число 4 находится в ячейке с адресом .
  • Каковы правила записи формул в ячейках?
  • Чему равно значение, вычисляемое по формуле, в ячейке С1?
  • Чему равно значение, вычисляемое по формуле, в ячейке D2?

2) Какой результат будет получен в ячейках с формулами?

А В
1 25 4
2 2 =A1*B1/2
3
A B C D
1 5 2 1
2 6 8 3
3 8 3 4
4 =СУММ(B1:D3)
  • Что означает запись =СУММ(В1:D3)?
  • Сколько элементов содержит блок В1:D3? Ответ: 9.
  • Содержимое ячейки D3?

Ответ: 5+2+1+6+8+3+8+3+4= 40

3) Проверка домашнего задания

Результаты соревнований по плаванию

Один ученик рассказывает, как он выполнил домашнее задание (через проектор).

Ф.И.О. 1 2 3 Лучшее время Среднее время Отклонение
1 Лягушкин 3.23 3.44 3.30
2 Моржов 3.21 3.22 3.24
3 Акулов 3.17 3.16 3.18
4 Рыбин 3.24 3.20 3.18
5 Черепахин 3.56 3.44 3.52
Лучший результат соревнований
Среднее время участников соревнований
Максимальное отклонение
  • Среднее время для каждого спортсмена находится как среднее арифметическое трех его заплывов.
  • В ячейку "Лучшее время" записывается минимальный результат из 3 заплывов.
  • В ячейку "Лучший результат соревнований" записывается минимальное время из столбца.
  • В столбец "Отклонение" записывается разность между лучшим временем спортсмена и лучшим результатом соревнований.
  • В ячейку "Максимальное отклонение" записывается максимальное значение столбца.
Результаты соревнований по плаванию
Ф.И.О. 1 2 3 Лучшее время Среднее время Отклонение
1 Лягушкин 3,23 3,44 3,30 3,23 3,32 0,07
2 Моржов 3,21 3,22 3,24 3,21 3,22 0,05
3 Акулов 3,17 3,16 3,18 3,16 3,17 0,00
4 Рыбин 3,24 3,20 3,18 3,18 3,21 0,02
5 Черепахин 3,56 3,44 3,52 3,44 3,51 0,28
Лучший результат соревнований 3,16
Среднее время участников соревнований 3,29
Максимальное отклонение 0,28

4) Решение простых задач.

Маленький великан Вася решил отремонтировать забор вокруг своего огорода и вскопать его под посадку овощей (наступила очередная весна), разметить грядки прямоугольной формы. Для работы ему потребовалось найти длину забора и площадь участка. Но ведь в школе он никогда не учился. Поможем Васе.

№ 1. Вычислить периметр и площадь прямоугольника со сторонами:

а) 3 и 5; б) 6 и 8; в) 10 и 7.

Эту задачу обсуждаем совместно с детьми:

  • Как оформить таблицу?
  • Какие формулы использовать?
  • Как использовать уже записанные формулы для следующего прямоугольника?

Оформление таблицы – на доске и в тетрадях.

В то же время другой ученик самостоятельно решает следующую задачу и представляет свое решение учащимся (через проектор).

№ 2. Маленький великан Вася решил подсчитать, через сколько дней в его копилке будет 100 руб., если ежедневно он стал класть туда на 5 руб. больше, чем в предыдущий день. Помогите Васе. Сейчас в его копилке 2,02 руб.

Обсудив решение задачи № 2, переходим к решению следующей.

Один ученик показывает, как работать с формулами, другой – как использовать функцию суммирования, числовой формат (общий, денежный) и т.д. (Таблица уже готова, ученикам предстоит ввести формулы, использовать суммирование и получить ответ).

№ 3. Посчитайте, используя ЭТ, хватит ли Васе 150 рублей, чтобы купить все продукты, которые ему заказала мама, и хватит ли на чипсы за 10 рублей? Сдачу мама разрешила положить в копилку. Сколько рублей попадет в копилку?

5) Самостоятельное решение задачи.

Маленький великан Вася часто бывал в гостях у жителей Цветочного города.

Собираясь на пляж, веселые человечки решили запастись прохладительными напитками. Незнайка взял с собой 2 литра кваса, 1 литр газировки и 1 литр малинового сиропа, Пончик – 3 литра газировки и 2 литра малинового сиропа, Торопыжка – 2 литра газировки, доктор Пилюлькин – 1 литр кваса и 1 литр касторки.

  • Сколько литров напитков каждого вида взяли все человечки вместе?
  • Сколько всего литров напитков взял с собой каждый из человечков?
  • Сколько всего литров напитков взяли все человечки вместе?

Оформите таблицу произвольно и сохраните в своей личной папке.

Веселые человечки. Напитки.
Напиток Незнайка Пончик Торопыжка Пилюлькин Всего
Квас, л 2 0 0 1 3
Газировка, л 1 3 2 0 6
Сироп, л 1 2 0 0 9
Касторка, л 0 0 0 1 1
ИТОГО: 4 5 2 2 13

7) Подведение итогов. Оценки.

8) Домашнее задание.

Подумайте и решите эту задачу, если известны еще следующие величины.

Как изменится таблица? Какие формулы появятся?

Известно, что 1 литр кваса в Цветочном городе стоит 1 монету, 1 литр газировки – 3 монеты, 1 литр малинового сиропа – 6 монет, 1 литр касторки – 2 монеты.

  • Сколько монет истратил на покупку напитков каждый человечек?
  • Сколько монет затрачено на покупку напитков каждого вида?
  • Сколько потрачено денег всеми человечками вместе?
  • Информатика. Задачник-практикум в 2 т. /Под ред. И.Г.Семакина, Е.К.Хеннера – М.: Лаборатория Базовых Знаний, 2010.
  • Ефимова О. Курс компьютерной технологии с основами информатики. – М.: ООО “издательство АСТ”; АВF, 2005.
  • Поиск решения EXCEL. Знакомство

    history 23 марта 2015 г.
      Группы статей

    • Надстройка "Поиск решения"

    Поиск решения — это надстройка Microsoft Excel, с помощью которой можно найти оптимальное решение задачи с учетом заданных пользователем ограничений.

    Поиск решения будем рассматривать в MS EXCEL 2010 (эта надстройка претерпела некоторые изменения по сравнению с предыдущей версией в MS EXCEL 2007) . В этой статье рассмотрим:

    • создание оптимизационной модели на листе MS EXCEL
    • настройку Поиска решения;
    • простой пример (линейная модель).

    Установка Поиска решения

    Команда Поиск решения находится в группе Анализ на вкладке Данные .

    Если команда Поиск решения в группе Анализ недоступна, то необходимо включить одноименную надстройку. Для этого:

    • На вкладке Файл выберите команду Параметры , а затем — категорию Надстройки ;
    • В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти;
    • В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.

    Примечание . Окно Надстройки также доступно на вкладке Разработчик . Как включить эту вкладку читайте здесь .

    После нажатия кнопки Поиск решения в группе Анализ, откроется его диалоговое окно .

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

    О моделях

    Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.

    Совет . Перед использованием Поиска решения настоятельно рекомендуем изучить литературу по решению оптимизационных задач и построению моделей.

    Ниже приведен небольшой ликбез по этой теме.

    Надстройка Поиск решения помогает определить лучший способ сделать что-то :

    • "Что-то" может включать в себя выделение денег на инвестиции, загрузку склада, доставку товара или любую другую предметную деятельность, где требуется найти оптимальное решение.
    • "Лучший способ" или оптимальное решение в этом случае означает: максимизацию прибыли, минимизацию затрат, достижение наилучшего качества и пр.

    Вот некоторые типичные примеры оптимизационных задач:

    • Определить план производства , при котором доход от реализации произведенной продукции максимальный;
    • Определить схему перевозок , при которой общие затраты на перевозку были бы минимальными;
    • Найти распределение нескольких станков по разным видам работ , чтобы общие затраты на производство продукции были бы минимальными;
    • Определить минимальный срок исполнения всех работ проекта (критический путь).

    Для формализации поставленной задачи требуется создать модель, которая бы отражала существенные характеристики предметной области (и не включала бы незначительные детали). Следует учесть, что модель оптимизируется Поиском решения только по одному показателю (этот оптимизируемый показатель называется целевой функцией ). В MS EXCEL модель представляет собой совокупность связанных между собой формул, которые в качестве аргументов используют переменные. Как правило, эти переменные могут принимать только допустимые значения с учетом заданных пользователем ограничений. Поиск решения подбирает такие значения этих переменных (с учетом заданных ограничений), чтобы целевая функция была максимальной (минимальной) или была равна заданному числовому значению.

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

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

    Подготовка оптимизационной модели в MS EXCEL

    Поиск решения оптимизирует значение целевой функции. Под целевой функцией подразумевается формула, возвращающая единственное значение в ячейку. Результат формулы должен зависеть от переменных модели (не обязательно напрямую, можно через результат вычисления других формул). Ограничения модели могут быть наложены как на диапазон варьирования самих переменных, так и на результаты вычисления других формул модели, зависящих от этих переменных. Все ячейки, содержащие переменные и ограничения модели должны быть расположены только на одном листе книги. Ввод параметров в диалоговом окне Поиска решения возможен только с этого листа. Целевая функция (ячейка) также должна быть расположена на этом листе. Но, промежуточные вычисления (формулы) могут быть размещены на других листах.

    Совет . Организуйте данные модели так, чтобы на одном листе MS EXCEL располагалась только одна модель. В противном случае, для выполнения расчетов придется постоянно сохранять и загружать настройки Поиска решения (см. ниже).

    Приведем алгоритм работы с Поиском решения , который советуют сами разработчики ( ]]> www.solver.com ]]> ):

    • Определите ячейки с переменными модели (decision variables);
    • Создайте формулу в ячейке, которая будет рассчитывать целевую функцию вашей модели (objective function);
    • Создайте формулы в ячейках, которые будут вычислять значения, сравниваемые с ограничениями (левая сторона выражения);
    • С помощью диалогового окна Поиск решения введите ссылки на ячейки содержащие переменные, на целевую функцию, на формулы для ограничений и сами значения ограничений;
    • Запустите Поиск решения для нахождения оптимального решения.

    Проделаем все эти шаги на простом примере.

    Простой пример использования Поиска решения

    Необходимо загрузить контейнер товарами, чтобы вес контейнера был максимальным. Контейнер имеет объем 32 куб.м. Товары содержатся в коробках и ящиках. Каждая коробка с товаром весит 20кг, ее объем составляет 0,15м3. Ящик — 80кг и 0,5м3 соответственно. Необходимо, чтобы общее количество тары было не меньше 110 штук.

    Данные модели организуем следующим образом (см. файл примера ).

    Переменные модели (количество каждого вида тары) выделены зеленым. Целевая функция (общий вес всех коробок и ящиков) – красным. Ограничения модели: по минимальному количеству тары (>=110) и по общему объему ( =СУММПРОИЗВ(B8:C8;B6:C6) – это общий вес всех коробок и ящиков, загруженных в контейнер. Аналогично рассчитываем общий объем — =СУММПРОИЗВ(B7:C7;B8:C8) . Эта формула нужна, чтобы задать ограничение на общий объем коробок и ящиков ( =СУММ(B8:C8) . Теперь с помощью диалогового окна Поиск решения введем ссылки на ячейки содержащие переменные, целевую функцию, формулы для ограничений и сами значения ограничений (или ссылки на соответствующие ячейки). Понятно, что количество коробок и ящиков должно быть целым числом – это еще одно ограничение модели.

    После нажатия кнопки Найти решение будут найдены такие количества коробок и ящиков, при котором общий их вес (целевая функция) максимален, и при этом выполнены все заданные ограничения.

    Совет : в статье " Поиск решения MS EXCEL. Экстремум функции с несколькими переменными. Граничные условия заданы уравнениями " показано решение задачи, в которой функция и граничные условия заданы в явном виде, т.е. математическими выражениями типа F(x1, x2, x3)=x1+2*x2+6*x3, что существенно облегчает построение модели, т.к. не требуется особо осмыслять задачу: можно просто подставить переменные x в поле переменные, а ограничения ввести в соответствующее поле окна Поиска решения.

    Резюме

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

    Поиску решения не удалось найти решения (Solver could not find a feasible solution)

    Это сообщение появляется, когда Поиск решения не смог найти сочетаний значений переменных, которые одновременно удовлетворяют всем ограничениям. Если вы используете Симплекс метод решения линейных задач , то можно быть уверенным, что решения действительно не существует. Если вы используете метод решения нелинейных задач, который всегда начинается с начальных значений переменных, то это может также означать, что допустимое решение далеко от этих начальных значений. Если вы запустите Поиск решения с другими начальными значениями переменных, то, возможно, решение будет найдено. Представим, что при решении задачи нелинейным методом, ячейки с переменными были оставлены не заполненными (т.е. начальные значения равны 0), и Поиск решения не нашел решения. Это не означает, что решения действительно не существует (хотя это может быть и так). Теперь, основываясь на результатах некой экспертной оценки, в ячейки с переменными введем другой набор значений, который, по Вашему мнению, близок к оптимальному (искомому). В этом случае, Поиск решения может найти решение (если оно действительно существует).

    Примечание . О влиянии нелинейности модели на результаты расчетов можно прочитать в последнем разделе статьи Поиск решения MS EXCEL (4.3). Выбор места открытия нового представительства .

    В любом случае (линейном или нелинейном), Вы должны сначала проанализировать модель на непротиворечивость ограничений, то есть условий, которые не могут быть удовлетворены одновременно. Чаще всего это связано с неправильным выбором соотношения (например, =) или граничного значения. Если, например, в рассмотренном выше примере, значение максимального объема установить 16 м3 вместо 32 м3, то это ограничение станет противоречить ограничению по минимальному количеству мест (110), т.к. минимальному количеству мест соответствует объем равный 16,5 м3 (110*0,15, где 0,15 – объем коробки, т.е. самой маленькой тары). Установив в качестве ограничения максимального объема 16 м3, Поиск решения не найдет решения.

    При ограничении 17 м3 Поиск решения найдет решение.

    Некоторые настройки Поиска решения

    Метод решения Рассмотренная выше модель является линейной, т.е. целевая функция (M – общий вес, который может быть максимален) выражена следующим уравнением M=a1*x1+a2*x2, где x1 и x2 – это переменные модели (количество коробок и ящиков), а1 и а2 – их веса. В линейной модели ограничения также должны быть линейными функциями от переменных. В нашем случае ограничение по объему V=b1*x1+b2*x2 также выражается линейной зависимостью. Очевидно, что другое ограничение — Максимальное количество тары (n) – также линейно x1+x2

    Ссылка на основную публикацию