Тема: Математические встроенные функции. Функции даты и времени в MSExcel.

 

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

Теоретические сведения

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

1. СУММЕСЛИ(ДИАПАЗОН;УСЛОВИЕ;ДИАПАЗОН__СУММИРОВАНИЯ) — группа математических функций, применяют для суммирования ячеек, удовлетворяющих определенному критерию.

• ДИАПАЗОН — это диапазон, в котором определяется критерий;

• УСЛОВИЕ — указывается в форме числа, выражения или текста;

• ДИАПАЗОН_СУММИРОВАНИЯ — это диапазон суммируемых
ячеек.

2. СЧЕТ (ЗНАЧЕНИЕ1;ЗНАЧЕНИЕ2;...) - группа статистических функций, испльзуют для подсчета количества числовых значений в диапазоне.

3. СЧИТАТЬПУСТОТЫ(ДИАПАЗОН) — группа статистических фун­кций, используютдля подсчета количества пустых ячеек в диапазоне.

4. СЧЕТЕСЛИ(ДИАПАЗОН; УСЛОВИЕ) — группа статистических функций, подсчет количества непустых ячеек в диапазоне, удовлетворяющих заданному условию.

§ ДИАПАЗОН — это диапазон, в котором определяется крите­
рий;

§ УСЛОВИЕ — указывается в виде числа, выражения или тек­
ста и определяет какие ячейки надо подсчитывать.

5. СРЗНАЧ(ДИАПАЗОН1; ДИАПАЗОН2;...) - группа статистиче­ских функций. В текущую ячейку возвращается среднее значение для чисел указанного диапазона, расчет среднего значения.

6. МАКС(ДИАПАЗОН1; ДИАПАЗОН2;...) — группа статистических функций. В текущую ячейку возвращается максимальное число из данного диапазона, определение максимального значения.

7. МИН(ДИАПАЗОН1; ДИАПАЗОН2;...) — группа статистических функций. В текущую ячейку возвращается минимальное число из данного диапазона.

8. РАНГ(АДРЕС ЯЧЕЙКИ; ДИАПАЗОН) — группа статистических функций. В текущую ячейку возвращается величина, соответст­вующая положению (рангу) числа, заданного адресом ячейки, в указанном диапазоне.

9. ПРОЦЕНТРАНГ(ДИАПАЗОН; АДРЕС ЯЧЕЙКИ) - группа стати­стических функций. В текущую ячейку возвращается величина, определяющая процентную долю числа, заданного адресом ячей­ки, от максимального значения в указанном диапазоне. Действие функции аналогично функции ранг, только ранг определяется в процентном отношении (максимальное число принимается за 100%, минимальное — за 0%)

10. Для прогнозирования используется ряд функций (ПРЕДСКАЗ, РОСТ, ТЕНДЕНЦИЯ) и диаграммы.

Функция ПРЕДСКАЗ

ПРЕДСКАЗ (X; Известные значения Y; Известные значения X).

Функция ПРЕДСКАЗ вычисляет или предсказывает будущее значение по существующим значениям.

X — это точка данных, для которой предсказывается значение. Известные значения Y — это зависимый массив или интервал данных.

Известные значения X — это независимый массив или интер­вал данных.

11. Функция РОСТ

РОСТ(Известные значения Y; Известные значения X; Новые значения X; КОНСТ).

Функция РОСТ рассчитывает прогнозируемый экспоненциаль­ный рост на основании имеющихся данных. Функция РОСТ воз­вращает значения Y для последовательности новых значений X, за­даваемых с помощью существующих Х- и Y-значений. Функция ра­бочего значения РОСТ может применяться также для аппроксима­ции существующих Х- и Y-значений экспоненциальной кривой.

Известные значения Y — это множество значений Y, которые уже известны для соотношения Y = b*m^Х.

Известные значения X — это необязательное множество значе­ний х, которые уже известны для соотношения Y = b*m^Х.

Новые значения X — это новые значения X, для которых РОСТ возвращает соответствующие значения Y.

КОНСТ — это логическое значение, которое указывает, тре­буется ли, чтобы константа b была равна 1.

12. Функция ТЕНДЕНЦИЯ

ТЕНДЕНЦИЯ(Известные значения Y; Известные значения X;

Новое значение X; КОНСТ).

В текущую ячейку возвращается новое значение X, рассчитан­ное на основании известных значений. Выполняется линейная аппроксимация.

13. МОБР(МАССИВ) — группа математических функций. Возвращает в выделенный диапазон обратную матрицу для матрицы, хранящейся в массиве.

Массив — это числовой массив с равным количеством строк и столбцов. Массив может быть задан как диапазон ячеек, напри­мер А1:СЗ или как имя диапазона или массива. Если какая-либо из ячеек в массиве пуста или содержит текст, а также если массив имеет неравное число строк и столбцов, то функция МОБР воз­вращает значение ошибки #ЗНАЧ!.

14. МОПРЕД(МАССИВ) — группа математических функций. Воз­вращает определитель матрицы (матрица хранится в массиве). Определитель матрицы — это число, вычисляемое на основе зна­чений элементов массива. Массив — это числовой массив с рав­ным количеством строк и столбцов. Если какая-либо ячейка в массиве пуста или содержит текст, то функция МОПРЕД возвра­щает значение ошибки #ЗНАЧ!. МОПРЕД также возвращает зна­чение ошибки #ЗНАЧ!, если массив имеет неравное количество строк и столбцов.

15. МУМНОЖ(МАССИВ1;МАССИВ2) — группа математических функ­ций. Возвращает произведение матриц, хранящихся в массивах 1 и 2.

Результатом является массив с таким же числом строк, как массив-1 и с таким же числом столбцов, как массив-2.

Количество столбцов аргумента массив-1 должно быть таким же, как количество строк аргумента массив-2, и оба массива должны со­держать только числа. Массив-1 и массив-2 могут быть заданы как интервалы, массивы констант или ссылки. Если хотя бы одна ячейка в аргументах пуста или содержит текст, или если число столбцов в аргументе массив-1 отличается от числа строк в аргументе массив-2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!.

16. Функции даты и времени

Функция текущей даты СЕГОДНЯ() — возвращает текущую дату компьютера.

Функция текущей даты и времени ТДАТА() — возвращает текущую дату и время в числовом фор­мате.

Функция определения дня недели

ДЕНЬНЕД(ДАТА_КАК_ЧИСЛО;ТИП) - преобразует дату в чи­словом формате в номер дня недели. Если ТИП не указан или ра­вен 1, то первым днем недели считается воскресенье, последним (7-м) — суббота. Если тип равен 2, первый день недели — поне­дельник.

17. Функция определения количества дней между двумя датами

ДНЕЙ360(НАЧАЛЬНАЯ_ДАТА;КОНЕЧНАЯ_ДАТА;МЕТОД) - ко­личество дней определяется как разница между начальной и ко­нечной датами, исходя из 360 дней в году. МЕТОД — задает ис­пользование европейского стандарта (необязательный параметр).

18. Функция определения номера месяца

МЕСЯЦ(ДАТА_КАК_ЧИСЛО) — преобразует дату в числовом формате в номер месяца.

Задание

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

2. Подсчитайте количество значений в диапазоне.

3. Подсчитайте количество пустых ячеек в диапазоне.

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

5. Рассчитайте среднее значение.

6. Определите максимальное значение.

7. Определите минимальное значение.

8. Определите ранг числа.

9. Определите процентную норму числа.

10. Выполните расчет ожидаемой прибыли.

11. Выполните операции над матрицами.

12. Изучите функции даты и времени.

 

 

Технология работы

 

1. Суммирование ячеек, удовлетворяющих определенному критерию.

1.1. В ячейки А1,А2,А3,А4,А5 введите информацию:

А1=1000, А2=2000, А3=900, А4=800, А5=1500.

Требуется подсчитать сумму чисел, значение которых больше или равно 1000. Результат должен быть получен в ячейке А6.

1.2. Пошаговыми действиями Мастера функций в ячейку А6 введите формулу:

=СУММЕСЛИ(А1:А5;">=1000"). В ячейке А6 получится число 4500.

1.3. Измените значение ячейки A3 на любое большее 1000. Про­следите изменение результата вычислений.

2. Подсчет количества значений в диапазоне

2.1. В ячейки В1,В2,ВЗ,В4,В5 введите информацию:

В1=«Текст», В2=2000, В3=, В4=800, В5=1500.

Требуется подсчитать количество ячеек с числовыми значе­ниями в диапазоне В1:В5. Результат должен быть получен в ячейке В6.

2.2. Пошаговыми действиями Мастера функций в ячейку В6 следу­ет ввести формулу: =СЧЕТ (В1:В5). В ячейке В6 получится число 3.

2.3. Для подсчета количества всех значений в списке аргументов и непустых ячеек используют СЧЕТЗ(ЗНАЧЕНИЕ1;ЗНАЧЕНИЕ2;...) — группа статистических функций.

Если эта функция будет введена в примере 2 в ячейку В7, то результат в ячейке В7 будет равен 4.

3. Подсчет количества пустых ячеек в диапазоне

3.1. В ячейки С1,С2,СЗ,С4,С5 скопируйте информацию из соот­ветствующих ячеек столбца А, оставив пустой ячейку СЗ:

С1 = 1000, С2=2000, С3=, С4=800, С5=1500.

Требуется подсчитать количество пустых ячеек в диапазоне С1:С5. Результат должен быть получен в ячейке Сб.

3.2. Пошаговыми действиями Мастера функций в ячейку С6 следу­ет ввести формулу: =СЧИТАТЫПУСТОТЫ(С1:С5). В ячейке С6 по­лучится число 1.

4. Подсчет количества непустых ячеек в диапазоне, удовлетворяющих заданному условию.

4.1. Подсчитайте количество непустых ячеек в диапазоне С1:С5, значение которых больше или равно 1000. Результат должен быть получен в ячейке С7.

4.2. Пошаговыми действиями Мастера функций в ячейку С7 следу­ет ввести формулу: =СЧЕТЕСЛИ(С1:С5;">=1000"). В ячейке С7 по­лучится число 3.

4.3. Измените значение ячейки С1 на любое, меньшее 1000. Про­следите изменение результата вычислений.

5. Расчет среднего значения.

В диапазоне ячеек А1:А5 из примера 1 определить среднее зна­чение. Результат должен быть получен в ячейке А7.

Пошаговыми действиями Мастера функций в ячейку А7 следу­ет ввести формулу: =СРЗНАЧ(А1:А5).

6. Определение максимального значения.

В диапазоне ячеек А1:А5 из примера 1 определить максималь­ное значение. Результат должен быть получен в ячейке А8.

Пошаговыми действиями Мастера функций в ячейку А8 следует ввести формулу: =МАКС(А1:А5). В ячейке А8 получится число 2000.

7. Определение минимального значения

В диапазоне ячеек В1:В5 из примера 2 определить минималь­ное значение. Результат должен быть получен в ячейке В8.

Пошаговыми действиями Мастера функций в ячейку В8 следует ввести формулу: =МИН(В1:В5). В ячейке В8 получится число 800.

8.1.В ячейки D1,D2,D3,D4,D5 скопируйте информацию из соот­ветствующих ячеек столбца А. Для каждой ячейки из диапазона D1:D5 определить ранг числа. Результат должен быть получен в ячейках Е1:Е5.

8.2. Функция ранга введите сначала в ячейку Е1, затем копируйте для всех ячеек до Е5. Пошаговыми действиями Мастера функ­ций в ячейку Е1 вводим формулу: =PAHГ(D1; $D$1:$D$5) — знак $ устанавливает абсолютные адреса, чтобы диапазон ячеек не ме­нялся при копировании. После копирования формулы вниз для всех ячеек до Е5 получим ранги для каждого значения диапазона. Ранг числа с максимальным значением в диапазоне D1:D5 будет равен 1, а с минимальным — 5.

9. Определение процентной нормы числа

Для значений ячеек D1,D2,D3,D4,D5 определить процентную долю каждого числа от максимального значения в диапазоне.

Результат должен быть получен в ячейках F1:F5. Для этих яче­ек задайте процентный формат.

10. Функции прогнозирования

В качестве примера выполнить расчет ожидаемой прибыли на 2000 год на основе данных о полученной прибыли за 6 месяцев за 1995—2000 гг. и в целом за год за 1995—1999 гг., а также рас­считать ожидаемую прибыль на 2007 год, используя функцию ПРЕДСКАЗ.

10.1.Оформите и заполните таблицу, приведенную ниже.

Прогноз прибыли
Год	Прибыль предприятия
6 месяцев	Год
		1690,4797
	ПРЕДСКАЗ	1783,6531
	РОСТ	1801,0942
	ТЕНДЕНЦИЯ	1783,6531

Результат прогнозирования на 2000 год должен быть получен в ячейке С9.

10.2. Установите курсор в ячейку С9, задайте команду Вставка –Функция – ПРЕДСКАЗ. И в появившемся диалоговом окне запол­ните поля.

В ячейке С9 должна быть формула =ПРЕДСКАЗ (В9;С4:С8;В4:В8). В ячейке С9 получится число 1690,4797.

Результат прогнозирования на 2001 год должен быть получен в ячейке С10.

В ячейке С10 должна быть формула =ПРЕДСКАЗ (А10;С4:С9;А4:А9). В ячейке С10 получится число 1783,6531.

10.3.Выполнить расчет ожидаемой прибыли на 2001 год на основе предыдущих данных.

Установите курсор в ячейку С11, задайте команду Вставка > Функция > РОСТ и в появившемся диалоговом окне заполнить по­ля. В ячейке С11 должна быть формула =РОСТ (С4:С9;А4:А9;А11).

В ячейке С11 получится число 1801,0942.

10.4. В качестве примера выполнить расчет ожидаемой прибыли на 2001 год на основе тех же данных.

Результат прогнозирования должен быть получен в ячейке С12.

Установить курсор в ячейку С12, задать команду Вставка > Функция > ТЕНДЕНЦИЯ, и в появившемся диалоговом окне за полнить поля. В ячейке С12 должна быть формула =ТЕНДЕНЦИЯ (С4:С9;А4:А9;А12).

В ячейке С12 получится число 1783,6531.

11. Функции для работы с матрицами

11.1. На новом рабочем листе в ячейки А1:В2 ввести матрицу: 5 2 2 1.

Требуется найти матрицу, обратную данной. Результат должен быть получен в диапазоне ячеек А4:В5.

11.2. Выделите диапазон, в который будет помещена матрица, об­ратная данной.

11.3. В выделенный диапазон введите формулу: =МОБР(А1:В2). Ввод формулы завершить нажатием клавиш <Ctrl><Shift><Enter>.

11.4. Для матрицы найдите определитель. Результат по­лучить в ячейке А7.

В ячейку А7 ввести формулу: =МОПРЕД(А1:В2).

11.5. Определите произведение матриц, хранящихся в массивах А1:В2 и А4:В5. Результат должен быть получен в диапазоне ячеек А7:В8.

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

11.6. В выделенный диапазон введите формулу: =МУМНОЖ(А1:В2;А4:В5). Ввод формулы завершите нажатием клавиш <Ctrl+Shift><Enter>.

В данном примере (произведения матрицы на обратную) результатом является единичная матрица, то есть квадратный массив, у которого диагональные элементы равны 1, а все осталь­ные элементы равны 0.

11.7. Измените значения исходной матрицы. Проследите измене­ния результатов.

12. Определите день недели от даты рождения.

12.1. Вернитесь на Лист1 рабочей книги.

12.2. В ячейку G8 введите дату рождения в числовом формате: например, 14.09.1980.

12.2. Пошаговы­ми действиями Мастера функций в ячейку G9 введите формулу: =ДEHЬHEД(G8;2).

В ячейке G9 получится число 7, что соответствует «воскресе­нью».

12.3. Определите количество дней от даты рождения по текущую дату.

Пошаговыми действиями Мастера функций в ячейку G10 вве­дите формулу: =ДНЕЙ360(С8;СЕГОДНЯ()).

Работа №9