Resumen Tecnologías De La Información

Unidad 3 Diseño de modelos con hoja de cálculo

Introducción: Resolver problemas de diversas especialidades y la toma de decisiones son situaciones que enfrentan estudiantes y profesionistas. La hoja electrónica de cálculo es una herramienta útil en el diseño de modelos matemáticos que den solución a tales problemas, de ahí la importancia de ampliar los conocimientos y desarrollar destrezas que permitan un mejor dominio de esta aplicación.

3.1 Operaciones con rangos

Concepto de modelo

En términos sencillos, entendemos por modelo la representación adecuada de un fenómeno o situación de la vida real. El modelo no necesariamente tiene que ser de naturaleza matemática, pero en nuestro caso puede ser lo más común, ya que hablamos de usar una hoja de cálculo.

En los modelos matemáticos, las relaciones significativas suelen representarse por medio de funciones. La función es, en esencia, una entidad de entrada/salida. Se proporciona una entrada a una regla matemática que la transforma en una salida específica. La ecuación matemática es la regla que permite, dado un valor de entrada, calcular el valor de salida.

Por ejemplo:

- La velocidad de un objeto está en función de la distancia recorrida y del tiempo empleado. La ecuación puede ser: Velocidad = distancia / tiempo

- El total de las ventas de un producto está en función del precio de venta y del número de unidades vendidas. La ecuación puede ser: Ventas = precio * unidades

- El salario de un empleado depende del número de días trabajados y del pago por día. La ecuación puede ser: Salario = días * pago

Crear un modelo en la hoja de cálculo requiere:

1. Comprender el problema para identificar los datos de entrada y qué salida se requiere.

2. Encontrar la relación entre la entrada y la salida para establecer una función.

3. Determinar una ecuación o regla matemática.

4. Trasladar la ecuación l regla a la hoja de cálculo.

Rango

La manipulación de una celda es relativamente simple y sin problemas, pero en ocasiones es necesario utilizar una región de la hoja de cálculo. En tal caso es preciso conocer y aplicar el concepto de rango para facilitar cierto tipo de operaciones.

Rango es una región rectangular de celdas contiguas en una hoja de cálculo. En su forma más simple un rango puede contener una sola celda. Para hacer referencia a un rango es necesario especificar los dos puntos anclas (direcciones de las celdas esquina superior izquierda y esquina inferior derecha del rango). Por ejemplo el rango A1:B3, comprende a las celdas A1, A2, A3, B1, B2, B3. La expresión A1:A1 es válida como expresión de un rango y equivale a la celda A1.

Asignar nombre a un rango

Es esta operación la de mayor interés para la generación de modelos con la hoja de cálculo, debido a que su uso facilita el entender o mantener las expresiones o las fórmulas.

A continuación se presentan los métodos generales para lograr este propósito, partiendo de que el rango se encuentra previamente seleccionado.

Método 1 Realiza la secuencia: Cinta de opciones, ficha Fórmulas, grupo Nombres definidos, opción Asignar nombre a rango, en la ventana de diálogo Nombre nuevo en Nombre: Escribir el nombre del rango y Aceptar.

Método 2 Hacer clic en el Cuadro de nombres (localizado a la izquierda de la Barra de fórmulas) escribir el nombre de rango y [Enter].

Los nombres de rango deben cumplir con las siguientes reglas:

1. No pueden ser iguales a las referencias de celda.

2. El primer caracter debe ser una letra, un caracter de subrayado (_) o la diagonal inversa (\). El resto pueden ser letras, números, puntos y caracteres de subrayado.

3. No se permiten espacios para separar palabras, en su lugar usar el carácter de subrayado o el punto.

4. No se diferencia entre caracteres en mayúsculas y minúsculas.

5. El máximo de caracteres es 255.

Eliminar el nombre de un rango

