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

66alex66 · 7 Juillet 2022

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.

job75 · 8 Juillet 2022

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 · 7 Juillet 2022

Bonjour 66alex66,

66alex66 à dit:
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 · 7 Juillet 2022

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 · 7 Juillet 2022

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)

job75 · 7 Juillet 2022

Notez que si l'on déclare Res As Double on obtient des résultats légèrement différents.

Bonne nuit.

66alex66 · 8 Juillet 2022

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 · 8 Juillet 2022

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

job75 · 8 Juillet 2022

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+

AtTheOne · 8 Juillet 2022

Bonjour @job75
On se croise souvent ce matin ...
Amicalement
Alain

66alex66 · 8 Juillet 2022

Mille mercis pour votre aide !
Ca fonctionne parfaitement

66alex66 · 8 Juillet 2022

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 · 8 Juillet 2022

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+

66alex66 · 8 Juillet 2022

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 · 8 Juillet 2022

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

job75 · 8 Juillet 2022

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.

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

66alex66

XLDnaute Nouveau

Pièces jointes

job75

job75

XLDnaute Barbatruc

66alex66

XLDnaute Nouveau

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

66alex66

XLDnaute Nouveau

AtTheOne

XLDnaute Accro

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

AtTheOne

XLDnaute Accro

66alex66

XLDnaute Nouveau

66alex66

XLDnaute Nouveau

job75

XLDnaute Barbatruc

Pièces jointes

66alex66

XLDnaute Nouveau

AtTheOne

XLDnaute Accro

Pièces jointes

job75

XLDnaute Barbatruc

Pièces jointes

Discussions similaires