Funciones matriciales de rango – Un ejemplo

En el pasado en mis notas sobre fórmulas matriciales mencionaba que las funciones matriciales de Excel también pueden usarse para obtener el resultado de un cálculo en varias celdas simultáneamente, lo que llamamos fórmulas matriciales "multicelulares".
En una nota más reciente sobre cómo extraer elementos únicos de un rango en Excel mencionaba el uso de la función UNIQUEVALUES del complemento MoreFunc de Laurent Longre.

Menciono esto porque en esta nota mostraré cómo combinar todos estos ingredientes para resolver una consulta de uno de mis lectores.

La situación es la siguiente: en una hoja de Excel tenemos esta tabla



En una segunda tabla queremos poner debajo del nombre del cliente a que empresa pertenece



Como no puede ser de otra manera, queremos que esta segunda tabla sea dinámica. Es decir, si cambiamos el nombre del cliente en el encabezamiento, los valores deberán adaptarse automáticamente. Lo mismo si cambiamos la ubicación de las "X" en la primer tabla.

Nuestro primer paso consiste en crear una fórmula matricial que de como resultado un rango de valores.
Debemos prestar atención al hecho que en la primer tabla, los clientes están ordenados en columna y las empresas en filas, es decir, a la inversa de lo que queremos obtener en la segunda tabla.
Empezamos por crear la fórmula matricial que nos de los nombres de las empresas por cliente donde haya una X y si no la hay, un espacio en blanco. Tomando como ejemplo el primer nombre de la segunda tabla, LUIS, escribimos esta fórmula matricial

={SI(I5:N5="X";I2:N2;"")}

simultáneamente en el rango B3:E3, presionado al mismo tiempo Ctrl+Mayúsculas+Enter



Para "girar" el rango 90 grados, usamos la función TRANSPONER, que también es matricial

={TRANSPONER(SI(G5:L5="X";G2:L2;""))}



Nuestro próximo paso es convertir nuestro modelo en dinámico. Para esto creamos nombres que contengan los rangos de los clientes:

JUAN ='Hoja1 (3)'!$G$3:$L$3
LUCAS='Hoja1 (3)'!$G$8:$L$8
LUIS ='Hoja1 (3)'!$G$5:$L$5
MARIO='Hoja1 (3)'!$G$6:$L$6
PEDRO='Hoja1 (3)'!$G$7:$L$7
ROSA ='Hoja1 (3)'!$G$4:$L$4
SONIA='Hoja1 (3)'!$G$10:$L$10
VICTOR='Hoja1 (3)'!$G$9:$L$9

Estos nombres nos servirán para crear una referencia dinámica en nuestra fórmula con la ayuda de la función INDIRECTO. Reemplazamos nuestra fórmula anterior por

{=TRANSPONER(SI(INDIRECTO(B$2)="X";$G$2:$L$2;""))}



Ahora, si cambiamos el nombre en el encabezamiento, los resultados cambiarán automáticamente.

Nuestra solución es casi perfecta. Nos falta eliminar los espacios en blanco entre los nombres de las empresas. Para esta tarea usaremos la función UNIQUEVALUES del complemento MoreFunc ya mencionado. Esta función también es matricial y debe ser introducida pulsando al mismo tiempo Ctrl+Mayúsculas+Enter

{=UNIQUEVALUES(TRANSPONER(SI(INDIRECTO(B$2)="X";$G$2:$L$2;"")))}

Nuestro modelo se ve ahora así



que es lo que queríamos obtener.

Technorati Tags: MS Excel

Extraer elementos únicos de un rango en Excel

Ya hemos visto cómo generar una lista de elementos únicos a partir de un rango en una columna en Excel. Para esta tarea usamos Datos—Filtro--Filtro Avanzado—Elementos Únicos.
El problema surge cuando el rango incluye más de una columna. En estos casos no podemos usar Filtro Avanzado.
Dado que últimamente he recibido varias consultas sobre este tema, presentaré aquí dos soluciones posibles.

