Файл: Лабораторная работа использование статистических, математических и текстовых функций.doc

Примените любой из финансовых долларовых форматов для оформления ячеек F5:F18.
Вычислите, сколько студентов – юбиляров в вашей таблице. Используйте функцию СЧЕТЕСЛИ.
Вычислите, сколько студентов в вашей таблице моложе 24 лет. Используйте функцию СЧЕТЕСЛИ.
Вычислите, сколько студентов в вашей таблице старше 25 лет. Используйте функцию СЧЕТЕСЛИ.

ЗАДАЧА 6

Построение и редактирование диаграмм.

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

Формулы для определения месячных показателей:

Доход = Цена товара * Реализовано

Себестоимость = Постоянные издержки +Переменные издержки * Реализовано

Прибыль = Доход – Себестоимость

Рабочему листу с таблицей дайте имя Пример. Сохраните созданную таблицу под именем Dohod.xls.
ПОСТРОЕНИЕ ДИАГРАММ

1. Внедренная диаграмма. Построить внедренную диаграмму, отражающую объем реализации по месяцам, тип диаграммы – круговая объемная. Для удобства построения установить масштаб просмотра 75%.

Рисунок 6 – Пример таблицы
Решение задачи:

Выделить диапазон ячеек А7:D8 ( по данным, содержащимся в этих ячейках, должна быть построена диаграмма);
Щелкнуть на кнопке Мастер диаграмм панели инструментов Стандартная;
Переместить курсор мыши в поле таблицы;
Указать область таблицы для размещения диаграммы в диапазоне ячеек А13:Е23. Для этого нужно поместить курсор в ячейку А13, нажать левую клавишу мыши и, не отпуская ее, переместить курсор мыши в ячейку Е23, после чего отпустить левую клавишу мыши;
Далее следовать указаниям Мастера диаграмм.

В этом примере перед вызовом Мастера диаграмм выделялся диапазон данных с информацией

, на основе которой строилась сама диаграмма. Возможна и обратная последовательность действий.
ЗАДАЧА 7. Построить внедренную диаграмму, отражающую объем реализации по месяцам, тип диаграммы – график. Для удобства построения установить масштаб просмотра – 75%.

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

Щелкнуть на кнопке Мастер диаграмм панели инструментов Стандартная;
Указать область таблицы для размещения диаграммы в диапазоне F2:J18;
В появившемся окне Мастера диаграмм необходимо задать диапазон ячеек $А$7:$D$8;
Далее следовать указаниям Мастера диаграмм.

ЗАДАЧА 8. Построить диаграмму, отражающую объем реализации по месяцам на отдельном листе, тип диаграммы – гистограмма.

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

Выделить диапазон ячеек А7:D8;
Нажать клавишу F11. В результате диаграмма будет помещена на отдельном листе – листе диаграмм. Имя листа по умолчанию Диаграмма1, каждый последующий лист диаграмм будет содержать слово Диаграмма, но номер будет увеличиваться с шагом 1. Переименуйте этот лист, дав ему имя Гистограмма.

ЗАДАЧА 9. Построить диаграмму, отражающую объем реализации по месяцам, тип диаграммы – круговая объемная.

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

Выделить диапазон ячеек А7:D8;
Выбрать пункт меню Вставка, команда Диаграмма, а в открывшемся подменю выполнить команду На новом листе;
После появления окна Мастер диаграмм выполнить необходимые действия.
Переименуйте этот лист, дав ему имя Круговая.

ЗАДАЧА 10. Для внедренной диаграммы (объемная круговая) изменить тип на плоскую круговую.

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

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

ЗАДАЧА 11. Для внедренной линейчатой диаграммы изменить Автоформат.

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

Перейти в режим редактирования внедренной диаграммы;
Выполнить команду Автоформат в разделе Форматы выбрать значок под номером 2;
Щелкнуть на клавише ОК или нажать клавишу ENTER.

РЕДАКТИРОВАНИЕ ДИАГРАММ

Редактирование диаграмм (внедренных или на отдельном листе) производится по одним правилам. Сначала надо перейти на лист с нужной диаграммой или перейти в режим редактирования внедренной диаграммы. Затем необходимо произвести редактирование или форматирование нужного элемента диаграммы (они также называются термином объект). Для этого можно использовать 2 способа:

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

ЗАДАЧА 12. Для диаграммы на листе Гистограмма изменить тип диаграммы на гистограмму и установить вариант Автоформата с разноцветными полосами. Изменить название диаграммы на «Объем реализации за 1 квартал». Добавить подписи данных к столбцу диаграммы Февраль. Отформатировать текст на диаграмме.

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

Щелкнуть на одном из столбцов диаграммы. При этом на каждом из столбцов появится квадратик – признак выделения всех столбцов;
В контекстном меню Формат рядов включить подписи данных.
Оформить текст названия диаграммы шрифтом вида Times New Roman, стиль – жирный, размер – 14 пт. Текст обвести рамкой.

ЗАДАЧА 13. Построить смешанную внедренную диаграмму, отражающую доход и себестоимость за 1 квартал. Построенная смешанная диаграмма представляет собой комбинацию из диаграммы и графика.

ЗАДАЧА 14

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

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

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

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

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

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

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

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

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

Выделите ячейку В6;
Выбрать команду Сервис – Подбор параметра, появится диалоговое окно;
В поле Установить в ячейке введите $B$6;
В поле Значение введите 5000;
В поле Изменяя ячейку введите $B$5.

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

ЗАДАЧА 15

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

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

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

Создайте на листе Транспортные расходы таблицу (рис. 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