[Calc] Quelle astuce pour traduire des "départements" en "régions" ?

[Enzolo]

Sur un grand nombre de lignes de données, où le n° de département figure en colonne A, je souhaiterais attribuer à chaque ligne la région correspondante.
La solution que je sais faire consiste à ajouter une colonne, avec dans chaque cellule une longue formule du type

= SI ( A1=1 ; “Auvergne-Rhône-Alpes” ; SI (A1=2 ; “Hauts-de-France” ; SI ( A1=3 ; … )))))))))))))

C’est un peu fastidieux et ça utilise beaucoup de mémoire…

J’imagine que par exemple en créant un tableau de correspondances entre n° de département et noms de région, on doit pouvoir simplifier la formule, mais je ne sais pas quelles fonctions utiliser, ni où chercher le mode d’emploi, du coup. Quelqu’un aurait une piste ?

Merci d’avance !

[njhub]

Bonjour Enzolo,
Si vos régions sont classées comme suit :

• Auvergne-Rhône-Alpes 1
  Bourgogne-Franche-Comté 2
  Bretagne 3
  Centre-Val de Loire 4
  Corse 5
  Grand Est 6
  Hauts-de-France 7
  Île-de-France 8
  Normandie 9
  Nouvelle-Aquitaine 10
  Occitanie 11
  Pays de la Loire 12
  Provence-Alpes-Côte d’Azur 13
  Guadeloupe 14
  Martinique 15
  Guyane 16
  La Réunion 17
  Mayotte 18

Utilisez la fonction choisir :

=CHOISIR(A1;“Auvergne-Rhône-Alpes”;“Bourgogne-Franche-Comté”;“Bretagne”;“Centre-Val de Loire”;“Corse”;“Grand Est”;“Hauts-de-France”;“Île-de-France”;“Normandie”;“Nouvelle-Aquitaine”;“Occitanie”;“Pays de la Loire”;“Provence-Alpes-Côte d’Azur”;“Guadeloupe”;“Martinique”;“Guyane”;“La Réunion”;“Mayotte”)

Bonjour Enzolo,

Une formule de 1199 caractères…

=SI(NBCAR(A102)>2;CHOISIR(DROITE(A102;1);"Guadeloupe";"Martinique";"Guyane";"La Réunion";"";"Mayotte");SI(OU(A102=75;A102=77;A102=78;A102=91;A102=92;A102=93;A102=94;A102=95);"Île-de-France";SI(OU(A102=18;A102=28;A102=36;A102=37;A102=41;A102=45);"Centre-Val de Loire";SI(OU(A102=21;A102=25;A102=39;A102=58;A102=70;A102=71;A102=89;A102=90);"Bourgogne-Franche-Comté";SI(OU(A102=14;A102=27;A102=50;A102=61;A102=76);"Normandie";SI(OU(A102=2;A102=59;A102=60;A102=62;A102=80);"Hauts-de-France";SI(OU(A102=8;A102=10;A102=51;A102=52;A102=54;A102=55;A102=57;A102=67;A102=68;A102=88);"Grand Est";SI(OU(A102=44;A102=49;A102=53;A102=72;A102=85);"Pays de la Loire";SI(OU(A102=22;A102=29;A102=35;A102=56);"Bretagne";SI(OU(A102=16;A102=17;A102=19;A102=23;A102=24;A102=33;A102=40;A102=47;A102=64;A102=79;A102=86;A102=87);"Nouvelle-Aquitaine";SI(OU(A102=9;A102=11;A102=12;A102=30;A102=31;A102=32;A102=34;A102=46;A102=48;A102=65;A102=66;A102=81;A102=82);"Occitanie";SI(OU(A102=1;A102=3;A102=7;A102=15;A102=26;A102=38;A102=42;A102=43;A102=63;A102=69;A102=73;A102=74);"Auvergne-Rhône-Alpes";SI(OU(A102=4;A102=5;A102=6;A102=13;A102=83;A102=84);"Provence-Alpes-Côte d'Azur";SI(OU(A102="2A";A102="2B");"Corse"))))))))))))))

une autre avec seulement 1145 caractères…

=SI(OU(A102="2A";A102="2B");"Corse";SI(NBCAR(A102)>2;CHOISIR(DROITE(A102;1);"Guadeloupe";"Martinique";"Guyane";"La Réunion";"";"Mayotte");SI(OU(TEXTE(A102;"##")={"75";"77";"78";"91";"92";"93";"94";"95"});"Île-de-France";SI(OU(TEXTE(A102;"##")={"18";"28";"36";"37";"41";"45"});"Centre-Val de Loire";SI(OU(TEXTE(A102;"##")={"21";"25";"39";"58";"70";"71";"89";"90"});"Bourgogne-Franche-Comté";SI(OU(TEXTE(A102;"##")={"14";"27";"50";"61";"76"});"Normandie";SI(OU(TEXTE(A102;"##")={"2";"59";"60";"62";"80"});"Hauts-de-France";SI(OU(TEXTE(A102;"##")={"8";"10";"51";"52";"54";"55";"57";"67";"68";"88"});"Grand Est";SI(OU(TEXTE(A102;"##")={"44";"49";"53";"72";"85"});"Pays de la Loire";SI(OU(TEXTE(A102;"##")={"22";"29";"35";"56"});"Bretagne";SI(OU(TEXTE(A102;"##")={"16";"17";"19";"23";"24";"33";"40";"47";"64";"79";"86";"87"});"Nouvelle-Aquitaine";SI(OU(TEXTE(A102;"##")={"9";"11";"12";"30";"31";"32";"34";"46";"48";"65";"66";"81";"82"});"Occitanie";SI(OU(TEXTE(A102;"##")={"1";"3";"7";"15";"26";"38";"42";"43";"63";"69";"73";"74"});"Auvergne-Rhône-Alpes";SI(OU(TEXTE(A102;"##")={"4";"5";"6";"13";"83";"84"});"Provence-Alpes-Côte d'Azur"))))))))))))))

[Enzolo]

Grand merci !

[Enzolo]

Magnifique !

[Enzolo]

Ah zut.
En fait, la suggestion de Njhub ne répond pas exactement à ma question.

Il s’agit de faire correspondre les numéros de département à leur région correspondante :

• 1 = Ain → Auvergne-Rhône-Alpes
• 2 = Aisne → Hauts de France
• 3 = Allier → Auvergne-Rhône-Alpes
• 4 = Alpes de Haute Provence → PACA
• 5 = Hautes Alpes → PACA
  … mais aussi des numéros atypiques :
• 2A = Corse du Sud → Corse
• 973 = Guyane → Guyane

Je vois bien une solution consistant à créer :

• une colonne intermédiaire U pour transformer les numéros atypiques en nombres consécutifs, avec un

SI ( A1 = “2A” ; 20 ; SI ( A1 = “2B” ; 20 ; SI ( A1 = 971 ; 96 ; … ; A1 )))))))

• puis la colonne que je cherche, avec

CHOISIR (U1 ; “Auvergne-Rhône-Alpes” ; “Hauts de France” ; " Auverge-Rhône-Alpes" ; “PACA” ; “PACA” etc. jusqu’au 101ème département)

Mais si une astuce permettait d’attribuer à une cellule une valeur (nom de région) à partir d’un tableau de correspondances, je suis intéressé

[Didier_Excel_libO]

departements-francais.odsBonjour,

Tout d’abord la ressources des données département région est téléchargeable sur texte du lien

Ensuite pour ce que vous voulez faire il faut imbriquer les fonctions EQUIV et INDEX:
Dans un onglet “Base” (le fichier téléchargé) Col A =NUMÉRO DPT; Col B=NOM DPT; Col C=REGION
La 1ère colonne est nommée “N0DPT”, La base de Col A à Col C est Nommée “BaseDpt”
Dans l’onglet où vous souhaitez effectuer le lien N° DPT/ Région
Colonne A = N° de Département (dont 2A et 2B et 971 à 976)
Colonne B = Région : la formule de recherche dans la base sera en B2 << =INDEX(BaseDpt;EQUIV(A2;N0Dpt ;0);3)>>
Explication :
EQUIV(valeur, Matrice à 1 seule colonne, 0=correspondance exacte) renvoie le N° de ligne dans la matrice N0Dpt (cad la 1ère colonne de l’onglet “Base”) qui est utilisé par la fonction INDEX;
INDEX (Matrice, N°de Ligne; N° de Colonne)
Matrice = “BaseDpt”
N°de Ligne = EQUIV(A2;N0Dpt ;0) (O pour correspondance exacte)
N° de colonne = 3 cad la colonne C

Voir le fichier joint LibreOfice
Cordialement

[Enzolo]

Merci infiniment !