Manual Excel Capítulo 2

Asesoría y Soluciones Informáticas

1 

Asesoría y Soluciones Informáticas

Capítulo 2 ELABORACION DE CUADROS CON FORMULAS SIMPLES

En este capítulo, aprenderemos a elaborar cuadros de diferentes casos, empleando solo el uso de fórmulas simples (sumar celdas, restar, celdas, multiplicar celdas, dividir celdas y su combinación entre ellas; sin emplear ninguna función propia del Microsoft Excel y mucho menos el asistente de funciones y formulas.

Esto lo hacemos con la finalidad, que, en este capítulo aprendas la lógica en el manejo de operaciones con las celdas, para luego combinarlas con funciones matemáticas, financieras, estadísticas, lógicas, de fechas, entre otras.

2 

Asesoría y Soluciones Informáticas

5.1. Elaboración de cuadros y formulas en el Microsoft Excel.

En la mayoría de los cuadros que se elaboran en la hoja electrónica, se emplean las fórmulas para poder hallar los resultados en base a los cálculos, según el tipo y propósito del trabajo.

Para la implementación de fórmulas, se hacen uso de los operadores.

5.1.1. Operadores en Microsoft Excel.

Por lo general, los diferentes cuadros que se puedan diseñar, en la hoja electrónica del Microsoft Excel, se emplean formulas simples para el cálculo de las operaciones. Asimismo se emplean funciones (estadísticas, contables, financieras, de fechas, lógicas, entre otros), pero este tema, lo trataremos en el próximo capítulo. Para la aplicación de fórmulas, se emplean los operadores. Los operadores se agrupan entre sí, según su uso y aplicación, la cual detallo a continuación.

5.1.1.1. Función de los operadores en Microsoft Excel Existen varios tipos de operadores en Excel que te ayudan a llevar a cabo diferentes operaciones en tus hojas de cálculo. Estos operadores los ocupas en cualquier fórmula y función, en cada suma, en cada resta o en cualquier otra operación estás usando los operadores.

Es muy importante saber cuáles son los operadores en Microsoft Excel y qué hace cada uno de ellos, ya que además de realizar las operaciones matemáticas más comunes, te permite realizar comparaciones entre números, también puedes hacer operaciones con palabras, caracteres o con texto en general entre muchos más.

Los operadores en Microsoft Excel, se agrupan en cuatro grandes categorías, estás son: 1 2 3 4 5

Operadores aritméticos Operadores de comparación Operadores de concatenación de texto Operadores de referencia A continuación detallo cada categoría.

5.1.1.2. Operadores aritméticos Las fórmulas aritméticas son las más comunes y combinan números, referencias de celda, funciones y operadores aritméticos para realizar cálculos matemáticos. La siguiente tabla muestra los operadores aritméticos de Excel:

3 

Asesoría y Soluciones Informáticas

Aunque el porcentaje no es un operador aritmético, Excel le da un tratamiento como operador ya que al ingresar un símbolo de porcentaje después de un número provocará que Excel realice una división entre 100 de manera automática.

5.1.1.3. Operadores de comparación Los operadores de comparación nos permiten comparar dos o más números o cadenas de texto. Si el resultado de la comparación es positivo, obtendremos como resultado en valor lógico VERDADERO. De lo contrario obtendremos como resultado el valor FALSO. A continuación tenemos la lista de operadores de comparación:

Los operadores de comparación son muy utilizados con las funciones lógicas de Excel que nos permiten ejecutar una acción al cumplirse la condición establecida.

5.1.1.4. Operadores de texto Las fórmulas de Excel también pueden manipular texto y pueden hacer uso del operador de concatenación para unir el valor de dos cadenas de texto.

5.1.1.5. Operadores de referencia Los operadores de referencia nos ayudan a combinar dos o más referencias de celda para crear una sola referencia.

Recuerda que si tu sistema tiene la configuración regional de España, entonces el operador de unión será el punto y coma (;) y no la coma como aparece en la imagen. 4 

Asesoría y Soluciones Informáticas

5.1.1.6. Precedencia de operadores en Excel Cuando creamos fórmulas que contienen más de un operador, será necesario conocer el orden en que dichas operaciones serán calculadas por Excel. Por esta razón existe un orden de precedencia que determina la manera en que Excel hace los cálculos:

Si tenemos una fórmula con varios operadores con la misma precedencia, entonces Excel realizará los cálculos de izquierda a derecha.

5.1.1.7. Cómo controlar el orden de precedencia La única manera en que podemos influir en el orden de precedencia de operadores en Excel es utilizando paréntesis. Al colocar paréntesis alrededor de un cálculo estaremos diciendo a Excel que deseamos otorgar una alta prioridad a dicho cálculo y por lo tanto se realizará primero. En la siguiente imagen puedes observar cómo se modifica el resultado de una fórmula al incluir paréntesis.

Cuando existen paréntesis dentro de una fórmula, Excel comenzará los cálculos con el paréntesis que tenga el mayor nivel de anidación. Y si dentro de un mismo paréntesis existen varios operadores, entonces se aplicarán las reglas de precedencia antes vistas. De esta manera los paréntesis nos permiten controlar el orden de precedencia de los cálculos es una fórmula de Excel.

5 

Asesoría y Soluciones Informáticas

Caso Práctico Nº1.

El Profesor de la asignatura “La persona y su acción”, nos ha solicitado que procesar el control de notas de sus alumnos, que, actualmente lo lleva manualmente. Las consideraciones para la implementación de la planilla de notas, son las siguientes:

Ingreso de datos:  Código del alumno  Apellidos y nombres  Examen Parcial  Foro  Tav (Tutoría Asesoría Virtual)  Trabajo individual  Examen final  Promedio final.

Cálculos: Promedio final, se obtiene sumando todas las evaluaciones y dividirlo entre el número de notas (05).

El formato a emplear será la siguiente:

Creando nuestro cuadro: 1.

Empleando la forma conocida, estudiada anteriormente, proceda a ingresar los campos (títulos) y datos, para que nuestro cuadro, se muestre como en la siguiente figura:

Recuerda que aparentemente los títulos, se están cortando entre sí, pero no es así, las celdas almacenan 255 caracteres y que ampliando el ancho de las columnas se podrá solucionar, por ahora que quede así, para continuar con el orden de nuestra práctica. 6 

Asesoría y Soluciones Informáticas

2. Seguidamente procede a ingresar los códigos, apellidos y nombres de los alumnos, tal como se muestra en la siguiente ventana:

3. Ahora procede ampliar el ancho de las columnas Código, Apellidos y Nombres, Foro e Tav (de la forma ya aprendida), para que el cuadro quede de la siguiente manera:

4. Seguidamente procedemos a digitar los encabezados “DATOS DEL ALUMNO” y “EVALUACIONES”, para ello combinemos las celdas tal como te muestro a continuación:  Presione el click en la celda “B4” y mueve el puntero hasta la celda “C4” y deja de presionar (se habrán sombreado solo las celdas B4 y C4), quedando así:.

Inmediatamente hacemos click en el botón “Combinar y centrar”.

a

En este momento ambas celdas se combinaron, formando una sola celda.

7 

Asesoría y Soluciones Informáticas

b

c

En este momento procedemos a digitar el primer encabezado “DATOS DEL ALUMNO”, quedando de la siguiente manera:

En Por último haga click en el botón “centrar” para que el título se centre en la celda actual y se mostrara como en la siguiente ventana:

5. A manera de práctica y empleando lo aprendido, procede a sombrear las celdas, combinarlas, digitar el título “EVALUACIONES”, y por ultimo a centrarlo. Si todo lo realizaste bien, nuestro cuando estará quedando de la siguiente manera:

6. Continuemos con la práctica. Si observas los títulos Examen Parcial (celda D5), Trabajo individual (celda G5), Examen final (celda K5) y Promedio final (celda I5) no se muestran como en el cuadro ejemplo que nos solicitaron, a manera de recuerdo colocare aquí los encabezados y títulos:

Aquí apreciamos que dichos títulos siguen ocupando una sola celda respectivamente, pero ocupan dos líneas de texto dentro de ellas, a esto se le llama “ajustar texto en las celdas”

8 

Asesoría y Soluciones Informáticas

Haga click derecho en la celda “D5” Examen parcial, y se mostrara un menú de opciones y selecciona la opción “Formato de celdas”.

d En Hacemos click en la opción “Formato de celas” y se estará mostrando la venta de “Formato de celdas”.

e Hacemos click en la pestaña “Alineación”.

f Esta opción es la que permite que el texto se muestre en dos líneas

g Hacemos click en “Aceptar” para que los cambios se fijen.

Ahora podrás observar, que el texto de la celda “D5”, se está mostrando en dos líneas.

9 

Asesoría y Soluciones Informáticas

Copiando el formato de dos líneas a las celdas “G5”, “H5”, “H5” y “I5”.

En este momento el curso está en la celda “D5”. Inmediatamente, haga click en el botón , y haga click en la celda “G5” del título “Trabajo Individual”, y observaras que el texto de dicha celda se mostrara en dos líneas. Repite este procedimiento para las celdas “H5” y “I5”, si lo haces correctamente, nuestro cuadro se estará mostrando de la siguiente manera:

Ahora para que los títulos se observen centrados horizontalmente y verticalmente, presione el click en la celda “B5” y mueve el puntero hasta la celda “I5” y deje de presionar (se habrá sombreado las celdas)

Seguidamente procedamos a centra los títulos de forma horizontal y vertical mediante los botones que te indico a continuación: h

i Este botón permitirá centrar los títulos verticalmente.

Este botón permitirá centrar los títulos horizontalmente.

Ahora nuestro cuadro se estará mostrando de la siguiente manera:

 10 

Asesoría y Soluciones Informáticas

Fijando los bordes a nuestro cuadro.

Presiona el click en la celda “B4” y mueve el puntero hasta la celda “I10”, todas las celdas de ese rango se habrá sombreado, tal como se muestra en la siguiente figura:

Ahora en cualquier lugar dentro de las celdas sombreadas, haga click derecho del mouse y se mostrara un menú de opciones y haga click en la opción “Formato de celdas” y se mostrar la siguiente ventana: j

k Haga click en la pestaña de bordes..

Este botón, asignara un borde al contorno de lo sombreado.

l Este botón, asignara líneas horizontales y verticales, al interior de los sombreado de lo sombreado.

Click en “Aceptar” para fijar las líneas y bordes..

 11 

ll

Asesoría y Soluciones Informáticas

Si el procedimiento se hizo correctamente, ahora nuestro cuadro se mostrara como la siguiente presentación:

Aplicando fórmulas a nuestro cuadro.

Para hallar el promedio final, por ahora el criterio a utilizar será sumar todas las notas y dividirlas entre el número de notas, es decir entre 05. Para esto el Microsoft Excel, nos obliga a trabajar con la direcciones de celdas, es decir ¿en que celdas están las notas a suma? Y la respuesta a esta pregunta es: D6, E6, F6, G6, H6 y estas celdas se deben sumar y a la vez dividirla entre 05, de la siguiente manera:

m Haga un click izquierdo en esta celda “I6” y digita: =(D6+E6+F6+G6+H6)/5 y pulsas enter para que se ingrese la formula. Observa que se muestra el valor 0.

Copiando la fórmula ingresada.

Ahora pulsa una sola vez flecha hacia arriba o haga un click en la celda “I6” para que el cursor este en la formula y procede a ubicar el puntero del mouse justo en el punto ubicado en la parte inferior derecha de dicha celda, tal como se muestra en la siguiente figura: Presiona el click del mouse justo en este punto y mueve el puntero hacia abajo hasta la celda “I10” y deja de presiona el click.

n

 12 

Asesoría y Soluciones Informáticas

Aparentemente se ha copiado el valor 0 a las demás celdas, pero o es asi, sube con las flechas y observa en cada celda esta la formula, mas no el valor 0. El valor 0 solo es para la presentación, el Excel reconoce la formula.

Viene a ser, el indicado de celda, es decir, aquí se muestra la ubicación actual del cursor dentro de la celda.

Viene a ser, el indicado de contenido de la celda actual. En este caso el cursor esta en la celda I6 y su contenido es esta fórmula.

Por último, procede a ingresar las notas, según tu criterio personal y de forma automática, las celdas en donde se ingresó y copio la formula se mostraran los promedios correspondientes. Puedes tomar como ejemplo las notas del siguiente ejemplo:

Felicitaciones!!!l terminaste con éxito nuestro cuadro ejemplo. Si observas con detenimiento, los promedios se han obtenido gracias a la formula ingresada y copiada hacia las demás celdas, pero se muestra con numero de decimales. Debemos de redondear el promedio, reduciendo el número de decimales a 0 decimales.

 13 

Asesoría y Soluciones Informáticas

Reduciendo el número de decimales (redondeando el promedio).  Sombrea las celdas de los promedio.  Ahora puedes aumentar o reducir el número de decimales para los promedios, para ello haga click en los botones de aumentar /disminuir decimales, tal como se muestra en la siguiente figura: O

Haga click aquí, para ir disminuyendo el número de decimales. Al llegar a 0 decimales, observa que de forma automática los promedios se han redondeado.

Caso Práctico Nº2.

La empresa “PanaAutos SAC.”, vende exclusivamente autos Toyota. Nos pide que elaboremos un formato que permita registra las ventas de la semana y asimismo la planilla de los vendedores.

Para ello, después dele estudio correspondiente, se tendrá en cuenta las siguientes consideraciones:

   

La empresa cuenta con 03 vendedores.

La ventas se realizan de lunes a sábado Se ingresaran: el modelo del auto a vender, la cantidad de autos vendidos, el precio de cada auto, el sueldo del vendedor.

Se calculara el importe de la venta. De la siguiente manera: =CeldaCantidad*CeldaPrecio



Se calculara el total de ventas de cada vendedor a la semana, de la siguiente manera: =sumarCeldasdeImportes



Se calculara las comisiones de venta x vendedor, de la siguiente manera: Todos cuentan con una comisión del 5% del total de sus ventas



Se calculara el total a recibir de cada trabajador. =Sueldo+Comision

 14 

Asesoría y Soluciones Informáticas



Por último el total de la planilla a la semana. =seSumaCeldasTotalPagar.

El formato para la realización del trabajo será el siguiente:

Nota: No olvides de asignar el formato de moneda a las celdas de precio, importe, total ventas, comisiones, sueldo, total a pagar y total planilla.

Calculando el importe para el vendedor: Carlos Valenzuela. a Ubica el cursor en esta celda y procede a digitar la formula para calcular el importe de la venta del día lunes de la siguiente manera: =D13*E13 y pulsamos enter.

b Al ingresar la fórmula del importe el cursor bajara una celda hacia abajo, en este momento vuelve a ubicar el cursor en la celda del primer importe, es decir en la celda “F13”.

 15 

Asesoría y Soluciones Informáticas

Copiando la fórmula del importe. c Ubica el puntero del mouse, justo en este punto y presiona el click y mueve el puntero hacia abajo, hasta la celda “F18” y deja de presionar el click y la formula se copiara.

Ahora si realizaste bien el procedimiento, el cuadro del primer vendedor, se estará mostrando de la siguiente manera:

Empleando el mismo criterio del procedimiento realizado para el primer vendedor, continúa para ingresar la fórmula para el segundo y tercer vendedor respectivamente. Si realizaste correctamente el procedimiento, nuestro cuadro se mostrara de la siguiente manera:

 16 

Asesoría y Soluciones Informáticas

Calculando el total de ventas, comisiones y total a pagar de los vendedores.

Total de Ventas para el vendedor: Carlos Valenzuela.

d Ubica el puntero en esta celda y procede a digitar la formula que suma todas las celdas de los importes de ventas de la emana para el vendedor Carlos Valenzuela, de la siguiente manea: =F13+F14+F15+F16+F17+F18 y pulsa enter.

Comisiones para el vendedor: Carlos Valenzuela.

g Ahora digitamos la fórmula para hallar la comisión, que es el que es el 5% de su total de ventas, de la siguiente manera: =D21*0.05 y pulsamos enter.

f Ubica el puntero del mouse en esta celda.

 17 

Asesoría y Soluciones Informáticas

Sueldo del vendedor.

Solo será necesario ingresar el sueldo al mes del trabajador.

Total a pagar.

Se obtiene, sumando el sueldo, con el monto de la comisión, tal como se muestra en la siguiente imagen:

h Ubica el cursor en esta celda y procede a digitar la fórmula para hallar el total a pagar, de la siguiente manera: =D21*0.05 y pulsamos enter.

Empleando el mismo criterio, puedes repetir el procedimiento para los dos vendedores respectivamente o sino copiar cada formula obtenida de la forma aprendida y listo.

Total a Planilla.

Representa el total que la empresa pagara en la planilla semanal de los vendedores de autos.

Se obtiene sumando las celdas de comisión+sueldo, tal como se muestra a continuación.

i Ubica el cursor en esta celda y procede a digitar la fórmula para hallar el total de la planilla, sumando todas las celdas de Total a pagar, de la siguiente manera: =G21+G22+G23 y pulsamos enter.

 18 

Asesoría y Soluciones Informáticas

Si realizaste correctamente el ingreso de las formulas en las celdas respectivas, en este momento nuestro cuando se estaría mostrando de la siguiente manera:

Por último, ingresa los datos del modelo del auto, cantidad de venta, el precio de cada auto, el sueldo del vendedor y automáticamente observaras, como el Microsoft Excel, calculara y mostrara los totales, según las formulas ingresadas (Recuerda que s muy probable que algunas celdas se muestren con el signo del “#####”, esto debe a que el ancho de la columna es muy pequeña y lo que debemos de hacer es ampliar el ancho de dicha columna y listo). A manera de ejemplo, aquí hemos ingresado los datos y por consecuencia se muestran los resultados, como en la siguiente figura:

 19 

Asesoría y Soluciones Informáticas

Caso Práctico Nº3.

La tienda de venta de artículos de vestir deportivos “Arakawa Sport”, nos pide que elaboremos un formato que permita emitir las facturas de venta. Aplicando lo aprendido en el caso Nº1, proceda a elaborar el modelo de la factura, de tal manera que quede como el siguiente modelo:

El formato contemplara: bordes de celdas, fuente (tipo de letras), tamaño de letras, combinación de celdas.

El ingreso de datos será: para el Nº de factura, nombre del cliente, dirección del cliente, cantidad de venta, nombre del artículo, precio x unidad y el importe de la venta.

El Sub total, Igv y total general se calculan con fórmulas, las cuales te detallo a continuación: Una vez elaborado el cuadro con la presentación y consideraciones indicadas, procedemos a realizar los cálculos empleando las formulas.

Importe, para calcular el importe, se obtiene multiplicando la dirección de celda de la cantidad (B16) por la dirección de celda del precio x unidad (E16), de la siguiente manera:

A l En esta celda “F16”, haga un click y digita la fórmula que calculara el importe con la siguiente =b16*e16 y pulsas enter.

B l Ahora hay que copiar la formula, ubica el puntero en este punto y presiona el click y mueve el mouse hacia abajo hasta la celda “F21” y de ja de presionar. Observa que las celdas “F16” hasta la celda “F21” se ha sombreado.

 20 

Asesoría y Soluciones Informáticas

Puedes mover el cursos en cada celda de los importes copiados y veras que la columna se mantiene, pero las filas se incrementaron. Esto se debe porque hemos copiado hacia abajo. Los valores de 0 salen así, debido a que, en cantidad y precio aún no hemos ingresado datos. En este momento procede a ingresar los datos y veras que los importes se calculan automáticamente.

S.Total, Se obtiene, sumando las celdas de todos los importes, si bien es cierto existen funciones que calculan la sumatoria de rangos de celdas, pero en esta oportunidad a manera de practica digitaremos la fórmula de suma, empleando el operador aritmético “mas +”, tal como se muestra a continuación:

C l Ahora mueve el cursor en esta celda, y digitamos la fórmula que suma todos los importes, de la siguiente manera: =f16+f17+f18+f19+f20+f21 y pulsa enter..

 21 

Asesoría y Soluciones Informáticas

IGV. 19% y T.GRAL., el igv se obtiene multiplicando el S.total x 0.19, asimismo el Tgral, se obtiene sumando las celdas de S.total con el Igv. De la siguiente manera:

F l D l

Ubica el cursor en esta celda y digitamos la fórmula para sumar todos los importes, de la siguiente manera: =Suma(F16:F21) y pulsa enter..

G l

Ubica el curso en esta celda y digitamos la fórmula que multiplica al S.total con 19% de la siguiente manera: =F22*19% y pulsa enter.

El total general es la suma del S.Total con el igv: =F22+F23 y pulsa enter..

La Función =Suma() sumara los valores existente a partir de la celda “F16” hasta la celda “F21” hallando el total.

 22 

Asesoría y Soluciones Informáticas

Si ingresas los datos, tal como se muestra en la siguiente figura, nuestro cuadro se mostrara con los importes calculados automáticamente, de la siguiente manera:

Recuerda.

Que, el Microsoft Excel, cuenta con funciones estadísticas, financieras, de fecha, lógicas, etc. Estas funciones lo aprenderemos en el próximo capitulo. Por ahora continuaremos aplicando formulas simples para nuestros cuadros propuestos.

Hasta el próximo capítulo 3 “Funciones matemáticas, estadísticas, de fechas, lógicas, de búsqueda, entre otras”. ¿Si deseas el documento completo en formato Word (.doc) y en .PDF de este manual? ¿Si deseas los ejemplos originales de los ejercicios en Microsoft Excel en formato .XLS y otros más?

Deposita la cantidad de S/.50.00 a la cuenta en Soles del Banco Continental Nº 0011-0269-0200159598-88 Víctor Garay Yauri.

Confirma el depósito, cualquier consulta o sugerencia hazla a mi correo electrónico, que es el mismo para mi Skype profesional: garaysoft@hotmail.com

 23 