Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Нужно списком сделать привязку месяца к Договору, если продаж в месяце больше 0
 
#1
24.01.2019 15:21:11
Большая просьба помочь. :)

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

Буду очень благодарна любой идее! :)

Спасибо заранее
 
 
 
#2
24.01.2019 15:32:24
Наталья Лукина, а можно логику подробнее, т.е. если по договору допустим 1 12 месяцев продажи были то договор 1 будет в строку 12 раз ?
Изменено: Mershik - 24.01.2019 15:33:15
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
24.01.2019 17:50:08

Извините за сумбур. В таблице в ячейках указан месяц. Т.е если в Договоре 1 - стоит 3 под мартом, то это значит, что Договор был активным (были продажи) только в марте. :)
 
 
 
#4
24.01.2019 17:53:52
Цитата
Mershik написал:
если по договору допустим 1 12 месяцев продажи были то договор 1 будет в строку 12 раз
еще раз вопрос на всякий случай
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#5
24.01.2019 18:38:36
Да, верно, если по Договору 1 были активны все 12 месяев, то Договор должен быть продублирован 12 раз и в нем напротив каждой строки будет - месяц продажи: 1,2,3 и т.д. В нашем случае по файлу Договор 1 должен быть в 1 строку. :) Спасибо за желание помочь. :)
 
 
 
#6
24.01.2019 18:51:45
Наталья Лукина, если вас правильно понял..
Код
let
    Src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes
            (
             Src, 
             List.Transform(Table.ColumnNames(Src), each {_, if _ = "Договора" then type text else Int64.Type})
            ),
    FilteredCols = Table.RemoveColumns(Type,{"Итого"}),
    Unpivoted = Table.UnpivotOtherColumns(FilteredCols, {"Договора"}, "Attribute", "Value"),
    FilteredRows = Table.SelectRows(Unpivoted, each [Value] > 0),
    Pivoted = Table.Pivot(FilteredRows, List.Distinct(FilteredRows[Attribute]), "Attribute", "Value", List.Sum)
in
    Pivoted
Изменено: Alexey_Spb - 24.01.2019 18:53:56
 
 
 
#7
24.01.2019 19:01:25
Спасибо большое.  Не так. :) Как нужно - пример я привела в табличке.
В итоге мне нужно всего два столбца. В 1 - Будет номера договоров, а во втором - текст, который есть в таблице.
Т.е если у одного договора присутствуют данные из 2 столбцов, то этот номер договора будет фигурировать в 2 строчках.

Извините, если не могу донести понятно суть вопроса.
Должно   получится:
ДоговораМесяц
Договор 13
Договор 21
Договор 22
Договор 23
Договор 31
Договор 32
Договор 33
и т.д
 
 
 
#8
24.01.2019 19:45:17
Зайдите в редактор запросов и сотрите операцию Pivoted.  
Изменено: Alexey_Spb - 24.01.2019 19:47:09
 
 
 
#9
24.01.2019 20:26:30
Не поняла о чем речь?.. Скажите, пожалуйста, может кто то помочь? :( Массив с информацией  огромный, "руками " выискивать долго...
 
 
 
#10
25.01.2019 10:10:50
Там надо было пару кликов мышью сделать, что именно, я писал ранее.
 
 
 
#11
25.01.2019 10:38:37
Алексей, большое спасибо! :) А это макросы, да? Я, к сожалению, в этом не сильна... А просто формулами это сделать реально?
 
 
 
#12
25.01.2019 10:46:15
Это не макросы (если иметь в виду под этим словом VBA), а Power Query (с другой стороны, его тоже можно обозвать макросом).

Условно можно считать PQ неким аналогом формул, но более высокого уровня. PQ, а точнее, язык М, на котором работает PQ является функциональным языком (то есть итоговое значение последовательно вычисляется), так же как и язык формул. Это, имхо, одна из и долгожданных лучших вещей, сделанных для Excel - возможности его в части обработки данных поражают.

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

