Libro de cocina Excel

Capítulo 4. Matemáticas e ingeniería

Este trabajo se ha traducido utilizando IA. Agradecemos tus opiniones y comentarios: [e-mail]

Las operaciones matemáticas son una parte crucial de muchas hojas de cálculo, y Excel incluye un amplio conjunto de funciones que te ahorran la molestia de realizar engorrosos cálculos manuales.

Este capítulo te guía a través de las fórmulas matemáticas y de ingeniería de Excel, llevándote más allá de los operadores +, -, *, /, y ^. Las recetas incluyen formas de redondear números; trabajar con sumas, múltiplos y divisores; utilizar la trigonometría; calcular permutaciones y combinaciones; resolver problemas con matrices; y trabajar con números complejos.

4.1 Generar números

Problema

Quieres generar números al azar o en una secuencia.

Solución

Para generar un número decimal aleatorio mayor o igual que 0 y menor que 1, utiliza la función RAND escribiendo =RAND(). Para devolver un número aleatorio mayor o igual que a y menor que b, puedes modificar esta fórmula para que sea =RAND()*(b-a)+a. Si escribes =RAND()*100por ejemplo, devuelve un número decimal aleatorio mayor o igual que 0 y menor que 100.

Para generar un número entero aleatorio entre a y b inclusive, utiliza la función RANDBETWEEN. En general, utiliza la fórmula =RANDBETWEEN(a, b)por lo que si escribes =RANDBETWEEN(1, 10) devuelve un número entero aleatorio entre 1 y 10, ambos inclusive.

Para generar una matriz dinámica (ver Receta 3.4) de números aleatorios, utiliza la fórmula =RANDARRAY(rows, columns, min, max, integer)donde rows (opcional) es el número de filas (por defecto es 1), columns (opcional) es el número de columnas (por defecto es 1), min (opcional) es el número más bajo que es posible devolver (por defecto es 0), max (opcional) es el mayor (por defecto es 1), y integer especifica si quieres devolver números enteros o decimales: utiliza TRUE para los enteros y FALSE (por defecto) para los decimales. Por tanto, si escribes =RANDARRAY(6) devuelve seis filas de números decimales aleatorios entre 0 y 1 (ambos inclusive), mientras que si escribes =RANDARRAY(5, , 1, 10, TRUE) devuelve cinco filas de números enteros aleatorios entre 1 y 10 (ambos inclusive).

Para generar una matriz dinámica de números secuenciales, utiliza la fórmula =SEQUENCE​(rows, columns, start, step)donde rows (opcional) es el número de filas (por defecto es 1), columns (opcional) es el número de columnas (por defecto es 1), start (opcional) es el primer número de la secuencia (por defecto es 1), y step (opcional) es el número por el que quieres incrementar los números siguientes (por defecto es 1). Por tanto, si escribes =SEQUENCE(6) devuelve seis filas que contienen los números del 1 al 6, y escribiendo =SEQUENCE(5, , 0, 0.2) devuelve cinco filas que contienen los números 0, 0,2, 0,4, 0,6 y 0,8.

Advertencia

Las funciones RANDARRAY y SEQUENCE sólo están disponibles en Excel 2021 y Excel 365.

Debate

Esta receta ofrece varios métodos para generar números, dependiendo de tu versión de Excel. Puedes utilizarlos en una gran variedad de situaciones, como la generación de caracteres de texto (ver Recetas 5.3 y 5.4).

Ver también

Consulta la Receta 9.9 para conocer otras formas de generar números aleatorios.

4.2 Convertir un texto o un booleano en un número

Problema

Tienes un valor almacenado como texto o un valor TRUE/FALSE y quieres convertirlo en unnúmero.

Solución

Si un valor numérico se almacena como texto, puedes convertirlo en un número utilizando =text*1, =text+0o =—text. Si la celda A1 contiene la fórmula =TEXT(123, "00000"), que convierte el número 123 en el texto 00123 (ver Receta 5.17), tecleando =A1*1, =A1+0o=—A1 vuelve a convertir el texto en un número, devolviendo 123.

Puedes convertir un valor booleano TRUE/FALSE en un número utilizando una técnica similar. Así, si A1:A5 contienen valores TRUE/FALSE, puedes devolver sus valores numéricos utilizando las fórmulas =A1:A5*1, =A1:A5+0 o =—A1:A5. Consulta la Receta 7.6 para ver un ejemplo de esta técnica.

Debate

Las funciones matemáticas como SUM sólo incluyen valores numéricos en sus cálculos, lo que puede dar lugar a resultados inesperados. Si la celda A1 contiene la fórmula ="123", por ejemplo, que evalúa el texto 123 en lugar de un número, la fórmula =SUM(A1) devuelve 0. Esta receta ofrece una cómoda solución a este tipo de problema.

Ver también

En puedes utilizar las funciones ISNUMBER y ISTEXT para comprobar si un valor está almacenado como número o como texto; consulta la Receta 7.7.

4.3 Obtener el signo y el valor absoluto de un número

Problema

Tienes un número y quieres saber si es positivo o negativo y su valor absoluto.

Solución

La función SIGN te permite determinar el signo de un número mediante la fórmula =SIGN​(num⁠ber). La función devuelve 1 si number es positivo, -1 si es negativo y 0 si es cero. =SIGN(-10)por ejemplo, devuelve -1.

