Discussion :

[Ayber]

Bonjour,

Je suis depuis quelques temps face à un problème épineux qui me met dans l'impasse, n'étant pas habitué à manipuler Excel.
J'ai beaucoup cherché sur internet, et rien de ce que j'ai trouvé ne m'a aidé. Mon problème étant assez spécifique, adapter des solutions données pour d'autres problèmes n'est vraiment pas top. C'est pourquoi je me tourne vers vous.

Je travaille sur Excel 2010, mais il faut que la solution soit applicable sur Excel 2007, d'où la version indiquée dans le préfixe. Il faudrait aussi qu'on puisse utiliser cette solution mensuellement, à chaque fois sur de nouvelles données (la structure reste bien sûr inchangée).

La personne qui se charge de corriger les données le faisait jusqu'à maintenant manuellement, ce qui prenait des jours et des jours de travail, le tableur actuel faisant par exemple 150 000 lignes. Comme je travaille sur les données d'une entreprise, je vais essayer d'être le plus clair possible sans joindre de captures d'écran.

En ce qui concerne mon problème, j'ai 3 colonnes :

idProduit

ticket_start

ticket_end

La colonne idProduit peut référencer dans ce cas là deux valeurs, 1 an ou 1 mois.

Les colonnes ticket_start et ticket_end référencent des dates de début et de fin de validité, sous la forme aaaammjjhhmmss. Par exemple, pour aujourd'hui : 20170126235959 (l'heure étant automatiquement indiquée comme 235959, indépendamment de la véritable horaire).

Hors, les données ne sont parfois pas précisées, ce qui fait que ces colonnes de date peuvent ne pas contenir une valeur valide, mais à la place indiquer "IndéfiniIndéfiniIndéfini235959".

Ce que j'aimerais pouvoir faire est :
- Repérer toutes les cellules contenant ces valeurs indéfinies.
- Selon l'id produit, et donc la durée de validité, éditer ces valeurs en fonction de la valeur dans ticket_start ou ticket_end.

Un exemple pour être clair :

idProduit	ticket_start	ticket_end
1 an	IndéfiniIndéfiniIndéfini235959	20190101235959
1 mois	IndéfiniIndéfiniIndéfini235959	20190201235959
1 an	20180101235959	IndéfiniIndéfiniIndéfini235959
1 mois	20180101235959	IndéfiniIndéfiniIndéfini235959

Pour la première ligne, la validité est d'un an et la fin de validité le 01/01/2019, il faudrait donc 20180101235959 en début de validité.
Pour la deuxième ligne, la validité est d'un mois et la fin de validité le 01/02/2018, il faudrait donc 20180101235959 en début de validité.

Pour la troisième ligne, la validité est d'un an et le début de validité le 01/01/2018, il faudrait donc 20190101235959 en fin de validité.
Pour la quatrième ligne, la validité est d'un mois et le début de validité le 01/01/2018, il faudrait donc 20180102235959 en fin de validité.


Désolé si je ne suis pas assez clair, j'ai conscience que ce que je demande est complexe, donc n'hésitez pas à demander si vous avez besoin de détails ou de précisions.

Merci d'avoir pris le temps de m'avoir lu, et merci d'avance si vous prenez le temps de m'aider.

[Menhir]

En supposant que les colonnes B et C ne peuvent pas être simultanément indéfinies pour la même ligne,
En supposant qu'il ne puisse y avoir rien d'autre en colonne A que "1 an" ou "1 mois",
En supposant que tes données commencent en ligne 1,

Mettre en D1 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(GAUCHE(B1;8)="Indéfini";TEXTE(MOIS.DECALER(DATE(GAUCHE(C1;1;4);STXT(C1;5;2);STXT(C1;7;2));SI($A1="1 an";-12;-1));"aaaammjj")&"235959";B1)

Mettre en E1 la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(GAUCHE(C1;8)="Indéfini";TEXTE(MOIS.DECALER(DATE(GAUCHE(B1;1;4);STXT(B1;5;2);STXT(B1;7;2));SI($A1="1 an";12;1));"aaaammjj")&"235959";C1)

Copier D1:E1 vers le bas autant que nécessaire.
Sélectionner les colonnes D:E > Ctrl+C
Sélectionner les colonnes B:C > Clic droit > Copier valeur

[Ayber]

Toutes mes excuses, j'aurais du être plus précis dès le début, en essayant de simplifier je t'ai induit en erreur.

Ma colonne idProduit ne contient pas en vérité "1 an" ou "1 mois", mais des id comme 246, 248, 322, 324, etc. ... Et ces id ont tous en commun qu'ils correspondent à une période de un mois ou un an.

Les données ne commencent pas en ligne 1 (ni en colonne A, d'ailleurs), mais pas de ce problème de ce côté-ci, je peux l'adapter sans difficulté.

Et il en peut en effet y avoir des lignes ou les deux champs de date sont indéfinis, cependant elles sont assez rares. Donc si je peux déjà avoir de quoi corriger toutes les lignes contenant au moins une date valide, ce serait un sacré bond en avant.
Bien sûr, si quelqu'un peut aussi m'aider pour ça, ce serait génial. La méthode de correction manuelle est de mettre une date de début prédéfinie pour toutes les lignes sans aucune date valide (1er janvier 2018, par exemple), et de mettre la date de fin correspondante selon la période de validité. Donc, pour cet exemple, 1er février 2018 ou 1er janvier 2019.

Enfin, par rapport à la fonction GAUCHE(), j'ai eu une erreur en utilisant ta syntaxe. Il semble que le nombre d'arguments doit être de 2 et non de 3. GAUCHE(C1; 4) au lieu de GAUCHE(C1; 1; 4), par exemple.


Voilà, encore désolé si je ne suis pas clair, ce n'est pas évident.
Et merci de m'accorder de ton temps.

[Menhir]

Et il en peut en effet y avoir des lignes ou les deux champs de date sont indéfinis.
Bien sûr, si quelqu'un peut aussi m'aider pour ça, ce serait génial.

Déterminer une période sans en connaitre ni le début ni la fin ?
Oui, ce sera peut-être possible quand Microsoft intègrera à Excel une fonction BOULE.DE.CRISTAL().

Ma colonne idProduit ne contient pas en vérité "1 an" ou "1 mois", mais des id comme 246, 248, 322, 324, etc. ... Et ces id ont tous en commun qu'ils correspondent à une période de un mois ou un an.

Tant que tu ne diras pas comment ce fait la relation entre ID produit et durée ...

[Ayber]

Déterminer une période sans en connaitre ni le début ni la fin ?

Comme je l'ai précisé, dans ce cas là on saisit une date prédéfinie pour le début (1er janvier 2018 par exemple). Pour la date de fin on la détermine grâce à la durée associée à l'id produit, qui lui est toujours indiqué.

Tant que tu ne diras pas comment ce fait la relation entre ID produit et durée ...

J'ai fait une une feuille recensant tous les id et la durée qui correspond, sous cette forme :

idProduit	Durée
1	1 an
2	1 mois
3	1 mois
4	1 an

[Menhir]

J'ai fait une une feuille recensant tous les id et la durée qui correspond, sous cette forme :

Dans ce cas, il suffit de remplacer les $A1 par des RECHERCHEV allant chercher dans cette liste la durée correspondante.