Comment trier un tableau dans Excel s'il contient des références de cellules?

j'ai un tableau de données en excel dans la feuille 1 qui fait référence à différentes cellules dans de nombreuses autres feuilles. Lorsque j'essaie de trier ou de filtrer la feuille, les références changent lorsque la cellule se déplace. Cependant, je ne veux pas entrer manuellement dans chaque cellule et insérer des $ signs partout, parce que certaines des références sont continues, et je pourrais vouloir auto-remplir plus tard.

par exemple, une de mes colonnes de données utilise la feuille 2, la colonne B 23:28, la feuille 2, la colonne C 1:15, et quelques autres. Le la section de la colonne qui utilise la colonne B de la feuille 2 est toutes au même endroit avant le tri, comme le sont chacune des autres sections. Si j'insère $ signs, Je ne serais plus en mesure d'insérer des lignes vides et d'utiliser auto-remplir pour de nouvelles données.

en résumé, je voudrais un moyen de garder mes références de cellules statiques sans utiliser de $ signs, mais uniquement à des fins de tri/filtrage. Ma solution actuelle consiste à copier les valeurs de la table, à coller dans une feuille vide, puis à trier. J'espère qu'il n'y une meilleure façon de le faire.

24
demandé sur Teofrostus 2011-11-02 15:58:48
la source

21 ответов

je suis assez sûr que cela peut être résolu avec le indirect() fonction. Voici une feuille de calcul simplifié:

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

Normalement la formule dans la cellule C2 serait =B2*1.33, ce qui fonctionne bien jusqu'à ce que vous fassiez un tri complexe. Pour le rendre robuste au tri, construisez votre propre référence de cellule en utilisant le numéro de ligne de cette cellule comme ceci: =indirect("B"&row())*1.33.

j'Espère que les travaux dans votre situation. Elle fixe un problème similaire que j'avais.

16
répondu Alex Svetlev 2014-11-18 05:20:57
la source

Pour moi cela a fonctionné comme ci-dessous -

j'avais des références de nom de feuille dans la formule pour la même feuille. Quand j'ai enlevé le nom de la feuille courante de la formule et trié il a fonctionné correctement.

9
répondu user3601903 2014-05-04 21:24:57
la source

même avec des références absolues, sort ne gère pas les références correctement. Les références relatives pointent vers le même décalage relatif par rapport à la nouvelle position de la ligne (ce qui est évidemment erroné parce que les autres lignes ne sont pas dans la même position relative) et les références absolues ne sont pas changées (parce que le tri omet l'étape de traduire les références absolues après chaque réarrangement d'une ligne). La seule façon de le faire est de DÉPLACER manuellement les lignes (après avoir converti les références à absolue), un par un. Excel effectue ensuite la traduction nécessaire des références. Le SORT Excel est déficient car il ne fait pas cela.

7
répondu rjdc 2013-05-02 15:18:31
la source

Vous avez deux options:

(1) Il n'y a aucun moyen d'éviter que les références de cellules se gâtent lors du tri lorsqu'on n'utilise pas de références statiques. La manière la plus simple de gérer cela est de simplement copier et coller les valeurs avant de les trier, ce qui pourrait être automatisé via une macro VBA simple.

(2) Vous pouvez également essayer d'utiliser les gammes nommées si vous utilisez un certain nombre de gammes communes à travers vos formules. Vous pouvez définir la " Feuil2!B23: 28 "Comme" Range1 " et référence 'Plage1" dans vos formules. Dans ce cas, le tri n'affecterait évidemment pas la plage spécifiée puisqu'elle est définie ailleurs.

HTH

6
répondu Chris 2011-11-03 02:40:18
la source

j'ai moi-même eu des problèmes avec cela et j'ai trouvé la meilleure et la solution la plus facile...

  1. Copiez les données que vous souhaitez faire dans une table et puis trier.
  2. Coller est quelque part d'autre sur votre feuille de calcul
  3. les valeurs vont changer en raison de vos références de cellules, mais nous ne voulons pas que cela alors cliquez sur le pop-up pour faire un 'paste special' et choisissez paste 'values' seulement.
  4. transformez vos nouvelles données en une table et triez votre vie loin!!!

j'Espère que vous trouverez ça fonctionne. Égard.

2
répondu Chris 2015-07-21 14:38:04
la source

Nous sommes également aux prises avec le même problème.

en guise de solution de contournement, nous utilisons une macro pour couvrir une table à la liste, trier la liste et puis couvrir la liste de retour à la table.

