Manual de Excel 2010

Lección 22. Ordenar datos

La función Ordenar está destinada a solucionar los problemas de manejo de libros que contienen cantidades muy grandes de registros introducidos sin seguir ningún orden. Después de ordenar los datos, el programa devuelve todos los registros de la tabla en una nueva ubicación, siguiendo los criterios de ordenación. El orden se establecerá según un criterio numérico o alfabético, según sea el contenido de la columna seleccionada. En cada uno de los casos, el sentido puede ser ascendente o descendente. Si ejecutamos esta orden desde los iconos Ordenar de A a Z y Ordenar de Z a A del grupo de herramientas Ordenar y filtrar de la ficha Datos o bien del grupo Modificar de la ficha Inicio, la tabla se ordena por los contenidos de la columna seleccionada o la columna de la celda activa. En cambio, si utilizamos la herramienta Ordenar del grupo Ordenar y filtrar de la ficha Datos o la herramienta Orden personalizado del grupo Modificar de la ficha Inicio, accedemos a un cuadro de diálogo donde pueden establecerse hasta tres criterios de ordenación distintos. Desde Excel 2007 se pueden ordenar datos por color de celda o de fuente, por ejemplo, y por más de tres niveles (hasta un máximo de 64).

RECUERDE

Excel permite realizar muchos tipos de ordenaciones: textuales, numéricas, de fecha y hora, de más reciente a más antiguo, personalizadas, por formato. Aunque la mayoría de ordenaciones son de columnas, también es posible ordenar por filas.

1 Según el tipo de libro con el que esté trabajando en Excel, es probable que necesite ordenar alfabéticamente una lista de nombres, compilar una lista de niveles de inventario de productos de menor a mayor u ordenar filas por colores o por iconos. El orden de los datos facilita su visualización y su comprensión. Para practicar con las herramientas de ordenación de Excel 2010 utilizaremos el libro Festival.xlsx, con el que ya hemos trabajado antes y que puede encontrar en la zona de descargas de nuestra página web. Las herramientas de ordenación de datos se encuentran en el grupo Modificar de la ficha Inicio y en el grupo Ordenar y filtrar de la ficha Datos. Primero ordenaremos alfabéticamente la lista de nombres de los países participantes en el festival. Seleccione la celda A5.

2 Cuando se van a ordenar datos numéricos o de texto es importante que éstos estén almacenados con formato de número y con formato de texto respectivamente, ya que en caso contrario la ordenación podría no llevarse a cabo correctamente. Haga clic en el botón del grupo de herramientas Ordenar y filtrar y pulse sobre la opción Ordenar de A a Z. (1)

3 Observe que automáticamente toda la tabla se ha ordenado siguiendo como criterio de ordenación la inicial de los países participantes, puesto que el contenido de la celda seleccionada es de tipo texto. (2) Ahora ordenaremos la tabla de manera que la lista de países sea en orden alfabético descendente. En esta ocasión, utilizaremos las herramientas de ordenación de la ficha Datos. Haga clic en la pestaña Datos de la Cinta de opciones.

4 Pulse sobre el icono Ordenar de Z a A, que muestra esas letras y una flecha hacia abajo en el grupo de herramientas Ordenar y filtrar. (3)

5 Se hace efectiva la ordenación por orden alfabético de países. Ahora imaginemos que queremos ordenar la tabla de manera que se muestren en primer lugar los países participantes con mayor número de puntos y en último lugar los participantes con menor número de puntos totales. Haga clic sobre la herramienta Ordenar del grupo Ordenar y filtrar. (4)

Para incluir la primera fila de datos en una ordenación cuando no es un encabezado, desactive la opción Mis datos tienen encabezados en el cuadro Ordenar.

6 Esta herramienta abre el cuadro de diálogo Ordenar, donde debemos establecer los criterios de ordenación que debe seguir el programa. En este caso, la ordenación se basará en la columna cuyo encabezado es Total. Haga clic en el botón de punta de flecha del campo Ordenar por.

7 Como ve, este listado incluye los encabezados de todas las columnas que componen nuestra tabla de puntuaciones de ejemplo. Haga clic sobre la opción Total. (5)

8 A continuación, haga clic en el botón de punta de flecha del campo Ordenar según para ver las opciones que incluye.

9 Desde Excel 2007, es posible ordenar las celdas por color de celda o de fuente o por icono de celda. En este caso, puesto que nuestro libro de ejemplo no tiene aplicados colores ni iconos, mantendremos seleccionada la opción Valores. Pulse sobre ella.

