Формирование резюме сотрудника

Формирование резюме сотрудника VBA ExcelФормирование резюме сотрудника в VBA Excel.

В статье мы создали базу для внесения и хранения данных о сотрудниках.

В этой поговорим о том, как сформировать резюме по какому-либо одному сотруднику из этой базы.

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

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

Но, обо-всем по порядку.

Создание шаблона резюме

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

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

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

В резюме предусмотрим два вычисляемых поля – расчет возраста и расчет стажа работы в компании. В первом случае рассчитаем просто количество полных лет, используя функцию РАЗНДАТ. Формула будет выглядеть так: = РАЗНДАТ(ДР;СЕГОДНЯ();»Y»).

Второй вариант расчета несколько усложним. Нам понадобиться вычислить не только количество лет, но и количество месяцев. Формула здесь будет выглядеть следующим образом: =РАЗНДАТ(ДПрием;СЕГОДНЯ();»y»)&» лет «&РАЗНДАТ(ДПрием;СЕГОДНЯ();»ym»)&» мес.»

Разработка алгоритма

Итак, заложим следующую логику переноса данных:

  1. В таблице учета персонала добавим колонку, каждая ячейка которой будет запускать макрос переноса данных о сотруднике, к строке которого относится эта ячейка.
  2. При клике на ячейке должен открыться шаблон резюме с предложением сохранить резюме под новым именем.
  3. Одиночные данные будут переносится в файл резюме прямым переносом – каждой поименованной ячейке резюме должно быть присвоено соответствующее значение из таблицы.
  4. Данные об образовании и прежних местах работы будут переноситься на основании кода, которому соответствует порядковый номер строки сотрудника (как вы помните, этот код присваивался в строках таблиц об образовании и стаже. Таким образом мы обеспечивали связь между разными таблицами). Для переноса данных будем использовать цикл по счетчику, где конечное значение счетчика будет равно количеству строк в основной таблице (или, другими словами, количеству сотрудников).

Начнем программирование.

Разработка кода

Первым делом создадим переменные для наименований файлов. Они нам пригодятся, когда нужно будет активировать тот или иной файл. Для файла резюме я назвал переменную fil, а для файла со списком сотрудников — bas

Затем нам понадобятся переменная для хранения кода (порядкового номера) сотрудника, и номера строки, в которой расположены данные о сотруднике в основной таблице. Я их назвал kod и Arow.

Для хранения информации о количестве строк в таблицах с данными об образовании и стаже внесем переменные x и y.

И, наконец, нам понадобиться два счетчика. Дадим им имена i и ii.

Первый блок кода посвящен присвоению значений переменным и созданию файла резюме:

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

Затем идет копирование одиночных данных. Здесь все просто – поименованным ячейкам файла резюме присваиваем соответствующие значения из списка сотрудников. Обратите внимание, используем два вида обозначения ячеек – Cells и Range. Мы еще поговорим на эту тему в одной из следующих статей.

И, наконец, два блока копирования данных об образовании и стаже.

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

Затем необходимо добавить соответствующее количество строк. Это сделаем с помощью цикла:

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

Последним штрихом осуществим форматирование – объединим необходимые ячейки и нарисуем их границы:

Код по копированию данных о местах работы незначительно отличается от предыдущего:

Теперь нам необходимо обеспечить запуск макроса по нажатию на соответствующей ячейке. Добавим колонку в таблице со списком сотрудников, и назовем ее «Формирование резюме«. Войдем в редактор VBA и внесем макрос в код листа «База«:

Тем самым мы настроили запуск макроса «Создание_резюме» по клику в ячейке колонки «Формирование резюме«.

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

Таблица учета персонала в Excel. II часть

Таблица учета персонала в ExcelТаблица учета персонала в Excel. Часть II.

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

Но есть одна сложность – некоторые данные о кандидате нужно разместить в нескольких строках.

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

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

