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

Страницы: 1
Писк подстановка: по заданному ключу и условию по дате
 
#
02.04.2024 10:10:42
evgeniygeo,
У меня было пересечение.
Добавил знак неявного пересечения, но тогда не подтягивается сумма.
Код
=@ЕСЛИОШИБКА(ФИЛЬТР(ИНДЕКС(СОРТ(ФИЛЬТР(Заказы;(Заказы[Клиент]=G11)*(Заказы[Дата покупки]>=F11));2;1);1);{0;1;0;1});ФИЛЬТР(ИНДЕКС(СОРТ(ФИЛЬТР(Заказы;(Заказы[Клиент]=G11)*(Заказы[Дата покупки]>=F11));2;1);0);{0;1;0;1}))

В итоге сделал для каждого признака отдельную формулу с номером столбца.

Можно это как сделать изящнее?)

Изменено: mikhailkhv - 02.04.2024 10:11:05
 
Перейти    
Писк подстановка: по заданному ключу и условию по дате
 
#
01.04.2024 19:15:28
Добрый день!
Весь день шерстил форму с поиском ответа. Нашел похожие решения, но преобразовать из для решения конкретно моей задачи не смог.
Мне необходимо сделать поиск по номеру клиента из одного массива в другом, и подставить дату ближайшую дате отсечки, но не позднее ее.
Пробовал через СУММПРОИЗВ, ФИЛЬТР и PQ, но так и не смог решить задачку.
Буду признателен за помощь.
Спасибо!
 
Перейти    
Мера: разница между датами
 
#
12.02.2024 14:36:08
Alex, Garrys,
Спасибо вам больше за помощь!
Скиньте номер кошелька в ЛС, с удовольствием угощу вас пивом)
 
Перейти    
Мера: разница между датами
 
#
09.02.2024 18:19:56
Alex,
Да, в том то и дело, в вашем примере я бы хотел видеть все рейсы которые начались в январе, а завершится они могут в любую другую дату.
В любом случае спасибо за предложенное решение.
Осталось понять как оно работает)
 
Перейти    
Мера: разница между датами
 
#
09.02.2024 16:30:22
Garrys,
Залил сюда. Спасибо!
https://disk.yandex.ru/d/5W0123d8F71sGw

Alex,
Спасибо большое!
Решение работает намного быстрее, чем PQ!)
Но когда я хочу сгруппировать оборот по коду или по месяцам, но выдает не верный результат, потому что мера считает только по массиву группировки (см. пример на яндекс-диске).
 
Перейти    
Мера: разница между датами
 
#
08.02.2024 11:13:07
Garrys,
Спасибо за ответ!
На массиве до 100 тыс строк из 6 фитчей (столбцы) работает отлично!
Но на 1 миллионе строк (данные за 2 месяцев) и 12 фитчах обработка данных занимает часа 2.
Я же хочу обработать данные за 2 года ~ 12 млн строк. Один раз уже запускал, но за сутки мой компьютер не справился.
Как думаете можно оптимизировать Ваше решение для PQ, или пора переходить на питон?)
Заранее спасибо!  
 
Перейти    
Мера: разница между датами
 
#
07.02.2024 16:56:37
Garrys,
Спасибо большое!
Попробовал ваше решение, но почему-то оно подходит только для первого рейса.
Например для второй рейса "40018962" с 15.01.2023 по 27.01.2023 во второй дате значение получается null (см. вложение).

Если не сложно, не могли бы объяснить что за магия происходит тут?))
Цитата
написал:
f =(x)=> [  z0 = Table.ToColumns(Table.Sort(x, a0{1})),
               z1 = List.Skip(z0{1}),
               z2 = Table.FromColumns(z0&{z1}, a0&{"Дата2"})],
   a2 = Table.Group(a1, a0{0}, {"Date", (x)=>f(x)[z2]})[Date],
Upd: вроде разобрался, надо было определить тип данных и все заработало, но вопрос про магию все еще актуален)
Изменено: mikhailkhv - 07.02.2024 17:15:10
 
Перейти    
Мера: разница между датами
 
#
06.02.2024 12:48:44
Цитата
написал:
у вас кол-во дат от 1 до 5. И как тогда считать эту вашу разницу между датами?
Добрый день!
Я поправил в описании проблемы. Я хочу посчитать меру "Средний оборот Транспорта" как среднее разницы между ближайшими датами по совпадающему номеру транспорта начиная с наименьшей даты, заканчивая наибольшей.
Я думаю это можно реализовать как разницу между максимальной и минимальной датой у одного и того же номера транспорта разделить на кол-во повторений в таблице без дубликатов - (минус) 1 (во вложении сделал через формулы excel).
Но мне кажется, если я в исходном файле захочу посмотреть оборот поквартально (оригинальный массив за год), то у меня будет искажение меры, нежели если считать в лоб через разницу ближайших дат.
Заранее спасибо!
 
Перейти    
Мера: разница между датами
 
#
06.02.2024 12:13:31
Привет всем,

Помогите пожалуйста с таким вопросом.
Есть таблица данных. В одном столбце есть повторяющиеся значения c "номером транспорта", в другом столбце "дата", не повторяющиеся для конкретного номера транспорта и другие столбы.
1. Нужно средствами Power Query убрать дубликаты по строкам где повторяются одновременно "Номер транспорта", "Дата", "Код" - это я сделал через удаление дубликата ключа, в котором объединил столбцы. Может быть есть более элегантный способ.
2. Нужно средствами Power Pivot создать меру, которая будет считать средний оборот рейса транспорта как среднее разницы между ближайшими датами по совпадающему номеру транспорта.

Заранее спасибо за помощь.
Изменено: mikhailkhv - 06.02.2024 12:33:16
 
Перейти    
Добавить вычисляемые столбцы к сводной таблице через меру. Power Pivot.
 
#
21.09.2018 11:57:11
Цитата
PooHkrd написал: ...попробую
Будет здорово! Со своей стороны тоже пробую сам разобрать в данном вопросе.
В любом случае скиньте мне, пжста, номер карты сбера или втб в личку. Хочу угостить Вас пивом в конце рабочей недели)
 
Перейти    
Добавить вычисляемые столбцы к сводной таблице через меру. Power Pivot.
 
#
20.09.2018 18:37:51
Цитата
PooHkrd написал: Везде где у вас стоят фильтры... их нужно переправить на таблицу Сортировка.
Спасибо еще раз! вроде справился)

Цитата
PooHkrd написал: Вам нужно по-плотнее почитать и по-экспериментировать с контекстами в моделях данных.
Буду благодарен, если подскажете где лучше почитать)
Можно последний "глупый" вопрос:
Почему у меня не отображается мера "Маржинальность продавца" в моем примере?
Заранее спасибо!
Код
Маржинальность продавца:=DIVIDE([Доходы]-[Расходы];[Кол-во продавцов])
----------------------------------------------------------------------
МАРЖА:=IF(ISFILTERED('Сортировка'[Группа]);
   SUMX(
      ADDCOLUMNS(
         'Сортировка';
         "прибыль";[Доходы]+[Расходы]+[Кол-во продавцов]+[Маржинальность продавца]);
      [прибыль]);
   [Доходы]-[Расходы]
)
Изменено: mikhailkhv - 21.09.2018 15:45:08
 
Перейти    
Добавить вычисляемые столбцы к сводной таблице через меру. Power Pivot.
 
#
20.09.2018 17:27:55
Цитата
PooHkrd написал: ...для конкретной вот этой структуры сводной это возможно.
Еще такой вопрос: А если сортировка происходит по другой связанной таблице? Возможно ли переписать меру, чтобы она работала корректно?

Цитата
StepanWolkoff написал: лучше бы вы в реальной структуре тогда показали данные
Не влезает файл
Изменено: mikhailkhv - 21.09.2018 15:44:02
 
Перейти    
Добавить вычисляемые столбцы к сводной таблице через меру. Power Pivot.
 
#
20.09.2018 15:39:01
а это процедура не увеличит время обработки файла? моя исходная модель данных состоит из 18 тыс строк и 12 столбцов

Цитата
PooHkrd написал: Но в принципе для конкретной вот этой структуры сводной это возможно.
Спасибо за решение!
Изменено: mikhailkhv - 21.09.2018 15:42:56
 
Перейти    
Добавить вычисляемые столбцы к сводной таблице через меру. Power Pivot.
 
#
20.09.2018 15:07:55
Добрый день!
Просьба помочь решить, либо подтвердить, что решения моей задачи нет)
Задача:
Есть следующая модель данных

Необходимо в Power Pivot вычислить меру МАРЖА=новая строка [Доходы-Расходы] так, чтобы в сводной таблице добавлялась еще строка "Кол-во продавцов".
 
Я знаю как это сделать с помощью вычисляемого поля и сортировки строк.

Но как этот механизм правильно реализовать через меру в Power Pivot - вопрос?!
Заранее спасибо!
 
Перейти    
Условное форматирование ячеек с заданным текстом внутри формулы, Условное форматирование ячеек с заданным текстом внутри формулы
 
#
18.09.2018 18:30:22
Цитата
БМВ написал:
В имя нужно занести формулу
Спасибо за помощь!
 
Перейти    
Условное форматирование ячеек с заданным текстом внутри формулы, Условное форматирование ячеек с заданным текстом внутри формулы
 
#
18.09.2018 18:17:30
Цитата
БМВ написал:
то см #4Если хотите через поиск, то=SUM(--ISNUMBER(FIND({"Обновление";"ААА.";"БББ"};FORMULATEXT(B5))))

Выдает такую ошибку(
 
Перейти    
Условное форматирование ячеек с заданным текстом внутри формулы, Условное форматирование ячеек с заданным текстом внутри формулы
 
#
18.09.2018 17:41:03
Цитата
БМВ написал:
Офис версии от 2013 и новее.ну или через макро функцию см. вариант _1
Спасибо большое!
Один нюанс, если я хочу подсвечивать по нескольким критериям (содержит текст "ААА" или "ИИИ" или "БББ".), то мне нужно создавать для каждого критерия новое правило?
формула типа
=ИЛИ(ПОИСК("ААА";Ф.ТЕКСТ(B3));ПОИСК("ИИИ";Ф.ТЕКСТ(B3)))
или
=ПОИСК(ИЛИ("ААА";"ИИИ");Ф.ТЕКСТ(B3))
не работает(
 
Перейти    
Условное форматирование ячеек с заданным текстом внутри формулы, Условное форматирование ячеек с заданным текстом внутри формулы
 
#
18.09.2018 14:02:23
Цитата
БМВ написал:
=sum(countif(a1;{"*ААА*" ; "*ИИИ*"; "*БББ*"}))только это позволит искать "ААА" или "ИИИ" или "БББ" в резултатах формул. Если всеж именно в формулах, то для 2013 и новее=sum(countif(formulatext(a1);{"*ААА*" ; "*ИИИ*"; "*БББ*"}))
Добрый день!
Этим условием задавать функцию?
Цитата
Sanja написал:
Что-то у Вас все в кучу. И макросы и формулы и УФ. Лучше покажите в файле-примере. Как есть - Как надо
для поиска ячеек в которых есть расчет, а не значение, я макросом создавал функцию типа
Function IsFormula(Check_Cell As Range)
IsFormula = Check_Cell.HasFormula
End Function
А потом уже использовал ее в УФ.
Поэтому я хотел по аналогии написать макрос на поиск определенных слов в формуле, но не разобрался в синтаксисе)
Пример во вложении.
Заранее Спасибо!
 
Перейти    
Условное форматирование ячеек с заданным текстом внутри формулы, Условное форматирование ячеек с заданным текстом внутри формулы
 
#
18.09.2018 10:03:10
Добрый день, уважаемые!
Столкнулся такой задачей:
Нужно подсветить ячейки с формулами в которых содержится текст "ААА" или "ИИИ" или "БББ".
Как правильно описать макросом функцию, чтобы использовать ее в правиле "условного форматирование"?
За ранее спасибо!
 
Перейти    
Сумма поставки из графика накопительным итогом
 
#
18.08.2016 11:50:51
Спасибо!
 
Перейти    
Сумма поставки из графика накопительным итогом
 
#
18.08.2016 10:59:06
Добрый день, Уважаемые!
Просьба помочь решить задачку:
У меня есть график отгрузки товара накопительным, мне необходимо посчитать сколько товара было отгружено ВСЕГО.
В примере решение задачи со вспомогательной строкой
Нужно решение без вспомогательной строки.
Заранее спасибо!
 
Перейти    
ВПР в Power Pivot
 
#
28.06.2016 16:27:48
Андрей VG, Спасибо за участие! Ждал от Вас ответа.
Примерная реализация того что я хочу сделать я показал в посте #7
Цитата
Прикрепленные файлы
Пример v.1.zip  (46.83 КБ)
а под фразой
Цитата
mikhailkhv написал:
причем так чтобы все индексированные значения после даты индексирования были бы равны значению в момент индексирования.
я имел ввиду следующее: Например в таблицу TD_Input для авто1 категории ДР в определенную дату (01.01.2018) вводим значение индекса. Стоимость автомобиля в эту дату индексируется на величину (1+индекс) и в последующие месяцы стоимость этого авто будет равной новой индексированной стоимости.
 
Перейти    
ВПР в Power Pivot
 
#
27.06.2016 19:14:24
Еще раз добрый день всем!
Решил задачу, но через одно место))
Коряво потому, что
  1. Данные надо выгружать на лист
  2. После обновления входящих данных (Инфляция) надо "Обновлять данные"(Ctrl+alt+F5)
