VBA и Python для автоматизации Excel и MS Office
Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel .
Эта заметка более подробно раскрывает всем известный тезис: Под конкретную задачу надо выбирать наиболее подходящий инструмент применимо к офисной автоматизации.
Как записать макрос в Excel? Пошаговая инструкция
- Мой выбор: VBA . Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
В статье мы показали разницу между макросами и надстройками и продемонстрировали на примерах лишь малую часть их возможностей. Вы легко можете разработать свою надстройку, скомпилировать внешние модули для использования в её составе и расширить функциональные возможности программ МойОфис под свои запросы.
Создать макрос в Excel с помощью макрорекордера
Для начала проясним, что собой представляет макрорекордер и при чём тут макрос.
Этот способ очень полезен тем, кто не владеет навыками и знаниями работы в языковой среде VBA. Но такая легкость в исполнении и записи макроса имеет свои минусы, как и плюсы:
- Записать макрорекордер может только то, что может пощупать, а значит записывать действия он может только в том случае, когда используются кнопки, иконки, команды меню и всё в этом духе, такие варианты как сортировка по цвету для него недоступна;
- В случае, когда в период записи была допущена ошибка, она также запишется. Но можно кнопкой отмены последнего действия, стереть последнюю команду которую вы неправильно записали на VBA;
- Запись в макрорекордере проводится только в границах окна MS Excel и в случае, когда вы закроете программу или включите другую, запись будет остановлена и перестанет выполняться.
Для включения макрорекордера на запись необходимо произвести следующие действия:
- в версии Excel от 2007 и к более новым вам нужно на вкладке «Разработчик» нажать кнопочку «Запись макроса»
- в версиях Excel от 2003 и к более старым (они еще очень часто используются) вам нужно в меню «Сервис» выбрать пункт «Макрос» и нажать кнопку «Начать запись».
Следующим шагом в работе с макрорекордером станет настройка его параметров для дальнейшей записи макроса, это можно произвести в окне «Запись макроса», где:
Примечание. Если в главном меню отсутствует закладка «РАЗРАБОТЧИК», тогда ее необходимо активировать в настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту». В правом списке «Основные вкладки:» активируйте галочкой опцию «Разработчик» и нажмите на кнопку ОК.
Написание простейшего макроса в Excel. Самоучитель. Как записать макрос в Excel? Пошаговая инструкция.
- В MS Excel 2003 и более старше переходите в меню «Вид», выбираете «Панель инструментов» и нажимаете кнопку «Формы».
- В MS Excel 2007 и более новее вам нужно на вкладке «Разработчик» открыть выпадающее меню «Вставить» и выбрать объект «Кнопка».
Открываем в Excel редактор Visual Basic, и вставляем код следующей процедуры в исходный код этой книги (ЭтаКнига, открыть двойным кликом) или в модуль, который Вы предварительно должны создать.
Надстройка VBA-Excel
Цель данной программы — расширить стандартные возможности Excel, добавив в него дополнительное количество функций и команд, сократить время выполнения однотипных действий и автоматизировать рутинную работу. На любую команду вы сможете назначить горячую клавишу чтобы быстро ей пользоваться.
Сравнить диапазоны
Быстро сопоставьте данные двух таблиц. Найдите ячейки из первой таблице, которые присутствуют во второй. Найдите совпадающие данные. Объедините две таблицы исключив дубликаты.
Стрелки зависимостей
В чужих файлах особенно тяжело разобраться как получено то или иное значение. Для наглядности можно использовать стрелки, показывающие какие данные используются. Надстройка позволяет включать такие стрелки для всех формул на активном листе.
Вставить строки
Аналогично поможет вставить большое количество строк
Вставить столбцы
Поменять местами строки
Поменять местами столбцы
Удалить пустые строки или столбцы
Удаляет все строки или столбцы на листе, которые не содержат данных.
Скрыть пустые строки или столбцы
Макрос скрывает пустые строки или столбцы без данных
Вставить значения
Заменить формулы их значениями в указанном диапазоне, на одном или нескольких листах, во всей книге
Разорвать связи
Разрывает связи с другими книгами и заменяет формулы, ссылающиеся на другие книги, на значения.
Объединить сохранив текст
В отличие от стандартной функции сохраняет значения всех ячеек при объединении.
Уникальные
Подсветка уникальных значений и/или вывод их в отдельный диапазон.
Закрепить ячейки
Закрепление строки/столбца или ячейки в формулах всего выделенного диапазона.
Текст формулы в комментарий
Макрос для быстрой записи текста формулы в комментарии к ячейке
Обновить все связи
Принудительное обновление всех связей в книге для пересчета формул и актуализации данных.
Собрать комментарии
Макрос собирает комментарии со всех листов на отдельный лист
Поменять местами ячейки
Позволяет поменять местами два выделенных диапазона в одно действие
Заполнить пустые ячейки
Заполняет пустые ячейки значениями верхних или левых непустых ячеек
Цветовая карта ячеек
Макрос наглядно позволяет определить тип данных в ячейках. Моментально увидеть какое число сохранено как текст, или дана как число.
Диспетчер листов
Диспетчер помогает быстро перемещаться по листам, выполнять различные операции с ними (удалять, скрывать, сортировать и многое другое).
Координатное выделение строки и столбца
Наглядное выделение текущей строки и столбца при работе с большими таблицами.
Стиль ссылок
Быстрое переключение между ссылками вида A1 и R1C1.
Сортировка листов
Макрос позволит отсортировать листы по возрастанию, убыванию или по цвету.
Скриншот области
Выделите диапазон ячеек и сохраните его как рисунок (например, для отправки по почте).
Создание оглавления книги
Помогает быстро создать лист Оглавление с перечнем всех листов книги и ссылками на них.
Регистр
Функция для быстрого изменения регистра букв в Excel.
Диспетчер имен
Макрос позволит удалить именованные диапазоны в книге в том числе и скрытые
Диспетчер стилей
Можно быстро удалить все пользовательские и встроенные стили в книге
Объединить листы в один
Макрос для сбора данных с одного или нескольких листов
Копирование листов n раз
Необходим для того чтобы массово размножить листы в книге
Как создать листы из диапазона
Данная функция позволяет создавать листы с нужным названием из указанного диапазона
Как вывести список листов
Вы можете вывести в указанную ячейку список всех листов книги.
Как массово переименовать листы книги
Функция позволяет переименовывать все листы книги за один раз, а не поштучно как в стандартном функционале
Как удалить пустые листы
Если в вашем файле образовалось много пустых листов, которые долго удалять вручную, то удалить их можно с помощью макроса
Удаление пробелов в выделенном диапазоне
Функция позволяет удалить лишние пробелы в указанном диапазоне ячеек
Выбор даты из календаря
Функция позволяет удалить лишние пробелы в указанном диапазоне ячеек
Отображение ярлыков листов
Макрос для быстрого отображение или скрытия ярлычков листов
Диспетчер открытых книг
Менеджер книг призван облегчить работу с большим количеством открытых книг.
Сохранить выбранные книги
Помогает разово сохранить все открытые книги Excel
Изменить видимость листов
Удобное скрытие и отображение листов книги, а также возможность сделать лист супер скрытым.
Удалить картинки на листе
Функционал позволяет удалить все картинки с выбранного листа
Резервное копирование
Быстро создайте резервную копию файла с указанием даты и времени сохранения. При этом не нужно переименовывать текущий файл в котором работаете.
Текущая папка
Не тратьте время на поиск файла, откройте папку в которой он расположен.
Сохранить листы
Сохраните выбранные листы как отдельные книги Excel
Защита листов
Импортировать листы
Скопируйте листы из закрытых книг в текущую. Можно импортировать листы как из одной, так и из нескольких книг.
Скопировать ссылку на файл
Копируйте ссылку на текущий файл Excel в буфер обмена.
Сумма прописью (СУММАПРОПИСЬЮ)
ПЕРЕВОД
Перевод с любого языка на любой язык с помощью сервиса Яндекс.Перевод.
СКЛОНЕНИЕ
СЦЕПИТЬДИАПАЗОН
ЧИСЛОИЗСТРОКИ
ТЕКСТИЗСТРОКИ
ПОЧТАИЗСТРОКИ
ПОДСТРОКА
Извлекает подстроку текста, разделенного символом-разделителем (Пример: =ПОДСТРОКА(«Мама мыла раму»;» «;1) вернет «Мама»).
СЧЁТСИМВЛ
Подсчитывает количество заданных символов в строке.
ТЕКСТКАК
Возвращает ИСТИНА если указанный текст соответствует шаблону.
УБРАТЬСЛУЧЛАТИН
Функция помогает убрать случайно введенный текст латиницей.
ПРОИЗНЕСТИ
Функция позволяет проговорить введенный текст в ячейку Excel. Предупредить об изменении результата в расчете и т.п.
ТРАНСЛИТ
ЕЛАТИН
ЕКИРИЛЛ
ИЗВЛЕЧЬЛАТИНИЦУ
ИЗВЛЕЧЬКИРИЛЛИЦУ
Текст, из которого необходимо извлечь слова, написанные кириллицей.
ЕПОЧТА
ИНВЕРСИЯ
НАЧАЛОСТРОКИ
Выполняет поиск заданного текста и возвращает текст, стоящий перед искомым текстом.
КОНЕЦСТРОКИ
Выполняет поиск заданного текста и возвращает текст, стоящий после искомого текста.
ТЕКСТИЗURL
Выполняет импорт текста из интернет страницы в Excel.
ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО
Функции для удобной работы с фамилией, именем и отчеством помогут разделить ячейку с ФИО на части
УБРАТЬСЛУЧКИРИЛ
Функция помогает убрать случайно введенный текст на русской раскладке.
ПРЕДМЕТПРОПИСЬЮ
Функция ПРЕДМЕТПРОПИСЬЮ позволит указать прописью любой тип данных будь то штуки, дни, рубли, тоны, километры и так далее.
ДОМЕН
Функция помогает получить название домена из ссылки (url).
ЕФОРМУЛА
ЕЖИРНЫЙ
Возвращает ИСТИНА если текст ячейки написан жирным шрифтом.
ЕКУРСИВ
Возвращает ИСТИНА если текст ячейки написан курсивом.
ЕДАТА
ЕПОДЧЕРКН
ФАЙЛСУЩ
Проверяет наличие файла по заданному пути и возвращает ИСТИНА если указанный файл существует.
ПАПКАСУЩ
Проверяет наличие директории по заданному пути и возвращает ИСТИНА если указанная папка существует.
ЛИСТСУЩ
Проверяет существование листа и возвращает ИСТИНА если указанный лист существует в активной книге и ЛОЖЬ если такого листа нет.
КНИГАОТКРЫТА
Проверяет открыта ли книга Excel и возвращает ИСТИНА указанный файл открыт и ЛОЖЬ в противном случае.
ФАЙЛРАСШИР
Вставка курса любой валюты на любую дату прямо с сайта Центробанка РФ (cbr.ru).
КУРСПЕРИОД
Вставка среднего курса валюты за любой период прямо с сайта Центробанка РФ (cbr.ru).
Формула для быстрого расчета суммы с НДС или же размера НДС.
ДЕНЬНЕДТ
Выводит день недели текстом (понедельник, вторник и т.д.)
СЕГОДНЯСТАТ
Выводит текущую дату. Функция автоматически не пересчитывается.
ДАТАИЗСТРОКИ
Функция ищет дату в строке вне зависимости от того в какой части текста она находится.
Дата прописью (ДАТАПРОПИСЬЮ)
Преобразует дату в текст в различных падежах и форматах.
Время прописью (ВРЕМЯПРОПИСЬЮ)
Записывает выбранное время прописью в различных форматах.
НОМКВАРТАЛА
Удобная формула для вычисления номера квартала по дате в Excel.
МАКСЕСЛИ
Определяет максимальное значение в указанном диапазоне при выполнении значений в этом диапазоне условию.
МИНЕСЛИ
Определяет минимальное значение в указанном диапазоне при выполнении значений в этом диапазоне условию.
СУММЗАЛИВКА
СУММШРИФТ
СЧЁТЗАЛИВКА
СЧЁТШРИФТ
СЧЁТУНИК
Подсчитывает количество уникальных ячеек в указанном диапазоне.
ПЕРВОЕВСТРОКЕ
Возвращает значение первой непустой ячейки в строке.
ПЕРВОЕВСТОЛБЦЕ
Возвращает значение первой непустой ячейки в столбце.
ПОСЛЕДНЕЕВСТРОКЕ
Возвращает значение последней непустой ячейки в строке.
ПОСЛЕДНЕЕВСТОЛБЦЕ
Возвращает значение последней непустой ячейки в столбце.
КОЛИЧЕСТВОСТРОК
Возвращает номер последней строки, которая содержит какие-либо данные.
КОЛИЧЕСТВОСТОЛБЦОВ
Возвращает номер последнего столбца, который содержит какие-либо данные.
УРОВЕНЬСТРОКИ
УРОВЕНЬСТОЛБЦА
СЛУЧМЕЖДУСТАТ
Возвращает случайное число между заданными. Функция не пересчитывается автоматически.
СЛЯЧЕЙКА
Возвращает значение случайной ячейки из выбранного диапазона.
ГЕНЕРАЦИЯПАРОЛЯ
АДРЕССЫЛКИ
ТИПЯЧЕЙКИ
ТЕКСТФОРМУЛЫ
ТЕКСТФОРМУЛЫEN
Возвращает текст формулы указанной ячейки на английском языке.
ТЕКСТКОММЕНТА
ФОРМАТЯЧЕЙКИ
ИМЯЛИСТА
ИМЯКНИГИ
ИМЯПОЛЬЗОВАТЕЛЯ
РАСПОЛОЖЕНИЕКНИГИ
ДАТАСОЗДАНИЯ
ДАТАСОХРАНЕНИЯ
Возвращает дату последнего сохранения текущего файла.
ЦВЕТЗАЛИВКИ
ЦВЕТШРИФТА
ЦВЕТЗАЛИВКИRGB
Возвращает шестнадцатеричный код RGB заливки указанной ячейки.
ЦВЕТШРИФТАRGB
Возвращает шестнадцатеричный код RGB шрифта указанной ячейки.
ДАТАПЕЧАТИ
ШРИФТИМЯ
ШРИФТРАЗМЕР
ФАЙЛИМЯ
ФАЙЛПУТЬ
Определяет расположение папки в котором находится файл
IMAGE
Функция позволяет импортировать изображение по ссылке. Ссылкой может быть как адрес в Интернете, так и путь к изображению на локальном компьютере. Данная функция отлично экономит время при создании прайс-листов.
Функция генерирует QR код и вставляет его в выбранную ячейку Excel.
VBA и Python для автоматизации Excel и MS Office | Tech&Biz Insights | Яндекс Дзен
На сегодняшний день надстройка Excel содержит более 100 пользовательских функций, и более 25 макросов. Мы стараемся учитывать ваши пожелания и выпускаем обновления с новым функционалом и улучшениями. Все обновления проходят автоматически в пару кликов, быстро и удобно.
Запись макроса в Excel
Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например «Excel».
Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.
Теперь давайте рассмотрим код который записал макрорекодер. Выполните следующие действия, чтобы открыть редактор кода:
Вы увидите, что как только вы нажмете кнопку «Выполнить», текст «Excel» будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.
Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.
Как сохранить макросы Excel. Личная книга макросов в Excel и как ее использовать
- Выберите любую ячейку (кроме A1).
- Перейдите на вкладку «Разработчик».
- В группе «Код» нажмите кнопку «Макросы».
- В диалоговом окне «Макрос» кликните на сохраненный макрос «ОтносительныеСсылки».
- Нажмите кнопку «Выполнить».
Кроме того, конверторов из VBA в Lua не существует в принципе. Поэтому, если вам требуется работать с документами, которые содержат макросы и были ранее созданы в Microsoft Office, то вам придется переводить код макрокоманд на язык Lua. Мы понимаем, что это может быть проблемой для тех, кто переходит на отечественное ПО, и специально для наших покупателей предлагаем такие решения:
Абсолютная и относительная запись макроса
Теперь давайте запишем макрос в режиме относительных ссылок:
Макрос в режиме относительных ссылок будет сохранен.
- Выберите любую ячейку (кроме A1).
- Перейдите на вкладку “Разработчик”.
- В группе “Код” нажмите кнопку “Макросы”.
- В диалоговом окне “Макрос” кликните на сохраненный макрос “ОтносительныеСсылки”.
- Нажмите кнопку “Выполнить”.
Как вы заметите, макрос записал текст “Excel” не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel – ячейка B4 и в конечном итоге выберет ячейку B5.
Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.
Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.
Изучаем МойОфис: как использовать макросы в редакторах документов и электронных таблиц / Хабр
- Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
- Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
- Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
- Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).
Запрет на выполнение любых макросов. При открытии книги Excel не уведомляет о том, что эта книга содержит макросы, поэтому пользователь может не понять причину, по которой книга Excel не работает так, как должна.
