PowerPivot қондырмасындағы біріктірулер

Біріктірулер — деректерді тасалаудың, жиынтықтаудың немесе топтаудың жолы. Кестелерден немесе басқа деректер көздерінен алынған өңделмеген деректермен бастағанда, деректер көбінесе аралас болады, яғни, көп мәліметтер болады, бірақ ол ешқалай реттелмеген немесе топталмаған болады. Бұл жиынтықтардың немесе құрылымның болмауы деректердегі үлгілерді анықтауды қиын етуі мүмкін. Деректерді үлгілеудің маңызды бөлігі — белгілі бір іскери сұраққа жауап ретінде үлгілерді жеңілдететін, абстракциялайтын немесе жиынтықтайтын біріктірулерді анықтау.

AVERAGE, COUNT, DISTINCTCOUNT, MAX, MIN немесе SUMсияқты функцияны пайдаланатын көптеген біріктірулер Автоқосынды мүмкіндігін пайдаланып есептелетін өрістерде автоматты түрде жасалады. AVERAGEX, COUNTX, COUNTROWS немесе SUMXсияқты біріктірулердің басқа түрлері кестені береді және олар үшін DAX өрнектерін пайдаланып жасалған формула қажет.

Power Pivot қондырмасындағы біріктірулерді түсіну

Біріктіру үшін топтарды таңдау

Деректерді біріктіргенде деректерді өнім, баған, аймақ немесе күн сияқты атрибуттар бойынша топтап, содан кейін топтағы барлық деректерде жұмыс істейтін формуланы жасайсыз. Мысалы, жыл үшін қорытындыны жасағанда, біріктіруді жасайсыз. Егер содан кейін осы жылдың алдыңғы жылға қатынасын жасап, бұларды пайыздар ретінде көрсетсеңіз, бұл біріктірудің басқа түрі болады.

Деректерді топтау жолын іскери мәселе анықтайды. Мысалы, біріктірулер келесі сұрақтарға жауап бере алады:

Сандар   Ай ішінде қанша транзакция болды?

Орташа мәндер    Сатушының осы айдағы орташа сатылымы қандай болды?

Ең төмен және ең жоғары мәндер    Сатылған бірліктер тұрғысынан қай сату аудандары үздік бестікте болды?

Бұл сұрақтарға жауап беретін есептеуді жасау үшін сізде санау немесе қосу керек сандарды қамтитын толық мәліметтер болуы керек және бұл сандық деректер нәтижелерді реттеу үшін пайдаланылатын топтарға қатысты болуы керек.

Егер деректерде өнім санаты немесе дүкен орналасқан географиялық аймақтың атауы сияқты топтау үшін пайдалануға болатын мәндер болмаса, санаттарды қосу арқылы деректерге топтарды енгізуге болады. Excel бағдарламасында топтарды жасағанда жұмыс парағындағы бағандар арасынан топтарды қолмен теру немесе таңдау керек. Бірақ, реляциялық жүйеде өнімдердің санаттары сияқты иерархиялар көбінесе нақты немесе мәндер кестесінен басқа кестеде сақталады. Әдетте санаттар кестесі бір кілт түрімен нақты деректерге байланыстырылады. Мысалы, деректерде өнім кодтары бар, бірақ өнімдердің атаулары немесе санаттары жоқ делік. Құрылымданбаған Excel жұмыс парағына санатты қосу үшін санат атауларын қамтитын бағанды көшіру керек болатын еді. Power Pivot қондырмасының көмегімен өнім санаттары кестесін деректер үлгісіне импорттауға, сандық деректер бар кесте мен өнім санаттары тізімінің арасында қатынасты жасауға, содан кейін санаттарды пайдаланып деректерді топтауға болады. Қосымша ақпаратты Кестелер арасында қатынастарды жасау (оқулық) бөлімінен қараңыз.

Біріктіру үшін функцияны таңдау

Пайдаланылатын топтауларды анықтап, қосқаннан кейін біріктіру үшін қай математикалық функциялар пайдаланылатын таңдауға болады. Жиі біріктіру сөзі қосындылар, орташа мәндер, орташа мән немесе сандар сияқты біріктірулерде пайдаланылатын математикалық немесе статистикалық амалдардың синонимі ретінде пайдаланылады. Алайда, Power Pivot қондырмасы Power Pivot және Excel бағдарламаларының екеуінде де бар стандартты біріктірулерге қоса біріктіру үшін реттелетін формулаларды жасауға мүмкіндік береді.

