Създаване на обобщение на данни от няколко листа. 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 и успях да пригодя функциите за моята таблица. Благодаря още веднъж и извинявайте, че чак сега отговарям, но просто въпросът ми не беше видим за мен. Поздрави!

  • Може ли да се използват кодовете на отделните материали в КСС-то? 

    Марианна Даверова:

    Става дума за СМР, които са записани в отделни листа на количествената сметка по нива. Когато става дума за изкоп и фундамент има 6-7 СМР и 2-3 материала. Например кожраж, арматура и бетон, но когато става дума за реновация, то на една кота, която е на един лист, може да има 50-60 позиции, понякога с дублиращи се материали. Вата на тавана, вата в предстенната обшивка, вата в преградната стена и т.н. Всички са с различни дебелини, различни цени и марки. Не може да се обобщят като арматурата по тегло.

    Диана Христова:

    В такъв случай ги подреждаш по видове и си описваш за всеки вид дебелина, цена, доставчик, марка, параметри и т.н.

    Марианна Даверова:

    Въпросът ми беше по-скоро "Как програмата може да ги извади от таблиците" и да ги обобщи в рекапитулация. Например : гипсокартонът има код 07.02, 07.03, 07.04 и т.н. във всички листове в зависимост от етажите. Има ли формула която може да ми извади количествата и да ги сумира, ако се зададе например =sum(07.02....07.08)

    Диана Христова:

    Има програма - PROJECT ESTIMATOR. Там това е направено. Иначе - задай въпроса наново така, както го доуточняваш, за да може някой да помогне. Според мен това става с допълнителна програмка.

    Марианна Даверова:

    Благодаря! Ползвам PROJECT ESTIMATOR, но не е това което ми трябва. В един основен ремонт на триетажна къща с примерно 5-6 апартамента, където всичко е свалено до тухла и се прави отново има около 60-70 операции с различни материали. Например една преградна стена има конструкция , вата, шумоизолационна лента, гипсокартон, мрежа, шпакловка и латекс / може и да пропускам нещо/. И като започна да обобщавам материалите започва едно оцветяване или маркиране по някакъв друг начин на вече включен материал. И въпреки всичко рядко изкарвам едно и също количество при проверката. Имаше някакъв смисъл в това да се вкарва материала с кодове, но не се сещам как.

    Диана Христова:

    Здравей, PROJECT ESTIMATOR вади спецификация на материалите, така че не е необходимо да го правиш на ръка. В базата данни си вкарай каквито желаеш материали, с каквито искаш кодове, направи си анализни цени за всяка стена и задай коректно количествата им. Програмата върши останалото.