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


Se llama así cuando una segunda función SI se coloca dentro de la primera de manera que pruebe alguna condición adicional, teniendo la posibilidad de poder anidar hasta 64 funciones SI.

Ejemplo1: Según los porcentajes realizar los cálculos de la Bonificación

Perú=20% Ecuador=15%, Chile=12% Colombia=10%

1. Ubicar el cursor en la celda C83 2. Digitar la formula: 3. =SI(A83="Peru",20%*B83,SI(A83="Ecuador",15%*B83,SI(A83="Chile",12%*B83,SI(A83="Colombia ",10%*B83,0))))

Ejemplo2: Según los siguientes datos presentar el mensaje de la observación.

Nota

Observación

a) >=18 ------> EXCELENTE b) 16-17 ------> BUENO c) 14-15 ------> REGULAR d) <14 ------> DEFICIENTE

1. Ubicar el cursor en la celda B93 2. Digitar la siguiente formula:


Pág. 32


3. =SI(A93>=18,"Excelente",SI(A93>=16,"Bueno",SI(A93>=14,"Regular","Deficiente")))

La función Y es una función lógica que nos permitirá evaluar varias expresiones lógicas y saber si todas ellas son verdaderas, de tal manera que si una expresión lógica es FALSA, entonces el resultado de la función también será FALSO, en otras palabras, si todas las expresiones lógicas son verdaderas, entonces la formula también será VERDADERA.

Sintaxis de la función Y =Y(Valor_lógico1, =Y(Valor_lógico1, Valor_lógico2, Valor_lógico3,………)

Los argumentos de la función Y en Excel son los siguientes: 1. Valor_lógico1 (obligatorio): Expresión lógica que será evaluada por la función. 2. Valor_lógico2 (opcional): Expresiones lógicas a evaluar, opcional hasta un máximo de 255.

La función Y solamente regresará el valor VERDADERO si todas las expresiones lógicas evaluadas son verdaderas. Bastará con que una sola expresión sea falsa para que la función Y tenga un resultado FALSO.

A partir de los siguientes datos utilizar la función Y para la verificación de los siguientes casos:

Caso1: VERIFICAR QUE LA ZONA SEA SUR Y QUE LA VENDEDORA SEA ANA. 1. Ubicar el cursor en la celda E2 2. Digitar la formula: =Y(A2="S",B2="ANA")

Caso2: VERIFICAR QUE LA ZONA SEA NORTE,VENDEDOR IRIS Y QUE EL MONTO SEA MAYOR QUE 300 1. Ubicar el cursor en la celda F2 2. Digitar la formula: =Y(A2="N",B2="IRIS",C2>300)


Pág. 33


Caso3: VERIFICAR QUE LA ZONA SEA ESTE,VENDEDOR PEDRO, Y QUE EL OBJETIVO SEA MAYOR QUE 500 1. Ubicar el cursor en la celda G2 2. Digitar la formula: =Y(A2="E”,B2="PEDRO",D2>500) =Y(A2="E”,B2="PEDRO",D2>500)

Caso4: VERIFICAR QUE LA ZONA SEA OESTE, VENDEDOR JUAN,Y QUE EL MONTO SEA MAYOR QUE EL OBJETIVO 1. Ubicar el cursor en la celda H2 2. Digitar la formula: =Y(A2="O",B2="JUAN",C2>D2) =Y(A2="O",B2="JUAN",C2>D2)

La función O es una de las funciones lógicas de Excel y como cualquier otra función lógica solamente devuelve los valores VERDADERO o FALSO después de haber evaluado las expresiones lógicas que se hayan colocado como argumentos.

La función O en Excel nos ayudará a determinar si al menos uno de los argumentos de la función es VERDADERO y si es así, entonces el resultado de la formula también será VERDADERA y será falsa solamente en el caso en el cual todas las expresiones lógicas resulten FALSAS.

Sintaxis de la función O =O(Valor_lógico1, Valor_lógico2, Valor_lógico3,………) Valor_lógico3,………)

1. Valor_lógico1 (obligatorio): Expresión lógica que será evaluada por la función. 2. Valor_lógico2 (opcional): A partir del segundo argumento las expresiones lógicas a evaluar con

opcionales hasta un máximo de 255.


Pág. 34


La única manera en que la función O devuelva el valor FALSO es que todas las expresiones lógicas sean falsas. Si al menos una expresión es verdadera entonces el resultado de la función O será VERDADERO.

A partir de los siguientes datos utilizar la función O para la verificación de los siguientes casos:

Caso1: VERIFICAR QUE LA ZONA ZONA SEA NORTE O QUE LA VENDEDOR SEA Pedro. 1. Ubicar el cursor en la celda E18 2. Digitar la formula: =O(A18="N",B18="PEDRO")

Caso2: VERIFICAR QUE LA ZONA SEA SUR, O QUE LA VENDEDORA SEA IRIS O QUE EL MONTO SEA MENOR QUE 300 1. Ubicar el cursor en la celda F18 2. Digitar la formula: =O(A18="S",B18="IRIS",C18<300)

