Решение задач прикладной информатики в менеджменте icon

Решение задач прикладной информатики в менеджменте



НазваниеРешение задач прикладной информатики в менеджменте
Дата конвертации14.09.2012
Размер136.38 Kb.
ТипРешение



Практикум по Excel-2. Занятие 1

Решение задач прикладной информатики в менеджменте.

Практическое занятие 1.

Статистический анализ данных.



Цель работы:

  1. Освоение приемов работы с функциями массивов (табличными функциями).

  2. Изучение элементарных статистических функций Excel




  • Формулы массивов (табличные формулы)

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

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

Рассмотрим работу с использованием массивов на следующем примере. Требуется определить прибыль для каждого, представленного в таблице 1.1 года деятельности предприятия.

^ Таблица 1 1. Пример использования функция массива.




A

B

C

D

1

Год

Приход

Расход

Прибыль

2

2005

200

150

{B2:B5-C2:C5}.

3

2006

360

230

{B2:B5-C2:C5}.

4

2007

410

250

{B2:B5-C2:C5}.

5

2008

200

180

{B2:B5-C2:C5}.


Выделим блок D2:D5, активная ячейка при этом D2. Начнем ввод формулы – наберем знак =.
Выделим блок B2:B5, наберем знак минус -, выделим блок С2:С5. Ввод формул массива заканчивается комбинацией клавиш Ctrl+Shift+Enter. После нажатия такой комбинации во всех ячейках блока D2:D5 появится формула {B2:B5-C2:C5}.

Основные правила работы с формулами массива:

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

  • фигурные скобки, отмечающие формулу массива, вводятся при завершении ввода формулы клавишами Ctrl+Shift+Enter, если фигурные скобки ввести вручную, такой ввод будет воспринят Excel как текст.

  • для редактирования формулы массива необходимо выделить блок, активировать строку формул, внести изменения и завершить редактированием клавишами Ctrl+Shift+Enter;

  • блок ячеек может указываться присвоенным ему именем (клавиша F3 и выбор имени в диалоге «Вставка имени»;

  • массив исходных данных и массив результатов могут быть многомерными, т.е. включать несколько строк и столбцов.



  • ^ Функции Excel, используемые для статистического анализа

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

В категорию статистических функций Excel входит около 80 функций, кроме того, значительное число функций статистического анализа входят в надстройку «Пакет анализа».

Для выполнения задания потребуются статистические функции, полное описание которых приведено ниже.

  • МАКС(число1;число2; ...) - возвращает наибольшее значение из набора значений.
    Число1, число2,...— от 1 до 30 чисел, среди которых требуется найти наибольшее.
    Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок.
    Если аргумент является массивом или ссылкой, то в нем учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА. Если аргументы не содержат чисел, то функция МАКС возвращает 0 (ноль);

  • МИН(число1;число2; ...) - возвращает наименьшее значение из набора значений, в остальном полностью аналогична функции ^ МАКС;

  • СРЗНАЧ(число1; число2; ...) - возвращает среднее (арифметическое) своих аргументов.
    Число1, число2, ... — это от 1 до 30 аргументов, для которых вычисляется среднее.
    Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
    Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются;

  • ТЕНДЕНЦИЯ(известные_значения_y; известные_значения_x; новые значения_x; конст) - возвращает значения в соответствии с линейным трендом, т.е. аппроксимирует прямой линией (по методу наименьших квадратов) массивы ”звестные_значения_y” и “известные_значения_x”. Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x.
    Известные_значения_y — множество значений y, которые уже известны для соотношения y = mx + b.
    Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная.
    Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.
    Известные_значения_x — необязательное множество значений x, которые уже известны для соотношения y = mx + b.
    Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность.
    Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец).
    Если известные_значения_x опущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.
    Новые_значения_x — новые значения x, для которых ТЕНДЕНЦИЯ возвращает соответствующие значения y. Новые_значения_x должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_x. Таким образом, если известные_значения_y — это один столбец, то известные_значения_x и новые_значения_x должны иметь такое же количество столбцов. Если известные_значения_y — это одна строка, то известные_значения_x и новые_значения_x должны иметь такое же количество строк.
    Если новые_значения_x опущены, то предполагается, что они совпадают с известные_значения_x.
    Если опущены оба массива известные_значения_x и новые_значения_x, то предполагается, что это массив {1;2;3;...} такого же размера, что и известные_значения_y.
    ^ Конст — логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.
    Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом.
    Если конст имеет значение ЛОЖЬ, то b полагается равным 0, и значения m подбираются таким образом, чтобы выполнялось соотношение y = mx.

  • РОСТ(известные_значения_y;известные_значения_x;новые_значения_x; конст) - возвращает значения y для последовательности новых значений x, задаваемых с помощью существующих x- и y-значений, т.е. функция рассчитывает прогнозируемый экспоненциальный рост на основании имеющихся данных.

    Известные_значения_y — это множество значений y, которые уже известны в соотношении y = b*m^x. Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная. Если массив известные_значения_y имеет одну строку, то каждая строка массива известные_значения_x интерпретируется как отдельная переменная.

    Известные_значения_x — это необязательное множество значений x, которые уже известны для соотношения y = b*m^x. Если массив известные_значения_y имеет один столбец, то каждый столбец массива известные_значения_x интерпретируется как отдельная переменная. Массив известные_значения_x может содержать одно или несколько множеств переменных. Если используется только одна переменная, то известные_значения_y и известные_значения_x могут иметь любую форму, при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то известные_значения_y должны быть вектором (то есть интервалом высотой в одну строку или шириной в один столбец). Если известные_значения_x опущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.
    Новые_значения_x — это новые значения x, для которых РОСТ возвращает соответствующие значения y. Новые_значения_x должны содержать столбец (или строку) для каждой независимой переменной, как и известные_значения_x. Таким образом, если известные_значения_y — это один столбец, то известные_значения_x и новые_значения_x должны иметь такое же количество столбцов. Если известные_значения_y — это одна строка, то известные_значения_x и новые_значения_x должны иметь такое же количество строк. Если аргумент новые_значения_x опущен, то предполагается, что он совпадает с аргументом известные_значения_x. Если оба аргумента известные_значения_x и новые_значения_x опущены, то предполагается, что это массив {1;2;3;...} такого же размера, как и известные_значения_y.

    ^ Конст — это логическое значение, которое указывает, требуется ли, чтобы константа b была равна 1. Если конст имеет значение ИСТИНА или опущено, то b вычисляется обычным образом. Если конст имеет значение ЛОЖЬ, то b полагается равным 1, а значения m подбираются так, чтобы y = m^x.

