Формулы и функции. Решение задач

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

Если в качестве аргумента функции используется диапазон смежных ячеек, то он задается указанием адреса верхней левой и нижней правой ячеек диапазона, разделенных двоеточием, например, A3:C5. Диапазоны несмежных ячеек перечисляются через точку с запятой, например:

=СУММ(A3:C5;A10:C10)

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

=SIN(Лист2!А3)

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

Обычно диалоговое окно Мастер функций разделено на две части. Слева (сверху) расположен список категорий встроенных функций, справа (снизу) – перечень функций из выбранной категории. Наиболее часто используемые функции кратко описываются ниже:

Математические функции:

ABS(число) − возвращает модуль (абсолютную величину) числа;

COS(число) − возвращает косинус угла;

EXP(число) − экспонента заданного числа;

LN(число) − натуральный логарифм;

LOG(число; основание) − логарифм числа по указанному основанию;

SIN(число) − возвращает синус угла;

TAN(число) − возвращает тангенс угла;

КОРЕНЬ(число) − возвращает значение квадратного корня;

ОКРУГЛ(число; число_разрядов) − округляет число до указанного количества десятичных разрядов;

ПИ() − число π, округленное до 15 знаков после запятой;

ПРОИЗВЕД(число1; число2;...) − произведение аргументов;

СЛЧИС() − случайное число, равномерно распределенное на диапазоне [0; 1); функция используется без аргументов;

СТЕПЕНЬ(число; степень) − результат возведения в степень;

СУММ(число1; число2;...).

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

МАКС(число1; число2;...) − возвращает максимальное значение из списка аргументов;

МИН(число1; число2;...) − минимальное значение из списка аргументов;

СРЗНАЧ(число1; число2;...) − среднее арифметическое аргументов;

СЧЁТЗ(значение1; значение2; …) − количество значений в списке аргументов и непустых ячеек;

Логические функции:

ЕСЛИ(условие; значение_если_истина; значение_если_ложь) − проверяет условие. Если условие верно, то функция возвращает одно значение, если условие не выполняется − другое;

И(условие1; условие2;...) − возвращает значение ИСТИНА, если истинны все аргументы;

ИЛИ(условие1; условие2;...) − возвращает ИСТИНА, если значение ИСТИНА имеет хотя бы один аргумент. В противном случае возвращает значение ЛОЖЬ.

Дата и время:

СЕГОДНЯ() − возвращает текущую дату в формате даты;

ДЕНЬНЕД(дата_в_числовом_формате; тип) − число от 1 до 7, соответствующее номеру дня недели для текущей даты, для России тип равен 2.

Текстовые:

НАЙТИ(искомый_текст; просматриваемый_текст; нач_позиция) − возвращает позицию начала текущей строки в содержащей ее строке;

ПРОПИСН(текст) − делает все буквы в текстовой строке прописными;

СТРОЧН(текст) − делает все буквы в строке текста строчными;

СЦЕПИТЬ(текст1; текст2; …) − объединяет несколько текстовых строк в одну;

ТЕКСТ(значение; формат) − форматирует число и преобразует его в текст.

В категории Полный алфавитный перечень указаны все встроенные функции Excel.

Пример 1. Вычислить значения функции

(1)

при b 1 = 1,3, если m изменяется в промежутке [5; 11] с шагом Δ m = 0,6.

Решение.

1 В ячейку А1 введите текст b 1=, в ячейку B1 – текст m =; в ячейку C1 – текст fun 1=.

Примечание − В активную ячейку Excel можно ввести либо текст, либо число, либо формулу. Если ввести в ячейку последовательность символов b 1=1,3, то эта запись будет восприниматься табличным процессором как текст, который, несмотря на знак =, не предназначен для выполнения математических операций, а имеет только информационное назначение.

2 В ячейке A2 наберите число 1,3. В Excel в качестве десятичного разделителя по умолчанию используется запятая.

3 В ячейку B2 введите начальное значение переменной m, равное 5.

4 В ячейку B3 введите значение 5,6, которое подсчитывается как начальное значение 5 плюс шаг 0,6.

5 Выполните автозаполнение ячеек в столбце B значениями переменной m.

Для этого выделите две ячейки B2 и B3, содержащие первое и второе значения этой переменной, как показано на рисунке 4.

Рисунок 4 – Выделение двух ячеек

Наведите указатель мыши на маркер заполнения (правый нижний угол рамки, обрамляющей ячейки B2 и B3) так, чтобы указатель мыши принял вид крестика É. Нажав в этом положении левую кнопку мыши, протяните маркер заполнения вниз, не отпуская, пока на всплывающей подсказке не отобразится последнее значение переменной m, равное 11, как показано на рисунке 5. После этого отпустите левую кнопку мыши. В ячейках столбца отобразятся все значения переменной m от 5 до 11 с шагом 0,6 (рисунок 6).

Рисунок 5 – Автозаполнение ячеек в столбце		Рисунок 6 – Результаты автозаполнения ячеек

6 В ячейке C2 наберите знак = и выражение для вычисления значения функции fun 1(m). Вызовите мастер вставки функции щелчком мыши на кнопке панели инструментов или командой Вставка / Функция. Выберите категорию функций Математические, а в ней − функцию с именем КОРЕНЬ и щелкните на кнопке OK. Появится диалоговое окно Аргументы функции, показанное на рисунке 7.

Рисунок 7 – Диалоговое окно Аргументы функции

По условию задачи операция извлечения квадратного корня применяется к значению переменной m. Но в качестве аргумента функции в Excel, как правило, используется не имя переменной, а адрес ячейки, в нашем примере это B2, в которой хранится ее значение. Причем для ввода необходимого адреса достаточно щелкнуть левой кнопкой мыши на соответствующей ячейке. При этом в строке формул отобразится =КОРЕНЬ(B2), а ячейка B2 выделится мерцающей рамкой.

Примечание − Не следует закрывать диалоговое окно Мастера функции, пока формула не набрана целиком. Это позволит откорректировать любую функцию в составе формулы, перейдя на нее щелчком левой кнопкой мыши, осуществляемым на имени функции в строке формул.

Перейдите в строку формул и наберите знак деления / (можно использовать клавишу дополнительной клавиатуры), а затем открывающуюся скобку (Shift 9). Так как окно Мастера функций открыто, для возведения тангенса в квадрат обратитесь к списку в начале строки формул, откуда выберите пункт Другие функции…:

В категории функций Математические найдите функцию СТЕПЕНЬ и щелкните на кнопке OK. В Excel отобразится диалоговое окно Аргументы функции, показанное на рисунке 8.

Рисунок 8 – Вставка обращения к степенной функции

В поле Степень напечатайте число 2 и перейдите в строку Число, чтобы ввести тангенс переменной m. В категории Математические окна Мастер функций выберите TAN. В строке Число укажите адрес B2 (рисунок 9), щелкнув для этого на ячейке B2, содержащей число 5.

Рисунок 9 – Вставка обращения к функции тангенс

В строке формул отобразится:

