Урок 4. Основные функции и формулы

Из предыдущих уроков мы с вами уже кое-что знаем о формулах и функциях Excel. Так, в уроке 1 на этапе знакомства с таблицей эксель мы узнали, что эксель поддерживает различные формулы и встроенные функции для выполнения вычислений и обработки данных.

Это могут быть простые арифметические операции (сложение, вычитание, умножение, деление) или сложные вычисления, включая статистические, финансовые, логические и текстовые функции.

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

Отдельно разберем, что такое логическая функция ЕСЛИ (IF) и ее использование для аналитики. И научимся задействовать автосуммирование и другие встроенные функции для быстрого расчета, чтобы получить корректное значение в эксель.

Содержание:

• Введение в работу с формулами: основные операторы
• Основные функции эксель
• Логическая функция ЕСЛИ (IF) и ее использование для анализа данных
• Автосумма и другие встроенные функции для быстрого расчета
• Проверочный тест

Нам в помощь материал «Функции Excel по категориям» [Microsoft, 2023]. Также нам будет полезен материал «Полные сведения о формулах в Excel» [Microsoft, 2023]. И мы возьмем за основу материал «Использование операторов в формулах Excel» [Microsoft, 2024].

Введение в работу с формулами: основные операторы

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

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

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

Формулы могут включать:

• Числовые данные и конкретные значения (например, 5, 10).
• Ссылки на ячейки (например, A1 или B3).
• Операторы – символы для выполнения операций (например, +, -, *, /).

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

Виды операторов в эксель:

• Арифметические операторы – эти операторы используются для выполнения базовых математических операций, таких как сложение, вычитание и прочие.
• Операторы сравнения – эти операторы позволяют сравнивать значения и возвращать логическое значение TRUE (ИСТИНА) или FALSE (ЛОЖЬ).
• Текстовые операторы – эти операторы используются для работы с текстовыми значениями в ячейках.
• Ссылочные операторы – эти операторы используются для работы с диапазонами данных.

Теперь чуть подробнее об операторах эксель.

Арифметические операторы:

• Оператор + (плюс) – это сложение. Пример: =A1 + B1 (сложение значений в ячейках A1 и B1).
• Оператор - (минус) – это вычитание. Пример: =A1 - B1 (вычитание значения B1 из A1).
• Оператор * (умножение) – это умножение. Пример: =A1 * B1 (умножение значений A1 и B1).
• Оператор / (деление) – это деление. Пример: =A1 / B1 (деление значения A1 на значение B1).
• Оператор ^ (возведение в степень) – это возведение в степень. Пример: =A1^2 (возведение значения A1 в квадрат).
• Оператор % (процент) – это вычисление процента. Пример: =A1 * 10% (10% от значения в ячейке A1).

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

• Оператор = (равно) – проверяет равенство. Пример: =A1 = B1 (вернет TRUE, если значения в A1 и B1 равны).
• Оператор > (больше) – проверяет, больше ли одно значение другого. Пример: =A1 > B1 (вернет TRUE, если A1 больше B1).
• Оператор < (меньше) – проверяет, меньше ли одно значение другого. Пример: =A1 < B1 (вернет TRUE, если A1 меньше B1).
• Оператор >= (больше или равно) – проверяет, больше или равно значение. Пример: =A1 >= B1 (вернет TRUE, если A1 больше или равно B1).
• Оператор <= (меньше или равно) – проверяет, меньше ли или равно значение. Пример: =A1 <= B1 (вернет TRUE, если A1 меньше или равно B1).
• Оператор <> (не равно) – проверяет, не равны ли значения. Пример: =A1 <> B1 (вернет TRUE, если A1 и B1 не равны).

Текстовые операторы:

• Оператор & (амперсанд) используется для объединения (соединения) одной или нескольких текстовых строк в одну. Пример: =A1&B1. Тут амперсанд объединяет текст эксель в ячейках А1 и В1. Так, если в ячейке А1 будет значиться «День», а в ячейке В1 будет указано «1», в итоге мы получим «День1».
• Если нам нужен пробел, формулу нужно дополнить кавычками " " и продублировать & следующим образом: =A1 & " " & B1. Тогда в итоге мы получим «День 1». Например, в расписании мероприятий конференции.

Ссылочные операторы:

• Оператор : (двоеточие) – это оператор диапазона, указывает на диапазон ячеек. Пример: =SUM(A1:A10) (суммирует значения от ячейки A1 до A10).
• Оператор , (запятая) – это оператор объединения диапазонов, указывает на отдельные ячейки или диапазоны. Пример: =SUM(A1:A10, B1:B10) (суммирует значения диапазонов A1-А10 и B1-В10).

