создание приложений в excel

Как создать программу в «Excel»

Один из наиболее часто задаваемых вопросов задаваемых начинающими пользователями звучит так: «Как создать программу в «Excel» и возможно ли это сделать в принципе?»

Ответ на него не так прост.

Создать полноценную программу или продвинутую игру инструментами «Эксель» практически невозможно.

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

Опытные программисты при помощи макросов написанных на языке VBA даже создают примитивные игры популярные в начале 90-х прошлого столетия: тетрис, змейка, пинг-понг и т.д.

Рассмотрим азы создания программ в VBA Excel.

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

Второй этап — подготовка программы «Excel» к написанию макросов: включить макросы, отобразить панель разработчика и т.д.
Подробно подготовка к написанию простого макроса описана в статье:

Как написать простой макрос в программе Excel

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

Некоторые варианты кодов макросов опубликованы на нашем сайте в разделе Макросы и VBA.

Источник

Создание приложения на основе холста с данными из файла Excel

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

Файл Excel должен находиться в облачной учетной записи хранения, например OneDrive, Dropbox или Google Диск. В этой статье используется OneDrive для бизнеса.

При отсутствии лицензии на Power Apps можно зарегистрироваться бесплатно.

Посмотрите это видео, чтобы узнать, как создать приложение на основе холста с данными из файла Excel:

Предварительные условия

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

Создание приложения

В Начать с данных выберите Другие источники данных.

Выберите свое подключение или выберите Создать соединение, чтобы создать, а затем выберите Создать.

В разделе Выбор файла Excel найдите файл FlooringEstimates.xlsx и выберите его.

В разделе Выбор таблицы щелкните FlooringEstimates, а затем нажмите кнопку Подключить.

Выполнить приложение

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

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

Отфильтруйте список, введя или вставив один или несколько символов в поле поиска.

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

Щелкните значок «плюс».

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

Выберите стрелку для записи, которую требуется изменить.

Выберите значок карандаша.

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

В качестве альтернативы выберите значок отмены, чтобы отменить изменения.

Удалите какую-то запись:

Выберите стрелку «Далее» для записи, которую требуется удалить.

Выберите значок корзины.

Дальнейшие действия

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

Источник

Создание с нуля приложения на основе холста из данных Excel

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

Предварительные условия

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

Скопируйте эти данные и вставьте их в файл Excel.

Отформатируйте данные в виде таблицы под названием Schedule, чтобы приложение Power Apps могло проанализировать их.

Дополнительные сведения см. в разделе Форматирование таблицы в Excel.

Сохраните файл под именем eventsignup.xls, затем закройте его и отправьте в учетную запись облачного хранилища, например OneDrive.

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

Открытие пустого приложения

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

Укажите имя приложения, выберите Телефон, а затем выберите Создать.

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

Power Apps Studio создаст пустое приложение для телефонов.

Если откроется диалоговое окно Вас приветствует Power Apps Studio, выберите в нем Пропустить.

Подключение к данным

В центре экрана выберите подключение к данным.

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

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

В разделе выбора таблицы установите флажок Расписание и щелкните Подключиться.

Закройте область Данные, нажав значок закрытия (X) в правом верхнем углу.

Создание экрана просмотра

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

Экран добавляется с несколькими элементами управления по умолчанию, в том числе с полем поиска и элементом управления Gallery (Коллекция). Коллекция занимает размер всего экрана под полем поиска.

В верхней части нового экрана выберите элемент управления Label (Метка), а затем замените текст [Title] строкой View records (Просмотр записей).

На панели навигации слева выберите BrowseGallery1.

Вокруг коллекции появится рамка выделения с маркерами захвата.

На вкладке Свойства на панели справа щелкните стрелку вниз в меню Макет.

Выберите Заголовок, подзаголовок и текст.

В строке формулы замените CustomGallerySample на Schedule, а также замените оба экземпляра SampleText на Volunteer.

Справа от строки формулы щелкните стрелку вниз, а затем выберите Форматирование текста.

Формула соответствует следующему примеру:

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

В поле Title2 выберите Volunteer, в поле Subtitle2 выберите StartDay, а в поле Body1 выберите StartTime.

Закройте область Данные, нажав значок закрытия (X) в правом верхнем углу.

Пользователи могут сортировать и фильтровать коллекции по имени волонтера, используя функции формулы SortByColumns и Search.

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

Создание экрана изменений

На вкладке Главная щелкните стрелку вниз рядом с элементом Новый экран и выберите Форма.

На левой панели навигации выберите EditForm1.

На вкладке Свойства на панели справа щелкните стрелку вниз рядом с элементом Источник данных и в появившемся списке выберите Расписание.

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

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

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

Закройте область Поля, нажав значок закрытия (X) в правом верхнем углу.

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

В верхней части экрана выберите элемент управления Метка, а затем замените текст [Title] строкой Изменение записей.

Добавление и переименование экранов

На панели навигации слева нажмите на кнопку с многоточием (. ) для экрана Screen1 и выберите действие Удалить.

Нажмите на кнопку с многоточием (. ) для экрана Screen2, выберите действие Переименовать и введите для него имя ViewScreen.

Нажмите на кнопку с многоточием (. ) для экрана Screen3, выберите действие Переименовать и введите для него имя ChangeScreen.

Настройка значков на экране просмотра

В верхней части экрана ViewScreen щелкните значок с кольцевой стрелкой.

Для свойства OnSelect этой кнопки введите следующую формулу:

Когда пользователь щелкнет этот значок, данные из таблицы Schedule будут заново извлечены из файла Excel.

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

В правом верхнем углу экрана ViewScreen щелкните значок «плюс».

Для свойства OnSelect этой кнопки введите следующую формулу:

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

Щелкните стрелку вправо рядом с первой записью в коллекции.

Задайте для свойства OnSelect этой стрелки следующую формулу:

EditForm(EditForm1); Navigate(ChangeScreen, ScreenTransition.None)

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

Настройка значков на экране редактирования

На экране ChangeScreen щелкните значок «X» в левом верхнем углу.

Для свойства OnSelect этой кнопки введите следующую формулу:

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

В правом верхнем углу выберите значок галочки.

Задайте для свойства OnSelect этой галочки следующую формулу:

SubmitForm(EditForm1); Navigate(ViewScreen, ScreenTransition.None)

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

На вкладке Вставка выберите Значки, а затем — значок мусорной корзины.

Задайте для свойства Color этого значка значение White и переместите его так, чтобы он располагался рядом со значком галочки.

Для свойства Visible значка мусорной корзины введите следующую формулу:

Этот значок будет отображаться, только если форма находится в состоянии Изменить, а не в состоянии Создать.

Для свойства OnSelect значка мусорной корзины введите следующую формулу:

Remove(Schedule, BrowseGallery1.Selected); Navigate(ViewScreen, ScreenTransition.None)

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

Тестирование приложения

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

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

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

Измените значения полей в новой записи и сохраните эти изменения.

Измените значения полей в этой же записи и отмените эти изменения.

Удалите запись, которую вы добавили ранее.

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

Источник

Автоматизация рутины в Microsoft Excel при помощи VBA

В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.

VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.

Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.

Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.

Поэтому, увы, будем учить Visual Basic.

Чуть-чуть подготовки и постановка задачи

Итак, поехали. Открываем Excel.

Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.

Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):

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

Результат, которого хотим добиться, выглядит примерно так:

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

Кодим

Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».

И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».

Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:

Напишем Hello World:

Sub FormatPrice()
MsgBox «Hello World!»
End Sub

И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.

Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.

Примеры синтаксиса

Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i

Dim x As Double
x = Val( «1.234» ) ‘ Парсинг чисел
x = x + 10
MsgBox x

On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox «OK!»
GoTo ne

ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок

‘ Циклы бывает, какие захотите
Do While True
Exit Do

Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( «abc» ), Val( «4» ))
Case 24
MsgBox «0»
Case 25
MsgBox «1»
Case 26
MsgBox «2»
End Select

Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.

Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.

Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.

Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.

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

Кодим много и под Excel

В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.

Sub FormatPrice()
Sheets( «result» ).Cells.Clear
Sheets( «data» ).Activate
End Sub

Работа с диапазонами ячеек

Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.

Примеры работы с Range

Sheets( «result» ).Activate
Dim r As Range
Set r = Range( «A1» )
r.Value = «123»
Set r = Range( «A3,A5» )
r.Font.Color = vbRed
r.Value = «456»
Set r = Range( «A6:A7» )
r.Value = «=A1+A3»

Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:

Для упрощения работы рекомендую определить следующие функции-сокращения:

Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( «A» ) + Col)
End Function

Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».

Глобальные переменные

Option Explicit ‘ про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3

FormatPrice

Sub FormatPrice()
Dim I As Integer ‘ строка в data
CurRow = 1
Dim Groups(1 To GroupsCount) As String
Dim PrGroups(1 To GroupsCount) As String

Теперь надо заполнить массив Groups:

На месте многоточия

И создать заголовки:

На месте многоточия в предыдущем куске

For I2 = 1 To GroupsCount
If Groups(I2) <> PrGroups(I2) Then
Dim I3 As Integer
For I3 = I2 To GroupsCount
AddHeader I3, Groups(I3)
Next I3
Exit For
End If
Next I2

Не забудем про процедуру AddHeader:

Перед FormatPrice

Теперь надо перенести всякую информацию в result

Подогнать столбцы по ширине и выбрать лист result для показа результата

После цикла в конце FormatPrice

Sheets( «Result» ).Activate
Columns.AutoFit

Всё. Можно любоваться первой версией.

Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:

Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:

Поэтому чуть-чуть меняем код с добавлением стиля границ:

Select Case Ty
Case 1 ‘ Тип
.Font.Bold = True
.Font.Size = 16
.Borders(xlTop).Weight = xlThick
Case 2 ‘ Производитель
.Font.Size = 12
.Borders(xlTop).Weight = xlMedium
End Select
.Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
End With
CurRow = CurRow + 1
End Sub

Осталось лишь добится пропусков перед началом новой группы. Это легко:

В начале FormatPrice

Dim I As Integer ‘ строка в data
CurRow = 0 ‘ чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String

В цикле расстановки заголовков

If Groups(I2) <> PrGroups(I2) Then
CurRow = CurRow + 1
Dim I3 As Integer

В точности то, что и хотели.

Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.

Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.

Спасибо за внимание.

Буду рад конструктивной критике в комментариях.

UPD: Перезалил пример на Dropbox и min.us.

UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.

Источник