Manual - Excel Intermedio 2016.PDF

Facultad de Ingeniería Industrial y de Sistemas

............................................................................................................. ................ 5 1. Repaso de fórmulas y funciones I ............................................................................................. ................................................................................................................................................. ........................... 6 2. Funciones ....................................................................................................................... ¿Qué es una función? ......................................................................................... ............................................................................................................................... ...................................... 6 Cómo trabajar con funciones .............................................................................................................. ................................................................................................................... ..... 7 ......................................................................................................................................... ........................... 7 Función Contar.Si ............................................................................................................... ................................................................................................................................. ....................................................................... ..... 9 La función Sumar.Si ............................................................... ........................................................................................................................... ..................................................................... ... 11 11 La Función Promedio.Si ......................................................... ........................................................................................................................ .............................................. 13 Función Contar.Si.Conjunto Contar.Si.Conjunto .......................................................................... .................................................................................................................... .................................... 1 4 La Función Sumar.Si.Conjunto ................................................................................ ............................................................................................................. ... 16 La Función Promedio.Si.Conjunto. ........................................................................................................... ........................................................................................................................................ ......................... 17 Función MAYUSC ............................................................................................................... ........................................................................................................................................ ... 17 Función MINUSC ...................................................................................................................................... ................................................................................................................................. 18 Función NOMPROPIO ................................................................................................................................. ................................................................................................................................... .................................... 18 Función IZQUIERDA ................................................................................................ ...................................................................................................................................... 18 Función DERECHA ....................................................................................................................................... .......................................................................................................................................... .......................................................... 1 9 Función EXTRAE ................................................................................ .............................................................................................................................. ......................... 19 La función ENCONTRAR ..................................................................................................... ................................................................................................................................. .................................... 21 La función COINCIDIR .............................................................................................. ........................................................................................................................................... ............................................... 23 Función LARGO ............................................................................................ ............................................................................................................................... .............. 23 Función CONCATENAR .................................................................................................................. ........................................................................................................................................... .......................................................... 2 4 La función AÑO ................................................................................. ........................................................................................................................................... 2 4 La función MES ........................................................................................................................................... ............................................................................................................................................. .................................... 2 5 La función Dia ......................................................................................................... ......................................................................................................................................... ............................................... 25 La función HOY() .......................................................................................... ....................................................................................................................... ............. 25 La función AHORA() .................................................................................................................................... ............................................................................................................................... ................................................ 27 La Función FECHA.MES ............................................................................... ........................................................................................................................... ............................................................................... ............. 29 La Función HORA ......................................................... ........................................................................................................................................ ......................... 29 Función MINUTO ............................................................................................................... .............................................................................................................................. ..................................................................... ... 29 La Función SEGUNDO ............................................................ .............................................................................................................................................. 30 La Función SI ...............................................................................................................................................

Microsoft Excel Intermedio 2016

Pág. 1


................................................................................................................................................... .................................... 32 SI anidado ............................................................................................................... ................................................................................................................................................ ............................................... 33 La función Y .................................................................................................. ............................................................................................................................................... .............. 34 La función O .................................................................................................................................. ....................................................................................................................................... .......................................................... 3 7 Función BUSCARV ............................................................................. ...................................................................................................................................... .............. 39 Función BUSCARH ......................................................................................................................... ................................................................................................... ............................................... 41 3. Listas Personalizadas, Ordenar y filtrar .................................................... ............................................................................................................................... ..................................................................... ... 41 41 Listas Personalizadas ............................................................. .......................................................................................................... ... 41 Creación de una Lista Personalizada ........................................................................................................ ................................................................................................................................ ..................................................................... ... 43 Formato Condicional.............................................................. Formato Condicional Rápido R ápido ............................................................ ...................................................................................................................... .......................................................... 43 .................................................................................................................. ............................................... 4 5 Formato Condicional Avanzado ................................................................... Usar una fórmula para aplicar formato condicional .................................................................................. .................................................................................. 46 ................................................................................................................. .............. 48 Formato de Conjunto de Iconos ................................................................................................... ........................................................................................................................... ................................................................................ .............. 50 Ordenar y Filtrar ......................................................... ................................................................................................................. ......................... 54 Ordenar por lista personalizada ......................................................................................... ............................................................................................................................ 57 57 Filtrar un rango de datos ............................................................................................................................ ................................................................................................................................. ............................................................................... ............. 59 Eliminar Filtro ............................................................... .......................................................................................................................... ......................... 64 4. Esquemas y SubTotales .................................................................................................. ................................................................................................................................................... .................................... 64 Esquemas ................................................................................................................ .............................................................................................................................. .............. 64 Crear un Autoesquema ................................................................................................................. .................................................................................................................... ... 66 Copiar datos esquematizados .................................................................................................................. ..................................................................................................................................... ......................................................... 6 8 Borrar un esquema ............................................................................ ................................................................................................................. .............. 69 69 Crear un esquema de columnas ................................................................................................... ............................................................................. ... 70 Cómo aplicar un esquema a un grupo interno anidado ........................................................................... .................................................................................................................................................. ... 71 Subtotales ................................................................................................................................................ .................................................................................................................................... 74 Eliminar Subtotales ..................................................................................................................................... ................................................................................................................................... .................................... 7 5 Subtotales Anidados ............................................................................................... .................................................................................................................................... ............................................... 77 5. Filtros Avanzados ..................................................................................... ............................................................. ... 77 Consideraciones para la creación de una lista de datos con Excel. .......................................................... ...................................................................................... .............. 78 Examinar una Base de datos empleando paneles ......................................................................... ................................................................................ .............. 78 78 Inmovilizar Primera Fila Superior o Primera Columna .................................................................. ....................................................................................................................... .......................................................... 7 8 Inmovilizar Filas Y Columnas .............................................................


Pág. 2


....................................................................................................................................... 79 Filtros Avanzados ........................................................................................................................................ ................................................................................................................................ ................................................................................ .............. 81 Eliminar Filtro .............................................................. Filtrar utilizando criterios de carácter comodín para buscar valores de texto que comparten algunos ............................................................................................................................ ... 84 84 caracteres pero no otros .........................................................................................................................

..................................................................................................................... 85 85 Filtrar utilizando una fórmula ..................................................................................................................... 6. Herramientas de datos ........................................................................................................................... ........................................................................................................................... 87 ................................................................................................................................... ... 87 Validación De Datos ................................................................................................................................. .......................................................... 8 7 Caso A: Validación de datos Numéricos (Vale también para fechas) .......................................................... ......................................................................................................................... .................................... 90 Caso B: Lista de Validación ...................................................................................... ....................................................................................................................... ... 91 Caso C: Longitud de Texto .......................................................................................................................... ................................................................................................................................ .............. 92 Caso D: Personalizada ................................................................................................................... ........................................................................................................................................ ......................... 93 Consolidar Datos ................................................................................................................ ........................................................................................................................ 94 Consolidación por Posición ......................................................................................................................... ........................................................................................................................... ............................................... 97 Consolidar por categorías ............................................................................ Convertir texto en columnas en Excel ........................................................................................................ 99

.......................................................................................................................... .............................................................................. ............ 101 Quitar duplicados ........................................................ 7. Informes de tabla y gráficos dinámicos ................................................................................................ 103

...................................................................................................................................... ....................... 103 Tablas dinámicas ................................................................................................................ ................................................................................................ ................................. 104 Principales Partes de Una Tabla Dinámica ............................................................... ................................................................................................................... ............................................. 1 06 Cambiar el tipo de Operación ...................................................................... ............................................................................................................................................ ............................................. 107 Agrupaciones ............................................................................................... ...................................................................................................................................... ........................................................ 1 10 Campo Calculado .............................................................................. ................................................................................................................. ....................... 1 11 Actualizar una Tabla dinámica. .......................................................................................... .......................................................................................................................................... 112 Listas de Datos ......................................................................................................................................... ................................................................................................................................... .................................. 113 Gráficos Dinámicos ................................................................................................. ......................................................................................... ............ 116 8. Herramientas de Colaboración y Seguridad .............................................................................. .......................................................................................... 116 Protección de los elementos de libros y hojas ......................................................................................... ....................................................................................................................................... 118 Ocultar formulas ...................................................................................................................................... .......................................................................................... 1 21 Usar contraseñas para proteger todo el libro. .......................................................................................... ......................................................................................................................... ........... 122 Compartir un libro .................................................................................................................................... .................................................................................................................................. ............ 126 Control de cambios ....................................................................................................................... .............................................................................. .................................. 1 27 Opciones de la ficha ventana: Comparación de libros ............................................


Pág. 3


................................................................................................................................... ............................................. 130 Inmovilizar Paneles ...................................................................................... ......................................................................................................................... ....................... 133 Bloques tridimensionales ................................................................................................... 1. Introducción de datos .................................................................................................................. .................................................................................................................. 133 ........................................................................................................................................ ............ 134 Aplicar formato ............................................................................................................................. ................................................................................................................................ ............ 135 Realizar operaciones .....................................................................................................................


Pág. 4


1. Repaso de fórmulas y funciones I Abrir el archivo Repaso.xlsx Comenzar a resolver con ayuda del profesor.


Pág. 5


2. Funciones ¿Qué es una función?

Las funciones son muy útiles a la hora de crear fórmulas complejas, por ejemplo, si usted utiliza una fórmula ordinaria como: =A1+A2+A3+A4+A5+A6, el resultado será el correcto pero tuvo que emplear algún tiempo para crear la fórmula, ahora imagínese que tenga 100 celdas a la cual usted tiene que sumar, ¿piensa crear la misma fórmula? Las funciones le ayudarán a simplificar el trabajo, como por ejemplo: =SUMA(A1:A100). De esta manera ha ahorrado tiempo a la hora de operar esta suma de rangos. Excel posee diferentes funciones entre ellas funciones de texto, de fecha y hora, lógicas, de referencias, matemáticas y trigonométricas, etc.


Pág. 6


Cómo trabajar con funciones

La función CONTAR.SI permite contar cuantos valores que cumplen con solo criterio o condición y que se encuentren presentes en una lista de datos, base de datos o rango de valores. Es decir cuenta los valores que se repiten en un rango de datos.

La sintaxis de la función es la siguiente: =CONTAR.SI(rango; criterio)

Partes de la Función Contar.si: Esta función tiene dos partes Rango: El primer parámetro de la función indica el rango donde se debe buscar el criterio. Criterio: Es el dato que se desea contar, el cual puede ser un valor constante o una dirección de celda.

Supongamos que tenemos la siguiente lista de datos (abrir el archivo Listas.xlsx) que informa de la venta de los vendedores en diferentes Zonas, donde a su vez se supone que cada fila es una venta. Parar efectos de los ejercicios se considerara como rangos desde la celda A2 hasta la celda D22.


Pág. 7


Ejemplo 1: Determinar el número de ventas por cada Zona.

1. Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G5: Digitar la formula =CONTAR.SI(A2:A22,F5)

Ejemplo 2. Determinar el número de ventas por cada Vendedor.

Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G11 Digitar la formula =CONTAR.SI(B2:B22,F11)

Determinar el número de ventas por Rangos Numéricos. N uméricos.

Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G17 Digitar la formula =CONTAR.SI(C2:C22,F17)

Aquí los resultados


Pág. 8


La función SUMAR.SI permite sumar un rango de valores que se encuentran en un rango de datos si es que se cumple con un criterio establecido.

La sintaxis de la función es la siguiente: =SUMAR.SI(rango;criterio;rango_suma)

Partes de la Función Sumar.si: Esta función tiene tres partes 1. La primera parte (parámetro) de la fórmula es el rango donde se encuentra el criterio que se quiere evaluar. 2. La segunda parte o parámetro contiene el criterio a evaluar. 3. La tercera parte o parámetro contiene el rango donde se ha ha de sumar si se cumple con con el criterio establecido.

Supongamos que tenemos la siguiente lista de datos (abrir el archivo Listas1.xlsx) que informa de la venta de los vendedores en diferentes Zonas, donde a su vez se supone que cada fila es una venta. Parar efectos de los ejercicios se considerara como rangos desde la celda A2 hasta la celda D22.


Pág. 9


Ejemplos: Determinar el Total de ventas por cada Zona.

1. Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G5 2. Digitar la formula =SUMAR.SI(A2:A22,F5,C2:C22)

Determinar el Total de ventas por cada Vendedor.

1. Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G11 2. Digitar la formula = SUMAR.SI(B2:B22,F11,C2:C22)

Determinar el Total de ventas por Rangos Numéricos.

1. Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G17 2. Digitar la formula =SUMAR.SI(C2:C22,F17,C2:C22)



Pág. 10


La función PROMEDIO.SI permite hallar el promedio de un rango de datos, que cumplan con solo un criterio.

La sintaxis de la función es la siguiente: =PROMEDIO.SI(rango, criterios, rango_promedio)

Esta función tiene tres partes: 1. rango: Es un argumento obligatorio y representa una o más celdas cuyo promedio se desea obtener

que incluyan números, matrices o referencias que contengan números. 2. criterio: Es un argumento obligatorio y contiene el criterio en forma de número, expresión,

referencia de celda o texto que determina las celdas cuyo promedio se desea calcular. 3. rango_promedio: Es el rango cuyos datos se van a utilizar para calcular el promedio

Notas: •

Si rango es un valor en blanco o de texto, PROMEDIO.SI devuelve el valor de error #¡DIV0!.

•

Si una celda de criterio está vacía, PROMEDIO.SI PROMEDIO.SI la trata como un valor 0.

•

Si no hay celdas en el rango que cumplan los criterios, PROMEDIO.SI devuelve el valor de error error #¡DIV/0!.

•

En los criterios se puede utilizar los caracteres comodín de signo de interrogación (?) y asterisco asterisco (*). El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a


Pág. 11


cualquier secuencia de caracteres. Si desea buscar un signo de interrogación o un asterisco reales, escriba una tilde (~) delante del carácter que desea buscar. •

No es necesario necesario que rango promedio tenga el mismo tamaño y forma que que rango. Las celdas reales de las que se debe obtener el promedio se determinan utilizando la celda superior izquierda de rango promedio como la celda inicial e incluyendo las celdas que corresponden con el tamaño y la forma del rango.

Supongamos que tenemos la siguiente lista de datos (abrir el archivo Lista2.xlsx) que informa de la venta de los vendedores en diferentes Zonas, donde a su vez se supone que cada fila es una venta. Parar efectos de los ejercicios se considerará como rangos desde la celda A2 hasta la celda D22.

Ejemplos:

Determinar el Promedio de ventas por cada Zona.

1. Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G5 2. Digitar la formula =PROMEDIO.SI(A2:A22,F5,C2:C22)

Determinar el Promedio de ventas por cada Vendedor.

1. Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G11 2. Digitar la formula = PROMEDIO.SI(B2:B22,F11,C2:C22)

Determinar el Promedio de ventas por Rangos Numéricos. N uméricos.

1. Ubicar el cursor en la celda donde se va presentar el resultado. En este caso es la celda G17 2. Digitar la formula =PROMEDIO.SI(C2:C22,F17,C2:C22)


Pág. 12



La función CONTAR.SI.CONJUNTO nos permite contar las celdas de un rango que cumplen con uno o más criterios. Esta función nos permite especificar hasta 127 criterios.

La Sintaxis de la función es: = CONTAR.SI.CONJUNTO(R CONTAR.SI.CONJUNTO(Rango1_criterios1, ango1_criterios1, Rango2_criterios2, Rango3_criterios3…..)

Partes de la Función CONTAR.SI.CONJUNTO 1. Rango1_criterios1 (obligatorio): El rango1 de celdas que será evaluado con el Criterio1. 2. Rango2_criterios2 (opcional): El rango de celdas que será evaluado con el Criterio2. A partir del Rango2_criterios2, todos los argumentos son opcionales y se permiten hasta 127 rangos y criterios a evaluar por la función CONTAR.SI.CONJUNTO. Cada rango especificado debe tener la misma cantidad de filas (o columnas) que los anteriores.

Supongamos que tenemos la siguiente lista de datos (abrir el archivo Ventas.xlsx) que informa de la venta de los agentes en diferentes países, donde a su vez se supone que cada fila es una venta en una fecha específica. Parar efectos de los ejercicios se considerará como rangos desde la celda A2 hasta la celda D22.


Pág. 13


Hallar el número de ventas realizadas en cada país por cada vendedor. 1. Se ubica el cursor en la celda G4. 2. Se digita la formula =CONTAR.SI.CONJUNTO($A$2:$A$22,$F4,$B$2:$B$22,G$3) Aquí los resultados:

La función SUMAR.SI.CONJUNTO permite sumar un determinado rango según se cumplan varios criterios, lo que la hace diferente a la función SUMAR.SI que sólo puede considerar un solo criterio. Sintaxis de la función =SUMAR.SI.CONJUNTO(Rango =SUMAR.SI.CONJUNTO(Rango Suma,Rango1_criterios1, Rango2_criterios2, Rango3_criterios3…..) R ango3_criterios3…..) Microsoft Excel Intermedio 2016

Pág. 14


Partes de la Función SUMAR.CONTAR.SI.CONJUNTO 1. Rango Suma: Es el rango donde se encuentran los datos que se van a sumar. 2. Rango1_criterios1 (obligatorio): El rango1 de celdas que será evaluado con el Criterio1. 3. Rango2_criterios2 (opcional): El rango de celdas que será evaluado con el Criterio2.

A partir de la lista de datos se nos pide: Hallar el total de ventas realizadas en cada país por cada vendedor.

1. Se ubica el cursor en la celda G4. 2. Se digita la fórmula: =SUMAR.SI.CONJUNTO($C$2:$C$22,$A$2:$A$22,$F4,$B$2:$B$22,G$3) Aquí los resultados:


Pág. 15


La función PROMEDIO.SI.CONJUNTO nos devuelve el promedio de un rango de celdas que cumplan con las condiciones definidas. Esta función nos permitirá incluir hasta 127 criterios distintos para las celdas que deseamos considerar.

Sintaxis de la función PROMEDIO.SI.CONJUNTO =PROMEDIO.SI.CONJUNTO(RangoPromedio,Rango1_ =PROMEDIO.SI.CONJUNTO (RangoPromedio,Rango1_criterios1,Rango2_criterios2, criterios1,Rango2_criterios2, Rango3_criterios3…)

Partes de la Función PROMEDIO.CONTAR.SI.CONJUNTO 1. Rango Promedio: Es el rango donde se encuentran los datos que se van a promediar. 2. Rango1_criterios1 (obligatorio): El rango1 de celdas que será evaluado con el Criterio1. 3. Rango2_criterios2 (opcional): El rango de celdas que será evaluado con el Criterio2.

Ejemplo de la función PROMEDIO.SI.CONJUNTO

A partir de la lista de datos se nos pide:

Hallar el promedio de ventas realizadas en cada país por cada vendedor.

1. Se ubica el cursor en la celda G4. 2. Se digita la fórmula:=PROMEDIO.SI.CONJUNTO($C$2:$C$22,$A$2:$A$22,$F4,$B$2:$B$22,G$3)


Pág. 16


Aquí los resultados:

Aplicación de Funciones de Texto:

MAYUSC: Esta función convierte todo el contenido de un texto o celda en mayúsculas

Sintaxis de la función: =MAYUSC(Texto o Referencia con texto)

Ejemplo:

MINUSC: Esta función convierte todo el contenido de un texto o celda en minúsculas.

Sintaxis de la función: =MINUSC(Texto o Referencia con texto)


Pág. 17


Ejemplo:

Esta función convierte la primera letra en mayúsculas de cada palabra que forma parte del texto.

Sintaxis de la función: =NOMPROPIO(Texto o Referencia con texto)

Ejemplo:

Esta función devuelve los “n” primeros caracteres de la izquierda de una celda que contenga un dato.

Sintaxis de la función: =IZQUIERDA(Texto,n)

1. Texto: Es el texto a convertir, donde también puede ser una dirección de celda. 2. n: Numero de caracteres a devolver.

Esta función devuelve los “n” primeros caracteres de la derecha de un dato o de una celda que contenga un dato.

Sintaxis de la función: =DERECHA(Texto,n)


Pág. 18

Facultad de Ingeniería Industrial y de Sistemas 1. Texto: Es la dirección de la celda que contiene el texto 2. n: Numero de caracteres a devolver.

Esta función devuelve un número de caracteres de una celda con un dato, a partir de una cierta posición inicial contada desde la izquierda.

Sintaxis de la función: =EXTRAE(Dato o celda,pi,n)

1. Dato o Celda: Es la dirección de la celda que contiene el texto. 2. pi: Es un número que indica desde donde deseamos copiar un texto. 3. n: Es el número de caracteres a copiar.

Ejemplos:

En la celda C36 se encuentran las palabras “SISTEMAS UNI SEDE CENTRAL”, se pide: 1. Que en la celda A39 se presente la palabra SISTEMAS. a. Ubicar el cursor en la celda A39 b. Digitar la formula =IZQUIERDA(C36,9)

2. Que en la celda C39 se presenten las palabras SEDE CENTRAL. a. Ubicar el cursor en la celda C39 b. Digitar la formula =DERECHA(C36,12)

3. Que en la celda E39 se presente la palabra UNI. a. Ubicar el cursor en la celda E39 b. Digitar la formula =EXTRAE(C36,10,3)

Esta función nos permite buscar un carácter, letra o palabra, dentro de una cadena de texto y si la función encuentra lo buscado, nos devolverá la posición que ocupa dicho carácter, letra o palabra.


Pág. 19

Facultad de Ingeniería Industrial y de Sistemas Sintaxis de la función ENCONTRAR =ENCONTRAR(texto_buscado,, dentro_del_texto, [núm_inicial]) =ENCONTRAR(texto_buscado

1. texto_buscado: Carácter, letra o palabra que buscamos. 2. dentro_del_texto: Dirección de celda o texto donde se realizará la búsqueda. 3. núm_inicial: posición desde donde se iniciará la búsqueda.

Notas: a. El tercer argumento podría ser opcional y si no es proporcionado entonces se realizará la búsqueda comenzando en el primer carácter. b. Si la función no encuentra el texto buscado, entonces devolverá un error de tipo #¡VALOR!. También es importante recordar que la función ENCONTRAR hace diferencia entre mayúsculas y minúsculas. Otra función similar es la función Hallar que tiene el mismo formato sino que no distingue mayúsculas de minúsculas.

Ejemplo: 1. A partir del dato que se encuentra en la celda A2, indicar la ubicación de la letra G

Solución: 1. Ubicar el cursor en la celda C2. 2. Digitar la formula =ENCONTRAR("G",A2,1)

A partir del dato que se encuentra en la celda A2, indicar la ubicación del primer espacio en blanco.

1. Ubicar el cursor en la celda C2. 2. Digitar la formula =ENCONTRAR(" ",A2,1)


Pág. 20


A partir del dato que se encuentra encuentra en la celda A2, indicar la ubicación del segundo espacio en blanco.

1. Ubicar el cursor en la celda C2. 2. Digitar la formula =ENCONTRAR(" ",A2,ENCONTRAR(" ",A2,1)+1)

Ejercicio: Según la figura, presentar el nombre, el apellido paterno y materno. En este caso el nombre completo se encuentra en la celda A49.

1. El Nombre: Ubicar el cursor en la celda B51 y digitar la formula:

=IZQUIERDA(A49,HALLAR(" ",A49,1)-1) 2. El Apellido Materno: Ubicar el cursor en la celda B53 y digitar la siguiente formula:

=DERECHA(A49,LARGO(A49)-HALLAR(" ",A49,HALLAR(" ",A49,1)+1)) 3. El Apellido Paterno: Ubicar el cursor en la celda B52 y digitar la siguiente formula:

=EXTRAE(A49,HALLAR(" ",A49,1)+1,LARGO(A49)-LARGO(B51)-LARGO(B53)-2)

La función COINCIDIR ubica un elemento dentro de un rango de celdas y nos devuelve su posición. En otras palabras, la función COINCIDIR nos ayuda a obtener el número de fila que ocupa el elemento buscado.


Pág. 21


Sintaxis de la función COINCIDIR: = COINCIDIR(Valor buscado, Matriz buscada (obligatorio), Tipo_de_coincidencia) Tipo_de_coincidencia)

La función COINCIDIR tiene tres argumentos: 1. Valor buscado (obligatorio): El valor que estamos buscando. 2. Matriz buscada (obligatorio): El rango de celdas donde realizaremos la búsqueda. 3. Tipo_de_coincidencia (opcional): Un número que indica el tipo de coincidencia en la búsqueda.

Los siguientes valores son los posibles para el tercer argumento de la función COINCIDIR: 1:

La función COINCIDIR encontrará el valor más grande que sea menor o igual al valor buscado.

La matriz de búsqueda debe estar ordenada de manera ascendente. 0:

La función COINCIDIR encontrará el primer valor que sea exactamente igual al valor buscado.

La matriz de búsqueda puede estar en cualquier orden. -1:

La función COINCIDIR encontrará el valor más pequeño que sea mayor o igual que el valor

buscado. La matriz de búsqueda debe estar ordenada de manera descendente.

Cuando no se especifica el tercer argumento de la función COINCIDIR se utilizará de manera predeterminada el valor 1.

Ejemplo: A partir de los siguientes datos:

Se nos pide que al digitar un nombre de país en la celda C14, en la celda C15 se presente la ubicación de dicho país de la columna País.

1. Ubicar el cursor en la celda C14 y digitar un nombre de un país. Microsoft Excel Intermedio 2016

Pág. 22


2. Ubicar el cursor en la celda C15 y digitar la fórmula: =COINCIDIR(C14,B5:B10) De igual forma que en la celda F15 se presente la ubicación del mes, cuando se digite un nombre de mes en la celda F14.

1. Ubicar el cursor en la celda F14 y digitar un nombre de mes. 2. Ubicar el cursor en la celda F15 y digitar la fórmula: =COINCIDIR(F14,C4:F4)

Devuelve un número que indica la cantidad de caracteres que contiene una celda. Si la celda contiene una fecha, entonces devuelve el número de caracteres de su número de serie de la fecha.

Sintaxis de la función Largo: = LARGO(celda)

Ejemplo: A partir de los siguientes datos, se nos pide hallar el número de caracteres que contiene el nombre de cada país.

1. Ubicar el cursor en la celda C2. 2. Digitar la formula =LARGO(A2)

Esta función nos permite unir en una celda uno o más valores. La función recibe de 1 a 30 parámetros cada uno con el valor que deseamos unir al texto final.

Sintaxis de la función: =CONCATENAR(CELDA1, CELDA2,CELDA3,…….)


Pág. 23


Ejemplo: Los datos que a continuación se presentan deben de estar en una sola celda y con la primera

letra en mayúsculas.

Ubicar el cursor en la celda E2.

Digitar la fórmula: =CONCATENAR(NOMPROPIO(A2)," =CONCATENAR(NOMPROP IO(A2)," ",NOMPROPIO(B2)," ",NOMPROPIO(B2)," ",NOMPROPIO(C2))

Aplicación de Funciones de fecha y Hora:

La función AÑO devuelve el número de año de una fecha que sea especificada como argumento de la función.

Sintaxis de la función AÑO =AÑO(CF)

Donde CF es la dirección de la celda que contiene una fecha

La función MES devuelve el número del mes de una fecha que sea especificada como argumento de la función.

Sintaxis de la función Mes =MES(CF)



Pág. 24


La función DIA nos devuelve el número de día de una fecha y que estará expresado como un número entero entre 1 y 31.

Sintaxis de la función DIA =DIA(CF)


Esta función devuelve la fecha del sistema, siendo asi una función volátil, es decir que se debe de actualizar de manera permanente. Esta función no presenta paramentros.

Sintaxis de la función HOY() =HOY()

Esta función devuelve la fecha hora del sistema, siendo asi una función volátil, es decir que se debe de actualizar de manera permanente. Esta función no presenta paramentros.

Sintaxis de la función AHORA() =AHORA()

Ejemplos: Según los datos de la siguiente tabla rellenar las celdas con las funciones de fecha que sean necesarias.


Pág. 25

Facultad de Ingeniería Industrial y de Sistemas Fecha Actual:

Ubicar el cursor en la celda B1 y digitar la formula =HOY()

Fecha y Hora Actual:

Ubicar el cursor en la celda B2 y digitar la formula =AHORA()

Año de Nacimiento:

Ubicar el cursor en la celda A13 y digitar la formula =AÑO(E5)

Mes de Nacimiento:

Ubicar el cursor en la celda B13 y digitar la formula =MES(E5)

Dia de Nacimiento:

Ubicar el cursor en la celda C13 y digitar la formula =DIA(E5)

La función DIASEM

La función DIASEM en Excel da como resultado el día de la semana en base a una fecha. El día resultante Excel lo expresa como un número entero que va del 1 (domingo) a 7 (sábado).

Sintaxis de la función DIASEM

=DIASEM(CF, [tipo])

1. CF: Es una dirección de celda que contiene una fecha 2. tipo: Es un argumento opcional y es un número que determina el tipo de valor que debe

devolverse.

De acuerdo a la siguiente tabla es cómo se comporta el argumento tipo: Tipo

Número devuelto

1 u omitido

Números del 1 (domingo) al 7 (sábado).

2

Números del 1 (lunes) al 7 (domingo).

3


11


12

Números 1 (martes) a 7 (lunes).

13

Números 1 (miércoles) a 7 (martes).

14

Números 1 (jueves) a 7 (miércoles).

15

Números 1 (viernes) a 7 (jueves).


Pág. 26


16

Números 1 (sábado) a 7 (viernes).

17

Números del 1 (domingo) al 7 (sábado).

Esta función nos retorna una fecha resultante de sumarle tantos meses como deseamos a una fecha inicial

Sintaxis de la función FECHA.MES =FECHA.MES(CF; N° de Meses)

En Fecha pondremos la fecha a partir de la que deseamos realizar el cálculo y en Meses el número de meses a sumar a esta fecha inicial.

Ejemplo: A partir de los siguientes datos determinar la fecha de Expiración para cada producto:

Ubicar el cursor en la celda C80 Digitar la formula =FECHA.MES(A80,B80) La Función SIFECHA

Esta función devuelve la diferencia entre dos fechas, expresada en determinado intervalo.

Sintaxis de la función SIFECHA =SIFECHA(fecha_1, fecha_2, intervalo)

Donde fecha_1 y fecha_2 deben ser fechas válidas, de otra forma, obtendremos un error de tipo #¡VALOR!. *fecha_1 debe ser menor (más antigua) que fecha_2. Si no es así, se obtiene #¡NUM!


Pág. 27


El argumento intervalo específica la unidad de medida en la que Excel devolverá el resultado.

Puede ser uno de los siguientes valores: 1) "m" : meses. Número de meses completos entre fecha_1 y fecha_2. 2) "d" ; días. Número de días entre fecha_1 y fecha_2. 3) "y" : años. Número de años completos entre fecha_1 y fecha_2. 4) "ym" : meses excluyendo años. Número de meses entre fecha_1 y fecha_2.. 5) "yd" : días excluyendo años. Número de días entre fecha_2 y fecha_2. 6) "md" : días excluyendo meses y años. Número de días entre fecha_2 y fecha_2.

Ejemplos:

1. Numero de meses completos entre fecha inicial y fecha final Ubicar el cursor en la celda H5 Digitar la formula =SIFECHA(A2,B2,”m”) 2. Numero de días entre fecha inicial y fecha final

Ubicar el cursor en la celda H6 Digitar la formula =SIFECHA(A2,B2,”d”) 3. Número de años completos entre fecha inicial y fecha final

Ubicar el cursor en la celda H7 Digitar la formula =SIFECHA(A2,B2,”y”) 4. Numero de meses excluyendo años entre fecha inicial y fecha final final

Ubicar el cursor en la celda H8 Digitar la formula =SIFECHA(A2,B2,”ym”) 5. Número de días excluyendo años años entre fecha inicial y fecha final final

Ubicar el cursor en la celda H9 Digitar la formula =SIFECHA(A2,B2,”yd”) 6. Número de días excluyendo meses y años años entre fecha inicial y fecha final final

Ubicar el cursor en la celda H10 Microsoft Excel Intermedio 2016

Pág. 28


Digitar la formula =SIFECHA(A2,B2,”md”)

Devuelve la hora de un valor de hora. La hora se expresa como número entero, comprendido entre 0 (12:00 a.m.) y 23 (11:00 p.m.). Sintaxis de la función HORA

=HORA(CH) Donde CH es una celda con formato de hora

Devuelve los minutos de un valor de hora. Los minutos se expresan como números enteros comprendidos entre 0 y 59. Sintaxis de la función MINUTO

=MINUTO(CH)

Devuelve los segundos de un valor de hora. El segundo se expresa como número entero comprendido entre 0 (cero) y 59.

Sintaxis de la función SEGUNDO

=SEGUNDO(CH) Donde CH es una celda con formato de hora

Ejemplo: Determinar el pago de los trabajadores según la siguiente tabla.

1. H. Trabajadas: Ubicar el cursor en la celda C200 2. Digitar la formula: = (B200-A200)-$B$196 3. Pago : =Ubicar el cursor en la celda D200 Microsoft Excel Intermedio 2016

Pág. 29

Facultad de Ingeniería Industrial y de Sistemas 4. Digitar la formula: =C200*$B$197*24

La función SI es parte del grupo de funciones Lógicas y nos permite evaluar una condición para determinar si es falsa o verdadera.

Sintaxis de la función SI =SI(Prueba_lógica, Valor_si_verdadero, Valor_si_falso)

1. Prueba_lógica (obligatorio): Expresión lógica que será evaluada.

Para realizar la pregunta lógica podremos utilizar los siguientes operadores de comparación:

a. = para preguntar si dos valores son iguales. b. > para saber si un valor es mayor que otro. c. < para preguntar por menor. d. >= con este podremos conocer si es mayor o igual. e. <= preguntamos por menor o igual. f. <> para preguntar si dos valores son diferentes.

2. Valor_si_verdadero : El valor que se devolverá en caso de que el resultado de la Prueba_lógica sea

VERDADERO. 3. Valor_si_falso: El valor que se devolverá si el resultado de la evaluación es FALSO.

La Prueba_lógica puede ser una expresión que utilice cualquier operador lógico o también puede ser una función de Excel que regrese como resultado VERDADERO o FALSO. Los argumentos Valor_si_verdadero y Valor_si_falso pueden ser cadenas de texto, números, referencias a otra celda o inclusive otra función de Excel que se ejecutará de acuerdo al resultado de la Prueba_lógica.


Pág. 30


Ejemplo1: Presentar las palabras deudor o acreedor según sea el tipo. D=DEUDOR, A=ACREEDOR 1. Ubicar el cursor en la celda B2 2. Digitar la fórmula: =SI(A2="A","Acreedor","Deudor")

Ejemplo2: Presentar las palabras nacional o importado según sea la procedencia. N=Nacional. I=Importado.

1. Ubicar el cursor en la celda B12 2. Digitar la formula: 3. =SI(A12="N","Nacional","Importado")

Ejemplo3: Presentar el nuevo sueldo con un aumento de S/.100 en el caso que el sueldo del trabajador sea menor que el promedio, de lo contrario se mantendrá su sueldo.

1. Ubicar el cursor en la celda B22 2. Digitar la formula =SI(A22

Pág. 31

Manual - Excel Intermedio 2016.PDF

Recommend Documents