Formule SI appliquée à une colonne entière
Résolu/Fermé
A voir également:
- Excel formule colonne entière
- Formule excel - Guide
- Déplacer une colonne excel - Guide
- Formule somme excel colonne - Guide
- Liste déroulante excel - Guide
- Formule moyenne excel - Guide
6 réponses
eriiic
Messages postés
24570
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
23 avril 2024
7 214
26 nov. 2011 à 16:25
26 nov. 2011 à 16:25
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
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 396
Modifié par Vaucluse le 26/11/2011 à 17:41
Modifié par Vaucluse le 26/11/2011 à 17:41
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
eriiic
Messages postés
24570
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
23 avril 2024
7 214
Modifié par eriiic le 27/11/2011 à 14:13
Modifié par eriiic le 27/11/2011 à 14:13
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
eriiic
Messages postés
24570
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
23 avril 2024
7 214
27 nov. 2011 à 17:36
27 nov. 2011 à 17:36
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
eriiic
Messages postés
24570
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
23 avril 2024
7 214
27 nov. 2011 à 20:02
27 nov. 2011 à 20:02
Fais exactement comme je te dis.
Si tu regardes bien le fichier que je t'ai fourni tu verras que j'ai mis les MFC jaunes et roses, et que la MFC bleue est toujours valable.
eric
Si tu regardes bien le fichier que je t'ai fourni tu verras que j'ai mis les MFC jaunes et roses, et que la MFC bleue est toujours valable.
eric
