Макросы в эксель 2010

  • автор:

⇐ ПредыдущаяСтр 12 из 148

  1. E) Работа в цикле
  2. II. Работа над текстом и его оформление
  3. IV. Работа над задачами.
  4. IV. Работа над задачами.
  5. IV. Работа над задачами.
  6. IV. Работа над задачами.
  7. IV. Работа над задачами.
  8. IV. Работа над новым материалом.
  9. IV. Работа над новым материалом.
  10. IV. Работа над новым материалом.

Для выполнения, отладки, изменения, удаления макроса, а также для создания нового макроса, можно воспользоваться диалоговым окном Макрос. Чтобы вызвать данное окно, на вкладке Вид в группе Макросы нажмите одноименную кнопку или воспользуйтесь сочетанием клавиш Alt+F8.

В диалоговом окне Макрос (Рис. 44) представлен полный список макросов, доступных для работы. Чтобы посмотреть макросы, сохраненные в других файлах, воспользуйтесь списком Макросы из.

Рис. 44. Диалоговое окно Макрос

В частности, диалоговое окно Макрос для выделенного в списке макроса дает возможность:

§ выполнить макрос. Это удобно для запуска макросов, которые используются очень редко;

§ изменить или отладить макрос с помощью редактора Visual Basic, который позволяет изменять макросы и создавать собственные программы;

§ удалить макрос.

Задание 7. В списке Макросы из выберите название текущего файла-документа (Документ) и удалите ранее созданные макросы. Затем, если работа с макросами закончена, то верните настройки безопасности в первоначальное состояние (Отключить все макросы с уведомлением).

Приложение

Контрольные задания по теме «Электронные формы»

Вариант 1

1. Создайте шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными.

Директору

ООО «Нева» от

фио работника

Заявление на отпуск

Прошу предоставить мне очередной отпуск сроком на выбрать элемент списка с дата1.

дата2

фио работника

Вариант 2

1. Создайте шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными.

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

Приказ № №приказа от дата1

Табельный номер работника: табельный №

ФИО работника: фио

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

Дата начала: дата2

Дата конца: дата3

Вариант 3

1. Создайте шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными.

Приказ о расторжении трудового договора (увольнении)

Приказ об увольнении № №приказа от дата

Табельный номер работника: табельный №

ФИО работника: фио

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

Основание увольнения: выбрать элемент из списка

Вариант 4

1. Создайте шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными.

В организация1

От ООО «Нева»

Претензия

дата1 между нашими компаниями был заключен Договор купли-продажи № №договора, в соответствии с которым продавец (организация1) обязался передать покупателю (ООО «Нева») товар в ассортименте:

выбрать элемент из списка

выбрать элемент из списка

Однако дата2 товар был поставлен в ассортименте, не соответствующем указанному Договору.

Просим осуществить замену товара в соответствии ассортимента, указанного в Договоре.

дата3 должность

ООО «Нева»

фио

Вариант 5

1. Создайте шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными.

Руководителю магазин

От фио

проживающего по адресу адрес

Претензия

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

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

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

Прошу произвести замену товара надлежащего качества на аналогичный товар.

дата2 фио

Вариант 6

1. Создайте шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными.

Директору

организация

фио руководителя

Гарантийное письмо

Я, гражданин фио (паспорт паспорт), прошу Вас выполнить услуги в виде:

выбрать элемент из списка

выбрать элемент из списка

Оплату в полном размере гарантирую в течение количество дней банковских дней.

дата фио

Вариант 7

1. Создать шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными

Начальнику телефонного узла района

район

от

фио

Заявление

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

дата фио

Вариант 8

1. Создать шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными

В отдел номер ЗАГСа

г. Санкт-Петербурга

от фио1 ,

проживающего

адрес ,

паспорт паспорт

Заявление

Прошу выдать свидетельство о рождении на гр. фио2, дата рождения дата1.

Регистрация рождения произведена дата2 в место регистрации.

Родители:

Отец: фио отца

Мать: фио матери

Указанное свидетельство требуется для:

выбрать элемент из списка

дата3 фио1

Вариант 9

1. Создать шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными

Директору

организация1

фио руководителя

Наша организация ООО «Нева» занимается поставкой компьютерного оборудования и программного обеспечения с год года.

Мы поставляем оборудование марок:

выбрать элемент из списка,

выбрать элемент из списка,

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

Наш адрес: адрес .

дата фио

Вариант 10

1. Создать шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными

Управление государственного страхования

область

Инспекция государственного страхования по

город

район

Письмо-распоряжение

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

Начальник управления

государственного страхования

дата фио

Вариант 11

1. Создать шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными

Личный листок

ФИО студента фио

Факультет выбрать элемент из списка

Курс выбрать элемент из списка

Группа выбрать элемент из списка

Специальность выбрать элемент из списка

Сроки практики с дата1 по дата2

Вариант 12

1. Создать шаблон, используя элементы Форм.

2. Создайте документ на основе шаблона и заполните его произвольными данными

Заведующему кафедрой

кафедра

фио1

Гарантийное письмо

организация гарантирует прохождение производственной практики студенту(ке) фио2

группы выбрать элемент из списка

факультета выбрать элемент из списка

с дата1 по дата2.

Консультирование и предоставление необходимых материалов гарантируем.

дата3 должность

фио3

Контрольные задания по теме «рассылка писем»

Источник данных (№1):

Источник данных (№2):

Вариант.

На основе соответствующего основного документа и источника данных №1 получите следующий результат слияния (письма):

Используйте поле IF для получения обращения Уважаемая и Уважаемый. Установите фильтр по кредитному лимиту (до 40000 руб.).

Вариант.

На основе соответствующего основного документа и источника данных №1 получите следующий результат слияния (письма):

Используйте поле IF: если льготный период кредитования больше 100 дней, то устанавливается 3% за кредит, в противном случае — 5%.

Вариант.

На основе соответствующего основного документа и источника данных №1 получите следующий результат слияния (письма):

Установите сортировку по фамилии.

Вариант.

На основе соответствующего основного документа и источника данных №1 получите следующий результат слияния (письма):

Используйте поле IF для получения обращения Господин и Госпожа. Установите фильтр по кредитному лимиту (больше 25000 руб.).

Вариант.

На основе соответствующего основного документа и источника данных №1 получите следующий результат слияния (письма):

Установите фильтр по полу.

Вариант.

На основе соответствующего основного документа и источника данных №2 получите следующий результат слияния (письма):

Установите сортировку по количеству баллов.

Вариант.

На основе соответствующего основного документа и источника данных №2 получите следующий результат слияния (письма):

Используйте поле IF:

§ для группы 101 первое занятие состоится 02.09 в 9.00 в аудитории № 101;

§ для группы 102 первое занятие состоится 03.09 в 10.00 в аудитории № 102.

Установите сортировку по № группы и по фамилии.

Вариант.

На основе соответствующего основного документа и источника данных №2 получите следующий результат слияния (письма):

Используйте поле IF:

§ при № группы меньше 102 празднование состоится на 3 этаже;

§ для остальных групп – на 2 этаже.

Установите сортировку по № группы и по фамилии.

Вариант.

На основе соответствующего основного документа и источника данных №2 получите следующий результат слияния (письма):

Используйте поле IF:

§ для группы № 101 время работы с 16.00 до 18.00;

§ для остальных групп – с 18.00 до 20.00.

Установите фильтр по году рождения (1992).

Вариант.

На основе соответствующего основного документа и источника данных №2 получите следующий результат слияния (письма):

Установите сортировку по фамилии.

Контрольные задания по теме «макросы»

Напоминаем, что в конце работы, после предъявления её преподавателю, необходимо удалить созданные Вами макросы и вернуть настройки безопасности в первоначальное состояние (отключить все макросы с уведомлением).

Вариант 1

Создайте внутренний макрос для текущего документа, меняющий цвет и начертание выделенного текста. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 2

Создайте внутренний макрос для текущего документа, удаляющий текст от начала строки до позиции курсора (нажатие клавиш Shift+Home). Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 3

Создайте внутренний макрос для текущего документа, вставляющий в документ определенного вида таблицу. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 4

Создайте внутренний макрос для текущего документа, центрирующий данные в таблице и устанавливающий другой размер шрифта. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 5

Создайте внутренний макрос для текущего документа, меняющий ориентацию страниц в документе. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 6

Создайте внутренний макрос для текущего документа, меняющий интервал между абзацами в документе. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 7

Создайте внутренний макрос для текущего документа, меняющий текущий регистр букв в абзаце на прописные. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 8

Создайте внутренний макрос для текущего документа, удаляющий текст от текущей позиции курсора до конца текущей строки. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 9

Создайте внутренний макрос для текущего документа, устанавливающий отступы и выравнивание в выделенном абзаце. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 10

Создайте внутренний макрос для текущего документа, обрисовывающий границей выделенный абзац. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 11

Создайте внутренний макрос для текущего документа, изменяющий цвет фона и цвет текста для выделенного абзаца. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

Вариант 12

Создайте внутренний макрос для текущего документа, изменяющий направление текста в таблице. Присвойте имя макросу. Создайте кнопку, измените вид кнопки или закрепите макрос за комбинацией клавиш (по выбору преподавателя).

ВВЕДЕНИЕ.. 3

ЭЛЕКТРОННАЯ ФОРМА.. 4

ОПРЕДЕЛЕНИЕ МАКЕТА ФОРМЫ… 5

НАСТРОЙКА WORD ДЛЯ СОЗДАНИЯ ФОРМЫ… 6

ОТКРЫТИЕ ШАБЛОНА ИЛИ ДОКУМЕНТА, НА ОСНОВЕ КОТОРОГО БУДЕТ СОЗДАНА ФОРМА 7

СОЗДАНИЕ СТРУКТУРЫ ФОРМЫ (ПОСТОЯННАЯ ЧАСТЬ) 7

СОЗДАНИЕ СТРУКТУРЫ ФОРМЫ (ПЕРЕМЕННАЯ ЧАСТЬ) 8

ДОБАВЛЕНИЕ ЭЛЕМЕНТОВ УПРАВЛЕНИЯ СОДЕРЖИМЫМ В ФОРМУ.. 8

НАСТРОЙКА СВОЙСТВ ЭЛЕМЕНТОВ УПРАВЛЕНИЯ СОДЕРЖИМЫМ… 10

НАСТРОЙКА ЭЛЕМЕНТА УПРАВЛЕНИЯ «ОБЫЧНЫЙ ТЕКСТ». 10

НАСТРОЙКА ЭЛЕМЕНТА УПРАВЛЕНИЯ «ДАТА». 11

НАСТРОЙКА ЭЛЕМЕНТА УПРАВЛЕНИЯ «РАСКРЫВАЮЩИЙСЯ СПИСОК». 12

НАСТРОЙКА ЭЛЕМЕНТА УПРАВЛЕНИЯ «ФОРМАТИРОВАННЫЙ ТЕКСТ». 13

НАСТРОЙКА ЭЛЕМЕНТА УПРАВЛЕНИЯ «ПОЛЕ СО СПИСКОМ». 13

ЗАЩИТА ЭЛЕМЕНТОВ УПРАВЛЕНИЯ СОДЕРЖИМЫМ ФОРМЫ… 14

УСТАНОВКА ЗАЩИТЫ ФОРМЫ… 14

ОТКРЫТИЕ И РЕДАКТИРОВАНИЕ ШАБЛОНА.. 16

ЗАПОЛНЕНИЕ И СОХРАНЕНИЕ ФОРМЫ… 18

ПРОСМОТР ФОРМЫ И ПЕЧАТЬ. 18

РАССЫЛКА ПИСЕМ… 19

СОЗДАНИЕ ПОСТОЯННОЙ ЧАСТИ ОСНОВНОГО ДОКУМЕНТА.. 20

СОЗДАНИЕ ИСТОЧНИКА ДАННЫХ.. 21

ВСТАВКА ПОЛЕЙ В ОСНОВНОЙ ДОКУМЕНТ. 22

ПРЕДВАРИТЕЛЬНЫЙ ПРОСМОТР РЕЗУЛЬТАТОВ СЛИЯНИЯ.. 27

ПОЛУЧЕНИЕ СОСТАВНОГО ДОКУМЕНТА.. 29

МАКРОСЫ… 31

СОЗДАНИЕ И ВЫПОЛНЕНИЕ МАКРОСА.. 33

РАБОТА С МАКРОСОМ… 38

ПРИЛОЖЕНИЕ.. 40

КОНТРОЛЬНЫЕ ЗАДАНИЯ ПО ТЕМЕ «ЭЛЕКТРОННЫЕ ФОРМЫ». 40

КОНТРОЛЬНЫЕ ЗАДАНИЯ ПО ТЕМЕ «РАССЫЛКА ПИСЕМ». 52

КОНТРОЛЬНЫЕ ЗАДАНИЯ ПО ТЕМЕ «МАКРОСЫ». 60

Безопасность макросов Excel – это защита от вирусов, которые могут проникнуть в компьютер вместе с макросами. Безопасность макросов значительно изменилась со времени выхода в свет Excel 2003, поэтому в рамках данной темы особенности старых и новых версий Excel будут рассмотрены раздельно:

  • Безопасность макросов в современных версиях Excel (2007 и более новых)
  • Безопасность макросов в Excel 2003

Безопасность макросов в современных версиях Excel (2007 и более новых)

Чтобы запустить макрос в современных версиях Excel, файл должен быть сохранён как Книга Excel с поддержкой макросов. Открывая такой файл, по его расширению xlsm Excel понимает, что в данной рабочей книге содержатся макросы (в отличие от файла со стандартным расширением xlsx).

Поэтому, чтобы иметь возможность запускать созданный в обычной рабочей книге Excel макрос всегда и везде, нужно сохранить её с расширением xlsm. Для этого на вкладке Файл (File) нажмите Сохранить как (Save as) и в поле Тип файла (Save as type) выберите Книга Excel с поддержкой макросов (Excel Macro-Enabled Workbook).

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

  1. Отключить все макросы без уведомления (Disable all macros without notification)Запрет на выполнение любых макросов. При открытии книги Excel не уведомляет о том, что эта книга содержит макросы, поэтому пользователь может не понять причину, по которой книга Excel не работает так, как должна.
  2. Отключить все макросы с уведомлением (Disable all macros with notification)Запрет на выполнение макросов. Однако, если в рабочей книге есть макрос, появится предупреждение о том, что макрос присутствует, но отключен.
  3. Отключить все макросы без цифровых подписей (Disable all macros except digitally signed macros)Разрешено выполнение макросов только из доверенных источников. Все прочие макросы выполняться не будут. При открытии книги Excel не уведомляет о том, что эта книга содержит макросы, поэтому пользователь может не понять причину, по которой книга Excel не работает так, как должна.
  4. Включить все макросы (Enable all macros)Разрешено выполнение всех макросов. При открытии книги Excel не появляется уведомление о том, что в ней содержатся макросы. Соответственно пользователь может не знать, что какой-либо макрос выполняется, пока открыт файл.

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

Чтобы разрешить выполнение макросов нужно просто кликнуть по кнопке Включить содержимое (Enable Content).

Где найти параметры безопасности макросов в Excel

Вот как можно просматривать или изменять параметры безопасности макросов в Excel 2007, 2010 или 2013:

Excel 2007

  • Откройте главное меню Excel, для этого кликните по большой круглой кнопке Office в левом верхнем углу. Далее нажмите Параметры Excel (Excel Options).
  • В появившемся диалоговом окне выберите Центр управления безопасностью (Trust Center) и нажмите кнопку Параметры центра управления безопасностью (Trust Center Settings).
  • В разделе Параметры макросов (Macro Settings) выберите нужный параметр и нажмите ОК.

Excel 2010 или 2013

  • На вкладке Файл (File) нажмите Параметры (Options).
  • В открывшемся диалоговом окне выберите Центр управления безопасностью (Trust Center) и нажмите кнопку Параметры центра управления безопасностью (Trust Center Settings).
  • В разделе Параметры макросов (Macro Settings) выберите нужный параметр и нажмите ОК.

Замечание: Потребуется перезапустить Excel, чтобы новые настройки безопасности макросов вступили в силу.

Надёжные расположения в современных версиях Excel (2007 и более новые)

Excel 2007, 2010 и 2013 позволяет объявить выбранные папки на жёстком диске компьютера как надёжные расположения. При открытии файлов, хранящихся в таких расположениях, Excel не выполняет стандартную проверку безопасности макросов. Другими словами, если поместить файл Excel в папку, которая объявлена надёжной, то при открытии этого файла все макросы, записанные в нём, будут запущены независимо от состояния параметров безопасности.

Microsoft по умолчанию присваивает некоторым расположениям статус надёжных, их список хранится в разделе параметров безопасности Excel. Открыть список надёжных расположений можно так:

  • Откройте главное меню Excel, для этого кликните по большой круглой кнопке Office в левом верхнем углу. Далее нажмите Параметры Excel (Excel Options).
  • В появившемся диалоговом окне выберите Центр управления безопасностью (Trust Center) и нажмите кнопку Параметры центра управления безопасностью (Trust Center Settings).
  • В меню слева нажмите Надёжные расположения (Trusted Locations).
  • На вкладке Файл (File) нажмите Параметры (Options).
  • В открывшемся диалоговом окне выберите Центр управления безопасностью (Trust Center) и нажмите кнопку Параметры центра управления безопасностью (Trust Center Settings).
  • В меню слева нажмите Надёжные расположения (Trusted Locations).

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

  • В разделе Надёжные расположения (Trusted Locations) нажмите кнопку Добавить новое расположение (Add new location).
  • Выберите папку, которую нужно объявить надёжным расположением и нажмите ОК.

Предупреждение: Не рекомендуется добавлять в список надёжных расположений большие разделы жёсткого диска, например, целиком папку Мои документы (My Documents), так как при этом значительно возрастает риск выполнения макроса, полученного из ненадёжного источника.

Безопасность макросов в Excel 2003

В Microsoft Office 2003 существует 4 уровня безопасности макросов, управлять которыми можно в меню Excel. К ним относятся:

  1. Высокая / Очень высокаяЭти два уровня разрешают запуск макросов только из надёжных источников. Все прочие макросы не будут запущены. При открытии книги Excel не уведомляет о том, что эта книга содержит макросы, поэтому пользователь может не понять причину, по которой книга Excel не работает так, как должна.
  2. СредняяЕсли в книге присутствуют макросы, то при открытии книги появится окно уведомления с вопросом, следует ли эти макросы запускать.
  3. НизкаяВсе макросы выполняются. При открытии книги Excel не появляется уведомление о том, что в ней содержатся макросы. Соответственно пользователь может не знать, что какой-либо макрос выполняется, пока открыт файл.

