Comment additionner correctement des nombres avec des unités dans une plage Excel ?
Lorsque vous travaillez avec Excel, vous pouvez rencontrer une situation où vous devez additionner des nombres qui incluent des unités, comme "5 kg", "12 lbs" ou "10 m". Cela est courant, par exemple, lors du suivi de poids, de longueurs ou de quantités - en particulier dans les listes d'achat, les mesures d’ingénierie, les registres de laboratoire et les données logistiques où chaque valeur est suivie d'une unité de mesure. Cependant, si vous essayez d’utiliser la formule SOMME standard d’Excel sur ces données, elle ne fonctionnera pas comme prévu. Cela est dû au fait qu’Excel interprète les valeurs contenant du texte (comme des unités) comme des chaînes de texte plutôt que comme des valeurs numériques, ce qui signifie que SOMME et d'autres fonctions mathématiques de base les ignorent ou renvoient des erreurs. Pour additionner correctement ces valeurs, il est nécessaire d'extraire la partie numérique avant d’effectuer le calcul. Ce tutoriel vous guidera à travers plusieurs méthodes pratiques et efficaces, y compris des formules, des fonctionnalités intégrées et des modules complémentaires comme Kutools pour Excel, afin de vous aider à additionner correctement des nombres avec des unités dans divers scénarios réels.
➤ Additionner des nombres avec des unités en utilisant une formule
➤ Additionner des nombres avec des unités en utilisant Kutools pour Excel
➤ Additionner des nombres selon la même unité avec Kutools pour Excel
➤ Combiner des lignes en double et additionner les nombres correspondants en utilisant Kutools pour Excel
➤ Macro VBA : Extraire des nombres de chaînes de texte avec des unités et additionner automatiquement
➤ Utiliser Power Query pour extraire et additionner des nombres avec des unités
Additionner des nombres avec des unités en utilisant une formule
Lorsque toutes vos valeurs partagent la même unité et que vous avez besoin d’une solution rapide basée sur une formule, les formules matricielles Excel peuvent vous aider à extraire la partie numérique et à calculer la somme. Cette méthode convient aux plages où chaque cellule contient un nombre immédiatement suivi de la même unité exacte, par exemple "8 kg", "15 kg", etc.
Sélectionnez une cellule vide pour votre résultat et entrez la formule suivante :
=SUM(SUBSTITUTE(B2:B7, "kg", "") +0)Après avoir tapé la formule, confirmez-la en appuyant simultanément sur Ctrl + Shift + Entrée (sur certains systèmes, cela est nécessaire pour les formules matricielles), afin qu'Excel la reconnaisse comme une opération matricielle. Vous verrez ensuite la somme des parties numériques dans la cellule sélectionnée.
Astuce : Dans la formule ci-dessus, B2:B7 fait référence à la plage de données contenant les valeurs avec des unités que vous souhaitez additionner, et "kg" est l'unité incluse dans vos données. Assurez-vous que toutes les entrées de la plage sont cohérentes en termes d'unité et qu'il n'y ait ni espaces supplémentaires ni différences d'orthographe, sinon la formule n'extraira pas correctement les valeurs. Si votre unité ou plage est différente, ajustez-les en conséquence. Cette formule ne gère pas les scénarios où plusieurs types d'unités sont présents dans la plage.
Additionner des nombres avec des unités en utilisant Kutools pour Excel
Pour les utilisateurs qui préfèrent une approche plus facile et visuelle ou qui souhaitent éviter de mémoriser des formules, Kutools pour Excel propose une méthode directe pour nettoyer les valeurs numériques. Sa fonction Supprimer des caractères spécifiques facilite la suppression des unités non numériques afin que vous puissiez additionner directement les nombres. Cela est idéal pour les situations où vos données contiennent des nombres et des unités dans une seule colonne et où vous devez rapidement rendre les nombres utilisables pour des calculs.
1. Mettez en surbrillance les cellules contenant les nombres avec des unités. Allez dans l'onglet Kutools, cliquez sur Texte, puis sélectionnez Supprimer des caractères spécifiques. Voir la capture d'écran :
2. Dans la boîte de dialogue Supprimer des caractères spécifiques, sélectionnez uniquement l'option Caractères non numériques. Ce paramètre supprime tous les caractères sauf les nombres. Voir la capture d'écran :
3. Cliquez sur OK ou Appliquer. Maintenant, seuls les contenus numériques restent dans vos cellules sélectionnées, que vous pouvez maintenant additionner avec une simple formule. Sélectionnez une cellule vide et saisissez la formule ci-dessous pour obtenir votre résultat :
=SUM(B2:B7)L'image ci-dessous montre à quoi ressemblent la colonne nettoyée et la somme après cette procédure.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
Remarque : Kutools effectue des remplacements directs dans les cellules sélectionnées, donc vous voudrez peut-être travailler sur une copie de vos données si vous devez conserver le texte original. Cette solution fonctionne mieux pour les unités de type unique. Après avoir utilisé cette fonction, assurez-vous que toutes les valeurs nettoyées sont numériques avant de faire la somme.
Additionner des nombres avec la même unité en utilisant Kutools pour Excel et la fonction SOMME
Additionner des nombres selon la même unité avec Kutools pour Excel
Si votre tableau contient des valeurs avec différentes unités - par exemple, des poids en "kg" et en "livres" - et que vous souhaitez additionner séparément pour chaque unité (comme obtenir les totaux en kg et en livres), Kutools propose une méthode efficace avec sa fonction Assistant de formule. Cela est utile pour les tableaux récapitulatifs, les rapports d'inventaire ou lors de la préparation des données pour une comparaison ultérieure.
Kutools pour Excel - Regorge de plus de 300 outils essentiels pour Excel. Profitez de fonctionnalités IA définitivement gratuites ! Téléchargez dès maintenant !
1. Choisissez la cellule où vous souhaitez que le résultat de la somme apparaisse. Accédez à Kutools > Assistant de formule > Statistique > Somme en fonction du même texte.
2. Dans la fenêtre d'assistance des formules, configurez votre calcul : sous Entrée d'arguments, choisissez la plage de données complète comme votre Plage, et tapez ou sélectionnez l'unité spécifique (comme "kg" ou "livres") dans la zone de texte. Ensuite, cliquez sur OK.
Astuce : La formule par défaut utilise des références absolues (par ex., $B$2:$B$7). Si vous prévoyez de remplir automatiquement cette fonction pour plusieurs unités ou lignes, modifiez manuellement les références en relatives si nécessaire.
Maintenant, votre cellule de résultat affiche le total pour l'unité choisie. Répétez pour chaque unité pour obtenir tous les totaux des groupes.
Remarque : Cette méthode fonctionne bien pour les tableaux avec des types d'unités clairement étiquetés et aide à automatiser les sommes multi-unités, minimisant ainsi les erreurs dues au regroupement manuel.
Combiner des lignes en double et additionner les nombres correspondants en utilisant Kutools pour Excel
Certains ensembles de données comportent des articles ou entités listés plusieurs fois avec des quantités associées et des unités, comme des fruits avec des entrées de poids dans différentes unités. Supposons que vous souhaitiez additionner les valeurs groupées par article et unité, fusionner les lignes en double et consolider le total. La fonction Avancée Fusionner des lignes de Kutools pour Excel est conçue précisément pour ce genre de scénarios, rendant l'agrégation rapide et simple.
1. Sélectionnez votre tableau contenant des articles en double et des unités mélangées, puis allez dans Kutools > Fusionner et Diviser > Avancée Fusionner des lignes.
2. Dans la boîte de dialogue Combiner des lignes basées sur la colonne, sélectionnez d'abord la colonne contenant les unités et marquez-la comme Clé primaire. Cela permet à Kutools de regrouper vos données par unité. Voir la capture d'écran :
3. Ensuite, mettez en surbrillance la colonne avec les valeurs numériques (comme "Poids"), puis sélectionnez Calculer et choisissez Somme pour ajouter ensemble les valeurs pour chaque groupe.
4. Cliquez sur OK et votre tableau résumera par unité, en combinant les nombres comme requis. Cela est particulièrement utile pour les données d'inventaire et de vente où plusieurs entrées doivent être agrégées en fonction de l'unité correspondante.
Kutools pour Excel - Boostez Excel avec plus de 300 outils essentiels. Profitez de fonctionnalités IA gratuites en permanence ! Obtenez-le maintenant
Astuce : Vous pouvez regrouper par plusieurs clés (par ex., article et unité) pour une consolidation plus approfondie.
Combiner des doublons et additionner des nombres
Macro VBA : Extraire des nombres de chaînes de texte avec des unités et additionner automatiquement
Pour les utilisateurs avancés à la recherche d'une automatisation complète ou travaillant avec des motifs complexes ou variés dans les chaînes d'unités, une macro VBA peut gérer l'extraction et l'addition en une seule étape. Cette méthode est particulièrement utile lorsque votre plage contient des nombres suivis de différentes unités ou structures de données incohérentes, et lorsque les formules Excel intégrées sont trop limitées ou encombrantes pour s'adapter à chaque cas.
1. Ouvrez l'Éditeur VBA en cliquant sur Développeur > Visual Basic. Dans la fenêtre Microsoft Visual Basic for Applications, cliquez sur Insertion > Module, puis collez le code suivant dans le module :
Sub SumNumbersWithUnits()
Dim cell As Range
Dim WorkRng As Range
Dim xTitleId As String
Dim NumSum As Double
Dim NumStr As String
Dim i As Long
Dim ch As String
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range with numbers and units", xTitleId, WorkRng.Address, Type:=8)
NumSum = 0
For Each cell In WorkRng
NumStr = ""
For i = 1 To Len(cell.Value)
ch = Mid(cell.Value, i, 1)
If ch Like "[0-9.]" Then
NumStr = NumStr & ch
ElseIf Len(NumStr) > 0 Then
Exit For
End If
Next i
If IsNumeric(NumStr) Then
NumSum = NumSum + Val(NumStr)
End If
Next cell
MsgBox "Sum of numbers is: " & NumSum, vbInformation, "Kutools for Excel"
End Sub2. Pour exécuter le code, cliquez n'importe où à l'intérieur de la macro et appuyez F5, ou cliquez sur le 
bouton Exécuter au-dessus de la fenêtre de l'éditeur de code. Une boîte de dialogue apparaîtra vous demandant de sélectionner la plage contenant des nombres avec des unités. Le script extraira automatiquement la valeur numérique principale de chaque cellule de texte et additionnera tous les résultats, puis affichera la somme dans une fenêtre contextuelle.
Conseils et notes d'utilisation :
- Cette macro extrait les caractères numériques depuis le début de chaque chaîne jusqu'à ce qu'un caractère non numérique soit trouvé, ce qui signifie qu'elle fonctionne sur la plupart des formats comme "5kg", "12 lbs" ou "20.5 m".
- Si le format des nombres est incohérent ou intégré dans la chaîne, des personnalisations supplémentaires peuvent être nécessaires.
- Vous pouvez modifier le code VBA pour collecter ou traiter les unités séparément si souhaité.
- Pour éviter toute perte de données originales, utilisez toujours des macros sur une copie ou sauvegarde de votre feuille de calcul.
- Requiert que la fonctionnalité des macros soit activée dans vos paramètres Excel.
Cette solution VBA est polyvalente lorsqu'il s'agit de variations dans vos données, et constitue un gain de temps considérable lorsqu'elle est appliquée à de grands ensembles de données.
Utiliser Power Query pour extraire et additionner des nombres avec des unités
Power Query, l'outil de transformation de données intégré dans Excel, est idéal pour traiter des ensembles de données plus volumineux ou plus complexes contenant des nombres avec des unités.
Il fonctionne particulièrement bien lorsque les structures de données sont incohérentes ou lorsqu'une automatisation est nécessaire pour des tâches récurrentes.
1. Sélectionnez la plage contenant vos données (y compris les nombres avec des unités), puis allez dans l'onglet Données et cliquez sur À partir du tableau/plage. Si vous êtes invité, assurez-vous que vos données ont des en-têtes.
2. Dans l'Éditeur Power Query, sélectionnez la colonne avec vos valeurs de texte. Utilisez l' onglet Ajouter une colonne > Extraire > Texte avant délimiteur ou Texte entre délimiteurs.
Choisissez en fonction du format de vos données :
- Utilisez un délimiteur espace si les données sont comme "12 kg"
- Utilisez un délimiteur basé sur des lettres si c'est "12kg"
3. Changez le type de données de la nouvelle colonne en Nombre décimal ou Nombre entier selon vos besoins en faisant un clic droit sur son en-tête et en sélectionnant Changer le type.
4. (Facultatif) Si vous souhaitez additionner par unité, divisez encore l'unité avec Extraire le texte après délimiteur, puis Grouper par (dans l'onglet Accueil) et sélectionnez Somme pour le nouveau champ numérique.
5. Cliquez sur Fermer & Charger pour importer les données traitées et les sommes directement dans Excel sous forme de nouvelle feuille ou table.
Avantages :
- Traite des données complexes, incohérentes ou massives de manière propre et rapide.
- Les étapes peuvent être enregistrées et réappliquées à des données futures ou mises à jour - rendant cette approche hautement reproductible.
- Vous pouvez personnaliser la logique de transformation au-delà de l'extraction simple, y compris la gestion des préfixes, suffixes, décimales et regroupements par différentes unités.
Limitations :
- Il existe une courbe d'apprentissage pour les utilisateurs novices avec Power Query.
- Meilleur appliqué à des données structurées ou lorsqu'on travaille avec des tableaux plutôt que des entrées ad hoc directement dans les cellules.
Dépannage et suggestions :
- Pour de meilleurs résultats, passez toujours en revue les nombres extraits ou convertis à la recherche d'anomalies, surtout si le format des unités ou des nombres varie grandement.
- Sauvegardez vos données sources avant toute action en masse (y compris avec Kutools et VBA).
- Lorsque vous utilisez des formules, vérifiez deux fois la présence d'espaces cachés ou de fautes de frappe dans les données et les unités.
- Si des formules échouent ou retournent des erreurs (telles que
#VALEUR!), vérifiez que toutes les cellules référencées contiennent les formats attendus, et ajustez la plage ou les critères d'unité en conséquence. - Lorsque vous utilisez VBA, assurez-vous que les macros sont activées, et enregistrez avant d'exécuter pour une récupération plus facile.
- Les étapes de transformation de Power Query peuvent être ajustées après un aperçu via le volet Étapes appliquées au cas où vos données auraient changé.
Articles connexes :
- Comment combiner plusieurs cellules avec des zéros initiaux en une seule cellule dans Excel ?
- Comment combiner des plages provenant de différentes feuilles de calcul/classeurs en un seul ?
- Comment fusionner rapidement des lignes en fonction d'une valeur de colonne puis effectuer des calculs dans Excel ?
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels