Автоматизация задач с помощью средства записи макросов — Excel. Макросы для excel для экономистов


Автоматизация задач с помощью средства записи макросов — Excel

Посмотрите видео о том, как создать макрос, который форматирует даты в Excel.

При записи макроса все необходимые действия записываются в виде кода Visual Basic для приложений (VBA). Такими действиями может быть ввод текста или чисел, выбор ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов и даже импорт данных из внешнего источника, такого как Microsoft Access. Visual Basic для приложений (VBA) является частью языка программирования Visual Basic. Он доступен в большинстве приложений Office. Хотя VBA позволяет автоматизировать процессы в приложениях Office и между ними, вам не нужно уметь программировать или знать язык VBA, так как все нужные действия выполнит средство записи макросов.

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

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки "Разработчик".

Вкладка "Разработчик" на ленте

Запись макроса

Перед записью макросов полезно знать следующее:

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

  • Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.

  • В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

Чтобы записать макрос, следуйте инструкциям ниже.

  1. На вкладке Разработчик в группе Код нажмите кнопку Запись макроса.

    -ИЛИ-

    Нажмите ALT+T+M+R.

    Команда "Записать макрос" в группе "Код" на вкладке "Разработчик"
  2. В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.

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

  3. Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции "Отменить" в данном экземпляре Excel.

  4. В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

    Как правило, макросы сохраняются в расположении Эта книга, но если вы хотите, чтобы макрос был всегда доступен при работе в Excel, выберите пункт Личная книга макросов. Если выбрать этот вариант, Excel создаст скрытую личную книгу макросов Personal.xlsb (если она еще не существует) и сохранит макрос в ней.

  5. В поле Описание при необходимости введите краткое описание действий макроса.

    Хотя поле "Описание" является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.

  6. Чтобы начать запись макроса, нажмите кнопку ОК.

  7. Выполните действия, которые нужно записать.

  8. На вкладке Разработчик в группе Код нажмите кнопку Остановить запись Изображение кнопки .

    -ИЛИ-

    Нажмите ALT+T+M+R.

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.

Диалоговое окно "Макрос"

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Задача

Описание

Изменение параметров безопасности макросов в Excel

Сведения о параметрах безопасности макросов и их значении.

Запуск макроса

Макросы можно запускать различными способами, например с помощью сочетания клавиш, графического объекта, панели быстрого доступа, кнопки или даже при открытии книги.

Изменение макроса

С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.

Копирование модуля макроса в другую книгу

Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.

Назначение макроса объекту, фигуре или графическому элементу

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

  2. В поле Назначить макроса выберите макрос, который вы хотите назначить.

Назначение макроса кнопке

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

Назначение макроса для элемента управления на листе

Вы можете назначать макросы формам и элементам ActiveX на листе.

Включение и отключение макросов в файлах Office

Узнайте, как включать и отключать макросы в файлах Office.

Открытие редактора Visual Basic

Нажмите клавиши ALT+F11.

Поиск справки по использованию редактора Visual Basic

Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.

Пример изменения записанного кода можно найти в статье Начало работы с VBA в Excel.

Запись макроса

Перед записью макросов полезно знать следующее:

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

  • Если вам нужно записать длинную последовательность задач, советуем вместо этого использовать несколько более мелких макросов.

  • В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее.

  1. Выберите Excel > Параметры> Лента и панель.

  2. В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

Чтобы записать макрос, следуйте инструкциям ниже.

  1. На вкладке Разработчик нажмите кнопку Запись макроса.

  2. В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.

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

  3. В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

    Как правило, макросы сохраняются в той же книге, но если вам нужно, чтобы данный макрос был всегда доступен при работе в Excel, выберите вариант Личная книга макросов. Если вы выберете этот параметр, Excel создаст скрытую личную книгу макросов Personal.xlsb (если она еще не существует) и сохранит макрос в нее. Книги из этой папки автоматически открываются при каждом запуске Excel, а макросы, сохраненные в таких книгах, указываются в окне "Макрос" (см. следующий раздел).

  4. Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш, которые еще не назначены другим командам, так как они будут переопределять совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос.

  5. В поле Описание при необходимости введите краткое описание действий макроса.

    Хотя поле "Описание" является необязательным, рекомендуется его заполнить. Кроме того, желательно ввести понятное описание, которое будет полезно вам и всем, кто запускает макрос. Если у вас много макросов, описания помогут быстро определить, для чего они нужны.

  6. Чтобы начать запись макроса, нажмите кнопку ОК.

  7. Выполните действия, которые нужно записать.

  8. На вкладке Разработчик щелкните Остановить запись.

Работа с макросами, записанными в Excel

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

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Задача

Описание

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

Узнайте, как включать и отключать макросы в Excel для Mac.

Создание, выполнение, изменение или удаление макроса 

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

Копирование модуля макроса в другую книгу

Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.

Назначение макроса объекту, фигуре или графическому элементу

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

  2. В поле Назначить макроса выберите макрос, который вы хотите назначить.

Назначение макроса кнопке

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

Назначение макроса для элемента управления на листе

Вы можете назначать макросы формам и элементам ActiveX на листе.

Открытие редактора Visual Basic

На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic.

Поиск справки по использованию редактора Visual Basic

Узнайте, как найти справку по элементам Visual Basic.

support.office.com

Краткое руководство: создание макроса - Служба поддержки Office

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

Для автоматизации часто выполняемых в Microsoft Excel задач можно записать макрос. Макрос представляет собой действие (или набор действий), которое можно выполнять любое количество раз. При создании макроса записываются щелчки мышью и нажатия клавиш. После создания макроса его можно отредактировать, чтобы изменить выполняемые им действия.

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

Процедура

Изображение значка

Перед записью макроса   

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки "Разработчик".

Вкладка "Разработчик" на ленте

Изображение значка

Запись макроса   

  1. В группе Код на вкладке Разработчик нажмите кнопку Запись макроса.

  2. Если необходимо, заполните поля Имя макроса, Сочетание клавиш и Описание, а затем нажмите кнопку OK, чтобы начать запись макроса.

    Команда "Записать макрос" в группе "Код" на вкладке "Разработчик"

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

  4. На вкладке Разработчик щелкните Остановить запись.

    Команда "Остановить запись" в группе "Код" на вкладке "Разработчик"

Изображение значка

Более подробное изучение макроса   

При редактировании макроса можно немного изучить язык программирования Visual Basic.

Чтобы изменить макрос, в группе Код на вкладке Разработчик нажмите кнопку Макросы, выделите имя макроса и нажмите кнопку Изменить. Запустится редактор Visual Basic.

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

Измените код, закройте редактор Visual Basic и запустите макрос повторно. Посмотрите, что произойдет.

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

Процедура

Изображение значка

Перед записью макроса   

Убедитесь в том, что на ленте отображается вкладка Разработчик. По умолчанию вкладка Разработчик не видна, поэтому необходимо выполнить указанные ниже действия.

  1. Выберите Excel > Параметры> Лента и панель.

  2. В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

Изображение значка

Запись макроса   

  1. На вкладке Разработчик нажмите кнопку Запись макроса.

  2. Если необходимо, заполните поля Имя макроса, Сочетание клавиш и Описание, а затем нажмите кнопку OK, чтобы начать запись макроса.

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

  4. На вкладке Разработчик в группе Код нажмите кнопку Остановить запись.

Изображение значка

Более подробное изучение макроса   

При редактировании макроса можно немного изучить язык программирования Visual Basic.

Для изменения макросов, на вкладке Разработчик нажмите кнопку макросы, выберите имя макроса и нажмите кнопку Изменить. Запустится редактор Visual Basic.

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

Измените код, закройте редактор Visual Basic и запустите макрос повторно. Посмотрите, что произойдет.

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

support.office.com

VBA Code Excel Macro Examples

VBA Code Excel Macro Examples – Useful Macros, Codes, 100+ How To explained for Basic Beginners to Advanced VBA users. Tutorials to learn Excel 2003, 2007, 2010, 2013 Macros and Mastering in VBA. Selected examples to deal with different objects, methods and properties in Excel. Numerous free most useful VBA codes and tips will help you to deal with various Excel Objects like Cell, Range, Worksheets, Workbooks, Application, Charts, Pivot Tables, Hyperlinks, functions, User Forms, MsgBox, ListBox, ComboBox. Also provided free example codes to deal with MS Word, PowerPoint, Outlook, Access, Other Applications and File Handling. If you think that I missed any useful code, please feel free to write us. We will respond with a solution with in couple of days and publish here to make it available for all VBA Users.100+ Excel VBA макросов коды Примеры | excel VBA makro kod örnekleri | Excel的VBA宏代码范例 | Excel-VBA-Makros Codes Beispiele | excel VBA Macro Codes Contoh | एक्सेल VBA मैक्रो कोड्स उदाहरण.

Most Useful Excel VBA Codes - 100+ Examples for Basic and Advanced Users

The Most Useful VBA Codes Excel Examples Macros! Learning Path

Learn VBA Codes Excel Examples Macros and do almost everything! We have explained verity of examples to cover most frequently used codes. Start learning …!

Cells and Range Objects : VBA Codes Excel Examples Macros

Reading data from a Cell was my first exciting VBA Code when I stared learning Excel VBA. Then I successfully write data to Excel Cells. That was my first happy moment which motivated me to engage with VBA from last 10 years.

Here are the common VBA Codes Excel Examples Macros to deal with Cell and Range Objects of Worksheet.You can find examples on reading and writing the data, selecting, copying and pasting the data.Clearing, changing font color, font to bold, background color of cells or range. We can also see the examples on merging cells, adding comments and changing the font case to lower or upper using Excel VBA.

Top

Rows and Columns Objects: VBA Codes Excel Examples Macros

Cell and Range objects helps to reading and writing the data from worksheet. Now we will look into Rows and Columns of the Worksheet, helps to show or hide the data. Here you can find the VBA Codes Excel Examples Macros on delete rows, columns, change row height, column width. Hiding or un-hiding columns or rows. Inserting or deleting Rows or Columns. And finding the Last row, or columns in the worksheet in different situations.

Top

Worksheet and Workbook Objects: VBA Codes Excel Examples Macros

Now we will see the VBA Codes Excel Examples Macros on Worksheet and Workbook Objects. Here you can find creating new workbook, opening, closing and saving workbook. And example to show running a macro on opening or staring the workbook. We will also see how to protecting or unprotecting Excel workbooks or worksheets, Copying the data from one worksheet to another worksheet.Hiding and unhiding worksheets. Changing the tab color of worksheet. Activating workbooks or worksheet, etc.

Top

Hyperlink: VBA Codes Excel Examples Macros

Hyperlinks are most widely used concepts in Excel. We generally use hyperlinks to navigate or open a file, link or folder. But we can do many other things using Hyperlinks. Examples in this topic will show you the power of Hyperlinks in Excel VBA. We have covered the following examples in this tutorial:

  • Add Create Hyperlinks in Excel VBA
  • Removing Hyperlinks in Excel VBA
  • VBA Open File Folder Website Using FollowHyperlink method in Excel
  • VBA Create Send Emails Using FollowHyperlink Method – Send Keys in Excel

Hyperlinks in Excel VBA – Explained with Examples!

Charts : VBA Codes Excel Examples Macros

