Excel avanzado

Ordenar y filtrar

Algunas herramientas que incorpora Excel nos permiten analizar los datos de nuestras tablas. Es el caso de las herramientas Ordenar y Filtrar de Excel.

La primera, Ordenar, como su propio nombre indica nos permite ordenar una serie de datos. En el caso de datos numéricos podremos ordenar de mayor a menor, o de menor a mayor. Si las celdas contienen datos de texto podremos ordenar de la A a la Z, o de la Z a la A, en orden alfabético.
También podemos establecer un orden personalizado.

La herramienta Filtrar nos permite mostrar sólo los datos que cumplen con los criterios que hemos especificado.

Si hacemos clic en el botón Filtro, dentro del grupo de herramientas Ordenar y Filtrar de la barra Modificar, aparecerá en el encabezado de cada columna un triángulo que muestra que tenemos un desplegable. Según el tipo de filtro que apliquemos se nos mostrarán más o menos datos de nuestra tabla. Es importante saber que esta herramienta no borra ni modifica los datos de la tabla, simplemente deja de mostrar algunos datos.

En el caso de los filtros también podemos aplicar Filtros personalizados que variarán según el tipo de valor al que estamos aplicando del filtro.

Formato condicional

Para acceder al formato condicional selecciona la pestaña INICIO, después, en el grupo ESTILOS, selecciona el botón de “Formato condicional” como te enseñamos en la siguiente imagen:

RESALTAR REGLAS DE CELDAS

Como puedes ver en la siguiente imagen hay un montón de reglas de formato condicional que te ayudarán a resaltar ciertos valores de tus datos. Simplemente selecciona el rango donde están las celdas que quieres destacar y elige el tipo de formato condicional que quieras:

• Es mayor que…: Resalta las celdas que son mayores a un determinado valor.

• Es menor que…: Ídem que el anterior pero con las celdas que son menores que un determinado valor.

• Entre…: Resalta las celdas que se encuentran entre dos valores.

• Es igual a…: Resalta las celdas que son iguales a un cierto valor o texto.

• Texto que contiene….: Resalta las celdas que contienen un determinado valor o conjunto de caracteres.

• Una fecha…: Resalta las celdas cuya fecha coincide con la fecha dada.

• Duplicar valores…: Resalta aquellas celdas que contengan valores duplicados del total de las celdas seleccionadas.

De cada uno de estos modos de formato condicional podrás seleccionar el formato (color de fuente, fondo de celda, tipo de letra, bordes…) que prefieras para resaltar las celdas.

REGLAS SUPERIORES E INFERIORES

En la siguiente imagen puedes ver todos los tipos de formato condicionales que te permitirán marcar automáticamente aquellas celdas que cumplan con determinadas circunstancias:

10 superiores…: Permite resaltar los 10 valores mayores de un conjunto de celdas. Dicho número se puede variar para poder resaltar los tres mayores o cualquier otro valor.

• 10% de valore superiores…: Esta regla remarca los valores que pertenecen al 10% de los mayores valores del rango.

• 10 inferiores…: Permite marcar los valores inferiores. Puedes modificar el número de valores que quieres que se marquen.

• 10% de valores inferiores…: Permite marcar el 10% de los menores valores de un rango.

• Por encima del promedio…: Determina los valores que están por encima de la media del conjunto de los valores.

• Por debajo del promedio…: Determina los valores que están por debajo de la media de un conjunto de valores.

BARRAS DE DATOS

Este formato condicional te permite crear un pequeño gráfico en el que, por cada valor, se muestra una barra horizontal dentro de cada celda cuya longitud es proporcional al valor de la celda. Esta proporción es relativa al mayor valor del conjunto de celdas seleccionadas.

ESCALAS DE COLOR

Las escalas de color te permiten marcar en diferentes colores las celdas seleccionadas. Las celdas se colorearán en los colores elegidos dependiendo de si son mayores o menores que un determinado valor. Estos valores que marcan los umbrales de color pueden modificarse donde pone “Más reglas…”

CONJUNTOS DE ICONOS

El formato condicional del tipo conjunto de iconos te permite marcar aquellas celdas que has seleccionado con los iconos que más te gusten dependiendo de los valores relativos del conjunto de datos que tengamos.

Estos valores intermedios se pueden modificar haciendo clic al final de las opciones del Conjunto de iconos donde pone “Más reglas…”. La pantalla que aparecerá será:

Donde podremos editar la regla de formato condicional para poder adecuarla a lo que la necesitemos.

FORMATO CONDICIONAL DE UNA CELDA DEPENDIENDO DEL VALOR DE OTRA

Esto se entenderá mucho mejor con un sencillo ejemplo. En la siguiente tabla queremos que los nombres de las personas que han llegado tarde al trabajo más de tres días en el último mes se pongan en rojo. La tabla es la siguiente:

En esta tabla vemos a los jugadores del Madrid y las veces que cada uno ha llegado tarde (ojo, la información es inventada por mí para este ejemplo).

Para poner el nombre en rojo en función de las veces que cada uno ha llegado tarde seguiremos los siguientes pasos (en la columna A están los nombres y en la B están los números):

1. Seleccionar el rango de nombres en los que queremos usar el formato condicional

2. Ir a: Formato Condicional>>Nueva regla

3. Seleccionar de la lista la opción “Utilice una fórmula que determine las celdas para aplicar formato.”

4. Una vez seleccionada en la barra de fórmulas escribiremos:=B2>3


Es muy importante, si seleccionamos la celda primera de la columna en la que queremos basar nuestro formato condicional, quitar los símbolos de dólar para que no se quede fija la referencia y cada una de las celdas apunte a su “par”.

5. Una vez que hayas escrito la fórmula elige las opciones de formato que más te gusten. En nuestro caso nosotros hemos puesto negrita y color de fuente rojo. El menú de formato condicional estaría así:

6. Hacemos clic en OK para aceptar el formato y la fórmula introducidos. Por arte de magia el nombre de cada jugador que ha llegado tarde más de tres días aparecerá en rojo.

Esto mismo nos puede servir para dar formato condicional a una celda dependiendo del valor de otra, a una fila entera o una columna entera o un rango de celdas. También se puede hacer de la combinación de varios valores de varias celdas… un sin fin de posibilidades.

Tablas Dinamicas y Tablas Dinamicas recomendas

Primero, en Excel hay que seleccionar toda la información de nuestros datos. Para ello recomiendo convertir nuestro rango donde encontramos nuestra información a una tabla de datos. ¿Cómo? Muy sencillo. Selecciona el rango y ve a Ventana Insertar >> Grupo Tablas >> Tabla

Una vez que hemos hecho esto la información de nuestra tabla quedará de la siguiente manera:



Esto nos permitirá, que al hacer crecer la información de nuestra tabla en número de filas, al refrescar las tablas dinámicas, todo el rango sea cogido por la tabla dinámica en cuestión.

Pero ahora sí, para crear una tabla dinámica en excel seleccionamos nuestra recién creada tabla y hacemos: Ventana Insertar >> Grupo Tablas >> Tabla Dinámica

Al hacer click en tabla dinámica aparecerá una ventana como la siguiente:



En esta pestaña vemos que el rango/tabla que ha seleccionado. En la siguiente parte nos permitirá elegir donde situaremos nuestra tabla dinámica. En este caso vamos a permitir que cree una hoja nueva y nos coloque la tabla dinámica ahí. Haciendo click en OK lo que veremos será lo siguiente: una nueva hoja con el espacio creado para una tabla dinámica genérica y a la derecha un display para ordenar la información de nuestra tabla dinámica.

ORGANIZAR UNA TABLA DINÁMICA EN EXCEL

En la parte de la derecha vemos que en el recuadro superior se han almacenado todos los títulos de columna de nuestra tabla. Eligiendo el que queramos podremos arrastrarlo a una de las cajitas de abajo.

EN LA TABLA DINÁMICA EXCEL, ¿QUÉ SON LAS CAJITAS DE ABAJO?

Filtros de reporte:

 nos permitirá filtrar la tabla entera seleccionando uno o varios elementos de la lista del filtro que hayamos aplicado.

Columnas: nos permitirá organizar nuestra información por columnas (podremos seleccionar uno o varios elementos de la colección)

Filas: nos permite organizar nuestra información por filas (podremos seleccionar uno o varios elementos de la colección)

Valores: serán los valores de cálculo. Podremos visualizar los valores como suma, máximo, media, contar valores…

En la siguiente imagen vemos los filtros que hemos aplicado nosotros:



Hemos colocado nuestras columnas para conseguir el siguiente efecto:

• Valores: Suma de las ventas (precio) conseguido según los filtros seleccionados.

• Filtro de reporte: Mes. De esta manera podremos seleccionar el mes del que queremos conocer los datos de ventas.

• Etiquetas de filas: hemos puesto las etiquetas de los vendedores para poder ver lo que han vendido en el mes seleccionado.

• Etiquetas de columnas: veremos los productos. De esta manera se desglosará para cada vendedor los productos que ha vendido y la suma de su precio.

Esto, explicado así, es un poco abstracto, lo mejor será ver cómo queda la tabla dinámica Excel una vez que hemos hecho las diferentes selecciones:



En la imagen anterior vemos como la información de ventas queda organizada para el mes de Enero según los vendedores y los diferentes productos. Además de esto, veremos las columnas de totales de ventas por productos y por vendedores (fila inferior y columna derecha respectivamente).

Como puedes ver, crear tablas dinámicas en Excel es mucho más sencillo de lo que habías pensado nunca y en cuanto le cojas el truco podrás ir a toda velocidad sacando interesantes conclusiones de tus análisis.

Por último, ¿cómo cambio el tipo de valores mostrados? ¿y si me viniera mejor ver cómo se reparten porcentualmente las ventas? Pues es muy sencillo, tan sólo tendremos que seguir los siguientes pasos:

• Hacemos clic sobre la flechita negra en el organizador de tablas dinámicas sobre los valores que queremos cambiar (en nuestro ejemplo el valor suma de precio).

• Elegimos la opción de “Configuración de campo de valor”.

• Elegimos la pestaña de “Mostrar valores como”.

• Del desplegable de “Mostrar valores como” elegimos “% de Gran Total”.

De esta manera el resultado obtenido será:

Tablas dinámicas recomendadas

• Abre una hoja de Excel en blanco.

• Introduce una serie de datos en forma de tabla. Puedes realizar el ejercicio con los datos de la imagen.

• Sitúate con el cursor del ratón en una celda vacía.

• Pulsa arriba en la pestaña Insertar dentro del grupo de trabajo llamado Tablas.

• En el medio encuentras un botón con una tabla y un signo de interrogación ?

• A continuación, haz clic sobre ese botón qué se llama tablas dinámicas recomendadas.

• Se abre una nueva ventana llamada Elegir origen de datos.

• Elige y selecciona con el ratón el área en dónde se encuentran los datos.

• Pulsa Aceptar.

Aparecerán en la siguiente pantalla en una nueva ventana varias tablas en la zona izquierda.

Basta con situarte en una de ellas, la que mas se adapte a lo que necesitas. A continuación pulsa el botón Aceptar.

En una hoja aparte, (esa hoja se abre a la izquierda de la hoja donde tienes los datos), aparece en la tabla dinámica que has elegido.

Gráficos excel

Desde los comienzos de Microsoft Excel los gráficos han sido uno de sus puntos fuertes y por eso mismo los señores de Microsoft no paran de inventar nuevos tipos de gráficos. A continuación te explicamos cuales son los diferentes tipos de gráficos Excel que puedes crear.
1 TIPOS DE GRÁFICOS: GRÁFICOS DE COLUMNAS Y BARRAS


Los gráficos de columnas o barras son una representación muy sencilla de una o varias series numéricas.

Columnas agrupadas

En las dos siguientes imágenes vemos un gráfico Excel de columnas sencillo, en el que sólo tenemos una única serie de datos que va a de Enero a Julio (categorías). Como puedes ver, el gráfico expresa muy bien en vertical aquellos meses con mayores valores y permite comparar muy fácilmente con el resto de los meses.

Columnas agrupadas con varias series

En el siguiente gráfico hemos añadido una serie más (pongamos la del año anterior) con la que poder comparar dentro de un mes la evolución año a año. En este caso hemos mantenido los mismos meses. Es muy importante en estos gráficos de Excel de columnas agrupadas que no pongas muchas series, de lo contrario conseguirás que el gráfico sea muy difícil de leer.

Columnas apiladas

Los gráficos con columnas apiladas puede ser una manera muy sencilla de hacer una comparativa rápida. En este caso comparamos mes a mes la suma de dos series de valores.

Columnas apiladas al 100%

Pero también podemos hacer una gráfica Excel con columnas apiladas al 100% de manera que veamos, para cada mes, qué serie tiene mayor peso relativo sobre la categoría (mes).

Gráfico de barras

Este gráfico es igual que el de columnas pero con la pequeña diferencia de que el gráfico se muestra en horizontal. Hay una cosas que sigo sin entender de este gráfico y es que el último valor de las categorías aparece en la parte de arriba en vez de al revés… pero gracias a Dios el orden del eje se puede cambiar mediante un par de clicks así que tampoco es un drama.

Gráfico de barras apiladas

El gráfico de barras apiladas también es muy útil, al igual que el de columnas apiladas. Es recomendable usarlo cuando el texto de las categorías es largo. Por ejemplo, se usa mucho con las respuestas a las preguntas de una encuesta. Yo, particularmente, también lo uso mucho cuando quiero mostrar un ranking donde el primer valor siempre está en la parte superior y el último de los valores está en la parte inferior de la gráfica.

Barras apiladas al 100%

Otro gráfico que también resulta muy útil es el de las barras apiladas al 100% que funciona exactamente que el gráfico de Excel de columnas pero que a mi me parece que explica mejor el mismo contenido porque puedes ver cuanto a la derecha o a la izquierda se encuentra un valor con un pequeño truquillo.





El truco para mejor este gráfico es crear una serie intermedia con un color que resalte mucho.





2 TIPOS DE GRÁFICOS: GRÁFICO CIRCULAR (O DE QUESITOS)


Los gráficos circulares o también llamados de quesitos son unos gráficos muy usados pues representan de manera muy sencilla la proporción de una serie de valores respecto del total. Dominar este tipo de gráficos es fundamental. Nos puede ayudar a representar de manera muy sencilla una serie de valores muy complejos. Siguiendo el ejemplo de antes tendríamos.





Como puedes ver, automáticamente los valores numéricos se han convertido en porcentajes. Este tipo de gráfica con porcentajes es muy fácil de entender y, en seguida visualizamos cuál es la parte que destaca sobre el resto.


También podemos modificar un poco su formato para que se aprecien mejor todas sus características o destacar un valor.





Estos gráficos circulares son de los más utilizados en Excel.

Gráfico de anillo en Excel

Una variable que últimamente se está poniendo más de moda para hacer reportes o para valor indicadores. Es un gráfico sencillo de hacer pero no tan fácil de que sea útil. Un ejemplo podría ser el siguiente:





Es muy importante que los elementos de un gráfico en excel queden bien definidos.
3 TIPOS DE GRÁFICOS: GRÁFICOS DE ÁREAS


Unos gráficos muy interesantes y que se usan muy poco como gráficos de Excel. Realmente no es fácil usar este tipo de gráficos porque su aplicación puede resultar un poco concreta en algunos casos.

Gráfico de áreas

Como puede verse en la siguiente imagen este gráfico de Excel nos permite representar las series como valores. Por ejemplo, en este ejemplo, podríamos compara las horas dedicadas a una tarea en el Año 1 frente al Año 2. También lo podemos usar para comparar ingresos o cualquier otra comparativa.

Áreas apiladas

Este tipo de gráfico Excel nos permite acumular ciertos valores de manera muy gráfica. Es muy frecuente usarlo para ver volúmenes que se usan de manera continua. Podría usarse para ver el volumen de agua acumulado en diferentes pantanos por ejemplo. Como puedes ver, el volumen de agua acumulado en este caso disminuye en los meses de Junio y Julio.





El gráfico de áreas en excel es uno de los más utilizados, debido a la gran claridad que aporta.
4 TIPOS DE GRÁFICOS: GRÁFICOS DE LÍNEAS

Líneas

Nos permite identificar tendencias de los valores a lo largo del tiempo. Este gráfico Excel está muy indicado cuando en las categorías tenemos cualquier referencia de tiempo como días, años, meses, horas, minutos…

Líneas con marcadores

Además, podremos añadir los marcadores de los valores con o sin etiqueta de los valores. Dependiendo del número de series que tengamos convendrá tener los valores aunque si sólo queremos ver las tendencias y no los valores exactos de cada punto no será conveniente usar las etiquetas para evitar que el gráfico esté demasiado recargado.

Varias líneas

En muchas ocasiones necesitaremos comparar dos o más series de valores y ver sus tendencias. Para ello usaremos el gráfico de líneas sin usar los marcadores.

Varias líneas apiladas

Este tipo de gráfico de Excel nos permite acumular los valores de dos series y ver la tendencia conjunta. La línea superior es la suma de la línea inferior y los valores de la superior. De esta manera conseguimos ver la progresión de un conjunto de elementos.






5 TIPOS DE GRÁFICOS: GRÁFICOS DE DISPERSIÓN

Gráficos de puntos (o dispersión)

Estos gráficos son la representación gráfica de una serie de coordenadas. Por ejemplo, pueden servirnos para ver la situación de una serie de fábricas. En el eje de las x podemos tener el volumen de fabricación y en el el eje de las y (vertical) el número de horas de mantenimiento requeridas. Este tipo de gráfico está especialmente recomendado para comparar un conjunto de datos con dos características diferentes. Por ejemplo, también podríamos comparar el precio y el margen de un conjunto de productos…

Gráficos de burbuja

Este gráfico de Excel es muy similar al anterior con la salvedad de que podemos introducir una tercera variable que nos permitirá hacer una mejor comparación. Esta tercera variable está representada por el tamaño del punto como puede verse en la siguiente imagen.




6 TIPOS DE GRÁFICOS: GRÁFICO COMBINADO


Los gráficos Excel combinados son mis favoritos. En estos gráficos podemos tener dos de los gráficos anteriormente explicados en un único gráfico. Normalmente se usan para representar variables que son completamente diferentes. Por ejemplo, podríamos comparar el porcentaje de dedicación a una tarea frente al resto y el coste de dicha tarea. Como ves, no parecen características similares y por eso las usamos con gráficos de diferente naturaleza.





Además, si queremos hacer una comparación de valores que no tienen un orden de magnitud similar podremos usar el eje vertical de la izquierda como eje secundario:





En la gráfica anterior hemos modificado los datos de la serie “Superficie” para que esté expresada en porcentaje. Como el Volumen está medido en unas unidades y la superficie en porcentaje parece indicado modificar el tipo de gráfico para que sea mas fácil de entender al tener dos ejes diferentes.
7 TIPOS DE GRÁFICOS: GRÁFICO DE ARAÑA


El gráfico de araña en Excel es muy poco usado, yo diría que casi nunca se usa excepto en alguna rara circunstancia. Por ejemplo, este tipo de gráfico de Excel puede usarse cuando tengamos una serie de características que queramos comparar.


Siguiendo con el ejemplo de los valores según los meses del año, el gráfico de araña se vería de la siguiente manera en Excel:





Como puedes ver, mediante este gráfico somos capaces de comparar visualmente unos valores. Por ejemplo, suele usarse para valorara diferentes resultados en una encuesta y su comparativa con el año anterior. Es decir, no sirve para comparar los diferentes meses como vemos en el ejemplo anterior.
NUEVOS GRÁFICOS EN EXCEL 2016
8 TIPOS DE GRÁFICOS: GRÁFICOS ESTADÍSTICOS


Microsoft Excel 2016 ha decidido poner este nombre a dos tipos de gráficos muy particulares usados, sobretodo, en estadística.

Histograma

El gráfico histograma está muy relacionado con los conjuntos de valores de una única serie de datos histórica. Por ejemplo, se usa mucho para comparar series pluviométricas (de lluvias) en una región a lo largo de un año.





Como puedes ver, la separación entre las diferentes categorías de la gráfica no existe. Además, este tipo de gráfico Excel nos permite agrupar según: ancho del rango o número de rangos para poder hacer otro tipo de valoraciones como los gráficos de frecuencia.

Gráfico de Pareto

Este diagrama muestra una distribución a modo de Histograma más una línea que son los valores acumulados de la serie. Como puede verse, la línea acaba en el 100%. Lo importante de esta línea es mostrar el comportamiento de los valores mientras se va acumulando.





Estos son los dos tipos de gráficos en excel 2016 más novedosos.

9 TIPOS DE GRÁFICOS: GRÁFICOS DE CASCADA


Este es un gráfico que cada vez se usa más. Aunque no se suele usar con series de tiempo sino con elementos que se acumulan o se restan… por ejemplo, en el mundo financiero se usa mucho o en la consultoría. Este gráfico refleja como diferentes elementos se van acumulando sobre el total y lo van disminuyendo otros elementos.





Las características tan peculiares de este gráfico en excel hacen que tenga este aspecto tan peculiar.

10 TIPOS DE GRÁFICOS: GRÁFICOS DE JERARQUÍA


Este tipo de gráfico de Excel es muy interesante pues te permite mostrar aquellos valores que sobresalen del resto de una manera muy visual.




EL MAPA DE LOS GRÁFICOS


Estoy seguro de que después de ver más de 10 tipos de gráficos de Excel diferentes te sientes un poco mareado, pero no te preocupes. Hemos preparado el mapa de los gráficos para que puedas guiarte en tú aventura con las gráficas para que siempre puedas apoyarte en ello a la hora de hacer gráficos en Excel. Este mapa te servirá para saber qué tipo de gráfico es mejor en cada situación. Guárdalo porque es el mapa que te llevará hacia el gran tesoro: acertar con el tipo de gráfico de Excel que necesitas en cada momento.





Casi todos los tipos de gráficos que hemos visto en esta guía pueden hacerse en 3D, o al menos, dar aspecto de relieve a las diferentes formas. Te aviso desde ahora, tengo una manía persecutoria contra los gráficos 3D. No se muy bien de donde viene pero lo que sí que te puedo decir es que no he visto gráficas más feas que las que son en 3D. Te garantizo que he visto muchos gráficos y todavía no he visto un informe que tenga gráficos en 3D hechos con Excel que no me hayan hecho tener pesadillas.


Ahora bien, otra cosa son los gráficos con relieve, esos sí que me gustan. Y si los haces bien pueden tener un efecto muy chulo, muy moderno. Por ejemplo, un gráfico de columnas como el que veíamos antes puede ser muy sencillo de hacer y, en ocasiones, puede parecer poco trabajado. En la siguiente imagen vamos a ver un gráfico hecho con relieve. Bueno, una comparación del antes y el después y estoy seguro de que te gustará lo que vas a ver.





No es que sean una obra de arte pero estoy seguro de que el gráfico de la derecha te parece mucho más trabajado. Efectivamente, tiene unos pocos cambios además de poner las columnas con relieve pero ya te digo que la diferencia entre uno y otro pueden ser 30 segundos, no más.
COMO HACER GRÁFICOS EN EXCEL


Hacer un gráfico en Excel es una tarea muy sencilla. Para ello no necesitas más que una pequeña tabla con datos como la siguiente:





Una vez seleccionado el rango de la tabla seleccionaremos la pestaña “Insertar” y después iremos al grupo “Gráficos” donde seleccionaremos uno de los gráficos que nos ofrece Excel y que hemos explicado anteriormente. Los pasos que debes seguir puedes verlos en esta imagen:




Un vez que seleccionemos el tipo de gráfico este se creará automáticamente en nuestra hoja de Excel.

formulas de Excel

Existen diferentes tipos de funciones y se agrupan en Excel en categorías como Financieras, Lógicas, Texto, Matemáticas y trigonométricas, entre otras. Al hacer clic sobre una categoría, se desplegará un menú donde encontrarás las diferentes funciones que puedes usar.

2. Botón Insertar función:

Al hacer clic sobre este botón, se abrirá un cuadro de diálogo donde podrás insertar la función que quieras en una celda o en un grupo de celdas que debes haber seleccionado anteriormente. Si no sabes cuál es la función que necesitas, la puedes describir y el programa te sugerirá la más cercana a tus necesidades.

3. Autosuma:

Esta es la función que usamos para sumar el contenido de varias celdas al mismo tiempo y el total aparecerá en la celda siguiente a la última celda sumada. Allí también se desplegará un menú donde podrás elegir otras funciones, como encontrar el promedio, el máximo y el mínimo, entre otras.




4. Nombres definidos:

Aquí encontrarás opciones como Administrador de nombres, Asignar nombre y Crear desde la selección que te permitirán controlar, crear y buscar los nombres en la hoja o libro de cálculo en que estés trabajando.

5. Auditoria de fórmulas:

En esta parte de la Cinta de opciones encontrarás las herramientas necesarias para tener control sobre las fórmulas que uses.

6. Ventana de inspección:

Te permite estar al tanto de los valores de ciertas celdas mientras realizas cambios en la hoja de cálculo. Los valores se mantienen en una hoja separada que siempre tendrás a la vista.

7. Opciones para el cálculo:

Al hacer clic sobre este comando podrás elegir si quieres insertar las fórmulas de forma automática o manual.




Cómo crear una función en Excel

Como vimos en la página anterior, Excel 2016 tiene una pestaña donde podrás encontrar todas las funciones disponibles. Estas funciones están agrupadas en nueve categorías que son: 

Autosuma: te permite acceder a las funciones como Suma, Promedio, Máximo y Mínimo, entre otras cosas.

Usado recientemente: desde este comando podrás acceder a las funciones con las que hayas trabajado recientemente.

Financieras: esta categoría contiene funciones especializadas en operaciones contables y financieras.

 
Lógicas: aquí encontrarás funciones que te ayudarán a revisar los argumentos de las condiciones o valores de la tabla que estés creando. Por ejemplo, si una orden tiene un valor inferior a 50 dólares, añadirle 5 dólares de envío, pero si tiene un valor igual o mayor a 50 dólares, no añadirle nada.

 
Texto: contiene funciones que te ayudarán a trabajar con texto en una tabla para que el sistema lo puede tener en cuenta al construir fórmulas o usar otras funciones.

Fecha y hora: en esta categoría encontrarás funciones para trabajar con datos de fechas y horas en la tabla. 

 
Búsqueda y referencia: aquí encontrarás funciones como Hipervínculopara añadir un enlace a una celda o Dirección para crear una referencia a una celda en particular.

Matemáticas y trigonométricas: esta comando incluye funciones para crear argumentos numéricos, como por ejemplo, redondear un valor o calcular un subtotal.

Más funciones: en esta categoría están agrupadas otras funciones bajo las categorías de Estadísticas, Ingeniería, Cubo, Información, Compatibilidad y Web. 

Junto a estas categorías encontrarás el botón Insertar función que te ayudará a buscar la función que necesites.


Al hacer clic sobre alguna de estas categorías, se desplegará un menú en donde encontrarás las diferentes funciones que puedes utilizar. Sin embargo, el proceso no es el mismo al usar una de las opciones de Autosuma y una función de cualquier otra categoría.

Con Autosuma:

En el comando Autosuma encontrarás las opciones:


Suma: esta es una función que usamos para añadir las cantidades de diferentes celdas rápidamente.

Promedio: esta opción te permite calcular el promedio de las celdas seleccionadas.

Contar números: esta opción se encarga de enumerar la cantidad de objetos.

Max: esta función determina el valor más alto entre las celdas seleccionadas.

Min: esta función muestra la celda de valor más bajo entre las celdas seleccionadas.

Veamos cómo puedes insertar una de estas funciones.

Paso 1:

Selecciona la celda en donde quieres insertar la función.

Paso 2:

En la pestaña Fórmulas, haz clic en le comando Autosuma.

Paso 3:

Se desplegará un menú donde encontrarás las opciones Suma, Promedio, Contar número, Max y Min. Haz clic sobre la función que quieras utilizar.

Paso 4:

Inmediatamente aparecerá la función seleccionada en la celda señalada. Por ejemplo, si seleccionamos la función Suma, se sumarán todos los valores de la columna en que hayas seleccionado la celda. Si Excel falla al seleccionar el rango de celdas, puedes cambiarlo manualmente.

Con otras categorías:

Paso 1:

Después de seleccionar la celda en donde irá la función, en la pestaña Fórmulas, haz clic en la categoría de la tarea que debes realizar. 

Paso 2:

Allí se desplegará un menú donde podrás seleccionar la función que vas a usar.

Paso 3:

Se abrirá un cuadro de diálogo donde podrás introducir los argumentos de la función. Este espacio cambiará dependiendo del tipo de función que utilices. Una vez que termines, haz clic en Aceptar.

Paso 4:

El resultado de la función aparecerá en la celda que seleccionaste al inicio. Para copiar la función a otras celdas, haz clic en la esquina inferior derecha de la celda y arrastra el puntero hacía las otras celdas.

Texto en columnas

vamos a seleccionar la celda o la columna que contiene el texto que deseamos dividir. Después de esto vamos al menú “Datos” y en el grupo “Herramientas de datos” ubicamos la opción “Texto en columnas”:

Paso 2

Allí se desplegará el siguiente asistente para convertir texto en columnas, allí vamos a seleccionar la casilla “Delimitado” y pulsamos en Siguiente:

Paso 3

En la siguiente ventana vamos a seleccionar los delimitadores para los datos a dividir, independiente del seleccionado podemos ver una vista previa de los mismos en la ventana “Vista previa de datos”:

Paso 4

Allí damos clic en Siguiente y ahora definimos el formato de datos de la columna o también podemos dejar la opción por defecto de Excel 2016/2019:







Paso 5

