Учебное пособие для тех, кто устал от классических способов обучения, и хочет
попробовать что-то новое.
___________________
2019
ВВЕДЕНИЕ.
Microsoft Excel способен вместо нас выполнять большую часть рутинной
работы. Причем добиться этого можно совершенно без знания
программирования.
Как? - спросите Вы.
Автоматизация будет происходить при помощи обычных средств программы,
которыми Вы уже наверняка пользуетесь. Даже если об этих инструментах
Вы слышите впервые, научиться использовать их можно буквально за
несколько минут, в отличие от программирования, которое надо изучать
месяцами и годами.
Итак, что это за инструменты?
Во первых, это макросы.
… ну вот, а Вы говорили, что программирования не будет - скажете Вы. Я
тоже раньше думал, что макросы - это «высшая математика», связанная с
программированием. А все оказалось иначе …
Макрос - это обычный диктофон. Причем у него только две кнопки. «Начать
запись» и «остановить запись». Макрос способен записать все действия,
которые проделываете Вы, а затем в нужный момент повторить записанную
последовательность действий самостоятельно. Причем выполнять эти
действия макрос может неограниченное количество раз.
Например, из месяца в месяц мы заполняем однотипный отчет, а затем каждый
месяц копируем этот шаблон для следующего периода, перенося данные из одной
его части в другую и очищая ненужные данные. Так вот эту рутинную работу
переноса и очистки данных за Вас может сделать макрос.
Для этого мы должны лишь показать макросу что нужно делать. Просто
запускаем макрос и самостоятельно, ручками переносим данные в отчете.
Как все будет готово - останавливаем запись. Теперь в следующий месяц
ничего делать не нужно. Просто запускаем макрос и отчет готов к работе за
пару секунд.
Макрос тем и хорош, что он может повторить за Вами любую проделанную
работу. Причем Вы эту работу можете делать в течении 30 минут, а макросу
достаточно 2 секунд.
Все очень просто, так как у макроса, как я уже говорил всего две кнопки.
Следующий инструмент, который поможет нам бороться с рутинной
работой этой элементы управления. Они также очень просты и как я
обещал нисколько не связаны с программированием. Элементов управления
около 10, но реально используются при автоматизации 5-6 из них.
Что же могут элементы управления? Они, например, могут облегчить запуск
макроса.
В частности, есть элемент управления «кнопка» который можно добавить на
лист Excel. Этой кнопке можно присвоить один из макросов и вызывать его,
когда это необходимо.
Еще один элемент управления – это «поле со списком». При помощи его
можно выбирать один из вариантов.
Элементы управления очень просты в обращении. Все что нужно это
добавить элемент на рабочий лист и настроить на нем 1-2 параметра.
Например, в элементе «кнопка» всего один параметр – это выбор макроса. А в
элементе управления «поле со списком» таких параметров 2 – это
непосредственно сам список и ячейка, в которой будет отображаться номер
строки выбранной из списка.
Еще один элемент управления, который нам поможет в работе это
«счетчик». Он позволяет задать диапазон значений и увеличивать
(уменьшать) значение простым нажатием кнопки, без ввода самого
значения.
Элемент управления «переключатель» позволяет сделать выбор из
нескольких значений, а элемент «флажок» позволяет выбрать сразу
несколько значений.
И наконец, третья группа инструментов которая поможет нам в
автоматизации рутинной работы - это функции. Но не все подряд функции,
а именно те, которые могут работать в связке с элементами управления.
Функция номер один в этом ряду называется ИНДЕКС. Она довольно проста
в обращении и состоит из трех элементов.
1. Диапазон значений;
2. Номер строки;
3. Номер столбца
Т.е. функция выдает нам определенное число из диапазона находящееся в
определенном столбце и строке. Эта функция превосходно работает в
связке с элементом управления «поле со списком».
Например, Вам нужно подставить значения определенного столбца в какую-либо
форму. Т.е. столбец заранее известен. Его и вписываем в функцию. А номер
строки выбираем при помощи элемента управления «поле со списком».
Подведем небольшие итоги вводного урока по по автоматизации.
Во первых, мы должны научиться работать с макросами, освоив 2 кнопки
«rec» и «stop».
Во вторых, нам нужно уметь пользоваться 5-6 элементами управления и
уметь их настраивать при помощи всего одного (максимум 2-х параметров)
В третьих, должны уметь составлять 2-3 функции, которые работают в
связке с элементами управления.
Вот и все.
РАЗДЕЛ №1 МИНИМУМ ТЕОРИИ
Часть первая. МАКРОСЫ.
Итак, мы уже с Вами говорили, макросы - это простой диктофон без
каких-либо настроек.
Чтобы запустить макрос заходим на вкладку «Разработчик» и
выбираем команду «запись макроса».
Если У Вас на ленте Excel отсутствует вкладка «Разработчик», тогда
нажимаем на кнопку «Файл», затем на «Параметры» и переходим на
вкладку «Настроить ленту».
Здесь мы должны установить галочку рядом с пунктом «разработчик».
Итак, после выбора команды «запись макроса» появится вот такое
окно, в котором мы должны заполнить всего три параметра:
1. Название макроса. Оно может быть любым, но осознанным чтобы
Вы легко смогли его найти, когда макросов будет большое
количество;
2. Присвоить горячую кнопку. Т.е. в этом случае макрос можно
запустить не из вкладки разработчик, а при помощи сочетания
клавиш, например, CTRL + F. Это очень удобно.
3. Ну и третий параметр. Вам нужно выбрать место, где будут
хранится макросы. Обычно они сохраняются в той книге в которой
создаются. Но если вы делаете макрос, который впоследствии
должен работать в нескольких книгах, то сохраняйте его в личной
книге макросов.
Теперь нажимаем на кнопку «ОК» и запись макроса начата. Об этом
свидетельствует надпись «остановить макрос», которая появилась
вместо «запись макроса».
После того, как макрос записан нажимаем на «остановить макрос».
Визуально ничего не происходит, но макрос сохранен. Убедиться в
этом можно нажав на кнопку «макросы.
Появится окно «макрос», в которым вы увидите список макросов, в
том числе и созданный Вами только что.
Кстати, если Вы не задали горячую кнопку для макроса сделать это
можно в разделе «параметры»
Еще один очень важный ну и, пожалуй, единственный параметр,
который нужно учесть при записи макроса это выбор ссылок:
относительные или абсолютные. По умолчанию используются
абсолютные ссылки.
Т.е. макрос совершает за Вами действия в тех же самых ячейках где
делали вы, независимо от того где находится активная ячейка.
Но если Вы нажмете на «относительные ячейки», то макрос будет
выполнять действия относительно активной ячейки.
Чтобы понять это рассмотрим простой пример. Мы установили
активную ячейку на A1 и начали запись макроса. Во время записи мы
изменили форматирование ячейки B3. Теперь запустим макрос, при
этом активная ячейка находится на F1. Тем не менее макрос выполнил
форматирование ячейки В3, как и было при записи.
Но если мы при записи нажмем на «относительные ссылки» при
активной ячейке А1 и также займемся форматированием ячейки В3, то
при запуске макроса картина будет иная.
Теперь если активная ячейка будет на F1, то форматирование пройдет
в G3.
Часть вторая. Элементы управления.
На ленте переходим на вкладку «разработчик» и нажимаем на кнопку
«вставить» в группе команд «элементы управления».
Среди элементов управления выбираем самый первый – кнопка.
Теперь его нужно добавить на рабочую область. Для этого удерживая
левую кнопку мыши присваиваем фигуре нужный нам размер, после
чего отпускаем мышь.
Далее в появившемся окне выбираем макрос и нажимаем ОК.
Кнопка готова.
При помощи маркеров по ее краям мы можем изменить размеры
кнопки. А также мы можем изменить ее название. Для этого один раз
щелкаем по ней и изменяем название. Также можно придать
форматирование надписи. Изменить шрифт и цвет.
Для этого щелкните правой кнопкой мыши и в контекстном меню
выберите «формат».
После этого ткните по любой точке вне кнопки, и кнопка примет свой
облик (маркеры вокруг нее также исчезнут)
Если Вы захотите еще раз отредактировать кнопку, то жмите по ней
правой кнопкой мыши.
Следующий элемент называется «поле со списком». Добавляется он
таким же образом, как и кнопка. Но только после этого не появляется
автоматически окно с предложением прикрепить макрос.
После того, как мы разместили элемент на рабочей области,
переходим в «формат объекта». Для этого нажимаем по нему правой
кнопкой мыши.
Появится вот такое окно, в котором нужно настроить 2 параметра.
1. Выбрать диапазон, который будет выступать списком;
2. Выбрать ячейку, в которой будет отображаться номер выбранного
значения согласно списка.
Чтобы выбрать диапазон нужно нажать на вот эту кнопку и перейти к
выбору списка.
Далее удерживая левую кнопку мыши выделяем диапазон – он же будущий
список. Обращаю Ваше внимание что диапазон может состоять только из 1
столбца, а вот строк может быть неограниченное число.
В нашем примере списком будет диапазон N6:N10
После того, как диапазон отмечен вновь нажимаем на туже кнопку и
возвращаемся в формат объекта.
Таким же образом мы должны указать любую ячейку на листе, которая
станет связанной с элементом управления. У нас эту роль будет играть
ячейка N1.
Вот и все, инструмент настроен. Теперь посмотрим, как он работает.
В выпадающем списке выбираем «Сидоров». Он расположен третьем в
данном списке, поэтому в связанной ячейке отображается число три.
Если мы выберем «Мишин», то соответственно получим число 5. И так
далее.
Переходим к следующему элементу управления, который называется
«список»
Он отличается от инструмента «поле со списком» только дизайном. А
функционал и настройки те же.
Следующий элемент «счетчик». Выглядит он так.
Предположим, при помощи счетчика мы будем вводить какой-нибудь
параметр, который может быть от 10 до 100.
Поэтому в графе минимальное значение укажем 10.
Максимальное значение 100.
А шаг – 1.
А также уже знакомый нам параметр «связь с ячейкой».
Теперь нажатие на счетчик привет к увеличению или уменьшению числа на
1 единицу. Но как значение достигнет 100, счетчик остановится.
Аналогичным способом работает и полоса прокрутки. Она удобнее, когда
диапазон значений очень большой.
Следующие элементы — это «флажок» и «переключатель».
Оба элемента имеют практически одинаковые настройки.
Для того, чтобы их настроить нужно указать лишь ячейку, которая будет
связана с данным элементом управления.
Главное отличие между флажком и переключателем в том, что при выборе
флажка можно выбрать несколько значений, а при выборе переключателя
только одно.
Это отличие отражено и в настройках. Для переключателя нужно выбирать
связанную ячейку для каждого элемента. В ячейке будет отображаться
«Истина» или «Ложь».
А для переключателя достаточно выбрать связанную ячейку только для
одного из элементов. Остальные установятся автоматически. В связанной
ячейке будет отображаться номер переключателя.
Теперь предположим, что у нас несколько блоков переключателей.
Естественно каждый блок связан со своей ячейкой. Для того чтобы
разделить элементы по группам существует элемент, который называется
«группа». Выбираем его и устанавливаем поверх переключателей. Те из
них, которые окажутся внутри группы будут разделены от других
элементов.
Часть третья. Функции.
Самая полезная функция в автоматизации процессов это функция Индекс.
Особенно она хороша в связке с элементом управления «поле со списком».
Функция состоит из трех элементов:
1. Диапазон;
2. Номер строки;
3. Номер столбца.
Результатом функция выдает то значение, которое находится в указанной
строке и столбце диапазона.
Предположим есть
диапазон B4:E7 в котором 4 строки и 4 столбца.
Диапазон B4:E7 и будет первым элементом формулы.
Вторым элементом будет номер строки, например, 3-строка
А третий элемент - это номер столбца, например – 2-й столбец
Вся формула будет выглядеть так…
=ИНДЕКС(B4:E7;3;2)
Эта функция выдаст значение в строке № 3 столбца №2 указанного
диапазона, т.е. число 21.
Если функция будет выглядеть =ИНДЕКС(B4:E7;4;1) то ее результатом
будет число 84.
Функция ИНДЕКС может работать в идеальной связке с элементом «поле со
списком». Т.е. в поле со списком Вы выбираем какое-либо значение из
списка. Порядковый номер выбранного нами значения отображается в
связанной ячейке. Вот эту связанную ячейку мы и будем использовать в
виде аргумента «номер строки».
Рассмотрим пример. У нас есть вот такая таблица
На основе нее мы должны создать бланк личной карточки каждого
сотрудника.
В данной карте мы будем выбирать только имя сотрудника, а остальные
поля должны заполняться автоматически.
Для этого в поле ФИО добавляем элемент управления «поле со списком».
Теперь настроим его указав диапазон равный B5:E10, а зависимую ячейку
укажем B1
Теперь форма будет выглядеть вот так.
А при выборе значения в ячейке В1 публикуется порядковый номер
выбранной строки. Например, при выборе Сидоров, который в списке 4-й в
В1 появляется число 4.
Начинаем вводить формулу в ячейку «должность»
Теперь переходим к ячейке «стаж»
И последнее поле - «дата рождения»
Теперь в поле со списком выбираем «Мишин А». Так как товарищ Мишин 5й по списку - в связанной ячейке B1 загорается число 5. Это значит, что все
ячейки с формулами ИНДЕКС будут выдавать значения из 5-й строки.
В графе «должность» из 4-го столбца, в графе «стаж» из 3-го, а в графе
«дата рождения» из 2-го.
Теперь попробуем выбрать вариант «Петров С.» Так как это вторая строчка,
во всех ячейках с формулой ИНДЕКС отображаются данные из 2-й строки.
Следующая функция, которая придет нам на помощь в автоматизации это
ДВССЫЛ. Она поможет организовать нам связанные диапазоны. Т.е. будут 2
и более списков, которые будут формироваться в зависимости от того, что
выбрано в первом списке. Итак, начнем.
У нас имеется вот такой список.
Теперь создадим три именованных диапазона. В первый войдет диапазон
A2:A4, во второй B2:B4 в третий C2:С4. Названия диапазонов должны
совпадать с названиями стран.
В итоге должно получиться вот так:
Теперь переходим на вкладку «Данные» и выбираем «проверка данных»
В появившемся окне тип данных установим «список», а источник диапазон
с названиями диапазонов, в нашем случае это название стран.
Получился вот такой выпадающий список.
Теперь в ячейке F1 также запускаем проверку данных. Тип данных также
указываем «список», а вот в поле «источник» вбиваем формулу
=ДВССЫЛ(F1)
После этого при выборе в ячейке E1 параметра «Россия», в ячейке F1 будут
выдаваться только российские города.
РАЗДЕЛ №2 ПРАКТИКА
Теперь попробуем закрепить материал на практическом примере.
Компания «Светлый путь» имеет свой гараж и ежедневно
регистрирует в Excel все путевые листы. Для этого используется вот
такой шаблон.
Т.е. данные записываются в шаблон и сохраняются на одном листе, затем
копируется второй лист и так далее.
Это конечно очень неудобно, приходится создавать новые листы, книги,
очень трудно вести поиск, не говоря уже о регистрации путевых листов.
Сейчас мы с Вами совместно попробуем автоматизировать данный процесс.
Перед нами стоит задача создать регистрацию всех путевых листов с
возможностью вывода на экран или печати любого из них.
Для начала создаем второй лист под названием «регистрация».
Вводим на нем следующую форму:
Т.е. столбы этой формы повторяют все пункты путевого листа, которые
заполняются вручную.
Теперь переходим на страницу с путевым листом и добавим элемент «поле
со списком» в графу, где отображается номер листа. Для этого на вкладке
«разработчик» выбираем элемент управления «поле со списком».
Устанавливаем его на рабочую область.
Переходим в формат объекта.
И нажимаем на вот этот значок.
Теперь переходим на вкладку «регистрация» и выбираем столбец А
целиком.
В поле «связь с ячейкой» укажем ВВ1.
Все, поле со списком настроено и готово к работе. Очень скоро нам будет
достаточно выбрать только регистрационный номер путевого листа, и он
автоматически будет заполнен.
Ну а пока создадим еще один лист который назовем «авто» и поместим там
вот такую таблицу. Эта таблица нужна для того, чтобы облегчить ввод часто
используемых данных, таких как марка автомобиля и гос.номер.
В этой таблице выделяем столбец гос.номер и присваиваем выбранному
диапазону имя «гос_номер». Тоже самое делаем со столбцом марка и
присваиваем имя «марка»
Для того, чтобы создать диапазон с именем достаточно выбрать его, в поле
имя ввести название и нажать на Enter. Только учтите, что пробел в
названиях недопустим, поэтому используем нижнее подчеркивание.
Теперь переходим на вкладку «регистрация» и выделяем столбец В. Затем
идем на вкладку «Данные» – «Проверка данных».
В поле тип данных установим «список», а в поле источник укажем =марка.
Выражение «=марка» заменяет стандартное выделение диапазона с
перечнем марок автомобилей.
Теперь устанавливаем табличный курсор на любой ячейке столбца B и
увидим выпадающий список. Не правда ли, удобно!
Аналогичным способом указываем проверку данных для поля «гос.номер».
Теперь попробуем автоматизировать ввод фамилий сотрудников. Для них
сохраним отдельный лист, где запишем всех по профессиям.
Затем каждую группу сохраним в диапазоне, присвоив имя.
Вновь переходим на вкладку «регистрация». Выделяем столбец «механик»
и запускаем проверку данных. В качестве источника указываем =механики
– это будет служить ссылкой на только что созданный именованный
диапазон.
Аналогичным образом делаем со всеми столбцами, куда надо вводить
фамилии.
Теперь приступим к настройке главного шаблона, но перед этим создадим
именованный диапазон размером во весь наш бланк регистрации и назовем
его к примеру «путь».
Начинаем с первого значения «марка автомобиля». Начинаем вводить
формулу:
=ИНДЕКС(
Теперь нужно ввести диапазон. Для этого мы нажимаем на кнопку F3 и в
появившемся окошке выбираем диапазон «путь».
Продолжаем собирать формулу.
=ИНДЕКС(путь;BB1;2)
где BB1 ссылка на связанную ячейку, а 2 номер столбца в диапазоне с
регистрацией.
Переходим к следующему параметру, где надо автоматизировать ввод
номерных знаков.
Аналогично повторяем на всех полях формы. Если выберем путевой лист
№2, то форма автоматически заполнится нужными значениями.
А
Выбираем путевой лист №3 и получаем готовую форму.
Теперь настроим раздел «движение топлива»
В графе расход по норме нужно указать следующую формулу, так как норма
для всех авто одинаковая – 8 литров на 100 км. =(I32-I25)/100*8
В графе расход по факту простая формула разницы между показаниями
одометра.
=AA28-AA29
В графе экономия осталось вписать формулу
=ЕСЛИ(AA31AA30;AA30-AA31;"")
Таким образом будут отображаться результаты либо в графе «экономия»,
либо в «перерасход».
Теперь наша автоматизированная таблица полностью готова.
ЗАКЛЮЧЕНИЕ.
Наверняка, изучив пример в практической части нашего курса Вы
убедились, что автоматизация не только не страшна, но и очень интересна.
Вы получите колоссальное удовольствие от создания таких документов.
Попробуйте, и убедитесь в этом сами!
Подобным образом можно организовать регистрацию любых документов,
где выбрав лишь номер нужной записи можно вывести ее на экран.
Если у Вас возникнут трудности, то в течении месяца я готов совершенно
бесплатно провести для Вас любые консультации, а также помочь в
автоматизации одного документа. Для этого пишите на мою почту
progmasoft@mail.ru
