Оплаты в excel. Реестр учета платежей в excel
Деятельность любой организации неразрывно связана с входящим и исходящим денежными потоками. Своевременное исполнение финансовых обязательств по оплате счетов непосредственно влияет на уровень доверия и имидж организации на рынке товаров и услуг. Контроль расходов необходим для эффективного управления бизнесом, сокращения издержек, расчета баланса организации. Автоматизированный реестр платежей позволяет без труда завести оплаты в excel и создать учет как постоянных статей расходов (ежемесячных), так и переменных (разовых). Ведение умного учета оплат в excel позволит значительно снизить вероятность пользовательских ошибок ввода данных, а также позволит более тщательно контролировать дату исполнения платежа. Далее будут подробно описаны основные функции и возможности автоматизированного реестра платежей в excel.
Ссылки для скачивания:
— Скачать реестр платежей в excel — оплаты в excel
Как начать работу с файлом оплаты в excel
1. Разархивируйте скачанный архив с реестром платежей с помощью 7zip или WinRar.
2. При появлении сообщения о доверенном источнике: закройте программу, кликните правой кнопкой мыши на файле — «Свойства», далее установите галочку напротив «Разблокировать».
3. Если в Вашем Excel запуск макросов по умолчанию отключен, в данном окне необходимо нажать «Включить содержимое».
4. Для полноценной работы с файлом необходимо нажать «Разрешить редактирование» при появлении данного сообщения.
Как завести оплаты в excel и заполнить справочники реестра платежей
Первую настройку макроса оплаты в excel необходимо начать с заполнения справочников, на основании которых будут формироваться дальнейшие варианты полей выбора. Таким образом, остановимся подробнее на редактировании листа «Справочники».
В первой колонке необходимо, по возможности, указать все основные статьи оплат, которые совершает организация в процессе ведения своей деятельности. Далее идет справочник контрагентов, с которыми осуществляется взаимодействие. В столбце «Плательщик» указываем наименование юр. лица (или юр. лиц), от которого будут совершаться платежи. В колонке «Тип платежа» вводим любую внутреннюю классификацию оплат (для примера платежи разделены на обязательные и необязательные). «Платим» — столбец окончательного решения по платежу (по умолчанию стоит 0 или 1). «Статус платежа» может быть заполнен в соответствии с внутренними процессами согласования и проведения выплат в организации. Справочники могут быть отредактированы в дальнейшем, при появлении новых данных.
Важно! Не удаляйте значение Все из второй строки каждой колонки справочника!
Далее переходим к заполнению повторяющихся (ежемесячных, ежеквартальных и т.д.) платежей. Основа, которая является постоянной и переходит из одного периода в другой.
Вносим сумму, выбираем контрагента из выпадающего списка (редактирование на листе Справочник). Таким же образом выбираем юр. лицо плательщика, тип платежа, вводим наименование товара или услуги. Указываем предельный срок оплаты. Дата выбирается с помощью встроенного календаря, вызов которого происходит по нажатию на ячейке правой кнопкой мыши. Заполняем номер счета и номер договора. Осуществляем выбор статьи расхода из выпадающего списка (также редактируется в справочнике). В завершении вводим через календарь дату выставления счета (если известна к моменту заполнения). В верхней части таблицы для наглядности показаны суммы оплат с помощью стандартных формул excel. Итоги считаются по первым двум введенным плательщикам.
Когда оплаты введены в excel, справочники инициализированы, можно переходить на лист «Планирование» для построения автоматизированного реестра платежей на месяц.
В левую часть листа автоматически подтягиваются статьи оплат из справочника реестра платежей в excel. Здесь можно указать плановые месячные значения по каждому из пунктов. Выбираем год и месяц целевого учета, указываем масштаб листа реестра, нажимаем кнопку «Создать реестр на месяц».
Работа с реестром платежей в excel
В результате работы макроса создается месячный лист реестра платежей, в который переносятся все постоянные оплаты с листа «Ежемесячные платежи».
Для того чтобы добавить в реестр очередную оплату необходимо воспользоваться кнопкой «Добавить платеж». Новая строка появится сразу под заголовком таблицы.
Функция «Сводка за день» соберет для вас в отдельном отчете все платежи за указанное число, которое совпадает с датой в колонке «Дата оплаты». Напомню, что для заполнения дат необходимо использовать встроенный календарь, который вызывается кликом по правой кнопке мыши на соответствующей ячейке ввода даты.
«Сводка по статьям» создаст сводный лист в разрезе статей оплат. Сравнит полученные данные с плановыми показателями с листа «Планирование». И покажет отклонение от текущего плана.
В левом верхнем углу таблицы реализован блок фильтров, который позволяет сгруппировать нужные данные для упрощения поиска информации по оплатам. По умолчанию при создании в фильтре указано значение «Все». Выбранная комбинация параметров отражается в колонке «Фильтр», которая принимает значение Истина или Ложь. Формулы в данном столбце ручному редактированию не подлежат!
Заказать разработку программы или доработку реестра платежей в excel
Благодарю Вас за ознакомление с данной инструкцией!
Если мой продукт вам оказался полезен, но Вы хотели бы доработать функционал или донастроить его под логику ведения учета Вашей компании, или заказать любую иную расчетную программу/макрос, проконсультироваться по возможности автоматизации процессов своего бизнеса, связаться со мной можно через WatsApp +79507094770, сайт excellab.ru или написать мне на почту: goryaninov@bk.ru
Ссылки для скачивания:
— Скачать реестр платежей в excel — оплаты в excel
Предлагаю Вашему вниманию другие полезные программы по учету и автоматизации, разработанные мной и описанные в следующих статьях:
— Дневной табель учета рабочего времени в excel
— Почасовой табель учета рабочего времени в excel
— Табель учета рабочего времени в днях по форме Т-13
— Табель расчет и планирование вахты
— Табель учета рабочего времени с учетом ночных смен
— Учет доходов и расходов
— Автоматизированное формирование документов
— Учет выдачи инструментов
— Учет производства. Расчет сдельной заработной платы
Журнал учета счетов за продажи
Этот шаблон журнала учета счетов за продажи хранит много больше информации, чем обычный счет. Журнал учета позволяет вести историю клиентов, счетов и деталей счетов, чтобы хранить несколько счетов, не создавая дополнительных файлов. Этот шаблон журнала учета счетов с поддержкой специальных возможностей годится также для анализа данных ранее выставленных счетов.
Excel
Скачать
Открыть в браузере
Поделиться
|
Таблица контроля оплаты счетов |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Здравствуйте, друзья сайта itswat.ru. Людям определённых профессий приходится работать с большим количеством всевозможных документов – внутренних, входящих, исходящих и других. В крупных организациях, где число документов гигантское, система их учёта чётко отлажена. Для создания электронных реестров используются профессиональные программы, лицензия на которые стоит прилично. Но что делать небольшой, только родившейся и ещё не раскрученной компании, или скромному предпринимателю? Документов не слишком много, но они есть и все очень важные. Их потеря может выйти боком, а платить ежемесячно за профессиональное ПО – лишние и пока неуместные траты. Мой ответ – используйте Excel и с помощью нескольких функций систематизируйте и учитывайте свои «важные бумажки». Итак, тема данного материала — как создать реестр документов в Excel. Дело не сложное, но муторное и с множеством нюансов, поэтому расскажу пошагово с картинками.
Содержание
1. Что можно поместить в реестр?
2. Пошаговая инструкция
2.1. Папка
2.2. Название
2.3. Документ
Что можно поместить в реестр?
Реестр в Excel можно создать для чего угодно, например, для договоров с партнёрами, клиентами или подрядчиками. Ниже вы можете увидеть картинку – шаблон таблицы с заголовками столбцов, отражающими данные, которые необходимо учесть.
Вы можете сделать также реестр платежей. Тоже прикреплю фото примера, может, кому-нибудь пригодится.
Я же буду создавать реестр текстовых документов, содержащих описания товаров, которые я когда-то составлял по просьбе одной бизнесвумен, по чистой случайности, завалявшиеся на моём ноутбуке. Делаю я это не по необходимости, а для того, чтобы наглядно продемонстрировать вам весь процесс. Это и будет пошаговая инструкция. Уяснив принципы, вы легко сможете адаптировать её под себя, изменив шапку таблицы и выбрав только те действия, которые понадобятся именно вам. Я использую Excel 2007, но версия не принципиальна. Вы можете работать в Excel 2010 или более поздних годов выпуска, ничего не потеряете, так как во всех редакциях используемые мной функции также присутствуют, а разделы меню и команды находятся там же, где и раньше.
Пошаговая инструкция
Друзья, вот план того, что нам предстоит сделать для создания удобного и функционального реестра документов:
- Создаём новый документ, задаём ему имя и сохраняем как «Книга с поддержкой макросов».
- Выделяем примерную область значений, выбираем раздел «Вставка», элемент «Таблица», подтверждаем наличие заголовков.
- Заполняем названия заголовков. В моём случае это «Номер», «Заказчик», «Папка», «Название», «Документ» и «Директория».
- Приступаем к заполнению столбцов.
- Столбец «Номер» заполняется просто. Необходимо ввести в первую строку значение 1, а во вторую – 2, выделить обе заполненные ячейки, зацепиться за маленький чёрный квадратик в правом нижнем углу выделенной области и протащить его вниз на нужное количество строк. В нижних ячейках автоматически проставятся значения от 1 до …(зависит от нас).
- Столбец «Заказчик» можно заполнить вручную или способом, описанным для столбца «Название» при условии, что имена заказчиков – это по совместительству названия папок в общей директории.
- В столбце «Папка» я хочу видеть адрес папки, в которой располагается конкретный документ и при этом сделать его ссылкой, при нажатии на которую Excel будет перебрасывать меня в эту папку. Для этого я воспользуюсь функцией «ГИПЕРССЫЛКА». Подробнее опишу ниже.
- В столбце «Название» мне нужно отобразить имена документов, но вводить их все вручную очень долго. В Excel существует небольшая хитрость, позволяющая в разы ускорить процесс и автоматически заполнить ячейки названиями всех документов, находящихся в указанной папке. Это возможно благодаря функции «Файлы», ищите алгоритм чуть ниже.
- Столбец «Документ» будет содержать рабочую ссылку на нужный файл, нажатие на которую спровоцирует его открытие. Как это сделать, ищите чуть ниже.
- Столбец «Директория» — это всё тот же адрес папки, но без гиперссылки. Он нужен для того, чтобы заполнить предыдущие столбцы.
Папка
Чтобы получить активный адрес папки, где расположен нужный документ, воспользуемся функцией «Гиперссылка»:
- Ставим курсор в первую ячейку соответствующего столбца.
- Копируем адрес нужной папки.
- Вставляем его сразу в «Директорию», он пригодится в дальнейшем.
- Нажимаем в строке значений на fx и находим нужную функцию или вписываем вручную =ГИПЕРССЫЛКА(«адрес папки»).
- Ещё один способ, на мой взгляд, самый удобный – щёлкаем в нужной ячейке ПКМ (правой кнопкой мыши), в меню выбираем команду «Гиперссылка», в открывшемся мастере отыскиваем нужную папку и подтверждаем действие.
- Так придётся делать для каждого документа, но если учесть, что большое количество их расположено в одной и той же папке, то процесс не должен отнять много времени. Для последующих документов, являющихся соседями первого по директории, адрес можно просто скопировать.
Название
С названием немного сложнее:
- Добавляем к адресу, помещённому нами в «Директорию» ещё один обратный слеш и звёздочку, то есть *.
- Выбираем раздел меню «Формулы», функцию «Диспетчер имён».
- А в её мастере команду «Создать».
- Задаём имя, в моём случае это будет «Название».
- В строке «Диапазон» пишем =ФАЙЛЫ( и щёлкаем курсором по соответствующей ячейке в столбце «Директория», закрываем скобку).
- Нажимаем ОК и «Закрыть».
- Ставим курсор в первую ячейку столбца.
- Вписываем туда формулу =ИНДЕКС(Название;СТРОКА()-1), где Название – это имя, заданное нами на четвёртом шаге данного алгоритма, а -1 обозначает, что данная ячейка стоит второй в столбце.
- Нажимаем «Энтер» и видим, что в столбце появились имена всех файлов, расположенных в указанной папке.
- Таким методом можно поместить в реестр названия не всех файлов, а выборочно в зависимости от формата. Для этого предварительно в адресе папке, указываемом в столбце «Директория», после звёздочки следует написать расширение файла, например, *docx, xls или jpeg. Это актуально, когда в одной папке находятся вперемешку файлы разных форматов – таблицы, картинки, текстовые документы и любые другие.
Если что-то вам по моим картинкам и описанию не понятно, то посмотрите следующую подробную инструкцию в формате видео.
Документ
Друзья, столбец «Документ» содержит то же название, но являющееся ссылкой. Чтобы этого добиться, следует проделать все те же действия, что и в предыдущем алгоритме до шага 8. Мы их опустим, так как повторно делать одно и то же не нужно. Теперь немного усложняем формулу. Она примет вид =ГИПЕРССЫЛКА(ИНДЕКС(Название;СТРОКА()-1)). После её размещения и ввода строки заполнятся теми же названиями, но являющими собой активные ссылки.
Вот, кажется, и всё. Старался описать алгоритм наиболее подробно. Меня ещё недавно спросили, как создать реестр сведений о доходах физических лиц, то есть формировать справки 2-НДФЛ и вести их учёт в программе «Налогоплательщик ЮЛ». Тема, в общем, та же, но программа другая, поэтому я решил посвятить ей отдельный материал. До скорых встреч.
Читайте также:
От управления денежными потоками, обеспечения платежеспособности и ликвидности зависит эффективность бизнеса. Особенно сложно управлять денежными потоками не в отдельной компании, а в группе компаний. Для решения этой задачи финансовой службе группы компаний рекомендуем создать Excel-модель для управления реестром платежей. Порядок создания такой модели подробно рассмотрим в статье.
ЗАДАЧИ ФИНАНСОВОЙ СЛУЖБЫ ПРИ РАБОТЕ С ИСХОДЯЩИМ ДЕНЕЖНЫМ ПОТОКОМ
Работая с исходящим денежным потоком, финансовая служба должна решать следующие задачи:
• вести расчетно-кассовые операции компаний группы;
• собирать заявки на платежи от центров финансовой ответственности (ЦФО) группы компаний и контролировать их исполнение;
• составлять реестры счетов на оплату, формировать и согласовывать график платежей;
• формировать платежный календарь и управлять им;
• составлять отчетность (в том числе консолидированную) по исполнению заявок, договоров с контрагентами, бюджетов движения денежных средств;
• автоматизировать рутинные и повторяющиеся процедуры и функции в части подготовки и согласования заявок на расходование денежных средств, формирования отчетности и др.
Чтобы успешно решить поставленные задачи, следует организовать учет в Excel-модели. Для этого работу всех финансовых специалистов переводят в единое информационное пространство, организация которого начинается с создания справочников и классификаторов.
СПРАВОЧНИКИ И КЛАССИФИКАТОРЫ
Чтобы стандартизировать учет и контроль, обеспечить сопоставимость показателей, сократить время на подготовку реестра платежей и отчетности, формирование Excel-модели начинают с создания справочников и классификаторов. Заполняют их максимально точно и подробно.
Небольшие классификаторы целесообразно разместить на одном листе Excel (табл. 1–5). Регламентируют, что заявки на платежи имеют право подавать только подразделения, за которыми закреплен статус ЦФО (табл. 1). Это отдел закупок и логистики, отдел охраны труда и др.
Приоритетность заявок устанавливает специалист финансовой службы согласно внутренним положениям (табл. 2). Например, к заявкам с высоким приоритетом относят платежи по кредитам или закупку основных материалов. Статус заявки позволит одновременно визуализировать суммы, уже оплаченные контрагенту, и суммы, взятые на учет к оплате, но на текущую дату не оплаченные (табл. 3).
Чтобы разделять платежи компаниям группы и внешним контрагентам, понадобится табл. 4. Для календарного планирования и отчетности нужна табл. 5.
Все классификаторы отформатированы как умные таблицы: вкладка Главная → Стили → Форматировать как таблицу. Им присвоены соответствующие имена. Посмотреть имя таблицы можно на вкладке Конструктор → Свойства → Имя таблицы.
Справочник статей движения денежных средств (табл. 6) позволит:
- сгруппировать заявки на платежи в эффективные отчеты;
- установить приоритеты при принятии решения о дате фактической оплаты в зависимости от приоритета расходов.
На этапе проработки справочников продумывают конечный результат — составление отчетов. Чтобы в дальнейшем формировать отчеты с помощью сводных таблиц, здесь и в других таблицах Excel-модели применяем «сцепки»: =A2&»_»&B2 (графа «Код + Статья») (см. табл. 6).
|
Таблица 6. Справочник статей движения денежных средств (фрагмент) |
||
|
Код |
Статья ДДС |
Код + Статья |
|
10000 |
Операционная деятельность |
10000 Операционная деятельность |
|
11000 |
Выплаты по операционной деятельности |
11000 Выплаты по операционной деятельности |
|
11001 |
Материалы для основной деятельности |
11001 Материалы для основной деятельности |
|
11002 |
Энергоресурсы |
11002 Энергоресурсы |
|
11003 |
Запчасти, стройматериалы |
11003 Запчасти, стройматериалы |
|
11004 |
Транспортные услуги |
11004 Транспортные услуги |
|
11005 |
Мебель, компьютерная техника, оснащение помещений |
11005 Мебель, компьютерная техника, оснащение помещений |
|
11006 |
Оплата труда |
11006 Оплата труда |
|
… |
||
|
11015 |
Прочие операционные расходы |
11015 Прочие операционные расходы |
|
30000 |
Финансовая деятельность |
30000 Финансовая деятельность |
|
31000 |
Платежи по финансовой деятельности |
31000 Платежи по финансовой деятельности |
|
31001 |
Перечисление средств в депозиты |
31001 Перечисление средств в депозиты |
|
31002 |
Погашение банковских кредитов |
31002 Погашение банковских кредитов |
|
31003 |
Погашение займов |
31003 Погашение займов |
|
31004 |
Прочие выплаты по финансовой деятельности |
31004 Прочие выплаты по финансовой деятельности |
Справочник проектов (табл. 7) сформируем идентично. Учет не должен быть избыточным. Нет смысла все платежи привязывать к проектам, поэтому вносим строку «без проекта» (пустые ячейки в Excel-модели недопустимы).
|
Таблица 7. Справочник проектов |
||
|
№ |
Наименование проекта |
№ + наименование |
|
– |
без проекта |
– без проекта |
|
101 |
Ремонт системы электроснабжения |
101 Ремонт системы электроснабжения |
|
102 |
Модернизация цеха № 2 |
102 Модернизация цеха № 2 |
|
201 |
Открытие брендового магазина № 1 |
201 Открытие брендового магазина № 1 |
|
202 |
Открытие брендового магазина № 2 |
202 Открытие брендового магазина № 2 |
|
203 |
Открытие брендового магазина № 3 |
203 Открытие брендового магазина № 3 |
|
301 |
Контрактное производство ООО «Меркурий» |
301 Контрактное производство ООО «Меркурий» |
|
302 |
Контрактное производство ООО «Спецодежда» |
302 Контрактное производство ООО «Спецодежда» |
Классификаторы (см. табл. 1–5) применяют для организации более сложного справочника контрагентов (рис. 1). Чтобы подключить выпадающий список, выполняют следующие действия: вкладка Данные → Работа с данными → Проверка данных. Выбирают Тип данных — Список, в поле «Источник» указывают: =ДВССЫЛ($B$1).
Указание в качестве источника ссылки позволяет в дальнейшем не подстраивать диапазон при расширении классификатора. Так, в справочнике контрагентов указано, что ИП Кузнецов Д. Б. — внешний контрагент, а ИП Михайлов А. Е. входит в группу компаний.
Обозначенный перечень справочников минимальный, при необходимости набор справочников расширяют. Например, можно сформировать справочник счетов бухгалтерского учета или договоров.
Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 8, 2021.
Платежи есть в любой компании. А когда бизнес набирает обороты — их становится больше, как и связанных с ними проблем.
Рассказываем, как навести порядок в платежах с помощью единого формуляра. С ним вы сможете собрать в одном месте документы на оплату, а затем быстро перенести их в реестр платежей, подготовить отчет БДДС и рассчитать платежный календарь. Все, что вам нужно, — хороший навык работы в Excel, знание нескольких «секретных» формул, последовательность действий и терпение.
Когда и кому будет полезен такой формуляр
Когда:
- Счета на оплату часто теряются
- Платежный календарь делается «на коленке» и не работает
- Отчет БДДС собирается долго и нудно
Кому:
- Финансовому менеджеру — даже если он единственный в компании, отлаженная работа по формуляру займет совсем немного времени.
- Финансистам из организаций, где создаются филиалы или планируется автоматизация. Формуляр — понятный способ сразу начать с порядка и протестировать разные варианты работы.
Как создать формуляр
Для начала нужен бюджетный классификатор (перечень статей БДДС): отдельный для поступлений, отдельный — для расходных платежей компании.
Бюджетный классификатор — это константа. Он свяжет все формы контроля и планирования денежных средств (БДДС, платежный календарь и реестр платежей). Если пока нет бюджетного классификатора, его можно собрать из стандартного набора статей. Даже 5 достаточно — пусть они не идеальны и не все учитывают, но начните с них. Остальные уточните в процессе работы.
Пример названий: оплата труда, налоги, связь и интернет, аренда и т. д.
Лайфхак. Просмотрите выписку за месяц, составьте общий перечень назначений платежей, затем удалите дубликаты по смыслу. Названия статей формулируйте кратко и помните — вы должны быстро и без сомнений идентифицировать по ним каждый платеж и не запутаться.
Перейдем к созданию формуляра. Последовательно рассмотрим все формы (каждая — на отдельном листе Excel).
Форма 1.1 Регистрация документов для оплаты
Здесь регулярно регистрируются все поступившие документы на оплату и графики платежей (погашение кредита, лизинговые и т. д.) Это залог порядка и гарантия, что ничего не потеряется и все будет оплачено вовремя.
Да, поначалу половину информации придется заполнять вручную по мере появления документов на оплату. Затем с предыдущего периода можно переносить неоплаченные и ежемесячные платежи (если сумма неизвестна, то проставлять ее потом), а новые платежи — по мере поступления документов.
Старайтесь делать форму регистрации в виде плоской таблицы. Нужно сохранить возможность фильтровать и сортировать информацию (см. рис. 1).
Каждый внесенный документ кодируется статьей БДДС. Для удобства кодировки используйте всплывающие списки в Excel. Важно, чтобы статьи БДДС во всех формах назывались одинаково, иначе данные будут переноситься некорректно.
В столбце K — «выбор на оплату» — ставится планируемая дата из конкретного договора или сроков внедоговорных обязательств (например, заработная плата, налоги). Реестр договоров со сроками синхронизируйте с этой формой. Если его нет — составляйте по мере поступления документов на регистрацию. После оплаты — дата и отметка «оплачено» (столбцы N и О).
Очень важный столбец в этой форме — технический (M). В нем формула I6&» «&N6 (для платежа «окончательный расчет за август 2020») позволяет сцепить статью БДДС и статус оплаты (или его отсутствие).
Возможны также рабочие пометки: «перенос» (для платежей, которые будут внесены в другом периоде), «корректировка» (платежи, остановленные до корректировки БДДС) или любая удобная для вас.
Лайфхак: левую часть этой таблицы сделайте близнецом Реестра на оплату. В нашем примере это столбцы от B до H (столбец I — по желанию). Далее фильтр по столбцу «выбор на оплату», простое CTRL+C и CTRL +V — и у вас заполненный реестр платежей. По нему вы быстро проверите и факт оплаты.
Форма 1.2 Регистрация документов для поступлений
Она идентична форме 1.1, но со своим классификатором (другой всплывающий список) и иным принципом работы с ней (см. рис. 2).
Ее можно заполнять аналогично форме с расходными платежами. Например, загрузить все выставленные на оплату счета и/или платежи по графику. Затем оставить только те, которые по условиям договоров будут оплачены в отчетном месяце.
Преимущество: у вас будет детальная расшифровка ожидаемых поступлений, но возможно много ежедневных корректировок по факту. Такой вариант подходит для стабильных поступлений или их небольшого количества.
Когда сложно спланировать поступления на конкретные даты, проще ежедневно загружать факт из выписки. Не стремитесь заполнить все столбцы (например, отдельно столбцы E-G — договор и документ-основание).
Преимущество: высокая скорость и простота обработки информации, но у вас не будет расшифровки ожидаемых поступлений. А она важна для платежного календаря. Этот вопрос нужно будет позже проработать отдельно.
Хотите получать дайджест статей?
Одно письмо с лучшими материалами за неделю. Подписывайтесь, чтобы ничего не упустить.
Спасибо за вашу подписку!
Лайфхак: если ваш вариант — загрузка выписок по факту, то удобна формула для обработки оплаты (на примере платежа от компании Дрим на сумму $1080 от 07.09.2020):
- ЕСЛИ(H7>0;»оплачено»). Как только в столбце H появляется сумма — автоматически проставляется статус «оплачено».
Следующие формы формуляра требуют минимального заполнения в процессе работы.
Форма 2. Отчет БДДС
Этот отчет вы теперь сможете быстро делать каждое утро после получения выписки. Например, вот так:
- сначала сверяете выписку с реестром платежей по остатку денег на конец дня
- затем в формах регистрации через фильтр по столбцу K находите все платежи и поступления, проставляете статус «оплачено» и дату оплаты
Рекомендуется делать это ежедневно. Так вы не только актуализируете информацию, но и оперативно проверите, все ли платежи прошли и нет ли потерявшихся после возвратов. От привычного отчета БДДС эту форму отличает только то, что данные стоят нарастающим итогом с начала месяца, а не каждый день отдельно (см. рис. 3).
Секрет отчета — в двух формулах:
- СУММЕСЛИ(‘Форма 1.1′!$M:$M;A24&» «;’Форма 1.1’!$H:$H)
Каждый заявленный на оплату документ идентифицируется со статьей БДДС на этапе регистрации.
- СУММЕСЛИ(‘Форма 1.1′!$M:$M;A24&» оплачено»;’Форма 1.1’!$H:$H)
Распределяет все документы из форм регистрации на оплаченные и заявленные, но не оплаченные.
Если у вас есть план БДДС, то появляется возможность проверить лимит БДДС сразу при регистрации документа. Например, можно в отчете предусмотреть столбец «статус корректировки» (столбец B) с проверочной формулой.
Формула ЕСЛИ((H23-I23)>=0;»OK»;»Корректировка») для статьи «годовая премия» проверяет, достаточно ли в БДДС запланировано средств для оплаты всех документов, зарегистрированных по этой статье. Если недостаточно, то высвечивается сообщение «Корректировка». В Excel есть опция «условное форматирование». Можно для визуального удобства все ячейки с сообщениями о корректировке выделять цветом.
Далее вы корректируете бюджет или принимаете решение о переносе платежа, отразив это в форме регистрации. Форма работает и при отсутствии плана БДДС.
Лайфхак 1. Можно через аналогичный алгоритм собирать и сам план БДДС. Внесите в формы регистрации заявки от кураторов или информацию обо всех плановых платежах и прокодируйте их статьями БДДС (статус «оплачено» не требуется).
Лайфхак 2. Вы сможете собрать факт БДДС предыдущих периодов. Загрузите в формы 1.1 и 1.2 данные выписок, прокодируйте и поставьте информацию об оплате. Аналогично, если в процессе работы изменили классификатор.
Форма 3. Платежный календарь
Частично эту функцию у вас уже выполняет БДДС месяца, если он актуализированный и планируется по договорам (так делать правильно). Частично ее выполняют все зарегистрированные платежи. Ведь дата оплаты планируется по условиям договора или графика платежей с запасом дней без риска возникновения просроченной задолженности.
Функцию платежного календаря выполняет и реестр платежей, когда вы видите фактический остаток на начало дня, ожидаемый приход — и корректируете план оплаты.
Но сам платежный календарь служит еще и для своевременного выявления кассовых разрывов (дефицита денежных средств). Его можно построить из форм 1.1 и 1.2, рассчитать дефицит/профицит. Образец формы — на рис. 4.
Просто распределите в формах регистрации все планируемые дни оплаты из столбца K «выбор на оплату» дополнительно по неделям. Для этого в столбце L проставьте номер недели. Например, «неделя 1», «неделя 2» и т. д.
Ожидаемые поступления и платежи в платежном календаре сгруппируйте по формулам:
- СУММЕСЛИМН(‘Форма 1.2′!$H:$H;’Форма 1.2′!$I:$I;$A14;’Форма 1.2’!$L:$L;D$4)
для группировки расходных платежей по неделям
- СУММЕСЛИМН(‘Форма 1.1′!$H:$H;’Форма 1.1′!$I:$I;$A22;’Форма 1.1’!$L:$L;D$4)
для группировки поступлений по неделям, где H — сумма, L — номер недели
В платежном календаре сумма не разделяется на оплаченную и заявленную к оплате. Аналогично можно сделать расчет по дням. Отрицательный остаток на конец периода фиксирует кассовый разрыв. Это значит, что вам потребуется или перераспределение платежей, или заемный источник финансирования.
При формировании планового БДДС по аналогичному алгоритму можно сразу подготовить и плановый платежный календарь. Затем уточнять его по зарегистрированным документам к оплате и ожидаемым или уточненным поступлениям.
Что вы получите уже в первый месяц с таким формуляром
- Факт БДДС, и вы для него заново не перебираете суммы из выписок
- Сокращение времени подготовки Реестра платежей
- Меньше потерянных и случайно забытых неоплаченных обязательств компании
- Сигналы о возможных кассовых разрывах
- Оперативную информацию о необходимости корректировки БДДС
В первый день месяца, следующего за отчетным, у вас уже будет готов отчет БДДС и основной блок информации о платежах на следующий месяц. В процессе вы будете создавать недостающие формы, что-то дополнять, что-то удалять, пробовать разные варианты заполнения.
Сейчас весь формуляр держится на четырех формулах. Возможно, вы найдете более эффективные приемы в Excel. Но тестовый период всегда длится не менее 3 месяцев — для оценки эффективности новых приемов нужно несколько завершенных платежных периодов.
Хотите получать дайджест статей?
Одно письмо с лучшими материалами за неделю. Подписывайтесь, чтобы ничего не упустить.
Спасибо за подписку!
Содержание
- 1 Ведение реестра документов
- 2 Создание печатной формы
- 3 Заполнение печатной формы
- 4 Списки для сортировки и заполнения
- 5 Создание выпадающих списков
- 5.1 Создание выпадающего списка с источником на этом же листе
- 5.2 Создание выпадающего списка с источником на другом листе
- 5.3 Как скрыть лист с источником списка
Ведение реестра документов в электронном виде в Excel позволит их систематизировать, обеспечит к ним быстрый доступ и исключит необходимость хранить каждый документ в отдельности. В любое время документ из реестра можно вывести на печатную форму, созданную на листе Excel. Ссылка для скачивания файла с примером внизу.
- Ведение реестра документов
- Создание печатной формы
- Заполнение печатной формы
Ведение реестра документов
Реестр документов удобно заполнять в «Умной таблице» Excel, если не предполагается использование объединенных ячеек.
Далее мы рассмотрим реестр с данными сотрудников, который может использоваться для хранения этих данных и распечатки различных документов. На практике это был реестр Трудовых договоров, из которого распечатывались: Трудовой договор, Приложение к трудовому договору, Договор о материальной ответственности, Приказ о приеме на работу, Изменения к трудовому договору, Соглашение о расторжении трудового договора.
В примере реестр значительно уменьшен, из печатных форм оставлен только Договор о материальной ответственности, чтобы показать сам принцип ведения реестра и заполнения данными печатной формы.
Пример реестра документов в «Умной таблице» Excel
Для заполнения реестра данными можно использовать автоформу, подробнее о которой можно прочитать в разделе Работа с «Умной таблицей».
Создание печатной формы
Чтобы заполнить печатную форму с помощью формул, она должна быть создана в Excel. Если не удалось найти нужный файл в Excel, его текст можно перенести из файла другой программы на рабочий лист, учитывая максимальное количество знаков, умещающееся в ячейке, иначе текст будет обрезан.
В нашем примере используется печатная форма Договора о материальной ответственности, текст которого скопирован на рабочий лист Excel из документа Word:
Договор о материальной ответственности
Если печатная форма на рабочем листе Excel готова, остается переместить (или скопировать) ее в книгу с реестром. Если реестр еще не создан, можно просто добавить новый лист в книгу с печатной формой и назвать его «Реестр…».
Заполнение печатной формы
Для заполнения печатной формы данными из реестра будем использовать встроенную функцию Excel — «ВПР». Ориентиром поиска по таблице будет номер записи в реестре по порядку, поэтому ячейку с номером договора укажем в формулах «ВПР» печатной формы как «Искомое_значение».
Аргументы функции «ВПР»
В мастере функций набраны аргументы для формулы в ячейке «L4», которая после ввода примет следующий вид: =ВПР('Печать ДМО'!G1;'Реестр ДМО'!A2:J102;2;ЛОЖЬ)
Во все остальные ячейки с переменными значениями копируется эта формула, и в ней заменяется только «Номер_столбца».
Теперь при записи в ячейку «G1» номера нужной строки реестра, печатная форма Договора о материальной ответственности заполнится данными соответствующего работника.
Вы можете скачать файл с примером ведения реестра в Excel и автоматического заполнения печатной формы Договора о материальной ответственности данными выбранного сотрудника.
Отчет «Универсальный реестр документов» позволяет формировать реестры с произвольным набором группировок и произвольными настройками отборов. Отчет вызывается из пункта «Учет документов» — «Универсальный реестр документов» главного меню программы или с рабочего стола «Учет документов» по одноименной ссылке.
Отбор, сортировка и группировка строк настраиваются на специальной панели, вызываемой по кнопке «Настройка».
Отчет содержит в себе такие сведения документах, как дату и номер регистрации в организации, количество документов, сумму, размер и количество прикрепленных файлов, если таковые имеются.
Новый документ можно зарегистрировать прямо из отчета, нажав на кнопку «Ввести документ» на верхней командной панели.
Пример реестра документов представлен ниже:
Универсальный реестр документов
Реквизиты отчета, обязательные для заполнения
Для формирования отчета необходимо указать:
- Период формирования реестра. Ограничивает собственную дату документов (может отличаться от даты регистрации). При открытии формы отчета устанавливается период «по умолчанию», равный текущему месяцу.
- Организацию, для которой формируется реестр документов. По умолчанию значение реквизита соответствует основной организации, которая указана в персональных настройках пользователя.
Доступные аналитические разрезы, возможности группировки строк и колонок
При формировании отчета пользователь может самостоятельно настроить группировки строк по аналитическим разрезам, аналогичным тем, что используются в отчете «Реестр документов по типам»: организация, подразделение, контрагент (или физ.лицо), договор контрагента, контактное лицо, тип документа, деловая классификация, гриф доступа, ответственное лицо, автор.
Дополнительная информация по использованию отчета
Как и в других отчетах по документам, расшифровкой универсального реестра является документ, зарегистрированный в системе.
Отчет «Универсальный реестр документов» доступен пользователям с наборами прав «Учет документов» или полным набором прав.
Создание списков инструментами Excel дает возможность не только сделать этот процесс максимально быстрым, но и унифицировать написание тех или иных слов и терминов. Последнее непосредственно касается создания так называемых выпадающих списков.
Списки для сортировки и заполнения
Списки для сортировки и заполнения, это те списки, элементы которых располагаются в строгой последовательности, кроме того, они циклически повторяются. Примерами таких списков являются дни недели, месяца года и т.п.
Приложение Excel позволяет не вписывать в ячейки каждый раз следующий день недели. Достаточно один раз вписать в ячейку, например, понедельник и выполнить автоматическое заполнение данными последующих строк или столбцов при помощи мыши. Для этого цепляем мышью уголок ячейки и протягиваем его куда нам нужно.
Изменять параметры такого списка можно в меню списки, которое открывается в основных параметрах Excel. Находим кнопку «Изменить списки» и нажимаем ее. В появившемся окне видны имеющиеся списки, также можно задавать новые. Для этого в окне «Элементы списка» нужно последовательно указать его части и «добавить» в списки. Также можно импортировать списки прямо с листа Excel, предварительно выделив необходимый диапазон.
Создание выпадающих списков
Выпадающие списки дают возможность не только вставлять в ячейку только заранее определенные значения, но и не дают возможности допустить ошибки при написании элементов этого списка. Чтобы в выпадающем окошке появлялся список нужно его предварительно создать. Это можно сделать как на открытом, так и на другом листе книги Excel.
Создание выпадающего списка с источником на этом же листе
Чтобы пользоваться выпадающим списком нужно сначала этот список создать. Создаем список на этом же (открытом) листе и проверяем правильность написания его составляющих. Затем выделяем ячейку или несколько ячеек, куда будем вставлять эти данные, открываем окошко «Проверка вводимых значений», которое открывается по прохождении следующего пути: данные/работа с данными/проверка данных.
В этом окне выбираем тип данных — список, в строке источник указываем диапазон ранее созданного списка-источника. Можно ввести этот диапазон с клавиатуры, но легче это сделать, просто выделив его мышью. После нажатия кнопки ОК увидим, возле нашей ячейки показывается стрелочка, при нажатии на которую выпадает наш перечень.
Создание выпадающего списка с источником на другом листе
Использование списка-источника, расположенного на том же активном листе, не совсем удобно, поскольку можно «нечаянно» изменить его содержимое. Поэтому предпочтительнее этот список «спрятать» на другом листе, а доступ к нему заблокировать.
В этом случае, описанным выше способом не обойтись, так как при открытом окне «Проверки…» мы не сможем попасть на другой лист, чтобы выделить или определить диапазон ячеек исходного списка. В этом случае пользуются назначением имени первоначальному списку.
Выполняем это так: создаем список данных на одном листе, затем выделяем его и присваиваем ему имя, кликаем последовательно вкладка формулы/присвоить имя (в разделе определенные имена), в открывшемся окне задаем имя списка. Если, предварительно мы не выделили список, то задаем диапазон его ячеек.
Переходим к выпадающему перечню на другом листе. Выделяем ячейки рабочего поля редактор, в которые будем вставлять элементы списка, открываем окно проверки… . В типе данных указываем список, в источнике ставим знак равенства и имя требуемого списка.
Как скрыть лист с источником списка
Лист с источниками выпадающих списков желательно открывать как можно реже, дабы случайно не внести в него ненужные изменения. Для этого лист можно защитить паролем и скрыть его отображение. Для того, чтобы спрятать лист нужно кликнуть по его ярлычку правой клавишей мыши и в открывшемся перечне команд нажать скрыть.
Таким образом Excel представляет большие возможности по созданию списков, даже по сравнению с текстовым процессором Word Office.
Новая Excel-таблица
«Реестр договоров»
Контроль выполнения, контроль оплат
версия 1.0 от 7 декабря 2022
Если есть большое количество договоров, находящихся на различной стадии выполнения, сложно контролировать их все. Ситуация усложняется, если договоры с отсрочкой платежа и оплате частями. Трудно держать в голове сроки каких платежей подходят, а какие уже просрочены.
В подобной ситуации вам поможет моя таблица.
Меня зовут Владимир Прохоров (ИП Прохоров В.В., ОГРНИП 311645410900040), я занимаюсь экономическими расчетами более 20 лет. С 1992 по 2008 год работал программистом и начальником отдела IT в банках и коммерческих организациях. Имею большой опыт в разработке программного обеспечения для банковских систем и бухгалтерии. С 2008 года разрабатываю Excel-таблицы экономическиой и управленческой тематики.
Со мной можно связаться по почте v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru), по телефону +7 927 055 9473, ВКонтакте vk.com/vvprokhorov
Таблица предназначена для ведение реестра (списка) договоров, контроля хода выполнения договоров, контроля оплаты.
Таблица работает следующим образом — вводим данные договора и периодичность платежей, таблица рассчитывает даты платежей и добавляет их в общий список платежей.
По мере поступления оплат вводим их в таблицу.
По графику плановых платежей и введенным данным о фактической оплате таблица рассчитывает суммарные показатели фактической оплаты, задолженности и просроченной задолженности на текущую дату:
- По всем договорам в разбивке по степени выполнения
- По всем договорам в разбивке по продуктам
- По месяцам по всем продуктам суммарно за 24 месяца
- По месяцам по продукту выборочно за 24 месяца
- В разбивке по продуктам по месяцу выборочно
Кроме того, для каждого договора можно задать до 10 этапов выполнения, например: Замер, Изготовление, Установка, Полная оплата.
Выполнение этапа отмечается проставлением 1 в соответствующей ячейке. Таблица считает количество выполненных этапов и процент выполнения.
Ход выполнения отображается цветом фона ячеек в столбце [Выполнение].
Предусмотрено 4 градации цвета: белый, желтый, светло-зеленый, зеленый (100% выполнение).
При необходимости количество этапов можно увеличить.
Посмотрите демо-версию https://finances-analysis.ru/download/reestr-demo.zip
На приведенных ниже рисунках показаны скриншоты (образы экрана) всех рабочих листов таблицы. Не показаны 2 листа с пояснениями.
Лист «Настройки РСТ» — Список продуктов (работ, услуг), Список видов оплаты, Периодичность оплаты.
Здесь и далее значения вводятся в ячейки с голубым фоном.
Значения ячеек обведенных синей рамкой попадают в выпадающие списки. Далее, при вводе договоров надо просто выбирать из выпадающих списков, см. рисунок ниже.
При необходимости строки можно добавить.
Лист «Новый» — Ввод нового договора.
Лист «Договоры» — Список договоров.
Лист «Платежи» — Список плановых платежей по договорам.
С помощью фильтра можно выбрать определенные платежи, например по клиенту:
Посмотрите демо-версию https://finances-analysis.ru/download/reestr-demo.zip
Лист «Сумм РСТ» — Суммарные показатели.
По всем договорам в разбивке по степени выполнения
По всем договорам в разбивке по продуктам
По месяцам по всем продуктам суммарно за 24 месяца
По месяцам по продукту выборочно за 24 месяца
В разбивке по продуктам по месяцу выборочно
Я гарантирую:
- Доставка в течение от нескольких минут до нескольких часов после прихода платежа. Сроки прохождения платежа для различных способов оплаты см. ниже.
- Все таблицы заполнены данными, которые служат примером и помогают понять логику работы.
- Таблицы снабжены подробными пояснениями по заполнению и работе.
- Отвечаю на любые вопросы по работе с таблицами. Конечно, предполагается что вы знакомы с предметной областью и имеете базовые навыки работы с Excel.
При выходе новой версии:
- Стоимость обновления зависит от того, насколько увеличивается функциональность новой версии таблицы.
- Сообщения о новых версиях получают только подписчики рассылки. Форма подписки внизу страницы.
Таблица
«Реестр договоров»
Версия 1.0 для Windows: 1990 руб.
Таблица работает в Excel для Windows (2007, 2010, 2013, 2016, 365). В ОpenOffice не работает.Сейчас я переделываю таблицы для работы под Mac. Чтобы получать сообщения о выходе новых версий подпишитесь на рассылку внизу страницы.
Телефон для вопросов оплаты и доставки +7 927 055 9473
почта v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru)
Видео инструкция по оформлению заказа и оплате
Откроется в новом окне
Способы оплаты:
- Robokassa. Сервис приема платежей в интернете. Можно оплачивать используя компьютер (телефон) или через терминалы приема платежей и салоны связи.
- Карты Visa, MasterCard, Maestro, Мир
- Интернет-клиент Альфа-Банк, Русский Стандарт, Промсвязьбанк и др.
- Электронные деньги Яндекс.Деньги, QIWI, WebMoney, Wallet One (W1), Элекснет
- Терминалы
- Салоны связи Евросеть, Связной
Robokassa высылает электронный чек согласно 54-ФЗ «О применении контрольно-кассовой техники».
- Банк. Можно оплачивать через Сбербанк Онлайн, сервисы интернет-клиент других банков или через кассу любого банка.
На расчетный счет 40802810556000029171
Индивидуальный предприниматель Прохоров Владимир Викторович,
ИНН 645400330452,
КПП 0 (у ИП нет реквизита КПП, если требуется ввести — вводите 0),
ПОВОЛЖСКИЙ БАНК ПАО СБЕРБАНК,
кор.сч. 30101810200000000607,
БИК 043601607.
Обратите внимание, что указание формы собственнсти (Индивидуальный предприниматель) является обязательным.После оформления заказа можно распечатать счет для юр. лиц
или квитанцию по форме ПД-4 для физ. лиц.Также можно получить счет, написав на v_v_prokhorov@mail.ru (v_v_prokhorov@mail.ru).
В назначении платежа обязательно указывайте номер счета, например:
По счету № … За электронные таблицы «Реестр договоров». Без НДС.При платежах не из России в начале назначения платежа укажите код валютной операции {VO10100}
Например, назначение платежа:
{VO10100} По счету № … За электронные таблицы «Реестр договоров». Без НДС.
Где VO — заглавные латинские буквы, 10100 — цифры. Фигурные скобки обязательны. Пробелы внутри фигурных скобок не допускаются.
Доставка:
Cсылка на скачивание таблиц будет отправлена на указанный в заказе e-Mail после поступления денег. Придет письмо с темой «Вы оплатили счет №…». Если его долго нет, проверьте папку СПАМ.
Для способа 1 (Robokassa) письмо со ссылкой отправляется автоматически, сразу после поступления денег. Обычное время поступления денег — минуты. Сервис Robokassa высылает электронный чек согласно 54-ФЗ «О применении контрольно-кассовой техники».
Для способа 2 (Банк) письмо со ссылкой отправляется после поступления платежа на расчетный счет. Время поступления денег от нескольких часов до 2 рабочих дней, обычно, в пределах рабочего дня. Юридическим лицам и ИП высылаю pdf-копии накладной по форме Торг 12. Оригинал накладной высылается по запросу, в запросе укажите, пожалуйста, почтовый адрес.
Если нужны еще какие-либо документы, прошу согласовать этот вопрос ДО ОПЛАТЫ.
Дальнейшая переписка по поводу полученных таблиц
Дальнейшая переписка по поводу полученных таблиц (вопросы по работе с таблицами, обновления и т.п.) происходит через адрес, на который были отправлены таблицы. При переписке по эл.почте сохраняйте, пожалуйста, историю переписки, т.е. включайте в ответ все предыдущие письма и ответы на них. При этом помещайте Ваше сообщение вверху (перед историей).
Владимир Прохоров, 7 декабря 2022
P.S.
Если вы хотите получить рабочую версию одной таблицы и демо-версии всех остальных моих таблиц, подпишитесь на ознакомительную серию из 9 писем с кратким описанием таблиц
Вы получаете:
- Рабочую версию таблицы «Учет доходов и расходов». Ссылка на скачивание в первом письме.
- Демо-версии всех моих таблиц: Оценка стоимости бизнеса, Платежный календарь, Себестоимость, Инвестиции, Фин.анализ МСФО, Фин.анализ РСБУ, Точка безубыточности, Анализ ассортимента, Диаграмма Ганта, Личное планирование. Ссылка на скачивание в первом письме.
- Скидку 40% (4706 руб.) на комплект из пяти моих таблиц. Как получить скидку — во втором письме.
- Скидку порядка 20% на отдельные таблицы. Как получить скидку — в письме с описанием конкретной таблицы.
- Скидку 20% на новые таблицы. Срок действия скидки трое суток с момента отправки письма о новой таблице.
- Сообщения о новых таблицах.
- Сообщения о новых версиях таблиц.
- Сообщения об акциях скидок. Обычно срок действия скидки трое суток с момента отправки письма о скидке.
- Сообщения об исправленных вариантах при обнаружении ошибок.
Кроме этого, прошу учесть, что извещения об акциях скидок и выходе новых версий получают только подписчики. Никаких специальных объявлений на сайте не делается.
Некоторые письма рассылки будут содержать предложения с ограниченным сроком, поэтому рекомендую подписаться на тот адрес, который вы регулярно просматриваете.
Не рекомендую подписываться на корпоративные почтовые ящики, т.к. есть вероятность что почтовый сервер посчитает рассылку спамом.
Вводите адрес способом копирования/вставки. При ручном вводе велика вероятность ошибки. Я это вижу по адресам подписчиков ***@gmai.com, ***@gmail.ru, ***@mall.ru и подобным. Сколько опечаток делают в той части адреса, которая стоит до @, трудно представить.
Если письмо с подтверждением подписки не пришло в течение нескольких минут — адрес с ошибкой или письмо попало в спам.
Посмотрите полный список таблиц
Финансовый анализ. Главная страница
© 2008-2023 ИП Прохоров В.В. ОГРНИП 311645410900040
Igor67
Пользователь
Сообщений: 3729
Регистрация: 21.12.2012
Держите вариант макроса.
Поищите тему по определению границ данных. Вы использовали надежный, но не совсем эффективный способ.
Кроме того, у Вас не все переменные были объявлены. Для избежания ошибок с переменными используйте Option Explicit. Старайтесь избегать использования Select. ВБА позволяет обрабатывать диапазоны ячеек и без выделения. Правда бывает что иногда эффективнее его использовать. Смотрите пример от KL
http://www.planetaexcel.ru/forum.php?thread_id=14948
Option Explicit
Sub Архивация()
‘
‘ Архивация Макрос
‘ Макрос записан 04.04.2010 (Avatar)
‘
Dim row_count As Long, i As Integer, lRow As Long
‘переменная row_count — для подсчёта количества заполненных ячеек
‘переменная i используется в цикле для хранения номера текущей строки
Application.ScreenUpdating = False
‘последняя заполненая строка на активном листе
row_count = Cells(Rows.Count, 3).End(xlUp).Row
‘операции с целевым листом
With Sheets(«Registry»)
‘определяем первую не заполнную строку
lRow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
‘очищаем от подписей
.Range(.Cells(lRow, 1), .Cells(lRow + 5, 8)).ClearContents
‘копируем диапазон
Range(Cells(2, 1), Cells(row_count, 7)).Copy
‘вставляем скопированные данные
.Cells(lRow, 2).PasteSpecial Paste:=xlPasteValues
‘определяем новый размер данных
lRow = .Cells(Rows.Count, 2).End(xlUp).Row
‘нумеруем используя свойство — Автозаполнение
.Range(.Cells(10, 1), .Cells(lRow, 1)).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1 ‘, Trend:=False
.Cells(lRow + 2, 3) = «Начальник______________»
.Cells(lRow + 4, 3) = «Дурак______________»
End With
MsgBox «Данные перенесены», vbInformation, «Готово»
Application.ScreenUpdating = True
End Sub