Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.
Конструкция формулы включает в себя: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки содержащие аргументы и другие формулы. На примере разберем практическое применение формул для начинающих пользователей.
Формулы в Excel для чайников
Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.
В Excel применяются стандартные математические операторы:
Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.
Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.
При изменении значений в ячейках формула автоматически пересчитывает результат.
Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.
В нашем примере:
- Поставили курсор в ячейку В3 и ввели =.
- Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
- Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.
Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:
- %, ^;
- *, /;
- +, -.
Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.
Как в формуле Excel обозначить постоянную ячейку
Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.
Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.
Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Программа Microsoft Excel Office имеет в себе множество удобных функций, которые помогают не заниматься долгими расчётами перед внесением данных в таблицы или диаграммы. Для подсчёта некоторых значений, например сложения, вычитания, отношения и других, можно использовать изначально заданные формулы. Их вы можете внести самостоятельно либо выбрать из списка уже существующих. Научитесь вставлять формулу поэтапно в этой статье.
Откройте программу Эксель. Перед вами будет рабочая область, удостоверьтесь, что вкладка “Главная” открыта. В пустой строке, расположенной между шапкой программы и её рабочей областью, вы увидите поле для ввода формулы, она вам понадобится после ввода аргументов, то есть, чисел, которые нужно посчитать.
Теперь вы знаете как именно создать формулу в программе Эксель и посмотреть их расшифровку в мастере функций.
- Наиболее распространенная координатная ссылка - это использование буквы или букв, представляющих столбец, а за ней номер строки, в которой находится ячейка: например, А1 указывает на ячейку в столбце А и строке 1. Если вы добавите строки над ячейкой, то ссылка на ячейку изменится, чтобы отобразить ее новую позицию; добавление строки над ячейкой А1 и столбца слева от нее, изменит ссылку на нее на В2 во всех формулах, которые ее используют.
- Разновидность этой формулы - сделать строковую либо столбцовую ссылки абсолютными, добавив знак доллара ($) перед ними. Хотя ссылка на ячейку A1 изменится, если будет добавлена строка над ней или столбец слева от нее, ссылка $A$1 всегда будет указывать на верхнюю левую левую ячейку на листе; таким образом, в формуле, ячейка $A$1 может иметь другое или даже недопустимое значение в формуле, если строки или столбцы вставляются на лист. (При желании, вы можете использовать абсолютную ссылку для столбца или строки отдельно, например, $A1 или A$1).
- Другой способ сделать ссылку на ячейку - это числовой метод, в формате RxCy, где "R" указывает на "строку," "C" указывает на "столбец," а "x" и "y" - номера строки и столбца соответственно. Например, ссылка R5C4 в этом формате указывает на то же место, что и ссылка $D$5. Ссылка типа RxCy указывает на ячейку относительно левого верхнего угла листа, то есть есть если вы вставите строку над ячейкой или столбец слева от ячейки, то ссылка на нее изменится.
- Если вы используете в формуле только знак равно и ссылку на единственную ячейку, то вы, фактически, копируете значение из другой ячейки в новую ячейку. Например, ввод "=A2" в ячейку B3 скопирует значение, введенное в ячейку А2, в ячейку В3. Чтобы скопировать значение из ячейки на другом листе, добавьте имя листа, а за ним восклицательный знак (!). Ввод "=Лист1!B6" in Cell F7 на Лист2 отобразит значение ячейки В6 на Лист1 в ячейке F7 на Лист2.
-
Используйте арифметические операторы для базовых операций. Microsoft Excel может выполнить все базовые арифметические операции: сложение, вычитание, умножение и деление, а также возведение в степень. Некоторые операции требуют других символов, чем те, которые мы используем при написании вручную. Список операторов дан ниже, в порядке приоритета (то есть порядок, в котором Excel обрабатывает арифметические операции):
- Отрицание: Знак минус (-). Эта операция возвращает число, противоположное по знаку числу или ссылке на ячейку (это эквивалентно умножению на -1). Этот оператор нужно ставить перед числом.
- Процент: Знак процента (%). Эта операция вернет десятичный эквивалент процента числовой константы.Этот оператор нужно ставить после числа.
- Возведение в степень: Знак вставки (^). Эта операция возводит число (либо значение ссылки), стоящее до знака вставки, в степень, равную числу (либо значению ссылки) после знака вставки. Например, "=3^2" - это 9.
- Умножение: Звездочка (*). Звездочка используется для умножения, чтобы умножение не путали с буквой "x."
- Деление: Косая черта (/). Умножение и деление имеют одинаковый приоритет, они выполняются слева направо.
- Сложение: Знак плюс (+).
- Вычитание: Знак минус (-). У сложения и вычитания одинаковый приоритет, они выполняются слева направо.
-
Используйте операторы сравнения, чтобы сравнить значения в ячейках. Чаще всего, вы буде использовать операторы сравнения с функцией ЕСЛИ. Вы ставите ссылку на ячейку, числовую константу или функцию, которая возвращает числовое значение, по обе стороны оператора сравнения. Операторы сравнения указаны ниже:
- Равно: Знак равно (=).
- Не равно (<>).
- Меньше (<).
- Меньше или равно (<=).
- Больше (>).
- Больше или равно (>=).
-
Используйте амперсанд (&), чтобы соединить текстовые строки. Соединение текстовых строк в одну называется конкатенация, и амперсанд - это оператор, который делает в Excel конкатенацию. Можно использовать амперсанд со строками или ссылками на строки; например, ввод "=A1&B2" в ячейку C3 отобразит "АВТОЗАВОД", если в ячейку A1 введено "АВТО", а в ячейку B2 введено "ЗАВОД".
-
Используйте ссылочные операторы при работе с областью ячеек. Наиболее часто вы будете использовать область ячеек с функциями Excel, такими как СУММ, которая находит сумму значений области ячеек. Excel использует 3 ссылочных оператор:
- Оператор области: двоеточие (:). Оператор области указывает на все ячейки в области, которая начинается с ячейки перед двоеточием и заканчивается ячейкой после двоеточия. Обычно, все ячейки в той же строке или столбце; "=СУММ(B6:B12)" отобразит результат сложения значений ячеек B6, B7, B8, B9, B10, B11, B12, в то время как "=СРЗНАЧ(B6:F6)" отобразит среднее арифметическое значений ячеек с B6 до F6.
- Оператор объединения: запятая (,). Оператор объединения включает все ячейки или области ячеек до и после него; "=СУММ(B6:B12, C6:C12)" суммирует значения ячеек с B6 до B12 и с C6 до C12.
- Оператор пересечения: пробел (). Оператор пересечения ищет ячейки, общие для 2-х или более областей; например, "=B5:D5 C4:C6" это только значение ячейки C5, поскольку она встречается и с первой, и второй области.
-
Используйте скобки, чтобы указать аргументы функций и переопределить порядок вычисления операторов. Скобки в Excel используются в двух случаях: определить аргументы функции и указать иной порядок вычисления.
- Функции - это заранее определенные формулы. Такие, как SIN, COS или TAN, требуют один аргумент, в то время как ЕСЛИ, СУММ или СРЗНАЧ могут принимать много аргументов. Аргументы внутри функции отделяются запятой, например, "=ЕСЛИ (A4 >=0, "ПОЛОЖИТЕЛЬНОЕ," "ОТРИЦАТЕЛЬНОЕ")" для функции ЕСЛИ. Функции могут быть вложены в другие функции, до 64-х уровней.
- В формулах с математическими операциями, операции внутри скобок выполняются раньше, чем вне их; например, в "=A4+B4*C4," B4 умножается на C4 и результат прибавляется к A4, а в "=(A4+B4)*C4," сначала складываются A4 и B4, а затем результат умножается на C4. Скобки в операциях могут быть вложены одна в другую, операция внутри самой внутренней пары скобок будет выполнена первой.
- Не имеет значения встречаются ли вложенные скобки в математических операциях или во вложенных скобках, всегда следите за тем, чтобы количество открывающихся скобок равнялось количеству закрывающихся, иначе получите сообщение об ошибке.
Начинайте любую формулу со знака равно (=). Знак равно говорит Excel, что набор символов, которые вы вводите в ячейку - это математическая формула. Если вы забудете знак равно, то Excel будет трактовать ввод как набор символов.
Используйте координатные ссылки на ячейки, которые содержат значения, используемые в формуле. Хотя вы можете вводить числовые константы в свои формулы, но в большинстве случаев вам нужно будет использовать значения в других ячейках (или результаты других формул, отображаемые в тех ячейках) в формулах. Вы обращаетесь к тем ячейкам, используя координатную ссылку строки и столбца, в которых находится ячейка. Есть несколько форматов:
Программа Microsoft Excel предназначена для работы с данными, таблицами и числовыми значениями. Для удобства вычислений существуют формулы в «Экселе», позволяющие получать точные расчеты.
Редактирование формул
Любую формулу, которая находится в ячейке, можно моментально отредактировать. Для этого нужно выделить определенную ячейку и установить курсор в строке формул, где можно сделать все необходимые изменения. Некоторым пользователям удобнее редактировать непосредственно в ячейке с формулой: для этого нужно кликнуть по ней пару раз.
Когда все необходимые изменения будут введены, нужно нажать кнопки Enter или Tab. После этого действия программа Excel выполнит пересчет и отобразит результат.
В том случае, когда формула введена неправильно или вследствие удаления содержимого какой-либо ячейки происходит ошибка в вычислениях, программа обязательно предупредит пользователя об этом. Рядом с клеточкой, где содержится неточность, появится в ромбике. Приложение также укажет, какая именно неточность была допущена.
Трактовка ошибок в программе Excel
- Если результат расчетов не помещается в ячейке или число даты и времени отрицательное, появится символ #####.
- Если используется аргумент формулы, который недопустим, высветится #ЗНАЧ!
- Когда пользователь пытается делить на ноль, программа оповестит его символом #ДЕЛ/0!
- В том случае, если имя некорректно и Excel не может его прочитать, он выдает ошибку #ИМЯ?
- Неопределенные данные обозначаются #Н/Д.
- Если в состав формулы входит ссылка на недопустимую ячейку, символ #ССЫЛКА! оповестит об этом.
- MS Excel имеет определенный диапазон чисел, и в том случае, когда результат вычислений выпадает из этого диапазона, программа выдаст символ #ПУСТО!
Пользователь должен помнить, что ошибки могут появляться не только потому, что формула содержит некорректные данные, но и тогда, когда в ячейке содержится неправильная информация.
Если таблица содержит огромное количество ссылок на ячейки, то при расчетах могут возникнуть ошибки, выявить которые будет очень сложно. Чтобы пользователь не испытывал неудобств, в программе Excel существует специальный инструмент, при помощи которого можно видеть зависимые и влияющие ячейки.
Влияющими называются те ячейки, на которые ссылаются формулы, а зависимые ячейки - это те, которые содержат формулы, ссылающиеся на координаты клеток.
При помощи команд на ленте можно графически отобразить связи между формулами и ячейками. Это осуществляется посредством стрелок зависимостей. Чтобы их убрать, следует нажать «Убрать стрелки».
Адреса ячеек: абсолютные и относительные
Концепция относительной адресации позволяет копировать формулы из одной ячейки в другую, автоматически изменяя адреса. Эта функция удобна, но иногда возникает необходимость использовать значение конкретной клеточки и ссылка не должна изменяться. В этом случае нужно применять абсолютную ссылку, которая будет оставаться неизменной.
- Изменять ссылку на столбец, при этом сохраняя на строки.
- Сохранять ссылки на столбец, но изменять ссылки на строки.
- Не менять ссылки как на строки, так и на столбцы.
Как преобразовать относительную ссылку в абсолютную?
Ячейку нужно выделить, затем ввести знак равенства, после чего кликнуть по клетке. После нажатия клавиши F4 перед буквой столбца и номером строки Excel установить знак доллара. Повторные нажатия позволят перейти от одного типа ссылок к другим. При желании $ можно вводить вручную.
Формулы в Excel
Формулы в «Экселе» необходимы для вычислений по заданным величинам. Сложность операций может быть абсолютно разная, но в любом случае вместо ввода всех адресов ячеек используется конкретная функция. В качестве аргумента указывается диапазон.
Все инструменты для работы с функциями находятся в закладке «Формулы». Для удобства все функции разделены на группы в зависимости от сложности вычислений и типа решаемых задач.
Перед тем как сделать формулу в «Экселе», нужно поставить знак равенства. После этого необходимо указать имя функции. Оно представлено в виде аббревиатуры из заглавных букв, которые определяют значение функции. Далее в скобочках фиксируются аргументы функции, которые используются для получения результата. Аргументом может быть как конкретное число, так и ссылка на клеточку, серия ссылок или диапазон ячеек. Некоторые формулы в «Экселе» в качестве аргумента имеют текст или числа, другие - время и дату.
Мастер функций
В «Эксель» формула суммы имеет простое определение, но не все функции так просты. Некоторые требуют сложного синтаксического написания и аргументов определенных типов. Без ошибок составить такую формулу зачастую очень нелегко. Разработчики программы учли этот нюанс, поэтому в Ecxel есть помощник по составлению - «Мастер функций».
Чтобы с его помощью вводить формулы в «Экселе», необходимо выбрать значок «Вставить функцию». Он расположен с левой стороны от «Строки формул». Затем пользователю нужно подобрать подходящую категорию формулы.
Чуть ниже перечня категорий отображается краткое описание каждой функции, которая будет выделена. Там же можно узнать информацию и по аргументам. Сделав выбор, стоит подтвердить его, нажав ОК. После этого появится окошко «Аргументы функции».
Внизу окна есть справка, где указывается назначение формулы, аргумента и конечного значения.
Функция, которая выступает аргументом, имеет название вложенной. Например, и сумма. В «Экселе» формула как суммирует значения ячеек выбранного диапазона, так и выводит
Вводить ссылки можно и при помощи мышки, щелкая кнопкой по выбранным ячейкам, и вручную. Все значения будут подставляться в текущее поле. Это диалоговое окно при необходимости можно сворачивать или уменьшать. Результат вычисления появится после нажатия ОК.
Для удобства пользователей разработана формула «Эксель», проценты по которой не составит особого труда подсчитать.
Формула представляет собой выражение, которое вычисляет значение ячейки. Функции – это предопределенные формулы и они уже встроены в Excel.
Например, на рисунке ниже ячейка А3 содержит формулу, которая складывает значения ячеек А2 и A1 .
Ещё один пример. Ячейка A3 содержит функцию SUM (СУММ), которая вычисляет сумму диапазона A1:A2 .
SUM(A1:A2)
=СУММ(A1:A2)
Ввод формулы
Чтобы ввести формулу, следуйте инструкции ниже:
Совет: Вместо того, чтобы вручную набирать А1 и А2 , просто кликните по ячейкам A1 и A2 .
Редактирование формул
Когда вы выделяете ячейку, Excel показывает значение или формулу, находящиеся в ячейке, в строке формул.
- Чтобы отредактировать формулу, кликните по строке формул и измените формулу.
Приоритет операций
Excel использует встроенный порядок, по которому ведутся расчеты. Если часть формулы в скобках, она будет вычислена в первую очередь. Затем выполняется умножение или деление. После этого Excel будет складывать и вычитать. Смотрите пример ниже:
Сперва Excel умножает (A1*A2 ), затем добавляет значение ячейки A3 к этому результату.
Другой пример:
Сначала Excel вычисляет значение в круглых скобках (A2+A3 ), потом умножает полученный результат на величину ячейки A1 .
Копировать/вставить формулу
Когда вы копируете формулу, Excel автоматически подстраивает ссылки для каждой новой ячейки, в которую копируется формула. Чтобы понять это, выполните следующие действия:
Вставка функции
Все функции имеют одинаковую структуру. Например:
SUM(A1:A4)
СУММ(A1:A4)
Название этой функции – SUM (СУММ). Выражение между скобками (аргументы) означает, что мы задали диапазон A1:A4 в качестве входных данных. Эта функция складывает значения в ячейках A1 , A2 , A3 и A4 . Запомнить, какие функции и аргументы использовать для каждой конкретной задачи не просто. К счастью, в Excel есть команда Insert Function (Вставить функцию).
Чтобы вставить функцию, сделайте следующее:
Примечание: Вместо того, чтобы использовать инструмент «Вставить функцию », просто наберите =СЧЕТЕСЛИ(A1:C2,”>5″). Когда напечатаете ” =СЧЁТЕСЛИ(“, вместо ввода “A1:C2” вручную выделите мышью этот диапазон.
