10 consejos y trucos avanzados de Excel

Las hojas de Excel son una gran opción para comparar y operar datos y sus variantes en filas y columnas. Sin embargo, esta herramienta del paquete de Office también cuenta con excelentes características para convertir unidades, analizar escenarios económicos o filtrar, seleccionar y editar varios elementos. Estas funciones, que no son conocidas por muchos usuarios básicos del programa, pueden facilitar la vida de quienes usan las hojas de trabajo en el trabajo o en el día a día. Comprueba diez pequeños tutoriales de trucos y consejos avanzados de Microsof Excel.

Siete funciones del Excel que poca gente conoce

Cómo crear gráficos en Microsoft Excel

¿Quieres comprar celular, TV y otros productos con descuento? Conozca Comparar

1. Selección avanzada

Con la práctica característica "Buscar", Microsoft Excel, al igual que los demás programas de Office, puede encontrar rápidamente elementos y términos en un documento. Sin embargo, con el objetivo de editar múltiples datos simultáneamente, la aplicación permite seleccionar todos.

Paso 1. Marque toda la tabla con los datos que desea buscar y seleccionar. Luego, en la pestaña "Página Inicial", elija la opción "Buscar y Seleccionar" y señale "Buscar ...";

Con los datos seleccionados, apriete en los comandos de la pestaña "Página Inicial" en destaque

Paso 2. En la ventana "Buscar y reemplazar", escriba el término que desea encontrar y pulse "Buscar todo";

Introduzca el elemento que desea buscar y pulse "Buscar todo"

Paso 3. Seleccione uno de los elementos visualizados y pulse el botón "Ctrl" + "A" para seleccionar todos los resultados simultáneamente. A continuación, pulse cerrar - las celdas marcadas continuarán seleccionadas;

Haga clic en un elemento de los resultados y "Ctrl" + "A" para seleccionar todos

Paso 4. A través de esta selección es posible borrar, resaltar o copiar todos los elementos seleccionados, y al mismo tiempo.

Con la selección avanzada es posible editar o borrar todos los elementos encontrados

2. Transposición avanzada

La función de transposición de Excel permite convertir columnas en líneas y viceversa. Sin embargo, con la transposición avanzada, el usuario utiliza una fórmula, vinculando los datos de la tabla transpuesta y haciendo que cualquier modificación de los elementos sea replicada.

Paso 1. Seleccione el número de celdas y columnas equivalente al número de columnas y celdas que desea transponer, por ejemplo: si una tabla es 2X5, seleccione celdas en formato 5X2;

Marque un número de celdas equivalente a la hoja de cálculo que se transpuesta

Paso 2. Aún con las celdas marcadas, escriba la función "TRANSPOR (X: Y)" - siendo "X" la primera celda de la esquina superior izquierda de la tabla, y "Y" la última celda de la esquina inferior derecha;

A continuación, escriba "TRANSPOR (X: Y)", con "X" y "Y" indicando el inicio y el final de la tabla que se transpuesta

Paso 3. La tabla será transpuesta por la función, fijando los valores originales. Sin embargo, cualquier cambio en los valores de la tabla original cambiará inmediatamente los elementos transpuestos.

La tabla transpuesta tendrá valores fijos y reflejará cualquier cambio en la tabla original

3. Segmentación de datos

Excel cuenta con un filtro especial para tablas identificadas en su hoja de cálculo. La segmentación de datos muestra macros especiales con los elementos de la tabla, presentando resultados en una excelente disposición visual.

Paso 1. Seleccione las celdas que se identificar como tabla y, en la pestaña "Insertar", elija la opción "Tabla";

Marque las celdas y haga clic en los comandos destacados para insertar una tabla

Paso 2. En la ventana que se abrirá, compruebe la selección de las celdas que forman la tabla, marque la opción "Mi tabla tiene encabezados" y confirme en "OK";

En la ventana "Crear tabla", marque "Mi tabla tiene encabezados" y haga clic en "Aceptar"

Paso 3. A continuación, en la pestaña "Diseño", haga clic en la opción "Insertar Segmentación de datos" para activar el sistema de filtrado dinámico de la tabla creada;

Creada la tabla, presione el botón "Insertar Segmentación de Datos", en la pestaña "Diseño"

Paso 4. Se abrirá una ventana con las opciones de encabezados de la tabla creada. Seleccione los que sea necesario y pulse "OK";

Seleccione los encabezados de las columnas que desea utilizar

Paso 5. Creada la segmentación de datos, cada opción marcada en las ventanas de los encabezados eliminará las alternativas incompatibles con las demás. De la misma forma, los elementos seleccionados se mostrarán en la tabla, mientras que los no marcados permanecerán ocultos.

Cada elemento marcado aparecerá en la tabla, mientras que los demás serán ocultados

