Деректер үлгісіндегі кестелер арасындағы қатынастар

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

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

ТұтынушыИдентификаторы

Аты

ЭлектрондықПошта

ЖеңілдікМөлшері

ТапсырысИдентификаторы

ТапсырысКүні

Өнім

Мөлшер

1

Эштон

chris.ashton@contoso.com

.05

256

2010-01-07

Шағын сандық

11

1

Эштон

chris.ashton@contoso.com

.05

255

2010-01-03

SLR камерасы

15

2

Яворский

michal.jaworski@contoso.com

.10

254

2010-01-03

Бюджет фильм жасаушы

27

Бұл әдіс нәтиже беруі мүмкін, бірақ ол әр тапсырыс үшін тұтынушының электрондық пошта мекенжайы сияқты көп артық деректерді қамтиды. Жад арзан, бірақ электрондық пошта мекенжайы өзгерсе, сол тұтынушы үшін әрбір жолды жаңарту керек. Бұл мәселенің бір шешімі — деректерді бірнеше кестеге бөлу және сол кестелер арасында қатынастарды анықтау. Бұл — SQL Server сияқты реляциялық дерекқорларда пайдаланылатын әдіс. Мысалы, импорттайтын дерекқор тапсырыс деректерін үш байланысқан кестені пайдалана отырып көрсетуі мүмкін:

Тұтынушылар

[ТұтынушыИдентификаторы]

Аты

Электрондық пошта

1

Эштон

chris.ashton@contoso.com

2

Яворский

michal.jaworski@contoso.com

ТұтынушыЖеңілдіктері

[ТұтынушыИдентификаторы]

ЖеңілдікМөлшері

1

.05

2

.10

Тапсырыстар

[ТұтынушыИдентификаторы]

ТапсырысИдентификаторы

ТапсырысКүні

Өнім

Мөлшер

1

256

2010-01-07

Шағын сандық

11

1

255

2010-01-03

SLR камерасы

15

2

254

2010-01-03

Бюджет фильм жасаушы

27

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

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

Беттің жоғарғы жағы

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

Бағандар мен кілттер

Қатынастардың түрлері

Қатынастар мен өнімділік

Кестелер арасындағы бірнеше қатынас

Кесте қатынастары үшін талаптар

Кесте қатынастарында қолдау көрсетілмейді

Құрама кілттер және қондырмалы бағандар

Көп-көпке қатынастары

Өздігінен қосылулар және циклдер

Байланыстарды Power Pivot қондырмасында автоматты түрде анықтау және шығару

Аталған жиындарды автоматты түрде анықтау

Қатынастарды шығару

Бағандар мен кілттер

Қатынастар бірдей деректерді қамтитын әр кестедегі бағандарға негізделеді. Мысалы, «Тұтынушылар» және «Тапсырыстар» кестелері бір біріне байланысты бола алады, өйткені екеуі де тұтынушы идентификаторын сақтайтын бағанды қамтиды. Мысалда баған атаулары бірдей, бірақ бұл талап емес. Біреуі ТұтынушыИдентификаторы және екіншісі ТұтынушыНөмірі бола алады, тек «Тапсырыстар» кестесіндегі барлық жолдарда «Тұтынушылар» кестесінде де сақталатын идентификатор болуы керек.

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

Төмендегі кілттер — біздің мақсаттарымыз үшін ең қызық:

  • Негізгі кілт: кестедегі жолды бір мәнді анықтайды, мысалы, «Тұтынушылар» кестесіндегі «ТұтынушыИдентификаторы».

  • Баламалы кілт (немесе кандидат кілт): негізгі кілттен басқа, бір мәнді баған. Мысалы, «Қызметкерлер» кестесі екеуі де бір мәнді қызметкер идентификаторын және әлеуметтік қауіпсіздік нөмірін қамтуы мүмкін.

  • Сыртқы кілт: басқа кестедегі бір мәнді бағанға сілтеме жасайтын баған, мысалы, «Тұтынушылар» кестесіндегі «ТұтынушыИдентификаторы» бағанына сілтеме жасайтын «Тапсырыстар» кестесіндегі «ТұтынушыИдентификаторы» бағаны.

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

Қатынастардың түрлері

«Тұтынушылар» және «Тапсырыстар» арасындағы қатынас — бір-көпке қатынасы. Әр тұтынушыда бірнеше тапсырыс бар, бірақ тапсырыста көп тұтынушы бола алмайды. Қатынастардың түрлері — бір-біреуге және көп-көпке. Әр тұтынушы үшін бір жеңілдік көрсеткішін анықтайтын тұтынушы жеңілдіктері кестесі — «Тұтынушылар» кестесімен біреу-біреуге қатынасы.

Төмендегі кестеде үш кесте арасындағы қатынастар көрсетілген:

Қатынас

Түр

Қондырмалы баған

Баған

Тұтынушылар-ТұтынушыЖеңілдіктері

біреу-біреуге

Тұтынушылар.ТұтынушыИдентификаторы

ТұтынушыЖеңілдіктері.ТұтынушыИдентификаторы

Тұтынушылар-Тапсырыстар

бір-көпке

Тұтынушылар.ТұтынушыИдентификаторы

Тапсырыстар.ТұтынушыИдентификаторы

Ескерту :  Деректер үлгісінде көп-көпке қатынастарына қолдау көрсетілмейді. Көп-көпке қатынасының мысалы — «Өнімдер» мен «Тұтынушылар» арасындағы тікелей қатынас. Онда тұтынушы көп өнімдерді сатып ала алады және бірдей өнімді көп тұтынушы сатып ала алады.

Қатынастар мен өнімділік

Кез келген қатынас жасалғаннан кейін, Excel бағдарламасы әдетте жаңадан жасалған қатынастағы кестелердегі бағандарды пайдаланатын барлық формулаларды қайта есептеуі керек. Деректер көлеміне және қатынастардың күрделілігіне байланысты өңдеу біраз уақыт алуы мүмкін. Мәліметтерді Формулаларды қайта есептеу бөлімінен қараңыз.

Кестелер арасындағы бірнеше қатынас

Деректер үлгісінде екі кесте арасында бірнеше қатынас бола алады. Дәл есептеулерді жасау үшін Excel бағдарламасына бір кестеден келесісіне бір жол керек. Сондықтан, кестелердің әр жұбының арасындағы бір қатынас қана бір уақытта белсенді болады. Басқалар белсенді емес болады, бірақ формулалар мен сұрауларда белсенді емес қатынасты көрсетуге болады. Диаграмма көрінісінде белсенді қатынас — қалың сызық және белсенді еместері пунктирлік сызықтар. Мысалы, AdventureWorksDW2012 жұмыс кітабында, ӨлшКүн кестесі КүнКілт бағанын қамтиды. Бұл баған НақтыИнтернетСатылымдары кестесіндегі үш түрлі бағанға байланысты: ТапсырысКүні, МерзімКүні және ЖіберуКүні. Егер белсенді қатынас КүнКілт және ТапсырысКүні арасында болса, басқаша көрсетпесеңіз, бұл формулалар арасындағы әдепкі қатынас болып табылады.

Беттің жоғарғы жағы

Кесте қатынастары үшін талаптар

Келесі талаптар орындалғанда қатынастар жасалады:

Шарт

Сипаттама

Әр кесте үшін бір мәнді идентификатор

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

Бір мәнді қондырмалы бағандар

Қондырмалы бағандағы деректер мәндері бір мәнді болуы керек. Басқаша айтқанда, бағанда қайталамалар болмауы керек. Деректер үлгісінде null мәндері және бос жолдар бос мәніне тең, бұл — ерекше деректер мәні. Яғни, қондырмалы бағанда бірнеше null мәндері болмауы керек.

Үйлесімді деректер түрлері

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

Кесте қатынастарында қолдау көрсетілмейді

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

Құрама кілттер және қондырмалы бағандар

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

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

Көп-көпке қатынастары

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

Өздігінен қосылулар және циклдер

Өздігінен қосылуларға деректер үлгісінде рұқсат етілмейді. Өздігінен қосылу — кесте мен өзінің арасындағы рекурсивтік қатынас. Өздігінен қосылулар жиі тектік-еншілес иерархияларын анықтау үшін пайдаланылады. Мысалы, «Қызметкерлер» кестесін өзіне қосып, бизнестегі басқару тізбегін көрсететін иерархияны жасауға болады.

Excel бағдарламасы жұмыс кітабында қатынастар арасында циклдерді жасауға рұқсат етпейді. Басқаша айтқанда, келесі қатынастар жиынына тыйым салынған.

  • 1-кесте, а бағаны   -   2-кесте, f бағаны

  • 2-кесте, f бағаны   -   3-кесте, n бағаны

  • 3-кесте, n бағаны   -   1-кесте, a бағаны

Циклды жасауға әкелетін қатынасты жасауға тырыссаңыз, қате пайда болады.

Беттің жоғарғы жағы

Байланыстарды Power Pivot қондырмасында автоматты түрде анықтау және шығару

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

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

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

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

  • Қатынас сәтті анықталуы үшін қондырмалы бағандағы бір мәнді кілттердің саны көп жағындағы кестедегі мәндерден үлкен болуы керек. Басқаша айтқанда, қатынастың көп жағындағы кілт бағанда алмастырым кестенің кілт бағанында емес кез келген мәндер болмауы керек. Мысалы, идентификаторлары бар өнімдерді тізетін кесте (алмастырым кестесі) және әр өнім үшін сатылымдарды тізетін сатылымдар кестесі (қатынастың көп жағы) бар делік. Егер сату жазбаларында «Өнімдер» кестесінде сәйкес идентификаторы жоқ өнім идентификаторы бар болса, қатынасты автоматты түрде жасау мүмкін емес, бірақ оны қолмен жасай аласыз. Excel қатынасты анықтауы үшін алдымен «Өнім» алмастырым кестесін жетіспейтін өнімдердің идентификаторларымен жаңарту керек.

  • Көп жағындағы кілт бағанның атауы алмастырым кестесіндегі кілт бағанның атауына ұқсас екенін тексеріңіз. Атаулар бірдей болуы қажет емес. Мысалы, бизнес жағдайларында, жиі негізінен бірдей деректерді қамтитын бағандардың атауларының нұсқалары болады: Қыз Идентификаторы, ҚызметкерИдентификаторы, Қызметкер идентификаторы, ҚЫЗМ_ИДЕНТИФИКАТОРЫ және т.с.с. Алгоритм ұқсас атауларды анықтайды және ұқсас немесе дәл сәйкес келетін атаулары бар бағандарға жоғарырақ ықтималдықты тағайындайды. Сондықтан, қатынасты жасау ықтималдығын арттыру үшін импорттайтын деректердегі бағандарды бар кестелердегі бағандарға ұқсас бірдеңеге қайта атап көруге болады. Excel бағдарламасы ықтимал қатынастарды тапса, онда ол қатынасты жасамайды.

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

Аталған жиындарды автоматты түрде анықтау

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

Қатынастарды шығару

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

  • Өнімдер мен Санат -- қолмен жасалған

  • Санат және ІшкіСанат -- қолмен жасалған

  • Өнімдер мен ІшкіСанат -- қатынас шығарылған

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

Беттің жоғарғы жағы

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

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

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

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

×