ici s'il vous plaît trouver un exemple de macro

    Sub Sort_Table()

    'change table name to "table1"
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A:$c"), , 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:$c"), , xlYes).Name = _
    "Table1"
  ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
  End Sub
0
répondu Bing Maletz 2013-06-13 15:48:30
la source

j'ai eu ce même problème; j'ai eu une fiche qui était un résumé de l'information sur d'autres feuilles dans mon classeur.

si vous voulez juste filtrer / trier dans une feuille de travail où vous avez vos données stockées, et ensuite le retourner à son état d'origine (peu importe ce que vous filtrez/tri) il suffit de faire votre première colonne un numéro D'article de ligne.

après votre premier filtre / tri vous pouvez alors simplement recourir au "numéro D'article de ligne" pour tout retourner à la normale. NOTE: cela ne fonctionne que si vous ajoutez toujours de nouvelles lignes à la fin de la liste dans l'ordre.

0
répondu OSC 2014-03-19 01:06:44
la source

Meilleure façon est de garder vos données de référence sur un côté de la feuille et toutes les formules de l'autre côté de la feuille. ensuite, laissez une colonne vide entre eux (masquez-la si vous voulez) et alors vous ne trierez que les données de référence en gardant les références de Formule pointant toujours au même endroit. L'inconvénient, C'est Qu'Excel va revenir chaque fois que vous triez.

0
répondu Dimitar 2014-10-29 15:50:31
la source

une façon très simple est de garder les colonnes de problèmes hors des données filtrées, mais ajouter une colonne dans la section filtrée avec des références de cellules à eux (simplement "='cell'").

de cette façon les formules de problème resteront intactes mais les données filtrées les référenceront correctement de sorte que vous pouvez utiliser ces cellules pour trier en conséquence

0
répondu Danieljfern 2015-02-18 12:45:29
la source

Essayez cette méthode:

  1. Insérer une colonne à gauche des cellules à trier.
  2. supposons que les colonnes B:G soient triées en fonction de B
  3. Copier col B
  4. coller les valeurs dans la colonne
  5. en surbrillance cols B: G
  6. Remplacer = signes avec le n ° des signes
  7. Trier cols A:G basé sur un
  8. en surbrillance cols B: G
  9. remplacer # signes avec = les signes
  10. Supprimer le col A.
  11. Voila!
0
répondu Wozza 2015-04-13 02:59:20
la source

j'avais besoin de trier les cellules avec des références, et j'avais vraiment besoin d'éviter de coller des valeurs avec lesquelles travailler.. La" table à Pivot " a fait l'affaire.

  1. préparez vos tableaux avec des références.
  2. sélectionnez la table (avec les références) et insérez la table à Pivot
  3. dans la table pivotante, sélectionnez les filtres nécessaires pour que la table pivotante ressemble à votre Table originale (si nécessaire).
  4. trier / filtrer les données plus loin au besoin.

il suffit d'être sûr de cliquez avec le bouton droit de la souris sur la table de pivotement et cliquez sur "Actualiser" chaque fois que vous modifiez des données génériques (utilisées dans vos tables).

j'Espère que ça aidera. Andrei

0
répondu Andrei 2015-06-30 15:55:47
la source

Je ne suis pas sûr que cela satisfera tous, mais j'ai trouvé une solution simple qui a réglé le problème pour moi. Déplacez toutes vos cellules/formules référencées sur une feuille différente de sorte que vous ne référencez aucune des cellules dans la feuille qui a la table à trier. Si vous faites cela, vous pouvez trier loin!

0
répondu Steve S 2015-09-09 21:07:27
la source

ceci est une bonne réponse pour comprendre comment le tri fonctionne d'un autre utilisateur et je vais ajouter mes notes pour savoir comment il n'est pas entièrement correct et ce qui est correct:

l'effet sur la formule après une sorte est le même que la copie. Une sorte ne déplace pas le contenu de la ligne, elle les Copie. La formule peut (ou peut ne dépend pas de l'abs/références relatives) l'utilisation de nouvelles données, comme une formule copiée.

Mon point est que si la formule peut être copié d'une ligne à l'autre et les effets ne changent pas, le tri n'affectera pas les résultats de la formule. Si les formules sont si complexes et si dépendantes de la position que les copier change le contenu relatif, alors ne les triez pas.

Et ma note que j'ai expérimenté dans la pratique:

