Метод 1. Формулалар
Баштайлы, жылытуу үчүн, эң жөнөкөй варианты – формулалар. Эгерде бизде киргизүү катары датасы боюнча сорттолгон кичинекей таблица болсо, анда иштеп жаткан сумманы өзүнчө тилкеде эсептөө үчүн бизге элементардык формула керек:
Бул жердеги негизги өзгөчөлүк, SUM функциясынын ичиндеги диапазонун татаал бекитилиши – диапазонун башына шилтеме абсолюттук (доллар белгилери менен), ал эми аягына чейин – салыштырмалуу (долларсыз) жасалган. Демек, формуланы бүт мамычага көчүрүп жатканда, биз кеңейтүүчү диапазону алабыз, анын суммасын биз эсептейбиз.
Бул ыкманын кемчиликтери айдан ачык:
- Таблица датасы боюнча иреттелиши керек.
- Маалыматтар менен жаңы саптарды кошкондо, формула кол менен узартылышы керек болот.
Метод 2. Пивот таблица
Бул ыкма бир аз татаал, бирок алда канча жагымдуу. Ал эми курчутуу үчүн, келгиле, олуттуураак маселени карап көрөлү – 2000 катар маалыматтардын таблицасы, мында дата тилкеси боюнча сорттоо жок, бирок кайталануулар бар (б.а. биз ошол эле күнү бир нече жолу сата алабыз):
Биз баштапкы таблицабызды "акылдуу" (динамикалык) баскычтоптун жарлыгына айландырабыз Ctrl+T же команда Башкы бет – Таблица катары форматтоо (Башкы бет — Таблица катары формат), анан биз буйрук менен ага пивот таблицасын курабыз Кыстаруу – Пивот таблицасы (Киргизүү — Пивот таблицасы). Биз кыскача саптардын аймагына датаны жана баалуулуктар аймагында сатылган товарлардын санын коебуз:
Эсиңизде болсун, эгер сизде Excelдин анча эски эмес версиясы болсо, анда даталар автоматтык түрдө жылдар, кварталдар жана айлар боюнча топтолот. Эгер сизге башка топтоо керек болсо (же такыр кереги жок болсо), анда каалаган күндү оң баскыч менен чыкылдатып, буйруктарды тандоо менен аны оңдой аласыз Топтоо / Топтон чыгаруу (Топтоо / Группадан чыгаруу).
Эгерде сиз алынган жыйынтыктарды чекиттер боюнча да, иштеп жаткан жалпы сумманы да өзүнчө тилкеде көргүңүз келсе, анда талааны маани аймагына ыргытуунун мааниси бар. сатылган талаанын дубликатын алуу үчүн дагы - анда биз иштеп жаткан жыйынтыктардын дисплейин күйгүзөбүз. Бул үчүн, талааны оң баскыч менен чыкылдатып, буйрукту тандаңыз Кошумча эсептөөлөр – Жыйынтык жалпы (Маанилерди көрсөтүү — Иштеп жаткан жалпылар):
Ал жерден сиз ошондой эле пайыз катары суммаларды көбөйтүү опциясын тандай аласыз, жана кийинки терезеде сиз топтоо бара турган талааны тандооңуз керек - биздин учурда бул дата талаасы:
Бул ыкманын артыкчылыктары:
- Маалыматтардын чоң көлөмү тез окулат.
- Эч кандай формулаларды кол менен киргизүүнүн кереги жок.
- Булак маалыматтарын өзгөртүүдө, чычкандын оң баскычы менен же Маалымат – Баарын жаңылоо буйругу менен корутундуну жаңыртуу жетиштүү.
Кемчиликтери бул кыскача мазмунунан келип чыгат, демек, анда сиз каалаган нерсени кыла албайсыз (сызыктарды киргизүү, формулаларды жазуу, диаграммаларды түзүү ж.б.) мындан ары иштебей калат.
Метод 3: Power Query
Келиңиз, булак маалыматтары менен "акылдуу" таблицабызды Power Query суроо редакторуна буйрук аркылуу жүктөйбүз Берилиштер – Таблицадан/Аразиядан (Маалымат — Таблицадан/Аразиядан). Excelдин акыркы версияларында, демек, ал кайра аталды - азыр ал деп аталат Жалбырактары менен (Барактан):
Андан кийин биз төмөнкү кадамдарды жасайбыз:
1. Таблицаны өсүү тартибинде буйрук менен дата тилкеси боюнча иреттеңиз Өсүү боюнча сорттоо жадыбалдын башындагы чыпка ылдый түшүүчү тизмеде.
2. Бир аздан кийин, иштеп жаткан сумманы эсептөө үчүн, бизге катардын катар номери менен жардамчы тилке керек. Аны буйрук менен кошобуз Мамыча кошуу – Индекс тилкеси – 1дөн (Кошуу тилкеси — Индекс тилкеси — 1дөн).
3. Ошондой эле, иштеп жаткан сумманы эсептөө үчүн, мамычага шилтеме керек сатылган, анда биздин жалпыланган маалыматтар жатат. Power Queryде тилкелер тизмелер (тизме) деп да аталат жана ага шилтеме алуу үчүн мамычанын башын оң баскыч менен чыкылдатып, буйрукту тандаңыз. деталдаштыруу (Чоо-жайын көрсөтүү). Бизге керектүү туюнтма мурунку кадамдын аталышынан турган формула тилкесинде пайда болот #"Индекс кошулду", биз таблицаны жана тилкенин атын кайдан алабыз [Сатуу] чарчы кашаанын ичинде бул таблицадан:
Андан ары колдонуу үчүн бул сөз айкашын алмашуу буферине көчүрүңүз.
4. Керексиз акыркы кадамды жок кылыңыз сатылган жана анын ордуна команда менен иштеп жаткан сумманы эсептөө үчүн эсептелген тилкени кошуу Мамыча кошуу – Ыңгайлаштырылган тилке (Кошуу тилкеси — Ыңгайлаштырылган тилке). Бизге керектүү формула төмөнкүдөй болот:
Бул жерде функция List.Range баштапкы тизмени алат (мамыча [Сатуу]) жана биринчиден баштап андан элементтерди чыгарат (формулада бул 0, анткени Power Queryде номерлөө нөлдөн башталат). Калыпка алынуучу элементтердин саны - бул биз мамычадан алган сап номери [Индекс]. Ошентип, биринчи сап үчүн бул функция тилкенин бир биринчи уячасын гана кайтарат сатылган. Экинчи сап үчүн - буга чейин биринчи эки клетка, үчүнчү үчүн - биринчи үч, ж.б.
Анда функция Тизме.сум алынган маанилердин суммасын чыгарат жана биз ар бир сапта бардык мурунку элементтердин суммасын алабыз, башкача айтканда, жалпы сумма:
Бизге керек болбой калган Индекс тилкесин жок кылуу жана натыйжаларды Home – Жабуу жана жүктөө буйругу менен Excelге кайра жүктөө калды.
Маселе чечилди.
Тез жана каардуу
Негизи муну токтотсо болмок, бирок майдын ичинде кичинекей чымын бар – биз жараткан өтүнүч таш баканын ылдамдыгы менен иштейт. Мисалы, менин эң алсыз компьютеримде 2000 саптан турган таблица 17 секундада иштетилет. Көбүрөөк маалымат болсочу?
Ылдамдатуу үчүн атайын List.Buffer функциясын колдонуу менен буферлөөнү колдонсоңуз болот, ал аргумент катары ага берилген тизмени (тизмени) оперативдүү эс тутумга жүктөйт, бул келечекте ага жетүүнү абдан тездетет. Биздин учурда, 2000-саптык таблицабыздын ар бир сабында иштеп жаткан жалпы санын эсептөөдө Power Query кире турган #"Кошулган индекс"[Сатылды] тизмесин буферлөө мааниси бар.
Бул үчүн, Негизги өтмөктөгү Power Query редакторунда Өркүндөтүлгөн редактордун баскычын чыкылдатыңыз (Башкы бет - Өркүндөтүлгөн редактор) Power Query ичинде орнотулган М тилиндеги сурообуздун баштапкы кодун ачуу:
Анан ал жерде өзгөрмөлүү сызыкты кошуңуз MyList, анын мааниси буферлөө функциясы менен кайтарылат жана кийинки кадамда биз тизмеге чакырууну бул өзгөрмө менен алмаштырабыз:
Бул өзгөртүүлөрдү киргизгенден кийин, биздин сурообуз бир топ ылдамдайт жана 2000 саптык таблица менен 0.3 секунданын ичинде иштей алат!
Дагы бир нерсе, туурабы? 🙂
- Парето диаграммасы (80/20) жана аны Excelде кантип куруу керек
- Текстте ачкыч сөздөрдү издөө жана Power Queryде суроо буферлөө