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

Страницы: 1
Создание таблицы с распределением на основе двух таблиц с данными
 
Добрый день
просмотрел форум на подобные задачу, но точного ответа ненашел.

Есть 2 таблицы со списком продуктов и их ценой и списком менеджером и их оборотом (например). Нужно получить новую таблицу в которой будут распределены менеджеры по продуктам соглано суммы оборота менеджера (грубо говоря из сводной таблицы по менеджерам нужно получить исходную таблицу). Для наглядности добавил файл и его скриншот
P.S 100% совпадение суммы по менеджеру не обязательно, но хотелось бы чтобы итоговая сумма продуктов была максимально близка к сумме оборота по менеджеру

Моя логика решения задачи:
1. узнать количество строк (countrows)
2. разделить сумму менеджера на количество строк, чтобы узнать ~ сколько продуктов должно быть под ним (нужно будет округление применить)
3. посчитать долю для каждого менеджера от общей суммы оборота
4. нужен цикл который буде проверять сумму продуктов

заранее спасибо
Изменено: sanych09 - 18 янв 2020 16:27:11
Опыт и практика - великое дело! Век живи, Век учись!
Создание сводной таблицы с помощью Power Query и DAX формул
 
доброго времени суток. появилась необходимость создать сводную таблицу средствами Power Query (и DAX формулами если необходимо). в обычной сводной таблице получается, впринципе, получить нужный результат и с помощью обычных формул COUNTIFS тоже вышло. Цель таблицы - увидеть количество документов которое создаются в системе по месяцам, и количество тех же документов которые были оплачены до регистрации их в системе, в тот же месяц либо после регистрации. так же другие статусы этих документов. Результат, который бы хотелось получить на скрине. Тестовые данные добавил для Марта и Апреля 2019. Пытался сделать что-то похожее, но не получается... может быть нужно разбивать на несколько запросов....? И вообще, возможно что-то похожее сделать? Заранее спасибо!
Опыт и практика - великое дело! Век живи, Век учись!
Макрос по замене текста в файлах выбранной папки
 
добрый день. появилась необходимость сделать замену названий столбов в файлах (файлов очень много, находятся в одной папке). Данные, которые нужно заменить расположены в одном и том же листе с названием "ItemDetails" во всех файлах. попробовал записать макрос для нахождения и замены вкроде как работает.
Код
Sub replacetext()

    ActiveCell.Replace What:="GR Document number", Replacement:= _
        "Capitalization.GR Document number", LookAt:=xlPart, SearchOrder:=xlByRows _
        , MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="GR Document number", Replacement:= _
        "Capitalization.GR Document number", LookAt:=xlPart, SearchOrder:=xlByRows _
        , MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="Evaluation Code", Replacement:= _
        "Capitalization.Evaluation Code", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="Asset Class", Replacement:= _
        "Capitalization.Asset Class", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="Cost Center", Replacement:= _
        "Capitalization.Cost Center", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

подскажите, как дописать сюда код, который будет запрашивать папку с расположением файла, открывать файлы по очереди, изменять данные только на листе ItemDetails, сохранять и закрывать файлы в фоне. Откывать файлы и запускать макрос в ручном режиме даже страшно подумать :) зарене спасибо!
Опыт и практика - великое дело! Век живи, Век учись!
Power BI - как связать календарь с несколькими столбцами с датами, Необходимо построить отчет по месяцам к котором дожны отображаться фактическое количество выполненых этапов
 
Добрый день, форумчанам!
Надеюсь вопросы по Power BI можно публиковать.
Есть обычная балица с данными - простой трекер, в котом есть уникальные записи по контрактам (столбец ContratID), название отдела, подрядчика и сумма контракта. Данные по обработке каждого контракта обновляются в столбцах майлстоунах (MS1 Star Date, MS2 Finish Date, MS1 Status и т.д). Таблицу с календарем можно связать только с одним столбцом, например, MS1 Start Date в котором будут данные без дат. Построил обычную таблицу (без мер) в которую вывел количество обработанных контрактов по майлстоунам. В данном примере видно, сколько обработанных контрактов было в определенном месяце по MS1 Start Date и сколько из них имеет обновленный майлстоун MS2. На самом деле, например, в апреле, никаких контрактов по MS2 не было обработано. Подскажите, как правильно сделать вывод фактически выполненых MS в определенный период? Думал создать меры по каждому майлстоуну... но если у меня таких майлстоунов больше 10 будет ли это правильно? pbix файл доступен по ссылке https://dropmefiles.com/yMLoY
Опыт и практика - великое дело! Век живи, Век учись!
Консолидация данных из разных щитов в один, VBA цикл модифицировать код
 
