Limitar el area de trabajo en una hoja Excel - Scroll Area

A veces queremos limitar la zona de trabajo en una hoja de cálculo de Excel. Por lo general hacemos esto cuando queremos ocultar de otros usuarios la posibilidad de ver cálculos o datos auxiliares que se encuentran en la zona oculta, o evitar que realicen cambios en datos o fórmulas en ese área.
Uno de los métodos es ocultar las columnas involucradas usando el menú Ocultar y luego aplicar le menú Herramientas--Proteger para evitar que otro usuario utilice el menú Mostrar. Para evitar que alguien desproteja la hoja, usamos Proteger con contraseña.

Yo personalmente utilizo otro método que tiene la ventaja, a mí entender, de definir una zona "autorizada" al usuario y "congelar" y no permitir desplazarse hacia la zona "prohibida".
Tomemos el ejemplo de la tabla de posiciones de mi nota sobre el uso de JERARQUIA y SUMAPRODUCTO

Y supongamos que queremos limitar a los usuarios al rango B5:J19 e impedir que pueda acceder a cualquier celda que no se encuentre en este rango.

Hacemos lo siguiente:

1 – Abrimos la barra Cuadro de Controles





2 – Apretamos el botón "Propiedades" para abrir la ventana de propiedades de la hoja

3 – En el cuadro ScrollArea anotamos el rango permitido, en nuestro caso B5:J19



4 – Cerramos la ventana de propiedades y la barra Cuadro de Controles

A partir de este momento solo podemos desplazarnos y efectuar cambios en el área señalada. No hay forma que podamos seleccionar ninguna celda que no esté en esta área.

Setiembre 5, 2006: ver actualización a esta entrada.

Categorías: Varios_

Technorati Tags: Excel

Calendario del Campeonato Mundial de Futbol en Excel

Los interesados en seguir las instancias del Campeonato Mundial de Futbol, pueden descargar un excelente cuaderno Excel con un calendario activo del campeonato.

Este calendario fue desarrollado por la gente de Excely.com en una hoja de cálculo, funciona solamente con fórmulas, sin macros. Se puede descargar gratuitamente aquí.

La hoja tiene un botón que permite "traducir" las leyendas a 20 idiomas! La hoja es ideal para armar pronósticos.

Felicitaciones a la gente de Excely.com

Ahhh! También pueden leer mi entrada sobre ordenar una tabla de acuerdo a varios criterios usando las funciones JERARQUIA y SUMAPRODUCTO. El ejemplo se basa en una tabla de posiciones.

Categorías: Varios_

Calculos instantaneos de valores en un rango seleccionado en MS Excel

Como ya he comentado en alguna oportunidad, este blog resume en gran parte, consultas de mis compañeros de trabajo que utilizan Excel para sus tareas cotidianas.
Uno de mis "tips" favoritos, y que pocos de mis compañeros conocen, es el uso de esta funcionalidad:

Al apretar el botón derecho del mouse, cuando este apunta a la barra de situación (inmediatamente por debajo de las pestañas de la hoja, si no ha alguna barra de por medio), se abre este menú

Estas funciones dan el resultado correspondiente a todo rango que tengamos seleccionado en el momento de abrir el menú.

Por ejemplo, si la función activa es SUMA, y tenemos seleccionado un rango de números enteros de 1 a 15, veremos en la barra inferior el resultado "120"

Si la función seleccionada es PROMEDIO, veremos el resultado "8".


Esta funcionalidad también funciona con rangos no contiguos



De la misma manera podemos utilizar la funciones MAX, MIN, Cuenta (CONTARA) y Cuenta.Num (CONTAR) .

Esta funcionalidad es muy útil para realizar cálculos "instant", sin necesidad de anotar fórmulas en la hoja.

Categorías: Varios_

Technorati Tags: Excel

Uso de la función JERARQUIA combinada con SUMAPRODUCTO - Ranking de listas con más de un criterio

