Opciones avanzadas

Con las opciones avanzadas de Excel aprenderemos a filtrar y ordenar datos, dos procedimientos que facilitan el análisis de la información. Veremos cómo buscar y reemplazar elementos en las planillas para modificar una gran cantidad de celdas a la vez. Utilizaremos los esquemas para agrupar y resumir contenido y obtendremos subtotales. Para terminar, veremos cómo importar texto desde otras fuentes y cómo editarlo.

Filtrar y ordenar

Excel proporciona gran cantidad de herramientas para ordenar los datos de una planilla y tener un mejor manejo de la información. Cuando ingresamos datos en una hoja de cálculo, comúnmente lo hacemos en forma desordenada.

Entonces, muchas veces resulta difícil tener una visión clara de su contenido, en especial, cuando hay una gran cantidad de elementos. Lo más conveniente sería ordenarlos según algún criterio. Podemos ordenar una lista de datos basándonos en valores de texto o números, color de la celda, color de la fuente e iconos de la celda. A continuación, veremos las diferentes opciones.

Ordenar rápidamente

Es posible hacer una ordenación rápida de los datos de una manera muy simple. Para esto, seleccionamos un rango de datos, una columna o una celda de la columna que queremos ordenar y luego vamos al grupo Ordenar y Filtrar de la ficha Datos. Encontraremos dos botones: Ordenar de A a Z y Ordenar de Z a A. El primero ubicará los valores de menor a mayor y el segundo lo hará a la inversa.

Con este procedimiento, Excel ordena la lista por columna. En caso de haber seleccionado un rango, lo hará por la primera columna.

Ordenar por más columnas

También podemos ordenar los datos por más de un criterio. Supongamos que tenemos un listado de alumnos que incluye apellido, nombre y las notas de cada trimestre, y deseamos ordenarlo primero por apellido en forma ascendente y, luego, por nombre. Para esto, seleccionamos el rango que contiene los datos, vamos al grupo Ordenar y Filtrar de la ficha Datos y pulsamos Ordenar. Aparecerá el cuadro de diálogo Ordenar, en el cual debemos especificar las columnas por las que queremos ordenar los datos.

Si nuestra lista posee encabezado, marcamos la opción Mis datos tienen encabezados; de este modo no se incluirán los títulos de los campos de ordenación. En la lista Ordenar por, seleccionamos la primera columna. En nuestro caso, Apellido; y en Criterio de ordenación, elegimos el tipo de ordenación: ascendente o descendente.

Para agregar una columna adicional, presionamos el botón Agregar nivel y, otra vez, definimos los valores para cada una de las listas. Siguiendo nuestro ejemplo, en la lista Ordenar por, seleccionamos la columna Nombre y en la lista Criterio de ordenación elegimos el tipo ascendente.

Al presionar el botón Aceptar, se aplicará el orden que indicamos para cada una de las columnas. Los datos se ordenarán, primero, por el primer nivel de la lista, y sucesivamente por los demás niveles en orden descendente. En Excel 2013 es posible ordenar hasta 64 columnas.

Ordenar por formatos

También podemos ordenar valores de acuerdo con algunos criterios de formato de celda: color de celda, color de fuente e iconos de celda.

El procedimiento es similar al anterior: seleccionamos la lista y presionamos el botón Ordenar del grupo Ordenar y Filtrar. En el cuadro de diálogo Ordenar, dentro de la lista Ordenar por, pulsamos la columna que queremos ordenar. A continuación, en la lista Ordenar según, podemos elegir Color de celda, Color de fuente o Icono de celda.

Finalmente, en la lista Criterio de ordenación elegimos el tipo que deseamos. Para agregar una columna adicional presionamos el botón Agregar nivel y otra vez definimos los valores para cada una de las listas.

Ordenar de izquierda a derecha

Hasta ahora hemos ordenado los datos de una lista de arriba hacia abajo, por columnas, pero Excel también permite ordenar el contenido por filas, es decir, de izquierda a derecha. Para hacerlo, primero seleccionamos el rango de datos que queremos ordenar y, luego, hacemos clic en el botón Opciones…, ubicado dentro de la ventana Ordenar, que aparece al pulsar el botón Ordenar del grupo Ordenar y filtrar en la ficha Datos.

