如何用PowerShell生成AD用户-指定组归属矩阵并添加至Excel汇总表

要在现有脚本基础上添加用户-组归属矩阵的汇总工作表，可按以下方式修改脚本：

完整修改后的脚本 #

# 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 with 1 worksheet per AD Group, listing it's members.
# * Adds a summary worksheet showing user-group membership matrix
#
# PRE-REQUISITES: 
# * Export-Excel module must be installed
# * ActiveDirectory module must be installed
# ** Remote Server Administration Tools (RSAT) must be installed
# --------------------------------------------------------------------------

# Shows what Properties are available that can be added to lines 30-35 below
#Get-ADUser 12345678 -Properties *

Clear-Host

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

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

# 存储每个组的成员SamAccountName，用于快速查询
$groupMembers = @{}
# 存储所有涉及的用户，去重
$allUsers = @()

# 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.
ForEach($AdGroup in $AdGroups)
{
     $members = Get-AdGroupMember -Identity $AdGroup -Recursive | 
                Get-ADUser -Properties SamAccountName,Surname,GivenName,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
     
     # 保存当前组的成员SamAccountName到哈希表
     $groupMembers[$AdGroup] = $members.SamAccountName | Sort-Object
     # 合并到总用户列表并去重
     $allUsers += $members
     $allUsers = $allUsers | Sort-Object SamAccountName -Unique

     # 导出当前组的工作表
     $members | Export-Excel -Path $ExcelPath -AutoSize -WorksheetName $AdGroup -TableName $AdGroup -FreezeTopRow
} 

# -------------------------- 生成汇总矩阵并导出 --------------------------
$summaryData = foreach ($user in $allUsers) {
    # 初始化用户对象，包含基础信息
    $userObj = [PSCustomObject]@{
        User = $user.SamAccountName
        DisplayName = $user.DisplayName
    }
    # 为每个组添加归属状态列
    foreach ($group in $AdGroups) {
        if ($groupMembers[$group] -contains $user.SamAccountName) {
            $userObj | Add-Member -MemberType NoteProperty -Name $group -Value '1'
        }
        else {
            $userObj | Add-Member -MemberType NoteProperty -Name $group -Value ''
        }
    }
    $userObj
}

# 将汇总数据导出到新的工作表
$summaryData | Export-Excel -Path $ExcelPath -AutoSize -WorksheetName "Group-Member Summary" -TableName "GroupMemberSummary" -FreezeTopRow -Append
# --------------------------------------------------------------------------

关键部分说明 #

• 数据收集：用$groupMembers哈希表存储各组成员账号，后续查询用户归属时效率更高；$allUsers汇总所有出现过的用户并去重，确保矩阵每行是唯一用户。
• 矩阵生成：遍历每个用户，先创建包含基础信息的对象，再逐个判断用户在目标组的归属状态，添加对应列的1或空值，最终生成符合要求的矩阵数据。
• Excel导出：使用-Append参数将汇总表追加到已有Excel文件，避免覆盖之前的组工作表；保留AutoSize和FreezeTopRow参数，保持格式统一。

内容的提问来源于stack exchange，提问作者Shell D