Filtrar tabla dinmica segn valor de celdaCuando tienes varias tablas dinmicas 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 fcil de hacerlo en Excel. Hoy analizaremos una alternativa para filtrar una tabla dinmica segn el valor de una celda.Antes de continuar, debo aclarar que los filtros de informe son la opcin ms sencilla para filtrar una tabla dinmica ya que podemos elegir los datos que deseamos visualizar. Sin embargo, hay ocasiones en las que una frmula de Excel inserta un valor en una celda por el cual queremos filtrar una tabla dinmica o simplemente porque el usuario desea ingresar el texto manualmente. Cualquiera que sea el caso, ser necesario utilizar cdigo VBA para filtrar la tabla dinmica en base al valor de dicha celda.Como primer ejemplo utilizaremos una tabla dinmica que muestra la lista de estados con sus respectivos montos de ventas de los ltimos dos aos. Cada estado pertenece a una regin (Norte, Sur, Este, Oeste) que est presente como el filtro de informe de la tabla dinmica, pero no utilizaremos dicho filtro de informe sino que aplicaremos el filtro a la tabla dinmica en base al valor de la celda F1.

Filtrar tabla dinmica segn valor de celdaEl cdigo que utilizaremos detecta cualquier cambio en la celda F1 y posteriormente se realiza la actualizacin de los datos de la tabla dinmica.123456789101112131415161718Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("F1")) Is Nothing Then'En base al campo Region de la Tabla dinmica1:With PivotTables("Tabla dinmica1").PivotFields("Region")'Limpiar todos los filtros.ClearAllFilters'Filtrar por el valor de la celda F1On Error Resume Next.CurrentPage = Range("F1").ValueEnd WithEnd IfEnd Sub

La coleccin PivotTables nos permite acceder a los campos de una tabla dinmica. 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 dinmica y puedes notar que el filtro de informe tambin cambia automticamente 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 vlida en la celda F1, o si simplemente dejamos la celda en blanco, ya que con la lnea de cdigo On Error Resume Next se captura el error y se limpia el filtro.Macro para filtrar todas las tablas dinmicasComo segundo ejemplo crear una tabla dinmica diferente en otra hoja y modificar la macro anterior para filtrar todas las tablas dinmicas del libro de acuerdo al valor de la celda F1. El cdigo de la macro es el siguiente:123456789101112131415161718192021222324252627282930Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("F1")) Is Nothing ThenDim hoja As WorksheetDim td As PivotTable'Recorrer todas las hojas del libroFor Each hoja In ThisWorkbook.Worksheets'Recorrer las tablas dinmicas de la hojaFor Each td In hoja.PivotTables'En base al campo Region de la tabla dinmica:With td.PivotFields("Region")'Limpiar todos los filtros.ClearAllFilters'Filtrar por el valor de la celda F1On Error Resume Next.CurrentPage = Range("F1").ValueEnd WithNext tdNextEnd IfEnd Sub

A diferencia del primer ejemplo, en esta macro utilizamos la coleccin ThisWorkbook.Worksheets para recorrer todas las hojas del libro y de esa manera acceder a todas las tablas dinmicas. Al ingresar un valor en la celda F1 se aplicar el filtro correspondiente a la tabla dinmica de la hoja actual:

Adems, si cambiamos a la hoja del primer ejemplo, notars que tambin se ha aplicado el mismo filtro a la tabla dinmica:

Observa que el filtro de informe muestra el valor Sur aunque la celda F1 est vaca, 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 dinmicas deben incluir el campo por el cual se est filtrando, que en nuestro ejemplo es el campo Region.Las macros que hemos creado te ayudarn a filtrar una tabla dinmica por el valor de una celda e inclusive podrs aplicar dicho filtro a todas las tablas dinmicas de un libro si as lo requieres. Descarga el libro de trabajo utilizado en este artculo y contina probando los filtros.Por ltimo debo decir que, si en lugar de utilizar el valor de una celda, deseas filtrar varias tablas dinmicas con un solo filtro de informe, te recomiendo leer el artculo Cmo vincular tablas dinmicas por un filtro de informe donde encontrars otra alternativa de solucin.Punto de equilibrio en ExcelEl punto de equilibrio de cualquier negocio est determinado por el nivel de ventas necesario para cubrir los costos totales de produccin. 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 operacin y los costos de fabricacin 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 prdidas monetarias.Frmula del punto de equilibrioLa frmula 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 energa elctrica o telefona, etc. Este tipo de costos los conocemos como fijos porque no importa si vendemos o no productos, sern gastos que tendremos que cubrir.Los costos variables se refieren principalmente a las materias primas utilizadas en la produccin de los artculos que venderemos. Estos costos son variables porque dependen de la cantidad de productos a fabricar ya que entre mayor sea la produccin, mayor sern los costos de las materias primas. Para dejar en claro el uso de la frmula del punto de equilibrio haremos un ejemplo utilizando Excel.Ejemplo de punto de equilibrio en ExcelUna compaa 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. Cuntas almohadas deber vender la empresa para cubrir los costos totales?Para obtener el punto de equilibrio en Excel aplicamos la frmula 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 frmula:=B2*B4 - (B3*B4) - B1En 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 grficamente, pero para eso ser necesario generar los datos para crear el grfico de Excel.Grfico del punto de equilibrio en ExcelLa tabla que crearemos tendr 4 columnas que sern: 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 ms los costos variables correspondientes a las unidades producidas y el clculo se hace con la siguiente frmula:

Finalmente las utilidades son la diferencia entre las ventas y los costos. Con nuestros datos listos ser suficiente con insertar un grfico de lneas para obtener el siguiente resultado.

Una vez que hemos creado el grfico del punto de equilibrio en Excel podemos darnos cuenta fcilmente que justamente en la marca de las 1000 unidades suceden dos cosas importantes:1. La lnea de Ventas y Costos se intersectan y a partir de ese punto las ventas son mayores.2. La lnea 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 ms sobre el punto de equilibrio y la manera en que podemos calcularlo con Excel adems de crear un grfico que nos permita visualizar rpidamente 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.Cmo multiplicar horas por dinero en ExcelTal vez te ha sucedido que llega el momento del pago a los empleados de la empresa y tienes lista la informacin en Excel con las horas trabajadas durante la semana. Ya has calculado el total de horas a pagar y solo hace falta la multiplicacin por la tarifa establecida, sin embargo solo obtienes montos incorrectos.Cmo se hace la multiplicacin de horas por dinero en Excel? Todo indicara que es un clculo muy sencillo pero por ms que lo intentas solo consigues datos errneos. En la siguiente imagen podrs darte cuenta a lo que me refiero.

La celda C9 tiene la frmula =C7*C8 que es la multiplicacin del total de horas trabajadas por la tarifa indicada. El resultado debera ser $4,050 pero en su lugar tenemos solo $168.75 que es un monto incorrecto.Si analizamos las celdas involucradas en el clculo nos damos cuenta que la tarifa por hora es simplemente el valor numrico 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:

Cul de los dos valores es el culpable del error? Definitivamente no es el valor numrico de la tarifa por hora, as que el problema debe ser ocasionado por la suma de horas.Las horas en Excel son decimalesEl problema con la multiplicacin 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 numrico 1.6875 y es la razn 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 nmero 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 da completo de 24 horas y por tal motivo el valor de la celda C7 significa que tenemos 1 da entero y 0.6875 de otro da.Multiplicar horas por dinero en ExcelPara resolver este problema del valor decimal de las horas ser suficiente con agregar la multiplicacin por 24 de manera que se haga la conversin del valor decimal a la cantidad correcta de horas. Observa cmo al incluir esta multiplicacin en la frmula de la celda C9 obtenemos el resultado correcto:

