Excel带节点层级表格转换：=offset()能否实现表1到表2的数据转换？

Absolutely! The OFFSET() function is totally up to the task of converting your hierarchical Excel table from Table 1 to Table 2. Let me break down exactly how to make it work, using common hierarchical data setups as examples.

Core Logic Behind the Solution #

At its core, OFFSET() lets you reference a cell by defining a starting point, then offsetting a specific number of rows or columns from it. For hierarchical data, this is perfect because we can:

• Track the last occurrence of each higher-level parent node (e.g., the last Level 1 department when processing a Level 3 team)
• Use OFFSET() to pull that parent node’s value into your flattened Table 2 structure

Example Setup #

Let’s assume your Table 1 looks something like this (super common for hierarchical data):

• Column A: Explicit level number (1 = top-level, 2 = child of Level 1, 3 = child of Level 2, etc.)
• Column B: Node name (e.g., "Headquarters", "Department A", "Team 1")

Your goal for Table 2 is to flatten this into columns for each level (e.g., "Level 1", "Level 2", "Level 3"), so every row shows the full path of the node.

Step-by-Step Formulas #

1. Level 1 Column in Table 2

If your first data row in Table 1 is row 2, enter this formula in the first Level 1 cell of Table 2 (e.g., Table2!A2):

=OFFSET(Table1!B$2, MATCH(MAX(Table1!$A$2:INDEX(Table1!$A:$A,ROW()-1)*(Table1!$A$2:INDEX(Table1!$A:$A,ROW()-1)=1)), Table1!$A$2:INDEX(Table1!$A:$A,ROW()-1), 0)-1, 0)

What this does: It finds the most recent Level 1 node above the current row in Table 1, then uses OFFSET() to pull its name into Table 2.

2. Level 2 Column in Table 2

For the Level 2 column (e.g., Table2!B2), use this:

=IF(Table1!A2=2, Table1!B2, OFFSET(Table2!B1, 0, 0))

• If the current row in Table 1 is a Level 2 node, it grabs that name directly.
• For Level 3+ nodes, it copies the Level 2 value from the row above in Table 2 (using OFFSET() to reference the cell just above).

3. Level 3 & Beyond

Extend the same pattern for deeper levels. For Level 3 (e.g., Table2!C2):

=IF(Table1!A2=3, Table1!B2, OFFSET(Table2!C1, 0, 0))

This works for any number of hierarchy levels—just adjust the level number in the IF() condition and the column reference in OFFSET().

Pro Tips to Avoid Headaches #

• Sort your data first: Make sure your Table 1 is sorted so parent nodes always come before their children. This ensures OFFSET() can correctly find the right parent node.
• Watch your references: Use absolute ($) and relative references correctly so formulas copy properly when you drag them down rows.
• Adjust for indented data: If your hierarchy uses indents instead of explicit level numbers, tweak the formulas to check cell indentation (e.g., with CELL("indent",...)) instead of level values—OFFSET() still works perfectly for tracking parents.

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