Формула сообщает Excel, что делать с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.
В конструкцию формулы входят: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки, содержащие аргументы, и другие формулы. На примере обсудим практическое применение формул для начинающих пользователей.
Формулы в Excel для чайников
Чтобы задать формулу для ячейки, необходимо ее активировать (поставить курсор) и ввести равно (=). Вы также можете ввести знак равенства в строке формул. После ввода формулы нажмите Enter. Результат расчета появится в ячейке.
Excel использует стандартные математические операторы:
Оператор | Операция | Пример |
+ (подробнее) | Добавление | =В4+7 |
— (меньше) | Вычитание | =А9-100 |
* (звездочка) | Умножение | =А3*2 |
/ (косая черта) | Разделение | =А7/А8 |
^ (циркумфлекс) | Степень | =6^2 |
= (знак равенства) | Так же | |
Меньше | ||
> | Плюс | |
Меньше или равно | ||
>= | Больше или равно | |
Это не то же самое |
При умножении обязательно используется символ «*». Опускать его, как это принято при письменных арифметических вычислениях, недопустимо. То есть запись (2+3) 5 Excel не поймет.
Excel можно использовать как калькулятор. То есть ввести в формулу числа и математические операторы расчета и сразу получить результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, на значение которой будет действовать формула.
При изменении значений в ячейках формула автоматически пересчитывает результат.
Ссылки можно комбинировать в одной формуле с простыми числами.
Оператор умножил значение в ячейке B2 на 0,5. Чтобы ввести ссылку на ячейку в формулу, просто нажмите на эту ячейку.
В нашем примере:
- Поместите курсор в ячейку B3 и введите =.
- Щелкнули по ячейке В2 — Excel ее «обозначил» (в формуле появилось название ячейки, вокруг ячейки образовался «мигающий» прямоугольник).
- Вводим с клавиатуры знак *, значение 0.5 и нажимаем ENTER.
Если в формуле используется несколько операторов, программа будет обрабатывать их в следующей последовательности:
- %, ^;
- *, /;
- +, -.
Вы можете изменить последовательность, используя круглые скобки: Excel сначала вычисляет значение выражения в круглых скобках.
Как в формуле Excel обозначить постоянную ячейку
Существует два типа ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: меняются относительные, абсолютные остаются постоянными.
Программа считает все ссылки на ячейки относительными, если пользователь не укажет другое условие. Относительные ссылки позволяют дублировать одну и ту же формулу в нескольких строках или столбцах.
- Вручную заполните первые столбцы обучающей таблицы. У нас есть такой вариант:
- Помните из математики: чтобы найти стоимость нескольких единиц продукта, вы умножаете цену 1 единицы на количество. Для расчета стоимости введите в ячейку D2 формулу: = цена за единицу * количество. Константы формулы — это ссылки на ячейки с соответствующими значениями.
- Нажимаем ENTER — программа выводит значение умножения. Такие же манипуляции необходимо произвести для всех ячеек. Как установить формулу для столбца в Excel: Скопируйте формулу из первой ячейки в другие строки. Относительные ссылки помогают.
Находим маркер автозаполнения в правом нижнем углу первой ячейки столбца. Нажмите на эту точку левой кнопкой мыши, удерживайте ее и перетащите вниз по столбцу.
Отпустите кнопку мыши — формула будет скопирована в выделенные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Звенья в ячейке связаны с цепью.
Формула абсолютной ссылки относится к той же ячейке. То есть при автодополнении или копировании константа остается неизменной (или константой).
Чтобы указать Excel на абсолютную ссылку, пользователь должен поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.
- Создадим строку «Итого». Найдите общую стоимость всех товаров. Выберите числовые значения из столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
- Воспользуемся функцией автозаполнения. Кнопка находится на вкладке Главная в группе инструментов редактирования».
- После нажатия значка «Сумма» (или комбинации клавиш ALT + «=») выбранные числа складываются вместе, и результат отображается в пустой ячейке.
Сделаем еще одну колонку, где посчитаем долю каждого товара в общей стоимости. Для этого вам нужно:
- Разделите стоимость одного предмета на стоимость всех предметов и умножьте результат на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы она оставалась неизменной при копировании.
- Чтобы получить проценты в Excel, необязательно умножать частное на 100. Выделите ячейку с результатом и нажмите «Форматировать проценты». Или нажмите комбинацию горячих клавиш: CTRL+SHIFT+5
- Копируем формулу на весь столбец: меняется только первое значение формулы (относительная ссылка). Вторая (абсолютная ссылка) остается прежней. Проверим правильность расчетов: найдем результат сто%. Все правильно.
При создании формул используются следующие форматы абсолютной ссылки:
- $B$2 — при копировании столбец и строка остаются постоянными;
- B$2 — при копировании строка не меняется;
- $B2: столбец не изменяется.
Как составить таблицу в Excel с формулами
Для экономии времени при вводе однотипных формул в ячейки таблицы используются маркеры автозаполнения. Если вам нужно исправить ссылку, сделайте ее абсолютной. Для изменения значений при копировании относительной ссылки.
Самые простые формулы для заполнения таблиц в Excel:
- Давайте вставим еще один столбец перед названиями продуктов. Выберите любую ячейку в первом столбце, щелкните правой кнопкой мыши. Нажмите «Вставить». Или сначала нажмите комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А затем комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
- Назовем новый столбец «Нет п/п». Введите «1» в первую ячейку и «2» во вторую. Выберите первые две ячейки: «вставьте» маркер автозаполнения левой кнопкой мыши, перетащите его вниз.
- По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковы: день, месяц, год. Вводим «15 октября» в первую ячейку и «15 ноября» во вторую. Выберите первые две ячейки и «растяните» маркер вниз.
- Найдите среднюю цену товара. Выберите столбец с ценами + еще одна ячейка. Откройте меню кнопки «Сумма»: выберите формулу для автоматического расчета среднего значения.
Чтобы проверить правильность вставленной формулы, дважды щелкните ячейку с результатом.
Формулы в Excel — одно из важнейших преимуществ этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы можете делать все. При этом Excel поможет на каждом шагу — практически в любом окне есть специальные подсказки.
Как вставить формулу
Чтобы создать простую формулу, просто следуйте инструкциям ниже:
- Активируйте любую ячейку. Нажмите на строку ввода формулы. Поставьте знак равенства.
- Введите любое выражение можно использовать как числа
и ссылки на ячейки.
При этом пораженные ячейки всегда подсвечиваются. Это сделано для того, чтобы вы не ошиблись с выбором. Ошибку легче увидеть визуально, чем в текстовом виде.
Из чего состоит формула
В качестве примера возьмем следующее выражение.
Состоит из:
- символ «=» — с него начинается любая формула;
- функция «СУММ»;
- аргумент функции «A1:C1» (в данном случае это массив ячеек от «A1» до «C1»);
- оператор «+» (сложение);
- ссылки на ячейку «С1»;
- оператор «^» (возведение в степень);
- константы «2».
Использование операторов
Операторы в редакторе Excel точно указывают, какие операции следует выполнять над указанными элементами формулы. Расчет всегда выполняется в одном и том же порядке:
- скобка;
- экспоненты;
- умножение и деление (в зависимости от последовательности);
- сложение и вычитание (также в зависимости от последовательности).
Арифметические
это включает:
- сумма — «+» (плюс);
=2+2
- отрицание или вычитание — «-» (минус);
=2-2 =-2
Если вы поставите перед числом знак минус, оно примет отрицательное значение, но абсолютное значение останется точно таким же.
- умножение – «*»;
=2*2
- разделение «/»;
=2/2
- процент «%»;
=20%
- возведение в степень – «^».
=2^2
Операторы сравнения
Эти операторы используются для сравнения значений. Операция возвращает TRUE или FALSE. Это включает:
- знак «равно» – «=»;
=С1=D1
- больше знака» – «>»;
=С1>D1
- знак «меньше чем» — «=D1
- знак «меньше или равно» — «3»; Б3: С3)
- Excel умеет добавлять, учитывая сразу несколько условий. Вы можете вычислить сумму ячеек в первом столбце, значение которых больше 2 и меньше 6. И такую же формулу можно задать для второго столбца.
=СУММЕСЛИ.ЕСЛИ(B3:B9,B3:B9,»>2″,B3:B9,»3″)
- Результат всех формул будет следующим.
Математические функции и графики
С помощью Excel можно вычислять различные функции и строить по ним графики, а затем выполнять графический анализ. Как правило, такие приемы используются в презентациях.
В качестве примера попробуем построить графики для показателя степени и некоторого уравнения. Инструкция будет следующей:
- Создадим таблицу. В первом столбце у нас будет исходное число «Х», во втором — функция «EXP», в третьем — заданный коэффициент. Можно было бы сделать квадратное выражение, но тогда результирующее значение в контексте экспоненты на графике практически исчезло бы.
- Для преобразования значения «Х» необходимо указать следующие формулы.
=EXP(B4) =B4+5*B4^3/2
- Удваиваем эти выражения до конца. В итоге получаем следующий результат.
- Выберите всю таблицу. Перейдите на вкладку «Вставка». Нажмите на инструмент «Рекомендуемые графики».
- Выберите тип линии. Нажмите «ОК», чтобы продолжить».
- В результате получилось очень красиво и аккуратно.
Как мы уже говорили, показатель степени растет намного быстрее, чем у обычного кубического уравнения.
Точно так же любую математическую функцию или выражение можно представить графически.
Отличие в версиях MS Excel
Все описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 годов. Старый редактор Excel значительно уступает по возможностям, количеству функций и инструментов. Если вы откроете официальную справку Microsoft, то увидите, что в них дополнительно указано, в какой версии программы появилась эта функция.
В остальном все выглядит почти точно так же. В качестве примера посчитаем сумму нескольких ячеек. Для этого вам нужно:
- Укажите некоторые данные для расчета. Нажмите на любую ячейку. Нажмите на значок «Фекс».
- Выберите категорию «Математика». Найдите функцию «СУММ» и нажмите «ОК».
- Укажите данные в нужном диапазоне. Для отображения результата нужно нажать «ОК».
- Можно попробовать пересчитать в любом другом редакторе. Процесс будет точно таким же.
Заключение
В этом уроке мы рассказываем обо всем, что связано с формулами в редакторе Excel, от самых простых до самых сложных. Каждый раздел сопровождался подробными примерами и пояснениями. Это сделано для того, чтобы информация была доступна даже полным дуракам.
Если у вас что-то не работает, значит, вы где-то ошибаетесь. У вас могут быть опечатки в выражениях или неправильные ссылки на ячейки. Главное, что нужно понимать, это то, что все нужно проводить очень внимательно и осторожно. Также все функции не на английском, а на русском.
Также важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают об этом.
Файл примеров
Чтобы вам было проще обращаться с описанными выше формулами, мы подготовили специальный демонстрационный файл, в котором были скомпилированы все приведенные выше примеры. Вы можете скачать его с нашего сайта бесплатно. Если во время обучения вы будете использовать заготовленную таблицу с формулами на основе заполненных вами данных, вы получите результаты гораздо быстрее.
Видеоинструкция
Если наше описание вам не помогло, попробуйте посмотреть прикрепленное ниже видео, в котором более подробно объясняются основные моменты. Возможно, вы все делаете правильно, но чего-то не хватает. С помощью этого видео вы должны разобраться со всеми проблемами. Мы надеемся, что эти уроки помогли вам. Заходите к нам чаще.
Рассмотрим различные способы перетаскивания (копирования) формул в строки и столбцы Excel.
Первый способ: протянуть ячейку в столбце или строке.
Чтобы растянуть (распространить) формулу из одной ячейки в несколько ячеек в столбце или строке, вам нужно сделать ряд вещей:
1. Введите функцию (формулу) в ячейку и нажмите клавишу ВВОД.
2. Поместите курсор в правый нижний угол ячейки так, чтобы он принял вид тонкого черного креста.
3. Удерживайте левую кнопку мыши.
4. Не отпуская кнопку мыши, перетащите крестик в нужном направлении. В направлении, в котором должно распространяться значение ячейки.Второй способ: быстрое протягивание формулы в один клик мыши.
Чтобы быстро заполнить столбец формулой или значением ячейки, просто выполните следующие действия:
1. Введите функцию (формулу) в ячейку и нажмите «ENTER».
2. Поместите курсор в правый нижний угол ячейки так, чтобы он принял вид тонкого черного креста.
3. Дважды щелкните в правом нижнем углу ячейки.Формула автоматически распространится на первую пустую или заполненную ячейку в тех случаях, когда в столбце после ячейки с функцией умножения есть пустые ячейки.
Третий способ: протянуть формулу без изменения (смещения) исходных ячеек.
Чтобы извлечь функцию, не изменяя ее, необходимо выполнить те же операции, что и в первом и втором случаях. Только перед броском надо зафиксировать адреса неизменяемых ячеек.
Адреса ячеек можно исправить, добавив знак доллара перед значением адреса столбца или перед значением адреса строки.
Теперь, когда адрес закреплен, он не изменится при перетаскивании.Четвертый способ: протянуть формулу при помощи копирования.
Подходит для фильтров.
Это делается следующим образом:
1. Введите функцию (формулу) в ячейку и нажмите клавишу ВВОД.
2. Скопируйте значение ячейки с помощью выпадающего контекстного меню правой кнопкой мыши или комбинацией клавиш «ctrl» + «c».
3. Выберите нужный диапазон.
Чтобы быстро выбрать вниз, просто нажмите комбинацию клавиш:«Ctrl» + «shift» + стрелка вниз Чтобы быстро выделить рядом с ним, достаточно нажать сочетание клавиш:
«Ctrl» + «shift» + стрелка в сторону
- Выделив диапазон, нужно вставить скопированную формулу с помощью выпадающего контекстного меню правой кнопкой мыши или комбинацией клавиш «ctrl» + «v».
- знак «меньше или равно» — «3»; Б3: С3)