Excel para ciencia e ingeniería E-Book

Fátima Ángela Ternero Fernández

• Herausgeber: Marcombo
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Spanisch
• Veröffentlichungsjahr: 2022

Seitenzahl: 583

Beschreibung

¿Por qué científicos e ingenieros necesitan dominar Excel? Microsoft® Excel no es una aplicación especializada en tareas de ciencia e ingeniería. Sin embargo, es una de las herramientas más utilizadas por científicos e ingenieros. La razón no se debe a que Microsoft® Excel sea la herramienta más idónea para resolver los problemas científico-técnicos en sus múltiples campos. De hecho, puede decirse que las capacidades nativas de esta aplicación están a años luz de los paquetes comerciales de cálculo altamente especializados. La razón estriba en su omnipresencia y su versatilidad. El que todos los paquetes comerciales cuenten con integración con Microsoft® Excel es una muestra del reconocimiento de su ubicuidad. La versatilidad de Microsoft® Excel, su amplia adaptabilidad, es una virtud que debe al VBA, el lenguaje interpretado que lleva incorporado. Las posibilidades de este entorno de programación son tales, que pronto se percatará de que solo su conocimiento del tema impondrá los límites. Excel para ciencia e ingeniería le enseñará cómo sacar partido a Excel y al VBA en las tareas que a menudo realiza en distintas aplicaciones. Gracias a este libro aprenderá a: 1.Ampliar el repertorio de funciones nativas de Excel con otras más especializadas. 2.Programar en el lenguaje del VBA de manera eficiente. 3.Generar e instalar un complemento propio con su colección de funciones especializadas. 4.Implementar y utilizar funciones y procedimientos en VBA para resolver tareas numéricas elementales: interpolación, regresión, cálculo de raíces, operaciones matriciales, resolución de sistemas de ecuaciones, optimización, diferenciación, integración, entre otras. 5.Mejorar los gráficos de Excel. Asimismo, se incluyen gratis los libros Excel de trabajo y un complemento instalable de Excel, Excelsior, que reúne todas las funciones y los procedimientos desarrollados en el libro, agrupados en distintos módulos de códigos, cada uno encargado de una tarea específica. Con Excelsior ahorrará muchas horas de trabajo y podrá hacer con Excel lo que antes solo estaba al alcance de paquetes especializados. El libro está escrito por dos profesores con formación científica, pero que trabajan en el ámbito ingenieril: Juan Manuel Montes Martos (Dr. en Físicas) y Fátima Ternero Fernández (Dra. en Químicas), ambos profesores del departamento de Ingeniería y Ciencia de los Materiales de la Universidad de Sevilla. El libro es el resultado de muchos años de trabajo con la plataforma Excel aplicada a la resolución numérica de problemas ingenieriles. Tanto si es un estudiante de una carrera científico-técnica como un profesional de ellas, encontrará en este libro un poderoso aliado. No solo le facilitará la ejecución de tareas complejas en un tiempo menor, sino también la resolución de problemas que creía imposibles de realizar salvo con aplicaciones específicas, de costosas licencias.

Leseprobe

Excel para ciencia e ingeniería

Primera edición, 2022

© 2022 Juan Manuel Montes Martos y Fátima Ternero Fernández

© MARCOMBO, S.L. 2022

www.marcombo.com

Diseño de la cubierta: ENEDENÚ DISEÑO GRÁFICO

Corrección: Nuria Barroso

Directora de producción: M.a Rosa Castillo

Cualquier forma de reproducción, distribución, comunicación pública o transformación de esta obra solo puede ser realizada con la autorización de sus titulares, salvo excepción prevista por la ley. Diríjase a CEDRO (Centro Español de Derechos Reprográficos, www.cedro.org) si necesita fotocopiar o escanear algún fragmento de esta obra.

ISBN: 9788426734457

Producción del ePub: booqlab

A Leonardo Volpi y a su equipo Foxes Team,juntos nos demostraron las formidables posibilidadesde expansión de Excel, con su enorme talento y rigorsolo superado por su inmenso altruismo.

Índice

A modo de introducción

Capítulo 1. Una rápida visita

Capítulo 2. Operadores y funciones de Excel

Capítulo 3. Gráficos

Capítulo 4. El VBA

Capítulo 5. Extender el entorno

Capítulo 6. Evaluación de funciones

Capítulo 7. Interpolación

Capítulo 8. Regresión lineal y linealizada

Capítulo 9. Regresión no lineal

Capítulo 10. Derivación numérica

Capítulo 11. Resolución de ecuaciones

Capítulo 12. Resolución de sistemas de ecuaciones

Capítulo 13. Optimización

Capítulo 14. Integración numérica

Capítulo 15. Extender los gráficos de Excel

Epílogo. El complemento Excelsior

Bibliografía

A modo de introducción

Incluso en el improbable caso de que este sea su primer contacto con la aplicación Microsoft® Excel, seguramente ya sabrá que se trata de una aplicación informática perteneciente a la categoría de hojas de cálculo. Aunque Excel en sus orígenes estaba dirigida al campo de la contabilidad y las finanzas, pronto se fue haciendo hueco en los campos de la ciencia y de la ingeniería, convirtiéndose, en poco tiempo, en la herramienta extremadamente potente y versátil que hoy tenemos a nuestra disposición. Pese a ello, Excel continúa teniendo ciertas carencias en el campo científico-ingenieril, que, no obstante, pueden suplirse de modo sencillo, como aquí demostraremos.

Aprender un nuevo entorno o aplicación es siempre una tarea ardua. Los manuales a menudo nos saturan con todo tipo de datos, no siempre necesarios parar empezar. Hemos puesto especial interés en que esto no sea así en este libro. Para ello, hemos dispuesto unos cuantos capítulos iniciales tratando los aspectos más básicos, lo que le permitirá hacerse una idea rápida de las capacidades y potencialidades de Excel. Estos capítulos iniciales no pretenden desvelar todos los secretos de Excel, sino, más bien, servir de botón de muestra.

El resto de los capítulos abordarán aspectos numéricos de interés para la ciencia y la ingeniería. El hecho de que inicialmente Excel fuera concebida como una aplicación destinada al mundo de la contabilidad y de las finanzas podría hacernos recelar, justificadamente, de su idoneidad para acometer cálculos matemáticos y científico-ingenieriles, para cuyo desempeño existen, por otro lado, programas especializados. Así pues, resulta razonable preguntarse si Excel puede proporcionarnos la precisión y los intervalos numéricos adecuados para dichos cálculos, y si dispone de las funciones matemáticas y de las herramientas adecuadas para implementar modelos, algoritmos, representaciones gráficas, ajustes, resolución de ecuaciones, cálculo diferencial e integral, etc., habituales en las tareas científicas e ingenieriles. Como podremos comprobar a lo largo de todo el libro, Excel proporciona una precisión e intervalos numéricos adecuados, y más capacidades y funciones matemáticas que muchos compiladores, además de ser programable en un lenguaje de alto nivel extraordinariamente sencillo y potente: el VBA.

No obstante, Excel posee carencias notables. Para solventarlas, deberá aprender a programar en el VBA. En esta tarea, quizá prefiera limitarse a ser un mero espectador y usuario de las soluciones que aquí le proporcionaremos. No es una incómoda elección; así se ahorrará el montón de horas de trabajo y esfuerzo que el diseño y puesta a punto que todo código de programación exige. Obviamente, desde aquí le invitamos a que no se limite a eso, e intente introducir variantes en el código, buscando mejoras y nuevas soluciones. Solo así sentirá la verdadera potencia del entorno y la mejor de sus virtudes: la versatilidad y adaptabilidad a todo género de problemas.

Uno de los objetivos de este libro es mostrar al lector que escribir funciones y procedimientos en VBA no es una tarea compleja. Por eso, los temas tratados se presentan de manera que inviten a experimentar con la programación en VBA; esta es la mejor manera de aprenderla. Los ejemplos planteados se centran, a menudo, en aspectos simples, matemáticos, pero en algunos casos (diseminados por todo el libro) su mayor envergadura le permitirá hacerse una idea clara de la potencia del trabajo realizado. El texto, por tanto, será adecuado para científicos e ingenieros profesionales, pero también para sus estudiantes. No olvide, sin embargo, que este libro no ha nacido con el propósito de enseñar a programar, ni enseñar métodos numéricos, aunque le servirá para introducirle y guiarle en ambas tareas. Si no tiene conocimientos básicos sobre estos aspectos, deberá iniciarse consultando, por ejemplo, los libros mencionados en la bibliografía.

El deseo de proporcionar un libro breve, pero garantizando un mínimo de rigor en los temas incluidos, nos obliga a trasladar a un segundo volumen aspectos más avanzados que los tratados aquí. Y es que el campo a explorar es, en verdad, amplio y las posibilidades de Excel lo son aún más.

Esperamos que este libro le convenza de las potencialidades de Excel, y de cómo convertirla en una poderosa aliada para su labor. Iremos poco a poco, pero al final dispondrá de un conjunto de herramientas numéricas útiles al servicio de Excel. Hemos agrupado estas herramientas en un complemento que, fieles a nuestros objetivos marcados, hemos denominado: Excelsior, de sistema integrado optimizador de rendimiento para Excel. Nada más y nada menos.

Comencemos…

Advertencia:

Las imágenes contenidas en el libro referidas a la aplicación Excel corresponden a la versión de Office 365. Si su versión es alguna anterior, las imágenes pueden diferir ligeramente.

Capítulo 1. Una rápida visita

1. Elementos de la interfaz

2. Configuración inicial

2.1 Selección del símbolo decimal

2.2 Añadir la cinta Programador

2.3 Añadir complementos de Excel útiles

3. Celdillas y rangos

4. Cálculos elementales

5. Nombres de celdillas y rangos

6. Formateado de los resultados

7. Operaciones con funciones

8. Operaciones con funciones matriciales

9. Omisión o exceso de paréntesis

10. Representaciones gráficas

11. Líneas de tendencias

Capítulo 1

Una rápida visita

1. Elementos de la interfaz

El nombre de “hoja de cálculo” se debe, lógicamente, a que la principal tarea de esta aplicación es calcular. Como comprobará de inmediato, hacer cálculos con Excel es algo muy sencillo. Abra Microsoft Excel haciendo clic en el icono de dicha aplicación. Tras la tarjeta de presentación del programa verá una pantalla similar a la mostrada en la figura 1.1 (el aspecto puede diferir ligeramente, dependiendo de la versión instalada).

Figura 1.1

Como ocurre con todas las aplicaciones que se ejecutan bajo Windows, Microsoft Excel cuenta con una serie de elementos comunes: la barra de título, los botones de cerrar, maximizar y minimizar o una barra de estado. Son también habituales los menús emergentes, las barras de desplazamiento y la posibilidad de abrir varios documentos simultáneamente (e incluso otras aplicaciones, como corresponde a cualquier plataforma multitarea). Además, cada vez resulta más habitual la denominada cinta de opciones, que sustituye al clásico menú director.

En la misma barra de título, en el margen izquierdo, encontramos la barra de herramientas de acceso rápido. Se trata de una barra que, por defecto, solo incluye un selector (para activar/desactivar la opción de Autoguardado) y tres botones (para guardar el libro abierto, deshacer y rehacer cambios). Esta barra es personalizable y reubicable.

Además de estos elementos genéricos existen otros más específicos, destinados al manejo de las hojas de cálculo que contiene el archivo actual. Como el nombre puede variar dependiendo de la versión de Excel, conviene que observe la figura 1.1, donde se han indicado los distintos nombres. La cinta de opciones tiene un comportamiento dinámico que le permite adaptarse a distintas configuraciones de pantalla. Dependiendo del espacio disponible, mostrará más o menos opciones, manteniendo, en cualquier caso, siempre visibles las de uso más frecuente. Inicialmente, muestra un número concreto de fichas o pestañas (que agrupan distintas funciones que guardan relación entre sí), a las que se agregan algunas otras que se vuelven visibles cuando se trabaja con tablas, gráficas, formas u otros elementos.

Debajo de la cinta de opciones se encuentran dos elementos de suma importancia: el cuadro de referencias y la barra de edición, cuyo funcionamiento describiremos un poco más adelante.

Lógicamente, la mayor parte del área de la pantalla lo ocupa la hoja de cálculo, propiamente dicha. En la imagen capturada de la figura 1.1, se muestra la hoja de cálculo llamada Hoja1 (puede ver este nombre en la pestaña situada en la esquina inferior izquierda de la imagen, señalada por una flecha). Una hoja de cálculo no es más que una tabla constituida por celdillas (o celdas), en las que puede escribir texto, números, fechas, tiempos horarios o fórmulas.

Aunque al comenzar solo existe una hoja, puede añadir cuantas desee haciendo clic en el símbolo ⊕, que está a la derecha de la pestaña Hoja1. Siguiendo el símil, en Excel se denomina Libro al archivo que contiene una o varias hojas de cálculo. Las nuevas hojas que agreguemos recibirán nombres por defecto (Hoja2, Hoja3, etc.), nombres que podremos cambiar, si así lo deseamos. La hoja de cálculo activa en cada momento es aquella cuyo nombre tiene fondo blanco. Para cambiar el nombre de una hoja, selecciónela y haga doble clic sobre su nombre; accederá con ello a un modo de edición que le permitirá hacer el cambio que desee. Puede salir del modo de edición haciendo clic sobre cualquier celdilla. Un modo alternativo que proporciona más posibilidades es hacer clic con el botón derecho del ratón. El menú contextual que le aparecerá le permitirá no solo cambiar el nombre de la hoja activa, sino eliminarla o crear una copia, entre otras opciones.

2. Configuración inicial

Antes de comenzar, conviene que hagamos una configuración inicial mínima en relación a tres aspectos que tienen especial relevancia para las tareas científico-técnicas habituales. Estas tres configuraciones son: 1) selección del símbolo decimal, 2) añadir la cinta Programador y 3) añadir complementos de Excel útiles.

2.1 Selección del símbolo decimal

Como científico o ingeniero no debería tener dudas sobre con qué signo decimal trabajar, si coma o punto. Encontrará más útil trabajar con el punto, porque la mayoría de las publicaciones en lengua inglesa utilizan ese signo. De modo, que ¿cómo indicarle a Excel que utilice el punto decimal y no la coma, que es la que viene por defecto en la versión española de Microsoft Office?

Puede hacerlo de dos formas: modificando la configuración regional del Sistema Operativo Windows, con lo cual su decisión afectará a todos los programas, o hacerlo desde Excel, en cuyo caso solo afectará a la propia Excel.

Cambiar el símbolo decimal en todo Windows 10

Haga clic en el botón de Windows (en el borde inferior izquierdo de la pantalla) y seleccione Configuración, luego elija Hora e idioma → Región e idioma → Opciones adicionales de fecha, hora y configuración regional → Región → Configuración adicional y verá un panel de opciones como el que se muestra en la figura 1.2. Una vez allí, escriba el punto en el cuadro de texto correspondiente al campo Símbolo decimal.

Figura 1.2

Cambiar el símbolo decimal solo en Excel

