Dans la cellule B1, j'ai : moyenne(A1:A3). En tirant cette formule vers le bas, je voudrais obtenir la formule moyenne (A4:A6) et non moyenne (a2:a4).
Sur la ligne 2 :
=MOYENNE(INDIRECT("A"&LIGNE()*3-2&":A"&LIGNE()*3+1))
ou
=MOYENNE(DECALER(A1:A3;LIGNE()*3-3;0))
à tirer vers le bas sans modération.
=MOYENNE(INDIRECT("A"&LIGNE()*3-2&":A"&LIGNE()*3+1))
ou
=MOYENNE(DECALER(A1:A3;LIGNE()*3-3;0))
à tirer vers le bas sans modération.
Daniel Colardelle,
Ajouté ou modifié le 05/04/2008 (N°2003)
Ajouté ou modifié le 05/04/2008 (N°2003)
Je voudrais entrer en A1 une valeur et que celle ci s'ajoute à la valeur contenue dans B1.
Tu peux obtenir ce que tu souhaites (en fait additionner une cellule à elle même) en utilisant les références circulaires.
Il te faut pour cela, dans les options d'excel, autoriser le calcul itératif dans ton classeur et mettre à 1 ne nombre d'itérations.
(Dans excel 2007, c'est maintenant dans le bouton officel/formules/activer le calcul itératif, nombre d'itérations = 1)
dans ta cellule B1 tu tapes =A1+B1
en bas à gauche de la fenêtre, excel te signale que la cellule B1 contient une référence circulaire.
Il te faut pour cela, dans les options d'excel, autoriser le calcul itératif dans ton classeur et mettre à 1 ne nombre d'itérations.
(Dans excel 2007, c'est maintenant dans le bouton officel/formules/activer le calcul itératif, nombre d'itérations = 1)
dans ta cellule B1 tu tapes =A1+B1
en bas à gauche de la fenêtre, excel te signale que la cellule B1 contient une référence circulaire.
Flo Cabon,
Ajouté ou modifié le 05/01/2008 (N°1953)
Ajouté ou modifié le 05/01/2008 (N°1953)
En A1 j'ai 2, "+" en B1, 4 en C1. Comment afficher en D1 le résultat de l'expression =A1&B1&C1 ?
Sans VBA :
clique dans la cellule où tu veux voir s'afficher le résultat (par exemple en D1)
menu insertion/nom/définir : dans la zone de nom en haut tape MonCalcul
dans la zone de formule en bas, écris
MonCalcul=EVALUER(A1&B1&C1 )
Ensuite, dans D1, entre =moncalcul
Si tu veux faire cette opération sur une série d'opérations inscrites dans les colonnes A, B et C, il te suffit de tirer vers le bas la formule écrite en D1.
Avec VBA et une fonction personnalisée :
Dans un module ordinaire (pas un module de feuille)
Function zaza(Selection)
Dim i As Integer, srG As String
nBe = Selection.Count
srG = ""
For i = 1 To nBe
srG = srG & Selection.Item(i)
Next i
zaza= Evaluate("=" & srG)
End Function
il te suffit d'écrire en D1
=zaza(A1:C1)
clique dans la cellule où tu veux voir s'afficher le résultat (par exemple en D1)
menu insertion/nom/définir : dans la zone de nom en haut tape MonCalcul
dans la zone de formule en bas, écris
MonCalcul=EVALUER(A1&B1&C1 )
Ensuite, dans D1, entre =moncalcul
Si tu veux faire cette opération sur une série d'opérations inscrites dans les colonnes A, B et C, il te suffit de tirer vers le bas la formule écrite en D1.
Avec VBA et une fonction personnalisée :
Dans un module ordinaire (pas un module de feuille)
Function zaza(Selection)
Dim i As Integer, srG As String
nBe = Selection.Count
srG = ""
For i = 1 To nBe
srG = srG & Selection.Item(i)
Next i
zaza= Evaluate("=" & srG)
End Function
il te suffit d'écrire en D1
=zaza(A1:C1)
Serge Garneau, ChrisV,
Ajouté ou modifié le 11/11/2007 (N°1917)
Ajouté ou modifié le 11/11/2007 (N°1917)
Soit deux colonnes, l'une (A1:A10) contient des valeurs numériques, sans valeurs vides et sans code d'erreur. La seconde (B1:B10) contient des résultats de calculs calculs. Elle contient soit des valeurs numériques, soit des #N/A et je ne souhaite pas modifier la formule qui peut générer ce #N/A. Comment utiliser la fonction sommeprod dans ces conditions, par exemple pour additionner des valeurs de la colonne B en fonction d'un critère dans la colonne A ?
Concernant l'usage de SOMMEPROD
Il faut faire une distinction entre DÉNOMBRER ET SOMMER
Il est possible de dénombrer le nombre d'occurence d'une valeur dans la colonne A en utilisant sommeprod
=SOMMEPROD((NON(ESTNA(B1:B5))*(A1:A5=401)))
Mais il n'est pas possible d'additionner une colonne qui a une valeur d'erreur dans cette même colonne. Il faut se référer à la définition de la fonction SOMMEPROD = LA SOMME DES PRODUITS
En fin d'analyse, on se retrouve toujours dans la position de vouloir additionner une donnée "erreur" comme faisant partie de l'addition.
Des exemples faciles :
=Somme(1;2;#N/A;5) = #N/A
=Somme(1;2;#Valeur!;5) = #Valeur!
Solution : se passer de sommeprod et utiliser une matricielle...
=SOMME((A1:A10=4001)*SI(ESTNA(B1:B10);0;B1:B10))
Valider avec Maj+ctrl+entrée
s'il y a en plus du texte ou des #N/A en colonne B :
=SOMME((code=5004)*SI(ESTNA(montant)+(ESTTEXTE(montant));0;montant))
Valider avec Maj+ctrl+entrée
ou encore
=SOMME(SI(NON(ESTERREUR(B1:B5));B1:B5)*(A1:A5=401)) (si le critère à appliquer pour la colonne A est 401)
Valider avec Maj+ctrl+entrée
Il faut faire une distinction entre DÉNOMBRER ET SOMMER
Il est possible de dénombrer le nombre d'occurence d'une valeur dans la colonne A en utilisant sommeprod
=SOMMEPROD((NON(ESTNA(B1:B5))*(A1:A5=401)))
Mais il n'est pas possible d'additionner une colonne qui a une valeur d'erreur dans cette même colonne. Il faut se référer à la définition de la fonction SOMMEPROD = LA SOMME DES PRODUITS
En fin d'analyse, on se retrouve toujours dans la position de vouloir additionner une donnée "erreur" comme faisant partie de l'addition.
Des exemples faciles :
=Somme(1;2;#N/A;5) = #N/A
=Somme(1;2;#Valeur!;5) = #Valeur!
Solution : se passer de sommeprod et utiliser une matricielle...
=SOMME((A1:A10=4001)*SI(ESTNA(B1:B10);0;B1:B10))
Valider avec Maj+ctrl+entrée
s'il y a en plus du texte ou des #N/A en colonne B :
=SOMME((code=5004)*SI(ESTNA(montant)+(ESTTEXTE(montant));0;montant))
Valider avec Maj+ctrl+entrée
ou encore
=SOMME(SI(NON(ESTERREUR(B1:B5));B1:B5)*(A1:A5=401)) (si le critère à appliquer pour la colonne A est 401)
Valider avec Maj+ctrl+entrée
Denis Michon, Jacques Boisgontier,
Ajouté ou modifié le 25/08/2007 (N°1899)
Ajouté ou modifié le 25/08/2007 (N°1899)
En A1 j'ai une distance en km, en B1 le temps mis pour parcourir cette distance. Comment calculer la vitesse ?
Si tu saisis le temps au format décimal : 1 ou 1.0 ou 1,0 (suivant ton séparateur)
La formule est : =A1/B1
Si tu saisis le temps au format heure d'excel : 1:00, la formule est : =(A1/B1*24)
Défini un format personnalisé (Format/Cellule/Nombre/Personnalisé) Standard" km/h" de façon à afficher x km/h
La formule est : =A1/B1
Si tu saisis le temps au format heure d'excel : 1:00, la formule est : =(A1/B1*24)
Défini un format personnalisé (Format/Cellule/Nombre/Personnalisé) Standard" km/h" de façon à afficher x km/h
Flo Cabon,
Ajouté ou modifié le 12/08/2007 (N°1875)
Ajouté ou modifié le 12/08/2007 (N°1875)
Dans un tableau de données, comment, sans trier le tableau, déterminer
- les X premières valeurs ?
- les Y dernières valeurs ?
- quelle est la valeur qui a le rang Z ?
- quelles sont les valeurs, entre le minimum et le maximum qui n'ont pas été attribuées ?
Avec une plage nommée "Plage" (insertion/nom/définir) contenant des valeurs numériques.
Formules à recopier vers le bas :
les X premières valeurs :
=GRANDE.VALEUR(Plage;LIGNE())
les Y dernières valeurs :
=PETITE.VALEUR(Plage;LIGNE())
La valeur qui a le rang Z :
Avec Z = 5 :
=INDEX(Plage;EQUIV(GRANDE.VALEUR(Plage;5);Plage;0))
Les valeurs, entre le minimum et le maximum qui n'ont pas été attribuées :
avec la colonne B vide (à adapter) :
Sub zzz()
x = [Max(Plage)]: y = [Min(Plage)]: lg = 1
For i = y + 1 To x - 1
On Error Resume Next
test = [Plage].Find(i)
If Err.Number <> 0 Then
Cells(lg, 2) = i: lg = lg + 1
End If
Next
End Sub
Formules à recopier vers le bas :
les X premières valeurs :
=GRANDE.VALEUR(Plage;LIGNE())
les Y dernières valeurs :
=PETITE.VALEUR(Plage;LIGNE())
La valeur qui a le rang Z :
Avec Z = 5 :
=INDEX(Plage;EQUIV(GRANDE.VALEUR(Plage;5);Plage;0))
Les valeurs, entre le minimum et le maximum qui n'ont pas été attribuées :
avec la colonne B vide (à adapter) :
Sub zzz()
x = [Max(Plage)]: y = [Min(Plage)]: lg = 1
For i = y + 1 To x - 1
On Error Resume Next
test = [Plage].Find(i)
If Err.Number <> 0 Then
Cells(lg, 2) = i: lg = lg + 1
End If
Next
End Sub
Alain Vallon,
Ajouté ou modifié le 06/05/2005 (N°1622)
Ajouté ou modifié le 06/05/2005 (N°1622)
J'ai des tableaux de plusieurs colonnes et je cherche à identifier un élement qui réponde
à plusieurs critères en même temps.
Soit le tableau suivant
A B C
1 717 1013 600
2 717 2013 100
3 250 1013 300
Comment trouver dans la colonne C la valeur (ici 600) qui réponde à la fois aux deux
critères suivants :
A= 717 ET B= 1013 ?
Tape tes critères de recherche par exemple en E1 (717) et F1 (1013)
ou tu veux dans la feuille tape maintenant cette formule que tu valides en appuyant en
même temps sur ctrl+majuscules+enter (c'est une formule matricielle)
=INDEX(C1:C3;EQUIV(1;(E1=A1:A3)*(F1=B1:B3);0))
A B C
1 717 1013 600
2 717 2013 100
3 250 1013 300
Comment trouver dans la colonne C la valeur (ici 600) qui réponde à la fois aux deux
critères suivants :
A= 717 ET B= 1013 ?
Tape tes critères de recherche par exemple en E1 (717) et F1 (1013)
ou tu veux dans la feuille tape maintenant cette formule que tu valides en appuyant en
même temps sur ctrl+majuscules+enter (c'est une formule matricielle)
=INDEX(C1:C3;EQUIV(1;(E1=A1:A3)*(F1=B1:B3);0))
Alain Vallon, Daniel Maher,
Ajouté ou modifié le 05/05/2005 (N°1620)
Ajouté ou modifié le 05/05/2005 (N°1620)
Je définis dynamiquement des plages de données que j'utilise dans une fonction mais si je
supprime la première ligne, j'ai une erreur.
La fonction décaler est très pratique pour définir dynamiquement des plages de données.
Les noms ainsi définis peuvent être utilisés dans des fonctions (=somme(truc)) , pour
construire des graphiques se mettant à jour tout seul ou des listes de validations qui
s'adaptent automatiquement.
Sur la page lexique d'excelabo, tu trouveras une description
détaillée des différents arguments de cette fonction et de son utliisation très
puissante.
Si par exemple, tu veux faire référence dans une formule à l'ensemble des données qui
vont
de A1 à la dernière ligne remplie de cette colonne, en nommant truc par
insertion/nom/définir, fait référence à = decaler (feuil1!$1;;;nbval(feuil1!:)),
truc s'ajustera automatiquement. Mais si tu supprimes la ligne 1 (et donc la cellule A1),
dans ce cas, excel ne comprend plus rien. Pour éviter cela il faut ruser et définir
dynamiquement cette cellule A1 :
=DECALER(INDIRECT(ADRESSE(1;1));;;NBVAL(feuil1!:) -1)
pour une plage située en colonne C (3eme colonne) la formule deviendrai :
=DECALER(INDIRECT(ADRESSE(1;3));;;NBVAL(feuil1!C:C) -1 )
Les noms ainsi définis peuvent être utilisés dans des fonctions (=somme(truc)) , pour
construire des graphiques se mettant à jour tout seul ou des listes de validations qui
s'adaptent automatiquement.
Sur la page lexique d'excelabo, tu trouveras une description
détaillée des différents arguments de cette fonction et de son utliisation très
puissante.
Si par exemple, tu veux faire référence dans une formule à l'ensemble des données qui
vont
de A1 à la dernière ligne remplie de cette colonne, en nommant truc par
insertion/nom/définir, fait référence à = decaler (feuil1!$1;;;nbval(feuil1!:)),
truc s'ajustera automatiquement. Mais si tu supprimes la ligne 1 (et donc la cellule A1),
dans ce cas, excel ne comprend plus rien. Pour éviter cela il faut ruser et définir
dynamiquement cette cellule A1 :
=DECALER(INDIRECT(ADRESSE(1;1));;;NBVAL(feuil1!:) -1)
pour une plage située en colonne C (3eme colonne) la formule deviendrai :
=DECALER(INDIRECT(ADRESSE(1;3));;;NBVAL(feuil1!C:C) -1 )
Flo Cabon, GeeDee,
Ajouté ou modifié le 30/04/2005 (N°1618)
Ajouté ou modifié le 30/04/2005 (N°1618)
J'ai deux tableaux : le premier contient des références et des prix, le second (pas de la
même taille ni dans le même ordre) contient des désignations et des références. Comment
compléter le second tableau pour ajouter une colonne prix ?
Voici une autre utilisation de RechercheV ou de index/equiv.
--------A ------ B---------------E---------F---------------G
1------ ref1---prix------------ ref------ designation----prix ??
2------ AZ-----1----------------DF------ truc
3------ ZA-----3----------------FD------ machin
4------ DF---- 4 --------------- rt ------chose
5------ FD-----2--------------- az------ bidule
6--------------------------------ZA------ zaza
Par insertion/nom/définir, nomme "ref1" les cellules A2:A5 et prix B2:B5
En G2 tu peux écrire l'une ou l'autre de ces formules puis la tirer vers le bas:
=rechercheV(E2;A2:B5;2;0)
que tu peux traduire pour t'en souvenir par :
recherchev (maquestion; tableau de correspondance; colonne contenant la valeur à renvoyer; 0)
N'oublie pas : la colonne est indiquée par son N° d'ordre dans le tableau (ici 2) et non par le N° de la colonne sur la feuille excel. Le 0 en fin de formule est indispensable si tes données ne sont pas triées !
ou
=INDEX(prix;EQUIV(E2;ref;0);)
Ce qui présente l'avantage pour moi d'utiliser des noms définis (très utile quand les colonnes font des milliers de lignes..., sans compter que ces noms peuvent être définis en utilisant la formule
=décaler... (voir sur la page lexique pour l'utilisation) ce qui les rend dynamiques (ils restent valables même quand on ajoute des données à la suite des précédentes).
Le deuxième avantage c'est que tu peux placer cette formule dans une colonne située n'importe où par rapport à tes deux tableaux alors que recherchev t'impose que le résultat soit renvoyé à droite de la plage de recherche
=index(plage contenant la valeur à renvoyer;equiv(maquestion;plage contenant les références connues;0);)
Le dernier argument de RECHERCHEV() est un booléen.
Lorsque son évaluation donne 0 (ou FAUX), il fait une recherche exacte.
Lorsque son évaluation <>0 (ou VRAI), il fait une recherche dichotomique en considérant un plage triée ascendante.
Le dernier argument de EQUIV() est un nombre
S'il est 0, il fait une recherche exacte
S'il est positif, il fait une recherche dichotomique en considérant une plage triée ascendante.
S'il est négatif, il fait une recherche dichotomique en considérant une plage triée descendante.
Il y a donc un peu plus de possibilité avec EQUIV(). Avec un dernier argument négatif, on obtient des résultats différents.
Malgré cette légère possibilité additionnelle, la principale raison d'utiliser INDEX(EQUIV()) au lieu de RECHERCHEV() a plutôt directement rapport avec l'idée qu'avec INDEX(EQUIV()), on associe explicitement dans sa formule notre structuration des données, au lieu de fournir un nombre de colonnes de décalage.
Ce nombre pourrait être éventuellement mis-à-mal pour le déplacement d'une colonne dans ses données.
AMA, il faut privilégier la stabilité des résultats (des formules) et se prémunir contre des formules qui, comme RECHERCHEV(), soudainement se mettent à fournir d'autres résultats simplement parce qu'on ajoute/détruit/déplace des colonnes (sans changer les données dépendantes).
--------A ------ B---------------E---------F---------------G
1------ ref1---prix------------ ref------ designation----prix ??
2------ AZ-----1----------------DF------ truc
3------ ZA-----3----------------FD------ machin
4------ DF---- 4 --------------- rt ------chose
5------ FD-----2--------------- az------ bidule
6--------------------------------ZA------ zaza
Par insertion/nom/définir, nomme "ref1" les cellules A2:A5 et prix B2:B5
En G2 tu peux écrire l'une ou l'autre de ces formules puis la tirer vers le bas:
=rechercheV(E2;A2:B5;2;0)
que tu peux traduire pour t'en souvenir par :
recherchev (maquestion; tableau de correspondance; colonne contenant la valeur à renvoyer; 0)
N'oublie pas : la colonne est indiquée par son N° d'ordre dans le tableau (ici 2) et non par le N° de la colonne sur la feuille excel. Le 0 en fin de formule est indispensable si tes données ne sont pas triées !
ou
=INDEX(prix;EQUIV(E2;ref;0);)
Ce qui présente l'avantage pour moi d'utiliser des noms définis (très utile quand les colonnes font des milliers de lignes..., sans compter que ces noms peuvent être définis en utilisant la formule
=décaler... (voir sur la page lexique pour l'utilisation) ce qui les rend dynamiques (ils restent valables même quand on ajoute des données à la suite des précédentes).
Le deuxième avantage c'est que tu peux placer cette formule dans une colonne située n'importe où par rapport à tes deux tableaux alors que recherchev t'impose que le résultat soit renvoyé à droite de la plage de recherche
=index(plage contenant la valeur à renvoyer;equiv(maquestion;plage contenant les références connues;0);)
Le dernier argument de RECHERCHEV() est un booléen.
Lorsque son évaluation donne 0 (ou FAUX), il fait une recherche exacte.
Lorsque son évaluation <>0 (ou VRAI), il fait une recherche dichotomique en considérant un plage triée ascendante.
Le dernier argument de EQUIV() est un nombre
S'il est 0, il fait une recherche exacte
S'il est positif, il fait une recherche dichotomique en considérant une plage triée ascendante.
S'il est négatif, il fait une recherche dichotomique en considérant une plage triée descendante.
Il y a donc un peu plus de possibilité avec EQUIV(). Avec un dernier argument négatif, on obtient des résultats différents.
Malgré cette légère possibilité additionnelle, la principale raison d'utiliser INDEX(EQUIV()) au lieu de RECHERCHEV() a plutôt directement rapport avec l'idée qu'avec INDEX(EQUIV()), on associe explicitement dans sa formule notre structuration des données, au lieu de fournir un nombre de colonnes de décalage.
Ce nombre pourrait être éventuellement mis-à-mal pour le déplacement d'une colonne dans ses données.
AMA, il faut privilégier la stabilité des résultats (des formules) et se prémunir contre des formules qui, comme RECHERCHEV(), soudainement se mettent à fournir d'autres résultats simplement parce qu'on ajoute/détruit/déplace des colonnes (sans changer les données dépendantes).
Daniel Maher, Flo Cabon,
Ajouté ou modifié le 05/03/2005 (N°1584)
Ajouté ou modifié le 05/03/2005 (N°1584)
J'ai besoin de faire des opérations sur des nombres comportant plus de 15 chiffres
significatifs avec excel. Or quand j'entre ces nombres, excel les arrondit et les met en
notation scientifique ce qui ne me convient pas.
Ci-dessous un code exemple montrant qu'il est possible d'effectuer
des opérations sur des nombres comportant plus de 15 chiffres
sans perdre la précision. Vous pouvez l'adapter selon vos modes
de calcul.
1) Tout d'abord il faut saisir vos nombres avec une quote ( ' ) devant
pour permettre leur visualisation et éviter la perte de la précision.
2) On utilise le type Decimal pour convertir la chaîne de caractères
immanquablement obtenue par la quote. Le type Decimal permet
d 'obtenir des nombres sur 12 octets soit environ des nombres
au delà des 27 chiffres. Comme figurant dans le code exemple,
on ne peut déclaré immédiatement le type Decimal par
Dim x As Decimal (pas bon)
Il faut passer par un variant puis le convertir au moyen de CDec soit
Dim x As Variant
x = CDec(x)
3) Dans le code il y une Sub (donc sans valeur de retour) qui affiche par
MsgBox le résultat du triple et du quart du nombre sélectionné.
Il y a aussi des fonctions permettant les opérations de base sur
les nombres de plus de 15 chiffres. A noter que la valeur de retour est
une chaîne de caractères pour que le nombre puisse apparaître
complet, au niveau de la précison, dans la cellule de réception.
Les fonctions ont des arguments Range. Il faut par conséquent leur
passer des paramètres Range.
Exemple pour utiliser la fonction DECMultiplier:
Entrez en A1 la valeur '12345678901234567890 (ne pas oublier la quote)
Entrez en B1 le multiplicateur soit la valeur 17 (par exemple)
Entrez en C1 la formule =DECMultiplier(A1;B1)
Le résultat '209876541320987654130 s'affichera en C1
Si #VALEUR! s'affiche il y a un dépassement de capacité.
'*************************
Option Explicit
'_____________________________
Function DECDiviser(Cellule1 As Range, _
Cellule2 As Range) As String
Dim x As Variant
x = CDec(Cellule1) / CDec(Cellule2)
DECDiviser = CStr(x)
End Function
'_____________________________
Function DECMultiplier(Cellule1 As Range, _
Cellule2 As Range) As String
Dim x As Variant
x = CDec(Cellule1) * CDec(Cellule2)
DECMultiplier = CStr(x)
End Function
'_____________________________
Function DECSomme(Plage As Range) As String
Dim C As Range
Dim x As Variant
Dim i&
For Each C In Plage
If Not IsEmpty(C) And _
IsNumeric(C) Then
x = x + CDec(C)
End If
Next C
DECSomme = CStr(x)
End Function
'_____________________________
Sub OperationsBigNumber()
Dim x As Variant
x = ActiveCell
If IsEmpty(x) Or _
Not IsNumeric(x) Then Exit Sub
x = CDec(x)
MsgBox "le triple de " & x & " est: " & x * 3
MsgBox "le quart de " & x & " est: " & x / 4
End Sub
'*************************
des opérations sur des nombres comportant plus de 15 chiffres
sans perdre la précision. Vous pouvez l'adapter selon vos modes
de calcul.
1) Tout d'abord il faut saisir vos nombres avec une quote ( ' ) devant
pour permettre leur visualisation et éviter la perte de la précision.
2) On utilise le type Decimal pour convertir la chaîne de caractères
immanquablement obtenue par la quote. Le type Decimal permet
d 'obtenir des nombres sur 12 octets soit environ des nombres
au delà des 27 chiffres. Comme figurant dans le code exemple,
on ne peut déclaré immédiatement le type Decimal par
Dim x As Decimal (pas bon)
Il faut passer par un variant puis le convertir au moyen de CDec soit
Dim x As Variant
x = CDec(x)
3) Dans le code il y une Sub (donc sans valeur de retour) qui affiche par
MsgBox le résultat du triple et du quart du nombre sélectionné.
Il y a aussi des fonctions permettant les opérations de base sur
les nombres de plus de 15 chiffres. A noter que la valeur de retour est
une chaîne de caractères pour que le nombre puisse apparaître
complet, au niveau de la précison, dans la cellule de réception.
Les fonctions ont des arguments Range. Il faut par conséquent leur
passer des paramètres Range.
Exemple pour utiliser la fonction DECMultiplier:
Entrez en A1 la valeur '12345678901234567890 (ne pas oublier la quote)
Entrez en B1 le multiplicateur soit la valeur 17 (par exemple)
Entrez en C1 la formule =DECMultiplier(A1;B1)
Le résultat '209876541320987654130 s'affichera en C1
Si #VALEUR! s'affiche il y a un dépassement de capacité.
'*************************
Option Explicit
'_____________________________
Function DECDiviser(Cellule1 As Range, _
Cellule2 As Range) As String
Dim x As Variant
x = CDec(Cellule1) / CDec(Cellule2)
DECDiviser = CStr(x)
End Function
'_____________________________
Function DECMultiplier(Cellule1 As Range, _
Cellule2 As Range) As String
Dim x As Variant
x = CDec(Cellule1) * CDec(Cellule2)
DECMultiplier = CStr(x)
End Function
'_____________________________
Function DECSomme(Plage As Range) As String
Dim C As Range
Dim x As Variant
Dim i&
For Each C In Plage
If Not IsEmpty(C) And _
IsNumeric(C) Then
x = x + CDec(C)
End If
Next C
DECSomme = CStr(x)
End Function
'_____________________________
Sub OperationsBigNumber()
Dim x As Variant
x = ActiveCell
If IsEmpty(x) Or _
Not IsNumeric(x) Then Exit Sub
x = CDec(x)
MsgBox "le triple de " & x & " est: " & x * 3
MsgBox "le quart de " & x & " est: " & x / 4
End Sub
'*************************
Patrick Morange,
Ajouté ou modifié le 18/12/2004 (N°1541)
Ajouté ou modifié le 18/12/2004 (N°1541)
Je cherche un moyen de calculer la clef des numéros de sécurité sociale.
si le numéro de sécu est en A1,
la clé est égale à
97-($1-97*ENT($1/97))
(sauf s'il y a des lettres, Corse...)
la clé est égale à
97-($1-97*ENT($1/97))
(sauf s'il y a des lettres, Corse...)
Denis,
Ajouté ou modifié le 24/10/2004 (N°1504)
Ajouté ou modifié le 24/10/2004 (N°1504)
Comment incrémenter de 2 le numéro de ligne de la référence à une cellule
Exemple: =B2
=B4
=B6
B2 étant sur la ligne 1, =B4 étant sur la ligne 2 etc.
un truc que j'utilise est de placer le caractère « devant le signe =
«=B2
«=B4
sélectionne ces deux cellules et tire la poignée vers le bas
il reste à faire un rechercher de « et remplacer tout par ""
«=B2
«=B4
sélectionne ces deux cellules et tire la poignée vers le bas
il reste à faire un rechercher de « et remplacer tout par ""
Isabelle,
Ajouté ou modifié le 06/08/2004 (N°1471)
Ajouté ou modifié le 06/08/2004 (N°1471)
Je travaille sur un tableau comprenant 31 feuilles dont je doit faire la moyenne dans un
tableau récapitulatif. Or la fonction MOYENNE est limité à 30 arguments. Y a t'il une
formule ou une astuce pour le faire directement?
Essaie une formule 3D :
=MOYENNE(Feuil1:Feuil30!A1:A10)
Avec des noms de feuilles comprenant des espaces :
=MOYENNE('Ma 1ère feuille:Ma 30ème feuille'!A1:A10)
A la souris :
=MOYENNE(
+ sélection de la plage sur la 1ère feuille
+ appuyer sur la touche MAJ
+ clic sur l'onglet de la dernière feuille
+ validation de la formule
Une autre solution possible, par formule matricielle, dans l'hypothèse où :
- Tes 30 feuilles portent le même nom suivi d'un numéro, par exemple "Tableau
1", "Tableau 2",... "Tableau 30"
- La plage sur ces 30 feuilles dont tu veux faire la moyenne comporte une seule
colonne (c'est également adaptable à une seule ligne).
Exemple avec la moyenne des plages A1:A99 des feuilles "Tab1" à "Tab30" :
=SOMME(SI(SOUS.TOTAL(2;INDIRECT("'Tab"&COLONNE(INDIRECT("C1:C30";0))&"'!A"&
LIGNE(INDIRECT("1:99"))));N(INDIRECT("'Tab"&COLONNE(INDIRECT("C1:C30";0))&"'!A"
&LIGNE(INDIRECT("1:99"))))))/NB('Tab1:Tab30'!A1:A99)
... à valider par Ctrl-Maj-Entrée.
Le 30 de "C30" représente le numéro de la dernière feuille à inclure, "'!A" =
lettre de colonne de la plage dont il faut faire la moyenne, "1:99" = numéros de
ligne de la plage.
Cette formule exclut du calcul les celles vides ou contenant #N/A.
Autre solution avec la macro complémentaire
http://longre.free.fr/pages/telecharge/#Morefun:
=MOYENNE(SI(ESTNUM(TAB3D('Tab1:Tab30'!A1:A9));TAB3D('Tab1:Tab30'!A1:A9)))
... toujours à valider avec Ctrl-Maj-Entrée.
=MOYENNE(Feuil1:Feuil30!A1:A10)
Avec des noms de feuilles comprenant des espaces :
=MOYENNE('Ma 1ère feuille:Ma 30ème feuille'!A1:A10)
A la souris :
=MOYENNE(
+ sélection de la plage sur la 1ère feuille
+ appuyer sur la touche MAJ
+ clic sur l'onglet de la dernière feuille
+ validation de la formule
Une autre solution possible, par formule matricielle, dans l'hypothèse où :
- Tes 30 feuilles portent le même nom suivi d'un numéro, par exemple "Tableau
1", "Tableau 2",... "Tableau 30"
- La plage sur ces 30 feuilles dont tu veux faire la moyenne comporte une seule
colonne (c'est également adaptable à une seule ligne).
Exemple avec la moyenne des plages A1:A99 des feuilles "Tab1" à "Tab30" :
=SOMME(SI(SOUS.TOTAL(2;INDIRECT("'Tab"&COLONNE(INDIRECT("C1:C30";0))&"'!A"&
LIGNE(INDIRECT("1:99"))));N(INDIRECT("'Tab"&COLONNE(INDIRECT("C1:C30";0))&"'!A"
&LIGNE(INDIRECT("1:99"))))))/NB('Tab1:Tab30'!A1:A99)
... à valider par Ctrl-Maj-Entrée.
Le 30 de "C30" représente le numéro de la dernière feuille à inclure, "'!A" =
lettre de colonne de la plage dont il faut faire la moyenne, "1:99" = numéros de
ligne de la plage.
Cette formule exclut du calcul les celles vides ou contenant #N/A.
Autre solution avec la macro complémentaire
http://longre.free.fr/pages/telecharge/#Morefun:
=MOYENNE(SI(ESTNUM(TAB3D('Tab1:Tab30'!A1:A9));TAB3D('Tab1:Tab30'!A1:A9)))
... toujours à valider avec Ctrl-Maj-Entrée.
Laurent Longre,
Ajouté ou modifié le 06/08/2004 (N°1466)
Ajouté ou modifié le 06/08/2004 (N°1466)
Quelle formule utiliser pour convertir de l'hexadécimal en binaire et réciproquement ?
Tu peux utiliser la formule générique de changement de base qui est dispo
dans morefun que tu peux télécharger href="http://longre.free.fr">ici
L'avantage par rapport à HEXBIN ou BINHEX ?
Une limite à 52 (ou 53) caractères et non 16.
Très pratique pour convertir en UNE FOIS 0AB8E5CD en binaire.
dans morefun que tu peux télécharger href="http://longre.free.fr">ici
L'avantage par rapport à HEXBIN ou BINHEX ?
Une limite à 52 (ou 53) caractères et non 16.
Très pratique pour convertir en UNE FOIS 0AB8E5CD en binaire.
Laurent Longre,
Ajouté ou modifié le 02/05/2004 (N°1377)
Ajouté ou modifié le 02/05/2004 (N°1377)
Je dois faire la moyenne des plages A1:A20 de l'ensemble de mes 31 feuilles. Comment faire
sachant que le nombre d'arguments dans la fonction moyenne est limité à 30 ?
Essaie une formule 3D :
=MOYENNE(Feuil1:Feuil30!A1:A20)
Avec des noms de feuilles comprenant des espaces :
=MOYENNE('Ma 1ère feuille:Ma 30ème feuille'!A1:A20)
A la souris :
=MOYENNE(
+ sélection de la plage sur la 1ère feuille
+ appuyer sur la touche MAJ
+ clic sur l'onglet de la dernière feuille
+ validation de la formule
=MOYENNE(Feuil1:Feuil30!A1:A20)
Avec des noms de feuilles comprenant des espaces :
=MOYENNE('Ma 1ère feuille:Ma 30ème feuille'!A1:A20)
A la souris :
=MOYENNE(
+ sélection de la plage sur la 1ère feuille
+ appuyer sur la touche MAJ
+ clic sur l'onglet de la dernière feuille
+ validation de la formule
Laurent Longre,
Ajouté ou modifié le 02/05/2004 (N°1371)
Ajouté ou modifié le 02/05/2004 (N°1371)
Je voudrais faire un tableau styles "distances entre deux villes". ? J'ai en colonne
A une ville de départ, en colonne B la ville d'arrivée, en C la distance. Comment mettre les
villes en colonne A et en ligne 1 et inscrire la distance à l'instersection ?
En supposant que dans le tableau 1, les plages soient nommées (pour cela,
Insertion, Nom, Créer, Ligne du dessus).
La fonction (non matricielle)
suivante remplit le tableau. Attention, dans
le tableau d'arrivée, il faut décaler Angers su la première ligne.
La recopie de la formule envoie 0 pour les
intersections des villes qui sont en
même temps départ et arrivée.
=SOMMEPROD((=Depart)*(B$1=Arrivee)*prix)
Attention, en cas de modif d'un tarif
c'est tout le tableau qui est recalculé...
Insertion, Nom, Créer, Ligne du dessus).
La fonction (non matricielle)
suivante remplit le tableau. Attention, dans
le tableau d'arrivée, il faut décaler Angers su la première ligne.
La recopie de la formule envoie 0 pour les
intersections des villes qui sont en
même temps départ et arrivée.
=SOMMEPROD((=Depart)*(B$1=Arrivee)*prix)
Attention, en cas de modif d'un tarif
c'est tout le tableau qui est recalculé...
Pierre Fauconnier, (N°863)
J'ai à calculer en DU5 la somme de une cellule sur trois de B5 À DR5. Donc, quelle est la
formule à inscrire dans DU5 pour avoir la sommation de B5+E5+H5+K5+....DR5 sans avoir à les
nommer à tour de rôle ?
"=SOMMEPROD((MOD(COLONNE(B5:DR5)-5;3)=0)*B5:DR5)
Alain Vallon,
Ajouté ou modifié le 16/04/2004 (N°861)
Ajouté ou modifié le 16/04/2004 (N°861)
Comment répondre aux deux questions suivantes 1) Si je place 1000€ pendant 5 ans au taux fixe
annuel de 6%, quelle somme récupérerai-je à la fin ? 2) Quel serait le taux annuel fixe pour
que 1000€ placés pendant 5 ans me rapportent (intérêt + capital) 2000€ ?
Toutes les fonctions existent sous Excel pour les calculs que tu veux faire,
pour les besoins de la cause il est possible d'avoir accès à des fonctions
supplémentaires en installant l'utilitaire d'analyse dans Outils/Macro
complémentaires/Utilitaire d'analyse.
Première question.
Ce que tu cherches correspond à la valeur future (dans 5 ans) d'un
investissement fait aujourd'hui. Dans Excel cela correspond à la fonction
Vc..., Le taux est de 6% = 6/100 = 0.06
Taux 0.06
Npm 5
Vpm 0
Va -1000
Type 0
Vc 1338.23
La valeur de ton investissement dans 5 ans sera donc de 1338.23, mais ce
calcul suppose que les intérêts sont versés une fois par année, ce qui est
le cas par exemple si tu prêtes le 1000 à un ami et qu'il te paies les
intérêts à la fin de chaque année (avec un peu de chance!!)
Toutefois le même investissement pourraît être fait mais en demandant de
recevoir les intérêt à la fin de chaque mois, le rendement sera alors
meilleurs car tu fera des intérêts sur les intérêts, en autant que les
intérêts demeurrent dans le fond d'investissement, ils seront alors composés.
Le taux est alors de 6%/12 = 0.005, mais il est calculé 60 fois en 5 ans (5 x 12)
Taux 0.005
Npm 60
Vpm 0
Va -1000
Type 0
Vc 1348.85
Le fait de recevoir les intérêts à la fin de chaque mois plutôt qu'une fois
par année aura donc rapporté 10.62 = 1348.85 - 1338.23.
Question N° 2.
La fonction Excel qui permet de récupérer le taux d'intérêt porte le même
nom "Taux".
Npm 5
Vpm 0
Va -1000
Type 0
Vc 2000
Taux 14.8698%
Ici encore on suppose que le paiement des intérêts se fait une fois par
année.
Npm 60
Vpm 0
Va -1000
Type 0
Vc 2000
Taux 1.161944%
Le taux que excel affiche est le taux de la période ici le mois pour avoir
le Taux annuel il faut le multiplier par 12.
1.161944 x 12 = 13.94%. Ce taux est inférieur au taux précédent car les
intérêts rapportent d'avantage rappelons-nous qu'ils sont composés
mensuellement dans cet exemple.
pour les besoins de la cause il est possible d'avoir accès à des fonctions
supplémentaires en installant l'utilitaire d'analyse dans Outils/Macro
complémentaires/Utilitaire d'analyse.
Première question.
Ce que tu cherches correspond à la valeur future (dans 5 ans) d'un
investissement fait aujourd'hui. Dans Excel cela correspond à la fonction
Vc..., Le taux est de 6% = 6/100 = 0.06
Taux 0.06
Npm 5
Vpm 0
Va -1000
Type 0
Vc 1338.23
La valeur de ton investissement dans 5 ans sera donc de 1338.23, mais ce
calcul suppose que les intérêts sont versés une fois par année, ce qui est
le cas par exemple si tu prêtes le 1000 à un ami et qu'il te paies les
intérêts à la fin de chaque année (avec un peu de chance!!)
Toutefois le même investissement pourraît être fait mais en demandant de
recevoir les intérêt à la fin de chaque mois, le rendement sera alors
meilleurs car tu fera des intérêts sur les intérêts, en autant que les
intérêts demeurrent dans le fond d'investissement, ils seront alors composés.
Le taux est alors de 6%/12 = 0.005, mais il est calculé 60 fois en 5 ans (5 x 12)
Taux 0.005
Npm 60
Vpm 0
Va -1000
Type 0
Vc 1348.85
Le fait de recevoir les intérêts à la fin de chaque mois plutôt qu'une fois
par année aura donc rapporté 10.62 = 1348.85 - 1338.23.
Question N° 2.
La fonction Excel qui permet de récupérer le taux d'intérêt porte le même
nom "Taux".
Npm 5
Vpm 0
Va -1000
Type 0
Vc 2000
Taux 14.8698%
Ici encore on suppose que le paiement des intérêts se fait une fois par
année.
Npm 60
Vpm 0
Va -1000
Type 0
Vc 2000
Taux 1.161944%
Le taux que excel affiche est le taux de la période ici le mois pour avoir
le Taux annuel il faut le multiplier par 12.
1.161944 x 12 = 13.94%. Ce taux est inférieur au taux précédent car les
intérêts rapportent d'avantage rappelons-nous qu'ils sont composés
mensuellement dans cet exemple.
Syl,
Ajouté ou modifié le 16/04/2004 (N°860)
Ajouté ou modifié le 16/04/2004 (N°860)
Comment afficher les 5 valeurs les plus petites d'une colonne de chiffres ?
Avec une plage (verticale) de données nommée ici Zn.
A saisir sans les { } et valider par Ctrl+Shift+Entrée, sur une plage de 5
cellules adjacentes verticales
{=PETITE.VALEUR(Zn;{1;2;3;4;5})
ou, pour un résultat que tu voudrais "horizontal"
(toujours sur une plage de 5 cellules, mais horizontales cette fois-ci)
{=TRANSPOSE(PETITE.VALEUR(Zn;{1;2;3;4;5}))}
A saisir sans les { } et valider par Ctrl+Shift+Entrée, sur une plage de 5
cellules adjacentes verticales
{=PETITE.VALEUR(Zn;{1;2;3;4;5})
ou, pour un résultat que tu voudrais "horizontal"
(toujours sur une plage de 5 cellules, mais horizontales cette fois-ci)
{=TRANSPOSE(PETITE.VALEUR(Zn;{1;2;3;4;5}))}
ChrisV, (N°859)
J'ai dans une colonne un millier de données chiffrées et je cherche à retrouver les 5 plus
grands valeur sans passer par un filtre. Je cherche une fonction qui me permettrait de
sélectionner le 2 ème plus grand chiffre dans une liste, le 3ème , etc.;;
Si ta plage de recherche est A1:A20
Sélection de B1:B5 >
=GRANDE.VALEUR(A1:A20;{1;2;3;4;5})
Attention c'est une formule matricielle,
Validation par Ctr+Maj+Entrée
Sélection de B1:B5 >
=GRANDE.VALEUR(A1:A20;{1;2;3;4;5})
Attention c'est une formule matricielle,
Validation par Ctr+Maj+Entrée
Alain Vallon, (N°858)
Comment résoudre le système de 3 équations à 3 inconnues suivant :
2x + 23 y + 8z = 5
10x + 6 y + 90z = 6
4x + 7 y + 12z = 7
écrit sous forme matricielle:
zone1 zone2
| 2 23 8 | |x| |5|
|10 6 90 | |y| |6|
| 4 7 12 | |z| |7|
entrez la fonction suivante dans une plage 3*3 :
=INVERSEMAT(zone1)
et validez en appuyant simultanément sur les touches Ctrl Shift Enter
(appelons cette matrice "zone3")
sélectionnez une plage d'une colonne * 3 lignes et entrez la fonction
suivante:
=PRODUITMAT(zone3;zone2) suivi de Ctrl Shift Enter
zone1 zone2
| 2 23 8 | |x| |5|
|10 6 90 | |y| |6|
| 4 7 12 | |z| |7|
entrez la fonction suivante dans une plage 3*3 :
=INVERSEMAT(zone1)
et validez en appuyant simultanément sur les touches Ctrl Shift Enter
(appelons cette matrice "zone3")
sélectionnez une plage d'une colonne * 3 lignes et entrez la fonction
suivante:
=PRODUITMAT(zone3;zone2) suivi de Ctrl Shift Enter
Christian Herbé, (N°857)
Comment retrouver la clé RIB à partir d'un numéro de compte bancaire ?
Avec codB (code banque), codG (code guichet), et numC (numéro de compte)
=MOD(97-(89*MOD(codB;97)+15*MOD(codG;97)+3*MOD(numC;97));97)
Si c'est un compte bancaire belge, la clé ( les deux derniers chiffres)
représente le modulo 97 des 10 premiers.
Si c'est pour une vérification du numéro dans une cellule, tu peux donc
utiliser la fonction
=MOD(Numéro;97)
Cela vaut également pour les numéros VCS ( Virements à communication
structurée)
Les numéros de tva se vérifient d'une manière similaire
=97-MOD(1234567;97) où 1234567 représente les 7 premiers chiffres du n°.
=MOD(97-(89*MOD(codB;97)+15*MOD(codG;97)+3*MOD(numC;97));97)
Si c'est un compte bancaire belge, la clé ( les deux derniers chiffres)
représente le modulo 97 des 10 premiers.
Si c'est pour une vérification du numéro dans une cellule, tu peux donc
utiliser la fonction
=MOD(Numéro;97)
Cela vaut également pour les numéros VCS ( Virements à communication
structurée)
Les numéros de tva se vérifient d'une manière similaire
=97-MOD(1234567;97) où 1234567 représente les 7 premiers chiffres du n°.
Pierre Fauconnier, Ricky, ChrisV, (N°856)
Je voudrais faire la moyenne des 10 valeurs les plus petites dans une colonne. Il faudrait que
quand j'ajoute une valeur, la moyenne soit éventuellement corrigée.
Si tes données sont sur la feuil1 dans la colonne A avec une étiquette en A1,
nomme les (insertion/nom/définir) ListeV par la formule =decaler(feuil1!$2;;;nbval(:)-1).
De cette façon, toute valeur ajoutée sous les autres dans cette colonne sera prose en compte
et utilise la formule
=SI(NBVAL(ListeV)>=10;MOYENNE(PETITE.VALEUR(ListeV;{1.2.3.4.5.6.7.8.9.10}));"")
La formule suivante ne tient pas compte des cellules vides pour établir la moyenne,
mais pour obtenir un résultat, il te faut au moins 10 valeurs .
{1.2.3.4.5.6.7.8.9.10} , remplace le point par , ou / ou ; ou
=SI(NBVAL(ListeV)>=10;MOYENNE(PETITE.VALEUR(ListeV;{1.2.3.4.5.6.7.8.9.10}));MOYENNE(ListeV))
nomme les (insertion/nom/définir) ListeV par la formule =decaler(feuil1!$2;;;nbval(:)-1).
De cette façon, toute valeur ajoutée sous les autres dans cette colonne sera prose en compte
et utilise la formule
=SI(NBVAL(ListeV)>=10;MOYENNE(PETITE.VALEUR(ListeV;{1.2.3.4.5.6.7.8.9.10}));"")
La formule suivante ne tient pas compte des cellules vides pour établir la moyenne,
mais pour obtenir un résultat, il te faut au moins 10 valeurs .
{1.2.3.4.5.6.7.8.9.10} , remplace le point par , ou / ou ; ou
=SI(NBVAL(ListeV)>=10;MOYENNE(PETITE.VALEUR(ListeV;{1.2.3.4.5.6.7.8.9.10}));MOYENNE(ListeV))
Denis Michon,
Ajouté ou modifié le 25/10/2003 (N°855)
Ajouté ou modifié le 25/10/2003 (N°855)