ADMINISTRACION PYMES CLASE 7 – REFEFERENCIA RELATIVA, FORMATO CONDICIONAL Y BUSCARV En esta clase vamos a aprender varias cosas pero para hacerlo vamos a armar la tabla de un almacén, ya sea kiosco, un súper, un mini mercado, etc.
Para ello: En A1: Escribir CODIGO En B1: Escribir PRODUCTO En C1: Tipo
En D1: Proveedor En E1: Costo En F1: Ganancia
Y en G1: TOTAL
Y tenemos que escribir este cuadrito en las celdas correspondientes:
Lo siguiente que vamos a hacer será RELLENAR: CODIGO: Con un código único para cada producto (15 productos). PRODUCTO: Ingresar algún nombre de lo que venda ese local. TIPO: Ingresar valor por VALIDACION DE DATOS: LISTA de I12 a I14 PROVEEDOR: Esto se resuelve según la tabla… SI FRUTA SI VERDURA CARNE
Entonces Entonces
Bonita Sino Don Ramón Sino Tia Julia
=SI(TIPO=“FRUTA”; “Bonita”; SI(TIPO= “VERDURA”; “Don Ramón” ; “Tia Julia”)) Si luego reemplazamos los valores por las celdas quedaría como: =SI(C2=I12;J12; SI(C2=I13;J13;J14))
REFEFERENCIAS Ahora vamos a hablar de este tema tan importante en Excel y que para algunas personas es muy difícil y para otras muy fácil… básicamente se trata de cómo interactúan las celdas con funciones cuando se AUTORRELLENA en alguna dirección… “Una referencia en Excel identifica a una celda (o rango de celdas) dentro de una hoja de trabajo y le hace saber a Excel 1 en dónde buscar los valores que se quieren utilizar en una fórmula.”
HAY TRES TIPOS DIFERENTES DE REFERENCIAS: REFERENCIARELATIVA: es una referencia libre, la REF (A1) se mueve en la misma dirección en la que se rellena. Es decir, si relleno hacia abajo (como en el ejemplo), A1 a medida que se va cambiando va bajando su número de fila, es decir A2, A3, A4 y A5.
Un ejemplo con un rango en una función lo vemos aquí, al autorellenar hacia abajo, automáticamente el rango baja, es decir, si el resultado baja, los datos que le pusimos a la función también bajan y lo mismo pasaría de costado)
de esta forma nosotros ahorramos tener que escribir la
función, lo hacemos una sola vez y listo. REFERENCIAABSOLUTA: es una referencia FIJA, donde se ingresa un símbolo “$” que sirve de ancla (porque le pone un “peso” para que no se mueva) que obliga a que no varía la referencia, la REF (A1) no se mueve si se rellena. Es decir, si relleno hacia abajo (como en el ejemplo), A1 a medida que se va cambiando va manteniéndose, es decir A1, A1, A1 y A1.
Un ejemplo con un rango en una función lo vemos aquí, al autorellenar hacia abajo, automáticamente el rango queda IGUAL. Y si de un rango, solo “trabamos” una de las dos celdas, al arrastrar la que quedo con ANCLAS queda en el lugar y la otra cambia.
Para poder poner un ancla hay que poner el CURSOR numero y hay que apretar la tecla F4
1
“|” entre la letra de la columna y el
Texto obtenido de: http://exceltotal.com/referencias-absolutas-y-relativas/ de Moisés Ortíz
REFERENCIAMIXTA: es una referencia donde solo esta FIJA la columna o la fila, se ingresa un símbolo “$” de ancla solo para aquel valor que queremos que no varíe: • B$1 -- Para que no varíe la fila • $A2 – Para que no varíe la columna Si queremos que la REF (B1) no varíe su fila si se rellena(como en el ejemplo), debemos dejar así B$1 y a medida que se va cambiando la fila va manteniéndose la fila, es decir B$1, B$1, B$1 y B$1 pero si se lo varia de costado la columna cambiará: C$1, D$1, E$1, F$1…
Notar como en $A2 no varía la columna cuando se arrastra de costado… Notar como en B$1 no varía la fila cuando se arrastra hacia abajo…
¿Y PARA QUE OCUPAREMOS LAS REFERENCIAS?Se acuerdan que la última función que nosotros hicimos fue: =SI(C2=I12;J12;SI(C2=I13;J13;J14)), si colocamos esa función en la celdaD2 nos dará el proveedor que necesitamos pero si AUTORELLENAMOS la función hacia abajo, puede que con suerte, nos funciones en la celda D3 y D4 pero el resto va a quedar mal (tal como se ve en la imagen de la izquierda).
Para que quede bien tenemos que usar, en este caso, referencia ABSOLUTA de la forma: =SI(C2=$I$12;$J$12;SI(C2=$I$13;$J$13;$J$14)) (¿Por qué no ancle C2? Porque C2 Si debe ir “bajando” ya que hace referencia al producto, y cuando autorrelleno la función hacia abajo, se debe tomar el producto de abajo… …pero como la tablita se queda quieta, cuando hacemos referencia a los valores de la tablita, tenemos que hacer que se queden “quietos” o anclados).
(SI NO HICISTE PROVEEDOR, HAZLO AHORA) COSTO: lo resolveremos simplemente escribiéndoles el costo según lo que te parece. GANANCIA: similar a proveedor… =SI(C2=I12;K12; SI(C2=I13;K13;K14)) Y después arrastramos con referencia ABSOLUTA. =SI(C2=$I$12;$K$12; SI(C2=$I$13;$K$13;$K$14)) TOTAL: para resolverlo simplemente debemos usar un poco de lógica simple… El total es básicamente la GANANCIA REAL + el COSTO… … la ganancia NO ES ese porcentaje que pusimos, sino el % de ganancia * el costo Entonces tenemos por ejemplo: COSTO: $49 y GANANCIA: 20% … entonces: La “Ganancia real” = $49*20% Luego la GANANCIA REAL ( $49*20%)+ COSTO ($49)= TOTAL TOTAL= $49*20%+ $49
FORMATO CONDICIONAL ES UNA HERRAMIENTA QUE PERMITE CAMBIAR EL FORMATO (APARIENCIA) DE CELDAS QUE CUMPLEN CON UNA “REGLA” Esto se usa mucho para resaltar los valores que se usan más, lo que son de un tipo determinado, tenemos la opción de poner una barrita que represente el valor de la celda, un icono, incluso se puede resaltar el más grande o/y el más chico. NOSOTROS USAREMOS ESTO PARA: • Poner un color para cada tipo de producto • Resaltar los productos caros de los baratos Ahora vamos a SELECCIONAR de C2 a C16 Allí buscamos en la barra FORMATO CONDICIONAL --> ADMINISTRAR REGLAS
Barras de Datos Escala de color Conjunto de iconos Quedan para que ustedes practiquen…
Luego de apretar en ADMINISTRAR REGLAS aparecerá la siguiente ventana: La primera impresión es que asusta, pero en realidad esta opción es bastante simple. Apretamos en NUEVA REGLA…
Vamos a ver que tenemos TIPOS de reglas en la parte superior … y debajo diferentes opciones según la REGLA elegida …pero todas tienen algo en común, permiten cambiar EL FORMATO (forma, colores, letra, tamaño) según la regla. NOSOTROS seleccionamos la segunda opción: “Aplicar formato contengan…”
únicamente
a
las
celdas
que
Debería aparecer lo siguiente:
Allí elegimos: TEXTO ESPECIFICO:
que contiene
CARNE
Y luego el formato: Con RELLENO VERDE
Luego hacemos una nueva regla y ponemos: o o
TEXTO ESPECIFICO: que contiene Con RELLENO AMARILLO
VERDURAS
Y otra regla más: o o
TEXTO ESPECIFICO: Con RELLENO AZUL
Al terminar debería quedar así:
que contiene
FRUTAS
Y ahora vamos a RESALTAR LOS PRODUCTOS CAROS DE LOS BARATOS. • SELECCIONAMOS de G2 a G16. • FORMATO CONDICIONAL --> ADMINISTRAR REGLAS • Nueva Regla: o Aplicar formato a todas las celdas según sus valores o Allí da la opción de elegir el color más bajo y el más alto Color más bajo: VERDE Color más alto: ROJO
Y si hicimos bien todo debe quedar algo así:
Una vez que llegamos hasta acá vemos que es muy bonito pero lo único que tenemos acá son datos simples y nosotros, en base a estos datos vamos a aprender a hacer una factura… Para ello vamos a ir a la hoja 2 y allí vamos a crear una tabla como esta:
CODIGO: lo vamos a rellenar con una VALIDACION DE DATOS del código de los productos, para ello podemos directamente seleccionarlos de la hoja 1, el rango A2:A16 (en el Excel 2010) o tenemos que copiar los datos de la hoja 1 a la hoja 2 y de allí recién hacer la validación (en el Excel 2001). PRODUCTO: el producto que aparece aquí debería ser el mismo que en la primera hoja pero para que se ponga automáticamente debería usar muchos si o… usar una nueva función llamada BUSCARV (CONSULTA V en el Excel 2010)
BUSCARV (Excel 2007) o CONSULTAV (Excel 2010) BUSCA UN VALOR EN LA PRIMERA COLUMNA DE UNA TABLA Y LUEGO DEVUELVE, DE ESA TABLA, UN VALOR EN OTRA COLUMNA ESPECIFICADA Esta función se aplica mucho en la vida real normalmente, cuando él la boletería nos piden el DNI y con ello te preguntan si te llamas porque a ellos les da el nombre, cuando llamamos a un 0800 nos pide el numero de cliente y luego ya saben todos nuestros datos, cuando tenemos que votar, nos piden NOMBRE Y APELLIDO y a partir de allí nos buscan en el PADRON el DNI y si puedo o no votar, etc... Todas esas búsquedas se realizan en una lista que ya esta armada previamente. Su lenguaje coloquial seria: El “valor” de “valor buscado” Su estructura: =BUSCARV(Valor Buscado; Tabla donde buscar; Columna; Método) Condiciones y Aclaraciones:
los ejemplos con flecha roja, corresponden a resolver C12 • VALOR BUSCADO: corresponde al valor que se debe buscar en la tabla para luego obtener el valor que se desea. Habitualmente es un código, un número, DNI o algo similar. Se encuentra en el mismo lugar donde está la función.
• TABLA DONDE BUSCAR:aquí hay que indicar el rango de una tabla entera, es algo creado anteriormente donde en la primer columna se halla una lista de los distintos valores que puede tomar el valor buscado. • COLUMNA: corresponde a que numero de columna de la TABLA esta el valor que quiero obtener.(en el ejemplo es 2) • METODO: solo se puede colocar VERDADERO (o 1) o FALSO (o 0). o Si ponemos VERDADERO: indica que en la búsqueda, si no encuentra el valor esperado, puede obtener resultados parecidos (ej: si una persona va a comprar mermelada de damasco y no encuentra, puede traer de durazno). oSi ponemos FALSO: indica si en la búsqueda, si no encuentra el valor esperado, devuelve el error #N/A. (ej: si una persona va a comprar mermelada de damasco y no encuentra, no puede traer otra cosa en su lugar). (en el ejemplo es FALSO)
Tal vez, de todas las funciones que vimos, esta es una de las 2 más difíciles de implementar (la otra es el SI anidado) así que vamos a ver cómo resolvemos PRODUCTOS de la hoja 2. En mi caso, suponiendo que en B2 está el valor B31-004,… … en C2: tenemos que obtener el PRODUCTO de B2 =BUSCARV(Valor Buscado; Tabla donde buscar; Columna; Método) =BUSCARV(B2; Tabla donde buscar; Columna; FALSO) FALSO = porque se busca un código y ese debe dar exacto (imagínense que buscara arecido y que sin querer diera el producto de al lado, pero ese valiera $7000?)
=BUSCARV(B2; Hoja1!A2:G36; Columna; 0) Hoja1!A2:G36
quiere decir que hay que ir hasta la HOJA1 y seleccionar TODA la tabla, se pone “;” y recién pude volver a la hoja 2
=BUSCARV(B2; Hoja1!A2:G36; 2; 0) Columna = 2 , porque si contamos con el dedo, de izquierda a derecha el PRODUCTO está en la columna 2 de la tabla (NOTA IMPORTANTE: si toda la tabla estuviera una posición más a la derecha, seguiría siendo 2) PARA PODER ARRASTRAR DEBE QUEDAR ASI: =BUSCARV(B2; Hoja1!$A$2:$G$36; 2; 0)
USTEDES COMPLETEN C2 y luego prueben cambiar el valor de B2 y vean lo que pasa. Luego de eso van a completar productos hasta C23 VALORUNITARIO: aquí debemos poner el precio de venta correspondiente a cada producto… Aquí lo podemos hacer como lo hicimos recién o mejor, vamos a aprender un método que nos aliviará un poco la tarea…
NOMBRE ES PONERLE NOMBRE A UN RANGO DE DATOS, DE MANERA QUE TENGA UN “ALIAS”. SU USO EN LAS FUNCIONES LAS SIMPLIFICAN MUCHO. Con la selección de rango que están en otra hoja, suele hacerse a veces muy complicado tener que estar cuidándonos que al movernos entre las hojas quede de la manera correcta, debido a que si nos desplazamos mal obtenemos cualquier dato menos el esperado… Para poderle poner nombre a un rango de Celdas debemos: 1. Ir hasta donde está el rango: por ejemplo la HOJA1 2. Seleccionar el RANGO: por ejemplo de A2 hasta G36 3. Ubicar el CUADRO DE NOMBRE 4. Escribir allí el ALIAS deseado: por ejemplo DATOS Luego cada vez que seleccionemos ese rango allí aparecerá DATOS.
PARA EDITAR EL RANGO (por si lo escribimos mal) DEBEMOS IR A MENU FORMULAS ADMINISTRADOR DE NOMBRES. Ahora que tenemos un nombre creado vamos a resolver el VALOR UNITARIO de la hoja 2: En mi caso, suponiendo que en B2 está el valor B31-004,… … en D2: tenemos que obtener el VALOR UNITARIO de B2 =BUSCARV(Valor Buscado; Tabla donde buscar; Columna; Método) =BUSCARV(B2; Tabla donde buscar; Columna; FALSO) FALSO = porque se busca un código y ese debe dar exacto (imagínense que buscara arecido y que sin querer diera el producto de al lado, pero ese valiera $7000?)
=BUSCARV(B2; DATOS; Columna; 0) DATOS simplemente lo escribimos y listo, porque fue ese nombre que le dimos al rango de la tabla =BUSCARV(B2; DATOS; 7; 0) Columna = 7 , porque si contamos con el dedo, de izquierda a derecha el TOTAL (que es el precio de venta) está en la columna 7 de la tabla. PARA PODER ARRASTRAR DEBE QUEDAR ASI:=BUSCARV(B2; DATOS; 7; 0) (como notarán no es necesario hacerle nada)
Resolver eso y completar todo el resto hacia abajo. CANTIDAD: es un valor que ustedes deben colocar, sería la cantidad que compra el Cliente. TOTAL: es VALOR UNITARIO * CANTIDAD El TOTAL de ABAJO: es la suma de los totales PAGOCON: es un valor que ustedes deben colocar, es con cuanto pagaría el Cliente VUELTO: es PAGO CON – (menos) TOTAL