10 Como queremos mostrar el total de resultados en orden descendente, mantendremos también seleccionada la opción De mayor a menor en el campo Criterio de ordenación. Pulse el botón Aceptar del cuadro Ordenar.

11 La tabla se ha ordenado correctamente siguiendo el criterio establecido. Sin embargo, puede ver que los participantes con el mismo número de puntos totales siguen ordenados de la Z a la A. (6) Vamos a acceder de nuevo al cuadro Ordenar para añadir un nivel de ordenación en el que especifiquemos que el orden alfabético de la Columna A debe ser de la A a la Z. Pulse el botón Ordenar del grupo de herramientas Ordenar y filtrar.

12 En el cuadro Ordenar, pulse sobre el botón Agregar nivel. (7)

13 Podemos añadir hasta un máximo de 64 niveles de ordenación. Haga clic en el botón de punta de flecha del campo Luego por y seleccione la opción Columna A. (8)

14 Mantendremos la opción Valores en el campo Ordenar según y la opción A a Z en el campo Criterio de ordenación, pero antes de aceptar la operación, veamos qué opciones incluye este cuadro. Pulse sobre el botón Opciones.

15 El cuadro Opciones de ordenación nos permite distinguir entre mayúsculas y minúsculas así como cambiar la orientación del orden. Ciérrelo pulsando el botón Cancelar. (9)

Si el resultado no es el que esperaba al realizar la ordenación, pulse el icono Deshacer.

16 De nuevo en el cuadro Ordenar, pulse el botón Aceptar para ordenar de nuevo la tabla.

17 Puede comprobar que la ordenación se ha llevado a cabo correctamente mirando los países que tienen el mismo número de puntos totales. (10) Para acabar, veremos qué ocurre si intentamos ordenar una selección de datos contigua a otras columnas con datos sin entrar en el cuadro Ordenar. Haga clic en la celda E5 para seleccionarla.

18 Pulse la tecla Mayúsculas y, sin liberarla, haga clic sobre la celda E20 para seleccionar ese rango de celdas.

19 Ahora pulse sobre la herramienta Ordenar de A a Z del grupo Ordenar y filtrar.

20 Antes de llevar a cabo la ordenación, Excel lanza un mensaje en el que nos advierte de que existen datos junto a nuestra selección y nos permite ampliarla o continuar con ella. Si continuamos con la selección, los datos contiguos no serán ordenados, lo que nos conducirá a una ordenación errónea. Con la opción Ampliar la selección activa, pulse el botón Ordenar del cuadro Advertencia antes de ordenar. (11)

21 De este modo, todos los datos que contiene la tabla se han ordenado correctamente. (12) Haga clic en la celda A3 para eliminar la selección del rango de celdas E5:E20.

22 Por último, guarde los cambios pulsando el icono Guardar de la Barra de herramientas de acceso rápido.

Lección 23. Aplicar filtros

Para manejar tablas y listados con un gran número de datos, Excel pone a disposición del usuario diferentes herramientas. Una de ellas es la función Buscar del grupo de herramientas Edición de la ficha Inicio, que permite localizar celdas que contengan cadenas de texto o numéricas. El inconveniente de esta función es que las coincidencias se muestran de una en una, seleccionándose las celdas. También disponemos de la herramienta Ordenar, que hemos conocido en el ejercicio anterior, que también presenta un inconveniente: el orden de la tabla es modificado de forma definitiva. Los filtros constituyen la función más adecuada cuando se trata de localizar valores concretos. Con ellos, podemos obtener, agrupadas de forma provisional, las celdas cuyos contenidos contengan uno o más criterios.

1 Para practicar con la función Autofiltro de Excel 2010 utilizaremos los libros de ejemplo Festival.xlsx y Precios01.xlsx (este último es una versión ligeramente modificada del libro Precios que hemos ido completando a la largo de estas lecciones). Ya sabe que puede encontrar estos libros en la zona de descargas de nuestra página web. Utilizaremos la herramienta Autofiltro para localizar registros que cumplan criterios, sin modificar el orden ni el contenido de las listas. Empezamos en el libro Festival.xlsx. Haga clic en la celda B4 y pulse el botón Filtro del grupo de herramientas Ordenar y filtrar de la ficha Datos. (1)

Sepa que también puede habilitar la función de filtrado de datos pulsando la combinación de teclas Ctrl.+Mayúsculas+L.

2 En cada una de las celdas que el programa ha interpretado como rótulos, se ha situado un botón de flecha. Imaginemos que sólo queremos mostrar los países participantes que en la primera ronda obtuvieron más de 10 puntos. Pulse el botón de flecha de la celda B4.

