Excel Intermedio

Por: C.C.

1

Dar formato a una tabla en Excel Después de crear una tabla en Excel tendremos un formato predeterminado para las celdas de la tabla sin embargo posible aplicar un estilo diferente e inclusive crear nuestro propio estilo de tabla para utilizarlo posteriormente.

Cambiar el estilo de una tabla en Excel Para cambiar el estilo predeterminado de una tabla en Excel debes seleccionar alguna celda de la tabla e ir a la ficha contextual Herramientas de tabla > Diseño > Estilos de tabla y seleccionar cualquiera de los estilos mostrados:

Si la ventana de Excel está muy angosta, es probable que encuentres los estilos de tabla bajo el botón Estilos rápidos, pero si puedes ver los estilos tal como en la imagen superior, entonces puedes hacer clic en los controles de la derecha para desplazarte por todos ellos. Estas mismas opciones de estilo de tablas las puedes encontrar en Inicio >

Por: C.C.

2

Estilos > Dar formato como tabla.

Cuando expandimos todo el menú de estilos de tabla podrás notar que están organizados en tres categorías: Claro, Medio y Oscuro. Es posible mover el puntero del ratón sobre alguno de los estilos para tener una vista previa de la tabla con el estilo seleccionado y para hacer permanentes los cambios debemos hacer clic sobre el estilo. También es posible variar un poco el estilo aplicado a una tabla utilizando las cajas de selección que se encuentran en Herramientas de tabla > Diseño > Opciones de estilo de tabla:

Por: C.C.

3

      

Fila de encabezado: Activa o desactiva la fila de encabezado de la tabla. Fila de totales: Activa o desactiva la fila de totales de la tabla. Filas con bandas: Muestra filas con bandas, es decir, aplica un formato especial donde las filas pares tienen un fondo diferente al de las filas impares. Primera columna: Aplica un formato especial a la primera columna de la tabla. Última columna: Aplica un formato especial a la última columna de la tabla. Columnas con bandas: Aplica un fondo especial a las columnas impares de manera que se distingan de las columnas pares. Botón de filtro: Activa o desactiva el botón de filtro de los encabezados de columna.

Alternativas adicionales de estilo Es posible que después de trabajar por un tiempo con los mismos estilos de tabla quieras tener nuevas opciones. Para visualizar estilos diferentes es suficiente con ir a

Por: C.C.

4

Diseño de Página > Temas > Temas y elegir un tema diferente al de Office:

Una vez que hayas elegido un nuevo tema, tendrás opciones diferentes para los estilos de una tabla en Excel.

Crear un nuevo estilo de tabla Si ninguna de las opciones anteriores es de tu agrado, entonces puedes crear tu propio estilo de tabla y para eso debes expandir el menú de estilos y seleccionar la opción Nuevo estilo de tabla y se mostrará el siguiente cuadro de diálogo:

Por: C.C.

5

En medio de la ventana se mostrará una lista de todos los elementos de la tabla y para cada uno de ellos podrás pulsar el botón Formato y definir el estilo deseado. Si lo deseas, puedes marcar el nuevo estilo como predeterminado de manera que se aplique automáticamente al momento de crear una tabla en Excel. De todos modos el estilo aparecerá en una nueva categoría llamada Personalizada de donde podrás elegirlo para aplicarlo a cualquier tabla. Si después de crear tu propio estilo deseas eliminarlo es tan sencillo como hacer clic derecho sobre él y seleccionar la opción Eliminar.

Convertir una tabla en rango Por: C.C.

6

Ya hemos visto los beneficios que nos da tener nuestros datos en una tabla de Excel pero si por alguna razón necesitas convertir la tabla de nuevo en un rango podrás hacerlo utilizando el comando Herramientas de tabla > Diseño > Herramientas > Convertir en rango.

Esto removerá las características de tabla pero dejará el formato de las celdas el cual podrás quitar fácilmente seleccionando el rango de celdas y posteriormente el comando Inicio > Estilos > Estilos de celdas > Normal.

Formato condicional en Excel. El formato condicional en Excel nos permite aplicar de manera selectiva un formato a las celdas de acuerdo a su contenido. Este formato se aplicará de manera automática sobre la celda después de evaluar el cumplimiento de la regla de formato condicional.

Para qué sirve el formato condicional Es muy común entre los usuarios de Excel querer cambiar la apariencia de una celda en base a su valor o al resultado de otra fórmula. Sin embargo, no existe una función de Excel que nos permita cambiar el color de fondo de una celda o el color de la fuente y por esa razón se creó el formato condicional en Excel que nos permite evaluar el valor de una celda para decidir si se aplicará o no el formato definido.

Es así como el formato condicional nos sirve para detectar de una manera visual a aquellas celdas que cumplen con una condición establecida. Cada vez que cambiemos el

Por: C.C.

7

valor de una celda, Excel volverá a hacer la evaluación de la regla para saber si debe seguir aplicando el formato.

Reglas de formato condicional Podemos definir una regla de formato condicional como la condición que debe cumplirse para que Excel aplique el formato a una celda, si la regla no se cumple entonces no se hará ningún cambio a la apariencia de la celda. Una regla de formato condicional nos ayuda a especificar dos cosas:  

El criterio de decisión para saber si se debe aplicar el formato a una celda. El formato que se debe aplicar en caso de que se cumpla la regla.

Cada regla de formato condicional se puede aplicar a una sola celda o a un rango de celdas y una misma celda puede tener varias reglas de formato condicional aplicadas sobre ella pero será visible aquella regla que tenga la mayor prioridad. Dicha prioridad se define dentro del Administrador de reglas de formato condicionales.

Formato de celdas Al crear una regla de formato condicional podremos indicar el detalle del formato de la celda que será aplicado cuando se cumpla la condición establecida. Para la mayoría de las reglas tendremos la posibilidad de ingresar al cuadro de diálogo Formato de celdas para especificar todo el detalle del formato.

Por: C.C.

8

   

Formato: En la sección Formato podemos establecer el formato para los números. Fuente: Nos permite elegir la fuente a utilizar así como su tamaño y estilo. Borde: Podemos especificar el estilo y color de los bordes de la celda. Relleno: Elegimos el color de fondo de la celda o una trama.

Todas estas características integran el formato de la celda que será aplicado cuando se cumpla la regla de formato condicional.

Ejemplo de formato condicional Tenemos un rango de celdas con números entre 1 y 100 y deseamos resaltar las celdas que tengan un valor mayor que 60. Para crear la regla de formato condicional seleccionamos primero el rango de celdas a las que aplicaremos el formato condicional y luego vamos a Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Es mayor que.

Por: C.C.

9

Se mostrará el cuadro de diálogo Es mayor que donde podremos indicar el valor mínimo que deben cumplir las celdas y también podremos elegir el formato que será aplicado:

Mientras especificamos los valores en el cuadro de diálogo, Excel mostrará una vista previa y podremos ver de antemano cómo se verán las celdas al aplicar el formato seleccionado. Al pulsar el botón Aceptar se creará la nueva regla de formato condicional y será aplicada sobre todas las celdas del rango previamente seleccionado.

Por: C.C.

10

Formato condicional en Excel. En esta ocasión realizaremos algunos ejemplos de formato condicional en Excel 2013 para dejar en claro el potencial y ventajas de uso que nos ofrece esta funcionalidad al momento de analizar nuestros datos.

Formato condicional en números Cuando los datos de nuestras celdas son valores numéricos, Excel provee de varias opciones para aplicar un formato condicional rápidamente. Las primeras alternativas que analizaremos serán las que se encuentran en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas.

Sabiendo que nuestros datos son valores numéricos, podemos utilizar las siguientes opciones: 

Es mayor que: Se aplicará el formato a todas las celdas con un valor mayor al especificado.

Por: C.C.

11

  

En menor que: El formato será aplicado a las celdas con un valor menor que el indicado. Entre: Excel evaluará las celdas para saber aquellas que tengan un valor dentro del rango indicado y se les aplicará el formato. Es igual a: Solo las celdas que sean iguales al valor indicado tendrán el formato.

Una vez que seleccionamos la opción que deseamos utilizar, Excel mostrará un cuadro de diálogo que nos permitirá indicar los valores numéricos con los cuales se realizará la comparación y también podremos proporcionar todo el detalle del formato a aplicar. En la siguiente imagen puedes notar la aplicación de cada una de las reglas mencionadas anteriormente sobre nuestros datos numéricos:

Otras reglas de formato condicional que podemos aplicar rápidamente a celdas con valores numéricos son las que se encuentran bajo la opción de menú Reglas superiores e inferiores:

Por: C.C.

12

A continuación describo brevemente cada una de estas opciones de formato condicional. 



  



10 superiores: Se aplicará el formato exactamente a las 10 celdas que tengan los valores más altos. Es posible modificar la cantidad de celdas superiores a las que se aplicará el formato. 10% de valores superiores: Excel aplicará el formato al 10% de las celdas que contengan los valores más altos. También es posible indicar un porcentaje diferente al 10%. 10 inferiores: El formato se aplica a las 10 celdas con los valores más bajos. 10% de valores inferiores: El formato es aplicado al 10% de las celdas con los valores más bajos dentro del rango. Por encima del promedio: Excel obtiene el promedio de todos los valores numéricos del rango y aplica el formato a las celdas que tengan un valor por encima de dicho promedio. Por debajo del promedio: Después de obtener el promedio, el formato será aplicado en las celdas que tengan un valor inferior.

En la siguiente imagen vemos aplicada cada una de las reglas de formato condicional anteriores:

Por: C.C.

13

Con estos ejemplos de formato condicional podemos ver lo fácil que es utilizar esta funcionalidad para resaltar los valores numéricos de nuestro interés.

Formato condicional en texto Si nuestras celdas contienen texto podemos utilizar algunas opciones de formato condicional para resaltar nuestras celdas. La primera opción que podemos utilizar es la regla que nos ayuda a saber si un valor es igual a otro, me refiero a la opción que se encuentra en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Es igual a.

Esta opción comparará el valor de cada celda con la cadena de texto especificada y en caso de ser iguales se aplicará el formato. Es importante mencionar que esta regla no es sensible a mayúsculas y minúsculas. Otra regla de formato condicional para texto que Por: C.C.

14

podemos utilizar es la opción Texto que contiene que se encuentra en el mismo menú que la opción anterior, solo que en este caso, Excel buscará las celdas que contengan la cadena de texto especificada. Observa un ejemplo de ambas reglas:

