varias - ¿Cómo ordeno una tabla en Excel si tiene referencias de celda?

ordenar datos en excel automaticamente (21)

Coloque un signo de dólar delante de la fila y / o columna de la celda que desea que permanezca constante.

¡Me lo solucionó!

Tengo una tabla de datos en excel en la hoja 1 que hace referencia a varias celdas diferentes en muchas otras hojas. Cuando intento ordenar o filtrar la hoja, las referencias cambian cuando la celda se mueve. Sin embargo, no quiero ingresar manualmente a cada celda e insertar $ signos en todas partes, porque algunas de las referencias son continuas y es posible que desee completarlas automáticamente más tarde.

Por ejemplo, una de mis columnas de datos utiliza la hoja 2, columna B 23:28, hoja 2, columna C 1:15 y algunas otras. La sección de la columna que utiliza la columna B de la hoja 2 está en el mismo lugar antes de la clasificación, al igual que cada una de las otras secciones. Si inserto $ signos, ya no podría insertar filas vacías y utilizar el autocompletar para nuevos datos.

En resumen, me gustaría una forma de mantener las referencias de mi celda estáticas sin usar $ signos, pero solo para fines de clasificación / filtrado. Mi solución actual consiste en copiar los valores de la tabla, pegarlos en una hoja vacía y luego ordenarlos. Espero que haya una mejor manera de hacer esto.

El método más fácil es usar la función OFFSET. Entonces, para el ejemplo original, la fórmula sería: = desplazamiento (c2,0, -1) * 1.33 = "usando la celda actual (c2) como punto de referencia, obtenga el contenido de la celda en la misma fila, pero una columna a la izquierda (b2) y multiplíquelo por 1.33 "

Funciona un placer

Esperaba encontrar un porqué aquí, pero creo que la respuesta es más simple de lo que estamos haciendo.

Las celdas deberían ordenar independientemente de la página a la que hacen referencia en su libro de trabajo. Lo que estaba causando el problema para nosotros era cualquier referencia de nombre de página en la página actual.

EX: Estoy en el libro de trabajo Hoy, y estoy haciendo referencia a datos para un representante de ventas en una página diferente.

Si mi criterio (el nombre de los representantes) está en ¡Hoy! C1 (o $ C1, $ c $ 1) y trato de ordenar, la hoja no reconocerá la acción. Pero si elimina el nombre que hace referencia a la página en la que se encuentra (realmente referencia redundante), este problema debería detenerse.

¡Así que los SUMADOS (''Ventas''! C1, ''Ventas''! A1, ¡Hoy! C1) ahora serán SUMA (''Ventas''! C1, ''Ventas''! A1, C1)

Si alguien puede iluminarse como por qué funciona de esa manera, sería increíble.

He tenido problemas con esto y he encontrado LA MEJOR y más fácil solución ...

Copia los datos que deseas convertir en una tabla y luego ordena.
Pegar está en otro lugar en su hoja de cálculo
Los valores cambiarán debido a sus referencias de celda, pero no queremos eso, haga clic en la ventana emergente para hacer un ''pegado especial'' y elija pegar ''valores'' solamente.
Convierta sus nuevos datos en una tabla y clasifique su vida lejos !!!

Espero que encuentres que funciona. Saludos.

Incluso con referencias absolutas, sort no maneja las referencias correctamente. Las referencias relativas se hacen para señalar el mismo desplazamiento relativo desde la nueva ubicación de fila (lo cual es obviamente incorrecto porque otras filas no están en la misma posición relativa) y las referencias absolutas no se cambian (porque SORT omite el paso de traducir las referencias absolutas después de cada reordenamiento de una fila). La única forma de hacerlo es mover manualmente las filas (habiendo convertido las referencias a absolutas) una por una. Excel luego hace la traducción necesaria de referencias. El SORT de Excel es deficiente ya que no lo hace.

La mejor manera es mantener sus datos de referencia en un lado de la hoja y todas las fórmulas en el otro lado de la hoja. luego, deje una columna en blanco entre ellos (ocúltelo si lo desea) y luego estará ordenando solo los datos de referencia manteniendo las referencias de Fórmula apuntando siempre en el mismo lugar. La desventaja es que Excel recalculará cada vez que clasifique.

