MATEMÁTICAScon

EXCEL

Matemáticas con EXCEL

Capítulo 1 HERRAMIENTAS BÁSICAS DE EXCEL

Ejemplo 1.1: ARITMÉTICA.xls [agua]Con el pretexto de resolver un ejercicio sencillísimo de aritmética elemental, presentare-mos en un mismo ejemplo muchas de las herramientas básicas de EXCEL (y, también, otras más avanzadas) cuyo empleo cualquier usuario debe conocer para sacar el máxi-mo partido a la HC.

Herramientas EXCEL Introducir texto / valor / numérico / fórmula. Distinguir entre copiar una celda, moverla y rellenar. Conocer el significado de referencia. Utilizar distintos formatos: moneda, porcentaje, etc. Utilizar el botón Autosuma. Utilizar la herramienta Buscar objetivo. Proteger celdas aisladas. Rellenar en Series. Utilizar el asistente para gráficos. Modificar el aspecto de los gráficos: fijar la escala de los ejes, etc. Agregar Series de datos a un gráfico. Crear Hipervínculos. Insertar Comentarios. Insertar Archivos de sonido.

Conceptos matemáticos Significado de variable y de parámetro. Cálculo de porcentajes. Cálculo de aumentos porcentuales. Elaboración de tabla de valores para una función. Representación gráfica de un problema aritmético. Uso correcto y necesario de los paréntesis. Prioridad de las operaciones. Búsqueda de soluciones por tanteo. Significado real de la pendiente de una recta. Resolución de problemas directos (aritméticos) e inversos (alge-

braicos).

a) Factura de consumo de agua.1. Crear un Libro Nuevo.

Archivo / Nuevo... / Aceptar2. Guardar el Libro.

Archivo / Guardar / “ARITMÉTICA”3. Cambiar el nombre de la Hoja1 por AGUA

BS en Hoja1 / Cambiar nombre / “AGUA”4. Introducir textos, valores numéricos y fórmulas según la tabla.

1

Matemáticas con EXCEL

[nota1] En las celdas con datos numéricos no debe escribirse unidades ni ningún otro ti-po de texto, sólo el número pues, de otro modo, al calcular, EXCEL lo interpreta como texto y devuelve un mensaje de error: # ¡VALOR![nota2] EXCEL alinea, por defecto, los valores numéricos a la derecha de la celda y los de texto a la izquierda. Eso puede cambiarse.[nota3] En la Barra de Fórmulas puede introducirse el nombre de una celda directamente con el teclado, o haciendo un clic en esa celda con el ratón.[nota4] Para que EXCEL realice un cálculo es necesario escribir al principio, en la Barra de Fórmulas, el símbolo de igual “=”; en otro caso, lo interpreta como texto.5. Ajustar el ancho de las columnas arrastrando con el ratón cuando aparezca la doble flecha al pasar el puntero por la cabecera de la columna. [nota 5] No se debe ajustar exactamente el ancho de columnas pues si introducimos un número más largo, no cabrá, y EXCEL nos devolverá el mensaje ## o notación científica.[nota6] El botón Deshacer resulta muy útil para suprimir la última acción realizada. Asimismo, Bloq Mayús + F4 repite la última acción realizada.6. Cambiar el formato de la celda C6 a moneda euro con un decimal.

Clic en C6. Formato / Celdas... / Número / Moneda: € /Posiciones decimales: 1.7. Cambiar el color de relleno de las celdas C3 y C4.

Seleccionar el rango C3:C4. Color de relleno: Amarillo claro.[Utilizaremos este color para advertir al usuario de que las celdas así remarcadas

son las modificables. Más adelante veremos cómo se bloquean las demás]8. Cambiar el color de la fuente de la celda C6.

Seleccionar la celda C6. Color de fuente: Azul oscuro.9. Cambiar los datos C3 y C4 y observar cómo EXCEL recalcula las celdas C5 y C6.

b) Factura de consumo de agua con IVA.1. Insertar una fila nueva.

BS en el botón de la fila 2. Insertar.[Se habría conseguido el mismo efecto seleccionando el rango C3:D6 y moviéndo-

lo una fila hacia abajo][Observar Cómo EXCEL ha cambiado las referencias en las celdas C6 y C7]

2. Rellenar las celdas según la tabla.

2

Matemáticas con EXCEL

Utilizar la herramienta Autosuma para calcular C9. Tener cuidado con el rango: cambiarlo a C7:C8. [Análogo resultado se obtiene si se escribe en C9: =C7+C8.3. Cambiar el formato de C8 y C9 a dos decimales y el de C9 a Azul oscuro y Negrita.4. Ajustar el ancho de la columna C si aparece el mensaje ###. Puede hacerse, como antes se ha explicado, arrastrando el ratón por el borde de la columna en su cabecera o bien seleccionando la columna C, Formato / Columna / Autoajustar a la selección.c) Utilizar el formato porcentaje.1. Cambiar el formato del número en la celda F2.

Seleccionar F2. Formato / Celdas / Número / Porcentaje.Observar cómo del 7 pasamos a 700%: EXCEL multiplica automáticamente por

100 y añade el signo del porcentaje.2. Debemos por tanto redefinir F2: 0,07, borrar el símbolo de porcentaje en G2 y redefinir C8= C7*F2.3. Cambiar el relleno de F1 y F2 a Amarillo claro.4. Cambiar los valores de F1 y f2 y observar cómo EXCEL recalcula las celdas C7, C8 y C9.d) De la Aritmética al Álgebra: la herramienta Buscar objetivo.

En los apartados anteriores hemos visto cómo EXCEL, a partir de unas variables y unos parámetros, calcula resultados atendiendo a fórmulas que el programador escribe en la Barra de fórmulas. Se trata de un procedimiento aritmético, directo.

En muchas ocasiones, el problema que se intenta resolver no es directo sino inver-so. Cuál debe ser el ángulo de tiro para que el proyectil salve una altura determinada, qué coeficiente de evaporación puede permitirse para que el caudal final de un conducto de agua sea el prefijado, cómo de abierta debe ser una parábola para que el paraboloide que genere por rotación tenga tal volumen, etc.

Supongamos modelizado el problema. Y transportado a la HC EXCEL. Resolver la cuestión inversa pasa por:

i) probar por tanteo soluciones válidas (lo que, mediante la rapidez en los cálculos de EXCEL, no resulta excesivamente engorroso, incluso favorece la comprensión del mis-mo).

ii) resolver de forma algebraica la ecuación resultante, lo que, independientemen-te del interés procedimental que ello puede implicar, nos conduce en demasiadas ocasio-nes a senderos sin retorno.

Sin menoscabo de las dos opciones apuntadas, consideraremos una herramienta de EXCEL, de gran utilidad bien para resolver el problema inverso de forma directa, bien para comparar soluciones obtenidas por otros procedimientos. Es la llamada Buscar obje-tivo..., cuyo funcionamiento se expone a continuación.

1. Determinar cuál es el coste del metro cúbico de agua, sabiendo la cantidad con-sumida, el porcentaje de IVA aplicado y el dinero que se paga al final.

Seleccionar la celda C9.Herramientas / Buscar objetivo... / Definir la celda: C9, con el valor: 18, para cam-

biar la celda: F1, Aceptar.EXCEL calcula el precio del metro cúbico de agua se-

gún la condición final y lo escribe en la celda correspon-diente.

3

Matemáticas con EXCEL2. Puede repetirse la operación para estimar qué IVA se ha aplicado o cuál ha sido

la lectura final. e) Representación gráfica consumo / gasto.1. Elaborar la tabla de valores.

Escribir en A31, B31 el texto de la Tabla .Escribir en A32: 0.Rellenar desde A32 hasta A132, buscando en el

vértice inferior derecho de la celda A32 el cambio de puntero al +.

Sin deseleccionar el bloque A32:A132: Edición / Rellenar / Series / Incremento: 1 / Aceptar.

Escribir en B32 la fórmula: “=A32*F1*(1+F2)”Doble clic en vértice inferior derecho de B12 ( o

rellenar arrastrando con el ratón hasta B132, lo que es más engorroso).

[Observar que EXCEL arrastra la referencia A12, lo que es deseable, pero también las F1, F2, lo que no interesa, pues son dos parámetros fijos, aun-que arbitrarios].2. Bloquear las celdas F1 y F2.

Escribir en B12 las letras F escoltadas por el signo $, como aparece en Tabla.Doble clic en vértice inferior derecho de B12.[Observar ahora cómo EXCEL arrastra la referencia A12, pero no las F1, F2].

3. Representar el gráfico.Seleccionar el bloque A31:B132.Botón Asistente para gráficos Seleccionar en Tipos estándar, Tipo de gráfico: XY(Dispersión) y en Subtipo de grá-

fico: Dispersión con líneas suavizadas y sin marcadores de datos. Siguiente.[Es interesante probar otras combinaciones y ver los resultados pulsando los boto-

nes Siguiente y Atrás]En la siguiente ventana observar el Rango de datos: =AGUA!$A$31:$B$132. Si-

guiente.4. Experimentar con las distintas opciones del gráfico.

Títulos: Eje de valores (X): consumo (m3)Eje de valores (Y): gasto (euros)Líneas de división: Desactivar todas.Leyenda: Desactivar Mostrar leyenda.Rótulo de datos: Ninguno.Siguiente. Guardar como objeto en : AGUA. Finalizar.

5. Pasar el puntero del ratón por la recta y observar cómo el gráfico nos da las coordena-das de cada punto. Los gráficos de EXCEL son “gráficos activos”.[nota7] Si hubiésemos construido la tabla sólo con tres valores ( o con 20) o incluso con sólo dos habríamos obtenido la misma representación (por tratarse de un recta), pero el gráfico sólo nos devolvería las coordenadas de los puntos de la tabla, de ninguno más.f) Mejora de la representación del gráfico.

Estirar el gráfico desde un vértice hasta llenar la pantalla.BS en Eje de valores (X). Formato de ejes. Escala: Máximo: 100. Unidad menor:5.

Fuente: ComicSans. Tamaño: 9. Aceptar. [nota8] Por defecto, EXCEL escala automáticamente los gráficos. Esto resulta a veces conveniente pero, en otras ocasiones, no.

BS en Eje de valores (Y). Fuente: ComicSans. Tamaño: 9. Aceptar. [Si pasamos el botón por el punto (100, 32,1) podemos interpretar el significado

de la pendiente. Cambiando el valor del parámetro de F1, el del coste del metro cúbico de agua, cambiará la pendiente, pero, como no hemos desactivado la opción de autoes-calar automáticamente en el eje de ordenadas, el efecto será numérico, pero no visual]

BS en Área de trazado. Color: Turquesa claro.

4

Matemáticas con EXCELAmpliar con el ratón el Área de trazado.Mover las leyendas de los ejes. Con el BS cambiar el color y la fuente.Con el BS hacer más gruesa la línea de la recta.Cambiar el dato de F1: 0,2 y observar cómo el eje de ordenadas se autoescala.

Cambiar la escala del eje de ordenadas a manual (máximo: 60)Para ocultar al usuario los valores de la tabla, seleccionar el bloque y, en Color de

fuente, elegir el Blanco.Para obtener una presentación sin los bordes de las celdas, Herramientas / Opcio-

nes / Ver / Desactivar Líneas de división / Aceptar.g) Proteger (bloquear) celdas aisladas.

La propiedad más interesante de una HC es sin duda su carácter manipulativo. El usuario, barajando las condiciones iniciales, induce, especula, investiga, deduce. Pero só-lo son unas pocas celdas a las que el que manipula la HC debe tener acceso. La mayoría, aún estando a la vista, no deben ser modificadas para no alterar la eficacia del progra-ma.

Estableciendo que el Libro sea de Sólo lectura, salvaguardamos el programa de eventuales cambios que el usuario haya introducido impensadamente.Más elegante resulta proteger directamente las celdas que de-seamos permanezcan inalterables.1. Seleccionar las celdas que deseamos permanezcan modifica-bles o no protegidas.

Seleccionar C4:C5.Formato / Celdas / Proteger / Desactivar Bloqueada /

Aceptar.Herramientas / Proteger / Proteger HC (puede escribirse

opcionalmente una contraseña) / Aceptar.[nota9] Si interesa realizar algún cambio en la HC, ésta puede desprotegerse di-

rectamente.

OPCIONES AVANZADAS 1.Agregar un punto móvil al gráfico.1. Enriquecer el gráfico con una serie nueva: con un punto que representa el consumo concreto de un cliente.

BS en Área de trazado / Datos de origen / Serie / Agregar. Valores de X : copiar C6. Valores de Y: copiar C9. Aceptar.

Hemos añadido un punto, pero no se ve. Para editarlo:BS en Área de trazado / Opciones de gráfico / Leyenda / Mostrar leyenda.Seleccionar la línea de la Serie 2: Serie 2 Clave de leyenda / Doble clic / Tramas /

Cambiar el Color el Tamaño y el Estilo del Marcador / Aceptar.Borrar el Rótulo de las series y ampliar el Área de trazado.Mover el punto rojo con el ratón (el cursor debe cambiar a cruz en flecha y mostrar

las coordenadas del punto) y comprobar que funciona análogo a la función Buscar objeti-vo.OPCIONES AVANZADAS 2.Insertar hipervínculos.

1. Insertar un hipervínculo desde una imagen a una página web.Insertar / Imagen / Imagen prediseñada.Seleccionar la Imagen. Insertar / Hipervínculo / En Escriba el nombre del archivo o

de la página web escribir (o seleccionar, si se encuentra a la vista): http://www.educara-gon.org/

Aceptar.OPCIONES AVANZADAS 3.Insertar comentarios.

Con el fin de proporcionar información al usuario pero sin sobrecargar la HC, se puede agregar en las cel-das que deseemos un comentario que aparecerá como

5

Matemáticas con EXCELtexto emergente al pasar el puntero del ratón por la celda en cuestión. Las celdas con comentario se distinguen por una pestañita roja en el vértice superior derecho de las mismas.

1. Vincular en la celda F2 un comentario.Seleccionar la celda F2. Insertar / Comentario. Escribir el texto:”El IVA puede cambiar en función de la renta del cliente”. [nota10] Para modificar o eliminar un comentario, abrir el menú contextual desde la cel-da en cuestión.OPCIONES AVANZADAS 4.Insertar archivos de sonido.

Otra forma interesante de mejorar la HC sin saturar la pantalla es a través de ar-chivos de sonido, mediante los cuales podemos transmitir sugerencias, proponer ejerci-cios, etc.

1. Insertar en el margen del gráfico dos archivos de sonido.Grabar los archivos wav con la Grabadora de sonidos de Windows.Insertar / Objeto / Archivo de sonido / Crear de un archivo / Examinar (seleccionar

el archivo de sonido) / Aceptar.

Ejemplo 1.2: NOTAS.xlsCon este ejemplo, además de presentar una aplicación útil para el profesor en su labor de evaluación, trabajamos el cálculo de medias ponderadas y, aunque la Estadística se abordará en un capítulo posterior, introducimos un par de funciones que nos serán útiles en otros ámbitos: la media aritmética y el conteo del número de celdas que verifican una determinada condición (CONTARSI). Se explica asimismo otra función fundamental, el condicional (SI), tanto de forma simple, como anidada. Una presentación de las funciones más habituales de EXCEL puede verse en FUN_EXCEL.xls.Herramientas EXCEL

Cambiar la alineación de un texto. Abrir un Libro. Copiar un bloque de un Libro a otro. Insertar una función mediante el botón Pegar función. Escribir una función directamente en la Barra de fórmulas. Función PROMEDIO. Función condicional SI. Uso simple y avanzado. Funciones lógicas O, Y. Función CONTAR.SI. Crear pictogramas. Ajustar el ancho de columnas al texto automáticamente.

Conceptos matemáticos Cálculo de medias ponderadas. Cálculo de medias aritméticas. Distinción de soluciones válidas o no válidas para un problema in-

directo (algebraico). Introducción a las operaciones lógicas. Distintas representaciones gráficas de la información.

a) Boletín de evaluación para la asignatura de Matemáticas.1. Escribir el bloque A1:G4 con los datos de la Figura.Para conseguir la alineación oblicua de la Fila 3:

Seleccionar Fila 3: Clic en el botón 3. Formato / Celdas... / Alineación/ Orientación: 60 Grados / Aceptar.Los valores de la Fila 4 representan las puntuaciones máximas en cada parcial y en la

nota final.Queremos que en F4 aparezca la nota media final máxima (en el supuesto

6

Matemáticas con EXCELde una persona con notas máximas en cada parcial), que en nuestro caso será 10, pero, de momento no escribiremos nada: EXCEL se encargará de calcularlo.2. Para evitarnos el trabajo de escribir los nombres y las notas de los alumnos, abriremos el Libro ya terminado, NOTAS.xls, y los copiaremos de allí.

Archivo / Abrir / (Buscar la ruta) / NOTAS.xls / Aceptar.Seleccionar el bloque A6:E17. Edición / Copiar.Abrir desde la Barra de tareas el Libro1. Seleccionar la celda A6. Edición / Pegar.Guardar el Libro1 con el nombre “1ºBCN.xls”.

[nota12] No podemos nombrar un Libro abierto con el nombre de otro Libro abierto.Cambiar el nombre de la Hoja1: “1ªev”.

3. Escribir en C5, D5 y E5 el peso porcentual de cada parcial: 30%, 30% y 40%, respecti-vamente.4. Calcular en F4 la media ponderada de un supuesto alumno con puntuaciones máximas en cada parcial.

(F4)=(C4*10/16)*0,3+D4*0,3+(E4*10/16)*0,4EXCEL nos debe devolver el valor 10.5. Rellenar hasta F17.6. Borrar F5.7. Cambiar el formato de la columna F a dos decimales.

b) Utilización del asistente de EXCEL: Pegar funciónEXCEL dispone de un gran número de funciones (más otras muchas que nosotros mis-mos podemos programar) que, con la práctica, podremos escribir directamente en la Ba-rra de fórmulas. Sin embargo, al principio suelen desconocerse su sintaxis y su funciona-miento, por lo que el botón que aparece en la Barra de herramientas Pegar función re-sulta considerablemente útil.1. Calcular las medias aritméticas de cada parcial y de nota final.

Seleccionar la celda C18. Clic en el botón Pegar función. EXCEL escribe automáti-camente el signo “=” y abre el siguiente Cuadro de diálogo, en el que, además de clasifi -car las funciones en Categorías (a la izquierda) aparece la sintaxis de la función, el nú-mero de argumentos y una sucinta explicación de la misma.

En la Categoría Estadísticas ( o en Todas, si se prefiere) seleccionar PROMEDIO /Aceptar.Observar que el rango que se nos propone no es el deseado. Cambiarlo a C6:C17. Antes de pulsar aceptar, ya sabemos el resultado (aparece en la parte inferior de la Ventana).2. Cambiar el formato de la celda C18 a un decimal.3. Rellenar desde C18 hasta F18, observando cómo se conserva en estas celdas el for-mato de la primera. [Cambiando el número de decimales podemos observar cómo EXCEL redondea a los de-cimales que le marcamos]4. Calcular en la columna G las notas definitivas, redondeadas por defecto y con ningún decimal.

Seleccionar G6. Botón Pegar función. Seleccionar en la Categoría de Matemáticas y trigonométricas REDONDEAR.MENOS. [Observar que EXCEL propone 5 clases distintas de redondeos] / Aceptar / En Número Seleccionar con un clic la celda F6 / En Num_deci-males Escribir: 0 / Aceptar.

El mismo resultado habríamos obtenido con la función ENTERO.5. Rellenar desde G6 hasta G18. [nota14] Observar con el Doble clic en el vértice inferior derecho de la celda G6, la ac-ción de rellenar se detiene en G12 porque encuentra en blanco la casilla D12. Si utiliza-mos la opción alternativa de arrastrar, solucionamos el escollo. [¿Qué diferencia apreciamos en la celda G18 si, en vez de rellenar desde G6 hasta G18, lo hacemos desde C18 hasta G18?]6. Si la columna G aparece con algún decimal, cambiar a cero decimales.

7

Matemáticas con EXCELc) Repaso de la herramienta Buscar objetivo.Observamos que la alumna del registro 12, Jordana, Alba, no tiene nota en el parcial de Álgebra. EXCEL otorga el valor 0 a la casilla en blanco.[nota13] Atención: el número “cero” (0) no es lo mismo que la letra “o mayúscula” (O). Si hubiésemos escrito en esta celda “no” o “faltó” o cualquier otra frase, EXCEL nos ha-bría devuelto en las celdas F12 y G12 sendos mensajes de error: #¡VALOR!Bien. Pongamos que tal alumna está pendiente de realizar en fecha próxima ese exa-men. Calcular la nota que debe obtener parai) sacar en la nota final al menos un 8. Respuesta: al menos un 8,1237.ii) sacar en la nota final al menos un 9. Respuesta: imposible.iii) sacar en la nota final un 5 o menos. Respuesta: imposible.

d) Utilización del condicional.d1) Simple.

Calcular en la columna H si el alumno es apto (cuando su nota final no es inferior al 5) o no apto (en otro caso).

Escribir en H3 promociona.Seleccionar la celda H3. Botón Pegar función / En la Categoría de funciones lógicas

Seleccionar SI / Aceptar.Rellenar los tres campos así:Prueba_lógica: G6<5Valor_si_verdarero: no aptoValor_si_falso: apto

Si no se escriben las comillas, se rellenan automáticamente. En la Barra de fórmulas aparece el siguiente texto (lo que deberíamos haber escrito no-sotros sin utilizar el botón asistente):(H6)=SI(G6<5;"no apto";"apto")

[Prestar atención a los “punto y coma”, “;” y a las comillas]EXCEL evalúa la sentencia inicial (s). Si es verdadera, escribe o calcula la orden primera (1); en otro caso, ejecuta la orden 2. En este caso, como 7 no es menor que 5, salta la or -den 1 y ejecuta la 2.Rellenar la celda H6 hasta H17.

d2) AnidadoLas órdenes en un condicional pueden contener a su vez funciones, lo que resulta muy útil, incluso necesario, para obtener ciertos resultados. Diremos entonces que los condi-cionales están anidados. [nota 5] Pueden anidarse hasta 7 condicionales en una función.Deberemos beneficiarnos de esta propiedad para trasladar las notas cuantitativas obte-nidas en la columna I “nota defitiva”.

Seleccionar la celda K6 y escribir:

Merece la pena detenerse un momento en la fórmula y realizar algunas observaciones: El orden de los condicionales no es conmutativo. No es difícil percatarse de que, si

“barriéramos” del sobresaliente al insuficiente, el resultado sería esencialmente distinto y erróneo, a no ser que utilizásemos el otro signo de desigualdad.

El segundo condicional no lleva a contradicción cuando impone que “todos” los menores de 6 los califique con “SF”, puesto que no son todos, son todos los que el primer condicional ha desechado por falsos.

8

s (F)2(V)1

Matemáticas con EXCEL No falta el último “SI” antes de “SB”: todo lo que no es lo anterior es “SB”. Aunque

no poniéndolo corremos el riesgo de que encuentre en la celda que se evalúa cual-quier cosa que no sea un número menor que 8,5 y le asigne el calificativo de “SB”.

Cuando vamos a cerrar los paréntesis encajados, EXCEL nos echa una mano adju-dicando colores distintos a parejas distintas de paréntesis.

También es de agradecer la labor correctora de EXCEL cuando escribimos una fór-mula sintácticamente incorrecta: el programa nos propone una enmienda que sue-le ser la adecuada.

e) Contar según una condición dada.Cuando los datos o resultados aparecen dispersos en la HC, la función CONTAR.SI se pre-senta como una herramienta interesante para agruparlos disponiendo así de tablas sus-ceptibles de representaciones gráficas u otro tipo de análisis.1. Confeccionar dos tablas, una de aptos / no aptos y otra de calificaciones cualitativas.

Escribir en el bloque A20:E25 lo que aparece en la Figura:

Obser-var que en A21 hemos de es-cribir “apto” y

no “aptos” si queremos que en B21 se haga referencia directa a la celda A21. En otro ca-so, en B21 deberíamos escribir, en lugar de A21, el texto “apto” y resultaría más costoso rellenar hacia abajo. Advertir también el bloqueo de los rangos en B21 y E21, para que no cambien cuando rellenemos hacia abajo.

Rellenar las celdas A21, B21 hacia abajo.f) Representación gráfica y últimos retoques1. Obtener una representación de aptos / no aptos, de tipo Circular con efecto 3D, con Tí-tulo MATEMÁTICAS, mostrando el porcentaje, en una HC nueva que se llame “1ªevAP-TOS”. Acercar los sectores, cambiar los colores del fondo, de los quesitos; aumentar el tamaño de las leyendas.2. Crear una representación de los resultados cualitativos, tipo Columnas, sin valor en el eje Y, sin líneas de división, sin leyenda, mostrando el valor en el Rótulo de datos, en una HC nueva con el nombre “1ªevCAL”, mejorando posteriormente su presentación. Dismi-nuir la distancia entre las barras: BS en una barra / Formato de serie de datos / Opcio-nes / Ancho de rango: 60 / Aceptar.3. Convertir el gráfico 1ªevCAL en un pictograma con distintas imágenes en cada barra.

En la HC 1ªev insertar cuatro o cinco imágenes prediseñadas referentes al ámbito académico. Seleccionar la primera / Copiar / Abrir 1ª evCAL / Seleccionar la 1ª barra (sólo la 1ª, no todas) / Pegar. Repetir el proceso para las demás. Si se quiere la misma imagen para todas, se seleccionan todas al principio, antes de pegar.4. Ajustar el texto a las columnas en la HC 1ªev.

Seleccionar toda la HC con un clic en el botón del vértice superior izquierdo. For-mato / Columnas / Autoajustar a la selección.Cambiar la alineación de la Fila 3 (hasta 85º) y volver a autoajustar la HC.

9

Matemáticas con EXCEL

Capítulo 2. SIMULACIÓN DE MOVIMIENTOEjemplo 2.1:RECT_PARAB.xls [rectas]Herramientas EXCEL

Abrir Visual Basic desde EXCEL Trabajar con la herramienta ScrollBar (Barra de desplazamiento)

que, realmente, con EXCEL, funciona como contador.

Conceptos matemáticos Significado geométrico de los coeficientes de la ecuación de una

recta cuando está expresada en forma explícita.Insertar un contador de Visual Basic para visualizar en la ecuación explícita de una recta:

i) la pendienteii) la ordenada en el origen

1. Escribir la ecuación de la recta en explícitas con parámetros concretos para que, al representarla, EXCEL no dé mensaje de error.2. Crear una tabla de valores con rango para las x’s de –50 a 50 y un incremento=1.

Rellenar hasta A115 y B1153. Representar la recta con el Asistente para gráficos. Elimi-nar la escala automática de los ejes fijando el de las x’s de –30 a 30 y de –20 a 20 el de las y’s. Mejorar el aspecto del gráfico utilizando el menú contextual sobre los ejes y el Área de trazado.4. Cambiar los valores de las celdas B1 y D1 y comprobar el efecto que produce en el gráfico.A continuación vamos a conseguir, ayudándonos del Visual Basic incorporado a EXCEL, una simulación de movimiento de la recta modificando el va-lor del coeficiente de la x (pendiente) y del término independiente (ordenada en el ori-gen).5. Visualizar la paleta de herramientas del Visual Basic.

Ver / Barras de herramientas / Visual Basic.6. Activar el botón de Cuadro de controles.7. Seleccionar el cuadro Barra de desplazamiento. [nota11] Realmente, este comando en Visual Basic no es un contador sino una barra de desplazamiento de texto. Este comando, cuando interviene en EXCEL, cambia su función y nos permite utilizarlo como una interesante herramienta didáctica.Dibujar el objeto debajo del coeficiente de las x’s.8. Hacer Doble clic sobre él: Bienvenido a Visual Basic.El efecto que pretendemos conseguir con este contador es el siguiente: cambiar el valor de la pendiente con un clic de ratón en la flecha de arriba o de abajo (también de forma continua deslizando el botón de la barra de desplazamiento) con un incremento, ponga-mos de 0,1, con valores extremos en –10 y +10.Para este objeto, las únicas propiedades del objeto que acabamos de crear (que se llama ScrollBar1) son las siguientes:LinkedCell, que hace referencia a la celda de la HC EXCEL cuyo valor cambiará a golpe de clic del ratón.Max, que es valor tope máximo que alcanzará y Min que es el valor mínimo.Como quiera que Visual Basic prefiere los números naturales a los demás, nos valdremos de una celda auxiliar, la A13, que luego volveremos invisible para el usuario, para conse-guir el movimiento en nuestro programa.9. Definir las propiedades del contador.

LinkedCell: A13. Max: 200. Min: 0.Cerrar la ventana de Visual Basic para volver a EXCEL.

10. Redefinir la celda B1.

10

Matemáticas con EXCELRealizar un cambio de escala en B1 para que el contador salte desde –10 hasta

+10 con un incremento de 0,1.11. El proceso se repite para el coeficiente independiente.12. Para ejecutar el programa es preciso Salir del modo diseño.13. Cuando abramos un Libro de este estilo, EXCEL nos preguntará si deseamos Habilitar macros, a lo que deberemos contestar siempre que Sí.14. Las únicas celdas que interesa tener visibles son las de la primera fila, así que pode-mos seleccionar el resto y cambiar su Color de fuente a Blanco para que el usuario no se distraiga con ellas.Las posibilidades didácticas que esta herramienta nos ofrece para la clase de Matemáti-cas (Física, etc.) es sólo comparable con la capacidad de nuestra imaginación.

11

Matemáticas con EXCELCapítulo 3 PROGRAMACIÓN EN VB CON MACROSEn el capítulo 2, Simulación de movimiento, tuvimos un primer contacto con Visual Basic: insertamos en la HC EXCEL un objeto de VB, un ScrollBar, al que modificamos alguna propiedad para utilizarlo como contador y aparentar así movimiento mediante el ratón. En este caso no tuvimos que escribir código para el objeto. Programar en VB pasa nece-sariamente por conocer la sintaxis de los controles, sus propiedades, los eventos, los mé-todos. Adquirir esta destreza requiere un estudio específico de Visual Basic; sin embargo, mediante la utilización de las macros de EXCEL, puede conseguirse algo parecido a pro-gramar.Las macros permiten automatizar tareas que normalmente se realizan repetidas veces. Una macro consiste en una serie de comandos e instrucciones que se agrupan en un solo comando de forma que la tarea pueda realizarse automáticamente. En lugar de perder el tiempo realizando una serie de acciones encadenadas, puede crearse y ejecutar una ma-cro, es decir, un comando personalizado, que ejecute la tarea mediante, por ejemplo, un golpe de ratón o una combinación de teclas.Las macros pueden grabarse. Y también pueden copiarse. El quid de la cuestión se resu-me en tres líneas: 1. Insertar un Botón de comando de VB.2. Grabar la macro.3. Copiar el código de la macro en el código del Botón de comando.De esta forma la macro nos traduce lo que queremos hacer a lenguaje VB, y cuando ha-cemos sobre el botón un clic (por ejemplo), EXCEL ejecuta nuestros deseos aunque no sepamos cómo se escribe. Algo parecido a ordenar a un carpintero chino que nos haga una mesa de nogal, nos pinte las ventanas en haya y nos fabrique una librería de roble. Todo, sin saber chino.El ejercicio siguiente es muy sencillo para comprender sin problemas la filosofía de lo ex-plicado más arriba. Ejemplo 3.1: TOMATERO.xls [tomate]Herramientas EXCEL

Utilizar la Barra de Dibujo de EXCEL Insertar una imagen prediseñada. Visualizar las herramientas VB desde EXCEL. Insertar un Botón de comando y cambiar sus propiedades. Grabar una macro. Copiar el código de una macro de EXCEL en el código de VB.

Conceptos matemáticos Calcular aumentos porcentuales.

Diseñar una HC que calcule, a partir de tres datos iniciales (el precio de com-pra de un kilo de tomate, el precio de envase de un kilo y el porcentaje de be-neficio sobre los gastos), el precio final de venta de un kilo y el porcentaje de gravamen sobre el precio de compra.Insertar un botón que limpie los datos iniciales.1. Escribir la HC como indica la Figura teniendo en cuenta que:

i) en D10 y D11, así como en F13, se ha elegido formato moneda en pesetas,ii) para los porcentajes se ha conservado la opción número, escribiendo en otra

celda el signo “%”,iii) las flechas se han dibujado con la Barra de herramientas Dibujo que debe vi-

sualizarse si no lo está:Ver / Barra de herramientas / Dibujo.iv) La imagen del tomate se obtiene de Imágenes prediseñadas:Insertar / Imagen / Imágenes prediseñadas

12

Matemáticas con EXCEL

2. El cálculo en la celda F13 es elemen-tal:

(F13) =(D10+D11)*(1+D12/100)3. Y así, el cálculo del porcentaje de gra-vamen se realiza con referencia la pre-cio final:

(F10) =(F13/D10-1)*1004. Esta HC puede ser perfectamente ela-borada por alumnos con conocimientos básicos de aumentos porcentuales y una vez acabada puede utilizarse para resolver problemas de tipo inverso (al-gebraico) como imponer un precio final

e investigar (por tanteo, con la herramienta Buscar objetivo y/o resolviendo la ecuación correspondiente) el precio del envasado o el porcentaje de beneficio, etc., proponiendo casos que admitan solución o no.5. Insertar un Botón de comando de VB.Visualizar (si no lo está) la Barra de herramientas de VB:

Ver / Barra de herramientas / Visual Basic.6. Visualizar el Cuadro de controles.7. Dibujar un Botón de comando.8. Con Doble clic en el botón, entramos en VB. En principio sólo hemos de cambiar la propiedad Caption: escribir “Limpiar”. Volvemos a EXCEL.9. Grabar macro.

Herramientas / Macro / Grabar nueva macro.Dejar el nombre que nos propone / Aceptar.10. Ahora debemos ejecutar todas las órdenes encadenadas que deseamos ejecute la macro.

Seleccionar la celda D10 y Suprimir. Ídem para D11 y D12.11. Detener la grabación de la macro en la Barra de herramientas de VB.12. Abrir la macro recién grabada.

Herramientas / Macro / Macros.13. Copiar el texto de la macro.

Seleccionar la macro grabada / Paso a paso.Seleccionar el código / BS Copiar.

14. Pegarla en el código del objeto comando de VB que hemos creado con anterioridad.Doble clic en el botón. Copiar en el espacio, vacío, reservado para el código del

CommandButton1: al ejecutarse el evento Clic, se realizarán, por este orden las seis lí -neas de código en negro (las verdes con comilla al principio de línea son meramente ex-plicativas). No es difícil interpretar que lo que hemos realizado al grabar la macro es algo parecido a lo que ahí está escrito (en inglés).15. Comprobar que funciona.

Volver a EXCEL cerrando VB. Salir del modo de diseño para poder ejecutar el programa.Rellenar las celdas de fondo amarillo (D10, D11 y D12) con tres datos.Clic en el botón Limpiar.

16. Podemos utilizar sendos condicionales en las celdas F13 y F10 para evitar mensajes de error cuando pulsamos el botón de limpiar. Teniendo en cuenta que una celda en blanco EXCEL la evalúa como 0, escribiremos:

(F10) =SI(D10<=0;"¿p?";(F13/D10-1)*100)(F13) =SI(D10<=0;"¿p?";(D10+D11)*(1+D12/100))

17. Proteger la HC desbloqueando previamente las celdas D10:D12, para que el usuario tengo acceso a ellas.18. No olvidemos que, cada vez que abramos este Libro, EXCEL nos preguntará si desea-mos habilitar las macros a lo que debemos contestar que afirmativamente.

13

Matemáticas con EXCEL

14

Matemáticas con EXCELCapítulo 4 ESTADÍSTICA A) MEDIDAS DE CENTRALIZACIÓN Y DISPERSIÓNCÁLCULOA1) Con fórmulas explícitasHemos obtenido las pulsaciones de un equipo de atletismo después de una carrera. Están recogidas en la HC de cálculo siguiente. Calcula las medidas de centralización y de disper-sión. Tienes que aplicar las fórmulas.Comprueba que coinciden con las fórmulas que EXCEL te de-vuelve, después de investigar cuáles son las que necesitas.

A2) Con las fórmulas de EXCEL.Calcula todas las medidas de centralización y de dispersión de la siguiente tabla estadís-tica.Escribe en una HC que se llame Definiciones una definición de cada una de ellas y, debajo, las definiciones que EXCEL da de ellas.

Solución: a) En E7 escribe la fórmula =CONTAR(A2:E6)Medidas de centralizaciónb) En E9 escribe la fórmula =PROMEDIO(A2:E6)c) En E1O escribe la fórmula =MEDIANA(A2:E6)d) En E11 escribe la fórmula =MODA(A2:E6)Medidas de dispersióne) En E13 escribe la fórmula =MAX(A2:E6) — MIN(A2:E6)

f) En E14 escribe la fórmula =VARP(A2:E6)g) En E15 escribe la fórmula =DESVESTP(A2:E6)

PROPIEDADESCambiando los datos de la fila 3 y los parámetros de las celdas B8 y B14, observa cómo van variando las medidas de centralización y dispersión:

Observa paralelamente el gráfico asociado y establece conclusiones.

B) ESTADÍSTICA BIDIMENSIONALIncidencia de la TV en el rendimiento escolar

Queremos estudiar la incidencia que tiene el número de horas que dedican a ver la te-levisión los alumnos de un grupo de Bachillerato en su rendimiento acadé-mico. Para ello, hemos tomado una muestra de 7 alumnos.

15

Matemáticas con EXCELCalcula:a)El centro de gravedad.b)Las desviaciones típicas marginales.c) La covarianza.d)El coeficiente de correlación.e)Representa la nube de puntos.f) Halla y representa la recta de regresión.g) ¿Cuántas asignaturas cabe esperar que aprobará un alumno que ve la TV 3 horas?

Solución:En las celdas siguientes escribe la fórmula que se indica.a)En la celda B9 escribe la fórmula =PROMEDIO(B2:B8) y arrastra el controlador de re-

lleno de la celda B9 hasta C9b)En la celda B1O escribe la fórmula =DESVESTP(B2:B8) y arrastra el controlador de

relleno de la celda B1O hasta C10c)En la celda B11 escribe la fórmula =COVAR(B2:B8;C2:C8)d) En la celda B12 escribe la fórmula =COEF.DE.CORREL(B2:B8;C2:C8)e)Selecciona los datos del rango B2:C8, haz clic Asistente para gráficos.

i) Paso 1. de 4- Tipo de gráfico: XY (Dispersión). Subtipo de gráfico: Compara pares de valores.

ii) Paso 2 de 4 - Datos de origen: haz clic en Siguiente (los datos ya están elegidos).iii) Paso 3 de 4 - Opciones de gráfico: rellena la ficha Títulos y desactiva la ficha Le-

yenda.iv) Paso 4 de 4 - Ubicación del gráfico: elige en la misma HC.

f) En el gráfico selecciona los puntos ha-ciendo clic en uno de ellos, activa el me-nú Contextual haciendo clic en el botón derecho, elige Agregar Línea de tenden-cia..., en la ficha Tipo: Lineal, en la ficha Opciones activa la casilla Presentar ecuación en el gráfico y Aceptar.Luego retoca el gráfico para que mejore la apariencia.

g)En la celda B13 escribe 3 y en la C13 la fórmula

= - 0,8478 * Bh3 + 6,6087

C) LA BINOMIAL TIENDE A LA NORMALVamos todavía a introducir un último recurso de EXCEL mediante el que se simula movi-miento sin apelar a VB. De paso, conoceremos la sintaxis de una de las múltiples varia-bles aleatorias estadísticas que EXCEL nos ofrece, la Binomial, y veremos cómo, efectiva-mente, independientemente del valor de p, cuando el número de pruebas es suficiente-mente alto, binomial y normal se confunden.1. Rellenar la columna D de 0 a 100.2. Escribir en B1 n y en C1 p. 3. En C2 0,8 y en B2 B2+1.Como se ha realizado una autorreferencia, EXCEL nos avisará. Aceptaremos, cerraremos la ayuda y en Opciones / Calcular elegiremos el Cálculo manual con nº máximo de itera-ciones 1. Aceptar.A partir de ahora, cada vez que pulsemos F9, la celda B2 saltará una unidad.3. Con el asistente Pegar función conseguir en A1

(A1) =DISTR.BINOM(D1;$B$2;$C$2;FALSO)y arrastrar hasta A100.4. Crear un gráfico de barras con este bloque.5. Pulsar F9 y comprobar el movimiento que acabamos de conseguir.

16

Matemáticas con EXCELCapítulo 5 OPTIMIZACIÓN. LA HERRAMIENTA SOLVERYa conocemos cómo funciona la herramienta Buscar Objetivo. Se utiliza en el caso de que se conozca el resultado deseado de una fórmula sencilla, pero no alguna de las va-riables que lo determinan. Al realizar una búsqueda de objetivo, EXCEL varía el valor de la celda específica hasta que una fórmula dependiente de dicha celda devuelve el resul-tado deseado.Así, pueden resolverse muchos problemas de tipo inverso cuyo planteamiento clásico comporta una ecuación más o menos complicada.En esta línea, EXCEL nos ofrece una herramienta más potente aún. Solver. Con Solver puede buscarse el valor óptimo para una celda, denominada celda objetivo, en una HC de cálculo. Solver funciona en un grupo de celdas que estén relacionadas, directa o indi-rectamente, con la fórmula de la celda objetivo, ajusta los valores en las celdas cambian-tes que se especifiquen, denominadas celdas ajustables, para generar el resultado espe-cificado en la fórmula de la celda objetivo. Pueden aplicarse restricciones para delimitar los valores de las soluciones.A menudo, se dice, los árboles nos impiden ver el bosque. Frecuentemente, las complica-ciones que conllevan el cálculo en la resolución de problemas en el aula de matemáticas hacen que perdamos de vista la esencia del mismo. Solver nos libera de esta carga en problemas como optimización de funciones (de una y varias variables), programación li-neal o resolución de sistemas de ecuaciones (complicados o no).Ejemplo 5.1: SOLVER.xls [chapa]Problemas de optimización pueden plantearse de todo tipo y en todos los niveles. Uno sencillo nos permitirá abordarlo desde tres puntos de vista distintos y nos dará pie a pre-sentar la herramienta Solver.Herramientas EXCEL

Funcionamiento de Solver.

Conceptos matemáticos Introducción a los problemas de optimización.

Diseñar una HC que resuelva el siguiente problema:Partiendo de una chapa cuadrada de una superficie dada, se cortan en los vértices cuatro cuadraditos iguales para, doblando las solapas resultantes, conseguir una caja sin tapa. Calcular el lado del cuadrado que se corta para conseguir una caja de máximo volumen.

Con la función MAXUn acercamiento al problema puede ser construir una tabla de valores con la que se cal-cula el volumen en función del corte.1. Definir la variable S (superficie del cuadrado inicial, que tomamos como 100).2. Definir el rango x, que será [1,50] con 1 de incremento.3. Escribir la fórmula del volumen en la celda C11.4. Hacer el gráfico de la curva.5. Calcular el máximo con la función MAX.Para que aparezca en pantalla también, al lado del volumen máximo el valor del corte para el que se alcanza tal valor procedemos así:En la columna D resaltamos la fila donde aparece el máximo escribiendo en D11:

(D11) =SI(C11=$H$6;B11;"")Y debajo de la celda del volumen máximo, H7, trasladamos el valor de la x:

(H7) =MAX(D11:D60)

Con BUSCAR OBJETIVOEn este caso, esta función no resuelve el problema, pero es útil si se quiere utilizar el método del tanteo.Con SOLVERSi nuestro ordenador no ha utilizado nunca Solver, tendremos que sacarlo de la opción Complementos, en el menú Herramientas.

17

Matemáticas con EXCELPlanteamos la cuestión:

(F20)=7 (G20)= =F20*(S-2*F20)^2 [devuelve 51772]Seleccionar G20 (celda objetivo)Herramientas / Solver

Celda objetivo: $G$20Valor de la celda objetivo: MáximoCambiando las celdas: $F$20Sujetas a las siguientes restricciones: [en este caso, como no hay ninguna, dejar en blanco]

Se obtiene, en la celda G20, el valor máximo, y en la F20 el corte que se precisa para conseguirlo.

Resolver / Utilizar solución de Solver / Aceptar.Veamos algún problema con restricciones.Ejemplo 5.2: SOLVER.xls [cocacola]

Calcular las dimensiones de una lata cilíndrica de 1/3 de litro de volumen para que el coste de la chapa sea mínimo [Suponemos los fondos y el lateral del mismo precio].Herramientas EXCEL

Utilizar Solver con restricciones. PI(), función sin argumentos.

Conceptos matemáticos Planteamiento de problemas de optimización de funciones de dos

variables con una restricción

1. Definir las variables r(radio de la base) y h(al-tura). Dar dos valores cualesquiera.2. Escribir la función que se optimiza:

(C9) =2*PI()*r^2+2*PI()*r*h3. Debajo, la condición

(C10) =PI()*r^2*h-333[La preparamos para igualarla a cero luego, aunque también podemos igualarla a cualquier otro número]4. Seleccionar C9 (celda objetivo) / Herramientas / Solver

Celda objetivo: $C$9Valor de la celda objetivo: MínimoCambiando las celdas: $A$7;$B$7Sujetas a las siguientes restricciones: $C$10=0

Aceptar / Resolver / Utilizar solución de Solver / Aceptar.Se obtiene, en la celda C9, el valor mínimo, en C10 la confirmación de que se ha cumpli-do la restricción impuesta y en las A7 y B7 los valores idóneos para el radio y la altura.

18

Matemáticas con EXCELCapítulo 6 RESOLUCIÓN DE ECUACIONESEjemplo 6.3 RESgenECU.xls [Bolzano]Se trata de determinar una secuencia de intervalos encajados que contengan una raíz de la ecuación dada evaluando la función correspondiente en los extremos, eligiendo aquél en el que toma valores de distinto signo e iterar el proceso hasta llegar a la precisión de-seada.Para no dar “palos de ciego” se representa la curva correspondiente:así se proporciona al usuario pistas para saber por dónde empezar.Es una manera muy clara e intuitiva de introducir sin demostración formal el Teorema de Bolzano.Herramientas EXCEL

Definir variables. Utilizar el formato rojo para resaltar los números negativos.

Conceptos matemáticos Calcular soluciones de cualquier ecuación utilizando el Teorema

de Bolzano.

Diseñar una HC que resuelva cualquier ecuación por el método de Bolzano de aproximaciones sucesivas y represente la curva asociada a la misma.1. Escribir las celdas como en la Figura

2. Podemos definir variables con el nombre que queramos para luego referirnos a ellas no por la celda sino por el nombre que le hemos asignado. Esta sutiliza permite olvidarse de bloqueos y hacer que el lenguaje de programación de la HC resulte más comprensivo.Definir la variable Arranque:

Seleccionar A4:A5 / Insertar / Nombre / Definir / Aceptar la que nos propone (Arranque) Podemos comprobar si lo hemos hecho bien: Seleccionando la celda A5 debe aparecer en el Cuadro de nombres (arriba, a la izquierda, la palabra Arranque).Definir la variable Incremento de la misma manera.3. Escribir en la celdas G6 y G7

y rellenar hasta G18.4. Definir la variable x:

Seleccionar G5:G18 / Insertar / Nombre / Definir / Selec-cionar x / Comprobar el rango [=Bolzano!$G$6:$G$18] /

Aceptar.5. A partir de ahora, cuando escribamos “x”, EXCEL entenderá el rango al que nos referi -mos. Así, el usuario, cuando cambie de función, no deberá escribir celdas, lo que no sue-le ser usual, sino que utilizará la notación habitual, con variable en x.Rellenar H6 hasta H18.6. Con el bloque G5:H18 seleccionado, realizar, con el Asistente para gráficos, uno con el formato, colores, etc. que deseemos pero sin quitar la opción de autoescalado, pues en este caso nos interesa una visión global de la gráfica desde el punto de arranque que es-timemos adecuado.7. Por fin, resaltaremos los casos en los que la función cambie de signo. Podemos hacerlo de dos maneras:a) Cambiar a Formato / Celdas / Número / Negativos en rojo el bloque correspondiente a los valores que va tomando la función. No debemos olvidar, si buscamos una precisión aceptable, aumentar el número de decimales en el rango G6:H18.b) Utilizar la columna I como testigo en caso de cambio de signo escribiendo así:

19

Matemáticas con EXCEL(I7) =SI(H7*H8>0;"";"b")

rellenando y cambiando la fuente en esta columna a Wingdings3, rojo: en caso de cam-bio de signo, una flecha roja nos lo marcará.8. Si el usuario desea probar con una nueva función deberá:1. Borrar la celda H5 y rescribirla, si quiere que se visualice la ecuación en el gráfico.2. Borrar la celda H6 y escribir la fórmula (utilizando el signo “=” al principio y la grafía “x”).3. Rellenar hasta H18.Podemos facilitar la tarea con un par de botones, que crearemos grabando las macros correspondientes, y dos comentarios en las celdas H5 y H6:

Borra el rango H6:H18Indica dónde hay que escribir la función.

Indica dónde hay que escribir la función con sintaxis EXCEL.

Rellena la fórmula hasta H18.

20

Matemáticas con EXCEL

Capítulo 7 ÁLGEBRA MATRICIALOperar con matrices con lápiz y papel resulta bastante pesado. Con EXCEL es rápido y di-recto. Sin embargo, esta modalidad de cálculo con ordenador no enseña nada (calcula-mos la matriz inversa de una dada pero no aprendemos el algoritmo y, menos aún, por qué el algoritmo funciona).En cualquier caso, esta herramienta nos permite comprobar propiedades que ya no se suelen demostrar en clase, además de liberarnos de cálculos engorrosos y proporcionar-nos seguridad en los resultados.Ejemplo 7.1: MATRICES.xls [operaciones]Herramientas EXCEL

Definición de matrices. Tratamiento de las operaciones cuyo resultado aparece en un

bloque. Funciones MMULT, MINVERSA, MDETERM.

Conceptos matemáticos Inducción o / y comprobación de las propiedades de las operacio-

nes con matrices.

Diseñar una HC que realice las operaciones elementales con matrices1. Introducir texto, coloreando los bloques para que aparezca clara la distinción en-tre las matrices:2. Dar nombre a las matrices:

Seleccionar el bloque A2:C3 / Inser-tar / Nombre / Definir. Nos propone por defecto A / Aceptar.Lo mismo para B y D. [ Recordemos que no admite el nombre C, sí el de C_]3. Escribimos en A5: A+B. (No es necesario, pero es conveniente si luego queremos nom-brar la matriz suma: EXCEL nos propondrá este nombre).Como la matriz resultante será de orden 2x3, habrá que seleccionar un rango que con-tenga 2 filas y 3 columnas.4. Seleccionar el rango A6:C7 e introducir en la barra la fórmula =A+B (también valdría A2:C3+D2:F3, en el caso en que no hubiéramos definido las matrices). (¡ATENCIÓN!) Pulsar CTRL.+MAYÚSCULAS+ENTER y se obtendrá la suma A+B en el ran-go seleccionado.En la barra de fórmulas puede verse que todas las celdas de ese rango contienen la fór -mula matricial {=A+B}. (Las llaves las escribe EXCEL).(Si sólo pulsamos ENTER o el botón de verificación, únicamente hace la cuenta en la cel-da A6).5. Para calcular AxD y DET(AxD) procedemos de la misma forma introduciendo ahora, bien directamente bien con el botón Insertar función si no recordamos la sintaxis, =MMULT(A;D) y =MDETERM(AxC), si hemos definido la matriz producto previamente.6. Por fin, introducimos y definimos F, una matriz 2x2, y calculamos DET(F), F -1, DET(F-1) mediante las fórmulas=MDETERM(F), =MINVERSA(F) y =MDETERM(E15:F16) (escribiendo en este caso el ran-go porque no se ha definido la matriz F-1).De paso podemos comprobar una propiedad, nada inmediata, de determinantes:

DET(A-1)=1/DET(A)7. Utilizando la opción $$$/$$$ para el formato de la matriz inversa, la obtenemos en for-ma de fracción.

21

Matemáticas con EXCEL

Capítulo 8 FUNCIONES DEFINIDAS POR EL USUARIOAdemás de las funciones definidas en EXCEL (muchas, por cierto), el usuario dispone de la opción de definir las suyas propias. Esta ventaja resulta conveniente para simplificar muchas HCs de cálculo y necesaria en la elaboración de otras como la que pasamos a explicar.Hemos llegado a un punto en el que el truco de grabar macro y copiarla ya no nos sirve. Hay que agregar el código manualmente, pero, en este caso, resulta muy sencillo y el es-fuerzo, sin duda, se verá recompensado con creces. Ejemplo 8.1: CRE_CON.xlsEn una ampliación de la HC LIMITES.xls.Estudiaremos gráfica y analíticamente la relación entre el signo de la primera y segunda derivadas y el crecimiento y la convexidad de una función en un punto.Herramientas EXCEL

Definir una función (Definida por el Usuario) con el Editor de Vi-sual Basic.

Conceptos matemáticos Relación entre el signo de la primera derivada y el crecimiento o

decrecimiento de una función en un punto. Máximos y mínimos relativos.

Relación entre el signo de la segunda derivada y la concavidad o convexidad de una función en un punto. Puntos de inflexión.

Diseñar una HC que evalúe el signo de la primera y segunda derivadas de cual-quier función en un punto.1. Con un nuevo Libro abierto, aún vacío, definir una función que llamaremos h.

Herramientas / Macro / Editor de Visual BasicYa en VB:

Insertar / MóduloEscribir en el espacio en blanco las siguientes líneas de código:

Public Function h(x As Single) As Single h = 3 * x - x ^ 3End Function

[Todas las variables tienen un tipo de dato específico. Un tipo de dato es una característi-ca de una variable que determina qué clase de dato puede almacenar. Si no se define un tipo de dato concreto, se da a la variable el tipo de Variante. En este caso, a menos que se especifique el tipo de dato del argumento y del valor de la Función Definida por el Usuario, dicha función no se ejecutará correctamente. El argumento y el valor de h son numéricos. Hay distintos datos numéricos; el que utilizaremos aquí será el Single].2. Volver a EXCEL. Clic en botón Pegar función. En la Categoría Definidas por el Usuario deberemos encontrar nuestra flamante función: h.A partir de ahora disponemos de esta función que podemos utilizar escribiendo directa-mente en la barra de fórmulas o pasando por el botón Pegar función.Cuando nos interese, podremos cambiar la definición abriendo de nuevo VB.Reflexione el lector en que la definición de una nueva función sugiere complejidades se-ductoras (discutir si un número es primo, calcular directamente el área bajo una curva, etc.) cuyo dominio pasa por el aprendizaje del lenguaje VB.Para calcular aproximadamente h’(a), h’’(a) –tan aproximadamente como queramos-, ha-remos uso de las siguientes aproximaciones:

3. Definir las variables a (punto donde la función va a ser analizada), incremento (el de la tabla de valores) y h (el incremento de las fórmulas). Por defecto, asignaremos 0,5 a in-cremento y 0,001 a h.4. Preparar el encabezado:

22

Matemáticas con EXCEL5. Tomando en A12 el punto (=a), rellenar hacia arriba (según =A12-incremento) y hacia abajo (=A12+incremento), obteniendo así el rango A2:A22 para las x’s.6. Definir ese rango como x.7. Escribir las fórmulas de f, f’ y f’’ en B2, D2 y F2. (Es aquí donde reside la bonanza de haber definido f previamente):

(B2)=f(x) [o f(x+0) si no funciona](D2) =(f(x+h)-f(x-h))/(2*h)(F2) =(f(x+2*h)+f(x-2*h)-2*f(x+0))/(4*h^2)

8. Rellenar hasta F22.9. Completar la tabla que da la información resumida. En P5 y Q5 decidimos a través de condicionales, pero en los casos críticos f’(a)=0, f’’(a)=0, como no tenemos certeza, es-cribimos”¿?”. El usuario solventará.

(P5) =SI(P4>0;"CRECE";SI(P4<0;"DECRECE";"¿?"))(Q5) =SI(Q4>0;"CÓNCAVA";SI(Q4<0;"CONVEXA";"¿P.I.?"))

10. Representamos la función utilizando nuestro gráfico personalizado y guardado “gráfi-co-xy10”.11. Para adjuntar la recta tangente en el punto a, necesitamos una nueva columna, la G, y en G2, apelando a los datos expuestos en la tabla, escribimos la ecuación de la recta tangente que arrastramos hasta G22:

(G2=$O$4+$P$4*(x-$N$4)12. En los datos de origen del gráfico agregamos esta nueva serie.13. Antes de proteger la HC (una vez protegida no permite ni cambios ni inserciones) ad-juntamos un archivo de sonido (y un comentario) para informar del procedimiento cómo cambiar de función.14. Por fin, tras desbloquear la celda I4, donde el usuario escribe el punto (optamos por dejar fijas J4, K4, aunque se pueden cambiar desprotegiendo la HC), protegemos la HC, con cuidado de desbloquear la opción objetos con el fin de que pueda activarse el objeto Archivo de sonido. Ocultamos las líneas de división y comprobamos cómo funciona.

23

Matemáticas con EXCEL

Capítulo 9 PROGRAMACIÓN LINEALSi tenemos en cuenta que las restricciones en Solver, no sólo admiten igualdades sino también desigualdades, caeremos en la cuenta de que esta herramienta también sirve para resolver problemas de Programación Lineal (¡y no sólo con dos variables!)Ejemplo 9.4 PL.xls [Hoja1]Herramientas EXCEL

Utilizar Solve con más de una restricción

Conceptos matemáticos Plantear problemas de Programación Lineal

Diseñar una HC que resuelva el siguiente problema de Programación Lineal:Minimizar la función

Sujeta a las siguientes restricciones

1. Preparar la HC escribiendo en las celdas con fondo amarillo el valor de los coeficien-tes, definiendo las variables x, y.

Damos unos valores iniciales a x, y (1, 1, por ejemplo) y simplemente calculamos en E12 (arrastrando a E13, E14) y en G9:

(E12) =B12*x+C12*y(G9) =B9*x+C9*y

La columna F12 es meramente infor-mativa, como la H9.2. Seleccionar G9 y entrar en Solver:Celda objetivo: $G$9Valor de la celda objetivo: MínimoCambiando las celdas: $B$7; $C$7Sujetas a las siguientes restricciones:

Botón Agregar$E$12>=$G$12$E$13>=$G$13$E$13>=$G$13

Entrar en Opciones y cerciorarse de que la estimación es Lineal.Resolver / Utilizar solución de Solver / Aceptar.

3. Aparece en pantalla la solución. Pondremos las celdas de salida con dos decimales:No cabe duda de que este procedimiento resuelve directamente el problema, pero para los alumnos, que todavía están en la fase de captar el método que se utiliza, es más in-teresante apoyar el análisis con un gráfico en el que aparecen las rectas que delimitan la región factible y la recta asociada a la función objetivo se mueve desde el origen barrien-do distintas posibles soluciones. También puede adjuntarse un punto (valor que toma la función objetivo en una primera aproximación). Podremos mover el punto sobre el gráfi-co y conseguir muy ajustadamente la solución sin ni siquiera hacer uso de la herramienta SolverEjemplo 9.4 PL.xls [PL_gen]Conceptos matemáticos

Resolución gráfica de problemas de Programación Lineal.

1. Elaborar un gráfico en el que aparezcan las rectas asociadas a las restricciones, la rec-ta asociada a la función objetivo (punteada para distinguirla de las otras), el punto (x,y)

24

Matemáticas con EXCELque representa el / los tanteo /s antes de apelar a Solver. [Si resolvemos el problema con Solver, el punto se irá a parar al vértice correspondiente a la solución óptima]2. El contador que aparece a la izquierda del gráfico es el encargado de mover la recta función objetivo.3. Las tablas de valores de las rectas, así como la celda vinculada al contador han sido ocultadas dándoles un color de fuente blanco.

4. Se ha optado por fijar el rango de los ejes del gráfi-co ([0,10]x[0,10]). El usua-rio debe adaptar los datos (dividiendo por 10, 100, etc.) para que las rectas aparezcan en pantalla.

25

Matemáticas con EXCELCapítulo 10: CÁLCULO INTEGRALA) LA INTEGRAL DE RIEMANNEjemplo 10.1: ÁREAS.xlsPartiendo del área del rectángulo, pasando por la del triángulo, por la del círculo, se llega a una idea precisa de la Integral de Riemann a través de funciones escalonadas.

Herramientas EXCEL Función RADIANES.

Conceptos matemáticos Área del rectángulo, triángulo rectángulo, paralelogramo, triángu-

lo. Área del círculo por triangulación de triángulos isósceles por de-

fecto. Integral de Riemann de cualquier función a través de sumas infe-

riores.

En la primera HC, [rectángulo], se calcula el área de un triángu-lo y se dibuja en papel cuadricu-lado. Se ha optado por un rango del gráfico fijo, [0,10]x[0,10]. El usuario sólo debe meter la an-chura y largura del rectángulo.La etiqueta para la fórmula es un inserto desde el Editor de ecuaciones.Para realizar el gráfico hay que escribir las coordenadas de los vértices (empezando y acabando por el mismo para que el polígono cierre) en unas casi -llas auxiliares (tapadas luego por el mismo gráfico) eligiendo el tipo de Dispersión con puntos de datos conectados por líneas.Análoga para el caso del triángulo es la siguiente, [tri_rec], con la novedad de que ahora hay dos series de datos, una para rectángulo y otra para el triángulo rectángulo que, ob-viamente, mide la mitad de aquél.

También hay dos series en el caso del paralelogramo, [paralelogramo], una para él y la otra para su triángulo asociado: el ver los dos polígonos superpuestos es tanto como captar la nueva fórmula. Aquí el alumno debe introducir las coordenadas del segundo vértice, B, y la abcisa del tercero, C, bien entendido que deben cumplir ciertas condiciones, para que la figura se visualice por completo. Parece sobrecargar innecesariamente la HC metiendo condicio-nales que le impidan introducir datos no de-seados. Así, las celdas no sombreadas, que se-ría bueno bloquear, quedan de esta forma:

La HC [triangulo] no presen-ta novedad.No así la del círculo, [circu-lo]. No tanto en encontrar la fórmula del área en función del número de triángulos sino en cuanto representar el círculo, el polígono inscrito y los triángu-los isósceles correspondientes, lo que implica un uso no elemental de celdas complementarias. La interpretación de las mismas se deja co-

mo interesante ejercicio para el lector.

26

Matemáticas con EXCEL(Éste puede ser un ejercicio no complicado de trigonometría para alumnos de 1º de Ba-chillerato e, incluso, de 4º de ESO [optimismo])[Recordemos que las funciones trigonométricas de EXCEL están en modo radianes, y que la función RADIANES convierte grados a radianes]Podríamos haber insertado un contador que moviera automáticamente el número de la partición, pero se ha optado por que el usuario lo escriba a mano.

En la última HC, [curva], el alumno introduce los extremos del intervalo que quiere eva-luar, i y f, y el número de rectángulos, p, (máximo de 1000) con los que se quiere aproximar el área de la curva por defecto. El programa le devuelve la suma pedida y una representación gráfica de la curva

aproximada por la correspondiente función escalonada.La función se va a definir como integral (¡ojo!, no es la integral, así hemos llamado a la función) abriendo el Editor de VB, insertando un Módulo y escribiendo en él:

Public Function integral(x As Single) As Single integral = x ^ 2End Function

A partir de ahora, apelaremos a la función cuadrática (que en cualquier momento pode-mos cambiar por la que queramos) por su nombre, integral. [No utilizamos f, ni h, porque estas letras ya las tenemos en nuestra lista particular de funciones definidas por el usua-rio].La complicación estriba en la representación no de la función, naturalmente, sino de la escalonada según los valores del parámetro p. Lo resolveremos con la ayuda de algunas celdas auxiliares:1. En la celda A10 escribimos 0 y rellenamos hasta A1000 en series de incremento 1, ob-teniendo los 1001 primeros números naturales.2. Escribiendo

consegui-mos

en B los números naturales siempre que sean menores que p

en C vamos evaluando el área de cada rectángulo y nos paramos en p-1.[en la celda reservada para la suma de todos, G2, bastará escribir

=SUMA(C10:C1010)] en D evaluamos sin más el valor de la función en todos los puntos (del 0 al 1000)

para representarla luego.3. Por fin, para representar la esca-lonada, jugamos con la fun- ción parte entera (ENTERO).

Para la curva usamos el tipo Dispersión con líneas suavizadas y sin marcadores de datos y para la escalonada Columna agrupada.

B) LA CURVA INTEGRALEjemplo 10.3; integral.XLS [Hoja3]Se trata de construir la curva integral de una función poligonal dada. El usuario anota los extremos del polígono y las dos curvas aparecen en pantalla. Pueden hacerse compara-ciones entre ambas. Lo que se pretende es llegar gráficamente a estos resultados:

Si la curva corta al eje de abcisas, la curva integral presenta un máximo o un míni-mo.

Si la curva presenta un máximo o un mínimo, la curva integral tiene un punto de inflexión.

La curva dada vale lo que la derivada de la curva integral.

27

Matemáticas con EXCEL

Conceptos matemáticos Construcción de la curva integral de una función dada. Propiedades de la curva integral. Teorema Fundamental del Cálculo Integral.

1. Escribir en la primera columna la serie del 0 al 10 (los valores de las x’s).2. Rellenar como se quiera los correspondien-tes de la columna B que marcarán los extre-mos de los segmentos del polígono.3. Completar la columna C con lo que van a ser las sumas acumuladas de las áreas de los trapecios.4. Representar las dos poligonales en sendos gráficos.5. Insertar tres comentarios con las propiedades que se quieren inducir.6. Cambiar los valores de entrada para analizar el problema.

C) LA REGLA DE BARROWEjemplo 10.3; INTEGRAL.XLS [Barrow]Es una ampliación un poco complicada de la HC anterior. El usuario define el polígono [f(x)] e introduce los extremos del intervalo que determina el área bajo la curva que quiere calcular. El programa devuelve, por una parte, el área bajo la curva f(x). Por otra, evalúa la función integral, F(x), en esos extremos y calcula la resta que, evidentemente, si el programa está bien hecho, debe coincidir con el área. Además, representa el polí -gono cuya área se desea calcular y los valores de la función integral en los extremos.

Todos estos resultados requieren, si no conocimientos nuevos de EXCEL, un esfuerzo de imaginación para crear las columnas auxiliares que proporcionan el éxito. Puede el lector aplicado apar-tar los gráficos para estudiar la parte oculta, analizar su contenido e intentar de paso una simplificación que sin du-da existe.Para no complicar demasiado la HC, se recomienda en un comentario no tra-zar la poligonal por debajo del eje de

abcisas.

28

Matemáticas con EXCELCapítulo 11 SIMETRÍASApelando al mismo truco utilizado en la HC CURVA_GEN.xls vamos a conseguir que, con-forme se vayan introduciendo las coordenadas de los puntos, el gráfico devuelva simétri-cos respecto un punto ([punto]) y respecto una recta ([recta]). No nos será difícil mover el punto y la recta de simetría para analizar qué pasa con las imágenes especulares.Ejemplo 11.1 SIMETRÍAS.xls [punto]Conceptos matemáticos

Simetría con respecto a un punto. Fórmula de las coordenadas del punto medio.

Diseñar una HC que devuelva simetrías respecto a un punto. Hacer que el pun-to de simetría sea móvil.1. Copiar en un Libro vacío la HC [ida_vuelta] de CURVA_GEN.xls.2. Llamar [punto] a la HC nueva y adaptar los datos de origen del gráfico.3. Añadir las celdas de la Figura y definir las variables a, b (coordenadas del punto de simetría).4. Añadir dos columnas a la tabla secun-

daria (la que se representa y está la fuente invisible) que contengan las coordenadas del punto simétrico (recurrimos a la fórmula de las coordenadas del punto medio):5. Agregar al gráfico una nueva serie con estas dos colum-nas.6. Agregar al gráfico el punto de simetría. 7. Para comprobar cómo funciona, dibujar un polígono y mover con el ratón el punto de simetría.

Ejemplo 11.1 SIMETRÍAS.xls [recta]Conceptos matemáticos

Simetría con respecto a una recta. Algoritmo para el cálculo de las coordenadas del punto simétrico

a otro con respecto de una recta dada de la forma y=mx+b.

Diseñar una HC que devuelva simetrías respecto a un punto. Hacer que el pun-to de simetría sea móvil.

1. Copiar en la HC 2 la HC [ida_vuelta] de CURVA_GEN.xls.2. Llamar [recta] a la HC nueva y adaptar los datos de origen del gráfico.3. Añadir las celdas de la Figura, reservando una celda para el coeficiente de la pendien-

te y otra para el de la ordenada en el origen.4. Añadir dos columnas a la tabla secundaria (la que se re-presenta y está la fuente invisible) que contengan las coor-denadas del punto simétrico (para encontrar tales fórmulas,

hemos de resolver previamente el problema de manera general, un buen ejercicio para 1º de Bachillerato):x’ viene dado por:

(O7) =2*(N7+M7/$B$2-$D$2)/($B$2+1/$B$2)-M7Las coordenadas de y’ son:

(P7) =2*($B$2*(N7+M7/$B$2-$D$2)/($B$2+1/$B$2)+$D$2)-N7

5. Agregar al gráfico una nueva serie con estas dos columnas.6. Utilizar las columnas Q, R para la tabla de la recta de simetría.7. Agregar dos series nuevas al gráfico: la de los puntos simétricos y la de la recta.

29

Matemáticas con EXCEL8. Insertar dos contadores debajo de los coeficientes de la recta con rangos [-10,10] e incrementos de 0,5. Utili-zar como celdas de vínculo las B3 y D3 para definir lue-go los coeficientes en función de éstas:

(B2) =(B3-20)/2 (D2) =(D3-20)/2

9. Para comprobar cómo funciona, dibujar un polígono y mover los contadores.

30

Matemáticas con EXCELCapítulo 12 PROBABILIDADA) SIMULACIÓNALEATORIO() es una función análoga a la RAN# de las calculadoras: devuelve un número aleatorio entre 0 y 1. Combinada con la tecla F9, que calcula todas las celdas cuando es pulsada, se convierte en un útil recurso para simular cualquier tipo de situación probabi-lística.Ejemplo 12.1: PROBABILIDAD [DADOS]Herramientas EXCEL

Función ALEATORIO(). Función ENTERO. Botón autosuma. Ocultar columnas. Calcular manual y automáticamente.

Conceptos matemáticos Experimento aleatorio. Suceso aleatorio. Éxito. Parte entera de un número. Simulación de un experimento aleatorio. Probabilidad teórica y probabilidad experimental.

Diseñar una HC que simule el resultado de sumar los resultados de dos dados cuando se lanzan 1000 veces.1. Se trata de obtener un número aleatorio entre 1 y 6. Esto se consigue escribiendo:

(A1) =1+ENTERO(ALEATORIO()*6)2. Repetir en B1 (o arrastrar la orden).3. Realizar la suma (podemos utilizar el botón de autosuma).

(C1) =SUMA(A1:B1)4. Rellenar el bloque A1:C100.5. Seleccionar las columnas A, B, C y ocultarlas:

Formato / Columna / Ocultar.6. Agrupar los resultados en las filas 1 y 2:

(D1): dos(D2): =CONTAR.SI(C1:C1000;2)

7. Y así hasta doce.8. Seleccionar el rango D1:N2 y hacer una gráfico de columnas. Activamos la pestaña Ta-bla de datos de la ventana opciones de gráfico y elegimos la opción Mostrar tabla de da-tos para que aparezcan los resultados también en la parte inferior del gráfico. Le pone-mos un título: Suma de dados. Una vez terminado el gráfico, accionamos con el BS la op-ción Formato de serie de datos y en la pestaña Opciones reducimos el Ancho de rango.9. Para simular distintas series de 1000 tiradas cada una vamos a:

Herramientas / Opciones / Calcular / Cálculo / Manual / Aceptar.Cada vez que pulsemos F9, EXCEL recalculará las celdas de las columnas primeras y adecuará el gráfico a los nuevos resultados.Es claro que, teniendo en cuenta que una HC contiene más de 65000 filas, la simulación podría llevarse a extremos elevados, pero, siendo que cada vez que apretamos F9 recal-cula, parece que no merece la pena utilizar más celdas, lo que, por otra parte, ralentiza bastante el procedimiento.El mismo Libro PROBABILIDAD.xls contiene algunos ejemplos más de simulación de expe-rimentos aleatorios que pueden realizarse con el ordenador y también en clase (simulan-do con monedas, cartas, etc.) para luego comparar los resultados como hacemos en el siguiente apartado.

B) LA LEY DE LOS GRANDES NÚMEROS.La Ley de los Grandes Números, ese resultado tan intuitivo pero que tanto esfuerzo le llevó demostrar al matemático Jacobo Bernouilli, admite un acercamiento bastante inte-resante a través de una HC EXCEL.

31

Matemáticas con EXCELEjemplo 12.2: PROBABILDAD.xls [LGN]Se pretende comparar la probabilidad teórica de un suceso aleatorio (que va a ser obte-ner suma 7 al lanzar dos dados y por tanto 16,7%) con la que devuelve el ordenador y la obtenida en clase tirando, efectivamente, dos dados 80 veces.Conceptos matemáticos

Ley de los Grandes Números: intuición, experiencia y simulación. Frecuencias acumuladas.

Diseñar una HC que compare las tendencias acumuladas de un suceso anterior: la teórica, la obtenida por simulación directa y la que el ordenador proporcio-na.

1. En la columna B (B5:B12) se escriben los datos obtenidos en clase.2. En la C se calculan las frecuencias porcentuales acumuladas.

(C6) =(C5*A5+B6)/A6Observar que se está utilizando el formato porcentaje y hay que tener cuidado con los números, que no son los que se visualizan en la pantalla.3. La columna D se reserva para los resultados que emite el ordenador.Necesitamos otras tres columnas auxiliares que si-mulan, como en el ejercicio anterior, el lanzamiento de dos dados y calculan la suma. Cada 10 resultados se ha escrito una celda más que va contabilizando el número de éxitos acumulados de 10 en 10. Así, por ejemplo, en J20 se lee:

(J20) =CONTAR.SI($I$1:I20;7)Los resultados de la columna D están por tanto vin-culados con los de esta columna y, así, en D5 se lee:

(D5) =J10/A5.4. En la columna E se escribe el resultado obtenido de forma teórica. Necesitamos que aparezca en todo el rango E5:E12 para luego poder representar la recta horizontal correspondiente.5. Por fin construimos un gráfico tipo línea con las tres poligonales y con la tabla de datos en la par-te inferior del mismo.

C) TEOREMA DE BAYESUn resultado que aparece en los libros de texto de todos los niveles (universitarios y no universi-tarios) con una notación bastante críptica (al menos para la mayoría de los alumnos) es el Teorema de Bayes. Utilizando una tabla de doble entrada1 en una HC EXCEL, el proble-ma general puede simplificarse.

Ejemplo 12.3 BAYES [camisetas]Conceptos matemáticos

Tablas de doble entrada. Probabilidad condicionada.

1 “Si la gente utilizara de manera habitual una sencillísima idea procedente de la estadística, avanzaría mucho [...] en la consolidación de un enfoque más crítico [...]. La idea es una tabla de las llamadas de dos por dos [...], tan elemental que podría enseñarse a los niños pequeños y a los políticos profesionales”. Érase una vez un número. J. A. Paulos.

32

Matemáticas con EXCELDiseñar una HC que el resuelve el siguiente problema y otros muchos análo-gos:En el entrenamiento de un equipo de rugby hay 22 jugadores. 14 de ellos llevan camiseta con número, el resto no. De los que llevan número, la mitad lleva escudo y de los que no llevan número, la cuarta parte no lleva escudo.Imagínate que juegas a la gallina ciega con ellos. Debes agarrar a uno cualquiera y apostar: ¿lleva escudo o no?Ya has cazado a uno. Te quitas la venda, ves que lleva escudo, pero no le ves la espalda: ¿qué es más probable, que lleve número o que no? El planteamiento del problema no entraña ninguna dificultad técnica con EXCEL. Las cel-das sombreadas en amarillo son las que el usuario debe rellenar: las otras, a base de restas (en este caso) las calcula el programa, así como todas las probabilidades sin más que calcular los cocientes correspondientes.

33

Matemáticas con EXCELCapítulo 13 GEOMETRÍAEjemplo 13.1: ELIPSE.xls [HC1]El enfoque clásico que se da al tratamiento de las cónicas suele ser analítico y estático.En esta HC se afronta la definición de elipse de manera dinámica de tal forma que el alumno, después de meter la distancia entre focos (2c) y la distancia constante suma de los radios vectores (2a) ve cómo la curva efectivamente se genera y cómo se mantiene constante la distancia de los radios vectores. (“Elipse del jardinero”)Esto lo puede hacer con cualquier elipse, grande o pequeña, más o menos excéntrica, debido a un botón que ajusta los ejes a la curva.Además. El programa permite trazar la recta tangente que envuelve a la curva conforme ésta se va generando. De esta forma, aunque el programa no calcula los ángulos, puede apreciarse a simple vista la famosa propiedad de la elipse de que un rayo que desde un foco se refleja en el interior de una elipse siempre rebota en el otro.Desde el punto de vista de EXCEL, lo interesante del programa consiste en idear la forma en que la curva aparece (o desaparece) al ritmo que nosotros le marcamos con un conta-dor.Herramientas EXCEL

Generación de un gráfico al ritmo de un contador. Cambio del código de un botón para hacer referencia a una celda

concreta.

Conceptos matemáticos La elipse como lugar geométrico. Propiedad “efecto túnel” de la elipse. Distancia entre dos puntos. Curvas en paramétricas *.

Analicemos la HC completa y el código de las celdas secundarias:1. En las celdas de la izquierda se realiza un cálculo elemental de los parámetros, los fo-cos y la excentricidad.2. Con el contador se moverá el parámetro t, que como vamos a trabajar en paramétri-cas, va a ser el ángulo en radianes. Dado que el paso de los contadores en VB no puede ser decimal y queremos que en nuestro caso salte de 0,2 en 0,2, necesitamos la celda auxiliar N4.3. Para calcular las distancias de los radios necesitamos las coordenadas del punto en cada momento, lo que aparece en el bloque C15:D18.4. La clave reside en la columna O, la tabla de valores de t. Vemos que au-menta con incremento o,2, pero se frena si no es mayor que el valor de t. Esto se consigue fácilmente con un condicional. 5. En P y Q se escribe simplemente el valor de la abcisa y ordenada de cada punto ya generado.

6. No es difícil ver cómo las tres últimas columnas están reservadas para la representa-ción de la recta tangente.7. En la representación se han agregado hasta 6 series distintas, a saber:

serie 1: la elipse: O4:Q37serie 2: un foco: A9:B9serie 3: otro foco: A10:B10serie 4: un radio vector: C15:D16serie 5: el otro radio vector: C17:D18serie 6: la recta tangente: S4:T37.

34

Matemáticas con EXCEL8. De los dos botones el de la recta tangente es el más sencillo: lo que hace es represen-tar la recta tangente.9. El otro resulta conveniente si queremos admitir cualquier pará-metro inicial y es generalizable a otros libros. La macro que se grabó consistía en transformar los ejes a un rango de [-11,11]x[-11,11]. Se copió y se pegó en el código del botón AJUSTAR EJES. En el rango D7:E7 se escribió el máximo de los valores a y b, asegurándonos así el encaje de elipses “en vertical” y “en horizontal”.Luego volvimos al código del botón y buscamos los número 11 y –11. .MinimumScale = -11 .MaximumScale = 11Aparecían en cuatro líneas, dos para las x’s, dos para las y’es.Cambiamos por .MinimumScale = Range("E7") .MaximumScale = Range("D7")y comprobamos que funcionaba.Si no se conoce la sintaxis precisa, puede averiguarse grabando pequeñas macros, como seleccionar una celda y ver su traducción a código.

Ejemplo 13.2 RES_TRI.xls [abB](Resolución de triángulos a partir de tres datos)(...)Un poco más complicado resulta el caso en el que los datos son a, b y B, pues este pro-blema admite 0, 1 o dos soluciones. Es preciso entonces reservar una segunda columna para una eventual segunda so-lución y una celda nueva en la que se informe del número de soluciones. Así, en H3 escribiremos:(H3) =SI(b<a;SI(b<a*SENO(BB*k);"NO HAY SOLUCIÓN";SI(b=a*SENO(BB*k);"HAY UNA SOLUCIÓN";"HAY DOS SOLUCIONES"));"HAY UNA SOLUCIÓN")Y en las columnas E, F, valiéndonos del Teorema de los senos, formularemos las solucio-nes, volviendo a utilizar el condicional:

[k se define en la celda K2: =PI()/180]Un análisis gráfico de la situación podemos dar representando el lado fijo a en la horizon-tal, el ángulo B y la circunferencia de radio b. No es difícil eligiendo los ejes adecuados (que luego se camuflaran convenientemente).Por fin, un botón programado copiando una macro, nos permitirá ajustar al marco las figuras sin que aparezcan distorsionadas. Deberemos apelar a las celdas secundarias L1:M3 para transformar el códi-go y adecuar los ejes al rango conveniente.Opcionalmente, se puede cerrar el triángulo en el caso de que exista solución agregando una serie nueva al gráfico: un segmento de ori-gen y extremo, en principio, el mismo, el (0,0). El bloque donde está definida es H16:I17.

35

Matemáticas con EXCELDespués de haber pulsado el botón RESOLVER, se trata de arrastrar uno de los extremos al punto (primero uno y luego el otro en el caso de solución doble) intersección entre la semirrecta y la semicircunferencia. Esto requiere un poco de paciencia, pero así parece más completa la solución del problema.Si queremos resolver un nuevo caso deberemos añadir al principio del código del botón RESOLVER la orden de que devuelva el extremo a la posición inicial. Se consigue con una macro.

' acortar Macro' Macro grabada el 08/09/2003 por PcMiguel'' Range("H17").Select

ActiveCell.FormulaR1C1 = "0" Range("I17").Select ActiveCell.FormulaR1C1 = "0" Range("I16").Select ActiveCell.FormulaR1C1 = "0" Range("H16").Select ActiveCell.FormulaR1C1 = "0" Range("J17").Select

Ejemplo 13.4: EPI_HIPO_CICLO.xlsDesde el punto de vista de aprendizaje de EXCEL, esta HC no aporta nada nuevo salvo la inserción de hipervículos de una HC a otra.Su interés es estético y matemático.El juego del ESPIROGRAFO es, a primera vista, un simple diver-timento gráfico con el que pueden dibujarse bellas figuras si-métricas: estrellas, astroides, rosas de múltiple pétalos y mu-chas más.Consta de una corona circular dentada (tanto el borde interno como el externo) y de algunos círculos, también dentados, con pequeños agujeros en su interior a distintas distancias del centro.El juego consiste simplemente en , una vez acoplada la punta del boli a uno de los aguje-ros de uno de los círculos, hacer rodar sin deslizar el círculo por la parte interior (n-hipo-cicloide) o exterior (n-epicicloide) del anillo, que permanece fijo en el papel. De esta ma-nera pueden obtenerse sugerentes figuras; algunas se parecen, otras no.Para simular las figuras que pueden generarse basta con co-nocer las fórmulas:n-hipocicloide(R,r)x=(R-r)*COS(t)+(r/n)*COS((R-r)*t/r)y =(R-r)*SENO(t)-(r/n)*SENO((R-r)*t/r)

n-epicicloide(R,r)x=(R+r)*COS(t)-(r/n)*COS((R+r)*t/r)y =(R+r)*SENO(t)-(r/n)*SENO((R+r)*t/r)

Es conveniente definir las tres variables que aparecen, poniendo nombres distintos en cada HC. Se trata de insertar un gráfico que conserve la opción de autoescalar para que las curvas aparezcan siempre a la vista del usuario. El resto es, combinando parámetros, “dibujar” y, opcionalmente pensar.Las dos cuestiones que se proponen al final son sólo unas de muchas que pueden plan-tearse. Un estudio completo de estas curvas supone una investigación muy interesante.Los hipervínculos de hoja a hoja se realizan después de haber cambiado el nombre a las mismas entrando en la opción Marcadores.

36

Matemáticas con EXCEL

Ejemplo 13.5: ESPIRALES.xlsPara acabar, otra HC de alto contenido es-tético. La novedad es que se ha conseguido simular con un contador y un condicional (igual que en ELIPSE.xls) la generación de 6 espirales cuyas ecuaciones, inicialmente en polares, hemos de transformar, de manera sencillísima, en paramétricas.

37

Matemáticas con EXCEL

Capítulo 14 OTRAS UTILIDADES

EXCEL puede ampliar su carácter de HC para convertirse en una sencilla base de datos, sin duda muchísimo menos potente que ACCES, pero suficiente en muchos casos y, so-bre todo, de manejo elemental.

Ejemplo 14 .1:CCAA.xlsHerramientas EXCEL

EXCEL como Base de Datos. Autofiltro.

Supongamos una tabla de cuatro columnas de las Comunidades Autónomas de España. Una vez introducidos (o copiados) los datos, seleccionamos la prime-ra fila

Datos / Filtro / Autofiltro.Aparecerá en la cabecera de cada columna una flechita desplegable que nos permitirá realizar filtros varios, incluso con dos criterios dis-tintos y con las funciones lógicas o / y. Para volver a mostrar todos los datos basta desplegar de nuevo y pulsar Mostrar.Muchos son los casos en los que no se requiere más potencia (ges-tión de una biblioteca, clientes de una empresa, etc.)

38