Ahora, en el campo “Destino” vamos a seleccionar la celda donde se alojarán los datos, en este caso B2:



Paso 6

Pulsamos en “Finalizar” para completar la tarea y el resultado era el siguiente:



Así de sencillo será usar el asistente integrado de Excel para realizar la división de texto en columnas y simplificar nuestras tareas de gestión de datos.

Quitar duplicados

Para filtrar por valores únicos, haga clic en datos > Ordenar y filtrar > Avanzadas. 

Para quitar valores duplicados, haga clic en datos > herramientas de datos > Quitar duplicados. 

Para resaltar los valores únicos o duplicados, utilice el comando Formato condicional en el grupo 

estiloen la pestaña Inicio .

Validación de datos

Seleccione una o más celdas para validar.

En la pestaña Datos, del grupo Herramientas de datos, haga clic en Validación de datos.

En la pestaña Configuración, en el cuadro Permitir, seleccione Lista.

En el cuadro Origen, escriba los valores de la lista, separados por comas. Por ejemplo:

Para limitar una respuesta a dos opciones (por ejemplo "¿Tiene hijos?"), escriba Sí,No.

Para limitar la reputación de la calidad de un proveedor a tres clasificaciones, escriba Baja, Media, Alta.


Nota: Generalmente, estos pasos solo se recomiendan para los elementos de lista que probablemente nunca cambien. Si tiene una lista que pueda cambiar, o si necesita agregar o quitar elementos de vez en cuando, le irá mejor si sigue el paso del siguiente procedimiento recomendado.

Procedimiento recomendado: También puede crear entradas de la lista mediante referencia a un rango de celdas ubicadas en otra parte del libro. La manera más eficaz es crear la lista, después aplicar el formato como una tabla de Excel (desde la pestaña Inicio, seleccione Estilos > Dar formato como tabla > elija el estilo de tabla que sea mejor para usted). Después, seleccione el rango de cuerpo de datos de la tabla, que es la parte de la tabla que solo tiene la lista, no el encabezado de la tabla (en este caso, Departamento), y proporciónele un nombre significativo en el cuadro Nombre encima de la columna A.

En lugar de escribir los valores de la lista en el cuadro Origen de la validación de datos, agregue el nombre que acaba de definir precedido de un signo igual (=).

Lo mejor sobre usar una tabla es que a medida que agrega o quita elementos de la lista, su lista de validación de datos se actualizará automáticamente.


Nota: Es mejor colocar sus listas en una hoja de cálculo independiente (oculta si fuera necesario) de forma que nadie pueda editarlas.


Asegúrese de que esté activada la casilla Celda con lista desplegable.

Para especificar cómo quiere administrar los valores en blanco (nulos), active o desactive la casilla Omitir blancos.


Nota: Si los valores permitidos se basan en un rango de celdas que tiene un nombre definido y existe una celda en blanco en cualquier lugar de ese rango, al seleccionar la casilla Omitir blancos permite que cualquier valor se escriba en la celda validada. Esto también se cumple para cualquier celda a la que se haga referencia mediante fórmulas de validación: si cualquier celda a la que se hace referencia está en blanco, al seleccionar la casilla Omitir blancos permite que cualquier valor se escriba en la celda validada.


Pruebe la validación de datos para asegurarse de que funciona correctamente. Trate de escribir datos válidos y no válidos en las celdas para asegurarse de que la configuración funciona como pretende y que los mensajes están apareciendo como espera.

Agrupar columnas o filas

Paso 1:

Selecciona las filas o columnas que quieres agrupar.

Paso 2:

En la pestaña Datos de la Cinta de opciones, haz clic en el comando Agrupar.

Paso 3:

Las columnas o filas seleccionadas se agruparán.

Cómo desagrupar columnas o filas:

Paso 1:

Selecciona el grupo de filas o columnas que quieras desagrupar.

Paso 2:

En la pestaña Datos, haz clic en el comando Desagrupar. 

Al formar un grupo de celdas, en la parte superior de la hoja de cálculo aparecerá un signo Menos (-) con el cuál podrás esconder el grupo.

Para ver nuevamente las filas o columnas escondidas, haz clic en el signo Más (+) que estará en la parte superior de la hoja de calculo.

Subtotales 

El comando Subtotal te permitirá crear grupos automáticamente y usar funciones como Suma o Porcentaje para resumir datos en una hoja de cálculo. Por ejemplo, si tienes un gran inventario, pero solo estás interesado en una parte de él, esta función agrupará la información para que sea más fácil encontrarla.
Cómo crear un subtotal:

Selecciona las columnas o filas de las que crearás subtotales y, en la pestaña Datos, haz clic en el comando Subtotal.

Paso 2:

Se abrirá el cuadro de diálogo de Subtotal. Haz clic en el campo Para cada cambio en:para seleccionar la columna donde quieres que se aplique el subtotal.

Paso 3:

Despliega el menú de Usar función haciendo clic en la flecha que estará junto a esta función para elegir la función que deseas aplicar a los grupos que se creen en la columna seleccionada.

Paso 4:

En el espacio de Agregar subtotal a, selecciona la columna donde quieres que el subtotal aparezca.

Paso 5:

Haz clic en el botón Aceptar para guardar las modificaciones hechas. La hoja de cálculo será resumida en grupos y el subtotal estará debajo de cada grupo creado.

Al crear subtotales, la hoja de cálculo se divide en diferentes niveles. Al lado izquierdo encontrarás los botones de los niveles. Al hacer clic en ellos, podrás controlar cuanta información estará visible. Recuerda que Excel puede crear hasta ocho niveles.

 remover subtotales

Paso 1:

Selecciona las columnas o filas de las que removerás los subtotales y, en la pestaña Datos, haz clic en en el comando Subtotal.

Paso 2:

Se abrirá el cuadro de diálogo de Subtotal. Allí, haz clic en el botón Quitar todos.

Paso 3:

Todo el contenido de la hoja de cálculo se desagrupará y los subtotales serán removidos. 

Protejer Hoja y Protejer Libro

Proteger una hoja de cálculo. Para evitar que, accidental o deliberadamente, otros usuarios cambien, muevan o eliminen datos de una hoja de cálculo, puede bloquear las celdas de la hoja de cálculo de Excel y luego proteger la hoja con una contraseña.

Haga clic en Revisar > Proteger libro/Proteger Hoja.

SUMA:

Esta opcion te permite sumar las celdas que selecciones.

SUMAR SÍ:

Por ejemplo, la fórmula =SUMAR.SI(B2:B5,"Juan",C2:C5) suma solamente los

valores del rango C2:C5 en los que las celdas correspondientes del rango B2:B5


sea igual a "Juan". Para sumar las celdas en función de varios criterios, vea la


función SUMAR.SI.CONJUNTO.

AHORA:

Devuelve 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 regiona.

ALEATORIO Y ALEATORIO ENTRE:

Aleatorio:

Un número aleatorio es aquel obtenido al azar, es decir, que todo número tenga la misma


probabilidad de ser elegido y que la elección de uno no dependa de la elección del otro.


El ejemplo clásico más utilizado para generarlos es el lanzamiento repetitivo de una


moneda o dado ideal no trucado.

EJEMPLO:

=ALEATORIO()
Un número aleatorio mayor o igual a 0 pero menor que 1

AÑO:

La función AÑO devolverá un número entero que se encontrará en el rango 1900-9999 ya
que Excel utiliza un sistema de fechas basado en el año 1900 donde la fecha más antigua
es el día 1 de enero de 1900.

CONTAR:

La función CONTAR cuenta la cantidad de celdas que contienen números y cuenta los
números dentro de la lista de argumentos. ... Por ejemplo, puede escribir la siguiente fórmula


para contar los números en el rango A1:A20: =CONTAR(A1:A20).

CONTAR.SI:

Cuenta el número de manzanas, el valor de A2, y de naranjas, el valor de A3, entre las
celdas A2 y A5. El resultado es 3. Esta fórmula usa CONTAR.SI dos veces para especificar


varios criterios, un criterio por expresión. También puede utilizar la función CONTAR.SI.


CONJUNTO.




BUSCARV:

La función BUSCARV en Excel nos permite encontrar un valor dentro de un rango de datos,


es decir, podemos buscar un valor dentro de una tabla y saber si dicho valor existe o no. Esta


función es una de las más utilizadas para realizar búsquedas en Excel por lo que es importante


aprender a utilizarla adecuadamente.


MIN:

La función MIN es una de las funciones estadísticas de más utilizadas en Excel.MIN representa


las tres primeras letras de la palabra MINIMO y se utiliza para encontrar el número más pequeño,


menor o mínimo en una lista de valores o argumentos.

MAX:

La función MAX en Excel es de gran ayuda siempre que necesitemos obtener el valor
máximo de un conjunto de valores que puede ser una lista de números ubicados en uno
o varios rangos de nuestra hoja de Excel.
PROMEDIO:


Descripción. Devuelve el promedio (media aritmética) de los argumentos. Por ejemplo, si el intervalo


A1:A20 contiene números, la fórmula =PROMEDIO(A1:A20) devuelve el promedio de dichos


números.

PROMEDIO.SI:



La función PROMEDIO.SI en Excel nos permite obtener el promedio o media aritmética de un grupo


de celdas que cumplan con un criterio. Dicho criterio es el que determina si una celda es tomada en


cuenta o no dentro del cálculo.


SI:




La función SI en Excel es parte del grupo de funciones Lógicas y nos permite evaluar una condición


para determinar si es falsa o verdadera. La función SI es de gran ayuda para tomar decisiones en base


al resultado obtenido en la pruebalógica.

CONTAR.BLANCO:


Función CONTAR.BLANCO. Use la función CONTAR.BLANCO, una de las funciones estadísticas,


para contar el número de celdas vacías en un rango de celdas.


INDICE:




En su forma matricial, la función INDICE en Excel nos ayuda a obtener el valor de la celda que se encuentra justamente en el cruce de la fila y de la columna que hayamos especificado. La sintaxis de la función INDICE es la siguiente: Matriz (obligatorio): El rango de celdas que conforma nuestra tabla de datos.