La respuesta para mí fue separar los datos en dos cuadrículas con una columna en blanco entre ellos. La cuadrícula de la izquierda es la información que desea ordenar, la cuadrícula de la derecha contiene la fórmula que desea calcular. Cuando ordena, la fórmula simplemente trabaja sobre los datos en las filas en la cuadrícula de la izquierda y no hace que las referencias de filas se mezclen por orden.

Necesitaba ordenar las celdas con referencias, y realmente necesitaba evitar pegar los valores con los que trabajar. La "tabla pivote" hizo el truco.

Prepare sus tablas con referencias.
Seleccione la tabla (con referencias) e inserte tabla pivote
En la tabla dinámica, seleccione los filtros necesarios para que la tabla Pivot se vea como su Tabla original (si es necesario).
Ordene / filtre datos más según sea necesario.

Solo asegúrese de hacer clic con el botón derecho sobre la tabla pivote y presionar "actualizar" cada vez que cambie algunos datos genéricos (utilizados en sus tablas).

Espero que ayude Andrei

No estoy seguro si esto satisfará todo, pero encontré una solución simple que me solucionó el problema. Mueva todas las celdas / fórmulas a las que hace referencia a una hoja diferente para que no esté haciendo referencia a ninguna celda en la hoja que tiene la tabla que se va a ordenar. Si haces esto, puedes ordenarlo!

Otra solución es: (1) copiar toda la tabla - pegar como un enlace en la misma hoja de cálculo, de ahora en adelante trabajar solo en la ''tabla vinculada'' (2) copiar la columna con valores para ordenar y pegar valores solo el siguiente a la tabla que desea ordenar (3) seleccione la tabla y reemplace all = con, por ejemplo, #, esto cambiará la referencia en un texto estático (4) ordene la tabla por los valores pegados (5) reemplace todo # con =, la las referencias están de vuelta ¡Listo! Va bastante rápido cuando se usan atajos de Excel

Para mí esto funcionó como a continuación -

Tenía referencias de nombre de hoja en la fórmula para la misma hoja. Cuando eliminé el nombre de la hoja actual de la fórmula y ordené que funcionó correctamente.

Prueba este método:

Inserte una columna a la izquierda de las celdas para ordenar.
Suponer que los cols B: G se deben ordenar según B
Copiar col B
Pegar valores en col A
Resaltar cols B: G
Reemplazar = signos con # signos
Sort cols A: G basado en A
Resaltar cols B: G
reemplazar # signos con = signos
Eliminar col A.
Voila!

Solución fácil: copie desde Excel y péguelo en hojas de google. Copia todas sus celdas de referencia como celdas absolutas para que pueda comenzar desde cero. Luego, clasifique y descargue como una cuadrícula de Excel y vuelva a formatear según sus necesidades.

También estamos luchando con el mismo problema.

Como solución, utilizamos una macro para encubrir la tabla a la lista, ordenamos la lista y luego volvemos la lista a la tabla.

aquí encuentre una macro de muestra

Sub Sort_Table() ''change table name to "table1" ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$c$4"), , xlYes).Name = _ "Table1" ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2" Dim oSh As Worksheet Set oSh = ActiveSheet ''remove table or list style oSh.ListObjects("Table1").Unlist ''Sort List Range("B2").Select ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A4"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Sheet1").Sort .SetRange Range("A1:C4") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With ''Change list back to table again ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$c$4"), , xlYes).Name = _ "Table1" ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2" End Sub

Una forma muy simple es mantener las columnas problemáticas fuera de los datos filtrados, pero agregue una columna en la sección filtrada con referencias de celda a ellas (simplemente "= ''celda''").

De esta forma, las fórmulas del problema permanecerán intactas, pero los datos filtrados harán referencia a ellas correctamente para que pueda usar esas células para ordenar en consecuencia

Usted tiene un par de opciones:

(1) No hay forma de que las referencias de celda se arruinen cuando se ordena cuando no se usan referencias estáticas. La forma más básica de lidiar con esto es simplemente copiar y pegar como valores antes de la clasificación, lo que podría automatizarse a través de una simple macro de VBA.

(2) También podría intentar utilizar rangos con nombre si está usando un número de rangos comunes en sus fórmulas. Podría definir ''Sheet2! B23: 28'' como ''Range1'' y hacer referencia a ''Range1'' dentro de sus fórmulas. En ese caso, la clasificación obviamente no afectaría el rango que se especifica, ya que está definido en otro lugar.

HTH

Utilicé "guardar como" para copiar la hoja en un nuevo archivo como formato de texto msdos. Al hacerlo, se eliminan las fórmulas y se reemplazan los contenidos de la celda con solo los valores calculados. A continuación, abra el nuevo archivo como delimitado por tabulaciones y ordene después de definir las columnas. Necesitaba ordenar los valores calculados por una cadena de texto asociada (destino) para el registro de kilometraje para poder resumir el kilometraje de cada destino.

fecha que comienza terminando destino de distancia

donde en la fila 2 y las filas sucesivas comenzando se encontraba el final de la fila anterior y la distancia terminaba menos el comienzo.

Yo tuve el mísmo problema; Tenía una hoja maestra que era un resumen de la información en otras hojas de trabajo en mi libro de trabajo.

Si solo desea filtrar / ordenar en una hoja de trabajo donde tiene sus datos almacenados, y luego devolverlo a su estado original (sin importar qué filtre / clasifique por) solo haga que su primera columna sea un número de línea de artículo.

Después de su filtro / clasificación inicial, puede recurrir al "Número de línea de artículo" para que todo vuelva a la normalidad. NOTA: Esto solo funciona si siempre agrega nuevas filas al final de la lista en secuencia.

cree dos pestañas, una con las fórmulas y luego una segunda donde pegue el enlace de toda la tabla. ¡la segunda pestaña no debería tener problemas al ordenar!

esta es una buena respuesta para descubrir cómo funciona la clasificación de otro usuario y agregaré mis notas para saber cómo no es totalmente correcta y cuál es la correcta:

El efecto sobre la fórmula después de una clasificación es igual a la copia. Una ordenación no mueve los contenidos de la fila, los copia. La fórmula puede (o puede no depender de las referencias absolutas / relativas) usar datos nuevos, tal como lo hace una fórmula copiada.

Mi punto es que si la fórmula se puede copiar de 1 fila a otra y los efectos no cambian, la clasificación no afectará los resultados de la fórmula. Si las fórmulas son tan complejas y tan dependientes de la posición que al copiarlas se modifican los contenidos relativos, entonces no las clasifique.

Y mi nota que experimenté en la práctica:

El usuario anterior está diciendo lo correcto, pero de hecho tiene alguna excepción: partes de una fórmula de columnas que contiene el nombre de la hoja (como sheet1! A1) se tratan como referencias absolutas (a pesar de la copia que cambia las referencias si son relativas) por lo que esa parte de la fórmula se copiará sin cambiar las referencias relativas a cambiar el lugar de la fórmula. Esto incluye las celdas de la hoja actual tratadas como: sheet1! A2 y se tratarán como referencias absolutas (solo para ordenar) Probé esto de excel 2010 y no creo este problema se resuelve en otras versiones. La solución es copiar y pasar especial como valor en otro lugar y luego usar clasificación.

Estoy bastante seguro de que esto se puede resolver con la función indirect() . Aquí hay una hoja de cálculo simplificada:

A B C D ... +------------------------------------------------------+- - - - - - - - - 1 |CITY |Q1-Q3 SALES|ANNUALIZED SALES:(Q1+Q2+Q3)*1.33| +======================================================+- - - - - - - - - 2 |Tampa | $23,453.00| $31,192.49| +------------------------------------------------------+ 3 |Chicago | $33,251.00| $44,223.83| +------------------------------------------------------+ 4 |Portland | $14,423.00| $19,182.59| +------------------------------------------------------+ ...| ... | ... | ... |

Normalmente, la fórmula en la celda C2 sería =B2*1.33 , que funciona bien hasta que haga una clasificación compleja. Para que sea robusto para ordenar, construya su propia referencia de celda usando el número de fila de esa celda como esta: =indirect("B"&row())*1.33 .

Espero que funcione en tu situación. Arreglaba un problema similar que estaba teniendo.