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:

Excel: Solver, resolución de ecuaciones

La función Solver (Barra de menús=> Herramientas=> Solver, si no la encuentras activa la casilla de verificación Solver de Barra de menús => Herramientas => Complementos) nos permite resolver ecuaciones matemáticas. Veamos un ejemplo:

Sea el siguiente sistema de ecuaciones:

-x + 4y = -1

2x - 3y = 0

En primer lugar escribiremos en la hoja de cálculo los nombres de las incógnitas. En segundo lugar crearemos un espacio donde plantear las ecuaciones:

En el rango A1:B2 obtendremos los valores para cada una de las incógnitas. En el rango A6:A8 incluiremos las ecuaciones en una estructura que Excel comprenda.

En la celda a7 escribiremos la siguiente fórmula:

=-1*A2+4*B2

Y en la celda A8:

=2*A2-3*B2

Lógicamente, al estar vacías las celdas A2 y B2 el resultado de estas dos ecuaciones es igual a 0

A continuación vamos a Barra de menús => Herramientas => Solver y nos aparece el siguiente cuadro de diálogo:

Lo rellenaremos del siguiente modo:

En el cuadro de texto 'Celda objetivo' marcaremos cualquiera de las celdas del rango A7:A8. Por ejemplo, hacemos clic en la celda A7.

Seguidamente activamos la casilla de verificación 'Valores de' y en su correspondiente cuadro de texto introducimos la solución correspondiente a dicha ecuación, es decir, el valor -1.

A continuación vamos al cuadro de texto 'Cambiando las celdas' y seleccionamos las celdas del rango A2:B2. Pulsamos en 'agregar'.

Nos aparece el cuadro de texto 'Agregar restricción' que cubrimos del siguiente modo:

En el cuadro de texto Referencia de celda marcamos la celda donde escribimos la segunda ecuación (A8), ponemos el símbolo igual en la lista desplegable del medio, y por último en el cuadro 'restricción' escribimos el resultado de la segunda ecuación:

Pulsamos en aceptar y obtenemos:

Pulsamos en resolver:

Y habremos obtenido los valores de las incógnitas. Fijarse que debe estar activada la casilla de verificación 'Utilizar solución de Solver'.

Por último pulsamos en aceptar.

Excel: Autoescala en los gráficos

Cuando creamos un gráfico y disminuimos su tamaño también se reducen todos sus elementos, de modo que el texto de los rótulos puede resultar casi ilegible. Para evitar esto y mantener su tamaño Excel posee la casilla de verificación 'Autoescala' que si la desactivamos permitirá conservar el tamaño original del rótulo.


Partimos de los siguientes datos:





Y creamos el correspondiente gráfico

Ahora, si seleccionamos el gráfico y reducimos su tamaño veremos cómo también se reducen todos sus elementos, en particular la leyenda o el formato de los ejes haciéndose practicamente ilegible.

Para evitar esto nos situamos sobre el elemento que queremos que conserve su tamaño, por ejemplo, el título del gráfico. Hacemos doble clic sobre él y en el cuadro de texto que aparece, en la pestaña fuente, desactivamos la casilla de verificación 'autoescala'.






Aceptamos los cambios y volvemos al gráfico. Lo seleccionamos de nuevo y posicionándonos en los tiradores (cualquiera de las cuatro esquinas) arrastramos el ratón hacia el interior del gráfico. Veremos como se reduce su tamaño pero se conserva el del título del gráfico.

Eliminación rápida de filas o columnas

Una forma rápida para eliminar filas o columnas es la siguiente.

Eliminar filas:

En primer lugar seleccionamos la fila que queremos eliminar (Mayúsculas + barra espaciadora).

A continuación pulsamos las teclas Control y la que representa el signo de la resta.

La fila desaparecerá.

Si queremos que aparezca una nueva fila pulsamos Control y la tecla que representa el signo de la suma.

Eliminar columnas

Primero nos posicionamos en una celda de la columna que queremos eliminar y seleccionamos la columna (Control + barra espaciadora)

A continuación pulsamos las teclas control y la que representa el signo menos.

La columna desaparecerá

Si queremos añadir nuevas columnas pulsaremos Control y la tecla que representa el signo de la suma

Para seleccionar varias filas o columnas primero seleccionamos la fila o la columna y luego, con la tecla mayúsculas presionada pulsamos la teclas de flecha del cursor.

Excel -Validación

Con la validación se garantiza que en la celda de la hoja de cálculo sólo se introduzcan determinados datos que nosotros queremos. De modo que si se introduce uno que no está validado aparecerá un mensaje advirtiéndonos del error. La validación es conveniente cuando trabajamos con grandes cantidades de datos.

Partimos de una lista de alumnos.

A continuación en las celdas C1 y D1 escribimos respectivamente Alumnos y Calificación.

Seleccionamos la columna C y vamos a la barra de menús => Datos => Validación.

Aparece el cuadro de diálogo 'Validación de datos' que tiene tres pestañas.

En la pestaña configuración, en el cuadro de texto 'Permitir' escogemos la opción personalizada. Aparecerá entonces un nuevo cuadro de texto titulado 'fórmula'. Introduciremos la siguiente fórmula.

=ESTEXTO(BUSCARV(C1;A:A;1;FALSO))

A continuación vamos a la pestaña 'mensaje de error'. En el cuadro de texto 'título' escribiremos el texto que queramos que aparezca en la barra de título del mensaje de error, por ejemplo "valor repetido". En el cuadro 'mensaje de error' escribiremos el mensaje de error, por ejemplo: "Sólo está permitido introducir nombres incluidos en la columna A"

Excel: Copiar valores únicos

Se trata de copiar de una lista con valores duplicados sólo los valores únicos.
En primer lugar creamos la lista:

A continuación vamos a la barra de menú=> Datos => Filtro => Filtro avanzado y en el cuadro de diálogo que aparece ingresamos el rango de la lista en el cuadro de texto de nombre 'Rango de la Lista', activamos la opción copiar a otro lugar, en el cuadro de texto 'Copiar a' escribimos la celda a partir de la cual queremos que se copien los valores y por último activamos la casilla de verificación 'Sólo registros únicos