Power Pivot қондырмасындағы DAX сценарийлері

Бұл бөлімде келесі сценарийлерде DAX формулаларын пайдалануды көрсететін мысалдарға сілтемелер берілген.

  • Күрделі есептеулерді орындау

  • Мәтінмен және күндермен жұмыс істеу

  • Шартты мәндер және қателерді тексеру

  • Уақыт функциясын пайдалану

  • Мәндерді бағалау және салыстыру

Бұл мақалада:

Жұмысты бастау

Үлгі деректер

Қосымша ресурстар

Сценарийлер: күрделі есептеулерді орындау

Жиынтық кесте үшін реттелетін есептеулерді жасау

Формулаға сүзгіні қолдану

Динамикалық қатынасты жасау үшін сүзгілерді іріктеп жою

Сыртқы циклдағы мәнді пайдалану

Сценарийлер: мәтінмен және күндермен жұмыс істеу

Біріктіру арқылы кілт бағанды жасау

Күнді мәтіндік күннен шығарып алынған күн бөліктерінің негізінде құрастыру

Реттелетін күн немесе сан пішімін анықтау

Формуланы пайдаланып деректер түрлерін өзгерту

Сценарий: шартты мәндер және қателерді тексеру

Шартқа негізделіп мәнді жасау

Формула ішінде қателер бар-жоғын тексеру

Сценарийлер: уақыт функциясын пайдалану

Жиналған сатылымды есептеу

Уақыт бойындағы мәндерді салыстыру

Реттелетін күн ауқымындағы мәнді есептеу

Сценарийлер: мәндерді бағалау және салыстыру

Жиынтық кестеде тек жоғарғы он элементті көрсету

Формуланы пайдалана отырып элементтерді динамикалық түрде орналастыру

Жұмысқа кіріскенде

Үлгі деректер

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

Қосымша ресурстар

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

Сценарийлер: күрделі есептеулерді орындау

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

Жиынтық кесте үшін реттелетін есептеулерді жасау

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

Формулаға сүзгіні қолдану

DAX функциясы кестені аргумент ретінде қабылдайтын орындардың көпшілігінде әдетте кесте атауының орнына FILTER функциясын пайдалана отырып немесе сүзгі өрнегін функция аргументтерінің біреуі ретінде көрсету арқылы оның орнына сүзілген кестені беруге болады. Келесі бөлімдерде сүзгілерді жасау жолының және сүзгілердің формулалардың нәтижелеріне әсер ету жолының мысалдары берілген. Қосымша ақпаратты DAX формулаларында деректерді сүзу мақаласында қараңыз.

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

Динамикалық қатынасты жасау үшін сүзгілерді іріктеп жояды

Формулаларды динамикалық сүзгілерді жасау арқылы келесі сияқты сұрақтарға оңай жауап беруге болады:

  • Ағымдағы өнім сатылымының жыл бойғы жалпы сатылымға үлесі қандай болды?

  • Бұл бөлім басқа бөлімдермен салыстырғанда бүкіл жұмыс істеу жылдары бойы жалпы табысқа қанша үлес қосты?

Жиынтық кестеде пайдаланатын формулаларға жиынтық кестенің контексті әсер етуі мүмкін, бірақ сүзгілерді қосу немесе жою арқылы контекстіні таңдау бойынша өзгертуге болады. ALL бөліміндегі мысал мұны істеу жолын көрсетеді. Нақты сатушының сатылымының басқа сатушылардың сатылымына қатынасын табу үшін ALL контекстінің мәніне бөлінген ағымдағы контекстінің мәнін есептейтін есептелетін өрісті жасайсыз.

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

Қатынастар мен пайыздарды есептеу жолының басқа мысалдарын мына бөлімдерден қараңыз:

Сыртқы циклдағы мәнді пайдалану

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

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

Сценарийлер: мәтінмен және күндермен жұмыс істеу

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

Біріктіру арқылы кілт бағанды жасау

Power Pivot құрама кілттерге рұқсат етпейді; сондықтан, деректер көзінде құрама кілттер бар болса, оларды бір кілт бағанға біріктіру керек болуы мүмкін. Келесі бөлімде құрама кілттің негізінде есептелетін бағанды жасау жолының бір мысалы берілген.

Күнді мәтіндік күннен шығарып алынған күн бөліктерінің негізінде құрастыру

Power Pivot қондырмасы күндермен жұмыс істеу үшін SQL Server күн/уақыт деректер түрін пайдаланады; сондықтан, егер сыртқы деректерде әр түрлі пішімделген күндер бар болса — мысалы, егер күндер Power Pivot деректер механизмі танымайтын аймақтық күн пішімінде жазылған болса немесе деректерде бүтін ауыстырушы кілттер пайдаланылса, DAX формуласын пайдаланып күн бөліктерін шығарып алып, бұл бөліктерді дұрыс күн/уақыт презентациясына құрастыру керек болуы мүмкін.

Мысалы, егер сізде бүтін сан ретінде көрсетілген, содан кейін мәтіндік жол ретінде импортталған күндер бағаны бар болса, жолды келесі формуланы пайдалана отырып күн/уақыт мәніне түрлендіруге болады:

=DATE(RIGHT([Value1],4),LEFT([Value1],2),MID([Value1],2))

1-мән

Нәтиже

01032009

1/3/2009

12132008

12/13/2008

06252007

6/25/2007

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

Реттелетін күн немесе сан пішімін анықтау

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

Формуланы пайдаланып деректер түрлерін өзгерту

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

  • Күн немесе сан жолын санға түрлендіру үшін 1.0 санына көбейтіңіз. Мысалы, келесі формула ағымдағы күн минус 3 күнді есептейді, содан кейін сәйкес бүтін мәнді шығарады.

    =(TODAY()-3)*1.0

  • Күн, сан немесе валюта мәнін жолға түрлендіру үшін мәнді бос жолмен біріктіріңіз. Мысалы, келесі формула бүгінгі күнді жол ретінде қайтарады.

    =""& TODAY()

Сондай-ақ, нақты деректер түрінің қайтарылуын қамтамасыз ету үшін келесі функцияларды пайдалануға болады:

Нақты сандарды бүтін сандарға түрлендіру

Сценарий: шартты мәндер және қателерді тексеру

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

Шартқа негізделіп мәнді жасау

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

Формула ішінде қателер бар-жоғын тексеру

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

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

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

Сценарийлер: уақыт функциясын пайдалану

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

Уақыт функцияларының тізімін Уақыт функциялары (DAX) бөлімінен қараңыз. Power Pivot талдауында күндерді және уақыттарды тиімді пайдалану жолы туралы кеңестерді қондырмасындағы күндерPower Pivot бөлімінен қараңыз.

Жиналған сатылымды есептеу

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

Уақыт бойындағы мәндерді салыстыру

Келесі бөлімдерде әр түрлі уақыт кезеңдерінде қосындыларды салыстыру жолының мысалдары бар. DAX қолдау көрсететін әдепкі уақыт кезеңдері — айлар, тоқсандар және жылдар.

Реттелетін күн ауқымындағы мәнді есептеу

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

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

  • PARALLELPERIOD функциясы

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

Сценарийлер: мәндерді бағалау және салыстыру

Бағандағы немесе жиынтық кестедегі элементтердің жоғарғы n санын ғана көрсету үшін бірнеше параметр бар:

  • Excel 2010 бағдарламасындағы мүмкіндіктерді пайдаланып «Жоғарғы» сүзгісін жасауға болады. Сондай-ақ, жиынтық кестеде жоғарғы немесе төменгі мәндердің санын таңдауға болады. Бұл бөлімнің бірінші бөлігінде жиынтық кестедегі жоғарғы 10 элементті сүзу жолы сипатталады. Қосымша ақпарат алу үшін Excel құжаттамасын қараңыз.

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

Әр әдістің артықшылықтары мен кемшіліктері.

  • Excel «Жоғарғы» сүзгісін пайдалану оңай, бірақ бұл сүзгі тек көрсету мақсатына арналған. Егер жиынтық кесте негізіндегі деректер өзгерсе, өзгертулерді көру үшін жиынтық кестені қолмен жаңарту керек. Егер бағалаулармен динамикалық түрде жұмыс істеу керек болса, DAX тілін пайдаланып мәндерді баған ішіндегі басқа мәндермен салыстыратын формуланы жасауға болады.

  • DAX формуласы қуаттырақ; бұған қоса, шектегішке бағалау мәнін қосу арқылы шектегішті жай басу арқылы көрсетілетін жоғарғы мәндердің санын өзгертуге болады. Бірақ, есептеулер компьютер ресурстарын көп пайдаланады және бұл әдіс жолдары көп кестелер үшін жарамауы мүмкін.

Жиынтық кестеде тек жоғарғы он элементті көрсету

Жиынтық кестедегі жоғарғы немесе төменгі мәндерді көрсету

  1. Жиынтық кестеде Жол белгілері тақырыбындағы төмен көрсеткіні басыңыз.

  2. Мән сүзгілері> Негізгі 10 тармағын таңдаңыз.

  3. Top 10 Filter <column name>диалогтық терезесінде бағалау керек бағанды және мәндердің санын келесідей таңдаңыз:

    1. Ең жоғары мәндер бар ұяшықтарды көру үшін Жоғарғы немесе ең төменгі мәндер бар ұяшықтарды көру үшін Төменгі параметрін таңдаңыз.

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

    3. Мәндерді көрсету жолын таңдаңыз:

Аты

Сипаттамасы

Элементтер

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

Percent

Бұл параметрді жиынтық кестені көрсетілген пайызды құрайтын элементтерді ғана көрсететіндей сүзу үшін пайдаланыңыз.

Sum

Бұл параметрді жоғарғы немесе төменгі элементтер үшін мәндердің қосындысын көрсету үшін таңдаңыз.

  1. Бағалау керек мәндерді қамтитын бағанды таңдаңыз.

  2. OK түймешігін басыңыз.

Формуланы пайдалана отырып элементтерді динамикалық түрде орналастыру

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

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

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

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

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

×