Элементы управления формы в EXCEL

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

Для вставки элементов управления на лист необходимо отобразить вкладку Разработчик.

  • В MS EXCEL 2007 это можно сделать через меню Кнопка офис/ Параметры Excel/ Основные/ Показывать вкладку Разработчик на ленте .
  • В MS EXCEL 2010 это можно сделать так: Откройте вкладку Файл ; Нажмите кнопку Параметры ; Нажмите кнопку Настроить ленту ; Выберите команду Настройка ленты и в разделе Основные вкладки установите флажок Разработчик .

Теперь вставить элемент управления можно через меню: Разработчик/ Элементы управления/ Вставить .

Обратите внимание, что в этом меню можно вставить Элементы ActiveX, которые расположены ниже интересующих нас Элементов управления формы. У обоих типов есть одни и те же элементы Кнопка, Список, Флажок и т.п. Разница между ними следующая: чтобы использовать Элементы ActiveX необходимо использовать VBA, а Элементы управления формы можно напрямую привязать к ячейке на листе.

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

  • Флажок ;
  • Счетчик ;
  • Полоса прокрутки ;
  • Переключатель ;
  • Список ;
  • Поле со списком .

В этой статье рассмотрим более сложный пример совместного использования элементов управления и Условного форматирования .

Форма Excel. Вводим данные!

Для чего нужна какая-то форма?! Бери таблицу — и пиши прямо в нее очередную строку информации! Зачем еще что-то выдумывать?

Мы так и делали при создании нашего небольшого и простого примера базы данных БД2 «Выпуск металлоконструкций участком №2», с которым работаем во всех статьях этого цикла.

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

При вводе данных в такие таблицы Excel «напрямую» при заполнении строк для перемещения по столбцам необходимо каждый раз нажимать клавишу «Tab», а для перехода к новой записи на новую строку пользоваться прокруткой и мышью или клавишами «Enter» и «<—» («стрелка влево») для возврата на первое поле очередной строки.

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

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

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

Задача №8:

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

Рассмотрим ввод данных в Excel при помощи формы на примере добавления очередной записи в базу данных БД2 «Выпуск металлоконструкций участком №2».

1. Открываем в MS Excel файл database.xls.

2. Активируем («щелкаем мышкой») любую ячейку внутри таблицы базы.

3. Выполняем команду главного меню программы «Данные» — «Форма…».

4. В появившемся окне, представляющем собой форму с именем «БД2» (по имени листа книги Excel, на котором расположена таблица), нажимаем кнопку «Добавить».

5. Заполняем окна данными новой записи, например, так, как показано ниже на снимке экрана. Переходим от окна к окну при помощи клавиатуры, нажимая клавишу «Tab».

Окно Excel "БД2"-14s

6. Нажимаем кнопку формы «Закрыть» или «Enter» на клавиатуре. Ввод данных в Excel успешно осуществлен — новая запись добавлена в базу!

База данных-ввод данных в Excel-14s

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

Если в форме «БД2» воспользоваться полосой прокрутки или кнопками «Далее» и «Назад», то можно, быстро перемещаясь по базе данных, просматривать интересующие записи.

Объекты, коллекции, свойства и методы

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

Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.

Что касается понятия «коллекция», то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.

Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в «Эксель» используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами (1,1) т.е. A1.

Вместе с ней нередко используется Selection.ClearContents. Ее выполнение означает очистку содержимого выбранной ячейки.

циклы VBA

Работа с Excel с помощью C# (Microsoft.Office.Interop.Excel): 11 комментариев

Как прочитать данные из ячейки excel,и записать эти данные в sql server?

Добрый день.
Отправил на почту.

Добрый день.
А мне можно тоже самое?)

Здравствуйте, как изменить цвет диаграммы при работе в C#?(круговая диаграмма)

Вы можете записать макрос на изменение цвета в Visual Basic и списать получившийся код. Затем использовать его в своей программе.

Здравствуйте.
Можете подсказать, как «заставить» приложение работать с разными версиями MS Office? На машине разработчика стоит Office 2010, при запуске на машине с 2003-м — увы — ошибка.

Microsoft.Office.Interop.Excel это довольно старый способ работать с Excel документами.
Что касается версии Office 2003, то он использует совсем другой драйвер.
Соответственно версия Microsoft.Office.Interop.Excel.dll нужна старая, плюс драйвер microsoft jet 4.0, который на новых системах (Win 8, 10) работает неправильно.
Единственное, что могу посоветовать, так это скачать Microsoft Office Compatibility Pack для Office 2003, чтобы научить его открывать xslx документы.
А в своей программе использовать не Interop.Excel, а библиотеку EPPlus. Она работает с excel документами, используя технологию OpenXml и не надо париться по поводу драйверов.
Код будет очень похож на Interop.Excel-ный.

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

Скажите пожалуйста, как прочитать данные из ячейки Excel и записать их в SQL Server?

COM DLL (Office Developer Tools – ODT)

Описание: полнофункциональная надстройка Excel
Требования: ODE 97 или ODT XP
Достоинства: возможность создания интерфейса любой сложности; полностью закрытый исходный код.
Недостатки: невозможно создание пользовательских функций для работы с данными (UDF)

Версии Excel 97 (8.0), 2000 (9.0) и XP (10.0) имели специальный вариант пакетов для разработчика под названием Developer Edition (ODE) или Developer Tools (ODT). В версиях ODE 9.0 и ODT 10.0 имелась возможность создавать библиотеки формата ActiveX DLL непосредственно при помощи пакета Office без использования внешних компиляторов. К сожалению, в последующих версиях Excel эта возможность была удалена, Microsoft отсылает разработчиков к технологии Visual Studio Tools for Office (VSTO) с использованием .NET Framework.

Главной особенность Excel ODT является наличие возможности создания проекта VBA независимого от xls-файла, с возможностью последующей компиляции данного проекта в файл формата DLL (ActiveX DLL).

Исходный VBA-проект содержит специальный дизайнер с методами обработки событий Excel. В проекте, кроме этого, доступны для создания обычные модули кода, классы и формы. Причем формы могут быть открыты немодально. Надстройки данного типа могут быть подключены к различным версиям Excel одновременно. Файл DLL, скомпилированный с помощью ODE/ODT не требует никаких дополнительных исполняемых модулей для своей работы, кроме, собственно говоря, Excel.

Здесь важное замечание, что, даже если вас заинтересовали возможности ODE/ODT, вам вряд ли удастся приобрести лицензионные версии этих продуктов – Microsoft с 2003го года прекратил их распространение. Вообще же вероятно, что в России, официальным способом было приобретено совсем небольшое количество копий пакетов для разработчиков, поэтому искать пиратскую версию этих продуктов тоже особого смысла не имеет.

Настройка ленты Office 2007:

В отличие от панелей инструментов, новый пользовательский интерфейс Excel 2007 в виде ленты (ribbons) не имеет встроенных механизмов настройки напрямую через VBA.

Читайте также:  Как найти одинаковые значения в столбце Excel

Имеется довольно странный механизм настройки ленты через редактирование xml-файла. Формат xlsx представляет из себя zip-архив нескольких файлов и папок, в одном из которых доступна настройка на ленте пользовательских функций. Странно, но разработчики Excel почему-то не предоставили интерфейс для настройки ленты иным способом. Подробнее см. MSDN:

Вызов процедур COM-DLL из VBA

В тех случаях, когда основной алгоритм закрыт в COM-надстройке, вызов функций и процедур (например, из обработчиков пунктов меню) осуществляется через механизм позднего связывания (Late-bound). Для получения доступа к объекту надстройки используется функция COMAddIns объекта Application с указанием имени COM-надстройки. Например:

Массивы и типы

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

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

Типы данных – значение, хранящееся в ячейке, больше не является просто строкой или числом. Одна ячейка может содержать расширенный тип данных с большим набором свойств.

Пример применения функций

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

