Excel бағдарламасында кестелер арасында байланыс жасау

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

Жұмыс кітабындағы барлық кестелер жиынтық кестеде және Power View өрістер тізімдерінде беріледі.

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

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

  1. Жұмыс кітабында кем дегенде екі кесте бар екенін және әр кестеде басқа кестедегі бағанға салыстыруға болатын баған бар екенін тексеріңіз.

  2. Деректерді кесте ретінде пішімдеңіз немесе

    Жаңа жұмыс парағында сыртқы деректерді кесте ретінде импорттаңыз.

  3. Әр кестеге мағыналы атау беріңіз: Кесте құралдары параметрінде Дизайн > Кесте атауы > атауды енгізіңіз.

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

    Мысалы, тұтынушымен арадағы сатылымды уақыт функциясымен байланыстыру үшін екі кестеде де бірдей пішімдегі күндер (мысалы, 1/1/2012) болуы және кем дегенде бір кесте (уақыт функциясы) баған ішіндегі әр күнді бір рет көрсетуі қажет.

  5. Деректер> Байланыстар тармағына өтіңіз.

Байланыстар сұр болса, жұмыс кітабыңызда бір ғана кесте бар.

  1. Байланыстарды басқару диалогтық терезесінде Жаңа түймешігін басыңыз.

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

  3. Ел (шетел) үшін Байланысқан баған (Бастапқы) қатысты деректерді қамтитын бағанды таңдаңыз. Мысалы, кестелердің екеуінде де география кілті болған болса, қазір сол бағанды таңдайтын едіңіз.

  4. Байланысқан кесте үшін жаңа ғана Кесте үшін таңдаған кестеге байланысты кемінде бір деректер бағаны бар кестені таңдаңыз.

  5. Байланысқан кесте (негізгі) үшін Баған үшін таңдаған бағандағы мәндерге сәйкес бірегей мәндер бар бағанды таңдаңыз.

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

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

Байланыстар туралы ескертулер

Мысалы: Уақыт функциясы деректерін әуе жолы рейсінің деректеріне байланыстыру

“Кестелер арасындағы байланыстар қажет болуы мүмкін”

1-қадам: Байланыста қай кестені көрсету керектігін анықтау

2-қадам: Бір кестеден келесі кестеге жол жасауға пайдаланылуы мүмкін бағандарды табу

Байланыстар туралы ескертулер

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

  • Қатынастарды жасау VLOOKUP функцияларын пайдалануға ұқсайды: сәйкес деректерді қамтитын бағандар қажет, сонда Excel бағдарламасы бір кестедегі жолдар мен басқа кестедегі жолдар арасында сілтеме жасай алады. Тұтынушы уақыт функциясы мысалында "Тұтынушы" кестесі уақыт функциясы кестесінде де бар деректер мәндерін қажет етуі мүмкін.

  • Деректер үлгісінде кесте байланыстары "біреуге біреу" (әр жолаушының бір отырғызу талоны бар) "көпке біреу" (әр рейстің бірнеше жолаушылары бар) болуы мүмкін, бірақ "көпке көп" болмайды. "Көпке көп" байланыстары “A кезеңдік тәуелділігі анықталды” секілді кезеңдік тәуелділік қателері нәтижесінде болады. Бұл қате "көпке көп" боп табылатын екі кесте арасында тікелей байланыс немесе жанама байланыс (әр байланыста "көпке біреу", бірақ үздіксіз қараған кезде "көпке көп" болып табылатын кесте байланыстарының тізбегі) жасаған кезде пайда болады. Деректер үлгісіндегі кестелер арасындағы байланыстар туралы толығырақ оқыңыз.

  • Екі бағандағы деректер түрлері үйлесімді болуы қажет. Мәліметтерді Excel деректер үлгілеріндегі деректер түрлері бөлімінен қараңыз.

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

Мысалы: Уақыт функциясы деректерін әуе жолы рейсінің деректеріне байланыстыру

Кестелер байланыстары және уақыт функциясы туралы Microsoft Azure дүкеніндегі тегін деректер арқылы біле аласыз. Осы деректер жиындарының кейбіреуі тым үлкен және деректерді жүктеуді белгілі бір уақыт ішінде жылдам интернет қосылымын қажет етеді.

  1. Microsoft Excel бағдарламасындағы Power Pivot қондырмасын іске қосыңыз және Power Pivot терезесін ашыңыз.

  2. Сыртқы деректерді алу > Деректер қызметінен > Microsoft Azure Marketplace қызметінен түймешігін басыңыз. Microsoft Azure Marketplace басты беті кестені импорттау шеберінде ашылады.

  3. Баға тармағынан Тегін түймешігін басыңыз.

  4. Санат тармағынан Ғылым & Статистика түймешігін басыңыз.

  5. Табу DateStream басып Жазылу. Бұл туралы қосымша уақыт функциясы деректер арнасы.

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

  7. Төменге қарай айналдырып, Сұрау таңдау түймешігін басыңыз.

  8. Келесі түймешігін басыңыз.

  9. Деректерді импорттау үшін НегізгіКүнтізбеАҚШ таңдап, Аяқтау түймешігін басыңыз. Жылдам интернет қосылымы арқылы импорттау бір минутқа жуық уақыт алуы мүмкін. Аяқталған кезде 73 414 жолдың тасымалданғаны жөнінде күй есебін көресіз. Жабу түймешігін басыңыз.

  10. Екінші деректер жиынын импорттау үшін Сыртқы деректерді алу > Деректер қызметінен > Microsoft Azure Marketplace қызметінен тармағын таңдаңыз.

  11. Түр тармағындағы Деректер түймешігін басыңыз.

  12. Баға тармағынан Тегін түймешігін басыңыз.

  13. АҚШ әуе жолы рейсінің кідірістері тауып, Таңдау түймешігін басыңыз.

  14. Төменге қарай айналдырып, Сұрау таңдау түймешігін басыңыз.

  15. Келесі түймешігін басыңыз.

  16. Деректерді импорттау үшін Дайын түймешігіне басыңыз. Жылдам интернет қосылымымен импорттауға 15 минут жұмсалуы мүмкін. Аяқталған кезде 2 427 284 жолдың тасымалданғаны жөнінде күй есебін көресіз. Жабу түймешігін басыңыз. Деректер үлгісінде енді екі кесте болуы қажет. Оларды байланыстыру үшін әр кестеде үйлесімді бағандар болуы қажет.

  17. НегізгіКүнтізбеАҚШ тармағындағы КүнКілті 1/1/2012 00:00:00 пішіміндегі екенін көріңіз. Жоспарланған_өнімділік кестесінде де күні/уақыты бағаны, РейсКүні бар, мәндері бірдей пішімде көрсетіледі: 1/1/2012 00:00:00. Екі баған бірдей деректер түрінің сәйкес деректерін қамтиды және бағандардың кем дегенде біреуі (КүнКілті) бірегей мәндерді ғана қамтиды. Келесі бірнеше қадамдарда кестелерді байланыстыру үшін осы бағандарды пайдаланатын боласыз.

  18. Жаңа немесе бұрыннан бар жұмыс парағында жиынтық кесте жасау үшін Power Pivot терезесіндегі Жиынтық кесте түймешігін басыңыз.

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

  20. НегізгіКүнтізбеАҚШ жолағын шығарып алып, оны Жолдар аймағына қосу үшін КүнтізбедегіАй жолағын басыңыз.

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

  22. Өріс тізімінен “Кестелер арасындағы байланыстар қажет болуы мүмкін” тармағында Жасау түймешігін басыңыз.

  23. Байланысқан кестеден Жоспарланған_өнімділік жолағын таңдаңыз және Байланысқан бағаннан (Бастапқы) РейсКүні жолағын таңдаңыз.

  24. Кестеден НегізгіКүнтізбеАҚШ және бағаннан (Сыртқы) КүнКілті жолағын таңдаңыз. Байланыс жасау үшін OK түймешігін басыңыз.

  25. Кідіртілген минуттар жиынтығының енді әр айда әр түрлі екенін байқаңыз.

  26. НегізгіКүнтізбеАҚШ ЖылКілті жолағын КүнтізбедегіАй жолағының үстіндегі Жолдар аймағына апарыңыз.

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

Кеңестер :  Әдепкі бойынша, айлар алфавиттік ретпен тізімделген. Power Pivot қондырмасы арқылы айларды хронологиялық ретпен пайда болатындай етіп сұрыптауды өзгертуге болады.

  1. НегізгіКүнтізбеАҚШ кестесінің Power Pivot терезесінде ашық екенін тексеріңіз.

  2. Басты кестеден Баған бойынша сұрыптау түймешігін басыңыз.

  3. Сұрыптау ішінен КүнтізбедегіАй параметрін таңдаңыз.

  4. Сұрыптау әдісі ішінен ЖылАйлары параметрін таңдаңыз.

Жиынтық кесте енді әр жыл-ай тіркесімін (2011 қазан, 2011 қараша) жыл ішіндегі (10, 11) ай саны арқылы сұрыптайды. Сұрыптау ретін өзгерту оңай, себебі осы сценарий жұмысын жасау үшін КүнАғымы арнасы қажетті бағандардың барлығын көрсетеді. Егер әр түрлі уақыт функциясы кестесін пайдалансаңыз, қадам басқаша болады.

“Кестелер арасындағы байланыстар қажет болуы мүмкін”

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

Қатынас қажет болғанда Жасау түймешігі пайда болады

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

1-қадам: Байланыста қай кестені көрсету керектігін анықтау

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

Ажыратылған кестелерді көрсететін Диаграмма көрінісі

Ескерту :  Жиынтық кесте немесе Power View есебінде пайдаланған кезде жарамсыз бір мәнді емес байланыстарды жасау мүмкін емес. Барлық кестелеріңіз үлгідегі басқа кестелерге белгілі бір жолмен байланысқанын шамалаңыз, бірақ өрістерді басқа кестелерден жинақтауға тырысқан кезде “Кестелер арасындағы байланыстар қажет болуы мүмкін” хабарын аласыз. Ең мүмкін боларлық себеп "көпке көп" байланысына іске қосқандығыңыздан болуы мүмкін. Пайдаланғыңыз келетін кестелерге қосылған кесте байланыстарының тізбегін орындасаңыз, сізде екі немесе одан көп "көпке бір" кесте байланыстары бар екендігін анықтауыңыз мүмкін. Барлық жағдайларға жүретін оңай мәселені айналып өту жолы жоқ, бірақ бір кестеге пайдаланғыңыз келетін бағандарды жинақтау үшін есептелетін бағандар жасап көруіңізге болады.

2-қадам: Бір кестеден келесі кестеге жол жасауға пайдаланылуы мүмкін бағандарды табу

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

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

Сәйкес мәндердкен басқа байланыс жасаудың бірнеше қосымша талаптары бар:

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

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

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

Беттің жоғары бөлігі

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

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

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

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

×