l'utilisateur ci-dessus dit droit mais en fait il a une certaine exception: les parties d'une formule de colonnes contenant le nom de la feuille (comme sheet1!A1) sont considérées comme des références absolues (malgré la copie qui change les références si elles sont relatives ) de sorte qu'une partie de la formule sera copiée sans changer les références relatives au changement de la place de la formule Cela inclut les cellules de feuille actuelles adressées complètement comme: sheet1!A2 et seront traités comme des références absolues(pour le tri) J'ai testé cela d'excel 2010 et je ne pense pas que ce problème soit résolu dans d'autres versions. La solution est de copier et de passer spécial comme valeur dans un autre endroit et ensuite utiliser le tri.

0
répondu jafar 2015-12-08 21:42:55
la source

La réponse pour moi était de séparer les données en deux grilles avec une colonne vide entre eux. La grille de gauche contient les données que vous voulez pouvoir trier, la grille de droite contient la formule que vous voulez calculer. Lorsque vous triez, la formule fonctionne simplement sur les données dans les lignes dans la grille de gauche et ne pas obtenir les références de ligne mélangées par le tri.

0
répondu wolfgang 2016-06-27 19:49:46
la source

la méthode la plus simple est d'utiliser la fonction OFFSET. Donc, pour l'exemple d'origine, la formule pourrait être: =decaler(c2,0,-1)*1.33 ="en utilisant la cellule courante (c2) comme point de référence, obtenir le contenu de la cellule sur la même ligne, mais une colonne à gauche (b2) et le multiplier par 1,33"

Fonctionne un régal.

0
répondu Angus Wolfendale 2016-12-13 21:00:29
la source

j'ai utilisé "Enregistrer sous" pour copier la feuille dans un nouveau fichier au format msdos. Cela supprime les formules, en remplaçant le contenu de la cellule par juste les valeurs calculées. Puis ouvrez le nouveau fichier comme onglet délimité et triez après avoir défini les colonnes. J'ai eu besoin de trier les valeurs calculées par une chaîne de texte associée (destination) pour le journal de kilométrage afin que je puisse résumer le kilométrage pour chaque destination.

date Début Fin distance destination

où à la ligne 2 et les rangées suivantes débutaient par la fin de la rangée précédente et la distance se terminait moins le début.

0
répondu Glenn 2017-08-13 01:36:57
la source

créer deux onglets, un avec les formules et un second où vous collez lier la table entière. le deuxième onglet devrait pas avoir de problèmes lors du tri!

0
répondu Daniel 2017-08-18 18:45:15
la source

une autre solution consiste à: (1) copier la table entière - coller comme un lien dans le même tableur, ne travailler désormais que sur la "table liée" (2) Copier la colonne avec les valeurs à trier et coller uniquement les valeurs de juste à côté de la table que vous souhaitez trier (3) Sélectionnez le tableau et remplacez all = par exemple #, cela changera la référence dans un texte statique (4) trier le tableau par les valeurs collé (5) remplacer dos tous les # avec =, les références sont de retour Fait! Il va assez rapide lors de l'utilisation d'excel raccourcis

0
répondu jacek 2018-03-15 15:57:54
la source

j'espérais trouver un pourquoi ici, mais je pense que la réponse est plus simple que nous en faisons.

les cellules devraient trier quelle que soit la page à laquelle elles renvoient dans votre classeur. Ce qui causait le problème pour nous était n'importe quelles références de nom de page sur la page actuelle.

EX: je suis dans le cahier D'Aujourd'hui, et je fais référence à des données pour un représentant des ventes sur une autre page.

si mes critères (le nom du Représentant) sont présents Aujourd'hui!C1 (ou C1 $ C1, $ c$1) et j'essaie de tri, la feuille ne reconnaîtra pas l'action. Mais si vous supprimez le nom qui fait référence à la page sur laquelle vous êtes (référence redondante vraiment) ce problème devrait s'arrêter.

donc SUMIFS ('Sales'!C1, "Ventes"!A1, Aujourd'Hui!C1) seront désormais SUMIFS ("ventes"!C1, "Ventes"!A1, C1)

Si quelqu'un peut éclairer comme pourquoi il fonctionne de cette manière, ce serait formidable.

-1
répondu Wesley Alexander 2017-10-02 22:09:29
la source

mettez un signe de dollar devant la rangée et/ou la colonne de la cellule que vous voulez rester constant.

réparé pour moi!

-2
répondu chitty 2016-04-07 23:33:40
la source

solution facile - copier à partir d'excel et coller dans Google sheets. Il copie toutes vos cellules de référence comme des cellules absolues de sorte que vous pouvez commencer à partir de zéro. Puis trier et télécharger à nouveau comme une grille excel et reformater à vos besoins.

-2
répondu Chris 2017-03-29 23:29:26
la source

Autres questions sur