=КОРЕНЬ(B2)/(СТЕПЕНЬ(TAN(B2);2)

Наберите далее знак + и адрес ячейки A2, где находится значение константы b 1. Так как оно единственное, то его следует зафиксировать, создав абсолютную ссылку. Для отображения знаков $ установите курсор на A2 и нажмите клавишу F4.

Введите закрывающую скобку (Shift 0) и нажмите клавишу Enter.

Итоговая формула должна иметь вид:

=КОРЕНЬ(B2)/(СТЕПЕНЬ(TAN(B2);2)+$A$2)

7 Выполните автозаполнение столбца С значениями функции fun 1 для соответствующих значений переменной m из столбца B. Для этого выделите ячейку C2 и наведите указатель мыши на правый нижний угол ее рамки так, чтобы указатель мыши принял вид тонкого черного крестика É. Протяните указатель мыши É вниз до последнего значения переменной m.

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

Рисунок 10 – Режим отображения чисел		Рисунок 11 – Режим отображения формул

Чтобы перейти в режим отображения формул в электронной таблице, вызовите командой Сервис / Параметры диалоговое окно Параметры и на вкладке Вид в поле Параметры окна установите флажок Формулы.

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

Пример 2. Создать одномерный массив MS, состоящий из десяти произвольных чисел. Для каждого элемента MS вычислить

, (2)

где р 9 = 5,7.

Найти сумму, произведение элементов массива RES, а также его минимальный элемент. Подсчитать количество значений RES, больших числа 5.

Решение.

1 Введите в ячейку А1 поясняющий текст р 9=, в ячейку В1 − текст MS =, а в ячейку С1 − текст RES =.

2 В ячейку А2 введите числовое значение р 9, а именно, 5,7.

3 Заполните ячейки В2 − В11 произвольными значениями массива MS.

4 В ячейке С2 наберите формулу

=СТЕПЕНЬ(В2+$A$2;1/3)+ABS(B2)

Адреса ячеек лучше вводить щелчком левой кнопкой мыши на соответствующей ячейке.

5 Примените инструмент автозаполнения для диапазона ячеек С2:С11, используя формулу в ячейке С2.

6 Введите в ячейку В13 поясняющий текст СУММА(RES)=.

7 Перейдите в ячейку С13 и щелкните на кнопке (Автосумма) панели инструментов Стандартная, чтобы вычислить сумму элементов массива RES. В строке формул и в ячейке С13 отобразится формула

=СУММ(С2:С12)

Откорректируйте ее, изменив ссылку (адрес) С12 на С11. Для этого выделите диапазон ячеек С2:С11, используя указатель мыши, либо просто установите текстовый курсор на ссылке С12 в формуле, а затем щелкните на ячейке С11. Нажмите клавишу Enter.

8 В ячейку В14 введите вспомогательный текст MIN(RES)=, а в ячейку С14 знак = (равно).

Щелчком мыши на кнопке панели инструментов Стандартная запустите Мастер функций, выберите категорию Статистические, в перечне функций выделите МИН и щелкните на кнопке OK. В открывшемся диалоговом окне укажите диапазон поиска минимального элемента С2:С11 и щелкните на кнопке OK. В ячейке С14 отобразится значение минимального элемента массива RES.

9 Вычисление произведения элементов массива RES производится аналогичным образом с помощью функции ПРОИЗВЕД(С2:С11) из категории Математические.

10 Количество элементов RES, больших числа 5, найдите посредством функции СЧЁТЕСЛИ(диапазон; критерий) из категории Статистические.

Итоговая электронная таблица в режиме отображения формул приведена на рисунке 12.

Рисунок 12 – Фрагмент электронной таблицы в режиме отображения формул

1.1.5 Графические возможности Excel. Построение графика функции

Для построения графиков и диаграмм в Excel используется Мастер диаграмм, вызываемый командой Вставка / Диаграмма или щелчком мыши на кнопке панели инструментов Стандартная.

В диалоговом окне Мастер диаграмм работа проводится в четыре этапа (шага).

1 На первом шаге выбирается тип диаграммы. Для построения графика функциональной зависимости следует выбрать тип График или Точечная.

2 На втором шаге определяется источник данных диаграммы (диапазон данных), добавляются или удаляются ряды данных.

3 Диалоговое окно Мастер диаграмм: параметры диаграммы третьего шага (рисунок 13) содержит шесть вкладок, на каждой из которых выбираются опции, соответствующие имени вкладки.

Рисунок 13 – Мастер диаграмм

4 Место расположения диаграммы − на имеющемся или на отдельном листе − определяется на последнем, четвертом шаге применения инструмента Мастер диаграмм.

Пример 3. Построить график функции

(3)

при b 1 = 1,3.

Решение.

1 Для построения графика выделите диапазон ячеек С1:С12 со значениями функции fun 1(m).

2 Вызовите Мастер диаграмм командой Вставка / Диаграмма или щелчком на кнопке панели инструментов Стандартная.

3 Выберите тип диаграммы Точечная и вид диаграммы со значениями, соединенными сглаживающими линиями (первая во втором ряду) на вкладке Стандартные. Щелкните на кнопке Далее для перехода ко второму шагу Мастера диаграмм.

4 На вкладке Диапазон данных в поле Диапазон видна автоматически сформированная ссылка на предварительно выделенный диапазон данных для построения графика в виде =Лист1!$C$1:$C$12 и выбрана опция Ряды в столбцах.

5 Откройте вкладку Ряд, поместите текстовый курсор в пустое поле Значения X, показанное на рисунке 14, и щелкните на кнопке .

Рисунок 14 – Поле Значения X в диалоговом окне Мастер диаграмм

Окно Мастер диаграмм свернется и примет вид, изображенный на рисунке 15, предоставив возможность пользователю выделить на рабочем листе диапазон ячеек B1:B12, хранящих последовательность значений переменной m.

Рисунок 15 – Ссылка на значения m в свернутом окне Мастер диаграмм

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

В поле Значения X отобразится ссылка

=Лист1!$B$1:$B$12

Щелкните на кнопке Далее для перехода к шагу 3.

6 В поле Название диаграммы наберите текст Значения функции fun1; в поле Ось X (категорий) − аргумент m; в поле Ось Y(значений) − fun 1.

7 Перейдите на вкладку Оси и выберите параметры графика, показанные на рисунке 16.

Рисунок 16 – Выбор параметров графика

8 На вкладке Линии сетки выберите вариант основные линии по оси X и по оси Y.

9 Перейдите на вкладку Легенда и уберите флажок Добавить легенду.

10 На вкладке Подписи данных дополните подписи данных значениями аргумента и функции.

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

Диаграмма должна принять вид, показанный на рисунке 17.

Рисунок 17 – Созданный график функции fun 1(m)

Примечания

1 Для изменения местоположения диаграммы следует установить указатель мыши в любом ее месте и, не отпуская левую кнопку мыши, тянуть диаграмму до нужного положения на листе.

2 Чтобы изменить размеры диаграммы, сначала щелкают левой клавишей мыши в области диаграммы. Затем наводят указатель мыши на маркер границы так, чтобы он принял вид двунаправленной стрелки, и тянут, не отпуская левую кнопку, пока не будут достигнуты требуемые размеры.

3 Для изменения параметров диаграммы дважды щелкают левой кнопкой мыши в том месте, которое нуждается в изменении. Можно также щелкнуть правой кнопкой мыши в области диаграммы для вызова контекстного меню и выбрать нужный пункт из списка. В результате появляется окно, в котором настраиваются желаемые опции.