Excel - priemer z posledných desať údajov

[Dušan]

Dosť podrobne som sa snažil prejsť príspevky na tému ktorá ma zaujíma. Presvedčený, že ani jeden z príspevkov nerieši môj problém, by som Vás rad poprosil o pomoc s takýmto problémom.
Rad by som poradiť. Potrebujem spočítať priemer čísel v tabule Excel. V stĺpci ktorý sa priebežne dopĺňa údajmi smerom dole. Avšak potrebujem urobiť priemer vždy iba posledných desať údajov. Pravdu povediac neviem ako zadať do vzorca rozsah buniek, tak aby spočítavalo vždy aktuálne iba posledných desať čísiel.
Za odpoveď vopred ďakujem Dušan.

[Palo-admin]

Ahoj Dušan.
Pretože výsledný vzorec je dosť dlhý a komplikovaný popíšem ho po krokoch.

Predpoklady - asi by to malo fungovať i v XL 2003 a starších, stĺpec, kde hľadáme poslednú hodnotu
môže mať i prázdne bunky. Príklad je urobený pre stĺpec A

1) Posl riadok v stĺpci A:

Kód: Vybrať všetko

=MATCH(MAX(A1:A60000)+1;A1:A60000;1)

2) Hodnota posl riadku:

Kód: Vybrať všetko

=INDIRECT("A" & MATCH(MAX(A1:A60000)+1;A1:A60000;1))

3) Hodnota posl riadku mínus 10 riadkov:

Kód: Vybrať všetko

=INDIRECT("A" & MATCH(MAX(A1:A60000)+1;A1:A60000;1)-10)

4) Priemer posl. 10 riadkov - staticky - pre kontrolu

Kód: Vybrať všetko

=AVERAGE(A6:A16)

5) Priemer posl. 10 riadkov - dynamicky - finálny vzorec

Kód: Vybrať všetko

=AVERAGE(INDIRECT("A" & MATCH(MAX(A1:A60000)+1;A1:A60000;1)-10 & ":A" & MATCH(MAX(A1:A60000)+1;A1:A60000;1)))

Samozrejme, treba ešte ošetriť, ak je menej ako 10 riadkov s hodnotami.
Možných riešení je viacero, niektoré však len pre XL 2007 a vyššie.

[Dušan]

Vyjadrujem úprimne poďakovanie za skvelú odpoveď. Nečakal som že môj problém bude vyriešený tak rýchlo. O to viac, že nikto (aspoň s tých čo som ich oslovil) to nevedeli vyriešiť. Aj napriek tomu že výsledok je na moje pomery trošku zložitejší, funguje perfektne.
Ešte raz úprimná vďaka

S úctou Dušan

[Dušan]

Za odpoveď ešte raz ďakujem pekne. Požiadavku ohľadom riešenia problému nad rámec predošlého dotazu som zakomponoval do Vami zaslanej tabuľky. Dúfam že príde v poriadku.
Ide v nej o bližšie špecifikovanie posledných desať čísiel.
S úctou Dušan

[Palo-admin]

No, toto je špecifický prípad, lebo hodnotu s bodkou identifikuje Excel (pri slovenskom regionálnom nastavení) ako text.
To znamená, že nemôžem použiť špecifické funkcie ako SUMIF, alebo array funkcie (resp. by boli príliš zložité).

Preto som vytvoril užívateľskú funkciu - makro - ktorú možno použiť ako štandard. funkciu:

Kód: Vybrať všetko

=Priemer_posl_10(B5:B24;".")

Celá funkcia (VBA kód) vyzerá takto:

Kód: Vybrať všetko

Public Function Priemer_posl_10(xRng As Variant, xIdentifikator As String)
    Dim c As Range, xObl As Range, i As Long
    Dim x, xSum, j As Byte
   
    Application.Volatile
    On Error GoTo xErr
    Set xObl = xRng
   
    For i = xObl.Cells.Count To 1 Step -1   'od konca po prvu
   
        If InStr(1, xObl.Cells(i).Text, xIdentifikator) > 0 Then
           x = Replace(xObl.Cells(i).Text, xIdentifikator, "", 1, -1, 1) 'testuj špecifikator
        ElseIf xIdentifikator = "" Then 'ak nie je Identifikator
           x = xObl.Cells(i).Text
        Else
           x = ""
        End If
       
        If x <> "" And IsNumeric(x) Then 'musi byt cislo
            xSum = xSum + CDbl(x)
            j = j + 1
        End If
       
        If j > 10 Then Exit For
    Next i
    If j > 0 Then
        Priemer_posl_10 = xSum / j
    Else
        Priemer_posl_10 = 0
    End If
   
    Exit Function
xErr:
    Priemer_posl_10 = "#Chyba#"
End Function


[Dušan]

Na začiatok by som rád úprimné vyjadril veľké poďakovanie. Nielen za pripravené riešenie ale rovnako tak aj za veľmi skoré vybavenie. Myslím, že podkovanie je v tomto prípade ozaj na mieste.

S úctou Dušan

[Dušan]

Aj napriek tomu že tabuľky spočítavaju nastali tam ešte drobne chýby. Preto by som Vás rád poprosil či by ste sa na to ešte pozreli. Komentár pripajam v súkromnej správe.

[Palo-admin]

Aj napriek tomu že tabuľky spočítavaju nastali tam ešte drobne chýby. Preto by som Vás rád poprosil či by ste sa na to ešte pozreli. Komentár pripajam v súkromnej správe.

No toto je úplne iný prípad, ako prvé zadanie.