Формулами можно сделать многое, думаю, и это тоже, только вопрос какой ценой..
Изменено: Alexey_Spb - 25.01.2019 10:48:23
 
 
 
#13
25.01.2019 11:10:30
Алексей, спасибо. А в поисковике просто набирать визуальный редактор PQ?
 
 
 
#14
25.01.2019 11:23:00
Все делается в три простых шага:

1. Создаете обьект Таблица на основе ваших данных



2. Создаете запрос PQ на основе этой таблицы.




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





После того как вы нажмете Close & Load, у вас на новой странице появится таблица - результат работы запроса. Как только вы нажмете Data -> Refresh all, запрос последовательно выполнит все указанные вами шаги на новых данных в таблице и обновит связанную таблицу.

Добавление / удаленик колонок, фильтрация строк и т.д. - все что вы делали в редакторе, это будет выполнено с вашими данными. Возможности этой штуки колоссальны (конечно, большая часть айсберга скрыта в кнопке Advanced Editor, но для 99% задач на этом форуме достаточно визуальногно редактора)
Изменено: Alexey_Spb - 25.01.2019 11:29:30
 
 
 
#15
25.01.2019 11:29:21
Е мое... :))) Попробую. :))
 
 
 
#16
25.01.2019 11:35:04
Только лучше удалите автоматически созданный шаг Changed Type - он вас привязывает ко ВСЕМ заголовкам вашей исходной таблицы, даже к тем, которые вам не нужны в запросе. Это плохо тем что если один из ненужных заголовков в будущем поменяется, в запросе будет ошибка так как такой заголовок он не найдет. Если вам нужно преобразовать тип данных в каких-то конкретных столбцах, сделайте это правым щелйком мыши по заголовку столбца.

Конечно, можно написать запрос, нечувствительный к именам заголовков, но это уже более продвинутый уровень.
 
 
 
#17
25.01.2019 11:39:40
Мне бы в этом разобраться.... :)) Спасибо!  
 
 
 
#18
25.01.2019 12:15:33
Ну спрашивайте что будет непонятно, тут много людей, разбирающихся в PQ.

Конкретно ваш запрос (вы можете щелкнуть на каждый шаг и смотреть как последовательно меняются данные:

Src - загрузка данных из исходной таблицы
Type - приведение типов данных столбцов. У вас во всех столбцах, кроме "договора" тип - числа, а в договоре - текст.  Это нужно для того чтобы корректно обрабатывать числа. Эту операцию я сделал не в визуальном редакторе (как раз чтобы не привязываться к именам столбцов), но можно было сделать в визуальном, определив автоматически типы данных в каждом столбце.

FilteredCols - я удалил столбец Итоги так как он нафиг не нужен и будет только мешать.

Далее самая важная операция это Unpivoted (есть в графическом редакторе). Она делает обратную операцию по отношению к операции "сводная таблица", то есть из сводной таблицы делает исходные данные для этой сводной. Как раз то что вам и было нужно:

Данные:

Имя       Параметр А     Параметр Б
Имя_1   Кол-во_1.А      Кол-во_1.Б
Имя_2   Кол-во_2.А      Кол-во_2.Б

Результат:

Имя       Атрибут          Значение
Имя_1   Параметр А   Кол-во_1.А
Имя_1   Параметр Б   Кол-во_1.Б
Имя_2   Параметр А   Кол-во_2.А
Имя_2   Параметр Б   Кол-во_2.Б

Это по сути то, что вам надо было получить. Это всего одна операция на PQ, остальное все - причесывание данных и результата.

FilteredRows - удаление из таблицы всех строк, у которых колонка Значение = 0 или пустая.

SortedRows - сортировка по договору.

Вот и все.
Изменено: Alexey_Spb - 25.01.2019 12:16:07
 
 
 
Страницы: 1
Наверх