Suma condicional con errores en Excel

Si el rango de la función SUMA incluye valores de error, por ejemplo #N/A (valor no disponible), el resultado de la función será también #N/A.
Por lo general lo que hacemos es investigar la celda o celdas que generan esos valores de error y corregirlas.
Pero a veces el rango reside en una hoja remota o sencillamente no queremos corregir estas celdas ya que el resultado, a pesar de ser #N/A, es válido.
Para sumar un rango que incluye valores #N/A sencillamente usamos SUMAR.SI

Dado este rango, por ejemplo



La fórmula SUMAR.SI(A1:A12;"<>#N/A") da el resultado correcto, 408.

Supongamos ahora que en nuestro rango tenemos varios tipos de errores. Por ejemplo, #N/A y también #¡DIV/0!. Por ejemplo



Cómo hacemos para sumar los valores que no son error?

Ya vimos que SUMAR.SI no acepta más de un criterio. Por lo general para sumar, contar, con más de una condición usamos SUMAPRODUCTO o fórmulas matriciales.

actualización de la entrada, siguiendo la observación de Vicente
Pero en este caso, tampoco SUMAPRODUCTO o funciones matriciales pueden ayudarnos. Esto se debe a que al multiplicar los elementos de las matrices entre si, incluimos valores #N/A o #¡DIV/0! en la operación y el resultado es, obviamente, #error!


En un caso de rango "mixto" como el nuestro, la única solución que encuentro es podemos usar esta función matricial (sugerida por Vicente en su comentario)

={SUMA(SI(ESERROR(A1:A12);"";A1:A12))}

o escribir una función definida por el usuario (UDF).

En un módulo del editor de Visual Basic escribimos esta función

Option Explicit

Public Function Sum_Err(rng)
Dim cell As Range, Count


Count = 0
For Each cell In rng
If Not IsError(cell.Value) Then
Count = Count + cell.Value
End If
Next

Sum_Err = Count

End Function

Para usar la función podemos usar el asistente de funciones, seleccionando la categoría "definidas por el usuario"



Seleccionamos el rango que queremos sumar



Y obtenemos el resultado esperado

Technorati Tags: MS Excel

Mejorar gráficos estándar de Excel

Hace un tiempo que leo el blog de BizViz de Jorge Camoes, que trata sobre visualización de información. Es un blog interesante, que recomiendo leer a aquellos que usen Excel para elaborar y presentar información. El blog está escrito en portugués, lo cual no representa una barrera para el lector de habla castellana.
En una de las últimas notas, Jorge Camoes da un ejemplo de cómo mejorar los gráficos estándar de Excel.

Veamos este ejemplo. Dada una tabla con dos columnas de datos, años y ventas, el gráfico de columnas que Excel construye es el siguiente:




Más que empezar a analizar los defectos de este gráfico, veamos como lo podemos mejorar, sin mucho esfuerzo

En primer lugar borramos la leyenda, que coincide con el título (tenemos sólo una serie)



Luego quitamos el fondo del área del gráfico y el borde



En ambos ejes, elegimos una fuente más pequeña y quitamos la marca de Autoescala. De esta forma también logramos que todos los puntos en el eje e las X tengan un rótulo.



En el menú de formato de líneas de división, elegimos una línea más delicada



Ahora es el turno de cambiar la escala del eje del eje de las Y



Finalmente, podemos cambiar el color estándar de Excel a uno de nuestro agrado. El resultado final



Si estamos satisfechos del resultado, podemos guardar el gráfico como tipo personalizado definido por el usuario. De esta manera, no tendremos que volver sobre el proceso de mejorar el gráfico estándar de Excel, cada vez que queramos producir un gráfico de columnas.
Seleccionamos el gráfico y abrimos el menú de Tipo de Gráfico. En la pestaña Tipos Personalizados señalamos Definido por el Usuario y pulsamos Agregar




En el diálogo que se abre definimos un nombre y una descripción para el gráfico



A partir de ahora tendremos en la pestaña de Tipos Personalizados, un nuevo modelo de gráfico que podremos elegir.

Technorati Tags: MS Excel

Extraer el ultimo elemento de un texto en una celda en Excel

Ayer un compañero de trabajo me trajo el siguiente problema. Había recibido una lista productos de nuestra empresa como esta




Mi compañero quería poner los números de catálogo en una columna y las descripciones de los productos en otra.
Como pueden ver el problema consiste en que el número de catálogo es la última palabra en la celda y el número de palabras por celda es variable. Esto hace que no podamos emplear Datos—Texto en Columnas.

La solución consiste en hacer algunas manipulaciones con funciones de Texto. Las funciones que usaremos en este caso son: DERECHA, ENCONTRAR, SUSTITUIR y LARGO.

Nuestro objetivo es construir una fórmula DERECHA(A1, número de caracteres). El problema es establecer el número de caracteres para cada celda.

El primer paso consiste en identificar el separador de las palabras; en nuestro caso es el espacio (que en fórmulas de Excel representamos " "). El número de caracteres será el número total de caracteres en la celda menos el número de caracteres hasta la última palabra, es decir hasta el último espacio.

Tomemos el texto de la celda A1



Usamos la formula SUSTITUIR(A1," ","") que da como resultado

DARKBLUENIPPLEWITHOUTTRAY022052801

Sencillamente hemos eliminado los espacios reemplazando " " por "".


Este resultado lo usamos como argumento en la función LARGO

LARGO(SUSTITUIR(A1," ","")) que da como resultado 34

Ahora usamos la fórmula

SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ","")))

Que da como resultado

DARK BLUE NIPPLE WITHOUT TRAY*022052801

Es decir, hemos puesto un asterisco * entre la anteúltima y la última palabra.
El truco consiste en que LARGO(A1) calcula el total de caracteres en la celda incluyendo los espacios en blanco (39); la fórmula LARGO(SUSTITUIR(A1," ","")) hace el cálculo sin los espacios en blanco (34). La diferencia (5) es la posición del último espacio.

Ahora podemos encontrar la posición de * usando
ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ",""))))

Que da como resultado 30

Ahora sabemos que el largo del texto es 39, y que nuestro separador de la última palabra se encuentra en la posición 30. Por lo tanto el largo de la última palabra es 9.

Así que usamos la fórmula

DERECHA(A1,LARGO(A1)-ENCONTRAR("*",SUSTITUIR(A1," ","*",LARGO(A1)-LARGO(SUSTITUIR(A1," ","")))))

Que nos da el resultado esperado.

Esta solución fue propuesta por Ken Wright y mencionada por David McRitchie

Para separar la descripción usamos la función IZQUIERDA de esta manera

=IZQUIERDA(A1,LARGO(A1)-LARGO(B1))

cuando en B1 hemos puesto la fórmula que nos da el número de catálogo.


Technorati Tags: MS Excel

Corrigiendo la posición del signo menos en números importados

Una tarea bastante corriente en Excel es importar datos de fuentes externas, como sistemas ERP (SAP, Oracle,etc) o bases de datos. Suele suceder que al importar los datos a Excel los números negativos aparezcan con el signo menos a la derecha del número; por ejemplo 550-, en lugar de -550.
El problema en estos casos no es tanto la estética como el hecho que Excel no interpreta estos datos como números, sino como texto.
Mostraremos como corregir esta situación con una fórmula.

Supongamos que hemos importado esta lista de un sistema externo



Si agregamos en la columna B la fórmula =ESNUMERO() vemos que aquellos con el signo menos a la derecha son considerados por Excel como texto.



En la celda B2 ponemos esta fórmula

=VALOR(SUSTITUIR(DERECHA(ESPACIOS(A2);1)&A2;"-";"";2))

el resultado es el valor corregido



Pasemos a explicar como funciona esta fórmula, lo que hacemos de "adentro" hacia "afuera".

ESPACIOS(A2) todo lo que hace es asegurarnos que no hay espacios en blanco después del signo menos.

DERECHA(ESPACIOS(A2);1)&A2 extrae el signo menos a la derecha del número y "lo pone" por delante de él. El resultado de esta fórmula es que el signo menos aparece por delante y por detrás del número



SUSTITUIR(DERECHA(ESPACIOS(A2);1)&A2;"-";"";2) sustituye el segundo signo menos en -5119- por un blanco, con lo que obtenemos el resultado deseado.

Finalmente usamos la función VALOR para que el resultado de SUSTITUIR sea numérico.

Esta fórmula tiene un serio problema. Si la copiamos a lo largo del rango de números a corregir, los números positivos serán deformados



Esto se soluciona agregando una función SI a nuestra fórmula

=SI(DERECHA(ESPACIOS(A3);1)="-";VALOR(SUSTITUIR(DERECHA(ESPACIOS(A3);1)&A3;"-";"";2));A3)

Lo que hace la fórmula es evaluar si el valor en la celda a corregir tiene un signo "-" a la derecha; si lo tiene usa la fórmula; si no, copia la celda como tal.



Si no queremos usar columnas auxiliares, podemos usar esta macro

Sub sustituir_menos()
Dim Celda As Range

For Each Celda In Selection
If Trim(Right(Celda, 1)) = "-" Then
Celda.Value = Val("-" & Left(Celda, Len(Celda) - 1))
End If
Next

End Sub

Para que esta macro esté siempre disponible hay que copiarla a un módulo del Personal.xls usando el editor Vba.

Technorati Tags: MS Excel

Funciones Base de Datos en Excel

Escribiendo las notas sobre Filtro Avanzado me acordé de un tema que parece haber caído en desuso en Excel: las funciones Base de Datos (Database Functions, DBase Functions).

Estas funciones permiten analizar y realizar cálculos en base a listas. En Excel "lista" es una tabla de datos que en su primera fila tiene encabezamientos.

Tal vez uno de los motivos de la pérdida de popularidad de estas funciones sea la alternativa de usar tablas dinámicas (pivot tables) y fórmulas matriciales (array formulas).

Sin embargo en la mayoría de los casos, las funciones base de datos son preferibles a las fórmulas matriciales.
Las funciones matriciales son muy poderosas pero tienen un gran inconveniente: producen problemas de recálculo. El uso intensivo de fórmulas matriciales hace que los archivos tarden en recalcularse. En este terreno, las funciones base de datos son la mejor alternativa.

Las funciones base de datos comienzan todas con BD y tienen una sintaxis en común:
Función BD(base_de_datos, campo ,criterios).

El argumento base_de_datos es la tabla/lista que contiene los datos; campo es la columna sobre cuyos datos queremos realizar el cálculo y criterios es el rango que contiene los criterios para filtrar la base de datos.

Existen 13 funciones base de datos (XL2003), entre ellas BSUMA, BCONTAR y BPROMEDIO.

Consideremos esta lista





El rango A5:E20 está asociado al nombre "alumnos"

Si queremos calcular la cantidad de alumnos que cumplen dos condiciones simultáneamente: han recibido por lo menos 80 puntos en matemática y por lo menos 75 puntos en historia, hacemos lo siguiente



Nótese que en la primer fila de la hoja hemos replicado los encabezamientos de las columnas de la tabla de datos.

En la celda E22 hemos puesto la fórmula =BDCONTARA(alumnos;1;B1:D2)

donde:

alumnos =Hoja1!$A$5:$E$20 es el rango de la tabla de datos

1 indica que queremos contar las miembros de la primer columna de la tabla

B1:D2 es el rango de criterios con los cuales queremos "filtrar" la tabla

En lugar de usar el número de columna, podemos usar el encabezamiento, con lo cual la fórmula se vuelve totalmente legible

=BDCONTARA(alumnos;"nombre";B1:D2)

Al poner las definiciones de los criterios en la misma fila, estamos indicando el uso del operador Y, es decir, todas las condiciones deben cumplirse simultáneamente.

Para usar el operador O, ponemos las condiciones en filas distintas. Por ejemplo, si queremos contar cuantos alumnos hay en la lista que recibieron por lo menos 80 puntos en matemática o 75 en historia, arreglamos la hoja de la siguiente manera



Por supuesto, también hemos modificado el rango de los criterios en la fórmula para que incluya todas las filas

=BDCONTARA(alumnos;"nombre";B1:C3)

También podemos combinar los operadores Y y O en el rango de criterios. Por ejemplo, si queremos saber cuantos alumnos hay que recibieron por lo menos 80 puntos en matemática Y 75 en historia O más de 85 de promedio, usamos el modelo



También aquí hemos modificado la fórmula para que incluya todos los criterios

=BDCONTARA(alumnos;"nombre";B1:E3)

Las funciones base de datos son fáciles de usar, una vez que hemos entendido la sintaxis básica. Son muy flexibles y permiten hacer cálculos que con más facilidad y velocidad que las funciones matriciales

La lista completa de funciones base de datos es:

BDPROMEDIO Devuelve el promedio de las entradas seleccionadas de la base de datos

BDCONTAR Cuenta las celdas que contienen números en una base de datos

BDCONTARA Cuenta las celdas que no están en blanco en una base de datos

BDEXTRAER Extrae de la base de datos un único registro que coincida con los criterios especificados

BDMAX Devuelve el valor máximo de las entradas seleccionadas de la base de datos

BDMIN Devuelve el valor mínimo de las entradas seleccionadas de la base de datos

BDPRODUCTO Multiplica los valores de un campo determinado de registros de la base de datos que coinciden con los criterios especificados

BDDESVEST Calcula la desviación estándar basándose en una muestra de entradas seleccionadas de la base de datos

BDDESVESTP Calcula la desviación estándar basándose en la población total de las entradas seleccionadas de una base de datos.

BDSUMA Suma los números de la columna del campo de los registros de la base de datos que coincidan con los criterios especificados

BDVAR Calcula la varianza basándose en una muestra de las entradas seleccionadas de una base de datos

BDVARP Calcula la varianza basándose en la población total de las entradas seleccionadas de una base de datos

IMPORTARDATOSDINAMICOS Devuelve los datos almacenados en una tabla dinámica

Technorati Tags: MS Excel

Filtro Avanzado – otros usos

Continuando al nota anterior sobre Filtro Avanzado, en esta veremos otro uso interesante de esta herramienta.
Supongamos esta lista que muestra la fecha de recepción de las órdenes de nuestros clientes y la fecha en que se efectuó el despacho




Si queremos obtener una lista de los despachos que han tardado más de 7 días en salir, aplicamos Filtro Avanzado usando esta fórmula como criterio

=(C5-B5)>7



Como hemos explicado en la nota anterior, el encabezamiento distinto del rango de los criterios debe ser distinto al de las columnas de la tabla. También podemos prescindir de encabezamiento para el criterio.



El resultado es



También podemos usar funciones más avanzadas. Por ejemplo, si queremos ver todas las órdenes que se despacharon por lo menos 5 días hábiles después de recibidas, usamos la función NETWORKDAYS

Technorati Tags: MS Excel

Funciones Texto Excel en megafórmulas

Uno de mis lectores me preguntaba si existe alguna función en Excel que, dado un nombre propio, de cómo resultado el sexo de la persona.
Semejante función no existe, pero basándonos en una lista de nombres y sexos, podemos construir una fórmula que lo haga.
El objetivo de esta nota es doble. Demostrar el uso de algunas funciones de texto y como podemos combinarlas y, una vez más, cavilar sobre el tema de "megafórmulas" versus "tablas auxiliares".

Comencemos por considerar esta tabla.




Los nombres son de alumnos de mi promoción del Colegio Nacional de Buenos Aires (y espero que nadie se moleste por publicarlos sin su consentimiento).
La idea es crear una fórmula en la columna B que de cómo resultado el sexo de la persona, basándose en el nombre propio.

El primer lugar creamos una lista que contiene nombres propios y el sexo apropiado (por suerte en castellano no hay nombres "duales").



A la tabla le asociamos el nombre "sexo"

Volviendo a la tabla de los alumnos, vemos que el apellido está separado de los nombres propios por una coma. Esto nos servirá para extraer el primer nombre propio del alumno, que a su vez nos servirá de argumento de una función BUSCARV para obtener el sexo del nombre en la tabla "sexo".

Primero mostraremos como resolvemos el problema usando tablas auxiliares. La solución es la siguiente



En la columna D ponemos esta fórmula: =ENCONTRAR(",";A2)+2
El resultado de ENCONTRAR es la posición de la coma, que separa el apellidos de los nombres propios, en la cadena de texto (apellido + nombres propios). Al sumarle 2 obtenemos la posición de la primer letra del nombre propio.

En la columna E ponemos la fórmula:

=SI(ESERROR(ENCONTRAR(" ";A2;D2+2));LARGO(A2)+D2;ENCONTRAR(" ";A2;D2+2))

Esta fórmula calcula la posición de la última letra del primer nombre propio. La función SI es necesaria para evitar que la fórmula de error si hay un solo nombre propio.
De haber más de un nombre propio basta con la fórmula ENCONTRAR(" ";A2;D2+2), que busca la posición del primer espacio después de la coma. Pero de haber un solo nombre, no hay espacio y la función da resultado ERROR. En ese caso usamos la función LARGO.

