☰

Excelの数式でグループ化レベルにアクセスすることは可能ですか

tag-icon • tag-icon microsoft-excel
Excelの数式でグループ化レベルにアクセスすることは可能ですか

次のような Excel シートがあります。

ここに画像の説明を入力してください

グループ化レベル (赤でマークされた数字) にアクセスする数式を記述することは可能ですか?

問題は、機械製品に含まれるさまざまな品目をトップダウン方式で記述した BOM (部品表) をエクスポートするソフトウェアがあることです。グループ番号は、特定の記事がどのレベルにあるかを知る唯一の方法です。。

答え1

マクロのVBAスクリプトを試してみませんか?

これが役に立つと思います: Worksheets("Sheet1").Rows(i).OutlineLevel

シートを調べて、このプロパティをセルに書き込みます。

答え2

私もまったく同じ状況に陥っており、アプリケーションから出力されたアウトライン レベルを適切に使用したいと考えています。その場合、Excel で Visual Basic スクリプトを作成する必要があります。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

そして最後に、レベル列をチェックして追加列のすべてのセルを入力し、正しいセルに「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 ファイルとして保存する必要があります。詳細情報ここ

  1. OutlineLevel次の数式を使用して、シートの一番上のセル (例: A1) に名前付き範囲 (例: ) を定義します。=GET.CELL(28,Sheet1!A1)
  2. 空の列でこの名前付き範囲 ( =OutlineLevel) を参照して、数式をコピーします。

対応する行の階層レベルが取得されます。

GlennUKが提案したかなり古い解決策に敬意を表します。Mr.Excel フォーラム

関連情報