Как создавать формулы в экселе. Формула excel среднего значения. Математические функции и графики
Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.
Конструкция формулы включает в себя: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки содержащие аргументы и другие формулы. На примере разберем практическое применение формул для начинающих пользователей.
Формулы в Excel для чайников
Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.
В Excel применяются стандартные математические операторы:
Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.
Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.
При изменении значений в ячейках формула автоматически пересчитывает результат.
Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.
В нашем примере:
- Поставили курсор в ячейку В3 и ввели =.
- Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
- Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.
Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:
- %, ^;
- *, /;
- +, -.
Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.
Как в формуле Excel обозначить постоянную ячейку
Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.
Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.
Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Если вы читали предыдущие , то должны быть в курсе, что запись формулы начинается со знака «Равно». Когда возникает необходимость написать в ячейке этот знак без формулы, программа настойчиво продолжает считать такую запись началом формулы. При клике в другую ячейку после знака прописывается адрес ячейки. В этом случае есть несколько способов перехитрить Excel.
Пример использования знаков "умножение" и "равно"Решение:
Перед написанием знака равно, плюс (сложение), минус (вычитание), наклонная черта (деление) или звездочки(умножение) поставить пробел или апостроф.
Почему в экселе формула не считает
Если вам приходится работать на разных компьютерах, то возможно придется столкнуться с тем, что необходимые в работе файлы Excel не производят расчет по формулам.
Неверный формат ячеек или неправильные настройки диапазонов ячеек
В Excel возникают различные ошибки с хештегом (#), такие как #ЗНАЧ!, #ССЫЛКА!, #ЧИСЛО!, #Н/Д, #ДЕЛ/0!, #ИМЯ? и #ПУСТО!. Они указывают на то, что что-то в формуле работает неправильно. Причин может быть несколько.
Вместо результата выдается #ЗНАЧ! (в версии 2010) или отображается формула в текстовом формате (в версии 2016).
Примеры ошибок в формулах
В данном примере видно, что перемножается содержимое ячеек с разным типом данных =C4*D4.
Исправление ошибки: указание правильного адреса =C4*E4 и копирование формулы на весь диапазон.
- Ошибка #ССЫЛКА! возникает, когда формула ссылается на ячейки, которые были удалены или заменены другими данными.
- Ошибка #ЧИСЛО! возникает тогда, когда формула или функция содержит недопустимое числовое значение.
- Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.
- Ошибка #ДЕЛ/0! возникает, когда число делится на ноль (0).
- Ошибка #ИМЯ? возникает из-за опечатки в имени формулы, то есть формула содержит ссылку на имя, которое не определено в Excel.
- Ошибка #ПУСТО! возникает, если задано пересечение двух областей, которые в действительности не пересекаются или использован неправильный разделитель между ссылками при указании диапазона.
Примечание: #### не указывает на ошибку, связанную с формулой, а означает, что столбец недостаточно широк для отображения содержимого ячеек. Просто перетащите границу столбца, чтобы расширить его, или воспользуйтесь параметром Главная - Формат - Автоподбор ширины столбца .
Ошибки в формулах
Зеленые треугольники в углу ячейки могут указывать на ошибку: числа записаны как текст. Числа, хранящиеся как текст, могут приводить к непредвиденным результатам.
Исправление: Выделите ячейку или диапазон ячеек. Нажмите знак "Ошибка" (смотри рисунок) и выберите нужное действие.
Пример исправления ошибок в Excel
Включен режим показа формул
Так как в обычном режиме в ячейках отображаются расчетные значения, то чтобы увидеть непосредственно расчетные формулы в Excel предусмотрен режим отображения всех формул на листе. Включение и отключение данного режима можно вызвать командой Показать формулы из вкладки Формулы в разделе Зависимости формул.
Отключен автоматический расчет по формулам
Такое возможно в файлах с большим объемом вычислений. Для того чтобы слабый компьютер не тормозил, автор файла может отключить автоматический расчет в свойствах файла.
Исправление: после изменения данных нажать кнопку F9 для обновления результатов или включить автоматический расчет. Файл – Параметры – Формулы – Параметры вычислений – Вычисления в книге: автоматически.
Формула сложения в Excel
Выполнить сложение в электронных таблицах достаточно просто. Нужно написать формулу, в которой будут указаны все ячейки, содержащие данные для сложения. Конечно же, между адресами ячеек ставим плюс. Например, =C6+C7+C8+C9+C10+C11 .
Пример вычисления суммы в Excel
Но если ячеек слишком много, то лучше воспользоваться встроенной функцией Автосумма . Для этого кликните ячейку, в которой будет выведен результат, а затем нажмите кнопку Автосумма на вкладке Формулы (выделено красной рамкой).
Пример использования функции Автосумма
Будет выделен диапазон ячеек для суммирования. Если диапазон выделился неправильно, например, выделены вертикальные ячейки, а необходимы горизонтальные, то выделите новый диапазон. Для этого нажмите левой кнопкой мыши в крайнюю ячейку нового диапазона и не отпуская кнопку проведите указатель по всем ячейкам диапазона до конечной. Завершите ввод формулы нажатием клавиши Enter на клавиатуре.
Формула округления в Excel до целого числа
Начинающие пользователи используют форматирование, с помощью которого некоторые пытаются округлить число. Однако, это никак не влияет на содержимое ячейки, о чем и указывается во всплывающей подсказке. При нажатии на кнопочку (см. рисунок) произойдет изменение формата числа, то есть изменение его видимой части, а содержимое ячейки останется неизменным. Это видно в строке формул.
Уменьшение разрядности не округляет число
Для округления числа по математическим правилам необходимо использовать встроенную функцию =ОКРУГЛ(число;число_разрядов).
Математическое округление числа с помощью встроенной функции
Написать её можно вручную или воспользоваться мастером функций на вкладке Формулы в группе Математические (смотрите рисунок).
Мастер функций Excel
Данная функция может округлять не только дробную часть числа, но и целые числа до нужного разряда. Для этого при записи формулы укажите число разрядов со знаком «минус».
Как считать проценты от числа
Для подсчета процентов в электронной таблице выберите ячейку для ввода расчетной формулы. Поставьте знак «равно», затем напишите адрес ячейки (используйте английскую раскладку), в которой находится число, процент от которого будете вычислять. Можно просто кликнуть мышкой в эту ячейку и адрес вставится автоматически. Далее ставим знак умножения и вводим число процентов, которое необходимо вычислить. Посмотрите на пример вычисления скидки при покупке товара.
Формула =C4*(1-D4)
Вычисление стоимости товара с учетом скидки
В C4 записана цена пылесоса, а в D4 – скидка в %. Необходимо вычислить стоимость товара с вычетом скидки, для этого в нашей формуле используется конструкция (1-D4) . Здесь вычисляется значение процента, на которое умножается цена товара. Для Excel запись вида 15% означает число 0.15, поэтому оно вычитается из единицы. В итоге получаем остаточную стоимость товара в 85% от первоначальной.
Вот таким нехитрым способом с помощью электронных таблиц можно быстро вычислить проценты от любого числа.
Шпаргалка с формулами Excel
Шпаргалка выполнена в виде PDF-файла. В нее включены наиболее востребованные формулы из следующих категорий: математические, текстовые, логические, статистические. Чтобы получить шпаргалку, кликните ссылку ниже.
PS: Интересные факты о реальной стоимости популярных товаров
Дорогой читатель! Вы посмотрели статью до конца.
Получили вы ответ на свой вопрос? Напишите в комментариях пару слов.
Если ответа не нашли, укажите что искали .
- Наиболее распространенная координатная ссылка - это использование буквы или букв, представляющих столбец, а за ней номер строки, в которой находится ячейка: например, А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 будет трактовать ввод как набор символов.
Используйте координатные ссылки на ячейки, которые содержат значения, используемые в формуле. Хотя вы можете вводить числовые константы в свои формулы, но в большинстве случаев вам нужно будет использовать значения в других ячейках (или результаты других формул, отображаемые в тех ячейках) в формулах. Вы обращаетесь к тем ячейкам, используя координатную ссылку строки и столбца, в которых находится ячейка. Есть несколько форматов:
Доброго времени суток дорогой читатель. Продолжаем обсуждать тему работы с офисными программами. Эксель — мощнейший инструмент офисного пакета, позволяющий автоматизировать многие математические операции. В этой статье мы поговорим о том
Как использовать формулы в Exel
Для удобства восприятия я разделил формулы на несколько групп и буду показывать работу с ними от простого к сложному.
Нужно запомнить: вне зависимости от задачи все формулы начинаются со знака «=».
Сумма ячеек в экселе
Пожалуй самая востребованная формула. Пример простого написания формулы: =A1+B1
Давайте разберем ее. Вы уже наверное знаете, что таблица делится на столбцы, строки и ячейки. Столбцы маркируются с помощью букв «A, B, C» и т.д. Для строк используются цифры «1, 2, 3…».
Для определения ячейки используется комбинация из буквы и цифры, что соответствует столбцу и номеру строки в нем. Таким образом первая ячейка будет иметь индекс «А1». Ячейка ниже первой будет иметь обозначение «А2», так как находится в столбце «А» и во второй строке.
Давайте проверим как Вы восприняли данную информацию. Какой индекс будет иметь ячейка, которая находится справа от ячейки «А1» и расположена с ней в одной строке? Правильно! Эта ячейка будет иметь обозначение «B1», так как находится в столбце «В» и в первой строке.
Давайте вернемся к нашей формуле
Сумма двух ячеек в Exel
Данная формура суммирует значение двух ячеек. В нашем случае это ячейки «А1» и «В1». Результат выводится в ту ячейку где Вы прописали формулу. Принцип я думаю понятен. Теперь покажу как это сделать на практике.
Деление и умножение ячеек в экселе
Так же довольно проста в написании. Общую теорию мы уже разобрали, поэтому сразу переходим к практике.
Среднее значение в экселе
Еще одна полезная функция.
Логические функции в экселе
Самая сложная группа формул, которая позволяет значительно упростить процедуру обработки данных. Работает по такому принципу: если значение в ячейке соответствует заданным критериям — выполнить определенное действие.
Тема логических функций довольно обширная и подробно я разберу ее в отдельной статье, но базовые принципы я Вам покажу.
Формула представляет собой выражение, которое вычисляет значение ячейки. Функции – это предопределенные формулы и они уже встроены в 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” вручную выделите мышью этот диапазон.