Another powerful tool in Excel is charting. You can create rich visualized dashboards using Excel Charts and VBA. We have covered most commonly used Charting VBA Codes Excel Examples Macros. In this topic will show you how to deal with different chart objects to automate the charting process using VBA. Examples on creating charts, changing chart types. Changing chart axes titles, chart title, axis format, primary and secondary axes. We have provided more than 33 example to cover A-z of Excel Chart VBA.

  • Example tutorials on Creating Charts using Excel VBA
  • Example tutorials on Chart Type using Excel VBA
  • Example Tutorials on Formatting Chart Objects using Excel VBA
  • Example Tutorials on Chart Collection in Excel VBA
  • Other useful Examples and tutorials on Excel VBA Charting
  • Excel VBA Charting Constants and Enumeration

Excel Chart VBA Examples and Tutorials

Tables: VBA Codes Excel Examples Macros

Tables in Excel help to manage our data in Excel and give the more control over the data. In this example we will see the different VBA Codes Excel Examples Macros like creating tables, sorting tables data and applying filters in the data. We have provided following example to deal with Tables using Excel VBA.

  • Create Tables in Excel VBA
  • Sorting Tables in Excel VBA
  • Filtering Tables in Excel VBA
  • Clear Toggle Table Filters in Excel VBA

Tables in Excel VBA – Explained with Examples!

Top

Pivot Tables: VBA Codes Excel Examples Macros

Pivot tables help us to summarize the data and analyze it. VBA Codes Excel Examples Macros provided for creating pivot tables, pivot charts, adding calculated, changing row fields, column fields, value field fields in pivot tables using VBA. We have provided the following example to deal with pivot tables using Excel VBA, we will add some more examples to do more tasks using pivot tables.

  • Creating Pivot Tables in Excel VBA
  • Create Pivot Chart using Excel VBA
  • Create Pivot Column Chart using Excel VBA
  • Create Calculated Pivot Field in Excel VBA

Pivot Tables in Excel VBA – Explained with Examples!

Names: VBA Codes Excel Examples Macros

Names are another time saving feature in the Excel. We can define the names to Cells, Ranges and Tables and use them across the worksheets in the workbook. Her we will see how to add or remove the Names using VBA. Hiding and un-hiding names from the users. Follwing examples covered in this topic to deal with Names using Excel VBA.

  • Adding Names in Excel VBA
  • Deleting Names in Excel VBA
  • Hide UnHide Names in Excel VBA

Names in Excel VBA – Explained with Examples!

Top

Other Applications: VBA Codes Excel Examples Macros

VBA is powerful, it can interact with the other applications like MS Word, PowerPoint, Access, Outlook, Internet Explorer, VBScript etc. Here are examples to deal with other application from Excel. Below examples are covered in this tutorial:

  • Interact with PowerPoint from Excel VBA
  • Dealing with MS Word From Excel VBA
  • Interact with MS Access from Excel VBA
  • Interact with Outlook from Excel VBA
  • Dealing with Internet Explorer
  • Dealing with Other Applications from Excel VBA – Calculator
  • Run VBScript from Excel VBA
  • VBA to Attach Send An Excel Chart to Outlook Email

Excel VBA to Interact with Other Applications

Application Object: Excel VBA Codes Examples Macros

Mastering the Application Objects Examples help you to fasten your VBA programs and writing optimized code in Excel VBA. Examples for stopping or displaying application alerts, stopping or enabling screen updating, stopping or enabling application events. The following example procedures and functions are covered in this topic to fasten and speed up VBA code processing.

  • Stop Screen Updating
  • Stop Events in Excel VBA – Disable Enable
  • Stop Application Alerts in Excel VBA – Disable Enable
  • Display Progress on Statusbar in VBA Excel
  • Set Windows State in Excel VBA – Minimize Maximize Normal
  • Toggle Full Screen in Excel VBA
  • Get User Name in VBA Excel
  • Stop Calculations in Excel VBA – Manual Automatic
  • Open Visual Basic Editor (VBE) – Open Module with VBA
  • VBA to Exit from Procedure or Function