Supongamos esta situación




En este rango tenemos 6 elementos, pero sólo 4 elementos únicos.

Para generar un lista de elementos únicos podemos usar la función UNIQUEVALUES que forma parte del complemento MoreFunc que tantas veces he recomendado y que recomiendo descargar e instalar.
Una vez instalado el complemento, seleccionamos la función con el asistente de funciones seleccionando la categoría MoreFunc




Dado que esta función da como resultado una matriz, pero un una celda sólo podemos ver un resultado, la combinamos con la función INDICE para exponer todos los elementos de la matriz en un rango de celdas. La fórmula, en nuestro ejemplo, es

=INDICE(UNIQUEVALUES($A$1:$C$2);FILAS($5:5))

que ponemos en la celda A5 y copiamos hasta la celda A9




También podemos usar esta variante con la función FILA en lugar de la función FILAS

=INDICE(UNIQUEVALUES($A$1:$C$2);FILA()-4)

Este complemento incluye también la función COUNTDIFF que da como resultado el número de elementos únicos en el rango




Otra alternativa es copiar el código de la función UNIQUEITEMS de John Walkenbach. Esta función también permite contar el número de elementos únicos en un rango y, combinándola con la función TRANSPONER, generar una lista de elementos únicos





La fórmula con la función TRANSPONER debe ser introducida como fórmula matricial (pulsando al mismo tiempo Ctrl+Mayúsculas+Enter)

Technorati Tags: MS Excel

Es posible jubilarse a los 40?

Hace unas semanas atrás publiqué en este una entrada sobre como hacerse millonario con Excel (y también advertía allí que no tengo la menor idea de cómo hacerlo, con o sin Excel).

Por una coincidencia me encuentro ayer una nota de Dick Kusleika en Daily Dose of Excel en la que menciona un artículo de John Walkenbach, How to retire at age 40 (cómo jubilarse a los 40), quien a su vez menciona un artículo publicado en MoneyCentral.msn.com sobre el tema.

Al día siguiente John Walkenbach publicó en su blog J-Walk Blog el modelo en Excel basado en las premisas de la nota de MoneyCentral.

El modelo que propone Walkenbach tiene un mecanismo de "autoajuste". La idea es que cuando el monto anual de los intereses sobre el ahorro igualan el ingreso anual, ese el momento en que podemos retirarnos (y seguir disfrutando del ingreso corriente al cual estamos acostumbrados).
Este enfoque ignora la existencia de la inflación y de la erosión del ingreso real. También ignora la existencia de impuestos al ahorro, que existen en varios países. Es decir, el modelo puede encontrar su punto de equilibrio en un ingreso muy bajo.

Por eso me parece más acertado este modelo para calcular la edad de jubilación en el cual nos fijamos como meta el ingreso anual con el cual queremos vivir cuando nos retiremos y luego hagamos el cálculo cuando podremos convertir nuestro sueño en realidad.

En definitiva, he agregado al modelo de Walkenbach los parámetros inflación anual promedio, impuestos al ahorro y jubilación anual deseada. La inclusión de estos parámetros hace el cálculo más realista.

Por ejemplo, en mi modelo, tomando en cuenta una tasa de inflación del 3%, un interés anual del 7% sobre la inversión y un impuesto del 10% sobre los intereses, si empezamos a ahorrar a los 20 y queremos llegar a una jubilación de 60 mil anuales, tendremos que ahorrar hasta los 55 años.

Y, además, si alguien conoce algún joven de 20 años que esté dispuesto a ahorrar el 20% de su ingreso, me lo presenta por favor. Yo no conozco ninguno.

Technorati Tags: MS Excel

Ordenar texto en Excel con fórmulas.

