Глава 3. Работа с формулами, использование функций и ссылок

Формулы в Excel

Все расчёты в Excel выполняются с помощью формул. Любая формула здесь начинается со знака =, и если она корректная и Excel сможет вычислить значение формулы,  то в ячейке будет отображаться это значение, а сама формула – в строке формул.

Ввод формул в ячейки Excel производится по следующим правилам:

  • Формулу необходимо вводить в ту ячейку, где должен отражаться результат вычислений.
  • Формула всегда начинается со знака «=».
  • В формулах используются арифметические операторы, такие как +, –, /, *, парные скобки, таким образом задаётся последовательность вычислений. На это необходимо обратить внимание тем, кто привык работать с калькулятором (например, формула «=2+2/2» даст результат «3», а не «2»).
  • В формулах недопустимы пробелы.
  • В формулах можно обращаться к ячейкам по их адресу или имени диапазона.

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

=?мяФункции([Аргумент1; Аргумент2; … ; АргументN])

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

По количеству аргументов функции Excel классифицируются следующим образом:

А) Без аргумента: функция =СЕГОДНЯ() отражает текущую дату, =П?() вставляет в ячейку известной константы π. Обратите внимание, что скобки нужны и в этом случае.

Б) С одним аргументом. Функция работы с текстом =ПРОПНАЧ(«?ВАН ?ВАНОВ?Ч») введёт значение «?ван ?ванович», преобразуя прописные буквы в строчные.

В) С фиксированным количеством аргументов: нужно ввести строго определённое количество аргументов в строго определённом порядке. Например, функция =ЕСЛ?(условие;значение_если_истина;значение_если_ложь) проверяет логическое условие и возвращает при его истинности одно значение, при ложности – другое, соответственно имеет три  аргумента. При попытке ввести 2 или 4 аргумента программа выдаст ошибку.

Г) С произвольным количеством аргументов. Наглядный пример – функция =СУММ(значение1;значение2;…) позволяет суммировать от 2 до нескольких сотен числовых аргументов (чисел или ссылок на ячейки/диапазоны).

При редактировании формул Excel (в виде всплывающей подсказки) сначала предлагает на выбор названия функций, имя которых начинается с введённых символов, после выбора функции подсказывает порядок подстановки аргументов. Если в предлагаемом порядке аргументы заключены в квадратные скобки, то они необязательны для использования.

Аргументом функции могут быть другие функции, сходные по типу с типом аргумента (численные, логические, текстовые). Например: функция «=СТЕПЕНЬ(П?();2)» вернёт значение, равное числу π в степени 2. Порядок вычислений совпадает с обычным порядком, принятым в арифметике: сначала вычисляются функции в скобках.

Ссылки в Excel

Ссылка в Excel – это адрес ячейки или диапазона, используемый в формулах. Например, формула =СУММ(А1:А10) ссылается на диапазон А1:А10. Важный момент: при редактировании формулы нет необходимости писать адрес ячейки в самой формуле: можно при в нужный момент выбрать мышью искомую ячейку или диапазон либо, что намного удобнее при работе с соседними ячейками, выбрать ячейку с клавиатуры — клавишами перемещения курсора).

Ссылаться можно на ячейки текущего листа Excel, текущей рабочей книги или в других книгах.

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

Практическая работа: действия с формулами и ссылками

Откройте прилагаемый файл Учебник — основы, лист «Ссылки и формулы», прочитайте текст задания. В ячейку «Наценка» внесите значение 0,3, задайте для ячейки процентный формат, выровняйте по центру.

В строке «Выручка с НДС» введите 2500000. Задайте для наглядности формат с разделителем. Скопируйте формат на все следующие ячейки (встаньте на ячейку В10, нажмите кнопку Формат по образцу в основном меню, выделите ячейки В11:В15). Введите в строку «Выручка без НДС» формулу «=В10/(1+НДС)» (здесь НДС – это имя диапазона, заданное в практической работе предыдущей главы). Как видите, в пределах одного листа удобно пользоваться адресацией именно к ячейке, в данном случае В10. Ссылку на ячейку В10 в строку формул введите, выбрав ячейку мышью (введите =, мышью выберите ячейку, дальше вводите /(1+НДС); обратите внимание, что при начале набора НДС программа сама предложит возможные имена диапазонов).

В следующей строке задайте формулу для расчёта маржинальной прибыли. Введите сумму постоянных расходов в соответствующую ячейку и рассчитайте формулами сумму чистой прибыли, используя параметр Налог_на_прибыль (формула для расчёта маржинальной прибыли: =(B11*B8)/(1+B8), можете вставить прямо в строку формул).

Рассчитайте формулой процент чистой прибыли как отношение чистой прибыли к выручке. Здесь используйте формулу проверки деления на ноль: =ЕСЛ?(B10=0;0;B16/B10), это позволит избежать неэстетичного вида незаполненной таблицы. Задайте этой ячейке процентный формат, выровняйте по центру. Результат должен быть таким:

ssilki-i-formuli

Рис.3-1 Работа с формулами

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

Рассмотрите способ использования ссылок на другие листы рабочей книги. Переключитесь на лист «Параметры решённый» файла Учебник — основы, в любом пустом месте листа нажмите «=», далее мышью нажмите на ярлык листа «Ссылки и формулы», мышью выберите ячейку В8, нажмите Enter, задайте процентный формат. В этой ячейке получится значение 30%, а в строке формул увидим формулу с полной ссылкой, включающей имя листа и адрес ячейки: =’Ссылки и формулы’!B8. Аналогичным образом можно использовать ссылки на ячейки и диапазоны в другой книге Excel, к ссылке добавится имя файла Excel. Как видите, этот способ не такой удобный, как использование имён диапазонов, рассмотренное ранее; тем не менее, этот способ адресации придётся применять не менее часто, особенно в случае больших таблиц.

 

Предыдущая глава: Глава 2. Работа с ячейками: ввод, форматирование

Следующая глава: Глава 4. Защита данных от нежелательных изменений

Содержание учебника: Содержание

Leave a Reply

Ваш e-mail не будет опубликован.