Excel
Diseño de modelos con hoja de calculo
La hoja de calculo es una herramienta útil en el diseño de modelos matemáticos que den soluciones a tales problemas, de ahí la importancia de ampliar los conocimientos y desarrollar destrezas que permitan un mejor dominio de esta aplicación. En el ámbito profesional el uso de esta aplicación logra que cosas como inventarios, bases de datos entre otras sean de manera mas rápida. Es por eso, que el conocimiento y uso de este es de suma importancia entre los jóvenes. Aquí se mostrara el uso de funciones existentes en este programa
Operaciones con rangos
Concepto de modelo
Un modelo de datos le permite integrar datos de varias tablas, de forma eficaz creando un origen de datos relacional dentro de un libro de Excel. Un modelo de datos se muestra como una colección de tablas en una lista de campos y, en la mayoría de las veces, nunca sabrá que está allí.
Crear un modelo en la hoja de calculo requiere:
- Comprender el problema para identificar los datos de entrada y salida que se necesitan
- Encontrar la relación entre la entrada y la salida para establecer una función.
- Determinar la ecuación o regla matemática.
- Trasladar la ecuación o la regla a la hoja de calculo.
Pasos para crear un modelo de datos en excel
1.- En Excel, use datos > obtener & datos de transformación > obtener datos para importar datos desde cualquier número de orígenes de datos externos, como un archivo de texto, un libro de Excel, un sitio web, Microsoft Access, SQL Server u otra base de datos relacional que contenga varias tablas relacionadas.
2.- Excel le solicitará que seleccione una tabla. Si desea obtener varias tablas del mismo origen de datos, active la opción Habilitar la selección de varias tablas . Al seleccionar varias tablas, Excel crea automáticamente un modelo de datos para usted.
3.- Seleccione una o más tablas y, a continuación, haga clic en cargar.
Ahora tiene un modelo de datos que contiene todas las tablas que importó y aparecerán en la lista de campos de tabla dinámica.
•
Los modelos se crean de forma implícita al importar dos o más tablas a la vez en Excel.
• Los modelos se crean explícitamente cuando se usa el complemento Power Pivot para importar datos. En el complemento, el modelo se representa con un diseño con pestañas similar a Excel, en el que cada pestaña contiene datos de tabla.
• Un modelo puede contener una tabla única. Para crear un modelo basado en solo una tabla, seleccione la tabla y haga clic en Agregar al modelo de datos en Power Pivot. Puede hacer esto si desea usar las características de Power Pivot, como conjuntos de datos filtrados, columnas calculadas, campos calculados, KPI y jerarquías.
• Las relaciones de la tabla pueden crearse automáticamente si se importan tablas relacionadas que tienen relaciones de clave principal y externa. Excel puede usar normalmente la información importada de la relación como base para las relaciones de la tabla en el modelo de datos.
Crear relaciones entre tablas
El siguiente paso es crear relaciones entre las tablas, de modo que pueda extraer datos de cualquiera de ellas. Cada tabla debe tener una clave principal, o un identificador de campo único, como un identificador de estudiante o un número de clase. La forma más sencilla es arrastrar y colocar esos campos para conectarlos en la vista de Diagrama de Power Pivot.
1.- Vaya a la Administraciónde Power Pivot >.
2.- En la pestaña Inicio , seleccione vista de diagrama.
3.- Se mostrarán todas las tablas importadas y es posible que desee tomarse algún tiempo para cambiar su tamaño en función de cuántos campos tiene cada una.
4.- A continuación, arrastre el campo de clave principal de una tabla a la siguiente.
Rango
Un rango en Excel es un conjunto de celdas contiguas (una junto a la otra) que pueden ser seleccionadas para realizar una operación igual con todas ellas.
Este conjunto de celdas puede tener desde una sola celda, hasta 'x' celdas. Los rangos se identifican por las referencias (una 'dirección' que nace a partir de la intersección de las columnas con las filas).
Por ejemplo:
Ingresa alguna informacion en las celdas A1, A2 y B1, B2.
Ahora seleciona con el ratón en la celda A1 y, sin soltar el botón, arrastra hasta la celda B2.
Observe como una porción de las celdas de su hoja han quedado marcadas. Esto significa que ese grupo de celdas está seleccionado. selecciono un rango de celdas y este rango tiene un nombre que está dado por los nombres de la primera y la última celda de la selección, separadas por el signo dos puntos.
Entonces, el nombre de nuestro rango anterior sería "A1:B2" porque la primera celda que seleccionamos fue la A1 y terminamos en la B2.
Los rangos son utilizados para casi todo lo que hagas dentro de Excel:
- Seleccionas un rango para darle formato a un conjunto de celdas.
- Un Gráfico utiliza los datos contenidos en un rango de celdas.
- Una Tabla se forma a partir de los datos de un rango de celdas.
- Una Tabla Pivote o "Pivot Table" genera reportes a partir de los datos de un rango de celdas.
- Una fórmula generalmente recibe como parámetros los datos contenidos en un rango de celdas.
¿Como se usa un rango?
La forma sencilla de usar un rango, es seleccionándolo con el ratón en cada situación en la que necesitas referirte a un conjunto de celdas.
Otra forma de usar un rango es escribir su "nombre" directamente en lugar de desplazarte por la hoja para buscarlo. Pero esto demanda que tú sepas de memoria dónde comienza y dónde termina tu rango. Para asignar un nombre a una celda sigue los siguientes pasos:
Selecciona la celda o rango a la que asignarás un nombre y haz clic en el cuadro Nombre que se encuentra en el extremo izquierdo de la barra de fórmulas, escribe el nombre que deseas y presiona Entrar.
Otra manera de crear un nombre para un rango es desde la ficha Fórmulas y el botón Asignar nombre. Una vez que hayas seleccionado el rango de celdas oprime este botón y se mostrará el cuadro de diálogo Nombre nuevo:
En la caja de texto Nombre coloca el nombre que asignarás a la celda o rango y oprime el botón Aceptar.
Como ejemplo, se utilizara el nombre que acabamos de crear dentro de una fórmula para que observes cómo Excel interpreta correctamente el nuevo nombre del rango:
Funciones de búsqueda y de referencia
Las funciones de búsqueda y referencia de Excel permiten encontrar valores que coincidan con los criterios establecidos en la búsqueda.
El listado de funciones es el siguiente:
• ÁREAS: Devuelve el número de áreas contenidas en la referencia especificada.
• BUSCAR: Busca un valor dentro de una fila/columna y devuelve el valor que se encuentra en la misma posición en una segunda fila/columna. Los datos deben estar ordenados de forma ascendente, de lo contrario considera utilizar la función BUSCARV ó BUSCARH.
• BUSCARH: Busca un valor dentro de una fila y regresa el valor en la misma posición de una segunda fila. Siempre busca en la primera fila del rango especificado.
• BUSCARV: Busca un valor dentro de una columna y regresa el valor en la misma posición de una segunda columna. Siempre busca en la primera columna del rango especificado.
• COINCIDIR: Busca un valor dentro de una lista y regresa la posición relativa del valor dentro del rango.
• COLUMNA: Obtiene el número de columna de la celda especificada.
• COLUMNAS: Obtiene el número de columnas de un rango.
• DESREF: Devuelve una referencia a una celda o un rango de celdas que ha sido desplazada en base a otra referencia dada.
• DIRECCION: Obtiene la dirección de una celda al especificar los números de fila y columna.
• ELEGIR: Elige un valor de una lista de valores en base a un índice.
• FILA: Obtiene el número de la fila de la celda especificada.
• FILAS: Obtiene el número de filas de un rango.
• HIPERVINCULO: Crea un acceso directo a un documento almacenado en una unidad de disco duro, en un servidor de red en una Intranet o en Internet.
• IMPORTARDATOSDINAMICOS: Regresa los datos visibles de un informe de tabla dinámica.
• INDICE: Devuelve el valor de la celda que se encuentra en la intersección de una fila y una columna dentro de una matriz especificada.
• INDIRECTO: Obtiene el valor de una celda cuya referencia es especificada como texto.
• RDTR: Devuelve los datos generados por una aplicación compatible con COM
• TRANSPONER: Convierte un rango de celdas vertical en un rango horizontal o viceversa.
Análisis de datos
Para poder analizar los datos de algún lugar o por ejemplo nosotros tenemos un negocio y necesitamos algunos datos importantes, podemos utilizar listas o tablas de datos, estas están compuestas de renglones y columnas. El primer renglón siempre es de encabezados. El encabezado de cada columna recibe el nombre de campo, y cada renglón es un registro.
Tablas Dinámicas.
Es un herramienta de la hoja de cálculo que permite crear una vista interactiva con un conjunto de datos. Se dice que la vista es un reporte o informe de la tabla dinámica.
se recomienda usar una tabla dinámica en lo siguiente:
- La cantidad de datos es muy grande y se dificulta analizarlos y resumirlos.
- Se requieren relaciones y agrupaciones entre los datos.
- Se necesita una lista de valores únicos en un campo.
- Cuando deseamos ver tendencias de datos en varios periodos.
- Cuando se requieren reportes de datos que cambian o se han agregado datos.
Las partes del área de diseño de la tabla dinámica son las siguientes:
- Filtro de informe: permite colocar un conjunto opcional de uno o más campos. Al poner campos en esta área se crean filtros para los campos de la tabla.
- Rótulos de fila: aquí se colocan los encabezados que aparecen hacia abajo a la izquierda de la tabla dinámica. Al colocar un campo en esta área se despliegan sus valores únicos.
- Rótulos de columna: aquí se colocan los encabezados de las columnas de la tabla dinámica. Al colocar campos se despliegan elementos orientados en forma de columnas. Esta área es ideal para mostrar tendencia sobre el tiempo.
- Valores o datos: esta región requiere por lo menos de un campo y un tipo de calculo. los campos de datos se colocan en esta área y son aquellos que queremos medir o calcular.
Procedimiento para crear una tabla dinámica
- Abrir el archivo *.xlsx. Este archivo consta de una lista de datos, la que llamaremos tabla, compuesta de renglones o registros y de columnas o campos.
- Familiarizarse con el contenido.
- Estudiar el planteamiento del reporte deseado para definir que colocar en cada una de las áreas.
- Activar cualquier celda de la región de la lista, realizar la secuencia Cinta de opciones, ficha Insertar, grupos Tablas, comando agrupado Tabla dinámica, del menú seleccionar Tabla dinámica.
- En la ventana de dialogo Crear tabla dinámica, marcar Seleccione una tabla o un rango, en Tabla/Rango muestra rango de los datos seleccionados de manera automática. En Elija dónde desea colocar el informe de tabla dinámica, Nueva hoja de cálculo está seleccionado por omisión como el lugar donde se ubicara el informe, Aceptar.
- Aparece activa la nueva Hoja 4 donde se muestra las áreas de la tabla dinámica y una lista de campos disponibles en la derecha. En la Cinta de opciones las fichas especiales relativas a las Herramientas de la tabla dinámica, Opciones y Diseño.
- En el área de lista de campos de tabla dinámica. Hacer clic en el cuadro de verificación tipo-cuenta para agregar a etiquetas de fila. Apuntar y hacer clic con el botón secundario del ratón sobre el campo sucursal, seleccionar agregar etiquetas de columna. Repetir el prendimiento anterior para el campo cantidad para seleccionar agregar a valores.
- Finalmente debe de interpretarse el resultado de la tabla. En caso de requerir modificaciones realizaros desde el panel de tareas Lista de campos de tabla dinámica.
Si la información anterior no quedo completamente clara, a continuación se mostrará un vídeo de el procedimiento ya explicado anteriormente, para que se pueda visualizar
Importar datos externos
Para analizar datos en la hoja electrónica de cálculo, primero se debe proporcionar en un formato que reconozca la aplicación (en este caso MS Excel). Esto es posible realizarlo de dos maneras.
1.- Abrir una fuente de datos en un formato que la aplicación reconozca
2.- Importar datos a una hoja de calculo
Los archivos mas comunes para importar son: archivos de texto, base de datos de MS Access y archivos XML (los cuales son aclarados en la unidad pagina web)
Archivos de texto
Estos pueden ser creados en el editor de Windows, Bloc de notas. Un archivo de texto sirve como fuente de datos para crear una lista en la hoja de calculo.
El formato CSV (Comma Separated Values), valor separado por coma, es uno de los mas comunes para intercambiar datos entre aplicaciones distintas.
El formato para estos archivos es el siguiente:
• Cada registro se escribe en una sola linea.
• Los campos se separan con comas.
• Los espacios antes y después de las comas se ignoran.
• Si un campo incluye comas, debe delimitarse con comillas.
• Si un campo incluye una comilla, esta debe cambiarse a doble comilla y delimitar todo el campo entre comillas.
Archivos de bases de datos Microsoft Access
Tienen la extension *.mdb
Son creados por la aplicacion Microsoft Acess.
Se entiende por base de datos un archivo que contiene una o mas tablas o listas de datos, que guardan cierta relación entre si.
Para efectos demostrativos
Localizar en Internet y con la ayuda del motor de búsqueda Google el archivo NWIND.MDB. Lo transferimos a nuestra computadora en la carpeta de mayor comodidad para usted y se podrá utilizar como base de datos.
