15 формул в Excel

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

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

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

Немного терминологии

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

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

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

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

Теперь давайте проверим все это на практике.

Формула 1: ВПР

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

Синтаксис

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

Синтаксис следующий:

=VPR(lookup_value;table;column_number;[interval_view])

Давайте рассмотрим все аргументы подробнее:

1. Значение для поиска.
2. Таблица. Должно быть значение для поиска, которое находится в первом столбце, и значение для возврата. Последние могут быть расположены в любом месте. Пользователь может решить, куда поместить результат формулы. 
3. Номер колонки.
4. Интервальный вид. Значение этого аргумента можно опустить, если в нем нет необходимости. Это логическое выражение, указывающее на степень точности совпадения, которое должно быть обнаружено функцией. Если указан параметр 'True', Excel ищет значение, наиболее близкое к указанному в качестве цели поиска. Если указан параметр 'False', функция будет искать только те значения, которые находятся в первом столбце.

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

Формула 2: Если

Эта функция необходима, если пользователь хочет задать определенное условие, при котором должно быть вычислено или выведено конкретное значение. Он может принимать два варианта: true и false.

Синтаксис

Эта функция имеет три основных аргумента в своей формуле и выглядит следующим образом

=If(logical_expression; 'value_if_true'; 'value_if_false').

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

Существует еще один способ использования функции IF - вложенные функции. Условий может быть гораздо больше, вплоть до 64. Пример рассуждения, соответствующего формуле на скриншоте, выглядит следующим образом. Если ячейка A2 равна двум, то нужно вывести значение "Да". Если она имеет другое значение, то нужно проверить, равна ли ячейка D2 двум. Если это так, выведите "Нет", а если условие все еще ложно, формула должна вернуть "Может быть".

 

 

 

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

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

Здесь синтаксис следующий:

=If(EPUST(номер ячейки); "Пусто"; "Не пусто").

 

 

 

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

 

 

 

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

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

Формула 3: СУММЕСЛИ

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

Синтаксис

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

=SUMMEDI(range;condition;[range_amount])

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

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

Поэтому в данной ситуации Excel суммировал данные для тех запросов, в которых количество переходов превышало 100 000.

 

 

 

Формула 4: СУММЕСЛИМН

Если существует более одного условия, используется связанная функция SUMMESLYMN.

Синтаксис

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

=UMMESLIMN(summation range; range_condition1; condition1; [range_condition2; condition2]; ...).

Второй и третий аргументы являются обязательными, а именно 'condition range1' и 'condition1'.

Формула 5: СЧЕТЕСЛИ и СЧЕТЕСЛИМН

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

Синтаксис

Чтобы ввести эту функцию, введите следующую формулу:

=CountingEASE(range;criterion)

Что означают приведенные аргументы?

1. Диапазон - это набор ячеек, среди которых будет производиться подсчет.
2. Критерий - это условие, которое необходимо учитывать при выборе ячеек.

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

 

 

 

Что касается связанной функции SCHETESLYMN, то она, как и предыдущий пример, позволяет использовать несколько критериев одновременно. Его формула выглядит следующим образом:

=CHOTESLIMN(range_condition1;condition1;[range_condition2;condition2];...).

И, как и в предыдущем случае, 'condition range1' и 'condition1' являются обязательными аргументами, остальные можно опустить, если в них нет необходимости. Функция позволяет сохранить максимум 127 диапазонов с условиями.

Формула 6: ЕСЛИОШИБКА

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

Синтаксис

Эта функция имеет два аргумента. Синтаксис следующий:

=ESLIO ERROR(value;value_if_error).

Описание аргумента:

1. Value - это сама формула, проверенная на наличие ошибок.
2. Значение, если ошибка - это результат, который появляется при обнаружении ошибки.

Что касается примеров, то эта формула покажет текст "Ошибка в вычислении", если деление не может быть выполнено.

 

 

 

Формула 7: ЛЕВСИМВ

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

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

=LEVSIMB(text;[number_characters])

Возможные аргументы:

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

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

 

 

 

Формула 8: ПСТР

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

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

=PSTR(text;start_position;number_of_characters).

Объяснение аргументов:

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

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

 

 

 

Формула 9: ПРОПИСН

Эта функция делает все буквы, встречающиеся в строке, заглавными. Синтаксис следующий:

=PROPEACE(text)

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

Формула 10: СТРОЧН

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

Синтаксис аналогичен, только один аргумент содержит текст или адрес ячейки.

Формула 11: ПОИСКПОЗ

Эта функция находит нужный элемент в диапазоне ячеек и выводит его положение.

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

=DISCRIPTION(искомое_значение;просмотренный_массив;тип_соответствия)

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

Существует три способа подбора:

1. Меньше или равно - 1.
2. Точность - 0.
3. Наименьшее значение, равное или большее -1.

В данном примере мы пытаемся определить, какое ключевое слово имеет до 900 переходов включительно.

 

 

 

Формула 12: ДЛСТР

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

Его синтаксис аналогичен предыдущему:

=DLSTR(text)

Поэтому его можно использовать для определения длины описания статьи при SEO-продвижении страницы.

 

 

 

Его также можно комбинировать с функцией IF.

Формула 13: СЦЕПИТЬ

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

Синтаксис следующий:

=CREATE(text1;text2;text3);

Формула 14: ПРОПНАЧ

Эта функция меняет местами прописные и строчные буквы.

Синтаксис очень прост:

=PROPNACH(text).

Формула 15: ПЕЧСИМВ

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

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

=PERSIMB(text)

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

Выводы

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