Создание простой формулы в Excel
Применяется кExcel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016
Можно создать простую формулу для добавления, вычитания, умножения или деления значений на листе. Простые формулы всегда начинаются со знака равенства (=), за которым следуют константы, которые являются числовыми значениями и операторами вычисления, такими как плюс (+), минус (—), звездочка (*) или косая черта (/).
Ниже приведен пример простой формулы.
-
На листе выделите ячейку, в которую нужно ввести формулу.
-
Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении.
В нашем примере введите =1+1.
Примечания:
-
Вместо ввода констант в формуле можно выбрать ячейки с нужными значениями и ввести операторы между ними.
-
В соответствии со стандартным порядком математических операций, умножение и деление выполняются до сложения и вычитания.
-
-
Нажмите клавишу ВВОД (Windows) или Return (Mac).
Рассмотрим другой вариант простой формулы. Введите =5+2*3 в другой ячейке и нажмите клавишу ВВОД или Return. Excel перемножит два последних числа и добавит первое число к результату умножения.
Использование автосуммирования
Для быстрого суммирования чисел в столбце или строке можно использовать кнопку «Автосумма». Выберите ячейку рядом с числами, которые вы хотите суммировать, выберите Автосумма на вкладке Главная , нажмите клавишу ВВОД (Windows) или Return (Mac) и все!
При выборе параметра Автосумма Excel автоматически вводит формулу (которая использует функцию СУММ) для суммирования чисел.
Примечание: Также в ячейке можно ввести ALT+= (Windows) или ALT++= (Mac), и Excel автоматически вставит функцию СУММ.
Приведем пример. Чтобы добавить числа января в этот бюджет развлечения, выберите ячейку B7, ячейку непосредственно под столбцом чисел. Затем выберите Автосумма. В ячейке B7 появится формула, а Excel выделяет ячейки, которые вы суммируете.
Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.
Примечания:
-
Чтобы получить сумму столбца чисел, выделите ячейку сразу под последним числом в столбце. Чтобы получить сумму строки чисел, выделите ячейку сразу справа.
-
Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, если скопировать формулу из ячейки B7 в ячейку C7, формула в C7 автоматически адаптируется к новому расположению и вычисляет числа в C3:C6.
-
Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, выбрать AutoSum и суммировать оба столбца одновременно.
Скопируйте данные примера из таблицы ниже и вставьте их в ячейку A1 нового листа Excel. При необходимости измените ширину столбцов, чтобы видеть все данные.
Примечание: Чтобы эти формулы выводили результат, выделите их и нажмите клавишу F2, а затем — ВВОД (Windows) или Return (Mac).
|
Данные |
||
|---|---|---|
|
2 |
||
|
5 |
||
|
Формула |
Описание |
Результат |
|
=A2+A3 |
Сумма значений в ячейках A1 и A2 |
=A2+A3 |
|
=A2-A3 |
Разность значений в ячейках A1 и A2 |
=A2-A3 |
|
=A2/A3 |
Частное от деления значений в ячейках A1 и A2 |
=A2/A3 |
|
=A2*A3 |
Произведение значений в ячейках A1 и A2 |
=A2*A3 |
|
=A2^A3 |
Значение в ячейке A1 в степени, указанной в ячейке A2 |
=A2^A3 |
|
Формула |
Описание |
Результат |
|
=5+2 |
Сумма чисел 5 и 2 |
=5+2 |
|
=5-2 |
Разность чисел 5 и 2 |
=5-2 |
|
=5/2 |
Частное от деления 5 на 2 |
=5/2 |
|
=5*2 |
Произведение чисел 5 и 2 |
=5*2 |
|
=5^2 |
Число 5 во второй степени |
=5^2 |
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
См. также
Полные сведения о формулах в Excel
Функция СУММ
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
#статьи
-
0
Рассказали и показали на примерах, как создавать формулы вручную, как использовать встроенные формулы, как «растянуть» формулу на другие ячейки.
Изображение: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Формулы в Excel — это команды. Их вводят в ячейки, чтобы провести расчёты или выполнить операции со значениями таблицы. Например, суммировать значения нескольких ячеек, рассчитать проценты или выполнить более сложные действия.
Если вы только начинаете разбираться в работе с электронными таблицами, прочитайте эту инструкцию. В ней мы рассказали:
- как создать формулу в Excel вручную;
- как использовать встроенные формулы Excel;
- как скопировать одну формулу в несколько ячеек, закрепив часть значений;
- как пользоваться формулами в «Google Таблицах»;
Чтобы создать формулу, нужно выбрать пустую ячейку, ввести знак равенства и дальше написать формулу в обычном математическом виде. Например, чтобы сложить числа 5 и 2, нужно ввести формулу: =5+2. Затем нажать Enter.
Скриншот: Excel / Skillbox Media
Скриншот: Excel / Skillbox Media
Кроме чисел, формулу можно создавать из ссылок на ячейки таблицы. Вместо того чтобы вводить значения с клавиатуры, кликните на нужные ячейки со значениями. Они будут отображаться в формуле в виде буквы и номера.
Например, чтобы суммировать значения ячеек A1 и A2, выберите пустую ячейку и введите знак равенства. Затем кликните на ячейку A1, введите оператор + и кликните на ячейку A2. Получится такая формула: =A1+A2. Затем также нажмите Enter.
Скриншот: Excel / Skillbox Media
Для создания формул в Excel используют стандартные математические операторы. В таблице перечислили основные операторы и их значения.
| Оператор | Какую операцию выполняет |
|---|---|
| + | Сложить |
| — | Вычесть или показать отрицательное значение |
| / | Поделить |
| * | Умножить |
| ^ | Возвести в степень |
| & | Объединить значения ячеек в одну последовательность |
| : | Создать диапазон ячеек. Создаёт одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона включительно |
При вводе формулы в ячейке она одновременно появляется в строке формул — в белой полосе под панелью инструментов.
Вводить формулу можно сразу в эту строку. Для этого выберите пустую ячейку — место, где появится результат формулы. Затем поставьте курсор мыши в строку формул, введите знак равенства и напишите формулу — математические операторы, числа, ссылки на ячейки.
Дальше также нажмите Enter или кнопку с галочкой, расположенную левее строки формул, — Excel проведёт расчёт и выдаст результат.
Скриншот: Excel / Skillbox Media
Скриншот: Excel / Skillbox Media
Обучение в Skillbox для тех, кто хочет профессионально работать в Excel
Вместо того чтобы писать формулы самостоятельно, можно использовать формулы, встроенные в Excel. Это готовые команды, с помощью которых проводят расчёты и выполняют разные операции с данными в таблице.
Например, функцию СУММ применяют для сложения чисел в ячейках, а СРЗНАЧ — для того, чтобы найти среднее значение.
Встроенные формулы используют не только для математических вычислений, но и для других задач. Например, с помощью них можно объединять информацию из нескольких ячеек в одной, удалять дубликаты, проверять данные таблицы на соответствие заданным условиям и так далее.
Разберёмся, где найти и как использовать встроенные формулы в Excel.
Выберите пустую ячейку и нажмите на кнопку Fx левее строки формул. В строке формул появится знак равенства, а в правой части экрана — окно «Построитель формул» с перечнем доступных функций.
Можно нажать на любую функцию, и в нижней части экрана появится краткое описание того, какие действия она выполняет. Подробное описание всех встроенных функций Excel можно прочитать на сайте поддержки Microsoft.
Скриншот: Excel / Skillbox Media
Двойным кликом выбираем нужную функцию — для примера выберем СУММ. Появится окно для ввода аргументов функции — значений, которые она будет использовать для выполнения расчётов.
В функции СУММ всё просто: аргументы — это значения, которые нужно суммировать. Но в некоторых функциях аргументы не такие очевидные. Чтобы разобраться в них, также обращайтесь к описанию функции под построителем формул или к справочным материалам Microsoft.
Скриншот: Excel / Skillbox Media
Как и при ручном создании формулы, о котором мы говорили выше, можно вводить аргументы функции с клавиатуры или выбирать значения из ячеек таблицы.
Чтобы выбрать значения из таблицы, поставьте курсор в строку аргумента, а затем кликните по ячейке с нужным значением. В строке аргумента появится ссылка на эту ячейку.
Скриншот: Excel / Skillbox Media
В нашем примере функция СУММ по умолчанию предлагает два аргумента. Если потребуется больше, нажмите на кнопку +, расположенную под аргументами.
Чтобы функция выполнила расчёт, нужно нажать кнопку «Готово». Результат расчёта появится в ячейке, которую мы выбрали перед запуском построителя формул.
Одновременно с созданием формулы в построителе формул она появляется в строке формул. В нашем примере итоговая формула приняла вид: Fx=СУММ(B1; B2).
Скриншот: Excel / Skillbox Media
Также встроенные формулы можно найти на вкладке «Формулы» в главном меню Excel. Там они для удобства разбиты по категориям: финансовые формулы, логические, текстовые, математические и так далее.
Скриншот: Excel / Skillbox Media
Для запуска нужной функции выберите её в выпадающем меню — в правой части экрана появится построитель формул. После этого порядок действий тот же, что мы описали выше.
Скриншот: Excel / Skillbox Media
Скриншот: Excel / Skillbox Media
Чаще всего в Excel применяют формулы одновременно к нескольким ячейкам. Например, сразу ко всем ячейкам столбца. Разберём, как это сделать, на примере.
Допустим, в таблице перечислено несколько наименований товаров, количество каждого товара на складе и цена за единицу товара.
Скриншот: Excel / Skillbox Media
Нам нужно рассчитать общую стоимость каждого товара — то есть умножить количество товара на складе на цену этого товара. Выберем первую пустую ячейку в столбце «Стоимость, руб.», введём знак равенства и напишем формулу: =B2*C2.
Скриншот: Excel / Skillbox Media
Дальше жмём Enter и получаем результат — стоимость первого товара в таблице. Чтобы не вводить эту же формулу для других товаров, её можно скопировать.
Для этого наведём курсор на нижний правый угол ячейки с результатом — курсор превратится в плюсик. Зажмём его и протянем вниз до конца таблицы.
Скриншот: Excel / Skillbox Media
Готово — формула скопировалась в остальные ячейки.
Скриншот: Excel / Skillbox Media
В этом примере ссылки на аргументы формулы были относительными — при копировании формулы они менялись для каждой строки таблицы. Например, формула для расчёта стоимости товара из пятой строки была такой: =B5*C5.
Относительные ссылки Excel устанавливает по умолчанию.
При необходимости ссылки на ячейки можно сделать абсолютными. Аргументы, на которых стоит абсолютная ссылка, при копировании формулы не меняются. Давайте разберёмся, как это работает, на примере.
Допустим, нам нужно рассчитать долю стоимости каждого товара в общей стоимости товаров.
Скриншот: Excel / Skillbox Media
Для расчёта доли нужно стоимость одного товара разделить на общую стоимость товаров.
Скриншот: Excel / Skillbox Media
В этом случае первый аргумент будет меняться для каждого товара, а второй аргумент — общая стоимость — должен быть всегда одним и тем же. Поэтому ссылку на этот аргумент нужно сделать абсолютной.
Для этого выделим в формуле ссылку на общую стоимость товаров — D8 — и нажмём:
- на Windows — клавишу F4;
- на macOS — сочетание клавиш Command + T.
После этого в абсолютной ссылке появятся дополнительные значки $. В нашем примере формула примет такой вид: =D2/$D$8.
Скриншот: Excel / Skillbox Media
Теперь при копировании формулы в остальные ячейки столбца расчёт будет проходить корректно: значения стоимости отдельных товаров будут меняться для каждой строки, а общая стоимость товаров будет единой.
Принципы работы с формулами в «Google Таблицах» такие же, как и в Excel. Главное отличие в том, что в «Google Таблицах» нет построителя формул — аргументы встроенных функций нужно вводить сразу в строку формул.
Скриншот: «Google Таблицы» / Skillbox Media
Скриншот: «Google Таблицы» / Skillbox Media
Чтобы посмотреть все встроенные функции «Google Таблиц», нажмите «Вставка» → «Функция». Полное описание функций — какие операции они проводят, а также какой у них синтаксис — можно прочитать в справке.
Скриншот: «Google Таблицы» / Skillbox Media
Чтобы закрепить ячейки в формуле в «Google Таблицах» — то есть поставить абсолютные ссылки — при вводе формулы нужно нажать:
- на Windows — клавишу F4;
- на macOS — сочетание клавиш Fn + F4.
Бесплатный курс: «Excel и „Google Таблицы“ для всех»
Начать учиться
Введение в формулы Excel
С формулами в Excel можно автоматически выполнять расчёты, работать с текстом, анализировать данные и автоматизировать задачи. Принцип такой: вы вводите формулу в ячейку, нажимаете Enter — и программа мгновенно выполняет вычисления вместо вас и показывает результат.
Формулы работают с разными типами данных:
-
числами — для арифметических операций;
-
текстом — для объединения и обработки строк;
-
датами и временем — для расчётов и преобразований;
-
логическими значениями (ИСТИНА и ЛОЖЬ) — для условий и проверок.
Применение формул упрощает работу с таблицами, сокращает количество ошибок и позволяет автоматизировать расчёты.
Станьте мастером Excel на курсах Eduson
Думаете, почему слетают формулы в Excel? Тогда пора закрыть пробелы в знаниях! Курс «MS Office: Excel, Word и PowerPoint» от Академии Eduson поможет вам работать с офисными программами без стресса.
Вы научитесь использовать Excel для расчётов, анализа данных и автоматизации рутины, грамотно оформлять документы в Word и создавать презентации в PowerPoint. Это ваш шанс стать мастером офисных инструментов, начать работать быстрее и эффективнее.
Структура формулы в Excel
Формула состоит из нескольких элементов:
-
Знак равно (=) — всегда стоит в начале. Без него Excel не распознает ввод как формулу.
-
Операнды — это данные, с которыми работает формула. Они бывают трёх типов: числовые значения (используются напрямую или берутся из ячеек), текстовые строки (заключаются в кавычки), логические значения (применяются в условиях и проверках).
-
Операторы — задают действия внутри формулы. Основные группы: арифметические (выполняют математические расчёты), текстовый & (объединяет строки), сравнительные (используются для сравнения значений).
-
Ссылки на ячейки — нужны для работы с данными из таблицы. Они бывают относительными, абсолютными и смешанными. Подробнее о них расскажем дальше.
Создание и вставка формул
Формулы в Excel можно вводить вручную или вставлять с помощью встроенных инструментов. Выбирайте способ в зависимости от задачи: ручной вариант даёт больше гибкости, а встроенные функции помогают ускорить работу.
Ручная вставка
Самый простой и универсальный способ — ввести формулу прямо в ячейку:
-
выберите ячейку, где должен появиться результат;
-
введите знак «=», затем формулу;
-
нажмите Enter — Excel выполнит расчёт.
При вводе можно использовать ссылки на другие ячейки, диапазоны и функции. Например, =A1+B1 сложит значения из двух ячеек, а =СУММ(A1:A10) рассчитает сумму чисел в указанном диапазоне.
Использование вкладки с формулами
Если формула сложная или незнакомая, удобнее воспользоваться вкладкой «Формулы» на верхней панели. Здесь собраны встроенные функции, сгруппированные по категориям. Чтобы вставить формулу в Excel через эту вкладку:
-
перейдите во вкладку «Формулы»;
-
выберите нужную категорию, например, «Математические» или «Логические»;
-
нажмите на функцию, заполните аргументы в открывшемся окне;
-
подтвердите ввод — Excel автоматически вставит формулу в ячейку.
Этот способ полезен, когда неизвестны аргументы функции, поскольку Excel подсказывает их значения.
Автосуммирование
Для быстрого подсчёта суммы можно использовать кнопку «Автосумма» (∑) во вкладке «Формулы» или на панели «Главная». Это автоматически создаёт формулу =СУММ.
Для этого:
-
выберите ячейку, где должен появиться результат;
-
нажмите «Автосумма» — Excel предложит диапазон для сложения;
-
если диапазон указан верно, нажмите Enter, если нет — выделите нужные ячейки и подтвердите ввод.
Этот инструмент ускоряет работу с числами, особенно при обработке длинных столбцов или строк.
Пройдите обучение в Академии Eduson
Вы всё ещё вручную суммируете числа в столбце или боитесь сломать формулу? На курсе «Excel и Google-таблицы: от новичка до эксперта» от Академии Eduson вы всего за 2 недели научитесь быстро обрабатывать данные, работать с функциями и сводными таблицами, автоматизировать расчёты и визуализировать отчёты так, чтобы всё было наглядно и понятно.
Типы формул в Excel
Формулы в Excel могут быть разные — от простых вычислений до сложных конструкций с вложенными функциями. Разберём основные типы.
Простые формулы
Это базовые выражения, выполняющие простые расчёты. Они состоят из чисел, операторов и ссылок на ячейки. Например:
-
=A1+A2 — сложение двух значений;
-
=B3*10 — умножение на число;
-
=C5/2 — деление.
Простые формулы удобны для базовых расчётов, но, если нужно автоматизировать процесс, лучше использовать функции.
Сложные формулы
Здесь уже задействовано несколько операций, и важно учитывать приоритет действий. Например:
-
=A1+B1*C1 — Excel сначала умножит B1 на C1, а затем прибавит A1.
-
=(A1+B1)*C1 — скобки меняют порядок: сначала сложение, потом умножение. Использование скобок помогает избежать ошибок и сделать расчёты предсказуемыми.
Комбинированные формулы
Это выражения, в которых используются несколько функций или логические проверки. Например, =ЕСЛИ(A1>B1;»Много»;»Мало») — проверяет значение и возвращает нужный текст. Такие формулы позволяют автоматизировать сложные расчёты и анализ данных.
Встроенные функции
Excel содержит сотни функций для работы с числами, текстом, датами и логикой. Они записываются в виде =ФУНКЦИЯ(аргументы). Например:
-
=СУММ(A1:A10) — сумма;
-
=СРЗНАЧ(B1:B10) — среднее;
-
=СЕГОДНЯ() — текущая дата и время.
Функции позволяют ускорить работу и минимизировать ошибки при расчётах.
Основные функции Excel
Функции в Excel помогают выполнять расчёты автоматически, без необходимости вручную складывать числа или проверять условия. Разберём самые полезные из них.
СУММ (SUM) — сложение чисел. Складывает все числа в указанном диапазоне. Вместо того чтобы писать =A1+A2+A3+A4, проще использовать =СУММ(A1:A4).
Как это работает:
-
=СУММ(A1:A10) — складывает все числа в диапазоне A1:A10;
-
=СУММ(A1, A3, A5) — суммирует только выбранные ячейки.
Пригодится для подсчёта итоговой суммы расходов, доходов, результатов тестов или для быстрого сложения больших массивов данных.
СРЗНАЧ (AVERAGE) — вычисление среднего значения группы чисел.
Как это работает:
-
=СРЗНАЧ(B1:B10) — найдёт среднее арифметическое всех чисел в диапазоне;
-
=СРЗНАЧ(A1, A3, A5) — посчитает среднее только для указанных ячеек.
Пригодится, например, чтобы узнать средний балл ученика или средний чек.
ЕСЛИ (IF) — проверка условия. Позволяет задать условие и выполнить разные действия в зависимости от результата.
Как это работает: =ЕСЛИ(A1>100;»Превышение»;»Норма») — если в A1 число больше 100, покажет «Превышение», иначе — «Норма».
Пригодится для автоматической классификации данных — например, прошёл ли студент экзамен или нет. Или для выявления ошибок в таблице.
СУММЕСЛИ (SUMIF) — суммирование по условию. Складывает только те числа, которые соответствуют определённому критерию.
Как это работает: =СУММЕСЛИ(A1:A10;»>200″) — сложит только числа больше 200.
Пригодится для подсчёта продаж по высокочеку и анализа расходов по категориям.
МАКС (MAX) и МИН (MIN) — функции находят максимальное и минимальное значение в диапазоне.
Как это работает:
-
=МАКС(A1:A10) — покажет наибольшее число;
-
=МИН(B1:B20) — найдёт наименьшее значение.
Пригодится, например, для определения самой высокой и самой низкой цены или анализа лучших и худших показателей.
Все эти функции значительно упрощают работу в Excel, поэтому, если вам предстоит работать с программой постоянно, рекомендуем запоминать. И изучить эту статью для полного погружения и эту — чтобы не упустить ни один лайфхак.
Ускорьте работу с финансами
Неважно, ведёте ли вы личный бюджет или управляете финансами бизнеса — хаос в таблицах приводит к потере денег. На курсе «Excel и Google-таблицы для управления финансами» вы по интерактивным упражнениям научитесь грамотно вести учёт доходов и расходов, прогнозировать траты и анализировать данные, чтобы на их основе принимать только правильные финансовые решения.
Работа с ячейками и диапазонами
Формулы в Excel работают с данными, которые хранятся в ячейках. Чтобы использовать их в расчётах, важно понимать, как правильно ссылаться на ячейки и когда применять константы.
Ссылки на ячейки
В Excel можно подставлять в формулы не конкретные числа, а ссылки на ячейки. Это позволяет автоматически обновлять расчёты при изменении данных.
Относительные ссылки (A1) используются, когда формулу нужно применять к разным строкам и столбцам. А ещё они позволяют копировать формулы без необходимости менять адреса ячеек вручную. При копировании они меняются относительно новой позиции: =A1+B1 в строке 2 превратится в =A2+B2.
Абсолютные ссылки ($A$1) фиксируют конкретную ячейку, чтобы при копировании формулы ссылка оставалась неизменной: =$A$1+B1 всегда будет использовать значение из A1.
Смешанные ссылки (A$1 или $A1) фиксируют либо строку, либо столбец и удобны в сложных таблицах, где часть данных должна изменяться, а часть — нет. Пример: =$A1+B1 при копировании вниз изменит только номер строки ($A2+B2).
Использование констант
Иногда в формулах нужны фиксированные значения, которые не должны изменяться. Их можно задавать двумя способами:
-
Прямо в формуле: =A1*1.2, где 1.2 — константа (например, налог или процент). Однако если ставка увеличится или уменьшится, придётся всё исправлять вручную.
-
В отдельной ячейке: =A1*$B$1, где B1 содержит коэффициент. Если значение изменится, пересчитывать ничего не нужно.
Общее правило таково: если оно постоянно, пишем прямо в формуле; если может поменяться, выносим в отдельную ячейку и используем абсолютную ссылку.
Правильная работа со ссылками и константами делает формулы гибкими и удобными в масштабировании.
Ошибки при работе с формулами
При работе в Excel ошибки случаются часто. Разберём самые распространённые из них и способы их исправления.
#ДЕЛ/0! — деление на ноль
Возникает, когда формула пытается разделить число на пустую ячейку или на 0.
Как исправить: проверить, что в знаменателе есть число и/или использовать ЕСЛИ: =ЕСЛИ(B1=0; «Ошибка»; A1/B1).
#ИМЯ? — ошибка в названии функции или ссылки
Появляется, когда есть ошибка в написании имени функции (СУМ вместо СУММ), ссылка на несуществующую ячейку или формула введена без «=» в начале.
Как исправить: проверить название функции и ссылку. Убедиться, что перед формулой стоит «=».
#ЗНАЧ! — несовместимые типы данных
Причина ошибки — использование несовместимых данных (например, текст вместо числа) в формуле.
Как исправить: проверить, все ли аргументы подходят для нужных вычислений или аналитики.
#ССЫЛКА! — ошибка в адресе ячейки
Возникает, когда формула ссылается на удалённую ячейку.
Как исправить: отследить, какие ячейки использует формула. Если данные были случайно удалены, восстановить их.
#ЧИСЛО! — неверное числовое значение
Такое бывает, когда формула использует слишком большое или маленькое число. А также когда есть проблемы с форматированием (например, дата в виде текста).
Как исправить: посмотреть, корректен ли числовой формат. Убедиться, что числа в допустимых пределах.
###### — столбец слишком узкий
Возникает, если число или дата не помещаются в ячейку.
Как исправить: расширить столбец (двойной клик по границе заголовка).
Большинство ошибок в Excel легко исправить, если понимать их причины. Внимательность к формулам и правильное использование ссылок помогут избежать проблем в расчётах.
Если вы до сих пор думаете, что Excel — это просто таблицы, посмотрите на вакансии: в 111 000+ из них требуется профессиональное владение этой программой. Хотите быстрее справляться с задачами, автоматизировать рутину и зарабатывать больше? Тогда вам нужно владеть Excel на уровне эксперта.
В Академии Eduson есть курсы по Excel для разных профессий и задач: управление финансами — для тех, кто хочет грамотно распоряжаться деньгами, Excel для бухгалтеров — если отчёты и расчёты занимают слишком много времени, продвинутый Excel — чтобы работать с массивами данных и аналитикой. Выберите курс под свои цели и освоите программу так, чтобы она работала на вас.
Подписывайтесь на телеграм-канал Академии Eduson, чтобы видеть больше полезных постов о востребованных профессиях и карьерном росте, узнавать про скидки и акции на обучение.
Чаще всего под формулами в Excel подразумевают именно встроенные функции, предназначенные для выполнения расчетов, и куда реже математические формулы, имеющие уже устоявшийся вид.
В этой статье я рассмотрю обе темы, чтобы каждый пользователь нашел ответ на интересующий его вопрос.
Окно вставки функции
Некоторые юзеры боятся работать в Экселе только потому, что не понимают, как именно устроены функции и каким образом их нужно составлять, ведь для каждой есть свои аргументы и особые нюансы написания. Упрощает задачу наличие окна вставки функции, в котором все выполнено в понятном виде.
-
Для его вызова нажмите по кнопке с изображением функции на панели ввода данных в ячейку.
-
В нем используйте поиск функции, отобразите только конкретные категории или выберите подходящую из списка. При выделении функции левой кнопкой мыши на экране отображается текст о ее предназначении, что позволит не запутаться.
-
После выбора наступает время заняться аргументами. Для каждой функции они свои, поскольку выполняются совершенно разные задачи. На следующем скриншоте вы видите аргументы суммы, которыми являются два числа для суммирования.
-
После вставки функции в ячейку она отобразится в стандартном виде и все еще будет доступна для редактирования.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Используем вкладку с формулами
В Excel есть отдельная вкладка, где расположена вся библиотека формул. Вы можете использовать ее для быстрого поиска и вставки необходимой функции, а для редактирования откроется то же самое окно, о котором шла речь выше. Просто перейдите на вкладку с соответствующим названием и откройте одну из категорий для выбора функции.
Как видно, их названия тематические, что позволит не запутаться и сразу отобразить тот тип формул, который необходим. Из списка выберите подходящую и дважды кликните по ней левой кнопкой мыши, чтобы добавить в таблицу.
Приступите к стандартному редактированию через окно аргументов функции. Кстати, здесь тоже есть описания, способные помочь быстрее разобраться с принципом работы конкретного инструмента. К тому же ниже указываются доступные значения, которые можно использовать для работы с выбранной формулой.
Ручная вставка формулы в Excel
Опытные пользователи, работающие в Excel каждый день, предпочитают вручную набирать формулы, поскольку так это делать быстрее всего. Они запоминают синтаксис и каждый аргумент, что не только ускоряет процесс, но и делает его более гибким, ведь при использовании одного окна с аргументами довольно сложно расписать большую цепочку сравнений, суммирований и других математических операций.
Для начала записи выделите ячейку и обязательно поставьте знак =, после чего начните вписывать название формулы и выберите ее из списка.
Далее начните записывать аргументы, в чем помогут всплывающие подсказки. По большей части они нужны для того, чтобы не запутаться в последовательности и разделителях.
По завершении нажмите клавишу Enter, завершив тем самым создание формулы. Если все записано правильно, и программе удается рассчитать результат, он отобразится в выбранной ячейке. При возникновении ошибки вы сможете ознакомиться с ее текстом, чтобы найти решение.
Вставка математических формул
В завершение поговорим о математических формулах в Excel, так как тематика статьи подразумевает и вставку таких объектов в таблицу тоже. Доступные уравнения относятся к символам, поэтому для их поиска понадобится перейти на вкладку со вставкой и выбрать там соответствующий раздел.
Из появившегося списка найдите подходящее для вас уравнение или приступите к его ручному написанию, выбрав последний вариант.
На экране появится редактор и блок формулы. Его используйте для перемещения, а сам редактор – для того, чтобы заносить в формулу числа и редактировать ее под себя. Учитывайте, что в этом случае не работают никакие проверки, поэтому правильность написания проверять придется собственноручно.
Это были самые простые способы вставить функции и формулы в Excel. Первые три помогут создать операции, а последний пригодится математикам и тем, кто выполняет сложные расчеты при помощи таблицы и нуждается во вставке математических формул.
Рутинные задачи при работе с таблицами Excel можно автоматизировать с помощью специальных формул. Они позволят выполнить сложные вычисления, значительно ускорят работу с большими объемами данных и помогут избежать ошибок.
Как использовать формулы в Excel
В Excel формулы вводят в строке формул, которая находится в верхней части окна Excel, под лентой инструментов.
Чтобы начать ввод:
- Выберите ячейку таблицы, в которую хотите вставить формулу.
- Введите формулу в строке формул. Формула всегда начинается со знака «=».
- Нажмите Enter для завершения ввода и отображения результата расчета в выбранной ячейке.
Какие операторы используют в формулах
В формулах Excel используют операторы для выполнения математических, логических и текстовых операций. Вот основные виды операторов:
Арифметические операторы
Операторы сравнения
В зависимости от результата формулы значением целевой ячейки будет ИСТИНА или ЛОЖЬ.
Текстовый оператор
Оператор конкатенации & соединяет («склеивает») текстовые строки.
Пример:
Ячейка A1 содержит значение «Skill», ячейка B1 содержит значение «Factory».
Результат формулы «=A1 & B1» будет соединенной строкой «SkillFactory».
Обратите внимание, что при соединении значений в конечной строке пробел отсутствует. Чтобы добавить пробел, нужно включить его в формулу: «=A1 & » » & B1».
Логические операторы
Ссылочные операторы
Оператор ссылки
Оператор абсолютной ссылки $ фиксирует ссылку на конкретную ячейку или диапазон.
Пример:
Если в формуле сослаться на ячейку A1 таким образом — $A$1, то при копировании формулы (например, при «растягивании» ячейки на нужный диапазон) ссылка на ячейку A1 не изменится, так как она зафиксирована.
Какие основные формулы есть в Excel и как их использовать
Расчет суммы
Формула для расчета суммы используется для сложения числовых значений в заданном диапазоне ячеек. Она помогает рассчитать общую сумму значений в столбце, строке или любом другом диапазоне ячеек. Для сложения можно использовать оператор + или формулу СУММ.
Пример
Допустим, у вас есть таблица с продажами товаров за несколько дней. Вам нужно рассчитать общую сумму продаж каждого товара.
В ячейке B7 рассчитываем сумму продаж товара A по формуле «=СУММ(B2:B6)». Это эквивалентно формуле «=B2 + B3 + B4 + B5 + B6», но гораздо короче.
В ячейке C7 проделаем то же самое для товара B с помощью формулы «=СУММ(C2:C6)». Можно просто растянуть ячейку B7 до C7.
Умножение
Используется для перемножения числовых значений из двух или более ячеек. Можно выполнить при помощи оператора * или формулы ПРОИЗВЕД.
Пример
У вас есть таблица, где указано количество проданных единиц товара и цена за единицу. Вы хотите рассчитать общую выручку для каждого дня.
В ячейке D2 указываем формулу расчета общей выручки, умножив цену одной единицы товара на количество проданного товара. Полученную формулу «=C2*B2» применяем ко всему столбцу выручки.
Произведение ячеек C2 и B2, которое мы оформили как «=C2*B2», можно также записать как «=ПРОИЗВЕД(C2:B2)», указав диапазон ячеек для перемножения. Этот способ будет актуален для большого количества множителей.
Например, используя формулу «=ПРОИЗВЕД(A14:D14)», мы получили произведение всего диапазона ячеек.
Объединение (конкатенация)
Кроме оператора & существует формула СЦЕПИТЬ, которая также нужна для объединения (сцепления) текста из нескольких ячеек в одну. Эти два способа выполняют одинаковые функции, но формула СЦЕПИТЬ удобнее, когда нужно объединить текст из большого количества ячеек.
Пример
Например, есть таблица со столбцами имен и фамилий, которые вы хотите объединить в одну строку.
Воспользуемся формулой СЦЕПИТЬ. Вставим ее в ячейку C1: «=СЦЕПИТЬ(A2; » «; B2)», а затем применим ко всему столбцу «Имя и фамилия». Указав в формуле пробел, мы избежим слитного написания имени и фамилии.
Округление
Формула ОКРУГЛ в Excel округляет числовые значения до заданного количества десятичных знаков. Это полезно для сокращения длинных дробей или для того, чтобы добиться точности до определенного знака после запятой.
Пример
Есть таблица с результатами расчетов, которые вам нужно округлить до сотых долей.
В ячейку B2 вводим формулу «=ОКРУГЛ(A2; 2)». Она округлит значение в ячейке A2 до двух знаков после запятой. Растянем ее на весь столбец «Округленное значение» и получим округленные дроби для остальных исходных значений.
Также можно контролировать, в какую сторону будет округлена дробь: вверх — ОКРУГЛВВЕРХ или вниз — ОКРУГЛВНИЗ.
Нахождение корня
Квадратный корень
Для нахождения квадратного корня в Excel используется формула КОРЕНЬ.
Пример
У вас есть таблица с числами, и вам нужно найти квадратный корень для каждого из этих чисел.
В ячейке B2 пишем формулу квадратного корня — «=КОРЕНЬ(A2)» и растягиваем ее на весь столбец.
Корень любой степени
Для извлечения корня любой степени (в том числе и квадратного) нужно воспользоваться оператором возведения в степень ^. В качестве степени нужно взять обратную степень корня. Например, для кубического корня — 1/3, для корня четвертой степени — 1/4 и так далее.
Пример
Нужно рассчитать кубический корень каждого числа в столбце «Число».
Запишем формулу «=A2^(1/3)» в ячейке B2 и растянем значение по всему столбцу. Получим корень третьей степени для столбца «Число».
Поиск значения по диапазону или таблице (ВПР)
ВПР (или VLOOKUP) помогает найти значение в первом столбце определенного диапазона и вернуть соответствующее значение из другого столбца в той же строке. Эта функция очень полезна в работе с большими таблицами.
В этой статье подробно рассказали о возможностях функции ВПР и приложили пошаговую инструкцию.
Изменение регистра
В Excel есть две функции для изменения регистра текста: ПРОПИСН и СТРОЧН.
ПРОПИСН
Преобразует все буквы в тексте в прописные (заглавные).
Пример
Текст из столбца «Исходный текст» нужно перевести в заглавные буквы.
В ячейке B2 пишем формулу «=ПРОПИСН(A2)» и растягиваем ее на весь столбец.
СТРОЧН
Нужно преобразовать все буквы в тексте в строчные (маленькие).
Пример
Можно также перевести текст из столбца «Заглавные буквы» в строчные.
В ячейку C2 введем формулу «=СТРОЧН(B2)» и растянем ее на весь столбец.
Убрать пробелы
Чтобы убрать лишние пробелы, есть формула СЖПРОБЕЛЫ. Эта функция оставляет только один пробел между словами.
Пример
Представим, что при копировании информации в таблицу форматирование текста было нарушено и вместо одного пробела между словами образовалась ненужная пустота. Нужно убрать лишние пробелы из исходного текста.
С помощью формулы «=СЖПРОБЕЛЫ(A2)» получим отформатированную строку в ячейке B2. Применим формулу ко всему столбцу.
Условные формулы
Чтобы использовать функции условий в Excel, применяют ЕСЛИ и его производные — ЕСЛИМН и ЕСЛИОШИБКА.
- ЕСЛИ
Выполняет логическую проверку условий, которые возвращают значение ИСТИНА или ЛОЖЬ.
Пример
У вас есть таблица с результатами экзаменов студентов. Вам нужно определить, сдал студент экзамен или нет, исходя из его баллов за экзамен. Проходной балл — 50.
В ячейку B2 вставим формулу «=ЕСЛИ(A2>=50; «Сдал»; «Не сдал»)» и применим ее ко всему столбцу. Из формулы следует, что мы получим значение «Сдал», если результат проверки условия — ИСТИНА, и «Не сдал», если ЛОЖЬ.
- ЕСЛИМН
Применяется тогда, когда условий несколько. Работает по такому принципу:
Функция последовательно проверяет условия, перечисленные в формуле, и, как только находит первое условие со значением ИСТИНА, возвращает соответствующее этому условию значение. Другие условия не проверяются, даже если они тоже истинны.
Пример
В той же таблице с баллами студентов за экзамен теперь нужно дать оценку согласно полученным баллам. Всего оценок четыре: «Отлично», «Хорошо», «Удовлетворительно» и «Неудовлетворительно».
Добавим формулу «=ЕСЛИМН(A2>=80; «Отлично»; A2>=60; «Хорошо»; A2>=50; «Удовлетворительно»; A2<50; «Неудовлетворительно»)» в ячейку C3 и растянем ее на весь столбец.
Обратите внимание, что оценки в условии нужно расположить в порядке убывания. Например, если сделать по возрастанию, то все оценки разделятся на «Неудовлетворительно» (<50) и «Удовлетворительно» (>=50).
- ЕСЛИОШИБКА
Эта функция нужна, чтобы обрабатывать возникающие при расчетах ошибки с помощью сообщения об ошибке. В сообщении можно указать любой текст.
Пример
Обработайте классический пример ошибки при делении на ноль. Пусть сообщение об ошибке звучит как «Делить на ноль нельзя».
В ячейку C2 ставим формулу «=ЕСЛИОШИБКА(A2/B2; «Делить на ноль нельзя»)» и растягиваем значение на весь столбец.
- Как применять условия для функции СУММ
Функция ЕСЛИ применяется в связке с другими функциями, например с СУММ. Рассмотрим на примерах:
— СУММЕСЛИ, чтобы посчитать сумму ячеек по определенному критерию.
«=СУММЕСЛИ(A2:A4; «Продажи»; B2:B4)» покажет итоговую сумму по показателю «Продажи».
— СУММЕСЛИМН, чтобы посчитать сумму значений в диапазоне, которые соответствуют нескольким условиям.
«=СУММЕСЛИМН(B2:B4; A2:A4; «Продажи»; C2:C4; «Январь»)» суммирует значения ячеек, где в столбце A указано «Продажи», а в столбце C — «Январь». В данном случае есть только одна такая ячейка.
- Где еще применяют ЕСЛИ
— СЧЁТЕСЛИ, чтобы посчитать количество ячеек, соответствующих одному критерию.
«=СЧЁТЕСЛИ(A1:A3; «Продажи»)» — эта формула подсчитает количество ячеек в диапазоне A1:A3, где указано слово «Продажи».
— СЧЁТЕСЛИМН, чтобы посчитать количество ячеек, которые соответствуют нескольким критериям одновременно.
«=СЧЁТЕСЛИМН(A1:A4; «Продажи»; B1:B4; «Январь»)» посчитает количество ячеек, где в столбце A указано «Продажи», а в столбце B — «Январь». Если оба условия выполняются для одной строки, то она будет включена в подсчет.
- Извлечение текста (ПСТР)
Функция ПСТР нужна для извлечения определенного количества символов из строки текста или числового ряда, начиная с заданной позиции.
Пример
Вам нужно извлечь из исходника три символа, начиная с четвертого.
C помощью формулы «=ПСТР(A2; 4; 3)» извлекаем символы из строк столбца A в столбец B.
Обратите внимание, что даже если функция не получит необходимое по условию количество символов в строке, она все равно выполнит операцию с тем количеством символов, которые есть. Если подходящие символы в строке отсутствуют, то в ячейке результата получится пустая строка.
- Замена текста
С помощью функции ЗАМЕНИТЬ можно заменить часть строки текста на другую, начиная с указанной позиции и на определенное количество символов.
Пример
Например, вам нужно скрыть последние цифры кода или номера из первого столбца, начиная с 5 символа, на 6 символов. Сделать это можно с помощью замены на звездочки *.
В ячейку B2 вставим формулу «=ЗАМЕНИТЬ(A2; 5; 6; «******»)» и применим ее ко всему столбцу. Количество новых символов для замены не обязательно должно совпадать с количеством заменяемых старых символов.
Подведем итог
- Для работы с формулами в Excel используют специальные операторы: арифметические, логические сравнения и другие.
- Математические операции, такие как сложение и умножение, можно провести двумя способами: через операторы + и * либо с помощью функций СУММ и ПРОИЗВЕД соответственно.
- В Excel можно округлять дроби с помощью функции ОКРУГЛ. В большую сторону — ОКРУГЛВВЕРХ, в меньшую — ОКРУГЛВНИЗ.
- Строки можно «скреплять» в одну с помощью оператора & или формулы СЦЕПИТЬ. При этом нужно вручную поставить пробел между ними при необходимости.
- Для работы с регистром символов в строке есть формулы ПРОПИСН и СТРОЧН.
- Формулы условий используют функцию ЕСЛИ и проверяют условия на значение ИСТИНА или ЛОЖЬ. С их помощью можно проверять диапазоны на соответствие требованиям, обрабатывать ошибки, выбирать конкретные ячейки для суммирования и т. д.
- Из ячеек можно извлекать установленное количество символов с помощью функции ПСТР, заменять символы через ЗАМЕНИТЬ, убирать пробелы с помощью СЖПРОБЕЛЫ.