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

[Mary1984]

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

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

Конечно, если данных немного и схитрить - можно сделать простые формулы, разнести по листам приход и расход, а остатки вынести вверх листа, закрепить область и получить результат простыми быстрыми формулами вида =СУММЕСЛИ() для каждого вида ящиков и просто вычтя из прихода - расход.  

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

[economist]

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

Ящиков будет только 2 вида (дерево/пластик), или м.б. другие виды "фасовки"? Что за "виды фруктов"?

[Mary1984]

Ящиков будет только 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.

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

Остатки нужны для логистики что бы поддерживать ассортимент
К сожалению БД не умеет работать с остатками и эта функция не планируется добавляться.

[rami]

страничка 1) содержит выгрузку из БД, для наглядности выделены строки которые учавствуют в примере.
страничка 2) содержит сведённую таблицу из csv на 1) странице. Для наглядности выделена ячейка которая учавствует в примере.
страничка 3) содержит целевую таблицу остатков которая заполняется вручную, из неё нужно вычесть то что в 1) либо во 2) страничке.

Файл в формате CSV может "содержать" только одну "страничку".

К сожалению БД не умеет работать с остатками и эта функция не планируется добавляться.

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

[economist]

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

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

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

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

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

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

[Mary1984]

Файл в формате CSV может "содержать" только одну "страничку".

csv скопировал для удобства в один файл с рабоичми таблицами

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

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

[Mary1984]

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

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

[rami]

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

[economist]

База данных выдаёт только расход.

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

Жёсткие привязки путей файлов неприветствую, файлы нужно будет передавать другим сотрудникам для работы.

- В одном файле сделать никак не получится, их уже как минимум два (CSV + ODS).

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

[Mary1984]

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

Непонимаю почему формулы легче должны использоваться? В Вашем примере классы помещаются друг в друга и получается очень длинная по строкам страничка, это позволяет более удобно посмотреть расход в конкретном магазине на всех поддонах но не облегчает вычисление остатков.. Хорошо, тогда как сравнить две похожие таблицы если в них разное количество подклассов и их родителей?

[Mary1984]

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

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

- В одном файле сделать никак не получится, их уже как минимум два (CSV + ODS).

Мне не составляет труда скопировать содержимое CSV в рабочий файл. Нужно что б работало корректно на большое количество строк и столбцов, остальное уже дело техники)

[rami]

Могу всё сразу в одном файле загрузить и опишу ещё раз что к чему:.

Обратите внимание, что файл template4.csv содержит только одну страницу, а вы хотели показать нам три. Выложите то, что вы хотели в формате .ods с пояснениями.

[Mary1984]

Могу всё сразу в одном файле загрузить и опишу ещё раз что к чему:.

Обратите внимание, что файл template4.csv содержит только одну страницу, а вы хотели показать нам три. Выложите то, что вы хотели в формате .ods с пояснениями.

Да извиняюсь, не то совсем. Вот нужный файл.