4. Gestor de escenarios

La presentación de los datos en las hojas de Excel facilita la visualización de diferentes resultados. Sin embargo, con la función Administrador de escenarios es posible cambiar variables y hacer informes con una escala de los mejores a los peores resultados posibles.

Paso 1. En una hoja de cálculo, seleccione los valores variables y, en la pestaña "Datos", haga clic en el botón "Prueba de hipótesis" y, a continuación, en la alternativa "Administrador de escenarios ...";

Marque los valores variables en la ficha y haga clic en los comandos resaltados en la imagen

Paso 2. Se abrirá la ventana "Editar escenario". Escriba un nombre en el campo "Nombre de escenario" y haga clic en "Aceptar" para continuar configurando uno de los posibles escenarios;

Nombre del escenario y pulse "OK"

Paso 3. A continuación, cambie los valores de cada variable, de acuerdo con las celdas previamente elegidas, y concluya en "OK";

Continúe la edición del escenario eligiendo los valores de cada variable y haga clic en "Aceptar"

Paso 4. Repita la operación para agregar otros escenarios y, cuando esté satisfecho, seleccione uno de éstos, en la ventana "Administrador de escenarios, pulse" Mostrar "para ver el escenario. Las fórmulas de la hoja de cálculo incorporarán y funcionarán inmediatamente los valores del escenario;

Seleccione un escenario y haga clic en "Mostrar" para verlo, así como el resultado con las fórmulas

Paso 5. En la ventana "Gestor de escenarios", presione el botón "Resumir" para hacer una hoja de cálculo especial con cada escenario;

La alternativa "Resumir" crea una hoja de trabajo con los resultados de todos los escenarios

Paso 6. El "Resumen del escenario" se creará en una nueva hoja de cálculo y mostrará los escenarios creados según la configuración deseada.

Excel creará el "Resultado del escenario" en una nueva hoja de cálculo

5. Función "CONVERTER"

Excel cuenta con una enorme base de datos de conversión para valores y medidas de temperatura, peso, tamaño, etc. Para acceder y utilizar esta función basta con insertar la función "CONVERTER", convirtiendo automáticamente los valores.

Paso 1. Introduzca la fórmula "= CONVERTER (X) (sin comillas) para mostrar la tabla de valores de conversión de Excel - siendo que" X "equivale a la celda con el valor original que se elegirá. después de identificar la celda para abrir la tabla de valores (en la imagen, por ejemplo, se eligió "números en litros");

Escriba la función "= CONVERTER (X;" y elija la categoría de medida que se convertirá

Paso 2. A continuación, con la fórmula "= CONVERTER (X;" Y ";" - siendo "Y" la medida seleccionada - elija en una segunda tabla, compatible con la primera, la fórmula para la que se convertirá el valor;

Después de escribir el segundo ";" de "= CONVERTER (X;" Y ";", elija la medida en la que se convertirá el valor

Paso 3. La celda con la fórmula pasará a convertir automáticamente cualquier valor determinado en el primer campo, pudiendo ser replicado y tener medidas alteradas en otras celdas.

El campo con la fórmula cambiará automáticamente los valores de la celda previamente elegida

6. Células con contenido invisible

Con funciones para ocultar columnas enteras de hojas de cálculo, Microsoft Excel también ofrece un truco para ocultar valores de ciertas celdas.

Paso 1. Haga clic con el botón derecho del ratón sobre la celda que tendrá la vista ocultada y, a continuación, elija la opción "Formato de celdas ...";

Presione el botón derecho del ratón sobre la celda y elija "Formato de celda ..."

Paso 2. En la ventana "Formato de celda", en la pestaña "Número", elija la opción "Personalizado" y, en el campo de texto, escriba ";;;" (sin comillas);

Seleccione "Personalizado" y escriba ";;;" en el campo de texto

Paso 3. El contenido de la celda se ocultará de la vista normal de la hoja de cálculo, que se muestra como un campo en blanco. Sin embargo, será posible comprobar su valor al consultar la barra de fórmulas de Excel.

Los valores de la celda sólo se mostrar en la barra de fórmulas

7. Análisis de datos

Microsoft Excel tiene un paquete de recursos para hacer análisis de datos estadísticos más refinados y específicos. Sin embargo, esta herramienta está disponible en la configuración original del programa.

Paso 1. Para activar el paquete de análisis de datos, haga clic en la pestaña "Archivos" y, a continuación, en la alternativa "Opciones";

En la pestaña "Archivo", pulsa en "Opciones"

Paso 2. Se abrirá una ventana con varias opciones de configuración de Excel. En la parte "Suplementos", seleccione la opción "Suplementos de Excel", en la casilla de la parte inferior de la interfaz, y complete en "Ir ...";

En "Suplementos", elige "Suplementos de Excel" y haga clic en "Ir ..."

