Как сделать в экселе макросы: примеры и как сделать / Skillbox Media

Используйте макросы в Excel на Mac, чтобы сэкономить время и сделать больше

Excel на Mac не всегда был таким же мощным, как в Windows. Макросы действительно не будут работать, если они не были созданы исключительно для Mac.

Откройте БЕСПЛАТНУЮ шпаргалку «Essential Excel Formulas» прямо сейчас!

Это подпишет вас на нашу рассылку

Прочитайте нашу политику конфиденциальности

Начиная с 2013 года Microsoft вернула макросы. Существует два типа макросов: те, которые вы можете создать, быстро записывая свои действия, и те, которые используют VBA для разработки более совершенной автоматизации. В Office 2016 Excel использует одну и ту же кодовую базу на всех платформах . Это изменение облегчит работу макросов на разных платформах.

Итак, давайте посмотрим, как это в настоящее время работает на macOS.

Включение макросов в Excel на Mac

Работа с макросами в Excel на вашем Mac может быть отключена по умолчанию. Этот параметр обусловлен тем, что макросы могут быть потенциальным вектором вредоносного ПО . Самый простой способ узнать, есть ли у вас вкладка « Разработчик » на ленте в Excel. Если вы этого не видите, это просто включить.

Нажмите на Excel в строке меню, а затем выберите Настройки в раскрывающемся списке. В меню выберите Лента и панель инструментов . В правом списке Разработчик должен быть внизу, установите флажок. Наконец, нажмите « Сохранить», и вы увидите вкладку «Разработчик» в конце ленты.

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

Запись макроса вручную в Excel на Mac

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

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

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

Пример 1: дневной объем продаж и среднечасовой

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

Нам нужно настроить первый лист. Использование этого первого бланка в качестве шаблона для ежедневного копирования на новую вкладку может сэкономить вам время. В первом столбце / строке поставьте Час / Дата. Через верх добавить с понедельника по пятницу.

Затем в первом столбце ставьте разбивку почасовых итогов от 8-5. Я использовал 24-часовое время, но вы можете использовать нотацию AM / PM, если хотите. Ваш лист должен соответствовать скриншоту выше.

Добавьте новую вкладку и скопируйте в нее свой шаблон. Затем заполните данные о продажах за день. (Если у вас нет данных для заполнения этого листа, вы можете ввести = RandBetween (10,1000) во все ячейки, чтобы создать фиктивные данные. ) Затем нажмите « Разработчик» на ленте.

Затем нажмите Запись макроса . В диалоговом окне введите имя AverageandSum и оставьте его в этой рабочей книге . Вы можете установить сочетание клавиш, если хотите. Вы можете ввести описание, если вам нужно больше подробностей о том, что делает макрос. Нажмите кнопку ОК, чтобы начать настройку макроса.

В нижней части почасовых списков введите ежедневные итоги . В ячейке рядом с ней введите = SUM (B2: B10) . Затем скопируйте и вставьте это в остальные столбцы. Затем в заголовке добавьте Среднее после последнего столбца. Затем в следующей ячейке вниз введите = Среднее (B2: F2) . Затем вставьте это в ячейки в оставшейся части столбца.

Затем нажмите « Остановить запись» . Теперь ваш макрос можно использовать на каждом новом листе, который вы добавляете в свою книгу. Как только у вас появится другой лист данных, вернитесь в Developer и нажмите Macros . Ваш макрос должен быть выделен, нажмите Run, чтобы добавить свои суммы и средние значения.

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

Макросы VBA в Excel на Mac

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

Добавьте еще один час и день на лист и запустите макрос. Вы увидите, что макрос перезаписывает ваши новые данные. Чтобы обойти это, мы используем код, чтобы сделать макрос более динамичным с помощью VBA, которая является уменьшенной версией Visual Basic . Реализация ориентирована на автоматизацию для Office.

Это не так легко подобрать, как Applescript , но автоматизация Office полностью построена на Visual Basic. Поэтому, как только вы поработаете с ним здесь, вы сможете быстро развернуться и использовать его в других приложениях Office. (Это также может быть очень полезно, если вы застряли на ПК с Windows на работе.)

При работе с VBA в Excel у вас есть отдельное окно. Снимок экрана выше — это наш записанный макрос, который отображается в редакторе кода. Оконный режим может быть полезен для изучения кода во время обучения. Когда ваш макрос зависает, появляются инструменты отладки, позволяющие посмотреть на состояние ваших переменных и данных листа.

Office 2016 теперь поставляется с полным редактором Visual Basic. Это позволяет вам использовать Object Browser и инструменты отладки, которые раньше были ограничены версией Windows. Вы можете получить доступ к Обозревателю объектов, выбрав « Просмотр»> «Обозреватель объектов» или просто нажав Shift + Command + B. Затем вы можете просмотреть все доступные классы, методы и свойства. Это было очень полезно при создании кода в следующем разделе.

Пример 2: дневной объем продаж и среднечасовой индекс

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

Вернитесь к пустому шаблону, который вы создали на последнем шаге. Нажмите на разработчика, чтобы вернуться на вкладку. Как только вы окажетесь на вкладке, нажмите кнопку . Затем щелкните где-нибудь на листе шаблона, чтобы разместить кнопку. Появится меню макросов, назовите свой макрос и нажмите « Создать» .

Окно Visual Basic откроется; в браузере проекта вы увидите его как Module2 . Панель кода будет иметь Sub AverageandSumButton () вверху и несколько строк вниз End Sub . Ваш код должен идти между этими двумя, поскольку это начало и конец вашего макроса.

Шаг 1: Объявление переменных

Для начала вам нужно будет объявить все ваши переменные . Они находятся в блоке кода ниже, но обратите внимание на то, как они построены. Вы должны объявить все переменные, используя Dim перед именем, а затем как с типом данных.

Sub AverageandSumButton() Dim RowPlaceHolder As Integer Dim ColumnPlaceHolder As Integer Dim StringHolder As String Dim AllCells As Range Dim TargetCells As Range Dim AverageTarget As Range Dim SumTarget As Range 

Теперь, когда у вас есть все ваши переменные, вам нужно сразу использовать некоторые переменные диапазона. Диапазоны — это объекты, которые содержат разделы рабочего листа в качестве адресов. Переменная All Cells будет установлена ​​для всех активных ячеек на листе, включая метки столбцов и строк. Вы получаете это, вызывая объект ActiveSheet, а затем его свойство UsedRange .

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

Вы вызываете это, вызывая свой диапазон AllCells , используя его класс Cells, чтобы получить эту конкретную ячейку, используя (2,2) . Чтобы получить последнюю ячейку в диапазоне, вы все равно будете вызывать AllCells . На этот раз с помощью метода SpecialCells получить свойство xlCellTypeLastCell . Вы можете увидеть оба из них в блоке кода ниже.

 Set AllCells = ActiveSheet.UsedRange Set TargetCells = Range(AllCells.Cells(2, 2), AllCells.SpecialCells(xlCellTypeLastCell)) 

Шаг 2: для каждого цикла

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

Перед тем, как запустить цикл для каждой строки, необходимо установить целевой столбец, в котором цикл записывает среднее значение для каждой строки. Вы используете переменную ColumnPlaceHolder, чтобы установить эту цель. Вы устанавливаете его равным переменной Count класса Cells AllCells . Добавьте один к нему, чтобы переместить его справа от ваших данных, добавив +1 .

Далее вы начнете цикл с помощью For Each . Затем вы хотите создать переменную для подмножества, в данном случае subRow . После In мы устанавливаем основной объект, который мы анализируем TargetCells . Добавить. В конце строки, чтобы ограничить цикл только каждой строкой, а не каждой ячейкой в ​​диапазоне.

Внутри цикла вы используете метод ActiveSheet.Cells, чтобы установить конкретную цель на листе. Координаты устанавливаются с помощью subRow.Row, чтобы получить строку, в которой находится цикл. Затем вы используете ColumnPlaceHolder для другой координаты.

Вы используете это для всех трех шагов. Сначала вы добавляете .value после скобок и устанавливаете равным WorksheetFunction.Average (subRow) . Это записывает формулу среднего значения строки в целевую ячейку. В следующей строке вы добавляете .Style и устанавливаете это равным «Валюта» . Этот шаг соответствует остальной части вашего листа. В последней строке вы добавляете .Font.Bold и устанавливаете его равным True . (Обратите внимание, что вокруг этой кавычки нет, так как это логическое значение. ) Эта строка выделяет шрифт, чтобы итоговая информация выделялась среди остальной части листа.

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

 ColumnPlaceHolder = AllCells.Columns.Count + 1 For Each subRow In TargetCells.Rows ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Value = WorksheetFunction.Average(subRow) ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Style = "Currency" ActiveSheet.Cells(subRow.Row, ColumnPlaceHolder).Font.Bold = True Next subRow RowPlaceHolder = AllCells.Rows.Count + 1 For Each subColumn In TargetCells.Columns ActiveSheet.Cells(RowPlaceHolder, subColumn.Column).Value = WorksheetFunction.Sum(subColumn) ActiveSheet.Cells(RowPlaceHolder, subColumn.Column). Style = "Currency" ActiveSheet.Cells(RowPlaceHolder, subColumn.Column).Font.Bold = "True" Next subColumn 

Шаг 3: пометьте свои резюме

Затем пометьте новую строку и столбец, снова установите RowPlaceHolder и ColumnPlaceHolder . Сначала используйте AllCells.Row, чтобы получить первую строку в диапазоне, а затем AllCells.Column + 1, чтобы получить последний столбец. Затем вы будете использовать тот же метод, что и в цикле, чтобы установить значение «Средние продажи» . Вы также будете использовать то же свойство .Font.Bold, чтобы выделить новый ярлык.

Затем измените его, установив в качестве заполнителей первый столбец и последнюю строку, чтобы добавить «Всего продаж» . Вы хотите смело это тоже.

Оба шага находятся в блоке кода ниже. Это конец макроса, отмеченного End Sub . Теперь у вас должен быть весь макрос , и вы сможете нажать кнопку, чтобы запустить его. Вы можете вставить все эти блоки кода по порядку в свой лист Excel, если хотите обмануть, но где в этом удовольствие?

 ColumnPlaceHolder = AllCells.Columns.Count + 1 RowPlaceHolder = AllCells.Row ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Value = "Average Sales" ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Font.Bold = True ColumnPlaceHolder = AllCells.Column RowPlaceHolder = AllCells.Rows.Count + 1 ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Value = "Total Sales" ActiveSheet.Cells(RowPlaceHolder, ColumnPlaceHolder).Font.Bold = True End Sub 

Что дальше для макросов в Excel на Mac?

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

Visual Basic открывает пользователям Mac Excel возможность углубиться в автоматизацию Office. Visual Basic традиционно был доступен только в Windows. Это позволяет вашим макросам динамически адаптироваться к данным, делая их более универсальными. Если у вас есть терпение, это может стать дверью к более сложному программированию.

Хотите больше трюков с электронными таблицами? Узнайте, как автоматически выделять определенные данные с помощью условного форматирования в Excel. с условным форматированием. и условное выделение в Numbers на Mac.

Запуск макроса иконкой

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: в двух предыдущих заметках мы рассказали, как запускать макросы клавиатурными сокращениями и кнопками, расположенными на листе Excel. Можно ли запустить макрос значком на панели инструментов?

Решение: почему-то при переходе с версии Excel 2003 на 2007 Microsoft уменьшил возможности использования иконок для запуска макросов. В Excel 2007 остался единственный способ (кроме кода VBA) использования иконок – добавление кнопки макроса на панель быстрого доступа. Выполните следующие действия.

Кликните правой кнопкой мыши в любом месте ленты и выберите Настройка панели быстрого доступа (рис. 1).

Рис. 1. Запуск настройки панели быстрого доступа

Скачать заметку в формате Word или pdf, примеры в формате Excel (с кодом VBA)

Откроется окно Параметры Excel на вкладке Панель быстрого доступа (рис. 2). В левом раскрывающемся списке выберите Макросы.

Рис. 2. Окно Параметры Excel (чтобы увеличить этот и следующие рисунки, кликните на картинке правой кнопкой мыши и выберите опцию Открыть картинку в новой вкладке).

Выберите требуемый макрос в списке левого окна и нажмите кнопку Добавить в центре диалогового окна (рис. 3).

Рис. 3. Добавить макрос MacroTolcon на панель быстрого доступа

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

Рис. 4. Изменение порядка расположения значков на панели быстрого доступа

По умолчанию каждому макросу соответствует одна и та же иконка. Чтобы изменить значок, нажмите кнопку Изменить (выделена на рис. 4). Вам доступны 181 иконка (рис. 5). Выберите ту, которая вам будет напоминать о действии макроса (я насчитал только 180 иконок 🙂 ). Измените отображаемое имя на что-то дружелюбное. Это имя будет всплывать при наведении курсора на значок в панели быстрого доступа.

Рис. 5. Настройки вида и имени значка

Нажмите Оk, чтобы закрыть окно Изменение кнопки, а затем еще раз, чтобы закрыть диалоговое окно Параметры Excel. Новый значок появится на панели быстрого доступа (рис. 6).

Рис. 6. Новый значок появился на панели быстрого доступа

Совет: начиная с Excel 2007 вы можете добавить на панель быстрого доступа значок, который будет появляться только когда открыта конкретная книга. Для начала кликните правой кнопкой мыши на значке макроса на панели быстрого доступа, и выберите опцию Удалить с панели быстрого доступа. Чтобы добавить значок только для одной книги, откройте окно Параметры Excel на вкладке Панель быстрого доступа. Это можно сделать, как описано на рис. 1, или кликнув на значок раскрывающегося списка на панели быстрого доступа (рис. 7), а затем выбрать опцию Другие команды.

Рис. 7. Вызов окна Параметры Excel из панели быстрого доступа

В окне Параметры Excel (рис. 8) в правом раскрывающемся списке измените настройку с Для всех документов (по умолчанию) на Для <имя книги>. В нашем случае – Для Запуск макроса иконкой. Примеры.xlsm. После этого добавьте макрос на панель быстрого доступа, повторив действия, описанные на рисунках 2–5.

Рис. 8. Изменить настройку панели быстрого доступа только для одной книги

Резюме: в Excel 2007 запуск макроса иконкой можно выполнить путем добавления иконки на панель быстрого доступа.

Как создать макрос Excel

Карен Уильямс | 20 июня 2017 г.   | | 43

Объявление о новом классе
Мастерство Excel: передовые методы аудита для безошибочных электронных таблиц 005

Создайте макрос Excel, чтобы ускорить выполнение повторяющихся задач!

Макросы — вы, наверное, слышали это слово раньше и, возможно, даже слышали, как люди говорят о том, какие они замечательные. Но что такое шумиха?

Когда дело доходит до работы с электронными таблицами в Excel 2013, некоторые процессы могут быть повторяющимися (и, осмелюсь сказать, скучными). Одним из примеров является форматирование отчета. Хотя редактирование стилей текста и корректировка ячеек могут показаться рутинной задачей, представление ваших данных очень важно для тех, кто позже будет извлекать смысл из отчета. Но вот где макросы могут вам помочь: вы можете создать макрос Excel, чтобы превратить 10 минут кликов в один клик!

Как создать макрос Excel

Диалоговое окно «Запись макроса» Excel с примерами выбора.

Чтобы приступить к созданию макросов, сначала нужно добавить на ленту вкладку «Разработчик». Посетите наш блог о том, как добавлять и удалять вкладки на ленте Office 2013, чтобы узнать, как это сделать.

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

1. Перейдите на вкладку «Разработчик» и нажмите кнопку «Запись макроса» в группе «Код» ИЛИ нажмите кнопку в левом нижнем углу экрана, которая выглядит как электронная таблица с красной точкой в ​​левом верхнем углу.
2. Придумайте имя для своего макроса. Пробелы не допускаются, но вместо них можно использовать подчеркивание. На рисунке на этой странице мы назвали наш макрос «Format_Report».
3. Выберите сочетание клавиш. Обязательно выберите букву, которая еще не используется в качестве сочетания клавиш, поскольку она заменит оригинал (т. е. не используйте Ctrl+Z, если не хотите потерять ярлык для отмены). Используя Shift, вы можете расширить свои возможности (например, вместо этого вы можете использовать Ctrl+Shift+Z). На нашем рисунке мы обозначили сочетание клавиш Ctrl+Shift+R.
4. Выберите место для хранения макроса. Обычно вы хотите сохранить свой макрос в этой книге, что мы и выбрали в нашем примере. Если вы хотите, чтобы он был доступен при каждом использовании Excel, выберите Личная книга макросов в раскрывающемся меню.
5. Введите краткое описание того, что будет делать ваш макрос, в поле Описание.
6. Нажмите кнопку ОК.

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

Когда вы закончите, нажмите кнопку «Остановить запись» в группе «Код» на вкладке «Разработчик» ИЛИ щелкните синий квадрат в левом нижнем углу окна Excel.

Поздравляем! Вы только что узнали, как создать макрос Excel!

Зачем изучать Excel?

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

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

Дополнительный совет: сравнение абсолютных и относительных эталонов

Подпишитесь на ежемесячные обновления обучения классы!

Имя

Первое
Последний

Рабочий адрес электронной почты

Организация

Как включить макросы в Microsoft Excel

Быстрые ссылки

• Будьте внимательны при включении макросов

• Как включить макросы для определенной электронной таблицы Excel Все электронные таблицы Excel

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

Будьте осторожны при включении макросов

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

Связано: Объяснение макросов: почему файлы Microsoft Office могут быть опасны

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

Как включить макросы для определенной электронной таблицы Excel

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

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

Excel позволит текущему файлу выполнить свои макросы, и все готово.

Связано: Как отключить панель предупреждений безопасности в программах Microsoft Office

Как включить макросы для всех электронных таблиц Excel

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

Активация макросов в Excel в Windows

Если вы пользователь Windows, сначала запустите Microsoft Excel на своем ПК.

В левой боковой панели Excel нажмите «Параметры». Если вы находитесь на экране электронной таблицы, вместо этого выберите «Файл» > «Параметры».

В окне «Параметры Excel» на левой боковой панели нажмите «Центр управления безопасностью».

На правой панели нажмите кнопку «Настройки центра управления безопасностью».

Вы увидите окно «Центр управления безопасностью». В левой боковой панели этого окна нажмите «Настройки макроса».

На правой панели активируйте параметр «Включить макросы VBA (не рекомендуется; может выполняться потенциально опасный код)».