ТОП-15 формул в Excel

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

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

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

Стало интересно? Тогда добро пожаловать на обзор 15 самых полезных формул Excel.

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

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

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

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

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

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

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

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

Синтаксис

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

Синтаксис:

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

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

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

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

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

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

Синтаксис

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

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

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

Есть еще один способ использования функции IF – вложенные функции. Условий здесь может быть гораздо больше, вплоть до 64. Пример рассуждения, соответствующего приведенной на скриншоте формуле, следующий. Если ячейка А2 равна двум, то нужно вывести значение «Да». Если оно имеет другое значение, то нужно проверить, равна ли ячейка D2 двум. Если да, то нужно вернуть значение «нет», если и здесь условие окажется ложным, то формула должна вернуть значение «может быть».

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

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

Здесь синтаксис:

=ЕСЛИ(ПУСТО(номер ячейки)», «Пусто», «Не пусто»).

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

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

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

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

Функция СУММЕСЛИ позволяет обобщить данные при условии их соответствия определенным критериям.

Синтаксис

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

=СУММЕСЛИ(диапазон, условие, [диапазон_суммы])

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

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

Итак, в данной ситуации Excel суммировал данные по тем запросам, где количество переходов превышает 100000.

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

Если условий несколько, то используется связанная функция СУММЕСЛИМН.

Синтаксис

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

=СУММИФ(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2], …)

Обязательны второй и третий аргументы, а именно «Диапазон условия 1» и «Диапазон условия 1».

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

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

Синтаксис

Для входа в эту функцию необходимо указать следующую формулу:

= СЧЁТЕСЛИ (диапазон; критерии)

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

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

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

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

=COUNTIFS(диапазон_условия1, условие1, [диапазон_условия2, условие2],…)

И как и в предыдущем случае, «Диапазон условий 1» и «Условие 1» являются обязательными аргументами, а остальные можно опустить, если в такой необходимости нет. Функция «Максимум» обеспечивает возможность применения до 127 диапазонов вместе с условиями.

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

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

Синтаксис

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

=ЕСЛИОШИБКА(значение;значение_если_ошибка)

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

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

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

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

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

Его синтаксис следующий:

=ЛЕВО(текст,[число_символов])

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

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

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

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

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

Его синтаксис следующий:

=MID(текст,начальная_позиция,количество_символов).

Расширение аргумента:

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

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

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

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

=ОБЯЗАТЕЛЬНО(текст)

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

Формула 10: НИЖНЯЯ

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

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

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

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

Шаблон для этой формулы:

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

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

Есть три способа соответствия:

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

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

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

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

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

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

Так, его можно использовать для определения длины описания статьи при SEO-продвижении сайта.

Также хорошо объединить его с функцией IF.

Формула 13: ПОДКЛЮЧИТЬ

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

Синтаксис:

= СЦЕПИТЬ (текст1, текст2, текст3);

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

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

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

=ПРОППЛАН(текст)

Формула 15: ПЕЧАТЬ

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

Его синтаксис следующий:

=ПЕЧАТЬ(текст)

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

Выводы

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

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