Замечания. 1) Формулы, которые возвращают массивы, должны быть введены как формулы массива.

2) При вводе константы массива для аргумента, такого как известные_значения_x, следует использовать точку с запятой для разделения значений в одной строке и двоеточие для разделения строк.



  • Задание

Для приведенных в таблице 1-2 данных о реализации туристической фирмой туров [1] вычислить:

  • минимальные, максимальные и среднее показатели по каждому кварталу;

  • средние показатели по каждому туру;

  • вычислить средний доход по всей фирме за отчетный период;

  • дать оценку каждому туру: «хорошо», если доход от тура превышает средний по фирме, и «плохо», если доход от тура меньше среднего по фирме;

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

  • оценить относительные отклонения для среднего значения и «Тенденции», для среднего значения и «Роста».

^ Таблица 1 2. Исходные данные и представление результатов.



    A

    B

    C

    D

    E

    F

    G

    1

    Тур

    1 кв.

    2 кв.

    3 кв.

    4 кв.

    Ср.по туру

    Оценка

    2

    Швеция

    1500

    2000

    6000

    8000





    3

    Дания

    1400

    5000

    4100

    5000





    4

    Норвегия

    3600

    3600

    3000

    4500





    5

    Финляндия

    1100

    1045

    9100

    7800





    6

    Германия

    3850

    3650

    7800

    11000





    7

    Польша

    6800

    7250

    8122

    9450





    8

    Чехия

    6590

    7050

    6400

    6440





    9

    Словакия

    930

    3970

    4512

    4600





    10

    Венгрия

    8912

    7490

    3570

    8000





    11

    Болгария

    3590

    3800

    5464

    5954





    12

    Мин













    13

    Мах













    14

    Среднее













    15



    1

    2

    3

    4

    5

    6

    16

    Тенденция по средн.













    17

    Рост по средн.













    18

    Погрешность
    тенденции













    19

    Погрешность
    роста















  • Указания по выполнению задания

    1. Минимальные, максимальные и средние значения по кварталам и средние значения по турам подсчитываются с помощью Мастера функций.

    2. Для оценки тура используется среднее значение дохода по фирме и функция ЕСЛИ().

    3. Функция Тенденция показывает динамику изменения данных и позволяет получить прогноз на будущее. При этом изменение данных описывается линейным уравнением. Для определения Тенденции:

      1. Выделить новый диапазон ячеек для размещения результатов (B16:E16);

      2. В
        строке формул вставить функцию Тенденция и в Мастере функций в поле аргумента известные_значения_y указать диапазон средних по кварталу значений (см. рис.1-1)


