Сумиране при таблици с филтър. Използване на SubTotal

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

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

Линк към help-a

Информация от help-а

 

Процедура по копиране на формули

  1. Кликва се клетката, от която ще се копира
  2. Избира се формулата от полето за въвеждане на формули и се копира (например с CTRL+C)
  3. Натиска се клавиша Escape, за да се прекрати режима на редакция
  4. Кликва се в новата клетка
  5. Избира се полето за въвеждане на формула (или се кликва два пъти на клетката) и се поставя фомулата с CTRL+V)

Функция SUBTOTAL

 

Връща междинна сума в списък или в база от данни. В повечето случаи е по-лесно да създадете списък с междинни суми, като използвате командата Междинни суми (меню Данни). Веднъж след като сте създали списък с междинни суми, можете да го модифицирате, като редактирате функцията 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?
Когато искаме да сумираме или да ползваме друга функция като средно аритметично и брой при таблици с филтър.
Когато искаме да сумираме или да ползваме друга функция като средно аритметично и брой, независимо каква е таблицата.
Само когато искаме да сумираме при таблици с филтър.

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

  • Каква е разликата между subtotal 9 и 109?

    Кирил Николаев:

    Здравейте. При мен няма някаква особена разлика между subtotal 9 и 109. Ако няма никави филтри включени и скрия някои редове тогава subtotal 109 ги игнорира. Когато обаче има някакъв филтър включен /например продажбите само на Петър/ и после скрия някои от тях то subtotal 9 и subtotal 109 дават винаги един и същ резултат - игнорират скритите клетки /докато аз очаквах само subtotal 109 да ги игнорира/.

    Иван Цукев:

    Разликата между 9 и 109 и много минимална и може да се забележи в редки случаи.

    9 ще сумира клетките след приложен филтър, но ако след това ръчно скрием някой ред с hide, то ще го сумира и него.

    109 е сумира клетките след приложен филтър, но ако след това ръчно скрием някой ред с hide, то него НЯМА да го сумира

    Кирил Николаев:

    Така би трябвало да става, но при мен дори и 9 не сумира hide-натите клетки. http://tranzit.dir.bg/load.php?id=s8cqXCsQFAWmvhjE1668736 Това са два скрийншота, които показват какво се получава. Първо съм филтрирал продажбите само на Петър, после съм скрил всички освен една. Клекти B63 и C63 са двете суми едната subtotal 9, другата subtotal 109 - вижда се, че резултатите са еднакви. Чудя се вече дали няма някакъв бъг. Поздрави!

    Иван Цукев:

    Не мисля, че е бъг, просто човек малко трябва да свикне при работата със двете опции - махни филтъра и разгледай сумите при 9 и 109 без филтър, но с няколко скрити реда и виж дали тогава има разлика.

    Кирил Николаев:

    Както казах и в първия пост без включени филтри разлика има и всичко си работи ОК. Въпроса е, че аз поне очаквах да има разлика и при включени филтри. Както и да е, не е болка за умиране. Поздрави.

    Росица Божкова:
    • The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use. Според мен ще има разлика само ако сте скрили редове с командата Hide/Unhide  rows и нямате филтриране
    Andrey Glushkov:

    Открих, че с бутона autosum, Ексел автоматично залага =subtotal(109; ...) Спестява доста писане. Успех!

    Юлияна Борисова:

    Благодаря! В този урок научих нещо ново и много полезно- функцията SUBTOTAL.

    Mila Georgieva-Petrova:

    Благодаря аз Subtotal. Научих полезна функция!

    Elenayordanova:

    До сега не бях използвала тази функция. Много ще улесни работата ми. Благодаря:)

  • Как се копират формули?

    Юлияна Григорова:

    Как да направя копирането на формула с Ctrl C и после с поставяне с Ctrl V. Не се копира формулата, а само стойността. Трябва ли да се направи още нещо. Благодаря.

    Стефан Петров Петров:

    Отваря се формулата за редакция (F2, двоен клик върху клетката или с мишката върху формулата във формула бара), селектира с мишката, както би селектирал текст

    Райна:

    Копирането на формули става като се постави курсора в долния десен ъгъл на клетката, от която ще се копира (едно уплътнено квадратче (1)), и когато курсорът се превърне в малък плюс, и се влаче при натиснат ляв бутон на мишката докъдето искаме да копираме формулата. 

    Когато таблицата е попълнена и искаме формулата да се копира до края на таблицата може и с двукратно кликване на малкото квадратче (1).
    Райна:

    A  със  Ctrl+C и Ctrl+V се копира само стойността, ако е избрана опцията Values.

    Rayna Damianova:

    Адашке, извинявай, но това не отговаря на въпроса. По начина, който описваш, можеш да "раздърпаш" формулата надолу в таблицата, но ако искам да премстя една формула от клетка В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;[Комисионна])". Другата формула няма да я коментирам, защото беше само за сравнение.

  • Стефан Петров Петров:

    Превърни данните в таблица и сложи отметка на ТОТАL ...

  • Как да отмаркирам даден урок като вече научен?

    Ani:

    Здравейте, бих искала някой от уроците, които съм изгледала, да маркирам като ненаучени, за да мога да се връщам на тях като важни за мен, но съм ги чекнала като научени, какво да направя?

    Маргарита Николова:

    Здравейте, Ани. Доколкото знам, при вече маркиран като научен, няма опция за отмаркиране и отново да се вижда като ненаучен. Вие ще прецените дали е по-добре занапред да не маркирате някои важни уроци, ако мислите, че така ще се отличават по-ясно, за да се връщате към тях.

    Ani:

    Благодаря за отговора на инж.Маргарита Николова. Занапред ще се съобразявам.

    Захари   Даскалов:

    Уважаема колежке Ani. Като прочетох за проблема Ви не можах да спя три дни и три нощи. Мислих, разсъждавах и анализирах и пак и пак. Учебен център АУЛА не може да не е предвидил процедура. В началото на всеки курс по учебната програма преди първия урок, методистите на центъра старателно са предвидили и предоставят "уникален" документ, който са нарекли Тетрадка. Не го пропускайте. За сега проблем за някои е, че е в .pdf формат и може да бъде полезен само на хартиен носител. Аз съм си направил такава тетрадка и във формат .doc в WORD. Любезно препоръчвам. Ще може да си отразявате какво ли не свързано с курса и темата. С извинения, ако Ви е било досадно да го дочетете. Желая успехи!