Функция excel: Справочник по функциям в Microsoft Excel

Содержание

Функция ВПР (VLOOKUP) в Excel: пошаговая инструкция с примерами

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

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

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

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

Как создать функцию ВПР в Excel

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

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

Выделить ячейку и вписать функцию.

Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

Синтаксис функции ВПР выглядит так:


=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:


=ВПР(A2;$G$2:$H$11;2;0)

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.


=ВПР(A2;

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:h21». Знаки «$» можно поставить вручную, а можно выделить «G2:h21» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.


=ВПР(A2;$G$2:$H$11

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

Формула сканирует таблицу по вертикали.

Находит в самом левом столбце совпадение с искомым значением.

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

Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.


=ВПР(A2;$G$2:$H$11;2

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.


=ВПР(A2;$G$2:$H$11;2;0)

Автозаполнение

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

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

В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.

ВПР и приблизительный интервальный просмотр

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

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

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

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

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

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.

Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

Функцию можно вписать вручную или в специальном окне (Shift + F3).

Искомое значение – относительная ссылка, а таблица – абсолютная.

Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.

Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

Порядок работы с функцией подходит для Гугл-таблиц.

Функции и ошибки в MS Excel — урок. Информатика, 9 класс.

Функция Excel — это заранее определённая формула, которая работает с одним или несколькими значениями и возвращает результат.

Фунции бывают:

Функции баз данных (Database)
Функции даты и времени (Date & Time)
Инженерные функции (Engineering)
Финансовые функции (Financial)
Проверка свойств и значений и Информационные функции (Information)
Логические функции (Logical)
Ссылки и массивы (References and arrays)
Математические и тригонометрические функции (Math & Trig)
Статистические функции (Statistical)
Текстовые функции (Text)

Приведём примеры часто используемых функций:

Функция	Описание
	Выдает значение ИСТИНА, если все аргументы имеют значение ИСТИНА.
НЕ() – NOT()	Меняет на противоположное логическое значение своего аргумента.
ИЛИ() – OR()	Выдаёт значение ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.
ИСТИНА() – TRUE()	Вставляет логическое значение ИСТИНА.
ABS() – ABS()	Находит модуль (абсолютную величину) числа.
ACOS() – ACOS()	Вычисляет арккосинус числа.
ACOSH() – ACOSH()	Вычисляет гиперболический арккосинус числа.
ASIN() – ASIN()	Вычисляет арксинус числа.
COS() – COS()	Вычисляет косинус числа.
COSH() – COSH()	Вычисляет гиперболический косинус числа.
ЧЁТН() – EVEN()	Округляет число до ближайшего чётного целого.
EXP() – EXP()	Вычисляет число $e$, возведённое в указанную степень.
ФАКТР() – FACT()	Вычисляет факториал числа.
СРЗНАЧ() – AVERAGE()	Вычисляет среднее арифметическое аргументов.
СЧЁТ() – COUNT()	Подсчитывает количество чисел в списке аргументов.
МАКС() – MAX()	Определяет максимальное значение из списка аргументов.
МИН() – MIN()	Определяет минимальное значение из списка аргументов.
СУММ() – SUM()	Суммирует аргументы.

Ошибки в формулах

Обрати внимание!

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

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

Ошибка $####$ появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.

Ошибка $#ДЕЛ/0!$ появляется, когда в формуле делается попытка деления на ноль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.

Ошибка $#Н/Д!$ является сокращением термина «неопределённые данные». Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.

Ошибка $#ИМЯ?$ появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.

Ошибка $#ПУСТО!$ появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.

Ошибка $#ЧИСЛО!$ появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.

Ошибка $#ССЫЛКА!$ появляется, когда в формуле используется недопустимая ссылка на ячейку. Например, если ячейки были удалены или в эти ячейки было помещено содержимое других ячеек.

Ошибка $#ЗНАЧ!$ появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введён текст.

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

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

Функция ЕСЛИ в Excel с примерами

Сегодня мы рассмотрим функцию ЕСЛИ.

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

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

Синтаксис функции ЕСЛИ очень простой:

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

лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.

В этой части необходимо проверить на соответствие выражения.

Например:

=ЕСЛИ(А1=10; [значение_если_истина]; [значение_если_ложь]) — если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ

Другой пример

=ЕСЛИ(А1>30; [значение_если_истина]; [значение_если_ложь]) — если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ

Еще пример

=ЕСЛИ(С1=”Да”; [значение_если_истина]; [значение_если_ложь]) — если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ

Надеюсь с этим понятно, поехали дальше. Рассмотрим следующие компоненты функции ЕСЛИ

=ЕСЛИ(лог_выражение; [значение_если_истина]; [значение_если_ложь])

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

Пример использования функции ЕСЛИ в Excel

Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы функции ВПР. Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)

Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ

=ЕСЛИ(A3="Ведро";D3;"-")

и нажмем Enter

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

Итак, первый аргумент (лог выражения) A3=»Ведро» проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь), в нашем случае это «-» ( т.е будет написано тире).

Таким образом, в ячейки E3 появится значение D3, т.е число 240.

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

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

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

Спасибо за внимание.

Что такое функция пользователя(UDF)?

Хитрости »
1 Май 2011 Дмитрий 124981 просмотров

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

Функция пользователя(UDF) — или в дословном переводе Функция, Определенная Пользователем, т.к. в оригинале она звучит как: User Defined Function. Так же их называют пользовательские функции.
Такие функции вызываются через Мастер функций -категория Определенные пользователем (User Defined):

Так что же это за функции такие? Функция пользователя это функция, написанная при помощи языка Visual Basic for Application (VBA) и вызываемая как любая другая функция с листа. Но т.к. эти функции пишутся самостоятельно — можно создать любую функцию, которая будет делать то, что ни одна стандартная функция делать не умеет. Естественно, теперь возникает вопрос как написать такую функцию. Для написания UDF понадобятся хотя бы базовые знания языка VBA. Я в статье опишу лишь принципы создания таких функций и после прочтения вы сможете создать простейшую функцию. Но это никак не означает, что я научу создавать функции на все случаи жизни, ибо это сводится к обучению самому языку программирования. В статье же рассмотрим основные принципы создания, некоторые нюансы и как уже написанные функции использовать в своей книге.

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

UDF не может изменять значения других ячеек (с небольшими недокументированными исключениями)
UDF не может изменять форматы ячеек либо присваивать форматы (с небольшими недокументированными исключениями)
UDF не может изменять так называемые объекты окружения самого Excel. Например, сменить стиль ссылок или параметры вычислений формул, вид курсора и т.п.
UDF будет некорректно работать с такими методами как FindNext, SpecialCells, CurrentRegion, CurrentArray, Select, ShowPrecedents и ShowDependents(выделение зависимостей ячеек), Application.GoTo и т.п. Хотя методы вроде Range.End(xlUp), Range.End(xlDown), обычный Find(без FindNext) проблем не вызывают.Подробнее про работу этих методов из UDF можно узнать из статьи: Глюк работы в UDF методов SpecialCells и FindNext
UDFможет возвращать результат только в ту ячейку, в которой записана сама функция
для работы функции пользователя(UDF) обязательно должны быть разрешены макросы

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

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

в отличие от процедуры(Sub) функция всегда начинается именно со слова Function, а не Sub;
в теле функции всегда должно быть присвоение ей значения, иначе функция не вернет необходимый результат;
функция должна располагаться в стандартном модуле или в модуле книги, если Вы планируете вызывать её непосредственно с листа Excel
функции пользователя «привязаны» к той книге, в которой созданы и по умолчанию не будут работать в других (для этого надо будет всегда указывать имя книги с функцией). Чтобы созданные функции работали удобно и без проблем в любой книге необходимо книгу с функциями сохранить как надстройку: Как создать свою надстройку?

Самая простая функция пользователя может выглядеть так:

Function ТекущаяДата()
    'присваиваем функции значение, чтобы она вернула его на лист(обязательно!)
    ТекущаяДата = Date 'ТекущаяДата - имя функции и именно ему необходимо передать результат
End Function

Function ТекущаяДата()
‘присваиваем функции значение, чтобы она вернула его на лист(обязательно!)
ТекущаяДата = Date ‘ТекущаяДата — имя функции и именно ему необходимо передать результат
End Function

Эта функция делает одно — возвращает в ячейку, в которую записана, текущую дату. В ячейке эта функция будет выглядеть так:
=ТекущаяДата()
К записи пользовательских функций в ячейку предъявляются такие же требования, как и к встроенным функциям. Это касается так же и скобок на конце функции, у которой нет аргументов. И так же это означает, что в функцию могут быть переданы наши собственные аргументы

Аргументы функции пользователя

Function MySum(vArg1 As Double, vArg2 As Double)
    Dim dblSum as Double
    'получаем сумму двух аргументов
    dblSum = vArg1 + vArg2
    'присваиваем функции значение, чтобы она вернула его на лист(обязательно!)
    MySum = dblSum 'MySum имя функции и именно ему необходимо передать результат
End Function

Function MySum(vArg1 As Double, vArg2 As Double)
Dim dblSum as Double
‘получаем сумму двух аргументов
dblSum = vArg1 + vArg2
‘присваиваем функции значение, чтобы она вернула его на лист(обязательно!)
MySum = dblSum ‘MySum имя функции и именно ему необходимо передать результат
End Function

В приведенном выше коде я упростил стандартную функцию СУММ(SUM) до двух аргументов. Записанная на лист функция будет иметь такой вид:
=Mysum(A1;A2)
где:
A1 — первый аргумент(vArg1), ссылка на ячейку или число
A2 — второй аргумент(vArg2), ссылка на ячейку или число
Функция вернет #ЗНАЧ!(#VALUE!), если в качестве одного из аргументов передано не числовое значение.

Необязательные аргументы функции пользователя
Однако иногда бывает неизвестно, сколько аргументов будет передано в функцию: 1, 2 или 10. Для этого можно использовать ключевой параметр Optional перед аргументом, который укажет функции, что этот аргумент является не обязательным, т.е. указывать его в функции при вызове этой функции не обязательно. На примере приведенной выше функции мы можем сделать обязательным только один параметр, а еще 4 необязательными:

Function SumFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum + arg2
    dblSum = dblSum + arg3
    dblSum = dblSum + arg4
    dblSum = dblSum + arg5
    SumFiveArgs = dblSum
End Function

Function SumFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
Dim dblSum As Double
dblSum = arg1
dblSum = dblSum + arg2
dblSum = dblSum + arg3
dblSum = dblSum + arg4
dblSum = dblSum + arg5
SumFiveArgs = dblSum
End Function

Функция будет работать отлично, даже если передать одно или два числа. Но это только в том случае, если для аргументов у нас заданы строгие типы данных — в примере это Double. Если тип не задан — получим ошибку #ЗНАЧ! (#VALUE!):

Function SumFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum + arg2
    dblSum = dblSum + arg3
    dblSum = dblSum + arg4
    dblSum = dblSum + arg5
    SumFiveArgs = dblSum
End Function

Function SumFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5)
Dim dblSum As Double
dblSum = arg1
dblSum = dblSum + arg2
dblSum = dblSum + arg3
dblSum = dblSum + arg4
dblSum = dblSum + arg5
SumFiveArgs = dblSum
End Function

Можно, конечно, всегда задавать тип данных, как в первом примере. Но стоит учитывать, что для числовых типов данных(Double, Integer, Long) значение по умолчанию будет всегда 0, даже если мы аргумент не передали в функцию(для типа String значение по умолчанию нулевая строка — «»). Это нам не мешает произвести операцию сложения и вычитания. Но операция внутри функции может быть умножением или делением и в этом случае мы получим ошибку или неверный результат:

'функция деления аргументов между собой
Function DivideFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum / arg2
    dblSum = dblSum / arg3 'уже здесь получим ошибку "на ноль делить нельзя"
    dblSum = dblSum / arg4
    dblSum = dblSum / arg5
    DivideFiveArgs = dblSum
End Function

‘функция деления аргументов между собой
Function DivideFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
Dim dblSum As Double
dblSum = arg1
dblSum = dblSum / arg2
dblSum = dblSum / arg3 ‘уже здесь получим ошибку «на ноль делить нельзя»
dblSum = dblSum / arg4
dblSum = dblSum / arg5
DivideFiveArgs = dblSum
End Function

'функция перемножения аргументов между собой
Function MultipleFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
    Dim dblSum As Double
    dblSum = arg1
    dblSum = dblSum * arg2
    dblSum = dblSum * arg3 'здесь arg3 равен нулю, значит далее сумма будет тоже равна нулю
    dblSum = dblSum * arg4
    dblSum = dblSum * arg5
    MultipleFiveArgs = dblSum
End Function

‘функция перемножения аргументов между собой
Function MultipleFiveArgs(arg1 As Double, Optional arg2 As Double, Optional arg3 As Double, Optional arg4 As Double, Optional arg5 As Double)
Dim dblSum As Double
dblSum = arg1
dblSum = dblSum * arg2
dblSum = dblSum * arg3 ‘здесь arg3 равен нулю, значит далее сумма будет тоже равна нулю
dblSum = dblSum * arg4
dblSum = dblSum * arg5
MultipleFiveArgs = dblSum
End Function

Передав меньше аргументов в функцию =DivideFiveArgs(A1;A4) мы получим ошибку #ЗНАЧ!(#VALUE!), которую вызовет деление на ноль внутри кода на третьем аргументе.
А передав меньше аргументов в функцию умножения =MultipleFiveArgs(A1;A4)) — получим в качестве результата 0, т.к. на третьем аргументе умножим общую сумму на аргумент, который равен 0.

Проверять каждый аргумент на равенство нулю(If arg2 = 0 Then) тоже будет неверно — вдруг какой-либо реально переданный аргумент будет действительно равен 0? Будет неверный результат функции. Поэтому, чтобы функции выше заработали правильно — нужна проверка на отсутствие в аргументе значения.
Тут надо знать, что если тип аргумента не указан и сам аргумент в функцию не был передан — то ему назначается особый тип — Missing. Который и дает понять, что аргумент просто не передавался в функцию(Missing в переводе можно представить как «пропущен»). И в VBA для таких случаев есть специальная функция — IsMissing. Тогда можно более гибко манипулировать аргументами(на примере функции с умножением):

Function MultipleFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5)
    Dim dblSum As Double
    dblSum = arg1
    'проверяем, что аргумент передан(NOT IsMISSING)
    If Not IsMissing(arg2) Then
        dblSum = dblSum * arg2
    End If
    If Not IsMissing(arg3) Then
        dblSum = dblSum * arg3
    End If
    If Not IsMissing(arg4) Then
        dblSum = dblSum * arg4
    End If
    If Not IsMissing(arg5) Then
        dblSum = dblSum * arg5
    End If
    MultipleFiveArgs = dblSum
End Function

Function MultipleFiveArgs(arg1 As Double, Optional arg2, Optional arg3, Optional arg4, Optional arg5)
Dim dblSum As Double
dblSum = arg1
‘проверяем, что аргумент передан(NOT IsMISSING)
If Not IsMissing(arg2) Then
dblSum = dblSum * arg2
End If
If Not IsMissing(arg3) Then
dblSum = dblSum * arg3
End If
If Not IsMissing(arg4) Then
dblSum = dblSum * arg4
End If
If Not IsMissing(arg5) Then
dblSum = dblSum * arg5
End If
MultipleFiveArgs = dblSum
End Function

Как видно — теперь Optional можно использовать вполне эффективно. Но надо помнить одно правило: аргументы, заданные в функции с ключевым Optional должны быть заданы самими последними. Т.е. после них не может идти никаких других обязательных аргументов(без ключевого Optional). Впрочем, в этом случае VBA сообщит нам об этом ошибкой «Expected: Optional», что означает: Ожидался не обязательный аргумент.

И для большего кругозора еще одна простая функция, но которая работает уже с текстом и вернет строку до первого пробела:

Function ТекстДоПервогоПробела(Текст As String) As String
    Dim i As Long
    Dim Result As String 'переменная для результата
    i = InStr(1, Текст, " ", 1) 'ищем позицию первого пробела в переданном тексте
    'если пробел есть и он не первый символ в строке
    If i > 1 Then
        Result = Mid(Текст, 1, i - 1) 'получаем текст до первого пробела
    Else
    'если пробела нет - возвращаем всю строку
        Result = Текст
    End If
    'присваиваем результат функции для возврата его на лист
    ТекстДоПервогоПробела = Result
End Function

Function ТекстДоПервогоПробела(Текст As String) As String
Dim i As Long
Dim Result As String ‘переменная для результата
i = InStr(1, Текст, » «, 1) ‘ищем позицию первого пробела в переданном тексте
‘если пробел есть и он не первый символ в строке
If i > 1 Then
Result = Mid(Текст, 1, i — 1) ‘получаем текст до первого пробела
Else
‘если пробела нет — возвращаем всю строку
Result = Текст
End If
‘присваиваем результат функции для возврата его на лист
ТекстДоПервогоПробела = Result
End Function

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

Function ТекстДоПервогоПробела(Текст As String) As String
    ТекстДоПервогоПробела = Split(Текст, " ")(0)
End Function

Function ТекстДоПервогоПробела(Текст As String) As String
ТекстДоПервогоПробела = Split(Текст, » «)(0)
End Function

Но в таком виде функция вернет значение ошибки #ЗНАЧ!(#VALUE!), если ячейка с текстом будет пустой. Вдаваться в подробности не буду. Могу лишь написать, что функция VBA Split разбивает указанный текст на отдельные части, используя для разбиения указанный разделитель. И создает из разбитых частей одномерный массив с нижней границей, равной нулю. А функция выше просто возвращает первый элемент этого массива.
Обе функции можно дополнить не обязательным аргументом — разделитель слов. И сделать его по умолчанию пробелом. Значение по умолчанию в данном случае задается сразу при объявлении аргумента. Выглядеть это будет так:

Function ТекстДоУказанногоСимвола(Текст As String, Optional Разделитель As String = " ") As String
    ТекстДоУказанногоСимвола = Split(Текст, Разделитель)(0)
End Function

Function ТекстДоУказанногоСимвола(Текст As String, Optional Разделитель As String = » «) As String
ТекстДоУказанногоСимвола = Split(Текст, Разделитель)(0)
End Function

В данном примере если вызвать функцию так:
=ТекстДоУказанногоСимвола(A1)
то функция будет использовать в качестве разделителя пробел(Optional Разделитель As String = » «). Или можно задать символ разделения напрямую в функции и это может быть как пробел, так и любой другой символ:
=ТекстДоУказанногоСимвола(A1;»;»)

Динамическое количество аргументов в функции пользователя(ParamArray)
Сразу после некоторого использования Optional напрашивается вопрос: а если заранее неизвестно сколько аргументов будет передано? Может их будет передано 50? Или 70? Что, все перечислять? В принципе, можно сделать и так. Но можно и иначе. В VBA предусмотрен очень интересный тип данных — ParamArray. Он представляет собой динамический массив, размер которого зависит от количества переданных аргументов. На примере суммирования данных функция будет выглядеть следующим образом:

Function SumMultiple(ParamArray args())
    Dim dblSum As Double, arg
    On Error Resume Next
    For Each arg In args
        dblSum = dblSum + arg
    Next
    SumMultiple = dblSum
End Function

Function SumMultiple(ParamArray args())
Dim dblSum As Double, arg
On Error Resume Next
For Each arg In args
dblSum = dblSum + arg
Next
SumMultiple = dblSum
End Function

Но такая функция может выдать ошибку, если в качестве любого аргумента будет передана не одна единственная ячейка или значение — а диапазон ячеек(A1:A4) или массив({10;20;30}). В этом случае внутри функции обязательно придется определять тип данных внутри ParamArray. Сделать это можно следующим образом:

Function SumMultiple_DiffTypes(ParamArray args())
    Dim dblSum As Double, arg, rc As Range, x
    On Error Resume Next
    For Each arg In args
        Select Case TypeName(arg)
        Case "Range"                     'это диапазон
            'цикл по всем ячейкам
            For Each rc In arg.Cells
                'проверяем, что в ячейке числовой тип данных
                If IsNumeric(rc.Value) Then
                    dblSum = dblSum + rc.Value
                End If
            Next
        Case "Variant()"                 'это произвольный массив({10;20;30})
            'цикл по всем ячейкам
            For Each x In arg
                'проверяем, что это числовой тип данных
                If IsNumeric(x) Then
                    dblSum = dblSum + x
                End If
            Next
        Case "Double", "Long", "Integer" 'это любой числовой тип
            'суммируем
            dblSum = dblSum + arg
        'все остальные типы игнорируем
        End Select
    Next
    SumMultiple_DiffTypes = dblSum
End Function

Function SumMultiple_DiffTypes(ParamArray args())
Dim dblSum As Double, arg, rc As Range, x
On Error Resume Next
For Each arg In args
Select Case TypeName(arg)
Case «Range» ‘это диапазон
‘цикл по всем ячейкам
For Each rc In arg.Cells
‘проверяем, что в ячейке числовой тип данных
If IsNumeric(rc.Value) Then
dblSum = dblSum + rc.Value
End If
Next
Case «Variant()» ‘это произвольный массив({10;20;30})
‘цикл по всем ячейкам
For Each x In arg
‘проверяем, что это числовой тип данных
If IsNumeric(x) Then
dblSum = dblSum + x
End If
Next
Case «Double», «Long», «Integer» ‘это любой числовой тип
‘суммируем
dblSum = dblSum + arg
‘все остальные типы игнорируем
End Select
Next
SumMultiple_DiffTypes = dblSum
End Function

И в такую функцию может быть передан любой из наиболее распространенных типов данных:
=SumMultiple_DiffTypes({10;20;30};A1:A4;10;C1)
Но и у ParamArray есть недостаток: он не может использоваться одновременно с необязательными аргументами(Optional). Вместе с ParamArray могут быть использованы только обязательные аргументы и они должны обязательно идти ДО ParamArray. Если хоть один будет указан после, то получим ошибку компилятора: «Expected: )». Т.е. ожидалась завершающая скобка функции.

Так же можно применить ParamArray, чтобы указывать «неограниченное» количество аргументов для сцепления значений из ячеек в одну строку с указанным разделителем:

Function ОбъединитьВсеСРазделителем(Разделитель As String, ParamArray Значения()) As String
    Dim result As String, arg, x, rc As Range
    For Each arg In Значения
        Select Case TypeName(arg)
        Case "Range"                     'это диапазон
            'цикл по всем ячейкам
            For Each rc In arg.Cells
                If result = "" Then
                    result = rc.Value
                Else
                    result = result & Разделитель & rc.Value
                End If
            Next
        Case "Variant()"                 'это произвольный массив({"а";"б";"в"})
            'цикл по всем ячейкам
            For Each x In arg
                If result = "" Then
                    result = x
                Else
                    result = result & Разделитель & x
                End If
            Next
        Case Else 'это любой другой тип
            'суммируем
            If result = "" Then
                result = arg
            Else
                result = result & Разделитель & arg
            End If
        End Select
    Next
    ОбъединитьВсеСРазделителем = result
End Function

Function ОбъединитьВсеСРазделителем(Разделитель As String, ParamArray Значения()) As String
Dim result As String, arg, x, rc As Range
For Each arg In Значения
Select Case TypeName(arg)
Case «Range» ‘это диапазон
‘цикл по всем ячейкам
For Each rc In arg.Cells
If result = «» Then
result = rc.Value
Else
result = result & Разделитель & rc.Value
End If
Next
Case «Variant()» ‘это произвольный массив({«а»;»б»;»в»})
‘цикл по всем ячейкам
For Each x In arg
If result = «» Then
result = x
Else
result = result & Разделитель & x
End If
Next
Case Else ‘это любой другой тип
‘суммируем
If result = «» Then
result = arg
Else
result = result & Разделитель & arg
End If
End Select
Next
ОбъединитьВсеСРазделителем = result
End Function

Пример вызова такой функции с листа(первым обязательно передается разделитель, а далее уже что объединять — любой тип данных):
=ОбъединитьВсеСРазделителем(«; «;A1:A4;C1;»Привет»;{«а»;»б»;»в»})

Как добавить уже созданную функцию в свою книгу

Для начала необходимо создать стандартный модуль(Insert —Module). Затем в этот модуль вставить весь текст функции(код). Все, теперь функция доступна из диспетчера функций в категории Определенные пользователем(User defined), так же можно будет вводить эту функцию напрямую в ячейки той книги, в которой содержится код функции. Чтобы функция заработала очень важно разрешить макросы. Иначе результатом будет ошибка #ИМЯ!(#NAME!)
GIF-ка с инструкцией, как вставить функцию к себе в книгу на примере функции ТекстДоПервогоПробела из этой статьи:

Если Вы используете версию Excel 2007 и выше, то книгу необходимо будет сохранить с поддержкой макросов: Меню -Сохранить как -Книга Excel с поддержкой макросов.

Обновление расчетов функции пользователя UDF(автопересчет)
По умолчанию функции пользователя не пересчитываются вместе с пересчетом листа или по нажатию F9(Shift+F9). Чтобы функция пользователя пересчиталась, как правило необходимо либо изменить значение любого аргумента функции(например, изменить значение участвующей в расчетах ячейки) или имитировать редактирование самой функции последовательным нажатием клавиш F2-Enter. Это не всегда удобно и часто возникает вопрос: как заставить функцию пересчитываться при любом изменении листа и при пересчете листа/книги клавишами(F9 или Shift+F9). Между тем делается это довольно просто и при этом сделать можно для каждой отдельной функции. На примере простой функции записи даты-времени в ячейку:

Function ТекущаяДатаВремя()
    ТекущаяДатаВремя = Now 'Now - возвращает текущие дату и время
End Function

Function ТекущаяДатаВремя()
ТекущаяДатаВремя = Now ‘Now — возвращает текущие дату и время
End Function

Если записать её в таком виде, то после записи в ячейку:
=ТекущаяДатаВремя()
при первой записи будут показаны текущие дата и время. Чтобы эксперимент был более наглядным, лучше перейти в Формат ячеек и выставить для ячейки с функцией формат «ДД.ММ.ГГ ч:мм:сс;@». С небольшим интервалом времени понажимайте клавишу F9, чтобы вызвать пересчет книги. Тогда наглядно будет видно, что при пересчете значение функции не изменяется — секунды «застынут» на том месте, где были при начальном вводе функции. Выделите ячейку с функцией — нажмите F2-Enter. Только тогда значение будет пересчитано. А теперь чуть изменим функцию — добавим ключевой параметр пересчета — Application.Volatile:

Function ТекущаяДатаВремя()
    Application.Volatile True
    ТекущаяДатаВремя = Now 'Now - возвращает текущие дату и время
End Function

Function ТекущаяДатаВремя()
Application.Volatile True
ТекущаяДатаВремя = Now ‘Now — возвращает текущие дату и время
End Function

Теперь при каждом пересчете листа и при любом изменении на листе функция будет пересчитываться. В некоторых случаях это делать просто необходимо(например, если применяется функция получения имени листа или книги).
Но стоит всегда учитывать тот факт, что не всегда такой автопересчет полезен. Если функция пользователя использует «тяжелые» расчеты и выполняется долго — добавление автопересчета может значительно затормозить работу с файлом. Поэтому применять параметр следует с осторожностью.
Если надо, чтобы функция пересчитывалась только при изменениях в конкретном диапазоне/ячейках, можно просто сделать необязательные параметры:

Function ТекущаяДатаВремя(Optional ДиапазонОбновления As Range = Nothing)
    ТекущаяДатаВремя = Now
End Function

Function ТекущаяДатаВремя(Optional ДиапазонОбновления As Range = Nothing)
ТекущаяДатаВремя = Now
End Function

тогда при любом изменении в ячейках аргумента ДиапазонОбновления функция будет пересчитана. При этом использовать хоть как-то сам этот аргумент внутри функции совершенно необязательно. Выглядеть запись такой функции будет так:
=ТекущаяДатаВремя(E:E)
при любом изменении в столбце E функция будет пересчитана.

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

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

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Создаем свою первую функцию в Excel

Что такое функция в VBA?

Функция написанная на VBA — это код, который выполняет вычисления и возвращает значение (или массив значений). Создав функцию вы можете использовать ее тремя способами:

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

Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.

В правилах условного форматирования.

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

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

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

В чем отличие процедуры (Sub) от функции (Function)?

Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать значение (или массив значений).

Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.

С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.

Создание простой пользовательской функции в VBA

Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.

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


Function Цифры(Текст As String) As Long
    Dim i As Long
    Dim result As String
    
    For i = 1 To Len(Текст)
        If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1)
    Next
    
    Цифры = CLng(result)
End Function

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

Теперь посмотрим как функция работает, попробуем использовать ее на листе:

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

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

Когда вы ввели знак «=» и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.

Разбираем функцию пошагово

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


Function Цифры(Текст As String) As Long

Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.

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

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

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

После названия в круглых скобках описываются аргументы функции. По аналогии со встроенными функциями Excel. В нашем случае используется единственный аргумент Текст. После названия аргумента мы указали As String, это означает, что наш аргумент — текстовое значение или ссылка на ячейку, содержащее текстовое значение. Если вы не укажете тип данных, VBA рассмотрит его как Variant (что означает, что вы можете использовать любой тип данных, VBA его определит самостоятельно).

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

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


Dim i As Long
Dim result As String

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

Задача функции — пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.


For i = 1 To Len(Текст)

Len — функция, которая определяет количество символов.

Основная строка функции — это проверка является ли очередной символ текста цифрой и если да — то сохранение его в переменной result


If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1)

Для этого нам потребуется функция IsNumeric — она возвращает True если текст — число и False в противном случае.

Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/

Функция Next — закрывает цикл For тут все понятно.


Цифры = CLng(result)

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

Последняя строка кода — End Function. Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.

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

Скачать

Функции в Excel. — it-black.ru

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

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

Синтаксис функций в Excel

Для корректной работы, функция должна быть написана в определенной последовательности, которая называется синтаксис. К базовому синтаксису функции относятся знак равенства (=), имя функции (например, СУММ) и один или более аргументов. Аргументы содержат информацию, которую необходимо вычислить. В следующем примере функция суммирует значения в диапазоне A1:A20.

Вставка функций в Excel

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

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

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

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

2. Вводим знак равенства (=) и нужное имя функции. В нашем случае вводим =СРЗНАЧ.

3. Вводим диапазон ячеек в качестве аргумента в круглых скобках. В нашем примере мы вводим (C3:C10). Эта формула суммирует значения в диапазоне С3:С10, а затем делит результат на количество ячеек в этом диапазоне, тем самым определяя среднее значение.

4. Нажимаем Enter на клавиатуре. Функция будет вычислена, и можно увидеть результат. В данном примере средняя цена за единицу заказанных товаров составляет $15,93.

Вставка функций с помощью команды Автосумма

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

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

2. В группе команд Редактирование на вкладке Главная находим и нажимаем стрелку рядом с командой Автосумма, а затем выбераем нужную функцию в раскрывающемся меню. В нашем случае мы выберем Сумма.

3. Выбранная функция появится в ячейке. Команда Автосумма автоматически определяет диапазон ячеек для аргумента. В данном примере диапазон D3:D11 был выбран автоматически, а значения просуммированы, чтобы вычислить полную стоимость. Также можно вручную ввести нужный диапазон.

4. Нажимаем Enter на клавиатуре. Функция будет вычислена, и можно увидеть результат. В нашем примере сумма значений диапазона D3:D11 составляет $606,05.

ВПРАВО функция в Excel (формула, примеры)

ПРАВАЯ функция в Excel (Содержание)

ПРАВАЯ функция в Excel
Правильная формула в Excel
Как использовать правую функцию в Excel?

ПРАВАЯ функция в Excel

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

Например:

RIGHT («Бангалор», 5) в этой формуле Функция RIGHT вернет 5 символов с правой стороны данного текста, то есть «alore» .

Функция RIGHT также доступна в VBA. Мы обсудим это в конце этой статьи.

Правильная формула в Excel

Ниже приведена правильная формула в Excel:

Объяснение функции RIGHT в Excel

Правильная формула в Excel имеет два параметра: текст, num_chars.

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

Обычно функция RIGHT в Excel используется вместе с другими текстовыми функциями, такими как SEARCH, REPLACE, LEN, FIND, LEFT и т. Д.

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

ПРАВО Функция в Excel очень проста и удобна в использовании. Давайте рассмотрим работу функции RIGHT в Excel на примере формулы RIGHT. Функция RIGHT может использоваться как функция рабочего листа и как функция VBA.

Вы можете скачать этот шаблон Excel с ПРАВОЙ функцией здесь — Шаблон Excel с ПРАВОЙ функцией

Пример № 1

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

Мы можем извлечь последние 4 цифры из приведенного выше текста с помощью функции RIGHT в Excel.

Итак, результатом будет:

Пример № 2

Предположим, у вас есть серийные номера от A1 до A10, и вам нужно извлечь 6 символов справа.

Функция RIGHT вернет последние 6 цифр в правом конце текста.

Результат:

Пример № 3

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

В приведенном выше примере мы не можем просто применить ПРАВО с указанными номерами, потому что фамилия каждого сотрудника отличается.

Для первого сотрудника последние 4 символа — 5, а для второго — 11 (включая пробел, например, Роборт Адам), а для третьего — 12 (включая пробел, например, Дж. Джонстон)

Здесь нам нужно использовать функцию LEN & FIND, чтобы определить количество символов из заданного текста.

Итак, результатом будет:

Часть 1: эта часть определяет желаемый текст, который вы хотите извлечь символы.

Часть 2: функция LEN выдаст вам общее количество символов в списке. Мы увидим подробную статью о LEN в следующих статьях.

Результат:

Часть 3: Функция НАЙТИ определяет, с какого числа начинается пробел, т.е. Конец имени. Мы увидим подробную статью о НАЙТИ в следующих статьях.

Результат:

Лен дает 10 символов и найти дает 5 символов для первого сотрудника. Это означает, что LEN — FIND (10 — 5) = 5 символов с правой стороны.

Результатом будет Смит .

Лен дает 16 символов, а поиск — 5 символов для первого сотрудника. Это означает, что LEN — FIND (16 — 5) = 11 символов с правой стороны.

Результатом будет Роборт Адам.

Лен дает 17 символов, а поиск — 5 символов для первого сотрудника. Это означает, что LEN — FIND (17 — 5) = 12 символов с правой стороны.

Результатом будет J. Johsnston.

Примечание: пробел также считается одним символом.

Пример № 4

Давайте рассмотрим тот же пример из приведенного выше. Имена сотрудников и вам нужно извлечь фамилию отдельно. т.е. только ADAM, а не ROBORT ADAM.

Это делается с помощью формул LEN, FIND и SUBSTITUTE вместе с функцией RIGHT в Excel.

Первая функция SUBSTITUTE заменит пробел («») на «#», а затем LEN, функция вычтет номер пробела из функции SUBSTITUTE, чтобы получить только символы последней фамилии.

Таким образом, результат будет:

Пример № 5

Из приведенной ниже таблицы извлеките последнее число, пока не найдете пробел.

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

Итак, результатом будет:

Часть 1: эта часть определяет желаемый текст, который вы хотите извлечь символы.

Часть 2: функция LEN выдаст вам общее количество символов в списке.

Часть 3: Функция НАЙТИ определяет, с какого числа начинается символ « @» .

Пример № 6

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

Это можно сделать с помощью комбинации LEN, SEARCH & SUBSTITUTE и функции RIGHT в Excel.

Первым шагом является вычисление общей длины строки с использованием функции LEN: LEN (A2)
Второй шаг — вычисление длины строки без разделителей с помощью функции SUBSTITUTE, которая заменяет все вхождения двоеточия ничем: LEN (SUBSTITUTE (A2, ”:”, ””))
Наконец, мы вычитаем длину исходной строки без разделителей из общей длины строки: LEN (A2) -LEN (SUBSTITUTE (A2, ”:”, ””))

Итак, результатом будет:

Пример № 7

ВПРАВО функция в Excel не работает с датами. Поскольку функция RIGHT является текстовой функцией, она также может извлекать числа, но она не работает с датами.

Предположим, у вас есть дата в ячейке A1 «22-окт-18»

Тогда мы попробуем извлечь год по формуле.

Результат будет 3395.

В идеологии Excel 3395 означает 2018, если формат в датах. Таким образом, функция RIGHT в Excel не распознает ее как дату, а как обычное число.

VBA RIGHT Функция

В VBA также мы можем использовать функцию RIGHT. Ниже приведен простой пример правильной функции VBA.

Если вы запустите приведенный выше код, он даст вам следующий результат.

Что нужно помнить о функции RIGHT в Excel

Форматирование чисел не является частью строки и не будет извлечено или подсчитано.
Правая функция в Excel предназначена для извлечения символов с правой стороны указанного текста.
Если пользователь не указывает последний параметр, он по умолчанию будет равен 1.
Num_chars должно быть больше или равно нулю. Если это отрицательное число, оно выдаст ошибку как # ЗНАЧЕНИЕ.
Правая функция не даст точных результатов в случае форматирования даты.
В случае сложных наборов данных вам нужно использовать другие текстовые функции, такие как LEN, SEARCH, FIND и SUBSTITUTE, чтобы получить параметр Num_chars.

формул и функций в Excel

Введите формулу | Редактировать формулу | Приоритет операторов | Копировать / вставить формулу | Вставить функцию

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

Например, ячейка A3 ниже содержит формулу, которая добавляет значение ячейки A2 к значению ячейки A1.

Например, ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1: A2.

Введите формулу

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

1. Выберите ячейку.

2. Чтобы сообщить Excel, что вы хотите ввести формулу, введите знак равенства (=).

3. Например, введите формулу A1 + A2.

Совет: вместо того, чтобы вводить A1 и A2, просто выберите ячейку A1 и ячейку A2.

4.Измените значение ячейки A1 на 3.

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

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

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

1. Чтобы изменить формулу, щелкните в строке формул и измените формулу.

2.Нажмите Ввод.

Приоритет оператора

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

Сначала Excel выполняет умножение (A1 * A2).Затем Excel добавляет к этому результату значение ячейки A3.

Другой пример,

Сначала Excel вычисляет часть в круглых скобках (A2 + A3). Затем он умножает этот результат на значение ячейки A1.

Копировать / вставить формулу

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

1. Введите формулу, показанную ниже, в ячейку A4.

2а. Выделите ячейку A4, щелкните правой кнопкой мыши и затем нажмите Копировать (или нажмите CTRL + c) …

… затем выберите ячейку B4, щелкните правой кнопкой мыши и затем щелкните Вставить в разделе «Параметры вставки:» (или нажмите CTRL + v).

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

Результат. Формула в ячейке B4 ссылается на значения в столбце B.

Вставить функцию

Все функции имеют одинаковую структуру. Например, СУММ (A1: A4). Имя этой функции — СУММ. Часть в скобках (аргументы) означает, что мы передаем Excel диапазон A1: A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4.Непросто запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, функция вставки в Excel помогает вам в этом.

Чтобы вставить функцию, выполните следующие шаги.

1. Выберите ячейку.

2. Нажмите кнопку «Вставить функцию».

Откроется диалоговое окно «Вставить функцию».

3. Найдите функцию или выберите функцию из категории.Например, выберите СЧЁТЕСЛИ в категории «Статистические данные».

4. Щелкните OK.

Откроется диалоговое окно «Аргументы функции».

5. Щелкните поле Диапазон и выберите диапазон A1: C2.

6. Щелкните поле Criteria и введите> 5.

7. Щелкните OK.

Результат. Функция СЧЁТЕСЛИ подсчитывает количество ячеек больше 5.

Примечание: вместо использования функции вставки просто введите = СЧЁТЕСЛИ (A1: C2, «> 5»). Когда вы придете к: = СЧЁТЕСЛИ (вместо ввода A1: C2 просто выберите диапазон A1: C2.

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

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

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

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

1. СЦЕПИТЬ

= СЦЕПИТЬ — одна из самых простых в освоении, но наиболее эффективных формул при проведении анализа данных. Объединяйте текст, числа, даты и многое другое из нескольких ячеек в одну. Это отличная функция для создания конечных точек API, SKU продуктов и запросов Java.

Формула:

= CONCATENATE (ВЫБЕРИТЕ ЯЧЕЙКИ, КОТОРЫЕ ВЫ ХОТИТЕ ОБЪЕДИНЯЙТЕ)

В этом примере:

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

2.LEN

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

Формула:

= ОБЪЕКТИВ (ВЫБОР ЯЧЕЙКИ)

В этом примере:

= ДЛИН (A2)

3.COUNTA

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

Формула:

= СЧЁТ (ВЫБРАТЬ ЯЧЕЙКУ)

В примере:

= СЧЕТ (A10)

4. ДНИ / ЧИСТЫЕ ДНИ

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

= ЧИСТРАБДНИ немного более надежен и полезен. Эта формула определяет количество «рабочих дней» между двумя датами, а также возможность учета праздников. Даже трудоголикам время от времени нужен перерыв! Использование этих двух формул для сравнения временных рамок особенно полезно для управления проектами.

Формулы:

= ДНЕЙ (ВЫБРАТЬ ЯЧЕЙКУ, ВЫБРАТЬ ЯЧЕЙКУ)

ИЛИ

= ЧИСТЫЕ ДНИ (ВЫБРАТЬ ЯЧЕЙКУ, ВЫБРАТЬ ЯЧЕЙКУ, [количество праздников])

примечание: [количество праздников] необязательно

В примере:

= ДНИ (C8, B8)

ИЛИ

= ЧИСТЫЕ ДНИ (B7, C7,3)

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

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

Формула:

= СУММЕСЛИ (ДИАПАЗОН; КРИТЕРИИ; [диапазон_суммы])

примечание: [диапазон_суммы] является необязательным

В примере:

= СУММЕСЛИ ($ B $ 2: $ B $ 28, $ A $ 2: $ A $ 28, $ F2)

6.СРЕДНЕНОМН

Как и СУММЕСЛИМН, СРЗНАЧЕСЛИМН позволяет брать среднее значение на основе одного или нескольких критериев.

Формула:

= СРЕДНЕЛИ (ВЫБРАТЬ ЯЧЕЙКУ, КРИТЕРИИ; [СРЕДНИЙ_ДИАПАЗОН])

примечание: [средний_ диапазон] является необязательным

В примере:

= СРЕДНЕЛИ ($ C: $ C, $ A: $ A, $ F2)

7. ВПР

ВПР — одна из самых полезных и узнаваемых функций анализа данных. Как пользователю Excel, вам, вероятно, в какой-то момент придется «объединить» данные.Например, отдел дебиторской задолженности может знать, сколько стоит каждый продукт, но отдел отгрузки может предоставить только отгруженные единицы. Это идеальный вариант использования ВПР.

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

Формула:

= ВПР (LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP])

В примере:

= ВПР ($ A2, $ G $ 1: $ H $ 5,2,0)

8.НАЙТИ / ПОИСК

= НАЙТИ / = ПОИСК — мощные функции для выделения определенного текста в наборе данных. Оба перечислены здесь, потому что = FIND вернет совпадение с учетом регистра, т.е. если вы используете FIND для запроса «Big», вы вернете только результаты Big = true. Но a = SEARCH для «Big» будет совпадать с Big или big, делая запрос немного шире. Это особенно полезно для поиска аномалий или уникальных идентификаторов.

Формула:

= НАЙТИ (ТЕКСТ, WITHIN_TEXT; [START_NUMBER]) ИЛИ = ПОИСК (ТЕКСТ, WITHIN_TEXT, [START_NUMBER])

примечание: [start_number] является необязательным и используется для указания начальной ячейки в тексте для поиска

В примере:

= (НАЙТИ («Большой»; A2,1) »»)

9.ЕСЛИ ОШИБКА

= ЕСЛИ ОШИБКА — это то, чем должен воспользоваться любой аналитик, который активно представляет данные. Используя предыдущий пример, поиск определенного текста / значений в наборе данных не вернет совпадения. Это вызывает ошибку #VALUE и, хотя и безвредно, отвлекает и раздражает глаза.

Используйте = ЕСЛИОШИБКА, чтобы заменить ошибки #VALUE любым текстом / значением. В приведенном выше примере ячейка пуста, поэтому потребители данных могут легко выбрать, какие строки вернули совпадающее значение.
Формула:

= ЕСЛИ ОШИБКА (НАЙТИ «ЗНАЧЕНИЕ», ВЫБРАТЬ ЯЧЕЙКУ, ЗНАЧЕНИЕ_IF_ОШИБКА)

В примере:

= ЕСЛИ ОШИБКА (НАЙТИ «БОЛЬШОЙ»; A6,1); «»)

10.СЧЕТЕСЛИ

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

= СЧЁТЕСЛИМН (ДИАПАЗОН, КРИТЕРИИ)

В примере:

= СЧЁТЕСЛИМН ($ A: $ A, $ F9)

11. ВЛЕВО / ВПРАВО

= LEFT, = RIGHT — эффективные и простые методы извлечения статических данных из ячеек.= LEFT вернет количество символов «x» от начала ячейки, а = right вернет количество символов «x» от конца ячейки. В приведенном ниже примере = LEFT используется для извлечения кода зоны потребителя из его номера телефона, а = RIGHT используется для извлечения последних четырех цифр.

Формула:

= ЛЕВАЯ (ВЫБОР ЯЧЕЙКИ, НОМЕР)

ИЛИ

= ПРАВАЯ (ВЫБРАТЬ ЯЧЕЙКУ, НОМЕР)

В этом примере:

= ЛЕВЫЙ (A6, 3)

= ВПРАВО (A6,4)

12.РАНГ

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

Формула:

= РАНГ (ВЫБРАТЬ ЯЧЕЙКУ, ДИАПАЗОН_ТО_РАНГ_AGAINST, [ПОРЯДОК])

примечание: [заказ] является необязательным

В примере:

= РАНГ ($ B7, $ B $ 2: $ B $ 7,0)

примечание: 0 возвращает наибольшее ранжированное значение # 1

13.MINIFS

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

Формула:

= МИНИ-РАЗМН (ДИАПАЗОН1, КРИТЕРИИ1, ДИАПАЗОН2)

В этом примере:

= MINIFS ($ B $ B, $ A: $ A, $ E5)

14. MAXIFS

= MAXIFS, как и его аналоги minifs, позволяет вам сопоставлять критерии, но на этот раз он ищет максимальное число.
Формула:

= МАКСЕСЛИМН (ДИАПАЗОН1, КРИТЕРИИ1, ДИАПАЗОН2)

В этом примере:

= MAXIFS ($ B $ B, $ A: $ A, $ E5)

15. СУММПРОИЗВ

= СУММПРОИЗВ — отличная функция для расчета средней доходности, ценовых пунктов и маржи. SUMPRODUCT умножает один диапазон значений на соответствующие ему эквиваленты строки. Это золото анализа данных. В приведенном ниже примере мы вычисляем среднюю отпускную цену всех наших продуктов, умножая цену на количество, а затем делим на общий проданный объем.

Формула:

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

В этом примере:

= СУММПРОИЗВ (B2: B9, C2: C9) / C10

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

Если вы хотите узнать больше об Excel, мы обучаем основам по цене чашки кофе.

18 Easy Excel Советы, хитрости и ярлыки

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

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

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

Что такое Excel?

Microsoft Excel — это программа для работы с электронными таблицами, которую маркетологи, бухгалтеры, аналитики данных и другие специалисты используют для хранения, организации и отслеживания наборов данных. Это часть пакета продуктов Microsoft Office.Альтернативы включают Google Таблицы и Числа. Дополнительные альтернативы Excel можно найти здесь.

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

Для чего используется Excel?

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

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

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

Не знаете, как на самом деле можно использовать Excel в своей команде? Вот список документов, которые вы можете создать:

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

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

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

Вы также можете скачать нижеприведенные шаблоны Excel для всех ваших маркетинговых потребностей.

После загрузки шаблонов пора приступить к использованию программного обеспечения. Давайте сначала рассмотрим основы.

Основы работы с Excel

Если вы только начинаете работать с Excel, мы предлагаем вам ознакомиться с несколькими основными командами. Это такие вещи, как:

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

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

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

Формулы Excel

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

Знак равенства: Перед созданием любой формулы вам необходимо написать знак равенства (=) в ячейке, в которой должен отображаться результат.
Добавление : чтобы сложить значения двух или более ячеек, используйте знак + . Пример: = C5 + D3 .
Вычитание : чтобы вычесть значения двух или более ячеек, используйте знак –. Пример: = C5-D3 .
Умножение : Чтобы умножить значения двух или более ячеек, используйте знак * . Пример: = C5 * D3 .
Деление : Чтобы разделить значения двух или более ячеек, используйте знак /.Пример: = C5 / D3 .

Соединив все это вместе, вы можете создать формулу, которая складывает, вычитает, умножает и делит все в одной ячейке. Пример: = (C5-D3) / ((A5 + B6) * 3) .

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

Функции Excel

Функции

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

СУММ: функция СУММ автоматически складывает диапазон ячеек или чисел. Чтобы получить сумму, вы должны ввести начальную и последнюю ячейки с двоеточием между ними. Вот как это выглядит: SUM (Cell1: Cell2) . Пример: = СУММ (C5: C30) .
СРЕДНИЙ: функция СРЕДНИЙ усредняет значения диапазона ячеек.Синтаксис такой же, как у функции СУММ: СРЕДНЕЕ (Ячейка1: Ячейка2). Пример: = СРЕДНИЙ (C5: C30) .
ЕСЛИ: функция ЕСЛИ позволяет возвращать значения на основе логической проверки. Синтаксис следующий: IF (логический_тест, значение_если_ истинно, [значение_если_ ложь]) . Пример: = ЕСЛИ (A2> B2, «Превышение бюджета», «ОК») .
ВПР. Функция ВПР помогает искать что угодно в строках листа. Синтаксис: ВПР (значение поиска, массив таблицы, номер столбца, приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)) .Пример: = ВПР ([@ Attorney], tbl_Attorneys, 4, ЛОЖЬ).
ИНДЕКС: функция ИНДЕКС возвращает значение из диапазона. Синтаксис следующий: ИНДЕКС (массив, номер_строки, [номер_столбца]) .
ПОИСКПОЗ: функция ПОИСКПОЗ ищет определенный элемент в диапазоне ячеек и возвращает положение этого элемента. Его можно использовать вместе с функцией ИНДЕКС. Синтаксис: MATCH (lookup_value, lookup_array, [match_type]) .
СЧЁТЕСЛИ: функция СЧЁТЕСЛИ возвращает количество ячеек, которые соответствуют определенным критериям или имеют определенное значение.Синтаксис: СЧЁТЕСЛИ (диапазон, критерий). Пример: = СЧЁТЕСЛИ (A2: A5, «Лондон»).

Хорошо, готовы перейти к мелочам? Давайте перейдем к этому. (И всем фанатам Гарри Поттера … добро пожаловать заранее.)

Советы по Excel

Используйте сводные таблицы для распознавания и анализа данных.
Добавьте более одной строки или столбца.
Используйте фильтры, чтобы упростить данные.
Удалите повторяющиеся точки или наборы данных.
Переставить строки в столбцы.
Разделите текстовую информацию между столбцами.
Используйте эти формулы для простых вычислений.
Получите среднее значение чисел в ячейках.
Используйте условное форматирование, чтобы ячейки автоматически меняли цвет в зависимости от данных.
Используйте формулу IF Excel для автоматизации определенных функций Excel.
Используйте знаки доллара, чтобы формула одной ячейки оставалась неизменной независимо от того, куда она перемещается.
Используйте функцию ВПР для переноса данных из одной области листа в другую.
Используйте формулы ИНДЕКС и ПОИСКПОЗ для извлечения данных из горизонтальных столбцов.
Используйте функцию СЧЁТЕСЛИ, чтобы Excel подсчитывал слова или числа в любом диапазоне ячеек.
Объедините ячейки с помощью амперсанда.
Добавьте флажки.
Гиперссылка ячейки на веб-сайт.
Добавить раскрывающиеся меню.

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

1. Используйте сводные таблицы для распознавания и анализа данных.

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

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

Чтобы создать сводную таблицу, я перехожу к Data > Pivot Table . Если вы используете самую последнюю версию Excel, перейдите к Insert > Pivot Table . Excel автоматически заполнит вашу сводную таблицу, но вы всегда можете изменить порядок данных.Затем у вас есть четыре варианта на выбор.

Фильтр отчета : позволяет просматривать только определенные строки в наборе данных. Например, если бы я хотел создать фильтр по домам, я мог бы включить только студентов Гриффиндора, а не всех студентов.
Ярлыки столбцов : это будут ваши заголовки в наборе данных.
Ярлыки строк : Это могут быть ваши строки в наборе данных. Метки строк и столбцов могут содержать данные из ваших столбцов (например,грамм. Имя можно перетащить на метку строки или столбца — это просто зависит от того, как вы хотите видеть данные.)
Значение : Этот раздел позволяет вам по-другому взглянуть на ваши данные. Вместо того, чтобы просто вводить какое-либо числовое значение, вы можете суммировать, подсчитывать, среднее, максимальное, минимальное, подсчитывать числа или выполнять несколько других манипуляций с вашими данными. Фактически, по умолчанию, когда вы перетаскиваете поле в значение, всегда выполняется подсчет.

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

2. Добавьте более одной строки или столбца.

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

Чтобы добавить несколько строк или столбцов в электронную таблицу, выделите такое же количество ранее существовавших строк или столбцов, которые вы хотите добавить.Затем щелкните правой кнопкой мыши и выберите «Вставить».

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

3. Используйте фильтры, чтобы упростить ваши данные.

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

Вот тут и пригодятся фильтры.

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

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

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

Pro Tip : Скопируйте и вставьте значения в электронную таблицу, когда фильтр включен, чтобы выполнить дополнительный анализ в другой электронной таблице.

4. Удалите повторяющиеся точки или наборы данных.

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

Чтобы удалить дубликаты, выделите строку или столбец, дубликаты которых вы хотите удалить. Затем перейдите на вкладку «Данные» и выберите «Удалить дубликаты» (который находится под подзаголовком «Инструменты» в старой версии Excel). Появится всплывающее окно, чтобы подтвердить, с какими данными вы хотите работать. Выберите «Удалить дубликаты», и все готово.

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

5. Переставьте строки в столбцы.

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

Начните с выделения столбца, который вы хотите преобразовать в строки. Щелкните его правой кнопкой мыши и выберите «Копировать». Затем выберите ячейки в электронной таблице, с которых вы хотите начать первую строку или столбец. Щелкните ячейку правой кнопкой мыши и выберите «Специальная вставка». Появится модуль — внизу вы увидите возможность транспонировать. Установите этот флажок и выберите ОК. Теперь ваш столбец будет перенесен в строку или наоборот.

В более новых версиях Excel вместо всплывающего окна появляется раскрывающийся список.

6. Разделите текстовую информацию между столбцами.

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

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

Во-первых, вам нужно выбрать «С разделителями» или «Фиксированная ширина».

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

В приведенном ниже примере выберите «С разделителями», чтобы мы могли разделить полное имя на имя и фамилию.

Тогда пришло время выбрать разделители. Это может быть табуляция, точка с запятой, запятая, пробел или что-то еще. («Что-то еще» может быть, например, знаком «@» в адресе электронной почты.) В нашем примере давайте выберем пробел. Затем Excel покажет вам предварительный просмотр того, как будут выглядеть ваши новые столбцы.

Когда вы довольны предварительным просмотром, нажмите «Далее». Эта страница позволит вам выбрать расширенные форматы, если вы захотите. Когда вы закончите, нажмите «Готово».

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

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

Чтобы добавить, используйте знак +.
Для вычитания используйте знак -.
Для умножения используйте знак *.
Для разделения используйте знак /.

Вы также можете использовать круглые скобки, чтобы убедиться, что сначала выполняются определенные вычисления. В приведенном ниже примере (10 + 10 * 10) второе и третье 10 были умножены вместе перед добавлением дополнительных 10.Однако, если мы сделаем это (10 + 10) * 10, первое и второе 10 будут добавлены вместе в первую очередь.

8. Получите среднее значение чисел в своих ячейках.

Если вы хотите получить среднее значение набора чисел, вы можете использовать формулу = СРЕДНЕЕ (Ячейка1: Ячейка2) . Если вы хотите суммировать столбец чисел, вы можете использовать формулу = СУММ (Ячейка1: Ячейка2) .

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

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

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

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

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

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

Формула: IF (логический_тест, значение_если_ истинно, [значение_если_ ложь])

Пример показан ниже: = IF (D2 = «Гриффиндор», «10», «0»)

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

Logical_Test : Логическая проверка — это часть оператора «ЕСЛИ».В этом случае логика D2 = «Гриффиндор», потому что мы хотим убедиться, что ячейка, соответствующая ученику, написана «Гриффиндор». Не забудьте поставить здесь Гриффиндор в кавычки.
Value_if_True : это то, что мы хотим, чтобы ячейка показывала, истинно ли значение. В этом случае мы хотим, чтобы в ячейке отображалось «10», чтобы указать, что ученику были присвоены 10 баллов. Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом.
Value_if_False : это то, что мы хотим, чтобы ячейка показывала, если значение ложно.В этом случае для любого ученика не из Гриффиндора мы хотим, чтобы в ячейке отображался «0». Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом.

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

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

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

Видите ли, ссылка на ячейку — например, когда вы ссылаетесь на ячейку A5 из ячейки C5 — по умолчанию является относительной. В этом случае вы на самом деле имеете в виду ячейку, которая находится на пять столбцов слева (C минус A) и в той же строке (5). Это называется относительной формулой.Когда вы копируете относительную формулу из одной ячейки в другую, она корректирует значения в формуле в зависимости от того, куда она переместилась. Но иногда мы хотим, чтобы эти значения оставались неизменными независимо от того, перемещаются они или нет — и мы можем сделать это, превратив формулу в абсолютную.

Чтобы преобразовать относительную формулу (= A5 + C5) в абсолютную формулу, мы бы поставили перед значениями строки и столбца знаки доллара, например: (= $ A $ 5 + $ C $ 5) . (Дополнительные сведения см. На странице поддержки Microsoft Office здесь.)

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

Были ли у вас когда-нибудь два набора данных в двух разных таблицах, которые вы хотите объединить в одну таблицу?

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

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

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

Формула: = ВПР (значение подстановки, массив таблицы, номер столбца, приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ))

Формула с переменными из нашего примера ниже: = ВПР (C2, Sheet2! A: B, 2, FALSE)

В этой формуле есть несколько переменных.Следующее верно, если вы хотите объединить информацию с листа 1 и листа 2 на листе 1.

Значение поиска : это идентичное значение, которое есть в обеих таблицах. Выберите первое значение в своей первой таблице. В следующем примере это означает первый адрес электронной почты в списке или ячейку 2 (C2).
Массив таблиц : массив таблиц — это диапазон столбцов на листе 2, из которого вы собираетесь извлекать данные, включая столбец данных, идентичный вашему значению поиска (в нашем примере, адреса электронной почты) на листе 1. как столбец данных, который вы пытаетесь скопировать на лист 1.В нашем примере это «Sheet2! A: B». «A» означает столбец A на листе 2, который является столбцом на листе 2, где перечислены данные, идентичные нашему поисковому значению (электронная почта) в листе 1. «B» означает столбец B, который содержит информацию, доступную только на листе 2, которую вы хотите перевести на лист 1.
Номер столбца : сообщает Excel, в каком столбце находятся новые данные, которые вы хотите скопировать на лист 1. В нашем примере это будет столбец, в котором находится «Дом». «Дом» — это второй столбец в наш диапазон столбцов (массив таблиц), поэтому номер столбца равен 2.[ Примечание : Ваш диапазон может состоять более чем из двух столбцов. Например, если на Листе 2 есть три столбца — Электронная почта, Возраст и Дом — и вы все еще хотите перенести Дом на Лист 1, вы все равно можете использовать ВПР. Вам просто нужно изменить «2» на «3», чтобы вернуть значение в третьем столбце: = ВПР (C2: Sheet2! A: C, 3, false).]
Приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ) : Используйте ЛОЖЬ, чтобы гарантировать, что вы выбираете только точные совпадения значений. Если вы используете ИСТИНА, функция найдет приблизительные совпадения.

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

Итак, когда мы вводим формулу = ВПР (C2, Лист2! A: B, 2, ЛОЖЬ) , мы переносим все данные о доме в Лист 1.

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

13. Используйте формулы ИНДЕКС и ПОИСКПОЗ для извлечения данных из горизонтальных столбцов.

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

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

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

Рассмотрим пример. Скажем, Лист 1 содержит список имен людей и их адреса электронной почты в Хогвартсе, а Лист 2 содержит список адресов электронной почты людей и патронуса, которые есть у каждого студента. (Для поклонников, не относящихся к Гарри Поттеру, у каждой ведьмы или волшебника есть опекун животных, называемый «Патронус», связанный с ним или с ней.) Информация, которая находится на обоих листах, — это столбец, содержащий адреса электронной почты, но этот столбец адресов электронной почты находится в разных столбцах на каждом листе.Я бы использовал формулы ИНДЕКС и ПОИСКПОЗ вместо ВПР, чтобы мне не пришлось переключать какие-либо столбцы.

Так в чем же формула? Формула на самом деле является формулой ПОИСКПОЗ, вложенной в формулу ИНДЕКС. Вы увидите, что я выделил формулу MATCH, используя здесь другой цвет.

Формула: = ИНДЕКС (массив таблиц, формула ПОИСКПОЗ)

Это становится: = ИНДЕКС (массив таблицы, ПОИСКПОЗ ( lookup_value, lookup_array))

Формула с переменными из нашего примера ниже: = ИНДЕКС (Sheet2! A: A, (MATCH (Sheet1! C: C, Sheet2! C: C, 0)))

Вот переменные:

Массив таблицы : диапазон столбцов на листе 2, содержащих новые данные, которые вы хотите перенести на лист 1.В нашем примере «A» означает столбец A, который содержит информацию «Patronus» для каждого человека.
Подстановочное значение : это столбец на листе 1, который содержит идентичные значения в обеих электронных таблицах. В следующем примере это означает столбец «электронная почта» на листе 1, который является столбцом C. Итак: Sheet1! C: C.
Подстановочный массив : это столбец на листе 2, который содержит идентичные значения в обеих электронных таблицах. В следующем примере это относится к столбцу «электронная почта» на листе 2, который также является столбцом C.Итак: Sheet2! C: C.

После того, как вы определите переменные, введите формулы ИНДЕКС и ПОИСКПОЗ в самой верхней ячейке пустого столбца Patronus на листе 1, где вы хотите разместить объединенную информацию.

14. Используйте функцию СЧЁТЕСЛИ, чтобы Excel подсчитывал слова или числа в любом диапазоне ячеек.

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

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

Формула: = СЧЁТЕСЛИ (диапазон, критерий)

Формула с переменными из нашего примера ниже: = СЧЁТЕСЛИ (D: D, «Гриффиндор»)

В этой формуле есть несколько переменных:

Диапазон : диапазон, который мы хотим охватить формулой. В этом случае, поскольку мы фокусируемся только на одном столбце, мы используем «D: D», чтобы указать, что первый и последний столбцы — это D.Если бы я смотрел на столбцы C и D, я бы использовал «C: D».
Критерии : любое число или фрагмент текста, которые Excel должен подсчитать. Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом. В нашем примере критерием является «Гриффиндор».

Просто введите формулу СЧЁТЕСЛИ в любой ячейке и нажмите «Ввод», чтобы увидеть, сколько раз слово «Гриффиндор» встречается в наборе данных.

15. Объедините ячейки с помощью &.

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

Формула с переменными из нашего примера ниже: = A2 & «» & B2

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

Но вы еще не закончили — если все, что вы вводите, это = A2 и B2, то между именем и фамилией человека не будет пробела. Чтобы добавить это необходимое пространство, используйте функцию = A2 & «» & B2 .Кавычки вокруг пробела указывают Excel, что нужно ставить пробел между именем и фамилией.

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

16. Добавьте флажки.

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

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

Вот как это сделать.

Выделите ячейку, для которой вы хотите добавить флажки в своей электронной таблице. Затем щелкните РАЗРАБОТЧИК. Затем в разделе ФОРМА УПРАВЛЕНИЯ установите флажок или кружок выбора, выделенный на изображении ниже.

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

17. Гиперссылка ячейки на веб-сайт.

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

Выделите слова, на которые вы хотите создать гиперссылку, затем нажмите Shift K. Оттуда появится окно, позволяющее разместить URL-адрес гиперссылки. Скопируйте и вставьте URL-адрес в это поле и нажмите или нажмите Enter.

Если сочетание клавиш по какой-либо причине не работает, вы также можете сделать это вручную, выделив ячейку и нажав Вставить > Гиперссылка .

18. Добавьте раскрывающиеся меню.

Иногда вы будете использовать электронную таблицу для отслеживания процессов или других качественных вещей. Вместо того, чтобы многократно писать в листе слова, такие как «Да», «Нет», «Стадия клиента», «Руководитель продаж» или «Потенциальный клиент», вы можете использовать раскрывающиеся меню, чтобы быстро отмечать описательные сведения о ваших контактах или о том, что вы отслеживаем.

Вот как добавить раскрывающиеся списки к ячейкам.

Выделите ячейки, в которых должны находиться раскрывающиеся списки, затем щелкните меню «Данные» в верхней части навигации и нажмите «Проверка».

Оттуда вы увидите открытое окно «Параметры проверки данных». Посмотрите на параметры «Разрешить», затем щелкните «Списки» и выберите «Раскрывающийся список». Проверьте раскрывающуюся кнопку In-Cell, затем нажмите OK.

Другие справочные ресурсы Excel

Используйте Excel для автоматизации процессов в вашей команде

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

Примечание редактора: этот пост был первоначально опубликован в августе 2017 года, но был обновлен для полноты.

Список функций — автоматизация Excel

Новые функции
XLOOKUP	Заменяет VLOOKUP, HLOOKUP и INDEX / MATCH	XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])
Логический	да
И	Проверяет выполнение всех условий.ИСТИНА / ЛОЖЬ	И (логический1, логический2)
IF	Если условие выполнено, сделайте что-нибудь, если нет, сделайте что-нибудь еще.	ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь)
ЕСЛИ ОШИБКА	Если результатом является ошибка, сделайте что-нибудь еще.	ЕСЛИОШИБКА (ЗНАЧЕНИЕ; значение_если_ошибка)
НЕ	Изменяет ИСТИНА на ЛОЖЬ и ЛОЖЬ на ИСТИНУ.	НЕ (логический)
ИЛИ	Проверяет выполнение каких-либо условий.ИСТИНА / ЛОЖЬ	ИЛИ (логический1, логический2)
XOR	Проверяет, выполняется ли одно и только одно условие. ИСТИНА / ЛОЖЬ	XOR (логический1, логический2)
Поиск и справочная информация	да
ЛОЖЬ	Логическое значение: ЛОЖЬ.	ЛОЖЬ
ИСТИНА	Логическое значение: ИСТИНА.	ИСТИНА
ADDRESS	Возвращает адрес ячейки в виде текста.	АДРЕС (row_num, column_num, abs_num, C1, sheet_text)
ОБЛАСТИ	Возвращает количество областей в ссылке.	ПЛОЩАДЬ (ссылка)
ВЫБРАТЬ	Выбирает значение из списка на основе номера позиции.	ВЫБРАТЬ (номер_индекса, значение1, значение2)
COLUMN	Возвращает номер столбца ссылки на ячейку.	КОЛОНКА (ссылка)
COLUMNS	Возвращает количество столбцов в массиве.	КОЛОННЫ (массив)
HLOOKUP	Найдите значение в первой строке и верните значение.	HLOOKUP (lookup_value, table_array, row_index_num, range_lookup)
HYPERLINK	Создает интерактивную ссылку.	ГИПЕРССЫЛКА (расположение_ссылки, дружественное_имя)
ИНДЕКС	Возвращает значение на основе номеров столбцов и строк.	ИНДЕКС (массив, номер_строки, номер_столбца)
КОСВЕННО	Создает ссылку на ячейку из текста.	КОСВЕННО (ref_text, C1)
ПРОСМОТР	Поиск значений по горизонтали или вертикали.	ПРОСМОТР (lookup_value, lookup_vector, result_vector)
MATCH	Ищет значение в списке и возвращает его позицию.	MATCH (lookup_value, lookup_array, match_type)
OFFSET	Создает опорное смещение от начальной точки.	OFFSET (ссылка, строки, столбцы, высота, ширина)
ROW	Возвращает номер строки ссылки на ячейку.	ROW (ссылка)
ROWS	Возвращает количество строк в массиве.	РЯД (массив)
TRANSPOSE	Изменяет ориентацию диапазона ячеек.	ТРАНСПОРТ (массив)
ВПР	Найдите значение в первом столбце и верните значение.	ВПР (lookup_value, table_array, col_index_num, range_lookup)
Дата и время	да
ДАТА	Возвращает дату из года, месяца и дня.	ДАТА (год, месяц, день)
РАЗНДАТ	Количество дней, месяцев или лет между двумя датами.	РАЗНДАТ
DATEVALUE	Преобразует дату, сохраненную в виде текста, в действительную дату	DATEVALUE (date_text)
ДЕНЬ	Возвращает день в виде числа (1-31).	ДЕНЬ (серийный_номер)
ДНЕЙ	Возвращает количество дней между двумя датами.	ДНЕЙ (end_date, start_date)
DAYS360	Возвращает дни между двумя датами в году из 360 дней.	ДНЕЙ360 (начальная_дата, конечная_дата, метод)
EDATE	Возвращает дату через n месяцев от начальной даты.	ДАТА (начальная_дата, мес.)
EOMONTH	Возвращает последний день месяца через n месяцев.	EOMONTH (начальная_дата, мес.)
ЧАС	Возвращает час в виде числа (0–23).	ЧАС (серийный_номер)
МИНУТА	Возвращает минуты в виде числа (0-59).	МИНУТА (серийный_номер)
МЕСЯЦ	Возвращает месяц в виде числа (1–12).	МЕСЯЦ (серийный_номер)
ЧИСТЫЕ ДНИ	Количество рабочих дней между двумя датами.	ЧИСТЫЕ ДНИ (начальная_дата, конечная_дата, праздники)
ЧИСТЫХ ДНЕЙ.INTL	Рабочие дни между 2 датами, выходные по индивидуальному заказу.	NETWORKDAYS.INTL (начальная_дата, конечная_дата, выходные, праздничные дни)
СЕЙЧАС	Возвращает текущую дату и время.	СЕЙЧАС ()
SECOND	Возвращает секунды в виде числа (0-59)	SECOND (serial_number)
ВРЕМЯ	Возвращает время в формате часа, минуты и секунды.	ВРЕМЯ (час, минута, секунда)
TIMEVALUE	Преобразует время, сохраненное в виде текста, в действительное время.	ВРЕМЕННОЕ ЗНАЧЕНИЕ (время_текст)
СЕГОДНЯ	Возвращает текущую дату.	СЕГОДНЯ ()
WEEKDAY	Возвращает день недели в виде числа (1-7).	WEEKDAY (serial_number, return_type)
WEEKNUM	Возвращает номер недели в году (1-52).	НОМЕР НЕДЕЛИ (серийный_номер, return_type)
РАБДЕНЬ	Дата n рабочих дней от даты.	РАБДЕНЬ (начальная_дата, дни, праздники)
WORKDAY.INTL	Дата n рабочих дней от даты, настраиваемые выходные.	WORKDAY.INTL (начальная_дата, дни, выходные, праздничные дни)
YEAR	Возвращает год.	ГОД (серийный_номер)
YEARFRAC	Возвращает долю года между двумя датами.	YEARFRAC (начальная_дата, конечная_дата, базис)
Инженерное дело	да
CONVERT	Преобразование числа из одной единицы в другую.	CONVERT (число, from_unit, to_unit)
Финансовый
FV	Вычисляет будущую стоимость.	FV (ставка, nper, pmt, pv, тип)
PV	Вычисляет текущую стоимость.	PV (ставка, кпер, пт, фс, тип)
NPER	Вычисляет общее количество периодов выплат.	КПЕР (ставка, pmt, pv, fv, тип)
PMT	Рассчитывает сумму платежа.	PMT (ставка, nper, pv, fv, type)
RATE	Вычисляет процентную ставку.	СТАВКА (КОП, ПП, ПС, БС, тип, предположение)
NPV	Вычисляет чистую приведенную стоимость.	NPV (ставка, значение1, значение2)
IRR	Внутренняя норма доходности для набора периодических CF.	IRR (значения, предположить)
XIRR	Внутренняя норма доходности для набора непериодических CF.	XIRR (значения, даты, предположение)
ЦЕНА	Вычисляет цену облигации.	ЦЕНА (расчет, срок погашения, ставка, дата погашения, ЧАСТОТА, базис)
YIELD	Рассчитывает доходность облигаций.	ДОХОДНОСТЬ (расчет, срок, ставка, цена, погашение, ЧАСТОТА, базис)
INTRATE	Процентная ставка полностью инвестированной ценной бумаги.	INTRATE (расчет, срок погашения, инвестирование, погашение, основание)
Информация	да
CELL	Возвращает информацию о ячейке.	ЯЧЕЙКА (тип_информации, ссылка)
ERROR.TYPE	Возвращает значение, представляющее ошибку ячейки.	ТИП ОШИБКИ (значение_ошибки)
ISBLANK	Проверить, пуста ли ячейка. ИСТИНА / ЛОЖЬ	ISBLANK (ЗНАЧЕНИЕ)
ISERR	Проверить, является ли значение ячейки ошибкой, игнорирует # N / A. ИСТИНА / ЛОЖЬ	ISERR (ЗНАЧЕНИЕ)
ISERROR	Проверить, является ли значение ячейки ошибкой.ИСТИНА / ЛОЖЬ	ОШИБКА (ЗНАЧЕНИЕ)
ISEVEN	Проверка четности значения ячейки. ИСТИНА / ЛОЖЬ	ЕСТЬ (число)
ISFORMULA	Проверить, является ли ячейка формулой. ИСТИНА / ЛОЖЬ	ISFORMULA (ссылка)
ISLOGICAL	Проверить, является ли ячейка логической (ИСТИНА или ЛОЖЬ). ВЕРНО / ЛОЖЬ	ИСЛОГИЧЕСКОЕ (ЗНАЧЕНИЕ)
ISNA	Проверить, является ли значение ячейки # N / A.ВЕРНО / ЛОЖЬ	ISNA (ЗНАЧЕНИЕ)
ISNONTEXT	Проверить, не является ли ячейка текстом (пустые ячейки не являются текстом). ИСТИНА / ЛОЖЬ	ISNONTEXT (VALUE)
ISNUMBER	Проверить, является ли ячейка числом. ИСТИНА / ЛОЖЬ	ЕМКОСТЬ (ЗНАЧЕНИЕ)
ISODD	Проверить, является ли значение ячейки нечетным. ИСТИНА / ЛОЖЬ	ISODD (число)
ISREF	Проверить, является ли значение ячейки ссылкой.ИСТИНА / ЛОЖЬ	ISREF (VALUE)
ISTEXT	Проверить, является ли ячейка текстом. ИСТИНА / ЛОЖЬ	ISTEXT (VALUE)
N	Преобразует значение в число.	Н (ЗНАЧЕНИЕ)
NA	Возвращает ошибку: # N / A.	NA ()
TYPE	Возвращает тип значения в ячейке.	ТИП (ЗНАЧЕНИЕ)
Математика	да
ABS	Вычисляет абсолютное значение числа.	ABS (номер)
AGGREGATE	Определение и выполнение вычислений для базы данных или списка.	АГРЕГАТ (номер_функции, параметры, массив, k)
CEILING	Округляет число до ближайшего указанного кратного.	ПОТОЛОК (число, значение)
COS	Возвращает косинус угла.	COS (номер)
ГРАДУСОВ	Преобразует радианы в градусы.	ГРАДУСОВ (угол)
DSUM	Суммирует записи базы данных, соответствующие определенным критериям.	DSUM (база данных, поле, критерии)
ЧЕТНОЕ	Округляет до ближайшего четного целого числа.	ЧЕТ (номер)
EXP	Вычисляет экспоненциальное значение для заданного числа.	EXP (номер)
FACT	Возвращает факториал.	ФАКТ (номер)
FLOOR	Округляет число до ближайшего указанного кратного.	ЭТАЖ (число, значение)
GCD	Возвращает наибольший общий делитель.	GCD (число1, число2)
INT	Округляет число до ближайшего целого.	INT (номер)
LCM	Возвращает наименьшее общее кратное.	LCM (число1, число2)
LN	Возвращает натуральный логарифм числа.	LN (номер)
LOG	Возвращает логарифм числа по указанному основанию.	ЖУРНАЛ (номер, основание)
LOG10	Возвращает десятичный логарифм числа.	LOG10 (номер)
MOD	Возвращает остаток после деления.	MOD (число, делитель)
MROUND	Округляет число до указанного кратного.	MROUND (число, кратное)
ODD	Округляет до ближайшего нечетного целого числа.	ODD (номер)
PI	Значение PI.	PI ()
СТЕПЕНЬ	Вычисляет число в степени.	POWER (число, мощность)
PRODUCT	Умножает массив чисел.	ТОВАР (номер1, номер2)
QUOTIENT	Возвращает целочисленный результат деления.	ЧИСЛО (числитель, знаменатель)
РАДИАНЫ	Преобразует угол в радианы.	РАДИАНЫ (угол)
RAND	Вычисляет случайное число от 0 до 1.	RAND ()
RANDBETWEEN	Вычисляет случайное число между двумя числами.	RANDBETWEEN (внизу, вверху)
ОКРУГЛ	Округляет число до указанного количества цифр.	КРУГЛЫЙ (число, число_цифров)
ОКРУГЛ. ВНИЗ	Округляет число в меньшую сторону (до нуля).	ОКРУГЛ ВНИЗ (число, число_цифров)
ROUNDUP	Округляет число в большую сторону (от нуля).	ОКРУГЛ ВВЕРХ (число, число_цифров)
SIGN	Возвращает знак числа.	ЗНАК (номер)
SIN	Возвращает синус угла.	SIN (номер)
SQRT	Вычисляет квадратный корень из числа.	SQRT (номер)
SUBTOTAL	Возвращает итоговую статистику для серии данных.	ПРОМЕЖУТОЧНЫЙ ИТОГ (function_num, REh2)
СУММ	Складывает числа.	СУММ (число1, число2)
СУММЕСЛИ	Суммирует числа, соответствующие критерию.	СУММЕСЛИ (диапазон, критерий, диапазон_суммы)
СУММЕСЛИМН	Суммирует числа, удовлетворяющие нескольким критериям.	СУММЕСЛИМН (диапазон_сумм, диапазон_критерия, критерий)
SUMPRODUCT	Умножает массивы чисел и суммирует результирующий массив.	СУММПРОИЗВ (массив1, массив2, массив3)
TAN	Возвращает тангенс угла.	TAN (номер)
TRUNC	Усекает число до определенного количества цифр.	TRUNC (число, число_цифров)
Статистика	да
СРЕДНЕЕ	Средние числа.	СРЕДНЕЕ (число1, число2)
AVERAGEA	Средние числа. Включает текст & FALSE = 0, TRUE = 1.	СРЕДНЕЕ (значение1, значение2)
AVERAGEIF	Усредняет числа, соответствующие критериям.	AVERAGEIF (диапазон, критерий, средний_диапазон)
СРЕДНЕНОМН	Усредняет числа, отвечающие нескольким критериям.	AVERAGEIFS (средний_диапазон, критерий_диапазон, критерий)
CORREL	Вычисляет корреляцию двух рядов.	КОРРЕЛ (массив1, массив2)
COUNT	Подсчитывает ячейки, содержащие число.	СЧЁТ (значение1, значение2)
COUNTA	Подсчитайте непустые ячейки.	СЧЁТ (значение1, значение2)
COUNTBLANK	Подсчитывает пустые ячейки.	СЧЕТЧИК (диапазон)
COUNTIF	Подсчитывает ячейки, соответствующие критериям.	СЧЁТЕСЛИ (диапазон, критерии)
СЧЁТЕСЛИ	Подсчитывает ячейки, соответствующие нескольким критериям.	СЧЁТЕСЛИМН (диапазон_критериев, критерий)
ПРОГНОЗ	Предскажите будущие значения y по линейной линии тренда.	ПРОГНОЗ (x, известные_y, известные_x)
ЧАСТОТА	Подсчитывает значения, попадающие в указанные диапазоны.	ЧАСТОТА (массив_данных, массив бинов)
РОСТ	Вычисляет значения Y на основе экспоненциального роста.	РОСТ (известный_ys, известный_x, новый_x, const)
INTERCEPT	Вычисляет точку пересечения Y для наиболее подходящей линии.	ПЕРЕСЕЧЕНИЕ (известное_ис, известное_x)
LARGE	Возвращает k-е наибольшее значение.	БОЛЬШОЙ (массив, k)
ЛИНЕЙН	Возвращает статистику линии тренда.	ЛИНЕЙН (известное_ys, известное_x, константа, статистика)
MAX	Возвращает наибольшее число.	МАКС (число1, число2)
MEDIAN	Возвращает среднее число.	МЕДИАНА (число1, число2)
MIN	Возвращает наименьшее число.	МИН (число1, число2)
РЕЖИМ	Возвращает наиболее часто встречающееся число.	РЕЖИМ (число1, число2)
PERCENTILE	Возвращает k-й процентиль.	ПРОЦЕНТИЛЬ (массив, k)
PERCENTILE.INC	Возвращает k-й процентиль. Где k включительно.	ПРОЦЕНТИЛЬ.INC (массив, k)
PERCENTILE.EXC	Возвращает k-й процентиль. Где k является исключительным.	ПРОЦЕНТИЛЬ.EXC (массив, k)
КВАРТИЛЬ	Возвращает указанное значение квартиля.	КВАРТИЛЬ (массив, кварт)
QUARTILE.INC	Возвращает указанное значение квартиля. Включительно.	QUARTILE.INC (массив, кварт)
QUARTILE.EXC	Возвращает указанное значение квартиля. Эксклюзив.	QUARTILE.EXC (массив, кварт)
RANK	Ранг числа в серии.	РАНГ (номер, исх., Заказ)
РАНГ.AVG	Ранг числа в серии. Средние.	RANK.AVG (номер, исх., Заказ)
RANK.EQ	Ранг числа в серии. Высший ранг.	RANK.EQ (номер, ссылка, порядок)
НАКЛОН	Вычисляет наклон на основе линейной регрессии.	НАКЛОН (known_ys, known_xs)
SMALL	Возвращает k-е наименьшее значение.	МАЛЫЙ (массив, k)
STDEV	Вычисляет стандартное отклонение.	СТАНДОТКЛОН (число1, число2)
STDEV.P	Вычисляет SD для всей генеральной совокупности.	СТАНДОТКЛОН.П (число1, число2)
STDEV.S	Вычисляет SD образца.	СТАНДАРТ.S (число1, число2)
STDEVP	Вычисляет SD для всей генеральной совокупности	STDEVP (число1, число2)
TREND	Вычисляет значения Y на основе линии тренда.	ТЕНДЕНЦИЯ (известные_вы, известные_x, новые_xs, константа)
Текст	да
CHAR	Возвращает символ, заданный кодом.	СИМВОЛ (число)
CLEAN	Удаляет все непечатаемые символы.	CLEAN (текст)
CODE	Возвращает числовой код символа.	КОД (текст)
CONCATENATE	Объединяет текст вместе.	СЦЕПИТЬ (текст1, текст2)
ДОЛЛАР	Преобразует число в текст в денежном формате.	ДОЛЛАР (число, десятичные знаки)
EXACT	Проверьте, равны ли ячейки. Деликатный случай. ИСТИНА / ЛОЖЬ	ТОЧНО (текст1, текст2)
НАЙТИ	Определяет положение текста в ячейке. С учетом регистра.	НАЙТИ (find_text, внутри_text, start_num)
LEFT	Обрезает текст на количество символов слева.	ЛЕВЫЙ (текст, число_знаков)
LEN	Подсчитывает количество символов в тексте.	LEN (текст)
НИЖНИЙ	Преобразует текст в нижний регистр.	НИЖНИЙ (текст)
MID	Извлекает текст из середины ячейки.	MID (текст, start_num, num_chars)
ПРАВИЛЬНО	Преобразует текст в правильный регистр.	ПРАВИЛЬНО (текст)
REPLACE	Заменяет текст в зависимости от его местоположения.	REPLACE (old_text, start_num, num_chars, new_text)
REPT	Повторяет текст несколько раз.	ПОВТОР (текст, число_раз)
СПРАВА	Обрезает текст на количество символов справа.	СПРАВА (текст, число_знаков)
ПОИСК	Определяет положение текста в ячейке.Нечувствительный к регистру.	ПОИСК (find_text, внутри_text, start_num)
ЗАМЕНА	Находит и заменяет текст. Деликатный случай.	ПОДСТАВИТЬ (текст, старый_текст, новый_текст, номер_экземпляра)
ТЕКСТ	Преобразует значение в текст с определенным числовым форматом.	ТЕКСТ (ЗНАЧЕНИЕ, формат_текст)
TRIM	Удаляет все лишние пробелы из текста.	ОТДЕЛКА (текст)
ВЕРХНИЙ	Преобразует текст в верхний регистр.	ВЕРХНИЙ (текст)
ЗНАЧЕНИЕ	Преобразует число, сохраненное в виде текста, в число.	ЗНАЧЕНИЕ (текст)

10 функций Excel, которые должен знать каждый маркетолог

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

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

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

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

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

1. Форматирование таблицы

Что он делает: преобразует ваши данные в интерактивную базу данных.

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

Чтобы настроить таблицу, щелкните любую ячейку в наборе данных и выберите «Главная»> «Стили»> «Форматировать как таблицу».Оттуда вы можете персонализировать свои настройки, пока не создадите чистый и интерактивный рабочий лист.

2. Сводные таблицы

Что он делает: суммирует данные и находит уникальные значения.

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

Чтобы создать сводную таблицу, начните с выбора данных и преобразования их в таблицу (см. Первый совет). Затем выберите сводную таблицу.Откройте новый рабочий лист и используйте построитель сводной таблицы, чтобы отсортировать данные по своему усмотрению. Это видео поможет вам изучить основы работы со сводной таблицей.

3. График

Что он делает: создает столбчатые, столбчатые, круговые или линейные диаграммы на основе ваших данных.

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

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

Даже простая диаграмма лучше, чем ничего, но если вы хотите сделать ее еще лучше, ознакомьтесь с «10 простыми советами, которые помогут сделать ваши диаграммы Excel интереснее».

4. СЧЕТЕСЛИ

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

СЧЁТЕСЛИМН позволяет быстро разрезать данные и подсчитывать количество ячеек в определенном диапазоне, которые соответствуют заданным критериям. Синтаксис функции СЧЁТЕСЛИ прост: = СЧЁТЕСЛИ (диапазон, критерий).

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

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

Что он делает: складывает все числа в диапазоне ячеек, которые соответствуют указанным критериям.

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

Например, вы можете использовать функцию СУММЕСЛИМН, чтобы сложить все продажи определенного продукта, сделанные конкретным продавцом. Базовый синтаксис функции СУММЕСЛИМН: = СУММЕСЛИМН (диапазон_суммы, диапазон_критерия1, критерий1, диапазон_критерия2, критерий2…).

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

6. Отчетность по IF

Что он делает: возвращает одно значение, если заданное условие истинно, и другое, если оно ложно.

Используя функцию If, вы можете быстро определить, соответствуют ли данные указанным условиям. Синтаксис только для функции ЕСЛИ прост: = IF (логический_тест, значение_если_ истинно, значение_если_ ложь).Как вы можете сделать вывод, основываясь на тестах «если правда» и «если ложь», эта функция допускает два возможных результата.

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

7. СЦЕПИТЬ

Назначение: объединяет текст из нескольких ячеек в одну.

Функция СЦЕПИТЬ — еще одна функция, которая может показаться простой, но она может сэкономить много времени.

Допустим, у вас есть две колонки текста, которые вы хотите объединить (например, имя человека и его фамилия). Вместо того, чтобы повторно вводить всю информацию, вы можете использовать функцию СЦЕПИТЬ, чтобы объединить эти два столбца.

Начните с вставки нового столбца, в который вы собираетесь собирать эти данные. Синтаксис этой функции = СЦЕПИТЬ (объединяемые ячейки). Например, = СЦЕПИТЬ (A2, B2) объединит ячейки A2 и B2 (скажем, в ячейке C3).

8.ВПР

Что он делает: находит значение в одном столбце, а затем находит соответствующее значение в той же строке, но в другом столбце.

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

Чтобы использовать ВПР, добавьте в свою электронную таблицу столбец, в котором будут отображаться найденные данные.Выберите первую пустую ячейку в этом столбце и нажмите «Вставить»> «Функция», а затем введите «ВПР». После выбора появится диалоговое поле, позволяющее определить четыре значения для поиска.

Вот краткое описание функции ВПР.

9. Условное форматирование

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

Если вы хотите определить тенденции в наборе данных, условное форматирование — один из лучших способов сделать это.Выберите данные, щелкните Формат, а затем щелкните Условный формат. Появится диалоговое окно «Управление правилами», в котором можно выбрать правила форматирования в соответствии с вашими требованиями.

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

10. Сочетания клавиш

Назначение: позволяет выполнять функции или открывать окна несколькими нажатиями клавиш.

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

CMD + SHIFT + T находит сумму всего столбца данных, CMD + 1 открывает окно «Формат ячеек», CMD + K вставляет гиперссылку, а F11 превращает выбранные данные в диаграмму на новом листе.

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

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

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

функций Microsoft Excel — полный список

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

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

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

Новые функции Excel

LAMBDA — Создавайте свои собственные функции в Excel.

Функции динамических массивов

ФИЛЬТР — возвращает массив, удовлетворяющий определенным критериям. Одна из лучших функций Excel.

Математические функции

СУММ — складывает все значения в диапазоне.

СУММЕСЛИ — складывает все значения в диапазоне, соответствующие определенным критериям.

СУММЕСЛИМН — добавляет значения в диапазоне на основе нескольких критериев.

СУММПРОИЗВ — суммирование диапазона ячеек, удовлетворяющих нескольким критериям.

ROUND — округление числа до указанного количества цифр.

ОКРУГЛВВЕРХ — округление числа до указанного количества цифр.

ROUNDDOWN — округлить число до указанного количества цифр.

ПОТОЛОК — округлите число до значимого, кратного значению.

ЭТАЖ — Округлите число до кратного значимости.

Статистические функции

COUNT — подсчитывает все значения в диапазоне.

СРЕДНИЙ — вычисляет среднее число из диапазона значений.

MAX — Находит максимальное значение в диапазоне.

MIN — Находит минимальное значение в диапазоне.

COUNTA — подсчитывает все непустые ячейки в диапазоне.

СЧИТАТЬПУСТОТЫ — подсчитывает все пустые ячейки в диапазоне.

СЧЁТЕСЛИ — подсчитывает все ячейки в диапазоне, удовлетворяющие определенным критериям.

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

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

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

НАИБОЛЬШИЙ — возвращает значение, зависящее от его ранжирования в диапазоне значений в порядке убывания.

SMALL — возвращает значение, зависящее от его ранжирования в диапазоне значений в порядке возрастания.

RANK — Возвращает ранг или позицию числа в диапазоне чисел.

Текстовые функции

LEN — Возвращает длину в количестве символов содержимого ячейки

REPT — повторяет символ указанное количество раз

TRIM — Удалить ненужные пробелы из ячеек

СЛЕВА — извлекает определенное количество символов из начала ячейки

ВПРАВО — извлекает определенное количество символов из конца ячейки

MID — извлекает определенное количество символов из середины ячейки

ВЕРХНИЙ — Преобразует содержимое ячейки в верхний регистр

LOWER — конвертирует содержимое ячейки в нижний регистр

PROPER — Преобразует содержимое ячейки в соответствующий регистр

REPLACE — Заменить существующие символы в ячейке другим набором символов

ЗАМЕНА — заменить существующие символы другим набором символов

Финансовые функции

PMT — Рассчитывает выплаты по кредиту на основе постоянных платежей и постоянной процентной ставки

RATE — возвращает процентную ставку за период ссуды или инвестиции

PV — возвращает текущую стоимость инвестиций при постоянной процентной ставке и выплатах

FV — возвращает будущую стоимость инвестиций на основе постоянных платежей и постоянной процентной ставки

IPMT — Расчет процентов, выплачиваемых в течение периода ссуды или инвестиции

PPMT — Рассчитывает основной платеж, произведенный в период инвестирования

IRR — возвращает внутреннюю норму доходности для серии регулярных инвестиций

XIRR — возвращает внутреннюю норму прибыли для серии нерегулярных платежей по инвестициям

NPV — возвращает чистую приведенную стоимость инвестиции на основе ряда денежных потоков и ставки дисконтирования

XNPV — возвращает чистую приведенную стоимость инвестиции на основе ряда денежных потоков, дат денежных потоков и ставки дисконтирования

Функции поиска и ссылки

ВПР — просматривает список по вертикали, чтобы найти запись, и возвращает информацию, относящуюся к этой записи

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

MATCH — Возвращает позицию значения в списке

INDEX — возвращает элемент из определенной позиции в списке.Еще одна из лучших функций Excel. Мой большой фаворит.

INDIRECT — позволяет использовать ссылку на ячейку, введенную в виде текстовой строки

СМЕЩЕНИЕ — возвращает значение из ячейки или диапазона ячеек, которые представляют собой указанное количество строк и столбцов из другой ячейки

CHOOSE — возвращает значение из списка значений на основе указанной позиции

АДРЕС — возвращает текстовое представление адреса ячейки из указанной строки и номера столбца

Логические функции

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

AND — Проверка до 30 условий с помощью логического И.

ИЛИ — проверьте до 30 условий с помощью логического ИЛИ.

ЕСЛИОШИБКА — выполняет указанное действие, если формула дает ошибку, и отображает результат формулы, если не

Функции даты и времени

СЕГОДНЯ — возвращает текущую дату

СЕЙЧАС — возвращает текущую дату и время

ДАТА — возвращает последовательный порядковый номер для указанной даты и форматирует результат как дату

ДЕНЬ — возвращает день, соответствующий дате, представленной числом от 1 до 31

МЕСЯЦ — возвращает месяц, соответствующий дате, представленной числом от 1 до 12.

ГОД — возвращает год, соответствующий дате, представленной числом в диапазоне от 1900 до 9999

РАБДЕНЬ — возвращает дату на указанное количество рабочих дней до или после даты

WEEKDAY — возвращает день недели, соответствующий указанной дате

ЧИСТРАБДНИ — возвращает количество рабочих дней между двумя датами

EOMONTH — вычисляет последний день месяца на указанное количество месяцев до или после даты

видов функций, используемых в Excel | Small Business

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

Агрегат

Некоторые из наиболее полезных и часто используемых функций Excel работают с диапазоном ячеек. Эти функции вычисляют результат, который является совокупностью диапазона. Например, СУММ (A1… A25) вычисляет сумму ячеек с 1 по 25 в столбце A электронной таблицы.Вычисление промежуточных и итоговых сумм с помощью SUM () — одна из наиболее частых операций, выполняемых в программах для работы с электронными таблицами. Другая функция, COUNT (), подсчитывает количество ячеек в своем диапазоне, содержащих числа. Многие статистические функции Excel также вычисляют совокупное значение диапазона ячеек.

Финансы

Аннуитетные, облигационные, ипотечные и другие сложные финансовые расчеты могут быть математически сложными, поэтому Microsoft включила 53 связанных функции в Excel. Например, функция PMT () вычисляет платеж по кредиту с учетом количества платежей, процентной ставки и основной суммы.Вы используете его в формуле ячейки, как показано в следующем примере:

= PMT (0,05,48,10000)

В этом примере процентная ставка является первым параметром (5 процентов), количество платежей — 48, и основная сумма кредита составляет 10 000 долларов США. Ячейка, в которую вы ввели функцию, содержит значение (553,18), что означает, что каждый платеж составляет 553,18 доллара США.

Аналогичным образом, чтобы найти будущую стоимость инвестиций, возвращающих стабильную ставку начисленных процентов, вы используете функцию FV (), как показано в этом примере:

= FV (.05, 48, -500, -10000)

Как и в случае с PMT, первым параметром является процентная ставка (0,05), а 48 — количество платежей. Отрицательные значения 500 и 10 000 представляют собой ежемесячный платеж в размере 500 долларов США в инвестицию при первоначальном взносе в размере 10 000 долларов США. Когда вы вводите эту функцию в электронную таблицу, в ячейке отображается 198 025,39 доллара США, что является будущей стоимостью инвестиций после того, как вы совершили последний платеж.

Статистический

Excel имеет множество функций, которые можно использовать для статистического анализа диапазона столбцов.Одна общая функция, СРЗНАЧ (), вычисляет среднее значение нескольких значений. Функции MAX () и MIN () дают вам максимальное и минимальное значения в диапазоне. Функция СТАНДОТКЛОН.P () вычисляет стандартное отклонение диапазона; небольшой показатель стандартного отклонения указывает на близкое соответствие между числами в диапазоне и их средним значением. Другая сложная статистика включает пять функций хи-квадрат, гармонические и геометрические средние, три функции для дисперсии и две, которые вычисляют ранжирование чисел в наборе.

Scientific

Инженеры и ученые используют тригонометрию, логарифмы и функции, управляющие числами. В Excel есть полный набор триггерных функций, таких как SIN (), COS () и TAN (), а также их обратных функций, ASIN (), ACOS () и ATAN, и соответствующих гиперболических функций SINH (), COSH ( ) и TANH (). Логарифмические функции включают в себя натуральные и общие журналы, LN () и LOG10 (), а также LOG (), в котором используется произвольное основание. Excel предоставляет числовые функции, такие как INT (), который округляет число до ближайшего целого числа, и CEILING (), который округляет число в большую сторону на указанное значение.

Логический

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

= IF (A1> = 500, A1 * .9, A1)

В этом примере тестовая часть функции «A1> = 500» проверяет, больше ли значение ячейки A1 или равно 500. Если оно истинно, ячейка, содержащая эту функцию, становится значением A1 раз.9; если false, значение A1 не изменяется. Вы можете использовать это, например, для предоставления 10-процентной скидки для заказов клиентов на общую сумму 500 долларов США или более. К другим логическим функциям Excel относятся OR (), которое проверяет набор условий и выполняет функцию, если любое из них истинно, и AND (), которое выполняет функцию, только если все условия истинны.

Текст

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

Функция excel: Справочник по функциям в Microsoft Excel

Функция ВПР (VLOOKUP) в Excel: пошаговая инструкция с примерами

Как создать функцию ВПР в Excel

Аргументы функции ВПР

Автозаполнение

ВПР и приблизительный интервальный просмотр

Итоги

Функции и ошибки в MS Excel — урок. Информатика, 9 класс.

Функция ЕСЛИ в Excel с примерами

Пример использования функции ЕСЛИ в Excel

Что такое функция пользователя(UDF)?

Поиск по меткам

Создаем свою первую функцию в Excel

Что такое функция в VBA?

В чем отличие процедуры (Sub) от функции (Function)?

Создание простой пользовательской функции в VBA

Разбираем функцию пошагово

Функции в Excel. — it-black.ru

Синтаксис функций в Excel

Вставка функций в Excel

Вставка функций с помощью команды Автосумма

ВПРАВО функция в Excel (формула, примеры)

ПРАВАЯ функция в Excel

Правильная формула в Excel

Объяснение функции RIGHT в Excel

Пример № 1

Пример № 2

Пример № 3

Пример № 4

Пример № 5

Пример № 6

Пример № 7

VBA RIGHT Функция

Рекомендуемые статьи

формул и функций в Excel

Введите формулу

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

Приоритет оператора

Копировать / вставить формулу

Вставить функцию

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

1. СЦЕПИТЬ

= CONCATENATE (ВЫБЕРИТЕ ЯЧЕЙКИ, КОТОРЫЕ ВЫ ХОТИТЕ ОБЪЕДИНЯЙТЕ)

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

2.LEN

= ОБЪЕКТИВ (ВЫБОР ЯЧЕЙКИ)

= ДЛИН (A2)

3.COUNTA

= СЧЁТ (ВЫБРАТЬ ЯЧЕЙКУ)

= СЧЕТ (A10)

4. ДНИ / ЧИСТЫЕ ДНИ

= ДНЕЙ (ВЫБРАТЬ ЯЧЕЙКУ, ВЫБРАТЬ ЯЧЕЙКУ)

= ЧИСТЫЕ ДНИ (ВЫБРАТЬ ЯЧЕЙКУ, ВЫБРАТЬ ЯЧЕЙКУ, [количество праздников])

= ДНИ (C8, B8)

= ЧИСТЫЕ ДНИ (B7, C7,3)

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

= СУММЕСЛИ (ДИАПАЗОН; КРИТЕРИИ; [диапазон_суммы])

= СУММЕСЛИ ($ B $ 2: $ B $ 28, $ A $ 2: $ A $ 28, $ F2)

6.СРЕДНЕНОМН

= СРЕДНЕЛИ (ВЫБРАТЬ ЯЧЕЙКУ, КРИТЕРИИ; [СРЕДНИЙ_ДИАПАЗОН])

= СРЕДНЕЛИ ($ C: $ C, $ A: $ A, $ F2)

7. ВПР

= ВПР (LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP])

= ВПР ($ A2, $ G $ 1: $ H $ 5,2,0)

8.НАЙТИ / ПОИСК

= НАЙТИ (ТЕКСТ, WITHIN_TEXT; [START_NUMBER]) ИЛИ = ПОИСК (ТЕКСТ, WITHIN_TEXT, [START_NUMBER])

= (НАЙТИ («Большой»; A2,1) »»)

9.ЕСЛИ ОШИБКА

= ЕСЛИ ОШИБКА (НАЙТИ «ЗНАЧЕНИЕ», ВЫБРАТЬ ЯЧЕЙКУ, ЗНАЧЕНИЕ_IF_ОШИБКА)

= ЕСЛИ ОШИБКА (НАЙТИ «БОЛЬШОЙ»; A6,1); «»)

10.СЧЕТЕСЛИ

= СЧЁТЕСЛИМН (ДИАПАЗОН, КРИТЕРИИ)

= СЧЁТЕСЛИМН ($ A: $ A, $ F9)

11. ВЛЕВО / ВПРАВО

= ЛЕВАЯ (ВЫБОР ЯЧЕЙКИ, НОМЕР)

= ПРАВАЯ (ВЫБРАТЬ ЯЧЕЙКУ, НОМЕР)

= ЛЕВЫЙ (A6, 3)

= ВПРАВО (A6,4)

12.РАНГ

= РАНГ (ВЫБРАТЬ ЯЧЕЙКУ, ДИАПАЗОН_ТО_РАНГ_AGAINST, [ПОРЯДОК])