|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
ПРЕДСТАВЛЕНИЕ РЕЗУЛЬТАТОВ В ГРАФИЧЕСКОМ ВИДЕ
Выделите ячейку. Щелкните на панели инструментов Вставка кнопку Создать диаграмму. Появится диалоговое окно Мастера диаграмм. Рассмотрим построение гистограммы. Выберите тип диаграммы – Гистограмма, а также вид гистограммы. Нажмите кнопку ОК. Далее выбирается Диапазон данных и произойдет переход в режим Таблица, выделите диапазон данных. Установите флажок, что данные находятся в строках/столбцах и напишите при необходимости их названия. Далее создаются подписи для осей, формат данных, шрифт и остальные функции форматирования. Для упрощения работы с диаграммами существуют их макеты, в которые уже внесены многие нужные составляющие диаграммы и ее отображения. РАБОТА С ФУНКЦИЯМИ Работа с текстовыми функциями. ДЛСТР возвращает количество знаков в текстовой строке. ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста. ЛЕВСИМВ возвращает указанное число знаков с начала текстовой строки. Функция НАЙТИ находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста. Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке. ПОИСК Возвращает позицию первого вхождения знака или текстовой строки при поиске слева направо, начиная с нач_позиция. ПРАВСИМВ возвращает заданное число последних знаков текстовой строки. ПРОПИСН Делает все буквы в тексте прописными. ПСТР возвращает указанное число знаков из текстовой строки, начиная с указанной позиции. СТРОЧН Преобразует знаки в текстовой строке из верхнего регистра в нижний. СЦЕПИТЬ Объединяет несколько текстовых строк в одну. Работа с финансовыми функциями. При задании аргументов финансовых функций следует помнить следующее. Все аргументы, означающие расходы денежных средств (например, ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления (например, дивиденды) - положительными числами. Все даты как аргументы функции имеют числовой формат. Для логических аргументов используются константы ИСТИНА или ЛОЖЬ, ли функции категории Логические.
ЗАДАЧИ НА ПОДБОР ПАРАМЕТРА Средство Подбор параметра – инструмент, предназначенный для вычисления входного значения некоторой формулы для того, чтобы формула возвращала требуемый результат. Пример. В ячейку А1 введите 10 – расстояние в милях. В ячейку А2 введите формулу =ПРЕОБР(А1; ''mi''; ''m'')/1000, преобразующую значение милей в километры. В ячейке А2 должно быть число 16, 09. Сколько миль будет соответствовать 20 километрам? Можно подобрать нужное значение, последовательно вводя в ячейку А1 значения 10, 11, 12 и т.д., пока в ячейке А2 не отобразится число 20 (или близкое к нему). ý Такой подбор нерационален. Задачу можно быстро решить, используя средство Подбор параметра. Средство Подбор параметра применяется тогда, когда вы знаете значение, которое должна возвращать формула, но не знаете входное значение для формулы, обеспечивающее желаемый результат вычислений. Чтобы применить средство Подбор параметра, выберите закладку в основном меню Данные " Подбор параметра. Откроется диалоговое окно, в которое надо заполнить все поля ввод, а затем щелкнуть на кнопке ОК. В диалоговом окне Подбор параметра нужно заполнить всего три поля ввода: Установить в ячейке, Значение, Изменяя значения ячейки. В поле Установить в ячейке введите адрес ячейки, содержащей формулу, для результата вычислений которой вы хотите задать значение. В поле Значение введите число, которое вы хотите увидеть в результате вычисления формулы. В поле Изменяя значение ячейки введите адрес ячейки, содержащей числовое значение, которое вы хотите подобрать. В результате появится диалоговое окно Результат подбора параметра. В диалоговом окне Результат подбора параметра отображается два числа: Подбираемое значение ( это значение, которое вы указали в поле Значение ) и Текущее значение (значение, которое Excel смогла добиться от формулы, указанно в поле Установить в ячейке ). Если числа Подбираемое значение и Текущее значение совпадают, то задача решена. Вернитесь к примеру. Подберите значение расстояния в километрах, чтобы расстояние в милях было равно 20. Порядок действия. 1. Данные " Подбор параметра. 2. В поле Установить в ячейке – А2. 3. В поле Значение – 20. 4. В поле Изменяя значение ячейки - А1. 5. Щелкните кнопку ОК.
ЗАДАЧИ НА ПОДВЕДЕНИЕ ПРОМЕЖУТОЧНЫХ ИТОГОВ Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Синтаксис функции: ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции, ссылка1, [ссылка2],...])
· Номер_функции Обязательный. Число от 1 до 11 (с включением скрытых значений) или от 101 до 111 (с исключением скрытых значений), которое указывает, какую функцию следует использовать при вычислении итогов внутри списка.
· Ссылка1 Обязательный. Первый именованный диапазон или ссылка, для которых требуется вычислить промежуточные итоги. · Ссылка2... Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги. Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Если уже имеются формулы подведения итогов внутри аргументов " ссылка1; ссылка2;..." (вложенные итоги), то эти вложенные итоги игнорируются, чтобы избежать двойного суммирования. Дополнительную информацию можно прочитать в справке Excel.
СВОДНЫЕ ТАБЛИЦЫ
Сводные таблицы предназначены для обобщения (объединения, переработки) информации, хранящейся в базе данных. Они также позволяют отображать табличные данные в виде двух мерной или трехмерной таблицы. Кроме того, с их помощью можно вывести промежуточные итоги с любым уровнем детализации. Каждая сводная таблица состоит из 4 областей: страница, строка, столбец, данные. Назначение областей следующее: · Cтрока. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков строк в сводной таблице. Если в эту область помещено одно поле, то количество строк в сводной таблице (без учета итогов) равно числу уникальных значений этого поля. · Столбец. Уникальные значения полей, помещенных в эту область, используются в качестве заголовков столбцов в сводной таблице. Если в эту область помещено одно поле, то количество столбцов в сводной таблице (без учета итогов) равно числу уникальных значений этого поля. · Данные. Значения полей, помещенных в эту область, используются для заполнения ячеек сводной таблицы итоговыми данными (суммирование, подсчет количества, вычисление среднего значения и т. д.). · Страница. Уникальные значения полей, помещенных в эту область, и элемент «все» используются для построения раскрывающихся списков. В поле страницы можно выбрать только одно значение в каждом из списков. В области данных будут отображены итоговые данные, для выбранного значения. Использование этого элемента сводной таблицы позволяет, в некоторой мере, реализовать отображение трехмерной таблицы. Пример задачи 1. На основании следующей таблицы:
Построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции разными исполнителями по месяцам в разрезе регионов; Выполнение. Рассчитайте значение поля «Прибыль», записав соответствующую формулу. Выполните команду Данные / Сводная таблица. Установите флажок – В списке или базе данных Microsoft Excel; Укажите диапазон, содержащий построенный список. Если список был построен правильно, нужный диапазон будет выбран автоматически.
Перетащите кнопки «Продукция» и «Менеджер» в область « Строка ». При этом важен порядок перетаскивания – поле «Менеджер» будет вложенным по отношению к полю «Продукция». Затем в область « Столбец » перетащите кнопку « Месяц » и в область страниц – кнопку « Регион ». В область данных перетащите кнопку «Прибыль». Построенная сводная таблица будет иметь следующий вид как указано на рисунке. Пример задачи 2. На основании построенного списка построить таблицу, показывающую объем прибыли полученной от продажи разных видов продукции разными исполнителями по кварталам в разрезе регионов; Выполнение. Скопируйте сводную таблицу задания 1 на второй лист. Отметьте диапазон С4: Е15. Для этого достаточно сделать текущей ячейку С4 (выделится столбец сводной таблицы за январь месяц) и нажав клавишу « Shift » щелкнуть по ячейке E4. Выполните команды « Данные » « Группа и структура » « Группировать ». В поле столбца появиться новое поле « Месяц 2 » и в сводную таблицу добавится строка, в которой для выделенных трех столбцов присвоится название « Группа 1 ». Выполните аналогичные действия для столбцов сводной таблицы за апрель и май месяцы. Для этих столбцов должно появится название « Группа 2 ». Удалите поле «месяц». Для этого вызовите контекстное меню или перетащите его из области сводной таблицы. Исправьте название « Месяц 2 » на « Квартал » (дважды щелкните мышкой по названию), « Группа 1 » – на « Первый » (просто введите новое название в ячейку), « Группа 2 » – на « Второй ». Полученная таблица должна иметь следующий вид:
ПОИСК РЕШЕНИЯ Средство Поиск решения позволяет находить для одной формулы такие значения ее входных переменных, которое приводили бы к точно заданному значению, либо минимально или максимально возможному значению. Это средство позволяет также налагать ограничения на значения любых переменных, от которых зависит результат вычисления формулы. Средство поиска решения является надстройкой (Надстройка. Вспомогательная программа, служащая для добавления в Microsoft Office специальных команд или возможностей.) Microsoft Office Excel, которая доступна при установке Microsoft Office или Microsoft Excel. Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее. 1. Нажмите кнопку Microsoft Office 2. Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel. 3. Нажмите кнопку Перейти. 4. В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК. Совет: Если Поиск решения отсутствует в списке поля Доступные надстройки, чтобы найти надстройку, нажмите кнопку Обзор. В случае появления сообщения о том, что надстройка для поиска решения не установлена на компьютере, нажмите кнопку Да, чтобы установить ее. 5. После загрузки надстройки для поиска решения в группе Анализ на вкладки Данные становится доступна команда Поиск решения.
Пример задачи 1 (производственные расчеты). Вычислим оптимальные цены и количество театральных билетов разной категории таким образом, чтобы стоимость всех проданных билетов достигала определенной величины. В театре имеются билеты трех категорий: детские, для взрослых и льготные. Найдем, сколько необходимо продать театральных билетов для того, чтобы в кассе оказалось ровно 240 тыс. руб., при выполнении следующих ограничений:
Рабочий лист выглядит так:
Параметры окна Поиск решения:
Пример задачи 2. Задача об оптимальном ассортименте Предприятие выпускает 2 вида продукции. Цена единицы 1 вида продукции – 25 000, 2 вида продукции – 50 000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57, 6 и 7 условных единиц. Нормы затрат каждого сырья на единицу продукции представлены в следующей таблице.
Требуется определить плановое количество выпускаемой продукции таким образом, чтобы стоимость произведенной продукции была максимальной Выполнение. Математическая модель задачи. Пусть продукция производится в количестве: 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 должны быть неотрицательными и целыми: x1³ 0, x2 ³ 0. Пример задачи 3. Сетевая транспортная задача На складах имеется груз, количество которого определяется в следующей таблице:
Этот груз необходимо перевезти в пункты назначения в соответствии с таблицей:
Стоимость перевозок определяется таблицей:
Необходимо составить план перевозок так, чтобы стоимость перевозок была минимальной. Пример задачи 4. Задача на составление диеты Необходимо составить диету, состоящую из двух продуктов А и Б. Дневное питание этими продуктами должно давать не более 14 единиц жира, но и не менее 300 калорий. В одном килограмме продукта А содержится 15 единиц жира и 150 калорий, а в одном килограмме продукта Б - 4 единицы жира и 200 калорий. При этом цена одного килограмма продукта А равна 15 у.е., а цена одного килограмма продукта Б - 25 у.е. Какое количество продуктов в день необходимо употреблять для соблюдения диеты, чтобы вложенные средства были минимальны? Выполнение Математическая модель задачи Пусть потребление продуктов составляет количество Вид А – x1 кг, вид В – x2 кг. Затраты на продукты составят: F=15x1+25x2, которые должны быть минимальными. Ограничение на калорийность: 15x1+4x2£ 14 150x1+200x2³ 300 и по смыслу задачи x1, x2 должны быть неотрицательными: x1³ 0, x2 ³ 0. Задачи на линейную оптимизацию также решаются с помощью надстройки «поиск решения».
Изучение рынка сбыта показало, что суточный спрос на материал В никогда не превышает спроса на материал А более чем на 1 т. Кроме того, установлено, что спрос на материал А никогда не превышает 2 т в сутки. Оптовые цены одной тонны материалов равны: 3000 у.е. для В и 2000 у.е. для А. Какое количество материала каждого вида должна производить фабрика, чтобы доход от реализации был максимальным? Выполнение Построение математической модели задачи: Формулировка математической модели задачи: 1) переменные для решения задачи. x1 - суточный объем производства материала А, Х2 - суточный объем производства материала В; 2) определение функции цели (критерия оптимизации). Суммарная суточная прибыль от производства X1 материала А и x2 материала В равна: поэтому цель фабрики - среди всех допустимых значений х1 и х2 найти такие, которые максимизируют суммарную прибыль от производства материалов. ограничения на переменные: а) объем производства материалов не может быть отрицательным, т.е.
в) ограничения на величину спроса на материалы: Таким образом получаем следующую математическую модель задачи: Найти максимум функции: При ограничениях: Подготовка рабочего листа На рабочий лист вводим необходимый текст, данные и формулы:
Пример задачи 5. Определение состава удобрений. Для получения удобрений видов 1 и 2 используются химических вещества А, В, С и D, требования, к содержанию которых в минералах, приведены в таблице 1.
Характеристики и запасы минералов, используемых для производства химических веществ А, В, С и D, указаны в следующей таблице 2.
Цена 1 т удобрения вида 1 равна 200 у.е., а 1 т удобрения вида 2 - 210 у.е. Необходимо максимизировать прибыль от продажи удобрений видов 1 и 2. Выполнение: Математическая модель задачи: Пусть: xA1, xB1, xC1, xD1 – количество химических веществ A, B, C, D, используемых для получения удобрений вида 1. xA2, xB2, xC2, xD2 – количество химических веществ A, B, C, D, используемых для получения удобрений вида 2. Yi, i=1, 2, 3 – количество используемого i-го минерала. Математическая модель данной задачи будет иметь вид: Найти максимум функции: F=200(xA1 + xB1 + xC1 + xD1)+210(xA2 + xB2 + xC2 + xD2)-30y1-40y2-50y3. При ограничениях: А) На состав вида удобрений (таблица 1): Б) На характеристики минералов (таблица 2): Таблица 1 Таблица 2
Подготовка рабочего листа:
Примеры задач о назначениях: Пример задачи 6. Четверо рабочих могут выполнять четыре вида работ. Стоимости су выполнения i-м рабочим j-й работы приведены в таблице. Составить план выполнения работ так, чтобы все работы были выполнены, каждый рабочий был загружен только на одной работе, а суммарная стоимость выполнения работ была минимальной.
Решение. 1. Проверка задачи на сбалансированность. Задача является сбалансированной, т.к. количество рабочих соответствует числу возможных работ. В случае несбалансированности задачи необходимо ввести недостающее число фиктивных рабочих (строчек) или работ (столбцов). 2. Построение математической модели задачи. Пусть xij=1, случае выполнения i-м рабочим j-й работы, и xij=0 - в случае невыполнения работы. Тогда математическая модель задачи примет вид: найти минимум функционала при следующих ограничениях: 3. Решение задачи с помощью надстройки Поиск решения . Подготовка рабочего листа может быть произведена в соответствии с рисунком
Пример решения транспортной задачи: Пример задачи 7. Производство продукции осуществляется на 4-х предприятиях, а затем развозится в 5 пунктов потребления. Предприятия могут выпускать в день 200, 150, 225 и 175 единиц продукции. Пункты потребления готовы принимать ежедневно 100, 200, 50, 250 и 150 единиц продукции Стоимость перевозки единицы продукции с предприятий в пункты потребления приведена в таблице:
Необходимо минимизировать суммарные транспортные расходы по перевозке продукции. Решение: 1. Проверка сбалансированности модели задачи. Модель является сбалансированной, т.к. суммарный объем производимой продукции в день равен суммарному объему потребности в ней: 200+150+225+175=100+200+50+250+150. Поэтому при решении этой задачи не учитываются издержки, связанные со складированием и недопоставкой продукции. 2. Построение математической модели. Объемы перевозок - это неизвестные задачи. Пусть хij - объем перевозок с i-го предприятия в j-й пункт потребления. Суммарные транспортные расходы - это критерий цели: Неизвестные в данной задаче должны удовлетворять следующим ограничениям: объемы перевозок не могут быть отрицательными; т.к. модель сбалансирована, то вся продукция должна быть вывезена с предприятий, а потребности всех пунктов потребления должны быть полностью удовлетворены.
Популярное:
|
Последнее изменение этой страницы: 2016-05-03; Просмотров: 2054; Нарушение авторского права страницы
Укажите, начиная с какой ячейки текущего листа должна располагаться сводная таблица.
, а затем щелкните Параметры Excel. 
Цена детских билетов постоянна и равна 6000
Пример задачи 4. Фирма выпускает два типа строительных материалов: А и В. Продукция обоих видов поступает в продажу. Для производства материалов используются два исходных продукта I и II. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн соответственно. Расходы продуктов I и II на 1 тонну соответствующих материалов приведены в таблице
.
б) расход исходного продукта для производства обоих видов материалов не может превосходить максимально возможного заданного исходного продукта, т.е. 
.
Работа с надстройкой Поиск Решения – вызовите окно «Поиск решения» (см. в теории).
Работа с надстройкой Поиск решения 
Установка ограничений в окне Поиск решения (в окне Параметры поиска решения необходимо также установить флажок Линейная модель) и результат поиска решенияприведены на рисунке
, где сij— стоимость перевозки единицы продукции с i-го предприятия в j-й пункт потребления.
3. Решение задачи с помощью окна Поиск решения. Подготовка листа рабочей книги.
