如何生成常规Excel公式作为查询结果? [英] How can I produce a regular Excel formula as the result of a query?

问题描述

我有一个大型而复杂的Excel查询，可以按需运行.

但是，我正在实现一些实时数据验证功能(即不需要数据刷新)，并且我需要在查询结果的其中一列中使用常规的excel公式.

此公式将使用工作簿中的其他工作表执行实时数据比较，这有意独立于查询本身.

我可以添加没有值的自定义列吗?
我假设null或""值会覆盖任何先前存在的数据，所以我不能在运行查询后仅将excel公式输入到结果表中(该公式在刷新之间不会持续存在).

为了进行测试，我尝试添加一个值为"=5+2"的自定义列，只是为了查看其行为.

let
    Source = Excel.CurrentWorkbook(){[Name="tblInvoicesCategorized"]}[Content],

    /* ... perform numerous query actions ... */

    // Use "=5+2" as a test formula
    #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Stale Data", each "=5+2"),

    /* ... perform numerous query actions ... */

in
    #"Changed Type"

查询确实将公式输出为列中单元格的值，但是Excel不会自动执行公式.
我仍然必须手动将光标放置到单元格中，然后按Enter键才能执行公式.

手动输入公式，甚至手动执行宏，对于此工作簿都是不可取的.

我是否可以在此查询列中使用常规公式，并使其在数据刷新之间自动保持不变?

解决方案

一种解决方法是通过某些VBA代码在单元格更改上触发refreshall ...

这是我的情况，希望对您有帮助...

Private Sub Worksheet_Change(ByVal Target As Range)

  '
  '  Dim lTest As Long, cn As WorkbookConnection
  '  On Error Resume Next
  '  For Each cn In ThisWorkbook.Connections
  '      lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
  '  If Err.Number <> 0 Then
  '      Err.Clear
  '      Exit For
  '  End If
  '  If lTest > 0 Then cn.Refresh
  '  Next cn




    If Intersect(Target, Me.Range("datachange")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop

    'Application.Goto Reference:="Tum_Santiyelerin_Satinalinan_Malzemeleri"
    Range("Tum_Santiyelerin_Satinalinan_Malzemeleri").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre"
    'Range("Filtre").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Malzeme"
    Range("Filtre_Malzeme").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Proje"
    Range("Filtre_Proje").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Firma"
    Range("Filtre_Firma").ListObject.QueryTable.Refresh BackgroundQuery:=False



    Application.Goto Reference:="Tum_Santiyelerin_Satinalinan_Malzemeleri"
    ActiveWorkbook.RefreshAll
    Application.EnableEvents = True

I have a large and complicated Excel query, which works as desired.

However, I'm implementing some real-time data-validation features (i.e. not requiring a data refresh), and I need to have a regular excel formula in one of the columns of my query results.

This formula would perform a real-time data comparison using other sheets in the workbook, intentionally independent from the query itself.

Can I add a custom column with no value?
I assume the values null or "" would overwrite any pre-existing data, so I couldn't just enter the excel formula into the results table after running the query (the formula wouldn't persist between refreshes).

For testing, I tried adding a custom column with the value "=5+2", just to see how it would behave.

let
    Source = Excel.CurrentWorkbook(){[Name="tblInvoicesCategorized"]}[Content],

    /* ... perform numerous query actions ... */

    // Use "=5+2" as a test formula
    #"Added Custom13" = Table.AddColumn(#"Added Custom12", "Stale Data", each "=5+2"),

    /* ... perform numerous query actions ... */

in
    #"Changed Type"

The query did output the formula as the value of the cells in the column, but Excel did not automatically execute the formulas.
I still had to manually place the cursor into a cell and press enter, to get it to execute the formula.

Manual entry of the formula, or even manual execution of a macro, is undesirable for this workbook.

Is there a way for me to have a regular formula in this query column, and have it automatically persist between data refreshes?

解决方案

a workaround is to trigger the refreshall on cell change by some VBA code ...

this was my case, hope it helps...

Private Sub Worksheet_Change(ByVal Target As Range)

  '
  '  Dim lTest As Long, cn As WorkbookConnection
  '  On Error Resume Next
  '  For Each cn In ThisWorkbook.Connections
  '      lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
  '  If Err.Number <> 0 Then
  '      Err.Clear
  '      Exit For
  '  End If
  '  If lTest > 0 Then cn.Refresh
  '  Next cn




    If Intersect(Target, Me.Range("datachange")) Is Nothing Then Exit Sub

    Application.EnableEvents = False 'to prevent endless loop

    'Application.Goto Reference:="Tum_Santiyelerin_Satinalinan_Malzemeleri"
    Range("Tum_Santiyelerin_Satinalinan_Malzemeleri").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre"
    'Range("Filtre").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Malzeme"
    Range("Filtre_Malzeme").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Proje"
    Range("Filtre_Proje").ListObject.QueryTable.Refresh BackgroundQuery:=False

    'Application.Goto Reference:="Filtre_Firma"
    Range("Filtre_Firma").ListObject.QueryTable.Refresh BackgroundQuery:=False



    Application.Goto Reference:="Tum_Santiyelerin_Satinalinan_Malzemeleri"
    ActiveWorkbook.RefreshAll
    Application.EnableEvents = True

这篇关于如何生成常规Excel公式作为查询结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！