Ahora ya sabes cmo 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 ACERCACalcular horas trabajadas entre dos fechas en ExcelEs muy fcil obtener los das laborables en Excel pero en ms de una ocasin necesitars calcular las horas trabajadas entre dos fechas y no existe una funcin en Excel que pueda hacer eso de manera automtica.Seguramente encontrars muchas alternativas para resolver este problema, pero la alternativa que yo utilizo implica el uso de la funcin DIAS.LAB que ser de gran ayuda en este clculo. La solucin 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 clculo. En segundo lugar tenemos dos celdas que contienen una fecha y una hora indicando la fecha de inicio y la fecha final del clculo.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 das intermedios, consideraremos un horario de trabajo completo, es decir, desde las 8:00 y hasta las 18:00 horas. Por ejemplo, para el da 09 de noviembre consideraremos 10 horas de trabajo.Obtener los das laborablesEl primer paso ser obtener los das laborables con un horario completo entre ambas fechas. Este clculo lo haremos con la funcin DIAS.LAB de la siguiente manera:=DIAS.LAB(B5, B6) - 2La funcin DIAS.LAB calcula los das 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 das har un clculo especial. Una vez que tengo los das laborables puedo obtener las horas fcilmente haciendo una multiplicacin por las horas totales en el horario laboral:=(DIAS.LAB(B5,B6) - 2) * (B3-B2)Esta frmula nos da el total de las horas trabajadas en los das laborables que se encuentran entre la fecha de inicio y la fecha final. Observa lo que sucede si aplico esta frmula en los datos de nuestra hoja:

La celda D8 muestra un total de 30 horas que es el total de horas laborables de 3 das. 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.An no llegamos al resultado final porque todava nos falta sumar las horas trabajadas durante el da inicial y tambin sumar las horas de la fecha final.Calcular las horas trabajadas al inicioYa que para el da inicial tenemos la hora exacta en que se comienza a trabajar haremos un clculo diferente. Para obtener las horas trabajadas en el da inicial utilizar la siguiente frmula:=B3 - RESIDUO(B5,1)Para poder explicar lo que hace la funcin RESIDUO debemos recordar que las fechas en Excel son nmeros. Al aplicar esta funcin a la fecha de inicio obtendr solamente las horas de manera que pueda hacer una resta con la celda B3 la cual est indicada tambin en horas.El resultado de esta frmula es 09:45 que son las horas trabajadas durante el primer da y es un nmero que deseo sumar a la frmula que habamos comenzado a elaborar:=(DIAS.LAB(B5,B6) - 2) * (B3-B2) + B3 - RESIDUO(B5,1)Observa el resultado de aplicar esta frmula a nuestros datos:

Calcular las horas trabajadas al finalPor ltimo calcularemos las horas trabajadas en el da final y de igual manera utilizar la funcin RESIDUO para obtener las horas:=RESIDUO(B6,1) - B2Esta frmula considera los 25 minutos excedentes en el da final por lo que el resultado es de 10:25 horas. Solo resta sumar este resultado a nuestra frmula principal:=(DIAS.LAB(B5,B6) - 2) * (B3-B2) + B3 - RESIDUO(B5,1) + RESIDUO(B6,1) - B2Con esta frmula 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 adicionalesEn el resultado final he dejado los minutos, pero es posible hacer un redondeo para tener una cantidad exacta de horas. Para tener ms informacin sobre cmo redondear las horas consulta el siguiente artculo: Cmo redondear horas en Excel.La funcin DIAS.LAB considera, de manera predeterminada, los das sbado y domingo como el descanso semanal pero podramos utilizar la funcin DIAS.LAB.INTL en caso de tener algn da de descanso diferente. Adems, con cualquiera de las dos funciones puedes tener una lista de das festivos que desees omitir del clculo. INICIO FUNCIONES ACERCALa funcin DIAS.LAB en ExcelLa funcin DIAS.LAB en Excel nos ayuda a obtener el nmero de das laborables entre dos fechas determinadas. La funcin DIAS.LAB nos permite especificar un conjunto de das de vacaciones que sern excluidos de los das laborables contabilizados.Sintaxis de la funcin DIAS.LABA continuacin una descripcin de los argumentos de la funcin DIAS.LAB:

Fecha_inicial (obligatorio): Es la fecha a partir de la cual se comenzarn a contar los das laborables. Fecha_final (obligatorio): La fecha que marca el final de la contabilizacin de das laborables. Vacaciones (opcional): Conjunto de una o varias fechas que sern excluidas del calendario de das laborables.La funcin DIAS.LAB contabiliza los das laborables de lunes a viernes y excluye los fines de semana (sbado y domingo).Ejemplos de la funcin DIAS.LABEn el siguiente ejemplo podrs observar el nmero de das 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 sbados y los domingos. Ahora incluir una lista de das 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 funcin DIAS.LAB para que considere los das de vacaciones.

La funcin deja de contabilizar las fechas especificadas como vacaciones y lo puedes ver reflejado en el resultado final.Si por alguna razn llegamos a colocar en la lista de das de vacaciones la fecha de un sbado o de un domingo esos das no surtirn ningn efecto porque la funcin DIAS.LAB no contabiliza esos das. Observa en el siguiente ejemplo cmo he agregado a la lista de das de vacaciones un par de fechas que corresponden a un sbado (18/02/2012) y un domingo (19/02/2012) y sin embargo el resultado de la funcin 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 funcin sino que coloqu explcitamente la referencia para el rango B3:B7. Esto nos confirma que podemos utilizar ambos mtodos al momento de especificar la lista de das de vacaciones.La funcin DIAS.LAB.INTL en ExcelLa funcin DIAS.LAB.INTL en Excel fue introducida en la versin 2010 y nos permite contar los das laborables entre dos fechas pero con la ventaja de poder especificar los das de la semana que necesitamos considerar como fines de semana adems de los das de vacaciones.La sintaxis de la funcin DIAS.LAB.INTLLa funcin DIAS.LAB.INTL tiene cuatro argumentos que nos permiten personalizar adecuadamente la manera en como deseamos contar los das laborables.

Fecha_inicial (obligatorio): Fecha donde se inicia el conteo de los das laborables. Fecha_final (obligatorio): Fecha final del conteo de das laborables. Fin_de_semana (opcional): Nmero que especifica el da o das que sern considerados como el fin de semana. Das_no_laborables (opcional): Conjunto de una o varias fechas que indican los das no laborables.Si no especifica el argumento de Fin_de_semana la funcin DIAS.LAB.INTL har el clculo de das laborables suponiendo un fin de semana de sbado y domingo.Establecer un fin de semana personalizadoEl tercer argumento de la funcin DIAS.LAB.INTL nos permite especificar los das de la semana que sern considerados como fines de semana. Por ejemplo, si deseo especificar que el fin de semana est formado slo por el da domingo debo escribir la funcin de la siguiente manera:=DIAS.LAB.INTL(B1,B2,11)El nmero 11 en el tercer argumento de la funcin har que se consideren slo los domingos como el fin de semana. Observa el resultado de la funcin recin escrita.

A continuacin muestro la lista completa de nmeros vlidos para el tercer argumento de la funcin DIAS.LAB.INTL:

Considerar das no laborablesPara agregar una lista de das no laborables a la funcin 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 sbado y adems agregar tres fechas como das no laborables en el rango B3:B5:

La funcin DIAS.LAB.INTL en Excel es una funcin muy flexible que seguramente se adaptar adecuadamente a la manera en cmo necesites realizar el clculo de los das laborables.Das laborables con ExcelLa funcin DIAS.LAB nos ayuda a obtener el total de das laborables entre dos fechas, sin embargo tiene un inconveniente y es que asume que los das de fin de semana son el sbado y el domingo. Qu hacer si queremos considerar das de fin de semana diferentes?La funcin DIAS.LABPrimero comprobar lo que he dicho. As que utilizar la funcin DIAS.LAB para calcular los das laborables del mes de enero del 2011. Observa el siguiente ejemplo.

Efectivamente existen 21 das laborables en el mes de enero considerando los sbados y domingos como fin de semana. Pero si quisiera tomar como das de descanso el viernes y el sbado el nmero de das laborables del mes sera diferente. Para obtener el resultado deseado utilizar la funcin DIAS.LAB.INTL.La funcin de Excel DIAS.LAB.INTLLa funcin DIAS.LAB.INTL permite especificar los das del fin de semana que deseo utilizar. Observa cmo al introducir la frmula Excel me permite seleccionar el fin de semana adecuado:

Al elegir los das viernes y sbado como el fin de semana a considerar, el resultado de das laborables para el mes de enero del 2011 cambia a 22 das.

