LA COMPUTADORA: EXCEL (FUNCIONES) 1001

Introducir Funciones: Una función es una fórmula predefinida por Excel (o por el usuario) que opera con uno o más valores y devuelve un resultado que aparecerá directamente en la celda o será utilizado para calcular la fórmula que la contiene.

La sintaxis de cualquier función es:

Siguen las siguientes reglas:

Si la función va al comienzo de una fórmula debe empezar por el signo =.
Los argumentos o valores de entrada van siempre entre paréntesis. No dejes espacios antes o después de cada paréntesis.
Los argumentos pueden ser valores constantes (número o texto), fórmulas o funciones.
Los argumentos deben separarse por un punto y coma ;.

TALLER FUNCIONES

Investigar y desarrollar en el cuaderno 6 categorías de funciones en Excel. Las más usadas.
Explicar cada una de las categorías.
Nombrar mínimo 5 funciones que corresponda a cada categoría.
Explicar 2 funciones que corresponda cada categoría donde explique el concepto, la sintaxis y un ejemplo.

FUNCIÓN SI

La función SI en Excel es una de las funciones más utilizadas. Nos permite evaluar una condición y en función del resultado nos dará un resultado u otro. Esta función es de las que se consideran básicas en Excel y nos permite combinarla con otras muchas funciones incluso siendo un argumento de las mismas.

SINTAXIS

La sintaxis de la función SI en Excel es muy sencilla pero hay que comprender unos conceptos clave como veremos un poco más adelante. La sintaxis es:

SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Quizás con la siguiente imagen se pueda entender un poco mejor:

La función SI tiene un claro carácter lógico. Evalúa si se cumple o no una determinada condición (prueba_lógica) y en función de ello devuelve un resultado (valor_si_verdadero) u otro (valor_si_falso). Como vemos en la imagen anterior, de la prueba lógica sólo pueden salir dos resultados: el verdadero o el falso.

A continuación vamos a explicar cada uno de sus argumentos

Prueba_lógica: Cualquier condición que pueda evaluarse como VERDADERA o FALSA. Para ello, Excel pone a nuestra disposición los seis operadores lógicos (>, <, =, >=, <=, <>).
Valor_si_verdadero: La función devolverá lo que aquí le indiquemos en caso de que la prueba lógica haya resultado ser verdadera. Este resultado podrá ser desde un texto, en cuyo caso deberá ir entre comillas (por ejemplo “soy un texto” o un texto vacío “”) hasta un número (-2, 0, 1478 etc). En caso de no escribir absolutamente nada en este argumento, la función por defecto devolverá 0 (cero).
Valor_si_falso: La función devolverá lo que aquí le indiquemos en caso de que la prueba lógica haya resultado ser falsa. De manera análoga a cuando era verdadero, debemos asignar un valor. En caso de no poner este argumento opcional Excel devolverá en este caso el operador lógico FALSO.

EJEMPLOS DE LA FUNCIÓN SI EN EXCEL

EJEMPLO 1: FUNCIÓN SI CON RESULTADO VERDADERO

En este ejemplo queremos saber si la celda A6 es mayor que la celda A7. Para ello escribimos la función que podemos ver a continuación.

=SI(A6>A7;VERDADERO;FALSO)

Ten en cuenta que en la función SI o en cualquier función en Excel no hace falta que escribas VERDADERO o FALSO entre comillas puesto que son variables preparadas por Excel. En cambio, si quieres poner otros valores de texto sí que deberás escribir las soluciones entre comillas:

=SI(A6>A7;"Es mayor";"No es mayor")

El resultado de la función SI en Excel en este caso es VERDADERO.

EJEMPLO 2: FUNCIÓN SI CON SUMA EN VALOR SI VERDADERO

En el mismo ejemplo que el anterior pero cambiando las celdas queremos que la función devuelva la suma de los números en caso de ser VERDADERO. Para ello aplicaremos la función SI de Excel con este formato:

