Сразу хочу сказать, что мы проектируем базу данных с тем предположением, что учет ДДС будет автоматизирован. Если же автоматизация не планируется (учет будет осуществляться в Excel, а документооборот – на бумажном носителе), то некоторые столбцы будут лишними. В дальнейшем их можно будет удалить. Какие именно столбцы окажутся ненужными, мы обсудим тогда, когда база будет окончательно сформирована.
В файле «Обработка выписки», в листе «Данные для базы» уже содержится некая информация. В строке № 4 есть заголовки столбцов, они окрашены в разные цвета. Неокрашенные заголовки в базу вообще не попадают, эта информация не нужна. Информация под заголовками зеленого цвета попадает в том виде, в котором она есть. Информация под заголовками желтого цвета обрабатывается формулами и попадает в область с заголовками голубого цвета. Таким образом, желтые заголовки в базу тоже не попадают, однако попадают результаты их обработки. Голубая область попадает в базу целиком.
Скачайте файл с проектом базы данных:
Будет лучше, если вы сохраните его в формате .xlsb. В этот файл вошли все заголовки столбцов, о которых шла речь выше. Столбцы расположены не в том порядке, в котором они были в файле «Обработка выписки». Это нужно только лишь для удобства визуального восприятия информации. Присвоим каждому столбцу уникальный номер, чтобы удобнее было на них ссылаться:
Теперь нужно настроить форматы ячеек.
О форматах. В выписке, с которой мы уже работали, номер банковского счета – 40702810932012345678. Если вставить его в ячейку Excel, у которой формат «Общий», то получится так:
В строке формул видно, что при вставке последние 5 цифр счета поменялись на нули.
В формате «Числовой» значение в ячейке выглядит иначе, но результат вставки неправильный:
Если задать формат для отображения двадцати знаков, то результат будет тот же:
Использование текстового формата приводит к нужному результату:
Использование апострофа, даже если формат «Общий» или «Числовой», также приводит к корректному отображению номера счета:
Апостроф видно в строке формул перед номером счета.
Но каждый раз ставить апостроф – это и по времени дольше, и вероятность ошибки выше.
Еще есть ситуации, когда номер начинается с нуля, например, если это БИК. Если внести его в ячейку с форматом «Общий», то ноль исчезнет. Если поставить формат для отображения девяти знаков, то БИК отобразится правильно. Но если в такую ячейку вдруг по какой-то причине нужно будет вставить число из иного количества цифр, например, из трех, то будет так:
Небольшое пояснение: если возникает вопрос, как в столбец для БИК может попасть запись, состоящая из иного количества знаков, то ответ такой: есть пользователи, которые предпочитают не вводить отдельную колонку для идентификаторов иностранных банков (SWIFT-код банка или, например, IBAN), и заносят эту информацию в колонку для БИК. Это означает, что с таким форматом ячеек информация может быть отражена некорректно.
Помимо БИК, с нулей нередко начинаются номера платежных поручений, номера счетов на оплату, номера счетов-фактур. И номер может содержать разное количество цифр.
Использование текстового формата или формата с апострофом и в этом случае дает правильный результат.
Я не буду уделять много времени форматам, а просто скажу о своем опыте: в тех случаях, когда значения ячеек не участвуют в вычислениях, я в большинстве случаев использую текстовый формат, даже если значения представляют из себя числа. Я делаю это, чтобы избежать некорректного отображения информации.
Далее мы зададим нужные форматы ячейкам в базе данных.
Сначала я желтым цветом выделю названия столбцов, у которых будет текстовый формат. Это все столбцы, кроме 2, 4 и 14. Потом нужно выделить ячейки под заголовками этих столбцов. Для этого я сначала ставлю курсор в ячейку A5, потом нажимаю Ctrl, после чего, удерживая Ctrl, я левой кнопкой мыши кликаю на каждую ячейку под желтым заголовком (C5, E5, F5 и т.д. – до конца таблицы).
Наводим курсор на любую из выделенных ячеек (это критично, иначе все ячейки перестанут быть выделенными):
После чего нажимаем правую клавишу мыши, выбираем пункт «Формат ячеек», в появившемся окне есть вкладка «Число» (она должна быть открыта по умолчанию), выбираем из списка формат «Текстовый» и нажимаем «ОК».
Затем выделяем ячейки под заголовками столбцов 2 и 4, кликаем правой клавишей, потом левой клавишей на «Формат ячеек», в поле «Числовые форматы» выбираем «(все форматы)», после чего помещаем курсор в поле «Тип», удаляем слово «Основной» и пишем такой текст: «ДД.ММ.ГГ;;- » и нажимаем «OK»:
Это поле состоит из трех частей, которые отделяются друг от друга точкой с запятой. Если в ячейке будет положительное значение, то оно будет выглядеть в виде даты в формате 01.01.19. После первой точки с запятой сразу следует вторая точка с запятой – это значит, что отрицательное значение не будет отображаться. Можно написать туда любое слово, например, слово «Ошибка», которое будет видно, если в ячейку попадет отрицательное число. Отрицательных дат не бывает, поэтому я не пишу для них формат. После второй точки с запятой следует область, в которой задается формат нулевых значений. Я для нулевых значений почти всегда задаю тире.
Почему я задаю именно такой формат, а не делаю просто: «Формат ячеек» - «Дата» - и любой понравившийся мне вид даты, как показано на рисунке ниже?
При таком формате, если в ячейке будет стоять ноль, то дата будет иметь вид 00.01.00, а мне такой вид не нравится. (Ноль я ставлю для того, чтобы избежать пустых ячеек. Все ячейки должны быть хоть чем-нибудь заполнены; позже объясню, почему. И если дата мне неизвестна, то я ставлю в ячейку ноль.)
Далее ставим курсор в ячейке под заголовком столбца 14 и присваиваем такой формат:
# ##0,00;[Красный](# ##0,00);-
(Внимание: обратите внимание на пробелы между решетками – это для разделения между классами единиц, тысяч, миллионов и т.д.!)
Этот формат означает, что положительные числа будут иметь привычный вид, отрицательные будут в скобках и выделены красным цветом, а нули будут в виде тире. Причина такого формата: я хочу визуально безошибочно распознавать положительные значения, отрицательные значения и нули. При разглядывании знаков «минус» глаза устают быстрее, чем при виде красного цвета.
Далее в ячейках под заголовками столбцов 2, 4, 14 нужно поставить нули, а в остальных ячейках – тире, после чего скопировать ячейки из диапазона A5:X5 на некоторое количество строк вниз. Получаем такой вид:
Когда я буду вносить в базу данные, то хотя бы несколько строк снизу должны содержать нули и тире и иметь такой вид, как на картинке выше, поэтому, по мере наполнения базы, ячейки придется постоянно копировать все ниже и ниже.
Поясню, для чего это нужно. Откройте новую книгу Excel и внесите данные так, как показано на картинке ниже:
После чего поставьте курсор в ячейку A1, перейдите на вкладку «Данные» и нажмите «Фильтр»:
После чего кликните левой клавишей на фильтр:
Как видно, те фрукты, которые были ниже пустой строки, под действие фильтра не попали. Если бы мы перед установкой фильтра выделили весь диапазон A1:A8, после чего нажали бы на фильтр, то тогда под действие фильтра попали бы все данные. Важно вот что: программа Excel очень чувствительна к разрывам списка. А поскольку фильтрами мы будем пользоваться очень активно, то нельзя допускать, чтобы какая-либо информация выпала из поля зрения (то есть из-под действия фильтра). По этой причине даже те ячейки, в которых нет данных, должны хоть что-то содержать, и это «что-то» желательно не должно отвлекать от работы. Лично мне комфортно видеть тире.
Теперь вставим данные в нашу базу. Для этого нужно вернуться в файл «Обработка выписки» и открыть в нем лист «Вставка в базу». В этом листе в диапазоне A3:X3 есть формулы, их нужно скопировать на область A5:X27. После копирования формул в диапазоне A5:X27 появятся значения. Далее выделяем этот диапазон (A5:X27), нажимаем Ctrl+C, переходим в файл «База», ставим курсор в ячейку A5, нажимаем правую клавишу и вставляем через «Специальная вставка» - «Значения». Должно получиться вот так:
Все, что нам было нужно из выписки, мы внесли в базу. Обратите внимание, что под самыми нижними записями есть строки с тире – они должны быть всегда после того, как в базу попадут новые данные.
Справа добавим еще несколько столбцов: «Валюта», «ЮЛ», «Тип источника», «Банк». Формат ячеек в этих новых столбцах – «Текстовый». Кроме того, я уберу заливку заголовков цветом.
Поскольку сейчас мы обрабатываем рублевые банковские выписки, в столбце 25 будет находиться значение «RUR». Другие возможные варианты значений: «RUR», «USD», «EUR» и т.д.
Вернемся к исходной выписке. Это выписка по юридическому лицу ЗАО «КЛЮКВА», банк – БАНК ИВА (ЗАО). В моей выписке они видны здесь:
Это наше юридическое лицо, по которому мы делаем управленческий учет. Юридических лиц в составе группы может быть много. В моей практике было, когда я делал базу с более чем 20-ю юридическими лицами, входившими в одну группу. А общее количество счетов, включая валютные, превышало 80. Хотя такое большое количество юридических лиц – это пример того, как делать не надо, но не буду забегать вперед, поскольку это уже вопрос менеджмента, а не учета.
Название юридического лиц вносится в колонку № 26 «ЮЛ», а название банка – в колонку 28 «Банк». В эти колонки я обычно вношу названия в сокращенном виде, без организационно-правовой формы, и само название делаю кратким. В нашем примере я внесу значения «Клюква» и «Ива», хотя обычно «наши» юридические лица (это те, которые входят в нашу группу) и «наши» банки (это в которых счета «наших» юридических лиц) у меня названы трех- или четырехбуквенными обозначениями.
В колонке «Тип источника» возможно одно из двух значений: «Банк» или «Касса». У нас сейчас значение «Банк».
Внесем эти значения и скопируем их до нижней заполненной строки. Кроме того, нужно выделить все заголовки (диапазон A4:AB4) и установить фильтр:
В диапазоне A5:AB27 есть пустые ячейки, которые я обычно заполняю тире (там, где формат «Текстовый») и ноликами (там, где даты или суммы). Для этого я ставлю курсор на заголовок, нажимаю на фильтр, выбираю «(Пустые)» и нажимаю «OK».
После чего ставлю курсор в ячейку E5, ставлю тире (или 0, если формат не текстовый), нажимаю Enter, снова ставлю курсор в ячейку E5, нажимаю Ctrl+C, спускаю курсор в ячейку E6, нажимаю и удерживаю Shift+↓, иду до конца списка и нажимаю Enter:
Далее отжимаю фильтр и проделываю это с каждым столбцом.
В разбираемом случае пустые ячейки на работу фильтра таблицы не влияют, поскольку пустота образовалась в результате работы формул, а не потому, что ячейки были пустыми изначально. Однако практика показывает, что наличие ячеек, которые пользователь визуально воспринимает как пустые, приводит к бо́льшему проценту технических ошибок.
В итоге я получил такую таблицу:
Если нужно, скопируйте ее в формате .xlsx:
В следующей статье я покажу, как работали формулы при обработке выписки.