[RESUELTO] Listar de mayor a menor, y vicev., búsq en matriz

Discute sobre la aplicación de hojas de cálculo
Responder
Avatar de Usuario
articulo47
Mensajes: 63
Registrado: Dom Jun 30, 2013 5:48 pm

[RESUELTO] Listar de mayor a menor, y vicev., búsq en matriz

Mensaje por articulo47 »

 Editado: Título original:
Listar de mayor a menor, y viceversa, búsqueda en matriz 
Hola a todos(as),

quizás me podáis aconsejar, estoy buscando una forma de poder crear un listado de mayor a menor y otro de menor a mayor para una misma búsqueda en una matriz. Explicado así, quizá no queda claro, lo mejor es que veais la 1ª hoja del documento adjunto, donde muestro como se vería sin realizar la búsqueda y cómo quedaría con una búsqueda realizada.

El caso es que he buscado en el foro y fuera del mismo, y no encuentro ninguna referencia válida.

He leído este tema:
https://forum.openoffice.org/es/forum/v ... =21&t=6028

Pensaba que era lo que necesitaba, lo he recreado (2ª hoja documento adjunto), pero no entiendo bien como funciona, supongo que faltará utilizar alguna fórmula mas.

Mi intención es utilizar una solución "sin macros", básicamente porque no las he utilizado nunca, y me llevaría mucho tiempo implementarlas.
 Editado: Si véis que lo que pretendo hacer, solo se puede lograr con macros, agracedería que me lo comunicárais 
Gracias de antemano.
Adjuntos
Busqueda_matriz_resultado_2columnas_mayor_menor_viceversa_mismos_datos.ods
(13.25 KiB) Descargado 167 veces
Última edición por articulo47 el Vie Abr 14, 2017 10:07 am, editado 7 veces en total.
LibreOffice 7.5.8.2
macOS Catalina v10.15.7
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Listar de mayor a menor, y viceversa, búsqueda en matriz

Mensaje por mriosv »

Bueno, parece posible, después de algunas vueltas.
Calculando la fila en la que se encuentra el dato, para lo que utilizamos el pequeño truco de añadir el número de fila al dato, antes de la clasificación, y eliminados el dato después de esta. Este truco habría que modificarlo si los números a clasificar tienen décimales o son muy largos.

En

Código: Seleccionar todo

E6: =COINCIDIR($D$6;$C$20:$F$20;0)
obtenemos la columna sobre la que ordenar,
después calculamos el número de fila de los datos ordenados, con una matriz [Mays+Ctrl+Intro],

Código: Seleccionar todo

A9: {=REDONDEAR((K.ESIMO.MAYOR(INDICE($C21:$G25;0;$E$6)+(FILA(C21:C25))/10^6;FILA(C1:C5))-ENTERO(ACTUAL()))*10^6)}
Sabiendo la fila del dato utilizamos el número de columna en E6 para obtener el que corresponde al seleccionado.

Código: Seleccionar todo

D9: =INDICE(INDICE($C:$F;0;$E$6);$A9)
Adjuntos
Busqueda_matriz_resultado_2columnas_mayor_menor_viceversa_mismos_datos-2.ods
(13.15 KiB) Descargado 129 veces
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Avatar de Usuario
articulo47
Mensajes: 63
Registrado: Dom Jun 30, 2013 5:48 pm

Re: Listar de mayor a menor, y viceversa, búsqueda en matriz

Mensaje por articulo47 »

Gracias por tu aportación mriosv, es lo que quiero hacer, pero se cumple la necesidad de modificación que habías contemplado:
Este truco habría que modificarlo si los números a clasificar tienen décimales o son muy largos.
son miles de datos con números largos y con decimales.

He probado a meterle un número largo y con décimal, y el valor da 0, el nombre da 0, y el nº de fila indica un número muy largo que no se corresponde con el nº de fila.

Con un nº largo, nº seguido de 9 ceros, funciona perfectamente hasta el "5000000000", con este "6000000000" ya falla.

Yo necesito que un valor como este 20.000,576 funcione.

¿Qué hay que modificar en las fórmulas para funcionen?

Gracias de antemano, disculpa las molestias.
LibreOffice 7.5.8.2
macOS Catalina v10.15.7
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Listar de mayor a menor, y viceversa, búsqueda en matriz

Mensaje por mriosv »

Pruebacam cambiado el 10^6 por10^9.
La cuestión es que el número de fila dividido por el 10^x sea menor que el decimal más pequeño.
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Avatar de Usuario
articulo47
Mensajes: 63
Registrado: Dom Jun 30, 2013 5:48 pm

Re: Listar de mayor a menor, y viceversa, búsqueda en matriz

Mensaje por articulo47 »

