Създаване на обобщение на данни от няколко листа. 3D формули в Excel.

Безплатни 20 урока
регистрирай се и научи

Ако сте вече потребител - Регистриран съм
Видео

Тест за преминаване към следващия урок

 
Кой от изброените изрази съдържа 3D препратка?
=sum('Сметка1:Сметка2'!C6)
=sum(Сметка1!C6:Сметка2!C6)
=Сметка1!C6 + Сметка2!C6

Въпроси и отговори

  • Краси Кръстев:

    Здравей, ето ти един много елементарен пример за това как би излеждала формата и как да се направи.


    Снимка 1,2,3 съм показал sheet 1, 2, 3 в които в точно определен диапазон - D23:E23 са въведени стойности.

    Във sheet ( 3D formula ) в една клекта искам да направя сума от всичките тези диапазони намиращи се на различен листа ( sheets ).

    Снимка 4 - В клетка пиша =sum(     и тук във скобите ще хвана диапазона, който е един и същ за всеки sheet, KATO ОБАЧЕ съм селектирал всичките sheet - ове по време на селекцията на диапазона - т.е. така избирам диапазона във всеки един sheet  ( основното правило при 3D формулите ).

    Затварям скобата след това резултата е на снимка 5.

    Надявам се да съм бил полезен с отговора.

    Росица Певецова:

    Благодаря за въпроса и отговора! Като ти го покаже някой изглежда много простичко :)

    Тони Коен:

    Много хубав въпрос и много добре обяснение благодаря

  • Можем ли да използваме 3Д референция за няколко листа, които не са последователни?

    Анита Данова:

    Опитах да направя упражнението, но вместо Shift да използвам Ctrl, за да маркирам няколко листа, които не са последователни, и да получа сумата от например 1, 4, и 6 лист, но не успях. Има ли начин това да стане с 3Д референция?

    Краси Кръстев:

    Здравей, може да стане, но не и ctrl решението може да е както на снимка 1.

    Идеята е да се селектират последователно отделните диапазони и да се разделят със ; 

    Това е един от вариантите за справяне с проблема.

    Друг е ако не ти е проблем просто дадените sheet- ове винаги да са ти един след друг.
    Далина Петрова:

    Много полезни функционалности. Благодаря за подробните обяснения.

    Никола Петров:

    Ако тази тема е все още актуална, ето едно предложение. Имаме таблиците в трите листа(Sheets). Листата може да са повече и да не са последователни. Таблиците са с различна подредба, важно е първата таблица да е правилно подредена, има и липсващи данни, може и да са от различни книги (Books), но трябва да са отворени. Отваряме нов Sheet и от Data, Data Tools, избираме Consolidate (свободен превод - обобщение). В прозорецът добавяме( Add) отделните таблици от листата или книгите, като ги избираме от полето Reference. Трябва да отметнем трите полета в прозореца - Top row, Left column и най - важното Create links to source data. Натискаме OK и получаваме таблица, в която данните са събрани и правилно подредени. Появяват се горе, в ляво две полета с цифрите 1 и 2, а отстрани се виждат пет плюса,съответстващи на броя на редовете в таблицата. При натискане на някой от плюсовете ще видим данните за съответния ред ( в този случай W1, W2, W3, W4 или W5), а при натискаме на цифрата 2 ще видим обобщеният вид на цялата таблица. Дано е станало ясно! Лек ден и успехи!

    Това е втората картина, дано е станало ясно!

    Asen:

    много яко

    Никола Петров:

    Надявам се да съм помогнал! Лек ден и успехи!

    Георги:

    Здравейте, сблъсквам се с проблем при обобщаване на данните в множество работни листа. Давам прост пример какво ми трябва и вие ще кажете къде допускам грешка. Имам A,B,C и D, които са на различни позиции за всеки отделен лист, но в един и същ диапазон V2:V5. Срещу всяка от тях имам стойност, която е различна за всеки лист(W2:W5). В обобщен работен лист искам да знам каква е сумата по отделно на A,B,C и D. Използвам sumif, но ми връща Value, което е някаква грешка, която не мога да разбера. Ето и формулата, която изписвам: =SUMIF(Sheet1:Sheet3!V2:W5;total!C2;Sheet1:Sheet3!W2:W5)

    ...Където V2:W5 e диапазона, total!C2 е критерия в работен лист total, а W2:W5 e диапазона, в който сумирам. И не разбирам къде е грешката в случая. Принципно това е много прост пример и става въпрос за множество листа с много различни стойности, но принципа е същия и затова го опростих. Благодаря!

    Никола Петров:

    Правилно ли разбирам въпросът по отношение на A, B, C, D, V2, V3, V4, V5, W2, W3, W4 и W5? Таблиците са в една работна книга, но на различни листа. Дано съм разбрал правилно! Лек ден и успехи!

    Краси Кръстев:

    Здравейте,

    @Никола Петров - правилно си разбрал въпроса.

    @ Георги

    Няма как да стане само със Sumif, защото функцията не подрържа 3D референция.

    Трябва да се направи един Sumproduct, след което да вкараш един Sumif, на ниво SumIF трябва да вкараш един Indirect към създаден списък).

    Ето един пример от КСС - та (снимка 1 позиция 1) за които ми трябваха крайни суми от няколко sheet-a.

    Снимка_1

    Формулата е малко по-специфична, затова ако може малко повече информация за конкретния случай или по-скоро ти препоръчвам да ми пратиш един Email с конкретния пример, за да видя дали наистина казуса е на 100% за това което си мисля.

    Георги:

    Здравейте, Първо благодаря за отговорите,

    Георги:

    Но за съжаление ги виждам едва сега. Просто на мен не ми се виждаше и въпроса дори. Стори ми се странно и затова писах на мейла. Отговорихте ми със скрийншот и наистина въпросът си е тук. Опитвах и през телефон и през друг лаптоп и не можех да го видя. Сега чак вече е наличен и може да се гледа. Иначе когато изчезна си помислих, че има проблем и започнах да търся из чужди сайтове и наистина разбрах, че трябва да се добави Sumproduct и Indirect и успях да пригодя функциите за моята таблица. Благодаря още веднъж и извинявайте, че чак сега отговарям, но просто въпросът ми не беше видим за мен. Поздрави!