10 Fórmulas de Excel para ser Más ProductivoNOVEMBER 13, 2013 BY PABLO FARÍAS 1 COMMENT

No tiene sentido pasar horas y horas frente a una planilla repitiendo tareas tediosas si puedes ahorrarte este dolor con sólo algunos tips. Las fórmulas de Excel que veremos a continuación te ayudarán a ser más productivo y efectivo en tu trabajo.

*** Excel desde Cero – Nivel Básico e Intermedio con 75% de descuento!

1. SUMAEsta es una fórmula elemental que no puedes no conocer. Si no la has usado hasta ahora creo que ya puedes darte por satisfecho porque verás cómo te ahorra tiempo.

SUMA te permite, tal como su nombre lo indica, sumar un grupo de celdas, o incluso filas y columnas enteras!

=SUMA(A1:A10),  =SUMA(A:A),  =SUMA(A1:Z10)

2. CONTAREsta fórmula te permite contar la cantidad de celdas en una selección que tienen números. Las celdas vacías o con texto son ignoradas. Muchas veces tenemos

planillas con datos incompletos y se necesita contar solamente los datos y no el total de celdas. En esos casos sirve muchísimo!

=CONTAR(A1:B10), =CONTAR(A:A)

3. CONTARASimilar a CONTAR pero cuenta no solamente las celdas con números sino todas las celdas no vacías (es decir, pueden contener también texto).

=CONTARA()

4. CONTAR.SIEsta fórmula permite contar elementos que cumplen un cierto criterio. Este criterio puede ser por ejemplo que las celdas sean iguales a un cierto valor, o que sean mayor/menos que un valor, o que sean iguales que algún texto, etc. Veamos algunos ejemplos:

Cuenta la cantidad de celdas con un número mayor que cero:

=CONTAR.SI(“A1:B10”, “>0”)

Cuenta la cantidad de celdas con la palabra “azul”:

=CONTAR.SI(“A1:B10”, “azul”)

5. SIEsta fórmula te permite tener un valor u otro en la celda dependiendo de una cierta condición. Las condiciones son similares a lo que se puede hacer con la fórmula CONTAR.SI. Por ejemplo, podemos tener las notas de un alumno en un listado, y si el promedio es mayor o menor que un cierto límite, podemos escribir “aprobado” o “reprobado” para el alumno:

=SI(A1 > 0, “VALOR POSITIVO”, “VALOR NEGATIVO”)

6. BUSCARVPermite buscar un valor específico en la primera columna de una tabla, y retornar el valor en la misma fila para otra columna diferente. Esto suena algo abstracto así que lo voy a ilustrar con un ejemplo.

Tenemos un listado con alumnos y sus notas en cada evaluación. Queremos obtener la nota que el alumno “PEDRO” obtuvo en la tercera evaluación, entonces utilizamos la siguiente fórmula:

=BUSCARV(“PEDRO”, A1:Z100, 10, FALSO)

Notar que el “FALSO” se ingresa para indicar que queremos una búsqueda exacta, es decir sólo queremos el valor para la entrada con índice PEDRO. Notas también que la primera columna que se cuenta es la primera, por eso la que buscamos (la tercera evaluación) es la columna número 4.

7. BUSCARHSimilar a BUSCARV pero utilizando la misma analogía en sentido horizontal. Es decir buscamos un valor en la primera celda de la tabla, y luego obtenemos el valor de la celda ubicada en la misma columna que nuestro índice, con el número de fila ingresado:

Ejemplo, tenemos las ventas de distintos productos, y queremos saber el total que se vendió para el producto “jabón” en el cuarto mes:

=BUSCARH(“JABON”, A1:C10, 4, FALSO)

El uso de “FALSO” es igual al caso anterior. La cuenta de las filas parte desde la fila con los encabezados, por lo que el tercer mes corresponde al índice 4.

8. SUMAR.SI, PROMEDIO SIEstas funciones nos permiten realizar una suma o un promedio solamente para los valore que cumplen una condición.

Las condiciones que se pueden ingresar siguen el mismo formato que las condiciones utilizadas en CONTAR.SI y el SI.

=SUMAR.SI(A1:Z1, “>0”)

9. CONCATENAREsta fórmula nos permite unir dos o más cadenas de texto en una sola celda. Por ejemplo tenemos una columna “nombres” y otra “apellidos”, y queremos crear una tercera columna llamada “nombre completo”. En este caso queremos unir el nombre con el apellido, y además poner un espacio entremedio. Se haría de la siguiente manera:

=CONCATENAR(“A1”, “ “, “B1”)

10. ALEATORIO.ENTREMuchas veces se necesita llenar una tabla con números distintos de manera rápida, solamente para mostrar algo y nos pasamos un buen rato escribiendo número uno a uno y tratando de que sean distintos entre sí.

Bueno, ALEATORIO.ENTRE nos permitegenerar un número aleatorio en un rango que nosotros especificamos:

=ALEATORIO.ENTRE(100, 1000)

CÓMO VOLVERTE UN EXPERTO EN MICROSOFT EXCELEspero que este tutorial te haya servido para mejorar tus habilidades de Excel. Si quieres aprender más trucos con Microsoft Excel puedes revisar mi curso online que cubre el uso de este software desde cero y abarcando temas más avanzados también:

*** Excel desde Cero – Nivel Básico e Intermedio con 75% de descuento!

Este curso incluye dos cursos en uno, un curso básico y uno intermedio. En elNivel Básico veremos, entre otras cosas:

Conceptos básicos desde cero: hojas de cálculo, filas, columnas, celdas. Seleccionar, copiar, pegar, cortar, todo desde cero, sin requerir de

conocimientos previos Crear tablas con bordes, colores y distintos tipos de letra. Insertar imágenes y formas. Realizar cálculos, obtener totales, promedios y desviación estándar. Crear gráficos de barra y gráficos circulares.

En el Nivel Intermedio veremos algunos de los siguientes temas:

Cómo crear tablas dinámicas, qué son y para qué sirven, todo desde cero! Utilización de control de cambios y comentarios para trabajar en equipo de

manera efectiva y eficiente. Ordenar y filtrar tablas con muchos datos. Cómo utilizar el formato condicional y los mini-gráficos para crear reportes

profesionales. Protección de contenido con contraseña. Validación de datos, desde cero.

BONUS

Antes de despedirme me gustaría incluir como bonus algunas lecciones de mi curso Excel desde Cero – Nivel Básico e Intermedio.

Bonus 1: Formato Condicional

Acceder a la lección siguiente

Bonus 2: Minigráficos

Acceder a la lección siguiente

Cálculos con fechas y horas en ExcelPublicado el 12 agosto, 2011 por josenrique

Últimamente me plantean con demasiada frecuencia dudas y problemas sobre el manejo de fechas y horas en Excel. Éste es un tema en el que a veces empleamos fórmulas demasiado complicadas por no entender bien desde el principio cómo interpreta y maneja Excel este tipo de información. Por todo ello he decidido hacer este pequeño tutorial. Te aconsejo que tengas abierto Excel y vayas probando mientras lees esta guía.

Lo primero que debes saber es que en el tema de las fechas y horas hay que estar jugando constantemente con el formato de la celda. A veces un simple cambio de formato te da la solución que necesitabas de forma inesperada. Cuando escribes en una celda de Excel 6/8/11 se le asigna de forma automática a esa celda el formato de fecha (dd/mm/aaaa) pero debes saber que afectos de cálculos esa fecha tiene asignado un número (40761). Ese número corresponde con un contador que empieza con la fecha 1/1/1900, a esa fecha se le asigna el número 1, al 2/1/1900 se le asigna el número 2, y así sucesivamente. De manera que es muy importante que entiendas que para Excel 6/8/11 es lo mismo que 40761 es cuestión de elegir el formato (fecha o número) que mejor se adapte a lo que necesitamos.

Por ejemplo, para calcular cuántos días hay entre dos fechas sólo tendrás que restar la fecha mayor menos la menor y si el resultado sale en el formato inadecuado (fecha) sólo le tendrás que cambiar a formato número.

Prueba el siguiente ejemplo:

Recuerda que al resultado de la celda C3 seguramente le tendrás que cambiar el formato a número.

¿Cómo es posible que Excel reste fechas? Es debido a que realmente resta números, acuérdate que “detrás” de cada fecha hay un número así que si cambias el formato de la celda A3 y B3 el resultado es el siguiente:

En otras ocasiones te interesará que el resultado lo de en formato de fecha. Si nos dan una fecha de solicitud y unos días máximos de resolución nos interesará calcular la fecha límite de resolución:

Es como si a 40761 le sumaras 15, el resultado es 40776 que al pasarlo a formato de fecha da 21/8/11

Ya has visto que cuando restas dos fechas calculas los días que han pasado entre una y otra. Pero, claro, hablamos de días naturales ¿y

los días laborables?  Para ello existe la función =DIAS.LAB(Fecha inicio; Fecha final; Festivos). Para poder usar esta función en Excel 2003 hay que activar un complemento. Puedes entrar en Herramientas, Complementos y marcar Herramientas para análisis, de esta manera podrás usar esta función y muchas más que no estaban instaladas por defecto.En Excel 2007 y 2010 viene “de fábrica”.Prueba el siguiente ejemplo:

La función DIAS.LAB calcula los días transcurridos entre dos fechas quitando los fines de semana (sábados y domingos) y los festivos indicados en el rango del tercer argumento.

Aquí hay diferencia de opiniones porque hay quien dice que el sábado es laborable y hay quien dice que el fin de semana entero es festivo, la función DIAS.LAB considera esto último.

Para solucionar este dilema la versión 2010 de Excel ha sacado la función =DIAS.LAB.INTL(fecha_inicial; fecha_final; fin_de_semana; festivos) donde en el argumento “fin de semana” se puede elegir qué consideramos como festivo siguiendo los siguientes códigos:

Pasemos a las horas y minutos.

Cuando en una celda escribes 9:54 Excel le asigna el formato de horas y minutos h:mm y cuando escribes 9:54:25 se le asigna formato de horas, minutos y segundos h:mm:ss

Pero “detrás” de ese formato también hay un número. Las horas, minutos y segundos tienen una equivalencia numérica que es con la que Excel opera. ¿Cuál es esa equivalencia? Pues resulta que cualquier horario que escribas tiene un valor numérico de cero y unos decimales que representan la parte proporcional de un día de 24 horas. Por ejemplo las 6 de la mañana (6:00) equivale a un cuarto de día, es decir, 0,25. Las 12:00 sería 0,5.

Escribe en cualquier celda de Excel 8:15 y cuando le cambies el formato a número verás que sale 0,34375. Ve haciendo pruebas hasta que lo asimiles. Las 24:00 será el número 1.

Por eso también puedes restar horas minuto y segundos porque realmente lo estás haciendo con números.

Hasta aquí es bastante básico. Los problemas se plantean cuando hablamos de horarios de diferentes días. Debes saber que en la misma celda puedes escribir una fecha y un horario dejando un espacio entre ambos. Por ejemplo para indicar el día 6 de agosto de 2011 a las 5 y 20 de la tarde lo puedes expresar en una celda como 6/8/11 17:20, prueba a escribirlo en una celda vacía de Excel. Si entras a ver el formato de la celda verás dd/mm/aaaa hh:mm.En este caso el valor numérico que Excel le asigna es un número con decimales. La parte entera es la que se le asigna a la fecha y la parte decimal es la equivalente a la hora. Si le cambias el formato a la fecha anterior y la muestras como número aparecerá la cantidad 40761,72222. Es la suma de 40761 de la fecha y de 0,72222 de la hora. Incluso podrías sumar una celda que contenga una fecha con una celda que contenga un horario, eso sí, las tres celdas tienen formato diferente.

Una vez que comprendemos la manera en que Excel interpreta la información de fechas y horas estamos en disposición de afrontar cualquier cálculo. Por ejemplo si tienes diferentes fechas y horas, ¿Cómo calcular el tiempo que pasan entre ellas?, es decir, ¿Cómo calcular cuántos días, horas y minutos hay entre dos fechas?

Primero te pongo un ejemplo en el que tienes en la misma celda la fecha y la hora:

Si te fijas en las fechas a la ligera dirías que han pasado 3 días sin embargo si compruebas con detenimiento las horas falta 1 hora y 5 minutos para completarse el tercer día.¿Cómo es posible que se resuelva así de fácil? Te cuento. El valor real de la celda A3 es 40762,4375 (prueba a cambiar el formato de la celda A3 a numérico) y el valor real de la celda B3 es 40765,39236. Recuerda que la parte entera representa el valor numérico de la fecha y la parte decimal es equivalente a la hora. Si restas esos dos valores da 2,954861111. La parte entera es 2. La función RESIDUO se queda con la parte decimal (0,954861111), que al darle formato de horas y minutos da el resultado que esperábamos.

En ocasiones te encontrarás con que la fecha y la hora están en celdas diferentes:

En este caso lo que debes hacer (como puedes comprobar en la imagen) es sumar la fecha y la hora antes de hacer las restas, es decir, sumas 40762 (A4) más 0,4375 (B4) y da 40762,4375 que es la cifra con la que partíamos en el ejemplo anterior.

Un matiz muy importante a tener en cuenta es que cuando escribes en una celda 13:25 estás indicando a Excel que son las 13:25 en el reloj. Cuando insertas en una celda 13:25:40 significa que son las 13 horas, 25 minutos y 40 segundos.

A veces no querrás indicarle a Excel una hora de reloj sino que ha pasado un tiempo determinado. No es lo mismo decir “son las 13 horas y 25 minutos” que “han pasado 13 horas y 25 minutos”. Por ejemplo en el primer caso no tendría sentido escribir 43:50 en una celda de Excel, esa hora no existe, pero en el segundo caso sí.

Para que Excel lo entienda en un sentido o en el otro el truco está en el formato de la celda. El formato h:mm indica  hora de reloj y el formato [h]:mm indica tiempo. De esta manera podrás poner en Excel 43:50 siempre y cuando el formato de esa celda sea [h]:mm

Aquí tienes un ejemplo:

Si hubieras sumado la columna D sin más no hubiera salido ese resultado pero con sólo cambiar el formato a [h]:mm ya lo tenemos. Por eso te decía al principio de este tutorial que con el tema de las fechas y las horas un simple cambio de formato puede ser la solución.

El valor real de la celda D10 es 1,431944444. Cada vez que las horas superen 24 se le suma 1 a la parte entera y el resto es la parte decimal. Por ejemplo 50:25 han pasado dos días completos y 2 horas y 25 minutos. Por eso el valor real de 50:25 sería 2,100694444. El 2 indica los días y el 0,100694444 sería la parte proporcional de día que queda, que pasado a formato de horas y minutos sería las 2:25 que quedaban.

Por ejemplo, partiendo de un tiempo hay que calcular cuántos días y horas son:

¡Qué simple! ¿Verdad?

Me gustaría contarte la función HORA(), MINUTO() Y SEGUNDO(). Estas funciones extraen de una hora (h:mm:ss) las horas, los minutos y los segundos respectivamente.

Por ejemplo si en la celda A1 hay escrito 19:25:30 la función =HORA(A1) dará 19, la función =MINUTO(A1) dará 25 y la función =SEGUNDO(A1) devolverá 30.Esto te resultará muy útil en aquellos casos en los que hay que pasarlo todo a minutos para multiplicar por algún importe en concreto. Por ejemplo:

Te explico la fórmula de la celda D4. Por un lado sacas las horas de la celda C4 y las multiplicas por 60 para pasarlo a minutos HORA(C4)*60, por otro lado extraes los minutos con MINUTO(C4) y por último calculas cuantos minutos son los 20 segundos de la celda C4 con SEGUNDO(C4)/60. Todo eso sumado nos dice cuantos minutos son 5 horas, 44 minutos y 20 segundos. Nos quedaba por pasar a minutos los 10 días con B4*24*60 que sumado a lo anterior da los minutos totales:

Creo que con estas técnicas serás capaz de resolver la mayoría de los problemas que se te planteen en Excel con el tema de las fechas y las horas, sobre todo es importante no perder de vista el valor real que almacena la celda y jugar con el formato.

Espero que te sirva y buena suerte con las fechas,

José Enrique Martínez

Recuerda que puedes hacer uso de este y de todo el material de este blog de forma gratuita siempre que no te lucres con él.