Para hallar el valor absoluto de un número -el número sin su signo- utiliza la fórmula =ABS(number). Por ejemplo, si la celda A1 contiene el número -10, al escribir =ABS(A1) obtendrás 10.

Debate

Esta receta ofrece una forma rápida de determinar si un número es positivo, negativo o cero y de obtener su valor absoluto. Se utilizan, por ejemplo, en la Receta 4.10.

4.4 Contar, sumar y promediar los valores de las celdas

Problema

Tienes un rango de celdas que contienen números y quieres contar cuántos hay y calcular su suma y su media.

Solución

Supón que A2:A5 contiene los números del 1 al 4, B2:B5 contiene los números del 11 al 14, C2:C5 contiene los números del 21 al 24, y quieres contar cuántas celdas contienen números, y calcular su suma y su media.

Para contar las celdas de que contienen números, puedes utilizar la función COUNT. Generalmente, utilizas la fórmula =COUNT(range)por lo que si escribes =COUNT(A2:C5) devuelve 12. También puedes pasar varios rangos a COUNT; si escribes =COUNT(A2:A5, C2:C5)por ejemplo, cuenta los números de A2:A5 y C2:C5, devolviendo 8 (ver Figura 4-1).

Figura 4-1. Utilizando COUNT, SUM, y AVERAGE

Consejo

También puedes utilizar la función COUNTA para contar las celdas que contienen cualquier valor (no sólo números) y la función COUNTBLANK para contar cuántas celdas están vacías.

Las funciones SUM y AVERAGE funcionan igual que COUNT, salvo que devuelven la suma y la media de valores numéricos, por lo que si escribes =SUM(A2:A5) devuelve 10, y tecleando =AVERAGE(A2:A5) devuelve 2,5.

Debate

Esta receta utiliza funciones matemáticas básicas -COUNT, SUM y AVERAGE- para contar los números de uno o varios rangos y calcular su suma y su media.

Ten en cuenta que si pasas dos rangos que se intersecan a las funciones COUNT, SUM o AVERAGE, éstas incluyen los números de la intersección dos veces, una por cada rango. Por ejemplo, si el rango A2:C5 contiene números, la fórmula =COUNT(A2:A5, A5:C5) devuelve 7 en lugar de 6 porque incluye A5 dos veces. Para solucionar este problema, puedes utilizar la fórmula =COUNT(range1, range2)-COUNT(range1 range2)donde la segunda COUNT utiliza el operador Espacio (ver Receta 3.1) para contar las celdas de laintersección.

Ver también

Consulta la Receta 8.4 para obtener más información sobre el cálculo de distintos tipos de medias.

4.5 Utilizar Criterios para Contar, Sumar y Promediar

Problema

Tienes un rango de celdas que contienen números y quieres contar cuántos coinciden con los criterios especificados y calcular su suma y su media.

Solución

Supón que A2:A8 enumera los alimentos, B2:B8 sus cantidades, y quieres hallar el recuento, la suma y la media de las celdas que cumplen las condiciones especificadas.

Si hay una única condición, puedes utilizar las funciones COUNTIF, SUMIF, y AVERAGEIF del siguiente modo (ver Figura 4-2):

COUNTIF

COUNTIF cuenta los valores que cumplen una única condición. Generalmente, se utiliza la fórmula =COUNTIF(range, condition)donde range es el rango de celdas al que quieres aplicar la fórmula condition por lo que si escribes =COUNTIF(B2:B8, ">10") cuenta cuántas cantidades son superiores a 10 y escribiendo =COUNTIF(A2:A8, "Pizza") cuenta el número de elementos Pizza.

SUMIF

SUMIF funciona de forma similar a COUNTIF, excepto que suma los valores que cumplen la condición. Generalmente, se utiliza la fórmula =SUMIF(range, condition, sum_range)donde range es el rango de celdas al que quieres aplicar la fórmula condition y sum_range (opcional) es el rango de celdas que quieres sumar (si es distinto de range), de modo que si escribes =SUMIF(A2:A8, "Pizza", B2:B8) devuelve la suma de las cantidades de Pizza.

AVERAGEIF

AVERAGEIF funciona igual que SUMIF, excepto que calcula la media, por lo que al teclear =AVERAGEIF(A2:A8, "Pizza", B2:B8) devuelve el importe medio de cualquier elemento Pizza.

Figura 4-2. Utilizar criterios para contar elementos y calcular la suma y la media

Si hay varias condiciones, puedes utilizar las funciones COUNTIFS, SUMIFS, y AVERAGEIFS del siguiente modo (ver Figura 4-2):

COUNTIFS

COUNTIFS cuenta el número de celdas que cumplen varias condiciones. En general, utiliza =COUNTIFS(condition_range1, condition1, condition_range2, condition2, …​) donde quieras aplicar condition1 a condition_range1, condition2 a condition_range2, y así sucesivamente. Para contar el número de artículos de Pizza con un importe superior a 10, escribirías =COUNTIFS(A2:A8, "Pizza", B2:B8, ">10").

SUMIFS

