Excelде белгилүү бир тапшырмаларды аткарууда кээде бири-бирине байланыштуу болгон бир нече таблицалар менен иштешүүгө туура келет. Башкача айтканда, бир таблицадан алынган маалыматтар экинчисине киргизилет жана алар өзгөрүлгөндө, бардык тиешелүү жадыбалдын диапазондорунда маанилер кайра эсептелип чыгат.
Шилтемеленген таблицалар маалыматтын чоң көлөмүн иштеп чыгуу үчүн абдан ыңгайлуу. Бардык маалыматтарды бир таблицага жайгаштыруу, андан тышкары, эгер ал бир тектүү болбосо, анда ал ыңгайлуу эмес. Мындай объектилер менен иштөө жана аларды издөө кыйынга турат. Көрсөтүлгөн көйгөй байланыштырылган таблицалар аркылуу четтетилип, алардын ортосунда маалымат таратылат, бирок ошол эле учурда бири-бирине байланыштуу. Шилтемеленген стол диапазондору бир барактын ичинде же бир китепте гана эмес, өзүнчө китептерде (файлдарда) жайгаштырылышы мүмкүн. Акыркы эки вариант иш жүзүндө көбүнчө колдонулат, анткени бул технологиянын максаты маалыматтардын топтолушунан алыс болуу жана аларды бир баракка коюу көйгөйдү түп-тамырынан чечпейт. Келгиле, маалыматтарды башкаруунун бул түрүн кантип түзүүнү жана кантип иштөөнү билип алалы.
Шилтемеленген таблицаларды түзүү
Биринчиден, ар кандай стол диапазондорунун ортосунда өз ара мамиле түзүүгө мүмкүнчүлүк бар деген суроого токтололу.
1-ыкма: таблицаларды формула менен түз байланыштырыңыз
Маалыматтарды байлоонун эң оңой жолу - бул башка таблицалар диапазонуна шилтеме берген формулаларды колдонуу. Бул түздөн-түз милдеттүү деп аталат. Бул ыкма интуитивдүү, анткени анын жардамы менен шилтеме бир таблицанын массивинде берилиштерге шилтемелерди түзүү сыяктуу эле жүргүзүлөт.
Келгиле, байланыштыруу аркылуу кандайча байланыш түзүүгө болорун карап көрөлү. Эки баракта эки стол бар. Эмгек акы бир столдо кызматкерлердин ставкасын бардыгына бирдиктүү коэффициентке көбөйтүү менен эсептелет.
Экинчи баракта таблицалар диапазону бар, анда кызматкерлердин эмгек акыларынын тизмеси келтирилген. Эки учурда тең кызматкерлердин тизмеси бирдей тартипте берилет.
Экинчи таблицанын чендери боюнча маалыматтар биринчи катмардын тиешелүү уячаларына киргизилгендигин текшерип турушу керек.
- Биринчи барактан, тилкедеги биринчи уячаны тандаңыз "Bet". Ага кол койдук "=". Андан соң, жарлыкты чыкылдатыңыз "2-барак", абал тилкесинин жогору жагында Excel интерфейсинин сол жагында жайгашкан.
- Документтин экинчи жагына жылат. Колоннадагы биринчи уячаны басабыз "Bet". Андан кийин баскычты чыкылдатыңыз кирүү белги коюлган уячага маалымат киргизүү үчүн, клавиатурада "Барабар".
- Андан кийин биринчи баракка автоматтык түрдө өтүү болот. Көрүнүп тургандай, экинчи столдогу биринчи кызматкердин ставкасы тиешелүү уячага салынат. Курсор коюмду камтыган уячага жайгаштыруу менен, кадимки формула маалыматтарды экранда көрсөтүү үчүн колдонулат. Бирок маалымат чыккан жерде уячанын координаттарынын алдында бир сөз айкашы бар "Sheet2!", алар жайгашкан документтин аталышын көрсөтөт. Биздин формабыздын жалпы формуласы мындай:
= 2-барак! B2 - Эми сиз ишкананын башка кызматкерлеринин ставкалары жөнүндө маалыматтарды өткөрүп беришиңиз керек. Албетте, бул биринчи кызматкердин тапшырмасын биз аткарган жол менен жасалышы мүмкүн, бирок кызматкерлердин экөө тең бирдей тартипте жайгаштырылгандыгын эске алганда, тапшырма бир кыйла жөнөкөйлөтүлүп, аны чечүүнү тездетет. Муну формуланы төмөнкү диапазонго көчүрүү менен жасаса болот. Демейкиде, Excelдеги шилтемелер салыштырмалуу болгондуктан, көчүрүлгөндө, маанилер жылып турат, бул бизге керек. Көчүрүү процедурасынын өзү толтуруу маркерин колдонсо болот.
Ошентип, курсорду формуланын жардамы менен элементтин төмөнкү оң жагына коюңуз. Андан кийин, курсор кара кайчылаш түрүндөгү толтуруу маркерине айландырылышы керек. Сол чычкан баскычты кысып, курсорду тилкенин эң түбүнө сүйрөңүз.
- Окшош тилкедеги бардык маалыматтар 2-барак үстөлгө ыргытышты 1-барак. Дайындарды өзгөртүүдө 2-барак биринчиси автоматтык түрдө өзгөрүлөт.
2-ыкма: INDEX операторлорунун тутумун колдонуу - SEARCH
Таблица массивдериндеги кызматкерлердин тизмеси бирдей эмес болсочу? Бул учурда, жогоруда айтылгандай, кол менен туташтырылышы керек болгон ар бир клетканын ортосунда байланыш түзүү. Бирок бул кичинекей үстөлдөргө гана ылайыктуу. Масштабдуу диапазондор үчүн мындай вариантты ишке ашыруу үчүн көп убакыт талап кылынат, эң жаманы, иш жүзүндө бул мүмкүн эмес. Бирок бул көйгөйдү бир топ операторлордун жардамы менен чечсе болот INDEX - MATCH. Буга чейинки ыкмада талкууланган таблицалардагы маалыматтарды шилтеме кылуу менен муну кантип кылса болорун карап көрөлү.
- Биринчи тилкенин элементин тандаңыз "Bet". Барыңыз Мүмкүнчүлүк чеберисөлөкөтүн чыкылдатып "Кыстыруу функциясы".
- The Функция устасы топто Шилтемелер жана массивдер атын таап, бөлүп көрсөтүңүз "INDEX".
- Бул оператордун эки формасы бар: массивдер менен иштөө форма жана маалымдама. Биздин учурда, биринчи вариант талап кылынат, андыктан кийинки терезеде ачылган форманы тандоо үчүн, аны тандап, баскычты чыкылдатыңыз "OK".
- Оператордун аргументтер терезеси ишке киргизилди INDEX. Бул функциянын милдети - тандалган диапазондогу маанини көрсөтүлгөн сан менен сапка чыгаруу. Жалпы оператордун формуласы INDEX мындай:
= INDEX (массив; катар_аты; [мамычанын номери])"Array" - көрсөтүлгөн сызыктын номери боюнча маалымат алып жаткан диапазондун дарегин камтыган аргумент.
Саптын номери - дал ушул катардын далили болгон аргумент. Сызыктын номери толугу менен документке эмес, тандалган массивге карата гана көрсөтүлүшү керек экендигин билүү маанилүү.
Тилкенин номери - милдеттүү эмес жүйөө. Биз аны конкреттүү көйгөйүбүздү чечүү үчүн колдонбойбуз, андыктан анын маңызын өзүнчө сүрөттөп кереги жок.
Курсорду талаага кой "Array". Андан кийин, барып 2-барак чычкандын сол баскычын кармап, тилкенин бардык мазмунун тандаңыз "Bet".
- Оператордун терезесинде координаттар көрсөтүлгөндөн кийин, курсорду талаага коюңуз Саптын номери. Бул аргументти оператордун жардамы менен чыгарабыз MATCH. Ошондуктан, функция линиясынын сол жагында жайгашкан үч бурчтукту басабыз. Жакында колдонулган операторлордун тизмеси ачылат. Эгер алардын арасынан ат тапсаңыз "Оюну"анда сиз аны бассаңыз болот. Болбосо, тизмедеги акыркы нерсени чыкылдатыңыз - "Башка өзгөчөлүктөр ...".
- Стандарттуу терезе башталат Функция усталары. Биз ошол топко өтүп кетебиз Шилтемелер жана массивдер. Бул жолу тизмедеги нерсени тандаңыз "Оюну". Баскычты чыкылдатыңыз. "OK".
- Оператордун аргументтер терезеси жандырылды MATCH. Көрсөтүлгөн функция белгилүү бир массивдеги аталыштын санын көрсөтүүгө арналган. Бул функциянын аркасында функциянын белгилүү бир маанисинин сызык номерин эсептеп чыгабыз INDEX. синтаксиси MATCH төмөнкүчө көрсөтүлгөн:
= SEARCH (издөө_ мааниси; издөө_арабы; [match_type])"Маани издөө" - үчүнчү жактын диапазонунда жайгашкан уячанын атын же дарегин камтыган жүйө. Бул аталышты максаттуу диапазондо эсептөө керек. Биздин учурда, биринчи аргумент клеткаларга шилтемелер болот 1-бараккызматкерлердин аты-жөнү жайгашкан жерде.
Көрүлгөн массив - анын ордун аныктоо үчүн көрсөтүлгөн маани изделип жаткан массивге шилтеме берген аргумент. Тилке дареги "бул жерде бул ролду ойнойт."Аты боюнча 2-барак.
Дал келүү түрү - аргумент, бул милдеттүү эмес, бирок мурунку билдирүүдөн айырмаланып, бизге бул кошумча аргумент керек болот. Ал оператор издөө массивине массив менен кандайча дал келээрин көрсөтөт. Бул аргумент үч маанинин бири болушу мүмкүн: -1; 0; 1. Иреттелбеген массивдер үчүн тандаңыз "0". Бул параметр биздин жагдайга ылайыктуу.
Ошентип, аргументтер терезесинин талааларын толтурууну баштайлы. Курсорду талаага кой "Маани издөө"тилкенин биринчи клеткасын чыкылдатыңыз "Name" боюнча 1-барак.
- Координаттар көрүнгөндөн кийин, курсорду курсорго коюңуз Көрүлгөн массив жана жарлыкты чыкылдатыңыз "2-барак", статусу тилкесинин үстүндө, Excel терезесинин ылдый жагында жайгашкан. Сол чычкан баскычын басып, курсор менен бардык тилкелердеги уячаларды тандаңыз "Name".
- Кийин алардын координаттары талаага көрүнөт Көрүлгөн массивталаага баруу Дал келүү түрү клавиатурадан ошол номерди коюңуз "0". Андан кийин биз кайрадан талаага кайтабыз Көрүлгөн массив. Чындыгында, биз мурунку ыкмадагыдай формуланы көчүрөбүз. Даректердин жылышы болот, бирок бул жерде каралып жаткан массивдин координаттарын оңдошубуз керек. Аны которбош керек. Курсор менен координаттарды тандап, функция баскычын басыңыз F4. Көрүнүп тургандай, доллар белгиси координаттардын алдында пайда болгон, бул шилтеме салыштырмалуу абсолюттукка айланганын билдирет. Андан кийин баскычты чыкылдатыңыз "OK".
- Натыйжа мамычанын биринчи уясында көрсөтүлөт. "Bet". Бирок көчүрүүдөн мурун, биз башка аймакты, тактап айтканда, функциянын биринчи аргументин оңдошубуз керек INDEX. Бул үчүн формула камтылган тилке элементин тандап, формулалар сызыгына өтүңүз. Оператордун биринчи аргументин тандап алабыз INDEX (B2: B7) жана баскычты чыкылдатыңыз F4. Көрүнүп тургандай, доллар белгиси тандалган координаттардын жанында пайда болду. Баскычты чыкылдатыңыз кирүү. Жалпысынан, формула төмөнкү формада болду:
= INDEX (2-барак! $ B $ 2: $ B $ 7; издөө (Sheet1! A4; 2-барак; $ A $ 2: $ A $ 7; 0)) - Эми сиз толтуруу маркерин колдонуп көчүрсөңүз болот. Биз аны жогоруда сөз кылган жол менен атайбыз жана аны столдун аягына чейин создук.
- Көрүнүп тургандай, эки байланышкан таблицалардын катар тартиби бири-бирине дал келбесе дагы, баардык маанилер кызматкерлердин фамилиялары боюнча чогултулат. Буга операторлордун айкалышын колдонуу аркылуу жетишилди INDEX-MATCH.
Дагы окуңуз:
Excelде EXEX функциясы
Excelде EXCEL функциясы
3-ыкма: Математикалык операцияларды тиешелүү маалыматтар менен аткарыңыз
Түздөн-түз маалыматтарды байлоо жакшы, анткени ал башка таблицалар диапазондорунда чагылдырылган маанилерди гана көрсөтпөстөн, ошондой эле алар менен ар кандай математикалык операцияларды (кошуу, бөлүү, бөлүп чыгаруу, көбөйтүү ж.б.) жүргүзүүгө мүмкүнчүлүк берет.
Келгиле, бул иш жүзүндө кандайча ишке ашырылаарын карап көрөлү. Келгиле, ошону жасайлы 3-барак компаниянын эмгек акысы жөнүндө жалпы маалыматтар кызматкерлер тарабынан бөлүштүрүлбөй көрсөтүлөт. Бул үчүн, кызматкердин ставкасы тартып алынат 2-барак, жалпыланган (функцияны колдонуу менен) сум) формуласын колдонуп коэффициент менен көбөйтүңүз.
- Эмгек акы эсептөөнүн натыйжасы көрсөтүлө турган уячаны тандаңыз. 3-барак. Баскычты чыкылдатыңыз. "Кыстыруу функциясы".
- Терезеден баштоо керек Функция усталары. Топко барыңыз "Математикалык" жана ошол жерде атты тандаңыз "Сум". Андан кийин, баскычты чыкылдатыңыз "OK".
- Функциянын аргументтери терезеге жылдырылат сумтандалган сандардын суммасын эсептөө үчүн иштелип чыккан. Анын төмөнкү синтаксиси бар:
= SUM (number1; number2; ...)Терезенин талаалары көрсөтүлгөн функциянын жүйөлөрүнө дал келет. Алардын саны 255ке жетиши мүмкүн, бирок биздин максатка бирөө гана жетиштүү болот. Курсорду талаага кой "Саны 1". Жарлыкты чыкылдатыңыз "2-барак" абал тилкесинен жогору.
- Китептин каалаган бөлүмүнө өткөндөн кийин, жалпыланган графаны тандаңыз. Биз чычкандын сол баскычын кармап жатканда курсор менен жасайбыз. Көрүнүп тургандай, тандалган аймактын координаттары ошол замат аргументтер терезесинде көрсөтүлөт. Андан кийин баскычты чыкылдатыңыз "OK".
- Андан кийин, биз автоматтык түрдө көчүп 1-барак. Көрүнүп тургандай, жумушчу табыштамалардын жалпы суммасы буга чейин тиешелүү элементте көрсөтүлгөн.
- Бирок бул дагы деле эмес. Эсибизде, эмгек акы ставканын маанисин коэффициентке көбөйтүү жолу менен эсептелет. Демек, биз кайрадан суммалык мааниси жайгашкан уячаны тандайбыз. Андан кийин биз формулалардын катарына өтөбүз. Андагы формулага көбөйтүү белгисин кошуңуз (*) коэффициентинин индикатору жайгашкан элементти чыкылдатыңыз. Эсептөө үчүн, баскычты чыкылдатыңыз кирүү клавиатурада. Көрүнүп тургандай, программа ишкананын жалпы эмгек акысын эсептеген.
- Артка 2-барак жана каалаган кызматкердин ставкасын өзгөртүү.
- Андан кийин, биз жалпы суммасы менен кайрадан баракчага өтөбүз. Көрүнүп тургандай, байланыштырылган таблицанын өзгөрүүсүнө байланыштуу, жалпы эмгек акынын натыйжасы автоматтык түрдө кайра эсептелген.
4-ыкма: бажы кыстаруу
Ошондой эле, атайын кошумча кыстырманы колдонуп, Excelдеги таблица массивдерин шилтеме кылсаңыз болот.
- Башка таблицага "тартуу" керек болгон маанилерди тандайбыз. Биздин учурда, бул мамычанын диапазону "Bet" боюнча 2-барак. Тандалган фрагментти чычкан оң баскычы менен басабыз. Ачкан тизмеде, тандаңыз "Көчүрүү". Альтернативдүү тергич жарлык Ctrl + C. Андан кийин биз көчүп 1-барак.
- Бизге керек болгон китептин аймагына өткөндөн кийин, биз баалуулуктарды алышыбыз керек болгон уячаларды тандайбыз. Биздин учурда, бул тилке "Bet". Тандалган фрагментти чычкан оң баскычы менен басабыз. Куралдар блогундагы контексттик менюда Жолдор салыңыз сөлөкөтүн чыкылдатыңыз Шилтемени чаптаңыз.
Альтернатива дагы бар. Баса, бул Excelдин эски версиялары үчүн бирден-бир. Контексттик менюдан, үстүнө өтүңүз "Атайын кошуу". Кошумча ачылган менюдан ошол эле ат менен позицияны тандаңыз.
- Андан кийин, атайын кыстаруу терезеси ачылат. Баскычты чыкылдатыңыз Шилтемени чаптаңыз клетканын төмөнкү сол бурчунда.
- Кайсы гана жолду тандабаңыз, бир таблица массивинен экинчисине маанилер киргизилет. Булактагы маалыматты өзгөрткөндө, алар киргизилген диапазондо автоматтык түрдө өзгөрөт.
Сабак: Excelге атайын кыстырма
5-ыкма: бир нече китептердеги таблицалардын ортосундагы байланыш
Мындан тышкары, сиз ар кандай китептердеги столдордун ортосундагы байланышты уюштура аласыз. Атайын орнотуу куралы колдонулат. Аракеттер мурунку ыкмада каралгандай эле болот, бир эле китептин райондорунун ортосунда эмес, файлдар ортосунда формулалар жасоодо чабыттап өтүшүңүз керек болот. Албетте, бардык тиешелүү китептер ачык болушу керек.
- Башка китепке өткөргүңүз келген маалыматтардын диапазонун тандаңыз. Оң баскычты чыкылдатып, ачылган менюдагы позицияны тандаңыз. "Көчүрүү".
- Андан кийин биз бул маалыматты киргизүү керек болгон китепке өтөбүз. Керектүү диапазонду тандаңыз. Оң баскычты чыкылдатыңыз. Топтогу контексттик менюда Жолдор салыңыз нерсени тандоо Шилтемени чаптаңыз.
- Андан кийин, маанилер киргизилет. Булак иш китепчесиндеги маалыматтар өзгөргөндө, жумуш китепчесиндеги таблица массиви аны автоматтык түрдө тартып алат. Бул үчүн эки китеп тең ачык болушу шарт эмес. Бир гана жумуш китепчесин ачуу жетиштүү жана буга чейин өзгөртүүлөр киргизилген болсо, автоматтык түрдө жабык шилтемеленген документтен маалыматтарды тартып алат.
Бирок белгилей кетүү керек, бул учурда кошулма массив катары жасалат. Киргизилген маалыматтар менен каалаган уячаны өзгөртүүгө аракет кылганда, сизге мындай кылуу мүмкүн эмес экендиги жөнүндө кабар пайда болот.
Башка китеп менен байланышкан мындай массивдеги өзгөрүүлөрдү шилтемени бузуу менен гана жасоого болот.
Таблицалардын ортосундагы ажырым
Кээде стол диапазондорунун ортосундагы байланышты үзүшүңүз керек. Буга себеп, сиз башка китепке киргизилген массивди өзгөртүү керек болгондо, жогоруда сүрөттөлгөн жагдай же колдонуучунун бир таблицада маалыматтарды автоматтык түрдө башкасынан жаңыртып алгысы келбегендиги болушу мүмкүн.
1-ыкма: Китептердин ортосундагы байланышты үзүү
Дээрлик бир амалды аткарып, бардык клеткалардагы китептер менен байланышты үзүүгө болот. Ошол эле учурда, уячалардагы маалыматтар сакталат, бирок алар башка документтерге көз каранды болбогон статикалык жаңыланбаган маанилерге айланат.
- Китептен башка файлдардагы баалуулуктар тартылып, өтмөккө өтүңүз "Маалымат". Сөлөкөтүн чыкылдатыңыз "Байланышты өзгөртүү"курал-жарак кутучасынын лентасында жайгашкан "Соолуган". Эгерде учурдагы китепте башка файлдарга шилтемелер жок болсо, анда бул баскыч иштебейт.
- Шилтемелерди өзгөртүү терезеси иштей баштады. Байланышты үзгүсү келген файлды тиешелүү китептердин тизмесинен тандап алабыз (эгер бир нече болсо). Баскычты чыкылдатыңыз Шилтемени буз.
- Маалымат терезеси ачылат, анда андан аркы иш-аракеттердин кесепеттери жөнүндө эскертүү бар. Эгер сиз эмне кылаарыңызды билсеңиз, анда баскычты чыкылдатыңыз "Байланыш үзүлүүдө".
- Андан кийин, учурдагы документте көрсөтүлгөн файлга шилтемелер статикалык маанилер менен алмаштырылат.
2-ыкма: Маанилерди киргизиңиз
Жогорудагы ыкма эки китептин ортосундагы байланыштарды толугу менен бузушуңуз керек болгондо гана ылайыктуу. Бир эле файлдын ичиндеги байланышкан таблицаларды бөлүү керек болсо эмне кылыш керек? Муну сиз маалыматтарды көчүрүп, андан кийин баалуулуктар менен бир жерге чаптап койсоңуз болот. Айтмакчы, ушул сыяктуу эле, файлдардын жалпы байланышын бузбастан, ар кандай китептердин жеке маалыматтар диапазонундагы байланышты бузууга болот. Бул ыкманын иш жүзүндө кандайча иштээрин карап көрөлү.
- Башка жадыбалга шилтемени алып салгыңыз келген диапазонду тандаңыз. Биз аны чычкандын оң баскычы менен басабыз. Ачылган менюдан, тандаңыз "Көчүрүү". Бул аракеттердин ордуна сиз ысык баскычтардын альтернативдүү айкалышын тере аласыз Ctrl + C.
- Андан ары, ошол фрагменттен тандоону алып салбастан, аны кайрадан оң баскыч менен чыкылдатыңыз. Бул жолу иш-аракеттердин тизмесиндеги сөлөкөттү чыкылдатыңыз "Баалуулуктар"курал тобунда жайгашкан Жолдор салыңыз.
- Андан кийин, тандалган диапазондогу бардык шилтемелер статикалык маанилер менен алмаштырылат.
Көрүнүп тургандай, Excelде бир нече таблицаны бириктирүүнүн жолдору жана каражаттары бар. Ошол эле учурда, таблицалык маалыматтар башка барактарда, ал тургай ар кандай китептерде болушу мүмкүн. Зарыл болсо, бул байланыш оңой бузулушу мүмкүн.
