Создание пользовательских функций в Excel
Хотя в Excel предлагается большое число встроенных функций, в нем может не быть той функции, которая нужна для ваших вычислений. К сожалению, разработчики Excel не могли предугадать все потребности пользователей. Однако в Excel можно создавать собственные функции, и ниже вы найдете все нужные для этого инструкции.
Пользовательские функции (как и макросы) записываются на языке программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Из этой статьи вы узнаете, как создавать и применять пользовательские функции. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне.
Предположим, что ваша компания предоставляет скидку в размере 10 % клиентам, заказавшим более 100 единиц товара. Ниже мы объясним, как создать функцию для расчета такой скидки.
В примере ниже показана форма заказа, в которой перечислены товары, их количество и цена, скидка (если она предоставляется) и итоговая стоимость.
Чтобы создать пользовательскую функцию DISCOUNT в этой книге, сделайте следующее:
Нажмите клавиши ALT+F11 (или FN+ALT+F11 на Mac), чтобы открыть редактор Visual Basic, а затем щелкните Insert (Вставка) > Module (Модуль). В правой части редактора Visual Basic появится окно нового модуля.
Скопируйте указанный ниже код и вставьте его в новый модуль.
Примечание: Чтобы код было более удобно читать, можно добавлять отступы строк с помощью клавиши TAB. Отступы необязательны и не влияют на выполнение кода. Если добавить отступ, редактор Visual Basic автоматически вставит его и для следующей строки. Чтобы сдвинуть строку на один знак табуляции влево, нажмите SHIFT+TAB.
Теперь вы готовы использовать новую функцию DISCOUNT. Закройте редактор Visual Basic, выделите ячейку G7 и введите следующий код:
Excel вычислит 10%-ю скидку для 200 единиц по цене 47,50 ₽ и вернет 950,00 ₽.
В первой строке кода VBA функция DISCOUNT(quantity, price) указывает, что функции DISCOUNT требуется два аргумента: quantity (количество) и price (цена). При вызове функции в ячейке листа необходимо указать эти два аргумента. В формуле =DISCOUNT(D7;E7) аргумент quantity имеет значение D7, а аргумент price — значение E7. Если скопировать формулу в ячейки G8:G13, вы получите указанные ниже результаты.
Рассмотрим, как Excel обрабатывает эту функцию. При нажатии клавиши ВВОД Excel ищет имя DISCOUNT в текущей книге и определяет, что это пользовательская функция в модуле VBA. Имена аргументов, заключенные в скобки ( quantity и price), представляют собой заполнители для значений, на основе которых вычисляется скидка.
Оператор If в следующем блоке кода проверяет аргумент quantity и сравнивает количество проданных товаров со значением 100:
Если количество проданных товаров не меньше 100, VBA выполняет следующую инструкцию, которая перемножает значения quantity и price, а затем умножает результат на 0,1:
Discount = quantity * price * 0.1
Результат хранится в виде переменной Discount. Оператор VBA, который хранит значение в переменной, называется оператором назначения, так как он вычисляет выражение справа от знака равенства и назначает результат имени переменной слева от него. Так как переменная Discount называется так же, как и процедура функции, значение, хранящееся в переменной, возвращается в формулу листа, из которой была вызвана функция DISCOUNT.
Если значение quantity меньше 100, VBA выполняет следующий оператор:
Наконец, следующий оператор округляет значение, назначенное переменной Discount, до двух дробных разрядов:
Discount = Application.Round(Discount, 2)
В VBA нет функции округления, но она есть в Excel. Чтобы использовать округление в этом операторе, необходимо указать VBA, что метод (функцию) Round следует искать в объекте Application (Excel). Для этого добавьте слово Application перед словом Round. Используйте этот синтаксис каждый раз, когда нужно получить доступ к функции Excel из модуля VBA.
Пользовательские функции должны начинаться с оператора Function и заканчиваться оператором End Function. Помимо названия функции, оператор Function обычно включает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel доступно несколько встроенных функций (например, СЛЧИС и ТДАТА), в которых нет аргументов.
После оператора Function указывается один или несколько операторов VBA, которые проверят соответствия условиям и выполняют вычисления с использованием аргументов, переданных функции. Наконец, в процедуру функции следует включить оператор, назначающий значение переменной с тем же именем, что у функции. Это значение возвращается в формулу, которая вызывает функцию.
Количество ключевых слов VBA, которые можно использовать в пользовательских функциях, меньше числа, используемого в макросах. Настраиваемые функции не могут выполнять другие задачи, кроме возврата значения в формулу на этом или в выражение, используемом в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окна, редактировать формулу в ячейке, а также изменять шрифт, цвет или узор текста в ячейке. Если в процедуру функции включить такой код действия, функция возвращает #VALUE! ошибку «#ВЫЧИС!».
Единственное действие, которое может выполнять процедура функции (кроме вычислений), — это отображение диалогового окна. Чтобы получить значение от пользователя, выполняющего функцию, можно использовать в ней оператор InputBox. Кроме того, с помощью оператора MsgBox можно выводить сведения для пользователей. Вы также можете использовать настраиваемые диалоговые окна ( UserForms), но эта тема выходит за рамки данной статьи.
Даже простые макросы и пользовательские функции может быть сложно понять. Чтобы сделать эту задачу проще, добавьте комментарии с пояснениями. Для этого нужно ввести перед текстом апостроф. Например, ниже показана функция DISCOUNT с комментариями. Благодаря подобным комментариями и вам, и другим будет впоследствии проще работать с кодом VBA. Так, код будет легче понять, если потребуется внести в него изменения.
Апостроф указывает приложению Excel на то, что следует игнорировать всю строку справа от него, поэтому вы можете добавлять комментарии в отдельных строках или в правой части строк, содержащих код VBA. Советуем начинать длинный блок кода с комментария, в котором объясняется его назначение, а затем использовать встроенные комментарии для документирования отдельных операторов.
Кроме того, рекомендуется присваивать макросам и пользовательским функциям описательные имена. Например, присвойте макросу название MonthLabels вместо Labels, чтобы более точно указать его назначение. Описательные имена макросов и пользовательских функций особенно полезны, если существует множество процедур с похожим назначением.
То, как документировать макрос и пользовательские функции, имеет личный выбор. Важно принятия определенного способа документации и его согласованного использования.
Для использования настраиваемой функции должна быть открыта книга, содержащая модуль, в котором она была создана. Если книга не открыта, вы получите #NAME? при попытке использования функции. Если вы ссылались на функцию в другой книге, ее имя должно предшествовать названию книги, в которой она находится. Например, при создании функции DISCOUNT в книге Personal.xlsb и вызове ее из другой книги необходимо ввести =personal.xlsb!discount(),а не просто =discount().
Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне «Вставка функции». Пользовательские функции доступны в категории «Определенные пользователем»:
Чтобы пользовательские функции всегда были доступны, можно хранить их в отдельной книге, а затем сохранять в качестве надстройки. Затем надстройку можно сделать доступной при запуске Excel. Вот как это сделать:
Создав нужные функции, выберите Файл > Сохранить как.
В Excel 2007 нажмите кнопку Microsoft Office, а затем щелкните Сохранить как.
В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions, в папке AddIns. Она будет автоматически предложена в диалоговом окне Сохранить как, поэтому вам потребуется только принять расположение, используемое по умолчанию.
Сохранив книгу, выберите Файл > Параметры Excel.
В Excel 2007 нажмите кнопку Microsoft Office и щелкните Параметры Excel.
В диалоговом окне Параметры Excel выберите категорию Надстройки.
В раскрывающемся списке Управление выберите Надстройки Excel. Затем нажмите кнопку Перейти.
В диалоговом окне Надстройки установите флажок рядом с именем книги, как показано ниже.
Создав нужные функции, выберите Файл > Сохранить как.
В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions.
Сохранив книгу, выберите Сервис > Надстройки Excel.
В диалоговом окне Надстройки нажмите кнопку «Обзор», найдите свою надстройку, нажмите кнопку Открыть, а затем установите флажок рядом с надстройкой в поле Доступные надстройки.
После этого пользовательские функции будут доступны при каждом запуске Excel. Если вы хотите добавить его в библиотеку функций, вернимся в Visual Basic редактора. Если вы заглянуть в Visual Basic редактора Project проводника под заголовком VBAProject, вы увидите модуль с именем файла надстройки. У надстройки будет расширение XLAM.
Дважды щелкните модуль в Project Explorer, чтобы вывести код функций. Чтобы добавить новую функцию, установите точку вставки после оператора End Function, который завершает последнюю функцию в окне кода, и начните ввод. Вы можете создать любое количество функций, и они будут всегда доступны в категории «Определенные пользователем» диалогового окна Вставка функции.
Эта статья основана на главе книги Microsoft Office Excel 2007 Inside Out, написанной Марком Доджем (Mark Dodge) и Крейгом Стинсоном (Craig Stinson). В нее были добавлены сведения, относящиеся к более поздним версиям Excel.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Источник
Эта хитрая функция улучшит качество изображения на ваших умных ТВ и приставках. Включаем AFR
Производители электроники ежегодно добавляют всевозможные фишки и опции, чтобы продать нам новый гаджет взамен старого, который вполне нормально работает. Мы видим это на примере наших любимых iPhone, iPad и Mac. Менять устройства каждый год нет особого смысла, иногда даже переход через 2-3 поколения не даёт серьёзных преимуществ в повседневном использовании.
Нечто подобное происходит и на рынке телевизоров. В арсенале каждой компании есть с десяток полезных и не очень фишек, которые во всю продвигают маркетологи и навязывают консультанты в магазинах.
Есть одна наиболее важная и полезная опция, наличие которой нужно проверять при покупке телевизора в 2021 году – AFR. Эта фишка гарантирует комфортное воспроизведение видео с любого источника с любыми параметрами картинки.
Включить AFR можно как в самом телевизоре, так и добавить поддержку при помощи подходящей ТВ-приставки. Сейчас расскажем, что это за опция и как проверить её работу.
Зачем нужен AFR
AFR (Auto Frame Rate) – это способность телевизора или приставки автоматически менять частоту выходного сигнала под частоту воспроизводимого контента.
На сегодняшний день существует общепринятый мировой стандарт частоты кадров для киносъёмки – 24 кадра в секунду. Кроме этого есть множество других распространённых стандартов:
► 25 кадров в секунду – частота кадров во время съёмки видео для перевода в европейский стандарт разложения 625/50.
► 26 кадров в секунду – частота съёмки для панорамной системы Синерама (Cinerama).
► 29,97002616 кадров в секунду – используемая в телевизионном стандарте NTSC, частота кадров. Стандарт распространён в странах Северной Америки и части стран Азии.
► 30 кадров в секунду – частота кадров, которая применялась в ранних версиях широкоформатного кинематографа и IMax.
► 48 кадров в секунду – частота кадров, которая используется в современных системах IMAX HD и Maxivision 48.
► 50 кадров в секунду – частота кадров в европейском стандарте телевидения высокой чёткости.
► 59,94 кадров в секунду — точная полукадровая частота телевидения высокой чёткости для стандарта NTSC.
► 60 кадров в секунду – частота киносъёмки для американского стандарта телевидения высокой чёткости.
Добавьте к этому консоли, смартфоны, видео- и экшн-камеры и записанный на компьютере стриминговый контент, который тоже может иметь свою частоту кадров и не совпадать с телевизионной.
Как видите, во всем мире существует множество разных стандартов для производства видеоконтента, каждый из которых имеет свою частоту кадров. При этом количество фреймов из одного стандарта сложно привести к другому стандарту без видимых потерь, артефактов или побочных эффектов.
Что такое judder эффект и как его увидеть
Одной из неприятных особенностей данной ситуации является так называемый judder-эффект. Если картинка в видео выше понравилась, и вы не заметили ничего необычного, то быстрее закройте данную статью и забудьте про Auto Frame Rate навсегда.
Если же за тестовые 20 секунд глаза сильно напряглись и начали уставать – продолжаем изучать тему.
С judder-эффектом сталкивается любой покупатель нового телевизора или Smart-TV бокса. В рекламном ролике или магазине на тестовых стендах транслируются специальные ролики, который сняты с поддерживаемой для каждой модели частотой кадров и разрешением. Все выглядит максимально плавно, эффектно и реалистично.
Но когда счастливый обладатель нового “телека” приносит его домой и начинает воспроизводить свой контент, его ждёт небольшое разочарование.
У поставщика кабельного телевидения или T2 используется одна частота кадров, вещающие в цифровом формате IPTV-каналы имеют другую частоту, контент в стриминговых видеосервисах настроен на третью частоту. Загруженные вами видео могут как совпадать по частоте с любым из перечисленных вариантов, так и иметь свой уникальный показатель.
Если количество кадров в секунду у контента совпадёт с настройками ТВ (или будет кратно параметрам), пользователь увидит чёткую картинку без рывков и размытия. В противном случае будет наблюдаться тот самый judder-эффект.
Большинство современных телевизоров поддерживают работу на частоте 60 Гц или 120 Гц. При этом они без проблем справляются с контентом, который снят с частотой 30 или 60 кадров в секунду. Всё это кратные значения и, например, панель с частотой 120 Гц при воспроизведение ролика с частотой 30 кадров в секунду будет отображать каждый кадр по четыре раза.
Так же гладко пройдёт воспроизведение 24-кадрового ролика на экране с частотой 120 Гц (по пять повторений каждого кадра). А вот на экране с максимальной частотой 60 Гц 24-кадровое видео уже будет выглядеть неидеально.
Вот так это выглядит на графике:
Трансляция 24-кадрового контента на частоте 60 Гц
Получается так называемый эффект “телесин” в соотношении два к трём. Один кадр видео телевизор будет отображать 2/60 доли секунды, а следующий кадр видео будет длиться 3/60 доли секунды и так далее. Глаз человека очень чётко заметит такой эффект дрожания или подтормаживания картинки. Не будет общего ощущения плавности, любой голливудский шедевр превратится в любительское видео с дешёвой камеры.
Всевозможные системы сглаживания (или так называемые “уплавнялки”) сейчас есть в арсенале любого крупного производителя телевизоров и матриц. Умные системы способны добавлять недостающие кадры и делать частоту фреймов кратной частоте выводимого сигнала. Так в нужных местах появится лишний кадр, и указанного выше эффекта наблюдаться не будет.
Наглядное сравнение картинки можете увидеть на тестовом видео ниже. Все кадры в правом ролике воспроизводятся с одинаковой частотой, а слева каждый второй кадр длится заметно дольше. Некоторые увидят разницу только при замедлении видео, а некоторые смогут разглядеть эффект и в динамике.
К сожалению, работает данная фишка не всегда правильно. При просмотре динамических роликов или спортивных трансляций judder-эффект максимально заметен. Так футбольный мяч после удара превращается в комету или дыню, либо автомобиль во время ускорения резко меняет свою форму и становится смазанным. В эти моменты встроенная в телевизор система помогает добавить недостающие кадры и сделать картинку более чёткой.
Эта же система способна испортить просмотр динамических сцен в кино. Когда, по задумке режиссёра, кадр должен иметь эффект размытия или быть смазан, телевизор делает его слишком резким и появляется эффект съёмки на любительскую камеру.
Чтобы полностью избавиться от judder-эффекта, частота выходного сигнала должна быть равна или кратна показателю fps воспроизводимого видео. Только такой способ трансляции позволить избежать видимых искажений и смотреть контент в таком виде, как задумали его создатели.
Что даёт Auto Frame Rate
Параметры Auto Frame Rate с поддержкой смены частоты и разрешения экрана
Именно для этого и нужна фишка под названием “Auto Frame Rate“. Наличие данной опции в телевизоре или приставке будет лучше сотни искусственных “уплавнялок” и “сглаживалок” картинки.
В идеале система должна подстраивать не только частоту кадров, но и разрешение. Так получится смотреть контент без каких-либо искажений. Телевизор при этом не будет делать апскейл картинки, самостоятельно дорисовывая несуществующие пиксели.
Разделяют два вида Auto Frame Rate: системный и программный. В первом случае фишка включается на уровне всей используемой системы (телевизионной ОС или операционки ТВ-бокса), а во втором – только в конкретном приложении-плеере.
Настройка Auto Frame Rate на уровне операционной системы Android TV
Первый случай более удобный, не требует дополнительных настроек и срабатывает сразу же после активации. Просто находим тумблер “Auto Frame Rate” в параметрах своей панели или в настройках Android TV и активируем его. Фишка появилась в Android TV начиная с шестой версии и есть в параметрах многих фирменных ТВ-оболочек.
После активации система будет пытаться распознать количество кадров в любом просматриваемом контенте и автоматически подстраивать частоту экрана под видео. С одной стороны это удобно, но с другой – любая смена видеорежима приводит к пропаданию картинки на 1-3 секунды.
Если просто включить фильм и смотреть его за один раз без перерывов, потерпеть пару секунд на смену режима можно. Другое дело, когда вы будете смотреть телевизионные каналы, IPTV, YouTube или просто трейлеры фильмов в каталоге стримингового сервиса. При переключении на новый канал или при начале воспроизведения любого ролика картинка на экране будет пропадать на несколько секунд. Подобный эффект возможен даже при перемотке видео.
Включение Auto Frame Rate в популярном плеере Vimu на Android TV
Программный способ требует настройки, но имеет неоспоримые преимущества. Можно включить Auto Frame Rate только в нужных приложениях. Есть возможность задать отсрочку его включения, например, установить 5-секундную задержку, которой хватит для пауз при переключении каналов или перемотке видео. Когда окончательно выберите контент для просмотра, приложение активирует Auto Frame Rate и сменит частоту экрана.
Многие сторонние приложения-плееры имеют умные алгоритмы активации AFR и не требуют от пользователя никаких ручных манипуляций. Утилиты вроде KODI, Smart Youtube, Perfect Player, Vimu, Amazon Video, TiviMate и другие уже имеют встроенный Auto Frame Rate.
Как проверить правильную работу Auto Frame Rate
Во-первых, фишку должен поддерживать телевизор или подключенный TV-бокс. В случае с приставкой телевизор должен уметь менять частоту вывода и разрешение по HDMI. Так умеет большинство современных ТВ-панелей, но проверить спецификацию все-таки стоит.
Во-вторых, телевизор должен поддерживать все распространённые частоты FPS. Чаще всего для комфортного воспроизведения фильмов требуется поддержка 24 кадров в секунду.
В-третьих, работе Auto Frame Rate не должны мешать встроенные системы повышения чёткости изображения.
Возможны ситуации, когда заявленная поддержка AFR со стороны производителя ТВ просто не работает из-за конфликта с другими фишками либо когда опция поддерживается на приставке, но неверно воспринимается телевизором и не даёт нужного эффекта.
Если все указанные выше условия соблюдены, можно проверить правильность работы AFR в вашем конкретном случае.
1. Настройте правильное разрешение. Перейдите в параметры телевизора или используемой приставки и установите правильное разрешение для используемой матрицы. Если телевизор поддерживает разрешение 4K – устанавливайте его, не нужно ставить 1080p по умолчанию на такой панели. И наоборот – не завышайте разрешение воспроизводящего устройства, если его не поддерживает матрица.
Не всегда AFR поддерживает смену разрешения и неверно установленный параметр не позволить получить максимальное качество картинки.
2. Верно выберите подходящую частоту. Здесь все зависит от наиболее предпочитаемого вида контента. Например, при регулярном просмотре IPTV следует установить частоту кадров на значение 50. В этом случае AFR не будет менять параметры экрана в большинстве сценариев использования.
Можете воспользоваться параметрами контента, которые указаны в первом разделе статьи или узнать рекомендуемую частоту кадров у поставщика своего контента.
3. Включите фишку Auto Frame Rate. Она может иметь различные названия в зависимости от используемой операционной системы или оболочки. Например, на многих распространённых моделях TV-боксов с Android TV фишка называется HDMI self-adaption и находится в разделе Playback Settings.
4. Установите подходящий режим работы фишки (если в настройках предусмотрена такая опция). Обычно производители предлагают два режима: частичный (part mode) с переключениям режима только при указанных частотах контента или полный (total mode) с переключением на любую частоту воспроизводимого контента.
5. Скачайте тестовый ролик на приставку. И включите его воспроизведение на устройстве.
Видео будет выглядеть как на примере выше.
6. Используйте любое стороннее приложение-камеру для iOS или цифровую камеру в ручном режиме.
7. Установите выдержку на отметку 1 секунда и сделайте фото телевизора.
Если Auto Frame Rate включён и работает правильно, увидите на снимке равномерно окрашенную серую доску. Это значит, что панель за секунду отображает кадры с подсветкой каждого из квадратов.
Если Auto Frame Rate не включился или конфликтует с какой-то другой фишкой телевизора, то вы увидите изображение-шахматку. Оно свидетельствует о том, что за секунду панель не успевает отобразить все нужные кадры. Так можно самостоятельно убедиться в работе фишки на ваших устройствах.
Теперь вы знаете, за какой фишкой гнаться при выборе современного телевизора в 2021 году.
Источник