Ова епизода приказује три начина за извођење укупних резултата.
Текући укупан износ је, за листу нумеричких вредности, збир вредности од првог реда до реда текућег збира. Уобичајена употреба текућег укупног броја је у регистру чековних књижица или књиговодственом листу. Постоји много начина за стварање текућег укупног броја, од којих су два описана у наставку.
Најједноставнија техника је додавање укупног броја из горњег реда у сваки ред вредности у ред. Дакле, прва формула у реду 2 је:
=SUM(D1,C2)
Разлог због којег користимо функцију СУМ је тај што у првом реду гледамо заглавље у горњем реду. Ако користимо једноставнију, интуитивнију формулу =D1+C2тада ће се генерисати грешка јер је вредност заглавља текст насупрот нумеричкој. Магија је у томе што СУМ функција занемарује текстуалне вредности, које се додају као нулте вредности. Када се формула копира доле у све редове у којима се жели текући зброј, референце ћелија се одговарајуће прилагођавају:
Друга техника такође користи функцију СУМ, али свака формула сабира све вредности од првог реда до реда који приказује укупни број. У овом случају користимо знак долара ($) да би прва ћелија у референци постала апсолутна референца, што значи да није прилагођена приликом копирања:
Сортирање и брисање редова не утиче на обе технике, али приликом уметања редова формула мора бити копирана у нове редове.
Екцел 2007 представио је Табелу која представља поновну примену Списка у програму Екцел 2003. Табеле су увеле низ врло корисних функција за табеле података, као што су форматирање, сортирање и филтрирање. Увођењем Табела такође смо добили нови начин референцирања делова Табеле. Овај нови стил референцирања назива се структурирано референцирање.
Да бисмо горњи пример претворили у Табелу, бирамо податке које желимо да уврстимо у Табелу и притиснемо Цтрл + Т. Након приказивања упита који тражи да потврдимо опсег Табеле и да ли постоје заглавља, Екцел претвара податке у форматирану табелу:
Имајте на уму да формуле које смо раније унели остају исте.
Једна од корисних функција које нуди Табеле је аутоматско форматирање и одржавање формула док се редови додају, уклањају, сортирају и филтрирају. На одржавање формуле ћемо се посебно фокусирати и што може бити проблематично. Да би табеле могле да раде док се њима манипулише, Екцел користи израчунате колоне које су колоне са формулама као што је колона Д у горњем примеру. Када се додају нови редови додају на дно, Екцел аутоматски попуњава нове редове „подразумеваном“ формулом за ту колону. Проблем са горњим примером је тај што се Екцел збуњује са стандардним формулама и не обрађује их увек правилно. Ово се очитује када се на дно Табеле додају нови редови (одабиром доње десне ћелије у Табели и притиском на ТАБ):
Овај недостатак се решава коришћењем новијег структурираног референцирања. Структурирано референцирање елиминише потребу за референцама на одређене ћелије помоћу стила референцирања А1 или Р1Ц1 и уместо тога користи имена колона и друге кључне речи за идентификацију и референцирање делова табеле. На пример, да бисмо креирали исту горе наведену формулу за извођење, али користећи структурирано референцирање, имамо:
=SUM(INDEX((Sales),1):(@Sales))
У овом примеру имамо референцу на назив колоне „Продаја“, заједно са знаком ат (@) за референцу на ред у колони у којој се налази формула која је такође позната и као тренутни ред.
Да бисте применили први пример изнад где смо додали текућу укупну вредност у претходном реду износу продаје у тренутном реду, можете да користите функцију ОФФСЕТ:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Ако су износи коришћени за израчунавање текућег укупног износа у две колоне, на пример један за „Дуговања“ и један за „Кредити“, онда је формула:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Овде користимо функцију ИНДЕКС да бисмо лоцирали ћелије кредита и задужења првог реда и сумирали целу колону до вредности текућег реда. Текући укупан износ је збир свих кредита до, укључујући и тренутни ред, умањен за збир свих терећења до, укључујући и тренутни ред.
За више информација о структурираним референцама посебно и Табелама уопште, препоручујемо књигу Екцел Таблес: Комплетни водич за креирање, употребу и аутоматизацију листа и табела Зацка Баррессеа и Кевина Јонес-а.
Када сам питао читаоце да гласају за њихове омиљене савете, табеле су биле популарне. Хвала Петеру Алберту, Снорре Еикеланд-у, Нанци Федерице, Цолин Мицхаел-у, Јамес Е. Моеде-у, Кеиур-у Пател-у и Паулу Петон-у што су предложили ову функцију. Петер Алберт написао је бонус за Реадабле Референцес. Зацк Баррессе је написао бонус савет за Руннинг Тоталс. Четири читаоца су предложила коришћење ОФФСЕТ-а за стварање проширених опсега за динамичке табеле: Цхарлеи Баак, Дон Кновлес, Францис Логан и Цецелиа Риеб. Табеле у већини случајева сада раде исту ствар.
Погледајте видео
- Ова епизода приказује три начина за извођење укупних резултата
- Прва метода има различиту формулу у 2. реду од свих осталих редова
- Прва метода је = Лево у реду 2 и = Лево + Горе у редовима 3 до Н
- Ако покушате да користите исту формулу, добићете грешку #Валуе са = Тотал + Нумбер
- Метод 2 користи
=SUM(Up,Left)или=SUM(Previous Total,This Row Amount) - СУМ игнорише текст тако да не добијете грешку ВАЛУЕ
- Метод 3 користи опсег који се шири:
=SUM(B$2:B2) - Опсези који се шире су хладни, али су спори
- Прочитајте техничку књигу Цхарлес Виллиамса о Екцел Формула Спеед
- Трећи метод је проблем када користите Цтрл + Т и додате нове редове
- Екцел не може да схвати како да напише формулу
- Заобилазна решења захтевају одређено знање о структурираном референцирању у табелама
- Заобилазно решење 1 је споро
=SUM(INDEX((Qty),1):(@Qty)) - Заобилазно решење 2 је променљиво
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Кти) се односи на Кти у овом реду
- (Кти) се односи на све вредности Кти
Видео транскрипт
Научите Екцел за подцаст, епизода 2004 - Руннинг Тоталс
Подставићу целу ову књигу. Кликните на И у горњем десном углу да бисте се претплатили.
Хеј, добродошли назад на мрежу мистичних ћелија. Ја сам Билл Јелен. Сада о овој теми у књизи, допринео ми је мој пријатељ Зацх Парисе. Кад смо већ код Екцел табела, Зацх је светски стручњак за Екцел табеле. Написао је књигу о Екцел табелама, али прво хајде да разговарамо о извођењу укупних вредности, а не у табелама.
Дакле, кад размишљам о укупним збројевима, постоје три различита начина за извођење укупних износа, а начин на који сам увек започео је у првом реду који само кажете, пренесите вредност. Дакле, изједначите све што је лево од мене. Дакле, овај формат овде је само = Б2. Ово су сви текстови формула овде у десном углу, тако да видите шта користимо, а онда од тамо нагоре, то је једноставна мала формула једнака претходној вредности, плус тренутна вредност десно и копирајте то доле , али сада знате, имамо овај проблем што су му биле потребне две различите формуле и знате у савршеној ситуацији да имате потпуно исту формулу до краја, а разлог зашто морамо да имамо другачију формулу тамо у првом реду је да када покушате да додате једнако 7 плус реч тотал, то је грешка у вредности,али цоол радник овде је да не користи само лево плус горе, већ да користи = (СУМ) претходне вредности плус количина у овом реду, и видите да је неки довољно далеко да се игноришу текстови. Тачно, тако да омогућава исту формулу. скроз.
У реду, тако је било када сам почињао да користим Екцел, користио сам то, а онда сам открио опсег који се шири, опсег који се шири каже да ћемо урадити Л $ 2: Л2 и оно што се дешава је да ово увек почиње у реду 2 али онда се спушта на тренутни ред. Дакле, када погледате како ово функционише када се копира, увек смо започињали ред 2, али спуштамо се на тренутни ред и ово је постао мој омиљени метод. Био сам као, ох, ово је много софистицираније и када уђемо у Екцел опције, идемо на картицу Формуле и одаберите Р1Ц1 у Референтном стилу. Добро, Р1Ц1, све ове формуле су потпуно исте до краја. Не знам да ли разумете Р1Ц1, добро је знати да имамо идентичне Р1Ц1 формуле до краја.
Вратимо се назад. Дакле, ова метода овде је метода која ми се свидела, све док Цхарлес Виллиамс, Екцел МБП из Енглеске, који има невероватан рад о брзини формуле, Екцел брзини формуле, није потпуно разоткрио ову методу. Ова метода, рецимо да имате 10.000 редова, свака појединачна формула гледа на две референце. Дакле, гледате 20.000 референци, али ова, ово гледа на двије, ово гледа на три, ово гледа на четири, ово гледа на пет, а посљедња гледа на 10.000 референци, и ужасно је спорије и тако сам престао да користим ову методу.
Затим настављам да читам Зацка у књизи Кевина Јонеса о Екцел табелама и откривам још један проблем са овом методом. Дакле, једна од корисних карактеристика које табеле нуде је „аутоматско форматирање и редови одржавања формула додају се, уклањају, сортирају и филтрирају“. У реду, то је цитат из његове књиге. А да бисте додали ред у табелу, само идите до последње ћелије на табели и притисните тастер. Дакле, овде све функционише. Тачно смо на 70, то је сјајно, а онда А104 и овде ћу ставити 100. У реду, тако да би 70 требало да се промени на 170 и то се и догоди, али ових 70 уопште није требало да се промени. У реду 68 + 2 није 170. Урадићу то поново. А 104 и ставите још сто у последњи је тачно. Ово двоје нису у праву. У реду, имамо чудну ситуацију да акопоново користите ову формулу и претворите у табелу и почнете да додајете редове, укупни број неће функционисати. Колико је то лоше?
У реду, дакле, Зацк нуди два начина рада и обојици је потребно мало знања о томе како функционишу референце на структуру. Овде ћемо само имати нову колону и ако бих желео да направим количину, једнаку количину, тачно, тако да = (@ Кти) каже количина у овом реду. Ох, супер, постоји још једна врста референце где користимо количину без знака @. Види ово. Дакле = СУМ (ИНДЕКС ((Количина), 1: (@ Количина)) значи све количине и рећи ћемо да желимо ЗУМАТИ од прве количине, па (ИНДЕКС ((Количина), 1 каже прва вредност овде, до тренутне количине реда, а ово користи заиста посебну верзију индекса, када индекс прати двотачка, он се заправо мења у референцу ћелије. У реду, тако да ово решење нажалост крши правило Цхарлес Виллиамс од, мимораћете да погледате сваку поједину референцу, па кад добијете 10.000 редова, ово ће ићи јако, јако споро.
Зацх има још једно заобилазно решење које не крши проблем Цхарлеса Виллиамса, али користи страшни ОФФСЕТ. ОФФСЕТ је испарљива функција, па ће сваки пут када нешто израчунате, ОФФСЕТ поново израчунати и све доње линије из ОФФСЕТ-а ће се израчунати. То је само одличан начин да у потпуности, потпуно забрљате своје формуле, и шта ово ради, каже, узимамо укупан број из овог реда, прелазећи за један ред, преко нула колона, и оно што то ради каже: дохватите укупан износ из претходног реда и тада му додајемо количину из овог реда. У реду, сада се све гледа на две референце сваки пут, али нажалост ОФФСЕТ уводи променљиве функције.
Па, ето, више него што сте икада желели да знате о Руннинг Тоталс. Претпостављам да је моје коначно мишљење да користим ову методу, јер изгледа само две. Иста формула до краја и ваше структуриране референце табеле ће радити.
За ово истраживање и још 39 заиста добрих савета погледајте ову књигу КСЛ, 40 најбољих Екцел савета свих времена.
Укратко о овој епизоди говорили смо о три начина за извођење укупних резултата. Прва метода има другачију формулу, ред 2, од свих осталих редова. Једнако је лево у реду 2, а затим једнако лево плус горе у редовима 3 до Н, али ако покушате и само употребите ту исту формулу, једнако лево плус горе, скроз доле, како ћете добити грешку #Валуе . Дакле = СУМ (горе, лево), што је претходно укупно, плус овај путоказ, који одлично функционише, без грешака у вредности, а затим шири опсег који волим да волим. Они су у реду, али све док не прочитам белешке Цхарлеса Виллиамса о Екцел облику брзине. Тада сам почео да мрзим ове референце које се шире. Има проблема и када користите ЦТРЛ Т и додате нове редове. Екцел не може да схвати како да прошири ту формулу, како да дода нове редове. Волим да овај савет оде до последње ћелије у табели и притисне Таб,који ће додати нови ред, а затим смо разговарали о структурираном референцирању, где користимо количину у овом реду, а затим све количине. = СУМ (ПОКРЕТ ((@ Укупно), - 1,00, (@ количина)).
Ок, желим да захвалим Зацху на томе што је дао свој савет. Желим да вам захвалим што сте навратили. Видимо се следећи пут за још једно емитовање од.
Скини докуменат
Преузмите датотеку узорка овде: Подцаст2004.клск