Para eliminar el nombre de un rango, se ejecuta la secuencia Cinta de opciones, ficha Fórmulas, grupo Nombres definidos, opción Administrador de nombres, en la ventana de diálogo Administrador de nombres, seleccionar en la lista el nombre del rango, hacer clic en el botón Eliminar y después en Cerrar.

Nombres definidos de rangos

La aplicación de la hoja de cálculo en su versión más actualizada permite asignar nombres definidos de rangos automáticos tomando el nombre de la etiqueta o encabezado de una columna o de un renglón. A esta facilidad se le denomina asignación de nombres definidos de rango en lenguaje natural. El procedimiento para crear un nombre definido consiste en: seleccionar el rango incluyendo el encabezado o rótulo respectivo. Realizar la secuencia: Cinta de opciones, ficha Fórmulas, grupo Nombres definidos, opción Crear desde la selección, en la ventana de diálogo Crear nombres a partir de la selección marcar la opción que corresponda a la posición del encabezado del rango y Aceptar.

3.2 Funciones de búsqueda y de referencia

Las funciones de búsqueda permiten, como su nombre indica, la búsqueda de valores dentro de un rango de la hoja de cálculo.

Funciones BUSCARV y BUSCARH

La búsqueda puede ser realizada vertical u horizontalmente mediante las funciones BUSCARV y BUSCARH.

Sintaxis

BUSCARV (valor_buscado; matriz_buscar_en; indicador_columnas; [ordenado])

BUSCARH (valor_buscado; matriz_buscar_en; indicador_filas; [ordenado])

En donde:

Parámetros Descripción

Valor_buscado Valor o expresión que se desea encontrar en la primera columna (BUSCARV) o en la primera fila (BUSCARH).

Matriz_buscar_en El rango en donde se desea buscar.

Indicador_columnas, indicador_filas Es el número de columnas o de filas donde la función va a regresar un valor.

Ordenado Parámetro opcional, si es VERDADERO encuentra la coincidencia más cercana en la columna o fila según la función. Si es FALSO, la coincidencia debe ser exacta.

- En estas funciones el parámetro opcional “ordenado” es muy importante.

- Si se requiere una búsqueda exacta, debemos usar FALSO.

- Si se requiere localización aproximada, el parámetro debe de ser VERDADERO o bien omitirse y ordenar los datos. En este último caso se regresa el valor más grande que está por abajo del valor buscado.

- En los primeros ejercicios emplearemos una búsqueda exacta por lo que el parámetro “ordenado” debe de ser FALSO.

3.3 Análisis de datos

Lista o tabla de datos

Una lista o tabla está compuesta de renglones y columnas. El primer renglón siempre es un renglón de encabezado. El encabezado de cada columna recibe el nombre de campo, y cada renglón es un registro.

El registro contiene todos los datos relacionados con un elemento de la tabla. Por ejemplo, si es una lista de alumnos, el registro contiene todos los datos de un alumno. Cada registro está organizado en campos, un campo tiene un nombre, y les corresponde un tipo de dato (fecha, moneda, número, etcétera).

Cuando se crea una lista es necesario asegurarse de lo siguiente:

1. Todos los registros comparten los mismos nombres de campos.

2. Los valores de cada campo son consistentes (por ejemplo, números con igual cantidad de decimales).

3. Los valores de cada campo son del mismo tipo de dato.

4. Una lista por cada hoja de cálculo (el ordenamiento y filtrado es más eficiente).

5. Los nombres de los campos deben de ser descriptivos.

6. Los campos deben de separarse en partes básicas (en el caso de una dirección, en calle, colonia, código postal, etcétera).

7. No debe de haber renglones en blanco.

Tablas dinámicas

Concepto: Una tabla dinámica (pivot table) es una herramienta de la hoja de cálculo que permite crear una vista interactiva de un conjunto de datos. Se dice que la vista es un reporte o informe de la tabla dinámica.

Este tipo de reporte es una forma rápida y fácil de:

- Resumir grandes cantidades de datos en información con significado.

- Categorizar los datos en grupos.

