Etendre horizontalement une formule où seul le chiffre varie

Bonjour,

C'est sans doute tout simple mais quand je veux étendre cette formule vers la droite :

=SI('[blabla]Janvier 2025'!C9=VRAI;"J";SI('[blabla]Janvier 2025'!D9=VRAI;"M";SI('[blabla]Janvier 2025'!E9=VRAI;"AM";"")))

il me met D9, E9, F9

Et je voudrais qu'il mette C10, D10, F10 dans le cellule suivante, puis C11,D11,E11, ...

En gros, il incrémente les lettres alors que je veux qu'elles restent figées, il fige les chiffres alors que je veux qu'ils augmentent de 1 par rapport à la cellule qui précède.

Merci

Bonjour,

Avec un fichier c'est plus simple.

Vous voulez étendre à droite et que cela incrémente les lignes ?

Vous pouvez utilisez la fonction Colonne()

Si votre formule est en colonne C

Construisez la référence et utilisez la fonction colonne() pour définir le numéro de ligne : "C"&colonne()+6, pour D9 "D"&Colonne() et pour E9 "E"&Colonne(), quand vous passerez à la colonne suivante, vous passerez à 10 etc...

Cordialement

bonjour,

edit , hello jbpt84

utilise la fonction indirect

=SI(INDIRECT("'[blabla]janvier2025'!C" & 8+COLONNE(A1))=VRAI;"J";SI(INDIRECT("'[blabla]janvier2025'!D" & 8+COLONNE(A1))=VRAI;"M";SI(INDIRECT("'[blabla]janvier2025'E" & 8+COLONNE(A1))=VRAI;"AM";"")))

Attention : indirect a un (gros) impact sur les performances s'il est utilisé de nombreuses fois. Dans ce cas, passer par vba ou formules pour générer et copier/coller les formules générées.

Merci à tous deux.

En ce qui concerne indirect, il y aura environ 24000 cellules concernées. Mais jamais plus de 30 par ligne.

Sans indirect, ça fonction bien (enfin sur les 2000 déjà faits) mais m'oblige à changer 3 variables à chaque cellule, ce qui me fait environ 75000 modifications à effectuer, soit mon souhait de pouvoir incrémenter.

Je testerai lundi.

Je préfère éviter le vba, dans mon environnement, il bug souvent.

Là, j'ai un classeur qui va chercher les infos dans 12 autres classeurs répartis dans 12 dossiers différents donc je cherche la solution la plus stable.

