Buscar V en Extracción de un rango de datos de una tab
Hola Estimado:
Leí tu post sobre como usar fórmulas matriciales, pero quede con una duda, como podría obtener las ganancias de castro el 06-10... Osea hacer una búsqueda pero de dos valores buscados.
Copio tu post para recordarte el tema.
Hola:
Bueno es posible. Comprendo la utilidad de buscarv(). Entiendo a la vez tu problema que quieres resolver. No se si habrás escuchado de las fórmulas matriciales, las cuales son muy poderosas para resolver cálculos complejos. Precisamente vamos a recurrir a las matriciales. Haré un ejemplo sencillo y por deducción lo generalizas a tu proyecto.
Abrimos un libro nuevo, ahora en la Hoja 1 situemosno. Haremos una pequeña base de datos mediante "Lista". La base de datos tendrá 3 columnas. Coloquemos los títulos de las columnas así:
A1: Fecha
B1: Vendedor
C1: Ventas
Vamos ahora a registrar 6 ventas.
Fila 2
En A2: Digitar la fecha 02/10 (debe darnos como resultado en la celda 02-oct)
En B2: Castro
En C2: 1000
FILA 3 (Vamos a hacer que hubo en la fecha 02-10 otra venta por otro vendedor)
En A3: Digitar la fecha 02/10 (debe darnos como resultado en la celda 02-oct)
En B3: Alvarez
En C3: 320
FILA 4 (Vamos a hacer que hubo en la fecha 02-10 otra venta por otro vendedor)
En A4: Digitar la fecha 02/10 (debe darnos como resultado en la celda 02-oct)
En B4: Gonzales
En C4: 4500
Fila 5
En A5: Digitar la fecha 03/10 (Otra fecha)
En B5: Castro
En C5: 650
Fila 6
En A6: Digitar la fecha 04/10 (Otra fecha)
En B6: Alvarez
En C6: 630
FILA 7 (Vamos a hacer que hubo en la fecha 04-10 otra venta por otro vendedor)
En A7: Digitar la fecha 04/10
En B7: Gonzales
En C7: 850
Bien, tenemos nuestra pequeña base de datos que abarca el rango A1:C7. Pues bien, pero para extraer mediante un informe de tabla dinámica es conveniente convertir nuestra base de datos en una lista. Situemosno ahora en cualquier parte dentro de la base de datos. Por ejemplo escojo que sea B5, que justo cae donde el vendedor "Castro", ahora presionar Ctrl + QUE (Menu Datos / Lista / Crear lista), aparecerá una ventana, importante activar la opción "La lista tiene encabezado" y aceptar. Vera que los títulos se pondrán en negrita y se enmarcara de un color azul. Eso es una lista la cual servirá mucho cuando ingresemos más datos, más ventas, y cuando creamos una tabla dinámica podrá reconocer lo que se encuentra dentro de la lista. Es una ventaja contar con esta herramienta.
Pues bien, si hasta acá hemos hecho bien las cosas, lo que vendrá ahora es importantísimo.
Imaginemos que alguien nos pregunte: ¿Cuál es el total de ventas de la fecha 02-oct? ó tal vez ¿Cuál es el total de ventas hecho por el vendedor Castro?
Si lo hacemos con buscarv() y hacemos que el valor buscado sea 02-oct saldrá un error puesto que hay varias filas con 02-oct. Entonces la necesidad es sumar simplemente de todo el rango de ventas, SOLAMENTE una parte de ese rango, el que me interesa es solamente es las ventas del 02-oct.
Situemosno en A15, y digitemos un titulo, que sea "Fecha" y en B15 "Ventas". Situemosno en A16, alli digitaremos la fecha que queremos consultar (en nuestro caso 02/10), a su derecha (B16) deseo saber las ventas de solamente la fecha indicada en B15. ¿Como podemos hacerlo?
Escribamos 02/10 en A16, esa es la fecha en consulta. Ahora situemosno en B16 y digitaremos la siguiente fórmula:
=SUMA(SI(A2:A7=A16;C2:C7))
Una vez ingresado la fórmula oprima Enter. No se asuste después de esto, no piense que la fórmula esta mal ingresa. Le falta algo a la fórmula para que sea matricial, ¿cuál?. Al inicio como al final de la fórmula tiene que tener llaves ({}). Ojo: Las llevas no se tienen que digitar, mucho menos copiar y pegar desde un archivo al Excel. Las llaves se insertan en la misma fórmula al hacer una combinación de teclas en la misma fórmula. Veamos.
Situemosno en la celda que contiene la fórmula, es decir en B16, ahora oprimir F2, veremos la fórmula. Ahora realizar la combinacion de teclas: Ctrl + Schift + Enter. (Ojo: la tecla Schift es aquella que esta arriba de Ctrl). No es necesario oprimir enter puesto que en la combinación dimos enter. Es necesario hacer esto para que se inserte las llaves dentro de la fórmula. Si miramos la fórmula en la barra de fórmulas veremos las llaves. ADVERTENCIA: Si queremos editar la fórmula podemos hacerlo, pero no podemos aceptar la fórmula oprimiendo Enter, debemos hacerlo con Ctrl +Schift + Enter (es decir la combinación ya mencionada). Porque si editamos y damos enter, la fórmula regresara a referencial y no a matricial. Matricial es cuando tiene llaves.
La respuesta que debe darnos es de 5820, esa es la venta generada en la fecha 02-oct. Como veras solamente ha tomado una parte de todo el rango.
Hagamos una segunda consulta.
En D15, demos el titulo "Vendedor" y en E15 "Ventas". En esat ocasión quiero saber la suma de ventas de un vendedor
En D16, escribamos a Castro, tal como hemos escrito en nuestra base de datos. En E16 quiero el resultado de la consulta. Para ello situemosno en E16 y digitemos la siguiente fórmula:
=SUMA(SI(B2:B7=D16;C2:C7))
Una vez digitado el ultimo paréntesis de la fórmula indicada, presionar Ctrl +Schift + Enter, para convertir a matricial. El resultado sera 1650. Quiere decir que las ventas hechas por el vendedor "Castro" fueron 1650 unidades monetarias.
Espero haberte ayudado con mi explicación detallada con 2 ejercicios sencillos que se pueden ampliar según las necesidades.
Por favor, no te olvides de finalizar y puntual la respuesta. De no hacerlo Todoexpertos, no podrá brindarte la oportunidad de realizar otra consulta a un experto.
Saludos,
Christian
Leí tu post sobre como usar fórmulas matriciales, pero quede con una duda, como podría obtener las ganancias de castro el 06-10... Osea hacer una búsqueda pero de dos valores buscados.
Copio tu post para recordarte el tema.
Hola:
Bueno es posible. Comprendo la utilidad de buscarv(). Entiendo a la vez tu problema que quieres resolver. No se si habrás escuchado de las fórmulas matriciales, las cuales son muy poderosas para resolver cálculos complejos. Precisamente vamos a recurrir a las matriciales. Haré un ejemplo sencillo y por deducción lo generalizas a tu proyecto.
Abrimos un libro nuevo, ahora en la Hoja 1 situemosno. Haremos una pequeña base de datos mediante "Lista". La base de datos tendrá 3 columnas. Coloquemos los títulos de las columnas así:
A1: Fecha
B1: Vendedor
C1: Ventas
Vamos ahora a registrar 6 ventas.
Fila 2
En A2: Digitar la fecha 02/10 (debe darnos como resultado en la celda 02-oct)
En B2: Castro
En C2: 1000
FILA 3 (Vamos a hacer que hubo en la fecha 02-10 otra venta por otro vendedor)
En A3: Digitar la fecha 02/10 (debe darnos como resultado en la celda 02-oct)
En B3: Alvarez
En C3: 320
FILA 4 (Vamos a hacer que hubo en la fecha 02-10 otra venta por otro vendedor)
En A4: Digitar la fecha 02/10 (debe darnos como resultado en la celda 02-oct)
En B4: Gonzales
En C4: 4500
Fila 5
En A5: Digitar la fecha 03/10 (Otra fecha)
En B5: Castro
En C5: 650
Fila 6
En A6: Digitar la fecha 04/10 (Otra fecha)
En B6: Alvarez
En C6: 630
FILA 7 (Vamos a hacer que hubo en la fecha 04-10 otra venta por otro vendedor)
En A7: Digitar la fecha 04/10
En B7: Gonzales
En C7: 850
Bien, tenemos nuestra pequeña base de datos que abarca el rango A1:C7. Pues bien, pero para extraer mediante un informe de tabla dinámica es conveniente convertir nuestra base de datos en una lista. Situemosno ahora en cualquier parte dentro de la base de datos. Por ejemplo escojo que sea B5, que justo cae donde el vendedor "Castro", ahora presionar Ctrl + QUE (Menu Datos / Lista / Crear lista), aparecerá una ventana, importante activar la opción "La lista tiene encabezado" y aceptar. Vera que los títulos se pondrán en negrita y se enmarcara de un color azul. Eso es una lista la cual servirá mucho cuando ingresemos más datos, más ventas, y cuando creamos una tabla dinámica podrá reconocer lo que se encuentra dentro de la lista. Es una ventaja contar con esta herramienta.
Pues bien, si hasta acá hemos hecho bien las cosas, lo que vendrá ahora es importantísimo.
Imaginemos que alguien nos pregunte: ¿Cuál es el total de ventas de la fecha 02-oct? ó tal vez ¿Cuál es el total de ventas hecho por el vendedor Castro?
Si lo hacemos con buscarv() y hacemos que el valor buscado sea 02-oct saldrá un error puesto que hay varias filas con 02-oct. Entonces la necesidad es sumar simplemente de todo el rango de ventas, SOLAMENTE una parte de ese rango, el que me interesa es solamente es las ventas del 02-oct.
Situemosno en A15, y digitemos un titulo, que sea "Fecha" y en B15 "Ventas". Situemosno en A16, alli digitaremos la fecha que queremos consultar (en nuestro caso 02/10), a su derecha (B16) deseo saber las ventas de solamente la fecha indicada en B15. ¿Como podemos hacerlo?
Escribamos 02/10 en A16, esa es la fecha en consulta. Ahora situemosno en B16 y digitaremos la siguiente fórmula:
=SUMA(SI(A2:A7=A16;C2:C7))
Una vez ingresado la fórmula oprima Enter. No se asuste después de esto, no piense que la fórmula esta mal ingresa. Le falta algo a la fórmula para que sea matricial, ¿cuál?. Al inicio como al final de la fórmula tiene que tener llaves ({}). Ojo: Las llevas no se tienen que digitar, mucho menos copiar y pegar desde un archivo al Excel. Las llaves se insertan en la misma fórmula al hacer una combinación de teclas en la misma fórmula. Veamos.
Situemosno en la celda que contiene la fórmula, es decir en B16, ahora oprimir F2, veremos la fórmula. Ahora realizar la combinacion de teclas: Ctrl + Schift + Enter. (Ojo: la tecla Schift es aquella que esta arriba de Ctrl). No es necesario oprimir enter puesto que en la combinación dimos enter. Es necesario hacer esto para que se inserte las llaves dentro de la fórmula. Si miramos la fórmula en la barra de fórmulas veremos las llaves. ADVERTENCIA: Si queremos editar la fórmula podemos hacerlo, pero no podemos aceptar la fórmula oprimiendo Enter, debemos hacerlo con Ctrl +Schift + Enter (es decir la combinación ya mencionada). Porque si editamos y damos enter, la fórmula regresara a referencial y no a matricial. Matricial es cuando tiene llaves.
La respuesta que debe darnos es de 5820, esa es la venta generada en la fecha 02-oct. Como veras solamente ha tomado una parte de todo el rango.
Hagamos una segunda consulta.
En D15, demos el titulo "Vendedor" y en E15 "Ventas". En esat ocasión quiero saber la suma de ventas de un vendedor
En D16, escribamos a Castro, tal como hemos escrito en nuestra base de datos. En E16 quiero el resultado de la consulta. Para ello situemosno en E16 y digitemos la siguiente fórmula:
=SUMA(SI(B2:B7=D16;C2:C7))
Una vez digitado el ultimo paréntesis de la fórmula indicada, presionar Ctrl +Schift + Enter, para convertir a matricial. El resultado sera 1650. Quiere decir que las ventas hechas por el vendedor "Castro" fueron 1650 unidades monetarias.
Espero haberte ayudado con mi explicación detallada con 2 ejercicios sencillos que se pueden ampliar según las necesidades.
Por favor, no te olvides de finalizar y puntual la respuesta. De no hacerlo Todoexpertos, no podrá brindarte la oportunidad de realizar otra consulta a un experto.
Saludos,
Christian
1 Respuesta
Respuesta de 230283
1
Claro que es posible realizar búsquedas con criterios múltiples que nos permite solamente extraer un rango determinado.
Podríamos afirmar que las Matriciales son casi las medias hermanas de los Filtros cuya función es similar.
En el ejemplo que detalle, es posible realizar la búsqueda mediante 1 criterio (como se hizo), pero se puede hacer con 2, 3, 4, etc criterios.
Bueno el ejemplo que hice solamente abarco fechas desde el 02-oct hasta el 04-oct y no hasta el 06-oct como señalas, se puede realizar la búsqueda del 06-oct siempre y cuando haya datos de fecha 06-oct.
Vamos a resolver ahora la siguiente pregunta. ¿Cuánto es la ganancia del vendedor Gonzales en la fecha 02-oct?
Ojo: Sigo manteniendo yo las ubicaciones de los valores y títulos.
Posicionemosno en Hoja 1, copiemos los títulos, es decir A1:C1 y lo pegamos en F1, al hacerlo se pegara desde F1:H1. Ahora escribamos los criterios de búsqueda.
En F2, escribamos la fecha 2/10 (02-oct)
En G2, escribamos el vendedor, en este caso "Gonzales", ahora en H2, deseamos que nos muestre el resultado de las ventas teniendo en cuenta estos dos criterios.
Situemosno en H2 y escribir la fórmula:
=SUMA(SI((A2:A7=F2)*(B2:B7=G2);C2:C7))
Después de escribir el ultimo paréntesis de la fórmula hacer Ctrl + Schift + Enter para insertar las llaves.
El resultado de esta operación debe ser 4500 unidades monetarias. Es decir, en la fecha 02-oct, el vendedor Gonzales recaudo una venta de 4500 um.
En la primera respuesta que te hice llegar en la fórmula se utiliza las funciones Suma() y Si() paralelamente.
Detengamosno en la función Si(), la cual tiene 3 partes (prueba logica; valor si verdadero; valor si falso)
En el argumento "Prueba lógica" hay 2 criterios entre paréntesis que se multiplican entre si. Si hay la necesidad de realizar la búsqueda tomando 3 criterios entonces dentro del argumento "Prueba lógica" deberá haber 3 factores que se multiplican entre si.
Para 2 criterios:
=Si((criterio 1)*(criterio 2); VALOR SI VERDADERO; VALOR SI FALSO)
para 3 criterios:
=Si((criterio 1)*(criterio 2)*(criterio 3); VALOR SI VERDADERO;VALOR SI FALSO)
En las fórmulas matriciales anteriores, como te darás cuenta no se ha colocado el argumento "VALOR SI FALSO" por lo que es opcional, pero si es obligatorio el argumento "VALOR SI VERDADERO"
Puedes generalizar con este sencillo ejemplo para tus proyectos y veras que obtendrás buenos resultados sin mucho esfuerzo gracias a las matriciales.
Por favor, no te olvides de finalizar y puntuar la respuesta.
Podríamos afirmar que las Matriciales son casi las medias hermanas de los Filtros cuya función es similar.
En el ejemplo que detalle, es posible realizar la búsqueda mediante 1 criterio (como se hizo), pero se puede hacer con 2, 3, 4, etc criterios.
Bueno el ejemplo que hice solamente abarco fechas desde el 02-oct hasta el 04-oct y no hasta el 06-oct como señalas, se puede realizar la búsqueda del 06-oct siempre y cuando haya datos de fecha 06-oct.
Vamos a resolver ahora la siguiente pregunta. ¿Cuánto es la ganancia del vendedor Gonzales en la fecha 02-oct?
Ojo: Sigo manteniendo yo las ubicaciones de los valores y títulos.
Posicionemosno en Hoja 1, copiemos los títulos, es decir A1:C1 y lo pegamos en F1, al hacerlo se pegara desde F1:H1. Ahora escribamos los criterios de búsqueda.
En F2, escribamos la fecha 2/10 (02-oct)
En G2, escribamos el vendedor, en este caso "Gonzales", ahora en H2, deseamos que nos muestre el resultado de las ventas teniendo en cuenta estos dos criterios.
Situemosno en H2 y escribir la fórmula:
=SUMA(SI((A2:A7=F2)*(B2:B7=G2);C2:C7))
Después de escribir el ultimo paréntesis de la fórmula hacer Ctrl + Schift + Enter para insertar las llaves.
El resultado de esta operación debe ser 4500 unidades monetarias. Es decir, en la fecha 02-oct, el vendedor Gonzales recaudo una venta de 4500 um.
En la primera respuesta que te hice llegar en la fórmula se utiliza las funciones Suma() y Si() paralelamente.
Detengamosno en la función Si(), la cual tiene 3 partes (prueba logica; valor si verdadero; valor si falso)
En el argumento "Prueba lógica" hay 2 criterios entre paréntesis que se multiplican entre si. Si hay la necesidad de realizar la búsqueda tomando 3 criterios entonces dentro del argumento "Prueba lógica" deberá haber 3 factores que se multiplican entre si.
Para 2 criterios:
=Si((criterio 1)*(criterio 2); VALOR SI VERDADERO; VALOR SI FALSO)
para 3 criterios:
=Si((criterio 1)*(criterio 2)*(criterio 3); VALOR SI VERDADERO;VALOR SI FALSO)
En las fórmulas matriciales anteriores, como te darás cuenta no se ha colocado el argumento "VALOR SI FALSO" por lo que es opcional, pero si es obligatorio el argumento "VALOR SI VERDADERO"
Puedes generalizar con este sencillo ejemplo para tus proyectos y veras que obtendrás buenos resultados sin mucho esfuerzo gracias a las matriciales.
Por favor, no te olvides de finalizar y puntuar la respuesta.