En el menú superior de la aplicación elegimos Archivo, y luego Opciones, en el menú que aparece en el lateral izquierdo. En el panel que habrá emergido (figura 1.3), seleccionamos la opción Avanzadas (en la sección de la izquierda), y en la sección de la derecha, desactivamos la opción Usar separadores del sistema e introducimos el Separador decimal que queramos utilizar; en nuestro caso, el punto. Tras hacer clic en Aceptar, la elección quedará fijada.

Figura 1.3

2.2 Añadir la cinta Programador

Vaya a Archivo → Opciones → Personalizar cinta de opciones; verá, entonces, un panel de opciones como el de la figura 1.4. En el panel de la izquierda, active la opción Programador (Desarrollador, en algunas otras versiones de Excel).

Al activar esta opción estamos incluyendo la pestaña Programador en la cinta de opciones. Es en esta pestaña donde se encuentra el Editor de Visual Basic (VBA), que es el intérprete (script) que emplearemos a lo largo de todo el libro para ampliar las funcionalidades nativas de Excel.

Figura 1.4

2.3 Añadir complementos de Excel útiles

Vaya a Archivo → Opciones → Complementos; verá un panel de opciones como el de la figura 1.5:

Figura 1.5

En el panel de la izquierda, última línea, seleccionamos la opción Complementos de Excel y hacemos clic en Ir a… Aparecerá el siguiente cuadro de diálogo que se muestra en la figura 1.6. Active todas las casillas, salvo Herramientas para el euro, que no la necesitará.

Figura 1.6

Las Herramientas para análisis constituyen un importante paquete que añade nuevas funciones a Excel (principalmente, estadísticas). El Solver es el motor de resolución de ecuaciones y de optimización que incorpora Excel; una herramienta indispensable en las tareas de cálculo científicotécnico.

A lo largo de todo este libro desarrollaremos nuevas funcionalidades de Excel, mediante la inserción de nuevas funciones programadas en VBA. Todas ellas ya se encuentran reunidas en el archivo Excelsior.xlam, que puede descargar e instalar siguiendo las instrucciones que aparecen en el Epílogo. Puede instalarlo ahora o hacerlo más adelante. Como prefiera.

3. Celdillas y rangos

Como las celdillas de cada hoja de cálculo están organizadas en filas y columnas, la forma más directa de referirse a ellas es a través de una letra, que identifica la columna a la que pertenece, y un número, que identifica su fila. De este modo, la celdilla A1 es la situada en la intersección de la columna A con la fila 1 (por ello, esta notación es conocida como estilo de referencia A1).

Cuando se hace clic con el botón izquierdo sobre una celdilla, esta queda seleccionada (celdilla activa), apareciendo resaltada con un marco con borde más grueso a su alrededor (marco de resalte), tal como se observa en la captura de pantalla de la figura 1.7. La referencia que identifica a la celdilla puede leerse en el cuadro de referencias/nombres, que muestra siempre la celdilla o rango seleccionados (activos, en la jerga de Excel).

Figura 1.7

Las celdillas de una hoja de cálculo de Excel pueden contener texto, números, fechas, tiempos horarios o fórmulas. Excel examina lo introducido en cada celdilla y adapta su contenido según corresponda. Si una celdilla contiene un número, Excel lo almacena como un valor numérico que podrá utilizarse en posteriores cálculos. Si una celdilla contiene una mezcla de texto y números, Excel lo almacena como texto. Si el contenido de una celdilla comienza con un signo igual (), Excel lo almacena como una fórmula. Si el contenido de una celdilla encaja con alguno de los formatos de fecha y tiempo horario admitidos, Excel lo almacenará como un número secuencial (o número de serie). Un número secuencial representa el número de días transcurridos desde el 1/1/1900, más las horas, minutos y segundos adicionales, expresados como fracciones de un día.

Cada celdilla tiene dos propiedades básicas asociadas: su contenido y su valor. El contenido es lo que se escribe en la celdilla y el valor es lo que se muestra en la pantalla. El formato aplicado a una celdilla condiciona su aspecto, pero no afecta a su valor. Para las fórmulas, el contenido es la propia fórmula, tal como se escribió, y el valor es el resultado de la evaluación de dicha fórmula. Para cadenas de texto y números, contenido y valor coinciden.

Un grupo de celdillas se denomina rango, en la jerga de Excel. Un rango de celdillas es una región rectangular de una hoja de cálculo; una tabla o matriz de celdillas, por así decirlo. Para especificar un rango daremos las referencias de las celdillas de las esquinas superior izquierda e inferior derecha de la región rectangular, separadas por dos puntos (:). Por ejemplo, el rango B8:D10 especifica todas las celdillas contenidas dentro del rectángulo que tiene a la celdilla B8 en la esquina superior izquierda y a la celdilla D10 en la esquina inferior derecha (esto es, el rango incluye a las celdillas B8, C8, D8, B9, C9, D9, B10, C10 y D10). Un rango puede contener una única celdilla (como, por ejemplo, B2:B2), una sola fila (B5:D5) o una sola columna (F2:F10). Para referirnos a una columna completa, por ejemplo, la A, emplearemos la notación A:A. No hay ninguna forma equivalente para referirse a una fila completa. La figura 1.8 muestra resaltados los distintos rangos que se han ido mencionando.

Figura 1.8

Para seleccionar un rango basta con hacer clic con el botón izquierdo en la celdilla de una de sus esquinas y, manteniendo pulsado el botón, arrastrar hasta cubrir el área deseada, momento en el que ya se puede liberar el botón. El marco de resalte de selección bordeará toda la región seleccionada.

Es posible combinar varios rangos bajo una sola referencia; basta solo unir las referencias de los distintos rangos con puntos y comas (en la Excel española; comas en la versión inglesa). Por ejemplo, podemos referirnos conjuntamente a los cuatro rangos mostrados en la figura 1.8 mediante la referencia B2:B2; B5:D5; F2:F10; B8:D10. Se dice que el resultante es el rango unión de los individuales.

Si en lugar de un punto y coma utilizamos un espacio para separar los distintos rangos, aludimos con ello únicamente a la intersección de todas las regiones, en lugar de a su unión. En el ejemplo anterior, tanto la unión como la intersección surtirían el mismo efecto, pero no en el ejemplo de la figura 1.9, en donde existen rangos solapados. En este caso, la referencia B3:C8 C4:D5 es equivalente a la C4:C5.

Figura 1.9

Para seleccionar un rango no contiguo a otro ya seleccionado, mantenga pulsada la tecla Ctrl después de seleccionar la primera celdilla o rango, y continúe la selección.

Digamos, por último, que existe otra forma de referenciar a las celdillas, que se denomina estilo de referencias F1C1 (F de fila y C de columna). De acuerdo con este estilo, la referencia F10C2 alude a la celdilla $B$10 (referencia absoluta). En cambio, la referencia F[-2]C[2] alude a la celdilla situada dos filas hacia arriba y dos columnas a la derecha de la celdilla que contiene la fórmula en donde aparece esta referencia. Cuando los números que siguen a la F y a la C se escriben entre corchetes ello indica que representan desplazamientos relativos de filas y columnas. Los números positivos representan desplazamientos hacia abajo y hacia la derecha; los negativos, hacia arriba y la izquierda. Aunque muy útil para determinadas operaciones, emplearemos preferentemente el estilo de referencias A1 en la mayoría de los ejemplos de este libro.

4. Cálculos elementales

Para comprobar lo fácil que resulta hacer cálculos con Excel, haga clic en una celdilla cualquiera de la Hoja1, por ejemplo, la A1, y escriba en ella lo siguiente:

=2*2

Al pulsar la tecla ↵, Excel devolverá, en la misma celdilla donde hemos escrito, el resultado de dicho cálculo, esto es, 4. Para volver a ver la fórmula que habíamos escrito, sitúese de nuevo en la celdilla A1, y mire la barra de edición que se halla enmarcada en la captura de pantalla de la figura 1.10.

Figura 1.10

Los operadores matemáticos definidos en Excel pueden ser binarios o unarios. Los operadores binarios son los de la suma (+), la resta (-), la multiplicación (*), la división (/) y la exponenciación (^). Los operadores unarios son la negación (-) y el porcentaje (%).

=A1^2+2

tal como se muestra en la figura 1.11.

Figura 1.11

Puede introducir espacios e incluso saltos de líneas (pulsando las teclas Alt + ↵) en una fórmula para que esta resulte más legible; los cálculos no se verán afectados.

Una vez escrita la fórmula, pulsamos ↵, y la celdilla que la contiene mostrará entonces el valor 2. Ahora deberíamos repetir el proceso para la fila segunda, la celdilla B2, pero escribiendo la fórmula =A2^2+2. Y así sucesivamente. Tedioso, ¿no? Afortunadamente, Excel nos puede socorrer en esta tarea. El proceso es sencillo como veremos a continuación.

En la esquina inferior derecha del marco de resalte de una celdilla seleccionada, puede comprobar que existe un pequeño cuadradito que llamaremos controlador de autocompletado. Si nos situamos sobre él, la forma del cursor cambiará de aspecto, de cruz hueca () a cruz sólida (+). Pues bien, situémonos sobre una celdilla que contiene una fórmula, como la B1. Movemos el cursor hasta la esquina inferior derecha hasta que este adopte la forma +, y entonces pulsamos el botón izquierdo del ratón y lo mantenemos pulsado mientras nos desplazamos (“arrastramos”) hasta la última línea en la que hay valores escritos en la columna A. En ese momento, liberamos el botón y comprobaremos que todas las celdillas de la columna B se rellenan con la fórmula adecuada. Por adecuada, entendemos ‘convenientemente adaptada’. Por ejemplo, en la celdilla B3, la fórmula que se ha aparecido es =A3^2+2. Se trata de una característica avanzada de Excel que le permite gestionar el autocompletado (relleno automático) con fórmulas adaptadas de manera “inteligente” (figura 1.12).

Figura 1.12

Puede comprobar que una forma, a menudo equivalente, de efectuar el relleno automático es hacer doble clic sobre la cruz sólida, sin necesidad de arrastrar y liberar.

Figura 1.13

Si se fija, las celdillas D1 y D2 aparecen enmarcadas con un borde más grueso. Para conseguir este aspecto, solo tiene que seleccionar las celdillas D1 y D2 y hacer clic con el botón derecho. Aparecerá entonces un menú contextual como el que aparece en la captura de la figura 1.14.

Figura 1.14

Seleccione la pestaña Borde, elija el grosor de la línea en el recuadro de la izquierda, y las opciones de Contorno e Interior para hacer que las líneas se dibujen en el contorno y en el interior. (Como puede comprobar, las opciones no terminan aquí y puede cambiar muchas más características de la celdilla: su fuente, el tamaño de la fuente, el color de tinta, el del color de fondo, y muchas más que iremos viendo cuando proceda.)

Retomando nuestro objetivo, la fórmula que debemos escribir en la celdilla B1 será ahora =A2^2+D2. Sin embargo, si procedemos a arrastrar esta fórmula hacia abajo, obtendremos un resultado no deseado, puesto que la referencia D2 contenida en la fórmula se actualizará a D3, D4 y así sucesivamente, como era de esperar. Por desgracia, en esta ocasión deseamos que D2, que es la celdilla que contiene el valor del parámetro a, no se modifique durante el arrastre. ¿Cómo lo hacemos? Afortunadamente, los diseñadores de Excel contemplaron esta contingencia y dispusieron el modo de conseguirlo. Basta sustituir la fórmula anterior por esta otra =A2^2+D$2. Como puede comprobar, se ha antepuesto un signo $ al número de fila; con esto se le dice a Excel que, en el proceso de arrastre, el número de fila no debe actualizarse. Naturalmente, si el arrastre de la fórmula fuera en horizontal, interesará que la letra de la columna no cambie. La solución sería similar: antepondríamos a la letra el signo $. Cuando una celdilla se referencia con dos signos $ por ejemplo, $D$2, se dice que se trata de una referencia absoluta, y referencia mixta, cuando solo tiene uno de ellos, como $D2 o D$2. La figura 1.15 muestra el resultado obtenido. (Ahora solo tiene que variar el valor del parámetro a y todo será recalculado.)

Figura 1.15

Acostúmbrese a trabajar de este modo, reservando celdillas para los parámetros del problema. Las fórmulas que introduzca incluirán referencias absolutas a las celdillas paramétricas, de modo que el usuario solo tendrá que variar el contenido de dichas celdillas para experimentar los cambios que se producen. Ahorrará así esfuerzo y trabajo.

5. Nombres de celdillas y rangos

Por claridad en nuestros cálculos, podemos asignar un nombre a una celdilla o a todo un rango y usar este nombre en lugar de su referencia por defecto. Para asignar un nombre a una sola celdilla o a un rango, seleccione la celdilla o rango y, a continuación, haga clic en el cuadro de referencias/nombres en el extremo izquierdo de la barra de edición (figura 1.1). Sobrescriba el nombre que desea utilizar para referirse a su selección. Los nombres pueden tener hasta 255 caracteres de longitud, presione finalmente ↵. A partir de ahora, además de emplear su referencia por defecto, la celdilla o rango podrá ser referenciada por su nombre asignado, por ejemplo, en una fórmula. Otra forma de asignar los nombres, con más opciones, es la que proporciona el Administrador de nombres en el grupo Nombres definidos de la pestaña Fórmulas de la cinta de opciones.

Aprovechando el ejemplo anterior, hagamos clic en la celdilla D2, y escribamos en el cuadro de referencias/nombres la letra a. Aparentemente nada ha cambiado. Si hace clic en la celdilla B2, comprobará que la fórmula sigue siendo =A2^2+D$2. Cambie ahora D$2 por a y comprobará que Excel no genera ningún error y efectúa el cálculo correctamente. Estire la fórmula hacia abajo. Cambie el valor de la celdilla D3 y compruebe que todo se recalcula correctamente (figura 1.16).

Figura 1.16

Si desea eliminar el nombre introducido, vaya a la pestaña Fórmulas, y haga clic en el icono de Administrador de nombres. Le aparecerá una caja de diálogo en donde aparecerá la lista de todos los nombres definidos hasta el momento, con opciones de Modificar y Eliminar. Seleccione el nombre a, y haga clic en Eliminar. Salga del cuadro de diálogo haciendo clic en Cerrar. El aspecto de la hoja de cálculo habrá cambiado, tal como se muestra en la figura 1.17.

Figura 1.17

El texto #¿NOMBRE? que aparece en las celdillas de la columna B es un mensaje error de Excel que nos advierte de que las fórmulas contenidas en el rango de celdillas B1:B11 contienen un nombre desconocido. En efecto, se trata del nombre a que acabamos de eliminar. Haga clic en la celdilla B2 y reescriba la fórmula a =A2^2+D$2. Estire la fórmula y todo quedará restablecido. Quizá le parezca que Excel podía haber actualizado las fórmulas y haber procedido a la sustitución automática del nombre a por $D$2. En este caso, los diseñadores de Excel han preferido resaltar la pérdida de un nombre, para advertir al usuario de eliminaciones no voluntarias.

6. Formateado de los resultados

Excel trabaja internamente con una precisión numérica de 15 dígitos. Si en una celdilla se introduce un número que requiera una precisión superior, el número será redondeado convenientemente. Si se trata de un número entero demasiado grande para las capacidades de Excel, entonces este es aproximado convirtiéndolo en un número decimal en coma flotante (para los que Excel dispone de un inmenso intervalo de variación). Si no quiere que esta conversión suceda, anteponga al número el carácter ‘. Excel considerará que todo lo que sigue es texto. Le resultará fácil reconocer que la cadena numérica que acaba de introducir no será considerada por Excel como un número. Observe que aparece alineada a la izquierda dentro de la celdilla, lo que indica que se ha considerado como texto. En cambio, Excel alinea los números a la derecha de la celdilla que los contiene.

El número de cifras significativas que se muestra de un número contenido en una celdilla no tiene que coincidir con el número de cifras con el que fue introducido, pudiendo ser menor o mayor (aunque en este último caso, siempre se rellenaría con ceros). Conviene, por ello, tener claro que, aunque la precisión real de Excel es siempre de 15 dígitos, la precisión aparente, la mostrada, puede ser mayor o menor, según el usuario desee.

Con ayuda de un ejemplo ilustraremos cómo alterar el número de dígitos mostrados. Vamos a construir la tabla formada por los 10 primeros enteros y sus correspondientes inversos. Para ello, escribimos 1 en la celdilla A1, 2 en la celdilla A2, seleccionamos ambas celdillas y, desplazando el cursor hacia el controlador de relleno, cuando este adopte la forma de cruz sólida (+), estiramos la fórmula hacia abajo, hasta la celdilla A10. Como comprobará, las celdillas se rellenarán con números secuenciales. Este relleno inteligente, basado en la detección de patrones, es otra de las serviciales características de Excel.

Escriba a continuación, en la celdilla B1, la fórmula . Sitúe nuevamente el cursor sobre el controlador de relleno y, cuando este cambia a la forma de cruz sólida (+), haga doble clic. Como comprobará, todo el rango de celdillas B1:B10 se habrá rellenado ahora con la misma fórmula, convenientemente adaptada a cada fila. El resultado será similar al mostrado en la figura 1.18.

Figura 1.18

Como se puede comprobar, los resultados mostrados en la columna B poseen un número de decimales distinto. Por defecto, Excel presenta los números con un máximo de 10 dígitos (incluido el punto decimal), ignorando los últimos ceros tras el punto decimal. Si se quiere mejorar el aspecto y armonizar el número de decimales mostrados, una manera rápida de conseguirlo es seleccionando todos los resultados de la columna B, y jugando con los iconos resaltados en la figura 1.19, que permiten aumentar o disminuir el número de decimales mostrados.

Figura 1.19

Una forma alternativa de proceder consiste en modificar directamente el formato de presentación. Con las celdillas de resultados de la columna B seleccionadas, haga clic en el botón derecho del ratón. Accederá con ello a un menú contextual en el que ha de elegir la opción Formato de celdillas… Elíjalo, y en el cuadro de diálogo que aparecerá (figura 1.20) seleccione la categoría Número, y cambie el número de decimales, por ejemplo, a 2.

Figura 1.20

7. Operaciones con funciones

Al igual que cualquier lenguaje de programación, Excel también dispone de funciones que pueden ser incluidas en las fórmulas. Para invocar una función de Excel se ha de escribir su nombre seguido de la lista de argumentos encerrados entre paréntesis, y separados por puntos y comas (por comas en la Excel en inglés). Los argumentos de las funciones pueden ser números, cadenas de texto, referencias de celdillas u otras funciones (con anidamiento de hasta 64 niveles de profundidad).

Los nombres de las funciones pueden escribirse, indistintamente, en mayúsculas o minúsculas; Excel siempre los convertirá a mayúsculas. De hecho, constituye una buena práctica escribir los nombres de las funciones en minúsculas; si Excel no los convierte a mayúsculas es que no los reconoce y muy probablemente habremos cometido algún error ortográfico al escribirlos.

Para evitar estos errores ortográficos, o si no recordamos el orden correcto de los argumentos, es posible insertar una función haciendo uso del asistente Insertar función. Comience a escribir una fórmula y cuando llegue al punto donde desea insertar la función, seleccione la pestaña Fórmulas de la cinta de opciones y haga clic en Insertar función. Aparecerá el cuadro de diálogo del asistente Insertar función mostrando una lista de las funciones disponibles (figura 1.21), clasificadas por categorías, y una breve ayuda, muy útil, del cometido de la función actualmente seleccionada.

Figura 1.21

Seleccione la función que desee y haga clic en Aceptar y se mostrará un segundo cuadro de diálogo que le ayudará a escribir los argumentos (figura 1.22). Puede escribir directamente en los cuadros de texto habilitados para ello o hacer clic en los iconos situados a la derecha de estos cuadros de texto, lo que le permitirá seleccionar con el ratón las celdillas o rangos de celdillas que contienen los datos.

Figura 1.22

El número de argumentos puede variar. Hay funciones que no necesitan ningún argumento como =PI(), que devuelve el valor del número π, 3.14159265358979. Hay funciones con un solo argumento como, por ejemplo, =RAIZ(A1), que da la raíz cuadrada del valor contenido en la celdilla. Hay funciones que esperan dos argumentos como =REDONDEAR(PI(); 4), que redondea el valor de devuelto por PI() a 4 decimales, devolviendo 3.1416. Hay funciones que admiten un número variable de argumentos, como, por ejemplo, =SUMA(A1:A10; B12; C3) que suma los valores de todas las celdillas especificadas en los argumentos (advierta que unos argumentos son rangos y otros, celdillas simples).

Cuando el número de argumentos es variable, el número máximo permitido ellos es 255 y el máximo número de caracteres es 8192. Un rango cuenta como un único argumento.

8. Operaciones con funciones matriciales

Habitualmente, las funciones de Excel devuelven un único valor. Sin embargo, algunas funciones de Excel devuelven una matriz de valores. Por ejemplo, la función MINVERSA( ) calcula la inversa de una matriz cuadrada y devuelve otra matriz cuadrada del mismo orden. Supongamos que los valores de la matriz original se encuentran ubicados en el rango A1:C3 y que la matriz inversa ha de escribirse en el rango E1:G3.

Para insertar una función en todo un rango de celdillas, seleccionamos primeramente el rango de celdillas donde deben escribirse los resultados (en nuestro caso, el E1:G3). Escribimos la función con sus correspondientes argumentos en la celdilla superior izquierda del rango seleccionado. A continuación, manteniendo pulsadas las teclas Ctrl y , pulse ↵ o haga clic en el icono ✓ de la barra de edición que ahora estará activo. Comprobará que la fórmula introducida aparece ahora encerrada entre llaves: {=MINVERSA(A1:C3)}, pues esta es la forma con la que Excel nos indica que se trata de una función matricial. (No intente escribir las llaves desde el teclado, pues Excel interpretaría como texto la entrada; no funciona así.) La figura 1.23 muestra el resultado del ejercicio descrito:

Figura 1.23

El principal inconveniente de esta forma de introducir las fórmulas matriciales es que el usuario ha de seleccionar previamente el rango de respuesta, por lo que debe conocer sus dimensiones. Si el rango seleccionado resultase demasiado pequeño, se perdería información, sin que Excel produjese error o aviso alguno. Si el área seleccionada fuera demasiado grande, las celdillas sobrantes se rellenarían con el aviso/error #N/D.

Por eso, una mejora largamente deseada por los usuarios de Excel ha sido el que las fórmulas matriciales pudieran introducirse como una fórmula más, en una sola celdilla y con solo pulsar ↵, y que fuese Excel la encargada de extender, de esparcir, la fórmula a las celdillas adyacentes cuando fuera necesario. Pues bien, los usuarios de Excel 365 están de enhorabuena porque esa característica, largamente anhelada, ya está a su disposición.

Las nuevas fórmulas matriciales automáticamente esparcibles de Excel se introducen en una sola celdilla, pulsando ↵, y es Excel la encargada de esparcir la fórmula a todo el rango afectado. En la figura 1.24 se puede ver el resultado del mismo problema que se resolvió en la figura 1.23, pero aprovechando la nueva funcionalidad.

Figura 1.24

Observe en la figura 1.24 que, cuando el usuario tiene seleccionada una celdilla del rango esparcido, este aparece con un borde de realce que lo delimita. Dicho borde desaparecerá en cuanto el usuario seleccione una celdilla fuera de él. Observe, además, que las fórmulas matriciales ya no aparecen encerradas entre llaves en la barra de fórmulas.

Si se desplaza por el rango esparcido, comprobará que solo se puede editar la primera celdilla de dicho rango. Si selecciona cualquier otra celdilla de dicho rango, la fórmula estará visible en la barra de fórmulas, pero no será editable. Por esta razón, la forma de referenciar a un rango con fórmula esparcida es aludir a su primera celdilla seguida de #. Por ejemplo, en nuestro caso, el rango se podría referenciar como E1#.

Si durante el esparcido de la fórmula, Excel tropieza con una celdilla ya rellena, generará un error, que, muy desafortunadamente, en las primeras versiones se ha traducido por #¡DESBORDAMIENTO! (#SPILL! en la versión original). Un mensaje de error mucho más apropiado sería #¡ESPARCIR!

Naturalmente, incluso en Excel 365, puede seguir utilizando la forma manual de introducir las fórmulas matriciales. En lo que sigue supondremos, de modo general, que no dispone de esa potente característica (si dispusiera de ella, sencillamente, todo sería más fácil).

Con o sin esta nueva funcionalidad, siempre podrá aplicar una fórmula escalar a toda una matriz de valores. Por ejemplo, la siguiente fórmula matricial, introducida en el rango E1:G4, convierte en enteros todos los valores contenidos en el rango origen A1:C4

{=ENTERO(A1:C4)}

Con una versión anterior a la de Excel 365, si la fórmula no hubiese sido introducida matricialmente, esto es, sin mantener presionadas las teclas Ctrl + , solo se hubiese devuelto un único valor; el correspondiente de convertir a entero el valor contenido en la primera celdilla del rango. Con Excel 365, en cambio, el resultado habría sido el mismo que el conseguido al introducirla matricialmente. La posibilidad de convertir en matricial cualquier fórmula es, sin duda, una potente característica. Por ejemplo, esta otra fórmula, introducida de manera matricial, sumaría verdaderamente dos matrices, las contenidas en los rangos A1:C4 y E1:G4.

{=A1:C4 + E1:G4}

Pero ¡cuidado!, esta otra fórmula

{=A1:C4 * E1:G4}

9. Omisión o exceso de paréntesis

Un fallo muy frecuente se produce cuando al escribir una fórmula el número de paréntesis abiertos y cerrados en una fórmula no coincide. Tras escribir una fórmula en una celdilla, al pulsar ↵, o hacer clic en el icono ✓, Excel revisará la fórmula introducida y si encuentra que el número de paréntesis está desequilibrado, lanzará un cuadro de diálogo proponiendo una solución que debe aceptar o rechazar el usuario (figura 1.25). Si la fórmula que nos sugiere Excel es correcta, haga clic en Sí, y si no lo es, haga clic en No y modifíquela a mano. La solución sugerida por Excel no siempre acierta; si faltan paréntesis, tiende a añadirlos al final de la fórmula, lo que no siempre es lo que queremos. Por lo tanto, analice cuidadosamente la opción propuesta antes de aceptarla.

Figura 1.25

Para evitar la omisión o exceso de paréntesis, Excel ofrece dos pistas visuales en la propia fórmula cuando la estamos modificando. La primera pista aparece cuando escribimos un paréntesis de cierre. Excel marca tanto el de inicio como el correspondiente de cierre. Si escribimos el que creemos que es el último paréntesis de cierre y Excel no marca el primero de inicio, significa que faltan o sobran paréntesis. La segunda aparece cuando utilizamos las teclas de flecha derecha o izquierda para navegar por una fórmula. Al pasar sobre un paréntesis, Excel marca su pareja correspondiente con el mismo color.

Consejo: Al editar una fórmula, escriba siempre los pares de paréntesis, y luego complete el interior. Además, es una buena práctica dividir las fórmulas largas o complicadas en varias más simples. Cuando esté seguro de que las fórmulas simples funcionan, podrá combinarlas para conseguir resultados computacionalmente más eficientes.

10. Representaciones gráficas

Suponga que hemos introducido un conjunto de parejas de datos (x e y), procedentes de la monitorización de algún experimento, en las columnas A y B de nuestra hoja de trabajo. Para hacer una representación gráfica de dichos datos, primero debe hacer clic en la primera celdilla de los datos (la A2) y luego debe arrastrar el cursor hacia abajo y a la derecha hasta incluir todas las celdillas que contienen datos. (Recuerde que si las columnas de datos no son contiguas, debe seleccionar la primera columna y, manteniendo presionada la tecla Ctrl, seleccionar la segunda columna.) Alternativamente, puede hacer clic en el encabezado de la columna A y luego mover el cursor hacia la derecha para seleccionar la columna B.

Una vez seleccionados los datos a representar, desplácese al menú superior, seleccione la pestaña Insertar de la cinta de opciones y coloque el cursor sobre el icono de Insertar gráfico de dispersión (X,Y) o de burbujas, como se ve en la figura 1.26. El tipo de gráfico de dispersión es el único que tiene el eje X como una variable, lo que le permite representar valores de x no equiespaciados (en el resto de los gráficos, los valores de x deben estar equiespaciados, por lo que realmente son tratados como categorías no numéricas).

Figura 1.26

Haciendo clic en dicho icono, accederá a un cuadro de diálogo que le permitirá elegir entre varias opciones: puntos de datos no enlazados, puntos enlazados con líneas rectas, o enlazados con líneas curvas suavizadas. Seleccione el que se muestra a la izquierda y aparecerá su gráfico (figura 1.27). Realmente, no es importante la opción que elija en este momento: siempre podrá modificar, posteriormente, el tipo de línea, colores, estilo, etc.

Figura 1.27

Observe que las dos columnas que contienen los datos aparecen resaltadas (las x con recuadro magenta, y las y con recuadro azul). Estos recuadros son interactivos; de existir otra columna de datos, por ejemplo, la C, podría desplazar el recuadro azul para seleccionar los datos de la columna C; todo de un golpe.

Solo cuando un gráfico está activo (se ha hecho clic en él), en la cinta de opciones aparecerá la pestaña de Diseño de gráfico. Selecciónela y haga clic en el icono Agregar elemento de gráfico, situado a la izquierda. Se desplegará un menú con distintas acciones frecuentes: añadir Ejes, añadir o editar los Títulos de eje, el Título del gráfico, las etiquetas de datos, la leyenda, etc. (figura 1.28).

Figura 1.28

Pero existe un atajo para llegar a todas esas acciones. Al seleccionar el gráfico habrá comprobado que aparecen tres iconos en la esquina superior izquierda del gráfico. Haga clic en el primero de ellos, el que tiene forma de , y accederá a un conjunto de acciones frecuentes relacionadas con añadir nuevos elementos al gráfico, tal como se muestra en la figura 1.29.

Figura 1.29

Ensaye con todas estas opciones, y con los iconos que aparecen en la esquina superior derecha del gráfico, cuando este está seleccionado. Comprobará el carácter amigable de Excel en toda su profundidad.

