Cálculo manual y automático en Excel.

Hoy un compañero de trabajo vino a verme totalmente alarmado. Después de recuperar el aliento me cuenta que en "su Excel" es decir, la hoja en la llevaba trabajando varias horas, los resultados no cambiaban a pesar de que había cambiado los datos.
Para ponerlo con un ejemplo supongamos esta hoja donde en la celda C2 tenemos la fórmula A2*B2



Cambiamos la cantidad en la celda A2 a 100. El resultado en la celda C2 debería cambiar a 25000, pero



Sin embargo sí hay un cambio. En la parte inferior izquierda de la hoja aparece "Calcular". Este mensaje significa que Excel se encuentra en situación de cálculo manual y para recalcular las fórmulas de la hoja debemos apretar F9.

Para saber cuál es el método de cálculo del cuaderno con el que estamos trabajando podemos fijarnos en Herramientas-Opciones



Todo esto es seguramente "noticias de ayer" para la mayoría de mis lectores. Pero existen no pocos aspectos menos conocidos del método de cálculo de Excel que en ciertas circunstancias pueden ocasionar problemas, también a usuarios experimentados.

Excel tiene un mecanismo de cálculo muy sofisticado. Este mecanismo minimiza el tiempo de recálculo recalculando sólo las celdas que necesitan ser recalculadas. Sin extendernos en detalles técnicos podemos decir que hay algunas excepciones a esta regla. Por ejemplo, las funciones volátiles son calculadas con cada cambio en la hoja, también si este cambio no afecta a la fórmula en cuestión.
Cuando Excel está en modo de cálculo manual, ninguna celda es recalculada, tampoco aquellas que contienen fórmulas con funciones volátiles.

El método de cálculo es determinado por el método del primer cuaderno que abrimos al iniciar una sesión de Excel. Este es un detalle crítico: todo cuaderno que abramos después, no importa cuál sea el método de cálculo con que fue guardado, funcionará con el método de cálculo del cuaderno que inicio la sesión corriente de Excel.

Al cambiar el método de cálculo de un cuaderno, con el menú Herramientas-Opciones, se cambia el método de todos los cuadernos abiertos en esa sesión de Excel.

También después de cerrar todos los cuadernos de una sesión, al abrir un nuevo cuaderno el método de cálculo será el de último cuaderno que hayamos guardado. La excepción a esta regla es si creamos un cuaderno a partir de una plantilla.

Cuando trabajamos en situación de cálculo manual podemos pulsar la tecla F9 para recalcular todos los cuadernos abiertos en la sesión o Mayúsculas+F9 para recalcular sólo la hoja activa.
Otra combinación posible es Ctrl+Alt+F9 que realiza un recálculo completo (full calculation), es decir, de todas las fórmulas aún de aquellas cuyas variables no han cambiado.

También podemos usar F9 para momentáneamente el resultado de una fórmula o parte de ella, en la barra de fórmulas. Por ejemplo, activamos la celda C2 en nuestro ejemplo y seleccionamos la operación



Al apretar F9 vemos el resultado en la barra de fórmulas

Technorati Tags: MS Excel

Descargas de archivos de JLD Excel en Castellano

Últimamente varios lectores me comentan sobre dificultades para descargar archivos usando los enlaces en las notas del blog.
Estas dificultades son más aparentes que reales y se deben a que el sitio donde alojo los archivos ha cambiado la interfaz haciéndola, sin ninguna intención supongo, difícil para el usuario promedio.
El sitio en cuestión, ESnips, está usando una nueva tecnología de publicación de documentos en la WEB llamada ScribD.

Cuando pulsamos un enlace en el blog para descargar un archivo se abre una página como esta



Para poder descargar el archivo tenemos que llegar a la parte inferior de la página



Allí hay una serie de enlaces. El primero a la izquierda, "Download", es el que nos permitirá descargar el archivo



Espero que estas instrucciones sean útiles. Buen fin de semana.

Technorati Tags: MS Excel

Controlar valores fuera de rango en Excel

