Créer un tableau qui montre les lignes sélectionnées à partir d'une autre feuille

J'ai un classeur Excel avec une feuille à la recherche de quelque chose comme ceci:

Apple    |    15    |    Bob
Banana   |    20    |    Tim
Apple    |    19    |    Mary
Banana   |    25    |    Kate

Je veux créer une autre feuille qui n'affiche que les lignes de la première feuille qui ont "Apple" dans la première colonne:

Apple    |    15    |    Bob
Apple    |    19    |    Mary

Dans un sens, c'est analogue à une requête SQL de type SELECT, où nous avons sélectionnez toutes les entrées dans le premier tableau (feuille) qui ont une valeur donnée.

Le hic, c'est que je ne veux pas avoir à maintenir constamment la deuxième feuille. Si je change la première feuille—par exemple, le changement "Apple" dans la première ligne de "Raisin"—je veux la deuxième feuille automatiquement mise à jour afin qu'il ne montre que "Apple" lignes—dans cet exemple, seule la "Marie" ligne maintenant être montré.

Cela semble être souvent très utile. Est-il possible de le faire sans l'aide de Visual Basic scripting?

Vous pouvez soit utiliser un tableau croisé dynamique dans la deuxième feuille ou une formule de tableau. Les deux solutions sont indiqués dans la Excel: valeurs de retour de plusieurs lignes correspondantes
Est-il une raison de réponse n'est pas acceptée ?
Je n'ai pas testé. Je ne peux pas parce que je suis sur Mac Excel 2008 et ne peut pas utiliser VBA (il s'avère). J'ai donc besoin de non-VBA réponses seulement. Je vais mettre à jour la question.

OriginalL'auteur OldPeculier | 2013-05-24

5

Vous pouvez le faire avec un complexe non-matrice de formule standard, mais vous aurez à remplir quelques lignes au-delà de ce que vous avez initialement revenir, afin de compenser automatiquement les futurs ajouts/suppressions/modifications.

J'ai mis la table cible sur la même feuille de calcul que la table source pour économiser de l'espace pour cet exemple, mais j'ai ajusté les références de plage de cellules dans la formule(s), de sorte que vous pouvez facilement dire qui est qui.

Démarrer en tapant Apple en E4 puis mettre cette formule en F4.
```
=IF(LEN($E4), IFERROR(INDEX('first sheet'!B$1:B$999, SMALL(INDEX(ROW($1:$999)+('first sheet'!$A$1:$A$999<>$E4)*1E+99, , ), ROW(1:1))), ""), "")
```
Remplir ce droit à G4, puis de remplir F4:G4 une ligne vers le bas de la touche F5:G5. Mettre cette formule en E5.
```
=IF(COUNTIF('first sheet'!A:A, E4)>(COUNTIF(E$4:E4, E4)), E4, "")
```
Maintenant remplir E5:G5 vers le bas pour attraper tous de retour possible (en plus de quelques lignes) à partir de la feuille de calcul source.

Tapant un nouveau fruit de la valeur en E4 instantanément modifier la nature des données renvoyées.

Les calculs sur ce ne sont pas terrible et complexe et les plages de cellules pointant vers la source de données devrait être facilement en mesure de gérer quelques milliers de lignes, mais vous allez commencer à voir de calcul retard que la source de données augmente.

J'ai vu certains utilisateurs d'utiliser une mise en forme conditionnelle vérifier à l'encontre d'un NB.SI ou COUNTIFS fonction assurez-vous qu'ils sont la collecte de toutes les lignes possibles. Cela semble indiquer quand il n'y a pas assez de formules de tenir compte de tous les résultats. La mise en œuvre de quelque chose comme ça, c'est strictement de l'utilisateur. Post de retour avec des détails si c'est quelque chose que vous voulez mettre à utiliser.

J'ai mis que l'exemple de classeur (avec differrent source et cible les feuilles de calcul) sur mon Bureau Exprimés ici: Excel_Formula_to_Mimic_SQL_Query.xlsx.

Je serais intéressé par une solution similaire, mais au lieu que montre (sur une autre feuille, par exemple) quel est le résultat de la filtrages effectués sur la feuille de référence. ex: ref feuille de 10000 lignes, mais après quelques colonnes de filtrage, c'est maintenant à 2000 lignes affichées. J'aimerais avoir la 2ème feuille habe 2000 valeurs des lignes 2-2001, ce qui est maintenant affiché dans la feuille 1 (c'est à dire, le résultat de tous les filtrages) : semble y avoir une façon de votre solution, mais j'ai du mal à analyser. TLDR: un moyen d'avoir feuil2 d'affichage, ce qui serait un manuel "tout copier à partir de la feuille sheet1" , lors de la feuille sheet1 a été filtrée

OriginalL'auteur

Copier le code ci-dessous à la feuille sheet1 section de code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    On Error Resume Next

    Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    Dim rng As Range, cell As Range
    Set rng = Range("A1:A" & lastRow)

    Sheets("sheet2").Cells.Clear
    If Not Intersect(rng, Target) Is Nothing And Target.Value <> "" Then
        For Each cell In rng
            If Trim(LCase(cell.Value)) = Trim(LCase(Range("A1"))) Then
                With Sheets("sheet2")
                    cell.Resize(, 3).Copy .Cells(.Range("A" & .Rows.Count).End(xlUp).Row + 1, 1)
                End With
            End If
        Next
    End If

    Application.EnableEvents = True
End Sub

OriginalL'auteur Santosh

Vous devez vous connecter pour publier un commentaire.