ГлавнаяРазноеФункция в excel содержит

Примеры как создать пользовательскую функцию в Excel. Функция в excel содержит


Функция ЕСЛИ СОДЕРЖИТ - EXCEL СПб

Наверное, многие задавались вопросом, как найти функцию в EXCEL«СОДЕРЖИТ», чтобы применить какое-либо условие, в зависимости от того, есть ли в текстовой строке кусок слова, или отрицание, или часть наименования контрагента, особенно при нестандартном заполнении реестров вручную.

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

Рассмотрим пример автоматизации учета операционных показателей на основании реестров учета продаж и возвратов (выгрузки из сторонних программ автоматизации и т.п.)

 

У нас есть множество строк с документами Реализации и Возвратов.

Все документы имеют свое наименование за счет уникального номера.

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

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

Для того, чтобы это сделать, необходимо:

 

 

  1. Начинаем с ввода функции ЕСЛИ (вводим «=», набираем наименование ЕСЛИ, выбираем его из выпадающего списка, нажимаем fx в строке формул).
  2. В открывшемся окне аргументов, в поле Лог_выражение вводим СЧЁТЕСЛИ(), выделяем его и нажимаем 2 раза fx.

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

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

  4. Аргумент «Диапазон» - это соответствующая ячейка с наименованием документа.
  5. Далее нажимаем ОК, выделяем в строке формул ЕСЛИ и нажимаем fx и продолжаем заполнение функции ЕСЛИ.

  6. В Значение_если_истина вводим «Реализация», а в Значение_если_ложь – можно ввести прочерк « - »
  7. Далее протягиваем формулу до конца таблицы и подключаем сводную.

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

excelspb.ru

Проверка ячейки на наличие в ней текста (без учета регистра)

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

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

Для поиска текста можно также использовать фильтр. Дополнительные сведения см. в статье Фильтрация данных.

Поиск ячеек, содержащих текст

Выполните следующие действия, чтобы найти ячейки, содержащие определенный текст.

  1. Выделите диапазон ячеек, среди которых требуется осуществить поиск.

    Чтобы выполнить поиск по всему листу, щелкните любую ячейку.

  2. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить и нажмите кнопку Найти.

  3. В поле Найти введите текст — или номера —, вам нужно найти. Или выберите из раскрывающегося списка Найти последнего поиска.

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

  4. Чтобы задать формат для поиска, нажмите кнопку Формат и внесите нужные изменения во всплывающем окне Найти формат.

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

    Для поиска на текущем листе или во всей книге можно выбрать в поле Искать вариант Лист или Книга.

  6. Нажмите кнопку Найти все или Найти далее.

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

Примечание: Чтобы прекратить процесс поиска, нажмите клавишу ESC.

Проверка ячейки на наличие в ней любого текста

Для выполнения этой задачи используется функция ЕТЕКСТ .

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

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

Проверка соответствия части ячейки определенному тексту

Для выполнения этой задачи используются функции Если, Поиски ЕЧИСЛО .

Примечание: Функция поиска не учитывается регистр.

support.office.com

Знакомство с функциями в Excel

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

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

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

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

В Excel существуют функции, которые не содержат ни одного аргумента. К примеру, функция СЕГОДНЯ() возвращает текущую дату из системного времени вашего компьютера.

Работа с аргументами

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

Например, функция =СРЗНАЧ(B1:B9) будет вычислять среднее значение в диапазоне ячеек B1:B9. Эта функция содержит только один аргумент.

Несколько аргументов должны быть разделены точкой с запятой. Например, функция =СУММ(A1:A3; C1:C2; E2) суммирует значения всех ячеек в трех аргументах.

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Примеры как создать пользовательскую функцию в Excel

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

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

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

  1. Открыть редактор языка VBA с помощью комбинации клавиш ALT+F11.
  2. В открывшемся окне выбрать пункт Insert и подпункт Module, как показано на рисунке:
  3. Новый модуль будет создан автоматически, при этом в основной части окна редактора появится окно для ввода кода:
  4. При необходимости можно изменить название модуля.
  5. В отличие от макросов, код которых должен находиться между операторами Sub и End Sub, пользовательские функции обозначают операторами Function и End Function соответственно. В состав пользовательской функции входят название (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, если они требуются (некоторые могут не принимать аргументов), тип возвращаемого значения, тело функции (код, отражающий логику ее работы), а также оператор End Function. Пример простой пользовательской функции, возвращающей названия дня недели в зависимости от указанного номера, представлен на рисунке ниже:
  6. После ввода представленного выше кода необходимо нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
  7. Чтобы воспользоваться созданной функцией, необходимо вернуться к табличному редактору Excel, установить курсор в любую ячейку и ввести название пользовательской функции после символа «=»:

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

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

  1. Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать:
  2. В результате будет создан новый модуль с заготовкой, ограниченной операторами Sub и End Sub.
  3. Введите код, как показано на рисунке ниже, указав требуемое количество переменных (в зависимости от числа аргументов пользовательской функции):
  4. В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» - переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» - массив переменных типа String с текстами описаний аргументов пользовательской функции.
  5. Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:

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



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

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

Вид исходной таблицы данных:

Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:

  • N – суммарная зарплата за год;
  • n – число праздничных дней в году.

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

Код примера:

Public Function Otpusknye(summZp As Long, holidays As Long) As LongIf IsNumeric(holidays) = False Or IsNumeric(summZp) = False Then    Otpusknye = "Введены нечисловые данные"    Exit FunctionElseIf holidays <= 0 Or summZp <= 0 Then    Otpusknye = "Отрицательное число или 0"    Exit FunctionElse    Otpusknye = summZp * 24 / (365 - holidays)End IfEnd Function

Сохраним функцию и выполним расчет с ее использованием:

=Otpusknye(B3;C3)

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

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

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

Вид исходной таблицы данных:

Для расчета используем формулу Миффлина - Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:

Public Function CaloriesPerDay(sex As String, age As Integer, weight As Integer, height As Integer) As IntegerIf sex = "женский" Then    CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age - 161ElseIf sex = "мужской" Then    CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age + 5Else: CaloriesPerDay = 0End IfEnd Function

Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).

Пример расчета для первого участника:

=CaloriesPerDay(B3;C3;D3;E3)

В результате использования автозаполнения получим следующие результаты:

Пользовательская функция для решения квадратных уравнений в Excel

Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.

Вид исходной таблицы:

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

Код примера:

Public Function SquareEquation(a As Integer, b As Integer, c As Integer) As StringDim answer1 As StringDim answer2 As StringIf a = 0 Then    answer1 = "Единственный корень - "    SquareEquation = answer1 & "(" & -c / b & ")"ElseIf c = 0 Then    answer1 = "Единственный корень - "    SquareEquation = answer1 & "(" & -b / a & ")"ElseIf b = 0 And c < 0 Then    answer1 = "Единственный корень - "    SquareEquation = answer1 & "(" & Sqr(a / c) & ")"ElseIf b ^ 2 - 4 * a * c >= 0 Then    answer1 = "Первый корень - "    answer2 = "Второй корень - "    SquareEquation = answer1 & "(" & (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")" & "; " & _         answer2 & "(" & (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")"Else:    SquareEquation = "Решений нет"End IfEnd Function

Найдем корни первого уравнения:

=SquareEquation(A3;B3;C3)

Выполним расчеты для остальных уравнений. Полученные результаты:

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

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

exceltable.com