Если хотите запустить макрос в Excel 2003, то безопасность макросов должна быть выбрана Низкая (Low) или Средняя (Medium).

Чтобы открыть и настроить параметры безопасности макросов в Excel 2003, нужно:

  • Открыть меню Сервис > Макрос > Безопасность (Tools > Macro > Security):
  • Установить нужный уровень безопасности и нажать ОК.

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

Урок подготовлен для Вас командой сайта office-guru.ru

Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

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

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

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

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

В Excel нажмите комбинацию клавиш alt + F11. Это приведет вас к редактору VBA в MS Excel. Затем щелкните правой кнопкой мыши папку Microsoft Excel Objects слева и выберите Insert => Module. Это место, где сохраняются макросы. Чтобы использовать макрос, вам нужно сохранить документ Excel как макрос. Из табуляции File => Save as, выберите Save as macro-enabled Workbok (расширение .xlsm) Теперь пришло время написать свой первый макрос!

1. Копирование данных из одного файла в другой.

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

Sub CopyFiletoAnotherWorkbook() ‘Copy the data Sheets(«Example 1»).Range(«B4:C15″).Copy ‘Create a new workbook Workbooks.Add ‘Paste the data ActiveSheet.Paste ‘Turn off application alerts Application.DisplayAlerts = False ‘Save the newly file. Change the name of the directory. ActiveWorkbook.SaveAs Filename:=»C:\Temp\MyNewBook.xlsx» ‘Turn application alerts back on Application.DisplayAlerts = True End Sub

2. Отображение скрытых строк

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

Sub ShowHiddenRows() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

3. Удаление пустых строк и столбов

Пустые строки в Excel — может быть проблемой для обработки данных. Вот как избавиться от них:

Sub DeleteEmptyRowsAndColumns() ‘Declare your variables. Dim MyRange As Range Dim iCounter As Long ‘Define the target Range. Set MyRange = ActiveSheet.UsedRange ‘Start reverse looping through the range of Rows. For iCounter = MyRange.Rows.Count To 1 Step -1 ‘If entire row is empty then delete it. If Application.CountA(Rows(iCounter).EntireRow) = 0 Then Rows(iCounter).Delete ‘Remove comment to See which are the empty rows ‘MsgBox «row » & iCounter & » is empty» End If ‘Increment the counter down Next iCounter ‘Step 6: Start reverse looping through the range of Columns. For iCounter = MyRange.Columns.Count To 1 Step -1 ‘Step 7: If entire column is empty then delete it. If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then Columns(iCounter).Delete End If ‘Step 8: Increment the counter down Next iCounter End Sub

4. Нахождение пустых ячеек

Sub FindEmptyCell() ActiveCell.Offset(1, 0).Select Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop End Sub #### 5. Заполнение пустых ячеек Как упоминалось ранее, пустые ячейки препятствуют обработке данных и созданию сводных таблиц. Вот один примерный код, который заменяет все пустые ячейки на 0. Этот макрос имеет очень большое приложение, потому что Вы можете использовать его для поиска и замены результатов N/A, а также других символов, таких как точки, запятые или повторяющиеся значения: Sub FindAndReplace() ‘Declare your variables Dim MyRange As Range Dim MyCell As Range ‘Save the Workbook before changing cells? Select Case MsgBox(«Can’t Undo this action. » & _ «Save Workbook First?», vbYesNoCancel) Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select ‘Define the target Range. Set MyRange = Selection ‘Start looping through the range. For Each MyCell In MyRange ‘Check for zero length then add 0. If Len(MyCell.Value) = 0 Then MyCell = 0 End If ‘Get the next cell in the range Next MyCell End Sub #### 6. Сортировка данных Следующий макрос сортирует по возрастанию все числа из столбца активной ячейки. Просто дважды нажмите любую ячейку из столбца, который вы хотите отсортировать. NB: Здесь нам нужно поставить этот код в Sheet1 (папка Microsoft Excel Objects), а не в Module1 (папка Modules): Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel As Boolean) ‘Declare your Variables Dim LastRow As Long ‘Find last row LastRow = Cells (Rows.Count, 1) .End (xlUp) .Row ‘Sort ascending on double-clicked column Rows («6:» & LastRow) .Sort _ Key1: = Cells (6, ActiveCell.Column), _ Order1: = xlAscending End Sub #### 7. Удаление пустых пространств Иногда данные в книге содержат дополнительные пробелы (whitespace charachters), которые могут мешать анализу данных и коррумпировать формулы. Вот один макрос, который удалит все пробелы из предварительно выбранного диапазона ячеек: Sub TrimTheSpaces() ‘Declare your variables Dim MyRange As Range Dim MyCell As Range ‘Save the Workbook before changing cells Select Case MsgBox(«Can’t Undo this action. » & _ «Save Workbook First?», vbYesNoCancel) Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select ‘Define the target Range. Set MyRange = Selection ‘Start looping through the range. For Each MyCell In MyRange ‘Trim the Spaces. If Not IsEmpty(MyCell) Then MyCell = Trim(MyCell) End If ‘Get the next cell in the range Next MyCell End Sub #### 8. Выделение дубликатов цветом Иногда в нескольких столбцах, которые мы хотели бы осветить, есть повторяющиеся значения. Этот макрос делает именно это: Sub HighlightDuplicates() ‘Declare your variables Dim MyRange As Range Dim MyCell As Range ‘Define the target Range. Set MyRange = Selection ‘Start looping through the range. For Each MyCell In MyRange ‘Ensure the cell has Text formatting. If WorksheetFunction.CountIf(MyRange, MyCell.Value) > 1 Then MyCell.Interior.ColorIndex = 36 End If ‘Get the next cell in the range Next MyCell End Sub #### 9. Выделение десяти самых высоких чисел Этот код будет отображать десять самых высоких чисел из набора ячеек: Sub TopTen() Selection.FormatConditions.AddTop10 Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1) .TopBottom = xlTop10Top ‘Change the rank here to highlight a different number of values .Rank = 10 .Percent = False End With With Selection.FormatConditions(1).Font .Color = -16752384 .TintAndShade = 0 End With With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 13561798 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False End Sub Вы можете легко настроить код, чтобы выделить различное количество чисел. #### 10. Выделение данных больших чем данные число Когда вы запустите этот код, появится окно. Вам надо написать число, которое вы хотите сравнить с выбранными ячейками. Sub HighlightGreaterThanValues() Dim i As Integer i = InputBox(«Enter Greater Than Value», «Enter Value») Selection.FormatConditions.Delete ‘Change the Operator to xlLower to highlight lower than values Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:=i Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1) .Font.Color = RGB(0, 0, 0) .Interior.Color = RGB(31, 218, 154) End With End Sub Вы тоже можете настроить этот код, чтобы выделить более низкие чисел. #### 11. Выделение ячеек комментариями Простой макрос, который выделяет все ячейки, содержащие комментарии: Sub HighlightCommentCells() Selection.SpecialCells(xlCellTypeComments).Select Selection.Style= «Note» End Sub #### 12. Выделение ячеек со словами с ошибками Это очень полезно, когда вы работаете с функциями, которые принимают строки, однако кто-то ввел строку с ошибкой, и ваши формулы не работают. Вот как решить эту проблему: Sub ColorMispelledCells() For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(Word:=cl.Text) Then _ cl.Interior.ColorIndex = 28 Next cl End Sub

13. Создание сводной таблицы

Вот как создать сводную таблицу в MS Excel (версия 2007). Особенно полезно, когда вы делаете индивидуальный отчет каждый день. Вы можете оптимизировать создание сводной таблицы следующим образом: Sub PivotTableForExcel2007() Dim SourceRange As Range Set SourceRange = Sheets(«Sheet1»).Range(«A3:N86″) ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=SourceRange, _ Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:=»», _ TableName:=»», _ DefaultVersion:=xlPivotTableVersion12 End Sub

14. Отправка активного файла по электронной почте

Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).

Sub SendFIleAsAttachment() ‘Declare your variables ‘Set reference to Microsoft Outlook Object library Dim OLApp As Outlook.Application Dim OLMail As Object ‘Open Outlook start a new mail item Set OLApp = New Outlook.Application Set OLMail = OLApp.CreateItem(0) OLApp.Session.Logon ‘Build your mail item and send With OLMail .To = «admin@datapigtechnologies.com; mike@datapigtechnologies.com» .CC = «» .BCC = «» .Subject = «This is the Subject line» .Body = «Hi there» .Attachments.Add ActiveWorkbook.FullName .Display ‘Change to .Send to send without reviewing End With ‘Memory cleanup Set OLMail = Nothing Set OLApp = Nothing End Sub

15. Вставка всех графиков Excel в презентацию PowerPoint

Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:

Sub SendExcelFiguresToPowerPoint() ‘Set reference to Microsoft Powerpoint Object Library ‘Declare your variables Dim PP As PowerPoint.Application Dim PPPres As PowerPoint.Presentation Dim PPSlide As PowerPoint.Slide Dim i As Integer ‘Check for charts; exit if no charts exist Sheets(«Slide Data»).Select If ActiveSheet.ChartObjects.Count < 1 Then MsgBox «No charts existing the active sheet» Exit Sub End If ‘Open PowerPoint and create new presentation Set PP = New PowerPoint.Application Set PPPres = PP.Presentations.Add PP.Visible = True ‘Start the loop based on chart count For i = 1 To ActiveSheet.ChartObjects.Count ‘Copy the chart as a picture ActiveSheet.ChartObjects(i).Chart.CopyPicture _ Size:=xlScreen, Format:=xlPicture Application.Wait (Now + TimeValue(«0:00:1»)) ‘Count slides and add new slide as next available slide number ppSlideCount = PPPres.Slides.Count Set PPSlide = PPPres.Slides.Add(SlideCount + 1, ppLayoutBlank) PPSlide.Select ‘Paste the picture and adjust its position; Go to next chart PPSlide.Shapes.Paste.Select PP.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True PP.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True Next i ‘Memory Cleanup Set PPSlide = Nothing Set PPPres = Nothing Set PP = Nothing End Sub

16. Вставка таблицы Excel в MS Word

Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:

Sub ExcelTableInWord() ‘Set reference to Microsoft Word Object library ‘Declare your variables Dim MyRange As Excel.Range Dim wd As Word.Application Dim wdDoc As Word.Document Dim WdRange As Word.Range ‘Copy the defined range Sheets(«Revenue Table»).Range(«B4:F10»).Cop ‘Open the target Word document Set wd = New Word.Application Set wdDoc = wd.Documents.Open _ (ThisWorkbook.Path & «\» & «PasteTable.docx») wd.Visible = True ‘Set focus on the target bookmark Set WdRange = wdDoc.Bookmarks(«DataTableHere»).Rangе ‘Delete the old table and paste new On Error Resume Next WdRange.Tables(1).Delete WdRange.Paste ‘paste in the table ‘Adjust column widths WdRange.Tables(1).Columns.SetWidth _ (MyRange.Width / MyRange.Columns.Count), wdAdjustSameWidth ‘Reinsert the bookmark wdDoc.Bookmarks.Add «DataTableHere», WdRange ‘Memory cleanup Set wd = Nothing Set wdDoc = Nothing Set WdRange = Nothing End Sub

17. Извлечение слов из текста

Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():

Function FindWord(Source As String, Position As Integer) As String On Error Resume Next FindWord = Split(WorksheetFunction.Trim(Source), » «)(Position — 1) On Error GoTo 0 End Function Function FindWordRev(Source As String, Position As Integer) As String Dim Arr() As String Arr = VBA.Split(WorksheetFunction.Trim(Source), » «) On Error Resume Next FindWordRev = Arr(UBound(Arr) — Position + 1) On Error GoTo 0 End Function

Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.

18. Защита данных в MS Excel

Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:

Sub ProtectSheets() ‘Declare your variables Dim ws As Worksheet ‘Start looping through all worksheets For Each ws In ActiveWorkbook.Worksheets ‘Protect and loop to next worksheet ws.Protect Password:=»1234″ Next ws End Sub

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

69 08.09.2016

При упоминании баз данных (БД) первым делом, конечно, в голову приходят всякие умные слова типа SQL, Oracle, 1С или хотя бы Access. Безусловно, это очень мощные (и недешевые в большинстве своем) программы, способные автоматизировать работу большой и сложной компании с кучей данных. Беда в том, что иногда такая мощь просто не нужна. Ваш бизнес может быть небольшим и с относительно несложными бизнес-процессами, но автоматизировать его тоже хочется. Причем именно для маленьких компаний это, зачастую, вопрос выживания.

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

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

Со всем этим вполне может справиться Microsoft Excel, если приложить немного усилий. Давайте попробуем это реализовать.

Шаг 1. Исходные данные в виде таблиц

Информацию о товарах, продажах и клиентах будем хранить в трех таблицах (на одном листе или на разных — все равно). Принципиально важно, превратить их в «умные таблицы» с автоподстройкой размеров, чтобы не думать об этом в будущем. Это делается с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table). На появившейся затем вкладке Конструктор (Design) присвоим таблицам наглядные имена в поле Имя таблицы для последующего использования:

Итого у нас должны получиться три «умных таблицы»:

Обратите внимание, что таблицы могут содержать дополнительные уточняющие данные. Так, например, наш Прайс содержит дополнительно информацию о категории (товарной группе, упаковке, весу и т.п.) каждого товара, а таблица Клиенты — город и регион (адрес, ИНН, банковские реквизиты и т.п.) каждого из них.

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

Шаг 2. Создаем форму для ввода данных

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

В ячейке B3 для получения обновляемой текущей даты-времени используем функцию ТДАТА (NOW). Если время не нужно, то вместо ТДАТА можно применить функцию СЕГОДНЯ (TODAY).

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

В ячейке B7 нам нужен выпадающий список с товарами из прайс-листа. Для этого можно использовать команду Данные — Проверка данных (Data — Validation), указать в качестве ограничения Список (List) и ввести затем в поле Источник (Source) ссылку на столбец Наименование из нашей умной таблицы Прайс:

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

=ДВССЫЛ(«Клиенты»)

Функция ДВССЫЛ (INDIRECT) нужна, в данном случае, потому что Excel, к сожалению, не понимает прямых ссылок на умные таблицы в поле Источник. Но та же ссылка «завернутая» в функцию ДВССЫЛ работает при этом «на ура» (подробнее об этом было в статье про создание выпадающих списков с наполнением).

Шаг 3. Добавляем макрос ввода продаж

После заполнения формы нужно введенные в нее данные добавить в конец таблицы Продажи. Сформируем при помощи простых ссылок строку для добавления прямо под формой:

Т.е. в ячейке A20 будет ссылка =B3, в ячейке B20 ссылка на =B7 и т.д.

Теперь добавим элементарный макрос в 2 строчки, который копирует созданную строку и добавляет ее к таблице Продажи. Для этого жмем сочетание Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer). Если эту вкладку не видно, то включите ее сначала в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon). В открывшемся окне редактора Visual Basic вставляем новый пустой модуль через меню Insert — Module и вводим туда код нашего макроса:

Sub Add_Sell() Worksheets(«Форма ввода»).Range(«A20:E20»).Copy ‘копируем строчку с данными из формы n = Worksheets(«Продажи»).Range(«A100000»).End(xlUp).Row ‘определяем номер последней строки в табл. Продажи Worksheets(«Продажи»).Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues ‘вставляем в следующую пустую строку Worksheets(«Форма ввода»).Range(«B5,B7,B9»).ClearContents ‘очищаем форму End Sub

Теперь можно добавить к нашей форме кнопку для запуска созданного макроса, используя выпадающий список Вставить на вкладке Разработчик (Developer — Insert — Button):

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

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

Шаг 4. Связываем таблицы

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

Для этого на вкладке Данные (Data) нажмите кнопку Отношения (Relations). В появившемся окне нажмите кнопку Создать (New) и выберите из выпадающих списков таблицы и названия столбцов, по которым они должны быть связаны:

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

Само-собой, аналогичным образом связываются и таблица Продажи с таблицей Клиенты по общему столбцу Клиент:

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

Шаг 5. Строим отчеты с помощью сводной

Теперь для анализа продаж и отслеживания динамики процесса, сформируем для примера какой-нибудь отчет с помощью сводной таблицы. Установите активную ячейку в таблицу Продажи и выберите на ленте вкладку Вставка — Сводная таблица (Insert — Pivot Table). В открывшемся окне Excel спросит нас про источник данных (т.е. таблицу Продажи) и место для выгрузки отчета (лучше на новый лист):

Жизненно важный момент состоит в том, что нужно обязательно включить флажок Добавить эти данные в модель данных (Add data to Data Model) в нижней части окна, чтобы Excel понял, что мы хотим строить отчет не только по текущей таблице, но и задействовать все связи.

После нажатия на ОК в правой половине окна появится панель Поля сводной таблицы, где нужно щелкнуть по ссылке Все, чтобы увидеть не только текущую, а сразу все «умные таблицы», которые есть в книге.А затем можно, как и в классической сводной таблице, просто перетащить мышью нужные нам поля из любых связанных таблиц в области Фильтра, Строк, Столбцов или Значений — и Excel моментально построит любой нужный нам отчет на листе:

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

Также, выделив любую ячейку в сводной и нажав кнопку Сводная диаграмма (Pivot Chart) на вкладке Анализ (Analysis) или Параметры (Options) можно быстро визуализировать посчитанные в ней результаты.

Шаг 6. Заполняем печатные формы

Еще одной типовой задачей любой БД является автоматическое заполнение различных печатных бланков и форм (накладные, счета, акты и т.п.). Про один из способов это сделать, я уже как-то писал. Здесь же реализуем, для примера, заполнение формы по номеру счета:

Предполагается, что в ячейку C2 пользователь будет вводить число (номер строки в таблице Продажи, по сути), а затем нужные нам данные подтягиваются с помощью уже знакомой функции ВПР (VLOOKUP) и функции ИНДЕКС (INDEX).

Ссылки по теме

  • Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений
  • Как заменить ВПР функциями ИНДЕКС и ПОИСКПОЗ
  • Автоматическое заполнение форм и бланков данными из таблицы
  • Создание отчетов с помощью сводных таблиц

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *