Ejercicio de la clase
En esta oportunidad, el resultado que teníamos que alcanzar era el siguiente.
Para llegar a estos resultados, tuvimos que llenar una serie de datos y aplicar unas formulas en unas determinadas columnas, con el fin de obtener los datos que necesitábamos.
En primer lugar, diligenciamos la siguiente columna.
Según las indicaciones, sí la columna AF tiene información, en la columna de CUMPLIMIENTO debe indicarse que CUMPLIDO, de lo contrario NO CUMPLIDO. Por esa razón usamos la función SI ya que es una condición lo que impone la instrucción.
En prueba lógica escribimos que si la primer casilla de referencia -AF4- es <> de "" -eso es para indicar si es diferente de nada-, el valor verdadero se "CUMPLIDO" y si no se cumple con la condición en valor falso "NO CUMPLIDO".
En la siguiente columna teníamos que realizar la siguiente operación.
La imagen de la derecha, nos muestra la siguiente columna a operar, mientras que la imagen de la izquierda nos presenta una hoja en la cual había información necesaria para completar con la instrucción para este paso.
En primer lugar, abrimos una función SI, esta función nos da la posibilidad de abrir otra serie de funciones para cumplir con la instrucción.
Primero empezamos en prueba lógica, allí tendremos que indicar que en si los valores de la columna AS -empezando desde la columna AS, fila 4- son iguales a TRANSFERENCIA, en valor verdadero indicaremos "COBRADO". Para lograr eso, tendremos que agregar en prueba lógica una función conocida como EXTRAE, e indicar que seleccione -empezando desde la casilla AS4- la posición inicial 1, y el número de caracteres 1. Después nos devolvemos a la función SI -dando Clic en la barra de funciones en la parte que dice SI- y en prueba lógica indicar que la función de EXTRAE sea igual a "T", esto porque necesitamos buscar los valores que digan TRANSFERENCIA. Quedando Así.
Sí la casilla indicada cumple con la función se indicará COBRADO como ya se indicó anteriormente. Ahora en valor falso de la función si tendremos que agregar una función ya conocida, la famosa BUSCARV, y nuestro valor buscado, serán los valores de la columna AS4 en adelante, nuestra matriz buscar en será la tabla de cheques -imagen de la izquierda- y nuestro indicador de columnas será 2, porque queremos traer el estado, es decir ENTREGADO Y COBRADO, al finalizar nuestra formula BUSCARV, podemos dar ACEPTAR.
Pero no todo está finalizado, como hay cheques que no están ENTREGADOS Y COBRADOS O COBRADOS, la habrá casillas que nos mostraran un error, así que tenemos que recortar la formula hasta el igual y abrir otra formula llamada SI.ERROR, y en valor pegaremos la formula que hemos recortados, y en valor si.error escribiremos que indique "ENTREGADO Y SIN COBRAR".
Y así daríamos por finalizada la columna.
Para la siguiente columna, recordaremos varias de las funciones ya vistas, entre ellas la FORMATO CONDICIONAL.
Después de eso, seleccionamos texto que contiene, e indicamos que cuando la casilla diga "FACTURADO" en con escogemos formato personalizado y ponemos que el relleno sea verde y el color del texto blanco. Repetimos el mismo procedimiento, pero en esta ocasión pondremos que si la casilla dice "SIN FACTURAR", el relleno sea rojo y el color del texto blanco, quedando así.
Y así solo nos faltaría una columna para culminar.
Para la columna AW, es necesario cumplir con ciertos requisitos.
Por ejemplo, si en la columna AV hay celdas que dicen SIN FACTURAR, tendremos que aplicar intereses según la siguiente tabla.
Para desarrollar esta condición, primero, abrimos la Función SI, con la que daremos forma a la condición, Entonces en prueba lógica pondremos que sí las celdas de la columna AV dicen SIN FACTURAR, en valor si verdadero tenemos que agregar otro SI ya que también hay que multiplicar el peso de la columna O por una cantidad de pesos, para eso en la prueba lógica del SI que abrimos, ponemos que si la columna O4 -en adelante- es menor "<" a 9000 en valor si verdadero se multiplicara O4 Por 5, y en valor si falso del SI que acabamos de abrir, agregamos otro SI, en este pondremos que si O4 es menor "<" a 10000, en valor si verdadero multipliquemos O4 por 4 y en valor si falso ponemos que O4 por 3.
Lo anterior es para cumplir con el requisito de:
Si O4 es <9000 multiplicar por 5
Si O4 está entre 9000 y 10000, multiplicar por 4. Para que se cumpliera esta condición pusimos primero < a 9000 y después <10000, para que los números que queden entre ese rango sea los que se multipliquen por 4.
y por ultimo, si O4 es >10000 se tendrá que multiplicar por 3.
Quedando la formula de la siguiente manera.
Para terminar, tenemos que poner en el valor si falso del primer SI, agregaremos la función BUSCARV, y buscaremos los porcentajes de la tabla, después nos devolvemos al SI, y ponemos que el resultado del BUSCARV, se multiplique por K4 -o total flete o remesa- y por ultimo aceptar.
Para llevar a cabo el BUSCARV, primero lo agregamos en la celda de valor si falso, después, en valor a buscar ponemos la celda AG4 ya que son los nombres de los remitentes, después en matriz buscar en seleccionamos la tabla de tasa de intereses, en indicador de columnas pondremos 2, ya que es el porcentaje el valor que deseamos, y en ordenado 0 porque queremos el valor exacto. Después nos devolvemos al primer SI valor si falso y multiplicamos esta formula por la correspondiente Columna.
Por ultimo, en resumen ejecutivo, tendremos que mostrar los totales según lo pida el informe.
Primero, número de viajes por remitente. Para saber el total de viajes por remitente, tenemos que usar una formula llamada CONTAR.SI.
Estas imágenes son respaldo del trabajo, y sirven como guía para entender el proceso.
Para llegar a estos resultados, tuvimos que llenar una serie de datos y aplicar unas formulas en unas determinadas columnas, con el fin de obtener los datos que necesitábamos.
Según las indicaciones, sí la columna AF tiene información, en la columna de CUMPLIMIENTO debe indicarse que CUMPLIDO, de lo contrario NO CUMPLIDO. Por esa razón usamos la función SI ya que es una condición lo que impone la instrucción.
En prueba lógica escribimos que si la primer casilla de referencia -AF4- es <> de "" -eso es para indicar si es diferente de nada-, el valor verdadero se "CUMPLIDO" y si no se cumple con la condición en valor falso "NO CUMPLIDO".
En la siguiente columna teníamos que realizar la siguiente operación.
La imagen de la derecha, nos muestra la siguiente columna a operar, mientras que la imagen de la izquierda nos presenta una hoja en la cual había información necesaria para completar con la instrucción para este paso.
En primer lugar, abrimos una función SI, esta función nos da la posibilidad de abrir otra serie de funciones para cumplir con la instrucción.
Primero empezamos en prueba lógica, allí tendremos que indicar que en si los valores de la columna AS -empezando desde la columna AS, fila 4- son iguales a TRANSFERENCIA, en valor verdadero indicaremos "COBRADO". Para lograr eso, tendremos que agregar en prueba lógica una función conocida como EXTRAE, e indicar que seleccione -empezando desde la casilla AS4- la posición inicial 1, y el número de caracteres 1. Después nos devolvemos a la función SI -dando Clic en la barra de funciones en la parte que dice SI- y en prueba lógica indicar que la función de EXTRAE sea igual a "T", esto porque necesitamos buscar los valores que digan TRANSFERENCIA. Quedando Así.
Sí la casilla indicada cumple con la función se indicará COBRADO como ya se indicó anteriormente. Ahora en valor falso de la función si tendremos que agregar una función ya conocida, la famosa BUSCARV, y nuestro valor buscado, serán los valores de la columna AS4 en adelante, nuestra matriz buscar en será la tabla de cheques -imagen de la izquierda- y nuestro indicador de columnas será 2, porque queremos traer el estado, es decir ENTREGADO Y COBRADO, al finalizar nuestra formula BUSCARV, podemos dar ACEPTAR.
Pero no todo está finalizado, como hay cheques que no están ENTREGADOS Y COBRADOS O COBRADOS, la habrá casillas que nos mostraran un error, así que tenemos que recortar la formula hasta el igual y abrir otra formula llamada SI.ERROR, y en valor pegaremos la formula que hemos recortados, y en valor si.error escribiremos que indique "ENTREGADO Y SIN COBRAR".
Y así daríamos por finalizada la columna.
Lo primero que hay que hacer, es incluir la Función SI, en prueba lógica, indicaremos que si W4 tiene algún valor correspondiente a una factura.
Indicaremos en valor verdadero de la función si "FACTURADO", y si la columna de factura no cumple con esa condición, entonces en valor si falso se indicará "SIN FACTURAR".
Después de haber cumplido con esa condición, señalamos toda la columna AV y nos desplazamos a la barra de funciones y escogemos FORMATO CONDICIONAL.
Y así solo nos faltaría una columna para culminar.
Para la columna AW, es necesario cumplir con ciertos requisitos.
Por ejemplo, si en la columna AV hay celdas que dicen SIN FACTURAR, tendremos que aplicar intereses según la siguiente tabla.
 |
| "tabla en la página tasa de intereses" |
Lo anterior es para cumplir con el requisito de:
Si O4 es <9000 multiplicar por 5
Si O4 está entre 9000 y 10000, multiplicar por 4. Para que se cumpliera esta condición pusimos primero < a 9000 y después <10000, para que los números que queden entre ese rango sea los que se multipliquen por 4.
y por ultimo, si O4 es >10000 se tendrá que multiplicar por 3.
Quedando la formula de la siguiente manera.
Para terminar, tenemos que poner en el valor si falso del primer SI, agregaremos la función BUSCARV, y buscaremos los porcentajes de la tabla, después nos devolvemos al SI, y ponemos que el resultado del BUSCARV, se multiplique por K4 -o total flete o remesa- y por ultimo aceptar.
Para llevar a cabo el BUSCARV, primero lo agregamos en la celda de valor si falso, después, en valor a buscar ponemos la celda AG4 ya que son los nombres de los remitentes, después en matriz buscar en seleccionamos la tabla de tasa de intereses, en indicador de columnas pondremos 2, ya que es el porcentaje el valor que deseamos, y en ordenado 0 porque queremos el valor exacto. Después nos devolvemos al primer SI valor si falso y multiplicamos esta formula por la correspondiente Columna.
Por ultimo, en resumen ejecutivo, tendremos que mostrar los totales según lo pida el informe.
Primero, número de viajes por remitente. Para saber el total de viajes por remitente, tenemos que usar una formula llamada CONTAR.SI.
En la primer casilla rango escogemos la columna AG, ya que ahí se encuentran los nombres de todos los remitentes, y en criterio, escogeremos la celda C4, donde están los nombres de los remitentes que tenemos en la tabla estado de viajes por remitente de la hoja RESUMEN FINANCIERO. Después damos aceptar, arrastramos para copiar la formula en las celdas correspondientes de cada remitente.
Para estado de pago de viajes, del mismo resumen, usaremos misma formula CONTAR.SI pero esta vez nuestro rango será la columna AU, ya que ahí tenemos el estado de nuestros cheques. En criterio igual que en el informe anterior, seleccionaremos la casilla C15, ya que ahí es donde encontramos la condición que queremos contar.
En estado de cumplimiento, usaremos igualmente la formula CONTAR.SI, ya que necesitamos saber cuantos viajes están cumplidos o sin cumplir, así que en rango seleccionaremos la columna AT, ya que está nos indica cuales han sido cumplidos y cuáles no, y en criterio seleccionaremos la celda C21 de nuestra tabla de estado de cumplimiento de viajes -hoja resumen financiero- y damos aceptar, igual que en las tablas anteriores, arrastramos hacia abajo, para copiar la formula en las celdas siguientes y completar el informe.
Ahora, debemos saber cuanto son los intereses por remitentes. Para eso usaremos la función SUMAR.SI.CONJUNTO Ya que nos permite sumar valores según una serie de criterios.
Primero, en rango suma escogemos la columna AW, ya que ahí se encuentran el valor de los intereses, después en rango criterio1 escogemos la columna AG puesto que ahí encontramos los nombres de los remitentes, y en criterio1 escogemos la celda F4 ya que es el criterio de nuestra tabla, y es la condición que deseamos sumar. Después de dar aceptar, desplazamos hacia abajo para copiar la formula en los demás remitentes.
Por ultimo, encontramos la tabla de ESTADO DE FACTURACIÓN DE VIAJES y al igual que en las primeras tablas de este informe, usaremos la función CONTAR.SI, cuyo rango será la columna AV pues ahí encontraremos el estado en el que se encuentran las Facturas, y el criterio será F15, pues es la celda del informe que contiene la condición que queremos contar. Quedado así.
Con este aplicativo, se dan los primeros pasos para la presentación de informes versión Excel, así como el uso adecuado de las formulas vistas en el transcurso del semestre.
No hay comentarios:
Publicar un comentario