Une moyenne pondérée

    
 function calculerMoyenne(){
      var laCelluleBcle
      var laZone
      var laFeuille = SpreadsheetApp.getActiveSheet();
      var laCellule = laFeuille.getRange("A2");
      laCellule.setValue ="Moyenne pond.";
     //la cellule A3 contiendra le nb de ligne contenant des noms d'élèves donc le nombre d'eleves
     //=NBVAL($C:$C)
      LaCellule = laFeuille.getRange("A3");
      laFormule = "=COUNTA($C:$C)";
      laCellule.setFormula(laFormule);
      nbEleves = laCellule.getValue();
      //Calcul du nb de devoirs donnés
      //Bcler car peut être absence à un devoir
      var nbDevoirs = 0;
      LaCellule = laFeuille.getRange("A"+ (nbEleves + 2));
      for ( i = 4;   i < (nbEleves +3) ; i++) {    
            laFormule = "=COUNT(OFFSET($"+i+":$"+ i+";0;3;1;COUNTA($"+i+ ":$"+i+")))";
            laCellule.setFormula(laFormule);
            var nbNoteI=laCellule.getValue()
            if (nbDevoirs < nbNoteI) {
              nbDevoirs = nbNoteI
            }
      }
      // lesCoef est la plage D3:G3 si 4 devoirs
      var lesCoef = "OFFSET(D3;0;0;1;"+nbDevoirs+")";
      for ( i = 4;   i < (nbEleves +3) ; i++) {
        // lesNotes est par exemple la plage D7:G7 (eleve n° 5)
        var lesNotes = "OFFSET(D"+ i + ";0;0;1;"+nbDevoirs+")";
            //la moyenne     
        var leNum ="SUM(IF(ISNUMBER("+lesCoef+");IF(ISNUMBER("+lesNotes+");"+lesNotes+"*"+lesCoef+";0);"+lesNotes+"))";
        var leDeno = "SUM(IF(ISNUMBER("+lesNotes+");IF(ISNUMBER("+lesCoef+");"+lesCoef+";1);0))";
        laFormule = "=ARRAYFORMULA(" + leNum + "/" + leDeno + ")"
            var laCelluleBcle = SpreadsheetApp.getActiveSheet().getRange( i , 1 );
            laCelluleBcle.setFormula(laFormule);
      }
}                                   

 Sub calculerMoyenne()
    Dim laCellule As Object, laCelluleBcle As Object, laPlage As Object
    Dim laNote As Double, nbNoteI As Double
    Dim nbEleves As Integer, nbDevoirs As Integer
    Dim i As Integer
    Dim nomCel As String, laFormule As String
    Dim lesCoef As Object, lesNotes As Object
    Dim leNum As String, leDeno As String
    '
    Set laCellule = ActiveSheet.Range("A2")
    laCellule.Value = "Moyenne pond."
    '
    'le nombre d'élèves
    '=NBVAL($C:$C)- 1
    Set laPlage = ActiveSheet.Range("$C:$C")
    nbEleves = WorksheetFunction.CountA(laPlage) - 1
    '
    Set laCellule = ActiveSheet.Range("A1")
    nbDevoirs = 0
    For i = 3 To nbEleves + 2
        'COUNT(OFFSET($" + CStr(i) +  ";0;0;1;COUNTA($" + CStr(i) + ":$" + CStr(i) + ")))"
        ' la fonction OFFSET  pas définie dans worksheetFunction d'ou adaptation
        Set laPlage = ActiveSheet.Range("$" + CStr(i) + ":$" + CStr(i))
        Set laPlage = ActiveSheet.Range("C" + CStr(i)).Offset(0, 0).Resize(1, WorksheetFunction.CountA(laPlage))    
        nbNoteI = WorksheetFunction.Count(laPlage)        
        If nbDevoirs < nbNoteI Then
           nbDevoirs = nbNoteI
         End If
     Next i
    ' 
    'lesCoef est la plage D2:2 si 4 devoirs
    'lesCoef = "OFFSET(D2,0,0,1," + CStr(nbDevoirs) + ")"
    Set lesCoef = ActiveSheet.Range("D2").Offset(0, 0).Resize(1, nbDevoirs)
   '
    For i = 3 To nbEleves + 2    
        'lesNotes est par exemple la plage D7:G7 (eleve n° 5)
        'lesNotes = "OFFSET(D" + CStr(i) + ",0,0,1," + CStr(nbDevoirs) + ")"
        Set lesNotes = ActiveSheet.Range("D" + CStr(i)).Offset(0, 0).Resize(1, nbDevoirs)     
        'la moyenne
        leNum = "SUM(IF(ISNUMBER(" + lesCoef.Address + "),IF(ISNUMBER(" + lesNotes.Address + ")," + lesNotes.Address + "*" + lesCoef.Address + ",0)," + lesNotes.Address + "))"
        leDeno = "SUM(IF(ISNUMBER(" + lesNotes.Address + "),IF(ISNUMBER(" + lesCoef.Address + ")," + lesCoef.Address + ",1),0))" 
        laFormule = "=" + leNum + "/" + leDeno
        Set laCelluleBcle = ActiveSheet.Cells(i, 1)
        laCelluleBcle.FormulaArray = laFormule     
     Next i
End Sub                       

 function calculerMoyenne ()
    Dim FAS as Object
    Dim laFeuille as object, laCellule as object, laCelluleBcle as Object
    Dim leForm as Object
    Dim laZone as object
    Dim laPlage as object    
    Dim info as object
    Dim laNote as double
    Dim nbEleves as integer, nbNoteI as integer, nbDevoirs as Integer
    dim i as integer
    Dim lesCoef as string, lesNotes as string, leNum as string, leDeno as string    
    FAS = CreateUnoService("com.sun.star.sheet.FunctionAccess")
    laFeuille =thisComponent.currentController.activeSheet
    LaCellule = laFeuille.getCellRangeByName("A2")
    laCellule.string ="Moyenne pond."
    'la cellule A1 contient le nb de ligne contenant des noms d'élèves
    '=NBVAL($C:$C)- 2
    LaCellule = laFeuille.getCellRangeByName("A1")
    laZone = thisComponent.currentController.activeSheet.getCellRangeByName("$C:$C")
    nbEleves = FAS.callFunction("COUNTA", Array(laZone))    
    nbDevoirs=0
    for i = 4 to nbEleves +1        
        laFormule ="=COUNT(OFFSET($"+i+":$"+ i+";0;3;1;COUNTA($"+i+ ":$"+i+")))"
        laCellule.Formula = laFormule
        nbNoteI = laCellule.Value
        if nbDevoirs < nbNoteI then
            nbDevoirs = nbNoteI
        end if
    next i
    'lesCoef est la plage D3:G3 si 4 devoirs
    lesCoef = "OFFSET(D2;0;0;1;"+Cstr(nbDevoirs)+")"       
    for i = 3 to nbEleves    
        'lesNotes est par exemple la plage D7:G7 (eleve n° 5)
        lesNotes = "OFFSET(D"+ CStr(i) + ";0;0;1;"+CStr(nbDevoirs) +")"
        'la moyenne     
        leNum ="SUM(IF(ISNUMBER("+lesCoef+");IF(ISNUMBER("+lesNotes+");"+lesNotes+"*"+lesCoef+";0);"+lesNotes+"))"
        leDeno = "SUM(IF(ISNUMBER("+lesNotes+");IF(ISNUMBER("+lesCoef+");"+lesCoef+";1);0))"
        laFormule = "=" + leNum + "/" + leDeno         
        laCelluleBcle = laFeuille.getCellByPosition( 0 , i -1  )
        laCelluleBcle.ArrayFormula = laFormule
    next i
end function