Excel-də müəyyən vəzifələri yerinə yetirərkən bəzən bir-birinizlə əlaqəli bir neçə masa ilə məşğul olmalısınız. Yəni, bir cədvəldən verilən məlumatlar digərinə çəkilir və dəyişdikdə, bütün əlaqədar masa aralığındaki dəyərlər yenidən hesablanır.
Bağlı masalar böyük miqdarda məlumatların işlənməsi üçün çox faydalıdır. Bütün məlumatları bir masa içində yerləşdirmək çox rahat deyil və əgər birbaşa deyilsə. Belə obyektlərlə işləmək və onları axtarmaq çətindir. Bu problem, aidiyyəti olan cədvəlləri, onların arasında bölüşdürülən məlumatları aradan qaldırmaq üçün nəzərdə tutulur, eyni zamanda bir-biri ilə bağlıdır. Əlaqədar masa aralıkları yalnız bir səhifədə və ya bir kitabda deyil, ayrıca ayrıca kitablarda (fayllarda) yerləşdirilə bilər. Praktikada son iki variant ən çox istifadə olunur, çünki bu texnologiyanın məqsədi məlumatların yığılmasından uzaqlaşmaq və onları eyni səhifəyə yığmaq, problemi əsaslı şəkildə həll etmir. Bu cür məlumatların idarə edilməsi ilə necə yaradılacağını və necə işlədiyini öyrənək.
Əlaqəli cədvəllərin yaradılması
Birincisi, müxtəlif masa aralıkları arasında bir əlaqə yaratmağın mümkünlüyü sualına diqqət yetirək.
Metod 1: Masaları bir formulla birbaşa əlaqələndirir
Verileri birləşdirmək üçün ən asan yol, digər masa aralığına keçid formullarını istifadə etməkdir. Bu birbaşa bağlanma adlanır. Bu metod intuitivdir, buna görə də cədvəl bir cədvəl sətrindəki məlumatlara istinadlar yaradaraq demək olar ki, eyni şəkildə həyata keçirilir.
Bir məsələnin birbaşa bağlanması yolu ilə bir bürc yarada bilərik. İki təbəqədə iki masa var. Bir cədvəldə əmək haqqı işçi nisbətinin hamısı üçün vahid bir dərəcə ilə çarparaq bir formula istifadə edilir.
İkinci hesabatda, əmək haqqı olan işçilərin siyahısı olan cədvəlli bir sıra var. Hər iki halda da işçilərin siyahısı eyni qaydada təqdim olunur.
İkinci hesabatın qiymətləri haqqında məlumatlar ilk növbədə müvafiq hüceyrələrə çəkilməlidir.
- İlk hesabatda birinci sütun hüceyrəsini seçin. "Bahis". Onun izini qoyduq "=". Sonra, etiketi basın "Səth 2"Excel arabiriminin sol tərəfində vəziyyət barının üstündə olan.
- Sənədin ikinci sahəsinə hərəkət edir. Sütundakı ilk hüceyrəni basın. "Bahis". Sonra düyməni basın. Daxil edin işarə daha əvvəl müəyyən edilmiş hüceyrədə məlumat girişi yerinə yetirmək üçün klaviatura üzərində bərabərdir.
- Sonra birinci hesabata avtomatik keçid var. Gördüyünüz kimi, ikinci cədvəldən ilk işçinin dərəcəsi müvafiq hüceyrəyə çəkilir. İmleci bahsi olan hüceyrəyə yerləşdirərək, adi formulun ekranda məlumatları göstərmək üçün istifadə edildiyini görürük. Lakin məlumatın göstərildiyi hüceyrənin koordinatlarından əvvəl bir ifadə var "Sheet2!"olan sənədin ərazisinin adını göstərir. Davamızdakı ümumi formul aşağıdadır:
= Sheet2! 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şçinin vəzifəsini yerinə yetirdiyimiz kimi eyni şəkildə həyata keçirilə bilər, lakin işçilərin həm siyahıları eyni qaydada təşkil edildikdə, məsələ sadələşdirilib və həllini sürətləndirə bilər. Bu, sadəcə aşağıdakı formulun kopyalanması ilə edilə bilər. Excel-in əlaqələri default olaraq nisbi olduğundan, kopyalanırken, dəyərlər bizə lazım olanı dəyişir. Kopyalama proseduru özü doldurma markerindən istifadə edilə bilər.
Belə ki, kursoru elementin aşağı sağ hissəsində formula ilə qoyun. Bundan sonra, kursor qara xaç formasında doldurulmalıdır. Siçanın sol siçanının qısqacını yerinə yetiririk və imleci sütunun ən altına sürükləyirik.
- Həmin sütundakı bütün məlumatlar Səth 2 masaya çəkildi Sheet 1. Məlumat dəyişir Səth 2 avtomatik olaraq ilk olaraq dəyişəcəklər.
Metod 2: INDEX - MATCH operatoru dəstini istifadə edin
Nə cədvəlli dizilədəki işçilərin siyahısı eyni qaydada təşkil edilsə? Bu halda, əvvəllər də qeyd edildiyi kimi, seçimlərdən biri əl ilə əlaqələndirilməli olan hər bir hüceyrə arasında əlaqə yaratmaqdır. Lakin bu yalnız kiçik masalar üçün uygundur. Kütləvi diapazonlar üçün, bu seçim ən yaxşı halda tətbiq etmək üçün çox vaxt aparacaq və ən pis halda - praktikada heç də mümkün olmayacaqdır. Ancaq bu problemi bir qrup operatorla həll edə bilərsiniz INDEKS - MATCH. Bunu bir əvvəlki üsulda müzakirə edilən cədvəl aralığındaki məlumatların əlaqələndirilməsi yolu ilə necə ediləcəyini nəzər salaq.
- Sütundakı ilk maddəni seçin. "Bahis". Gedin Function Sihirbazısimvolu tıklayarak "Əlavə funksiyası".
- Daxildir Function sihirbazı bir qrupda "Linklər və seriallar" adı tapmaq və seçin INDEKS.
- Bu operator iki forma var: dizilerle işləmək üçün bir forma və bir istinad. Bizim halda, ilk seçim tələb olunur, buna görə də növbəti pəncərədə açılacaq bir forma seçmək üçün seçirik və düyməni basın "OK".
- Operator dəlilləri pəncərəsi işə salınıb. INDEKS. Göstərilən funksiyanı tapşırıq göstərilən nömrə ilə xəttin seçilmiş aralığında olan dəyəri göstərməkdir. Ümumi operator formula INDEKS bu:
= INDEX (array; line_number; [column_number])"Array" - göstərilən simli sayına görə informasiyanı çıxardıqları aralığın ünvanını ehtiva edən arqument.
"Çıxış nömrəsi" - bu xəttin özü sayılan arqument. Çətin nömrəsinin bütün sənədə nisbətən müəyyən edilməməsi, ancaq seçilmiş array ilə müqayisəli olması vacibdir.
"Sütun nömrəsi" - Arqument opsiyoneldir. Xüsusi problemimizi həll etmək üçün biz onu istifadə etməyəcəyik və buna görə də mahiyyətini ayrı-ayrılıqda təsvir etmək lazım deyil.
Kursoru sahəyə qoyun "Array". Bundan sonra get Səth 2 və sol siçan düyməsini basıb sütunun bütün məzmununu seçin "Bahis".
- Koordinatlar operatorun pəncərəsində göründükdən sonra, kursoru sahəyə qoyun "Çıxış nömrəsi". Biz bu mübahisəni operatordan istifadə edəcəyik MATCH. Buna görə, funksiya xəttinin solunda yerləşən üçbucağı basın. Son zamanlarda istifadə edilən operatorların siyahısı açılır. Əgər onların adını tapsanız "MATCH"sonra üzərinə basa bilərsiniz. Əks təqdirdə, siyahıda ən sonuncu maddəyə - "Digər xüsusiyyətlər ...".
- Standart pəncərə başlayır. Fəaliyyət ustaları. Eyni qrupda ona gedin. "Linklər və seriallar". Siyahıda bu dəfə maddəni seçin "MATCH". Düyməni bir klikləyin. "OK".
- Operator penceresi argümanlarını aktivləşdirir MATCH. Göstərilən funksiya özünün adı ilə xüsusi bir sıra içində bir ədədin sayını göstərməyi nəzərdə tutur. Bu fürsət sayəsində, funksiya üçün müəyyən bir dəyərin sıra nömrəsini hesablayacağıq. INDEKS. Sintaksis MATCH kimi təqdim olunur:
= MATCH (axtarış dəyəri; axtarış array; [match_type])"Arzu olunan dəyər" - yerləşdiyi üçüncü tərəf aralığının adını və ya ünvanını ehtiva edən arqument. Bu adın hesablanması lazım olan hədəf aralığında mövqeyi. Bizim halda, ilk arqument hücum istinadları olacaq Sheet 1burada çalışanların adları yer alır.
"Göründüyü array" - müəyyən bir dəyərin onun mövqeyini müəyyənləşdirmək üçün axtarış etdiyi bir sıra bir keçid təmsil edən bir arqument. Biz bu rol ünvan sütunu oynayacağıq "Adı on Səth 2.
"Xəritəçəkmə Tipi" - isteğe bağlı bir arqument, lakin, əvvəlki bəyanatdan fərqli olaraq, bu isteğe bağlı arqumentə ehtiyacımız var. Bu, operatorun sıra ilə istədiyiniz qiymətə uyğunlaşdıracağını göstərir. Bu arqument üç dəyərdən birinə malik ola bilər: -1; 0; 1. Ardıcıl olmayan dizilər üçün, seçimi seçin "0". Bu seçim bizim vəziyyətimizə uyğun gəlir.
Beləliklə, arqumentlər pəncərəsinin sahələrini doldurmağa başlayaq. Kursoru sahəyə qoyun "Arzu olunan dəyər", sütunun ilk hüceyrəsinə vurun "Ad" on Sheet 1.
- Koordinatlar göstərildikdən sonra, kursoru sahəyə qoyun "Göründüyü array" və qısa yola getmək "Səth 2"status çubuğunun yuxarıdakı Excel penceresinin altındakı yer. Sol siçan düyməsini basıb saxlayın və sütundakı bütün hüceyrələri vurun. "Ad".
- Sahəsində koordinatları göstərildikdən sonra "Göründüyü array"sahəyə gedin "Xəritəçəkmə Tipi" nömrəni klaviaturadan təyin edin "0". Bundan sonra təkrar sahəyə qayıdırıq. "Göründüyü array". Əslində, əvvəlki üsulla etdiyimiz kimi formulun surətini çıxaracağıq. Ünvanların bir nömrələnməsi olacaq, ancaq baxılan array koordinatlarını düzəltməliyik. Bu keçməməlidir. Kursorun koordinatlarını seçin və funksiya düyməsini basın F4. Gördüyünüz kimi koordinatların qarşısında bir dollar əlaməti ortaya çıxdı, yəni qohumluq əlaqəsi mütləq halına gəldi. Sonra düyməni basın "OK".
- Nəticə sütunun ilk hüceyrəsində göstərilir. "Bahis". Lakin kopyalamadan əvvəl başqa bir sahəni, yəni funksiyanın ilk arqumentini düzəltməliyik INDEKS. Bunu etmək üçün formulu olan sütunun elementini seçin və formula barına keçin. Operatorun ilk arqumentini seçin INDEKS (B2: B7) düyməsini basın F4. Gördüyünüz kimi, dollar işarəsi seçilmiş koordinatlara yaxınlaşdı. Düyməni basın Daxil edin. Ümumiyyətlə, formula aşağıdakı forma götürdü:
= İNDEKS (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0)) - İndi doldurmaq markerini istifadə edərək kopyalayabilirsiniz. Daha əvvəl də danışdığımız kimi onu zəng edin və onu masa aralığının sonuna qədər uzat.
- Gördüyünüz kimi, iki əlaqəli masaların sırasına uyğun gəlmədiyinə baxmayaraq, bütün dəyərlər işçilərin adına görə sıxılır. Bu, operatorların birləşməsindən istifadə etməklə əldə edilmişdir INDEKS-MATCH.
Həmçinin baxın:
Excel funksiyası INDEX
Excel-də oyun funksiyası
Metod 3: Riyazi əməliyyatları əlaqəli məlumatlarla yerinə yetirir
Birbaşa məlumatların bağlanması da, yalnız masaların birində digər masa aralıklarında göstərilən dəyərləri göstərməklə yanaşı müxtəlif riyazi əməliyyatlar (əlavə, bölmə, toplama, çarpma və s.) Yerinə yetirmək üçün də imkan verir.
Bunun necə həyata keçirildiyini görək. Bunu edək Səth 3 ümumi müəssisənin əmək haqqı məlumatları işçi dağıdılmadan nümayiş olunacaq. Bunun üçün kadr dərəcələri çəkiləcək Səth 2, funksiyanı istifadə edərək yekunlaşdırın SUM) və formula ilə əmsalla vurulur.
- Ümumi əmək haqqının göstərildiyi hüceyrəni seçin Səth 3. Düyməni basın "Əlavə funksiyası".
- Pəncərəni başlamalıdır Fəaliyyət ustaları. Qrupa gedin "Riyaziyyat" və orada adı seçin "SUMM". Sonra, düyməni basın "OK".
- Function argument penceresine keçir SUMseçilmiş nömrələrin cəmini hesablamaq üçün nəzərdə tutulmuşdur. Aşağıdakı sözdiziminə malikdir:
= SUM (nömrə 1; nömrə 2; ...)Pəncərədəki sahələr göstərilən funksiyanın argumentlərinə cavab verir. Onların sayı 255 nəfərə çatmasına baxmayaraq, məqsədimiz üçün yalnız birimiz kifayətdir. Kursoru sahəyə qoyun "1 nömrə". Etiketi basın "Səth 2" vəziyyət barının üstündə.
- Kitabın istənilən hissəsinə köçürdükdən sonra, toplanmalı olan sütunu seçin. Siçan sol siçan tutaraq imleci edirik. Gördüyünüz kimi, seçilmiş ərazinin koordinatları dərhal arqument penceresinin sahəsində göstərilir. Sonra düyməni basın. "OK".
- Bundan sonra avtomatik olaraq hərəkət edirik Sheet 1. Gördüyünüz kimi, işçilərin əmək haqqının ümumi məbləği artıq müvafiq elementdə göstərilir.
- Amma bu hamısı deyil. Xatırladaq ki, əmək haqqı məzənnənin dəyərini əmsalla vuraraq hesablanır. Buna görə də, biz yenidən toplanan dəyərin olduğu hüceyrəni seçirik. Bundan sonra formul barına gedin. Onun formuluna çarpma işarəsi əlavə edirik (*) və sonra əmsalın yerləşdiyi elementə basın. Hesabı yerinə yetirmək üçün basın Daxil edin klaviaturada. Gördüyünüz kimi, proqram müəssisənin ümumi əmək haqqını hesablayır.
- Geri qayıdın Səth 2 və hər hansı bir işçinin dərəcəsinin ölçüsünü dəyişdirin.
- Bundan sonra yenidən ümumi məbləğlə səhifəyə keçin. Gördüyünüz kimi, cədvəldə dəyişikliklərə görə ümumi ödənişin nəticəsi avtomatik olaraq yenidən hesablanıb.
Metod 4: xüsusi əlavə
Masa dizilerini Excel-də xüsusi bir keçid ilə əlaqələndirə bilərsiniz.
- Başqa bir masaya "sıxılmış" olmaq üçün lazım olan dəyərləri seçin. Bizim halda, bu sütun üçündür. "Bahis" on Səth 2. Sağ fare düyməsiylə seçilmiş fraqmenti basın. Açılan siyahıda maddəni seçin "Kopyala". Alternativ birləşmə kombinasiyasıdır Ctrl + C. Bu hərəkətdən sonra Sheet 1.
- Kitabın istənilən yerinə keçərək, dəyərləri çəkmək istədiyiniz hüceyrələri seçirik. Bizim vəziyyətimizdə bu bir sütundur. "Bahis". Sağ fare düyməsiylə seçilmiş fraqmenti basın. Toolbar-da kontekst menyusunda "Daxiletmə Seçimləri" simvol basın "Əlavə et".
Alternativ bir alternativ var. Yeri gəlmişkən, bu, Excel-in köhnə versiyaları üçün yeganədir. Kontekst menyusundan imleci maddəyə daşıyın "Xüsusi Yapıştır". Açılan əlavə menyuda eyni adlı elementi seçin.
- Bundan sonra xüsusi bir əlavə penceresi açılır. Düyməni basırıq "Əlavə et" hüceyrənin aşağı sol küncündə.
- Seçdiyiniz variantlardan biri, bir masa aralığından gələn dəyərlər başqa birinə daxil ediləcəkdir. Mənbəyədəki məlumatları dəyişdiyiniz zaman, avtomatik olaraq əlavə edilən aralıda dəyişəcəklər.
Dərs: Excel-də xüsusi yapışdırın
Metod 5: Bir neçə kitabdakı masalar arasında əlaqə
Əlavə olaraq, müxtəlif kitablarda yerlər arasında əlaqəni təşkil edə bilərsiniz. Bu xüsusi insert vasitədir. Dəyişikliklər bir kitabın əraziləri arasında deyil, fayllar arasında meydana gəlməməsi üçün hərəkətlər əvvəlki üsulla nəzərdən keçirdiklərimizə tamamilə bənzəyir. Təbii ki, bütün kitablar açıq olmalıdır.
- Başqa bir kitaba köçürmək istədiyiniz məlumatların çeşidini seçin. Sağ siçan düyməsini basın və açılır menyudan mövqeyi seçin "Kopyala".
- Sonra biz bu məlumatların daxil edilməsi lazım olan kitaba keçirik. İstədiyiniz aralığı seçin. Sağ siçan düyməsini basın. Qrupdakı kontekst menyusunda "Daxiletmə Seçimləri" bir maddə seçin "Əlavə et".
- Bundan sonra dəyərlər əlavə olunacaq. Mənbə kitabında olan məlumatları dəyişdikdə, iş kitabından cədvəlli array onları avtomatik olaraq çıxarır. Hər iki kitabın bunun üçün açıq olması lazım deyil. Yalnız bir iş kitabını açmaq üçün kifayətdir və dəyişikliklər daha əvvəl edildiyi təqdirdə avtomatik olaraq bağlanan sənəddən məlumatları çəkəcəkdir.
Lakin qeyd etmək lazımdır ki, bu halda əlavə yerləşdirmə dəyişməz bir sıra şəklində veriləcəkdir. Daxil olunan hər hansı bir hüceyrəni dəyişməyə cəhd etsəniz, bu mesaj mümkün deyil, bunun mümkün olmadığını bildirirsiniz.
Digər bir kitabla əlaqəli bir sıra dəyişikliklər yalnız linki pozaraq edilə bilər.
Masaların arasına bağlanması
Bəzən masa aralığının arasındakı əlaqəni pozmaq lazımdır. Bunun səbəbi, yuxarıda təsvir olunduğu kimi, başqa bir kitabdan daxil edilmiş bir sıra dəyişdirmək və ya istifadəçinin bir masa içindəki məlumatları bir-birindən avtomatik olaraq yenilənməsini istəmədikləri üçün dəyişdirmək istədiyiniz zaman ola bilər.
Metod 1: kitablar arasında əlaqəni kəsmək
Həqiqi bir əməliyyat aparmaqla bütün hüceyrələrdəki kitablar arasındakı əlaqəni poza bilərsən. Eyni zamanda, hüceyrələrdə olan məlumatlar qalacaq, ancaq artıq digər sənədlərə asılı olmayan statik qeyri-yenilənmiş dəyərlər olacaqdır.
- Digər fayllardan götürülmüş dəyərlərdə olan kitabda, nişana keçin "Məlumat". Simgeyi basın "Bağlantıları redaktə et"alətlərin blokundan tape üzərində yerləşir "Əlaqələr". Qeyd edək ki, mövcud kitabda digər fayllara keçid olmadığı təqdirdə, bu düymə qeyri-aktivdir.
- Bağlantıların dəyişdirilməsi üçün pəncərə açılır. Əlaqədar kitabların siyahısından (əgər bir neçə varsa) əlaqəni pozmaq istədikləri faylları seçin. Düyməni basın "Bağlantıyı kəsin".
- Daha bir hərəkətin nəticəsi barədə xəbərdarlıq olduğu bir informasiya pəncərəsi açılır. Nə edəcəyinizə əmin olsanız, düyməni basın. "Bağları kəsin".
- Bundan sonra, mövcud sənəddə göstərilən faylın bütün istinadları statik dəyərlərlə əvəz olunacaq.
Metod 2: Dəyərlər əlavə edin
Lakin yuxarıda göstərilən metod yalnız iki kitab arasındakı bütün əlaqələri tamamilə ləğv etmək lazımdır. Eyni faylda olan əlaqəli cədvəlləri ayırmaq istəyirsinizsə nə etməli? Bunu məlumatları kopyalayarak və sonra dəyərləri eyni yerdə yerləşdirməklə edə bilərsiniz.Yeri gəlmişkən, eyni metod fayllar arasında ümumi əlaqəni pozmadan fərqli kitabların ayrı-ayrı məlumat aralıkları arasındakı əlaqəni pozmaq üçün istifadə edilə bilər. Bu metodun praktikada necə işlədiyini görək.
- Bağlantıyı başqa bir tabloya silmək istədiyimiz aralığı seçin. Sağ siçan düyməsini basın. Açılan menyuda maddə seçin "Kopyala". Bu hərəkətlər əvəzinə, alternativ bir isti birləşməni yaza bilərsiniz. Ctrl + C.
- Sonra seçimi eyni hissədən çıxarmadan, biz onu yenidən sağ siçan düyməsinə basdıq. Bu dəfə hərəkətlər siyahısında biz simvol basın "Dəyərlər"bir qrup vasitəyə yerləşdirilir "Daxiletmə Seçimləri".
- Bundan sonra, seçilmiş intervaldakı bütün bağlantılar statik dəyərlərlə əvəz olunacaq.
Gördüyünüz kimi, Excel bir neçə masaları bir-birinə bağlamaq üçün üsullar və vasitələrə malikdir. Bu halda, cədvəl məlumatları digər təbəqələrdə və hətta müxtəlif kitablarda ola bilər. Lazım olsa, bu əlaqə asanlıqla sınıq ola bilər.
