• Author / Uploaded
• Merry Helen Gare

Citation preview

Filtrar tabla dinámica según valor de celda Cuando tienes varias tablas dinámicas y necesitas aplicar un filtro a todas ellas al mismo tiempo en base al valor de una celda te das cuenta que no existe una manera fácil de hacerlo en Excel. Hoy analizaremos una alternativa para filtrar una tabla dinámica según el valor de una celda. Antes de continuar, debo aclarar que los filtros de informe son la opción más sencilla para filtrar una tabla dinámica ya que podemos elegir los datos que deseamos visualizar. Sin embargo, hay ocasiones en las que una fórmula de Excel inserta un valor en una celda por el cual queremos filtrar una tabla dinámica o simplemente porque el usuario desea ingresar el texto manualmente. Cualquiera que sea el caso, será necesario utilizar código VBA para filtrar la tabla dinámica en base al valor de dicha celda. Como primer ejemplo utilizaremos una tabla dinámica que muestra la lista de estados con sus respectivos montos de ventas de los últimos dos años. Cada estado pertenece a una región (Norte, Sur, Este, Oeste) que está presente como el filtro de informe de la tabla dinámica, pero no utilizaremos dicho filtro de informe sino que aplicaremos el filtro a la tabla dinámica en base al valor de la celda F1.

Filtrar tabla dinámica según valor de celda

El código que utilizaremos detecta cualquier cambio en la celda F1 y posteriormente se realiza la actualización de los datos de la tabla dinámica. 1 2 Private Sub Worksheet_Change(ByVal Target As Range) 3 4 If Not Intersect(Target, Range("F1")) Is Nothing Then 5 'En base al campo Region de la Tabla dinámica1: 6 With PivotTables("Tabla dinámica1").PivotFields("Region") 7 8 'Limpiar todos los filtros .ClearAllFilters 9 10 'Filtrar por el valor de la celda F1 11 On Error Resume Next 12 .CurrentPage = Range("F1").Value 13 14 End With 15 End If 16 17 End Sub 18 La colección PivotTables nos permite acceder a los campos de una tabla dinámica. En nuestro ejemplo nos interesa el campo Region para el cual se limpia cualquier filtro antes de aplicar uno nuevo basado en el valor de la celda F1. El resultado de esta macro es el siguiente:

Al ingresar el valor Norte en la celda F1 la macro aplicará el filtro a la tabla dinámica y puedes notar que el filtro de informe también cambia automáticamente al valor Norte. En cuanto a los posibles errores que se pudieran presentar, nuestra macro considera aquellos casos en los que ingresamos una Region no válida en la celda F1, o si simplemente dejamos la celda en blanco, ya que con la línea de código On Error Resume Next se captura el error y se limpia el filtro.

Macro para filtrar todas las tablas dinámicas Como segundo ejemplo crearé una tabla dinámica diferente en otra hoja y modificaré la macro anterior para filtrar todas las tablas dinámicas del libro de acuerdo al valor de la celda F1. El código de la macro es el siguiente: 1 2 Private Sub Worksheet_Change(ByVal Target As Range) 3 4 If Not Intersect(Target, Range("F1")) Is Nothing Then 5 6 Dim hoja As Worksheet Dim td As PivotTable 7 8 'Recorrer todas las hojas del libro 9 For Each hoja In ThisWorkbook.Worksheets 10 11 'Recorrer las tablas dinámicas de la hoja 12 For Each td In hoja.PivotTables 13 14 'En base al campo Region de la tabla dinámica: With td.PivotFields("Region") 15 16 'Limpiar todos los filtros 17 .ClearAllFilters 18 19 'Filtrar por el valor de la celda F1 20 On Error Resume Next 21 .CurrentPage = Range("F1").Value 22 End With 23 24 Next td 25 Next 26 End If 27 28 End Sub 29 30 A diferencia del primer ejemplo, en esta macro utilizamos la colección ThisWorkbook.Worksheets para recorrer todas las hojas del libro y de esa manera acceder a todas las tablas dinámicas. Al ingresar un valor en la celda F1 se aplicará el filtro correspondiente a la tabla dinámica de la hoja actual:

Además, si cambiamos a la hoja del primer ejemplo, notarás que también se ha aplicado el mismo filtro a la tabla dinámica:

Observa que el filtro de informe muestra el valor Sur aunque la celda F1 esté vacía, pero eso no importa porque el filtro se ha aplicado considerando la celda F1 de la otra hoja. Lo único que es necesario para que esta macro funcione correctamente es que todas las tablas dinámicas deben incluir el campo por el cual se está filtrando, que en nuestro ejemplo es el campo Region. Las macros que hemos creado te ayudarán a filtrar una tabla dinámica por el valor de una celda e inclusive podrás aplicar dicho filtro a todas las tablas dinámicas de un libro si así lo requieres. Descarga el libro de trabajo utilizado en este artículo y continúa probando los filtros. Por último debo decir que, si en lugar de utilizar el valor de una celda, deseas filtrar varias tablas dinámicas con un solo filtro de informe, te recomiendo leer el artículo Cómo vincular tablas dinámicas por un filtro de informe donde encontrarás otra alternativa de solución.

Punto de equilibrio en Excel El punto de equilibrio de cualquier negocio está determinado por el nivel de ventas necesario para cubrir los costos totales de producción. Es de vital importancia conocer este punto de equilibro ya que nos ayudara a determinar la rentabilidad de un negocio. ¿Qué sucede en el punto de equilibrio? Cuando una empresa está en el punto de equilibrio no se obtienen beneficios, es decir, la empresa no gana dinero pero tampoco pierde dinero sino que solamente se han recuperado los gastos de operación y los costos de fabricación del producto. Si la empresa conoce de antemano este punto de equilibrio, podrá determinar con certeza el nivel de ventas necesario para cubrir todos los gastos y comenzar a obtener ganancias. Por el contrario, si las ventas realizadas no alcanzan el punto de equilibrio, la empresa tendrá pérdidas monetarias. Fórmula del punto de equilibrio La fórmula para calcular el punto de equilibrio es la siguiente:

Los costos fijos son aquellos que debemos cubrir independientemente de que el negocio funcione como lo es la renta de las oficinas, el pago de servicios como la energía eléctrica o telefonía, etc. Este tipo de costos los conocemos como fijos porque no importa si vendemos o no productos, serán gastos que tendremos que cubrir. Los costos variables se refieren principalmente a las materias primas utilizadas en la producción de los artículos que venderemos. Estos costos son variables porque dependen de la cantidad de productos a fabricar ya que entre mayor sea la producción, mayor serán los costos de las materias primas. Para dejar en claro el uso de la fórmula del punto de equilibrio haremos un ejemplo utilizando Excel. Ejemplo de punto de equilibrio en Excel Una compañía fabricante de almohadas ha fijado el precio de cada una en $60. El costo variable por cada almohada es de $20 y los costos fijos de la empresa ascienden a $40,000. ¿Cuántas almohadas deberá vender la empresa para cubrir los costos totales?

Para obtener el punto de equilibrio en Excel aplicamos la fórmula de la siguiente manera:

El resultado es el valor 1000, lo que significa que llegaremos al punto de equilibrio al vender mil almohadas con lo cual habremos cubierto todos los costos. Una manera de comprobarlo es calculando las utilidades y para ello podemos utilizar la siguiente fórmula: =B2*B4 - (B3*B4) - B1

En primer lugar calculamos el monto de las ventas (B2*B4) y restamos el costo variable de las unidades producidas (B3*B4) para finalmente restar los costos fijos (B1). Como es de esperarse, la utilidad en el punto de equilibrio es cero ya que la empresa no obtendrá ganancias ni perdidas:

Ahora que ya tenemos el punto de equilibrio podemos representarlo gráficamente, pero para eso será necesario generar los datos para crear el gráfico de Excel. Gráfico del punto de equilibrio en Excel La tabla que crearemos tendrá 4 columnas que serán: Unidades, Ventas, Costos y Utilidades. La columna Unidades representa la cantidad de unidades producidas y la columna Ventas será dicha cantidad de unidades multiplicada por el precio unitario.

La columna Costos es la suma de los costos fijos más los costos variables correspondientes a las unidades producidas y el cálculo se hace con la siguiente fórmula:

Finalmente las utilidades son la diferencia entre las ventas y los costos. Con nuestros datos listos será suficiente con insertar un gráfico de líneas para obtener el siguiente resultado.

Una vez que hemos creado el gráfico del punto de equilibrio en Excel podemos darnos cuenta fácilmente que justamente en la marca de las 1000 unidades suceden dos cosas importantes: 1. La línea de Ventas y Costos se intersectan y a partir de ese punto las ventas son mayores. 2. La línea de Utilidades cruza el eje horizontal indicando que a partir de las 1000 unidades vendidas comenzaremos a tener una ganancia en el negocio. Es así como hemos aprendido un poco más sobre el punto de equilibrio y la manera en que podemos calcularlo con Excel además de crear un gráfico que nos permita visualizar rápidamente dicho punto. Ahora descarga el libro de trabajo y comienza a calcular el punto de equilibrio para tu negocio de manera que puedas conocer las ventas necesarias para obtener ganancias. Cómo multiplicar horas por dinero en Excel Tal vez te ha sucedido que llega el momento del pago a los empleados de la empresa y tienes lista la información en Excel con las horas trabajadas durante la semana. Ya has calculado el total de horas a pagar y solo hace falta la multiplicación por la tarifa establecida, sin embargo solo obtienes montos incorrectos.

¿Cómo se hace la multiplicación de horas por dinero en Excel? Todo indicaría que es un cálculo muy sencillo pero por más que lo intentas solo consigues datos erróneos. En la siguiente imagen podrás darte cuenta a lo que me refiero.

La celda C9 tiene la fórmula =C7*C8 que es la multiplicación del total de horas trabajadas por la tarifa indicada. El resultado debería ser $4,050 pero en su lugar tenemos solo $168.75 que es un monto incorrecto. Si analizamos las celdas involucradas en el cálculo nos damos cuenta que la tarifa por hora es simplemente el valor numérico 100. Por otro lado la celda C7 hace la suma del rango C2:C6 y la única peculiaridad es que tiene aplicado un formato personalizado para desplegar correctamente la suma de horas y minutos:

¿Cuál de los dos valores es el culpable del error? Definitivamente no es el valor numérico de la tarifa por hora, así que el problema debe ser ocasionado por la suma de horas. Las horas en Excel son decimales El problema con la multiplicación de tiempo por dinero es que las horas en Excel no son en realidad lo que parece. Para darnos cuenta del valor real de la celda C7 basta con aplicar el formato General a dicha celda:

Las 40 horas y 30 minutos que inicialmente desplegaba la celda C7, son en realidad el valor numérico 1.6875 y es la razón por la cual el monto a pagar calculado en la celda C9 nos devuelve como resultado el monto $168.75. Al trabajar con datos de tiempo en Excel vemos desplegadas las horas y minutos tal como los conocemos, pero su valor real es un número decimal entre 0.0, para las 00:00:00 horas, y hasta 0.99999999 que representa las 23:59:59 horas. Eso quiere decir que el valor entero 1 significa un día completo de 24 horas y por tal motivo el valor de la celda C7 significa que tenemos 1 día entero y 0.6875 de otro día. Multiplicar horas por dinero en Excel Para resolver este problema del valor decimal de las horas será suficiente con agregar la multiplicación por 24 de manera que se haga la conversión del valor decimal a la cantidad correcta de horas. Observa cómo al incluir esta multiplicación en la fórmula de la celda C9 obtenemos el resultado correcto:

Ahora ya sabes cómo multiplicar horas por dinero en Excel de manera que puedas calcular y pagar correctamente a cada empleado por las horas trabajadas en la empresa. 

INICIO



FUNCIONES



ACERCA

Calcular horas trabajadas entre dos fechas en Excel Es muy fácil obtener los días laborables en Excel pero en más de una ocasión necesitarás calcular las horas trabajadas entre dos fechas y no existe una función en Excel que pueda hacer eso de manera automática. Seguramente encontrarás muchas alternativas para resolver este problema, pero la alternativa que yo utilizo implica el uso de la función DIAS.LAB que será de gran ayuda en este cálculo. La solución supone que tenemos los siguientes datos en nuestra hoja:

En primer lugar tenemos el Horario laboral que indica la hora de entrada y la hora de salida que tomaremos en cuenta para nuestro cálculo. En segundo lugar tenemos dos celdas que contienen una fecha y una hora indicando la fecha de inicio y la fecha final del cálculo. De esta manera, en nuestro ejemplo, calcularemos las horas trabajadas entre el 08 de noviembre a las 8:15 y el 14 de noviembre a las 18:25 considerando un horario de trabajo de 8:00 a 18:00 horas. Es importante mencionar que al no tener el horario trabajado para los días intermedios, consideraremos un horario de trabajo completo, es decir, desde las 8:00 y hasta las 18:00 horas. Por ejemplo, para el día 09 de noviembre consideraremos 10 horas de trabajo.

Obtener los días laborables El primer paso será obtener los días laborables con un horario completo entre ambas fechas. Este cálculo lo haremos con la función DIAS.LAB de la siguiente manera: =DIAS.LAB(B5, B6) - 2

La función DIAS.LAB calcula los días laborables entre ambas fechas, que en este caso son 5, pero hago una resta de 2 porque deseo excluir los extremos ya que para esos días haré un cálculo especial. Una vez que tengo los días laborables puedo obtener las horas fácilmente haciendo una multiplicación por las horas totales en el horario laboral: =(DIAS.LAB(B5,B6) - 2) * (B3-B2)

Esta fórmula nos da el total de las horas trabajadas en los días laborables que se encuentran entre la fecha de inicio y la fecha final. Observa lo que sucede si aplico esta fórmula en los datos de nuestra hoja:

La celda D8 muestra un total de 30 horas que es el total de horas laborables de 3 días. Es importante mencionar que la celda tiene el formato personalizado [hh]:mm el cual nos permite mostrar el total de horas tal como lo observas en la imagen anterior.

Aún no llegamos al resultado final porque todavía nos falta sumar las horas trabajadas durante el día inicial y también sumar las horas de la fecha final.

Calcular las horas trabajadas al inicio Ya que para el día inicial tenemos la hora exacta en que se comienza a trabajar haremos un cálculo diferente. Para obtener las horas trabajadas en el día inicial utilizaré la siguiente fórmula: =B3 - RESIDUO(B5,1)

Para poder explicar lo que hace la función RESIDUO debemos recordar que las fechas en Excel son números. Al aplicar esta función a la fecha de inicio obtendré solamente las horas de manera que pueda hacer una resta con la celda B3 la cual está indicada también en horas. El resultado de esta fórmula es 09:45 que son las horas trabajadas durante el primer día y es un número que deseo sumar a la fórmula que habíamos comenzado a elaborar: =(DIAS.LAB(B5,B6) - 2) * (B3-B2) + B3 - RESIDUO(B5,1)

Observa el resultado de aplicar esta fórmula a nuestros datos:

Calcular las horas trabajadas al final Por último calcularemos las horas trabajadas en el día final y de igual manera utilizaré la función RESIDUO para obtener las horas: =RESIDUO(B6,1) - B2

Esta fórmula considera los 25 minutos excedentes en el día final por lo que el resultado es de 10:25 horas. Solo resta sumar este resultado a nuestra fórmula principal:

=(DIAS.LAB(B5,B6) - 2) * (B3-B2) + B3 - RESIDUO(B5,1) + RESIDUO(B6,1) B2

Con esta fórmula podemos entonces calcular el total de horas trabajadas entre dos fechas tomando en cuenta las horas de la fecha inicial y final. Observa el resultado:

Tips adicionales En el resultado final he dejado los minutos, pero es posible hacer un redondeo para tener una cantidad exacta de horas. Para tener más información sobre cómo redondear las horas consulta el siguiente artículo: Cómo redondear horas en Excel. La función DIAS.LAB considera, de manera predeterminada, los días sábado y domingo como el descanso semanal pero podríamos utilizar la función DIAS.LAB.INTL en caso de tener algún día de descanso diferente. Además, con cualquiera de las dos funciones puedes tener una lista de días festivos que desees omitir del cálculo. 

INICIO



FUNCIONES



ACERCA

La función DIAS.LAB en Excel La función DIAS.LAB en Excel nos ayuda a obtener el número de días laborables entre dos fechas determinadas. La función DIAS.LAB nos permite especificar un conjunto de días de vacaciones que serán excluidos de los días laborables contabilizados.

Sintaxis de la función DIAS.LAB

A continuación una descripción de los argumentos de la función DIAS.LAB:



Fecha_inicial (obligatorio): Es la fecha a partir de la cual se comenzarán a contar los días laborables.



Fecha_final (obligatorio): La fecha que marca el final de la contabilización de días laborables.



Vacaciones (opcional): Conjunto de una o varias fechas que serán excluidas del calendario de días laborables.

La función DIAS.LAB contabiliza los días laborables de lunes a viernes y excluye los fines de semana (sábado y domingo).

Ejemplos de la función DIAS.LAB En el siguiente ejemplo podrás observar el número de días laborables que existen entre el 1 de enero del 2012 y el 22 de febrero del 2012.

Con este resultado puedes comprobar que se han excluido los sábados y los domingos. Ahora incluiré una lista de días de vacaciones de la siguiente manera:

Observa que he colocado un nombre para el rango de celdas B3:B5 y lo he llamado Vacaciones. En seguida modificaré la función DIAS.LAB para que considere los días de vacaciones.

La función deja de contabilizar las fechas especificadas como vacaciones y lo puedes ver reflejado en el resultado final. Si por alguna razón llegamos a colocar en la lista de días de vacaciones la fecha de un sábado o de un domingo esos días no surtirán ningún efecto porque la función DIAS.LAB no contabiliza esos días. Observa en el siguiente ejemplo cómo he agregado a la lista de días de vacaciones un par de fechas que corresponden a un sábado (18/02/2012) y un domingo (19/02/2012) y sin embargo el resultado de la función DIAS.LAB no cambia respecto al ejemplo anterior:

Observa que para este último ejemplo no utilicé el nombre Vacaciones en el tercer argumento de la función sino que coloqué explícitamente la referencia para el rango B3:B7. Esto nos confirma que podemos utilizar ambos métodos al momento de especificar la lista de días de vacaciones.

La función DIAS.LAB.INTL en Excel La función DIAS.LAB.INTL en Excel fue introducida en la versión 2010 y nos permite contar los días laborables entre dos fechas pero con la ventaja de poder especificar los días de la semana que necesitamos considerar como fines de semana además de los días de vacaciones.

La sintaxis de la función DIAS.LAB.INTL La función DIAS.LAB.INTL tiene cuatro argumentos que nos permiten personalizar adecuadamente la manera en como deseamos contar los días laborables.



Fecha_inicial (obligatorio): Fecha donde se inicia el conteo de los días laborables.



Fecha_final (obligatorio): Fecha final del conteo de días laborables.



Fin_de_semana (opcional): Número que especifica el día o días que serán considerados como el fin de semana.



Días_no_laborables (opcional): Conjunto de una o varias fechas que indican los días no laborables.

Si no especifica el argumento de Fin_de_semana la función DIAS.LAB.INTL hará el cálculo de días laborables suponiendo un fin de semana de sábado y domingo.

Establecer un fin de semana personalizado El tercer argumento de la función DIAS.LAB.INTL nos permite especificar los días de la semana que serán considerados como fines de semana. Por ejemplo, si deseo especificar que el fin de semana está formado sólo por el día domingo debo escribir la función de la siguiente manera: =DIAS.LAB.INTL(B1,B2,11)

El número 11 en el tercer argumento de la función hará que se consideren sólo los domingos como el fin de semana. Observa el resultado de la función recién escrita.

A continuación muestro la lista completa de números válidos para el tercer argumento de la función DIAS.LAB.INTL:

Considerar días no laborables Para agregar una lista de días no laborables a la función DIAS.LAB.INTL podemos crear un rango con la lista de fechas que deseamos que no sean contadas. En el siguiente ejemplo especificaré un fin de semana de viernes y sábado y además agregaré tres fechas como días no laborables en el rango B3:B5:

La función DIAS.LAB.INTL en Excel es una función muy flexible que seguramente se adaptará adecuadamente a la manera en cómo necesites realizar el cálculo de los días laborables. Días laborables con Excel La función DIAS.LAB nos ayuda a obtener el total de días laborables entre dos fechas, sin embargo tiene un inconveniente y es que asume que los días de fin de semana son el sábado y el domingo. ¿Qué hacer si queremos considerar días de fin de semana diferentes? La función DIAS.LAB Primero comprobaré lo que he dicho. Así que utilizaré la función DIAS.LAB para calcular los días laborables del mes de enero del 2011. Observa el siguiente ejemplo.

Efectivamente existen 21 días laborables en el mes de enero considerando los sábados y domingos como fin de semana. Pero si quisiera tomar como días de descanso el viernes y el sábado el número de días laborables del mes sería diferente. Para obtener el resultado deseado utilizaré la función DIAS.LAB.INTL. La función de Excel DIAS.LAB.INTL La función DIAS.LAB.INTL permite especificar los días del fin de semana que deseo utilizar. Observa cómo al introducir la fórmula Excel me permite seleccionar el fin de semana adecuado:

Al elegir los días viernes y sábado como el fin de semana a considerar, el resultado de días laborables para el mes de enero del 2011 cambia a 22 días.

Varios países del medio oriente tienen un fin de semana establecido en los días viernes y sábado, lo cual ha sido una de las razones por las que se ha introducido la función DIAS.LAB.INTL en Excel. Aunque es poco probable que estés leyendo este artículo desde alguno de estos países, es más probable que alguna vez tengas algún trato comercial con alguno de ellos o simplemente la compañía donde laboras desea hacer un cálculo de días laborables utilizando algún día de la semana en especial. Tiempo en Excel En el artículo anterior mencioné cómo las fechas son un número entero con el cual Excel realiza los cálculos correspondientes a fechas. De manera similar, las horas del día tienen un número asignado que permite trabajar con operaciones de tiempo. A diferencia de las fechas donde cada día tiene asignado un número entero, las horas de un día son representadas por un valor decimal que va desde 0, que representa las 0:00:00 horas y hasta 0.99999999 que representa las 23:59:59 horas.

Valor decimal de una hora De igual manera que con una fecha, con tan solo cambiar el formato de la celda que contiene la información podremos conocer el valor decimal asignado a una hora específica.

IMPORTANTE: Si ya has aplicado el SP1 de Office 2010, la función TIEMPO es ahora la función NSHORA y la función VALHORA tiene ahora el nombre HORANUMERO. La función TIEMPO Si por alguna razón tienes los datos de la hora, el minuto y el segundo por separado, puedes utilizar la función TIEMPO para obtener un dato de tipo Hora. Observa el siguiente ejemplo.

Al especificar la hora debes utilizar un número entre 0 y 23, es decir, utilizar el formato de 24-horas. Otro dato interesante sobre esta función es que si especificas un número de minutos o segundos mayor a 59 se hará una conversión automática, es decir, si especifico 90 minutos, Excel lo entenderá como 1 hora y 30 minutos:

La función VALHORA Es posible que en ocasiones intercambies información con terceros y te envíen información sobre la hora del día pero en formato texto. Aun así puedes utilizar la función de Excel VALHORA para convertir dicho texto al tipo de dato Hora.

Si el resultado se muestra como un número decimal recuerda que solamente es cuestión de cambiar el formato de la celda al tipo Hora para que se despliegue adecuadamente. La función AHORA Ya que hemos cubierto el tema tanto de fechas como de horas en Excel puedo explicarte un poco mejor la función AHORA. Esta función obtiene el número de serie de la fecha y hora del reloj interno de tu equipo justo en el instante en que se llama la función. Si aplicamos el formato General a la celda que utiliza la función AHORA podríamos ver el valor numérico que utiliza Excel para describir el instante de tiempo actual.

Puedes observar que la celda tiene la combinación de día y hora por lo que la parte entera del número representa al día, y los decimales representan la hora. Ya que la función AHORA se actualiza cada vez que recalculamos una hoja de Excel, será suficiente con pulsar la tecla F9 para ir cambiando el valor numérico asignado a un instante en el tiempo. Fechas en Excel

Excel almacena una fecha como si fuera un número entero. Al utilizar un sistema como éste, Excel puede sumar, restar y comparar fácilmente las fechas sin necesidad de hacer alguna conversión ni cálculo especial. Por ejemplo, la fecha 1/1/2000 está representada por el número de serie 36526 y la fecha 05/10/2000 tiene asignado el valor 36804. Como puedes imaginar, al realizar una operación con fechas se utiliza su número de serie para facilitar las operaciones. El primero y último día de Excel Excel reconoce todas las fechas posteriores a 1/1/1900. Es por eso que el primer día del año 1900 tiene asignado el número 1. Por el contrario, el último día que Excel reconoce es el 31/12/9999 el cual tiene asignado el número 2958465. Valor numérico de una fecha Es realmente sencillo conocer el valor numérico de una fecha, solamente cambia el formato de la celda que contiene una fecha a un formato General y Excel desplegará su valor numérico.

La función FECHA Finalmente mencionaré la función FECHA la cual nos ayuda a obtener el número de serie que le corresponde a una fecha dados el día, el mes y el año.

Esta función es de gran utilidad para convertir datos a un formato fecha en caso de que tengas el día, mes y año en columnas separadas. Una vez convertidos al tipo de dato adecuado se podrán hacer operaciones con las fechas sin dificultad alguna. Sumar valores entre dos fechas La función SUMAR.SI nos permite sumar valores que cumplen con cierta condición. Si necesitas sumar valores entre dos fechas la condición especificada en la función será de gran relevancia para obtener el resultado deseado. Supongamos la siguiente tabla de datos:

Quiero sumar los valores que están entre las fechas 4 de enero de 2011 y 10 de enero de 2011. Antes de escribir la fórmula explicaré el razonamiento que hay detrás de ella. El resultado final lo obtendré al realizar una resta de dos sumas, es decir, sumaré todos los valores que sean menores o iguales al 10 de enero de 2011 y a ese resultado le restaré la suma de los valores menores al 4 de enero de 2011. ¿Te confundió mi explicación? Observa la siguiente imagen:

La columna A en azul ilustra las filas que se sumarán con la primera fórmula, es decir, aquellos valores que sean menores o iguales al 10 de enero de 2011. La columna B en rojo son las filas que se sumarán en la segunda operación y que se restarán del primer resultado para obtener el total deseado el cual está ilustrado por las filas marcadas en verde en la columna B. La función SUMAR.SI y la función FECHA La primera fórmula es la siguiente: =SUMAR.SI(A1:A14,"18")

El resultado se muestra en la celda F2 de la siguiente imagen:

Cuando necesites contar elementos en Excel utilizando múltiples criterios, la función CONTAR.SI.CONJUNTO será de mucha utilidad. Contar condicional La función CONTAR.SI es una función estadística que nos permite contar las celdas de un rango que cumplen con cierta condición. Esta función utiliza solamente dos argumentos, el primero siendo el rango y el segundo argumento la condición. Detalles de la función CONTAR.SI en Excel La sintaxis de la función CONTAR.SI es la siguiente: CONTAR.SI(rango, criterio)

El argumento rango indica el rango de celdas sobre el que se hará el recuento condicional. El argumento criterio es la condición que deberá cumplir el valor de una celda del rango para ser considerado en la cuenta. El criterio se puede expresar como un número, como una expresión o un texto que indica las celdas que se deberán contar. Un número como criterio Cuando se especifica un número como criterio no se debe encerrar entre comillas. Por ejemplo, en un rango de celdas llamado tabla_datos deseamos contar las celdas que contienen el valor 3 podemos utilizar la función de la siguiente manera:

=CONTAR.SI(tabla_datos, 3)

Una expresión como criterio Si queremos utilizar una expresión como criterio, debemos encerrar la expresión entre comillas dobles. Supongamos que deseamos contar las celdas que tienen un valor mayor a 3 entonces podemos utilizar la función de la siguiente manera: =CONTAR.SI(tabla_datos, ">5")

Un texto como criterio Si deseamos contar las celdas que sean iguales a un texto es suficiente con especificarlo entre comillas dobles. Es importante mencionar que la condición no es sensible a mayúsculas y minúsculas por lo que la condición “verde” será lo mismo que “VERDE”. =CONTAR.SI(tabla_datos, "verde")

Contar celdas únicas Este es un ejemplo sencillo sobre cómo puedes contar celdas que contienen valores únicos dentro de una lista. Es decir, de todos los valores de una lista ¿cuántos son los que aparecen una sola vez? Y para solucionar este problema utilizaré la función CONTAR.SI. Esta función nos permite establecer un rango y una condición para saber si el elemento se debe contar. Pero observa el comportamiento peculiar de esta función al establecer como condición el mismo rango especificado.

Al colocar el mismo rango en ambos parámetros de la función, Excel calcula el número de veces que aparece dicho elemento dentro de la lista. Para nuestro ejemplo los elementos que nos interesan son los marcados como “1” ya que son los que aparecen solamente una vez. Si vuelvo a hacer uso de la función CONTAR.SI sobre el rango de la columna B puedo especificar que se contabilicen solamente aquellos que son igual a 1.

Esta es solo una alternativa para contar celdas únicas en Excel. Contar repeticiones dentro de una lista En ocasiones es de gran ayuda saber el número de veces que un elemento aparece dentro de una lista. Esto se puede lograr de una manera muy sencilla a través de la función CONTAR.SI. Esta función cuenta el número de celdas que cumplen con el criterio especificado. Contar repeticiones en Excel Supongamos la siguiente lista:

Si me interesa saber cuántas veces aparece el día domingo, puedo utilizar la función CONTAR.SI de la siguiente manera: CONTAR.SI(A1:A25, "Domingo")

La función contará el número de veces que se repite el valor y mostrará el resultado. Puedo cambiar el parámetro “Domingo” para colocar una referencia a una celda de manera que pueda dejar la fórmula como: CONTAR.SI($A$1:$A$25,C1)

De esta manera puedo colocar los nombres de la semana en una columna y aplicar fácilmente la fórmula para cada uno de ellos obteniendo el resultado adecuado:

Consulta más información sobre la función CONTAR.SI. Filtrar valores únicos A veces es necesario conocer los valores únicos que existen dentro de una columna de datos. Para lograr este objetivo puedes aplicar un filtro en Excel para ocultar temporalmente los valores duplicadoss. En primer lugar deberás seleccionar el rango de celdas sobre el que deseas aplicar el filtro. Filtrar lista con valores repetidos

Una vez que tienes seleccionado el rango de datos, debes ir a la ficha Datos, dentro del grupo Ordenar y filtrar hacer clic en Avanzadas

Aparecerá el cuadro de diálogo Filtro avanzado, asegúrate de seleccionar la opción Filtrar la lista sin moverla a otro lugar y activa la casilla de verificación Sólo registros únicos y haz clic en Aceptar.

Lista con valores únicos Observarás que la lista de datos se contrae y solamente muestra los valores únicos. Los datos originales no han sido afectados, solamente se ha aplicado un filtro sobre ellos.

Para ver de nuevo los datos originales deberás remover el filtro haciendo clic sobre el botón Borrar de la sección Ordenar y filtrar.

Ya hemos logrado filtrar los datos pero el filtro que aplicamos esconde las celdas de los valores repetidos en los datos originales. ¿Qué pasa si deseas que los registros únicos se copien a otra columna? Si logramos que los valores únicos se muestren en un lugar diferente entonces nuestros datos originales quedarán intactos. Valores únicos en otro lugar Para lograr esto debes hacer una selección diferente en el cuadro de diálogo Filtro Avanzado donde deberás seleccionar la opción Copiar a otro lugar y posteriormente en el cuadro Copiar a deberás escribir la referencia de la celda a donde se copiarán los registros únicos.

Esta opción hará que tengas una copia de los valores únicos de tus datos en otra columna.

Los valores únicos encontrados no son ordenados bajo ningún criterio, solamente son mostrados de acuerdo al orden en que son encontrados por Excel dentro del rango de celdas especificado. Contar valores únicos en Excel En ocasiones necesitamos contar valores únicos en Excel de manera que podamos conocer la cantidad exacta de entradas que no se repiten dentro de un rango. Para resolver este problema haré uso de las fórmulas matriciales. Supongamos que nos ha llegado un archivo de Excel que tiene la lista consolidada de varias personas con su ciudad de origen.

Ahora me han pedido que cuente las diferentes ciudades de la lista, es decir obtener el número de ciudades únicas de la columna B. Para este ejemplo lo podría hacer visualmente, pero si tengo una lista con miles de registros la tarea se puede complicar. Contar valores únicos en un rango Para realizar la cuenta de valores únicos en Excel podemos utilizar la siguiente fórmula matricial: {=SUMA(1/CONTAR.SI(B2:B10, B2:B10))}

Recuerda que para que una fórmula sea matricial debemos pulsar las teclas CTRL + MAYÚS + ENTRAR justo al terminar de introducir la fórmula lo cual hará que Excel coloque los corchetes alrededor de la fórmula. Observa el resultado de aplicar esta fórmula en el ejemplo:

La única desventaja de esta fórmula es que dejará de funcionar adecuadamente si una celda está vacía y obtendremos un error #¡DIV/0! como resultado.

Para resolver este problema podemos utilizar la función SI.ERROR de manera que nuestra fórmula siga funcionando. Esta es la fórmula a utilizar: =SUMA(SI.ERROR(1/CONTAR.SI(B2:B10, B2:B10), 0))

Observa el resultado al utilizar esta fórmula sobre el rango que contiene una celda vacía:

De esta manera hemos eliminado el error #¡DIV/0! y hemos logrado contar valores únicos en Excel aún dentro de un rango con celdas vacías. Evitar duplicados en Excel

En algunos artículos he sugerido métodos para remover duplicados de una lista, sin embargo a veces necesitamos aplicar algunas medidas preventivas para no introducir duplicados al momento de capturar la información. Supongamos que utilizaré la columna A de una hoja de Excel para capturar los códigos de diversos productos. Para lograr que Excel me notifique en el momento en que introduzca un valor duplicado puedo utilizar la funcionalidad de Validación de datos. Evitar duplicados con Validación de datos en Excel Para aplicar dicha validación debo seleccionar primero toda la columna A que será donde se realizará la captura y posteriormente hacer clic en el botón Validación de datos que se encuentra en la ficha Datos dentro del grupo Herramientas de datos.

Se mostrará el cuadro de diálogo Validación de datos y deberás seleccionar la opción Personalizada y colocar la siguiente fórmula: =CONTAR.SI(A:A,A1)=1

La fórmula introducida hará que cada vez que ingresemos un código de producto en la columna A se haga un recuento del número de veces que aparece dicho valor. La condición indica que solamente se permitirán los valores que aparezcan una sola vez así que, si dicho valor aparece por segunda ocasión Excel mostrará un mensaje de advertencia.

Es importante mencionar que este método de validación funciona solamente cuando realizamos la captura manual de los datos, pero si copiamos un valor proveniente de otra celda y lo pegamos Excel no aplicará la validación. Quitar valores duplicados Es muy común tener una lista de valores y querer quitar los valores duplicados. Para ello podemos utlizar el comando Quitar duplicados pero debes recordar que al hacerlo, los valores duplicados de la lista serán eliminados de manera permanente. Es por eso que debes tener cuidado al momento de aplicar este procedimiento. Remover valores duplicados Al momento de remover los valores duplicados sólo se afecta el rango de celdas seleccionado por lo que el primer paso es seleccionar el rango de celdas sobre el cuál se aplicará la acción.

En la ficha Datos, dentro del grupo Herramientas de datos, haz clic sobre el comando Quitar duplicados.

Aparece el cuadro de diálogo Quitar duplicados y en la sección Columnas puedes seleccionar una o más columnas de datos. Para seleccionar todas las columnas rápidamente puedes hacer clic en Seleccionar todo. Para quitar la selección de las columnas haz clic en Anular selección. El comando se ejecutará sobre aquellas columnas que estén

seleccionadas. Asegúrate de marcar la caja de selección Mis datos tienen encabezados en caso de que tus datos los tengan.

Finalmente haz clic en Aceptar y Excel mostrará un mensaje indicando la cantidad de valores duplicados que han sido quitados y la cantidad de valores únicos que permancen.

Finalmente debes hacer clic en el botón Aceptar para terminar con la operación y podrás observar el resultado.

Si lo que deseas es sólamente filtrar los valores sin eliminarlos de la lista, consulta el siguiente artículo Filtrar valores únicos Encontrar mayores de edad En muchos países la mayoría de edad se obtiene al cumplir 18 años. Si tienes un listado con una gran cantidad de datos personales, entre ellos la edad, y necesitas contar las personas mayores de edad puedes utilizar la opción que describo a continuación. Un primer método es ordenar los datos por la columna edad y contabilizar manualmente los registros de las personas que sean mayores a 18 años, pero si la lista es demasiado grande o si el resultado de dicha cuenta será utilizado posteriormente en algún otro cálculo, entonces lo mejor será utilizar una fórmula. Para este ejemplo consideraré la siguiente tabla de datos:

Contar mayores de edad en Excel Para contar los registros que cumplen con la condición de mayoría de edad utilizaré la función CONTAR.SI de la siguiente manera: =CONTAR.SI(D2:D16,">18")

El primer argumento de la función es la matriz que especifica los valores de la columna Edad los cuales serán comparados y contabilizados si cumplen con la condición de mayoría de edad que es el segundo argumento de la función. El resultado es el siguiente:

Utilizando esta misma tabla de datos y la función CONTAR.SI podríamos contabilizar la cantidad de mujeres y hombres. Haciendo uso de la columna C podemos identificar a las mujeres de la siguiente manera: =CONTAR.SI(C2:C16,"F")

Para el caso de los hombres solamente debemos cambiar la condición para que el valor sea igual a “M”.

Función Excel CONTAR.BLANCO Categoría: Estadísticas Nombre en inglés: COUNTBLANK ¿Qué hace? Cuenta las celdas en blanco dentro de un rango. Sintaxis CONTAR.BLANCO(rango) 

rango (obligatorio): El rango de celdas donde se contarán las celdas en blanco.

Ejemplos CONTAR.BLANCO(A1:A20) = Número de celdas en blanco en el rango A1:A20 Función Excel RESTO Categoría: Matemáticas y trigonométricas Nombre en inglés: MOD ¿Qué hace? Obtiene el residuo de la división de dos números. Sintaxis RESTO(número, divisor) 

número (Obligatorio): Número del cual quieres obtener el residuo.



divisor (Obligatorio): Número por el cual se va a dividir.

Ejemplos RESTO(8,5) = 3 RESTO(5,8) = 5 Ventas por representante En el ejemplo del día de hoy tengo una tabla de datos que muestra el número de factura, el representante de ventas y el total de la factura. Necesito saber el total de ventas para cada uno de los representantes. Los datos con los que trabajaremos son los siguientes.

La función SUMAR.SI en Excel Para obtener la suma de las ventas de cada representante utilizaré la función SUMAR.SI con la cual podré sumar todos los registros correspondientes a cada persona. Esta función tiene 3 argumentos, el primer de ellos indica el rango de datos en donde se buscará el valor, el segundo argumento es precisamente el valor a buscar y por último el tercer argumento es el rango de datos que contiene los valores a sumar. Por ejemplo, para sumar las ventas realizadas por Andrés puedo emplear la siguiente fórmula: =SUMAR.SI($B$2:$B$21,"Andrés",$C$2:$C$21)

La función SUMAR.SI buscará todos los registros de la columna B que sean iguales a “Andrés” y sumará el valor que le corresponde de la columna C. Observa el resultado al aplicar la fórmula anterior, pero sustituyendo la cadena de texto “Andrés” por una referencia de celda.

En este ejemplo, para el segundo parámetro de la función, he especificado la dirección de la celda que contiene el valor “Andrés” que es la celda E1. Ya que tenemos referencias absolutas en ambos rangos de la función, solamente debo copiar la fórmula hacia abajo para conocer las ventas de los demás representantes.

La función SUMAR.SI nos permite establecer un criterio para realizar sumas condicionadas, así como en este ejemplo la hemos utilizado para obtener las ventas totales de cada representante de ventas.

Función Excel AHORA Categoría: Fecha y hora Nombre en inglés: NOW

¿Qué hace? Obtiene la fecha y hora actual.

Sintaxis AHORA()

Ejemplos AHORA() = La fecha y hora actual Función Excel TIEMPO Categoría: Fecha y hora Nombre en inglés: TIME ¿Qué hace? Obtiene el número de serie que representa la hora especificada. Sintaxis TIEMPO(hora, minuto, segundo) 

hora (obligatorio): Número que representa la hora.



minuto (obligatorio): Número que representa los minutos.



segundo (obligatorio): Número que representa los segundos.

Ejemplos TIEMPO(7,35,40) = 0.316435185 TIEMPO(21,7,18) = 0.880069444 Función Excel VALHORA Categoría: Fecha y hora Nombre en inglés: TIMEVALUE ¿Qué hace? Convierte un texto en un número de serie para una hora. Sintaxis VALHORA(texto_de_hora) 

texto_de_hora (obligatorio): Cadena de texto que representa una hora.

Ejemplos VALHORA(“7:35:40″) = 0.316435185 VALHORA(“21:07:18″) = 0.880069444 Sumar horas y minutos en Excel

Sumar horas y minutos en Excel no es una tarea simple, es necesario aplicar algunos trucos para poder hacerlo de la manera adecuada. Si eres de las personas que suma primero los minutos y luego los convierte en horas, entonces este consejo será para ti. Sumar horas y minutos en Excel El problema principal se presenta cuando tenemos que sumar varias celdas que contienen horas y minutos ya que por cada 60 minutos debemos agregar 1 a la suma de horas y es entonces cuando la suma de horas y minutos se complica. Supongamos que tenemos un rango de celdas con las horas y minutos que ha trabajado una persona durante una semana en la empresa:

Para poder hacer el pago de honorarios debo realizar la suma correcta del tiempo. A continuación te mostraré el método más sencillo para sumar horas y minutos en Excel. En la celda B7 utilizaré la función SUMA para realizar la suma de todos los elementos. Observa cómo la celda muestra una hora que no corresponde con la suma correcta.

Para desplegar la suma de tiempo correcta debes hacer clic derecho sobre la celda B7 y seleccionar la opción Formato de celdas e ir a la sección Número y posteriormente a la opción Personalizado para finalmente elegir el formato [h]:mm:ss.

Si no encuentras este tipo de formato, entonces debes capturarlo en el cuadro de texto Tipo. Observa que este formato coloca el símbolo para las horas entre corchetes lo cual será importante para mostrar el resultado adecuado de nuestra suma. Al terminar haz clic en Aceptar y observa el resultado.

Con esto hemos aprendido a sumar horas y minutos en Excel sin la necesidad de realizar operaciones adicionales sino solamente modificando el formato de la celda que muestra la

suma de horas y minutos. También puedes aplicar un truco similar al momento de sumar minutos y segundos en Excel. Sumar minutos y segundos ¿Has tenido la necesidad de sumar minutos y segundos en Excel? Esta operación no es muy intuitiva, pero en esta ocasión te mostraré cómo dar formato a este tipo de datos y hacer operaciones básicas con ellos. Para expresar minutos y segundos en una celda, la información debe introducirse utilizando el formato hh:mm:ss. Este formato nos permite mostrar la cantidad exacta de horas, minutos y segundos. Si deseas introducir el valor de 320 minutos y 55 segundos debes hacerlo de la siguiente manera: 0:320:55 Es importante colocar el cero inicial para que Excel interprete los datos de minuto y segundo correctamente. Al introducir esta información en Excel obtenemos el siguiente resultado:

Observa como Excel convierte los números ingresados a un número decimal. Formato de celda para minutos y segundos Para tener la información con el formato que necesitamos debemos hacer clic derecho sobre la celda y seleccionar la opción Formato de celdas y dentro de la sección Personalizada colocar el siguiente valor en el cuadro de texto para Tipo: [mm]:ss

Al aceptar los cambios Excel mostrará el valor de la celda con el formato que necesitamos:

De la misma manera puedes dar formato a una lista que contenga información de minutos y segundos y posteriormente hacer la suma como si fuera cualquier otro tipo de datos.

La ventaja de utilizar este método es que al momento de sumar los segundos, Excel añadirá automáticamente un minuto por cada 60 segundos que se sumen. Esta converisión la tendríamos que hacer manualmente en caso de tener los minutos y los segundos en columnas separadas.

Formato de celdas personalizado A través del formato de celdas personalizado puedes dar la apariencia que necesitas a tus datos numéricos sin la necedidad de editar celda por celda para dar la apariencia que necesitas. Por ejemplo, si en tu hoja de Excel tienes una columna que contendrá un número de folio y necesitas que ese número siempre sea de 5 dígitos, entonces necesitas que Excel convierta el valor “1″ por “00001″, y el valor “2″ por “00002″ y así sucesivamente. En realidad tú no quieres introducir todos los caracteres para cada uno de los folios sino solamente introducir el valor “1″ y que Excel presente el formato adecuado. Eso es posible a través del formato de celdas personalizado. Trabajaremos en ese ejemplo a continuación.

Formato personalizado a una celda Introduce el valor “1″ en la celda “A1″. Haz clic derecho sobre la celda y selecciona la opción Formato de celdas.

Se mostrará el cuadro de diálogo Formato de celdas donde deberás hacer clic en la categoría Personalizada.

Sobre la caja de texto Tipo introduce el valor “00000″.

Al hacer clic sobre Aceptar la celda “A1″ que tenía el valor “1″ se mostrará en pantalla como “00001″.

Si copias este formato de celda a toda tu columna, tendrás el comportamiento deseado en todos tus datos.

Recuerda que puedes copiar in formato de celdas con el botón Copiar formato que se encuentra en la ficha Inicio dentro del grupo Portapapeles.

Códigos de formato personalizado Existen otras opciones de formato personalizado que puedes utilizar: Valor 1 1 11.1 55385040 28/01/2011

Formato personalizado 000 “N”000 000.00 00-00-00-00 dddd-dd-mmmm-aaaa

Presentación 001 N001 011.10 55-38-50-40 viernes-28-enero-2011

Debes considerar que aunque Excel cambia el formato en pantalla de los datos, su valor no es afectado. Así que si tienes el valor 1.2345 y le das formato a la celda para que se vea como 1.23, al momento de hacer cálculos con esa celda Excel tomará el valor 1.2345

Sumar y contar celdas por color de formato condicional Hace algunos meses publique el artículo Operaciones con colores en Excel y de inmediato recibí preguntas sobre la posibilidad de utilizar dichas funciones para evaluar los colores aplicados a una celda a través del formato condicional. Las funciones creadas en ese artículo evalúan la propiedad Interior.Color que tiene cada una de las celdas en Excel, sin embargo, el formato condicional no utiliza dicha propiedad sino que tiene su propia “versión” para almacenar el color de fondo de una celda. Por esa razón no es posible utilizar dichas funciones para evaluar colores establecidos a través de una regla de formato condicional. Así que hoy crearemos una nueva función VBA (UDF) para sumar y contar celdas por color de formato condicional. Pero antes de iniciar con el código hablaremos un poco sobre los objetos y propiedades que debemos evaluar para obtener el color de fondo proveniente de un regla de formato condicional.

La colección FormatConditions Primero debemos recordar que un mismo rango de celdas en Excel puede estar sujeto a varias reglas de formato condicional al mismo tiempo, así que para guardar esa lista de formatos condicionales se creó la colección FormatConditions en VBA la cual enumera todas las reglas de formato condicional aplicadas en un rango. Para comprender el funcionamiento de esta colección haremos un ejemplo. Considera la siguiente lista de números en el rango A1:A10 donde he aplicado una regla de formato condicional que resalta en color rojo los valores mayores a 750.

Ahora insertaré un botón de comando ActiveX en la misma hoja y colocaré el siguiente código VBA en su evento Click: 1 Private Sub CommandButton1_Click() MsgBox Range("A1:A10").FormatConditions.Count 2 End Sub 3 La única línea de código ejecutada se encargará de mostrar el valor de la propiedad Count de la colección FormatConditions la cual contiene el recuento de las reglas de formato condicional para el rango indicado. Al hacer clic sobre el botón de comando se mostrará el siguiente mensaje:

El mensaje nos indica que el rango A1:A10 tiene una sola regla de formato condicional. Ahora crearé una segunda regla para el mismo rango que resaltará de color verde todas las celdas con un valor menor a 250. Una vez creada la regla de formato condicional, volveré a

pulsar el botón de comando y el número mostrado en el mensaje habrá aumentado debido a la nueva regla creada:

Es así como la colección FormatConditions nos permite obtener información sobre las reglas de formato condicional aplicadas a un rango de celdas. Ahora centraremos nuestra atención a una propiedad específica de dicha colección.

La propiedad FormatCondition.Interior.Color Cada regla de formato condicional almacena el estilo que aplicará a las celdas que cumplan con las condiciones establecidas y específicamente el color de relleno se almacena en la propiedad Interior.Color. Para demostrar el valor de esta propiedad agregaré un nuevo botón de comando con el siguiente código: 1Private Sub CommandButton2_Click() For i = 1 To Range("A1:A10").FormatConditions.Count 2 MsgBox "Regla " & i & vbLf & _ 3 "Color: " & 4Range("A1:A10").FormatConditions(i).Interior.Color 5 Next i End Sub 6 El código anterior recorre todos los elementos de la colección FormatConditions y para cada elemento mostrará un mensaje con el número de regla y su color de relleno que está almacenado en la propiedad Interior.Color. Al pulsar el botón obtengo el siguiente resultado:

El color devuelto será un valor entre 0 y 16777215 que corresponde a una de las combinaciones de colores primarios (rojo, verde y azul) que se pueden formar en Excel. Si quieres saber un poco más sobre los colores en Excel consulta el artículo Evaluar el color de fondo de una celda. Los colores mostrados con el código anterior son los colores pertenecientes a cada una de las reglas de formato condicional y no el color de una celda específica. Para conocer el color de formato condicional aplicado a una celda será necesario encontrar la regla que se cumple sobre dicha celda para entonces obtener el color correspondiente.

Macro para obtener el color de una celda El desafío más grande al crear una macro para obtener el color de una celda es descubrir la regla de formato condicional que está activa. Para eso utilizamos un bucle For Next que recorrerá toda la colección de formatos haciendo una evaluación de cada regla para descubrir si está activa. 1 2 3 4 5 6 7 8

Function COLORFC(Celda As Range) As Long 'Indicará si la relga de formato condicional está activa Dim ReglaActiva As Boolean 'Recorrer todas las reglas de formato condicional para la celda indicada For i = 1 To Celda.FormatConditions.Count 'Evaluar la regla FormatConditions(i)

With Celda.FormatConditions(i) 9 1 'Si la regla está basada en el valor de la celda 0 If .Type = xlCellValue Then 11 1 'Identificar el operador de la regla y evaluar si está activa 2 Select Case .Operator Case xlBetween: ReglaActiva = Celda.Value >= 1 3 Evaluate(.Formula1) _ And Celda.Value Evaluate(.Formula1) 1 Case xlLess: ReglaActiva = Celda.Value < Evaluate(.Formula1) 7 Case xlGreaterEqual: ReglaActiva = Celda.Value >= 1 Evaluate(.Formula1) Case xlLessEqual: ReglaActiva = Celda.Value Modificar > Ordenar y filtrar > Filtro. Otra manera de crear un filtro es transformar nuestros datos en una tabla de Excel, lo cual insertará los filtros además de aplicar un formato especial a los datos. Cómo usar los filtros en Excel Para filtrar la información debemos elegir una columna y hacer clic en la flecha de filtro correspondiente para mostrar las opciones de filtrado. Todos los filtros, en la parte inferior, mostrarán una lista de valores únicos con una caja de selección a la izquierda de cada uno.

Una opción que tenemos para filtrar los datos es elegir de manera individual aquellos valores que deseamos visualizar en pantalla. También podemos utilizar la opción (Seleccionar todo) para marcar o desmarcar todos los elementos de la lista. En la imagen anterior he elegido el nombre Hugo de manera que el filtro mostrará solamente las filas con dicho nombre.

Al pulsar el botón Aceptar se ocultarán las filas que no cumplen con el criterio de filtrado establecido. Observa que la flecha de filtro de la columna Vendedor ha cambiado para indicarnos que hemos aplicado un filtro. Además, los números de fila de Excel se muestran en un color diferente indicándonos que existen filas ocultas. Filtrar por varias columnas Si queremos segmentar aún más los datos mostrados en pantalla podemos filtrar por varias columnas. En el ejemplo anterior filtré las filas pertenecientes a Hugo, pero si además necesito saber las que pertenecen a la región Norte y Sur, entonces debo seleccionar dichas opciones dentro del filtro de la columna Región:

Al aceptar estos cambios se mostrarán solamente las filas que cumplen ambos criterios. Observa que ambas columnas habrán cambiado sus iconos para indicarnos que se ha aplicado un filtro en cada una de ellas.

Esto demuestra que es posible crear tantos filtros como columnas tengamos en nuestros datos y entre más criterios de filtrado apliquemos mucha mayor será la segmentación de datos que obtendremos. Cómo quitar un filtro en Excel Para quitar un filtro aplicado a una columna debemos hacer clic en la flecha del filtro y seleccionar la opción Borrar filtro de “Columna” donde Columna es el nombre de la columna que hemos elegido. Esta acción eliminará el filtro de una sola columna, pero si tenemos filtros aplicados a varias columnas y deseamos eliminarlos todos con una sola

acción, entonces debemos pulsar el comando Borrar que se encuentra en la ficha Datos > Ordenar y filtrar.

Filtrar en Excel buscando valores Ya hemos visto que todos los filtros muestran una lista de valores únicos de la cual podemos seleccionar uno o varios de ellos y justo por arriba de dicha lista de valores se muestra un cuadro de texto que nos permite hacer una búsqueda. Por ejemplo, en la siguiente imagen he colocado la palabra “este” en el cuadro de búsqueda y como resultado se ha modificado la lista de valores mostrando solo aquellos donde se ha encontrado dicha palabra:

Cuando tenemos una lista muy grande de valores únicos y no podemos identificar fácilmente aquellos que deseamos seleccionar, podemos utilizar el cuadro de búsqueda

para encontrar los valores que necesitamos. También es posible utilizar caracteres comodines como el asterisco (*) o el símbolo de interrogación (?) tal como si hiciéramos una búsqueda aproximada en Excel de manera que podamos ampliar los resultados de búsqueda. Filtros de texto en Excel Además de las opciones ya mencionadas para filtrar en Excel, cuando en una columna se detecta el tipo de dato texto, se mostrará una opción de menú llamada Filtros de texto como la siguiente:

Al elegir cualquiera de estas opciones se mostrará un cuadro de diálogo que nos permitirá configurar cada uno de los criterios disponibles. Por ejemplo, al elegir la opción Comienza por se mostrará el siguiente cuadro de diálogo:

Si colocamos la letra “a” en el cuadro de texto junto a la opción “comienza por”, entonces Excel mostrará solamente los elementos de la columna Vendedor que comiencen por la letra “a”. Filtros de número en Excel De manera similar, si Excel detecta que una columna contiene valores numéricos, nos permitirá utilizar filtros específicos para dicho tipo de dato tal como lo puedes observar en la siguiente imagen:

A diferencia de los Filtros de texto, Excel nos permitirá utilizar los Filtros de número para mostrar valores que sean mayores o iguales que otro o simplemente aquellos que son superiores al promedio. Filtros de fecha en Excel Las fechas son el tipo de dato que más opciones de filtrado nos proporcionan, tal como lo muestra la siguiente imagen:

Excel nos permitirá filtrar las fechas por días específicos como hoy, mañana o ayer e inclusive por períodos de tiempo más largos como semanas, meses, trimestres o años con tan solo seleccionar la opción adecuada. Filtrar por color en Excel No podíamos pasar por alto y dejar de hablar de la opción de Filtrar por color que nos ofrece Excel. Para que esta opción se habilite es necesario que las celdas tengan aplicado un color de relleno ya sea por una regla de formato condicional o modificando directamente el color de relleno con las herramientas de formato. En nuestro ejemplo he aplicado una regla de formato condicional para aquellas celdas que tengan un valor superior a $850 en la columna Total.

Una vez que las celdas tienen un color de relleno, al hacer clic en el filtro de la columna Total se mostrará habilitada la opción Filtrar por color y dentro de ella podré elegir alguno de los colores presentes en la columna.

Filtro avanzado en Excel Aunque los filtros en Excel nos ofrecen una amplia gama de posibilidades para segmentar la información, es posible realizar un filtrado mucho más avanzado si utilizamos el Filtro avanzado. Si quieres leer un poco más al respecto, te recomiendo leer el siguiente artículo: Filtro avanzado en Excel. Tablas en Excel Las tablas no son más que un conjunto de filas y columnas que contienen datos relacionados y que son manejados de manera independiente por Excel. Las tablas son una herramienta muy poderosa que debes aprender a utilizar adecuadamente. ¿Qué es una tabla en Excel? Las tablas son la manera en que Excel identifica un rango de celdas y de esa manera sabe que la información contenida en ellas está relacionada. La primera fila de una tabla siempre contendrá los encabezados de columna y el resto de las filas contendrán los datos. El utilizar tablas es muy conveniente porque Excel se hará cargo de los detalles del manejo de las tablas, como facilitar el agregar nuevas filas o eliminarlas, lo cual te permitirá enfocarte más en el análisis de los datos. Cómo crear una tabla

Para crear una tabla en Excel todo lo que debes hacer es seleccionar el rango de celdas que contiene los datos y pulsar el botón Tabla que se encuentra dentro de la ficha Insertar.

El uso de las tablas en Excel es altamente recomendable así que a continuación mencionaré 7 beneficios de utilizar las tablas de Excel. 1. Cambiar fácilmente el estilo de una tabla Excel contiene algunos estilos de tabla predefinidos de los cuales podrás elegir alguno para aplicarlo a tu tabla. Solamente selecciona cualquier celda que se encuentra dentro de la tabla y cambia su estilo seleccionado la opción adecuada dentro del grupo Estilos de tabla que se encuentra dentro de la ficha Diseño de las Herramientas de tabla.

Si no te agrada ninguno de los estilos mostrados puedes hacer clic en la opción Nuevo estilo de tabla para crear un nuevo estilo. 2. Resaltar filas alternas de una tabla Cuando creas una nueva tabla, Excel aplicará un fondo especial a las filas impares. Si por alguna razón no deseas que se aplique este formato puedes deshabilitarlo con la opción Filas con bandas dentro de la ficha Herramientas de tabla.

3. Filtrar y ordenar datos de la tabla Cada tabla que se crea en Excel ya trae consigo filtros para cada columna, así como opciones de ordenamiento para cada columna.

4. Referencias estructuradas de una tabla Una de las ventajas más importantes de una tabla de Excel es que, en lugar de utilizar las referencias de celdas como las conocemos, podemos utilizar referencias estructuradas las cuales hacen uso del nombre de la tabla así como de los nombres de las columnas para hacer referencia a los datos.

Al igual que con las referencias tradicionales, las referencias estructuradas se actualizan automáticamente al insertar nuevos datos en la tabla o al removerlos. 5. Crear columnas calculadas fácilmente Una vez que sabes utilizar las referencias estructuras será muy sencillo crear columnas calculadas utilizando los nombres de las columnas. Por ejemplo:

6. Fila de totales de una tabla Se pueden agregar totales a una tabla con tan solo marcar la caja de selección Fila de totales:

Y por si fuera poco, podemos modificar la Fila de totales especificando la operación que deseamos aplicar.

7. Imprimir una tabla Para imprimir una tabla debes seleccionar cualquiera de sus celdas y oprimir la combinación de teclas CTRL + P y posteriormente, en la sección de Configuración, seleccionar la opción Imprimir la tabla seleccionada.

Estas son solo algunas de las características y beneficios de las tablas en Excel que demuestran que son intuitivas, fáciles de usar y que además nos proveen de mucha funcionalidad sin tanta complejidad. La próxima vez que organices tus datos en Excel considera utilizar una tabla.

Utilizando referencias absolutas y relativas En Excel puedes especificar tres tipos de referencias: relativas, absolutas y mixtas. Al utilizar el tipo de referencia adecuado nos aseguramos de que al copiar la fórmula a una nueva ubicación seguirá funcionando adecuadamente. Si tienes una fórmula que hace referencia a otras celdas y copiamos esa fórmula a otra ubicación, Excel actualizará automáticamente las referencias de la fórmula para adecuarlas a la nueva ubicación. Considera el siguiente ejemplo.

La celda C1 hace la suma de A1 y B1. Observa lo que sucede si copiamos el contenido de la celda C1 y lo pegamos en la celda C2.

Excel ha sido tan inteligente que ha supuesto que la fórmula de la celda C2 debe sumar los valores de A2 y B2. Si volvemos a copiar la fórmula y pegarla en la celda C3 obtendremos la fórmula: =A3 + B3.

Referencias relativas Cuando copiamos fórmulas Excel utiliza el tipo de referencia relativa que es el comportamiento predeterminado. Este tipo de referencia se adapta al contexto de la nueva celda cambiando las referencias de acuerdo a la nueva ubicación. Sin embargo, en algunas ocasiones no queremos tener este comportamiento sino que queremos hacer referencia siempre a una celda específica sin importar que copiemos la fórmula a otra ubicación y para ello necesitamos utilizar las referencias absolutas.

Referencias absolutas Para fijar la columna o fila de una referencia utilizamos el signo $ y lo colocamos antes de la letra de la columna o del número de fila de la referencia. Así por ejemplo podemos tener una referencia absoluta que fija tanto la columna como la fila, por ejemplo: $A$1

Referencias mixtas Las referencias mixtas son aquellas donde solamente se fija la columna ($A1) o solamente se fija la fila(A$1).

Atajo para crear referencias absolutas y mixtas Una manera muy sencilla de crear una referencia absoluta es editar la fórmula y posicionar el cursor sobre la referencia relativa que deseamos convertir en referencia absoluta. Posteriormente debemos presionar la tecla F4 lo cual insertará el signo $ tanto para la columna como para el número de fila. Si pulsas dos veces la tecla F4 observarás que Excel solamente inserta el signo $ para la fila y si pulsas la tecla F4 tres veces se creará una referencia mixta con la columna fija. Al

pulsar una cuarta vez la tecla F4 se removerán todos los signos $ y tendremos de nuevo una referencia relativa. A continuación una tabla que te ayudará a recordar este tipo de referencias. Referencia Ejemplo Descripción La columna y la fila pueden cambiar al momento de copiar la Relativa =A1 fórmula. Absoluta =$A$1 Ni la columna ni la fila pueden cambiar. Mixta =$A1 La columna no cambia, solamente la fila puede cambiar. Mixta =A$1 La fila no cambia, solamente la columna puede cambiar. Búsquedas aproximadas en Excel Cuando tenemos grandes cantidades de datos podemos tener dificultades para encontrar la información que necesitamos. El cuadro de diálogo Buscar y remplazar tiene una característica especial que muchos pasan por alto. Palabras diferentes por una sola letra ¿Te ha sucedido que no recuerdas como se escribe el nombre de una persona? Por su puesto que si. Recuerdas muy bien que su apellido es Vásquez ¿o será Vázquez? Cuando tienes duda sobre un carácter específico dentro de una palabra puedes remplazarlo por un caracter comodín de manera que Excel busque tanto Vásquez como Vázquez. Este caracter comodín es el símbolo “?” y para realizar la búsqueda simplemente debes sustituir la letra sobre la cual tienes duda con el símbolo “?” de la siguiente manera:

Esta búsqueda encontrará tanto Vásquez como Vázquez y podrás entonces decidir cuál de los dos resultados es el adecuado.

Palabras diferentes por varias letras Si el problema no es una letra sino que son varias letras entonces tenemos otra opción que es utilizar el símbolo “*”. Con este caracter comodín podemos remplazar varias letras a la vez y de esta manera podríamos buscar todas las palabras que terminan en “ción” de la siguiente manera:

El símbolo “*” le dice a Excel que no importan las letras que sean, ni la cantidad que sean, mientras tanto y sean precedidas por “ción”. El cuadro de diálogo Buscar y remplazar soporta el uso de dos caracteres comodines: 

? remplaza un solo caracter.



* remplaza cualquier número de caracteres.

Los caracteres comodines pueden ser colocados en cualquier posición dentro de una palabra: al inicio, en medio o al final, lo único que harán será sustituir una letra o un grupo de letras. Estos caracteres comodines también funcionan con valores. Por ejemplo, la búsqueda *5 devolverá todas los valores numéricos que terminen en 5. Por el contrario, una búsqueda

como 8?3 encontrará todos los números de tres dígitos que empiecen con 8 y terminen con 3. Buscar un caracter comodín Ahora la pregunta natural es ¿Cómo hago para encontrar una palabra que contiene un carácter comodín? Supongamos que necesito encontrar una celda cuyo valor es *ADA*. Si coloco esta frase en el cuadro de diálogo de búsqueda traerá como resultado todas las palabras que contengan las letras ADA.

Para indicar a Excel que deseo buscar el símbolo “*” y que no lo deseo utilizar como caracter comodín, debo anteponer el símbolo “~” (tilde). De esta manera, para encontrar las celdas que tengan el valor *ADA* debo realizar la búsqueda de la siguiente forma:

De igual manera podemos anteponer el símbolo “~” si queremos buscar un texto que contenga el símbolo “?”. Buscar información El comando Buscar es una herramienta muy poderosa que nos ayuda a encontrar texto y valores dentro de una hoja de Excel. Podemos limitar los resultados obtenidos al especificar criterios adicionales en la búsqueda. Sigue estos pasos para encontrar algún dato específico. Haz clic en la ficha Inicio y pulsa el botón Buscar y seleccionar y elige la opción Buscar.

Búsqueda rápida en Excel Dentro del cuadro de texto Buscar especifica el texto que deseas encontrar y pulsa el botón Buscar siguiente.

De inmediato Excel moverá la hoja a la celda donde ha encontrado el texto especificado. Si por el contrario Excel no encuentra ninguna celda con ese contenido mostrará el mensaje “Microsoft Excel no encuentra los datos de la búsqueda”. Opciones de búsqueda en Excel Si deseas proporcionar criterios de búsqueda adicionales, entonces debes pulsar el botón Opciones y el cuadro de diálogo se expandirá permitiéndote ser más específico en la búsqueda:



Dentro de: Nos permite indicar si la búsqueda será dentro de la hoja o en el libro completo.



Buscar: Podemos elegir una búsqueda por filas o por columnas.



Buscar dentro de: Selecciona si deseas buscar en las fórmulas de cada celda o en sus valores que es lo que vemos desplegado en cada celda. También puedes realizar una búsqueda en los comentarios de las celdas.



Coincidir mayúsculas y minúsculas: Marca esta opción si deseas que la búsqueda concuerde con mayúsculas y minúsculas.



Coincidir con el contenido de toda la celda: Excel encontrará aquellas celdas que coinciden en su totalidad con el texto buscado.

Finalmente pulsa el botón Buscar siguiente para encontrar la siguiente coincidencia o pulsa el botón Buscar todos para obtener una lista completa de los resultados de la búsqueda. Caracteres comodín en Excel Opcionalmente puedes utilizar caracteres comodín en tu búsqueda. Utiliza el símbolo ? (signo de interrogación) para indicar un solo carácter desconocido. Por ejemplo, al especificar el término de búsqueda Gr?cia se encontrarán las palabras Gracia y Grecia.

Otro caracter comodín que podemos utilizar es el símbolo * (asterisco) para indicar varios carateres desconocidos. Por ejemplo, al especificar una búsqueda como ante* se encontrarán todas las palabras que comiencen con “ante” sin importar lo que siga. Esto retornará resultados de búsqueda como anterior, antebrazo, antecedente, anteriormente, antesala, etc. Filtro avanzado En Excel puedes realizar un filtrado de datos totalmente personalizado proporcionando los criterios que deseas aplicar a la información. Este tipo de filtrado es conocido como Filtro avanzado y en esta ocasión te mostraré cómo utilizarlo. Supongamos la siguiente tabla de datos.

Los criterios por los cuales se realizará el filtrado deben especificarse dentro de celdas de la misma hoja. Supongamos que deseo filtrar los registros del departamento de Finanzas.

Para este ejemplo coloqué los criterios por arriba de la tabla de datos aunque realmente su ubicación no es de importancia. Aplicar un filtro avanzado a los datos Antes de aplicar el filtro avanzado debo seleccionar la tabla de datos (A4:D13) y posteriormente ir a la ficha Datos y pulsar el botón Avanzadas que se encuentra en el grupo Ordenar y filtrar. Se mostrará el cuadro de diálogo Filtro avanzado.

Observa cómo en el cuadro de texto para Rango de criterios he seleccionado el rango que contiene las condiciones del filtro avanzado necesarias. Solamente resta pulsar el botón Aceptar para aplicar el filtro.

El resultado es precisamente lo que necesito, así que el filtrado avanzado ha sido exitoso. Pero ahora tengo un desafío mayor ¿Cómo podría hacer para especificar condiciones para una segunda columna? Supongamos que deseo filtrar la información de las personas que tienen apellido Hernández y que además pertenecen al departamento de Finanzas. Para filtrar los datos por estos criterios debo agregar dichas condiciones en otra celda.

Observa cómo el Rango de la lista es el mismo que en el caso anterior ya que son los mismos datos, pero el Rango de criterios ahora abarca también las celdas que contienen el criterio para el Apellido. Al aceptar los cambios Excel aplicará el filtro avanzado adecuadamente.

De la misma manera puedes especificar un criterio para cada columna. Hasta ahora solamente he especificado una sola condición por columna, pero ahora deseo agregar a los resultados del filtro la información del departamento de Informática. Para ello solamente agregaré una fila adicional al rango de criterios de la siguiente manera.

Al aceptar los cambios Excel filtrará la información de los empleados de Finanzas con apellido Hernández y además mostrará la información de los empleados del departamento de Informática sin importar el apellido que tengan.

Como podrás observar, es factible especificar una condición por cada fila del rango de criterios. De esta manera puedes crear un filtro avanzado en Excel. Filtrar registros de una tabla Existe una característica en Excel conocida como Autofiltrar que permite esconder los regitros de una tabla de Excel excepto aquellos que deseamos ver. Al filtrar los registros

de una tabla sólo estamos mostrando un subconjunto de los datos y de esa manera tenemos una mejor visibildiad de los mismos para realizar un buen análisis. Filtrar datos en Excel El filtrado no re-ordena los datos, simplemente oculta de manera temporal los registros que no cumplen con los criterios especificados. Para aplicar un filtro sobre los datos de una tabla siguie los siguientes pasos. Haz clic dentro de la tabla y selecciona el comando Ordernar y Filtrar que se encuentra en el grupo Modificar de la ficha Inicio y dentro de las opciones mostradas selecciona Filtro:

Inmediatamente aparecen flechas de filtrado al lado de los encabezados de cada columna. Si ya habías dado formato a los datos como tabla previamente, entonces estas flechas de filtrado ya estaban presentes. Ahora haz clic sobre la flecha de filtrado de la columna que deseas filtrar y Excel desplegará una lista que incluye cada uno de los valores únicos de la columna.

Para aplicar el filtro en los datos remueve la marca de selección de la opción (Seleccionar todo). Esto limpirá todas las cajas de selección permitiéndote seleccionar sólamente las opciones que estás interesado ver:

Filtrar por varios criterios o columnas Puedes seleccionar múltiples cajas de selección para filtrar por varios criterios. Al hacer clic en el botón Aceptar Excel desplegará sólamente los datos que cumplen son la selección realizada. También puedes aplicar filtros en varias columnas a la vez, solamente repite los pasos que acabamos de revisar para cualquier otra columna.

Podrás notar que una columna tiene un filtro aplicado porque el ícono de flecha de filtrado es reemplazado por un ícono de filtro.

Remover un filtro Para remover un filtro puedes hacer clic sobre el ícno de filtro de la columna deseada y seleccionar la opción Borrar filtro

Si has aplicado filtros a varias columnas y deseas quitar todos los filtros al mismo tiempo puedes hacer clic en el comando Ordernar y filtrar y seleccionar la opción Borrar

Filtrar números en Excel Excel provee múltiples opciones para filtrar datos numéricos como filtrar los valores superiores o inferiores, filtrar los valores que son mayores o menores a un número específico, etc. Ya hemos visto cómo filtrar registros de una tabla y cómo Excel coloca automáticamente flechas de filtrado para cada columna. Filtros de número en Excel Pero existe una opción de menú especial para las columnas que son numéricas y que revisaremos a continuación. Haz clic sobre el icono de filtrado de una columna de datos

numéricos y selecciona la opción de menú Filtros de número. Observa las opciones disponibles:

Primero probaremos la opción Diez mejores. Esta opción muestra el cuadro de diálogo Autofiltro de las diez mejores

En la primera lista de selección podrás decidir si deseas obtener los valores superiores o inferiores. En la segunda opción puedes especificar el número de elementos que deseas observar (desde 1 hasta 500). En la última lista de selección eliges si deseas filtrar los elementos o filtrar por su porcentaje. Un ejemplo de porcentaje podría ser obtener los 10 clientes superiores por el porcentaje de ventas. Una vez que has especificado los valores adecuados haz clic en Aceptar y Excel desplegará los registros que cumplen con el criterio especificado. Otra de las opciones de Filtros de número es filtrar por Superior del promedio o Inferior del promedio. Estas opciones son directas y en cuanto las selecciones Excel aplicará el

filtro automáticamente. Por último podrás observar que el resto de los filtros posibles se puede realizar a través del cuadro de diálogo Autofiltro personalizado

A través de este cuadro de diálogo podrás hacer comparaciones como: Igual a, mayor que, menor que, entre, etc. Sólamente debes especificar la condición que necesitas y podrás filtrar números en Excel. Cómo restar en Excel Cuando comenzamos a utilizar las hojas de cálculo es normal preguntarnos cómo realizar las operaciones aritméticas más elementales como sumar y multiplicar, pero estoy seguro que cuando eras principiante en Excel te preguntaste en más de una ocasión cómo restar en Excel. ¿La función RESTA en Excel? Una de las primeras funciones que aprendemos a utilizar en Excel es la función SUMA, así que es prácticamente inevitable razonar que: Si existe una función para sumar, seguramente debe existir una función para restar. Sin embargo, después de hacer una búsqueda exhaustiva, todo usuario que comienza en Excel se entera de que la función RESTA no existe. A muchos les parece extraño que no exista una función para restar en Excel, pero todo comienza a hacer más sentido cuando nos damos cuenta de que podemos restar números utilizando la función SUMA. Lo único que debemos hacer es utilizar valores negativos y dejar que la función se encargue de realizar la operación aritmética. Considera como ejemplo la siguiente fórmula: =SUMA(10, -3)

En esta fórmula he colocado el valor -3 como segundo argumento de la función SUMA y por lo tanto al realizar el cálculo obtendré el resultado de la operación diez menos tres:

Ya que la función SUMA nos permite indicar valores numéricos o referencias, entonces también es posible restar el valor de varias celdas de la siguiente manera:

Observa que he especificado valores negativos en las celdas B1 y C1 y por lo tanto la función SUMA en realidad estará haciendo una resta de dichos números. De esta manera podemos concluir que al no existir la función RESTA en Excel, podemos utilizar la función SUMA siempre y cuando indiquemos los valores negativos. El operador aritmético para la resta Una segunda alternativa que tenemos para restar en Excel es utilizar directamente el operador aritmético para la resta que es el guion medio (-). Este operador lo podemos utilizar ya sea con valores numéricos o con referencias de celdas. Observa cómo resto el valor de las celdas B1 y C1 al valor de la celda A1 utilizando este operador:

A diferencia del ejemplo anterior que utilizaba la función SUMA, en esta ocasión los valores de todas las celdas son positivos y el operador se encarga de hacer las restas correspondientes. Son los operadores los que nos permiten utilizar Excel como si fuera una calculadora, por ejemplo, podemos escribir fórmulas como la siguiente para mezclar operaciones de suma y resta: =A1+B1-C1-D1

También es posible mezclar el operador de resta con la función SUMA para restar un conjunto de números a otro. En la siguiente imagen hago la suma de todos los costos de una empresa y el resultado lo resto a los ingresos para obtener la utilidad bruta.

Si quieres saber un poco más sobre los operadores aritméticos en Excel consulta el artículo: Tipos de operadores en Excel. Restar con la función IM.SUSTR La función IM.SUSTR está presente desde la versión de Excel 2007 y es utilizada para restar dos números complejos, pero la verdad es que son muy pocas las personas que hacen operaciones con este tipo de números. Lo único que debemos saber es que un número complejo está formado por un número real y un número imaginario. Los números que hemos utilizado hasta ahora en este artículo son reales, así que esta función puede ayudarnos perfectamente a restar en Excel, pero debemos recordar que solo nos permitirá restar dos números a la vez. Observa la siguiente imagen donde hago uso de esta función:

La celda C1 muestra correctamente el resultado de la operación 10 menos 3. La única peculiaridad del resultado es que el número 7 está alineado a la izquierda, lo cual nos indica que Excel lo ha identificado como una cadena de texto. Esto se debe a que los números complejos son representados en Excel como cadenas de texto, así que debes tener eso en cuenta si alguna vez decides utilizar la función IM.SUSTR para hacer una resta en Excel. Ahora ya conoces los diferentes métodos que tenemos para restar en Excel. El más utilizado de todos es el operador de resta y probablemente en alguna ocasión te encontrarás a la función SUMA haciendo cálculos con números negativos pero lo que es menos usual, pero posible, es el uso de la función IM.SUSTR para hacer restas en Excel. Función Excel SUMA

Categoría: Matemáticas y trigonométricas Nombre en inglés: SUM ¿Qué hace? Devuelve el subtotal de una lista de valores aplicando la operación indicada. Sintaxis SUMA(número1, [número2], …) 

número1 (Obligatorio): Primer número (o rango de celdas) a considerar en la suma.



número2 (Opcional): Números (o rangos de celdas) adicionales a sumar. Se pueden especificar hasta 255 números adicionales.

Ejemplos SUMA(1,3,4) = 8 SUMA(A1:A5) = Suma de los valores en el rango A1:A5 Sumar celdas de distintas hojas Para sumar celdas de distintas hojas en Excel debemos poner especial atención a las referencias de las celdas que vamos a utilizar de manera que la función SUMA devuelva los resultados adecuados. Referencias a celdas de otras hojas En primer lugar debemos recordar que para crear una referencia a una celda de otra hoja debemos hacerlo colocando el nombre de la hoja seguido de un signo de exclamación (!) y posteriormente la dirección de la celda. Por ejemplo: Hoja2!A1 Sumar celdas de diferentes hojas Si quiero sumar todas las celdas A1 de la Hoja1, Hoja2 y Hoja3 puedo tener una fórmula como la siguiente:

Con esta fórmula hemos sumado los valores de todas las celdas A1 de la Hoja1 a la Hoja3. De esta manera podemos especificar la dirección exacta de todas las celdas que deseamos sumar.

Sumar la misma celda en distintas hojas En el ejemplo anterior sumamos la celda A1 de todas las hojas y especificamos las tres direcciones de la celda A1. Sin embargo existe un método abreviado para estos casos. Cuando necesitamos sumar la misma celda en varias hojas podemos utilizar el siguiente método abreviado en la fórmula:

En este caso los dos puntos (:) separan los nombre de la primera hoja y de la última hoja que vamos a incluir seguidos del signo de exclamación (!) y la dirección de la celda que se sumará en cada una de las hojas. Este método es muy útil cuando tienes que sumar la misma celda de muchas hojas porque evita introducir la dirección de cada una de las celdas. Por ejemplo, si deseamos sumar la celda B5 de las Hoja1 hasta la Hoja15 la fórmula será: SUMA(Hoja1:Hoja15!B5) Sumar un rango en distintas hojas Así como podemos sumar una misma celda en diferentes hojas, también podemos sumar un rango en distintas hojas. La nomenclatura es similar.

Esta fórmula regresará la suma del rango A1:A3 en todas las hojas desde la Hoja1 hasta la Hoja3. Las técnicas que hemos revisado pueden ser aplicadas a otras funciones, así que puedes hacer esta misma prueba con otras funciones como PROMEDIO, MAX, MIN. Referencias a otras celdas En esta ocasión mostraré cómo hacer una referencia a otra celda al escribir una fórmula. La referencia puede ser a una celda dentro de la misma hoja o en otra hoja del mismo libro, e incluso una referencia a una celda de otro libro. Beneficios de las referencias

Al utilizar referencias dentro de una fórmula ganas dinamismo y flexibilidad ya que los valores utilizados dentro de la fórmula están almacenados dentro de alguna celda. Si se cambia el valor constante de la celda, entonces el resultado de la fórmula asociada se actualiza al mismo tiempo. Referencias a celdas en la misma hoja Este es el tipo de referencia más conocido y utilizado. Para crear una referencia a una celda en la misma hoja es suficiente con especificar su dirección. Observa el siguiente ejemplo de la celda A1 haciendo referencia a la suma de las celdas B1 y C1.

Referencias a celdas en otra hoja Si los valores que deseas utilizar se encuentran en una hoja diferente a la actual entonces la referencia a esa celda debe ser antecedida por el nombre de la hoja de la siguiente manera:

Primero se coloca el nombre de la hoja seguido por el signo de exclamación (!) y posteriormente la dirección de la celda. En caso de que el nombre de la hoja contenga espacios, entonces deberá de encerrarse entre comillas simples antes del signo de exclamación, por ejemplo: ='Reporte de ventas'!A1

