Эксель функции: Функция ВПР — Служба поддержки Office

Функция ВЫБОР — Служба поддержки Office

В этой статье описаны синтаксис формулы и использование функции ВЫБОР в Microsoft Excel.

Описание

Использует номер_индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. Функция ВЫБОР позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента «номер_индекса».

Синтаксис

ВЫБОР(номер_индекса;значение1;[значение2];. ..)

Аргументы функции ВЫБОР описаны ниже.

• 
  Номер_индекса    — обязательный аргумент. Номер выбираемого аргумента-значения. Номер индекса должен быть числом от 1 до 254, формулой или ссылкой на ячейку, содержащую число в диапазоне от 1 до 254.

  • Если номер_индекса равен 1, то функция ВЫБОР возвращает значение1; если он равен 2, возвращается значение2 и так далее.

  • Если index_num меньше 1 или больше числа последнего значения в списке, то выбор возвращает #VALUE! значение ошибки #ЗНАЧ!.

  • Если номер_индекса представляет собой дробь, то он усекается до меньшего целого.

• 
  Значение1,значение2,…     — аргумент «значение1» является обязательным, следующие за ним — нет. От 1 до 254 аргументов-значений, из которых функция ВЫБОР, используя номер индекса, выбирает значение или выполняемое действие. Аргументы могут быть числами, ссылками на ячейки, определенными именами, формулами, функциями или текстом.

Замечания

• Если номер_индекса является массив, то при выполнении функции ВЫБОР вычисляется каждое значение.

• Аргументы-значения функции ВЫБОР могут быть как ссылками на интервал, так и отдельными значениями.

  Например, формула:

=СУММ(ВЫБОР(2;A1:A10;B1:B10;C1:C10))

эквивалентна формуле:

=СУММ(B1:B10)

которая возвращает значение, вычисленное на основе значений в интервале ячеек B1:B10.

В этом примере сначала вычисляется функция ВЫБОР, которая возвращает ссылку на интервал B1:B10. Затем вычисляется функция СУММ, причем в качестве аргумента используется интервал B1:B10, то есть результат функции ВЫБОР.

Примеры

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

Пример 2

Функции Excel 2016: 10 самых важных формул

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

Объединение текстовых значений

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

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

Раскрывающиеся списки Excel

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

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(” “;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

Применение: Данная опция часто используется для склеивания ФИО, когда отдельные составные части находятся в разных колонках и есть общая сводная колонка с полным именем человека.

Выполнение условия ИЛИ

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, <, =, так и поиск конкретного значения для ячейки, которое может быть текстовым. В частности, для поиска слова «Сдал» в конкретных ячейках будет использоваться формула =ИЛИ(D4= “Сдал”; E4= “Сдал”; F4= “Сдал”)

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

Поиск и использование значения

По горизонтали

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

Применение: Для расчета бонуса конкретному сотруднику можно задать интервалы, начиная от которых действует тот или иной процент от прибыли. Скажем, формула =ГПР(E5;$D$1:$G$2;2;ИСТИНА) будет искать в первой строке таблицы из интервала D1:G2 значение, приблизительно схожее со значением из ячейки E5, а результатом формулы станет вывод ячейки со второй строки соответствующего столбца.

По вертикали

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

То есть для формулы =ВПР(E4;$I$3:$J$6;2;ИСТИНА) будет сравниваться значение ячейки E4 с ячейками столбца I из таблицы интервала I3:J6, а значение будет выдаваться из соседней ячейки столбца J.

Выполнение условия ЕСЛИ

При использовании данной функции задается конкретное условие, а следом два результата – один для случаев, если условия выполнено, а другое – наоборот. Скажем для сравнения денежных средств из двух колонок может использоваться такая формула =ЕСЛИ(C2>B2; “Превышение бюджета”; “В пределах бюджета”).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; “ДА”;ЕСЛИ(D4=2; “Нет”; “Возможно”)).

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

Формула ранжирования

Для значения чисел можно использовать формулу РАНГ, которая выдаст величину каждого числа относительно других в заданном списке. При этом ранжирование может быть как от меньшего значения в сторону увеличения, так и обратно.

Как установить пароль на документ Word или Excel

Как установить пароль на документ Word или Excel
Для безопасности своих документов не лишним бывает установить на них персональный пароль.
Подробнее

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

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

Максимум из выбранных значений

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

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

Минимум из выбранных значений

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

Среднее из выбранных значений

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

Сумма выбранных значений

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

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; “Да”; F5:F9)

Примечание: Подробную информацию об использовании каждой функции Excel можно найти на официальном сайте Microsoft Office.

Создание и примеры пользовательской функции в Excel читать в пошаговом руководстве

Автор Дмитрий Якушев На чтение 31 мин. Просмотров 9k.

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

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

В этом руководстве я расскажу о создании и использовании пользовательских функций в VBA.

Что такое функциональная процедура в VBA?

Процедура Function — это код VBA, который выполняет вычисления и возвращает значение (или массив значений).

Используя процедуру Function, вы можете создать функцию, которую вы можете использовать на рабочем листе (как и любую обычную функцию Excel, такую ​​как SUM или VLOOKUP).

Когда вы создали процедуру Function с использованием VBA, вы можете использовать ее тремя способами:

1. В качестве формулы на рабочем листе, где она может принимать аргументы в качестве входных данных и возвращать значение или массив значений.
2. Как часть кода вашей подпрограммы VBA или другого кода функции.
3. В условном форматировании

Хотя на рабочем листе уже имеется более 450 встроенных функций Excel, вам может потребоваться настраиваемая функция, если:

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

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

Функция против Подпрограммы в VBA

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

Например, если у вас есть список чисел (как положительных, так и отрицательных), и вы хотите идентифицировать отрицательные числа, вот что вы можете сделать с помощью функции и подпрограммы.

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

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

Когда вы создаете пользовательскую функцию (UDF) с использованием VBA, вы можете использовать эту функцию на листе, как и любую другую функцию. Я расскажу об этом подробнее в разделе «Различные способы использования пользовательских функций в Excel».

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

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

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

Function GetNumeric(CellRef As String) as Long
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

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

Ниже показано, как эту функцию — GetNumeric — можно использовать в Excel.

Теперь, прежде чем я расскажу вам, как эта функция создается в VBA и как она работает, вам нужно знать несколько вещей:

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

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

Анатомия пользовательской функции в VBA

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

Теперь давайте углубимся и посмотрим, как создается эта функция. Вы должны поместить приведенный ниже код в модуль в VB Editor. Я рассматриваю эту тему в разделе «Где разместить код VBA для пользовательской функции».

Function GetNumeric(CellRef As String) as Long
' Эта функция извлекает числовую часть из строки
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumeric = Result
End Function

Первая строка кода начинается со слова «Функция».

Это слово говорит VBA, что наш код является функцией (а не подпрограммой). За словом Function следует имя функции — GetNumeric. Это имя, которое мы будем использовать на листе, чтобы использовать эту функцию.

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

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

В скобках необходимо указать аргументы.

В нашем примере есть только один аргумент — CellRef.

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

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

Обратите внимание, что функция указана как тип данных «String». Это сообщит VBA, что результат формулы будет иметь тип данных String.

Здесь я могу использовать числовой тип данных (например, Long или Double), но это ограничит диапазон возвращаемых чисел. Если у меня есть строка длиной 20 номеров, которую мне нужно извлечь из общей строки, объявление функции как Long или Double приведет к ошибке (так как число будет вне диапазона). Поэтому я сохранил тип выходных данных функции как String.

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

Третья строка кода объявляет переменную StringLength как тип данных Integer. Это переменная, в которой мы храним значение длины строки, которая анализируется по формуле.

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

Пятая строка назначает длину строки во входном аргументе переменной «StringLength». Обратите внимание, что «CellRef» относится к аргументу, который будет предоставлен пользователем при использовании формулы в рабочей таблице (или при использовании ее в VBA — которую мы увидим позже в этом руководстве).

Шестая, седьмая и восьмая строки являются частью цикла For Next. Цикл выполняется столько раз, сколько символов во входном аргументе. Этот номер задается функцией LEN и присваивается переменной «StringLength».

Таким образом, цикл проходит от «1 до Stringlength».

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

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

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

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

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

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

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

Создание функции в VBA без каких-либо аргументов

В листе Excel у нас есть несколько функций, которые не принимают аргументов (например, RAND, TODAY, NOW).

Эти функции не зависят от входных аргументов. Например, функция TODAY возвращает текущую дату, а функция RAND возвращает случайное число в диапазоне от 0 до 1.

Вы можете создать такую же функцию в VBA.

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

Function WorkbookName() As String
WorkbookName = ThisWorkbook.Name
End Function

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

Эта функция присваивает функции значение «ThisWorkbook.Name», которое возвращается, когда функция используется на рабочем листе.

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

Выше есть одна проблема, хотя.

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

При желании вы можете форсировать пересчет с помощью сочетания клавиш — Control + Alt + F9.

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

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

Function WorkbookName() As String
Application. Volatile True
WorkbookName = ThisWorkbook.Name
End Function

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

Создание функции в VBA с одним аргументом

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

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

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

Function ConvertToUpperCase(CellRef As Range)
ConvertToUpperCase = UCase(CellRef)
End Function

Эта функция использует функцию UCase в VBA для изменения значения переменной CellRef. Затем он присваивает значение функции ConvertToUpperCase.

Поскольку эта функция принимает аргумент, нам не нужно использовать здесь часть Application.Volatile. Как только аргумент изменится, функция автоматически обновится.

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

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

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

Function GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String
Dim Result As String
Dim DelimPosition As Integer
DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1
Result = Left(CellRef, DelimPosition)
GetDataBeforeDelimiter = Result
End Function

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

Обратите внимание, что для каждого аргумента вы можете указать тип данных. В приведенном выше примере «CellRef» был объявлен как тип данных диапазона, а «Delim» был объявлен как тип данных String. Если вы не укажете какой-либо тип данных, VBA считает, что это вариант данных.

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

Затем он проверяет положение разделителя с помощью функции INSTR в VBA. Эта позиция затем используется для извлечения всех символов перед разделителем (используя функцию LEFT).

Наконец, он присваивает результат функции.

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

Function GetDataBeforeDelimiter(CellRef As Range, Delim As String) as String
Dim Result As String
Dim DelimPosition As Integer
DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1
If DelimPosition < 0 Then DelimPosition = Len(CellRef)
Result = Left(CellRef, DelimPosition)
GetDataBeforeDelimiter = Result
End Function

Мы можем дополнительно оптимизировать эту функцию.

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

Это происходит, когда мы указали «CellRef» в качестве типа данных диапазона.

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

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

Код ниже сделает это:

Function GetDataBeforeDelimiter(CellRef, Delim) As String
Dim Result As String
Dim DelimPosition As Integer
DelimPosition = InStr(1, CellRef, Delim, vbBinaryCompare) - 1
If DelimPosition < 0 Then DelimPosition = Len(CellRef)
Result = Left(CellRef, DelimPosition)
GetDataBeforeDelimiter = Result
End Function

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

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

Например, легендарная функция VLOOKUP имеет 3 обязательных аргумента и один необязательный аргумент.

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

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

Например, в функции VLOOKUP, если вы не указали четвертый аргумент, VLOOKUP выполняет приблизительный поиск, а если вы указываете последний аргумент как FALSE (или 0), то он выполняет точное совпадение.

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

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

Функция только с необязательным аргументом

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

Но мы можем создать один с VBA.

Ниже приведен код функции, которая выдаст вам текущую дату в формате dd-mm-yyyy, если вы не вводите никаких аргументов (т.е. оставьте это поле пустым), и в формате «dd mmmm, yyyy», если вы введете что-либо в качестве аргумента (т. е. что угодно, чтобы аргумент не был пустым).

Function CurrDate(Optional fmt As Variant)
Dim Result
If IsMissing(fmt) Then
CurrDate = Format(Date, "dd-mm-yyyy")
Else
CurrDate = Format(Date, "dd mmmm, yyyy")
End If
End Function

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

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

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

Function CurrDate(Optional fmt As Variant)
Dim Result
If IsMissing(fmt) Then
CurrDate = Format(Date, "dd-mm-yyyy")
ElseIf fmt = 1 Then
CurrDate = Format(Date, "dd mmmm, yyyy")
Else
CurrDate = CVErr(xlErrValue)
End If
End Function

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

Функция с необходимыми и необязательными аргументами

Мы уже видели код, который извлекает числовую часть из строки.

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

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

Function GetText(CellRef As Range, Optional TextCase = False) As String
Dim StringLength As Integer
Dim Result As String
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
If TextCase = True Then Result = UCase(Result)
GetText = Result
End Function

Обратите внимание, что в приведенном выше коде мы инициализировали значение «TextCase» как False (смотрите в скобках в первой строке).

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

Создание функции в VBA с массивом в качестве аргумента

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

Вы также можете создать функцию, которая может принимать массив в качестве аргумента. В функциях листа Excel есть много функций, которые принимают аргументы массива, такие как SUM, VLOOKUP, SUMIF, COUNTIF и т.д.

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

Function AddEven(CellRef as Range)
 Dim Cell As Range
 For Each Cell In CellRef
 If IsNumeric(Cell. Value) Then
 If Cell.Value Mod 2 = 0 Then
 Result = Result + Cell.Value
 End If
 End If
 Next Cell
 AddEven = Result
 End Function

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

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

В приведенном выше коде я указал аргумент CellRef как Range (который может принимать массив в качестве входных данных). Вы также можете использовать вариантный тип данных здесь.

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

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

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

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

Примером такой функции рабочего листа является функция SUM. Вы можете предоставить несколько аргументов (например, это):

= SUM (A1, A2: A4, B1: B20)

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

Вы можете создать такую ​​функцию в VBA, указав последний аргумент (или единственный аргумент) в качестве необязательного. Кроме того, этому необязательному аргументу должно предшествовать ключевое слово «ParamArray».

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

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

Function AddArguments(ParamArray arglist() As Variant)
For Each arg In arglist
AddArguments = AddArguments + arg
Next arg
End Function

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

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

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

Function AddArguments(ParamArray arglist() As Variant)
For Each arg In arglist
For Each Cell In arg
AddArguments = AddArguments + Cell
Next Cell
Next arg
End Function

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

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

Создание функции, которая возвращает массив

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

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

Формулы массивов также доступны в виде встроенных функций на листах Excel. Если вы знакомы с формулами массива в Excel, вы знаете, что они вводятся клавишами Control + Shift + Enter (а не только Enter). Вы можете прочитать больше о формулах массива здесь. Если вы не знаете формул массива, не беспокойтесь, продолжайте читать.

Давайте создадим формулу, которая возвращает массив из трех чисел (1,2,3).

Код ниже сделает это.

Function ThreeNumbers() As Variant
Dim NumberValue(1 To 3)
NumberValue(1) = 1
NumberValue(2) = 2
NumberValue(3) = 3
ThreeNumbers = NumberValue
End Function

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

Переменная NumberValue объявлена как массив из 3 элементов. Он содержит три значения и присваивает его функции «Три числа».

Вы можете использовать эту функцию на рабочем листе. Введите эту функцию и нажмите клавиши Control + Shift + Enter (удерживайте клавиши Control и Shift и затем нажмите Enter).

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

= MAX (ThreeNumbers ())

Используйте вышеуказанную функцию с Control + Shift + Enter. Вы заметите, что теперь результат равен 3, так как это самые большие значения в массиве, возвращаемом функцией Max, которая получает три числа в результате нашей пользовательской функции — ThreeNumbers.

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

Function Months() As Variant
Dim MonthName(1 To 12)
MonthName(1) = "Январь"
MonthName(2) = "Февраль"
MonthName(3) = "Март"
MonthName(4) = "Апрель"
MonthName(5) = "Май"
MonthName(6) = "Июнь"
MonthName(7) = "Июль"
MonthName(8) = "Август"
MonthName(9) = "Сентябрь"
MonthName(10) = "Октябрь"
MonthName(11) = "Ноябрь"
MonthName(12) = "Декабрь"
Months = MonthName
End Function

Теперь, когда вы введете функцию = Months () на листе Excel и используете Control + Shift + Enter, она вернет весь массив названий месяцев. Обратите внимание, что вы видите только январь в ячейке, поскольку это первое значение в массиве. Это не означает, что массив возвращает только одно значение.

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

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

=INDEX(Months(),ROW())

Теперь, если у вас много значений, не рекомендуется назначать эти значения одно за другим (как мы делали выше). Вместо этого вы можете использовать функцию Array в VBA.

Поэтому тот же код, в котором мы создаем функцию «Месяцы», станет короче, как показано ниже:

Function Months() As Variant
Months = Array("Январь", "Февраль", "Март", "Апрель", "Май", "Июнь", _
"Июль", "Август", "Сентябрь", "Октябрь", "Ноябрь", "Декабрь")
End Function

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

Обратите внимание, что все функции, созданные выше, возвращают горизонтальный массив значений. Это означает, что если вы выберете 12 горизонтальных ячеек (скажем, A1: L1) и введете формулу = Months () в ячейку A1, вы получите все названия месяцев.

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

Вы можете сделать это, используя формулу TRANSPOSE на листе.

Просто выберите 12 вертикальных ячеек (смежные) и введите приведенную ниже формулу.

Понимание объема пользовательской функции в Excel

Функция может иметь две области действия — Public или Private.

• Общая область означает, что функция доступна для всех листов в рабочей книге, а также для всех процедур (вспомогательных и функциональных) во всех модулях в рабочей книге. Это полезно, когда вы хотите вызвать функцию из подпрограммы (мы увидим, как это делается в следующем разделе).
• Частная область означает, что функция доступна только в том модуле, в котором она существует. Вы не можете использовать его в других модулях. Вы также не увидите его в списке функций на рабочем листе. Например, если имя вашей функции — «Месяцы ()», и вы вводите функцию в Excel (после знака =), она не будет отображать вам имя функции. Однако вы все равно можете использовать его, если вводите название формулы

Если вы ничего не указали, функция по умолчанию является публичной.

Ниже приведена функция, которая является частной функцией:

Private Function WorkbookName() As String
WorkbookName = ThisWorkbook.Name
End Function

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

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

Function WorkbookName() As String
WorkbookName = ThisWorkbook.Name
End Function

Различные способы использования пользовательской функции в Excel

Создав пользовательскую функцию в VBA, вы можете использовать ее по-разному.

Давайте сначала рассмотрим, как использовать функции на листе.

Использование пользовательских функций в рабочих листах

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

Все, что вам нужно сделать, это ввести имя функции, и оно отобразится в intellisense.

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

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

• Перейдите на вкладку «Данные».
• Нажмите «Вставить функцию».

• В диалоговом окне «Вставка функции» выберите «Определено пользователем» в качестве категории. Эта опция отображается только тогда, когда у вас есть функция в редакторе VB (и функция Public).

• Выберите функцию из списка всех общедоступных пользовательских функций.
• Нажмите кнопку ОК

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

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

=UPPER(WorkbookName())

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

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

Если функция Public, она может использоваться в любой процедуре в том же или другом модуле. Если это Private, его можно использовать только в том же модуле.

Ниже приведена функция, которая возвращает имя рабочей книги.

Function WorkbookName() As String 
WorkbookName = ThisWorkbook.Name 
End Function

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

Sub ShowWorkbookName()
MsgBox WorkbookName
End Sub

Вы также можете вызвать функцию из другой функции.

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

Function WorkbookName() As String
WorkbookName = ThisWorkbook.Name
End Function

Function WorkbookNameinUpper()
WorkbookNameinUpper = UCase(WorkbookName)
End Function

Вызов пользовательской функции из других книг

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

Есть несколько способов сделать это:

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

Создание надстройки

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

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

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

• Перейдите на вкладку «Файл» и нажмите «Сохранить как».

• В диалоговом окне «Сохранить как» измените тип «Сохранить как» на .xlam. Имя, которое вы назначаете файлу, будет именем вашей надстройки. В этом примере файл сохраняется с именем GetNumeric.
  • Вы заметите, что путь к файлу, в котором он сохраняется, автоматически изменяется. Вы можете использовать по умолчанию или изменить его, если хотите.

• Откройте новую книгу Excel и перейдите на вкладку Разработчик.
• Выберите параметр «Надстройки Excel».

• В диалоговом окне «Надстройки» найдите и найдите сохраненный файл и нажмите «ОК».

Теперь надстройка была активирована.

Теперь вы можете использовать пользовательские функции во всех книгах.

Сохранение функции в персональной книге макросов

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

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

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

Ссылка на функцию из другой книги

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

Предположим, у вас есть рабочая книга с именем «Рабочая тетрадь с формулой», и она имеет функцию с именем «GetNumeric».

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

=’Workbook with Formula’!GetNumeric(A1)

Приведенная выше формула будет использовать пользовательскую функцию в файле Workbook with Formula и даст вам результат.

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

Использование оператора выхода из VBA

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

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

Function GetNumericFirstThree(CellRef As Range) As Long
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If J = 3 Then Exit Function
If IsNumeric(Mid(CellRef, i, 1)) Then
J = J + 1
Result = Result & Mid(CellRef, i, 1)
GetNumericFirstThree = Result
End If
Next i
End Function

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

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

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

Отладка пользовательской функции с помощью окна сообщения

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

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

Отладка пользовательской функции путем установки точки останова

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

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

Отладка пользовательской функции с помощью Debug. Print в коде

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

Например, в приведенном ниже коде я использовал Debug.Print, чтобы получить значение двух переменных — «j» и «Result».

Function GetNumericFirstThree(CellRef As Range) As Long
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If J = 3 Then Exit Function
If IsNumeric(Mid(CellRef, i, 1)) Then
 J = J + 1
 Result = Result & Mid(CellRef, i, 1)
 Debug.Print J, Result
 GetNumericFirstThree = Result
End If
Next i
End Function

Когда этот код выполняется, он показывает следующее в immediate window.

Встроенные функции Excel против Пользовательской функции VBA

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

• Встроенные функции работают намного быстрее, чем функции VBA.
• Когда вы создаете отчет / панель мониторинга с использованием функций VBA и отправляете его клиенту / коллеге, им не нужно беспокоиться о том, включены макросы или нет. В некоторых случаях клиенты / клиенты пугаются, увидев предупреждение в желтой полосе (которое просто просит их включить макросы).
• Благодаря встроенным функциям Excel вам не нужно беспокоиться о расширениях файлов. Если у вас есть макросы или пользовательские функции в рабочей книге, вам нужно сохранить их в формате .xlsm

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

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

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

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

Ниже приведены инструкции по размещению кода для функции «GetNumeric» в книге.

1. Перейдите на вкладку Разработчик.
2. Нажмите на Visual Basic. Это откроет редактор VB в бэкэнде.
3. На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект для книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и щелкните Project Explorer.
4. Перейти к Вставить и нажмите на модуль. Это вставит объект модуля для вашей книги.                 
5. Скопируйте и вставьте код в окно модуля. 

VBA Excel. Пользовательская функция (синтаксис, компоненты)

Создание пользовательской функции в VBA Excel, ее синтаксис и компоненты. Описание пользовательской функции и ее аргументов. Метод Application.MacroOptions.

Пользовательская функция – это процедура VBA, которая производит заданные вычисления и возвращает полученный результат. Используется для вставки в ячейки рабочего листа Excel или для вызова из других процедур.

Объявление пользовательской функции

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

Компоненты функции

• Static — необязательное ключевое слово, указывающее на то, что значения переменных, объявленных в функции, сохраняются между ее вызовами.
• Имя — обязательный компонент, имя пользовательской функции.
• СписокАргументов — необязательный компонент, одна или более переменных, представляющих аргументы, которые передаются в функцию. Аргументы заключаются в скобки и разделяются между собой запятыми.
• Операторы — необязательный компонент, блок операторов (инструкций).
• Имя = выражение — необязательный* компонент, присвоение имени функции значения выражения или переменной. Обычно, значение присваивается функции непосредственно перед выходом из нее.
• Exit Function — необязательный компонент, принудительный выход из функции, если ей уже присвоено окончательное значение.

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

Видимость функции

Видимость пользовательской функции определяется необязательными ключевыми словами Public и Private, которые могут быть указаны перед оператором Function (или Static, в случае его использования).

Ключевое слово Public указывает на то, что функция будет доступна для вызова из других процедур во всех модулях открытых книг Excel. Функция, объявленная как Public, отображается в диалоговом окне Мастера функций.

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

Если ключевое слово Public или Private не указано, функция считается по умолчанию объявленной, как Public.

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

Пример пользовательской функции

Для примера мы рассмотрим простейшую пользовательскую функцию, которой в следующем параграфе добавим описание. Называется функция «Деление», объявлена с типом данных Variant, так как ее возвращаемое значение может быть и числом, и текстом. Аргументы функции – Делимое и Делитель – тоже объявлены как Variant, так как в ячейках Excel могут быть числовые значения разных типов, и функция IsNumeric тоже проверяет разные типы данных и требует, чтобы ее аргументы были объявлены как Variant.

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

• Если делимое или делитель не являются числом, функция возвращает значение: «Ошибка: Делимое и Делитель должны быть числами!», и производится принудительный выход из функции оператором Exit Function.
• Если делитель равен нулю, функция возвращает значение: «Ошибка: деление на ноль!», и производится принудительный выход из функции оператором Exit Function.

Если проверяемые условия не выполняются (возвращают значение False) производится деление чисел и функция возвращает частное (результат деления).

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

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

Добавление описания функции

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

• Запустите Мастер функций, посмотрите, как отображается имя нужной функции и закройте его.
• Откройте список макросов и в поле «Имя макроса» впишите имя пользовательской функции.
• Нажмите кнопку «Параметры» и в открывшемся окне добавьте или отредактируйте описание.
• Нажмите кнопку «OK», затем в окне списка макросов – «Отмена». Описание готово!

Добавление описания на примере функции «Деление»:

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

Описание функции «Деление» в диалоговом окне Мастера функций «Аргументы функции»:

Описание пользовательской функции в окне «Аргументы функции»

С помощью окна «Список макросов» можно добавить описание самой функции, а ее аргументам нельзя. Но это можно сделать, используя метод Application.MacroOptions.

Метод Application.MacroOptions

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

Пример кода с методом Application.MacroOptions:

• ИмяПодпрограммы — любое уникальное имя, подходящее для наименования процедур.
• ИмяФункции — имя функции, параметры которой добавляются или изменяются.
• Описание функции — описание функции, которое добавляется или изменяется.
• Название категории — название категории в которую будет помещена функция. Если параметр Category отсутствует, пользовательская функция будет записана в раздел по умолчанию – «Определенные пользователем». Если указанное Название категории соответствует одному из названий стандартного списка, функция будет записана в него. Если такого Названия категории нет в списке, будет создан новый раздел с этим названием и функция будет помещена в него.
• “Описание 1”, “Описание 2”, “Описание 3”, … — описания аргументов в том порядке, как они расположены в объявлении пользовательской функции.

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

Сейчас с помощью метода Application.MacroOptions попробуем изменить описание пользовательской функции «Деление» и добавить описания аргументов.

После однократного запуска этой подпрограммы получаем следующий результат:

Новое описание пользовательской функции и ее второго аргумента

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

5 полезных функций Excel для финмоделирования

Excel уже давно доказал свою ценность в работе с любого рода математическими данными. Но помимо обычных инструментов, о которых знает любой обыватель, программа скрывает в себе и более широкие возможности. К слову, при сложном моделировании или анализе больших финансовых моделей, состоящих из множества вкладок либо требующих длительной прокрутки на сотни ячеек, чаще всего используются «Элементы управления формы». Освоить другие полезные функции программы на продвинутом уровне можно на онлайн-курсе «Excel для карьеры и поиска работы» от Changellenge >> ToolKit.

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

Если вы нечасто пользуетесь Excel, то вряд ли сразу найдете вкладку с нужными элементами. В стандартном виде программы ее нет. Чтобы включить, нажмите на кнопку Office или «Файл», зайдите в параметры Excel, в разделе «Основные» поставьте галочку у строки «Показывать вкладку “Разработчик”». Затем, зайдя во вкладку «Разработчик» и нажав на кнопку «Вставить», вы увидите все элементы управления формы.

Если у вас установлена версия Microsoft Office 2013, вкладку «Разработчик» вы найдете по следующему алгоритму. Нажмите на кнопку «Файл», откройте вкладку «Параметры» и выберите пункт «Настроить ленту». В списке «Основные вкладки» поставьте галочку у элемента «Разработчик».

Если вы пользуетесь версией программы для macOS, войти в меню «Разработчик» будет еще проще. В меню Excel выберите пункт «Параметры», затем «Представление» и поставьте в разделе «На ленте показывать» флажок напротив «Вкладка “Разработчик”».

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

1. Поле со списком

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

2. Список

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

3. Переключатель

«Переключатель», так же как «Список» и «Поле со списком», используется для предоставления пользователям возможности выбора из нескольких вариантов. От одного переключателя на листе толку нет, ведь он, как следует из названия, должен что-то переключать. Этот инструмент может пригодиться, например, при составлении плана продаж. Можно, скажем, рассчитать его по трем сценариям: базовому, умеренному и оптимистичному.

4. Флажок

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

Более продвинутые пользователи, впрочем, знают, что диапазон применения «Флажка» не ограничивается только этим. С помощью различных формул можно отображать разные надписи. Также можно проводить различные вычисления, скажем выводить сумму одного из двух диапазонов (например, A1 : A5 и B1 : B5). А еще «Флажок» часто используют для того, чтобы включать и выключать заливку ячейки.

5. Счетчик

«Счетчик» предоставляет возможность производить тонкую настройку какого-либо параметра путем его изменения с некоторым настраиваемым шагом (1, 2, 3…). Этот виджет может пригодиться, если перед вами модель, зависящая от определенного параметра, а ваша задача в том, чтобы проверить, как ведут себя показатели в зависимости от него. Вместо того чтобы каждый раз вводить новое значение в ячейку, просто нажимайте на кнопку счетчика. Можно даже нажать на счетчик, удерживая левую кнопку мыши, чтобы быстро прокрутить цифры в ячейке. Значение показателя может использоваться в дальнейшем, например, с помощью функций =СМЕЩ(), =ВЫБОР(), =ЕСЛИ(). В английской версии программы эти функции называются =OFFSET(), =CHOOSE() и =IF().

Хотите больше узнать о работе в Excel?

Мы создали онлайн-курс, на котором учим использовать самые важные функции этой программы на реальных бизнес-задачах. Вебинары от экспертов с опытом работы в Big3 и Big4, много практики и возможность получить карьерные рекомендации — подробности тут.

Чтобы проверить, насколько хорошо вы знаете Excel, пройдите тестирование.

Пройти тест >>

Теги

Самые нужные функции в excel

10 полезных функций программы Microsoft Excel

​Смотрите также​ и выберите инструмент:​ будем рассматривать практическое​Применение функции СМЕЩ​ Excel.​МОДА.ОДН​ аргументу проверяем условие,​ диапазонов ячеек в​ информационные сообщения пользователям​ данных в Excel,​ столбцам, Верхний и​ приведенных примеров вы​

​ каждая рабочая книга​Каждый раздел содержит несколько​

Функция ВПР

​ введете в ячейки,​ условиям, или одному​ различного типа диаграмм.​Программа Microsoft Excel способна​ «Формулы»-«Логические»-«ЕСЛИ».​ применение функций.​ для динамического получения​Примеры использования функции​. Форма записи​ по третьему –​ Excel. С помощью​ Вашей программы.​ чтобы видеть только​ нижний регистр, Удалить​ узнаете, как предоставить​ Excel содержит три​ глав. В каждой​ расположенные у границ​ из нескольких условий.​Чтобы создать диаграмму, нужно​ в значительной мере​Заполняем аргументы функции в​Как найти среднее арифметическое​ ссылки на ячейку​ ЗАМЕНИТЬ при различных​МОДА​

​ находим среднее.​ данных функций Вы​Объекты Workbook и Worksheet:​ записи, удовлетворяющие определённому​ ненужные символы, Сравнение​

​ общий доступ к​ листа (в Excel​ главе вы найдете​ таблицы, какие-то данные,​Урок: Применение функции «ЕСЛИ»​ выделить набор ячеек​

​ облегчить пользователю работу​ диалоговом окне как​

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

​ число в Excel?​ в диапазоне. Создание​ поставленных задачах для​также поддерживается в​Допустим, в таблице ниже​ можете вычислить наибольшее,​ Узнайте больше об​ критерию.Примеры: Числовые и​ текста, Функции НАЙТИ​ книге Excel.​ 2010 и более​ ознакомительный урок, раскрывающий​ то они будут​ в Microsoft Excel​ с данными, которые​ с таблицами и​ показано на рисунке​

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

​ наименьшее или среднее​ объектах Workbook и​

Создание диаграмм

​ текстовые фильтры, Фильтры​ и ПОИСК, Функции​Примеры: Лист Excel в​ ранних версиях).​ основные понятия и​ автоматически включены в​С помощью макросов, в​ вы хотите визуально​ числовыми выражениями, автоматизировав​ и жмем ОК​ как показано на​ интерфейсом формы. Автоматическое​

​ в ячейках таблиц.​Как известно, категория​ стоимости лекарств в​ значение, подсчитать количество​ Worksheet в VBA.​ по дате, Расширенный​ ПОДСТАВИТЬ и ЗАМЕНИТЬ.​ Word, Сохранить в​Примеры: Масштаб, Разделяем лист,​ положения, и несколько​ эту таблицу. Кроме​

​ программе Microsoft Excel​ отобразить. Затем, находясь​ её. Этого удается​ (обратите внимание вторая​ рисунке:​ обновление итоговых данных​

​ Как изменить несколько​Статистические​ городе. В одной​ ячеек, содержащих заданную​Объект Range: Объект Range​ фильтр, Форма данных,​

​Функции поиска и ссылок:​ PDF, Общий доступ​

Формулы в EXCEL

​ Закрепление областей, Группировка​ простых и понятных​ того, при прокрутке​ можно записывать выполнение​ во вкладке «Вставка»,​ достичь с помощью​ ссылка $A$4 -​В ячейках D5 и​ при заполнении таблицы.​ символов в текстовой​в Excel содержит​ аптеке лекарство стоит​

​ информацию, и т.д.​ – это ячейка​ Удаляем дубликаты, Структурирование​ Узнайте всё о​ к книге, SkyDrive,​ листов, Консолидация, Просмотр​ примеров. Хотите узнать​ вниз, шапка таблицы​ определенных действий, а​ выбрать на ленте​ инструментария данного приложения,​ абсолютная):​ E5 введем функции,​Примеры работы функции EXP​ строке?​ более 100 самых​ дороже, в другой​Данная категория содержит более​ (или ячейки) Вашего​

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

​Функцию из D7 копируем​ которые помогут получить​

Функция «ЕСЛИ»

​ для возведения числа​Примеры использования функции СТРОКА​ разноплановых функций. Но,​ дешевле. Чтобы посчитать​ 100 самых различных​ листа. Это самый​Условное форматирование: Условное форматирование​ работы со ссылками​ данных из Access,​ правописания.​ больше? Немедленно приступайте​

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

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

​ важный объект VBA.​ в Excel позволяет​

Макросы

​ и массивами, таких​ Microsoft Query, Импорт​Форматирование ячеек: При форматировании​ к изучению Microsoft​Урок: Создание таблицы в​ экономит время на​ подходящим для достижения​ наиболее полезные функции​Описание аргументов функции: =ЕСЛИ().​

​ успеваемости по урокам​Пример применения функции​ Excel.​ львиная доля этих​ среднем по городу,​

​ часть из которых​Переменные: В этом разделе​ выделить ячейки определённым​ как ВПР, ГПР,​

​ и экспорт текстовых​ ячеек в Excel,​

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

​ Excel вместе с​ Microsoft Excel​ выполнении большого количества​ поставленных целей.​ программы Microsoft Excel.​ В ячейке A4​ Английского и Математики.​ EXP в лабораторных​Полезные формулы с​ функций практически не​ воспользуемся следующей формулой:​

​ предназначена исключительно для​ Вы научитесь объявлять,​ цветом в зависимости​ ПОИСКПОЗ, ИНДЕКС и​ файлов, XML.​ мы изменяем лишь​ Офис-Гуру! С нами​С помощью функции подбора​ однотипной работы.​Более точная настройка диаграмм,​Скачать последнюю версию​ у нас количество​ Ячейка E4 не​ исследованиях и анализах.​ использованием функции СТРОКА​

​ применяется, а особенно​

​Если требуется соблюсти несколько​ статистических расчетов и​

«Умная» таблица

​ инициализировать и отображать​ от содержащихся в​ ВЫБОР.​Защита: Зашифруйте файл Excel​ внешний вид содержимого,​ вам будет гораздо​ параметров, можно подобрать​Макросы можно записывать, просто​ включая установку её​ Excel​ всех студентов, а​

​ имеет значения, поэтому​ Применение функции EXP​ на готовых примерах:​ начинающими пользователями. В​ условий, то всегда​ обычному рядовому пользователю​ переменную в VBA.​ них значений.Примеры: Управление​Примеры: Налоговые ставки, Функция​ при помощи пароля.​ не изменяя самого​ проще!​

​ исходные данные, исходя​ включив запись своих​ наименования и наименования​Одной из самых востребованных​ в ячейке D6​ результат будет вычислен​ для финансовых расчетов​

​ удаление повторяющихся значений,​ этом уроке мы​ можно применить статистическую​ покажется темным лесом.​Выражение «If Then»: Используйте​ правилами, Гистограммы, Цветовые​ СМЕЩ, «Левый” поиск,​ Только зная пароль,​ значения.​Данный курс по Excel​ из конечного нужного​ действий в программе,​ осей, производится в​ функций в программе​ и E6 –​

​ из 2 оценок.​ при анализе вложений​

Подбор параметра

​ нумерация ячеек в​ постарались познакомить Вас​ функцию​ Мы же в​ выражение «If Then»​

​ шкалы, Наборы значков,​ Двумерный поиск, Поиск​ можно будет открыть​Примеры: Десятичные знаки, Денежный​ – это перевод​ для вас результата.​ через соответствующую кнопку​ группе вкладок «Работа​ Microsoft Excel является​ количество оценок. Функция​Перейдите в ячейку D5.​ инвестиций на депозитные​

​ таблице, выбор значения​ только с самыми​СРЗНАЧЕСЛИМН​ рамках этого урока​ в VBA, чтобы​ Новое правило, Поиск​ максимального значения, Функция​ этот файл.​ против финансового, Форматы​ очень популярного англоязычного​Для того, чтобы использовать​ на ленте.​ с диаграммами».​ ВПР (VLOOKUP). С​

​ ЕСЛИ() проверяет, совпадают​Выберите инструмент из выпадающего​

Функция «ИНДЕКС»

​ счета в банк.​ по условию.​ популярными статистическими функциями​, которая позволяет считать​ рассмотрим самые полезные​ выполнять строки кода,​ дубликатов, Закрашивание чередующихся​ ДВССЫЛ.​Примеры: Защита книги, Защита​

​ даты и времени,​ сайта-самоучителя – excel-easy.com,​

​ эту функцию, нужно​Также, запись макросов можно​Одним из видов диаграмм​ помощью данной функции,​ ли показатели D6​ списка: «Главная»-«Сумма»-«Среднее». В​Примеры формул с функциями​Функция ВЕРОЯТНОСТЬ для расчета​

​ Excel, которые Вы​

lumpics.ru>

300 примеров по Excel

​ среднее арифметическое ячеек,​ и распространенные функции​ если встречается определённое​ строк, Сравнение двух​Финансовые функции: Этот раздел​ листа, Блокировка ячеек,​ Дроби, Текст в​ который посещают десятки​​ находиться во вкладке​ производить, используя язык​ являются графики. Принцип​​ можно значения одной​ и E6 со​ данном выпадающем списке​ ИНДЕКС и ПОИСКПОЗ​ вероятности событий в​ рано или поздно​ удовлетворяющих двум и​

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

​ число, Число в​ тысяч человек ежедневно!​ «Данные». Затем, требуется​ разметки Visual Basic,​ построения их тот​ или нескольких таблиц,​ значением A4. Если​ находятся часто используемые​ СУММПРОИЗВ в Excel.​ Excel.​ сможете применить на​ более критериям.​В рамках данной статьи​Цикл: Циклы – это​ Чеклист.​

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

Введение

​ математические функции.​Примеры использования массивных​Примеры расчетов процента​

​ практике. Надеюсь, что​Статистическая функция​ мы не будем​ мощнейшая из техник​Диаграммы: Простейшая диаграмма в​ Excel.​ окончательную.​ форматы, Формат по​ вас он не​ «Анализ «что если»»,​Урок: Применение макросов в​

1. ​ у остальных типов​ При этом, поиск​ ответ ДА, а​Диапазон определяется автоматически, остается​ функций ИНДЕКС и​ вероятности возникновения события​ данный урок был​МАКС​

   ​ затрагивать такие популярные​ программирования. Цикл в​ Excel может быть​Примеры: Кредиты различной длительности,​Узнайте, как функции в​ образцу, Стили ячеек,​

2. ​ оставит равнодушными!​ которая располагается в​ Microsoft Excel​ диаграмм.​ производится только в​ если нет –​

   ​ только нажать Enter.​ ПОИСКПОЗ, СУММПРОИЗВ как​ путем простых вычислений​ для Вас полезен.​возвращает наибольшее значение​

Основы

​ статистические функции Excel,​ VBA позволяет Вам​ более красноречивой, чем​

