Кол-во просмотров с 26.09.25г. :: 64
Добрый день. Продолжаем заниматься платёжным календарём по цели. Непростая эта задачка, но мы будем постепенно с ней справляться. Значит, у нас сегодня второй семинар. Ну и наша задача, вот как здесь написано на чёрном фоне, база учёных. Самое главное — создать функционал в базе данных, которое мы учитываем операцией. Операции начисления и соответствующие графики оплаты. Вот эти начисления. Переходим в наш пайлинг. Давайте мы заглянем в окладочку report. И здесь мы видим следующее. В прошлый раз у нас было, кажется, 23 сентября, и наши вот эти вот Наш сегодняшний день, а также просроченные долги и просроченный аванс висели в толпцах, где-то вот здесь, УВВ. Видите, теперь они автоматически сместились. То есть автоматически у нас происходит смещение настоящего времени относительно него. У нас мы видим дни, которые в прошлом. И здесь мы будем видеть фактические операции, проведенные, учтенные. Ну и, соответственно, будущее у нас начинается с завтрашнего дня, с 27 сентября. До этого мы еще доберемся до прописания формул, рассчитывающих те или иные обороты в поступлении оплаты по датам. Наша сегодняшняя задача — это работа с базой данных, с базой данных, которые мы учитываем, в которые мы вносим операции начисления, по которым потом происходят те или иные оплаты, поступления, движения денег. Ну, во-первых, давайте сделаем так. Ровно так же, как мы смотрели в нашем онлайн-сервисе, если мы заходим в платежный календарь, то у нас здесь разведены потоки входящие, DDS IN, и потоки исходящие, DDS OUT. Для чего это делать? По крайней мере, чтобы у нас чтобы нам не нужно было добавлять такую опцию, там, выберите, там, какая-нибудь кнопка, да, или там, выпадающий список, выберите, какой тип операции вы хотите внести, там, поступление или оплата. Лучше их разделить, а вот если мы смотрим в Excel, то нам же нужно будет вот эти вот формулы прописывать. И когда у нас формулы вот в этих вот ячейках, в этих строчечках будут возникать, то если мы не... разложен на две базы операции поступления и операции оплат, то тогда нам нужно будет внутри одной формулы прописывать вот эти вот самые «если». То есть нам нужно будет определить, что если у нас это операция поступления денег, то тогда мы прописываем, ссылаемся на какие-нибудь одни столбцы. Если это операция оплат, то на другие. Формула будет очень громоздкой, поэтому мы и здесь сделаем так, чтобы у нас... Мы сначала создадим базу данных вместе с формочкой ввода этих данных. Такую пару вкладок для учета операции поступления денег. И потом ее просто скопируем и сделаем базу данных, создадим базу данных. для операции оплат. Это просто удобнее. Ну а в связи с этим саму базу данных назовем вот так вот in. И, естественно, база данных, где у нас будут учитываться оплаты, мы ее назовем out. То есть есть поток входящий, есть поток выходящий. Ну и соответствующая форма, давайте напишем так, f форма для поступлений, ну и потом будет у нас форма для оплат. Так скажем, покомпактнее сделаем наше название. Это, в общем, тоже удобно. Почему? Потому что когда у нас очень большие будут потоки ячеек, в которых будут прописываться формулы, то чем формулы длиннее, тем файл становится более тяжелым, ну и так далее. Так, теперь дальше идем. Теперь у нас возникают вот какие моменты. Существует еще такая вот как бы проблема, о которой мы в прошлый раз разговаривали, которая, ну, например, в платежном календаре решается… А, ну давайте я сначала скажу, что за проблема. Если у нас… то или иное начисление оплачивается, то есть график оплат состоит из большого количества траншей, из большого количества поступлений или большого количества оплат, то есть предусмотрена какая-то длинная цепочка оплат для того, чтобы перекрыть вот это вот самое начисление. то в Excel у нас возникает проблема. Нам, во-первых, надо либо закладывать какое-то большое количество вот этих вот пар, доля и период оборачиваемых. Ну и потом, когда мы будем выстраивать базу данных, у нас получается, что здесь нам нужно какое-то необъятное количество столбцов использовать для этого. А вдруг их будет конечное число, которое будет меньше, чем необходимое нам. Извините. То есть возникает некоторый такой вопрос с ограничением наших возможностей. Ну, а поэтому каким образом решать такую проблему в Excel? Такую проблему в Excel тогда нужно решать не тем, что мы расширяем по столбцам базу данных, а нам необходимо, чтобы у нас внутри основных полей нашей базы данных, той части, где у нас вот эти два поля, у нас должна быть тогда доля и период оборачивания. И мы должны тогда вносить, например, 20 процентов, я не знаю, за 14 дней, за 2 недели, 30%, например, по факту, и сколько там, 50? Ну, например, 40% через 10 дней, например, оплаты, и какой-нибудь гарантийный платеж, отстоящий там подальше, допустим, на через 3 месяца, и через 90 дней еще 10%. Допустим, вот такая вот система оплаты. Ну и понятно, что в таком случае Если мы в базу будем прописывать вот таким вот образом, то есть одна строчка, одна пара, то тогда у нас другая проблема возникает, что если это относится к той или иной операции начисления, например, вот к этой операции начисления, где клиент номер два, мы ему что-то продаем на 250 тысяч. чтобы нам понимать, что вот эти четыре оплаты относятся именно к этой строчке, нам нужно будет ее вот здесь вот постоянно повторять. Если мы ее будем постоянно повторять, а нам, например, необходимо будет посчитать размер дебиторской задолженности потом, ну или там нам нужно будет, допустим, то же самое посчитать Вот здесь у нас количество начисления идет, сумма начисления, а вот по вот этим парам столбцов, в этой паре столбцов у нас здесь каждая пара, это есть конкретный платеж. Мы должны будем просуммировать и посмотреть, они совпадают, то есть полностью ли перекрывается начисление запланированными оплатами. Если у нас здесь четыре одинаковых строчки, то тогда у нас начисление будет один миллион рублей. Если у нас здесь четыре одинаковых строчки, то тогда у нас начисление будет один миллион рублей. Если у нас здесь четыре одинаковых строчки, то тогда у нас начисление будет один миллион рублей. Если у нас здесь четыре одинаковых строчки, то тогда у нас начисление будет один миллион рублей. С этой стороны тоже возникает некоторая проблема. С одной стороны проблема, что мы можем добавлять огромное количество столбцов, но это крайне невыгодно и неудобно, потому что вдруг возникнет ситуация, при которой количество платежей на единичку будет больше, и что потом все менять. С одной стороны такая проблема. С другой стороны, если мы это начинаем вот так плодить, Дублируя фактически эти атрибуты, начисления, то при вычислениях вертикальных суммирований мы будем тоже получать проблему задвоения, затроения и так далее. Как это можно решить? Понятно, что можно включать идентификатор. Для чего мы берем кучу столбцов слева? Для того, чтобы идентифицировать. Что значит идентификатор? Мы, например, идем сверху вниз. прочитываем, да, а Excel обычно, его последовательность действий в Excel сверху вниз, слева направо, да, то есть такой стандартный порядок, да, стандартный порядок прочитывания файла Excel. И вот если мы сверху вниз идем и, например, видим, что вот у нас встретилось вот такое, вот такой атрибут, вот такое начисление, да, то понятно, что мы можем напротив него, допустим, в толпце B, например, напротив вот этой строчки поставить, допустим, единичку. Эта единичка идентифицирует, что вот у нас есть одно вот такое начисление. А дальше, когда мы прочитываем еще раз точно такую же строчку, мы, например, сюда ставим 0. И тогда при суммировании, вот таком вот вертикальном суммировании, мы можем поставить условие, что в толпце B должна быть единичка. Тогда мы будем отсекать только вот эти вот 250 тысяч один раз, а вот эти вот три раза по 250 тысяч, они уже в эту сумму не попадут. Идея, ну, в принципе, неплохая, мне кажется, идея. Это можно реализовывать. Вот. но тогда у нас возникает другая проблема, не проблема, но другой... возникает или нет? нет, это, наверное, из другой локтеры. сейчас я перескакиваю. во-первых, таким вот образом мы в принципе можем включать вот эти единички-идентификаторы, и через идентификаторы мы можем отсекать дубли, отсекать дубли вот этих начислений. Теперь есть еще одна проблема в Excel. Когда у нас вот эта база данных будет огромная, а я уже говорил, что когда мы подобные базы данных особенно вот, например, для строительных компаний разрабатывали, у них очень быстро набегают вот эти вот строчки, вот эти вот начисления, так называемые. И там по нескольку тысяч, до десятков тысяч доходит количество этих строк. И в Excel, чем более громоздко становится файл, тем здесь возникает проблема, что когда вы вот так вот копируете строчку и пытаетесь ее вставить вот таким вот образом, вставить в скопированные ячейки, Не просто сделать специальную вставку и вставить все, не сдвигая, а если вы именно вставку делаете, вставить скопированные ячейки. Через вот эту опцию происходит, что весь массив сдвигается на одну ячейку вниз и потом в высвободившуюся ячейку вы вставляете ту ячейку, в высвободившуюся строчку вы вставляете ту строку, которую копируете. Вот такая вставка, она подвисает. Excel начинает долго крутиться, и большие задержки происходят. Нежели если вы, например, просто естественным образом копируете эту строчку и, грубо говоря, по хронологию ее ставите следующей. То есть ставите ее... в ниже следующую пустую строчку. Потом вы опять берете эту строчку, копируете и ее вставляете еще ниже. Потом дальше опять копируете, опять вставляете ниже. Вот такая вставка обычная, просто обычное копирование, оно срабатывает при любом объеме файла, оно срабатывает быстро. То есть вставка происходит моментально, причем мы вставляем как значение. И потом докопируем вот эти строчки. На предыдущем занятии обсуждали функциональные строчки. Но тогда возникает другая проблема. Мы же макрос записывали каким образом? Макрос записывался следующим. Мы копировали строчку 2, наводили на 11 строчку, и этот макрос он всегда в одиннадцатую строчку вставляет вот эту вот новую, которая во второй. То есть по нажатию вот этой кнопочки «Добавить в базу» срабатывал макрос, который работает ровно с двумя определенными строками, со строчкой 2 и со строчкой 11. Если нам нужно вставлять каждый раз самую нижнюю строчку после самой нижней заполненной в ряду вниз, вставлять в очередную пустую строчку, то каждый раз происходит смещение на единичку. Макрос таким образом у нас не сработает, если мы его записываем тем способом, как мы это делали. Просто обычную запись, и все. Надо тогда влезть в макрос и каким-то образом этому макросу указать, в какую строку нам нужно чего вставлять. Давайте зайдем в макрос, вот мы заходим в разработчик, вот наши макросы, у нас вот один макрос добавили в базу. Если мы туда войдем, то вот он наш этот самый макросик. И вот мы видим, что мы берем строчку 2, rows, строчку 2 берем и вставляем в одиннадцатую строчку, создвигаем вниз. Ну и все. Дальше там потом уже вот этот функционал мы там перекопим. То есть получается так. то для того, чтобы нам не делать вот эту ставку со смещением, а делать обычное, абсолютно обычное копирование, нам нужно, чтобы наш макрос каким-то образом находил вот эту пустую строчку, самую ближнюю, самую верхнюю, примыкающую к нашей базе данных. Не проблема, это тоже можно сделать. Соответственно, чтобы это сделать, тоже мы сейчас подумаем, как это лучше. тут надо приложить некоторые небольшие усилия, так скажем. получается, что Ну, вот как бы возникает некоторый функционал для вот этих вот столбиков слева. Сейчас я просто подумаю, с чего нам лучше начать. Так, теперь давайте начнем. А, давайте вот что сделаем. Давайте тогда уже окончательно определимся. с тем, что в нашей форме мы будем... А, и вот еще что, вот еще такая возникает проблема. Если мы здесь не создаем достаточное количество пар, вот этих вот доля и период оборачивания, то нам необходимо, чтобы у нас была, получается, одна такая пара, просто доля и период оборачивания. мы каждый раз должны добавлять в базу все атрибуты нашего начисления и нажимать на кнопку или вносить только пары этих доля-период, сколько у нас по договору прописано от отдельных платежей. Это ничего страшного, это несложно. Но при этом нам нужно куда-то вот сюда, во-первых, выводить. Нам нужно куда-то сюда будет выводить информацию о том, сколько платежей уже внесено, во-первых. То есть совпадает ли это с отчислением. Сумма всех платежей совпадает ли с отчислением, то есть то, что мы план полностью 100% прописали. И есть еще одна проблема, состоящая в следующем. Допустим, мы чего-то внесли, ну, например, мы точно знаем, за какое количество времени у нас, например, предоплата, а про окончательный платеж, например, информации нет. Ну, допустим, тогда это... Форма должна позволять нам делать следующее. То есть мы, например, вводим информацию по одному какому-то начислению, какую-то часть информации вынесли, потом, например, по другому какому-то начислению вводим информацию. И если мы хотим обратно, назад вернуться к предыдущему, то нам нужно сделать так, чтобы вот в эту формочку у нас была возможность подтянуть данные из базы. то есть у нас здесь должен быть еще один столбик, который будет работать по следующему принципу, то вот то, что у нас вот здесь вот вносится в базу, то по совокупности... Извините. Да, микрофончик. Я просто думал, вы ко мне. Значит, есть некоторая совокупность таких вот моментов, которые… ну и давайте мы сейчас посмотрим просто эти моменты, например, в этом самом платежном календаре. То есть я сейчас здесь зайду, заавторизуюсь. Вот о чем я говорил сейчас. Если мы идем в dds.in, то, во-первых, когда речь идет о будущем, когда мы в договоре что-то прописали, или уж на чем-то договорились, то иногда возникают такие моменты, что что-то может быть уточнено. Вот, например, вот здесь вот договорились о том, что сумма начисления будет 121 тысяча. Ну, допустим, она стала 125 тысяч. Или где-то что-то, какие-то там моменты у нас меняются. То есть мы вот сюда как бы заходим и, например, сумма там не 121 тысяча, а, например, становится 125 тысяч. И мы ее там сохраняем, вводим. Корректировки в атрибуты начисления внесены. Но здесь-то мы в онлайн никак не работаем. Мы нажимаем на соответствующую кнопочку и подтягиваются данные по вот этой операции. В Excel, если просто в тупую работать, то здесь что нужно сделать? Нужно зайти во вкладку in, найти вот эту соответствующую строчку и в ней что-то руками внести. Понятно, что если у вас здесь 10 тысяч строк, то это все становится очень неудобным для работы. Поэтому у нас и называется данное занятие, как платёжный календарь, из веб-сервиса в Excel. То есть мы смотрим на возможности, которые нам даёт, грубо говоря, интернет, да, то есть вот эти вот онлайн-сервисы, и пытаемся понять. Ну, во-первых, мы через это проблематику понимаем, да, то есть... И вообще говоря, саму методологию, в том числе платёжного календаря. Но понятно, что нам каким-то образом нужно сделать так, чтобы, когда мы работаем вот этой вот самой формой, У нас где-то в каком-то месте могли появляться выдержки из базы данных. Например, мы сюда вводим какое-нибудь название компании или статью или то и другое вместе, даже какие-то части названий, по какой-то части названий, чтобы у нас сюда, например, подтягивались те строчки из базы данных, в атрибутах которых содержатся вот эти вот отрывки каких-то фраз, каких-то текстов и так далее. Если мы это сможем сделать, то тогда вот сюда будет подтягиваться некоторое небольшое количество конкретных учетных данных, Ну и, допустим, мы их можем там как-то обозначить, там, единичку поставить, выбрать, да, и нажимать на кнопочку, которая подтянет все точные атрибуты, которые мы уже вводили. Иначе может получиться так, что если мы там где-то что-то забудем, там, как там договор назывался, или как мы там что-нибудь сюда вписали, назвали, и так далее, или ошибку какую-то сделали, то мы... то может получиться так, что... мы вроде как будто бы добавляем в базу новую информацию о платеже, про то начисление, по поводу которого мы хотим это внести, а мы просто вручную сделали ошибку, допустим. И получилось так, что мы добавили новое какое-то начисление. То есть, короче говоря, нам необходимо, чтобы наша форма и база данных Они взаимодействовали ровно так же, как это работает в онлайн-сервисе. Ну и так же, что мы могли взять, нажать и посмотреть. Мы нажали и смотрим, что у нас здесь и как. Была начислена сумма 25 тысяч, аванс 37, доплата сколько-то, факт поступления 36 300 и так далее. Чтобы мы подобную аналитику по поводу каждого вычисления могли посмотреть, нам необходимо, чтобы наравне с тем, что у нас есть кнопка, которая переносит данные из формы, переносит данные в базу, нам необходимо, чтобы Если мы что-то здесь вносим, чтобы вовнутрь этой базы, в том числе, подтягивались данные, вовнутрь этой формы, в том числе, подтягивались данные из базы, которые нам нужны. То есть, если этого механизма не будет, а нам придется все равно правками и редактированием заниматься в большой простыне, вот в этом, то грош – цена такому эксельскому элементу, Тогда не надо ничего здесь городить, всяких макросов и так далее. Просто вручную тупо работаем, вносим и вносим. Можно и так работать. Очень многие так и работают. Мы стараемся подумать, каким образом можно простыми методами себе улучшить жизнь. Понятно, что здесь вообще в Excel можно все запрограммировать, напрячься и так далее. Это неудобно, потому что большинство людей, которые в Excel работают, они все-таки так, по-простому. Максимум, что используется, сумма если mn, сумма произведений, какие-то простейшие макросы, как мы это делали в прошлый раз. И хотелось бы в рамках этого функционала и остаться. То есть это на уровне простого пользователя. То есть не перегибать, так скажем. Мы как бы себя считаем дилетантами в Excel, самыми простыми какими-то его свойствами, какими-то инструментарием пользуем. Но пытаемся это сделать изящно. А для этого нужно подумать, каким образом сюда, например, в нижнюю часть подтягивать некоторые выборки из базы данных. То есть должно как бы и в одну и в другую сторону. Ну, поэтому, во-первых, нам желательно определиться с ключевыми атрибутами. Атрибуты, которые для нас будут идентификаторами, которые будут идентифицировать учетную запись. У нас как бы получается так. Одна учетная запись – это Компания. Ну, если у нас несколько компаний, холдинг у нас внутри периметра нескольких компаний, то желательно, чтобы у нас была компания. Компания – это наш внутригрупп. Можно здесь сделать не компания, непонятно, может быть, компания – это наш контрагент, хоть здесь и есть слово контрагент, но здесь можно написать внутригрупп. Внутри группы. Внутри группы – это означает, что наша группа компаний, которую мы ведем, которую мы учитываем, вот сюда это как бы вносится. Ну, там какой-то выпадающий список можно будет сделать и так далее. Статья DDS. Статью DDS нам она необходима. То есть по какой компании, по какой статье у нас происходит документ. дата начисления, сумма начисления и контрагент. Давайте сделаем так. Контрагента. Внутри групп статья. Наверное, контрагента лучше поставить после статьи. Во-первых, вот так мы его вырежем. И поставим вот сюда, вставить в вырезанные чеки. Вот так вот. Внутри групп, статья, контрагент, документ, дата начисления, сумма начисления, а потом вот доля и период оборачивания. Вот это вот слово «доля» мы уберем 1, 2, 3. Просто будет доля и период оборачивания. Вот это нам не нужно. Удаляем. И идем во вкладку «in». Значит, у нас здесь внутри групп, статья, Дальше мы должны вырезать контрагента, поставить его вот сюда, ставить вырезанные ячейки. Внутри групп статья, контрагент, документ, дата написления, сумма написления, потом доля, период оборачиваемости. Вот это вот в конце удалить. Здесь у нас тоже вот это вот мы пока все тоже удалим. Здесь у нас какой-то контроль. Давайте вот это вот удалим. столбец, нам пока это все здесь не надо, это все удалим. То есть вот с этими вот столбиками, которые здесь слева, я их пока что сделаю, там ширина 2, например. Сейчас мы к ним вернемся. Значит, документ, дата начисления, сумма начисления, доля, период оборачивания. Значит, Это то и вот эти вот 1, 2, 3, 4, 5, 6, вот эти 6 полей. Будем считать, что вот эти поля, мы их выделим, допустим, как-то вот так. Будем считать, что вот эти поля, они являются идентификатором. То есть вот эти шесть полей, они полностью определяют учетную запись. То есть квант учета, учетная запись нашей базы данных будет взаимоотнозначно определяться вот этими шестью полями. Теперь, эти шесть полей, они у нас распределены, да, как будто они идут вот в таком виде, и понятно, что если мы желаем в рамках нашей работы, вот здесь у нас, например, будет, допустим, вот какая-то вот такая примерно часть, вот здесь у нас будет, допустим, форма, да, тогда вот здесь вот снизу мы, допустим, хотели бы сюда подтягивать записи из базы данных, которые соответствуют вот этим вот полям, да, заданным значением для вот этих вот полей. А, например, справа можно еще сделать один экранчик, который мы будем выводить, ну, я не знаю, там, графикоплат, например, визуально посмотреть, как он выглядит. Сюда можно какие-то, какую-то аналитику выводить, ну, и так далее. для каждой учетной записи. То есть примерно как вот у нас вот здесь выглядит. Вот когда мы берем конкретное начисление, сумма начисления, нажимаем на вот эту вот штучку, и у нас вот здесь аналитика в разрезе выбранного основания начисления по состоянию на 26 сентября, то есть вот сегодня. И мы видим там дебиторка 8800, просроченная 1200, срок просрочки, план поступлений до настоящего времени, поступила по факту, текущее отклонение факт-план, плановая оборачиваемость, оборачиваемость и так далее. Да, здесь вот все подробно-подробно-подробно расписано только про вот это вот, только вот про одно вот это начисление. То есть одна учетная запись, связанная с продажей на 125 тысяч, с реализацией и графиком, каким-то графиком оплаты. И мы сразу же справа видим всю аналитику только в разрезе данного начисления. Ну и нам, наверное, здесь в эксцентре ровно то же самое было бы неплохо сделать. тоже вот здесь вот будет у нас окошечко, в которое будет выводиться аналитика по той учетной записи, которая будет находиться в этих полях. Но тогда нам желательно сделать так, чтобы у нас соответствующие поля были по по горизонтали, да, выстроили. То есть сейчас они по вертикали выстроены, а нам бы желательно их вот так вот по горизонтали выстроить, ровно так же, как они вот здесь. Ну и, пожалуй, мы это так и сделаем. Можно даже сделать так, что вот у нас есть толбец L, L, M, N, O, P. Здесь вот тоже L, M, N, O, P. И вот мы можем прям туда это все и Все это туда можем внести. Я не знаю, нужно это или не нужно, нам понадобится или не понадобится, сколько это важно, чтобы у нас эти столбцы были синхронизованы. Но так как бы пусть будет. Я что делаю? Я беру вот так вырезаю. именно почему я сейчас вырезаю, потому что у нас идет ссылка вот отсюда из базы данных in, у нас идет ссылка на c4, на название. И чтобы эта ссылка перенеслась, я вот здесь вырезаю вот это название и ставлю его толбец l. Пусть это будет вот так. Теперь статья DDS вырезаю, колбец M вставлю. Контрагент вырезаю, колбец N вставляю. Документ вырезаю O. Дата начисления вырезаю P. Сумма начисления вырезаю Q, вставлю вот так. И вот эти, соответственно, желтые ячейки вырезаю. Давайте мы это вот отступим. Здесь у нас четвертая строчка. Ставим это в седьмую строчку. Ставить. Отсюда тоже вырезать. Ставить. Вырезать. Ставить. Вырезать. Ставить. Вырезать. Ставить. Вырезать. Ставить. Такс! доля и период оборачиваемости. ну, доля и период оборачиваемости... допустим, это можно вот так вырезать. и пока что куда-нибудь... здесь у нас седьмая строчка, пропустим еще две, даже три вот так. и вот, например, куда-нибудь... Вот сюда вот так вот пока что ставим вот таким вот образом. Пусть у нас там пока что звездочки уберем. Здесь давайте вот этот вот звездочки тоже уберем. И все вот эти столбцы слева сделаем ширину столбцов единичка. И пусть вот у нас форма теперь вот так вот выглядит. Здесь даже можно сделать ширину столбцов не единичка, а поменьше, допустим, 0.5 поставим. Вот так вот. Здесь можно сделать формат ячеек и как-то вот эти вот границы обозначить. И пусть будут там какие-то такие цвета. Это пока пусть вот выглядит так. Ну вот, вот у нас сюда мы вносим данные про соответствующую учетную операцию начисления. А это здесь мы сделаем жирным. И теперь начнем вот с чего. Давайте сначала все-таки вот с этим макросом, наверное, разберемся. С макросом будем разбираться так. Значит, у нас вот здесь получается 11 11 столбцов. Возможно, что нам этих 11 столбцов здесь хватит для того, чтобы прописать функционал обработки базы данных. Сейчас посмотрим. Функционал обработки базы данных. Я сейчас не знаю, насколько нам нужно будет и нужно будет время, но здесь вот можно поставить время написать и написать сегодня. написать сегодня и поставим, а не сегодня, а давайте мы напишем, кажется, это вот так пишется дата. Нет. Время. Так. или data.t, что-то такое, нет. Чтобы, ну, например, мы можем сделать так, чтобы у нас здесь все время было дата-время, чтобы когда мы вот это вот дело там как бы копируем, то, ну, например, оставались данные о том, когда была внесена данная учетная запись, да, то есть когда это произошло. А, там T, кажется, вот так вот. T data. Вот. T data. Вот. Возвращает текущую дату и время в формате даты и времени. Вот так. И если мы здесь сделаем формат ячеек вот такой вот, то мы это и получим. Так, давайте мы здесь сделаем шрифт. Так, восьмерочку поставим просто в мелкую. Наверное, формат ячеек. 9 шрифт. Вот у нас время. Теперь дальше. Что нам здесь нужно еще? Когда мы для того, чтобы макрос нам вставлял все строчки последовательно сверху вниз, чтобы вставлялись строчки последовательно сверху вниз, нам необходимо, чтобы в каком-то месте постоянно присутствовал номер строки, номер той строки, в которую нужно в очередной раз вставлять данные. Значит, как нам этот номер строки найти? Как нам найти номер строки, в которую вставлять очередные данные. Что мы можем сделать? Мы можем сделать следующее. мы можем сделать... Значит, теперь смотрите, формулы, которые мы будем вот здесь функциональные прописывать, они у нас будут прописываться во второй строчке вот в этих столбцах слева. Это будут формулы, которые обрабатывают каждую учетную запись, которая вот здесь вот пойдет вправо. При этом нам необходимо, чтобы эти формулы не применялись во второй строке. Нам нужно, чтобы формулы не применялись во второй строке. И коль скоро мы будем все вот это добавлять, мы будем добавлять последовательно вниз. Давайте сначала так сделать. значит, значит, значит так. мы пропишем в столбце А мы пропишем формулу, которая будет давать значение в ячейке, показывать значение в ячейке номера строки, в которой она находится, вот этот вот номер строки, который здесь написан, он будет появляться только в том случае, если у нас вот в этих столбцах в базовых l, m, n, op, f, q присутствует хотя бы что-то непустое. То есть если здесь хоть что-то непустое присутствует, это будет означать, что данная строка занята какой-то учетной операцией. И если здесь не пусто, то тогда в столбце А у нас должен отобразиться номер такой строки. Понятно, что тогда мы можем в ячейке А1, вот в этой ячейке А1, разместить формула. в прошлом, кстати, равняется максимум и от 2 до как бы вниз. а мы можем вот что сделать. давайте мы вот с чего начнем. Пусть у нас... Нет, давайте так сделаем. Пусть у нас в столбце А будут номера строк. Я обычно всегда так делаю. Просто в столбце А мы прописываем номера строк. Но при этом формулу мы сделаем следующим образом. Мы напишем, ровняем. Если... Если строка... Если номер строки, в котором написана эта формула, равняется 2, то тогда мы пишем пусто. А иначе мы пишем строка вот так. То есть здесь у нас, если мы находимся во второй строчке, то мы этих формул не видим. Почему? Потому что мы, когда будем обрабатывать нашу базу данных, то по этим столбцам a, b, c, d до j, 11-10 столбцам, мы будем ставить разные условия. Если во второй строчке здесь тоже что-то будет появляться, оно нам будет взбивать общую картину. То есть вот эта вторая строка – это строка, которая взаимодействует с нашей формой. А значит, нам нужно, чтобы здесь формулы-то были, но только они не работали для строки номер 2. Мы их будем потом копировать, и тогда все будет нормально. Значит, мы прописали, и теперь смотрим. Если мы вот эту формулу копируем вот сюда куда-нибудь, вот в эти трочки, которые у нас здесь наполнены, то вот у нас появляются номера скролк 11, 12, 13 и так далее. Пусть здесь тоже будет формат ячеек. Главное тоже сделаем 9. И здесь напишем номер строки. хотя мы этот самый номер строки, да, мы его потом произведем, а вот в столбце b мы сделаем следующее. мы напишем формулу, вот здесь вместе пишем, что равняется, если мы имеем дело с ненулевой учетной записью, то тогда мы сюда ставим номер спротив. А, с другой стороны, если мы в эту таблицу всегда только вносим... А, если мы что-нибудь удалим, да, это в конце. Да, давайте, значит, мы говорим так, что если у нас данная в данной строчке не нулевая учетная запись, то тогда мы сюда ставим вот это значение из соседнего столбца. Как прописать, что не нулевая? Значит, пишем, если или у нас l12 не равно хуста, точка с запятой, И дальше вот я вот сюда перемещусь и копирую. Копировать. 1, 2, 3, 4, 5. Или m12, или n12 не равняется пусто, или o12 не равняется пусто, или p12 не равняется пусто, или q12 не равняется пусто. то тогда… Так, теперь надо понять, удерживать эти столбцы или нет. Давайте будем удерживать. Вдруг нам нужно будет этот столбец куда-нибудь скопировать. Мы захотим какую-то другую логику. Тогда, чтобы, по крайней мере, столбцы были закреплены. Я нажимаю раз-два-три, F4. Раз-два-три на F4 нажимаю, и тем самым я удерживаю, через доллар удерживаю столбцы. При копировании… Если у нас хотя бы в одной из шести вот этих ячеек не пусто, не пусто, то тогда мы берем и ставим сюда из соседней, слева из соседней ячейки, кстати, тоже здесь их 4-3 раза, ставим номер строки. Ну, а иначе, а иначе мы пишем пусто. Вот так. И кнопка закрывается. Вот. тоже здесь сделаем 9 и мы это вот так вот распределим. теперь в ячейке а1 мы можем написать форму. уравняется максимум по столбцу b. вот так максимум по столбцу b. если мы берем максимум по столбцу b, здесь тоже берем f4. то мы получаем, что 21 строчка – это последняя строчка в нашей базе данных, в которой присутствуют ни нулевые, ни пустые данные. А значит, следующая строчка, 22, это та, куда можно вставлять следующую запись, то есть мы прибавляем 1. И у нас получается, что Вот здесь вот, в этом месте у нас все время будет в ячейке А1 у нас всегда будет присутствовать номер той строки, в которую нужно в очередной раз вставлять новую учетную запись. Вот так. Значит, теперь наша, я сохраняю, так, теперь наша задача состоит в том, чтобы мы перезаписали макрос, и этот макрос будет производить следующее. Что он должен делать? Он будет копировать вторую строчку, то есть здесь как бы все в порядке, но должен будет эту самую вторую строчку вставить куда? Вставить простым копированием, вставлять строчку с номером, который указан в ячейке А1. А для этого мы должны разобраться, каким образом макрос может затянуть из файла, получить из файла данные, которые находятся в ячейке, в заданной ячейке, в данном случае в ячейке 1. Если мы это сможем сделать, то тогда наш макрос будет всегда вставлять туда, где ближайшая пустая строчка, и туда будет добавлять данные. ну, в принципе, план хороший. Единственное, что теперь еще можно... вот что еще можно сделать. Посмотреть, что нам здесь, например, еще понадобится. Значит, столбец B. Давайте здесь как раз напишем номер строки. Пусть будет так написано. Толбец t назовем его 1. Копирую сюда специальная вставка, формат. Единичка. Теперь номер строки. Формулу мы прописали вот в этом месте. Например, мы берем теперь формулу из строки 11, формулу для столбца b. Нам ее нужно скопировать и вставить вот сюда, в ячейку B2. И при этом здесь нужно дополнить вот этой записью, что если мы находимся во второй строке, то мы пишем пусто. А иначе вот это. Если мы находимся во второй строке, то мы пишем пусто. Теперь вот здесь мы пишем формулу. Если мы находимся опять во второй строке, то пишем пусто, а иначе мы пишем единичку. То есть вот этот вот столбец, столбец С, это просто полностью столбец, состоящий из единиц. Я сейчас это объяснил. Я всегда об этом говорю. Дело вот в чем. Когда у нас появляются очень большие массивы данных, и нам приходится, например, считать какое-то количество строк, удовлетворяющих тому или иному условию, то оказывается функция счет если или счет если mn работает примерно в 7 раз дольше, чем функция стум если mn. Поэтому для того, чтобы считать, производить количественные расчеты по строчкам, лучше делать суммирование по столбцу, в котором находятся единички. Тогда ваш файл будет работать гораздо быстрее, когда здесь будет много-много данных. То есть пересчет будет гораздо быстрее делать. Поэтому я всегда ввожу вот такую строчку one, называю ее, и, соответственно, вместо функции счет если я обычно использую функцию сумма если. Дальше. Что нам нужно сделать дальше? Идентификатор. Теперь нам нужен ID, назовем так, идентификатор учетной записи. Это вот та самая единичка. То есть мы спускаемся сверху вниз, идем сверху вниз и мониторим вот эти вот шесть столбцов. Как только мы встретили запись, поступление денег, клиент номер 2, договор 123, то мы как только первый раз его встречаем, мы сюда ставим единичку. Как только мы его второй раз встречаем, мы здесь ставим нолик. То есть единичка определяет первое вхождение. Если смотреть сверху вниз, единички будут стоять напротив тех учетных записей, которые встречаются в базе данных впервые. Давайте посмотрим, как это сделать. Равняется. И мы говорим. Мы должны поставить единичку, если вот эта совокупность шести ячеек встретилась впервые в этой таблице. Она встретилась впервые, если смотреть сверху вниз. Но при этом есть одна проблема. Какая проблема? Если мы будем смотреть вот просто вот так вот сверху вниз, то вот эта вот вторая строчка, она нам может здесь помешать, потому что если во второй строчке вот в этих вот шести ячейках отображается учетная запись вот такая, она подтягивается вот отсюда. Если вот здесь вот сюда мы подтянули в очередной раз ту же самую учетную запись, то она у нас отобразится вот во второй строчке. Поэтому просто тянуть и мониторить с первой строчки мы не можем. Мы должны мониторить в третьей строчке. Пусть у нас третья строчка пустая и причем мы ее сделаем такой отсекающей. Мы сделаем, например, высота строки 7, так чтобы визуально было. И вот она у нас вот здесь. вот такая. давайте подсветим. и мы от нее будем смотреть в мониторе. значит, теперь пишем равняет. если, что мы теперь хотим прописать, если выше, вот здесь вот выше, включая вот эту вот саму строчку, эта строчка присутствует один раз, то тогда мы сюда ставим 1, а иначе 0. понять, что она присутствует один раз. Мы можем суммировать по соседней, вот этому столбцу С. То есть если мы суммируем по столбцу С, по единичкам, при условии совпадения вот с этими данными, вот с этими атрибутами конкретной вот этой вот строки, учетной записи, то тогда мы получим в сумме, ну как бы просуммируем столько единичек, сколько раз она встречается вот здесь вот сверху. Давайте сначала саму эту формулу запишем. Напишем sum, если m, m. Суммируем... А, вы знаете... Да, суммируем, да, суммируем, начиная от c3 до текущей строки, до 11 строки, в которой мы находимся. При этом, значит, что мы делаем? Сначала вот эту вот C3, ячейку C3 мы удерживаем полностью. C1 у нас бежит вниз, но при этом $C мы удерживаем столбец. При условии, при каком условии? Ну, при условии, что у нас вот в этих строчках вот в этих столбцах, да, и вот сверху в этих ячеях в этом столбце. Также мы здесь L3 удерживаем, $L. И происходит совпадение с тем, что стоит вот в этой строке, в которой мы находимся, вот с этими атрибутами. Столбцы мы договорились удерживать. И дальше мы просто-напросто повторяем вот это пять раз. ctrl-v, ctrl-v, ctrl-v, ctrl-v, ctrl-v. И здесь теперь у нас l идет. Теперь для столбка l, m. m. Для столбца m проверяем. Для столбца n проверяем условие. Для столбца o проверяем условие. p, q. p и q. p, q. Вот так. Значит, суммируем по вот этим вот единичкам при условии, что у нас идет совпадение вот с этого. Ну и вот если мы это... Пусть у нас здесь тоже будет девяточка. Если мы это теперь вниз протянем, то вот у нас что получается. То вот, например, вот это у нас здесь встречается дважды. Вот первое вхождение, вот второе вхождение. Вот эта строчка, которая вот здесь, которая договор номер 123, вот я вот сюда скопировал. Здесь вот 2, 3, 4, 5, 5 вхождений. Здесь вот это нолики, да, вот они пошли, 1, 2, 3. А наша задача сделать так, чтобы при первом вхождении мы пишем единичку и потом больше ничего не пишем, потом нуль идут. И здесь мы еще сделаем следующее, что если у нас вот здесь вот оказывается пустота, вот эта строчка, она пустая, если бы казалось. Если у нас оказывается пусто, то, наверное, ничего не считаем. Здесь, правда, вот ноль стоит. Ладно. Потом посмотрим. Вот этими пустыми нулевыми строчками, всегда с ячейками в Excel, иногда возникает кое-какой демарой, но будем потом, по мере того, если будут возникать какие-то проблемы, будем их изучать. Они всплывут, если там что-то у нас где-то, какие-то ошибки будут. Значит, давайте так. Теперь пишем условия. Пишем «если». Если вот эта сумма оказалась равна единице, то есть это первое вхождение, то тогда мы здесь пишем один. Во всех остальных случаях мы пишем ноль. Стопочка закрывается. Давайте посмотрим. Вот мы ее копируем. И вот у нас получается три единички. Три единички. У нас как бы три учетные запись. Одна учетная запись. вторая учетная запись, то есть договор 123, договор 111, а вот это вот здесь вот нули. Ну, нули, нули, как бы предполагается, что мы сюда нули не филюриваем, да, то есть это, ну, работаем не в холостой, но если как бы нули, ну, то есть пусть будут нули, они ничего, в общем-то, нами не дадут. Пока что, ладно, пусть как бы будут. Пока что нам они не мешают, короче говоря. Так. Всегда можно вот так вот это взять и удалить. Причем очень важно, чтобы у нас вот эти вот все формулы, они работали внутри одной строки. Чтобы не получилось так, что если мы удаляем какую-то строчку, чтобы это цепляло еще какие-то другие строчки, чтобы ошибка возникала как будто бы ссылка. То есть на эти строчки не должно быть никаких ссылок идти. То есть если мы что-нибудь удаляем, нам что-то не понравилось, тогда это никак не повлияет на базу данных. Значит, у нас в строке 2 у нас формула есть b, c, d. Вот теперь у нас d, а здесь у нас формула должна быть какая? Копируем. Копируем во вторую строчку специальная ставка формулы. Здесь у нас нолик возникает. Нам нужно, чтобы было пусто. Если это строка 2, мы добавляем префикт. Если это у нас строка 2, то тогда мы пишем пусто. Вот здесь пусто. Есть. Значит, идентификатор мы создали. Идентификатор мы создали. И еще нам необходимо, прежде чем мы к макросу перейдем, нам еще необходимо сделать следующее. Нам нужно идентификатор для формы. То есть если мы в форму вот сюда вносим какие-то тексты, какие-то данные вносим, то нам необходимо, чтобы в каком-то столбце подсвечивались соответствующие учетные данные, соответствующие учетные строчки, которые подходят под введенные данные. Что это значит? Это значит, что если мы сюда, например, написали там «ооо», И если у нас где-то у кого-то присутствует вот это ООО в названии, то напротив таких вот строчек должны появиться тоже единички. То есть идентификатор относительно значений, заданных в форме. Давайте мы так и напишем. Вот здесь идентификатор, здесь напишем F, FID. Это здесь мы создадим идентификатор. Так, копирую специальные форматы. Вот в этом столбце E мы создадим формулу, мы должны придумать формулу, которая будет ставить единички в ячейке, напротив которых находится учетная запись, у которых по совокупности шести вот этих вот ячеек есть совпадение. понятно. сейчас буду прописывать формулу, будет понятно. то есть если мы, например, вот здесь вот напишем вот так вот аа. аа, например, 3а, здесь, например, ds. и здесь как у нас там контрагент называют? давайте вот И ПОК напишем, да, вот здесь контрагент, так вот, ПОКУП. Вот если мы вот здесь вот напишем вот такие вот три текстовых отрезка, да, вот такие три текстовые записи, то по совокупности вот этих вот трех записей у нас должна единичка появиться вот напротив вот этих вот двух строчек. То есть вот здесь вот у нас должна появиться, должны две единички появиться, потому что Вот у этих учетных записей присутствуют в названиях, одновременно в названиях присутствуют три вот таких вот текстовых кусочка. Как это сделать? Делается это следующим образом. Для этого мы как раз-таки вот сюда, вот здесь вот снизу мы сделали вот такие вот пустые строчки. Давайте мы сделаем следующее. Вот здесь вот напишем формулу, напишем равняется. Я сейчас покажу, как это делается, потом объясню еще. Берем вот так вот, кавычка, звездочка, кавычка. В Excel, если идет звездочка и она объединяется в какой-то текст, Если звездочка объединяется в текст, потом дальше объединяем со звездочкой, звездочка означает, что это любые символы. Вот такая запись обозначает для XL. Когда XL работает с текстами, то это говорит о том, что слева может быть любой текст, справа может быть любой текст, а вот это вот внутри, вот здесь, обязательно должен быть текст, который как бы записан. Вот. И вот эта штука, она очень интересно работает вот в таком плане. Вот если мы теперь вот здесь, например, напишем равняет сум. Теперь здесь вот очень интересно. Сум, если mn написан, суммируем, но суммируем по одной ячейке. Вот я вот сейчас вот по вот это вот, по столбцу 1, но суммируем прям вот по... C13, C13. Суммируем по вот этой вот ручейке C13. То есть если мы суммируем, но это ведь SUMIFMN, то есть суммирование с условием. То есть если условие будет выполнено, то тогда будет единичка. Если не будет выполнено, то SUMIFMN даст ноль. Значит, мы вот это вот C здесь тоже удерживаем, толбец C. а здесь пишем при условии при каком условии ну давайте вот напишем при условии у нас по столбцу а при условии что здесь также берем вот так l13 l13 пишем при условии что l13 совпадает вот с этой вот запись вот с этим вот l8 И здесь уже F4 полностью удерживается. И вот что мы видим. Получаем единичку. Вот здесь вот у нас 3a. Давайте вот здесь вот сделаем 2a. Вот так вот. Видите, стало нолик. Если у нас здесь будет много a, то получается единичка. То есть вот такая формула, вот такая конструкция. она позволяет сделать следующее. Она позволяет вот сюда поставить единичку. Если в составе текста, который вот здесь находится, присутствует кусочек текста, который мы внесли вот в эту самую табличку, как вот здесь. Ну и дальше понятно, что мы должны сделать. Нам необходимо вот здесь, везде, вот эту формулу сделать, специально старта формулы. Единственное, что вот с датами начисления и суммами начисления тут надо будет сейчас посмотреть. Я вот сейчас точно не помню, как там по числу. Давайте так. Все, что касается вот этих первых четырех полей, они текстовые. Вот эти первые четыре поля, они у нас текстовые, и по ним все понятно, как нужно работать. То есть мы вот здесь просто добавляем. Мы пишем вот эту точку с запятой, L13, L13, и ссылку вот туда на L8, копируем. И вставляем еще три раза. Раз, два, три. И здесь, соответственно, мы берем... Поскольку у нас синхронизированы столбцы, то KL... Вот здесь мы берем M. Для столбца M. И во вкладке FIN у нас тоже M. L, M, N. n и тоже n. Здесь o столбец, o столбец и там столбец o. То есть просто обычная сумма, если mn и все. И она нам дает либо 1 либо 0. То есть если мы теперь ее вот так вот протянем вверх-вниз, то что она нам показывает? Что вот если у нас вот это вот вбиты аа, дс, покуп, документ. Вот, кстати, если здесь пусто, то он как бы... Если здесь вот смотрите, ну давайте так посмотрим. Если здесь вот везде пусто, везде пусто, то есть никаких данных не вбито, по которым мы хотим отсечку сделать из базы, то видите, везде идут единички. Ну, понятно, вот эти вот занесутся нолики. То есть там, где текст. Это работает на уровне текста. Нолики вот эти – это уже числа. Они как бы здесь не сработают. Но на уровне текста мы делаем вот эту вот отсечку, вот эти вот как бы единицы. Если мы… Давайте попробуем вот здесь вот сделать вот документ, например, вот если 1.1, например, вбить, то видите, вот у нас в договоре вот это 1.1 присутствует только вот здесь, вот в этих строчках. И вот она, единичка, нам отсекла. То есть получается так, что по вот этим вот самым единичкам мы отсекаем, вычленяем только лишь те учетные записи, которые условия, на которых мы задаем форме. То есть если мы вот здесь вот начинаем вводить какие-то... если мы здесь начинаем вносить вносить те или иные текстовые фрагменты из наших учетных записей для того, чтобы сделать отбор учетных записей то тогда мы придумали механизм. У нас, получается, в столбце E придуман механизм, который вычленяет из всей базы именно те учетные записи, которые соответствуют вот такой комбинации текстовых фрагментов. Ну что ж, уже хорошо. И далее... нам необходимо сделать так, чтобы у нас вот здесь где-то снизу, например, 21 строчка, давайте вот с 21 строчки нам нужно сделать так, чтобы у нас вот сюда как раз-таки подтягивались вот эти вот самые выбранные, отобранные учетные записи. Вот вопрос, как их сюда подтянуть. Опять же, это несложно сделать. Я, по крайней мере, обычно использую следующий подход. Мы просто возьмем и перенумеруем. Мы перенумеруем сейчас вот эти вот единички. Здесь просто еще есть один вопрос. Это, видите, у нас толпца E, единички, которые соответствуют вот этим учетным записям. Но поскольку у нас вот эти вот самые учетные записи могут повторяться, для разных траншек, так сказать, то тут у нас возникает такой естественный достаточно вопрос, а нам вытягивать сюда необходимо что? Нам вытягивать сюда всю информацию или только один раз? Если мы всю сюда информацию вытягиваем, то у нас сюда вытягивается повторение. у нас будет повторяться вот эта вот самая учетная запись, и вот эти вот даты и суммы начисления здесь будут как бы идти те, которые нестины. В принципе, нормально? Да, нормально. Ну, допустим, мы будем сюда подтягивать. А, следовательно, вот в этом столбце f мы можем сделать просто такой вот f номер. Для нашей формы мы введем номер, нумерацию такую введем, которая будет вот что делать. Здесь специальная ставка форматы. Мы пронумеруем сверху вниз все те учетные записи, которые у нас отобраны. То есть вот эти вот там, где есть единички, мы, соответственно, будем это... Как это сделать? А, так, давайте вот здесь у нас... нам нужно вторую строчку тоже оформить. мы берем вот эту формулу, копируем, специальная вставка формулы, и здесь также пишем префикс, что если мы находимся в скроте номер 2, то здесь должно быть пусто. вставляем сюда, равняемся. если мы находимся в скроте номер 2, то у нас будет пусто. теперь вот в столбце f нам необходимо пронумеровать отобранные учетные записи. Но они, поскольку это единички, ну мы просто можем суммировать эти единички, да и в общем-то и все. То есть мы здесь можем написать так. Равняется сумма M до суммы. И начиная вот здесь вот с третьей строчки по вот текущую строчку суммируем. E3, F4 удерживаем. И столбец E тоже как бы. Нас удерживает. Кнопку закрываем и протягиваем вниз. Получается 1, 2, 2, 2. Тогда нам нужно, чтобы не повторялось, мы пишем, так, равняется, если у нас в соседнем, слева в соседнем столбце, 0, то тогда мы и здесь пишем 0, а иначе мы берем эту сумму. Соответственно, здесь столбец E тоже удерживается. Enter. Протягиваем. Ну и вот мы пронумеровали. У нас есть номера строк, которые мы хотим увидеть, подсветить, то есть вытянуть. Если мы это желаем вытаскивать в 21 строчку, то давайте мы вот здесь возьмем какой-нибудь столбец. Допустим, раз, два, три. Вот так вот. Столбец i. Я его сделал, ширину столбца сделаю 1. Мы его расширим. И вот в этом месте я напишу, пусть будет равняется, максимум. Цепляемся за 20 строчку. пишем максимум плюс один. удерживаем вверх и едем вниз. вот так вот один и протягиваем. здесь мы, например, можем сделать все в десятом шрифте, например. толбец вот этот давайте сделаем в девятом. вот у нас пошла нумерация. Если это номерация, если это номера вот этих вот самых строчек, которые вот здесь, вот эти номера, то через номера, через с помощью функции индекс мы спокойно можем вытянуть всю вот эту вот информацию, которая находится напротив вот этих номеров. Значит, внутри групп, статья там и так далее. Как нам это подтянуть сюда? Пишем равняется. И берем функцию индекс. Индекс. Вытягиваем. Что нам нужно вытягивать? Внутри групп, то есть название наших компаний. Берем столбец, нам нужно из столбца L подтянуть через индекс, подтянуть то название, которое находится напротив вот этой вот единички. Вот этой вот единички. У нас там один стоит. Для этого нам нужно найти номер строки. Номер строки вот в этом массиве. Номер строки, да, вот написано номер строки, где находится вот эта единица. То есть мы пишем sum, если m. Мы суммируем номера строк. Здесь вот это 22, оно у нас всегда вот там, оно никогда нам не помешает. Поэтому суммируем, например, по столбцу A. Можно, конечно, и по столбцу B. В принципе, можно и по столбцу B. Давайте пока что по столбцу A. Хотя... Нет, давайте по столбцу B. По столбцу B здесь прям вот у нас конкретно номер строки, и здесь ничего нет. Вот это вот номер, мало ли, что-нибудь куда-нибудь там помешает. Давай так, sum, если m, n. Мы суммируем по номерам строк, ищем тот номер строки, напротив которого при условии, что у нас в столбце f номер совпадает с тем номером, который находится вот здесь, в этом месте. Ну, а здесь, соответственно, столбец i держим. Вот эту вот ссылочку на фильм можно убрать. Стопка закрываем, стопка закрываем. Единственное, что нам здесь нужно сделать одно условие. Нужно написать, что если у нас вот эта сумма равняется нулю, то тогда мы здесь пишем пункт. Ну, а иначе вот этот самый in. Вот оно у нас подтянулось. Теперь мы это все смещаем вот сюда. И смещаем вот сюда. И вот у нас вот здесь везде вот эти вот самые формулы находятся. Вот здесь можно сделать пошире, чтобы… Ну сколько-то мы вот сюда делали, 15 строк. Понятно, что когда мы подтягиваем, нам слишком много тоже не надо. Понятно, что вот здесь у нас даты идут. Давайте так запишем здесь. Толбец с датами начисления. Пусть будет так. Формат ячеек. Дата. Во всем столбце есть сумма начисления. Делаем формат ячеек. Числовой. Разделитель групп. Хотя давайте два знака без записей возьмем и посмотрим. Надо, не надо. Текст. Значит, вот таким образом подтягивается. Теперь смотрите, вот я беру вот здесь вот все удаляю, и у нас вся база сюда вылезает. Вся база вылезла, вот она перед нами. Теперь мы говорим, слушайте, ну, хотим только там, не знаю, вытащить там клиента номер два. Вот давайте мы вот здесь номер два, берем номер два, и нам база, ну, как бы из базы мы делаем выгрузку только по клиенту номер два. ну вот так если нужно покупатель да у нас был покупатель вот у нас появляется только по покупателю понятно что мы сюда вот право можем в том числе подтягивать и вот эти вот доли там и так далее ну таким же образом А вот если мы вот сюда вот вправо, вот они, 0.4, минус 5, ну, там вот эти дела. Пока что не будем. Сейчас вот эта вот ситуация понятна. То есть у нас появился механизм, с помощью которого мы можем вот сюда вытягивать какие-то названия учетных записей, их будет становиться меньше существенно, и потом мы можем там уточнять и отсекать, так скажем. То есть, если мы, например, вот здесь напишем дог, договор дог, то у нас все как бы присутствует. Если мы, например, потом мы смотрим и такие говорим, так, нам нужен только клиент номер 2. Мы добавляем номер 2, и у нас отсекается только вот этот клиент номер 2. Дальше мы можем создать кнопочку, кнопочку, нажимая на которую у нас будет, ну, то есть, если мы видим, что у нас В итоге сюда вылезла только одна учетная запись, которая дублируется только лишь по транше, с разными траншами. Мы, например, нажимаем на кнопочку, и мы можем сделать макрос, который нам вот сюда заполнит все вот эти названия. И таким образом у нас будет работать механизм вытягивания из базы данных необходимой нам вот этой вот учетной записи, с которой мы хотим, например, что-то делать, добавлять условия, например, новые по оплате или редактировать и так далее. Единственное, что у нас тогда должна быть функция «добавить в базу», и отредактировать, то есть у нас должна быть возможность в эту вторую строчку или в ту строчку, номер которой здесь указан, добавить новую учетную запись, но также, если мы подтянули какую-то учетную запись и какие-то сделали по ней редактирование, какие-то корректировки внесли, то по данной учетной записи у нас тоже должна храниться информация о номере, о номере той строчки, в которой находится эта учетная запись. И, соответственно, если мы, например, добавляем макрос, на который мы, например, нажмем «Откорректировать» или «Внести изменения в базу», то тогда у нас должен быть другой макрос, который возьмет соответствующий номер строки, и вот эту запись, которая вот здесь вот образуется, несет именно в ту строчку, которую нам нужно. Ну, чисто теоретически, да, вот я вот этот алгоритм рассказал, он, похоже, имеет место быть, вот, и, наверное, это можно будет сделать. Так, уже хорошо. Так, ну, давайте теперь Ну и еще давайте посмотрим, что нам здесь нужно. Так, какую-то аналитику мы уже, наверное, вынесем за пределы этого семинара. А сейчас давайте мы все-таки отредактируем вот этот вот макрофон. Макрофон с добавлением в базу. Что мы сделаем? Мы идем в разработчика и в макрофон. Давайте мы удалим этот макрос. Удалить. Почему? Ну, во-первых, этот макрос работал с вертикальным этим. Он там вертикально еще и удалял. Мы сейчас делаем так. Ну, очищал нашу вот эту вот ячейку для вноса ввода данных. Мы их не будем удалять, потому что сейчас мы делаем все таким образом, то, что находится во второй строчке, не мешало нам. То есть, чтобы вторая строчка нам не мешала. Если она нам мешать не будет, то тогда нам очищать специально не нужно вот эти ячейки после добавления в базу. Мы можем просто здесь сделать кнопочку, просто очистить. Вот здесь, например, вот в этом месте где-нибудь, вот здесь просто очистить. Нажимаем на нее, и здесь просто очищается все то, что здесь есть. это чисто так же. можно взять, выделить, а можно сделать кнопочку для очистки. секунду подождите. Так. Удалили макрос. Давайте мы сейчас разберемся, как создать макрос, который будет вставлять скопированную вторую строчку ровно туда, куда нам нужно. То есть тот номер строки, который у нас тут находится. Вот здесь вот A1. Для этого мы сначала сделаем следующее. Давайте мы внесем какое-нибудь там опять ООО, пусть будет БББ. Статья ДДС, пусть будет опять поступление. Понтрагент, пусть будет ООО Звездочка. Какой-нибудь договор придумали. Договор номер будет 555. Дата начисления будет 28 сентября 2025 года. Сумма начисления 218 тысяч рублей. ну, какие-то балды пишем. Так, ну, понятно, вся вот эта информация, она у нас перемещается вот в эту вторую строчку. Доли пока что мы не пишем, нам это неинтересно. Мы сейчас вот как бы, ну, в принципе, ладно, пусть будет там какая-нибудь доля, например, 30% за 10 дней доли, да, пусть будет. Мы видим, что оно, да, вот сюда у нас подтягивается, ну, и пусть будет. Это как раз-таки ничего страшного, нормально. Нам важно сейчас сделать так, чтобы вторая строчка перекочевала в 22, а потом нам нужно таким образом будет поправить макрос, внести туда вручную изменение, так чтобы у нас строчка, в которую мы будем вставлять данные, вставлять копирование, чтобы... О, а что это у нас вот здесь? Здесь нули, а здесь… А, здесь у нас… Это я когда копировал… Да, давайте вот это удалим. Здесь просто… Ну, это я просто так копировал и немножко нарушил эту тему. Давайте вообще вот эти нули тоже удалим. Вот. И вот эту пустоту ударим. Во-первых, посмотрим. Все везде работает нормально. Вот. Теперь у нас, видите, здесь в ячейке А1 у нас находится номер 18, то есть вот 18-я строчка. Но мы сейчас как раз туда сначала просто прямым методом вставим, а потом надо будет разобраться, как в макросе сделать какие-то корректировки так, чтобы макрос читывал ячейку А1. Ну, давайте проговорим, значит, что у нас должен делать макрос. Значит, после того, как мы нажимаем на кнопку добавить базу, наш макрос должен зайти во вкладку in, выделить вторую строчку, копировать ее и просто сделать специальную вставку, ставить все. А, или нет, как значение. Как значение вставить. Как значение вставить. После чего нам необходимо взять 10 ячеек строчки 2, копировать и сюда их вставить, специальная вставка, как формулы. Вот так, как формулы их вставить. И на этом все. И вернуться обратно. Так. Ну, наверное, да. И причем мы вот эту строчку, да, мы ее должны вот здесь вот увидеть. Сейчас ее нет. Это мы новые данные внесли, поэтому вот здесь вот она не отображается. Здесь, кстати, надо будет, ну, потом посмотрим, какие-то факультаты сделаем, да, отключать, где что у нас, где форма заканчивается, где начинается вот эта таблица подтянутых данных и так далее. Ну что, в принципе, мы готовы все это делать. Давайте вот так, чтобы ничего не выделялось. Аккуратненько начинаем записывать наш маркер. Верхняя часть зуба. Давайте я, во-первых, сохраню файл. И теперь начинаем. Разработчик. Запись маркера. Имя макрос. Добавить в базу. Английский. Добавить в базу. Базу. Ну, давайте in запишем. Добавить в базу in. Нажимаю на окей, и сейчас нужно будет после этого сделать все вот эти движения, все операции, как мы все проговорили. Окей. Идем в in. Выделяем. вторую строчку, копируем, выделяем 18-ую, делаем специальная вставка значения. Вот у нас появилось это значение. Теперь выделяем ячейки до толста j, копируем, идем в 18-ую строчку, делаем специальная вставка формулы. Переводим курсор в ячейку А1, просто обратно его здесь отнести в А1. Видите, здесь, кстати, 19 появилось. То есть 19-я строчка стала следующей пустой. И возвращаемся вот сюда. Кстати, здесь у нас почему-то не отобразилось. Сейчас будем разбираться. и разработчик остановить запись. Запись остановили. Единственное, что интересно, почему здесь... Так, вот в этом месте у нас единичка, а здесь у нас... А, мы вот сюда забыли сделать формулу. То есть у нас столбцы A есть, B есть, C, D, E, F, G. Так, F, A. Ну да, вот эту формулу копируем. Сюда специальная вставка формулы. И нам нужно вот этот преференс добавить, что если строка 2, то копировать. Сюда добавляем, вставить. Если строка 2, то ходку закрываем. И теперь докопируем вот эту вот... Здесь была пустота, поэтому вот она и сюда пустота встала. Нам нужно, чтобы было, чтобы она сюда встала. Вот она, единичка. Единичка, которая перенумеровывает отобранные формой строчки. Заходим сюда, и вот она здесь появилась. Я просто посмотрел, вот эта строчка не появилась, а это значит, что что-то неправильно у нас здесь работает. Макрос правильно работает, но просто мы сюда, во вторую строчку, мы не добавили формулу с толста f. Теперь добавили, все как бы работает. Давайте сохраним все то, что мы сделали. И теперь самое интересное. Давайте еще раз заходим в разработчика, в макросы заходим. Вот она, войти в макросы. И вот мы видим, он очень простой. Тут ничего сложного нет. Единственное, что здесь... Вот она, вторая строчка копируется в восемнадцатую. Нам нужно вот чтобы вместо восемнадцати здесь отображалось номер строки, вместо восемнадцати отображался номер строки везде. Отображался номер строки, который указан. в ячейке а1. вот. я не помню как это все делать и сколько скоро у нас сейчас везде искусственный интеллект. давайте прям так и зададим. я прям пишу. как в макрос в б а эксель в переменную ну, какой-нибудь там перемен, пусть будет перемен, пусть будет nctr, номер строки, переменную nctr, как в macros, vba, переменную nctr, добавить, поместить, присвоить значение, которое находится в ячейке. В нашем случае в ячейке А1. В ячейке А1 листа. Какой у нас там лист? Листа in. Листа in. Как в Macros VBA, кстати, переменную MCTR присвоит. Переменной. Переменной. MCTR присвоит значение, которое находится в ячейке a1, листа in. Как-то так. Попробуем, посмотрим, что нам скажут. Короткий ответ. dim nctr s3, то есть определяется переменная nctr. Потом мы берем thisbook. вкладку in и range a1. Отлично. То есть вот такую вот штуку нам нужно списать. Берем вот эту вот штуку, копируем. И идем в макросы. Разработчик, макросы, вайти. Давайте здесь в самом начале именно это и пишем. НСТР равняется... Так, переменную НСТР добавим. Теперь нам нужно вот сюда добавить вот этот вот НСТР. Теперь пишем. Пишем, как в команду вот такую добавить... добавить... в такую команду вместо восемнадцатой строки вместо восемнадцатой строки использовать значение из переменной n, c, d, r. Вот так вот он и пишет. Просто вместо 18 поставить nctr. Как в команду, так. Можно подставить значение переменной rows двумя простыми способами. Если nctr число, то достаточно просто rows nctr. Или вот так, ncts. Если ncts строка, например, 18, можно вот так. Тут nctr как число, nctr as long, nctr равняется... выбирает 18 строчку. У нас, соответственно, nctr нужно вот таким вот образом использовать, копировать, и идем в наш макрос. У нас получается... Хорошо, у нас nctr.string, если у нас это string, то тогда нам нужно... давайте вот так это поставить, nctr.string. Ну и по аналогии, наверное, вот здесь где у нас a18, нам нужно и то же самое с этим. Давайте прямо напишем, чтобы пусть прямо он нам ответит. Чтобы никаких ошибок не было. Как мы здесь поставили? Как команду. А, и вот здесь. И здесь мы пишем другую команду. Как команду теперь. Вот это вот. Ctrl-X, Ctrl-V. И здесь тоже. Ну и, в принципе, все понятно. Так, сейчас. Совет присоединяется. Краткий ответ. А N density. Вот так. Давайте попробуем. Ну, в общем-то, и все. Теперь проверяем. Сохраняем. Сохраняем. Закрываем. Теперь нам нужно, чтобы у нас в девятнадцатую строчку залетело следующее нажатие. Так, окей. Заходим сюда. Добавим базу. Мы теперь назначаем макрос добавить базу. Окей. сюда ну давайте теперь берем о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о-о RomSnapPS77, дата начисления, пусть будет 30 сентября и 500 тысяч. Вот так. Пусть будет доля 60% за минус 14, предоплата. Так, давайте сохраним. Еще раз сохраню, чтобы все везде поменяли. И нажимаю. Добавили базу. Смотрим. Заходим в In. И оно в девятнадцатую строчку поместило PromSnap. Отлично. Единственное, давайте здесь сделаем формат ячеек. Время, чтобы нормально выглядело. Так, дата. Так, дата, время. Окей. Чтобы вот эти вот. И мы видим, что 26.09.13.39 была внесена вот эта запись. О, звездочка. А вот сейчас то, что мы уже внесли с исправленным макросом, у нас сюда вот, кстати, вот эта единичка сюда вошла, и мы видим, что у нас OOBBB, Chrome Snap, и вот он, Chrome Snap, здесь отображен. Ну и давайте еще сделаем макрос очистки. На этом на сегодня мы очистим. Пусть оно у нас поменьше будет. Будет именно приворочено вот сюда. То есть очистить вот это все. Можем даже вот так сделать. Очистить. То есть очистить вот то, что слева. Некрасиво. Очистить то, что слева. Макрос очень просто сделать такой для очистки. Мы можем... Заходим в разработчика, макросы. Разработчик. Запись макроса. Допишем. Очистить им. В форме для поступления. Нажимаем ОК. Ну и здесь, в принципе, можно прям вот так. Всю строчку выделим. Только надо будет не забыть, что здесь вся строчка ощущается. Хотя, может быть, и не стоит так. Давайте так. Надо просто помнить, что вся седьмая строчка ощущается. Нажимаем на дилейт. Ставим курсором куда-нибудь сюда. И разработчик остановить. Матрас. И здесь настраиваем. назначить макрос, макрос очистить. Ну и, например, здесь берем звездочку. Мы вот даже зв вот так вот пишем, и вот она у нас, звездочка, уже подтягивает. Очистить. И вот очищаем, и вся база данных перед нами. По крайней мере, ее начальная часть. Понятно, что мы вот берем здесь первые 15. Можно побольше взять. внутри здесь допустим берем бэбэбэ да это и вот у нас две записи о бэбэбэ веточка просто те которые вот так вот все это работает очистить в базу добавить пусть у нас будет там ооо а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а-а вступление денежных средств. Вот это все я скопирую, вот отсюда специальная вставка значения. Только здесь возьмем клиент номер 2, договор 1, 2, 3, допустим, 1, 2, 3, 4, 0, 5, 10, допустим, на 10, 10, еще кто-нибудь. Договорились. Не на 250 тысяч, будет на 300. И вносим базу. Добавить в базу. И мы видим, вот оно сюда сразу же подтягивается, но подтягивается откуда? Из базы. Значит, в базе появилось. Смотрим сюда и видим, что в 20-ю строчку добавилась новая запись. Быстренько, хорошо, красиво все добавляется. Наша вот эта конструкция с ячейкой А1 работает. То есть в ячейке А1 отображается следующая пустая строка, и туда у нас добавляется. То есть мы научились вставлять вторую строчку ровно туда, куда нам это нужно. Ну, понятно, что если мы теперь подтянем сюда какую-то запись из базы и захотим ее отредактировать и внести обратно в базу, если мы будем в какой-то ячейке содержать ее номер, номер этой записи, то мы туда целевым образом сможем это дело вставлять. Ну, все, все работает, все хорошо, красиво. Так. Давайте-ка мы на этом сегодня завершим, и уже на следующей встрече будем все это дело, ну, еще дорабатывать дополнительный функционал. Но постепенно-постепенно мы почти создадим неплохой инструмент прямо в Excel. На этом мы сегодня завершаем. Всем спасибо. До свидания.