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

6

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 :

FINN2.PNG


Voici le rendu que j'aimerais obtenir :

FINN.PNG


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
job75
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.
Trier par date Trier par votes
job75

job75

XLDnaute Barbatruc
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
Cliquez pour agrandir...
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+
 
Vote positif 0 Vote négatif
6

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 !
 
Vote positif 0 Vote négatif
job75

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
Vote positif 0 Vote négatif
6

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 :

FINN3.PNG


Merci pour votre aide !
 
Vote positif 0 Vote négatif
AtTheOne

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:
Vote positif 0 Vote négatif
job75

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
Vote positif 0 Vote négatif
6

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...

FINN4.PNG
 
Vote positif 0 Vote négatif
job75

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
Vote positif 0 Vote négatif
6

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 ?

FINN5.PNG


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 !
 
Vote positif 0 Vote négatif
AtTheOne

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
Vote positif 0 Vote négatif
job75

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
Vote positif 0 Vote négatif

Discussions similaires

M
  • Question
Autres Nombre cellules vides par mois
Réponses
3
Affichages
524
sylvanu
sylvanu
G
  • Question
XL 2016 Comment concaténer plusieurs dates (provenant d'une formule) de différentes cellules en une seule cellule?
Réponses
4
Affichages
213
JHA
J
M
  • Question
Microsoft 365 Indice de révision de chaque ligne automatiquement
Réponses
2
Affichages
175
MLIZIN
M
M
  • Résolu(e)
XL 2021 Calcul en fonction valeur case,colonne <date & texte case,colonne
Réponses
5
Affichages
420
sylvanu
sylvanu
Fipat
  • Question
Microsoft 365 Calcul de moyenne avec prise en compte de dates
Réponses
9
Affichages
280
Fipat
Fipat
6
  • Question
XL 2019 Calcul à réaliser si une ou plusieurs cellules vides
Réponses
6
Affichages
887
vgendron
vgendron
K
  • Question
Microsoft 365 Mise en forme conditionnelle [Texte ET Date]
Réponses
5
Affichages
952
Cousinhub
Cousinhub
P
  • Résolu(e)
XL 2021 Besoin d'aide pour copier/coller données de plusieurs classeurs
2
Réponses
20
Affichages
677
Pac41240
P
T
  • Question
XL 2013 Colonne dynamique en fonction de choix d'une liste déroulante
Réponses
5
Affichages
579
JHA
J
M
  • Question
XL 2021 Créer une liste d'informations en colonne dans des cellules différentes
Réponses
3
Affichages
432
djidji59430
djidji59430

Membres actuellement en ligne

14 membres et 67 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
315 096
Messages
2 116 179
Membres
112 677
dernier inscrit
Justine11