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