Paso 3. En la ventana "Suplementos", marque la alternativa "Herramientas de análisis" y pulse "OK";

A continuación, seleccione "Herramienta de análisis" y haga clic en "Aceptar"

Paso 4. Las opciones de análisis de datos pueden ser accedidas a través de un botón en la pestaña "Datos", bastando escoger una de las varias alternativas para iniciar la configuración de los recursos de estadística.

En la pestaña "Datos" estará el botón "Análisis de Datos" con los sofisticados recursos estadísticos de Excel

8. Quitar celdas en blanco

Un truco práctico y fácil de editar y estandarizar hojas de Excel es la eliminación de celdas en blanco. A pesar de que no existe un recurso para hacer exactamente esta operación, la alternativa a la adecuación es muy útil y versátil.

Paso 1. En una hoja de cálculo con celdas en blanco, seleccione sus columnas y escriba el comando "Ctrl" + "G" para abrir la función "Ir a" y haga clic en "Especial ...";

Seleccione las columnas de la hoja de cálculo, abra "Ir a" con "Ctrl" + "G" y haga clic en "Especial ..."

Paso 2. En la ventana "Ir a especial", seleccione la opción "En blanco" y pulse "OK" para seleccionar todas las celdas vacías en la selección de la hoja de cálculo;

Seleccione "En blanco" y haga clic en "Aceptar" para seleccionar todas las celdas vacías

Paso 3. A continuación, en la pestaña "Página Inicial", haga clic en el comando "Eliminar" y seleccione la opción "Eliminar celdas ...". En la casilla de selección que se abrirá, elija la opción "Resaltar celdas hacia arriba" para mantener la unidad de la hoja de cálculo con campos continuos.

En la pestaña "Página Inicial", haga clic en "Eliminar", "Eliminar celdas ..." y "Resaltar celdas hacia arriba"

9. Filtro Avanzado

Con un sofisticado sistema de filtro para hojas de cálculo, Excel también cuenta con la función de filtro avanzado que permite formar una nueva tabla con elementos extraídos de la hoja de cálculo original.

Paso 1. En la pestaña "Datos", seleccione "Avanzado" para activar la ventana de configuración del filtro avanzado. Es importante también copiar el encabezado de la hoja de cálculo para no mezclar celdas y ya determinar el término que se filtrar;

Copie el encabezado de la hoja de cálculo con el término de filtro, y en la pestaña "Datos, haga clic en" Avanzado "

Paso 2. En la ventana Filtro avanzado, determine cómo "Intervalo de lista" toda la hoja de cálculo que se utilizará;

Marque toda la hoja de cálculo como "Intervalo de lista"

Paso 3. A continuación, elija el encabezado y el término de filtrado para el campo "Intervalo de criterios";

Marque el encabezado y el término de filtro como "Intervalo de criterios"

Paso 4. Para completar el filtro avanzado, marque la opción "Copiar a una nueva ubicación" y, a continuación, elija el campo en "Copiar a:" y pulse "OK";

Marque "Copiar a una nueva ubicación" y elija la celda en "Copiar a"

Paso 5. El filtro avanzado formará una nueva tabla sólo con las líneas que contengan el término del filtrado

Una nueva hoja de cálculo se mostrará en el campo determinado por el filtro avanzado

Paso 6. De la misma forma, es posible combinar más de dos términos y utilizar valores e indicadores numéricos para mejorar el filtrado.

El filtro avanzado permite combinar términos y utilizar valores con indicadores numéricos

10. Función "DIATRABALHOTOTAL"

Excel tiene varios recursos para contabilizar días y horas de trabajo, pero con la fórmula "DIATRABALHOTOTAL" es posible configurar también festivos y descansos.

Paso 1. La fórmula "= DIATRABALHOTOTAL (A, B)" (sin comillas) hace la contabilización automática del total de días de trabajo - siendo "A" la celda con la fecha del primer día, y "B" el campo con la fecha fecha del último día;

Escriba "= DIATRABALHOTOTAL (A, B)", con "A" y "B" indicando, respectivamente, el primer y el último día de trabajo

Paso 2. Sin embargo, esta función cuenta con una tercera variante que, además de verificar si la fecha cae en un fin de semana, ya sustrae del importe total los días de trabajo. Para continuar la fórmula, escriba "= DIATRABALHOTOTAL (A, B, C: D)" - con C y D indicando la primera y la última celda de las fiestas o holguras;

Con "= DIATRABALHOTOTAL (A, B, C: D)", el intervalo "C: D" restar días de la suma total

Paso 3. La función mostrará el resultado automáticamente cada vez que se cambie alguna de sus variantes.

La fórmula presentará inmediatamente los resultados

¿Cuál es el mejor modo de convertir un archivo PDF en Excel? ¿Por qué?