☰

Можно ли получить доступ к уровню группировки в формуле Excel?

tag-icon • tag-icon microsoft-excel
Можно ли получить доступ к уровню группировки в формуле Excel?

У меня есть таблица Excel, которая выглядит примерно так:

введите описание изображения здесь

Можно ли написать формулу, которая обращается к уровням группировки (числа, отмеченные красным)?

Проблема в том, что у нас есть программное обеспечение, которое экспортирует спецификацию материалов (BOM), описывающую сверху вниз различные статьи, содержащиеся в механическом изделии.Номер группировки — единственный способ узнать, на каком уровне находится определенная статья..

решение1

почему бы не попробовать скрипт макроса VBA?

Я думаю, это поможет: Worksheets("Sheet1").Rows(i).OutlineLevel

Пройдитесь по листу и запишите это свойство в ячейки.

решение2

У меня точно такая же ситуация, когда я хочу получить правильное использование уровня структуры, который был выведен из моего приложения. Затем вам нужно создать скрипт Visual Basic в Excel. Редактор скриптов VB можно открыть через редактор макросов. Типичный код, который вставляет дополнительный 2-й столбец, который будет содержать уровень, а затем проходит через файл и заполняет столбец правильным уровнем, может выглядеть так:

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

И наконец, двойная циклическая последовательность, которая заполняла все ячейки в дополнительных столбцах с проверкой по столбцу Level и ставила "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Форум Mr.Excel

Связанный контент