Formula excel o macro que selecione un rango de una tabla a partir de una referencia de celda variable
Necesito una formula excel o una macro que seleccione un rango de datos numericos de una tabla ( maestro de materiales) a partir de una referencia de celda variable( varios codigos numericos) y una vez hallado el codigo en la tabla extraiga el rango de datos y lo multiplique por el valor numerico de otra celda. De este modo modo se explosiona el resultado de lo materiales necesarios
En la hoja 2 tengo la tabla, la fila 1 estan los encabezados ( descripcion, cod 1, 2, 3, ... 10.)
Columna A = descripcion a partir de la celda A2
De la columna B a la K losa partir de la segunda fila, se hallan valores numericos
LA TABLA comprende el rango desde A1: K62 ( incluidos los encabezados)
En la hoja 1
Rango fila ( C3:I3) DIAS DE LA SEMANA
Por cada dia se selecionan desde 1 a 2 codigos a fabricar
RANGO C4:I4 ( codigos de la tabla a selecionar en 1º turno)
RANGO C5:I5 ( cantidades de unidades a fabricar del codigo elegido del rango anterior)
RANGO C6:I6( codigos de la tabla a selecionar para el 2º turno)
RANGO C7:I7 ( cantidades de unidades a fabricar del codigo elegido del rango anterior)
En la fila 9 ENCABEZADOS
En a9-materiales, b9 ( um), c9:i9 ( dia de la semana), j9 (semana 1)
A partir dela columan A10 la descripcion de cada material
Necesito que la formula tome el valor de C4 (codigo numerico elegido) y extraiga de tabla ( hoja2) el rango de datos que correspondan a la columna del codigo elegido, lo multiplique por el valor de la celda C5, y que realice la misma operacion con el valor(codigo) de C6 y C7, luego que sume ambos resultados y los a partir de la celda C10 hacia abajo
Esta misma operacion realizarla en cada celda de la columna contigua hasta terminar la semana
En la columna "J" a partir de J10 hacia abajo agrgar la sumatoria de cada fila
Perdon por lo extenso.
1 Respuesta
Hector, te mando una fórmula a colocar en Hoja1 C10 y desde ahí copiar hacia abajo y derecha:
=+BUSCARH(C$4;Hoja2!$A$1:$J$62;+COINCIDIR($A10;Hoja2!$A:$A;0);0)*C$5+BUSCARH(C$6;Hoja2!$A$1:$J$62;+COINCIDIR($A10;Hoja2!$A:$A;0);0)*C$7
Importante: en la Hoja2 la tabla con las descripciones debe estar por orden alfabético ascendente.
Comprueba, p.f., si he interpretado bien tus instrucciones.
Cuando dices "tome el valor de C4 (código numérico elegido) y extraiga de tabla ( hoja2) el rango de datos que correspondan a la columna del código elegido" estoy tomando en Hoja2 el valor numérico resultante de cruzar el código (en fila 1) y la descripción (en columna A) correspondientes ¿es esto?.
Si .Has interpretado bien
En C4 ( codigo) aparece como encabezado también en una de las columnas de a hoja 2 , debajo de ese codigo esta la descripción , sin embargo no esta ordenada alfabeticamente, aunque siempre mantiene el orden en ese rango
Otra cosita, la formula buscarH, en mi versión de excel,esta como consultaH
Saludos
Lo del nombre de la función lo han modificado en alguna de las versiones recientes, supongo 2013 o las de Mac yo tengo Excel 2010 y también el 2000. Buscarv y buscarh es la denominación tradicional desde hace mucho tiempo.
En cuanto al orden alfabétco no te preocupes, en realidad sólo hay que tener cuidado de que no haya descripciones repetidas. Al poner el 4º argumento de buscarh y el 3º de coincidir como 0 daría igual el orden, sólo cuando este argumento es 1 o -1 deben ordenarse.
Hola, estoy probando la formula y anda muy bien , te felicito!!!
Solo una observacion: Cuando elijo cualquier codigo de la tabla de la hoja 2,por ejemplo para el dia lunes, en C4 y C6 , en ambos debe existir dos codigos selelcionados, cuando hay uno solo codigo elegido todos los valores #N/A, ocultando los valores del segundo codigo, me refiero a que por ejemplo El lunes decido realizar un solo codigo, entonces no parecen los valores de materiales del segundo codigo.
Por lo demas esta muy bien!!
saludos
Hector, puedes cambiar la fórmula para C10 por esta:
=+BUSCARH(C$4;Hoja2!$A$1:$J$62;+COINCIDIR($A10;Hoja2!$A:$A;0);0)*C$5+(SI(ESERROR(BUSCARH(C$6;Hoja2!$A$1:$J$62;+COINCIDIR($A10;Hoja2!$A:$A;0)))=VERDADERO;0;BUSCARH(C$6;Hoja2!$A$1:$J$62;+COINCIDIR($A10;Hoja2!$A:$A;0))))*C$7
Ahora en el caso de que el código del turno 2 esté vacío (o el código no coincida con ningún otro) suma cantidad o y ya no da error.
