- Регистрация
- 30 Дек 2017
- Сообщения
- 150,298
- Симпатии
- 4,196
[iMacros] Создание макросов и их применение в Excel. Написание макросов Excel (2019)
Описание:
Вебинар 1. Минимум необходимой теории
Теоретический вебинар
Что такое макросы? Как их писать?
Наша задача научиться трём способам написания макросов:
Макросы пишут в редакторе Visual Basic Editor и наша задача научиться эффективно использовать его для решения своих задач. На вебинаре узнаем из каких окон состоит, как настроить "под себя" (какие панелии окно добавить, а какие лучше убрать).
Объектная модель Excel
90% макросов что-то делают с ячейками (Cells), диапазонами (Range), листами (WorkSheets) и excel-файлами (Workbooks), нам предстоит разобраться с иерархией этих объектов. Также узнаем их основные свойства и методы.
Домашнее задание
Пишем с нуля макросы, которые:
Практический вебинар
3 варианта копирования
Существует три варианта копирования ячеек/диапазонов и мы научимся их использовать в зависимости от задачи и результата, который хотим получить.
5 способов удаления
Вы замечали, что вариантов удаления несколько:
Определяем последнюю заполненную ячейку
Макросы должны быть универсальными, для этого потребуется каждый раз вычислять диапазон, для которого писать формулу, либо границы копирования и т.д. Расскажу про три способа, которые точно будете использовать в своих кодах:
Пишем с нуля макросы, которые:
Практический вебинар
Что такое циклы? Почему они короли?
Макросы должны уметь делать работу за нас. Мы напишем код, а макрос пусть +100500 раз повторит все наши действия.
Если Вы уже "в теме" макросов, то вот какие типы циклом рассмотрим:
Основы работы с переменными
Если циклы - это короли, то переменные - их верные помощники. Имя листа, файла, путь сохранения и т.д. - постоянно меняются. Определим для подобных параметров переменные и напишем макрос, который будет использовать универсальный код и выполняться в 2-3 раза быстрее.
Домашнее задание
Пишем с нуля макросы, которые:
Практический вебинар
Скажи мне: "Кто ты?"
До искуственного интеллекта нам будет ещё далеко, но вот запросить информацию от пользователя (ответить Да/Нет, ввести число, выделить диапазон ячеек, для которых будет выполнен макрос и т.д.) мы научимся. Возможно, Вы уже слышали про:
Работа с файловой системой
Нам следует научиться писать команды, которые будут:
Домашнее задание
Пишем с нуля макросы, которые:
Практический вебинар
Макросы на VBA - это Вам не ВПР писать. Тут думать надо!
https://skladchik.com/styles/default/xenforo/smilies/smile.png" class="bbCodeImage LbImage"
Формулы в VBA можно писать несколькими способами, каждый имеет свои особенности, вот нам и надо будет разобраться с:
[LIST]
[*]текстом (UCase, LCase, StrConv, Left, Mid, Len, InStr, RevStr, Trim и др.)
[*]датой и временем (Date, Time, Now, WeekDay, DateDiff, DateAdd и др.)
[*]числами (Abs, Fix, Int, Round, Rnd, Sgn, Sin, Cos, Tan, Atn и др.)
[*]преобразование данных (IsNumeric, IsDate, IsArray, IsObject, IsEmpty и др.)
[*]форматами (Format, FormatCurrency/DateTime/Number/Percent)
[/LIST]Function - то тоже макрос, но необычный
Узнаем про отдельный вид макросов - Функции (Function). Расскажу, про принципиальные отличия от "обычных", научу писать свои собственные формулы (их нет ни в одной версии Excel):
[LIST]
[*]СуммаЯчеекПоЦвету (находит сумму ячеек с определённым цветом заливки)
[*]ЛатиницаВРусские и РусскиеВЛатиницу (преобразует буквы)
[*]ЧислоИзЯчейки (извлекает из ячейки с текстом число)
[/LIST]Также поделюсь собственными готовыми функциями, которые не надо писать самим, а просто взяли и используете (+можно под свои задачи легко изменить).
WorkSheetFunction - ещё один вариант написания формул VBA
Особый вид функций, который работает быстрее обычных, но имеет свои особенности. К примеру формулу ВПР в ячейку B1 можно написать так:
[LIST]
[*]Range("B1").FormulaLocal = "=ВПР(A1;$D$1:$F$5;2;0)"
[*]Range("B1") = Application.Vup(Сells(1,1);Range($D$1:$F$5);2;0)
[*]Range("B1") = Application.WorksheetFunction.Vlookup(сells(1,1);Range($D$1:$F$5);2;0)
[/LIST]Да, и каждый вернёт свой результат. На вебинаре разберёмся
[IMG]https://skladchik.com/styles/default/xenforo/smilies/smile.png" class="bbCodeImage LbImage"
Домашнее задание
Напишем много строк кода, которые:
[LIST]
[*]посчитают выполнение плана,
[*]подставят данные из одной таблицы в другую,
[*]выполнят проверку наличия латинских букв в русских названиях продуктов,
[*]короче много всего посчитаем, чтобы разобраться с функциями VBA.
[/LIST]
[B]Вебинар 6. Автоматизируем создание сводных таблиц[/B]
Практический вебинар
Сводная макросом? Вы серьёзно?
Сразу предостерегу Вас о идеи строить ВСЕ СВОДНЫЕ таблицы с помощью VBA. У нас нет такой задачи!!! На вебинаре напишем код VBA для анализа данных, а сводная таблица будет промежуточным этапом этого анализа.
Так чему же мы научимся?
Нам следует научиться писать команды, которые будут:
[LIST]
[*]добавлять поля в область данных,
[*]форматировать сводную таблицу,
[*]фильтровать (в т.ч. с помощью срезов),
[*]управлять итогами,
[*]группировать данные (даты, числа, текст),
[*]выполнять вычисления (+ создавать свои поля),
[*]и другие.
[/LIST]+ будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы.
Домашнее задание
Построим с помощью макросов 5 сводных таблицы, в каждой будет своя изюминка (надо будет не просто построить, а где-то применить фильтр, выполнить группировку и доп.вычисления, создать вычисляемое поле и т.д.). Скучно точно не будет!
[B]Вебинар 7. Создание пользовательских форм (UserForms)[/B]
Практический вебинар
Пишем солидные программы на VBA
Когда Вы запрашиваете информацию от пользователей, лучше всего исключить элемент "творчества" (ограничить ввод текста в поля, где должны быть числа, запись дат в произвольной форме и т.д.) Создание формы, в которой всё интуитивно понятно - отличный способ уберечься от криворуких ответственных коллег.
Научимся создавать формы (UserForm) и работать с основными элементами:
[LIST]
[*]текстовое поле (TextBox) и метка (Label),
[*]обычным (ListBox) списком и выпадающим(ComboBox),
[*]флажками (CheckBox) и переключателями (OptionButton),
[*]кнопками (CommandButton), полосами прокрутки (ScrollBar) и т.д.
[/LIST]На практике разберём их свойства и методы.
Продвинутая настройка форм
Чтобы создавать по-настоящему классные формы научимся продвинутым приёмам и фишкам. Вот некоторые из них (здесь не все):
[LIST]
[*]запрет ввода текста, туда, где должны быть числа (и наоборот),
[*]ввод даты с помощью календаря, а текста по маске,
[*]организация связанных выпадающих списков,
[*]"умные переключатели" и др.
[/LIST]Многие полезности можно просто брать и использовать в своих проектах.
Домашнее задание
Создадим всего 2 формы, но количество и взаимосвязь элементов на них не оставит Вас равнодушными.
[B]Вебинар 8. Подружим Excel с Outlook и Word[/B]
Практический вебинар
Макросы не только для Excel пишут
Когда можешь писать макросы для Excel, автоматизировать работу в Word или Outlook не составит особого труда. Просто в Excel мы работаем с листами, ячейками, а в Word cо страницами, абзацами и т.д.. Нам надо будет просто понять как устроена объектная модель, какие свойства и методы присущи объектам Word и Outlook.
Что мы сможем автоматизировать в Word?
Вот список некоторых задач, которые мы поручим макросам:
[LIST]
[*]удалять пустые абзацы только в выделенном фрагменте/во всем документе
[*]массовое форматирование документов (приведение к одному образцу)
[*]извлечение e-mail адресов из текста документа (создадим новый файл со списком)
[*]и др.
[/LIST]+ будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы.
Что мы сможем автоматизировать в Outlook?
Вот список некоторых задач, которые мы поручим макросам:
[LIST]
[*]отправка писем по списку пользователей (каждому свой файл во вложении)
[*]отправка красивых писем (HTMLBody)
[*]сохранить все вложения из писем в нужную папку
[*]и др.
[/LIST]90% макросов уже написаны, поэтому наша задача будет сводиться к тому, чтобы понять как они устроены, чтобы изменить под решение своих задач.
Домашнее задание
Организуем массовое формирование документов Word по данным Excel-таблицы с последующей отправкой через Outlook по списку контрагентов.
[B][URL='https://imacros.ru/raznoe/napisanie-makrosov-excel.html']Продажник[/URL] [/B]
[B]Скачать:[/B] [GROUPS=5,7,6]https://cloud.mail.ru/public/2Aim/2ze8sF3FK[/GROUPS]
Описание:
Вебинар 1. Минимум необходимой теории
Теоретический вебинар
Что такое макросы? Как их писать?
Наша задача научиться трём способам написания макросов:
- с помощью макрорекордера (только как помощника)
- используя чужой код (где брать и как изменять под себя)
- сами с нуля (посвятим бОльшую часть времени)
Макросы пишут в редакторе Visual Basic Editor и наша задача научиться эффективно использовать его для решения своих задач. На вебинаре узнаем из каких окон состоит, как настроить "под себя" (какие панелии окно добавить, а какие лучше убрать).
Объектная модель Excel
90% макросов что-то делают с ячейками (Cells), диапазонами (Range), листами (WorkSheets) и excel-файлами (Workbooks), нам предстоит разобраться с иерархией этих объектов. Также узнаем их основные свойства и методы.
Домашнее задание
Пишем с нуля макросы, которые:
- создаёт оглавление Excel-файла с большим количеством листов
- записывает информацию о пользователе, который открывает файл
Практический вебинар
3 варианта копирования
Существует три варианта копирования ячеек/диапазонов и мы научимся их использовать в зависимости от задачи и результата, который хотим получить.
5 способов удаления
Вы замечали, что вариантов удаления несколько:
- удалить только значения (ClearContents),
- очистить форматы (ClearFormats),
- удалить комментарий (ClearComments),
- удалить всё сразу (Clear),
- да ещё и со смещением (Delete)
Определяем последнюю заполненную ячейку
Макросы должны быть универсальными, для этого потребуется каждый раз вычислять диапазон, для которого писать формулу, либо границы копирования и т.д. Расскажу про три способа, которые точно будете использовать в своих кодах:
- свойство Range.End (когда в столбце все строки заполнены значениями)
- метод Range.Find (самую последнюю строку/столбец)
- метод SpecialCells ("самую-самую" последнюю (даже удалённую) строку/столбец)
Пишем с нуля макросы, которые:
- создаёт оглавление Excel-файла с большим количеством листов
- записывает информацию о пользователе, который открывает файл
Практический вебинар
Что такое циклы? Почему они короли?
Макросы должны уметь делать работу за нас. Мы напишем код, а макрос пусть +100500 раз повторит все наши действия.
Если Вы уже "в теме" макросов, то вот какие типы циклом рассмотрим:
- For ... next (цикл со счётчиком)
- Do ... Loop (с условиями While и Until)
- For each ... (по объектам коллекций WorkSheets, Сells и др.)
Основы работы с переменными
Если циклы - это короли, то переменные - их верные помощники. Имя листа, файла, путь сохранения и т.д. - постоянно меняются. Определим для подобных параметров переменные и напишем макрос, который будет использовать универсальный код и выполняться в 2-3 раза быстрее.
Домашнее задание
Пишем с нуля макросы, которые:
- заполнит 138 договоров ГПХ и отправит их каждому сотруднику на почту
- из выгрузки продаж за 9 мес. сформирует excel-файлы по зонам ответственности каждого менеджера и директора региона и сохранит в нужную папку
Практический вебинар
Скажи мне: "Кто ты?"
До искуственного интеллекта нам будет ещё далеко, но вот запросить информацию от пользователя (ответить Да/Нет, ввести число, выделить диапазон ячеек, для которых будет выполнен макрос и т.д.) мы научимся. Возможно, Вы уже слышали про:
- MsgBox (информационные сообщения),
- InputBox (получение данных от пользователя)
- Application.InputBox (продвинутый вариант InputBox)
Работа с файловой системой
Нам следует научиться писать команды, которые будут:
- создавать и удалять папки (нужно, чтобы макрос смог сохранять результат в нужное место),
- выводить FileDialog (решили собрать данные с нескольких excel-файлов, вот и надо попросить пользователя указать какие конкретно) и т.д.
Домашнее задание
Пишем с нуля макросы, которые:
- создаёт список файлов в папке (покажу два принципиально разных варианта решения задачи)
- сбор данных из нескольких файлов Excel в один (все из одной папки, по списку файлов, по тем, что выберет пользователь)
Практический вебинар
Макросы на VBA - это Вам не ВПР писать. Тут думать надо!
https://skladchik.com/styles/default/xenforo/smilies/smile.png" class="bbCodeImage LbImage"
Формулы в VBA можно писать несколькими способами, каждый имеет свои особенности, вот нам и надо будет разобраться с:
[LIST]
[*]текстом (UCase, LCase, StrConv, Left, Mid, Len, InStr, RevStr, Trim и др.)
[*]датой и временем (Date, Time, Now, WeekDay, DateDiff, DateAdd и др.)
[*]числами (Abs, Fix, Int, Round, Rnd, Sgn, Sin, Cos, Tan, Atn и др.)
[*]преобразование данных (IsNumeric, IsDate, IsArray, IsObject, IsEmpty и др.)
[*]форматами (Format, FormatCurrency/DateTime/Number/Percent)
[/LIST]Function - то тоже макрос, но необычный
Узнаем про отдельный вид макросов - Функции (Function). Расскажу, про принципиальные отличия от "обычных", научу писать свои собственные формулы (их нет ни в одной версии Excel):
[LIST]
[*]СуммаЯчеекПоЦвету (находит сумму ячеек с определённым цветом заливки)
[*]ЛатиницаВРусские и РусскиеВЛатиницу (преобразует буквы)
[*]ЧислоИзЯчейки (извлекает из ячейки с текстом число)
[/LIST]Также поделюсь собственными готовыми функциями, которые не надо писать самим, а просто взяли и используете (+можно под свои задачи легко изменить).
WorkSheetFunction - ещё один вариант написания формул VBA
Особый вид функций, который работает быстрее обычных, но имеет свои особенности. К примеру формулу ВПР в ячейку B1 можно написать так:
[LIST]
[*]Range("B1").FormulaLocal = "=ВПР(A1;$D$1:$F$5;2;0)"
[*]Range("B1") = Application.Vup(Сells(1,1);Range($D$1:$F$5);2;0)
[*]Range("B1") = Application.WorksheetFunction.Vlookup(сells(1,1);Range($D$1:$F$5);2;0)
[/LIST]Да, и каждый вернёт свой результат. На вебинаре разберёмся
[IMG]https://skladchik.com/styles/default/xenforo/smilies/smile.png" class="bbCodeImage LbImage"
Домашнее задание
Напишем много строк кода, которые:
[LIST]
[*]посчитают выполнение плана,
[*]подставят данные из одной таблицы в другую,
[*]выполнят проверку наличия латинских букв в русских названиях продуктов,
[*]короче много всего посчитаем, чтобы разобраться с функциями VBA.
[/LIST]
[B]Вебинар 6. Автоматизируем создание сводных таблиц[/B]
Практический вебинар
Сводная макросом? Вы серьёзно?
Сразу предостерегу Вас о идеи строить ВСЕ СВОДНЫЕ таблицы с помощью VBA. У нас нет такой задачи!!! На вебинаре напишем код VBA для анализа данных, а сводная таблица будет промежуточным этапом этого анализа.
Так чему же мы научимся?
Нам следует научиться писать команды, которые будут:
[LIST]
[*]добавлять поля в область данных,
[*]форматировать сводную таблицу,
[*]фильтровать (в т.ч. с помощью срезов),
[*]управлять итогами,
[*]группировать данные (даты, числа, текст),
[*]выполнять вычисления (+ создавать свои поля),
[*]и другие.
[/LIST]+ будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы.
Домашнее задание
Построим с помощью макросов 5 сводных таблицы, в каждой будет своя изюминка (надо будет не просто построить, а где-то применить фильтр, выполнить группировку и доп.вычисления, создать вычисляемое поле и т.д.). Скучно точно не будет!
[B]Вебинар 7. Создание пользовательских форм (UserForms)[/B]
Практический вебинар
Пишем солидные программы на VBA
Когда Вы запрашиваете информацию от пользователей, лучше всего исключить элемент "творчества" (ограничить ввод текста в поля, где должны быть числа, запись дат в произвольной форме и т.д.) Создание формы, в которой всё интуитивно понятно - отличный способ уберечься от криворуких ответственных коллег.
Научимся создавать формы (UserForm) и работать с основными элементами:
[LIST]
[*]текстовое поле (TextBox) и метка (Label),
[*]обычным (ListBox) списком и выпадающим(ComboBox),
[*]флажками (CheckBox) и переключателями (OptionButton),
[*]кнопками (CommandButton), полосами прокрутки (ScrollBar) и т.д.
[/LIST]На практике разберём их свойства и методы.
Продвинутая настройка форм
Чтобы создавать по-настоящему классные формы научимся продвинутым приёмам и фишкам. Вот некоторые из них (здесь не все):
[LIST]
[*]запрет ввода текста, туда, где должны быть числа (и наоборот),
[*]ввод даты с помощью календаря, а текста по маске,
[*]организация связанных выпадающих списков,
[*]"умные переключатели" и др.
[/LIST]Многие полезности можно просто брать и использовать в своих проектах.
Домашнее задание
Создадим всего 2 формы, но количество и взаимосвязь элементов на них не оставит Вас равнодушными.
[B]Вебинар 8. Подружим Excel с Outlook и Word[/B]
Практический вебинар
Макросы не только для Excel пишут
Когда можешь писать макросы для Excel, автоматизировать работу в Word или Outlook не составит особого труда. Просто в Excel мы работаем с листами, ячейками, а в Word cо страницами, абзацами и т.д.. Нам надо будет просто понять как устроена объектная модель, какие свойства и методы присущи объектам Word и Outlook.
Что мы сможем автоматизировать в Word?
Вот список некоторых задач, которые мы поручим макросам:
[LIST]
[*]удалять пустые абзацы только в выделенном фрагменте/во всем документе
[*]массовое форматирование документов (приведение к одному образцу)
[*]извлечение e-mail адресов из текста документа (создадим новый файл со списком)
[*]и др.
[/LIST]+ будет много мелких команд, которые важны, но я не смог выделить их в отдельные группы.
Что мы сможем автоматизировать в Outlook?
Вот список некоторых задач, которые мы поручим макросам:
[LIST]
[*]отправка писем по списку пользователей (каждому свой файл во вложении)
[*]отправка красивых писем (HTMLBody)
[*]сохранить все вложения из писем в нужную папку
[*]и др.
[/LIST]90% макросов уже написаны, поэтому наша задача будет сводиться к тому, чтобы понять как они устроены, чтобы изменить под решение своих задач.
Домашнее задание
Организуем массовое формирование документов Word по данным Excel-таблицы с последующей отправкой через Outlook по списку контрагентов.
[B][URL='https://imacros.ru/raznoe/napisanie-makrosov-excel.html']Продажник[/URL] [/B]
[B]Скачать:[/B] [GROUPS=5,7,6]https://cloud.mail.ru/public/2Aim/2ze8sF3FK[/GROUPS]
Если у Вас нет Премиум статуса:
Преимущества VIP-подписки