A continuación, se abrirá el cuadro de diálogo Opciones de ordenación, donde tenemos que seleccionar la opción Ordenar de izquierda a derecha. Una vez que aceptamos, seguimos los mismos pasos que usamos para ordenar por más columnas, con la única diferencia de que ahora los niveles de ordenación aparecen por filas en vez de por columnas.

Listas personalizadas

Otra opción es ordenar datos sobre la base de listas predeterminadas, tales como días de la semana o meses del año. Para utilizarlas, seleccionamos los datos, pulsamos el botón Ordenar y en la lista Criterio de ordenación elegimos la opción Listas personalizadas. Se abre el cuadro Listas personalizadas donde elegimos la que necesitamos.

Autofiltros

Cuando tenemos planillas con gran cantidad de registros, seguramente necesitaremos filtrar la información para encontrar datos de manera rápida. El filtrado de un conjunto de valores consiste en ocultar las filas que no cumplen con los criterios especificados. Con esta herramienta, no solo controlamos lo que deseamos ver, sino que, además, podemos modificar el conjunto de datos obtenido, aplicarle formato, representarlo en gráficos e imprimirlo.

Para utilizar los autofiltros, primero debemos tener una tabla de datos, con encabezados en la primera fila. Luego, seleccionamos una celda de la tabla, vamos a la ficha Datos y, dentro del grupo Ordenar y filtrar, presionamos el botón Filtro.

Aparecerán flechas desplegables a la derecha de cada encabezado de las columnas de la tabla. Haciendo un clic en estas flechas, se abrirá una lista donde encontraremos una serie de opciones para establecer cómo filtraremos los datos.

Tipos de filtros en opciones avanzadas

Podemos establecer filtros de texto, números, fechas o por color, en caso de tener celdas que posean un color de fuente o de fondo. Para activar el filtro, seleccionamos un elemento de la lista o escribimos en el cuadro de búsqueda el texto o los números que queremos mostrar.

Luego, pulsamos Aceptar. Si el tipo de dato es numérico, al desplegar la lista de filtro Excel mostrará la opción Filtros de número. Si hacemos clic en esta opción, aparecerá un submenú que nos permitirá filtrar según diferentes condiciones, como: Es igual a…, Diez Mejores…, entre otras.

En cambio, si el tipo de dato es texto, al desplegar la lista de filtro Excel mostrará la opción Filtro de texto, donde visualizaremos las opciones propias de este tipo de dato. Por ejemplo, podemos buscar palabras que comiencen con una determinada letra.

Si hemos aplicado color de fuente o celdas en una tabla, entonces podemos también filtrar los datos sobre la base de este criterio. Esta herramienta permite especificar criterios de filtro en más de una columna. Simplemente, filtramos la tabla según una columna y, a continuación, filtramos la lista resultante en base a otra columna, y así sucesivamente, hasta que obtenemos el resultado buscado.

Borrar filtros

Para eliminar un autofiltro de una columna en particular, abrimos la lista desplegable de la columna y seleccionamos Borra filtro de o presionamos Borrar dentro del grupo Ordenar y filtrar de la ficha Datos. Para eliminar todos los autofiltros, pulsamos el botón Filtro de la misma ficha o usamos la combinación de teclas CTRL + MAYÚS + L.

Buscar y reemplazar

Cuando trabajamos con planillas que tienen una gran cantidad de registros, seguramente, de manera frecuente, necesitaremos localizar algún dato en particular o reemplazar un dato por otro.

Para simplificar estas tareas, que de forma manual podrían demandarnos mucho tiempo, Excel nos brinda herramientas destinadas a buscar y reemplazar datos numéricos y de texto en una hoja de cálculo.

Al realizar una búsqueda, la aplicación rastreará el dato en una cadena de caracteres, es decir, dentro de una secuencia de texto, números, operadores matemáticos, símbolos de puntuación y la combinación de todos ellos.

Si queremos buscar en toda la hoja, seleccionamos una sola celda. Excel comenzará el proceso desde esta celda y recorrerá la hoja completa. Para buscar solo en una parte de la hoja de cálculo, seleccionamos el rango correspondiente.

Las herramientas para buscar y reemplazar datos se encuentran en el botón Buscar y seleccionar, ubicado en el grupo Modificar de la ficha Inicio. También podemos acceder a ellas por medio del teclado. Para activar la opción de buscar, presionamos la combinación de teclas CTRL + B y para activar la opción reemplazar, pulsamos CTRL + L.