A tono con el mundial de fútbol que se acerca, uno de mis lectores me pregunta como es posible organizar una tabla de posiciones usando MS Excel, de manera que si hay empate en la cantidad de puntos, la posición se defina de acuerdo a la cantidad de goles a favor. En caso de persistir la igualdad, la posición se definiría por mayor diferencia de goles. Todo esto utilizando solamente fórmulas!!
Ya hemos visto en una entrada anterior sobre el uso de la función JERARQUIA (RANK en inglés), para dar un número de orden a los miembros de una lista de acuerdo a su posición relativa. En una nota adicional sobre la función JERARQUIA (RANK) vimos que esta función tiene un problema: en caso de "empate" ambos miembros en la lista reciben el mismo número de orden. Este "problema" puede ser usado a nuestro favor para resolver la pregunta sobre la tabla de posiciones.

Supongamos esta tabla de un campeonato imaginario




Como podemos ver los equipos 4,5 y 6 comparten la cuarta posición; los equipos 7,8 y 9 comparten la quinta posición

Empezamos creando columnas auxiliares (K, L y M), para facilitar el cálculo.


En la columna auxiliar K anotamos la fórmula =JERARQUIA(J5,$J$4:$J$19) para calcular el número de orden de acuerdo a la cantidad de puntos de cada equipo.

En la columna L anotamos =SUMAPRODUCTO((J5=$J$4:$J$19)*(G5<$G$4:$G$19)) para generar un ranking entre los equipos de igual cantidad de puntos, de acuerdo a los goles a favor.
En la columna M anotamos =SUMAPRODUCTO((J5=$J$4:$J$19)*(I5<$I$4:$I$19)) para general el ranking entre los equipos con igual cantidad de puntos, de acuerdo a la diferencia de goles.
Finalmente, combinamos las tres fórmulas en esta "mega-fórmula" que anotamos en la columna A
=JERARQUIA(J5,$J$4:$J$19)+SUMAPRODUCTO((J5=$J$4:$J$19)*(G5<$G$4:$G$19))+SUMAPRODUCTO((K5=$K$4:$K$19)*(L5=$L$4:$L$19)*(M5>$M$4:$M$19))

Una vez obtenido el ranking de los equipos de acuerdo a los tres criterios (puntaje, goles a favor y diferencia de goles) podemos ordenar nuestra tabla utilizando el menú Ordenar de acuerdo a la columna del ranking (A).

Esta fórmula funciona de la siguiente manera:

- el primer miembro, la función JERARQUIA, calcula el número de orden de acuerdo al puntaje.

- El segundo miembro, la primera función SUMAPRODUCTO, calcula un número de orden interno sólo para los equipos con el mismo puntaje. Este orden interno comienza con el número cero. Si el puntaje del equipo evaluado es único, esta fórmula da cero.

- El tercer miembro, la segunda función SUMAPRODUCTO, calcula un número de orden interno sólo para los equipos que tienen el mismo puntaje y la misma cantidad de goles a favor. Si esto no se cumple, da como resultado cero.

Esta fórmula puede ser escrita en forma más compacta utilizando nombres en lugar de rangos explícitos.



Categorías: Funciones&Formulas_

Technorati Tags: JEARARQUIA, SUMAPRODUCTO

Convertir un Numero Decimal en Excel a formato hh:mm:ss

Algunos sistemas de asistencia utilizan notación decimal en los reportes que producen. Por ejemplo, el total de horas de un operario que trabajó de la 8:00 a las 17:30, aparece en el informe como 9.50. Otro operario que trabajó de la 8:00 a las 17:45, aparecerá en el informe 9.75.
Como con muchos otros sistemas de información, estos informes son exportados a Excel. Al sumar las horas trabajadas por los dos operarios, el total que aparecerá en Excel será 9.50 + 9.75 = 19.25.
Este resultado es incorrecto, ya que el total de horas trabajadas es 19 horas y 15 minutos,

Para convertir, las horas y minutos del formato decimal al formato hh:mm:ss en Microsoft Excel utilizamos la siguiente fórmula: ="Horas.minutos" en forma decimal/24
Al usar esta fórmula resultará un número de serie. Para mostrar el número de serie en el formato correspondiente, elige Número en el menú Formato (en el menú Formato, hace clic en Celdas y después, selecciona la ficha Número) y selecciona hh:mm:ss.



Si queremos convertir minutos en formato decimal a formato hh:mm:ss, aplicamos la siguiente fórmula: ="minutos en formato decimal"/1440.

Finalmente si queremos convertir segundos exhibidos en formato decimal a formato hh:mm:ss, usamos: ="segundos en formato decimal"/86400.

Los divisores (24, 1440, 86400), se deben a que Excel utiliza notación decimal efectuar cálculos de tiempo, como expliqué en la entrada fechas y tiempo en MS Excel. La unidad (1) representa un día completo, que equivale a 24 horas, a 1440 minutos y a 86400 segundos.




Categorías: Varios_



Technorati Tags: Cálculos de tiempo en Excel

Formato personalizado de números en Excel – Nota II

En la nota anterior sobre formatos de números personalizados en Excel, expliqué, brevemente, como crear formatos de números en Excel. En esta nota completaré la explicación mostrando algunos de los códigos con los cuales se crean los formatos.

Para crear formatos personalizados usamos el menú Formato de Celdas---Personalizada. En la ventanilla "Tipo" escribimos la combinación de códigos que crean el formato deseado. Estos códigos son los siguientes:

"General": el formato general de Excel

"#": muestra únicamente los dígitos significativos y no muestra los ceros sin valor.

"0" (cero): muestra los ceros sin valor si un número tiene menos dígitos que ceros en el formato

"?": agrega los espacios de los ceros sin valor a cada lado del separador decimal, para alinear los decimales con el ancho fijo del formato. También se puede utilizar ? para las fracciones que tengan un número de dígitos variable.

"%": porcentaje

[color]: determina el color del número (ver ejemplo en la entrada anterior). El color debe ser el primer elemento de la sección. Ejemplos de colores: [Negro], [Azul], [Aguamarina], [Verde], [Fucsia] [Rojo], [Blanco], [Amarillo]

"texto": para agregar texto inmediatamente después (o antes) del número, ponga el texto deseado entre comillas, como en el ejemplo de la entrada anterior.

También se pueden generar formatos condicionales que se aplicarán únicamente si coinciden con las condiciones que se hayan especificado. La condición se escribe entre corchetes y consta de un operador de comparación y un valor. Por ejemplo, el siguiente formato muestra los números iguales o inferiores a 100 en color rojo y los números superiores a 100 en color azul.

[Rojo][<=100];[Azul][>100]

En general es más eficiente utilizar el comando Formato Condicional en el menú Formato para formatos condicionales.

En la próxima nota mostraré los códigos que se utilizan para crear formatos de fechas y horas.

Categorías: Varios_

Technorati Tags: Formato personalizado de numeros

Formato personalizado de números en MS Excel.

Cuando hablamos de formato de números nos referimos a como se ve un número en una celda de Excel. Al cambiar el formato de un número en una hoja de Excel, sólo estamos cambiando lo forma en que Excel exhibe el número. El número en si mismo no cambia.
Es fácil dar un ejemplo con la forma en que Excel exhibe las fechas, como mostré en mi entrada sobre fechas tiempo en MS Excel [link]. Allí vimos que cuando vemos la fecha "9/05/2006" en una celda, Excel "ve" el número 38846 que es la cantidad de días transcurridos desde el 1/01/1900 hasta el 9/05/2006.
Excel viene provisto con una gran cantidad de formatos para números, organizada en distintas categorías:

En esta entrada nos ocuparemos de la categoría "Personalizada".
Excel permite crear formatos de números para adaptarlos a nuestras necesidades. Para hacer esto activamos el menú de formato de celdas y elegimos la categoría "Personalizada". En la ventanilla "Tipo" escribimos el código que generará el formato deseado.

La estructura general del código se compone de 4 secciones:

Formato de números positivos; formato de números negativos; formato de ceros; formato de texto.

Por ejemplo, si queremos general un formato donde aparezca la palabra "Euros" inmediatamente después del número y los números negativos aparezcan entre paréntesis, usamos los códigos:

#,##0.00 "Euros";( #,##0.00) "Euros"

Si queremos que en este formato, en caso de que el valor sea 0 (cero) aparezca en la celda la frase "sin valor", agregamos la parte de formato de ceros

#,##0.00 "Euros";( #,##0.00) "Euros";"sin valor";

Si queremos que los números negativos aparezcan en rojo, podemos agregar el código [color] en la parte de números negativos:

#,##0.00 "Euros";[Rojo]( #,##0.00) "Euros";"sin valor";

En esta tabla se pueden ver los efectos de aplicar estos formatos:

