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

Страницы: 1 2 3 4 След.
Заполнение отчетов данными из умной таблицы
 
#
17.05.2024 22:25:12
Идеальный вариант - конечно же сводная. Но если задача в том, чтобы заполнять только таблицы установленных форм, то функция СУММЕСЛИМН вам в помощь:

Отчёт ТС
Код
=СУММЕСЛИМН(Data[[Время]:[Время]];Data[[Дата]:[Дата]];ДАТА(2024;4;отчет_ТС!F$3);Data[[Наименование ТС]:[Наименование ТС]];отчет_ТС!$B5;Data[[Гос.№]:[Гос.№]];отчет_ТС!$C5;Data[[Объект]:[Объект]];отчет_ТС!$D5;Data[[Смена]:[Смена]];отчет_ТС!$E5)

Отчёт ТАБ
Код
=СУММЕСЛИМН(Data[[Время]:[Время]]; Data[[Сотрудник]:[Сотрудник]];отчет_ТАБ!$B5;Data[[Объект]:[Объект]];отчет_ТАБ!$C5;Data[[Смена]:[Смена]];отчет_ТАБ!$D5;Data[[Дата]:[Дата]];ДАТА(2024;4;отчет_ТАБ!E$3))

Тут есть одна тактическая хитрость. Дело в том, что столбцы умной по умолчанию не закрепляются при протягивании в бок. И нужно закреплять их вручную таким вот образом - Data[[Время]:[Время]]
Спасибо пользователю "Василию ПВР" за подсказку на этом форуме - Закрепить столбцы умной таблицы
Шлюхогон42
 
Перейти    
В сводной таблице по полю нарастающ Итог отобрать только 20%, Создал поле с нарастающим итогом до 100%, как фильтром вывести только до 20% по нарастающему итогу
 
#
15.05.2024 15:36:45
Цитата
написал:
но есть потребность реализовать без Power Pivot , вот ищу решение
Если вам нужно реализовать это только формулами в Excel, зачем заморачиваться со сводной?
Сделайте все расчёты в исходной (умной) таблице. См. вложение.  
Шлюхогон42
 
Перейти    
Общий итоговый график, Построение суммарного профита на дистанции в покере на графике
 
#
15.05.2024 01:35:56
Считаешь профит по формуле "нарастающего итога", строишь график по столбцу "профит". См. вложение
Шлюхогон42
 
Перейти    
В сводной таблице по полю нарастающ Итог отобрать только 20%, Создал поле с нарастающим итогом до 100%, как фильтром вывести только до 20% по нарастающему итогу
 
#
15.05.2024 00:13:42
Это у вас "ABC-анализ".
Я знаю, как это сделать в Power Pivot. См. вложение
Фильтруете по категории A - останутся только те товары, чья доля накопленных продаж от общей суммы продаж меньше или равна 20%
Обратите ещё внимание на то, что у вас позиции (документ продажи) в исходной (умной) таблице повторяются.
Для корректной работы таблицу нужно предварительно "схлопнуть" (сгруппировать) по столбцу "документ продажи"
Шлюхогон42
 
Перейти    
Ошибка при создании календаря в Power BI Desktop
 
#
14.05.2024 19:47:11
Цитата
написал:
При написании кода (код взяла из интернета) в целях создания таблицы Календарь в Power BI Desktop  выскочила ошибка:
А есть же простая функция CALENDARAUTO. Почему не использовать её?
Шлюхогон42
 
Перейти    
Формула сложного ВПР с вложением, ВПР с пропорциональным распределением
 
#
14.05.2024 19:43:51
Вот так, например:
Код
=ОКРУГЛ(ВПР(A2;$I$2:$J$5;2;0)*СУММЕСЛИМН($C$2:$C$9;$A$2:$A$9;A2;$B$2:$B$9;B2)/СУММЕСЛИ($A$2:$A$9;A2;$C$2:$C$9);0)

Только номера надо продлить по столбцу, чтоб не было пустых ячеек. Умеете продлевать не вручную?
Изменено: Дмитрий Никитин - 14.05.2024 19:44:32
Шлюхогон42
 
Перейти    
Распределить данные по столбцам
 
#
09.05.2024 01:27:35
У вас есть столбец "Название папки", есть столбец "пользователи папки на текущий мом".
Что именно и как вы хотите отсортировать?
Шлюхогон42
 
Перейти    
как соединить (слить) уникальные данные в столбце А, но данные в столбце В при этом должны соединиться через ;, есть таблица два столбца, в столбце А попадаются ункиальные значения, им соответствуют неуникальные значения в столбце В
 
#
07.05.2024 18:18:35
xenpush, с помощью Power Query.
Если знакомы с инструментом, то готов объяснить - там всего два действия. Пишите лучше мне в телегу (тут это расписывать не вижу смысла).
Если нет, то просто добавьте в таблицу "БЫЛО" свежие данные, затем во вкладке "Данные" нажмите "обновить всё", и таблица "СТАЛО" обновится автоматически.  
Шлюхогон42
 
Перейти    
как соединить (слить) уникальные данные в столбце А, но данные в столбце В при этом должны соединиться через ;, есть таблица два столбца, в столбце А попадаются ункиальные значения, им соответствуют неуникальные значения в столбце В
 
#
06.05.2024 23:35:56
Так?
Шлюхогон42
 
Перейти    
[ Закрыто] Помощь новичку
 
#
06.05.2024 18:11:14
Kkast, СУММЕСЛИМН. Почитайте справку по функции
А если хотите готовую формулу - приложите файл
Шлюхогон42
 
Перейти    
Повторяющееся значение при создании новой строки в таблице
 
#
06.05.2024 18:08:51
Решение из серии "вкл/выкл":
преобразуйте таблицу в обычный диапазон, а затем опять в умную.  
Шлюхогон42
 
Перейти    
Выбор значения из таблицы по нескольким параметрам, находящихся в диапазоне
 
#
06.05.2024 17:17:31
Kuzmich, те, кто знают VBA, не признают ничего, кроме макросов )))
Шлюхогон42
 
Перейти    
Выбор значения из таблицы по нескольким параметрам, находящихся в диапазоне
 
#
06.05.2024 17:09:10
Формула:
Код
=ИНДЕКС($L$3:$L$7;ПОИСКПОЗ(1;($I$3:$I$7=A3)*($J$3:$J$7<=B3)*($K$3:$K$7>B3);0))

Формула массива, поэтому нажимать "CTRL SHIFT ENTER"
Шлюхогон42
 
Перейти    
Создать вычисляемое поле в сводной из сложного запроса
 
#
05.05.2024 19:17:42
Много лишних действий у вас в файле.
Все вычисления нужно проводить в Power Pivot
Power Query для консолидации и приведения данных в порядок.
Зачем вам мера именно по яблокам? По сливам, грушам и прочим фруктам также потребуется создавать отдельные меры ? Не лучше ли реализовать это срезом?
Шлюхогон42
 
Перейти    
Создать в модели данных столбец с номером заказа, В модели данных Power Pivot создать вычисляемый столбец с номером заказа с помощью функции ALLEXCEPT
 
#
02.05.2024 21:27:32
Цитата
написал:
Вам бы купить книжку Подробное руководство по DAX…
К сожалению, не мой формат. Не воспринимаю инфу в текстовом виде. Мне подходит только  "говорящая голова", которая объясняет, показывает, а я повторяю за ней и выполняю задания для самоконтроля

Посоветуйте может быть какой-нибудь хороший курс по DAX.  
Изменено: Дмитрий Никитин - 13.05.2024 23:25:04
Шлюхогон42
 
Перейти    
Создать в модели данных столбец с номером заказа, В модели данных Power Pivot создать вычисляемый столбец с номером заказа с помощью функции ALLEXCEPT
 
#
02.05.2024 20:08:24
Alex, ок, давайте я вам попробую объяснить свою логику, а вы мне укажете, в чём её изъяны

Цитата
написал:
2. По вашей 2 мере:
Тут я просто добавил к вашей мере FILTER. С переменной вместо EARLIER это будет вот так:
Код
=var d = Orders[Order Date] 
RETURN 
     CALCULATE(                            
           DISTINCTCOUNT(Orders[Order ID]);   
           ALLEXCEPT(
                 Orders;
                 Orders[Customer ID]);                 
           FILTER(                    
                 'Orders';  
                 'Orders'[Order Date] <= d))

потому что я не понимаю, как 'Orders'[Order Date] <= d может работать без FILTER

Вот какая логика: ALLEXCEPT - убирает все фильтры, кроме как с Orders[Customer ID] . Но иначе можно сказать, что она фильтрует только по Orders[Customer ID] Значит получаем на входе таблицу, отфильтрованную по Customer ID, который соответствует данной строке.
Далее функция FILTER забирает эту таблицу  (отфильтрованную по Customer ID) и начинает фильтровать её, чтобы дата заказа текущей строки была больше или равна всех дат в этой  (отфильтрованной по Customer ID) таблице. Таким образом, сколько будет меньше или равно дат дате текущей строки  (отфильтрованной по Customer ID) таблицы, такое количество и будет соответствовать номеру заказа.

То есть я не понимаю: почему так работает:
Код
=var d = Orders[Order Date] 
RETURN 
     CALCULATE(                            
           DISTINCTCOUNT(Orders[Order ID]);   
           ALLEXCEPT(
                 Orders;
                 Orders[Customer ID]);                 
           'Orders'[Order Date] <= d)


а вот так:
Код
=var d = Orders[Order Date] 
RETURN 
     CALCULATE(                            
           DISTINCTCOUNT(Orders[Order ID]);   
           ALLEXCEPT(
                 Orders;
                 Orders[Customer ID]);                 
           FILTER(                    
                 'Orders';  
                 'Orders'[Order Date] <= d))
нет!

Цитата
написал:
3. По 3 мере вообще ничего не понятно
А тут я просто исходил из логики, что если можно использовать функцию EARLIER (ну или переменные - как в вашем примере) без функции FILTER (как опять же в вашем примере), то почему же не работает тогда формула расчёта количества заказов:
Код
= var customer = 'Orders'[Customer ID]
  RETURN 
  CALCULATE(                  
      DISTINCTCOUNT(Orders[Order ID]);                
      'Orders'[Customer ID] = customer)
Выдаёт одни единицы
Шлюхогон42
 
Перейти    
Создать в модели данных столбец с номером заказа, В модели данных Power Pivot создать вычисляемый столбец с номером заказа с помощью функции ALLEXCEPT
 
#
02.05.2024 17:46:35
Alex, Спасибо за помощь!

я заменил переменную d на
Код
 EARLIER('Orders'[Order Date])
и всё правильно считает.

Только я не понимаю как это работает.
Почему функция EARLIER используется без функции FILTER? А если я всё же её заворачиваю в FILTER:
Код
= CALCULATE(                  
          DISTINCTCOUNT(
                Orders[Order ID]);                  
                ALLEXCEPT(                               
                     Orders;
                     Orders[Customer ID]); 
                FILTER(
                    'Orders';      
                     'Orders'[Order Date] <= EARLIER(Orders[Order Date])))

то выдаёт какую-то ахинею

И почему, наоборот, если я в столбце с количеством заказов, рассчитанном с помощью функций FILTER и EARLIER, убираю FILTER:

Код
= CALCULATE(                  
       DISTINCTCOUNT(Orders[Order ID]);      
       'Orders'[Customer ID] = EARLIER('Orders'[Customer ID]))

то выдаёт мне одни единицы.

Значит в каких-то случаях можно использовать EARLIER без FILTER, а в каких-то нет? Как это понять?
Изменено: Дмитрий Никитин - 02.05.2024 18:04:02
Шлюхогон42
 
Перейти    
Создать в модели данных столбец с номером заказа, В модели данных Power Pivot создать вычисляемый столбец с номером заказа с помощью функции ALLEXCEPT
 
#
02.05.2024 15:51:24
Уважаемые знатоки DAX!

Помогите, пожалуйста, с формулой расчёта номера заказа.

Есть простенькая модель данных: ID клиента,  ID заказа, дата заказа и количественные характеристики заказа (они тут значения не имеют).

Я самостоятельно создал два вычисляемых столбца "Кол-во заказов" в разрезе каждого клиента.

Первый столбец - с использованием функции ALLEXCEPT:
Код
=CALCULATE(
         DISTINCTCOUNT(Orders[Order ID]);
         ALLEXCEPT(
             Orders; 
             Orders[Customer ID]))

Второй столбец - с использованием функций FILTER и EARLIER:
Код
=CALCULATE(
          DISTINCTCOUNT(Orders[Order ID]);
          FILTER(
              'Orders';
              'Orders'[Customer ID] = EARLIER('Orders'[Customer ID])))


Как я понимаю, использование функций ALLEXCEPT и FILTER + EARLIER в качестве внутреннего контекста фильтра в функции CALCULATE - равнозначно.

Затем я создал вычисляемый столбец "Номер заказа" с помощью функций FILTER и EARLIER:
Код
= CALCULATE( 
           DISTINCTCOUNT('Orders'[Order ID]); 
           FILTER( 
              'Orders'; 
              'Orders'[Customer ID] = EARLIER('Orders'[Customer ID]) && 
              'Orders'[Order Date] <= EARLIER('Orders'[Order Date])))

Вопрос: как теперь создать вычисляемый столбец "Номер заказа", но уже с помощью функции ALLEXCEPT ?
Изменено: Дмитрий Никитин - 02.05.2024 15:53:19
Шлюхогон42
 
Перейти    
Суммирование значений подгрупп в группу
 
#
28.04.2024 17:26:13
Цитата
gling написал:
СУММПРОИЗВ(СУММЕСЛИМН($E:$E;$C:$C;($O$4:$O$9=H25)*$N$4:$N$9))

Интересное решение. Чуть вас поспрашиваю по формуле.

Вот эта конструкция:

Код
($O$4:$O$9=H25)*$N$4:$N$9)


тут $O$4:$O$9=H25 нам выдаст массив из ИСТИН и ЛЖЕЙ. ИСТИНА - это однёрка, ЛОЖЬ - нуль.
тут $N$4:$N$9 - номера покупателей.
Соответственно номера ($N$4:$N$9) умножаются на однёрки и нули ($O$4:$O$9=H25), остаётся массив из номеров покупателей и нулей.
(Кстати, интересно - как бы решалась задачка, если бы столбец с покупателями был бы не числовой, а текстовый?)

В итоге мы получаем массив из нескольких условий. Например для первой строки:
{1:2:0:0:0:0}

Вопрос: как тут работает функция СУММПРОИЗВ?
Насчёт СУММ понятно - она будет суммировать результаты по покупателю 1 и покупателю 2
А ПРОИЗВ - что тут с чем перемножается?
Шлюхогон42
 
Перейти    
Сводная таблица. От даты остался только месяц
 
#
27.04.2024 00:23:27
Цитата
EvaAleks написал:
При создании сводной выводится только "янв"
Это автоматическая группировка дат по месяцам, кварталам и годам.
У вас в исходной таблице данные по месяцам, но месяц задан 1-м числом месяца.
Сводная воспринимает 1-е число месяца как дату и автоматически группирует по месяцам, кварталам и годам
Если вам нужны даты, как в исходной таблице, можете просто нажать правой кнопкой мышки по "авг" и разгруппировать.
Изменено: Дмитрий Никитин - 27.04.2024 00:24:27
Шлюхогон42
 
Перейти    
Cводная с разной грануляцией периодов
 
#
27.04.2024 00:03:23
Цитата
RayI написал:
Как можно модифицировать исходную таблицу для сводной,

Исходная таблица должна иметь одинаковую разрядность периодов.

То есть в столбце "период" должны быть либо только годы, либо только кварталы, либо только месяцы, либо только дни.

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

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

Приводите исходные данные в порядок - тогда и со сводной проблем не будет
Изменено: Дмитрий Никитин - 27.04.2024 00:04:11
Шлюхогон42
 
Перейти    
РР. Поиск максимального условия и суммирование значений
 
#
26.04.2024 14:39:31
surkenny, я с вашего позволения ещё поспрашиваю по этой задачке.
Я пытался сделать это (то есть вывести значения по максимальному индексу в разрезе проекта)  в одно действие по примеру формулы нарастающего итога.
Код
=CALCULATE(
      sum('Таблица1'[Значение]);
      FILTER(
              ALL('Таблица1');
              'Таблица1'[Индекс (№записи)] = MAX('Таблица1'[Индекс (№записи)])))

Вопрос: почему не получается? Можно ли как-то задать одной формулой (без если) вывод значения по максимальному индексу в разрезе проекта?
Изменено: Дмитрий Никитин - 26.04.2024 15:56:08
Шлюхогон42
 
Перейти    
Преобразовать список в таблицу
 
#
26.04.2024 03:48:54
Преобразовал в PQ, загрузил в модель данных, построил сводную
Шлюхогон42
 
Перейти    
РР. Поиск максимального условия и суммирование значений
 
#
26.04.2024 00:22:45
Цитата
surkenny написал:
Замените на просто'Таблица1'[Значение]
Спасибо за подсказку! Я ещё не такой опытный юзер DAX, чтобы замечать такие тонкости :)

Цитата
surkenny написал:
что будет в Вашем расчете в случае двух полностью одинаковых строк в данных (являющихся последними по индексу)?  Подумайте.
Вы намекаете на то, что сумма задвоится? Но ведь индекс будет разным, значит не должно.
Вот я добавил ещё 2 значения в проект АЛЬФА и последнее (с индексом 6) тоже 360 - как и у ЗЕТА
скрин

и всё корректно посчиталось.
Шлюхогон42
 
Перейти    
РР. Поиск максимального условия и суммирование значений
 
#
25.04.2024 23:23:46
Мой вариант.
1. Доп. столбец "Последнее значение"
Код
= 
VAR cond =  CALCULATE(
                   MAX('Таблица1'[Индекс (№записи)]);
                   ALLEXCEPT('Таблица1'; 'Таблица1'[Номер проекта]))
RETURN 
IF('Таблица1'[Индекс (№записи)] = cond; CALCULATE(SUM('Таблица1'[Значение])); BLANK())


2. Мера =

Код
=SUM('Таблица1'[Последнее значение])


Норм?
Изменено: Дмитрий Никитин - 25.04.2024 23:24:18
Шлюхогон42
 
Перейти    
Вычисление суммы по динамическому списку
 
#
24.04.2024 16:47:01
Формула:
Код
=ЕСЛИОШИБКА(СУММЕСЛИМН(data!D:D;data!A:A;table!A3;data!B:B;table!B3;data!C:C;table!C3)/СУММЕСЛИМН(data!E:E;data!A:A;table!A3;data!B:B;table!B3;data!C:C;table!C3);"не найдено")
Шлюхогон42
 
Перейти    
Поиск по части строки. Из строки "Фамилия Имя Отчество" найти в столбце "Фамилия", списки в Excel
 
#
24.04.2024 16:31:22
С помощью условного форматирования.

Формула для столбца с ФИО:
Код
=СЧЁТЕСЛИ($B:$B;ЛЕВСИМВ($A1;НАЙТИ(" ";$A1)-1))


Формула для столбца с Фамилией:
Код
=И(СЧЁТЕСЛИ(A:A;"*"&$B1&"*");НЕ(ЕПУСТО($B1)))


скрин

На будущее - прикладывайте файл с примером!
Шлюхогон42
 
Перейти    
Сравнение двух списков
 
#
23.04.2024 19:58:46
Это то, что нужно?
Ещё можно воспользоваться инструментом расширенного фильтра
Шлюхогон42
 
Перейти    
Сумма в зависимости от выбранного условия в слайсере
 
#
23.04.2024 19:26:54
Потому что у вас исходная таблица неправильно организована.

Она не плоская, по ней нельзя строить сводную таблицу. Плоская таблица - это таблица, где по строкам идут параметры, по столбцам - значения (метрики). А у вас в столбцах - месяцы. Месяц - это параметр, а не метрика. См. вкладку "Плоская таблица"

Нормализовать исходную таблицу в плоскую можно с помощью Power Query. Ну или вручную, если данных совсем мало
Шлюхогон42
 
Перейти    
удалить дубликаты по столбцу с сохранением данных в строках
 
#
23.04.2024 16:21:40
Цитата
Остап Бендер написал:
Есть варианты с помощью формул?    

Да, на самом деле можно и без PQ.

Да и формул никаких не надо.

Делаете так:

1. Сортируете по двум уровням сортировки:
    1) подвид от А до Я;
    2) цена по убыванию
именно в таком порядке!
скрин1


2. Затем удаляете дубли, но оставляете галочку только у "подвид"
скрин2

А PQ удобен тем, что делаешь один раз, затем просто обновляешь, и всё само пересчитывает без лишних действий.
Изменено: Дмитрий Никитин - 23.04.2024 16:26:11
Шлюхогон42
 
Перейти    
Страницы: 1 2 3 4 След.
Наверх