Excel Avanzado

Excel Avanzado

Este es un blog de excel y es la continuación del blog anterior (Blog Anterior ) dándole continuación a las opciones avanzadas de excel que no fueron explicadas anteriormente.

Opciones avanzadas de la pestaña inicio de Excel

Ordenar y Filtrar

Cada encabezado de columna de una tabla en Excel tiene un botón de flecha en su extremo derecho el cual es conocido como el botón de filtro y cuando hacemos clic sobre él se muestran diferentes opciones para ordenar y filtrar datos en Excel. Esto permite ordenar los datos de una manera más fácil para su uso.

Ordenar de forma ascendente (A a Z)

Esta opción te permite ordenar las palabras de forma ascendente en relación al abecedario de una manera más llana esta opción te permite ordenar las palabras que inician con la letra A, después con la letra B y así sucesivamente hasta llegar a la letra Z.

Ordenar de forma descendente (Z a A)

Esta opción te permite ordenar las palabras de forma descendente en relación al abecedario de una manera más llana esta opción te permite ordenar las palabras que inician con la letra Z, después con la letra Y y así sucesivamente hasta llegar a la letra A.

Filtrar por color

Esta opción te permite ordenar los datos que posee la tabla mediante los colores que tengan .Si no tiene colores no podrás usar esta opción. Fíjense en los colores que tiene la tabla y el orden en que los presenta en la tableta de colores.

Filtras de texto

Esta opción te permite poner distintos tipos de condiciones dependiendo de la condición que usas (es igual a,no es igual a,comienza por entre otras opciones que se muestran en la tablas de condiciones).

Buscar 

Esta opción te permite buscar un tipo de registro en específico por ejemplo si deseamos buscar solo los datos de Angela ponemos el Nombre en la barra de búsqueda y aparece ese solo registro en la tabla.

Seleccionar todo

Esta opción va de la mano con buscar ,pero que esta te permite solo con un clic seleccionando y de-seleccionando casillas puedes buscar los registros que te interesan.

Formato condicional

Esta es una herramienta que permite agregarle un formato especial a una o varias celdas de acuerdo con una condición que le coloquemos a las celdas.

Reglas para resaltar celdas

En estas reglas puedes resaltar el contenido de las celdas colocando diversas reglas en las cifras pertenecientes a las celdas pudiendo poner si (Es mayor que 20) ponerlo de color azul las letras.(Si es menor) que puedes ponerle un formato como un relleno de color azul y el texto de color rojo y así sucesivamente. Pudiendo usar los caracteres que están (entre) dos números por ejemplo buscar los números que están entre 50 y 100 y así sucesivamente.

Dependiendo del tipo de formato se aplican las reglas pudiendo decir Es igual a 100 te seleccionan los caracteres que son iguales a 100.También usando pudiendo seleccionar los textos que contienen una letra en específico con la opción Texto que contiene una cierta letra.

También podemos darle formato a una fecha en específico por ejemplo podemos buscar la fecha de ayer y darte y formato de fondo azul.Como también le podemos poner un color de letra  a los caracteres especiales con la opción Valores duplicados.

Reglas para valores superiores e inferiores

1. La regla 10 superiores te permitirá resaltar de manera inmediata las celdas que contengan los 10 valores con mayor valor dentro del rango de celdas.
2. La regla 10% de valores superiores es similar a las reglas anteriores, pero en lugar de indicar un número específico de celdas se introduce un porcentaje de celdas a seleccionar.
3. La regla inferiores te permitirá resaltar de manera inmediata las celdas que contengan los 10 valores con menor valor dentro del rango de celdas.
4. La regla 10% de valores inferiores es similar a las reglas anteriores, pero en lugar de indicar un número específico de celdas se introduce un porcentaje de celdas a seleccionar.
5. Por encima del promedio esta te permite resaltar las celdas que estén por encima del promedio de un rango de celdas.
6. Por debajo del promedio esta te permite resaltar las celdas que estén por encima del promedio de un rango de celdas.

Barras de estados

Las barras de datos, las escalas de colores y los conjuntos de iconos son formatos condicionales que crean efectos visuales en los datos. Estos formatos condicionales facilitan la comparación de los valores de un rango de celdas al mismo tiempo.

Escalas de colores

Con esta opción podemos  identificar visualmente los valores numéricos que se aproximan tanto al valor máximo como al valor mínimo dentro de un rango. La tonalidad de cada celda dependerá de su cercanía con cualquiera de estos dos extremos.

Conjunto de iconos

 Esta opción contiene una gran cantidad de iconos los cuales puedes asignarles a las celdas dependiendo de la condición que coloques.

Nueva regla

Esta opción como su nombre lo anuncia ,te permite crear una nueva regla a un rango seleccionado dándole un formato personalizado utilizando los formatos a tu gusto o dependiendo de la acción que estés realizando.

Borrar reglas

Con esta opción no hay que hablar mucho,ya que el nombre define su función y esta es borrar las reglas de un rango de celdas seleccionando .Como también puedes eliminar las reglas de toda la hoja de calcula dejando el formato de texto intacto , pero eliminando las reglas que tiene.

Las otras opciones que están inactivas hasta que inserte una tabla normal o una tablas dinámica por medio de la paleta insertar.

Administrar reglas

Esta opción hace referencia a su nombre, ya que  te permite administrar las reglas que has colocado en un rango de celdas que selecciones.

Opciones avanzadas de la pestaña insertar de Excel

Tablas dinámicas

Es una de las herramientas de visión de datos que sirve para agrupar información  de las tablas y realizar una tabla que permita una mejor organización y comprensión de la información para que el público o las personas a las que se la presentan entiendan de manera rápida.

En excel se hacen de la siguiente manera

1.Primero vamos a la paleta insertar y le damos clic  a la opción donde dice tabla dinámica que se ubica en el bloque tablas.

2.Luego seleccionamos la tabla o la celdas de donde queremos hacer la tabla.

3.Ya hecho esto , seleccionamos la ubicación de la tabla dinámica existen dos opciones en la en otra hoja o en la hoja existente puede elegir según tu gusto.y le das a aceptar.

4.Después seleccionamos con un clic las columnas o filas que queremos mostrar en la tablas dinámica.

Tablas dinámicas recomendadas

Estas son tablas que ya están prediseñadas y solo necesitan que le des clic para añadirlas.

Se realizan los mismos pasos que con la otra pero que debes de seleccionar un diseño.

 Gráficos

Estos en excel son una de las herramientas más útiles para analizar datos. Son Representaciones gráficas de datos que pertenecen a una tabla, permitiendo así un mejor comprendimiento de los datos de una manera más fácil.

En excel los gráficos se presentan de distintas maneras o tipos algunos de ellos son:

Gráfico circular o de anillo:Este gráfico en excel mayormente se utiliza para mostrar las proporciones de total y es recomendado usar cuando el total de los números sea 100%.

Gráfico de columnas o barras: Este gráfico está comprendido por unas barras o columnas. Estos gráficos se utilizan para hacer comparaciones de ciertas cantidades en una tabla.

Gráfico de líneas: Este tipo de gráfico es usado mayormente para mostrar tendencias en el tiempo(día,mes,año). Este se basa en las líneas para hacer su representación.

Gráfico de brújula: Este tipo de gráfico se utiliza para hacer una comparación entre dos números o más, reflejándose como si fuesen punto en el plano cartesiano.

 Gráfico de cascada: Un gráfico de cascada muestra un total acumulativo como valores son sumar o restar. Es útil para comprender cómo afecta un valor inicial (por ejemplo, los ingresos netos) mediante una serie de valores positivos y negativos.

Gráfico de estadísticas: Estos gráficos permiten representar las ganancias de una empres, exponiendo en ella las subidas y las bajadas de los ingresos.

Gráfico combinado: Este tipo de gráfico sirve para resaltar elementos haciendo combinaciones de distintos elementos como por ejemplo combinar (líneas y columna agrupada).

Biblioteca de formulas en excel

Insertar Función

