Comment combiner l'expérience de deux cellules en une seule

Pour les formules Excel vanille, c'est un peu cependant. En supposant que votre chaîne se trouve dans la cellule A2 , vos valeurs peuvent être calculées en utilisant la logique suivante:
Vous pouvez rechercher le premier nombre avec la formule suivante:

=INT(X1)&" Years " & ROUND((X1-INT(X1))*12;0) & " Months"

et vous pouvez rechercher la dernière chaîne avec la formule suivante:

=TRIM(MID($A2;MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};$A2&"0123456789"));2))*1+TRIM(MID($A2;MAX(ISNUMBER(VALUE(MID($A2;{1;2;3;4;5;6;7;8;9};1)))*{1;2;3;4;5;6;7;8;9})-1;2))/12

En supposant qu'aucun de vos candidats n'a travaillé plus de 99 ans, un TRIM est suffisant:

=TRIM(MID($A2;MAX(ISNUMBER(VALUE(MID($A2;{1;2;3;4;5;6;7;8;9};1)))*{1;2;3;4;5;6;7;8;9})-1;2))

ans et

=TRIM(MID($A2;MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"));2))*1

mois. Pour obtenir un seul nombre pouvant être additionné, vous stockez les années sous forme d'entiers et ajoutez les mois divisés par 12 en tant que partie non entière

=MAX(ISNUMBER(VALUE(MID(A2;{1;2;3;4;5;6;7;8;9};1)))*{1;2;3;4;5;6;7;8;9})

Ces nombres peuvent être additionnés et en résulteront dans 10.83333 . Afin d'obtenir votre chaîne à la fin, vous devrez utiliser une formule sur la somme (dans mon exemple de cellule X1 ), par exemple

=MIN(SEARCH({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))

Veuillez noter que vous devrez probablement formuler quelque chose d'encore plus complexe à moins que vos utilisateurs ne conservent des données comme 0 ans 6 mois pour des périodes inférieures à 1 an.

Avec une fonction définie par l'utilisateur, il est plus facile d'esquiver les inconvénients majeurs des longues formules vanille:

Function sumyearmonth(rng As Range) As String
Dim rng2 As Range, strArr() As String, runtotal As Double, outYear As Long, outMonth As Long

For Each rng2 In rng ' for each of the cells
    strArr = Split(rng2.Value2, " ") 'splits any sort of string by spaces
    For i = LBound(strArr) To UBound(strArr) ' for each element of the array
        Select Case True
            Case strArr(i) Like "*year*" Or strArr(i) Like "*Year*" 'if it says something like "year", "Year" or "Years"
                runtotal = runtotal + Val(strArr(i - 1)) 'then the running total gets incremented by the preceding item of the array, e.g. 12 (years)
            Case strArr(i) Like "*month*" Or strArr(i) Like "*Month*"  'if it says something like "month", "Month" or "Months"
                runtotal = runtotal + Val(strArr(i - 1)) / 12 'then the running total gets incremented by the preceding item of the array, e.g. 9 (months) /12
        End Select
    Next i
Next rng2
'now we just need to interpret the running total
outYear = Int(runtotal) 'years is the integer part of the running total
outMonth = Round((runtotal - outYear) * 12, 0) 'months is the noninteger part of the running total multiplied by 12 and rounded to the next integer
sumyearmonth = outYear & " Year" & IIf(outYear > 1, "s", "") 'if it's more than 1 year, then years
sumyearmonth = sumyearmonth & " " & outMonth & " Month" & IIf(outMonth > 1, "s", "") 'if it's more than 1 month, then months
End Function

Cette solution ne suppose pas que l'entrée se présente comme Année ET Mois, elle a un bon format de sortie [Année (s)] et vous n'avez pas besoin d'afficher un total cumulé à côté.

Supposons que le libellé des expériences soit toujours sous la forme x année (s) y mois , et que toutes les expériences soient répertoriées dans une colonne comme ci-dessous:

Supposons que vous ayez donné un nom à la liste ci-dessus en tant que List_Exp , vous pouvez utiliser le tableau suivant formule , qui doit être confirmée en appuyant sur Ctrl + Maj + Entrée dans la barre de formule, pour trouver le mois au total en premier:

=INT(Ttl_Mth/12)&" "&"Year"&IF(INT(Ttl_Mth/12)>1,"s","")&" "&MOD(Ttl_Mth,12)&" "&"Month"&IF(MOD(Ttl_Mth,12)>1,"s","")

Ici, j'ai utilisé les fonctions TRIM + MID + SUBSTITUTE + REPT pour extraire le chiffre de l'année et du mois de la chaîne d'origine, qui est {5,4; 4,9; 1,2 ; 1,1; 10,9} à partir de l'exemple ci-dessus, puis utilisez SUMPRODUCT pour calculer le nombre total de mois en recherchant les multiplicateurs correspondants {12,1; 12,1; 12,1; 12,1; 12,1} en utilisant les fonctions IF + ROW + INDEX .

Ensuite, vous pouvez convertir le Total des mois en Années et Mois comme indiqué ci-dessous:

Si vous ne souhaitez pas utiliser de cellules d'assistance (Cell D3: D6 ) dans l'exemple ci-dessus, vous pouvez donner un nom au Le nombre total de mois correspond à Ttl_Mth et combine les formules suivantes en une seule:

Chiffre pour les années: = INT (Ttl_Mth / 12)

Mot pour les années: ="Year"&IF(INT(Ttl_Mth/12)>1,"s","")

Chiffre pour les mois: =MOD(Ttl_Mth,12)

Mot pour les mois: = "Month" & IF (MOD (Ttl_Mth, 12)> 1, "s" , "")

Combiné:

=SUMPRODUCT(--TRIM(MID(SUBSTITUTE(List_Exp," ",REPT(" ",100)),{1,200},100)),IF(ROW($A$1:INDEX($A:$A,ROWS(List_Exp)))>0,{12,1}))

Veuillez voir ci-dessous un résultat de test pour une nouvelle liste de expériences:

[Hint] Je recommande de convertir la liste des expériences en tableau avant de donner un nom à la liste. L'avantage est que chaque fois que vous ajoutez ou remplacez des valeurs dans la liste (qui est en fait un tableau à une colonne), la plage nommée sera automatiquement mise à jour afin que les calculs soient également mis à jour automatiquement.