Referencias a celdas en otro libro También puedes hacer referencia a una celda que se encuentre en otro libro. Para ello, se deberá insertar el nombre del libro antes del nombre de la hoja y deberá estar encerrado por corchetes. Ambos deberán estar encerrados por comillas simples: ='[Libro2.xlsx]Hoja1'!A1

El libro referenciado debe estar previamente guardado en disco duro y en la misma carpeta que el archivo con el que se trabaja, de lo contrario se debe especificar la ruta completa del libro que se desea utilizar tal como se observa en la imagen de ejemplo. Calcular las ventas acumuladas Las ventas acumuladas del año no son más que la suma de las ventas de los meses anteriores hasta la fecha actual, es decir, una suma acumulada. Un concepto básico y muy fácil de implementar en Excel. Sumas acumuladas en Excel Supongamos los siguientes datos de ventas mensuales:

Insertaré una nueva columna y haré uso de la función SUMA para crear la siguiente fórmula:

Observa detenidamente el rango de la función SUMA, porque lo estoy escribiendo de manera que al copiar la fórmula hacia abajo obtenga la suma acumulada automáticamente. En primer lugar estoy indicando a la función que sume un rango que siempre comenzará en la celda B2 y terminará en la celda actual. Es por eso que he colocado el símbolo “$” junto al número 2 para fijar la fila y que no se auto incremente aun cuando copie la fórmula hacia abajo. Para el caso específico de la celda C2 el rango inicia y termina en la misma celda, pero al copiar la fórmula hacia abajo obtendremos el resultado esperado:

La celda C13 muestra claramente cómo la fórmula mantuvo la referencia a la celda inicial B$2 y realizó la suma hasta la fila correspondiente, que en este caso es la fila 13. Los datos

están correctos por ejemplo, para el mes de febrero la columna Acumulado muestra la suma de las ventas de enero y febrero. Para el mes de marzo muestra la suma de las ventas de los primeros 3 meses del año. Es de esperarse que la última celda de la columna Acumulado muestre la suma de todos los meses del año. Lo más importante al realizar sumas acumuladas es establecer adecuadamente el rango de suma haciendo buen uso de las referencias absolutas y relativas en Excel. Calcular el promedio acumulado Ahora comprueba que has aprendido bien el concepto y agrega una nueva columna para mostrar el promedio de ventas acumulado:

Tip: Debes utilizar la función PROMEDIO Función Excel PROMEDIO Categoría: Estadísticas Nombre en inglés: AVERAGE ¿Qué hace? Obtiene el promedio de los números especificados. Sintaxis PROMEDIO(número1, [número2], …) 

número1 (obligatorio): Este parámetro puede ser un número ó tambien puede ser un rango de celdas que contiene el conjunto de números a promediar.



número2 (opcional): A partir del segundo número los parámetros son opcionales. De igual manera puedes colocar un número u otro rango de celdas de donde la función obtendrá más valores a promediar.

Ejemplos PROMEDIO(73, 52, 87, 13, 39) = 52.8 Analizando números con Excel Excel 2010 incluye funciones estadísticas que nos ayudan a analizar los números contenidos en nuestras hojas de cálculo. Funciones como PROMEDIO, MAX, MIN son de las funciones estadísticas más utilizadas en Excel. Las tres funciones tienen la misma sintaxis, la cual especifica que los parámetros de la función deben ser la lista de números que se desea evaluar. Cada una de estas funciones acepta como máximo 30 números en su lista de parámetros aunque se puede hacer uso de rangos para evitar cualquier complicación por esta limitante. A continuación un ejemplo sobre cómo utilizar estas funciones estadísticas.

Las funciones MAX y MIN obtienen los valores máximos y mínimos del rango especificado. La función PROMEDIO calcula la media aritmética, es decir, suma todos los valores de las celdas especificadas y divide ese valor entre el número de elementos sumados. La función PROMEDIO es equivalente a la siguiente fórmula: =SUMA(B1:B10)/CONTAR(B1:B10)

La función SUMA obtiene el total de los valores y la función CONTAR, que es otra función estadística, determina la cantidad de valores en el rango. Con estas funciones podrás realizar un análisis básico, pero importante, de los números contenidos en tus hojas de Excel. Función Excel MAX Categoría: Estadísticas Nombre en inglés: MAX ¿Qué hace? Encuentra el valor máximo de una lista de números. Sintaxis MAX(número1, [número2], …) 

número1 (obligatorio): El primero de los valores a evaluar. También puede ser un rango.



número2 (opcional): A partir del segundo número los parámetros son opcionales. De igual manera puedes colocar un número o un rango de celdas.

Ejemplos MAX(1, 2, 3) = 3

La función MAX en Excel La función MAX en Excel es de gran ayuda siempre que necesitemos obtener el valor máximo de un conjunto de valores que puede ser una lista de números ubicados en uno o varios rangos de nuestra hoja de Excel. Sintaxis de la función MAX



Número1 (obligatorio): El primer número de la lista o el rango de celdas que contiene los números a evaluar.



Número2 (opcional): El segundo número de la lista o un rango de celdas con números adicionales a incluir en la evaluación.

Podemos especificar un máximo de 255 números en la función MAX en caso de enlistarlos directamente como argumentos pero podemos especificar rangos de celdas como argumentos de la función que a su vez pueden contener varios números que serán evaluados. La función MAX considera números negativos y el cero, por lo que dentro de una lista de números negativos el valor máximo podría ser el cero. Observa el siguiente ejemplo:

Ejemplos de la función MAX En el ejemplo anterior pudiste observar que podemos especificar los números directamente en la función MAX, sin embargo de esta manera solamente podríamos tener hasta 255 números. Para aumentar la cantidad de números que podemos incluir debemos colocarlos en un rango de celdas y especificar dicho rango como argumento de la función.

De la misma manera podemos indicar varios rangos para obtener el valor máximo de todos ellos. Observa que en el siguiente ejemplo los rangos proporcionados a la función MAX son de diferente tamaño y no existe problema alguno para obtener el resultado correcto:

El mayor de una columna con la función MAX La función MAX nos permite obtener fácilmente el número mayor de toda una columna utilizando la siguiente fórmula:

=MAX(A:A)

De esta manera he especificado a la función MAX que tome en cuenta todas las celdas de la columna A.

El comando Autosuma en la ficha Inicio Otra manera de acceder a la función MAX en Excel es utilizar el comando Autosuma que se encuentra en la ficha Inicio. Este método funciona de la siguiente manera, en primer lugar seleccionamos el rango de celdas que deseamos evaluar (M1:M10) y posteriormente desplegamos el menú del botón Autosuma para seleccionar la opción Máx.

Excel insertará la función MAX y como argumento colocará el rango seleccionado previamente. En este ejemplo la función MAX en insertada en la celda M11 que es justo por debajo del rango seleccionado:

La función MAX en Excel será de gran ayuda cada vez que necesitemos encontrar el valor máximo de un grupo de números. La función MIN en Excel

La función MIN en Excel nos devuelve el valor mínimo de un conjunto de valores. Los argumentos de la función MIN pueden ser los números, las celdas o los rangos que contienen los valores que deseamos evaluar. Sintaxis de la función MIN



Número1 (obligatorio): El primer número a considerar o la celda o rango que contiene los números.



Número2 (opcional): El segundo número de la lista o la celda o rango que contiene valores adicionales a considerar.

A partir del segundo argumento todos los demás serán opcionales y hasta un máximo de 255 argumentos. Sin embargo, esto no quiere decir que solamente podemos incluir 255 números en la función MIN ya que podremos indicar rangos con una cantidad de números mayor. Al realizar la evaluación de los números especificados la función MIN tomará en cuenta también los números negativos y el cero. Ejemplos de la función MIN A continuación mostraré algunos ejemplos utilizando la función MIN para dejar en claro su funcionamiento. Para el primer ejemplo, utilizaré la función MIN especificando los números que deseo evaluar como argumentos de la función:

La función MIN devuelve el número más pequeño de la lista especificada. Ahora bien, haré este mismo ejemplo pero ahora tomando los números de un rango de celdas:

El resultado es el mismo que el anterior solo que en este ejemplo solamente estamos utilizando un solo argumento de la función MIN para obtener el valor mínimo de una lista de diez números que se encuentra en un rango. Ya que la función MIN acepta como argumentos rangos de celdas que contienen una lista de números, entonces podemos evaluar varias listas de números y obtener el valor mínimos de todas ellas. A continuación observa cómo son evaluados tres rangos de celdas para obtener el valor mínimo:

El valor regresado por la función MIN es el número 4 que se encuentra en la celda E8. De esta manera puedes observar que la cantidad de números evaluados por la función MIN es prácticamente ilimitada. La única restricción que tenemos es que podemos especificar hasta 255 rangos como argumentos de la función. Excluir ceros de la función MIN En ocasiones nos encontramos con una lista de números que contiene ceros pero necesitamos encontrar el valor mínimo mayor a cero. Para ello podemos utilizar la función MIN junto con la función SI para agregar una condición: =MIN(SI(A1:A10>0, A1:A10))

Lo importante de esta fórmula es que debe ser utilizada en forma matricial por lo que deberás pulsar la combinación de teclas CTRL + MAYÚS + ENTRAR al terminar de introducirla. Observa en el siguiente ejemplo cómo la función MIN devuelve el valor 4 en lugar del valor cero.

La función MIN en Excel siempre nos ayudará a obtener el valor mínimo de una lista de valores. Mostrar el máximo y mínimo en un gráfico En esta ocasión haré una variación del artículo Formato condicional en gráficos de columna de manera que podamos mostrar los valores máximo y mínimo en un gráfico de líneas. Nuestra intención es tener marcadores dinámicos para dichos valores. Suponiendo que los valores originales de las ventas del año pasado se encuentran en la columna B, he creado un par de series de datos auxiliares las cuales me dirán el valor máximo y mínimo de la lista.

La función de la columna Máximo es la siguiente: =SI(B4=MAX($B$2:$B$13), B4, NOD())

La función de la columna Mínimo será similar y solo cambia por el uso de la función MIN: =SI(B11=MIN($B$2:$B$13), B11, NOD())

Crear el gráfico de líneas El siguiente paso es crear el gráfico de líneas utilizando todas las columnas. Es importante decir que a primera vista no observarás los puntos máximo y mínimo:

Crear los marcadores de máximo y mínimo Para poder mostrar los puntos máximo y mínimo necesitamos seleccionar cada serie para modificar sus propiedades. La manera más sencilla es ir a la ficha Presentación, que se encuentra dentro de la ficha contextual Herramientas de gráficos. Dentro del grupo Selección actual debemos elegir de la lista la opción Serie Máximo.

Inmediatamente después debemos pulsar el botón Aplicar formato a la selección lo cual abrirá el cuadro de diálogo Formato de serie de datos.

En las Opciones de marcador selecciona la opción Integrado y elige un Tipo de marcador, así como un tamaño apropiado. Al pulsar el botón cerrar observarás el marcador de Máximo en el gráfico.

Realiza este mismo procedimiento para la Serie Mínimo y obtendrás un gráfico de líneas como el siguiente:

Resaltar el diez por ciento mayor Se hizo un ejercicio de evaluación a varias personas y tengo una lista de puntos alcanzados por cada uno de ellos. Ahora necesito resaltar los registros que se encuentran dentro del diez por ciento mayor de todos los puntajes. La regla es la siguiente. Si el puntaje mayor fue 1,000 puntos quiero saber cuáles puntajes son mayores a 900. Este problema lo resolveré utilizando formato condicional pero primero definiré la fórmula a utilizar. Fórmula para obtener el diez por ciento mayor Dentro de una lista de puntajes puedo obtener el valor más alto con la función MAX y una vez que tengo ese valor puedo multiplicarlo por .90 para obtener ese puntaje mínimo que deben tener las celdas que me interesa resaltar. La fórmula sería la siguiente: MAX(A$1:A:$20) * 0.9

Por ejemplo, si de una lista de 20 resultados el puntaje mayor es 1,000, dicho valor lo obtendré fácilmente con la función MAX, posteriormente lo multiplicaré por .90 y obtendré el valor 900. Ahora solo tendría que resaltar las celdas que tengan un valor mayor a 900. Formato condicional al diez por ciento mayor El cuadro de formato condicional se ve de la siguiente manera. Pon especial atención al cuadro de fórmula:

Una vez aceptados los cambios Excel resaltará las celdas mayores a 900 con el color azul. Estos son los resultados.

Este formato condicional servirá para cualquier lista de puntajes independientemente del puntaje mayor. Observa esta lista de puntajes diferentes.

En esta lista el puntaje mayor es 973 por lo que se resaltan las celdas mayores a 875.70. Es probable que la fórmula utilizada en este ejemplo no sea de utilidad para muchos, pero puedes utilizar cualquier fórmula que resuelva tu necesidad y de esa manera hacer uso de todo el potencial que tiene el Formato condicional de Excel. Gráfica de barras dentro de una celda Una gráfica de barras puede dar un significado especial a los datos en Excel ya que permite visualizar adecuadamente la tendencia de los mismos. De la misma manera que en el artículo Gráfica de pastel dentro de una celda, en esta ocasión mostraré un comportamiento similar pero con una gráfica de barras que estará dentro de una celda de Excel. Descargar la fuente para gráficos de barras

El primer paso es descargar la fuente que nos ayudará a mostrar el minigráfico. Una vez que hayas descargado la fuente debes instalarla en tu equipo (Clic aquí si no recuerdas cómo instalar una fuente). Esta fuente tiene una representación de barra para cada número entre el 0 y el 9 por lo que debemos adecuar nuestros datos para que tengan un valor en dicho rango y puedan ser representados por una barra. Supongamos la siguiente tabla de datos:

Normalizar datos para gráfico de barras Para normalizar la información se puede aplicar la siguiente fórmula a cada celda: =REDONDEAR(día_semana/MAX(todos_días)*9,0)

La fórmula obtiene el valor máximo del rango de todos los días de la semana para un producto y lo utiliza como divisor para las ventas de un solo día. Se multiplica por 9 porque es el rango que necesito para utilizar la fuente adecuada. En un ejemplo concreto, la fórmula para las ventas del domingo del Producto A que se encuentra en la celda C3 será la siguiente: =REDONDEAR(C3/MAX($C$3:$I$3) * 9,0)

Esta fórmula se debe repetir para todas las demás celdas para obtener el siguiente resultado:

Ahora que los datos ya están normalizados, debo concatenar cada uno de los valores en las columnas J a la P y colocarlas en un nueva celda utilizando la función CONCATENAR. El resultado es el siguiente:

El resultado de la columna Q son los datos base para cada una de las barras. Sólo resta cambiar el tipo de fuente de la columna Concatenado a bargraph que es la fuente que instalamos al principio:

Con este último cambio tendrás el resultado deseado.

Finalmente puedes ocultar las columnas con los datos normalizados y dar formato a las celdas de la columna Concatenado para tener una presentación adecuada los minigráficos. Función Excel MIN Categoría: Estadísticas Nombre en inglés: MIN ¿Qué hace? Encuentra el valor mínimo de una lista de números. Sintaxis MIN(número1, [número2], …) 

número1 (obligatorio): El primer número a evaluar.



número2 (opcional): El segundo número a evaluar y hasta 255 números opcionales.

Ejemplos MIN(1, 2, 3) = 1 La función MIN en Excel La función MIN en Excel nos devuelve el valor mínimo de un conjunto de valores. Los argumentos de la función MIN pueden ser los números, las celdas o los rangos que contienen los valores que deseamos evaluar. Sintaxis de la función MIN



Número1 (obligatorio): El primer número a considerar o la celda o rango que contiene los números.



Número2 (opcional): El segundo número de la lista o la celda o rango que contiene valores adicionales a considerar.

A partir del segundo argumento todos los demás serán opcionales y hasta un máximo de 255 argumentos. Sin embargo, esto no quiere decir que solamente podemos incluir 255 números en la función MIN ya que podremos indicar rangos con una cantidad de números mayor. Al realizar la evaluación de los números especificados la función MIN tomará en cuenta también los números negativos y el cero. Ejemplos de la función MIN A continuación mostraré algunos ejemplos utilizando la función MIN para dejar en claro su funcionamiento. Para el primer ejemplo, utilizaré la función MIN especificando los números que deseo evaluar como argumentos de la función:

La función MIN devuelve el número más pequeño de la lista especificada. Ahora bien, haré este mismo ejemplo pero ahora tomando los números de un rango de celdas:

El resultado es el mismo que el anterior solo que en este ejemplo solamente estamos utilizando un solo argumento de la función MIN para obtener el valor mínimo de una lista de diez números que se encuentra en un rango. Ya que la función MIN acepta como argumentos rangos de celdas que contienen una lista de números, entonces podemos evaluar varias listas de números y obtener el valor mínimos de todas ellas. A continuación observa cómo son evaluados tres rangos de celdas para obtener el valor mínimo:

El valor regresado por la función MIN es el número 4 que se encuentra en la celda E8. De esta manera puedes observar que la cantidad de números evaluados por la función MIN es

prácticamente ilimitada. La única restricción que tenemos es que podemos especificar hasta 255 rangos como argumentos de la función. Excluir ceros de la función MIN En ocasiones nos encontramos con una lista de números que contiene ceros pero necesitamos encontrar el valor mínimo mayor a cero. Para ello podemos utilizar la función MIN junto con la función SI para agregar una condición: =MIN(SI(A1:A10>0, A1:A10))

Lo importante de esta fórmula es que debe ser utilizada en forma matricial por lo que deberás pulsar la combinación de teclas CTRL + MAYÚS + ENTRAR al terminar de introducirla. Observa en el siguiente ejemplo cómo la función MIN devuelve el valor 4 en lugar del valor cero.

La función MIN en Excel siempre nos ayudará a obtener el valor mínimo de una lista de valores. Tipos de operadores en Excel Los operadores son un elemento básico de las fórmulas en Excel. Un operador es un símbolo que representa una determinada operación. En esta ocasión haremos una revisión detallada de todos los tipos de operadores en Excel así como su precedencia, es decir, el orden en que se realizan las operaciones. Descarga el archivo utilizado en esta lección Podemos clasificar las fórmulas de Excel en cuatro grupos principales: 1. Fórmulas aritméticas

2. Fórmulas de comparación 3. Fórmulas de texto 4. Fórmulas de referencia Cada uno de estos grupos tiene su propio conjunto de operadores que permiten realizar los cálculos y operaciones específicas de cada grupo. A continuación enlistaré los operadores de cada grupo. 1. Operadores aritméticos Las fórmulas aritméticas son las más comunes y combinan números, referencias de celda, funciones y operadores aritméticos para realizar cálculos matemáticos. La siguiente tabla muestra los operadores aritméticos de Excel:

Aunque el porcentaje no es un operador aritmético, Excel le da un tratamiento como operador ya que al ingresar un símbolo de porcentaje después de un número provocará que Excel realice una división entre 100 de manera automática. 2. Operadores de comparación Los operadores de comparación nos permiten comparar dos o más números o cadenas de texto. Si el resultado de la comparación es positivo, obtendremos como resultado en valor lógico VERDADERO. De lo contrario obtendremos como resultado el valor FALSO. A continuación tenemos la lista de operadores de comparación:

Los operadores de comparación son muy utilizados con las funciones lógicas de Excel que nos permiten ejecutar una acción al cumplirse la condición establecida. 3. Operadores de texto Las fórmulas de Excel también pueden manipular texto y pueden hacer uso del operador de concatenación para unir el valor de dos cadenas de texto.

4. Operadores de referencia Los operadores de referencia nos ayudan a combinar dos o más referencias de celda para crear una sola referencia.

Recuerda que si tu sistema tiene la configuración regional de España, entonces el operador de unión será el punto y coma (;) y no la coma como aparece en la imagen. Precedencia de operadores en Excel Cuando creamos fórmulas que contienen más de un operador, será necesario conocer el orden en que dichas operaciones serán calculadas por Excel. Por esta razón existe un orden de precedencia que determina la manera en que Excel hace los cálculos:

Si tenemos una fórmula con varios operadores con la misma precedencia, entonces Excel realizará los cálculos de izquierda a derecha. Cómo controlar el orden de precedencia La única manera en que podemos influir en el orden de precedencia de operadores en Excel es utilizando paréntesis. Al colocar paréntesis alrededor de un cálculo estaremos diciendo a Excel que deseamos otorgar una alta prioridad a dicho cálculo y por lo tanto se realizará primero. En la siguiente imagen puedes observar cómo se modifica el resultado de una fórmula al incluir paréntesis.

Cuando existen paréntesis dentro de una fórmula, Excel comenzará los cálculos con el paréntesis que tenga el mayor nivel de anidación. Y si dentro de un mismo paréntesis existen varios operadores, entonces se aplicarán las reglas de precedencia antes vistas. De esta manera los paréntesis nos permiten controlar el orden de precedencia de los cálculos es una fórmula de Excel. Material adicional Si quieres leer un poco más sobre el operador de intersección consulta el artículo El operador de intersección de Excel. Fórmulas en Excel

Las fórmulas en Excel son el medio por el cual podemos indicar a la herramienta la manera de realizar un cálculo. Todas la fórmulas en Excel deben iniciar con el símbolo igual (=) o de lo contrario Excel interpretará la fórmula como un simple texto. Decarga el archivo utilizado en esta lección. Las fórmulas en Excel siempre están formadas por una combinación de los siguientes cinco elementos: 1. Operadores: Los operadores son los símbolos que representan las operaciones como la suma (+), la resta (-), la multiplicación o la división (/). 2. Valores: Las fórmulas trabajan con valores los cuales pueden ser numéricos o de texto. Los valores de texto deben estar siempre encerrados por dobles comillas. 3. Paréntesis: Los paréntesis nos ayudan a controlar el orden en que se evalúan las expresiones dentro de una fórmula. 4. Referencias de celda: En lugar de especificar los valores en una fórmula podemos indicar la referencia de la celda que contiene el valor que deseamos incluir en el cálculo. 5. Funciones: Una función es un procedimiento que ya está incorporado en Excel y que realiza un tipo de cálculo específico. Algunos ejemplos de funciones son: SUMA, PRODUCTO, PROMEDIO, HORA, etc. Cómo ingresar fórmulas en Excel Para ingresar una fórmula será suficiente con introducir el símbolo igual (=) en la barra de fórmulas y Excel sabrá que hemos comenzado a introducir una fórmula. Para aquellas fórmulas en Excel que contienen referencias de celda existen tres maneras de indicar dichas referencias: de forma manual, con el teclado o con el ratón. 

Manual: Si decidimos ingresar las referencias en forma manual seremos responsables de colocar la dirección correcta de cada celda o de lo contrario obtendremos un mensaje de error.



Con flechas del teclado: La otra alternativa que tenemos para ingresar referencias de celda dentro de las fórmulas es utilizando las flechas del teclado. Cuando llega el momento de indicar una referencia dentro de una fórmula, en lugar de escribir la referencia, nos movemos con la flechas del teclado sobre la celda que deseamos incluir en la fórmula y Excel insertará la referencia en la fórmula.



Con el ratón: También podemos introducir una referencia utilizando el ratón con solo hacer clic sobre cada celda que necesitemos incluir en la fórmula.

Sin importar el método que utilices para ingresar una fórmula será necesario pulsar la tecla Entrar al finalizar o hacer clic sobre el botón Introducir de la barra de fórmulas. Si por el contrario quieres cancelar la introducción de la fórmula puedes hacer clic en el botón Cancelar de la barra de fórmulas o simplemente pulsar la tecla Esc. Cómo editar las fórmulas en Excel Si por alguna razón debes hacer cambios a tu hoja de Excel será probable que necesites modificar tus fórmulas. También es común que después de realizar algún cambio en la hoja, nuestra fórmula envíe un error por lo que se hará necesario editar la fórmula para corregir el problema. Para editar fórmulas en Excel tenemos dos métodos: 

Edición sobre la celda: Podemos modificar una fórmula directamente en la celda si entramos en el modo de edición de Excel ya sea haciendo doble clic sobre la celda o pulsando la tecla F2.



Edición en la barra de fórmulas: Podemos editar una fórmula haciendo clic sobre ella para convertirla en la celda activa e ir directamente a la barra de fórmulas para hacer las modificaciones necesarias.

Una vez que termines de hacer la edición de una fórmula debes pulsar la tecla Entrar, o pulsar el botón Introducir de la barra de fórmulas, para que Excel vuelva a realizar el cálculo tomando en consideración las modificaciones. Las fórmulas en Excel son el corazón de la herramienta. Es importante que te familiarices pronto con su uso ya que son el ingrediente principal para obtener el máximo provecho de Excel. El operador de intersección de Excel El operador de intersección de Excel es poco conocido pero es de gran utilidad para encontrar una celda que sea común entre dos rangos. El operador de intersección es un espacio en blanco que se coloca entre los dos rangos que deseamos evaluar. Encontrar la intersección de dos rangos Considera la siguiente tabla donde he colocado números aleatorios. De color verde podrás identificar el rango A1:B3 y de color azul el rango B3:C5. La celda B3 tiene un fondo de color gris que significa que dicha celda pertenece a ambos rangos.

Si quisiera encontrar la intersección de dos rangos utilizando una fórmula puedo utilizar el operador de intersección de la siguiente manera: =A1:B3 B3:C5

Debes notar el espacio en blanco que se encuentra entre ambos rangos. Al colocar esta fórmula en Excel obtendré el valor de la intersección:

Con esta fórmula podemos entender fácilmente el comportamiento del operador intersección de Excel. Sumar la intersección de dos rangos Ahora mostraré cómo podemos utilizar el operador de intersección dentro de la función SUMA para sumar el valor de todas las celdas que se encuentra en la intersección de dos rangos. La fórmula que utilizaré es la siguiente: =SUMA(A1:B3 B2:C5)

En la siguiente imagen puedes observar esta fórmula y comprobar el resultado es efectivamente la suma de las celdas B2 y B3 que son la intersección entre ambos rangos:

Encontrar la intersección entre una fila y una columna Considera la siguiente tabla de datos que contiene la información de ventas de cada uno de los vendedores a lo largo de un año:

Si quisiera encontrar las ventas para Hugo en el mes Junio podría utilizar algunas funciones de Excel para obtener el resultado correcto: =INDICE(A1:D13, COINCIDIR("Junio", A1:A13,0), COINCIDIR("Hugo", A1:D1, 0))

Observa cómo la fórmula nos devuelve el resultado esperado:

Sin embargo, este mismo resultado lo podemos obtener de una manera mucho más simple utilizando el operador de intersección de Excel con la siguiente fórmula: =B1:B13 A7:D7

Observa el resultado de dicha fórmula en la siguiente imagen:

De esta manera puedes encontrar fácilmente la intersección entre una fila y una columna en Excel utilizando el operador de intersección. Función Excel IM.SUSTR Categoría: Ingeniería Nombre en inglés: IMSUB ¿Qué hace? Devuelve la resta de dos o más números complejos. Sintaxis IM.SUSTR(núm_complejo1, [núm_complejo2], …) 

núm_complejo1 (obligatorio): El primer número complejo a restar.



núm_complejo2 (opcional): El segundo número complejo a restar y hasta un máximo de 255 números.

Ejemplos IM.SUSTR(“4+3j”, “2+5j”) = 2-2j IM.SUSTR(“3+4i”, “5-3i”) = -2+7i