- Realizar una amplia variedad de cálculos en una fracción de tiempo de lo que tomaría hacerlo a mano (o de otra manera).

La verdadera potencia de las tablas dinámicas es que, de una buena manera interactiva, con sólo hacer unos cuantos clics sobre los campos, se realizan los cálculos para obtener un reporte.

Cuando se recomienda hacer una tabla dinámica

Ésta técnica puede servir en cualquiera de las siguientes situaciones:

1. La cantidad de datos es muy grande y se dificulta analizarlos y resumirlos.

2. Se requiere de relaciones y agrupamientos entre los datos.

3. Se necesita una lista de valores únicos de un campo.

4. Cuando deseamos ver tendencias de datos en varios periodos.

5. Cuando se requieren reportes de datos que cambian o se han agregado datos.

Crear tabla dinámica

Para crear una tabla dinámica se parte de un conjunto de datos, denominado lista, tabla o base de datos. A cada renglón o fila se le denomina registro y a cada columna se le llama campo. Las columnas o campos de una lista pueden ser de dos tipos.

- Datos. Contienen valores numéricos que pueden sumarse, contarse, promediarse, etcétera.

- Categorías. Describen a los datos, como fechas, nombres y así por el estilo.

Una lista puede tener cualquier cantidad de campos del tipo de datos y cualquier cantidad de campos del tipo categoría. Normalmente, en los campos de datos se hacen operaciones de suma, promedio, cuenta, etcétera.

Formato de una tabla dinámica

Las partes de una tabla dinámica son esencialmente dos:

- Área de diseño (a la izquierda).

- Panel de tareas. Lista de campos de tabla dinámica (a la derecha).

- Filtro de informe. Ubicada en la parte superior de la tabla dinámica. Esta área 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. En esta área 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 en esta área se despliegan elementos orientados en forma de columnas. Esta área es ideal para mostrar tendencias sobre el tiempo, por ejemplo meses, periodos, etcétera.

- Valores o datos. Es el área que se calcula, se ubica a la derecha. Esta región requiere por lo menos un campo y un tipo de cálculo (suma, promedio, cuenta, etc.). Los campos de datos se colocan en esta área y son aquellos que queremos medir o calcular.

3.4 Importar datos externos

Para analizar datos en la hoja electrónica de cálculo, primero se deben proporcionar en un formato que reconozca la aplicación (en este caso MS Excel). Esto es posible realizarlo de dos maneras:

- Abrir una fuente de datos en un formato que la aplicación reconozca.

- Importar datos a una hoja de cálculo.

Los tipos de archivos más comunes para importarlos a Excel, son los siguientes:

- Archivos de texto.

- Base de datos de MS Access.

- Archivos XML.

Archivos de texto

Los archivos de texto pueden ser creados en el editor de Windows, es decir, el Bloc de notas. Un archivo de texto puede servir como una fuente de datos para crear una lista en la hoja de cálculo.

El formato CSV (Comma Separated Values), valor separado por coma o simplemente archivo de texto delimitado por coma, es uno de los más comunes para intercambiar datos entre aplicaciones distintas.

El formato para estos archivos es el siguiente:

- Cada registro se escribe en una sola línea de texto.

- 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, ésta debe cambiarse a doble comilla y delimitar todo el campo entre comillas.

Archivos de bases de datos Microsoft Access

Los archivos de este tipo tienen la extensión *.mdb y son creados por la aplicación Microsoft Access. Se entiende por base de datos un archivo que contiene una o más tablas o listas de datos, que guardan cierta relación entre sí. Nuestro objetivo no es crear estos archivos, sino importar datos desde un archivo existente. Una tabla está compuesta por renglones y columnas. Cada renglón es un registro y cada columna es un campo del registro.

Archivos XML

XML significa lenguaje de marcación extensible (eXtensible Markup language). Éste se ha vuelto un estándar para el intercambio de información entre diferentes aplicaciones. Es un archivo de texto plano que incluye etiquetas que permiten establecer una estructura de datos fácilmente entendible.

...