1-әдіс. Формулалар
Жылыту үшін ең қарапайым нұсқадан бастайық - формулалар. Егер бізде кіріс ретінде күні бойынша сұрыпталған шағын кесте болса, онда жеке бағандағы орындалатын жиынтықты есептеу үшін бізге қарапайым формула қажет:
Мұндағы басты ерекшелік – SUM функциясының ішіндегі диапазонды күрделі бекіту – диапазонның басына сілтеме абсолютті (доллар белгілерімен), ал соңына дейін – салыстырмалы (долларсыз) жасалады. Сәйкесінше, формуланы бүкіл бағанға көшіргенде, біз қосындысын есептейтін кеңейту ауқымын аламыз.
Бұл тәсілдің кемшіліктері айқын:
- Кестені күні бойынша сұрыптау керек.
- Деректермен жаңа жолдарды қосқанда, формуланы қолмен кеңейту керек болады.
2-әдіс. Жиынтық кесте
Бұл әдіс сәл күрделірек, бірақ әлдеқайда жағымды. Ал шиеленістіру үшін анағұрлым маңызды мәселені қарастырайық – деректердің 2000 жолынан тұратын кестені қарастырайық, мұнда күн бағанасы бойынша сұрыптау жоқ, бірақ қайталанулар бар (яғни біз бір күнде бірнеше рет сата аламыз):
Біз бастапқы кестені «ақылды» (динамикалық) пернелер тіркесіміне түрлендіреміз Ctrl+T немесе команда Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу), содан кейін оған пәрмен арқылы жиынтық кесте құрастырамыз Кірістіру – жиынтық кесте (Кірістіру — жиынтық кесте). Біз жиынтықта жолдар аймағына күнді және құндылықтар аймағында сатылған тауарлардың санын енгіземіз:
Егер сізде Excel бағдарламасының ескі емес нұсқасы болса, күндер автоматты түрде жылдар, тоқсандар және айлар бойынша топтастырылатынын ескеріңіз. Егер сізге басқа топтау қажет болса (немесе ол мүлдем қажет болмаса), оны кез келген күнді тінтуірдің оң жақ түймешігімен басып, пәрмендерді таңдау арқылы түзете аласыз. Топтау / Топтан шығару (Топтастыру / Топтан шығару).
Нәтижелерді нүктелер бойынша және орындалатын жиынтықты бөлек бағанда көргіңіз келсе, өрісті мән аймағына тастау мағынасы бар. Сатылды өрістің телнұсқасын алу үшін тағы да – онда біз орындалған қорытындылардың дисплейін қосамыз. Ол үшін өрісті тінтуірдің оң жақ түймешігімен басып, пәрменді таңдаңыз Қосымша есептеулер – Жиынтық жиынтық (Мәндерді көрсету — Іске қосу жиындары):
Мұнда сіз сондай-ақ пайыздық мөлшерлемеде өсу сомасын таңдауға болады, ал келесі терезеде жинақтау жүргізілетін өрісті таңдауыңыз керек – біздің жағдайда бұл күн өрісі:
Бұл тәсілдің артықшылықтары:
- Деректердің үлкен көлемі тез оқылады.
- Ешқандай формулаларды қолмен енгізу қажет емес.
- Бастапқы деректерді өзгерту кезінде тінтуірдің оң жақ түймешігімен немесе Деректер – Барлығын жаңарту пәрменімен қорытындыны жаңарту жеткілікті.
Кемшіліктер бұл қорытындылау фактісінен туындайды, яғни онда сіз қалаған нәрсені жасай алмайсыз (жолдарды кірістіру, формулаларды жазу, кез келген диаграммаларды құру және т.б.) енді жұмыс істемейді.
3-әдіс: Power Query
Пәрменді пайдаланып Power Query сұрау өңдегішіне бастапқы деректері бар «ақылды» кестемізді жүктеп алайық Деректер – Кестеден/Ауқымнан (Деректер — кестеден/ауқымнан). Excel бағдарламасының соңғы нұсқаларында, айтпақшы, ол өзгертілді - қазір ол аталады Жапырақтарымен (Парақтан):
Содан кейін біз келесі қадамдарды орындаймыз:
1. Пәрмен арқылы кестені күн бағаны бойынша өсу ретімен сұрыптаңыз Өсуі бойынша сұрыптау кесте тақырыбындағы сүзгі ашылмалы тізімінде.
2. Біраз уақыттан кейін орындалатын жиынтықты есептеу үшін бізге реттік жол нөмірі бар көмекші баған қажет. Оны пәрменмен қосамыз Баған қосу – Индекс бағанасы – 1-ден (Бағанды қосу — Индекс бағаны — 1 бастап).
3. Сондай-ақ, орындалатын жиынтықты есептеу үшін бағанға сілтеме қажет Сатылды, онда біздің жинақталған деректеріміз жатыр. Power Query бағдарламасында бағандар тізімдер (тізім) деп те аталады және оған сілтеме алу үшін баған тақырыбын тінтуірдің оң жақ түймешігімен басып, пәрменді таңдаңыз. Толығырақ (Егжей-тегжейлі көрсету). Бізге қажет өрнек алдыңғы қадамның атынан тұратын формула жолында пайда болады #"Индекс қосылды", қай жерден кестені және баған атын аламыз [Сатылымдар] төртбұрышты жақшадағы осы кестеден:
Бұл өрнекті әрі қарай пайдалану үшін алмасу буферіне көшіріңіз.
4. Қажетсіз басқа соңғы қадамды жойыңыз Сатылды және оның орнына пәрмен арқылы орындалатын жиынтықты есептеу үшін есептелген бағанды қосыңыз Баған қосу – теңшелетін баған (Баған қосу — реттелетін баған). Бізге қажет формула келесідей болады:
Мұнда функция Тізім.Ауқым бастапқы тізімді алады (баған [Сату]) және одан элементтерді біріншіден бастап шығарады (формулада бұл 0, өйткені Power Query жүйесінде нөмірлеу нөлден басталады). Шығарылатын элементтер саны - біз бағаннан алатын жол нөмірі [Индекс]. Осылайша, бірінші жолға арналған бұл функция бағанның бір бірінші ұяшығын ғана қайтарады Сатылды. Екінші жол үшін – қазірдің өзінде алғашқы екі ұяшық, үшінші үшін – алғашқы үш және т.б.
Ал, содан кейін функция Тізім.сома алынған мәндерді қосады және біз әр жолда барлық алдыңғы элементтердің қосындысын аламыз, яғни жиынтық жиынтық:
Бізге енді қажет емес Индекс бағанын жою және нәтижелерді Басты - Жабу және жүктеп салу пәрменімен Excel бағдарламасына кері жүктеп салу қалады.
Мәселе шешілді.
Жылдам және қаһарлы
Негізінде, мұны тоқтатуға болатын еді, бірақ жақпада кішкентай шыбын бар - біз жасаған сұраныс тасбақа жылдамдығымен жұмыс істейді. Мысалы, менің ең әлсіз емес компьютерімде бар болғаны 2000 жолдан тұратын кесте 17 секундта өңделеді. Егер көбірек деректер болса ше?
Жылдамдау үшін арнайы List.Buffer функциясы арқылы буферлеуді қолдануға болады, ол оған аргумент ретінде берілген тізімді (тізімді) ЖЖҚ-ға жүктейді, бұл болашақта оған қол жеткізуді айтарлықтай жылдамдатады. Біздің жағдайда, 2000 жолдық кестенің әрбір жолындағы орындалатын жиынтықты есептеу кезінде Power Query қол жеткізуге тиіс #"Қосылған индекс"[Сатылған] тізімін буферлеу мағынасы бар.
Бұл әрекетті орындау үшін, Негізгі қойындысындағы Power Query өңдегішінде Power Query ішіне ендірілген M тіліндегі сұрауымыздың бастапқы кодын ашу үшін Кеңейтілген өңдегіш түймесін (Басты бет – Кеңейтілген өңдегіш) басыңыз:
Содан кейін айнымалысы бар жолды қосыңыз Менің тізімім, оның мәні буферлеу функциясы арқылы қайтарылады және келесі қадамда тізімге шақыруды осы айнымалымен ауыстырамыз:
Осы өзгертулерді енгізгеннен кейін біздің сұрауымыз айтарлықтай жылдам болады және 2000 жолдық кестені небәрі 0.3 секундта жеңеді!
Тағы бір нәрсе, солай ма? 🙂
- Парето диаграммасы (80/20) және оны Excel бағдарламасында құру жолы
- Мәтінде кілт сөзді іздеу және Power Query жүйесінде сұрау буферлеу