Добавить материал и получить бесплатное свидетельство о публикации в СМИ
Эл. №ФС77-60625 от 20.01.2015
Свидетельство о публикации

Автоматическая выдача свидетельства о публикации в официальном СМИ сразу после добавления материала на сайт - Бесплатно

Добавить свой материал

За каждый опубликованный материал Вы получите бесплатное свидетельство о публикации от проекта «Инфоурок»

(Свидетельство о регистрации СМИ: Эл №ФС77-60625 от 20.01.2015)

Инфоурок / Информатика / Другие методич. материалы / Инструкционная карта. "MS EXCEL. Использование задачи оптимизации (поиска решения), связи между файлами и консолидации данных."
ВНИМАНИЮ ВСЕХ УЧИТЕЛЕЙ: согласно Федеральному закону № 313-ФЗ все педагоги должны пройти обучение навыкам оказания первой помощи.

Дистанционный курс "Оказание первой помощи детям и взрослым" от проекта "Инфоурок" даёт Вам возможность привести свои знания в соответствие с требованиями закона и получить удостоверение о повышении квалификации установленного образца (180 часов). Начало обучения новой группы: 28 июня.

Подать заявку на курс
  • Информатика

Инструкционная карта. "MS EXCEL. Использование задачи оптимизации (поиска решения), связи между файлами и консолидации данных."

библиотека
материалов

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ


Тема занятия: MS EXCEL. Использование задачи оптимизации (поиска решения), связи между файлами и консолидации данных.

Цель выполнения задания: научиться использовать задачи оптимизации при поиске решения в табличном редакторе.

Необходимо знать: основные приёмы работы файлами в табличном редакторе.

Необходимо уметь: совершать стандартные действия в табличном редакторе Excel.

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


ОСНОВНЫЕ ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ

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

Под термином консолидация подразумевается ряд стандартных операций с несколькими рабочими таблицами и рабочими книгами. В некоторых случаях консолидация может включать в себя создание связанных формул. Основной фактор, влияющий на консолидацию данных – это способ размещения информации в рабочих таблицах. Если размещение информации во всех таблицах одинаково, то говорят о консолидации по позиции. В том случае, когда размещение информации не идентично, но достаточно похоже, то можно объединить данные по заголовкам строк и/или столбцов. Такая консолидация называется консолидацией по категориям. Если же рабочие таблицы имеют мало общего друг с другом, то необходимо отредактировать листы, чтобы они стали единообразными.


ХОД ВЫПОЛНЕНИЯ ЗАДАНИЯ, МЕТОДИЧЕСКИЕ УКАЗАНИЯ

Задание 1. Задача о оптимальном ассортименте

Предприятие выпускает 2 вида продукции. Цена единицы 1 вида продукции – 25 000, 2 вида продукции – 50 000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57,6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице.

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

Выполнение.

1. Такие задачи решаются при помощи инструмента Excel «Поиск решения». Для установки этого инструмента необходимо :

Главное меню: Сервис / Надстройки / Установить флажок «Поиск решения» / OK.

После загрузки инструмента «Поиск решения» в меню Сервис появляется команда «Поиск решения». Выполнение этой команды начинается с вывода диалогового окна, в котором вводятся исходные данные задачи.

2. Математическая модель задачи.

Пусть продукция производится в количестве: 1-й вид – x1 единиц, 2-й вид – x2 единиц.

Тогда стоимость произведенной продукции выражается целевой функцией: f(x1,x2)=25000 x1+50000x2,

для которой необходимо найти максимум.

При этом следует учесть ограничения по запасам сырья:1,2 x1+1,9 x2 37,

2,3 x1+1,8 x2 57,6,

0,1 x1+0,7 x2 7

и по смыслу задачи x1, x2 должны быть неотрицательными и целыми: x10, x2 0.

3. Ввод исходных данных в компьютер.

3.1. Введем целевую функцию и ограничения.

Дhello_html_5910badb.pngля переменных x1,x2 определим соответственно ячейки С2:D2, и зададим им начальные значения, равные нулю. Затем коэффициенты целевой функции и нормы расхода сырья расположим под неизвестными в ячейках С3:D3 и С6:D8 соответственно. Запасы сырья расположим справа от матрицы норм расхода в ячейках G6:G8. В ячейке F2 вычислим значение целевой функции, а в ячейках F6:F8   реальный расход сырья.



F6

= СУММПРОИЗВ($C$2:$D$2;C6:D6)

F7

= СУММПРОИЗВ($C$2:$D$2;C7:D7)

F8

= СУММПРОИЗВ($C$2:$D$2;C8:D8)


3.2. Задание параметров для диалогового окна «Поиск решения».

Выполнить команду Сервис / Поиск решения.

В диалоговом окне «Поиск решения» нужно указать:

  • адрес ячейки, в которой находится формула, вычисляющая значение целевой функция;

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

  • адреса ячеек, в которых находятся значения изменяемых переменных х1, х2;

  • матрицу ограничений, для чего нажимается кнопка «Добавить»;

  • параметры решения задачи, для чего нажимается кнопка «Параметры».

Диалоговое окно «Поиск решения» и схема расположения исходных данных приведены ниже. Информация в этом окне соответствует решаемой задаче.

После ввода всех данных и задания параметров нажать кнопку «Выполнить».

Задание 2.

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

Филиал № 1

Название товара

Январь

Февраль

Март

А–995

110

10

20

В–123

10

10

20

А143

20

20

40

В–123

30

30

60

С–070

40

40

80

Д–060

60

60

120

Е–130

50

50

100

Ф–270

70

70

140

Т–234

120

20

20

М–235

11

11

24



Филиал № 2

Название товара

Январь

Февраль

Март

Т–234

10

10

20

В–123

10

10

20

Р–234

20

20

20

А143

20

40

40

В–123

30

30

60

С–070

40

40

80

Д–060

60

60

120

Е–130

50

20

100

Ф–270

70

70

140

У–111

40

40

45

К–254

30

20

45



Филиал № 3

Название товара

Январь

Февраль

Март

А–995

10

10

20

В–123

10

10

20

А143

20

20

40

Р–234

100

100

100

В–123

30

30

60

С–070

40

40

80

Д–060

60

60

120

Е–130

50

50

100

Ф–270

70

70

140

К–254

10

10

10

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

Выполнение.

Для выполнения данного задания необходимо:

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

  2. Создайте новую рабочую книгу (выберите новый рабочий лист), где должны размещаться результаты консолидации. Выполните команду Данные / Консолидация.

  3. Задание параметров для диалогового окна «Консолидация»

    1. В поле «Функция» укажите функцию Сумма, которая показывает тип объединения данных.

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

    3. Ведите ссылку на диапазон второй рабочей таблицы и добавьте ее к списку диапазонов. Выполните указанное действие для остальных диапазонов консолидации

    4. Так как способы размещения информации в рабочих таблицах различны, установим опции Подписи верхней строки и Значения левого столбца. В результате Excel будет подбирать данные по заголовкам.

    5. Для того, чтобы консолидация была динамической, установим опцию Создавать связи с исходными данными и нажмем. кнопку «ОК». В результате Excel создаст структуру, содержащую внешние ссылки.

  4. Построить требуемую диаграмму.

Задание 3.

Отредактировать исходные данные первого задания так, чтобы диапазоны консолидации стали идентичными. Провести консолидацию этих данных:

  • используя формулы, содержащие внешние ссылки. Для задания внешней ссылки используется формат:

=[Имя_рабочей_книги]Имя_листа!Адрес_ячейки

Если имя рабочей книги или имя листа содержит один или более пробелов, то такое имя нужно заключить в апострофы. Например:

=’[Бюджет на 2001 год]Лист1’!A1

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

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

  • с помощью команд Данные / Консолидация.


?Контрольные вопросы:

  1. Какие производится поиск решения в табличном редакторе Excel?

  2. Какую функцию выполняет кнопка ПАРАМЕТРЫ?

  3. Под термином консолидация понимается ….

  4. Как создать ссылки?


Литература:

  1. Задачник практикум Информатика и ИКТ под. Редакцией И. Семакина, Е. Хеннера, Москва, 2010

  2. Ефимова О.В., Моисеева М.В., Ю.А. Шафрин Практикум по компьютерной технологии. Примеры и упражнения. Пособие по курсу «Информатика и вычислительная техника» - Москва: ABF,2007

  3. Горячев А., Шафрин Ю. Практикум по информационным технологиям. М.: Лаборатория базовых знаний, 2011

  4. Семакин И.Г., Шеина Т.Ю. Преподавание курса информатики в средней школе. М.: Лаборатория базовых знаний, 2002



Подайте заявку сейчас на любой интересующий Вас курс переподготовки, чтобы получить диплом со скидкой 50% уже осенью 2017 года.


Выберите специальность, которую Вы хотите получить:

Обучение проходит дистанционно на сайте проекта "Инфоурок".
По итогам обучения слушателям выдаются печатные дипломы установленного образца.

ПЕРЕЙТИ В КАТАЛОГ КУРСОВ

Автор
Дата добавления 31.10.2016
Раздел Информатика
Подраздел Другие методич. материалы
Просмотров113
Номер материала ДБ-304321
Получить свидетельство о публикации
Похожие материалы

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