Този епизод показва три начина за извършване на текущи суми.
Текущо общо е, за списък с числови стойности, сума от стойностите от първия ред до реда на текущото общо. Често използваните текущи суми са в регистър на чекова книжка или счетоводен лист. Има много начини за създаване на текущо общо - два от които са описани по-долу.
Най-простата техника е на всеки ред да добавите текущата сума от горния ред към стойността в реда. Така че първата формула в ред 2 е:
=SUM(D1,C2)
Причината да използваме функцията SUM е, че на първия ред гледаме заглавката в реда по-горе. Ако използваме по-простата, по-интуитивна формула, =D1+C2тогава ще се генерира грешка, тъй като стойността на заглавката е текст спрямо числова. Магията е, че функцията SUM игнорира текстови стойности, които се добавят като нулеви стойности. Когато формулата се копира надолу към всички редове, в които се желае текущо общо, препратките към клетките се коригират съответно:
Другата техника също използва функцията SUM, но всяка формула сумира всички стойности от първия ред до реда, показващ текущото общо. В този случай използваме знак за долар ($), за да направим първата клетка в препратката абсолютна препратка, което означава, че не се коригира при копиране:
И двете техники не се влияят от сортиране и изтриване на редове, но при вмъкване на редове формулата трябва да бъде копирана в новите редове.
Excel 2007 представи таблицата, която представлява повторно внедряване на списъка в Excel 2003. Таблиците представиха редица много полезни функции за таблици с данни, като форматиране, сортиране и филтриране. С въвеждането на таблици ни беше предоставен и нов начин за препращане към частите на таблица. Този нов стил на препращане се нарича структурирано препращане.
За да преобразуваме горния пример в Таблица, ние избираме данните, които искаме да включим в Таблицата, и натискаме Ctrl + T. След извеждане на подкана да поискаме да потвърдим обхвата на Таблицата и дали съществуват заглавки, Excel преобразува данните във форматирана таблица:
Имайте предвид, че формулите, които въведохме по-рано, остават същите.
Една от полезните функции, които Tables предлага, е автоматично форматиране и поддържане на формули, когато редовете се добавят, премахват, сортират и филтрират. По-специално поддръжката на формулата, върху която ще се съсредоточим и която може да бъде проблематична. За да поддържа таблиците да работят, докато се манипулират, Excel използва изчислени колони, които са колони с формули като колона D в горния пример. Когато се добавят нови редове и се добавят към дъното, Excel автоматично попълва новите редове с формулата „по подразбиране“ за тази колона. Проблемът с горния пример е, че Excel се бърка със стандартните формули и не винаги се справя правилно с тях. Това става очевидно, когато се добавят нови редове в долната част на таблицата (като изберете долната дясна клетка в таблицата и натиснете TAB):
Този недостатък се решава с помощта на по-новото структурирано препращане. Структурираното позоваване елиминира необходимостта от препращане към конкретни клетки, използвайки стила на препращане A1 или R1C1 и вместо това използва имена на колони и други ключови думи за идентифициране и препращане към частите на таблица. Например, за да създадем същата формула за общо изпълнение, използвана по-горе, но използвайки структурирано препращане, имаме:
=SUM(INDEX((Sales),1):(@Sales))
В този пример имаме препратка към името на колоната, „Продажби“, заедно със знака at (@) за препратка към реда в колоната, в който се намира формулата, който е известен също като текущия ред.
За да приложите първия пример по-горе, където добавихме текущата обща стойност в предходния ред към сумата на продажбите в текущия ред, можете да използвате функцията OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Ако сумите, използвани за изчисляване на текущата сума, са в две колони, например една за „Дебити“ и една за „Кредити“, тогава формулата е:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Тук използваме функцията INDEX, за да намерим кредитните и дебитните клетки на първия ред и сумираме цялата колона до включително стойностите на текущия ред. Текущият сбор е сумата от всички кредити до и включително текущия ред, намалена с сумата на всички дебити до и включително текущия ред.
За повече информация относно структурираните препратки в частност и таблиците като цяло, препоръчваме книгата Excel Tables: Пълно ръководство за създаване, използване и автоматизиране на списъци и таблици от Зак Барес и Кевин Джоунс.
Когато помолих читателите да гласуват за любимите им съвети, таблиците бяха популярни. Благодаря на Питър Албърт, Сноре Айкеланд, Нанси Федерис, Колин Майкъл, Джеймс Е. Моеде, Кейър Пател и Пол Петон, че предложиха тази функция. Питър Албърт написа бонус за Readable References. Зак Барес написа бонус съвет Running Totals. Четирима читатели предложиха да се използва OFFSET за създаване на разширяващи се диапазони за динамични класации: Чарли Баак, Дон Ноулс, Франсис Логан и Сеселия Риб. В повечето случаи таблиците правят същото нещо в повечето случаи.
Гледам видео
- Този епизод показва три начина за извършване на текущи суми
- Първият метод има различна формула в ред 2 от всички останали редове
- Първият метод е = Наляво в ред 2 и = Наляво + Нагоре в редове 3 до N
- Ако се опитате да използвате същата формула, получавате грешка #Value с = Общо + Число
- Метод 2 използва
=SUM(Up,Left)или=SUM(Previous Total,This Row Amount) - SUM игнорира текста, за да не получите грешка VALUE
- Метод 3 използва разширяващ се диапазон:
=SUM(B$2:B2) - Разширяващите се диапазони са готини, но те са бавни
- Прочетете бялата книга на Чарлз Уилямс за скоростта на Excel Formula
- Третият метод е проблем, когато използвате Ctrl + T и добавите нови редове
- Excel не може да разбере как да напише формулата
- Заобиколните решения изискват известни познания за структурираното позоваване в таблици
- Заобиколно решение 1 е бавното
=SUM(INDEX((Qty),1):(@Qty)) - Заобиколно решение 2 е летливото
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) се отнася до Qty на този ред
- (Qty) се отнася до всички стойности на Qty
Видео транскрипция
Научете Excel за подкаст, епизод 2004 - Текущи суми
Ще подкастирам цялата тази книга. Щракнете върху това I в горния десен ъгъл, за да се абонирате.
Хей, добре дошли обратно в мистичната клетъчна мрежа. Аз съм Бил Джелен. Сега тази тема в книгата ми допринесе моят приятел Зак Паризе. Говорейки за таблици в Excel, Зак е световният експерт по таблици в Excel. Той е написал книга за таблиците в Excel, но първо нека поговорим за извеждането на суми, които не са в таблици.
Така че, когато се замисля за текущи суми, има три различни начина да направя текущите суми и начинът, по който винаги съм започвал, е в първия ред, който просто казвате, да пренесете стойността. Така че равна на всичко, което е вляво от мен. Добре, така че този формат тук е просто = B2. Всичко това е текст на формула тук в десния ъгъл, така че виждате какво използваме и след това оттам нататък, това е проста малка формула, равна на предишната стойност, плюс текущата стойност вдясно и копирайте това надолу , но сега знаете, имаме този проблем, че той изискваше две различни формули и вие знаете, че в перфектна ситуация имате точно същата формула докрай и причината да имаме различна формула на първия ред е че когато се опитате да добавите равно 7 плюс думата общо, това е грешка в стойността,но страхотният работник тук е не просто да използва ляво плюс нагоре, а да използва = (SUM) на предишната стойност плюс количеството в този ред и да видим, че някои са достатъчно далеч, за да игнорират текстове. Точно така, че позволява същата формула. по целия път надолу.
Така беше, когато започнах да използвам Excel, използвах това и тогава открих разширяващия се обхват, разширяващият се обхват казва, че ще направим L $ 2: L2 и това, което се случва, е, че това винаги започва от ред 2, но след това се спуска към текущия ред. Така че, когато погледнете как работи това, когато се копира, ние винаги започваме ред 2, но слизаме до текущия ред и това стана любимият ми метод. Бях като, о, това е много по-сложно и когато влезем в опциите на Excel, отидете в раздела Формули и изберете R1C1 в Референтен стил. Добре, вижте, R1C1, всички тези формули са абсолютно еднакви по целия път надолу. Не знам дали разбирате R1C1, просто е добре да знаете, че имаме еднакви формули R1C1 докрай.
Нека да се върнем. Така че този метод тук е методът, който ми хареса, докато докато Чарлз Уилямс, Excel MBP от Англия, който има невероятна книга за скоростта на формулата, скоростта на формулата на Excel, напълно развенча този метод. Този метод, да речем, че имате 10 000 реда, всяка формула разглежда две препратки. И така, гледате 20 000 препратки, но тази, тази гледа на две, тази на три, тази на четири, тази на пет и последната на 10 000 препратки, и е ужасно по-бавна и затова спрях да използвам този метод.
След това продължавам да чета Зак в книгата на Кевин Джоунс за таблиците на Excel и откривам още един проблем с този метод. Така че една от полезните функции, които предлагат таблиците, е „автоматичното форматиране и поддържането на формули се добавят, премахват, сортират и филтрират“. Добре, това е цитат от неговата книга. И за да добавите ред към таблица, просто отидете до последната клетка на таблицата и натиснете раздела. Така че всичко работи тук. Направо сме до 70, това е страхотно и след това A104 и ще сложа 100 тук. Добре, така че 70 трябва да се промени на 170 и се променя, но тези 70 изобщо не е трябвало да се променят. Добре 68 + 2 не е 170. Ще го направя отново. A 104 и поставете още сто в последния е прав. Тези двамата не са прави. Добре, имаме някаква странна ситуация, която акокато използвате тази формула и преобразувате в таблица, започнете да добавяте редове, текущата сума няма да работи. Колко лошо е това?
Добре, така че Зак предлага две работни области и и двете изискват малко знания за това как работят референциите на структурата. Просто ще имаме нова колона тук и ако исках да направя количество, равно количество, нали, така че = (@ Qty) казва количество в този ред. О, готино, ами има и друг вид референция, където използваме Qty без @. Виж това. Така че = SUM (INDEX ((Qty), 1: (@ Qty)) означава всички количества и ние ще кажем, че искаме да СБОРИМ от първото количество, така че (INDEX ((Qty), 1 казва първа стойност тук, до текущото количество редове и това използва наистина специална версия на индекса, когато индексът е последван от двоеточие, той всъщност се променя на препратка към клетка. от, ние "ще трябва да разгледате всяка отделна препратка, така че когато получите 10 000 реда от това, ще отиде наистина, наистина бавно.
Зак има още едно решение, което не нарушава проблема на Чарлз Уилямс, но използва страховития OFFSET. OFFSET е променлива функция, така че всеки път, когато изчислите нещо, OFFSET ще се преизчисли и всичко надолу от OFFSET ще се преизчисли. Това е просто чудесен начин напълно, напълно да объркате вашите формули и това, което прави, се казва, ние вземаме сумата от този ред, качвайки се един ред, над нула колони и така това, което правим, казва: вземете общото от предишния ред и след това добавяме към него количеството от този ред. Добре, така че сега всичко разглежда по две препратки всеки път, но за съжаление OFFSET въвежда летливи функции.
Е, има го, повече, отколкото някога сте искали да знаете за Running Totals. Предполагам, че последното ми мнение тук е да използвам този метод, защото изглежда само два. Една и съща формула по целия път надолу и вашите препратки към структурирани таблици ще работят.
За това проучване и 39 други наистина добри съвети вижте тази книга XL, 40-те най-добри съвета на Excel за всички времена.
Recap for this episode we talked about three ways to do running totals. The first method has a different formula, row 2, than all the other rows. It's equal left in row 2 and then equal left plus up in rows 3 through N, but if you try and just use that same formula, equal left plus up, all the way down, how you're going to get a #Value Error. So =SUM(Up,Left), which is previous total, plus this roadmap, that works great, no Value Errors and then the expanding range which I use to love. They're cool, but until I read Charles Williams white paper on Excel form of speed. Then I started to hate these expanding references. It also has a problem when you use CTRL T and add new rows. Excel can't figure out how to expand that formula, how to add new rows. I love this tip go to the very last cell in the table and press Tab, that will add a new row and then we talked about some structured referencing, where we're using quantity in this row and then all quantities. =SUM(OFFSET((@Total),-1,00,(@Qty)).
Добре, искам да благодаря на Зак, че допринесе с този съвет. Искам да ви благодаря, че се отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2004.xlsx