Categorías: Varios_

Technorati Tags: Formato de numeros

Cálculos de tiempo con MS Excel – Funciones para cálculos de horas

En las últimas entradas he estado hablando sobre cálculos de tiempo con MS Excel, una explicación general sobre el método de calcular fechas y horas de Excel y en la entrada más reciente sobre el problema de "tiempos negativos". En el pasado también he escrito sobre sumas de tiempo con Excel.
En esta entrada haré una breve revisión de las distintas funciones de tiempo de Excel:

AHORA() - Devuelve el número de serie de la fecha y hora actuales. Esta función no utiliza ningún argumento. La función AHORA sólo cambia cuando se realiza un cálculo en la hoja de cálculo o cuando se ejecuta una macro que contiene la función. No se actualiza constantemente. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Esto quiere decir que el resultado de AHORA() aplicado hoy en una celda con formato General es "15/05/2006 18:17".
Si queremos obtener la hora del momento, sin la fecha, podemos usar la fórmula "=AHORA()-HOY()". Para ver el resultado de esta fórmula con formato de hora debemos dar este formato a la celda: "hh:mm".

HORA(núm_de_serie) – Da como resultado la hora correspondiente al número de serie (que indica horas, minutos segundos). Por ejemplo, =HORA(0.885) da 21:00, cuando 0.885 corresponde a la hora 21:14:24

HORANUMERO(texto_de_hora) - Devuelve el número decimal de la hora representada por una cadena de texto. Por ejemplo, =HORANUMERO("21:14:24") da como resultado 0.885.

MINUTO(núm_de_serie) - Devuelve los minutos de un valor de hora. Los minutos se expresan como números enteros comprendidos entre 0 y 59. Siguiendo con nuestro ejemplo, =MINUTO(0.885) da como resultado 14.

SEGUNDO(núm_de_serie) - Funciona como MINUTO respecto a los segundos.

NSHORA(hora;minuto;segundo) - Devuelve el número decimal de una hora determinada. El número decimal que NSHORA devuelve es un valor comprendido entre 0 (cero) y 0, 99999999 que representa las horas entre 0:00:00 (00:00:00 a. m.) y 23:59:59 (11:59:59 p.m.). Por ejemplo, =NSHORA(21,14,24) da como resultado 0.885 (si la celda tiene formato General).
Lo interesante con esta función es que acepta valores aparentemente "inválidos" y los corrige. Por ejemplo, si en la fórmula anterior usamos "65" en lugar de "14" para los minutos, y usamos el formato de "h:mm AM/PM", veremos como resultado 10:05 PM, en lugar de 9:14 PM. Excel corrige automáticamente el exceso de minutos y los transforma en horas.

Categorías: Funciones&Formulas_

Technorati Tags: Fechas y Horas

Cálculos de tiempo con MS Excel – Diferencia de horas

En la entrada de ayer hablé sobre como Excel realiza cálculos de fechas y tiempo
Vimos que Excel representa las fechas como una serie de números enteros. Si trabajamos con la base del año 1900, el primer número de la serie, el 1, representa el 1 de Enero de 1900. Si usamos la base 1904, el 1 representa el 1 de Enero de 1904.
Las horas, minutos y segundos del día son representados por fracciones del número 1. De esta manera la hora 12:00 (mediodía) es representada por el número 0.5. Las seis de la tarde, 18:00, por el número 0.75.
Cuando efectuamos cálculos de fechas y tiempos, de hecho estamos trabajando con números, que Excel representa con formatos especiales como "dd-mm-yy", "h:mm:ss", o "dd-mmm-yyyy hh:mm". Cuando vemos la fecha 9-05-2006 en una celda, Excel "ve" el número 38846. Cuando queremos calcular cuantos días transcurrieron entre el 17-07-1991 y el 9-07-1992, restamos la última fecha de la primera. De hecho estamos efectuando la resta 33794-33436 = 358.
Si trabajamos con la base 1900 hay que tener en cuenta que Excel no "sabe" representar tiempos y fechas negativos.
Para aclarar este punto, mostraré un ejemplo de cálculo de lapsos entre dos horas.
Supongamos que queremos calcular el tiempo trabajado por operarios. Cuando el comienzo y el final de la jornada no caen en el mismo día, la hora del final es menor que la del principio y Excel no sabrá representar el resultado




