Олег Мединов - Excel. Мультимедийный курс

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

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

1. Выделите ячейку R2C4.

2. Введите знак =.

3. Щелкните кнопкой мыши на ячейке R1C1, введите знак + и щелкните кнопкой мыши на ячейке R1C2.

4. Введите знак умножения (*) и щелкните кнопкой мыши на ячейке R1C3.

5. Нажмите клавишу Enter. Ячейка R2C4 выдаст вам результат 310. Это неверный результат.

Попробуем разобраться, почему так получилось. Тот, кто хорошо помнит математику со школы, поймет сразу. Дело в том, что программа Excel при совершении арифметических операций использует элементарные математические правила. В математике сначала производятся операции умножения и деления, а затем – сложения и вычитания. Наша формула сначала умножила содержимое ячейки R1C2 на значение ячейки R1C3, а затем сложила этот результат с содержимым ячейки R1C1, что и привело к неверному результату.

Чтобы наша формула считала правильно, необходимо сумму ячеек R1C1 и R1C2 заключить в скобки. Операции в скобках в математике имеют приоритет, то есть сначала выполняются вычисления в скобках, а потом – за их пределами.

1. Внесите коррективы в строку формул. Исправленная формула должна выглядеть следующим образом: =(R[-1]C[-3]+R[-1]C[-2])*R[-1]C[-1].

2. Нажмите клавишу Enter. Изменения в формуле будут применены, а ячейка R2C4 вернет результат 500, что соответствует нашей задаче и верному решению (рис. 6.5).

Рис. 6.5. В формуле определен верный порядок вычисления

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

Вот некоторые математические операторы, перечисленные в порядке приоритета выполнения: % (вычисление процента), ^ (возведение в степень), * и / (умножение и деление), + и – (сложение и вычитание). Иными словами, если в формуле производится возведение в степень какого-то числа или переменной, это делается раньше умножения или деления и тем более – сложения и вычитания. Если часть формулы заключена в скобки, то сначала производится вычисление в скобках. В свою очередь, если в скобках также присутствует несколько операторов, то они выполняются в порядке приоритета, о котором мы только что говорили.

«Формулы в формулах»

При создании формул вы используете в качестве данных константы и адреса ячеек с данными. Вы можете также указать адреса ячеек, содержащих формулы. Допустим, в вашей таблице есть формула, возвращающая результат какого-то вычисления. Назовем ее Формула1. Вы создаете новую формулу (Формула2), в которой вам нужно произвести какие-то операции над результатом вычисления Формулы1. В этом случае, вам следует в Формуле2 указать адрес ячейки Формулы1 и произвести с ней какие-либо операции. При этом в Формуле2 будет обрабатываться не Формула1, а результат, который она возвращает.

Для следующего примера используйте таблицу, которую мы создали в предыдущем разделе. В ячейке R2C4 содержится формула, возвращающая произведение ячейки R1C3 и суммы ячеек R1C1 и R1C2. Мы создадим формулу в ячейке R2C5, которая будет делить содержимое ячейки R2C4 на 2.

1. Щелкните кнопкой мыши на ячейке R2C4.

2. Введите знак =.

3. Щелкните кнопкой мыши на ячейке R2C4. В формуле появится относительный адрес этой ячейки.

4. Ведите знак деления (/) и затем цифру 2.

5. Нажмите Enter. В ячейке R2C5 появится результат вычисления 250.

6. Измените значение в любой из ячеек: R1C2 или R1C3. Результат вычисления изменится и в ячейке R2C5, и в ячейке R2C4.

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

В некоторых случаях в ячейке с формулой вы можете вместо результатов увидеть одно из следующих значений: ######, #ЗНАЧ! или #ДЕЛ/0!.

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

• #ЗНАЧ! возникает при вводе некорректного значения в одну из ячеек, ссылка на которую присутствует в формуле. Например, если вы попытаетесь умножить число на текст, получите результат #ЗНАЧ!.

• #ДЕЛ/0! появляется при попытке деления на 0 или на пустое значение ячейки (которое программой Excel расценивается как 0).

Использование функций

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

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

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

Создайте чистую таблицу. Эту таблицу мы будем использовать для примеров использования функций.

Наиболее часто используемая функция в математических расчетах – это КОРЕНЬ.

1. Выделите ячейку R2C2. В эту ячейку мы будем вставлять функцию.

2. Нажмите кнопку Вставить функцию, расположенную в левой части строки формул. Появится диалоговое окно Мастер функций − шаг 1 из 2 (рис. 6.6).

Рис. 6.6. Диалоговое окно Мастер функций – шаг 1 из 2

3. В раскрывающемся списке Категория выберите пункт Математические.

4. В списке Выберите функцию выберите пункт КОРЕНЬ. Обратите внимание, что в нижней части диалогового окна появляется краткое сообщение о назначении выбранной функции.

5. Нажмите кнопку ОК. Появится диалоговое окно Аргументы функции. В поле Число следует указать число или адрес ячейки, из значения которой следует извлечь корень (рис. 6.7).

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

ПРИМЕЧАНИЕ

Вы также можете воспользоваться кнопкой, расположенной в правой части поля Число, чтобы указать ячейку R1C1 мышью.

ПРИМЕЧАНИЕ

Вы можете не пользоваться диалоговым окном Мастер функций – шаг 1 из 2, а просто ввести в ячейку R2C2 следующий текст: =КОРЕНЬ(R1C1). Со временем, скорее всего, вы запомните часто используемые функции и в некоторых случаях будете вводить их вручную.

6. Введите в поле Число адрес ячейки R1C1. В эту ячейку мы будем подставлять числовые значения, из которых следует извлечь корень.

7. Нажмите кнопку ОК. Диалоговое окно закроется, а в ячейке R2C2 появится значение 0. Функция вернула значение 0, потому что ячейка R1C1 пуста.

8. Введите число 81 в ячейку R1C1 и нажмите клавишу Enter. В ячейке R2C2 появится число 9, которое является корнем числа 81.

Какое бы число вы ни подставляли в ячейку R1C1, ячейка R2C2 вернет вам корень этого числа. Если вы введете отрицательное число в ячейку R1C1, получите результат #ЧИСЛО!. Корень не может быть извлечен из отрицательного числа. Кроме того, сообщение об ошибке (#ЗНАЧ!) вы получите при попытке извлечь корень из текста.

В следующем примере мы переведем величину угла из градусов в радианы.

1. Очистите ячейку R2C2.

2. Нажмите кнопку Вставить функцию. В появившемся диалоговом окне в списке Выберите функцию щелкните кнопкой мыши на пункте РАДИАНЫ и нажмите кнопку ОК (убедитесь, что в диалоговом окне выбрана категория Математические).

3. В поле Число появившегося диалогового окна укажите адрес ячейки R1C1.

4. Нажмите кнопку ОК. Диалоговое окно будет закрыто, а в ячейке R2C2 появится значение ячейки R1C1, переведенное в радианы.