Sú tu dve podstatné zmeny:
1) počet vyhodnocovaných sa zmenil z 10 na 6,
2) ale hlavne rozsah buniek, ktoré sa vyhodnocujú je iný, ako tých čo sa priemerujú.

Okrem toho treba ošetriť i stav " bez bodky".

Upravil som pôvodnú funkciu a čo je potrebné sprametrizoval:

Kód: Vybrať všetko

Public Function Priemer_posl_X(xPodmienka As Variant, xCoSpracovat As Variant, _
                               xKolkoPoloziek As Integer, _
                               xIdentifikator As String, Optional xCoVylucit As String)
                               
    Dim c As Range, xOblPodm As Range, xOblSprac As Range, i As Long
    Dim xRP, xRCS, xSum, j As Byte, aVyl, k As Byte
   
    Application.Volatile
    On Error GoTo xErr
   
    Set xOblPodm = xPodmienka                       ' co sa bude vyhodnocovat
    Set xOblSprac = xCoSpracovat                    ' co sa bude priemerovat
    aVyl = Split(xCoVylucit, "|")                   ' ak je viac znakov co vylucit, odelit ich znakom |
   
    For i = xOblPodm.Cells.Count To 1 Step -1       'od konca po prvu
        xRP = xOblPodm.Cells(i).Text
       
        If xCoVylucit <> "" Then
            For k = LBound(aVyl) To UBound(aVyl)    'obskoc, co netreba spracovat
                If InStr(1, xRP, aVyl(k)) > 0 Then GoTo xNext
            Next k
        End If
           
        If (xIdentifikator <> "") And (InStr(1, xRP, xIdentifikator)) > 0 Then
            xRCS = xOblSprac.Cells(i).Text
           
        ElseIf xIdentifikator = "" Then             'ak nie je Identifikator
            xRCS = xOblSprac.Cells(i).Text
           
        Else
            xRCS = ""
        End If
       
        If xRCS <> "" And IsNumeric(xRCS) Then      'musi byt cislo
            xSum = xSum + CDbl(xRCS)
            j = j + 1
        End If
       
xNext:
        If j >= xKolkoPoloziek Then Exit For
       
    Next i
   
   
    If j > 0 Then
        Priemer_posl_X = xSum / j
    Else
        Priemer_posl_X = 0
    End If
   
    Exit Function
xErr:
    Priemer_posl_X = "#Chyba#"
End Function


Palo

[Dušan]

Najprv by som sa vám chcel veľmi pekne poďakovať. Hoci to vyzerá skôr na zázrak ale funguje to.
Mam známych, ktorý sa zaoberajú excelom profesionálne v nejakej firme, vraj vyrábajú programy pre školstvo. Samozrejme som sa na nich obrátil s problémom ešte pred tým než som oslovil vás- nevedeli s tým pohnúť. Keď som im následné ukázal váš výsledný produkt najprv stíchli a potom povedali to je šaman ten programátor. Ja som ich oslovil s tým, že aj keď to funguje stále si s tým neviem rady. Ide zrejme o nejakú drobnú blbosť a tak som si myslel že toto by už mohli zvládnuť. Výsledok je " vieš čo budeš sa s tým musieť obrátiť na toho šamana, lebo my s tým nevieme pohnúť". Podobne to bolo aj tým problémom pri usporiadaní tabuľky. To svedči o tom že akékoľvek slova chváli zrejme dostatočne nedokážu vyjadriť to čo pre nás lajkov robíte.
K tým vzorcom na posledných šesť čísel. Ak som prevzal tabuľku do svojho P.C. funguje krásne, ale ak prekopírujem obsah kde je ten vzorec do druhej mojej hlavnej tabuľky zasvieti ### čo značí že niečo nie je v poriadku. Pritom excel v ktorom ste vytvorili vzorec je môj teda verzia aj ostatne parametre by mali byť rovnaké. Neviem čo mam urobiť aby to fungovalo.
Rovnako tak aj problém s usporiadaním tabuľky funguje perfektne iba, že ja potrebujem ukladať podľa kritéria od najväčšieho po najmenšie a ono to robí presne naopak.
Aj keď sa už cítim trápne rad by som vás ešte raz požiadal či by ste sa to pozreli.
Teda 1/ tabuľku opraviť aby ukladala od najväčšieho po najmenšie
2/ skúsiť nájsť chybu prečo vzorec s poslednými šesť číslami nefunguje po skopírovaní do mojej tabuľky.

Za skoré vybavenie vopred ďakujem

S úctou Dušan

[Palo-admin]

Použité funkcie v oboch riešeniach sú súčasťou príslušného súboru (nie Excelu samotného)
Treba si ich prekopírovať do svojho riešenia.

Ako použiť makrá - rýchly tip:
1) stlačením ALT+F11 sa zobrazí VBA okno (Visual Basic okno),
2) klik pravým tl. myši na ThisWorkbook, Insert / Module a vložiť kód uvedený v oboch príkladoch.

Pozor:
A) funkcia Worksheet_Change pre príklad sortovania musí byť uložená nad príslušným listom (napr Sheet1) v ktorom je volaná
B) pre verziu Excelu 2007 a vyššie treba uložiť súbor ako nazov_suboru.XLSM

1/ ad "tabuľku opraviť aby ukladala od najväčšieho po najmenšie"
Treba upraviť funkciu Sort_podla_C nasledovne (Order1:=xlDescending):

Kód: Vybrať všetko

Sub Sort_podla_C()
    Range("C1").Select
    Range("A:C").Sort Key1:=Range("C1"), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
End Sub