Использование макроса для автоматизации повторяющихся операций Excel

ExcelИспользование макроса для автоматизации повторяющихся операций Excel.

Это страшное слово «Макрос», на самом деле, не такая уж и сложная штука.

Любой начинающий пользователь Excel может самостоятельно запрограммировать типичные операции, абсолютно ничего не зная ни о каких языках программирования.

Для этого MS Excel имеет очень интересный инструмент, под названием макрорекордер — надстройка, которая включается через меню параметров Excel.

Нажимаем кнопку office в левом верхнем углу и выбираем пункт «Параметры Excel«. В открывшемся окне уже будет активна вкладка «Основные«. Находим пункт «Показывать вкладку «Разработчик» на ленте«, и ставим галочку в окошке слева от него. На ленте меню появится вкладка «Разработчик«. Ею мы и будем пользоваться.

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

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

Итак, мы имеем прайс-лист, который нужно раскидать по листам в соответствии с группой клиентов (см.рисунок. как вы понимаете — компания вымышленная, и даже цены по всей номенклатуре установлены нереальные). Алгоритм действий несложен:

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

Макрос готов. Давайте проверим — удалим со всех листов, кроме «Свод» все данные, и нажмем сочетание клавиш Ctrl+п. Как видите — все работает. Теперь, чтобы обновить прайс-листы, не нужно будет нудно выполнять одни и те же операции.

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

Оценка 360 градусов в Excel

Оценка 360 градусов в Excel
Оценка 360 градусов в Excel

Многим сотрудникам HR-служб не в диковинку система оценки 360 градусов.

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

Нередко такую оценку используют при аттестации сотрудников.

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

Структура файлов

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

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

Создание бланка

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

Откройте лист «Компетенции» и внесите наименования компетенций во вторую строку. Обратите внимание, что все наименования вносятся через одну колонку, поскольку вторая колонка предназначена для примечаний (ячейки колонки для примечаний окрашены в желтый цвет).

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

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

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

Возвращаемся на лист «Компетенции и жмем кнопку «Обновить бланк». Оценка 360 градусов в Excel

На листе «Бланк» будет сформировано необходимое количество блоков по количеству компетенций.

Настройка бланка

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

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

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

Остается исключить лишние листы. Сделать это можно двумя способами – либо скрыть листы, либо переместить лист «Бланк» с копированием в новый файл. Одно условие – наименование листа с бланком должно быть именно «Бланк», и никак иначе.

Импорт бланков и обработка результатов

Оценка в Excel 360 градусов

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

В самую последнюю очередь появится окно с вопросом: «Будем обновлять список менеджеров?«. Если вы заранее составили полный список оцениваемых сотрудников, то нажмите «Да» только при загрузке самого первого бланка. В дальнейшем, можете нажимать «Да» только в случаях, когда список оцениваемых будет дополнен.

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

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

  • Перекрестная – в строках расположены ФИО оцениваемых сотрудников, а в колонках – ФИО экспертов. Это вспомогательная таблица. На пересечении фамилий можете ставить любые отметки.
  • KPI – помимо экспертной оценки, неплохо бы использовать и показатели результатов деятельности. Внесите процент выполнения показателей каждым оцениваемым сотрудником, и эти проценты преобразуются в баллы в соответствии с расположенной ниже таблицей. Интервалы в таблице можно менять. Главное – не менять расположение от меньшего к большему.
  • Подсчет результатов – здесь размещаются блоки с расчетными баллами по каждому оцениваемому. Осмысленные значения будут появляться только при наличии оценок, как минимум, Руководителя и самооценки.
  • Рейтинг – итоговые расчеты с рейтингом.
  • Веса — страница, на которой можно настроить вес той или иной оценки при расчете итогового балла. Как видим, если у сотрудника есть только оценка руководителя и самооценка, то при расчете итоговой оценки вес баллов будет распределяться по варианту 3. При наличии оценок руководителя, подчиненного и самооценки — по варианту 2. При наличии оценок всех экспертов — по варианту 1. Если вы хотите исключить участие оценок KPI в рейтинге, то установите этому виду оценок вес 0, а компетенциям вес 100%.

Конструктор бланков скачивайте , а обработчик .

P. S: надеюсь, вы помните, что для работы макросов, их необходимо подключить. Тем, кто не знает, как это делать, ознакомьтесь со .

График смен. I часть

График смен в ExcelГрафик смен. Часть I.

Как показывает анализ посещений страниц этого сайта, читателей интересуют готовые файлы для офисной работы.

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

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

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

А сейчас вернемся к инструментарию кадровиков. В я писал об учете рабочего времени. Точнее – о создании автозаполняемого табеля рабочего времени Т-12. Хотя там и предлагалась форма ввода данных об отработанном времени сотрудников, но она не позволяла вести, например, посменный учет. Для планирования графика смен и расчета отработанного времени в соответствии с выбранным графиком, лучше использовать файл, созданный специально для этих целей. О нем и речь. Как всегда, в конце статьи имеется ссылка на скачивание.

Итак, что умеет делать созданный мной файл? Сразу оговорюсь, что для его нормального функционирования потребуется включить макросы (похожий файл без макросов можно скачать ). В параметрах Excel есть пункт «Центр управления безопасностью».

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

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

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

Затем вносим фамилии, имена и отчества работников в соответствующую колонку. При внесении ФИО сотрудника, автоматически добавляется строка и порядковый номер этой строки. В календарной части строки ячейки, соответствующие выходным дням, окрашиваются в бледно-розовый цвет. Если в месяце имеются праздничные дни, то ячейки, соответствующие им, окрасятся в красный цвет.

К вопросу о праздниках – государств и республик много. У каждого свои порядки. Поэтому в файле предусмотрена возможность коррекции списка праздников. Достаточно просто в колонку «А» внести наименование праздника, а в колонку «В» внести дату. Если к одному празднику относятся несколько дней (как в новогодние каникулы, например), то придется продублировать все эти дни отдельными строками. Интервалы дат таблица не поймет.

После ввода фамилии можно ввести наименование должности и выбрать смену на месяц для этого сотрудника. Но для выбора необходимо настроить списки. Список должностей вносится на листе«Списки». Зайдите на него и просто отредактируйте имеющийся список должностей в соответствующей таблице. А вот для настройки необходимых смен потребуется отдельная тема.

На листе «Смены» можно настроить любое количество смен. Для этого введите наименование смены в соответствующей колонке. К таблице автоматически добавится строка с соответствующим порядковым номером.

В колонки «Время начала» и «Время окончания» вносим, соответственно, время начала ежедневной работы в данной смене, и время ее окончания. Можно вводить значения от 0:00 до 23:59 только в формате времени (часы и минуты, разделенные двоеточием). При вводе любых других значений и в другом формате программа выдаст сообщение об ошибке и не даст внести данные.

Аналогичным образом внесите продолжительность обеденного перерыва. Здесь возможен ввод временного интервала от 0:00 до 2:00 часов (время перерыва, допускаемого законодательством).

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

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

В колонке ИТОГО автоматически будет считаться общее количество часов, накопленных за месяц в настраиваемой смене. Это удобно при настройке, учитывая, что мы ориентируемся на нормативное количество рабочих часов в месяце, установленных законодательством.

После настройки всех смен, возвращаемся на страницу «График».

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

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

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

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

Формирование отчета

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

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

Я постарался предусмотреть возможные ошибки, и предотвратить их появление. Но, возможно, в процессе эксплуатации вы, все-таки, их обнаружите. Очень вас прошу – напишите об этом в комментариях к статье. Тем самым, вы поможете мне их исправить и опубликовать более совершенную версию.

И, как обещал, выкладываю  на скачивание созданного файла.

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

Формирование резюме сотрудника 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. IV часть

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

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

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

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

В файле «Список кандидатов» ошибка может быть связана с тем, что пользователь выбрал не тот файл. Других вариантов я здесь не вижу.  Поэтому в макрос нам необходимо добавить блок, который будет проверять, не возникла ли при выполнении основного макроса ошибка.  Это своеобразный аналог функции ЕОШИБКА в Excel.

Выглядит он так:

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

После строки «If Err <> 0 Then» вносим текст макроса, выводящего сообщение «Вероятно, вы выбрали не тот файл!«.  Если мы начнем проверять, как выполняется этот макрос, то обнаружим, что дополнительную строку он, все-таки, создаст. Более того, в этой строке будет вставлена ссылка на тот, ошибочный, файл, который мы выбрали. поэтому добавим еще одну команду — удаление строки. Вот что должно получиться в самом конце нашего макроса:

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

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

Если выбрать пункт «Сортировка от А до Я«, то вся таблица будет отсортирована от А до я по колонке «Настоящая должность«. Это бывает удобно, когда требуется найти кандидата по фамилии в большом списке.

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

Теперь перейдем к отчетам. Ничего нового здесь я вам не скажу. Будем создавать отчеты также, как и при создании базы вакансий (подробнее здесь). Предлагаю такие варианты:

Создаем соответствующие страницы:

Вставляем в них сводные таблицы и настраиваем поля отображения сводных значений.

При желании — вставляем графики, на основании сводных.

Учет кандидатов на вакансии в 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 и нажмите на него. В открывшемся окне внесите следующий макрос (см.рис.):

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

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