Центр Компьютерного Обучения " Специалист ", 2012 Microsoft Excel 2010. Расширенные возможности решение
 Скачать 4.3 Mb.
|
|
Visual Basic for Applications). Существует два способа создания макроса: Первый способ – запись действий с помощью макрорекордера. Простой способ создания макроса, не требует навыков программирования. Запустив запись макроса, пользователь выполняет последовательность действий, а макрорекордер создает текст программы на VBA. Второй способ – написание алгоритма действий, используя язык программирования VBA. Сложный способ написания макроса, требуются навыки программирования, но позволяет создавать более мощные и более гибкие программы. Для создания макроса таким способом следует запустить редактор VBA и писать алгоритм макроса «с чистого листа». Для работы с макросами необходимо отобразить вкладку Разработчик [Developer]. 1. Выбрать Файл [File], Параметры [Options]. 2. В разделе Настройка ленты [Customize Ribbon]в группе Основные вкладки [Main Tabs], выбрать Разработчик [Developer]. 3. ОК. Запись макросов Продумайте всю последовательность действий, которую нужно записать. В действиях используйте команды на ленте и панели быстрого доступа, сочетания клавиш, – всё это позволит избежать ряд ошибок и сделает макрос более универсальным. 1. Запустить макрорекордер для записи макроса на вкладке Разработчик [Developer], в группе Код [Code], нажать кнопку Запись макроса [Record Macro]. 2. В диалоговом окне Запись макроса [Record Macro] в поле Имя макроса [Macro Name] ввести имя. Имя макроса может начинаться с буквы или подчеркивания, затем могут следовать буквы, цифры, подчеркивание. Не допустимо использовать пробелы. Имя не может совпадать с именем или адресом ячейки. Сочетание клавиш [Shortcut key] (не обязательно) – можно назначить в сочетании с клавишей Ctrl любую букву для быстрого запуска макроса с клавиатуры. Назначенное Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 84 сочетание клавиш будет заменять при совпадении стандартное сочетание клавиш до тех пор, пока открыта книга с макросом. в списке Сохранить в [Store macro in] выбрать место сохранения макроса: Новая книга [New Workbook] – создается книга, вместе с которой будет сохранен макрос. Для доступа к макросу – открыть книгу. Эта книга [This Workbook] – макрос сохраняется в текущей книге. Для доступа из других книг надо открыть книгу с макросом. Личная книга макросов [Personal Macro Workbook] – макрос будет доступен в любой книге на этом компьютере. В этом случае макрос записывается в скрытую личную книгу макросов Personal.xlsb. в поле Описание [Description] ввести текст – комментарий к действиям макроса (не обязательно) ОК. 3. Выполнить набор действий, которые нужно записать. Если макрос делается для разных диапазонов, то перед выполнением действия надо нажать кнопку Относительные ссылки [Use Relative References] в группе Код [Code] вкладки Разработчик [Developer]. Если макрос будет применяться к одним и тем же ячейкам, то кнопку Относительные ссылки [Use Relative References] нажимать не надо. 4. Остановить запись – на вкладке Разработчик [Developer], в группе Код [Code], нажать кнопку Остановить запись [Stop Recording]. ПРАКТИКУМ: Открыть файл 05 Источник. Сохранить файл с именем 05 Макросы и типом Книга Excelс поддержкой макросов. На листе Задание1 написать макрос ЕВРО с сохранением в текущей книги с возможностью запуска «горячими клавишами» Ctrl + й , который к любым выделенным ячейкам будет применять следующее форматирование: числовой формат – денежный с обозначением евро (€) и 2-мя знаками в дробной части, полужирное начертание, зеленый цвет шрифта (выбрать из стандартных цветов), желтый цвет заливки. Сохранить изменения в файле. На листе Задание2 написать 3 макроса для работы с итогами: Макрос ИтогиНаименование, который будет рассчитывать для каждого наименования суммы по полям: объем партии, затраты, выручка и прибыль. Свернуть итоги до промежуточных итогов по каждой группе. Макрос ИтогиПоставщик, который будет рассчитывать по каждому поставщику суммы по полям: объем партии, затраты, выручка и прибыль. Свернуть итоги до промежуточных итогов по каждой группе. Макрос ОчисткаИтогов, который будет удалять существующие итоги на листе. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 85 Сохранить изменения в файле и закрыть его. Параметры безопасности для работы с макросами VBA-макросы являются источниками потенциального риска, т.к. злоумышленник может внедрить в файл потенциально опасный макрос, который запускается при открытии документа и может вызвать заражение компьютера макровирусом. По умолчанию программа Excel настроена на Отключение всех макросов с уведомлением [Disable all macros with notification]. При открытии книги с макросом под лентой появляется панель сообщений или окно с выбором: Включить макросы [Enable Macros] или Отключить макросы [Disable Macros]. Для настройки уровня безопасности надо на вкладке Разработчик [Developer], в группе Код [Code], нажать Безопасность макросов [Macros Security] и выбрать нужный вариант в окне Центр управления безопасностью [Trust Center]. Отключить все макросы без уведомления [Disable all macros without notification]– при отсутствии доверия к макросам; запрет на все макросы, кроме макросов, имеющих цифровую подпись или хранящиеся в надежном расположении. Отключить все макросы с уведомлением [Disable all macros with notification] – установлено по умолчанию, выбор включать или отключать макросы осуществляется пользователем при открытии любого файла с макросом. Отключать все макросы кроме макросов с цифровой подписью [Disable all macros except digitally signed macros] – макрос запускается при наличии цифровой подписи и если разработчик макроса занесен в список Надежных издателей [Trusted Publishers]. Если разработчик не занесен в этот список, то последует сообщение с выбором: включить макрос или занести разработчика в список надежных издателей. Все макросы без цифровой подписи отключаются без уведомления. Включить все макросы (не рекомендуется, возможен запуск опасной программы) [Enable all macros (non recommended; potentially dangerous code can run] – разрешается выполнению любых макросов. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 86 ПРАКТИКУМ: Открыть файл 05 Макросы. Включить запуск макросов. Запуск макроса Один и тот же макрос можно запустить разными способами. Прежде, чем запустить макрос следует подготовиться к запуску макроса – выделить ячейки, ввести данные или ничего не выполнять в зависимости от предназначения макроса. 1-й способ. С помощью диалогового окна Макрос 1. На вкладке Разработчик [Developer], в группе Код [Code], нажать кнопку Макросы [Macros] или нажать клавиши Alt + F8 2. В окне Макрос [Macro] выделить нужный макрос и нажать Выполнить [Run]. 2-й способ. С помощью «горячих клавиш» Нажать на клавиатуре клавишу Ctrl и заранее известную Букву на клавиатуре. Сочетание клавиш Ctrl +Буква задаются при создании макроса. Для уже созданных макросов можно посмотреть, создать или изменить сочетание клавиш в диалоговом окне Макрос. Для этого выделить нужный макрос и нажать кнопку Параметры [Options], в поле Сочетание клавиш [Shortcut key] ввести прописную или строчную букву. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 87 3-й способ. Создание кнопки на панели Быстрого доступа 1. Выбрать Файл [File], Параметры [Options], в разделе Панель быстрого доступа [Quick Access Toolbar] в списке Выбрать команды из [Choose commands from] выбрать Макросы [Macros]. 2. Выделить нужный макрос в списке, нажать кнопку Добавить >> [Add >>]. 3. Новая кнопка выделена в списке Настойка панели быстрого доступа [Customize Quick Access Toolbar], нажав кнопку Изменить [Modify] можно изменить символ (значок) кнопки и всплывающую подсказку – Отображаемое имя [Display name]. Нажать ОК. 4. ОК. 4-й способ. С помощью кнопки на рабочем листе 1. На вкладке Разработчик [Developer] в группе Элементы управления [Controls] раскрыть список кнопки Вставить [Insert] и выбрать Кнопка (элемент управления формы) [Button (Form Controls]. 2. Щелкнуть левой кнопкой мыши в рабочей области листа, где нужно разместить кнопку. 3. В появившемся окне Назначить макрос объекту [Assign Macro] выделить нужный макрос и нажать ОК. 4. Изменить имя кнопки с клавиатуры. 5. Для завершения работы с кнопкой – щелкнуть в свободном месте листа. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 88 Кнопку можно редактировать. Для этого щелкнуть правой кнопкой мыши по кнопке и выбрать команду Изменить текст [Edit text] – можно переименовать, изменить размер, переместить или удалить. Чтобы назначить для кнопки другой макрос – щелкнуть правой кнопкой мыши по кнопке и выбрать Назначить макрос [Assign Macro]. Выход из режима редактирования – щелчок в свободном месте листа. ПРАКТИКУМ: В открытом файле 05 Макросы, на листе Задание1, выделить ячейки B2:B21 и запустить макрос ЕВРО через диалоговое окно Макрос. На листе Задание1 к ячейкам С2:С21 применить действие макроса ЕВРО с помощью «горячих клавиш» ( Ctrl + й ). Изменить сочетание клавиш на Ctrl + q На листе Задание1 выделить ячейки D2:D21 и запустить макрос ЕВРО назначенным сочетанием клавиш ( Ctrl + q ). Создать кнопку на панели быстрого доступа для макроса ЕВРО, изменить стандартный вид значка на любой из предлагаемого списка. На рабочем листе Задание1 проверить действие кнопки для диапазона ячеек Е2:Е21. На рабочем листе Задание2 создать 3 кнопки с именами: Итоги Наименование для запуска макроса ИтогиНаименование. Итоги Поставщик для запуска макроса ИтогиПоставщик. Удалить Итоги для запуска макроса ОчисткаИтогов. Сохранить сделанные изменения. Редактирование макросов в редакторе Visual Basic Editor Чтобы посмотреть текст программы на VBA, необходимо зайти в редактор Visual Basic. 1. На вкладке Разработчик [Developer], в группе Код [Code], нажать кнопку Макросы [Macros] или нажать клавиши Alt + F8 2. Выделить нужный макрос и нажать кнопку Изменить Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 89 Любой макрос начинаться с оператора Sub, за которым идет имя макроса и список аргументов в скобках – если аргументов нет, то скобки остаются пустыми. Затем идет тело макроса – последовательность выполняемых команд, которые заканчиваются оператором End Sub. Для перехода в окно программы Excel нажать кнопку View Microsoft Excel. Окно редактора Visual Basic останется открытым и в любой момент в него можно вернуться. Чтобы выйти из редактора Visual Basic достаточно закрыть окно стандартным способом. ПРАКТИКУМ: В открытом файле 05 Макросы просмотреть код макроса ЕВРО. Сделать следующие изменения в форматировании: удалить дробную часть в числовом формате, изменить начертание с «полужирный» на «полужирный курсив», изменить цвет текста с зеленого (код 5287936) на красный (код 255) Перейти в окно программы Excel. Выделить на листе Задание1 ячейки E2:E21 и любым способом запустить макрос ЕВРО. Использование готовых макросов Алгоритмы типовых макросов можно встретить в литературе, чтобы ими воспользоватьеся, необходимо выполнить следующие действия: 1. На вкладке Разработчик [Developer], в группе Код [Code], нажать кнопку Visual Basic. 2. В редакторе Visual Basic создать новый модуль, выбрав в меню Insert команду Module. Microsoft Excel 2010. Уровень 2. Расширенные возможности www.specialist.ru Центр Компьютерного обучения «Специалист» 90 3. Ввести текст из литературного источника или скопировать, если он находится в электронном виде. Запустить макрос можно всеми известными способами. ПРАКТИКУМ: В открытом файле 05 Макросы выполнить вставку готового алгоритма макроса. Открыть текстовый файл Удаление пустых строк и столбцов, скопировать весь текст (алгоритмы макросов). Вернуться в редакторе Visual Basic, создать новый модуль и вставить содержимое буфера обмена. Выйти из редактора Visual Basic (закрыть), перейти на лист Задание3 и через окно Макрос запустить последовательно макросы: УдалениеПустыхСтрок и УдалениеПустыхСтолбцов. Пользовательские функции с использованием VBA Создание пользовательской функции с использованием VBA 1. На вкладке Разработчик [Developer], в группе Код [Code], нажать кнопку Visual Basic. 2. В редакторе Visual Basic создать новый модуль, выбрав в меню Insert команду Module. 3. Выбрав в меню Insert команду Procedure. Ввести имя функции в поле Name. В именах нельзя использовать пробелы, имя должно быть уникальным. Первый символ имени – буква, следующие – буквы, цифры и _. В группе Type выбрать Function. В группе Scope выбрать Public. ОК. 4. Внести данные в шаблон: Аргументы функции перечисляются внутри круглых скобок, через запятую. Разделитель целой и дробной части в числа используется только точка. До строки End Function необходимо присвоить Имени функции значение: Имя_Функии = Имя_аргумента_1+Имя_аргумента_2 и т.д. Например, функция ИтогоRUB рассчитывает, какова будет сумма в рублях с учетом налога 13% и при условии, что исходные данные: Оклад в долларах (ОкладUSD), Премия в евро (ПремияEUR), курс доллара равен 30,55 и курс евро – 40,77. Microsoft Excel 2010. Уровень 2. Расширенные возможности Центр Компьютерного обучения «Специалист» www.specialist.ru 91 Использование пользовательской функции в расчетах 1. Выделить ячейку для результата на листе Excel. 2. На вкладке Формулы [Formulas], нажать Вставить функцию [Insert Function]. 3. Выбрать категорию Определенные пользователем [User Defined] и найти в ней свою функцию. 4. Заполнить аргументы функции, ОК. ПРАКТИКУМ: В открытом файле 05 Макросы создать пользовательскую функцию ИтогоRUB, которая позволит рассчитывать Сумму в рублях с учетом 13% налога, учитывая, что Оклад в долларах (ОкладUSD), Премия в евро (ПремияEUR), курс доллара равен 30,55 и курс евро – 40,77. Открыть лист Функции VBA1, выделить ячейку D2 и с помощью пользовательской функции ИтогоRUB сделать расчет. Создать пользовательскую функцию НДС, вычисляющую 18% от стоимости. Применить функцию для вычисления НДС в ячейках G2:G11 на листе Функции VBA2. (подсказка: чтобы вычислить 18% НДС надо сумму умножить на 18% и разделить на 118%) |