Varios pases del medio oriente tienen un fin de semana establecido en los das viernes y sbado, lo cual ha sido una de las razones por las que se ha introducido la funcin DIAS.LAB.INTL en Excel.Aunque es poco probable que ests leyendo este artculo desde alguno de estos pases, es ms probable que alguna vez tengas algn trato comercial con alguno de ellos o simplemente la compaa donde laboras desea hacer un clculo de das laborables utilizando algn da de la semana en especial.Tiempo en ExcelEn el artculo anterior mencion cmo las fechas son un nmero entero con el cual Excel realiza los clculos correspondientes a fechas. De manera similar, las horas del da tienen un nmero asignado que permite trabajar con operaciones de tiempo.A diferencia de las fechas donde cada da tiene asignado un nmero entero, las horas de un da 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 horaDe igual manera que con una fecha, con tan solo cambiar el formato de la celda que contiene la informacin podremos conocer el valor decimal asignado a una hora especfica.

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

Al especificar la hora debes utilizar un nmero entre 0 y 23, es decir, utilizar el formato de 24-horas. Otro dato interesante sobre esta funcin es que si especificas un nmero de minutos o segundos mayor a 59 se har una conversin automtica, es decir, si especifico 90 minutos, Excel lo entender como 1 hora y 30 minutos:

La funcin VALHORAEs posible que en ocasiones intercambies informacin con terceros y te enven informacin sobre la hora del da pero en formato texto. Aun as puedes utilizar la funcin de Excel VALHORA para convertir dicho texto al tipo de dato Hora.

Si el resultado se muestra como un nmero decimal recuerda que solamente es cuestin de cambiar el formato de la celda al tipo Hora para que se despliegue adecuadamente.La funcin AHORAYa que hemos cubierto el tema tanto de fechas como de horas en Excel puedo explicarte un poco mejor la funcin AHORA. Esta funcin obtiene el nmero de serie de la fecha y hora del reloj interno de tu equipo justo en el instante en que se llama la funcin.Si aplicamos el formato General a la celda que utiliza la funcin AHORA podramos ver el valor numrico que utiliza Excel para describir el instante de tiempo actual.

Puedes observar que la celda tiene la combinacin de da y hora por lo que la parte entera del nmero representa al da, y los decimales representan la hora.Ya que la funcin AHORA se actualiza cada vez que recalculamos una hoja de Excel, ser suficiente con pulsar la tecla F9 para ir cambiando el valor numrico asignado a un instante en el tiempo.Fechas en ExcelExcel almacena una fecha como si fuera un nmero entero. Al utilizar un sistema como ste, Excel puede sumar, restar y comparar fcilmente las fechas sin necesidad de hacer alguna conversin ni clculo especial.Por ejemplo, la fecha 1/1/2000 est representada por el nmero de serie 36526 y la fecha 05/10/2000 tiene asignado el valor 36804. Como puedes imaginar, al realizar una operacin con fechas se utiliza su nmero de serie para facilitar las operaciones.El primero y ltimo da de ExcelExcel reconoce todas las fechas posteriores a 1/1/1900. Es por eso que el primer da del ao 1900 tiene asignado el nmero 1. Por el contrario, el ltimo da que Excel reconoce es el 31/12/9999 el cual tiene asignado el nmero 2958465.Valor numrico de una fechaEs realmente sencillo conocer el valor numrico de una fecha, solamente cambia el formato de la celda que contiene una fecha a un formato General y Excel desplegar su valor numrico.

La funcin FECHAFinalmente mencionar la funcin FECHA la cual nos ayuda a obtener el nmero de serie que le corresponde a una fecha dados el da, el mes y el ao.

Esta funcin es de gran utilidad para convertir datos a un formato fecha en caso de que tengas el da, mes y ao en columnas separadas. Una vez convertidos al tipo de dato adecuado se podrn hacer operaciones con las fechas sin dificultad alguna.Sumar valores entre dos fechasLa funcin SUMAR.SI nos permite sumar valores que cumplen con cierta condicin. Si necesitas sumar valores entre dos fechas la condicin especificada en la funcin ser de gran relevancia para obtener el resultado deseado.Supongamos la siguiente tabla de datos:

Quiero sumar los valores que estn entre las fechas 4 de enero de 2011 y 10 de enero de 2011. Antes de escribir la frmula explicar el razonamiento que hay detrs 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 explicacin? Observa la siguiente imagen:

La columna A en azul ilustra las filas que se sumarn con la primera frmula, 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 sumarn en la segunda operacin y que se restarn del primer resultado para obtener el total deseado el cual est ilustrado por las filas marcadas en verde en la columna B.La funcin SUMAR.SI y la funcin FECHALa primera frmula 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 mltiples criterios, la funcin CONTAR.SI.CONJUNTO ser de mucha utilidad.Contar condicionalLa funcin CONTAR.SI es una funcin estadstica que nos permite contar las celdas de un rango que cumplen con cierta condicin. Esta funcin utiliza solamente dos argumentos, el primero siendo el rango y el segundo argumento la condicin.Detalles de la funcin CONTAR.SI en ExcelLa sintaxis de la funcin 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 condicin que deber cumplir el valor de una celda del rango para ser considerado en la cuenta. El criterio se puede expresar como un nmero, como una expresin o un texto que indica las celdas que se debern contar.Un nmero como criterioCuando se especifica un nmero 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 funcin de la siguiente manera:=CONTAR.SI(tabla_datos, 3)Una expresin como criterioSi queremos utilizar una expresin como criterio, debemos encerrar la expresin entre comillas dobles. Supongamos que deseamos contar las celdas que tienen un valor mayor a 3 entonces podemos utilizar la funcin de la siguiente manera:=CONTAR.SI(tabla_datos, ">5")Un texto como criterioSi deseamos contar las celdas que sean iguales a un texto es suficiente con especificarlo entre comillas dobles. Es importante mencionar que la condicin no es sensible a maysculas y minsculas por lo que la condicin verde ser lo mismo que VERDE.=CONTAR.SI(tabla_datos, "verde")Contar celdas nicasEste es un ejemplo sencillo sobre cmo puedes contar celdas que contienen valores nicos dentro de una lista. Es decir, de todos los valores de una lista cuntos son los que aparecen una sola vez?Y para solucionar este problema utilizar la funcin CONTAR.SI. Esta funcin nos permite establecer un rango y una condicin para saber si el elemento se debe contar. Pero observa el comportamiento peculiar de esta funcin al establecer como condicin el mismo rango especificado.

Al colocar el mismo rango en ambos parmetros de la funcin, Excel calcula el nmero 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 funcin 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 listaEn ocasiones es de gran ayuda saber el nmero de veces que un elemento aparece dentro de una lista. Esto se puede lograr de una manera muy sencilla a travs de la funcin CONTAR.SI. Esta funcin cuenta el nmero de celdas que cumplen con el criterio especificado.Contar repeticiones en ExcelSupongamos la siguiente lista:

Si me interesa saber cuntas veces aparece el da domingo, puedo utilizar la funcin CONTAR.SI de la siguiente manera:CONTAR.SI(A1:A25, "Domingo")La funcin contar el nmero de veces que se repite el valor y mostrar el resultado. Puedo cambiar el parmetro Domingo para colocar una referencia a una celda de manera que pueda dejar la frmula como:CONTAR.SI($A$1:$A$25,C1)De esta manera puedo colocar los nombres de la semana en una columna y aplicar fcilmente la frmula para cada uno de ellos obteniendo el resultado adecuado:

Consulta ms informacin sobre la funcin CONTAR.SI.Filtrar valores nicosA 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 debers 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 dilogo Filtro avanzado, asegrate de seleccionar la opcin Filtrar la lista sin moverla a otro lugar y activa la casilla de verificacin Slo registros nicos y haz clic en Aceptar.

Lista con valores nicosObservars 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 debers remover el filtro haciendo clic sobre el botn Borrar de la seccin 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 quedarn intactos.Valores nicos en otro lugarPara lograr esto debes hacer una seleccin diferente en el cuadro de dilogo Filtro Avanzado donde debers seleccionar la opcin Copiar a otro lugar y posteriormente en el cuadro Copiar a debers escribir la referencia de la celda a donde se copiarn los registros nicos.

Esta opcin har que tengas una copia de los valores nicos de tus datos en otra columna.

Los valores nicos encontrados no son ordenados bajo ningn criterio, solamente son mostrados de acuerdo al orden en que son encontrados por Excel dentro del rango de celdas especificado.Contar valores nicos en ExcelEn 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 frmulas 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 nmero de ciudades nicas de la columna B. Para este ejemplo lo podra hacer visualmente, pero si tengo una lista con miles de registros la tarea se puede complicar.Contar valores nicos en un rangoPara realizar la cuenta de valores nicos en Excel podemos utilizar la siguiente frmula matricial:{=SUMA(1/CONTAR.SI(B2:B10, B2:B10))}Recuerda que para que una frmula sea matricial debemos pulsar las teclas CTRL + MAYS + ENTRAR justo al terminar de introducir la frmula lo cual har que Excel coloque los corchetes alrededor de la frmula. Observa el resultado de aplicar esta frmula en el ejemplo:

La nica desventaja de esta frmula es que dejar de funcionar adecuadamente si una celda est vaca y obtendremos un error #DIV/0! como resultado.

Para resolver este problema podemos utilizar la funcin SI.ERROR de manera que nuestra frmula siga funcionando. Esta es la frmula a utilizar:=SUMA(SI.ERROR(1/CONTAR.SI(B2:B10, B2:B10), 0))Observa el resultado al utilizar esta frmula sobre el rango que contiene una celda vaca:

De esta manera hemos eliminado el error #DIV/0! y hemos logrado contar valores nicos en Excel an dentro de un rango con celdas vacas.Evitar duplicados en ExcelEn algunos artculos he sugerido mtodos para remover duplicados de una lista, sin embargo a veces necesitamos aplicar algunas medidas preventivas para no introducir duplicados al momento de capturar la informacin.Supongamos que utilizar la columna A de una hoja de Excel para capturar los cdigos de diversos productos. Para lograr que Excel me notifique en el momento en que introduzca un valor duplicado puedo utilizar la funcionalidad de Validacin de datos.Evitar duplicados con Validacin de datos en ExcelPara aplicar dicha validacin debo seleccionar primero toda la columna A que ser donde se realizar la captura y posteriormente hacer clic en el botn Validacin de datos que se encuentra en la ficha Datos dentro del grupo Herramientas de datos.

Se mostrar el cuadro de dilogo Validacin de datos y debers seleccionar la opcin Personalizada y colocar la siguiente frmula:=CONTAR.SI(A:A,A1)=1

La frmula introducida har que cada vez que ingresemos un cdigo de producto en la columna A se haga un recuento del nmero de veces que aparece dicho valor. La condicin indica que solamente se permitirn los valores que aparezcan una sola vez as que, si dicho valor aparece por segunda ocasin Excel mostrar un mensaje de advertencia.

Es importante mencionar que este mtodo de validacin 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 validacin.Quitar valores duplicadosEs muy comn 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 sern eliminados de manera permanente. Es por eso que debes tener cuidado al momento de aplicar este procedimiento.Remover valores duplicadosAl momento de remover los valores duplicados slo se afecta el rango de celdas seleccionado por lo que el primer paso es seleccionar el rango de celdas sobre el cul se aplicar la accin.

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

Aparece el cuadro de dilogo Quitar duplicados y en la seccin Columnas puedes seleccionar una o ms columnas de datos. Para seleccionar todas las columnas rpidamente puedes hacer clic en Seleccionar todo. Para quitar la seleccin de las columnas haz clic en Anular seleccin. El comando se ejecutar sobre aquellas columnas que estn seleccionadas. Asegrate de marcar la caja de seleccin 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 botn Aceptar para terminar con la operacin y podrs observar el resultado.

Si lo que deseas es slamente filtrar los valores sin eliminarlos de la lista, consulta el siguiente artculo Filtrar valores nicosEncontrar mayores de edadEn muchos pases la mayora de edad se obtiene al cumplir 18 aos. 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 opcin que describo a continuacin.Un primer mtodo es ordenar los datos por la columna edad y contabilizar manualmente los registros de las personas que sean mayores a 18 aos, pero si la lista es demasiado grande o si el resultado de dicha cuenta ser utilizado posteriormente en algn otro clculo, entonces lo mejor ser utilizar una frmula. Para este ejemplo considerar la siguiente tabla de datos:

Contar mayores de edad en ExcelPara contar los registros que cumplen con la condicin de mayora de edad utilizar la funcin CONTAR.SI de la siguiente manera:=CONTAR.SI(D2:D16,">18")El primer argumento de la funcin es la matriz que especifica los valores de la columna Edad los cuales sern comparados y contabilizados si cumplen con la condicin de mayora de edad que es el segundo argumento de la funcin. El resultado es el siguiente:

Utilizando esta misma tabla de datos y la funcin CONTAR.SI podramos 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 condicin para que el valor sea igual a M.

Funcin Excel CONTAR.BLANCOCategora: EstadsticasNombre en ingls: COUNTBLANKQu hace?Cuenta las celdas en blanco dentro de un rango.SintaxisCONTAR.BLANCO(rango) rango (obligatorio): El rango de celdas donde se contarn las celdas en blanco.EjemplosCONTAR.BLANCO(A1:A20) = Nmero de celdas en blanco en el rango A1:A20Funcin Excel RESTOCategora: Matemticas y trigonomtricasNombre en ingls: MODQu hace?Obtiene el residuo de la divisin de dos nmeros.SintaxisRESTO(nmero, divisor) nmero (Obligatorio): Nmero del cual quieres obtener el residuo. divisor (Obligatorio): Nmero por el cual se va a dividir.EjemplosRESTO(8,5) = 3RESTO(5,8) = 5Ventas por representanteEn el ejemplo del da de hoy tengo una tabla de datos que muestra el nmero 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 funcin SUMAR.SI en ExcelPara obtener la suma de las ventas de cada representante utilizar la funcin SUMAR.SI con la cual podr sumar todos los registros correspondientes a cada persona. Esta funcin 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 Andrs puedo emplear la siguiente frmula:=SUMAR.SI($B$2:$B$21,"Andrs",$C$2:$C$21)La funcin SUMAR.SI buscar todos los registros de la columna B que sean iguales a Andrs y sumar el valor que le corresponde de la columna C.Observa el resultado al aplicar la frmula anterior, pero sustituyendo la cadena de texto Andrs por una referencia de celda.

En este ejemplo, para el segundo parmetro de la funcin, he especificado la direccin de la celda que contiene el valor Andrs que es la celda E1.Ya que tenemos referencias absolutas en ambos rangos de la funcin, solamente debo copiar la frmula hacia abajo para conocer las ventas de los dems representantes.

La funcin 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.Funcin Excel AHORACategora: Fecha y horaNombre en ingls: NOWQu hace?Obtiene la fecha y hora actual.SintaxisAHORA()EjemplosAHORA() = La fecha y hora actualFuncin Excel TIEMPOCategora: Fecha y horaNombre en ingls: TIMEQu hace?Obtiene el nmero de serie que representa la hora especificada.SintaxisTIEMPO(hora, minuto, segundo) hora (obligatorio): Nmero que representa la hora. minuto (obligatorio): Nmero que representa los minutos. segundo (obligatorio): Nmero que representa los segundos.EjemplosTIEMPO(7,35,40) = 0.316435185TIEMPO(21,7,18) = 0.880069444Funcin Excel VALHORACategora: Fecha y horaNombre en ingls: TIMEVALUEQu hace?Convierte un texto en un nmero de serie para una hora.SintaxisVALHORA(texto_de_hora) texto_de_hora (obligatorio): Cadena de texto que representa una hora.EjemplosVALHORA(7:35:40) = 0.316435185VALHORA(21:07:18) = 0.880069444Sumar horas y minutos en ExcelSumar 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 ExcelEl 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 continuacin te mostrar el mtodo ms sencillo para sumar horas y minutos en Excel. En la celda B7 utilizar la funcin SUMA para realizar la suma de todos los elementos. Observa cmo 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 opcin Formato de celdas e ir a la seccin Nmero y posteriormente a la opcin 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 smbolo 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. Tambin puedes aplicar un truco similar al momento de sumar minutos y segundos en Excel.Sumar minutos y segundosHas tenido la necesidad de sumar minutos y segundos en Excel? Esta operacin no es muy intuitiva, pero en esta ocasin te mostrar cmo dar formato a este tipo de datos y hacer operaciones bsicas con ellos.Para expresar minutos y segundos en una celda, la informacin 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:55Es importante colocar el cero inicial para que Excel interprete los datos de minuto y segundo correctamente. Al introducir esta informacin en Excel obtenemos el siguiente resultado:

Observa como Excel convierte los nmeros ingresados a un nmero decimal.Formato de celda para minutos y segundosPara tener la informacin con el formato que necesitamos debemos hacer clic derecho sobre la celda y seleccionar la opcin Formato de celdas y dentro de la seccin 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 informacin de minutos y segundos y posteriormente hacer la suma como si fuera cualquier otro tipo de datos.

La ventaja de utilizar este mtodo es que al momento de sumar los segundos, Excel aadir automticamente un minuto por cada 60 segundos que se sumen. Esta converisin la tendramos que hacer manualmente en caso de tener los minutos y los segundos en columnas separadas.Formato de celdas personalizadoA travs del formato de celdas personalizado puedes dar la apariencia que necesitas a tus datos numricos 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 nmero de folio y necesitas que ese nmero siempre sea de 5 dgitos, 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 travs del formato de celdas personalizado. Trabajaremos en ese ejemplo a continuacin.Formato personalizado a una celdaIntroduce el valor 1 en la celda A1. Haz clic derecho sobre la celda y selecciona la opcin Formato de celdas.

Se mostrar el cuadro de dilogo Formato de celdas donde debers hacer clic en la categora Personalizada.

Sobre la caja de texto Tipo introduce el valor 00000.

Al hacer clic sobre Aceptar la celda A1 que tena el valor 1 se mostrar en pantalla como 00001.

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

Recuerda que puedes copiar in formato de celdas con el botn Copiar formato que se encuentra en la ficha Inicio dentro del grupo Portapapeles.Cdigos de formato personalizadoExisten otras opciones de formato personalizado que puedes utilizar:ValorFormato personalizadoPresentacin

1000001

1N000N001

11.1000.00011.10

5538504000-00-00-0055-38-50-40

28/01/2011dddd-dd-mmmm-aaaaviernes-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 clculos con esa celda Excel tomar el valor 1.2345Sumar y contar celdas por color de formato condicionalHace algunos meses publique el artculo 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 travs del formato condicional.Las funciones creadas en ese artculo evalan 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 versin para almacenar el color de fondo de una celda. Por esa razn no es posible utilizar dichas funciones para evaluar colores establecidos a travs de una regla de formato condicional.As que hoy crearemos una nueva funcin VBA (UDF) para sumar y contar celdas por color de formato condicional. Pero antes de iniciar con el cdigo 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 coleccin FormatConditionsPrimero 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 coleccin FormatConditions en VBA la cual enumera todas las reglas de formato condicional aplicadas en un rango.Para comprender el funcionamiento de esta coleccin haremos un ejemplo. Considera la siguiente lista de nmeros 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 botn de comando ActiveX en la misma hoja y colocar el siguiente cdigo VBA en su evento Click:123Private Sub CommandButton1_Click()MsgBox Range("A1:A10").FormatConditions.CountEnd Sub

La nica lnea de cdigo ejecutada se encargar de mostrar el valor de la propiedad Count de la coleccin FormatConditions la cual contiene el recuento de las reglas de formato condicional para el rango indicado. Al hacer clic sobre el botn 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 botn de comando y el nmero mostrado en el mensaje habr aumentado debido a la nueva regla creada:

Es as como la coleccin FormatConditions nos permite obtener informacin sobre las reglas de formato condicional aplicadas a un rango de celdas. Ahora centraremos nuestra atencin a una propiedad especfica de dicha coleccin.La propiedad FormatCondition.Interior.ColorCada regla de formato condicional almacena el estilo que aplicar a las celdas que cumplan con las condiciones establecidas y especficamente el color de relleno se almacena en la propiedad Interior.Color. Para demostrar el valor de esta propiedad agregar un nuevo botn de comando con el siguiente cdigo:123456Private Sub CommandButton2_Click()For i = 1 To Range("A1:A10").FormatConditions.CountMsgBox "Regla " & i & vbLf & _"Color: " & Range("A1:A10").FormatConditions(i).Interior.ColorNext iEnd Sub

El cdigo anterior recorre todos los elementos de la coleccin FormatConditions y para cada elemento mostrar un mensaje con el nmero de regla y su color de relleno que est almacenado en la propiedad Interior.Color. Al pulsar el botn 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 ms sobre los colores en Excel consulta el artculo Evaluar el color de fondo de una celda.Los colores mostrados con el cdigo anterior son los colores pertenecientes a cada una de las reglas de formato condicional y no el color de una celda especfica. 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 celdaEl desafo ms 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 coleccin de formatos haciendo una evaluacin de cada regla para descubrir si est activa.12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849Function COLORFC(Celda As Range) As Long'Indicar si la relga de formato condicional est activaDim ReglaActiva As Boolean'Recorrer todas las reglas de formato condicional para la celda indicadaFor i = 1 To Celda.FormatConditions.Count'Evaluar la regla FormatConditions(i)With Celda.FormatConditions(i)'Si la regla est basada en el valor de la celdaIf .Type = xlCellValue Then'Identificar el operador de la regla y evaluar si est activaSelect Case .OperatorCase xlBetween: ReglaActiva = Celda.Value >= Evaluate(.Formula1) _And Celda.Value Evaluate(.Formula1)Case xlLess: ReglaActiva = Celda.Value < Evaluate(.Formula1)Case xlGreaterEqual: ReglaActiva = Celda.Value >= 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 adems de aplicar un formato especial a los datos.Cmo usar los filtros en ExcelPara filtrar la informacin 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, mostrarn una lista de valores nicos con una caja de seleccin a la izquierda de cada uno.

Una opcin que tenemos para filtrar los datos es elegir de manera individual aquellos valores que deseamos visualizar en pantalla. Tambin podemos utilizar la opcin (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 botn Aceptar se ocultarn 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. Adems, los nmeros de fila de Excel se muestran en un color diferente indicndonos que existen filas ocultas.Filtrar por varias columnasSi queremos segmentar an ms los datos mostrados en pantalla podemos filtrar por varias columnas. En el ejemplo anterior filtr las filas pertenecientes a Hugo, pero si adems necesito saber las que pertenecen a la regin Norte y Sur, entonces debo seleccionar dichas opciones dentro del filtro de la columna Regin:

Al aceptar estos cambios se mostrarn solamente las filas que cumplen ambos criterios. Observa que ambas columnas habrn 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 ms criterios de filtrado apliquemos mucha mayor ser la segmentacin de datos que obtendremos.Cmo quitar un filtro en ExcelPara quitar un filtro aplicado a una columna debemos hacer clic en la flecha del filtro y seleccionar la opcin Borrar filtro de Columna donde Columna es el nombre de la columna que hemos elegido. Esta accin eliminar el filtro de una sola columna, pero si tenemos filtros aplicados a varias columnas y deseamos eliminarlos todos con una sola accin, entonces debemos pulsar el comando Borrar que se encuentra en la ficha Datos > Ordenar y filtrar.

Filtrar en Excel buscando valoresYa 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 bsqueda. Por ejemplo, en la siguiente imagen he colocado la palabra este en el cuadro de bsqueda 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 fcilmente aquellos que deseamos seleccionar, podemos utilizar el cuadro de bsqueda para encontrar los valores que necesitamos. Tambin es posible utilizar caracteres comodines como el asterisco (*) o el smbolo de interrogacin (?) tal como si hiciramos una bsqueda aproximada en Excel de manera que podamos ampliar los resultados de bsqueda.Filtros de texto en ExcelAdems de las opciones ya mencionadas para filtrar en Excel, cuando en una columna se detecta el tipo de dato texto, se mostrar una opcin de men llamada Filtros de texto como la siguiente:

Al elegir cualquiera de estas opciones se mostrar un cuadro de dilogo que nos permitir configurar cada uno de los criterios disponibles. Por ejemplo, al elegir la opcin Comienza por se mostrar el siguiente cuadro de dilogo:

Si colocamos la letra a en el cuadro de texto junto a la opcin comienza por, entonces Excel mostrar solamente los elementos de la columna Vendedor que comiencen por la letra a.Filtros de nmero en ExcelDe manera similar, si Excel detecta que una columna contiene valores numricos, nos permitir utilizar filtros especficos 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 nmero para mostrar valores que sean mayores o iguales que otro o simplemente aquellos que son superiores al promedio.Filtros de fecha en ExcelLas fechas son el tipo de dato que ms opciones de filtrado nos proporcionan, tal como lo muestra la siguiente imagen:

Excel nos permitir filtrar las fechas por das especficos como hoy, maana o ayer e inclusive por perodos de tiempo ms largos como semanas, meses, trimestres o aos con tan solo seleccionar la opcin adecuada.Filtrar por color en ExcelNo podamos pasar por alto y dejar de hablar de la opcin de Filtrar por color que nos ofrece Excel. Para que esta opcin 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 opcin Filtrar por color y dentro de ella podr elegir alguno de los colores presentes en la columna.

Filtro avanzado en ExcelAunque los filtros en Excel nos ofrecen una amplia gama de posibilidades para segmentar la informacin, es posible realizar un filtrado mucho ms avanzado si utilizamos el Filtro avanzado. Si quieres leer un poco ms al respecto, te recomiendo leer el siguiente artculo: Filtro avanzado en Excel.Tablas en ExcelLas tablas no son ms 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 informacin contenida en ellas est relacionada. La primera fila de una tabla siempre contendr los encabezados de columna y el resto de las filas contendrn 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 ms en el anlisis de los datos.Cmo crear una tablaPara crear una tabla en Excel todo lo que debes hacer es seleccionar el rango de celdas que contiene los datos y pulsar el botn Tabla que se encuentra dentro de la ficha Insertar.

El uso de las tablas en Excel es altamente recomendable as que a continuacin mencionar 7 beneficios de utilizar las tablas de Excel.1. Cambiar fcilmente el estilo de una tablaExcel contiene algunos estilos de tabla predefinidos de los cuales podrs elegir alguno para aplicarlo a tu tabla. Solamente selecciona cualquier celda que se encuentra dentro de la tabla y cambia su estilo seleccionado la opcin adecuada dentro del grupo Estilos de tabla que se encuentra dentro de la ficha Diseo de las Herramientas de tabla.

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

3. Filtrar y ordenar datos de la tablaCada 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 tablaUna de las ventajas ms 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 automticamente al insertar nuevos datos en la tabla o al removerlos.5. Crear columnas calculadas fcilmenteUna 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 tablaSe pueden agregar totales a una tabla con tan solo marcar la caja de seleccin Fila de totales:

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

7. Imprimir una tablaPara imprimir una tabla debes seleccionar cualquiera de sus celdas y oprimir la combinacin de teclas CTRL + P y posteriormente, en la seccin de Configuracin, seleccionar la opcin Imprimir la tabla seleccionada.

Estas son solo algunas de las caractersticas y beneficios de las tablas en Excel que demuestran que son intuitivas, fciles de usar y que adems nos proveen de mucha funcionalidad sin tanta complejidad. La prxima vez que organices tus datos en Excel considera utilizar una tabla.Utilizando referencias absolutas y relativasEn 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 frmula a una nueva ubicacin seguir funcionando adecuadamente.Si tienes una frmula que hace referencia a otras celdas y copiamos esa frmula a otra ubicacin, Excel actualizar automticamente las referencias de la frmula para adecuarlas a la nueva ubicacin. 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 frmula de la celda C2 debe sumar los valores de A2 y B2. Si volvemos a copiar la frmula y pegarla en la celda C3 obtendremos la frmula: =A3 + B3.Referencias relativasCuando copiamos frmulas 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 ubicacin. Sin embargo, en algunas ocasiones no queremos tener este comportamiento sino que queremos hacer referencia siempre a una celda especfica sin importar que copiemos la frmula a otra ubicacin y para ello necesitamos utilizar las referencias absolutas.Referencias absolutasPara fijar la columna o fila de una referencia utilizamos el signo $ y lo colocamos antes de la letra de la columna o del nmero de fila de la referencia. As por ejemplo podemos tener una referencia absoluta que fija tanto la columna como la fila, por ejemplo: $A$1Referencias mixtasLas 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 mixtasUna manera muy sencilla de crear una referencia absoluta es editar la frmula 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 nmero de fila.Si pulsas dos veces la tecla F4 observars 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 removern todos los signos $ y tendremos de nuevo una referencia relativa.A continuacin una tabla que te ayudar a recordar este tipo de referencias.ReferenciaEjemploDescripcin

Relativa=A1La columna y la fila pueden cambiar al momento de copiar la frmula.

Absoluta=$A$1Ni la columna ni la fila pueden cambiar.

Mixta=$A1La columna no cambia, solamente la fila puede cambiar.

Mixta=A$1La fila no cambia, solamente la columna puede cambiar.

Bsquedas aproximadas en ExcelCuando tenemos grandes cantidades de datos podemos tener dificultades para encontrar la informacin que necesitamos. El cuadro de dilogo Buscar y remplazar tiene una caracterstica especial que muchos pasan por alto.Palabras diferentes por una sola letraTe 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 Vsquez o ser Vzquez?Cuando tienes duda sobre un carcter especfico dentro de una palabra puedes remplazarlo por un caracter comodn de manera que Excel busque tanto Vsquez como Vzquez. Este caracter comodn es el smbolo ? y para realizar la bsqueda simplemente debes sustituir la letra sobre la cual tienes duda con el smbolo ? de la siguiente manera:

Esta bsqueda encontrar tanto Vsquez como Vzquez y podrs entonces decidir cul de los dos resultados es el adecuado.Palabras diferentes por varias letrasSi el problema no es una letra sino que son varias letras entonces tenemos otra opcin que es utilizar el smbolo *. Con este caracter comodn podemos remplazar varias letras a la vez y de esta manera podramos buscar todas las palabras que terminan en cin de la siguiente manera:

El smbolo * le dice a Excel que no importan las letras que sean, ni la cantidad que sean, mientras tanto y sean precedidas por cin.El cuadro de dilogo Buscar y remplazar soporta el uso de dos caracteres comodines: ? remplaza un solo caracter. * remplaza cualquier nmero de caracteres.Los caracteres comodines pueden ser colocados en cualquier posicin dentro de una palabra: al inicio, en medio o al final, lo nico que harn ser sustituir una letra o un grupo de letras.Estos caracteres comodines tambin funcionan con valores. Por ejemplo, la bsqueda *5 devolver todas los valores numricos que terminen en 5. Por el contrario, una bsqueda como 8?3 encontrar todos los nmeros de tres dgitos que empiecen con 8 y terminen con 3.Buscar un caracter comodnAhora la pregunta natural es Cmo hago para encontrar una palabra que contiene un carcter comodn?Supongamos que necesito encontrar una celda cuyo valor es *ADA*. Si coloco esta frase en el cuadro de dilogo de bsqueda traer como resultado todas las palabras que contengan las letras ADA.

Para indicar a Excel que deseo buscar el smbolo * y que no lo deseo utilizar como caracter comodn, debo anteponer el smbolo ~ (tilde). De esta manera, para encontrar las celdas que tengan el valor *ADA* debo realizar la bsqueda de la siguiente forma:

De igual manera podemos anteponer el smbolo ~ si queremos buscar un texto que contenga el smbolo ?.Buscar informacinEl 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 bsqueda.

Sigue estos pasos para encontrar algn dato especfico. Haz clic en la ficha Inicio y pulsa el botn Buscar y seleccionar y elige la opcin Buscar.

Bsqueda rpida en ExcelDentro del cuadro de texto Buscar especifica el texto que deseas encontrar y pulsa el botn 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 bsqueda.Opciones de bsqueda en ExcelSi deseas proporcionar criterios de bsqueda adicionales, entonces debes pulsar el botn Opciones y el cuadro de dilogo se expandir permitindote ser ms especfico en la bsqueda:

Dentro de: Nos permite indicar si la bsqueda ser dentro de la hoja o en el libro completo. Buscar: Podemos elegir una bsqueda por filas o por columnas. Buscar dentro de: Selecciona si deseas buscar en las frmulas de cada celda o en sus valores que es lo que vemos desplegado en cada celda. Tambin puedes realizar una bsqueda en los comentarios de las celdas. Coincidir maysculas y minsculas: Marca esta opcin si deseas que la bsqueda concuerde con maysculas y minsculas. Coincidir con el contenido de toda la celda: Excel encontrar aquellas celdas que coinciden en su totalidad con el texto buscado.Finalmente pulsa el botn Buscar siguiente para encontrar la siguiente coincidencia o pulsa el botn Buscar todos para obtener una lista completa de los resultados de la bsqueda.Caracteres comodn en ExcelOpcionalmente puedes utilizar caracteres comodn en tu bsqueda. Utiliza el smbolo ? (signo de interrogacin) para indicar un solo carcter desconocido. Por ejemplo, al especificar el trmino de bsqueda Gr?cia se encontrarn las palabras Gracia y Grecia.

Otro caracter comodn que podemos utilizar es el smbolo * (asterisco) para indicar varios carateres desconocidos. Por ejemplo, al especificar una bsqueda como ante* se encontrarn todas las palabras que comiencen con ante sin importar lo que siga. Esto retornar resultados de bsqueda como anterior, antebrazo, antecedente, anteriormente, antesala, etc.Filtro avanzadoEn Excel puedes realizar un filtrado de datos totalmente personalizado proporcionando los criterios que deseas aplicar a la informacin. Este tipo de filtrado es conocido como Filtro avanzado y en esta ocasin te mostrar cmo 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 ubicacin no es de importancia.Aplicar un filtro avanzado a los datosAntes de aplicar el filtro avanzado debo seleccionar la tabla de datos (A4:D13) y posteriormente ir a la ficha Datos y pulsar el botn Avanzadas que se encuentra en el grupo Ordenar y filtrar. Se mostrar el cuadro de dilogo Filtro avanzado.

Observa cmo 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 botn Aceptar para aplicar el filtro.

El resultado es precisamente lo que necesito, as que el filtrado avanzado ha sido exitoso. Pero ahora tengo un desafo mayor Cmo podra hacer para especificar condiciones para una segunda columna? Supongamos que deseo filtrar la informacin de las personas que tienen apellido Hernndez y que adems pertenecen al departamento de Finanzas. Para filtrar los datos por estos criterios debo agregar dichas condiciones en otra celda.

Observa cmo 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 tambin 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 condicin por columna, pero ahora deseo agregar a los resultados del filtro la informacin del departamento de Informtica. Para ello solamente agregar una fila adicional al rango de criterios de la siguiente manera.

Al aceptar los cambios Excel filtrar la informacin de los empleados de Finanzas con apellido Hernndez y adems mostrar la informacin de los empleados del departamento de Informtica sin importar el apellido que tengan.

Como podrs observar, es factible especificar una condicin por cada fila del rango de criterios. De esta manera puedes crear un filtro avanzado en Excel.Filtrar registros de una tablaExiste una caracterstica 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 slo estamos mostrando un subconjunto de los datos y de esa manera tenemos una mejor visibildiad de los mismos para realizar un buen anlisis.Filtrar datos en ExcelEl 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 habas 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 seleccin de la opcin (Seleccionar todo). Esto limpir todas las cajas de seleccin permitindote seleccionar slamente las opciones que ests interesado ver:

Filtrar por varios criterios o columnasPuedes seleccionar mltiples cajas de seleccin para filtrar por varios criterios. Al hacer clic en el botn Aceptar Excel desplegar slamente los datos que cumplen son la seleccin realizada. Tambin puedes aplicar filtros en varias columnas a la vez, solamente repite los pasos que acabamos de revisar para cualquier otra columna.Podrs notar que una columna tiene un filtro aplicado porque el cono de flecha de filtrado es reemplazado por un cono de filtro.

Remover un filtroPara remover un filtro puedes hacer clic sobre el cno de filtro de la columna deseada y seleccionar la opcin 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 opcin Borrar

Filtrar nmeros en ExcelExcel provee mltiples opciones para filtrar datos numricos como filtrar los valores superiores o inferiores, filtrar los valores que son mayores o menores a un nmero especfico, etc.Ya hemos visto cmo filtrar registros de una tabla y cmo Excel coloca automticamente flechas de filtrado para cada columna.Filtros de nmero en ExcelPero existe una opcin de men especial para las columnas que son numricas y que revisaremos a continuacin.Haz clic sobre el icono de filtrado de una columna de datos numricos y selecciona la opcin de men Filtros de nmero. Observa las opciones disponibles:

Primero probaremos la opcin Diez mejores. Esta opcin muestra el cuadro de dilogo Autofiltro de las diez mejores

En la primera lista de seleccin podrs decidir si deseas obtener los valores superiores o inferiores. En la segunda opcin puedes especificar el nmero de elementos que deseas observar (desde 1 hasta 500). En la ltima lista de seleccin eliges si deseas filtrar los elementos o filtrar por su porcentaje. Un ejemplo de porcentaje podra 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 nmero es filtrar por Superior del promedio o Inferior del promedio. Estas opciones son directas y en cuanto las selecciones Excel aplicar el filtro automticamente.Por ltimo podrs observar que el resto de los filtros posibles se puede realizar a travs del cuadro de dilogo Autofiltro personalizado

A travs de este cuadro de dilogo podrs hacer comparaciones como: Igual a, mayor que, menor que, entre, etc. Slamente debes especificar la condicin que necesitas y podrs filtrar nmeros en Excel.Cmo restar en ExcelCuando comenzamos a utilizar las hojas de clculo es normal preguntarnos cmo realizar las operaciones aritmticas ms elementales como sumar y multiplicar, pero estoy seguro que cuando eras principiante en Excel te preguntaste en ms de una ocasin cmo restar en Excel.La funcin RESTA en Excel?Una de las primeras funciones que aprendemos a utilizar en Excel es la funcin SUMA, as que es prcticamente inevitable razonar que: Si existe una funcin para sumar, seguramente debe existir una funcin para restar. Sin embargo, despus de hacer una bsqueda exhaustiva, todo usuario que comienza en Excel se entera de que la funcin RESTA no existe.A muchos les parece extrao que no exista una funcin para restar en Excel, pero todo comienza a hacer ms sentido cuando nos damos cuenta de que podemos restar nmeros utilizando la funcin SUMA. Lo nico que debemos hacer es utilizar valores negativos y dejar que la funcin se encargue de realizar la operacin aritmtica. Considera como ejemplo la siguiente frmula:=SUMA(10, -3)En esta frmula he colocado el valor -3 como segundo argumento de la funcin SUMA y por lo tanto al realizar el clculo obtendr el resultado de la operacin diez menos tres:

Ya que la funcin SUMA nos permite indicar valores numricos o referencias, entonces tambin 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 funcin SUMA en realidad estar haciendo una resta de dichos nmeros. De esta manera podemos concluir que al no existir la funcin RESTA en Excel, podemos utilizar la funcin SUMA siempre y cuando indiquemos los valores negativos.El operador aritmtico para la restaUna segunda alternativa que tenemos para restar en Excel es utilizar directamente el operador aritmtico para la resta que es el guion medio (-). Este operador lo podemos utilizar ya sea con valores numricos o con referencias de celdas. Observa cmo 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 funcin SUMA, en esta ocasin 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 frmulas como la siguiente para mezclar operaciones de suma y resta:=A1+B1-C1-D1Tambin es posible mezclar el operador de resta con la funcin SUMA para restar un conjunto de nmeros 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 ms sobre los operadores aritmticos en Excel consulta el artculo: Tipos de operadores en Excel.Restar con la funcin IM.SUSTRLa funcin IM.SUSTR est presente desde la versin de Excel 2007 y es utilizada para restar dos nmeros complejos, pero la verdad es que son muy pocas las personas que hacen operaciones con este tipo de nmeros.Lo nico que debemos saber es que un nmero complejo est formado por un nmero real y un nmero imaginario. Los nmeros que hemos utilizado hasta ahora en este artculo son reales, as que esta funcin puede ayudarnos perfectamente a restar en Excel, pero debemos recordar que solo nos permitir restar dos nmeros a la vez. Observa la siguiente imagen donde hago uso de esta funcin:

La celda C1 muestra correctamente el resultado de la operacin 10 menos 3. La nica peculiaridad del resultado es que el nmero 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 nmeros complejos son representados en Excel como cadenas de texto, as que debes tener eso en cuenta si alguna vez decides utilizar la funcin IM.SUSTR para hacer una resta en Excel.Ahora ya conoces los diferentes mtodos que tenemos para restar en Excel. El ms utilizado de todos es el operador de resta y probablemente en alguna ocasin te encontrars a la funcin SUMA haciendo clculos con nmeros negativos pero lo que es menos usual, pero posible, es el uso de la funcin IM.SUSTR para hacer restas en Excel.Funcin Excel SUMACategora: Matemticas y trigonomtricasNombre en ingls: SUMQu hace?Devuelve el subtotal de una lista de valores aplicando la operacin indicada.SintaxisSUMA(nmero1, [nmero2], ) nmero1 (Obligatorio): Primer nmero (o rango de celdas) a considerar en la suma. nmero2 (Opcional): Nmeros (o rangos de celdas) adicionales a sumar. Se pueden especificar hasta 255 nmeros adicionales.EjemplosSUMA(1,3,4) = 8SUMA(A1:A5) = Suma de los valores en el rango A1:A5Sumar celdas de distintas hojasPara sumar celdas de distintas hojas en Excel debemos poner especial atencin a las referencias de las celdas que vamos a utilizar de manera que la funcin SUMA devuelva los resultados adecuados.Referencias a celdas de otras hojasEn 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 exclamacin (!) y posteriormente la direccin de la celda. Por ejemplo: Hoja2!A1Sumar celdas de diferentes hojasSi quiero sumar todas las celdas A1 de la Hoja1, Hoja2 y Hoja3 puedo tener una frmula como la siguiente:

Con esta frmula hemos sumado los valores de todas las celdas A1 de la Hoja1 a la Hoja3. De esta manera podemos especificar la direccin exacta de todas las celdas que deseamos sumar.Sumar la misma celda en distintas hojasEn el ejemplo anterior sumamos la celda A1 de todas las hojas y especificamos las tres direcciones de la celda A1. Sin embargo existe un mtodo abreviado para estos casos. Cuando necesitamos sumar la misma celda en varias hojas podemos utilizar el siguiente mtodo abreviado en la frmula:

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 exclamacin (!) y la direccin de la celda que se sumar en cada una de las hojas.Este mtodo es muy til cuando tienes que sumar la misma celda de muchas hojas porque evita introducir la direccin de cada una de las celdas. Por ejemplo, si deseamos sumar la celda B5 de las Hoja1 hasta la Hoja15 la frmula ser: SUMA(Hoja1:Hoja15!B5)Sumar un rango en distintas hojasAs como podemos sumar una misma celda en diferentes hojas, tambin podemos sumar un rango en distintas hojas. La nomenclatura es similar.

Esta frmula regresar la suma del rango A1:A3 en todas las hojas desde la Hoja1 hasta la Hoja3.Las tcnicas 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 celdasEn esta ocasin mostrar cmo hacer una referencia a otra celda al escribir una frmula. 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 referenciasAl utilizar referencias dentro de una frmula ganas dinamismo y flexibilidad ya que los valores utilizados dentro de la frmula estn almacenados dentro de alguna celda. Si se cambia el valor constante de la celda, entonces el resultado de la frmula asociada se actualiza al mismo tiempo.Referencias a celdas en la misma hojaEste es el tipo de referencia ms conocido y utilizado. Para crear una referencia a una celda en la misma hoja es suficiente con especificar su direccin. Observa el siguiente ejemplo de la celda A1 haciendo referencia a la suma de las celdas B1 y C1.

Referencias a celdas en otra hojaSi 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 exclamacin (!) y posteriormente la direccin 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 exclamacin, por ejemplo:='Reporte de ventas'!A1Referencias a celdas en otro libroTambin 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 debern 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 acumuladasLas ventas acumuladas del ao no son ms que la suma de las ventas de los meses anteriores hasta la fecha actual, es decir, una suma acumulada. Un concepto bsico y muy fcil de implementar en Excel.Sumas acumuladas en ExcelSupongamos los siguientes datos de ventas mensuales:

Insertar una nueva columna y har uso de la funcin SUMApara crear la siguiente frmula:

Observa detenidamente el rango de la funcin SUMA, porque lo estoy escribiendo de manera que al copiar la frmula hacia abajo obtenga la suma acumulada automticamente.En primer lugar estoy indicando a la funcin que sume un rango que siempre comenzar en la celda B2 y terminar en la celda actual. Es por eso que he colocado el smbolo $ junto al nmero 2 para fijar la fila y que no se auto incremente aun cuando copie la frmula hacia abajo. Para el caso especfico de la celda C2 el rango inicia y termina en la misma celda, pero al copiar la frmula hacia abajo obtendremos el resultado esperado:

La celda C13 muestra claramente cmo la frmula 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 estn 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 ao. Es de esperarse que la ltima celda de la columna Acumulado muestre la suma de todos los meses del ao.Lo ms 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 acumuladoAhora comprueba que has aprendido bien el concepto y agrega una nueva columna para mostrar el promedio de ventas acumulado:

Tip: Debes utilizar la funcin PROMEDIOFuncin Excel PROMEDIOCategora: EstadsticasNombre en ingls: AVERAGEQu hace?Obtiene el promedio de los nmeros especificados.SintaxisPROMEDIO(nmero1, [nmero2], ) nmero1 (obligatorio): Este parmetro puede ser un nmero tambien puede ser un rango de celdas que contiene el conjunto de nmeros a promediar. nmero2 (opcional): A partir del segundo nmero los parmetros son opcionales. De igual manera puedes colocar un nmero u otro rango de celdas de donde la funcin obtendr ms valores a promediar.EjemplosPROMEDIO(73, 52, 87, 13, 39) = 52.8Analizando nmeros con ExcelExcel 2010 incluye funciones estadsticas que nos ayudan a analizar los nmeros contenidos en nuestras hojas de clculo. Funciones como PROMEDIO, MAX, MIN son de las funciones estadsticas ms utilizadas en Excel.Las tres funciones tienen la misma sintaxis, la cual especifica que los parmetros de la funcin deben ser la lista de nmeros que se desea evaluar. Cada una de estas funciones acepta como mximo 30 nmeros en su lista de parmetros aunque se puede hacer uso de rangos para evitar cualquier complicacin por esta limitante.A continuacin un ejemplo sobre cmo utilizar estas funciones estadsticas.

Las funciones MAX y MIN obtienen los valores mximos y mnimos del rango especificado.La funcin PROMEDIO calcula la media aritmtica, es decir, suma todos los valores de las celdas especificadas y divide ese valor entre el nmero de elementos sumados. La funcin PROMEDIO es equivalente a la siguiente frmula:=SUMA(B1:B10)/CONTAR(B1:B10)

La funcin SUMA obtiene el total de los valores y la funcin CONTAR, que es otra funcin estadstica, determina la cantidad de valores en el rango.Con estas funciones podrs realizar un anlisis bsico, pero importante, de los nmeros contenidos en tus hojas de Excel.Funcin Excel MAXCategora: EstadsticasNombre en ingls: MAXQu hace?Encuentra el valor mximo de una lista de nmeros.SintaxisMAX(nmero1, [nmero2], ) nmero1 (obligatorio): El primero de los valores a evaluar. Tambin puede ser un rango. nmero2 (opcional): A partir del segundo nmero los parmetros son opcionales. De igual manera puedes colocar un nmero o un rango de celdas.EjemplosMAX(1, 2, 3) = 3La funcin MAX en ExcelLa funcin MAX en Excel es de gran ayuda siempre que necesitemos obtener el valor mximo de un conjunto de valores que puede ser una lista de nmeros ubicados en uno o varios rangos de nuestra hoja de Excel.Sintaxis de la funcin MAX

Nmero1 (obligatorio): El primer nmero de la lista o el rango de celdas que contiene los nmeros a evaluar. Nmero2 (opcional): El segundo nmero de la lista o un rango de celdas con nmeros adicionales a incluir en la evaluacin.Podemos especificar un mximo de 255 nmeros en la funcin MAX en caso de enlistarlos directamente como argumentos pero podemos especificar rangos de celdas como argumentos de la funcin que a su vez pueden contener varios nmeros que sern evaluados.La funcin MAX considera nmeros negativos y el cero, por lo que dentro de una lista de nmeros negativos el valor mximo podra ser el cero. Observa el siguiente ejemplo:

Ejemplos de la funcin MAXEn el ejemplo anterior pudiste observar que podemos especificar los nmeros directamente en la funcin MAX, sin embargo de esta manera solamente podramos tener hasta 255 nmeros. Para aumentar la cantidad de nmeros que podemos incluir debemos colocarlos en un rango de celdas y especificar dicho rango como argumento de la funcin.

De la misma manera podemos indicar varios rangos para obtener el valor mximo de todos el