Excel permite ordenar datos, ya sean texto o número, con facilidad y flexibilidad usando el menú Datos-Ordenar.
Pero hay situaciones en las cuales queremos realizar la tarea con fórmulas, como me consultaba uno de mis lectores hace unos días atrás.
La solución consiste en usar la función CONTAR.SI con un pequeño truco. Empecemos por plantear la situación. Supongamos esta lista de nombres que queremos ordenar alfabéticamente con fórmulas



El primer paso consiste en crear una columna auxiliar con esta fórmula:

=CONTAR.SI($A$2:$A$11;"<="&A2)

que copiamos a todo el rango de la columna B

Es importante notar que el segundo argumento de la función está formado por el texto "<=" concatenado con el operador "&" a la celda correspondiente de la columna A.



Como ven la función CONTAR.SI hace aquí las veces de la función JERARQUIA, que sólo funciona con números, no con texto.

El segundo paso consiste en crear una tabla con dos columnas. Una columna auxiliar donde ponemos los números de posición en forma ordenada y una segunda columna donde aparecerán los nombres. En esta columna ponemos esta fórmula

=INDICE($A$2:$A$11;COINCIDIR(D2;$B$2:$B$11;0))

Usamos INDICE y COINCIDIR ya que los números auxiliares en la tabla original están a la derecha de los nombres. Si estuvieran a la izquierda podríamos usar la función BUSCARV.



Como ven, tenemos nuestra lista ordenada en la tabla D1:E11.

Esta técnica tiene un serio inconveniente. Si un nombre aparece repetido, obtendremos un resultado #N/A



Para superar este problema creamos una segunda columna auxiliar. En esta columna ponemos esta fórmula relacionada a la primera columna auxiliar

=JERARQUIA(B2;$B$2:$B$11;2)+CONTAR.SI($B$2:B2;B2)-1

Ya hemos mostrado esta técnica para lograr "desempates" usando la función JERARQUIA.



Como ven, el primer Daniel recibe el número de orden 5 y el segundo, 6.

Todo lo que nos queda por hacer es aplicar la misma fórmula INDICE y COINCIDIR que usamos más arriba

Technorati Tags: MS Excel

Área de impresión dinámica en Excel, con macros.

La consulta que dio origen a la nota anterior sobre cómo determinar un área de impresión en forma dinámica, pedía hacerlo con macros.

Así que para saldar mi deuda con el lector mostramos aqu[i una de las formas de hacerlo con Vba.

Volviendo a nuestro ejemplo anterior,

podemos usar las celdas G2 y G3 con esta macro:

ActiveSheet.PageSetup.PrintArea = [G2] & ":" & [G3]

Cambiando los valores de las celdas G2 y G3, al correr la macro cambiará la selección del área de impresión.

Technorati Tags: MS Excel

Área de impresión dinámica en Excel, sin macros.

En mi última nota sobre nombres en Excel vimos que Excel crea nombres "reservados" para ciertas funciones, como determinar el área de impresión.
En respuesta a una consulta, vamos a mostrar cómo crear un área de impresión dinámica en una hoja, sin usar macros.

Como vimos, al determinar el área de impresión (Archivo-Configurar Página-Hoja), Excel crea el nombre local Área_de_impresión




Este nombre contiene un rango estático, pero podemos convertirlo en dinámico con esta relativamente simple técnica como mostramos en este ejemplo:

1 – definimos dos celdas que contendrán las direcciones de los vértices del rango que queremos imprimir. Por ejemplo en nuestro ejemplo el vértice superior izquierdo será la celda A1 y el vértice inferior derecho E15. En la celda G2 ponemos el valor A1 y en la celda G3 el valor E15



2 – Abrimos el diálogo de definición de nombres



y cambiamos la definición del nombre Área_de_impresión por la fórmula =INDIRECTO(CONCATENAR($G$2;":";$G$3))



Ahora podemos probar cambiar los valores en las celda G2 y G3 y veremos como el área de impresión va cambiando (veremos una línea partida delimitando el área seleccionada).

Technorati Tags: MS Excel

Tabla de Posiciones para campeonato de Fútbol – ampliación

Hace ya un tiempo que varios de mis lectores piden que amplíe el números de equipos en la tabla de posiciones en Excel para un campeonato de fútbol.

La nueva versión de la tabla de posiciones permite la participación de hasta 40 equipos.

Hay otros pedidos como adaptar la tabla para un campeonato de rugby y agregar el manejo de estadísticas.

En la medida que disponga de tiempo libre intentaré adaptar la tabla a un campeonato de rugby, en especial después del éxito de Los Pumas en el torneo mundial en Francia.

Un buen fin de semana para todos!

21-11-2007 Actualización: acabo de reemplazar el archivo, que tenía una falla, por uno corregido

10-12-2007 Actualización: nuevamente he reemplazado el archivo después de descubrir otro "bug", gracias al lector Juán José G.

Technorati Tags: MS Excel

Una vejez tranquila con Excel

Ayer hablamos del manejo de las finanzas familiares con Excel. Y como ya sabemos que millonarios no seremos, veamos si podemos asegurarnos una vejez tranquila.

Bien, tal como decía ayer, no se si podremos asegurarnos una vejez tranquila. Con Excel o si él. Pero sí podemos hacer algunos cálculos que tal vez nos ayuden en la toma de decisiones estratégicas en los que a nuestros gastos se refiere.

Veamos el siguiente escenario. Juan y José, ambos de 25 años de edad, tienen por delante 40 años de trabajo hasta llegar a la edad de retiro,
Ambos deciden ahorrar 500 pesos por mes durante 15 años. José decide empezar a ahorrar desde ese mismo momento y dejar de ahorrar a los 40 años. Juan decide esperar hasta los 50 años y entonces empezar a ahorrar, con lo cual terminará de ahorrar a los 65 años, la edad de la jubilación.
¿Cuál será la situación de cada uno de ellos al llegar a los 65 años de edad?

Construimos un pequeño modelo en Excel

En la celda B8 (Total a los 65 años) ponemos esta fórmula

=VF($B$1/12;B7*12;-$B$2)*(1+$B$1)^($B$3-B6-B7)

que copiamos también en C8

La primer parte de la fórmula usa la función VF (valor futuro) para calcular la suma que acumularemos al cabo de 15 años de ahorro. La segunda parte calcula la acumulación de intereses sobre la suma en los años que corren desde que terminamos de ahorrar hasta la jubilación.

Como podemos ver, a José le espera una vejez más tranquila con 450.000 pesos ahorrados, mientras que Juan ha ahorrado sólo 134.000 pesos. Y esto a pesar que ambos han depositado durante 15 años la misma suma: 90.000 pesos (500 pesos X 12 meses X 15 años).

La diferencia se debe a que José ha dispuesto de 25 años adicionales para sumar intereses sobre la suma ahorrada durante 15 años. Esta es la fuerza del interés compuesto.

Ahora podemos usar la funcionalidad Datos-Tabla, para crear un análisis de sensibilidad y ver cuál sería la situación de cada uno de nuestros personajes con distintas tasa de interés y cuotas de ahorro:






Sobre cómo crear tablas de sensibilidad con Excel pueden leer mi nota Análisis de Tablas de Datos con MS Excel – Análisis de Sensibilidad.


Como dicen los gringos: "Time is money".

Technorati Tags: MS Excel

Como hacerse millonario con Excel

Bien, el título de la nota no es muy exacto que digamos. En realidad no tengo la menor idea de cómo hacerme millonario ni con Excel ni con ninguna otra herramienta. Sucede que hace unos días atrás, hablando con una amiga sobre ahorro y manejo de finanzas domésticas, traje a colación un ejemplo que había visto en algún sitio.
Le decía a mi amiga que si fuera capaz de ahorrar 50 pesos por mes (o dólares, o euros o la moneda que ustedes prefieran), al cabo de 35 años (al llegar a los 65) dispondría de la nada despreciable suma de 190.000 pesos (cálculo hecho con una tasa de interés del 10% anual). Si hacemos un cálculo más realista, con una tasa de interés del 5%, obtendríamos la bastante atractiva suma de 57.000 pesos.
Excel nos permite realizar estos cálculos con facilidad. Si nos gusta trabajar duro podemos crear una tabla con 420 filas (35 años de ahorro * 12 meses) como esta:




La celda B2 está ligada a la celda E1 y a partir de la celda B3 ponemos esta fórmula:
=B2*(1+$E$2/12)+$E$1

Luego miramos el resultado en la celda B421 (la cuota número 420) y vemos el resultado: 56.804,62



Pero mucho más fácil es usar alguna de las funciones financieras nativas de Excel. En nuestro caso usamos VF (valor futuro). Esta función da como resultado el valor futuro de una inversión basándose en pagos periódicos constantes y en una tasa de interés constante.



Pero sucede que no todo en la vida son números y hay otras cosas que tienen valor para nosotros. Por ejemplo, gastarme 50 pesos por mes en el pub tomando unos tragos con los amigos.
Miremos las cosas de otra manera. Acabamos de ver cual es el valor futuro de ahorrar 50 pesos por mes durante 35 años. ¿Cuál es el valor actual de ese flujo de ahorro? Para esto podemos usar la función VA, valor actual. En nuestro ejemplo, con una tasa del 5%, nos dará como resultado un valor actual de 9.900 pesos



Visto así ya no parece tan tremendo renunciar a los 57.000 pesos dentro de 35 años para pasarla bien con los amigos hoy. Además, ¿quien nos asegura que viviremos otros 35 años?

Technorati Tags: MS Excel

Algo más sobre nombres en Excel

Supongamos una hoja de un cuaderno Excel con la siguiente tabla



Si abrimos el diálogo de nombres (Insertar-Nombres-Definir), veremos que no hay ningún nombre definido



Empezamos por definir un nombre que contiene el rango B2:B13 (que llamamos "ventas")



Si abrimos ahora el diálogo Insertar-Nombres-Definir veremos el nombre que acabamos de crear



Digamos que queremos ver en qué meses las ventas fueron inferiores a 70.000, para lo cual aplicamos Autofiltro



Ahora queremos imprimir nuestro reporte, para lo cual definimos algunos parámetros con el menú Configurar Página-Hoja



Ahora volvemos a abrir el menú Insertar-Nombres-Definir



Vemos ahora que Excel a creado dos nombres que corresponden a las definiciones de impresión que hemos establecido: Área_de_impresión y Títulos_a_imprimir.

Estos nombres han sido creados como nombres "locales", es decir, son válidos sólo para la Hoja1 (en nuestro caso). Si abrimos el diálogo Insertar-Nombes-Definir en la Hoja2, sólo veremos "ventas".

¿Son estos todos los nombres en nuestro cuadernos? Excel no tiene un método nativo para exhibir todos los nombres definidos en el cuaderno. Para ver todos los nombres podemos usar una macro como la siguiente (sugerida por John Walkenbach)

Sub ListAllNames()
Row = 1
For Each n In ActiveWorkbook.names
Cells(Row, 4) = n.Name
Cells(Row, 5) = " " & n.RefersTo
Row = Row + 1
Next n
End Sub

o usar el excelente complemento Name Manager, creado por Jan Karle Pieterse,.



Como vemos, Excel ha creado el nombre Hoja1!_FilterDatabase que contiene el rango de la tabla a la que hemos aplicado Autofiltro. Este es un nombre "oculto", es decir, no parece en la lista de nombres (Insertar-Nombres-Pegar). Excel crea nombres ocultos en todo tipo de tareas, por ejemplo impresión como hemos visto, cuando usamos el Solver, etc.

Volviendo a Hoja1!_FilterDatabase, si agregamos datos a la tabla y volvemos a aplicar Autofiltro, veremos que Excel cambia el rango del nombre para adecuarlo a la nueva situación.

Un uso interesante, pero que hay que ejercer con cautela, consiste en reemplazar manualmente el rango de estos nombres. Esto es relevante, por supuesto, a los nombres que aparecen en el diálogo de Nombres-Definir.

Por ejemplo, el lector Carlos Clemente me envía una hoja para calcular tablas de amortización (que les recomiendo descargar y analizar). En esta hoja, Carlos reemplaza el rango del nombre Área_de_impresión por una fórmula, lo que le permite definir el área de impresión en forma dinámica de acuerdo a la cantidad de pagos del préstamo. Esto es necesario, ya que si reducimos el número de pagos y por ende el número de filas a imprimir, Excel seguirá imprimiendo el área mayor que haya sido impresa con anterioridad.
Otro característica interesante en el modelo del amigo Carlos es el uso de Formato Condicional para ocultar las filas sin datos.

Como vemos, el uso de nombres no sólo nos permite simplificar la interpretación de las fórmulas y crear rangos dinámicos, sino también afectar el comportamiento de Excel, como en el caso del área de impresión (o de Títulos a Imprimir).




Technorati Tags: MS Excel

Comentarios y consultas en el blog

Todo deleite tiene su precio, o como dicen los americanos, "no free meals". De vuelta de las vacaciones me he encontrado con más de 50 mails esperando respuesta y más de 20 comentarios que deben ser moderados. En las tres semanas que han pasado desde mi regreso he logrado moderar todos los comentarios y responder a parte de los mails.
Dado que también debo ocuparme de mi trabajo (como decimos los argentinos: "hay que parar la olla"), tomará su tiempo hasta que pueda ponerme al día con todos. Es más, supongo que algunos mails quedarán sin responder.

Esta es una buena oportunidad para hacer algunas aclaraciones en lo que respecta a mails y comentarios.

Los comentarios están ligados a una nota específica y su cometido es, como su nombre lo dice, comentar o aportar algo sobre la nota. Los comentarios son uno de los indicadores más importantes de la relevancia y del éxito del blog.

Cada vez veo más casos de lectores que usan el comentario como medio de contacto conmigo o para plantear consultas fuera del contexto del tema de la nota.
No tengo dudas que todo está hecho con las mejores intenciones. Pero consultas específicas, más aún sino están ligadas al tema de la nota, deben ser dirigidas a mi dirección electrónica: jorgedun@gmail.com.

En cuanto a las consultas, estas pueden ser clasificadas en tres categorías. Ciertas consultas se limitan a algún problema específico del tipo "como hago para realizar una búsqueda de acuerdo a dos criterios" o "cómo usar la función SI con más de 7 condiciones". Este tipo de consultas han dado origen a muchas de las notas que he publicado en este blog.

Otro tipo de consultas van más lejos. Por ejemplo, "tengo un cuaderno con 40 hojas con datos de regiones, meses, agentes de ventas y montos. Cómo hago para consolidar las hojas de acuerdo al criterio que elija el usuario y que produzca un informe con gráfico incluido".
Este tipo de consultas tocan muchas veces el límite de la consulta profesional. Sin embargo ha habido casos en los cuales les he dedicado muchas horas sin esperar ninguna recompensa económica, como el caso del modelo para un campeonato de fútbol. Este modelo se originó en un pedido de una escuela de fútbol sin fines de lucro manejada por voluntarios en un barrio pobre de Santiago de Chile.

Hay otras que van aún más lejos: "por favor, envíame un modelo para organizar la contabilidad de mi pequeña empresa que acabo de lanzar esta semana. Mi mail es XXX". Por lo general no respondo a este tipo de pedidos o, si la consulta está en el marco de mis conocimientos y tiempo disponible, envío un presupuesto tentativo del proyecto.

En resumen: gracias por los comentarios, gracias por las consultas. Como digo más arriba, estos son los indicadores más importantes del éxito del blog. Más que el número de visitantes diario (que el 25 de octubre último supero los 1500 visitantes diarios por primera vez). Y, por favor, usemos el comentario como herramienta para enriquecer la nota o para consultar temas relacionados con la nota. Toda otra consulta puede ser enviada vía mail.

Technorati Tags: MS Excel

Medir fracciones de segundos en Excel (décimas, centésimas, milésimas)

Uno de mis lectores me consulta cómo hacer cálculos de diferencia de tiempos en Excel tomando en consideración fracciones de segundos. Es decir, expresar los tiempos con décimas, centésimas y/o milésimas de segundos.

El formato de Excel para expresar fracciones de segundos es

hh:mm:ss,000

Este es un formato personalizado y debemos definirlo usando el diálogo Formato de Celdas




Dependiendo de las definiciones del sistema, la coma en "ss,000" debe ser reemplazada por un punto.

Digamos que tomamos los tiempos de una competición de natación. Para calcular la diferencia de tiempos empezamos por dar el formato adecuado a los rangos que contendrán los tiempos (en nuestro caso mm:ss,000, es decir minutos, segundos y fracción de segundo); luego ponemos los tiempos y calculamos la diferencia



Un detalle interesante de notar es el siguiente:



A pesar de haber escrito en la celda 01:24,140, la barra de las fórmulas muestra 12:01:24 a.m. Esto no tendría gran importancia si no fuera por el siguiente hecho:

- seleccionamos la celda B2 de nuestra tabla, que contiene el tiempo del primer nadador
- ahora presionamos F2 para editar el contenido de la celda
- inmediatamente presionamos Enter, cuidándonos de no cambiar nada en la celda



El resultado es que Excel, a pesar del formato que hemos dado, cambia el contenido de la celda a la hora del día.

La única explicación que le encuentro a este fenómeno es que si bien Excel usa las fracciones de segundos para los cálculos, no los expone en la barra de fórmulas. Por esto al editar el contenido de la celda, las fracciones de segundo se pierden.

La conclusión es que cuando usamos Excel para cálculos que incluyen fracciones de segundos, debemos tener mucho cuidado cuando editamos las celdas.
Otra conclusión importante es que la máxima precisión posible con Excel es de milésimas de segundo. Si agregamos un cero más al formato mostrado más arriba, por ejemplo mm:ss,00000, Excel seguirá exhibiendo los resultados hasta la precisión de milésimas de segundo como con mm:ss,000.

Una solución para estos problemas es usar una función definida por el usuario (UDF) como la propuesta por Doug Jenkins en un comentario a una nota en Daily Dose of Excel:

Function ETime(Hours As Double, Mins As Double, Secs As Double) As Double
ETime = ((Secs / 60 + Mins) / 60 + Hours) / 24
End Function

Esta función permite usar fracciones de segundos más precisas que las milésimas de segundo.

Technorati Tags: MS Excel

Atajos de teclado en Excel

Como soy un ferviente usuario de estos atajos (también llamados métodos abreviados), ya en el pasado he publicado una nota sobre Métodos abreviados en MS Excel para aplicar bordes a celdas .
En el último setiembre David Gainer publicó una tabla con todos los atajos de teclado de Excel.
No todos estos atajos tienen el mismo grado de utilidad o practicidad.
Algunos ejemplos:

- Ctrl + F1 abrir Panel de Tareas

- Shift + Alt + F1 agrega una hoja al cuaderno

- Ctrl + 1 abre el diálogo de formato de celdas

- Shift + Ctrl + 1 da formato decimal con posiciones después del punto

- Ctrl + 5 produce efecto de tachado al contenido de la celda

- Ctrl + 9 oculta la fila

- Ctrl + ' copia la celda superior y queda en estado de edición

- Ctrl + - abre el diálogo de eliminar celdas

- Alt + = inserta la función SUMA

- Shift + Ctrl + = abre el diálogo Insertar Celdas

- Ctrl + Z rehacer

- Ctrl + Y rehacer borrar

Technorati Tags: MS Excel

Rangos tridimensionales en Excel (3D ranges)

En la nota anterior sobre CONTAR.SI con rangos en varias hojas de Excel mencionábamos los rangos tridimensionales (rangos 3D).
Rangos tridimensionales son aquellos que se extienden a más de una hoja. Supongamos un cuaderno con tres hojas, donde cada hoja contiene datos de ventas de los agentes de una empresa. Cada hoja contiene los datos de un año determinado. Por ejemplo, la tabla de la Hoja1 se ve así:

La Hoja2 y la Hoja3







Si queremos sumar el total del Agente 1 para los años 2002 (Hoja1), 2003 (Hoja2) y 2004 (Hoja3) podemos usar esta fórmula:

=SUMA(B2;Hoja2!B2;Hoja3!B2)

Pero en lugar de usar referencias separadas para cada rango (la celda B2 en cada hoja, en nuestro ejemplo), podemos usar un rango tridimensional:

SUMA(Hoja1:Hoja3!B2)

De la misma manera, si queremos sumar las ventas de todos los agentes a lo largo de los tres años, usamos la fórmula

=SUMA(Hoja1:Hoja3!B2:B6)

O el promedio de ventas de los agentes a lo largo de los tres años

=PROMEDIO(Hoja1:Hoja3!B2:B6)


No todas las funciones pueden ser usadas con rangos tridimensionales. De acuerdo a la ayuda de Excel, las funciones que permiten el uso de rangos 3D son:
SUMA, PROMEDIO, PROMEDIOA, CONTAR, CONTARA, MAX, MAXA, MIN, MINA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, DESVESTPA, VAR, VARA, VARP y VARPA.
A juzgar por un artículo del año 1999 del Excel Experts E-letter (EEE) publicado por David Hager, hay más funciones que aceptan rangos 3D que las mencionadas por la ayuda de Excel. Entre ellas: PERCENTIL, CUARTIL, K.ESIMO.MENOR, K.ESIMO.MAYOR, JERARQUIA, MEDIANA, MEDIA.ACOTADA, COEFICIENTE.ASIMETRIA, Y, O, DESVPROM, DESVIA2, SUMA.CUADRADOS.


Como con todo rango en una fórmula, este puede ser introducido manualmente, escribiendo cada uno de los términos, o "apuntando" al rango con el mouse.
En este caso, empezamos escribiendo el nombre de la función



Luego, manteniendo la tecla Mayúsculas (Shift) apretada señalamos con el mouse la pestaña de la última hoja en nuestro rango



Finalmente marcamos el rango de celdas



Los rangos tridimensionales no pueden ser usados con fórmulas matriciales. Es posible hallar muchas UDF's (funciones definidas por el usuario) en la red que dan respuesta al uso de rangos tridimensionales en fórmulas matriciales o funciones que no usan este tipo de rangos.

Mi favorito es el complemento MOREFUNC.XLL de Laurent Longre, ya mencionado en mi nota anterior, y que puede descargarse aquí.
Este complemento trae varias funciones que trabajan con rangos 3D y amplían su uso más allá de la capacidad de las funciones nativas de Excel:

COUNTIF.3D: la función CONTAR.SI con rangos 3D

THREED: convierte un rango 3D en un rango simple, permitiendo de esta manera el uso de rangos tridimensionales en fórmulas matriciales.

Por ejemplo, si queremos contar todos los valores mayores a 5000 en las tres hojas de nuestro ejemplo, al usar la fórmula

=CONTAR.SI(Hoja1:Hoja3!B2:B6;">5000")

el resultado es #¡VALOR!

En cambio usando =COUNTIF.3D(Hoja1:Hoja3!B2:B6;">5000") obtenemos el resultado "10".

Para usar estas fórmulas debemos primero instalar el complemento. Luego podeemos usar el asistente de funciones



y seleccionar la función

Technorati Tags: MS Excel