Miércoles 16 de Abril de 2014 | Hay 40 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 Monografías > Computación > Varios >
Material educativo de Alipso relacionado con Funciones busqueda referencia planillas calculo
  • Dinámica de un sistema de partículas: ...
  • Experimentos en microondas.: ...
  • Lenguajes de programcacion de automatas programables.: Lenguajes de programación, Lenguajes Booleanas y lista de instrucciones, Diagrama de contactos, Plano de funciones, Lenguajes de Alto Nivel, Grafcet, Resumen histórico, Diseño basado en Grafcet, Gemma, Resumen.

  • Enlaces externos relacionados con Funciones busqueda referencia planillas calculo

    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 Búsqueda

    y Referencia trabajando con Planillas de Cálculo en Secundaria.

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

    aplicación que consistía en un Formulario para Cálculo de Presupues-

    tos. En la tercera se vio una aplicación sobre Notas (aprobado, di-

    ciembre, marzo, etc.)

    Memo: las funciones de Búsqueda y Refencia son:

    AREAS       Devuelve el número 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

                través 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 número de columnas de una referencia

    COLUMNA     Devuelve el número 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 número de filas de una referencia

    FILA        Devuelve el número 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 transposición de una matriz

       

        Veamos ahora algo sobre la función INDICE en EXCEL.

    ---

    La función 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

    intersección de una fila con una columna) o una matriz de valores. Tra-

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

    una disposición 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_búsqueda;num_fila;num_columna) devuelve el valor de una

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

    _de_búsqueda", seleccionado por los índices de número de fila y de columna

    de la "matriz_de_búsqueda".

    INDICE(matriz_de_búsqueda; num_fila; num_columna)

    Matriz_de_búsqueda: es un rango de celdas que se introduce como una matriz.

    Num_fila: selecciona, en el rango matriz_de_búsqueda, 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_búsqueda, 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 intersección de los

    argumentos num_fila y num_columna.

    Si "matriz_de_búsqueda" contiene sólo una fila o una columna (VECTOR),

    el argumento num_fila o num_columna que corresponde, es opcional.

    Si "matriz_de_búsqueda" tiene más de una fila y más de una columna y

    sólo 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_búsqueda", 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 homogéneos). No tenemos que incluir las dos primeras columnas

    con el Código de artículo ni su descripción o detalle de la mercad.

    Memo: FILa va primero, COLumna va después! :-) 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 aplicación de la Función de

    Búsqueda y Referencia INDICE en su sintaxis matricial:

                INDICE(matriz_de_búsqueda; 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 artículo. Ej. Mayoristas, minoristas.

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

    pondientes al volumen de compra (máximo) registrado en el último pe-

    ríodo (año, mes, bimestre, o lo que se desee) por el cliente.

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

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

      de 5.001 en adelante ... lista 1 (precios más bajos, 10% descuento)

      Hoja 2: Lista_de_Precios_Múltiples.

      ~~~~~~

           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 más

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

    el área de prestación de servicios:

        * mutuales - particulares

        * mayoristas - minoristas - consumidor final

        * regulares - becas - medias_becas, etc.

        La MATRIZ_DE_BUSQUEDA para la función 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 función INDICE en su forma matricial:

        INDICE(matriz_de_búsqueda; num_fil; num_col)

        El num_fil está dado por el ORDEN en que se encuentra el artículo

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

    tamiento, vamos a considerar que los artículos (ver Lista de Precios

    en la nota anterior) están ordenados por un código numérico simple

    que se corresponde biunívocamente con su ordinal en la lista. En el

    caso de que se optara por otro tipo de codificación, tendríamos que

    crear una fórmula que nos convirtiese dicho código en un ordinal

    (ej creando otra tabla del tipo ...

        Codigo    Orden

        12345       1

        24680       2

        ... etc. y luego utilizando la función BUSCARV(cod;matriz;2) )

        El num_col está dado por la LISTA_DE_PRECIOS que dependía del im-

    porte acumulado de compras registrados durante el último período (año,

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

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

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

      de 5.001 en adelante ... lista 1 (precios más bajos, 10% descuento)

        Esto se puede trabajar por el lado de la diagramación lógica li-

    neal (bloques, líneas de flujo ...)

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

                               | C5 = 2500 |

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

                                     |

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

                             <  C5 5000 ? >

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

                       ______________|_________________

                      |                                |

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

               <  C5 1000 ?                        |

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

         NO   ________|_________ SI                    |

             |                  |                      |

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

        |    3    |        |    2    |            |    1    |       

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

                                            

    Esto llevado a función de EXCEL es: | Si "damos" la función 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 comparación]

           ENTONCES le_corresponde_Lista_1

           SINO ... SI C5>1000 ?              [segunda comparación]

                       ENTONCES le_corresponde_Lista_2

                       SINO ... le_corresponde_Lista_3

        En suma, las ALTERNATIVAS simples, dobles o múltiples, se prestan

    para el principal objetivo subyacente a cualquier otro propósito: el

    enseñar a pensar!!! Creando un clima favorable a la creatividad y a la

    participación de los estudiantes se obtienen resultados importantes.

                Finalizando: "la reflexión 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 más, la sintaxis de la función INDICE en su

    forma matricial:

        INDICE(matriz_de_búsqueda; num_fil; num_col)

        Recordemos también que nuestra Lista de Precios MULTIPLES es una

    Tabla de DOBLE ENTRADA. Las dos entradas serían FILA y COLUMNA.

        La FIL estaría dada por el número de artículo o prestación (si

    estamos utilizando códigos numéricos estos tendrían que ser correla-

    tivos y ordenados en forma ascendente).

        La COL sería 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 período 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: sólamente

    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 quedaría ...

         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 función INDICE en su forma matricial:

        INDICE(matriz_de_búsqueda; num_fil; num_col)

    Días atrás mencioné que la última clase con un grupo de ADULTerOS

    vimos la Función INDICE en dos formas:

    a) fácil, 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 serían FILA y COLUMNA, en

    ese orden.

    Si hacemos en otra hoja un BUSCADOR de Información, tendríamos

            A         B          C

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

    1 | Meses   | Columna | Resultado |

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

    2 |    2    |    3    |     400 $ |

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

         Febrero    Saldo     Saldo_feb

    Y la fórmula de [C2] sería: INDICE($B$2:$D$4;A2;B2)

    Facilonga la fórmula!!! Pero ... dificilonga la forma de operar!

    El usuario debería fijarse, previamente, cual es el número de FILA

    que le corresponde al mes requerido. Idem con el número de COLUMNA

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

    sería 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 Información, sería:

            A         B          C

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

    1 | Meses   | Columna | Resultado |

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

    2 | Febrero | Saldo   |     400 $ |

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

    Claro que la fórmula de [C2] sería ...

    =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 más "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 encontrarían el valor porque

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

    primera fila (en el BUSCARH)están DESORDENADOS!!!

    Moraleja: Los TIEMPOS de preparación y de uso son INVERSAMENTE

    PROPORCIONALES! A mayor dedicación al momento de preparar una

    aplicación ... menor tiempo de uso, más fácil, mucha menor

    posibilidad de error, ... Además, mejora el humor, no provoca

    acidez, evita la halitosis, mejora la digestión, deja MAS TIEMPO

    LIBRE para dedicárse al SEXO (o al SESO=pensar, como más 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é? ; Dónde_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é? ; Dónde_busco? ; Fil ; Ordenado? )

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


     
    Sobre ALIPSO.COM

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

    Contacto »
    Contacto

    Teléfono: +54 (011) 3535-7242
    Email:

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