Excel-da muayyan vazifalarni bajarayotganda, ba'zida bir-biriga bog'liq bo'lgan bir nechta jadvallar bilan ishlashga to'g'ri keladi. Ya'ni, bitta jadvaldagi ma'lumotlar boshqasiga tortiladi va ular o'zgartirilganda, qiymatlar jadvalning barcha tegishli diapazonida qayta hisoblab chiqiladi.
Bog'langan jadvallar katta hajmdagi ma'lumotlarni qayta ishlash uchun foydalanish uchun juda qulaydir. Barcha ma'lumotlarni bitta jadvalga joylashtirish, bundan tashqari, agar u bir hil bo'lmasa, bu juda qulay emas. Bunday ob'ektlar bilan ishlash va ularni qidirish juda qiyin. Ko'rsatilgan muammo o'zaro bog'liq jadvallar yordamida bartaraf etilishi uchun mo'ljallangan bo'lib, ular orasida ma'lumotlar tarqatiladi, lekin ayni paytda o'zaro bog'liqdir. Bog'langan jadvallar diapazoni nafaqat bitta varaq yoki bitta kitob ichida, balki alohida kitoblarda (fayllarda) ham joylashtirilishi mumkin. So'nggi ikkita variant ko'pincha amalda qo'llaniladi, chunki ushbu texnologiyaning maqsadi ma'lumotlarni to'plashdan qochishdir va ularni bitta sahifaga joylashtirish muammoni tubdan hal qilmaydi. Keling, ushbu turdagi ma'lumotlarni boshqarishni qanday yaratishni va qanday ishlashni o'rganamiz.
Bog'langan jadvallarni yaratish
Birinchidan, keling, stol usti stollari orasidagi munosabatlarni qanday usullar bilan yaratishga imkon bor degan savolga to'xtalib o'tamiz.
1-usul: jadvallarni formulalar bilan to'g'ridan-to'g'ri bog'lash
Ma'lumotni bog'lashning eng oson usuli - bu boshqa jadval oralig'iga murojaat qiladigan formulalardan foydalanish. To'g'ridan-to'g'ri bog'lanish deyiladi. Ushbu usul intuitivdir, chunki u bilan bog'lanish bitta jadval massivida ma'lumotlarga havolalar yaratish bilan deyarli bir xil tarzda amalga oshiriladi.
Keling, qanday qilib bog'lanishni to'g'ridan-to'g'ri bog'lash orqali misol qilib bog'lanishni ko'rib chiqamiz. Ikkita varaqda ikkita stol mavjud. Bir stolda ish haqi, ish haqi miqdorini hamma uchun bitta koeffitsientga ko'paytirish orqali formula yordamida hisoblanadi.
Ikkinchi varaqda jadvallar ro'yxati keltirilgan bo'lib, unda ish haqi ko'rsatilgan xodimlarning ro'yxati keltirilgan. Ikkala holatda ham xodimlarning ro'yxati bir xil tartibda taqdim etiladi.
Ikkinchi varaqdagi stavkalar to'g'risidagi ma'lumotlar birinchisining tegishli kataklariga tushirilganligiga ishonch hosil qilish kerak.
- Birinchi varaqdagi ustundagi birinchi uyani tanlang Taklif. Biz unga belgi qo'ydik "=". Keyingi, yorliqni bosing "2-varaq", holatlar panelining tepasida Excel interfeysining chap tomonida joylashgan.
- Hujjatning ikkinchi sohasiga o'tadi. Biz ustundagi birinchi katakchani bosamiz Taklif. Keyin tugmachani bosing Kirish oldindan belgilab qo'yilgan katakka ma'lumotlarni kiritish uchun klaviaturada teng.
- Keyin birinchi varaqqa avtomatik o'tish mavjud. Ko'rib turganingizdek, ikkinchi jadvaldagi birinchi xodimning stavkasi tegishli katakka tushiriladi. Kursorni stavkani o'z ichiga olgan katakka joylashtirib, odatdagi formuladan ma'lumotlarni ekranda aks ettirish uchun foydalanilganligini ko'ramiz. Ammo ma'lumotlar chiqadigan uyaning koordinatalari oldida bir ibora bor "2-varaq!", ular joylashgan hujjat sohasi nomini bildiradi. Bizning holatimizda umumiy formula quyidagicha ko'rinadi.
= 2-varaq! B2 - Endi siz korxonaning boshqa barcha xodimlarining stavkalari to'g'risida ma'lumotlarni uzatishingiz kerak. Albatta, bu birinchi ishchi uchun vazifani bajara olganimizdek amalga oshirilishi mumkin, ammo xodimlarning ikkala ro'yxati ham bir xil tartibda joylashtirilganligini hisobga olsak, vazifa sezilarli darajada soddalashtirilishi va uning echimini tezlashtirishi mumkin. Buni oddiy formulani quyidagi diapazonga nusxalash orqali amalga oshirish mumkin. Excel-dagi havolalar sukut bo'yicha nisbiy bo'lganligi sababli, ular ko'chirilganda, qiymatlar siljiydi, bu bizga kerak. Nusxalash protsedurasining o'zi plomba belgisi yordamida amalga oshirilishi mumkin.
Shunday qilib, kursorni formuladan foydalanib elementning pastki o'ng qismiga qo'ying. Shundan so'ng, kursor qora xoch shaklida to'ldirish markeriga aylantirilishi kerak. Sichqonchaning chap tugmachasini bosib, kursorni ustunning eng pastki qismiga torting.
- Shu kabi ustundagi barcha ma'lumotlar 2-varaq stolga tortishdi 1-varaq. Ma'lumotni quyidagiga o'zgartirganda 2-varaq birinchisida ular avtomatik ravishda o'zgaradi.
2-usul: INDEX operatorlaridan foydalanish - SEARCH
Ammo jadval massivlaridagi xodimlarning ro'yxati bir xil tartibda bo'lmasa-chi? Bu holda, yuqorida aytib o'tilganidek, variantlardan biri qo'lda ulanishi kerak bo'lgan hujayralarning har biri o'rtasida aloqa o'rnatishdir. Ammo bu faqat kichik jadvallar uchun javob beradi. Katta poligonlar uchun bunday variantni amalga oshirish uchun ko'p vaqt talab etiladi va eng yomoni, amalda bunday bo'lmaydi. Ammo bu muammoni ko'plab operatorlar yordamida hal qilish mumkin INDEX - Qidiruv. Oldingi usulda muhokama qilingan jadval oralig'idagi ma'lumotlarni bog'lash orqali buni qanday amalga oshirish mumkinligini ko'rib chiqaylik.
- Birinchi ustun elementini tanlang Taklif. Ga boring Xususiyat ustasibelgini bosish orqali "Qo'shish funktsiyasi".
- Ichida Funktsiya ustasi guruhda Adabiyotlar va qatorlar ismini tanlang va ta'kidlang INDEX.
- Ushbu operator ikkita shaklga ega: massivlar bilan ishlash shakli va ma'lumotnoma. Bizning holatda, birinchi variant kerak, shuning uchun ochilgan shaklni tanlash uchun keyingi oynada uni tanlang va tugmani bosing "OK".
- Operator argumentlari oynasi ishga tushirildi INDEX. Ushbu funktsiyaning vazifasi tanlangan diapazonda ko'rsatilgan raqam bilan qatorda qiymatni chiqarishdir. Umumiy operator formulasi INDEX shunday:
= INDEX (qator; qator_nomasi; [ustun_ raqami])Array - biz ko'rsatilgan satr raqami bo'yicha ma'lumot oladigan diapazonning manzilini o'z ichiga olgan argument.
Chiziq raqami - argument, bu shu qatorning raqami. Chiziq raqami butun hujjatga nisbatan belgilanmasligi kerakligini bilish kerak, faqat tanlangan qatorga nisbatan.
Ustun raqami - ixtiyoriy bo'lgan argument. Biz uni aniq muammoni hal qilish uchun ishlatmaymiz va shuning uchun uning mohiyatini alohida tavsiflashning hojati yo'q.
Kursorni maydonga qo'ying Array. Shundan so'ng, o'ting 2-varaq sichqonchaning chap tugmachasini bosib ushlab turib, ustunning barcha tarkibini tanlang Taklif.
- Koordinatalar operator oynasida ko'rsatilgandan so'ng, kursorni maydonga qo'ying Chiziq raqami. Ushbu argumentni operator yordamida chiqaramiz Qidiruv. Shuning uchun, funktsiya chizig'ining chap tomonida joylashgan uchburchakni bosamiz. Yaqinda ishlatilgan operatorlar ro'yxati ochiladi. Agar ular orasida nom topsangiz "Qidiruv"keyin siz uni bosishingiz mumkin. Aks holda, ro'yxatdagi oxirgi elementni bosing - "Boshqa xususiyatlar ...".
- Standart oyna boshlanadi Funktsiya sehrgarlari. Biz o'sha guruhga o'tamiz Adabiyotlar va qatorlar. Bu safar ro'yxatdagi narsani tanlang "Qidiruv". Tugmani bosing. "OK".
- Operator argumentlari oynasi faollashtirilgan Qidiruv. Belgilangan funktsiya ma'lum bir massivda qiymat nomini uning nomiga ko'ra namoyish etish uchun mo'ljallangan. Ushbu xususiyat tufayli funktsiyaning ma'lum bir qiymatining satr raqamini hisoblaymiz INDEX. Sintaksis Qidiruv quyidagicha ifodalanadi:
= Qidiruv (qidiruv_qiymati; qidirish_razeri; [match_type])"Qidiruv qiymati" - u joylashgan uchinchi tomon joylashgan uyaning nomi yoki manzilini o'z ichiga olgan argument. Hisoblash kerak bo'lgan maqsad oralig'ida ushbu nomning pozitsiyasi. Bizning holatda, birinchi dalil, bu uyalarga havolalar bo'ladi 1-varaqxodimlarning ismlari qaerda.
Ko'rilgan Array - o'z o'rnini aniqlash uchun ko'rsatilgan qiymat qidirilayotgan massivga havolani bildiruvchi argument. Ustun manzili "bu erda bu rol o'ynaydi."Ism yoqilgan 2-varaq.
Moslik turi - dalil, bu ixtiyoriy, ammo oldingi bayonotdan farqli o'laroq, bizga bu qo'shimcha dalil kerak bo'ladi. Bu operator qidiruv qiymatini massiv bilan qanday moslashishini ko'rsatadi. Ushbu argument uchta qiymatdan biriga ega bo'lishi mumkin: -1; 0; 1. Tartiblanmagan massivlar uchun tanlang "0". Ushbu parametr bizning holatlarimizga mos keladi.
Shunday qilib, keling argumentlar oynasining maydonlarini to'ldirishni boshlaymiz. Kursorni maydonga qo'ying "Qidiruv qiymati"ustunning birinchi katagiga bosing "Ism" yoqilgan 1-varaq.
- Koordinatalar ko'rsatilgandan so'ng, maydonga kursorni qo'ying Ko'rilgan Array va yorliqni bosing "2-varaq", holatlar paneli ustidagi Excel oynasining pastki qismida joylashgan. Sichqonchaning chap tugmachasini bosib ushlab turing va kursor bilan barcha ustunlarni tanlang "Ism".
- Ularning koordinatalari maydonda ko'rsatilgandan so'ng Ko'rilgan Arraymaydonga boring Moslik turi va klaviaturadan raqamni o'rnating "0". Shundan so'ng biz yana maydonga qaytamiz Ko'rilgan Array. Gap shundaki, biz oldingi usulda bo'lgani kabi formuladan nusxa ko'chiramiz. Manzilning siljishi ro'y beradi, lekin bu erda biz ko'rilayotgan qatorning koordinatalarini tuzatishimiz kerak. Uni boshqa joyga ko'chirmaslik kerak. Kursor bilan koordinatalarni tanlang va funktsiya tugmachasini bosing F4. Ko'rib turganingizdek, dollar belgisi koordinatalar oldida paydo bo'ldi, bu esa bog'lanish mutlaqdan mutlaqga aylanganligini anglatadi. Keyin tugmachani bosing "OK".
- Natijada ustunning birinchi katakchasida ko'rsatiladi. Taklif. Ammo nusxa ko'chirishdan oldin biz boshqa maydonni, ya'ni funktsiyaning birinchi dalilini tuzatishimiz kerak INDEX. Buning uchun formulani o'z ichiga olgan ustun elementini tanlang va formulalar qatoriga o'ting. Biz operatorning birinchi dalilini tanlaymiz INDEX (B2: B7) tugmachasini bosing F4. Ko'rib turganingizdek, dollar belgisi tanlangan koordinatalar yaqinida paydo bo'ldi. Tugmani bosing Kirish. Umuman olganda, formula quyidagi shaklni oldi:
= INDEX (2-varaq! $ B $ 2: $ B $ 7; Qidiruv (1-varaq! A4; varaq2! $ A $ 2: $ A $ 7; 0)) - Endi siz to'ldirish markeridan foydalanib nusxa olishingiz mumkin. Biz buni ilgari aytib o'tganimizdek chaqiramiz va stol oralig'ining oxirigacha cho'zamiz.
- Ko'rinib turibdiki, ikkita bog'liq jadvalning qator tartiblari bir-biriga to'g'ri kelmasa ham, barcha qiymatlar xodimlarning ism-shariflariga ko'ra chiqariladi. Bunga operatorlar kombinatsiyasidan foydalanish orqali erishildi INDEX-Qidiruv.
Shuningdek o'qing:
Excelda EXEX funktsiyasi
Excelda EXCEL funktsiyasi
3 usul: tegishli ma'lumotlar bilan matematik operatsiyalarni bajarish
To'g'ridan-to'g'ri ma'lumotni bog'lash ham yaxshi, chunki u sizga jadvallarning birida boshqa jadval diapazonida aks ettirilgan qiymatlarni namoyish etish bilan bir qatorda ular bilan har xil matematik operatsiyalarni (qo'shish, bo'lish, ajratish, ko'paytirish va boshqalar) ham bajarishga imkon beradi.
Keling, bu amalda qanday amalga oshirilishini ko'rib chiqaylik. Keling, buni amalga oshiramiz 3-varaq kompaniyaning ish haqi to'g'risidagi umumiy ma'lumotlar xodimlar tomonidan taqsimlanmasdan ko'rsatiladi. Buning uchun ishchilar stavkalari olinadi 2-varaq, umumlashtirildi (funktsiya yordamida) SUM) va formuladan foydalangan holda koeffitsientga ko'paying.
- Ish haqi hisoblash natijasi ko'rsatiladigan uyani tanlang. 3-varaq. Tugmani bosing. "Qo'shish funktsiyasi".
- Oynani boshlash kerak Funktsiya sehrgarlari. Guruhga o'ting "Matematik" va u erda ismni tanlang SUM. Keyin, tugmachani bosing "OK".
- Funktsiya argumentlari oynaga o'tkaziladi SUM, tanlangan raqamlarning yig'indisini hisoblash uchun mo'ljallangan. U quyidagi sintaksisga ega:
= SUM (1 raqam; 2 raqam; ...)Oynadagi maydonlar belgilangan funktsiyaning dalillariga mos keladi. Ularning soni 255 ga etishi mumkin bo'lsa-da, bizning maqsadimizga faqat bittasi kifoya qiladi. Kursorni maydonga qo'ying "1 raqam". Yorliqni bosing "2-varaq" holat panelidan yuqori.
- Kitobning kerakli bo'limiga o'tgandan so'ng, xulosa qilinishi kerak bo'lgan ustunni tanlang. Biz sichqonchaning chap tugmachasini bosib ushlab turgan holda buni kursor bilan bajaramiz. Ko'rib turganingizdek, tanlangan maydonning koordinatalari darhol argumentlar oynasida ko'rsatiladi. Keyin tugmachani bosing "OK".
- Shundan so'ng biz avtomatik ravishda ko'chib o'tamiz 1-varaq. Ko'rinib turibdiki, xodimlarning takliflarining umumiy miqdori allaqachon tegishli elementda ko'rsatiladi.
- Ammo bu hammasi emas. Yodimizda bor, ish haqi stavkaning qiymatini omilga ko'paytirish orqali hisoblanadi. Shunday qilib, biz yana birlashtirilgan qiymat joylashgan katakni tanlaymiz. Shundan so'ng biz formulalar qatoriga o'tamiz. Formulaga ko'paytirish belgisini qo'shing (*) ni tanlang va koeffitsient ko'rsatkichi joylashgan elementni bosing. Hisoblashni amalga oshirish uchun tugmani bosing Kirish klaviaturada. Ko'rib turganingizdek, dastur korxona uchun ish haqining umumiy miqdorini hisoblab chiqdi.
- Orqaga 2-varaq va har qanday xodimning ish haqini o'zgartirish.
- Shundan so'ng biz yana umumiy miqdor bilan sahifaga o'tamiz. Ko'rib turganingizdek, bog'langan jadvaldagi o'zgarishlar tufayli umumiy ish haqining natijasi avtomatik ravishda qayta hisoblab chiqilgan.
4-usul: odatiy qo'shish
Maxsus qo'shimchadan foydalanib, Excel-da jadval massivlarini bog'lashingiz mumkin.
- Boshqa jadvalga "tortish" kerak bo'lgan qiymatlarni tanlaymiz. Bizning holatda, bu ustunning oralig'i Taklif yoqilgan 2-varaq. Tanlangan parchani sichqonchaning o'ng tugmasi bilan bosamiz. Ochilgan ro'yxatda tanlang Nusxalash. Boshqa alternativ klaviatura Ctrl + C. Shundan so'ng biz ko'chib o'tamiz 1-varaq.
- Bizga kerak bo'lgan kitob maydoniga o'tib, qiymatlarni olishimiz kerak bo'lgan katakchalarni tanlaymiz. Bizning holatda, bu ustun Taklif. Tanlangan parchani sichqonchaning o'ng tugmasi bilan bosamiz. Asboblar blokidagi kontekst menyusida Parametrlarni joylashtiring belgini bosing Havolani joylashtiring.
Shu bilan bir qatorda alternativa ham mavjud. Aytgancha, bu Excelning eski versiyalari uchun yagona. Kontekst menyusida kursor ustiga o'ting "Maxsus qo'shimchalar". Ochilgan qo'shimcha menyuda xuddi shu nom bilan pozitsiyani tanlang.
- Shundan so'ng, maxsus kiritish oynasi ochiladi. Tugmani bosing Havolani joylashtiring kameraning pastki chap burchagida.
- Qaysi variantni tanlashingizdan qat'i nazar, bitta jadval qatoridagi qiymatlar boshqasiga qo'shiladi. Manbadagi ma'lumotlarni o'zgartirganda, ular avtomatik ravishda kiritilgan diapazonda o'zgaradi.
Dars: Excelga maxsus qo'shimchalar
5-usul: bir nechta kitoblardagi jadvallar o'rtasidagi aloqa
Bunga qo'shimcha ravishda, turli xil kitoblarda stol maydonlari o'rtasidagi aloqani tashkil qilishingiz mumkin. Qo'shish uchun maxsus vosita ishlatiladi. Harakatlar avvalgi usulda ko'rib chiqilganlar bilan bir xil bo'ladi, bundan tashqari siz formulalarni bir xil kitobning joylari orasida emas, balki fayllar o'rtasida qilish uchun harakat qilishingiz kerak bo'ladi. Tabiiyki, barcha tegishli kitoblar ochiq bo'lishi kerak.
- Boshqa kitobga o'tkazmoqchi bo'lgan ma'lumotlar oralig'ini tanlang. Unga sichqonchaning o'ng tugmachasini bosing va ochiladigan menyuda o'rnini tanlang. Nusxalash.
- Keyin biz ushbu ma'lumotlarni kiritish kerak bo'lgan kitobga o'tamiz. Kerakli diapazonni tanlang. O'ng tugmachani bosing. Guruhdagi kontekst menyusida Parametrlarni joylashtiring elementni tanlang Havolani joylashtiring.
- Shundan so'ng, qiymatlar kiritiladi. Dastlabki ish daftaridagi ma'lumotlar o'zgarganda, ish daftaridagi jadval massivi uni avtomatik ravishda tortib oladi. Bundan tashqari, buning uchun ikkala kitob ham ochiq bo'lishi shart emas. Faqat bitta ishchi daftarni ochish kifoya qiladi va agar unga oldin biron-bir o'zgartirish kiritilgan bo'lsa, u avtomatik ravishda yopiq bog'langan hujjatdan ma'lumotlarni olib qo'yadi.
Ammo shuni ta'kidlash kerakki, bu holda qo'shimchalar o'zgarmas qator sifatida amalga oshiriladi. Kiritilgan ma'lumotlar bilan har qanday uyani o'zgartirishga harakat qilganingizda, xabar sizga buni amalga oshirishning iloji yo'qligi haqida xabar beradi.
Boshqa kitob bilan bog'langan bunday qatorni o'zgartirish faqat havolani buzish orqali amalga oshiriladi.
Jadvallar orasidagi bo'shliq
Ba'zan stol oralig'i orasidagi aloqani buzishingiz kerak. Buning sababi yuqorida tavsiflangan holat bo'lishi mumkin, agar siz boshqa kitobdan kiritilgan qatorni o'zgartirishingiz kerak bo'lsa yoki bitta jadvaldagi ma'lumotlar avtomatik ravishda boshqasidan avtomatik ravishda yangilansa, foydalanuvchining istamasligi.
1-usul: kitoblar orasidagi aloqani buzish
Siz deyarli bitta operatsiyani bajarib, barcha kataklardagi kitoblar o'rtasidagi aloqani uzishingiz mumkin. Shu bilan birga, hujayralardagi ma'lumotlar saqlanib qoladi, ammo ular boshqa hujjatlarga bog'liq bo'lmagan statik yangilanmagan qiymatlar bo'lib qoladi.
- Boshqa fayllardan qiymatlar olinadigan kitobda yorliqqa o'ting "Ma'lumotlar". Belgini bosing "Aloqalarni o'zgartirish"asboblar qutisidagi lentada joylashgan Aloqalar. Shuni ta'kidlash kerakki, agar joriy kitobda boshqa fayllarga havolalar bo'lmasa, ushbu tugma faol emas.
- Havolalarni o'zgartirish oynasi ishga tushirildi. Biz aloqani uzmoqchi bo'lgan faylni tegishli kitoblar ro'yxatidan tanlaymiz (agar bir nechta bo'lsa). Tugmani bosing Havolani uzing.
- Keyingi harakatlarning oqibatlari to'g'risida ogohlantirish mavjud bo'lgan ma'lumot oynasi ochiladi. Agar nima qilayotganingizni aniq bilsangiz, tugmachani bosing "Aloqalarni uzish".
- Shundan so'ng, joriy hujjatda ko'rsatilgan faylga barcha havolalar statik qiymatlar bilan almashtiriladi.
2-usul: Qiymatlarni joylashtiring
Ammo yuqoridagi usul, agar siz ikkita kitob o'rtasidagi barcha aloqalarni butunlay buzishingiz kerak bo'lsa, mos keladi. Agar bitta faylga tegishli jadvallarni ajratish kerak bo'lsa, nima qilish kerak? Buni ma'lumotlarni nusxalash va keyin qiymatlar bilan bir joyga joylashtirish orqali qilishingiz mumkin. Aytgancha, xuddi shu tarzda, fayllar orasidagi umumiy aloqani buzmasdan, siz turli xil kitoblarning individual ma'lumotlar qatorlari o'rtasidagi aloqani buzishingiz mumkin. Keling, ushbu usul amalda qanday ishlashini ko'rib chiqaylik.
- Boshqa jadvalga havolani olib tashlamoqchi bo'lgan diapazonni tanlang. Biz sichqonchaning o'ng tugmasi bilan bosamiz. Ochilgan menyuda tanlang Nusxalash. Ushbu harakatlar o'rniga siz issiq tugmachalarning alternativ kombinatsiyasini yozishingiz mumkin Ctrl + C.
- Bundan tashqari, tanlovni o'sha qismdan olib tashlamasdan, ustiga yana sichqonchaning o'ng tugmachasini bosing. Bu safar harakatlar ro'yxatidagi belgini bosing "Qadriyatlar"asboblar guruhida joylashgan Parametrlarni joylashtiring.
- Shundan so'ng, tanlangan oraliqdagi barcha havolalar statik qiymatlar bilan almashtiriladi.
Ko'rib turganingizdek, Excel-da bir nechta jadvallarni bir-biriga 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 osongina buzilishi mumkin.
