您正在尝试生成数据透视表。您需要按用户分组，以便可以向表中添加一行用户。然后通过属性枚举。使用如下代码

$table = [System.Collections.ArrayList]::new()
ForEach($user in $users)
{
   $newRow = New-Object -TypeName psobject
   ForEach($property in $properties)
   {
      $newRow | Add-Member -NotePropertyName  propertyName -NotePropertyValue value
   }
   $table.Add($newRow) | Out-Null
}

下面是我使用的解决方案。我添加了一个“摘要”工作表，其中有2列：用户和组。然后添加了透视“摘要”工作表数据的“透视数据”工作表。

# The ActiveDirectory (AD) App generally only lists Staff Ids which is painful when trying to find out each staff members name to determine who needs to be removed from what groups.
# This script will:
# * Export certain properties of AD Group Members (eg: StaffIds, Names, Depts, etc) for each SSRS AD Group that <Dept> uses for <Dept> RAP Report Security.
# * Exports the data to an Excel Spreadsheet
# ** 1 worksheet per AD Group, listing it's members
# ** Summary worksheet
# ** PivotData worksheet (based on Summary worksheet)
#
# PRE-REQUISITES: 
# * Export-Excel module must be installed
# * ActiveDirectory module must be installed
# ** Remote Server Administration Tools (RSAT) must be installed
# --------------------------------------------------------------------------

# 1st shows default properties (-Properties not reqd) that can be used in the Select below.
# 2nd shows extended properties, must use -Properties then Select.
#Get-ADUser 12345678 
#Get-ADUser 12345678 -Properties *

Clear-Host

# Set the filename for the output Excel file
$dateTimeStr = (get-date).tostring('yyyyMMdd_HHmmss')
[String]$ExcelPath  = "C:\Temp\SsrsAdGroups_$dateTimeStr.xlsx"

# Set the list of AD Groups to export results for
[String[]]$AdGroups = ("Group1", "Group3", "Group10")

# Loop through each AD Group in the list
# Export certain properties/fields for each user/member in the AD Group to a worksheet with the same name as the AD Group
# Each excel worksheet will have a table with the AD Group name, auto sized and the top row frozen for formatting/layout purposes.
$UserGroupSummary = @()
ForEach($AdGroup in $AdGroups)
{
    # Prepare data for Summary Excel Worksheet
    $Users = Get-AdGroupMember -Identity $AdGroup -Recursive | `
             Get-ADUser -Properties DisplayName | `
             ForEach { "{0}  ({1})" -f $_.DisplayName, $_.SamAccountName } 
    ForEach( $User in $Users ) {
        $UserGroupSummary += [PSCustomObject]@{
                            User  = $User
                            Group = $AdGroup
                        }
    }

    # AdGroup Excel Worksheets
    Get-AdGroupMember -Identity $AdGroup -Recursive | `
    Get-ADUser -Properties DisplayName,EmailAddress,Enabled,Title,Department,Office,Company,Manager | `
    Select SamAccountName,Surname,GivenName,DisplayName,EmailAddress,Enabled,@{n="ManagerName";e={(Get-AdUser $_.manager -Properties DisplayName).DisplayName}},Title,Department,Office,Company | `
    Sort-Object Surname,GivenName | `
    Export-Excel -Path $ExcelPath -AutoSize -WorksheetName $AdGroup -TableName $AdGroup -FreezeTopRow
} 
# Summary Excel Worksheet - Users & their matching Groups - data source for an Excel pivot table
$UserGroupSummary | Sort-Object User,Group | Export-Excel -Path $ExcelPath -AutoSize -WorksheetName Summary -TableName Summary -FreezeTopRow

# Add a Pivot Table to the Excel file (if it exists) based on the Summary worksheet
If (Test-Path $ExcelPath) 
{
    Try {

        # Open the Excel file
        $excel = Open-ExcelPackage -Path $ExcelPath

        # Create new worksheet at the start
        Add-Worksheet -ExcelPackage $excel -Activate -MoveToStart -WorksheetName PivotData | Out-Null

        # Add a pivot table to the new Excel worksheet
        Add-PivotTable -ExcelPackage $excel -SourceWorkSheet "Summary" -PivotTableName "PivotData" -PivotTableStyle Medium9 -PivotRows User -PivotColumns Group -PivotData Group #-Activate

        # Save & close the Excel file
        Close-ExcelPackage $excel
    }
    Catch {
        # An error occurred
        $message = $_
        write-output "ERROR - $message"
        write-warning "ERROR updating existing Excel file $ExcelPath. ABORTING. $message"
    }

    # Open the excel file
    $excelObj = New-Object -ComObject Excel.Application
    $excelObj.DisplayAlerts = $false;

    # Change pivot table to tabular format - only seems to work if make Visible true for some reason
    $excelObj.Visible = $true
    $excelWb = $excelObj.Workbooks.Open($ExcelPath)
    $excelWsh = $excelWb.Sheets("PivotData")
    $pivot = $excelWsh.PivotTables(1)
    $pivot.RowAxisLayout(1)   # Tabular layout
    $pivot.EnableFieldList = $false
    $excelObj.Visible = $false

    # Freeze panes at B3 in PivotData Worksheet
    [void]$excelObj.Cells.Item(3,2).Select()
    $excelObj.ActiveWindow.FreezePanes = $True

    # Save and close
    $excelObj.ActiveWorkbook.SaveAs($ExcelPath, 51)   # xlsx
    $excelObj.Workbooks.Close()
    $excelObj.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObj) | Out-Null

}

write-output "Finished exporting data to $ExcelPath"

# --------------------------------------------------------------------------