En la columna F ponemos la fórmula =EXTRAE(A2;D2;E2-D2), que se basa en los resultados de las columnas auxiliares. Como ven, aquí obtenemos el nombre propio.

Todo lo que nos queda por hacer es buscar el sexo del nombre en la tabla "sexo". Esto lo hacemos con la función BUSCARV, que ponemos en la columna B

=BUSCARV(F2;sexo;2;0)

Otra alternativa es construir una "megafórmula". Un megafórmula combina en una sola fórmula todas las funciones que hemos usado en las columnas auxiliares. La megafórmula que usaríamos es

=BUSCARV(EXTRAE(A2;ENCONTRAR(",";A2)+2;SI(ESERROR(ENCONTRAR(" ";A2;(ENCONTRAR(",";A2)+2)+2));LARGO(A2)+(ENCONTRAR(",";A2)+2);ENCONTRAR(" ";A2;(ENCONTRAR(",";A2)+2)+2))-(ENCONTRAR(",";A2)+2));sexo;2;0)

La ventaja de usar "megafórmulas" es que eliminamos las columnas auxiliares, resultando en archivos más pequeños y con menor tiempo de recálculo.
La desventaja consiste en que estas "megafórmulas" son muy difíciles, o imposibles, de interpretar.

Technorati Tags: MS Excel

Usando Filtro Avanzado en Excel

Autofiltro permite filtrar una lista de acuerdo a una serie de criterios. Por cada columna podemos elegir hasta dos criterios, combinándolos con los operadores Y (AND) u O (OR).
Autofiltro es una gran herramienta para filtrar datos, pero en ciertas ocasiones resulta limitada. En muchos de esos casos podemos utilizar Filtro Avanzado.




En una nota anterior en este blog he mostrado uno de los usos de Filtro Avanzado: extraer una lista de valores únicos.

En esta nota veremos otros usos de Filtro Avanzado. Empecemos por considerar esta lista de nombres con sus respectivas edades



Como pueden ver hemos dejado algunas líneas en blanco por encima de la lista. Esto es necesario para permitir introducir los criterios con los cuales queremos filtrar la lista.

Supongamos que queremos filtrar la lista de manera que vemos todas las personas mayores de 40 años o menores de 30 años. El primer paso consiste en copiar los encabezamientos de las columnas en la fila 1; luego escribimos los criterios en las celdas B2 y B3



Ahora abrimos el menú Datos—Filtro--Filtro Avanzado y señalamos la tabla de datos y el rango que contiene los criterios



Apretamos aceptar y Excel filtrará la lista



Para volver a mostrar todas las líneas usamos el menú Datos—Filtro—Mostrar todo



Al escribir los criterios en distintas líneas estamos indicando a Excel que queremos usar el operador O (OR). Si queremos usar el operador Y (AND) tenemos que poner los criterios en la misma línea. Por ejemplo, si queremos mostrar solo aquellos cuyo nombre empieza con la letra R y son mayores de 30 años






Nótese que usamos R* como criterio.

También podemos combinar operadores Y (AND) y O (OR)






También podemos usar fórmulas como criterio. Para esto deben cumplirse dos condiciones:
1 - que den como resultado un valor VERDADERO o FALSO.
2 – el encabezamiento del rango de criterios debe ser distinto del encabezamiento de la columna de datos

Por ejemplo, queremos mostrar sólo aquellos nombres cuya edad sea menor que la edad promedio de la lista. En el rango de criterios ponemos esta fórmula

=B6<PROMEDIO($B$6:$B$15)






Otro ejemplo sería mostrar aquellos nombres que aparecen más de una vez. Para eso usaremos la fórmula

=CONTAR.SI($A$6:$A$15,A6)>1






Otra gran ventaja de Filtro Avanzado es que nos permite copiar la lista filtrada a otra ubicación en la hoja. Volviendo sobre el ejemplo anterior, marcamos la posibilidad "Copiar a otro lugar", por ejemplo a D1






Como podemos ver, Filtro Avanzado permite filtrar listas de maneras que no serían posibles con Autofiltro.



Technorati Tags: MS Excel