Duda sobre la función COINCIDIR

garbage · el 15 de diciembre

¡Hola foreros!

He estado haciendo un test de Excel y me ha salido la siguiente pregunta:

Cita

Si aplicamos la fórmula =COINCIDIR(150000;C9:C14;-1) a la celda C15 de la "Hoja Excel 2" del Anexo de Excel, ¿cuál será el valor de la celda C15?

En las celdas C9:C14 aparecen los siguientes números:

312.042

331.441

192.703

141.239

33.289

2,27

Las posibles respuestas eran a) 2 b) 3 c) 4 d) Devuelve un resultado erróneo.

Yo contesté la D), porque según la Ayuda de Excel para esta función, si en el tercer argumento aparece -1 , los valores que aparecen en el segundo argumento de la función tienen que estar ordenados en orden descendente (en este caso, de mayor a menor), cosa que no ocurre en este caso. De hecho, en el ejemplo que aparece en la Ayuda de Excel, aparece que al poner -1 y el rango de números que aparecen en él al no estar colocados en orden descendente, provoca un error del tipo #N/A.

Pues bien, cuál es mi sorpresa cuando veo el resultado y dan como correcta la respuesta B (3). Pruebo a hacerlo en el ordenador y, efectivamente, como resultado da 3, lo cual se contradice, en mi opinión, con el funcionamiento esperado de la función según aparece en la Ayuda de Excel.

¿Alguién me lo podría aclarar?

Gracias!!

Frank2021 · el 15 de diciembre

Hola

Los números "sí" están ordenados de mayor a menor, es decir, en descendente.

La función COINCIDIR busca un elemento determinado en un intervalo de celdas y después devuelve la posición relativa de dicho elemento en el rango.

Cuando el tercer argumento es -1 y los número están ordenados de forma descendente, encuentra el número más pequeño que es mayor o igual al valor buscado, en este caso 150000 y el número encontrado que cumple la condición es 192703, que es el número más pequeño de la lista que es mayor que 150000 y como está en la posición 3, que es su posición relativa en la lista, es lo que devuelve COINCIDIR.

image.png.59b8260c86384ed60903c753bc3b899e.png

Función COINCIDIR (microsoft.com)

Saludos

garbage · el 15 de diciembre

Gracias por responder, pero los números no están ordenados de forma descendente. Si así fuera, primero aparecería el 331.441 y luego el 312.042, no al revés, que es como aparece en la pregunta del test. De ahí mi duda....

Saludos.

Frank2021 · el 15 de diciembre

Hola,

tienes razón, los números no están totalmente ordenados. Lo siento.

Parece ser que la función COINCIDIR fue diseñada para alta velocidad, da por supuesto que el rango que le pasas está ordenado excepto cuando el tercer argumento es 0 (cero) que puede estar en cualquier orden.

Aunque a veces da un resultado no es garantía de que sea correcto, pues a veces con el mismo rango de valores en otro orden da un error.

Nos puede parecer un comportamiento inconsistente y podríamos esperar que si no está ordenado el rango diera un error, pero eso requiere tiempo de cálculo para decidir si el rango está ordenado o no, así que lo da por supuesto o esa es la condición que impone la función según la Ayuda para su correcto funcionamiento.

Si sí lo está, ascendente o descendente, en coherencia con el el tercer parámetro pasado, el resultado que da COINCIDIR es correcto, si no lo está puede dar un error o un valor, según el algoritmo interno de búsqueda binaria que utiliza le dé un error (interno) o resultado, así lo mostrará como #N/D o resultado engañoso (aunque a primera vista nos parezca correcto o sorprendente (porque no lo entendemos) porque no estamos viendo un error).

Entonces si esto es cierto, llegamos a la conclusión de que, quien puso el test, cayo en el mismo error dando por supuesto cierto comportamiento.

Saludos

garbage · el 15 de diciembre

Entonces, lo que aparece en la Ayuda de Excel sobre esta función no es del todo cierto, ya que si los datos no están ordenados en orden descendente (en el caso de utilizar el parámetro -1 como tercer argumento), la función no siempre devolverá #N/A, sino que lo hará de forma "aleatoria", dependiendo también de cómo estén situados el resto de valores del rango de datos, ¿no?

Gracias y un saludo.

Frank2021 · el 15 de diciembre

Hola.

Correcto.

La nueva función que sustituye a COINCIDIR (MATCH) es COINCIDIRX (XMATCH) a la que le han añadido un 4 parámetro para que decidas tú si quieres la búsqueda binaria o no y que advieten que si los datos no están ordenados los resultados devueltos no serán válidos.

XMATCH function (microsoft.com)

Saludos

garbage · el 15 de diciembre

De acuerdo.

Muchas gracias por tu ayuda.

Un saludo.

Gerson Pineda · el 20 de diciembre

Hola a ambos

Vaya que curioso el caso, lo que si pude detectar o descubrir es que mientras existan en el rango [ordenado o no], valores mayores o igual al buscado en la parte superior, COINCIDIR no dará un error

Saludos

njnsp · el 21 de diciembre

Por si alguno le interesa... (hablando de tercer parámetro "-1")

Excel evalúa el "ordenamiento" del rango comparando el primer y ultimo valor. Según esos valores, determina si el rango esta ordenado o no y a partir de ahí, hace cosas que están bien, o calcula mal.