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

Страницы: 1
Удалить значения в строке в соседнем столбце от активной ячейки в умной таблице (VBA)
 
#
26.11.2025 20:52:46
Добрый день,
Два вопроса:
1.  В VBA я не разбираюсь совсем, к сожалению. Вся задача решается в Эксель с помощью умных таблиц и формул.
Единственная загвоздка: Есть два списка в умной таблице. Один зависит от другого (сначала выбираешь значение в одном списке, затем - в другом с учетом выбранного значения в первом).
Необходимо удалять выбранные из второго списка значения при изменении значения в первом списке (в конкретной строке). Можно ли это реализовать без VBA? Если "нет", то как это сделать через VBA?
2. Можно ли для ячеек, в которых выбирается второй список сделать ссылку на список не просто, допустим, "B5", а ссылкой на соседнюю ячейку умной таблицы, т.е. второй список --> Таблица1[@Столбец1]?

Заранее благодарю за конструктивные советы.
Изменено: Sviman144 - 26.11.2025 20:53:50
 
Перейти    
Вывод исходных данных из сводной таблицы с учетом дополнительного контекста (PP)
 
#
22.09.2025 13:33:35
Добрый день,

Прошу совета, как можно решить следующую задачу:

Есть таблица (см. пример).
Для таблицы определяется мера в PP ("Сумма (тип 1)").

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

Т.е. в примере сейчас нет разницы, если дважды щелкну на числа в мере "Общая сумма" или "Сумма (тип 1)". В обоих случаях я получу "все заказы". А хотелось бы сделать так, чтобы при двойном щелчке на значении каждой меры я получал бы таблицу только со строками, которые участвовали в расчете меры.

Это возможно или нет?

Заранее благодарю за ответы.
 
Перейти    
Функция SAMEPERIODLASTYEAR + ошибка в "непрерывности" дат в сводной (PP)
 
#
13.09.2025 12:18:58
Добрый день,

Прошу помочь разобраться, почему в таблице "Сводная 1" мера [Итого PY (fx Sameperiod)]  с функцией SAMEPERIODLASTYEAR работает, а в другой - вызывает "Ошибку вычисления в мере..." по причине отсутствия непрерывности дат.

Получить данные за предыдущий аналогичный период (месяц) получается только через функцию PARALLELPERIOD ([Итого PY (fx Parallelperiod)])

См. пример во вложении.

Заранее благодарю за ответы.
 
Перейти    
Определить для сумм диапазоны значений (PQ)
 
#
28.08.2025 19:00:21
Добрый день,

Прошу помочь справиться со следующей задачей в PQ:
Есть таблица заказов, у каждого заказа - своя стоимость.
Есть таблица "диапазонов сумм" (от 100 до 200 тыс. руб., от 1 млн. до 2 млн. руб. и т.д)
Как в PQ определить в столбце с каждым заказов, в какой диапазон значений входит его стоимость (Столбец1 во вложенном примере).

Никак не могу справиться с задачей.
Заранее благодарю за содержательные ответы.
 
Перейти    
Низкая скорость обработки запроса с регулярными выражениями
 
#
15.08.2025 08:07:04
Добрый день,
Прошу совета, можно ли как-то ускорить обработку запроса с заменой регулярных выражений.
1. Есть большая (основная) таблица (выписка) с кучей полей, в том числе, поле "Контрагент"
2. В поле "Контрагент" организации пишутся, зачастую, с дополнительной информацией, вроде номеров расчетных и лицевых счетов, доп. офисов и т.п.
3. Необходимо в поле "Контрагент" оставить только само название контрагента + краткую форму собственности (ООО, АО и т.п.)

4. Для решения этой задачи использую следующую функцию:
Код
let fx=(txt as text, lst as list) =>
    try List.Accumulate(lst, txt, (state, current) =>
        Text.Replace(
            Text.Upper(state),
            Text.Upper(let  
                reg = Web.Page(
                "<script>
                    var x = '" & txt & "';
                    var pattern = new RegExp('" & current & "', 'gmi');
                    var result = x.match(pattern);
                    document.write(result);
                </script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0}
                in
                reg), "")
    )
    otherwise ""
in
fx

5. Функцию вызываю для каждого поля "Контрагент" в основной таблице, где в качестве txt - значение поля, lst - ссылка на список значений регулярных варажений (см. скриншот).

Всё работает, но оооочень медленно.
Есть ли какие-либо варианты, чтобы ускорить обработку таблицы с заменой ненужных значений?

Заранее благодарю за конструктивные ответы
 
Перейти    
Среднее значение в "Итогах" на основании уникальных значений
 
#
27.03.2025 16:49:19
Добрый день,
Прошу помочь с решением следующей задачи:
1. Есть Таблица с списком, допустим, проданных товаров за период (Дата | Id товара | Кол-во | Цена | Сумма)
2. Необходимо рассчитать количество уникальных товаров, проданных за месяц
3. Среднее количество проданных уникальных товаров за год.

