Подсчёт остатка чисел

Автор Mary1984, 15 октября 2019, 11:22

0 Пользователи и 1 гость просматривают эту тему.

Mary1984

#30
Цитата: economist от 16 октября 2019, 21:45
Mary1984 - если столбцы могут прыгать, то это очень суровый довод за использование базы данных, скажем на основе CSV. Только БД "прощает" произвольную смену порядка столбцов и добавление новых.  

Реализация на формулах на одном листе - будет как раз с очень сложными формулами. Работать с ними будет комфортно до 3-5 тыс. строк и то с большой натяжкой.

Конечно, если данных немного и схитрить - можно сделать простые формулы, разнести по листам приход и расход, а остатки вынести вверх листа, закрепить область и получить результат простыми быстрыми формулами вида =СУММЕСЛИ() для каждого вида ящиков и просто вычтя из прихода - расход.  
С трудом понимаю написанное) Нет нужды работать на одном листе, столбцы могут прыгать при этом в остатках я хочу отсортировывать ящики и фрукты по своему усмотрению.
Прирост данных будет постоянный, количество ящиков 300-500 в перспективе, фруктов до 50 и 150 их видов. 500 не 5000 далеко.
Возможно ли использовать здесь как-то сведение таблиц? Оно ищет нужные строки по нескольким критериям из этой строки и распологает столбец с видом фруктов в родительском классе(column fields). Может его можно заставить принудительно присваивать подклассу основной класс? Т.е. назначать виду фруктов родительское значение что бы оно было уникальным(яблоко+красное) и далее вычитывать первым способом hlookup


economist

Консолидацию и Сводные таблицы из Calc использовать можно, но до них важно придумать максимально простую форму хранения/ввода, поскольку переделывать будет труднее, чем делать заново.

Ящиков будет только 2 вида (дерево/пластик), или м.б. другие виды "фасовки"? Что за "виды фруктов"?
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Mary1984

#32
Цитата: economist от 16 октября 2019, 22:54Ящиков будет только 2 вида (дерево/пластик), или м.б. другие виды "фасовки"? Что за "виды фруктов"?
Давайте по принципу сравнения) "Ящиков", как уже упоминалось выше, будет 300-500шт, их можно сравнить с магазинами. фрукты имеют разные сорта. яблоки - антоновка/голден/симиренко, апельсины - крупные/мелкие/для_сока. В каждом магазине продаются эти фрукты и что бы не запутаться все "яблоки" хранятся на поддоне с яблоками, "апельсины" на поддоне с апельсинами. Но что бы не запутаться в отдельном поддоне - фрукты разделены по сортам в отдельные коробки(ячейки). Необходимо вычислить остаток конкретного вида фрукта на конкретном поддоне, во всех магазинах.
Добавлю что поддон с яблоками может быть во всех магазинах и сорта яблок на этих поддонах тоже могут быть одинаковы.

economist

Нужна таблица с рабочим примером всей нужной аналитики.
Я насчитал уже 5 сущностей.

Один и тот же поддон не может быть во всех магазинах. Если это ячеистый склад, скажем, в каждом магазине есть холодильник, оперсклад и прилавок - то так их лучше и называть.
А еще лучше уникальные имена. То есть Магазин1хол, Магазин1опер итд. Да, может получиться большой список, но комп все стерпит.

Очень легко в постановках таких задач угодить в т.н. XY-проблему. К примеру вам нужно решить проблему X, но её вы не озвучивате, а просите помочь с проблемой Y (которая на ваш взгляд точно решает проблему X), причем методом Z (формулами Calc). Возможно поэтому мы и плохо понимаем как вам помочь.

Для чего нужно видеть остатки? Для логистики перемещений, чтобы поддерживать ассортимент? Почему это не сделать в имеющейся БД. Что за БД, которая не может из всех приходов вычесть все расходы и выдать остаток?
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Mary1984

Сущности всего 3 - ящики/магазины, фрукты/поддоны, их сорт/виды. Ящиков много а фрукты одинаковые. csv файл содержит строки в каждой из которых: номер ящика/магазина, фрукт/поддон, сорт/вид, количеством фруктов в этой выгрузке выступает количество строк. Каждая проданная единица записывается в виде строки которая содержит: ящик/магазин, фрукт/поддон, сорт фрукта/вид. Не представляю какие ещё таблицы загрузить т.к. все реальные таблицы загружены ранее. Могу всё сразу в одном файле загрузить и опишу ещё раз что к чему:.
страничка 1) содержит выгрузку из БД, для наглядности выделены строки которые учавствуют в примере.
страничка 2) содержит сведённую таблицу из csv на 1) странице. Для наглядности выделена ячейка которая учавствует в примере.
страничка 3) содержит целевую таблицу остатков которая заполняется вручную, из неё нужно вычесть то что в 1) либо во 2) страничке. Для наглядности выделена ячейка которая учавствует в примере, обратите внимание что её координаты не соответствуют координатам в сведённой таблице, т.к. есть нужда в сортировке ящиков и фруктов.
Исходя из этого предпологаю что формула не может учитывать перемещение ячеек с названием фруктов и их сортов но может учитывать премещение ящиков. В сортировке нет особой нужды но я хочу подчеркнуть то что в БД будут добавлятся фрукты и их сорт, а это возможно изменит расположение столбцов при сведении, так же изменится сама таблица остатков.

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

Цитата: economist от 17 октября 2019, 10:03Для чего нужно видеть остатки? Для логистики перемещений, чтобы поддерживать ассортимент? Почему это не сделать в имеющейся БД. Что за БД, которая не может из всех приходов вычесть все расходы и выдать остаток?
Остатки нужны для логистики что бы поддерживать ассортимент
К сожалению БД не умеет работать с остатками и эта функция не планируется добавляться.

rami

Цитата: Mary1984 от 17 октября 2019, 11:09страничка 1) содержит выгрузку из БД, для наглядности выделены строки которые учавствуют в примере.
страничка 2) содержит сведённую таблицу из csv на 1) странице. Для наглядности выделена ячейка которая учавствует в примере.
страничка 3) содержит целевую таблицу остатков которая заполняется вручную, из неё нужно вычесть то что в 1) либо во 2) страничке.
Файл в формате CSV может "содержать" только одну "страничку".

Цитата: Mary1984 от 17 октября 2019, 11:09К сожалению БД не умеет работать с остатками и эта функция не планируется добавляться.
Это хорошо, что база не умеет работать с остатками ;D , база полная остатков — это наверно мусорный полигон :o
База отлично умеет работать с запросами, это её основной мощный инструмент. Если ваша исходная база только выдаёт данные в формате CSV, можно создать промежуточную рабочую базу на основе CSV, сгруппировать данные запросом, а результат обработать в таблице Calc (получится плоская сводная таблица) или в базе сложить два запроса — предыдущий с текущим.

economist

#36
Если база данных выгружает приход и расход в виде строк:

Приход, Магаз1Ящ1, ЯблокиПоддон4, ДелишесКрупные, 40
Расход, Магаз1Ящ1, ЯблокиПоддон4, ДелишесКрупные, -10

То отобразить остатки в Calc можно следующим образом
1) Лист - Вставить из Файла - Связь. Теперь Calc всегда при открытии ods-файла сразу "знает" все движения.
2) На другом листе рисуем отчет, заполняя магазины и товары как вам нужно  
3) В клетках отчета пишем формулы с функцией =СУММЕСЛИМН(), которая просто суммирует кол-во по ящики/магазины, фрукты/поддоны, сорт/вид. Её результат - это есть остаток.

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

Файлы с демонстрацией обоих подходов прилагаю, сложить в одну папку (у меня лежали в C:\DOWNLOAD)  

UPD: Способо rami, через создание базы данных - гораздо правильнее и мощнее, т.к. в базе данных можно писать SQL-запросы и делать любые группировки, и этот способ выдержит сотни тысяч строк, а мой способ - лишь десятки тысяч.  
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Mary1984

Цитата: rami от 17 октября 2019, 13:09Файл в формате CSV может "содержать" только одну "страничку".
csv скопировал для удобства в один файл с рабоичми таблицами

Цитата: rami от 17 октября 2019, 13:09Это хорошо, что база не умеет работать с остатками Смеющийся , база полная остатков — это наверно мусорный полигон Шокирован
База отлично умеет работать с запросами, это её основной мощный инструмент. Если ваша исходная база только выдаёт данные в формате CSV, можно создать промежуточную рабочую базу на основе CSV, сгруппировать данные запросом, а результат обработать в таблице Calc (получится плоская сводная таблица) или в базе сложить два запроса — предыдущий с текущим.

Даже не представляю как эти запросы формировать и как дальше их обрабатывать.

Mary1984

Цитата: economist от 17 октября 2019, 13:45Если база данных выгружает приход и расход в виде строк:
База данных выдаёт только расход. Приход я вношу вручную для отслеживания количества ассортимента. Так же данные из остатков я могу изменить на другой вид данных, например цену, и проводить уже не вычетание остатков, а умножение - расчёт стоимости проданного. Жёсткие привязки путей файлов неприветствую, файлы нужно будет передавать другим сотрудникам для работы.

rami

Тогда базу меняем на сводную таблицу, но другого формата (так должно быть легче использовать формулы):

economist

Цитата: Mary1984 от 17 октября 2019, 14:24База данных выдаёт только расход.
- Значит приход бейте на отдельный лист в Calc. Но нужно озадачиться тождеством наименований товаров в БД и в Calc, сделать что-то типа справочников на списках уник. значений по диапазонам.

Цитата: Mary1984 от 17 октября 2019, 14:24Жёсткие привязки путей файлов неприветствую, файлы нужно будет передавать другим сотрудникам для работы.
- В одном файле сделать никак не получится, их уже как минимум два (CSV + ODS).

В моем варианте (отражение CSV внутри ODS через связь) - поддерживаются относительные ссылки, т.е. файлы могут лежать где угодно, лишь бы рядом. В случае ODB - ссылки абсолютные, с именем диска, но можно намутить макрос, обновляющий путь к зарег. БД. Хотя проще подключить облако и держать там хотя бы сам CSV.

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

Mary1984

#41
Цитата: rami от 17 октября 2019, 15:06
Тогда базу меняем на сводную таблицу, но другого формата (так должно быть легче использовать формулы):
Непонимаю почему формулы легче должны использоваться? В Вашем примере классы помещаются друг в друга и получается очень длинная по строкам страничка, это позволяет более удобно посмотреть расход в конкретном магазине на всех поддонах но не облегчает вычисление остатков.. Хорошо, тогда как сравнить две похожие таблицы если в них разное количество подклассов и их родителей?

Mary1984

#42
Цитата: economist от 17 октября 2019, 15:16- Значит приход бейте на отдельный лист в Calc. Но нужно озадачиться тождеством наименований товаров в БД и в Calc, сделать что-то типа справочников на списках уник. значений по диапазонам
Непойму по поводу тождествления наименований.. Наименования поддонов иденитичны но их ассортимент, как и ассортимент сортов фруктов на поддоне может изменяться - добавляться или удаляться. Поэтому мне сложно представить что значит "список уникальных значений по диапазонам". Если появляется новый магазин/поддон/сорт фрукта, я его просто добавляю в конец файла остатков, но в БД эти столбцы, после сведения таблицы могут оказаться в разных местах и несоответствовать располжоению как в остатках, отсюда и диапазаон этих столбцов, как я понимаю, будет меняться. Я думаю что вариант склеивания поддона+сорта выглядит более успешным пока что.. Мне достаточно один раз склеить эти столбцы и внести их в остатки и дальше по такой же схеме каждый раз склеивать стобцы из проданного в csv файле, сводить таблицу и вычитывать формулой из первого примера от Bigor.

Цитата: economist от 17 октября 2019, 15:16- В одном файле сделать никак не получится, их уже как минимум два (CSV + ODS).
Мне не составляет труда скопировать содержимое CSV в рабочий файл. Нужно что б работало корректно на большое количество строк и столбцов, остальное уже дело техники)

rami

Цитата: Mary1984 от 17 октября 2019, 11:09Могу всё сразу в одном файле загрузить и опишу ещё раз что к чему:.
Обратите внимание, что файл template4.csv содержит только одну страницу, а вы хотели показать нам три. Выложите то, что вы хотели в формате .ods с пояснениями.

Mary1984

Цитата: rami от 17 октября 2019, 15:56
Цитата: Mary1984 от 17 октября 2019, 11:09Могу всё сразу в одном файле загрузить и опишу ещё раз что к чему:.
Обратите внимание, что файл template4.csv содержит только одну страницу, а вы хотели показать нам три. Выложите то, что вы хотели в формате .ods с пояснениями.
Да извиняюсь, не то совсем. Вот нужный файл.