SUMIF iz več listov

[nikoj]

Pozdravljeni,

Tudi meni se je prikradel problem pa se obračam na VAS.

Imam 13 listov (12 mesecev plus zadnji za seštevke). V A stplcu so osebe, katere pa niso več na vseh listih v enakem zaporedju (ne ležijo na soležnih celicah na vseh listih).
V nadaljnih stoplcih imam števila, katera pa želim na zadnjem trinajstem listu sešteti, seveda po posameznih osebah. Dokler je bilo na vseh listih enako oseb (soležne celice) sem vrednosti sešteval z SUM.
Sedaj pa sem podobno pouskusil tudi z SUMIF pa zadeva na več listih hkrati ne deluje.

Ali obstaja kakšna "pametna" režitev z funkcijami ali kako drugače.

Prosim za nasvet - pomoč

LP Nikoj

[admin]

Pozdravljeni,

Kot ste sami ugotovili, finkcija SUMIF ne pozna 3D formul in zato ji ne morete podati več listov hkrati. Predlagam vam dve rešitvi:

PRVIČ:
Sami zapišete 12 SUMIF formul:
=SUMIF(List1!A1:A200;A1;List1!B1:B200)+
SUMIF(List2!A1:A200;A1;List2!B1:B200)+
SUMIF(List3!A1:A200;A1;List3!B1:B200)+
SUMIF(List4!A1:A200;A1;List4!B1:B200)+
SUMIF(List5!A1:A200;A1;List5!B1:B200)+
SUMIF(List6!A1:A200;A1;List6!B1:B200)+
SUMIF(List7!A1:A200;A1;List7!B1:B200)+
SUMIF(List8!A1:A200;A1;List8!B1:B200)+
SUMIF(List9!A1:A200;A1;List9!B1:B200)+
SUMIF(List10!A1:A200;A1;List10!B1:B200)+
SUMIF(List11!A1:A200;A1;List11!B1:B200)+
SUMIF(List12!A1:A200;A1;List12!B1:B200)

Kot vidite lahko to v kakšnem urejevalniku hitro zapišete, potem pa samo zapišete vse v eno vrstico in to je to. Izgleda veliko, a gre praktično samo za kopiranje teksta (par sekund!).

DRUGIČ
Ker se z istim problemom srečujejo mnogi uporabniki, lahko uporabite že najdene rešitve: http://www.dailydoseofexcel.com/archive ... functions/
Od tam skopirate sledeči funkciji (oz. skopirate jih iz tega foruma, ker je v originalu napaka, ki sem jo jaz odpravil)

Koda: Izberi vse

' pobrano iz: http://www.dailydoseofexcel.com/archives/2006/02/25/3d-user-defined-functions/
Function Parse3DRange(sBook As String, SheetsAndRange _
    As String, FirstSheet As Integer, LastSheet As Integer, _
    sRange As String) As Boolean
 
    Dim sTemp As String
    Dim i As Integer
    Dim Sheet1 As String
    Dim Sheet2 As String
 
    Parse3DRange = False
    On Error GoTo Parse3DRangeError
 
    sTemp = SheetsAndRange
    i = InStr(sTemp, "!")
    If i = 0 Then Exit Function
 
    'next line will generate an error if range is invalid
    'if it's OK, it will be converted to absolute form
    sRange = Range(Mid$(sTemp, i + 1)).Address
 
    sTemp = Left$(sTemp, i - 1)
    i = InStr(sTemp, ":")
    Sheet2 = Trim(Mid$(sTemp, i + 1))
    If i > 0 Then
      Sheet1 = Trim(Left$(sTemp, i - 1))
    Else
      Sheet1 = Sheet2
    End If
 
    'next lines will generate errors if sheet names are invalid
    With Workbooks(sBook)
    FirstSheet = .Worksheets(Sheet1).Index
    LastSheet = .Worksheets(Sheet2).Index
 
    'swap if out of order
    If FirstSheet > LastSheet Then
      i = FirstSheet
      FirstSheet = LastSheet
      LastSheet = i
    End If
 
    i = .Worksheets.Count
    If FirstSheet >= 1 And LastSheet <= i Then
      Parse3DRange = True
    End If
    End With
Parse3DRangeError:
    On Error GoTo 0
    Exit Function
 
End Function  'Parse3DRange
 
Function SumIf3D(Range3D As String, Criteria As String, _
    Optional Sum_Range As Variant) As Variant
 
    Dim sTestRange As String
    Dim sSumRange As String
    Dim Sheet1 As Integer
    Dim Sheet2 As Integer
    Dim n As Integer
    Dim Sum As Double
 
    Application.Volatile
 
    If Parse3DRange(Application.Caller.Parent.Parent.Name, _
      Range3D, Sheet1, Sheet2, sTestRange) = False Then
      SumIf3D = CVErr(xlErrRef)
    End If
 
    If IsMissing(Sum_Range) Then
      sSumRange = sTestRange
    Else
      sSumRange = Sum_Range
    End If
 
    Sum = 0
     For n = Sheet1 To Sheet2
      With Worksheets(n)
        Sum = Sum + Application.WorksheetFunction.SumIf(.Range _
        (sTestRange), Criteria, .Range(sSumRange))
      End With
    Next n
    SumIf3D = Sum
End Function

Potem pa lahko zapišete kar:

Koda: Izberi vse

=SumIf3D("List1:List12!A1:A200";A1;"B1:B200")

Pozor! Oznake listov so samo v prvem nizu, pri drugem jih ni!!

[nikoj]

Pozdravljeni Matjaž!

Hvala za hiter odgovor.
Želel bi uporabiti vaš drugi primer vendar mi stvar nekako ne deluje, še vedno v celici dobim izpis: #VREDN!
Naj ponovim kaj sem naredil:
Vašo kodo prekopiral v: Orodja/Makro/Urejevalnik za Visual Basic
Shranil podatke
Nato pa vpisal naslednjo kodo v celico:
=sumif3d('JANUAR 06:DECEMBER 06'!A8:A87;A8;AG8:AG87)

Vidim, da se v kodi razlikujejo moji in vaši narekovaji. Jaz sem jih poskusil postaviti tako kot jih imate v vaši kodi pa mi potem javi napako v formuli, če pa jih postavim tako kot so vidni sedaj pa dobim v celici napis:#VREDN!

prosim za nasvet!

LP Nikoj

[admin]

Pozdravljeni,

Narekovaji morajo biti postavljeni natanko tako kot so v primeru, ki sem vam ga napisal. Če vam Excel javi napako jo napišite, da vam bom lahko pomagal!

Vaš primer pa bi moral izgledati takole:

Koda: Izberi vse

=sumif3d("JANUAR 06:DECEMBER 06!A8:A87";A8;"AG8:AG87")

[nikoj]

Pozdravljeni,

Odlično, že deluje.
Čim sem vašo kodo kopiral v celico je takoj delovalo. Jaz sem jo sicer ročno pisal kar nekajkrat pa ni in ni šlo. Vedno je javil napako, očitno mi je naghajal kakšen presledek ali kaj podobnega.

Še enkrat hvala in lep pozdrav.
P.S.
rešili ste me velike težava še bolje uporabnika.

Lp Nikoj

[TomZzz]

Pozdravljeni!

Zgoraj sem opazil lep primer seštevanja števil glede na osebe. Sam namreč potrebujem podobno zadevo, vendar takole:

Na prvem listu imam v prvem stolpcu šifro, v drugem naziv izdelka ter v tretjem ceno izdelka. Vsaka šifra je recimo številka, ki pripada določeni osebi. Potreboval bi kodo, ki bi glede na šifro seštela vse cene, ki ji pripadajo.

Primer:
Alenka -> šifra: 00001
V stolpcu A poišče vse vrednosti 00001 ter pri teh vrednostih sešteje zneske iz stolpca C.

Upam, da ste me razumeli in mi pomagali priti do te formule.
Hvala, Tomaž

[admin]

Pozdravljeni,

Če prav razumem vaš problem, je rešitev preprosto v uporabi funkcije SUMIF, o kateri si lahko veliko preberete tudi v tem forumu:

Koda: Izberi vse

=SUMIF(a1:a1000; "00001"; c1:c1000)

[TomZzz]

hvala za vaš odgovor. S to funkcijo sem že poizkusil vendar mi prej ni delovalo, sedaj pa zadeva deluje brezhibno.

Hvala

[FORCE]

Mene pa zanima če to deluje tudi samo kot SUM, recimo če napišem
=sum3d... pa da bi samo sešteval iz večih listov

[admin]

Tega vprašanja pa ne razumem.

[FORCE]

Se opravičujem, si mi že odgovoril v drugi temi.