Excel-da tegishli jadvallar: batafsil ko'rsatmalar

Excel-da ba'zi vazifalarni bajarayotganda, ba'zan bir-biri bilan bog'liq bo'lgan bir nechta jadvallarni hal qilish kerak. Ya'ni, bitta jadvaldagi ma'lumotlar boshqalarga tortiladi va barcha tegishli jadvallardagi qadriyatlar o'zgartirilganda qayta hisoblanadi.

Tegishli jadvallar ko'p miqdordagi ma'lumotlarni hal qilish uchun juda qulaydir. Barcha ma'lumotlarni bitta jadvalda joylashtiring, bundan tashqari, agar u bir hil bo'lmagan bo'lsa, unchalik qulay emas. Bunday narsalar bilan ishlash va ularni qidirish qiyin. Belgilangan muammo faqat taqsimlangan, shu bilan birga tegishli jadvallarni yo'q qilish uchun mo'ljallangan, ammo bir vaqtning o'zida o'zaro bog'liq. Tegishli jadvallar nafaqat bitta varaq yoki bitta kitob ichida, balki alohida kitoblarda (fayllar) ichida bo'lishi mumkin. Amaliyotning oxirgi ikki varianti ko'pincha ishlatiladi, chunki ushbu texnologiyaning maqsadi faqat ma'lumot to'plashdan uzoqlashishi va bitta sahifada kaltaklanishi tubdan hal qilinmaydi. Keling, ma'lumotni boshqarishning bunday turini qanday yaratish va qanday ishlashni o'rganaylik.

Tegishli jadvallar yaratish

Birinchidan, keling, turli jadvallar orasidagi aloqani yaratish mumkin bo'lgan savolga e'tibor qarataylik.

1-usul: to'g'ridan-to'g'ri bog'lash jadvallari formulasi

Ma'lumotlarni bog'lashning eng oson usuli - bu boshqa jadvallarga havolalar mavjud bo'lgan formulalar foydalanish. U to'g'ridan-to'g'ri bog'lash deb ataladi. Ushbu usul sezgir, chunki u deyarli bir nechta massivlar qatoridagi ma'lumotlarga havolalar keltirib chiqaradi.

Keling, siz to'g'ridan-to'g'ri bog'lash orqali aloqa qanday o'rnak ko'rsatishda qanday o'rnak ko'rsatishi mumkinligini ko'rib chiqaylik. Ikkita varaqda ikkita stol bor. Xuddi shu jadvalda ish haqi xodim stavkalarini bitta koeffitsient uchun ko'paytirish orqali formuladan foydalangan holda hisoblanadi.

Ikkinchi varaqda ish haqi bilan ishlaydigan xodimlar ro'yxati mavjud. Ikkala holatda ham ishchilarning ro'yxati bir tartibda taqdim etiladi.

Ikkinchi varaqni ikkinchi varaqdan birinchi varaqni tikish bo'yicha birinchi o'rindiqlarga torting.

1. Birinchi varaqda biz "pul tikish" ustunining birinchi hujayrasini ajratamiz. Biz unga belgi qo'ydik "=". Keyin "2-varaq 2" yorlig'ini bosing, u yuqori qismida Excel interfeysining chap qismida joylashgan.



2. Hujjatning ikkinchi sohasida harakat bor. "Bet" ustunidagi birinchi katakchani bosing. Keyin klaviaturadagi ENTBET tugmasini bosing, unda "teng" belgisi oldindan o'rnatilgan hujayraga kirish uchun bosing.



3. Keyin birinchi varaqqa avtomatik o'tish mavjud. Ko'rinib turibdiki, birinchi xodimning ikkinchi stolning qiymati tegishli katakka tortiladi. Kursorni o'rnatgan kameraga o'rnatib, odatiy formula ekrandagi ma'lumotlarni namoyish qilish uchun foydalanilishini ko'ramiz. Ammo hujayraning koordinatalari oldida, ma'lumotlar chiqadigan joydan "Ro'yxat2!" Iborasi mavjud, bu ular joylashgan hujjat maydonining ismini ko'rsatadi. Bizning ishimizning umumiy formulasi quyidagicha ko'rinadi:

   = Ro'yxati2! B2



4. Endi siz korxonaning boshqa xodimlarining stavkalari haqida ma'lumot berishingiz kerak. Albatta, bu birinchi xodim uchun vazifani bajarganimiz mumkin, ammo ikkala xodim ro'yxatlari shu tartibda joylashganligini hisobga olib, vazifa uning qarori bilan sezilarli darajada soddalashtirilishi va tezlashishi mumkinligini hisobga olib. Buni faqat formulani quyidagi maydonchaga nusxalash orqali amalga oshirish mumkin. Excelga havolalar qarindoshi bo'lganligi sababli, ularning qadriyatlarini nusxalashda, qadriyatlarning o'zgarishi biz kerak. Nusxalash protsedurasi to'ldirish belgisi yordamida amalga oshirilishi mumkin.

   Shunday qilib, biz kursorni formula bilan elementning pastki o'ng qismiga qo'yamiz. Shundan so'ng, kursor to'ldirish belgisini qora xoch shaklida aylantirishi kerak. Biz sichqonchaning chap tugmachasini siqib chiqaramiz va kursorni ustunning raqamiga tortamiz.



5. 2 varaqdagi barcha ma'lumotlar 2 varaqdagi stolga stolga tortildi. Ma'lumotlar 2 varaqda ma'lumotlar avtomatik ravishda birinchisiga avtomatik ravishda o'zgaradi.

2-usul: Operatorlarning indeksini buzish - Qidiruv

Ammo agar stol qatoridagi xodimlar ro'yxati bir xil tartibda joylashgan bo'lsa, nima qilish kerak? Bunday holda, avvalroq aytilganidek, variantlardan biri bu hujayralarning har bir hujayralarining har bir hujayralarining har biridagi munosabatlarni qo'lda o'tkazishdir. Ammo kichik stollardan tashqari mos keladi. Katta diapazonlar uchun ushbu parametr eng yaxshi vaqtni bajaradi va eng yomoni - amalda u umuman haqiqat emas. Ammo bu muammoni bir guruh operatorlar indeksi - qidiruvdan foydalanib hal qilish mumkin. Keling, buni suhbat oldingi usulda bo'lgan jadvallardagi ma'lumotlarni tugatishdan qanday amalga oshirish mumkinligini ko'rib chiqaylik.

1. "Bet" ustunining birinchi elementini ajratib turamiz. "Qo'shish funktsiyasini" tugmachasini bosish orqali funktsiyalarga o'ting.



2. "Havolalar va massivlar" guruhidagi funktsiyalar ustida "Index" ismini topamiz va ajratamiz.



3. Ushbu operator ikkita shaklga ega: massivlar va ma'lumotnoma bilan ishlash uchun shakl. Bizning holatlarimizda birinchi variant talab qilinadi, shuning uchun keyingi shaklni tanlash oynasida uni oching va "OK" tugmasini bosing.



4. Operatorning dalillari indeksi ishlay boshlaydi. Belgilangan funktsiyaning vazifasi belgilangan raqamga ega bo'lgan tanlangan diapazonda joylashgan qiymatni ishlab chiqarishdir. Umumiy formula operatori indeksi:

   = Indeks (massiv); raqam_name; [raqam_stolits])

   "Massi" - bu yo'naltirilgan qatorlar soni bo'yicha ma'lumotni ajratib oladigan diapazonni o'z ichiga olgan dalil.

   "Qator raqami" bu ushbu chiziqning soni bo'lgan dalildir. Chiziq raqami to'liq hujjatga nisbatan emas, balki faqat ajratilgan qatorga nisbatan ko'rsatilishi kerakligini bilish juda muhimdir.

   "Ustunning soni" - bu majburiy emas. Xususan bizning vazifamizni hal qilish uchun biz undan foydalanmaymiz va shuning uchun uni alohida tasvirlab berish shart emas.

   Biz kursorni "massiv" maydoniga qo'ydik. Shundan so'ng, 2 varaqqa o'ting va Sichqonchaning chap tugmachasini bosib, "Rate" ustunining to'liq tarkibini tanlang.



5. Koordinatalar Operatorning derazasida ko'rsatiladi, biz kursorni "qator raqam" maydoniga qo'yamiz. Biz ushbu tortishuvni qidiruvchidan olib chiqamiz. Shuning uchun funktsiya satrining chap tomonida joylashgan uchburchakni bosing. Yangi ishlatiladigan operatorlar ro'yxati ochiladi. Agar siz ular orasida "Izlash kompaniyasida" nomini topsangiz, uni bosishingiz mumkin. Qarama-qarshi holatda, ro'yxatning so'nggi nuqtasini bosing - "Boshqa funktsiyalar ...".



6. Standart oyna ustasi deraza boshlanadi. Unga bitta guruh "havolalar va massivlar" da boring. Bu safar ro'yxatdagi "Izlash kompaniyasida" mahsulotini tanlang. "OK" tugmachasini bosish.



7. Qidiruv operatori dalillarining dalillarini faollashtirish amalga oshiriladi. Belgilangan funktsiya qiymat raqamini o'z nomi bilan ma'lum bir qatordan chiqarish uchun mo'ljallangan. Ushbu xususiyat tufayli funktsiya funktsiyasi uchun ma'lum bir qiymat satrini hisoblaymiz. Qidiruv kengashi sintaksisi quyidagicha keltirilgan:

   = Qidiruv taxtasi (qidirish_name; ko'rishni ko'rish); [tipte_station])

   "Kerakli" - bu uchinchi tomon diapazoni nomi yoki manzili bo'lgan dalil. Bu ushbu nomning maqsadli doirada joylashgan va hisoblash kerak. Bizning holatlarimizda, birinchi dalilning roli xodimlarning 1 varaqidagi hujayralarga murojaat qiladi, unda xodimlar joylashgan.

   "Ro'yhat massivi" - bu archaga murojaat qiladigan argument, bu uning pozitsiyasini aniqlash uchun belgilangan qiymatni qidirishni amalga oshiradi. Bizda 2 varaqdagi "ism" ustunini bajarish uchun bizda bu rol bo'ladi.

   "Taqqoslash turi" - bu majburiy emas, balki oldingi operatordan farqli o'laroq, ushbu ixtiyoriy argument kerak bo'ladi. Bu operatorga qanday moslashish kerakligini ko'rsatadi, bu esa massiv bilan kerakli qiymat. Ushbu argument uchta qiymatdan biriga ega bo'lishi mumkin: -1; 0; 1. Cheklangan massivlar uchun "0" variantini tanlang. Ushbu parametr bizning ishimiz uchun mos keladi.

   Shunday qilib, argument oynasini to'ldirish uchun davom eting. Biz kursorni "Foguman qiymati" da joylashtirdik, 1 varaqdagi birinchi katakchasini bosing.



8. Koordinatalar aks ettirilgandan so'ng, "Ro'yxatli" maydoniga kursorni o'rnating va "2-jadval" yorlig'iga o'ting, u status panelidagi yuqori qismida joylashgan. Sichqonchaning chap tugmachasini mahkamlash va "Ism" ustunining barcha hujayralarini ajrating.



9. Koordinatalardan so'ng "Ro'yxatga ega" maydonda namoyish etilgandan so'ng, "xaritalash turi" maydoniga o'ting va klaviaturadan "0" raqamini o'rnating. Shundan so'ng, biz yana "massiv orqali qarab" dalasiga qaytamiz. Gap shundaki, biz oldingi usulda qilgani kabi formulani nusxalashimiz mumkin. Manzillarning o'zgarishi bo'ladi, ammo bu erda biz massivning kuzatilgan koordinatalari biz xavfsizligimiz kerak. U siljishi kerak emas. Biz koordinatalarni kursor bilan ajratamiz va F4 funktsiyasini bosing. Ko'rinib turibdiki, dollar belgisi koordinatalardan oldin paydo bo'ldi, bu qarindoshlikning mutlaqiga aylanadi. Keyin "OK" tugmasini bosing.



10. Natijada "Bet" ustunining birinchi kamerasida ko'rsatiladi. Ammo nusxa ko'chirishdan oldin biz boshqa maydonni tuzatishimiz kerak, ya'ni birinchi argument funktsiyasi indeksidir. Buning uchun formulani o'z ichiga olgan ustun elementini tanlang va formula satriga o'ting. Operator indeksining birinchi dalilini (B7: B7) ajrating va F4 tugmasini bosing. Ko'rinib turibdiki, tanlangan koordinatalar yaqinida dollar belgisi paydo bo'ldi. Enter tugmachasini bosing. Umuman olganda, formulani quyidagi shaklda amalga oshirdi:

    = Indeksi (varaqasi 2! $ 2: $ 7; Qidiruv kengashi (varaq1! A4! $ 2! $ 2; $ 7; 0))



11. Endi siz to'ldiruvchi marker yordamida nusxalashingiz mumkin. Biz buni ilgari aytganimizdek deb ataymiz va jadvalning oxirigacha cho'zamiz.



12. Ko'rinib turibdiki, ikkita tegishli stolda satrlarning tartibi to'g'ri kelmaydi, shunga qaramay, ishchilarning ismlariga muvofiq barcha qadriyatlar kuchaytiriladi. Bu operatorlar indeksini qidirish kombinatsiyasidan foydalanganligi sababli erishildi.

4-usul: maxsus kiritish

Excel-dagi jadvalli massivlar maxsus kiritish mumkin.

1. Siz boshqa jadvalga "torting" kerak bo'lgan qadriyatlarni tanlang. Bizning holatlarimizda, bu "pul tikish" ustuni 2. sichqonchaning o'ng tugmachasiga ega bo'lgan maxsus parchani bosing. Open ro'yxatida "Nusxalash" elementini tanlang. Muqobil kombinatsiya CTRL + C tugmalari kombinatsiyasidir. Shundan so'ng, biz 1 varaqqa o'tamiz.



2. Sizga kerak bo'lgan kitob doirasiga o'tish, qiymatlarni kuchaytirish kerak bo'lgan hujayralarni ajratib turing. Bizning holatda, bu "taklif" ustunidir. Sichqonchaning o'ng tugmachasiga ega bo'lgan maxsus parchani bosing. Kontekst menyusida asboblar panelidagi "Sabablar panelidagi" Aloqa "belgisini bosing.

   

   Shuningdek, alternativa ham mavjud. Aytgancha, u Excelning eski versiyalari uchun yagona narsadir. Kontekst menyusida biz kursorni "maxsus kiritish" bandligiga olib keladi. Ochilgan qo'shimcha menyuda xuddi shu nom bilan pozitsiyani tanlang.



3. Shundan so'ng, maxsus kiritish oynasi ochildi. Hujayraning pastki chap burchagidagi "aloqa" tugmachasini bosing.



4. Siz tanlagan har qanday variant, bitta jadval massividan qiymatlar boshqasiga joylashtiriladi. Ma'lumotni manbada o'zgartirganda, ular ham o'rnatilgan turdagi avtomatik ravishda o'zgaradi.

Dars: Excelda maxsus kiritish

5-usul: Bir nechta kitoblardagi jadvallar orasidagi aloqa

Bundan tashqari, siz turli kitoblardagi jadval sohalari o'rtasidagi bog'liqlikni tashkil qilishingiz mumkin. Bu maxsus kiritish vositasidan foydalanadi. Amallar avvalgi usulda ko'rib chiqqanlarga mutlaqo o'xshash bo'ladi, faqat bitta kitobning joylari orasida navigatsiyasiz, lekin fayllar orasida. Tabiiyki, barcha tegishli kitoblarni ochish kerak.

1. Boshqa kitobga o'tkaziladigan ma'lumotlarni tanlang. Sichqonchaning o'ng tugmachasini bosing va ochilgan menyuda "Nusxalash" holatini tanlang.



2. Keyin biz ushbu ma'lumotlar kiritilishi kerak bo'lgan kitobga o'tamiz. Siz xohlagan oralig'ini tanlang. Sichqonchaning o'ng tugmachasini bosing. Kontekst menyusida "Sozlamalar" guruhida "Aloqa kiritish" ni tanlang.



3. Shundan so'ng, qiymatlar kiritiladi. Manba kitobida ma'lumotlarni o'zgartirganda, ishchi daftardan bir martalik masofani avtomatik ravishda tortadi. Ikkala kitob ham ochiq bo'lishini ta'minlash uchun juda muhim emas. Faqat bitta ish daftarini ochish uchun etarli, agar u avvalgi o'zgarishlar bo'lsa, yopiq bog'liq hujjatdan avtomatik ravishda yopiladi.

Ammo shuni ta'kidlash kerakki, bu holda, qimirlash o'zgarmagan massiv shaklida ishlab chiqariladi. Qo'shilgan ma'lumotlar bilan biron bir hujayrani almashtirishga harakat qilayotganda, buni amalga oshira olmaslik haqida xabar beriladi.

Boshqa kitob bilan bog'liq bo'lgan bunday qatordagi o'zgarishlar faqat ulanishni buzishi mumkin.

Stollar orasidagi sarlavha tanaffuslari

Ba'zan jadvallar orasidagi ulanishni buzish talab etiladi. Buning sababi yuqorida aytilganlar boshqa kitobdan kiritilgan va shunchaki foydalanuvchining istisno qilinishini istaganingizda bo'lishi mumkin, shunda foydalanuvchi boshqalardan tashqari ma'lumotlar avtomatik ravishda yangilanadi.

1-usul: kitoblar o'rtasida aloqa tanaffuslari

Barcha hujayralarda kitoblar o'rtasidagi bog'liqlikni, aslida bitta operatsiyani amalga oshirish orqali buzish. Bunday holda, hujayralardagi ma'lumotlar qoladi, ammo ular boshqa hujjatlarga bog'liq bo'lmagan yangilangan qiymatlar emas.

1. Boshqa fayllarning tobora kuchayib boradigan kitobda, ma'lumotlar yorlig'iga o'ting. "Ulanish" asboblar panelida lentada joylashgan "Fayllarni o'zgartirish" belgisini bosing. Shuni ta'kidlash kerakki, agar joriy kitob boshqa fayllar bilan ulanish bo'lmasa, ushbu tugmani faollashtirasiz.



2. Havola o'zgarishi oynasi ishga tushirildi. Tegishli kitoblar ro'yxatidan (agar ular bir nechta bo'lsa) biz ulanishni istagan faylni tanlang. "Ulanishni buzish" tugmachasini bosing.



3. Axborot oynasi - keyingi harakatlar oqibatlari haqida ogohlantiradigan ma'lumotlar ochilgan. Agar siz qilmoqchi ekanligingizga ishonchingiz komil bo'lsa, "Bilming" tugmasini bosing.



4. Shundan so'ng, amaldagi hujjatdagi ko'rsatilgan faylga barcha murojaatlar statik qadriyatlar bilan almashtiriladi.

2-usul: Qo'shimcha qiymatlar

Ammo yuqorida ko'rsatilgan usul faqat ikkita kitobning barcha havolalarini to'liq buzishingiz kerak bo'lsa. Agar siz bitta fayl ichidagi tegishli jadvallarni uzishingiz kerak bo'lsa-chi? Buni ma'lumotlarni nusxalash orqali qilishingiz mumkin, so'ngra bir xil joyni qiymat sifatida kiritishingiz mumkin. Aytgancha, ushbu usulda turli xil kitoblarning turli xil kitoblar oralig'ida fayllar o'rtasidagi umumiy munosabatlarni buzmasdan yoritilishi mumkin. Keling, bu usul amalda qanday ishlashini ko'rib chiqaylik.

1. Biz boshqa jadval bilan aloqani o'chirishni istagan diapazonni ta'kidlaymiz. Sichqonchaning o'ng tugmachasini bosing. Ochiq menyuda "Nusxalash" elementini tanlang. Belgilangan harakatlar o'rniga, siz CTRL + C so'zli tugmachalarning muqobil kombinatsiyasini terishingiz mumkin.



2. Keyinchalik bir xil bo'lakdan tanlovni olib tashlamasdan, uni to'g'ri sichqoncha tugmachasi bilan bosing. Bu safar aktsiyalar ro'yxatida, kiritilgan "Qiymat" belgisini bosing, u joylashtirilgan parametrlar guruhida joylashtirilgan.



3. Shundan so'ng, bag'ishlangan oralig'idagi barcha ma'lumotlar statik qadriyatlar bilan almashtiriladi.

Ko'rinib turibdiki, Excel bir nechta jadvallarni bir nechta jadvallarni bog'lash usullari va vositalari mavjud. Shu bilan birga, jadval ma'lumotlari boshqa varaqlarda va hatto turli xil kitoblarda bo'lishi mumkin. Agar kerak bo'lsa, bu ulanish osonlikcha buzilishi mumkin.