Ejemplo 3: utilizando un filtro automático, seleccione los estudiantes que estudian en el grupo No. 5433 con un apellido que comienza con la letra C.
Secuencia de acciones
1. Copie la base de datos (Fig. 30) a la Hoja 3.
2. Apellido.
3. Seleccione un elemento de la listaFiltros de texto → Filtro personalizado. En la ventana que aparece Autofiltro personalizado seleccione el criterio de selección comienza con , ingrese en el campo opuesto la letra deseada(comprueba que el diseño sea ruso). Haga clic en Aceptar.
4. Abrir lista desplegable en columna Grupo no.
5. Seleccione el número deseado.
Filtrar registros de bases de datos usando un filtro avanzado
Filtro avanzado le permite buscar cadenas utilizando criterios más complejos en comparación con los autofiltros personalizados. El filtro avanzado utiliza una variedad de criterios para filtrar datos.
Cuando se utiliza un filtro avanzado, los nombres de las columnas en las que se establecen las condiciones se copian debajo de la tabla de origen. Los criterios de selección se ingresan debajo de los nombres de las columnas. Después de aplicar un filtro, solo se pueden mostrar en la pantalla aquellas filas que cumplen con los criterios especificados y los datos filtrados se pueden copiar a otra hoja o a otra área de la misma hoja de trabajo.
Ejemplo 4: Seleccionar todos los estudiantes del grupo No. 5433 cuyo puntaje promedio sea mayor o igual a 4.5.
Secuencia de acciones
1. Copie la base de datos (Fig. 30) a la Hoja 4.
2. Copiar nombres de columnas Número de grupo y puntuación media
al área debajo de la tabla original. Introduzca los criterios de selección requeridos debajo de los nombres de las columnas (Fig. 32)
Arroz. 32. Ventana de Excel con filtro avanzado
2. En la pestaña Datos en la barra de herramientas Ordenar
y filtrar seleccione Avanzado. Aparecerá un cuadro de diálogo (Fig. 33), en el que se indican los rangos de datos.
Arroz. 33. Ventana de filtro avanzado
En el campo de entrada Gama original especifica el intervalo que contiene la base de datos de origen. En nuestro caso se destaca el rango de celdas desde A1 hasta I9.
En el campo de entrada Rango de condiciones Se resalta el rango de celdas de la hoja de trabajo que contiene los criterios requeridos (C12:D13).
En el campo de entrada Coloque el resultado en el rango indica el intervalo en el que se copian las líneas que satisfacen los criterios
teriam. En nuestro caso, se indica la celda debajo del área de criterios, por ejemplo A16. Este campo solo está disponible cuando se selecciona el botón de opción Copia el resultado a otra ubicación..
Caja Sólo entradas únicas diseñado para mostrar sólo líneas que no se repiten.
La tabla resultante que satisface los criterios de filtrado se muestra en la Fig. 34.
Arroz. 34. Ventana de Excel con resultados de filtrado.
1. Cree su propia base de datos, cuyo número de registros debe ser al menos 15 y el número de columnas debe ser al menos 6. Por ejemplo, una base de datos Listado de clientes (Fig. 35).
2. Aplique tres autofiltros a la base de datos (en hojas separadas). El número de criterios debe ser al menos dos.
3. Aplique tres filtros avanzados a los registros de la base de datos, cada uno de los cuales debe contener al menos dos criterios. Coloque todos los filtros avanzados en una hoja debajo de la tabla original.
Arroz. 35. Ventana de Excel con base de datos Lista de clientes
TRABAJO DE LABORATORIO No. 5
Diferenciación numérica y análisis de funciones simples.
Objeto del trabajo: Investigar una función hasta el extremo, aprender a determinar el punto crítico.
De un curso de matemáticas sabemos que la fórmula para la derivada en vista general se ve así:
f "(x)=lím |
||
Δx 0 |
||
donde Δx es el incremento del argumento; x es un número que tiende a cero. Usando la derivada, puedes determinar los puntos críticos de una función: mínimos, máximos o inflexiones. Si el valor de la derivada de una función en algún valor de x es igual a cero, entonces en este valor de x la función tiene un punto crítico.
Ejemplo 1: La función f x = x 2 + 2x 3 está dada en el intervalo x 5;5. Investiga el comportamiento de la función f(x).
Secuencia de acciones
1. Sea Δx = 0,00001. En la celda A1 ingrese: šDx=Ÿ (Fig. 36). Seleccione la letra D, haga clic derecho en la letra seleccionada, seleccione Formato de celdas. En la pestaña Fuente, seleccione la fuente Símbolo. La letra D se convertirá en la letra griega ѓў. La alineación en una celda se puede hacer hacia la derecha. En la celda B1, ingrese el valor 0.00001.
2. En las celdas A2 a F2, cree un encabezado de tabla, como se muestra en la Fig. 36.
3. La columna A, a partir de la tercera fila, contendrá los valores de x. En las celdas A3 a A13, ingrese valores de –5 a 5.
4. En la celda B3, escriba la fórmula =A3^2+2*A3-3 y estírela hasta el valor final x (hasta la línea 13).
5. Para determinar la derivada de una función y calcular sus valores en un intervalo dado, es necesario hacer una intermedia
cálculos precisos. En la celda C3, ingrese la fórmula para la suma del argumento x y su incremento Δx. La fórmula se ve así: =A3+$B$1. Extienda su valor al valor final del argumento x .
Arroz. 36. Ventana de Excel con un estudio del comportamiento de una función.
6. En la celda D3 escribe la fórmula =C3^2+2*C3-3, que calcula el valor de la función f a partir del argumento x Δx. Extienda el valor resultante al valor final del argumento.
7. En la celda E3, escribe la fórmula derivada (1), teniendo en cuenta que los valores de f x están en B3, y los valores de f x + Δx están en D3.
La fórmula se verá así: =(D3-B3)/$B$1.
8. Determinar el comportamiento de la función en un intervalo determinado (aumenta, disminuye o tiene un punto crítico). Para hacer esto, debe escribir de forma independiente una fórmula en la celda F3 para determinar el comportamiento de la función. La fórmula contiene tres condiciones:
f"(x)< 0 |
– la función disminuye; |
||
f" (x) > 0 |
– la función aumenta; |
||
f"(x)= 0 |
– hay un punto crítico*. |
9. Dibuje gráficas basadas en los valores de f x y f" (x). La gráfica (Fig.37) muestra que si el valor de la derivada de una función es cero, entonces en este punto la función tiene un punto crítico. .
* Debido a un error de cálculo demasiado grande, el valor de f"(x) puede no ser igual a 0. Pero aún es necesario describir esta situación.
Arroz. 37. Diagrama para estudiar el comportamiento de una función.
Tareas para el trabajo independiente.
La función f(x) está dada en el intervalo x. Investiga el comportamiento de la función f(x). Construir gráficos.
2x2 |
X[4;4] |
||||||||
X[5;5] |
|||||||||
2x+2 |
|||||||||
f(x)= x3 |
3x2 |
2 , x [ 2 ;4 ] |
|||||||
f(x)=x |
X[2;3] |
||||||||
x2 + 7 |
|||||||||
TRABAJO DE LABORATORIO No. 6
Construir una tangente a la gráfica de una función.
Objeto del trabajo: Dominar el cálculo de los valores de la ecuación de una tangente a la gráfica de una función en el punto x 0.
Ecuación de la tangente a la gráfica de la función y = f(x) en un punto
Ejemplo 1: La función y = x 2 + 2x 3 está dada en el intervalo x [ 5; 5]. Construye una tangente a la gráfica de esta función en el punto x 0 = 1.
Secuencia de acciones:
1. Diferenciar esta función numéricamente (ver. Trabajo de laboratorio N° 5). La tabla de datos iniciales se muestra en la Fig. 38.
Arroz. 38. Tabla de datos iniciales.
2. Determine la ubicación de x, x 0, f(x 0) y f" (x 0) en la tabla. Obviamente, x serán los valores de
columna A, comenzando desde la tercera línea (Fig. 38). Si x 0 = 1, entonces la celda A9 actuará como x 0. En consecuencia, el valor de la función f en el punto x 0 está en la celda B9, y el valor de f" (x 0)
– en la celda E9.
3. En la columna F se calcula la ecuación de la tangente a la gráfica de la función f(x). Al calcular la ecuación (1), es necesario que los valores de x 0, f(x 0) y f" (x 0) no cambien. Por lo tanto, al escribir
Para determinar las direcciones de las celdas A9, B9 y E9, debe utilizar referencias absolutas a estas celdas. Las celdas se fijan mediante el signo š$Ÿ. Las celdas se verán así: $A$9 , $B$9 y $E$9 .
Arroz. 39. Gráfica de la función f(x) y la tangente a la gráfica en el punto x=1
Tareas para el trabajo independiente.
La función f(x) está definida en el intervalo x. Calcula la ecuación tangente. Construye una tangente a la gráfica de la función en un punto dado.
2x2 |
X[ 4 ;4 ] , x0 = 1 |
|||||||||
X[5;5], x0 |
||||||||||
2x+2 |
||||||||||
f(x)= x3 |
3x2 |
2 , x [ 2 ;4 ] , x0 = 0 |
||||||||
f(x)=x |
X[2;3],x0 |
|||||||||
x2 + 7 |
||||||||||
1. Vedeneeva, E. A. Funciones y fórmulas Excel 2007. Biblioteca de usuario / E. A. Vedeneeva. – San Petersburgo: Peter, 2008. – 384 p.
2. Sviridova, M. Yu. Hojas de cálculo de Excel / M. Yu. – M.: Academia, 2008. – 144 p.
3. Serogodsky, V.V. Gráficos, cálculos y análisis de datos.
V Excel 2007 / V. V. Serogodsky, R. G. Prokdi, D. A. Kozlov, A. Yu. – M.: Ciencia y Tecnología, 2009. – 336 p.
Se sabe que mediante métodos numéricos aproximados se puede calcular la derivada de una función en un punto dado utilizando la fórmula de diferencias finitas. La expresión para calcular la derivada de una función de una variable en el punto x k escrita en diferencias finitas tiene la forma
donde Δх es un valor finito muy pequeño.
Para valores suficientemente pequeños de Δх, es posible obtener el valor de la derivada de la función en un punto con una precisión aceptable. Para calcular la derivada en MS Excel, usaremos la fórmula anterior. Veamos la tecnología para calcular la derivada usando el ejemplo..
Ejemplo 1.18 Encuentra la derivada de la función y = 2x 3 + x 2 en el punto x=3. Tenga en cuenta que la derivada de la función reducida en el punto x = 3, calculada mediante el método analítico, es igual a 60; necesitaremos este valor para verificar el resultado obtenido mediante el cálculo mediante el método numérico.
El problema de calcular la derivada en un procesador de tabla se puede solucionar de dos formas.
Solución de primera vía
Ingresemos la fórmula para el lado derecho de la relación funcional dada en una celda de la hoja de trabajo, por ejemplo en la celda B2, como se muestra en la figura, haciendo referencia a la celda donde se ubicará el valor de x, por ejemplo A2,
2*A2^3+A2^2.
Establezcamos una vecindad del punto x = 3 de un tamaño suficientemente pequeño, por ejemplo, el valor de la izquierda es x k = 2,9999999 y el valor de la derecha es x k +1 = 3,00000001, e ingresamos estos valores en las celdas. A2 y A3, respectivamente. En la celda C2 ingresamos la fórmula para calcular la derivada =(B3-B2)/(A3-A2).
Como resultado del cálculo, el valor aproximado de la derivada se mostrará en la celda C2. función dada en el punto x=3, cuyo valor es 60, que corresponde al resultado obtenido analíticamente (Fig. 1.24).
Solución de segunda vía
Ingresemos el valor dado del argumento igual a 3 en la celda A2 de la hoja de trabajo, en la celda B2 indicaremos un incremento bastante pequeño del argumento - (1E - 9) y en la celda C2 ingresaremos una fórmula para calculando la derivada
=(2*(A2+B2) ^ 3+(A2+B2) ^ 2-(2*A2 ^ 3+A2 ^ 2))/B2.
Después de presionar la tecla
Como puedes ver, el resultado obtenido es el mismo que con el primer método. El segundo método dado es más preferible en los casos en que necesita construir una tabla de valores de la derivada de una función para valores dados del argumento.
Cálculo de extremos locales de una función.
Recuerde que la función Y=f(x) tiene un extremo en el valor x = x k si la derivada de la función en este punto es igual a cero.
Si la función f(x) es continua en el intervalo [a, b] y tiene un extremo local dentro de este intervalo, entonces se puede encontrar usando el complemento de Excel Buscar una solución.
Consideremos la secuencia para encontrar el extremo de una función usando el ejemplo.
Ejemplo 1.19 Se da una función continua y = x 2 + x + 2. Se requiere encontrar su extremo (valor mínimo) en el segmento [-2; 2].
Solución
En la celda A3 de la hoja de trabajo, ingrese cualquier número que pertenezca al segmento dado; esta celda contendrá el valor x.
En la celda B3 ingresamos una fórmula que determina la dependencia funcional dada. En lugar de la variable x en esta fórmula debería haber una referencia a la celda A3: =A3^2+A3+2.
Ejecutemos el comando de menú Servicio/Buscar solución.
En el cuadro de diálogo Buscar una solución que se abre, en el campo Establecer celda de destino, indique la dirección de la celda que contiene la fórmula (B3), configure el interruptor Valor mínimo y en el campo Cambiar celda, indique la dirección de la celda que contiene la variable x-A3.
Agreguemos dos restricciones al campo correspondiente: A3 > = - 2 y A3<=2 (рис. 1.25).
 |
Haga clic en el botón Opciones y en el cuadro de diálogo que se abre, los parámetros de búsqueda de solución establecerán el error de cálculo relativo y el número máximo de iteraciones.
Haga clic en el botón Ejecutar. En la celda A3 se calculará el valor del argumento x de la función, en el que toma el valor mínimo, y en la celda B3, el valor mínimo de la función.
Como resultado de los cálculos en la celda A3, se obtendrá el valor de la variable independiente, en el cual la función toma el valor más pequeño -0,5, y en la celda B3, el valor mínimo igual a 1,75.
Tracemos una gráfica de la función dada y asegurémonos de que la solución a la ecuación sea correcta y encontrada.
Nota. En un caso particular, al encontrar un extremo local utilizando la tecnología considerada, es posible obtener un valor que no es un extremo, sino simplemente el mínimo o máximo de la función en un rango dado de cambios en el argumento.
Por lo tanto, es necesaria una verificación adicional, es decir calcular la derivada de una función en el punto encontrado.
Usando la tecnología dada para calcular numéricamente la derivada de una función en un punto dado, comprobaremos si el punto encontrado x = -0,5 es el punto extremo de la función y = x 2 + x + 2. La solución se muestra en la cifra.
Como puede ver, la derivada en el punto encontrado es igual a cero, por lo tanto, el valor encontrado de la función es su valor extremo.
Ejemplo 1.20 Necesita encontrar valores de argumentos en el rango [-1; 1], para el cual la función y = x 2 + x + 2 tiene extremos.
Solución
Tabulamos la función dada en incrementos de 0,2.
Utilizando el segundo de los métodos anteriores para calcular la derivada, calculamos los valores de la función y = f(x + dx).
Calculemos los valores derivados para cada valor de tabla del argumento.
Analizando los valores obtenidos de las derivadas de la función en puntos, encontramos que la derivada cambia de signo en el intervalo de valores de los argumentos (-0,6; -0,4), por lo tanto, hay un punto extremo en este intervalo. Además, observe que el signo de la derivada cambia de menos a más, por lo tanto, el punto extremo es el mínimo de la función.
Usando la herramienta Selección de parámetros o Buscar soluciones para resolver la ecuación Y(x) = 0
 |
En relación con x, calculamos el valor exacto del argumento en el que la función original toma un valor extra (-0,5) (figura 1.26).
El valor resultante de la derivada de la función en estudio en punto x = -0,5 es igual a cero, por lo tanto, en este punto la función tiene un extremo.
La diferenciación gráfica comienza trazando una gráfica de función basada en valores dados. En un estudio experimental, dicho gráfico se obtiene utilizando instrumentos de registro. A continuación se dibujan tangentes a la curva en posiciones fijas y se calculan los valores de la derivada respecto de la tangente del ángulo formado por la tangente con el eje de abscisas.
En la figura. 5.8, A Se muestra la curva obtenida experimentalmente en la instalación (Fig. 5.6). La determinación de la aceleración angular (la función deseada) se realiza mediante diferenciación gráfica según la relación:
(5.19)
Tangente del ángulo de inclinación de la tangente a la curva en algún punto i representado como una proporción de segmentos, donde A– segmento de integración seleccionado (Fig. 5.8, b)
Después de sustituir esta relación en la relación (5.19), obtenemos
¿Dónde está la ordenada de la gráfica de demanda de aceleración angular?
Escala del gráfico deseado; Unidades SI: = mm; = mm/(rad s -2).
La gráfica de la función se construye utilizando los valores de ordenadas encontrados para varias posiciones. Los puntos de la curva se conectan a mano con una línea suave y luego se delinean mediante un patrón.
La diferenciación gráfica utilizando el método tangente considerado tiene una precisión relativamente baja. Se obtiene una mayor precisión mediante la diferenciación gráfica utilizando el método de cuerdas (Fig. 5.8, V Y GRAMO).
 |
En una curva determinada se marcan varios puntos. 1 ", 2 ", 3" , que están conectados por cuerdas, es decir reemplace la curva dada con una polilínea. Se acepta el siguiente supuesto: el ángulo de inclinación de las tangentes en los puntos ubicados en el medio de cada tramo de la curva es igual al ángulo de inclinación de la cuerda correspondiente. Esta suposición introduce algún error, pero sólo se aplica a este punto. Estos errores no cuadran, lo que garantiza una precisión aceptable del método.
Las construcciones restantes son similares a las descritas anteriormente en diferenciación gráfica utilizando el método tangente. Seleccione un segmento (mm); conducir rayos inclinados en ángulos 
hasta la intersección con el eje de ordenadas en los puntos 1
", 2
", 3
"..., que se trasladan a las ordenadas trazadas en medio de cada uno de los intervalos. Los puntos resultantes 1
*, 2
*, 3
* son puntos de la función requerida 
.
Las escalas a lo largo de los ejes de coordenadas con este método de construcción están relacionadas por la misma relación (5.21), que se derivó para el caso de diferenciación gráfica mediante el método tangente.
Diferenciación de una función f(x), especificado (o calculado) en forma de una matriz de números, se realiza mediante el método de diferenciación numérica utilizando una computadora.
Cuanto menor sea el paso en la matriz de números, con mayor precisión podrá calcular el valor de la derivada de la función en este intervalo
La resolución de muchos problemas de ingeniería a menudo requiere el cálculo de derivadas. Cuando hay una fórmula que describe el proceso, no hay dificultades: tomamos la fórmula y calculamos la derivada, como nos enseñaron en la escuela, encontramos los valores de la derivada en diferentes puntos y listo. Probablemente la única dificultad sea recordar cómo calcular las derivadas. Pero ¿qué pasa si sólo tenemos unos pocos cientos o miles de filas de datos y ninguna fórmula? En la mayoría de los casos, esto es exactamente lo que sucede en la práctica. Sugiero dos maneras.
La primera es que aproximamos nuestro conjunto de puntos con una función estándar de Excel, es decir, seleccionamos la función que mejor se ajuste a nuestros puntos (en Excel esta es una función lineal, logarítmica, exponencial, polinómica y potencia). El segundo método es la diferenciación numérica, para la cual solo necesitamos la capacidad de ingresar fórmulas.
Recordemos qué es una derivada en general:
La derivada de una función f (x) en un punto x es el límite de la relación entre el incremento Δf de la función en el punto x y el incremento Δx del argumento cuando este último tiende a cero:
Entonces usaremos este conocimiento: simplemente tomaremos valores muy pequeños del incremento del argumento para calcular la derivada, es decir Δx.
Para encontrar el valor aproximado de la derivada en los puntos que necesitamos (y nuestros puntos son diferentes valores del grado de deformación ε), podemos hacer esto. Miremos nuevamente la definición de derivada y veamos que cuando usamos pequeños incrementos del argumento Δε (es decir, pequeños incrementos en el grado de deformación que se registran durante la prueba), podemos reemplazar el valor de la derivada real en el punto x. 0 (f'(x 0)=dy/dx (x 0)) a la relación Δy/Δx=(f (x 0 + Δx) – f (x 0))/Δx.
Entonces esto es lo que sucede:
f’(x 0) ≈(f (x 0 + Δx) – f (x 0))/Δx (1)
Para calcular esta derivada en cada punto, realizamos cálculos utilizando dos puntos vecinos: el primero con la coordenada ε 0 a lo largo del eje horizontal, y el segundo con la coordenada x 0 + Δx, es decir uno es la derivada en la que calculamos y el de la derecha. La derivada calculada de esta manera se llama derivada de diferencia hacia la derecha (hacia adelante) en pasosΔ incógnita.
Podemos hacer lo contrario, tomando los otros dos puntos vecinos: x 0 - Δx y x 0, es decir, el que nos interesa y el de la izquierda. Obtenemos la fórmula para calcular. derivada de diferencia hacia la izquierda (hacia atrás) con un paso -Δ incógnita.
f’(x 0) ≈(f (x 0) – f (x 0 – Δx))/Δx (2)
Las fórmulas anteriores eran “izquierda” y “derecha”, pero existe otra fórmula que permite calcular derivada de diferencia central con un paso de 2 Δx, y que Se utiliza con mayor frecuencia para la diferenciación numérica:
f’(x 0) ≈(f (x 0 + Δx) – f (x 0 – Δx))/2Δx (3)
Para verificar la fórmula, considere un ejemplo simple con la función conocida y=x 3. Construyamos una tabla en Excel con dos columnas: xey, y luego construyamos un gráfico usando los puntos disponibles.
La derivada de la función y=x 3 es y=3x 2, cuya gráfica, es decir una parábola, debemos obtenerla usando nuestras fórmulas.
Intentemos calcular los valores de la derivada de la diferencia central en los puntos x. Para esto. En la celda de la segunda fila de nuestra tabla ingresamos nuestra fórmula (3), es decir la siguiente fórmula en Excel:
Ahora construimos una gráfica usando los valores existentes de x y los valores obtenidos de la derivada en diferencias centrales:
¡Y aquí está nuestra pequeña parábola roja! ¡Entonces la fórmula funciona!
Bueno, ahora podemos pasar al problema de ingeniería específico del que hablamos al principio del artículo: encontrar el cambio en dσ/dε con una deformación creciente. La primera derivada de la curva tensión-deformación σ=f (ε) se denomina "tasa de endurecimiento por deformación" en la literatura extranjera y "coeficiente de endurecimiento" en la nuestra. Entonces, como resultado de las pruebas, tenemos una matriz de datos que consta de dos columnas: una con los valores de deformación ε y la otra con los valores de tensión σ en MPa. Tomemos como ejemplo la deformación en frío del acero 1035 o nuestro 40G (ver tabla de análogos de acero) a 20°C.
| do | Minnesota | PAG | S | Si | norte |
| 0.36 | 0.69 | 0.025 | 0.032 | 0.27 | 0.004 |
Aquí está nuestra curva en las coordenadas σ-ε de “esfuerzo real - deformación verdadera”:
Procedemos de la misma forma que en el ejemplo anterior y obtenemos esta curva:
Este es el cambio en la tasa de endurecimiento durante la deformación. Qué hacer con él es una cuestión aparte.
Además de formatear elementos de campo de celda, fila y columna, suele resultar útil utilizar varias hojas de cálculo de Excel. Para sistematizar y buscar información en un libro, conviene asignar nombres propios a los nombres de las hojas, reflejando su contenido semántico. Por ejemplo, “datos iniciales”, “resultados de cálculos”, “gráficos”, etc. Es conveniente hacer esto usando menú contextual. Haga clic derecho en la pestaña de la hoja, cambie el nombre de la hoja y haga clic
Para agregar una o más hojas nuevas, seleccione el comando Hoja en el menú Insertar. Para insertar varias hojas a la vez, debe seleccionar las pestañas del número requerido de hojas manteniendo presionada la tecla
Una operación útil para mover hojas es tomar la pestaña de la hoja con el botón izquierdo del mouse y moverla a la ubicación deseada. Si presionas
Tarea 7. Cambie el formato de toda la celda B2 a: fuente – Arial 11; ubicación: en el centro, a lo largo del borde inferior; una palabra por línea; formato de número – “0,00”; borde de celda – doble línea
2.3. Funciones integradas
Excel contiene más de 150 funciones integradas para simplificar los cálculos y el procesamiento de datos. Un ejemplo del contenido de una celda con una función: =B2+SIN(C7) , donde B2 y C7 son las direcciones de las celdas que contienen números y SIN() es el nombre de la función. Funciones de Excel más utilizadas:
SQRT(25) = 5 – calcula la raíz cuadrada del número (25) RADIANS(30) = 0,5 – convierte 30 grados a radianes ENTERO(8,7) = 8 – redondea al entero inferior más cercano REMAIN(-3,2 ) = 1 – deja un resto al dividir el número (-3) por
divisor(2). El resultado tiene el signo divisor. SI(E4>0.2;”adicional”;”error”)– si el número en la celda E4 es inferior a 0,2,
luego Excel devuelve "extra" (verdadero); en caso contrario, "error" (falso).
En una fórmula, las funciones se pueden anidar unas dentro de otras, pero no más de 8 veces.
Cuando se utiliza una función, lo principal es definir la función en sí y su argumento. El argumento, por regla general, especifica la dirección de la celda en la que se registra la información.
Puede definir una función escribiendo texto (iconos, números, etc.) en la celda deseada, o use Asistente de funciones. Aquí, para facilitar la búsqueda, todas las funciones se dividen en categorías: matemáticas, estadísticas, lógicas y otras. Dentro de cada categoría están ordenados alfabéticamente.
Asistente de funciones llamado por comando de menú Insertar, Función
o pulsando el icono (f x ). En la primera ventana del Asistente de funciones que aparece (Fig. 4), determine la categoría y el nombre de la función específica, haga clic en
Arroz. 4. Vista de la ventana del Asistente de funciones
Arroz. 5. Ventana para especificar los argumentos de la función seleccionada.
Tarea 8. Encuentra el valor promedio de una serie de números: 2,5; 2,9; 1,8; 3.4; 6.1;
1,0; 4,4.
Solución . Ingrese números en las celdas, por ejemplo, C2:C8. Selecciona la celda C9, en la que escribimos la función =PROMEDIO(C2:C8), presiona
Tarea 9. Utilizando la función SI lógica condicional, cree una fórmula para cambiar el nombre de los números impares a "otoño" y a los números pares como "primavera".
Solución . Seleccionamos una columna para ingresar los datos iniciales: números pares (impares), por ejemplo, A. En la celda B3 escribimos la fórmula =SI(REM(A3,2)=0,"peso","eje"). Copiando la celda B3 a lo largo de la columna B, obtenemos los resultados del análisis de los números escritos en la columna A. Los resultados de la resolución del problema se presentan en la Fig. 6.
Arroz. 6. Solución al problema número 9.
Problema 10. Calcular el valor de la función y = x3 + senx – 4ex para x = 1,58.
Solución . Coloquemos los datos en las celdas A2 – x, B2 – y. La solución al problema se muestra en la Fig. 7 en forma numérica a la izquierda y en forma de fórmula a la derecha. Al resolver este problema, debe prestar atención a llamar a las funciones SIN y exponente para ingresar un argumento (ver Fig. 8).
Fig.7. Solución al problema número 10.
Fig.8. Ventana para ingresar el argumento de las funciones SIN y EXP
Problema 11. Cree un modelo matemático del problema en Excel para calcular la función y= 1/ ((x- 3) · (x+ 4)), para los valores x= 3 y y= -4 mostrar valores numéricos “indefinidos” de la función – en otros casos.
Problema 12. Crear un modelo matemático del problema en Excel: 12.1. para cálculos con raíces
a) √ x3 y2 z / √ x z ; b) (z · √ z)2 ; c) 3 √ x2 · 3 √ x ; d) √ 5 x 5 3-1 / √ 20 x 3-1
12.2. para cálculos geométricos a) determinar los ángulos de un triángulo rectángulo, si x es el cateto e y es la hipotenusa;
b) determine la distancia entre dos puntos en el sistema de coordenadas cartesiano XYZ usando la fórmula
d = (x2 − x1 )2 + (y2 − y1 )2 + (z2 − z1 )2
c) determine la distancia desde el punto (x 0 ,y 0 ) a la recta a x + b y + c = 0 usando la fórmula
d = a x0 +b y0 +c / √ (a2 +b2 )
d) determinar el área del triángulo a partir de las coordenadas de los vértices usando la fórmula
S = 1 2 [ (x1 − x3 )(y2 − y3 ) − (x2 − x3 )(y1 − y3 )]
3. Resolver problemas usando fórmulas y funciones.
En realidad, existen muchos problemas que se pueden resolver con éxito utilizando fórmulas y funciones de Excel. Consideremos los problemas que se resuelven con mayor frecuencia en la práctica utilizando hojas de cálculo: ecuaciones lineales y sus sistemas, cálculo de valores numéricos de derivadas e integrales definidas.
La derivada de una función y = f(x) es la relación entre su incremento ∆y y el incremento correspondiente ∆x del argumento, cuando
∆x→ 0
y = f (x + x) − f (x)
Problema .13. Encuentra la derivada de la función y = 2x 3 + x 2 en el punto x=3.
Solución. La derivada calculada por el método analítico es 60. Calcularemos la derivada en Excel usando la fórmula (1). Para ello, realizamos la siguiente secuencia de acciones:
· Designemos las columnas: X – argumentos de función, Y – valores de función, Y ` – derivada de función (Fig. 9).
· Tabular la función en una vecindad del punto. x = 3 con un pequeño paso, por ejemplo, 0.001, ingresamos los resultados en la columna X.
Arroz. 9. Tabla para calcular la derivada de una función.
· En la celda B2, ingresa la fórmula para calcular la función =2*A2^3+A2^2.
· Copiemos la fórmula a la línea. 7, obtenemos los valores de la función en las tabulaciones de argumentos.
· En la celda C2, ingresa la fórmula para calcular la derivada =(B3-B2)/ (A3-A2).
· Copiemos la fórmula a la línea. 6, obtenemos los valores de las derivadas en los puntos de tabulación del argumento.
Para el valor x = 3, la derivada de la función es igual al valor 60,019, que se aproxima al valor calculado analíticamente.
método trapezoidal. En el método trapezoidal, la región de integración se divide en segmentos con un cierto paso y se calcula el área bajo la gráfica de la función en cada segmento. área igual trapecios. Entonces la fórmula de cálculo toma la siguiente forma
S norte = ∫ f (u) du ≈ h norte ∑ − 1 [ f (a + h i) + f (a + h (i + 1)) ] (2), |
|
2 yo = 0 |
|
donde h= (b- a)/ N – paso de partición; N – número de puntos de división.
Para aumentar la precisión, se duplica el número de puntos de partición y se vuelve a calcular la integral. La fragmentación del intervalo inicial se detiene cuando se alcanza la precisión requerida:
integral realizamos las siguientes acciones:
– elija N= 5, en la celda F2 calculamos el paso de partición h (Fig. 10);
Arroz. 10. Cálculo integral definida
· En la primera columna Y anotamos el número del intervalo i;
· En la celda B2, escribe la fórmula =3*(2+F2*A2)^2 para calcular el primer término de la fórmula (2);
· En la celda C2, escribe la fórmula =3*(2+F2*(A2+1))^2 para calcular el segundo término;
· "Estirar" celdas con fórmulas para 4 filas por columnas;
· En la celda C7 escribimos la fórmula y calculamos la suma de los términos,
· En la celda C8, escribe la fórmula y calcula SN el valor deseado de la integral definida 19.02 (el valor SN obtenido analíticamente
19).
Tarea. 15. Calcula la integral definida:
1. Y = ∫ 2 x re x |
2. Y = ∫ 2x3dx |
−1 |
|
2π |
|||||||||||||||
Y = ∫ 2sin(x )dx |
Y = ∫ x2dx |
||||||||||||||
−2 |
|||||||||||||||
Y = ∫ |
Y = ∫ |
||||||||||||||
3x-2 |
(2x + 1) 3 |
||||||||||||||
x+3 |
|||||||||||||||
Y = ∫cos |
Y = ∫ |
||||||||||||||
x2 + 4 |
|||||||||||||||
3.2. Solución ecuaciones lineales
Ecuaciones lineales en Excel se puede resolver usando la función Selección de parámetro. Al seleccionar un parámetro, el valor de la celda (parámetro) que influye cambia hasta que la fórmula que depende de esa celda devuelve el valor especificado.
Consideremos el procedimiento para buscar un parámetro en ejemplo sencillo Se desconocen las soluciones de una ecuación lineal con uno.
Problema 16. Resuelve la ecuación 10 x - 10 / x = 15 .
Solución. Para el valor deseado del parámetro – x, seleccione la celda A3. Ingresemos en esta celda cualquier número que se encuentre en el dominio de definición de la función (en nuestro ejemplo, este número no puede ser igual a cero). Que sean 3. Este valor se utilizará como valor inicial. En la celda, por ejemplo, B3, de acuerdo con la ecuación anterior, ingrese la fórmula =10*A3-10/A3. Como resultado de una serie de cálculos utilizando esta fórmula, se seleccionará el valor del parámetro deseado. Ahora en el menú Herramientas, seleccionando el comando Selección de parámetro, Iniciemos la función de búsqueda de parámetros (Fig. 11, a). Ingresemos los parámetros de búsqueda:
· En el campo Establecer en celda Ingresemos una referencia absoluta a la celda $B$3 que contiene la fórmula.
· En el campo Valor, ingrese el resultado deseado 15.
· En el campo Cambiar el valor de una celda ingrese un enlace a la celda A3 que contiene el valor seleccionado y haga clic en
Al finalizar la función Selección de parámetros aparecerá una ventana en la pantalla Resultado de la selección de parámetros, que mostrará los resultados de la búsqueda. El parámetro encontrado 2.000025 aparecerá en la celda A3, que estaba reservada para él.
Preste atención al hecho de que en nuestro ejemplo la ecuación tiene dos soluciones, pero solo se ha seleccionado un parámetro. Esto ocurre porque el parámetro se modifica solo hasta que se devuelve el valor requerido. El primer argumento encontrado de esta manera se nos devuelve como resultado de la búsqueda. si como
indique el valor inicial en nuestro ejemplo -3, luego se encontrará la segunda solución a la ecuación: -0,5.
Figura 11. Solución de la ecuación: a - entrada de datos, b - resultado de la solución
Problema 17. Resolver ecuaciones |
||||
5x/ 9- 8= 747x/ 12 |
(2x+ 2)/ 0,5= 6x |
|||
0,5 (2x- 1)+x/ 3= 1/6 |
7 (4x-6)+ 3 (7-8x)= 1 |
|||
sistema lineal |
ecuaciones |
se puede resolver de diferentes maneras |
||
métodos: sustitución, suma y resta de ecuaciones, utilizando matrices. Consideremos un método para resolver el sistema canónico de ecuaciones lineales (3) usando matrices.
a1 x + a2 y + b1 = 0
a3 x + a4 y + b2 =0
Se sabe que un sistema de ecuaciones lineales en representación matricial se escribe en la forma:
donde A es una matriz de coeficientes, X es un vector, una columna de incógnitas,
B es el vector columna de términos libres. La solución a tal sistema.
escrito en la forma
X = A-1 B,
donde A -1 es la matriz inversa de A. Esto se desprende del hecho de que al resolver ecuaciones matriciales para X, la matriz identidad E debe permanecer. Multiplicando por la izquierda ambos lados de la ecuación AX = B por A -1, obtenemos la solución sistema lineal ecuaciones.
Problema 18. Resolver un sistema de ecuaciones lineales.
Solución. Para un sistema dado de ecuaciones lineales, los valores de la matriz y el vector columna correspondientes tienen la forma:
Para resolver el problema, realicemos los siguientes pasos:
· A2:B3 y escribe en él los elementos de la matriz A.
· Seleccionemos un bloque de celdas, por ejemplo, C2:C3 y escribe los elementos de la matriz B en él.
· Seleccionemos un bloque de celdas, por ejemplo, D2:D3 para colocar el resultado de resolver un sistema de ecuaciones.
· en la celda D2 ingrese la fórmula = MULP(MOBR(A2:B3),C2:C3).
La biblioteca de Excel en la sección de funciones matemáticas contiene funciones para realizar operaciones con matrices. En concreto, estas son las funciones:
Los parámetros de estas funciones pueden ser enlaces de direcciones a matrices que contienen valores de matriz o nombres de rango y expresiones.
Por ejemplo, MOBR (A1: B2) o MOPR (matriz_1).
· Digamos a Excel que se está realizando una operación sobre arrays presionando la combinación de teclas
4. Resolver problemas de optimización
Muchos problemas de previsión, diseño y fabricación pueden reducirse a una clase amplia de problemas de optimización. Tales tareas son, por ejemplo: maximizar la producción de bienes con restricciones sobre las materias primas para la producción de estos bienes; elaboración de personal para lograr mejores resultados al menor costo; minimizar el costo de transporte de mercancías; lograr la calidad especificada de la aleación; determinar las dimensiones de un determinado contenedor, teniendo en cuenta el costo del material para lograr el volumen máximo; varios
tareas que incluyen variables aleatorias y otros problemas de asignación óptima de recursos y diseño óptimo.
Los problemas de este tipo se pueden resolver en EXCEL utilizando la herramienta de búsqueda de soluciones, que se encuentra en el menú Herramientas. La formulación de tales problemas puede ser un sistema de ecuaciones con varias incógnitas y un conjunto de restricciones en las soluciones. Por tanto, la solución del problema debe comenzar con la construcción de un modelo apropiado. Conozcamos estos comandos usando un ejemplo.
Problema 20. Supongamos que decidimos producir dos tipos de lentes A y B. Una lente de tipo A consta de 3 componentes de lente, el tipo B, de 4. En una semana se pueden producir un máximo de 1.800 lentes. Se necesitan 15 minutos para montar una lente tipo A y 30 minutos para una lente tipo B. Semana laboral para 4 empleados es de 160 horas. ¿Cuántas lentes A y B se deben producir para obtener el máximo beneficio? Si una lente del tipo A cuesta 3500 rublos, la del tipo B cuesta 4800 rublos.
Solución. Para resolver este problema, es necesario elaborar y completar una tabla de acuerdo con la Fig. 12:
· cambiar el nombre de la celda B2 en x, el número de lentes de tipo A.
· Y de manera similar, cambiemos el nombre de la celda B3 a y.
Función objetivo Beneficio = 3500*x+4800*y ingrese en la celda B5. · Los costos por embalaje son iguales a =3*x+4*y, ingrese en la celda B7.
· Los costos de tiempo son iguales a =0.25*x+0.5*y, ingrese en la celda B8.
Nombre |
|||
juego completo |
|||
Costo por tiempo |
|||
Figura 12. Llenar la tabla con datos de origen
· Seleccionamos la celda B5 y seleccionamos el menú Datos, luego de lo cual activamos el comando Buscar una solución. Llenemos las celdas de esta ventana de acuerdo con la Fig. 13.
· Haga clic<Выполнить >; Si se hace correctamente, la solución será la siguiente.