Suscríbete al blog si quieres tener noticias mías por correo cada vez que publique un nuevo tutorial, manual, artículo, truco, ejercicio o resuelva una duda.

Comparte esto:

Facebook 17

Twitter 6

Correo electrónico

Imprimir

Esta entrada fue publicada en Tutoriales y etiquetada excel, fechas, formatos, horas, laborables. Guarda el enlace permanente.

← Saludo y tratamiento en función del género en combinación de correspondencia

Gráficos de Excel con imágenes o formas →

65 respuestas a Cálculos con fechas y horas en Excel

1. Mª Carmen dijo:

13 agosto, 2011 en 10:54

Me ha resultado muy útil la explicación. Gracias!

2. Laura dijo:

14 agosto, 2011 en 14:25

Muy interesante……gracias.

3. Carmen Pantoja dijo:

15 agosto, 2011 en 18:35

Muy ilustrativo el atículo y muy útil, ya que se desconcen cosas básicas como las expuestas que son sin embargo

esenciales saber y comprender para afrontar ejercicios de mayor dificultad, y entender lo que se está haciendo de paso,

y además siguiendo tu línea de explicar de manera sencilla y clara, para que llegue a todos con más o menos idea de

este mundo tan complejo como es Excel.

4. Yolanda dijo:

16 agosto, 2011 en 20:52

Muchísimasssss gracias!! Te explicas de maravilla, xD.

5. SANDRA dijo:

17 octubre, 2011 en 19:55

MUY BUENA EXPLICACIÓN, ME FUE DE MUCHA AYUDA PORQUE FUE FÁCIL DE APLICAR!! GRACIAS

6. lucero dijo:

23 noviembre, 2011 en 16:22

excel_ente aporte muy didactico y completo este manual me ilustrado lo que habia buscado por varias horas

gracias……………

7. Yigo dijo:

28 noviembre, 2011 en 17:53

Hola, sobre el tiempo que ha pasado excel no me respeta las horas al restar entre dos fecha, es decir, que me resta la

hora aparte de la fecha.

Ejemplo:

C3 == > 27-11-2011 3:00

D3 ==> 28-11-2011 13:50 …. Formula = RESIDUO(D3-C3;1) … Resultado: 10:50:45

Cuando devería ser por lo menos: 32:50:45

en cuanto a que fueron las 3:00am del día anterior v/s las 13:50 del día siguiente…

8. Yigo dijo:

28 noviembre, 2011 en 17:56

*O*, Hola de nuevo, simplemente resté a secas los dos campos (D3-C3) y formatié la celda como está en tu tutorial

[h]:mm y me funcionó… Gracias por tremendo aporte.. 

9. Mikel dijo:

27 diciembre, 2011 en 14:55

Muy amable por tu aportación.

La verdad es que me ha ayudado. Es ni más ni menos la info que buscaba.

Gracias por compartir tu conocimiento.

saludos.

10. Carlos dijo:

19 enero, 2012 en 13:11

¿Cómo puedo calcular el número de horas festivas en un turno de trabajo que empiece o acabe entre un día laboral y

un día festivo?

Gracias

11. josenrique dijo:

21 enero, 2012 en 12:46

Uf, ¿quién nos ayuda con esta duda de carlos?

12. Carlos dijo:

22 enero, 2012 en 10:24

Creo que ya he resuelto el problema para los domingos. Es decir, el turno puede empezar el sábado y acabar el

domingo, empezar el domingo y acabar el domingo, o empezar el domingo y acabar el lunes.

SI(O(DIASEM(L3;2)=7;DIASEM(M3+N3;2)=7);SI(Y(DIASEM(L3;1)=1;DIASEM(M3+N3;1)=1)=VERDADERO;(M3+N3)-

L3;SI(DIASEM(L3;1)=1;(E3+1)-L3;SI(DIASEM(M3+N3;1)=1;(M3+N3)-(E3+1);””)));””)

L3= Hora inicio

M3= Hora Fin de turno

N3= prolongación de jornada que se añade a M3

Ahora me falta hacer lo mismo pero con festivos especiales, y es donde estoy atrancado pues los domingos los detecto

fácilmente con la función DIASEM pero no acabo de dar con la forma de que me detecte el día festivo.