Връща междинна сума в списък или в база от данни. В повечето случаи е по-лесно да създадете списък с междинни суми, като използвате командата Междинни суми (меню Данни). Веднъж след като сте създали списък с междинни суми, можете да го модифицирате, като редактирате функцията SUBTOTAL.
Синтаксис SUBTOTAL(function_num, ref1, ref2, ...)
Function_num е число от 1 до 11 (включва скрити стойности) или от 101 до 111 (игнорира скрити стойности), което указва коя функция да използва при пресмятането на междинни суми в списък.
| Function_num (включва скрити стойности) |
Function_num (игнорира скрити стойности) |
Функция |
|---|---|---|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
Здравейте. При мен няма някаква особена разлика между subtotal 9 и 109. Ако няма никави филтри включени и скрия някои редове тогава subtotal 109 ги игнорира. Когато обаче има някакъв филтър включен /например продажбите само на Петър/ и после скрия някои от тях то subtotal 9 и subtotal 109 дават винаги един и същ резултат - игнорират скритите клетки /докато аз очаквах само subtotal 109 да ги игнорира/.
Разликата между 9 и 109 и много минимална и може да се забележи в редки случаи.
9 ще сумира клетките след приложен филтър, но ако след това ръчно скрием някой ред с hide, то ще го сумира и него.
109 е сумира клетките след приложен филтър, но ако след това ръчно скрием някой ред с hide, то него НЯМА да го сумира
Така би трябвало да става, но при мен дори и 9 не сумира hide-натите клетки. https://tranzit.dir.bg/load.php?id=s8cqXCsQFAWmvhjE1668736 Това са два скрийншота, които показват какво се получава. Първо съм филтрирал продажбите само на Петър, после съм скрил всички освен една. Клекти B63 и C63 са двете суми едната subtotal 9, другата subtotal 109 - вижда се, че резултатите са еднакви. Чудя се вече дали няма някакъв бъг. Поздрави!
Не мисля, че е бъг, просто човек малко трябва да свикне при работата със двете опции - махни филтъра и разгледай сумите при 9 и 109 без филтър, но с няколко скрити реда и виж дали тогава има разлика.
Както казах и в първия пост без включени филтри разлика има и всичко си работи ОК. Въпроса е, че аз поне очаквах да има разлика и при включени филтри. Както и да е, не е болка за умиране. Поздрави.
Открих, че с бутона autosum, Ексел автоматично залага =subtotal(109; ...) Спестява доста писане. Успех!
Благодаря! В този урок научих нещо ново и много полезно- функцията SUBTOTAL.
Благодаря аз Subtotal. Научих полезна функция!
До сега не бях използвала тази функция. Много ще улесни работата ми. Благодаря:)
Всеки урок е изключително полезен. Винаги има какво да се научи. Благодаря!
Здравейте, като посоча range всичко е ок, но така ако включа лице в последствие не се отчита неговата допълнителна сума. Не ми позволява да изпиша с:с, как да окажа, че искам цялата колона, т.е да влизат и евентуални бъдещи записи?
Чудесно структурирани и отлично водени уроци, благодаря!
Здравейте! Много ми допада стилът на преподаване! И аз досега не бях използвала тази функция, изключително полезно! Благодаря!
Как да направя копирането на формула с Ctrl C и после с поставяне с Ctrl V. Не се копира формулата, а само стойността. Трябва ли да се направи още нещо. Благодаря.
Отваря се формулата за редакция (F2, двоен клик върху клетката или с мишката върху формулата във формула бара), селектира с мишката, както би селектирал текст
Копирането на формули става като се постави курсора в долния десен ъгъл на клетката, от която ще се копира (едно уплътнено квадратче (1)), и когато курсорът се превърне в малък плюс, и се влаче при натиснат ляв бутон на мишката докъдето искаме да копираме формулата.
A със Ctrl+C и Ctrl+V се копира само стойността, ако е избрана опцията Values.
Адашке, извинявай, но това не отговаря на въпроса. По начина, който описваш, можеш да "раздърпаш" формулата надолу в таблицата, но ако искам да премстя една формула от клетка В2 например в клетка Н5 така няма да стане. Отговора на инж, Стефан Петров Петров е правилния, само че е пропуснал да каже, че след копирането като текст трябва да се натисне escape, за да не възприеме excel избраната клетка за копиране като част от формулата. Надявам се да не се обидиш от критиката! :-)
Здравей, Райна, разбира се, че няма място за обида. Но да кажа как аз разбрах поставения въпрос: При използването на Ctrl+C и Ctrl+V, не е избрана исканата опция. В клетката, в която се копира от (1) избираме исканата опция, а в случая е избрана (2).
Аз много често, когато искам да представя резултатите от определени изчисления на друго място във файла, вместо да копирам формулата като съдържание, посочвам, че клетката е равна на съответната клетка. Т.е. ако резултатът ми е в клетка C125 и искам да ми се появява и в А2, просто в А2 записвам "=C125". На пръв поглед изглежда може би малко елементарно, но е много удобно, особено ако данните са на различни шийтове във файла. А и ако реша да променям нещо в течение на работата, не се налага наново да копирам или да коригирам на две места. Клетка А2 винаги ще ми дава резултата на С125.
Здравейте! Честно казано, когато искам да видя резултатите от сумирането на друго място, постъпвам като г-жа Лора Тонева и не мисля, че е грешен начин. Единствено, ако по някаква причина решите да изтриете информацията от сумарния ред, или въобще целия ред, в кетката на новото място, EXCEL ще Ви покаже съответно "0" или "#REF!". Ако наистина, не трябва да имате сумарни клетки под таблицата, можете да ги маркирате и чрез "хващане" с мишката за някоя от страните (не за долния десен ъгъл), с влачене да преместите маркираната клетка или клетки на което пожелаете свободно място.
Още нещо по въпроса: EXCEL по различен начин гледа на нашите данни преди и след дефинирането им като таблица. Ако използваме бутона AutoSum, в таблицата от този урок, да речем за продажбите, в клетка C63 ще получим резултат "253 718 лв.", а в реда за формулите ще се вижда същата формула, като в началото на урока; "=SUMM(C2:C62)". Ако обаче, направите същото, след като в предния урок сте дефинирали данните като таблица, визуално ще получите същия резултат "253 718 лв.", но формулата вече е съвсем друга, а именно "=SUBTOTAL(109;[Продажби])", при което програмата използва името на колоната и е (почти) равнозначно на формулата до която стигнахме ръчно, в края на настоящия урок, но е с "абсолютни адреси", а именно "=SUBTOTAL(109;$C$2:$C$62)". Ако искате да копирате тази формула с Ctrl+C и после да я поставите с Ctrl+V, да Ви сумира комисионните (или в която и да е свободна клетка), ще получите резултата от колона продажби, което създава впечатление че са копирани само данните. Проверката, дали това е така, е лесна - достатъчен е един поглед към реда за формулите. Ако искате формулата да работи и в колона Комисионна, тя трябва да изглеждат така: "=SUBTOTAL(109;[Комисионна])". Другата формула няма да я коментирам, защото беше само за сравнение.
Ето правилният начин за копиране: 1.Формулата се селектира с мишката от реда за формули(горе под иконите за подравняване на текст). 2.Copy 3.Селектира се клетката,в която ще копираме формулата. 4.Esc 5.Два клика с ляв бутон на мишката върху клетката за копиране. 6.Paste
Аз съм с MS Excel 2013.
Превърни данните в таблица и сложи отметка на ТОТАL ...
Инж. Стефан Петров, стъпките са обратно. 1 и 2 трябва да ги размените. Първо се отваря Table tools --> Design--> Total Row. :)
Здравейте, бих искала някой от уроците, които съм изгледала, да маркирам като ненаучени, за да мога да се връщам на тях като важни за мен, но съм ги чекнала като научени, какво да направя?
Здравейте, Ани. Доколкото знам, при вече маркиран като научен, няма опция за отмаркиране и отново да се вижда като ненаучен. Вие ще прецените дали е по-добре занапред да не маркирате някои важни уроци, ако мислите, че така ще се отличават по-ясно, за да се връщате към тях.
Благодаря за отговора на инж.Маргарита Николова. Занапред ще се съобразявам.
Уважаема колежке Ani. Като прочетох за проблема Ви не можах да спя три дни и три нощи. Мислих, разсъждавах и анализирах и пак и пак. Учебен център АУЛА не може да не е предвидил процедура. В началото на всеки курс по учебната програма преди първия урок, методистите на центъра старателно са предвидили и предоставят "уникален" документ, който са нарекли Тетрадка. Не го пропускайте. За сега проблем за някои е, че е в .pdf формат и може да бъде полезен само на хартиен носител. Аз съм си направил такава тетрадка и във формат .doc в WORD. Любезно препоръчвам. Ще може да си отразявате какво ли не свързано с курса и темата. С извинения, ако Ви е било досадно да го дочетете. Желая успехи!
Може ли да се направи филтър, с който да се сумират стойности на клетки, като се сравняват само първите три знака от клетката? Например в кол.А имаме 110,111,2041,2042, 20415 и т.н., като искам сбор само на клетки започващи с 204?
Може разбира се.
Вариант 1 (с допълнителна колона):
Формулата връща първите три знака от всяка клетка. Използвам VALUE, за да върне число, а не текст.
=SUMIF(E1:E5;204;A1:A5)
резултатът е 24,498
Вариант 2 (без допълнителна колона):
=SUMPRODUCT(--(VALUE(LEFT(A1:A5;3))=204);A1:A5)
ще ни върне същият резултат, но без допълнителна колона
Уточнение: при вариант2, пред Value са две тиренца
БЛАГОДАРЯ KOCHO! СЛЕД ДЪЛГО ЛУТАНЕ И ПРОБА ГРЕШКА, СТИГНАХ ДО ПЪРВИЯ ВАРИАНТ, НО ЦЕЛТА МИ БЕШЕ ВТОРИЯ. БЛАГОДАРЯ!!
Как изглеждат нещата ако числата за събиране са в друга колона, т. 101,111 2041, 2045 /това са по-скоро текстови данни/ и т.н. са в колона А, като на същия ред има сума на всеки ред?
Ако приемем, че числата за събиране са в колона Е, то формулата става така: =SUMPRODUCT(--(VALUE(LEFT(A1:A5;3))=204);Е1:Е5)
ОК! ПРЕД Е1 ТРЯБВА ДА ИМА ЗВЕЗДИЧКА
А за какво е звездичката?
Не знам, но така получих желания резултат.
ДАННИ РЕЗУЛТАТ
101 102 101 102
110 110,25 110 110,25
2041 22,33 204 67,09 67,09 SUMPRODUCT(--(VALUE(LEFT(A3:A8;3))=204)*B3:B8)
2045 44,56 67,09
2043 0,2
208 5 208 5
284,34
А, ако използваш точка и запетая (;) какво става?
ОК!!
Здравейте, има ли начин да се комбинират функциите Subtotal и Sumif, за да получим обща стойност (с филтриране) на събираеми, отговарящи на някакъв критерий, например да се даде общата стойност (Subtotal) на всички положителни числа, които са в дадена колона, игнорирайки отрицателните?
Не разбирам точно какъв е казусът.
Ако приемем, че числата са в областта В1:В40, следната формула ще ви даде общата стойност на всички положителни числа:
=SUMIF(В1:В40;">0";В1:В40)
Или пък става въпрос за следното: В областта А1:А40 има въведени стойности, примерно ябълки, круши, картофи и т.н. и е сложен филтър, да речем избрано е "ябълки". Искате да сумирате положителните числа в колона В, които се виждат, след като е сложен този филтър в колона А.
Това може да стане със SUMIFS: =SUMIFS(B1:B40;B1:B40;">0";A1:A40;"ябълки")
Но най-удобният начин за такива обобщения и събирания е чрез пивот таблица
По-скоро първият казус, но, ако имаме данни за две години, например 2016 г. и 2017 г. и искаме да видим сумата на положителните числа само за 2016 г., игнорирайки отрицателните.
SUMIFS: =SUMIFS(B1:B40;B1:B40;">0";A1:A40;"=2016")
В колона А са годините, в колона В - числата
От споделените казуси си направих изводи, които ще използвам в мои задачи. Благодаря на всички!
Здравейте, моля да ми кажете дали имам възможност да използвам Subtotal да сумирам времена за каране. Пример: Имаме няколко камиона с различни времена на дейности имаме каране,работа,почивка и др. Камион №100 има 05:09:00 часа каране, 00:45:00 работа, 01:02:38 почивка. Как да използвам Subtotal да е за всеки камион поотделно? Благодаря предварително!
Функцията Subtotal си има опредлена функция - сумира. Няма проблем да сумира, числа, дати, часове и т.н., стига да са правилно дефинирани и определени /форматирани/
Здравейте, защо след като съм задала функцията sub total филтърът не работи или по- скоро работи избирателно за някой от имината и то не винаги. Може би бъркам някъде, но не разбирам къде? Моля, помогнете. Благодаря! Screen Shot 2020-06-11 at 23.55.50.png Screen Shot 2020-06-11 at 23.57.32.png Screen Shot 2020-06-11 at 23.58.12. Screen Shot 2020-06-12 at 0.15.28.png Screen Shot 2020-06-12 at 0.21.38.png
Покажете картинки, моля.
Screen Shot 2020-06-16 at 18.11.57.png
Съжалявам, но явно принт скрийна който изпращам не се отваря при Вас. Не знам защо. Веднъж го правих преди време, но сега не става. Работя с mac.
Screen Shot 2020-06-16 at 18.17.55.jpg
Изпратете файла на мейла dutoto@gmail.com
сработват и останалите оператори и формули с които работихме в предишните уроци и модули, когато имаме филтър? Защото ако направим една количествена сметка и имаме скрити от филтъра клетки резултатът може да е доста странен. Ако е фрапантно различен - всеки професионалист в областта си ще се усети, че греши, но ако е относително близък до истината - може и да не разбере.
subtotal?
да извлечем таблица с филтрирана извадка и суми или др. резултати от нея? И така на Петър от примера да изпратим по имейла файл с неговите продажби, без да вижда продажбите на другите?
Може, разбира се.
Един начин е чрез pivot таблица.
Това, което аз бих направил е, стига екипът да не е огромен, а да са просто 5-10 човека е следното.
Един sheet Report. С падащо меню се избира името на човека. В самия лист на А4 са извадени най-важните данни и графики за периода.
Така само се сменя името и се генерира красив А4 отчет за месеца примерно.
Здравейте, аз съм с по-нова версия и на мен не ми се изписват номерата на sum(9), aver(1).... Някой зане ли как мога да ги намирам бързо освен във Window. SUBTOTAL(function_num;ref....)?
File → options → formulas → отметка на Formula Auto Coplete Когато започнем да пишем в избраната клетка формулата =subtotal отдолу се появява подсказка, кликваме два пъти с мишката и тогава се появвява списъка с номерата и функциите.
File → options → formulas → отметка на Formula Auto Coplete Когато започнем да пишем в избраната клетка формулата =subtotal отдолу се появява подсказка, кликваме два пъти с мишката и тогава се появвява списъка с номерата и функциите.
Здравейте, Имам един интересен казус. Как да използвам сумиране с условие, което да се влияе от филтър? Поне нещо такова би ми помогнало да си реша задачката, която е следната. Представете си, че на колона А има филтър А B ябълки 2.00лв ябълки 2.00лв ябълки 2.00лв круши 1.00лв дюли 5.00лв дюли 5.00лв
Искам като сортирам ябълки да ми покаже сума 2,00, а не 2+2+2=6лв (до тук добре, ще направя subtotal(109;....)/subtotal(102;...). Проблемът идва, когато сортирам ябълки и дюли. Тогава искам да ми даде 2,00+5,00=7,00. Тоест искам при повтаряне на артикул да ми сметне стойността в колона В само при първото срещане и да продължи цикъла. Предполагам, ще стане с масив, но не мога да си го формулирам. Може би, = ??? (ако А:А съдържа (диапазон с всички артикули на друг шийт); събери съответните стойности от диапазон с цени срещу всеки артикул от другия шийт)... С Pivot Table също не е вариант, защото трябва целият офис лесно да борави с таблицата (и на стара версия) и да не я разбъзиква.
Една картинка ще помогне да намерим решение
Това най-лесно става с пивот таблица.
Слагате по редовете типа на стоките, а във Values - общата цена. Само че избирате като функция за обобщаване Min (десен бутон върху Sum of Обща цена > Summarize Values By > Min)
Махате гранд тотала от редовете и слагате един слайсър за по-лесно филтриране.
След това си връзвате формулата към това, което показва пивот таблицата.
Ако желаете с формула - трябва да се помисли, но принципно се върви към нещо от сорта на MINIFS
Ако не е достатъчно ясно, пишете ми на kochomindev@gmail.com, ще изпратя файл с примерно решение
