Основные формулы в экселе: 15 формул в Excel, которые помогут в работе

Основные формулы Excel и как их использовать • BUOM

Автор: редакционная команда Indeed

7 апреля 2021 г.

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

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

Что такое формулы и функции Excel?

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

Например, предположим, что ячейка A1 содержит значение «2», а ячейка B1 содержит значение «3». Чтобы получить сумму A1 и A2 в ячейке C1, вы должны ввести формулу «=A1+B1» в ячейке C1. Значение в C1 теперь должно быть «5». Если вы измените значение в A1 на «4», значение в C1 изменится на «7». Эта функциональность означает, что вам не нужно вручную вычислять значения каждый раз, когда вам нужно изменить элемент.

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

Совет для профессионалов: многие функции Excel также можно использовать в других программах для работы с электронными таблицами, таких как Google Sheets.

Основные формулы в Excel

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

СЧИТАТЬ

С помощью функции COUNT вы можете определить, сколько ячеек содержит числовое значение. Например, вы можете использовать функцию COUNT, чтобы просмотреть первые 100 записей столбца A и вернуть количество ячеек, в которых есть числа.

Формула этой функции: =СЧЁТ(Диапазон).

СЧЁТ

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

Формула для этой функции: =СЧЁТЗ(Диапазон).

СУММА

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

Формула для этой функции: =СУММ(Диапазон).

СРЕДНИЙ

Как и СУММ, формула СРЗНАЧ возвращает среднее значение предоставленных ячеек. Он берет общую сумму ячеек, а затем делит ее на количество ячеек. По сути, это функция СУММ, разделенная на функцию СЧЁТ.

Формула этой функции: =СРЗНАЧ(Диапазон).

операторы ЕСЛИ

Операторы ЕСЛИ очень полезны, особенно в сочетании с другими формулами. Иногда вам может потребоваться запустить формулу только при определенных условиях. Вы можете ввести эти условия в формулу ЕСЛИ вместе с тем, что вы хотите, чтобы она делала в зависимости от того, были ли эти условия выполнены или нет.

Формула для операторов ЕСЛИ: =ЕСЛИ(условие, ИСТИНА, ЛОЖЬ).

Например, допустим, вы составляете маркетинговый список для предстоящей отправки по электронной почте. Вы хотите отправить письмо клиентам, которые в этом году потратили больше денег (столбец A), чем в прошлом году (столбец B). В этом случае вы должны использовать формулу = ЕСЛИ (A1> B1, «Отправить», «Не отправлять»). Затем, если значение в A1 больше, чем значение в B1, формула вернет текст «Отправить». В противном случае он вернет «Не отправлять».

СЦЕПИТЬ

С помощью функции СЦЕПИТЬ вы можете объединить значения двух или более ячеек. Например, если есть список имен в столбце A и фамилий в столбце B, можно использовать CONCATENATE для возврата полного имени в столбце C.

Формула для этой функции: =СЦЕПИТЬ(Значение1, Разделитель, Значение2). В приведенном выше примере формула в C1 будет =СЦЕПИТЬ(A1, » «, B1).

МАКС. и МИН.

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

Формула для функции MAX: =MAX(Value1, Value2). Например, =MAX(B1, B10) вернет наибольшее значение, найденное между B1 и B10.

Формула для функции MIN: =MIN(Value1, Value2). Например, =MIN(B1, B10) вернет наименьшее значение между B1 и B10.

А ТАКЖЕ

Функция AND проверяет, является ли что-то истинным или ложным.

Например, формула =И(A6>10, B4=»Печать») вернет значение ИСТИНА, если A6 больше 10, а B4 содержит текст «печать». Если какое-либо из этих условий не выполняется, формула возвращает ЛОЖЬ. Внутри функции AND вы можете добавить столько условий, сколько вам нужно.

XLOOKUP

