1.3: Introducción a las hojas de cálculo Excel

Última actualización
Guardar como PDF

Page ID: 115971

$ \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } $ $ \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} $$\newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\kernel}{\mathrm{null}\,}$ $ \newcommand{\range}{\mathrm{range}\,}$ $ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$ $ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$ $ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$ $ \newcommand{\Span}{\mathrm{span}}$ $\newcommand{\id}{\mathrm{id}}$ $ \newcommand{\Span}{\mathrm{span}}$ $ \newcommand{\kernel}{\mathrm{null}\,}$ $ \newcommand{\range}{\mathrm{range}\,}$ $ \newcommand{\RealPart}{\mathrm{Re}}$ $ \newcommand{\ImaginaryPart}{\mathrm{Im}}$ $ \newcommand{\Argument}{\mathrm{Arg}}$ $ \newcommand{\norm}[1]{\| #1 \|}$ $ \newcommand{\inner}[2]{\langle #1, #2 \rangle}$ $ \newcommand{\Span}{\mathrm{span}}$$\newcommand{\AA}{\unicode[.8,0]{x212B}}$

Este libro se apoyará en gran medida en el uso de Excel, ya que es la herramienta matemática estándar utilizada en el mundo de los negocios. (Más precisamente, la hoja de cálculo es la herramienta estándar, y Excel es actualmente la marca estándar de facto. La mayor parte de este texto se puede utilizar fácilmente con otras hojas de cálculo.) No obstante, no asumimos que el alumno haya trabajado previamente con Excel. A lo largo del curso presentaremos aquellas características de Excel que necesitamos para hacer matemáticas y modelar los problemas de negocio que encontremos.

Al introducir Excel, también introduciremos reglas de “Buena práctica de Excel”. En un entorno empresarial, las hojas de cálculo deben escribirse para que otra persona pueda entender fácilmente la hoja de trabajo y mantenerla para uso futuro. Debes asumir esos mismos estándares al momento de enviar trabajos en Excel.

Esta sección está pensada como una introducción a varias características estándar de Excel que usaremos a menudo. Estos incluyen:

Aritmética básica como sumar, restar, multiplicar etc.
Mostrar fórmulas: nos permite verificar si las fórmulas en las celdas son lo que deberían ser.
Relleno rápido: esta función toma un patrón y lo llena a través de una columna o una fila.
Referencia relativa y absoluta: ¿cuándo nos referimos a una celda fija y cuándo depende la referencia de nuestro lugar en la hoja de cálculo?
SUM (): Agregar una gran cantidad de celdas se puede hacer de manera eficiente con esta característica.

Aritmética básica, mostrar fórmulas y relleno rápido..

$Figure \text { } 1.3.1.$Video presentación de este ejemplo

Comenzamos con un ejemplo que abarca la aritmética básica. Supongamos que se nos da la siguiente hoja de trabajo:

$clipboard_e3c8121c1db616e94214e1c011a686a7f.png$

A partir de los datos nos gustaría calcular las cifras del trimestre (sumar los tres meses), el beneficio mensual (ingresos-costo) y los costos unitarios mensuales (costos/ unidades vendidas).

Con la cinta de fórmulas, vamos a mostrar y seleccionamos Mostrar fórmulas. Como queremos que la hoja de trabajo sea legible por otros, agregamos etiquetas para las cantidades que estamos calculando, y en cada celda ingresamos la fórmula para la cantidad. La barra de fórmulas nos indica qué celda se ha seleccionado y la fórmula para esa celda. Por lo general, es más fácil editar una fórmula usando la barra de fórmulas.

En este ejemplo, hemos utilizado varias formas diferentes de escribir la fórmula. En las celdas E2, B6 y B7 simplemente escribimos la ecuación como lo haríamos en una calculadora. Por lo tanto, la ganancia para enero es Ingresos — Costos, o 3600-2700. Como queremos que Excel calme este valor, ponemos un signo de igual al inicio de la fórmula.

En las celdas E3, C6 y C7, en lugar de escribir los valores, usamos una referencia a la celda donde se guarda el valor. Esto nos permite cambiar los datos brutos y hacer que Excel vuelva a calcular automáticamente las cantidades que se derivaron de esos números.

En las celdas E4 y E5 utilizamos el comando SUM de Excel. En la celda E4, estamos tomando la suma de los valores en las celdas de B4 a D4. Volveremos a los comandos en Excel más adelante en la sección.

$clipboard_e831c88463a90b6d3fe421ff323160082.png$

Si deseleccionamos Mostrar fórmulas, vemos los valores que calcula Excel.

$clipboard_e4031372f31e748472834e9f501ca7e17.png$

Queremos terminar nuestra tarea calculando el Beneficio y los Costos por Unidad para Marzo y para el Trimestre. No obstante, preferiríamos no escribir más fórmulas. (Escribir en cuatro celdas más no es tan malo, pero podemos imaginar que nos digan que hagan esto durante varios años de datos). Utilizaremos un proceso llamado Quick Fill, que le dice a Excel que repita la misma fórmula, con las referencias de celda debidamente modificadas.

Para hacer el relleno rápido, seleccionamos las celdas que queremos copiar.

$clipboard_e54a6302e4134f438e24657acd967f5a3.png$

Podemos mover el cursor hasta que la (s) celda (s) muestre el manejador de relleno. Esto cambiará el símbolo en la esquina de la celda a un delgado '+' oscuro.

$clipboard_ee2eba9fde3f67c0cfa7f030d20526585.png$

Luego arrastramos el pequeño cuadro azul en la esquina inferior izquierda del cuadro de celdas seleccionadas. Excel rellena automáticamente los nuevos valores.

$clipboard_e430c14bb34ea4d616756320a110837f8.png$

Miramos hacia atrás en las fórmulas y vemos que Excel ha producido fórmulas donde las celdas están en la misma posición relativa. El beneficio es el valor de la celda 4 filas más altas menos el valor de la celda tres filas más altas.

$clipboard_e584e5f50ffbb53f195a1a647a70549b5.png$

Hay un último detalle que fijar en nuestro informe. Las cantidades en beneficio y Costo por Unidad están en dinero, por lo que queremos que se formateen correctamente. (Deben comenzar con un signo de dólar, tener un punto decimal y detenerse en dos decimales o centavos). Esto lo hacemos seleccionando las celdas y luego formateando las celdas como moneda.

$clipboard_e3e1e28e006437a423eb8f722a762e4b9.png$

Si usamos el relleno rápido en un par de números, Excel produce una secuencia aritmética. Un par de celdas que contienen 1 luego 4 se convierte en el inicio de una secuencia 1, 4, 7, 10,...

Referencias Celulares Absolutas y Relativas.

$Figure \text { } 1.3.2.$Video presentación de este ejemplo

Una de las razones por las que las hojas de cálculo son tan útiles para hacer matemáticas en un entorno empresarial es que las empresas a menudo hacen un cálculo relativamente simple para un gran número de casos. Eso significa que debemos prestar atención a las fórmulas con referencias de celda y al proceso de copiar una fórmula de un caso a otro. En el ejemplo anterior, todos los valores cambian de un mes al siguiente. No es difícil imaginar un cálculo donde algunos valores sigan siendo los mismos para muchos casos. Así queremos mirar la idea de referencias celulares absolutas y relativas. Este es un tema muy importante y una característica de Excel que usaremos para el resto del término.

Considera el siguiente ejemplo: Tu tío rico Fred decidió entregarte 10 acciones de Google (GOOG) el primero de enero de 2009, con la opción de recibir en cambio el mismo valor ya sea en acciones de Microsoft (MSFT) o Apple (AAPL). Te gustaría ver el cambio mensual en el valor de las carteras a lo largo de un periodo de tres años.

Comenzamos yendo a finance.yahoo.com y recogiendo los precios mensuales de las acciones, descargando las respuestas en una hoja de cálculo. Cuando buscamos precios históricos de yahoo, nos interesa el precio de cierre ajustado. (Ajustan el precio para contabilizar el cuidado de escisiones y dividendos.) Eso produce una hoja de cálculo como la de abajo.

$clipboard_e6093ef1e6db40528f5bf976b05b733dc.png$

A continuación queremos calcular el número de acciones para cada acción. Esto es 10 veces el precio de cierre de Google dividido por el precio de cierre de las acciones que seleccionamos.

$clipboard_ef90a33e18cd24236439b71c371cb0c3c.png$

En la fórmula para el número de acciones de MSFT, utilizamos $ B $3 para el precio inicial de GOOG. Esta es una referencia de celda absoluta. Cuando copiamos la fórmula de la celda C1 a la celda D1, la nueva fórmula es =10*$B $3/D3. Esta fórmula en la celda D1 pide 10 veces el valor en la celda B3, dividido por el valor en las 2 filas debajo de la celda de la fórmula.

Las referencias absolutas se refieren a una columna y/o fila en particular. Se utiliza el signo de dólar '$' para fijar la referencia.

Las referencias relativas se refieren a la celda a la misma distancia de la celda que contiene la fórmula.

Continuamos nuestro ejemplo calculando el cambio de valor de nuestra cartera GOOG en el primer mes. Ese será el precio de las acciones a principios del mes siguiente menos el precio de las acciones a principios de mes, multiplicado por el número de acciones. Para enero de 2009, para GOOG esto se convierte en =( B4-B3) *B$1.

Dado que hemos utilizado correctamente las referencias relativas y absolutas, ahora podemos copiar esta fórmula para completar el gráfico, y Excel modificará la fórmula apropiadamente.

Observamos que las filas y columnas pueden hacerse independientemente absolutas o relativas. Así, si estamos viendo una fórmula en la celda A1, y vemos una referencia a B2 significa la celda uno de abajo y a la derecha de la ubicación de la fórmula. Si vemos $B2 significa la celda en la columna B que está una fila por debajo de la fórmula. Si vemos B$2 significa la celda en la fila dos que es una columna a la derecha de la fórmula.

Cuando volvemos a convertir para ver los valores, vemos que una inversión original de $3,385.30 habría obtenido una ganancia de $3,073.70 en acciones GOOG, $2,128.02 en acciones MSFT y $11,826.60 en acciones de AAPL. Una vez más usamos la función SUM y un rango de celdas para agregar los valores en la columna. También utilizamos los iconos de pantalla dividida en las barras de desplazamiento para poder ver las filas y columnas correctas.

$clipboard_e200c0c8b76d600596a43caa3b386263b.png$

Nota: Excel también puede hacer referencias en varias páginas de un libro de trabajo, pero no necesitaremos esa capacidad para este curso.

Referencias de celdas con nombre.

Una alternativa al uso de referencias absolutas en fórmulas es nombrar las celdas.

Por defecto, Excel nombra cada celda por su fila y columna. Podemos usar la celda de nombre en la esquina superior izquierda de la hoja de Excel para cambiar el nombre del formato de carta/número a un nombre descriptivo.

El nombre más descriptivo puede ser útil a la hora de construir y documentar el proceso que estamos utilizando para nuestros cálculos. Considera el ejemplo anterior con el tío rico. En las celdas B1, C1 y D1, teníamos el número de acciones de Google, Microsoft y Apple que podríamos haber tenido en la cartera. Los mejores nombres para esas celdas serían entonces SharesGoog, SharesMsft y SharesAAPL. Podemos nombrar una celda editando el cuadro de nombre en el lado izquierdo de la barra de fórmulas.

$clipboard_e8f222dc1bfcfd2fb0132ba523b538434.png$

Entonces podemos usar los nombres en fórmulas. En general, las fórmulas con variables bien nombradas son más fáciles de leer.

$clipboard_e66abe744403e0556aeaf16627170723f.png$

Obteniendo ayuda.

Una de las formas en que hacer matemáticas con un programa como Excel difiere de trabajar con una calculadora es que los programas de computadora tienen funciones de ayuda. Vale la pena señalar dos que vienen con Excel. Ilustramos ambos con la función SUM que hemos utilizado varias veces.

Cuando llamamos a Ayuda desde el menú superior, se nos da una ventana emergente para Ayuda de Excel. Tiene una serie de temas enumerados por defecto. También cuenta con una barra para buscar temas.

$clipboard_eb6b489d0984dcdbf437f99c7fd9b6ddb.png$

Escribimos el nombre del comando que estamos buscando y se nos da una página de ayuda para ese comando.

$clipboard_e751b88b4d18954a35baa3df302222994.png$

Un segundo tipo de ayuda es el constructor de fórmulas de la cinta de fórmulas. Da una ayuda más concisa cuando no recuerdas la sintaxis exacta de un comando.

$clipboard_e0d4d2da52c78a4cb74ba5e1df8ef081e.png$

Una tercera fuente de ayuda es simplemente hacer una búsqueda en la web de ayuda de Excel. Para encontrar cómo hacer un cálculo con funciones exponenciales puedes buscar “Fórmulas Excel exponenciales”.

Otros Detalles.

Excel es una herramienta rica y compleja. Estaremos viendo más características a medida que avanzamos por el curso. Hay varios que vale la pena señalar explícitamente en este punto.

Para la aritmética ordinaria, Excel utiliza los símbolos estándar de +, -, *,/y ^ para más, menos, veces, dividido por, y elevando a una potencia.
También podemos usar los comandos SUM, PRODUCT, COCIENT y POWER para la aritmética ordinaria.
El orden de operaciones utilizado por Excel difiere del orden tradicional de operaciones cuando se trata de tomar poderes de números negativos. El problema se ilustra al evaluar$-3^2\text{,}$ cuál tiene un signo negativo y una exponenciación. En todas las clases de matemáticas que hayas tomado esto se interpreta como$-(3^2)$ o$-9\text{,}$ con exponenciación hecha primero. En Excel, esto se interpreta como$(-3)^2$ o 9, con la negación hecha primero. En caso de duda, use paréntesis para hacer explícito el orden de las operaciones.
Excel también tiene las otras funciones matemáticas que has usado antes. Las funciones para raíz cuadrada, base logarítmica 10, base logarítmica e y e a la potencia de, son respectivamente, SQRT, LOG, LN y EXP.
El valor de$e$ está representado por EXP (1).
Excel tiene una serie de operaciones muy útiles en colecciones de números. Comenzamos con los fáciles donde el nombre se explica por sí mismo, como SUMA, PROMEDIO, COUNT, MIN y MAX.

clase=”

Ejercicios Ejercicios Introducción a las hojas de cálculo de Excel

Ejercicio 1:

Producir una hoja de cálculo donde se utilicen las primeras 100 filas. La celda de la fila n y la columna A deben tener valor n. La celda de la fila n y la columna B deben tener el valor 2*n. Debe poder hacerlo escribiendo el valor de 4 celdas y usando relleno rápido.

Contestar

Las fórmulas ingresadas deben verse así:

$clipboard_e06a1693272a238a354c22fe4852d7ca0.png$

Una vez que rellenamos rápidamente y tenemos la vista regular (no mostrando las fórmulas)

$clipboard_e50922d5346e1f2a219b4c3336ed58cd3.png$

La hoja de cálculo real tiene 100 filas.
Tenga en cuenta que hay una pequeña ventana que muestra hasta dónde ha llegado el relleno rápido.
Si quieres consultar las fórmulas utilizadas vas al menú “Fórmula” y eliges “Mostrar fórmulas”. Esta característica es buena para depurar cualquier problema que pueda ocurrir.

Ejercicio 2:

Producir una hoja de cálculo donde se utilicen las primeras 100 filas. La columna A debe contener los primeros 100 números impares. La columna B debe contener múltiplos de 7 empezando por 21.

Ejercicio 3:

Comience con la hoja de trabajo dada. Complete la hoja de trabajo de tal manera que si se cambian los valores de x, y y z, los otros valores se vuelven a calcular automáticamente.

Contestar

No necesitamos usar referencias absolutas en este caso, pero cuando estamos calculando algo que involucra los$z$ valores$x\text{,}$$y$ y debemos referirnos a las celdas B1, B2 y B3 respectivamente.

$clipboard_e4ce4fda3fa0f502bc20da8187c812654.png$

La hoja de cálculo debe tener entradas como se muestra arriba.

Ejercicio 4:

Producir una hoja de cálculo donde se utilicen las primeras 101 filas. La fila 1 debe usarse para etiquetas. La columna A debe contener números enteros de 1 a 100. Las columnas B a F deben contener los cuadrados, cubos, raíces cuadradas, base de troncos 10 y troncos naturales de las entradas en las columnas A.

Ejercicio 5:

Comience con la sección de hoja de cálculo a continuación.

$clipboard_ef06885030551e8abe5100ef20da5b5cd.png$

Si la columna E se copia y pega en la columna G, dé tanto la fórmula como el valor para cada celda no vacía de la columna G.

Contestar

Cualquier entrada que sea absoluta (con un $) será fija. Las referencias relativas (sin el $) cambiarán todas las Bs a Ds porque nos movemos sobre 2 columnas.

Fórmulas

$clipboard_e488ac7cfaa7690a36e2b5248a937f979.png$

Entradas

$clipboard_e7a62f483af5d4cfeee5c1cf0fcb68cc8.png$

Ejercicio 6:

Nos gustaría entender realmente lo que sucede cuando usamos el relleno rápido.

Consideremos las entradas =A1, =$A1, =A$1 y =$A$1 en la fila 2. Haz un relleno rápido a continuación para rellenar 3 filas más y ver qué pasa. Claramente en la primera fila estas celdas apuntan ahora a la celda A1 y el valor devuelto es 1. Después de la primera fila obtenemos una mezcla de valores. ¿Por qué?
$clipboard_effd2622ed844273ebe1abedab903a841.png$