Problemas de redondeo y precisión en Excel

Excel suele presentar en ciertas situaciones lo que parecieran ser problemas de cálculo.
Veamos una situación con la cual me enfrenté no hace mucho al elaborar una solución para uno de mis lectores. La idea era desarrollar una fórmula que cada vez que la parte fraccional de un número fuera 0.1 diera un determinado resultado.
Aparentemente una tarea sencilla. Por ejemplo si el número a evaluar en al celda A2 es 4.1, aplicamos esta fórmula en la celda B2:

=A2-ENTERO(A2)

esta fórmula da como resultado 0.1, lo que usaremos como argumento en una fórmula en la celda C2. En la celda C2 he puesto la fórmula =B2=0.1, que tiene que dar como resultado VERDADERO. Veamos que pasa en Excel




Como vemos el resultado es FALSO. Esto se debe a que Excel, como la mayoría de los programas, guarda los números en forma binaria de acuerdo al estándar 754 (Punto Flotante) del IEEE.
Si están interesados en ampliar conocimientos sobre este tema pueden consultar el artículo en la base de conocimientos de Microsoft (también hay una versión en castellano) o el artículo de Chip Pearson sobre el tema.

Volviendo sobre el tema, veamos si lo que vemos es lo que es. Agreguemos 16 decimales al formato de la celda B2



Como ven el resultado de la fórmula es un número muy cercano a 0.1, pero no exactamente 0.1. Este fenómeno está explicado en los artículos mencionados más arriba.

Antes de encarar la solución del problema, veamos otra situación similar que hace sufrir especialmente a los contadores. Veamos esta situación:




Si nos fijamos sólo en la columna C, nos parecerá que Excel se equivoca al sumar los tres números 0.3. El resultado esperado es 0.9 y Excel da 1.0. Por supuesto que Excel no se equivoca, y el problema es un problema de formato.
Sin embargo, en los informes financieros, si quisiéramos que Excel de cómo resultado 0.9.

Ambos problemas pueden ser solucionados de dos maneras:

1 – Usar funciones de redondeo de Excel. Por ejemplo, si en la celda B2 combinamos la fórmula con la función REDONDEAR

=REDONDEAR(A2-ENTERO(A2),1)
obtenemos



2 – Usar la opción Precisión de Pantalla en el menú Herramientas-Opciones-Cálculo



Luego de aplicar esta opción los resultados se convierten en




Al usar esta opción hay que tener en cuenta dos detalles muy importantes: el cambio es irreversible (los valores no volverán al estado anterior al cambio si desactivamos la opción) y es aplicado a todo el cuaderno, no sólo a la hoja activa o a un rango determinado.





Categorías: Manejo de Datos_, Varios_

Technorati Tags: Excel

Comparar listas en Excel

Una de los temas más corrientes en Excel es el de la comparación de listas, y su "hermano gemelo" la eliminación o prevención de duplicados.
Ya he tratado el tema en diversas oportunidades, por ejemplo como comparar dos listas usando formato condicional o como evitar duplicados usando validación de datos. También he mostrado como usar consolidación de datos para comparar listas


En esta nota haremos una revisión ordenada del tema. Empecemos por plantearnos un ejemplo. En una misma hoja tenemos dos listas de productos

Para facilitar nuestro trabajo hemos definido dos nombres

lista1 =Hoja1!$A$2:$A$11
lista2 =Hoja1!$D$2:$D$11

1 - Encontrar duplicados usando la función COINCIDIR

En el rango B2:B11 ponemos la fórmula =COINCIDIR(A2,lista2,0); en el rango E2:E11 ponemos la fórmula =COINCIDIR(D2,lista1,0). El resultado es




Los resultados N/A identifican los valores de la lista 1 que no se encuentran en la lista 2, y viceversa. Podemos lograr un resultado más "elegante" combinando COINCIDIR con SI y ESERROR en esta fórmula

=SI(ESERROR(COINCIDIR(A2,lista2,0)),"","duplicado")

El resultado es




Podemos mejorar aún más la presentación usando formato condicional para dar un fondo de color a los duplicados. Por ejemplo, para la lista2 definimos esta condición



La dirección de la celda de referencia debe ser relativa (sin los signos $)

2 – Señalar duplicados usando formato condicional.

Como explicamos en la nota sobre el tema, usamos la función CONTAR.SI en el menú de formato condicional para comprobar si valores de la lista 1 también figuran en la lista 2. Usamos una fórmula distinta para cada lista.
En la lista 1 usamos la fórmula =CONTAR.SI(lista2,A2)>0





En la lista 2 usamos la fórmula =CONTAR.SI(lista1,A2)>0



El resultado es idéntico al método anterior



3 – Generar una lista de valores comunes a ambas listas

Digamos que ahora queremos generar una lista de valores que aparecen en ambas listas.
En nuestro ejemplo, escribimos esta fórmula en la columna F:

=SI(CONTAR.SI(lista2,A2)>0,A2,"")

Alternativamente podemos usar la fórmula =SI(CONTAR.SI(lista1,D2)>0,D2,""), que dará el mismo resultado aunque en distinto orden.

Como podemos ver en la lista de valores comunes aparecen celdas en blanco. Una alternativa para eliminar las celdas en blanco es copiar los valores de la lista a otro rango y usar la función Ir A--Especial--Celdas en Blanco, como explicamos en esta nota.

También podemos hacerlo usando fórmulas. Para esto necesitaremos crear dos rangos de fórmulas auxiliares.
Siguiendo con nuestro ejemplo, escribimos esta fórmula en la celda H2 y la copiamos a lo largo del rango

=SI(CELDA("contenido",F2)="",0,FILA())

Esta fórmula nos permite dar un número único si en la columna F aparece un producto común a ambas listas o 0 (cero) si el resultado en F es blanco. Usamos la función CELDA y no ESBLANCO, ya ninguna de las celdas en la columna F está en blanco (todas contienen fórmulas).

El próximo paso es usar la función JERARQUIA en la columna I

=JERARQUIA(H2,$H$2:$H$11)

En esta fórmula hemos omitido el argumento opcional "orden", por lo tanto el resultado aparece en forma descendiente.




Podemos ver que para las celda con resultado 0 en la columna H, obtenemos el mismo número de orden.

El resultado en la columna I nos sirve de variable en la fórmula que escribimos en la columna J,

=INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))

que nos da esta lista




El problema con esta fórmula es que aparecen resultados N/A. Para evitar esto agregamos una condición a nuestra fórmula, resultando

=SI(ESERROR(INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))),"",INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0)))

es decir, primero evaluamos la fórmula y si el resultado da error, la función SI da como resultado "blanco", si no se aplica la función INDICE.




4 – Generar una lista de valores únicos

Para generar una lista de valores únicos, usamos el mismo método con la diferencia que la fórmula en la columna F pasa a ser

=SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,""))

donde combinamos dos condiciones para obtener los productos que sólo aparecen en una de las listas.


Por supuesto, existen otros métodos para comparar listas, incluyendo el uso de macros. Esto será tema de una futura nota.


Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: Excel, Duplicados en Excel

Gráficos Excel - Agregar rótulos al eje de los valores (Y)

Después de leer mi nota sobre como agregar líneas dinámicas en gráficos de Excel, el jefe de control de calidad de mi empresa me pidió que le preparara el siguiente gráfico:

-en el eje de las X (categorías) aparecen meses del año.

-los valores (Y) son representados por columnas que representan un índice entre 0% y 100% de productos aprobados de la producción del mes.

-en el eje de las Y en lugar de los valores quiere que aparezcan los rótulos "reprobado", "aceptable", "bueno", "muy bueno", "excelente", de acuerdo a los resultados de cada mes.

-partiendo de cada rótulo debe haber una línea horizontal que ayude a interpretar el nivel de resultados alcanzado en cada mes.

Supongamos que esta es la tabla de calificaciones del laboratorio





Y esta la tabla de índices para el año:



El gráfico que queremos construir es el siguiente:



Para lograr este gráfico, con rótulos de texto en el eje de las Y en lugar de valores numéricos, usaremos una combinación de gráfico de columnas y puntos XY.
Mostraremos el proceso paso por paso:

1 - Seleccionamos la tabla de datos (el rango A1:B9) y creamos un gráfico de columnas, sin leyenda y con el título.




2 - Creamos una tabla auxiliar para definir la nueva serie de puntos con los que definiremos las líneas auxiliares. En el rango E3:F9 entramos los valores que definirán los puntos



3 - Seleccionamos el rango E3:F9 y lo copiamos (Ctrl+C). Luego seleccionamos el gráfico con un clic, abrimos el menú Edición—Pegado Especial, y elegimos las opciones señaladas en la imagen



Al pulsar Aceptar aparece una nueva serie de columnas



4 - Seleccionamos la nueva serie y en el menú Tipo de Grafico elegimos el tipo XY (Dispersión) con puntos conectados por líneas. Como resultado aparecen los puntos sobre el eje de las Y y dos ejes secundarios a la derecha y por encima del gráfico



5 - Seleccionamos el eje secundario de las Y, abrimos el menú de formato del eje. En la pestaña Escala fijamos el valor máximo en 100 y el mínimo en 0. En la pestaña Tramas ponemos todos los valores en "ninguna", con lo cual ocultamos el eje.

6 - Hacemos lo mismo con el eje secundario de las X.

7 - Seleccionamos la serie de los puntos XY que aparecen sobre el eje de las Y y abrimos el menú de Formato de serie de datos. En la pestaña Tramas elegimos el color negro para la línea; elegimos un marcador adecuado, con color negro




8 - En la pestaña Barras de Error X, señalamos la opción "por exceso" y ponemos 100 para "valor fijo" (coincide con la escala del eje secundario de las X).



9 - En la pestaña Rótulos de Datos elegimos "nombre de la serie". Luego de pulsar aceptar, seleccionamos los rótulos con un clic y abrimos el menú Formato de rótulos de Datos. En la pestaña Alineación seleccionamos "izquierda" para Posición.



10 – Seleccionamos el eje principal de la Y y los hacemos "desaparecer" poniendo "ninguno" en todas las opciones de la pestaña Tramas.



11 – Ahora nos ocuparemos de los rótulos "Serie 2". En primer lugar borramos el rótulo inferior que aparece en el punto de intersección de las coordenadas. Para esto seleccionamos el rotulo con dos clics y pulsamos "borrar".
Para cambiar el texto de los restantes rótulos tenemos dos opciones: seleccionar cada rótulo con dos clis y escribir el texto correspondiente, o crear un vínculo dinámico a los textos que aparecen en el rango D4:D9.
Para crear un vínculo dinámico, seleccionamos, por ejemplo, el rótulo superior, que debe decir "sobresaliente", luego ponemos el signo "=" en la barra de fórmulas y cliqueamos la celda D9 (donde aparece el texto "sobresaliente").




Volvemos sobre lo mismo para cada uno de los rótulos. De esta manera los valores de los rótulos y de las líneas se adaptarán automáticamente a los cambios en la tabla auxiliar. Es decir, nuestro gráfico es totalmente dinámico.



Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel