Excel Avanzado

En este blog les explicaremos como utilizar las opciones mas avanzadas de M. Excel

Ordenar y filtrar: 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.

Filtro: nos permite mostrar sólo los datos que cumplen con los criterios que hemos especificado.

Aquí esta la opción de ordenar 

También tenemos esta otra que es del filtro 

Esto seria el menú de filtro

El formato condicional: es una útil herramientas que se encarga de aplicar automáticamente un formato determinado a aquellas celdas que cumplan una serie de criterios concretos. Por ejemplo, puedes indicarle a Excel que evalué el valor que se introduce en un rango determinado.

Una de las utilidades del formato condicional de Excel es que ayuda a identificar rápidamente, de un sólo vistazo, determinados valores.

Tablas Dinámicas y Tablas Dinámicas recomendas

Una tabla dinámica es un informe resumido en el que puedes cambiar la información a tu conveniencia y rápidamente es por ello el nombre de dinámicas.Al crearlas tú eliges donde quieres los datos, bien en filas, en columnas, en filtro, en valores. Excel facilita el trabajo de realizar Tablas dinámicas. A partir de la versión Excel 2013.

Pasos para crear en Excel la tabla dinámica recomendada.

• Abre una hoja de Excel en blanco.

Introduce una serie de datos en forma de tabla.

• 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.
• Si deseas realizar modificaciones en la tabla dinámica solamente tienes que llevar y arrastrar los campos en función de cómo quieras la información.

¿Que es un gráfico en Excel?

Un gráfico Excel es una representación gráfica de ciertos valores que nos permite hacer una comprobación comparativa de manera visual. Los gráficos en Excel son una de las herramientas más potentes que nos ofrece Microsoft para hacer informes, análisis de datos, etc.

¿Objetivos de un buen gráfico?

El objetivo a conseguir con los gráficos de Excel será conseguir que la información mostrada se entienda mejor que los números en sí mismos. Para poder cumplir con este objetivo una gráfica debe tener las siguientes características:

• Explicar visualmente los valores de mejor manera que los valores en sí mismos
• Ser autoexplicativo, es decir, un gráfico Excel debe ser simple y no requerir de una explicación por parte de su autor
• Debe indicar las unidades en las que están expresados los valores. No es lo mismo si la gráfica está en dólares que en miles de dólares que en barriles de petróleo.
• Si hay varias series tiene que tener una leyenda para poder entender muy claramente el contenido del gráfico.
• Una gráfica debe ser limpia. No debe estar llena de colores en los ejes, las series, etc… además, si puedes quitarle todos los elementos de distracción pues mejor que mejor.

Tipos de gráficos en 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.

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.

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.

GRÁFICOS EN 3D

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.

La gran ventaja de crear estos gráficos en Excel es que se pueden editar muy fácilmente a través de las opciones del menú y también se puede cambiar el tipo de gráfico de manera muy sencilla.

Biblioteca de funciones:

La biblioteca de funciones está ubicada en la pestaña Fórmulas. Aquí puedes buscar y seleccionar las funciones de Excel 2010 basado en categorías como Financieras, Lógicas, Texto, Fecha y hora, y mucho más. A continuación, verás la descripción de cada una de las categorías para que que puedas ubicarte fácil y rápidamente.

1. Insertar función:

Te permite buscar fácilmente una función ingresando la descripción de lo que estás buscando.

2. Autosuma:

Con este comando puedes obtener automáticamente el resultado de funciones comunes.

3. Recientes:

Usa esta categoría para acceder a las funciones con las que has trabajado recientemente.

4. Financieras:

Esta categoría contiene funciones para cálculos financieros como determinar un pago (PAGO) o la tasa de interés de un préstamo (RATE)

5. Lógicas:

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.

6. Texto:

Esta categoría contiene funciones que trabajan con argumentos de texto. Por ejemplo, convertir palabras a mayúsculas o reemplazar textos.

6. Fecha y Hora:

Contiene funciones para trabajar con datos y tiempo. Obtienes resultados como la fecha y hora actual.

7. Búsqueda y referencia:

Contiene funciones para obtener resultados por búsqueda y referencia. Por ejemplo, puedes agregar un hipervínculo a una celda o ver el valor de la intersección de una fila y columna.

8. Matemáticas y trigonométricas:

Incluye funciones para argumentos numéricos. Por ejemplo, puedes redondear valores (REDONDEAR), encontrar el valor de Pi (PI), multiplicar (PRODUCTO), subtotal (SUBTOTALES) y mucho más.

9. Más funciones:

Contiene funciones adicionales dentro de las categorías de Estadística, Ingeniería, Cubo, Información y Compatibilidad.

Utiliza la Biblioteca de funciones

En este ejemplo, vamos a suponer que eres un vendedor y necesitas una función para contar los días que hay entre la fecha de pedido y recibido de los productos que requieren tus clientes. Para ello, creaste la siguiente hoja de cálculo:

¿Cómo usar la Biblioteca?

Paso 1:

Selecciona la celda donde aparecerá la respuesta. E3, por ejemplo.  

Paso 2:

Haz clic en la pestaña Fórmulas. En el grupo Biblioteca de funciones, ubica y selecciona la categoría de la función que necesitas. En este ejemplo, seleccionaremos el comando Fecha y hora.  

Paso 4: 

Selecciona alguna función del menú desplegable. Aquí, vamos a elegir DIAS.LAB. Esta contará los días entre la fecha en que se hace un pedido hasta el día en que se recibe.

Paso 5: 

Aparecerá el cuadro de diálogo Argumentos de función. Ubica el cursor en el primer campo (Fecha_inicial) y escribe o selecciona la o las casillas que desees (C3, por ejemplo). Luego, ubica el cursor en el siguiente campo (Fecha_final) y repite el mismo procedimiento.

Paso 6:

Haz clic en el botón Aceptar y el resultado aparecerá en la celda o celdas que elegiste.

El comando Insertar Función es conveniente porque te permite buscar una función escribiendo su descripción o seleccionando una categoría para examinarla detenidamente.

En el siguiente ejemplo, buscaremos una función que cuente el número total de productos en una lista. La función básica COUNT sólo cuenta las celdas con números, pero necesitamos una que cuente aquellas que tienen texto. Veamos cómo hacerlo.

¿Cómo utilizar el comando Insertar Función? 

Paso 1: 

Selecciona la celda donde aparecerá la respuesta. C3, en este caso.  

Paso 2: 

Haz clic en la pestaña Fórmulas y selecciona el comando Insertar función.

Paso 3: 

Verás el cuadro de diálogo Insertar Función. Allí, escribe una descripción de la función que estás buscando y haz clic en el botón Ir. Para nuestro ejemplo, vamos a escribir: Contar celdas con texto. También puedes buscar seleccionando una categoría.  

Paso 4: 

Revisa los resultados haciendo clic en las opciones y leyendo la descripción que aparece en la parte inferior. Cuando encuentres la que deseas, pulsa el botón Aceptar.

Paso 5: 

Aparecerá el cuadro de diálogo Argumentos de función. Allí, inserta el cursor en el primer campo y escribe o selecciona la o las casillas que desees (A6: A23). Si necesitas añadir argumentos adicionales, haz clic en el siguiente campo y repite el mismo procedimiento.  

Paso 6: 

Para terminar, haz clic en el botón Aceptar y el resultado aparecerá.

Clasificación alfabética de los datos

Con más de 17 mil millones de celdas en una sola hoja de cálculo, Excel 2010 te ofrece la posibilidad de trabajar con una enorme cantidad de datos. Organizarlos en orden alfabético, del más pequeño al más grande o por otros criterios, puede ayudarte a encontrar la información que buscas más rápidamente.

¿Cómo organizar los datos en orden alfabético?

Por ejemplo, podrías organizar la lista de cumpleaños de los funcionarios de tu oficina por empleado, fecha de nacimiento o departamento. Veamos todo lo que puedes hacer.

Paso 1:  

Selecciona una celda de la columna que deseas ordenar. En este ejemplo, vamos a ordenar por el nombre. 

Paso 2: 

Haz clic en la pestaña Datos y ubica el grupo Ordenar y filtrar.

Paso 3: 

Ahora, pulsa el comando ascendente Ordenar de A a Z o descendente Ordenar de Z a A. Verás que los datos en la hoja de cálculo se organizan alfabéticamente.

Texto de columnas: 

1. Seleccione la celda o columna que contiene el texto que quiere dividir.
2. Seleccione Datos > Texto en columnas.
3. En el Asistente para convertir texto en columnas, seleccione Delimitado > Siguiente.
4. Seleccione los Delimitadores para sus datos. Por ejemplo, Coma y Espacio. Puede ver una vista previa de los datos en la ventana Vista previa de datos.
5. Seleccione Siguiente.
6. Seleccione el Formato de datos de la columna o use la sugerencia de Excel.
7. Seleccione el Destino, que es donde quiere que aparezcan los datos divididos en la hoja de cálculo.
8. Seleccione Finalizar.

Quitar duplicados: Que es un duplicado un duplicado es A veces, los datos duplicados son útiles y a veces solo dificultan la comprensión de los datos. Use el formato condicional para buscar y resaltar datos duplicados. De esa manera puede revisar duplicados y decidir si desea eliminarlos.

