Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » Vasya Ivanoff
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Расчет глубины задолженности (с помощью PQ)
 
#
04.09.2024 11:12:51

Коллеги, доброго дня!

Не уверен что эта задача для PQ, но тем не менее хочется попробовать, буду признателен за помощь.

Сама задача: Продавец в конце каждого месяца знает общую сумму задолженности по каждому Покупателю.

Так же Продавец имеет историю отгрузок по каждому Покупателю.

Для каждого Покупателя в каждом месяце нужно посчитать глубину задолженности в днях.

Глубина задолженности - период в днях, за который сформировалась сумма задолженности.
Т.е. например на конец января задолженность 100 руб.
Отгрузки с конца января и в прошлое были такие:

датаотгрузки, руб
08.11.2023          11
16.11.2023           8
24.11.2023           9
02.12.2023          11
10.12.2023          10
18.12.2023            9
26.12.2023          11
03.01.2024          10
11.01.2024           9
19.01.2024          11
27.01.2024          10

С конца января в прошлое суммируем все отгрузки до тех пор, пока их сумма максимально не приблизится к сумме задолженности (но не превышая ее).
Искомая сумма (98 руб.) набирается в интервале с 27.01.24 по 16.11.23.
Смотрим дату самой ранней отгрузки из этой суммы и находим разницу в днях между 31.01.24 и 16.11.23. Получается 76 дней.

Изменено: Vasya Ivanoff - 04.09.2024 11:13:51
 
Перейти    
Расчет задолженности помесячно с учетом отсрочки, решения для DAX/Power Pivot
 
#
23.07.2024 10:46:42
Доброго дня!

Прошу помощи с решением для DAX/Power Pivot.

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

Файл с исходными данными и примером расчета прилагаю.
Изменено: Vasya Ivanoff - 23.07.2024 11:51:01
 
Перейти    
Суммирование во временном диапазоне по условию, DAX
 
#
22.07.2024 20:18:53
Доброго дня, господа!

На этом замечательном форуме мне ранее подсказали отличное решение на формулах Excel для суммирования по условию внутри временного диапазона.
Теперь у меня такая же задача, только сделать это хочу в Power Pivot. Надеюсь получить не менее красивое решение и на DAX  :D

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

Прилагаю файл с примером решения на формулах и и исходником для DAX.
 
Перейти    
Расчет срока хранения до продажи, есть закупка и продажа в динамике. Как посчитать срок хранения?
 
#
13.06.2024 12:53:32
Добрый день!

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

Учет товара строго по фифо (всегда продаем тот товар, который поступил раньше).

Продажа в тот же месяц, когда была закупка считается как срок хранения равен 1, продажа на второй с месяца закупки - 2 и т.д.

Для расчета срока хранения берем самую раннюю партию в продажах за месяц. Т.е. если в этом месяце продали 100 упаковок, закупленных в этом же месяце и всего одну упаковку, которую закупили 3 месяца назад, то срок хранения равен 3.

Прилагаю файл с примером, срок хранения там посчитан руками. Нужна "тягабельная" формула.

Спасибо! :)
 
Перейти    
Группировка в сводной для расчетных показателей, DAX/Power Pivot
 
#
03.05.2024 10:13:02
Приветствую сообщество!

Есть исходные данные в Power Pivot, по которым нужно построить сводную таблицу.
В сводной должна быть группировка для разных показателей.
Для тех показателей, что есть в исходных данных (продажи, маржа...) предполагаю добавить столбец в исходных данных, в котором будут указаны группы.

Но не могу сообразить как загнать в группировку в сводной расчетные показателей в % и как сделать группу "Маржинальность, %", который вычисляется по исходным данным.

Файл с примером прилагаю.
Изменено: Vasya Ivanoff - 03.05.2024 10:34:33
 
Перейти    
Динамически изменяемый план-факт в одну строку, методология построения модели
 
#
29.02.2024 10:32:12
Доброго дня сообщество!

Прошу методологического совета по решению этой задачки

Исходные данные в двух таблицах:
- плановые показатели в виде плоской таблице на год, помесячно
- периодически обновляемые фактические показатели в виде плоской таблице на год, помесячно

Загружаю их из разных источников с помощью PQ в модель данных.

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

Пока в голову приходит только вариант с ручным созданием третьей таблицы из двух исходных. Но хотелось бы, чтобы отчет строился автоматом, например с помощью срезов или выбора месяца из выпадающего списка.
 
Перейти    
Проверка единообразия формул на большом количестве листов, Как найти возможные ошибки в формулах таблиц, созданных по заданной форме
 
#
01.02.2024 14:30:19
Всем доброго дня!

Дано: Десятки ексель-файлов и случайное кол-во листов в каждом из них. Суммарно более 500 листов. Все листы содержат несколько больших таблиц как с формулами, так и с данными. Все листы единообразны, созданы по одной форме, ячейки полностью совпадают, формулы везде должны быть одинаковые.

Проблематика: Шаловливые ручки пользователей за долгую историю использования этих файлов внесли рандомные правки на рандомных листах. Где-то формула удалена, где-то формула заменена значением.

Задача: Проверить все эти сотни листов на предмет отклонения формул от заданной эталонной формы.


Моей соображалки хватило на то, чтобы с помощью PQ создать список всех книг-листов и путь к файлам. На VBA сделал цикл, который последовательно открывает все файлы и перебирает все листы.

Дальше предполагаю преобразовать все формулы в текст и сравнивать ячейки открытого листа и эталона (отдельная таблица), фиксируя результат на том же листе который проверяю (еще одна отдельная таблица). А затем опять же через PQ собрать результаты проверки в единую таблицу и уже руками посмотреть каждую ошибку.

Но как то слишком все трудоемко и не рационально это выглядит.
Буду признателен если кто-то подскажет более оптимальный вариант.
 
Перейти    
Расчет платежей с учетом бонуса, оптимизируем расчет и форму таблицы
 
#
26.10.2023 12:56:41
Прошу помощи уважаемого сообщества

Закупаем товар у поставщика.
Ежемесячно поставщик начисляет нам бонусы за продажи этого товара.
Эти бонусы учитываются в оплатах поставщику в следующем квартале.

Принципиальное решение есть (файл прилагаю), но хочется избавиться от промежуточных строк с расчетами.

В идеале только три строки:
исходные данные - сумма закупленного товара и ежемесячно начисляемые бонусы
расчетная строка - сумма к оплате поставщику
 
Перейти    
Редактирование множества файлов по шаблону
 
#
10.03.2023 16:10:47
Приветствую уважаемое сообщество!

Ситуация:
Имеем 6 разных таблиц-шаблонов в Ексель.
По этим шаблонам создано много сотен файлов.

Задача:
Периодически возникает необходимость отредактировать шаблон (добавить элемент, строку или изменить формулу).
После изменения шаблона необходимо соответствующим образом отредактировать все файлы, созданные по этому шаблону.

Как организовать этот процесс оптимальным образом?

PS Сейчас под каждую правку пишу макрос VBA, который пробегает по всем нужным файлам и редактирует их.
 
Перейти    
Работа VBA c множеством файлов или множеством вкладок
 
#
01.03.2023 11:06:57
Приветствую уважаемое сообщество!

Задача: есть экспортируемый из внешней системы ексель-файл, данные из которого нужно разнести (скопировать-вставить) по множеству других однотипных файлов.

Проблема:
при разнесении данных из источника в 100 разных файлов (в каждом файле одна вкладка) процесс занимает примерно 1,5 минуты
при разнесении данных из источника в 1 файл по 100 вкладкам процесс длится около 10 минут

Вопросы:
1. Почему так происходит?
2. Можно ли как-то оптимизировать процесс во втором случае?
 
Перейти    
VBA Возникает ошибка при замене имени файла в выражении, Возникает ошибка при замене имени файла в выражении
 
#
11.01.2023 16:53:48
Добрый день,

Прошу помощи. Код просто копирует диапазон ячеек. Если копирование происходит на одном листе, то все работает

Так работает:
Код
Sub test()

    Dim lngCounter_RowNumber As Long   счетчик цикла и номер строки в файле выгрузки
        
    Dim lngMonthQnty As Long      индекс месяца, заданного пользователем
    
lngCounter_RowNumber = Workbooks("macro 2023.xlsm").Sheets("test").Cells(2, 3) + 7
lngMonthQnty = Workbooks("macro 2023.xlsm").Sheets("test").Cells(1, 3)

Workbooks("macro 2023.xlsm").Sheets("test").Range(Cells(lngCounter_RowNumber, 7), Cells(lngCounter_RowNumber, lngMonthQnty + 6)).Copy _
Workbooks("macro 2023.xlsm").Sheets("test").Cells(6, 1)
     
End Sub



Но если пытаюсь скопировать в другой файл (или даже на другую страницу той же книги), возникает ошибка 1004 application-defined or object-defined error.

Вот так уже не работает:
Код

Workbooks("macro 2023.xlsm").Sheets("test").Range(Cells(lngCounter_RowNumber, 7), Cells(lngCounter_RowNumber, lngMonthQnty + 6)).Copy _
Workbooks("test.xlsm").Sheets("sheet1").Cells(6, 1)


Оба файла постоянно открыты. В чем причина?

ЗЫ Думаю это никак не связано, но существует проблема со шрифтами, когда копирую код из разработчика кирилица копируется "кракозябрами".
Изменено: Vasya Ivanoff - 11.01.2023 17:00:08
 
Перейти    
Excel 365 - не вставляется срез, баг или фича?
 
#
16.11.2022 13:18:22
Здравствуйте форумчане

Офис 365. Сделал файл с несколькими запросами PQ и моделью в Power Pivot. Срезы прекрасно вставлялись и работали.
Затем сделал копию файла и немного переделал как запросы, так и модель. Теперь срезы не вставляются, хотя кнопка "Вставить срез" активна, меню выбора полей для среза появляется, галочка ставиться, "ок" нажимается.
Но срез не появляется  :cry:

Подскажите как лечить, очень не хочется все с нуля переделывать.
 
Перейти    
Ручное добавление строк в запрос Power Query, возможно ли вручную добавить одну или несколько строк в запрос Power Query
 
#
19.10.2022 11:05:25
Доброго дня форумчане!

С помощью PQ создаю простейшую таблицу-справочник для Power Pivot. Иногда возникает необходимость добавить в этот справочник одну или две строки. Можно ли это сделать вручную и как?
 
Перейти    
Логика связей в Модели данных в Power Pivot, как связи в Модели данных влияют на расчет и вывод итоговых результатов в сводной таблице
 
#
06.10.2022 12:24:07
Всем здравствовать!

Прошу помощь зала)) Разбираюсь с Power Pivot. Не могу понять логику связей в Модели данных, как и на что они влияют. В открытых источниках нашел много материала о том как технически устанавливать эти связи, но нигде не смог найти объяснений как это работает.

На примере: имеем две таблицы, с продажами и остатками. В таблице с продажами есть и код и название товара. В таблице с остатками только код. Создаю таблицу-справочник по кодам/товару и связываю обе таблицы в Pivot по Коду. Строю сводную Остаток по Товарам.

Вопросы:

1. Результат в этой сводной выводится не так, как выводился бы в обычной сводной. Ожидал, что по каждому товару будет указан остаток именно по этому товару.

2. Создаю два среза - поле Товар из Продаж и поле Товар из Справочника. И эти вроде бы одинаковые срезы работают по разному. Почему?

Файл с примером прилагаю.
 
Перейти    
Выбор недели внутри заданного месяца
 
#
12.07.2022 22:51:02
Существует некий процесс планирования ресурсов. Интервал планирования - месяц. Детализация планирования внутри месяца - понедельно.
Пользователь выбирает месяц (выпадающий список).
После определения месяца в таблице становятся доступны к выбору недели в этом месяце (так же в виде выпадающего списка).
Либо просто дата первого дня недели (понедельник), либо первый-последний день недели в виде например 01.08.22-07.08.22.
При этом если месяц начинается посреди недели, то эта неделя так же указывается с 01 числа, например 01.09.22-04.09.22.

С выпадающим списком по месяцам проблем нет. Не могу сообразить как определять и подтягивать недели.

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

Пример формы прилагаю.
 
Перейти    
Сбор и преобразование данных с разных листов книги, исходные данные "в неудобном" виде
 
#
03.07.2022 11:07:15
Есть файл с большим количеством листов. На каждом листе данные по конкретному товару - таблицы и просто единичные значения.
Необходимо собрать данные со всех листов в одну большую таблицу в виде, удобном для дальнейшего создания сводных таблиц.
Расположение таблиц и т.д. на исходных листах неизменно и одинаковое для всех листов.

Сам вижу только такой вариант - расставить руками данные для одного листа в нужном виде, потом сделать все адреса абсолютными, а для каждого следующего листа менять название листа в формуле руками через "поиск-замена".
Объем ручной работы огромный и легко можно накосячить.

Может есть какие то более рациональные решения? Буду признателен за подсказку. Файл с примером прилагаю.
 
Перейти    
Когда будет доступен новый тираж "Мастер формул" ?
 
#
17.06.2022 16:35:42
Хочу купить книгу "Мастер Формул" в бумажном виде, но тираж распродан.
Может быть кто-то знает когда появится новый тираж?
 
Перейти    
Расчет платежей при меняющейся отсрочке
 
#
09.06.2022 16:13:12
Здравствуйте всем

Не могу понять с какой стороны (и с какими функциями) подступиться к такой задачке. Буду признателен за любую подсказку.

Есть два параметра:

1. Отсрочка платежа,
которая указана для месяца в котором она действует.
Положительное значение - отсрочка по оплате, отрицательное значение - предоплата.
Она может с течением времени меняться, но не может быть направлена в прошлое. То есть если сейчас июнь, то на июль она не может измениться на предоплату -2 месяца, но может быть изменена на -2 месяца в сентябре. Это правило не обязательно прошивать в формуле, этот момент контролирует человек.

2. Объем поставки,
требуемая в указанном месяце для производства продукция (в рублях). Т.е. если на июнь указано 100 000, значит в июне эта продукция должна поступить. Отсрочка указан равной 3, значит оплата будет в сентябре. А в июле отсрочка (предоплата) равна -2, значит оплата должна была быть в мае.

Требуется составить график платежей.

Файл примера прилагаю.
 
Перейти    
Страницы: 1
Наверх