Топ-10 самых важных функций excel
1. Функция ЕСЛИ
Функция ЕСЛИ возвращает значение, если указанное вами условие имеет значение ИСТИНА, в противном случае — какое-то другое значение. Проще говоря, функция ЕСЛИ может сначала проверить условие и вернуть значение на основе результата этого условия.
Синтаксис
ЕСЛИ(логический_тест, значение_если_истина, значение_если_ложь)
Аргументы
- логический_тест: условие, которое вы хотите оценить.
- value_if_true: значение, которое вы хотите получить, если это условие истинно.
- value_if_false: значение, которое вы хотите получить, если это условие ЛОЖЬ.
Комментарии
- Максимальное количество вложенных условий, которые вы можете выполнить, — 64.
- Для оценки условия можно использовать операторы сравнения.
Пример
В приведенном ниже примере мы использовали оператор сравнения для оценки различных условий.
- Мы использовали определенный текст, чтобы получить результат, выполнено ли условие или нет.
- Вы также можете использовать TRUE и FALSE для получения результата.
- Если вы пропустите указание значения для получения результата, если условие истинно, оно вернет ноль.
- И если вы пропустите указание значения для получения результата, если условие ЛОЖЬ, оно вернет ноль.
В приведенном ниже примере мы использовали функцию ЕСЛИ для создания формулы вложения.
Мы указали условие, и если это условие ложно, мы использовали другое ЕСЛИ для оценки другого условия и выполнения задачи, а если это условие ЛОЖНОЕ, мы использовали другое ЕСЛИ.
Таким образом, мы пять раз использовали ЕСЛИ для создания формулы вложения. Вы можете использовать одно и то же 64 раза для формулы вложения.
2. Функция ЕСЛИОШИБКА
Функция ЕСЛИОШИБКА возвращает определенное значение в случае возникновения ошибки. Проще говоря, он может проверить значение и, если это значение является ошибкой, он вернет указанное вами значение.
Синтаксис
ЕСЛИОШИБКА(значение, значение_если_ошибка)
Аргументы
- значение: значение, которое вы хотите проверить на наличие ошибок.
- value_if_error: значение, которое вы хотите получить обратно в случае возникновения ошибки.
Комментарии
- Функция ЕСЛИОШИБКА касается возникновения ошибки, а не ее типа.
- Если вы проигнорируете значение или value_if_error, в результате будет возвращено 0.
- Он может проверять #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? и #NULL!.
- Если вы оцениваете массив, он вернет массив результатов для каждого указанного элемента.
Пример
В приведенном ниже примере мы использовали функцию ЕСЛИОШИБКА для замены #ДЕЛ/0! со смысловым текстом.
ЕСЛИОШИБКА совместима только с версиями 2007 и более ранними. Чтобы решить эту проблему, вы можете использовать ISERROR.
3. Функция TRUNC
Функция TRUNC возвращает целое число после усечения исходного числа. Проще говоря, он удаляет десятичные знаки из числа с определенной точностью, а затем возвращает целую часть результата.
Синтаксис
TRUNC(число, [число_цифр])
Аргументы
- число: число, которое вы хотите усечь.
- [num_digits]: число, указывающее точность усечения числа.
Комментарии
- Если вы проигнорируете указание нескольких, будет возвращена ошибка.
- Оно округляется от нуля.
- Если у вас есть два кратных на одном и том же расстоянии, он вернет большее кратное числа, которое вы округляете.
Пример
В приведенном ниже примере мы использовали TRUNC для усечения данных и удаления времени из дат.
4. Функция СУММЕСЛИ
Функция СУММЕСЛИ возвращает сумму чисел, удовлетворяющих указанному вами условию . Проще говоря, он считает и вычисляет только сумму значений, удовлетворяющих условию.
Синтаксис
СУММЕСЛИ(диапазон, критерии, [диапазон_суммы])
Аргументы
- диапазон: диапазон ячеек, критерии которого вы хотите проверить.
- критерий: критерий, который может быть числом, текстом, выражением, ссылкой на ячейку или функцией.
- [диапазон_суммы]: диапазон ячеек, содержащий значения, которые вы хотите суммировать.
Комментарии
- Если параметр sum_range опущен, ячейки в диапазоне будут суммироваться.
- Обязательно используйте двойные кавычки для указания текстовых критериев или математических символов, которые следует заключать в двойные кавычки.
- Размер диапазона критериев и диапазона суммы должен быть одинакового размера.
Пример
В приведенном ниже примере мы указали A1:A9 в качестве диапазона критериев и B1:B9 в качестве диапазона суммы, а после этого мы указали критерии в A12, который имеет значение C.
Вы также можете вставить критерии непосредственно в функцию. В приведенном ниже примере мы использовали подстановочный знак звездочки, чтобы указать критерий, имеющий букву «S».
И если вы проигнорируете диапазон сумм, вы получите сумму диапазона критериев. Но это будет возможно только в том случае, если диапазон критериев имеет числовые значения.
5. Функция ИНДЕКС
Функция ИНДЕКС возвращает значение из списка значений на основе его порядкового номера. Проще говоря, ИНДЕКС возвращает значение из списка значений, и вам необходимо указать позицию этого значения.
Синтаксис
ИНДЕКС имеет два разных синтаксиса. В первом вы можете использовать форму индекса в виде массива, чтобы просто получить значение из списка, используя его позицию.
ИНДЕКС(массив, номер_строки, [номер_столбца])
Во- вторых , вы можете использовать форму спонсорства, которая реже используется в реальной жизни, но вы можете использовать ее, если у вас есть более одного диапазона для продвижения.
ИНДЕКС(ссылка, номер_строки, [номер_столбца], [номер_области])
Аргументы
- массив: диапазон ячеек или константа массива.
- ссылка: диапазон ячеек или несколько диапазонов.
- row_number: номер строки, из которой вы хотите получить значение.
- [col_number]: номер столбца, из которого вы хотите получить значение.
- [номер_области]: если вы ссылаетесь на несколько диапазонов ячеек (используя ссылочный синтаксис), укажите число, которое будет относиться к одному диапазону среди всех них.
Комментарии
- Если указаны аргументы row_num и columns_num, он вернет значение в ячейке на пересечении этих двух.
- Если вы укажете row_num или columns_num как 0 (ноль), он вернет массив значений для всего столбца или строки соответственно.
- Если номер_строки и номер_столбца выходят за пределы допустимого диапазона, возвращается #ССЫЛКА! ошибка.
- Если area_number больше указанного вами диапазона чисел, будет возвращен #REF!.
Пример 1. Использование ARRAY для получения значения из списка.
В приведенном ниже примере мы использовали функцию ИНДЕКС, чтобы получить количество за июнь. В списке Джун находится на 6-й позиции (6-я строка), поэтому в row_number я указал 6. ИНДЕКС вернул в результате значение 1904.
А если вы имеете в виду диапазон с более чем одним столбцом, вы должны указать номер столбца.
Пример 2. Использование REFERENCE для получения значения нескольких списков.
В приведенном ниже примере вместо выделения всего диапазона сразу я выделил его в трех разных диапазонах. В последнем аргументе мы указали 2 в поле area_number, которое будет определять используемый диапазон из этих трех разных диапазонов.
Теперь во второй строке мы обращаемся к 5-й строке и 1-му столбцу. ИНДЕКС вернул значение 172, которое находится в 5-й строке 2-й строки.
6. Функция ВПР.
Функция ВПР ищет значение в первом столбце таблицы и возвращает значение в той же строке соответствующего значения, используя порядковый номер. Проще говоря, он выполняет вертикальный поиск.
Синтаксис
ВПР(искомое_значение,табличный_массив,кол_индекс_номер,диапазон_просмотра)
Аргументы
- искомое_значение: значение, которое вы хотите найти в столбце. Вы можете сослаться на ячейку, содержащую искомое значение, или ввести это значение непосредственно в функцию.
- table_array: диапазон ячеек, именованный диапазон, из которого вы хотите найти значение.
- col_index_num: число представляет номер столбца, из которого вы хотите получить значение.
- range_lookup: используйте false или 0 для точного совпадения и true или 1 для правильного совпадения. По умолчанию установлено значение «Истина».
Комментарии
- Если VLOOKUP не находит искомое значение, он вернет #N/A.
- ВПР может дать вам только то значение, которое находится справа от искомого значения. Если вы хотите посмотреть с правой стороны, вы можете использовать для этого ИНДЕКС и ПОИСКПОЗ.
- Если вы используете точное соответствие, оно будет соответствовать только тому значению, которое находится первым в столбце.
- Вы также можете использовать подстановочные знаки с помощью VLOOKUP .
- Вы можете использовать TRUE или 1, если хотите правильного совпадения, и FALSE или 0 для точного совпадения.
- Если вы используете правильное совпадение (True): будет возвращено следующее наименьшее значение в списке, если точного совпадения нет.
- Если искомое значение меньше наименьшего значения в списке, ВПР вернет #Н/Д.
- Если есть точное значение, которое вы ищете, оно даст вам это точное значение.
- Убедитесь, что вы отсортировали список в порядке возрастания.
Пример
1. Использование ВПР для категорий
В приведенном ниже примере у нас есть список учащихся с полученными ими оценками, и в столбце примечаний нам нужна оценка, основанная на их оценках.
В приведенный выше список брендов мы хотели бы добавить примечания в соответствии с диапазоном категорий, указанным ниже.
В этом случае у нас есть два варианта использования.
ПЕРВЫЙ — создать формулу вложения с помощью ЕСЛИ, что занимает немного времени, а ВТОРОЙ вариант — создать формулу с помощью ВПР с подходящим совпадением. И формула будет такая:
=ВПР(B2,$E$2:$G$5,3,ИСТИНА)
Как это работает
Я использую столбец «MIN MARKS», чтобы сопоставить искомое значение и получить обратно значение из столбца «Примечания».
Я уже упоминал, что когда вы используете TRUE и нет точного значения поиска, оно вернет следующее наименьшее значение из значения поиска. Например, когда мы ищем значение 77 в таблице категорий, 65 — это наименьшее значение после 77.
Именно поэтому в комментариях мы поставили оценку «Хорошо».
2. Обработка ошибок в функции ВПР.
Одна из наиболее распространенных проблем, возникающих при использовании ВПР, заключается в том, что вы получаете сообщение #Н/Д всякий раз, когда совпадение не найдено. Но решение этой проблемы простое и легкое. Позвольте мне показать вам на простом примере.
В приведенном ниже примере у нас есть список имен и их возраст, а в ячейке E6 мы используем функцию ВПР для поиска имени в списке. Каждый раз, когда я ввожу имя, которого нет в списке, я получаю #N/A.
Но здесь я хочу отобразить значимое сообщение вместо ошибки. Формула будет следующей: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0)», «Не найдено»).
Как это работает : IFNA может проверить значение на #N/A, и если есть ошибка, вы можете указать значение вместо ошибки.
7. Функция ИФНА
Функция IFNA возвращает определенное значение, если возникает ошибка #Н/Д. В отличие от ЕСЛИОШИБКА, он оценивает только ошибку #Н/Д и возвращает указанное вами значение.
Синтаксис
IFNA(значение, значение_если_на)
Аргументы
- значение: значение, которое вы хотите проверить на наличие ошибки #Н/Д.
- value_if_na: значение, которое вы хотите вернуть в случае возникновения ошибки.
Комментарии
- Если вы не укажете никаких аргументов, IFNA будет рассматривать их как пустую строку («»).
- Если значение является массивом, оно вернет результат в виде массива.
- Все остальные ошибки будут игнорироваться #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? и #NULL!.
Пример
В функции ВПР #Н/Д возникает, когда искомое значение не находится в диапазоне поиска, и для этого мы указали значимое сообщение с помощью IFNA.
Примечание. IFNA появилась в Excel 2013, поэтому она недоступна в предыдущих версиях.
8. Функция СЛЧИС
Функция СЛЧИС возвращает случайное число от 0 до 1. Проще говоря, вы можете сгенерировать случайное число от 0 до 1 (оно обновляет свое значение каждый раз, когда вы вносите изменения в рабочий лист).
Синтаксис
СЛЧИС()
Аргументы
- В функциях RAND нет аргументов для указания.
Комментарии
- Если вы поместите ноль в кратное число, оно вернет ноль в результате.
- Если вы проигнорируете указание нескольких, будет возвращена ошибка.
- Оно округляется от нуля.
- Если у вас есть два кратных на одном и том же расстоянии, он вернет большее кратное числа, которое вы округляете.
Пример
Помимо чисел от 0 до 1, вы также можете использовать RAND для случайных чисел между двумя конкретными числами. В приведенном ниже примере я использовал его для создания формулы, генерирующей случайное число от 50 до 100.
Когда вы вводите эту формулу в ячейку, она возвращает число от 100 до 50 путем умножения значений, возвращаемых функцией RAND, на использованное нами уравнение. Чтобы понять эту формулу, нам нужно разделить ее на три части:
- Во-первых, когда он обнаруживает меньшее число из большего числа, вы получаете разницу между ними.
- Затем он умножает эту разницу на случайное число, полученное после вычитания.
- И в-третьих, сложите это число с наименьшим оставшимся числом в третьей части уравнения.
9. Функция СУММ
Функция СУММ возвращает сумму предоставленных значений . Проще говоря, с помощью функции СУММ вы можете вычислить сумму списка значений (вы можете напрямую ввести значение в функцию или обратиться к диапазону ячеек.
Синтаксис
СУММ(число1,[число2],…)
Аргументы
- число1 : число, диапазон ячеек, содержащих числа, или отдельная ячейка, содержащая число.
- [номер2] : число, диапазон ячеек, содержащих числа, или одна ячейка, содержащая число.
Комментарии
- Он игнорирует текстовые значения.
Пример
В приведенном ниже примере вы можете вставлять числа непосредственно в функцию, разделяя их запятыми.
Вы также можете просто обратиться к диапазону, чтобы вычислить сумму чисел, и если есть какой-либо текст, логическое значение или пустая ячейка, они будут игнорироваться.
Если в ячейке, на которую вы ссылаетесь, есть значение ошибки, в результате будет возвращено значение #N/A.
Если у вас есть числовые значения, отформатированные как текст, он их проигнорирует. Перед использованием СУММ рекомендуется преобразовать их в числа.
10. Функция ИЛИ
Функция ИЛИ возвращает логическое значение (ИСТИНА или ЛОЖЬ) после проверки указанных вами условий. Проще говоря, вы можете проверить несколько условий с помощью функции AND, и она возвращает TRUE, если какое-либо из этих условий (или все) имеет значение TRUE, и возвращает FALSE, только если все эти условия являются FALSE.
Синтаксис
ИЛИ(логика1, [логика2],…)
Аргументы
- логическое1: условие, которое вы хотите проверить.
- [логическое2]: дополнительные условия, которые вы хотите проверить.
Комментарии
- Значения будут игнорироваться, если ссылочная ячейка или таблица содержит пустую ячейку или текст.
- Результатом условий должно быть логическое значение (ИСТИНА или ЛОЖЬ).
- Он вернет ошибку, если не будет возвращено логическое значение.
Пример
В приведенном ниже примере мы создали условие с помощью функции ЕСЛИ, согласно которому, если учащийся набирает 60 баллов выше по любому из двух предметов, формула возвращает ИСТИНА.
Теперь в примере ниже мы использовали число для получения логических значений в формуле. Вы также можете выполнить вышеуказанное условие в обратном порядке.
Вместо чисел можно использовать TRUE и FALSE. Функция ИЛИ рассматривает эти логические значения как числа.
Больше уроков
Статистические функции / Функции даты / Строко-текстовые функции / Финансовые функции