Теперь несколько слов о том, зачем нужны операторы в эксель.

Зачем нужны операторы в Excel:

• Автоматизация расчетов – операторы позволяют автоматизировать вычисления. Вместо ручного подсчета можно использовать формулы, что экономит время и снижает вероятность ошибок.
• Анализ данных – с помощью операторов можно быстро проводить анализ данных. Например, сравнивать значения, вычислять проценты, находить максимальные и минимальные значения, применять сложные логические условия.
• Гибкость работы с данными – операторы позволяют проводить различные манипуляции с данными, от арифметических вычислений до объединения текстов и работы с логическими выражениями.

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

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

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

Формулы с текстовыми операторами используются для работы с текстом, например, для объединения строк.

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

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

Итак, мы сделали введение в работу с формулами и рассмотрели основные виды формул. Разумеется, их намного больше, в чем легко убедиться, если просто открыть любой файл эксель и изучить вкладку «Формулы»:

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

Основные функции эксель

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

Функции могут принимать аргументы – значения, с которыми будет производиться вычисление. Аргументами могут быть числа, текст, логические значения, такие как TRUE или FALSE, массивы, значения ошибок, такие как #N/A, или ссылки на ячейки.

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

Наиболее часто употребляемые функции эксель:

Если пояснения относительно функции COUNT кажутся слишком сложными и малоприменимыми, вам, скорее всего, пока что эта функция в работе не нужна.

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

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

Ссылки на ячейки:

• Абсолютная ссылка ($A$1) – фиксирует ссылку, чтобы она не изменялась при копировании формулы.
• Относительная ссылка (A1) – при копировании формулы ссылка изменяется в зависимости от ее нового положения.

По умолчанию ссылка на ячейку является относительной. Поэтому, если вам по какой-то причине требуется, чтобы определенная ячейка в экселе оставалась неизменной при любых внешних манипуляциях, нужно дополнить адрес ячейки значком $. Например, $A$1.

Для новичков в экселе это информация на будущее, а тем, кому эти сведения нужны были «еще вчера», можем рекомендовать материал «Использование относительных и абсолютных ссылок» [Microsoft, 2021].

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

Так, формула = (A1 + B1) * C1 / 2 сначала сложит A1 и B1, затем результат умножит на C1 и разделит на 2.

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

Логическая функция ЕСЛИ (IF) и ее использование для анализа данных

Функция ЕСЛИ (IF) в Excel – это одна из наиболее важных логических функций, которая позволяет проверять условие и возвращать один результат, если условие истинно (True), и другой результат, если условие ложно (False).

Посмотрим, как выглядит функция ЕСЛИ (IF):

=ЕСЛИ(логическое_выражение;значение_если_истина; значение_если_ложь)

Далее рассмотрим, какие аргументы принимает данная функция. Мы помним, что аргументы – это значения, с которыми будет производиться вычисление. Мы видим три основных аргумента:

• логическое_выражение – условие, которое необходимо проверить. Это может быть сравнение (например, A1 > 10);
• значение_если_истина – результат, который возвращается, если условие выполняется (истинно);
• значение_если_ложь – результат, который возвращается, если условие не выполняется (ложно).

Для наглядности приведем примеры использования функции ЕСЛИ.

Где и как нам все это может пригодиться? Сфера применения функции ЕСЛИ (IF) весьма широка, начиная от простых математических вычислений до проверки результатов тестов, контрольных и курсовых работ и предоставления скидок в торговых сетях.

Зачем нужна функция ЕСЛИ (IF):

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

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

Автосумма и другие встроенные функции для быстрого расчета

И, наконец, одна из самых востребованных функций эксель – автосуммирование. Собственно, немало людей используют эксель исключительно с тем, чтобы не считать на калькуляторе, как когда-то считали наши родители, или на счетах, как давным-давно делали подсчеты наши бабушки.

Автосумма и другие встроенные функции эксель позволяют быстро выполнять вычисления и анализ данных без необходимости ручного ввода формул. Автосумму можно найти как на вкладке «Формулы» слева вверху (см. рисунок 1 в этом уроке), так и на вкладке Главная > Редактирование справа вверху:

Как посчитать в экселе сумму:

• Выделите ячейку, куда нужно внести результат суммирования. Обычно это ячейка под или рядом с диапазоном чисел, которые вы хотите сложить.
• Нажмите кнопку «Автосумма» (Σ) на вкладке «Главная» или «Формулы».

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

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

Так, если вы выбрали диапазон A1-А5, эксель предложит формулу:

=SUM(A1:A5)

После нажатия Enter сумма этих чисел появится в ячейке А6.

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

Проверьте свои знания

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

Далее мы будем изучать создание таблиц.

Ольга Обломова