SUMIFS funciona de forma similar a COUNTIFS, excepto que calcula la suma. En general, utiliza =SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2, …​) donde sum_range es el rango de celdas que quieres sumar, yquieres aplicar condition1 a condition_range1, condition2 acondition_range2, y así sucesivamente. Por ejemplo =SUMIFS(B2:B8, A2:A8, "Pizza", B2:B8, ">10") devuelve el importe total de cualquier elemento de Pizza con un importe superior a 10.

AVERAGEIFS

AVERAGEIFS funciona igual que SUMIFS, excepto que calcula la media, por lo que al escribir =AVERAGEIFS(B2:B8, A2:A8, "Pizza", B2:B8, ">10") calcula el importe medio de cualquier elemento de Pizza cuyo importe sea superior a 10.

Debate

Esta receta amplía la Receta 4.4 para calcular el recuento, la suma y la media sujetos a una o varias condiciones. Un enfoque alternativo sería añadir estos valores a una tabla (ver Receta 2.18) y aplicar un filtro, o utilizar la función FILTER (ver Receta 7.3).

4.6 Sumar y restar cuadrados de valores

Problema

Tienes un rango de números y quieres realizar cálculos utilizando la suma -o diferencia- de sus valores al cuadrado.

Solución

Supongamos que las celdas A2:A6 contienen los valores x 1 a 5, B2:B6 los correspondientes valores y 11 a 15, y quieres realizar cálculos utilizando el cuadrado de sus valores. Excel incluye varias funciones que puedes utilizar como se indica a continuación (ver Figura 4-3):

SUMSQ

SUMSQ calcula ^Σx2, la suma de los cuadrados de los valores de x. Generalmente se utiliza la fórmula =SUMSQ(x_values)por lo que si escribes =SUMSQ(A2:A6) devuelve 55. También puedes pasar varios rangos a la función SUMSQ, de modo que si escribes =SUMSQ(A2:A6, B3:B5)por ejemplo, suma los cuadrados de los valores de A2:A6 y B3:B5, obteniendo 564.

SUMX2PY2

SUMX2PY2 calcula Σ(^x2+y2)-la suma de los cuadrados de los valores x e y. Tiene la forma =SUMX2PY2(x_values, y_values)por lo que si tecleas =SUMX2PY2(A2:A6, B2:B6) devuelve 910.

SUMX2MY2

SUMX2MY2 calcula Σ(^x2-y2)-la suma de las diferencias de los cuadrados entre los valores x e y. Generalmente, se utiliza =SUMX2MY2(x_values, y_values)por lo quesi escribes =SUMX2MY2(A2:A6, B2:B6) devuelve -800.

SUMXMY2

SUMXMY2 calcula Σ(x-y^{)2 -la}suma de los cuadrados de las diferencias entre los valores x e y. Generalmente, se utiliza =SUMX2MY2(x_values, y_values)por lo que si escribes =SUMXMY2(A2:A6, B2:B6) devuelve 500.

Figura 4-3. Suma y resta de sumas de cuadrados

Advertencia

Las funciones SUMX2PY2, SUMX2MY2 y SUMXMY2 esperan el mismo número de valores x que de valores y. Si no es así, las funciones devuelven el valor de error #N/A.

Debate

Esta receta ofrece una forma flexible de realizar distintos cálculos utilizando valores al cuadrado. Ten en cuenta que si tienes dos rangos que contienen el mismo número de valores, las fórmulas =SUMSQ(range1, range2) y =SUMX2PY2(range1, range2) devuelven el mismo valor.

4.7 Utilizar la multiplicación y los múltiplos

Problema

Tienes un rango de números y quieres multiplicarlos, calcular la suma de sus productos para cada fila y hallar el mínimo o mínimo común múltiplo.

Solución

Supón que en las celdas A2:A4 aparecen los números 1, 2 y 6, y en las B2:B4 los números 4, 8 y 16. Quieres hallar su producto, la suma de los productos de cada fila y el mínimo común múltiplo.

Para multiplicar todas las celdas que contengan números, utiliza la función PRODUCT. Generalmente, se utiliza la fórmula =PRODUCT(range)por lo que si escribes =PRODUCT(A2:B4) devuelve 6144 (ver Figura 4-4).

Para calcular la suma de los productos de dos o más rangos correspondientes -por ejemplo, dos columnas- utiliza la función SUMPRODUCT. Esta función tiene la forma =SUMPRODUCT(range1, range1)donde range1 y range2 son los dos rangos cuyos valores quieres multiplicar y luego sumar. Para multiplicar los valores de cada fila y sumar los resultados, por ejemplo, escribe =SUMPRODUCT(A2:A4, B2:B4)que devuelve 116.

Figura 4-4. Utilizando PRODUCT, SUMPRODUCT, y LCM

Consejo

También puedes utilizar la función SUMPRODUCT para realizar otras operaciones antes de sumar los valores, como la división o la resta. Para ello, sustituye la coma por el operador que quieras utilizar (*, /, +, o -)-por ejemplo, =SUMPRODUCT(A2:A4/B2:B4).

Para hallar el mínimo común múltiplo de un conjunto de números enteros -el número entero positivo más pequeño que es múltiplo de cada número- utiliza la función LCM. Generalmente se utiliza la fórmula =LCM(range)por lo que si escribes =LCM(A2:B4) devuelve 48.

Debate

Esta receta ofrece varias funciones que puedes utilizar al multiplicar números. Ten en cuenta que la función SUMPRODUCT se utiliza a menudo en estadística para calcular medias ponderadas (para más información, consulta la Receta 8.4 ).