XLOOKUP очень полезен для поиска информации в электронной таблице. С помощью этой функции вы предоставляете значение в данном столбце или строке и запрашиваете соответствующее значение. Например, есть таблица с именами в столбце A, от A1 до A10, и их соответствующими днями рождения в столбце B, от B1 до B10. Использование функции XLOOKUP позволит вам найти чей-то день рождения. В этом примере формула может выглядеть примерно так =XLOOKUP(«Пирожок»,A1:A10,B1:B10).

XLOOKUP — это новая формула для пользователей Excel. Если у вас более старая версия Excel, вам может понадобиться использовать функции ВПР или ГПР, которые выполняют практически ту же цель.

Советы по использованию формул Excel

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

Как ввести формулу

1. Начните с нажатия на ячейку, где должен появиться результат.

2. Над электронной таблицей, расположенной рядом с кнопкой Fx, есть длинная белая полоса, где вы можете ввести формулу. Это известно как панель формул. Все формулы начинаются с оператора «=», за которым следует формула. Например, чтобы найти сумму A1 и A2, введите «=A1+A2».

Как использовать функцию

Чтобы ввести функцию, выполните следующие действия:

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

2. Найдите кнопку Fx, расположенную над электронной таблицей и рядом с панелью формул. Должно появиться новое окно, отображающее доступные функции.

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

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

5. После заполнения этой страницы заполненная формула появится в строке формул.

Как изменить формулу

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

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

2. Щелкните внутри этой формулы и отредактируйте ее по мере необходимости. Например, можно изменить формулу с «=A1+A2» на «=A1+A3» или «=СУММ(A1:A2)» на «=СУММ(B1:B2)».

3. После редактирования нажмите кнопку ВВОД или щелкните за пределами строки формул, чтобы изменения вступили в силу.

Как удалить формулу и сохранить значение

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

1. Выделите все ячейки с формулой.

2. Нажмите «Ctrl + C», чтобы скопировать ячейки.

3. Щелкните правой кнопкой мыши по выделенным ячейкам.

4. Выберите «Вставить значения > значения», чтобы вставить вычисленные значения обратно в выбранные ячейки.

Избегайте использования форматированных чисел

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

Основные формулы Excel — voxt.ru

Список 10 лучших основных формул и функций Excel

Вот список 10 основных формул и функций в Excel.

1. СУММ
2. COUNT
3. COUNTA
4. СЧИТАТЬПУСТОТЫ
5. В СРЕДНЕМ
6. MIN Excel
7. MAX Excel
8. LEN Excel
9. ОБРЕЗАТЬ Excel
10. ЕСЛИ Excel

Теперь давайте подробно обсудим каждый из них —

Вы можете скачать этот шаблон Excel с основными формулами здесь — Шаблон Excel с основными формулами

# 1 СУММА в Excel

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

пример

= СУММ (A1: A5)

Результат = 41 (см. Изображение ниже)

# 2 СЧЁТ Функция Excel

Эта базовая функция Excel используется для подсчета числового значения в одной или нескольких ячейках или диапазоне.

пример

= СЧЁТ (A1: A5)

Результат = 4 (Ячейка A3 будет исключена, поскольку эта формула рассчитывает только числовое значение. См. Изображение ниже)

# 3 COUNTA в Excel

Эта формула используется для подсчета значения в одной или нескольких ячейках (это будет подсчитывать ячейки независимо от числового или текстового значения)

пример

= СЧЁТ (A1: A5)

Результат = 5 (Это будет включать ячейку A3, поскольку эта формула вычисляет как текстовое, так и числовое значение. См. Изображение ниже)

# 4 СЧИТАТЬПУСТОТЫ в Excel

Эта функция Excel Basic используется для подсчета пустого значения в диапазоне. (Примечание: только пробел в ячейке не будет считаться пустой ячейкой).

пример

= СЧИТАТЬПУСТОТЫ (A1: A5)

Результат = 2 (будет вычислено количество пустых ячеек в диапазоне. См. Изображение ниже)

# 5 СРЕДНИЙ в Excel

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

пример

= СРЕДНИЙ (A1: A5)

Результат = 4 (см. Изображение ниже)

№6 Формула MIN в Excel

Эта базовая функция Excel используется для получения минимального значения в ячейках или диапазоне.

пример

= МИН (A1: A5). Результат = 2 (см. Изображение ниже)

# 7 Формула MAX в Excel

Эта базовая функция Excel используется для получения максимального значения в ячейках или диапазоне.

пример

= МАКС (A1: A5)

Результат = 9 (см. Изображение ниже)

# 8 LEN в Excel

Эта базовая функция excel используется для вычисления количества символов в ячейке или тексте.

пример

= LEN (A1)

Значение ячейки A1 — Шивам, длина которого составляет 6 символов. Таким образом, результат будет 6 (см. Изображение ниже).

# 9 ОБРЕЗАТЬ в Excel

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

СИНТАКСИС: ОБРЕЗАТЬ (ТЕКСТ)

пример

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

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

# 10 ЕСЛИ в Excel

Функция ЕСЛИ используется для выполнения логического теста в Excel.

пример

= ЕСЛИ (A1> 33, «P», «F»). Значение в ячейке A1 равно 50, и логический тест: если значение больше 33, то результатом будет P, иначе результат будет F.

Поскольку значение 50 больше 33, результатом будет P. (см. Изображение ниже).

То, что нужно запомнить

• Формула всегда должна начинаться со знака равенства, иначе будет отображаться ошибка.
• Если вы вводите какое-либо текстовое значение вместо адреса ячейки, тогда текстовое значение должно быть указано в виде перевернутой запятой («»).
• Прежде чем вводить функцию в ячейку, убедитесь, что формат ячейки в целом. Если выбран текстовый формат, формула не будет работать.
• Пробел (_) всегда считается одним символом, поэтому, если вы работаете с пустыми ячейками, имейте в виду, что если в ячейке есть только пространство, она не будет считаться пустой.

Видео об основных формулах Excel

УЗНАТЬ БОЛЬШЕ >>

Post Views: 654

Похожие записи

Прокрутить вверх

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

Итак, вы только начинаете работать с Excel и слышали о том, насколько хороши формулы. Есть только одно — вы мало что знаете о том, как использовать формулы 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 .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

6. ВПР

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

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

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

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

• Искомое_значение — — это то, что вы хотите найти.
• 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.

8. TRIM

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

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

  =TRIM(текст)  

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

9. НАЛЕВО 

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

.
  =LEFT(text, [num_chars])  

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

10. ПРАВО

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

.
  =ПРАВО(текст, [число_символов])  

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

11. MID

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

  =MID(text, start_num, num_chars)  

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

12. СЦЕП

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

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

Например,

  СЦЕПЛЕНИЕ (A2, "", B2) 
 

скопирует значение из ячеек A2 и B2, вставив между ними пробел.

Заключение — основные формулы Excel

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

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

Повысьте уровень своих навыков работы с Excel

Станьте сертифицированным мастером Excel с небольшими курсами GoSkills

Начать бесплатную пробную версию

основных формул и функций Excel с примерами

Учебное пособие содержит список основных формул и функций Excel с примерами и ссылками на соответствующие подробные учебные пособия.

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

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

• Основы формул Excel
• 10 основных функций Excel, которые вы обязательно должны знать
• Рекомендации по написанию формул Excel

Основы формул Excel

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

Например, вместо того, чтобы указывать каждое значение для суммирования, как в приведенной выше формуле, вы можете использовать функцию СУММ для сложения диапазона ячеек: =СУММ(A2:A4)

Вы можете найти все доступные Excel функций в библиотеке функций на вкладке Формулы :

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

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

Совет. Вам не обязательно вводить имя функции заглавными буквами, Microsoft Excel автоматически сделает его заглавным, как только вы закончите вводить формулу и нажмете клавишу Enter, чтобы завершить ее.

10 основных функций Excel, которые вы обязательно должны знать

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

СУММ

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

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

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

=СУММ(B2:B6) — суммирует значения в ячейках с B2 по B6.

=СУММ(B2, B6) — суммирует значения в ячейках B2 и B6.

При необходимости можно выполнить другие расчеты в рамках одной формулы, например, сложить значения в ячейках с B2 по B6, а затем разделить сумму на 5:

=СУММ(B2:B6)/5

Для суммирования с условиями используйте функцию СУММЕСЛИ: в 1-м аргументе вы вводите диапазон ячеек для проверки на соответствие критериям (A2:A6), во 2-м аргументе — сам критерий (D2), а в последнем аргументе — ячеек для суммирования (B2:B6):

=СУММЕСЛИ(A2:A6, D2, B2:B6)

В таблицах Excel формулы могут выглядеть примерно так:

Совет. Самый быстрый способ суммировать столбец или строку чисел — это выбрать ячейку рядом с числами, которые вы хотите суммировать (ячейка непосредственно под последним значением в столбце или справа от последнего числа в строке). ) и нажмите кнопку AutoSum на вкладке Главная в группе Форматы . Excel автоматически вставит формулу СУММ.

Полезные ресурсы:

• Excel Примеры формул суммы — формулы для суммирования столбца, строки, только отфильтрованных (видимых) ячеек или суммирования по листам.
• Excel AutoSum — самый быстрый способ суммирования столбца или строки чисел.
• СУММЕСЛИ в Excel — примеры формул для условного суммирования ячеек.
• СУММЕСЛИМН в Excel — примеры формул для суммирования ячеек на основе нескольких критериев.

СРЗНАЧ

Функция Excel СРЗНАЧ делает именно то, что следует из ее названия, то есть находит среднее или среднее арифметическое чисел. Его синтаксис похож на SUM:

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

Присмотревшись к формуле из предыдущего раздела ( =СУММ(B2:B6)/5 ), что она на самом деле делает? Суммирует значения в ячейках с B2 по B6, а затем делит результат на 5. А как вы называете сложение группы чисел, а затем деление суммы на количество этих чисел? Ага, средний!

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

=СРЗНАЧ(B2:B6)

Для усреднения ячеек на основе условия используйте следующую формулу СРЗНАЧЕСЛИ, где A2:A6 — диапазон критериев, D3 — критерии, а B2:B6 — ячейки для усреднения:

=СРЗНАЧЕСЛИ(A2:A6, D3, B2:B6)

Полезные ресурсы:

• Excel СРЗНАЧ — среднее значение ячеек с числами.
• Excel AVERAGEA — найти среднее значение ячеек с любыми данными (числами, логическими и текстовыми значениями).
• Excel СРЗНАЧЕСЛИ — усреднение ячеек по одному критерию.
• Excel AVERAGEIFS — усреднение ячеек на основе нескольких критериев.
• Как рассчитать средневзвешенное значение в Excel
• Как найти скользящее среднее в Excel

МАКС И МИН

Формулы МАКС и МИН в Excel получают наибольшее и наименьшее значение в наборе чисел соответственно. Для нашего примера набора данных формулы будут такими простыми, как:

=MAX(B2:B6)

=MIN(B2:B6)

Полезные ресурсы:

• Функция MAX — найти наибольшее значение.
Формула
• MAX IF — получить наибольшее число с условиями.
• Функция MAXIFS — получение наибольшего значения на основе нескольких критериев.
• Функция MIN — возвращает наименьшее значение в наборе данных.
• Функция MINIFS — найти наименьшее число на основе одного или нескольких условий.

COUNT & COUNTA

Если вам интересно узнать, сколько ячеек в данном диапазоне содержат числовых значений (числа или даты), не тратьте время на пересчет вручную. Функция СЧЁТ в Excel моментально подсчитает:

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

