第一个使用单元格地址以上 [英] First used cell above address

问题描述

我在Excel中创建了一个预算，因此我有一些类别和子类别。与其各自的父类别相比，子类别由1个单元计算。子类别当然可以有子类别。

在我的类别树旁边的列中，我想打印类别路径。

假设我有以下类别的树：

我现在想要将类别路径打印出来，如下所示：

我以为我可以使用父类别（如果有）的类别路径和当前行的类别名称来组成类别路径。
由于子类别可以有子类别，我不知道在哪一列中存储当前行的类别名称。要获取当前行中的类别名称，我使用以下公式（在

更好的结构在我看来，让所有的类都被填满（更像是数据库），然后如果您希望您可以使用数据透视表，这样可以更轻松地分析数据。

I'm creating a budget in Excel and therefore I have some categories and subcategories. Subcategories are intended by 1 cell compared to their respective parent category. Subcategories of course can have subcategories on their own.

In the column next to my "category tree" I want to print the "category path".

Let's say I have the following tree of categories:

I now want the "category path" to be printed next to it like this:

I thought I could compose the category path with the category path of the parent category (if any) and the category name on the current row. As subcategories can have subcategories on their own, I don't know in which column the category name on the current row is stored. To get the category name on the current row I'm using the following formula (found on this site (German)):

{=INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0))}

I know there's no category more than five levels deep, therefore I can savely "hard code" e.g. the range A7:F7 for row 7 and receive "Side job" as result of the above formula.

Now comes the tricky part (at least for me) and the reason I'm asking this question: I need to get the line of the parent category so I can then use the parent's category path to compose the category path of the subcategory. In this case it would be

{=H4&" > "&INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0))}

I don't know how to dynamically calculate the address of the parent's category path. From another German site I've got a formula which goes into the right direction and would return the row of the parent category "Wage & Salary":

{=MAX((A1:A7<>"")*ROW(A1:A7))}

But only if the matrix in the formula is constant. It doesn't work with a dynamically calculated matrix. The dynamic matrix should range from A1 to A7 in case of "Side job" as A is the column left to the category name, 1 is the very first row (and the row I want to search up to) and 7 is the current row. The dynamic part of this matrix is A and 7.

In order to compose the matrix (the DYNAMIC_MATRIX) I'm using this formula:

{=INDIRECT(ADDRESS(1;CATEGORY_COLUMN-1)&":"&ADDRESS(ROW();CATEGORY_COLUMN-1))}

where CATEGORY_COLUMN is my first formula wrapped in COLUMN():

{=COLUMN(INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0)))}

Unfortunately, the MAX(... formula returns an error when using it with the DYNAMIC_MATRIX:

{=MAX((DYNAMIC_MATRIX<>"")*ROW(DYNAMIC_MATRIX))}

To bypass the error I replaced DYNAMIC_MATRIX<>"" with NOT(ISBLANK(DYNAMIC_MATRIX)):

{=MAX(NOT(ISBLANK(DYNAMIC_MATRIX))*ROW(DYNAMIC_MATRIX))}

Now, that's how the formula looks like without the placeholders:

{=MAX(NOT(ISBLANK(INDIRECT(ADDRESS(1;COLUMN(INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0)))-1)&":"&ADDRESS(ROW();COLUMN(INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0)))-1))))*ROW(INDIRECT(ADDRESS(1;COLUMN(INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0)))-1)&":"&ADDRESS(ROW();COLUMN(INDEX(A7:F7;MATCH(TRUE;A7:F7<>"";0)))-1))))}

My formula is returning the current row instead of the parent category's row (that is, e.g. for "Side job" it returns 7 instead of 4).

I don't know why :(

Short, the whole formula should do the following:
If it's an subcategory on the current row, print parent category's path > subcategory name. The parent category's path should be Income > category name if the parent category is stored in column A (and therefore has no parent category on their own (except the "Income" group heading)).

It's absolutely sufficient for me if someone who wants to answer my questions only focuses on the "get parent category's path" part :)

Thanks,
Marcel

解决方案

Here is an answer that will go out the 5 levels asked for in the question.

Based on a formula Here.

=LOOKUP(2,1/($A$2:A2<>""),$A$2:A2) &
IF(COUNTA($B2:$D2)>0,">" & LOOKUP(2,1/($B$2:B2<>""),$B$2:B2)  &
IF(COUNTA($C2:$D2)>0,">" & LOOKUP(2,1/($C$2:C2<>""),$C$2:C2)  &
IF(COUNTA($D2:$D2)>0,">" & LOOKUP(2,1/($D$2:D2<>""),$D$2:D2)  &
IF(COUNTA($E2:$E2)>0,">" & LOOKUP(2,1/($F$2:F2<>""),$F$2:F2),""),""),""),"")

Notice I put income in it's own column, as soon as you make exceptions about when you indent in and when you don't its trouble for data management (and this data storage format is already not great).

A better structure In my opinion is to have All the catagories filled down (more like a database) and then if you want you can use pivot tables and such to analyse the data easier.

这篇关于第一个使用单元格地址以上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！