Formato condicional en fechas Si los datos que tenemos en nuestra hoja son fechas, entonces existe una opción especialmente diseñada para este tipo de datos. Podemos aplicar formato condicional a celdas que contienen fechas desde Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Una fecha. Al hacer clic sobre esta opción se mostrará el siguiente cuadro de diálogo:

Por: C.C.

15

Esta opción nos permitirá resaltar fácilmente las celdas que contengan una fecha que cumpla con el criterio seleccionado: Hoy, Ayer, Mañana, En los últimos 7 días, Semana pasada, etc.

Resaltar duplicados con Formato condicional También es posible utilizar el formato condicional para resaltar duplicados en nuestros datos. Esta opción funciona para cualquier tipo de dato que tengamos en las celdas, ya sean números, fechas e inclusive texto. La opción que tenemos que elegir para resaltar valores duplicados se encuentra en Inicio > Estilos > Formato condicional > Resaltar reglas de celdas > Duplicar valores:

Por: C.C.

16

Esto mostrará un cuadro de diálogo que nos permitirá elegir si queremos resaltar los valores que están duplicados o los valores únicos. A continuación puedes ver un ejemplo de estas opciones sobre nuestros datos:

Por: C.C.

17

La regla Duplicados se aplicará sobre todos los elementos que aparecen más de una vez y la regla Únicos será para las celdas que solamente aparecen una vez dentro del rango. Con estos ejemplos de formato condicional en Excel podemos darnos cuenta de los beneficios que tiene esta funcionalidad para ayudarnos a resaltar rápidamente celdas que cumplen con algún criterio establecido. Además de las opciones revisadas hasta ahora existen otros tipos de formato condicional que revisaremos en lecciones posteriores.

Por: C.C.

18

Filtrar números en Excel Excel nos provee de diversas opciones para filtrar valores numéricos. Es posible obtener aquellos valores que están dentro de un rango o inclusive aquellos números mayores o menores a un límite previamente establecido. En esta ocasión revisaremos con detenimiento las opciones que tenemos para filtrar números en Excel. La siguiente imagen muestra los datos que utilizaremos en nuestros ejemplos. En la primera columna se observan los nombres de alumnos y en la segunda columna tenemos las calificaciones obtenidas por cada uno de ellos en una escala de 100.

Para este ejemplo he creado una tabla de Excel (Insertar > Tabla) pero los ejemplos funcionan correctamente aunque solo se inserten filtros con el comando Inicio > Modificar > Ordenar y filtrar > Filtro. Para ver las opciones de filtrado que nos ofrece Excel para nuestros datos numéricos debemos hacer clic sobre el botón de flecha en el encabezado de la columna Calificación y elegir la opción Filtros de número.

Por: C.C.

19

Es importante recordar que la opción Filtros de número solo será mostrada en aquellas columnas donde Excel haya detectado que los valores son numéricos.

Filtrar números iguales o diferentes a otro número Las primeras dos opciones que revisaremos serán las opciones “Es igual a” y “No es igual a”. Al seleccionar la opción Es igual a obtendremos un cuadro de diálogo como el siguiente:

Por: C.C.

20

Lo único que debemos ingresar es ese valor que estamos buscando y pulsar el botón Aceptar. En la imagen anterior ingresé el valor 80 y por lo tanto obtendré el siguiente resultado:

Por el contrario, si elegimos la opción de filtrado No es igual a e ingresamos el valor 80, al aplicar el filtro obtendremos el siguiente resultado:

Si comparas estos datos con el listado original, podrás notar la ausencia de la fila correspondiente a Miguel quien tiene la calificación igual a 80. De esta manera, el filtro ha excluido aquellos valores que no son iguales al valor indicado.

Cómo remover un filtro Antes de continuar, quiero recordarte que para eliminar un filtro será suficiente con elegir la opción “Borrar filtro de” que se mostrará al hacer clic en el botón de flecha del encabezado que tenga el filtro.

Por: C.C.

21

Filtrar números mayores o menores que otro número El segundo grupo de opciones de filtrado que tenemos disponibles nos ayudan a obtener aquellos números que son mayores o menores que otro número que hayamos indicado. Por ejemplo, seleccionaré la opción Mayor que y posteriormente ingresaré el valor 80.

Al hacer clic en el botón Aceptar obtendré el siguiente resultado.

Por: C.C.

22

El filtro nos permite ver aquellos valores que son superiores a 80 pero excluye aquellos registros que son iguales a 80. Si quisiéramos incluir ese límite inferior tendríamos que elegir la opción Mayor o igual que. Con el siguiente ejemplo nos quedará claro lo que significa elegir alguna opción de filtrado que incluya la igualdad. Seleccionaré la opción Menor o igual que e ingresaré el valor 80 como se observa en la siguiente imagen.

Como resultado tendremos todos los valores menores a 80 y también obtendremos el registro que es igual a 80. La siguiente imagen muestra el resultado de dicho filtro.

Por: C.C.

23

Filtrar números entre un rango Excel nos permite filtrar aquellos números que se encuentra dentro de un rango utilizando la opción Entre. En el siguiente ejemplo aplicaré un filtro indicando los valores 70 y 90 como los límites inferior y superior del rango por el cual deseo filtrar los datos.

Observa que esta opción de filtrado es la combinación de las opciones Mayor o igual a y Menor o igual a. Al aplicar el filtro obtendré el siguiente resultado:

Filtrar los diez mejores Aunque la opción de filtrado está nombrada como “Diez mejores” la verdad es que puedes utilizarla para obtener uno, dos, cinco, veinte, treinta o los valores que necesites. De manera predeterminada el filtro nos dará la opción de filtrar los 10 valores superiores, pero podrás editar ese número por cualquier otro que necesites.

Por: C.C.

24

En la imagen anterior he indicado al filtro obtener solamente los 3 valores superiores y por lo tanto el resultado será el siguiente:

Esta opción de filtrado también es útil para obtener los valores más pequeños es decir, los valores inferiores de una lista de números. Lo único que debes hacer es elegir la opción “inferiores” de la primera lista desplegable.

Además, la última lista desplegable nos permite elegir un porcentaje en lugar de una cantidad específica de elementos.

Lo que la imagen anterior quiere decir es que, podremos obtener el 25% de elementos superiores de nuestra lista de números. Esto es de gran ventaja cuando no conocemos la cantidad exacta de elementos que deseamos obtener y solo queremos indicar un porcentaje.

Filtrar números por su promedio Por: C.C.

25

El último par de opciones de filtrado se refieren al promedio de los valores numéricos de la columna, así que es buena idea conocer dicho promedio antes de aplicar el filtro. Podemos conocer fácilmente ese valor al seleccionar todo el rango de celdas y observar el promedio en la barra de estado.

Para nuestro ejemplo, el promedio de los valores numéricos es 78.92 así que al seleccionar el filtro Superior del promedio, obtendremos el siguiente resultado:

Todos los valores de la imagen anterior son superiores al promedio. En caso de elegir la opción de filtrado Inferior al promedio, obtendremos el otro grupo de registros de nuestra lista.

Por: C.C.

26

Filtro personalizado en Excel En varias de las opciones de filtrado anteriores pudiste observar un cuadro de diálogo como el siguiente:

En dichas opciones, Excel seleccionará de manera predeterminada algún valor de la lista, pero al elegir la opción de Filtro personalizado las listas aparecerán sin selección previa para permitirnos elegir cualquiera de las opciones disponibles.

Aunque en este listado aparecen todas las opciones de filtrado de Excel, debes tener cuidado porque no podrás utilizar todas ellas para filtrar números. Por ejemplo, las opciones: comienza por, no comienza por, termina con, no termina con, contiene, no contiene, son utilizadas exclusivamente para filtrar cadenas de texto. Con el resto de las opciones podrás hacer combinaciones para crear un filtro más específico sobre tus datos. Por ejemplo, para obtener aquellos registros que sean iguales a 60 y 80 puedo utilizar el filtro de la siguiente manera:

Por: C.C.

27

La clave para obtener el resultado deseado es la selección de la opción “O” la cual indicará a Excel que filtre en base a cualquiera de las dos condiciones. El resultado es el siguiente:

Filtrar números pares o impares en Excel Las opciones de filtrado que hemos revisado hasta ahora son todas las que Excel nos permite aplicar sobre valores numéricos sin embargo es posible crear algunos filtros diferentes utilizando las funciones de Excel. Por ejemplo, una de las necesidades frecuentes de los usuarios es aplicar un filtro para obtener los números pares o impares. Para lograrlo haremos uso de la función ES.PAR sobre una nueva columna que agregaré a la tabla de datos de la siguiente manera:

Por: C.C.

28

La función ES.PAR evalúa cada número de la columna Calificación y nos dice si es par o no. Ya que mis datos están en una tabla de Excel, la función ES.PAR utiliza referencias estructuradas [@Calificación] para referirse a la columna Calificación pero podría funcionar correctamente con referencias relativas. Para visualizar solamente los números pares debo aplicar un filtro sobre la columna ES.PAR y seleccionar la opción VERDADERO como se muestra en la siguiente imagen:

Por: C.C.

29

Como resultado de aplicar este filtro obtendremos solamente los números pares.

Para visualizar solamente los números impares será suficiente con elegir el valor FALSO en el filtro de la columna ES.PAR.

Por: C.C.

30

Filtrar datos por fecha Si los datos de tu hoja de Excel incluyen alguna columna con fechas, podrás aplicar un filtro que te permitirá obtener solamente aquellos datos que se encuentren dentro del rango de fechas establecido en el filtro. Crear un filtro para tus datos es muy sencillo y tienes dos opciones: puedes convertir tu rango en una tabla de Excel con el comando Insertar > Tabla o puedes agregar un filtro a los datos con el comando Inicio > Ordenar y filtrar > Filtro. La siguiente imagen muestra los datos de ejemplo después de haber convertido el rango en una tabla de Excel. Observa que los encabezados de columna tienen un botón de flecha en su extremo derecho que nos permitirá utilizar los filtros.

De manera especial utilizaremos el filtro de la columna Fecha. Al pulsar el botón de flecha en el encabezado de dicha columna, observarás la opción Filtros de fecha que contiene todas las opciones para filtrar los datos de la columna.

Por: C.C.

31

En las siguientes secciones, analizaremos con detenimiento cada una de las opciones disponibles para filtrar los datos por fecha en Excel.

Filtrar datos por fecha específica La primera opción en la sección Filtros de fecha, es la opción Es igual a. Al seleccionar dicha opción se mostrará un cuadro de diálogo como el siguiente:

Por: C.C.

32

En la lista desplegable izquierda estará seleccionada la opción es igual a y en el recuadro derecho podrás ingresar con el teclado la fecha que deseamos encontrar o también podrás elegir alguna de las fechas de la lista desplegable y que son precisamente las fechas que Excel ha encontrado en la columna. Otra opción que tenemos para indicar una fecha al filtro es pulsar el botón de calendario que se encuentra a la derecha del recuadro y que nos mostrará un calendario donde podremos hacer la selección de la fecha.

Una vez que hayas indicado la fecha por la cual deseas filtrar los datos, al pulsar el botón Aceptar se mostrarán aquellas filas que cumplan con dicho criterio.

Por: C.C.

33

Observa que, después de aplicar el filtro, el botón de flecha de la columna Fecha muestra un icono diferente para recordarnos que los datos han sido filtrados. Para remover el filtro deberás hacer clic sobre ese botón y elegir la opción Borrar filtro de “Columna”.

Filtros de fecha antes y después Las siguientes opciones de filtrado que tenemos disponibles son Antes y Después. En caso de elegir la opción Antes, se mostrará el cuadro de diálogo siguiente:

La selección “es anterior a” mostrará solamente los registros que tengan una fecha anterior a la que indiquemos en el filtro. Esta regla es excluyente es decir, la fecha indicada en el recuadro será excluida de los resultados. En la imagen anterior he indicado la fecha 02/01/2016 y por lo tanto el filtro devolverá todas las fechas anteriores. El resultado lo puedes ver en la siguiente imagen:

Por: C.C.

34

Todas las fechas mostradas son anteriores al límite establecido en el filtro. Por el contrario, si elegimos el filtro Después, entonces Excel mostrará los registros posteriores a la fecha indicada, como lo puedes observar en la siguiente imagen.

Observa que en ninguno de los ejemplos anteriores se muestra el registro asociado a la fecha 02/01/2016 ya que ambos filtros excluyen dicha fecha. Al final del artículo aprenderemos un método diferente que nos permitirá incluir la fecha límite dentro de los resultados mostrados.

Por: C.C.

35

Filtrar datos entre dos fechas La opción de filtrado Entre nos permite indicar una fecha mínima y una fecha máxima para obtener todos los registros entre ambos límites. Una vez que seleccionamos la opción Entre, tendremos un cuadro de diálogo como el siguiente:

Ingresaremos la fecha mínima en el recuadro superior y en el recuadro inferior colocaremos la fecha máxima. Excel incluirá ambas fechas en los resultados mostrados ya que la regla de filtrado considera la igualdad: “es posterior o igual a” y “es anterior o igual a”. Al hacer clic en el botón Aceptar obtendré el siguiente resultado:

Observa que las fechas límite que hemos indicado en el filtro son la primera y última fecha del rango mostrado, es decir, esta opción incluye las fechas indicadas en el filtro.

Por: C.C.

36

Los filtros de fecha que hemos revisado hasta ahora (Es igual a, Antes, Después y Entre) son muy útiles y ampliamente utilizados en Excel. Los tipos de filtro que revisaremos a continuación son muy prácticos pero diferentes a los anteriores.

Filtros de fecha instantáneos La mayoría de las opciones mostradas en la sección Filtros de fecha nos permitirán aplicar un filtro con un solo clic y por esa razón yo les llamo filtros de fecha instantáneos. Estas opciones de filtrado no nos pedirán una fecha sino que Excel tomará en cuenta la fecha actual del equipo. Las opciones de filtrado a las que me refiero son las siguientes:

Por: C.C.

37

Puedes aplicar un filtro basado en el día (Mañana, Hoy, Ayer), basado en la semana (Próxima semana, Esta semana, Semana pasada), basado en el mes (Próximo mes, Este mes, Mes pasado), basado en el trimestre (Próximo trimestre, Este trimestre, Trimestre pasado) o basado en el año (Próximo año, Este año, Año pasado). Ya que el funcionamiento de todas las opciones mencionadas anteriormente es evidente, centraré mi atención en las últimas dos opciones delimitadas en la imagen anterior y que son: Hasta la fecha y Todas las fechas en el período. Por: C.C.

38

Al seleccionar la opción Hasta la fecha, Excel mostrará todas las fechas anteriores incluyendo el día de hoy pero solamente del año actual. Esta opción es ampliamente conocida por sus siglas en inglés YTD (Year-to-Date) y es una opción de filtrado muy utilizada en reportes financieros para desplegar la información del año en curso. Por otro lado, la opción Todas las fechas en el período mostrará una serie de opciones adicionales de las cuales podremos elegir alguna.

Por: C.C.

39

Podemos elegir algún trimestre o algún mes pero cualquiera que sea nuestra elección, Excel mostrará los registros de todos los años. Es decir, si elegimos la opción Enero, se mostrarán los registros del mes de enero de cualquier año y sucederá lo mismo al elegir algún trimestre.

Filtro de fecha personalizado La última de las opciones para los Filtros de fecha es el Filtro personalizado que nos mostrará el mismo cuadro de diálogo que revisamos en las primeras opciones de este artículo. Dicho cuadro de diálogo nos permite elegir cualquiera de las opciones de filtrado disponibles.

Anteriormente hicimos un ejemplo con la opción Después la cual excluía la fecha indicada en el filtro (02/01/2016). Si queremos incluir esa fecha en los resultados, es necesario elegir la opción “es posterior o igual a” tal como se muestra en la siguiente imagen:

Como resultado obtendremos aquellas filas con una fecha posterior o igual a la fecha indicada en el filtro.

Por: C.C.

40

Solo debes recordar que las opciones comienza por, no comienza por, termina con, no termina con, contiene, no contiene, son exclusivamente para filtrar cadenas de texto. Puedes crear el filtro utilizando una condición o también puedes utilizar las dos condiciones disponibles y elegir la opción Y para pedir que se cumplan forzosamente ambas o elegir la opción O en caso de requerir que se cumpla alguna de las dos condiciones. Por ejemplo, la siguiente imagen muestra el resultado de aplicar un filtro donde he pedido a Excel que muestre los registros de dos fechas específicas:

Las fechas son un tipo de dato muy común en las hojas de cálculo y es importante conocer las opciones que Excel nos da para filtrar datos por fecha. Pon en práctica los filtros que hemos revisado en esta ocasión y la próxima vez que necesites crear un reporte podrás obtener la información de una manera rápida y precisa.

Por: C.C.

41

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, entonces podemos realizar una agrupación de filas en Excel, lo cual nos permitirá obtener el resultado deseado en nuestro reporte. 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. Para nuestro ejemplo utilizaré una pequeña muestra de datos que contiene la información de ventas de una empresa.

Nuestro primer objetivo será mostrar el total de los artículos vendidos en cada mes así como el monto total de las ventas. Esto quiere decir que, independientemente del método que decidamos utilizar, tendremos que agrupar la información por mes, por lo que el primer paso que debemos dar es asegurarnos que los datos están ordenados por la columna Mes tal como lo ves en la imagen anterior.

Agrupar filas en Excel automáticamente Por: C.C.

42

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. Esto nos indica que Excel agregará una nueva fila con los subtotales cada vez que detecte un cambio en esa columna y además agrupara dichas filas. Para nuestro ejemplo he elegido la columna Mes porque son los datos que deseo agrupar.

Por: C.C.

43

La siguiente lista nos permite elegir la operación que deseamos aplicar sobre las columnas. Las opciones son diversas como la suma, cuenta, promedio, máximo, mínimo, etc. Ya que para nuestro reporte quiero mostrar la suma de artículos y de ventas, entonces selecciono dicha función. La tercera opción nos permite elegir las columnas sobre las cuales se efectuará la operación y de manera predeterminada Excel nos sugerirá alguna columna pero será nuestra responsabilidad asegurarnos que la operación a realizar hace sentido sobre dicha columna. Para nuestro ejemplo Excel sugirió la columna Ventas y manualmente agregué la columna Artículos. La opción Reemplazar subtotales actuales hace sentido cuando ya hemos creado previamente subtotales para los datos. Si seleccionamos esta opción, entonces se insertarán los nuevos resultados sobre la misma línea de subtotales o de lo contrario se creará una nueva línea. La opción Salto de página entre grupos se encargará de insertar un salto de página después de cada cambio en la columna indicada. La verdad es que esta opción casi nunca la utilizo, pero es importante que sepas lo que hace. Finalmente la opción Resumen debajo de los datos se encarga de insertar la fila de subtotales por debajo de cada grupo. Si removemos esta selección, entonces la fila de subtotales estará por arriba de cada grupo. Ahora que tenemos una idea más clara sobre todas las opciones del cuadro de diálogo Subtotales, haré clic en el botón Aceptar y tendremos el siguiente resultado:

Por: C.C.

44

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 [-]. Al pulsar en cualquiera de esos recuadros se colapsarán las filas correspondientes al grupo. Por ejemplo, en la siguiente imagen ya no se muestran las filas con los detalles del mes de Febrero sino solamente la fila del subtotal.

Por: C.C.

45

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. También podrás utilizar los botones Mostrar detalle y Ocultar detalle que se habrán habilitado dentro de la ficha Datos > Esquema.

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. Para nuestro ejemplo, el nivel 3 significa que se mostrarán las filas de datos, los subtotales de cada grupo y el total general. Si hacemos clic en el nivel 2, se mostrarán solamente los subtotales y el total general.

Por: C.C.

46

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 por el contrario, solamente deseas desagrupar las filas, pero que permanezcan los subtotales y el total general, deberás abrir menú desplegable del botón Desagrupar y elegir la opción Borrar esquema.

Por: C.C.

47

Si posteriormente quieres remover fácilmente las filas de subtotales y el total general, deberás ejecutar el comando Subtotales y 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.

Por: C.C.

48

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.

Por: C.C.

49

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. Sin embargo, si comparas el total general de este reporte con el que hicimos en la sección anterior, observarás que tienen el mismo resultado.

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. Por ejemplo, ¿cómo podemos hacer que nuestro reporte muestre la cantidad de vendedores que participaron en cada mes además del total de artículos y ventas?

Por: C.C.

50

Para hacer este tipo de reporte debemos estar dispuestos a tener dos filas de subtotales: una para la función suma y otra fila para la función de cuenta. El reporte lo podemos hacer en dos pasos: primero creamos el mismo reporte del primer ejemplo y en seguida volvemos a ejecutar el comando Subtotales, solo que en esta segunda ocasión nos aseguramos de tres cosas: 1. Elegir la función Cuenta. 2. Agregar el subtotal a la columna Vendedor. 3. 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. Al pulsar el botón Aceptar obtendremos el siguiente resultado.

Por: C.C.

51

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. Este método se refiere a que Excel se podrá encargar de la agrupación, pero nosotros tendremos que agregar manualmente las filas de subtotales. En la siguiente imagen puedes observar los datos originales de nuestro ejemplo y además que he insertado manualmente las filas 8, 14, 22 y 23. En las celdas de cada fila he insertado las fórmulas que necesito, por ejemplo, la celda D8 hace uso de la función SUMA para obtener el total de las filas superiores.

Por: C.C.

52

Por el contrario, la celda B8 utiliza la función CONTARA para hacer la cuenta de los vendedores del mes. Una vez que he creado manualmente cada una de las filas de subtotales y del total general, debo ir a la ficha Datos > Esquema y abrir el menú del comando Agrupar para seleccionar la opción Autoesquema.

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

Por: C.C.

53

Para desagrupar las filas de este reporte podrás ir al comando Desagrupar y seleccionar la opción de menú Borrar esquema. Como puedes observar, este reporte incluye dos operaciones diferentes en una misma línea, algo que no podemos lograr con el comando Subtotales. Este es el único caso donde recomiendo utilizar el procedimiento manual ya que, para la gran mayoría de los casos, el comando Subtotales será suficiente para agrupar y desagrupar filas en Excel.

Por: C.C.

54

¿Qué es una tabla dinámica? Una tabla dinámica es una de las herramientas más poderosas de Excel, pero también es una de las características que más usuarios de Excel se sienten intimidados a utilizar. Si eres uno de ellos te estás perdiendo de utilizar una gran herramienta de Excel. Las tablas dinámicas te permiten resumir y analizar fácilmente grandes cantidades de información con tan sólo arrastrar y soltar las diferentes columnas que formarán el reporte.

Reportes flexibles Es verdad que puedes formar muy buenos reportes con lo que ya sabes de Excel, pero imagina la siguiente situación. Ya has creado un gran reporte que compara el total de ventas en las diferentes regiones del país, pero ahora tus superiores han pedido que hagas otro reporte que compare las ventas de cada región con los tipos de productos vendidos. Y por si fuera poco, después de terminar el segundo reporte, te piden un tercero para comparar las ventas de los productos pero ahora por cada ciudad del país. Es muy probable que tengas que empezar desde cero para crear los nuevos reportes. Afortunadamente Excel tiene la funcionalidad de tablas dinámicas que ayuda a resolver este problema. Al utilizar una tabla dinámica podrás crear los reportes sin escribir una sola fórmula, pero lo más notable será que podrás arreglar el reporte de una manera dinámica de acuerdo a tus necesidades.

El verdadero problema de las tablas dinámicas Muchos usuarios de Excel evitan el uso de las tablas dinámicas porque parecieran muy complicadas a primera vista. Aunque las tablas dinámicas pueden parecer desafiantes, la realidad es que el problema radica en que muy pocas veces se explican adecuadamente. En varias ocasiones he leído libros o tutoriales en Internet que utilizan términos como “Análisis multidimensional”, que aunque es un concepto importante, el presentar este tema a un principiante con este lenguaje solo lo intimidará desde un principio. A partir de hoy iniciaré con una serie de artículos que hablarán sobre tablas dinámicas. Mi objetivo es presentarlas con un lenguaje sencillo de manera que puedas entender fácilmente y pronto puedas utilizar adecuadamente esta característica de Excel que te hará más productivo.

Por: C.C.

55

Funcionamiento de las tablas dinámicas Las tablas dinámicas en Excel permiten agrupar datos en una gran cantidad de maneras diferentes para poder obtener la información que necesitamos. En esta ocasión explicaré el funcionamiento básico de una tabla dinámica. Supongamos que tienes una tabla de datos que contiene mucha información sobre las ventas de la compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.

Por: C.C.

56

Te han solicitado un reporte con el total de ventas por ciudad y el total de ventas por producto. Así que lo que necesitas hacer es sumar las ventas para cada ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:

Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:

Por: C.C.

57

Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.

Finalmente elegimos una columna de valores numéricos que serán calculados y resumidos en la tabla dinámica:

Por: C.C.

58

Así tendrás un reporte que compara las ciudades y los productos y para cada combinación obtendrás el total de ventas. Lo más importante que quiero transmitir con este artículo es que las tablas dinámicas te permiten elegir entre todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un buen análisis de la información.

En la siguiente publicación tomaremos este mismo ejemplo pero ahora haciéndolo con Excel para dejar claro el concepto.

Cómo crear una tabla dinámica Las tablas dinámicas en Excel reciben su nombre por su capacidad de cambiar dinámicamente la información agrupada con tan solo rotar las columnas o filas de la tabla. En esta ocasión veremos un ejemplo claro de cómo crearlas.

Crear una tabla dinámica en Excel Por: C.C.

59

Haz clic sobre cualquier celda de la tabla de datos que se desea considerar en la nueva tabla dinámica.

Ahora selecciona el comando Tabla dinámica que se encuentra dentro del grupo Tablas de la ficha Insertar.

Se mostrará el cuadro de diálogo Crear tabla dinámica. Si es necesario podrás ajustar el rango de datos que se considerará en la tabla dinámica.

Por: C.C.

60

En este mismo cuadro de diálogo se puede elegir si se desea colocar la tabla dinámica en una nueva hoja de Excel o en una ya existente. Haz clic en el botón Aceptar y se creará la nueva tabla dinámica.

Por: C.C.

61

Excel agregará en la parte izquierda del libro la tabla dinámica y en la parte derecha la lista de campos. Esta lista de campos está dividida en dos secciones, primero la lista de todos los campos de los cuales podremos elegir y por debajo una zona a donde arrastraremos los campos que darán forma al reporte ya sea como columna, fila, valor o como un filtro. Para completar la tabla dinámica debemos arrastrar los campos al área correspondiente. Siguiendo el ejemplo propuesto del artículo anterior, colocaré como columna el campo Producto y como fila al campo Ciudad. Finalmente como valores colocaré el campo Ventas.

De manera predeterminada Excel aplica la función SUMA a los valores y la tabla dinámica que resulta después de hacer esta configuración es la siguiente:

Por: C.C.

62

Utilizando una tabla dinámica fue posible crear un reporte de una manera fácil y sin la necesidad de utilizar fórmulas. Pronto veremos cómo se pueden elaborar tablas dinámicas más complejas que permitirán realizar un análisis profundo de la información.

Partes de una tabla dinámica Una vez que has aprendido cómo crear una tabla dinámica en Excel es conveniente detenerse un momento para conocer las partes que la componen y comprender el funcionamiento de cada una de ellas.

Partes de una tabla dinámica en Excel Justo cuando se ha creado una tabla dinámica se muestra en la parte derecha de la hoja la lista de campos disponibles y por debajo las áreas donde podemos arrastrar dichos campos. Estas áreas denotan cada una de las partes de una tabla dinámica.



Filtro de informe. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que ves en

Por: C.C.

63

  

pantalla. Estos filtros son adicionales a los que se pueden hacer entre las columnas y filas especificadas. Etiquetas de columna. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica. Etiquetas de fila. Contiene los campos que determinan las filas de la tabla dinámica. Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y que serán totalizados para cada columna y fila.

Una vez especificados los campos para cada una de las áreas, la tabla dinámica cobra vida. Puedes tener una tabla dinámica funcional con tan solo especificar las columnas, filas y valores. Los filtros son solamente una herramienta para mejorar el análisis sobre los datos de la tabla dinámica.

Dar formato a una tabla dinámica Una vez que has creado una tabla dinámica, Excel permite aplicarle formato fácilmente como si fuera una tabla de datos. La ficha Diseño incluye comandos especiales para aplicar formato a una tabla dinámica. La ficha Diseño es una ficha contextual, por lo que deberás seleccionar la tabla dinámica para que se muestre.

Por: C.C.

64

Esta ficha está dividida en tres grupos. 

Diseño. Este grupo permite agregar subtotales y totales generales a la tabla dinámica así como modificar aspectos básicos de diseño.



Opciones de estilo de tabla dinámica. Las opciones de este grupo permiten restringir la selección de estilos que se muestran en el grupo que se encuentra justo a su derecha. Es decir, si seleccionas la opción “Filas con bandas”, entonces se mostrarán a la derecha los estilos que tienen filas con bandas.



Estilos de tabla dinámica. Muestra la galería de estilos que se pueden aplicar a la tabla dinámica. Con tan sólo hacer clic sobre el estilo deseado se aplicará sobre la tabla.

Puedes hacer clic en el botón Más del grupo Estilos de tabla dinámica para ver todos los estilos disponibles. Con tan sólo elegir alguno de los estilos se aplicará inmediatamente a la tabla dinámica.

Por: C.C.

65

Formato de valores en una tabla dinámica En esta ocasión te mostraré cómo dar formato rápidamente a los valores agrupados de una tabla dinámica de manera de puedan tener el formato de número adecuado. Solamente sigue los siguientes pasos: Supongamos la siguiente tabla dinámica:

Por: C.C.

66

Para dar formato a los valores numéricos debes hacer clic sobre el campo correspondiente dentro del área Valores.

Del menú mostrado debes seleccionar la opción Configuración de campo de valor.

Se mostrará el cuadro de diálogo Configuración de campo de valor.

Por: C.C.

67

Debes hacer clic en el botón Formato de número y se mostrará el cuadro de diálogo Formato de celdas donde podrás seleccionar el formato deseado:

Por: C.C.

68

Después de hacer la selección adecuada acepta los cambios y de inmediato se aplicará el nuevo formato a todos los valores de la tabla dinámica:

Filtrar una tabla dinámica Puedes filtrar y ordenar la información que se encuentra dentro de una tabla dinámica utilizando los filtros que Excel coloca de manera predeterminada en el reporte como Etiquetas de columna y Etiquetas de fila. Al seleccionar cualquier de las opciones del filtro, la información será resumida y solamente mostrará un subconjunto de los datos de la tabla dinámica.

Por: C.C.

69

Una vez que se ha aplicado un filtro, Excel reemplaza el icono predeterminado para indicar que ese campo está siendo actualmente filtrado.

Para mostrar de nuevo todos los valores de los campos filtrados debes hacer clic en el botón de filtrado y seleccionar la opción Borrar filtro de.

De igual manera puedes ordenar instantáneamente los valores de la tabla dinámica. Solamente haz clic sobre el botón de Etiquetas de fila o Etiquetas de columna y elige la opción Ordenar de A a Z o la opción Ordenar de Z a A.

Por: C.C.

70

Segmentación de datos en tablas dinámicas La segmentación de datos en tablas dinámicas es una nueva característica de Excel 2010 que permite hacer un filtrado de los datos dentro de una tabla dinámica. De esta manera puedes filtrar fácilmente la información por más de una columna. En primer lugar debes hacer clic sobre cualquier celda de la tabla dinámica y posteriormente en la ficha Opciones y dentro del grupo Ordenar y filtrar deberás hacer clic sobre el comando Insertar Segmentación de datos.

Excel mostrará el cuadro de diálogo Insertar segmentación de datos.

Por: C.C.

71

En este cuadro deberás seleccionar los campos que deseas utilizar como filtros en la tabla dinámica y Excel colocará un filtro para cada campo seleccionado:

Para filtrar la información de la tabla dinámica es suficiente con hacer clic sobre cualquiera de las opciones del filtro.

Excel ajustará la información de la tabla dinámica de acuerdo a las opciones seleccionadas. Para mostrar de nuevo toda la información puedes hacer clic en el botón Borrar filtro que se encuentra en la esquina superior derecha de cada panel.

Por: C.C.

72

Podrás agregar tantos filtros como campos disponibles tengas en la tabla dinámica, lo cual te permitirá hacer un buen análisis de la información.

Modificar campos de una tabla dinámica Por: C.C.

73

Las tablas dinámicas son muy fáciles de manipular y de modificar. Excel permite cambiar los campos de la tabla de una manera muy sencilla y reestructurar la información con tan solo arrastrar los nuevos campos.

Lista de campos de una tabla dinámica Las modificaciones las haremos en la Lista de campos de la tabla dinámica que se encuentra en la parte derecha de la pantalla. Esta lista se mostrará con tan solo hacer clic en cualquier celda de la tabla dinámica.

Si por alguna razón esta lista de campos no aparece en tu libro de Excel, debes activarla con el comando Lista de campo que se encuentra dentro del grupo Mostrar de la ficha Opciones.

Por: C.C.

74

Quitar un campo de una tabla dinámica Para remover un campo arrástralo fuera del área donde se encuentra y cuando el puntero del ratón se convierta en una “X” suelta el botón del ratón para eliminar el campo. Otra manera de eliminar el campo es haciendo clic sobre él y seleccionar la opción Quitar campo.

Mover un campo de una tabla dinámica Para mover un campo ya existente hacia una nueva ubicación solamente arrástralo de un área a otra o de igual manera selecciona cualquiera de las opciones de menú que permiten mover el campo a cualquiera de las áreas disponibles: Mover al filtro de informe, Mover a rótulos de fila, Mover a rótulos de columna o Mover a valores.

Por: C.C.

75

Con este método puedes cambiar fácilmente las columnas por filas y viceversa.

Nuevo campo en una tabla dinámica Finalmente para agregar un nuevo campo puedes arrastrarlo desde la lista de campos hacia el área deseada. También puedes marcar la caja de selección del campo lo cual hará que sea agregado a alguna de las áreas predeterminadas.

Las tablas dinámicas son un elemento de análisis de información de Excel muy poderoso y esa fortaleza proviene de la facilidad que provee para manipular la información de acuerdo a tus necesidades.

Modificar el tipo de cálculo de una tabla dinámica Por: C.C.

76

De manera predeterminada Excel utiliza la función SUMA al momento de crear los totales y subtotales de los valores de una tabla dinámica sin embargo es probable que necesites utilizar alguna otra función diferente como el promedio o la cuenta de elementos. Para cambiar la función que se utiliza en los valores resumidos debes hacer clic sobre el campo de valor y seleccionar la opción de menú Configuración de campo de valor:

En el cuadro de diálogo mostrado podrás cambiar la función que deseas utilizar:

Los valores disponibles son los siguientes:  

Cuenta. Contabiliza el número de registros. Éstas es la opción predeterminada si el campo es de tipo texto (en lugar de la Suma). Promedio. Obtiene el promedio de los elementos.

Por: C.C.

77

       

Máx. Obtiene el valor numérico más alto. Mín. Obtiene el valor numérico más bajo. Producto. Multiplica todos los valores numéricos de ese campo, si existe algún campo de texto será ignorado. Contar números. Obtiene el número de registros con valor numérico. Desvest. Obtiene la desviación estándar. Desvestp. Obtiene la desviación estándar en función de la población. Var. Calcula la varianza de una muestra. Varp. Calcula la varianza en función de la población.

Al aceptar los cambios Excel aplicará de inmediato el nuevo tipo de cálculo seleccionado.

Crear un gráfico dinámico Un gráfico dinámico es un gráfico que está basado en los datos de una tabla dinámica y que se ajusta automáticamente al aplicar cualquier filtro sobre la información. Con este tipo de gráfico podrás ayudar en la comprensión de los datos de una tabla dinámica.

Cómo crear un gráfico dinámico en Excel Para crear un gráfico dinámico debes hacer clic sobre cualquier celda de la tabla dinámica que servirá como base del gráfico y posteriormente hacer clic sobre el comando Gráfico dinámico que se encuentra dentro del grupo Herramientas de la ficha Opciones.

Se mostrará el cuadro de diálogo Insertar gráfico de donde podrás seleccionar el tipo de gráfico que deseas utilizar.

Por: C.C.

78

Una vez que has seleccionado el gráfico adecuado Excel lo insertará en la hoja de trabajo. Observa cómo el gráfico se modifica al momento de aplicar algún filtro sobre la tabla dinámica:

Por: C.C.

79

De igual manera puedes filtrar la información utilizando los botones de filtrado que aparecen dentro del gráfico dinámico:

Por: C.C.

80

Para eliminar un gráfico dinámico solamente debes seleccionarlo y pulsar la tecla Suprimir.

Mover un gráfico dinámico Es probable que encuentres útil el mover un gráfico dinámico a su propia hoja de Excel para poder trabajar con él de manera independiente e inclusive aplicar algún estilo y formato personalizado.

Mover un gráfico dinámico a otra hoja Para mover un gráfico dinámico debes seleccionarlo y posteriormente seleccionar el comando Mover gráfico que se encuentre en el grupo Ubicación dentro de la ficha Diseño. Por: C.C.

81

Se mostrará el cuadro de diálogo Mover gráfico y podrás seleccionar una nueva hoja donde será colocado.

Modificar el estilo de un gráfico dinámico Ya sea que muevas el gráfico a una nueva hoja de Excel o lo dejes en su ubicación original puedes modificar su estilo con los comandos de la ficha Diseño, Presentación y Formato:

Dentro de la ficha Diseño encontrarás los comandos necesarios para seleccionar un nuevo estilo del gráfico dinámico. Con los comandos de la ficha Presentación podrás personalizar aún más el gráfico dinámico agregando títulos, rótulos de eje y leyendas. Finalmente la ficha Formato permitirá refinar los estilos de forma del gráfico creado.

Por: C.C.

82

Cambiar origen de datos de una tabla dinámica Es probable que el origen de datos de una tabla dinámica haya cambiado de ubicación y ahora tengas que considerar este cambio en tu tabla dinámica. A continuación unos pasos muy sencillos para modificar el origen de los datos. Haz clic sobre la tabla dinámica y selecciona el comando Cambiar origen de datos que se encuentra en la ficha Opciones dentro del grupo Datos.

Se mostrará el cuadro de diálogo Cambiar origen de datos de tabla dinámica el cual te permitirá ampliar (o reducir) o cambiar el rango de los datos de la tabla dinámica.

Acepta los cambios y la tabla dinámica reflejará el nuevo rango de datos que has especificado.

Por: C.C.

83

Dividir texto en columnas Cuando tenemos un texto que deseamos dividir en varias celdas de nuestra hoja, podemos utilizar el Asistente para convertir texto en columnas. Este asistente nos ayudará a colocar cada palabra de nuestro texto en una columna diferente. Las utilidades de este asistente pueden ser muchas, algunos usuarios lo utilizan para separar nombres y apellidos, otros lo usan para preparar los valores que serán exportados a un archivo separado por comas (CSV).

Dividir texto en celdas diferentes Para nuestro primer ejemplo utilizaremos una cadena de texto muy simple: “Dividir texto en celdas diferentes”. Esta cadena de texto se encuentra en la celda A1 y para dividirla debemos seleccionarla y posteriormente pulsar el comando Texto en columnas que se encuentra en la ficha Datos > Herramientas de datos.

Al pulsar el comando Texto en columnas se mostrará el primer paso del asistente el cual nos guiará por todo el proceso. En el paso 1 debemos asegurarnos de seleccionar la opción Delimitados ya que existe un carácter, que es el espacio en blanco, que delimita la separación de cada palabra que deseamos colocar en diferentes columnas.

Por: C.C.

84

Una vez hecha esta selección debemos pulsar el botón Siguiente para ir al paso 2 del proceso. En este paso debemos elegir el carácter por el cual necesitamos hacer la división de la cadena de texto. En nuestro caso, es el espacio en blanco y por lo tanto debemos seleccionar dicha opción en la sección Separadores.

Por: C.C.

85

Al seleccionar la opción Espacio, la vista previa de los datos mostrará la manera en que será dividido el texto de la celda A1. Al pulsar el botón Siguiente iremos al paso final del proceso donde tenemos acceso a algunas configuraciones avanzadas sobre el formato de los datos.

Por: C.C.

86

Por ahora omitiremos dichas opciones y dejaremos la selección en la opción General. Lo único que modificaré será la celda Destino de manera que el texto dividido comience en la celda $B$1. Al pulsar el botón Finalizar, el texto de la celda A1 se dividirá en varias columnas, colocando una sola palabra en cada una de ellas como se observa en la siguiente imagen.

