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 Impliqué
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 Impliqué
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
271
sylvanu
sylvanu
M
  • Résolu(e)
XL 2021 Calcul en fonction valeur case,colonne <date & texte case,colonne
Réponses
5
Affichages
225
sylvanu
sylvanu
6
  • Question
XL 2019 Calcul à réaliser si une ou plusieurs cellules vides
Réponses
6
Affichages
663
vgendron
vgendron
K
  • Question
Microsoft 365 Mise en forme conditionnelle [Texte ET Date]
Réponses
5
Affichages
699
Cousinhub
Cousinhub
Usine à gaz
  • Résolu(e)
XL 2021 affectation et classement de mes appels selon contenu des cellules en colonnes J ou L ou M
2 3
Réponses
30
Affichages
951
Usine à gaz
Usine à gaz
J
  • Question
Microsoft 365 calcul occurences en fonction de plusieurs criteres
Réponses
9
Affichages
352
merinos
merinos
B
  • Question
XL 2013 Automatiser une formule sur les nouvelles lignes insérées en fonction d'une recherche sur les enregistrements
Réponses
9
Affichages
220
bedley
B
papacorno
  • Question
XL 2016 calcul age en an et moi entre deux dates fixes
Réponses
11
Affichages
504
Webperegrino
W
F
  • Résolu(e)
XL 2016 Fonction SI et DATEDIF combinée
Réponses
3
Affichages
497
ALS35
A
K
  • Résolu(e)
Microsoft 365 Formule de répartition du gain par mois, de 1 à 24 mois en fonction de nb de mois demandé
Réponses
6
Affichages
379
TooFatBoy
TooFatBoy

Membres actuellement en ligne

18 membres et 365 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
312 169
Messages
2 085 922
Membres
103 040
dernier inscrit
badi