Доброго всем дня.
Есть код. появилась необходимость его немного модернизировать (в VBA ничего не понимаю). Вкратце: код консолидирует данные из разных щитов в один согласно названиям колонок в главном щите.
Так как в некоторых листах нет колонок с заданным названием я бы хотел чтобы код проставлял следующий текст "N/R" вместо пробелов (сейчас код так работает)
сам код
Код
Sub SS_WP_UpDateData()
    
    Sheets("Weekly_Plan_Sites").Select
    Range("A2").Select
    
    Dim i As Long, j As Long, k As Long, n As Long, wData As Worksheet, _
        Process(1 To 5) As String, iProc As Long, Dict As Object
    Process(1) = "Sheet1"
    Process(2) = "Sheet2"
    Process(3) = "Sheet3"
    Process(4) = "Sheet4"
    Process(5) = "Sheet5"
    
                        
    Set wData = Sheets("Weekly_Plan_Sites")
    Set Dict = CreateObject("Scripting.Dictionary")
    With wData
        .UsedRange.Offset(1).Clear
        For j = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
            If Len(.Cells(1, j)) > 0 Then Dict.Add LCase$(.Cells(1, j)), j
        Next j
    End With
    i = 2
    For iProc = 1 To 5
        With Sheets(Process(iProc))
            n = .Cells(.Rows.Count, 1).End(xlUp).Row
            For j = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
                If Dict.exists(LCase$(.Cells(1, j))) Then
                    k = Dict(LCase$(.Cells(1, j)))
                    .Cells(2, j).Resize(n - 1).Copy wData.Cells(i, k).Resize(n - 1)
                End If
            Next j
        End With
        i = i + n - 1
    Next iProc
End Sub
Изменено: sanych09 - 26 апр 2019 14:04:05
Опыт и практика - великое дело! Век живи, Век учись!
PQ Merge queries (Слияние запросов) проблема, при слиянии запросов происходит ошибка... данные в первой таблице меняются местами
 
Добрый день!
Есть две таблицы 1 и 2 в которых содержится наименование объектов, название, суммы.
Нужно сравнить эти две таблицы между собой, например, чтобы увидеть изменение цены...В связи с тем, что для одного объекта могут быть повторяющиеся элементы, сделано следующее: сортировка столбца Item Name, сортировка столбца Total amount (об большей суммы к меньшей); группировка по Location, PO Number и внутри группы добавлены индексы (замечательные ребята на этом форуме подсказали решение). Далее создаю новый запрос, где делаю Merge таблиц Full outer : Location, PO Number, Item Name, Index)
После разворачивания у одинакового элемента в Таблице 1 меняются между собой индексы, те. максимальная сумма становится с индексом 2, а должна была остаться с 1
Прошу подсказать, что я сделан не так... и как исправить данную ошибку.
Опыт и практика - великое дело! Век живи, Век учись!
Расчет длительности (количества дней) процесса
 
Здравствуйте
Столкнулся с проблемой расчета "чистого" количества дней, которое было затрачено на выполнения определенного объекта.
Нужно посчитать количество дней без учета перерыва между этапами и также учитывать, что некоторые этапы выполнялись параллельно с другим(и)
Для наглядности, добавил столбцы  D - X.
Можно ли решить вопрос формулами или Power Query? Спасибо заранее за любую наводку
Опыт и практика - великое дело! Век живи, Век учись!
Power Query: вычитание двух произвольных столбцов, Найти разность двух столбцов. Имена столбцов меняются по критерию (ссылка на другую таблицу)
 
Доброго дня! продолжаю интересоваться Power Query. Задача найти разницу дней между 2 столбцами. Столбы для нахождения разности задаются в отдельной таблице.
Получилось отфильтровать Исходную таблицу по условия заданным в фильтре. Теперь не могу разобраться, как вычитать столбцы, чтобы данные бралисть с нужного столбца Duration.Days(ms2 - ms1)

Код
let
    Source = Excel.CurrentWorkbook(){[Name="dpr"]}[Content],
    ms1 = mslist[Column2]{0},
    ms2 = mslist[Column2]{1},
    ColumnstoKeep = List.Select(Table.ColumnNames(Source),each Text.Contains(_, "SITE ID") or Text.Contains(_, ms1) or Text.Contains(_,ms2)),
    KeepedColumns = Table.SelectColumns(Source, ColumnstoKeep),
    #"Inserted Date Subtraction" = Table.AddColumn(KeepedColumns, "Duration", each Duration.Days([MS 10.2 Finish] - [MS 10.1 Start]), Int64.Type)
in
    #"Inserted Date Subtraction"
Изменено: sanych09 - 27 апр 2018 12:36:09
Опыт и практика - великое дело! Век живи, Век учись!
Power Query: Последний выполненный этап, Последняя дата выполнения проекта в Power Query
 
Всем привет! Нужен совет знатоков.Задача найти последний выполненный этап (milestone) по объекту, его день и статус в PQ. В примере сделал с помощью добавления условного столбца. Пытался делать по другому Unpivot всех кроме Site ID, потом Group, List.Max...но в таком случае есть значение отличные от дат (MS Status) :(. Есть ли какой-то другой способ (do while, перебирать данные с отдельного списка этапов которые только важные или по всем столбцам сразу), потому как в таблице может быть до 50 таких столбцов и прописывать все 50 с помощью условного столбика как-то до неприличия некрасиво
Опыт и практика - великое дело! Век живи, Век учись!
Power Query - Сравнение двух таблиц содержащие дубликаты
 
Добрый день. Необходимо с помощью Power Query сравнить две таблицы в которых содержаться дубликаты. Идеально подходило сделать Merge - Full Outer. позже оказалось, что в таблицах есть повторяющиеся строки и в результате полученная таблица оказалась не тем что ожидалось. Например, в Table1 есть 2 строки одинаковых позиций, а в Table2 - такая же позиция только запись содержиться в одной строке. Результат Merge: 2-2, а нужно 2-1.
Спасибо заранее!
Опыт и практика - великое дело! Век живи, Век учись!
Сбор данных с листов по критерия, консолидация данных по названию столбцов
 
Доброго времени суток!
сделал сводную табличку лист Summary. в шапке заранее прописал критерии, имена столбцов с других таблиц которые бы я хотел видеть в одном листе. так как расположение нужных мне столбцов в листах (их у меня 17) разное, пользовался формулой ВПР(vlookup), ДВССЫЛ(indirect) и ПОИСКПОЗ(match)
вроде все получилось, но почему-то есть ошибки, выделил их красным. если без использования ДВССЫЛ и вручную указать для формулы ВПР что искать - то выводит нужное значение..
в связи с этим помогите решить несколько вопросов:
1. автоматическое копирование информации SITE ID со всех листов и вставкой друг за другом в вертикальном порядке в Лист Summary и добавлением имени листа, с которого SITE ID было скопировано.
в ручном режиме делается, но если каждый день будут какие-то изменения, то не хотелось бы эту процедуру повторять
2. почему по некоторым SITE ID выводиться #Н/Д?
3. возможно ли обойтись без формул и сделать это все с помощью макроса? Который бы делал сводный отчетик если в листе Summary в первой строке (шапке) прописать заранее имена нужных столбцов из которых нужно получить данные (количество столбиков в шапке может меняться). в макросах ничего не понимаю (((
файлик прилагаю
спасибо за ответы заранее
Изменено: sanych09 - 17 авг 2015 06:50:33
Опыт и практика - великое дело! Век живи, Век учись!
Страницы: 1
Наверх