Прошу подсказать пути решения для задачи Расчет средневзвешенного значения в динамической таблице.
Есть таблица, которая содержит данные по городам, товарам, ценам в месячной динамике. Эта таблица – результат работы функции QUERY. Таблица постоянно изменяется. Количество и структура столбцов остаются прежними, но количество строк динамически изменяется по мере внесения данных в файлы. Могут появляться (исчезать) города, товары и цены.
Необходимо для каждой пары город-товар рассчитывать средневзвешенную цену для каждого месяца отдельно. Отчет (расчет) должен быть тоже динамическим.
Решение должно быть без скриптов (я их еще не освоил и не смогу поддерживать такое решение).
PS Может быть, будет «проще» сделать это через Power Query Excel?
Добрый день!
Прошу подсказать пути решения для задачи Расчет средневзвешенного значения в динамической таблице.
Есть таблица, которая содержит данные по городам, товарам, ценам в месячной динамике. Эта таблица – результат работы функции QUERY. Таблица постоянно изменяется. Количество и структура столбцов остаются прежними, но количество строк динамически изменяется по мере внесения данных в файлы. Могут появляться (исчезать) города, товары и цены.
Необходимо для каждой пары город-товар рассчитывать средневзвешенную цену для каждого месяца отдельно. Отчет (расчет) должен быть тоже динамическим.
Решение должно быть без скриптов (я их еще не освоил и не смогу поддерживать такое решение).
aliramora191, спасибо за красивое и компактное решение!
Но, к сожалению, оно требует каждый раз протягивать формулу, если появляются новые пары «город-товар». Можно сделать с запасом, но его все равно придется контролировать…
Пытался обернуть Ваши формулы в формулу массива (см. второй лист в книге), но «в лоб» не вышло.
Можно ли что-то сделать в этом направлении?
aliramora191, спасибо за красивое и компактное решение!
Но, к сожалению, оно требует каждый раз протягивать формулу, если появляются новые пары «город-товар». Можно сделать с запасом, но его все равно придется контролировать…
Пытался обернуть Ваши формулы в формулу массива (см. второй лист в книге), но «в лоб» не вышло.
Я, как обычно - с лекцией по "чистописанию". В принципе, всё правильно, всё хорошо, всё работает, но вижу возможность выразиться более лаконично, о чём считаю нужным сообщить. Т.е. чуть подправить даже не напильником, а надфилем - для доводочки.
Итак, первая формула (для ячейки J14). Поскольку колонки в диапазоне A2:B идут в нужном естественном порядке, то собирать из них динамический массив {A2:A\B2:B} не имеет большого смысла. В добавок к этому функция UNIQUE является массивной сама по себе, поэтому обертка ArrayFormula рядом с ней тоже не нужна: [vba]
Код
=SORT(UNIQUE(A2:B))
[/vba]
Во второй формуле (для ячейки L14) лучше избавиться от IF-ов - они вносят назойливую громоздкость необходимостью указания их и TRUE-, и FALSE-частей. Для знаменателя же тоже используем функцию SUMPRODUCT - ведь числитель длиннее знаменателя лишь на один сомножитель (столбец), а визуальная похожесть числителя и знаменателя будут только способствовать пониманию работы формулы в целом: [vba]
Я, как обычно - с лекцией по "чистописанию". В принципе, всё правильно, всё хорошо, всё работает, но вижу возможность выразиться более лаконично, о чём считаю нужным сообщить. Т.е. чуть подправить даже не напильником, а надфилем - для доводочки.
Итак, первая формула (для ячейки J14). Поскольку колонки в диапазоне A2:B идут в нужном естественном порядке, то собирать из них динамический массив {A2:A\B2:B} не имеет большого смысла. В добавок к этому функция UNIQUE является массивной сама по себе, поэтому обертка ArrayFormula рядом с ней тоже не нужна: [vba]
Код
=SORT(UNIQUE(A2:B))
[/vba]
Во второй формуле (для ячейки L14) лучше избавиться от IF-ов - они вносят назойливую громоздкость необходимостью указания их и TRUE-, и FALSE-частей. Для знаменателя же тоже используем функцию SUMPRODUCT - ведь числитель длиннее знаменателя лишь на один сомножитель (столбец), а визуальная похожесть числителя и знаменателя будут только способствовать пониманию работы формулы в целом: [vba]
Кстати, нашлась уже специализированная табличная функция для наших вычислений - AVERAGE.WEIGHTED. Правда, подготовка аргументов для нее (а они должны быть обязательно числовыми) - то ещё занятие... [vba]
Кстати, нашлась уже специализированная табличная функция для наших вычислений - AVERAGE.WEIGHTED. Правда, подготовка аргументов для нее (а они должны быть обязательно числовыми) - то ещё занятие... [vba]
Gustav, спасибо. Но из Вашей из формулы тоже не получается сделать "само-протягивающуюся" ... Может быть надо использовать комбинацию функций МУМНОЖ и ТРАНСП? Был ваш пост на эту тему...
Gustav, спасибо. Но из Вашей из формулы тоже не получается сделать "само-протягивающуюся" ... Может быть надо использовать комбинацию функций МУМНОЖ и ТРАНСП? Был ваш пост на эту тему...book
из Вашей из формулы тоже не получается сделать "само-протягивающуюся"
Никто не обещал, что из любой формулы можно сделать само-протягивающуюся - ни Гугл, ни я
"Если бы я был директором", я бы на отдельном рабочем листе (возможно, затем скрытым) создал "справочник" - список комбинаций всех городов (сейчас их 4) и всех товаров (сейчас их 5). Получилась бы табличка из 4х5 = 20 строк (кол-во которых, наверное, менялось бы нечасто). Колонки - по-прежнему месяцы. В эту табличку бы прописал и протянул (вручную) сегодняшние разработанные формулы. И затем бы вытягивал значения из этого справочника с помошью ВПР. А уж из ВПР можно сделать "само-протяжку".
из Вашей из формулы тоже не получается сделать "само-протягивающуюся"
Никто не обещал, что из любой формулы можно сделать само-протягивающуюся - ни Гугл, ни я
"Если бы я был директором", я бы на отдельном рабочем листе (возможно, затем скрытым) создал "справочник" - список комбинаций всех городов (сейчас их 4) и всех товаров (сейчас их 5). Получилась бы табличка из 4х5 = 20 строк (кол-во которых, наверное, менялось бы нечасто). Колонки - по-прежнему месяцы. В эту табличку бы прописал и протянул (вручную) сегодняшние разработанные формулы. И затем бы вытягивал значения из этого справочника с помошью ВПР. А уж из ВПР можно сделать "само-протяжку".Gustav
Serge_007, что-то я торможу... Пожалуйста, объясните, в чем разница ? Спасибо. PS ВПР - я знаю как - можно сделать формулой массива и она будет сама протягиваться .
Serge_007, что-то я торможу... Пожалуйста, объясните, в чем разница ? Спасибо. PS ВПР - я знаю как - можно сделать формулой массива и она будет сама протягиваться .book
Нет. В терминах текущей ситуации: скрытая - 20 строк, "финальная" - 9. Да, в скрытой будет много нулевых сумм - для неактуальных (возможно, в данный момент несуществующих на практике) сочетаний "город-товар". Но "лазить" в неё придется значительно реже - только когда появится новый город или новый товар.
Кстати, как раз недавно была тема по динамической генерации всех возможных парных сочетаний значений из двух массивов: Массовое объединение ячеек
Т.е. сегодня, допустим, у вас есть скрытая таблица на 20 строк (4 города x 5 товаров) и, соответственно, на 20 строк вручную протянута формула для средневзвешенной цены. Завтра появляется еще один город, который вы вписываете в отдельный список городов и таблица (динамически!) становится уже на 5 x 5 = 25 строк. Всё, что останется - это сходить на скрытый лист и протянуть формулы для средневзвешенного еще на 5 добавившихся строк.
Нет. В терминах текущей ситуации: скрытая - 20 строк, "финальная" - 9. Да, в скрытой будет много нулевых сумм - для неактуальных (возможно, в данный момент несуществующих на практике) сочетаний "город-товар". Но "лазить" в неё придется значительно реже - только когда появится новый город или новый товар.
Кстати, как раз недавно была тема по динамической генерации всех возможных парных сочетаний значений из двух массивов: Массовое объединение ячеек
Т.е. сегодня, допустим, у вас есть скрытая таблица на 20 строк (4 города x 5 товаров) и, соответственно, на 20 строк вручную протянута формула для средневзвешенной цены. Завтра появляется еще один город, который вы вписываете в отдельный список городов и таблица (динамически!) становится уже на 5 x 5 = 25 строк. Всё, что останется - это сходить на скрытый лист и протянуть формулы для средневзвешенного еще на 5 добавившихся строк.Gustav
Теперь - понятно! В моих реальных данных кол-во сочетаний получается около 1600. Теперь осталось сообразить, как автоматически создать такой список... Ваша ссылка - очень кстати. Спасибо И еще - добавить формулу - проверку, показывающую необходимость протянуть формулу, но здесь, я думаю, справлюсь сам. PS В любом случае, это быстрее, чем конструировать запросы в PQ.
Теперь - понятно! В моих реальных данных кол-во сочетаний получается около 1600. Теперь осталось сообразить, как автоматически создать такой список... Ваша ссылка - очень кстати. Спасибо И еще - добавить формулу - проверку, показывающую необходимость протянуть формулу, но здесь, я думаю, справлюсь сам. PS В любом случае, это быстрее, чем конструировать запросы в PQ.book
-- С уважением, Андрей.
Сообщение отредактировал book - Четверг, 16.12.2021, 18:40
Да, может, и никак. Я же тоже не истина в последней инстанции Вот COUNTBLANK выглядит, наверное, перспективнее. Считаем отдельно по саморасширяющемуся столбцу и по столбцу с ручным протягиванием. Как только COUNTBLANK по саморасширяющемуся стал меньше COUNTBLANK-а с ручным протягиванием, идём на скрытый лист и "компенсируем", т.е. протягиваем в новые строки.
Да, может, и никак. Я же тоже не истина в последней инстанции Вот COUNTBLANK выглядит, наверное, перспективнее. Считаем отдельно по саморасширяющемуся столбцу и по столбцу с ручным протягиванием. Как только COUNTBLANK по саморасширяющемуся стал меньше COUNTBLANK-а с ручным протягиванием, идём на скрытый лист и "компенсируем", т.е. протягиваем в новые строки.Gustav
Сделал большую таблицу – получилось примерно 1650 всевозможных пар Город-Товар, умножаем на 24 месяца, получаем 39,600 ячеек. Таблица сильно очень тормозит.
А реальных пар всего 126. Поэтому придется делать контрольную ячейку и протягивать формулы в случае необходимости.
Хочу поделиться результатом.
Сделал большую таблицу – получилось примерно 1650 всевозможных пар Город-Товар, умножаем на 24 месяца, получаем 39,600 ячеек. Таблица сильно очень тормозит.
А реальных пар всего 126. Поэтому придется делать контрольную ячейку и протягивать формулы в случае необходимости.book
Так и сделал. Получилась одна таблица, не две (см. сообщение 12). Добавил проверку на количество строк по формуле СЧЁТЗ, чтобы отлавливать появление новых пар.
Так и сделал. Получилась одна таблица, не две (см. сообщение 12). Добавил проверку на количество строк по формуле СЧЁТЗ, чтобы отлавливать появление новых пар.book
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
