Recherche par paquets de colonne

[CISCO]

Rebonsoir à tous

Ci-joint un petit fichier pour expliquer ma question.

Pour chaque jour, je rempli pour certains noms une cellule (motif) avec un code, puis forcément une ou les deux colonnes directement à droite (matin et ap midi) avec des lettres.

Sur ce fichier, il me faut trouver les formules pour remplir les cases grise comme dans l'exemple donné. Dans celles ci je dois compter les cellules jaunes non vides (chacune compte pour 0,5), en fonction du contenu des cellules vertes, par paquet de 3.

Seule la valeur en K3 a été obtenue avec une formule. Celle-ci fonctionne bien, mais malheureusement, dans le vrai fichier, il ni a pas 3 ou 4 jours, mais tout un mois (et pas une dizaine, mais une centaine de noms !). Plutot que de répéter, dans chaque cellule grise, une trentaine de fois SOMME(SI(B3=K2;SI...)+, n'existerait-il pas une autre technique plus "propre", plus esthétique pour obtenir le même résultat sans écrire des formules à rallonge ?



Merci d'avance et bon W.E.

@ plus

 

[wilfried_42]

Re : Recherche par paquets de colonne

Bonjour,

Je ne suis pas sur d'avoir tout compris, apres avoir ajuster les $, dans ere cellule le resultat est conforme, mais dans les autres un difference de 0,5

mais n'en sachant pas plus ......

les $ permettent de figer, soit la colonne, soit la ligne, soit la cellule
$A$1, cellule figée
$A1, Colonne Figée, la ligne evolue quand on tire la formule verticalement
A$1, ligne figée, la colonne evolue quand on tire la formule horizontalement
A1 : la ligne et la colonne voluent quand on tire la formule

=SOMME(SI($B3=K$2;SI($C3:$D3<>"";1;0);0))/2+SOMME(SI($E3=K$2;SI($F3:$F3<>"";1;0);0))/2+SOMME(SI($H3=K$2;SI($I3:$J3<>"";1;0);0))/2

seulement en fonction de ce que j'ai compris

 

[CISCO]

Re : Recherche par paquets de colonne

Bonsoir

Merci Wilfried_42

Le problème n'est pas de faire glisser la formule de K3 vers les autres cellules grises, ce qui serait effectivement assez facilement faisable avec des $ correctement placés, mais de trouver une écriture plus courte, plus "propre" même lorsque le test doit être fait pour une trentaine de jours.

Dans le fichier donné en exemple précédemment, on a en K3 et pour trois jours seulement :
=SOMME(SI(B3=K2;SI(C3😀3<>"";1;0);0))/2+SOMME(SI(E3=K2;SI(F3:G3<>"";1;0);0))/2+SOMME(SI(H3=K2;SI(I3:J3<>"";1;0);0))/2

Pour 5 jours cela donnerait en Q3, avec le code correct en Q2 :
={SOMME(SI(B3=Q2;SI(C3😀3<>"";1;0);0))/2+SOMME(SI(E3=Q2;SI(F3:G3<>"";1;0);0))/2+SOMME(SI(H3=Q2;SI(I3:J3<>"";1;0);0))/2+SOMME(SI(K3=Q2;SI(L3:M3<>"";1;0);0))/2+SOMME(SI(N3=Q2;SI(O3😛3<>"";1;0);0))/2}

Imaginez la longueur de la formule pour trente jours (une trentaine de +SOMME(SI(..) ), pour une seule cellule grise !

Cela doit être faisable en créant une nouvelle fonction, mais là, j'y perd mon latin😕

@ plus

 

[Luki]

Re : Recherche par paquets de colonne

Salut Cisco. Salut Wilfried, content de te croiser 🙂

J'adore jouer avec les fonctions personnalisées !😀

Une première approche en Pj.

La fonction a besoin de la colonne de début et de fin. Une sélection de cellules suffit.
Tu peux placer les dollars (pas dans ta poche!) correctement et ça fait le travail : les colonnes début et fin doivent être en absolu pour copier glisser ta formule dans la zone modif.
Tu trouves ta fonction dans: menu/insertion/fonction/personnalisées(dans la liste de gauche) et "SommeCisco".

On pourrait aller plus loin en faisant directement le total sans passer par les colonnes de modif "a" à "f". Mais je ne sais pas combien tu dois gérer de colonnes de modif dans ton fichier réel....

Dis nous si c'est l'esprit de ce que tu veux.

Bonne nuit

 

[CISCO]

Re : Recherche par paquets de colonne

Bonjour à tous, bonjour luki.

Merci Luki, c'est tout à fait ce dont j'avais besoin.

Il ni a qu'un petit problème : On est forcé de commencer les calculs sur la ligne 3. Si le tableau démarre sur une autre ligne, 4, 5..., et qu'on tappe $3 ou $4 ,ou autre, dans Sommecisco, cela ne fonctionne pas. Ce n'est pas très grave, car j'ai pu adapter mon tableau original pour que cela fonctionne. Toutefois, ne serait -il pas possible de résoudre ce petit problème ?

Comme je suis un peu curieux, j'aurai deux questions :
* Comment fait on pour exporter ce type de fonction personnalisée vers d'autres fichiers ? Dans le cas présent, j'ai fait le contraire, j'ai pris votre fichier, et j'y ai collé mes tableaux. Quand il y en a beaucoup, ce n'est pas très pratique.

* Est-il possible de créer ce type de fonction sans passer par une macro, sans utiliser du VBA... ?

Merci d'avance et bon W.E.

 

[jeanpierre]

Re : Recherche par paquets de colonne

Bonjour CISCO, Wil, Luki,

A la seconde question, la réponse est non, ici.

A la première, ALT F11, tu passes sous VBE et au besoin Affichage/Explorateur de projet.

Tu as, à gauche, VBA Project et ton nom de fichier, et si besoin encore, tu développes le petit + en - . De là, tu vois toutes tes feuilles et en dessous Module. S'il est sur +, tu l'ouvres pour trouver Module1, ici.

Il te suffit de selectionner le code, d'en faire un Copier et dans un autre projet, de faire Insertion Module et pour finir un Coller.

Tu peux faire plus simple encore. Tu selectionnes le module1 et tu le glisses dans ton autre fichier (ouvert). J'espère que c'est assez clair.

Bonne fin d'après-midi.

Jean-Pierre

 

[Monique]

Re : Recherche par paquets de colonne

Bonjour,

Avec SommeProd

En J3 puis copier et coller sur tout le tableau :
=SOMMEPROD(($B3:$H3=K$2)*(($C3:$I3<>"")+($D3:$J3<>"")))/2

Ou bien on peut se passer des colonnes intermédiaires K😛
et calculer directement le total.
On nomme une plage contenant tous les motifs
=SOMMEPROD(NB.SI(Motif;$B3:$H3)*(($C3:$I3<>"")+($D3:$J3<>"")))/2

 

[Luki]

Re : Recherche par paquets de colonne

Bonsoir à tous,

Monique, un jour il faudra bien que j'essaie de comprendre cette satanée fonction! 😱

Jean-Pierre a bien répondu à tes question pour utilser ta fonction dans d'autres fichiers Cisco.

Alors, pour voir si tu as compris comment t'y prendre, voici la version avec un paramètre supplémentaire : le choix de la ligne de données à comparer.
Les 3 paramètres peuvent être en référence absolue. C'est maintenant à toi de jouer pour la coller où tu veux! 😉

Bonne soirée

Function SommeCisco(ColonneDépart As Range, ColonneFin As Range, LigneComparaison As Range)

Application.Volatile

Dim i As Integer
Dim result As Currency
Dim CallCol As Integer, CallRow As Long

CallCol = Application.Caller.Column
CallRow = Application.Caller.Row
For i = ColonneDépart.Column To ColonneFin.Column Step 3
   If Cells(CallRow, i) = Cells(LigneComparaison.Row, CallCol) Then
        If Cells(CallRow, i + 1) <> "" Then result = result + 0.5
        If Cells(CallRow, i + 2) <> "" Then result = result + 0.5
   End If
Next i
    SommeCisco = result

End Function

 

[CISCO]

Re : Recherche par paquets de colonne

Bonsoir.

Grand merci Monique. Ca marche super bien.

Merci aussi à JeanPierre et Luki. Reste plus qu'à appliquer vos conseils...

@ plus.