Cuando Excel se equivoca al ordenar datos

jueves, agosto 29, 2013

Hay muchos motivos por los cuales se pueden producir desajustes al ordenar una tabla en Excel. Cuando nos topamos con este problema, nuestra primera reacción es "¡hay un bug en Excel!". Pero los que "hemos fatigado los arduos senderos de Excel por años" (parafraseando al inmortal Borges), sabemos que en casi todos los casos la culpa recae sobre el usuario.

Este artículo de Microsoft contiene una lista de errores que pueden causar desajustes al ordenar datos.

Una vez dicho todo esto, veamos el siguiente caso



En esta tabla, los valores de la columna Porcentaje se obtienen de la tabla en la hoja "clientes", usando la función INDICE combinada con COINCIDIR.



Si observamos la fórmula en la columna "Porcentaje" veremos que Excel incluye la referencia a la hoja "ventas" a pesar de ser esta la hoja activa.



Esto se debe a que al construir la fórmula comenzamos en la hoja activa (Ventas), pasamos a la hoja "clientes" para señalar el rango de la matriz de búsqueda de INDICE y luego volvemos a "Ventas" para completar la fórmula.

Esta referencia a la hoja donde se encuentra la fórmula parece superflua pero inocua. Ahora veamos que pasa al ordenar la tabla por tipo de clientes. Como referencia recordemos que las ventas netas del Cliente 1 son 259,508



Las ventas neto del Cliente 1 ahora son 274,773!



Si nos fijamos en las fórmulas en la tabla ordenada por Tipo, veremos que, a pesar de que las referencias a la columna Tipo son relativas (por ejemplo, ventas!C3 para el Cliente 1), al ordenar la tabla éstas actúan como referencias absolutas.
En la imagen anterior podemos ver que el Cliente 10 se encuentra en la fila 3, pero la formula en la celda E3 se refiere a la celda C12, que era la fila del cliente antes de ordenar la tabla.

Para solucionar o evitar este problema lo que hacemos es eliminar la referencia a la hoja activa ("ventas" en nuestro caso) en las fórmulas. Nuestra fórmula ahora se verá así

=INDICE(clientes!$B$2:$B$4,COINCIDIR(C3,clientes!$A$2:$A$4,0))

Ahora, al ordenar los datos por tipo, no se producirá el desajuste


6 comments:

Lennon 29 agosto, 2013 21:27  

Me pasó hace pocos días, encontré la solución que mencionas de manera empírica, es un muy buen tip. Uno más para la biblioteca. Saludos.

Christian Calderon Maldonado 16 marzo, 2016 22:13  

Buenas tardes, por favor quisiera que desarrolle el siguiente tema que he descubierto:
tengo una pestaña con 3 tablas, en las cuales hay grupos y subgrupos, rotulados sin celdas combinadas (cada subgrupo tiene su rotulo igual para cada fila a la izquierda), quiero darle un primer orden de nivel por total del grupo y otro nivel para cada subgrupo, de mayor a menor para todo, pero el excel solo me permite darle orden a UNA tabla, las demás no las ordena. quisiera saber más acerca de este misterio, ya que también descubrí que cuando dejo una sola tabla por pestaña si me ordena muy bien todo, solo que mi "jefecito" quiere todo en una sola, gracias de antemano y espero me pueda haber comprendido.

Jorge Dunkelman 17 marzo, 2016 06:55  

Christian, te sugiero que me envíes el archivo o un ejemplo ya que me resulta difícil analizar tu problema a partir la descripción (por ejemplo, no me queda claro qué quieres decir por subgrupo).
Ahora, si estamos hablando de ordenar y no de filtrar, Excel permite ordenar rangos independietemente uno del otro.

Christian Calderon Maldonado 17 marzo, 2016 17:52  

Estimado Jorge te he enviado el archivo a tu correo, gracias

Jorge Dunkelman 18 marzo, 2016 20:24  

Hola Christian, le doy un vistazo y te contesto por mail privado.

DaniHerreno 15 noviembre, 2016 23:40  

Gracias, logré solucionar el problema :)

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP