Jueves 30 de Octubre de 2014 | Hay 70 usuarios online en este momento!
 

Funciones de busqueda y referencia en planillas de calculo

Imprimir
Recomendar a un amigo
Recordarme el recurso
Descargar como pdf

Seguinos en en Facebook


Agregado: 12 de ABRIL de 2000 (Por ) | Palabras: 2718 | Votar! | Sin Votos | Sin comentarios | Agregar Comentario
Categoría: Apuntes y Monografas > Computacin > Varios >
Material educativo de Alipso relacionado con Funciones busqueda referencia planillas calculo
  • Revisin de la cimentacin de estructuras portuarias: ...
  • Recuperatorio de lengua. Evaluacin trimestral: ...
  • Alimentacin de receptores.: ...

  • Enlaces externos relacionados con Funciones busqueda referencia planillas calculonalga

    FUNCIONES DE BUSQUEDA y REFERENCIA en PLANILLAS DE CALCULOS. #1/4

    -----------------------------------------------------------------

    Por Gustavo O. Delfino - EDUCOMP - master@canopus.com.ar

    En notas anteriores hemos visto algo sobre Funciones de Bsqueda

    y Referencia trabajando con Planillas de Clculo en Secundaria.

    En la primera vimos en detalle BUSCARV. En la segunda vimos una

    aplicacin que consista en un Formulario para Clculo de Presupues-

    tos. En la tercera se vio una aplicacin sobre Notas (aprobado, di-

    ciembre, marzo, etc.)

    Memo: las funciones de Bsqueda y Refencia son:

    AREAS Devuelve el nmero de reas de una referencia

    BUSCARH Busca en la primera fila de una matriz y se desplaza hacia

    abajo en la columna para devolver el valor de una celda

    BUSCARV Busca en la primera columna de una matriz y se desplaza a

    travs de la fila para devolver el valor de una celda

    BUSCAR Busca valores en una referencia o matriz

    COINCIDIR Busca valores en una referencia o matriz

    COLUMNAS Devuelve el nmero de columnas de una referencia

    COLUMNA Devuelve el nmero de columna de una referencia

    DESREF Devuelve una referencia desviada a partir de una refer.dada

    DIRECCION Devuelve a una celda individual una ref. en forma de texto

    ELEGIR Elige un valor de una lista de valores

    FILAS Devuelve el nmero de filas de una referencia

    FILA Devuelve el nmero de fila de una referencia

    INDICE Usa un ndice para elegir un valor de una matriz o de una ref

    INDIRECTO Devuelve una referencia indicada por un valor de texto

    TRANSPONER Devuelve la transposicin de una matriz

    Veamos ahora algo sobre la funcin INDICE en EXCEL.

    ---

    La funcin INDICE() tiene dos sintaxis diferentes: referencial y matri-

    cial. La forma referencial siempre devuelve una referencia y la forma

    matricial siempre devuelve un valor (el contenido de una celda que es la

    interseccin de una fila con una columna) o una matriz de valores. Tra-

    bajaremos sobre la segunda forma (matricial) Recordemos que una matriz es

    una disposicin de valores en forma de columnas y filas

    Sintaxis 1 - Forma referencial

    INDICE(referencia; num_fila;num_columna;num_area) devuelve una referen-

    cia a una celda o a varias celdas dentro del argumento "referencia".

    Sintaxis 2 - Forma matricial

    INDICE(matriz_de_bsqueda;num_fila;num_columna) devuelve el valor de una

    celda (o de una matriz de celdas) determinada dentro del argumento "matriz

    _de_bsqueda", seleccionado por los ndices de nmero de fila y de columna

    de la "matriz_de_bsqueda".

    INDICE(matriz_de_bsqueda; num_fila; num_columna)

    Matriz_de_bsqueda: es un rango de celdas que se introduce como una matriz.

    Num_fila: selecciona, en el rango matriz_de_bsqueda, la fila desde la

    cual se devolver un valor. Si se omite num_fila, se requiere el ar-

    gumento num_columna.

    Num_columna: selecciona, en el rango matriz_de_bsqueda, la columna des-

    de la cual se devolver un valor. Si se omite num_columna, se re-

    quiere el argumento num_fila.

    Observaciones (simplificadas)

    Si se utilizan ambos argumentos: num_fila y num_columna, INDICE devuelve

    el valor (contenido) que se encuentra en la celda de interseccin de los

    argumentos num_fila y num_columna.

    Si "matriz_de_bsqueda" contiene slo una fila o una columna (VECTOR),

    el argumento num_fila o num_columna que corresponde, es opcional.

    Si "matriz_de_bsqueda" tiene ms de una fila y ms de una columna y

    slo utiliza num_fila o num_columna, INDICE devuelve una matriz con toda

    una fila o columna.

    Si se define num_fila o num_columna como 0, INDICE devuelve la matriz de

    valores de toda la columna o fila, respectivamente.

    Los argumentos num_fila y num_columna deben indicar una celda contenida

    en la "matriz_de_bsqueda", de lo contrario, INDICE devuelve el valor

    de error #REF!

    Ejemplo:

    A B C D

    +--------+------------------------+-------------+-------------+

    1 | Codigo | Descripcion | L_Precios_1 | L_Precios_2 |

    +--------+------------------------+-------------+-------------+

    2 | 1 | Tubos 40 Watts Luz dia | 3,50 $ | 5,75 $ |

    +--------+------------------------+-------------+-------------+

    3 | 2 | Arrancadores | 1,25 $ | 2,30 $ |

    +--------+------------------------+-------------+-------------+

    ... supongamos que la parte de precios llega hasta la fila 20

    Si en una celda colocamos =INDICE(C2:D20;2;1) obtenemos 1,25$. Ojo

    al definir la matriz (el rango). Solamente abarcamos a los precios!!!

    (datos homogneos). No tenemos que incluir las dos primeras columnas

    con el Cdigo de artculo ni su descripcin o detalle de la mercad.

    Memo: FILa va primero, COLumna va despus! :-) Para los pibes ...

    Phil Collins :-)

    ---

    Sigue.

    ---

    Gustavo O. Delfino

    master@canopus.com.ar

    FUNCIONES DE BUSQUEDA y REFERENCIA en PLANILLAS DE CALCULOS. #2/4

    -----------------------------------------------------------------

    Por Gustavo O. Delfino - EDUCOMP - master@canopus.com.ar

    Vamos a desarrollar un ejemplo de aplicacin de la Funcin de

    Bsqueda y Referencia INDICE en su sintaxis matricial:

    INDICE(matriz_de_bsqueda; num_fil; num_col)

    Para esto vamos a trabajar en dos hojas. En la segunda desarrolla-

    mos la LISTA DE PRECIOS MULTIPLES, entendiendo por tal a una lista con

    varios precios para un mismo artculo. Ej. Mayoristas, minoristas.

    Supongamos que nuestra empresa tiene 3 listas de precios, corres-

    pondientes al volumen de compra (mximo) registrado en el ltimo pe-

    rodo (ao, mes, bimestre, o lo que se desee) por el cliente.

    de 0 a 1.000 $ ..... lista 3 (precios ms altos, 15% de recargo)

    de 1.001 a 5.000 $ ..... lista 2 (valor de referencia)

    de 5.001 en adelante ... lista 1 (precios ms bajos, 10% descuento)

    Hoja 2: Lista_de_Precios_Mltiples.

    ~~~~~~

    A B C D E

    +--------+----------------------+----------+----------+----------+

    1 | LISTA DE PRECIOS MULTIPLE | -10 | 1 | 15 |

    +--------+----------------------+----------+----------+----------+

    2 | Codigo | Detalle | L_de_P_1 | L_de_P_2 | L_de_P_3 |

    +--------+----------------------+----------+----------+----------+

    3 | 1 | Arroz grano relleno | 11,11 $ | 12,34 $ | 14,19 $ |

    +--------+----------------------+----------+----------+----------+

    4 | 2 | Aceite primer orde~e | 6,89 $ | 7,65 $ | 8,80 $ |

    +--------+----------------------+----------+----------+----------+

    5 | 3 | Pinza pico de loro | 311,10 $ | 345,67 $ | 397,52 $ |

    +--------+----------------------+----------+----------+----------+

    6 | 4 | Martillo de bola | 8,01 $ | 8,90 $ | 10,24 $ |

    +--------+----------------------+----------+----------+----------+

    7 | 5 | Lamparas de Aladino | 0,11 $ | 0,12 $ | 0,14 $ |

    +--------+----------------------+----------+----------+----------+

    8 | 6 | Merluza fresquita | 21,11 $ | 23,45 $ | 26,97 $ |

    +--------+----------------------+----------+----------+----------+

    [C3] = =D3*(1+$C$1/100) copiar al resto C4:C8

    [D3] = dato ; precio de referencia (inventados en este ejemplo)

    [E3] = =D3*(1+$E$1/100) copiar al resto E4:E8

    Destacar con los alumnos que con esta forma nos ahorramos de hacer

    tres listas de precios tres! Sencillamente agregamos una columna ms

    por cada lista de precios. Esto es bastante usual en el comercio y en

    el rea de prestacin de servicios:

    * mutuales - particulares

    * mayoristas - minoristas - consumidor final

    * regulares - becas - medias_becas, etc.

    La MATRIZ_DE_BUSQUEDA para la funcin INDICE ser ... ???

    a) ___ A2:E8 b) ___ A3:E8 c) ___ C2:E8 d) ___ C3:E8

    e) ___ $A$2:$E$8 f) ___ $A$3:$E$8 g) ___ $C$2:$E$8 h) ___ $C$3:$E$8

    ---

    sigue.

    ---

    FUNCIONES DE BUSQUEDA y REFERENCIA en PLANILLAS DE CALCULOS. #3/4

    -----------------------------------------------------------------

    Por Gustavo O. Delfino - EDUCOMP - master@canopus.com.ar

    Recordemos la sintaxis de la funcin INDICE en su forma matricial:

    INDICE(matriz_de_bsqueda; num_fil; num_col)

    El num_fil est dado por el ORDEN en que se encuentra el artculo

    del cual deseamos averiguar el precio. A efectos de simplificar el tra-

    tamiento, vamos a considerar que los artculos (ver Lista de Precios

    en la nota anterior) estn ordenados por un cdigo numrico simple

    que se corresponde biunvocamente con su ordinal en la lista. En el

    caso de que se optara por otro tipo de codificacin, tendramos que

    crear una frmula que nos convirtiese dicho cdigo en un ordinal

    (ej creando otra tabla del tipo ...

    Codigo Orden

    12345 1

    24680 2

    ... etc. y luego utilizando la funcin BUSCARV(cod;matriz;2) )

    El num_col est dado por la LISTA_DE_PRECIOS que dependa del im-

    porte acumulado de compras registrados durante el ltimo perodo (ao,

    mes, bimestre, o lo que se desee) por el cliente.

    de 0 a 1.000 $ ..... lista 3 (precios ms altos, 15% de recargo)

    de 1.001 a 5.000 $ ..... lista 2 (valor de referencia)

    de 5.001 en adelante ... lista 1 (precios ms bajos, 10% descuento)

    Esto se puede trabajar por el lado de la diagramacin lgica li-

    neal (bloques, lneas de flujo ...)

    +-----------+

    | C5 = 2500 |

    +-----------+

    |

    +-------------+

    < C5 5000 ? >

    NO +-------------+ SI

    ______________|_________________

    | |

    +------------+ |

    < C5 1000 ? |

    +------------+ |

    NO ________|_________ SI |

    | | |

    +---------+ +---------+ +---------+

    | 3 | | 2 | | 1 |

    +---------+ +---------+ +---------+

    Esto llevado a funcin de EXCEL es: | Si "damos" la funcin asi, tal

    | cual, sin esfuerzo por parte de

    SI(C5>5000;1;SI(C5>1000;2;3)) | los pibes, no sirve. La idea es

    | hacerlos transpirar ... las neu-

    Otra forma ... | ronas! :-)

    SI C5>5000 ? [primera comparacin]

    ENTONCES le_corresponde_Lista_1

    SINO ... SI C5>1000 ? [segunda comparacin]

    ENTONCES le_corresponde_Lista_2

    SINO ... le_corresponde_Lista_3

    En suma, las ALTERNATIVAS simples, dobles o mltiples, se prestan

    para el principal objetivo subyacente a cualquier otro propsito: el

    ensear a pensar!!! Creando un clima favorable a la creatividad y a la

    participacin de los estudiantes se obtienen resultados importantes.

    Finalizando: "la reflexin rehuye a la prisa"

    FUNCIONES DE BUSQUEDA y REFERENCIA en PLANILLAS DE CALCULOS. #4/4

    -----------------------------------------------------------------

    Por Gustavo O. Delfino - EDUCOMP - master@canopus.com.ar

    Recordemos, una vez ms, la sintaxis de la funcin INDICE en su

    forma matricial:

    INDICE(matriz_de_bsqueda; num_fil; num_col)

    Recordemos tambin que nuestra Lista de Precios MULTIPLES es una

    Tabla de DOBLE ENTRADA. Las dos entradas seran FILA y COLUMNA.

    La FIL estara dada por el nmero de artculo o prestacin (si

    estamos utilizando cdigos numricos estos tendran que ser correla-

    tivos y ordenados en forma ascendente).

    La COL sera la Lista de Precios (1, 2 3 en nuestro ejemplo).

    En la Hoja 1 la Lista de Precios figura como L_de_Precio (que le corres-

    ponde a cada cliente de acuerdo al importe ACUMULADO de compras hechas

    en el ltimo perodo considerado)

    Ejemplo ya visto: 1ra Col "2da" Col

    A B C | -D-

    +--------+------------------------+-------------+--|----------+

    1 | Codigo | Descripcion | L_Precios_1 | L_Precios_2 |

    +--------+------------------------+-------------+--|----------+

    2 | 1 | Tubos 40 Watts Luz dia | 3,50 $ | | 5,75 $ |

    +--------+------------------------+-------------+--|----------+

    3 | 2 | Arrancadores | 1,25 $ | | 2,30 $ |

    +--------+------------------------+-------------+--|----------+

    4 | --3 --|-Adaptadores 3 a 2------|----98,76 $--|-- 123,45 $ | <<

    +--------+------------------------+-------------+-------------+

    ---

    ... supongamos que la parte de precios llega hasta la fila 20

    definimos la MATRIZ_DE_BUSQUEDA como $C$2:$D$20. Es decir: slamente

    la parte de precios! exclusivamente los precios!

    Si pedimos INDICE($C$2:$D$20;3;2) -Excel devuelve 123,45

    siendo 3 la fila y 2 la columna.

    ---

    La Hoja 1 de nuestro ejemplo, nos quedara ...

    A B C D E

    01 THE BEST I. A. C Nro 0000 - 00000001

    02 Av Rivadavia 12345 Fecha: ___/___/19__

    03 1704 Ramos Mejia City Cuit: 20-12345678-9

    04 Pcia. Buenos Aires Ingr.Bestias N 070-12345678/9

    05 Caja Autonomos A 333333

    06 Sres/as/ita: Elba Gallo (Pirucha)

    07 Domicilio: Cucha! Cucha! 1234 Dto Al fondo

    08 IVA Resp.Incript: ___ Resp.No Inscr: ___ Exento: ___ Cons.Final: ___

    09 C.U.I.T. Nro ___________________ Ingresos Brutos: __________________

    10

    11 Condiciones de Venta: Ctdo ___ Cta.Cte ___ Acumulado: 2.500,00 $

    12

    13 L_de_Precio: 2 =SI($E$11>5000;1;SI($E$11>1000;2;3))

    14

    15 Codigo Cantidad Descripcion Precio Uni Importe

    16 2 3 Aceite primer orde~e 7,65 $ 22,95 $

    17 3 1 Pinza pico de loro 345,67 $ 345,67 $

    18 5 2 Lamparas de Aladino 0,12 $ 0,24 $

    19

    20 TOTAL: 368,86 $

    [C16] =SI($A16="";"";BUSCARV($A16;Hoja2!$A$3:$B$8;2))

    [D16] =SI($A16="";"";INDICE(Hoja2!$C$3:$E$8;$A16;$B$13))

    [E16] =SI($A16="";"";$D16*$E16) (Precio Unitario * Importe)

    [D16] =SI($A16="";"";INDICE(Hoja2!$C$3:$E$8;$A16;L_de_Precio))

    FUNCIONES DE BUSQUEDA y REFERENCIA en PLANILLAS DE CALCULOS. #4/4

    -----------------------------------------------------------------

    Por Gustavo O. Delfino - EDUCOMP - master@canopus.com.ar

    Ya vimos la sintaxis de la funcin INDICE en su forma matricial:

    INDICE(matriz_de_bsqueda; num_fil; num_col)

    Das atrs mencion que la ltima clase con un grupo de ADULTerOS

    vimos la Funcin INDICE en dos formas:

    a) fcil, sencillita

    b) king size, complicadita

    Supongamos que tenemos una Tabla de Datos conteniendo ...

    Rango (matriz) de BUSQUEDA = $B$2:$D$4

    A B C D

    +---------+----------+---------+----------+

    1 | Meses | Ingresos | Egresos | Saldos |

    +---------+----------+---------+----------+

    2 | Enero | 1.200 $ | 900 $ | 300 $ |

    +---------+----------+---------+----------+

    3 | Febrero | 1.500 $ | 1.100 $ | 400 $ |

    +---------+----------+---------+----------+

    4 | Marzo | 1.800 $ | 1.200 $ | 600 $ |

    +---------+----------+---------+----------+

    a) Sencillita = facilonga

    Esta tabla, tal cual la Lista de Precios MULTIPLES anterior, es una

    Tabla de DOBLE ENTRADA. Las dos entradas seran FILA y COLUMNA, en

    ese orden.

    Si hacemos en otra hoja un BUSCADOR de Informacin, tendramos

    A B C

    +---------+---------+-----------+

    1 | Meses | Columna | Resultado |

    +---------+---------+-----------+

    2 | 2 | 3 | 400 $ |

    +---------+---------+-----------+

    Febrero Saldo Saldo_feb

    Y la frmula de [C2] sera: INDICE($B$2:$D$4;A2;B2)

    Facilonga la frmula!!! Pero ... dificilonga la forma de operar!

    El usuario debera fijarse, previamente, cual es el nmero de FILA

    que le corresponde al mes requerido. Idem con el nmero de COLUMNA

    que necesita. Y ... este ejemplo es una pavada! Pensemos lo que

    sera buscar en una TABLA de DATOS en serio!

    b) Forma 'king-size' ;-) para valientes!!!

    Modifiquemos ahora nuestra TABLA DE DATOS de modo de agregarle

    ... ayudas! Insertamos una FILA y una COLUMNA

    Nuevo RANGO (matriz) de BUSQUEDA = $C$3:$E$5

    A B C D E

    +---------+----------+----------+----------+----------+

    1 | Meses | | Ingresos | Egresos | Saldos |

    +---------+----------+----------+----------+----------+

    2 | Meses | | 1 | 2 | 3 |

    +---------+----------+----------+----------+----------+

    3 | Enero | 1 | 1.200 $ | 900 $ | 300 $ |

    +---------+----------+----------+----------+----------+

    4 | Febrero | 2 | 1.500 $ | 1.100 $ | 400 $ |

    +---------+----------+----------+----------+----------+

    5 | Marzo | 3 | 1.800 $ | 1.200 $ | 600 $ |

    +---------+----------+----------+----------+----------+

    Si re-hacemos en otra hoja el BUSCADOR de Informacin, sera:

    A B C

    +---------+---------+-----------+

    1 | Meses | Columna | Resultado |

    +---------+---------+-----------+

    2 | Febrero | Saldo | 400 $ |

    +---------+---------+-----------+

    Claro que la frmula de [C2] sera ...

    =INDICE($C$3:$E$5;BUSCARV(A2;$A$3:$B$5;2;falso);BUSCARH(B2;$C$1:$E2;2;falso))

    En esencia qu ha cambiado? Simple: cambi la forma de preguntar!

    La hemos hecho mucho ms "USER FRIENDLY" En vez de pedir el dato:

    Fil=2 y Col=3, hemos pedido el Saldo de Febrero !!!!!!!!!!

    OjO con 'FALSO' tanto en BUSCARV como en BUSCARH !!! Si se lo

    omite el BUSCARV y el BUSCARH no encontraran el valor porque

    los valores de la primera columna (en el BUSCARV) y de la

    primera fila (en el BUSCARH)estn DESORDENADOS!!!

    Moraleja: Los TIEMPOS de preparacin y de uso son INVERSAMENTE

    PROPORCIONALES! A mayor dedicacin al momento de preparar una

    aplicacin ... menor tiempo de uso, ms fcil, mucha menor

    posibilidad de error, ... Adems, mejora el humor, no provoca

    acidez, evita la halitosis, mejora la digestin, deja MAS TIEMPO

    LIBRE para dedicrse al SEXO (o al SESO=pensar, como ms le guste) :-)

    ---

    BUSCARV(A2;$A$3:$B$5;2;falso) recordar: Falso =desordenado

    A B

    +---------+----------+

    3 | Enero | 1 |

    +---------+----------+

    4 | Febrero | 2 |

    +---------+----------+

    5 | Marzo | 3 |

    +---------+----------+

    Memo: BUSCARV( Qu? ; Dnde_busco? ; Col ; Ordenado? )

    A2 $A$3:$B$5 2 Falso

    BUSCARH(B2;$C$1:$E2;2;falso)

    C D E

    +----------+----------+----------+

    1 | Ingresos | Egresos | Saldos |

    +----------+----------+----------+

    2 | 1 | 2 | 3 |

    +----------+----------+----------+

    Memo: BUSCARH( Qu? ; Dnde_busco? ; Fil ; Ordenado? )

    B2 $C$1:$E$2 2 Falso


     
    Sobre ALIPSO.COM

    Monografias, Exmenes, Universidades, Terciarios, Carreras, Cursos, Donde Estudiar, Que Estudiar y ms: Desde 1999 brindamos a los estudiantes y docentes un lugar para publicar contenido educativo y nutrirse del conocimiento.

    Contacto »
    Contacto

    Telfono: +54 (011) 3535-7242
    Email:

    Formulario de Contacto Online »
     
    Cerrar Ventana
    ALIPSO.COM
    Cursos Multimedia Online, CD y DVD