В файле - пример таблицы, где в каждой строке собрано количество уникальных товаров, проданных за месяц.
Код
Мера: Кол-во уникальных товаров за месяц:= DISTINCTCOUNT('Таблица[Id товара])
Но, используя эту меру, в "Итогах" я получаю количество уникальных товаров за год. А как определить среднее значение, на основании ежемесячных итогов?

Заранее благодарю за ответы.
 
Перейти    
Расчет значений за предыдущий год в сводной таблице
 
#
18.02.2025 15:13:39
Добрый день,
Прошу помочь разобраться как отразить в сводной таблице корректные данные за предыдущий год.
Исходные данные:
1. Таблица "заказы", с данными о заказах с некой "опорной датой" (Id заказчика, Id заказа, опорная дата, стоимость заказа)
2. "Опорная дата" связана с "Календарем"
3. Дополнительно существует еще несколько таблиц, допустим, две, которые связаны с основной таблицей "Заказов" по Id заказа.
3.1. Одна таблица - оплаты по Id заказа
3.2. Другая таблица - расходы по Id заказа
3.3. Даты в этих таблицах не имеют значения
4. Прибыль рассчитывается как разница между "Оплатами" и "Расходами"

Необходимо создать сводную таблицу, в которой бы все было сведено по Заказчику (строки). Столбцы - Даты (в частности, ГОДЫ из Календаря).
В значения необходимо иметь возможность вывести данные о Прибыли в текущем и предыдущим году с учетом дополнительных фильтров.

Если накладываю только один фильтр на "Прибыль" вроде Sameperiodlastyear('Каленадь'[дата]), то вроде как считает, как надо. Если дополнительно накладываю фильтр на заказы, то значения для Прибылей становятся одинаковыми, что за текущий, что за предыдущие годы.

Пример - во вложении.

В чем моя ошибка?
Заранее благодарю за конструктивные ответы.
 
Перейти    
Power Pivot. Средняя загрузка в день (за месяц) в зависимости от количества дней занятости
 
#
01.02.2025 22:49:18
Добрый день,

прошу помочь советом с решением следующей задачи:
1. Есть таблица (см. Пример.xlsx), в которой обозначены работы (ID) и даты, в которые эти работы были проведены.
2. Необходимо рассчитать: среднее количество работ, которыми занимались в месяц.
3. Возможно, для созданной меры есть более простое написание, нежели сделанное через Filter(...), см. Пример.

Описание "Примера":
1. Таблица слева - исходные данные
2. Таблица справа - сводная (из PP)
3. Таблица внизу ("Сводная (по месяцам)") - требуемый итог.

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

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

Заранее благодарю за конструктивные советы.
 
Перейти    
Построение графика в Эксель (гистограмма с накоплением + разделение по годам)
 
#
23.08.2024 18:39:13
Добрый день,
Прошу помочь советом.
Есть большая таблица вида: Дата/Категория/Тип/Количество (в PP) + Таблица дат.
Необходимо построить график так, чтобы:
1. Ось х - месяцы
2. Ось у - значения с накоплением по категориям и типам (гистограмма с накоплением)
3. Каждый год - это отдельная гистограмма на одном и том же графике.

Как ни стараюсь, только годы и складываются в "гистограмму с накоплением".

Во вложении - пример графика (от руки).

Заранее благодарю за конструктивные ответы.
 
Перейти    
Оптимизация скорости вычисления количества рабочих дней выполнения работ в PQ
 
#
20.05.2024 12:55:12
Добрый день,
Сразу прошу прощения, что не прикладываю файл с примером, т.к. вопрос больше теоретический.
  1. Есть большая таблица (более 100 тыс. строк) со списком работ, выполненных за год. Для каждой "работы" указана дата/время начала и окончания выполнения (Id работы, <... дополнительные данные по работе>, ДатаВремяНачалаВыполнения, ДатаВремяОкончания).
  2. Есть отдельная таблица со списком всех рабочих дней в году.
  3. Необходимо вычислить количество рабочих дней для каждой выполненной работы. Далее вычисляется общее количество часов/минут для получения чистого рабочего времени, в течение которого работа была выполнена.
Проблема — очень медленная скорость вычисления количества рабочих дней для каждой работы.

Порядок вычисления:
1. Добавляется столбец со списком всех календарных дней, в течение которых выполнялась работа:
Код
List.Dates(Date.From([DateTimeBegin]) + #duration(1,0,0,0), Number.From(Date.From([DateTimeEnd])) - Number.From(Date.From([DateTimeBegin])), #duration(1,0,0,0))
2. Затем добавляется еще один столбец, в котором собирается список только рабочих дней.
Код
List.Select([список из п1.], each List.Contains("Таблица, содержащая только столбец с рабочими днями", _))

Понимаю, что происходит каждый элемент одного списка сравнивается с каждым элементом другого. И это, скорее всего, и влияет на скорость.
Пробовал другой вариант для п.2 через List.Intersect() но скорость вычислений примерно такая же.

Хотел спросить совета, может быть возможен другой, более быстрый алгоритм для расчета рабочих дней?
Как вариант, создавать список для сравнения не рабочих дней, а нерабочих. Их - меньше, стало быть и скорость будет чуть выше?

Или же есть еще более быстрый вариант?

Заранее благодарю за конструктивные ответы.
 
Перейти    
Создание "промежуточной" сводной таблицы в Power Pivot, получить промежуточные сводные данные для последующей фильтрации и агрегирования
 
#
15.05.2024 17:05:36
Добрый день,

Прошу помочь советом в следующей ситуации:
1. Есть некий набор данных - идентификатор заявки (StageId), дата создания заявки (Дата заявки) и время выполнения заявки.
2. Одна заявка может выполняться несколько раз

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

Пробовал сделать промежуточную таблицу в Power Pivot через Summarize:
Код
мера:=SUMMARIZE('Таблица1';'Таблица1'[StageId];"tmp";sum('Таблица1'[Итого, мин.]))
, но неизменно получаю, что "выражение ссылается на несколько столбцов".

Подскажите, пожалуйста, что не так делаю.
Заранее благодарю за конструктивные ответы.
 
Перейти    
Связи между таблицей дат и данными для расчета мер в диапазоне дат, Как связать таблицу дат с данными, если в данных - несколько временных значений
 
#
20.02.2024 11:55:57
Добрый день,
Прошу помочь конструктивным советом для решения задачи: Определить суммарную стоимость заказов в производстве в каждом заданном периоде (неделя, месяц, год) с учетом ранее выполненных отгрузок по ним.

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

Есть две таблицы:
№1. список заказов, даты начала/конца производства, а также стоимость заказов
№2. список отгрузок по этим заказам (дата + сумма отгрузки).

Через обычные таблицы + формулы, в принципе, получается, но трудоемко и нет гибкости для получения результатов в разных временных интервалах.
Хотелось бы сделать через Power Pivot (если возможно). Но, непонятно, как таблицу дат привязать к нескольким датам в разных таблицах и как правильно создать меры.

В файле-примере:
1. Две исходные таблицы (Заказы, Отгрузки)
2. Справа расписаны результаты вычислений для каждого месяца на основе исходных таблиц
3. Итоги (строка "Результат") в таблице в нижнем правом углу - итоговые значения, которые должны получиться в сводной таблице.

Заранее благодарю за помощь.
 
Перейти    
Преобразование таблицы в Power Query. Работа со списками
 
#
23.01.2024 13:09:43
Добрый день,

Прошу помочь преобразовать таблицу в Power Query.
Есть три столбца Атрибут, Значение 1 и Значение 2. (см. source.png)

Задача: по каждому атрибуту определить сумму значений по каждому параметру.
Т.е. для 170421 должно в итоге получиться: Параметр 8. Итог - 100
для 172404 - Параметр 8. Итог - 100, Параметр 12 - Итог 0
для 172817 - Параметр 8. Итог - 100, Параметр 97 - Итог 0
для 173010 - Параметр 12. Итог - 79, Параметр 97 - Итог 0, Параметр 94 - Итог 21.

Вариантов уникальных параметров может быть  - от 1 до 5
Количество строк для каждого атрибута - не определено (от 1 до 20, например)

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

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

К сожалению, не могу сообразить как это сделать.
Буду рад содержательным ответам.

Заранее благодарю.
 
Перейти    
Определение диапазона ячеек, содержащих ошибки
 
#
17.01.2015 16:14:58
Добрый день, прошу помощи в решении следующей задачи:
Есть таблица, в некоторых ячейках которой внесены формулы. Формулы считаются на основе значений, которые вносит пользователь в другие ячейки.
Если ничего не внесено - в ячейках с формулами - ошибка "#Знач!".

Вопрос, как средствами VBA найти и выделить строки, в ячейках которых результаты формул - ошибки, в частности "#ЗНАЧ!", чтобы затем удалить их опять же средствами VBA.
Пробовал метод Find, но не могу сообразить, что вписывать в свойство What:=. Пробовал различные варианты, в т.ч. "#Знач!", но не подходит. Вариантов для перебора уже нет, потому обращаюсь к вам.

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

С уважением и надеждой на скорейший ответ...
Сергей
 
Перейти    
Создание выпадающего списка на основе значений в соседнем столбце
 
#
28.12.2014 23:15:12
Добрый день,

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

Суть:
Таблица 1 - исходные данные (справочник)
Таблица 2 - результаты.
Необходимо при заполнении "Столбец 1" (Таблица 2):
1. выбирать значения только из "выпадающего списка"
2. значения в выпадающем списке - это "Параметр 1" (Таблица 1), у которых "Параметр 2" = "да".

Сам решил эту задачу через создание промежуточной сводной таблицы с фильтром по "Параметр 2", но такой вариант не подходит, т.к. лист защищен паролем, который не хочется демонстрировать в коде.
Было бы замечательно создать нужный диапазон для выпадающего списка только  средствами VBA.

Заранее благодарю и надеюсь на скорейший ответ...
С уважением,
Сергей
 
Перейти    
Страницы: 1
Наверх