excel - 透视全文而不是计数 - Excel

Question

我正在尝试确定将 sheet1 上的中央表格中的特定行显示为 sheet2 上的文本的最高效/有效的方法。

我设置了一个表，其中包含许多不断被多人使用的事件。该表充当中央数据库，并与实时使用它的多个人共享。

我想在 sheet2 上提供一个表格，允许其他用户查看中央数据库中的特定事件。因此，我需要将特定值从 sheet1 导出到 sheet2。我知道使用过滤器可以轻松完成此操作，但 sheet1 上的表格会不断被使用，并且不会被中断。

我不能只对 sheet1 表进行排序或过滤，因为它需要随时被其他方使用

我实际上只需要查看上个月 sheet1 中的特定值。我已经编写了基于 Sheet1 上指定列中输入的特定值导出所有行的代码。但由于文件的大小，Excel 经常崩溃。

然后我认为数据透视表可能更容易，而且我不必使用 VBA。是否可以将特定行转出为文本，可以按日期分组，例如月？

例如，如果我想查看最后一个 Column B 中的所有 ['A's'] 和 ['X's']月份全文如下:

中央数据库表Sheet1

   A   B  C   D
0 11/1 A Big Dog
1 10/1 X  1   2 
2 11/1 Y  Y   Y
3 1/2  A Big Cat
4 1/2  X  3   4 
5 1/2  Y  Y   Y

输出表Sheet2

   A  B  C   D
1 1/2 A Big Cat
2 1/2 X  3   4

Answer 1

正如其他人在评论中提到的，将 SQL 与 ADODB 结合使用可能是比使用数据透视表更好的方法。我还建议将数据 (Sheet1) 与表示层 (Excel) 分开。例如。将数据存储在实际数据库中，例如 Access、SQL Server 等。

但是，当您正在寻找权宜之计时，我想我可以为您提供一种可能暂时满足需求的方法。代码已注释，但请随时提出问题。您需要添加对 Microsoft Active X Data Object 2.8 或更高版本 的引用才能使其正常工作。 How to add a reference?

<小时/>

早期绑定(bind)方法

Option Explicit
Public Sub DisplayView(StartDate As Date, EndDate As Date)
    'Add a reference to Microsoft Active X Data Object 2.8 or greater
    Dim dbConnection  As ADODB.Connection
    Dim dbRecordset   As ADODB.Recordset
    Dim dbCommand     As ADODB.Command
    Dim OutputSheet   As Excel.Worksheet
    Dim dbField       As Variant
    Dim fieldCounter  As Long

    Set dbConnection = New ADODB.Connection
    Set dbRecordset = New ADODB.Recordset
    Set dbCommand = New ADODB.Command
    Set OutputSheet = ThisWorkbook.Worksheets("Sheet2")

    'Do a quick check to determine the correct connection string
    'if one of these don't work, have a look here --> https://www.connectionstrings.com/excel/
    If Left$(ThisWorkbook.FullName, 4) = "xlsm" Then
        dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES';"
    Else
        dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES';"
    End If

    'Open the connection and parameterize the query
    dbConnection.Open
    With dbCommand
        .ActiveConnection = dbConnection
        .CommandType = adCmdText
        'A in B in the text below are the field names in your Sheet 1
        'I wasn't sure what the names of the fields are so I named them as they appeared
        'That being Column A is called A, Column B is called B etc
        .CommandText = "Select * from [Sheet1$] where B in ('A','X') and A >= @StartDate and A < @EndDate"
        .Parameters.Append .CreateParameter("@StartDate", adDate, adParamInput, , StartDate)
        .Parameters.Append .CreateParameter("@EndDate", adDate, adParamInput, , EndDate)
        Set dbRecordset = .Execute
    End With

    'Clear the Output Sheet
    OutputSheet.Cells.Clear

    'Add Headers to output
    For Each dbField In dbRecordset.Fields
        fieldCounter = fieldCounter + 1
        OutputSheet.Cells(1, fieldCounter).Value2 = dbField.Name
    Next

    'Dump the found records
    OutputSheet.Range("A2").CopyFromRecordset dbRecordset
    If dbConnection.State = adStateOpen Then dbConnection.Close
End Sub

'Run from here
Public Sub ExampleRunner()
    'Supply the dates you want to filter for
    DisplayView #1/1/2019#, #1/20/2019#
End Sub

根据要求，这里是后期绑定(bind)方法，不需要显式引用Microsoft Active X 数据对象。

Option Explicit
Private Const adCmdText As Long = 1
Private Const adDate As Long = 7
Private Const adParamInput As Long = 1
private const adStateOpen as long = 1

Public Sub DisplayView(StartDate As Date, EndDate As Date)
    'Add a reference to Microsoft Active X Data Object 2.8 or greater
    Dim dbField       As Variant
    Dim fieldCounter  As Long
    Dim dbConnection  As Object
    Dim dbRecordset   As Object
    Dim dbCommand     As Object
    Dim OutputSheet   As Excel.Worksheet

    Set dbConnection = CreateObject("ADODB.Connection")
    Set dbRecordset = CreateObject("ADODB.Recordset")
    Set dbCommand = CreateObject("ADODB.Command")

    Set OutputSheet = ThisWorkbook.Worksheets("Sheet2")

    'Do a quick check to determine the correct connection string
    'if one of these don't work, have a look here --> https://www.connectionstrings.com/excel/
    If Left$(ThisWorkbook.FullName, 4) = "xlsm" Then
        dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES';"
    Else
        dbConnection.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        ThisWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES';"
    End If

    'Open the connection and parameterize the query
    dbConnection.Open
    With dbCommand
        .ActiveConnection = dbConnection
        .CommandType = adCmdText
        'A in B in the text below are the field names in your Sheet 1
        'I wasn't sure what the names of the fields are so I named them as they appeared
        'That being Column A is called A, Column B is called B etc
        .CommandText = "Select * from [Sheet1$] where B in ('A','X') and A >= @StartDate and A < @EndDate"
        .Parameters.Append .CreateParameter("@StartDate", adDate, adParamInput, , StartDate)
        .Parameters.Append .CreateParameter("@EndDate", adDate, adParamInput, , EndDate)
        Set dbRecordset = .Execute
    End With

    'Clear the Output Sheet
    OutputSheet.Cells.Clear

    'Add Headers to output
    For Each dbField In dbRecordset.Fields
        fieldCounter = fieldCounter + 1
        OutputSheet.Cells(1, fieldCounter).Value2 = dbField.Name
    Next

    'Dump the found records
    OutputSheet.Range("A2").CopyFromRecordset dbRecordset
    If dbConnection.State = adStateOpen Then dbConnection.Close
End Sub

'Run from here
Public Sub ExampleRunner()
    'Supply the dates you want to filter for
    DisplayView #1/1/2019#, #1/20/2019#
End Sub