Рисунок 1-1. Мастер функций, ввод значений известных_Y функции Тенденция




      1. Известные_значения_x можно не устанавливать, т.к. это 1,2,3, 4 кварталы.

      2. Выйти из Мастера функций – Ok.

      3. Установить курсор в строке формул, нажать комбинацию клавиш Ctrl+Shift+Enter, в выделенном новом массиве появятся результаты.

    1. Функция Тенденция показывает линейную модель изменения показателей, экспоненциальная модель строится функцией Рост.

    2. Самостоятельно вычислите функцию Рост для средних по кварталам, подобно тому, как вычислялась функция Тенденция.

    3. Вычислить прогноз развития событий на ближайшие два квартала, используя функцию Тенденция:

      1. Справа от ячейки со значением Тенденция для 4-го квартала выделить две свободные ячейки.

      2. .Вставить функцию Тенденция и в Мастере функций указать:
        в поле известные_значения_y вычисленные ранее значения Тенденция за четыре квартала (диапазон B16:E16);

в поле новые_значения_x – диапазон F15:G15 – кварталы 5 и 6, для которых выполняется прогноз.

      1. Завершить работу Мастера – Ok, завершить ввод функции массива Ctrl+Shift+Enter, в выделенных ячейках появятся предсказанные по линейной модели значения для 5 и 6 кварталов.

    1. Таким же образом рассчитать прогноз по экспоненциальной модели с помощью функции Рост.

    2. Оценить относительные отклонения в процентах для среднего значения и Тенденции, для среднего значения и Роста (для каждого из четырех кварталов) по формуле:

Относительное отклонение=(yфакт - yмодели)/yмодели,
где yфакт - среднее значение;

yмодели – значение, определенное с помощью Тенденции или Роста.

  • Дополнительные задания

    1. В строках 25-27 рабочего листа выполнить самостоятельно функцию Тенденция для туров «Польша», «Чехия» и «Болгария».

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




www.alural.narod.ru/inform/intro.htm Александр Ю. Алексеев






Похожие:

Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
Цель работы: построение простейшей математической модели и исследование ее параметров
Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
Цель работы: построение простейшей математической модели и исследование ее параметров
Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
Необходимо определить, сколько путевок и на какие туры турагентство должно приобрести и реализовать, чтобы получить максимальную...
Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
Необходимо минимизировать транспортные расходы по доставке туристов, прибывающих различными видами транспорта, в пять гостиниц, расположенных...
Решение задач прикладной информатики в менеджменте iconБиблиографический список
Решение задач прикладной информатики в менеджменте туризма на Excel / Ф. А. Гурьянова, Л. А. Родигин, А. И. Сеселкин; Под общ ред....
Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
По результатам предварительного тестирования претендентов необходимо так отобрать претендентов, чтобы сумма баллов отобранных претендентов...
Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
Это позволяет легко выполнить анализ «что-если», т е исследовать влияние исходных данных на результат. Excel расширяет возможности...
Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
Необходимо найти кратчайшее расстояние между пунктом отправления и пунктом прибытия груза [1]. Между этими двумя пунктами имеются...
Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
В этом случае среди параметров модели выделяют один или несколько, доступных нашему влиянию – независимые переменные или управляемые...
Решение задач прикладной информатики в менеджменте iconРешение задач прикладной информатики в менеджменте
Отель на берегу моря планирует проложить дорожку на пляж из точки а в точку с (рисунок 1). Стоимость строительства одного метра дорожки...
Разместите кнопку на своём сайте:
Документы


База данных защищена авторским правом ©podelise.ru 2000-2014
При копировании материала обязательно указание активной ссылки открытой для индексации.
обратиться к администрации
Документы

Разработка сайта — Веб студия Адаманов