Имеем список работников предприятия с положенными им заработными платами. Но, кроме того, всем работникам положена премия. Обычная премия составляет 700 рублей. Но пенсионерам и женщинам положена повышенная премия в размере 1000 рублей. Исключение составляют работники, по различным причинам проработавшие в данном месяце менее 18 дней. Им в любом случае положена только обычная премия в размере 700 рублей.

Попробуем составить формулу. Итак, у нас существует два условия, при исполнении которых положена премия в 1000 рублей – это достижение пенсионного возраста или принадлежность работника к женскому полу. При этом, к пенсионерам отнесем всех тех, кто родился ранее 1957 года. В нашем случае для первой строчки таблицы формула примет такой вид: =ЕСЛИ(ИЛИ(C4<1957;D4=»жен.»);»1000″;»700″) . Но, не забываем, что обязательным условием получения повышенной премии является отработка 18 дней и более. Чтобы внедрить данное условие в нашу формулу, применим функцию НЕ: =ЕСЛИ(ИЛИ(C4<1957;D4=»жен.»)*(НЕ(E4<18));»1000″;»700″) .

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

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

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

Как создать пользовательский список в Excel

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

  1. Чтобы создать свой собственный список, нажмите файл Вкладка.
    как создавать собственные списки в Excel
  2. Затем нажмите Опции на экране за кулисами.
    как создавать собственные списки в Excel
  3. На Параметры Excel диалоговое окно, нажмите продвинутый. Затем прокрутите вниз до генеральный раздел справа и нажмите Редактировать пользовательские списки.
    как создавать собственные списки в Excel
  4. в Пользовательские списки в окне вы увидите предопределенные списки дней недели и месяцев года.

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

1. Введите ваш список напрямую

Первый способ создать собственный список — ввести его прямо в Пользовательские Списки диалоговое окно.

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

2. Импортируйте ячейки из листа

Второй способ создать пользовательский список — это импортировать их из ячеек в одной из ваших таблиц.

  1. Нажмите кнопку выбора ячейки на Импорт списка из ячеек коробка.
    как создавать собственные списки в Excel
  2. Пользовательские Списки диалоговое окно сокращается до Импорт списка из ячеек коробка. Выберите лист, содержащий список, который вы хотите импортировать. Затем выберите диапазон ячеек, содержащих элементы в списке, и нажмите кнопку справа от Импорт списка из ячеек коробка.
    как создавать собственные списки в Excel
  3. Пользовательские Списки диалоговое окно снова раскрывается. Вы можете заметить, что заголовок диалогового окна меняется на Опции. Это все тот же диалог, хотя. Нажмите Импортировать добавить элементы списка с рабочего листа в Список записей коробка.
    как создавать собственные списки в Excel
  4. Этот метод позволяет легко добавить пользовательский список, который у вас уже есть в ячейках вашей рабочей книги.

3. Импортируйте список из именованного диапазона ячеек

Третий способ создания настраиваемого списка — это импорт списка из именованного диапазона ячеек.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Количество знаков в столбце или строке

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

  1. Чтобы растянуть результат на другие строки столбца наводим указатель мыши на правый нижний угол ячейки с функцией “ДЛСТР”, как только он сменит вид на небольшой плюсик, зажав левую кнопку мыши тянем его вниз до той строки, для которой мы хотим получить аналогичный результат.
  2. Как мы можем заметить, благодаря этому нехитрому действию нам удалось в считанные секунды получить данные по количеству символов для каждой ячейки выбранного столбца.

Ранее мы упоминали, что в качестве аргумента функции “ДЛСТР” можно указать только адрес одной конкретной ячейки. Поэтому, чтобы определить суммарное количество знаков во всех ячейках столбца нужно воспользоваться одним из способов подсчета суммы значений. Подробнее ознакомиться со всеми методами можно в нашей статье – “Как посчитать в Экселе сумму столбца”.

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

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

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

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

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

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

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

Изучить возможности встроенного языка программирования Visual Basic for Application для создания пользовательских функций;

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

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

Объект исследования – управление потоками данных возможностями табличного процессора Ms Excel .

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

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

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

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

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

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

§1. Технология создания пользовательских функций

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

Операторы. Символы, например "+" (сложение) и "*" (умножение).

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

Значения или строки. Например, 7,5 или "Результаты на конец года".

Скобки. Задают порядок выполнения действий в формуле.

Функции и их аргументы. Например, СУММ или СРЗНАЧ и их аргументы.

Формула не может содержать более, чем 1024 символа. Если необходима формула, количество символов в которой превышает предельно допустимое, следует разделить ее на несколько формул или создать пользовательскую функцию (с помощью VB А ).

Функции — заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления. Например, функция ОКРУГЛ округляет число в ячейке A10 (см. рис. 1).

Рис. 1.Структура функции

Структура функции.

Структура функции начинается со знака равенства (=), за ним следует имя функции, открывающая скобка, список аргументов, разделенных запятыми, закрывающая скобка ( см. рис. 1).

Имя функции. Для появления списка доступных функций нужно щелкнуть ячейку и нажать клавишу SHIFT+F3 .

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

Всплывающая подсказка аргумента. Всплывающая подсказка с синтаксисом и аргументами появляется после ввода функции. Например, всплывающая подсказка появится после ввода = ОКРУГЛ( . Всплывающие подсказки появляются только для встроенных функций.

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

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

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

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

Функции удобны, поскольку они:

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

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

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

В Excel внедрено более 300 предопределенных функций, более того, после установки надстройки " Пакет анализа " становятся доступными дополнительные функции. Excel предоставляет возможность создания дополнительных (пользовательских) функций средствами редактора Visual Basic .

Язык программирования VB А — мощный инструмент, позволяющий Excel выполнять такие задачи, осуществить которые иными средствами чрезвычайно трудно или невозможно. С помощью VB А создаются следующие основные типы макросов:

макросы, автоматизирующие операции в Excel ;

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

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

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

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

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

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

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

Объявление функции

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

[statements] [name = expression]

[statements] [name = expression]

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

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

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

Function — ключевое слово, отображающее начало функции (обязательно).

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

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

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

Statements являются полноценными VBA-операторами (необязательно).

Exit Function — это оператор, результат которого— выход из функции (необязательно).

End Function — ключевое выражение, показывающее конец работы функции (обязательно).

Выбор имени функции

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

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

Можно использовать любую комбинацию букв верхнего и нижнего регистров.

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

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

Нельзя использовать пробелы или точки. Для придания именам функции лучшей читабельности можно использовать символ нижнего подчеркивания ( Минимальный_Элемент ).

В имя функции нельзя вставлять следующие символы: #, $, %, !. Они являются символами объявления типа, которые имеют специальное назначение в VB А .

Имя функции может состоять не более чем из 255 символов.

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

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

Рис. 2. Tools  References

ибо добавить ссылку на эту книгу. Например, чтобы использовать функцию Минимальный_Элемент , объявленную в рабочей книге Мои_функции. xls , следует использовать следующую формулу: =Мои_функции. xls !Минимальный_Элемент( Al : A 1000) . Если вставить функцию с помощью диалогового окна вставки функции, ссылка на рабочую книгу будет вставлена автоматически.

или настр оить ссылку на рабочую книгу. Это можно сделать с помощью средств редактора Visual Basic Tools  References (см. рис.2). Если функция объявлена в рабочей книге ссылки, имя рабочего листа использовать нет необходимости. Даже когда зависимая рабочая книга задана как ссылка, диалоговое окно вставки функции продолжит вставлять ссылку на рабочую книгу (даже если это не необходимо).

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

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

Активизировать редактор Visual Basic , выбрать рабочую книгу в окне проекта. Выберать Tools  VBAProject Properties и перейти на вкладку Protection. Установить флажок опции Lock Project for Viewing и дважды ввести пароль. Эту операцию нужно выполнить только в том случае, если вы хотите предохранить ваш макрос от вмешательства в него или в диалоговые окна постороннего пользователя.

3. Возобновить работу Excel . Выбрать команду Файл  Свойства , щелкнуть на вкладке Документ и ввести краткое описательное название в поле Название , и более длинное описание в поле Заметки .

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

Выбрать в меню команду Файл  Сохранить как.

В диалоговом окне сохранения выбрать из списка типов файла опцию Надстройка Microsoft Excel (*.xla) .

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

Щелкнуть на кнопке Сохранить . Копия рабочей книги будет сохранена (с расширением . xla ) как надстройка, а исходная рабочая книга останется открытой.

После создания надстройки можно установить ее, используя стандартную процедуру: выбрать Сервис  Надстройки и щелкнуть в появившемся диалоговом окне на кнопке Обзор . Указать расположение файла * . xla .

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

Функция может не иметь аргументов.

Функция может иметь фиксированное количество обязательных аргументов (от 1 до 60).

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

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

В VBA есть множество встроенных функций, упрощающих вычисление и выполнение операций. В выражении х = Sqr ( MyValue ) вычисляется квадратный корень с помощью функции Sqr , а результат присваивается переменной х. Многие из функций VBA схожи с функциями рабочих листов Excel. Например, функция VBA Len , которая возвращает длину строки, эквивалентна функции ДЛСТР рабочего листа Excel .

Имея представление о функциях VBA , можно избавить себя от большого объема работы. В коде VBA можно использовать многие (но не все) функции рабочего листа Excel. Чтобы в выражении VBA использовать функцию рабочего листа, предварительно необходимо указать ключевое слово WorksheetFunction или Application . Следующие выражения являются эквивалентными:

Result = Application.Max(x, у, z)

Result = WorksheetFunction.Max(x, y, z)

Нижеприведенный код демонстрирует использование функции Excel в выражении VBA . Редко используемая в Excel функция Римское ( Roman ) преобразует десятичные числа в римские.

Переменная RValue содержит строку MCMXCIX .

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

х = Application.SQRT(123) ‘ошибка

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

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

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

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

При выборе функции в диалоговом окне вставки функции появляется краткое описание этой функции. Если не описывать функцию, в диалоговом окне вставки функции появится следующий текст: Справка недоступна (см. рис. 3).

Рис. 3. Краткое описание выбранной функции в диалоговом окне вставки функции

Рис. 4. Подготовка описания функции в диалоговом окне параметров макроса

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

Создать функцию в редакторе Visual Basic .

Перейти в Excel и выбрать Сервис  Макрос  Макросы (или нажать < Alt + F 8>). Диалоговое окно макроса представит в виде списка доступные внедренные процедуры, но не функции.

Ввести имя функции в поле имени макроса.

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

Введите описание функции в поле описания (см. рис. 4).

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

Определение категории функции

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

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

Вывести окно View  Immediate Window.

Набрать код Application.MacroOptions Macro:=" Вознаграждение ", Category:=1

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

Массивы и типы

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

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

Типы данных – значение, хранящееся в ячейке, больше не является просто строкой или числом. Одна ячейка может содержать расширенный тип данных с большим набором свойств.

Применение формул в книгах

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

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

  • В этом фрагменте кода мы снова заполняем список файлов. Цикл for открывает каждый файл и присваивает соответствующее название листа.
  • Затем мы присваиваем строку = SUM(F5: F8) ячейке F9 и используем атрибут style для назначения стиля ячейки. Больше стилей ячеек можно найти в официальной документации.

Если у вас Excel на русском языке, то вместо 'Sheet1' указывайте 'Лист1' и записывайте формулы соответственно на русском, например, =СУММ(F5:F8) .

Редактирование формул при помощи Мастера

После того как Вы нажали кнопку OK и формула вставилась на страницу, вызвать снова окно Мастера для внесения изменений очень легко. Для этого выделите ячейку с формулой (если она ещё не выделена) и снова нажмите кнопку «Fx» на панели инструментов, как было уже показано выше. В открывшемся окне Мастера функций внесите необходимые изменения (значение аргументов, добавление новых аргументов) и подтвердите их кнопкой OK.

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

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

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

Ссылка на основную публикацию