excel_2

Пусть, например, замеры количества покупателей в течение недели в магазинах №1 и №2 дали результаты, представленные в таблицах 3 и 4 соответственно.

Таким образом, в данном примере <x>=<y>, т.е. в среднем каждый день в каждом из рассматриваемых магазинов бывает 700/7= 100 покупателей

Важно также знать, как сильно значения изучаемой величины отличаются от ее среднего, или, иначе говоря, насколько широк разброс случайной величины. Рассеивание случайной величины вокруг ее среднего характеризует дисперсия D[X]. Чем больше дисперсия, тем «случайнее» случайная величина. Для приближенного значения дисперсии дискретной случайной величины X используют следующую формулу:

D[X]=1 ∑n (xi −<x >)2 . n i=1

23

На практике часто используют и другую характеристику рассеивания

– среднеквадратичное отклонение σx, вычисляемое по формуле

σx = D[X ] . Величина σx также характеризует размах колебаний случайной

величины X около среднего значения, но σx, в отличие от D[X], имеет ту же размерность, что и случайная величина X.

Так, например, в магазине №1 (см. данные последнего столбца табл.3) в среднем количество покупателей каждый день отличается от средней величины (100 человек в день) на 35 человек (т.к. 8400/7=1200 и 1200≈352). В магазине же №2 (см. данные последнего столбца табл.4) в среднем количество покупателей каждый день отличается от средней величины (100 человек в день) на 2 человека (т.к. 28/7=4 и 4=22).Таким образом, в нашем примере разброс случайной величины X-количества покупателей в магазине №1 – около своего среднего значения достаточно велик и составляет приблизительно третью часть средней величины, в то время как разбросом случайной величины Y -количества покупателей в магазине №2

– около ее среднего значения можно пренебречь, так как он составляет всего лишь 2% от средней величины.

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

Excel предусматривает также применение 18 статистических пакетов анализа, в том числе таких, как описательная статистика, гистограмма, генерация случайных чисел, корреляция, ковариация и ряд других.

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

Эти инструменты позволяют автоматизировать анализ данных и статистических параметров. Доступ к ним можно получить, выбрав в меню «Сервис» команду «Анализ данных». Затем в диалоговом окне «Инструменты анализа» следует выбрать нужный инструмент и задать входной и выходной интервалы, а также другие требуемые параметры.

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

24

Для этого инструмент «Описательная статистика» создает таблицу, содержащую следующие статистические характеристики: среднюю выборочную, выборочную дисперсию, среднее квадратическое отклонение, медиану, моду и эксцесс. Большинство из этих параметров можно вычислить и с помощью других инструментов пакета анализа, либо с помощью соответствующих встроенных функций Excel. Инструмент «гистограмма» позволяет представить числовые данные графически – в виде диаграмм (обычно столбчатых). Подробнее об инструментах пакета анализа можно посмотреть встроенную Справку.

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

12.Встроенные статистические функции Microsoft Excel

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

Функции, применяемые для вычисления выборочных средних

Функции СУММ и СРЗНАЧ В качестве аргумента этих функций можно указать диапазон ячеек

или список чисел, разделенных точкой с запятой. Функция СУММ вычисляет сумму числовых значений, указанных в качестве аргументов. Например, следующая формула

= СУММ(А1;А3;А6;А7)

вычисляет сумму чисел, расположенных в ячейках с адресами А1, А3, А6 и А7. Функция СРЗНАЧ вычисляет среднее арифметическое, суммируя числовые значения, с последующим делением на их количество. Она игнорирует пустые, логические и текстовые ячейки. Например, следующая формула

= СРЗНАЧ(А1;А2;А3;А4)

вычисляет среднее арифметическое ряда чисел, расположенных в ячейках с адресами А1, А2, А3 и А4. Очевидно, проще ввести:

= СРЗНАЧ(А1:А4)

Функции МЕДИАНА, МОДА, МАКС, МИН, СЧЕТ

25

Аргументом всех этих функций может быть диапазон ячеек или список чисел, разделенных точкой с запятой. Функция МЕДИАНА вычисляет медиану, т.е. серединное значение признака, которое делит ранжированный числовой ряд на две равные по численности группы. Например, следующая формула возвращает значение 22:

=МЕДИАНА(11;22;33;-1;55)

Функция МОДА определяет наиболее часто встречающееся значение признака. Например, следующая формула возвращает значение, равное 22:

=МОДА(-1;-1;11;22;22;22;44;55)

Замечание. Если совокупность чисел не содержит повторяющихся чисел, МОДА возвращает ошибочное значение #Н/Д

Функции МАКС, МИН возвращают соответственно наибольшее и наименьшее значения из указанного набора данных. Функция СЧЕТ определяет количество ячеек, в заданном диапазоне, которые содержат числа. Эта функция учитывает только числовые значения и игнорирует текстовые, логические и ошибочные значения. Для определения количества непустых ячеек (независимо от их содержимого) используется функция СЧЕТ3.

Функции СУММПРОИЗВ и СУММКВ Функция СУММПРОИЗВ перемножает соответствующие элементы

нескольких массивов, а затем вычисляет сумму этих произведений. При этом нечисловые значения в аргументах рассматриваются как нулевые. На рис. 2 показан лист, в котором используется функция СУММПРОИЗВ:

= СУММПРОИЗВ(A2:A5;B2:B5)

Рис. 2. Фрагмент таблицы с числовыми данными, использующимися при вызове функции СУММПРОИЗВ

26

Формула в ячейке А6 определяет объем выборки, т.е. общее число работающих на предприятиях некоторой отрасли, попавших в выборку. В данном случае значение функции вычисляется по формуле:

А2*В2+А3*В3+А4*В4+А5*В5