4.8 Hallar cocientes, restos y divisores

Problema

Tienes dos números y quieres hallar las partes entera y resto al dividir un número por el otro, así como el máximo común divisor de los dos números.

Solución

Supongamos que A2 contiene el número 26 y A3 contiene 6. Quieres dividir A2 entre A3 y dividir el resultado en partes enteras y resto.

Para hallar la parte entera, utiliza la función QUOTIENT. Tiene la forma =QUOTIENT(number, divisor)donde quieres dividir el argumento number argumento por el divisor. Para hallar la parte entera cuando divides A2 por A3, escribe =QUOTIENT(A2, A3)que devuelve 4 cuando A2 es 26 y A3 es 6.

Consejo

También puedes obtener este resultado escribiendo =TRUNC(A2/A3). Consulta la Receta 4.9 para obtener más información sobre esta función.

Para hallar el resto, utiliza la función MOD. Generalmente utilizas la fórmula =MOD​(num⁠ber, divisor)donde quieres dividir el number argumento por el divisor, así que para hallar el resto cuando divides A2 entre A3, escribes =MOD(A2,A3)que devuelve 2 cuando A2 es 26 y A3 es 6.

Consejo

También puedes utilizar la función MOD para devolver la parte fraccionaria de un número utilizando la fórmula =MOD(number, 1).

Para hallar el máximo común divisor -el mayor número entero que divide los números sin resto- utiliza la función GCD. Generalmente, utilizas la fórmula =GCD​(num⁠bers)para hallar el máximo común divisor de los números de las celdas A2:A3, escribe =GCD(A2:A3)que devuelve 2 cuando A2 es 26 y A3 es 6.

Debate

Esta receta te muestra cómo hallar cocientes, residuos y el máximo común denominador. También introduce la función MOD, que tiene muchas aplicaciones; por ejemplo, la Receta 6.8.

4.9 Redondeo a decimales y enteros

Problema

Tienes un número y quieres redondearlo a un número determinado de decimales o a un número entero.

Solución

Excel incluye varias funciones de redondeo que puedes utilizar, dependiendo de si quieres redondear números positivos y negativos hacia arriba, hacia abajo o al número más próximo. La Figura 4-5 resume el comportamiento de estas funciones.

Para redondear al número entero o decimal más próximo, utiliza la función ROUND. Generalmente se utiliza la fórmula =ROUND(number, num_digits)donde num_digits especifica el número de decimales al que quieres redondear el argumento number argumento. Si num_digits es positivo, especifica el número de decimales; si es cero, la función redondea al entero más próximo; y si es negativo, la función redondea a la izquierda del punto decimal. Por tanto, si escribes =ROUND(5.6, 0) redondea 5,6 al entero más próximo (devolviendo 6), y tecleando =ROUND(-5.673,2) redondea -5,673 a 2 decimales (devolviendo -5,67).

Figura 4-5. Cómo redondean las distintas funciones los números positivos y negativos

Para redondear números positivos y negativos alejándolos de cero, utiliza la función ROUNDUP. Esta función funciona de forma similar a ROUND, excepto que redondea los números positivos hacia arriba y los negativos alejándolos de cero; entre bastidores, redondea hacia arriba el valor absoluto del número, aumentando su magnitud, y luego aplica su signo. Así que tecleando =ROUNDUP(5.321, 2) redondea 5,321 a 5,33, y escribiendo =ROUNDUP(-5.3, 0) redondea -5,3 a -6.

Para redondear números positivos alejándolos de cero y números negativos acercándolos, utiliza la función CEILING.MATH. Generalmente se utiliza la fórmula =CEILING.MATH(number, multiple)donde number es el número que quieres redondear y multiple (opcional) especifica el múltiplo al que quieres redondear los números-por defecto es 1 para números positivos y -1 para negativos. Por ejemplo, si escribes =CEILING.MATH(5.321, 0.1) redondea 5,321 hasta 5,4, y =CEILING.MATH(-5.3) redondea -5,3 a -5.

Para redondear números positivos y negativos hacia cero, utiliza la función ROUNDDOWN. Esta función funciona de forma similar a las funciones ROUND y ROUNDUP, excepto que redondea los números positivos hacia abajo y los negativos hacia cero; entre bastidores, redondea hacia abajo el valor absoluto del número, disminuyendo su magnitud, y luego aplica su signo. Por tanto, si escribes =ROUNDDOWN(5.32, 1) redondea 5,32 a 5,3, y teclear =ROUNDDOWN(-5.3, 0) redondea -5,3 a -5.

Consejo

Si quieres redondear un número a un entero, puedes utilizar la función TRUNC en lugar de ROUNDDOWN. TRUNC funciona igual que ROUNDDOWN y utiliza los mismos argumentos, pero su num_digits es opcional y por defecto es cero. Por tanto, si escribes =TRUNC(5.3) redondea 5,3 a 5, y escribiendo =TRUNC(-5.3) redondea-5,3 a -5.

Para redondear números positivos hacia cero y números negativos alejándolos de él, utiliza la función FLOOR.MATH, que funciona de forma similar a CEILING.MATH. Generalmente utilizas la fórmula =FLOOR.MATH(number, multiple)donde number es el número que quieres redondear y multiple (opcional) especifica el múltiplo al que quieres redondear los números-por defecto es 1 para números positivos y -1 para negativos. Por tanto, si escribes =FLOOR.MATH(5.321, 0.1) redondea 5,321 a 5,3, y si escribes =FLOOR.MATH​(-5.3) redondea -5,3 a -6.

Consejo

Si quieres redondear un número a un entero, puedes utilizar la función INT en lugar de FLOOR.MATH. Esta función acepta un único argumento -el número que quieres redondear a un entero-, de modo que si escribes =INT(5.3) redondea 5,3 a 5, y si escribes =INT(-5.3) redondea-5,3 a -6.

Debate

Como puedes ver, Excel incluye varias funciones que redondean los números de formas ligeramente distintas; mientras que la función ROUND redondea los valores al número más próximo, todas las demás redondean los números positivos o negativos hacia o desde cero. Consulta la Figura 4-5 para comparar el comportamiento de redondeo de las funciones.

4.10 Redondeo a cifras significativas y múltiplos

Problema

Tienes un número y quieres redondearlo a un múltiplo de otro número o a un número determinado de cifras significativas.

Solución

Las funciones ROUND, ROUNDUP, ROUNDDOWN, y TRUNC (ver Receta 4.9) te permiten redondear un número a un múltiplo que sea una potencia de 10. Si escribes =ROUND(234.5, -1)por ejemplo, redondea 234,5 al múltiplo de 10 más próximo, devolviendo 230, y tecleando =ROUND(234.5, -2) lo redondea al múltiplo de 100 más próximo, obteniendo 200.

También puedes utilizar estas funciones para redondear un número a un número determinado de cifras significativas. Generalmente utilizas la fórmula =ROUND(number, significant_figures-LEN(TRUNC(ABS(number))))cuando quieras redondear number al número especificado de significant_figures. Así, si la celda A1 contiene el número 23456,789, lo redondearías a dos cifras significativas escribiendo =ROUND(A1, 2-LEN(TRUNC(ABS(A1))))devolviendo 23000.

Para redondear un número a un múltiplo que no sea una potencia de 10, utiliza las funciones MROUND, CEILING.MATH y FLOOR.MATH.

La función MROUND redondea un número al múltiplo más próximo y tiene la forma =MROUND(number, multiple). Para redondear 23456 al múltiplo de 5 más próximo, escribirías =MROUND(23456, 5)que devuelve 23455, y para redondear -23456 al múltiplo más cercano de -5, escribirías =MROUND(-23456, -5)que devuelve -23455.

Advertencia

Los parámetros de la función MROUND number y multiple deben tener el mismo signo, o devolverá un valor de error #NUM!. Si necesitas redondear números positivos y negativos al múltiplo más próximo, utiliza la fórmula =MROUND(number, multiple*SIGN(number)) en su lugar.

Para redondear un número positivo al múltiplo más próximo, utiliza la función CEILING.MATH en la fórmula =CEILING.MATH(number, multiple, mode)donde mode (opcional) especifica cómo quieres redondear number si es negativo; omite mode para redondear un negativo number hacia cero, y utiliza 1 para redondearlo alejándolo de cero. Por tanto, si escribes =CEILING.MATH(-234, 5) redondea -234 al múltiplo de cinco más cercano a cero y devuelve -230, y si escribes =CEILING.MATH(-234, 5, 1) lo redondea al múltiplo más cercano alejado de cero y devuelve -235.

Para redondear un número positivo al múltiplo más próximo, utiliza la función FLOOR.MATH en la fórmula =FLOOR.MATH(number, multiple, mode); en este caso, omite el argumento opcional mode para redondear un número negativo number lejos de cero y utiliza -1 para redondearlo hacia cero. Por tanto, si escribes =FLOOR.MATH(-234, 5) redondea -234 al múltiplo de cinco más próximo a cero y devuelve -235, y si tecleas =FLOOR.MATH(-234, 5, -1) lo redondea al múltiplo más cercano hacia cero y devuelve -230.

Por último, para redondear un número al número par o impar más próximo a cero, utiliza las funciones EVEN y ODD. Si escribes =EVEN(2.3)por ejemplo, devuelve 4, mientras que si tecleas =ODD(2.3) devuelve 3.

Debate

Esta receta se basa en la Receta 4.9 para redondear un número a un número determinado de cifras significativas o a un múltiplo de otro número.

4.11 Utilizar potencias, exponentes, raíces cuadradas y logaritmos

Problema

Tienes un número y quieres elevarlo a una potencia o hallar su raíz cuadrada.

Solución

Puedes utilizar el operador ^ o la función POWER para elevar un número a una potencia. Escribiendo =2^4 o =POWER(2,4)por ejemplo, calcula²⁴ y devuelve 16.

También puedes utilizar la función POWER con rangos en la fórmula =POWER(numbers, powers). Si A2:A5 contiene los números del 1 al 4, por ejemplo, al escribir =POWER(A2:A5, 2) en Excel 2021 o Excel 365 devuelve una matriz dinámica (ver Receta 3.4) que contiene cada número elevado al cuadrado, y tecleando =POWER(A2:A5, A2:A5) calcula ^xx para cada valor:¹¹,²², etc. (ver Figura 4-6).

Figura 4-6. Utilizando la función POWER

Para hallar la raíz cuadrada de un número, utiliza la función SQRT. Si escribes =SQRT(4)por ejemplo, devuelve 2, y tecleando =SQRT(A2:A5) en Excel 2021 o Excel 365 devuelve una matriz dinámica que contiene la raíz cuadrada de cada número de A2:A5.

Para elevar e a la potencia de un número, utiliza la fórmula =EXP(number). Así que si escribes =EXP(5) calcula ^e5, obteniendo 148,4132.

La inversa de EXP es la función LN, que devuelve el logaritmo natural de un número. Generalmente se utiliza la fórmula =LN(number)que devuelve la potencia a la que debes elevar e para que sea igual a number. Por tanto, si tecleas =LN(3) devuelve 1,0986 -ya que e1^,0986 es aproximadamente 3- y tecleando =LN(EXP(5)) devuelve 5.

Para hallar el logaritmo en base 10 de un número, utiliza la función LOG10. En general, utiliza la fórmula =LOG10(number)que te devuelve la potencia a la que tienes que elevar 10 para que sea igual a number. Por tanto, si tecleas =LOG10(1000) devuelve 3 porque¹⁰³ es igual a 1000.

Para hallar el logaritmo de un número para una base que especifiques, utiliza la función LOG. Esta función toma la forma =LOG(number,base) y te devuelve la potencia a la que debes elevar base para que sea igual a number. Por tanto, si escribes =LOG(16,2) devuelve 4 porque²⁴ es igual a 16.

Debate

Esta receta detalla varias funciones que puedes utilizar para trabajar con potencias, exponentes y logaritmos, incluida la búsqueda de la raíz cuadrada de un número.

4.12 Suma de una serie de potencias

Problema

Tienes una serie de potencias (por ejemplo, 5x3+4x2+3x+2) y quieres conocer su resultado para un valor de x.

Solución

La función SERIESSUM devuelve la suma de una serie de potencias. Tiene la forma =SERIESSUM(x, n, m, a)que representa la ecuación ^a1xn + a2x^(n+m) + a3x^(n+2m) + a4x^(n+3m), etc. El argumento x argumento es el valor de x, n es la primera potencia de x en la serie, m es el tamañodel paso utilizado para aumentar n en cada potencia sucesiva de x, y a es una matriz de los coeficientes utilizados para multiplicar x. Puedes calcular ^5x3 + ^4x2 + 3x + 2, donde x es 2, por ejemplo, escribiendo la fórmula =SERIESSUM(2, 3, -1, {5,4,3,2})que devuelve 64.

Debate

Esta receta ofrece una forma de calcular la suma de una serie de potencias. En lugar de escribir la fórmula utilizando los operadores * y ^, puedes utilizar la función SERIESSUM para obtener el mismo resultado.

4.13 Utilizar Factoriales, Permutaciones y Combinaciones

Problema

Quieres encontrar el factorial de un número, o el número posible de combinaciones y permutaciones cuando seleccionas r elementos de n.

Solución

Para hallar el factorialde un número n -elnúmero de formas de ordenar n elementos, o el producto de todos los enteros positivos- utiliza la función FACT. Generalmente se utiliza la fórmula =FACT(n)que devuelve n¡-el producto de todos los enteros positivos de 1 a n-, por lo que si tecleas =FACT(5) devuelve 120.

Nota

Excel también tiene una función FACTDOUBLE, que devuelve el doble factorial de un número, o n!!!-el producto de todos los números enteros de 1 a n que tienen la misma paridad (par o impar) que n.

Para hallar el número de formas de disponer n elementos, donde algunos son duplicados, utiliza la función MULTINOMIAL. Supongamos, por ejemplo, que quieres encontrar el númerode formas de ordenar las letras de la palabra Mississippi, que sólo tiene cuatroletras únicas. Como hay una letra M, dos Ps y cuatro I y S, puedes calcular el número de disposiciones escribiendo =MULTINOMIAL(1, 2, 4, 4)que devuelve 34.650. Generalmente, se utiliza la fórmula =MULTINOMIAL(item_counts)donde item_counts especifica cuántos hay de cada elemento.

Para hallar el número de permutaciones cuando seleccionas r elementos de n -elnúmero de formas de disponer los elementos cuando el orden importa- y puedes seleccionar cada elemento una sola vez, utiliza la función PERMUT. Generalmente, se utiliza la fórmula =PERMUT(n, r)así que si, por ejemplo, quisieras saber cuántas carteras podrías crear eligiendo 2 acciones de 20 en las que quieres que una acción tenga el 60% de la asignación y la otra el 40% restante, escribirías =PERMUT(20, 2)devolviendo 380.

Para hallar el número de combinaciones -dondeel orden no importa- cuando seleccionas r elementos de n y puedes seleccionar cada elemento una sola vez, utiliza la función COMBIN. Esta función tiene la forma =COMBIN(n, r)por lo que si, por ejemplo, quisieras saber cuántas carteras podrías crear eligiendo 2 acciones con la misma ponderación de entre 20, escribirías =COMBIN(20, 2), devolviendo 190.

Nota