Separar texto en palabras Como has visto, al indicar el espacio como el separador de una cadena de texto, Excel ha colocado todas las palabras en diferentes celdas. El ejercicio anterior lo hemos hecho para una sola cadena de texto, pero puedes hacer lo mismo para varias cadenas de texto a la vez. Para eso deberás asegurarte de seleccionar todo el rango de celdas antes de iniciar el Asistente para convertir texto en columnas. Por ejemplo, en la siguiente imagen puedes ver que tengo varias cadenas de texto en el rango A1:A4 y han sido seleccionadas antes de pulsar el comando Texto en columnas.

Por: C.C.

87

Los pasos del asistente para este ejemplo son exactamente los mismos del ejercicio anterior, así que al terminar todos ellos y pulsar el botón Finalizar tendremos un resultado como el mostrado en la siguiente imagen:

Separar nombres y apellidos en columnas Por: C.C.

88

Al principio de este artículo mencioné que el comando Texto en columnas es utilizado por algunos usuarios de Excel para separar nombres y apellidos, así que quiero terminar con un ejemplo que ilustre este caso. Pero antes de continuar debo decirte que la separación de nombres y apellidos es una tarea que puede llegar a ser sumamente compleja. Si quieres saber a lo que me refiero, puedes consultar el artículo Cómo separar nombres y apellidos en Excel. El comando Texto en columnas no puede detectar la diferencia entre un nombre y un apellido, de hecho ningún comando de Excel puede hacerlo. Así que, para nuestro ejercicio supondremos una columna que contiene los apellidos y nombres separados por una coma tal como lo muestra la imagen siguiente:

Nuestro objetivo es separar todas las cadenas de texto por la coma (,) que en este momento separa los apellidos y los nombres bajo la columna Participantes. El primer paso será seleccionar todo el rango de celdas bajo dicha columna y pulsar el botón Texto en columnas.

Por: C.C.

89

De nueva cuenta seleccionaré la opción Delimitados porque conozco de antemano que la coma es el carácter que delimita las cadenas de texto que necesito separar. En el paso 2 debo asegurarme de elegir la Coma como el separador.

Por: C.C.

90

En el paso final colocaré la celda destino como la celda $B$2 y al pulsar el botón Finalizar obtendré el siguiente resultado:

Por: C.C.

91

Es así como el comando Texto en columnas es de gran utilidad para esas ocasiones en las que necesitas dividir un texto en varias celdas, ya sea que necesitas separar un texto en palabras o que existe un carácter delimitador que indicará las partes que se colocarán en cada columna.

Cómo separar nombres y apellidos en Excel Por: C.C.

92

Aprender a separar nombres de apellidos en Excel es de suma importancia para tener una base de datos confiable ya que dicha información es vital para elaborar reportes y análisis, pero también es cierto que es una tarea muy compleja de realizar.

Dificultad para separar nombres y apellidos La dificultad principal para separar nombres y apellidos en Excel es la cantidad de variantes que pueden existir en los nombres de las personas lo cual hace necesario analizar cada caso específico para utilizar el procedimiento adecuado. La solución no es tan sencilla como decirle a Excel que la primera palabra es el nombre y la segunda palabra es el apellido. La siguiente imagen muestra el resultado de aplicar esta lógica a los datos:

Para ti y para mí es muy fácil saber que en este ejemplo todos los nombres consisten de un solo nombre y un apellido pero ¿Cómo le decimos a Excel que “del Toro” y “de la Fuente” son apellidos? Excel no es capaz de hacer esta distinción por sí solo así que es necesario darle indicaciones específicas.

El caso de separación más sencillo Si después de hacer una revisión de los datos te das cuenta que para la mayoría de los registros tienes nombres de una sola palabra así como apellidos de una sola palabra, entonces te recomiendo utilizar el Asistente para convertir texto en columnas el cual colocará cada palabra en una columna diferente.

Por: C.C.

93

Este asistente se encuentra en la ficha Datos dentro del grupo Herramientas de datos y justamente en el paso 2 del proceso podrás elegir el Espacio como el carácter delimitador para hacer la separación en varias columnas. Al finalizar la ejecución del asistente tendrás un resultado similar al siguiente:

Observa que no importa la cantidad de palabras en el nombre, el asistente colocará cada palabra en una columna diferente. Esta solución es muy buena en caso de que hayas detectado que la mayoría de los nombres y apellidos consisten de una sola palabra ya que solo tendrás que ajustar manualmente aquellos nombres que tuvieran más de tres palabras porque el asistente habrá ocupado columnas adicionales en esos casos. Un beneficio de este método es que no importa si los datos comienzan por los apellidos o por los nombres ya que de igual manera se realizará la separación por palabras. Lo que es muy importante es que todos los registros tengan la misma estructura, es decir,

Por: C.C.

94

que todos comiencen por apellido o que todos comiencen por nombre o de lo contrario tendrás un caso más difícil de resolver.

La función que hemos creado trabaja correctamente sin importar si los nombres comienzan por los apellidos. En la siguiente imagen puedes observar el resultado de ejecutar los mismos pasos anteriores pero con datos que tienen un orden diferente.

La diferencia más importante es que para las primeras dos filas, el nombre se ha colocado bajo la Palabra 2. En este caso será necesario implementar una fórmula de Excel para evaluar si la Palabra 3 está vacía, y en su caso considerar el valor de la Palabra 2 como el nombre en lugar de ser apellido.

Por: C.C.

95

Validación de datos en Excel La validación de datos en Excel es una herramienta que no puede pasar desapercibida por los analistas de datos ya que nos ayudará a evitar la introducción de datos incorrectos en la hoja de cálculo de manera que podamos mantener la integridad de la información en nuestra base de datos.

Importancia de la validación de datos en Excel De manera predeterminada, las celdas de nuestra hoja están listas para recibir cualquier tipo de dato, ya sea un texto, un número, una fecha o una hora. Sin embargo, los cálculos de nuestras fórmulas dependerán de los datos contenidos en las celdas por lo que es importante asegurarnos que el usuario ingrese el tipo de dato correcto. Por ejemplo, en la siguiente imagen puedes observar que la celda C5 muestra un error en el cálculo de la edad ya que el dato de la celda B5 no corresponde a una fecha válida.

Este tipo de error puede ser prevenido si utilizamos la validación de datos en Excel al indicar que la celda B5 solo aceptará fechas válidas. Una vez creada la validación de datos, al momento de intentar ingresar una cadena de texto, obtendremos un mensaje de advertencia como el siguiente:

Por: C.C.

96

Más adelante veremos que es factible personalizar los mensajes enviados al usuario de manera que podamos darle una idea clara del problema, pero este pequeño ejemplo nos muestra la importancia de la validación de datos en Excel al momento de solicitar el ingreso de datos de parte del usuario.

El comando Validación de datos en Excel El comando Validación de datos que utilizaremos a lo largo de este artículo se encuentra en la ficha Datos y dentro del grupo Herramientas de datos.

Al pulsar dicho comando se abrirá el cuadro de diálogo Validación de datos donde, de manera predeterminada, la opción Cualquier valor estará seleccionada, lo cual significa que está permitido ingresar cualquier valor en la celda. Sin embargo, podremos elegir alguno de los criterios de validación disponibles para hacer que la celda solo permita el ingreso de un número entero, un decimal, una lista, una fecha, una hora o una determinada longitud del texto.

Por: C.C.

97

Cómo aplicar la validación de datos Para aplicar la validación de datos sobre una celda específica, deberás asegurarte de seleccionar dicha celda y posteriormente ir al comando Datos > Herramientas de Datos > Validación de datos. Por el contrario, si quieres aplicar el mismo criterio de validación a un rango de celdas, deberás seleccionar dicho rango antes de ejecutar el comando Validación de datos y eso hará que se aplique el mismo criterio para todo el conjunto de celdas. Ya que es común trabajar con una gran cantidad de filas de datos en Excel, puedes seleccionar toda una columna antes de crear el criterio de validación de datos.

Por: C.C.

98

Para seleccionar una columna completa será suficiente con hacer clic sobre el encabezado de la columna. Una vez que hayas hecho esta selección, podrás crear la validación de datos la cual será aplicada sobre todas las celdas de la columna.

La opción Omitir blancos Absolutamente todos los criterios de validación mostrarán una caja de selección con el texto Omitir blancos. Ya que esta opción aparece en todos ellos, es conveniente hacer una breve explicación.

De manera predeterminada, la opción Omitir blancos estará seleccionada para cualquier criterio, lo cual significará que al momento de entrar en el modo de edición de la celda podremos dejarla como una celda en blanco es decir, podremos pulsar la tecla Entrar para dejar la celda en blanco. Sin embargo, si quitamos la selección de la opción Omitir blancos, estaremos obligando al usuario a ingresar un valor válido una vez que entre al modo de edición de la celda. Por: C.C.

99

Podrá pulsar la tecla Esc para evitar el ingreso del dato, pero no podrá pulsar la tecla Entrar para dejar la celda en blanco. La diferencia entre dejar esta opción marcada o desmarcada es muy sutil y casi imperceptible para la mayoría de los usuarios al momento de introducir datos, así que te recomiendo dejarla siempre seleccionada.

Crear validación de datos en Excel Para analizar los criterios de validación de datos en Excel podemos dividirlos en dos grupos basados en sus características similares. El primer grupo está formado por los siguientes criterios:     

Número entero Decimal Fecha Hora Longitud de texto

Estos criterios son muy similares entre ellos porque comparten las mismas opciones para acotar los datos que son las siguientes: Entre, No está entre, Igual a, No igual a, Mayor que, Menor que, Mayor o igual que, Menor o igual que.

Para las opciones “entre” y “no está entre” debemos indicar un valor máximo y un valor mínimo pero para el resto de las opciones indicaremos solamente un valor. Por ejemplo, podemos elegir la validación de números enteros entre los valores 50 y 100 para lo cual debemos configurar del criterio de la siguiente manera:

Por: C.C.

100

Por el contrario, si quisiéramos validar que una celda solamente acepte fechas mayores al 01 de enero del 2015, podemos crear el criterio de validación de la siguiente manera:

Una vez que hayas creado el criterio de validación en base a tus preferencias, será suficiente con pulsar el botón Aceptar para asignar dicho criterio a la celda, o celdas, que hayas seleccionado previamente.

Lista de validación de datos

Por: C.C.

101

A diferencia de los criterios de validación mencionados anteriormente, la Lista es diferente porque no necesita de un valor máximo o mínimo sino que es necesario indicar la lista de valores que deseamos permitir dentro de la celda. Por ejemplo, en la siguiente imagen he creado un criterio de validación basado en una lista que solamente aceptará los valores sábado y domingo.

Puedes colocar tantos valores como sea necesario y deberás separarlos por el carácter de separación de listas configurado en tu equipo. En mi caso, dicho separador es la coma (,) pero es probable que debas hacerlo con el punto y coma (;). Al momento de hacer clic en el botón Aceptar podrás confirmar que la celda mostrará un botón a la derecha donde podrás hacer clic para visualizar la lista de opciones disponibles:

Para que la lista desplegable sea mostrada correctamente en la celda deberás asegurarte que, al momento de configurar el criterio validación de datos, la opción Celda con lista desplegable esté seleccionada. En caso de que los elementos de la lista sean demasiados y no desees introducirlos uno por uno, es posible indicar la referencia al rango de celdas que contiene los datos. Por ejemplo, en la siguiente imagen puedes observar que he introducido los días de la semana en el rango G1:G7 y dicho rango lo he indicado como el origen de la lista. Por: C.C.

102

Lista de validación con datos de otra hoja Muchos usuarios de Excel utilizan la lista de validación con los datos ubicados en otra hoja. En realidad es muy sencillo realizar este tipo de configuración ya que solo debes crear la referencia adecuada a dicho rango. Supongamos que la misma lista de días de la semana la he colocado en una hoja llamada DatosOrigen y los datos se encuentran en el rango G1:G7. Para hacer referencia a dicho rango desde otra hoja, debo utilizar la siguiente referencia: =DatosOrigen!G1:G7

Para crear una lista desplegable con esos datos deberás introducir esta referencia al momento de crear el criterio de validación.

Por: C.C.

103

Si tienes duda sobre cómo crear referencias de este tipo, te recomiendo leer el artículo llamado Hacer referencia a celdas de otras hojas en Excel.

Personalizar el mensaje de error Tal como lo mencioné al inicio del artículo, es posible personalizar el mensaje de error mostrado al usuario después de tener un intento fallido por ingresar algún dato. Para personalizar el mensaje debemos ir a la pestaña Mensaje de error que se encuentra dentro del mismo cuadro de diálogo Validación de datos.

Por: C.C.

104

Para la opción Estilo tenemos tres opciones: Detener, Advertencia e Información. Cada una de estas opciones tendrá dos efectos sobre la venta de error: en primer lugar realizará un cambio en el icono mostrado y en segundo lugar mostrará botones diferentes. La opción Detener mostrará los botones Reintentar, Cancelar y Ayuda. La opción Advertencia mostrará los botones Si, No, Cancelar y Ayuda. La opción Información mostrará los botones Aceptar, Cancelar y Ayuda. La caja de texto Título nos permitirá personalizar el título de la ventana de error que de manera predeterminada se muestra como Microsoft Excel. Y finalmente la caja de texto Mensaje de error nos permitirá introducir el texto que deseamos mostrar dentro de la ventana de error. Por ejemplo, en la siguiente imagen podrás ver que he modificado las opciones predeterminadas de la pestaña Mensaje de error:

Como resultado de esta nueva configuración, obtendremos el siguiente mensaje de error:

Cómo eliminar la validación de datos Por: C.C.

105

Si deseas eliminar el criterio de validación de datos aplicado a una celda o a un rango, deberás seleccionar dichas celdas, abrir el cuadro de diálogo Validación de datos y pulsar el botón Borrar todos.

Al pulsar el botón Aceptar habrás removido cualquier validación de datos aplicada sobre las celdas seleccionadas.

Crear una lista desplegable en Excel Las listas desplegables en Excel nos ayudan a garantizar que los usuarios elegirán un solo valor de la lista en lugar de ingresar sus propios valores. De esta manera, evitamos un problema de consistencia en los datos ya que, si cada usuario ingresa un valor diferente, necesitaremos aplicar procesos de transformación y limpieza de datos los cuales podrían ser altamente complejos.

Qué es una lista desplegable en Excel Las listas desplegables son una característica de la validación de datos en Excel que, entre muchas otras cosas, nos permite convertir una celda de nuestra hoja en una lista desplegable. La siguiente imagen muestra una lista desplegable ubicada en la celda B2.

Por: C.C.

106

Al momento de activar la celda que contiene la lista desplegable, Excel muestra un botón a la derecha de la celda para indicar que dicha celda contiene una lista. Al hacer clic en el botón con flecha, se muestra una pequeña ventana que contendrá todos los valores de la lista y de los cuales se podrá elegir uno solo.

Cómo crear una lista desplegable en Excel Para crear una lista desplegable debemos colocar la lista de elementos dentro de un rango de celdas y posteriormente indicar dicho rango como el origen de datos de la lista. A continuación, enlistaré los pasos exactos que debemos seguir: 1. Colocar la lista de elementos dentro de un rango de celdas. La siguiente imagen muestra los datos en el rango E1:E5.

2. Seleccionar la celda donde necesitamos crear la lista desplegable. En nuestro ejemplo, crearemos la lista en la celda B2.

Por: C.C.

107

3. Hacer clic en Datos > Herramientas de datos > Validación de datos.

4. Se mostrará el cuadro de diálogo Validación de datos y deberás elegir la opción Lista.

5. En el recuadro Origen debemos indicar el rango donde se encuentran los elementos de la lista. Para este ejemplo, los datos están en el rango E1:E5.

Por: C.C.

108

Al hacer clic en el botón Aceptar, Excel habrá creado la lista desplegable. Al momento de seleccionar la celda B2, y hacer clic en el botón de flecha, se mostrará la lista desplegable.

Ahora que hemos creado la lista desplegable en la celda B2, cualquier usuario de la hoja podrá seleccionar alguno de los valores de la lista o podrá dejarla en blanco, pero no podrá ingresar un valor diferente. La siguiente imagen muestra el resultado después de haber ingresado el texto “Domingo” en la celda B2 y pulsar la tecla Entrar. Excel sabe que no podemos ingresar un valor diferente a los elementos de la lista y por lo tanto muestra un mensaje de error.

Por: C.C.

109

De esta manera, la lista desplegable nos ayuda a asegurar que los usuarios de la hoja solamente ingresarán alguno de los elementos de la lista.

Elementos de la lista desplegable Cuando tenemos muchos elementos a incluir en una lista desplegable, Excel mostrará solamente los primeros elementos e incluirá una barra de desplazamiento para que el usuario pueda moverse entre todos los elementos de la lista.

Por: C.C.

110

Esta lista tiene como origen el rango E1:E12 que contiene los meses del año, pero solo se muestran los primeros ocho meses y el resto lo podremos visualizar al movernos hacia abajo en la lista. Este es un comportamiento predeterminado de Excel y no podemos cambiar la cantidad de elementos que se muestran al abrir la lista, pero a través de la barra de desplazamiento podrás ver todos los elementos para seleccionar alguno de ellos.

Ocultar los datos de origen En muchas ocasiones, no queremos que los datos de origen de la lista desplegable estén visibles en la hoja de cálculo, así que los podemos ocultar.

Solo debemos hacer clic derecho sobre el encabezado de la columna que contiene los datos y elegir la opción Ocultar. Es muy importante recordar que, los datos de origen no pueden ser eliminados o de lo contrario la lista desplegable dejará de funcionar.

Copiar la lista desplegable Si copiamos y pegamos una celda que contiene una lista desplegable, entonces la celda destino también tendrá la misma lista desplegable. Excel copiará la regla de validación de datos a la nueva celda, pero cada una será independiente y podrá tener un valor diferente.

Por: C.C.

111

Este comportamiento es muy útil cuando queremos copiar la misma lista desplegable sobre una columna de datos. Con solo copiar la celda donde hemos creado la lista desplegable, podemos pegarla sobre el resto de filas para tener el mismo comportamiento en toda la columna.

Agregar nuevos elementos a la lista Es inevitable que los datos sufran alguna modificación y que necesitemos aumentar o disminuir los elementos de una lista desplegable. El primer paso será agregar o eliminar los datos en la columna que contiene los elementos de la lista. En la siguiente imagen puedes observar que he agregado los días sábado y domingo al final de la columna E.

Por: C.C.

112

Una vez que hemos modificado las celdas que contienen los elementos de la lista, debemos actualizar el origen de la lista con el nuevo rango de celdas. Para hacer este cambio debemos seleccionar la celda que contiene la lista desplegable y pulsar el comando Datos > Validación de datos.

Al hacer clic en el botón Aceptar, la lista desplegable incluirá los nuevos elementos. Lamentablemente no existe un atajo para realizar este tipo de actualización a una lista, sino que es necesario abrir el cuadro de diálogo Validación de datos para indicar el nuevo rango de Origen.

Cómo eliminar la lista desplegable Cuando tenemos una celda con una lista desplegable, la tecla Suprimir eliminará la selección realizada en la lista, pero no desaparecerá la lista de la celda. Si quieres eliminar una lista desplegable, debes eliminar la regla de validación de datos y para eso

Por: C.C.

113

debes ir a Datos > Validación de datos y en las opciones para Permitir deberás elegir la opción Cualquier valor.

También puedes hacer clic en el botón Borrar todos que se encuentra en la esquina inferior izquierda del cuadro de diálogo. Y si lo prefieres, puedes evitar abrir este cuadro de diálogo y eliminar la lista desde la pestaña Inicio > Editar > Borrar > Borrar todo. Con cualquiera de estos métodos se eliminará la lista desplegable de la celda y podrás ingresar cualquier valor en ella.

Función CONTAR.SI.CONJUNTO Nombre en inglés: COUNTIFS

¿Qué hace? Cuenta las celdas de un rango que cumplan con varios criterios especificados.

Sintaxis CONTAR.SI.CONJUNTO(rango_criterios1, criterio1, [rango_criterios2], [criterio1])

Por: C.C.

114

   

rango_criterios1 (obligatorio): El rango al que se le aplicará el primer criterio. criterio1 (obligatorio): El criterio que se aplicará al primer rango para ser considerado en la cuenta. rango_criterios2 (opcional): El segundo rango al que se le aplicará un criterio. criterio2 (opcional): El criterio que se aplicará al segundo rango para ser considerado en la cuenta. Se permiten hasta 127 pares de rango y criterio adicionales.

