Formule SI appliquée à une colonne entière
Résolu
virginie
-
virginie -
virginie -
Bonjour,
j'aimerais pouvoir créer une formule avec si appliquée à une colonne entière.
Voici dans le concret dans le cadre d'un voyage professionnel :
j'ai une colonne avec des prénoms, une colonne avec le nom de l'hotel attribué, une colonne ou j'ai mis des 1.
mon problème est que pour savoir combien de personne j'ai dans chaque hotel, j'aimerais pouvoir faire une formule SI de type : si tel nom d'hotel apparait, alors =1.
je voudrais pouvoir sélectionner toute la colonne, mais ça ne marche pas..
pourriez vous m'aider s'il vous plaît??
merci bcp d'avance.
virginie
j'aimerais pouvoir créer une formule avec si appliquée à une colonne entière.
Voici dans le concret dans le cadre d'un voyage professionnel :
j'ai une colonne avec des prénoms, une colonne avec le nom de l'hotel attribué, une colonne ou j'ai mis des 1.
mon problème est que pour savoir combien de personne j'ai dans chaque hotel, j'aimerais pouvoir faire une formule SI de type : si tel nom d'hotel apparait, alors =1.
je voudrais pouvoir sélectionner toute la colonne, mais ça ne marche pas..
pourriez vous m'aider s'il vous plaît??
merci bcp d'avance.
virginie
A voir également:
- Excel formule colonne entière
- Déplacer colonne excel - Guide
- Formule somme excel colonne - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Trier colonne excel - Guide
- Formule si et excel - Guide
6 réponses
Bonjour,
Regarde l'aide excel sur la fonction somme.si()
Tu peux aussi faire un TCD (Tableau Croisé Dynamique du menu 'Données')
Ca demande plus d'investissement au départ mais ensuite tu gagnes ton temps.
Si tu modifies tes données ou ajoutes un hotel il faut rafraichir le TCD avec l'icone ! rouge et tout se met à jour.
ex : https://www.cjoint.com/?AKAqzAnsxLf
eric
Regarde l'aide excel sur la fonction somme.si()
Tu peux aussi faire un TCD (Tableau Croisé Dynamique du menu 'Données')
Ca demande plus d'investissement au départ mais ensuite tu gagnes ton temps.
Si tu modifies tes données ou ajoutes un hotel il faut rafraichir le TCD avec l'icone ! rouge et tout se met à jour.
ex : https://www.cjoint.com/?AKAqzAnsxLf
eric
Bonjour
soit la solution d'Eric via un TCD,
soit :
puisqu'il semble que vous ayez un nom d'hôtel par personne (puisque vous mettez des 1), il devrait suffire de compter combien de fois le nom de l'hôtel apparaît dans la liste:
soit:
à partir de ligne 2 pour l'ensemble
en A les prénoms
en B hôtels:
faites en C la liste des hôtels, à partir de C2
en D2:=NB.SI(B:B;D2)
à tirer sur la hauteur de liste C
vous donnera combien de fois le nom de l'hôtel apparaît en B et donc sauf erreur de compréhension, le nombre de personnes par hôtel.
un conseil: pour éviter les fautes de frappe dans la liste en B, utilisez la liste des noms d'hôtels en C pour faire une liste de validation et l'appliquer à la colonne B
sélectionnez le champ B
Données / validation
en haut de la fenêtre choisir liste
en bas:
cliquez et sélectionnez la liste en C
OK
vous n'avez plus qu'à choisir dans la colonne B avec la petite flèche affichée à droite à la sélection de la cellule.
crdlmnt
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
soit la solution d'Eric via un TCD,
soit :
puisqu'il semble que vous ayez un nom d'hôtel par personne (puisque vous mettez des 1), il devrait suffire de compter combien de fois le nom de l'hôtel apparaît dans la liste:
soit:
à partir de ligne 2 pour l'ensemble
en A les prénoms
en B hôtels:
faites en C la liste des hôtels, à partir de C2
en D2:=NB.SI(B:B;D2)
à tirer sur la hauteur de liste C
vous donnera combien de fois le nom de l'hôtel apparaît en B et donc sauf erreur de compréhension, le nombre de personnes par hôtel.
un conseil: pour éviter les fautes de frappe dans la liste en B, utilisez la liste des noms d'hôtels en C pour faire une liste de validation et l'appliquer à la colonne B
sélectionnez le champ B
Données / validation
en haut de la fenêtre choisir liste
en bas:
cliquez et sélectionnez la liste en C
OK
vous n'avez plus qu'à choisir dans la colonne B avec la petite flèche affichée à droite à la sélection de la cellule.
crdlmnt
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
Merci beaucoup!! le tableau croisé dynamique fonctionne très bien!
je n'ai pas bien compris ni réussi la 2ème solution, mais merci quand même...;.)
j'en profite pour poser 2 autres questions.
J'ai un autre tableau avec une liste de personnes, et des colonnes pour indiquer les repas du midi et du soir sur 1 mois.
J'aimerais pouvoir faire une mise en forme conditionnelle pour mettre de la couleur avec pour condition :
si le total de repas (par exemple du midi) est supérieur à 10 alors toutes les lignes où il y a le chiffre 1, se met en jaune pour dire que nous mettrons en place une cantine. Par contre si c'est inférieur ou égal à 10 alors toutes les lignes où il y a un 1 se mettent en rose pour dire que les personnes iront manger ailleurs.
savez vous comment faire? pour le moment j'arrive à metttre des couleurs quand il y a un 1, mais pas par rapport à un total.. je ne sais pas si je suis assez claire??
Une autre question :
si dans la même case je souhaite mettre Prénom et NOM avec cette casse à savoir Majuscule sur la premère lettre du prénom et MAJ sur le NOM et ceci donc dans la même cellule et appliquée à toute une colonne... comment puis-je faire? pour l'instant j'ai trouvé que soit tout en MIN soit tout en MAJ ou 1ere lettre de tous les mots en MAJ. Une idée???
Par avance je vous remercie beaucoup pour votre aide!!
virginie
je n'ai pas bien compris ni réussi la 2ème solution, mais merci quand même...;.)
j'en profite pour poser 2 autres questions.
J'ai un autre tableau avec une liste de personnes, et des colonnes pour indiquer les repas du midi et du soir sur 1 mois.
J'aimerais pouvoir faire une mise en forme conditionnelle pour mettre de la couleur avec pour condition :
si le total de repas (par exemple du midi) est supérieur à 10 alors toutes les lignes où il y a le chiffre 1, se met en jaune pour dire que nous mettrons en place une cantine. Par contre si c'est inférieur ou égal à 10 alors toutes les lignes où il y a un 1 se mettent en rose pour dire que les personnes iront manger ailleurs.
savez vous comment faire? pour le moment j'arrive à metttre des couleurs quand il y a un 1, mais pas par rapport à un total.. je ne sais pas si je suis assez claire??
Une autre question :
si dans la même case je souhaite mettre Prénom et NOM avec cette casse à savoir Majuscule sur la premère lettre du prénom et MAJ sur le NOM et ceci donc dans la même cellule et appliquée à toute une colonne... comment puis-je faire? pour l'instant j'ai trouvé que soit tout en MIN soit tout en MAJ ou 1ere lettre de tous les mots en MAJ. Une idée???
Par avance je vous remercie beaucoup pour votre aide!!
virginie
Bonjour,
1) il faudrait un extrait de ton fichier anonymisé (cjoint.com et coller le lien fourni ici)
2)
=NOMPROPRE(GAUCHE(A2;CHERCHE(" ";A2)))&MAJUSCULE(STXT(A2;CHERCHE(" ";A2)+1;50))
à recopier vers le bas, copier-collage spécial valeur sur la colonne, puis remplacer la colonne d'origine.
eric
1) il faudrait un extrait de ton fichier anonymisé (cjoint.com et coller le lien fourni ici)
2)
=NOMPROPRE(GAUCHE(A2;CHERCHE(" ";A2)))&MAJUSCULE(STXT(A2;CHERCHE(" ";A2)+1;50))
à recopier vers le bas, copier-collage spécial valeur sur la colonne, puis remplacer la colonne d'origine.
eric
Merci Eric,
à vrai dire pour ta deuxieme réponse je n'ai pas réussi...
et voici un lien vers un tableau type pour mon problème de couleur en fonction de la somme obtenue. (ne prends pas en compte les colonnes bleues)
http://cjoint.com/?3KBqNrn4Gqd
Merci beaucoup!
virginie
à vrai dire pour ta deuxieme réponse je n'ai pas réussi...
et voici un lien vers un tableau type pour mon problème de couleur en fonction de la somme obtenue. (ne prends pas en compte les colonnes bleues)
http://cjoint.com/?3KBqNrn4Gqd
Merci beaucoup!
virginie
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Re,
Il faut utiliser une formule pour tes MFC :
=ET(A4<>"";MOD(COLONNE();3)<>1;SOMME(A$4:A$54)<=10)
et
=ET(A4<>"";MOD(COLONNE();3)<>1;SOMME(A$4:A$54)>10)
Sélectionne A4:U54 avant d'appliquer les MFC, elles s'appliqueront sur toutes les cellules en une fois.
La colonne A doit être une colonne hotel, si décalage il faut changer la valeur <>1 du modulo
Dans une autre feuille je t'ai ajouté des explications pour le 2)
https://www.cjoint.com/?AKBrJ2S63TT
eric
Il faut utiliser une formule pour tes MFC :
=ET(A4<>"";MOD(COLONNE();3)<>1;SOMME(A$4:A$54)<=10)
et
=ET(A4<>"";MOD(COLONNE();3)<>1;SOMME(A$4:A$54)>10)
Sélectionne A4:U54 avant d'appliquer les MFC, elles s'appliqueront sur toutes les cellules en une fois.
La colonne A doit être une colonne hotel, si décalage il faut changer la valeur <>1 du modulo
Dans une autre feuille je t'ai ajouté des explications pour le 2)
https://www.cjoint.com/?AKBrJ2S63TT
eric
Merci Eric, la formule NOMPROPRE marche parfaitement merci!!!
quant à l'autre... alors j'ai réussi en recopiant parfaitement ce que tu as mis, par compte je suis obligée de copier le format pour chaque colonne... je ne peux pas faire le coup de pinceau dans tout le tableau... car sinon mon colonne bleu ne fonctionne plus..
est-ce qu'il faut donc le faire colonne par colonne?
je pensais que comme la formule indique de ne pas l'appliquer à la colonne bleue, je pouvais donc ne faire la manip' qu'une seule fois, mais ce n'est pas possible,n 'est ce pas??
merci 1000 fois pour ton aide précieuse!!
virginie
quant à l'autre... alors j'ai réussi en recopiant parfaitement ce que tu as mis, par compte je suis obligée de copier le format pour chaque colonne... je ne peux pas faire le coup de pinceau dans tout le tableau... car sinon mon colonne bleu ne fonctionne plus..
est-ce qu'il faut donc le faire colonne par colonne?
je pensais que comme la formule indique de ne pas l'appliquer à la colonne bleue, je pouvais donc ne faire la manip' qu'une seule fois, mais ce n'est pas possible,n 'est ce pas??
merci 1000 fois pour ton aide précieuse!!
virginie