Формулы, математические операции

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

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

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

В формуле используются операторы, числа и ссылки на ячейки.

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

Excel использует стандартные операторы для формул, такие как: знак плюс для сложения (+), минус для вычитания (-), звездочка для умножения (*), косая черта для деления (/) и циркумфлекс (или просто “домик”) для возведения в степень.

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

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

Для примера рассчитаем накопления за 12 месяцев в ячейке С10. Предположим, мы знаем сумму аванса, зарплаты и расходов за месяц.

• Выберем ячейку. Поскольку ячейка пуста, можно сразу начать ввод данных.
• Начинаем ввод формулы со знака “равно”.
• Введем некую сумму аванса, затем знак “плюс” и сумму зарплаты.
• Вычтем расходы. Добавим “минус” и сумму расходов. Завершим ввод клавишей Enter.
• Получилась сумма, сэкономленная за месяц. Чтобы узнать накопление за год, умножим её на 12.
• Выберем ячейку. Если начать ввод данных, формула будет удалена. Чтобы отредактировать формулу, нажмите F2 или дважды кликните мышью по ячейке. Также можно отредактировать выражение в строке формул.
• Заключим выражение в скобки. Добавим знак умножения — “звёздочка” и число месяцев — 12. Завершим ввод кнопкой Enter.

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

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

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

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

• Выберем ячейку.
• Начинаем ввод формулы со знака “равно”. Формула в ячейке исчезнет, появится знак равно.
• Теперь кликнем по ячейке с суммой аванса. В ячейке с формулой и в строке с формулой справа от знака равно появится адрес ячейки С3, где содержится сумма аванса.
• Далее знак плюс и кликнем по ячейке с суммой зарплаты. К формуле добавится адрес этой ячейки.
• Вычтем расходы. Добавим “минус” и кликнем по ячейке с суммой расходов. Завершим ввод клавишей Enter.
• Снова рассчиталась сумма, сэкономленная за месяц. Умножим её на количество месяцев.
• Дважды кликнем по ячейке.
• Заключим выражение в скобки. Добавим знак умножения — “звёздочка” и кликнем по ячейке с числом месяцев. Завершим ввод кнопкой Enter.

Итак, формула опять рассчитает накопления за год, получилась та же сумма. А теперь посмотрим на преимущества использования ссылок. Увеличим расходы на несколько тысяч и сократим количество месяцев с 12 до 6.

Формула автоматически пересчитает сумму накоплений.

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

Сумма изменится. Адрес ячейки с зарплатной изменим другим способом, перетаскиванием.

• Дважды кликнем по ячейке с формулой. Все ячейки, на которые ссылается формула, будут выделены разноцветными границами.
• Наведём мышь на границу ячейки с зарплатой, курсор примет вид крестика со стрелками. Нажмём левую кнопку мыши, удерживая её, перетащим рамку к ячейке с другим вариантом зарплаты и отпустим кнопку. Выделение переместилось, а адрес ссылки в формуле изменился с С4 на В4.
• Завершим редактирование кнопкой Enter.

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

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

Если в ходе редактирования вы передумаете, можно нажать клавишу Esс на клавиатуре или щелкнуть команду Отмена в Строке формул, чтобы отменить изменения. После завершения редактирования отменить изменения можно командой “Отменить” в панели быстрого доступа.

[expert_bq id=»1570″]Если наше описание вам не помогло, попробуйте посмотреть приложенное ниже видео, в котором рассказываются основные моменты более детально. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для того чтобы вам было легче разобраться с описанными ранее формулами, мы подготовили специальный демо-файл, в котором составлялись все указанные примеры. Вы можете скачать его с нашего сайта совершенно бесплатно. Если во время обучения вы будете использовать готовую таблицу с формулами на основании заполненных данных, то добьетесь результата намного быстрее.

Как в excel поставить формулу на весь столбец. Использование вычисляемых столбцов в таблице Excel Online

• Выберем ячейку.
• Начинаем ввод формулы со знака “равно”. Формула в ячейке исчезнет, появится знак равно.
• Теперь кликнем по ячейке с суммой аванса. В ячейке с формулой и в строке с формулой справа от знака равно появится адрес ячейки С3, где содержится сумма аванса.
• Далее знак плюс и кликнем по ячейке с суммой зарплаты. К формуле добавится адрес этой ячейки.
• Вычтем расходы. Добавим “минус” и кликнем по ячейке с суммой расходов. Завершим ввод клавишей Enter.
• Снова рассчиталась сумма, сэкономленная за месяц. Умножим её на количество месяцев.
• Дважды кликнем по ячейке.
• Заключим выражение в скобки. Добавим знак умножения — “звёздочка” и кликнем по ячейке с числом месяцев. Завершим ввод кнопкой Enter.

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