Проработка структуры

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

  • Образование
    • Код сотрудника;
    • Дата поступления;
    • Дата окончания;
    • Вуз;
    • Факультет;
    • Специальность.
  • Стаж
    • Код сотрудника;
    • Организация;
    • Дата начала работы;
    • Дата окончания работы;
    • Должность.

Теперь обдумаем порядок внесения данных в эти таблицы. В основной таблице предусмотрим возможность ввода данных в колонку «Образование» из списка (о создании списков я писал ранее).

Внесем следующие варианты:

  • Начальное;
  • Н. среднее;
  • Среднее;
  • Н. высшее;
  • Высшее.

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

Аналогичным образом предусмотрим внесение данных через форму в таблицу «Стаж». Создадим список с вариантами «Есть опыт работы» и «Не работал» и запуск формы настроим по выбору варианта «Есть опыт работы».

Создание форм

Для начала создадим формы. Открываем вкладку «Разработчик» и жмем на кнопку «Visual Basic».

В открывшемся мастере выбираем  пункт меню «Insert» -> «UserForm». Будет создана пустая форма, в свойствах которой найдем строку Name  и внесем туда наименование формы «Образование». На панели элементов формы нам понадобятся:

  • Label – для внесения наименования полей;
  • TextBox – поля для внесения текста;
  • ComboBox – поля для выбора значений из списка;
  • CommandButton – кнопки для запуска команд в макросе.

С помощью них создадим форму «Образование», и форму, которой дадим наименование «ТрудовойСтаж»:

Форма «Образование»

Форма «ТрудовойСтаж»

Как видите, в обеих формах ввод дат должен производится с помощью выбора каждого элемента даты из списка. Эти элементы необходимо создать на листе «Списки». После того, как все сделано, настроим эти поля. Кликнем на одном из них (например, на поле «Число»), и в его свойствах найдем строку RowSource. Туда внесем ссылку на диапазон со списком. Аналогичным образом настроим ссылки на всех прочих полях со списком.

Формы созданы. Теперь необходимо написать макросы

Макросы заполнения данных из форм

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

  • Добавить запись – данные вносятся в соответствующую таблицу, затем все поля формы очищаются а курсор устанавливается в поле начала ввода данных (для формы «Образование» это число даты поступления, а для формы «ТрудовойСтаж» поле нименования организации.
  • Завершить ввод – данные вносятся в соответствующую таблицу и форма закрывается.
  • Отмена – форма закрывается без ввода данных.

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

Макрос запуска формы

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

Вносим сюда следующий код:

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

Таблица учета персонала в Excel. I часть

Таблица учета персонала в ExcelТаблица учета персонала в Excel. Часть I.

Базу данных по персоналу целесообразнее всего создавать в Access.

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

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

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

А сейчас подумаем, что нужно предусмотреть в нашей программе:

  • Для начала – это просто место хранения данных по работающим сотрудникам. Поэтому туда должна войти вся кадровая информация (паспортные данные, сведения об образовании, стаже и т.п.);
  • Некоторые данные о сотруднике (например — места обучения, места работы)мы не сможем уместить в одну строку (то есть, конечно, сможем, но это будет неправильно с точки зрения ведения баз данных), поэтому необходимо предусмотреть возможность внесения нескольких строк, но без нарушения структуры списка и без дублирований;
  • В списке кандидатов уже внесена необходимая информация. Было бы безумным расточительством тратить время сотрудников на внесение одинаковых данных несколько раз. Данные должны импортироваться, но, при этом, необходимо допустить и занесение информации вручную;
  • Как обычно – предусмотрим возможную защиту от ошибочного ввода;
  • Неплохо было бы формировать вывод на печать резюме по сотруднику. Подумаем, как это можно будет реализовать;
  • Помимо личных данных о сотруднике, в базе должна собираться информация о развитии сотрудника в рамках организации – смена должности, повышение разряда и т.п.
  • На основании статуса «принят» или «уволен» сотрудник должен по-разному отображаться в списке. Уволенные сотрудники не должны пропадать из списков, но данные по ним должны собираться отдельно.

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

Создание таблицы                                

О создании таблицы много писалось на страницах этого сайта (см., например, ), поэтому просто перечислю список необходимых полей:

  1. — порядковый номер;
  2. Фамилия — фамилия сотрудника;
  3. Имя;
  4. Отчество;
  5. Дата рождения;
  6. Пол;
  7. Образование — здесь будет не только отметка об уровне образования, но и ссылка на форму ввода данных о местах обучения;
  8. Опыт работы — ссылка на ввод данных о местах работы;
  9. Дата приема — дата приема сотрудника в компанию;
  10. e-mail — адрес личной электронной почты сотрудника;
  11. Рабочий e-mail — адрес корпоративной электронной почты сотрудника;
  12. Телефон домашний;
  13. Телефон мобильный;
  14. Телефон рабочий;
  15. Департамент — допустим, компания разбита на департаменты. Соответственно, указываем департамент, к которому относится подразделение сотрудника;
  16. Отдел;
  17. Должность;
  18. Статус — например, это могут быть варианты «испытательный срок«, «принят«, «уволен«.

Дополнительные пункты можно добавлять. Поскольку таблица у нас будет «Умная», то дополнение колонок не повлияет на формулы и макросы, которые мы будем использовать.

Примерно такая таблица должна получится.

В прошлых списках, которые мы создавали ранее, порядковый номер строки приходилось вводить вручную. Для «Умной таблицы», которая автоматически добавляет формулу в новую строку, логичнее было бы вставить функцию, которая указывает порядковый номер. Это функция СТРОКА. Она указывает порядковый номер строки указанной ячейки. Поскольку первая строка таблицы начинается не с первой строки листа, то внесем формулу =СТРОКА(A1). Таким образом, при добавлении новой строки, следующая формула будет указывать уже на ячейку A2 и так далее, отображая, тем самым, тот порядковый номер, который нас интересует.

Импорт данных из таблицы учета кандидатов

Давайте подумаем, каким образом будет удобнее давать команду на перенос данных из таблицы кандидатов в таблицу сотрудников? Вариантов два:

  1. В момент, когда в таблице кандидатов вносится статус «принят»;
  2. По команде из таблицы со списком сотрудников.

На мой взгляд, первый вариант будет и логичнее и удобнее. Мы выбираем кандидата, устанавливаем ему статус «принят» и, тем самым, запускаем макрос экспорта данных об этом кандидате в таблицу принятых сотрудников. Так и поступим. Вернемся к созданному ранее файлу «Список кандидатов«.

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

«Экспорт_данных» это имя того макроса, который должен запуститься. Ранее мы уже писали код, который данные из одного файла копирует в другой файл (см. например, статью «»). Слегка модифицируем его, ведь нам нужно копировать не в тот файл, из которого запускается макрос, а, наоборот — из него в другой. А конце, предлагаю, закрыть файл «Список кандидатов«, а активировать для дальнейшего редактирования файл «Список сотрудников«.

Получается такой код:

В номерах колонок сориентироваться будет проще, если изменить стиль на R1C1. Для этого открываем Параметры Excel, жмем на пункт меню «Формулы» и ставим галочку напротив пункта «Стиль ссылок R1C1«. Теперь все колонки пронумерованы.

При изменении структуры таблиц кандидатов и сотрудников, следует изменить номера колонок в соответствующих ссылках, а именно:

  • Если изменена структура таблицы «Список сотрудников«, то во всех командах Offset меняем второе число на номер колонки минус 1 (в нашем коде Offset, это смещение от первой колонки, поэтому такое соответствие);
  • Если изменена структура таблицы «Список кандидатов«, то во всех командах Cells меняем второе число на новый номер колонки.

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

Учет кандидатов на вакансии в Excel. III часть

Учет кандидатов на вакансии в ExcelУчет кандидатов на вакансии в Excel. Часть III.

В статье мы разработали макрос, импортирующий данные о кандидате на вакансию из анкеты кандидата.

Однако, чтобы этот макрос корректно работал, необходимо создать шаблон анкеты (подобно тому, как мы создавали ).

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

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

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

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

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

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

Для ряда ячеек нужно создать списки:

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

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

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

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

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

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

После создания кнопки и прикрепления к ней макроса, шаблон можно считать готовым. Если вы редактируете список в шаблоне или меняете форму анкеты, то сохраняйте файл с помощью макроса, запускаемого из созданной нами кнопки (программа предложит заменить прежний шаблон, нажмите «Да»). А если создаете анкету из шаблона, то используйте обычное сохранение.

Файл, пользуйтесь. В статье займемся небольшими доработками и отчетностью.

Учет кандидатов на вакансии в Excel. I часть

Учет кандидатов на вакансии в ExcelУчет кандидатов на вакансии в Excel. Часть I.

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

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

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

Давайте продумаем возможности этой базы:

  • Хранение необходимых данных о кандидате на вакансию;
  • Возможность быстрого поиска и фильтрации по разным признакам;
  • Возможность быстрого открытия полного резюме о выбранном кандидате;
  • Возможность экспорта данных при изменении статуса кандидата на статус сотрудника;
  • Желательно – автоматическая загрузка данных о кандидате из анкеты;
  • Увязывание источника информации о вакансии с базой затрат (с данными о контрагентах);
  • Возможность быстрого вывода списка кандидатов при выборе той или иной вакансии в списке вакансий.

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

Создание таблицы и списков

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

  • — порядковый номер записи;
  • Фамилия – фамилия кандидата;
  • Имя – имя кандидата;
  • Отчество – отчество кандидата;
  • Дата обращения – дата обращения в службу персонала;
  • Дата рождения;
  • Образование – уровень образования (начальное, среднее и т.д.);
  • Специальность – специальность по образованию;
  • Текущее (последнее) место работы;
  • Настоящая должность – должность, в которой в настоящее время работает кандидат либо последняя должность, в которой он работал;
  • Текущая ЗП – размер заработной платы, которую кандидат получает (получал) на последнем месте работы;
  • e-mail – контактный адрес электронной почты;
  • Телефон – контактный телефон;
  • Источник – источник информации о вакансии;
  • Вакансия – вакансия, на которую претендует кандидат;
  • Ожидаемая ЗП – размер ожидаемой заработной платы;
  • Итог 1 – результат первого собеседования (телефонного интервью);
  • Итог 2 – результат 2-го собеседования;
  • Итог 3;
  • Итог 4;
  • Итог 5;
  • Итоговый результат (статус);
  • Дата выхода – дата выхода кандидата на работу по взаимной договоренности.

Для следующих полей создадим списки:

  • Образование – уровень образования (начальное, среднее и т.д.);
  • Источник – источник информации о вакансии, на которую отреагировал кандидат;
  • Итог 1 – результат первого собеседования (телефонного интервью);
  • Итог 2 – результат 2-го собеседования;
  • Итог 3;
  • Итог 4;
  • Итог 5;
  • Итоговый результат (статус);

А вот значения поля «Вакансия» должны браться из файла «База вакансий». Это необходимо для того, чтобы обеспечить идеальное совпадение списков в разных файлах. В противном случае невозможно будет обеспечить взаимосвязь разных таблиц в отчетах или других обработках.

Импорт списка

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

Задача макроса будет состоять в том, чтобы при каждом открытии файла обновлять списки, ссылающиеся на другой файл. Незаметно открывать файлы мы научились . Воспользуемся тем же алгоритмом. Единственное, что необходимо изменить в макросе – это ссылки. Во-первых, мы будем не выбирать файл вручную (ведь нам уже известно, что это за файл и где он расположен), а укажем сразу к нему путь, чтобы он запускался без нашего участия. Во-вторых — мы будем ссылаться не на диапазон ячеек, а на колонку таблицы «Должность», чтобы при изменении списка удаленные или добавленные его элементы были учтены в дублирующем списке. Вот текст макроса, который необходимо добавить:

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

Последний штрих – необходимо обеспечить запуск макроса при каждом открытии книги. Для этого необходимо в списке объектов редактора VBA найти пункт «Эта книга», подвести к нему курсор и нажать правую кнопку мыши. Выберите пункт View Code и нажмите на него. В открывшемся окне внесите следующий макрос (см.рис.):

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

На этом мы прервемся. Создание списка закончено, а последующими обработками и отчетами займемся .

Учет заявок на персонал в Excel. II часть

Учет заявок на персонал в ExcelУчет заявок на персонал в Excel. Часть II.

Продолжаем работу.

В прошлой статье описан порядок создания шаблона заявки на подбор персонала.

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

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

В этом случае нам никак не обойтись без создания макроса (подробнее о работе с макросами здесь).

Поэтому создаем новый файл, и сохраняем его в формате с поддержкой макросов.Назовем его «База вакансий«. Лист с таблицей назовем «База»

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

Открываем закладку меню «Разработчик» и жмем пункт Visual Basic. В открывшемся приложении для написания кода VBA.

Вносим наименование макроса «Заполнение_базы_заявок» (в имени макроса не должно быть пробелов, поэтому вместо них вносим нижние подчеркивания).

Для начала, необходимо продумать порядок работы макроса. Я предлагаю такой:

  1. Запуск макроса будем производить с помощью кнопки, расположенной в первой строке. Поэтому таблицу необходимо сдвинуть на строку вниз;
  2. При нажатии кнопки должно появиться окно, для выбора импортируемого файла;
  3. Данные из файла, поскольку они имеют совершенно иную структуру, скопируем на некий промежуточный лист. Создадим его и назовем «Операт» (в принципе, его создание можно было бы тоже заложить в макрос, но я не стал усложнять задачу);
  4. Скопированные данные необходимо разместить ниже заполненной строки таблицы. Таким образом мы обеспечим накопление данных.

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

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

  • для получения пути к файлу (тип String (строка));
  • для определения диапазона копирования (тип String (строка));
  • для указания данных (тип Variant, т.е. — любой. Этот тип можно не указывать).

Чтобы долго не расписывать, выкладываю текст макроса со своими комментариями:

Внесение порядкового номера новой строки

Чтобы внести порядковый номер новой строки, я предлагаю использовать цикл со счетчиком For…Next. Но, прежде чем его использовать, необходимо обдумать, как мы будем определять последний разряд счетчика.

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

Используем макрорекордер — запускаем запись макроса, жмем Ctrl-Shift-стрелка вниз, останавливаем запись макроса. Записанную команду используем для макроса «Заполнение_базы_заявок«.

Нам потребуется переменная для счетчика цикла. Объявляем ее строкой Dim i As Integer. Затем используем команды в следующей последовательности:

  1. активируем лист «База«;
  2. устанавливаем курсор на ячейку «А2» (для определения верхней границы выделяемого диапазона);
  3. выделяем диапазон с заполненными ячейками;
  4. если первая строка таблицы не заполнена, то в ячейку «А3» ставим цифру 1, а переменной «i» присваиваем значение 2. Это необходимо, так как цикл в этом случае не запускается, и счетчик будет равен нулю, а на следующих шагах он будет использован;
  5. если первая строка таблицы заполнена, то запускаем цикл, проставляющий порядковые номера в выделенном диапазоне;
  6. Устанавливаем значение переменной «i».

Текст макроса следующий:

Вставка новых данных

Значение переменной «i» указывает нам номер строки, в которую мы будем вставлять скопированные данные. Здесь мы используем команду Cells, которая указывает на ячейку по номеру строки и колонки. Поскольку наша таблица смещена на строку вниз, то к номеру новой строки таблицы необходимо прибавить единицу.

Текст макроса достаточно прост, просто повторяем его по отношению к каждой ячейке таблицы, меняя номер колонки и адрес ячейки, с которой переносим данные:

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

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

На этом все. Файл с текстом макроса во вложении.

В следующей статье приступим к формированию отчета по открытым вакансиям за период.

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

Доработка макроса I

Доработка макроса I в vba excelВ прошлой статье мы освоили макрорекордер.

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

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

Поэтому, как ни крути, придется нам все-таки влезть в код, и поправить ситуацию. А, за одним, рассмотрим логику программирования VBA.

Язык программирования, это, по сути, обозначение неких объектов (в Excel объектами являются, например, ячейки и листы), придание этим объектам каких-либо свойств (например, присвоение ячейке какого-либо значения) и назначение им команды (например, скопировать или вставить).

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

Итак — займемся разборкой записанного ранее макроса.

Изучение записанного макроса

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

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

Входим в закладку меню «Разработчик» и жмем на кнопку «Visual Basic«. Это мы открываем редактор для ручного ввода макросов на языке VBA. Вот что мы видим (с небольшими вариациями):

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

Вроде бы ничего сложного, правда?

Внесение исправлений в макрос

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

Но, собственно, нам нет необходимости выделять ненужные колонки.  Мы просто удалим их без выделения. А для этого нужно просто удалить команду выделения (select), и вместо нее вставить команду удаления обозначенных колонок. Таким образом строка будет выглядеть так: Columns(«F:J»).Delete Shift:=xlToLeft

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

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

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

Команда очистки пишется так: Clear. Это единственное слово, которое отсутствовало в макросе, но  его не мешало бы и запомнить. Для очистки листа нужно его выделить (Sheets(«Прайс123»).Select), и дать команду на очистку всех ячеек (Cells.Clear).

Далее, для вставки скопированного диапазона, я не выделял весь лист, а просто активировал ячейку A1 (Range(«A1»).Select). Полностью исправленный макрос выглядит так:

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

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

Доработка макроса II

Доработка макроса II в vba excelСозданный в макрорекордере макрос, как мы уже убедились ранее, не всегда будет работать именно так, как бы нам хотелось.

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

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

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

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

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

Что меня здесь не устраивает?

  • Множество объединенных ячеек мешает воспринимать данные как таблицу. С точки зрения оформления это — неплохо, но как мы будем писать формулы без риска ошибиться? Кроме того, когда мы будем протягивать формулы на другие ячейки, они будут у нас сбиваться на ненужные диапазоны.
  • В каждой строке у нас расположены наименования должностей. А наименования подразделений и департаментов находятся в объединенных ячейках, а, значит, соответствуют только одной верхней строке того диапазона, который они объединяют. Нужно разбить ячейки и скопировать записи на каждую строку, чтобы программа однозначно понимала, что такая-то должность находится в таком-то подразделении такого-то департамента.
  • Чтобы таблица воспринималась программой целостно, в колонках и строках не должно быть разрывов. А это произойдет, когда мы отменим объединение ячеек.

В общем-то исправить нужно не так уж и много, но когда мы этим займемся, поймем, что манипуляций придется проделать немало, и лучше бы все автоматизировать. Для этого сохраним файл в формате с поддержкой макросов (с расширением .xlsm). Открываем закладку меню «Разработчик» и жмем на пункт «Запись макроса«. Я установил имя «Штатное«. Далее выполняем последовательно следующие операции:

  1. Выделить весь лист (нажать мышкой на пересечение адресных строк и колонок);
  2. Нажать на кнопку «Объединить и поместить в центре» в закладке меню «Главная» (таким образом мы отменили объединение ячеек);
  3. Вызвать контекстное меню (правая кнопка мыши) и выбрать «Формат ячеек«. Там:
  4. За закладке «Выравнивание» настроить «Ориентацию» 0 градусов;
  5. Установить начертание «Обычный«, а размер 10 пунктов;
  6. В закладке меню «Главная» найти и нажать пункт «Формат«. В открывшемся списке выбрать пункт «Ширина столбца» и установить ширину 5 (чтобы проще было удалять пустые колонки);
  7. Удалить все колонки без записей. При этом, не обращайте внимания на строки с 1 по 12. Их мы потом тоже будем удалять. У вас должны остаться все пронумерованные колонки с 1 по 10;
  8. Настроить ширину колонок как вам удобнее;
  9. Дать новые имена колонкам (чтобы наименование колонки располагалось в одной ячейке;
  10. Удалить строки с 1 по 15;
  11. Протянуть наименования департаментов, чтобы каждая должность соответствовала тому департаменту, к которому она относится;
  12. Так же протянуть наименования подразделений;
  13. Увеличить шрифт заголовков;
  14. Выделить заголовки жирным;
  15. Поправить ширину колонок;
  16. Остановить запись макроса.

Вот что должно примерно получиться:

Удаление лишних записей

Теперь давайте откроем вкладку меню «Разработчик«, и нажмем пункт Visual Basic. Я предлагаю свернуть его в оконный режим, и разместить в нижней части экрана, чтобы в верхней части мы видели нашу рабочую область в Excel (см.рис.).

В меню редактора VBA есть пункт Debug. В переводе он означает «Отлаживать«. То есть, другими словами, в этом меню находятся команды для отладки макроса. Открываем его и видим пункт, который нас интересует — Step Info — пошаговый режим. С его помощью мы будем проверять работу каждой команды макроса. Чтобы не лезть всякий раз в меню, проще запомнить и использовать «Горячую клавишу» F8. Каждое ее нажатие запустит одну команду макроса.

Итак, жмем клавишу F8 и… получаем сообщение об ошибке. Оно говорит нам, что процедура слишком большая. Это произошло из-за того, что команда по отмене объединения ячеек воспринялась программой своеобразно — она начала проверять каждую выделенную ячейку на предмет объединения и, кроме того, выполнять массу других команд, которых мы, собственно, не задавали. Жмем на кнопку остановки (квадратик во второй строке меню), и смотрим на текст макроса.

Видим повторяющиеся блоки, начинающиеся со слова With, и заканчивающиеся словами End With. Это блочная команда, которая назначает свойства объектам. Она повторяется много раз, и перегружает наш макрос. А нужно то всего лишь одно свойство — отмена объединения по отношению ко всем выделенным ячейкам сразу. То есть, нужна только одна строка — отменить объединение ячеек (MergeCells = False), которые выделены (Selection).

Чтобы понять, что означает каждая команда в блоке With … End With, я просто посмотрел их перевод в гугле и понял, что за объединение отвечает команда со словом Merge. А Cells, это ячейки.

Все вышесказанное означает, что нам необходимо удалить ВСЕ блоки следующего содержания:

Это, не удивляйтесь, более 95% всего содержания макроса! Для упрощения задачи зажмите курсор мыши на каретке перемещения справа, и сдвиньте ее вниз примерно на 95%. Если все еще будут эти повторяющиеся блоки, то сдвигайте курсор ниже. Установите курсор после слова End With последнего повторяющегося блока (будьте внимательны, там есть другие блоки With … End With, но они с другим содержанием. Не удалите случайно их.

Теперь с помощью каретки поднимитесь наверх, зажмите Shift и кликните курсором мыши перед словом With второго блока (первый мы будем править). Нажмите Delete.

Для отмены объединения ячеек нам не нужна блочная команда. Мы ведь прочие свойства не трогаем. Поэтому удаляем блок With … End With, оставляя команды в одну строку Selection.MergeCells = False.

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

Доработка макроса III

Доработка макроса III в vba excelВ прошлой статье мы записали макрос и начали его правку — удалили лишние записи, обеспечив, тем самым, сокращение текста программы более чем на 95%!

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

Параметры шрифта и колонок

Следующий блок With … End With устанавливает ориентацию надписей 0 градусов. Это я понял по слову .Orientation = 0. Здесь нам, собственно, тоже целый блок не нужен. Оставляем и здесь одну строку: Selection.Orientation = 0

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

Следующие команды, это серия манипуляций с колонками — назначение ширины, удаление лишних колонок. Можно, конечно, с помощью клавиши F8 просматривать выполнение этих команд, но тут уже все очевидно — выделить (Select) колонки (Columns), удалить (Delete) выделенное (Selection) со смещением влево (Shift:=xlToLeft). Здесь все оставляем без изменений.

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

Затем мы писали наименования колонок. Команда Range(«A16»).Select выделяет нужную ячейку, а команда  ActiveCell.FormulaR1C1 = «Департамент» вносит в эту ячейку слово «Департамент«. И так с каждой колонкой. Две следующие команды выделяют и удаляют лишние строки.

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

Ручная запись макроса

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

С помощью макрорекордера можем выяснить как обозначается свойство «Нижняя толстая граница«:

  1. Открываем новый файл;
  2. Запускаем макрорекордер;
  3. В закладке меню «Главная» находим и жмем пункт «Нижняя толстая граница»;
  4. Останавливаем макрорекордер.

Таким образом выясняем, что свойство обозначается «.LineStyle = xlContinuous«.

В VBA есть три вида циклов:

  • по счетчику;
  • по условию;
  • по виду обрабатываемых объектов.

Я выбираю цикл по условию, поскольку в качестве условия мы уже выбрали нижнюю толстую границу ячейки. Цикл по условию выглядит так:

Do

команды, которые необходимо повторять

Loop Until условие, при котором цикл должен прекратиться

И еще одно новшество, которое мы ранее не использовали — назначение переменных. Это что-то похожее на присвоение имен ячейкам. Только имена мы присваиваем не ячейкам, а значениям, которые не появляются в таблице, но используются для вычислений. В нашем случае будет одна переменная, которую мы назовем, например, rngX (от слова Range — диапазон).

Назначаем переменные с помощью команды Dim … As …, где после Dim указываем имя, которое мы придумали переменной, а после As указываем тип данных. Тип данных может иметь много  значений:

String  Используется для хранения строк. До миллиона символов.
Integer Целое число от -32768 до 32767
Long Целое число от -2 147 483 648 до 2 147 483 647
Single Дробное число одинарной точности (до 8-го знака. Целые числа и запятая тоже считаются за знаки. )
Double Дробное число двойной точности (до 16-го знака. Целые числа и запятая тоже считаются за знаки.)
Date Полная дата, включая время. Пишется между решетками — #06/15/2012 10:00:00# Сначала пишется месяц, потом день, потом год.
Boolean Хранить только два значения — True и False
VARIANT Объявление переменной без указания типа. Требует много памяти, поэтому не рекомендуется использовать часто. Кроме того, может неверно определить данные.
Range Адрес ячейки или ссылка на диапазон

Нам подходит тип Range.

Запись 1-го цикла

Итак, после обозначения переменной, присваиваем ей адрес первой ячейки обрабатываемого диапазона — А4 (Set rngX = Range(«A4»)). Затем объявляем начало цикла словом Do. В следующей строке, копируем значение, которое находится в переменной, который мы указывали ранее: rngX.Copy

Далее необходимо переместить переменную вниз. Для этого мы используем команду Set (которая закрепляет за переменной какой-либо объект) и команду Offset (которая смещает объект по горизонтали или вертикали). Вот как это выглядит: Set rngX = rngX.Offset(1, 0)

Следующий шаг — вставка скопированного значения. Мы могли бы ограничиться командой PasteSpecial, но она вставит полностью всю ячейку, и, в этом случае, мы потеряем толстую нижнюю границу в соответствующих ячейках. Поэтому используем ограничение, которое пишется так: Paste:=xlPasteValues. Полностью строка команды будет такой: rngX.PasteSpecial Paste:=xlPasteValues

И, последнее, установим условие прекращения цикла. Как мы помним, таким условием является толстая граница ячейки, обозначаемая LineStyle = xlContinuous. Но, прежде чем указывать свойство, нам нужно обозначить объект, которым является нижняя граница нашей переменной. А обозначается она rngX.Borders(xlEdgeBottom).

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

Мы записали цикл до первого жирного разделителя. А таблица то у нас продолжается! Поэтому этот первый алгоритм необходимо вложить в другой, который будет запускать его со следующей строки. Об этом мы поговорим в следующей статье.