11. Líneas de tendencias

Excel tiene la capacidad de realizar una variedad de ajustes por regresión (o ajustes de tendencia) de datos, proporcionando siempre el coeficiente de determinación, R2, que cuantifica la bondad del ajuste. Para añadir una línea de tendencia a la nube de puntos ya representada, seleccione el gráfico (haciendo clic en él), seleccione la pestaña Diseño de gráfico de la cinta de opciones y, en el grupo Agregar elemento de gráfico, seleccione Línea de tendencia. En el nuevo menú que se desplegará, seleccione la opción Más opciones de línea de tendencia... (figura 1.30), para acceder a un panel (en el lado derecho de la pantalla) con múltiples opciones sobre el ajuste de regresión a realizar.

Figura 1.30

Como de costumbre, existe otra forma de hacerlo. Haga clic sobre uno de los puntos de la nube representada, y entonces haga clic con el botón derecho del ratón. En el menú contextual que le aparecerá, elija Agregar línea de tendencia…, lo que le dará acceso al mismo panel que antes, y que se muestra en la figura 1.31.

Figura 1.31

Este panel le permite elegir el tipo de curva de regresión que mejor considere que se adecúa a la nube de puntos. Elija Polinómica de Grado 2. Active las casillas de verificación correspondientes a las opciones Presentar ecuación en el gráfico y Presentar el valor R cuadrado en el gráfico, si quiere que aparezca una leyenda en el interior del gráfico con esa información, tal como se muestra en la figura 1.32. Tenga en cuenta que puede editar el contenido de dicha leyenda, si lo desea, por ejemplo, como habitualmente necesitará, para cambiar los nombres de las variables x e y.

Figura 1.32

Un valor R2 igual a la unidad representa un ajuste perfecto de la curva de tendencia a los datos. Multiplicando R2 por 100, obtenemos el porcentaje de variación de la variable y causado por la variación de la variable x. A medida que aumenta la dispersión en los datos, el valor de R2 disminuye.

Lo que hemos visto no es más que un rápido muestrario de las capacidades inmediatas de Excel relativas a las tareas frecuentes en ciencia e ingeniería. Naturalmente, hay muchas más. Pero tiene todo el libro por delante para familiarizarse con ellas.

Capítulo 2. Operadores y funciones de Excel

1. Precisión e intervalos numéricos

2. Valores de error

3. Operadores y precedencia

4. Inventario de funciones de Excel

5. Funciones matemáticas

5.1 Funciones matemáticas básicas

5.2 Funciones logarítmicas

5.3 Funciones trigonométricas

5.4 Funciones hiperbólicas

5.5 Funciones para matrices

5.6 Funciones lógicas

5.7 Funciones de ingeniería

6. Funciones estadísticas

6.1 Funciones estadísticas básicas

6.2 Funciones estadísticas avanzadas

6.3 Funciones de ajuste de curvas

6.4 Funciones estadísticas de distribución y prueba

6.5 Funciones de base de datos

6.6 Funciones de cubos

7. Funciones informativas

8. Funciones de búsqueda y referencia

8.1 Funciones de búsqueda

8.2 Funciones de referencia

9. Funciones de texto

10. Funciones de fecha y hora

10.1 Funciones de fecha

10.2 Funciones de hora

11. Otras funciones

11.1 Funciones financieras

11.2 Funciones instaladas con complementos

11.3 Funciones web

12. Una crítica acerca de los nombres de las funciones

Tareas propuestas

Capítulo 2

Operadores y funciones de Excel

1. Precisión e intervalos numéricos

Aunque la precisión numérica no suele ser importante cuando los cálculos no son demasiado complejos, es cierto también que muchos algoritmos numéricos (como los iterativos) son bastante sensibles a esta característica. En la tabla 2.1 se muestran los límites para datos numéricos y de tiempos en Excel.

Tabla 2.1 Límites numéricos y de tiempos en Excel

Característica

Límite máximo

Precisión numérica

15 dígitos

Número negativo más bajo permitido

-2.2251E-308

Número positivo más bajo permitido

2.2251E-308

Número positivo más alto permitido

9.99999999999999E+307

Número negativo más alto permitido

-9.99999999999999E+307

Número positivo más alto permitido mediante fórmula

+1.7976931348623158E+308

Número negativo más alto permitido mediante fórmula

–1.7976931348623158E+308

Primera fecha permitida en un cálculo

1 de enero de 1900 (1 de enero de 1904, si se usa fecha del sistema)

Última fecha permitida en un cálculo

31 de diciembre de 9999

Período de tiempo máximo que puede escribirse

9999:59:59

Excel mantiene en sus cálculos una precisión numérica interna de 15 dígitos, aunque redondea el número para la visualización en pantalla, de acuerdo con el formato de la celdilla que lo contiene. Como puede comprobarse en la tabla 2.1, esta precisión le permite a Excel almacenar números entre –1.798×10+308 y +1.798×10+308, lo que proporciona un intervalo numérico de ±10+308. (En comparación, una típica calculadora científica almacena 11 o 12 dígitos, muestra solo 10 dígitos, y puede operar en un intervalo numérico de solo ±1099.) Realmente, estas características y limitaciones no son propias de Excel, sino de la arquitectura del hardware (microprocesador) para la que está implementada, por lo tanto, son las mismas que tendrá cualquier compilador desarrollado para el mismo microprocesador.

Aunque el número más grande que Excel puede almacenar es 1.798×10+308, el número más grande que puede escribir es de ±9.999×10+307. Si escribe un valor mayor en valor absoluto, Excel lo tratará como una cadena de texto.

La mayoría de los resultados científico-ingenieriles implican números comprendidos en el intervalo de 10-40 a 10+40. Pero cuando estos números se utilizan en una ecuación, los resultados intermedios pueden ser bastante más grandes o más pequeños. Si un resultado intermedio excede el intervalo de la máquina, se producirá un error de desbordamiento.

Con un intervalo de 10±308, no es probable que llegue a toparse con este problema en Excel. Si aun así, se las arregla para provocarlo, Excel devolverá el valor de error #¡NUM!, con objeto de marcar la celdilla, y cualquier otra que dependa de ella, como afectada de error. Si un número desborda el límite inferior (debido a que es menor que ±2.225×10-308), Excel almacenará su valor como cero, sin generar error.

2. Valores de error

El error de desbordamiento mencionado en el apartado anterior es solo un ejemplo. Lo cierto es que, en todo proceso de cálculo complejo, los errores son inevitables, por lo que cualquier aplicación destinada a calcular debe tener previsto cómo manipular y gestionar la aparición de errores. Excel considera los siete valores de error como si fueran números (códigos de error) que se muestran en la tabla 2.2.

Tabla 2.2 Valores de error en Excel

#¡DIV/0!

División por ceroEn la mayoría de los casos, este valor indica que una fórmula está intentando dividir entre cero, que es una operación matemática no permitida. La causa suele ser que en la fórmula interviene la referencia a una celdilla en blanco o que contiene el valor cero. Conviene, entonces, revisar las celdillas a las que se hace referencia en la fórmula (directa o indirectamente) para encontrar el origen del error

#¿NOMBRE?

Un nombre de variable o función en una fórmula no se ha definidoSe produce normalmente al escribir erróneamente el nombre de una función o celdilla. Por ello, es importante revisar las fórmulas, corregir los nombres de rangos o de funciones, e insertar comillas o puntos omitidos. Cuando introduzca nombres de funciones y nombres definidos, escriba en letras minúsculas. Cuando Excel reconoce un nombre, si se trata de una función, pasa dicho nombre a mayúsculas; si se trata del nombre definido de una celdilla o rango, lo formatea tal como fue definido. Si no se lleva a cabo ninguna conversión, ello significa que el nombre está mal escrito, que aún no está definido, o que está utilizando una función de un componente que no está instalado. Este error también se produce cuando se ha utilizado un nombre de rango en una fórmula y, posteriormente, se ha eliminado dicho nombre