1. ​ Инвестиции и аннуитет,​ Excel помогают сэкономить​ Темы.​Этот раздел предназначен для​ блоке инструментов «Работа​Для того, чтобы выделить​

   ​Урок: Применение диаграмм в​ первом столбце таблицы.​

2. ​ ответ НЕТ.​Функцию, которую теперь содержит​ альтернатива формулам массива.​ с использованием статистической​ Удачи Вам и​

   ​ в диапазоне ячеек:​ как​ обрабатывать диапазон ячеек​ таблица полная чисел.​

3. ​ Расчёт сложных процентов,​ время. Если Вы​Найти и выделить: В​ пользователей, не знакомых​ с данными». Потом,​ определенные данные в​ Microsoft Excel​ Тем самым, при​В логическом выражении для​ ячейка D5, скопируйте​

   ​ Формулы для поиска​ функции ВЕРОЯТНОСТЬ.​ успехов в изучении​Статистическая функция​СЧЕТ​

4. ​ снова и снова,​ Вы увидите: диаграммы​ График погашения кредита,​ только начинаете знакомство​ этой главе Вы​ с Excel.​

   ​ выбрать в появившемся​ таблице применяется функция​Для работы с числовыми​ изменении данных в​ удобства применяется 2​ в ячейку E5.​ значений по столбцам​Примеры функции ПОДСТАВИТЬ для​ Excel.​

5. ​МИН​и​ написав для этого​ – это не​ Амортизация.​ с функциями в​

   ​ научитесь использовать два​Microsoft Excel – одно​ списке пункт «Подбор​ условного форматирования. С​ данными в программе​

6. ​ таблице-источнике, автоматически формируются​ типа ссылок: относительная​Функция среднее значение в​ таблицы.​ замены текста в​Автор: Антон Андронов​возвращает наименьшее значение​СЧЕТЕСЛИ​ всего лишь несколько​ сложно.Примеры: Гистограмма, График,​

   ​Статистические функции: Здесь Вы​ Excel, то рекомендуем​ очень полезных инструмента​ из самых широко​

7. ​ параметра…».​ помощью этого инструмента,​ Microsoft Excel удобно​ данные и в​ и абсолютная. Это​ Excel: =СРЗНАЧ() в​

   ​Функция РИМСКОЕ в Excel​ ячейке Excel.​Примеры использования функций​ в диапазоне ячеек:​, для них подготовлен​

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

   ​ Excel: Найти и​ используемых приложений в​

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

   ​ для перевода арабских​Функция ПОДСТАВИТЬ предназначена​ в формулах. Описание​Возвращает n-ое по величине​ отдельный урок.​Ошибки макросов: Этот раздел​ диаграмма, Диаграмма с​ полезных статистических функций​

10. ​ с главой Введение​ заменить и Перейти.​ истории. Сотни миллионов​ В поле «Установить​ выделения ячеек. Само​ С их помощью​ которой могут выполняться​ формулу без ошибок​ текст. Так же​ чисел в римские.​ для работы с​

    ​ предназначения и назначения​ значение из массива​Статистическая функция​ научит справляться с​ областями, Точечная диаграмма,​ Excel.​ в формулы и​Примеры: Особенности инструмента «Найти»,​ людей во всём​

11. ​ в ячейке» вы​ условное форматирование можно​ можно производить различные​ отдельные расчеты. Например,​ в результатах ее​

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

Функции

​СРЗНАЧ​ ошибками макросов в​ Ряды данных, Оси,​Примеры: Отрицательные числа на​ функции.​ Удаляем пустые строки,​ мире пользуются Microsoft​ должны указать ссылку​ выполнить в виде​ арифметические действия с​

1. ​ данные из таблицы,​ вычисления.​ ячейку. Но если​ РИМСКОЕ в переводе​ используется, когда необходимо​ функциях.​ на рисунке ниже​возвращает среднее арифметическое​ Excel.​

   ​ Лист диаграммы, Линия​ нули, Случайные числа,​СЧЁТ и СУММ: Чаще​ Отличия по строкам,​ Excel. Excel умеет​

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

   ​Примечание. Закладка «Формулы» предоставляет​ в ячейке будет​

3. ​ арабских чисел в​ подставлять значения в​Функция ГРАДУСЫ для выполнения​ мы нашли пятое​ своих аргументов.​Операции со строками: В​ тренда, Предел погрешностей,​ Функция РАНГ, ПЕРСЕНТИЛЬ​

   ​ всего в Excel​ Копируем только видимые​ работать с данными​

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

   ​Данная функция может принимать​ этом разделе Вы​ Спарклайны, Комбинированная диаграмма,​ и КВАРТИЛЬ, ПРЕДСКАЗ​ используются функции, которые​ ячейки, Строка поиска.​

5. ​ любого типа и​ В поле «Значение»​Для того, чтобы перейти​ деление, возведение в​ товары, могут использоваться​

   ​ наиболее часто используемым​ результат естественно измениться.​ переводить, преобразовать, конвертировать​ ячейки.​ Excel.​ из списка.​ до 255 аргументов​ найдёте сведения о​ Диаграмма-спидометр, Диаграмма-термометр, Диаграмма​

6. ​ и ТЕНДЕНЦИЯ.​ подсчитывают количество и​Шаблоны: Вместо того, чтобы​ выполнять финансовые, математические​ должен быть указан​ к условному форматированию,​ степень извлечение корня,​ для расчета показателей​

   ​ функциям. Больше можно​В Excel еще существует​ и заменить арабские​Примеры функции ТЕНДЕНЦИЯ в​Полезные примеры как​

7. ​Чтобы убедиться в этом,​ и находить среднее​ самых важных функциях​ Ганта, Диаграмма Парето.​

   ​Округление: В этом разделе​ сумму. Вы можете​ создавать рабочую книгу​ и статистические вычисления.​ конечный результат, который​

8. ​ нужно, находясь во​ и т.д.​ в таблице, об​ получить, вызвав окно​

   ​ функция =СРЗНАЧА() –​ цифры римскими?​ Excel для прогнозирования​ делать геометрические расчеты​ можно отсортировать числа​

9. ​ сразу в нескольких​ для работы со​Сводные таблицы: Сводные таблицы​ описаны три самых​ посчитать количество и​ Excel с чистого​Диапазон: Диапазон в Excel​

   ​ вы хотите получить.​ вкладке «Главная», выделить​Для того, чтобы применить​

10. ​ объёме закупок в​ «Мастер функций» нажав​ среднее значение арифметическое​Функция КПЕР для расчета​ данных.​

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

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

    ​ формулу, нужно в​ денежном выражении.​ на кнопку «Вставить​ число. Она отличается​ количества периодов погашений​Примеры составления простого,​ функцию ГРАДУСЫ: расчет​Возвращает n-ое наименьшее значение​ ячейках:​ VBA.​ самых мощных инструментов​ округления чисел в​

Анализ данных

​ по одному или​ создать рабочую книгу​ из двух или​ значения ячейки» нужно​

1. ​ вы собираетесь отформатировать.​ ячейке, куда планируется​ВПР запускается путем вставки​ функцию» вначале строки​ от предыдущей тем,​ в Excel.​ но эффективного прогнозирования​ траектории движения, вычисление​
2. ​ из массива числовых​Если в рассчитываемом диапазоне​Дата и время: Научитесь​ Excel. Сводная таблица​ Excel. Это функции​ по нескольким критериям.​ на базе шаблона.​ более ячеек. В​ указать координаты ячейки​ Далее, в группе​
3. ​ выводить результат, поставить​ оператора «ВПР» из​ формул. Или нажать​ что:​Примеры использования функции​ тенденции по собранным​ оборотов электродвигателя и​ данных. Например, на​ встречаются пустые или​ работать с датой​ позволит выделить суть​ ОКРУГЛ, ОКРУГЛВВЕРХ и​Примеры: Подсчёт вхождений текста,​
4. ​ Существует множество бесплатных​ этой главе Вы​ с корректируемым значением.​ инструментов «Стили» нажать​ знак «=». После​ Мастера функций в​ комбинацию клавиш SHIFT+F3.​=СРЗНАЧ() – пропускает ячейки​ КПЕР для расчетов​ данным статистики за​ др.​ рисунке ниже мы​ содержащие текст ячейки,​ и временем в​ из огромного набора​ ОКРУГЛВНИЗ.​
5. ​ Подсчёт логических значений,​ шаблонов, которые так​ найдёте обзор некоторых​Урок: Применение подбора параметров​ на кнопку, которая​ этого, вводится сама​ ту ячейку, где​
6. ​Функция =ОКРУГЛ() более​ которые не содержат​ сроков погашений кредита​ определенный период с​
7. ​Примеры работы функций РУБЛЬ​ нашли четвертое наименьшее​ то они игнорируются.​ VBA.​
8. ​ данных.​Примеры: Отбрасываем десятичные знаки,​ Подсчёт пустых и​ и просятся, чтобы​ очень важных операций​ в Microsoft Excel​ так и называется​ формула, которая может​ данные должны отображаться.​
9. ​ точна и полезнее​ чисел;​ и вычисления реальной​ помощью использования функции​ РУБЛЬ.ДЕС и РУБЛЬ.ДРОБЬ​ значение из списка.​

VBA

​ В примере ниже​События: События – это​Таблицы: Таблицы нужны для​ Ближайшее кратное, Четные​ непустых ячеек, Функция​ их использовали.​ с диапазонами.​Возможности, которые предоставляет функция​

1. ​ «Условное форматирование». После​ состоять из математических​В появившемся, после запуска​ чем округление с​=СРЗНАЧА() – пропускает только​ суммы долга с​ ТЕНДЕНЦИЯ. 1 2​ в Excel.​
2. ​Если отсортировать числа в​ среднее ищется по​ действия, совершаемые пользователями,​ того, чтобы выполнять​ и нечетные.​ СУММПРОИЗВ.​
3. ​Примеры: Календарь, Бюджет, Планировщик​Примеры: Последовательность Фибоначчи, Пользовательские​ «ИНДЕКС», в чем-то​ этого, нужно выбрать​
4. ​ знаков, чисел, и​ этой функции окне,​ помощью формата ячеек.​ пустые ячейки, а​ учетом переплаты и​
5. ​ 3 4 5​Функции РУБЛЬ, РУБЛЬ.ДЕС​ порядке возрастания, то​ четырем ячейкам, т.е.​
6. ​ которые запускают выполнение​ анализ имеющихся данных​Ошибки в формулах: Эта​Логические функции: Узнайте, как​ питания, Счет-фактура, Автоматизированный​ списки, Примечания, Скрытие​
7. ​ близки к возможностям​ тот вариант форматирования,​ адресов ячеек. Для​ нужно указать адрес​ В этом легко​ текстовые значения воспринимает​ процентной ставки.​ 6 7 8​ и РУБЛЬ.ДРОБЬ предназначены​
8. ​ все станет гораздо​(4+15+11+22)/4 = 13​ кода VBA.​ быстро и легко.​
9. ​ глава научит Вас​ пользоваться логическими функциями​ счет-фактура, Шаблоны по​ строк и столбцов,​ функции ВПР. Она​ который считаете наиболее​ того, чтобы указать​
10. ​ ячейки или диапазона​ убедиться на практике.​ как 0.​Примеры работы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ​
11. ​ 9 10 11​ для финансовых расчетов.​ очевидней:​Если необходимо вычислить среднее,​
12. ​Массив: Массив – это​Анализ «Что-если»: Анализ «Что-если»​ справляться с некоторыми​ Excel, такими как​ умолчанию.​ Пропускать пустые ячейки,​ также позволяет искать​ подходящим.​
13. ​ адрес ячейки, из​ ячеек, откуда данные​Создайте исходную таблицу так​​ в Excel.​ 12 13 14​ Вторые две активно​
14. ​Статистическая функция​ учитывая все ячейки​ группа переменных. В​ в Excel позволяет​ распространёнными ошибками в​ ЕСЛИ, И, ИЛИ.​Проверка данных: Используйте проверку​ Транспонирование, Объединение и​ данные в массиве​
15. ​Форматирование будет выполнено.​ которой берутся данные​ будут подтягиваться.​ как показано на​В ячейки D6 и​Примеры использования функции​
16. ​ 15 16 17​ применяются для финансового​МЕДИАНА​ диапазона, то можно​

​ VBA Вы можете​

office-guru.ru>

Статистические функции Excel, которые необходимо знать

​ подставлять различные значения​​ формулах Excel.​​Примеры: Вложенные функции ЕСЛИ,​ данных в Excel​ пересечение.​ значений, и возвращать​Урок: Применение условного форматирования​ для расчета, достаточно​Урок: Применение функции ВПР​ рисунке:​ E6 нашей таблицы​ ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ для выборки​

​Сборник интересных функций​ анализа ценных бумаг.​возвращает медиану из​ воспользоваться статистической функцией​ сослаться на определённую​ (сценарии) в формулы.​Примеры: ЕСЛИОШИБКА, ЕОШИБКА, Циклическая​ Игра «Бросить кости».​ и будьте уверены,​Формулы и функции: Формула​ их в указанную​ в Microsoft Excel​ кликнуть по ней​

​ в Microsoft Excel​Ячейку B2 отформатируйте так,​ введите функцию подсчета​ отдельных значений из​ с практическими примерами,​​Функция ФИКСИРОВАННЫЙ для округления​​ заданного массива числовых​​СРЗНАЧА​​ переменную (элемент) массива,​Поиск Решения: В арсенале​

СРЗНАЧ()

​ ссылка, Зависимости формул,​​Ссылки на ячейки: Ссылка​​ что пользователи введут​ – это выражение,​

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

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

​ на ячейку –​ в ячейку только​ которое вычисляет значение​Синтаксис данной функции выглядит​​ что таблицу, просто​​ координаты появится в​ программы Excel является​ только 2 знака​ Функция позволит нам​​ Формулы для работы​​ синтаксиса и параметров.​

​ Excel.​​ число, которое является​​ среднее ищется уже​ имя массива и​ который называется «Поиск​Формулы массива: Изучив эту​

