☰

É possível acessar o nível de agrupamento em uma fórmula Excel

tag-icon • tag-icon microsoft-excel
É possível acessar o nível de agrupamento em uma fórmula Excel

Eu tenho uma planilha Excel parecida com esta:

insira a descrição da imagem aqui

É possível escrever uma fórmula que acesse os níveis de agrupamento (os números marcados em vermelho)?

O problema é que temos um software que exporta uma BOM (Bill Of Materials) descrevendo, de cima para baixo, os diferentes artigos contidos em um produto mecânico.O número de agrupamento é a única maneira de saber em que nível um determinado artigo está.

Responder1

por que não tentar o script VBA de macro?

Acho que isso vai ajudar: Worksheets("Sheet1").Rows(i).OutlineLevel

Percorra a planilha e escreva essa propriedade nas células.

Responder2

Tenho exatamente a mesma situação em que desejo obter um uso adequado do nível de estrutura de tópicos gerado pelo meu aplicativo. Então, você precisa fazer um script visual básico no Excel. O editor vb-script pode ser acessado através do editor Macro. Um código típico que insere uma segunda coluna adicional que conterá o nível e depois passa por um arquivo e preenche a coluna com o nível correto pode ter a seguinte aparência:

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

Isso também pode ser estendido com outras funcionalidades. Acompanhei o nível de contorno mais alto no loop acima e criei tantas colunas adicionais quantos fossem os níveis e, em seguida, fiz alguma lógica em cada uma dessas colunas para que a estrutura do nível pudesse ser visualizada.

A inserção de colunas ficou assim (incluía uma configuração de formatação condicional que tornava as células contendo um "X" preto):

  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

E, finalmente, uma sequência de loop duplo que preencheu todas as células nas colunas adicionais com uma verificação na coluna Nível e colocou um "X" na célula correta. Este código ficou assim:

  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

Vejo que o belo recuo do código acima está arruinado na postagem final, mas se você estiver familiarizado com alguma codificação, deverá conseguir resolver isso da melhor maneira.

Responder3

Sim é possivel. A função GET.CELL()ajudará.
GETCELLé uma função especial. De fato, uma função de macro do Excel 4 e, portanto, o arquivo deve ser salvo como .xlsm-File ao usá-lo. Mais informaçõesaqui

  1. Defina um intervalo nomeado (por exemplo, OutlineLevel) na célula superior da sua planilha (por exemplo, A1) com a seguinte fórmula:=GET.CELL(28,Sheet1!A1)
  2. em uma coluna vazia, consulte este intervalo nomeado ( =OutlineLevel) e copie a fórmula.

Você obterá o nível de hierarquia da linha correspondente.

Parabéns à solução proposta bastante antiga de GlennUK noFórum Mr.Excel

informação relacionada