Opciones avanzadas: Buscar un dato

Cuando seleccionamos Buscar…, se abre una ventana donde tenemos que indicar las opciones de búsqueda. En el cuadro Buscar ingresamos la cadena de caracteres que deseamos localizar. Si presionamos el botón Opciones, podemos definir más detalles:

Los datos a buscar son los siguientes:

• Dentro de: permite elegir dónde buscar, en una hoja o en el libro
• Buscar: podemos indicar si queremos buscar por filas o por columnas. Cuando seleccionamos la opción por filas, Excel recorre la hoja de cálculo en forma horizontal, fila por fila, comenzando por la celda seleccionada. La opción por columnas busca a través de la hoja, columna por columna, empezando con la celda seleccionada
• Buscar dentro de: aquí elegimos entre buscar el dato en las fórmulas, valores o comentarios. Cuando indicamos fórmulas, Excel busca dentro de los argumentos; por lo tanto, es posible buscar y reemplazar una referencia incluida en una fórmula. También es muy útil para agregar o quitar signos, en referencias absolutas o relativas, de varias celdas a la vez. Cuando seleccionamos valores, Excel busca en los resultados visualizados de las fórmulas en la hoja de cálculo; es decir, los valores constantes
• Formato…: para realizar la búsqueda del dato con un formato concreto, por ejemplo, solo celdas con formato contabilidad con dos decimales
• Coincidir mayúsculas y minúsculas: marcando esta opción, Excel localizará solo aquellas ocurrencias que coincidan exactamente en los caracteres mayúscula o minúscula con la cadena de búsqueda. Si dejamos esta opción sin marcar, Excel no diferenciará entre ambos caracteres
• Coincidir con el contenido de toda la celda: buscará solo los caracteres que escribimos en el cuadro Buscar. Al realizar la búsqueda, Excel encuentra cualquier ocurrencia de una cadena de caracteres, incluso si forma parte de otra. Por ejemplo, una hoja de cálculo contiene los nombres Ana, Juan, Mariana, y deseamos buscar Ana. Cuando realizamos la búsqueda sin marcar esta opción, Excel localiza el dato Ana y también Mariana, que contiene la cadena de los datos por buscar. Si marcamos la opción Coincidir con el contenido de toda la celda, Excel solo encontrará el dato Ana.

Opciones avanzadas: Reemplazar datos

Cuando elegimos la opción Reemplazar…, se abre un cuadro de diálogo similar al de Buscar…, con la diferencia de que se agregará el cuadro Reemplazar con.

En el cuadro Buscar ingresamos el texto o los números que necesitamos rastrear, mientras que en Reemplazar con escribimos el texto o los números con los que los queremos reemplazar.

Además, podemos especificar los criterios explicados en la opción de búsqueda. Una vez que establecimos todos los parámetros, podemos presionar el botón Reemplazar todos para sustituir al mismo tiempo todas las ocurrencias encontradas. O podemos pulsar el botón Reemplazar para hacer un solo reemplazo. Con el botón Formato es posible reemplazar un dato, pero con color de fuente y color de celda para resaltar la información.

Uso de comodines

Podemos usar los caracteres comodín ? (signo de interrogación) y *(asterisco) para ampliar las posibilidades de búsqueda. Los comodines son útiles cuando estamos buscando en un grupo de datos similares, pero no idénticos, o cuando estamos buscando un dato que no recordamos por completo.

El signo “?” reemplaza un único carácter en la cadena de caracteres, mientras que * sustituye una secuencia de caracteres.

Subtotal

Después de haber analizado diferentes herramientas para ordenar, filtrar, buscar y reemplazar datos de distinto tipo en una planilla, conoceremos cómo funciona Subtotal y para qué podemos utilizarla. Esta herramienta se utiliza principalmente cuando tenemos datos filtrados o agrupados en un esquema.

Cuando trabajamos con listas de datos que tienen cierta estructura –por ejemplo, una planilla de ventas organizada en columnas por región, sucursal, fecha e importe– es probable que necesitemos calcular cuánto lleva vendido una región determinada, cuánto facturó cada sucursal o cuál es el total vendido, entre otra información que queremos obtener. Para este fin utilizamos la herramienta Subtotal. Veamos en detalle su funcionamiento.

Insertar subtotales

Partiendo de un rango de datos con encabezados y sin filas ni columnas vacías, vamos a la ficha Datos y, en el grupo Esquemas, presionamos Subtotal. Se abrirá el cuadro de diálogo Subtotales, donde completaremos las siguientes opciones:

• Para cada cambio en: hacemos clic en la flecha y, en el menú, seleccionamos el encabezado de la columna que deseamos agrupar
• Usar función: en el menú desplegable, elegimos entre las distintas funciones de cálculo disponibles, como Suma, Cuenta, Promedio, Máx., Mín., Producto y Contar números, entre otras
• Agregar subtotal a: activamos las casillas de las columnas que contienen los valores que queremos calcular
• Salto de página entre grupos: activamos esta casilla si queremos que, luego de cada subtotal, se inserte un salto de página automático (es decir, un cambio de página)
• Resumen debajo de los datos: si queremos que el subtotal se muestre por encima de los valores calculados, desactivamos esta casilla.

Subtotales anidados

La casilla Reemplazar subtotales actuales se activa si hemos realizado modificaciones en las opciones y queremos aplicarlas reemplazando las anteriores. Si, por el contrario, deseamos ingresar subtotales anidados o nuevas funciones de cálculo para los datos, dado un rango que ya contiene subtotales, repetimos las acciones de generación de subtotales y, desactivando esta casilla, los nuevos subtotales se agregarán para complementar el resumen.

Una vez validadas las opciones, presionamos Aceptar. Veremos los subtotales agregados al rango de datos y unos botones a la izquierda de los encabezados de filas, que nos permiten visualizar el reporte de manera esquemática. El primero corresponde al total general; los intermedios, a los distintos niveles de subtotales; y el último nos mostrará todo el esquema abierto.

Esquemas

Un esquema permite agrupar y resumir los datos de una planilla. Cada grupo integra un nivel y recibe un número que lo identifica. Los niveles pueden contraerse para mostrar filas o columnas de resumen, o expandirse para mostrar el detalle de datos de cada grupo.

Los esquemas pueden tener hasta ocho niveles, y es posible generar agrupaciones de filas, de columnas y mixtas. Para acceder al grupo Esquema, tenemos que ingresar a la ficha Datos. En ella encontraremos las herramientas Agrupar, Desagrupar y Subtotal, que hemos visto anteriormente.

Esquema de filas

Antes de comenzar, es preciso respetar los siguientes detalles:

• Las columnas deben tener encabezados, y los datos dentro de ellas tienen que ser similares.
• El rango no debe contener columnas ni filas en blanco, si existe alguna tenemos que eliminarla.
• Los datos del criterio de agrupación deben estar ordenados. Por ejemplo, si tenemos una columna con regiones y otra con sucursales, y queremos agrupar las sucursales de cada región, debemos ordenar como primer criterio por región, y como segundo criterio por sucursales. De esta manera, podremos agrupar los datos y darle orden al esquema.
• Cada agrupación deberá tener una fila resumen con un cálculo de los datos, ya sea suma, conteo o promedio. Esto puede realizarse con la herramienta Subtotal o mediante una función.
• La fila resumen podrá estar por encima o por debajo de los datos. Si omitimos la fórmula de cálculo, deberemos igualmente dejar una fila con el título o resumen del grupo; de lo contrario, se asignará la anterior o posterior fila de datos agrupados como resumen del grupo.
• Para elegir la ubicación de las filas resumen, seleccionamos el rango y pulsamos la flecha inferior derecha del grupo Esquema. Se abrirá el cuadro Configuración, donde marcamos Filas resumen debajo del detalle.

Generación del esquema

Ahora podemos generar el esquema. Seleccionamos las filas del primer grupo del nivel inicial, sin incluir la fila resumen, y pulsamos Agrupar. A la izquierda del encabezado de filas se agregan dos botones, que marcan los niveles, y una línea vertical que abarca el grupo seleccionado.

Esta línea tiene un signo menos (-); si lo presionamos,el grupo se contrae y el signo se transforma en un signo más (+). Si pulsamos el signo más, el grupo vuelve a expandirse.

Realizamos la misma operación de agrupamiento por cada grupo del nivel inicial. Se agregarán entonces nuevas líneas de grupos, pero no nuevos niveles. Para agregar los niveles, primero seleccionamos las filas de un subconjunto de un grupo inicial y luego, presionamos Agrupar. Se añadirán un nuevo nivel y la línea correspondiente al subgrupo generado. Cada subgrupo debe tener su fila resumen.

Si presionamos los botones de los niveles que se han generado, el esquema se contraerá en los niveles inferiores y se expandirá en los superiores. Si tenemos un esquema con tres niveles, al presionar el botón del tercer nivel, el esquema se expandirá con todos sus detalles, y al presionar 1, lo veremos contraído; solo se mostrarán las filas de resumen de cada grupo del nivel inicial.

Opcoines avanzadas: Esquema de columnas

Las consideraciones para el esquema en columnas son similares a las del esquema de filas, solo que ahora necesitamos rótulos en cada fila de datos y que cada grupo tenga su columna de resumen. Por cada agrupación, seleccionamos las columnas y presionamos Agrupar.

Veremos los botones de esquema encima de los rótulos de las columnas, junto con la línea que marca cada grupo y su respectivo signo menos o más, dependiendo de si el grupo está expandido o contraído. Como en el esquema de filas, podemos elegir la ubicación del resumen. Para situarlo a la derecha, marcamos la opción Columnas resumen a la derecha del detalle.

Desagrupar un esquema

Para eliminar un esquema seleccionamos una celda del rango, desplegamos Desagrupar y pulsamos Borrar esquema. Desaparecerán los botones de niveles, las líneas y los signos de los grupos, y veremos la totalidad de los datos del rango original.

Aplicar formato a esquema

Si bien podemos darle un formato personalizado al esquema, también es posible utilizar una opción de esta herramienta. Seleccionamos los datos, desplegamos la flecha inferior derecha de Esquema y, en la ventana Configuración, presionamos Aplicar estilos.

El esquema quedará con el estilo predeterminado para cada nivel. Si en el cuadro está seleccionado Estilos automáticos, el formato para cada nivel se irá aplicando en la medida en que vayamos generando las agrupaciones respectivas.

Texto en columnas

Al trabajar con bases de datos en Excel, puede ocurrir que en una misma celda tengamos información que deseamos mostrar en celdas diferentes. Un ejemplo bastante común se da cuando tenemos datos sobre personas. En este caso, habitualmente aparecen el nombre y el apellido en una misma columna, y quizá necesitemos disponer de columnas distintas para cada parámetro.

La herramienta que nos permite dividir la información contenida en una celda para generar diferentes columnas es Texto en columnas, ubicada dentro del grupo Herramientas de datos de la ficha Datos.

Podemos utilizar esta herramienta cuando los datos están separados tanto por espacios como por algún carácter. Otro caso en el que podemos aplicarla es cuando parte de la información que queremos desagrupar es de un ancho fijo, es decir, posee una determinada cantidad de caracteres.

Un ejemplo de este último caso puede ser si en una misma celda tenemos la fecha y las unidades vendidas de un producto, y la cantidad de caracteres de la fecha siempre es la misma para todos los registros de la base de datos.

Aplicación

Veamos cómo funciona la herramienta. Primero seleccionamos el rango donde se sitúan las celdas que queremos dividir. Luego, pulsamos Texto en columnas y se abrirá el asistente. En el primer paso debemos especificar si los datos se encuentran Delimitados o son De ancho fijo.

Cuando los datos están separados por espacios o caracteres, elegimos la opción Delimitados. Una vez elegida la opción deseada, pulsamos Siguiente. En el próximo paso, el asistente nos ofrece como posibles separadores Tabulación, Punto y coma y Espacio. Si ninguno de estos corresponde a nuestro caso, elegimos Otro y completamos el carácter apropiado.

Si en el primer paso seleccionamos la opción De ancho fijo, debemos definir los anchos de los campos, en lugar de los separadores. Para hacerlo, tenemos que crear los saltos de columnas manualmente en la vista previa.

En el tercer y último paso, podemos definir el formato de los datos de las nuevas columnas. Las opciones son General, Texto y Fecha. Por defecto, los datos toman el formato General. La opción No importar columna permite indicar las columnas que no queremos que se generen. Además, podemos definir el Destino de las columnas y configurar, para datos numéricos, el Separador decimal y el Separador de miles, dentro del cuadro Avanzadas.

Si así lo prefiere, puede dirigirse a la siguiente URL para aprender más acerca de este tópico.

Si deseas, visita nuestra página de Facebook aprendeinformaticas. Tienes la libertad de compartir y comentar.