Бородина А.И., Крошинская Л.И., Сапун О.Л. Основы информатики и вычислительной техники

Подождите немного. Документ загружается.

СЧЕТЕСЛИ (В3:В6;”>55”) равняется 2.

ЧИСЛОКОМБ – количество комбинаций для заданного числа объектов.

Рассчитывает количество комбинаций для заданного числа объектов. Функция

ЧИСЛОКОМБ может использоваться для определения числа всех возможных

сочетаний объектов.

Пример. Необходимо сформировать группу из четырех человек, при

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

составит: ЧИСЛОКОМБ(10;4) равняется 210.

СУММЕСЛИ – сумма ячеек, определенных по заданному критерию.

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

СУММЕСЛИ(интервал;критерий; сумм_интервал)

где интервал – интервал вычисляемых ячеек;

критерий – критерий в форме числа, выражения или текста, который

определяет, какая ячейка суммируется с другими.

Например, критерий может быть выражен как 25, «25»,

>25, «дома»;

сумм_интервал – ячейки для суммирования. Эти ячейки

суммируются в том случае, если соответствующие

им ячейки в аргументе интервал удовлетворяют

критерию. Если аргумент сумм_интервал опущен,

то суммируются ячейки в аргументе интервал.

Пример. Пусть ячейки А2:А6 содержат величины стоимости для пяти

объектов: 100000 руб., 200000 руб., 300000 руб., 400000 руб., 500000 руб.

соответственно. Пусть ячейки С1:С4 содержат величины комиссионных при

продаже объектов: 9000 руб., 15000 руб., 22000 руб., 29000 руб., 36000 руб.

СУММЕСЛИ(А2:А6;”>210000”;С1:С4) равняется 66000 руб.

Статистические функции

КОРЕЛЛ – связь между двумя множествами данных. Данная функция

позволяет рассчитать коэффициент корреляции между двумя интервалами

ячеек. Коэффициент корреляции обычно используется для определения

взаимосвязи между двумя свойствами объекта.

КОРЕЛЛ(массив1;массив2)

где массив1 – первый интервал ячеек;

массив2 – второй интервал ячеек.

Аргументы должны быть числами или именами, массивами или ссылками,

содержащими числа. Если аргумент, который является массивом или ссылкой,

содержит тексты, логические значения или пустые ячейки, то такие значения

игнорируются; однако ячейки с нулевыми значениями учитываются.

Функция КОРЕЛЛ выдает значение ошибки #Н/Д в том случае, если

массив1 и массив2 имеют различное количество данных и значение ошибки

#ДЕЛ/0! в том случае, если массив1 или массив2 пуст, или, если отклонение их

значений равно нулю.

Пример. КОРЕЛЛ({3;2;4;5;6};{9,7,12,15,17} равняется 0,997054.

МАКС – максимальное значение из списка чисел. Позволяет рассчитать

наибольшую величину из набора числовых значений.

МАКС(число1;число2;…)

где число1, число2,… - до 30 чисел, среди которых ищется максимальное

значение.

Можно задавать аргументы, которые являются числами, пустыми

ячейками, логическими значениями или текстовыми представлениями чисел.

Аргументы, которые являются значениями ошибки или текстами, не

преобразуемыми в числа, вызывают ошибку.

Если аргумент является массивом или ссылкой, то в нем учитываются

только числа. Пустые ячейки, логические значения или текст в массиве или

ссылке игнорируются. Если логические значения или текст не должны

игнорироваться, следует использовать функцию МАКСА.

Пример. Определить максимальное значение, если ячейки А1:А5 содержат

числа 12,5,6,29 и 3.

МАКС(А1:А5) равняется 29.

МАКСА(А1:А5;40) равняется 40.

Если аргументы не содержат чисел, то функция МАКС выдает 0 (ноль).

СРЗНАЧ – среднее арифметическое. Позволяет рассчитать среднее

(арифметическое) двух чисел.

СРЗНАЧ(число1;число2;…)

где число1, число2,… - до 30 аргументов, для которых вычисляется

среднее арифметическое.

Аргументы должны быть числами или именами, массивами или ссылками,

содержащими числа. Если аргумент, который является массивом или ссылкой,

содержит тексты, логические значения или пустые ячейки, то такие значения

игнорируются; однако, ячейки, которые содержат нулевые значения,

учитываются.

Вычисляя средние значения ячеек, следует учитывать различие между

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

не установлен флажок Нулевые Значения на панели Вид в диалоговом окне

Параметры. Пустые ячейки не учитываются, но нулевые ячейки учитываются.

Чтобы открыть диалоговое окно Параметры, надо выбрать команду

Параметры в меню Сервис.

Пример: Пусть ячейки А1:А5 имеют имя Баллы и содержат числа 10, 7, 9,

27 и 2, тогда

СРЗНАЧ(А1:А5) равняется 11

СРЗНАЧ(Баллы) равняется 11

СРЗНАЧ(А1:А5;5) равняется 10

СРЗНАЧ(А1:А5 равняется СУММ(А1:А5)/СЧЕТ(А1:А5) и равняется 11.

МОДА – наиболее часто встречающееся значение. Функция позволяет

рассчитать наиболее часто встречающееся или повторяющееся значение в

массиве или интервале данных.

МОДА(число1;число2;…)

где число1, число2,… - до 30 аргументов, среди которых вычисляется наиболее

часто встречающееся значение. Можно использовать массив или ссылку на

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

Аргументы должны быть числами, именами, массивами или ссылками,

которые содержат числа. Если аргумент, который является массивом или

ссылкой, содержит тексты, логические значения или пустые ячейки, то такие

значения игнорируются; однако, ячейки, которые содержат нулевые значения,

учитываются.

Функция МОДА выдает значение ошибки #Н/Д! в том случае, если

множество данных содержит не однотипные данные.

Пример. МОДА({5;6;4;4;3;2;4}) равняется 4.

4.3. Выборка и анализ данных в Excel

В Excel имеется набор инструментов для анализа данных, называемый

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

или экономических задач. Для использования одного из этих инструментов

необходимо указать входные данные и выбрать параметры. Анализ будет

проведен с помощью подходящей статистической или инженерной

макрофункции, и результаты будут представлены в выходном диапазоне.

Некоторые инструменты позволяют представить результаты анализа в

графическом виде.

Статистический пакет анализа данных. Для его установки в меню

Сервис выбирается команда Надстройки и далее в списке Пакет анализа.

Для использования инструментов анализа, анализируемые данные следует

представить в виде строк или столбцов. Совокупность ячеек, содержащих эти

данные, называется входным диапазоном.

В меню Сервис выбирается команда Анализ данных. В списке

Инструменты анализа выбирается необходимая строка. Далее вводятся

входной и выходной диапазоны.

Корреляционный анализ. Используется для количественной оценки

взаимосвязи двух наборов данных, представленных в безразмерном виде.

Корреляционный анализ дает возможность установить: ассоциированы ли

наборы данных по величине, то есть, большие значения из одного набора

данных связаны с большими значениями другого набора (положительная

корреляция), или, наоборот, малые значения одного набора связаны с

большими значениями другого (отрицательная корреляция), или данные двух

диапазонов никак не связаны (корреляция близка к нулю).

Для вычисления коэффициента корреляции между двумя наборами данных

используется статистическая функция КОРЕЛ.

Ковариационный анализ. Ковариация является мерой связи между двумя

диапазонами данных. Используется для вычисления среднего произведения

отклонений точек данных относительно средних.

Ковариационный анализ дает возможность установить, ассоциированы ли

наборы данных по величине, то есть, большие значения из одного набора

данных связаны с большими значениями другого набора (положительная

ковариация), или, наоборот, малые значения одного набора связаны с

большими значениями другого (отрицательная ковариация), или данные двух

диапазонов никак не связаны (ковариация близка к нулю).

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

помощью статистической функции КОВАР.

Экспоненциальное сглаживание. Предназначается для предсказания

значения на основе прогноза для предыдущего периода, скорректированного с

учетом погрешностей в этом прогнозе. Использует константу сглаживания, по

величине которой определяет, насколько сильно влияют погрешности на

прогнозы в предыдущем прогнозе.

Скользящее среднее. Используется для расчета значений в периоде

прогнозирования на основе среднего значения переменной для указанного

числа предшествующих периодов. Скользящее среднее, в отличие от простого

среднего для всей выборки, содержит сведения о тенденциях изменения

данных. Процедура может использоваться для прогноза сбыта, инвентаризации

и других процессов.

Генерация случайных чисел. Используется для заполнения диапазона

случайными числами, извлеченными из одного или нескольких распределений.

С помощью данной процедуры можно моделировать объекты, имеющие

случайную природу, по известному распределению вероятностей. Например,

можно использовать нормальное распределение для моделирования

совокупности данных по арифметическим ошибкам в бухгалтерском учете.

Чтобы в результате выполнения вычислений вернуть равномерно

распределенное случайное число, большее или равное 0 и меньшее 1,

используется функция СЛЧИС(). Чтобы вернуть случайное число, лежащее

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

СЛУЧМЕЖДУ().

Ранг и персентиль. Используется для вывода таблицы, содержащей

порядковый и процентный ранги для каждого значения в наборе данных.

Данная процедура может быть применена для анализа относительного

взаимораспределения данных в наборе.

Регрессия. Линейный регрессионный анализ заключается в подборе

графика для набора наблюдений с помощью метода наименьших квадратов.

Регрессия используется для анализа воздействия на отдельную зависимую

переменную значений одной или более независимых переменных. Например,

на объем реализации влияют несколько факторов, включая цену, выпуск и

сезонность. Регрессия пропорционально распределяет меру реализации по этим

трем факторам на основе данных функционирования организации. Результаты

регрессии впоследствии могут быть использованы для предсказания объема

реализации.

Выборка. Создает выборку из генеральной совокупности, рассматривая

входной диапазон как генеральную совокупность. Если совокупность слишком

велика для обработки или построения диаграммы, можно использовать

представительную выборку. Кроме того, если предполагается периодичность

входных данных, то можно создать выборку, содержащую значения только из

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

квартальных продаж, создание выборки с периодом 4 разместит в выходном

диапазоне значения продаж из одного и того же квартала.

Вопросы для самоконтроля

1. Встроенные функции MS Excel, их назначение и классификация

2. Встроенные функции MS Excel, используемые в экономике

3. Статистические встроенные функции Ms Excel

4. Выборка и анализ данных в MS Excel

5. СРАВНИТЕЛЬНАЯ ХАРАКТЕРИСТИКА MATHCAD

И MICROSOFT EXCEL

В настоящее время для рутинных расчетов на компьютере чаще

используются не традиционные языки программирования (Basic, Pascal,

Fortran) а электронные таблицы и специальные математические программы.

Говоря об электронных таблицах, мы обычно имеем в виду Microsoft Excel.

Математическая же программа у нас часто ассоциируется в основном с пакетом

MathCad. Оба эти пакета задумывались как средства работы на компьютере

пользователей, не желавших или не умевших «возиться» с языками

программирования при решении финансовых, научно-технических и прочих

прикладных задач (программирование без программирования).

MathCad – это гибкий инструмент для математических, инженерных,

научных и финансовых вычислений более высокого уровня, чем электронные

таблицы, так как он создавался на основе электронных таблиц.

В последних выпусках MathCad видна устойчивая тенденция к тесной

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

от других высокофункциональных пакетов, например Mathematica, которые

выполняют роль основного инструмента специалиста, MathCad вполне

пригоден и в качестве вспомогательного средства. Так, пользователь, которого

не удовлетворяют графика или программистские возможности Microsoft Excel,

может использовать более мощные средства MathCad, предоставляющего

вместо жестких сеток Excel среду для создания сложных документов.

Ядро MathCad напоминает рабочую тетрадь с интерфейсом в стиле

текстового редактора и имеет готовые инструменты для числовых и

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

гораздо более мощные и гибкие возможности, чем электронные таблицы.

MathCad обладает несравнимо более высоким потенциалом в сфере

обработки экспериментальных данных (интерполяции, регрессии,

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

виде электронной таблицы, строить на их основе графики и диаграммы лучше в

MathCad.

Технология работы в средах Microsoft Excel и MathCad имеет много

общего. Так, процесс создания «программы» идет параллельно с ее отладкой и

оптимизацией. Отладочные фрагменты (не только числа, но и графики, а также

анимационные клипы) можно оставить в готовой таблице или в MathCad-

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

Microsoft Excel и в MathCad встроено большое количество математических

операторов и функций. И в среде Microsoft Excel, и в среде MathCad

математический аппарат можно расширять, создавая пользовательские

операторы, функции и процедуры.

Следовательно, в силу своей схожести, эти пакеты могут работать

совместно. Экспортировать данные из таблицы Microsoft Excel в матрицу

MathCad можно просто скопировав их и вставив затем в пустую таблицу ввода.

Однако провести обратную операцию, то есть данные из матрицы MathCad

экспортировать в Microsoft Excel, используя таблицу ввода, невозможно. Для

выполнения этой задачи следует задействовать таблицу Microsoft Excel как

компонент MathCad.

Идея совместной работы реализована в среде программы MathConnex,

входящей в состав MathCad. MathConnex позволяет интегрировать различные

приложения Windows (Excel, MathCad, MatLab, Axum) и организовывать

передачу данных между ними.

На рабочем столе MathConnex находятся три компоненты: вверху –

электронная таблица Excel с одним выходом; в середине – MathCad-документ

(там комментарии прописаны ярким шрифтом, а рабочие формулы – блеклым)

с одним входом и одним выходом; одна электронная таблица Excel внизу с

одним входом.

Входы и выходы компонент пользователь соединяет линиями с помощью

протяжки мышью. При создании рабочего листа MathConnex (а это делается

довольно просто: нажимается кнопка с пиктограммой соответствующей

компоненты, и «перетаскивается» на рабочий лист) в результате появляются

диалоговые окна для задания параметров компонент. Одно из таких окон,

работая с которым пользователь задает число входов (Inputs) и выходов

(Outputs) у электронной таблицы, связывает с ними области ячеек.

Вставляя электронную таблицу Excel в рабочий лист MathConnex,

пользователь может либо создать новую таблицу, либо взять готовую из

архива.

В среде MathCоnnex имеется 16 компонент (разбитых на 4 группы):

1. Ввод и вывод данных:

 ввод локальных констант, можно обойтись без верхней Excel-таблицы,

вводя исходные данные (константы 138, 540, 5 и 3) прямо в MathCad-

компоненту;

 ввод глобальных констант – констант, доступных во всех компонентах;

 чтение (запись) данных из файлов на дисках;

 генерирование последовательностей (значения переменной, линейно

меняющейся в заданном диапазоне с заданной скоростью), эта

компонента незаменима при моделировании динамических процессов,

ею можно, к примеру, задавать течение времени.

2. Просмотр результата:

 «инспектор» – эту компоненту обычно вставляют на линиях для

контроля потоков данных;

 график MathConnex (не просто график, а скорее осциллограф,

позволяющий визуализировать динамику изменения неких величин);

 график Axum (графики можно построить и в Excel- и MathCad-

компонентах.

3. Вычисление:

 MathCad;

 Excel;

 MatLab;

 калькулятор MathConnex – его используют для небольших вычислений,

когда не имеет смысла задействовать один из вышеперечисленных

«монстров» – Excel, MathCad или MatLab.

4. Управление потоками данных:

 выключатель;

 слияние потоков;

 разветвитель по условию;

 стоп/пауза.

MathConnex – это одно из средств визуализации OLE2-технологии. Раньше

такие программно-апаратные средства решения задач назывались системными

интеграторами. Они предназначены, в том числе, и для ситуационного

моделирования. Аналогичные функции выполняет MathConnex только уже на

качественно ином уровне. MathConnex – это виртуальная аналоговая

вычислительная машина (АВМ), точнее, гибридная ЭВМ, на которой можно

проводить работы по математике, физике, химии, теории автоматического

регулирования и т.д. При этом пригодятся наработки, созданные и в среде

MathCad, и в среде Microsoft Excel.

В настоящее время интеграционные процессы в среде программных

продуктов различных фирм-производителей носят массовый характер, и тесное

взаимодействие MathCad и Microsoft Excel лишь часть процесса создания

единого мощнейшего программного продукта, который должен соединить в

себе все наиболее необходимые пользователю функции и организовать их на

должном уровне. MathCad и Excel, Promt и Word, Word и Fine Reader и многие

другие разработки в области программного обеспечения имеют

многоуровневую взаимосвязь. Без понимания принципов интегрирования

различных программных продуктов современный пользователь лишается

широких возможностей ускорения и упрощения работы на компьютере.

Вопросы для самоконтроля

1. Назначение и общая характеристика пакета MatLab

2. Структура пакета MatLab

3. Общая характеристика пакета Mathematica

4. Достоинства пакета Mathematica

5. Интерфейс пакета Mathematica

6. Общая характеристика пакета MathCad

7. Интерфейс пакета MathCad

8. Ввод формул

9. Работа с текстом

10.Встроенные функции MS Excel, их назначение и классификация

11.Встроенные функции MS Excel, используемые в экономике

12.Статистические встроенные функции Ms Excel

13.Выборка и анализ данных в MS Excel

ЛИТЕРАТУРА

1. Гурский Д., Гурский Ю. MathCad 2001 первые шаги: Компьютерная газета,

№ 37, 2 октября 2001г.

ГЛОССАРИЙ

№

пп

Понятие Смысл понятия

1. Math-документ Электронный документ, поддерживающий на

экране и на печати текст, формулы, и графику с

цветом, массой шрифтов и символов, привычной

двухмерностью математических обозначений и

средствами формирования всего перечисленного, и

анимацию со звуком

2. Функции Специально, заранее созданные формулы, которые

позволяют быстро выполнять сложные вычисления

ТРЕНИНГ УМЕНИЙ

Умение первое: элементарные вычисления с помощью пакета

MathCad

ЗАДАНИЕ 1. Найти факториал числа 5.

Рекомендации по выполнению

1. Для выполнения вычисления следует вывести панель Арифметика:

Вид Панель инструментов Арифметика

2. Для нахождения значения факториала некоторого числа:

 нажмите на панели Арифметика символ [n!];

 в появившейся в рабочей области рамке наберите требуемое число 5;

 нажмите на этой же панели знак равно [=] (не путать со знаком

присваивания [:=]).

После этих действий в рабочей области экрана получите искомый

результат.

Внимание! Место набора символа помечается синей рамкой. Двигать

рамку следует клавишей пробела.

ЗАДАНИЕ 2. Вычислить следующие значения:

-2

, 10

,121

121

;

log(1), log(-1), ln(0), ln(5);

sin(0), sin(1), sin(90);

cos(0), cos(1), cod(45);

tg(0), tg(180), tg(360).

Пояснение. Аргументы логарифмической и тригонометрической функций

следует заключать в круглые скобки.

Умение второе: построение графиков с помощью пакета MathCad

ЗАДАНИЕ 1. Построить график функции прямой f(x):=x. Определить

значение функции при заданном значении аргумента.

Рекомендации по выполнению

1. Для построения графика прямой линии:

 наберите на клавиатуре, расположив в рабочем поле, требуемую функцию

f(x):=x;

 нажмите клавишу [Enter];

 нажмите на панели Графики кнопку [Декартов график] – и в рабочем

поле появится прямоугольник, представляющий собой декартовы

координаты;

 напишите на оси абсцисс имя переменной х, а на оси ординат имя функции

f(x);

 нажмите клавишу [Enter].

На экране появится график требуемой функции.

Пояснение. По Умолчанию система MathCad задает диапазон изменения

аргумента от –10 до 10. Если вам необходимо изменить этот диапазон, то

активизируйте график и измените значения диапазона слева и справа по оси Х.

2. Выполните редактирование построенного графика по вашему усмотрению,

воспользовавшись правой кнопкой мыши. Например, измените толщину

линии, выполнив команду:

ФорматСледТолщина

Выполните другие виды редактирования графика.

ЗАДАНИЕ 2. Построить графики прямых линий:

y=2x; y=x+2; y=-2x; y=x-2; y=2x+2; y=-2x+2; y=2x-2.

Выполнить анализ поведения функции прямой линии в зависимости от

углового коэффициента и свободного члена.

Внимание! Не забывайте в качестве имени функции указывать f(x), а не y, как

в задании.

ЗАДАНИЕ 3. Построить графики тригонометрических функций:

y=sin(x); y=sin(2x); y= -sin(x); y=sin(-x);

y=sin(x+2); y=sin(x-2); y=sin(x)+2; y=sin(x)-2;

y=cos(x); y=cos(2x); y= -cos(x); y=cos(-x);

y=cos(x+3); y=cos(x-3); y=cos(x)+3; y=cos(x)-3;

y=tg(x); y=tg(2x); y=2tg(x); y=tg(-x).

ЗАДАНИЕ 4. Построить графики парабол:

y=2x

; y=-x

; y=(2x)

; y=-(2x)

; y=x

+5; y=(x+5)

Замечание. При построении графиков 2-го порядка, таких как круг,

эллипс, гипербола, на экране в рабочей области отражается лишь часть

графика, расположенная выше оси Х (то есть в положительной области

изменения Y).

Умение третье: приближенные вычисления с помощью пакета

MathCad

ЗАДАНИЕ 1. Получить значения функции y=5x+3 для х, изменяющихся от

0 до 10 с шагом 2.