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
Monografías
