Centro de Extensión y Proyección Social CEPS UNI

CURSO: EXCEL – INTERMEDIO OL (57174)

PROFESORA: Úrsula Becerra Flórez ([email protected])

PRIMERA PARTE

Temas a desarrollar en esta sesión:

- Intersección
- Funciones:
o INDIRECTO
o ESBLANCO
- Validación de datos:
o Validar con Listas de datos
- Filtros Avanzados
- Convertir Rango en Tabla de Base de Datos
- Insertar Tablas Dinámicas

Archivo de Trabajo de Excel: 01 Electrodomésticos - Filtro Tablas – Tablas dinámicas

OPERADORES DE REFERENCIA
Recordemos, cuáles son los operadores de Referencia:
: Rango Ejemplo: =SUMA(D11:D25)
,o; Unión Ejemplo: =SUMA(D11,D25)
Espacio Intersección
INTERSECCIÓN (Espacio)
En muchos casos, los datos que deseamos evaluar en la Hoja de Cálculo, están dados por
intersecciones:
Por ejemplo en el siguiente cuadro:

Se tiene los precios de tres artículos y tres marcas; es decir, los precios están
determinados por el artículo (filas) y por la marca (columnas).
Si se desea obtener el precio del artículo Lavadora de la marca LG, pbservamos que éste,
está determinado por la intersección del rango B6:D6 (precios de lavadoras) con el rango
C5:C7 (precios de LG).
Entonces, la fórmula para extraer el precio de una Lavadora marca LG, sería:
=B6:D6 C5:C7  730
Si damos nombres a los rangos, entonces tendríamos la fórmula:
=Lavadora LG  730
En el ejemplo anterior, averiguamos el precio de un solo artículo. Pero, qué sucede si
deseamos obtener los precios de muchos artículos como el caso del ejemplo siguiente:
En este caso, deseamos escribir una sola fórmula en la celda H13, para luego poder
copiarla hacia abajo.
Como podemos apreciar, se trata de distintos artículos y distintas marcas
Entonces, vamos a trabajar con Intersección, pero con la ayuda de la función INDIRECTO.

FUNCIÓN INDIRECTO
Esta función, devuelve el valor o valores de una celda o rango, cuyo nombre se encuentra
expresado como un texto en el argumento Referencia:
Sintaxis
INDIRECTO(Referencia)
Pasos para aplicar en este caso la Función INDIRECTO:
- Asignar nombres a todos los rangos de Precios, a partir de los títulos de la Fila
superior y la Columna izquierda del cuadro de Precios:

a. Seleccione A4:D7
b. Ficha Fórmulas – Grupo Nombres definidos – Crear desde la selección:

c. Marcamos las casillas: Fila superior y Columna izquierda

d. Clic en Aceptar
De esta manera, todos los rangos de precios, tanto de las filas (artículos), como de
las columnas (marcas), tienen nombres.
 Y si observamos los nombres de los rangos, son iguales a los
textos de las columnas E y F de la Base de datos, que
contienen los nombres de todos los artículos y marcas de
todos los pedidos.
Así por ejemplo, se tiene un rango con el nombre Lavadora y
otro con el nombre LG: Si sólo deseamos averiguar el precio
de un artículo y una marca por ejemplo Lavadora LG:

Entonces, la fórmula sería: =Lavadora LG  730.00

O, si se desea extraer el precio de un Refrigerador de marca Samsung:

Entonces, la fórmula sería: =Refrigerador Samsung  1,160.00

Pero, la fórmula la deseamos copiar hacia abajo para el resto de Pedidos. Veamos
entonces cómo utilizamos la Función INDIRECTO:
En E13 se encuentra el texto Lavadora y, en la celda F13 se encuentra el texto LG.
Por tanto, la Fórmula de Precio en la celda H13 sería:
=INDIRECTO(E13) INDIRECTO(F13)

El Precio, se obtiene mediante la Intersección de los rangos cuyos nombres se

encuentran en las columnas E y F. Esta fórmula sí la podemos copiar para el resto de
Pedidos.
FILTRO AVANZADO
Los filtros avanzados, permiten trabajar con criterios más complejos y da la posibilidad de
copiar el resultado del Filtro a otro lugar.
Archivo de trabajo de Excel: 01 Electrodomésticos - Filtro Tablas
Elementos para un Filtro Avanzado
A. El rango de la Lista o Base de Datos:

B. Un rango de criterios:

C. Área de Extracción: Son los nombres de los campos debajo de los cuales se copiarán
las filas o registros que cumplan con el o los criterios:
PROCEDIMIENTO:
- Dar nombre a la Lista o Base de Datos, Por ejemplo: Lista
- Crear el rango de criterios
Por ejemplo, se desea mostrar los registros (Pedidos) en los cuales la Tienda sea igual a
Carsa, entonces nuestro rango de criterios debe ser:

- Dar nombre al rango de criterios; por ejemplo CritTienda

- Crear el Área de Extracción
- Debemos copiar los nombres de los campos (Títulos), que se desea mostrar:

- Dar nombre al Área de Extracción; por ejemplo, ExtraerCopia

Para filtrar, realice lo siguiente:
- Ficha Datos – Grupo Ordenar y Filtrar – Avanzadas

- En el cuadro de arriba, marque el botón: Copiar a otro lugar, para que la lista se
copie al área de extracción. Si no lo marcamos, se filtrará en la misma base de datos.
- Luego, ingrese los nombres de los 3 rangos:
o Lista
o CritTienda
o ExtraerCopia
- Finalmente, debemos hacer clic en el botón Aceptar
En el Área de extracción, se copiarán solo los registros (pedidos) de la Tienda Carsa:

ALGUNOS ALCANCES SOBRE EL RANGO DE CRITERIOS

- Cuando hay varios criterios de Tipo Y, se escriben en la
misma fila.
Por ejemplo, se desea mostrar los pedidos de Carsa de
Barranco.

Pero cuando los criterios son de tipo O, se escriben en filas

diferentes. Por ejemplo, se desea mostrar los pedidos de las tiendas
Carsa y Elektra. El rango de criterios debe ser
Y, se lee: Tienda igual a Carsa o Tienda igual a Elektra
También se puede combinar criterios de tipo Y, con criterios de tipo O.
Ejemplo:
En este caso se mostrarán los pedidos de Carsa
de Barranco, como los pedidos de Elektra de
Los Olivos

Si, se desea mostrar los pedidos de los distritos que comiencen con San:
Se escribe San*

El asterisco (*), es un carácter comodín que reemplaza a

 cualquier caracter o caracteres.

EXTRAER REGISTROS ÚNICOS

Esta opción sirve para extraer registros que no se repitan. Por ejemplo, en la Base de datos
de los Pedidos de Electrodómesticos, hay 3 tiendas: Carsa Elektra e Hiraoka. Pero que se
repiten muchas veces.
Tiendas Pero, lo que se desea, es extraer de la Lista, no 90 tiendas sino, solo una de
Carsa
Elektra
cada una.
Hiraoka Como se solicita TODAS las tiendas, entonces, no hay criterio.
Procedimiento:
Rango de Criterios:
Como NO hay criterio, entonces el rango CritTienda, la celda donde debe ir el
criterio propiamente, debe quedar en blanco: tienda igual a todas.
Área de Extracción:
Como el área de extracción va a contener solamente un campo, entonces copiamos el
título Tienda a una celda vacía
A la celda R12, le podemos dar un nombre por ejemplo:
ExtraerTiendasÚnicas

Para Extraer las tiendas únicas:

- Ficha Datos – Grupo Ordenar y Filtrar – Avanzadas

- Marque el botón Copiar

a otro lugar
- Ingrese los rangos
respectivos:
 Lista
 CritTienda
 ExtraerTiendasÚnicas
- Marcar la casilla Sólo
registros únicos y clic en
Aceptar.
El Resultado será:
CONVERTIR UN RANGO EN TABLA (de Base de Datos)
- Seleccione la Lista o Base de datos con los títulos
- Ficha Insertar – Tablas – Tabla

- Deje marcada la casilla La tabla tiene encabezados (Nombres de campos)

