Modulo De Excel Intermedio.docx

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA Lima – Perú

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA

Introducción

Estimado participante: En esta Unidad aprenderá acerca del uso de las funciones de Excel, aplicación de formatos condicionales avanzados y protección de elementos de la hoja de cálculo. Excel 2016 proporciona muchas funciones predeterminadas agrupadas en diferentes categorías que permiten realizar cálculos de diferentes tipos, facilitando la construcción de aplicaciones que involucran el desarrollo de cálculos sencillos y complejos. Veremos la utilización de formatos condicionales avanzados, los cuales están basados en una fórmula o criterio lógico, los cuales determinan la aplicación de dichos formatos. Finalmente, se aplicará protección a la hoja de cálculo o parte de ella, de tal manera que no pueda ser modificada sin la autorización correspondiente. Te deseo éxitos en el desarrollo de esta Unidad.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA

1 INTRODUCCIÓN A LAS FUNCIONES DE EXCEL Definición de función Una función es una fórmula predefinida por Excel 2016 (o por el usuario), que opera con uno o más argumentos (valores) para devolver un resultado. Estas funciones están agrupadas por categorías y su elección es sencilla, según el método que se emplee.

Sintaxis: Todas las funciones de Excel, tienen una misma estructura de ingreso a la hoja de cálculo, lo único que cambia son los argumentos utilizados por ellas; por eso se tiene como sintaxis general: Nombre_de_la_Función (arg1; arg2;....; argN)

Separador de grupo Dónde: 

Nombre_de_la_Función: es un nombre abreviado que identifica a la función.



(arg; arg2,....; argN): son los argumentos requeridos para proveer un resultado.



Separador de grupo: puede ser “;” o “,” (debe tener presente, ya que puede devolverle un Error como resultado).

2

INGRESAR UNA FUNCIÓN EN LA HOJA DE CÁLCULO Método 1: Escribiendo la función Este método consiste en “digitar la función

y sus argumentos” con los

datos de la aplicación, teniendo en cuenta la sintaxis de la función que estás empleando. ¡No olvide!, que cada función tiene argumentos diferentes. Para explicar adecuadamente este método, desarrolle el siguiente ejercicio:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA

Ejercicio: Se tiene las notas de los alumnos de una institución educativa, y se pide Calcular el puntaje total obtenido por cada uno de ellos. A continuación se detallan los pasos a realizar: 

Sitúe el cursor en la celda donde escribirá la función. Para nuestro ejemplo: celda F2



!

Escriba la función según su sintaxis. Ejemplo: =SUMA(B2:E2)

Importante: Observa que al escribir el nombre de la función, iniciando con el signo “=”, el programa te localiza rápidamente en una lista de funciones, la que desea utilizar; para ingresar sus argumentos.

l



Al escribir la función y sus argumentos, debe seguir exactamente la sintaxis que le muestra en ese instante el Excel:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA



Seleccione las celdas correspondientes B2:E2, cierra paréntesis.



Pulse la tecla Enter para ver el resultado.



Ahora, debe copiar la función obtenida, verticalmente desde el controlador de relleno.

!

Importante: ¡MUY BIEN!: Lo ha logrado, ya tiene los resultados deseados, este método es rápido, pero requiere de mucha práctica de su parte, ya que debe conocer conceptualmente sus argumentos, antes de ingresarlos. Le recomiendo que lo use, cuando logres mucha destreza en el uso de funciones. Ahora veremos el método 2.

Método 2: Usando el asistente “Insertar función” Este método consiste en “insertar la función y sus argumentos” a través del botón Insertar función, para luego obtener los resultados. Para explicar adecuadamente este método, desarrolla el siguiente ejercicio: Ejercicio: Ahora usando el mismo cuadro de datos, debe calcular la máxima nota de cada alumno. Previamente ingrese el texto “MAXIMA NOTA” en la celda G1. 

Sitúa el cursor en la celda donde se insertarás la función. Ejemplo: celda G2

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA



Haz clic en el botón Insertar función que está en la Barra de fórmulas



En el cuadro de diálogo “Insertar función”, seleccione la categoría y la función a usar: Ejemplo: Categoría: Estadísticas y Función: Max.



Haz clic en el botón Aceptar



Ingresa los argumentos solicitados. Puedes escribir el rango de celdas o seleccionarlo desde el mismo cuadro de datos (con arrastre). Ejemplo: Número1: B2:F2.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA



Ahora haz clic en el botón Aceptar, enseguida copia la fórmula hacia abajo.

!

Importante: ¡LO LOGRASTE!: Como debe haberse dado cuenta, este método es muy sencillo, ya que solo se ingresas o selecciona los argumentos; después de seleccionar la función. Cada argumento describe su uso al ser seleccionado. Le recomiendo que lo use, si está recién aprendiendo a trabajar con funciones. Ahora veremos el método 3.

Método 3: Uso del botón Autosuma Es otro método que también puedes usar, consiste en utilizar el botón Autosuma, que contiene básicamente a las funciones más usuales del programa.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

HERRAMIENTAS OFIMATICA AVANZADA

Para explicar adecuadamente este método, desarrolla el siguiente ejercicio: Ejercicio: Siguiendo con el mismo ejercicio, ahora debes calcular la mínima nota de cada alumno. Previamente ingresa el texto “MINIMA NOTA” en la celda H1. 

Haga clic en la celda donde se insertará la función. Ejemplo: celda H2



Seleccione la ficha Inicio, y del grupo Modificar elija la opción: Autosuma



Seleccione el nombre de la función a usar. Ejemplo: Min



Seleccione el rango de datos que se vas a usar (hazlo con arrastre). Ejemplo: B2:E2



Ahora pulsa Enter para obtener los resultados y copia la función verticalmente.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

!

Importante: ¡MUY BIEN…!: Es un método fácil que nos ayudará a utilizar las funciones en forma rápida. Le recomiendo que lo uses, es muy efectivo para un aprendizaje sencillo. Ahora veremos el método 4.

Método 4: Uso de la Biblioteca de Funciones Este método es un resumen del método 2, ya que las funciones están agrupadas por categorías en el grupo Biblioteca de funciones; para que las puedas seleccionar según el cálculo a realizar.

Para explicar este método, continuará desarrollando el siguiente ejercicio:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Ejercicio: Ahora debe calcular el promedio de cada alumno. Previamente ingrese el texto “PROMEDIO NOTAS” en la celda I1. 

Ubique el cursor en la celda donde insertará la función. Ejemplo: Celda I2



En la ficha Fórmulas y en el grupo Biblioteca de Funciones, localiza la categoría y función: Promedio.



Ingrese los argumentos requeridos. Seleccione el rango de datos, con arrastre.



Haga clic en el botón Aceptar y copia la función.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

NOTA: ¿QUE LE PARECIO?: Este método también es muy fácil de aprender. Puede usarlo como una nueva alternativa en el manejo de funciones. A continuación, verá con más detalle el uso de las funciones en el desarrollo de las siguientes aplicaciones agrupadas por categoría:

ESTADISTICAS,

MATEMATICAS

Y

TRIGONOMETRICAS, FECHA Y HORA, etc.

3

FUNCIONES ESTADÍSTICAS a) FUNCIÓN PROMEDIO: Devuelve el promedio de un rango numérico de datos seleccionado. Sintaxis: =PROMEDIO (Número1; Número2;...) Para explicar el uso de esta función, emplea el siguiente ejercicio: Ejercicio: Se tiene las ventas realizadas en el primer trimestre del presente año por los vendedores de la empresa TOLIMA´S S.A. Se pide obtener el promedio de ventas por vendedor. El método de funciones a usar quedará a su criterio según lo aprendido anteriormente.



Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Calculando el Promedio del Vendedor Para este caso usará el método 3: Botón Autosuma 

Haz clic en la celda E2, para obtener el Promedio Vendedor.



Como es una función básica, puede usar rápidamente el botón Autosuma (ficha Inicio):



Ahora debe seleccionar (con arrastre) los argumentos que serán promediados: B2:D2

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Pulse la tecla Enter para obtener el Promedio Vendedor. Ahora puede copiar la función al resto de vendedores (arrastre desde el controlador de relleno).

!

Importante: ¡BUEN LOGRO!, ya utilizaste la función Promedio para obtener el Promedio de las ventas de cada vendedor, pues aproxima los resultados a dos decimales. Ahora verá el uso de la función MIN.

b) FUNCIÓN MIN: Devuelve el valor mínimo de un rango de valores. Sintaxis: =MIN (Número1; Número2;...) Ejercicio: Ahora debe calcular la menor venta de cada vendedor. Para este ejercicio, tambien emplea el método 3: Botón Autosuma, por ser una función básica. Previamente debe ingresar en la celda F1, el texto: MINIMA VENTA.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Seleccione la celda F2,

para obtener la Mínima Venta por

vendedor. 

Haga clic en el botón Autosuma y elige la función MIN:



Ahora seleccione (con arrastre) el rango de datos donde se buscará el valor mínimo: B2:D2



Pulse la tecla Enter, para obtener el resultado. Puede copiar la función insertada al resto de vendedores:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

!

Importante: ¡MUY BIEN!, haz usado la función MIN, para obtener en un rango de datos numéricos el valor más bajo, ahora veremos la función MAX.

c) FUNCION MAX: Devuelve el valor máximo de un rango de valores. Sintaxis: =MAX (Número1; Número2;...) Ejercicio: También en el mismo cuadro de datos que estás trabajando deberá calcular la venta mayor de cada vendedor. Previamente debe ingresar en la celda G1, el texto “MAXIMA VENTA” y podrá usar también el botón Autosuma para obtener el resultado.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Seleccione la celda G2,

para obtener la Máxima Venta por

vendedor. 

Haz clic en el botón Autosuma y elige la función MAX:



Ahora seleccione (con arrastre) el rango de datos donde se buscará el valor máximo: B2:D2.



Pulse la tecla Enter, para obtener el resultado. Puede copiar la función insertada al resto de vendedores:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Importante:

!

¡FÁCIL VERDAD!, ha trabajado con la función MAX, para obtener en un rango de datos numéricos el valor más ALTO, ahora verá la función CONTAR.

d) FUNCIÓN CONTAR: Devuelve la cantidad de datos numéricos de un rango. Sintaxis: =CONTAR (Valor1; Valor2;...)

Ejercicio: Usando la misma aplicación anterior, debe calcular el Nº de ventas hechas en el trimestre. Previamente, debe ingresar en la celda C10, el texto “Nº Ventas hechas en el Trimestre” y podrá usar también el botón Insertar función para obtener el resultado.



Seleccione la celda D10, para que inserte la función CONTAR.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Seleccione la ficha Fórmulas y haga un clic en el botón Insertar función

de

la

Biblioteca

de

funciones.

Elija

la

categoría:

Estadísticas y la función CONTAR.



Luego, debe ingresar el argumento del conteo: Seleccione el rango de datos donde se hará el conteo.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Pulse la tecla Enter para obtener el resultado.

Importante:

!

¡IMPORTANTE! Debe tener siempre en cuenta que esta función solo se aplica sobre rango de datos numéricos. Ahora veremos la función CONTARA.

e) FUNCIÓN CONTARA: Devuelve la cantidad de datos de un rango de celdas llenas. Sintaxis: =CONTARA (Valor1; Valor2;...)

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Ejercicio: Debe calcular el Nº de vendedores. Previamente,

debe

ingresar

en

la

celda

C12,

el

texto

“Nº

Vendedores” y podrá usar también el botón Insertar función para obtener el resultado.



Selecciona la celda D12, para que insertes la función CONTARA.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Seleccione la ficha Fórmulas y haga un clic en el botón Insertar función

de

la

Biblioteca

de

funciones.

Elija

la

categoría:

Estadísticas y la función CONTARA.



Luego, debe ingresar el argumento del conteo: Seleccione el rango de datos donde se hará el conteo.



Pulse la tecla Enter para obtener el resultado.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

!

Importante: ¡IMPORTANTE! Debe tener presente que esta función a diferencia de contar, realiza el conteo sobre celdas que contengan cualquier tipo de datos. Ahora veremos la función CONTAR.BLANCO.

f) FUNCIÓN CONTAR.BLANCO: Devuelve la cantidad de celdas en blanco en un rango de celdas. Sintaxis: =CONTAR.BLANCO (Rango) Ejercicio: Calcule el Nº de ventas no realizadas en el trimestre. Previamente, debe ingresar en la celda C14, el texto “Nº Ventas no hechas en el trimestre”,

podrá usar también el botón Insertar

función para obtener el resultado.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Seleccione la celda D14, para insertar la función CONTAR.BLANCO



Seleccione la ficha Fórmulas y haga un clic en el botón Insertar función de la Biblioteca de funciones. Elija la categoría: Estadísticas y la función CONTAR.BLANCO.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



A continuación, debe ingresar el argumento del conteo: Seleccione el rango de datos donde se hará el conteo. Para este ejemplo: Todos los trimestres.



Pulse la tecla Enter para obtener el resultado.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

!

Importante: ¡IMPORTANTE! Debe tener presente que esta función solo cuenta celdas vacías, para obtener su resultado. Ha terminado con el análisis de las principales funciones estadísticas, pasara

ahora

estudiar

las

funciones

MATEMATICAS

Y

TRIGONOMETRICAS del Excel.

4

FUNCIONES MATEMÁTICAS Y TRIGONOMÉTRICAS a) FUNCIÓN SUMA: Devuelve la suma de todos los números de un rango de celdas. Sintaxis: =SUMA (Número1; Número2;...)

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Ejercicio 1: Se tiene información sobre el stock y las unidades vendidas de los productos de una distribuidora de bebidas y se pide calcular el total de unidades vendidas de Enero a Abril.

Como es una función muy básica, podemos escribir la función y sus argumentos para obtener el resultado. 

Seleccione la celda D10



Escriba la función y sus argumentos: = SUMA (D3:G8) y pulse Enter.



Habrás obtenido el resultado:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Importante:

!

¡IMPORTANTE! Como debe haberse dado cuenta, hemos obtenido el resultado escribiendo la función SUMA y sus argumentos, este método para este caso es sencillo ya que sus argumentos son fáciles de entender. Ahora veremos la función Entero.

b) FUNCIÓN ENTERO: Redondea un número hasta el entero inferior más próximo. Sintaxis =ENTERO (Número)

Ejercicio: Calcular el valor entero del promedio de las 3 notas. Recuerde que ya tenemos calculado el promedio de las notas, para ello use la función Promedio.

Ahora debe editar la función Promedio para obtener el Entero del Promedio: 

Haga doble clic sobre la celda donde se calculó el promedio (F3)

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Haga clic después del signo =

y escriba exactamente lo que se

muestra a continuación con rojo: =ENTERO(PROMEDIO (C3:E3))



Pulsa la tecla Enter y verás el resultado:



Finalmente, copie verticalmente la función obtenida para hallar el promedio entero del resto de alumnos.

!

Importante: Observación: Este ejercicio, muestra el valor entero en la misma celda donde está el promedio o valor original. Ahora verá cómo al mismo resultado no le aplicamos Entero, REDONDEAR, notará la diferencia.

sino la función

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

c) FUNCIÓN REDONDEAR: Redondea un número al número de decimales especificado. Sintaxis =REDONDEAR (Número; núm_decimales)

Ejercicio: Ahora redondee el mismo promedio de las 3 notas a 2 decimales. “Recuerde que al igual que en el caso anterior ya tiene calculado el promedio de las notas, con la función Promedio”.

Ahora, debe editar la función Promedio para obtener el Promedio Redondeado a 2 decimales: 

Haga doble clic sobre la celda donde se calculó el promedio (G3)



Haga clic después del signo =

y escriba exactamente lo que se

muestra a continuación con color rojo: =REDONDEAR (PROMEDIO (C3:E3), 2)

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Pulsa la tecla Enter y verás el resultado:



Finalmente, copie verticalmente la función obtenida para hallar el promedio redondeado del resto de alumnos.

Importante: Observación: Este ejercicio, muestra el valor REDONDEADO en la

!

misma celda donde está el promedio o valor original. Puede comparar con el anterior promedio obtenido a través de la función ENTERO, es diferente ya que este último promedio considera 2 decimales del valor original, mientras que el anterior solo la parte entera del promedio. También puede usar la función TRUNCAR para ver la diferencia entre ENTERO, REDONDEAR Y TRUNCAR.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

d) FUNCIÓN TRUNCAR: Trunca un número a un entero, suprimiendo la parte fraccionaria de dicho número. Sintaxis =TRUNCAR (Número; núm_decimales) Ejercicio: Ahora trunca el mismo promedio de las 3 notas a 2 decimales. “Recuerde que al igual que en el caso anterior ya tiene calculado el promedio de las notas, con la función Promedio”.

Ahora, debe editar la función Promedio para obtener el Promedio Truncado a 2 decimales: 

Haga doble clic sobre la celda donde se calculó el promedio (G3)



Haga clic después del signo =

y escribe exactamente lo que se

muestra a continuación con color rojo: =TRUNCAR(PROMEDIO (C3:E3); 2)



Pulse la tecla Enter y verá el resultado:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Finalmente, copia verticalmente la función obtenida para hallar el promedio redondeado del resto de alumnos.

Importante:

!

Observación: Aparentemente usando truncar y redondear es lo mismo, pero no es así, ya que REDONDEAR muestra con 2 decimales el resultado redondeado: 14.33, y tiene en cuenta si en el tercer decimal es igual o superior a 5, el último digito decimal aumentaría a 4, es decir quedaría 14.34; mientras que en TRUNCAR no se considera el valor de tercer decimal. Pruebe ingresando otras notas y observe los resultados.

5

FUNCIONES DE FECHA Y HORA Estas funciones son muy usuales cuando se realizan cálculos con fecha, por ejemplo: Tiempos de Servicio, días de mora, etc. Es por ello que se explicará su uso:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

a) FUNCIÓN HOY(): Muestra la fecha que tiene su PC. Observa que esta función no tiene argumentos. Sintaxis: =HOY () b) FUNCIÓN AHORA (): Muestra la fecha y hora que tiene la PC. Observa que esta función no tiene argumentos. Sintaxis: =AHORA ()

Ejercicio: Insertar la fecha actual y la hora del sistema.

Calculando la fecha actual: 

Seleccione la celda C8, para obtener la fecha actual



Escriba la función: =HOY () y pulsa la tecla Enter, verás que ya tiene la fecha actual del sistema.



Proceso muy similar seguirás para obtener la hora actual:



Haga clic en la celda C10, y escribe la función: =AHORA () y pulsa la tecla Enter, habrás obtenido la fecha y hora actual.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Pero si desea mostrar solo la hora actual, cambie el formato a la celda C10 (donde apareció el resultado): 

Seleccione la celda donde harás el cambio de formato: C10

!



En la ficha Inicio, selecciona el formato de Número



Elija el nuevo formato: Hora

Importante: ¡MUY BIEN!: Lo has logrado, pero debemos practicar más, recuerda la práctica nos hace diestros.

Visualiza los siguientes videos para complementar tus conocimientos:  Funciones básicas 01(ver carpeta Videos)  Funciones básicas 02(ver carpeta (Videos)

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

6

FUNCIONES CON CRITERIO LÓGICO Como se comentó líneas arriba, estas funciones ya utilizan criterios o condiciones para obtener sus resultados, por ello se le llaman con criterio lógico. ¡Empecemos! a) FUNCIÓN CONTAR.SI: Cuenta las celdas no vacías dentro de un rango, pero solo aquellas que cumplen el criterio especificado por ti. Sintaxis: =CONTAR.SI (rango, criterio) Ejercicio: Ingrese los siguientes datos y determina el número de empleados del Departamento de Ventas.

Calculando el Nº de empleados del Departamento de Ventas “Aquí el criterio del conteo es que sean del Departamento de Ventas” 

Seleccione la celda E17



Puede usar la biblioteca de funciones para insertar la función, para lo cual debe seguir la secuencia mostrada en la siguiente figura:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Ahora,

ingrese

los argumentos

solicitados, para

obtener

el

resultado:

!

Importante: ¡IMPORTANTE! Puede observar que el criterio es un texto: “Ventas”, el cual debe ser leído en el rango de los departamentos (B2:B15)



Haga clic en el botón Aceptar. El resultado será:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Calcular el Nº de empleados que tienen un sueldo superior a 1500 soles “Aquí el criterio del conteo es que su sueldo sean mayor a 1500” 

Seleccione la celda E18



Puede

escribir

también

=CONTAR.SI(F2:F15,">1500")

la

función,

así

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Calcular el Nº de empleados que tienen fecha de ingreso a partir del año 2005 “Aquí el criterio del conteo es que su fecha de ingreso sea mayor o igual al 1 de enero del 2005” 

Seleccione la celda E19



Puede escribir también la función, así: =CONTAR.SI(C2:C15,">=1/1/2005")

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

b) FUNCIÓN SUMAR.SI: Esta función, suma las celdas en un rango de datos, pero solo aquellas que coinciden con el criterio establecido. Sintaxis: =SUMAR.SI (rango, criterio, rango_suma) Ejercicio: Este ejercicio, explicará claramente el uso de esta función. Se tiene el mismo cuadro anterior, pero ahora se requiere conocer el Sueldo total de todos los empleados del Departamento de Contabilidad. “El criterio a usar para obtener el sueldo total de los empleados, es que solo sean del departamento de Contabilidad” 

Haga clic en la celda F21



Puede usar la biblioteca de funciones para insertar la función, para lo cual siga la secuencia mostrada en la siguiente figura:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Ahora debe ingresar los argumentos solicitados, para obtener el resultado:

Importante:

!

¡IMPORTANTE! Ahora

observe que el criterio es una un texto:

“Contabilidad”, el cual debe ser leído en el rango de los cursos (B2:B15), mientras que el rango_suma es el rango de las notas (F2:F15) que será sumado al ser evaluado el criterio.



Haz clic en el botón Aceptar. El resultado completo será:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

c) FUNCIÓN PROMEDIO.SI: Devuelve el promedio de todas las celdas de un rango que cumplen un criterio que tú determines. Sintaxis: =PROMEDIO.SI (rango, criterio, rango_promedio) Ejercicio: Para explicar esta función se requiere calcular el promedio de sueldos de los empleados de que están afiliados a la AFP Profuturo. “El criterio a usar para obtener el promedio de sueldos de los empleados es que solo sean de la AFP PROFUTURO” 

Haga clic en la celda F23



Puede usar la biblioteca de funciones para insertar la función, para lo cual debe seguir la secuencia mostrada en la siguiente figura:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Ahora debe ingresar los argumentos solicitados, para obtener el resultado:

!

Importante: ¡IMPORTANTE! Ahora

observe que el criterio es un texto:

“Profuturo”, el cual debe ser leído en el rango de las AFPs (D2:D15), mientras que el rango_promedio es el rango

de los

sueldos (F2:F15) que serán promediadas al ser evaluado el criterio.



Haz clic en el botón Aceptar. El resultado completo será:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

!

Importante: MUY FÁCIL ¡VERDAD! Las funciones con criterio lógico tienen un análisis muy sencillo, espero lo vuelva a practicar, ya que generalmente

la

información

a

obtener

siempre

está

condicionada.

7

FUNCIONES CON MÚLTIPLES CRITERIOS LÓGICOS a) FUNCIÓN CONTAR.SI.CONJUNTO: Realiza el conteo de las celdas que cumplen los criterios especificados, dentro del rango. Sintaxis: =CONTAR.SI.CONJUNTO (rango1, criterio1, rango2, criterio2…) Para ello explicaremos esta función sobre el mismo ejercicio que venimos trabajando. Copie el cuadro de datos a la hoja 2.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Ejercicio: Determine el número de empleados del Departamento de ventas con sueldos menores a 1500 soles.



Haga clic en la celda F17



Use la biblioteca de funciones para insertar la función, para lo cual siga la secuencia mostrada en la siguiente figura:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Ahora debe ingresar los argumentos solicitados, para obtener el resultado:

Importante: ¡INTERPRETACIÓN! Ahora

!

observa que el Rango_criterios1

(B2:B15), es el rango de los departamentos, Criterio1: “Ventas; Rango_criterios2 es el rango de los sueldos (F2:F15); y Criterio2: <1500.



Haga clic en el botón Aceptar. El resultado será:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

!

Importante: SENCILLO Y FÁCIL. Se dio cuenta, ha utilizado dos criterios para contar, cada criterio se establece en su propio rango de datos.

b) FUNCIÓN SUMAR.SI.CONJUNTO: Esta función suma las celdas de un rango de valores, pero solo a aquellos que cumplen con varios criterios que tú especifiques. Sintaxis: =SUMAR.SI.CONJUNTO(rango_suma, rango1_criterio, criterio1, rango2_criterio, criterio2…)

Ejercicio: Determine el sueldo total de los empleados afiliados a la AFP Profuturo y que tienen fecha de ingreso a partir del año 2006.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Haga clic en la celda F18 (combinar celdas con F19)



Use la biblioteca de funciones para insertar la función, siga la secuencia mostrada en la siguiente figura:



Ahora debe ingresar los argumentos solicitados, para obtener el resultado:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Importante: ¡INTERPRETACIÓN! Ahora observe que el Rango_suma (F2:F15), es el

!

rango

de

los

sueldos (ellos serán sumados según los criterios

especificados), Rango_criterios1 es el rango de las AFPs (D2:D15) y como Criterio1 se indica el nombre de la AFP que será evaluada, en este caso Profuturo y por último; Rango_criterios2 es el rango de las fechas de ingreso (C2:C15), en el que se ingresa 1/1/2006, es decir; todos los que ingresaron a partir del año 2006.



Haga clic en el botón Aceptar. El resultado será:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

c) FUNCIÓN PROMEDIO.SI.CONJUNTO Devuelve el promedio (media aritmética) de todas las celdas que cumplen múltiples criterios que tú especificaste Sintaxis: =PROMEDIO.SI.CONJUNTO (rango_promedio, rango_criterio1, criterio1, rango_criterio2, criterio2…) Ejercicio: Determina el sueldo promedio de los empleados

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Haga clic en la celda F20 (combinar celdas con F21)



Use la biblioteca de funciones para insertar la función, para lo cual debe seguir la secuencia mostrada en la siguiente figura:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Ahora debe ingresar los argumentos solicitados, para obtener el resultado:

Importante:

!

¡INTERPRETACIÓN! Ahora

observe que el Rango_promedio

(F2:F15), es el rango de los sueldos que serán promediados, Rango_criterios1 es el rango de las AFPs (D2:D15) y como Criterio1 se indica el nombre de la AFP que será evaluada, en este caso Integra y por último; Rango_criterios2 es el rango de número de hijos (E2:E15), en el que se indica si tiene hijos, es decir: >0 .



Haga clic en el botón Aceptar. El resultado será:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

!

Importante: ¡EXCELENTE! Es muy sencillo trabajar con este tipo de funciones ya que el uso de criterios o condiciones hacen que los resultados sean selectivos. Ahora veremos la función Frecuencia muy importante en la tabulación de un muestreo de datos.

d) FUNCIÓN SUMAPRODUCTO: Esta función, multiplica los componentes correspondientes de las matrices (rangos de datos) suministrados y devuelven la suma de esos productos. Sintaxis: =SUMAPRODUCTO (matriz1; matriz2; matriz3;...) Ejercicio: Calcula la venta total del siguiente cuadro de ventas.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Ingrese los datos y la función SUMAPRODUCTO, como se indica, en la celda B15.

Importante:

!

INTERPRETACIÓN! En este caso se utiliza dos rangos: cantidades y precios unitarios. Cada cantidad se multiplica por su precio unitario correspondiente, al final esos productos se suman dando como resultado la venta total.

Ejercicio: Calcule el interés simple total generado por tres capitales depositados a plazo fijo. La fórmula del Interés Simple = Capital * Tasa * Plazo Ingrese los siguientes datos y función según se muestra a continuación.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

!

Importante: INTERPRETACIÓN! En este caso se utiliza tres rangos: capitales, tasas y plazos. Cada capital se multiplica por su tasa y plazo correspondiente, al final esos productos se suman dando como resultado el interés total.

Visualiza los siguientes videos para complementar tus conocimientos:  Funciones con criterios lógicos (ver carpeta Videos)

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Práctica Desarrollada Teniendo como información las calificaciones de un grupo de alumnos correspondiente a la primera unidad, calcule el promedio ponderado según la siguiente fórmula:

Ingresa los siguientes datos:

Lo primero que debe calcular es el Peso Total. En la celda F2, ingrese la función =SUMA(B2:E2) o puede hacer uso del botón Autosuma y presionar Enter. Para calcular el Promedio del primer alumno, según se indica en la fórmula, debe sumar la multiplicación de cada calificación por su peso correspondiente, luego dividir dicha suma entre el Peso Total. Como dicha fórmula debe copiarse hacia abajo, para obtener los promedios del resto de alumnos, las referencias de cada uno de los pesos y el peso total, deben ser absolutas. Entonces, la fórmula a ingresar en la celda F4, sería: = (B4*$B$2+C4*$C$2+D4*$D$2+E4*$E$2)/$F$2

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Pero, como puede observar la fórmula contenida dentro del paréntesis puede ser sustituida por la función SUMAPRODUCTO, así: =SUMAPRODUCTO (B4:E4,$B$2:$E$2)/$F$2 En donde el rango de los pesos y la referencia del peso total son absolutos. A continuación se muestra el resultado:

FUNCIONES ESPECIALES a) FUNCIÓN SIFECHA: Es una función que nos permite obtener el número de días, meses y años que han transcurrido entre dos fechas determinadas. Sintaxis: =SIFECHA(Fecha_inicial,Fecha_final,”Parámetro”) El parámetro a utilizar en la última parte de la fórmula puede ser: 

“D” si queremos obtener los días que han transcurrido



“M” si queremos obtener los meses



“Y” si queremos obtener los años



“YM” si queremos obtener los meses sin tener en cuenta los años



“MD” si queremos obtener los días sin tener en cuenta los meses

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Ejercicio: Determinar la Edad y el Tiempo de servicios en años de los siguientes empleados.

Ingrese los datos y la función SIFECHA como se indica en la imagen.

!

Importante: ¡IMPORTANTE! Para calcular la edad, debes considerar la fecha de nacimiento y la fecha actual, representada por la función HOY(). Además, como la edad se está calculando en años, el parámetro a usar es “Y”. En forma similar, para calcular el tiempo de servicio, debe considerar la fecha de ingreso y la fecha actual.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Ejercicio: Determinar su record laboral en años, meses y días de los siguientes empleados.

Ingrese los datos y la función SIFECHA como se indica en la imagen.

!

Importante: ¡IMPORTANTE! Para calcular los años del record laboral, la forma es la misma que se utilizó para determinar el tiempo de servicios en años. Sin embargo, para calcular los meses restantes sin considerar los años ya contabilizados, usa el parámetro “YM”. Para determinar los días restantes sin considerar los meses y años contabilizados, use el parámetro “MD”.

¿Dónde está la función SIFECHA? Uno de los misterios de Excel es la función SIFECHA. Observará que esta función no aparece en la lista de funciones desplegable de la categoría Fecha y Hora. La función SIFECHA tiene su origen en Lotus 1-2-3 y aparentemente, Excel la ofrece para mantener la compatibilidad. Por alguna razón, Microsoft quiere mantener esta función en secreto.

Página |4

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

b) FUNCIÓN FRECUENCIA: calcula la frecuencia con que se repiten los valores dentro de un rango, devolviendo una matriz vertical de números. Sintaxis =Frecuencia (datos, grupos) “El uso de frecuencias se explicará en el siguiente ejercicio”. Ejercicio: Se hizo una encuesta para conocer las edades de los pacientes atendidos en el Hospital de Neoplásicas, la muestra poblacional fue de 120 encuestados. Se pide clasificar dicha muestra con una de amplitud de 10, considerando un límite mínimo de 29 y un límite máximo de 79, obteniendo la frecuencia absoluta y relativa.

Para desarrollar esta aplicación necesita tener el cuadro donde clasificarás la muestra y obtendrás las frecuencias. Entonces, ingresa siguiente:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Creando los Grupos de datos: Estos Grupos o rangos servirán para clasificar las edades de los encuestados. Se sabe que existe un límite mínimo de 29 para el primer rango y un máximo de 79 para el último rango, con una amplitud de 10. o o

Digite en la celda K10, el dato 29 como límite del primer grupo Ahora, en la celda K11, ingresa el dato 39 como límite de este grupo, este valor se obtiene sumando al límite anterior, la amplitud que es de 10; y así sucesivamente sobre el resto de grupos. Quedará el cuadro con los grupos así:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Calculando las frecuencias absolutas: o

Seleccione el

rango

de celdas

donde

se

obtendrán

todas las

frecuencias: L10:L15 o

Puede usar la biblioteca de funciones para insertar la función, siga la secuencia mostrada en la siguiente figura:

o Ahora, debe ingresar los argumentos solicitados, para obtener el resultado:

!

Importante: ¡INTERPRETACIÓN! En el cuadro Datos (B5:I19) contiene el rango de la muestra, y Grupos (K10:K15), son los datos que ingreso a partir de los límites y la amplitud, llamados también intervalos de clase.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

o La función FRECUENCIA por ser una función de tipo matricial, los resultados se mostrarán presionando las teclas: CRTL+SHIFT+ENTER:



Calculando las frecuencias relativas: Estas frecuencias se obtienen con la fórmula: =frecuencia Absoluta/Total muestra. Por ello debe calcular el total de la muestra con la función SUMA: o

Haga clic en la celda L16

o

Escriba la función: =SUMA(L10:L15) y pulsa la tecla Enter.

Ahora ya puede calcular la frecuencia relativa: o

Haga clic en la celda M10

o

Escriba la fórmula: =L10/$L$16 y pulsa la tecla Enter.

o

Copie la fórmula hacia abajo y ya tendrás la información completa.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

NOTA: Observe que la frecuencia absoluta es cuantitativa y la frecuencia relativa es porcentual.



2 FUNCIONES DE TEXTO a) FUNCIÓN IZQUIERDA Extrae el número indicado de caracteres, colocados a la izquierda o al principio de la cadena de texto. Sintaxis: =IZQUIERDA(Texto, Núm_de_caracteres)



Texto: Es la cadena de texto que contiene los caracteres que se desea extraer.



Núm_de_caracteres: Especifica el número de caracteres que se desea extraer con la función IZQUIERDA.

NOTAS: 

Núm_de_caracteres debe ser mayor o igual a cero.



Si Núm_de_caracteres es mayor que la longitud del texto, IZQUIERDA devolverá todo el texto.



Si Núm_de_caracteres se omite, se calculará como 1.



Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

b) FUNCIÓN DERECHA Extrae el número indicado de caracteres, colocados al final de la cadena de texto. Sintaxis: =DERECHA(Texto, Núm_de_caracteres) 

Texto: Es la cadena de texto que contiene los caracteres que se desea extraer.



Núm_de_caracteres: Especifica el número de caracteres que se desea extraer con la función DERECHA.

NOTAS: 

Núm_de_caracteres debe ser mayor o igual a cero.



Si Núm_de_caracteres es mayor que la longitud del texto, DERECHA devolverá



todo el texto. 

Si Núm_de_caracteres se omite, se calculará como 1.

Ejercicio: Uso de las funciones IZQUIERDA y DERECHA Los datos de referencia tienen un delimitador que es el punto y coma. Se desea separar todas las palabras que están antes y después de dicho delimitador. En el primer caso, utiliza la función IZQUIERDA para extraer los 15 primeros caracteres (“América del Sur”) del dato en referencia. En el segundo caso, utiliza la función DERECHA para extraer los 4 últimos caracteres (“Perú”) del dato en referencia.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

NOTA: 

Las funciones ingresadas no deben ser copiadas verticalmente, ya que la cantidad de caracteres a extraer tanto de la izquierda como de la derecha, son diferentes.





Cada caso es particular.

c) FUNCIÓN MED: Obtiene el número de caracteres de una cadena de texto a partir de una posición indicada. Sintaxis: =MED(Texto, Posición_inicial,Núm_de_caracteres) 

Texto: Es la cadena de texto que contiene los caracteres que se desea extraer.



Posición_inicial: Posición del primer carácter que se desea extraer del texto. La Posición_inicial del primer carácter de texto es 1, y así sucesivamente.



Núm_de_caracteres: Específica el número de caracteres que se desea que MED devuelva del argumento texto.

A considerar:  Si posición_inicial es mayor que la longitud de texto, MED devuelve “” (texto vacío).  Si posición_inicial es menor que la longitud de texto, pero posición_inicial más núm_de_caracteres excede la longitud de texto, MED devuelve los caracteres hasta el final de texto.  Si posición_inicial es menor que 1, MED devuelve el valor de error #¡VALOR!  Si núm_de_caracteres es negativo, MED devuelve el valor de error #¡VALOR! Ejercicio: Del texto que se muestra en la celda C4, se desea extraer la palabra “Sur” Debes tener en cuenta la posición de la letra “S”, si cuentas, su posición es 12. Desde esa posición extrae 3 caracteres, que es la cantidad de caracteres de la palabra “Sur”. Utiliza la función MED como se indica en la imagen.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

d) FUNCIÓN CONCATENAR Une varios elementos de texto en uno solo. Sintaxis: =CONCATENAR (texto1,texto2,…) 

texto1, texto2,... son de 1 a 255 elementos de texto que serán unidos en un elemento de texto único. Los elementos de texto pueden ser cadenas de texto, números o referencias a celdas únicas.

Ejercicio: Se desea mostrar en una sola celda tanto los apellidos y los nombres:

Utilice

la

función

CONCATENAR,

donde el texto1 son los apellidos (celda A2), el texto2 es un espacio en blanco (“ “) y el texto3 son los nombres (celda B2). Copie la función verticalmente. Si desea separar los apellidos de los nombres, no por un espacio en blanco sino por una coma, entonces el texto2 será la coma más un espacio en blanco. Así:

Después de copiar el resultado será el siguiente:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

Ejercicio: Existe un una tienda por secciones en la cual se necesita realizar una organización de los datos de tal manera que esté asociado un código con una sección en específica, sin embargo los códigos que se tienen están con caracteres adicionales que no se requieren, por lo tanto se necesita hacer la extracción del código antes para que sea combinado con el nombre de la sección.

Inicialmente usarás la función MED y luego emplearás la función de Texto CONCATENAR para hacer la combinación de la sección con el código. El uso de la función MED simplifica el trabajo en alto grado ya que optimizamos el tiempo que normalmente invertimos en los procedimientos manuales y además que puede ser anidada con una gran diversidad de funciones con lo que se crean fórmulas nuevas para solucionar problemas más complejos. e) FUNCIÓN HALLAR: Devuelve el número de caracteres en el cual se encuentra un carácter en particular o cadena de texto, leyendo de izquierda a derecha (no diferencia entre mayúsculas ni minúsculas). Sintaxis: =HALLAR(Texto_buscado,Dentro_del_texto,Núm_inicial) 

Texto_buscado: es el texto que desea encontrar.



Dentro_del_texto: es el texto en que se desea encontrar el Texto_buscado.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Núm_inicial: es, contando desde la izquierda, el número del carácter en Dentro_del_texto desde donde se desea iniciar la búsqueda. Si se omite, se usa 1.

Por ejemplo, para buscar la ubicación de la letra "p" en la palabra "impresora", puede usar la siguiente función: =HALLAR("p","impresora") Esta función devuelve 3 porque "p" es el tercer carácter en la palabra "impresora." Además, puede buscar por palabras dentro de otras palabras. Por ejemplo, la función =HALLAR("medio";"promedio") devuelve 4, porque la palabra "medio" comienza en el cuarto carácter de la palabra "promedio". Ejercicio: Uso de la función HALLAR. Utilice la función HALLAR para encontrar la posición del carácter indicado en el dato de referencia.

En este caso se puede omitir la posición inicial, así

!

=HALLAR(C3,B3)

Importante: ¡IMPORTANTE! El carácter asterisco “*” es un carácter especial que representa un carácter o conjunto de caracteres cualquiera, es por eso que el resultado en la columna posición encontrada es 1.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

f) FUNCIÓN LARGO: Devuelve el número de una cadena de texto Sintaxis: =LARGO(Texto) 

Texto: es el texto cuya longitud se desea conocer. Los espacios cuentan como caracteres.

En el siguiente ejemplo, se ha determinado el número de caracteres que tiene el dato de referencia.

Ejercicio: Uso de funciones combinadas Se desea separar todas las palabras que están a lado izquierdo y derecho del delimitador punto y coma. Para este caso, debe usar una combinación de las funciones de texto, así:



Al utilizar la función IZQUIERDA, el problema es determinar en forma general la cantidad de caracteres a extraer hasta antes del punto y coma. Para ello, usa la función HALLAR para determinar la posición del punto y coma, luego resta menos 1, para determinar la cantidad exacta de caracteres a extraer desde el inicio.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



Al utilizar la función DERECHA, el problema es determinar en forma general la cantidad de caracteres a extraer después del punto y coma. Para ello, usa la función LARGO para determinar la cantidad de caracteres de todo el texto, luego usa la función HALLAR para determinar la posición del punto y coma, que restarás de la longitud total.

Ejercicio: Una Empresa desea generar el código de su nuevo grupo de empleados, tomando en cuenta la siguiente estructura: CODIGO = Primera letra del apellido + Dos primeras letras del nombre + la categoría + Año Ingreso

Procedimiento: 1.

Seleccione la celda B3

2.

Digite lo siguiente: =CONCATENAR(IZQUIERDA(C3,1),MED(C3,HALLAR(" ",C3)+1,2),D3,E3)

3.

Presione Enter

4.

Copie la fórmula a las celdas siguientes y observa el resultado.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

g) FUNCIÓN MINUSC: Convierte todas las letras de un texto en minúsculas. Sintaxis: =MINUSC(Texto) Texto

es el texto que desea convertir en minúsculas. Los caracteres en Texto que no sean letras no cambiarán.

h) FUNCIÓN MAYUSC: Convierte todas las letras de un texto en mayúsculas. Sintaxis: =MAYUSC(Texto) Texto

es el texto que desea convertir en mayúsculas. Los caracteres en Texto que no sean letras no cambiarán.

i)

FUNCIÓN NOMPROPIO: Pasa en mayúsculas la primera letra de todas las palabras de la cadena de texto y el resto de los caracteres en minúsculas. Así como cualquiera de las otras letras que se encuentren después de un carácter que no se una letra (espacios, números, signos de puntuación, etc.). Sintaxis: =NOMPROPIO(Texto)

Ejercicio: Uso de las funciones MINUSC, MAYUSC y NOMPROPIO

P á g i n a | 17

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

3 FORMATO CONDICIONAL AVANZADO El formato condicional avanzado utiliza una fórmula como condicionante en la aplicación de los formatos seleccionados. A continuación mostramos una aplicación en donde la condición del formato condicional es una fórmula: Ejercicio: Al ingresar un código en la celda C9, automáticamente debe resaltarse las celdas con relleno de color amarillo (código, artículo y stock) del artículo correspondiente en la tabla.

Sigue los pasos siguientes: 1. Seleccione el rango de celdas que desea formatear, en este caso A2:A7 2. En la ficha Inicio, en el grupo Estilo, haz clic en la flecha junto a Formato condicional y, a continuación, haga clic en Nueva regla… 

En el cuadro Selecciona un tipo de regla, haz clic en la opción Utilice una fórmula que determine las celdas para aplicar formato

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



En el cuadro Dar formato a los valores donde esta fórmula sea verdadera, introduce la siguiente fórmula: =A2=$C$9



Haga clic en el botón Formato y luego selecciona la ficha Relleno y elije el color amarillo. Finaliza haciendo clic en el botón Aceptar.

Repite los mismos pasos para los rangos B2:B7 y C2:C7, la fórmula en el formato condicional es la misma. El resultado será el siguiente:

Ejercicio: Resaltar con color verde, los nombres de los vendedores cuyas ventas son mayores a 5000 soles.

Siga los pasos siguientes: 1. Seleccione el rango de celdas que desea formatear, en este caso A2:A8 2. En la ficha Inicio, en el grupo Estilo, haga clic en la flecha junto a Formato condicional y, a continuación, haga clic en Nueva regla…

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO



En el cuadro, Seleccione un tipo de regla, haga clic en la opción Utilice una fórmula que determine las celdas para aplicar formato



En el cuadro Dar formato a los valores donde esta fórmula sea verdadera, introduzca la siguiente fórmula: =B2>5000



Haga clic en el botón Formato y luego selecciona la ficha Relleno y elije el color verde. Finaliza haciendo clic en el botón Aceptar.

El resultado será el siguiente:

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

4

PROTECCIÓN DE ELEMENTOS Los resultados obtenidos en una hoja de cálculo son producto de simples o complicados procesos que exigen ser protegidos de modificaciones o vulneraciones accidentadas o mal intencionadas. Existen distintos niveles de protección dentro de un Libro de Excel disponibles según las necesidades de los usuarios. A Nivel de hoja: Permite proteger los diversos elementos, atributos y recursos que componen una hoja de cálculo detallados en una lista. Protege el contenido de todas las celdas configuradas para acatar una protección de la Hoja. Procedimiento: 1. Clic derecho en la etiqueta de la Hoja 2. Clic en la opción Proteger Hoja 3. Activar las casillas que PERMITAN al usuario liberar de la protección el elemento, atributo o recurso que necesita.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

4. Haga clic en el botón Aceptar y confirmar la clave

A Nivel de celdas: Permite proteger un rango de celdas. Ejercicio: Proteger las celdas que contienen fórmulas y además hacer que no se muestren.

Procedimiento 1. Seleccione toda la hoja de cálculo 2. De la ficha Inicio, del comando Formato, elige la opción Formatos de celdas 3. Luego, haga clic en la Ficha Proteger y desactiva la casilla Bloqueada.

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

4. Ocultando la fórmula: a. Seleccione las celdas que contienen fórmulas, en nuestro caso C2:C8 b. De la ficha Inicio, comando Formato, elija la opción Formatos de celdas c. Luego, Seleccione la ficha Proteger y marque las casillas Bloqueada y Oculta

5. Ahora, Proteja la hoja: Inicio  Formato  Proteger hoja 6. Verifique la protección de las celdas.

A NIVEL DE LIBRO(ARCHIVO)

Instituto Nacional de Estadística e Informática

Escuela Nacional de Estadística e Informática

NOMBRE DEL CURSO

PRÁCTICA GUIADA A 40 estudiantes se les pidió que estimen el número de horas que habrían dedicado a estudiar la semana pasada (tanto en clase como fuera de ella), obteniéndose los siguientes resultados: 36

30

47

60

32

35

40

50

54

35

45

52

48

58

60

38

32

35

56

48

30

55

49

39

58

50

65

35

56

47

37

56

58

50

47

58

55

39

58

45

Se pide hallar las frecuencias relativas y absolutas, si se sabe que existe un límite mínimo de 35 para el primer rango y un máximo de 65 para el último rango, con una amplitud de 6. Solución: a) Ingrese los siguientes datos en la hoja de cálculo:

b) Seleccione el rango C13:C18 (donde se van obtener las frecuencias absolutas)

P á g i n a | 24

c) Escriba la función =FRECUENCIA seleccionando el rango de datos (A5:H9) y grupos (B13:B18), como se muestra a continuación:

d) Para obtener las frecuencias absolutas, presiona CTRL + SHIFT + ENTER

e) Ahora, para obtener las frecuencias relativas, primero tiene que hallar el total de las frecuencias absolutas. Así:

En la celda C19, ingrese la función SUMA usando el botón Autosuma.

!

Importante: ¡IMPORTANTE! El total de las frecuencias absolutas debe coincidir con la cantidad de datos de la muestra, para este caso, debe ser 40.

f) Luego, en la celda D13, ingrese la fórmula =C13/$C$19 y luego copie hasta la celda D18.

!

Importante: ¡IMPORTANTE! Recuerde, que para obtener las frecuencias relativas tiene que dividir cada frecuencia absoluta entre el total de datos.

Después de copiar los datos, el resultado es:

g) Aplica formato estilo porcentual para mostrar las frecuencias relativas en forma de porcentajes.

h) Finalmente, hallar la suma de las frecuencias relativas y centra los datos. El resultado final será:

FUNCIONES LOGICAS La función SI en Excel La función SI en Excel es parte del grupo de funciones Lógicas y nos permite evaluar una condición para determinar si es falsa o verdadera. La función SI es de gran ayuda para tomar decisiones en base al resultado obtenido en la prueba lógica. Sintaxis de la función SI

Además de especificar la prueba lógica para la función SI también podemos especificar valores a devolver de acuerdo al resultado de la función.

  

Prueba_lógica (obligatorio): Expresión lógica que será evaluada para conocer si el resultado es VERDADERO o FALSO. Valor_si_verdadero (opcional): El valor que se devolverá en caso de que el resultado de la Prueba_lógica sea VERDADERO. Valor_si_falso (opcional): 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. Ejemplos de la función SI Probaremos la función SI con el siguiente ejemplo. Tengo una lista de alumnos con sus calificaciones correspondientes en la columna B. Utilizando la función SI desplegaré un mensaje de APROBADO si la calificación del alumno es superior

o igual a 60 y un mensaje de REPROBADO si la calificación es menor a 60. La función que utilizaré será la siguiente: =SI(B2>=60,"APROBADO","REPROBADO") Observa el resultado al aplicar esta fórmula en todas las celdas de la columna C.

Utilizar una función como prueba lógica Es posible utilizar el resultado de otra función como la prueba lógica que necesita la función SI siempre y cuando esa otra función regrese como resultado VERDADERO o FALSO. Un ejemplo de este tipo de función es la función ESNUMERO la cual evalúa el contenido de una celda y devuelve el valor VERDADERO en caso de que sea un valor numérico. En este ejemplo quiero desplegar la leyenda “SI” en caso de que la celda de la columna A efectivamente tenga un número, de lo contrario se mostrará la leyenda “NO”. =SI(ESNUMERO(A2), "SI", "NO") Este es el resultado de aplicar la fórmula sobre los datos de la hoja:

Utilizar una función como valor de regreso Como último ejemplo mostraré que es posible utilizar una función para especificar el valor de regreso. Utilizando como base el ejemplo anterior, necesito que en caso de que la celda de la columna A contenga un valor numérico se le sume el valor que colocaré en la celda D1. La función que me ayudará a realizar esta operación es la siguiente: =SI(ESNUMERO(A2), SUMA(A2, $D$1), "NO") Como puedes observar, el segundo argumento es una función la cual se ejecutará en caso de que la prueba lógica sea verdadera. Observa el resultado de esta fórmula:

Sólo en los casos donde la función SI era verdadera se realizó la suma. De la misma manera podríamos colocar una función para el tercer argumento en caso de que el resultado de la prueba lógica fuera falso.

Ejemplo 1: ANUNCIOS

Supongamos que tenemos una partida de plátanos, de manera que si no miden igual o más que 15 cm de longitud no los podemos vender en el mercado. Catalogaremos como “Apto” el plátano que podemos vender (mide igual o más de 15 cm) y como “No apto” el pequeño.

Utilizamos la función lógica de la desigualdad para identificar los plátanos aptos de los no aptos. Cuando miden 15 o más centímetros, la prueba lógica es verdadera y por lo tanto toma el valor_si_verdadero, es decir, “Apto”. En caso contrario es “No apto”. La fórmula SI utilizada es:

=SI (B2>=15; “Apto”; “No apto”)

Ejemplo 2: Ahora pondremos otro ejemplo con una función de texto. Supongamos que tenemos un listado de los nombres de los trabajadores de una empresa. Queremos determinar si el nombre de cada trabajador es “Largo” o “Corto”. Supongamos que consideramos que un nombre es largo si tiene más de siete letras.

Para este caso utilizamos la función de texto LARGO. Esta función se aplica sobre una cadena y determina el número de caracteres que tiene un campo. Por ejemplo, LARGO(“Ramón”)=5 porque tiene 5 carácteres. Por tanto, determinamos el número de caracteres que tiene cada nombre y con la prueba lógica valoramos si su nombre es largo o corto. La fórmula utilizada es: =SI (LARGO(A2)>7; “Largo”; “Corto”)

Ejemplo 3: En este ejemplo, realizaremos una función en la que el valor_si_verdadero y valor_si_falso pueden ser también fórmulas del excel. Tenemos un listado de alumnos universitarios, de manera que no todos asistieron a clase. Marcamos la asistencia a clase en “Si” o “No”. Todos los alumnos realizaron el examen y el trabajo final. La nota de trabajo final podría subir hasta un punto, con la condición de que se asiste a clase. En caso contrario, la nota sería la obtenida en el examen final.

En esta caso, si se cumple la prueba_lógica (el alumno ha ido a clase) el valor_si_verdadero es la fórmula Excel SUMA, la cual suma la nota del examen y la del trabajo. La fórmula del SI utilizada es: =SI (B2=”Si”; SUMA(C2,D2); C2)

Ejemplo 4: Los vendedores de una empresa tienen en su sueldo una parte fija y otra variable, que es un incentivo a comisión, según las ventas realizadas. El incentivo es un porcentaje creciente sobre las ventas. Si vende más de 20.000 €, tiene un incentivo del 8% sobre la cantidad vendida, si la venta está entre 20.000 € y más de 15.000 €, le añaden un 6%, si vende entre 15.000 € y más de 9.000 €, el suplemento es el 4%. Finalmente, si las

ventas son de 9.000 € o una cantidad inferior, el incentivo se limitarà a un 1% sobre la cantidad vendida. La fórmula consta de una série de funciones SI que están anidadas en una de las alternativas de la función SI de Excel anterior, ocupando el lugar de la alternativa “falso” de la prueba lógica. Obsérvese que, para añadir a la parte variable del sueldo los 600 € fijos, sumamos la casilla D2. Para fijar el valor de esta celda, que está en la columna D y en la fila 2, usamos la sintaxis $D$2.

Fijémonos en la celda D2.

=SI(B2>20000; B2*8%+$D$2; SI(B2>15000; B2*6%+$D$2; SI(B2>9000; B2*4%+$D$2; B2*1%+$D$2))) La fórmula la hemos resuelto yendo de más a menos en el orden de las condiciones para los incentivos. Pero, veamos cómo obtendríamos igual resultado si ordenásemos las condiciones en sentido ascendente:

=SI(B2<=9000; B2*1%+$D$2; SI(B2<=15000; B2*4%+$D$2; SI(B2<=20000; B2*6%+$D$2; B2*8%+$D$2)))

Ejemplo 5: Este ejercicio, incluye unas condiciones más complejas que requerirán introducir la función Y y la función O. Un departamento de Recursos Humanos está buscando cubrir un puesto para un profesional en un puesto cualificado. Después de una primera fase, van a seleccionar a los candidatos finales, a los que se les exige que hayan cursado un máster MBA, que tengan 5 o más años de experiencia en el puesto y que su nivel de inglés sea alto. Pero exigen también o una licenciatura universitaria en Informática o bien la licenciatura en Estadística.

Vemos que la función SI realiza la prueba lógica que determina la decisión buscada. Pero, dentro de la función SI está la función Y que criba a los aspirantes que cumplan todas las condiciones pedidas (MBA, experiencia, inglés y titulación). Pero, para resolver la doble posibilidad de o licenciatura en Informática o en Estadística, a su vez, se ha añadido la función O, que detecta si, al menos se dispone de una de las dos.

=SI(Y(C2=”MBA”; D2>=5; E2=”alto”; O(F2=”Estadístico”;F2=”Informático”)); “SI”; “descartado”)

FUNCIÓN SI ANIDADA Está función se emplea cuando evaluamos una condición con más de dos posibilidades dentro de la solución, usando dentro de la función SI otra función SI. Ejercicio 01: Se desea saber el Nivel obtenido de cada alumno de acuerdo a la tabla de lado derecho.

Solución: 1. Seleccionar la celda D3 2. En la ficha Fórmulas abrir el botón Lógicas, luego ingresar a la función SI de la biblioteca de funciones.

3. Ingresar los argumentos de la función en la ventana que se muestra a continuación: 4

1 2 3

1

En Prueba_lógica ingresar: C3>18

2

En el Valor_si_verdadero ingresar: “EXCELENTE”

3

En el valor_si_falso se debe dar clic dentro del cuadro.

4

Se debe hacer clic sobre la lista desplegable del cuadro de nombres para activar otra función Si e ir completando la información requerida.

El resultado de todo el procedimiento anterior de la fórmula es: =SI (C3>18,”EXCELENTE”, SI (C13>15,”BUENO”, SI (C13>10,”REGULAR”,”MALO”)))

Nota: Tambien puede escribir toda la sintaxis de la función Si anidada anterior.

Observación: 

Es posible anidar hasta 64 funciones SI como argumentos valor_si_verdadero y valor_si_falso para crear pruebas más complicadas.



Tome en cuenta que a veces se puede realizar otro tipo de anidamientos (una función como uno de los argumentos de otra función). Por ejemplo, la siguiente fórmula utiliza la función anidada PROMEDIO y compara el resultado con el valor 50:

=SI(PROMEDIO(F2:F5)>50,SUMA(G2:G5),0)

Ejercicio 02: Se tiene el siguiente reporte de evaluación de los alumnos de una Institución Educativa referente a un curso, y se desea conocer su condición final

Solución: 1. Haga clic en la celda I4 2. Escriba la fórmula siguiente: =SI (G4<=4, SI (H4>=10.5,"Aprobado","Desaprobado"),"Inhabilitado")

Ejercicio 03: Una agencia de turismo ofrece comisiones a sus vendedores, de acuerdo al sector que pertenezcan. Completar la tabla de datos de acuerdo a las consignas que aparecen debajo de la misma.

Solución: 1. Seleccione la celda D13. 2. Digite lo siguiente: =SI (B13=”INT”,5%, SI (B13=”NAC”,3%,0))*C13 Otra forma sería usando las celdas, de la siguiente manera: =SI (B13=”INT”, $B$9, SI (B13=”NAC”, $B$10,0))*C13

3. Presione la tecla Enter. 4. Copie verticalmente la fórmula a las celdas siguientes y observe el resultado. Ejercicio 04: Determinar el Aumento y Nuevo Sueldo de cada Empleado, teniendo en cuenta que la tasa de Aumento (% Aumento) se asigna en función de la Zona en donde trabaja. Considere: Aumento= % Aumento*Sueldo actual y Nuevo sueldo= Sueldo Actual + Aumento.



Haga clic en la celda D7 e inserte la fórmula: =SI (B7=”SUR”, $B$2, SI (B7=”CENTRO”, $B$3, SI (B7=”NORTE”, $B$4)))*C7

Ejercicio 05: Si sueldo es mayor que 2000, entonces en la columna Observación colocar “Alto”, si el sueldo es menor que 1000 colocar “Bajo”, caso contrario colocar “Medio.”



Haga clic en la celda C2 e inserte la fórmula: =SI (B2>2000,”ALTO”, SI (B2<1000,”BAJO”,”MEDIO”))*C7

Ejercicio 06: En un depósito se encuentran almacenados distintos productos. En la tabla adjunta debajo se detallan los códigos de identificación y otras características más.

1. Haga clic en la celda E5 2. Escriba la función si anidada: =SI(D5="A","Máxima Precaución",SI(D5="B","Precaución Moderada", SI(D5="C", "Precaución de Rutina")))

2. FUNCIÓN SIERROR Está función devuelve un valor determinado en caso que una fórmula o celda contenga un error. Utilice la función SI.ERROR para interceptar y controlar errores en una fórmula. Sintaxis: =SI.ERROR(valor, valor_si_error) Dónde: 

valor: puede ser una referencia a otra celda, o una fórmula.



valor_si.error: valor a mostrar en caso que el argumento valor devuelva un error.

Ejercicio 01: En la empresa SNACKS se desea obtener el porcentaje de gastos con relación a las ventas para cada zona.

Solución: 1. Seleccionar la celda E3 2. En la ficha Fórmulas abrir el botón Lógicas, luego ingresar a la función SIERROR.

3. Ingresar los argumentos de la función en la ventana que se muestra a continuación:

1 2

1

En Valor ingresa: D3/C3, gastos sobre ventas.

2

En Valor_si_error ingresa: “-“, nos devolverá un guión en caso se produzca un error.

3. FUNCIONES DE TEXTO Este tipo de funciones nos sirve para manipular datos tipo texto, cadena, o carácter. Excel posee varias funciones que permiten manipular texto, se detallan algunas a continuación:

Función / Descripción Función CONCATENAR Concatena varios elementos de texto en uno solo. Función IGUAL Comprueba si dos valores de texto son idénticos. Función IZQUIERDA Devuelve los caracteres del lado izquierdo de un valor de texto. Función LARGO Devuelve el número de caracteres de una cadena de texto. Función MINUSC Pone el texto en minúsculas. Función MED (EXTRAE) Devuelve un número específico de caracteres de una cadena de texto que comienza en la posición que se especifique. Función NOMPROPIO Pone en mayúscula la primera letra de cada palabra de un valor de texto. Función REEMPLAZAR Reemplaza caracteres de texto. Función REPETIR Repite el texto un número determinado de veces. Función DERECHA Devuelve los caracteres del lado derecho de un valor de texto. Función HALLAR Busca un valor de texto dentro de otro (no distingue mayúsculas de minúsculas). Función SUSTITUIR Sustituye texto nuevo por texto antiguo en una cadena de texto. Función TEXTO Da formato a un número y lo convierte en texto.

EJEMPLO =CONCATENAR(“UCV”,”TRUJILLO”)

=IGUAL(“María”,”María”)

=IZQUIERDA(“Universidad Vallejo”,11)

=LARGO(“Vallejo”) =MINUSC(“UNIVERSIDAD”)

=MED(“José Cruz”,6,4)

=NOMPROPIO(“césar”)

=REEMPLAZAR(“María Vargas”,1,5,"Ana") =REPETIR(“UCV”,4)

=DERECHA(“Curso Libre”,5)

=HALLAR(" ",”Alianza Lima”)

=SUSTITUIR(“SERGIO ALEJANDRO”,"SERGIO","Omar") =TEXTO("14/10/2010","dddd")

Función / Descripción Función RECORTAR (ESPACIOS) Quita los espacios del texto. Función MAYUSC Pone el texto en mayúsculas. Función VALOR Convierte un argumento de texto en un número

EJEMPLO =RECORTAR(“Juan

Saavedra”)

=MAYUSC(“universidad césar vallejo") =VALOR("400")

Ejercicio 01: Se pide realizar los siguientes ejercicios que se muestran a continuación: Solución: 1. Seleccione la celda C5. 2. Digite lo siguiente: =MAYUSC (B5) 3. Seleccione la celda C14. 4. Digite lo siguiente: =MINUSC (B14) 5. Seleccione la celda C23. 6. Digite lo siguiente: =NOMPROPIO (B23) 7. Seleccione la celda F5. 8. Digite lo siguiente: =CAR(E5) 9. Seleccione la celda F14. 10. Digite lo siguiente: =LARGO (E14) 11. Seleccione la celda F23. 12. Digite lo siguiente: =REPETIR (E23, 2) 13. Copie la fórmula en las celdas siguientes y observe el resultado.

Ejercicio 02: Una empresa desea generar el código de su nuevo grupo de empleados, tomando en cuenta la siguiente estructura: Considere: CODIGO = Primera letra del apellido + Dos primeras letras del nombre + la categoría + Fecha Ingreso

Solución: 1. Seleccione la celda A5. 2. Seleccione la ficha Fórmulas, elija el botón Texto dela biblioteca de funciones y seleccione la función Concatenar. Ingrese los argumentos solicitados:

O también digite lo siguiente: =CONCATENAR(IZQUIERDA(B5,1),MED(B5,HALLAR(" ",B5)+1,2),C5,D5) 3. Presione Enter. 4. Copie verticalmente la formula a las celdas siguientes y observe el resultado.

FUNCIÓN BUSCAR: Esta función de búsqueda tiene dos formas de sintaxis: matriz y vector. “Explicaremos cada una de ellas con los siguientes ejercicios”. Ejercicio: Se desea localizar el precio unitario de un artículo en la matriz Lista de Precios, usando el código del mismo del artículo como dato buscado. Este ejercicio lo desarrollaremos por ambos métodos para que tú puedas comparar y elegir el que más conveniente. A. Por Matriz: La búsqueda se realiza en un rango de varias filas y varias columnas. Sintaxis: = BUSCAR (valor_buscado, matríz) Dónde: 

Valor_buscado: Dato

a

buscar para

encontrar el

resultado

esperado. 

Matriz: Rango de celdas que contiene en la primera fila o columna los valores buscados y en la última fila o columna los resultados a reportar.

Por lo tanto, en nuestra aplicación tendríamos lo siguiente:

Debes tener en cuenta que la MATRIZ contiene -En la primera columna o fila, los valores referencial a buscar. Ej.: CODIGO -En la última columna o fila, los posibles valores a reportar. Ej.: PRECIO UNITARIO

Solución: 1. Selecciona la celda C2, ya que es aquí donde se obtendrá el resultado de la búsqueda. 2. Utiliza cualquier método aprendido para insertar la función solicitada. Ej.: En la ficha Fórmulas, abre el botón Búsqueda y referencia, y luego elige la función BUSCAR.

3. A continuación, en la ventana Seleccionar argumentos, haz clic en la opción: valor_buscado, matriz.

4. Finalmente, ingresa los argumentos de la función: Observación: matriz,

En

se

la han

seleccionado

también

los

de

nombres

los

artículos; ya que están en el rango necesario para

seleccionar

el

precio unitario.

Donde: 

C1: celda que tiene el valor a buscar (código del artículo)



A6:C13: rango de celdas o matriz (A6:C13) aquí se buscará el Código del articulo y se obtendrá el precio del artículo en base a ese código. Si lo deseas está fórmula también puedes escribirla: = BUSCAR (C1, A6:C13)

5. Comprueba la búsqueda: Ingresa un código de artículo (en la celda C1) y pulsa Enter. Se obtuvo el precio unitario de dicho artículo (en la celda C2).

Observa cómo a partir de

un

dato

(CODIGO), información UNITARIO)

referencial

obtienes

la

(PRECIO desde

un

cuadro de datos. FACIL VERDAD, claro que sí. SIGAMOS

con

otra

técnica de búsqueda.

B. Por VECTOR: La búsqueda se realiza en un rango de una columna y varias filas o varias columnas y una fila. Usaremos el mismo ejercicio. Sintaxis: =BUSCAR (Valor_buscado, Vector_de_comparación, Vector_resultado)

Dónde: 

Valor buscado: dato referencial a buscar, para obtener el resultado esperado.



Vector de comparación: Rango de celdas donde solo se buscarán los posibles valores buscados.



Vector resultado: rango de celdas donde se encuentran los posibles resultados a obtener.

Solución: 1. Selecciona la celda C2 2. En la ficha Fórmulas, abre el botón Búsqueda y referencia, luego ingresa a la función BUSCAR. 3. En la ventana Seleccionar argumentos, haz clic en la opción: valor_buscado, vector de comparación, vector resultado.

OBSERVA: No hemos

4. Finalmente, ingresa los argumentos de la función:

seleccionado el rango de los nombres de los artículos;

ya

estamos

que usando

vectores para buscar, y

ese

rango

necesario.

no

es

Dónde: 

C1: celda que tiene el valor a buscar (código del artículo)



A6:A13: rango de celdas donde se buscará sólo el código del artículo.



C6:C13: rango de celdas donde solo se encontrarán a los precios del artículo que se desea reportar.

Si deseas está fórmula también puedes escribirla: = BUSCAR (C1, A6:A13, C6:C13) 5. Comprueba la búsqueda: Ingresa un código de artículo (en la celda C1) y pulsa Enter. Se obtuvo el precio unitario de dicho artículo (en la celda C2).

!

Importante: El objetivo de búsqueda es el mismo que el método anterior, pero aquí tanto el rango de valores a buscar están separados del rango donde están los resultados a obtener. TAMBIEN ES FACIL, pero. SIGAMOS con otra técnica de búsqueda.

2. FUNCIÓN CONSULTAV (BUSCARV): Esta función se usa para buscar un valor (dato) específico en forma vertical en una tabla de datos. Esta función hace la búsqueda en la primera columna de una matriz y encuentra el resultado en la misma fila de una columna específica de la matriz de búsqueda.

Sintaxis: =CONSULTAV (Valor_buscado, Matriz_buscar_en, Indicador_columnas, Ordenado)

Donde: 

Valor_buscado: dato a buscar para encontrar el resultado.



Matriz_buscar_en: Rango de celdas donde se hará la busqueda del valor y se encontrará el resultado a obtener.



Indicador de columna: Número de columna en la matriz del resultado a obtener.



Ordenado: Puede usar Verdadero (Coincidencia aproximada del dato a buscar) o Falso (Coincidencia exacta del dato a buscar).

Ejercicio: Se desea asignar el NIVEL a cada alumno a apartir del puntaje obtenido en un examen de clasificación en IDIOMAS. Para ello se tiene una tabla de valoración de puntajes, cada nivel debe ser reportado luego de buscarse el puntaje obtenido por el alumno.

Solución: Usaremos la función CONSULTAV (BuscarV), ya que el valor referencial (Puntajes) estan distribuidos en la tabla de valoración en forma vertical. 1. Asigna el nombre Tabla_valora al rango A4:B9. (si lo deseas puedes dar este paso)

2. Selecciona

la

celda

F5

e

inserta

la

función

de

búsqueda

CONSULTAV: 3. Selecciona la ficha Fórmulas, el botón Búsqueda y referencia, y haz clic en CONSULTAV 4. En la ventana que muestra, debes llenar los argumentos:

Donde: 

Valor_buscado: Se indica la celda E5 (puntaje del alumno) como valor a buscar en el Rango A4:B9. La búsqueda lo hace en la primera columna de la matriz.



Matriz_buscar_en: Escriba el nombre Tabla_valora que corresponde a la matriz donde se busca un valor y se obtendrá el resultado. De lo contrario puedes seleccionar el rango.



Indicador_columnas: Escribe 2, ya que es el número de columna que corresponde a la ubicación del nivel a encontrar como resultado.



Ordenado: Como los datos de la primera columna de la matriz Tabla_valora están

ordenados (ver tabla de

valoración), entonces este casillero se deja en blanco o se escribe VERDADERO para que haga la búsqueda por aproximación. 5. Del

procedimiento

anterior

se

obtiene

la

fórmula

=CONSULTAV(E5,Tabla_valora,2); luego está fórmula debes copiar para las demás celdas del rango F5:F14. 6. El resultado será el siguiente:

Importante:

!

Observa también el objetivo de buscar un valor referencial para obtener un resultado, se cumple. Pero a diferencia de los métodos anteriores, la matriz de búsqueda define la localización del resultado con un valor que indica el Nº DE COLUMNA donde éste se encuentra. AHORA veremos otra técnica de búsqueda.

3. FUNCIÓN CONSULTAH (BUSCARH): Esta función se usa para buscar un valor (dato) específico en forma horizontal en una tabla de datos. Esta función hace la búsqueda en la primera columna de una matriz y encuentra el resultado en la misma fila de una columna específica de la matriz de búsqueda. Sintaxis: =CONSULTAH (Valor_buscado, Matriz_buscar_en, Indicador_filas, Ordenado) Ejercicio: Se desea asignar el SUELDO BASICO y ASIGNACION a cada empleado en base a su CATEGORIA.

Solución: Usaremos la función CONSULTAH, ya que el valor referencial (Categorías) estan distribuidos en la tabla de valoración en forma horizontal. A. Calculando el Básico: 1. Asigna el nombre Categorias al rango G2:J5 (si lo deseas). 2. Selecciona la celda C4 e inserta la función de búsqueda CONSULTAH. 

Selecciona

la

ficha

Fórmulas,

el

botón

Búsqueda

y

referencia, y haz clic en CONSULTAH.

3. En la ventana que muestra, debes llenar los argumentos de la función CONSULTAH.

Donde: 

Valor_buscado: Se ingresa la celda B4 (Categoría del docente) como valor a buscar en la matriz G2:J5. La búsqueda lo hace en la primera fila del rango.



Matriz_buscar_en:

escribe

el

nombre

de

rango

Categorías que corresponde a la matriz donde se busca un valor y encuentra el resultado. 

Indicador_filas: Escribe 3 ya que es el que corresponde a la ubicación de la fila donde están los sueldos básicos a encontrar.



Ordenado: Como lo que se busca es la categoría del empleado, entonces en este casillero se escribe FALSO para que haga la búsqueda exacta.

4. Haz clic en el botón Aceptar, y habrás obtenido el resultado.

5. Ahora, podrás copiar la formula obtenida para el resto de empleados:

B. Calculando la Asignación: 1. Selecciona la celda D4 e inserta la función de búsqueda CONSULTAH 

Selecciona

la

ficha

Fórmulas,

el

botón

Búsqueda

y

referencia, y haz clic en CONSULTAH

2. En la ventana que muestra como resultado el procedimiento anterior, debes llenar los argumentos de la función CONSULTAH.

Donde: 

Valor_buscado: Se escribe la celda B4 (Categoría del docente) como valor a buscar en la matriz G2:J5. La búsqueda lo hace en la primera fila del rango.



Matriz_buscar_en:

Escribe

el

nombre

de

rango

Categorías que corresponde a la matriz donde se busca el valor y encuentra el resultado. 

Indicador_filas: Escribe 4 ya que es el número de fila que corresponde a la ubicación del % Asignación.



Ordenado: Como lo que se busca es la categoría del empleado, entonces en este casillero se escribe FALSO para que haga la búsqueda exacta.

3. Haz clic en el botón Aceptar y obtendrás el resultado.

4. Lo que se ha encontrado es el % Asignación, este valor debes multiplicarlo por el BASICO, para obtener el monto de la asignación. 

Haga clic en la celda donde está la función que acabamos de insertar. Ej.: D4



En la barra de fórmulas, multiplica el resultado obtenido por C4 que es el BASICO y pulsa Enter.

5. Copia la formula obtenida para el resto de empleados: Importante:

!

Observa también el objetivo de buscar un valor referencial para obtener un resultado, se cumple. Pero a diferencia de la función CONSULTAV, la matriz de búsqueda define la localización del resultado con un valor que indica el Nº DE FILA donde éste se encuentra. AHORA veremos la última técnica de búsqueda.

4. FUNCIÓN INDICE: Devuelve un valor o la referencia a un valor en una tabla o rango. La función INDICE presenta dos formas: matricial y de referencia. Esta técnica es la más sencilla, ya que se basa en localizar un dato en una matriz, a partir de su ubicación en dicha matriz: Nº fila y Nº columna, Aquí no se busca un dato referencial, solo la ubicación del valor. Sintaxis:

=INDICE (Matriz, Num_fila, Num_col)

Dónde: 

Matriz: Rango de celdas donde se hará la búsqueda del elemento a partir de un N° Fila y N° Columna determinado.



Num_Fila: N° Fila donde está el elemento a encontrar.



Num_Columna: N° Columna donde está el elemento a encontrar.

Ejercicio: Se pide localizar el nombre del alumno de la columna C4:C13, a partir de su posición en la matriz.

Solución: 1. Haz clic en la celda F3, ya que es donde se mostrará el resultado. 2. Inserta la función INDICE: 

Selecciona la Ficha Fórmulas y en el grupo Biblioteca de funciones, haz clic en el botón Búsqueda y referencias y elige INDICE.

3. Ingresa el argumento a usar:

4. Haz clic en el botón Aceptar 5. Ingresa los argumentos solicitados:

6. Haz clic en el botón Aceptar 7. Los resultados son:

!

Importante: Puedes darte cuenta que en esta función no se busca un valor referencial, sino se localiza la ubicación del elemento en la matriz de datos. Bien hemos terminado la

unidad de hoy, espero practiques bastante estas

técnicas de búsqueda para lograr destreza sobre ellas. NOS VEMOS….

PRACTICA GUIADA Ejercicio 01: Crear una aplicación que genere una consulta de búsqueda, donde se ingrese el código de un vendedor para reportar la ciudad en la que trabaja. Use la función BUSCAR por vector. 1.

Selecciona la celda C17

2. En la ficha Fórmulas, abre el botón Búsqueda y referencia, luego ingresa a la función BUSCAR. 3. En la ventana Seleccionar argumentos, haz clic en la opción: valor_buscado, vector de comparación, vector resultado.

4. Finalmente, ingresa los argumentos de la función:

Dónde: 

C17: celda que tiene el valor a buscar (código del artículo)



A3:A13: rango de celdas donde se buscará sólo el código del artículo.



C3:C13: rango de celdas donde solo se encontrarán a los precios del artículo que se desea reportar.

Si deseas está fórmula también puedes escribirla: = BUSCAR (C17, A6:A13, C6:C13)

5. Comprueba la búsqueda: Ingresa un código del vendedor (en la celda C17) y pulsa Enter. Se obtuvo el nombre de la ciudad (en la celda C18).

Ejercicio 02: Se tiene la siguiente hoja de cálculo donde se debe calcular el sueldo a partir de su código; así como también calcular su bonificación, descuento y sueldo neto de cada trabajador; a partir de su categoría.

Procedimiento: CALCULANDO INFORMACION SOLICITADA EN EL CUADRO DE INFORMACION: A. Calculando el Cargo del empleado según el Cod_Cargo: 1. Haga clic en la celda E10 2. Haga clic en la ficha Fórmulas/elija el Búsqueda y Referencia/Buscar

3. Seleccione el argumento: valor_buscado, matriz.

4. Haga clic en el botón Aceptar e ingrese los argumentos solicitados.

B. Calculando el Sueldo según el Cod_Cargo: 1. Haga clic en la celda F10 2. Inserte la función Índice (Ficha Fórmulas/grupo Biblioteca de funciones/botón Búsqueda y referencias/INDICE.

3. Ingrese los argumentos solicitados:

4. Haga clic en el Aceptar. 5. Ingrese los datos solicitados:

C. Calculando la Bonificación según la Categoría: 1. Seleccionar la celda H10 e inserta la función de búsqueda CONSULTAH (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAH)

2. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAH. -

-

-

-

Valor_buscado  Se indica la celda G10 (Categoría del empleado) como valor a buscar en el Rango H3:K5. La comparación lo hace en la primera fila del rango. Matriz_buscar_en  matriz donde se busca un valor y retorna otro de la misma columna en la fila indicada. Indicador_filas  Es un número que corresponde a la ubicación de la columna que devuelve un resultado. Ordenado  Como los datos de la primera fila de la matriz NO están ordenados, entonces en este casillero se indica FALSO para que haga la búsqueda exacta.

D. Calculando el Descuento según la Categoría: 1. Seleccionar la celda I10 e inserta la función de búsqueda CONSULTAH (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAH)

2. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAH. -

-

-

-

E. Calculando el Sueldo Neto:

Valor_buscado  Se indica la celda G10 (Categoría del empleado) como valor a buscar en el Rango H3:K5. La comparación lo hace en la primera fila del rango. Matriz_buscar_en  matriz donde se busca un valor y retorna otro de la misma columna en la fila indicada. Indicador_filas  Es un número que corresponde a la ubicación de la columna que devuelve un resultado. Ordenado  Como los datos de la primera fila de la matriz NO están ordenados, entonces en este casillero se indica FALSO para que haga la búsqueda exacta.

1. Haga clic en la celda I10 2. Escriba la fórmula: =F10+H10-I10

BUSCANDO DATOS DEL EMPLEADO A PARTIR DE SU CÓDIGO: A. Para obtener el Empleado: 1. Seleccionar la celda D26 e introducir la función de búsqueda CONSULTAV (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAV) 2. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAV.

3. Del procedimiento anterior se obtiene la fórmula: =CONSULTAV(D25,$B$10:$J$19,2,FALSO) B. Para obtener el Cargo: 1. Haga clic en la celda D27 2. Introducir la función de búsqueda CONSULTAV (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAV) 3. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAV.

4. Del procedimiento anterior se obtiene la fórmula: =CONSULTAV(D25,$B$10:$J$19,4,FALSO) C. Para obtener el Sueldo: 1. Haga clic en la celda D28 2. Introducir la función de búsqueda CONSULTAV (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAV) 3. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAV.

D. Para obtener Categoría 1. Haga clic en la celda D29 2. Introducir la función de búsqueda CONSULTAV (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAV) 3. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAV.

E. Para obtener Bonificación: 1. Haga clic en la celda D30 2. Introducir la función de búsqueda CONSULTAV (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAV) 3. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAV.

F.

Para obtener Descuento: 1. Haga clic en la celda D31 2. Introducir la función de búsqueda CONSULTAV (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAV) 3. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAV.

G. Para obtener el Sueldo Neto: 1. Haga clic en la celda D32 2. Introducir la función de búsqueda CONSULTAV (Ficha Fórmulas, Botón Búsqueda y referencia, Clic CONSULTAV) 3. En la ventana que muestra como resultado el procedimiento anterior, se debe llenar los argumentos de la función CONSULTAV.

Comprobar la aplicación desarrollada:  Ahora que las funciones están ingresadas podemos comprobar la veracidad del proceso que desarrollaste.  Ingresa un nuevo código del alumno y verás como cambia la información