Ejemplos CONTAR.SI.CONJUNTO(A1:A5, «=5») = Cuenta las celdas que son iguales a 5 en el rango A1:A5 CONTAR.SI.CONJUNTO(A1:A5, «>5», A1:A5, «<10») = Cuenta las celdas en el rango A1:A5 mayores a 5 pero menores a 10

La función CONTAR.SI en Excel 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.

Sintaxis de la función CONTAR.SI Solamente existen dos argumentos para la función CONTAR.SI que son lo siguientes:



Rango (obligatorio): El rango que contiene los valores que se desean contar.

Por: C.C.

115



Criterio (obligatorio): La condición que determinará las celdas que serán contadas.

El criterio puede ser un número o una expresión que indicará la condición a cumplir, por ejemplo: 55, “>26”, “VERDE”. El criterio no hará diferencia entre mayúsculas y minúsculas. La función CONTAR.SI solamente permite especificar una sola condición, en caso de necesitar aplicar varias condiciones se puede utilizar la función CONTAR.SI.CONJUNTO.

Ejemplo de la función CONTAR.SI Tengo una lista de artículos y cada uno tiene asociado su color. Ahora necesito contar el total de artículos que son de color azul y para ello utilizaré la siguiente fórmula: =CONTAR.SI(B2:B11, "azul")

Al aplicar esta fórmula a los datos de la columna B obtengo la cuenta correcta:

Puedes observar que el segundo argumento de la función CONTAR.SI está todo en minúsculas mientras que las celdas de la columna B tienen la primera letra en mayúsculas, sin embargo el valor “Azul” es considerado dentro de la cuenta por lo que comprobamos que la condición no es sensible a mayúsculas y minúsculas. Recuerda que la función CONTAR.SI nos permitirá contar el número de celdas dentro de un rango que cumplan con el criterio que se haya especificado en el segundo argumento de la función.

Por: C.C.

116

La función SUMAR.SI en Excel La función SUMAR.SI en Excel nos permite hacer una suma de celdas que cumplen con un determinado criterio y de esta manera excluir aquellas celdas que no nos interesa incluir en la operación.

Sintaxis de la función SUMAR.SI La función SUMAR.SI tiene tres argumentos que explicaré a continuación.

  

Rango (obligatorio): El rango de celdas que será evaluado. Criterio (obligatorio): La condición que deben cumplir las celdas que serán incluidas en la suma. Rango_suma (opcional): Las celdas que se van a sumar. En caso de que sea omitido se sumaran las celdas especificadas en Rango.

El Criterio de la suma puede estar especificado como número, texto o expresión. Si es un número hará que se sumen solamente las celdas que sean iguales a dicho número. Si el criterio es una expresión podremos especificar alguna condición de mayor o menor que. Si el Criterio es un texto es porque seguramente necesito que se cumpla una condición en cierta columna que contiene datos de tipo texto pero realizar la suma de otra columna que tiene valores numéricos. Todos estos casos quedarán más claros con los siguientes ejemplos.

Ejemplos de la función SUMAR.SI

Por: C.C.

117

El primer ejemplo es muy sencillo ya que de una lista de valores aleatorios quiero sumar todas las celdas que contienen el número 5.

Recuerda que la función SUMAR.SI no realiza una cuenta de las celdas que contienen el número 5, de lo contrario el resultado habría sido 2. La función SUMAR.SI encuentra las celdas que tienen el número 5 y suma su valor. Ya que las celdas A2 y A7 cumplen con la condición establecida se hace la suma de ambas celdas lo cual da el número 10 como resultado. Ahora cambiaré la condición a una expresión y sumare aquellas celdas que sean menores a 3. Observa el resultado de esta nueva fórmula.

Ventas de un vendedor Ahora utilizaremos un criterio en texto y el tercer argumento de la función SUMAR.SI el cual nos deja especificar un rango de suma diferente al rango donde se aplica el Por: C.C.

118

criterio. En el siguiente ejemplo tengo una lista de vendedores y deseo conocer el total de ventas de un vendedor específico. Para obtener el resultado colocaré el rango A2:A10 como el rango que debe ser igual al texto en la celda F1. El tercer argumento de la función contiene el rango C2:C20 el cual tiene los montos que deseo sumar.

La celda F2 que contiene la función SUMAR.SI muestra la suma de las ventas que pertenecen a Juan y excluye el resto de celdas. Podría modificar un poco esta fórmula para obtener las ventas de un mes específico. Observa el resultado de esta adecuación en la celda F5:

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

Por: C.C.

119

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 a los datos de origen. Para utilizar la función BUSCARV debemos cumplir con algunas condiciones en nuestros datos. En primer lugar debemos tener la información organizada de manera vertical, es decir organizada por columnas. Esto es necesario porque la función BUSCARV recorre los datos de manera vertical (por eso la letra “V” en el nombre de la función) hasta encontrar la coincidencia del valor que buscamos. Por ejemplo, los datos de nuestro directorio telefónico deberán estar organizados de la siguiente manera:

Algo que nunca debes olvidar es que la función BUSCARV siempre realizará la búsqueda sobre la primera columna de los datos. En el rango mostrado en la imagen anterior (A2:B11), la función BUSCARV realizará la búsqueda sobre la columna A. Esto quiere decir que para nuestro ejemplo podremos buscar un nombre y obtener el teléfono, pero no podremos buscar un teléfono y obtener el nombre ya que la búsqueda siempre se realiza sobre la primera columna de la izquierda. Por esta razón es Por: C.C.

120

importante preparar los datos adecuadamente para obtener los resultados que necesitamos. Algo que también debemos cuidar con la tabla de búsqueda es que, si existen otras tablas de datos en la misma hoja de Excel debemos dejar al menos una fila y una columna en blanco entre nuestros datos de búsqueda y las otras tablas de manera que la función detecte adecuadamente el rango donde se realizará la búsqueda. Una vez que nuestros datos cumplen con estas condiciones estaremos listos para utilizar la función BUSCARV y realizar búsquedas en Excel.

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:



 

Valor_buscado (obligatorio): Este es el valor que queremos encontrar y el cual será buscado en la primera columna del rango de datos. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. La función BUSCARV no hará diferencia entre mayúsculas y minúsculas. Matriz_buscar_en (obligatorio): El segundo argumento es una referencia al rango de celdas que contiene los datos. Indicador_columnas (obligatorio): El Indicador_columnas es el número de columna que deseamos obtener como resultado. Una vez que la función

Por: C.C.

121



BUSCARV encuentra una coincidencia del Valor_buscado nos devolverá como resultado la columna que indiquemos en este argumento. Ordenado (opcional): Este argumento es un valor lógico, es decir falso o verdadero. Con este argumento indicamos a la función BUSCARV el tipo de búsqueda que realizará y que puede ser una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). Si este argumento se omite se supondrá un valor VERDADERO.

Como puedes ver, la función BUSCARV tiene tres argumentos obligatorios y uno opcional. Sin embargo, te recomiendo siempre utilizar los cuatro argumentos indicando en el último de ellos el valor FALSO para asegurar una búsqueda exacta. Por otra parte, es importante mencionar que no todos los países de habla hispana utilizamos las misma normas en cuanto al carácter separador de listas. Así que, dependiendo de la configuración regional de tu equipo, deberás separar los argumentos de la función BUSCARV por una coma (,) o por un punto y coma (;). Los ejemplos están hechos en una versión de Excel instalada en un equipo con configuración regional Español (México) y por lo tanto verás una coma en la separación de los argumentos de la función BUSCARV pero deberás estar consciente de que existe la posibilidad de que debas utilizar el punto y coma. Como regla general puedo decir que, si en tu país se acostumbra utilizar la coma como el separador de miles, entonces también utilizarás la coma (,) para separar los argumentos de la función BUSCARV. Por el contrario, si en tu país se utiliza el punto como el separador de miles, entonces utilizarás el punto y coma (;) en los argumentos de la función.

La función BUSCARH en Excel La función BUSCARH en Excel busca un valor dentro de una fila y devuelve el valor que ha sido encontrado o un error #N/A en caso de no haberlo encontrado. Esta función es similar, en cierto sentido, a la función BUSCARV.

Cuando utilizar la función BUSCARH Debemos utilizar la función BUSCARH cuando el valor que estamos buscando se encuentra en una fila de alguna tabla de datos. Por el contrario, la función BUSCARV realiza la búsqueda en una columna.

Por: C.C.

122

Sintaxis de la función BUSCARH La función BUSCARH tiene tres argumentos que son los siguientes:

   

Valor_buscado (obligatorio): El valor que estamos buscando. Matriz_buscar_en (obligatorio): El rango que contiene los valores y que debe ser una fila. Indicador_filas (obligatorio): El número de fila que contiene el valor que regresará la función. Ordenado (opcional): El valor debe ser FALSO si queremos una coincidencia exacta o VERDADERO para una coincidencia aproximada.

Si la función BUSCARH no encuentra el valor que está siendo buscado regresará el valor de error #N/A.

Ejemplo de la función BUSCARH

Por: C.C.

123

En la siguiente tabla tengo la información de los artículos que ha vendido cada uno de los vendedores en los últimos meses. Como título de columnas están los nombres de los vendedores y como filas los meses.

Ahora quiero saber los productos vendidos en el mes de Febrero por Paco y para ello puedo utilizar la función BUSCARH de la siguiente manera: =BUSCARH("Paco", B1:D6, 3,FALSO)

El primer argumento es “Paco” porque es el vendedor que estoy buscando. El segundo argumento contiene todo el rango de datos sin incluir la columna de meses (columna A) porque no me interesa dicha información. El tercer argumento es el número de fila que deseo que la función BUSCARH regrese como resultado. Ya que la fila con los nombres es la fila uno, entonces la fila para el mes de febrero es la fila número 3. Finalmente coloca el valor FALSO en el cuarto argumento para indicar que deseo una coincidencia exacta al buscar a Paco. El resultado de esta fórmula es el siguiente:

Si quisiera busca la información del mismo mes para Luis, entonces la función cambiará de argumentos de la siguiente manera: =BUSCARH("Luis", B1:D6, 3,FALSO)

Por: C.C.

124

Si por el contrario quiero conocer los productos vendidos por Hugo en el mes de Abril, entonces la función sería la siguiente: =BUSCARH("Hugo", B1:D6, 5,FALSO)

De esta manera, la función BUSCARH nos permite hacer una búsqueda en una fila (búsqueda horizontal) y encontrar fácilmente el valor requerido.

Por: C.C.

125