Автоматическое заполнение грамот из excel
Мне надо регулярно делать сотни дипломов для участников курса. Я настроил всё так, чтобы оно делалось само. Делюсь способом с вами.
У меня есть рассылка, на которую подписываются люди, месяц получают уроки, а в конце им должен прийти диплом. Никаких групп и потоков нет, все подписываются вразнобой. Каждую неделю появляются новые «выпускники», которым надо сделать дипломы. А после скидок и распродаж таких людей сотни.
Делать это руками неудобно. Даже если на каждый диплом будет уходить 10 секунд, все равно нужно караулить выпускников, вписывать их имена в шаблон, а потом отправлять PDF’ки по имейлу.
Сначала я хотел найти сервис, пусть даже платный, который будет автоматически генерировать PDF’ки и отправлять их подписчикам. Но я его не нашел, поэтому пришлось заколхозить подручными средствами.
Если коротко, схема такая:
- После курса подписчикам приходит письмо со ссылкой на Google-форму.
- В форме они пишут имейл, ФИО и указывают пол.
- Данные попадают в Google-таблицу.
- Формула в таблице разносит мужчин и женщин по разным листам.
- У мужчин и женщин есть отдельные шаблоны диплома с правильным родом глаголов.
- Сервис Zapier забирает данные из таблицы и создает одностраничную презентацию в «Google Слайдах» по заранее подготовленному шаблону.
- Презентация попадает в папку на «Google Диске».
- Сервис Zapier видит новый файл в папке и отправляет его подписчику по имейлу.
Если вы вдруг захотите сделать что-то подобное, рассказываю, как все настроить.
Тут все просто, заходим в «Google Формы» и создаем новую. У меня вот такая:
В нашей форме открываем вкладку «Ответы» и нажимаем на зеленую кнопку «Создать таблицу». Теперь все результаты заполнения формы будут попадать в Google-таблицу.
Сейчас в таблице будет всего одна вкладка, связанная с формой, — в нее попадают все ответы. Нам надо разделить подписчиков, чтобы на одной вкладке были женщины, а на другой мужчины.
Создаем вкладку «женщины». В ней делаем два столбца: «Имя» и «Email». Лучше писать названия столбцов в первой строке, так будет проще настраивать автоматизацию в Zapier.
Во второй строке столбца «Имя» пишем формулу:
=filter('Ответы на форму (1)'!$B$1:$B; 'Ответы на форму (1)'!$C$1:$C="Женский")У вас могут быть другие диапазоны — просто укажите столбец с именем, а потом столбец с полом.
То же самое делаем в столбце «Email»:
=filter('Ответы на форму (1)'!$E$1:$E; 'Ответы на форму (1)'!$C$1:$C="Женский")Формула находит на первой вкладке все записи с полом «Женский» и выводит их в этом столбце.
То же самое делаем для мужчин. Только в формулах вместо женского пола будет мужской.
Отправляем через форму несколько тестовых заявок и видим, что все работает.
Заходим в «Google Слайды», создаем новую презу, оформляем ее по своему вкусу.
Вместо имени выпускника пишем > — именно такими шрифтом и кеглем, какие должны быть в готовом дипломе.
Переменных со скобками может быть несколько. У меня это только имя, но вы можете добавить что-то еще: дату, название курса, номер потока, оценку и так далее.
У нас будет два шаблона: один для мужчин, второй для женщин. Разница в правильных родах глаголов вроде «получил/получила», «прошел/прошла» и так далее.
У меня получилось вот так:
Создаем папку на «Google Диске», в которой будем генерировать дипломы, кидаем оба шаблона в нее.
На бесплатном тарифе можно делать только простые автоматизации из двух шагов — именно поэтому мы заранее отфильтровали людей по полу в таблице. У нас будет два «запа» — каждый будет забирать людей из своей вкладки в таблице и создавать диплом на основе своего шаблона.
Регистрируемся в Zapier, нажимаем Create Zap.
В качестве первого приложения выбираем Google Sheets. Выбираем Trigger Event: New Response is Spreadsheet.
Авторизуемся в своем Google-аккаунте, чтобы сервис получил доступ к таблицам.
Находим в списке Spreadsheet нашу таблицу, а в Worksheet выбираем вкладку с выпускниками-женщинами. Если сервис тупит и не видит вашей таблицы, нажмите Refresh Fields.
Нажимаем Test Trigger. Если вы все сделали правильно, сервис найдет несколько записей в таблице (если они там есть). Нажимаем Continue.
Теперь нам надо выбрать второй шаг — что делать с данными, которые мы нашли в таблице.
Выбираем Google Slides. Выбираем Action Event: Create Presentation from template.
Открываем доступ к нашему Google-аккаунту.
Теперь настраиваем генерацию Google-слайда:
Is Shared — ставим True, чтобы к диплому был доступ по ссылке, иначе люди не смогут открыть его.
T emplate Presentation — находим наш шаблон для женщин. Дальше будет список полей в шаблоне, которые отмечены >. У меня только одно поле: Name.
Name — снова выбираем название столбца, у нас это «Имя». Оно вставится в презентацию вместо переменной в скобках.
Нажимаем Test & Continue и проверяем, что у нас создалось. Если вы все сделали правильно, в папке с шаблонами должна появиться новая презентация.
Затем делаем второй такой же Зап для мужчин.
Обратите внимание, что Zapier забирает данные из таблицы не мгновенно, а просматривает ее раз в 15 минут. Не пугайтесь, если после заполнения формы диплом не появится, — это нормально, надо просто немного подождать.
Изначательно я просто открыл доступ к папке с сертификатами по ссылке и добавил в цепочку рассылки письмо с инструкциями:
Но в комментариях справедливо заметили, что это нарушает закон о персональных данных — ведь каждый участник курса увидит в папке имена других участников. Поэтому я немного переделал этот шаг. Стало еще круче, потому что теперь дипломы могут получить только те, кто подписан на курс, а не все, у кого есть ссылка на анкету.
Я создал еще одну автоматизацию в Zapier — она находит новые дипломы в папке и добавляет их в Mailchimp в виде пользовательского события.
Создаем новый зап. В качестве первого приложения выбираем Google Drive. Trigger Event: New File in Folder. Открываем доступ к Google-аккаунту, находим в списке свою папку с дипломами.
В качестве второго приложения выбираем Mailchimp. Action Event: Create Custom Event.
Подключаем свой Mailchimp-аккаунт. В разделе Customize Event → Audience выбираем список наших получателей рассылки.
Custom Event Name — указываем название нашего пользовательского события. У меня это Diplom.
Properties — это параметры события, которые мы передаем в Mailchimp. Я сделал параметр Link, а в качестве значения выбрал PDF URL из списка. Если перейти по такой ссылке, то вместо презентации сразу скачается PDF-файл.
Заходим в Mailchimp, создаем новую кампанию. В качестве триггера выбираем API Event и указываем Event Name: Diplom. Теперь письмо будет сразу отправляться людям, у которых случилось наше пользовательское событие.
Создаем письмо, оформляем на свой вкус и в том месте, где должна быть ссылка на сертификат, пишем *|EVENT:link|*
Вместо него автоматически подставится значение параметра Link, в котором у нас лежит ссылка на сертификат.
У меня получилось вот так:
Запускаем рассылку — и все готово. Теперь, когда человек заполнит форму, в папке с шаблонами появится диплом, название которого — имейл подписчика. Когда в папке появляется новый файл, Zapier находит подписчика с нужным имейлом и создает для него событие со ссылкой на диплом. Когда у подписчика случается событие, Mailchimp отправляет ему письмо, в которое подставляется ссылка из события.
А если имейла в базе нет, то ничего не произойдет. Получается, если человек не подписан на курс, он не сможет получить диплом, даже если заполнил форму.
Через эту цепочку прошло уже больше 500 человек — все работает стабильно. Пару раз сервис глючил и не вставлял имя вместо переменной в презентации. Но выпускники просто оставляли еще одну заявку, и на второй раз все было окей.
На настройку всей этой фигни нужно не больше 20 минут.
Не секрет, что в наше время учителя буквально завалены бумажной работой. Помимо подготовки к урокам и проверки тетрадей, им приходится составлять бесконечные отчеты, считать проценты, рисовать диаграммы и графики, что-то сравнивать и анализировать. В этой статье мы хотели бы поделиться некоторыми приемами, которые, надеемся, хоть немного облегчат вам этот поистине Сизифов труд — работу с документами.
Прием 1
Часто в документах Excel встречаются списки, в которых ФИО написаны в одном столбце. А нам требуется разделить их на 3 столбца. Для этого:
- Выделите все ячейки, в которых ФИО написаны полностью.
- Выберите вкладку Данные — Текст по столбцам. Далее следуйте указаниям Мастера распределения текста по столбцам. Укажите формат данных — с разделителями:
Выберите разделитель — пробел.
Укажите первую ячейку, в которую нужно поместить данные:
Прием 2
Также в документах часто встречается дата, написанная в формате 25.05.2016. Нам надо разбить дату, так, чтобы день, месяц и год были в трех разных столбцах. Существуют разные способы такого преобразования. Можно в новых столбцах воспользоваться функциями:
=ДЕНЬ(A2) =МЕСЯЦ(A2) =ГОД(A2)
Если же месяц нужно написать словами, то вместо функции =МЕСЯЦ(A2) можно написать такую функцию:
Прием 3
Представим типичного классного руководителя, который должен в конце учебного года напечатать увесистую стопку грамот за участие в олимпиадах и конкурсах, спортивные достижения, дежурства и уборку класса. К счастью, в продаже большой ассортимент готовых грамот, но печатать то текст на них все равно нужно самостоятельно!
Для работы нам понадобятся сразу две программы — Word и Excel.
- В электронных таблицах Excel мы подготовим список награжденных детей.
Сохраните его под названием «Список.xls»
- Теперь возьмем в руки готовую грамоту и измерим линейкой расстояние от верхнего края грамоты до нижней линии строки с надписью «Грамота». Предположим, что это расстояние равно 8 см. (в дальнейшем от этой линии нужно будет отступить вниз еще несколько сантиметров)
- Открываем текстовый редактор Word. Перейдем на вкладку Разметка страницы — Поля
Установим ширину полей:
Теперь можно подготовить шаблон текста, который мы и будем печатать на грамотах, подставляя в него данные из нашего списка.
Установим размер шрифта 16-18 пт. и напишем, например, такой текст:
Классный руководитель: (напишите ФИО учителя)
Директор школы: (напишите ФИО директора)
Обратите внимание, что информация с фамилиями детей остается незаполненной, а ФИО классного руководителя и директора школы вписываются, так как эта информация повторяется на всех грамотах.
Сохраните этот документ-шаблон под названием «Шаблон.doc», но не закрывайте!
Перейдем на вкладку «Рассылка» и выберем команду «Начать слияние». Выберите тип документа, который нужно создать (например, письма).
Подключим этот документ к источнику данных (к нашему списку в Excel) Выберите команду Выбрать получателей — Использовать существующий список:
Укажите нужный файл «Список.xls»
Вставьте в нужные места документа Поля слияния (эти поля будут заполняться данными из файла списка).
Перед тем как печатать весь комплект копий грамот, каждую из копий можно увидеть в Предварительном просмотре.
Надеемся, что эти советы помогут вам в работе.
Источники информации
Об авторах: Курбанова Ирина Борисовна, Хайми Наталия Ивановна, учителя информатики ГБОУ школы № 594 Санкт-Петербурга, Россия.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст
Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.
Кстати, такая возможность есть
на всех страницах нашего сайта
Девиз: поднемите руки выше!
по
Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.
Для подтверждения подлинности выданных сайтом документов сделайте запрос в редакцию.
О работе с сайтом
Мы используем cookie.
Публикуя материалы на сайте (комментарии, статьи, разработки и др.), пользователи берут на себя всю ответственность за содержание материалов и разрешение любых спорных вопросов с третьми лицами.
При этом редакция сайта готова оказывать всяческую поддержку как в публикации, так и других вопросах.
Если вы обнаружили, что на нашем сайте незаконно используются материалы, сообщите администратору — материалы будут удалены.
При работе в электронных таблицах Excel бывает нужно постоянно вводить одни и те же значения (из списка). Стандартными списками являются последовательности дней недели или месяцев, но порой хочется создать свой список автозаполнения. Например, список класса, возраст учеников, размеры одежды или любых других данных, к которым постоянно приходится обращаться. Чтобы не вводить каждый раз эти значения вручную, можно создать свой список автозаполнения, а затем использовать его — в Excel есть такая возможность.
Для создания своего списка автозаполнения выполните следующие действия.
Если используется Excel версии 2003, то нужно выбрать меню Сервис — Параметры — Списки — Новый список — вводим элементы списка через клавишу Enter — выбираем Добавить — ОК.
Если используется Excel версии 2007 (2010), то нужно выбрать Файл — Параметры — Дополнительно — в Общие Изменить списки — Новый список — вводим элементы списка через клавишу Enter — выбираем Добавить — ОК.
После создания своего списка автозаполнения достаточно в нужную ячейку таблицы ввести первое значение из списка (в примере Иванов Антон) и протянуть маркер заполнения ячейки в нужном направлении. Смотрите подробнее Как пользоваться списками автозаполнения и вводить стандартные последовательности.
Что делать, если нет маркера автозаполнения?
Если маркер (курсор) заполнения отсутствует, то нужно настроить Excel так, чтобы маркер отображался.
Для этого, если Вы используете версию 2003, выбираем Сервис — Параметры — на вкладке Параметры устанавливаем галочку Перетаскивание ячеек.
Если Вы используете версию 2007 или 2010, Файл (кнопка Офис) — Параметры — Дополнительно — Разрешить маркеры заполнения и перетаскивания ячеек — ОК.
Ввод данных экспресс-методом
Если таблица содержит в нескольких ячейках одинаковые данные, для быстрого ввода этих данных можно использовать экспресс-метод.
Используя клавишу Ctrl, выделим ячейки, в которые нужно ввести одинаковые значения.
В строку формул введем нужное значение и нажмем на клавиатуре сочетание клавиш Ctrl+Enter. Все выделенные ячейки автоматически заполнятся нужными данными.
Кратко об авторе:
Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ "СОШ", с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Многие из нас сталкиваются с тем, что учащимся, их родителям нужно выдать большое число грамот, благодарственных писем и т.п.
Как правило, это отнимает у учителя много времени.
Алгоритм таких действий обычно у всех примерно одинаковый:
- Создаем шаблон грамоты
- Методом копирования и вставки заполняем шаблон на одного ученика из списка
- Печатаем грамоту
- И повторяем пункты 2-3 для всех учащихся из списка.
Все это занимает довольно много времени. Вероятность испортить красивые бланки довольно большая.
Для грамоты на одного учащегося нужно скопировать и вставить примерно четыре поля, каждый раз переходя из одного окна в другое или добирать данные вручную.
Пример фрагмента шаблона грамоты (красным цветом выделены поля, которые нужно заполнять для каждого учащегося).
Данный процесс можно ускорить если для заполнения шаблона использовать инструмент «слияние», который расположен на вкладке «Рассылки» текстового редактора MS Word.
Рассмотрим процесс создание грамоты помощью инструмента «Слияние» более подробно.
Для создания данного шаблона нам необходимо выполнить следующие действия:
- Создать шаблон грамоты в MS Word
- Создать таблицу с данными на каждого учащегося в MS Excel
- Выполнить процесс слияния
- Настроить поля
- Вывести грамоты на печать
Процесс создания шаблона я описывать не буду, так как шаблон вы можете взять любой и подготовить его самостоятельно таким каким он вам нужен.
Рассмотрим следующие действия на примере шаблона грамоты, представленного на первом рисунке.
Красным цветом выделены поля, которые необходимо вводить для каждого учащегося.
Для автоматизации процесса заполнения мы создадим в программе MS Excel таблицу с данными (такую таблицу лучше заранее раздать учителям которые формируют списки учащихся для выдачи грамот, чтобы они заполнили ее и тогда вам останется только настроить поля при слиянии документов).
При создании таблице в первой сроке указываем названия полей
После заполнения таблицы, ее необходимо обязательно закрыть, дать название.
Когда шаблон и список готовы, переходите к процессу слияния. Для этого открываете шаблон в программе Ms Word и переходите в меню «Рассылки»
Нажимаем на стрелочку справа от надписи «Начать слияние» и выбираем пункт «Пошаговый мастер слияния»
В правой части вашего документа откроется окно «Слияние»
Необходимо нажать на ссылку «Этап 1 из 6», затем на ссылку «Этап 2 из 6».
После выполнения этих действий вы окажетесь на закладке «Этап 3 из 6». Далее нажимаете кнопку «Обзор» и выбираете файл с таблицей с данными для заполнения шаблона.
Третий этап мастера «Слияние» пошаговая инструкция.
После того как вы открыли файл с данными, приступайте к расстановке полей в шаблоне. Для этого выделяем поочередно поля которые выделены были в шаблоне красным цветом.
Вставка полей в шаблон.
После того, как вы вставите в шаблон все поля, документ примет вид, представленный на рисунке.
Вид документа после вставки полей.
Теперь нажимаем на кнопку Просмотреть результаты и с помощью кнопок навигации просматриваем грамоты
Вид документа после включения режима «Просмотреть результаты».
Для печати всех грамот одновременно или заданного диапазона необходимо нажать на кнопку «Найти и объединить» и выбрать пункт «Печать документов». В появившемся окне выбираем вариант действий, например выведем на печать диапазон грамот с 1 по 3
Выводим данные на печать
Указываем диапазон для вывода на печать
Данным шаблоном можно пользоваться многократно.
Только помните, что при каждом открытии программа будет запрашивать у вас выполнить запрос к данным, на запрос необходимо ответить утвердительно.
Запрос на доступ к данным из таблицы MS Excel
Надеюсь данный материал поможет вам сократить временные затраты при работе с подобными документами.
Читайте также: