Excel бағдарламасында белгілі бір тапсырмаларды орындау кезінде кейде бір-бірімен байланысты бірнеше кестелермен күресу керек. Яғни, бір кестенің деректері басқаларға қатаң және барлық ілеспе кестелердегі мәндер өзгертілген кезде қайта есептеледі.
Байланысты кестелер көптеген ақпаратты өңдеу үшін қолдануға ыңғайлы. Барлық ақпаратты бір үстелге орналастырыңыз, бұдан басқа, егер ол біртекті болмаса, онша ыңғайлы емес. Мұндай заттармен жұмыс істеу және оларды іздеу қиын. Көрсетілген мәселе байланысты кестелерді жоюға арналған, оның арасындағы ақпарат таратылатын, бірақ сонымен бірге өзара байланысты. Байланысты кестелер тек бір парақта немесе бір кітап ішінде ғана емес, сонымен қатар жеке кітаптарда (файлдарда) орналасуы мүмкін. Тәжірибастағы соңғы екі нұсқа көбінесе қолданылады, өйткені бұл технологияның мақсаты тек деректердің жиналуынан кету керек, ал олардың бір параққа қойылуы түбегейлі шешпейді. Деректерді басқарудың осындай түрімен қалай құруға және қалай жұмыс істеу керектігін білейік.
Байланысты кестелер құру
Біріншіден, бұл сұраққа назар аударайық, қай жағынан түрлі кестелер арасында байланыс жасауға болады.1-әдіс: Тікелей байланыстыру кестелері формула
Деректерді байланыстырудың ең оңай әдісі - басқа кестелерге сілтемелер бар формулаларды қолдану. Ол тікелей байланыстыру деп аталады. Бұл әдіс интуитивті, өйткені ол бір кесте массивіндегі мәліметтерге сілтемелерді жасау сияқты.
Сіз қандай да бір мысалмен байланысуға болатынын көрейік. Бізде екі парақта екі үстел бар. Дәл осы кестеде жалақы формула бойынша, қызметкердің бір коэффициентіне көбейту арқылы есептеледі.
Екінші парақта үстел бар, олардың құрамында жалақысы бар қызметкерлер тізімі бар. Екі жағдайда да қызметкерлердің тізімі бір тапсырыспен ұсынылған.
Екінші парақтан бастап ставкаларда бұл мәліметтерді бірінші орынның тиісті ұяшықтарына қатайтыңыз.
- Бірінші парақта біз «ставка» бағанының бірінші ұяшығын бөлдік. Біз оған «=» белгісін қойдық. Әрі қарай, «2-парақ 2» жапсырмасын нұқыңыз, ол Excel интерфейсінің сол жақ бөлігінде, күй жолағының сол жағында орналасқан.
- Құжаттың екінші аймағында қозғалыс бар. «Ставка» бағанындағы бірінші ұяшықты нұқыңыз. Содан кейін «Тең» белгісі бұрын орнатылған ұяшыққа деректерді енгізу үшін пернетақтадағы Enter түймесін басыңыз.
- Содан кейін бірінші параққа автоматты көшу бар. Көріп отырғанымыздай, бірінші қызметкердің екінші кестенің мәні тиісті ұяшыққа тартылады. Ставкасы бар ұяшықта курсорды орнату арқылы біз әдеттегі формула экранда деректерді көрсету үшін пайдаланылатынын көреміз. Бірақ ұяшықтың координаттарының алдында, деректер шығатын жерден «List2!» Өрнегі бар, ол олар орналасқан құжат аймағының атауын көрсетеді. Біздің ісіміздегі жалпы формула келесідей:
= List2! B2
- Енді сіз кәсіпорынның барлық басқа қызметкерлерінің тарифтері туралы мәліметтерді беруіңіз керек. Әрине, бұл бірінші қызметкер үшін тапсырманы орындағанымыздай, бірақ қызметкерлер тізімдерінің екеуі де бірдей тәртіпте орналасқанын ескере отырып жасалуы мүмкін, бірақ ол бірдей тәртіпте орналасқанын және оның шешімімен жеделдетілуі мүмкін. Мұны формуланы төмендегі диапазонға көшіру арқылы жасауға болады. Excel-ге сілтемелер салыстырмалы болғандықтан, олардың мәндерін көшіру кезінде, бізге қажетті мәндер ауысады. Көшіру процедурасын өзі толтыру маркерін қолдана отырып жасауға болады.
Сонымен, біз меңзерды формула бар элементтің төменгі оң жақ аймағына қойдық. Осыдан кейін, курсор қара крест түрінде толтыру маркеріне айналуы керек. Біз тінтуірдің сол жақ батырмасының қысқышын орындаймыз және курсорды бағанның санына шығарамыз.
- 2-парақтағы ұқсас бағандағы барлық мәліметтер парақтағы кестеге тартылды. 1. Деректер 2-парақта өзгерген кезде, олар бірінші рет өзгереді.
2-әдіс: операторлар индексіне тоқылмышты пайдалану - іздеу
Бірақ егер кестелік массивтердегі қызметкерлер тізімі бірдей тәртіпте болмаса, не істеу керек? Бұл жағдайда, бұрынғыдай, бұрынғыдай, опциялардың бірі - қолмен байланысуы керек ұяшықтардың арасында қатынасты орнату. Бірақ кішкене кестелерден басқа қолайлы. Жаппай полигондар үшін бұл опция жүзеге асыруда көп уақытты қажет етеді, ал ең нашар жағдайда - іс жүзінде ол, әдетте, шындыққа жанаспайтын болады. Бірақ бұл мәселені операторлардың индексін қолдану арқылы шешуге болады - іздеу. Сөйлесуі алдыңғы әдісте болған кестелердегі мәліметтерді қалай жасау арқылы қалай жасауға болатынын көрейік.
- Біз «ставканың» бағанының бірінші элементін бөліп көрсетеміз. «Кірістіру функциясы» белгішесін басу арқылы функциялар шеберіне өтіңіз.
- «Сілтемелер мен массивтер» тобындағы функциялар шеберінде біз «Индекс» атауын табамыз және бөлеміз.
- Бұл оператордың екі нысаны бар: массивтермен және анықтамалармен жұмыс істеу формасы. Біздің жағдайда, бірінші нұсқа қажет, сондықтан келесі пішінді таңдау терезесінде оны таңдаңыз, оны таңдап, «OK» түймесін басыңыз.
- Оператордың дәлелдер индексі іске қосылады. Көрсетілген функцияның тапсырмасы көрсетілген санмен таңдалған диапазонда орналасқан мәннің шығуы болып табылады. Жалпы формула операторының индексі:
= Индекс (массив; нөмір_натасы; [number_stolbits])
«Массив» - бұл белгілі бір жолдың нөмірі бойынша ақпаратты шығаратын ауқымнан тұратын дәлел.
«Жол нөмірі» - бұл жолдың саны болып табылатын дәлел. Жол нөмірі бүкіл құжатқа қатысты емес, тек бөлінген массивпен салыстырғанда көрсетілуі керек екенін білу маңызды.
«Баған саны» - бұл міндетті емес дәлел. Біздің міндеттерімізді шешу үшін біз оны пайдаланбаймыз, сондықтан оны бөлек сипаттаудың қажеті жоқ.
Біз жүгіргіні «массив» өрісіне қойдық. Осыдан кейін, параққа барып, тінтуірдің сол жақ батырмасын басып тұрып, «жылдамдық» бағанының барлық мазмұнын таңдаңыз.
- Координаталар оператордың терезесінде көрсетілгеннен кейін жүгіргіні «Жол нөмірі» өрісіне қойдық. Біз бұл дәлелді іздеу операторының көмегімен аламыз. Сондықтан, функция жолының сол жағында орналасқан үшбұрышты басыңыз. Жаңадан пайдаланылған операторлардың тізімі ашылады. Егер сіз олардың арасында «Іздеу компаниясы» атауын тапсаңыз, оны нұқуға болады. Қарама-қарсы жағдайда, тізімнің соңғы нүктесін нұқыңыз - «Басқа функциялар ...».
- Стандартты терезе шебері терезесі басталады. Оған «Сілтемелер мен массивтер» тобында барыңыз. Бұл жолы тізімдегі «Іздеу компаниясы» тармағын таңдаңыз. «ОК» түймесін басыңыз.
- Іздеу операторының дәлелдерінің дәлелдерін қосу жүзеге асырылады. Көрсетілген функция белгілі бір массивтегі мән санын оның атымен шығаруға арналған. Бұл мүмкіндіктің арқасында біз функция функциясы үшін белгілі бір мәннің санының санын есептейміз. Іздеу тақтасының синтаксисі ұсынылған:
= Іздеу тақтасы (іздеу_NAME; қарау__)
«Қажетті» - ол орналасқан үшінші тараптың ұяшығының аты немесе мекен-жайы бар дәлел болып табылады. Бұл атаудың мақсатты ауқымда орналасуы және есептеу керек. Біздің жағдайда бірінші дәлелдің рөлі 1 парақтағы ұяшықтарға сілтеме жасалады, онда қызметкерлер орналасқан.
«Тірі массив» - бұл аргумент, ол көрсетілген мәнді анықтайды, ол көрсетілген мәнді іздеуді жүзеге асырады, ол оның позициясын анықтау үшін орындалады. Бізде бұл рөл бар, 2-парақтағы «атау» бағанының мекен-жайын орындау үшін бізде болады.
«Салыстыру түрі» - дәлелсіз дәлел, бірақ алдыңғы оператордан айырмашылығы, бұл қосымша дәлел қажет болады. Бұл операторға қалай сәйкестендіру керектігін білдіреді, бұл массивпен қажетті мән. Бұл дәлел үш мәннің біреуі болуы мүмкін: -1; 0; 1. Дұрыс бұзылған массивтер үшін «0» опциясын таңдаңыз. Бұл опция біздің ісіміз үшін жарамды.
Сонымен, аргумент терезесінің өрістерін толтыруға өтіңіз. Біз жүгіргіні «тұманды мән» өрісіне қойдық, парақтағы бірінші ұяшықты «ат» бағанын нұқыңыз.
- Координаталар пайда болғаннан кейін, меңзерді «Листингтің массиві» өрісіне орнатыңыз және «Парақ 2» белгісіне өтіңіз, ол «Парақ 2» жапсырмасына өтіңіз, ол excel 2 белгісіне өтіңіз, ол excel терезесінің төменгі жағында орналасқан, ол күй жолағының астыңғы жағында орналасқан. Тінтуірдің сол жақ батырмасын толқытып, курсорды «Атау» бағанының барлық ұяшықтарын бөлектеңіз.
- Олардың координаттары «Листингтің үлкені» өрісінде көрсетілгеннен кейін, «Салыстыру типі» өрісіне өтіп, пернетақтадан «0» нөмірін орнатыңыз. Осыдан кейін біз тағы да «массивке қарап» өрісіне оралдық. Біз алдыңғы әдіспен жасағандай формуланы көшіруді жүзеге асырып жатырмыз. Мекенжайлардың ауысуы болады, бірақ мына жерде бізде тұраққа қойылған массивтің координаттары қажет. Ол ауыспауы керек. Біз Координаттарды курсормен бөліп, F4 функциясын нұқыңыз. Көріп отырғаныңыздай, доллар белгісі координаттарға дейін пайда болды, бұл салыстырмалы түрде сілтеме абсолютті болып шықты. Содан кейін «ОК» түймесін басыңыз.
- Нәтиже «ставка» бағанының бірінші ұяшығында көрсетіледі. Бірақ көшіру алдында біз басқа аймақты түзетуіміз керек, атап айтқанда, бірінші аргумент функциясы индексі. Ол үшін формула бар баған элементін таңдаңыз және формула жолына өтіңіз. Оператор индексінің бірінші дәлелін (B2: B7) бөліп, F4 түймесін басыңыз. Көріп отырғаныңыздай, дионс белгісі таңдалған координаттардың жанында пайда болды. Enter пернесін басыңыз. Жалпы, формула келесі форманы алды:
= Индекс (парақ2! $ B $ 2: $ 7 $ 7; Іздеу тақтасы (парақ! A4! A4! $ A4! $ A $ 2: $ $ 2: $ 7; 0))
- Енді сіз толтыру маркерін пайдаланып көшіре аласыз. Біз оны бұрынғыдай сөйлейміз және кестелік ауқымның соңына қарай созамыз.
- Көріп отырғаныңыздай, екі байланысты кестелердегі жолдар тәртібі сәйкес келмесе де, барлық құндылықтар жұмысшылардың атауларына сәйкес күшейтіледі. Бұған операторлардың индексін іздеудің арқасында қол жеткізілді.
4-әдіс: арнайы қондырғы
Топтың кестесін Excel бағдарламасындағы массивтер арнайы кірістіруді қолдануға болады.
- Басқа кестені «қатайтқыңыз келетін мәндерді» таңдаңыз. Біздің жағдайда, бұл парақта «ставка» бағанының ауқымы. 2. Тінтуірдің оң жақ батырмасымен бөлінген фрагментті нұқыңыз. Ашылған тізімде «Көшіру» элементін таңдаңыз. Балама комбинация - бұл Ctrl + C пернелер тіркесімі. Осыдан кейін параққа көшеміз 1.
- Кітаптың аймағына жылжу, сізге қажет, олар мәндерді күшейту қажет ұяшықтарды бөліңіз. Біздің жағдайда бұл «өтінім» бағаны. Тінтуірдің оң жақ батырмасымен бөлінген фрагментті нұқыңыз. «Параметрлерді кірістіру» құралдар тақтасындағы Контекстік мәзірде «Байланыс енгізу» белгішесін нұқыңыз.
Сондай-ақ балама бар. Айтпақшы, ол Excel бағдарламасының ескі нұсқалары үшін жалғыз. Контекстік мәзірде біз жүгіргіні «арнайы кірістіру» элементіне жеткіземіз. Ашылған қосымша мәзірде бірдей атаумен позицияны таңдаңыз.
- Осыдан кейін арнайы енгізу терезесі ашылады. Ұяшықтың төменгі сол жақ бұрышындағы «Байланыс енгізу» түймесін басыңыз.
- Сіз қандай нұсқаны таңдасаңыз, бір кесте массивінен алынған мәндер басқасына салынады. Деректерді көзден өзгерту кезінде олар енгізілген ауқымда автоматты түрде өзгереді.
Сабақ: Excel бағдарламасындағы арнайы қондырғы
5-әдіс: Бірнеше кітаптардағы кестелер арасындағы байланыс
Сонымен қатар, сіз әр түрлі кітаптардағы кесте аймақтары арасында байланыс орната аласыз. Бұл арнайы қондырғышты қолданады. Әрекеттер алдыңғы әдіспен қарастырылғандарға да ұқсас, ал формулалар кезінде навигация бір кітаптың аудандары арасында, бірақ файлдар арасында болмауы керек. Әрине, барлық байланысты кітаптар ашылуы керек.
- Басқа кітапқа жіберілетін деректер ауқымын таңдаңыз. Тінтуірдің оң жақ батырмасын басып, ашылған мәзірдегі «Көшіру» орнын таңдаңыз.
- Содан кейін біз осы деректерді енгізу керек кітапқа ауысамыз. Қажетті ауқымды таңдаңыз. Тінтуірдің оң жақ батырмасын басыңыз. «Орнату параметрлері» тобындағы Контекстік мәзірде «Байланыс енгізу» элементін таңдаңыз.
- Осыдан кейін мәндер енгізіледі. Бастапқы кітаптағы деректерді өзгерту кезінде жұмыс кітабынан кесте массиві оларды автоматты түрде тартады. Екі кітаптың ашық болуын қамтамасыз ету үшін мүлдем қажет емес. Тек бір ғана жұмыс кітабын ашу жеткілікті, және егер оларда алдыңғы өзгерістер болса, ол автоматты түрде жабық құжаттан деректерді басқарады.
Бірақ бұл жағдайда, бұл жағдайда инфекция өзгеріссіз массив түрінде шығарылатындығын атап өткен жөн. Кірістірілген деректермен кез-келген ұяшықты өзгертуге тырысқанда, хабарлама бұған қол жеткізу мүмкіндігі туралы хабарланады.
Басқа кітаппен байланысты осындай массивтегі өзгерістер тек қосылымды бұза алады.
Кестелер арасындағы тақырып үзілістері
Кейде кестелер арасындағы байланысты бұзу қажет. Мұның себебі жоғарыда сипатталған болуы мүмкін, егер сіз басқа кітаптан салынған массивті өзгерткіңіз келсе және пайдаланушының сол кестедегі деректер бір кестемен автоматты түрде жаңартылғандай етіп жаңартылады.1-әдіс: кітаптар арасындағы байланыс үзілістері
Барлық жасушалардағы кітаптар арасындағы байланысты бұзу, іс жүзінде бір операцияны орындау арқылы. Бұл жағдайда ұяшықтардағы деректер қалады, бірақ олар басқа құжаттарға тәуелді емес жаңартылған мәндер болады.
- Басқа файлдардан алынған мәндер қатайтылған кітапта Деректер қойындысына өтіңіз. «Қосылым» құралдар тақтасындағы таспада орналасқан «Сілтемелерді өзгерту» белгішесін нұқыңыз. Айта кету керек, егер ағымдағы кітапта басқа файлдармен байланыс болмаса, онда бұл түйме белсенді емес.
- Сілтемені өзгерту терезесі іске қосылды. Байланыстырылған кітаптар тізімінен (егер олардың саны болса), байланыстырғыңыз келетін файлды таңдаңыз. «Қосылымды бұзу» түймесін басыңыз.
- Қосымша әрекеттердің салдары туралы ескерту беретін ақпарат терезесі ашылады. Егер сіз не істегіңіз келетініне сенімді болсаңыз, «Байланыс» түймесін басыңыз.
- Осыдан кейін, ағымдағы құжаттағы көрсетілген файлға барлық сілтемелер статикалық мәндермен ауыстырылады.
2-әдіс: мәндерді енгізу
Бірақ жоғарыда аталған әдіс екі кітап арасындағы барлық сілтемелерді толығымен бұзу қажет болған жағдайда ғана қолайлы. Егер сізге бір файлдағы ілеспе кестелерді ажырату қажет болса ше? Сіз мұны деректерді көшіру арқылы жасай аласыз, содан кейін мәндермен бірдей жерге кіре аласыз. Айтпақшы, бұл әдісті файлдар арасындағы жалпы байланысы жоқ түрлі кітаптардың жеке деректер диапазондары арасында бұзуға болады. Бұл әдіс іс жүзінде қалай жұмыс істейтінін көрейік.
- Біз басқа кестемен байланысын жойғымыз келетін ауқымын атап өтеміз. Тінтуірдің оң жақ батырмасын басыңыз. Ашық мәзірде «Көшіру» элементін таңдаңыз. Көрсетілген әрекеттердің орнына сіз Ctrl + C ыстық пернелерінің балама комбинациясын тере аласыз.
- Келесі, таңдауды сол фрагменттен шығармай, қайтадан тінтуірдің оң жақ батырмасымен басыңыз. Бұл жолы әрекет тізімінде, «Мән» белгішесін нұқыңыз, ол «Кірістіру» параметрлері тобында орналастырылған.
- Осыдан кейін арнайы ауқымдағы барлық сілтемелер статикалық мәндермен ауыстырылады.
Көріп отырғаныңыздай, Excel бағдарламасында бірнеше кестелерді өздері байланыстырудың тәсілдері мен құралдары бар. Сонымен бірге кестелік деректер басқа парақтарда және тіпті әр түрлі кітаптарда болуы мүмкін. Қажет болса, бұл қосылымды оңай сындыруға болады.