☰

是否可以存取 Excel 公式中的分組級別

tag-icon • tag-icon microsoft-excel
是否可以存取 Excel 公式中的分組級別

我有一個 Excel 工作表，看起來像這樣：

在此輸入影像描述

是否可以編寫一個存取分組層級（以紅色標記的數字）的公式？

問題是我們有一個軟體可以匯出 BOM（物料清單），以自上而下的方式描述機械產品中包含的不同物品。分組編號是了解某篇文章屬於哪個層級的唯一方法。

答案1

為什麼不試試巨集的VBA腳本呢？

我認為這會有所幫助：Worksheets("Sheet1").Rows(i).OutlineLevel

瀏覽工作表並將此屬性寫入儲存格中。

答案2

我有完全相同的情況，我想正確使用從我的應用程式輸出的大綱層級。然後，您需要在Excel中製作一個Visual Basic腳本。可以透過巨集編輯器存取 vb 腳本編輯器。插入包含層級的附加第二列，然後遍歷檔案並使用正確層級填入該列的典型程式碼如下所示：

Sub GetOutlineLevel()
' Initiate where to start. With a header row, typical start is row 2
  RowCount = 2

' Insert a new column B, write "Level" as the title in row 1, 
' and define the width of the column  
  Columns("B:B").Select
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Cells(1, 2).Value = "Level"
  Columns("B:B").Select
  Selection.ColumnWidth = 5

' Go to the first row you want to analyze
  Cells(RowCount, 1).Activate

' Make a loop to go through all lines that contains data.
' This assumes that there is a continuous row of lines that all have data in the
' first column  
  Do
' Fill the cell in column 2 at the current row with the outline level
    Cells(RowCount, 2).Value = ActiveCell.Rows.OutlineLevel
' Go to the next row
    RowCount = RowCount + 1
    Cells(RowCount, 1).Activate
' Continue and stop at the first empty row
  Loop Until ActiveCell.Value = ""
End Sub

然後還可以透過其他功能進行擴充。我追蹤了上面循環中的最高大綱級別，並製作了與級別一樣多的附加列，然後在每個列中創建了一些邏輯，以便可以可視化級別結構。

列的插入如下所示（這包括使包含“X”的單元格變為黑色的條件格式設定）：

  ColumnsInserted = 0
  Do
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells(1, 3).Value = "L" & MaxLevel - ColumnsInserted
    Columns("C:C").Select
    Selection.ColumnWidth = 4
    Columns("C:C").Select
    Selection.FormatConditions.Add Type:=xlTextString, String:="X", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

    ColumnsInserted = ColumnsInserted + 1
  Loop Until ColumnsInserted = MaxLevel

最後是一個雙循環序列，透過檢查等級列來填入附加列中的所有儲存格，並在正確的儲存格中放置一個「X」。這段程式碼看起來像這樣：

  FIns = 1
  Do
    RowCount = 2
    Do
      Cells(RowCount, 2 + FIns).Select
      ActiveCell.FormulaR1C1 = "=IF(RC[-" & FIns & "]=" & FIns & ",""X"","""")"
      RowCount = RowCount + 1
    Loop Until Cells(RowCount, 1).Value = ""
    FormulaInsert = FormulaInsert + 1
  Loop Until FormulaInsert > MaxLevel

我發現上面程式碼的良好縮排在最後一篇文章中被破壞了，但是如果您熟悉某些編碼，您應該能夠以良好的方式解決這個問題。

答案3

是的，這是可能的。該功能GET.CELL()會有所幫助。
GETCELL是一個特殊的函數。確實是 Excel 4 巨集函數，因此使用時必須將檔案另存為 .xlsm-File。更多資訊這裡

  1. OutlineLevel使用下列公式在工作表的頂部儲存格（例如 A1）中定義命名範圍（例如）：=GET.CELL(28,Sheet1!A1)
  2. 在空白列中引用此命名範圍 ( =OutlineLevel) 並複製公式。

您將獲得相應行的層次結構層級。

值得讚揚的是 GlennUK 提出的相當古老的解決方案Excel先生論壇

相關內容