Ya hemos escrito en alguna nota sobre ciertos problemas que pueden surgir al usar validación de datos para controlar los valores que son introducidos en una celda.
Supongamos esta lista de números en el rango A1:A10, para el cual hemos definido como valores permitidos sólo números enteros en 50 y 100



Si intentamos poner, por ejemplo, 48 Excel genera un mensaje de error



¿Cómo es entonces que en la lista aparecen valores "ilegales"? Esto se debe a que Excel controla la validez de los datos en el momento de ser introducidos manualmente. Si los datos existen en la hoja antes de haber definido la regla de validación de datos o si copiamos los datos de otra fuente y los pegamos en la hoja, Validación de datos no funciona.

Una forma rápida de controlar la validez de valores en situaciones como las descritas en el párrafo anterior es usar la barra de Auditoría de fórmulas



Esta barra tiene un botón para rodear con círculos valores no válidos



En nuestro caso, al apretar el botón vemos este resultado



Al reemplazar el valor inválido por uno permitido, el círculo desaparece. Esto hace que este método sea muy cómodo.

Por ejemplo, si tenemos que ubicar un valor determinado en una tabla existente, podemos definir una regla para validación de datos que excluya este valor y luego usar auditoría de fórmulas para ubicarlo.

Si queremos ubicar el valor 561 en esta tabla,



definimos validación de datos con la fórmula =A1<>561 (asegurándonos que la elda activa sea A1 y que usamos referencias relativas)



Luego usamos auditoría de fórmulas para ubicar la celda que contiene el valor



Para ubicar duplicados podemos usar esta fórmula en validación de datos

=CONTAR.SI($A$1:$F$14,A1)=1



Al aplicar auditoría de fórmulas vemos que el valor 360 se repite

Technorati Tags: MS Excel

Contar palabras y caracteres con Excel

La mujer de mi buen amigo Abner es maestra de inglés en el colegio secundario de la zona. Como tal tiene la dura tarea de corregir exámenes y trabajos que preparan sus alumnos. Una las tareas que suele encomendar a sus discípulos es preparar una composición que no exceda un determinado número de palabras.
Abner me preguntó si había alguna manera de contar las palabras de cada trabajo en forma automática. Mi sugerencia fue que los alumnos presentaran los trabajos en archivos de Word (lo que me parece que hacen de todas maneras). Luego todo lo que hay que hacer es abrir el archivo y usar el menú Herramientas-Contar palabras



Había cierto aire de decepción en la mirada de Abner, que es un fanático de Excel. Si por él fuera también el café del desayuno lo prepararía con Excel.
-Ah!, pensé que se podría hacer con Excel, dijo cerrando la frase con un suspiro.

Personalmente soy enemigo de estas exageraciones pero me quedé pensando si, efectivamente, se podría hacer con Excel. Me acordé entonces de mi nota sobre la Biblia en Excel. Allí justamente había usado Excel para contar palabras por versículos y también encontrar con qué frecuencia cierta palabra aparecería en el texto.

Decidí adaptar ese modelo a las necesidades de Bárbara, la mujer de Abner. En esta nota presento el resultado que tal vez le resulte útil a alguno de mis lectores. Y de no ser así, por lo menos puede contribuir a mostrar algunas técnicas interesantes en Excel.

Este modelo cuenta con dos hojas, Contador donde usamos fórmulas para contar caracteres, palabras y frecuencia de una determinada palabra



y la hoja Capítulos donde vamos poniendo los capítulos (o parágrafos o trabajos de los alumnos) en celdas de la columna B. En las celdas de la columna A ponemos el nombre del capitulo o el número de parágrafo o el nombre del alumno



Veamos ahora que fórmulas usamos. Para contar la cantidad de caracteres, incluyendo los espacios, usamos la función LARGO



Sencillo, no? Para contar los caracteres, sin tomar en cuenta los espacios tenemos que usar una fórmula más elaborada



Lo que hace la fórmula =LARGO(SUSTITUIR(ESPACIOS(Capitulos!B2)," ","")) es sustituir los espacios en blanco , que señalamos en la fórmula con " ", por ausencia de espacios que señalamos con "" (comillas sin espacios entre ellas). Usamos la función ESPACIOS para quitar todos los espacios que no se encuentren entre palabra y palabra.

Las cosas se complican un poco más cuando queremos contar la cantidad de palabras. Para hacer esto partimos de la base que toda combinación de caracteres que se encuentre entre dos espacios es una palabra. Una vez establecido esto usamos la fórmula



=LARGO(ESPACIOS(Capitulos!B2))-D8+(LARGO(Capitulos!B2)>=1)

La expresión LARGO(ESPACIOS(Capitulos!B2)) es similar a la fórmula de la celda C8, sólo que hemos agregado la función ESPACIOS como hemos explicado más arriba. De esta expresión restamos el resultado de D8, que es la cantidad de caracteres del parágrafo sin tomar en cuenta los espacios entres las palabras. Esta diferencia es el número de palabras en el parágrafo menos una.
No podemos agregar sencillamente 1 a esta fórmula ya que existe la posibilidad que la celda de la referencia está vacía. Por lo tanto agregamos la expresión

(LARGO(Capitulos!B2)>=1)

Esta es una expresión lógica cuyo resultado puedes ser 1 (VERDADERO) o 0 (FALSO). Es decir, si la celda contiene texto y por lo tanto el largo es por lo menos 1, la expresión da como resultado 1 que es agregado al resultado de la fórmula.

Ahora necesitamos una fórmula para calcular la frecuencia con que una palabra se repite en el texto. La idea es que en la celda C4 ponemos la palabra que queremos evaluar, en la celdas de la columna F veremos la frecuencia de la palabra en cada parágrafo y en la celda C5 veremos el total.



En la primera etapa del desarrollo del modelo usé la misma fórmula que en el modelo de la Biblia, pero no en forma matricial

=(LARGO(ESPACIOS(MAYUSC(Capitulos!B3)))-LARGO(SUSTITUIR(ESPACIOS(MAYUSC(Capitulos!B3)),MAYUSC(Contador!$C$4),"")))/LARGO($C$4)

Me eximo de explicar esta fórmula por la sencilla razón que es errónea (mea culpa!, mea culpa!).
Esta fórmula funciona bien a condición que la palabra buscada no coincida con alguna o algunas sílabas de otras palabras. Por ejemplo, al buscar la frecuencia de la palabra "el", el resultado para el parágrafo 1 es 4 veces. Sin embargo "el" sólo aparece 2 veces



Después de darle vuelta al asunto, decidí escribir una función definida por el usuario (UDF) que resultó ser de lo más sencilla



Al aplicar esta fórmula en el modelo vemos resultados totalmente distintos



El código se basa en crear una matriz (array) con las palabras de la celda usando la función Split de Visual Basic. Luego usamos InsStr para comparar cada una de las palabras de la matriz con la palabra buscada. En caso de coincidencia incrementamos el valor de la variable Counter en 1.

Basándonos en la misma idea podemos escribir otra función UDF para realizar el recuento de palabras



El archivo con el modelo y las funciones puede descargarse aqui.


Technorati Tags: MS Excel

Conversión entre unidades con Excel

De tanto en tanto me enfrento al problema de convertir valores de una unidad a otra, como por ejemplo, de acres a hectáreas o de galones a litros.
La Internet nos ofrece un sinnúmero de sitios que nos asisten en hacer esta conversiones, como OnlineConversion.com o Online Unit Converter.
En Excel podemos realizar conversiones entre unidades con la función CONVERTIR (CONVERT). Esta función estará disponible sólo si tenemos instalado el complemento Analysis ToolPak.
El alcance de esta función está limitado a una lista de unidades y su uso no es muy flexible. Pero si desarrollamos un modelo en el cual necesitamos hacer operaciones entre unidades, esta función puede ser útil.

La sintaxis de CONVERTIR es obvia

CONVERTIR(número;de_unidad;a_unidad)

Para ver la lista de unidades podemos apelar a la ayuda en línea de la función. Ahí podemos ver listas ordenadas por categorías, como por ejemplo la de peso y masa



o la de temperatura



Y si alguna vez necesitamos saber cuántas cucharadas soperas hay en una pinta o en un galón, Excel vendrá a nuestro rescate



Para convertir 8 metros a pies usamos =CONVERT(C1,"m","ft")



En mi sistema, que no está montado en castellano, la función se llama CONVERT y el símbolo para la unidad pies es "ft". Como señalo más arriba, conviene chequear los símbolos de las unidades en la ayuda en línea, ya que ante cualquier imprecisión l función da un resultado #ERROR o #VALOR.

Technorati Tags: MS Excel

Calcular frecuencias y crear histogramas con Excel

En una nota anterior hemos mostrado como crear un histograma usando el Data Analyisis del Analysis ToolPak.
En esta nota veremos como hacerlo manualmente usando la función FRECUENCIA. Además veremos una fórmula para crear grupos de distribución uniformes a partir de los datos de la muestra.
Y como dijo Jack el Destripador, vamos por partes.
Supongamos esta tabla de notas de los alumnos de un colegio imaginario.



Digamos ahora que el director del colegio nos ha pedido analizar la distribución de las notas, dividiéndolas en cinco grupos de igual amplitud.
Vemos que la nota mínima obtenida es 50 lo que calculamos fácilmente con la fórmula

=MIN(A2:D18)

Lo mismo para la nota máxima, con =MAX(A2:D18)

Como nuestro director quiere cinco grupos de notas, estos serán:

de 0 a 60
de 61 a 70
de 71 a 80
de 81 a 90
de 91 a 100

Para nuestro uso en Excel tomamos sólo el extremo superior de intervalo, es decir



Para crear la columna con las frecuencias de cada grupo usamos la función FRECUENCIA. Esta función es matricial, es decir, la introducimos en el rango de celdas apretando simultáneamente Ctrl+Mayúsculas+Enter



Esta función tiene dos argumentos: Datos, en nuestro caso la tabla con las notas y Grupos, los grupos de distribución. Nótese que si bien el grupo es un intervalo, por ejemplo 0 - 60, sólo usamos la cota superior.
Para calcular el peso relativo de cada grupo usamos la fórmula

=G2/SUMA(G$2:G$8)



Ahora podemos representar la distribución fácilmente en un gráfico, que hará las delicias del director del colegio



También podemos calcular la distribución de las notas para cada asignatura por separado y mostrarlas en un mismo gráfico, lo que permitiría hacer una comparación



En lugar de crear dos columnas para cada asignatura, podemos calcular directamente la distribución relativa de los grupos de notas usando esta formula matricial

=FRECUENCIA(B2:B18,$F$2:$F$6)/CONTAR(B2:B18)



Sencillamente dividimos el resultado de FRECUENCIA para cada grupo por el total de elementos (notas) de la asignatura.

¿Cómo haríamos para calcular el intervalo de cada grupo en función de los datos de la tabla?
Lo haremos, como ya sospechan, con una fórmula matricial. Nuestras variables son la nota mínima, la máxima y la cantidad de grupos requeridos. Nuestra fórmula deberá calcular la cata superior de cada grupo.
Siguiendo con nuestro ejemplo, la nota mínima es 50, la máxima 100 (de hecho 99, pero el sentido común nos dice que debemos tomar el cuenta la máxima posible) y queremos 5 grupos divididos uniformemente.
Para facilitar la lectura de la fórmula he definido el rango de la tabla (A2:D18) en un nombre: "notas". La fórmula matricial que nos permitirá calcular los grupos es

={MIN(notas)+FILA(INDIRECTO("1:5"))*(MAX(notas)-MIN(notas)+1)/5}



Un detalle importante: seleccionamos todo el rango F2:F6 antes de introducir la fórmula. Luego escribimos la fórmula y pulsamos Ctrl+Mayúsculas+Enter simultáneamente.

¿Cómo funciona esta fórmula?

La expresión MIN(notas) genera una matriz de cinco líneas, todas con el valor 50 (la menor nota de la tabla);

FILA(INDIRECTO("1:5") genera una matriz de cinco valores del 1 al 5, siendo 5 el número de grupos que queremos exponer

MAX(notas)-MIN(notas)+1 genera una matriz de cinco líneas todas con el valor 50 (99-50+1)

La fórmula genera, entonces, una matriz con los valores 60, 70, 80, 90, 100 como resultado de:

50 + (1*50/5)
50 + (2*50/5)
50 + (3*50/5)
50 + (4*50/5)
50 + (5*50/5)

Si queremos generar 10 grupos, por ejemplo, usamos como variable 10 en lugar de 5

={MIN(notas)+FILA(INDIRECTO("1:10"))*(MAX(notas)-MIN(notas)+1)/10}

Technorati Tags: MS Excel

Calcular semestres o trimestres en Excel.

Excel cuenta con varias funciones que nos permiten calcular, por ejemplo, el día, el mes o el año dada una determinada fecha.
Por ejemplo, si en la celda A1 tenemos la fecha 27/08/2008, la fórmula =DIA(A1) da como resultado 27; la fórmula =MES(A1) da como resultado 8 y la fórmula =AÑO(A1) dará como resultado 2008.
Excel no tiene funciones nativas para calcular otros intervalos como el semestre del año o el trimestre del año.
Para calcular el trimestre del año de una determinada fechas podemos usar una de estas fórmulas

=REDONDEAR.MAS(MES(A1)/3,0)

=COINCIDIR(MES(A1),{1;4;7;10})

En la primer fórmula obtenemos primero el número de mes con la función MES, luego lo dividimos por 3, el número de meses que hay en cada trimestre y finalmente redondeamos el resultado hacia arriba para obtener el número de trimestre.
En nuestro ejemplo,

=MES(A1) da 8

8/3= 2.666667

=REDONDEAR.MAS(2.666667) = 3

La segunda fórmula usa una constante matricial como matriz de búsqueda en la función COINCIDIR. Siguiendo con nuestro ejemplo, =MES(A1) da 8, que coincide con el tercer elemento de la constante matricial.

Si queremos calcular el semestre del año a partir de la fecha, podemos usar una de estas fórmulas

=REDONDEAR.MAS(MES(A2)/6,0)

=COINCIDIR(MES(A2),{1;7})

Y cómo calculamos la semana del año a partir de la fecha? Bien, Chip Pearson propone esta fórmula


=TRUNCAR(((A1-FECHA(AÑO(A1),1,1))/7))+1+SI(DIASEM(FECHA(AÑO(A1),1,1))>DIASEM(A1),1,0)

Technorati Tags: MS Excel

Crear vínculos con Pegado Especial

Ya hemos mostrado algunos de los usos de Pegado Especial en Excel. Una de las posibilidades es la de crear vínculos a la celda o al rango que estamos copiando. Esto la hacemos usando la opción Pegar Vínculos



Al copiar el rango A1:A5 usando esta opción vemos el valor de la celda A1 en la celda B4, pero en la barra de fórmulas vemos que Excel a creado una referencia a la celda de origen



También podemos usar esta opción para crear referencias al rango en otra hoja



Y también crear vínculos a cuadernos remotos



Excel tiene un comportamiento un tanto curioso con esta opción. Al copiar rangos usando Pegar Vínculos, el vínculo es creado como referencia relativa (sin los símbolos $ en la referencia). En cambio se usamos Pegar Vínculos para copiar una única celda, Excel creará una referencia absoluta (es decir $A$1).

Otra forma de copiar creando vínculos es arrastrando el rango a copiar presionando al mismo tiempo la tecla Mayúsculas (o Ctrl o Alt). Al arrastrar el rango a copiar presionamos el botón derecho del ratón



Al igual que con el menú de Pegado Especial, si copiamos una única celda se creará una referencia absoluta. Si lo hacemos con un rango de celdas, la referencia será relativa.

Technorati Tags: MS Excel

Calcular fracciones de año

Ya hace varios días que no sólo no publico notas en este blog, sino que tampoco estoy respondiendo a los muchos mails que recibo con todo tipo de consultas.
Así que a modo de disculpa hasta que encuentre el tiempo de ir respondiendo a las consultas que han llegado, va aquí una sugerencia sobre como calcular fracciones de año.
Excel dispone de muchas funciones que permiten hacer cálculos con tiempo. Una lista de estas funciones se puede ver abriendo al asistente de funciones y seleccionando la categoría Fecha y hora



Sin embargo, hay otras funciones para este tipo de cálculos que están a nuestra disposición sólo si hemos activado el Analysis ToolPak.
Una de estas funciones es FRAC.AÑO (YEARFRAC en la versión inglesa).
Esta función calcula la fracción de año que representa el número de días enteros entre la fecha inicial, el primer argumento de la función, y la fecha final, el segundo argumento.
Existe un tercer argumento, base. Este argumento determina sobre que base se efectuará el cálculo.
Supongamos que en la celda A1 tenemos el valor de fecha 01/01/2008 y en la celda A2 01/07/2008. La fracción de días transcurridos es obviamente 1/2 (o 0.5). Sin embargo veremos que dependiendo de la base escogida obtendremos distintos resultados:

=FRAC.AÑO(A1,A2) = 0.5

En esta fórmula hemos omitido el argumento "base", por lo que Excel supone que la base es 30/360.

=FRAC.AÑO(A1,A2,1) = 0.4972677

Usar uno como base es interpretado con "real/real", es decir, el número real de días transcurridos dividido por el número real de días del año en cuestión. En nuestro ejemplo 182/366 (2008 es bisiesto!).

=FRAC.AÑO(A1,A2,2) = 0.505555

La base 2 es "real/360", es decir, la cantidad de días realmente transcurridos (182) dividido por un año estándar de 360 días (cosa de gringos!).

=FRAC.AÑO(A1,A2,3) = 0.4986301

La base es como la anterior pero dividida por un año de 365 días (a pesar que 2008 tiene 366, como ya hemos señalado).

=FRAC.AÑO(A1,A2,4) = 0.5

La base 4 calcula de acuerdo a 30/360 (europea), es decir meses de 30 días y año de 360.

Technorati Tags: MS Excel

Autofiltro - comandos personalizados

Si utilizan con cierta frecuencia Autofiltro para analizar datos de una tabla en Excel, seguramente sabrán apreciar esta sugerencia.
Para activar Autofiltro lo que hacemos normalmente es abrir el menú Datos-Filtro-Autofiltro



Si quisiéramos filtrar la lista para ver sólo los clientes de la Argentina, pulsamos la fecha del campo (Country, en nuestro ejemplo) y marcamos "Argentina"



Contemos la cantidad de operaciones que hemos efectuado:
1 - Clic al menú Datos
2 - Clic al menú Filtro
3 - Clic al menú Autofiltro
4 - Clic a la flecha del filtro en el campo Country
5 - Clic a la opción "Argentina".

En total cinco operaciones. Todo esto se puede hacer en un solo clic, si instalamos previamente el comando Autofiltro en alguna de la barras de herramientas.

Para instalar el comando, abrimos el menú Herramientas-Personalizar (o clic con el botón derecho del Mouse apuntando al área de las barras de herramientas)



En la pestaña Categorías del diálogo Personalizar, elegimos la categoría Datos; en la ventanilla Comandos seleccionamos Autofiltro



y lo arrastramos a alguna de las barras de herramientas



Una vez instalado, el comando quedará a nuestra disposición cada vez que usemos Excel.

Volviendo a nuestro ejemplo, en el cual hemos quitado el Autofiltro, seleccionamos la celda A13 que contiene el valor "Argentina" (el criterio con el cual queremos filtrar la lista).



Todo lo que tenemos que hacer es pulsar una vez el icono de Autofiltro. Excel activa
el autofiltro y realiza el filtrado de acuerdo al valor de la celda activa



Lo que antes hicimos con cinco operaciones. Hemos hecho ahora con un único clic.

Como podrán notar, he agregado otro comando muy útil, "Mostrar todo". Un clic a este comando elimina todos los filtros de la lista.

Technorati Tags: MS Excel