Как сделать квитанцию в excel
Microsoft Excel
трюки • приёмы • решения
Автоматизация заполнения бланка квитанции при помощи макросов Excel
В заключительной части этой темы мы разберем интересную практическую ситуацию, связанную с выпиской квитанции на оплату. Начнем работу с создания новой книги и один из листов оформим в виде, представленном на рис. 3.33. Это будет бланк квитанции об оплате. Здесь используется форматирование ячеек и размещение на листе дополнительных фигур (линий).
Рис. 3.33. Бланк квитанции
Далее наша цель заключается в обеспечении автоматизации при заполнении фрагментов квитанции, выделенных линиями. Для определенности будем считать, что выписываются квитанции для внесения оплаты за обучение (хотя какого-то принципиального значения тематика рассматриваемого примера не имеет). При оплате за обучение возможна ситуация, когда плательщик и сам учащийся — разные люди. Например, обучение оплачивает родственник. И это мы в нашей разработке должны предусмотреть. Также в квитанции сумму необходимо заполнить как цифрами, так и прописью. Поэтому потребуется обеспечить перевод числа в пропись.
Создадим еще один лист в нашей книге, который назовем Управление (рис. 3.34). Здесь, начиная с восьмой строки, располагается справочная информация об учащихся и плательщиках. Элемент управления «Поле со списком» заполняется фамилиями учащихся при открытии книги. Для этого нам понадобится оформить соответствующим образом процедуру (листинг 3.33), выполняемую при открытии книги.
‘ Листинг 3.33. Процедура, выполняемая при открытии книги Private Sub Workbook_Open() ‘ Подсчет числа учащихся N = 0 While Worksheets(1).Cells(N + 8, 1).Value <> «» N = N + 1 Wend ‘ Заполнение списка учащихся Worksheets(1).Spk.Clear For i = 1 To N Worksheets(1).Spk.AddItems(1).Cells(i + 7, 1).Value Next End Sub
Рис. 3.34. Лист Управление
Здесь предполагается, что лист Управление располагается на первом месте среди листов книги. Также для поля со списком подобрано Spk в качестве значения свойства Name.
Теперь пользователь, после выбора щелчком в поле со списком интересующего его студента, должен автоматически получить в ячейке, расположенной ниже заголовка Плательщик, фамилию плательщика (как уже говорилось, это может быть либо сам студент, либо один из его родственников). После этого необходимо внести сумму для квитанции и дату. Эта информация вводится в третий и четвертый столбцы ниже соответствующих подписей на листе.
Теперь важный технический момент. Для квитанции необходимо преобразовать числовое выражение суммы в строковое (представить прописью). Разумеется, необходима функция, которая это делает. Среди стандартных функций Microsoft Excel такой нет, и следует воспользоваться дополнительным модулем.
Один из наиболее удобных вариантов решения данной проблемы выглядит следующим образом. В любой поисковой системе (например, Rambler или Yandex) следует набрать в строке поиска «пропись Excel». В результате вы получите несколько предложений. Рекомендуется остановиться на модуле d2w.xla. Этот файл представляет надстройку для Microsoft Excel. Необходимо распаковать содержимое загруженного архива и разместить файл d2w.xla в папке Program Files ► Microsoft Office ► Office12 ► XLSTART. После этого модуль будет автоматически подключаться при запуске Microsoft Excel.
‘ Листинг 3.34. Обработка щелчка на кнопке Заполнить Private Sub CommandButton1_Click() ‘ Информация о плательщике Worksheets(«Бланк»).Cells(10, 4).Value = Cells(2, 2).Value Worksheets(«Бланк»).Cells(26, 4).Value = Cells(2, 2).Value ‘ Информация об учащемся Worksheets(«Бланк»).Cells(11, 3).Value = Spk.Text Worksheets(«Бланк»).Cells(27, 3).Value = Spk.Text ‘ Информация о сумме Worksheets(«Бланк»).Cells(12, 4).Value = Cells(2, 3).Value Worksheets(«Бланк»).Cells(28, 4).Value = Cells(2, 3).Value ‘ Информация о сумме прописью Worksheets(«Бланк»).Cells(13, 3).Value = Cells(3, 3).Value Worksheets(«Бланк»).Cells(29, 3).Value = Cells(3, 3).Value ‘ Дата Worksheets(«Бланк»).Cells(15, 7).Value = Cells(2, 4).Value Worksheets(«Бланк»).Cells(31, 7).Value = Cells(2, 4).Value End Sub
Результат заполнения бланка квитанции продемонстрирован на рис. 3.35.
Table of Contents:
Используя Excel 2013, создайте квитанцию, которую вы можете легко настроить для каждой продажи. Использование формул в квитанции позволяет вводить цены на проданные товары без необходимости извлекать калькулятор. Затем вы можете распечатать квитанцию или предложение отправить ее по электронной почте вашему клиенту.
Создание квитанции в основном наряжается, поэтому она признается квитанцией от вашего бизнеса, включая налог и общую стоимость.
Создание собственной квитанции
Макет страницы и форматирование
кредит: Скриншот любезно предоставлен Microsoft.
Откройте пустую книгу в Excel. Нажмите на Посмотреть вкладку, а затем нажмите Макет страницы просматривать на ленте, чтобы вы могли легко видеть, где границы страницы для бумаги размером с букву. Чтобы использовать для печати бумагу другого размера, измените ее, используя Размер варианты под Макет страницы ленты.
кредит: Скриншот любезно предоставлен Microsoft.
Введите название вашей компании, адрес и номер телефона справа. Слева введите Чек и номер квитанции, такой как 0001, Чтобы убедиться, что нули отображаются, нажмите Главная вкладка и изменить Число меню для Текст для ячейки номера чека. Чтобы изменить размер текста и шрифт, выделите ячейки и используйте Главная опции меню. Чтобы включить текущую дату и время, которые меняются автоматически для каждой квитанции, введите = NOW () в ячейке даты.
кредит: Скриншот любезно предоставлен Microsoft.
Введите заголовки столбцов под деловой информацией, включая Вещь, Единицы, Цена, скидка а также Всего, Выделите ячейки заголовка столбца, переместив курсор над ними, и добавьте двойную линию под ними, нажав Главная вкладка и выбор Двойная нижняя граница от бордюр значок. Нажмите на заполнить значок и выберите светлый цвет, как светло-серый. Выделите раздел бизнес-информации и используйте тот же цвет заливки для этих ячеек.
кредит: Скриншот любезно предоставлен Microsoft.
Прокрутите страницу вниз. Во втором столбце слева введите Промежуточный итог, налог а также Всего в колонке. Выделите эти строки и используйте тот же цвет заливки, который вы использовали в верхней части страницы. Использовать Двойная нижняя граница под Промежуточный итог а также Всего клетки. Использовать Тонкая одиночная граница на налог клетки.
кредит: Скриншот любезно предоставлен Microsoft.
Разделите каждый раздел квитанции с помощью Box Border от бордюр значок в Главная ленты. Для группы ячеек слева от разделов Итого и Итого добавьте любую дополнительную информацию, которую вы хотите включить в квитанцию, или используйте ее для заметок, как показано на скриншоте.
Выделите все пустые ячейки в столбцах Цена, Скидка и Всего. Нажмите на Главная вкладка и изменить Число меню от общего до валюта.
Формулы
Эта квитанция была сокращена в иллюстративных целях. Кредит: Скриншот любезно предоставлен Microsoft.
Выберите Промежуточный итог клетка. Нажмите на Формулы вкладку, а затем нажмите AutoSum значок. Перетащите курсор на все Всего клетки и пресса Войти, Всякий раз, когда вы добавляете значение в эти ячейки, они добавляются в Промежуточный итог.
кредит: Скриншот любезно предоставлен Microsoft.
Выберите налог ячейка, которая будет умножать промежуточную сумму на вашу ставку местного налога. Введите = и нажмите Промежуточный итог клетка. Введите * а затем процент налога в виде десятичной дроби. В приведенном выше примере мы используем 8-процентную ставку налога на ячейку Промежуточный итог. I20так что формула =120*.8, Нажмите Войти.
кредит: Скриншот любезно предоставлен Microsoft.
Нажмите на Всего ячейку, а затем нажмите AutoSum значок в Главная лента снова. Выделить Промежуточный итог а также налог клетки и пресса Войти, Общая сумма добавляет налог к промежуточной сумме.
кредит: Скриншот любезно предоставлен Microsoft.
Сделайте так, чтобы рабочий лист Excel оставлял все ячейки с нулевым значением пустыми, иначе в квитанции скоро будет много записей по 0,00 долларов. Для этого нажмите файл выберите вкладку Опции а потом продвинутый, Прокрутите вниз, а затем снимите флажок рядом с Показать ноль в ячейках, которые имеют нулевую ценность, Нажмите Хорошо.
кредит: Скриншот любезно предоставлен Microsoft.
Выберите первый пустой Всего клетка. Эта ячейка должна умножить единицы на цену, а затем вычесть любую введенную скидку. Введите = и нажмите Единицы клетка, тип * чтобы умножить, нажмите Цена клетка, тип — вычесть, а затем нажмите скидка клетка. В нашем примере, если скриншот не ясен, формула = F8 * G8-H8, Нажмите Войти.
Скопируйте эту формулу, выделив эту ячейку и нажав Ctrl-C,, Вставьте его во все ячейки пустого элемента под ним, выделив их и нажав Ctrl-V,.
кредит: Скриншот любезно предоставлен Microsoft.
Протестируйте квитанцию, введя данные в ячейки, используя элементы по 10 долларов, чтобы вы могли легко увидеть, правильны ли итоги по пунктам, подытог, налог и итоговая сумма. Предполагая, что они верны, сохраните файл квитанции.
Использование шаблона квитанции
кредит: Скриншот любезно предоставлен Microsoft.
Откройте Excel или нажмите файл вкладка, если она уже открыта. Введите Квитанцию в Поиск поле. Нажмите квитанцию, чтобы увидеть предварительный просмотр и нажмите Создайте кнопку, чтобы открыть его.
кредит: Скриншот любезно предоставлен Microsoft.
Замените все поля деловой информации своими собственными данными. Если в квитанции есть место для логотипа вашего бизнеса, выберите Заполнитель образ. Нажмите на Формат инструментов изображения вкладку, а затем нажмите Заменить изображение значок. Выберите ваше изображение в меню навигации, и оно автоматически изменится в соответствии с шаблоном.
кредит: Скриншот любезно предоставлен Microsoft.
Как внедрить в excel формирование QR кода на квитанциях
Частой задачей для УК, ТСЖ, ТСН, ООО или ИП оказывающих коммунальные услуги, услуги связи, домофона или парковки является формирование квитанций для сбора платежей. Современные технологии позволяют принимать платежи по QR коду с автоматическим заполнением реквизитов. Но зачастую начисления и квитанции готовятся в самом excel. Квитанции уже привычны плательщикам и бухгалтеру и стоит задача внедрить в уже существующие квитанции и начисления QR код для оплаты через Сбербанк-Онлайн, Тинькофф, ВТБ, Альфа и другие банка.
На примере диалога между заказчиком и разработчиком ниже рассмотрим один из вариантов решения этой проблемы. А также приведём пример работы excel с макросом, который формирует QR код.
Заказчик:
Нашей организации нужно сделать автоматическое формирование QR кода для оплаты коммунальных услуг. Ведём начисления в excel. Формат квитанции мы не хотим менять, так как он уже привычен жильцам и содержит все нужные данные. Можете предложить решение?
Разработчик:
Да, можно такое сделать. Для этого разрабатывается VBA макрос для excel.
Если можете пришлите, пожалуйста, пример начислений в файле excel, по которым хотите формировать код. Я посмотрю, как лучше автоматизировать и сориентирую по цене и срокам реализации.
Заказчик:
Мне нужно именно формирование квитанции целиком, вместе с QR кодом и данными в ней.
Разработчик:
Да, можно по начислениям сделать формирование квитанций для каждой строки. Каждая строка начислений будет соответствовать отдельной квитанции с QR кодом для оплаты.
Заказчик:
Еще вопрос по макросу, правильно я понимаю, что в дальнейшем мы просто меняем данные за месяц и автоматически формируются все квитанции?
Разработчик:
Да, верно.
Заказчик
В каком формате формируются квитанции? пдф, эксель? jpeg?
Разработчик:
Excel. Его потом можно при необходимости сохранить в pdf.
Заказчик
Также может у вас реализована возможность начисления пеней в квитанции?
Разработчик:
Пени обычно считает сам поставщик услуги, если это поле есть в отдельной колонке в excel, то можно его добавить на квитанцию. В QR коде тег под пеню есть, но его никто не использует. Обычно пеню добавляют и считают в «сумму итого» к оплате, которую и помещают в QR код.
Заказчик:
Можно ли будет менять формат квитанции? Например, добавить раздел или изменить форматирование? Можно будет менять формат строк начислений?
Разработчик:
Да, формат квитанции можно будет менять. Но стоит учитывать, что позиция на которую будет помещаться QR код фиксированная, это место лучше оставлять без изменения. Остальное форматирование можно будет менять.
Насчёт начислений колонки в начислениях из которых берутся ключевые реквизиты ( ФИО, Лицевой счёт, Адрес, Сумма) макрос их отслеживает, они заданы через параметры. При крайней необходимости можно будет поменять.
Заказчик:
Что у нас останется в итоге после вашей работы?
Разработчик:
В результате работы у Вас будет привычный файл excel с вашими начислениями. Всё полностью сохраниться, будет добавлена одна кнопка «Штрихкодировать» с макросом, при нажатие на которую по начислениям будут сформированы квитанции и сгенерированы QR коды. Макросом можно будет пользоваться сколько угодно раз. Оплата за разработку единоразовая, а использовать можно будет бесконечно.
Заказчик:
Потребуется ли Ваша поддержка для этого макроса в дальнейшем? Что если мы отроем счёт в другом банке и реквизиты изменяться?
Разработчик:
Поддержка не потребуется. Будет инструкция, в которой будет всё описано. Вот пример.
В случае добавления новых счетов изменения старых — всё сможете сделать самостоятельно. Также если поменяются реквизиты организации, например смените расчётный счёт, то тоже сможете сделать самостоятельно.
Если Вам не принципиально старый формат и готовы попробовать новое решение, то может воспользоваться вэб-сервисом формирования группы квитанций. Это будет дешевле и можно будет попробовать бесплатно 1 месяц. Подробнее о группой подготовки квитанций с QR кодами можно прочитать здесь.
Как сделать квитанцию в excel
Инструмент Слияние позволяет отправить данные электронной таблицы MS Excel в документ MS Word.
В данной статье рассматривается вариант, когда на странице документа Word необходимо разместить данные из нескольких записей таблицы. По приведённому алгоритму можно формировать страницы для печати квитанций, талонов, визиток, карточек и других однотипных небольших по размеру документов
Рассмотрим пример. Предположим есть некий прайс мебельного магазина «Мир диванов».
Предположим также, что произошло изменение цен, и нужно распечатать новые ценники. На листе формата А4 можно разместить шесть ценников. Примерно так
Разберём процесс создания ценников по шагам. Предполагается, что база данных в Excel, на основе которой будут создаваться ценники, уже существует. Как подготовить файл Excel к слиянию, можно прочитать в этой статье. Если коротко, то таблица, предназначенная для слияния, должна удовлетворять некоторым требованиям:
Шаг 1. Разработка макета документа
На вкладке Рассылки под кнопкой Начать слияние выбираем вариант Наклейки. Раскрывается диалоговое окно Параметры наклеек, в котором можно выбрать макет или создать новый на основе уже имеющихся.
Выбираем наиболее подходящий макет и нажимаем кнопку Создать (или Настройка). Раскрывается диалоговое окно, в котором задаются количество, размеры и расположение будущих ценников. Для нашего примера были заданы значения, указанные на рисунке
Если шаг по вертикали и горизонтали равен соответственно высоте и ширине наклейки, то они будут располагаться вплотную друг к другу. В нашем случае между наклейками будет зазор 5 мм.
Нажимаем ОК, возвращаемся в предыдущее окно и там тоже ОК. После этого на странице появляется разметка в соответствии с заданными параметрами. Строго говоря, разметка представляет собой таблицу, в ячейках которой и будут располагаться ценники.
Шаг 2. Выбор источника данных
Кроме того, после выбора источника под кнопкой Вставить поле слияния на вкладке Рассылки появляется список полей таблицы-источника записей.
Шаг 3. Форматирование ценника
Этот шаг является самым долгим, так как именно здесь мы формируем внешний вид будущего ценника. Так как содержимое автоматически не распространяется сразу на все ячейки таблицы, рекомендуется полностью отформатировать ценник в одной ячейке, а затем скопировать в остальные. Чтобы просматривать внешний вид ценника в процессе работы, следует использовать кнопку Просмотреть результаты на вкладке Рассылки.
Набираем с клавиатуры текст, который будет присутствовать во всех ценниках, в нужных местах вставляем поля слияния с помощью кнопки Вставить поле слияния, форматируем ценник, при необходимости редактируем коды полей слияния. Например, в нашем документе потребуется доработать коды полей Дата и Цена.
После того как желаемый результат достигнут, копируем содержимое первой ячейки в другие ячейки таблицы, не удаляя поле NEXT.
Шаг 4. Формирование окончательного файла с ценниками для печати
На последнем шаге под кнопкой Найти и объединить выбираем вариант либо Печать документов, и тогда все листы сразу отправляются на принтер, либо Изменить отдельные документы, и тогда создаётся новый документ Word, в котором можно увидеть и при необходимости отредактировать заполненные ценники
В заключение можно добавить, что те же шаги можно выполнить с помощью Мастера слияния, который запускается из-под кнопки Начать слияние.