EXCELGA BOG'LANGAN ELEKTRON JADVALLAR

Excelda muayyan vazifalarni bajarayotib, ba'zida bir-birlari bilan bog'liq bo'lgan bir nechta jadvallarni ko'rib chiqish kerak. Ya'ni, bir stoldan olingan ma'lumotlar boshqa tomonga tortiladi va ular o'zgarganda, barcha tegishli jadval oralig'idagi qadriyatlar qayta hisob qilinadi.

Bog'langan jadvallar katta hajmdagi ma'lumotlarni qayta ishlash uchun juda foydali. Barcha ma'lumotlarni bir stolda olish juda qulay, agar u bir hil bo'lmagan bo'lsa. Bunday narsalar bilan ishlash va ularni qidirish qiyin. Bu muammoni o'zaro bog'liq jadvallarni yo'q qilish uchun mo'ljallangan, ular o'rtasida axborot tarqatiladi, biroq ayni paytda o'zaro bog'liqdir. Bog'langan jadval oralig'i nafaqat bitta varaq yoki bitta kitobda bo'lishi mumkin, balki alohida kitoblar (fayllar) da joylashgan bo'lishi mumkin. Amalda, oxirgi ikki variant ko'pincha ishlatiladi, chunki ushbu texnologiyaning maqsadi ma'lumotlarning to'planishidan uzoqlashish va ularni bitta sahifaga qistirish bu muammoni tubdan hal qilmaydi. Keling, ushbu ma'lumotlarni boshqarish usulini qanday yaratishni va qanday ishlashni o'rganib chiqamiz.

Bilan bog'liq jadvallarni yaratish

Birinchidan, keling, turli jadval oralig'lari o'rtasida qanday qilib bog'lanish mumkinligi haqidagi savolga to'xtalamiz.

1-usul: Jadvallarni formulalar bilan to'g'ridan-to'g'ri bog'lash

Ma'lumotni ulashning eng oson usuli boshqa jadval oraliqlariga bog'langan formuladan foydalanish. U to'g'ridan-to'g'ri bog'lanish deb ataladi. Ushbu usul intuitivdir, chunki u bilan bog'lanish deyarli bir xil tarzda amalga oshiriladi, chunki ma'lumotlar bitta jadval majmuasida ma'lumotlarga ishlov berish.

Keling, bir misolning to'g'ridan-to'g'ri bog'lanish orqali bog'lanishini qanday ko'rsata olamiz. Bizda ikkita jadval mavjud. Bir stolda ish haqi hamma uchun bir stavka bo'yicha ko'payib, formuladan foydalanib hisoblab chiqiladi.

Ikkinchi sahifada ish haqi bilan ishlaydigan xodimlar ro'yxati mavjud bo'lgan jadvallar mavjud. Har ikki holatda ham xodimlarning ro'yxati bir xil tartibda taqdim etiladi.

Ikkinchi plyonkaning stavkalari haqidagi ma'lumotlar birinchi navbatdagi mos keladigan hujayralarga tortilishi uchun kerak.

Birinchi varaqda birinchi ustunli xonani tanlang. "Tikish". Biz uning belgisiga qo'ydik "=". Keyinchalik yorliqni bosing "Sheet 2"Vaziyat satrining ustki qismidagi Excel interfeysi chap tomonida joylashgan.

Hujjatning ikkinchi qismiga o'tadi. Ustundagi birinchi xonani bosing. "Tikish". Keyin tugmani bosing. Kirish belgini ilgari o'rnatgan kamerada ma'lumotlarni kiritish uchun klaviatura orqali tengdir.

Keyin birinchi varaqqa avtomatik o'tish. Ko'rib turganingizdek, ikkinchi stolda birinchi xodimning nisbati tegishli kameraga tushiriladi. Kursorni pul tikadigan xujayraga joylashtirgan holda, odatdagi formulani ekranda ma'lumotlarni ko'rsatish uchun ishlatilganini ko'ramiz. Ammo ma'lumotlar ko'rsatiladigan hujayraning koordinatalari oldida bir ifoda mavjud "Sheet2!"bu ular joylashgan joyning nomini bildiradi. Bizning umumiy formula quyidagicha:
= Sheet2! B2

Endi siz korxonaning barcha xodimlarining tariflari bo'yicha ma'lumotlarni uzatishingiz kerak. Albatta, bu birinchi ishchi uchun vazifani bajarganimizdek amalga oshirilishi mumkin, ammo ikkala xodimlarning ro'yxati ham bir xil tartibda tartibga solinishi kerak, bu vazifani ancha soddalashtirishi va hal etishni tezlashtirishi mumkin. Buni formulani quyidagi diapazonga ko'chirish orqali amalga oshirish mumkin. Excelda ishoratlar sukut bo'yicha nisbiy, chunki kopyalanırken, qadriyatlar kaydırılır, zarur bo'lgan narsadir. Nusxa olish jarayonining o'zi to'ldirish belgisi yordamida amalga oshirilishi mumkin.
Shunday qilib, kursorni formuladan foydalanib elementning pastki o'ng maydoniga qo'ying. Shundan so'ng, kursorni qora chiziq shaklida to'ldirish kerak. Biz chap sichqoncha tugmachasini bajaramiz va kursorni ustunning pastki qismiga sudraymiz.

Bir xil ustundagi barcha ma'lumotlar Sheet 2 stolga tortildi Qobiq 1. Ma'lumotlar o'zgarganda Sheet 2 ular birinchi bo'lib avtomatik ravishda o'zgaradi.

2-usul: INDEX - MATCH operatorlari guruhidan foydalaning

Xo'sh, jadvallardagi ishchilarning ro'yxati bir xil tartibda tartibga solilmagan bo'lsa-chi? Bunday holda, yuqorida aytib o'tilganidek, variantlardan biri qo'l bilan bog'lanishi kerak bo'lgan barcha hujayralar o'rtasidagi ulanishni o'rnatishdan iborat. Lekin, bu faqat kichik jadvallar uchun javob beradi. Katta intervallarni uchun, bu variant, eng yaxshi tarzda amalga oshirish uchun ko'p vaqt talab etadi, eng yomoni esa amalda umuman amalga oshirilmaydi. Biroq, bu muammoni bir guruh operatorlar bilan hal qila olasiz INDEX - MAT. Keling, buni avvalgi usulda muhokama qilingan jadvallar oralig'ida ma'lumotlar bilan bog'lash yo'li bilan amalga oshirish mumkinligini ko'rib chiqamiz.

Ustundagi birinchi elementni tanlang. "Tikish". Boring Funktsiya ustasibelgini bosish orqali "Funktsiyani kiritish".

In Funktsiya ustasi guruhda "Links va arrays" nomini toping va tanlang INDEX.

Ushbu operator ikki shaklga ega: bir qatorda ishlaydigan va ariza bilan ishlaydigan form. Bizning holatda birinchi variant talab qilinadi, shuning uchun keyingi oynada ochiladigan formani tanlash uchun biz uni tanlaymiz va tugmani bosing "OK".

Operator argumenti oynasi ishga tushirildi. INDEX. Belgilangan vazifani bajarish vazifasi tanlangan dekabrdagi qiymatni ko'rsatilgan raqam bilan ko'rsatilganligini ko'rsatishdir. Bosh operatorlik formulasi INDEX bu shunday:
= INDEX (qator; line_number; [column_number])
"Array" - ma'lumotni belgilangan satr soniga ko'ra biz chiqaradigan oraliq manzilini o'z ichiga olgan argument.
"Chiziqli raqam" - bu satrning o'zi bo'lgan argument. Shuni bilish kerakki, chiziq raqami barcha hujjatga nisbatan emas, balki faqat tanlangan qatorga nisbatan belgilanmasligi kerak.
"Ustun raqami" - argument ixtiyoriy. Muammoni aniq hal qilish uchun uni ishlatmaymiz, shuning uchun uning mohiyatini alohida aytib berishning hojati yo'q.
Kursorni maydonga qo'ying "Array". Shundan keyin boringlar Sheet 2 va chap sichqoncha tugmachasini bosib, ustunning tarkibini tanlang "Tikish".

Operator oynasida koordinatalar ko'rsatilgandan so'ng, kursorni maydonga qo'ying "Chiziqli raqam". Ushbu argumentni operator yordamida namoyish etamiz MAT. Shuning uchun, funktsiya chizig'ining chap tomonida joylashgan uchburchakni bosing. Yaqinda foydalanilgan operatorlarning ro'yxati ochiladi. Agar ular orasida ismingizni topsangiz "MATCH"Keyin ustiga bosishingiz mumkin. Aks holda, ro'yxatdagi eng yangi narsani bosing - "Boshqa funktsiyalar ...".

Standart oyna boshlanadi. Funktsional ustalar. Xuddi shu guruhga boring. "Links va arrays". Ro'yxatdagi bu safar, elementni tanlang "MATCH". Tugmachani bosing. "OK".

Operator oyna argumentlarini faollashtiradi MAT. Belgilangan funktsiya ma'lum bir qatordagi qiymatning nomini uning nomi bilan aks ettirishga mo'ljallangan. Fursatdan foydalanib, funksiya uchun ma'lum bir qiymatning qator sonini hisoblaymiz. INDEX. Matnni sintaksisi MAT quyidagicha taqdim etildi:
= MATCH (qidirish qiymati; qidirish qatori; [match_type])
"Ko'rsatilgan qiymat" - u joylashgan uchinchi tomon oralig'i uyasi nomini yoki manzilini o'z ichiga olgan argument. Bu nomning maqsad oralig'ida joylashganligi, hisoblash kerak. Bizning holatda birinchi dalil hujayra arizalari bo'ladi Qobiq 1unda xodimlarning ismlari joylashgan.
"Ko'rilgan qator" - ko'rsatilgan qiymatni uning o'rnini aniqlash uchun izlanadigan qatorga bog'lanishni ifodalovchi argument. Biz ushbu rolni "Ism ochiq Sheet 2.
"Mapping Type" - ixtiyoriy bo'lgan argument, lekin avvalgi bayonotdan farqli o'laroq, biz ushbu ixtiyoriy dalilga muhtojmiz. Operatorning istalgan qiymatni qator bilan qanday bajarilishini ko'rsatadi. Ushbu dalillar uchta qiymatdan biriga ega bo'lishi mumkin: -1; 0; 1. Ajitilmagan qatorlar uchun variantni tanlang "0". Ushbu parametr bizning ishimiz uchun javob beradi.
Shunday qilib, argumentlar oynasining maydonlarini to'ldiraylik. Kursorni maydonga qo'ying "Ko'rsatilgan qiymat", ustun birinchi xonasida bosing "Ism" ochiq Qobiq 1.

Koordinatalar ko'rsatilgandan so'ng, kursorni maydonga qo'ying "Ko'rilgan qator" yorliqni davom eting "Sheet 2"Excel oynasining pastki qismida joylashgan holat satrida joylashgan. Sichqonchaning chap tugmachasini bosing va ushlab turing va ustundagi barcha xujayralarni belgilang. "Ism".

Joylarda koordinatalari ko'rsatilgandan keyin "Ko'rilgan qator"maydonga boring "Mapping Type" va raqamni klaviaturadan o'rnating "0". Shundan keyin yana maydonga qaytamiz. "Ko'rilgan qator". Aslida, avvalgi usuliga ko'ra, formulani nusxa ko'chirishimiz kerak. Manzillar almashinuvi bo'ladi, lekin ko'rib chiqilayotgan qatorning koordinatalarini tuzatishimiz kerak. O'zgarish kerak emas. Kursor koordinatalarini tanlang va funktsiya tugmachasini bosing F4. Ko'rib turganingizdek, koordinatalar oldida dollar belgisi paydo bo'ldi. Ya'ni, qarindoshlar bilan bog'lanish absolyut bo'ldi. Keyin tugmani bosing "OK".

Natijada ustun birinchi xonasida ko'rsatiladi. "Tikish". Lekin nusxalashdan oldin biz boshqa maydonni, ya'ni funksiyaning birinchi argumentini tuzatishimiz kerak INDEX. Buning uchun formulani o'z ichiga olgan ustun elementini tanlang va formulalar paneliga o'ting. Operatorning birinchi dalilini tanlang INDEX (B2: B7) tugmasini bosing va tugmani bosing F4. Ko'rib turganingizdek, tanlangan koordinatalar yonida dollar belgisi paydo bo'ldi. Tugmasini bosing Kirish. Umumiy holda, formula quyidagi shaklni oldi:
= INDEX (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0))

Keling, to'ldirish belgisini ishlatib nusxa ko'chirishingiz mumkin. Uni ilgari aytib o'tganimiz kabi chaqiring va uni stol oralig'i oxirigacha cho'zing.

Ko'rib turganingizdek, ikkita jadvalning satrlari tartibiga to'g'ri kelmasligiga qaramasdan, barcha qiymatlar ishchilar nomlari bo'yicha mustahkamlanadi. Bunga operatorlarning kombinatsiyasidan foydalanish orqali erishildi INDEX-MAT.

Shuningdek qarang:
Excel funktsiyasi INDEX
Excelda o'yin funksiyasi

Boshqaruv 3: Matematik operatsiyalarni biriktirilgan ma'lumotlar bilan bajarish

To'g'ridan-to'g'ri ma'lumotlarni uzatish, shuningdek, jadvallarning birida boshqa jadval oralig'ida ko'rsatilgan qiymatlarni aks ettirishga emas, balki ular bilan birgalikda turli matematik operatsiyalarni amalga oshirishga (qo'shimcha, bo'linish, olib tashlash, ko'paytirish, va hokazo) imkonini beradi.

Keling, buni amalda qanday amalga oshirayotganini ko'rib chiqaylik. Keling, buni qilaylik Qog'oz 3 umumiy korxona ish haqi ma'lumotlari ishchilarning tanaffusisiz namoyish etiladi. Buning uchun xodimlarning stavkalari olinadi Sheet 2(Funktsiyadan foydalangan holda) SUM) va formuladan foydalanib koeffitsientga ko'paytiriladi.

To'liq bordro ko'rsatiladigan kamerani tanlang Qog'oz 3. Tugmasini bosing "Funktsiyani kiritish".

Bu oyna ochilishi kerak Funktsional ustalar. Guruhga boring "Matematik" va u erda ismni tanlang "SUMM". Keyin tugmani bosing "OK".

Funktsiya argumentlari oynasiga o'tish SUMtanlangan raqamlarning yig'indisini hisoblash uchun mo'ljallangan. Quyidagi sintaksisga ega:
= SUM (raqam1; raqam2; ...)
Oynadagi maydonlar belgilangan funktsiyaning argumentlariga mos keladi. Ularning soni 255 donani tashkil etishi mumkin bo'lsa-da, bizning maqsadimiz uchun faqat bittadir. Kursorni maydonga qo'ying "1-son". Yorliqni bosing "Sheet 2" holat satrining yuqorisida.

Kitobning istalgan qismiga ko'chgandan keyin, jamlanadigan ustunni tanlang. Sichqonchaning chap tugmachasini bosib, kursorni bajaramiz. Ko'rib turganingizdek, tanlangan maydonning koordinatalari darhol dalillar oynasi maydonida ko'rsatiladi. Keyin tugmani bosing. "OK".

Shundan so'ng biz avtomatik tarzda harakat qilamiz Qobiq 1. Ko'rib turganingizdek, ishchilarning ish haqi miqdorining umumiy miqdori allaqachon tegishli elementda ko'rsatiladi.

Lekin bu hammasi emas. Ma'lumki, ish haqi stavka qiymatini koeffitsient bo'yicha ko'paytirish orqali hisoblab chiqiladi. Shuning uchun, biz jamlangan qiymatning joylashgan kamerani yana tanlaymiz. Shundan so'ng formula bariga o'ting. Biz uning formula bilan ko'payish belgisini qo'shamiz (*) va keyin koeffitsient joylashgan elementni bosing. Hisoblashni bajarish uchun bosing Kirish klaviaturada. Ko'rib turganingizdek, dastur korxonaning umumiy ish haqini hisoblab chiqdi.