Функция СУММПРОИЗВ может иметь до 30 аргументов. Все числовые массивы должны иметь одинаковые размерности, в противном случае функция возвратит ошибочное значение #ЗНАЧ!

Функция СУММКВ, в отличие от СУММПРОИЗВ, вычисляет сумму квадратов, а не произведений. Например, вычисление по следующей формуле:

=СУММКВ(B2:B5) даст значение 529 (25+100+400+4).

Замечание. Массив – это объект Excel, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. В Microsoft Excel определены два типа массивов: диапазоны массива и диапазоны констант. Диапазоном массива называется непрерывный диапазон ячеек, использующих общую формулу; диапазон констант представляет собой набор констант, используемых в качестве аргументов функций.

Функции, применяемые для вычисления характеристик рассеяния

Функции ДИСПР и СТАНДОТКЛОНП Эти функции вычисляют дисперсию и среднее квадратическое от-

клонение числовых данных из входного диапазона. При этом, если значения признака из входного диапазона образуют генеральную совокупность и не сгруппированы, то ДИСПР вычисляет генеральную дисперсию как среднюю арифметическую квадратов отклонений значений признака от его генеральной средней. Если же числовые значения являются выборкой из генеральной совокупности, то функция ДИСПР вычисляет выборочную дисперсию как среднюю арифметическую квадратов отклонений значений признака от его выборочной средней. Функция СТАНДОТКЛОНП вычисляет арифметический квадратный корень из дисперсии.

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

Функции ДИСП и СТАНДОТКЛОН Эти функции позволяют вычислить исправленную выборочную дис-

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

27

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

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

Существуют задачи, в которых требуется найти значение, которое сложным образом зависит от нескольких других величин. Например, некая фирма производит и продает какие-то товары и стремится получить максимальную прибыль. Ей придется потратить средства на закупку сырья, рекламу, оплату электроэнергии и многое другое. При этом финансовые и производственные возможности фирмы ограничены. Средство поиска решения позволяет найти значение в определенной ячейке на основе содержимого ряда других ячеек, возможно, с учетом ограничений. Именно оно обеспечивает возможность решения задач оптимизации. Однако использование Excel для решения таких задач требует серьезной подготовки в области исследования операций и математического программирования.

29

нужно воспользоваться 5 раз (по числу сотрудников). Так, чтобы вычислить сумму к выдаче для Васильева В.В., нужно:

–активизировать ячейку, находящуюся на пересечении строки 5 (в которой находится фамилия Васильев) и столбца F (графа «К выдаче с учетом подоходного налога»);

–напечатать в ней знак равенства «=» (признак начала формулы) или нажать на кнопку «Изменить формулу» в строке формул;

–напечатать открывающую круглую скобку;

–щелкнуть клавишей мыши по ячейке (С5), в которой содержится значение оклада для сотрудника Васильева. Адрес этой ячейки появится после открывающей круглой скобки (при желании можно печатать адреса и вручную);

–напечатать знак плюс «+»;

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

=(C5+D5*C5+E5*C5)*(1–Налог)

–когда ввод будет завершен, нажмите клавишу <Enter>. В ячейке F5 отобразится искомая сумма (3101,55).

Чтобы получить суммы к выдаче для других сотрудников, нет необходимости вводить формулу для каждого из них. Достаточно активизировать ячейку F5 и потянуть вниз мышью за маркер заполнения, чтобы скопировать формулу в четыре соседние ячейки. Адреса ячеек, входящих в первый сомножитель, при этом изменятся согласно правилам копирования относительных адресов. Имя «Налог» является абсолютным адресом и фигурирует во всех пяти формулах неизменно.

Замечание. Сохраняйте результаты Вашей работы после выполнения каждого задания. Выполняйте каждую лабораторную работу на отдельном листе рабочей книги. Называйте листы так же, как задания: «Задание 1А», «Задание 3В» и т.д.

Задание B. Создать таблицу умножения чисел от 1 до 9, используя средство автоматического заполнения Excel.

УКАЗАНИЯ

Сначала следует заполнить строку и столбец, содержащие сомножители. Чтобы заполнить строку, нужно:

–напечатать в ячейках B2 и B3 числа 1 и 2 соответственно;

–выделить обе ячейки (начиная с B2);

31

–тянуть вправо за маркер заполнения, пока не будет достигнута ячейка J1 (рядом с маркером заполнения в маленьком белом окошке отображается значение последнего элемента прогрессии).

Столбец заполняется аналогично, с той лишь разницей, что начальные значения – числа 1 и 2 – печатаются в ячейках A2 и A3, а маркер заполнения нужно тянуть вниз до ячейки A10 включительно.

Следующий шаг состоит в заполнении собственно таблицы. Удобно воспользоваться возможностью ввести формулу одновременно во все ячейки. Для этого следует:

–выделить диапазон ячеек B2:J10;

–напечатать формулу

=B$1*$A1

(Поскольку сомножители всегда должны находиться в первой строке и первом столбце, эти координаты и фиксируются в фор-

муле: $1 и $A).

–нажать клавиши <Ctrl>+<Enter>.

Врезультате Вы получите полностью заполненную таблицу умножения.

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

1.Что называется электронной таблицей?

2.Что такое рабочий лист Excel?

3.Что понимают под рабочей книгой Excel?

4.В чем отличие электронной таблицы от обыкновенной?

5.Как создать таблицу на рабочем листе?

6.Как удалить электронную таблицу?

7.Что понимают под координатами ячейки?

8.В чем разница между относительными и абсолютными координатами?

9.Что такое имя ячейки?

10.Данные каких типов можно вводить в ячейки таблицы? 11.Перечислите способы ввода новых значений в ячейки таблицы.

32