Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2019 Calcul à réaliser en fonction d'une ou plusieurs cellules vides

66alex66

XLDnaute Nouveau
Bonjour,

J'aimerais qu'une formule donnée s'appliquer en fonction d'une ou plusieurs cellule vides.
La formule permettant de calculer le nombre d'unités est déjà créée et présente dans la cellule C5 (voir fichier annexé). Elle calcule le nombre d'unités présent entre deux dates sur base d'un tableau de référence (colonne M) repris ci-dessous :



Voici le rendu que j'aimerais obtenir :



J'aimerais que cette formule d'addition d'unités, intègre dans la colonne C, le nombre d'unités repris entre deux dates situées de part et d'autre des cellules vides.

Dans le cas présent, la somme d'unités entre le 01/06/2019 et le 01/01/2021 est de 3366,8584.
J'aimerais donc que cette somme s'intègre tant dans la cellule C5 que C6.

Ensuite, pour les dates allant du 01/01/2021 au 07/04/2022, la somme d'unité est de 3476,23462 devrait s'intégrer en cellule C8.

La formule de calcule est créée, mais je ne trouve pas la condition me permettant de générer ce résultat...

D'avance un tout grand merci.
 

Pièces jointes

  • calcul-unite.xlsm
    17.2 KB · Affichages: 11
Solution
Bon si l'on tient à éviter les divisions par zéro ça se passe ici, fichier (4) :
VB:
ElseIf choix = 3 Then
    If colRef(c.Row) <> "" Then Res = colRef(c.Row) Else If c(1, -2) = 0 Then Res = colRef(deb) Else Res = c(1, 0) * c(1, -1) / c(1, -2) + colRef(deb)
ElseIf choix = 4 Then
    If colRef(c.Row) = "" Then CalculUnités = "=SI(C" & c.Row & "=0;B" & deb & ";E" & c.Row & "*D" & c.Row & "/C" & c.Row & "+B" & deb & ")"
    Exit Function
Bonne nuit.

job75

XLDnaute Barbatruc
Bonjour 66alex66,
Dans le cas présent, la somme d'unités entre le 01/06/2019 et le 01/01/2021 est de 3366,8584.

Ensuite, pour les dates allant du 01/01/2021 au 07/04/2022, la somme d'unité est de 3476,23462
Le calcul du fichier donne respectivement 3352,75244 et 3449,34058...

29 jours pour février n'est valable que pour 2020.

Enfin la validation matricielle de la formule est inutile.

A+
 

66alex66

XLDnaute Nouveau
En effet, le mois de février ne comporte pas toujours 29 jours.
Mais ce calcul sert d’estimation.
Dès lors, la variation des jours du mois de février n’est pas importante.

La formule donne les unités des dates situées de part et d’autre des cellules vides.

Il est donc normal d’obtenir 3366 pour les deux premières cellules vides et 3476 pour la suivante.

L’idée serait d’appliquer cette formule dès qu’une cellule vide est détectée.
Je n’y parviens pas…

Merci d’avance !
 

job75

XLDnaute Barbatruc
Voyez le fichier joint et la fonction VBA revue et complétée :
VB:
Function CalculUnités(colRef As Range, colDate As Range, ProdJourMois As Range) As Variant
Dim c As Range, derlig&, deb&, fin&, j&, Res As Single
Set c = Application.Caller
derlig = colRef(colRef.Rows.Count).End(xlUp).Row
CalculUnités = ""

If c.Row = 1 Or c.Row > derlig Or colRef(c.Row) <> "" Then Exit Function

For deb = c.Row - 1 To 1 Step -1
    If colRef(deb) <> "" Then Exit For
Next deb
If deb = 0 Then Exit Function

For fin = c.Row + 1 To derlig
    If colRef(fin) <> "" Then Exit For
Next fin
If fin > derlig Then Exit Function

For j = colDate(deb) + 1 To colDate(fin)
    Res = Res + ProdJourMois(Month(j))
Next j
CalculUnités = Res
End Function
Formule en C1 à tirer vers le bas :
Code:
=CalculUnités(B:B;A:A;O$2:O$13)
 

Pièces jointes

  • calcul-unite.xlsm
    18.8 KB · Affichages: 5

