написание скриптов для эксель
Автоматизация рутины в Microsoft Excel при помощи VBA
В этом посте я расскажу, что такое VBA и как с ним работать в Microsoft Excel 2007/2010 (для более старых версий изменяется лишь интерфейс — код, скорее всего, будет таким же) для автоматизации различной рутины.
VBA (Visual Basic for Applications) — это упрощенная версия Visual Basic, встроенная в множество продуктов линейки Microsoft Office. Она позволяет писать программы прямо в файле конкретного документа. Вам не требуется устанавливать различные IDE — всё, включая отладчик, уже есть в Excel.
Еще при помощи Visual Studio Tools for Office можно писать макросы на C# и также встраивать их. Спасибо, FireStorm.
Сразу скажу — писать на других языках (C++/Delphi/PHP) также возможно, но требуется научится читать, изменять и писать файлы офиса — встраивать в документы не получится. А интерфейсы Microsoft работают через COM. Чтобы вы поняли весь ужас, вот Hello World с использованием COM.
Поэтому, увы, будем учить Visual Basic.
Чуть-чуть подготовки и постановка задачи
Итак, поехали. Открываем Excel.
Для начала давайте добавим в Ribbon панель «Разработчик». В ней находятся кнопки, текстовые поля и пр. элементы для конструирования форм.
Теперь давайте подумаем, на каком примере мы будем изучать VBA. Недавно мне потребовалось красиво оформить прайс-лист, выглядевший, как таблица. Идём в гугл, набираем «прайс-лист» и качаем любой, который оформлен примерно так (не сочтите за рекламу, пожалуйста):
То есть требуется, чтобы было как минимум две группы, по которым можно объединить товары (в нашем случае это будут Тип и Производитель — в таком порядке). Для того, чтобы предложенный мною алгоритм работал корректно, отсортируйте товары так, чтобы товары из одной группы стояли подряд (сначала по Типу, потом по Производителю).
Результат, которого хотим добиться, выглядит примерно так:
Разумеется, если смотреть прайс только на компьютере, то можно добавить фильтры и будет гораздо удобнее искать нужный товар. Однако мы хотим научится кодить и задача вполне подходящая, не так ли?
Кодим
Для начала требуется создать кнопку, при нажатии на которую будет вызываться наша програма. Кнопки находятся в панели «Разработчик» и появляются по кнопке «Вставить». Вам нужен компонент формы «Кнопка». Нажали, поставили на любое место в листе. Далее, если не появилось окно назначения макроса, надо нажать правой кнопкой и выбрать пункт «Назначить макрос». Назовём его FormatPrice. Важно, чтобы перед именем макроса ничего не было — иначе он создастся в отдельном модуле, а не в пространстве имен книги. В этому случае вам будет недоступно быстрое обращение к выделенному листу. Нажимаем кнопку «Новый».
И вот мы в среде разработки VB. Также её можно вызвать из контекстного меню командой «Исходный текст»/«View code».
Перед вами окно с заглушкой процедуры. Можете его развернуть. Код должен выглядеть примерно так:
Напишем Hello World:
Sub FormatPrice()
MsgBox «Hello World!»
End Sub
И запустим либо щелкнув по кнопке (предварительно сняв с неё выделение), либо клавишей F5 прямо из редактора.
Тут, пожалуй, следует отвлечься на небольшой ликбез по поводу синтаксиса VB. Кто его знает — может смело пропустить этот раздел до конца. Основное отличие Visual Basic от Pascal/C/Java в том, что команды разделяются не ;, а переносом строки или двоеточием (:), если очень хочется написать несколько команд в одну строку. Чтобы понять основные правила синтаксиса, приведу абстрактный код.
Примеры синтаксиса
Dim res As sTRING ‘ Регистр в VB не важен. Впрочем, редактор Вас поправит
Dim i As Integer
‘ Цикл всегда состоит из нескольких строк
For i = 1 To 10
res = res + CStr(i) ‘ Конвертация чего угодно в String
If i = 5 Then Exit For
Next i
Dim x As Double
x = Val( «1.234» ) ‘ Парсинг чисел
x = x + 10
MsgBox x
On Error GoTo Err ‘ При ошибке перейти к метке Err
x = 5 / 0
MsgBox «OK!»
GoTo ne
ne:
On Error GoTo 0 ‘ Отключаем обработку ошибок
‘ Циклы бывает, какие захотите
Do While True
Exit Do
Loop ‘While True
Do ‘Until False
Exit Do
Loop Until False
‘ А вот при вызове функций, от которых хотим получить значение, скобки нужны.
‘ Val также умеет возвращать Integer
Select Case LengthSqr(Len( «abc» ), Val( «4» ))
Case 24
MsgBox «0»
Case 25
MsgBox «1»
Case 26
MsgBox «2»
End Select
Грабли-1. При копировании кода из IDE (в английском Excel) есь текст конвертируется в 1252 Latin-1. Поэтому, если хотите сохранить русские комментарии — надо сохранить крокозябры как Latin-1, а потом открыть в 1251.
Грабли-2. Т.к. VB позволяет использовать необъявленные переменные, я всегда в начале кода (перед всеми процедурами) ставлю строчку Option Explicit. Эта директива запрещает интерпретатору заводить переменные самостоятельно.
Грабли-3. Глобальные переменные можно объявлять только до первой функции/процедуры. Локальные — в любом месте процедуры/функции.
Еще немного дополнительных функций, которые могут пригодится: InPos, Mid, Trim, LBound, UBound. Также ответы на все вопросы по поводу работы функций/их параметров можно получить в MSDN.
Надеюсь, что этого Вам хватит, чтобы не пугаться кода и самостоятельно написать какое-нибудь домашнее задание по информатике. По ходу поста я буду ненавязчиво знакомить Вас с новыми конструкциями.
Кодим много и под Excel
В этой части мы уже начнём кодить нечто, что умеет работать с нашими листами в Excel. Для начала создадим отдельный лист с именем result (лист с данными назовём data). Теперь, наверное, нужно этот лист очистить от того, что на нём есть. Также мы «выделим» лист с данными, чтобы каждый раз не писать длинное обращение к массиву с листами.
Sub FormatPrice()
Sheets( «result» ).Cells.Clear
Sheets( «data» ).Activate
End Sub
Работа с диапазонами ячеек
Вся работа в Excel VBA производится с диапазонами ячеек. Они создаются функцией Range и возвращают объект типа Range. У него есть всё необходимое для работы с данными и/или оформлением. Кстати сказать, свойство Cells листа — это тоже Range.
Примеры работы с Range
Sheets( «result» ).Activate
Dim r As Range
Set r = Range( «A1» )
r.Value = «123»
Set r = Range( «A3,A5» )
r.Font.Color = vbRed
r.Value = «456»
Set r = Range( «A6:A7» )
r.Value = «=A1+A3»
Теперь давайте поймем алгоритм работы нашего кода. Итак, у каждой строчки листа data, начиная со второй, есть некоторые данные, которые нас не интересуют (ID, название и цена) и есть две вложенные группы, к которым она принадлежит (тип и производитель). Более того, эти строки отсортированы. Пока мы забудем про пропуски перед началом новой группы — так будет проще. Я предлагаю такой алгоритм:
Для упрощения работы рекомендую определить следующие функции-сокращения:
Function GetCol(Col As Integer ) As String
GetCol = Chr(Asc( «A» ) + Col)
End Function
Далее определим глобальную переменную «текущая строчка»: Dim CurRow As Integer. В начале процедуры её следует сделать равной единице. Еще нам потребуется переменная-«текущая строка в data», массив с именами групп текущей предыдущей строк. Потом можно написать цикл «пока первая ячейка в строке непуста».
Глобальные переменные
Option Explicit ‘ про эту строчку я уже рассказывал
Dim CurRow As Integer
Const GroupsCount As Integer = 2
Const DataCount As Integer = 3
FormatPrice
Sub FormatPrice()
Dim I As Integer ‘ строка в data
CurRow = 1
Dim Groups(1 To GroupsCount) As String
Dim PrGroups(1 To GroupsCount) As String
Теперь надо заполнить массив Groups:
На месте многоточия
И создать заголовки:
На месте многоточия в предыдущем куске
For I2 = 1 To GroupsCount
If Groups(I2) <> PrGroups(I2) Then
Dim I3 As Integer
For I3 = I2 To GroupsCount
AddHeader I3, Groups(I3)
Next I3
Exit For
End If
Next I2
Не забудем про процедуру AddHeader:
Перед FormatPrice
Теперь надо перенести всякую информацию в result
Подогнать столбцы по ширине и выбрать лист result для показа результата
После цикла в конце FormatPrice
Sheets( «Result» ).Activate
Columns.AutoFit
Всё. Можно любоваться первой версией.
Некрасиво, но похоже. Давайте разбираться с форматированием. Сначала изменим процедуру AddHeader:
Осталось только сделать границы. Тут уже нам требуется работать со всеми объединёнными ячейками, иначе бордюр будет только у одной:
Поэтому чуть-чуть меняем код с добавлением стиля границ:
Select Case Ty
Case 1 ‘ Тип
.Font.Bold = True
.Font.Size = 16
.Borders(xlTop).Weight = xlThick
Case 2 ‘ Производитель
.Font.Size = 12
.Borders(xlTop).Weight = xlMedium
End Select
.Borders(xlBottom).Weight = xlMedium ‘ По убыванию: xlThick, xlMedium, xlThin, xlHairline
End With
CurRow = CurRow + 1
End Sub
Осталось лишь добится пропусков перед началом новой группы. Это легко:
В начале FormatPrice
Dim I As Integer ‘ строка в data
CurRow = 0 ‘ чтобы не было пропуска в самом начале
Dim Groups(1 To GroupsCount) As String
В цикле расстановки заголовков
If Groups(I2) <> PrGroups(I2) Then
CurRow = CurRow + 1
Dim I3 As Integer
В точности то, что и хотели.
Надеюсь, что эта статья помогла вам немного освоится с программированием для Excel на VBA. Домашнее задание — добавить заголовки «ID, Название, Цена» в результат. Подсказка: CurRow = 0 CurRow = 1.
Файл можно скачать тут (min.us) или тут (Dropbox). Не забудьте разрешить исполнение макросов. Если кто-нибудь подскажет человеческих файлохостинг, залью туда.
Спасибо за внимание.
Буду рад конструктивной критике в комментариях.
UPD: Перезалил пример на Dropbox и min.us.
UPD2: На самом деле, при вызове процедуры с одним параметром скобки можно поставить. Либо использовать конструкцию Call Foo(«bar», 1, 2, 3) — тут скобки нужны постоянно.
Атанас Йонков Блоггер, Веб-разработчик
yonkov.atanas@gmail.com
Атанас Йонков Блоггер, Веб-разработчик Вам не нужен опыт программирования, чтобы воспользоваться информаций из этой статьи, но вы должны иметь базовые знания Excel. Если вы еще учитесь работать с Excel, я бы рекомендовал Вам прочитать статью 20 формул Excel, которые вам нeобходимо выучить сейчас, чтобы узнать больше о функциональных возможностях Excel.
Я подготовил для вас несколько самых полезных примеров VBA Excel с большой функциональностью, которую вы сможете использовать для оптимизации своей работы. Чтобы их использовать, вам необходимо записать их в файл. Следующий параграф посвящен установке макроса Excel. Пропустите эту часть, если вы уже знакомы с этим.
Table of Contents
Как включить макросы в Excel
1. Копирование данных из одного файла в другой.
Очень полезный макрос, поскольку он показывает, как скопировать ряд данных изнутри vba и как создать и назвать новую книгу. Вы можете изменить этот макрос в соответствии с вашими собственными требованиями:
2. Отображение скрытых строк
Иногда большие файлы Excel можно содержать скрытые строки для большей ясности И для лучшего удобства пользователей. Вот один макрос, который отобразит все строки из активной рабочей таблицы:
3. Удаление пустых строк и столбов
4. Нахождение пустых ячеек
13. Создание сводной таблицы
14. Отправка активного файла по электронной почте
Мой любимый код VBA. Он позволяет вам прикреплять и отправлять файл, с которым вы работаете, с предопределенным адресом электронной почты, заголовком сообщения и телом сообщения! Сначала Вам нужно сделать референцию в Excel на Microsoft Outlook (в редакторе Excel VBA, нажмите tools => references и выберите Microsoft Outlook).
15. Вставка всех графиков Excel в презентацию PowerPoint
Очень удобный макрос, который позволяет вам добавлять все ваши графики Excel в презентацию Powerpoint одним щелчком мыши:
16. Вставка таблицы Excel в MS Word
Таблицы Excel обычно помещаются внутри текстовых документов. Вот один автоматический способ экспорта таблицы Excel в MS Word:
17. Извлечение слов из текста
Мы можем использовать формулы, если хотим извлечь определенное количество символов. Но что, если мы хотим извлечь только одно слово из предложения или диапазон слов в ячейке? Для этого мы можем сами создать функцию Excel с помощью VBA. Это одна из самых удобных функций VBA, поскольку она позволяет создавать собственные формулы, которые отсутствуют в MS Excel. Давайте продолжим и создадим две функции: findword() и findwordrev():
Отлично, мы уже создали две новые функции в Excel! Теперь попробуйте использовать их в Excel. Функция = FindWordRev (A1,1) берет последнее слово из ячейки A1. Функция = FindWord (A1,3) берет третье слово из ячейки A1 и т. Д.
18. Защита данных в MS Excel
Иногда мы хотим защитить данных нашего файла, чтобы только мы могли его изменять. Вот как это сделать с VBA:
Поздравления! Поскольку вы все еще читаете это, вы действительно заинтересованы в изучении VBA. Как вы уже сами видели, язык программирования VBA чрезвычайно полезен и может сэкономить нам много времени. Надеюсь, вы нашли эту информацию полезной и использовали ее, чтобы стать мастером MS Excel, VBA и компьютерных наук в целом.
VBA Excel. Начинаем программировать с нуля
Первое знакомство с редактором VBA Excel, создание процедур (подпрограмм) и написание простейшего кода, работающего с переменными и ячейками рабочего листа.
Начинаем программировать с нуля
Часть 1. Первая программа
[Часть 1] [Часть 2] [Часть 3] [Часть 4]
Знакомство с редактором VBA
В левой части редактора VBA расположен проводник проекта, в котором отображены все открытые книги Excel. Верхней строки, как на изображении, у вас скорее всего не будет, так как это – личная книга макросов. Справа расположен модуль, в который записываются процедуры (подпрограммы) с кодом VBA. На изображении открыт модуль листа, мы же далее создадим стандартный программный модуль.
После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.
Первая программа на VBA Excel
Добавляем на стандартный модуль шаблон процедуры – строки ее начала и завершения, между которыми мы и будем писать свою первую программу (процедуру, подпрограмму).
В результате откроется окно добавления шаблона процедуры (Sub).
Имя процедуры может быть написано как на латинице, так и на кириллице, может содержать цифры и знак подчеркивания. Оно обязательно должно начинаться с буквы и не содержать пробелы, вместо которых следует использовать знак подчеркивания.
Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».
Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!
Работа с переменными
Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).
Пример 2
Присвоение переменным числовых значений:
Основные сценарии Office скриптов в Excel в Интернете
Следующие примеры — это простые сценарии, которые можно попробовать в собственных книгах. Чтобы использовать их в Excel в Интернете:
Основы скрипта
В этих примерах демонстрируются основные строительные блоки для Office скриптов. Расширите эти скрипты, чтобы расширить решение и решить распространенные проблемы.
Чтение и журнал одной ячейки
В этом примере считывать значение A1 и печатать его на консоли.
Чтение активной ячейки
В этом скрипте региструется значение текущей активной ячейки. Если выбрано несколько ячеек, будет зарегистрирована верхняя левая ячейка.
Изменение соседней ячейки
Этот скрипт получает соседние ячейки с использованием относительных ссылок. Обратите внимание, что если активная ячейка находится в верхнем ряду, часть скрипта не работает, так как она ссылается на ячейку выше выбранной в настоящее время.
Изменение всех смежных ячеек
Этот скрипт копирует форматирование в активной ячейке в соседние ячейки. Обратите внимание, что этот скрипт работает только тогда, когда активная ячейка не на краю таблицы.
Изменение каждой отдельной ячейки в диапазоне
Этот скрипт цикличен по диапазону выбора в настоящее время. Он очищает текущее форматирование и задает цвет заполнения в каждой ячейке случайным цветом.
Получить группы ячеек на основе специальных критериев
Этот скрипт получает все пустые ячейки в используемом диапазоне текущего листа. Затем он выделяет все эти ячейки с желтым фоном.
Коллекции
Эти примеры работают с коллекциями объектов в книге.
Итерации над коллекциями
Этот скрипт получает и записывает имена всех таблиц в книге. Он также задает цвета вкладки случайным цветом.
Запрос и удаление из коллекции
Этот скрипт создает новую таблицу. Он проверяет существующую копию листа и удаляет его перед созданием нового листа.
В примерах этого раздела покажите, как использовать объект даты JavaScript.
Следующий пример получает текущую дату и время, а затем записывает эти значения в две ячейки в активном таблице.
В следующем примере считываю дату, храняную в Excel, и переводим ее на объект JavaScript Date. В качестве ввода для даты JavaScript используется числовый серийный номер даты.
Отображение данных
В этих примерах показано, как работать с данными таблицы и предоставить пользователям лучшее представление или организацию.
Применение условного форматирования
В этом примере применяется условное форматирование к используемой в настоящее время линейке в таблице. Условное форматирование — это зеленое заполнение для 10% значений.
Создание отсортировать таблицу
В этом примере создается таблица из используемого диапазона текущего таблицы, а затем сортируются на основе первого столбца.
Журнал значений «Grand Total» из pivotTable
В этом примере находится первый pivotTable в книге и регистрируемые значения в ячейках «Grand Total» (как выделено зеленым цветом на рисунке ниже).
Создание выпадаемого списка с помощью проверки данных
В этом скрипте создается список выбора для ячейки. Он использует существующие значения выбранного диапазона в качестве выбора для списка.
Формулы
В этих примерах Excel формулы и покажите, как работать с ними в сценариях.
Единая формула
Этот скрипт задает формулу ячейки, а затем отображает, как Excel хранит формулу и значение ячейки отдельно.
Обработка #SPILL! ошибки, возвращаемой из формулы
Этот скрипт передает диапазон «A1:D2» на «A4:B7» с помощью функции TRANSPOSE. Если переливание приводит к ошибке, он очищает целевой диапазон и снова #SPILL применяет формулу.
Предложить новые примеры
Мы приветствуем предложения по новым образцам. Если существует распространенный сценарий, который поможет другим разработчикам сценариев, сообщите нам об этом в разделе отзывов в нижней части страницы.
Как сделать скрипт в excel?
В процессе работы неоднократно приходиться сталкиваться с табличными процессорами типа Excel. И хотя это не совсем САПР, но информацию в рамках BIM контекста хочешь не хочешь, а приходится обрабатывать, либо с помощью Базы Данных, либо в тех самых экселевских табличках. А лучше и так и эдак.
Естественно, как и во всем, что связанно с компьютером эту деятельность тоже можно изрядно упростить с помощью автоматизации. С появлением таких вещей как Pivot Tables и Power Queries — так и вовсе становится не понятно как работали с информацией до этого. Но тут мы поговорим о старых добрых ламповых макросах на VBA.
Excel VBA Macro script
Макросы в экселе пишут на VBA (Visual Basic Advance) — слегка переработанный под нужды офисного пакета старый добрый Бейсик, со всеми своими достоинствами и недостатоками. Сами программы по умолчанию являются частью эксель файла. И убедится в этом можно с помощью соответствующей консоли (Microsoft Visual Basic for Application)
Добраться до них можно в любое время нажав сочетание клавишь Alt+F11
Но сначала надо включить вкладку Developers в стандартной ленте экселя.
Путем этих не хитрых манипуляций мы получили доступ к панели разработчика в Excel — не будем особо заострять на ней внимание. Нам там понадобится буквально несколько кнопок
— Visual Basic — собственно уже обусжденная выше консоль для работы со скриптами.
— Macos — список доступных макросов для использования.
— Record Macro — эта кнопка позволяет включить запись ваших действий с табличным редактором.
Ну и дальше порядок работы довольно простой — запускаете запись макроса, производите необходимые манипуляции с данными, останавливаете запись (той же кнопкой что и включили), идете в редактор, правите если необходимо. Потом с помощью списка макросов вызываете вновь созданный макрос в любое удобное время.
Однако есть нюанс. Созданный макроскрипт будет по умолчанию доступен только в одном эксель файле. Скопировать его в другой — конечно можно, но довольно заморочно. Есть ли выход из этой ситуации? Конечно!
Создаем собственную библиотеку Excel макросов — Personal.xlsb
Для этого нам нужно создать файл Personal.xlsb. Создается он один раз, и потом автоматически подгружается Экселем, благодаря чему сохраненные в нем Скрипты и функции становятся доступны во всех ваших файлах. Другой плюс — теперь они не сохраняются в самих файлах, и соответственно можно без опаски передавать результаты за пределы компании.
Собственно смотрим на картинку:
1. Необходимо включить макрос на запись.
2. В появившемся окне в выпадающем списке выбрать пункт — Personal Macro Workbook. Если такого файла не существует — он будет создан автоматические (то что нам и нужно)
3. Запускаем скрипт на запись
4. И останавливаем её.
Готово. Теперь у нас есть файл в который наши модули и функции можно закинуть единожды, а доступны они будут сразу во всех эксель файлах. К тому же так они не утекут за пределы компании, вместе с файлами.
Небольшой топик-шпаргалка для быстрого написания скриптов для автоматической обработки документов ms office’а. А так же для помощи в преодолении синдрома чистого листа.
Как правильно заметили в недавнем топике, сама работа скрипта может быть и не быстрой, но чаще важнее сам результат, да и объёмы далеко не всегда такие большие. Зато Automation позволяет написать скрипт практически на любом языке. Здесь я выдам заготовки для JavaScript и IronPython, но, надеюсь, в комментариях найдётся место и для других языков (например, на PowerShell).
Чуть-чуть лирики
Для работы через automation нужен следующий минимум:
Ещё раз повторюсь. Цель — быстро написать скрипт без сложных требований (производительность, встраивание, работа без ms office и т.п.).
Собственно, код
Имхо, самый разумный вариант — это JScript или VBScript (точнее Windows script host), т.к. интерпретатор уже идёт с операционной системой, и можно раздавать скрипт друзьям/коллегам, не требуя от них никаких дополнительных действий — перетащил xls(x)-файл на скрипт в explorer’е и работа пошла:
Что такое VBA
Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.
К его достоинствам специалисты относят сравнительную легкость освоения. Как показывает практика, азами VBA могут овладеть даже пользователи, которые не имеют навыков профессионального программирования. К особенностям VBA относится выполнение скрипта в среде офисных приложений.
Недостатком программы являются проблемы, связанные с совместимостью различных версий. Они обусловлены тем, что код программы VBA обращается к функциональным возможностям, которые присутствуют в новой версии продукта, но отсутствуют в старой. Также к минусам относят и чрезмерно высокую открытость кода для изменения посторонним лицом. Тем не менее Microsoft Office, а также IBM Lotus Symphony позволяют пользователю применять шифрование начального кода и установку пароля для его просмотра.
Объекты, коллекции, свойства и методы
Именно с этими понятиями нужно разобраться тем, кто собирается работать в среде VBA. Прежде всего необходимо понять, что такое объект. В Excel в этом качестве выступают лист, книга, ячейка и диапазон. Данные объекты обладают специальной иерархией, т.е. подчиняются друг другу.
Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.
Что касается понятия «коллекция», то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.
Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.
Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в «Эксель» используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами (1,1) т.е. A1.
Вместе с ней нередко используется Selection.ClearContents. Ее выполнение означает очистку содержимого выбранной ячейки.
Как начать
Прежде всего требуется создать файл и сохранить его, присвоив имя и выбрав тип «Книга Excel с поддержкой макросов».
Затем необходимо перейти в приложение VB, для чего достаточно воспользоваться комбинацией клавиш «Alt» и «F11». Далее:
Он выглядит следующим образом:
Обратите внимание, что строка «’Наш код» будет выделена другим цветом (зеленым). Причина в апострофе, поставленном в начале строки, который обозначает, что далее следует комментарий.
Теперь вы можете написать любой код и создать для себя новый инструмент в VBA Excel (примеры программ см. далее). Конечно, тем, кто знаком с азами Visual Basic, будет намного проще. Однако даже те, кто их не имеет, при желании смогут освоиться достаточно быстро.
Макросы в Excel
За таким названием скрываются программы, написанные на языке Visual Basic for Application. Таким образом, программирование в Excel — это создание макросов с нужным кодом. Благодаря этой возможности табличный процессор Microsoft саморазвивается, подстраиваясь под требования конкретного пользователя. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению конкретных примеров программ VBA Excel. Лучше всего начать с самых элементарных кодов.
Пример 1
Задача: написать программу, которая будет копировать значение содержимое одной ячейки и затем записывать в другую.
Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.
Теперь, когда уже запущена запись макроса, производят копирование содержимого какой-либо ячейки в другую. Возвращаются на исходную пиктограмму. Нажимают на «Запись макроса». Это действие означает завершение программки.
В результате происходит действие, которое было осуществлено в процессе записи макроса.
Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.
Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.
Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.
Пример 2
Циклы VBA помогают создавать различные макросы в Excel.
Циклы VBA помогают создавать различные макросы. Предположим, что имеется функция y=x + x2 + 3×3 – cos(x). Требуется создать макрос для получения ее графика. Сделать это можно только, используя циклы VBA.
За начальное и конечное значение аргумента функции берут x1=0 и x2=10. Кроме того, необходимо ввести константу — значение для шага изменения аргумента и начальное значение для счетчика.
Все примеры макросов VBA Excel создаются по той же процедуре, которая представлена выше. В данном конкретном случае код выглядит, как:
Do While x1 Пример 3
Для реализации циклов в VBA Excel 2010, как и в других версиях, наряду с уже приведенной конструкцией Do While используется For.
Рассмотрим программу, которая создаст столбец. В каждой его ячейке будут записаны квадраты номера соответствующей строки. Использование конструкции For позволит записать ее очень коротко, без использования счетчика.
Сначала нужно создать макрос, как описано выше. Далее записываем сам код. Считаем, что нас интересуют значения для 10 ячеек. Код выглядит следующим образом.
For i = 1 to 10 Next
Команда переводится на «человеческий» язык, как «Повторять от 1 до 10 с шагом один».
Если ставится задача получить столбец с квадратами, например, всех нечетных чисел из диапазона от 1 до 11, то пишем:
For i = 1 to 10 step 1 Next.
Здесь step — шаг. В данном случае он равен двум. По умолчанию отсутствие этого слова в цикле означает, что шаг единичный.
Полученные результаты нужно сохранять в ячейки с номером (i,1). Тогда при каждом запуске цикла с увеличением i на величину шага автоматически будет расти и номер у строки. Таким образом, произойдет оптимизация кода.
В целом код будет выглядеть, как:
For i = 1 To 10 Step 1 (можно записать просто For i = 1 To 10)
Cells(i, 1).Value = i ^ 2 (т.е. в ячейку (i,1) записывается значение квадрата i)
Next (в некотором смысле играет роль счетчика и означает еще один запуск цикла)
Если все сделано правильно, в том числе запись и запуск макроса (см. инструкцию выше), то при его вызове каждый раз будет получаться столбец заданного размера (в данном случае состоящий из 10 ячеек).
Пример 4
В повседневной жизни сплошь и рядом возникает необходимость принять то или иное решение в зависимости от какого-то условия. Не обойтись без них и в VBA Excel. Примеры программ, где дальнейший ход выполнения алгоритма выбирается, а не предопределен изначально, чаще всего используют конструкцию If …Then (для сложных случаев) If …Then …END If.
Рассмотрим конкретный случай. Предположим, необходимо создать макрос для «Эксель», чтобы в ячейку с координатами (1,1) было записано:
1, если аргумент положительный;
0, если аргумент нулевой;
-1, если аргумент отрицательный.
Создание такого макроса для «Эксель» начинается стандартным способом, через использование «горячих» клавиш Alt и F11. Далее записывается следующий код:
x= Cells(1, 1).Value (эта команда присваивает x значение содержимого ячейки с координатами (1, 1))
If x>0 Then Cells(1, 1).Value = 1
If x=0 Then Cells(1, 1).Value = 0
09 Мар 3 способа написать и оформить скрипт продаж
Давайте разберемся, как обычно создаются скрипты продаж?
В своей практике я обычно сталкиваюсь с 3 способами написания и оформления скриптов продаж. Давайте по порядку рассмотрим эти способы. Напишите в комментариях, используете ли Вы скрипты продаж и, если ДА, то каким именно способом Вы это делаете.
Первый способ. Скрипт «от руки».
Вы можете написать скрипт продаж (или хотя бы примерный сценарий общения с клиентом) на листочке, положить перед собой и использовать его как шпаргалку во время разговора с клиентом.
Это может выглядеть так:
Никаких затрат с Вашей стороны.
Но и эффективность таких скриптов оставляет желать лучшего:
Этот способ может быть уместен, когда Вы делаете разовые звонки каким-то клиентам и, чтобы ничего не упустить, тезисно выписываете содержание разговора.
К сожалению, такой способ часто используется во многих компаниях малого и среднего бизнеса. Обычно это бывает, когда система телефонных продаж не настроена и менеджеры делают звонки как умеют и используют те инструменты, которые знают :-).
Второй способ. Скрипт продаж в офисных программах (Excel, Word и тд)
Более продвинутый и системный подход к внедрению скриптов продаж — это составление и использование скриптов в офисных программах, таких как Excel или Word (или аналогичных).
Вы можете прописать все возможные варианты возражений и отговорок клиентов, свою аргументацию и способы мотивации клиента принимать решение «здесь и сейчас» в одном файле, а потом с помощью создания гиперссылок автоматизировать скрипт.
В этом случае менеджеру будет удобно им пользоваться: услышал возражение — нажал на ссылку в скрипте — на экране появился ответ на это возражение.
Именно такой принцип создания скриптов я использовал в курсе Конструктор скриптов продаж. Я разобрал не только фразы и речевые обороты, которые повышают конверсию звонков, но и показал, как автоматизировать скрипт в обычном экселе.
Плюсы этого подхода в том, что Вам не нужно платить ежемесячную абонентскую плату за пользование сервисами. Скорее всего, у Вас и так есть Excel или Word — а это значит, что Вы можете один раз составить скрипт и начать использовать его всем отделом продаж.
Минусы — нет интеграции с CRM системами и телефонией.
Третий способ. Специальные сервисы «Конструкторы скриптов продаж»
В последнее время популярность набирают специальные сервисы, которые позволяют конструировать скрипты продаж. Обычно это происходит через веб-интерфейс.
Вы заходите на сайт сервиса — регистрируетесь — начинаете рисовать свой скрипт продаж.
Это может выглядеть вот так:
Возможно, картинка выглядит сложно, но на самом деле процесс создания скриптов продаж достаточно комфортен и позволяет даже новичку легко сориентироваться.
При использовании скрипта экран выглядит гораздо проще: есть фраза менеджера, есть варианты ответов клиентов. Менеджер слушает и нажимает на нужную кнопку.
Я протестировал некоторые сервисы конструкторы скриптов продаж и сделал для себя определенные выводы. В следующей статье я поделюсь с Вами и сделаю обзор некоторых сервисов по созданию скриптов, которые мне больше понравились.
Преимуществами таких сервисов, на мой взгляд, являются
Как Вы понимаете, за такие удобства нужно платить. Обычно взимается ежемесячная абонентская плата за каждого менеджера, который пользуется системой.
В следующих статьях поподробнее расскажу о конструкторах скриптов продаж.
Напишите в комментариях, какими способами пользуетесь Вы в своей работе.
С уважением, Олег (дружить в ВК, инстаграм)