Выбрать дату в календареВыбрать дату в календаре

Страницы: 1
VBA. Ошибка при присваивании массиву значений из "умной" таблицы.
 
Добрый день, уважаемые форумчане. У меня макрос работает корректно, а у коллеги возникает ошибка в строке присвоения массиву значений из умной таблицы.
Код
Sub maxValue()
    valuesLst = ThisWorkbook.Sheets("values").Range("values[Значения]") 'Ошибка в этой строке
    ThisWorkbook.Sheets("values").Cells(1, 3).Value = Application.WorksheetFunction.Max(valuesLst)
End Sub

Код
Sub minValue()
    Dim valuesLst() As Variant
    valuesLst() = ThisWorkbook.Sheets("values").Range("values[Значения]").Value 'Ошибка в этой строке
    ThisWorkbook.Sheets("values").Cells(2, 3).Value = Application.WorksheetFunction.Min(valuesLst())
End Sub


В основном макросе немного другая ошибка:
Скрытый текст

Изменено: surkenny - 29.03.2022 09:35:28
VBA. Получить массив значений поля из модели данных PP.
 
Уважаемые форумчане, добрый день!
Возникла необходимость сохранения отображаемых в сводной таблице данных в отдельный файл для каждого значения одного из фильтров.
Сложность в том, что это поле не выводится в саму сводную таблицу, поэтому получить массив всех значений с помощью метода .PivotItems нельзя.
Сейчас массив значений просто берется из другой таблицы: citiesLst = Range("cities[Город]").
Можно ли как-то этот массив получить именно из данных?
Для примера "сводную" копирую на отдельный лист:
Код
Sub test()
    Application.ScreenUpdating = False
    citiesLst = Range("cities[Город]")
    Set pt = Worksheets("pt").PivotTables("pivotT")
    For Each x In citiesLst
        pt.PivotFields("[data].[Город].[Город]").CurrentPageName = "[data].[Город].&[" & x & "]"
        pt.TableRange1.Copy
        Set nwSheet = Worksheets.Add
        nwSheet.Name = x
        nwSheet.Paste
    Next x
    Application.ScreenUpdating = True
End Sub
Изменено: surkenny - 24.03.2022 13:22:56
Поиск последней актуальной цены на дату продажи Power Query
 
Добрый день, уважаемые форумчане!

Необходимо в данные продаж подтянуть последнюю актуальную на дату продажи цену.
В случае, если нет данных о цене на даты до продажи, вывести самую раннюю установленную цену (независимо от даты этой цены). Если данных о цене нет вообще - null.

На маленьких объемах данных работает следующий код:
Код
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип",{"Товар"},#"Цены",{"Товар"},"Цена",JoinKind.LeftOuter),
    #"Замененное значение" = 
        Table.ReplaceValue(
            #"Объединенные запросы",
            each [#"Цена"],
            each if Table.IsEmpty( [#"Цена"] ) then null else
                let
                    OrderDate = [Дата],
                    MinPriceDate = List.Min([#"Цена"][Дата]),
                    FilterDate = List.Max({OrderDate,MinPriceDate}), 
                    Filter = Table.LastN(Table.SelectRows([#"Цена"], each [Дата] <= FilterDate ), 1)[Цена]{0}
                in Filter,
            Replacer.ReplaceValue,
            {"Цена"}
        )

Но на больших объемах безумно медленно.

Может, кто-то подскажет более быстрый код.
Пример.xlsx (19.37 КБ)  
Ошибка при создании связи один ко многим в модели данных Power Pivot
 
Добрый день, уважаемые форумчане!

Имеются несколько несколько txt файлов, из которых PQ загружает данные в модель.
При создании связи между таблицами возникает ошибка. При это связь между дубликатами (='Номенклатура'[Номенклатура.Код]) тех же столбов создается.

В чем может быть ошибка? Хотелось бы избежать копирования столбцов.
Отображение разных меры для разных дат в сводной таблице
 
Добрый день, уважаемые форумчане!

В модели данных есть ретроспективные данные (план-факт за прошлые периоды) и данные текущего и будущего периодов (план и расчетные данные).
Необходимо в одной сводной таблице вывести данные продаж только для прошлых периодов и расчетные данные только для будущих.

Определение набора делается вручную.
Код
{([Календарь].[Дата].&[2020-12-01T00:00:00],[Measures].[План_пф]),([Календарь].[Дата].&[2020-12-01T00:00:00],[Measures].[Факт_пф]),
([Календарь].[Дата].&[2021-01-01T00:00:00],[Measures].[План_пф]),([Календарь].[Дата].&[2021-01-01T00:00:00],[Measures].[Факт_пф]),
([Календарь].[Дата].&[2021-02-01T00:00:00],[Measures].[План_пф]),([Календарь].[Дата].&[2021-02-01T00:00:00],[Measures].[Факт_пф]),
([Календарь].[Дата].&[2021-03-01T00:00:00],[Measures].[План_п]),
([Календарь].[Дата].&[2021-04-01T00:00:00],[Measures].[План_п]),
([Календарь].[Дата].&[2021-05-01T00:00:00],[Measures].[План_п])}

Как сделать автоматическое определение набора? Для прошлых месяцев выводить в значения мер План_пф и Факт_пф, а для текущего и будущих значение меры План_п и других мер.

К сожалению, книга с моделью данных больше 100Кб
В примере для текущей и будущих дат только план. Сводная выглядит так:
Изменено: surkenny - 26.03.2021 14:43:19
Распределение приходов товаров по клиентам, Power Query
 
Доброго времени суток, уважаемые форумчане!

Прошу помощи в задаче распределения резервов.

При приходе товара, он распределяется в резерв текущего месяца в приоритет клиентов 1, затем в приоритет клиентов 2 и тд. Остаток после распределения текущего месяца распределяется в следующий по такой же логике.
Если товара недостаточно, чтобы обеспечить потребность всех клиентов одного приоритета, он распределяется среди них пропорционально оставшейся на данный момент потребности.

В файле примере помесячное решение.


Примерная суть такая:
1. Для каждого месяца определяется потребность и приходы в этом месяце.
2. Вычисляется количество товара, который попадет в резерв соответствующего месяца: MIN(приходы месяца + остаток с предыдущих месяцев; потребность месяца).
3. Определяется процент обеспечения одноприоритетных клиентов внутри месяца.
4. Распределяется приход.

Результат в файле.

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


Возможно, я совсем что-то неправильно делаю:) Буду благодарен, если хотя бы подскажете направление:)

В реальных данных ~65000 строк потребность, ~ 1000-2000 строк приход.
Решение нужно в PQ или PP (PP - если не будет сильно замедлять применение срезов, фильтров и тп.).
Изменено: surkenny - 19.03.2021 09:07:20
Ошибка при создании объекта V83.COMConnector
 
Добрый день, уважаемые форумчане!

Подскажите, при попытке создать COM-соединение в макросе
Код
Sub ff()
Dim cntr As Object
Dim trade As Object
Set cntr = CreateObject("V83.COMConnector")
Set trade = cntr.Connect("Srvr=""s***"";Ref=""***"";Usr=""***"";Pwd="""";")

a = trade.GETPRICE(41400)

End Sub
при создании объекта ошибка Run-Time Error '429': ActiveX Component Can't Create Object.
Остановка макроса на изменение на листе во время выполнения другого макроса
 
Добрый день, уважаемы форумчане.
Подскажите решение проблемы:

Имеется макрос на изменение на странице:
Код
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target <> "" Then
    If Not Intersect(Target, Range("C:C")) Is Nothing Then

        код
    
    End If
End If
End Sub
Другой макрос обновляет информацию записей в нужном диапазоне дат.
Так как данных у каждой записи и самих записей очень много, эти циклы очень замедляют работу.

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

Интересует помощь в таком вопросе:

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

При этом возможно добавление строк(-и) в середину таблицы.
Работа с таблицей на защищенном листе
 
Добрый день, уважаемые форумчане.
Постарался найти на форуме, но именно такой формулировки не нашел.
Есть таблица, форматированная как таблица на защищенном листе. Простите за масло масляное. В некоторых столбцах данные, которые может менять пользователь. В некоторых формулы. Столбцы с формулами должны быть защищены. Данные нет. При этом при добавлении данных внизу таблицы она должна автоматически расширяться. Возможно ли такое?  
Ошибка при заполнении столбца умной таблицы формулами посредством макроса
 
Добрый день. В Excel 2016 был создан макрос. Все работало отлично. Открыл книгу в Excel 2007 - вылетает ошибка 1004 application defined error.
В чем проблема?

Прилагаю код макроса:
Код
Private Sub Workbook_Open()
Application.Calculation = xlCalculationManual
Range("Заказ[код]").FormulaR1C1 = _
        "=IFERROR(впр2(INDIRECT(""База""&LEFT([@артикул])&""[наименование]""),[@[наименование в базе]],INDIRECT(""База""&LEFT([@артикул])&""[артикул]""),[@артикул],INDIRECT(""База""&LEFT([@артикул])&""[код]"")),"""")"
Range("Заказ[наименование в базе]").FormulaR1C1 = "=IF([@[наименование клиента]]="""",RC[7],фпр([@[наименование клиента]],RC[7]:RC[16]))"
Application.Calculation = xlCalculationAutomatic
End Sub
Преобразование неизвестного формата в текст
 
Добрый день!
Ситуация такая: есть ячейки с данными в разном формате. Нужно получить их в текстовом. Проблема в том, что функция ТЕКСТ не "видит" первые 0. Задать какой-то формат нельзя, так как данные разной длинны. Есть одно решение: скопировать в текстовый редактор - скопировать в excel в ячейки с текстовым форматом. Хотелось бы получить функцию, которая берет значение ячейки, копирует в word (или, если можно, в блокнот, так как быстрее будет работать), копирует из word и вставляет в ячейку с формулой.
Так как нет знаний VBA, не могу правильно прописать формулу:
Код
Function ЗвТ(val)
    Set oWord = CreateObject("Word.Application")
    ` oWord.Visible = True
    oWord.Documents.Add    
    Range(val).Copy
    oWord.Selection.Paste
    oWord.Range.Copy
    Range.Paste
    oWord.Quit SaveChanges:=wdDoNotSaveChanges
End Function
Поиск позиции в базе
 
Доброго времени суток!

Постараюсь кратко описать задачу. Имеется список всех товаров, у каждого товара уникальный код. Артикулы могут быть одинаковыми для совершенно разных позиций (разные поставщики). Клиент предоставляет список артикулов, наименований и количества товара. При этом названия у клиента и в базе могут несколько отличаться друг от друга (сокращение, порядок слов, вставлен артикул перед названием, отличаются некоторые слова; при этом, вручную сравнивая названия, можно подобрать нужное). Необходимо получить таблицу со столбцами уникального кода и количества товара. Так же базе имеются товары с одинаковым названием, но разными кодом и артикулом.

Расскажу свои мысли:
1.       Найти все товары, соответствующие артикулу клиента. Разделить название товара клиента на отдельные слова, аналогично поступить со всеми подобранными товарами, посчитать количество совпадений отдельных слов. В итоге выбрать максимально похожий по названию с таким артикулом и возвратить его код.
2.       Вероятно, первый вариант может работать не всегда корректно (большая разница у названий, подберется не тот товар). Поэтому желательно «прикрутить» возможность ручного выбора, к примеру, из списка (на листе 2 примера, ячейки со списком подсвечены зеленым). То есть excel выбирает код и наименование сам, но его можно изменить списком, в котором названия товаров только с данным артикулом. При этом подтянется код товара по наименованию (оно уже будет совпадать с базой, так как оттуда и берется) и артикулу. Вероятно, пригодится функция СУММЕСЛИМН().

Возможно, есть более красивые варианты. В любом случае, буду благодарен за помощь.
Изменено: surkenny - 23.11.2016 00:54:59
Таблица на защищенном листе, Необходима возможность добавлять данные в таблицу на защищенном листе
 
Добрый вечер! В примере есть таблица. Столбцы A и B заполняются пользователем. Столбец A*B защищен. При защите листа (чтобы не удалить случайно формулы), я убираю возможность добавлять строки в таблицу. Можно ли это как-то решить?
Вывести результат после нажатия кнопки
 
Добрый день!
Так как почти ничего не знаю про синтаксис макросов, прошу помощи.
Необходимо создать программу для розыгрыша подарков (на новый год семье хочу не просто раздать их, а сделать подобие лотереи, на самом деле подарки уже распределены). На Листе2 таблица соответствия кода номеру подарка. В ячейку F1 Листа1 вводится код. После нажатия кнопки через некоторое время в ячейке F7 Листа1 должен появиться номер соответствующего подарка. Если будет какая-либо анимация, что программа "думает" и определяет подарок - вообще супер.

Заранее спасибо!  
Ошибка при поиске значений выше среднего
 
День добрый!
Не могу понять, откуда возникает ошибка в столбце I. Безусловно, с помощью еслиошибка можно решить, как в столбце J, но все же в чем проблема?
Сортировка таблицы
 
Добрый день!
Имеется таблица с названием и значениями. Необходимо получить таблицу с названиями, значения которых выше среднего.

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

В идеале хотелось бы узнать более "человеческий" способ. заранее спасибо.
Таблицу с названиями и их значениями сортировать по алфавиту
 
Вводится таблица таблица с названиями и их значениями. Как получить такую же таблицу с сортировкой по алфавиту?
P.S. Сортироваться должно автоматически без участия пользователя.
Изменено: surkenny - 16.06.2014 02:07:52
Таблица суммированных баллов за выполнения разных задач
 
Необходима помощь в решении задачи. Существуют файлы с датой и данными кто и какую задачу выполнял (в примере 3 первые вкладки). Существует файл с баллами за выполнение каждой задачи (в примере 4 вкладка). Есть отметка о выполнении задачи (если не выполнил - баллы не начислять). Если задачу выполняли n человек, то баллы делятся поровну среди них (я так понимаю, можно реализовать с помощью ф-ии СЧЕСЛИ (COUNTIF), подсчитав количество непустых ячеек с фамилиями и разделив на это число). Существует файл с вкладками в количестве различных людей, выполнявших задачи. Если человек в конкретную дату выполнял задачи а, необходимо посчитать сумму баллов за их выполнение в соответствующее поле. Аналогично с б и в. То есть необходима проверка имени, даты, вида задачи и ее выполнения.
Оба примера одинаковы, разница только в формате.

Заранее спасибо!
Страницы: 1
Наверх