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