Функция что вы хотите сделать
Как создать пользовательскую функцию?
В решении многих задач обычные функции Excel не всегда могут помочь. Если существующих функций недостаточно, Excel позволяет добавить новые настраиваемые пользовательские функции (UDF). Они делают вашу работу легче.
Мы расскажем, как можно их создать, какие они бывают и как использовать их, чтобы ваша работа стала проще. Узнайте, как записать и использовать пользовательские функции, которые многие называют макросами..
Что такое пользовательская функция в Excel?
На момент написания этой статьи Excel предлагает вам более 450 различных функций. С их помощью вы можете выполнять множество различных операций. Но разработчики Microsoft Excel не могли предвидеть все задачи, которые нам нужно решать. Думаю, что многие из вас встречались с этими проблемами:
Как можно решить эти проблемы?
Хотя первые два варианта кажутся вам знакомыми, третий может вызвать некоторую путаницу. Итак, давайте подробнее рассмотрим настраиваемые функции в Excel и решим, стоит ли их использовать.
Пользовательская функция – это настраиваемый код, который принимает исходные данные, производит вычисление и возвращает желаемый результат.
Исходными данными могут быть числа, текст, даты, логические значения, массивы. Результатом вычислений может быть значение любого типа, с которым работает Excel, или массив таких значений.
Другими словами, пользовательская функция – это своего рода модернизация стандартных функций Excel. Вы можете использовать ее, когда возможностей обычных функций недостаточно. Основное ее назначение – дополнить и расширить возможности Excel, выполнить действия, которые невозможны со стандартными возможностями.
Существует несколько способов создания собственных функций:
Даже если вы сохранили эту огромную формулу в своем архиве, вам нужно ее найти, скопировать и вставить, а затем аккуратно поправить все ссылки на ячейки. Согласитесь, это потребует затрат времени, да и ошибки не исключены.
А на ввод функции вы потратите всего несколько секунд.
Для чего можно использовать?
Вы можете использовать настраиваемую функцию одним из следующих способов:
Для чего нельзя использовать пользовательские функции:
Как создать пользовательскую функцию в VBA?
Прежде всего, необходимо открыть редактор Visual Basic (сокращенно — VBE). Обратите внимание, что он открывается в новом окне. Окно Excel при этом не закрывается.
После открытия VBE вам нужно добавить новый модуль. В него вы будете записывать ваш код. Щелкните правой кнопкой мыши на панели проекта VBA слева и выберите «Insert», затем появившемся справа окне — “Module”.
Справа появится пустое окно модуля, в котором вы и будете создавать свою функцию.
Прежде чем начать, напомним правила, по которым создается функция.
Теперь давайте попробуем создать вашу первую собственную формулу. Для начала мы создаем код, который будет подсчитывать количество слов в диапазоне ячеек.
Для этого в окно модуля вставим этот код:
Я думаю, здесь могут потребоваться некоторые пояснения.
Код функции всегда начинается с пользовательской процедуры Function. В процедуре Function мы делаем описание новой функции.
В начале мы должны записать ее имя: CountWords.
Затем в скобках указываем, какие исходные данные она будет использовать. NumRange As Range означает, что аргументом будет диапазон значений. Сюда нужно передать только один аргумент — диапазон ячеек, в котором будет происходить подсчёт.
As Long указывает, что результат выполнения функции CountWords будет целым числом.
Во второй строке кода мы объявляем переменные.
Оператор Dim объявляет переменные:
rCell — переменная диапазона ячеек, в котором мы будем подсчитывать слова.
lCount — переменная целое число, в которой будет записано число слов.
Цикл For Each… Next предназначен для выполнения вычислений по отношению к каждому элементу из группы элементов (нашего диапазона ячеек). Этот оператор цикла применяется, когда неизвестно количество элементов в группе. Начинаем с первого элемента, затем берем следующий и так повторяем до самого последнего значения. Цикл повторяется столько раз, сколько ячеек имеется во входном диапазоне.
Внутри этого цикла с значением каждой ячейки выполняется операция, которая вычисляет количество слов:
Как видите, это обычная формула Excel, которая использует стандартные средства работы с текстом: LEN, TRIM и REPLACE. Это английские названия знакомых нам русскоязычных ДЛСТР, СЖПРОБЕЛЫ и ЗАМЕНИТЬ. Вместо адреса ячейки рабочего листа используем переменную диапазона rCell. То есть, для каждой ячейки диапазона мы последовательно считаем количество слов в ней.
Подсчитанные числа суммируются и сохраняются в переменной lCount:
Когда цикл будет завершен, значение переменной присваивается функции.
Функция возвращает в ячейку рабочего листа значение этой переменной, то есть общее количество слов.
Именно эта строка кода гарантирует, что функция вернет значение lCount обратно в ячейку, из которой она была вызвана.
Закрываем наш код с помощью «End Function».
Как видите, не очень сложно.
Сохраните вашу работу. Для этого просто нажмите кнопку “Save” на ленте VB редактора.
Вы можете сравнить работу с пользовательской функцией CountWords и подсчет количества слов в диапазоне при помощи формул.
Как использовать пользовательскую функцию в формуле?
Когда вы создали пользовательскую, она становится доступной так же, как и другие стандартные функции Excel. Сейчас мы узнаем, как создавать с ее помощью собственные формулы.
Чтобы использовать ее, у вас есть две возможности.
Первый способ. Нажмите кнопку fx в строке формул. Среди появившихся категорий вы увидите новую группу — Определённые пользователем. И внутри этой категории вы можете увидеть нашу новую пользовательскую функцию CountWords.
Можно посчитать этой же функцией и количество слов в диапазоне. Запишите в ячейку С3:
Мы только что указали функцию и установили диапазон, и вот результат подсчета: 14 слов.
Для сравнения в C1 я записал формулу массива, при помощи которой мы также можем подсчитать количество слов в диапазоне.
Как видите, результаты одинаковы. Только использовать CountWords() гораздо проще и быстрее.
Различные типы пользовательских функций с использованием VBA.
Теперь мы познакомимся с разными типами пользовательских функций в зависимости от используемых ими аргументов и результатов, которые они возвращают.
Без аргументов.
Вы можете создать такую функцию и в VBA.
Ниже приведен код, который запишет в ячейку имя вашего рабочего листа.
Или же можно использовать такой код:
Обратите внимание, что в скобках после имени нет ни одного аргумента. Здесь не требуется никаких аргументов, так как результат, который нужно вернуть, не зависит от каких-либо значений в вашем рабочем файле.
Приведенный выше код определяет результат функции как тип данных String (поскольку желаемый результат — это имя файла, которое является текстом). Если вы не укажете тип данных, то Excel будет определять его самостоятельно.
С одним аргументом.
Создадим простую функцию, которая работает с одним аргументом, то есть с одной ячейкой. Наша задача – извлечь из текстовой строки последнее слово.
Аргумент The_Text — это значение выбранной ячейки. Указываем, что это должно быть текстовое значение (As String).
Оператор StrReverse возвращает текст с обратным порядком следования знаков. Далее InStr определяет позицию первого пробела. При помощи Left получаем все знаки заканчивая первым пробелом. Затем удаляем пробелы при помощи Trim. Вновь меняем порядок следования символов при помощи StrReverse. Получаем последнее слово из текста.
Поскольку эта функция принимает значение ячейки, нам не нужно использовать здесь Application.Volatile. Как только аргумент изменится, функция автоматически обновится.
Использование массива в качестве аргумента.
Многие функции Excel используют массивы значений как аргументы. Вспомните функции СУММ, СУММЕСЛИ, СУММПРОИЗВ.
Мы уже рассмотрели эту ситуацию выше, когда учились создавать пользовательскую функцию для подсчета количества слов в диапазоне ячеек.
Приведённый ниже код создает функцию, которая суммирует все чётные числа в указанном диапазоне ячеек.
Аргумент NumRange указан как Range. Это означает, что функция будет использовать массив исходных данных. Необходимо отметить, что можно использовать также тип переменной Variant. Это выглядит как
Тип Variant обеспечивает «безразмерный» контейнер для хранения данных. Такая переменная может хранить данные любого из допустимых в VBA типов, включая числовые значения, текст, даты и массивы. Более того, одна и та же такая переменная в одной и той же программе в разные моменты может хранить данные различных типов. Excel самостоятельно будет определять, какие данные передаются в функцию.
В коде есть цикл For Each … Next, который берет каждую ячейку и проверяет, есть ли в ней число. Если это не так, то ничего не происходит, и он переходит к следующей ячейке. Если найдено число, он проверяет, четное оно или нет (с помощью функции MOD).
Все чётные числа суммируются в переменной Result.
Когда цикл будет закончен, значение Result присваивается переменной SumEven и передаётся функции.
С несколькими аргументами.
Большинство функций Excel имеет несколько аргументов. Не являются исключением и пользовательские функции. Поэтому так важно уметь создавать собственные функции с несколькими аргументами.
В приведенном ниже коде создается функция, которая выбирает максимальное число в заданном интервале.
Она имеет 3 аргумента: диапазон значений, нижняя граница числового интервала, верхняя граница интервала.
Здесь мы используем три аргумента. Первый из них — rngCells As Range. Это диапазон ячеек, в которых нужно искать максимальное значение. Второй и третий аргумент (MinNum, MaxNum) указаны без объявления типа. Это означает, что по умолчанию к ним будет применён тип данных Variant. В VBA используется 6 различных числовых типов данных. Указывать только один из них — это значит ограничить применение функции. Поэтому более целесообразно, если Excel сам определит тип числовых данных.
Цикл For Each … Next последовательно просматривает все значения в выбранном диапазоне. Числа, которые находятся в интервале от максимального до минимального значения, записываются в специальный массив arrNums. При помощи стандартного оператора MAX в этом массиве находим наибольшее число.
С обязательными и необязательными аргументами.
Чтобы понять, что такое необязательный аргумент, вспомните функцию ВПР (VLOOKUP). Её четвертый аргумент [range_lookup] является необязательным. Если вы не укажете один из обязательных аргументов, получите ошибку. Но если вы пропустите необязательный аргумент, всё будет работать.
Но необязательные аргументы не бесполезны. Они позволяют вам выбирать вариант расчётов.
Например, в функции ВПР, если вы не укажете четвертый аргумент, будет выполнен приблизительный поиск. Если вы укажете его как ЛОЖЬ (или 0), то будет найдено точное совпадение.
Если в вашей пользовательской функции есть хотя бы один обязательный аргумент, то он должен быть записан в начале. Только после него могут идти необязательные.
Чтобы сделать аргумент необязательным, вам просто нужно добавить «Optional» перед ним.
Теперь давайте посмотрим, как создать функцию в VBA с необязательными аргументами.
Этот код извлекает текст из ячейки. Optional CaseText = False означает, что аргумент CaseText необязательный. По умолчанию его значение установлено FALSE.
Если необязательный аргумент CaseText имеет значение TRUE, то возвращается результат в верхнем регистре. Если необязательный аргумент FALSE или опущен, результат остается как есть, без изменения регистра символов.
Думаю, что у вас возник вопрос: «Могут ли в пользовательской функции быть только необязательные аргументы?». Ответ смотрите ниже.
Только с необязательным аргументом.
Насколько мне известно, нет встроенной функции Excel, которая имеет только необязательные аргументы. Здесь я могу ошибаться, но я не могу припомнить ни одной такой.
Но при создании пользовательской такое возможно.
Перед вами функция, которая записывает в ячейку имя пользователя.
Function UserName(Optional Uppercase As Variant)
If IsMissing(Uppercase) Then Uppercase = False
If Uppercase Then UserName = UCase(UserName)
Как видите, здесь есть только один аргумент Uppercase, и он не обязательный.
Если аргумент равен FALSE или опущен, то имя пользователя возвращается без каких-либо изменений. Если же аргумент TRUE, то имя возвращается в символах верхнего регистра (с помощью VBA-оператора Ucase). Обратите внимание на вторую строку кода. Она содержит VBA-функцию IsMissing, которая определяет наличие аргумента. Если аргумент отсутствует, оператор присваивает переменной Uppercase значение FALSE.
Можно предложить и другой вариант этой функции.
В этом случае необязательный аргумент имеет значение по умолчанию FALSE. Если функция будет введена без аргументов, то значение FALSE будет использовано по умолчанию и имя пользователя будет получено без изменения регистра символов. Если будет введено любое значение кроме нуля, то все символы будут преобразованы в верхний регистр.
Возвращаемое значение — массив.
В VBA имеется весьма полезная функция — Array. Она возвращает значение с типом данных Variant, которое представляет собой массив (т.е. несколько значений).
Пользовательские функции, которые возвращают массив, весьма полезны при хранении массивов значений. Например, Months() вернёт массив названий месяцев:
Обратите внимание, что функция выводит данные в строке, по горизонтали.
В Office365 и выше можно вводить как обычную формулу, в более ранних версиях – как формулу массива.
А если необходим вертикальный массив значений?
Мы уже говорили ранее, что созданные нами функции можно использовать в формулах Excel вместе со стандартными.
Используем Months() как аргумент функции ТРАНСП:
Как можно использовать пользовательские функции с массивом данных? Можно применять их для ввода данных в таблицу, как показано на рисунке выше. К примеру, в отчёте о продажах не нужно вручную писать названия месяцев.
Можно получить название месяца по его номеру. Например, в ячейке A1 записан номер месяца. Тогда название месяца можно получить при помощи формулы
Альтернативный вариант этой формулы:
Согласитесь, написанная нами функция делает формулу Excel значительно проще.
Эта статья откроет серию материалов о пользовательских функциях. Если мне удалось убедить вас, что это стоит использовать или вы хотели бы попробовать что-то новое в Excel, следите за обновлениями;)
ТОП-8 советов для новичков в Android
За годы использования того или иного смартфона мы очень сильно привыкаем к его интерфейсу. На интуитивном уровне знаем как сделать скриншот или поставить будильник. Все меняется с покупкой нового телефона. Ничего не понятно, куча каких-то настроек и приложений. Те, кто испытывал похожие ощущения, точно поймут меня. Обычно на формирование привычки к базовым функциям уходит целая неделя, а то и больше. Сегодня я предлагаю вам разорвать шаблон и освоить все основные фишки Android уже с первого дня, чтобы в дальнейшем подобных проблем у вас не возникало.
Эти советы помогут разобраться с новым Android-девайсом за считанные минуты.
Конечно, в современном мире есть только два варианта, при которых вам придется осваивать Android. Либо до этого вы всегда пользовались айфоном, либо смартфон у вас в руках впервые. Как бы прискорбно это ни звучало, операционок для смартфонов не так уж и много. Вот и выбирай: Android или iOS. Изредка еще встречаются Windows Phone, но это уж совсем динозавры.
Как сделать скриншот на Андроид
Скриншот — дело первой необходимости. Даже если в каких-то других настройках запутаетесь, всегда можно отправить снимок экрана и наглядно продемонстрировать проблему. К слову, наш Телеграм-чат для вас открыт 24/7, 7 дней в неделю. Смело обращайтесь при возникновении трудностей. Либо мы, либо другие пользователи вам обязательно помогут.
Чтобы сделать скриншот на Android, вам необходимо:
Скриншот — одна из основных функций смартфона. Без нее никак.
Все, скриншот сохранится в приложении Фото (Галерея). Найти его можно в одноименном альбоме. Отправить скриншот или внести какие-либо правки можно прямо здесь. Для этого существуют специальные значки «Поделиться» и «Изменить».
Как сделать запись экрана на смартфоне
Вы уже многому научились и готовы раздавать советы товарищам? Для этого вам пригодится функция записи экрана. Зачем? Да много зачем. Отправить в Вотсапп прикол из какого-нибудь закрытого аккаунта, рассказать о том, как работает приложение Zenly. Способов применения у этой фишки, правда, очень много.
Запись экрана на Android.
Если вдруг в меню быстрых настроек функции не оказалось, рекомендуем вам подборку лучших приложений для записи экрана. С их помощью кроме базовых функций откроются и дополнительные параметры, такие как качество видео, частота кадров, ориентация экрана и замедленная запись.
Как раздать интернет на Android
Режим модема на смартфоне — одна из моих любимых функций. С ее помощью я могу пользоваться интернетом на ноутбуке или планшете, и ни в чем себе не отказывать. Во-первых, отпала необходимость покупки планшета с SIM-картой. Во-вторых, вместо душного офиса можно поработать с ноутбуком где-нибудь в парке. В-третьих, раздать интернет товарищу, если возникли какие-то проблемы с оператором.
Раздавайте Wi-Fi и будьте счастливы.
Активировать режим модема на Андроид очень просто. Для этого вам необходимо:
Точка доступа Wi-Fi на Android.
Если что-то пошло не по плану и второе устройство не видит сеть Wi-Fi, рекомендую обратиться к материалу автора AndroidInsider.ru Ивана Кузнецова. В статье он разобрал все популярные проблемы и рассказал о способах их решения. Ну и не забывайте, что оператор может запретить раздачу интернета и искусственно снизить скорость.
Как очистить кэш на Андроид
Кэш – это небольшой объём данных на вашем смартфоне, который приложения, сайты и другие сервисы сохраняют в памяти для более быстрого доступа к ним при следующих запусках программы.
Первое, на чем хотелось бы заострить ваше внимание: не скачивайте лишние сторонние приложения для очистки кэша. Они вам попросту ни к чему. Сами справитесь ничуть не хуже. Плюс, такие программы зачастую работают в фоновом режиме, используют системные ресурсы и собирают разные данные.
Очищайте кэш осмысленно.
Внимание: все изменения будут необратимы. Хорошо подумайте, оправданно ли их удаление. В конце концов кэширование данных предназначено, чтобы приносить вам пользу.
Как посмотреть экранное время на Android
Думаю, что многие пользователи задавались вопросом времени, проведенного за смартфоном. Цели могут быть разные: побороть зависимость от социальных сетей или игр, оценить количество часов, чтобы выстроить свой план на день и многое другое.
Чтобы узнать количество времени в любом приложении, вам необходимо:
Здесь будет указываться время, которое вы проводите за смартфоном.
Здесь хранится вся статистика по времени. Если же вы хотите посмотреть сколько времени был включен экран на вашем телефоне, сделать это можно здесь же:
К слову, почти на любом Android-устройстве можно настроить Родительский контроль или Цифровое благополучие, с помощью которого можно чутко контролировать свое время и время ребенка. Мы касались этого вопроса ранее, рекомендую к прочтению.
Как посмотреть сохраненные пароли на смартфоне
Несмотря на наличие встроенного инструмента во всех современных смартфонах, добраться до менеджера паролей на Android не так-то просто. Если попытаться отыскать его самостоятельно, можно потратить минут 30 точно. А на деле вот где он оказывается:
Узнайте все пароли, которые сохранены на телефоне.
Круто еще, что даже если вы решите удалить то или иное приложение, смартфон запомнит все данные и необходимость в повторном заполнении полностью отпадает. Технологии, что еще скажешь.
Как привязать карту на Андроид
Платить с телефона или часов — отдельный вид наслаждения. Сразу отпадает необходимость в пластиковых картах, которые засоряют кошелек. Куда бы ни пошли, деньги при вас. Телефон ведь с собой. Еще круто, что привязать можно даже те карты, которые не имеют физических носителей (виртуальные).
Чтобы привязать банковскую карту к Google Pay, вам необходимо:
Привяжите карту сразу же. Не известно, когда она вам понадобится.
Как отключить безопасный режим
Безопасный режим — это функция запуска смартфона только с самыми важными системными приложениями. В этом режиме автозапуск блокируется — то есть, те программы, которые загружаются в обычном режиме, в данный момент не работают.
Безопасный режим может помочь вам при возникновении трудностей с телефоном.
Ранее мы подробно рассказывали о том, зачем нужен безопасный режим и подробно касались всех нюансов его работы. Но если вы не знаете, что это вообще такое и вошли в него случайно, вот инструкция к выходу:
Повторюсь. При возникновении любых трудностей не стесняйтесь обращаться к нам в Телеграм-чат. Все расскажем и покажем. А чтобы не пропустить еще больше новостей из мира мобильных технологий рекомендую подписаться на наш новостной канал.
Новости, статьи и анонсы публикаций
Свободное общение и обсуждение материалов
На чём зарабатывает Telegram? Популярность этого запроса в Google ну просто зашкаливает. Людям действительно интересно, что откуда Павел Дуров берёт деньги. Ведь логично, что бесплатным мессенджер быть не может. А поскольку пользователи за него не платят, значит, Telegram зарабатывает на чём-то ещё. Но на самом деле это не так. На данный момент мессенджер полностью убыточен и существует только на личные деньги его создателя и инвесторов, которые верят в проект и вкладывают в него свои деньги. Однако рано или поздно Telegram должен был начать зарабатывать, и, судя по всему, он уже начал это делать.
Уже давно в Сети обсуждают вероятность блокировок сервисов Google в целом и YouTube в частности. Заблокируют или не заблокируют, а если заблокируют, то будет ли можно обойти блокировку? Такие вопросы теперь задают друг другу пользователи, которым эта тема интересна. Мы-то, конечно, знаем, что никто ничего блокировать не собирался, даже если такую инициативу обсуждали на самом верху. Но все так увлеклись темой гипотетической блокировки YouTube, что совсем забыли обо всём остальном.
Сам не знаю, зачем, но почему-то очень многих интересует вопрос, можно ли запускать Android-приложения на Windows. Для чего им это нужно и почему нельзя пользоваться Android-приложениями просто на Android, непонятно. Тем не менее, практика показывает, что аудитория пользователей, которым это жизненно необходимо, довольно широка. Поэтому ради них создаются различные эмуляторы и компиляторы, которые делают возможным запуск мобильного софта на десктопных компьютерах. Другое дело, что всё это – суррогат и костыли. Но теперь это останется в прошлом.