Страницы: 1
RSS
Обойти ошибку в вычисляемом поле сводной при делении на ноль
 
Всем доброго, товарищи!
В сводной таблице есть вычисляемое поле, в котором присутствует деление.
т.к. в данных есть "0" одна строка сводной выдает #ДЕЛ/0.
Как можно выйти из ситуации?
 
Ну так вставьте туда формулу IFERROR или ЕСЛИОШИБКА.
 
_
 
Vladimir Chebykin, Спасибо)

Цитата
skais675 написал: Ну так вставьте туда формулу IFERROR или ЕСЛИОШИБКА.
Да, спасибо)
Не думал что Вычисляемое поле работает с этими операторами
 
Цитата
Araik A написал:
Не думал что Вычисляемое поле работает с этими операторами
И не будет. Если мне не изменяет память, то вычисляемое поле работает только с самыми простыми формулами типа: СУММ, МИН...
Если я ошибаюсь - поправьте меня
Изменено: Vladimir Chebykin - 02.07.2020 10:06:25
 
Нормально сработало)
Изменено: Araik A - 02.07.2020 12:07:40 (Некорректная вставка скрина)
 
Цитата
Vladimir Chebykin написал:
типа: СУММ, МИН
Вот как раз с ними - нет. Вычисляемые поля работают только с так называемыми линейными функциями. Т.е. только с теми, которые не обрабатывают диапазоны значений, а требуют в аргументах одну ячейку или константу и ни в коем случае НЕ строго ДИАПАЗОН(типа СУММЕСЛИ).
Поэтому функции ЕСЛИ, И, ИЛИ, НЕ, ЕНД и т.д. работают в вычисляемых полях прекрасно.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков, спасибо за науку!
 
Правила составления формул для вычисляемых полей сводных таблиц здесь.
Изменено: sokol92 - 02.07.2020 16:19:13
Владимир
 
sokol92, я вот как-то прочитал эту штуку про вычисляемые поля и понял Power Pivot наше все. А этот костыль, ну его нафиг.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
А этот костыль, ну его нафиг
как ни печально, но не все могут себе позволить PowerPivot даже в больших компаниях. Не у всех 2016 офис(и выше), а ставить доп.ПО как правильно низя...

P.S. По ссылке далеко не полный перечень функций, которые не будут правильно работать внутри вычисляемых полей и объектов :)
Изменено: Дмитрий(The_Prist) Щербаков - 02.07.2020 18:08:19
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий(The_Prist) Щербаков, ИМХО возможности 2016 ПРО+ при правильном использовании отбиваются в первую неделю использования, дальше идут сплошные бонусы. Если мы про зарабатывание денег.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
при правильном использовании отбиваются в первую неделю использования
да-да, я это знаю. Но как показывает практика далеко не везде это понимают в полной мере. Притом я не про маленькие конторки говорю - у меня тренинги и в весьма крупных компаниях и как ни печально, даже в них понимание такое не приходит сразу(а то и вообще не приходит).  
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
PooHkrd написал:
Power Pivot наше все
А управлять им из среды Excel VBA уже можно?
Владимир
 
Владимир, пивотом пока вроде как нельзя. Квери только чуть-чуть поддается в последних версиях.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Дмитрий, спасибо! Что ж, подождем еще... :)  
Владимир
 
Владимир, а чем именно вы там хотите управлять, если не секрет? Если нужно загрузить данные, то можно использовать объектную модель управления запросами PQ. Если нужно выгрузить на лист, то можно создавать таблицу и запросом SQL вытаскивать нужные выборки. Или создавать сводную и управлять выборками через её фильтры. Ваш опыт, конечно, с моим не сравним, но моя фантазия ограничивается вот таким.
З.Ы. Хотя, единственно чего очень хотелось бы, но нет из коробки, это итеративное обновление источников, чтобы можно было дописывать данные в таблицы модели. Это очень сильно сэкономило бы время на ожидание.
Вот горшок пустой, он предмет простой...
 
Мы используем Excel для отображения данных корпоративных систем. Обычные сводные таблицы не сложно генерировать "на лету" с помощью Excel VBA, а вот на расширенные возможности Power Pivot пока остается только облизываться. :)  
Владимир
 
sokol92, ага, вы хотите управлять связями и создавать меры? Но для этого вроде имеются внешние элементы управления без залезания в модель данных. Получается что для управления этими элементами нет инструментов в ВБА? Блин, не знал. Странно это.
Вот горшок пустой, он предмет простой...
 
А я и сейчас не знаю, вот и спросил. Возможности управления PQ из VBA, как пишет Дмитрий, тоже крайне ограничены.
В идеале хотелось бы, чтобы в Excel не было действий, которые можно было бы сделать интерактивно и нельзя с помощью VBA.
Изменено: sokol92 - 02.07.2020 19:17:37
Владимир
 
sokol92, ну макрорекодер это ж сила. Все можно подсмотреть. А вообще мы тут увлеклись, я думаю. Если действительно есть интерес, то заходите к нам на огонёк. Пообщаемся на тему что можно сделать внешними инструментами, в что только внутренними в модели данных и хватит ли вам этого функционала.
Вот горшок пустой, он предмет простой...
 
Здравствуйте, Алексей! В подтверждение слов Дмитрия прочитал, что
Цитата
The  creation and deletion of the PowerPivot Model (PPM) is triggered by  user exposed actions and cannot be created directly by the developer.
Developer'ы терпеливые, подождем еще лет 10. :)

А все-таки интересно, в чем состоит непреодолимая сложность занесения DAX-формулы (или формулы на языке M для PQ) с помощью макроса по сравнению с занесением этой же формулы через диалоговое окно?
Изменено: sokol92 - 06.07.2020 20:31:04
Владимир
 
Доброе время суток.
Цитата
sokol92 написал:
cannot be created
Ну, тут как бы не всё так грустно, по крайней мере в Excel 365, особенно если про Power Query. С удалением, да в объектной модели Power Pivot, пока швах. С переименованием объектов, забавно Dummy1 :)   Так и не наковырял, как вычисляемый столбец в таблицу подженить :(
Код
Public Sub JustTryIt()
    Dim sPQL As String, wbQuery As WorkbookQuery
    Dim wbConn As WorkbookConnection, pTable As ModelTable
    Dim pMeasuure As ModelMeasure
    sPQL = "let" & vbLf & "    items = {1..100},"
    sPQL = sPQL & vbLf & "    repeat = List.Transform(items, each List.Numbers(1000, 100, 100)),"
    sPQL = sPQL & vbLf & "    members = List.Combine(repeat),"
    sPQL = sPQL & vbLf & "    toTable = Table.FromColumns({members}, type table [Column1 = Int64.Type]),"
    sPQL = sPQL & vbLf & "    add = Table.AddColumn(toTable, ""Column2"", each try 1000 * Number.Random() otherwise null, Number.Type)"
    sPQL = sPQL & vbLf & "in" & vbLf & "    add"
    'create power query query
    Set wbQuery = ThisWorkbook.Queries.Add("TPQLData", sPQL, "This is auto geterated query")
    'change created query
    wbQuery.Formula = "// this is this query description in its code" & vbLf & wbQuery.Formula
    ThisWorkbook.Model.Initialize
    Set wbConn = ThisWorkbook.Connections.Add2("TPQLDataPP", "Add that query to model", "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=TPQLData;Extended Properties=""""", _
    "Select * From [TPQLData]", xlCmdSql, True, False)
    Set pTable = ThisWorkbook.Model.ModelTables(1)
    'excelent change of name
    pTable.Dummy1 wbQuery.Name
    Set pMeasuure = ThisWorkbook.Model.ModelMeasures.Add("Just Sum", pTable, "SUM('" & wbQuery.Name & "'[Column2])", ThisWorkbook.Model.ModelFormatDecimalNumber(True, 2), "created by vba measure")
End Sub
Посмотрю завтра, что из этого в Excel 2016 действует.
Updated
Посмотрел. Код полностью отработал.
P. S. Глючит, если открыто окно Power Pivot.
Изменено: Андрей VG - 07.07.2020 09:25:20
 
Андрей, как всегда, огромное спасибо!
Владимир
 
Цитата
Андрей VG написал:
Так и не наковырял, как вычисляемый столбец в таблицу подженить
Посчитать в PQ и подженить!  :D
А на DAX - никак. Не видел я внешних относительно модели кнопок, чтобы формулу столбца внести. В модель затащить таблицу запросом извне - можно. Настроить связи между таблицами - можно. Нужно сделать календарь? Запросом PQ - легко. Пошаманить с подключениями и SQL-запросами к ним, тоже можно.
Но, вот сделать сортировку по другому столбцу, вычисляемый столбец формулами DAX, создать KPI и управлять им. Этого нельзя.
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
Не видел я внешних относительно модели кнопок, чтобы формулу столбца внести
Эк вас, Алексей, на кнопочках то приклинило :)  Есть доказательство того, что есть только однозначная связь между существованием кнопки в интерфейсе и чего-нибудь в объектной модели, только и позволяющее это использовать в VBA?
Предполагаю, что MDX тут путь лежит, но идти туда не охота.
Изменено: Андрей VG - 07.07.2020 19:27:36
 
Андрей VG, нет конечно, но очень велика вероятность того, что если кнопка вне управления модели данных имеется, то и при помощи ВБА этим можно управлять. А вот про обратное как раз много где упоминается, что туда ход закрыт. Вы ж знаете, что в ВБА я нуль. Так что обхожусь только общими предположениями на основе логики, которые, кстати, вами же и подтверждаются. ;)
Вот горшок пустой, он предмет простой...
Страницы: 1
Наверх