Выбрать дату в календареВыбрать дату в календаре

Страницы: 1
Вычислить порядковый номер недели, Недели начинаются не с начала года и состоят из нескольких лет
 
Приветствую!

Дано:
Таблица состоящая из столбцов: "Неделя года" и "Месяц" (в формате ДД.ММ.ГГГГ на начало месяца т.е. Октябрь 2024 - 01.10.2024)

Необходимо:
Вычислить в соседнем столбце для текущей строки порядковый номер недели.
Например, таблица начинается с 49 неделя 2024 года и строка должна иметь порядковой номер 1,
50 - 2024 - 2,
51 - 2024 - 3,
52 - 2024 - 4,
1 - 2025 - 5,
2 - 2025 - 6,
3 - 2025 - 7 и так далее

Недели могут быть пропущены и не отсортированы, дополнительные столбцы возможны, динамические массивы не желательны.
Измерение времени вычисления способом от MS всегда возвращает 0
 
Добрый день!

На официальной странице microsoft по производительность Excel есть код vba по замеру времени вычисления.

Проблема: при выполнении макроса всегда возвращает 0.
Подскажите, в чём может быть причина?

Microsoft® Excel® LTSC MSO (версия 2409 Сборка 16.0.18025.20030) 64-разрядная
Перенос правил УФ в VBA, Повысить производительность файла
 
Добрый день!

Подскажите, пожалуйста, не встречалась ли Вам надстройка или инструмент/готовый код позволяющий перенести правила условного форматирования в код VBA.
Для применения УФ на отдельную кнопку - дабы повысить производительность файла? Или это гиблое дело?

Возможно есть вариант проще? Чтобы на уровне книги, пользователю, можно было регулировать обновление правил УФ.
Например, положение элемента, которое отвечало за - обновлять правила по умолчанию / не обновлять
Изменено: Vladimir K - 16.05.2024 13:48:21
Найти самую позднюю дату для нескольких диапазонов дат и клиентов подставив соседнее значение, Power Query
 
Добрый день!
Подскажите, пожалуйста, способ для решения следующей задачи в power query (рабочий набор с клиентами на несколько млн строк):

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

Например:

(диапазон)
Дата сДата по
01.01.202331.12.2023
(даты клиента)
КлиентДатаГород
Клиент00101.12.2022Город4
Клиент00110.01.2023Город1
Клиент00118.05.2023Город4
Клиент00120.01.2024Город2
В этот диапазон входят даты из 2 и 3 строки, при этом самая поздняя в 3 строке

В результате хочу получить:
КлиентДата сДата поМакс дата в   диапазонеГород
Клиент00101.01.202331.12.202318.05.2023Город4
подробнее в примере файла
Изменено: Vladimir K - 19.04.2024 13:06:38
Создать ряд чисел с перебором каждого отдельно взятого разряда, PQ
 
Доброго дня!

Хочу из одного числа получить набор чисел, где будет заменена каждая цифра разряда отдельно, на цифры от 0 до 9 в PQ
(в примере есть решение формулами)

Пример: 123 => {120, 121, 122, 123.. 129} , {103, 113, 123, 133.. 193}, {023, 123, 223, 323.. 923}

Разрядность может меняться, но это не обязательно учитывать
доп.по желанию: убрать исходное число из получившегося ряда (123)

Буду рад Вашим вариантам и всех с праздником!
Как подготовить третий аргумент функции ЧИСТРАБДНИ.МЕЖД из двух выходных дней
 
Доброго дня!

Использую функцию ЧИСТРАБДНИ.МЕЖД столкнулся с тем, что для третьего аргумента у меня входные из названий двух дней недели
Скрытый текст
Таким образом рождается задача преобразовать строку "Суббота" и "Воскресенье" в "0000011"

Решил это через сложение двух двоичных путем перевода их из дв в дес и обратно с использованием справочника
Скрытый текст

0000010 + 0000001 = 2 + 1 = 3, где 3 как двоичное с разрядностью 7 =0000011

Вопрос: существует ли решение по проще (без использования доп.столбцов/таблиц, vba, надстроек и пр. т.е. только штатные функции excel) т.к. мне кажется, что я "намудрил" слегка
Сумма разности нескольких значений времени при группировке строк, Power query
 
Доброго времени суток!

Помогите, пожалуйста, решить задачу с помощью power query

Из
ИзмерениеВремя
Изм1 10:00
Изм1 14:00
Изм1 18:00
Изм2 14:00
Изм2 16:00
В
Измерение Время
Изм1 10:00 14:00 18:00 (08:00)
Изм2 14:00 16:00 (02:00)


Где по каждому измерению нужно перечислить (сгруппировать в строку) все значения времени и найти сумму разности между значениями:

Пример вычисления для первого измерения:
(14-10) + (18-14) = 4 + 4 = 8

Значений при группировке может быть больше 3, но не меньше 2
PQ. Применение функции для тек. и предыд. стоки содержащей List, List.RemoveItems для списка списков
 
Приветствую!
Необходимо получить кол-во новых элементов в следующем списке по сравнению с предыдущим:

1. {A, B, C}                  
2. {B, C, D, E, F} - 3 элемента (B и C были в 1)
3. {C, D, H} - 1 элемент (C и D были в 2)

Вот такой запрос я сочинил для примера не требующий файла
Код
let
  Источник = #table(
    type table [Дата = date, Листы = list], 
    {
      {#date(2022, 11, 30), {"A", "B", "C"}}, 
      {#date(2022, 12, 31), {"B", "C", "D", "E", "F"}}, 
      {#date(2023, 01, 31), {"C", "D", "H"}}
    }
  ), 
  #"Сортированные строки" = Table.Sort(Источник, {{"Дата", Order.Ascending}}), 
  #"Добавлен индекс" = Table.AddIndexColumn(#"Сортированные строки", "Индекс", 0, 1, Int64.Type), 
  БуферЛистов = List.Buffer(#"Добавлен индекс"[Листы]), 
  #"Добавлен пользовательский объект" = Table.AddColumn(
    #"Добавлен индекс", 
    "Предыдущий лист", 
    each List.Count(List.RemoveItems(БуферЛистов{[Индекс]}, БуферЛистов{[Индекс] - 1})))
in
  #"Добавлен пользовательский объект"

Проблема в том, что для больших файлов (9 мб, 15мб, 10 мб) запрос превращается в гигабайтный и не завершается
* рабочие файлы загружаются из папки и немного преобразовываются

Прикрепил пример рабочих файлов в архиве
Изменено: Vladimir K - 27.01.2023 12:16:47
Ошибка "Comma" при использовании "??" в новых версиях, Power query
 
Доброго всем дня! Подглядел когда то функцию замены значений по справочнику
Код
( table as table, columnNames as list, optional newColumnNames as list, optional operation as text, optional dictTableName as text ) =>
  let
    Dict = ПредстНасПунктов,
    Lookup = List.Buffer ( List.Zip ( Table.ToColumns ( Dict ) ) ),
    NewNames = newColumnNames ?? List.Transform ( columnNames, ( x ) => "new_" & x ),
    Corrected = List.Accumulate ( columnNames, {}, ( s, c ) => s & { List.ReplaceMatchingItems ( Table.Column ( table, c ), Lookup ) } ),
    ResultAdd = Table.FromColumns ( Table.ToColumns ( table ) & Corrected, Table.ColumnNames ( table ) & NewNames ),
    ResultTransform = Table.TransformColumns (
      table,
      List.Transform ( columnNames, ( x ) => { x, ( y ) => List.ReplaceMatchingItems ( { y }, Lookup ){0} } )
    ),
    Result = if operation = "transform" then ResultTransform else ResultAdd
  in
    Result

В строке 5 есть фрагмент:
Код
NewNames = newColumnNames ?? List.Transform ( columnNames, ( x ) => "new_" & x ),

В другой версии (2019 MSO (16.0.10827.20138); Версия 1809) возникает ошибка "Expression.SyntaxError: Ожидается токен Comma" и ругается на "??".

Нигде не нашел информацию в документации PQ по использованию "??" (двух вопросительных знаков).

Подскажите, пожалуйста, что выполняют эти символы и как обойти ошибку?
Визуализация связей, Визуализировать данные таблицы посредством связей элементов с известными указателями
 
Приветствую!

Существует ли способ, без особых выкрутасов визуализировать таблицу, где представлены связи из множества элементов?
Если кто-то сталкивался с подобной задачей или есть идеи буду рад услышать ваши предложения

Или быть может Вам известны другие инструменты, не Excel, где подобно можно реализовать?

Вот некая абстракция:

https://ibb.co/684jxyM

Суть такова, что два элемента в строке таблицы объединяет один №, но у одного элемента может быть до 4-х №, которые могут связывать с другими элементами.

В таблицы могут быть группы которые между собой не имеют связи (отделено пунктиром), группы эти ни как не определены, но это задача уже другого порядке, сейчас не об этом
Изменено: Vladimir K - 07.07.2022 15:49:02
Подставить значение из списка в случайную строку таблицы в PQ., Генерация строк с заданной долей вероятности от общего кол-ва строк
 
Хорошего всем начала недели и привет!

Имеется таблица "Список_показателей" с самими показателями и "доля этого показателя" (х.з как точно выразится, дальше поймете  :oops: )

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

(например показатель "А-1" его доля определена как 30%, а "А-2" - 70%, заполняя 100 строк должно получится 30 строк с показателем "А-1" и 70 строк с "А-2")
Изменено: Vladimir K - 12.08.2022 17:52:48
Транспонирование названий имен столбцов через один, сохраняя значение транспонируемого и след. столбца, PQ
 
Приветствую!
Собственно вот, пытаюсь сделать своего рода Unpivot в power query. Помогите, пожалуйста, с решением
Скрин
Изменено: Vladimir K - 20.04.2022 18:02:06
Каждой группе подобных текстовых значений присвоить ID., Power Query. Идентификация SMS по содержанию сообщения.
 
Доброго времени суток!

Имеется таблица "Сообщения" , где каждая строка это текст SMS сообщения и прочая информация в др. столбцах.

Задача: каждой группе подобных сообщений присвоить идентификационный код

Условие: На выходе должна быть таблица, как исходная, не лист, без потери столбцов.

Моё решение варварское, в лоб:
Я создаю доп. таблицу "Справочник" с шаблоном для группы сообщений, проверяю через комбинации if и текстовых функции (Text.StartsWith, Text.Contains, Text.EndsWith).
Заранее задаю в запросе оператора, к какой строке применить, и возвращаю код из этой же строки.

Скрин https://ibb.co/J7pX2BD

Код
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Сообщения"]}[Content],
    Код = Table.AddColumn(Источник, "Код", each
        if                 [Текст сообщения] =Справочник{0}[Условие]  then Справочник{0}[Код] else
        if Text.StartsWith([Текст сообщения], Справочник{1}[Условие]) then Справочник{1}[Код] else
        if Text.Contains(  [Текст сообщения], Справочник{2}[Условие]) then Справочник{2}[Код] else
        if Text.EndsWith(  [Текст сообщения], Справочник{3}[Условие]) then Справочник{3}[Код] else
                         0)
in
    Код

Можно заменить, что перебор строк последовательный, исходя из этого у меня возникла идея (как по мне опять же варварская) -
Где-то на просторах этого форума я видел, что можно заранее подготовить код запроса на листе excel (например формулами) и передать его в PQ, но найти этой темы у меня не получилось.

Подскажите, пожалуйста, как на ваш взгляд решение через заранее подготовленный запрос на листе excel?
Если ок, то дайте пример как это реализовать или быть может у Вас будет собственная идея.
Excel 2016
Благодарю за внимание!
PQ. Вернуть самый ранний статус по ключу, Ускорить обработку запроса вернув ранний статус (один-ко-многим)
 
Добрый день

Сделал запрос кнопками в PQ, но объём большой и обрабатывается долго, всё зависает. Результат получил, но чувствую, что можно быстрее.

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

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

Я делаю следующим образом:
В "Таблица2" группирую по коду применяя операцию минимум по периоду, получаю код и период самый ранний период (без статуса), потом присоединяю с источником "Таблица2" и возвращаю статус по коду на период. После чего присоединяю статус по код для "Таблица1".

Подскажите, пожалуйста, есть ли возможность ускорить эту обработку данных?
Изменено: Vladimir K - 23.03.2021 13:00:28 (Опечатка)
Суммирование по условию пропуская скрытые, Оптимизация СУММПРОИЗВ*ПРОМЕЖУТОЧНЫЕ.ИТОГИ (СМЕЩ)
 
Приветствую! Прошу ассиста в вопросе ибо не силен, ленюсь и немного сдался.

Использую набор функций в следующей формуле:
Код
=СУММПРОИЗВ(($A$2:$A$23=$A27)*ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;СМЕЩ(C$1;СТРОКА(C$2:C$23)-СТРОКА(C$1);)))
Вопрос: Есть ли альтернативная формула?
  • более производительная, ибо на офисных пекарнях в таблицах с большим количеством строк не комфортно

Формула должна возвращать сумму значений в табл. "Динамическая" из другой табл. "Исходная":

  1. АГРЕГИРОВАТЬСЯ - Пропуская скрытые фильтром строки
  2. Принимать СУММЕСЛИ - по другому =СУММПРОИЗВ(($A$2:$A$23=$A27)
  3. Не пересчитываться при каждом изменении
В моём понимании вся проблема в "=СМЕЩ()" т.к. она (вроде как) является "Переменной функцией", выполняющая вычисления после любых изменений с ячейками
Пробовал =АГРЕГАТ(), но всё тщетно, получаю #ЗНАЧ!
Скрытый текст

Страницы: 1
Loading...