Excel бағдарламасында белгілі бір тапсырмаларды орындағанда, кейде сіз бірнеше кестелермен айналысуыңыз керек, олар да бір-бірімен байланысты. Яғни, бір кестеден алынған деректер екіншісіне түседі және олар өзгергенде, барлық сәйкес кестелер ауқымындағы мәндер қайта есептеледі.
Байланыстырылған кестелер көптеген ақпаратты өңдеу үшін өте пайдалы. Бір кестедегі барлық ақпаратты алу өте ыңғайлы емес, егер ол біртекті болмаса. Мұндай объектілермен жұмыс істеу қиын және оларды іздеу қиын. Бұл мәселе арасындағы байланысты кестелерді жоюға арналған, олар арасында ақпарат бөлінеді, бірақ сонымен бірге өзара байланысты. Байланыстырылған кесте ауқымдары бір парақта немесе бір кітапта ғана емес, бөлек кітаптарда (файлдарда) орналастырылуы мүмкін. Іс жүзінде, соңғы екі нұсқасы ең жиі пайдаланылады, өйткені бұл технологияның мақсаты деректер жинақтаудан кету және оларды бір парақта жинақтау проблеманы түбегейлі шешпейді. Келіңіздер, деректерді басқарудың осы түрімен қалай жұмыс істеу керектігін үйренейік.
Байланыстырылған кестелерді жасау
Ең алдымен, әр түрлі кесте ауқымдары арасындағы байланыстың қалай қалыптасуы мүмкін екендігі туралы мәселеге тоқталайық.
1-әдіс: Кестелерді формуламен тікелей байланыстыру
Деректерді байланыстырудың ең оңай жолы - басқа кесте ауқымдарына сілтейтін формулаларды пайдалану. Ол тікелей байланысу деп аталады. Бұл әдіс интуитивті болып табылады, өйткені онымен байланыстыру дерлік бір кесте жиегінде деректерге сілтеме жасау сияқты бірдей түрде орындалады.
Келіңіздер, мысал тікелей байланыстыру арқылы байланыс жасай алатындығын қарастырайық. Бізде екі парақта екі үстел бар. Бір кестеде жалақы төлеу формуланы пайдаланып, барлық қызметкерлер үшін бірыңғай ставкамен жұмысшылардың санын көбейту жолымен есептеледі.
Екінші беттегі таблицалық ауқым бар, онда жалақысы бар қызметкерлердің тізімі бар. Екі жағдайда да қызметкерлердің тізімі сол тәртіппен берілген.
Екінші парағының тарифтері туралы мәліметтер бірінші болып келетін тиісті ұяшықтарда алынатындай етіп жасау керек.
- Бірінші парақта бірінші баған ұяшығын таңдаңыз. «Бета». Біз оның белгілерін қойдық "=". Содан кейін белгіге басыңыз «Sheet 2»Excel интерфейсінің сол жағында күй жолағының үстінде орналасқан.
- Құжаттың екінші аймағына жылжытады. Бағандағы бірінші ұяшықты басыңыз. «Бета». Содан кейін түймесін басыңыз. Кіру белгіше бұрын орнатылған ұяшыққа деректер енгізуді орындау үшін пернетақтада тең.
- Содан кейін бірінші параққа автоматты көшу бар. Көріп отырғаныңыздай, екінші кестеден бірінші қызметкердің тарифі тиісті ұяшыққа шығарылады. Курсорды ставканы қамтитын ұяшыққа орналастырған кезде, қарапайым формула экрандағы деректерді көрсету үшін пайдаланылатынын көреміз. Бірақ деректер көрсетілетін ұяшықтың координаттары алдында өрнек бар «Sheet2!»ол орналасқан жері туралы құжаттың атауын көрсетеді. Біздің жағдайымыздағы жалпы формула келесідей:
= Sheet2! B2 - Енді сіз деректерді кәсіпорынның барлық басқа қызметкерлерінің тарифтері бойынша беруіңіз керек. Әрине, мұны бірінші қызметкердің міндетін орындағандай етіп жасай аламыз, бірақ қызметкерлердің екі тізбесі бірдей тәртіппен ұйымдастырылғандықтан, тапсырманы айтарлықтай жеңілдетіп, оны шешуді жылдамдатуға болады. Бұл формуланы төмендегі ауқымға көшіру арқылы ғана жүзеге асыруға болады. Excel-те сілтемелер әдепкі бойынша салыстырмалы болып табылады, олар көшіру кезінде мәндерді ауыстыру қажет. Көшіру процедурасының өзі толтыру маркері арқылы орындалуы мүмкін.
Мәселен, элементтің төменгі оң жағына курсорды формуламен қойыңыз. Осыдан кейін курсорды қара крест түрінде толтыру керек. Біз сол жақ тінтуірдің қысқышын орындаймыз және курсорды бағанның ең төменгі бөлігіне сүйреңіз.
- Бір бағандағы барлық деректер 2-парақ үстелге тартылды Парақ 1. Деректер өзгерген кезде 2-парақ олар автоматты түрде бірінші болып өзгереді.
2-әдіс: INDEX - MATCH операторларының тобын қолданыңыз
Ал егер кестеленген массивтердегі қызметкерлердің тізімі сол тәртіппен реттелмесе не болады? Бұл жағдайда, жоғарыда айтылғандай, нұсқалардың бірі қолмен байланыстырылуы тиіс әрбір ұяшықтың арасындағы байланысты орнату болып табылады. Бірақ бұл тек шағын үстелдер үшін жарамды. Үлкен диапазондар үшін бұл нұсқа ең жақсы жағдайда іске асыруға көп уақыт кетеді, ал нашар жағдайда - іс жүзінде мүлдем мүмкін болмайды. Бірақ сіз бұл мәселені операторлар тобымен шеше аласыз INDEX - MATCH. Оны алдыңғы әдіспен талқыланған кестелік ауқымдардағы деректермен байланыстыру арқылы қалай жасауға болатынын қарастырайық.
- Бағандағы бірінші элементті таңдаңыз. «Бета». Өту Функция шеберібелгішені басу арқылы «Функцияны кірістіру».
- Ин Функция шебері топта «Сілтемелер және массивтер» атын тауып, таңдап алыңыз INDEX.
- Бұл оператордың екі пішіні бар: массивтермен жұмыс істеу және сілтеме. Біздің жағдайда бірінші нұсқа қажет, сондықтан ашылатын келесі пішінді таңдау терезесінде біз оны таңдап, батырманы басыңыз «ОК».
- Оператор дәлел терезесі іске қосылды. INDEX. Көрсетілген функцияның міндеті таңдалған ауқымда көрсетілген мәнді көрсетілген жолдағы мәнді көрсету болып табылады. Жалпы оператор формуласы INDEX бұл:
= INDEX (массив; сызықтық нөмір; [column_number])«Массив» - көрсетілген жолдың санымен ақпаратты шығаратын ауқымның мекенжайын қамтитын дәлел.
«Жол нөмірі» - дәл осы сызықтың өзі болып табылатын дәлел. Жолдың нөмірін бүкіл құжатқа қатысты емес, тек таңдалған массивке қатысты анықтау керек.
«Баған нөмірі» - Аргумент міндетті емес. Біздің мәселемізді нақты шешу үшін біз оны пайдаланбаймыз, сондықтан оның мәнін бөлек сипаттаудың қажеті жоқ.
Курсорды өріске қойыңыз «Массив». Содан кейін барыңыз 2-парақ және тінтуірдің сол жақ батырмасын ұстап, бағанның барлық мазмұнын таңдаңыз «Бета».
- Оператор терезесінде координаталар көрсетілгеннен кейін курсорды өріске қойыңыз «Жол нөмірі». Бұл дәлелді оператор арқылы көрсетеміз MATCH. Сондықтан функция жолының сол жағында орналасқан үшбұрышты нұқыңыз. Жақында пайдаланылған операторлардың тізімі ашылады. Егер сіз олардың атын тапсаңыз «MATCH»онда сіз оны баса аласыз. Әйтпесе, тізімдегі ең соңғы элементті басыңыз - «Басқа мүмкіндіктер ...».
- Стандартты терезе басталады. Функцияның шеберлері. Бір топта оған барыңыз. «Сілтемелер және массивтер». Бұл жолы тізімде элементті таңдаңыз «MATCH». Түймені басыңыз. «ОК».
- Оператор терезесінің дәлелдерін белсендіреді MATCH. Көрсетілген функция белгілі бір масштабтағы мәннің санын оның аты бойынша көрсету үшін арналған. Осы мүмкіндіктің арқасында біз белгілі бір мәннің жол нөмірін есептейміз INDEX. Синтаксис MATCH ретінде ұсынылды:
= MATCH (іздеу мәні; іздеу массиві [match_type])«Қажет мән» - ол орналасқан үшінші тарап ауқымының ұяшығының аты немесе мекенжайын қамтитын дәлел. Бұл атауды есептелетін мақсатты диапазондағы позициясы. Біздің жағдайда бірінші дәлел ұялы сілтемелер болады Парақ 1онда қызметкерлердің есімдері орналасқан.
«Қарастырылған массив» - көрсетілген мәнді оның орнын анықтау үшін ізделетін массивке сілтемесін білдіретін дәлел. Біз бұл рөлді мекен-жай бағанында ойнаймыз «Аты-жөні қосу 2-парақ.
«Түрді салыстыру» - факультативті болып табылатын дәлел, бірақ, алдыңғы мәлімдемеге қарағанда, біз бұл міндетті емес дәлелге мұқтажбыз. Ол оператордың массивпен қажетті мәнге сәйкес келетінін көрсетеді. Бұл дәлелде үш мәннің біреуі болуы мүмкін: -1; 0; 1. Реттелмеген массивтер үшін параметрді таңдаңыз "0". Бұл опция біздің ісімізге сай келеді.
Мәселен, дәлелдер терезесінің өрістерін толтыра бастаймыз. Курсорды өріске қойыңыз «Қажет мән», бағанның бірінші ұяшығын басыңыз «Аты» қосу Парақ 1.
- Координаттар көрсетілгеннен кейін курсорды өріске орнатыңыз «Қарастырылған массив» және енбелгіге өтіңіз «Sheet 2»ол Excel терезесінің төменгі жағында күй жолағының үстінде орналасқан. Тінтуірдің сол жақ батырмасын ұстап, бағандағы барлық ұяшықтарды бөлектеңіз. «Аты».
- Олардың координаттары өрістерде көрсетілгеннен кейін «Қарастырылған массив»далаға барыңыз «Түрді салыстыру» пернетақтадан нөмірді орнатыңыз "0". Осыдан кейін тағы да далаға қайта ораламыз. «Қарастырылған массив». Өйткені, біз бұрынғы әдіспен жасаған формуланы көшіруіміз керек. Мекенжайлардың өзара есепке алынуы болады, бірақ біз қаралатын массивтің координаттарын түзету керек. Ол өзгермеу керек. Курсордың координаттарын таңдап, функционалдық пернені басыңыз F4. Көріп тұрғаныңыздай, координаттардың алдында доллар белгісі пайда болды, яғни салыстырмалы түрде алынған сілтеме абсолютті болды. Содан кейін түймесін басыңыз «ОК».
- Нәтиже бағанның бірінші ұяшығында көрсетіледі. «Бета». Бірақ көшіруден бұрын, біз басқа аумақты, яғни функцияның алғашқы дәлелін түзетуіміз керек INDEX. Ол үшін формуланы қамтитын бағанның элементін таңдап, формула жолына өтіңіз. Оператордың бірінші дәлелін таңдаңыз INDEX (B2: B7) батырмасын басыңыз F4. Көріп отырғаныңыздай, таңдалған координаттардың жанында доллар белгісі пайда болды. Түймешікті басыңыз Кіру. Жалпы формула келесі форманы алды:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0)) - Енді толтыру белгісін қолданып көшіруге болады. Біз оны ертерек айтқанымыздай етіп шақырыңыз және оны кесте ауқымының соңына дейін созыңыз.
- Көріп тұрғаныңыздай, екі байланысты кестелердің қатарына сәйкес келмейтініне қарамастан, барлық мәндер жұмысшылардың есімдері бойынша күшейтіледі. Оған операторлардың комбинациясын пайдалану арқылы қол жеткізілді INDEX-MATCH.
Сондай-ақ, қараңыз:
Excel функциясы INDEX
Excel бағдарламасындағы матч функциясы
3-әдіс: Байланыстырылған деректермен математикалық амалдарды орындау
Тікелей деректерді байланыстыру сонымен қатар кестелердің бірінде басқа кесте ауқымдарында көрсетілетін мәндерді ғана көрсетіп қана қоймай, онымен әртүрлі математикалық амалдарды орындау үшін (қосу, бөлісу, алып тастау, көбейту және т.б.) жақсы мүмкіндік береді.
Келіңіздер, бұл қалай іс жүзінде жүзеге асады. Мұны істейік Парақ 3 қызметкерлердің жалақысы туралы жалпы деректер қызметкерлердің бұзылуынсыз көрсетіледі. Бұл үшін қызметкерлердің тарифтері алынып тасталады 2-парақ, (функцияны пайдалана отырып) SUM) формула бойынша коэффициентке көбейтіледі.
- Жалпы жалақы көрсеткіші көрсетілетін ұяшықты таңдаңыз Парақ 3. Түймешікті басыңыз «Функцияны кірістіру».
- Терезені іске қосу қажет Функцияның шеберлері. Топқа барыңыз «Математикалық» және сол жерде есімін таңдаңыз «СУММ». Бұдан әрі батырманы басыңыз «ОК».
- Function argument терезесіне жылжу SUMол таңдалған сандардың сомасын есептеуге арналған. Келесі синтаксисі бар:
= SUM (сан1; сан2; ...)Терезенің өрістері көрсетілген функцияның аргументтеріне сәйкес келеді. Дегенмен олардың саны 255 дана жетуі мүмкін, біздің мақсатымыз үшін тек біреу жеткілікті. Курсорды өріске қойыңыз «№1». Жапсырманы басыңыз «Sheet 2» күй жолағынан жоғары.
- Кітаптың қажетті бөлігіне көшкеннен кейін, жинақталуға тиіс бағанды таңдаңыз. Біз оны курсорды жасаймыз, ол тінтуірдің сол жақ батырмасын ұстап тұрады. Көріп отырғандай, таңдалған аймақтың координаттары дереу дәлел терезесінің өрісінде көрсетіледі. Содан кейін түймесін басыңыз. «ОК».
- Осыдан кейін біз автоматты түрде қозғаламыз Парақ 1. Көріп отырғаныңыздай, жұмысшылардың жалақы мөлшерінің жалпы мөлшері тиісті элементте көрсетіледі.
- Бірақ бұл бәрі емес. Естеріңізде болса, жалақы коэффициент бойынша ставка құнының көбеюімен есептеледі. Сондықтан, біз қайтадан жинақталған мән орналасқан ұяшықты таңдап аламыз. Содан кейін формула жолына өтіңіз. Оның формуласына көбейту белгісін қосамыз (*), содан кейін коэффициент орналасқан элементті басыңыз. Есептеуді орындау үшін басыңыз Кіру пернетақтада. Көріп отырғаныңыздай, бағдарлама кәсіпорын үшін жалпы жалақыны есептеді.
- Артқа қайту 2-парақ және кез-келген қызметкердің мөлшерін өзгертіңіз.
- Осыдан кейін қайтадан жалпы сомаға барыңыз. Көріп отырғаныңыздай, тиісті кестедегі өзгерістерге байланысты жалпы жалақының нәтижесі автоматты түрде қайта есептелді.
4-әдіс: арнайы кірістіру
Сондай-ақ, кесте массивтерін Excel бағдарламасында арнайы кірістірумен байланыстыруға болады.
- Басқа кестеге «керілген» болуы керек мәндерді таңдаңыз. Біздің жағдайда бұл бағандар ауқымы. «Бета» қосу 2-парақ. Тінтуірдің оң жақ түймешігімен таңдалған фрагментті басыңыз. Ашылатын тізімде элементті таңдаңыз «Көшіру». Баламалы пернелер тіркесімі Ctrl + C. Содан кейін көшу Парақ 1.
- Кітаптың қажетті аймағына жылжу біз құндылықтарды тартқыңыз келетін ұяшықтарды таңдаймыз. Біздің жағдайда бұл баған. «Бета». Тінтуірдің оң жақ түймешігімен таңдалған фрагментті басыңыз. Құралдар тақтасындағы мәтінмәндік мәзірде «Ендіру параметрлері» белгішені нұқыңыз «Сілтемені кірістіру».
Балама да бар. Айтпақшы, бұл Excel бағдарламасының ескі нұсқалары үшін жалғыз ғана. Контекстік мәзірде меңзерді элементке жылжытыңыз «Арнайы қою». Ашылатын қосымша мәзірде бірдей атпен элементті таңдаңыз.
- Содан кейін арнайы кірістіру терезесі ашылады. Біз батырманы басамыз «Сілтемені кірістіру» ұяшықтың төменгі сол жақ бұрышында.
- Қандай нұсқаны таңдасаңыз, бір кесте массивіндегі мәндер екіншіге енгізіледі. Дереккөздегі деректерді өзгерткен кезде олар кірістірілген ауқымда автоматты түрде өзгереді.
Сабақ: Excel бағдарламасында арнайы қойыңыз
5-әдіс: Бірнеше кітаптардағы кестелер арасындағы байланыс
Сонымен қатар, түрлі кітаптардағы кестелік кеңістіктер арасындағы байланысты ұйымдастыра аласыз. Бұл арнайы кірістіру құралын пайдаланады. Әрекеттер бұрынғы әдіспен қарастырғандарға мүлдем ұқсайды, тек формулаларды енгізу кезінде навигация бір кітаптың облыстары арасында емес, файлдар арасында орын алмайтын болады. Әрине, барлық байланысты кітаптар ашық болуы керек.
- Басқа кітапқа ауысатын деректер ауқымын таңдаңыз. Оны тінтуірдің оң жақ түймешігімен басып, ашылатын мәзірде позицияны таңдаңыз «Көшіру».
- Содан кейін біз осы деректерді енгізу қажет кітапқа көшеміз. Қажетті диапазонды таңдаңыз. Тышқанның оң жақ батырмасын басыңыз. Топтағы мәтінмәндік мәзірде «Ендіру параметрлері» элементті таңдаңыз «Сілтемені кірістіру».
- Осыдан кейін мәндер енгізіледі. Деректерді бастапқы кітабында өзгерткен кезде, жұмыс кітабындағы кестелік массаны оларды автоматты түрде тартып алады. Және бұл екі кітап үшін де бұл үшін ашық емес. Бір ғана жұмыс кітабын ашу жеткілікті, және егер ол бұрын жасалған болса, ол автоматты түрде жабық сілтеме жасалған құжатта деректерді тартады.
Бірақ бұл жағдайда бұл кірістіру өзгермейтін массив түрінде жасалатынын атап өту керек. Енгізілген деректермен бірге кез келген ұяшықты өзгертуге тырыссаңыз, хабар сізге бұл мүмкін емес екендігін хабарлайды.
Басқа массивтермен байланысты массивтердегі өзгерістер тек сілтемені бұзу арқылы жасалуы мүмкін.
Кестелер арасындағы ажырату
Кейде кесте ауқымдары арасындағы байланысты бұзу қажет. Мұның себебі, жоғарыда сипатталғандай, басқа кітаптан енгізілген жиынды өзгерту керек болғанда немесе пайдаланушы бір кестедегі деректерді екіншіден автоматты түрде жаңартқысы келмегендіктен болуы мүмкін.
1-әдіс: кітаптарды ажырату
Іс жүзінде бір операцияны орындау арқылы барлық ұяшықтардағы кітаптар арасындағы байланыс үзілуі мүмкін. Сонымен бірге, ұяшықтардағы деректер қалады, бірақ олар басқа құжаттарға тәуелді емес статикалық жаңартылмаған мәндер болып қалады.
- Басқа файлдардың мәндері тартылған кітабында қойындыға өтіңіз «Деректер». Белгішені басыңыз «Сілтемелерді өңдеу»ол құралдар блогында таспада орналасқан «Қосылымдар». Назар аударыңыз, егер ағымдағы кітапта басқа файлдарға сілтемелер болмаса, бұл түйме белсенді емес.
- Сілтемелерді өзгерту терезесі іске қосылды. Байланысты үзуге талпынатын файлдар тізімін (егер бар болса) таңдаңыз. Түймешікті басыңыз «Сілтемені үзу».
- Кейінгі әрекеттердің салдары туралы ескерту бар ақпараттық терезе ашылады. Егер сіз не істейтініңізге сенімді болсаңыз, онда батырманы басыңыз. «Байланыстарды үзу».
- Содан кейін ағымдағы құжатта көрсетілген файлға барлық сілтемелер статикалық мәндермен ауыстырылады.
2-әдіс: Мәндерді кірістіру
Бірақ жоғарыда аталған әдіс екі кітаптың арасындағы барлық байланыстарды толығымен ажырату қажет болған жағдайда ғана жарамды. Бір файлдағы сәйкес кестелерді ажыратқыңыз келсе, не істеу керек? Мұны деректерді көшіру арқылы жасауға және оны мәндермен бір жерге қоюға болады.Айтпақшы, сол әдіс файлдар арасындағы жалпы байланысты үзбей, түрлі кітаптардың жеке деректер ауқымдары арасындағы байланысты бұзу үшін пайдаланылуы мүмкін. Бұл әдістің іс жүзінде қалай жұмыс істейтінін көрейік.
- Басқа кестеге сілтемені алып тастағымыз келетін ауқымды таңдаңыз. Оны тінтуірдің оң жақ түймешігімен басыңыз. Ашылатын мәзірде элементті таңдаңыз «Көшіру». Осы әрекеттердің орнына баламалы пернелер тіркесімін теруіңізге болады. Ctrl + C.
- Содан кейін, таңдауды сол фрагменттен алып тастамастан, оны тінтуірдің оң жақ түймешігімен қайта басамыз. Бұл жолы біз іс-әрекеттер тізімінде белгішені басамыз «Мәндер»ол құралдар тобына орналастырылған «Ендіру параметрлері».
- Содан кейін таңдалған ауқымдағы барлық сілтемелер статикалық мәндермен ауыстырылады.
Көріп отырғаныңыздай, Excel бағдарламасында бірнеше кестелерді біріктіретін әдістер мен құралдар бар. Бұл жағдайда кестелік деректер басқа парақтарда және тіпті әртүрлі кітаптарда болуы мүмкін. Қажет болса, бұл байланыс оңай бұзылуы мүмкін.