El resultado ######## nos indica que Excel no sabe "traducir" la diferencia a formato de tiempo (tiempo negativo).
En este caso no podemos utilizar la función ABS (valor absoluto) a que el resultado sería incorrecto (ABS daría 16 horas en lugar de 8 horas).
En caso de tener que realizar este tipo de operaciones hay varias soluciones:
1 – Utilizar las horas con sus correspondientes fechas, como "08/05/2006 19:00" (ver en el archivo del ejemplo);
2 - Utilizar la fórmula que aparece en la fila 20 del ejemplo, =C20 + (C20"<"B20) - B20. En esta fórmula la expresión (C20"<"B20) puede dar como resultado 1 (verdadero) o 0 (falso). Si la hora del final es menor que la del comienzo, (C20"<"B20)=1, por lo tanto agregamos 1 a la hora del final, que es como agregar un día entero.
3 – El mismo efecto se puede conseguir con la fórmula que aparece en la fila 26 del ejemplo: =RESIDUO(C26-B26,1);


Categorías: Varios_


Technorati Tags: Fechas y tiempo en Excel

Análisis de Tablas de Datos con MS Excel – Análisis de Sensibilidad

Cuando hablo de análisis de sensibilidad me refiero a como influyen cambios en determinados datos en el resultado de algún modelo de cálculo construido en Excel.
Excel dispone de varias herramientas como el comando Buscar Objetivo (Goal Seek) y el Solver.
Una herramienta menos utilizada, según mi experiencia, es el comando Tabla del menú Datos. Esta herramienta puede ser muy útil y ahorrar mucho tiempo.
Expliquemos esto con un ejemplo del uso de tablas de datos en MS Excel. Supongamos que nuestro modelo mide la tasa de ganancia neta, dados el monto de las ventas sabiendo que los gastos variables representan el 35% de las ventas y los gastos fijos son 400 mil (qué moneda usar depende de ustedes, por supuesto).

Una tabla de análisis de sensibilidad nos muestra, por ejemplo, cuál será la tasa de ganancia neta de acuerdo a variaciones en el monto de las ventas y en el porcentaje de los gastos variables:



Excel nos permite construir esta tabla automáticamente usando el comando Tabla en el menú Datos



En nuestro ejemplo mostramos el caso de una Tabla de Datos de doble entrada (Two-Input), que enseguida explicaremos. Por supuesto existe también el caso de Tabla de Datos de entrada simple (One-Input Data Table).

Para generar nuestra Tabla de Análisis de Sensibilidad necesitamos un modelo de cálculo como en nuestro ejemplo.
Luego creamos una matriz para los resultados con el siguiente diseño:
1 – la fórmula que da el resultado aparece en el ángulo superior izquierdo de la matriz (o una referencia a la celda que la contiene, como en nuestro ejemplo)

2 – en la fila superior de la matriz anotamos los distintos valores para una de las variables de nuestro modelo (en nuestro ejemplo, el monto de las ventas);
3 – el la columna izquierda de la matriz anotamos los distintos valores de la segunda variable (en nuestro ejemplo, el porcentaje de los gastos variables respecto a las ventas);
4 – Seleccionamos toda la matriz (en nuestro ejemplo el rango B12:G17)

y activamos el menú Datos--->Tabla

5 – en la ventanilla "celda de entrada (fila)" señalamos la celda que contiene el valor de la variable correspondiente (en nuestro caso B4, el monto de las ventas)

6 - en la ventanilla "celda de entrada (columna)" señalamos la celda que contiene el valor de la variable correspondiente (en nuestro caso C6, el porcentaje de los gastos variables);
7 – apretamos "aceptar" y Excel calculará automáticamente los valores para cada una de las intersecciones en la matriz.
8 - La fórmula, o la referencia a ella, que aparece en la celda superior izquierda de la matriz puede causar confusión. Por lo tanto conviene ocultarla. Una de las formas de hacerlo es formarla el font con el mismo color del celda, de manera que se torne invisible.

Si usamos Tablas de Datos con una sola entrada, marcamos sólo la variable que corresponde a la fila (o columna).

Categorías: Manejo de Datos_

Technorati Tags: Tabla de Datos, Analisis de Sensibilidad