salga - referencia absoluta en tabla excel

Rangos nombrados de Excel para hacer encabezados y subcabeceras de incremento

Question

Estoy creando una aplicación de hoja de cálculo que requerirá que el usuario complete diferentes secciones de una hoja de entrada. Para separar estas secciones en medios lógicos, estoy usando encabezados y encabezados secundarios como se ve a continuación:

Si tengo muchos encabezados, habrá una gran cantidad de trabajo manual en la numeración de los encabezados. He intentado automatizar el proceso creando un Header1 nombre Header1 que equivale a los encabezados grises en la imagen a continuación. Entonces, dentro de las celdas A2 y A11 , la fórmula es =Header1 . La fórmula en el Header1 nombre Header1 es:

IF(COUNTA(INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()))))=0,0,INDEX(INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()))),MATCH(ROW(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()))),TRUE)))+1

Básicamente, la fórmula cuenta todos los valores en la columna A y agrega 1 en. En otras palabras, se incrementa cada vez que se llama a Header1 en su celda, independientemente de la hoja de trabajo en la que se encuentre (de ahí el uso de INDIRECT ). La única pieza de codificación dura es la celda inicial que es A1 y he puesto un 0 en eso, así que detecta que empieza desde 1.

Mi pregunta es : de una manera similar a como he logrado incrementar los encabezados usando rangos con nombre, ¿cómo puedo hacer esto para sub encabezados? Mi imagen de arriba muestra el efecto que estoy tratando de lograr (es decir, 2.1 , 2.2 ), sin embargo, quiero que esto sea automático simplemente poniendo una fórmula de =Header2 en la celda, por ejemplo.

EDITAR - He llegado tan lejos como esta fórmula:

=SUM(INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()-1)))) & "." & MAX(1,COUNTA(INDEX(INDIRECT(CONCATENATE("$B$1",":",ADDRESS(ROW()-1,COLUMN()))),MATCH(SUM(INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()))))-1,INDIRECT(CONCATENATE("$A$1",":",ADDRESS(ROW()-1,COLUMN()-1))),FALSE)):INDIRECT(ADDRESS(ROW()-1,COLUMN()))))

... que funciona cuando se coloca como una fórmula en una celda, pero no funciona cuando se utiliza en un rango con nombre. ¡Impar!

Answer 1

Buena idea al usar Defined Names para contener la fórmula. Sin embargo, su fórmula es altamente volátil.

Sugerir crear dos nombres definidos en el nivel de libro de trabajo (alcance) de la siguiente manera:

1. Llamado _Hdr (cambiar según se requiera) con la siguiente fórmula:

   =IF( COLUMN() <> 1, "", 1 
   + MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ) )

2. Llamado _Sub (cambiar según se requiera) con la siguiente fórmula:

   =IF( COLUMN() <> 2, "", 0.01 
   + IF( MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ) 
   > MAX( INDEX(!$B:$B, 1 ) : INDEX(!$B:$B, - 1 + ROW() ) ), 
   MAX( INDEX(!$A:$A, 1 ) : INDEX(!$A:$A, - 1 + ROW() ) ), 
   MAX( INDEX(!$B:$B, 1 ) : INDEX(!$B:$B, - 1 + ROW() ) ) ) )

Use 0.1 si los elementos secundarios son menores que 10, si son más altos pero son menores de 100, use 0.01 (ajuste según sea necesario)

Editar: ! Agregar ! a los rangos de columna en las fórmulas para garantizar que las referencias se actualizarán automáticamente a la Sheet correspondiente donde se utiliza la fórmula.

Las fórmulas anteriores están rotas en varias líneas para facilitar la lectura y la comprensión, ingrese luego como una línea al crear los nombres.

Sugiera también ocultar los Define Names (es decir, Visible = False ) para evitar que las fórmulas se alteren accidentalmente .

Con los nombres anteriores:

• No es necesario ingresar cero en la fila 1.
• Las fórmulas funcionan con números que ignoran cualquier etiqueta.
• Primero verifican que la fórmula se ingrese en la columna esperada (es decir, _Hdr y _Sub columnas 1 y 2 respectivamente) .
• A medida que las fórmulas funcionan con números, obtienen el número MAX para cada columna y agregan 1 o 0.1 para generar el siguiente número.
• Las fórmulas no son volátiles (no se usa INDIRECT ) .
• Usan la función INDEX para generar los rangos requeridos.