Как формулу в excel сделать значением?

Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из «калькулятора-переростка» в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:

• Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
• Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
• Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
• Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании «сползут» все ссылки в формулах.

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

1. Выделите диапазон с формулами, которые нужно заменить на значения.
2. Скопируйте его (Ctrl+C или правой кнопкой мыши – Копировать).
3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):

либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:

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

В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

Способ 2. Ловкость рук

Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

1. выделяем диапазон с формулами на листе
2. хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
3. в появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).

При некотором навыке делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали 😉

Способ 3. Макросами для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

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

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

Способ 4. Для ленивых

Если ломает делать все вышеперечисленное, то можно поступить еще проще — установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

Ссылки по теме

Формулы в Excel – одно из самых главных достоинств этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы сможете сделать всё что угодно. При этом Эксель будет помогать на каждом шагу – практически в любом окне существуют специальные подсказки.

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

Для создания простой формулы достаточно следовать следующей инструкции:

При этом затронутые ячейки всегда подсвечиваются. Это делается для того, чтобы вы не ошиблись с выбором. Визуально увидеть ошибку проще, чем в текстовом виде.

Из чего состоит формула

• символ «=» – с него начинается любая формула;
• функция «СУММ»;
• аргумента функции «A1:C1» (в данном случае это массив ячеек с «A1» по «C1»);
• оператора «+» (сложение);
• ссылки на ячейку «C1»;
• оператора «^» (возведение в степень);
• константы «2».

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

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

Арифметические

Если перед числом поставить «минус», то оно примет отрицательное значение, но по модулю останется точно таким же.

Операторы сравнения

Данные операторы применяются для сравнения значений. В результате операции возвращается ИСТИНА или ЛОЖЬ. К ним относятся:

• знак «меньше» — «=D1
  • знак «меньше или равно» — «3″;B3:C3)
    1. Excel может складывать с учетом сразу нескольких условий. Можно посчитать сумму клеток первого столбца, значение которых больше 2 и меньше 6. И ту же самую формулу можно установить для второй колонки.

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

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

  В качестве примера попробуем построить графики для экспоненты и какого-нибудь уравнения. Инструкция будет следующей:

  1. Создадим таблицу. В первой графе у нас будет исходное число «X», во второй – функция «EXP», в третьей – указанное соотношение. Можно было бы сделать квадратичное выражение, но тогда бы результирующее значение на фоне экспоненты на графике практически пропало бы.

  Как мы и говорили ранее, прирост экспоненты происходит намного быстрее, чем у обычного кубического уравнения.

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

  Отличие в версиях MS Excel

  Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

  Во всём остальном всё выглядит практически точно так же. В качестве примера, посчитаем сумму нескольких ячеек. Для этого необходимо:

  Заключение

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

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

  Кроме этого, важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают про это.

  Файл примеров

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

  Видеоинструкция

  Если наше описание вам не помогло, попробуйте посмотреть приложенное ниже видео, в котором рассказываются основные моменты более детально. Возможно, вы делаете всё правильно, но что-то упускаете из виду. С помощью этого ролика вы должны разобраться со всеми проблемами. Надеемся, что подобные уроки вам помогли. Заглядывайте к нам чаще.

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

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

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

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

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

  Оператор	Операция	Пример
  + (плюс)	Сложение	=В4+7
  — (минус)	Вычитание	=А9-100
  * (звездочка)	Умножение	=А3*2
  / (наклонная черта)	Деление	=А7/А8
  ^ (циркумфлекс)	Степень	=6^2
  = (знак равенства)	Равно
  Меньше
  >	Больше
  Меньше или равно
  >=	Больше или равно
  Не равно

  Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (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%. Все правильно.

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

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

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

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

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

  4-1 Формулы, математические операции - ExcelProfi

  В данном самоучителе мы рассказали обо всем, что связано с формулами в редакторе Excel, – от самого простого до очень сложного. Каждый раздел сопровождался подробными примерами и пояснениями. Это сделано для того, чтобы информация была доступной даже для полных чайников.
  [expert_bq id=»1570″]В данном самоучителе мы рассказали обо всем, что связано с формулами в редакторе Excel, от самого простого до очень сложного. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

  Как формулу в excel сделать значением?

  1. выделяем диапазон с формулами на листе
  2. хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
  3. в появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).

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

  Оператор	Операция	Пример
  + (плюс)	Сложение	=В4+7
  — (минус)	Вычитание	=А9-100
  * (звездочка)	Умножение	=А3*2
  / (наклонная черта)	Деление	=А7/А8
  ^ (циркумфлекс)	Степень	=6^2
  = (знак равенства)	Равно
  Меньше
  >	Больше
  Меньше или равно
  >=	Больше или равно
  Не равно

  Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции