Excel: Contar valores repetidos rapidamente

Partimos de una lista de nombres, algunos repetidos. Para saber cuantas veces se repite cada uno crearemos una tabla dinámica. La tabla dinámica tendrá tanto en la fila como en los datos los nombres.

Finalmente resumiremos por cuenta

Excel: Doble BUSCARV()

La función
=BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado) busca un valor específico en la primera columna de una tabla (matriz) y devuelve, en la misma fila, un valor de otra columna de la misma matriz. La V significa vertical.

En el ejemplo queremos saber cual es el precio del producto en función del mes. Si tan sólo tuvieramos un mes, por ejemplo enero, una sóla función =BUSCARV() sería suficiente:

=BUSCARV(A2;$E$22:$F$26;2;0)

Pero en este caso tenemos tres meses, con lo que la función anterior no es suficiente. Recurrimos a utilizar la función =BUSCARV() dos veces. así podremos saber el precio del producto en función del mes.

Creamos las siguientes dos tablas, precios y meses:

y escribimos la siguiente fórmula:

=BUSCARV(A2;$E$22:$H$26;BUSCARV(B2;$A$29:$B$31;2)+1;0)

que nos dará el precio del producto en función del mes.

Una explicación de esta fórmula es la que sigue:

Lo que tenemos es una función Buscarv() con otra ingresada en su tercer argumento, que es el indicador de columnas. Ahora el valor buscado depende del mes, y el mes cambia.

Queremos saber el precio (matriz E22:H26) del producto (columna A) en función del mes (matriz A29:B31)

=BUSCARV(Gasolina;MATRIZ PRECIO; Indicador de columna; ordenado)

Y el indicador de columna es la matriz meses. =BUSCARV(MES DE SUMINISTRO;MATRIZ MESES;COLUMNA EN LA QUE BUSCAR). Al estar ya ordenada podemos omitir el cuarto parámetro.
Y a este resultado le sumamos 1 porque la busqueda en la matriz precios se inicia en la segunda columna y no en la primera.

La plantilla fue sacada y modificada del siguiente enlace :www.excelavanzado.com

Excel: Anatomía y formateo de un gráfico (I)

Una primera cuestión a tener en cuenta a la hora de crear un gráfico es elegir el tipo de gráfico adecuado a nuestras necesidades. El 'Asistente para gráficos de Excel' proporciona los siguientes tipos de gráficos estandar:

• Columnas:Compara valores entre una o más series, normalmente a lo largo del tiempo. Por ejemplo, la evolución en el tiempo de las ventas de mi compañía respecto a la competencia.

• Barras Resalta a los ganadores y perdedores:

• Líneas: Muestra la tendencia o la relación entre valores en un periodo de tiempo:

Una manera sencilla para moverse por las diferentes partes del gráfico es seleccionar cualquier parte del gráfico y a continuación ir pulsando las teclas de flecha arriba y abajo, izquierda y derecha.

Excel permite añadir elementos dentro del gráfico como cuadros de texto. Para ello se selecciona el area de gráfico o trazado y se empieza a escribir sobre él. A continuación se pulsa Intro y Excel creará automáticamente un cuadro de texto que podremos moverlo a donde queramos

Si seleccionamos 'Area de gráfico' y pulsamos el botón secundario y en el menú contextual elegimos 'opciones de gráfico' se abrirá un cuadro de diálogo con seis fichas que nos permiten ajustar las opciones del gráfico.

Excel: Establecer un gráfico como predeterminado

La forma más fácil de crear un gráfico es seleccionar el rango de datos y pulsar F11.

El gráfico así creado utiliza todas las opciones predeterminadas de Excel. El tipo de gráfico predeterminado es el tipo de gráfico columnas. Pero Excel nos permite establecer nuestro propio gráfico personalizado como predeterminado.

Para ello hemos primero de crear nuestro gráfico.


A continuación situamos el ratón dentro del area de gráfico y pulsamos el botón secundario. Se abre un menú contextual y elegiremos la opción tipo de gráfico. Se abre el siguiente cuadro de diálogo:




En la esquina inferior izquierda pulsamos el botón 'Establecer como predeterminado'. Nos aparecerá un mensaje en el que nos consulta si queremos establecer el gráfico seleccionado como predeterminado. Haremos clic en sí.
A partir de ese momento siempre que pulsemos F11 el gráfico predeterminado que aparezca será el que hayamos elegido.

Excel: Gráficos de combinación

Los gráficos de combinación mezclan dos tipos de gráficos en un único gráfico. Se utilizan para mostrar distintos tipos de información en un sólo gráfico, para realizar comparaciones, por ejemplo, comparar los salarios de los trabajadores de dos paises según su categoría profesional. Excel 2002 incluye varios gráficos de combinación. El tipo de gráfico de lineas y columnas, que se haya en la ficha 'Tipos personalizados del cuadro de diálogo 'Tipo de gráfico', permite formatear una serie de datos a lo largo de una línea y otra en columnas.





En la categoría 'Cotizaciones' en la pestaña 'Tipos estandar' del mismo cuadro de diálogo encontrará gráficos de combinaciones.

En la categoría 'Circular' existen dos subtipos de gráficos combinados. El circular seccionado y el circular con gráfico de barras. Estos gráficos son útiles cuando existen tantos puntos de datos que hace dificil leer el gráfico. Por ejemplo:

Tenemos esta lista de 14 elementos, Si nos fijamos más de la mitad representan tan sólo el 8% del total con porcentajes individuales del 1%. Representar todos estos datos en un gráfico puede hecerlo ilegible

Pero podemos utilizar un gráfico circular con subgráfico de barras para combinar las partes más pequeñas en una sóla parte grande, y luego mostrar el detalle en un gráfico independiente conectado con el original

En primer lugar abriremos el cuadro de diálogo 'Tipo de gráfico' y seleccionaremos el tipo 'Circular'. A continuación escogeremos el subtipo que más nos convenga, en este caso el subtipo 'Circular con gráfico de barras'. Aceptamos

Una vez creado el gráfico es preciso determinar qué partes del gráfico irán en el gráfico secundario de barras. Para ello hacemos clic con el botón derecho sobre cualquiera de los datos y elegimos 'Formato de series de datos'. A continuación nos situamos en la ficha 'Opciones' que es donde vamos a configurar nuestro gráfico.

En el cuadro de texto 'dividir serie por' escogemos valor porcentual ya que los datos de la tabla están en porcentajes.

Donde pone 'segundo trazado con valores menores que' ponemos 2% ya que la mayoría de los valores de la lista están por debajo de este porcentaje. Con ello hemos agrupado en una sóla porción del gráfico principal todos los valores pequeños.

A continuación sólo nos queda definir el tamaño del gráfico de barras, 100, y la distancia de este gráfico en relación con el principal, 160. Por último activamos las dos casillas de verificación de la parte superior derecha. Aceptamos.


Habremos obtenido nuestro gráfico combinado:

Excel: Formato personalizado

En la barra de menu vamos a formato => celdas => numero => personalizada => o bien directamente pulsamos 'control + 1':

Nos aparece el siguiente cuadro de diálogo




Es en el cuadro de texto 'tipo' donde personalizamos nuestros datos. Excel nos proporciona ejemplos en el cuadro de abajo que pueden servir como punto de partida para nuestros formatos.

Los formatos personalizados utilizan codigos de formato para indicarle a Excel cómo mostrar los datos, (números, caractéres, lugares decimales, etc).
Cada formato personalizado puede incluir hasta cuatro secciones separadas por punto y coma.
Con la primera sección se establece el formato para números positivos, con la segunda para números negativos, con la tercera para valores cero y con la cuarta para texto.

Un primer ejemplo para comprender esto es seleccionar una celda y escribir el siguiente formato en el cuadro de texto 'tipo':

"positivo";"negativo";"cero";"texto"

A continuación pulsamos aceptar e introducimos números o texto en la celda. Si el número es negativo se aplicará el formato "negativo" y así según el dato introducido.

Si introducimos sólo una sección, este formato se aplicará a todos los números que se introduzcan.
Si queremos saltarnos una sección, por ejemplo, que no se impriman los números negativos insertaremos un punto y coma para la sección que nos queremos saltar:

#.##;;"cero"

Es importante recordar que el verdadero valor de la celda es el que está en la barra de fórmulas.

Excel: Gráficos de comparación

Me refiero a aquellos gráficos que adoptan la forma de pirámides de población, en la que se muestran los datos enfrentados.

Partimos de la siguiente tabla de datos.

Para crear el gráfico existen varias formas. Una sencilla es poner los valores de la columna de la derecha en negativo pero dándoles el formato de positivo. Recordar que el verdadero valor del dato no es el que figura en la celda sino el que figura en la barra de fórmulas.

Para convertir los números en negativos seleccionamos una celda cualquiera fuera del rango A1:C9 y escribimos en la barra de fórmulas -1. A continuación copiamos el valor, seleccionamos el rango C3:C9 y aplicamos pegado especial con la opción de multiplicar activada.

El siguiente paso es 'camuflar' estos números de manera que en la hoja aparezcan como positivos aún cuando sean negativos. Para ello volvemos a seleccionar de nuevo el rango C3:C9 y accedemos a la pestaña números del cuadro de diálogo celdas (Control + 1). En el cuadro de texto 'categoría' seleccionamos 'personalizada' y en el cuadro 'tipo' escribimos el siguiente formato:

#.##0;#.##0

Damos a aceptar y veremos como los datos de la columna mujeres aparecen en positivo aún cuando en la barra de fórmulas sigan siendo negativos.

El siguiente paso es seleccionar el rango A2:C9 y pulsar en el icono de gráfico o bien 'insertar => gráfico'.

Seleccionamos el tipo de gráfico barras y el subtipo "barras apiladas". Pulsamos en siguiente. Le ponemos un título y finalmente pulsamos en 'finalizar'

Por último podemos formatear el gráfico para hacerlo más vistoso.

Son muchas las opciones para formatear. Aquí sólo me limitaré a apilar las barras. Para ello situo el ratón encima de cualquiera de las barras del gráfico y hago doble click. Aparece un cuadro de diálogo. Me voy a la pestaña 'opciones' y dejo el valor de 'superposición' en 100 y el ancho de rango en 0. Pulso 'Aceptar' y el resultado será el siguiente: