Puedes ayudarme con este ejercicio de matriz de Microsoft Excel
Bueno expongo aquí mi problema, el cual no he sabido solucionar a pesar de la respuesta del la pregunta anterior.
Tengo una matriz de este tipo:
A B C D E F
1 Seccion/nºcondc 2 3 4 5 6
2 1.5 11 11.5 13 15 21
3 2.5 15 17 19 22 31
4 4 20 22 27 31 42
Los valores de la matriz son intensidades de corriente. En principio lo que quiero es pasar en B5 el numero de conductores (números enteros de la fila 1) y en la celda C5 una intensidad calculada con una fórmula que lleva decimales y no coincide con ningún valor de la matriz de intensidades. La idea es que entre en la columna que indique B5 (nºconductores) y luego busque en esa columna el valor inmediato superior a la celda C5, para terminar devolviendo en A5 la sección correspondiente.
Un ejemplo que es un poco lioso:
En B5: 4 en C5: 15.147 por lo que en A5 tendría que devolver o poner 2.5
Apreciaría mucho vuestra ayuda, he probado bastantes cosas pero no he dado con la tecla. Así que de antemano Gracias.
Tengo una matriz de este tipo:
A B C D E F
1 Seccion/nºcondc 2 3 4 5 6
2 1.5 11 11.5 13 15 21
3 2.5 15 17 19 22 31
4 4 20 22 27 31 42
Los valores de la matriz son intensidades de corriente. En principio lo que quiero es pasar en B5 el numero de conductores (números enteros de la fila 1) y en la celda C5 una intensidad calculada con una fórmula que lleva decimales y no coincide con ningún valor de la matriz de intensidades. La idea es que entre en la columna que indique B5 (nºconductores) y luego busque en esa columna el valor inmediato superior a la celda C5, para terminar devolviendo en A5 la sección correspondiente.
Un ejemplo que es un poco lioso:
En B5: 4 en C5: 15.147 por lo que en A5 tendría que devolver o poner 2.5
Apreciaría mucho vuestra ayuda, he probado bastantes cosas pero no he dado con la tecla. Así que de antemano Gracias.
1 Respuesta
Respuesta de jrgces
1
El problema es que los datos de las columnas B a F están ordenados de menor a mayor, pero sucede que COINCIDIR, si lo que se busca es el valor igual o menor a uno dado, necesita que los datos estén ordenados de mayor a menor. Así pues la pregunta es: ¿Es viable cambiar el orden de las filas 2 a 4? Es decir, ¿qué la fila 4 pasase a ser la 2 y la 2 pasase a ser la 4?
Si esto fuese admisible, la fórmula no resultaría demasiado complicada:
=INDICE($A$2:$A$4;COINCIDIR(C5;DESREF($A$2:$A$4;;COINCIDIR($B$5;$B$1:$F$1;0));-1))
Si fuese necesario mantener los datos tal como están, pienso que sería posible conseguir lo que necesitas, pero la fórmula necesaria sería bastante más complicada.
Si esto fuese admisible, la fórmula no resultaría demasiado complicada:
=INDICE($A$2:$A$4;COINCIDIR(C5;DESREF($A$2:$A$4;;COINCIDIR($B$5;$B$1:$F$1;0));-1))
Si fuese necesario mantener los datos tal como están, pienso que sería posible conseguir lo que necesitas, pero la fórmula necesaria sería bastante más complicada.
Existe alguna manera de invertir automáticamente la columna. Por ejemplo copiar la columna B (en otra columna la AA por ejemplo u hoja ) de manera que el valor que estaba el ultimo en B4 aparezca en B2 (es que tengo como 22 filas y 12 columnas llenas de datos y pasarlos a mano, creo que merece la pena preguntar por una solución de este tipo)
Es posible "dar la vuelta" a los datos en otra hoja. Lo más sencillo es aplicar un nombre al rango original, por ejemplo Datos, para no tener que andar haciendo referencias directas a él. En el ejemplo que has puesto, el rango Datos debería incluir el rango A2:F4
En la celda A2 de una hoja en blanco iría la fórmula
=INDICE(Datos;FILAS(Datos)-FILA()+2;COLUMNA())
Que bastaría con copiar hacia abajo y hacia la derecha para que los datos aparezcan en orden vertical inverso con respecto a los originales.
De todas formas, creo que ya tengo una fórmula que funciona incluso si los datos están en orden ascendente. Como me temía, es bastante complicada:
=INDICE($A$2:$A$4;COINCIDIR(INDICE(K.ESIMO.MAYOR(DESREF($A$2:$A$4;;COINCIDIR($B$5;$B$1:$F$1;0));FILA(INDIRECTO("1:"&FILAS($A$2:$A$4))));COINCIDIR(C5;K.ESIMO.MAYOR(DESREF($A$2:$A$4;;COINCIDIR($B$5;$B$1:$F$1;0));FILA(INDIRECTO("1:"&FILAS($A$2:$A$4))));-1));DESREF($A$2:$A$4;;COINCIDIR($B$5;$B$1:$F$1;0));0))
Se trata de una fórmula matricial, así que hay que introducirla pulsando mayúsculas control entrada al mismo tiempo.
En la celda A2 de una hoja en blanco iría la fórmula
=INDICE(Datos;FILAS(Datos)-FILA()+2;COLUMNA())
Que bastaría con copiar hacia abajo y hacia la derecha para que los datos aparezcan en orden vertical inverso con respecto a los originales.
De todas formas, creo que ya tengo una fórmula que funciona incluso si los datos están en orden ascendente. Como me temía, es bastante complicada:
=INDICE($A$2:$A$4;COINCIDIR(INDICE(K.ESIMO.MAYOR(DESREF($A$2:$A$4;;COINCIDIR($B$5;$B$1:$F$1;0));FILA(INDIRECTO("1:"&FILAS($A$2:$A$4))));COINCIDIR(C5;K.ESIMO.MAYOR(DESREF($A$2:$A$4;;COINCIDIR($B$5;$B$1:$F$1;0));FILA(INDIRECTO("1:"&FILAS($A$2:$A$4))));-1));DESREF($A$2:$A$4;;COINCIDIR($B$5;$B$1:$F$1;0));0))
Se trata de una fórmula matricial, así que hay que introducirla pulsando mayúsculas control entrada al mismo tiempo.
