Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 След.
Сводная таблица с вычисляемыми полями: получить результат вычитания полученных данных из других полученных данных
 
Цитата
Настя_Nastya написал:
Или так
Впал в ступор на пару минут, пока не сообразил, что это отличие, просто вид операции переименован :D  
Построение отчётов в excel подобно access'овским
 
Может я не оч понял ТЗ, но можно же включить табличное представление сводной для отдельных полей.
Либо можно текст выводить как меру, если в массиве запись одна. Одну запись можно индексацией строк обеспечить.

Скрытый текст
А PBi вроде умеет таблицы рисовать, перемежая меры и группировки, но там не экспериментировал
Функция или запрос в PQ и скорость обновления: где лучше разместить больше шагов?
 
Товарищи, а у вас часто наблюдается такая картина?
В запросе функция которая таблицу преобразуется в 3 столбца "индекс", "атрибут" и "значение", функция применяется к двум файлам. Затем в атрибуте правятся заголовки, чтобы развернуть потом в одинаковый, ну и разворачивается. Короче даже не суть что делается, достаточно часто наблюдаю такую картину со 100% загрузкой проца и "высоким энергопотреблением" процессов excel & mashup..
Скрытый текст
Функция или запрос в PQ и скорость обновления: где лучше разместить больше шагов?
 
Большое спасибо всем. Познавательно.

У меня вообще файлы на сервере лежат и к ним обращение по сети идет. Я чего-то не думал, что обращений к одному и тому же файлу может быть >1. Вообще собираю 1,5 млн строк из ~100 файлов разного размера и все встает колом. Пару месяцев назад данных было чуть меньше и все работало. Уж не знаю, что изменилось, обновления ли офиса, критическая масса достигнута или политики безопасности стали мешать.
Кину на локальный ssd, мож попустит, а то устал смотреть в диспетчере задач на подвисшие процессы mashup и эксель с пометкой "оч высокое энергопотребление"
Функция или запрос в PQ и скорость обновления: где лучше разместить больше шагов?
 
Всем привет.
Если собирать N файлов запросом PQ в один массив данных, как лучше поступать:
- максимум шагов добавлять в функцию и применять "большую" функцию к каждому файлу в головном запросе или
- делать функцию с минимумом шагов и добавлять все необходимые шаги в головном запросе после применения "маленькой" функции?
Есть ли тут какой-то универсальный совет?

Спасибо
Строка с итогом в DAX
 
Цитата
Raritet написал:
На скрине ничего не отображается
что бы это значило
Изменение в ряде ячеек формул на разные по условию.
 
Юрий М, это правильно
Макрос для сравнения столбцов с одинаковыми заголовками, находящимися в разных документах
 
В макросах не разбираюсь, но из любопытства попытался решить с помощью powerquery:
каждый столбец отдельными запросами очистил от пустых значений
добавил индекс
затем соединил запросы в один
отменил свертывание других столбцов кроме индекса
затем свел обратно в столбцы без агрегирования и получил таблицу из подзадачи 1
фильтранул, чтоб заголовок встречался 1 раз
поднял строки в заголовки

Сработало. Но только благодаря одинаковому кол-ву значений для каждого поля - индексы совпали. Растянул на 5000 строк - отработало быстро. Я к тому, что может можно решать не только макросом.

Со справочником PQ тоже легко сравнит с помощью левых/правых соединений.
Изменение в ряде ячеек формул на разные по условию.
 
пока ждешь нормальное решение, лови моё :D
Код
=ЕСЛИ(СТОЛБЕЦ()>ПОИСКПОЗ(0;$A$4:$E$4;-1);МИН($A$4:$E$4)/(5-ПОИСКПОЗ(0;$A$4:$E$4;-1));"")
может я не так понял задачу :-\
Изменено: tabularasa - 9 Апр 2019 00:20:28
Строка с итогом в DAX
 
Я вообще ни разу не специалист и смог придумать только такое :D
Скрытый текст



Вообще может быть можно еще набор создать при помощи MDX, но у меня не вышло по причине, озвученной выше
Изменено: tabularasa - 8 Апр 2019 23:01:50
сумма произведений и критерии
 
Спасибо :)
Эх, боюсь товаров на порядок больше трех. Че ж делать то... Пойду дальше мучить суммпроизв:)
сумма произведений и критерии
 