66alex66

XLDnaute Nouveau
MILLE mercis !!
Ca fonctionne parfaitement !

Est-ce qu'il serait possible d'adapter légèrement la formule, mais cette fois pour la colonne D.

La variante serait celle-ci :
Dès qu'une cellule vide est détectée dans la colonne B, calculer le nombre d'unités à partir de la première date située avant la cellule vide :

Donc du 01/06/2019 au 01/09/2020 ensuite du 01/06/2019 au 24/09/2020 et ainsi de suite.

Voici le rendu que j'aimerai obtenir :



Merci pour votre aide !
 

AtTheOne

XLDnaute Accro
Supporter XLD
Bonjour à toutes & à tous, bonjour @66alex66, @job75
Pour le fun sans modifier ta fonction initiale avec 2 formules matricielles (à valider par la combinaison CTRL MAJ ENTREE)
En C1 et à tirer vers le bas
Enrichi (BBcode):
=SI(B1="";CalculUnités(INDEX($A$1:$A$10;MAX(SI(B$1:B1<>"";LIGNE(B$1:B1);0)))+1;INDEX($A$1:$A$10;MIN(SI(B1:B$10<>"";LIGNE(B1:B$10);"")));$O$2:$O$13);"")

en D1 en à tirer vers le bas
Enrichi (BBcode):
=SI(B1="";CalculUnités(INDEX($A$1:$A$10;MAX(SI(B$1:B1<>"";LIGNE(B$1:B1);0)))+1;A1;$O$2:$O$13);"")
Voir le fichier joint (attention issu de EXCEL 2021 donc avec formules matricielles par défaut)

Amicalement
Alain
 

Pièces jointes

  • calcul-unite.xlsm
    18.2 KB · Affichages: 2
Dernière édition:

job75

XLDnaute Barbatruc
Bonjour 66alex66, AtTheOne,

Voyez le fichier joint et cette fonction avec l'argument optionnel choix utilisé en colonne D :
VB:
Function CalculUnités(colRef As Range, colDate As Range, ProdJourMois As Range, Optional choix As Boolean) As Variant
Dim c As Range, derlig&, deb&, fin&, j&, Res As Single
Set c = Application.Caller
derlig = colRef(colRef.Rows.Count).End(xlUp).Row
CalculUnités = ""

If c.Row = 1 Or c.Row > derlig Or colRef(c.Row) <> "" Then Exit Function

For deb = c.Row - 1 To 1 Step -1
    If colRef(deb) <> "" Then Exit For
Next deb
If deb = 0 Then Exit Function

If choix Then
    fin = c.Row
Else
    For fin = c.Row + 1 To derlig
        If colRef(fin) <> "" Then Exit For
    Next fin
    If fin > derlig Then Exit Function
End If

For j = colDate(deb) + 1 To colDate(fin)
    Res = Res + ProdJourMois(Month(j))
Next j
CalculUnités = Res
End Function
A+
 

Pièces jointes

  • calcul-unite(1).xlsm
    19.3 KB · Affichages: 4

66alex66

XLDnaute Nouveau
Une ultime demande d'aide pour que mon tableau soit complet à 100%...
Je vous remercie encore pour votre aide généreuse...

Est-il possible d'obtenir ce résultat en colonne E ?

Indiquer le delta des index de la colonne B situés de part et d'autre des cellules vides.

Donc B7-B4 en cellule E5 et E6
B9-B7 en cellule E8...

 

job75

XLDnaute Barbatruc
Fichier (2) avec la fonction VBA :
VB:
Function CalculUnités(colRef As Range, colDate As Range, ProdJourMois As Range, Optional choix As Byte) As Variant
'choix = 0 pour  la colonne C, choix = 1 pour la colonne D, choix = 2 pour la colonne E
Dim c As Range, derlig&, deb&, fin&, j&, Res As Single
Set c = Application.Caller
derlig = colRef(colRef.Rows.Count).End(xlUp).Row
CalculUnités = ""

If c.Row = 1 Or c.Row > derlig Or colRef(c.Row) <> "" Then Exit Function

For deb = c.Row - 1 To 1 Step -1
    If colRef(deb) <> "" Then Exit For
Next deb
If deb = 0 Then Exit Function

If choix = 1 Then
    fin = c.Row
Else
    For fin = c.Row + 1 To derlig
        If colRef(fin) <> "" Then Exit For
    Next fin
    If fin > derlig Then Exit Function
End If

If choix = 2 Then
    Res = colRef(fin) - colRef(deb)
Else
    For j = colDate(deb) + 1 To colDate(fin)
        Res = Res + ProdJourMois(Month(j))
    Next j
End If
CalculUnités = Res
End Function
Formule en C2 à tirer à droite et vers le bas :
Code:
=CalculUnités($B:$B;$A:$A;$O$2:$O$13;COLONNE()-3)
A+
 

Pièces jointes

  • calcul-unite(2).xlsm
    19.5 KB · Affichages: 4

66alex66

XLDnaute Nouveau
Un tout grand merci ! Cet outil va m'aider énormément !!

Je ne voudrais pas abuser de votre temps... vous m'avez déjà bien aidé...
Je me demandais s'il était possible d'obtenir ce résultat dans la colonne F ?



Un recopiage de la colonne B lorsqu'elle n'est pas vide.
Et introduire le calcul décrit dans la colonne G

Dans tous les cas, merci beaucoup pour l'aide apportée !
 

AtTheOne

XLDnaute Accro
Supporter XLD
Re,
Je persiste avec les formules (Matricielles ou non)

En F1 la formule =B1
A partir de B2 la formule suivante à tirer vers le bas
Enrichi (BBcode):
=SI(B2="";E2*(D2/C2)+B1;B2)

(En colonne E la formule matricielle
Code:
SI(B1="";INDEX($B$1:$B$10;MIN(SI(B1:B$10<>"";LIGNE(B1:B$10);"")))-INDEX($B$1:$B$10;MAX(SI(B$1:B1<>"";LIGNE(B$1:B1);0)));"")
à tirer vers le bas)

amicalement
Alain
 

Pièces jointes

  • calcul-unite.xlsm
    18.5 KB · Affichages: 5

job75

XLDnaute Barbatruc
Fichier (3) avec les condition supplémentaires pour les colonnes F et G :
VB:
Function CalculUnités(colRef As Range, colDate As Range, ProdJourMois As Range, Optional choix As Byte) As Variant
'choix = 0 pour  colonne C, choix = 1 pour colonne D, choix = 2 pour colonne E, choix = 3 pour colonne F, choix = 4 pour colonne G
Dim c As Range, derlig&, deb&, fin&, Res As Double, j&
Set c = Application.Caller
derlig = colRef(colRef.Rows.Count).End(xlUp).Row
CalculUnités = ""

If c.Row = 1 And choix <> 3 Or c.Row > derlig Or colRef(c.Row) <> "" And choix <> 3 Then Exit Function

For deb = c.Row - 1 To 1 Step -1
    If colRef(deb) <> "" Then Exit For
Next deb
If deb = 0 And choix <> 3 Then Exit Function

If choix = 1 Then
    fin = c.Row
ElseIf choix < 3 Then
    For fin = c.Row + 1 To derlig
        If colRef(fin) <> "" Then Exit For
    Next fin
    If fin > derlig Then Exit Function
End If

If choix = 2 Then
    Res = colRef(fin) - colRef(deb)
ElseIf choix = 3 Then
    If colRef(c.Row) = "" Then Res = c(1, 0) * c(1, -1) / c(1, -2) + colRef(deb) Else Res = colRef(c.Row)
ElseIf choix = 4 Then
    If colRef(c.Row) = "" Then CalculUnités = "=E" & c.Row & "*D" & c.Row & "/C" & c.Row & "+B" & deb
    Exit Function
Else
    For j = colDate(deb) + 1 To colDate(fin)
        Res = Res + ProdJourMois(Month(j))
    Next j
End If
CalculUnités = Res
End Function
Pour que les résultats en colonne F soient exactement ceux des formule Excel j'ai déclaré Res As Double.
 

Pièces jointes

  • calcul-unite(3).xlsm
    20.8 KB · Affichages: 4

Discussions similaires

Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…