Ez az epizód három módszert mutat be a futóösszegek végrehajtására.
A futóösszeg a numerikus értékek listája esetében az értékek összege az első sortól a futóösszeg soráig. A futó összeg általános felhasználása csekkfüzet-nyilvántartásban vagy könyvelési lapon található. Sokféle módon hozhat létre futót, amelyek közül kettőt az alábbiakban ismertetünk.
A legegyszerűbb módszer az, hogy minden sorban hozzáadjuk a fenti sor futási összegét a sor értékéhez. Tehát a 2. sor első képlete a következő:
=SUM(D1,C2)
Azért használjuk a SUM függvényt, mert az első sorban a fenti sor fejlécét nézzük. Ha az egyszerűbb, intuitívebb képletet használjuk, =D1+C2akkor hiba keletkezik, mert a fejléc értéke szöveg vagy numerikus. A varázslat az, hogy a SUM függvény figyelmen kívül hagyja a szövegértékeket, amelyeket nulla értékként adunk hozzá. Ha a képletet lemásolja az összes olyan sorra, amelyben a futás teljes összege szükséges, a cellahivatkozások ennek megfelelően kerülnek beállításra:
A másik technika a SUM függvényt is használja, de mindegyik képlet összesíti az összes értéket az első sortól a futó összeget megjelenítő sorig. Ebben az esetben egy dollárjelet ($) használunk arra, hogy a referencia első cellája abszolút referencia legyen, ami azt jelenti, hogy másoláskor nem módosul:
A sorok rendezése és törlése mindkét technikát nem befolyásolja, de sorok beszúrásakor a képletet át kell másolni az új sorokba.
Az Excel 2007 bevezette a táblázatot, amely a lista újratelepítése az Excel 2003 programban. A táblázatok számos nagyon hasznos funkciót mutattak be az adattáblák számára, például formázást, rendezést és szűrést. A Táblázatok bevezetésével új módszert is kaptunk a táblázat részeinek hivatkozására. Ezt az új hivatkozási stílust strukturált hivatkozásnak nevezzük.
A fenti példa táblázattá alakításához kiválasztjuk azokat az adatokat, amelyeket be akarunk venni a táblázatba, és megnyomjuk a Ctrl + T billentyűkombinációt. Miután megjelenített egy felszólítást, amelyben megkértük, hogy erősítsük meg a táblázat tartományát, és hogy vannak-e létező fejlécek, az Excel konvertálja az adatokat formázott táblázatba:
Ne feledje, hogy a korábban megadott képletek ugyanazok maradnak.
A Táblák egyik hasznos funkciója az automatikus formázás és képletkarbantartás, amikor sorokat adnak hozzá, távolítanak el, rendeznek és szűrnek. Különösen a képlet karbantartására kell összpontosítanunk, és ez problematikus lehet. A táblázatok működése közben az Excel kiszámított oszlopokat használ, amelyek olyan képletekkel rendelkező oszlopok, mint a fenti példa a D oszlop. Amikor új sorokat illeszt be az aljára, az Excel automatikusan feltölti az új sorokat az adott oszlop „alapértelmezett” képletével. A fenti példa problémája az, hogy az Excel összekeveredik a szokásos képletekkel, és nem mindig kezeli őket helyesen. Ez nyilvánvalóvá válik, amikor új sorokat adnak a táblázat aljára (a táblázat jobb alsó cellájának kiválasztásával és a TAB megnyomásával):
Ezt a hiányosságot egy újabb strukturált hivatkozás segítségével lehet megoldani. A strukturált hivatkozás kiküszöböli az adott cellák hivatkozásának szükségességét az A1 vagy R1C1 hivatkozási stílus használatával, ehelyett oszlopneveket és más kulcsszavakat használ a táblázat részeinek azonosításához és hivatkozásához. Például ugyanazon futó teljes képlet létrehozásához, amelyet fent használtunk, de strukturált hivatkozásokkal rendelkezünk:
=SUM(INDEX((Sales),1):(@Sales))
Ebben a példában hivatkozunk az oszlop nevére, „Értékesítés”, valamint az at jelre (@) az oszlop azon sorának hivatkozására, amelyben a képlet található, amely az aktuális sor néven is ismert.
A fenti első példa megvalósításához, ahol az előző sor futó összértékét hozzáadtuk az aktuális sor eladási összegéhez, használhatja az OFFSET funkciót:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Ha a futó összeg kiszámításához felhasznált összegek két oszlopban vannak, például egy a „Tartozások” és a „Kreditek” esetében, akkor a képlet:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Itt az INDEX függvény segítségével keressük meg az első sor jóváírási és terhelési celláit, és összegezzük az egész oszlopot az aktuális sor értékeivel együtt. A futóösszeg az összes jóváírás összege az aktuális sorig, beleértve az aktuális sorig számított összes terhelés összegét.
A strukturált hivatkozásokkal és általában a Táblázatokkal kapcsolatos további információkért javasoljuk az Zack Barresse és Kevin Jones Excel táblázatok: A teljes útmutató a listák és táblázatok létrehozásához, használatához és automatizálásához című könyvet.
Amikor arra kértem az olvasókat, hogy szavazzanak a kedvenc tippjeikre, a táblázatok népszerűek voltak. Köszönet Peter Albertnek, Snorre Eikelandnek, Nancy Federice-nek, Colin Michael-nek, James E. Moede-nek, Keyur Patel-nek és Paul Peton-nak, aki ezt a funkciót javasolta. Peter Albert írta az Olvasható referenciák bónusztippet. Zack Barresse írta a Running Totals bónusz tippet. Négy olvasó javasolta az OFFSET használatát a dinamikus diagramok bővülő tartományainak létrehozására: Charley Baak, Don Knowles, Francis Logan és Cecelia Rieb. A táblázatok most ugyanazt csinálják a legtöbb esetben.
Videót nézni
- Ez az epizód három módszert mutat be a futóösszegek elvégzésére
- Az első módszer más képlettel rendelkezik a 2. sorban, mint az összes többi sor
- Az első módszer = Balra a 2. sorban és = Balra + Felre a 3. – N. Sorban
- Ha ugyanazt a képletet próbálja használni, #Value hibát kap az = Összesen + Számmal
- A 2. módszer a
=SUM(Up,Left)vagy=SUM(Previous Total,This Row Amount) - A SUM figyelmen kívül hagyja a szöveget, így nem kap VALUE hibát
- A 3. módszer egy bővülő tartományt használ:
=SUM(B$2:B2) - A tartományok bővítése jó, de lassú
- Olvassa el az Charles Formula Speed című Charles Williams-kiadványt
- A harmadik módszer problémát jelent, ha a Ctrl + T billentyűkombinációt használja, és új sorokat ad hozzá
- Az Excel nem tudja kitalálni, hogyan kell írni a képletet
- A kerülő megoldások némi ismeretet igényelnek a strukturált hivatkozásokról a táblázatokban
- Az 1. megoldás a lassú
=SUM(INDEX((Qty),1):(@Qty)) - A 2. megoldás az illékony
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) erre a sorra vonatkozik
- (Mennyiség) az összes Mennyiség értékre vonatkozik
Videó átirat
Ismerje meg az Excel for Podcast, 2004. epizód - Összesen futás című részt
Podcastolom ezt az egész könyvet. A feliratkozáshoz kattintson a jobb felső sarokban található I gombra.
Üdvözlöm a misztikus sejtek közvetítésében. Bill Jelen vagyok. Most ebben a témában a könyvben Zach Parise barátom közreműködött. Beszéljünk az Excel táblákról: Zach a világ szakértője az Excel táblákban. Könyvet írt az Excel táblákról, de először beszéljünk arról, hogy az összesítést nem táblázatokban futtatjuk.
Tehát, amikor a futóösszegekre gondolok, három különböző módja van a futóösszegek elvégzésének, és az a módszer, amellyel mindig elindultam, az első sorban van, amit csak mondasz, hozd át az értéket. Tehát egyenlő, bármi van tőlem balra. Rendben, így ez a formátum itt csak = B2. Ezek mind a jobb oldali sarokban található képletszövegek, így láthatja, hogy mit használunk, majd onnantól lefelé, ez egy egyszerű kis képlet, amely megegyezik az előző értékkel, plusz a jelenlegi érték jobbra, és lemásolja azt , de most már tudod, hogy ezzel a problémával két különböző képletet igényeltünk, és tökéletes helyzetben pontosan ugyanazt a képletet használod, és annak az oka, hogy más képlettel kell rendelkeznünk az első sorban: hogy amikor megpróbálsz egyenlő 7-et plusz az össz szó hozzáadni, akkor ez egy értékhibade a hűvös munkás itt nem csak a balra felfelé, hanem az előző érték plusz (SUM) értékének és a sorban lévő mennyiségnek a használata, és néhány olyan elég messze van a szövegek figyelmen kívül hagyásához. Úgy, hogy ugyanazt a képletet engedélyezze. egészen lefelé.
Rendben, akkor, amikor elkezdtem az Excel használatát, ezt használtam, majd felfedeztem a bővülő tartományt, a bővülő tartomány szerint L $ 2: L2-t fogunk csinálni, és ami történik, ez mindig a 2. sorból indul, de aztán az aktuális sorra megy le. Tehát, ha megnézzük, hogyan működik ez a másoláskor, mindig a 2. sort kezdtük, de lemegyünk az aktuális sorra, és ez lett a kedvenc módszerem. Úgy voltam vele, hogy ó, ez sokkal kifinomultabb, és amikor belemegyünk az Excelbeállításokba, lépjünk a Képletek lapra, és válasszuk az R1C1 lehetőséget a Referencia stílusban. Rendben, R1C1, ezek a képletek teljesen azonosak egészen lefelé. Nem tudom, érted-e az R1C1-et, csak jó tudni, hogy végig azonos R1C1-képletek vannak.
Menjünk vissza. Tehát ez a módszer itt az a módszer, ami tetszett, mígnem Charles Williams, az angliai Excel MBP, akinek csodálatos papírja van a képletsebességről, az Excel képletsebességéről, teljesen lebontotta ezt a módszert. Tegyük fel, hogy ez a módszer 10 000 sorral rendelkezik, minden képlet két referenciát vizsgál. Tehát 20 000 referenciát néz, de ez, ez kettőt, ez hármat, ez négyet, ez ötöt és az utolsó 10 000 referenciát, és borzasztóan lassabb és ezért felhagytam ezzel a módszerrel.
Aztán tovább olvasom Zacket Kevin Jones könyvében az Excel táblázatokról, és még egy problémát fedezek fel ezzel a módszerrel. Tehát az egyik hasznos szolgáltatás, amelyet a táblák kínálnak, az az, hogy „automatikus formázási és képletkarbantartási sorok kerülnek hozzáadásra, eltávolításra, rendezésre és szűrésre”. Rendben, ez egy idézet a könyvéből. Ha hozzá szeretne adni egy sort egy táblához, akkor egyszerűen lépjen az asztal legutolsó cellájába, és nyomja meg a tab billentyűt. Tehát itt minden működik. Pontosan 70-re vagyunk, ez fantasztikus, majd az A104, és ide beteszek egy 100-at. Rendben, úgy, hogy a 70-nek 170-re kell váltania, és így is van, de ennek a 70-nek egyáltalán nem kellett volna megváltoznia. Rendben 68 + 2 nem 170. Megismételem. A 104-es, és tegyen még százat az utolsóba, igaz. Ennek a kettőnek nincs igaza. Rendben, szóval furcsa helyzetünk van, hogy haújból ezt a képletet használja, és táblává konvertálja a sorok hozzáadását, a futás összege nem fog működni. Mennyire rossz ez?
Rendben, így Zack kétféle munkát kínál, és mindkettőhöz egy kis ismeretre van szükség a struktúra-referenciák működéséről. Éppen itt lesz egy új oszlopunk, és ha mennyiséget, egyenlő mennyiséget akarok csinálni, úgy, hogy = (@ Mennyiség) a mennyiséget mondja ebben a sorban. Ó, jó, van még egyfajta hivatkozás, ahol a Qty-t a @ nélkül használjuk. Ezt nézd meg. Tehát = SUM (INDEX ((Mennyiség), 1: (@ Mennyiség)) az összes mennyiséget jelenti, és azt fogjuk mondani, hogy az első mennyiségtől szeretnénk összegezni, tehát (INDEX ((Mennyiség), 1 azt mondja, az első érték itt, az aktuális sormennyiségig, és ez az index egy igazán speciális változatát használja, amikor az indexet kettőspont követi, akkor ez valóban cellahivatkozássá változik. Rendben, ez a megoldás sajnos sérti a Charles Williams szabályt a mimeg kell vizsgálnunk minden egyes referenciát, és amikor 10 000 sort kap, akkor ez nagyon, nagyon lassan fog menni.
Zachnek van egy másik megoldása, amely nem sérti a Charles Williams problémáját, de a rettegett OFFSET-et használja. Az OFFSET egy volatilis függvény, így minden alkalommal, amikor kiszámol valamit, az OFFSET újraszámol, és az OFFSET-től lefelé minden újraszámol. Ez csak egy nagyszerű módja annak, hogy teljesen, teljesen elcsavarja a képleteit, és hogy mit csinál ez, azt mondják, ebből a sorból vesszük az összeget, egy sorral feljebb haladva, nulla oszlop fölé, és amit mond, azt mondja: ragadd meg az előző sor összesítését, majd hozzáadjuk az ebből a sorból származó mennyiséget. Rendben, szóval, most minden alkalommal két referenciát nézünk, de sajnos az OFFSET volatilis funkciókat vezet be.
Nos, itt van, többet, mint amit valaha is szerettél volna tudni a Futó totálokról. Gondolom, itt a végső véleményem az, hogy ezt a módszert használjam, mert csak kettőnek tűnik. Végig ugyanaz a képlet, és a strukturált táblázati hivatkozások működni fognak.
Ehhez a felfedezéshez és 39 további nagyon jó tipphez olvassa el ezt az XL könyvet, minden idők 40 legnagyobb Excel-tippjét.
Összefoglalva ehhez az epizódhoz, a futási összesítés három módjáról beszéltünk. Az első módszer más képlettel, a 2. sorral rendelkezik, mint az összes többi sor. Ez egyenlő balra a 2. sorban, majd egyenlő balra plusz felfelé a 3-tól N-ig terjedő sorokban, de ha megpróbálja csak ugyanazt a képletet használni, egyenlő bal plusz felfelé, egészen lefelé, hogyan fog #Value hibát kapni . Tehát = SUM (Fel, Balra), amely az előző összesítés, plusz ez a nagyszerűen működő ütemterv, nincs Értékhiba, majd a bővülő tartomány, amelyet szeretni szoktam. Hűvösek, de amíg el nem olvastam Charles Williams fehér könyvét az Excel formájú sebességről. Aztán gyűlölni kezdtem ezeket a bővülő referenciákat. Az is problémát okoz, ha a CTRL T-t használja, és új sorokat ad hozzá. Az Excel nem tudja kitalálni, hogyan lehet kibővíteni ezt a képletet, hogyan lehet új sorokat hozzáadni. Szeretem, ha ez a tipp megy a táblázat legutolsó cellájába, és nyomja meg a Tab billentyűt,ez egy új sort fog hozzáadni, majd beszéltünk néhány strukturált hivatkozásról, ahol a mennyiséget használjuk ebben a sorban, majd az összes mennyiséget. = SUM (OFFSET ((@ Összesen), - 1,00, (@ Mennyiség)).
Oké, szeretném megköszönni Zachnak, hogy hozzájárult ahhoz a tipphez. Szeretném megköszönni, hogy megálltál. Legközelebb találkozunk egy újabb netcast-lal.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2004.xlsx