я осёл, таблицу цен надо было изобразить иначе...
а так проще или сложнее сложить произведения?  :oops:  
сумма произведений и критерии
 
Спасибо, сейчас посмотрю

Кстати получилось то, что сам выше предположил с помощью второго критерия даты.
Код
=СУММПРОИЗВ(факты[@[товар1]:[товар3]]*
(цены[[#Заголовки];[товар1]:[товар3]]=факты[[#Заголовки];[товар1]:[товар3]])*
(цены[[товар1]:[товар3]])*
(цены[точка]=[@точка])*
(цены[действует с]<=[@дата])*
(цены[действует по]>=[@дата]))



А как производительность протестировать, не растягивая массив на 10000 строк и без секундомера? В 2019 экселе не предусмотрено ничего такого случаем? )
сумма произведений и критерии
 
=СУММПРОИЗВ(факты[@[товар1]:[товар3]]*(цены[[#Заголовки];[товар1]:[товар3]]=факты[[#Заголовки];[товар1]:[товар3]])*(цены[[товар1]:[товар3]])*(цены[точка]=[@точка])*(цены[действует с]=[@дата]))

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

Пока писал подумал, может добавить в таблице с ценами еще колонку, которая будет вычислять конечную дату, если для товара появилась новая цена, тогда можно будет добавить второй критерий по дате... ща попробую ))
сумма произведений и критерии
 
Привет.
Пытаюсь сделать файл, удобный для заполнения, но не выходит найти элегантного решения ни самой формы, ни формулы в частности.
Пока придумал так: в одной таблице вводится дата, торговая точка, кол-во товаров; в другой - цены. Цены меняются.
Хочу в левой табличке получить сумму. Это единственная колонка, где предполагается формула.
Делать столбец под каждый товар, чтоб отдельно цену считать, слишком жирно, т.к. товаров больше, чем в примере.

Возможно ли решение без vba, powerquery, массивов, то есть простой формулой, но так, чтоб файл не задыхался?

Спасибо
КУБЭЛЕМЕНТ, ссылающийся на значение др ячейки
 
Дайте шанс еще раз выразить благодарность
КУБЭЛЕМЕНТ, ссылающийся на значение др ячейки
 
БМВ, спасибо. Я наверное файл примера слишком упростил. Сцепка работает для первого уровня таблиц, действительно
На втором уровне иерархии в формуле появляются фигурные скобки, и этот "трюк" с амперсандом уже не выходит.
Вот файл.
Создание списка выборкой из двух листов по нескольким условиям
 
Анна Таскаева, там в редакторе powerquery есть закладки "преобразование", "добавление". Вот на закладке добавление есть кнопка "добавить условный столбец", которая открывает тот интерфейс, что я сфоткал.

Я понял, эту кнопку добавили в каком-то из обновлений excel. Если у вас он не обновляется, возможно у вас кнопки нет. Тогда два варианта - 1) обновлять или 2) писать строку запроса по созданию условного столбца вручную в редакторе, что уже требует знаний "кода".
Изменено: tabularasa - 2 Дек 2017 00:50:38
счётесли без учета ячеек скрытых фильтром
 
сводная
ввод квартальной цены по заданной продукции, arslantm80
 
Можно использовать ВПР, ИНДЕКС, СУММЕСЛИ, формулы массива и т.д.
Начните с ВПР в разделе сайта "Примеры".
Изъятие информации из одной таблицы в другую, Изъятие информации из одной таблицы в другую
 
Можно без макросов обойтись. Закладка "Данные" -"Из таблицы". Создаем два запроса powerquery и затем третьим объединяем их, делая внешнее соединение слева/справа (всё из одной таблицы, совпадающее из другой).
Синхронизировать срезы для сводных с разным источником
 
Oksanka, не знаю ничего про макросы, но если у двух таблиц есть общее поле, по которому вы хотите видеть срез, то необходимо добавить в модель данных три таблицы:
  • таблица 1 с данными,
  • таблица 2 с данными,
  • таблица 3 с уникальными значениями из общего поля (это у нас типа справочник)
Таблиц и справочников может быть любое кол-во, конечно.
Далее в модели данных необходимо создать связь между таблицами 1+3, 2+3 (достаточно перетянуть поля от одной к другой).
Построить сводную, в качестве среза выбрать значения из таблицы 3. Т.к. таблица 3 общая для первых двух, фильтр срезу будет применяться к обеим.

Все это делается чисто мышкой. Требуется включить в экселе стандартную надстройку powerpivot, по умолчанию она почему-то отключена.
Создание списка выборкой из двух листов по нескольким условиям
 
Анна Таскаева, если я правильно уловил суть, следует создать условный столбец, в котором сделать несколько условий:
если операция ВУ, то "Учитывать"
иначе если назначение ДЕПО, то "Учитывать"
иначе "Не учитывать"

следующим шагом фильтровать по "учитывать". Примерно так:
Создание списка выборкой из двух листов по нескольким условиям
 
Анна Таскаева, рекомендую посмотреть в сторону бесплатной надстройки power query (раздел Данные в на закладках excel). Можно данные собирать, фильтровать и еще много-много чего, причем в отличие от VBA для этого там есть пользовательский интерфейс в виде кнопок. Там это достаточно тривиальная задача - собрать данные из любого кол-ва источников, фильтрануть, обработать, вывести в единый список. Причем сами исходные данные могут вообще находиться в других файлах.
Массовое изменениe в строках
 
Ismailov, нет проблем, Все вышеперечисленное надо заключить в формулу ДВССЫЛ, чтоб выводился не адрес, а значение из адреса.
Объединение строк в таблице по совпадению параметров.
 
voodooas, я бы преобразовал в "умную" таблицу (CTRL+T) и построил сводную.
В сводной на закладке "конструктор" изменил бы макет отчета на "таблица" и отключил бы промежуточные итоги. Нужные колонки в нужном порядке кинул бы в строки, а суммы - в показатели.
Сводная сама сложила бы всё совпадающее в зависимости от группировки.
КУБЭЛЕМЕНТ, ссылающийся на значение др ячейки
 
Пример в архиве сойдет? А то не влезал :(

Дайте обратную связь, пожалуйста, понятна ли задача, а то я гадаю, это решения нет или я объяснять не умею?    
Изменено: tabularasa - 29 Ноя 2017 22:57:36
Массовое изменениe в строках
 
я пишу, с высоты своих неглубоких познаний :)
возможно следует  применить сперва Ф.ТЕКСТ, а затем ДВССЫЛ
Код
=ПСТР(Ф.ТЕКСТ(A2);1;ПОИСК("$";Ф.ТЕКСТ(A2);1))&ПРАВСИМВ(Ф.ТЕКСТ(A2);ДЛСТР(Ф.ТЕКСТ(A2))-ПОИСК("$";Ф.ТЕКСТ(A2);1))+D2

где D2 - необходимая прибавка, например 4
Массовое изменениe в строках
 
надо разделить строку на 2 части - текстовую и числовую.
с числовой выполнять действий нужные, а затем сцеплять обратно чеерез "СЦЕПИТЬ" или "&"

разделить же помогут формулы типа ПСТР , ПОИСК, ДЛСТР или разделение по столбцам по разделителю как инструмент excel
КУБЭЛЕМЕНТ, ссылающийся на значение др ячейки
 
Добрый день.
Если построить сводную, у которой источник модель данных в powerpivot, а затем сводную преобразовать в формулы, то все показатели будут ссылаться на ячейки с критериями, в которых будет вписана формула КУБЭЛЕМЕНТ(подключение;выражение_элемента).

Например, так выглядит элемент для даты из таблицы дат.
Код
=КУБЭЛЕМЕНТ("ThisWorkbookDataModel";{"[Measures].[шт]";"[календарь].[Date].&[2017-11-01T00:00:00]"})
Замена формата даты в PP никак не влияет на отображение элемента в формуле.

Преобразование в формулы хочу использовать для произвольной компоновки данных из PP. Вопрос в том, как сделать часть "2017-11-01" динамической - зависимой от значения, скажем, другой ячейки или фильтра таблицы.
Причем, если заменить в powerpivot-е формат даты на число, то формула выглядит так:
Код
=КУБЭЛЕМЕНТ("ThisWorkbookDataModel";{"[Measures].[шт]";"[календарь].[дата_числом].&[4.3043E4]"})
А если сцепить "день.мес.год" в текст, то так:
Код
=КУБЭЛЕМЕНТ("ThisWorkbookDataModel";{"[Measures].[шт]";"[календарь].[дата_сцеп].&[01.11.2017]"})
Но у меня в принципе не выходит подставить ссылку на другую ячейку листа excel.
Файл примера приложу через несколько минут.
Страницы: 1 2 След.
Наверх