Las funciones FILA() y COLUMNA()

Las funciones FILA() y COLUMNA() de Excel son de ese tipo de funciones que a primera vista parecen superfluas. FILA() da como resultado el número de fila de una referencia: COLUMNA() hace lo mismo en relación al número de columna de la referencia

Es decir, si ponemos en un celda =FILA(A4) el resultado será 4; si ponemos =FILA(), sin referencia, el resultado sera el número de fila de la celda que ocupa la fórmula.

El verdadero valor de esta funciones aparece cuando queremos crear series (vectores) de númenos enteros sucesivos lo que hacemos con una constante matricial.

En la nota sobre cálculo de vencimientos en día hábil usamos un constante matricial en esta fórmula

=MAX((C5+C4-{7;6;5;4;3;2;1;0})*(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0)))*(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6 span="">

(nótese el uso de "{" y "}" para crear la constante matricial y el ";" para indicar el sentido del vector). Esta notación tiene el problema de las definiciones regionales; en cierta definiciones se usa el "\" en lugar del punto y coma.
La función FILA supera este problema, ya que es independiente de las definiciones regionales:

=MAX((C5+C4+1-FILA(1:7))*(ESERROR(COINCIDIR(C5+C4+1-FILA(1:7);Feriados;0)))*(DIASEM(C5+C4+1-FILA(1:7);2)<6 span="">

Pero también esta notación tiene sus problemas ya que si copiamos la fórmula, la referencia cambia. Si ponemos en la celda A2 la fórmula =FILA(1:7), al copiarla a A3 veremos =FILA(2:8).

La solución es usar la función INDIRECTO de esta manera =FILA(INDIRECTO("1:7"))

Nótese que usamos comillas en la expresión "1:7", ya que INDIRECTO funciona con texto, no con valores numéricos.

La función FILAS()

He pasado una semana agitada. Todo empezó con un intimidante correo electrónico del departamento de informática que rezaba: "¡Urgente! Todos los laptops deben pasar una revisación. Traerlos de inmediato al laboratorio de informática". Todo en letras tamaño catástrofe. Sucede que algunos inocentes usuarios en la empresa habían caido en las garras del virus Ransom, ese que encripta los archivos del disco duro y exige rescate para quitar la codificación.
Tendría que haber fotografiado la cara del técnico cuando vio que tenía instalado Windows 10. Pero, ¿quién te autorizó a instalarlo?. Más que una pregunta era una acusación. Poniendo mi mejor cara de inocente le pregunté cuál era el problema. Me farfulló algo que no terminé de entender pero la sentencia fue terminante: te vamos a formatear el disco e instalar de nuevo el Windows 7, como si el disco duro fuera parte de mi organismo, carne de mi carne. Inútiles que fueron mis ruegos y súplicas pasé el resto de la semana reinstalando los programas que fueran despiadamente eliminados de mi laptop.Y toda este historia es para explicar por qué este post será mínimo pero espero que útil.

¿Recuerdan la nota sobre las funciones raramente usadas de Excel? A la lista de la nota, y de otras notas posteriores sobre las funciones CELDA, CARACTER y CODIGO, podemos agregar la función FILAS().

FILAS() da como resultado el número de filas de una referencia a un rango de filas o matriz

La utilidad de esta función es mucho mayor de lo que se desprende de la escueta información y la he utilizado en varias técnicas que aparecen en este blog.

Supongamos este ejemplo, basado en un anállisis que tuve que realiza para el departamento de mercadeo de mi organización. Tenemos una lista de órdenes de clientes en un rango que hemos definido como Tabla

Ahora supongamos que queremos calcular el número de filas en la tabla. Las técnica "tradicional" sería crear un nombre definido que se refiera a una fórmula con la función CONTARA. Pero si una de las celdas del rango esta vacía, el resultado será erróneo. La función FILAS() usada junto con la tabla soluciona este problema

La celda N3 contiene la fórmula =CONTARA(Tabla_Northwind[[#Todo],[Cliente]]) pero como la celda B5 está vacía el resultado es 19.
La celda N4 contiene la fórmula =FILAS(Tabla_Northwind[#Todo]) y dá el resultado correcto.

Si queremos calcular el porcentaje de órdenes de cada país del total de órdenes en la tabla podemos usar la fórmula =FILAS(Tabla_Northwind[#Datos])  como en la celda N2 en la imagen de abajo (y de paso vemos las ventajas del lenguaje estructurado de las tablas)

Las celdas N5 a N9 contienen la fórmula  =CONTAR.SI(Tabla_Northwind[Pais],M5)/FILAS(Tabla_Northwind[#Datos])

Al usar Tabla_Northwind[#Datos] la fila de los encabezados no es tomada en cuenta. Y por supuesto, ya que estamos usando tablas, a medida que agreguemos o eliminemos filas de la tabla, las fórmulas se adaptarán automáticamente.

Una curiosidad, no tan curiosa, es que FILAS() no crea una referencia circular también si la celda que contiene la fórmula es parte del rango de la referencia

Todo lo dicho aquí se aplica, por supuesto, a la función hermana COLUMNAS().

FILAS() y COLUMNAS() tienen dos primas cercanas, FILA() y COLUMNA() de las que hablaré en un futuro post.

Buen fin de semana.

Señalar rangos de nombres definidos - versión mejorada

En la nota anterior mostré como hacer visibles los rangos de nombres definidos en una hoja de Excel.  Uno de mis lectores me pregunta si se puede mejorar la macro de manera que cada rango se señale con un color de fondo distinto.
Para hacerlo tendremos que modificar un poco el código de la macro de la nota anterior, agregando el mecanismo para crear colores de fondo en forma aleatoria. Además tendremos que asegurarnos que el tono de los colores no oculte el contenido de las celdas.

El código modificado es el siguiente:

 Sub mostrar_nombres_dif_color()  
   Dim n As Name  
   Dim strNameStart As String  
   On Error Resume Next  
   Application.ScreenUpdating = False  
   For Each n In ActiveWorkbook.Names  
     With Range(n.RefersTo).Interior  
       .ColorIndex = Int((56 * Rnd) + 1)  
       .TintAndShade = 0.9  
     End With  
     strNameStart = Left(n.RefersTo, WorksheetFunction.Find(":", n.RefersTo) - 1)  
     With Range(strNameStart)  
       .AddComment  
       .Comment.Text n.Name  
       .Comment.Visible = False  
     End With  
   Next n  
   Application.ScreenUpdating = True  
   On Error GoTo 0  
 End Sub  

Como puede apreciarse estoy usando la expresión Int((56 * Rnd) + 1) para generar números enteros entre 1 y 56. Como alguno de estos colores son oscuros e impedirían ver el contenido de las celdas, usamos la propiedad .TintAndShade con un valor de 0.7 para obtener tonos pálidos. Si queremos tonos má claros usamos números más cercanos al 1, por ejemplo 0.85.

El resultado puede verse en esta animación

He agregado la sentencia On Error Resume Next para evitar la interrupción de la rutina si en la primer celda del rango ya existe un comentario. En ese caso el comentario original quedará y no se registrará el nombre del rango.

Como cuestión de buena práctica recomiendo no usar  On Error Resume Next tal como se ve en el código. Pero me permito esta licencia ya que se trata de una pequeña herramienta que puede resultar útil al construir modelos complejos.