#статьи
-
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
Applies ToExcel для 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
Функция СУММ
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
Введение в формулы 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 используют, когда данных очень много. Например, чтобы посчитать сумму нескольких чисел быстрее, чем на калькуляторе. Преимуществ много, поэтому работодатели часто указывают эту программу в требованиях. В конце ноября 2023 года больше 110 000 вакансий на хедхантере содержали формулировки вроде «уверенный пользователь Excel», «работа с формулами в Excel».
Кому важно знать Excel и где выучить основы
Excel нужен бухгалтерам, чтобы вести учет в таблицах. Экономистам, чтобы делать перерасчет цен, анализировать показатели компании. Менеджерам — вести базу клиентов. Аналитикам — строить и проверять гипотезы.
Программу можно освоить самостоятельно, например по статьям в интернете. Но это поможет понять только основные формулы. Если нужны глубокие знания — как строить сложные прогнозы, собирать калькулятор юнит-экономики, — пройдите курсы.
На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой. Кроме Excel вы изучите Metabase, SQL, Power BI, язык программирования Python. Программа подойдет даже тем, у кого совсем нет опыта в анализе и кто не любит математику. Вас ждут живые вебинары, мастер-классы, домашки с разбором, помощь наставников.
Урок из курса «Аналитик данных» в Skypro
Из чего состоит формула в Excel
Основные знаки:
= с него начинают любую формулу;
( ) заключают формулу и ее части;
; применяют, чтобы указать очередность ячеек или действий;
: ставят, чтобы обозначить диапазон ячеек, а не выбирать всё подряд вручную.
В таблицах Excel работают с простыми математическими действиями:
сложением +
вычитанием —
умножением *
делением /
возведением в степень ^
Еще в таблицах Excel используют символы сравнения:
равенство =
меньше <
больше >
меньше либо равно <=
больше либо равно >=
не равно <>
Основные виды
Все формулы в таблицах Excel делятся на простые, сложные и комбинированные. Их можно написать самостоятельно или воспользоваться встроенными.
Многие помнят их с уроков информатики в школе. Но даже если вы совсем не знакомы с таблицами Excel, на курсе Skypro «Аналитик данных» обучение начинают с основ. Не нужны глубокие знания математики и навыки работы с программами для анализа данных.
Простые
Применяют, когда нужно совершить одно простое действие, например сложить или умножить.
✅ СУММ. Складывает несколько чисел. Сумму можно посчитать для нескольких ячеек или целого диапазона в таблице.
=СУММ(А1;В1) — для соседних ячеек;
=СУММ(А1;С1;H1) — для определенных ячеек;
=СУММ(А1:Е1) — для диапазона.
Сумма всех чисел в ячейках от А1 до Е1
✅ ПРОИЗВЕД. Умножает числа в соседних, выбранных вручную ячейках или диапазоне таблицы.
=ПРОИЗВЕД(А1;В1)
=ПРОИЗВЕД(А1;С1;H1)
=ПРОИЗВЕД(А1:Е1)
Произведение всех чисел в ячейках от А1 до Е1
✅ ОКРУГЛ. Округляет дробное число до целого в большую или меньшую сторону. Укажите ячейку таблицы с нужным числом, в качестве второго значения — 0.
=ОКРУГЛВВЕРХ(А1;0) — к большему целому числу;
=ОКРУГЛВНИЗ(А1;0) — к меньшему.
Округление в меньшую сторону
✅ ВПР. Находит данные в таблице или определенном диапазоне.
=ВПР(С1;А1:В6;2)
- С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
- А1 по В6 — диапазон ячеек в таблице. Ищем название цвета по коду.
- 2 — порядковый номер столбца таблицы для поиска. В нём указаны названия цвета.
Формула вычислила, какой цвет соответствует коду
✅ СЦЕПИТЬ. Объединяет данные диапазона ячеек таблицы, например текст или цифры. Между содержимым ячеек можно добавить пробел, если объединяете слова в предложения.
=СЦЕПИТЬ(А1;В1;С1) — текст без пробелов;
=СЦЕПИТЬ(А1;» «;В1;» «С1) — с пробелами.
Формула объединила три слова в одно предложение
✅ КОРЕНЬ. Вычисляет квадратный корень числа в ячейке таблицы.
=КОРЕНЬ(А1)
Квадратный корень числа в ячейке А1
✅ ПРОПИСН. Преобразует текст в верхний регистр, то есть делает буквы заглавными.
=ПРОПИСН(А1:С1)
Формула преобразовала строчные буквы в прописные
✅ СТРОЧН. Переводит текст в нижний регистр, то есть делает из больших букв маленькие.
=СТРОЧН(А2)
✅ СЧЕТ. Считает количество ячеек таблицы с числами.
=СЧЕТ(А1:В5)
Формула вычислила, что в диапазоне А1:В5 четыре ячейки с числами
✅ СЖПРОБЕЛЫ. Убирает лишние пробелы. Например, когда переносите текст из другого документа и сомневаетесь, правильно ли там стоят пробелы.
=СЖПРОБЕЛЫ(А1)
Формула удалила двойные и тройные пробелы
Сложные
✅ ПСТР. Выделяет определенное количество знаков в тексте, например одно слово.
=ПСТР(А1;9;5)
- Введите =ПСТР.
- Кликните на ячейку таблицы, где нужно выделить знаки.
- Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
- Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.
В ячейке А1 формула выделила 5 символов, начиная с 9-го
✅ ЕСЛИ. Анализирует данные по условию. Например, когда в таблице нужно сравнить одно с другим.
=ЕСЛИ(A1>25;"больше 25";"меньше или равно 25")
В формуле указали:
- А1 — ячейку с данными;
- >25 — логическое выражение;
- больше 25, меньше или равно 25 — истинное и ложное значения.
Первый результат возвращается, если сравнение истинно. Второй — если ложно.
Число в А1 больше 25. Поэтому формула показывает первый результат — больше 25.
✅ СУММЕСЛИ. Складывает числа, которые соответствуют критерию. Обычно критерий — числовой промежуток или предел.
=СУММЕСЛИ(В2:В5;">10")
В формуле указали:
- В2:В5 — диапазон ячеек таблицы;
- >10 — критерий, то есть числа меньше 10 не будут суммироваться.
Число 8 меньше указанного в условии, то есть 10. Поэтому оно не вошло в сумму.
✅ СУММЕСЛИМН. Складывает числа, когда условий несколько. В формуле указывают диапазоны — ячейки таблицы, которые нужно учитывать. И условия — содержание подходящих ячеек. Например:
=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")
- D2:D6 — диапазон, из которого суммируем числа;
- C2:C6 — диапазон ячеек таблицы для категории;
- сувениры — условие, то есть числа другой категории учитываться не будут;
- B2:B6 — диапазон ячеек таблицы для компании;
- ООО XY — условие, то есть числа другой компании учитываться не будут.
Под условия подошли только ячейки D3 и D6: их сумму и вывела формула
Комбинированные
В таблицах Excel можно комбинировать несколько функций: сложение, умножение, сравнение и другие. Например, вам нужно найти сумму двух чисел. Если значение больше 65, сумму нужно умножить на 1,5. Если меньше — на 2.
=ЕСЛИ(СУММ(A1;B1)<65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))
То есть если сумма двух чисел в А1 и В1 окажется меньше 65, программа посчитает первое условие — СУММ(А1;В1)*1,5. Больше 65 — Excel задействует второе условие — СУММ(А1;В1)*2.
Сумма в А1 и В1 больше 65, поэтому формула посчитала по второму условию: умножила на 2
Встроенные
Используйте их, если удобнее пользоваться готовыми формулами, а не вписывать вручную.
- Поместите курсор в нужную ячейку таблицы.
- Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
- Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
- Внесите нужные данные. Например, числа, которые нужно сложить.
Ищите формулу по алфавиту или тематике, выбирайте любую из тех, что использовали недавно
Если вы хотите освоить Excel с нуля и эффективно работать с данными, пройдите в онлайн-университете Skypro специальный курс. Он идет всего два с половиной месяца — этого будет достаточно, чтобы узнать всё о таблицах и ускорить работу с ними.
В конце курса сможете создать калькулятор юнит-экономики и показать работодателю, что умеете считать прибыльность бизнес-модели.
Как скопировать
Если для разных ячеек таблицы нужны однотипные действия, например сложить числа не в одной, а в нескольких строках, скопируйте формулу.
- Впишите функцию в ячейку таблицы и кликните на нее.
- Наведите курсор на правый нижний угол — курсор примет форму креста.
- Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки таблицы.
- Отпустите кнопку. Появится итог.
Посчитали сумму ячеек в трех строках
Как обозначить постоянную ячейку
Это нужно, чтобы, когда вы протягивали формулу, ссылка на ячейку таблицы не смещалась.
- Нажмите на ячейку таблицы с формулой.
- Поместите курсор в нужную ячейку таблицы и нажмите F4.
- В формуле фрагмент с описанием ячейки таблицы приобретет вид $A$1. Если вы протянете формулу, то ссылка на ячейку $A$1 останется на месте.
Как поставить «плюс», «равно» без формулы
Когда нужна не формула, а данные, например +10 °С:
- Кликните правой кнопкой по ячейке таблицы.
- Выберите «Формат ячеек».
- Отметьте «Текстовый», нажмите «ОК».
- Поставьте = или +, затем нужное число.
- Нажмите Enter.
Как сделать фильтр по столбцам в Google Sheets
Фильтрация данных — это процесс выделения из набора строк и столбцов только тех, которые подходят определенным, заранее установленным условиям. Благодаря этому пользователям проще анализировать конкретные части данных и работать с ними.
Представим, что перед нами задача — собрать данные о клиентах: возраст до тридцати лет, из Москвы и Казани. Для этого нужно будет поработать с фильтром.
Таблица Excel. Работа с фильтром
Перейдите на любую ячейку таблицы, кликните «Создать фильтр».
Щелкните по выпадающему списку столбца «Город» и отметьте галочками только Москву и Казань. Нажмите «ОК».
Дальше точно так же кликните по выпадающему списку столбца «Возраст» и выберите опцию «Фильтровать по условию». Выберите в списке «Меньше или равно», а затем выставьте значение «30». Нажмите «ОК».
Если нужно вернуть исходный вид данных, можно сбросить настройки таблицы. Для этого перейдите по значку фильтра.
В онлайн-университете Skypro вы можете за пару месяцев пройти курс по Excel и получить востребованный навык для аналитики. Программа курса состоит из важнейших блоков: первичная обработка информации, работа со сложными данными и прогнозирование. Еще вы создадите проект по анализу бизнес-метрик.
Как создать сводную таблицу в Google Sheets
С помощью сводной таблицы можно объединять данные из разных таблиц и листов, чтобы посчитать общее значение.
Представим, что вам нужно посчитать, сколько продаж было в каждом отделе сети мебельных магазинов.
Сводная таблица в Google Sheets
Выделяем ячейку А1. Так программа поймет, c какими значениями нужно работать.
В верхнем меню «Вставка» выбираем «Создать сводную таблицу».
Откроется меню сводной таблицы. Выберите нужный диапазон и место. Из-за того что курсор стоит в ячейке с данными, поле диапазона заполнится автоматически. Если курсор — в пустой ячейке, нужно прописать диапазон вручную. Вы можете сделать сводную таблицу на этом же листе или на другом.
Сводная таблица в Google Sheets
На новом листе сформируем таблицу, которая покажет данные продаж по каждому отделу. В списке полей сводной таблицы нужно выбрать столбцы «Отдел» и «Сумма». Получаем итоги по каждому отделу.
Аналитики — одни из самых востребованных специалистов на рынке труда. Освоить профессию с нуля можно на курсе Skypro «Аналитик данных» и найти работу уже в процессе учебы. Преподаватели — эксперты в аналитике из топовых компаний: Skyeng, «Авито», «СберМаркет» и других. У вас будет вечный доступ к материалам и регулярным обновлениям программы, а еще гарантия трудоустройства: найдем вам работу или вернем деньги за курс.
Главное о формулах в Excel
- Формула состоит из математических знаков. Чтобы ее вписать, используют символы = ( ) ; : .
- С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
- Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.
- Значительно упростит жизнь аналитика возможность создавать сводную таблицу для расчетов и фильтровать данные по столбцам
Формула, она же функция, – одна из основных составляющих электронных таблиц, создаваемых при помощи программы Microsoft Excel. Разработчики добавили огромное количество разных функций, предназначенных для выполнения как простых, так и сложных расчетов. К тому же пользователю разрешено самостоятельно производить математические операции, что тоже можно назвать своеобразной реализацией формул. Именно о работе с этими компонентами и пойдет речь далее.
Я разберу основы работы с формулами и полезные «фишки», способные упростить процесс взаимодействия с таблицами.
Поиск перечня доступных функций в Excel
Если вы только начинаете свое знакомство с Microsoft Excel, полезно будет узнать, какие функции существуют, для чего предназначены и как происходит их создание. Для этого в программе есть графическое меню с отображением всего списка формул и кратким описанием действия расчетов.
-
Откройте вкладку «Формулы» и нажмите на кнопку «Вставить функцию» либо разверните список с понравившейся вам категорией функций.
-
Вместо этого всегда можно кликнуть по значку с изображением «Fx» для открытия окна «Вставка функции».
-
В этом окне переключите категорию на «Полный алфавитный перечень», чтобы в списке ниже отобразились все доступные формулы в Excel, расположенные в алфавитном порядке.
-
Выделите любую строку левой кнопкой мыши и прочитайте краткое описание снизу. В скобках показан синтаксис функции, который необходимо соблюдать во время ее написания, чтобы все аргументы и значения совпадали, а вычисления происходило корректно. Нажмите «Справка по этой функции», если хотите открыть страницу о ней в официальной документации Microsoft.
-
В браузере вы увидите большое количество информации по выбранной формуле как в текстовом, так и в формате видео, что позволит самостоятельно разобраться с принципом ее работы.
Отмечу, что наличие подобной информации на русском языке, еще и в таком развернутом виде, делает процесс знакомства с ПО еще более простым, особенно когда речь идет о переходе к более сложным функциям, действующим не совсем очевидным образом. Не стесняйтесь и переходите на упомянутые страницы, чтобы получить справку от специалистов и узнать что-то новое, что хотя бы минимально или даже значительно ускорит рабочий процесс.
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Вставка функции в таблицу
Теперь давайте разберемся с тем, как в Excel задать формулу, то есть добавить ее в таблицу, обеспечив вычисление определенных значений. Вы можете писать функции как самостоятельно, объявляя их название после знака «=», так и использовать графическое меню, переход к которому осуществляется так, как это было показано выше. В Комьюнити уже есть статья «Как вставить формулу в Excel», поэтому я рекомендую нажать по выделенной ссылке и перейти к прочтению полезного материала.
Использование математических операций в Excel
Если необходимо выполнить математические действия с ячейками или конкретными числами, в Excel тоже создается формула, поскольку все записи, начинающиеся с «=» в ячейке, считаются функциями. Все знаки для математических операций являются стандартными, то есть «*»– умножить, «/» – разделить и так далее. Следует отметить, что для возведения в степень используется знак «^». Вкратце рассмотрим объявление подобных функций.
Выделите любую пустую ячейку и напишите в ней знак «=», объявив тем самым функцию. В качестве значения можете взять любое число, написать номер ячейки (используя буквенные и цифровые значения слева и сверху) либо выделить ее левой кнопкой мыши. На следующем скриншоте вы видите простой пример =B2*C2, то есть результатом функции будет перемножение указанных ячеек друг на друга.
После заполнения данных нажмите Enter и ознакомьтесь с результатом. Если синтаксис функции соблюден, в выбранной ячейке появится число, а не уведомление об ошибке.
Попробуйте самостоятельно использовать разные математические операции, добавляя скобки, чередуя цифры и ячейки, чтобы быстрее разобраться со всеми возможностями математических операций и в будущем применять их, когда это понадобится.
Растягивание функций и обозначение константы
Работа с формулами в Эксель подразумевает и выполнение более сложных действий, связанных с заполнением строк всей таблицы и связыванием нескольких разных значений. В этом разделе статьи я объединю сразу две разных темы, поскольку они тесно связаны между собой и обе упрощают взаимодействие с открытым в программе проектом.
Для начала остановимся на растягивании функции. Для этого вам необходимо ввести ее в одной ячейке и убедиться в получении корректного результата. Затем зажмите точку в правом нижнем углу ячейки и проведите вниз.
В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.
Константа, или абсолютная ссылка, – обозначение, закрепляющее конкретную ячейку, столбец или строку, чтобы при растягивании функции выбранное значение не заменялось, а оставалось таким же.
Сначала разберемся с тем, как задать константу. В качестве примера сделаем постоянной и строку, и столбец, то есть закрепим ячейку. Для этого поставьте знак «$» как возле буквы, так и цифры ячейки, чтобы в результате получилось такое написание, как показано на следующем изображении.
Растяните функцию и обратите внимание на то, что постоянное значение таким же и осталось, то есть произошла замена только первого аргумента. Сейчас это может показаться сложным, но стоит вам самостоятельно реализовать подобную задачу, как все станет предельно ясно, и в будущем вы вспомните, что для выполнения конкретных задач можно использовать подобную хитрость.
В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:
-
$В$2 – при растяжении либо копировании остаются постоянными столбец и строка.
-
B$2 – неизменна строка.
-
$B2 – константа касается только столбца.
Построение графиков функций
Графики функций – тема, косвенно связанная с использованием формул в Excel, поскольку подразумевает не добавление их в таблицу, а непосредственное составление таблицы по формуле, чтобы затем сформировать из нее диаграмму либо линейный график. Сейчас детально останавливаться на этой теме не будем, но если она вас интересует, перейдите по ссылке ниже для прочтения другой моей статьи по этой теме.
Читайте также: Как построить график функции в Excel
В этой статье вы узнали, какие есть функции в Excel, как сделать формулу и использовать полезные возможности программы, делающие процесс взаимодействия с электронными таблицами проще. Применяйте полученные знания для самостоятельной практики и поставленных задач, требующих проведения расчетов и их автоматизации.