Fasten VBA Code – Application Objects Explained with Examples

File Handling: Excel VBA Examples Macros Codes

File handling examples are provided to creating deleting files, folders. Copying Files and Folders and Moving from one location to another location. And displaying file or folder dialog boxes to browse the files or folders. And check if files exists in a folder using VBA.

  • Check if Folder Exists using Excel VBA
  • Opening Folders using VBA Excel
  • Creating Folders in Excel VBA
  • Copying Folders From One Location to Another in Excel VBA
  • Move Folder From One Location to Another in Excel VBA
  • Deleting Folders in VBA Excel
  • Make File Read Only in VBA Excel
  • Copy all Excel Files One Folder to Another in VBA Excel
  • Opening Files Using File Dialog Box in Excel VBA
  • Customize File or Folder Dialog Box in VBA Excel
  • Excel VBA File Dialog Box – Displaying Vanilla Dialog Box to Pick Files

Folders and File Handling in Excel VBA

Some more VBA examples are added to deal with Files and Folders using Excel VBA:

Top

Miscellaneous Excel VBA Examples Macros Codes
 Related Resource External VBA Reference

analysistabs.com

Запуск и создание макросов в Excel для чайников

VBA коды для простых программ макросов необходимых при работе с таблицами на каждый день.

Создание и выполнение макросов

Макрос для копирования листа в Excel c любым количеством копий.Практический пример с описанием и исходным кодом макроса для одновременного копирования любого количества рабочих листов. Макрос для выделения и удаления пустых столбцов в Excel.Выделение, удаление, скрытие и добавление пустых столбцов в таблицу по условию пользователя с помощью макросов. Исходные коды предоставляться с описанием и примерами в картинках. Макрос для выделения и удаления пустых строк в Excel.Примеры макросов с исходными кодами для удаления, выделения, добавления и скрытия пустых строк в исходной таблице данных.  Макрос для выделения ячеек со снятой защитой на листе Excel.Исходный код с примером и описанием для VBA-макроса выделения ячеек у которых, снятая защита от ввода значений данных и редактирования. Макрос для поиска ячеек в Excel с выпадающим списком и условным форматированием.Исходный VBA-код макроса для поиска выпадающих списков на рабочем листе. Исходный код VBA-макроса для поиска ячеек с условным форматированием. Макрос для выделения ячеек Excel по условию больше меньше.2 исходных кода макросов для выделения ячеек по условию больше равно или меньше равно относительно их числовых значений. Макрос для выборочного выделения ячеек на листе Excel.Исходный код макроса для автоматического выделения отдельных ячеек по заданному условию в критериях поиска значений. Как быстро выделять несмежные диапазоны макросом? Макрос для добавления строк с заданной высотой в таблицу Excel.Пошаговое руководство по написанию кода макроса VBA для умной вставки пустых строк между ячейками таблицы. Как автоматически вставить строки через одну ячейку? Как сделать границы ячеек макросом в таблице Excel.Как автоматически рисовать границы в таблицах с помощью макроса? Автоматизированное форматирование границ ячеек по разным цветам, стилям и толщине линий используя макрос. Макрос для объединения повторяющихся ячеек в таблице Excel.Практический пример и пошаговый разбор исходного VBA-кода макроса для автоматического объединения повторяющихся одинаковых ячеек в строках таблицы. 1 2 3 4

exceltable.com

Полезные макросы для Excel - Александр Потокин

Макрос для интерактивного добавления фотографий

При наличии на листе ячеек, содержащих имя фотографии в формате jpg, макрос вставляет в данную ячейку указанную фотографию.

Удобно использовать в сводных таблицах: создаем поле группировки "Фото", в котором выводим наименование файлов картинок, макрос вызываем событием Worksheet_PivotTableUpdate. При удалении группировки по столбцу "Фото" все картинки на листе будут удалены. При любом изменении структуры сводной таблицы, фотографии будут перезакреплены.

Sub foto()     On Error GoTo lab    Application.StatusBar = "Проставляем фото"    Application.ScreenUpdating = False    For Each Pic In ActiveSheet.Shapes 'удаляем все картинки на текущем листе        If Pic.Type = msoPicture Or Pic.Type = msoLinkedPicture Then Pic.Delete    Next    ActiveSheet.UsedRange.EntireRow.AutoFit    'проверяем наличие поля "Фото" (для сводной не использовать)    If ActiveSheet.UsedRange.Find(what:="Фото", LookAt:=xlWhole) Is Nothing Then GoTo lab    'оптимизация поиска при работе со сводной    'Dim pt As PivotTable    'Set pt = ActiveSheet.PivotTables(1)    'проставляем фото из указанной директории    For Each c In ActiveSheet.UsedRange 'In pt.PivotFields("Фото").DataRange.Cells        If InStr(c.Value, ".jpg") > 0 Then            c.ColumnWidth = 10            filepath = "c:\photo\" & LTrim(Left(c.Value, InStr(c.Value, ".jpg") + 3)) 'указать каталог с фотографиями            on error goto er            Set ph = c.Parent.Pictures.Insert(filepath)            on error goto lab            ph.Top = c.Top: ph.Left = c.Left: k = ph.Width / ph.Height            ph.Width = c.Width: ph.Height = ph.Width / k            c.EntireRow.RowHeight = ph.Height + 1            ph.Placement = xlMoveAndSize            Application.StatusBar = "Вставляю фото. Выполнено " & Round((c.Row / ActiveSheet.UsedRange.Rows.Count * 100), 2) & " %. Обработано " & c.Row & " товаров из " & ActiveSheet.UsedRange.Rows.Count            'Application.StatusBar = "Выводим фото. Выполнено " & Round((c.Row / pt.PivotFields("Фото").DataRange.Rows.Count * 100), 2) & " %. Обработано " & c.Row & " строк из " & pt.PivotFields("Фото").DataRange.Rows.Count

        End If        er: set ph=nothing    Nextlab: Application.StatusBar = "Готово"    If (Err.Number <> 1004) And (Err.Number <> 0) Then MsgBox "Произошла ошибка # " & Str(Err.Number) & " сгенерирована " & Err.Source & Chr(13) & Err.Description    Application.ScreenUpdating = TrueEnd Sub

Макрос для формализации сводной таблицы

Заполняет пустые ячейки значениями сверху вниз.

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

Sub Fill_The_Name()Dim s As VariantDim i, j As Integer    s = ActiveCell.Value    For j = ActiveCell.Column To Selection.Columns.count + ActiveCell.Column        For i = ActiveCell.Row + 1 To ActiveCell.SpecialCells(xlLastCell).Row            If (IsEmpty(Cells(i, j))) Or (Cells(i, j) = " ") Then Cells(i, j) = s Else s = Cells(i, j)        Next i        s = Cells(ActiveCell.Row, j + 1)    Next jEnd Sub

Макрос для удаления скрытых картинок с листа Excel

Если высота картинки меньше 5 уе, то она будет удалена.

Sub Pict_hidden_delete()    Dim Shp As Object    Application.ScreenUpdating = False    On Error Resume Next    For Each Shp In ActiveSheet.Shapes        With Shp        If (.Type = msoPicture) Or (.Type = msoLinkedPicture) Then             If .Height< 5 Then .Delete  'критерий отбора жертвы        End If        End With    Next    Application.ScreenUpdating = TrueEnd Sub

PS работа с картинками в Excel - отдельная песня, правильней использовать СУБД.

Макрос для удаления чуждых символов (иероглифов)

Перед запуском выделить обрабатываемую область листа Excel.

Sub remove_unicode()Dim st, st2 As String    For Each c In Selection        st = ""        For j = 1 To Len(c.Value)            simv = AscW(Mid(c.Value, j, 1))            If (simv > 10) And (simv < 2000) Then st = st +ChrW(simv)        Next j        c.Value = Trim(st)    NextEnd Sub

Макрос для преобразования размерной сетки в построчный формат

Sub transf_razm_setki()'преобразование размерной сетки в построчные размеры для загрузки на сайт'в первом столбце размерная сетка через запятую'во второй столбец выводится размер данной строки

i = 3While Len(Cells(i, 1)) > 0    If InStr(Cells(i, 1), ",") > 0 Then        Rows(i).Select        Application.CutCopyMode = False        Selection.Copy        Rows(i + 1).Select        Selection.Insert Shift:=xlDown        Cells(i + 1, 1) = Trim(Right(Cells(i, 1), Len(Cells(i, 1)) - InStr(Cells(i, 1), ",")))        Cells(i, 2) = Trim(Left(Cells(i, 1), InStr(Cells(i, 1), ",") - 1))    Else: Cells(i, 2) = Cells(i, 1)    End If    i = i + 1WendEnd Sub

www.potok.in

Примеры макросов и теория анализа работы предприятия

Если экономист хочет "посчитать" своё предприятие - он должен уметь программировать на VBA - как минимум!

Когда читаешь статьи про эффективность внедрения ERP-систем и показатели даже не на проценты, а в разы превосходящие эффективность от работы штатных экономистов - сразу возникает вопрос о степени компетентности этих самых сотрудников, а не о гиперэффективности системы. Ведь в ERP заложены методики обработки из учебников! На своём опыте могу сказать, что чаще всего для глубокого "ручного" анализа нехватает лишь знаний по сведению и упорядочиванию разношёрстной информации, выдаваемой системами автоматизированного учёта типа 1С.

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

  1. Очистить помесячные отчёты от избыточной информации.
  2. Заполнить пустые ячейки нолями.
  3. Отсортировать по наименованию.
  4. Все наименования скопировать на одну страницу, отсортировать по наименованию и удалить дубликаты.
  5. Создать макрос, который соберёт для каждого наименования со всех страниц данные о продажах по месяцам и разложит их аккуратно в свои столбцы.
  6. Создать итоговую колонку и просуммировав - получить итоговые величины количества, сумм продаж, дохода и прочее.
  7. Автоматически создать список из трёх товаров - замещающих и сопутствующих.

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

http://opencomputer.narod.ru/allmacroses.htm

Экономист в нынешних условиях либо будет знать VBA, либо его ждёт сокращение, когда средства автоматизации дойдут до внедрения на его фирме. Эти макросы не так уж сложны - их реально доработать до полностью автоматического режима, когда всё будет вычисляться в одно нажатие. Можно сделать макрос очистки, который найдёт самые похожие строки/области данных, а все остальные автоматически убьёт и в результате пользователю только надо будет указать столбцы с данными по продажам (количество и суммы) - остальное алгоритм сделает сам. Это не очень сложно - ещё десяток-другой строк кода...

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

Автоматическое форматирование кода VBA можно осуществить с помощью бесплатного дополнения: http://oaltd.co.uk/Indenter/Default.htm Сохранить код VBA в HTML-файл с расцветкой поможет Notepad++

Массивы

Массивы на VBA описываются через конструкцию типа:

' Матрица 3х4 целого типа Dim MatrixI(3, 4) As Integer ' Матрица 3х4 вещественного типа Dim MatrixD(3, 4) As Double

Dim MyWeek, MyDay MyWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun") ' Return values assume lower bound set to 1 (using Option Base ' statement). MyDay = MyWeek(2) ' MyDay contains "Tue". MyDay = MyWeek(4) ' MyDay contains "Thu".

Большее количество примеров можно найти в справке по VBA.

Тренды

Создать график. Щёлкнуть правой кнопкой мыши на линии графика -> Добавить линию тренда.

  • Линейная - покажет общую динамику
  • Полиномиальная - в зависимости от степени покажет усреднённые показатели в разные моменты времени.

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

Анализ по дням недели, дням месяца

A B C D 1 2 3 4
 
2007.01.01 40 =ДЕНЬНЕД(A1;2) =СУММЕСЛИ(C:C;"=1";B:B)
2007.01.02 42 2 42
2007.01.03 36 3 36
2007.01.04 50 4 50

Аналогично для дней месяца и любых других условий. Но при сведении в единый отчёт и выведении средних - необходимо рассчитать количество понедельников, вторников и прочее по формуле =СЧЁТЕСЛИ(C:C;"=1")

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

Можно на одном и том же графике продаж разместить линию тренда и результат вычисления функции ПРЕДСКАЗ() для уже известных продаж на 30 дней назад от текущей позиции - тогда на графике можно будет наблюдать насколько эффективной оказалась работа отдела продаж по отношению к ожидаемой.

Расчёт объёмов пополнения склада вполне можно доверить машине - надо только сделать правильный макрос.

Инструмент Excel - Поиск решения

2008-10-31

Задачи экономики требуют поиска оптимальных значений. Применяя надстройку из пакета анализа MS Excel Поиск решения это становится возможным. Чтобы подробнее ознакомиться с теорией вопроса необходимо изучить часть высшей математики - методы оптимизации, симплекс метод - мини-максная стратегия поиска. На мехмате всё это даётся в полной мере, тогда как на экономических специальностях не рассматривается ни теория, ни применение в Excel. Важное замечание: всегда указывайте интервалы в которых будут изменяться исходные величины. Старайтесь строить предварительные графики, чтобы локализовать искомую точку.

Скачать пример: simplex.zip (16Kb)

 

Оригинал статьи: vbasamples.htm

November 2, 2009

Алексей Вячеславович Никитаев. mail: [email protected]

Hosted by uCoz

opencomputer.narod.ru

Создание и удаление макросов в Excel для Mac с помощью вкладки "Разработчик"

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

Для автоматизации повторяющихся задач, можно выполнять повторяющиеся задачи с одним щелчком мыши, можно использовать вкладку "Разработчик" в Excel для Mac для записи и запуск макроса. Кроме того, можно создать макрос с помощью редактор Visual Basic в Microsoft Visual Basic для приложений (VBA), чтобы записать свои собственные программный код макроса. Если вы больше не используете макроса, ее можно удалить.

Запись макроса

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

  1. Если вкладка Разработчик недоступна, выполните перечисленные ниже действия, чтобы открыть ее.

    1. Выберите Excel > настройки > панель инструментов и ленты.

    2. В разделе Настройка ленты выберите Основные вкладки и установите флажок Разработчик.

    3. Нажмите кнопку Сохранить и закройте Параметров Excel.

    Настройка вкладки "Разработчик" в Excel для Mac
  2. На вкладке Разработчик нажмите кнопку Запись макроса.

    Примечание: Чтобы создать макрос из VBE, щелкните Visual Basic. В окне кода модуля введите код макроса.

    Запись макроса в Excel для Mac
  3. В поле Имя макроса введите имя макроса.

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

  4. В списке Сохранить в выберите вариант эта книга.

  5. В поле Сочетание клавиш введите прописную или строчную букву, которую хотите использовать.

    Форма для записи макросов в Excel для Mac

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

  6. Введите описание макроса в поле Описание и нажмите кнопку ОК.

  7. Завершите запись макроса.

  8. На вкладке Разработчик щелкните Остановить запись.

К началу страницы

Удаление макроса

  1. На вкладке Разработчик нажмите кнопку Макросы.

  2. Выберите в списке макрос, который требуется удалить, и нажмите кнопку "Удалить".

    Выберите макрос и щелкните знак минуса, чтобы удалить его.

К началу страницы

См. также

Запуск макроса

support.office.com