#N/D

El valor no está disponibleSi se fija, este error es el único cuyo nombre no va entre admiraciones o interrogaciones, lo que significa que no requiere tanta atención por parte del usuario. Es más bien un aviso. (En versiones anteriores de Excel aparece como #N/A)

#¡NULO!

Un resultado no existe, o es una intersección no válida de dos rangosPor ejemplo, los rangos A1:B2 y D3:E5 no tienen celdillas en común, por lo tanto, la referencia A1:B2 D3:E5 que debería dar su intersección, generará este error

#¡NUM!

Desbordamiento numérico o uso incorrecto de un númeroNormalmente, se produce cuando se ha introducido un argumento incorrecto en una función matemática o trigonométrica. Por ejemplo, un número negativo como argumento de una función RAIZ( ) o LOG( )

#¡REF!

Referencia de celdilla no válida; la celdilla no está en la hoja de cálculoNormalmente, se produce porque hemos eliminado una celdilla a la que apunta una fórmula. Hay que recuperarla o ajustar la referencia de la fórmula. También se produce si hemos cortado una celdilla y la hemos pegado en una que utiliza la fórmula. En este caso, hay que deshacer la acción y pegar la celdilla en otro sitio

#¡VALOR!

Tipo de argumento no válidoSe produce frecuentemente cuando una función que espera un argumento numérico recibe un texto, no convertible en número

Cuando uno de estos valores de error interviene en una fórmula, el resultado de la fórmula también será el mismo valor de error. Por lo tanto, los valores de error se propagan a través de una hoja de cálculo, marcando todos los valores que dependen de ellos como erróneos. Esta propagación de errores asegura que no se pueda utilizar un número calculado a partir de los datos erróneos.

Si bien no es un auténtico valor de error, a menudo veremos en alguna celdilla algo parecido a #########. Este mensaje es frecuente, y sucede por dos razones: el valor que se ha introducido en una celdilla o el resultado de evaluar la fórmula que contiene es más largo que el ancho de la celdilla, o bien se ha introducido una fecha negativa.

Si vuelve a la tabla 2.2, comprobará que no existe ningún error asociado al desbalanceo del número de paréntesis abiertos y cerrados en una expresión, pese a que se trata de una circunstancia bastante frecuente. La razón es que, cuando esto se produce, Excel consulta al usuario qué hacer, a la vez que le sugiere una solución.

La inclusión de funciones en las fórmulas puede complicar su sintaxis y favorecer la presencia de errores. Por ello, es aconsejable dividir las fórmulas muy extensas en partes más simples, siempre podrá reagruparlas después, cuando haya comprobado que todo funciona correctamente. Tenga en cuenta que los errores más difíciles de diagnosticar son los debidos a fallos internos de Excel, principalmente ocasionados por falta de sincronización en la evaluación de fórmulas; una fórmula podría devolver un resultado erróneo porque no se recalcularon otras de las que depende. Por tanto, conviene recalcular todas las fórmulas contenidas en una hoja, lo que se consigue pulsando F9. Especialmente, esto suele ocurrir cuando la hoja contiene funciones creadas por el usuario. Si este es el caso, pulse la combinación Ctrl + Alt + F9 para forzar la revaluación de todas las fórmulas (predefinidas y definidas por el usuario) contenidas en la hoja.

3. Operadores y precedencia

Excel proporciona tres tipos de operadores: aritméticos, de texto y comparativos. En la tabla 2.3 se enumeran todos los operadores disponibles en Excel.

Los operadores aritméticos son los operadores aritméticos habituales: + (suma), – (resta), * (multiplicación) y ^ (potenciación). Además, el operador – es también el operador negación, actuando como unario. Excel no proporciona un operador unario positivo, lo que significa que, si no existe un operador unario delante de un número, el número se supone positivo. De hecho, puede comprobar que, si inserta un + delante de un valor o fórmula, Excel lo elimina (excepto cuando está en el exponente de un número en notación científica).

El único operador de texto es el de concatenación (&), que se utiliza para unir dos cadenas de texto en una sola.

Los operadores comparativos se utilizan para comparar dos valores numéricos o cadenas de texto. El resultado de la operación es el valor VERDADERO o el valor FALSO. Cuando utiliza resultados lógicos en fórmulas matemáticas, VERDADERO tiene el valor 1 y FALSO adquiere el valor 0. (Tenga presente que cuando Excel compara cadenas de texto no distingue entre mayúsculas y minúsculas.)

La precedencia de los operadores determina el orden en que han de actuar durante la evaluación de una fórmula. En la tabla 2.3 también se muestra el orden de precedencia de cada operador. En cualquier cálculo, se efectúan primero los operadores con una precedencia mayor, y luego los de precedencias inmediatamente inferior, y así sucesivamente. Si hay dos operaciones con igual precedencia en una fórmula, se evalúan de derecha a izquierda.

Si no está seguro de cómo se evaluará una fórmula, utilice paréntesis para forzar la fórmula a evaluar en el orden que desea.

Tabla 2.3 Operadores de Excel y orden de precedencia

Operador

Descripción

Orden de precedencia

Operadores aritméticos

–

Negación (unario: actúa sobre el valor a su derecha)

1

%

Porcentaje (unario: actúa sobre el valor a su izquierda)

2

^

Exponenciación

3

*

Multiplicación

4

/

División

4

+

Suma

5

–

Resta

5

Operadores de texto

&

Concatenación

6

Operadores comparativos

Igual a

7

<

Menor que

7

>

Mayor que

7

<=

Menor o igual que

7

>=

Mayor o igual que

7

<>

Distinto de

7

4. Inventario de funciones de Excel

Las funciones de Excel son herramientas que ejecutan cálculos complicados de forma fácil y rápida. Toda función realiza alguna operación sobre sus argumentos y devuelve un valor o conjunto de valores. En la tabla 2.4, se muestran los límites operativos relativos a las funciones de Excel: el número máximo de argumentos, el número máximo de anidamiento, etc.

Tabla 2.4 Limitaciones de las funciones de Excel

Característica

Límite máximo

Longitud del contenido de una fórmula

8192 caracteres

Longitud interna de la fórmula

16 384 bytes

Iteraciones

32 767

Argumentos en una función

255

Niveles anidados de funciones

64

Categorías de funciones definidas por el usuario

255

Número de funciones nativas disponibles

341

Excel está equipada con más de 340 funciones predefinidas y listas para su uso. Tan elevado número hace imposible acometer la descripción detallada, pormenorizada y con ejemplos de uso de todas ellas (ello requeriría un libro completo). Así pues, aquí solo mostraremos las de mayor interés junto a una brevísima descripción de su cometido. Solo las funciones consideradas más relevantes recibirán un tratamiento más detallado. Por fortuna, el usuario siempre podrá contar con la magnífica ayuda en línea de Excel, que incluye descripción detallada y ejemplos de uso de todas las funciones.

Para acceder a la información de un número tan grande de funciones, y así facilitar su búsqueda, Excel las clasifica en 14 grupos o categorías, tal como se muestra en la tabla 2.5. Nosotros seguiremos, más o menos, esa clasificación, aunque de manera flexible.

Tabla 2.5 Categorías de funciones de Excel

Funciones matemáticas y trigonométricas Funciones de ingeniería

Funciones lógicas

Funciones estadísticas

Funciones de cubo

Funciones de base de datos

Funciones de texto

Funciones de fecha y hora

Funciones informativas