3 Se despliega así un menú de autofiltro que nos permite ordenar los valores de la columna en cuestión o filtrarlos. Pulse en la opción Filtros de número y seleccione el comando Mayor que. (2)

4 Se abre de este modo el cuadro Autofiltro personalizado, en el que debemos establecer un valor para la condición que hemos elegido. En el campo de texto situado junto a la opción es mayor que, escriba el valor 10 y después pulse el botón Aceptar. (3)

5 Observe el resultado. De la manera más fácil y sin que se elimine ningún dato, la tabla muestra únicamente los registros que cumplen la condición especificada, esto es, los países con una puntuación en primera ronda superior a 10 puntos. (4) Vea que el botón de flecha se ha convertido en un embudo para indicar que la columna de datos tiene un filtro aplicado. Para quitar este filtro, pulse el botón Borrar del grupo de herramientas Ordenar y filtrar. (5)

6 Veamos a continuación el modo de combinar dos criterios de filtro. Pulse nuevamente el botón de flecha de la celda B4 y, en el menú de autofiltro, desactive las puntuaciones 2, 3, 4 y 5 pulsando en sus casillas de verificación. (6)

7 Pulse el botón Aceptar.

8 Ahora sólo se muestran los países que obtuvieron más de 5 puntos en la primera ronda. Abra el filtro de la columna Total, desactive las puntuaciones 16, 21 y 22 y pulse el botón Aceptar. (7)

9 La lista se reduce más aún. Una vez hemos conocido el sencillo funcionamiento de la función Filtro, la utilizaremos en nuestro libro Precios01.xlsx. Deshabilite el filtrado de celdas pulsando el botón Filtro del grupo de herramientas Ordenar y filtrar y abra el libro Precios01.xlsx.

RECUERDE

Puede hacer que el menú Autofiltro sea más ancho o más largo arrastrando el controlador que aparece en su margen inferior.

10 Como ve, al libro que hemos ido creando a lo largo de estas lecciones le hemos añadido algunos productos más con sus correspondientes precios. Imagine que desea filtrar el listado de manera que sólo se muestren aquellos productos cuyo precio de coste es igual o superior a 5 euros. Haga clic en la celda C1, que contiene el encabezado Precio Coste.

11 Esta vez aplicaremos el filtro desde la ficha Inicio de la Cinta de opciones. Actívela pulsando en su pestaña, pulse el botón Ordenar y filtrar del grupo de herramientas Modificar y elija la opción Filtro. (8)

12 Aparecen así los iconos de filtro en cada una de las cabeceras de columna. Pulse el botón de flecha de la celda C1, haga clic en la opción Filtros de número y elija Mayor o igual que.

13 Nuevamente accedemos al cuadro Autofiltro personalizado. En el campo de texto de la primera condición, escriba el valor 5 y pulse el botón Aceptar.

14 ¡Correcto! El listado muestra ahora únicamente aquellos artículos cuyo precio de coste es igual o superior a 5 euros. Lógicamente, las columnas que muestran los precios de venta también muestran valores superiores a ése. (9) Puede imaginar la enorme utilidad de la función de filtrado de Excel cuando se trabaja con listas de datos extensas y se desea localizar con rapidez valores concretos. Para deshabilitar el filtro y dar por acabado este ejercicio, pulse nuevamente el botón Ordenar y filtrar y haga clic en el comando Filtro.

Cuando el listado que vaya a filtrar sean muy extenso y contenga mucho valores distintos, utilice el cuadro de búsqueda de la función Autofiltro para localizar en el panel los que le interesan.

Lección 24. Filtros avanzados

La herramienta Autofiltro se complementa con la función denominada Filtro avanzado. A diferencia de la primera, la herramienta Filtro avanzado sólo se encuentra en la ficha Datos de la Cinta de opciones. Para ejecutar un filtro avanzado, debemos construir una pequeña tabla paralela, denominada Rango de criterios, que contenga los rótulos de las categorías y los criterios. Al seleccionar este rango, el programa interpreta como criterios de filtro las palabras situadas debajo de cada categoría. Si no se especifica lo contrario, el filtro se aplica a la totalidad de la tabla, sin necesidad de seleccionarla. En el cuadro de diálogo Filtro avanzado, disponemos también de la posibilidad de copiar el resultado de la operación en otro punto de la hoja o del libro.

1 Seguimos trabajando con el libro de ejemplo Precios.xlsx. Para trabajar con filtros avanzados, lo primero que haremos será introducir el rótulo de la categoría y el criterio de filtrado que queremos establecer como rango de criterios. Haga clic en la celda G1, escriba el término Precio Coste y pulse la tecla Retorno.

2 En la celda G2, ahora seleccionada, inserte la cifra 12 y vuelva a pulsar la tecla Retorno.

3 Con este criterio filtraremos el contenido del libro de manera que sólo se muestren los productos con un precio de coste de 12 euros. Ahora debemos seleccionar alguna de las celdas de la tabla original. De este modo, el programa interpretará automáticamente que el rango de la lista a partir del cual se realiza el filtro avanzado es la tabla en cuestión. Haga clic en la celda C3.

4 Sitúese en la ficha Datos de la Cinta de opciones (1) y pulse en el botón Avanzadas del grupo de herramientas Ordenar y filtrar. (2)

5 Se abre el cuadro Filtro avanzado, que muestra unos valores predeterminados. Como ve, el rango que se debe filtrar tomado por el programa por defecto comprende toda la tabla. Sólo nos falta establecer el rango de criterios. Haga clic dentro del campo Rango de criterios. (3)

6 Haga clic en la celda G1 y, manteniendo pulsada la tecla Mayúsculas, seleccione también la celda G2. (4)

Los iconos que aparecen a la derecha de los campos del cuadro Filtro avanzado permiten minimizarlo cuando sea necesario para seleccionar las celdas directamente en la hoja.

7 Los criterios del filtro han quedado establecidos. Ahora podemos elegir entre filtrar la lista sin moverla de lugar o bien copiarla en otro punto de la hoja. Haga clic en el botón de opción Copiar a otro lugar.

8 Al elegir esta opción, se activa un nuevo campo, Copiar a, en el que debemos establecer el lugar en el que se copiará el resultado del filtrado de datos. Puede copiar la lista resultante del filtrado en una nueva hoja o en la misma con la que está trabajando e incluso puede copiarla en otro libro que tenga abierto en este momento. Haga clic en el campo Copiar a y pulse en la celda G3 para seleccionarla como destino de la copia. (5)

Si se activa la opción Sólo registros únicos de este cuadro, los valores únicos del rango seleccionado se copiarán en la ubicación indicada.

9 Una vez establecidas todas las condiciones para el filtro avanzado, pulse el botón Aceptar.

RECUERDE

El filtrado avanzado es una operación que no puede deshacerse (vea que el icono Deshacer de la Barra de herramientas de acceso rápido está desactivado) por lo que para borrar el resultado deberá eliminar el contenido de las celdas.

10 El resultado es la visualización de los dos productos de nuestro listado de ejemplo cuyo precio de coste es de 12 euros. Mediante el arrastre, seleccione el rango de celdas G1:K5. (6)

11 Pulse la tecla Suprimir para borrar el contenido de las celdas seleccionadas y acabe el ejercicio pulsando el icono Guardar de la Barra de herramientas de acceso rápido. (7)

Lección 25. Importar datos

Excel 2010 ofrece la posibilidad de importar datos tanto directamente del web como de distintas bases de datos y archivos ya existentes, entre otras posibilidades. En esta lección aprenderemos a importar datos procedentes de bases de datos y archivos de texto. Las herramientas de importación de datos se encuentran en el grupo de herramientas Obtener datos externos, en la ficha Datos de la Cinta de opciones. Esas herramientas permiten importar datos desde una base de datos de Microsoft Access, desde una página Web, desde un archivo de texto o desde otros orígenes de datos que se incluyen en el botón De otras fuentes. En esta lección importaremos los datos de texto y de base de datos de nuestros propios archivos de origen de datos.

1 En este ejercicio aprenderemos a importar datos desde una sencilla base de datos de Access denominada Productos que puede descargar desde nuestra página web y almacenar en la carpeta Mis archivos de origen de datos de su equipo. Conocemos de antemano, pues, el nombre del archivo de origen de datos que deseamos importar y también el lugar donde deseamos ubicarlo, la Hoja2. Active la Hoja2 del libro Precios01.xlsx pulsando sobre su etiqueta, en la parte inferior del libro.

RECUERDE

Al importar datos, debe tener en cuenta que, en la mayoría de casos, es necesaria la obtención de una contraseña u otro tipo de información sobre la conexión que tal vez no poseamos si no es a través del administrador de la base de datos en cuestión. No obstante, desde Excel 2007, un administrador de conexiones de Excel nos permite ver todas las conexiones de un libro y hace más sencilla la tarea de volver a utilizar una de ellas o de sustituir una por otra.

2 Haga clic sobre el botón Desde Access del grupo de herramientas Obtener datos externos de la ficha Datos. (1)

Puede establecer cada cuánto tiempo desea actualizar los datos.

3 En el cuadro Seleccionar archivos de origen de datos haga doble clic sobre la carpeta Mis archivos de origen de datos para ver su contenido.

4 Haga clic sobre el archivo Productos y pulse el botón Abrir. (Recuerde que, si lo desea, puede utilizar sus propios archivos para realizar los ejercicios.) (2)

5 En el cuadro Importar datos debemos establecer el modo en que los datos se mostrarán en el libro así como indicar el punto exacto de la hoja actual o bien de una nueva hoja de cálculo donde se ubicarán. En este caso, mantendremos activada la opción Tabla para que los datos aparezcan a modo de tabla y la celda A1 de la hoja de cálculo actual para que se ubique en ese punto del libro. Antes de aceptar la importación de los datos de contactos, veamos cuáles son las propiedades de los mismos. Pulse el botón Propiedades de la ventana Importar datos.

6 En el cuadro Propiedades de conexión, haga clic en la casilla de verificación de la opción Actualizar cada. (3)

7 Así, cuando cambiemos los datos en la base de datos, éstos se actualizarán en la hoja de cálculo cada 60 minutos, el tiempo especificado por defecto. Pulse el botón Aceptar del cuadro Propiedades de conexión.

8 Pulse el botón Aceptar de la ventana Importar datos para que el rango de datos sea importado directamente a la hoja activa.

9 La operación se ha llevado a cabo correctamente y ahora, en el punto indicado de la hoja 2 se ha añadido la tabla de nuestros nuevos productos, a la vez que aparece en la Cinta de opciones la ficha Herramientas de tablas, con cuyas herramientas de diseño podemos modificar el aspecto de la tabla. Trabajaremos con ellas en otro ejercicio. Ahora importaremos un documento de texto (el denominado Productos2, que también puede descargar desde nuestra página web y almacenar en su carpeta Documentos) en esta misma hoja del libro para comprobar que el proceso es muy similar al que acabamos de ver. Seleccione la celda A10.

10 Haga clic en la pestaña Datos de la Cinta de opciones y pulse el botón Desde texto del grupo de herramientas Obtener datos externos. (4)

11 En este caso, se abre el cuadro Importar archivo de texto mostrando el contenido de la carpeta Documentos. Localice y seleccione el archivo de ejemplo Productos2 y pulse el botón Importar. (5)

Observe en el cuadro Importar archivo de texto los formatos de archivo de texto que pueden importarse a Excel 2010.

12 Al importar un documento de texto, aparece el Asistente para importar texto, que consta de tres pasos en los que debemos definir las condiciones de la importación. En el apartado Vista previa puede ver el aspecto que tendrá el documento cuando lo importemos a la hoja de cálculo. En este caso, haga clic en el botón de opción Delimitados para indicar que las comas de nuestro documento de ejemplo separan campos y pulse el botón Siguiente. (6)

13 La siguiente pantalla nos permite establecer los separadores contenidos en los datos. Mantenga en este caso el separador Tabulación y pulse el botón Siguiente. (7)

14 Y la última pantalla permite seleccionar las columnas y establecer el formato de los datos que contienen. Mantenga el formato General para los datos de las columnas y pulse el botón Finalizar. (8)

15 Como puede ver, los datos de tipo texto sólo pueden importarse como tabla pero también podemos elegir el punto de la hoja actual o de una hoja nueva donde se situarán. Haga clic en el botón Aceptar del cuadro Importar datos para que los datos de texto se importen como tabla y se coloquen a partir de la celda A10. (9)

Puede importar datos externos en la misma hoja de cálculo con la que está trabajando o en otra distinta.

16 Hemos aprendido a importar datos desde una base de datos y desde un documento de texto. Antes de acabar, veamos desde qué otras fuentes nos permite Excel 2010 importar datos. Haga clic en la celda A14.

17 Pulse el botón De otras fuentes del grupo de herramientas Obtener datos externos.

18 Éstas son las fuentes desde las cuales podemos importar datos en Excel 2010. Entre ellas se encuentran servidores SQL, Microsoft Query, etc. (10) Cierre el menú que aparece en pantalla pulsando el botón De otras fuentes.

19 Para acabar este ejercicio, guarde los cambios pulsando el icono Guardar de la Barra de herramientas de acceso rápido. (11)