Основные формулы в экселе: 13 функций Excel, которые должен знать каждый специалист

Формулы Excel для анализа данных: базовый и расширенный

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

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

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

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

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

Формулы Microsoft Excel для анализа данных

СЦЕПИТЬ

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

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

ФОРМУЛА

= Объединить (ячейка1, ячейка2)

LEN

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

ФОРМУЛА

= Лен (ячейка)

COUNTA

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

ФОРМУЛА

= COUNTA (ячейка)

ВПР

Формула ВПР используется для сопоставления данных из таблицы с входными данными. ВПР предлагает два режима сопоставления: точное и приблизительное. Эти два режима контролируются диапазоном поиска.

Теперь вам может быть интересно, какая роль является точным и приблизительным предложением. Что ж, если вы установите для диапазона значение ИСТИНА, тогда он будет искать приблизительное совпадение, но если вы установите диапазон в значение ЛОЖЬ, тогда он будет искать точное совпадение.

ФОРМУЛА

= ВПР (ПРОСМОТР-ЗНАЧЕНИЕ; ТАБЛИЦА_ARRAY; COL_INDEX_NUM; [RANGE_LOOKUP])

MINIFS / MAXIFS

MINIFS и MAXIFS аналогичны знанию минимального и максимального значения. Вам просто нужно установить критерии и дать им набор значений для работы. Затем он ищет минимальное и максимальное значения и сопоставляет их с установленными критериями.

ФОРМУЛА

= MINIFS (мин-диапазон, диапазон-критериев1, критерии1,…)

= МАКСЕСЛИМН (макс-диапазон, диапазон-критериев1, критерии1,…)

СРЕДНИЙ

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

ФОРМУЛА

= СРЕДНЕЛИМН (средн_диапазон, диапазон-критериев1, критерий1,…)

СЧЁТЕСЛИМН

Функция СЧЁТЕСЛИМН может использоваться для подсчета количества экземпляров набора данных, соответствующих определенным критериям. Функция позволяет добавлять в запрос неограниченные критерии.

ФОРМУЛА

= СЧЁТЕСЛИМН (ДИАПАЗОН; КРИТЕРИИ1;….)

СУММПРОИЗВ

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

ФОРМУЛА

= СУММПРОИЗВ (ДИАПАЗОН1; ДИАПАЗОН2) / ВЫБРАТЬ ЯЧЕЙКУ

НАЙТИ / ПОИСК

Если вы хотите изолировать определенный текст в наборе данных, можно использовать функцию НАЙТИ / ПОИСК. Обе функции одинаковы, но между ними есть одно существенное различие. Функция НАЙТИ возвращает только совпадения с учетом регистра, тогда как функция ПОИСК не имеет ограничений.

ФОРМУЛА

= НАЙТИ (ТЕКСТ; ВНУТРИ ТЕКСТА;[START-NUMBER])

= ПОИСК (ТЕКСТ; ВНУТРИ ТЕКСТА; [START-NUMBER])

ИНДЕКС / МАТЧ

Функция ИНДЕКС / ПОИСКПОЗ может использоваться для поиска конкретных данных на основе входных данных. Функцию ИНДЕКС / ПОИСКПОЗ можно использовать вместе, чтобы преодолеть ограничение, которое имеет ВПР. Когда вы используете ИНДЕКС / ПОИСКПОЗ вместе, они будут точно определять ссылку на данные и искать значение в одномерном массиве.

ФОРМУЛА

= ИНДЕКС (столбец данных, которые вы хотите вернуть)

= ПОИСКПОЗ (общая точка данных, которую вы пытаетесь сопоставить, столбец другого источника данных, имеющий общую точку данных, 0))

СУММЕСЛИМН

СУММЕСЛИМН — одна из известных формул Excel для анализа данных. Функция используется для вычисления суммы значений на основе нескольких критериев.

ФОРМУЛА

= СУММЕСЛИ (ДИАПАЗОН; КРИТЕРИИ;[SUM_RANGE])

КЛАССИФИЦИРОВАТЬ

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

ФОРМУЛА

= РАНГ (ЯЧЕЙКА; ДИАПАЗОН-ПРОТИВ;[ORDER])

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

ИМЕТЬ В ВИДУ()

ФОРМУЛА

= СРЕДНИЙ (число1;[num2],…)

МЕДИАНА ()

ФОРМУЛА

= МЕДИАНА (число1;[num2],…)

РЕЖИМ()

ФОРМУЛА

= РЕЖИМ (число1;[num2],…)

НИЖЕ()

Функция LOWER заменяет символы верхнего регистра на символы нижнего регистра.

ФОРМУЛА

= НИЖНИЙ (текст)

ВЕРХНИЙ ()

Функция UPPER заменяет символы нижнего регистра на символы верхнего регистра.

ФОРМУЛА

= ВЕРХНИЙ (текст)

ПРАВИЛЬНЫЙ()

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

ФОРМУЛА

= PROPER (текст)

ПОДРЕЗАТЬ()

Функция TRIM удаляет ненужные пробелы между словами в строке.

ФОРМУЛА

= ОБРЕЗАТЬ (текст)

WEEKNUM ()

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

ФОРМУЛА

= WEEKNUM (s.no,[return-type])

РАБОЧИЙ ДЕНЬ()

Функция РАБДЕНЬ используется для вывода даты рабочего дня, только если указано количество дней.

ФОРМУЛА

= РАБДЕНЬ (s.no,[return-type])

NETWORKDAYS ()

Функция ЧИСТРАБДНИ дает количество рабочих дней, если указаны два предоставленных дня. Он рассчитывает рабочие дни между этими двумя предоставленными днями, исключая выходные и праздничные дни, если таковые имеются.

ФОРМУЛА

= ЧИСТРАБДНИ (дата начала, дата окончания, [holiday])

YEARFRAC ()

YEARFRAC — это функция, которая позволяет пользователю узнать долю года, только если указаны две даты.

ФОРМУЛА

= ГОД (дата начала, дата окончания, [basis])

ДАТА ()

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

= ДАТА (дата начала, месяцы)

ТИП()

Функция TYPE дает числовой код в качестве вывода, когда вы передаете тип данных внутри этой функции.

ФОРМУЛА

= ТИП (значение)

ЗАКЛЮЧЕНИЕ

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

Знайте эти основные формулы, чтобы научиться пользоваться Excel

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

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

Индекс

• 1 Что такое формула в Excel?
• 2 Формулы, которые нужно знать, чтобы начать изучать Excel
  • 2.1 Основные математические операции
    • 2.1.1 Сума
    • 2.1.2 вычитание
    • 2.1.3 умножение
    • 2.1.4 деление
  • 2.2 СРЕДНИЙ
  • 2.3 SI
  • 2.4 БУДЕТ СЧИТАТЬ
  • 2.5 ГИПЕРССЫЛКА
  • 2.6 ПОИСКV

Что такое формула в Excel?

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

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

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

Формулы, которые нужно знать, чтобы начать изучать Excel

Основные математические операции

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

Сума

Чтобы выполнить сумму Excel, мы должны ввести следующую формулу:

=СУММ(A1:A2) или =СУММ(2+2)

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

вычитание

С другой стороны, процесс вычитания намного проще, потому что вам нужно использовать только символ «-». В этом смысле у нас будет что-то вроде:

= А1 – А3

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

умножение

 

Как и вычитание, умножение в Excel основано на использовании звездочки в качестве символа. Таким образом, для умножения двух или более элементов у нас будет что-то вроде:

= А1 * А3

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

деление

Наконец, для выполнения операций деления мы будем использовать черту как символ. Таким образом, мы имеем:

= А1/А3

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

СРЕДНИЙ

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

Чтобы использовать его, вы должны ввести:

=СРЕДНЕЕ(A1:B3)

SI

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

В этом смысле синтаксис выглядит следующим образом:

= ЕСЛИ (Условие, Значение, если оно истинно, Значение, если оно неверно)

Таким образом, если проходной балл равен 50, у нас будет такой пример:

= ДА(B2>=50, ПРОШЕЛ, НЕ ПРОШЕЛ)

БУДЕТ СЧИТАТЬ

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

Для этого используется следующий синтаксис:

СЧЁТ (A1:B3)

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

ГИПЕРССЫЛКА

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

Формула выглядит следующим образом:

=ГИПЕРССЫЛКА(ссылка, текст)

=ГИПЕРССЫЛКА(«www.windowsnoticias.com», «Познакомьтесь с нашим сайтом»)

ПОИСКV

ВПР — одна из самых популярных функций в Excel, которая позволяет найти нужное значение в диапазоне ячеек.. Таким образом, вы сможете не просматривать каждую ячейку, чтобы найти нужные данные, и вместо этого будет достаточно применить соответствующую формулу. Синтаксис следующий:

=ПРОСМОТР(ПОИСК ЗНАЧЕНИЯ, ДИАПАЗОН ЯЧЕЙ, НОМЕР СТОЛБЦА, ГДЕ НАХОДЯТСЯ Искомые данные, Упорядочено)

В этом смысле первым вводимым параметром является искомое значение, которое является не чем иным, как ссылкой, которую мы будем использовать для поиска рассматриваемых данных. Затем наступает очередь диапазона ячеек, в которых будет выполняться поиск, а затем добавляется номер столбца, в котором находятся данные, которые мы хотим найти. . Столбцы начинают нумероваться с того, который вы выбрали. Наконец, Sorted относится к тому, будет ли поиск возвращать точное или приблизительное совпадение. Для точного совпадения введите FALSE.

Формулы Excel: простые формулы

Урок 2: простые формулы

/en/excelformulas/about-this-tutorial/content/

Введение

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

Дополнительно: загрузите нашу рабочую тетрадь. 9 ) для показателей.

Стандартные операторы

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

Понимание ссылок на ячейки

Хотя вы можете создавать простые формулы в Excel вручную (например, =2+2 или =5*5 ), большую часть времени вы будете использовать адресов ячеек для создания формулы . Это известно как создание ссылка на ячейку . Использование ссылок на ячейки гарантирует, что ваши формулы всегда будут точными, поскольку вы можете изменить значение ячеек, на которые ссылаются, без необходимости переписывать формулу.

Использование ссылок на ячейки для пересчета формулы

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

Примеры простых формул

Чтобы создать формулу:

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку B3 .

   Выбор ячейки B3

2. Введите знак равенства (=) . Обратите внимание, как он отображается в ячейке и в формуле bar .

   Ввод знака =

3. Введите ячейка адрес ячейки, на которую вы хотите сначала сослаться в формуле: ячейка B1 в нашем примере. Вокруг указанной ячейки появится синяя рамка .

   Ссылка на ячейку B1

4. Введите математический оператор , который вы хотите использовать. В нашем примере мы напечатаем знак добавления ( + ).
5. Введите адрес ячейки ячейки, на которую вы хотите сослаться второй в формуле: ячейка B2 в нашем примере. Вокруг указанной ячейки появится красная рамка .

   Ссылка на ячейку B2

6. Нажмите Введите на клавиатуре. Формула будет вычислено , а значение будет отображаться в ячейке.

   Полная формула и расчетное значение

Если результат формулы слишком велик для отображения в ячейке, он может отображаться в виде знаков фунта стерлингов (#######) вместо значения. Это означает, что ширина столбца недостаточно широка для отображения содержимого ячейки. Просто увеличить ширину столбца для отображения содержимого ячейки.

Изменение значений с помощью ссылок на ячейки

Истинное преимущество ссылок на ячейки заключается в том, что они позволяют вам обновлять данные на листе без необходимости переписывать формулы. В приведенном ниже примере мы изменили значение ячейки B1 с 1200 до 1800 долларов. Формула в ячейке B3 автоматически пересчитает и отобразит новое значение в ячейке B3.

Пересчитанное значение ячейки

Excel не всегда будет сообщать вам , если ваша формула содержит ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, вы можете прочитать урок «Перепроверьте свои формулы» из нашего учебника по формулам Excel.

Чтобы создать формулу методом «укажи и щелкни»:

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D3 .

   Выбор ячейки D3

2. Введите знак равенства (=) .
3. Выберите ячейку , на которую вы хотите сослаться первой в формуле: ячейка B3 в нашем примере. В формуле появится адрес ячейки , а вокруг указанной ячейки появится пунктирная синяя линия .

   Ссылочная ячейка B3

4. Введите математический оператор , который вы хотите использовать. В нашем примере мы введем знак умножения (*) .
5. Выберите ячейку , которую вы хотите указать второй в формуле: ячейка C3 в нашем примере. В формуле появится адрес ячейки , а вокруг указанной ячейки появится пунктирная красная линия .

   Ссылка на ячейку C3

6. Нажмите Введите на клавиатуре. Формула будет вычислено , и в ячейке отобразится значение .

   Завершенная формула и вычисленное значение

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

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

Чтобы изменить формулу:

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

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

   Выбор ячейки B3

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

   Выбор формулы для редактирования

3. Вокруг всех ячеек, на которые есть ссылки, появится рамка . В нашем примере мы изменим вторую часть формулы, указав ссылку на ячейку B2 вместо ячейки C2 .

   Неуместная ссылка на ячейку
   

4. Когда закончите, нажмите Введите на клавиатуре или нажмите галочку в строке формул.

   Редактирование формулы

5. Формула будет обновлена ​​ , а в ячейке будет отображаться новое значение .

   Новое рассчитанное значение

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

Чтобы отобразить все формулы в электронной таблице, вы можете, удерживая клавишу Ctrl , нажать ` (большое ударение). Клавиша серьезного акцента обычно находится в верхнем левом углу клавиатуры. Вы можете нажать Ctrl+` еще раз, чтобы вернуться к обычному виду.

Вызов!

1. Откройте существующую книгу Excel. Если вы хотите, вы можете использовать нашу рабочую тетрадь.
2. Создайте простую формулу сложения, используя ссылок на ячейки . Если вы используете пример, создайте формулу в ячейке B4 для расчета общего бюджета.
3. Попробуйте изменить значение ячейки, на которую ссылается формула. Если вы используете пример, измените значение ячейки B2 до 2000 долларов. Обратите внимание, как формула в ячейке B4 пересчитывает итог.
4. Попробуйте использовать метод «наведи и щелкни» для создания формулы. Если вы используете пример, создайте формулу в ячейке G5 , которая умножает стоимость салфеток на количество , необходимое для расчета общей стоимости .
5. Редактировать формулу с помощью строки формул. Если вы используете пример, отредактируйте формулу в ячейке
   B9 , чтобы изменить знак деления ( / ) на знак минус ( — ).

Продолжать

Предыдущий: Об этом учебнике

Далее:Сложные формулы

/en/excelformulas/complex-formulas/content/

12 основных формул Excel, которые необходимо знать

Итак, вы только начинаете работать с Excel и слышали о том, насколько хороши формулы. Есть только одно — вы мало что знаете о том, как использовать формулы Excel. Забудьте о сложных вещах, таких как функция PI в Excel; основы тоже могут сбивать с толку!

Давайте исправим это прямо сейчас. В качестве бонуса мы также составили список из 12 рекомендуемых основных формул Excel.

---

Загрузите файл с базовыми формулами Excel!

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

Введите адрес электронной почты

---

Для чего в Excel используются формулы?

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

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

Эти операции представлены символами плюс, минус, звездочка и косая черта (+, -, *, /) соответственно. Чтобы сигнализировать о том, что ожидается вычисление, всегда начинайте формулу Excel со знака равенства (=).

Итак, это формула:

Но это не формула:

В чем разница между формулами и функциями?

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

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

Названия этих функций легко запоминаются, поскольку они близки к действию, которое они выполняют. Например, есть функция СУММ для добавления значений, функция МНИЖЕ для преобразования текста в нижний регистр и функция СЧИТАТЬПУСТОТЫ, которая подсчитывает количество пустых ячеек в диапазоне.

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

Например, вот как найти среднее значение группы чисел. Мы можем ввести формулу, добавив числа, используя символ плюс (+), а затем, используя косую черту (/), разделив эту сумму на количество значений в наборе данных. Однако вместо выполнения этих шагов в Excel существует предварительно созданная функция СРЗНАЧ, позволяющая выполнить эту задачу с минимальными усилиями с вашей стороны.

Синтаксис функции и возвращаемое значение

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

Все функции имеют следующую общую структуру:

знак равенства > имя функции > открывающая скобка > аргументы > закрывающая скобка

Например, чтобы найти среднее значение в ячейках от A2 до A10, формула СРЗНАЧ должна быть написана следующим образом:

  =СРЕДНЕЕ(A2:A10)  

При вводе вашей функции Excel помогает вам в следующем:

• Предлагая список функций, которые начинаются с введенных вами букв. Если вы нажмете клавишу табуляции, Excel дополнит имя функции. первая функция в списке и открывающая скобка. Или вы можете прокрутить вниз до нужного, нажать клавишу Tab и закончить, введя аргументы и закрывающие скобки.
• Путем запроса имени следующего аргумента в последовательности.
• Различая обязательные и необязательные аргументы. Необязательные аргументы показаны в квадратных скобках.

Различные способы вставки формул 

Excel использует символ равенства для распознавания формул (и, соответственно, функций).

1. Использование явных чисел

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

  =7+2*2 
 

2. Использование ссылок на ячейки

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

  =A7+A8*A8  

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

Примечание:

• Excel следует правилу операций PEMDAS (круглые скобки — показатели степени — умножение — деление — сложение — вычитание). Это означает, что значения в круглых скобках будут обработаны в первую очередь, затем степени и так далее.

Различные способы вставки функций

В Excel имеется более 400 функций, к которым можно получить доступ любым из следующих трех способов:

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

3. Вы также можете использовать вкладку Формулы для выбора функции по категории.

Преимущество методов 2 и 3 заключается в определении каждого аргумента и предварительном просмотре результата во время ввода значений или ссылок на ячейки.

12 основных функций Excel, которые вам необходимо знать

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

1. СУММ

Функция СУММ суммирует значения. Вы можете добавить явные числовые значения, ссылки на ячейки, диапазоны или сочетание всех трех. Основной синтаксис:

.
  СУММ(число1, [число2], [число3]...)  

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

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

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

Вот полезный совет: если вы хотите быстро просуммировать значения в непрерывном (непрерывном) диапазоне, перейдите к самой следующей ячейке и щелкните значок Автосумма на вкладке «Главная». Excel вставит функцию СУММ, чтобы найти сумму всех предыдущих ячеек, содержащих числовые значения.

Ярлык Alt = также выполняет это.

2. СРЗНАЧ

Другим основным арифметическим вычислением, которое выполняет Excel, является функция СРЗНАЧ. Как было показано ранее, это устраняет необходимость выполнения двухшагового расчета и, следовательно, является одной из наших рекомендуемых основных функций.

Синтаксис функции СРЗНАЧ:

  СРЗНАЧ(число1, [число2], ….)  

Аргументы также можно вводить в виде диапазона ячеек.

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

3. ЕСЛИ

Функция ЕСЛИ входит в область логических функций. Логические функции в основном проверяют, является ли ситуация истинной или ложной. Функция ЕСЛИ делает еще один шаг вперед, выполняя одно действие, если ситуация верна, и другое действие, если она ложна. Эта функция — отличный пример того, как Excel может превратить простую таблицу данных в аналитический инструмент.

Тестируемая ситуация может быть такой, когда одно значение или выражение равно, больше или меньше другого значения или выражения.

Синтаксис функции ЕСЛИ:

  ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь]) 
 

• Logical_test — тестируемое выражение.
• Value_if_true — это значение или выражение, которое Excel должен вернуть, если ячейка проходит логическую проверку.
• Value_if_false — это значение или выражение, которое Excel должен вернуть, если логическая проверка не пройдена.

В случае функции ЕСЛИ, если значение_если_истина опущено, необходимо указать значение_если_ложь , и наоборот. Но оба нельзя опускать.

С помощью ЕСЛИ мы можем заставить Excel выполнять другие вычисления или отображать другое значение в зависимости от результата логического теста. Функция ЕСЛИ запрашивает выполнение логического теста, какое действие следует предпринять, если тест верен, и альтернативное действие, если результат теста неверен.

Хотя и второй, и третий аргументы объявлены необязательными, должен быть указан хотя бы один из этих аргументов.

  =ЕСЛИ(B2>=90,«Выдающийся», «»)  

Приведенная выше формула проверяет, больше ли значение в ячейке B2 или равно 90. Если это так, Excel возвращает текстовое значение «Выдающийся» ( обратите внимание, что текстовые значения вводятся в двойных кавычках).

Если значение не больше или равно 90, ячейка должна оставаться пустой (обратите внимание на пустые двойные кавычки). Затем эта формула копируется в ячейки с C3 по C8, где C3 оценивает значение в ячейке B3 и так далее.

Чтобы узнать больше об относительных ссылках на ячейки и о том, как они помогают при копировании формул, ознакомьтесь с этим ресурсом (Относительные ссылки в Excel — руководство для начинающих).

4. СУММЕСЛИМН

СУММЕСЛИМН — очень полезная функция Excel. Он сочетает в себе базовую операцию СУММ с логическим тестом ЕСЛИ, чтобы добавить только те ячейки, которые соответствуют нескольким заданным пользователем критериям.

Можно отправить до 127 пар критериев. Ячейки, которые считаются «совпадающими» или «подходящими», должны удовлетворять все соответствуют заявленным критериям. СУММЕСЛИМН превосходит функцию СУММЕСЛИ, которая ограничивается одновременной оценкой только одного условия.

Синтаксис СУММЕСЛИМН:

  СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2], [критерий2],...) 
 

• Sum_range — диапазон ячеек для суммирования.
• Criteria_range1 — диапазон ячеек для оценки.
• Критерии1 — условие, которому должны удовлетворять ячейки в критерии_диапазона1 .
• Criteria_range2 — это второй диапазон ячеек для оценки.
• Критерии2 — — это условие или критерий, которому должны удовлетворять ячейки в критерии_диапазона2 .

Все аргументы после критерия 1 являются необязательными.

Вот пример работы функции СУММЕСЛИМН.

  =СУММЕСЛИМН($C$2:$C$10,$B$2:$B$10,E2)  

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

5. СЧЁТЕСЛИМН

Функция СЧЁТЕСЛИМН является ещё одним членом семейства функций ЕСЛИ. Подсчитываются только ячейки, удовлетворяющие всем заявленным критериям. СЧЁТЕСЛИМН превосходит функцию СЧЁТЕСЛИ, которая позволяет одновременно оценивать только одно условие.

Синтаксис функции СЧЁТЕСЛИМН:

  COUNTIFS(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2]…) 
 

• Criteria_range1 — это первый оцениваемый диапазон.
• Criteria1 — это первый критерий, которому должны удовлетворять ячейки в критерии_диапазона1 .
• Criteria_range2 — второй оцениваемый диапазон.
• Criteria2 — это первый критерий, который ячейки в критерии_диапазон2 должен удовлетворять.

Все пары после критериев1 являются необязательными. Допускается до 127 пар диапазонов и критериев.

Чтобы подсчитать количество торговых представителей в северной команде, которые добились продаж на сумму более 1500 долларов, используйте формулу:

  =СЧЁТЕСЛИМН(B2:B10,E1,C2:C10,E2)  

Примечание . Как правило, текстовые значения в СЧЁТЕСЛИМН необходимо заключать в двойные кавычки («»), а числа — нет. Но когда логический оператор (> или <) используется с числом, и число, и оператор должны быть заключены в кавычки.

  =СЧЁТЕСЛИМН(B2:B10,E1,C2:C10,   "   >1500") 
 

6. ВПР

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

Синтаксис ВПР:

  ВПР(искомое_значение, массив_таблиц, индекс_столбца, [диапазон_просмотра])  

Вот что означают эти аргументы:

• Lookup_value — — это то, что вы хотите найти.
• Table_array — где вы хотите его искать.
• Col_index_num — номер столбца в диапазоне, содержащем возвращаемое значение.
• Range_lookup — введите TRUE для приблизительного совпадения или FALSE для точного совпадения.

Формула

  =VLOOKUP(A14,$A$2:$B$10,2,FALSE)  

вводится в ячейку B14, которая возвращает имя сотрудника, соответствующее идентификационному номеру.

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

7. COUNT

Функция COUNT подсчитывает количество ячеек, содержащих числа. Вы можете использовать эту функцию, чтобы получить количество записей в числовом поле, которое находится в диапазоне или массиве чисел. Синтаксис функции СЧЁТ:

  =СЧЁТ(значение1, [значение2]...)  

Помещение функции СЧЁТ в ячейку B6 ниже определяет, что между ячейками B1 и B5 есть три числовых значения.

  =COUNT(B1:B5)  

8. TRIM

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

Следуйте этому простому формату:

  =TRIM(текст)  

TRIM работает с текстовой строкой, введенной в двойных кавычках, или со ссылкой на одну ячейку.

  =ОТРЕЗАТЬ(A1)  

9. ВЛЕВО 

Функция ВЛЕВО используется для извлечения определенного количества символов из текстовой строки, например определенной части телефонного номера. Синтаксис функции ВЛЕВО:

.
  =LEFT(text, [num_chars])  

Num_chars относится к количеству символов, которые нужно извлечь, считая слева. Если num_chars Аргумент опущен, Excel возвращает только крайний левый символ.

  =ЛЕВАЯ(A2,3)  

10. ПРАВАЯ 

Функция ПРАВАЯ используется для извлечения определенного количества символов из текстовой строки, например определенной части телефонного номера. Синтаксис функции ПРАВИЛЬНО:

.
  =RIGHT(текст, [количество_знаков])  

Число_символов относится к числу символов, которые необходимо извлечь, считая справа. Если num_chars Аргумент опущен, Excel возвращает только самый правый символ.

  =ПРАВО(A2, 4)  

11. MID

Функция MID используется для извлечения текста из середины текстовой строки. Синтаксис функции MID:

.
  =MID(text, start_num, num_chars)  

Аргумент start_num сообщает Excel, с какого номера позиции начинать извлечение (считая слева), а num_chars сообщает, сколько символов нужно извлечь.

  =MID(A2,4,3)  

12. СЦЕП

Наша последняя базовая функция Excel — СЦЕП. Эта функция объединяет значения из нескольких ячеек в одно. Это полезно для объединения различных частей текста, например, для объединения имени и фамилии в полное имя.