=SI(A13 > A14; A13 + A14 ; FALSO)

Como en este caso el valor de A13 es mayor que el de A15, entonces la función nos devolverá el número 25. Ahora bien, si la prueba lógica no se hubiese cumplido el valor que hubiera arrojado es el valor FALSO.

AMPLIACIÓN: FUNCIÓN SI EN EXCEL CON VARIAS CONDICIONES

En ocasiones tenemos que hacer cumplir dos condiciones de Excel en una misma función SI o en varias funciones SI. A continuación vamos a explicarte cómo se puede hacer una función SI en Excel con dos condiciones.

CASO 1: CUANDO ANIDAMOS DOS FUNCIONES SI UNA DENTRO DE LA OTRA.

Este es el caso por el que empezamos casi todos en Excel, se trata de poner una serie de condiciones anidadas. Por ejemplo, si Yolanda es rubia y además si Yolanda es guapa entonces pondremos «OK» pero si no lo es entonces pondremos «NO OK».

Vamos a verlo con un dibujo para que se entienda mejor como el siguiente.

Como ves es un árbol de decisión y al final hemos visto que tiene 2 ramas.

Para construir esto en Excel primero escribiremos la primera condición SI tal y comos sigue: Si Yolanda es Rubia entonces en el valor si verdadero escribiremos OK y luego en valor FALSO escribiremos NO OK.

Ten en cuenta que la prueba lógica deberán ser celdas o referencias en Excel por lo que vamos a asumir que en la celda A1 de una hoja de Excel pone si Yolanda es Rubia o Morena…..

=SI( A1 = "Rubia" ; "OK" ; "NO OK")

Como puedes ver en la función anterior sólo tenemos una condición… ahora vamos a añadir la segunda condición, es decir, si YOLANDA es guapa. Supongamos que este atributo de YOLANDA se encuentra en la celda A2. Para ello añadiremos una segunda función SI dentro de la primera función SI en Excel.

=SI( A1 = "Rubia" ; SI( A2 = "Guapa"; "OK"; "NO OK") ; "NO OK")

lo que equivale a:

=SI( A1 = "Rubia" ; SI( A2 = "Guapa"; "Es rubia y guapa"; "Es rubia pero no guapa") ; "ni rubia ni guapa")

Como puedes ver, esta función es algo más complicada pero con un poco de práctica podrás hacer miles de maravillas y complicar las funciones tanto como quieras.

CASO 2: CUANDO TENEMOS DOS CONDICIONES DENTRO DE UNA MISMA FUNCIÓN SI CON EXCEL

Para poder poner dos condiciones dentro de Excel deberemos usar la función Y en Excel.

Para ello vamos a usar el siguiente esquema:

Como puedes ver en la imagen anterior hemos unido las dos variables en una única fila y la función que usaremos es la siguiente:

=SI ( Y ( A1 = "Rubia"; A2 = "Guapa" ) ; "OK" ; "NO OK")

En este caso habrá únicamente dos resultados. Si se cumplen las dos condiciones o si no se cumplen las dos condiciones.

TALLER FUNCIÓN SI

1. Crear un archivo EXCEL y guardarlo en su dispositivo de almacenamiento con la siguiente nomenclatura:

1001.Nombre.Apellido.funcionSI

2. Aplicando la función SI desarrollar el siguiente ejercicio: Crear en el archivo el siguiente cuadro que es un inventario de artículos de un almacén.

En la casilla casilla E1 colocar el titulo: IVA%.
En la columna E desde la Fila E2 hasta la E11 colocar los siguientes valores de IVA respetando el orden indicado: 15,15,20,20,15,20,15,15,15,15.
En la casilla o celda F1 colocar el titulo: Aviso de compra de artículos.
En la columna F mediante la función SI indicar un aviso de "Comprar" a los artículos que presenta menos de 10 unidades y sino cumple con la condición con la leyenda "No".
En la columna G mediante una función SI aplicarle un descuento del 20% a los artículos que presentan una cantidad mayor a 10 unidades.
En la columna H mediante una función anidada indicar un aviso de "Cumple" a los que tenga una cantidad mayor de 10 artículos (Columna D) y que tengan un IVA=20 (Columna E).

FUNCIÓN BUSCAR V

La función BUSCARV en Excel nos permite encontrar un valor dentro de un rango de datos, es decir, podemos buscar un valor dentro de una tabla y saber si dicho valor existe o no. Esta función es una de las más utilizadas para realizar búsquedas en Excel por lo que es importante aprender a utilizarla adecuadamente.

Para utilizar la función BUSCARV debemos cumplir con algunas condiciones en nuestros datos. En primer lugar debemos tener la información organizada de manera vertical, es decir organizada por columnas. Esto es necesario porque la función BUSCARV recorre los datos de manera vertical (por eso la letra “V” en el nombre de la función) hasta encontrar la coincidencia del valor que buscamos.

SINTAXIS DE LA FUNCIÓN BUSCARV

La gran mayoría de las funciones de Excel tienen argumentos que son la manera en cómo le indicamos los datos con los que trabajará así como ciertos criterios de ejecución. En el caso de la función BUSCARV tenemos cuatro argumentos que describo a continuación:

BUSCARV(valor_buscado;matriz_buscar_en;indicador_columnas;ordenado)

Valor_buscado (obligatorio): Este es el valor que queremos encontrar y el cual será buscado en la primera columna del rango de datos. Podemos colocar el texto encerrado en comillas o podemos colocar la referencia a una celda que contenga el valor buscado. La función BUSCARV no hará diferencia entre mayúsculas y minúsculas.
Matriz_buscar_en (obligatorio): El segundo argumento es una referencia al rango de celdas que contiene los datos. e trata del rango que se corresponde con la tabla o matriz donde han de buscarse los datos.
Indicador_columnas (obligatorio): El Indicador_columnas es el número de columna que deseamos obtener como resultado. Una vez que la función BUSCARV encuentra una coincidencia del Valor_buscado nos devolverá como resultado la columna que indiquemos en este argumento.
Ordenado (opcional): Este argumento es un valor lógico, es decir falso o verdadero. Con este argumento indicamos a la función BUSCARV el tipo de búsqueda que realizará y que puede ser una búsqueda exacta (FALSO) o una búsqueda aproximada (VERDADERO). Si este argumento se omite se supondrá un valor VERDADERO.

EJEMPLO DE LA FUNCIÓN BUSCARV

Comenzaremos con un ejemplo sencillo donde utilizaremos la función BUSCARV para realizar una búsqueda dentro de un directorio telefónico. Para hacer una búsqueda con la función BUSCARV sobre los datos de ejemplo, seguiremos los siguientes pasos:

En la celda E1 colocaré el valor que deseo buscar y que es uno de los nombres de la columna A.
En la celda E2 ingresaré el nombre de la función BUSCARV de la siguiente manera: =BUSCARV(
Inmediatamente después de ingresar el paréntesis haré clic en la celda E1 para incluir la referencia de celda e introduzco una coma (,) para concluir con el primer argumento de la función: =BUSCARV(E1,
Para especificar el segundo argumento, debo seleccionar la tabla de datos sin incluir los títulos de columna que para nuestro ejemplo será el rango A2:B11. Una vez especificada la matriz de búsqueda debo introducir una coma (,) para finalizar con el segundo argumento: =BUSCARV(E1,A2:B11,
Como tercer argumento colocaré el número 2 ya que quiero que la función BUSCARV me devuelva el número de teléfono de la persona indicada en la celda E1. Recuerda que la numeración de columnas empieza con el 1 y por lo tanto la columna Teléfono es la columna número 2. De igual manera finalizo el tercer argumento con una coma (,): =BUSCARV(E1,A2:B11,2,
Para el último argumento de la función especificaré el valor FALSO ya que deseo hacer una búsqueda exacta y finalmente terminará el ingreso de los argumentos con un paréntesis. =BUSCARV(E1,A2:B11,2,FALSO).

De esta manera, la función BUSCARV hará la búsqueda del valor de la celda E1 sobre los valores del rango A2:A11 y como resultado nos devolverá la celda de la columna B2:B11 que le corresponda. Observa el resultado de la función recién descrita:

Una ventaja de haber colocado el valor buscado en la celda E1 es que podemos modificarlo para buscar el teléfono de otra persona y la función BUSCARV actualizará el resultado automáticamente.

LA FUNCIÓN BUSCARH

La función BUSCARH en Excel busca un valor dentro de una fila y devuelve el valor que ha sido encontrado o un error #N/A en caso de no haberlo encontrado. Esta función es similar, en cierto sentido, a la función BUSCARV.

Debemos utilizar la función BUSCARH cuando el valor que estamos buscando se encuentra en una fila de alguna tabla de datos. Por el contrario, la función BUSCARV realiza la búsqueda en una columna.

SINTAXIS DE LA FUNCIÓN BUSCARH

La función BUSCARH tiene tres argumentos que son los siguientes:

BUSCARH(valor_buscado, rango, valor_regresado, [ordenado])

Valor_buscado (obligatorio): El valor que estamos buscando.
Matriz_buscar_en (obligatorio): El rango que contiene los valores y que debe ser una fila.
Indicador_filas (obligatorio): El número de fila que contiene el valor que regresará la función.
Ordenado (opcional): El valor debe ser FALSO si queremos una coincidencia exacta o VERDADERO para una coincidencia aproximada.

Si la función BUSCARH no encuentra el valor que está siendo buscado regresará el valor de error #N/A.

EJEMPLO DE LA FUNCIÓN BUSCARH

En la siguiente tabla tengo la información de los artículos que ha vendido cada uno de los vendedores en los últimos meses. Como título de columnas están los nombres de los vendedores y como filas los meses.

Ahora quiero saber los productos vendidos en el mes de Febrero por Paco y para ello puedo utilizar la función BUSCARH de la siguiente manera:

=BUSCARH("Paco", B1:D6, 3,FALSO)

El primer argumento es “Paco” porque es el vendedor que estoy buscando.
El segundo argumento contiene todo el rango de datos sin incluir la columna de meses (columna A) porque no me interesa dicha información.
El tercer argumento es el número de fila que deseo que la función BUSCARH regrese como resultado. Ya que la fila con los nombres es la fila uno, entonces la fila para el mes de febrero es la fila número 3.
Finalmente coloca el valor FALSO en el cuarto argumento para indicar que deseo una coincidencia exacta al buscar a Paco. El resultado de esta fórmula es el siguiente:

Si quisiera busca la información del mismo mes para Luis, entonces la función cambiará de argumentos de la siguiente manera:

=BUSCARH("Luis", B1:D6, 3,FALSO)

Si por el contrario quiero conocer los productos vendidos por Hugo en el mes de Abril, entonces la función sería la siguiente:

=BUSCARH("Hugo", B1:D6, 5,FALSO)

De esta manera, la función BUSCARH nos permite hacer una búsqueda en una fila (búsqueda horizontal) y encontrar fácilmente el valor requerido.

TALLER FUNCIÓN BUSCAR V / H

1. Crear un archivo EXCEL y guardarlo en su dispositivo de almacenamiento con la siguiente nomenclatura:

1001.Nombre.Apellido.buscarvh

2. Realizar la siguiente tabla que trata de datos meteorológicos:

3. Mediante la función BUSCAR V indicar lo siguiente:

En una celda ingresar el Mes en el cual deseo obtener las temperaturas respectivas.
En una celda inferior y mediante la función mostrar la temperatura máxima del mes indicado anteriormente.
En una celda inferior y mediante la función mostrar la temperatura mínima del mes indicado inicialmente.
En una celda inferior y mediante la función mostrar la Precip. L/m2 del mes indicado inicialmente.

4. El resultado debe mostrar un cuadro similar al siguiente que al momento de ingresarle el mes arroje los datos de temperatura y precipitación de forma automática.

5. En una hoja diferente ala anterior realizar el siguiente cuadro:

6. Mediante una función BUSCAR H indicar lo siguiente:

En una celda ingresar el nombre de la persona de quien deseo saber el rendimiento del ano.
En una celda inferior mostrar el rendimiento del Ano 1.
En otra celda inferior mostrar el rendimiento del Ano 5.
Los procesos anteriores deben ser automáticos en el momento de ingresar en la celda el nombre de la persona que deseo saber el rendimiento.

FUNCIONES ESTADÍSTICAS BÁSICAS

Las funciones estadísticas de cualquier programa de hoja de cálculo, nos permiten describir, analizar e interpretar rápidamente un conjunto de datos determinado.

Las siguientes funciones son las más básicas que vamos a observar en este periodo son:

Contar.
Mínimo.
Máximo.
Mediana.
Moda.
Promedio.

FUNCIÓN CONTAR

La función CONTAR regresa la cantidad de celdas dentro de un rango que contienen números. La función MAX en Excel es de gran ayuda siempre que necesitemos obtener el valor máximo de un conjunto de valores que puede ser una lista de números ubicados en uno o varios rangos de nuestra hoja de Excel.

SINTAXIS

CONTAR(valor1, [valor2], …)

valor1 (obligatorio): Primer celda a considerar o el rango donde se desea contar.
valor2 (opcional): Celdas o rangos adicionales a considerar. Hasta 255 elementos.

Supongamos el siguiente ejemplo:

FUNCIÓN MIN

Encuentra el valor mínimo de una lista de números. La función MIN nos devuelve el valor mínimo de una lista de valores omitiendo los valores lógicos y el texto. Como argumentos de la función podemos ingresar directamente los números que deseamos evaluar o podemos indicar una referencia a una celda o el rango que contiene los valores numéricos.

SINTAXIS

MIN(número1, [número2], …)

número1 (obligatorio): El primer número a evaluar.
número2 (opcional): El segundo número a evaluar y hasta 255 números opcionales.

Ejemplo:

FUNCIÓN MAX

Encuentra el valor máximo de una lista de números.

SINTAXIS

MAX(número1, [número2], …)

número1 (obligatorio): El primero de los valores a evaluar. También puede ser un rango.
número2 (opcional): A partir del segundo número los parámetros son opcionales. De igual manera puedes colocar un número o un rango de celdas.

Ejemplo:

FUNCIÓN MEDIANA

Devuelve la mediana de los números dados.

SINTAXIS

MEDIANA(número1, [número2], …)

número1 (obligatorio): El primer número a evaluar.
número2 (opcional): El segundo número a evaluar y hasta 255 números opcionales.

Ejemplo:

MEDIANA(23, 48, 79, 83, 94) = 79

FUNCIÓN PROMEDIO

Obtiene el promedio de los números especificados.

SINTAXIS

PROMEDIO(número1, [número2], …)

número1 (obligatorio): Este parámetro puede ser un número ó tambien puede ser un rango de celdas que contiene el conjunto de números a promediar.
número2 (opcional): A partir del segundo número los parámetros son opcionales. De igual manera puedes colocar un número u otro rango de celdas de donde la función obtendrá más valores a promediar.

FUNCIÓN MODA.UNO

Devuelve el valor que se repite con más frecuencia en una matriz o rango de datos.

SINTAXIS

MODA.UNO(numero1, [numero2], …)

número1 (obligatorio): El primer número a evaluar.
número2 (opcional): El segundo número a evaluar y hasta 255 valores opcionales.

Ejemplo:

MODA.UNO(6,5,5,4,3,5) = 5

EJEMPLO APLICANDO TODAS LAS FUNCIONES ESTADÍSTICAS BÁSICAS

Análisis estadístico de una nota en el área de tecnología:

De acuerdo a los datos obtenidos en la tabla anterior debemos resolver las siguientes preguntas:

¿Cuál es el promedio de las notas de todos los alumnos?

¿Cuál es la nota más repetida entre todos los alumnos?

¿Cuál es la máxima nota obtenido por todos los alumnos?

¿Cuál es la nota mínima obtenida entre todos los alumnos?

¿A cuántos alumnos se les realizó el alumno estadístico?

¿Cuál es el valor medio de los notas tomadas de la tabla anterior?

Desarrollar una gráfica donde muestre el comportamiento de las notas de los alumnos.

2. Colocar los datos en una tabla de forma organizada.

3. Mostrar en una gráfica el comportamiento de las notas de los alumnos.

DESARROLLO

1. El desarrollo de cada una de las preguntas se indica a continuación.

Aplicando el para saber el promedio de la nota obtenida por los estudiantes son:

=PROMEDIO(B2:B11)

Para obtener la nota que más repitieron los estudiantes, lo sacamos con la función Moda:

=MODA.UNO(B2:B11)

Para obtener la nota máxima entre los estudiantes la obtenemos con la siguiente función:

=MAX(B2:B11)

Para obtener la nota mínima entre los estudiantes la obtenemos con la siguiente función:

=MIN(B2:B11)

Para averiguar la cantidad de estudiantes a la cual se les realizó el estudio lo desarrollamos con la siguiente función:

=MIN(B2:B11)

Para saber la cantidad de alumnos a los cuales se les ha realizado el análisis estadísticos, se emplea la siguiente función:

=CONTAR(B2:B11)

Para saber el valor medio de todas las notas analizadas, las obtenemos con la siguiente función:

=MEDIANA(B2:B11)

2. La tabla adjunta es la siguiente:

3. La gráfica que muestra el comportamiento en las notas de los estudiantes es:

TALLER ESTADÍSTICA

1. Crear un archivo EXCEL y guardarlo en su dispositivo de almacenamiento con la siguiente nomenclatura:

1001.Nombre.Apellido.estadistica

2. Desarrollar la siguiente tabla en su software Excel en las mismas celdas establecidas en la gráfica:

3. De acuerdo a la tabla anterior desarrollar las siguientes situaciones:

En la columna E indicar el promedio de toneladas de comida que se enviaron a cada una de las ciudades obtenidas en los tres meses mostrados en la tabla.
En la parte inferior de la tabla, aparte, indicar el promedio obtenido de toneladas de comida por todas las ciudades en cada uno de los meses establecidos.
En la parte inferior de la tabla indicar la moda de toneladas de comida en cada uno de los meses establecidos.
Indicar en la parte inferior de la tabla el valor medio de cada mes de acuerdo a los datos que muestra la tabla.
Mostrar en la tabla la tonelada máxima ingresada en cada mes de todas las ciudades.
Mostrar en la tabla la tonelada mínima ingresada en cada mes de todas las ciudades.
Contar en cada mes solo las ciudades a los cuales les ingresaron toneladas de comida.
Indicar en una tabla todos los resultados pedidos anteriormente, similar a la siguiente tabla:

3. Al lado de las tablas realizar las siguientes figuras:

El comportamiento de toneladas de comida de todas las ciudades en todos los meses.
EL comportamiento de toneladas de comida de todas las ciudades pero solo el del mes de enero.
El comportamiento de ingreso de toneladas de comida de la ciudad de Pitalito en todos los meses..
El comportamiento de ingreso de toneladas de comida de la ciudad de Isnos del mes de Febrero.