PS : je n'ai pas poussé jusqu'à les mettre dans 12 lecteurs virtuels différents (déjà testé et ce n'est pas top pour la stabilité), ils sont tous dans le même avec interdiction de les bouger

bonjour,

En ce qui concerne indirect, il y aura environ 24000 cellules concernées. Mais jamais plus de 30 par ligne.

A mon avis cela va ramer grave !

Je préfère éviter le vba, dans mon environnement, il bug souvent.

Le vba serait utilisé pour générer les formules, donc ne sera exécuté qu'une seule fois.

Il est aussi possible de générer les formules par une formule et de copier/coller en valeur le résultat (la formule générée) dans la cellule qui te convient. Mais cela demandera de passer en revue toutes les cellules pour valider les formules générées.

Cette formule génère ta formule.

="=SI('[blabla]janvier2025'!C" & 8+COLONNE(A1)&"=VRAI;""J"";SI('[blabla]janvier2025'!D" & 8+COLONNE(A1)&"=VRAI;""M"";SI('[blabla]janvier2025'!E" & 8+COLONNE(A1)&"=VRAI;""AM"";"""")))"

Tu veux dire que je dois la copier/coller dans chacune des 24000 cellules concernées ?

Pour les VBA, il y a aussi que je ne maîtrise pas bien le langage.

Autant xls, quand il y a un bug, j'arrive à repérer ou ça coince et réparer, autant en VBA, je serais bien embêté.

Quand je code, il est impératif que je comprenne ce que j'écris, une solution toute prête que je ne maîtrise pas, serait dangereuse :-)

C'est dommage qu'il n'existe pas une option dans les réglages pour dire à xls d'étendre les chiffres à l'horizontal et pas les lettres.

Pour que vous compreniez mieux ma démarche, je fais un calendrier des congés des 12 départements de ma structure. Chaque département gérant son personnel. Et les congés posés du personnel des 12 départements se retrouvent sur un calendrier général où chaque onglet est égal à un mois et contient en verticalité le nom des salariés de toute la structure.

Le pourquoi de cela en xls ? On veut un outil qu'on maîtrise à 100%, paramètre et modifie selon nos besoins, et qui soit en réseau interne, rien dans le cloud ou solution tierce intégrée.

Bonjour,

Pourriez-vous mettre un fichier anonymisé en exemple, avec quelques lignes pour que nous visualisions votre problématique précise et que nous puissions vous proposer une solution optimum ?

Indiquez bien, le nombre de colonnes et de lignes de chaque feuille, pour que nous puissions avoir une idée précise.

Cordialement

Voilà un peu à quoi ça ressemble :

Il y a donc 12 onglets car 12 mois dans l'année.

Là, c'est l'onglet de Janvier donc 31 jours

En ligne maintenant, c'est le nom de tous les salariés, soit 80.

Et donc ma formule maintenant (pour B2) : si dans l'xls nommé RH, la case en C9 est cochée alors écrire J, si c'est la D9 alors écrire M,, si c'est la E9, alors écrire AM.

Et je souhaiterai que dans C2 : il est indiqué C10, D10 et E10, que dans D2 : il est indiqué C11, D11, E11, ...

Pour le changement de ligne, je me débrouille ^^

Merci

Bonjour à tous

Pour éviter INDIRECT, ou pourrais utiliser DECALER.

Mais sans exemple, impossible de tester...

Cordialement

bonjour Efgé,

DECALER est aussi une possibilité, mais provoquera les mêmes problèmes de performances que INDIRECT si la fonction est utilisée dans un grand nombre de cellules.

En résumé, sans VBA, pas de solution stable, c'est bien ça ?

Après je n'ai réussi à utiliser indirect, il y a des éléments que je ne comprends pas dans vos formules. Faut dire que ça ne fait pas parti des fonctions que j'utilise, donc c'est de mon chef, pas de souci.

Si vous pouviez me traduire ce que signifie la formule en français, ça m'aiderait. 😊

Du genre, " en utilisant Indirect, je demande à xls de ... Et donc là je lui de ... dans la cellule ... pour qu'il me calcule ... et j'indique ... car... ".

Enfin voilà, l'important étant que je comprenne, non pas que j'ai une formule toute faite.

Sinon, me reste plus qu'à changer 24000 fois mes chiffres ^^ Ça doit représenter une centaine d'heures donc c'est pas catastrophique mais bon, je pensais qu'il y aurait une formule toute simple " et stable, même avec 100 000 occurrences ' car c'est un problème qui se répète souvent vu la logique d'incrémentation d'xls qui change uniquement les chiffres dans un sens et les lettres dans l'autre.

bonjour,

j'ai demandé à chatgpt de t'expliquer ce que fait la formule que je t'ai proposée (il le fait mieux que moi) voici :

je voudrais l'explication de cette formule excel :

ChatGPT a dit :

ChatGPT

Bien sûr ! Indique-moi la formule Excel en question, et je te fournirai une explication détaillée de son fonctionnement. 😊

4o

Vous avez dit :

=SI(INDIRECT("'[blabla]janvier2025'!C" & 8+COLONNE(A1))=VRAI;"J";SI(INDIRECT("'[blabla]janvier2025'!D" & 8+COLONNE(A1))=VRAI;"M";SI(INDIRECT("'[blabla]janvier2025'E" & 8+COLONNE(A1))=VRAI;"AM";"")))

ChatGPT a dit :

ChatGPT

Analysons cette formule Excel pas à pas pour bien comprendre son fonctionnement.

La formule

excelCopier le code<code>=SI(INDIRECT("'[blabla]janvier2025'!C" & 8+COLONNE(A1))=VRAI;"J";SI(INDIRECT("'[blabla]janvier2025'!D" & 8+COLONNE(A1))=VRAI;"M";SI(INDIRECT("'[blabla]janvier2025'!E" & 8+COLONNE(A1))=VRAI;"AM";"")))
</code>

1. Décomposition de la formule

• Fonction SI : Elle permet de tester une condition et de renvoyer une valeur en fonction du résultat :SI(condition; valeur_si_vrai; valeur_si_faux)
• Fonction INDIRECT : Elle permet de construire une référence à une cellule ou plage de cellules à partir d'une chaîne de texte. Ici, elle est utilisée pour accéder dynamiquement à des cellules dans une autre feuille ou fichier.
• COLONNE(A1) : Retourne le numéro de la colonne de la cellule A1. Ici, cela vaut 1 (car A est la première colonne). En fonction de la colonne où la formule est utilisée, cette valeur change.

2. Que fait cette formule ?

a. Première condition

INDIRECT("'[blabla]janvier2025'!C" & 8+COLONNE(A1))=VRAI

• "[blabla]janvier2025'!C" & 8+COLONNE(A1) : Construit une référence dynamique. Si COLONNE(A1)=1, cela donne la cellule C9 (car 8+1=9).
• La formule vérifie si la cellule C9 dans le fichier blabla (feuille janvier2025) contient VRAI.
• Si oui, elle renvoie "J".

b. Deuxième condition (si la première est fausse)

INDIRECT("'[blabla]janvier2025'!D" & 8+COLONNE(A1))=VRAI

• Vérifie la cellule correspondante dans la colonne D. Par exemple, si COLONNE(A1)=1, cela vérifie D9.
• Si cette cellule contient VRAI, la formule renvoie "M".

c. Troisième condition (si les deux premières sont fausses)

INDIRECT("'[blabla]janvier2025'!E" & 8+COLONNE(A1))=VRAI

• Vérifie la cellule correspondante dans la colonne E. Par exemple, si COLONNE(A1)=1, cela vérifie E9.
• Si cette cellule contient VRAI, la formule renvoie "AM".

d. Valeur par défaut

Si aucune des conditions n'est vraie, la formule renvoie une chaîne vide ("").

3. Résumé

Cette formule analyse dynamiquement les valeurs dans un fichier externe (blabla) et une feuille spécifique (janvier2025). Pour chaque colonne C, D, ou E de la ligne 8+COLONNE(A1):

• Si la cellule contient VRAI, elle renvoie :
  • "J" pour la colonne C,
  • "M" pour la colonne D,
  • "AM" pour la colonne E.
• Si aucune cellule ne contient VRAI, elle renvoie une chaîne vide.

4. Exemple d’utilisation

• Si cette formule est placée en A1 :
  • COLONNE(A1)=1,
  • Elle examine C9, D9, E9.
• Si cette formule est placée copiée/collée telle quelle dans la première cellule à droite : Le A1 sera transformé en B1 (par l'action de copier avec référence relative)
  • COLONNE(B1)=2,
  • Elle examine C10, D10, E10.

Cela permet d'analyser plusieurs lignes de manière dynamique en fonction de la colonne de la formule.

Est-ce clair ? Si tu as des questions ou si tu veux un exemple concret, fais-moi signe ! 😊

C'est fou ce que permet cette IA.

Merci !.Je vais travailler là dessus à tête reposée mais ça me semble déjà beaucoup plus clair.

Bon alors j'ai commencé en B3 pour tester mais il m'indique ref.

=SI(INDIRECT("'[blabla]janvier2025'!C" & 8+COLONNE(B3))=VRAI;"J";SI(INDIRECT("'[blabla]janvier2025'!D" & 8+COLONNE(B3))=VRAI;"M";SI(INDIRECT("'[blabla]janvier2025'E" & 8+COLONNE(B3))=VRAI;"AM";"")))

Pourtant j'ai bien modifié les A1 en B3. J'aurais commis un autre oubli ?

Merci

bonjour,

Tu dois laisser A1. Mets la formule telle quelle en B3 (sans modifier le A1) et puis tire-la vers la droite. En B3, cela fera référence à CDE de la ligne 8+1, la formule copiée en C3 (par la copie le A1 sera devenu B1 et colonne(B1) te renverra 2), cela fera référence à CDE de la ligne 8+2, la formule copiée en D3 (le A1 sera devenu C1 et colonne(c1) te renverra 3), cela fera référence à CDE de la ligne 8+3.

Bonjour,

C'est ce que j'avais fait au début et il m'indiquait aussi ref. 😔

Il y a un élément qu'il n'arrive pas à localiser et je ne comprends pourquoi. D'autant que sans indirect dans ma formule 1re, il y arrive très bien.

Là, pas de souci : =SI('[blabla]Janvier 2025'!C9=VRAI;"J";SI('[blabla]Janvier 2025'!D9=VRAI;"M";SI('[blabla]Janvier 2025'!E9=VRAI;"AM";"")))

Là, " ref " : =SI(INDIRECT("'[blabla]janvier2025'!C" & 8+COLONNE(B3))=VRAI;"J";SI(INDIRECT("'[blabla]janvier2025'!D" & 8+COLONNE(B3))=VRAI;"M";SI(INDIRECT("'[blabla]janvier2025'E" & 8+COLONNE(B3))=VRAI;"AM";"")))

bonjour, je vois une différence dans le nom de la feuille. "janvier 2025" vs "janvier2025".

tu peux également évaluer fonction par fonction une formule excel, via menu formules->évaluer la formule. tu y verras les résultats intermédiaires (et notamment, l'adresse qui est générée par la fonction "indirect", que tu pourras comparer à celle de ta formule qui fonctionne.

Merci à toi.

Alors j'avais aussi testé en retapant le chemin mais sans plus de succès.

J'avais aussi essayé évaluer mais sans forcément m'y étendre, je vais relire.

bonjour,

Indirect() ne fonctionne pas pour aller chercher des données sur des fichiers fermés. Et donne #REF si c'est le cas.

Merci.

Qu'est ce qu'on entend par fichier fermé ?