Contar valores únicos en un rango de Excel

En ciertas situaciones necesitamos saber cuantos valores únicos hay en un rango. Supongamos una hoja Excel donde tenemos esta tabla:



Si queremos calcular las ventas promedio por agente, tenemos que saber primero cuántos agentes hay en nuestra lista. En nuestro ejemplo hay cuatro agentes, Pedro, Roberto, Juan Carlos y Alberto, en doce filas de la tabla. Pero en la vida real nos enfrentamos con tablas que tienen cientos o miles de filas y por lo tanto necesitamos una forma más práctica de contar.
Para contar cuantos valores únicos hay en una lista usamos esta fórmula matricial:

={SUMA(1/CONTAR.SI(rango;rango))}

donde rango es un nombre que define el rango de celdas donde queremos contar los valores únicos

En nuestro caso rango = A2:A13, nuestra fórmula será

={SUMA(1/CONTAR.SI($A$2:$A$13,$A$2:$A$13))}



Esta es una buena oportunidad para volver a explicar cómo funcionan las fórmulas matriciales.

En primer lugar recordemos que al introducir estas fórmulas en una celda, apretamos simultáneamente Ctrl+Mayúsculas(Shift)+Enter. Los corchetes aparecen al introducir la fórmula de esta manera, y no deben ser puestos por el usuario.

Para ver los pasos del cálculo de la fórmula matricial, hay que pulsar el botón "Ver Detalle".

En nuestra fórmula matricial, la función CONTAR.SI crea una matriz de resultados que es número de veces que cada nombre aparece, como vemos en la columna B donde hemos introducido esta formula (no matricial)
=CONTAR.SI($A$2:$A$13,A2)



En la columna C calculamos la inversa de los valores de la columna B, por ejemplo en C2 ponemos la fórmula =1/B2

Luego sumamos todos los valores de la columna C, lo que nos da el número de valores únicos en el rango.

Todo esto es hecho en una sola fórmula, usando la técnica de fórmulas matriciales. El archivo con el ejemplo se puede descargar aquí.

Technorati Tags: MS Excel

Agrupar controles botones de opción en hojas Excel.

En la entrada sobre Agregando controles en hojas de cálculo mostré como agregar controles, tal como botones de opción o barras de desplazamiento, en una hoja de Excel.

Últimamente he recibido varias consultas sobre como crear grupos de botones de opción.
Cuando agregamos varios botones de opción en una hoja, Excel acepta una sola celda vinculada para todos los controles. Es decir, Excel agrupa todos los botones de opción en un solo grupo, no importa dónde o como los ubiquemos. Inclusive si definimos para cada botón una celda vinculada distinta, veremos que en todos los controles aparece como celda vinculada la última que hemos definido.

La técnica para superar este inconveniente depende de qué tipo de control estemos usando. Excel permite agregar dos tipos de controles directamente en la hoja: los controles de la barra de Formularios y los del cuadro de controles (controles ActiveX).




Supongamos que queremos crear un formulario para definir nuevos clientes. En este formulario definimos el nombre del cliente, el tipo (mayorista o minorista) y el tipo de crédito (15, 30 o 45 días). Nuestro formulario se verá así


Si usamos los controles de la barra de formularios, el truco consiste en poner cada uno de los grupos de botones de opción dentro de controles "cuadro de grupo" distintos. Para crear el formulario seguimos estos pasos:

1 – Ponemos un fondo gris al rango B2:F20
2 – Seleccionamos la celda C4 y ponemos "Nombre"
3 – Combinamos y centramos el rango D4:E4, y le quitamos el fondo gris
4 – Abrimos la barra de formularios y arrastramos dos cuadros de grupo



5 - Dentro de uno de los cuadro de grupo ponemos los botones de tipo de cliente. En el otro ponemos los botones de tipo de crédito. Cambiamos los encabezamientos directamente en el control.
6 – Los botones de tipo de cliente están ligados a la celda H3; los de tipo de crédito a la celda H4.
7 – Agregamos un control "botón" que nos servirá para activar una macro que agregue el nuevo cliente a nuestra base de datos (la hoja "Clientes")

En la celda I3 ponemos la fórmula =ELEGIR(H3;"Mayorista";"Minorista"); en la celda I4 la fórmula =ELEGIR(H4;"15 días";"30 días";"45 días")

Si usamos los controles ActiveX de la barra Cuadro de Controles, el truco consiste en cambiar la definición del Groupname en la propiedades del botón y darle a los botones del grupo el mismo Groupname.



En nuestro ejemplo los botones de tipo de cliente tienen como Groupname "Tipo" y los del tipo de crédito "Crédito".
Otro punto importante es que a cada control le definimos una celda vinculada distinta, a diferencia de los controles de la barra de formularios.

Sobre cómo definir los distintos controles, pueden consultar mi nota mencionada al principio.

En general es más fácil trabajar con los controles de la barra de formularios que con los controles ActiveX de la barra de cuadro de controles. La contrapartida es que los controles ActiveX tienen más posibilidades y son más flexibles a nuestras necesidades.

La hoja con los ejemplos y las macros se pueden descargar aquí.

Promedio en Excel con más de una condición

Dos lectores me consultan sobre cómo calcular con Excel el promedio de una serie de valores, sujeto a más de una condición. Uno de ellos me pide que lo muestre sin utilizar funciones Base de Datos como BDPROMEDIO. Cosa entendible ya que el uso de estas funciones es un tanto engorroso.

La técnica que sugiero es la que ya había mostrado en mi nota Sumar y contar con varias condiciones en Excel:SUMAR.SI, CONTAR.SI y SUMAPRODUCTO.

Definimos un nombre que contenga una lista de valores de un rango en una hoja de cálculo. Llamamos a este nombre "valores". En una celda ponemos el valor que será la cota inferior (nombre = "cota_inferior) y en otra la cota superior ("cota_superior").




Para sumar todos los valores comprendidos entre la cota superior y la inferior usamos la fórmula

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior)*valores)

Para contar la cantidad de valores comprendidos entre las cotas usamos

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior))

Para calcular el promedio podemos dividir entre las celdas que contienen los resultados o combinar ambas fórmulas en una sola

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior)*valores)/SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior))

Luego podemos usar Formato Condicional para señalar los valores de la lista usados en el cálculo



Nuestra hoja se vería así




El archivo con el ejemplo de promedio con más de una condición se puede descargar apretando el enlace.

Technorati Tags: MS Excel