Мысалы, алдыңғы мысалдарда пайдаланылған мәндер жиынын және топтауларды пайдаланып келесі сұрақтарға жауап беретін реттелетін біріктірулерді жасауға болады:

Сүзілген сандар   Ай соңындағы техникалық қызмет көрсету бос аралығын қоспағанда, ай ішінде қанша транзакция болды?

Уақыттағы орташа мәндер пайдаланылатын қатынастар    Өткен жылдағы бірдей кезеңмен салыстырғандағы пайыздық өсу немесе төмендеу қандай болды?

Топталған ең аз және ең жоғары мәндер    Әр өнім санаты немесе әр сату жарнамасы бойынша қай сату аудандары үздік болды?

Формулалар мен жиынтық кестелерге біріктірулерді қосу

Деректерді мағыналы болуы үшін қалай топтау керектігі және жұмыс істейтін мәндер туралы жалпы түсінігіңіз болса, жиынтық кестені жасауды немесе кесте ішінде есептеулерді жасауды таңдауға болады. Power Pivot қондырмасы Excel бағдарламасының қосындыларды, сандарды және орташа мәндерді жасау мүмкіндігін кеңейтеді және жақсартады. Power Pivot қондырмасында реттелетін біріктірулерді Power Pivot терезесінде немесе Excel PivotTable аумағында жасауға болады.

  • Есептелетін бағанда ағымдағы жол контекстін ескеріп басқа кестеден қатысты жолдарды шығарып алып, содан кейін қатысты жолдардағы сол мәндерді қосатын, санайтын немесе орташа мәнін шығаратын біріктірулерді жасауға болады.

  • Есептелетін өрісте формула ішінде анықталған сүзгілерді және жиынтық кесте үлгісі және таңдалған шектегіштер, баған тақырыптары және жол тақырыптары анықтайтын сүзгілерді пайдаланатын динамикалық біріктірулерді жасауға болады. Стандартты біріктірулерді пайдаланатын есептелетін өрістерді Power Pivot қондырмасында Автоқосынды мүмкіндігін пайдалану немесе формула жасау арқылы жасауға болады. Сондай-ақ, жасырын есептелетін өрістерді Excel бағдарламасындағы жиынтық кестедегі стандартты біріктірулерді пайдаланып жасай аласыз.

Жиынтық кестеге топтауларды қосу

Жиынтық кестені құрастырғанда, деректерді топтау үшін топтауларды, санаттарды немесе иерархияларды білдіретін өрістерді жиынтық кестенің бағандар және жолдар бөліміне апарасыз. Содан кейін сол сандық мәндерді қамтитын өрістерді санауға, орташа мәнін шығаруға немесе қосуға болатындай мәндер аумағына апарасыз.

Егер жиынтық кестеге санаттарды қоссаңыз, бірақ санат деректері нақты деректерге қатысты болмаса, қатені немесе оғаш нәтижелерді алуыңыз мүмкін. Әдетте Power Pivot қондырмасы байланыстарды автоматты түрде анықтау және ұсыну арқылы мәселені түзетуге тырысады. Қосымша ақпаратты Жиынтық кестелердегі байланыстармен жұмыс істеу бөлімінен қараңыз.

Сондай-ақ, қарау үшін белгілі бір деректер топтарын таңдау үшін өрістерді шектегіштерге апаруға болады. Шектегіштер жиынтық кестедегі нәтижелерді интерактивті түрде топтауға, сұрыптауға және сүзуге мүмкіндік береді.

Формуладағы топтаулармен жұмыс істеу

Сондай-ақ, топтауларды және санаттарды пайдаланып кестелер арасында қатынастарды жасау, содан кейін сол қатынастарға қатысты мәндерді іздеуге мүмкіндік беретін формулаларды жасау арқылы кестелерде сақталған деректерді біріктіруге болады.

Басқаша айтқанда, мәндерді санат бойынша топтайтын формуланы жасау керек болса, алдымен толық деректерді және санаттарды қамтитын кестелерді қосу үшін қатынасты пайдаланатын едіңіз, содан кейін формуланы жасайтын едіңіз.

Іздеуді пайдаланатын формулаларды жасау туралы қосымша ақпаратты PowerPivot формулаларындағы қондырмалы өрістер бөлімінен қараңыз.

Біріктірулерде сүзгілерді пайдалану

Power Pivot қондырмасындағы жаңа мүмкіндік — сүзгілерді пайдаланушы интерфейсінде және жиынтық кестеде немесе диаграммада ғана емес, сонымен бірге, біріктірулерді есептеу үшін пайдаланатын формулалардың өздерінде де деректердің бағандарына және кестелеріне сүзгілерді қолдану мүмкіндігі. Сүзгілерді есептелетін бағандарда, есептелетін өрістерде де пайдалануға болады.

Мысалы, жаңа DAX біріктіру функцияларында қосу немесе санау үшін пайдаланылатын мәндерді көрсетудің орнына аргумент ретінде бүкіл кестені көрсетуге болады. Егер ол кестеге ешбір сүзгілерді қолданбаған болсаңыз, біріктіру функциясы кестенің көрсетілген бағанындағы барлық мәндерге қатысты жұмыс істейтін еді. Бірақ, DAX тілінде кестеде динамикалық немесе статикалық сүзгіні жасауға болады, осылайша біріктіру сүзгі шарты мен ағымдағы контекстіге байланысты әр түрлі деректер жиынына қатысты жұмыс істейді.

Формулаларда шарттарды және сүзгілерді біріктіру арқылы формулаларда берілген мәндерге байланысты өзгеретін немесе жиынтық кестеде бөлектелген жол тақырыптары мен баған тақырыптарына байланысты өзгеретін біріктірулерді жасай аласыз.

Қосымша ақпаратты Формулаларда деректерді сүзу мақаласынан қараңыз.

Excel біріктіру функцияларын және DAX біріктіру функцияларын салыстыру

Келесі кестеде Excel бағдарламасы қамтамасыз ететін кейбір стандартты біріктіру функциялары тізілген, әрі Power Pivot қондырмасында осы функцияларды іске асыру сілтемелері берілген. Бұл функциялардың DAX нұсқасы синтаксистегі және белгілі бір деректер түрлерін өңдеудегі кейбір шағын айырмашылықтарды қоспағанда, Excel нұсқасы сияқты жұмыс істейді.

Стандартты біріктіру функциялары

Функция

Пайдалану

AVERAGE

Бағандағы барлық сандардың орташа мәнін (арифметикалық орташа мән) қайтарады.

AVERAGEA

Бағандағы барлық мәндердің орташа мәнін (арифметикалық орташа мәні) қайтарады. Мәтіндік және сандық емес мәндерді өңдейді.

COUNT

Бағандағы сандық мәндердің санын санайды.

COUNTA

Бағандағы бос емес мәндердің санын санайды.

MAX

Бағандағы ең үлкен сандық мәнді қайтарады.

MAXX

Кестеде есептелетін өрнектер жиынынан ең үлкен мәнді қайтарады.

MIN

Бағанда ең кіші сандық мәнді қайтарады.

MINX

Кестеде есептелетін өрнектер жиынынан ең кіші мәнді қайтарады.

SUM

Бағандағы барлық сандарды қосады.

DAX біріктіру функциялары

DAX біріктіру орындалатын кестені көрсетуге мүмкіндік беретін біріктіру функцияларын қамтиды. Сондықтан, жай бағандағы мәндерді қосудың немесе орташа мәнін шығарудың орнына бұл функциялар біріктіретін деректерді динамикалық түрде анықтайтын өрнекті жасауға мүмкіндік береді.

Келесі кестеде DAX тілінде қол жетімді біріктіру функциялары тізілген.

Функция

Пайдалану

AVERAGEX

Кестеде есептелетін өрнектердің жиынының орташа мәнін шығарады.

COUNTAX

Кестеде есептелетін өрнектердің жиынын санайды.

COUNTBLANK

Бағандағы бос мәндердің санын санайды.

COUNTX

Кестедегі жалпы жолдар санын санайды.

COUNTROWS

Сүзгі функциясы сияқты кірістірілген кесте функциясы қайтаратын жолдар санын санайды.

SUMX

Кестеде есептелетін өрнектер жиынының қосындысын қайтарады.

DAX және Excel біріктіру функцияларының арасындағы айырмашылықтар

Бұл функциялардың атаулары Excel бағдарламасындағылармен бірдей болғанымен, олар Power Pivot жады аналитикалық модулін пайдаланады және кестелермен және бағандармен жұмыс істейтіндей қайта жазылған.  DAX формуласын Excel жұмыс кітабында және керісінше пайдалану мүмкін емес. Оларды тек Power Pivot терезесінде және Power Pivot деректеріне негізделген жиынтық кестелерде пайдалануға болады. Сондай-ақ, функциялардың атаулары бірдей болғанымен, жұмыс әрекеті аздап әр түрлі болуы мүмкін. Қосымша ақпаратты әрбір функцияның анықтама бөлімінен қараңыз.

Біріктіруде бағандарды есептеудің жолы да Excel бағдарламасының біріктірулерді өңдеу жолынан басқаша. Мысал мұны көрсетуге көмектеседі.

«Сатылым» кестесіндегі «Сома» бағанындағы мәндердің қосындысын алу керек делік, сондықтан келесі формуланы жасайсыз:

=SUM('Sales'[Amount])

Ең оңай жағдайда функция мәндерді жалғыз сүзілмеген бағаннан алады және нәтиже әрқашан жай «Сома» бағанындағы мәндерді қосатын Excel бағдарламасындағымен бірдей болады. Бірақ, Power Pivot қондырмасында бұл формула ««Сату» кестесіндегі әрбір жол үшін «Сома» бағанындағы мәнді алу, содан кейін сол жекелеген мәндерді қосу» деп түсініледі. Power Pivot біріктіру орындалатын әрбір жолды есептейді және әр жол үшін жалғыз скалярлық мәнді есептейді, содан кейін сол мәндерді біріктіруді орындайды. Сондықтан, кестеге сүзгілер қолданылған болса немесе мәндер сүзілген болуы мүмкін басқа біріктірулердің негізінде есептелсе, формуланың нәтижесі басқа болуы мүмкін. Қосымша ақпаратты DAX формулаларындағы контекст мақаласынан қараңыз.

DAX уақыт функциялары

Алдыңғы бөлімде сипатталған кестенің біріктіру функцияларына қоса, DAX тілінде бекітілген уақыт функцияларын қамтамасыз ететін, көрсетілген күндермен және уақыттармен жұмыс істейтін біріктіру функциялары бар. Бұл функциялар күндер ауқымдарын пайдаланып қатысты мәндерді алады және сол мәндерді біріктіреді. Сондай-ақ, күн ауқымдары арасындағы мәндерді салыстыруға болады.

Келесі кестеде біріктіру үшін пайдалануға болатын уақыт функциялары тізілген.

Функция

Пайдалану

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Берілген кезеңнің күнтізбе соңындағы мәнді есептейді.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Берілген кезеңнің алдындағы кезеңнің күнтізбе соңындағы мәнді есептейді.

TOTALMTD

TOTALYTD

TOTALQTD

Кезеңнің бірінші күнінде басталатын және көрсетілген күн бағанындағы соңғы күнде аяқталатын аралықтағы мәнді есептейді.

«Уақыт функциялары» бөліміндегі (Уақыт функциялары) басқа функциялар — біріктіруде пайдаланылатын күндерді немесе реттелетін күндер ауқымдарын шығарып алу үшін пайдаланылатын функциялар. Мысалы, DATESINPERIOD функциясын пайдаланып күндер ауқымын қайтарып, сол күндер жиынын дәл сол күндер үшін реттелетін біріктіруді есептеу үшін басқа функцияның аргументі ретінде пайдалануға болады.

Дағдыларды жетілдіру
Оқыту курсымен танысыңыз
Жаңа мүмкіндіктерге бірінші болып қол жеткізу
Office Insider бағдарламасына қосылу

Осы ақпарат пайдалы болды ма?

Пікіріңіз үшін рақмет!

Пікіріңізге рақмет! Сізді Office қолдау көрсету қызметіндегі агенттердің бірімен байланыстырған жөн болуы мүмкін.

×