Disculpa mriosv, pero cuando intento hacer la modificación que me has indicado, no me deja, me muestra una ventana emergente que dice:
No puede modificar solo una parte de una matriz
Pensaba que tenía que editarla como las fórmulas, entrando en la celda correspondiente, pero parece que no es así.

¿donde se crean/editan las matrices en LO Calc?
 Editado: He probado las indicaciones de

https://help.libreoffice.org/3.5/Calc/A ... _de_matriz

utilizando Ctrl + / y no consigo poder editarlas. (/ teclado numérico)

¿Puedes decirme como hacerlo? 
LibreOffice 7.5.8.2
macOS Catalina v10.15.7
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: Listar de mayor a menor, y viceversa, búsqueda en matriz

Mensaje por mriosv »

Por una parte para editar una matriz se utiliza [Ctrl /] o bien seleccionas todo su rango y después [F2]

Adjunto de nuevo el fichero modificado para poder utilizar números con al menos tres decimales, si son mas modifica el 10^3 por 10^N siendo N el número máximo de décimales, He reducido el exponente para las filas a 10^5.
Debemos tener en cuenta que la longitud máxima de los números para calc es de 15 digitos incluidos los decimales.

Código: Seleccionar todo

A9: {=REDONDEAR((K.ESIMO.MAYOR(INDICE($C21:$G25;0;$E$6)*10^3+(FILA(C21:C25))/10^5;FILA(C1:C5))-ENTERO(ACTUAL()))*10^5)}
dentro de la formula

Código: Seleccionar todo

INDICE($C21:$G25;0;$E$6)*10^3+(FILA(C21:C25))/10^5
es el calculo que podría superar el máximo de digitos permitido.

He añadido un pequeña formula para calcular el número máximo de decimales para no tener que andar modificando la formula.
Adjuntos
Busqueda_matriz_resultado_2columnas_mayor_menor_viceversa_mismos_datos-4.ods
(14.15 KiB) Descargado 116 veces
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Avatar de Usuario
articulo47
Mensajes: 63
Registrado: Dom Jun 30, 2013 5:48 pm

Re: Listar de mayor a menor, y viceversa, búsqueda en matriz

Mensaje por articulo47 »

Como estoy con LO en MacOS, resulta que no funciona Ctrl + /, y tengo que utilizar Cmd + /.

Seleccionando la matriz, si funciona F2 en MacOS.

Gracias mriosv por ayudarme.
LibreOffice 7.5.8.2
macOS Catalina v10.15.7
Avatar de Usuario
articulo47
Mensajes: 63
Registrado: Dom Jun 30, 2013 5:48 pm

Re: [RESUELTO] Listar de mayor a menor, y vicev., búsq en ma

Mensaje por articulo47 »

Subo el .ods a MEGA para poder descargarlo, ya que es demasiado grande para adjuntarlo al foro.
https://mega.nz/#!rY8lBCoY!6M9OZYKB4tfm ... -1FlgOXKPc
mriosv reabro este tema por 2 motivos,

Primer motivo:
he visto hoy que has sustituido la versión 3 del .ods en el que dabas solución al problema planteado con la versión 4, en la que además, utilizabas una fórmula para el tema de los decimales. He probado la versión 4 y a mi no me funciona cuando selecciono algo distinto a los lápices. Quizá quieras solucionarlo en la versión 4 y resubirlo para que quienes entren a este tema no se encuentren una versión que no funciona. Lo he probado en MacOS con LO (versiones en mi firma).

Segundo motivo:
utilizando el método de la versión 3 que compartiste, he implementado este tipo de función con una tabla con 698 filas y 35 columnas (24.430 datos numéricos), y al principio no me funcionaba bien, porque el nombre del encabezado de algunas columnas tenía paréntesis, los cuales he quitado, y parece que funciona bien, pero me genera celdas,tanto en la parte de mayor a menor como en la contraria, en las que en el nombre aparece un 0 y en el valor también, indicándome en el nº de fila (donde está la matriz) números de fila enormes, que están fuera del rango de la tabla. También aparencen valores que no están ordenados de mayor a menor o viceversa.

(ver imagen adjunta) (ver actualizaciones y enlace a MEGA)
Imagen adjunta
Imagen adjunta
He revisado las fórmulas, las matrices y las referencias, conforme al sistema que me enseñaste y aparentemente están bien.

¿Te ha sucedido esto alguna vez? ¿Sabes donde podría estar el problema?

Gracias de antemano, disculpa las molestias.

Actualización
Acabo de probar la versión 2 que subiste, la que no permitía decimales, y al introducir decimales en la tabla da el mismo error que me está sucediendo a mi, por lo que es posible que el problema esté en los decimales que permite la matriz, la cual he cogido de la versión 3 que subiste, y funcionaba bien en tu ods. Voy a revisar de nuevo todo.
La matriz de mayor a menor:

Código: Seleccionar todo

=REDONDEAR((K.ESIMO.MAYOR(INDICE($BD.R3:$BD.AZ700;0;$E$702)*10^3+(FILA(BD.R3:R700))/10^5;FILA(C1:C698))-ENTERO(ACTUAL()))*10^5)
La matriz de menor a mayor:

Código: Seleccionar todo

=REDONDEAR((K.ESIMO.MENOR(INDICE($BD.R3:$BD.AZ700;0;$E$702)*10^3+(FILA(BD.BA3:BA700))/10^6;FILA(G1:G698))-ENTERO(ACTUAL()))*10^6)
Veo que en la 1ª matriz tengo 10^3 y 10^5; y en la 2ª matriz, 10^3 y 10^6, que es lo mismo que tenías en la versión 3, la cual funciona, pero en mi caso me da el error, que creo que se debe a los decimales.

He probado a igualar las dos matrices a 10^3 con 10^5, y da el mismo error, también he probado con 10^3 y 10^6 y lo mismo.
2ª actualización
He probado el documento en Apache OpenOffice y da el mismo error. Sin embargo, LO y AOO me ofrecen los valores con formato numérico distinto, mientras que AOO me muestra los valores igual que están en la tabla, LO les cambia el formato.
Veamos un ejemplo:

Calabaza, cruda

la tabla lo muestra así:

Código: Seleccionar todo

36,500 Kcal
AOO respeta el formato numérico, incluyendo al final Kcal.

Pero LO lo indica así:

Código: Seleccionar todo

36,4999998092651
3ª actualización, enlace a MEGA para descargar .ods
Subo el .ods a MEGA para poder descargarlo, ya que es demasiado grande para adjuntarlo al foro.
https://mega.nz/#!rY8lBCoY!6M9OZYKB4tfm ... -1FlgOXKPc
LibreOffice 7.5.8.2
macOS Catalina v10.15.7
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: *No resuelto_Listar de mayor a menor, y vic., búsq en ma

Mensaje por mriosv »

Había un problema con el calculo del número de decimales, pero he encontrado otra formula, además mas sencilla, que nos evita el uso del número de decimales. Utilizando un logaritmo de la base máxima que permite la función, y que coincide con el máximo de dígitos significativos que podemos utilizar para clasificar, en principio solo números positivos, aunque creo podría extenderse a los negativos, complicándola un poco.

En cuanto al formato parece funcionar bien con OpenOffice, pero no lo hereda en LibreOffice tomando el valor del dato, creo recordar que en algún momento se cambio por problemas de herencia de formato en algunas formulas.

Estamos limitados a 9 dígitos significativos para clasificar hasta 99999 filas con datos, si las filas fueran menores de 10000 podríamos aumentar un dígito significativo y si mayores de 100000 reducirlo a 8.

Adjunto tu archivo con la modificación y el de ejemplo.
Busqueda_test.ods
(220.5 KiB) Descargado 106 veces
Busqueda_test.ods
(220.5 KiB) Descargado 106 veces
Para estar seguros de un resultado correcto, sería bueno hacer un re-calculo completo [Ctrl+Mays+F9] después de cada selección.
Adjuntos
Busqueda_matriz_resultado_orden-ascendente-descendente.ods
(13.94 KiB) Descargado 115 veces
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Avatar de Usuario
articulo47
Mensajes: 63
Registrado: Dom Jun 30, 2013 5:48 pm

Re: *No resuelto_Listar de mayor a menor, y vic., búsq en ma

Mensaje por articulo47 »

Hola mriosv, muchas gracias por tu tiempo y soluciones.

La nueva fórmula con LOG que has implementado funciona sin generar los errores, pero en los archivos que has subido "Busqueda_test.ods" todos los resultados me los da en %, le he podido cambiar el formato de C706:C1403 al formato "Estandar", pero a G706:1403 no me dejaba, no se porque. Lo he solucionado, quitando las matrices en A706:1403 y en E706:1403, he formateado los intervalos de celdas de C y G citados, y después he vuelto a introducir las matrices, y ya funciona perfectamente.

Por otro lado, en la celda U1 de la hoja BD he visto que has puesto esta fórmula:

Código: Seleccionar todo

=LOG(U15;10^10)
¿Cual es su función?, la he quitado y el sistema de búsqueda y orden creciente/decreciente de valores sigue funcionando.
LibreOffice 7.5.8.2
macOS Catalina v10.15.7
mriosv
Mensajes: 2334
Registrado: Sab Dic 27, 2008 1:12 am
Ubicación: Galiza (España)

Re: [RESUELTO] Listar de mayor a menor, y vicev., búsq en ma

Mensaje por mriosv »

Puedes borrarla era una prueba.
Puedes usar "Subir adjunto" en Respuesta rápida - Editor Completo - Pestaña Subir adjunto
Apache OpenOffice 4, LibreOffice (Win10x64)
Responder