Te permite buscar fácilmente una función ingresando la descripción de lo que estás buscando.Edita la fórmula de la celda actual seleccionando las funciones y editando los argumentos.


Autosuma

 Adiciona los datos de las celdas seleccionados y muestra en un campo contiguo el resultado.Con este comando puedes obtener automáticamente el resultado de funciones comunes.

Usadas Recientemente

 Nos muestra una lista detallada de las últimas funciones usadas en la edición del documento.Osea esta opción te permite las funciones con las que has trabajado recientemente.

Financieras

 Examina y selecciona una lista de funciones utilizadas para realizar operaciones financieras como:( Tasa Nominal, Cupón).Esta opción se basa en lo que son las finanzas ya que, contiene funciones para cálculos financieros como determinar un pago (PAGO) o la tasa de interés de un préstamo (RATE).

Lógicas

 Examina y selecciona una lista de funciones utilizadas para resolver problemas matemáticos de lógica como :( Falso o Verdadero,Si,No).Teniendo un poco más de certeza las funciones en esta categoría evalúan los argumentos por valor o condición. Por ejemplo, si una orden es superior a $50 se cobra $4.99 por envío. Pero si es superior a $100, no se cobra.

 Texto

 Examina y selecciona una lista de funciones utilizadas para cambiar caracteres al cuerpo de las letras del documento como : (Mayusculas a Minusculas o reemplazar textos).

Fecha y Hora

 Examina y selecciona una lista de funciones que nos ayudan para actualizar la fechas al documento como : (Dia, Semana, Mes, Año)

Búsqueda y Referencia

 Examina y selecciona una lista de funciones que nos ayuda a encontrar referencias o antecedentes del documento que estamos realizando.Por ejemplo, puedes agregar un hipervínculo a una celda o ver el valor de la intersección de una fila y columna.

Matemáticas y Trigonometría

 Examina y selecciona una lista de funciones Matemáticas ( Adicion, Sustraccion, Multiplicacion, Division,)y Trigonométricas como (Seno, Coseno, Tangente, Cotangente, Secante, Cosecante).

 Más Funciones

 Contiene funciones adicionales que no están en las ya mencionadas estas se ubican dentro de las categorías de Estadística, Ingeniería, Cubo, Información y Compatibilidad.



 Paleta Datos

Herramientas de datos

Texto en columnas

 Nos permite separar el texto que se encuentra en una sola columna en diferentes columnas, procederé a explicar con un ejemplo:

Paso 1. 


Seleccionamos la celda que deseamos separar en varias columnas. Damos click en donde dice texto en columna y después le damos donde dice “Siguiente”.

Paso 2.
Aquí lo que tenemos que hacer para separar en columnas en darle a espacio y después le damos a “Siguiente”.

Paso 3.

Ya para terminar le damos al botón que dice finalizar ,cabe destacar que en este paso podemos seleccionar el formato de los datos que estarán en las columnas.

Resultado de la división del los nombre y el apellido.

Relleno rápido

Esta opción (como su nombre nos indica) nos permite  rellenar las celdas “vecinas”, es decir las que estén al lado, con información que sea semejante. Ejemplo

Paso 1.

Lo que debemos de hacer es seleccionar las celdas que rellenaremos, para que tome el formato de la celda vecina.

Lo que debemos de hacer es darle a relleno rapido.

Paso 2.

Ya como resultado vamos a obtener el relleno de forma automática de las celdas seleccionadas.

 Quitar duplicados

Esta opción te permite quitar los datos que están duplicados dentro de un rango seleccionado.

Paso 1.

Seleccionamos el rango de celdas que queremos quitar los valore duplicados.

Paso 2.

Después le damos a la opción quitar duplicados con las celdas seleccionadas.Te aparecera un menu y le das a aceptar.

Paso 3.

 Aqui excel te enviará un mensaje un mensaje diciendote la cantidad de valores duplicados que se van a eliminar y la cantidad de valores que son permanentes.

Validación de datos

Esta opción nos permite revisar los datos dentro de una tabla o varias tablas verificando si están dentro de un rango específico.

Paso 1.

Seleccionamos el rango de celdas a validar y le damos clic a  la donde dice validación de datos .

Paso 2.

En este paso le vamos a dar clic en donde dice permitir para especificar el tipo de datos que vamos a utilizar.

Donde dice datos y debajo entre vamos a dejarlo asi como esta y se desplegarán dos opciones ,en la primera colocaras el valor mínimo y el la segunda opción vas a colocar el valor máximo y le das a aceptar.

Paso 3.

Luego le damos a validación de datos de nuevo para que se desplieguen las sub-opciones.Le damos clic en donde dice "rodear con un circulo datos no validos".

Paso 4.

El resultado de esto queda de la siguiente forma.

Agrupar 

 Esta opción te permite agrupar una cierta cantidad de celdas o filas para ocultarlas con darle solo un clic.

Para usar la opción agrupar lo que debemos hacer es seleccionar un rango de celdas luego le damos clic y verificamos si queremos agrupar las filas o las columnas y le damos a aceptar.

Desagrupar

Esta opción es contraria a la anterior ,ya que sirve para desagrupar un grupo de filas o columnas.Al terminar de desagrupar quedará igual que como estaba al principio.

Subtotal

El subtotal  se calcula con  la función de Suma o Promedio, con la función SUBTOTAL. Puede mostrar más de un tipo de función de resumen de cada columna.

Para hallar el subtotal solo tienes darle clic a la opción de subtotal y después darle clic en aceptar. 

Paleta revisar

Proteger libro

Esta opción en excel se ubica en la paleta Revisar en el bloque proteger y se utiliza para proteger un libro, para que los demás usuarios que utilizan excel no puedan editar el contenido del libro.

Para utilizar esta opción lo que debemos hacer es dar clic sobre la opción proteger libro y se desplegará un cuadro en el cual debes de poner tu contraseña ,luego de eso la vuelve a colocar para verificar que sea la correcta y le das clic en aceptar.

Proteger hoja

Esta opción en excel se ubica en la paleta Revisar en el bloque proteger y se utiliza para proteger una hoja dentro de un libro, para que los demás usuarios que utilizan excel no puedan editar el contenido de la hoja.

Para utilizar esta opción lo que debemos hacer es dar clic sobre la opción proteger hoja y se desplegará un cuadro en el cual debes de poner tu contraseña, luego de eso la vuelve a colocar para verificar que sea la correcta y le das clic en aceptar.

Uso de las principales funciones en excel

Una función es una fórmula predefinida por Excel que está hecha para realizar una operación específica .La estructura de la operación es el signo de igual el nombre de la operación y el rango de celdas que se coloca dentro de una paréntesis ,colocando la primera celda del rango dos puntos  después la segunda.

El resultado de la operación es dado en la celda que hayas colocado la fórmula.

Suma

Aquí en este ejemplo se muestra como se realiza una suma en excel. Para realizar la suma solo tienes que utilizar la sintaxis de las operaciones.Colocas el signo de igual despues escribes el nombre de la función que vas a realizar (en este en este caso es la suma),despues abren  y cierran  paréntesis dentro de estos escribes el rango colocando  la primera celda después dos puntos(:) y  finalmente la segunda celda luego le das a enter.

En caso de que sean dos celdas o dos números en especifico, solo tienes que Colocar el signo de igual despues escribes el nombre de la función que vas a realizar ,después abren  y cierran  paréntesis dentro de estos escribes las dos celdas a sumar colocando la primera celda después punto y coma (;) y luego el segundo valor, para que te dé el resultado tienes que darle a enter.

Sumar.si

Esta función suma los valores dependiendo del valor de los números y una condición que le pongas al rango de números por ejemplo si es mayor que ,menor que ,igual etc.

Aquí en este ejemplo se muestra como se realiza una suma utilizar la función (Sumar.si).Para poder usar esta función correctamente tienes que usar la la sintaxis de las operaciones. Colocas el signo de igual despues escribes el nombre de la función que vas a realizar (en este en este caso es la suma.si ),despues abren y cierran paréntesis dentro de estos escribes el rango colocando la primera celda después dos puntos(:),luego de esto colocamos el criterio que le pondremos un ejemplo de un criterio seria (<90) o (=95) y para obtener el resultado presionamos enter.

Ahora

Esta función en excel no se utiliza para hacer ninguna operación matemática sino para colocar el número de serie de la fecha y hora actuales. Si el formato de celda es General antes de especificar la función, Excel cambia el formato de celda para que coincida con el formato de fecha y hora de la configuración regional y cada vez que entras al documento la hora se actualiza.

La sintaxis para esta fórmula es la utilizada para cualquier otra fórmula matemática .Se coloca el signo de igual, después se coloca la función y después los paréntesis sin ningún contenido dentro.Para obtener la hora y fecha actual le das enter y se pondrá la fórmula que posee la máquina en ese momento. 

Aleatorio

Esta función en excel trae los valores que sean mayor o igual que cero(0), pero que sean menor que uno(1).

La sintaxis para esta fórmula es la utilizada para cualquier otra fórmula matemática .Se coloca el signo de igual, después se coloca la función y después los paréntesis sin nada dentro de los paréntesis.Para obtener los números aleatorios sólo tenemos que darle a enter y aparecen de forma automática, pero cada vez que le des clic a cualquier celda se actualizarán los numero.

Aleatorio.Entre

Esta función es parecida a la función anterior, pero a diferencia que en la otra esta  nos permite colocar valores aleatorios entre ciertos valores que especifique.

La sintaxis para esta fórmula es la utilizada para cualquier otra fórmula matemática .Se coloca el signo de igual, después se coloca la función y después los paréntesis con los dos números dentro separados por comidas primero se coloca el numero menor y después se coloca el número mayor dentro del rango que establezcas. Para obtener los números aleatorios sólo tenemos que darle a enter y aparecen de forma automática, pero cada vez que le des clic a cualquier celda se actualizarán los números.

Año

Esta función se encarga de extraer el año de cualquier tipo de fecha buscando.

La sintaxis para esta fórmula se utiliza de la siguiente forma.Primero se coloca el signo de igual, después se coloca la función es este caso es (año) y después los paréntesis y dentro de ellos colocas la celda con la fecha.

CONTAR

En excel la función contar   es muy útil, ya que nos permite contar la cantidad de celdas que contienen números dentro de un rango de celdas seleccionado. 

La sintaxis para esta fórmula se utiliza de la siguiente forma.Primero se coloca el signo de igual, después se coloca la función (contar), luego colocamos los paréntesis y dentro de estos colocamos el rango de celdas que deseamos contar ,ya para finalizar solo tienes que darle a enter.

CONTAR.SI

En excel la función contar   es muy parecida a la anterior  ya que nos permite contar la cantidad de celdas que contienen números dentro de un rango de celdas seleccionado.Pero la sintaxis es diferente.

La sintaxis correcta de esta función seria: primero colocar  el signo de igual despues escribes el nombre de la función que vas a realizar (en este en este caso es la CONTAR.SI), despues abren y cierran paréntesis dentro de estos escribes el rango colocando la primera celda después dos puntos(:), luego de esto colocamos el criterio que le pondremos un ejemplo de un criterio seria: (<90) o (=95) y para obtener el resultado presionamos enter.

BUSCARV

La función buscarv es una potente función de Excel que te permite buscar a partir de un valor específico en la primera columna de la izquierda de un rango de valores y luego obtener un valor de otra columna dentro de la misma fila.

la sintaxis para esta función es la siguiente: Primero se coloca el signo de igual, luego el nombre de la función (buscarv), después abrimos y cerramos paréntesis.

Dentro de los paréntesis lo primero que colocaremos es la celda donde se escribirá en nombre, dividimos con punto y coma después colocamos el rango de celdas, luego pondremos el número de la columna donde se encuentran los valores y por último colocamos si es verdadero o falso.

BUSCARH

La función BUSCARH en Excel busca un valor dentro de una fila y devuelve el valor que ha sido encontrado o un error #N/A en caso de no haberlo encontrado. Esta función es similar, en cierto sentido, a la función BUSCARV.

la sintaxis para esta función es la siguiente: Primero se coloca el signo de igual, luego el nombre de la función (buscarh), después abrimos y cerramos paréntesis.

Dentro de los paréntesis lo primero que colocaremos es el nombre  de lo que deseamos busca, dividimos con punto y coma después colocamos el rango de celdas, luego pondremos el número de la fila donde se encuentran los valores y por último colocamos si es verdadero o falso.


MIN

La función MIN significa mínimo y  se utiliza para buscar la cantidad mínima o el número con menor valor dentro de un rango de celdas seleccionadas.

La sintaxis para esta fórmula es primero el signo de igual, después colocamos el nombre de la función (el cual es MIN )y luego colocamos el rango de celdas donde se buscará la mínima cantidad.

MAX

La función MAX significa máximo y  se utiliza para buscar la cantidad máxima o el número con mayor valor dentro de un rango de celdas seleccionadas.

La sintaxis para esta fórmula es primero el signo de igual, después colocamos el nombre de la función (el cual es MAX )y luego colocamos el rango de celdas donde se buscará la máxima cantidad.

Promedio

La función promedio de Excel calcula la media aritmética de una serie de datos. Es decir, suma todos los valores de la variable que estamos analizando y los divide entre la cantidad de valores sumados.

La sintaxis para esta fórmula es primero el signo de igual, después colocamos el nombre de la función (el cual es promedio )y luego colocamos el rango de celdas donde se buscará el promedio.


PROMEDIO.SI

Utilizaremos esta función cuando queramos introducir una única condición implícita en la función promedio.

La sintaxis correcta de esta función seria: primero colocar el signo de igual despues escribes el nombre de la función que vas a realizar (en este en este caso es la PROMEDIO.SI), despues abren y cierran paréntesis dentro de estos escribes el rango colocando la primera celda después dos puntos(:), luego de esto colocamos el criterio que le pondremos un ejemplo de un criterio seria: (<90) o (=95) y para obtener el resultado presionamos enter.
Si

La función SI es una de las funciones más conocidas en  Excel y es por que tiene uso muy importante, ya que esta  permite realizar comparaciones lógicas entre un valor y un resultado que espera.

Por esto, una instrucción SI puede tener dos resultados. El primer resultado es si la comparación es Verdadera y el segundo si la comparación es Falsa.
La sintaxis para esta función es el signo de igual ,después el nombre de la función (SI) luego colocas el criterio, despues pones el valor verdadero y por ultimo colocas el valor falso.

CONCATENAR

Esta función de excel sirve para unir textos de una celda y de otra.

Su sintaxis es primero el signo de igual, después el nombre de la operación, luego cerramos y abrimos paréntesis dentro de ellos colocamos las celdas a unir divididas por punto y coma(;).


CONTAR.BLANCO

Esta función nos permite contar las celdas que están en blanco o sin contenido dentro de un rango seleccionado.

La sintaxis correcta de esta función es: Primero colocamos el signo de igual, despues le ponemos la función, luego colocamos el rango de celdas donde se buscará los espacios en blanco.

SUBTOTALES

La función SUBTOTALES en Excel nos ayuda a calcular el subtotal de una lista de valores. Lo interesante es que podemos indicar a la función SUBTOTALES el tipo de operación que deseamos aplicar sobre los valores.

La sintaxis correcta para que funcione es primero el signo de igual, luego la función, después abrimos y cerramos paréntesis y dentro de ellos colocamos el numero de la operación y por último separamos por un punto y coma y colocamos el rango.

ÍNDICE

La función INDICE en Excel nos ayuda a obtener el valor de una celda dentro de una matriz especificando el número de fila y columna. Esta función tiene dos formas de uso: de forma matricial y de forma de referencia.

La sintaxis para utilizar esta función es: Primero el signo de igual, luego el nombre de la funcion, despues seleccionamos el rango de celdas después colocamos un punto y coma par separa y colocamos el numero de fila que corresponde y ya por último separamos sin  un punto y coma y colocamos el numero de columna.