Libre Calc

Эй-эй! Искать в последующие дни — почему нет? Счёт выставили в четверг, в пятницу начальник плательщика свалил на рыбалку, в понедельник был с бодуна... оплатили во вторник. Всё честно.

Я написал как сверить даты (в том числе если отличаться могут в любую сторону). Сортировать для этого не надо.

«Моё» решение имеет некоторое количество пользы. В результате его использования можно прямо в ёкселе/кальке получить таблички со списком точно неоплаченных счетов и каких-то непонятных оплат.

Сделав pivot по слепленным округлённым датам/суммам можно сразу увидеть сколько множественных совпадений следует ожидать.

Если вместо vlookup использовать match, то напустив pivot на результат получится выделить множественные совпадения. Небольшое их количество можно обработать вручную или вытащить в отдельную таблицу и в диапазон поиска для match добавить значение найденное в предыдущей строке, таким образом искать будет «следующее похожее».

Решение дающее миллион бесполезных строк годится только для того, чтобы «заказчик» им подавился.

Или что надо делать?

ВОТЫМЕНО!!! =)

Увидь за скупыми цифрами всю щедрость человеческой души.
Ты решаешь задачу типа «есть два набора кортежей, для каждого элемента из первого набора найти все похожие элементы во втором, где похожесть описывается следующими условиями... и ожидается значительная доля 1:M с большими M».
Можешь предложить практическую задачу, где это было бы надо?

Ну я не знаю. Что-то у тебя как-то сложно всё. Я, если честно, даже толком врубиться не могу после трудового дня.

Я написал как сверить даты. Сортировать для этого не надо.

Ну в итоге-то ты ведь их все сверяешь, каждую с каждой, да? А как ты это в екселе делаешь? Или все же просто ищешь первое совпадение? Оплатили два человека одинаковые счета в один день. Что выдаст твоё решение в этом случае? Одну и ту же проплату для обоих или по две проплаты на человека?

=mround(<дата>, 4) и =mround(<дата> + 3, 4)

Совпадение любого из этих двух чисел в одной строке с любым из двух в другой даст строки с датами отличающимися не больше чем на 7 дней.

Давай возьмём две даты: 2 и 6. В нормальном мире их разница составляет 4 дня, что меньше 7. По твоей формуле эти два числа дадут пары (4, 4) и (8, 8). Как видим, ни одно из чисел в первой паре не совпадает ни с одним числом из второй. Это значит разница между датами больше семи или что?

наконкатенировать четыре столбца с комбинациями

округлить в две пары значений и слепить

Сделав pivot по слепленным округлённым датам/суммам

Вот это я вообще не понимаю. Дальше тоже не идёт, где про «суммированием выделяем строки»… Извени.

Небольшое их количество можно обработать вручную

Да проще уж тогда на вбасике вложенный цикл написать. Завтра у тебя критерий поиска изменится, ты перелопачивать всё будешь?

Что выдаст твоё решение в этом случае? Одну и ту же проплату для обоих или по две проплаты на человека?

Моё «решение» выдаст, что для данных двух счетов есть хотя бы одна «похожая» проплата.

Допустим, выставлены счета на 100к, 100к, 200к и 300к. Имеются две оплаты — 102к и 280к.
Применение предложенного варианта в обе стороны покажет, что для 100к-шных счетов «что-то такое было оплачено», что счета на 200к и 300к оплачены не были и что была какая-то мутная оплата на сумму слишком отличающуюся от выставленных счетов. В рамках предложенных условий определить какой именно из двух счетов на 100к был оплачен не представляется возможным.

Вместо vlookup можно влепить count(iferror(search ...), ""), тогда увидишь сколько для данного счёта нашлось похожих оплат.
Если счета «кластеризуются», то можно попробовать свернуть всё несколькими pivot-ами и сравнивать число свёрнутых счетов с числом похожих на них оплат.

Вот только... ЗАЧЕМ? =)

Давай возьмём две даты: 2 и 6. В нормальном мире их разница составляет 4 дня, что меньше 7. По твоей формуле эти два числа дадут пары (4, 4) и (8, 8).

Это как было подсчитано?

Вот что мне выдаёт excel:

2	4	8
6	8	12

Вот это я вообще не понимаю.

Ничего, так бывает.

Завтра у тебя критерий поиска изменится, ты перелопачивать всё будешь?

Зависит от того как изменится критерий. В формулы ничего не мешает вставить значения расположенные отдельно в «служебной» строке. И сделать не 7 дней, а 3 или 15 и не 5к разницы, а восемь простой заменой чисел в нужных ячейках. Более того, если есть не «дата» транзакции, а «дата и время», то mround позволяет написать что-нибудь вроде «4:30:00» и округлять время до четырёх-с-половиной-часовых интервалов. («Потому что так тоже можно»).

Да проще уж тогда на вбасике вложенный цикл написать.

А это зависит.
Подавляющее большинство пользователей электронных таблиц не только не сможет ничего написать, но даже и готовый макрос не справится запустить. А всё от того, что «реальные повседневные задачи» делаются легко и относительно удобно без всяких скриптов.

Можешь предложить практическую задачу, где это было бы надо?

Легко. Найти все платежи клиента меньше 5к за неделю после его регистрации.

Зависит от того как изменится критерий. В формулы ничего не мешает вставить значения расположенные отдельно в «служебной» строке.

И будет у тебя служебная строка через служебную строку, костыль на костыле и функцией костыля подпирать. Когда что-то пойдёт не так вообще никто не захочет разбираться что ты там наваял в своих формулах. В итоге получается намного дороже, чем взять ПТУшника, чтобы он приделал клиент к рсубд и накидал несколько запросов.

Если ты не видишь разницы, то наверное дальше лучше не продолжать.

И будет у тебя

Не будет. Заведи в таблице отдельный лист, в одном столбце подписи, в другом значения.
С другого листа ссылайся на нужные ячейки. Если так прям хочется нагородить — вместо ссылок на ячейки используй vlookup по подписям с выбором значений.

В итоге получается

.. что юзер умеющий пользоваться электронными таблицами может решать свои типичные задачи самостоятельно без наличия навыков работы с БД, программирования или общения с юными гениями из ПТУ.

P.S. Впрочем, изрядная доля пользователей максимум чего может — добавить автофильтр.

Не будет. Заведи в таблице отдельный лист, в одном столбце подписи, в другом значения.
С другого листа ссылайся на нужные ячейки.

Ты правда считаешь, что проблема в том, на каком вся эта срань листе?

что юзер умеющий пользоваться электронными таблицами может решать свои типичные задачи самостоятельно без наличия навыков работы с БД, программирования или общения с юными гениями из ПТУ.

Зато ему на каждую микрохотелку нужен гений екселя и вба, которого он хер где найдет ибо это всё дикий колхоз за копейки без внятного результата и с проблемами в будущем. Потому что мс хотели сделать херню для бухов, но бухам это оказалось слишком сложно, а программистам слишком тупо.

Ты правда считаешь, что проблема

Я не считаю это проблемой.

Я не считаю это проблемой.

Субъективные оценки никого не волнуют. Ексель объективно не может в реляционные отношения, следовательно весь опыт человечества по обработке реляционных данных, который является самой формализованной вещью в ит, идёт под сраку в угоду решений через жопу на формулах и ссылках на ячейки, которые в здравом уме всерьёз нигде не применяли и применять не будут, ввиду крайней ущербности базового теоретического аппарата.

Давай возьмём две даты: 2 и 6. В нормальном мире их разница составляет 4 дня, что меньше 7. По твоей формуле эти два числа дадут пары (4, 4) и (8, 8).

Это как было подсчитано?

Я считал в уме, по доке МС:

MROUND rounds up, away from zero, if the remainder of dividing number by multiple is greater than or equal to half the value of multiple

2 % 4 = 2, что >=, чем 4/2, округляем до 4
(2+3) % 4 = 5 % 4 = 1, что <, чем 4/2, округляем до 4

6 % 4 = 2 => 8
(6+3) % 4 = 9 % 4 = 1 => 8

Вроде логично. Там примеры даже есть:

=MROUND(10, 3)   Rounds 10 to the nearest multiple of 3.    =9

В твоём варианте получается округление всегда только в большую сторону. И даже если так, то возьми даты 5 и 9, получатся пары (8, 8) и (12, 12), да? А если нет, то возьми даты 4 и 8.

Сейчас в каком-то онлайновом екселе с поддержкой .xlsx проверил, считает по доке.

Кто врёт? Скриншотик из оригинального екселя можно посмотреть?

Ексель объективно не может

кококоооо

зачётно неуча порвало

Вот что мне выдаёт excel:

Ну што, проверил я на оригинальном екселе. Как ты будешь оправдываться теперь?

Ах, ты ж… У меня там ceiling затесался вместо mround.

На 5 и 9 пары у меня получаются другие (может ещё не проснулся). Но тем не менее: был не прав, погорячился. При таком упрощении +/- три дня будет для каждой четвёртой даты, а между ними будет от -4/+6 до -6/+4. («Поначалу-то, сперва» я влепил больше mround-ов, но потом несправедливо оптимизнул…)

Если хочется конкретных и/или разных смещений, придётся лепить match(1, («дата» - «дельта1» <= y:y) * («дата» + «дельта2» >= y:y), 0), затем аналогичный для сумм и затем оба их заворачивать в ещё один match и делать это array formula (чёрт его знает как это на русский перевели).

Для даты, я так понял, есть DAYS(), который возвращает разницу дат в днях. Для сумм так же. Просто заверни разницу в ABS() или что там у вас.

Ладно, я так понял нормального решения, чтобы ничего там никуда не затесалось, в екселях не сделать. Отписываюсь.

https://wikiroot.ru/question/dekartovo-proizvedenie-v-excel-2013

Выделите.
Перетащите

Охерительное декартово. Перетащи на 100500 колонок. Будут новые данные перетащи еще раз. Наклади вбатоты сверху, чтобы работало. Если всё это уметь, можно и на пистоне/sql сесть накодить что надо.

неуча

А нахера разбираться в этом не нужном говне? Есть намного более удобные и вменяемые инструменты, которые уже много лет просто работают без плясок и приседаний. Решение на sql было написано еще в начале треда, а адепты екселя всё еще пытаются что-то сделать на формулах, лул.