XL 2019 Recopier Liste avec une valeur en Moins

[Caninge]

Bonjour,

Me revoici ! En ce moment je suis en plein dans les tableaux Excel.
J'ai essayé mais chuté !
J'ai une liste de noms dans la feuille "Liste de joueurs"
Je voudrais recopier cette liste mais avec un en moins Brigitte en B2
Pour arriver aux résultats F5 : F11. A chaque fois que je change dans la cellule B2 la liste se réactualise sans la personne.
J'ai essayé LOL (voir mes formules)
Merci de m'aider !
CANINGE

 

[sylvanu]

Bonsoir Caninge,
Un essai en PJ avec une petite macro.
et une V2 pour le fun, en utilisant qu'un seul array.

 

[job75]

Bonjour Caninge, sylvanu,

Depuis le temps qu'on manipule des formules matricielles vous devriez faire ça tout seul, en B4 :

=SIERREUR(INDEX('Liste joueurs'!B:B;PETITE.VALEUR(SI(('Liste joueurs'!B$3:B$1000<>"")*('Liste joueurs'!B$3:B$1000<>B$2);LIGNE('Liste joueurs'!B$3:B$1000));LIGNE(B1)));"")

A+

 

[Caninge]

Bonjour Job75, Sylvanu

Oui effectivement depuis le temps. ce n'est pas facile pour moi. J'aimerais bien !!! je me défends peut-être mieux au scrabble

 

[job75]

Une solution VBA avec cette macro dans le code de la feuille "Résultats" :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Application.ScreenUpdating = False
With Target(3).Resize(100) '100 à adapter
    Sheets("Liste joueurs").[B3].Resize(100).Copy .Cells
    .Replace Target, "", xlWhole
    On Error Resume Next 'si aucune SpecialCell
    .SpecialCells(xlCellTypeBlanks).Delete xlUp
End With
End Sub

Elle se déclenche quand on modifie la cellule B2.

Edit : on peut ne copier que les valeurs, c'est plus rapide :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$2" Then Exit Sub
Application.ScreenUpdating = False
With Target(3).Resize(100) '100 à adapter
    .Value = Sheets("Liste joueurs").[B3].Resize(100).Value
    .Replace Target, "", xlWhole
    On Error Resume Next 'si aucune SpecialCell
    .SpecialCells(xlCellTypeBlanks).Delete xlUp
End With
End Sub

 

[Phébus]

Bonjour à tous

Tu peux utiliser cette formule pour avoir la liste filtrée (sans la valeur de "B2").

=FILTRE('Liste joueurs'!B3:B10;'Liste joueurs'!B3:B10<>Résultats!B2)

mettre une liste déroulante en B2

Cordialement

 

[Caninge]

Bonjour Phébus, Job75, sylvanu
je vois que vous avez chacun votre technique. VBA, formules et filtre.
Il faut choisir.
CANINGE

 

[OKBI]

Bonjour à tous,

Un essai avec Power Query
Cdt

 

[AtTheOne]

Bonsoir à toutes & à tous, bonsoir @Caninge

Avec la solution par formule de @job75, en utilisant des tableaux structurés et des noms définis (pour éviter la validation par CTRL MAJ ENTRER).

Les nom définis :

Noms définis
Diff_Nb_Lignes	=(LIGNES(ts_Extrait[Nom])+N(Joueur_Exclu<>"")-LIGNES(ts_Joueurs[Joueur]))
Joueur_Exclu	=Résultats!$B$2
Joueur_Extrait	=SIERREUR(INDEX(ts_Joueurs;PETITE.VALEUR(SI((ts_Joueurs<>Joueur_Exclu);LIGNE(ts_Joueurs)-LIGNE(ts_Joueurs[#En-têtes]));LIGNE()-LIGNE(ts_Extrait[#En-têtes])));"")

Données des tableaux structurés
ts_Joueurs	'Liste joueurs'!$B$3:$B$10
ts_Extrait	Résultats!$B$7:$B$14

deux petites démos :

Voir le fichier joint
À bientôt

 

[job75]

Bonsoir AtTheOne,

L'utilisation de tableaux structurés est tout à fait utile.

Mais éviter la validation matricielle ne diminue pas la durée du recalcul.

Tes formules comme les miennes (avec bien sûr la plage 'Liste joueurs'!B$3:B$10) se recalculent chez moi en 0,23 ms.

A+

 

[AtTheOne]

Salut @job7,

Oui, je n'ai pas essayé d'accélérer les calculs, juste utiliser les tableaux structurés, et éviter la validation matricielle (puisque les formules rentrées dans des noms définis sont d'emblée matricielles).
Et puis, question de goût, je trouve les formules avec des noms definis saisies dans la feuille plus parlantes (à condition que les noms eux-même soient parlants) ...
Bref c'était une proposition sans aucune prétention et une occasion de faire un post comme j'arrive toujours un peu tard.

À bientôt

 

[Caninge]

bonjour à tous,
merci pour toutes vos propositions !
je regarde tout ça !
a bientôt
CANINGE

 

[job75]

Bonjour le forum,

Dans les fichiers joints on calcule les durées d'exécution.

1) VBA.xlsm : même avec 100 000 lignes c'est très rapide.

Curieusement ma macro est plus rapide quand on copie tout que quand on copie les valeurs.

Je n'ai pas d'explication à cela, si vous en avez une merci de me la donner.

2) Formules.xlsm : avec 10 000 lignes il faut environ 20 secondes chez moi.

Les formules sont rendues volatiles par T(ALEA()) et sont recalculées avec Calculate.

A+