Corregir suma de porcentajes redondeados.

Cuando sumamos números que hemos redondeado suele presentarse el problema que, aparentemente, Excel no suma los números correctamente.

Supongamos que queremos repartir un bono entre distintos vendedores, de acuerdo a la parte proporcional de cada uno en el total de las ventas. Dado que somos expertos en Excel sólo nos toma unos momentos preparar el informe

Nuestro jefe nos pide que presentemos el informe con números enteros, prescindiendo de los centavos. Ningún problema! Ajustamos el formato de los números




Pero nuestro jefe tiene ojo de lince y advierte que el total de las sumas a distribuir en la columna D suman 9999! Esto por supuesto no es un “bug” de Excel ni tampoco un problema matemático. Sencillamente, la parte decimal oculta completa el 1 que nos está faltando.

Supongamos ahora que usamos la función REDONDEAR para hacer el ajuste de las sumas. También en este caso nos topamos con el problema

Hay varias formas de solucionar el problema y todo depende del gusto personal de cada uno (o del jefe de cada uno).

Una posibilidad es cargar la diferencia en forma arbitraria a alguno de los vendedores, por ejemplo al último de la lista. Hacemos esto poniendo en la celda del último vendedor la diferencia entre el total a repartir y el total de bonos repartidos a los demás vendedores

En la celda D8 ponemos la fórmula =D9-SUMA(D4:D7), donde D9 es el total a repartir y SUMA(D4:D7) el total de bonos repartidos exceptuando el último vendedor.
Esta fórmula se “autoajusta” y nos garantiza que siempre el total de las sumas redondeadas coincida con la suma a distribuir.

Otra posibilidad es usar una fórmula que asigne automáticamente la diferencia al vendedor con los mejores resultados, en nuestro caso José. Podemos hacer esto usando una columna auxiliar para determinar dinámicamente quién es el vendedor con los mejores resultados usando la función JERARQUIA

En las celdas en el campo Auxiliar (columna D) usamos la fórmula

=JERARQUIA(B4,$B$4:$B$8)

En el campo Distribución (columna E) usamos la fórmula

=REDONDEAR($B$1*C4,0)+(D4=1)

La primer parte de la fórmula calcula el bono del vendedor y lo redondea. La segunda parte controla si es el mejor vendedor (JERARQUIA =1) y en caso de ser cierto agrega 1.

Claro que esta solución es parcial y se aplica sólo al caso que la diferencia sea +1.

Para hacer esta solución más general podemos usar dos campos auxiliares. En el primero calculamos los bonos redondeados; en el segundo usamos JERARQUIA como en el caso anterior. Luego podemos usar la diferencia entre el total de Auxiliar 1 (celda D9) y bono a distribuir (celda B1) para ajustar el resultado poniendo la fórmula

=D4+((E4=1)*($B$1-$D$9))

en el campo Distribución (columna F)

Technorati Tags: MS Excel

Algunos trucos con Autosuma en Excel

Todos conocemos el icono de Autosuma en Excel. Éste nos permite introducir la función SUMA con solo hacer un clic sobre él. Muchos de nosotros sabemos que este icono nos permite agregar muchas otras funciones

Si tenemos un rango de números contiguos (vertical u horizontalmente) y nos ubicamos en la primer celda libre por debajo (o a la derecha) del rango, al apretar Autosuma Excel reconoce automáticamente el rango y pone la fórmula. El rango automático siempre corre desde la última celda del rango (contigua a la celda libre donde queremos que aparezca la fórmula) y hasta la primer celda libre o la primera ocupada por una fórmula.

Si tenemos un rango con más de una columna, podemos totalizar las filas y las columnas con un solo clic a Autosuma. Seleccionamos el rango rectangular asegurándonos de incluir una fila y una columna en blanco

Al apretar Autosuma Excel pone las fórmulas correspondientes para las filas y las columnas



Otro truco interesante es el siguiente. Supongamos que tenemos esta serie de rangos de números, cada uno separado del otro por una fila libra



Con un solo clic podemos agregar un total para cada uno de los grupos. Empezamos por seleccionar los grupos dejando las celdas vacías fuera de la selección. Esto no lo haremos manualmente (supongamos que tenemos una gran cantidad de grupos) sino aplicando “Ir a” (Ctrl+G o F5). Seleccionamos toda la columna (o el rango adecuado) y usamos Ir A—Especial—Constantes—Números



Esto hará que sólo lo números sean seleccionados. Ahora apretamos el icono de Autosuma y veremos que Excel pone la fórmula en forma automática



Pero eso se pone más interesante cuando nos ponemos en la primer celda libre, en nuestro caso A15, y apretamos nuevamente Autosuma.

Excel selecciona automática las celdas que contienen la función SUMA y crea de hecho un suma de sumas



Si hay más de una fila libre entre cada grupo, este truco funciona parcialmente



Volvemos a los pasos anteriores (Ir A—Especial—Constantes—Números y apretar Autosuma) y veremos que Excel pone correctamente la función suma en la primer fila libre por debajo de cada grupo



Pero no podemos volver a aplicar Autosuma como en el caso anterior.

Technorati Tags: MS Excel

Búsqueda de archivos en Office

Si trabajan, como yo, intensamente con Office de Microsoft (Excel, Word, Access, PowerPoint, etc.) posiblemente sufran del problema como encontrar dónde diablos guardamos ese archivo…

Los programas del Office tienen una herramienta, poco conocida en mi opinión, que nos permite ubicar archivos por texto y por palabras claves.

Por ejemplo, tengo varios archivos de Excel con tablas para campeonatos de fútbol. Tienen en común que el texto “tabla de posiciones” forma parte del nombre del archivo. Para ubicarlos apretamos el botón de “Abrir” de la barra de herramientas (o Archivo-Abrir)



En el extremo superior derecho pulsamos Herramientas y Buscar. También podemos acceder a través del menú Archivo



En Excel 2003 existen ciertas diferencias funcionales entre ambas posibilidades.
Usando el primer método se abre este formulario



En “buscar texto” ponemos el texto de búsqueda y Tipo de archivo seleccionamos Excel. Al pulsar “Ir” comenzará la búsqueda de los archivos. En mi máquina la búsqueda dio estos resultados



Una vez seleccionado el archivo, apretamos Aceptar para abrirlo.
Una posibilidad más avanzada es usar palabras claves. El requisito es incluir las palabras claves en el formulario Propiedades del archivo. Por ejemplo, en los archivos cuyo tema es facturación con Excel, he puesto la palabra clave “factura”



Abrimos Herramientas-Buscar y apretamos la pestaña Avanzada. En Propiedad elegimos Palabras clave, en Condición elegimos “incluye” y apretamos el botón “Agregar”. Como estamos usando una sola propiedad no hay necesidad de elegir entre las posibilidades “Y” u “O”.



Apretamos “Ir” y podremos ver el resultado de la búsqueda



Como puede apreciarse este método es muy flexible y permite búsquedas de acuerdo a muchos criterios.

El método Archivo-Buscar tiene una interfaz levemente distinta

Technorati Tags: MS Excel