Excel Avanzado 2013 Pg. 1
Contenido 1.- Funciones ............................................................................................................................................4
Funcin de Base de datos: ...................................................................................................................4
Lista de Datos .......................................................................................................................................4
Criterios Normales ...............................................................................................................................5
Criterios Especiales...............................................................................................................................5
Criterios creados como resultado de una frmula ..............................................................................5
Funcin BDCONTAR ..............................................................................................................................6
Funcin BDCONTARA ...........................................................................................................................8
Funcin BDMAX ....................................................................................................................................9
Funcin BDMIN ................................................................................................................................. 11
Funcin BDSUMA .............................................................................................................................. 12
Funcin BDPROMEDIO ...................................................................................................................... 13
Para todas las funciones .................................................................................................................... 16
Funciones lgicas .............................................................................................................................. 17
Funcin Y ........................................................................................................................................... 18
Funcin O .......................................................................................................................................... 18
Funcin SI.ERROR .............................................................................................................................. 19
Funcin FALSO ................................................................................................................................... 19
Funcin VERDADERO ......................................................................................................................... 19
Funcin SI.ND .................................................................................................................................... 19
Funcin XO ........................................................................................................................................ 19
Relaciones y valores lgicos .............................................................................................................. 21
Expresiones complejas ...................................................................................................................... 22
Toma de decisiones ........................................................................................................................... 25
Uso de decisiones para evitar errores ............................................................................................... 25
Anidacin de expresiones y decisiones ............................................................................................. 26
Operaciones condicionales ............................................................................................................... 28
Seleccin de valores de una lista ...................................................................................................... 30
Funciones de Bsqueda y Referencia de datos ................................................................................. 32
La funcin DESREF ............................................................................................................................. 32
La funcin INDIRECTO ....................................................................................................................... 35
La funcin COINCIDIR ........................................................................................................................ 37
La funcin INDICE .............................................................................................................................. 38
Excel Avanzado 2013 Pg. 2
Otras funciones de inters ................................................................................................................ 40
Unin de cadenas .............................................................................................................................. 40
Extraccin de caracteres ................................................................................................................... 41
Trabajando con fechas y horas ......................................................................................................... 43
Funciones informativas ..................................................................................................................... 45
Calculando las semanas del ao en Excel ......................................................................................... 45
Funcin FIN.MES ............................................................................................................................... 47
Funciones operacionales ................................................................................................................... 48
Funcin DIA.LAB ................................................................................................................................ 48
Funcin DIAS.LAB .............................................................................................................................. 49
Buscar la fecha despus de una cantidad de meses ......................................................................... 52
Funcin SiFecha ................................................................................................................................. 53
Calcular el primer da hbil del mes con Excel .................................................................................. 54
Administracin de horas de trabajo en Excel .................................................................................... 56
Pasemos a las horas y minutos ......................................................................................................... 59
2.-Formulas matriciales ......................................................................................................................... 64
Qu es un formula Matricial? .......................................................................................................... 64
Usar formulas matriciales avanzadas de una sola celda ................................................................... 65
Trabajar con frmulas matriciales de varias celdas .......................................................................... 66
Qu son matrices constantes y cmo puedo usarlas? .................................................................... 72
Cmo puedo editar las frmulas de matriciales? ............................................................................ 72
3.- Controles de Formularios ................................................................................................................. 76
Formularios ....................................................................................................................................... 76
Controles de formulario .................................................................................................................... 77
Activando los controles de formularios ............................................................................................ 77
Etiqueta ............................................................................................................................................. 79
Cuadro de grupo ................................................................................................................................ 79
Botn ................................................................................................................................................. 79
Casilla ................................................................................................................................................ 80
Botn de opcin ................................................................................................................................ 80
Cuadro de lista .................................................................................................................................. 80
Cuadro combinado ............................................................................................................................ 80
Barra de desplazamiento .................................................................................................................. 81
Control de nmero ............................................................................................................................ 81
Excel Avanzado 2013 Pg. 3
Elaborar Formularios en la hoja ........................................................................................................ 82
Excel Avanzado 2013 Pg. 4
Excel Avanzado 2013
1.- Funciones
Funcin de Base de datos:
Lista de Datos
Una lista de Datos es una coleccin de datos organizados en registros (filas) y en campos (columnas). Un registro contiene
toda la informacin perteneciente a un elemento (tem), por ejemplo: los datos de un alumno. Un campo contiene un dato
perteneciente al elemento, por ejemplo: su nombre, edad, domicilio, cursos, etc.
CONDICIONES
1. El rango de una lista deber estar separado al menos por una fila y una columna de otra informacin
2. La primera fila, est destinada a los ttulos o nombres de los campos
3. No es vlido introducir espacios en blanco al empezar un dato en una celda
5. Cada columna debe usar el mismo formato, es decir debe de contener un mismo tipo de dato.
6. Excel diferencia los caracteres en minsculas o maysculas, cuando efecta ordenamientos
7. Se puede usar frmulas para calcular valores de campo.
8. No debe de haber columna o filas en blanco entre los datos
Nota: Para los ejemplos utilizaremos los datos de la figura anterior
FUNCIN DESCRIPCIN
BDCONTAR Cuenta el nmero de celdas que contienen nmeros en una base de datos.
BDCONTARA Cuenta el nmero de celdas no vacas en una base de datos.
BDMAX Devuelve el valor mximo de las entradas seleccionadas de la base de datos.
BDMIN Devuelve el valor mnimo de las entradas seleccionadas de la base de datos.
BDSUMA Suma los nmeros de la columna de campo de los registros de la base de datos que cumplen los criterios.
Excel Avanzado 2013 Pg. 5
FUNCIN DESCRIPCIN
BDPROMEDIO Devuelve el promedio de las entradas seleccionadas en la base de datos.
BDEXTRAER Extrae de una base de datos un nico registro que cumple los criterios especificados.
Criterios Normales
USE PARA BUSCAR
ADM Trabajadores que trabajan en el rea de Administracin.
>=3000 Trabajadores que ganan a partir de 3000 soles
Criterios Especiales
USE PARA BUSCAR
? (signo de interrogacin) Un nico carcter Por ejemplo, Gr?cia buscar "Gracia" y "Grecia"
* (asterisco) Cualquier nmero de caracteres Por ejemplo, *este buscar "Nordeste" y "Sudeste"
~ (tilde) seguida de ?, * o ~ Un signo de interrogacin, un asterisco o una tilde Por ejemplo, af91~? buscar "af91?".
Criterios creados como resultado de una frmula
Puede usar como criterio valores calculados que sean el resultado de una frmula (frmula:
secuencia de valores, referencias de celda, nombres, funciones u operadores de una celda
que producen juntos un valor nuevo.
Una formula comienza siempre con el signo igual (=).). Tenga en cuenta los puntos
importantes siguientes:
La frmula se debe evaluar como VERDADERO o FALSO.
Dado que est usando una frmula, escrbala como lo hara normalmente y evite escribir
la expresin de la forma siguiente: =''=entrada''
No use rtulos de columna para los rtulos de criterios; deje los rtulos de criterios en
blanco o use un rtulo distinto de los rtulos de columna del rango (en los ejemplos
siguientes, Promedio calculado y Coincidencia exacta).
Si en la frmula usa un rtulo de columna en lugar de una referencia de celda relativa o
un nombre de rango, Excel presenta un valor de error, como por ejemplo #NOMBRE? o
Excel Avanzado 2013 Pg. 6
#VALOR!, en la celda que contiene el criterio. Puede ignorar este error, ya que no afecta
al modo en que se filtra el rango.
La frmula que se usa para los criterios debe usar una referencia relativa (referencia
relativa: en una frmula, direccin de una celda basada en la posicin relativa de la celda
que contiene la frmula y la celda a la que se hace referencia. Si se copia la frmula, la
referencia se ajusta automticamente. Una referencia relativa toma la forma A1.) para
hacer referencia a la celda correspondiente de la primera fila.
Todas las dems referencias usadas en la frmula deben ser referencias absolutas
(referencia de celda absoluta: en una frmula, direccin exacta de una celda,
independientemente de la posicin de la celda que contiene la frmula. Una referencia
de celda absoluta tiene la forma $A$1.).
Funcin BDCONTAR
Cuenta las celdas que contienen datos en un campo (columna) de registros de datos
numricos de una lista o base de datos que cumplen las condiciones especificadas.
Sintaxis
=BDCONTAR(base_de_datos; nombre_de_campo; criterios)
Base_de_datos Obligatorio. Es el rango o nombre de celdas que compone la lista o base de
datos. La primera fila de la lista contiene los rtulos de cada columna.
Nombre_de_campo Obligatorio. Indica qu columna se usa en la funcin. Escriba el rtulo
de la columna entre comillas, como por ejemplo "Paterno" o "Zona", o un nmero (sin las
comillas) que represente la posicin de la columna en la lista: 1 para la primera columna, 2
para la segunda y as sucesivamente.
Criterios Obligatorio. El rango de celdas que contiene las condiciones especificadas. Puede
usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rtulo
de columna y al menos una celda debajo del rtulo de columna en la que se pueda
especificar una condicin de columna.
Ejemplo1: Hallar el nmero de trabajadores de categora 2 que hay en la lista de datos
personal.
Excel Avanzado 2013 Pg. 7
Detalles de la frmula: =BDCONTAR(PERSONAL,11,B2:B3)
PERSONAL, es el nombre que se ha asignado a toda la lista de datos.
11 es el nmero de orden de la columna sobre la cual se aplica la funcin, en este caso la
columna CATEGO que es de contenido numrico.
B2:B3 es el rango donde se encuentran los criterios.
Ejemplo2: Hallar el nmero de trabajadores del rea de administracin(ADM) y que trabajan
en la zona Norte (N) de la lista de datos personal.
Cuando los criterios contiene ms de una columna(AREA,ZONA) , entonces en estos casos el
segundo parmetro se ignora y solamente se consideran el primer parmetro, en este caso
el nombre de la lista de datos (PERSONAL) y el rango de criterios, en este caso B6:C7
Excel Avanzado 2013 Pg. 8
Funcin BDCONTARA
Cuenta las celdas que contienen datos en un campo (columna) de registros de datos
alfanumricos de una lista o base de datos que cumplen las condiciones especificadas.
Sintaxis
BDCONTARA(base_de_datos, nombre_de_campo, criterios)
Base_de_datos Obligatorio. Es el rango o nombre de celdas que compone la lista o base de
datos. La primera fila de la lista contiene los rtulos de cada columna.
Nombre_de_campo Obligatorio. Indica qu columna se usa en la funcin. Escriba el rtulo
de la columna entre comillas, como por ejemplo "Paterno" o "Zona", o un nmero (sin las
comillas) que represente la posicin de la columna en la lista: 1 para la primera columna, 2
para la segunda y as sucesivamente.
Criterios Obligatorio. El rango de celdas que contiene las condiciones especificadas. Puede
usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un rtulo
de columna y al menos una celda debajo del rtulo de columna en la que se pueda
especificar una condicin de columna.
Ejemplo1: Hallar el nmero de trabajadores del rea de Administracin (ADM) que hay en la
lista de datos personal.
Ejemplo2: Hallar el nmero de trabajadores del rea de Contabilidad (CON) y que adems
trabajen en la zona E que hay en la lista de datos personal.
Excel Avanzado 2013 Pg. 9
Ejemplo3: Hallar el nmero de trabajadores que ingresaron a trabajar durante el ao
noventa.
Ejemplo4: Hallar el nmero de trabajadores que tiene un sueldo mayor al promedio
1. Calcular el promedio en una celda que no formara parte del promedio.
2. Crear la zona del criterio donde se digitara la siguiente formula
=CONCATENAR(">",REDONDEAR(B32,2))
Funcin BDMAX
Devuelve el valor mximo de un campo (columna) de registros en una lista o base de datos
que cumple las condiciones especificadas.
Sintaxis
=BDMAX(base_de_datos, nombre_de_campo, criterios)
Excel Avanzado 2013 Pg. 10
Base_de_datos Obligatorio. Es el rango de celdas que compone la lista o base de datos.
Una base de datos es una lista de datos relacionados en la que las filas de informacin son
registros y las columnas de datos, campos. La primera fila de la lista contiene los rtulos de
cada columna.
Nombre_de_campo Obligatorio. Indica qu columna se usa en la funcin. Escriba el rtulo
de la columna entre comillas, como por ejemplo "Edad" o "SUELDO", o un nmero (sin las
comillas) que represente la posicin de la columna en la lista: 1 para la primera columna, 2
para la segunda y as sucesivamente. Es obligatorio que este campo sea de tipo numrico.
Criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas.
Puede usar cualquier rango en el argumento criterios mientras este incluya al menos un
rtulo de columna y una celda debajo del mismo en la que se pueda especificar una
condicin para la columna.
Ejemplo1: Hallar el mayor sueldo del rea de Ventas (VEN) que hay en la lista de datos
personal.
Ejemplo2: Hallar el mayor sueldo de los trabajadores del rea de Contabilidad (CON) y que
pertenezcan a la zona Este (E).
Ejemplo3: Hallar el mayor sueldo de los trabajadores que ingresaron a trabajar durante el
ao noventa.
Excel Avanzado 2013 Pg. 11
Funcin BDMIN
Devuelve el valor mnimo de un campo (columna) de registros en una lista o base de datos
que cumple las condiciones especificadas.
Sintaxis
=BDMIN(base_de_datos, nombre_de_campo, criterios)
Base_de_datos Obligatorio. Es el rango de celdas que compone la lista o base de datos.
Una base de datos es una lista de datos relacionados en la que las filas de informacin son
registros y las columnas de datos, campos. La primera fila de la lista contiene los rtulos de
cada columna.Nombre_de_campo Obligatorio. Indica qu columna se usa en la funcin.
Escriba el rtulo de la columna entre comillas, como por ejemplo "Edad" o "RENDTO", o un
nmero (sin las comillas) que represente la posicin de la columna en la lista: 1 para la
primera columna, 2 para la segunda y as sucesivamente. Es obligatorio que este campo sea
de tipo numrico.
Criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas.
Puede usar cualquier rango en el argumento criterios mientras este incluya al menos un
rtulo de columna y una celda debajo del mismo en la que se pueda especificar una
condicin para la columna.
Ejemplo1: Hallar el menor sueldo del rea de Informtica (INF) que hay en la lista de datos
personal.
Excel Avanzado 2013 Pg. 12
Ejemplo2: Hallar el menor sueldo de los trabajadores del rea del rea de VENTAS (VEN) y
que adems trabajen en la ZONA norte(N).
Ejemplo3: Hallar el menor sueldo de los trabajadores que ingresaron a trabajar durante el
ao 1990
Funcin BDSUMA
Suma los nmeros de un campo (columna) de registros de una lista o base de datos que
cumplen las condiciones especificadas.
Sintaxis
=BDSUMA(base_de_datos, nombre_de_campo, criterios)
Base_de_datos Obligatorio. Es el rango de celdas que compone la lista o base de datos.
Una base de datos es una lista de datos relacionados en la que las filas de informacin son
registros y las columnas de datos, campos. La primera fila de la lista contiene los rtulos de
cada columna.
Nombre_de_campo Obligatorio. Indica qu columna se usa en la funcin. Escriba el rtulo
de la columna entre comillas, como por ejemplo "Edad" o "RENDTO", o un nmero (sin las
comillas) que represente la posicin de la columna en la lista: 1 para la primera columna, 2
para la segunda y as sucesivamente. Es obligatorio que este campo sea de tipo numrico.
Excel Avanzado 2013 Pg. 13
Criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas.
Puede usar cualquier rango en el argumento Criterios mientras este incluya por lo menos un
rtulo de columna y al menos una celda debajo del rtulo de columna en la que se pueda
especificar una condicin de columna.
Ejemplo1: Hallar la suma de sueldos del rea de marketing(MAR).
Ejemplo2: Hallar la suma de sueldos de los trabajadores del rea de VENTAS(VEN) de la
ZONA SUR (S) y adems del rea de CONTABILIDAD (CON) de la ZONA OESTE(O).
Ejemplo3: Hallar la suma de sueldos de los trabajadores cuyos sueldos es mayor que el
promedio.
Funcin BDPROMEDIO
Devuelve el promedio de los valores de un campo (columna) de registros en una lista o base
de datos que cumple las condiciones especificadas.
Excel Avanzado 2013 Pg. 14
Sintaxis:
=BDPROMEDIO(base_de_datos; nombre_de_campo; criterios)
Base_de_datos es el rango de celdas que compone la lista o base de datos. Una base de
datos es una lista de datos relacionados en la que las filas de informacin son registros y las
columnas de datos, campos. La primera fila de la lista contiene los rtulos de cada columna.
Nombre_de_campo indica qu columna se usa en la funcin. Escriba el rtulo de la
columna entre comillas, como por ejemplo "Edad" o "Rendimiento", o un nmero (sin las
comillas) que represente la posicin de la columna en la lista: 1 para la primera columna, 2
para la segunda y as sucesivamente. Es obligatorio que este campo sea de tipo numrico.
Criterios es el rango de celdas que contiene las condiciones especificadas. Puede usar
cualquier rango en el argumento Criterios mientras este incluya por lo menos un rtulo de
columna y al menos una celda debajo del rtulo de columna en la que se pueda especificar
una condicin de columna.
Ejemplo1: Hallar el promedio de sueldos del rea de administracin(ADM).
Ejemplo2: Hallar el promedio de sueldos de los trabajadores del rea de INFORMATICA(INF)
de la ZONA ESTE (E) y adems del rea de VENTAS (VEN) de la ZONA SUR(S).
Excel Avanzado 2013 Pg. 15
Ejemplo3: Hallar el promedio de sueldos de los trabajadores cuyos sueldos es mayor que el
promedio.
Funcin BDEXTRAER
Extrae un nico valor de una columna de una lista o una base de datos que cumple las
condiciones especificadas.
Sintaxis
BDEXTRAER(base_de_datos, nombre_de_campo, criterios)
Base_de_datos Obligatorio. Es el rango de celdas que compone la lista o base de datos.
Una base de datos es una lista de datos relacionados en la que las filas de informacin son
registros y las columnas de datos, campos. La primera fila de la lista contiene los rtulos de
cada columna.
Nombre_de_campo Obligatorio. Indica qu columna se usa en la funcin. Escriba el rtulo
de la columna entre comillas, como por ejemplo "Edad" o "RENDTO", o un nmero (sin las
comillas) que represente la posicin de la columna en la lista: 1 para la primera columna, 2
para la segunda y as sucesivamente.
Criterios Obligatorio. Es el rango de celdas que contiene las condiciones especificadas.
Puede usar cualquier rango en el argumento criterios mientras este incluya al menos un
rtulo de columna y una celda debajo del mismo en la que se pueda especificar una
condicin para la columna.
Observaciones
Si ningn registro cumple los criterios, BDEXTRAER devuelve el valor de error #VALOR!.
Si ms de un registro coincide con los criterios, BDEXTRAER devuelve el valor de error
#NUM!.
Excel Avanzado 2013 Pg. 16
Cualquier rango se puede usar como argumento criterios, siempre que incluya al menos un
rtulo de columna y una celda debajo del mismo para especificar la condicin.
Ejemplo1: Presentar el rea de trabajo del trabajador con cdigo ADM0031
Ejemplo2: Presentar el rea de trabajo y zona del trabajador con cdigo CON0021.
rea de trabajo:
Zona de Trabajo
Para todas las funciones
Observaciones
Cualquier rango se puede usar como argumento criterios, siempre que incluya por lo
menos un nombre de campo y por lo menos una celda debajo del nombre de campo para
especificar un valor de comparacin de criterios.
Aunque el rango de criterios puede ubicarse en cualquier parte de la hoja de clculo, no
coloque el rango de criterios debajo de la lista. Si agrega ms informacin a la lista, la
Excel Avanzado 2013 Pg. 17
nueva informacin se agrega a la primera fila debajo de la lista. Si la fila de debajo no est
vaca, Microsoft Excel no podr agregar la nueva informacin.
Asegrese de que el rango de criterios no queda superpuesto a la lista.
Para realizar una operacin en toda una columna de la base de datos, inserte una lnea en
blanco debajo de los nombres de campo en el rango de criterios.
Funciones lgicas
Las funciones lgicas sirven para realizar la comparacin lgica entre valores o referencias de
celdas. Excel tiene varias funciones lgicas que permiten evaluar datos. La mayora de las
funciones lgicas retornan como resultado VERDADERO o FALSO.
En todos los mbitos de trabajo, ya sea pblico o privado, existen a diario necesidades
enfocadas en la utilizacin de criterios lgicos para tomar o inferir soluciones.
Ese tipo de criterios son herramientas que utiliza Microsoft Excel para desarrollar trabajos a
partir de la utilizacin de operadores circunscritos en funciones lgicas, que se convierten en
valiosa ayuda a momento de generar planillas, reportes, trmites, etc. Se presenta a
continuacin la forma de aplicacin de esas funciones lgicas dentro de Microsoft Excel
Las funciones lgicas de Excel pueden ser utilizados para realizar operaciones que permitan
la toma de decisiones dentro de una hoja de clculo.
Por ejemplo la funcin SI compara dos argumentos, realiza una operacin si el resultado es
verdadero y otra operacin si el resultado es falso.
Las principales funciones Lgicas comnmente utilizadas en Excel son: Y, O y SI
El siguiente resumen muestra la lista de cules son las funciones Lgicas predefinidas en
Excel.
Excel Avanzado 2013 Pg. 18
FUNCIN DESCRIPCIN
Funcin Y Devuelve VERDADERO si todos sus argumentos son VERDADERO.
Funcin FALSO Devuelve el valor lgico FALSO.
Funcin SI Especifica una prueba lgica que realizar.
Funcin SI.ERROR Devuelve un valor que se especifica si una frmula lo evala como un
error; de lo contrario, devuelve el resultado de la frmula.
Funcin SI.ND Devuelve el valor que se especifica, si la expresin se convierte en
#N/A; de lo contrario, devuelve el resultado de la expresin.
Funcin NO Invierte el valor lgico del argumento.
Funcin O Devuelve VERDADERO si cualquier argumento es VERDADERO.
Funcin
VERDADERO
Devuelve el valor lgico VERDADERO.
Funcin XO Devuelve un O exclusivo lgico de todos los argumentos.
Funcin Y
Devuelve VERDADERO si todos los argumentos se evalan como VERDADERO; devuelve
FALSO si uno o ms argumentos se evalan como FALSO.
Un uso comn de la funcin Y es expandir la utilidad de otras funciones que realizan pruebas
lgicas. Por ejemplo, la funcin SI realiza una prueba lgica y, luego, devuelve un valor si la
prueba se evala como VERDADERO y otro valor si la prueba se evala como FALSO. Con la
funcin Y como argumento prueba_lgica de la funcin SI, puede probar varias condiciones
diferentes en lugar de solo una.
Sintaxis
=Y(valor_lgico1; [valor_lgico2]; ...)
Funcin O
Devolver VERDADERO si alguno de los argumentos es VERDADERO; devolver FALSO si
todos los argumentos son FALSO.
Sintaxis
=O(valor_lgico1, [valor_lgico2], ...)
Excel Avanzado 2013 Pg. 19
Funcin SI.ERROR
Devuelve el valor especificado si una frmula se evala como un error; de lo contrario,
devuelve el resultado de la frmula. Use la funcin SI.ERROR para interceptar y controlar
errores en una frmula.
Sintaxis
=SI.ERROR(valor, valor_si_error)
Funcin FALSO
Devuelve el valor lgico FALSO. La sintaxis de la funcin FALSO no tiene argumentos.
Sintaxis
=FALSO()
Funcin VERDADERO
Devuelve el valor lgico VERDADERO. La sintaxis de la funcin VERDADERO no tiene
argumentos.
Tambin puede escribir la palabra FALSO o VERDADERO directamente en la hoja de clculo o
en la frmula y Microsoft Excel la interpretar como el valor lgico FALSO o VERDADERTO. La
funcin FALSO o VERDADERO se proporciona principalmente por motivos de compatibilidad
con otros programas para hojas de clculo.
Funcin SI.ND
Devuelve el valor que se especifica si la frmula devuelve el valor de error #N/A; de lo
contrario, devuelve el resultado de la frmula.
Sintaxis
=SI.ND(Valor, Valor_Si_#N/A)
Funcin XO
Devuelve un O exclusivo lgico de todos los argumentos.
Excel Avanzado 2013 Pg. 20
Sintaxis
=XO(valor_lgico_1, [valor_lgico_2],)
Para entender fcilmente lo que significa el O exclusivo ser de gran utilidad recordar que en
Excel ya tenemos la funcin O la cual nos permite evaluar varias expresiones lgicas y nos
devolver el valor VERDADERO en caso de que alguna de ellas sea verdadera. Por ejemplo,
en la siguiente imagen puedes ver el resultado de utilizar la funcin O al evaluar diferentes
combinaciones de valores VERDADERO y FALSO:
Como puedes observar, la funcin O devolver el valor VERDADERO cuando al menos una de
las expresiones lgicas evaluadas sea verdadera. Sin embargo, la funcin XO se comporta de
una manera diferente ya que nos devolver el valor verdadero cuando solo una expresin,
dentro de todas las expresiones evaluadas, sea verdadera.
De ah el nombre de O exclusivo que nos indica que obtendremos un valor verdadero
exclusivamente cuando una sola expresin sea verdadera. La funcin XO, junto con la
funcin O y la funcin Y, son de gran ayuda para evaluar mltiples condiciones en Excel.
Excel Avanzado 2013 Pg. 21
Relaciones y valores lgicos
Qu es una expresin lgica? Funciones (expresiones) en Excel pueden dar resultados
numricos o ser resueltas como expresin lgica con el valor VERDADERO o el valor FALSO.
Si escribimos la frmula "=A1=B1", Excel dar como resultado o FALSO o VERDADERO.
Esta caracterstica puede ser aprovechada para crear una alternativa a la funcin SI.
Veremos esto con un ejemplo. Supongamos un club de compras, donde los clientes reciben
descuentos sobre las compras mensuales basados en las siguientes reglas
Nuestra tarea es determinar el descuento que le corresponde a cada cliente dados estos
datos:
Para determinar el descuento de cada cliente no utilizaremos funciones SI, sino expresiones
lgicas. Dado que tenemos que tener en cuenta dos condiciones (las compras del mes
corriente y las del anterior) usaremos la funcin Y (AND en su versin inglesa) para armar la
expresin lgica. La frmulas que expresan las condiciones de los descuentos son:
Excel Avanzado 2013 Pg. 22
Por ejemplo, si la expresin =Y(B5
Excel Avanzado 2013 Pg. 23
Propuesta 1
Las frmulas propuestas variaron entre 4 y 5 funciones SI combinadas.
=SI(C2="Platinum",SI(Y(CONTAR(D2:E2)=2,F2>10000),F2*0.2,SI(Y(CONTAR(D2:E2)=2,F210000),F2*0.1))),SI(Y(C2="Gold",CONTAR(D2:E2)=2,F2
>15000),F2*0.1," "))
Propuesta 2
Si bien esta frmula resuelve el problema, se puede escribir de otra manera usando
solamente 2 funciones SI. La regla a seguir dice que la cantidad de funciones SI a combinar
equivale a la cantidad de casos menos 1. Si bien hay cinco casos de descuento, como
podemos ver en la tabla arriba, que de hecho hay tres casos: los que reciben 20% de
descuento adicional; los que reciben 10% y los que no reciben descuento adicional.
Para construir la frmula, primero definiremos los nombres que contengan las frmulas para
determinar el tipo de descuento.
Para esto escribimos las frmulas en celdas laterales, lo que nos permitir verificar su
funcionamiento.
Excel Avanzado 2013 Pg. 24
Asignar nombre a las formulas:
Nombre Formula
P_2_10 =Y(C2="Platinum",CONTARA(D2:E2)=2,F2>10000)
P_2 =Y(C2="Platinum",CONTARA(D2:E2)=2)
P_10 =Y(C2="Platinum",F2>10000)
G_2_15 =Y(C3="Gold",CONTARA(D3:E3)=2,F3>15000)
La funcin CONTARA nos permite contar la cantidad de productos comprados en cada
compra. Usamos esta funcin y no CONTAR para evitar resultados errneos cuando una de
las celdas del rango est en blanco.
Los nombres los definimos:
Dado que las condiciones P_2, P_10 y G_2_15 dan como resultado el mismo descuento
(10%), las combinaremos en una sola condicin usando la funcin O (OR en su versin
inglesa). Finalmente podemos escribir nuestra frmula de la siguiente manera:
=SI(P_2_10,F2*0.2,(SI(O(P_2,P_10,G_2_15),F2*0.1,"")))
Excel Avanzado 2013 Pg. 25
Toma de decisiones
Uso de decisiones para evitar errores
Supongamos un formulario en Excel donde controlamos un proceso de aprobacin de
crditos compuesto de tres partes: presentacin, proceso y aprobacin.
Al introducir una fecha en la celda A4, la celda Estatus (D4) muestra la etapa en que se
encuentra el proceso; al introducir una fecha en B4, el valor de Estatus cambia a En
proceso y finalmente al introducir una fecha en C4, Estatus muestra Aprobado
La solucin ms inmediata es crear una frmula con la funcin SI
=SI(A4"",SI(B4"",SI(C4"","Aprobado","En proceso"),"Presentado"),"")
Esta frmula tiene varios problemas. El primero es que el uso de SI anidado nos pone serias
limitaciones a la cantidad de condiciones que podemos procesar. Si bien en Excel 2007 o
2010 0 2013 podemos anidar hasta 64 niveles de SI (en comparacin a los 7 niveles en Excel
Excel Avanzado 2013 Pg. 26
Clsico), por encima de las 3 o 4 condiciones la frmula se vuelve compleja y difcil de
manejar.
Podemos superar este problema creando una frmula con la funcin ELEGIR
=ELEGIR(CONTARA(A4:C4)+1,"","Presentado","En proceso","Aprobado")
Anidacin de expresiones y decisiones
Encontrar valores que se encuentran entre dos meses sin repeticin
En el archivo ListaMeses.xlsx se tiene las siguientes transacciones escoger un mes entre
enero y diciembre y devolver como respuesta la lista de los productos que se han vendido en
dicho mes (sin repeticin de productos)
Para resolver este caso primero tienen que a hacer una validacin de datos que permita
escoger un nombre de mes entre enero y diciembre.
En una columna previamente creada deben colocar los cdigos de producto que cumplan
con la condicin de pertenecer al mes dado como dato, si no cumple con la condicin del
mes se colocar "".
Lista de productos.
1. Abrir el archivo ListaMeses.xlsx
2. Seleccionar la celda F7.
3. Escribir Cumple.
Excel Avanzado 2013 Pg. 27
4. Seleccionar la celda F8.
5. Ingresar la formula =SI(TEXTO(A8,"mmmm")=$B$5,C8,""), que permitir mostrar los
productos que se vendieron en el mes seleccionado en la celda B5.
6. Seleccionar la celda F8 y en modo copiar arrastrar hasta la celda F203.
7. Seleccionar la celda E7.
8. Escribir 0.
9. Seleccionar la celda E8.
10. Escribir la formula =SI(F8="",E7,SI(CONTAR.SI($F$7:F8,F8)=1,E7+1,E7)). Con la formula
solo se enumera los productos que se muestran en el rango F8:F203.
11. Seleccionar la celda E8 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda E203).
12. Seleccionar la celda G7.
13. Escribir 0.
14. Seleccionar la celda G8.
15. Escribir la formula =SI(G7
Excel Avanzado 2013 Pg. 28
En la columna H se muestran los productos que se vendieron en el mes consultado.
Operaciones condicionales
Convertir en valores homogneos
Hallar el mnimo sueldo entre las personas viudas con ms de 36 aos de edad y que
entraron a trabajar un da Lunes, o Viernes.
En caso que no haya datos, se debe mostrar el mensaje SIN DATOS
Lista de datos con diferentes formas de escribir un mismo valor.
1. Abrir el archivo Personal.xlsx
2. Seleccionar la celda G5.
3. Escribir Estado
Excel Avanzado 2013 Pg. 29
4. Seleccionar la celda G6.
5. Para borrar los espacios de los nombres escribir al formula =SUSTITUIR(C6," ","").
6. Seleccionar la celda H5.
7. Escribir Estado1
8. Crear una lista de datos en el rango M5:N8, seleccionar la celda M5.
9. Escribir VIU
10. Seleccionar la celda M6.
11. Escribir CAS
12. Seleccionar la celda M7.
13. Escribir SOL
14. Seleccionar la celda M8.
15. Escribir DIV
16. Seleccionar la celda N5.
17. Escribir Viudo
18. Seleccionar la celda N6.
19. Escribir Casado
20. Seleccionar la celda N7.
21. Escribir Soltero
22. Seleccionar la celda N8.
23. Escribir Divorciado
24. Seleccionar la celda H5.
25. Escribir Estado1
26. Seleccionar la celda H6
27. Escribir la frmula =CONSULTAV(IZQUIERDA(G6,3),$M$5:$N$8,2,0).
28. Seleccionar la celda H6 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda H48).
29. Seleccionar I5.
30. Escribir Sueldo
31. Seleccionar la celda I6.
32. Escribir la frmula =SUSTITUIR(SUSTITUIR(SUSTITUIR(E6," ",""),"S/.","S/"),"S/","") para
mostrar solo el importe.
Excel Avanzado 2013 Pg. 30
33. Seleccionar la celda I6 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda I48).
34. Seleccionar J5.
35. Escribir Dato.
36. Seleccionar la celda J6.
37. Escribir la frmula para que muestre el importe de las personas viudas con ms de 36
aos de edad y que entraron a trabajar un da Lunes, o Viernes.
=SI(Y(H6="VIUDO",D6>36,O(DIASEM(F6,2)=1,DIASEM(F6,2)=5)),I6*1,"")
38. Seleccionar la celda J6 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda J48).
39. Seleccionar la celda J2.
40. Escribir la formula =SI(SUMA(J6:J48)=0,"Sin Datos",MIN(J6:J48))
Valores homogneos.
Seleccin de valores de una lista
Crear listado sin repeticin
En el archivo ListaPersonalizada.xlsx se tiene la Hoja1, con un histrico de datos de los
clientes, se solicita obtener una lista de los nombres de los clientes sin repeticin.
Excel Avanzado 2013 Pg. 31
Histrico de datos.
1. Abrir el archivo ListaPersonalizada.xlsx
2. Seleccionar la celda I7.
3. Escribir la formula =CONSULTAV(B7,$B7:B$7,1,0).
4. Seleccionar la celda I8.
5. Para buscar en el rango que se encuentre encima de la celda que contiene el valor
buscado escribir la siguiente formula =CONSULTAV(B8,$B$7:B7,1,0)
6. Seleccionar la celda I8 y en modo copiar arrastrar hasta la celda I28 para copiar la
formula en el rango I8:I28.
7. Se va enumerar la cantidad de clientes no repetidos, seleccionar la celda J7.
8. Escribir 1.
9. Seleccionar la celda J8.
10. Escribir la frmula =SI(ESERROR(I8),J7+1,J7).
11. Seleccionar la celda J8 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda J28).
12. Seleccionar la celda K7.
13. Escribir la formula =B7.
14. Seleccionar la celda K7 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda K28).
15. Se va enumerar la cantidad de clientes para crear la lista, seleccionar la celda L7.
16. Escribir 1.
17. Seleccionar la celda L8.
Excel Avanzado 2013 Pg. 32
18. Para enumerar hasta la cantidad de cliente no repetidos escribir la siguiente formula
=SI(L7
Excel Avanzado 2013 Pg. 33
La sintaxis de esta funcin es:
=DESREF (referencia; filas movidas; columnas movidas; alto; ancho)
En donde:
Referencia: Es una celda o rango de celdas desde la cual comienza la desviacin. Si
especifica un rango de celdas, las celdas deben ser adyacentes unas con otras.
Filas movidas: Es el nmero de filas lejos de la referencia de celda o rango en la que quiere
que inicie el rango de referencia (la celda en la esquina superior izquierda en al rango de
desviacin). Un nmero negativo de filas lo mover arriba de la referencia; un nmero
positivo de filas lo mover abajo. Por ejemplo, si referencia es igual a C5 y filas movidas es
igual a -1, se mueve a la fila 4. Si filas movidas es igual a +1, se mueve a la fila 6. Si filas
movidas es igual a 0, permanece en la fila 5.
Columnas movidas: Es el nmero de columnas lejos de la referencia de celda o rango en la
que quiere que inicie el rango de referencia. Un nmero negativo de columnas lo mueve a la
izquierda de la referencia; un nmero positivo de columnas lo mueve a la derecha. Por
ejemplo, si referencia es igual a C5 y columnas movidas es igual a -1, se mueve a la columna
B. Si columnas movidas es igual a +1, se mueve a la columna D. Si columnas movidas es igual
a 0, permanece en la columna C.
Cmo puedo crear una referencia a un rango de celdas que es un nmero especfico de
filas y columnas desde una celda u otro rango de celdas?
En la figura se brinda algunos Ejemplos de la funcin DESREF en accin.
Excel Avanzado 2013 Pg. 34
Por ejemplo, en la celda B10, he introducido la frmula (mostrada en la celda A10) =SUMA
(DESREF (B7; -1; 1; 2; 1) ). Esta frmula comienza en la celda B7. Nos movemos una fila arriba
y una columna a la derecha, lo cual nos lleva a la celda C6. Ahora seleccionamos un rango
consistente de dos filas y una columna, lo cual produce el rango C6:C7. La funcin SUMA
agrega los nmeros en este rango, lo cual produce 2+6=8. Los otros dos ejemplos mostrados
en la figura trabajan de la misma forma.
En las siguientes secciones, aplicaremos la funcin DESREF para resolver problemas que me
fueron enviados por mis antiguos alumnos trabajando en una gran compaa en los Estados
Unidos.
Cmo puedo llevar a cabo una operacin de bsqueda basada en la columna ms a la
derecha en un rango de tabla en vez de la columna ms a la izquierda?
Bsqueda Izquierda.
En la figura bsqueda izquierda, he listado los miembros del equipo de baloncesto de la NBA
los Dallas Maverick y sus porcentajes de objetivos en el campo. Si me piden encontrar al
jugador con un porcentaje de objetivo en el campo especfico, podra resolver fcilmente
este problema usando la funcin BUSCARV. Pero qu sucede si realmente lo que quiero es
hacer una bsqueda a la izquierda, lo cual involucra encontrar el porcentaje objetivo de
Excel Avanzado 2013 Pg. 35
campo para un jugador usando su nombre. Una funcin BUSCARV no puede llevar a cabo
una bsqueda hacia la izquierda, pero una bsqueda hacia la izquierda es simple si combina
las funciones COINCIDIR y DESREF.
Primero, introduzca el nombre del jugador en la celda D7. Luego usamos una referencia a
celda de B7 (el porcentaje del campo objetivo en la cabecera de columna) en la funcin
DESREF. Para encontrar el porcentaje del campo objetivo del jugador, necesitamos
movernos hacia abajo a la fila debajo de la fila 7 en donde aparece el nombre del jugador.
Este es un trabajo para la funcin COINCIDIR. La porcin de la frmula de la funcin
COINCIDIR=DESREF(B7;COINCIDIR(D7;$C$8:$C$22;0);0;1;1) nos mueve debajo de la fila
conteniendo el nombre especfico del jugador y luego nos mueve sobre 0 columnas. Debido
a que la referencia consiste de una celda, omitiendo los argumentos ancho y alto de la
funcin DESREF nos aseguramos que el rango devuelto por esta frmula es tambin una
celda. Adems obtenemos el porcentaje del objetivo de campo del jugador.
La funcin INDIRECTO
Devuelve la referencia especificada por una cadena de texto. Las referencias se evalan de
inmediato para presentar su contenido. Use INDIRECTO para cambiar la referencia a una
celda en una frmula sin cambiar la propia frmula.
Sintaxis
INDIRECTO(Ref; [a1])
La sintaxis de la funcin INDIRECTO tiene los siguientes argumentos:
Ref Obligatorio. Una referencia a una celda que contiene una referencia de tipo A1 o F1C1,
un nombre definido como referencia o una referencia a una celda como cadena de texto. Si
ref no es una referencia de celda vlida, INDIRECTO devuelve el valor de error #REF!.
La funcin INDIRECTO es probablemente una de las funciones ms difciles de dominar en
Microsoft Office Excel. Sabiendo cmo usar la funcin INDIRECTO, sin embargo, le permite
resolver muchos problemas que parecen no tener solucin. Esencialmente, cualquier
referencia a celda dentro de una porcin de frmula INDIRECTO de un resultado de frmula
en la referencia a celda es inmediatamente evaluada para igualar el contenido de la celda.
Excel Avanzado 2013 Pg. 36
Un ejemplo simple de la funcin INDIRECTO.
En la celda C4, he introducido la frmula =INDIRECTO (A4). Excel devuelve un valor de 6,
debido a que la referencia a A4 es inmediatamente reemplazada por la cadena de texto B4.
Por lo tanto, la frmula es evaluada como =B4, que produce un resultado de 6. Igualmente,
introduciendo en la celda C5 la frmula =INDIRECTO (A5) devuelve el valor en la celda B5,
que es 9.
Mis frmulas en mi hoja de clculo frecuentemente contienen referencias a celdas, rangos o
ambos. Ms que cambiar estas referencias en mis frmulas, me gustara saber cmo puedo
colocar las referencias en sus propias celdas as que puedo fcilmente cambiar mi celda o
referencias a rangos sin cambiar mis frmulas.
En este ejemplo, los datos que usaremos estn mostrados en la figura SumaIndirecto. El
rango de celdas B4:H16 lista los datos de ventas mensuales para seis productos durante un
perodo de 12 meses.
Figura SumaIndirecto
Excel Avanzado 2013 Pg. 37
La funcin COINCIDIR
La funcin COINCIDIR busca un elemento especificado en un rango de celdas y, a
continuacin, devuelve la posicin relativa de ese elemento en el rango.
Sintaxis:
= COINCIDIR(valor_buscado;matriz_buscada; [tipo_de_coincidencia])
valor_buscado Obligatorio. Valor que desea buscar en matriz_buscada.
matriz_buscada Obligatorio. Rango de celdas en el que se realiza la bsqueda
tipo_de_coincidencia Opcional. Puede ser el nmero -1, 0 o 1. El argumento
tipo_de_coincidencia especfica cmo Excel hace coincidir el valor_buscado con los valores
de matriz_buscada. El valor predeterminado de este argumento es 1.
Tipo_de_coincidencia Comportamiento
1 u omitido
COINCIDIR encuentra el mayor valor que es menor o igual que el valor_buscado. Los valores del argumento
matriz_buscada
se deben colocar en orden ascendente, por ejemplo: ...-2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO.
0
COINCIDIR encuentra el primer valor que es exactamente igual que el valor_buscado. Los valores del argumento
matriz_buscada
pueden estar en cualquier orden.
-1
COINCIDIR encuentra el menor valor que es mayor o igual que el valor_buscado. Los valores del argumento
matriz_buscada
se deben colocar en orden descendente, por ejemplo: VERDADERO, FALSO, Z-A, ...2, 1, 0, -1, -2, ..., etc.
Ejemplo:
Excel Avanzado 2013 Pg. 38
La funcin INDICE
Devuelve un valor o la referencia a un valor de una tabla o rango. La funcin INDICE presenta
dos formas: matricial y de referencia.
Forma matricial
Sintaxis:
=INDICE(matriz; nm_fila; [nm_columna])
Usando los datos del rango A20:B22 en la celda A26 escribir la =INDICE(A21:B22,2,2) y en la
celda BB27 escribir =INDICE(A21:B22,2,1)
Forma de referencia
Sintaxis:
= INDICE(ref; nm_fila; [nm_columna]; [nm_rea])
Excel Avanzado 2013 Pg. 39
Frmula Descripcin (resultado) Frmula
=INDICE(A44:C48, 2, 3) Interseccin de la segunda fila y la tercera columna en el
rango A44:C48, que es el contenido de la celda C45 (38). 38
=INDICE((A43:C48, A50:C53), 2, 2, 2)
Interseccin de la segunda fila y la segunda
columna en la segunda rea de A50:C53, que es el
contenido de la celda B9 (3,55).
3.55
=SUMA(INDICE(A43:C53, 0, 3, 1)) Suma de la tercera columna en la primera rea del
rango A1:C11, que es la suma de C1:C6 (216). 216
=SUMA(B44:INDICE(A44:C48, 5, 2))
Suma del rango que comienza en B2 y termina en
la interseccin de la quinta fila y la segunda
columna del rango A2:A6, que es la suma de B2:B6
(2,42).
2.42
Ejemplo:
Excel Avanzado 2013 Pg. 40
Otras funciones de inters
Unin de cadenas
Convertir nmeros con espacios y smbolos monedas en valor numrico
Hallar el promedio en EUROS de los nmeros que se encuentran en el rango B7:B48 del
archivo Cadenas.xlsx.
Lista de datos en el rango B7:B48.
1. Abrir el archivo 12 Texto.xlsx
2. Seleccionar la celda D8.
3. Eliminar los espacios, escribiendo la formula =RECORTAR(SUSTITUIR(B8," ","")).
4. Seleccionar la celda D8 y en modo copiar arrastrar hasta la celda D48.
5. Eliminar los smbolos moneda S/. y US$ adems reemplazar la coma por el punto (solo si
el smbolo decimal es punto), seleccionar la celda E8.
6. Escribir la siguiente formula:
=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(D8,"dlares",""),"S/.",""),"US$",""),",",".")
7. Seleccionar la celda E8 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda E48).
8. Escribir Soles o Dlares segn le corresponda, seleccionar la celda F8
9. Escribir la formula =SI(ESERROR(HALLAR("S/",D8,1)),"Dlares","Soles")
10. Seleccionar la celda F8 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda F48).
11. Convertir los valores a euros, seleccionar la celda G8.
12. Escribir la formula =SI(F8="SOLES",E8/$B$3,E8*1)/$D$3.
Excel Avanzado 2013 Pg. 41
13. Seleccionar la celda G8 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda G48).
14. Seleccionar la celda G2.
15. Escribir la formula =PROMEDIO(G8:G48)
Columnas auxiliares para obtener datos numricos.
Extraccin de caracteres
Contar cuantas personas tienen dos nombres
En el archivo Nombres.xlsx, en la Hoja1 en rango B5:B18 se pide obtener la cantidad de
personas que tienen solamente dos nombre.
Nota: NO se consideran nombres las palabras de o del, por ejemplo Marcela del Pilar se
considera como 2 nombres.
Excel Avanzado 2013 Pg. 42
Lista de nombres.
1. Abrir el archivo 11 Texto.xlsx
2. Seleccionar la celda E5.
3. Se va extraer los nombres teniendo como referencia la coma que separa el apellido
paterno y apellido materno.
Se ubica la posicin de la segunda coma ms 2 posiciones y a partir de ah se extrae los
nombres.
La frmula es =MED(B5,HALLAR(",",B5,HALLAR(",",B5,1)+1)+2,300).
4. Seleccionar la celda E5 y en modo copiar haga doble clic (la formula se copiara hasta la
celda E18).
5. Como las palabras de o del no se considera como nombres, sustituir por (nada).
Seleccionar la celda F5.
6. Escribir la frmula =SUSTITUIR(SUSTITUIR(E5," de "," ")," del "," ")
7. Seleccionar la celda F5 y en modo copiar haga doble clic (la formula se copiara hasta la
celda F18).
8. Eliminar los espacios, seleccionar la celda G5,
9. Escribir la frmula =SUSTITUIR(F5," ","")
10. Seleccionar la celda G5 y en modo copiar haga doble clic (la formula se copiara hasta la
celda G18).
11. Calcular las longitudes de texto del rango F5:F18, seleccionar la celda H5.
12. Escribir la frmula =LARGO(F5).
13. Seleccionar la celda H5 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda H18).
14. Calcular las longitudes de texto del rango I5:I18, seleccionar la celda I5.
15. Escribir la frmula =LARGO(G5).
16. Seleccionar la celda I5 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda I18).
17. Calcular la diferencia de la longitud, seleccionar la celda J5.
18. Escribir la formula =H5-I5
19. Seleccionar la celda J5 y en modo copiar haga doble clic (la frmula se copiara hasta la
celda J18).
Excel Avanzado 2013 Pg. 43
Cuando el valor es cero, tiene un solo nombre
Cuando el valor es 1, tiene dos nombres
Cuando el valor es 2, tiene tres nombres
20. Seleccionar la celda G2.
21. Escribir la formula =CONTAR.SI(J5:J18,1).
Columnas auxiliares para obtener el resultado.
Trabajando con fechas y horas
Cuntas jornadas laborales faltan para las vacaciones? Cul es la fecha de finalizacin de
un proyecto? En este curso, le mostraremos cmo usar frmulas para buscar la cantidad de
das entre dos fechas. Mediante estas frmulas puede realizar muchos clculos (hasta le
mostraremos a determinar cuntos das pasaron desde su nacimiento)
Hay una diferencia entre lo que significan las fechas para el usuario y lo que significan para
Excel. Para el usuario, 22/8/2010 es un da, un mes y un ao. Para Excel, 22/8/2010 es el
nmero de serie 40412. En esta leccin aprender cmo realizar operaciones aritmticas con
fechas y cmo escribir fechas en un formato que Excel pueda entender. Aprender un poquito
ms sobre las fechas antes de realizar clculos le ahorrar tiempo.
Excel Avanzado 2013 Pg. 44
1. Aspecto de una fecha para el usuario.
2. Aspecto de una fecha para Excel
Las fechas se almacenan en Excel como nmeros de serie.
Excel 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.
Las fechas se almacenan en Excel como nmeros de serie comenzando por 1, que representa
el 1 de enero de 1900. En esta fecha comienza el calendario de Excel. Cada da despus de
esa fecha agrega un nmero a la secuencia. Por ejemplo, el 2 de enero de 1900 se almacena
como 2.
Esto significa que si escribe 22/8/2010, Excel almacena la fecha como 40412, o 40411 das
desde el 1 de enero de 1900.
Almacenar fechas como nmeros de serie permite realizar operaciones aritmticas con
fechas en Excel. Para averiguar la cantidad de das que hay entre dos fechas, por ejemplo,
Excel resta los dos nmeros de serie.
Nota No puede usar fechas anteriores al 1 de enero de 1900 en frmulas de Excel.
Excel Avanzado 2013 Pg. 45
Funciones informativas
Cmo averiguar el nmero de das, meses y aos que hay entre dos fechas
Para calcular el nmero de das, meses y aos que hay entre dos fechas, donde las fechas
inicial y final se introducen en las celdas A1 y A2 respectivamente, siga estos pasos:
1. Cree un libro Nuevo.
2. Haga clic en Libro en blanco
3. Escriba los datos siguientes en el libro:
A1: 01/11/2012
A2: 10/01/2014
4. Escriba la frmula siguiente en la celda A4:
=AO(A2)-AO(A1)-SI(O(MES(A2)
Excel Avanzado 2013 Pg. 46
El resto de esta entrada est basado en los excelentes artculos de Chip Pearson y Ron de
Bruin sobre el tema.
La primer semana comienza el 1 de enero; pero, cuando comienza la segunda, al 8 de enero
o el primer lunes despus del 1 de enero? Que da marca el comienzo de la semana, el lunes
o el domingo?
Veamos las distintas formas de calcular el nmero de semana.
Nmero de semana absoluto.
De acuerdo a este concepto, la primera semana empieza siempre el 1 de enero y concluye el
7 de enero, sin tener en cuenta el da de la semana. De esta manera tendremos 53 semanas
al ao. La semana 53 tendr un da o dos, si se trata de un ao bisiesto.
Para calcular el nmero de semana absoluto usamos la frmula
=TRUNCAR(((A1-FECHA(AO(A1),1,0))+6)/7)
Si en la celda A1 tenemos la fecha de hoy (28/06/2006), esta frmula da el resultado 26.
Nmero de semana de Excel.
Excel ofrece la funcin NUM.DE.SEMANA).
Esta funcin calcula el nmero de semana contando desde el domingo o el lunes. Por lo
tanto, la primera semana puede tener entre 1 y 7 das.
La sintaxis de esta frmula es NUM.DE.SEMANA(nm_de_serie;tipo)
Nm_de_serie es una fecha dentro de la semana. Las fechas deben introducirse mediante la
funcin FECHA o como resultados de otras frmulas o funciones.
Tipo es un nmero que determina en qu da comienza la semana. El valor puede ser 1
(domingo) o 2 (lunes).
La funcin = NUM.DE.SEMANA (A1,2), donde A1 contiene la fecha 28/06/2006 da el resulta
27. En cambio la funcin = NUM.DE.SEMANA (A1,1) da 26.
Nmero de semana ISO (International Organization for Standardisation )
De acuerdo a la norma ISO, la semana empieza siempre un lunes y termina un domingo. La
primera semana del ao es la que contiene el primer jueves. Es decir, la primera semana
tendr siempre 4 das por lo menos.
Excel Avanzado 2013 Pg. 47
La frmula para calcular el nmero de semana de acuerdo al estndar ISO es
=ENTERO((A1-FECHA(AO(A1-DIASEM(A1-1)+4),1,3)+DIASEM(FECHA(AO(A1-DIASEM(A1-
1)+4),1,3))+5)/7)
Si la celda A1 contiene la fecha 28/06/2006, el resultado ser 26.
Finalmente, si queremos utilizar frmulas en lugar de la funcin NUM.DE.SEMANA,
Si la semana empieza en domingo:
=1+ENTERO((A1-(FECHA(AO(A1),1,2)-DIASEM(FECHA(AO(A1),1,1))))/7)
Si la semana empieza en lunes:
=1+ENTERO((A1-(FECHA(AO(A1),1,2)-DIASEM(FECHA(AO(A1),1,0))))/7)
Funcin FIN.MES
FIN.MES: Esta funcin nos sirve para obtener a partir de una fecha inicial, la fecha de cierre
del mes que se requiera.
Tiene dos parmetros:
fecha_inicial: es la fecha a partir de la cual se requiere hacer el clculo
meses: Es la cantidad de meses luego de la fecha_inicial de la cual se requiere saber la fecha
de cierre.
Ejemplo 01
Tomando como fecha inicial 01/11/2013, se requiere conocer la fecha de cierre dentro de 3
meses:
Excel Avanzado 2013 Pg. 48
El resultado de la funcin indica que dentro de 3 meses y a partir del 01/11/2013, la fecha de
cierre es 28 de febrero de 2014.
Funciones operacionales
Funcin DIA.LAB
Devuelve un nmero que representa una fecha que es el nmero de das laborables antes o
despus de una fecha (la fecha inicial). Los das laborables excluyen los das de fin de semana
y cualquier fecha identificada en el argumento festivo.
Use DIA.LAB para excluir fines de semana o das festivos cuando calcule fechas de
vencimiento de facturas, las fechas de entrega esperadas o el nmero de das de trabajo
realizado.
Sintaxis
=DIA.LAB(fecha_inicial, das, [vacaciones])
La sintaxis de la funcin DIA.LAB tiene los siguientes argumentos:
Fecha_inicial Obligatorio. Es una fecha que representa la fecha inicial.
Das Obligatorio. El nmero de das laborables (das que no sean fines de semana ni das
festivos) anteriores o posteriores al argumento fecha_inicial. Un valor positivo para el
argumento das produce una fecha futura; un nmero negativo produce una fecha pasada.
Vacaciones Opcional. Es una lista opcional de una o varias fechas que deben excluirse del
calendario laboral, como los das festivos nacionales y locales. La lista puede ser un rango de
celdas que contengan las fechas o una constante de matriz de los nmeros de serie que
representen las fechas.
Ejemplo
Si vivimos en cualquier pas y los das de labor corren de lunes a viernes, podemos usar la
funcin DIA.LAB nativa de Excel. Por ejemplo, si queremos saber cul ser la fecha despus
de cinco das hbiles empezando el 06/04/2009, DIA.LAB nos da estas respuestas
Excel Avanzado 2013 Pg. 49
La celda C4 da la respuesta sin tomar en cuenta los feriados; la celda C5 es la fecha calculada
tomando en cuenta los feriados
Funcin DIAS.LAB
Devuelve el nmero de das laborables entre fecha_inicial y fecha_final. Los das laborables
no incluyen los fines de semana ni otras fechas que se identifiquen en el argumento
vacaciones. Use DIAS.LAB para calcular el incremento de los beneficios acumulados de los
empleados basndose en el nmero de das trabajados durante un perodo especfico.
Sintaxis
=DIAS.LAB(fecha_inicial, fecha_final, [vacaciones])
La sintaxis de la funcin DIAS.LAB tiene los siguientes argumentos:
Fecha_inicial Obligatorio. Es una fecha que representa la fecha inicial.
Fecha_final Obligatorio. Es una fecha que representa la fecha final.
Vacaciones Opcional. Es un rango opcional de una o varias fechas que deben excluirse del
calendario laboral, como los das festivos nacionales y locales.
Ejercicio
Empecemos por plantear el problema. Queremos saber cuntos das hbiles hay en el mes
de abril del ao 2009.
Excel Avanzado 2013 Pg. 50
El resultado, que no toma en cuenta los feriados de abril, es 22 das.
Agreguemos a nuestra frmula los feriados del ao 2009 (que aparecen en el rango G3:G14
de la hoja)
Vemos que el nmero de das hbiles es ahora 20 (el 12 de abril cae un domingo).
=DIAS.LAB.INTL
La 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 funcin DIAS.LAB.INTL tiene cuatro argumentos que nos permiten personalizar
adecuadamente la manera en como deseamos contar los das laborables.
Excel Avanzado 2013 Pg. 51
Sintaxis
DIA.LAB.INTL(fecha_inicial, das, [fin_de_semana], [das_no_laborables])
La sintaxis de la funcin DIA.LAB.INTL tiene los siguientes argumentos:
Fecha_inicial Obligatorio. Es la fecha inicial, truncada a entero.
Das Obligatorio. Es el nmero de das laborables antes o despus de la fecha_inicial. Un
valor positivo da como resultado una fecha futura; un valor negativo proporciona una fecha
pasada; un valor de cero proporciona la fecha_inicial. El desplazamiento de das se trunca a
entero.
Fin_de_semana Opcional. Indica los das de la semana que corresponden a das de la
semana y no se consideran das laborables. Fin_de_semana es un nmero de fin de semana
o cadena que especifica cundo ocurren los fines de semana.
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
Los valores numricos de fin de semana indican los siguientes das de fin de semana:
NMERO DE FIN DE SEMANA DAS DE FIN DE SEMANA
1 u omitido Sbado, domingo
2 Domingo, lunes
3 Lunes, martes
4 Martes, mircoles
5 Mircoles, jueves
6 Jueves, viernes
7 Viernes, sbado
11 Solo domingo
12 Solo lunes
13 Solo martes
14 Solo mircoles
15 Solo jueves
16 Solo viernes
17 Solo sbado
Excel Avanzado 2013 Pg. 52
El 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.
Considerar das no laborables
Para 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:
Buscar la fecha despus de una cantidad de meses
Suponga que tiene unos 25 meses desde el 9/6/2011 para completar un proyecto, y necesita
buscar la fecha de finalizacin del proyecto. Puede hacerlo mediante la funcin FECHA. Esta
funcin tiene tres argumentos: ao, mes y da.
Excel Avanzado 2013 Pg. 53
En nuestro ejemplo, despus de escribir 25 en la celda B2, deber escribir esta frmula en la
celda A4:
=FECHA(2011;6+B2;9) o =FECHA(AO(A2),MES(A2)+B2,DIA(A2)) o =FECHA.MES(A2,B2)
2011 es el argumento de ao, 6 ms el valor de la celda B2 es el argumento de mes, 9 es el
argumento de da. El signo de punto y coma separa los argumentos y los parntesis incluyen
todos los argumentos.
El proyecto finaliza el 9/7/2013. Dado que escribi la referencia de celda (B2) en lugar del
valor de la celda (25), Excel puede actualizar automticamente el resultado si cambia el
valor. Por ejemplo, si el perodo cambia de 25 meses a 23 meses, puede obtener la fecha
revisada 9/5/2013 cambiando el valor de la celda B2 de 25 a 23, sin volver a escribir la
frmula.
Funcin SiFecha
En Excel existe una frmula, no documentada en la ayuda y que no figura en el asistente de
funciones, denominada:
=SiFecha(fecha inicial;fecha final;tipo)
donde:
tipo es el tipo de respuesta que da la funcin, y puede ser:
y Calcula el nmero de aos transcurridos
m Calcula el nmero de meses transcurridos
d Calcula el nmero de das transcurridos. Equivale a restar ambas fechas
ym Calcula los meses sin considerar los aos enteros transcurridos
Excel Avanzado 2013 Pg. 54
md Calcula los das sin considerar los aos y meses enteros transcurridos
Calcular el primer da hbil del mes con Excel
Excel cuenta con varias funciones para calcular fechas tomando en cuenta feriados: para
calcular fechas, DIA.LAB, DIA.LAB.INTL ; para lapsos, DIAS.LAB, DIAS.LAB.INTL.
Para calcular el primer da hbil del mes combinamos las funciones DIA.LAB y FIN.MES de la
siguiente manera
En la celda C2 ponemos el nmero de mes, en la celda C3 el ao; en la celda C5 la frmula
=DIA.LAB(FIN.MES(FECHA(C3,C2,1),-1),1)
Hace el clculo de esta manera
FIN.MES calcula el ltimo da del mes de la FECHA, tantos meses atrs o adelante como
indique el segundo argumento de la funcin. En nuestro caso "'-1" indica un mes atrs, es
decir 31/08/2013.
DIA.LAB calcula el da laboral antes o despus de la fecha indicada, segn el segundo
argumento de la funcin. En nuestro caso "1" significa el primer da laboral despus del
31/08/2013.
Excel Avanzado 2013 Pg. 55
Si queremos poner todos los das hbiles del mes corriente en un rango de la hoja hacemos
lo siguiente:
En la celda C1 ponemos la funcin HOY() y le asignamos el nombre definido "fechaActual" (o
cualquier otro que les plazca).
El mes y el ao lo obtenemos en las celdas C2 y C3 con las funciones =MES y =AO. A la
celda C2 le asignamos el nombre definido "mesActual".
En la celda C6 ponemos esta frmula y la copiamos 30 filas abajo (dado que el nmero
mximo de das de un mes es 31)
=SI(MES(DIA.LAB(FIN.MES(fechaActual,-1),FILA()-
5))=mesActual,DIA.LAB(FIN.MES(fechaActual,-1),FILA()-5),"")
La condicin de la funcin SI evala si la fecha cae dentro del mes definido ("mesActual"), en
caso positivo calcula la fecha del da hbil usando como numerador la expresin FILA()-5 (-5
porque empezamos en la fila 6; en caso de comenzar en otra fila hay que modificar la
frmula).
Si la condicin no se cumple, la celda no muestra ningn valor.
Si queremos mostrar las fechas en una fila, usamos como numerador el expresin
COLUMNA()-x. Por ejemplo, si la primer fecha aparece en la columna D, la frmula ser
=SI(MES(DIA.LAB(FIN.MES(fechaActual,-1),COLUMNA()-
3))=mesActual,DIA.LAB(FIN.MES(fechaActual,-1),COLUMNA()-3),"")
Excel Avanzado 2013 Pg. 56
Administracin de horas de trabajo en Excel
Supongo que si se hiciera una estadstica sobre los usos de Excel, las herramientas para la
administracin de horas de trabajo figuraran entre los primeros lugares. Excel est "hecho a
medida" para estas tareas, pero para construir este tipo de herramientas tenemos que
comprender primero como maneja Excel el tema de las fechas y las horas.
Las horas son la parte decimal del nmero. Un da completo son 24 horas, de manera que las
12 del medioda est representado por el nmero 0.5; las seis de la maana por el 0.25, las 6
de la tarde por el 0.75, etc. En este momento es el 9 de febrero a las 9:50 de la maana. El
resultado de la funcin AHORA() es 09/02/2008 09:50:36
Si cambiamos el formato de la celda a "General" veremos el nmero 39487.4101446759
Donde 39487 es el nmero de das transcurrido desde el 01-01-1900 hasta hoy y
0.4101446759 resulta de dividir 35346 segundos (el equivalente de 9 horas, 50 minutos y 36
segundos) por 86400 (la cantidad de segundos que hay en un da).
Otro aspecto importante a tomar en cuenta, antes de abocarnos a la tarea de construir
herramientas para administrar horas, es el formato de nmeros en Excel y el de las fechas y
horas en particular. Cambios de formato no alteran el nmero sino como es representado
por Excel en la pantalla. Supongamos una tabla donde ponemos el comienzo de una tarea,
las horas a trabajar y el resultado ser la hora de finalizacin
Para ver el resultado, debemos cambiar el formato de la celda, que ha sido "heredado" de la
celda A2, por el formato hh:mm
Excel Avanzado 2013 Pg. 57
Extrao, no? Para entender este resultado volvemos a cambiar el formato de las celdas a
"General"
Sucede que cuando ingresamos 15:30 en la celda A2, Excel la interpreta como el 1ro. de
enero de 1900 a las 15:30. Al poner 8 en la celda B2, por ser un nmero entero, es
interpretado como 8 das. El resultado en la celda C2 ser el 8 de enero de 1900 a las 15:30.
Esto lo podemos ver cambiando el formato de las celdas a dd/mm/yyyy hh:mm
Para evitar resultados errneos debemos ingresar los datos de horas con formato horario.
En este ejemplo hemos ingresado el dato en la celda B2 como 08:00, lo que Excel interpreta
como 8 horas, y por lo tanto el resultado es el esperado
Hay otras cuestiones a considerar cuando trabajamos con horas y fechas en Excel, como el
manejo de resultados negativos, suma de horas por encima de 24 horas, distintos sistemas
de fechas y ms.
Excel Avanzado 2013 Pg. 58
Consideremos ahora este ejemplo
Todas las celdas de la tabla tienen el formato hh:mm. El Total Bruto es la cantidad de horas
transcurridas desde el ingreso hasta la salida, sin tomar en cuenta el descanso. La frmula de
Total bruto es =(E5-B5)+(E5
Excel Avanzado 2013 Pg. 59
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 detrs de ese formato tambin hay un nmero. Las horas, minutos y segundos tienen
una equivalencia numrica que es con la que Excel opera. Cul es esa equivalencia? Pues
resulta que cualquier horario que escribas tiene un valor numrico de cero y unos decimales
que representan la parte proporcional de un da de 24 horas. Por ejemplo las 6 de la maana
(6:00) equivale a un cuarto de da, es decir, 0,25. Las 12:00 sera 0,5.
Escribe en cualquier celda de Excel 8:15 y cuando le cambies el formato a nmero vers que
sale 0,34375. Ve haciendo pruebas hasta que lo asimiles. Las 24:00 ser el nmero 1.
Por eso tambin puedes restar horas minuto y segundos porque realmente lo ests haciendo
con nmeros.
Excel Avanzado 2013 Pg. 60
Hasta aqu es bastante bsico. Los problemas se plantean cuando hablamos de horarios de
diferentes das. Debes saber que en la misma celda puedes escribir una fecha y un horario
dejando un espacio entre ambos. Por ejemplo para indicar el da 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 vaca de Excel. Si entras a ver el formato de la celda vers dd/mm/aaaa hh:mm.
En este caso el valor numrico que Excel le asigna es un nmero 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 nmero aparecer la cantidad
40761,72222. Es la suma de 40761 de la fecha y de 0,72222 de la hora. Incluso podras 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 informacin de fechas y
horas estamos en disposicin de afrontar cualquier clculo. Por ejemplo si tienes diferentes
fechas y horas, Cmo calcular el tiempo que pasan entre ellas?, es decir, Cmo calcular
cuntos das, 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:
Excel Avanzado 2013 Pg. 61
Si te fijas en las fechas a la ligera diras que han pasado 3 das sin embargo si compruebas
con detenimiento las horas falta 1 hora y 5 minutos para completarse el tercer da.
Cmo es posible que se resuelva as de fcil? Te cuento. El valor real de la celda A3 es
40762,4375 (prueba a cambiar el formato de la celda A3 a numrico) y el valor real de la
celda B3 es 40765,39236. Recuerda que la parte entera representa el valor numrico 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 funcin RESIDUO se queda con la parte decimal (0,954861111), que
al darle formato de horas y minutos da el resultado que esperbamos.
En ocasiones te encontrars con que la fecha y la hora estn 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) ms 0,4375 (B4) y da
40762,4375 que es la cifra con la que partamos en el ejemplo anterior.
Un matiz muy importante a tener en cuenta es que cuando escribes en una celda 13:25 ests
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 querrs 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 tendra 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
podrs poner en Excel 43:50 siempre y cuando el formato de esa celda sea [h]:mm
Aqu tienes un ejemplo:
Excel Avanzado 2013 Pg. 62
Si hubieras sumado la columna D sin ms no hubiera salido ese resultado pero con slo
cambiar el formato a [h]:mm ya lo tenemos. Por eso te deca al principio de este tutorial que
con el tema de las fechas y las horas un simple cambio de formato puede ser la solucin.
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 das
completos y 2 horas y 25 minutos. Por eso el valor real de 50:25 sera 2,100694444. El 2
indica los das y el 0,100694444 sera la parte proporcional de da que queda, que pasado a
formato de horas y minutos sera las 2:25 que quedaban.
Por ejemplo, partiendo de un tiempo hay que calcular cuntos das y horas son:
Qu simple! Verdad?
Me gustara contarte la funcin 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 funcin =HORA(A1) dar 19, la funcin
=MINUTO(A1) dar 25 y la funcin =SEGUNDO(A1) devolver 30.
Excel Avanzado 2013 Pg. 63
Esto te resultar muy til en aquellos casos en los que hay que pasarlo todo a minutos para
multiplicar por algn importe en concreto. Por ejemplo:
Te explico la frmula 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 das con B4*24*60 que sumado a lo
anterior da los minutos totales:
Creo que con estas tcnicas sers capaz de resolver la mayora 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
Excel Avanzado 2013 Pg. 64
2.-Formulas matriciales
Formulas y Funciones con Matrices
Una matriz es un conjunto de datos organizados en filas y columnas, una hoja Excel tiene
forma de una gran matriz porque est divida en filas y columnas.
Excel trabaja con matrices unidimensionales, es decir de una fila o de una columna, y
matrices bidimensionales formadas por filas y columnas. Tambin existen matrices
tridimensionales que estn formadas por filas, columnas y profundidad, pero que Excel no
entiende.
Qu es un formula Matricial?
Una frmula matricial es una frmula que se aplica a todas las celdas de una matriz,
permitiendo de este modo un ahorro de trabajo.
Se utilizan para ejecutar varias operaciones y devolver un nico valor en la celda donde se la
introduce o tambin para ejecutar varias operaciones y devolver mltiples valores en
distintas celdas.
Las frmulas matriciales pueden actuar en 2 o ms rangos de valores, los que se denominan,
argumentos matriciales, los cuales tienen la caracterstica de tener el mismo nmero de filas
y de columnas, por ejemplo, podran actuar sobre los rangos A1:A12 y BI:B12.
Una frmula matricial se introduce de la misma forma que la frmula comn, la diferencia es
que luego de introducirla hay que apretar las teclas Control+shift+ENTER, con lo que
automticamente es rodeada por llaves y es por eso que se las conoce como frmulas CSE.
Excel Avanzado 2013 Pg. 65
Usar formulas matriciales avanzadas de una sola celda.
Ejemplo 1: Determinar las ventas totales a partir de los siguientes datos, utilizando una
formula matricial.
Para una formula matricial multiplicar 2 argumentos matriciales, como C2:C11*D2:D11
significa multiplicar las celdas C2*D2, C3*D3, C4*D4......C11*D11 si quiero sumar estos
resultados parciales uso la formula matricial {SUMA(C2:C11*D2:D11)}
Entonces para hallar el total en una sola celda utilizando una sola formula seria de la
siguiente manera:
1. Ubicar el cursor en la celda B13.
2. Digitar la frmula:
3. Se pulsan la teclas CRTL+SHIFT+ENTER
Excel Avanzado 2013 Pg. 66
En la barra de frmulas se podr visualizar lo siguiente:
Cuando se introduce una frmula matricial, Microsoft Excel inserta de forma automtica la
frmula entre llaves ({}).
Trabajar con frmulas matriciales de varias celdas
Ejemplo 1: A partir de los siguientes datos determinar las venta totales para cada vendedor
utilizando una formula matricial.
1. Seleccionar el rango E2:E11
Excel Avanzado 2013 Pg. 67
2. Activar el mouse en la barra de frmulas y digitar la frmula:
3. Pulsar las teclas CRTL+SHIFT+ENTER
En la barra de frmulas se podr visualizar lo siguiente:
Ejercicios
Ejemplo 1. A partir de los siguientes datos hallar las Ventas Total, mediante tres formas:
a) Mediante operaciones con celdas y la funcin Suma.
b) Usando la funcin SUMAPRODUCTO.
c) Utilizando una formula matricial.
Segn los datos se observa que se tiene una matriz de cantidades y otra de precios, entonces
se puede:
Obtener el total de ingresos por el mtodo bsico (multiplicar precios por cantidades y
sumarlo)
Excel Avanzado 2013 Pg. 68
Por el mtodo avanzado (usando la funcin SUMAPRODUCTO, que tambin trabaja con
matrices)
Por el mtodo experto (usando una frmula matricial). Observa la notacin de la frmula
matricial {=SUMA(C4:D7*F4:G7)}. Podran usarse parntesis para que quede un poco ms
claro, como {=SUMA((C4:D7)*(F4:G7))}:
En general se podra afirmar que la notacin de una frmula matricial puede expresarse
como:
=FORMULA(MATRIZ1 (operador) MATRIZ2)
Donde su destino puede ser una celda o un conjunto de ellas, y como ya se sabe se
introduce pulsando Ctrl+Shift+Enter.
Frmula puede ser alguna de las muchas funciones de Excel: SUMA, PROMEDIO,
El operador es la operacin que quieres realizar. Para establecer condiciones se utilizan los
operadores AND y OR (es decir Y y O), expresados respectivamente con el producto (*) o la
suma (+).
Ejemplo 2: Se tiene los datos de los valores y cotizaciones de cinco empresas y se nos pide el
promedio de sus Cotizaciones
Ubicar el cursor segn la figura y digitar la formula matricial:
={PROMEDIO(C23:C27-B23:B27)}
Excel Avanzado 2013 Pg. 69
Ejemplo 3: A partir de los datos de venta, segn la siguiente tabla
Completar las celdas de Venta Mxima y Total de ventas x 2 Vendedores
Venta Mxima:={MAX(SI(Vendedor=E7,Monto))}
Total de ventas x 2 Vendedores: ={MAX(SI(Vendedor=E7,Monto))}
Ejemplo 4: A partir de los siguientes datos, determinar el importe del total por el consumo
de Pan, Carne y Mantequilla para cada familia, haciendo uso de una formula matricial.
Excel Avanzado 2013 Pg. 70
Ubicar el cursor en la celda B40.
Digitar la formula =B30*Pan, donde Pan es el nombre del rango B35:E35
Pulsar las teclas CRTL+SHIFT+ENTER.
Excel Avanzado 2013 Pg. 71
Luego se procede a copiar la formula obteniendo el siguiente resultado
Ejemplo 5: Un grupo de alumnos rindi 3 exmenes, Windows, Excel y Word y quieren
saber la nota mxima y mnima que sacaron.
Ubicar el cursor en la celda F6.
Digitar la formula =MAX(SI((ALUMNO=E6),NOTA))
Pulsar las teclas CRTL+SHIFT+ENTER
Para Min. Nota digitar la funcin: =MIN(SI((ALUMNO=E6),NOTA)) y luego pulsar las teclas
CRTL+SHIFT+ENTER.
Excel Avanzado 2013 Pg. 72
Qu son matrices constantes y cmo puedo usarlas?
Cuando especifica una frmula de matriz, a menudo usa un rango de celdas en la hoja de
clculo pero no tiene que hacerlo. Tambin puede usar constantes de matriz, valores que
solo especifica en la barra de frmula dentro de las llaves: {}. A continuacin, puede
proporcionar un nombre a su constante de manera que sea ms sencillo usarla de nuevo.
Puede usar constantes en sus frmulas matriciales o por ellas mismas.
En la frmula de matriz, escriba una llave de apertura, los valores que desee y una llave de
cierre. Este es un ejemplo: =SUMA(A1:E1*{1,2,3,4,5
Recommended