EXCEL - HOJA DE CÁLCULO
APRENDIZAJE
Aplico procedimientos y fórmulas en la hoja de cálculo, que permitan la elaboración de documentos sencillos en los campos comerciales financieros y empresariales.
TRABAJANDO CON EXCEL
- Filtros (autofiltro y filtro avanzado)
- Función SI (sintaxis de la función si, aplicación de la función si)
- Función SI anidadas
- Hipervínculos
- Función buscar (buscar V, combinar datos, buscar H)
- Funciones estadísticas (Promedio, Moda, Frecuencia, Mediana)
- Resolver operaciones aritméticas y expresiones lógicas en Excel.
- Gráficos estadísticos
- Macros
- Tablas dinámicas
TRABAJANDO CON EXCEL (nivel intermedio)
CLASE Nº 1
FILTROS
CÓMO CREAR FILTROS EN EXCEL
Para crear un filtro podemos utilizar el comando Filtro que se encuentra en la ficha Datos dentro del grupo Ordenar y filtrar.
Tomado de https://exceltotal.com/filtros-en-excel/
ACTIVIDAD: Descargar el documento y aplicar como ejercicio, todas las orientaciones e instrucciones dadas en el video.
DESCARGAR EJERCICIO:
EJERCICIO Nº 2
CLASE Nº 2
CLASE Nº 3
HIPERVÍNCULOS
Crear un hipervínculo a un nuevo archivo
-
En una hoja de cálculo, haga clic en la celda en la que desea crear un hipervínculo.
También puede seleccionar un objeto, como una imagen o un elemento de un gráfico, que desee usar para representar el hipervínculo.
< >En la ficha Insertar, en el grupo Vínculos, haga clic en Hipervínculo
Puede también haga clic en la celda o el gráfico y, a continuación, haga clic en hipervínculo en el menú contextual, o puede presionar CTRL+k.
. -
En Vincular a, haga clic en Crear nuevo documento.
-
Escriba el nombre del nuevo archivo en el cuadro Nombre del nuevo documento.
Sugerencia: Para indicar una ubicación distinta a la que se muestra en Ruta de acceso completa, escriba la nueva ubicación antes del nombre en el cuadro Nombre del nuevo documento, o bien, haga clic en Cambiar para seleccionar a continuación la ubicación que desee y, después, en Aceptar.
-
En Cuándo modificar, haga clic en Modificar documento nuevo más adelante o en Modificar documento nuevo ahora para especificar cuándo desea abrir el archivo nuevo para modificarlo.
-
En el cuadro Texto, escriba el texto que desea usar para representar el hipervínculo.
-
Para mostrar información útil cuando se coloque el puntero sobre el hipervínculo, haga clic en Info. de pantalla y escriba el texto que desee en el cuadro Información en pantalla. Haga clic en Aceptar.
Para establecer un vínculo a una ubicación del libro actual o de otro libro, puede elegir entre definir un nombre para las celdas de destino o usar una referencia de celda.
-
Para usar un nombre, debe asignar un nombre a las celdas de destino del libro de destino.
Cómo asignar un nombre a una celda o rango de celdas
-
Seleccione la celda, el rango de celdas o selecciones no adyacentes a los que desee asignar un nombre.
-
Haga clic en el cuadro nombre en el extremo izquierdo de la barra de fórmulas
.
Cuadro Nombre -
En el cuadro nombre, escriba el nombre de las celdas y, a continuación, presione ENTRAR.
Nota: Los nombres no pueden contener espacios y deben comenzar con una letra.
-
-
En una hoja de cálculo del libro de origen, haga clic en la celda en la que quiere crear un hipervínculo.
También puede seleccionar un objeto, como una imagen o un elemento de un gráfico, que desee usar para representar el hipervínculo.
< >En la ficha Insertar, en el grupo Vínculos, haga clic en Hipervínculo
Puede también haga clic en la celda o el objeto y, a continuación, haga clic en hipervínculo en el menú contextual, o puede presionar CTRL+k. . -
En Vincular a, siga uno de los procedimientos siguientes:
-
Para establecer un vínculo a una ubicación del libro actual, haga clic en Lugar de este documento.
-
Para establecer un vínculo a una ubicación de otro libro, haga clic en Archivo o página Web existente, busque y seleccione el libro con el que desea crear el vínculo y, a continuación, haga clic en Marcador.
-
En el cuadro o seleccione un lugar de este documento, en Referencia de la celda, haga clic en la hoja de cálculo con la que desea establecer el vínculo y, a continuación, escriba la referencia de la celda en el cuadro Escriba la referencia de celda y haga clic en Aceptar.
-
En la lista que aparece bajo Nombres definidos, haga clic en el nombre que representa las celdas con las que desea establecer el vínculo y haga clic en Aceptar.
-
Siga uno de estos procedimientos:
-
En el cuadro Texto, escriba el texto que desea utilizar para representar el hipervínculo.
-
Para mostrar información útil cuando se coloque el puntero sobre el hipervínculo, haga clic en Info. de pantalla y escriba el texto que desee en el cuadro Información en pantalla. Haga clic en Aceptar.
CLASE Nº 4
GRÁFICOS ESTADÍSTICOS
Para crear gráficos en Excel lo primero que debes de hacer es organizar los datos que quieres mostrar en la gráfica. 
En el ejercicio ejemplo partimos de una tabla sencilla con los datos pluviométricos de dos años consecutivos. Una vez que tenemos los datos del gráfico debes seguir los siguientes pasos:
En el grupo de herramientas Datos, hacemos clic en Seleccionar Datos y en la ventana que aparece debemos rellenar lo siguiente:
Una vez seleccionado el tipo de gráfico se activan las Herramientas de Gráficos y aparece en la hoja de Excel el recuadro donde se creará el gráfico.
En la pestaña Insertar, dentro del grupo de herramientas Gráficos, elegir el tipo de gráfico que quieres utilizar. Si más adelante quieres cambiar el tipo de gráfico puedes hacerlo.
Rango de datos del gráfico; serán los valores numéricos que queremos representar en la gráfica. Los seleccionamos en nuestra tabla y aceptamos.
A continuación vamos a editar las etiquetas del eje horizontal, hacemos clic en Editar y seleccionamos el rango de datos que queremos que se muestre en el eje horizontal, en nuestro caso los meses del año.
Seguimos modificando el nombre de las entradas de leyenda, las series. Hacemos clic en Editar y seleccionamos en nuestra tabla las series, también se puede sobrescribir directamente el nombre de la serie.
Una vez que tenemos todos los datos introducidos correctamente podemos pasar a modificar el diseño de nuestro gráfico, podemos cambiar el tipo de gráfico, colores, estilos de diseño etc según nuestras necesidades.
VIDEO EXPLICATIVO: www.youtube.com/watch?v=HMLFrp_gqJc
CLASE Nº 5
FUNCIONES ESTADISTICAS (Promedio, Moda, Frecuencia, Mediana)
La moda de un conjunto de números es aquel número que se repite más veces dentro del conjunto. No hay que confundirlo con la Media, que es el valor promedio del conjunto de números o la Mediana, que es aquel que se encuentra en el medio.
Existe en Excel una función llamada MODA que permite calcularla fácilmente. Se expresa de la siguiente forma:
MODA(número1;número2; ...)
Para usar esta función simplemente hay que pasarle un listado de números. Automáticamente Excel calculará cuál es el número más repetido de todo el conjunto de cifras
Media, mediana y moda en Excel
Estos tres conceptos suelen confundirse muchas veces. Sin embargo, se trata de funciones diferentes con propósitos diferentes. En el ejemplo podrás ver como en un mismo conjunto de números los resultados de las diferentes estadísticas son diferentes
Si no es la moda lo que necesitas calcular, puedes aprender a calcular la media o a calcular la mediana en Excel.
Tomado de https://es.calcuworld.com/calcular-la-moda-en-excel/
CLASE Nº 5
Resolver operaciones aritméticas y expresiones lógicas en Excel.
EXPRESIONES LÓGICAS EN EXCEL
Las expresiones lógicas son aquellas que tienen solamente dos posibles valores: verdadero o falso. Las expresiones lógicas en Excel son las que nos permiten crear la condición utilizada por la función SI de manera que pueda devolver el valor adecuado.
UN RECORDATORIO DE LA FUNCIÓN SI
En caso de que no recuerdes la manera en cómo trabaja la función SI te mostraré a continuación su sintaxis:
SI(condición, [si_verdadero], [si_falso])
El primer argumento, que es conocido como la condición, es precisamente la expresión lógica que será evaluada para decidir sobre el valor que será devuelto. En caso de que la condición sea verdadera la función SI devolverá el valor del segundo argumento. Si por el contrario, la condición es falsa, se devolverá el valor del tercer argumento.
EXPRESIONES LÓGICAS SENCILLAS
Tengo una lista de personas con su edad y necesito crear una expresión lógica que indique las personas que son mayores de 18 años. Esto lo lograré utilizando el operador lógico mayor que (>). Observa el resultado al colocar la expresión lógica en la columna C:
Como era de esperarse, la expresión lógica es falsa para aquellos que son menores de 18 años. Si quieres conocer más operadores de comparación te recomiendo consultar el artículo Tipos de operadores en Excel.
EXPRESIONES LÓGICAS COMPUESTAS
Es posible tener expresiones lógicas compuestas que probarán más de una sola condición y combinarán los resultados para darnos la respuesta adecuada. La manera en que podemos crear este tipo de expresiones es utilizando la función Y en Excel.
La función Y comprobará que todas las expresiones lógicas que proveamos como argumentos sean verdaderas en cuyo caso devolverá el valor VERDADERO. Con que una sola expresión lógica sea falsa y la función Y devolverá el valor FALSO.
En el siguiente ejemplo he decidido que, de mi lista de personas que mostré anteriormente, deseo marcar aquellas personas que sean mayores de 18 años y además menores de 25. Para comprobar que la edad de cada persona cumpla con ambas condiciones debo de utilizar la función Y.
LA FUNCIÓN SI Y LA FUNCIÓN Y EN ACCIÓN
Para comprobar la utilidad de la función Y haré el mismo ejemplo anterior pero haciendo uso también de la función SI de manera que para una personas mayor de 18 años pero menor de 25 mostraré la leyenda “Permitido” de lo contrario mostraré la leyenda “Fuera de rango”. Observa el resultado:
Si observas esta fórmula con detenimiento podrás observar que podríamos obtener el mismo resultado con una función SI anidada sin embargo la solución sería un poco más compleja. Si quieres obtener el mismo resultado anterior con una función SI anidada deberíamos utilizar la siguiente fórmula:
=SI(B7>18, SI(B7<25, "Permitido", "Fuera de rango"), "Fuera de rango")
Sin embargo en nuestro ejemplo podemos obtener el mismo resultado utilizando dos expresiones lógicas dentro dela función Y de la siguiente manera:
=SI(Y(B7>18, B7<25), "Permitido", "Fuera de rango")
Así que, la próxima vez que te sientas tentado a utilizar una función SI anidada considera la posibilidad de simplificar la fórmula utilizando expresiones lógicas con la función Y.
EXPRESIONES LÓGICAS CON LA FUNCIÓN O
Otra función que también es de utilidad en la construcción de expresiones lógicas en Excel es la función O la cual nos devolverá el valor VERDADERO en caso de que al menos una de las expresiones evaluadas sea verdadera. El único caso donde la función O devolverá el valor FALSO será cuando todas las expresiones lógicas sean falsas.
Para conocer el valor de la función O modificaré el ejemplo anterior para mostrar la leyenda “Permitido” cuando una persona tenga 24 o 25 años y para los demás casos se mostrará el mensaje “Fuera de rango”. El resultado es el siguiente:
Como puedes ver, solamente las celdas que tienen los valores 24 o 25 son aquellas que muestran la leyenda “Permitido”. De esta manera comprobamos que la función O nos permite evaluar expresiones lógicas en Excel y saber si se cumple al menos una de las condiciones en cuyo caso devolverá el valor VERDADERO.
De la misma manera que en el caso anterior, pude haber utilizado una función SI anidada para obtener el mismo resultado de la siguiente manera:
=SI(B7=24, "Permitido", SI(B7=25, "Permitido", "Fuera de Rango"))
Sin embargo, al utilizar la función O podemos escribir la fórmula de una manera diferente:
=SI(O(B7=24, B7=25), "Permitido", "Fuera de rango")
Vale la pena que dediques un poco de tiempo a aprender a construir adecuadamente expresiones lógicas en Excel que te ayudarán a sacar el máximo provecho de la función SI y en más de una ocasión harán mucho más simple la solución de problemas en Excel.
Tomado de https://exceltotal.com/expresiones-logicas-en-excel/
EVALUACIÓN INDIVIDUAL:
FUNCIÓN BUSCAR (buscar V, combinar datos, buscar H)
La función BUSCAR en Excel nos permite buscar un valor dentro de un rango de celdas y como resultado nos devolverá el valor correspondiente del rango de resultados que especifiquemos. La función BUSCAR se puede utilizar en forma vectorial o en forma matricial.
Valor_buscado (obligatorio): Es el valor que deseamos encontrar.
Vector_de_compraración (obligatorio): Un rango de celdas que está formado por una sola
columna o una sola fila en donde se realizará la búsqueda.
Vector_resultado (opcional): El rango de celdas que contiene la columna o fila de resultados que deseamos obtener.
El Vector_de_comparación debe estar siempre en orden ascendente, de lo contrario la función devolverá resultados incorrectos.
En caso de especificar el Vector_resultado deberá ser del mismo tamaño que Vector_de_comparación.
En la celda F1 colocaré el nombre del alumno y en la celda F2 la función BUSCAR la cual me ayudará a obtener su calificación. Desarrollemos la fórmula paso a paso:
- Introducir la función BUSCAR y especificar el primer argumento que es el valor buscado.
=BUSCAR(F1, - Como segundo argumento debo especificar el rango donde se realizará la búsqueda:
=BUSCAR(F1, A2:A6, - El último argumento será el rango que tiene los resultados que deseo obtener y que en este ejemplo son las calificaciones en C2:C6:
=BUSCAR(F1, A2:A6, C2:C6)
Observa cómo la función BUSCAR regresa la calificación que corresponde al nombre buscado.
LA FUNCIÓN BUSCARV EN EXCEL
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.
VIDEO EXPLICATIVO: youtu.be/TONErtDwpPo
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.
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.
Como puedes ver, la función BUSCARV tiene tres argumentos obligatorios y uno opcional. Sin embargo, te recomiendo siempre utilizar los cuatro argumentos indicando en el último de ellos el valor FALSO para asegurar una búsqueda exacta.
EJEMPLO DE LA FUNCIÓN BUSCARV
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:
LA FUNCIÓN BUSCARH EN EXCEL
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.
CUANDO UTILIZAR LA FUNCIÓN BUSCARH
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:
- 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:
Tomado de https://exceltotal.com/la-funcion-buscarh-en-excel/
CLASE Nº 6
CLASE Nº 8