- Aceptar
INGRESAR NUEVOS REGISTROS
- Haga clic en el último dato de la tabla
- Ingrese nuevos registros, pulsando la tecla Tab
AGREGAR FILA DE TOTALES
- Haga clic en debajo del último dato de la tabla
- Luego haga clic en la herramienta Autosuma
INSERTAR CUADROS DE SEGMENTACIÓN DE DATOS
Sirve para filtrar tablas de bases de datos con mucha facilidad
- En la Ficha Diseño – Herramientas – Insertar Segmentación de Datos
- Marque las casillas de los campos por los que
desea filtrar.
- Use los cuadros de segmentación para filtrar
FUNCIONES ES
Estas funciones, comprueban el contenido de una referencia y devuelven los valores
lógicos VERDADERO o FALSO, dependiendo del tipo de dato que se encuentra en una
referencia.
Sintaxis
FUNCION_ES(Referencia)
LISTADO DE FUNCIONES ES:
ESNUMERO Comprueba si un valor es un número
ESTEXTO Comprueba si un valor es un texto
ESNOTEXTO Comprueba si un valor no es texto
ESBLANCO Comprueba si se refiere a una celda vacía
ES.PAR Devuelve Verdadero es el número es par
ES.IMPAR Devuelve Verdadero es el número es impar
ESLOGICO Comprueba si un valor es un Valor Lógico
ESERR Comprueba si un valor es un error excepto #N/A
ESERROR Comprueba si un valor es un error
ESNOD Comprueba si un valor de error es #N/A
ESFORMULA Comprueba si un valor es una fórmula
Veamos algunos ejemplos:

TABLAS DINÁMICAS
Archivo de trabajo de Excel: 01 Electrodomésticos - Tablas dinámicas

Cuando se tienen listas grandes de datos, la manera más práctica y potente de analizar y
resumir esta lista es haciendo uso de las llamadas Tablas Dinámicas; y a pesar de lo
potente que es esta herramienta, su creación es muy sencilla.
PROCEDIMIENTO
- Primero, convertiremos la Lista en Tabla
- Luego, clic en una celda de la Tabla
- Ficha Insertar – Tablas – Tabla Dinámica

Observe que en la opción Seleccione una Tabla o rango ya se muestra Tabla1, que es el
rango que contiene los datos de todos los pedidos.
- En la opción Elija dónde desea colocar el informe de tabla dinámica, dejamos
marcado el botón Nueva hoja de cálculo
- Hacemos clic en Aceptar.

A la izquierda de esta ventana se observará un esquema de cómo se verá la tabla dinámica

y que elementos deberá tener en su interior. En esta posición, se ubicará la Tabla
Dinámica.
A la derecha, aparece un Panel, el cual muestra en la parte superior la lista de campos de
la Base de Datos y, en la parte inferior las Áreas de la Tabla dinámica
Veamos un ejemplo:

En la Tabla Dinámica anterior, observemos los campos que se encuentran en cada Área

Áreas de una Tabla Dinámica:

• Columnas Campo Tienda
• Filas Campo Vendedor
• Valores Campo Monto (Total)
• Filtro del Informe No se muestra (más adelante lo usaremos)

- Del Panel superior, arrastre los campos que desea a las distintas áreas
Filtrar la Tabla Dinámica con los campos de Fila o Columna
Puede realizar filtros en los campos de Fila o de Columna.
Por ejemplo, si es que deseamos que la tabla anterior oculte los pedidos de la marca LG y
deje visible los pedidos de las marcas Daewoo y Samsung.
Procedimiento:

- Hacemos clic en el botón del filtro Marca y desmarcamos la casilla de la marca LG.
- Luego hacemos clic en Aceptar.

Área de Filtros del Informe:

En la parte superior de la tabla aparecen los campos Mes, Tienda y Artículo. Estos
permitirán realizar filtros en la tabla. Así, podríamos filtrar aquí el Mes de Abril, y entonces
la tabla mostrará resultados de los pedidos únicamente en ese Mes. Y lo mismo podríamos
hacer filtrando la Tienda y el Artículo si se desea.
TRABAJAR CON VARIOS NIVELES
Puede tener varios campos en una misma área como distintos niveles. Veamos el siguiente
ejemplo:

Crear Grupos
Ejemplo, crear varios grupos de distritos. Cada distrito conformará una Zona

- Seleccione los datos que desea agrupar, por ejemplo los distritos Barranco y San
Isidro
- Haga clic secundario en cualquiera de los datos (distritos) seleccionados
- Elija la opción Agrupar
Agregar subtotales a los grupos:
- Clic secundario en cualquier Grupo – Agregar subtotal
Mostrar Detalles
- Haga doble clic en uno de los valores de la Tabla dinámica

Los detalles se muestran en una nueva hoja de cálculo y se muestra como una Tabla
- Puede insertar una fila de Totales al final, como se muestra en la figura anterior.
Para ello, haga clic en la celda siguiente a la última celda de la tabla
- Luego haga clic en Autosuma.
Actualizar una Tabla dinámica

En las celdas de la Tabla Dinámica no existen fórmulas; por tanto, los resultados no se
recalcularán automáticamente cuando los datos de la Lista de Pedidos sean modificados o
cuando agregue nuevos registros.

El recálculo en la Tabla Dinámica, deberá hacerse manualmente, y para esto:

- Haga clic en una celda dentro de la Tabla Dinámica

- ficha Analizar de Herramientas de tabla dinámica, grupo Datos,
- Haga clic en el comando Actualizar, o del menú contextual de la Tabla elegir la
opción Actualizar.

Actualizar al abrir el archivo:

- Haga clic secundario en cualquier parte de la Tabla dinámica
- Elija Opciones de Tabla dinámica

- En la Ficha Datos, marque la casilla Actualizar al abrir el archivo

 ANALIZAR CON UN GRÁFICO DINÁMICO
- Clic en la tabla dinámica
- Ficha Analizar – Herramientas – Gráfico Dinámico:

También puede filtrar una Tabla o Gráfico dinámico, utilizando Segmentación de datos.
 SEGUNDA PARTE

Temas a desarrollar:
- Pegar Valores
- FUNCIONES:
o BUSCARV
o ESPACIOS
o SUSTITUIR
o DESREF
o COINCIDIR
o INDICE
o BUSCARV (Búsqueda por aproximación)
o BUSCAR
- CONSOLIDAR DATOS
Archivo de Trabajo de Excel: 02 Venta de Vehículos
DESARROLLO DEL EJERCICIO

Se trata de una Lista o Base de Datos que almacena información sobre las ventas de 4
tipos de vehículos de 3 marcas, en Lima y Provincias. Hasta la actualidad, cuenta con 46
registros (ventas).
En este listado, falta completar la información de 3 campos: La Comisión que recibirá cada
vendedor, El Monto Total de la Venta y el Precio de la Póliza de Seguro. Esta información,
la vamos a extraer de los 3 cuadros que están ubicados en la parte superior.
COMISIÓN:
La Comisión, depende del tipo de vehículo. Por tanto la vamos a resolver utilizando la
Función BUSCARV y además multiplicar por la Cantidad.
- Dar nombre al rango B4:C7: Comisiones
- En la celda I13 escribimos la fórmula:

=BUSCARV(C13,Comisiones,2,0)*H13

Al copiar la fórmula hacia abajo, observamos que en varios casos se muestra el valor
de error: #N/A (Valor de error no disponible – NOD)

Y estos resultados, se da en los casos en los cuales el tipo de vehículo (valor

buscado), es Camión y Omnibus; datos que aparentemente sí se encuentran en la
parte superior.
Lo que está ocurriendo, es que en el cuadro de Comisiones, después de algunos
nombres de vehículos hay un espacio adicional (espacios extra).

En este caso, solo hay cuatro textos y esto es muy fácil de detectar, pero podría
tratarse de cientos o miles de casos.
FUNCIÓN ESPACIOS
Esta función, elimina los espacios extra del texto, excepto el espacio normal que se
deja entre palabras. Use ESPACIOS en texto procedente de otras aplicaciones que
pueda contener un espaciado irregular.

Sintaxis

ESPACIOS(Texto)

- En la Hoja de Cálculo: Ventas

- En la celda: D4, ingresamos la fórmula:
=ESPACIOS(B4)
- Copiamos la fórmula hasta la celda B7
- Seleccionamos el rango que contiene las fórmulas: D4:D7
- Ejecutamos el comando Copiar (Ctrl + C)
- Hacemos clic en la celda: B4
- Desplegamos las opciones del comando Pegar – Pegar Valores
Con esta operación, se habrán eliminado los espacios extra del rango B4:B7
MONTO:
El Monto se calculará multiplicando Precio de la tabla de Precios de la parte superior por la
cantidad:
Los Precios dependen del tipo de vehículo y la marca (Intersección). Por tanto, para traer
el Precio, usaremos el operador Intersección y la función INDIRECTO
- Dar nombres a las columnas y filas del cuadro de precios
- En la celda J13, ingresamos la fórmula:
=INDIRECTO(C13) INDIRECTO(D13)*H13
PÓLIZA:
Los precios de las pólizas, se encuentran en el tercer cuadro de la parte superior de la Hoja
de cálculo; y, también dependen del tipo de vehículo y la marca:
Si deseamos resolver el precio de la póliza con la Función INDIRECTO, tendríamos que dar
nombres a los rangos del cuadro de los precios de las Pólizas.
El problema es que no se puede dar un mismo nombre a rangos diferentes.
Entonces, a los rangos del cuadro de las Pólizas, le daremos los nombres:
Ford1, Volvo1, Hyundai1, Automóvil1, Camión1, Camioneta1, Omnibus1.

Y luego, nos ayudaremos del operador de Concatenación (&) para resolver el ejercicio.
En la celda: K13, ingresamos la fórmula:
=INDIRECTO(C13&1) INDIRECTO(D13&1)

INDIRECTO CON SUSTITUIR

Archivo de Trabajo de Excel: 03 Equipos Móviles

Se trata de una Lista o Base de Datos que guarda la información de las ventas de Equipos
de telefonía móvil: Se vende 4 tipos de equipos y 2 planes
En este caso, los precios también están dados por la intersección de los precios de los
Equipos, con los precios de los Planes. Podríamos usar Intersección e Indirecto
Pero, la mayoría de los nombres de los Equipos y Planes, tienen espacio. Por ejemplo:
Samsung 8500 Wave; o también Plan Internet Total
Y, los nombres de rangos, no pueden tener espacios.
Procederemos de la siguiente manera:
- Seleccionamos el rango de precios B4:D8 para darles nombres:

- Ficha Fórmulas – Nombres definidos – crear desde la selección

- Marcamos las casillas: Fila Superior y Columna Izquierda

Al crear los nombres con este procedimiento, Excel habrá asignado nombres con
guiones en lugar de los espacios:
Samsung_8500_Wave; o también Plan_Internet_Total
Es decir, en lugar de los espacios, les habrá insertado un Guión bajo (_).

Pero, los nombres de los equipos y Planes de la Base de datos, tienen espacios:

Eq uip o Pla n
Sa m sung 8500 Wa ve Pre p a g o
Mo to ro la MB300 Pre p a g o
Sa m sung 8500 Wa ve Pla n Inte rne t To ta l
So ny Eric sso n Xp e ria X10 Pla n Inte rne t To ta l
Mo to ro la MB511 Pla n Inte rne t To ta l
So ny Eric sso n Xp e ria X10 Pre p a g o
Sa m sung 8500 Wa ve Pre p a g o
So ny Eric sso n Xp e ria X10 Pla n Inte rne t To ta l
Mo to ro la MB300 Pre p a g o

Entonces, vamos a utilizar la Función SUSTITUIR para sustituir los Espacios con
guiones.
FUNCIÓN SUSTITUIR
Sustituye texto_original por texto_nuevo dentro de una cadena de texto. Use
SUSTITUIR para reemplazar texto específico en una cadena de texto

Sintaxis

SUSTITUIR(texto, texto_original, texto_nuevo, [núm_de_repetición])

Ver el ejemplo en la Hoja de cálculo: SUSTITUIR

Volvemos a la Hoja de Cálculo Ventas
Los rangos de precios, tienen nombres con guiones
Ejercicio:
- En la celda G13, escriba la Fórmula que le permita traer los Precios del cuadro de
Precios.
=INDIRECTO(SUSTITUIR(D13," ","_")) INDIRECTO(SUSTITUIR(E13," ","_"))

- En la celda H13 escriba la Fórmula para calcular el Monto Total

- Guarde el archivo.
=F13*G13
Archivo de Trabajo de Excel: 04 Tabla de Sueldos Buscarv
Se tiene la siguiente Lista o Base de datos:

Se trata de una Lista o Base de Datos que guarda la información sobre los Sueldos y Bonos
que van a recibir los empleados de una empresa desde Enero hasta Junio.
ANÁLISIS DEL COMPORTAMIENTO DE LA FUNCIÓN BUSCARV DE L5
=BUSCARV(L3,Pagos,2,0)
Esta fórmula, busca el N° de DNI de L3, en la Primera columna del rango Pagos; y cuando
lo encuentra, devuelve el valor que se encuentra en la columna 2 del mismo rango.
La función BUSCARV, sólo puede devolver cualquiera de los valores que se encuentran a la
derecha del valor encontrado. En este caso, desde la columna 2 hasta la columna 9.
Veamos otro caso:
Archivo de trabajo de Excel: 05 Tabla de Sueldos COINCIDIR-DEREF

En este caso, la Base de Datos no comienza con N° de DNI, sino con los Nombres de los
empleados.
En la celda L6, para averiguar el Cargo del empleado, se encuentra la fórmula:
=BUSCARV(L3,Pagos,2,0)
El rango Pagos, es B4:I12. Comienza en la columna B, porque el valor buscado (L3), no se
encuentra en la columna A, sino en la columna B de la Base de datos.
En la Ficha del Empleado de la derecha, falta la fórmula que nos devuelva el Nombre. Pero
los nombres de los empleados, no se encuentran a la derecha, del DNI, sino a la izquierda.
Y, la Función BUSCARV, sólo puede devolver los datos que se encuentran a la derecha del
valor encontrado
Para resolver la fórmula que nos devuelva el Nombre, vamos a utilizar 2 Funciones:
COINCIDIR y DESREF.
FUNCIÓN COINCIDIR
Devuelve la posición relativa de un valor dentro de un rango o matriz. Esta función
devolverá la posición numérica.
Sintaxis
COINCIDIR(Valor buscado,Rango o matriz,Tipo de búsqueda)
Tipo de Búsqueda: Cero (0): Búsqueda exacta
Por ejemplo, se desea conocer la posición que ocupa el N° de DNI de
la celda L3 (07525864) en el rango: B4:B12 (rango con borde rojo)
Entonces:
Valor buscado: L3 (07525864)
Rango de búsqueda: B4:B12
Fórmula:
=COINCIDIR(L3,B4:B12,0)  6
Es decir el número de DNI buscado, ocupa la 6ta posición en el rango
B4:B12.

Pero, el valor que deseamos es el Nombre del empleado o empleada.

FUNCIÓN DESREF
Esta Función, devuelve el valor que se encuentra a un número de Filas y/o columnas, a
partir de una referencia inicial.
Sintaxis
DESREF(Referencia Inicial,#Filas,#Columnas)

Ejemplo: Se desea que Excel extraiga el nombre Sonia, que es el

nombre que corresponde al N° de DNI que se encuentra en L3
Entonces:
Referencia Inicial : A3 (Celda roja)
# de filas : 6
# de columnas : 0

=DESREF(A3,6,0)  Sonia
Esta fórmula devolverá el dato que se encuentra a 6 filas hacia abajo
a partir de la celda A3
Pero, no solo se desea el Nombre que se encuentra 6 filas hacia abajo; sino el nombre, que
le corresponde al número de DNI, que se encuentra en la celda L3. Es decir el nombre de
cualquier empleado.
Entonces la fórmula que nos devolverá el nombre de acuerdo al DNI de L3, será:
=DESREF(A3,COINCIDIR(L3,B4:B12,0),0)
COINCIDIR, nos devuelve la posición del DNI, que será a su vez, el número de filas que
usará DESREF.
FUNCIÓN INDICE
Archivo de trabajo: 06 INDICE - DEREF
Devuelve el valor de un elemento de una tabla o matriz, seleccionado por los índices de
número de fila y de columna
Sintaxis
INDICE(MATRIZ,# FILAS, # COLUMNAS)

Ejemplo: Cuánto fueron las exportaciones a España en el mes de abril

=INDICE(B10:F14,3,4)  4800
Si el rango B10:F14 tiene el nombre Ventas:
=INDICE(Ventas,3,4)  4800

Ejemplo: País que tuvo la mayor venta en el mes de abril

=INDICE(País,COINCIDIR(MAX(Abril),Abril,0))
Está fórmula, devolverá FRANCIA, que es país que tuvo la mayor venta de Abril
FUNCIÓN DESREF – SU APLICACIÓN PARA EXTRAER UNA MATRIZ
La Función DESREF, no solo puede devolver un valor. También puede devolver una matriz,
o un rango de valores.
Sintaxis
DESREF(Ref.Inicial,#Filas,#Columnas,[Alto],[Ancho])

Ejemplos:
Valor que se encuentra 3 filas hacia abajo, a partir de B9
=DESREF(B9,3,0)  España
Valor que se encuentra 2 filas hacia abajo, 1 columna a la derecha a partir de B9
=DESREF(B9,2,1)  6872
Extraer las ventas a Alemania, España y Francia en Enero y Febrero
=DESREF(B9,2,1,3,2)  Error #¡VALOR!
El error, es porque no nos puede devolver o extraer en una celda, 6 valores
Suma las ventas a Alemania, España y Francia en Enero y Febrero
=SUMA(DESREF(B9,2,1,3,2))
BÚSQUEDA POR APROXIMACIÓN CON BUSCARV
Archivo de trabajo de Excel: 07 Registro de Notas – Búsqueda aproximada

Se trata de un Registro de Notas. Ya están todos los cálculos de las Notas. Falta completar
la Escala y la Condición en la celda K14 y L14, respectivamente.
La Escala, depende de la Nota final, y se debe extraer del cuadro de la parte superior, cuyo
nombre es Escala_condición

Recordemos, que la función

BUSCARV, busca el dato
conocido en la primera
columna del rango
(Escala_Condición).
La primera Nota de J14, es 16. Este valor se encuentra en la
primera columna del rango de la parte superior. Pero, hay otras
notas que no se encuentran, como por ejemplo: 09, 10, 15, etc.
Por tanto debemos trabajar con búsqueda por aproximación:
=BUSCARV(J14,Escala_Condición,4)
Y, para la condición:
=BUSCARV(J14,Escala_Condición,5)
Estamos omitiendo el argumento 0 (búsqueda exacta)
Por tanto, la búsqueda será por aproximación.
En ambos casos, si no se encuentra el dato conocido, tomará el menor valor más próximo
encontrado
Requisitos para búsqueda por aproximación;
- La primera columna del Rango de búsqueda, debe estar ordenado de menor a
mayor
- El rango, NO debe incluir los títulos

TAREA:
Desarrollar las preguntas del archivo de Excel:
Tarea Elaborar Cuadro, Fórmula Aumentos y Consulta
FUNCIÓN BUSCAR
Busca un dato conocido, en un vector y devuelve el valor de otro vector.
Sintaxis
BUSCAR(Valor Buscado,Vector de Comparación,Vector de Resultados)
Valor buscado: J14 (Nota final)
Vector de comparación: E4:E8
Vector de resultados: H4:H8 (Escala)
La Fórmula para ESCALA con la función BUSCAR: =BUSCAR(J14,E$4:E$8,H$4:H$8)
La Función BUSCAR, solo realiza una búsqueda por aproximación. Y los rangos deben ser
paralelos. Pueden ser verticales u horizontales.
CONSOLIDAR DATOS
Archivo de trabajo: 08 Consolidar datos
Sirve para resumir o registrar datos de varias hojas independientes, en una hoja de cálculo
maestra. Así por ejemplo, suponga que tenemos los artículos adquiridos en tres oficinas
en distintos meses. Entonces, podemos crear un resumen de los gastos de las tres oficinas
durante todos los meses, en una Hoja de cálculo maestra.

En el ejemplo anterior, deseamos consolidar de acuerdo a los rótulos (títulos) de la

columna izquierda (columna que contiene los artículos adquiridos) y por la Fila Superior
(que contiene los meses)
Algunas consideraciones:
- No importa el orden en que los datos estén escritos. Observe que los artículos no
están en algún orden.
- Los artículos y los meses, deben estar escritos iguales en todos los cuadros. Por
ejemplo el artículo Lápices, debe estar escrito de la misma forma en todos los
cuadros.
- El cuadro de Totales u Hoja de Cálculo Maestra, debe contener la totalidad de
artículos y meses de todas las Hojas individuales (para nuestro caso, las oficinas):
PROCEDIMIENTO
- Asignar nombre a cada uno de los cuadros con lo títulos de la columna izquierda y
la fila superior. Los nombres en nuestro caso podrían ser: BREÑA, LINCE Y SURCO
- Haga clic en la Hoja Total (Hoja maestra)
- Seleccione todo el cuadro donde se va a realizar la consolidación: A3:A13
- Clic en la Ficha Datos – Grupo Herramientas de Datos – Clic en Consolidar
Se mostrará el siguiente cuadro de diálogo:

- En nuestro caso, bajo la opción Función, dejamos Suma, pero podría usarse otras
funciones, por ejemplo si se tratara de actas de notas de diferentes cursos de los
mismos alumnos, entonces podríamos usar la función Promedio.
- Luego, en la opción Referencia, debemos escribir los
nombres de los rangos que vamos a consolidar. Puede
usar la Tecla de función F3, para mostrar el cuadro de
diálogo Pegar nombre:

- Por ejemplo, seleccione Breña y Aceptar

- Y luego haga clic en el botón Agregar.
- De las misma forma, termine de agregar los demás nombres de rango
- Una vez que ya agregó todas las referencias o nombres de rango, debe marcar las
dos casillas de la parte inferior: Usar rótulos en Fila Superior y Columna Izquierda.
- Finalmente, hacemos clic en el botón Aceptar:

Si posteriormente, hace algún cambio o

corrección en una de las hojas
individuales, debe volver a seleccionar
TODO el cuadro de Total y volver a
Consolidar. Si desea, puede consolidar
solo una parte:
 TERCERA PARTE

Archivo de Trabajo de Excel: 09 Valorizaciones - Funciones condicionales

Temas a desarrollar
- REPASO: FUNCIONES PARA CÁLCULOS CON UN CRITERIO O CONDICIÓN
o CONTAR.SI
o SUMAR.SI
o PROMEDIO.SI
- FUNCIONES PARA CÁLCULOS CON VARIOS CRITERIOS O CONDICIONES
o CONTAR.SI.CONJUNTO
o SUMAR.SI.CONJUNTO
o PROMEDIO.SI.CONJUNTO
o MAX.SI.CONJUNTO

- FÓRMULAS MATRICIALES

- FUNCIONES DE BASES DE DATOS

FUNCIONES K.ESIMO.MENOR y K.ESIMO.MAYOR
FUNCIONES PARA RESOLVER CÁLCULOS QUE INVOLUCRAN UN CRITERIO O CONDICIÓN
Se tiene una Lista o Base de datos, sobre las valorizaciones de 41 viviendas, que se
encuentran ubicadas en distintas zonas y pertenecen a 3 propietarios.
En ocasiones, nos van a pedir realizar cálculos no con todos los registros; sino, solamente
de algunos que cumplan con uno o varios criterios o condiciones. Por ejemplo, Cantidad de
casas que se encuentran en la zona Sur.

Función CONTAR.SI
Cuenta en un rango sólo las celdas que cumplan con un criterio o condición.
Sintaxis

CONTAR.SI(RangoCriterio,"Criterio")
Donde:
Criterio : El tipo de comprobación que se debe cumplir
Ejemplos:
"=Sur"
">300000"
"<=400000"
Rango criterio: El rango en el que se evalúa el Criterio
Ejemplo: Cantidad de casas que se encuentran en la Zona Sur
Rango Criterio: Zona
Criterio: "=Sur"
Fórmula:

Recuerde, que cuando el operador es = (igual), se puede omitir:

=CONTAR.SI(Zona,"=Sur")

Función SUMAR.SI
Suma en un rango sólo las celdas que cumplan con un criterio o condición.
Sintaxis

SUMAR.SI(RangoCriterio,"Criterio",RangoSuma)
Donde:
Criterio: El tipo de comprobación que se debe cumplir
Rango criterio: El rango en el que se evalúa el Criterio
Rango Suma: El rango que se desea sumar
Ejemplo: Suma las valorizaciones de casas que se encuentran en la Zona Sur
Zona Valorización Rango Criterio: Zona
Sur S/ 114,000.00
Norte S/ 120,000.00 Criterio: "=Sur"
Sur S/ 186,000.00
Rango Suma: Valorización
Oeste S/ 75,000.00
Norte S/ 222,000.00 Fórmula:
Oeste S/ 150,000.00
Norte S/ 90,000.00
Norte S/ 98,000.00
Oeste S/ 412,000.00 =SUMAR.SI(Zona,"=Sur",Valorización)
Sur S/ 300,000.00
Sur S/ 380,000.00
Norte S/ 250,000.00
Oeste S/ 161,000.00
Norte S/ 320,000.00
Oeste S/ 400,000.00
Función PROMEDIO.SI
Calcula el promedio de un rango sólo las celdas que cumplan con un criterio o condición.
Sintaxis

PROMEDIO.SI(RangoCriterio,"Criterio",RangoPromedio)
Donde:
Criterio: El tipo de comprobación que se debe cumplir
Rango criterio: El rango en el que se evalúa el Criterio
Rango Promedio: El rango que se desea promediar

Ejemplo: Calcular el Promedio las valorizaciones de casas de Jorge

Rango Criterio: Propietario
Criterio: "=Jorge"
Rango Promedio: Valorización
Fórmula:
=PROMEDIO.SI(Propietario,"=Jorge",Valorización)

FUNCIONES PARA CÁLCULOS QUE INVOLUCRAN VARIOS CRITERIOS O CONDICIONES

Ya hemos visto, algunas funciones para que permiten realizar cálculo con un criterio, o
condición, como CONTAR.SI, SUMAR.SI

Función CONTAR.SI.CONJUNTO
Cuenta en un rango sólo las celdas que cumplan con varios (un conjunto) criterios o
condiciones.
Sintaxis
CONTAR.SI.CONJUNTO(RangoCriterio1,"Criterio1",RangoCriterio2,"Criterio2",…)
Ejemplo: Cantidad de casas que tiene Sonia en la Zona Norte
Fórmula:
=CONTAR.SI.CONJUNTO(Propietario,"=Sonia",Zona,"=Norte")
Función SUMAR.SI.CONJUNTO
Suma en un rango sólo las celdas que cumplan con varios (un conjunto) criterios o
condiciones.
Sintaxis
SUMAR.SI.CONJUNTO(RangoSuma,RangoCriterio1,"Criterio1",RanCrit2,"Crit2",…)
Ejemplo: Cuánto suman las valorizaciones de las casas de Sonia en la Zona Norte
Rango Suma : Valorización
Rango Criterio1 : Propietario
Criterio1 : "=Sonia" o "Sonia"
Rango Criterio2 : Zona
Criterio2 : "=Norte" o "Norte"

Fórmula:
=SUMAR.SI.CONJUNTO(Valorización,Propietario,"=Sonia",Zona,"=Norte")

Ejemplo: Cuánto suman las valorizaciones de las casas de Carlos, mayores o iguales que
300000
Fórmula:
=SUMAR.SI.CONJUNTO(Valorización,Propietario,"=Carlos",Valorización,">=300000")
Si los datos se encuentran en celdas:

Observar los criterios:

Cuando el operador es = (Igual), solo va la referencia: Propietario,F62
Pero, para los demás operadores: Valorización,">"&G62

Función PROMEDIO.SI.CONJUNTO
Calcula el promedio de un rango sólo de las celdas que cumplan con varios (un
conjunto) criterios o condiciones.
Sintaxis
PROMEDIO.SI.CONJUNTO(RangoPromedio,RangoCriterio1,"Criterio1",RanCrit2,"Crit2",…)
Ejemplo: Calcular el Promedio de las valorizaciones de las casas de Carlos en la Zona
Sur

Fórmula:

=PROMEDIO.SI.CONJUNTO(Valorización,Propietario,"=Carlos",Zona,"=Sur")
 Función MAX.SI.CONJUNTO (Solo versión 2019)
Devuelve el valor máximo de un rango, sólo de las celdas que cumplan con uno o varios
criterios o condiciones:
Sintaxis
MAX.SI.CONJUNTO(RangoMax,RangoCriterio1,"Criterio1", RangoCriterio2,Criterio2",…)

Ejemplo: Valor de la casa más cara de Sonia en la Zona Norte

Fórmula:
=MAX.SI.CONJUNTO(Valorización,Propietario,"=Sonia",Zona,"=Norte")

FÓRMULAS MATRICIALES

La funciones anteriores, solo permiten trabajar con criterios de tipo Y; pero, va a haber
casos en los que se requiera realizar cálculos que involucren criterios de tipo O.
Entonces, otro método para resolver cálculos que involucran uno o varios criterios o
condiciones, son las fórmulas matriciales.
La principal característica de una fórmula matricial, a diferencia de las fórmulas de hoja
normales, es que pueden procesar varios valores en lugar de sólo uno.
Veamos un ejemplo de una Fórmula matricial: Hoja de cálculo: Ejemplo de una Fórmula
matricial:
En el rango E6:E10, deseamos escribir una
fórmula que multiplique todos los precios
de los artículos por todas las cantidades.
- Seleccionamos el rango E6:E10
- Escribimos la fórmula:

=C6:C10*D6:D10

Se trata de una multiplicación de matrices (rangos)

- Al terminar de editar la fórmula, DEBE pulsar: SHIFT + CTRL + ENTER

Observe la fórmula en la Barra de Fórmulas

{=C6:C10*D6:D10}
Veamos otra forma de aplicar las fórmulas matriciales:
Sintaxis de las Fórmulas matriciales:
Sintaxis con una condición
FUNCIÓN(SI(PruebaLógica,RangoFunción))
SUMA(SI(Zona="Sur",Valorización))
Donde:
Función: De acuerdo al cálculo que desea realizar; ejemplo, SUMA, PROMEDIO, MAX,
etc.
Prueba Lógica: La Condición que se debe cumplir para que se ejecute el cálculo:
Ejemplo: Zona="Sur"
En este ejemplo, Zona es un rango o una matriz
RangoFunción: Es el rango sobre el que actúa la Función. Ejemplo Valorización
Nota1:
Al terminar de editar una Fórmula matricial, debe pulsar [SHIFT + CTRL + ENTER]
Nota2:
Una Fórmula matricial, NO se puede editar en celdas combinadas.
Ejemplo: Suma las valorizaciones de las casas de la Zona Sur
Fórmula:
=SUMA(SI(Zona="Sur",Valorización)) SHIFT+CTRL+ENTER

Ejemplo: Valor de la casa más cara de Sonia

Fórmula:
{=MAX(SI(Propietario="Sonia",Valorización))}

Sintaxis con varias condiciones de tipo Y (*)

FUNCIÓN(SI((PruebaLógica 1)*(PruebaLógica 2),RangoFunción))

Ejemplo: Suma las valorizaciones de las casas de Sonia en la Zona Norte
Fórmula:
{ =SUMA(SI((Propietario="Sonia")*(Zona="Norte"),Valorización))}
Sintaxis con varios criterios de tipo O (+)
FUNCIÓN(SI((PruebaLógica 1)+(PruebaLógica 2),RangoFunción))
Ejemplo: Suma las valorizaciones de casas de Carlos y Jorge
Fórmula:
{=SUMA(SI((Propietario="Carlos")+(Propietario="Jorge"),Valorización))}

FUNCIONES K.ESIMO.MENOR y K.ESIMO.MAYOR

Encuentran los primeros menores o primeros mayores valores de una lista
Sintaxis
K.ESIMO.MENOR(Rango,Posición)
K.ESIMO.MAYOR(Rango,Posición)
Ejemplo: Valor de la Segunda casa más cara
Fórmula:
=K.ESIMO.MAYOR(Valorización,2)

Resolver con fórmulas matriciales:

Ejemplo: Valor de la segunda casa más cara de Sonia

{=K.ESIMO.MAYOR(SI(Propietario="Sonia",Valorización),2)}

Quién es el propietario de la casa más cara de la Zona Sur:

CON DESREF y COINCIDIR
{=DESREF(A3,COINCIDIR(MAX(SI(Zona="Sur",Valorización)),Valorización,0),0)}
CON INDICE Y COINCIDIR
{=INDICE(Propietario,COINCIDIR(MAX(SI(Zona="Sur",Valorización)),Valorización,0))}
FUNCIONES DE BASES DE DATOS
Otro método para resolver cálculos con condiciones son las Funciones de Bases de
Datos (Funciones BD)
En estas funciones, las condiciones o criterios, van en rangos de celdas, lo que permite
trabajar con múltiples criterios tanto de tipo Y, como de tipo O
Sintaxis
FUNCIÓNBD(Base de datos,"Nombre" o número de campo a calcular,Rango de criterios)
Ejemplo de un rango de Criterios:
Zona=Sur

Cuando los criterios son de tipo Y, se escriben en la misma fila.

Ejemplo de dos criterios de tipo Y

Cuando los criterios son de tipo O, se escriben en filas diferentes.

Ejemplo de dos criterios de tipo O

Ejemplo: Suma las valorizaciones de Carlos y Jorge en la Zona Sur

Rango de criterios:

Fórmula:
=BDSUMA(BASEDATOS,"Precio",K22:L24)
 QUINTA PARTE

Archivo de trabajo de Excel: 10 Manejo del Tiempo en Excel

Temas a desarrollar:
Manejo del Tiempo en Excel:
- FUNCIONES
o HOY
o AHORA
o DIA
o MES
o AÑO
o DIASEM
o FECHA
o SIFECHA
o DIAS
o FIN.MES
o FILA
o FECHA.MES
o FORMATOS PERSONALIZADOS DE FECHA
o CÁLCULOS CON HORAS
o FUNCIONES: HORA, MINUTO
o FORMATOS DE HORA

MANEJO DEL TIEMPO

En muchas ocasiones, vamos a necesitar realizar cálculos con fechas; por ejemplo, para
conocer la edad de una persona, para saber cuánto tiempo lleva laborando una apersona
en una empresa o cuánto falta para que se venza el plazo de pago de alguna deuda.
Por tanto, las fechas en Excel son números con formato o apariencia de fecha.
Ingresar las fechas:
20-4
20/4
18/10/2017
Normalmente las fechas las expresamos con 3 números:
DIA 21
MES 8
AÑO 2020
En Excel, una fecha se expresa con un solo número:
DIA 44064
En Excel, la unidad de medida del tiempo es el día
El Sistema de Fechas en Excel, comienza el
01/01/1900 Día 1
02/01/1900 Día 2
21/08/2020 Día 44064
Archivo: 10 Manejo del tiempo en Excel:

Ingresar la Fecha actual como valor constante:

o Pulse [Ctrl + ,]
Ingresar la Hora actual como valor constante:
o Pulse [Ctrl + Shift + :]
 ALGUNAS FUNCIONES DE FECHA:

FUNCIÓN HOY
Devuelve la fecha actual (Del Sistema). La fecha se actualizará día a día.
Sintaxis
HOY()
Ejemplo: =HOY() 26/04/2021

La fecha se actualizará con la fecha del sistema.

Es decir al día siguiente mostrará: 27/04/2021

Función AHORA
Devuelve la fecha y hora actuales (Del Sistema).
Sintaxis
AHORA()
Ejemplo: =AHORA() 26/04/2021 09:50

La fecha y la hora también se actualizarán con la fecha y hora del Sistema.

Función DIA
Devuelve el número del día (1-31) de un número de serie o fecha determinada.
Sintaxis
DIA(Número de Serie)
Ejemplo:
Función MES
Devuelve el número del mes (1-12) de un número de serie o fecha determinada.
Sintaxis
MES(Número de serie)
Ejemplo:

Función AÑO
Devuelve el número de año (1900-9999) de un número de serie o fecha determinada.
Sintaxis
AÑO(Número de serie)
Ejemplo:

Función DIASEM
Devuelve el número del día de la semana (1 al 7)
Sintaxis
DIASEM(Número de serie,[Tipo])
Tipo:
1 o ninguno : Domingo = Día 1
2 : Lunes = Día 1
Ejemplo:

Función FECHA
Devuelve el número de serie que corresponde a la fecha especificada
Sintaxis
FECHA(Año,Mes,Día)
Ejemplo:
=FECHA(2016,11,21)  42695 (21/11/2016)
Ejemplo1:

Ejemplo2:
Función SIFECHA
Devuelve el tiempo transcurrido entre dos fechas. Puede ser en años, meses o días.
Sintaxis
SIFECHA(FechaInicial,FechaFinal,"Tiempo")
Tiempo:
Y Años
M Meses
D Días

Solución:
Función DIAS
Devuelve el tiempo transcurrido entre dos fechas en días
Sintaxis
DIAS(Fecha Final,Fecha Inicial)
Ejemplo:
=DIAS(C14,C11)  8477

Función FIN.MES
Devuelve el número del último día del mes de la fecha inicial o, de meses anteriores o
posteriores a la fecha inicial.
Sintaxis
=FIN.MES(FechaInicial,meses)
Ejemplo:
=FIN.MES("16/12/2019",2)  29/02/2020
Donde Meses:
0 es el mes del argumento Fecha Inicial
1 es el mes siguiente del argumento Fecha Inicial
-1 es el mes anterior del argumento Fecha Inicial
Los números positivos serán meses posteriores
Los números negativos serán meses anteriores
Ejemplo:
Se compra un artículo el 20/4/2020. El pago se realizará el último día del mes siguiente:
Función FILA
La función FILA en Excel nos devuelve el número de la fila que ocupa la referencia que
proporcionemos como argumento.
Si omitimos el argumento, la función FILA supondrá que nos referimos a la celda donde se
encuentra actualmente.
Sintaxis
FILA([Referencia])
Ejemplo:
=FILA(B1) 1 Si copiamos la fórmula hacia abajo:
=FILA(B2) 2
=FILA(B3) 3
=FILA(B4) 4

Función FECHA.MES
Devuelve el número de serie que representa la fecha que indica el número de meses
anteriores o posteriores a la fecha especificada (argumento fecha_inicial). Use FECHA.MES
para calcular las fechas de vencimiento que coinciden con la fecha de emisión.
Sintaxis
FECHA.MES(FechaInicial,Meses)
Ejemplo:
Se compra un artículo el 20/4/2020. El pago se realizará exactamente un mes siguiente:
20/5/2020
FORMATOS PERSONALIZADOS DE FECHA
Códigos de Formatos de Fecha

Procedimiento
- Seleccione los datos
- Ficha Inicio – Número
- Despliegue la lista de formatos
- Seleccione Más formatos de número

- Seleccione la categoría Personalizada

- En la opción Tipo, ingrese los códigos de formato. Ejemplo:
mmmm, dddd dd "de" yyyy
- Aceptar
LAS HORAS EN EXCEL
Al ser el DÍA, la unidad de medida del tiempo en Excel, las Horas son fracción de día. De
esta manera por ejemplo, medio de día viene a ser el valor 0.5
Veamos el ejemplo.

En el rango: C3:C5,se ha ingresado 3 horas. En el rango D3:D5, están los mismos datos
pero con formato General.

FORMATOS DE HORA:

Ejemplos:
FUNCIÓN HORA
Devuelve la hora como un número de 0 (12:00 a.m.) a 23 (11:00 pm)
Ejemplo, en el rango A2:A6, se ha ingresado datos como horas
En el rango B2:B6, se han extraído las horas con la fórmula:
=HORA(A2)
En la celda A2, en realidad se encuentra el valor:
0.33333333333, pues las horas son fracción de día.
En la celda B2, se encuentra el valor 8

FUNCIÓN MINUTO
Devuelve el minuto, un número de 0 a 59
Ejemplo, en el rango A2:A6, se ha ingresado datos como horas
En el rango B2:B6, se han extraído los minutos con la fórmula:
=MINUTO(A2)
En la celda A2, en realidad se encuentra el valor: 0.336805555
En la celda B2, se encuentra el valor 5
Desarrollar el ejercicio:
Cálculo de Pagos por horas de trabajo en cinco días (Hoja de cálculo Hora minuto) del
archivo
 SEXTA PARTE

Archivos de trabajo
11 Cuentas por Pagar
12 Texto - Abs - ES - Mayúsc - Repetir - Formatos

Temas a desarrollar en esta sesión:

- FUNCIONES
o TEXTO
o FUNCIONES ES
o ABS
o SI ANIDADOS
o MAYUSC, MINUSC, NOMPROPIO
o REPETIR

- CÁLCULO DE INTERÉS
- CONFIGURACIÓN REGIONAL DE WINDOWS
- FORMATOS PERSONALIZADOS DE NÚMEROS
- FORMATO CONDICIONAL

Función TEXTO
Esta función convierte un valor en texto, con un formato de número específico.
Sintaxis
TEXTO(Valor,"Formato")
Ejemplo:

En las celdas B8 y C8, se tienen datos numéricos.

Las fórmulas de las celdas B10 y C10, están convirtiendo los datos de la fila 8, en texto.
Por tanto, los datos de las celdas B12 y C12, son textos, pero con formato de número.
FUNCIONES PARA CAMBIAR MAYÚSCULAS – MINÚSCULAS
Excel, cuenta con tres funciones que permiten cambiar los textos de mayúsculas –
minúsculas:
MAYUSC : CONVIERTE TODO A MAYÚSCULAS
NOMPROPIO : Convierte Las Primeras Letras De Cada Palabra a Mayúsculas
MINUSC : convierte todo a minúsculas.
Ejemplo:
Para calcular PLAZO EN DÍAS – Archivo: 11 Cuentas por Pagar
Si ya se canceló la deuda:
Fecha de Vencimiento – Fecha de Pago
Si la deuda aún no ha sido cancelada:
Fecha de Vencimiento – HOY()

Fórmula:
=
Función ABS
Esta función, devuelve el valor absoluto de un número. El valor absoluto de un número, es
el número sin su signo.
Sintaxis
ABS(Número)
Ejemplo: =ABS(B16)  2
Fórmula para calcular el Interés
Archivo de trabajo 11 Cuentas por Pagar

I = C*(1+i)^t – C
I = VA*(1+i)^n – VA

Para imprimir el carácter: ^ puede pulsar [Alt + 94]

I : Interés
C o VA : Capital inicial o Valor Actual (Monto de la deuda – E4)
i : Tasa de Interés (14% TEA) (F3)
ton : Tiempo o número de periodos (Plazo en días – D4)

Archivo 11 Cuentas por Pagar:

Resolver la Fórmula para el cálculo del Interés moratorio:
Si se venció el Plazo de Pago, se pagará Interés, sino, la celda se mostrará vacía ("").
Fórmula:
=

FÓRMULA PARA EL CAMPO ESTADO:

Deben ir distintos textos, dependiendo de las siguientes condiciones:
Para el Campo ESTADO:
Condición Texto
Si ya se canceló la deuda : Pagada
Si ya se venció el plazo de Pago : Vencida
Si la Fecha de Vencimiento coincide con la Fecha de hoy : Vence hoy
Si falta hasta 7 días a partir de hoy para que se venza : Por vencer
Sino : Celda vacía
Fórmula:
=
CONFIGURACIÓN REGIONAL DE WINDOWS

- Haga clic en el Botón Inicio de la Barra

de Tareas
- Opción Configuración o Panel de
Control
- Opción Hora e Idioma
- Opciones Adicionales
- Configuración Regional (Región)
- Seleccione el Formato para el país:
Español Perú.
- Para modificar los formatos de
Número y Moneda, haga Clic en
Configuración Adicional

Ficha Número:
Ficha Moneda:

- Actualice si fuera necesario el símbolo de moneda de Perú: (S/ )

- Aceptar
FORMATOS PERSONALIZADOS DE NÚMERO
Códigos de Formato
0 Dígito obligatorio
# Dígito opcional
* Llena el resto de la celda con determinado carácter
_ Desplaza el número, una posición del tamaño de determinado carácter.
[Color] Muestra el número con el color indicado

"texto" Muestra el texto en la posición indicada

[Condición] Una condición va entre corchetes.

Secciones de Formatos de Número:

FormatoPositivo;FormatoNegativo;FormatoCero;Formato de textos
Ejemplo, para mostrar:

Formatos:

 
Formatos Personalizados con Condiciones
Las condiciones van entre corchetes.
Ejemplo: [=1]0 “Reloj”;0 "Relojes"
Formato de Plazo en Días:
Los valores positivos : Azul
Los Valores negativos : Rojo y entre paréntesis
Los valores Cero : Verde
Formato Condicional
Sirve para aplicar distintos tipos de formatos a los datos, dependiendo de los valores que
contengan.
Puede aplicar los formatos existentes o crear sus propias reglas de formato.
- Ficha Inicio – Estilos – Formato Condicional
- Para crear una nueva Regla, elija Nueva Regla o abra la ventana Administrar Reglas.

- Clic en Nueva Regla

- Seleccione el tipo de Regla y configure las opciones de la parte inferior (criterios)

- Clic en Formato y aplique los formatos que desee.
- Aceptar.

Resolver la Fórmula del título del archivo Cuentas por Pagar:

=
Función REPETIR
Repite el texto un número determinado de veces. Use REPETIR para llenar una celda con
una cadena de texto repetida un número determinado de veces.
El resultado de la función REPETIR no puede contener más de 32.767 caracteres.
Sintaxis
REPETIR(Texto,Número de veces)
Ejemplo:
=REPETIR("*",10)  **********
=REPETIR("█",10) ██████████
 SÉTIMA PARTE

Temas a desarrollar en esta sesión:

- FUNCIÓN ALEATORIO
- FUNCIÓN ALEATORIO.ENTRE
- FUNCIONES: REDONDEAR.MAS – REDONDEAR.MENOS
- TRANSPONER
- VALIDACIÓN DE DATOS
- Mostrar en un objeto de dibujo o cuadro de texto, el contenido de una celda
FUNCIONES ALEATORIO.ENTRE, ALEATORIO, REDONDEAR.MAS, REDONDEAR.MENOS

Función ALEATORIO.ENTRE (Archivo: 13 Aleatorio - Redondear - Transponer

Devuelve un número entero aleatorio entre los números que especifique. Cada vez que
calcula la hoja de cálculo, devuelve un nuevo número entero aleatorio. Cada vez que se
recalculan las fórmulas de la hoja de cálculo, o pulsa F9, también se recalcula la función
Aleatorio y devolverá números aleatorios diferentes.
Sintaxis
ALEATORIO.ENTRE(Número Inferior,Número Superior)
Ejemplo:
=ALEATORIO.ENTRE(10,20)
Esta fórmula devolverá números aleatorios como: 10,15,12,20,15,18,11,19…

Función ALEATORIO
Devuelve un número aleatorio mayor o igual que cero y menor que 1
Sintaxis
ALEATORIO()
Esta fórmula devolverá números aleatorios como:
0.25631
0.965324
0.1985423
0.11457
Generar valores aleatorios con decimales
Primera Forma: Si no importa el número de decimales devuelto:
=a+ALEATORIO()*(b-a)
Ejemplo: Generar números aleatorios con decimales entre 5 y 20
=5+ALEATORIO()*(20-5)
Esta Fórmula devolverá números como:
8.75422400279864, 9.85828763804731, 19.1211904417821, 11.5914455049139
Segunda Forma: Si lo que deseas es obtener un valor con una cantidad de decimales
determinada, por ejemplo 2 decimales, puedes utilizar el siguiente método:
=ALEATORIO.ENTRE(a*100,b*100)/100
Ejemplo: Generar números aleatorios entre 1000 y 3000 con 2 decimales:
=ALEATORIO.ENTRE(1000*100,3000*100)/100
Esta Fórmula devolverá números como:
1310.25
2930.87
1410.18
1330.42
1337.41

Función REDONDEAR.MAS
Redondea un número hacia arriba, en dirección contraria a cero
Sintaxis
REDONDEAR.MAS(Número,Número de decimales)

Función REDONDEAR.MENOS
Redondea un número hacia abajo, hacia cero
Sintaxis
REDONDEAR.MAS(Número,Número de decimales)
Ejemplo: REDONDEAR, REDONDEAR.MAS, REDONDEAR.MENOS a 1 decimal
TRANSPONER DATOS
Se tiene el siguiente cuadro:

Para transponer:
- Seleccione el rango B3:N6
- Ejecute el comando COPIAR
- Haga clic en la celda a partir de la cual desea copiar el rango
- Despliegue las opciones del comando PEGAR
- Elija la opción Transponer
VALIDACIÓN DE DATOS
Archivo de Excel: 14 Validación de datos
Sirve para evitar que en una celda o rango, se ingrese valores no válidos.
- Seleccione la celda o celdas
- Ficha Datos – Herramientas de Datos - Clic en Validación de datos

- En el cuadro de diálogo, en la opción Permitir, seleccione el tipo de datos: Número

entero, decimal, Lista, etc.
- Configure las demás opciones. Aceptar
 Otro ejemplo: Permitir números enteros no mayores que 50:
Mensajes de Entrada y Error en la Validación
Se puede crear un mensaje de entrada:

 
También puede personalizar el Mensaje de Error:
VALIDAR LISTAS VARIABLES (VALIDACIÓN CAMBIANTE
En el siguiente ejemplo:

En una tienda de electrodomésticos se desea consultar una lista de artículos. Pero en la

consulta se desea elegir primero la línea de artículos: Artefactos, Línea Blanca y Cómputo y
luego el tipo de artículo.
En el rango A4:A6, se encuentran las líneas de artículos, al mismo que le daremos el
nombre: Líneas

Luego tenemos los rangos de artículos, a los cuales le daremos nombres:

C4:C13 Artefactos
E4:E9 Línea Blanca
G4:G6 Cómputo
Luego, la celda K3, la validaremos con la lista Líneas
La celda K4, la validaremos con la lista, de acuerdo a la Línea de artículos que se encuentre
seleccionada en K3
Procedimiento
- Crear nombres a partir de los títulos de cada Lista
o Seleccione los rangos:
C4:C13
E4:E9
G4:G6
o Fecha Fórmulas - Nombres definidos – Crear desde la selección

o Marque solamente Fila superior - Aceptar

- Validar J3, con la Lista Líneas

- Validar con Listas variables la celda J4:
o Artefactos
o Línea Blanca
o Cómputo
Permitir: Lista
En Origen, escriba la fórmula que devuelva la lista cuyo nombre se encuentra en J3, como
en el cuadro siguiente:
Mostrar en un objeto de dibujo o cuadro de texto, el contenido de una celda
- Seleccione el dibujo (clic) o cuadro de texto o título de un gráfico.
- Haga clic en la Barra de Fórmulas
- Escriba Igual (=)
- Haga clic en la celda cuyo contenido desea mostrar
- Pulse Enter
 OCTAVA PARTE

Temas a desarrollar en esta sesión:

- FUNCIONES DE TEXTO
o IZQUIERDA
o DERECHA
o EXTRAE
o LARGO
o HALLAR
o ENCONTRAR
- AGRUPAR Y ESQUEMA
- TEXTO EN COLUMNAS
- LÍNEA DE TENDENCIA
FUNCIONES DE TEXTO (Archivo de Excel: 15 Lista de Empleados - Funciones de Texto

FUNCIÓN IZQUIERDA
Extrae los primeros caracteres del extremo izquierdo de una cadena de caracteres
Sintaxis
IZQUIERDA(Texto,Número de caracteres)
Ejemplo: Extraer del texto de la celda B13, los 7 primeros caracteres:

FUNCIÓN DERECHA
Extrae los últimos caracteres del extremo derecho de una cadena de caracteres.
Sintaxis
DERECHA(Texto,Número de caracteres)
Ejemplo: Extraer del texto de la celda B13, los 7 últimos caracteres:

FUNCIÓN EXTRAE
Extrae un número específico de caracteres de una cadena de texto, comenzando en la
posición que se indique.
Sintaxis
EXTRAE(Texto,Posición Inicial,Número de caracteres)
Ejemplo: Extraer del texto de la celda B13, 4 caracteres, a partir del 4to caracter:

FUNCIÓN LARGO
Devuelve el número (cantidad) de caracteres de una cadena de texto.
Sintaxis
LARGO(Texto)
Ejemplo: Largo de la cadena de la celda B13:

Hoja de Cálculo: Empleados

Ejercicio: La primera empleada es CAMARGO FALLA

CECILIA del área de ADMINISTRACIÓN
FUNCIÓN HALLAR
Busca una cadena de texto dentro de otra cadena de
texto, y leyendo de izquierda a derecha, devuelve el
número de la posición inicial de la cadena hallada (no
diferencia entre mayúsculas ni minúsculas. Si desea que
Excel diferencie entre mayúsculas y minúsculas, use
ENCONTRAR.
Sintaxis
HALLAR(texto_buscado;dentro_del_texto;
[posición_inicial])
Donde:
Texto_buscado: Es la cadena de texto o caracter que se desea ubicar. Puede usar los
comodines * o ?.

Dentro_del_texto Es la cadena de texto dentro de la cual se desea ubicar el texto

buscado

Posición_inicial Es la posición del primer carácter a partir de la cual se iniciará la

búsqueda.
Ejemplos con la función HALLAR:

AGRUPAR Y ESQUEMA
Se tiene el siguiente cuadro de Entregas trimestrales por zonas. Lo que se desea es
terminar de calcular los totales por Trimestres, Semestres y Total del año, y también
agrupar totales por zonas. Finalmente agrupar las columnas y filas para poder leer el
cuadro de una manera más resumida (Archivo de Excel: 16 Agrupar y Esquema)
Calcular los totales por trimestre, semestre y total anual:
- Seleccionar el rango E4:E23 del 1er Trim.
- Hacer un clic en Autosuma.
Se habrá escrito la siguiente fórmula: =SUMA(B4:D4)
- Repetir lo anterior para cada uno de los trimestres.
- Seleccionar el rango J4:J23 del 1er Sem.
- Hacer un clic en Autosuma.
Se habrá escrito la siguiente fórmula: =SUMA(I4,E4)
- Repetir lo anterior para el 2do Semestre.
- Seleccionar el rango T4:T23 del Total anual.
- Hacer un clic en Autosuma.
Se tendrá finalmente la siguiente fórmula: =SUMA(S4,J4)

Calcular los totales agrupados por zonas:

Primeramente se desea ordenar el cuadro de entregas trimestrales por zonas pero; en
este orden: Norte, Sur, Este y Oeste.
- Seleccionar una celda de la columna Zona.
- Elegir la Ficha Datos/Ordenar
- Elegir Ordenar por: Zona, y en Criterio de ordenación: Lista personalizadas
- En la ventana Listas personalizadas, escriba las siguientes entradas de lista: Norte,
Sur, Este y Oeste, luego haga un clic en el botón Agregar, y luego en Aceptar.

- Regresará a la ventana Ordenar. Entonces haga clic en el botón Aceptar, con lo cual
se tendrá finalmente ordenada el cuadro de entregas mensuales por zonas en el
orden que deseábamos.
A continuación vamos a calcular los subtotales por zonas:
- Seleccionar una de las celdas de la columna Zona
- Elegir la Ficha Datos/Subtotal

- En el grupo Agregar subtotal a: Marque con un check todos los casilleros, excepto
Zona; y luego Aceptar.
- Seleccione todo el cuadro y con la Ficha Inicio/Bordes/Todos los bordes.
- Observe que a la izquierda de los encabezados de columna hay tres botones: 1, 2 y
3. Si hace un clic en el botón 2, se esconde el detalle de las zonas y solo se muestran
los subtotales por cada Zona.

Si hace un clic en el botón 1, solo se mostraría el Total General.

Y si hace clic en el botón 3, se mostraría el cuadro completo
Para agrupar las columnas por trimestres, semestres o el Total, haga lo siguiente:

- Seleccionar el rango B3:D3, de Ene a Mar, y elija la Ficha Datos/ Agrupar/ Agrupar…;
y en la ventana que aparece, elegir “Columnas” y Aceptar.

- Seleccione ahora F3:H3, de Abr a Jun, y presione la tecla de función [F4]; esto
agrupará el 2do Trim.
- Repita lo anterior para los meses del 3er Trim y 4to Trim.
- Ahora seleccione el rango B3:I3, de Ene al 2do Trim, y presione [F4].
- Luego seleccione el rango K3:R3, de Jul al 4to Trim, y presione [F4].
- Finalmente seleccione B3:S3, de Ene al 2do Sem, y también presione [F4].
- (La tecla de función [F4] repite la última orden dada a Excel con la cinta de opciones)
El cuadro se vería entonces como en la siguiente figura:
TEXTO EN COLUMNAS Archivo: 11 Texto en columnas
Archivo de trabajo: 17 Texto en columnas
Se tiene una lista de datos escritos en una sola columna y que corresponden a los datos de
diferente alumnos. Esta lista se la desea separar en varias columnas como la que se
muestra en los títulos de la derecha en la figura:

Realizar los siguientes pasos:

- Seleccionar el rango B5:B16
Elegir la Ficha Datos - Herramientas de datos - Texto en columnas
- En la primera ventana que aparece “Paso 1 de 3” hacer clic en el botón Siguiente:

- En la siguiente ventana, marque el separador Espacio y haga clic en Siguiente

- En la siguiente ventana, marque Formato General
6. En la opción Destino, haga clic en D5
7. Haga clic en Finalizar
INSERTAR UNA LÍNEA DE TENDENCIA A LOS GRÁFICOS (12 Lín tendencia)

Archivo de trabajo: 18 Línea de tendencia

Las líneas de tendencia se usan para mostrar gráficamente las tendencias de los
datos y analizar los problemas de predicción. Este análisis también se denomina
análisis de regresión. Mediante el uso del análisis de regresión, puede representarse
una línea de tendencia en un gráfico más allá de los datos actuales para predecir los
valores futuros.

Tipos de gráfico que admiten líneas de tendencias

Pueden agregarse líneas de tendencia a las series de datos en los siguientes gráficos:
• De áreas 2D no apiladas,
• De barras,
• De columnas,
• De líneas,
• De cotizaciones,
• De tipo XY (Dispersión), y
• De burbujas.
No pueden agregarse líneas de tendencia a las series de datos en los gráficos 3D,
radiales, circulares, de superficie o de anillos
AGREGAR UNA LÍNEA DE TENDENCIA A UN GRÁFICO

- Haga clic secundario en una de las series de datos

- Elija Agregar línea de tendencia

A la derecha, se activa el panel de formato

de línea de tendencia.
Puede cambiar el tipo de Línea de
tendencia
Puede aplicar formatos
Puede agregar la ecuación en el gráfico.