1. Seleccione las celdas en las que desea comprobar duplicados.
2. Haga clic en Inicio > Formato condicional >Resaltar reglas de celdas > Valores duplicados.
   
3. En el cuadro que se encuentra junto a valores con, elija el formato que desea aplicar a los valores duplicados y haga clic en Aceptar.
   

Quitar valores duplicados

Cuando use la característica Quitar duplicados, los datos duplicados se eliminarán de manera permanente. Antes de eliminar los duplicados, es una buena idea copiar los datos originales a otra hoja de cálculo para que no pierda ninguna información de forma accidental.

1. Seleccione el rango de celdas con valores duplicados que desea quitar.
2. Haga clic en Datos > Quitar duplicados y, a continuación, debajo de Columnas, active o desactive las columnas donde desea eliminar los duplicados.
   
   Por ejemplo, en esta hoja de cálculo, la columna Enero tiene información de precios que deseo conservar.
   
   Por tanto, he desactivado Enero en el cuadro Quitar duplicados.
   
3. Haga clic en Aceptar.

Validación de datos: Puede usar la validación de datos para restringir el tipo de datos o los valores que los usuarios escriben en una celda. Uno de los usos más comunes de la validación de datos es crear una lista desplegable.

1. Seleccione una o más celdas para validar.
2. En la pestaña Datos, del grupo Herramientas de datos, haga clic en Validación de datos.
   
3. En la pestaña Configuración, en el cuadro Permitir, seleccione Lista.
   

Procedimiento recomendado: También puede crear entradas de la lista mediante referencia a un rango de celdas ubicadas en otra parte del libro.

1. 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.
2. Asegúrese de que esté activada la casilla Celda con lista desplegable.
   

CÓMO AGRUPAR FILAS EN EXCEL:

Cuando los datos de nuestra hoja son extensos y queremos crear un reporte que nos permita mostrar un resumen con los subtotales para cada una de las categorías de los datos.

Para agrupar filas en Excel tenemos dos métodos: el automático y el manual. En esta ocasión revisaremos ambos casos y veremos los beneficios de cada uno de ellos. 

Nuestro primer objetivo será mostrar el total de los artículos vendidos en cada mes así como el monto total de las ventas.

AGRUPAR FILAS EN EXCEL AUTOMÁTICAMENTE

Para agrupar filas en Excel automáticamente utilizaremos el comando Subtotal que se encuentra en la ficha Datos y dentro del grupo Esquema.

Antes de ejecutar dicho comando debemos asegurarnos de seleccionar cualquier celda de nuestra hoja que contenga datos y entonces pulsar el botón Subtotal lo cual mostrará el siguiente cuadro de diálogo.

La primera de las listas, la cual tiene la etiqueta Para cada cambio en, mostrará cada una de las columnas de nuestros datos. 

Observa que a la izquierda de los encabezados de fila se han agregado unas líneas verticales junto con unos recuadros que contienen el símbolo menos [-]. 

Observa que el recuadro de la izquierda, donde hice clic previamente, ya no muestra el símbolo menos [-] sino que tiene el símbolo más [+] y si hacemos clic sobre dicho recuadro se volverán a expandir las filas del grupo. 

Además de los controles anteriores, se habrán agregado unos botones de nivel justo por arriba de  las líneas verticales, prácticamente a la izquierda del encabezado de la columna A, y que para este  ejemplo tienen los números 1, 2 y 3.

Cada número indica un nivel de detalle de los datos. Entre más grande sea el número significa que se mostrará mayor detalle de los datos.

Si haces clic en el botón del nivel 1, entonces se mostrará solamente la fila con el Total general. Ahora que ya sabemos cómo agrupar filas en Excel, te mostraré como desagrupar los datos en caso de que lo necesites.

CÓMO DESAGRUPAR FILAS EN EXCEL

Ya que hemos utilizado el comando Subtotales para agrupar las filas de nuestros datos, te sugiero volver a utilizar dicho comando para desagrupar las filas. Una vez que abras el cuadro de diálogo Subtotales, deberás hacer clic en el botón Quitar todos.

Esto dejará los datos tal como los teníamos al principio, es decir, quitará la agrupación de filas y además removerá las filas de subtotales y el total general. 

Si posteriormente quieres remover fácilmente las filas de subtotales y el total general, deberás ejecutar el comando Subtotalesy pulsar el botón Quitar todos.

OTRO EJEMPLO DE AGRUPACIÓN DE FILAS

Si en lugar de agrupar los datos por la columna Mes, queremos conocer el total de Artículo y Ventas para cada Vendedor, entonces debemos comenzar por ordenar los datos por dicha columna.

Posteriormente, al ejecutar el comando Subtotales, debes asegurarte de seleccionar la columna Vendedor dentro de la primera lista.

Al pulsar el botón Aceptar se hará la agrupación de filas en base al nombre del vendedor y se agregarán los subtotales correspondientes.

En este caso, la presentación del reporte ha cambiado un poco ya que presenta un enfoque diferente de los datos donde podemos ver fácilmente los objetivos alcanzados por cada uno de los vendedores durante el primer trimestre del año.

GRUPOS CON OPERACIONES DIFERENTES

Otra variante común al momento de agrupar filas en Excel es cuando queremos aplicar dos operaciones diferentes sobre las columnas.

1. Agregar el subtotal a la columna Vendedor.
2. Desmarcar la opción Reemplazar subtotales actuales.

Observa que en la parte posterior de la ventana Subtotales se ven los datos que ya fueron agrupados previamente con la función Suma para las columnas Artículos y Ventas. 

Si queremos que los subtotales de ambas operaciones estén en una misma línea, la única opción que tenemos es realizar la agrupación de filas manualmente.

CÓMO AGRUPAR FILAS MANUALMENTE

La única ocasión que considero adecuada para el método Manual es cuando queremos tener una sola línea de subtotales donde se apliquen diferentes tipos de cálculo sobre las columnas. 

Por el contrario, la celda B8 utiliza la función CONTARA para hacer la cuenta de los vendedores del mes. 

Excel reconocerá automáticamente el esquema previamente definido y realizará la agrupación correspondiente de los datos.

Proteger hoja o proteger libro: Excel ofrece numerosas opciones para proteger el contenido de las hojas de cálculo. Podemos bloquear celdas específicas, o bien una parte de la hoja de cálculo, o toda ella. Es importante entender que nos referimos a hojas de cálculo y no a los ficheros en sí, ya que ese es otro procedimiento. En este caso las protegemos para que no se modifiquen, pero cualquiera podrá leerlas o copiarlas.

El proceso para permitir modificar solo ciertas celdas de un documento tiene un funcionamiento peculiar. Primero tienes que elegir las celdas modificables, desbloquearlas, y luego proteger todo el documento.

Por tanto, pulsa la tecla CTRL y, sin soltarla, pincha en las celdas que van a ser modificables, para seleccionarlas. Se volverán de color azul:

Recuerda, éstas son las celdas modificables. El resto quedarán bloqueadas.

Sobre una de estas celdas seleccionadas pulsa con el botón derecho del ratón y elige la orden Formato de celdas. Toca en la pestaña Proteger, y quita la marca de la opción Bloqueada:

Así habrás desbloqueado las celdas modificables.

Ahora vamos a la pestaña Revisar de la cinta de opciones de Excel (en algunas versiones de Excel se llama Herramientas) y elegimos Proteger y Proteger Hoja:

Se abrirá una ventana. Aquí podemos seleccionar una gran cantidad de opciones que se pueden proteger (o no). La acción principal es desmarcar la casilla Seleccionar celdas bloqueadas. De esta forma no podrán modificar las celdas bloqueadas, que son todas menos las que hemos desbloqueado al principio:

Si lo deseas, puedes añadir una contraseña para proteger la Hoja de cálculo. Si no lo haces cualquiera podrá entrar en este menú y proteger o desproteger lo que quiera.

Revisa el resto de opciones porque encontrarás otras muy útiles. Por ejemplo puedes impedir que se eliminen filas y columnas, o que se inserten más filas.

Pulsa en Aceptar y comprueba que se ha activado todo correctamente. Ya has aprendido a proteger una hoja de cálculo en Excel.

Suma:

Cuando haga clic en Autosuma, Excel especificará automáticamente una fórmula (que usa la función SUMA) para sumar los números. He aquí un ejemplo. ... Aparecerá una fórmula en la celda B7 yExcel resaltará las celdas que está sumando.

SUMARSÍ: La función SUMAR.SI sirve para sumar los valores de un intervalo que cumplen los criterios especificados. Por ejemplo, si tenemos una columna que contiene números y queremos sumar solamente los valores que sean superiores a 5, usaremos esta fórmula: =SUMAR.SI(B2:B25,">5")

Excel: La función AHORA en Excel devuelve la fecha y hora actuales del sistema. Esta
función no tiene argumentos ya que consulta la información del computador en el momento exacto en que pulsamos la tecla Entrar.
UTILIZAR LA FUNCIÓN AHORA
Para poder utilizar la función AHORA en Excel es suficiente con introducir su nombre y los paréntesis de la función: =AHORA()

La información tendrá un formato dd/mm/aaaa hh:mm y lo podemos comprobar haciendo clic derecho sobre la celda y seleccionando la opción Formato de celdas:

El formato predeterminado de la función AHORA muestra solamente la hora y los minutos pero si queremos mostrar también los segundos devueltos por la función debemos utilizar un formato personalizado diferente.
MOSTRAR SEGUNDOS Y MILISEGUNDOS

Para hacer que el resultado de la función AHORA muestre los segundos podemos remplazar el formato personalizado de la celda: dd/mm/aaaa hh:mm:ss.

on este nuevo formato la celda mostrará el segundo exacto en que fue introducida la función AHORA:

Para mostrar los milisegundos debemos modificar el formato personalizado de la celda por el siguiente:

dd/mm/aaaa hh:mm:ss.000
RESULTADO DE LA FUNCIÓN AHORA ACTUALIZADO

Debes estar consiente de que el resultado de la función AHORA se actualizará cada vez que Excel vuelva a calcular las fórmulas de la hoja.

ALEATORIO.ENTRE

La función ALEATORIO.ENTRE en Excel devuelve un número aleatorio que se encontrará entre el límite inferior y el límite superior especificados. La función ALEATORIO.ENTRE siempre devolverá un número entero.
SINTAXIS DE LA FUNCIÓN ALEATORIO.ENTRE

La función ALEATORIO.ENTRE tiene solamente dos argumentos:

Inferior (obligatorio): Límite inferior del número aleatorio generado.

Superior (obligatorio): Límite superior del número aleatorio generado.

La función ALEATORIO.ENTRE regresará un nuevo número aleatorio cada vez que se recalcule la hoja de cálculo.
EJEMPLO DE LA FUNCIÓN ALEATORIO.ENTRE

Para generar un número aleatorio entre 1 y 100 escribe la siguiente fórmula en cualquier celda de la hoja.

=ALEATORIO.ENTRE(1, 100)

Para obtener un número aleatorio diferente solamente debes presionar la tecla F9 y tendremos un nuevo número aleatorio entre 1 y 100.
NÚMEROS ALEATORIOS NEGATIVOS

La función ALEATORIO.ENTRE también puede generar números aleatorios negativos. Por ejemplo, si deseo tener un número aleatorio que se encuentre entre -50 y 50 puedo escribir la siguiente fórmula:

=ALEATORIO.ENTRE(-50, 50)

Solo recuerda colocar siempre en el primer argumento el límite inferior, que para este ejemplo es el número negativo, ya que si inviertes los argumentos la función ALEATORIO.ENTRE devolverá el error #¡NUM!

AÑO

La función AÑO en Excel devuelve el número de año de una fecha que sea especificada como argumento de la función. De esta manera, el único argumento de la función es la fecha de la que deseamos conocer el año.
SINTAXIS DE LA FUNCIÓN AÑO


Núm_de_serie (obligatorio): La fecha de la que será devuelto el 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.
EJEMPLOS DE LA FUNCIÓN AÑO

Podemos especificar la fecha como una cadena de texto en el argumento de la función AÑO y obtendremos de regreso el número de año:



De igual manera podemos especificar una fecha que se encuentre en alguna otra celda y que tenga un dato de fecha.


OBTENER LA DIFERENCIA EN AÑOS

Si tienes dos fechas y deseas conocer la diferencia en años que existe entre ambas fechas podemos utilizar la función AÑO de la siguiente manera:

=AÑO(B1) – AÑO(A1)

Esta fórmula obtendrá el año de cada una de las fechas y posteriormente realizará la resta:



La función AÑO en Excel regresará siempre el año de la fecha que hayamos especificado.

Contar

La función CONTAR.SI en Excel nos da la oportunidad de contar el número de celdas de un rango que cumplan con un criterio establecido. Solamente cuando el valor de dichas celdas cumple la condición es considerada dentro de la cuenta.

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.

PREPARAR LOS DATOS PARA LA FUNCIÓN BUSCARV

Comenzaremos con un ejemplo sencillo donde utilizaremos la función BUSCARV para realizar una búsqueda dentro de un directorio telefónico. Pero antes de escribir la fórmula que nos ayudará a realizar la búsqueda, será importante poner atención los datos de origen

.

SINTAXIS DE LA FUNCIÓN BUSCARV
La gran mayoría de las funciones de Excel tienen argumentos que son la manera en cómo le indicamos los datos con los que trabajará así como ciertos criterios de ejecución. En el caso de la función BUSCARV tenemos cuatro argumentos que describo a continuación: