Excel-də müəyyən tapşırıqları yerinə yetirərkən bəzən əlaqəli olan bir neçə cədvəllə də məşğul olmalısınız. Yəni bir cədvəldən məlumatlar digərinə çəkilir və dəyişdirildikdə, bütün əlaqəli masa aralığında dəyərlər yenidən hesablanır.
Bağlı masalar çox sayda məlumatın işlənməsi üçün istifadə üçün çox əlverişlidir. Bütün məlumatları bir cədvəldə yerləşdirmək, bundan əlavə, homojen deyilsə, çox əlverişli deyil. Belə obyektlərlə işləmək və onları axtarmaq çətindir. Göstərilən problem əlaqəli cədvəllər tərəfindən aradan qaldırılmaq üçün hazırlanmışdır ki, bunlar arasında məlumatlar paylanır, lakin eyni zamanda bir-biri ilə əlaqəlidir. Bağlı masa silsilələri yalnız bir vərəqdə və ya tək bir kitab içərisində deyil, ayrıca kitablarda (fayllarda) yerləşə bilər. Sonuncu iki seçim ən çox praktikada istifadə olunur, çünki bu texnologiyanın məqsədi məlumatların yığılmasından uzaqlaşmaqdır və onları bir səhifəyə yığmaq problemi köklü şəkildə həll etmir. Bu tip məlumatların idarə olunması ilə necə yaradılacağını və necə işlədiyini öyrənək.
Bağlı Masaların yaradılması
Əvvəla, fərqli masa aralığı arasında əlaqə yaratmaq üçün hansı üsulların olması sualına diqqət yetirək.
Metod 1: masaları birbaşa bir formula ilə əlaqələndirin
Məlumatları bağlamağın ən asan yolu, digər cədvəl diapazonlarına istinad edən düsturlardan istifadə etməkdir. Buna birbaşa bağlama deyilir. Bu metod intuitivdir, çünki onunla əlaqələndirmə bir cədvəl massivində məlumatlara keçid yaratmaqla demək olar ki, eyni şəkildə həyata keçirilir.
Nümunə olaraq bir bağlama birbaşa bağlanma yolu ilə necə yarana biləcəyini görək. İki vərəqdə iki masamız var. Bir cədvəldə əmək haqqı, hamı üçün vahid bir əmsala işçi nisbətinə vurularaq düsturla hesablanır.
İkinci vərəqdə, maaşları ilə işçilərin siyahısı olan bir cədvəl aralığı var. Hər iki halda işçilərin siyahısı eyni qaydada təqdim olunur.
İkinci vərəqdəki nisbətlərdəki məlumatların birincisinin müvafiq hücrələrinə çəkildiyinə əmin olmaq lazımdır.
- Birinci vərəqdə sütundakı ilk xananı seçin Təklif. Bir işarə qoyduq "=". Sonra, qısa yola vurun "Vərəq 2", status çubuğunun üstündəki Excel interfeysinin sol tərəfində yerləşir.
- Sənədin ikinci sahəsinə keçir. Sütundakı ilk xananı tıklayırıq Təklif. Sonra düyməni vurun Daxil edin əvvəlcədən işarənin qoyulduğu hücrəyə məlumat daxil etmək üçün klaviaturada bərabərdir.
- Sonra ilk vərəqə avtomatik keçid var. Gördüyünüz kimi, ikinci cədvəldən birinci işçinin nisbəti müvafiq xanaya çəkilir. Kursoru bahis olan hücrəyə yerləşdirməklə adi formulun ekranda məlumatları göstərmək üçün istifadə edildiyini görürük. Lakin məlumatların çıxdığı yerdən hücrənin koordinatlarından əvvəl bir ifadə var "Vərəq2!", yerləşdikləri sənəd sahəsinin adını göstərir. Bizim vəziyyətimizdəki ümumi formula belə görünür:
= Vərəq2! B2 - İndi müəssisənin bütün digər işçilərinin dərəcələri barədə məlumatları köçürməlisiniz. Əlbəttə ki, bu, ilk işçi üçün tapşırığı tamamladığımız şəkildə edilə bilər, ancaq işçilərin hər iki siyahının eyni qaydada qurulduğunu nəzərə alsaq, tapşırıq xeyli asanlaşdırıla və həllini sürətləndirə bilər. Bu, sadəcə düsturu aşağıdakı diapazona kopyalayaraq edilə bilər. Excel-dəki bağlantıların adətən nisbi olduğuna görə, kopyalananda dəyərlər dəyişir, bu da bizə lazım olan şeydir. Kopyalama prosedurunun özü doldurma işarəsi istifadə edərək edilə bilər.
Beləliklə, kursoru düsturla elementin sağ alt hissəsinə qoyun. Bundan sonra, kursor qara bir xaç şəklində doldurma işarəsinə çevrilməlidir. Sol siçan düyməsini sıxın və kursoru sütunun ən aşağı hissəsinə sürükləyin.
- Bənzər bir sütundakı bütün məlumatlar Vərəq 2 bir masaya çəkildi 1 nömrəli vərəq. Məlumat dəyişdirərkən Vərəq 2 avtomatik olaraq birincisinə dəyişəcəklər.
Metod 2: INDEX operatorlarından bir dəstə istifadə edərək - AXTAR
Bəs cədvəl seriallarında işçilərin siyahısı eyni qaydada deyilsə nə etməli? Bu vəziyyətdə, əvvəllər qeyd edildiyi kimi, seçimlərdən biri əl ilə bağlanmalı olan hüceyrələrin hər biri arasında əlaqə yaratmaqdır. Ancaq bu yalnız kiçik masalar üçün uygundur. Kütləvi aralıqlar üçün ən yaxşı halda belə bir seçim həyata keçirmək üçün çox vaxt tələb edəcək və ən pis halda, praktikada bu mümkün olmayacaqdır. Amma bu problem bir dəstə operatordan istifadə etməklə həll edilə bilər INDEX - AXIR. Əvvəlki metodda müzakirə olunan cədvəl aralığındakı məlumatları əlaqələndirərək bunun necə edilə biləcəyini görək.
- İlk sütun elementini seçin Təklif. Gedin Xüsusiyyət sehrbazdırişarəsinə basaraq "Funksiya daxil et".
- In Funksiya ustası qrupda İstinadlar və seriallar adı tapın və vurğulayın INDEX.
- Bu operatorun iki forması var: seriallarla işləmək üçün bir forma və bir istinad. Bizim vəziyyətimizdə ilk seçim tələb olunur, buna görə açılmış formanı seçmək üçün növbəti pəncərədə onu seçin və düyməni vurun "OK".
- Operator dəlilləri pəncərəsi açıldı INDEX. Bu funksiyanın vəzifəsi, seçilmiş diapazonda olan bir göstərilən nömrəyə uyğun bir dəyəri çıxarmaqdır. Ümumi operator düsturu INDEX belədir:
= INDEX (sıra; sıra_nömrə; [sütun_nömrəsi])Array - göstərilən sətrin nömrəsi ilə məlumat çıxardacağımız aralığın adını ehtiva edən bir dəlil.
Sətir nömrəsi - bu çox xəttin sayı olan dəlil. Sətir nömrəsinin bütün sənədə nisbətən göstərilməməsi vacibdir, ancaq seçilmiş massivə nisbətən.
Sütun nömrəsi - ixtiyari olan bir dəlil. Xüsusi problemimizi həll etmək üçün istifadə etməyəcəyik və buna görə onun mahiyyətini ayrıca izah etmək lazım deyil.
Kursoru sahəyə qoyun Array. Bundan sonra gedin Vərəq 2 və sol siçan düyməsini tutaraq sütunun bütün məzmununu seçin Təklif.
- Koordinatlar operator pəncərəsində göründükdən sonra kursoru sahəyə qoyun Sətir nömrəsi. Bu dəlili operatordan istifadə edərək çıxaracağıq AXIR. Buna görə, funksiya xəttinin solunda yerləşən üçbucağı tıklayırıq. Bu yaxınlarda istifadə olunan operatorların siyahısı açılır. Aralarında bir ad taparsan "AXTAR"sonra vura bilərsiniz. Əks təqdirdə, siyahıdakı son elementi vurun - "Digər xüsusiyyətlər ...".
- Standart pəncərə başlayır Funksiya sehrbazları. Eyni qrupa daxil oluruq İstinadlar və seriallar. Bu dəfə siyahıdakı elementi seçin "AXTAR". Düyməni vurun. "OK".
- Operator dəlilləri pəncərəsi aktivdir AXIR. Göstərilən funksiya, bir sıra sayını müəyyən bir massivdə öz adı ilə göstərmək məqsədi daşıyır. Bu xüsusiyyət sayəsində funksiya üçün müəyyən bir dəyərin xətt nömrəsini hesablayacağıq INDEX. Sintaksis AXIR aşağıdakı kimi təmsil olunur:
= AXTARIŞ (axtarış_qiymət; axtarış_array; [match_type])"Dəyər axtarır" - yerləşdiyi üçüncü tərəf aralığının hücrəsinin adını və ya ünvanını ehtiva edən bir dəlil. Hesablanmalı olan hədəf aralığında bu adın mövqeyi. Bizim vəziyyətimizdə, ilk dəlil, hüceyrələrə istinad olacaq 1 nömrəli vərəqişçilərin adlarının olduğu yer.
Array baxdı - mövqeyini müəyyənləşdirmək üçün göstərilən dəyərin axtarıldığı bir sıra istinadını təmsil edən bir dəlil. Sütun ünvanı "burada bu rol oynayacaq."Ad yandır Vərəq 2.
Uyğunluq növü - ixtiyari bir dəlil, lakin əvvəlki ifadədən fərqli olaraq bu ixtiyari arqumentə ehtiyac duyacağıq. Operatorun axtarış dəyərini serial ilə necə uyğunlaşdıracağını göstərir. Bu dəlil üç dəyərdən birinə sahib ola bilər: -1; 0; 1. Sıralanmayan seriallar üçün seçin "0". Bu seçim bizim vəziyyətimizə uyğundur.
Beləliklə, arqumentlər pəncərəsinin sahələrini doldurmağa başlayaq. Kursoru sahəyə qoyun "Dəyər axtarır"sütunun ilk xanasına vurun "Ad" yandır 1 nömrəli vərəq.
- Koordinatlar göstərildikdən sonra kursoru sahəyə qoyun Array baxdı və qısa yola vurun "Vərəq 2", status çubuğunun üstündəki Excel pəncərəsinin altındakı yerdədir. Sol siçan düyməsini basıb kursor ilə sütundakı bütün hüceyrələri seçin "Ad".
- Onların koordinatları sahəyə çıxdıqdan sonra Array baxdısahəyə getmək Uyğunluq növü klaviaturadan oradakı nömrəni təyin et "0". Bundan sonra yenidən sahəyə qayıdırıq Array baxdı. Fakt budur ki, əvvəlki metodda etdiyimiz kimi düsturu kopyalayacağıq. Ünvan dəyişməsi baş verəcək, ancaq burada görünən serialın koordinatlarını düzəltməliyik. Köçürülməməlidir. Kursorla əlaqələndiriciləri seçin və funksiya düyməsini basın F4. Gördüyünüz kimi, dollar işarəsi koordinatların qarşısında göründü, bu da əlaqənin nisbi haldan mütləqə çevrildiyini göstərir. Sonra düyməni vurun "OK".
- Nəticə sütunun ilk kamerasında göstərilir. Təklif. Ancaq kopyalamadan əvvəl başqa bir sahəni, yəni funksiyanın ilk arqumentini düzəltməliyik INDEX. Bunu etmək üçün, düsturu olan sütun elementini seçin və düsturlar xəttinə keçin. Operatorun ilk arqumentini seçirik INDEX (B2: B7) düyməsini basın F4. Gördüyünüz kimi, dollar işarəsi seçilmiş koordinatların yanında göründü. Düyməni vurun Daxil edin. Ümumiyyətlə, formula aşağıdakı forma aldı:
= INDEX (Vərəq2! $ B $ 2: $ B $ 7; AXTARIQ (Vərəq1! A4; Vərəq2! $ A $ 2: $ A $ 7; 0)) - İndi doldurma markerindən istifadə edərək kopyalaya bilərsiniz. Daha əvvəl danışdığımız kimi çağırırıq və masa aralığının sonuna qədər uzatırıq.
- Gördüyünüz kimi, iki əlaqəli cədvəlin sıra sırası uyğun gəlməməsinə baxmayaraq, bütün dəyərlər işçilərin adlarına görə çəkilir. Buna operatorların birləşməsindən istifadə etməklə nail olunmuşdur INDEX-AXIR.
Həm də oxuyun:
Excel-də EXEX funksiyası
Excel-də EXCEL funksiyası
Metod 3: əlaqəli məlumatlarla riyazi əməliyyatları yerinə yetirin
Birbaşa məlumatların bağlanması da yaxşıdır, çünki bu, cədvəllərdən birində digər cədvəl aralığında göstərilən dəyərləri göstərməyə deyil, onlarla birlikdə müxtəlif riyazi əməliyyatları (əlavə, bölmə, toplama işləmə, vurma və s.) Həyata keçirməyə imkan verir.
Bunun praktikada necə həyata keçirildiyini görək. Bunu edək Vərəq 3 şirkət üçün ümumi əmək haqqı məlumatları işçilər tərəfindən bölünmədən göstərilir. Bunun üçün işçi nisbətləri tutulacaq Vərəq 2, ümumiləşdirilmiş (funksiyadan istifadə etməklə) SUM) və düsturu istifadə edərək bir əmsalla vurun.
- Əmək haqqı hesablamasının nəticəsinin göstəriləcəyi hücrəni seçin. Vərəq 3. Düyməni vurun. "Funksiya daxil et".
- Pəncərə başlamalıdır Funksiya sehrbazları. Qrupa gedin "Riyazi" və orada ad seçin SUM. Sonra, düyməni vurun "OK".
- Funksiya dəlilləri pəncərəyə köçürülür SUM, seçilmiş nömrələrin cəmini hesablamaq üçün hazırlanmışdır. Bu aşağıdakı sintaksis var:
= SUM (nömrə 1; nömrə2; ...)Pəncərədəki sahələr göstərilən funksiyanın arqumentlərinə uyğundur. Onların sayı 255-ə çata bilsə də, məqsədimiz üçün yalnız biri kifayət edəcəkdir. Kursoru sahəyə qoyun "Sayı1". Qısayolu vurun "Vərəq 2" status barının üstündədir.
- Kitabın istənilən hissəsinə keçdikdən sonra ümumiləşdirilməli sütunu seçin. Sol siçan düyməsini tutarkən kursorla bunu edirik. Gördüyünüz kimi, seçilmiş ərazinin koordinatları dərhal arqumentlər pəncərəsinin sahəsində göstərilir. Sonra düyməni vurun "OK".
- Bundan sonra avtomatik olaraq hərəkət edirik 1 nömrəli vərəq. Gördüyünüz kimi, işçi təkliflərinin ümumi miqdarı artıq müvafiq elementdə göstərilir.
- Ancaq bu hamısı deyil. Yadımızdadır, əmək haqqı nisbətin dəyərini bir amilə çarpmaqla hesablanır. Buna görə, yenidən qiymətləndirilmiş dəyərin yerləşdiyi xananı seçirik. Bundan sonra düsturlar xəttinə keçirik. İçindəki düstura bir vurma işarəsi əlavə edin (*), sonra əmsal göstəricisinin yerləşdiyi elementə vurun. Hesablamanı yerinə yetirmək üçün düyməni vurun Daxil edin klaviaturada. Gördüyünüz kimi, proqram müəssisə üçün ümumi əmək haqqını hesabladı.
- Geri qayıt Vərəq 2 və hər hansı bir işçinin dərəcəsini dəyişdirin.
- Bundan sonra yenidən ümumi məbləği ilə səhifəyə keçirik. Gördüyünüz kimi, əlaqəli cədvəldəki dəyişikliklər səbəbindən ümumi əmək haqqının nəticəsi avtomatik olaraq yenidən hesablandı.
Metod 4: xüsusi əlavə
Xüsusi bir əlavə istifadə edərək Excel-də masa seriallarını əlaqələndirə bilərsiniz.
- Başqa bir cədvələ "çəkilmək" lazım olan dəyərləri seçirik. Bizim vəziyyətimizdə bu sütunun aralığıdır Təklif yandır Vərəq 2. Seçilən fraqmenti sağ siçan düyməsinə vururuq. Açılan siyahıdan seçin Surəti. Alternativ bir klaviatura qısa yoludur Ctrl + C. Bundan sonra köçürük 1 nömrəli vərəq.
- Lazım olan kitabın sahəsinə keçərək, dəyərləri çıxartmaq üçün lazım olan hüceyrələri seçirik. Bizim vəziyyətimizdə bu bir sütundur Təklif. Seçilən fraqmenti sağ siçan düyməsinə vururuq. Alətlər blokundakı kontekst menyusunda Seçimlər daxil edin simvolu vurun Link yapışdırın.
Alternativ də var. Yeri gəlmişkən, Excel-in köhnə versiyaları üçün yeganə. Kontekst menyusunda yuxarıya aparın "Xüsusi əlavə". Açılan əlavə menyudan eyni adla mövqe seçin.
- Bundan sonra xüsusi əlavə pəncərə açılır. Düyməni vurun Link yapışdırın hüceyrənin aşağı sol küncündə.
- Seçdiyiniz hansındansa, bir masa massivindəki dəyərlər digərinə daxil ediləcəkdir. Mənbədəki məlumatları dəyişdirərkən, daxil edilmiş diapazonda da avtomatik olaraq dəyişəcəklər.
Dərs: Excel-də xüsusi əlavə
Metod 5: çox kitabdakı cədvəllər arasında əlaqə
Bundan əlavə, müxtəlif kitablarda masa sahələri arasında ünsiyyət təşkil edə bilərsiniz. Xüsusi bir yerləşdirmə vasitəsi istifadə olunur. Eyni kitabın sahələri arasında deyil, sənədlər arasında düsturlar hazırlayarkən getmək məcburiyyətində qalmağınızdan başqa hərəkətlər əvvəlki metodda nəzərdən keçirdiyimizlə eyni olacaq. Təbii ki, əlaqəli bütün kitablar açıq olmalıdır.
- Başqa bir kitaba köçürmək istədiyiniz məlumat aralığını seçin. Bunun üzərinə sağ vurun və açılan menyudan mövqeyi seçin. Surəti.
- Sonra bu məlumatların daxil edilməsi lazım olan kitaba köçürük. İstədiyiniz diapazonu seçin. Sağ vurun. Qrupdakı kontekst menyusunda Seçimlər daxil edin element seçin Link yapışdırın.
- Bundan sonra dəyərlər daxil olacaq. Mənbə iş kitabındakı məlumatlar dəyişdikdə, iş dəftərindəki cədvəl silsiləsi onu avtomatik olaraq çəkəcəkdir. Üstəlik, hər iki kitabın bunun üçün açıq olması lazım deyildir. Yalnız bir iş kitabını açmaq kifayətdir və əvvəllər dəyişikliklər edildiyi təqdirdə avtomatik olaraq bağlanmış əlaqəli sənəddən məlumatları götürəcəkdir.
Ancaq qeyd etmək lazımdır ki, bu vəziyyətdə əlavə dəyişməz bir sıra şəklində hazırlanacaqdır. Daxil edilmiş məlumatlarla hər hansı bir hüceyrəni dəyişdirməyə çalışdığınız zaman, bunun mümkün olmadığını bildirən bir mesaj ortaya çıxır.
Başqa bir kitabla əlaqəli belə bir serialda dəyişikliklər yalnız əlaqəni pozmaqla edilə bilər.
Masalar arasındakı boşluq
Bəzən masa aralığı arasındakı əlaqəni pozmaq lazımdır. Bunun səbəbi ya başqa bir kitabdan daxil edilmiş bir sıra dəyişdirmək lazım olduqda, yuxarıda göstərilən hal və ya istifadəçinin bir cədvəldəki məlumatların avtomatik olaraq digərindən yenilənməsi istəyi ola bilər.
Metod 1: kitablar arasındakı əlaqəni pozmaq
Faktiki olaraq bir əməliyyat həyata keçirərək bütün hüceyrələrdəki kitablar arasındakı əlaqəni kəsə bilərsiniz. Eyni zamanda, hüceyrələrdəki məlumatlar qalacaq, ancaq onsuz da digər sənədlərdən asılı olmayan statik yenilənməmiş dəyərlər olacaqdır.
- Kitabda, digər sənədlərdən dəyərlərin çəkildiyi nişana keçin "Məlumat". İşarəni vurun "Rabitə dəyişdirin"alət qutusundakı lentdə yerləşir Bağlantılar. Qeyd etmək lazımdır ki, mövcud kitabda digər sənədlərə bağlantılar yoxdursa, bu düymə fəaliyyətsizdir.
- Bağlantı dəyişdirmə pəncərəsi başlayır. Əlaqəni kəsmək istədiyimiz faylı əlaqəli kitabların siyahısından seçirik (bir neçə varsa). Düyməni vurun Linki kəsin.
- Əlavə hərəkətlərin nəticələri barədə xəbərdarlıq olan bir məlumat pəncərəsi açılır. Nə edəcəyinizə əminsinizsə, düyməni vurun "Əlaqələri kəsmək".
- Bundan sonra, hazırkı sənəddə göstərilən fayla bağlantılar statik dəyərlərlə əvəz olunacaq.
Metod 2: Dəyərlər daxil edin
Ancaq yuxarıda göstərilən metod yalnız iki kitab arasındakı bütün əlaqələri tamamilə kəsmək lazımdırsa uyğundur. Eyni fayl içərisində olan əlaqəli cədvəlləri ayırmaq lazımdırsa nə etməli? Bunu məlumatları kopyalayaraq sonra dəyərlər ilə bir yerə yapışdıraraq edə bilərsiniz. Yeri gəlmişkən, eyni şəkildə, fayllar arasındakı ümumi əlaqəni pozmadan müxtəlif kitabların fərdi məlumat silsilələri arasındakı əlaqəni kəsə bilərsiniz. Bu metodun praktikada necə işlədiyini görək.
- Digər masaya bağlantını silmək istədiyimiz aralığı seçin. Bunun üzərinə sağ siçan düyməsini vururuq. Açılan menyuda seçin Surəti. Bu hərəkətlərin əvəzinə isti düymələrin alternativ birləşməsini yaza bilərsiniz Ctrl + C.
- Bundan əlavə, seçimi eyni fraqmentdən çıxarmadan yenidən üzərinə sağ vurun. Bu dəfə tədbirlər siyahısında, simge vurun "Dəyərlər"alətlər qrupunda olan Seçimlər daxil edin.
- Bundan sonra, seçilmiş diapazondakı bütün bağlantılar statik dəyərlərlə əvəz olunacaq.
Gördüyünüz kimi, Excel-də bir neçə masanı bir-birinə bağlamağın yolları və vasitələri var. Eyni zamanda, cədvəlli məlumatlar digər vərəqlərdə və hətta müxtəlif kitablarda ola bilər. Gerekirse bu əlaqə asanlıqla pozula bilər.
