Как делать формулы в excel

Можно создать простую формулу для сложения, вычитания, умножения и деления числовых значений на листе. Простые формулы всегда начинаются со знака равенства ( =), за которым следуют константы, т. е. числовые значения, и операторы вычисления, такие как плюс ( +), минус ( -), звездочка ( *) и косая черта ( /).

В качестве примера рассмотрим простую формулу.

Выделите на листе ячейку, в которую необходимо ввести формулу.

Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении.

В нашем примере введите =1+1.

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

В соответствии со стандартным порядком математических операций, умножение и деление выполняются до сложения и вычитания.

Нажмите клавишу ВВОД (Windows) или Return (Mac).

Рассмотрим другой вариант простой формулы. Введите =5+2*3 в другой ячейке и нажмите клавишу ВВОД или Return. Excel перемножит два последних числа и добавит первое число к результату умножения.

Использование автосуммирования

Для быстрого суммирования чисел в столбце или строке можно использовать кнопку "Автосумма". Выберите ячейку рядом с числами, которые необходимо сложить, нажмите кнопку Автосумма на вкладке Главная, а затем нажмите клавишу ВВОД (Windows) или Return (Mac).

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Примечание: Также в ячейке можно ввести ALT+= (Windows) или ALT+ += (Mac), и Excel автоматически вставит функцию СУММ.

Пример: чтобы сложить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая находится прямо под столбцом с числами. Затем нажмите кнопку Автосумма. В ячейке В7 появляется формула, и Excel выделяет ячейки, которые суммируются.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

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

Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.

Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.

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

Примечание: Чтобы эти формулы выводили результат, выделите их и нажмите клавишу F2, а затем — ВВОД (Windows) или Return (Mac).

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

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.

При изменении значений в ячейках формула автоматически пересчитывает результат.

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

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

Как в формуле Excel обозначить постоянную ячейку

Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.

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

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:
2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.
3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+«=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

• $В$2 – при копировании остаются постоянными столбец и строка;
• B$2 – при копировании неизменна строка;
• $B2 – столбец не изменяется.

Как составить таблицу в Excel с формулами

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

Простейшие формулы заполнения таблиц в Excel:

1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+"=", чтобы вставить столбец.
2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

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

Вычисление по формулам – пожалуй, самая полезная функция программы Excel. Она не только экономит время пользователя, но и исключает возможность арифметических ошибок, допускаемых человеком. Конечно, эти плюсы достижимы только тогда, когда формулы созданы и работают правильно. Как и в любом деле, учиться грамотно составлять формулы в Эксель нужно с простейших и планомерно двигаться к более сложным вычислениям.

В этом уроке мы предоставим расширенное практическое руководство по тому, как правильно создавать формулы в Microsoft Excel.

Составление элементарных формул

Проще всего в Эксель создаются формулы из четырех арифметических действий — сложения, вычитания, умножения и деления. При этом данные для вычислений должны находиться в разных ячейках/столбцах/строках.

Для начала нужно выбрать свободную ячейку и напечатать в ней знак “=”. Для программы это будет сигналом, что в этой ячейке будет производиться расчет по формуле.

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

Эти 2 способа выше дублируют друг друга и можно выбрать тот, который больше по душе.
Далее начинаем писать формулу. Допустим, мы хотим просуммировать ячейки B2-B7. Тогда в ячейке B8 (можно выбрать любую другу свободную ячейку) после знака “=” щелкаем курсором по B2 (ее контур начинает пульсировать, а координата ячейки появляется в формуле), далее ставим знак “+”, выделяем следующую ячейку, далее снова знак “+” и так далее. В конечном виде формула выглядит так: =B2+B3+B4+B5+B6+B7

Как только мы перечислим все ячейки, нажимаем клавишу “ENTER”, чтобы получить результат.

Оператор формул в Excel

Вместо знака “+” в формуле можно использовать знаки вычитания, умножения и деления, при этом в Эксель есть специальные знаки для этих математических действий, которые называются операторами формул:

• Плюс – это знак “+”
• Минус – это знак “-” (дефис).
• Умножение – это знак “*” (звездочка).
• Деление – это знак “/” (слэш или косая черта).
• Возведение в степень – это знак “^” (циркумфлекс, находится на клавише с цифрой 6, печатается вместе с зажатой клавишей Shift).

А так могут выглядеть формулы в ячейке:

Примечание: все знаки должны идти без пробелов.

Также, в формулах может быть задействовано сколько угодно ячеек из различных строк и столбцов (=А1+А2+А3+А4+В6… и т.д), а результирующей может быть любая свободная ячейка.

Примеры вычислений

Попробуем применить полученные знания на деле. Например, у нас есть заполненная таблица. В первом столбце указано наименование товара (разновидности велосипедов), во втором столбце – количество проданных штук, в третьем — цена за 1 штуку. Мы можем посчитать, на какую общую сумму был продан каждый вид велосипеда. Сделаем это, умножив цену за 1 штуку на количество проданных штук.

В ячейке D2, которая станет результирующей, ставим знак “=”, далее выделяем ячейку B2, ставим знак “*” и выделяем ячейку C2. Так формула выглядит в конечном виде: =B2*C2

Формула готова. Жмем “ENTER” и получаем результат умножения.

Теперь то же самое действие можно выполнить для остальных наименований в других строках. Причем, создавать каждый раз одинаковую формулу вовсе не обязательно, более того, это будет лишней тратой времени.В Excel на этот случай есть отличный способ. Щелкаем на результирующей ячейке. Ее контур в правом нижнем углу имеет форму небольшого квадрата. Зажав этот квадрат левой кнопкой мыши, тянем курсор, который принял вид плюса, вниз до последней строки, в которой хотим произвести аналогичный расчет.

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

Создание формул со скобками

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

Допустим, у нас есть данные по продажам за 1 и 2 квартала, при этом цена товара оставалась неизменной. Узнать нужно общую сумму реализованного товара за 1-2 кварталы.

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

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

Ставим курсор на результирующую ячейку (E2), пишем знак “=”, далее открываем скобку, в ней складываем ячейки B2 и C2, далее скобку закрываем, ставим знак умножения и, наконец, координаты ячейки D2. Так формула выглядит в конечном виде: =(B2+C2)*D2

Формула готова. Теперь жмем клавишу “ENTER”, чтобы увидеть результат вычислений.

При желании и необходимости, формулу можно протянуть на остальные строки, как это было описано выше.

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

Использование Excel в качестве калькулятора

Программу Excel можно использовать как обычный калькулятор. Ставим знак “=” в любой ячейке, пишем нужную формулу, а затем нажимаем ” ENTER”, чтобы получить результат.

Заключение

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