СРЗНАЧЕСЛИ()

​ это очень важный​ подходящее значение.​ ячейки. Функции –​ следующим образом: «ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца)».​ начерченную карандашом, или​​ ячейке для вывода​​ создание сводных таблиц.​ после запятой. Это​ узнать количество поставленных​

​ со сводными таблицами.​Примеры формул с использованием​Функция Фиксированный идеально​ серединой числового множества.​ по 6 ячейкам,​ числовой индекс элемента.​ Решения». Он использует​ главу, Вы будете​​ элемент Excel. Поймите​​Примеры: Отбросить недопустимые даты,​ это предопределённые формулы,​Это далеко не полный​ при помощи границы,​ результата.​ С помощью данной​ можно сделать с​

​ оценок.​Функция ЭФФЕКТ для расчета​ функций ИЛИ И​ подходит для трейдеров,​ Если в списке​ т.е.​Function и Sub: В​ различные методы анализа​ понимать, что такое​ разницу между относительной,​

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

МАКС()

​ ЕСЛИ в Excel.​​ брокеров и финансовых​​ нечетное количество значений,​(4+15+11+22)/6 = 8,6(6)​

МИН()

​ Visual Basic for​​ вычислений для поиска​​ формулы массива в​ абсолютной и смешанной​

НАИБОЛЬШИЙ()

​ дублирования записей, Коды​Примеры: Процентное изменение, Имена​ которые доступны в​ воспринимает, как простую​ можно использовать и​ данные из других​ «Формат ячеек» или​

​ и выберите инструмент​ в Excel .​Примеры логических функций​

НАИМЕНЬШИЙ()

​ аналитиков, которым нужно​ то функция возвращает​.​ Applications функция (Function)​ наилучших решений для​ Excel. Одноячеечные формулы​

​ ссылками, и Вы​ продуктов, Выпадающий список,​ в формулах, Динамический​ программе Microsoft Excel.​

МЕДИАНА()

​ область ячеек. Для​​ в качестве обычного​​ таблиц по различным​ инструментом расположенном на​ из выпадающего списка:​Примеры работы функции​ ЕСЛИ, И, ИЛИ​ быстро и качественно​ то, что находится​Статистическая функция​ может возвращать значение,​ задач оптимизации всех​ массива позволяют выполнять​ наверняка добьётесь успеха!​ Зависимые выпадающие списки.​ именованный диапазон, Параметры​

​ Мы остановили внимание​ того, чтобы этот​ калькулятора. Для этого,​ критериям, а также​

​ закладке «Главная»-«Уменьшить разрядность»​ «Главная»-«Сумма»-«Число».​ ЭФФЕКТ при расчете​ в формулах для​

МОДА()

​ округлять котировки цен​ ровно по середине.​СРЗНАЧ​

​ в то время​ видов.​ сразу серию вычислений​Примеры: Копируем точную формулу,​

​Сочетания клавиш: Сочетания клавиш​​ вставки, Строка состояния,​​ только на самых​ набор данных воспринимался​ в строке формул​ производить различные расчеты​В столбце C проставьте​​На этот рас нам​​ эффективных годовых процентных​​ выборки значений при​​ по ценным бумагам​ Если же количество​

​может использовать в​​ как процедура (Sub)​​Пакет анализа: Пакет анализа​ в одной ячейке.​ 3D ссылка, Создание​ позволяют увеличивать скорость​ Быстрые операции.​ популярных, и наиболее​ именно как таблица,​ или в любой​ с ними (суммировать,​ формулу вычитания 1,25​ не подходит автоматическое​ ставок по разным​ условии. Как использовать​ и курсам валют.​ значений четное, то​ качестве своих аргументов​ – не может.​ – это надстройка​Примеры: Подсчёт ошибок, Подсчёт​ внешней ссылки, Гиперссылки.​ работы, используя клавиатуру​

​Этот раздел даёт базовые​

office-guru.ru>

Обзоры использования самых популярных функций в Excel

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

Интересные примеры часто используемых функций

​ определение диапазона ячеек,​ банковским вкладам и​ функции И ИЛИ​
​ ​ функция возвращает среднее​ математические операторы и​Объект Application: Главным в​ Excel, которая предоставляет​ уникальных значений, Подсчёт​Дата и время: Чтобы​​ вместо мыши.​ понятия по работе​Автор: Максим Тютюшев​
​Делается это просто. Для​ математические выражения после​ т.д.), а результаты​ B: =B-1,25.​ поэтому его нужно​ депозитным счетам с​​ ЕСЛИ в формуле?​Функции СИМВОЛ ЗНАК ТИП​ для двух чисел.​
​ различные функции Excel:​ иерархии объектов является​ инструменты для анализа​ с критерием «ИЛИ»,​ ввести дату в​Примеры: Функциональные клавиши, Подсказки​ в Excel.​Ищите примеры работы в​ начала, выделяем нужный​​ знака «=».​ выводить в отдельную​Теперь вы знаете, как​ исправить на D2:D4.​
​ простыми или сложными​Примеры расчетов функций КОВАРИАЦИЯ.В​ в Excel и​Например, на рисунке ниже​Если необходимо вернуть среднее​ сам Excel. Мы​​ финансовых, статистических и​ Суммирование каждой n-ой​ Excel, используйте символы-разделители:​
​ клавиш.​Лента меню: Когда Вы​ Excel? Хотите найти​ диапазон с данными,​Урок: Применение формул в​ таблицу. При этом,​ пользоваться функцией ОКРУГЛ​ После чего жмем​​ процентами. 1 2​ и КОВАРИАЦИЯ.Г в​ примеры работы их​
​ формула возвращает медиану​ арифметическое значений, которые​ называем его объект​ технических данных.​ строки, Суммирование наибольших​ слеш (/), тире​Печать: Эта глава научит​ запускаете Excel, на​​ понятную инструкцию, которая​ а затем, находясь​ Microsoft Excel​
​ существуют очень широкие​ в Excel.​ Enter.​ 3 4 5​ Excel .​ формул.​ для списка, состоящего​​ удовлетворяют определенному условию,​ Application. Объект Application​VBA (Visual Basic for​
​ чисел, Суммирование диапазона​ (-) или точку​ вас отправлять листы​ Ленте меню открывается​ позволит в совершенстве​​ во вкладке «Главная»,​Одной из самых популярных​ возможности по настройке​
​Описание аргументов функции =ОКРУГЛ():​Из D6 в ячейку​ 6 7 8​Примеры использования функций​Примеры формул для​ из 14 чисел.​ то можно воспользоваться​​ открывает доступ ко​ Applications) – это​ с ошибками, Суммирование​
​ (.). Чтобы ввести​ Excel на печать​ вкладка Главная. Узнайте,​ овладеть великим множеством​ кликаем по кнопке​ функций, которые используются​ полей сводной таблицы.​Первый аргумент – это​ E6 скопируйте функцию​ 9 10 11​ КОВАРИАЦИЯ.В, КОВАРИАЦИЯ.Г и​ использования вспомогательных, но​

exceltable.com>

Самые интересные функции для работы в Excel

​Если отсортировать значения в​ статистической функцией​ многим параметрам, связанным​ название языка программирования​

Синтаксис и параметры функций

​ с критерием «ИЛИ»,​ время, используйте двоеточие​ и задавать некоторые​
​ как сворачивать и​ инструментов Excel, причем​ «Форматировать как таблицу».​ в Excel, является​Сводную таблицу можно создать​ ссылка на ячейку​ =СЧЕТ() – это​​ 12 13 14​ КОВАР для определения​ очень полезных функций​
​ порядке возрастания, то​СРЗНАЧЕСЛИ​ с самим приложением​ для Microsoft Office.​ Поиск в двух​ (:). Дату и​ важные настройки при​​ настраивать Ленту.​ быстро и легко?​ После этого, появляется​
​ функция «ЕСЛИ». С​ во вкладке «Вставка»,​ значение, которой нужно​ функция Excel для​ 15 16 17​ взаимосвязей между разными​​ ТИП, ЗНАК и​ все становится на​. Следующая формула вычисляет​
​ Excel.​ В данном разделе​ столбцах, Наиболее часто​ время можно вводить​ печати.​Примеры: Панель быстрого доступа,​ Предлагаем Вашему вниманию​ список с различными​​ её помощью можно​ нажав на кнопку»​ округлить.​
​ подсчета количества не​Функции в Excel –​ наборами данных. Расчет​ СИМВОЛ для быстрого​ много понятней:​ среднее чисел, которые​Элементы управления ActiveX: Научитесь​ описаны возможности и​​ встречающееся слово, Система​ в одну ячейку.​Примеры: Режимы просмотра, Разрывы​
​ Вкладка Разработчик.​самоучитель по работе в​ вариантами стилей оформления​ задать в ячейке​ которая так и​Второй аргумент – это​ пустых ячеек.​​ это вычислительные инструменты​ и вычисление ковариации​ решения сложных задач.​
​Возвращает наиболее часто встречающееся​ больше нуля:​ создавать элементы управления​ примеры использования VBA​ линейных уравнений.​Примеры: Функция РАЗНДАТ, Будние​ страниц, Верхние и​​Рабочая книга: Книгой называют​ Excel на простых​ таблицы. Выбираем наиболее​
​ вывод одного результата​ называется «Сводная таблица».​ количество знаков после​На данном примере хорошо​ пригодны для самых​ разных показателей.​Примеры формул где используется​​ значение в массиве​В данном примере для​
​ ActiveX, такие как​ применительно к Excel.​Эта глава рассказывает о​ и рабочие дни,​ нижние колонтитулы, Номера​ файл Excel. Когда​​ примерах​ подходящий из них.​ при выполнении конкретного​
​Урок: Применение сводных таблиц​ запятой, которое нужно​ видно, что функция​ разных отраслей деятельности:​Функция РЯД.СУММ для расчета​ функция СТОЛБЕЦ в​ числовых данных.​ подсчета среднего и​ кнопки, текстовые поля,​Создание макроса: При помощи​ мощных инструментах, которые​ Дней до дня​ страниц, Печать заголовков,​

exceltable.com>

Работа с функциями в Excel на примерах

​ Вы запускаете Excel,​. Здесь Вы найдёте​Также, таблицу можно создать,​ условия, и другого​ в Microsoft Excel​ оставить после округления.​ =СЧЕТ() игнорирует ячейки,​ финансы, статистика, банковское​ суммы степенных рядов​

​ Excel.​Если отсортировать числа в​ проверки условия используется​ поля со списком​ VBA Вы можете​ предлагает Excel для​ рождения, Табель, Последний​ Центрирование на странице,​

Функция вычисления среднего числа

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

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

1. ​ день месяца, Праздники,​
2. ​ Печать сетки и​ рабочая книга.​ различные вопросы и​ «Таблица», которая расположена​ его невыполнения.​
3. ​ размещенных в таблице,​ отображает округление но​
4. ​ числа или пусты.​ инженерство и проектирование,​Примеры работы с​

​ формулы с примерами​ все становится гораздо​ же диапазон, что​Userform: В этой главе​ Excel, записывая так​Сортировка: В Excel Вы​ Квартал, День года.​ заголовков строк/столбцов, Область​Примеры: Сохраняем в формате​

​ научитесь применять Excel​ во вкладке «Вставка»,​Синтаксис данной функции выглядит​ можно использовать диаграммы.​ не изменяет значение,​В ячейки D7 и​

• ​ анализ исследовательских данных​ функцией РЯД.СУММ при​ использования функции СТОЛБЕЦ.​
• ​ понятней:​ не всегда удобно.​ Вы узнаете, как​ называемые макросы. В​

​ можете сортировать по​

Функция подсчета количества значений в Excel

1. ​Текстовые функции: Excel предлагает​ печати.​ Excel 97-2003, Просмотр​ на практике. Самоучитель​ предварительно выделив определенную​ следующим образом «ЕСЛИ(логическое​ Их можно применять​
2. ​ а =ОКРУГЛ() –​ E7 введем логическую​ и т.д.​ вычислении сложных процентов,​
3. ​ А также применение​Статистическая функция​ На этот случай​ создавать пользовательские формы​ этом разделе Вы​ одному или нескольким​ огромное количество функций​
4. ​Обмен данными и общий​ нескольких рабочих книг,​ состоит из пяти​ область листа с​ выражение; [результат если​ в целях создания​

​ округляет значение. Поэтому​ функцию, которая позволит​Лишь мизерная часть возможностей​ расчета экспоненциального роста,​ функции СТОЛБЕЦ для​

Функция ЕСЛИ в Excel

​МОДА​ у функции​ в VBA.​ научитесь создавать простой​ столбцам. Расположите данные​ для самых различных​ доступ: Узнайте, как​

1. ​ Рабочая область, Автовосстановление.​ разделов:​ данными.​
2. ​ истина]; [результат если​ презентаций, написания научных​ для вычислений и​ нам проверить, все​ вычислительных функций включено​ прогноз траектории с​ нестандартных решений в​
3. ​на данный момент​СРЗНАЧЕСЛИ​

​Автор: Антон Андронов​ макрос.​ по убыванию или​ операций с текстовыми​ можно отправить данные​Рабочий лист: Лист –​Введение​После этого, выделенный набор​ ложь])».​ работ, в исследовательских​ расчетов нужно использовать​ ли студенты имеют​ в данный учебник​ учетом последовательности. ​ формулах при комбинации​

​ устарела, точнее, устарела​существует третий необязательный​Функции категории​MsgBox: MsgBox – это​ по возрастанию.Примеры: Сортировка​ строками.​ Excel в документ​ это набор ячеек,​

​Основы​ ячеек Microsoft Excel,​С помощью операторов «И»,​ целях, и т.д.​ функцию =ОКРУГЛ(), так​ оценки. Пример использования​ с уроками по​Примеры функции СМЕЩ для​ с другими функциями.​ ее форма записи.​

Как округлять числа в Excel

​ аргумент, по которому​Статистические​ диалоговое окно в​ по цвету, Обратный​Примеры: Разделение строки, Количество​ Word или в​