Caso3: VERIFICAR QUE LA ZONA SEA OESTE, O QUE EL VENDEDOR SEA PEDRO, O QUE EL OBJETIVO SEA MENOR QUE 500 1. Ubicar el cursor en la celda G18 2. Digitar la formula: =O(A18="O”,B18="PEDRO",D18<500)

Caso4: VERIFICAR QUE LA ZONA SEA ESTE, O QUE EL VENDEDOR SEA JUAN,O QUE EL MONTO SEA MENOR QUE EL OBJETIVO 1. Ubicar el cursor en la celda H18 2. Digitar la formula: =O(A18="O",B18="JUAN",C18
A continuación se presentan los resultados:


Pág. 35


Aplicación de LA FUNCION SI con la función Y

A partir de los siguientes datos:

Ejemplo1: Bonificar con el 15% del sueldo a toda persona de estado civil casado "C" y que tenga

menos de cuatro hijos, en caso contrario presentar el mensaje "NTB1".

Estos cálculos realizarlos en la columna “BONIF1”. 1. Ubicar el cursor en la celda E36. 2. Digitar la formula: =SI(Y(C36="C",B36<4),15%*D36,"NTB1")

Ejemplo2: Bonificar con el 5% del sueldo a toda persona de estado civil soltero="S" y que gane menos

que 2000, en caso contrario presentar el mensaje "NTB2" Estos cálculos realizarlos en la columna “BONIF2”.

1. Ubicar el cursor en la celda F36. 2. Digitar la formula: =SI(Y(C36="S",D36<2000),10%*D36,"NTB2")


Pág. 36

Facultad de Ingeniería Industrial y de Sistemas Aplicación de LA FUNCION SI con la función O

A partir de los siguientes datos:

Ejemplo1: Bonificar con el 10% del sueldo a toda persona de estado civil casado="C" O que gane

menos de S/.2000, en caso contrario presentar el mensaje "NTB1".

1. Ubicar el cursor en la celda E59. 2. Digitar la formula: =SI(O(C59=”C”,D59<2000),D59*10%,”NTB1”)

Ejemplo2: Bonificar con el 5% del sueldo a toda persona de estado civil soltero="S" O que tenga más

de cuatro hijos, en caso contrario presentar el mensaje "NTB2".

1. Ubicar el cursor en la celda E59. 2. Digitar la formula: =SI(O(C59=”S”,B59>4),D59*5%,”NTB2”)

La función BUSCARV busca un valor de forma vertical dentro de la primera columna de una tabla y devuelve el valor que ha sido encontrado o un error #N/A si es que no se encuentra en la tabla de datos.

Sintaxis de la función BUSCARV =BUSCARV(valor_buscado, =BUSCARV(valor_buscado, rangodedatos, valor_devuelto, [aproximado o Exacta])

Parámetros de la función BUSCARV 1. Valor_buscado: El primer parámetro de la función BUSCARV es el valor que se va buscar. Microsoft Excel Intermedio 2016

Pág. 37


Regularmente se trata de una clave, código, valor, indicador, con el que realizaremos la búsqueda. 2. Rangodedatos: El segundo parámetro de la función es el rango de datos. Es decir es la tabla, rango,

base de datos, donde se hará la búsqueda. 3. Valor_devuelto: El tercer parámetro es muy importante, porque indica la columna que Excel

regresará como resultado. En otras palabras es el número de columna de nuestra matriz donde se encuentra el resultado que deseamos obtener. 4. [aproximado o Exacta]: Finalmente el cuarto parámetro indica el tipo de coincidencia, si deseamos

una coincidencia Aproximada, entonces este parámetro no se considera, pero si deseamos una coincidencia exacta entonces se convierte en un parámetro obligatorio y se tendrá que escribir la palabra Falso o digitar el digito “0” (Cero). Se tienen los siguientes datos (BUSQUEDA APROXIMADA)

Se nos pide que al digitar un código valido en la celda N2 por ejemplo el código ADM0031, en las celdas subsiguientes se presentes sus otros datos.

1. Ordenar los datos por la primera columna. (No es obligatorio pero esta acción acelera la búsqueda en grandes listas de datos, además que este primer ejercicio se realizara con una búsqueda aproximada). 2. Ubicar el cursor en la celda N2 y digitar el dato que se utilizara para la búsqueda, en este caso es el código ADM0031. 3. Ubicar el cursor en la celda N4 y digitar: =BUSCARV(N2,A2:K440,1) 5. Ubicar el cursor en la celda N5 y digitar: =BUSCARV(N2,A2:K440,2) 6. Ubicar el cursor en la celda N6 y digitar: =BUSCARV(N2,A2:K440,3) 7. Ubicar el cursor en la celda N7 y digitar: =BUSCARV(N2,A2:K440,4) 8. Ubicar el cursor en la celda N8 y digitar: =BUSCARV(N2,A2:K440,5) 9. Ubicar el cursor en la celda N9 y digitar: =BUSCARV(N2,A2:K440,6) Microsoft Excel Intermedio 2016

Pág. 38


10. Ubicar el cursor en la celda N10 y digitar: =BUSCARV(N2,A2:K440,7) 11. Ubicar el cursor en la celda N11 y digitar: =BUSCARV(N2,A2:K440,8) 12. Ubicar el cursor en la celda N12 y digitar: =BUSCARV(N2,A2:K440,9) 13. Ubicar el cursor en la celda N13 y digitar: =BUSCARV(N2,A2:K440,10) 14. Ubicar el cursor en la celda N14 y digitar: =BUSCARV(N2,A2:K440,11)

El código anterior es útil cuando el código digitado en la celda N2, exista en la lista de datos, ya que si se digita un código que no exista en la lista de datos, entonces Excel presentara un dato que sea próximo o parecido al dato digitado.

Se tienen los siguientes datos (BUSQUEDA EXACTA)

La única diferencia es que se le agrega un cero (“0”) al final de cada formula

1. Ordenar los datos por la primera columna. (No es obligatorio pero esta acción acelera la búsqueda en grandes listas de datos.) 2. Ubicar el cursor en la celda N2 y digitar el dato que se utilizara para la búsqueda, en este caso es el código ADM0031. 3. Ubicar el cursor en la celda N4 y digitar: =BUSCARV(N2,A2:K440,1,0) 5. Ubicar el cursor en la celda N5 y digitar: =BUSCARV(N2,A2:K440,2,0) 6. Ubicar el cursor en la celda N6 y digitar: =BUSCARV(N2,A2:K440,3,0) 7. Ubicar el cursor en la celda N7 y digitar: =BUSCARV(N2,A2:K440,4,0) 8. Ubicar el cursor en la celda N8 y digitar: =BUSCARV(N2,A2:K440,5,0) 9. Ubicar el cursor en la celda N9 y digitar: =BUSCARV(N2,A2:K440,6,0) 10. Ubicar el cursor en la celda N10 y digitar: =BUSCARV(N2,A2:K440,7,0) 11. Ubicar el cursor en la celda N11 y digitar: =BUSCARV(N2,A2:K440,8,0) 12. Ubicar el cursor en la celda N12 y digitar: =BUSCARV(N2,A2:K440,9,0) 13. Ubicar el cursor en la celda N13 y digitar: =BUSCARV(N2,A2:K440,10,0) 14. Ubicar el cursor en la celda N14 y digitar: =BUSCARV(N2,A2:K440,11,0) Consulta Aproximada =BUSCARV(N2,A2:K440,1) Consulta Exacta =BUSCARV(N2,A2:K440,1,0 =BUSCARV(N2,A2:K440,1,0)

Busca un valor dentro de una fila y regresa el valor en la misma posición de una segunda fila. Siempre busca en la primera fila del rango especificado. Sintaxis de la función BUSCARH

=BUSCARH(valor_buscado, rango, valor_devuelto, [aproximado]) Microsoft Excel Intermedio 2016

Pág. 39

Facultad de Ingeniería Industrial y de Sistemas 1. valor_buscado (obligatorio): Valor que se buscará en el rango 2. rango (obligatorio): El rango de celdas que contiene la fila de valores y la fila de resultados. 3. valor_devuelto (obligatorio): Número de fila (dentro del rango) que contiene los resultados. 4. aproximado (opcional): Indica si será una coincidencia aproximada. Si se omite se toma como

verdadero.

Con los siguientes datos y se nos pide que al digitar el nombre del mes , en este caso en la celda H2(Marzo por ejemplo) se pueda sus datos subsiguientes. subsiguientes.

1. Ubicar el cursor en la celda H2 y digitar el dato que se utilizara para la búsqueda, en este caso es el nombre del mes “Marzo”. 2. Ubicar el cursor en la celda H3 y digitar: =BUSCARH(H2,B2:E11,2,0) 3. Ubicar el cursor en la celda H4 y digitar: digit ar: =BUSCARH(H2,B2:E11,3,0) 4. Ubicar el cursor en la celda H5 y digitar: =BUSCARH(H2,B2:E11,4,0) 5. Ubicar el cursor en la celda H6 y digitar: =BUSCARH(H2,B2:E11,5,0) 6. Ubicar el cursor en la celda H7 y digitar: =BUSCARH(H2,B2:E11,6,0) 7. Ubicar el cursor en la celda H8 y digitar: =BUSCARH(H2,B2:E11,7,0) 9. Ubicar el cursor en la celda H9 y digitar: =BUSCARH(H2,B2:E11,9,0) 10. Ubicar el cursor en la celda H11 y digitar: =BUSCARH(H2,B2:E11,10,0)


Pág. 40


3. Listas Personalizadas, Ordenar y filtrar

Son listas de datos que se encuentran encuentran almacenadas en la aplicación y que se pueden pueden utilizar en cualquier libro. Por ejemplo si Ud. en la celda A1 digita Enero, y luego desde el vértice inferior derecho de la celda arrastra hacia abajo con el botón rellenar hacia celdas que se encuentran por debajo hasta la c elda A1 se notara que de manera automática las celdas se habrán rellenado con los correspondientes meses para cada celda.

1. Activar el mouse en botón Archivo.

2. Activar luego en “Opciones”.


Pág. 41


3. Se presenta la ventana opciones de Excel, donde activamos el mouse en “Avanzadas”

4. Luego desplazamos la ventana hasta llegar al final y lograr visualizar el botón “Modificar listas personalizadas….” Que es donde activamos el mouse.


Pág. 42


5. Luego activamos el mouse en la ventana “Entrada de Lista” y digitamos los datos, pulsando Enter al finalizar con cada uno. Una vez finalizada con el ingreso de los datos, se activa el mouse el botón “Agregar” y después en el botón “Aceptar” hasta llegar a la hoja de Excel. 6. Para realizar una prueba digite la palabra Radio en la celda A1 y luego arrastre y suelte en la celda A6.

Procedimiento que se utiliza hacer que destaque una o más celdas ya sean su contenido, o su relleno con un color o figura cuando cumpla una condición. Formato Condicional Rápido

En este ejemplo, vamos a usar el formato condicional para resaltar el nombre del vendedor “Pedro” en color rojo. 1. Seleccione las celdas a las que desea aplicar formato condicional, en esta caso seleccionamos la columna “Vendedor”. 2. En la Cinta Inicio, active el mouse en Formato condicional > Resaltar reglas de celdas > Texto que contiene.


Pág. 43


3. En la ventana Texto que contiene, a la izquierda, escriba el texto que desea resaltar.

4. En el lado derecho, seleccione el formato de color para el texto y, a continuación, active el mouse en Aceptar.


Pág. 44


El texto seleccionado se resalta en toda la hoja de cálculo.

Se realiza este procedimiento cuando se tenga que asignar un color específico para un dato según cumpla una condición. Ejemplo1: En la columna ZONA asignar los siguientes colores según el detalle que se observa en el comentario de la celda A1.

1. Seleccione el rango de celdas al que desea aplicar formato condicional. 2. En la Cinta Inicio, active el mouse en Formato condicional > Nueva regla.

3. En la ventana Nueva regla regla de formato, active el mouse en Aplicar formato únicamente a las las celdas que contengan.


Pág. 45


4. Luego en la ventana donde dice “ Entre” seleccionar “Igual a” 5. En la ventana de texto que se encuentra a la derecha digitar la letra “ N”. Y luego activar el mouse en el botón “ Formato” Luego se presenta la ventana “ Formato de Celdas” donde con la ficha “Fuente” activa se activa el mouse en cuadro color (donde dice automático) , se escoge el color que sea necesario y luego se activa el mouse en los botones “Aceptar” de las ventanas que se presenten hasta llegar a la hoja de Excel.

Usar una fórmula para aplicar formato condicional

Se desea establecer en color rojo a todas las ventas que son menor que el promedio de VENTAS. 1. Seleccione las celdas de la columna VENTA, 2. A continuación, active el mouse en Inicio > Formato condicional > Nueva regla.


Pág. 46


1. En la ventana de diálogo Nueva regla de formato, active el mouse en Utilizar una fórmula para determinar en qué celdas desea aplicar el formato.

2. En Dar formato a los valores donde esta fórmula sea verdadera, escriba la fórmula:

3. Luego se activa el mouse en el botón con flecha de color rojo que se encuentra en el lado derecho. 4. Después activamos el mouse en el botón “Formato”, escogemos el color solicitado y activamos el mouse en el botón “Aceptar” hasta llega a la hoja de Excel. Microsoft Excel Intermedio 2016

Pág. 47


Aquí están los resultados:

Este tipo de formato le asigna una figura a cada celda figura que depende del valor que contenga dicha celda ya que en este caso Excel forma tres grupos de datos (Superior, Intermedio, Menor) 1. Se selecciona las celdas a las cuales se les va establecer el formato condicional de conjunto de iconos.


Pág. 48


2. Se activa el mouse en el botón INICIO/FORMATO CONDICIONAL/CONJUNTO DE ICONOS y luego escogemos uno de los modelos disponibles, por ejemplo DIRECCIONAL.

3. Aquí los resultados:


Pág. 49


La ordenación es uno de los procedimientos importantes del análisis de datos. Con esta funcionalidad, puede poner en orden alfabético:

a) Una lista de productos. b) Ordenar cronológicamente una serie de fechas. c) Ordenar los datos de acuerdo al color de relleno relleno de fondo o color de fuente fuente que tengan y así otros casos más de ordenación. La ordenación en Excel 2016 se puede realizar de dos formas:

A. Mediante los botones de ordenación rápida (Comandos AZ o ZA) B. Mediante el botón “Ordenar”

Ambos comandos se encuentran en la cinta DATOS tal como se ve en la figura.


Pág. 50


A continuación se presentan una lista de datos:

Se nos pide: Ordenar los datos por apellido PATERNO

Solución: 1. Se ubica el cursor en una sola celda que contenga un apellido paterno. En este caso el cursor se ha ubicado en el apellido paterno “Esparza”

2. Se activa el mouse en la cinta DATOS y luego en el comando AZ tal como se en la figura.


Pág. 51


De esta manera se ordenan los datos de una manera rápida por cualquier columna de una manera rápida.

Otra forma de realizar la ordenación puede ser:

Activar el mouse en cinta INICIO y después sobre el comando ORDENAR Y FILTRAR, luego escoger algún tipo de ordenación.

Ordenar los datos por los apellidos PATERNO y MATERNO. 1. Se ubica el cursor sobre cualquier celda que contenga un dato. 2. Se activa el mouse sobre la cinta DATOS y después sobre el comando ORDENAR.


Pág. 52


3. Se presenta la ventana de dialogo de ordenación ORDENAR

4. Se activa el mouse sobre la ventana “Ordenar por” y luego se escoge escoge el encabezado que contenga contenga el primer dato por el cual se desean ordenar los datos

En este caso seleccionamos el encabezado PATERNO 5. Luego activamos el mouse sobre el botón “Agregar Nivel”

6. Activamos el mouse en la ventana “Luego por”, escogemos la segunda columna de ordenación y finalmente activamos el mouse sobre el botón “Aceptar”.


Pág. 53


Se usa este procedimiento para ordenar datos de acuerdo a los datos que

se encuentren

almacenados en una “Lista personalizada” que previamente ha tenido que ser creada por el usuario. En este caso la “Lista personalizada” es la siguiente:

Refrigeradora, Televisor, Radio, Waflera,

Eq. Sonido, Filmadora, Licuadora,

Plancha,

Computadora.

Tener en cuenta que la ordenación de los datos se realizara de acuerdo ac uerdo a la ordenación que presenten los datos de la lista personalizada, lo cual no significa necesariamente que será una ordenación alfabética de los datos.

Se tiene la siguiente lista de datos los cuales contienen las unidades vendidas en los tres primeros meses del año.


Pág. 54


Se nos pide realizar la ordenación de estos datos, según la lista personalizada que ya se encuentra almacenada en el sistema.

Solución: 1. Se ubica el cursor en una celda de los datos a ordenar. 2. Se activa el mouse en la cinta DATOS/ORDENAR

3. Se presenta la ventana “Ordenar” donde en la lista despegable “Ordenar por”, se escoge la columna de ordenación, en este caso “Articulo”.

4. Luego se activa el mouse en la lista despegable “Criterio de ordenación” y seleccionamos “Lista personalizada”.


Pág. 55


5. Se presentara la ventana “Listas personalizadas”, donde se seleccionara los datos de la lista personalizada que se desea utilizar para la ordenación y luego se activa el mouse en el botón “Aceptar”.

6. Luego se presenta la ventana “Ordenar” donde se podrá visualizar la lista despegable una parte de los datos de la “Lista personalizada”.

7. Finalmente se activa el mouse en el botón “Aceptar” y aquí una muestra de los datos ordenados.


Pág. 56


Es el procedimiento mediante el cual se pueden seleccionar registros (filas) que cumplan con una o más condiciones:

Se tiene la siguiente lista de datos:

Se nos pide: a. Realizar una selección de los trabajadores del área de Ventas (VEN) Solución: 1. Activar el mouse sobre una celda que contenga un dato. 2. Activamos el mouse en la cinta DATOS y después en comando FILTRO.


Pág. 57


3. Luego en cada encabezado de columna se presentara una lista desplegable el cual nos va a permitir realizar el filtro o selección así como también futuras ordenaciones.

4. Activamos el mouse en el encabezado que contenga el criterio para realiza el filtro, en este caso en AREA.

Donde luego activamos el mouse la ventana “(Seleccionar todo)” (Al realizar esta acción todos los cuadros quedan desactivados) para después activar el mouse en la ventana “VEN” para finalmente activar el mouse en el botón “Aceptar”.


Pág. 58


Aquí una muestra de los resultados

1. Ubicar el cursor sobre una celda que contenga un dato. 2. Activar el mouse sobre la cinta DATOS y luego en el comando BORRAR.


Pág. 59


b. Realizar un filtro de todos los trabajadores que trabajen en la zona NORTE (“N”) y que sean de sexo femenino (“F”)

1. Se procede a crear el filtro tal como en el ejemplo anterior. 2. Activamos el mouse sobre la lista desplegable que se encuentra en el encabezado ZONA y luego seleccionamos “N”, tal como se vio en el ejemplo anterior.

3. Luego procedemos de igual forma para el encabezado SEXO. 4. Los resultados presenta los siguientes datos


Pág. 60


c. Realizar una selección de los trabajadores cuyo apellido PATERNO empieza por la letra “a”. 1. Se procede a crear el filtro. 2. Se activa el mouse sobre el encabezado PATERNO, luego en “ Filtros de texto” y después en “Comienza por…”.

3. Se presenta la ventana “Autofiltro personalizado?”, donde en la ventana que está a la derecha de “comienza por” digitamos “A*” y finalmente en el botón “Aceptar”


Pág. 61


d. Presentar una lista de todos los trabajadores que hayan ingresado a trabajar desde el año 1980 y 1990

1. Se procede a crear el filtro. 2. Se activa el mouse sobre el encabezado FECING, luego en “ Filtros de fecha” y después en “ Entre…”.

e. Realizar una lista de todos los trabajadores que ganen más de S/.4000 1. Se procede a crear el filtro. 2. Se activa el mouse sobre el encabezado SUELDO, luego en “Filtros de Numero” y después en “Mayor o igual que…”.


Pág. 62


3. Luego en la ventana “Autofiltro personalizado?” digitamos la cantidad y después activamos el mouse en el botón “Aceptar”.

4. En la ventana “ Autofiltro personalizado?” digitamos los datos y después activamos el mouse en el botón “Aceptar”.


Pág. 63


4. Esquemas y SubTotales

Se llama así a resúmenes de datos que se encuentran debidamente organizados. En una hoja solo podemos incluir un esquema, para tener más de un esquema sobre los m ismos datos, debemos copiar los datos a otra hoja.

Existen dos formas de crear un esquema en Excel 2016: Manual y Automática.

Se tienen los siguientes datos, para los cuales se pide realizar un Autoesquema, que permita formar grupos en filas por Zona, tener en cuenta que en esta lista de datos se organizan los datos por filas, donde se ubican los departamentos agrupados en tres zonas : Norte, Centro y Sur. Asimismo en las columnas tenemos las ventas en el primer Trimestre del año.

1. Ubicar el cursor en una celda que contenga un Dato. 2. En el Cinta Datos, elija Agrupar/Autoesquema.


Pág. 64


3. Los datos se presentan de la siguiente manera:

Se podrá notar que se presentan las líneas de agrupamiento de datos así como los números 1 2 en la parte superior izquierda que se usan para expandir o contraer la vista de datos.

Expandir o contraer todo el esquema hasta un nivel determinado

En los símbolos de esquema 12, activar el mouse en el número del nivel que desee. Los datos de detalle de los niveles inferiores se ocultan.

Ejemplo: Presentar los datos solamente de los totales por Zonas (Norte, Centro, Sur) 1. Ubicar el cursor en una celda que contenga un dato. 2. Activar el mouse en el número 1 que se encuentra en la parte superior izquierda.


Pág. 65


3. Ahora se presentan los datos en forma de resumen:

Para mostrar los datos completos se activara el mouse en el número 2 que se encuentra en la parte superior izquierda.

1. Utilice los símbolos de esquema 1 2,

y

para ocultar los datos de detalle que no desee copiar.

2. Seleccione el rango de filas de resumen. Microsoft Excel Intermedio 2016

Pág. 66


3. Pulse la tecla de función F5 y luego en la ventana que se presenta activar el mouse en el comando “Especial”.

4. Luego en la ventana xxx se activa el mouse en “Solo celdas visibles” (2) (2 ) y después se activa el mouse en el botón “Aceptar”.

5. Los datos presentaran unas franjas de selección


Pág. 67


6. Pulsamos las teclas CRTL+C, ubicamos el cursor c ursor donde deseamos la copia. 7. Activamos el botón derecho del mouse y luego en “pegado especial”.

8. Se presenta la ventana “Pegado Especial”, donde se activa el mouse en “Valores” y después en el botón “Aceptar”

9. Finalmente se ubica el cursor en la celda destino y se pulsa CRTL+V

1. Ubicar el cursor en una celda que contenga un dato del esquema. DATOS/DESAGRUPAR/BORRAR ESQUEMA 2. Se activa el mouse en la cinta DATOS/DESAGRUPAR/BORRAR Microsoft Excel Intermedio 2016

Pág. 68


1. Asegúrese de que cada fila de los lo s datos a los que desea aplicar un esquema tiene un rótulo en la primera fila, contiene datos similares en cada columna y el rango no tiene filas ni columnas en blanco.

2. Seleccione las columnas que formaran parte de la agrupación, en este caso es el rango B: P

3. Active el mouse en la cinta Datos/Agrupar

4. De esta forma hemos realizado nuestra agrupación de esquema de columnas Microsoft Excel Intermedio 2016

Pág. 69


Para grupo interno anidado, seleccione las columnas de detalle adyacentes a la columna que contiene la fila de resumen. 1. Seleccione las columnas para las cuales desea realizar una agrupación en forma de esquema, por ejemplo las columnas B: E (No debe de incluir la columna que incluye la fórmula de resumen).

3. Activar el mouse en la cinta Datos/Agrupar

Notas: 1. Cuando agrupe manualmente niveles de esquema, es mejor tener todos los datos mostrados para Microsoft Excel Intermedio 2016

Pág. 70


evitar agrupar las columnas incorrectamente. 2. No incluya la columna resumida en la selección. Siga seleccionando y agrupando columnas internas hasta que haya creado todos los niveles que desee en el esquema.

Procedimiento que se utiliza para crear listas de datos donde se puedan incluir operaciones por cada grupo homogéneo con datos donde se pueda realizar algún tipo de agrupamiento. Para los ejemplos utilizaremos los siguientes datos.

Ejemplo1: Realizar una lista de todos los datos donde se pueda visualizar el total de Sueldos, Bonificación, Descuentos y Total por cada AREA de trabajo. Solución:

1. Ordenar los datos por la columna AREA. 2. Activar el mouse en una celda que contenga un dato. 3. Activar el mouse mouse en la cinta Datos/ Subtotales.

Se presenta la siguiente ventana:


Pág. 71


4. En Para cada cambio en, activar el mouse en la ccolumna olumna de ordenación, en esta caso en la columna co lumna AREA.

5. En Usar función, elige la función de resumen que desees utilizar para calcular los subtotales. En este caso elige suma.

6. En Agregar subtotal ha, activa la casilla de verificación de cada columna que contenga valores cuyos subtotales desees calcular.


Pág. 72


7. Si deseas un salto de página automático después después de cada subtotal, activa la casilla de verificación Salto de página entre grupos . 8. Si deseas que los subtotales aparezcan encima de los datos en lugar de que aparezcan debajo, desactiva la casilla de verificación Resumen debajo de los datos. 9. Activar el mouse en Aceptar. En la base de datos aparecieron unos controles especiales a la derecha de la planilla, que permite hacer manejos sobre la misma. Así, en la planilla se pueden pueden leer tres niveles:

Nivel 1: muestra el total general, así:

Nivel 2: muestra los totales por cada Area y el total general, así:


Pág. 73


Nivel 3: muestra todos los datos más los totales por vendedor y el total general.

1. Ubicar el cursor en una celda con un dato. 2. En el Cinta Datos, activar el mouse en Subtotales.

Ejemplo 2: Realizar una lista de todos los datos donde se pueda visualizar el Promedio de Sueldos, Bonificación, Descuentos y Total por cada ZONA de trabajo.

1. Ordenar los datos datos por la columna ZONA . 2. Activar el mouse en cualquier celda de los datos. 3. En el Cinta Datos, activar el mouse en Subtotales.


Pág. 74


4. En Para cada cambio en, activar el mouse en producto. 5. En Usar función, elige la función promedio. 6. En Agregar subtotal ha , activa la casilla de verificación de la columna Ventas.

La ventana “Subtotales” debe quedar así:

7. Activar el mouse en Aceptar.

Para obtener subtotales anidados (subtotales de subtotales) se debe de ordenar la lista por las columnas que impliquen los niveles de Subtotales, ordenando primero por la columna de subtotales exteriores, después por la siguiente columna interior de los subtotales anidados, y así sucesivamente Ejemplo1: Realizar un listado de todos los trabajadores que permita visualizar el PROMEDIO total de sueldos por AREA y ZONA.


Pág. 75


1. Se ordena la lista primero primero por AREA y luego por ZONA. Utilice el botón ORDENAR. 2. En el Cinta Datos, activar el mouse en Subtotales. a. En Para cada cambio en, activar el mouse en la columna AREA. b. En La ventana Usar función, activar el mouse en la función suma. c. En Agregar subtotal ha , activa la casilla de verificación correspondiente a Sueldos, Bonificación, Descuentos y Total.

d. Desactivar el casillero “Reemplazar subtotales actuales”

Así queda La ventana: 3. Se procede a crear el segundo segundo Subtotal 1. En el Cinta Datos, activar el mouse en Subtotales. 2. En Para cada cambio en, activar el mouse en la columna ZONA 3. En La ventana Usar función, activar el mouse en la función suma. 4. En Agregar subtotal ha, activa la casilla de verificación correspondiente a Sueldos, Bonificación, Descuentos y Total .


Pág. 76


5. Filtros Avanzados Definición de una base de datos

Una base de datos es una colección de datos organizados de forma que una aplicación pueda almacenar, recuperar, modificar y seleccionar los fragmentos de datos que necesite.

Las bases de datos tradicionales se organizan por campos, registros y archivos. 1. Un campo es una pieza única de información. 2. Un registro es un sistema completo de campos; y un archivo es una colección de registros. Por ejemplo, una guía de teléfono es análoga a un archivo. Contiene una lista de registros, cada uno de los cuales consiste en tres campos: nombre, dirección, y número de teléfono.

1. Para los encabezados utilizar de preferencia caracteres alfanuméricos. 2. Los nombres de los lo s encabezados deben ser de pocas palabras. 3. Debe de existir un solo encabezado por columna. 4. Utilizar el guion bajo (_) para unir palabras. 5. Los nombres de los lo s encabezados deben ser descriptivos de su contenido. Microsoft Excel Intermedio 2016

Pág. 77


6. Los nombres de los lo s encabezados deben ser unívocos entre hojas. 7. Evitar celdas con mensajes de errores internos, que resultan como consecuencia del uso de fórmulas. 8. Eliminar las filas y columnas en blanco. 9. Eliminar las filas repetidas, salvo que sean necesarias. Los puntos anteriores corresponden muchos de ellos a preferencias personales, más que a reglas que debamos de cumplir, y en consecuencia muchos de ellos pueden ser pasados por alto, sin embargo, lo más importante es que la nomenclatura utilizada en nuestras listas de datos sea coherente y consistente con el fin de minimizar la posibilidad de errores.

Las listas de datos cuentan por lo general cuenta con una gran cantidad de registros, donde a su vez es necesario desplazarse sobre los mismos y por lo tanto con esta acción los encabezados de filas o columnas ya dejan de ser visibles para el usuario. Con este procedimiento estos encabezados o columnas y filas que sean necesarias se mantendrán visibles hasta cuando se considere necesario, para lo cual utilizaremos la siguiente lista de datos.

1. Ubicar el cursor en una celda con un dato. 2. Activar el mouse en la cinta “Vista” y luego en el comando “Inmovilizar” 3. Se activa el mouse en “Inmovilizar Fila Superior” o “Inmovilizar primera columna”.

1. Ubicar el mouse en la celda que corresponda en la celda que corresponda a una fila y columna de lo que se desee inmovilizar. Microsoft Excel Intermedio 2016

Pág. 78


En la figura se observa que van ser inmovilizadas 2 columnas y dos filas. 2. Se activa el mouse en la cinta VISTA y luego el comando Inmovilizar/Inmovilizar paneles.

3. Ahora se podrá desplazar tanto de forma Vertical como Horizontal y las 2 primeras columnas y filas

se mantendrán siempre visibles.

Se llama filtro "Avanzado" al procedimiento que permitan seleccionar registros que cumplan con una o más condiciones. Este procedimiento no muestra listas desplegables para las columnas. En lugar de ello, se tiene que escribir los criterios según los cuales desea filtrar los datos en un rango de criterios independiente situado sobre el rango. Para la realización de los ejemplos utilizaremos la siguiente lista de datos:


Pág. 79


Para realizar un filtro avanzado es necesario contar con 2 zonas.

1. La zona de criterio: Que puede estar formada por todos o algunos encabezados y una o más filas en

blanco.

2. La zona de datos: Que vienen a ser la lista de datos incluyendo el encabezado.

La hoja de cálculo presenta el siguiente formato:

Ejemplo1: Realizar una selección de los trabajadores del área de Administración(ADM)

1. En la zona de criterios y celda correspondiente digitar el criterio solicitado para realizar la selección, en este caso ADM.


Pág. 80


2. Activar el mouse en cinta DATOS/AVANZADAS.

3. Se presenta la ventana “Filtro Avanzado?” donde: a) En Rango de la lista, se digita el rango r ango de la lista de datos que en este caso es A7:N445 b) En Rango de criterios, se digita el rango de criterios que en este caso es A1:N2 c) Finalmente se activa el mouse en el botón “Aceptar”

1. Activar el mouse en la cinta DATOS/BORRAR.

Ejemplo2: Realizar una selección de los trabajadores ZONA norte (N) y que sean de categoría 1 (CATEGO) de Administración(ADM). Microsoft Excel Intermedio 2016

Pág. 81


1. En la zona de criterios y celda correspondiente digitar el criterio solicitado para realizar la selección, en este caso “N” y “1” en las celdas correspondientes, tal como se ve en el gráfico.


3. Se presenta la ventana “Filtro Avanzado?” donde: a) En Rango de la lista, se digita el rango r ango de la lista de datos que en este caso es A7:N445 b) En Rango de criterios, se digita el rango de criterios que en este caso es A1 :N2 c) Finalmente se activa el mouse en el botón “Aceptar”

Ejemplo3: Realizar una selección de los trabajadores área de Contabilidad(CON) o Ventas(VEN)

1. En la zona de criterios y celdas correspondiente digitar el criterio solicitado para realizar la selección, en este caso “CON” y “VEN”, tal como se ve en el gráfico.


Pág. 82



3. Se presenta la ventana “Filtro Avanzado?” donde: a) En Rango de la lista, se digita el rango r ango de la lista de datos que en este caso es A7:N445 b) En Rango de criterios, se digita el rango de criterios que en este caso es A1:N3 c) Finalmente se activa el mouse en el botón “Aceptar”