Orqaga qayting Sheet 2 va har qanday xodimning nisbati hajmini o'zgartirish.

Shundan keyin yana jami miqdorda sahifaga o'ting. Ko'rib turganingizdek, tegishli jadvaldagi o'zgarishlar tufayli, umumiy ish haqining natijasi avtomatik ravishda qayta hisoblab chiqilgan.

Boshqaruv 4: maxsus qo'shimchalar

Masofadagi jadvallarni Excelga maxsus biriktirma bilan bog'lashingiz mumkin.

Boshqa jadvalga "qisqartirilishi" kerak bo'lgan qiymatlarni tanlang. Bizning holatimizda bu ustunlar oralig'i. "Tikish" ochiq Sheet 2. Sichqonchaning o'ng tugmasi bilan tanlangan qismni bosing. Ochilgan ro'yxatda elementni tanlang "Nusxalash". Shu bilan bir qatorda kalit birikmasi Ctrl + C. Keyinchalik bu ko'chirish Qobiq 1.

Kitobning istalgan joyiga borib, biz qiymatlarni olishni istagan xujayralarni tanlaymiz. Bizning holatimizda bu ustundir. "Tikish". Sichqonchaning o'ng tugmasi bilan tanlangan qismni bosing. Asboblar panelidagi kontekst menyusida "Qo'shish tanlovlari" belgini bosing "Bog'ni qo'shing".
Bundan tashqari, muqobil mavjud. Aytgancha, u Excelning eski versiyalari uchun yagona. Kontekst menyusida kursorni elementga o'tkazing "Maxsus joylashtiring". Ochilgan qo'shimcha menyuda bir xil nomdagi elementni tanlang.

Shundan so'ng maxsus kirish oynasi ochiladi. Biz tugmani bosamiz "Bog'ni qo'shing" hujayraning pastki chap burchagida.

Siz tanlagan variantlardan biri, bitta jadval majmuasidan olingan qiymatlar boshqasiga kiritiladi. Resursdagi ma'lumotlarni o'zgartirsangiz, ular avtomatik ravishda kiritilgan intervalda ham o'zgaradi.

Kurs: Excelga maxsus joylashtiring

5-usul: Bir nechta kitoblarda jadvallar o'rtasidagi munosabatlar

Bundan tashqari, siz turli kitoblarda joylashgan jadvallar orasidagi aloqani tashkil qilishingiz mumkin. Bu maxsus qo'shish vositasidan foydalanadi. Amallar avvalgi usulda ko'rib chiqilgan narsalarga o'xshash bo'ladi, faqat formulalarni kiritish paytida navigatsiya bir kitobning maydonlari o'rtasida emas, balki fayllar o'rtasida bo'lishi kerak. Tabiiyki, barcha tegishli kitoblar ochiq bo'lishi kerak.

Boshqa kitobga o'tkazmoqchi bo'lgan ma'lumotlar doirasini tanlang. Sichqonchaning o'ng tugmasi bilan ustiga bosing va ochiladigan menyudan joyni tanlang "Nusxalash".

Keyin biz ushbu ma'lumotni kiritish kerak bo'lgan kitobga o'tamiz. Kerakli oraliqni tanlang. O'ng sichqoncha tugmasini bosing. Guruhdagi kontekst menyusida "Qo'shish tanlovlari" ob'ektni tanlang "Bog'ni qo'shing".

Shundan so'ng, qiymatlar kiritiladi. Resurs kitobidagi ma'lumotni o'zgartirganda, ishchi daftari jadvalidan ularni avtomatik ravishda tortib oladi. Va har ikkala kitob ham buning uchun ochiq bo'lishi shart emas. Faqatgina bitta ishchi daftarini ochish kifoya va agar u ilgari o'zgartirilgan bo'lsa, u avtomatik ravishda yopiq hujjatdagi ma'lumotlarni oladi.

Lekin shuni ta'kidlash kerakki, bu holatda kiritma o'zgarmas qator shaklida amalga oshiriladi. Agar kiritilgan ma'lumotlar bilan har qanday xujayrani o'zgartirmoqchi bo'lsangiz, buni amalga oshirishning iloji yo'qligi haqida sizni xabardor qiladi.

Boshqa bir kitob bilan bog'liq bo'lgan bunday qatordagi o'zgarishlar faqat havolani buzish orqali amalga oshirilishi mumkin.

Jadvallar o'rtasida uzilish

Ba'zan jadval oralig'i orasidagi aloqani uzish kerak. Buning sababi, yuqorida bayon qilingan holatga ko'ra, boshqa bir kitobdan kiritilgan qatorni o'zgartirmoqchi bo'lganingizda yoki foydalanuvchi bir jadvaldagi ma'lumotlarni avtomatik ravishda boshqasidan yangilanishini istamaganligi sababli bo'lishi mumkin.

1-usul: kitoblarni ajratish

Siz deyarli bitta operatsiyani bajarish orqali barcha xujjatlardagi kitoblar orasidagi aloqani uzib qo'yishingiz mumkin. Shu bilan birga, hujayralardagi ma'lumotlar qoladi, ammo ular allaqachon boshqa hujjatlarga qaram bo'lmagan statik yangilanmagan qiymatlar bo'ladi.

Boshqa fayllar qiymatlari tortilgan kitobda tabga o'ting "Ma'lumot". Belgini bosing "Aloqalarni tahrirlash"asboblar blokidagi kasetlarda joylashgan "Ishoratlar". Shuni ta'kidlash kerakki, agar mavjud kitobda boshqa fayllarga havolalar bo'lmasa, ushbu tugma faol emas.

Ulanishni o'zgartirish oynasi ishga tushirildi. Aloqani buzmoqchi bo'lgan faylni tegishli kitoblar ro'yxatidan (agar mavjud bo'lsa) tanlang. Tugmasini bosing "Aloqani uzib qo'ying".

Keyingi harakatlarning natijalari to'g'risida ogohlantiruvchi axborot oynasi ochiladi. Agar nima qilmoqchi ekanligingizga amin bo'lsangiz, tugmani bosing. "Bog'lanishni to'xtatish".

Shundan so'ng, mavjud dokumentdagi ko'rsatilgan faylga barcha havolalar statik qiymatlar bilan almashtiriladi.

Boshqaruv 2: qiymatlarni qo'shish

Ammo yuqoridagi usul faqat ikki kitob o'rtasidagi barcha aloqalarni butunlay yo'q qilish kerak bo'lsa, mos keladi. Agar siz o'sha faylda joylashgan tegishli jadvallarni o'chirib qo'ysangiz, nima qilish kerak? Buni ma'lumotlarni nusxalash va keyin qiymatlarni bir xil joyga joylashtirish orqali qilishingiz mumkin.Aytgancha, turli xil kitoblardagi alohida ma'lumotlar oralig'lari o'rtasidagi fayllarni fayllar o'rtasidagi umumiy ulanishni buzmasdan, bu usulni ishlatish mumkin. Keling, ushbu uslub amalda qanday ishlashini ko'rib chiqaylik.

Boshqa jadvalga aloqani olib tashlashni istagan intervalni tanlang. O'ng sichqoncha tugmachasini bosing. Ochilgan menyuda elementni tanlang "Nusxalash". Ushbu xatti-harakatlarning o'rniga muqobil hot-key kombinatsiyasini kiritishingiz mumkin. Ctrl + C.

Keyin, tanlovni bir xil qismdan olib tashlamasdan, sichqonchani o'ng tugmachasini bosamiz. Ushbu marshrutni ro'yxatda biz ikonka bosamiz "Qadriyatlar"bir qator vositalarga joylashtirilgan "Qo'shish tanlovlari".

Shundan so'ng, tanlangan intervaldagi barcha yo'nalishlarga statik qiymatlar qo'yiladi.

Ko'rib turganingizdek, Excelda bir nechta jadvallarni bir-biri bilan bog'lash uchun usullar va vositalar mavjud. Bunday holatda, jadvallar boshqa sahifalarda yoki hatto turli kitoblarda bo'lishi mumkin. Agar kerak bo'lsa, bu aloqa osongina singan bo'lishi mumkin.