Уверен, что можно сделать проще и элегантней с помощью DAX формул.
Если тут имеются специалисты, буду рад Вашей помощи.
Спасибо!
Изменено: mikhailkhv - 28.06.2016 11:13:26
 
Перейти    
ВПР в Power Pivot
 
#
23.06.2016 12:47:30
Юрий М, я про номер темы. Чтобы файл во вложении называть соответственно созданной теме (как я понял правило хорошего тона тут)
 
Перейти    
ВПР в Power Pivot
 
#
23.06.2016 12:18:20
Добрый день, уважаемые!
Пытаюсь рабочую модель бюджета перевести в модель данных Power Pivot. Только осваиваю надстройку и уже столкнулся с проблемами с использованием DAX формул. Просьба помочь разобраться.
Задача стоит следующая:
Проиндексировать стоимость автомобилей в определенную дату, или другими словами данные на листе Data проиндексировать переменными на листе ТD_Inputs в соответствии со "статьёй", "подстатьёй" и "датой", причем так чтобы все индексированные значения после даты индексирования были бы равны значению в момент индексирования.
Заранее Спасибо!
PS: как узнать номер поста?
https://1drv.ms/x/s!Av2ZRQ_zbcOKwlNPYb-s0BG1s-UJ
Изменено: mikhailkhv - 23.06.2016 12:30:10
 
Перейти    
Макрос для изменения формулы в ячейке
 
#
16.06.2016 17:01:47
Уважаемые, почему не работает Find?
Код
Sub Terst()
    Set Amount = Worksheets("Расчет").Range("A:A").Find(What:="дата", LookAt:=xlWhole).Column
    MsgBox Amount
End Sub
 
Перейти    
Макрос для изменения формулы в ячейке
 
#
16.06.2016 15:16:20
Forve, спасибо! Уже сделал.
Вопрос про выполнение макроса в несколько excel-ких книг одновременно остается открытым: как написать макрос который будет гулять по всем открытым книгам?
Изменено: mikhailkhv - 16.06.2016 15:30:47
 
Перейти    
Макрос для изменения формулы в ячейке
 
#
16.06.2016 13:23:26
Кнопка цитирования не для ответа [МОДЕРАТОР]

Спасибо за совет! совместил Ваше решение со своим (см. вложения).
Еще вопрос: как запустить этот макрос в нескольких (более 100) книгах одновременно? это вообще можно реализовать не открывая эти книги?

+хочу реализовать возможность ввода даты и значений которые надо менять и на которые надо сделать замену.
Буду рад помощи))
 
Перейти    
Макрос для изменения формулы в ячейке
 
#
16.06.2016 10:23:13
Forve, спасибо за участие! Возможно я не верно объяснил задачу в начале, но у меня задача удалить 1% (или заменить на 0%) во всех формулах в 3-ем столбце в октябре 2016г. и далее ПОСЛЕ этой даты.
Я написал макрос(не без помощи моих коллег) для замены "1%" на "0%", НО сейчас задача стоит следующая:
Оказалось, что в файле в 3-ем столбце могут быть пустые ячейки (как в примере), в таком случае длина векторов по датам и по значениям будут разные и макрос выдает ошибку "Script out of range".
К сожалению ответа на решение этой проблемы у меня нет.
Forve, может быть Вы сможете мне помочь?
Заранее спасибо!  
 
Перейти    
Макрос для изменения формулы в ячейке
 
#
15.06.2016 14:59:16
Добрый день, уважаемые!
Нужна ваша помощь в написании макроса, который удаляет в 3-ем столбце в октябре 2016 и после этой даты слагаемое - "1%*столбец1" в формуле ячейки.
Названия столбцов не повторяются, но расположение столбца3 на листе может быть разное.
Дата может может быть любая, но месяц и год точный - октябрь 2016г.
Заранее спасибо!
 
Перейти    
Страницы: 1
Наверх