Деректер үлгісі бойынша Pivot артықшылықтары

Excel бағдарламасында жиынтық кестені құру кезінде бізден бастапқы диапазонды орнату және жиынтық кестені кірістіру үшін орынды таңдау сұралатын ең бірінші диалогтық терезеде төменде көрінбейтін, бірақ өте маңызды құсбелгі бар – Бұл деректерді Деректер үлгісіне қосыңыз (Осы деректерді қосыңыз Деректер үлгісіне) және сәл жоғарырақ, қосқыш Осы кітаптың деректер үлгісін пайдаланыңыз (Осы жұмыс кітабының деректер үлгісін пайдаланыңыз):

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

Дегенмен, осы «тоқаштарды» жақыннан қарастырмас бұрын, алдымен бұл деректер үлгісінің не екенін түсінейік?

Деректер моделі дегеніміз не

Мәліметтер моделі (MD немесе DM = Деректер үлгісі ретінде қысқартылған) - кестелік деректерді сақтауға болатын Excel файлының ішіндегі арнайы аймақ - қажет болса, бір-бірімен байланыстырылған бір немесе бірнеше кестелер. Шын мәнінде, бұл Excel жұмыс кітабының ішіне енгізілген шағын дерекқор (OLAP текшесі). Excel парақтарындағы кәдімгі (немесе смарт) кестелер түріндегі деректерді классикалық сақтаумен салыстырғанда, Деректер моделінің бірнеше маңызды артықшылықтары бар:

• Кестелер дейін болуы мүмкін 2 миллиард жол, ал Excel парағы 1 миллионнан сәл артық сыйдырады.
• Үлкен өлшемдерге қарамастан, мұндай кестелерді өңдеу (сүзу, сұрыптау, олар бойынша есептеулер, құрылыс қорытындылары және т.б.) орындалады. өте жылдам Excel бағдарламасының өзінен әлдеқайда жылдам.
• Үлгідегі деректердің көмегімен сіз қосымша (қажет болса, өте күрделі) есептеулерді пайдалана аласыз кірістірілген DAX тілі.
  
• Деректер үлгісіне жүктелген барлық ақпарат өте маңызды қатты қысылған арнайы кірістірілген мұрағаттауыштың көмегімен және бастапқы Excel файлының өлшемін біршама үлкейтеді.
  

Модель Microsoft Excel бағдарламасына енгізілген арнайы қондырма арқылы басқарылады және есептеледі – қуат айналуыол туралы мен қазірдің өзінде жаздым. Оны қосу үшін қойындыда әзірлеуші басыңыз COM қондырмалары (Әзірлеуші ​​— COM қондырмалары) және тиісті құсбелгіні қойыңыз:

Егер қойындылар әзірлеуші (Әзірлеуші)сіз оны таспада көре алмайсыз, оны қосуға болады Файл – Параметрлер – Таспаны орнату (Файл — Параметрлер — таспаны теңшеу). COM қондырмаларының тізімінде жоғарыда көрсетілген терезеде сізде Power Pivot болмаса, ол сіздің Microsoft Office нұсқаңызға қосылмаған 🙁

Пайда болған Power Pivot қойындысында үлкен ашық жасыл түйме болады Басқару (Басқару), оны басу Excel бағдарламасының жоғарғы жағындағы Power Pivot терезесін ашады, онда біз ағымдағы кітаптың деректер үлгісінің мазмұнын көреміз:

Жол бойындағы маңызды ескерту: Excel жұмыс кітабында тек бір деректер үлгісі болуы мүмкін.

Деректер үлгісіне кестелерді жүктеңіз

Модельге деректерді жүктеу үшін алдымен кестені динамикалық «ақылды» пернелер тіркесіміне айналдырамыз Ctrl+T және қойындыда оған ыңғайлы атау беріңіз Конструктор (Дизайн). Бұл міндетті қадам.

Содан кейін таңдау үшін үш әдістің кез келгенін пайдалануға болады:

• Түймесін басыңыз Үлгіге қосу (Дерек үлгісіне қосу) қойындысы қуат айналуы қойындысы Home (Үй).
  
• Командаларды таңдау Кірістіру – жиынтық кесте (Кірістіру — жиынтық кесте) және құсбелгіні қосыңыз Бұл деректерді Деректер үлгісіне қосыңыз (Бұл деректерді Деректер үлгісіне қосыңыз). Бұл жағдайда, Үлгіге жүктелген деректерге сәйкес, жиынтық кесте де дереу құрастырылады.
  
• Қосымша қойындысында мәліметтер (Күні) түймешігін басыңыз Кестеден/Ауқымнан (Кестеден/ауқымнан)кестені Power Query өңдегішіне жүктеу үшін. Бұл жол ең ұзын, бірақ қажет болса, мұнда қосымша деректерді тазалау, өңдеу және Power Query өте күшті болатын барлық түрлендірулерді орындауға болады.

  Содан кейін тарақталған деректер пәрмен арқылы Модельге жүктеледі Негізгі бет — Жабу және жүктеу — Жабу және жүктеу… (Басты бет — Жабу&Жүктеу — Жабу&Жүктеу...). Ашылған терезеде опцияны таңдаңыз Тек қосылым жасаңыз (Тек қосылым жасау) және, ең бастысы, белгі қойыңыз Бұл деректерді Деректер үлгісіне қосыңыз (Бұл деректерді Деректер үлгісіне қосыңыз).
  

Деректер үлгісінің қысқаша мазмұнын жасаймыз

Жиынтық деректер үлгісін құру үшін үш тәсілдің кез келгенін пайдалануға болады:

• Түймешігін басыңыз жиынтық кесте (Жиынтық кесте) Power Pivot терезесінде.
• Excel бағдарламасында пәрмендерді таңдаңыз Кірістіру – жиынтық кесте және режимге ауысыңыз Осы кітаптың деректер үлгісін пайдаланыңыз (Кірістіру — жиынтық кесте — осы жұмыс кітабының деректер үлгісін пайдаланыңыз).
• Командаларды таңдау Кірістіру – жиынтық кесте (Кірістіру — жиынтық кесте) және құсбелгіні қосыңыз Бұл деректерді Деректер үлгісіне қосыңыз (Бұл деректерді Деректер үлгісіне қосыңыз). Ағымдағы «ақылды» кесте Үлгіге жүктеледі және бүкіл Үлгі үшін жиынтық кесте жасалады.

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

1-артықшылық: Формулаларды қолданбай кестелер арасындағы байланыс

Кәдімгі жиынтықты тек бір бастапқы кестедегі деректерді пайдалану арқылы құруға болады. Егер сізде олардың бірнешеуі болса, мысалы, сатылымдар, прейскурант, тұтынушылар анықтамалығы, келісім-шарттар тізілімі және т.б. болса, алдымен VLOOKUP сияқты функцияларды пайдаланып барлық кестелерден деректерді жинауыңыз керек (КӨРУ), ИНДЕКС (ИНДЕКС), КӨБІРЕК АШЫҚ (МАТЧ), SUMMESLIMN (SUMIFS) және т.б. Бұл ұзақ, жалықтырады және Excel-ді деректердің үлкен көлемі бар «ойға» айналдырады.

Деректер үлгісінің қысқаша мазмұны жағдайында бәрі әлдеқайда қарапайым. Power Pivot терезесінде кестелер арасындағы қатынасты бір рет орнату жеткілікті – және ол аяқталды. Мұны істеу үшін қойындыда қуат айналуы түймесін басыңыз Басқару (Басқару) содан кейін пайда болған терезеде – түймені басыңыз Диаграмма көрінісі (Диаграмма көрінісі). Сілтемелер жасау үшін кестелер арасында жалпы (кілт) баған атауларын (өрістерін) сүйреу қалады:

Осыдан кейін, Деректер үлгісіне арналған жиынтықта жиынтық аймағына (жолдар, бағандар, сүзгілер, мәндер) кез келген қатысты кестелерден кез келген өрістерді шығаруға болады – бәрі автоматты түрде байланыстырылады және есептеледі:

2-пайда: бірегей мәндерді санау

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

Өрісті тінтуірдің оң жақ түймешігімен басыңыз – пәрмен Мән өрісінің опциялары және қойындыда операция таңдау Әртүрлі элементтердің саны (Айрықша санау):

3-пайда: пайдаланушы DAX формулалары

Кейде жиынтық кестелерде әртүрлі қосымша есептеулерді орындауға тура келеді. Кәдімгі жиындарда бұл есептелген өрістер мен нысандар арқылы орындалады, ал деректер үлгісінің қорытындысы арнайы DAX тіліндегі өлшемдерді пайдаланады (DAX = Деректерді талдау өрнектері).

Өлшем жасау үшін қойындыда таңдаңыз қуат айналуы бұйрық Шаралар – Өлшем жасау (Шаралар — Жаңа шара) немесе жиынтық өрістер тізіміндегі кестені тінтуірдің оң жақ түймешігімен басып, таңдаңыз Өлшемді қосыңыз (Өлшем қосу) контекстік мәзірде:

Ашылған терезеде мынаны орнатыңыз:

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

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

4-пайда: теңшелетін өріс иерархиялары

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

Power Pivot терезесінде түйме арқылы диаграмма режиміне ауысыңыз Диаграмма көрінісі қойындысы Home (Үй — Диаграмма көрінісі), көмегімен таңдаңыз Ctrl қажетті өрістерді таңдап, оларды тінтуірдің оң жақ түймешігімен басыңыз. Мәтінмәндік мәзірде пәрмен болады Иерархия құру (Иерархия құру):

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

5-артықшылық: пайдаланушы трафареттері

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

Мұны істеу үшін қойындыда Жиынтық кестені талдау ашылмалы тізімде Өрістер, элементтер және жиындар сәйкес командалар бар (Талдау — Fields, Items & Sets — жол/баған элементтері негізінде жиын жасау):

Ашылған терезеде кез келген элементтердің орнын таңдап алып тастауға, қосуға немесе өзгертуге және алынған жиынды жаңа атаумен сақтауға болады:

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

6-артықшылық: кестелер мен бағандарды таңдаулы түрде жасыру

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

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

Артықшылық 7. Жетілдірілген төмен қарай жылжыту

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

Деректер үлгісінің қорытындысында бұл ыңғайлы құрал неғұрлым нәзік жұмыс істейді. Нәтижесі бізді қызықтыратын кез келген ұяшықта тұрып, оның жанында пайда болатын үлкейткіш әйнекпен белгішені басуға болады (ол деп аталады). Экспресс трендтер) және кез келген қатысты кестеде сізді қызықтыратын кез келген өрісті таңдаңыз:

Осыдан кейін ағымдағы мән (Модель = Explorer) сүзгі аймағына өтеді және қорытындыны кеңселер құрастырады:

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

8-пайда: Пивотты текше функцияларына түрлендіру

Деректер үлгісі үшін жиынтықта кез келген ұяшықты таңдасаңыз, содан кейін қойындыда таңдаңыз Жиынтық кестені талдау бұйрық OLAP құралдары – формулаларға түрлендіру (Талдау — OLAP құралдары — формулаларға түрлендіру), содан кейін бүкіл қорытынды автоматты түрде формулаларға түрлендіріледі. Енді жол-баған аймағындағы өріс мәндері және мән аймағындағы нәтижелер арнайы текше функцияларын пайдаланып Деректер үлгісінен шығарылады: CUBEVALUE және CUBEMBER:

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

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

• Power Pivot және Power Query көмегімен жиынтық кестедегі жоспар-факті талдауы
• Көп жолды тақырыбы бар жиынтық кесте
• Power Pivot көмегімен Excel бағдарламасында дерекқор жасаңыз