☰

Uso de la coincidencia de índice condicionalmente para indicar si

tag-icon • tag-icon microsoft-excel worksheet-function
Uso de la coincidencia de índice condicionalmente para indicar si

Me siento bastante cómodo con INDEX MATCH, pero encontré un problema en el que me gustaría usarlo y no estoy muy seguro de que funcione. Tengo dos hojas: la primera hoja más pequeña tiene un pequeño subconjunto de nombres que están en la otra hoja y tiene un conjunto de fechas asociadas con cada nombre. La otra hoja más grande tiene un conjunto diferente de fechas asociadas con cada nombre. Lo que me gustaría poder hacer es INDEXAR las fechas en la hoja más grande y COINCIDIR los nombres solo SI esta fecha es mayor que la fecha en la hoja más pequeña y menor que el final del mes asociado con esa fecha. En lugar de devolver el valor MATCH, me gustaría devolver, digamos, un 1 si se cumplen los criterios. ¿Es esto algo que se puede hacer con bastante facilidad con INDEX MATCH o hay otro conjunto de funciones que debería buscar? ¿Cómo haría usted para esto?

Respuesta1

Creo que su pregunta gira en torno a escribir una fórmula ÍNDICE/COINCIDENCIA en torno a estos criterios:

  • INDICE las fechas en la hoja más grande

  • COINCIDE los nombres solo SI esta fecha es mayor que la fecha en la hoja más pequeña

    • menos que el final del mes asociado con esa fecha

  • Luego devuelva 1 si se cumplen los criterios.

Podemos hacer esto anidando INDEX/MATCH dentro de una condición IF:

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

La función COINCIDIR devolverá valores cada vez que se encuentre una coincidencia y, por definición, ese valor será mayor que 0. Por lo tanto, queremos devolver 1 por cada aparición de esto.

¡Espero que esto ayude!

Respuesta2

Usaría el complemento Power Query para esto. Una consulta puede comenzar desde una tabla de Excel y luego generar la transformación de datos en pasos. Podrás revisar el resultado en cada paso.

Para sus necesidades, usaría el comando Fusionar para unir las dos hojas por nombre y expandir la columna de fecha de su hoja más grande.

http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx

Esto dará como resultado todas las coincidencias posibles. Entonces usaría el comando Filtrar para llegar solo a las fechas del rango. Es posible que necesites editar la función Filtro para esto. Tenga en cuenta que hay una función de Power Query para derivar el fin de mes:

http://office.microsoft.com/en-au/excel-help/date-endofmonth-HA104110485.aspx?CTT=5&origin=HA104122363

Finalmente entregaría la Consulta a una nueva Tabla de Excel.

información relacionada