Excel - Función SI anidada

Pos: 251

Val: 11

Función SI anidada

Publicado por Bernardo (6 intervenciones) el 30/11/2018 18:45:13

Hola qué tal buenas tardes, espero me puedan ayudar con mi problema.

Tengo un Archivo con 2 hojas, en una contiene la matriz de datos y en la otra la información que quiero me llegue de ésta. En la pestaña FLEET quiero que me arroje el Price Per Hour dependiendo de las horas en Last Running Hours, es decir; si yo tengo un equipo que llega a las 12,000 horas, de acuerdo al Modelo, Rango de Horas (Pestaña Price) me envíe el precio por hora de la pestaña Price, no se si me explique. Tengo una formula en Price Per Hour pero por la cantidad de Rangos no puedo anidarla más, además de que se me hace demasiado para lo que quiero lograr, ¿alguna formula mas sencilla para esto? por otro lado, no siempre me respeta la formula que tengo, por ejemplo: si cambio la hora del modelo DMM3 si me arroja correctamente el Price Per Hour pero por ejemplo con el modelo MT2010 no lo hace, siendo que la indicación es la misma.

Gracias de antemano.

File.zip(32,0 KB)

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 1

Val: 6.373

Función SI anidada

Publicado por wordexperto.com (2803 intervenciones) el 30/11/2018 19:36:58

Hola Bernardo:
No entiendo tus datos. Pon algún ejemplo. ¿Dónde están esos tramos que usas en el si?
En todo caso, no se deben, ni en tu caso, pueden, anidar tantos si. La alternativa es usar buscarv verdadero, cuarto argumento a 1 o verdadero, para rangos o indice+coincidir para valores exactos.
https://wordexperto.com/

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 251

Val: 11

Función SI anidada

Publicado por Bernardo (6 intervenciones) el 30/11/2018 20:23:31

Gracias por su pronta respuesta.

Básicamente, el Dato que quiero obtener es el Price Per Hour de la pestaña PRICE, dependiendo del rango que tenga cada modelo en Model Name y que el dato de Last Running Hour se encuentre en dicho rango.

Por ejemplo para el Modelo DMM3

Si el registro de Last Running Hours en la pestaña FLEET es 8,486, quiero que me arroje el Price Per Hour (Pestaña PRICE ) siempre y cuando coincida el dato de Model Name y al rango que tiene éste (8001 - 12000), es decir:

Si para el Model Name (DMM3) tengo un Last Running Hours de 8,486 quiero que me encuentre el Price Per Hour (Pestaña PRICE) siempre y cuando se encuentre en el rango preestablecido (8001-12000) que en este caso equivaldría al Year 3 siendo el Price Per Hour de 21.26

Aquí el problema es que estos rangos son ya fijos, cada Price Per Hour y Hour Range estan fijos de acuerdo a los modelos

Anexo nuevamente un archivo con los datos en concreto.

Gracias de antemano.

File.zip(22,9 KB)

Valora esta respuesta

Comentar

Pos: 1

Val: 6.373

Función SI anidada

Publicado por wordexperto.com (2803 intervenciones) el 01/12/2018 10:07:28

Bien Bernardo:
No funciona bien, porque, entre otras cosas, tienes un registro repetido, al menos (ST1520). Pero la idea es esta. Usar uno solo de los intervalos de los rangos, yo he usado el inferior. Definir nombres para los intervalos de cada modelo y usar indice+coincidir para traer el valor.
https://wordexperto.com/

Fleet2018.rar(30,6 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Función SI anidada

Publicado por Antoni Masana (2477 intervenciones) el 03/12/2018 07:55:59

He destripado la fórmula y tiene incoherencias de valores.

Si el valor de D4 es 900 que tipo de precio a de poner, el de la Columna 2, 3 o 4.

=  SI(Y(D4>=    0 ; D4<=  400) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 2 ; 0)

 ; SI(Y(D4>=    0 ; D4<=  600) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 2 ; 0)

 ; SI(Y(D4>=    0 ; D4<= 1000) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 2 ; 0)  <-- 900 ?¿

 ; SI(Y(D4>=    0 ; D4<= 1200) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 2 ; 0)

 ; SI(Y(D4>=    0 ; D4<= 1950) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 2 ; 0)

 ; SI(Y(D4>=    0 ; D4<= 2000) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 2 ; 0)

 ; SI(Y(D4>=    0 ; D4<= 4000) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 2 ; 0)

 ; SI(Y(D4>=  401 ; D4<=  800) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 3 ; 0)

 ; SI(Y(D4>=  601 ; D4<= 1200) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 3 ; 0)  <-- 900 ¿?

 ; SI(Y(D4>= 1001 ; D4<= 2000) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 3 ; 0)

 ; SI(Y(D4>= 1201 ; D4<= 2400) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 3 ; 0)

 ; SI(Y(D4>= 1951 ; D4<= 3900) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 3 ; 0)

 ; SI(Y(D4>= 2001 ; D4<= 4000) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 3 ; 0)

 ; SI(Y(D4>= 3001 ; D4<= 6000) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 3 ; 0)

 ; SI(Y(D4>= 4001 ; D4<= 8000) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 3 ; 0)

 ; SI(Y(D4>=  801 ; D4<= 1200) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 4 ; 0)  <-- 900 ¿?

 ; SI(Y(D4>= 1201 ; D4<= 1800) ; BUSCARV(B4 ; PRICE!$A$5:$K$61 ; 4 ; 0)

...

Creo que te has complicado mucho la vida con la fórmula. Te propongo una solución mucho más simple.

Una tabla auxiliar con los rangos y dos fórmulas BUSCARV para buscar el valor.

En la hoja FLEET Hay a tabla auxiliar (J1:L9) que la puedes poner en otra hoja y oculta si quieres

En la columna E esta la formula para buscar la columna de precio según el rango de valores.
En la columna F tu complicada fórmula simplificada y usando el valor de la columna E para saber el precio.
En la columna G la fórmula tal cual la deberías poner en tu columna C

Adjunto libro.

Saludos.
\\//_

Fleet2018_V2.zip(35,3 KB)

Valora esta respuesta

Comentar

Pos: 251

Val: 11

Función SI anidada

Publicado por Bernardo (6 intervenciones) el 03/12/2018 21:39:20

Gracias a ambos por sus respuestas.

Antoni,

No me queda muy clara el proceso que utilizaste, ya que dentro de Hoja PRICE los rangos son distintos dependiendo el modelo, tengo una un listado de rangos predeterminados (Imagen Anexa) y dependiendo sea el Model Name es el tipo de rangos que se manejan y de esto depende el Price Per Hour que me debería de mostrar en la Hoja FLEET, he anexado un par de rangos mas en la tabla auxiliar pero no entiendo la parte en cómo encuentra el Price Per Hour dentro de la Hoja PRICE y otro punto es que no entiendo por que no me arroja el Price Per Hour si en realidad si se tiene dicho rango dentro de la Tabla auxiliar.

Gracias por su ayuda.

Saludos,

Fleet2018_V2.zip(67,3 KB)

Valora esta respuesta

Comentar

Pos: 251

Val: 11

Función SI anidada

Publicado por Bernardo (6 intervenciones) el 03/12/2018 22:04:27

No sé si coloando una tabla auxiliar como la que colocaste pero por modelo y sus respectivos rangos u otra manera.

Gracias nuevamente.

saluds,

Valora esta respuesta

Comentar

Pos: 251

Val: 11

Función SI anidada

Publicado por Bernardo (6 intervenciones) el 03/12/2018 23:13:15

Hola de nuevo Antoni,

Realicé unos cambios sobre el archivo, no sé si colocando de esta manera la información se pueda realizar el proceso.

Coloqué varias tablas auxiliares con los distintos rangos de horas y los modelos de equipos que entrarían a esa lista de rangos, si observas las tablas marcadas en amarillo coloqué el Price por Modelo y por Rango, aqui lo complicado es que, los modelos que se encuentran dentro de una tabla auxiliar de cierto rango (por ejem en la Tabla del rango 0 - 1200) contiene varios modelos pero cada uno de ellos deben de tener sus Price Per Hour, no sé si me explique.

Saludos,

Fleet2018_V2.zip(61,4 KB)

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Función SI anidada

Publicado por Antoni Masana (2477 intervenciones) el 04/12/2018 09:33:42

A mí se me paso el detalle de que los intervalos van según el código y la respuesta de Pepe (WordExpert - 01/12/2018 - 10:07) te da la solución al problema.

Lo que él ha puesto en el rango B65:K121 debe ir, para ser correcto y coherente con la estructura, L5:V61
En este último rango tienes los datos como texto y no sirve para Buscar rangos de valores.
En definitiva la tabla auxiliar ya la tienes, solo falta arreglarla y usarla.

Ahora el problema es la fórmula, te cuento que tiene que hacer la fórmula:
- Tiene que buscar el testo de B3 de la hoja FLEET en la tabla de la hoja PRICE en la columna A, y saber en qué fila esta.
- En dicha fila mirar en el grupo de columnas L:V en que columna está el valor de la celda de la celda B3 de la hoja FLEET
- Cuando sepa la columna extra poner para saber la columna del precio, es decir restar 10.

Ahora solo hace falta saber que combinación de fórmulas hay que utilizar para que:
- Busque en la columna A.
- Busquen la fila resultante de la búsqueda anterior.
- Devuelva un valor del resultado de las búsquedas anteriores.

No tengo idea de como hacerlo, algunas de las formulas a utilizar pueden se INDICE, COINCIDIR, BUSCAR, etc, voy a investigarlo.

La fórmula de Pepe no me acaba de funcionar bien y no se porque.

=INDICE(PRICE!$A$5:$K$61;COINCIDIR(FLEET!B3;PRICE!$A$5:$A$61;0);COINCIDIR(FLEET!D3;INDIRECTO(B3);1))

Saludos.
\\//_

Valora esta respuesta

Comentar

Pos: 2

Val: 4.908

Función SI anidada

Publicado por Antoni Masana (2477 intervenciones) el 05/12/2018 10:54:41

He estado analizando el problema y he llegado a un punto muerto.

Tengo esta funcion pero me falta un detalle que no se como resolver.

=INDIRECTO("Price!F" & COINCIDIR(FLEET!B3;PRICE!$A$5:$A$61;0)+4 & "C" & COINCIDIR(D3;PRICE!L7:U7;1)+1;FALSO)

La primera función COINCIDIR me devuelve la fila donde esta el código en la hoja PRICE.

La segunda función COINCIDIR me devuelve la columna del precio que he tomar en función de los rangos. Y Aqui esta el problema.

La función INDIRECTO me devuelve el valor de la celda correspondiente.

Y cual es el problema:

COINCIDIR(D3;PRICE!L7:U7;1)

El 7 que hay en la fórmula debería cambiarlo por el resultado de la primera función COINCIDIR:

COINCIDIR(FLEET!B3;PRICE!$A$5:$A$61;0)+4

Quizas se podria hacer con una función matricial pero no se como se hace ni si se puede.

Lo que si se, es hacerlo con una FUNCIÓN personal, es decir código Visual Basic.

Option Explicit

Public Function Price_Per_Hour(c_Model as String, n_Horas as Long)

    Dim Fil As Integer, Col As Integer, Col_Year As Integer

    Col_Year = 0

    For Fil = 5 To 61

        If Sheets("Price").Cells(Fil, 1) = c_Model Then

           For Col = 21 To 12 Step -1

               If n_Horas >= Sheets("Price").Cells(Fil, Col) Then

                  Col_Year = Col - 10: Exit For

               End If

           Next

           If Col_Year > 0 Then Exit For

        End If

    Next

    If Col_Year > 0 Then

       Price_Per_Hour = Sheets("Price").Cells(Fil, Col_Year)

    Else

       Price_Per_Hour = "No aplicable"

    End If

End Function

Adjunto Fleet2018_Formula.zip

Al menos esto es más sencillo y funciona. Adjunto ejemplo con función. Si lo prefieres con los rangos como los tenias antes hay que hacer unos cambios

'Option Explicit

Option Explicit

Public Function Price_Per_Hour(c_Model As String, n_Horas As Long)

    Dim Fil As Integer, Col As Integer, Col_Year As Integer

    Dim Rango_Menor As Long, Rango As String, _

        Rango_Mayor As Long

    Col_Year = 0

    For Fil = 5 To 61

        If Sheets("Price").Cells(Fil, 1) = c_Model Then

           For Col = 21 To 12 Step -1

               Rango = Sheets("Price").Cells(Fil, Col)

               Rango_Menor = Val(Left$(Rango, InStr(Rango, " ")))

               Rango_Mayor = Val(Mid$(Rango, InStr(Rango, " - ") + 3))

               If n_Horas >= Rango_Menor And n_Horas <= Rango_Mayor Then

                  Col_Year = Col - 10: Exit For

               End If

           Next

           If Col_Year > 0 Then Exit For

        End If

    Next

    If Col_Year > 0 Then

       Price_Per_Hour = Sheets("Price").Cells(Fil, Col_Year)

    Else

       Price_Per_Hour = "No aplicable"

    End If

End Function

Adjunto Fleet2018_Formula_2.zip

Con esta nueva función hay un problema y es cuando los valores son muy altos ¿que precio se aplica?

|  8220       |  DM45HP  |  No aplicable  |  47,278  |

|  8219       |  DM45HP  |  No aplicable  |  41,426  |

|  8342       |  DM45HP  |  No aplicable  |  44,506  |

|  8345       |  DM45HP  |  No aplicable  |  49,593  |

|  8346       |  DM45HP  |  No aplicable  |  47,652  |

|  USS008376  |  DM45LP  |  No aplicable  |  37,970  |

Según entiendo de la tabla de precios:

El rango de la columna L (YEAR 12) le corresponde el precio de la columna B (YEAR 1), etc

Rango          Precio

-------        -------

YEAR 12    ->  YEAR 1

YEAR 23    ->  YEAR 2

YEAR 34    ->  YEAR 3

YEAR 45    ->  YEAR 4

YEAR 56    ->  YEAR 5

YEAR 67    ->  YEAR 6

YEAR 78    ->  YEAR 7

YEAR 89    ->  YEAR 8

YEAR 910   ->  YEAR 9

YEAR 1011  ->  YEAR 10

Pero ¿qué pasa cuando el valor de la columna D de la hoja FLEET supera el valor mayor de la columna U de la hoja PRCE (YEAR 1011)?

Saludos.
\\//

Fleet2018_Formula.zip(30,6 KB)
Fleet2018_Formula_2.zip(36,9 KB)

Valora esta respuesta

Comentar

Pos: 251

Val: 11

Función SI anidada

Publicado por Bernardo (6 intervenciones) el 07/12/2018 00:05:31

Muchas gracias Antoni,

Funciona bien así como el ejemplo Fleet2018_Formula_2

Es correcto, El rango de la columna L (YEAR 12) le corresponde el precio de la columna B (YEAR 1), etc

Sobre el qué pasa si sobrepasa el rango final, está bien que mande el mensaje de No Aplicable ya que se excedió la cantidad establecida.

Voy a integrarlo al archivo general y espero todo salga en orden.

nuevamente muchas gracias

Valora esta respuesta

Comentar

Buscar valores de listado de hojas en una tabla

Problemas al cargar página web en Chrome con macro