В то время как функция СЧЁТ работает только с теми ячейками, которые содержат числа, функция СЧЕТЧИК подсчитывает все ячейки, которые не являются . пусто , содержат ли они числа, даты, время, текст, логические значения ИСТИНА и ЛОЖЬ, ошибки или пустые текстовые строки («»):

СЧЕТЧИК (значение1, [значение2], …)

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

=СЧЕТ(B:B)

Чтобы подсчитать все непустые ячейки в столбце B, используйте эту формулу:

=COUNTA(B:B)

В обеих формулах используется так называемая «ссылка на весь столбец» (B:B), которая относится ко всем ячейкам в столбце B.

На следующем снимке экрана показана разница. : в то время как COUNT обрабатывает только числа, COUNTA выводит общее количество непустых ячеек в столбце B, включая текстовое значение в заголовке столбца.

Полезные ресурсы:

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

IF

Судя по количеству комментариев на тему IF в нашем блоге, это самая популярная функция в Excel. Проще говоря, вы используете формулу ЕСЛИ, чтобы попросить Excel проверить определенное условие и вернуть одно значение или выполнить одно вычисление, если условие выполнено, и другое значение или вычисление, если условие не выполнено:

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

Например, следующий оператор IF проверяет, выполнен ли заказ (т. е. есть ли значение в столбце C) или нет. Чтобы проверить, не является ли ячейка пустой, вы используете оператор «не равно» (<>) в сочетании с пустой строкой («»). В результате, если ячейка С2 не пуста, формула возвращает «Да», иначе «Нет»:

=ЕСЛИ(С2<>"", "Да", "Нет")

Полезные ресурсы:

• Функция ЕСЛИ в Excel с примерами формул
• Как использовать вложенные ЕСЛИ в Excel
• Формулы ЕСЛИ с несколькими условиями И/ИЛИ

TRIM

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

Существует несколько способов удаления ненужных пробелов в Excel, самый простой из которых — функция ОБРЕЗ:

TRIM(текст)

Например, чтобы удалить лишние пробелы в столбце A, введите следующую формулу в ячейку A1, а затем скопируйте ее вниз по столбцу:

=TRIM(A1)

лишние пробелы в ячейках, но один пробел между словами:

Полезные ресурсы:

• Функция TRIM в Excel с примерами формул
• Как удалить разрывы строк и непечатаемые символы
• Как удалить неразрывные пробелы ( )
• Как удалить определенный непечатаемый символ

ДЛСТР

Всякий раз, когда вы хотите узнать количество символов в определенной ячейке, используйте функцию ДЛСТР:

ДЛСТР(текст)

Хотите узнать, сколько символов в ячейке A2? Просто введите приведенную ниже формулу в другую ячейку:

=ДЛСТР(A2)

Помните, что функция ДЛСТР в Excel считает абсолютно все символы , включая пробелы :

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

Полезные ресурсы:

• Формулы Excel LEN для подсчета символов в ячейке
• Подсчитать количество символов в ячейках и диапазонах

И и ИЛИ

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

• И возвращает ИСТИНА, если все условия выполнены, FALSE в противном случае.
• ИЛИ возвращает ИСТИНА, если выполняется любое условие , иначе ЛОЖЬ.

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

Например, чтобы проверить результаты теста в столбцах B и C и вернуть «Пройдено», если оба значения больше 60, и «Не пройдено» в противном случае, используйте следующую формулу ЕСЛИ со встроенным оператором И:

=ЕСЛИ(И (B2>60, B2>60), «сдал», «не сдал»)

Если достаточно иметь только один результат теста выше 60 (тест 1 или тест 2), вставьте оператор ИЛИ:

=ЕСЛИ(ИЛИ(B2>60, B2>60), "Fail")

Полезные ресурсы:

• Excel И функция с примерами формул
• Функция Excel ИЛИ с примерами формул

СЦЕПИТЬ

Если вы хотите взять значения из двух или более ячеек и объединить их в одну ячейку, используйте оператор конкатенации (&) или функцию СЦЕПИТЬ:

СЦЕПИТЬ(текст1, [текст2], …)

Например, чтобы объединить значения из ячеек A2 и B2, просто введите следующую формулу в другую ячейку:

=СЦЕПИТЬ(A2, B2)

Чтобы разделить объединенные значения пробелом, введите символ пробела (» «) в списке аргументов:

= СЦЕПИТЬ (A2, " ", B2)

Полезные ресурсы:

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

СЕГОДНЯ И СЕЙЧАС

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

=СЕГОДНЯ() , чтобы вставить сегодняшнюю дату в ячейку.

=СЕЙЧАС() для вставки текущей даты и времени в ячейку.

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

Полезные ресурсы:

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

Рекомендации по написанию формул Excel

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

Не заключайте числа в двойные кавычки

Любой текст, включенный в ваши формулы Excel, должен быть заключен в «кавычки». Однако вы никогда не должны делать это с числами, если только вы не хотите, чтобы Excel обрабатывал их как текстовые значения.

Например, чтобы проверить значение в ячейке B2 и вернуть 1 для «Пройдено», в противном случае 0, вы помещаете следующую формулу, скажем, в C2:

=IF(B2="pass", 1, 0)

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

Теперь посмотрите, что произойдет, если вы закроете числа двойными кавычками:

=IF(B2="pass", "1", "0")

На первый взгляд, вывод нормальный — тот же столбец 1 и 0. Однако при ближайшем рассмотрении вы заметите, что результирующие значения по умолчанию выровнены по левому краю в ячейках, что означает, что это числовые строки, а не числа! Если позже кто-то попытается вычислить эти 1 и 0, он может в конечном итоге рвать на себе волосы, пытаясь понять, почему 100% правильная формула суммирования или подсчета не возвращает ничего, кроме нуля.

Не форматируйте числа в формулах Excel

Пожалуйста, помните это простое правило: числа, используемые в формулах Excel, должны быть введены без какого-либо форматирования, такого как десятичный разделитель или знак доллара. В Северной Америке и некоторых других странах запятая является разделителем аргументов по умолчанию, а знак доллара ($) используется для создания абсолютных ссылок на ячейки. Использование этих символов в числах может просто свести с ума ваш Excel 🙂 Итак, вместо того, чтобы вводить 2000 долларов, просто введите 2000, а затем отформатируйте выходное значение по своему вкусу, настроив собственный числовой формат Excel.

Совпадение со всеми открывающими и закрывающими скобками

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

Скопируйте ту же формулу в другие ячейки вместо повторного ввода

После того, как вы ввели формулу в ячейку, нет необходимости вводить ее снова и снова. Просто скопируйте формулу в соседние ячейки, перетащив маркер заполнения (небольшой квадрат в правом нижнем углу ячейки). Чтобы скопировать формулу во весь столбец, поместите указатель мыши на маркер заполнения и дважды щелкните знак «плюс».

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

Подробные пошаговые инструкции см. в разделе Как копировать формулы в Excel.

Как удалить формулу, но сохранить расчетное значение

При удалении формулы нажатием клавиши Delete расчетное значение также удаляется. Однако вы можете удалить только формулу и оставить полученное значение в ячейке. Вот как:

• Выберите все ячейки с вашими формулами.
• Нажмите Ctrl + C, чтобы скопировать выбранные ячейки.
• Щелкните правой кнопкой мыши выделенный фрагмент и выберите 9.0049 Вставить значения > Значения , чтобы вставить вычисленные значения обратно в выбранные ячейки. Или нажмите сочетание клавиш «Специальная вставка»: Shift+F10, а затем V.

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

Убедитесь, что для параметров расчета установлено значение «Автоматически».