EXCEL INTERMEDIO
Tema 5
Definición.
Excel Intermedio comprende todas las funcionalidades que por defecto nos
otorga Microsoft Excel, no solo el conocerlas, sino también utilizarlas con
fluidez, poder distinguir con claridad como poder resolver las distintas
situaciones en que nos encontremos con todas las herramientas disponibles.
5.1 Funciones y fórmulas.
Una función es una instrucción que encapsula alguna operación a ser
realizada.
Excel contiene un alto número de funciones predefinidas, estas funciones
pueden ser empleadas por medio de la sintaxis o reglas que se encuentran
definidas para su funcionamiento.
Una fórmula en cambio es un conjunto de operaciones, que se encuentran
combinadas para obtener un resultado, los cuales son entregados o asignados
en la celda en la cual se anota la fórmula, las fórmulas pueden hacer uso o no
de las Funciones de Excel.
Ejemplos de Fórmula y función de Excel:
Por ejemplo, si usamos = A2 + A3 * C6
Estamos empleando una fórmula que esta empleada tres celdas y ninguna
función.
En el caso de: = suma (A3:A10)
Estaríamos empleando una fórmula que emplea la función SUMA.
¿Cómo sumar datos dentro de un rango a partir de uno o varios criterios?
Sumar. Si y Sumar. sí. Conjunto.
Muchas veces cuando tenemos una base de datos en Excel y deseamos
conocer el monto total de un criterio dentro de dicha base, resulta trabajoso
sumar dato por dato de manera individual, lo cual puede tomar mucho tiempo e
incluso costar dinero. Por ello, excel presenta dos funciones que resolverán
dicho problema.
Por un lado, la función [Link] se utiliza para hallar la suma de montos,
respecto a un criterio, en una base de datos.
Por ejemplo, en el cuadro de abajo podemos observar una base de datos de la
empresa Milk Tank, la cual ha obtenido información de las cantidades vendidas
en los distintos sectores en los cuales se encuentran sus distribuidores (Norte,
Sur, Oeste, Este). Asimismo, se obtiene datos sobre los montos de las ventas
en dichas áreas, se nos pide hallar el monto total de las ventas de la región
Sur. Por consiguiente, utilizamos nuestra formula de [Link], donde rango
vendría a ser toda el área donde se encuentra nuestro criterio buscado, la parte
de criterio es la característica que debe cumplir el dato para ser considerado
válido para la suma, y finalmente [rango_ suma] seria el rango en donde se
encuentra los montos que deseamos sumar.
Fuente: [Link]
Algunos tips para que puedas aplicar estos nuevos conceptos y no fallar en el
intento son los siguientes:
1. Utilizar F4 para mantener los rangos establecidos y que estos no se muevan
al momento de copiar la formula y/o desplazarla a otro lugar.
2. Utilizar "" tanto al inicio como al final del criterio, ya que sin ellas la formula
emitirá mensajes de error.
3. Identifica los nombres de los criterios en dicho caso veas que existen datos
similares o que están incluidos en otros nombres puedes utilizar el * dentro de
la parte de criterio de la formula, con ello puedes agregar un pequeño filtro a la
base de datos. Por ejemplo: *Norte, solo seleccionara cuyos datos terminen
con la palabra Norte; Norte*, solo aquellos que empiecen con dicha palabra o
*Norte* para aquellos que en cualquier parte incluya la palabra Norte.
Uso de la función BUSCARV
El objetivo de la función BUSCARV, es que nos permite buscar y organizar los
elementos de una base de datos larga; a través de una matriz creada en
específico.
La matriz son varias columnas en el que se ubican cualquier tipo de datos.
Fuente: [Link]
¿Cómo se usa la función BUSCARV?
Los datos deben estar situados verticalmente. Luego, nos ubicamos en la
primera celda que queremos devolver los valores según la matriz; y se escribe
la siguiente función: =BUSCARV(B2,E2:F5,2,FALSO. En la celda "B2", significa
el valor que quiere ser buscado, para que el valor de la matriz sea devuelto.
Por ejemplo en este caso se quiere buscar los tipos de productos clasificados
en A,B y C; dependiendo el tipo, el valor que aparecerá en la columna de
descripción serán: Crítico, Importante o General.
Fuente: [Link]
En la celda "E2:F5", significa que debemos seleccionar la matriz completa,
sin olvidar de fijar con F4, para que estos datos no se muevan, al momento
de jalar cuando salgan en toda la base de datos. Este puede ser uno de los
errores que se puede cometer y que casi todos los datos salgan como
#N/A.
El número "2", significa el indicador de columnas, es decir que el valor que
se va a retornar sea los que están ubicados en la segunda columna vertical
de la matriz. No ubicar la columna correcta en la función, es un error para
que salga de manera correcta los datos buscados.
Finalmente, se pone "FALSO", ya que en el caso de que algún tipo de
producto en este caso sea uno de tipo D, al usar falso; nos colocará el
valor de #N/A, ya que el valor no ha sido encontrado; por otro lado si
usamos la palabra "VERDADERO", nos colocará el valor más cercano de
nuestra matriz.
Por ejemplo, si existe un producto de tipo "D", nos devolverá como si la
descripción del producto fuera tipo "C", ya que es el valor que más se
acerca a "D". Por ello, es otro de los errores que se pueden cometer
cuando usamos esta función, ya que se necesita encontrar con exactitud
los valores buscados.
5.2. Manipulación de datos.
Las tablas de datos en Excel son un tipo de análisis «y si» que, siendo un
aspecto muy potente de Excel es, quizá de los menos utilizados de manera
sistemática. Básicamente es el clásico análisis de sensibilidad de algo con
respecto a algunas variables. Nos brinda la posibilidad de ver cómo cambia el
resultado devuelto por una fórmula cuando varío otra u otras. Si usas nombres
de rango (aunque el rango sea de una sóla celda) en tus fórmulas o/y
referencias a otras celdas, ya puedes hacer uso del análisis y si.
ANÁLISIS «Y SI» MANUAL
Un ejemplo: necesito calcular los intereses anuales de un préstamo que estoy
pidiendo por 5.000 euros. El tipo de interés estará en torno al 5% anual. Podría
hacer simplemente = 5.000 x 5 / 100 y ya tengo el resultado. Pero Tambien
podrida hacer:
Me lo conceden al 6% y por 4.500 euros. Lo único que tendría que hacer es
modificar las cifras en B1 y B3 y ya tengo el resultado de nuevo. Todo bien
clarito.
Bueno, esto, normalmente, seguro que ya lo hacemos. Subamos al siguiente
escalón.
TABLAS DE DATOS EN EXCEL – EJEMPLO 1
Las tablas de datos en Excel son rangos dinámicos en las que vemos cómo
varía un resultado con los cambios de un dato o de dos. Vamos a verlo con
más detalle.
Supongamos que una empresa vende 140.000 euros, con un coste de ventas
de 30% y gastos generales por 74.588 euros. Quiere saber cómo afecta al
beneficio calculado según los datos anteriores, diversos cambios en el coste de
ventas.
Se trata de una tabla de datos con una entrada porque sólo varía el coste de
ventas, el beneficio lo hace como consecuencia de esa variación. Primero he
preparado con las fórmulas necesarias en las celdas de la izquierda. A
continuación veremos cómo preparar la tabla de datos de la derecha.
Ponemos en un rango los diferentes porcentajes de coste de ventas con los
que queremos trabajar. En el ejemplo las celdas F6 a F16. En la celda G5
pondremos =C13. La celda F5 la dejamos vacía. Seleccionamos el rango
completo que va desde la celda F5 a la G16.
Ahora vamos a la pestaña Datos y elegimos Análisis de hipótesis – tablas de
datos en el grupo Herramientas de datos
Nos aparecerá la ventana Tabla de datos …
… y en la opción Celda de entrada (columna) pondremos =C8 que es donde
tenemos el elemento que varía, o sea, Coste de ventas. Pulsamos Aceptar y
Excel rellena automáticamente con una fórmula matricial las celdas del rango
G6 a G16 con el cálculo del beneficio según el coste de ventas que aparece a
la izquierda.
¿Cómo sabe Excel que queremos que calcule cómo varía el beneficio? Porque
se lo hemos dicho en la celda G5.
Como es natural, si el elemento que varía estuviera en una fila, habría que
rellenar la opción Celda de entrada (fila).
No os preocupéis. Hacedlo una vez siguiendo estas instrucciones y veréis que
es más sencillo de hacer que de contar.
TABLAS DE DATOS EN EXCEL – EJEMPLO 2
He preparado este otro ejercicio. Más o menos, continuación del anterior. Aquí
veremos cuales son los requerimientos de Excel cuando tiene que crear una
tabla de datos de dos elementos (hay dos conceptos que varían): en este caso,
seguirá cambiando el coste de ventas e introduciremos el tipo de cambio como
variable a tener en cuenta en el cálculo del beneficio en una moneda distinta de
la de origen, por ejemplo, el euro.
La tabla de datos es un poco diferente:
La celda F4 ya no está vacía, contiene una referencia a la celda C13 (=C13)
donde está la fórmula que calcula el beneficio.
En el rango G4 a L4 he puesto los valores del tipo de cambio que quiero
analizar.
En la ventana Tabla de datos (que se habría desde Datos – Análisis de datos –
Tabla de datos) ahora deberemos también rellenar la opción Celda de
entrada(fila) con la referencia a la celda del tipo de cambio, que es el segundo
elemento que varía
5.3. Seguridad.
La seguridad en Excel es necesaria en diversas ocasiones.
¿Cómo proteger celdas y hojas de Cálculo en Excel?
Algunos pasos para proteger celdas y hojas de cálculo en Excel
Excel dispone de varias herramientas de protección y prevención de accidentes
que involucren proteger celdas, gráficos y hojas de cálculo. Este proceso
dispone de dos pasos sumamente importante que serán explicados a
continuación.
Fuente: [Link]
1. Excel dispone la opción “bloquear celda”, la cual es una herramienta que
sirve para proteger celdas de la hoja de cálculo, esto evitará la manipulación o
algunos cambios de los archivos, que pueden ser: formulas, gráficos o data
importante. Esta opción puede estar en dos modos, tanto en activada como
desactivada.
Sin embargo, esta opción permite que se trabaje con normalidad en todo el
documento, manteniendo ciertas celdas protegidas. Para poder aplicarlo solo
se debe dar la opción de formato y buscar en la columna la opción de bloquear
celda.
2. Excel permite la opción de proteger en su totalidad la hoja de cálculo. Para
ello, se debe activar la opción de proteger hoja. Sin embargo, al activar esta
opción, el programa de Excel permite una serie de posibilidades que permiten
determinar qué datos de la hoja de cálculo pueden cambiarse. Estas opciones
incluyen añadir una contraseña para proteger la hoja de cálculo, editar y mover
datos, borrar celdas, entre otras opciones.
¿Cómo proteger una hoja y dejar que se modifique un rango de celdas?
Se pueden proteger celdas, hojas de cálculo y libros completos, dependiendo
de las necesidades de seguridad de cada uno de nosotros.
En ocasiones necesitamos proteger nuestros archivos en Excel para impedir
que nos modifiquen los datos, agreguen o eliminen filas y columnas y otros.
Proteger libro: Se puede proteger de dos formas. Para impedir que un usuario
no autorizado pueda abrir el libro, y para permitir que los usuarios puedan abrir
el libro pero que no puedan realizar cambios.
El procedimiento es dar clic en el botón de office en la parte superior izquierda
de la pantalla, elegir “Guardar como”, y en la parte inferior izquierda de la
ventana que se abre, dar clic en “Herramientas”. Allí elegir “Opciones
generales” que es donde se le asignan las contraseñas, ya sea para apertura,
para escritura o ambos.
Proteger hoja: Es la más sencilla de todas. Simplemente se da clic con el
botón derecho del ratón sobre la pestaña de la hoja que se quiere proteger, y
allí se despliega un menú en el que aparece la opción “Proteger hoja”.
Así mismo se pueden marcar las opciones que se pueden o no realizar en la
hoja como insertar filas, columnas, aplicar formato, insertar hipervínculos,
ordenar, seleccionar celdas, etc.
Proteger celdas: Todo lo que hay que hacer es seleccionar el rango de celdas
que se quieren proteger y dar clic con el botón derecho del ratón, tras lo cual
emerge un menú, en donde se elige la opción “Formato de celdas”. En la
ventana que aparece, se elige la opción “Proteger” y se selecciona la opción
“Bloqueada” u “Oculta”.
La opción “Oculta”, se utiliza para ocultar las fórmulas que contienen las celdas,
opción muy útil cuando se quiere evitar que los usuarios se enteren como se ha
obtenido algún resultado.
Para que las celdas se puedan proteger, la respectiva hoja de cálculo debe
estar protegida, por tanto, lo primero que se debe hacer es proteger la hoja de
cálculo.
Este tipo de seguridad es apropiada para usuarios que tengan pocos
conocimientos sobre sistemas, ya que para usuarios avanzados les resultará
fácil burlar este tipo de contraseñas.
5.4. Tablas Dinámicas.
Las Tablas Dinámicas en Excel son sencillas de crear. Sirve para resumir los
datos que hay en una hoja de cálculo. Lo mejor de todo es que puedes
cambiarla fácil y rápidamente para ver los datos de una manera diferente,
haciendo de ésta una herramienta muy poderosa.
Crear una tabla dinámica para analizar datos de una hoja de cálculo
Partiendo de una hoja de cálculo que contiene las estadísticas de ventas para
una empresa ficticia, supongamos que queremos responder a la
pregunta: ¿cuánto es el total de ventas por cada vendedor?
Esto puede llevar mucho tiempo porque cada uno de ellos aparece en varias
filas al igual que su venta mensual. Aunque podríamos utilizar la
función Subtotal todavía tendríamos un montón de datos por analizar.
Para crear una tabla dinámica que responda a la pregunta, sigue estos pasos:
1. Selecciona la tabla o celdas, incluyendo los encabezados de columna, que
contienen los datos que vas a utilizar.
2. En la ficha Insertar, haz clic en el comando Tabla dinámica.
3. Aparecerá el cuadro de diálogo Crear tabla dinámica. Asegúrate de que la
configuración sea correcta y haz clic en Aceptar.
4. Una tabla dinámica en blanco aparecerá al lado izquierdo y la lista de
campos, al derecho.
Pasos para agregar campos a la tabla dinámica:
1. En la lista de campos, coloca una marca de verificación al lado de
cada campo que desees agregar.
2. Los campos seleccionados se agregarán a una de las cuatro áreas
por debajo de la Lista de campos. Si un campo no está en la zona
deseada, puedes arrastrarlo a uno diferente.
3. La tabla dinámica ahora muestra la cantidad vendida por cada
vendedor.
Al igual que con los datos de hojas de cálculo normales, puedes ordenar los
datos en una tabla dinámica utilizando el comando Ordenar y filtrar en la
pestaña Inicio. También puedes aplicar cualquier tipo de formato que quieras.
Por ejemplo, es posible que quieras cambiar el formato de número de moneda.
Sin embargo, ten en cuenta que algunos tipos de formato pueden desaparecer
cuando se modifica la tabla dinámica.
Agrupar datos en una Tabla dinámica
La agrupación de datos en una tabla dinámica puede ayudarle a mostrar un
subconjunto de datos que desea analizar. Tenemos las siguientes formas de
agrupar datos:
-Agrupar elementos númericos:
En el siguiente caso, queremos agrupar el número de facturass en grupo de 10.
Para ello, seleccionamos:
Obtenemos el siguiente resultado:
Como observamos, agrupar datos en la tabla dinámica, nos sirve para poder de
maneras más simplificada los datos que tenemos y agruparnos de tal manera
que reduzcan la cantidad de datos a los necesarios.
5.5 Gráficos Dinámicos.
A veces es difícil ver la gran imagen cuando los datos sin procesar no se han
resumido. El primer Instinct puede ser crear una tabla dinámica, pero no
todos pueden ver los números de una tabla y ver rápidamente lo que pasa. Los
gráficos dinámicos son una forma excelente de agregar visualizaciones de
datos a los datos.
Crear un gráfico dinámico:
1. Seleccione una celda en la tabla.
2. Seleccione Insertar > gráfico dinámico .
3. Seleccione Aceptar.
Crear un gráfico a partir de una tabla dinámica
1. Seleccione una celda en la tabla.
2. Seleccione Herramientas de tabla dinámica > analizar > gráfico
dinámico .
3. Seleccione un gráfico.
4. Seleccione Aceptar.
Referencia Web:
-Excel intermedio
[Link]
-Crear una tabla dinámica
[Link]
para-analizar-datos-de-una-hoja-de-c%C3%A1lculo-a9a84538-bfe9-40a9-a8e9-
f99134456576