Filtros Avanzados Con Resultados en Otra Hoja

Este procedimiento permite presentar presentar los resultados del Filtro en otra hoja. Ejemplo: Presentar los datos de los trabajadores del área de Informatica(INF) o del área de Marketing (MAR) 1. Se crea la zona de criterios de preferencia en otra hoja y se digitan los datos de los criterios.


Pág. 83


2. Se activa el mouse en la cinta “Datos” y después en “Avanzadas”. 3. Se presenta la ventana “Filtro Avanzado?” donde:

a. Se activa el mouse en el botón de opción “Copiar a otro lugar” b. En rango de lista se selecciona la etiqueta de la hoja que contienen los datos y se digita el rango de los datos de la lista incluyendo el encabezado. c. En rango de Criterios se selecciona la etiqueta de de la hoja que contienen los criterios y se digita el rango de los criterios incluyendo el encabezado. d. Se activa el mouse en el cuadro “Copiar a”, se selecciona la etiqueta de la hoja donde se encuentran los criterios, luego se activa el mouse en una celda vacía donde se desea que inicie la copia de los resultados y después se activa el mouse en el botón “Aceptar”.

Para buscar valores de texto que incluyen algunos caracteres pero no otros, siga uno o varios de estos procedimientos:

Utilice ? (signo de interrogación)


Para buscar Un único carácter

Por ejemplo, Gr?cia buscará "Gracia" y "Grecia"

Pág. 84

Facultad de Ingeniería Industrial y de Sistemas * (asterisco)

~ (tilde) seguida de ? , *, o ~

Cualquier número de caracteres

Por ejemplo, *este buscará "Nordeste" y "Sudeste" Un signo de interrogación, un asterisco o una tilde

Por ejemplo, af91~? buscará "af91?".

Ejemplo: Generar una lista de todos los trabajadores cuyo sueldo sea mayor que el promedio. Para estos casos se procede de la siguiente manera: 1. Genere un encabezado en la zona de criterios que será el cual en sus celdas inferiores se digitara la formula criterio.

2. Digite la formula en este caso en la celda L2, digitamos =K7>PROMEDIO(K7:K445)

Donde K7 es la celda donde está el primer sueldo y K7:K445 el rango de todos los sueldos

3. Activamos el mouse en la cinta DATOS/AVANZADAS 4. Se presenta la ventana “Filtro Avanzado?” que es donde procedemos a digitar: a) En Rango de la lista, se digita el rango r ango de la lista de datos que en este caso es A7:N445 b) En Rango de criterios, se digita el rango de criterios que en este caso es L1:L2 L1:L2 c) Finalmente se activa el mouse en el botón “Aceptar”


Pág. 85



Pág. 86


6. Herramientas de datos

La validación de datos es el procedimiento que se usa para restringir el ingreso de datos o los valores que los usuarios pueden escribir en una celda. Por ejemplo: A) Es posible que desee restringir la entrada de datos a un intervalo determinado de datos numéricos o de fechas. B) Limitar las opciones con una lista de datos de tal manera que sólo se pueda seleccionar un dato de una lista. C) Limitar el ingreso de datos a una determinada cantidad de caracteres D) Crear una fórmula que permita la restricción cuando el dato ingresado ya no cumpla con las condiciones para que sea aceptado.

Este procedimiento de Validación de datos nos va a permitir controlar el ingreso de datos que cumplan con condiciones prestablecidas. Ejemplo: En la columna “A”, en el rango A2:A14, se ingresaran precios los cuales están comprendidos entre S/.20 y S/80.

Solución: 1. Seleccionar el rango A2:A14


Pág. 87


DATOS/VALIDACION DE DATOS. 2. Se activa el mouse en la cinta DATOS/VALIDACION

3. Se presenta la ventana “Validación de datos”, la cual a su vez contiene tres fichas. Luego con la ficha “Configuración” activa: a. Se activa el mouse en la lista despegable “Permitir” y se selecciona “Numero Entero”. b. En los cuadros “Mínimo” y “Máximo”, se digitan las cantidades que correspondan para este ejemplo, que en este caso son 20 y 80 respectivamente.

4. Luego se activa el mouse en la ficha ”Mensaje de entrada”, para poder especificar los detalles que se deben de cumplir para digitar los datos. Este mensaje se va a presentar en cada celda del rango especificado y el usuario tendrá la oportunidad de saber las restricciones para el ingreso de los mismos. Luego en la ventana “Titulo” se digita por ejemplo: “Ayuda” y en la ventana “Mensaje de entrada” un texto que indique lo que el usuario debe de realizar.


Pág. 88


5. Luego activamos el mouse en la ficha “Mensaje de error”, donde en la ventana “Titulo” por ejemplo digitamos “Error” y en la ventana “Mensaje de error” digitamos un texto tipo llamada de atención como por ejemplo “Tenga más cuidado, dato fuera de rango”

Y finalmente activamos el mouse en el botón “Aceptar”.

Luego digitamos unos datos de prueba y mientas el dato se encuentre en el rango, entonces Excel permite el dato, en caso contrario presentara la ventana con el mensaje de error que se ingresó durante la configuración.


Pág. 89


Ejemplo: En la columna “B”, en el rango B2:B14, se seleccionara un dato de una lista desplegable. Solución: 1. Tener en un rango conocido el rango de los datos de la lista.

2. Se selecciona las celdas que van a ser validadas, en este caso el rango B2:B14 DATOS/VALIDACION DE DATOS. 3. Se activa el mouse en la cinta DATOS/VALIDACION

3. Se presenta la ventana “Validación de datos”, la cual a su vez contiene tres fichas. Luego con la ficha “Configuración” activa: a. Se activa el mouse en la lista despegable “Permitir” y se selecciona “Lista”.


Pág. 90


b. Se activa el mouse en la ventana “Origen” y se selecciona el rango de los datos de la lista, que en este caso es G2:G9.

4. Luego activamos el mouse en la ficha “Mensaje de error”, donde en la ventana “Titulo” por ejemplo digitamos “Error” y en la ventana “Mensaje de error” digitamos un texto que diga por ejemplo: “No se pueden digitar datos diferentes a los de la lista”, para después activar el mouse en el botón “Aceptar” 5. Ahora, en cada celda del rango B2:B14 se podrá observar que al activar el mouse en cada celda se hará visible una lista despegable para poder escoger un dato de dicha lista.

Este procedimiento de Validación de datos nos va a permitir controlar el ingreso de una determinada cantidad de caracteres en una o más celdas. Microsoft Excel Intermedio 2016

Pág. 91


Ejemplo: Realizar una Validación de datos para asegurar que en las celdas con rangos A17:A27 se puedan digitar seis caracteres para los códigos correspondientes. 1. Se selecciona las celdas que van a ser validadas, en este caso el rango A17:A27 2. Se activa el mouse en la cinta DATOS/VALIDACION DE DATOS. 3. Se presenta la ventana “Validación de datos”, luego con la ficha “Configuración” activa: a. Se activa el mouse en la lista despegable “Permitir” y se selecciona “Longitud de texto”, para después digitar las cantidades Mínimas y Máximas.

Con la fichas “Mensaje de entrada” y “Mensaje de error” se procede de igual forma que en los casos anteriores.

Este procedimiento de Validación de datos nos va a permitir controlar el ingreso de un dato mediante una formula. Ejemplo: Realizar una Validación de datos para asegurar que en las celdas con rangos A37:A47 se puedan digitar cantidades de tal forma que ya no se pueda ingresar datos cuando el acumulado sea mayor que 1000. 1. Se selecciona las celdas que van a ser validadas, en este caso el rango A17:A27 DATOS/VALIDACION DE DATOS. 2. Se activa el mouse en la cinta DATOS/VALIDACION

3. Se presenta la ventana “Validación de datos”, luego con la ficha “Configuración” activa: a. Se activa el mouse en la lista despegable “Permitir” y se selecciona “Personalizada”, para después activar el mouse en la ventana “Formula” y digitar la formula:


Pág. 92


=SUMA($A$37:$A$47)<1000

Con la fichas “Mensaje de entrada” y “Mensaje de error” se procede de igual forma que en los casos anteriores. Aquí una muestra de la validación:

Consolidar datos es el procedimiento que consiste en combinar los valores de varios rangos de datos en uno solo. Por ejemplo, si se tiene una hoja de cálculo de ventas para cada una de los diferentes vendedores de una empresa, entonces se puede utilizar una consolidación para reunir estas cifras en una hoja de cálculo de gastos para toda la organización. Para utilizar la herramienta de consolidación de Excel, los rangos a consolidar deben tener una estructura muy similar, es decir, los mismos rótulos de filas y columnas o estar en el mismo orden. Por ejemplo: tenemos las ventas del primer semestre para cada uno de los vendedores de la compañía y deseamos calcular las ventas totales de la compañía.


Pág. 93


Si vas a combinar datos que están en la misma celda en varios rangos, puedes consolidar por posición. 1. En primer lugar, examina los datos y decide si deseas consolidarlos por posición o por categoría. 2. Configura los datos que va a consolidar.

a) Asegúrate de que cada rango de datos está en formato de lista: cada columna tiene un rótulo en la primera fila, contiene datos similares y no tiene filas o columnas en blanco.

b) Coloca cada rango en una hoja de cálculo diferente. Ej.: una hoja de cálculo diferente para cada vendedor.

c) Tener en cuenta de que cada rango tiene el mismo diseño. d) No pongas ningún dato en la hoja de cálculo donde vayas a colocar la consolidación.


Pág. 94


3. Activar el mouse en la celda superior izquierda del área donde desees que aparezcan los datos

consolidados. 4. En el menú Datos, activar el mouse en Consolidar.

Se presenta la siguiente ventana: 5. En la lista lista desplegable “Función”, activar el mouse en la función que deseas que utilice Microsoft

Excel para consolidar los datos. Ej.: sumar, contar o promediar.

6. Activar el mouse en la ventana Referencia, elige la etiqueta de hoja del primer rango que vas a

consolidar, escribe el nombre que asignaste al rango o selecciona el rango.


Pág. 95


Y, a continuación, activar el mouse en Agregar. Repite este paso para cada rango.

Después de seleccionar todos los rangos a consolidar y luego de ser agregados, la ventana “Consolidar” debe de quedar de la siguiente manera:

7. Si deseas actualizar la tabla de consolidación automáticamente cada vez que cambien los datos en

cualquiera de los rangos de origen y no estás seguro de si más tarde desearás incluir rangos diferentes o adicionales en la consolidación, activa la casilla de verificación Crear vínculos con los datos de origen. En el ejemplo, la consolidación quedará más o menos así:


Pág. 96


9. Activar el mouse en aceptar. En nuestro ejemplo: la hoja de consolidación consolidación quedará así: así:

Nota: a) Con el procedimiento de “Consolidación” es posible reunir información de hasta 255 hojas de cálculo.

Consolide los datos por categorías si desea resumir un conjunto de hojas de cálculo que tienen los mismos rótulos de filas y columnas pero organizan los datos de forma diferente. Este método combina

los datos que tienen rótulos coincidentes en cada hoja de cálculo. Se tiene los siguientes datos:

Donde los datos se encuentran en hojas de nombre ENE, FEB y MAR

1. Para consolidar los datos crearé una nueva hoja y activare el mouse en el botón Consolidar que se encuentra en la ficha Datos dentro del grupo Herramientas Herramientas de datos datos.

2. Se mostrará la ventana ventana de diálogo diálogo Consolidar en donde se deberá especificar cada uno de los rangos de las diferentes hojas. Microsoft Excel Intermedio 2016

Pág. 97


Para lo cual es necesario activar el mouse en el botón de selección de referencia referencia para seleccionar los rangos. 3. Una vez especificada la referencia se debe pulsar el botón Agregar. De la misma manera se deben agregar cada una de las hojas hasta tener todas las referencias enlistadas. Finalmente debo marcar los cuadros de selección de rótulos en la Fila superior y en Columna Izquierda de manera que los datos sean presentados adecuadamente.

Al pulsar el botón Aceptar, Excel realizará la consolidación de los datos y los colocará en la nueva hoja de Excel. De esta manera la información que antes estaba en diferentes hojas de Excel ha sido consolidada correctamente en una sola hoja.


Pág. 98


Se tienen los siguientes datos: Se solicita presentar los apellidos y nombres en columnas separadas. 1. Seleccionar el texto el cual será separado en columnas. Es necesario que en el lado derecho no exista ningún tipo de dato. 2. Se activa el mouse en la cinta DATOS/TEXTO EN COLUMNAS


Pág. 99


3. Se presenta la ventana “Asistente para convertir texto en columnas – paso 1 de 3”, donde en este caso seleccionamos “Delimitados” por la razón que se pueden observar que los datos a separar tienen un espacio en blanco.

4. Luego en la sección “Separadores” seleccionamos “Espacio”, (luego de esto se podrá notar como Excel presenta líneas verticales para cada apellido y nombre) y después activamos el mouse en el botón “Siguiente”.

6. Luego de la zona “Vista previa de los datos” seleccionamos cada uno de ellos y en la ventana “Formato de los datos en columnas” activamos el mouse en la opción “Texto” que es lo que corresponde a cada uno de los datos.


Pág. 100


Luego de ello se activa el mouse en el botón “Finalizar”. Aquí una muestra de los datos:

1. Seleccionar el rango de celdas sobre el cuál se aplicará la acción de quitar duplicados.


Pág. 101


2. Activar el mouse en la ficha Datos, dentro del grupo Herramientas Herramientas de datos, activar el mouse sobre el comando Quitar duplicados.

3. Se presenta la ventana “Quitar advertencia de duplicados?” donde activamos en la l a opción o pción “Continuar con la selección actual” y después en el botón “Quitar duplicados”.


Pág. 102


7. Informes de tabla y gráficos dinámicos

Un informe de tabla dinámica es una tabla interactiva que permite realizar: a) Resúmenes o cruce de información combinando y comparando rápidamente grandes volúmenes de datos. b) Listas de datos que cumplan con una o más condiciones. c) Gráficos dinámicos.

Para los ejemplos del tema emplearemos una lista de datos, datos, la cual contiene los datos de unos clientes en diferentes distritos de Lima.

Ejemplo 1: Se pide realizar una tabla dinámica que presente en filas los distritos, en columnas las zonas y que permita presentar el total de deuda de distrito por zona. Se debe permitir filtrar los datos por el campo Sexo.

Para crear un informe de tabla dinámica debes seguir este procedimiento: 1. Activar el mouse mouse en una celda de la lista de datos. datos. 2. En el menú Datos, Activar el mouse en Informe de tablas y gráficos dinámicos .

3. Se presenta la ventana “Crear Tabla dinámica?”.


Pág. 103


Donde por defecto están seleccionadas las opciones: a) “Seleccione tabla o tango”, que a su vez debe de mostrar el rango de los datos con los cuales se creara la tabla dinámica, de no ser así hay que que repetir el procedimiento y seleccionar primero los datos. b) “Nueva hoja de cálculo” que indicara que Excel agregara una nueva hoja donde se creara la tabla dinámica. Una vez aceptadas las opciones se activa el mouse en el botón ”Aceptar”.

1. Campos de Tabla Dinámica: En esta zona se van a presentar todos los encabezados de la lista de datos, además de los encabezados de los campos que se originen como consecuencia de alguna operación. 2. Filtros: En esta zona se van a ubicar los capos que nos van a permitir filtrar los resultados de la tabla dinámica.


Pág. 104


3. Filas: En esta zona se van a ubicar los campos cuyos datos se van a presentar de forma horizontal uno por cada celda. 4. Columnas: En esta zona se van a ubicar los campos cuyos datos se van a presentar de forma vertical uno por cada celda. 5. Valores: En esta zona se van a ubicar los campos c ampos por lo general de tipo numérico con los cuales se van a realizar los cálculos.

4. Luego se arrastran y se sueltan los campos en las zonas que sean necesarias.

En este caso las ubicaciones han sido de la siguiente manera: - En la zona “FILTROS”, hemos llevado el campo “Sexo” - En la zona “FILAS”, hemos llevado el campo “Distritos” - En la zona “COLUMNAS”, hemos llevado el campo “Zona” - En la zona “VALORES”, hemos llevado el campo “Deuda”

5. Una vez obtenidos los resultados ya se podría cambiar ciertos detalles como formatos, encabezados.


Pág. 105


Cuando se crea una tabla dinámica, Excel por defecto presenta la operación de suma, pero es posible cambiar a otra que se encuentre disponible.

Ejemplo 2: Se pide realizar una tabla dinámica que presente en filas los distritos, en columnas las zonas y que permita presentar el PROMEDIO de deuda de distrito por zona. Se debe permitir filtrar los datos por el campo Sexo.

1. Se procede a crear la tabla dinámica tal como se vio en el punto anterior. 2. Se ubica el cursor sobre una celda de la tabla dinámica que contenga un dato numérico

3. Se activa el botón derecho del mouse, luego en “Resumir valores por“ y después se selecciona la función necesaria, en este caso es la función “Promedio”.

Luego se presentara la tabla dinámica dinámic a que muestra el promedio de DistritoxZona


Pág. 106


Con los resultados de la tabla dinámica es posible realizar grupos de datos, ya sea con campos de tipo Fecha o de tipo Numero. Ejemplo 3: Se pide realizar una tabla dinámica que presente en filas los distritos y la fecha de vencimiento, y que permita presentar el total de deuda.

1. Se procede a crear la tabla dinámica tal como se vio anteriormente. 2. En este caso en la zona “FILA” se ubican los campos c ampos “Distrito” y “Fecha venci”.

3. Se ubica el cursor en una celda que contenga una fecha.

4. Se activa el botón derecho del mouse y después en “Agrupar”


Pág. 107


5. Se presenta la ventana “Agrupar” donde en el cuadro “Por” seleccionamos las unidades de tiempo que sean necesarias y luego activamos el mouse en el botón “Aceptar”.

6. Aquí una muestra de los resultados

Presentar Resultados de la Tabla Dinámica Como C omo Porcentajes

Con los resultados de la tabla dinámica es posible presentarlos como porcentajes ya sea de filas o columnas.


Pág. 108


Ejemplo 4: Se pide realizar una tabla dinámica que presente en filas los distritos y la zona, en columna el campo “Sexo” y que permita presentar el porcentaje de deuda por tipo de sexo en cada distrito.

1. Se procede a crear la tabla dinámica tal como se vio anteriormente. 2. En este caso en la zona “FILAS” se ubican los campos “Distrito” y “Zona”, en la zona “COLUMNAS” el campo “Sexo” y en valores el campo “Deuda”.

Los resultados por ahora son los siguientes:

3. Se ubica el cursor en una celda de la tabla t abla dinámica que contenga un dato numérico 4. Se activa el botón derecho del mouse y después en “Mostrar valores como”, para después activar el mouse en una de las opciones disponibles que en este caso es “% del total de filas”


Pág. 109


5. Aquí una muestra de los resultados

Un campo calculado, es un campo temporal que no forma parte de la lista de datos original y que nos permite realizar operaciones con los datos de la tabla dinámica. Ejemplo: A partir de la siguiente tabla dinámica, presentar una columna adicional que permita visualizar el futuro cobro del 10% por concepto de comisión aplicado al total de deuda.

1. Ubicar el cursor en una celda que contenga un dato numérico, por ejemplo en la celda B4, según como se muestra en la figura. 2. Activar el mouse en la cinta emergente “Herramientas de Tabla dinámica”/”Analizar”/”Campos Elementos y Conjuntos”/”Campo Calculado”.

3. Se presenta la ventana “Insertar campo calculado”


Pág. 110


4. Luego se activa el mouse en el cuadro “Nombre” y se digita un nombre para el campo calculado, por ejemplo “Comisión”, después en el cuadro “Formula” se digita la formula correspondiente que en esta caso es =Deuda*10% y después se activa el mouse en el botón “Aceptar”.

5. Luego la tabla dinámica presentara una nueva columna donde se podrá visualizar el cálculo del 10% con respecto del campo “Deuda”.

Una vez corregidos los rangos de la lista de datos, para que que la tabla dinámica refleje los cambios de rangos o de datos, se activa el mouse en la cinta emergente “Herramientas de tabla Microsoft Excel Intermedio 2016

Pág. 111


dinámica”/”Actualizar”/”Actualizar todo”.

Con la tabla dinámica también es posible crear listas de datos en formato de Tabla que cumplan c umplan con una o más condiciones. Por ejemplo: Realizar una lista de datos de todo el personal que pertenezcan al distrito de los OLIVOS pero de la zona SUR (S). 1. Crear una tabla dinámica que contenga en la zona de “FILAS” y “COLUMNAS” los datos de los criterios, en este caso en la zona “FILAS” se ubicara al campo “Distrito” y en la zona “COLUMNAS” se ubicara el campo “Zona”.

2. Ubicar el mouse en la celda de la tabla dinámica que corresponda a la intersección de los dos criterios.


Pág. 112


Y después se activa el mouse dos veces sobre esa celda. Al realizar esta acción Excel agregara una hoja con los datos de los clientes del distrito de los Olivos de la zona Sur

1. Se activa el mouse en una celda de la tabla dinámica.

2. Activamos el mouse en la cinta emergente “Herramientas de tabla dinámica” luego en la ficha “Analizar” y después en el comando “Grafico dinámico”.

3. Se presenta la ventana “Insertar grafico”, donde se selecciona el tipo de grafico que más convenga y luego se activa en el botón “Aceptar”.


Pág. 113


4. Se presenta el grafico junto con los datos.

5. Para mover el grafico hacia otra hoja, activamos el mouse en la cinta “ Herramientas del grafico dinámico”/”Diseño”/”Mover grafico”.


Pág. 114


6. Se presenta la ventana “Mover grafico”, luego activamos en la opción “Hoja Nueva” y después activamos el mouse en el botón “Aceptar”.

7. El grafico se presentara en una hoja completa.


Pág. 115


8. Herramientas de Colaboración y Seguridad

Para impedir que otros usuarios puedan cambiar, mover o eliminar datos de sus hojas de cálculo, Excel 2016 nos ofrece la opción de proteger una o más celdas. En Excel 2016, todas las celdas están bloqueadas por defecto, pero se podrá ingresar datos en tanto no se haya digitado una contraseña. Para nuestro ejemplo, evitaremos que las celdas con datos de los títulos de “Apersonas”, “Venta” y “Fecha Venta” puedan ser modificadas por otros usuarios 1. Seleccione las celdas que desee permitir sean modificadas por los usuarios:

2. Se activa el botón derecho del mouse y luego en “Formato de celdas…”.

3. Se presenta la ventana “Formato de celdas” donde con la ficha “Proteger” activa se desactiva el cuadro de opción “Bloqueada”.


Pág. 116


4. Ahora procedemos a proteger la hoja de tal forma que solo se pueda modificar las celdas seleccionadas, para lo cual activamos el mouse en la ficha Revisar, en el grupo Cambios, activar el mouse en el botón Proteger hoja.

4. En la nueva ventana, debe estar activa la opción “ Proteger hoja y contenido de celdas bloqueadas ” y si desea, agregue una contraseña de escritura en el campo “ Contraseña para desproteger la hoja”. Termine activando el mouse en Aceptar

5. Intente modificar las celdas de los nombres de los productos o los meses y notará que Excel le generará el siguiente mensaje


Pág. 117


6. Para regresar la hoja a su modo habitual de edición se activa el mouse en la cinta “Revisar” y luego en “Desproteger hoja”.

1. Seleccione todas las celdas de la hoja activa.

2. Activar el botón derecho del mouse y luego en “Formato de Celdas”.


Pág. 118


3. Se presenta la ventana “Formato de celdas” y luego con la ficha “Proteger” activa, desactivar la casilla “Bloqueada” y luego activar el mouse en el botón “Aceptar”.

4. Se seleccionan las celdas que contienen fórmulas que se desean ocultar.

5. Luego se activa el botón derecho del mouse y después después en “Formato de celdas”, presentándose la ventana “Formato de celdas”. 6. Se activa el mouse en la ficha “Proteger”, luego se activa el mouse en las casillas “Bloqueada” y “Oculta”, para después activar el mouse en el botón “Aceptar”.


Pág. 119


6. Activamos el mouse en la cinta “Revisar” y después en el comando “Proteger hoja”

7. Se presenta la ventana “Proteger hoja” donde se digita la contraseña y después se activa el mouse en el botón “Aceptar”.

8. En la ventana “Confirmar contraseña” se vuelve a escribir la contraseña y luego se activa el mouse en el botón “Aceptar”.

9. Si se activa el mouse en una celda que contenga una formula, esta no se podrá visualizar en la barra de fórmulas.


Pág. 120


1. Active el mouse en Archivo y luego en “Información”/”Proteger libro”/”Cifrar con contraseña”

2. Se presenta la ventana “Cifrar documento?” donde donde luego escribimos la c--ontraseña y después se activa el mouse en el botón “Aceptar”.

3. Se presenta la ventana “Confirmar contraseña” donde se digita nuevamente la contraseña y después se activa el mouse en el botón “Aceptar”.


Pág. 121


4. Cada vez que se desee abrir el archivo se presentara la ventana “Contraseña”, donde se digitara la contraseña y después se activara el mouse en el botón “Aceptar”.

5. Para eliminar la contraseña se procede de igual forma que para crearla *ino que en este caso se elimina la contraseña, y luego se guardan los cambios.

Si accedemos a un archivo Excel que se encuentra en una carpeta compartida de otro equipo o nosotros mismos estamos compartiendo nuestro documento, normalmente Excel no permite que

varias personas lo modifiquen a la vez. Esto quiere decir que, si una de ellas lo tiene abierto, al resto de usuarios con acceso les aparecerá un mensaje como el siguiente cuando traten de acceder a él:

Como bien indica el mensaje, deberíamos abrirlo como Sólo lectura, y no podríamos realizar modificaciones en él. Pero esto se puede cambiar.

Para que varias personas puedan trabajar sobre el mismo libro Excel de forma simultánea: 1. Debemos situarnos en la cinta Revisar y activar el mouse en Compartir libro, en la sección Cambios.


Pág. 122


2. Se presenta la ventana “Compartir libro”.

Donde activamos el mouse la casilla “Permitir la modificación por varios usuarios a la vez”, y activar el mouse en el botón “Aceptar”.

3. Se presenta la ventana “Microsoft Excel” donde activamos el, mouse en el botón “Aceptar”.

Ahora, desde el listado que muestra la ventana “Compartir libro” podremos controlar quién tiene abierto el documento.

4. Si seleccionamos un usuario de la lista, podremos activar el mouse el botón Quitar usuario.


Pág. 123


De esta forma la persona expulsada perderá la conexión con el archivo. Esto implica que no podrá guardar los cambios que ha realizado en el libro. Lo que sí podrá hacer, para no perder el trabajo, es guardar una copia con las modificaciones que haya realizado.

Si alguien ha sido expulsado y ha guardado una copia de sus cambios, y luego quiere incorporarlos al archivo original, sería una pérdida de tiempo volver a editar el archivo de nuevo.

Notas:

1. No es necesariamente el dueño del archivo el que puede activar el mouse al resto. Todos los usuarios del archivo están en igualdad de condiciones en este aspecto.

2. Cuando varias personas trabajan sobre un mismo archivo, ya sea de forma simultánea o no, surge una necesidad nueva: la de controlar los cambios. Poder saber en cada momento qué modificaciones ha sufrido el documento. Para ello, Excel 2016 gestiona un historial de cambios.

3. Desde la ficha “Uso avanzado” de la ventana “Compartir libro”, se puede configurar cuánto tiempo se conservará este historial, cuándo se añadirá un cambio al mismo (si cada cierto tiempo o cuando se guarda el archivo), qué cambios prevalecen ante un conflicto, etc.


Pág. 124


Si no quieres que alguna de las personas que tienen acceso al libro modifique la configuración y de esa forma se pierda el historial, deberás protegerlo.

1. Para ello nos situaremos en la ficha “Revisar”, y activar el mouse en el botón “Proteger libro compartido”.

2. Se abrirá una ventana en la que deberemos marcar la casilla Compartir con control de cambios.

Notas:

1. Si el libro aún no ha sido compartido, te permitirá incluso protegerlo bajo contraseña. 2. Se desprotege del mismo modo, mo do, aunque el botón se llamará Desproteger libro compartido. Microsoft Excel Intermedio 2016

Pág. 125


Procedimiento que nos permite cómo controlar los cambios como tal.

1. Activamos el mouse en la cinta “Revisar” y luego en el comando “Control de cambios”

2. Resaltar cambios... permite configurar Excel para que m arque con un sutil cuadro negro los l os cambios que el documento sufre. Se abrirá una ventana como la de la imagen: Resaltar cambios Es necesario activar la casilla Efectuar control de cambios al modificar si no fuese así. Luego, dispondremos de tres opciones para elegir los cambios que nos interesan:

a) En función de cuándo se han producido (desde una fecha concreta, los que están sin revisar, etc.). b) De quién ha efectuado los cambios (el nombre de la persona será normalmente el de su usuario de Windows ) c) O incluso podremos elegir resaltar los cambios efectuados en determinadas celdas, marcando la opción dónde.

Por último, hay que marcar la opción “Resaltar cambios en pantalla”. Si lo preferimos también podemos incluir en una nueva hoja los cambios realizados. El resultado será que las celdas que hayan cambiado mostrarán un sutil recuadro enmarcándolas, así como un pequeño triángulo en la zona superior izquierda de la celda.


Pág. 126


En ocasiones deseamos "hacer converger" datos provenientes de varios libros en otro libro a modo de núcleo central, de modo que en ese libro se puedan resumir y observar datos como en el c aso práctico que acabamos de desarrollar. Otra interesante posibilidad es la de poder recorrer de forma paralela el contenido de dos archivos en pantalla, con estructura similar o no, de una forma visual por parte del usuario simplemente con el

fin de cotejarlos, de compararlos. En el caso de que la estructura y disposición de datos sea la misma (no los datos sino la disposición de los mismos), puede convenir recorrer ambos documentos en pantalla de forma sincrónica (a la vez) de cara a descubrir diferencias de datos o información "a la vista".

Mediante la ficha Vista, ya sabemos que en el grupo ventana disponemos de botones que nos permiten opciones diversas. Vamos a detallarlas:

A) Nueva ventana: Abre una nueva ventana con el mismo documento actual. Esto permite que mediante el siguiente botón de organizar todo, podamos realizar por ejemplo un mosaico visualizando dos hojas diferentes del mismo libro en pantalla y recorrerlas. B) Organizar todo: Permite hacer mosaicos, mosaicos horizontales, verticales, vista en cascada de las ventanas actuales, pudiendo elegir entre visualizar todas o solamente las que se corresponden con el documento actual. Ejemplo: Presentar en pantalla las ventas de las hojas “Ana” y “Juan”


Pág. 127


1. Se activa el mouse en la cinta Vista y después en el comando “Nueva Ventana”

2. Se activa el mouse en la cinta Vista y después en el comando “Organizar todo”

3.

Se

presenta

“Organizar

la

ventanas”,

ventana donde

activamos el mouse por ejemplo en la opción “Vertical”, activamos la casilla “Ventanas del libro activo” y después después en el comando comando “Aceptar”. 4. En pantalla se podrá visualizar las dos hojas de los vendedores Ana y Juan.


Pág. 128

Facultad de Ingeniería Industrial y de Sistemas C) Inmovilizar paneles : Es la opción que permite que, estando situado en una determinada y estratégica

celda de la hoja actual en el momento de invocar a esta acción, se fijen las filas por encima de esa celda actual y las columnas por la izquierda de la celda actual, de modo que en hojas de contenidos extensos podamos recorrerlas mediante las barras de desplazamiento o bien la rueda-stick del ratón sin perder las referencias con, por ejemplo unos títulos de columna y de fila que representan a los datos entre los que buscar. Es posible inmovilizar solo las filas, solo las columnas o bien filas y columnas. Ejemplo 1: Se cuenta con los siguientes datos y lo que se solicita es “Inmovilizar primera fila”

1. Se activa el mouse en la cinta “Vista”, luego en el comando “Inmovilizar” y después en “Inmovilizar fila superior”. 2. Una vez realizada esta acción se podrá desplazar por toda la hoja de datos de forma vertical y los encabezados se mantendrán visibles. Para regresar al modo habitual de trabajo, se activa el mouse en la cinta “Vista”, luego en el comando “Inmovilizar” y después en “Movilizar paneles”.


Pág. 129


Significa poder inmovilizar a la vez un determinado número de filas y columnas. Ejemplo 2: A partir de los siguientes datos se pide inmovilizar la primera y primera columna

1. Ubicar el cursor en la celda B2 de la hoja de datos de ejemplo 2. Se activa el mouse en la cinta “Vista”, luego en el comando “Inmovilizar” y después en “Inmovilizar paneles”.

3. Una vez realizada esta acción se podrá desplazar por toda la hoja de datos de forma vertical y la

primera fila y columna se mantendrán visibles. Para regresar al modo habitual de trabajo, se activa el mouse en la cinta “Vista”, luego en el comando “Inmovilizar” y después en “Movilizar paneles”.

D) Dividir ventana : Presenta unas divisiones desplazables a punta de ratón de modo que podemos

visualizar diferentes zonas de una misma hoja. Es posible dividir la ventana, arrastrando desde los selectores de división que se encuentran sobre la barra de herramientas vertical y a la izquierda de la barra de desplazamiento horizontal. Para quitar la división se activara el mouse mo use de nuevo en este botón. Ejemplo: Crear una división horizontal a partir de la décima fila de la parte superior. 1. Activar el mouse en la celda A10.


Pág. 130


2. Activar el mouse en la cinta “VISTA” y luego en el comando “DIVIDIR”.

3. Ahora se podrá observar que se presenta una línea de división a la altura de la fila 10.

E) Ocultar ventanas: Permite ocultas ventanas para que no se muestren porque en un momento dado no nos interesa visualizarlas aunque sí tenerlas abiertas.

F) Mostrar ventanas: Presenta un panel desde el que se permite visualizar las ventanas que previamente se han ocultado.


Pág. 131


G) Ver en paralelo: Ésta es la opción que permite revisar o comparar dos documentos para, visualmente apreciar las diferencias entre ellos.

Estando situado en uno de los dos documentos (el libro anfitrión), al elegir esta opción mediante este botón, se presenta un panel ofreciendo el resto de libros abiertos actualmente para mostrar la vista en paralelo. De tener abierto solamente otro libro además del actual, directamente abre ese otro para comparar los dos.

El recorrido efectuado desde uno de ellos puede ser sincrónico o no respecto al otro libro, dependiendo del estado del siguiente botón (el que se encuentra justo debajo de éste) de desplazamiento sincrónico. 8.- Desplazamiento sincrónico: Permite llevar en paralelo o de forma independiente los desplazamientos entre dos libros que están siendo comparados. La sincronización cuando se activa es tanto para desplazamientos verticales como horizontales.


Pág. 132


9.- Restablecer posición de la ventana: De tener activada la vista en paralelo y de haber maximizado una de las dos ventanas a comparar, este botón, restablece el mosaico horizontal entre las dos vistas en proceso de comparación.

10.- Cambiar ventanas: Sirve para poner como actual cualquiera de los libros abiertos en Excel. Equivale a poder elegir de entre los libros abiertos que en las versiones anteriores ofrecía la parte inferior del menú de ventana.

El empleo de bloques tridimensionales permite realizar de forma rápida una serie de operaciones

comunes a todas las hojas seleccionadas. selecc ionadas. Supongamos, por ejemplo, que deseamos diseñar una hoja de contabilidad sencilla, que que contenga los ingresos, los gastos y los beneficios, para todos los meses del año y deseamos los datos de cada mes en una hoja; una etiqueta para enero, otra para febrero, febrero, otra para marzo... De De este modo, utilizando utilizando bloques tridimensionales, podríamos realizar operaciones dentro de una de ellas, que se aplicarían a todas las hojas que lo componen. Algunas de las operaciones que van a afectar a todo el bloque tridimensional son las siguientes: 1. Introducción de datos


Pág. 133


En el ejemplo de los apartados anteriores se observa como las tres hojas del libro presentan un formato similar.

En este caso se puede introducir dicho contenido en todas las hojas sin más que teclearlo en una de ellas. Para ello debemos crear de manera previa todas las hojas que vamos a utilizar, ordenarlas y asignarles un nombre. Una vez hecho esto, seguiremos los siguientes pasos:

hoja. 1. Activar el mouse sobre la etiqueta de la primera hoja

2. Manteniendo la tecla Mayúsculas (Shift) pulsada Activar el mouse sobre la etiqueta de la tercera hoja.

3. Introducir el contenido deseado en las celdas de cualquiera de las hojas seleccionadas. Automáticamente en las celdas de todas las hojas que componen el bloque tridimensional, aparecen los mismos contenidos. Es decir, el contenido se ha calcado a tantas hojas como tuviéramos seleccionadas.

También es posible dar formato a las celdas de una de las hojas del bloque, consiguiendo la aplicación de este formato a todas las hojas seleccionadas. Este aspecto es muy útil en el caso de que las hojas presenten una misma estructura, tal y como ocurre en el ejemplo anterior.


Pág. 134


Los pasos a seguir son: 1. Seleccionar las hojas sobre las cuales aplicar el mismo formato. 2. Aplicar en una de ellas, el formato común que queremos que afecte a todas. 3. Si se quiere aplicar un determinado formato sólo a parte de las celdas seleccionadas bastará con hacer una nueva selección antes de aplicarlo.

También se pueden emplear los bloques tridimensionales en la creación c reación de fórmulas o funciones. Por ejemplo, si se quiere calcular en una celda la suma total de los ingresos correspondientes a los años

2010 y 2012 del ejemplo anterior, los pasos a seguir son: 1. Seleccionar las hojas sobre las cuales se le va a aplicar la formula. 2. Situarse en la celda en la cual se va a introducir la fórmula, por ejemplo en la hoja 2010 y luego en la celda D3

3. Digitar la formula, en este caso:

3. Copiar la formula hacia las celdas que sean necesarias, luego se podrá observar que la formula se ha repetido en todas las hojas seleccionadas.


Pág. 135

Manual - Excel Intermedio 2016.PDF

Recommend Documents