L'Extraction Des Valeurs À Partir D'Excel Colonnes Fusionnées

Je suis en train de faire un hlookup contre une valeur qui s'étend sur plusieurs colonnes. Mes données est similaire à ceci:

      A      B      C      D 
  ---------------------------  
1|       Col1          Col2
2|     x      y      z      w
3|
4|

Dans les lignes 3 et 4 (A3, B3, C3, D3, etc.), J'aimerais mettre des formules qui vont faire un hlookup quelque part d'autre dans le classeur. Le truc est que je voudrais pour rechercher des "Col1" pour les colonnes A et B et "Col2" pour les colonnes C et D. "Col1" est en A1, mais c'est vraiment A1 et B1 fusionné. Quand j'référence A1, "Col1" s'affiche, mais lorsque je de référence B1, la valeur de retour est vide.

Des idées?

Est le décalage toujours -1 si la cellule est nulle?
Dans la vraie feuille de calcul, il y a 5 cellules fusionnées. J'aurais besoin de ces cinq colonnes à la référence de la cellule fusionnée. Un sens?

OriginalL'auteur cLFlaVA | 2009-01-15

10

Pour obtenir l'accès à la "Col1" et "Col2" des étiquettes, vous pouvez utiliser les éléments suivants:

=INDEX($1:$1,1,COLONNE()-MOD(COLONNE()-1,2))

Note: Ceci suppose que vous êtes le regroupement, le même nombre de cellules. Si c'était trois cellules, vous venez de changer le dernier numéro de la formule 3, et ainsi de suite.

Edit: Voici comment cela fonctionne:

INDEX($1:$1,1, x ) renvoie la valeur de la cellule dans la ligne 1, colonne x. Si votre table n'est pas en fait situé dans le coin supérieur gauche de la feuille de calcul, vous pouvez le changer à la plage réelle qui comprend l'ensemble de vos étiquettes fusionnées. Dans ce cas, il serait:
INDEX($A$1:$D$1,1, x )

COLONNE() retourne le numéro de colonne de la cellule active (1 dans la colonne A, 2 dans la colonne B, etc.)

MOD(COLONNE()-1,x) renvoie à un décalage de la colonne à la colonne qui contient la bonne étiquette

Merci 🙂 j'aime ça "sucre syntaxique" de la vôtre
Si cette hypothèse ne tient pas, la solution par James Trou est mieux comme est complètement générique.
Cette fonction est très lisse, mais pour une raison que je viens de trouver que, parfois, il ne fonctionne pas, et à d'autres moments, il peut entrer dans un "état" où il était autrefois de travail, mais puis s'arrête. (Pas très utile, mais en espérant peut-être que quelqu'un d'autre avait rencontré des anomalies similaires?)

OriginalL'auteur e.James
15

Voici une autre solution qui peut également fonctionner lorsque les cellules fusionnées sont de largeurs différentes, permettez-moi d'illustrer avec un exemple:
1. Ouvrez à nouveau le Excel, de fusion B1, C1, D1
2. De Type Col1 dans la cellule fusionnée
3. Dans B2, tapez la formule =B1, et C2 =C1, D2 =D1
4. Vous devriez voir B2 Col1 C2, D2 0
5. Dans B3, tapez la formule =A3, le copier
6. Droit-cliquez sur la cellule fusionnée B1:D1, sélectionnez l'option "collage spécial -> formules"
7. Vous devriez voir la cellule fusionnée étant 0
8. De Type Col1 dans la cellule fusionnée
9. Vous devriez maintenant voir tous les B2, C2, D2 Col1, c'est à dire maintenant, vous pouvez faire référence à la cellule fusionnée comme vous l'attendez.
Si vous pouvez plusieurs cellules fusionnées, chacun de largeurs différentes, il suffit de coller la formule à l'ensemble d'entre eux en une seule fois.

La raison derrière cela fonctionne est un perculier choix de conception par Microsoft. Il semble que, lorsque vous collez des formules dans des cellules fusionnées, chaque cellule sous-jacente reçoit la formule (en revanche, si vous entrez une valeur, seule la partie supérieure gauche de la cellule, il reçoit), de Sorte que vous pouvez l'utiliser à votre avantage et coller une formule faisant référence à la cellule d'à côté, puis de l'écraser en haut à gauche de la cellule avec la valeur que vous souhaitez, puis toutes les cellules sous-jacentes de la cellule fusionnée aura cette valeur.

Truc intéressant. TLDR est que vous utilisez Coller des Formules pour disperser =<cell to my left> dans chaque cellule de la plage, puis tapez une valeur dans la première.
Quelques mineurs mises en garde à noter: l'Insertion d'une nouvelle colonne à l'intérieur de l'intervalle de ne pas reproduire les adjacente formules que vous pourriez vous attendre (si vous devez réappliquer ces gymnastique). Et les versions d'Excel antérieures à 2010 ont un bug qui désinstallation de la cellule, puis en cliquant sur Annuler, ne restitue pas le "dispersés" des formules.

OriginalL'auteur Martin
4

J'ai construit une fonction simple en vba qui permettra de résoudre ce problème:
```
Function mergedText(rngMergedCell As Range)

    If rngMergedCell.MergeCells = True Then
        mergedText = rngMergedCell.MergeArea(1, 1)
    Else
        mergedText = rngMergedCell
    End If

End Function
```
Si la cellule est une cellule fusionnée, la fonction retourne la valeur du premier élément de la cellule fusionnée - c'est là que la cellule fusionnée stocke sa valeur

OriginalL'auteur
2

Plus générique variante de l'e.James, de la proposition est :
```
={INDEX($A$1:A1, 1, MAX(NOT(ISBLANK($A$1:A1))*COLUMN($A$1:A1)-COLUMN($A$1)+1))}
```
Cela repose sur le fait que les cellules fusionnées sont vides à l'exception de la première (sauf si vous êtes dans un cas comme celui de Martin de la proposition).

Remarque: Les accolades sont là pour marquer une formule de tableau (ne pas les saisir, appuyez sur alt+entrée " pour valider la formule dans la cellule).

OriginalL'auteur Hoel
1

Je me rends compte que je suis en retard à ce fil, mais j'ai trouvé un vraiment de réponse simple à cette.

Si, par exemple, votre étiquette est fusionné sur 4 colonnes a1:d1, et si vous faites référence à b1, vous serez de retour "". Dynamique de trouver le bon étiquettes utiliser ce fx dans votre nouvelle table:
```
=if(OriginalTable!B1="",ThisTable!A1,OriginalTable!B1)
```
Je suis sûr que vous vous rendrez compte que cela permettra de capturer des plages dans e1:h1 etc comme vous faites glisser à travers.

Que c'est. Espérons que cela aide quelqu'un.

C'est une fantastique réponse qui ne nécessitent pas de fonctions supplémentaires/macros, etc.

OriginalL'auteur gavin
0

Les cellules B1 et D2 contiennent pas de valeurs, uniquement A1 et C1 ont quelque chose en eux.

De sorte que vous aurez juste à faire en sorte que vos formules dans les colonnes A et B font référence à A1 dans la mesure où la valeur de la recherche, et que vos formules dans les colonnes C et D sont tous deux font référence à la C1 pour la valeur de la recherche.

Je voulais éviter cela, il ya effectivement 132 colonnes de 6 cellules fusionnées (6 * 22). Je voulais un copiable formule qui font qu'il est facile dans le cas où la formule n'a besoin de changement.

OriginalL'auteur BradC

Vous devez vous connecter pour publier un commentaire.