1. ​ в которых Вы​Функции​ будет воспринимать как​
2. ​ «ИЛИ» и вложенной​ Программа Microsoft Excel​ как форматирование ячеек​ функции ЕСЛИ:​ Excel. На простых​ прохода по диапазону​Примеры работы с текстовой​ Вместо нее теперь​ можно вычислять среднее.​
3. ​предназначены в первую​ VBA, при помощи​ список, Случайный список.​ экземпляров текста, Количество​

​ другие файлы. Кроме​ храните и обрабатываете​Анализ данных​

​ таблицу. Вследствие этого,​

1. ​ функции «ЕСЛИ», можно​ предоставляет широкий набор​ приведет к ошибочным​Перейдите в ячейку D7​
2. ​ примерах и далее​ ячеек в Excel.​ функцией ЗАМЕНИТЬ в​ используется функция​

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

exceltable.com>

​ значениям в результатах.​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

АДРЕС

(функция АДРЕС) — служба поддержки Office

В этой статье описаны синтаксис формулы и использование функции АДРЕС в Microsoft Excel. Найдите ссылки на информацию о работе с почтовыми адресами или создании почтовых этикеток в разделе См. Также .

Описание

Функцию АДРЕС можно использовать для получения адреса ячейки на листе с заданными номерами строк и столбцов.Например, ADDRESS ( 2,3) возвращает $ C $ 2 . В качестве другого примера, ADDRESS ( 77,300) возвращает $ KN $ 77 . Вы можете использовать другие функции, такие как функции ROW и COLUMN , чтобы предоставить аргументы номера строки и столбца для функции ADDRESS .

Синтаксис

АДРЕС (row_num, column_num, [abs_num], [a1], [sheet_text])

Аргументы функции АДРЕС имеют следующие аргументы:

• row_num Обязательно.Числовое значение, указывающее номер строки для использования в ссылке на ячейку.

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

• abs_num Необязательно. Числовое значение, указывающее тип возвращаемой ссылки.

• A1 Дополнительно.Логическое значение, указывающее ссылочный стиль A1 или R1C1. В стиле A1 столбцы помечаются в алфавитном порядке, а строки — в цифрах. В ссылочном стиле R1C1 столбцы и строки помечаются численно. Если аргумент A1 имеет значение ИСТИНА или опущен, функция АДРЕС возвращает ссылку в стиле A1; если ЛОЖЬ, функция АДРЕС возвращает ссылку в стиле R1C1.

  Примечание: Чтобы изменить справочный стиль, который использует Excel, щелкните вкладку Файл , щелкните Параметры , а затем щелкните Формулы .В разделе Работа с формулами установите или снимите флажок R1C1 ссылочный стиль .

• sheet_text Необязательно. Текстовое значение, указывающее имя рабочего листа, который будет использоваться в качестве внешней ссылки. Например, формула = АДРЕС ( 1,1 ,,, «Sheet2») возвращает Sheet2! $ A $ 1 . Если аргумент sheet_text опущен, имя листа не используется, а адрес, возвращаемый функцией, относится к ячейке на текущем листе.

Пример

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

Основные формулы Excel — Список важных формул для начинающих

Руководство по основным формулам Excel

Освоение основных формул Excel имеет решающее значение для новичков, чтобы стать высокопрофессиональными в финансовом анализе Описание работы финансового аналитика В описании работы финансового аналитика ниже приводится типичный пример всех навыков, образования и опыта, необходимых для работы аналитиком в банке, учреждении или корпорации.Выполняйте финансовое прогнозирование, отчетность и отслеживание операционных показателей, анализируйте финансовые данные, создавайте финансовые модели. Microsoft Excel Ресурсы ExcelИзучайте Excel онлайн с помощью 100 бесплатных руководств, ресурсов, руководств и шпаргалок по Excel! Ресурсы CFI — лучший способ изучить Excel на своих условиях. считается отраслевым стандартом программного обеспечения для анализа данных. Программа электронных таблиц Microsoft также является одним из наиболее предпочтительных программ для инвестиционных банкиров. Описание вакансии в инвестиционном банке. В этом описании вакансии в инвестиционном банке излагаются основные навыки, образование и опыт работы, необходимые для того, чтобы стать аналитиком или партнером IB и финансовыми аналитиками в области обработки данных и финансового моделирования. Финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании.Обзор того, что такое финансовое моделирование, как и зачем создавать модель, и презентация. Это руководство предоставит обзор и список основных функций Excel.

После того, как вы освоите этот список, переходите к расширенному руководству CFI по формулам Excel Расширенные формулы Excel, которые необходимо знатьЭти сложные формулы Excel очень важно знать и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel !!

Основные термины в Excel

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

1. Формулы

В Excel формула — это выражение, которое работает со значениями в диапазоне ячеек или ячейке. Например, = A1 + A2 + A3, которое находит сумму диапазона значений от ячейки A1 до ячейки A3.

2. Функции

Функции — это предварительно определенные формулы в Excel.Они исключают трудоемкий ручной ввод формул, давая им понятные для человека имена. Например: = СУММ (A1: A3). Функция суммирует все значения от A1 до A3.

Пять экономящих время способов вставки данных в Excel

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

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

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

Excel достаточно умен тем, что когда вы начинаете вводить имя функции, появляется всплывающая подсказка функции. Из этого списка вы выберете свои предпочтения. Однако не нажимайте клавишу Enter.Вместо этого нажмите клавишу TAB, чтобы продолжить вставку других параметров. В противном случае вы можете столкнуться с ошибкой неверного имени, часто как «#NAME?». Чтобы исправить это, просто повторно выберите ячейку и перейдите к строке формул, чтобы выполнить свою функцию.

Изображение: бесплатный ускоренный курс CFI по Excel.

2. Использование опции «Вставить функцию» на вкладке «Формулы»

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

3. Выбор формулы из одной из групп на вкладке «Формула»

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

Изображение: CFI’s Excel Courses.

4. Использование опции автосуммирования

Для быстрых и повседневных задач функция автосуммы Автосумма Формула автосуммы Excel — это ярлык, который позволяет сэкономить время при финансовом моделировании в Excel. Введите «ALT =», чтобы быстро суммировать все числа в серии данных. Это позволяет вам легко складывать серии чисел по вертикали или горизонтали без использования мыши или даже клавиш со стрелками.Итак, перейдите на вкладку Home в дальнем правом углу и нажмите кнопку AutoSum. Затем щелкните курсор, чтобы отобразить другие скрытые формулы. Этот параметр также доступен в первом варианте вкладки «Формулы» после параметра «Вставить функцию».

5. Быстрая вставка: используйте недавно использованные вкладки

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

Бесплатное руководство по формулам Excel на YouTube

Просмотрите БЕСПЛАТНОЕ видео на YouTube от CFI, чтобы быстро изучить наиболее важные формулы Excel. Посмотрев демонстрационный видеоролик, вы быстро узнаете самые важные формулы и функции.

Семь основных формул Excel для вашего рабочего процесса

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

1. СУММ

Функция СУММ Функция СУММ Функция СУММ относится к математическим функциям и функциям тригонометрии. Функция суммирует ячейки, которые представлены как несколько аргументов. Это самая популярная и широко используемая функция в Excel. SUM помогает пользователям выполнять быстрое суммирование указанных ячеек в MS Excel. Например, нам дана стоимость 100 — это первая формула, которую необходимо знать в Excel. Обычно он объединяет значения из набора столбцов или строк из выбранного диапазона.

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

Пример:

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

= СУММ (A2: A8) — простой выбор, который суммирует значения столбца.

= СУММ (A2: A7, A9, A12: A15) — сложная коллекция, которая суммирует значения от диапазона A2 до A7, пропускает A8, добавляет A9, перескакивает с A10 и A11, а затем, наконец, добавляет от A12 к A15.

= СУММ (A2: A8) / 20 — показывает, что вы также можете превратить свою функцию в формулу.

Изображение: бесплатный ускоренный курс CFI по Excel.

2. СРЕДНЕЕ

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

= СРЕДНЕЕ ( число1 , [число2],…)

Пример:

= СРЕДНИЙ (B2: B11) — показывает простое среднее, также похоже на (СУММ (B2: B11) / 10)

3. COUNT

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

= COUNT ( value1, [value2],… )

Пример:

COUNT (A : A) — Подсчитывает все числовые значения в столбце A. Однако для подсчета строк необходимо настроить диапазон внутри формулы.

COUNT (A1: C1) — Теперь он может подсчитывать строки.

Изображение: CFI’s Excel Courses.

4. COUNTA

Как и функция COUNT, функция COUNTACOUNTA Функция COUNTA вычисляет количество непустых ячеек в заданном наборе значений. Функцию = counta () также часто называют формулой Excel Countif Not Blank. Как финансовый аналитик, функция полезна для подсчета ячеек, которые не являются пустыми или пустыми в заданном диапазоне. считает все клетки в данной ярости.Однако он считает все ячейки независимо от типа. То есть, в отличие от COUNT, которое считает только числа, он также считает даты, время, строки, логические значения, ошибки, пустую строку или текст.

= COUNTA ( value1, [value2],… )

Пример:

COUNTA (C2: C13) — Считает строки со 2 по 13 в столбце C независимо от типа. Однако, как и COUNT, вы не можете использовать ту же формулу для подсчета строк. Вы должны скорректировать выбор внутри скобок — например, COUNTA (C2: h3) будет считать столбцы от C до H

5.IF

Функция IF Функция IF Функция Excel IF Statement проверяет заданное условие и возвращает одно значение для ИСТИННОГО результата, а другое — для ЛОЖНОГО результата. Например, если общий объем продаж превышает 5000 долларов, тогда в качестве бонуса верните «Да», иначе верните «Нет». Мы также можем создавать вложенные операторы IF, которые часто используются, когда вы хотите отсортировать данные в соответствии с заданной логикой. Лучшая часть формулы ЕСЛИ заключается в том, что вы можете встраивать в нее формулы и работать с ними.

= IF ( logic_test, [value_if_true], [value_if_false] )

Пример:

= IF (C2 — Проверяет, если значение в C3 меньше значения D3.Если логика верна, пусть значение ячейки будет ИСТИНА, иначе ЛОЖЬ

= ЕСЛИ (СУММ (C1: C10)> СУММ (D1: D10), СУММ (C1: C10), СУММ (D1: D10) )) — Пример сложной логики ЕСЛИ. Сначала он суммирует C1 до C10 и D1 до D10 , затем сравнивает сумму. Если сумма от C1 до C10 больше суммы от D1 до D10 , тогда значение ячейки становится равным сумме от C1 до C10 . В противном случае это будет СУММА от C1 до C10 .

6. ОБРЕЗАТЬ

Функция ОБРЕЗАТЬ Функция ОБРЕЗАТЬ Функция ОБРЕЗАТЬ относится к текстовым функциям Excel. TRIM помогает удалить лишние пробелы в данных и, таким образом, очистить ячейки на листе. В финансовом анализе функция TRIM может быть полезна для удаления нерегулярных, чтобы убедиться, что ваши функции не возвращают ошибки из-за неуправляемых пробелов. Это гарантирует, что все пустые места удалены. В отличие от других функций, которые могут работать с диапазоном ячеек, TRIM работает только с одной ячейкой.Следовательно, это имеет обратную сторону — добавление дублированных данных в вашу электронную таблицу.

= ОБРЕЗАТЬ ( текст )

Пример:

ОБРЕЗАТЬ (A2) — Удаляет пустые места в значении в ячейке A2.

Изображение: бесплатный ускоренный курс CFI по Excel.

7. MAX и MIN

Функция MAXMAX Функция MAX относится к категории Статистических функций Excel. MAX вернет наибольшее значение в заданном списке аргументов.Из заданного набора числовых значений он вернет наибольшее значение. В отличие от функции MAXA, функция MAX будет подсчитывать числа, но игнорировать пустые ячейки и функцию MINMIN Функция MIN относится к категории статистических функций Excel. MIN вернет минимальное значение в заданном списке аргументов. Из заданного набора числовых значений он вернет наименьшее значение. В отличие от функции MINA, функции помогают найти максимальное и минимальное количество в диапазоне значений.

= MIN ( число1 , [число2],…)

Пример:

= MIN (B2: C11) — Находит минимальное число между столбцом B от B2 и столбцом C от C2 до строка 11 в обоих столбцах B и C.

= МАКС ( число1 , [число2],…)

Пример:

= МАКС (B2: C11) — Точно так же он находит максимальное число между столбцом B от B2 до столбец C от C2 до строки 11 в обоих столбцах B и C.

Дополнительные ресурсы

Спасибо за то, что прочитали руководство CFI по основным формулам Excel. Чтобы продолжить свое развитие в качестве финансового аналитика мирового уровня, станьте сертифицированным аналитиком финансового моделирования и оценки (FMVA) ® Сертификат финансового моделирования и оценки CFI (FMVA) ® поможет вам обрести уверенность в своей финансовой карьере.Запишитесь сегодня !, эти дополнительные ресурсы CFI будут полезны:

• Расширенные формулы Excel Расширенные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важно знать и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
• Преимущества сочетаний клавиш Excel Обзор сочетаний клавиш Excel Сочетания клавиш Excel — это недооцененный метод повышения производительности и скорости работы в Excel. Ярлыки Excel предлагают финансовому аналитику мощный инструмент.Эти ярлыки могут выполнять множество функций. так же просто, как навигация по электронной таблице для заполнения формул или группировки данных.
• Список функций ExcelФункцииСписок наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает 100 функций, которые критически важно знать аналитику Excel.
  

  100+ функций Excel (объяснено с примерами и видео)

  Excel СЦЕПИТЬ Функция	Excel СЦЕПИТЬ функцию можно использовать, когда вы хотите объединить 2 или более символов или строк.Его можно использовать для объединения текста, чисел, ссылок на ячейки или их комбинации.
  Excel FIND Function	Excel FIND можно использовать, когда вы хотите найти текстовую строку в другой текстовой строке и найти ее положение. Он возвращает число, которое представляет начальную позицию строки, которую вы находите в другой строке. Это чувствительно к регистру.
  Функция Excel LEFT	Функция Excel LEFT может использоваться для извлечения текста слева от строки.Он возвращает указанное количество символов слева от строки.
  Excel LEN Function	Excel LEN функция может использоваться, если вы хотите получить общее количество символов в указанной строке. Это полезно, если вы хотите узнать длину строки в ячейке.
  Excel LOWER Function	Excel LOWER LOWER можно использовать, когда вы хотите преобразовать все прописные буквы в текстовой строке в строчные.Цифры, специальные символы и знаки препинания не изменяются функцией НИЖНИЙ.
  Функция MID в Excel	Функция MID в Excel может использоваться для извлечения указанного количества символов из строки. Он возвращает подстроку из строки.
  Excel PROPER функция	Excel PROPER функция может использоваться, когда вы хотите использовать заглавные буквы в первом символе каждого слова. Цифры, специальные символы и знаки препинания не изменяются функцией ПРОПИСАН.
  Excel функция REPLACE	Excel REPLACE функция может использоваться, когда вы хотите заменить часть текстовой строки другой строкой. Он возвращает текстовую строку, в которой часть текста была заменена указанной строкой.
  Функция ПОВТОР Excel	Функция ПОВТОР Excel может использоваться, если вы хотите повторить указанный текст определенное количество раз.
  Excel RIGHT Function	RIGHT Функция может использоваться для извлечения текста справа от строки.Он возвращает указанное количество символов справа от строки.
  Функция ПОИСК в Excel	Функция ПОИСК в Excel может использоваться, когда вы хотите найти текстовую строку в другой текстовой строке и найти ее положение. Он возвращает число, которое представляет начальную позицию строки, которую вы находите в другой строке. Он не чувствителен к регистру.
  Excel ЗАМЕНА Функция	Excel ЗАМЕНА может использоваться, когда вы хотите заменить текст новым заданным текстом в строке.Он возвращает текстовую строку, в которой старый текст был заменен новым.
  Excel ТЕКСТ Функция	Excel ТЕКСТ может использоваться, когда вы хотите преобразовать число в текстовый формат и отобразить его в указанном формате.
  Excel TRIM Функция	Excel TRIM функция может использоваться, когда вы хотите удалить начальные, конечные и двойные пробелы в Excel.
  ВЕРХНИЙ ВЕРХНИЙ в Excel	ВЕРХНИЙ в Excel можно использовать, если вы хотите преобразовать все строчные буквы в текстовой строке в прописные.Цифры, специальные символы и знаки препинания не изменяются функцией ВВЕРХ.

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

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

  Мы обучаем и тестируем Microsoft Excel в течение десяти лет, и проведенный нами опрос нескольких сотен офисных сотрудников показал, что мы тратим более 10% своей рабочей жизни на электронные таблицы, а для тех, кто занимается исследованиями и разработками или финансами, это больше. вроде 30%, или 2,5 часа в день.

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

  Insight Center

  В прошлом году мы составили «100 самых полезных советов по Excel», по поводу которых мы консультировались с экспертами по Excel и изучили десятки тысяч результатов тестов и данные об использовании курсов.Хотя у каждой функции Excel есть вариант использования, ни один человек не использует все функции Excel самостоятельно. Пройдите через 500+ функций, и вы получите около 100 действительно полезных функций и возможностей для большинства современных работников умственного труда. Мы выбрали десять из этого списка, которые особенно просты в освоении (всего около 2 часов) и которые могут существенно повлиять на продуктивность. Эти десять перечислены в порядке убывания полезности из нашего списка 100 лучших. Если, как и многие, вы не знаете, что изучать дальше в Excel, вы можете взглянуть на эту матрицу 2 × 2, которая учитывает полезность и время, необходимое для изучения навыка.

  Специальная вставка (обучение 10 минут)

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

  На устранение этих мелких неудобств может потребоваться время, поэтому Paste Special настолько… особенный. Специальная вставка позволяет вам выбрать, какие элементы скопированной ячейки вы переносите. После того, как вы скопировали свою ячейку (Ctrl + C), нажмите Ctrl + Alt + V (или перейдите в раздел «Буфер обмена» на главной ленте или «Правка»> «Специальная вставка»), чтобы вызвать Специальную вставку и сделать свой выбор. Alt + E + S + V — это ярлык для простой вставки значений — вероятно, наиболее распространенное использование Специальной вставки.

  Добавить несколько строк (обучение 2 минуты)

  Нам часто нужно добавлять новые строки между существующими строками. Ярлык (Ctrl, shift, +) довольно удобен, тем более что вы можете переключать +, чтобы добавить несколько строк. Часто, просто выделив количество строк, которые вы хотите добавить (скажем, 5), и щелкнув правой кнопкой мыши , вставка выполняется быстрее при массовом добавлении, так как добавляется количество выделенных вами строк.

  Flash Fill (обучение 30 минут)

  Благодаря этой функции в 2013 году в

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

  Предположим, у вас есть список номеров продуктов в первых десяти ячейках столбца A, например От «ABC-00001» до «ABC-00010», и вам нужны только цифры после «-». Вы можете легко отказаться от «ABC», используя Flash Fill. До 2013 года это было возможно, но полагалось на комбинацию функций (НАЙТИ, ВЛЕВО, & и т. Д.).Теперь это намного быстрее, и произведет впечатление на людей.

  Установите шаблон, введя «00001» в первую пустую ячейку. Если функция Flash Fill включена (Параметры файла, Дополнительно), просто начните вводить следующий номер продукта в ячейку ниже, и Flash Fill распознает шаблон и заполнит оставшиеся номера продуктов за вас. Просто нажмите клавишу Enter, чтобы принять. Или запустите его вручную, нажав Data> Flash Fill или Ctrl + E.

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

  ИНДЕКС-МАТЧ (обучение 45 минут)

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

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

  Допустим, у вас есть таблица со списком продуктов. У вас есть столбцы с заголовками «Номер продукта», «Прибыль», «Название продукта» и «Доход». В другой таблице у вас есть список названий продуктов, и вы хотите узнать, сколько прибыли принесло каждый продукт. В этом сценарии мы используем название продукта (наше поисковое значение) для поиска прибыли. Название продукта находится справа от прибыли, поэтому функция ВПР не работает. Это идеальный сценарий для INDEX и MATCH.

  Синтаксис будет:

  = ИНДЕКС (столбец прибыли, ПОИСКПОЗ (значение поиска , столбец названия продукта, 0))

  Вот хороший способ вспомнить, как это работает:

  = ИНДЕКС (столбец, из которого требуется возвращаемое значение , ПОИСКПОЗ (мое значение поиска, столбец, по которому я хочу выполнить поиск, введите «0»)) (ноль дает точное совпадение, вы можете сопоставить меньше, чем ( -1) или больше (1).)

  На первый и даже второй взгляд INDEX и MATCH выглядят сложными.Это, безусловно, требует некоторой практики, но стоит задуматься, поскольку он более гибкий и мощный, чем ВПР. Это одно из наиболее важных применений Excel, точка.

  SUM (на обучение 2 минуты)

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

  CTRL Z / CTRL Y (обучение 1 минута)

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

  Удаление дубликатов (обучение 10 минут)

  Это так просто и быстро в использовании. Функция Remove Duplicates делает именно то, что вы ожидаете, — удаляет дубликаты в любом заданном диапазоне данных.Наш совет — удалить значения, которые вы хотите вывести, и поместить их на другой лист. Он находится на вкладке «Данные » в разделе «Инструменты данных » на ленте.

  Если вы просто хотите выделить дубликаты, вы можете сделать это с помощью условного форматирования. Ярлык, чтобы попасть туда: Alt H L . (Или найдите его на ленте «Главная» в разделе «Стили»).

  Freeze Panes (15 минут на изучение)

  Когда-нибудь прокручивали вниз большую таблицу данных только для того, чтобы забыть, какие столбцы какие? Freeze Panes — вот ответ.Вы можете закрепить только верхнюю строку, первый столбец или любое их количество. Определите столбцы и строки области, которую вы хотите зафиксировать. Затем выберите ячейку справа от этих столбцов и под этими строками. Перейдите на вкладку «Просмотр» и закрепите области в разделе «Окно». Alt W F — это ярлык.

  F4 (10 минут на обучение)

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

  CTRL + стрелки (на обучение 5 минут)

  Если вы обнаружили, что пролистываете набор данных, чтобы добраться до его нижней части, остановитесь прямо сейчас и начните использовать Ctrl + клавиши со стрелками ! Этот простой ярлык приведет вас прямо к концу данных в столбце или строке, которые вы используете.Комбинируйте его с Ctrl, Shift, чтобы выделить / выделить большие области данных за секунды.

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

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

  Список функций

  — автоматизация 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	Создает интерактивную ссылку.	ГИПЕРССЫЛКА (расположение_ссылки, дружественное_имя)
  INDEX	Возвращает значение на основе номеров столбцов и строк.	ИНДЕКС (массив; номер_строки; номер_столбца)
  КОСВЕННО	Создает ссылку на ячейку из текста.	КОСВЕННО (ref_text, C1)
  ПРОСМОТР	Поиск значений по горизонтали или вертикали.	ПРОСМОТР (lookup_value, lookup_vector, result_vector)
  MATCH	Ищет значение в списке и возвращает его позицию.	ПОИСКПОЗ (искомое_значение, искомое_массив, совпадение_типа)
  OFFSET	Создает смещение ссылки от начальной точки.	СМЕЩЕНИЕ (ссылка, строки, столбцы, высота, ширина)
  ROW	Возвращает номер строки ссылки на ячейку.	ROW (ссылка)
  ROWS	Возвращает количество строк в массиве.	РЯД (массив)
  TRANSPOSE	Изменяет ориентацию ряда ячеек.	ТРАНСПОРТ (массив)
  ВПР	Найдите значение в первом столбце и верните значение.	ВПР (lookup_value, table_array, col_index_num, range_lookup)
  Дата и время	да
  ДАТА	Возвращает дату из года, месяца и дня.	ДАТА (год, месяц, день)
  РАЗНДАТ	Количество дней, месяцев или лет между двумя датами.	РАЗНДАТ
  DATEVALUE	Преобразует дату, сохраненную в виде текста, в действительную дату	DATEVALUE (date_text)
  DAY	Возвращает день в виде числа (1-31).	ДЕНЬ (серийный_номер)
  ДНЕЙ	Возвращает количество дней между двумя датами.	ДНЕЙ (end_date, start_date)
  DAYS360	Возвращает дни между двумя датами в году из 360 дней.	ДНЕЙ360 (начальная_дата, конечная_дата, метод)
  EDATE	Возвращает дату через n месяцев от начальной даты.	ДАТА (начальная_дата, мес)
  EOMONTH	Возвращает последний день месяца через n месяцев.	EOMONTH (start_date, мес)
  ЧАС	Возвращает час в виде числа (0–23).	ЧАС (серийный_номер)
  МИНУТА	Возвращает минуты в виде числа (0-59).	МИНУТА (серийный_номер)
  МЕСЯЦ	Возвращает месяц в виде числа (1–12).	МЕСЯЦ (серийный_номер)
  NETWORKDAYS	Количество рабочих дней между двумя датами.	ЧИСТРАБДНИ (начальная_дата, конечная_дата, праздники)
  NETWORKDAYS.INTL	Рабочие дни между двумя датами, индивидуальные выходные.	NETWORKDAYS.INTL (начальная_дата, конечная_дата, выходные, праздничные дни)
  СЕЙЧАС	Возвращает текущую дату и время.	СЕЙЧАС ()
  SECOND	Возвращает секунды в виде числа (0-59)	SECOND (serial_number)
  ВРЕМЯ	Возвращает время в формате часа, минуты и секунды.	ВРЕМЯ (час, минута, секунда)
  TIMEVALUE	Преобразует время, сохраненное в виде текста, в действительное время.	ВРЕМЕННОЕ ЗНАЧЕНИЕ (время_текст)
  СЕГОДНЯ	Возвращает текущую дату.	СЕГОДНЯ ()
  WEEKDAY	Возвращает день недели в виде числа (1-7).	WEEKDAY (serial_number, return_type)
  WEEKNUM	Возвращает номер недели в году (1-52).	НОМЕР НЕДЕЛИ (серийный_номер, тип возврата)
  РАБДЕНЬ	Дата n рабочих дней от даты.	РАБДЕНЬ (начальная_дата, дни, праздники)
  WORKDAY.INTL	Дата n рабочих дней от даты, настраиваемые выходные.	WORKDAY.INTL (начальная_дата, дни, выходные, праздничные дни)
  YEAR	Возвращает год.	ГОД (серийный_номер)
  YEARFRAC	Возвращает долю года между двумя датами.	YEARFRAC (начальная_дата, конечная_дата, базис)
  Инженерное дело	да
  ПРЕОБРАЗОВАТЬ	Преобразование числа из одной единицы в другую.	ПРЕОБРАЗОВАТЬ (число, from_unit, to_unit)
  Финансовый
  FV	Вычисляет будущую стоимость.	ФВ (ставка, ппер, пвт, пв, тип)
  PV	Вычисляет приведенную стоимость.	PV (ставка, nper, pmt, fv, тип)
  КПЕР	Вычисляет общее количество периодов платежей.	КПЕР (ставка, pmt, pv, fv, тип)
  PMT	Рассчитывает сумму платежа.	ПМТ (ставка, ппер, пв, фв, тип)
  RATE	Вычисляет процентную ставку.	СТАВКА (кол-во, п.п., pv, fv, тип, предположение)
  NPV	Вычисляет чистую приведенную стоимость.	NPV (ставка, значение1, значение2)
  IRR	Внутренняя норма доходности для набора периодических CF.	IRR (значения, предположить)
  XIRR	Внутренняя норма доходности для набора непериодических CF.	XIRR (значения, даты, предположение)
  ЦЕНА	Рассчитывает цену облигации.	ЦЕНА (расчет, срок погашения, ставка, год, погашение, ЧАСТОТА, базис)
  ДОХОДНОСТЬ	Рассчитывает доходность облигации.	ДОХОДНОСТЬ (расчет, срок, ставка, цена, погашение, ЧАСТОТА, базис)
  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	Вычисляет абсолютное значение числа.	АБС (номер)
  AGGREGATE	Определите и выполните вычисления для базы данных или списка.	АГРЕГАТ (номер_функции, параметры, массив, k)
  CEILING	Округляет число до ближайшего указанного кратного.	ПОТОЛОК (число, значение)
  COS	Возвращает косинус угла.	COS (номер)
  ГРАДУСЫ	Преобразует радианы в градусы.	ГРАДУСОВ (угол)
  DSUM	Суммирует записи базы данных, соответствующие определенным критериям.	DSUM (база данных, поле, критерии)
  ЧЕТНОЕ	Округляет до ближайшего четного целого числа.	ЧЕТ (число)
  EXP	Вычисляет экспоненциальное значение для заданного числа.	EXP (номер)
  FACT	Возвращает факториал.	ФАКТ (номер)
  FLOOR	Округляет число до ближайшего указанного кратного.	ЭТАЖ (номер, значение)
  GCD	Возвращает наибольший общий делитель.	НОД (номер1, номер2)
  INT	Округляет число до ближайшего целого.	INT (номер)
  LCM	Возвращает наименьшее общее кратное.	LCM (номер1, номер2)
  LN	Возвращает натуральный логарифм числа.	LN (номер)
  LOG	Возвращает логарифм числа с указанным основанием.	ЖУРНАЛ (номер, база)
  LOG10	Возвращает десятичный логарифм числа.	LOG10 (номер)
  MOD	Возвращает остаток после деления.	MOD (число, делитель)
  MROUND	Округляет число до указанного кратного.	ОКРУГ (число, кратное)
  ODD	Округляет до ближайшего нечетного целого числа.	ODD (номер)
  ИП	Значение ИП.	PI ()
  МОЩНОСТЬ	Вычисляет число в степени.	МОЩНОСТЬ (число, мощность)
  PRODUCT	Умножает массив чисел.	ТОВАР (номер1, номер2)
  QUOTIENT	Возвращает целочисленный результат деления.	ЧАСТНОЕ (числитель, знаменатель)
  РАДИАНЫ	Преобразует угол в радианы.	РАДИАНЫ (угол)
  RAND	Вычисляет случайное число от 0 до 1.	RAND ()
  RANDBETWEEN	Вычисляет случайное число между двумя числами.	RANDBETWEEN (внизу, вверху)
  ОКРУГЛ.	Округляет число до указанного количества цифр.	КРУГЛЫЙ (число, число_цифров)
  ОКРУГЛ. ВНИЗ	Округляет число в меньшую сторону (до нуля).	ОКРУГЛ ВНИЗ (число, число_цифров)
  ROUNDUP	Округляет число в большую сторону (от нуля).	ОКРУГЛ ВВЕРХ (число, число_цифров)
  SIGN	Возвращает знак числа.	ЗНАК (номер)
  SIN	Возвращает синус угла.	SIN (номер)
  SQRT	Вычисляет квадратный корень числа.	SQRT (номер)
  ПРОМЕЖУТОЧНЫЙ ИТОГ	Возвращает сводную статистику для серии данных.	ПРОМЕЖУТОЧНЫЙ ИТОГ (function_num, REh2)
  SUM	Складывает числа.	СУММ (число1, число2)
  СУММЕСЛИ	Суммирует числа, соответствующие критерию.	СУММЕСЛИ (диапазон, критерий, диапазон_суммы)
  СУММЕСЛИМН	Суммирует числа, соответствующие нескольким критериям.	СУММЕСЛИМН (диапазон_сумм, диапазон_критерия, критерий)
  SUMPRODUCT	Умножает массивы чисел и суммирует результирующий массив.	СУММПРОИЗВ (массив1, массив2, массив3)
  TAN	Возвращает тангенс угла.	ТАН (номер)
  TRUNC	Усекает число до определенного количества цифр.	TRUNC (число, число_цифров)
  Статистика	да
  СРЕДНЕЕ	Средние числа.	СРЕДНИЙ (число1, число2)
  AVERAGEA	Средние числа.Включает текст & FALSE = 0, TRUE = 1.	СРЕДНЕЕ (значение1, значение2)
  СРЕДНЕЕ	Средние числа, соответствующие критериям.	AVERAGEIF (диапазон, критерии, средний_диапазон)
  AVERAGEIFS	Среднее число, отвечающее нескольким критериям.	AVERAGEIFS (средний_диапазон, критерий_диапазон, критерий)
  CORREL	Вычисляет корреляцию двух рядов.	КОРРЕЛЬ (массив1, массив2)
  COUNT	Подсчитывает ячейки, содержащие число.	СЧЁТ (значение1, значение2)
  COUNTA	Подсчитайте непустые ячейки.	СЧЁТ (значение1, значение2)
  COUNTBLANK	Подсчитывает пустые ячейки.	СЧЕТЧИК (диапазон)
  COUNTIF	Подсчитывает ячейки, соответствующие критерию.	СЧЁТЕСЛИ (диапазон, критерии)
  COUNTIFS	Подсчитывает ячейки, соответствующие нескольким критериям.	COUNTIFS (диапазон_критериев, критерий)
  ПРОГНОЗ	Предскажите будущие значения y по линейной линии тренда.	ПРОГНОЗ (x, известные_y, известные_x)
  ЧАСТОТА	Подсчитывает значения, попадающие в указанные диапазоны.	ЧАСТОТА (массив_данных, массив_бинов)
  РОСТ	Вычисляет значения Y на основе экспоненциального роста.	РОСТ (известный_ys, известный_x, новый_x, const)
  ПЕРЕСЕЧЕНИЕ	Вычисляет точку пересечения оси Y для наиболее подходящей линии.	ПЕРЕСЕЧЕНИЕ (известное_ис, известное_xs)
  LARGE	Возвращает k-е наибольшее значение.	БОЛЬШОЙ (массив, k)
  ЛИНЕЙН	Возвращает статистику по линии тренда.	ЛИНЕЙН (известные_вы, известные_x, константа, статистика)
  MAX	Возвращает наибольшее число.	МАКС (число1, число2)
  MEDIAN	Возвращает медианное число.	МЕДИАНА (число1, число2)
  MIN	Возвращает наименьшее число.	МИН (число1, число2)
  MODE	Возвращает наиболее часто встречающееся число.	РЕЖИМ (число1, число2)
  ПРОЦЕНТИЛЬ	Возвращает k-й процентиль.	ПРОЦЕНТИЛЬ (массив, k)
  PERCENTILE.INC	Возвращает k-й процентиль. Где k включительно.	PERCENTILE.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)
  МАЛЫЙ	Возвращает k-е наименьшее значение.	МАЛЫЙ (массив, k)
  STDEV	Вычисляет стандартное отклонение.	СТАНДОТКЛОН (номер1, номер2)
  STDEV.P	Вычисляет SD для всей генеральной совокупности.	СТАНДАРТ.П (номер1, номер2)
  STDEV.S	Вычисляет SD образца.	СТАНДАРТ.S (номер1, номер2)
  STDEVP	Вычисляет SD для всего населения	STDEVP (число1, число2)
  TREND	Вычисляет значения Y на основе линии тренда.	ТЕНДЕНЦИЯ (известные_вы, известные_x, новые_x, константа)
  Текст	да
  CHAR	Возвращает символ, указанный кодом.	СИМВОЛ (число)
  CLEAN	Удаляет все непечатаемые символы.	CLEAN (текст)
  CODE	Возвращает числовой код символа.	КОД (текст)
  CONCATENATE	Объединяет текст вместе.	СЦЕПИТЬ (текст1, текст2)
  ДОЛЛАР	Преобразует число в текст в денежном формате.	ДОЛЛАР (число, десятичные знаки)
  EXACT	Проверьте, равны ли ячейки.С учетом регистра. ИСТИНА / ЛОЖЬ	ТОЧНО (текст1, текст2)
  НАЙТИ	Определяет положение текста в ячейке. С учетом регистра.	НАЙТИ (найти_текст, внутри_текст, начальное_число)
  LEFT	Обрезает текст на несколько символов слева.	ЛЕВЫЙ (текст, число_символов)
  LEN	Подсчитывает количество символов в тексте.	LEN (текст)
  LOWER	Преобразует текст в нижний регистр.	НИЖНИЙ (текст)
  MID	Извлекает текст из середины ячейки.	MID (текст, start_num, num_chars)
  ПРАВИЛЬНО	Преобразует текст в правильный регистр.	ПРАВИЛЬНО (текст)
  REPLACE	Заменяет текст в зависимости от его местоположения.	REPLACE (old_text, start_num, num_chars, new_text)
  REPT	Повторяет текст несколько раз.	ПОВТОР (текст, число_раз)
  СПРАВА	Обрезает текст на количество символов справа.	ВПРАВО (текст, число_знаков)
  ПОИСК	Определяет положение текста в ячейке. Без учета регистра.	ПОИСК (найти_текст, внутри_текст, начальное_число)
  ЗАМЕНА	Находит и заменяет текст. С учетом регистра.	ПОДСТАВИТЬ (текст; старый_текст, новый_текст, номер_экземпляра)
  ТЕКСТ	Преобразует значение в текст с определенным числовым форматом.	ТЕКСТ (ЗНАЧЕНИЕ, формат_текст)
  TRIM	Удаляет все лишние пробелы из текста.	ОТДЕЛКА (текст)
  ВЕРХНИЙ	Преобразует текст в верхний регистр.	ВЕРХНИЙ (текст)
  ЗНАЧЕНИЕ	Преобразует число, сохраненное в виде текста, в число.	ЗНАЧЕНИЕ (текст)

  Формулы Microsoft Excel, которые вам нужно знать (практические демонстрации)

  Узнайте, почему это один из ПРЕВОСХОДНЫХ курсов Excel на Udemy:

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

  -Хулио Гарсия

  « Это исключительно ценный курс. Информация очень важна с примерами передового опыта от настоящего эксперта по Excel. Крис Даттон может научить! »

  -Барбара С.

  « Крис Даттон — ЭКСПЕРТ в Excel. Он делает понятным для ученика сложный (иногда сверхсложный) характер используемых им формул.Все, что написано в описании курса, хотя на первый взгляд может показаться чистым маркетингом и рекламой, действительно является правдой. Если бы я мог оценить это выше, я бы определенно поставил. СПАСИБО, Крис! »

  -Bruno Ricardo Silva Pinho
  __________

  ПОЛНОЕ ОПИСАНИЕ КУРСА:

  __________

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

  Этот курс даст вам глубокое понимание сложных формул и функций Excel, которые превращают Excel из базовой программы для работы с электронными таблицами в динамический и мощный инструмент аналитики. В то время как большинство курсов Excel сосредоточено на , ​​что делает каждая формула, я учу на практических контекстных примерах , разработанных, чтобы продемонстрировать , ​​почему эти формулы прекрасны, и как их можно применить в числах способов. Я не буду учить вас повторять функции и синтаксис формул; Я научу вас ДУМАТЬ, как в Excel.

  __________

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

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

  • Проявите мошенничество и создайте собственные правила форматирования Excel на основе формул , котировки акций, направления и т. д.)

  • Управление датами, временем, текстом и массивами

  • Автоматизация утомительных и трудоемких задач с помощью формул и функций ячеек в Excel (VBA не требуется!)

  __________

  Мы будем погрузиться в широкий спектр формул и функций Excel, в том числе:

  __________

  Что дает вам право проводить этот класс? Разве я не могу просто погуглить это?

  У меня искренняя страсть к Excel, которую большинство людей оставляют для таких вещей, как котята, мороженое и другие важные вещи.Единственное, что мне нравится больше, чем изучение Excel, — это , ​​преподающий его , и, как основателю Excel Maven и Maven Analytics, мне посчастливилось обучить Excel 200000+ студентов в 180+ странах . Мой стиль преподавания — разговорный, аутентичный и точный, и я всегда буду передавать сложные концепции в ясной и легкой для понимания структуре.

  Как консультант по аналитике и инструктор по Excel, я набрался опыта в использовании Excel для решения реальных бизнес-задач и разработки отмеченных наградами инструментов аналитики и визуализации данных для компаний из списка Fortune 500.Если вам небезразличны кредиты, я являюсь сертифицированным экспертом по Excel MOS, и моя работа была отмечена в Microsoft и New York Times. Хорошо, на самом деле я не использую для карты , но идею вы поняли.

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

  До встречи!

  — Крис (основатель , ​​ Maven Analytics )

  __________

  Ищете полный стек бизнес-аналитики? Найдите « Maven Analytics » , чтобы просмотреть нашу полную библиотеку курсов, включая курсы Excel, Power BI, MySQL и Tableau !

  * ПРИМЕЧАНИЕ: Полный курс включает в себя загружаемые ресурсы и Файлы проектов Excel , ​​ домашнее задание и викторины пожизненный доступ и 30-дневная гарантия возврата денег .Большинство лекций совместимо с Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019 или Office 365.

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

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

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

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

  1. СЦЕПИТЬ

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

  Формула:

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

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

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

  2. LEN

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

  Формула:

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

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

  = LEN (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. ВПР

  VLOOKUP — одна из самых полезных и узнаваемых функций анализа данных. Как пользователю 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 используется для определения того, какие клиенты заказывают больше всего продуктов.

  Формула:

  = РАНГ (ВЫБРАТЬ ЯЧЕЙКУ, RANGE_TO_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 умножает один диапазон значений на соответствующие ему эквиваленты строки. Это золото анализа данных. В приведенном ниже примере мы вычисляем среднюю отпускную цену всех наших продуктов, умножая цену на количество, а затем делим на общий проданный объем.

  Добавить комментарий Отменить ответ

  Ваш адрес email не будет опубликован. Обязательные поля помечены *

  Комментарий *

  Имя *

  Email *

  Сайт