Las funciones PERMUT y COMBIN asumen que puedes seleccionar cada elemento una sola vez. Si puedes seleccionar cada elemento más de una vez, utiliza en su lugar las funciones PERMUTATIONA y COMBINA. Para averiguar el número de posibles contraseñas de cinco dígitos que utilizan números del cero al nueve, escribirías =PERMUTATIONA(10, 5)que devuelve 100000.

Debate

Esta receta ofrece una visión general de las funciones combinatorias de Excel, que consisten en contar el número de disposiciones posibles. Estas funciones son útiles en áreas como la probabilidad y constituyen la base de la distribución binomial (ver Receta 8.14).

4.14 Utilizar la trigonometría

Problema

Tienes un problema de trigonometría y quieres saber qué funciones están disponibles.

Solución

Excel incluye las siguientes funciones de trigonometría:

PI

Esto devuelve la constante π (pi) a 15 dígitos.

RADIANS y DEGREES

RADIANS convierte un ángulo de grados a radianes, y DEGREES convierte un ángulo de radianes a grados.

SIN, COS, y TAN

Devuelven el seno, coseno y tangente de un ángulo especificado en radianes. Para calcular el seno de π/2 radianes, escribe =SIN(PI()/2)que devuelve 1.

CSC, SEC, y COT

Devuelven la cosecante, la secante y la cotangente de un ángulo especificado en radianes. Para calcular la secante de π/3 radianes, escribe =SEC(PI()/3)que devuelve 2.

ASIN, ACOS, ATAN, y ATAN2

Las funciones ASIN, ACOS y ATAN calculan el arcoseno, arcocoseno y arctangente de un número y devuelven un ángulo en radianes; la función ATAN2 devuelve el arctangente de las coordenadas x e y especificadas.

SINH, COSH, TANH, CSCH, SECH, COTH, ASINH, ACOSH, y ATANH

Estas funciones devuelven el seno hiperbólico, el coseno, la tangente, etc.

Debate

Como puedes ver, Excel ofrece una gran cantidad de funciones que puedes utilizar para resolver problemas de trigonometría. La mayoría de ellas aceptan o devuelven un ángulo en radianes, así que si es necesario, utiliza las funciones RADIANS y DEGREES para convertir entre radianes y grados.

4.15 Trabajar con matrices

Problema

Quieres resolver un problema en Excel utilizando matrices, pero no sabes cómo.

Solución

Supón que tres clientes deciden comprar cantidades diferentes de dos productos. Conoces el precio unitario y el peso de cada producto y quieres calcular el precio total y el peso de cada cliente. B2:C4 enumera las cantidades de cada cliente, y F2:G3 el precio unitario y el peso.

Puedes resolver este problema tratando las cantidades compradas y los precios unitarios y pesos como dos matrices y multiplicándolas. Para ello, utiliza la función MMULT, que multiplica dos matrices y devuelve una matriz dinámica. En este ejemplo, al escribir =MMULT(B2:C4, F2:G3) devuelve una matriz dinámica que calcula el precio total y el peso de cada cliente (ver Figura 4-7).

Figura 4-7. Utilizando la función MMULT para multiplicar dos matrices

Supón que conoces el precio unitario de cada producto, su peso y los totales de cada cliente. En este caso, puedes calcular las cantidades compradas por cada cliente multiplicando la matriz de totales de clientes por la inversa de la matriz de precios unitarios y pesos. Puedes hallar la inversa de una matriz (si existe) utilizando la función MINVERSE, de modo que si B2:C4 enumera los totales de clientes y F2:G3 enumera el precio unitario y el peso, escribiendo =MMULT(B2:C4, MINVERSE(F2:G3)) devuelve una matriz de las compras de los clientes (ver Figura 4-8).

Figura 4-8. Utilización de las funciones MMULT y MINVERSE

Otras funciones de la matriz son las siguientes

MUNIT

Esta función devuelve la matriz unitaria para una dimensión especificada. Por ejemplo, la fórmula =MUNIT(3) devuelve una matriz unitaria con tres filas y columnas.

MDETERM

Devuelve el determinante de una matriz.

TRANSPOSE

Esto transpone un rango de celdas que convierte las filas en columnas y las columnas en filas (véase la Receta 7.4).

Advertencia

MMULT, MINVERSE, MUNIT, y TRANSPOSE devuelven matrices dinámicas (ver Receta 3.4). Si utilizas una versión de Excel anterior a Excel 2021 o Excel 365, selecciona todo el rango de salida antes de introducir la fórmula y, a continuación, pulsa Ctrl+Mayús+Intro/Retorno.

Debate

Esta receta ofrece una práctica visión general del uso de las funciones matriciales de Excel. Puedes utilizarlas, por ejemplo, para resolver sistemas de ecuaciones lineales, como en los ejemplos utilizados en esta receta.

4.16 Convertir entre sistemas numéricos

Problema

Tienes un número y quieres convertirlo a otro sistema (por ejemplo, de decimal a binario, octal o hexadecimal).

Solución

Utiliza las funciones DEC2BIN, DEC2OCT, DEC2HEX, BIN2DEC, BIN2OCT, BIN2HEX, OCT2DEC, OCT2BIN, OCT2HEX, HEX2DEC, HEX2BIN, y HEX2OCT para convertir números de un sistema a otro. Para convertir el número decimal 9 a binario, por ejemplo, escribe =DEC2BIN(9)que devuelve 1001. Del mismo modo, para convertir el número hexadecimal A2 a decimal, escribe =HEX2DEC("A2")que devuelve 162.

Debate

Esta receta muestra la conversión de números entre decimales, binarios, octales y hexadecimales; esto es útil si, por ejemplo, quieres realizar operaciones bit a bit en un campo de bits binario (ver Receta 4.17).

4.17 Realizar operaciones bit a bit

Problema

Tienes un campo binario de bits y quieres realizar operaciones a nivel de bit.

Solución

Supón que tienes un conjunto de lecturas decimales del sistema controlador de un camión de sal, donde cada número representa el estado actual de sus cuatro sensores como un campo de bits binarios. El primer bit corresponde al sensor de la válvula de presión, el segundo al esparcidor, el tercero a la alimentación de sal y el cuarto a la baliza. El bit de cada sensor se pone a 1 si está encendido y a 0 si está apagado, de modo que si la válvula de presión y el esparcidor están encendidos pero la alimentación de sal y la baliza están apagadas, el controlador registra el valor 0011 en binario, o el número decimal 3.

Puedes aplicar la función BITAND a las lecturas del controlador para determinar si un sensor está encendido o apagado. Esta función tiene la forma =BITAND(number1, number2)donde number1 y number2 son decimales, y devuelve el AND a nivel de bits de sus argumentos como un decimal. Si el controlador registra el valor 6, por ejemplo, puedes averiguar si la válvula de presión -el primer sensor- está encendida escribiendo =BITAND(6, 1)>0que devuelve FALSE. Del mismo modo, puedes saber si el esparcidor -el segundo sensor- está encendido escribiendo =BITAND(6, BIN2DEC(10))>0que devuelve TRUE. Por último, si las celdas A2:A6 contienen un conjunto de lecturas del controlador y utilizas Excel 2021 o Excel 365, puedes devolver una matriz dinámica (ver Receta 3.4) que muestre el estado de cada sensor escribiendo =BITAND(A2:A6, BIN2DEC({1,10,100,1000}))>0 (ver Figura 4-9).

Figura 4-9. Utilización de la función BITAND con un campo binario de bits

Otras funciones bit a bit son las siguientes

BITOR y BITXOR

Devuelven el OR y el XOR (eXclusivo-OR) de sus argumentos.

BITLSHIFT y BITRSHIFT

Devuelven un número desplazado a la izquierda o a la derecha un número determinado de bits.

Debate

Los operadores bit a bit te permiten realizar operaciones a nivel de bit y trabajar con la representación binaria de un número en lugar del valor del número. Son útiles cuando se trabaja con estructuras como campos de bits, como en el ejemplo utilizado en esta receta.

Ver también

Para más información sobre la conversión entre los sistemas numéricos decimal y binario, consulta la Receta 4.16.

4.18 Trabajar con números complejos

Problema

Quieres trabajar con números complejos en Excel y quieres saber qué funciones están disponibles.

Solución

Un número complejo es uno de la forma a + bi, donde a y b son números reales, e i es la raíz cuadrada de -1.

Para introducir un número complejo en una celda, puedes escribirlo directamente o pasar coeficientes reales e imaginarios a la función COMPLEX. Para introducir el número imaginario 6+2i en la celda A1, por ejemplo, puedes escribir 6+2i o la fórmula =COMPLEX(6, 2).

Consejo

Excel también admite el uso de j para números complejos en lugar de i. Escribe 6+2j o la fórmula =COMPLEX(6, 2, "j").

Para recuperar los coeficientes real e imaginario de un número complejo, utiliza las funciones IMREAL y IMAGINARY. Por ejemplo, si la celda A1 contiene el número complejo 6 + 2i, al escribir =IMREAL(A1) obtendrás 6, y si tecleas =IMAGINARY(A1) devuelve 2.

Para encontrar el conjugado, el argumento y el valor absoluto de un número complejo, utiliza las funciones IMCONJUGATE, IMARGUMENT y IMABS. Por ejemplo, si la celda A1 contiene 6+2i, al escribir =IMCONJUGATE(A1) obtendrás 6-2i.

Para realizar operaciones aritméticas con números complejos, utiliza la función IMSUM para sumarlos, IMSUB para restar uno de otro, IMPRODUCT para calcular el producto y IMDIV para dividir uno por otro. Por ejemplo, si escribes =IMSUM("6+2i", "5-4i") devuelve 11-2i, y tecleando =IMPRODUCT("6+2i", "5-4i") devuelve 38-14i.

Advertencia

Asegúrate de utilizar sistemáticamente i o j para denotar un número complejo, no ambos. Si escribes =IMSUM("6+2j", "5-4j")por ejemplo, devuelve 11-2j, pero si tecleas =IMSUM("6+2i", "5-4j") devuelve el valor de error #VALUE!.

Excel también incluye versiones en números complejos de muchas otras funciones, cada una de ellas con el prefijo IM. Por ejemplo, utiliza IMSQRT para hallar la raíz cuadrada de un número complejo, IMLN para hallar el logaritmo natural, IMSIN para hallar el seno, etc.

Debate

Esta receta ofrece una visión general de las funciones de números complejos más importantes de Excel. Se pueden utilizar, por ejemplo, con la salida de la herramienta Análisis de Fourier del ToolPak Análisis (ver Receta 9.19).