Transcript
  • 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


Top Related