Mündəricat
Excel-də ölçüləri dəyişdirilə bilən, yəni işin gedişində sətirlərin (sütunların) sayı arta və ya azala bilən verilənləri olan cədvəlləriniz varmı? Cədvəl ölçüləri "üzən" olarsa, bu anı daim izləməli və düzəltməli olacaqsınız:
- cədvəlimizə istinad edən hesabat düsturlarındakı bağlantılar
- cədvəlimizə uyğun olaraq qurulmuş pivot cədvəllərinin ilkin diapazonları
- cədvəlimizə uyğun olaraq qurulmuş diaqramların ilkin diapazonları
- cədvəlimizi məlumat mənbəyi kimi istifadə edən açılan menyular üçün diapazonlar
Bütün bunlar bütövlükdə darıxmağınıza imkan verməyəcək 😉
Ölçüsü avtomatik olaraq verilənlərin sətir və sütunlarının faktiki sayına uyğunlaşacaq dinamik "rezin" diapazonu yaratmaq daha rahat və düzgün olacaq. Bunu həyata keçirməyin bir neçə yolu var.
Metod 1. Ağıllı masa
Hüceyrə aralığını vurğulayın və tabdan seçin Əsas səhifə – Cədvəl kimi format (Ev – Cədvəl kimi format):
Yan təsir olaraq masaya əlavə olunan zolaqlı dizayna ehtiyacınız yoxdursa, görünən nişanda onu söndürə bilərsiniz. Konstruktor (dizayn). Bu şəkildə yaradılmış hər bir cədvəl nişanın eyni yerində daha rahat olanı ilə əvəz edilə bilən bir ad alır. Konstruktor (dizayn) sahəsində Cədvəlin adı (Cədvəl adı).
İndi biz “ağıllı masamıza” dinamik keçidlərdən istifadə edə bilərik:
- Cədvəl 1 – başlıq sətirindən başqa bütün cədvələ keçid (A2:D5)
- Cədvəl 1[#Hamı] – bütün cədvələ keçid (A1:D5)
- Cədvəl 1[Piter] – birinci xana başlığı olmayan aralıq sütununa istinad (C2:C5)
- Cədvəl 1[#Başlıqlar] – sütunların adları ilə “başlığa” keçid (A1:D1)
Bu cür istinadlar düsturlarda əla işləyir, məsələn:
= SUM (Cədvəl 1[Moskva]) – “Moskva” sütunu üçün məbləğin hesablanması
or
=VPR(F5;Cədvəl 1;3;0) – F5 xanasından ayı cədvəldə axtarın və bunun üçün Sankt-Peterburq məbləğini verin (VLOOKUP nədir?)
Bu cür keçidlərdən pivot cədvəlləri yaradan zaman nişanda seçim etməklə uğurla istifadə etmək olar Daxil et – Pivot Cədvəl (Daxil et – Pivot Cədvəl) və məlumat mənbəyi kimi smart cədvəlin adının daxil edilməsi:
Əgər belə bir cədvəlin bir fraqmentini seçsəniz (məsələn, ilk iki sütun) və istənilən növ diaqram yaratsanız, yeni sətirlər əlavə edərkən onlar avtomatik olaraq diaqrama əlavə olunacaqlar.
Açılan siyahılar yaratarkən, ağıllı masa elementlərinə birbaşa keçidlərdən istifadə edilə bilməz, lakin siz taktiki hiylədən istifadə edərək bu məhdudiyyətdən asanlıqla keçə bilərsiniz - funksiyadan istifadə edin DOLAYI (Birbaşa), mətni keçidə çevirir:
Bunlar. mətn sətri şəklində ağıllı cədvələ keçid (dırnaq işarələrində!) tam hüquqlu bir keçidə çevrilir və açılan siyahı onu normal olaraq qəbul edir.
Metod 2: Dinamik adlandırılmış diapazon
Məlumatlarınızı ağıllı masaya çevirmək nədənsə arzuolunmazdırsa, bir az daha mürəkkəb, lakin daha incə və çox yönlü bir üsuldan istifadə edə bilərsiniz - Excel-də cədvəlimizə istinad edən dinamik adlandırılmış diapazon yaradın. Sonra, ağıllı cədvəldə olduğu kimi, yaradılmış diapazonun adından istənilən formullarda, hesabatlarda, diaqramlarda və s.-də sərbəst istifadə edə bilərsiniz. Sadə bir nümunə ilə başlayaq:
Tapşırıq: şəhərlərin siyahısına istinad edəcək dinamik adlandırılmış diapazon yaradın və yeni şəhərlər əlavə edərkən və ya onları silərkən avtomatik olaraq uzanan və ölçüsünü kiçildin.
İstənilən versiyada mövcud olan iki daxili Excel funksiyasına ehtiyacımız olacaq POICPOZ (MAÇ) diapazonun son xanasını müəyyən etmək üçün və İNDEKSİ (İNDEKS) dinamik əlaqə yaratmaq.
MATCH istifadə edərək son xananın tapılması
MATCH(axtarma_dəyəri, diapazon, uyğunluq_növü) – diapazonda (sətir və ya sütun) verilmiş dəyəri axtaran və onun tapıldığı xananın sıra nömrəsini qaytaran funksiya. Məsələn, MATCH(“Mart”;A1:A5;0) düsturu nəticədə 4 rəqəmini qaytaracaq, çünki “Mart” sözü A1:A5 sütununun dördüncü xanasında yerləşir. Son funksiya arqumenti Match_Type = 0 o deməkdir ki, biz dəqiq uyğunluq axtarırıq. Bu arqument göstərilməyibsə, onda funksiya ən yaxın ən kiçik dəyər üçün axtarış rejiminə keçəcək - bu, massivimizdə sonuncu işğal edilmiş xananı tapmaq üçün uğurla istifadə edilə bilən şeydir.
Hiylənin mahiyyəti sadədir. MATCH yuxarıdan aşağı diapazonda xanaları axtarır və nəzəri olaraq verilmiş birinə ən yaxın ən kiçik dəyəri tapanda dayanmalıdır. İstədiyiniz dəyər kimi cədvəldə mövcud olan hər hansı bir dəyərdən açıq-aydın böyük olan dəyəri göstərsəniz, MATCH cədvəlin ən sonuna çatacaq, heç nə tapmayacaq və sonuncu doldurulmuş xananın ardıcıl nömrəsini verəcəkdir. Və bizə lazımdır!
Əgər massivimizdə yalnız ədədlər varsa, o zaman cədvəldəki hər hansı bir rəqəmdən açıq şəkildə böyük olan rəqəmi istədiyiniz dəyər kimi təyin edə bilərik:
Zəmanət üçün siz 9E + 307 (9-nin gücünə 10 dəfə 307, yəni 9 sıfırla 307) rəqəmindən istifadə edə bilərsiniz - Excel-in prinsipcə işləyə biləcəyi maksimum rəqəm.
Sütunumuzda mətn dəyərləri varsa, o zaman mümkün olan ən böyük ədədin ekvivalenti olaraq REPEAT("i", 255) konstruksiyasını - 255 hərfdən ibarət "i" mətn sətrini - sonuncu hərfi daxil edə bilərsiniz. Əlifba. Excel həqiqətən axtarış zamanı simvol kodlarını müqayisə etdiyinə görə, cədvəlimizdəki hər hansı mətn texniki olaraq belə uzun "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" sətirindən "kiçik" olacaq:
INDEX istifadə edərək link yaradın
Cədvəldəki sonuncu boş olmayan elementin yerini bildiyimizə görə, bütün diapazonumuza bir keçid yaratmaq qalır. Bunun üçün funksiyadan istifadə edirik:
İNDEKS(aralıq; sətir_sayı; sütun_num)
O, xananın məzmununu sətir və sütun nömrələri üzrə diapazondan verir, məsələn, əvvəlki metoddan şəhər və aylarla cədvəlimizdə =INDEX(A1:D5;3;4) funksiyası 1240 – məzmun verəcəkdir. 3-cü sıradan və 4-cü sütundan, yəni D3 xanalarından. Yalnız bir sütun varsa, onun nömrəsi buraxıla bilər, yəni INDEX(A2:A6;3) düsturu sonuncu ekran görüntüsündə “Samara” verəcəkdir.
Və tamamilə aydın olmayan bir nüans var: əgər INDEX həmişəki kimi = işarəsindən sonra hüceyrəyə daxil edilmirsə, iki nöqtədən sonra diapazona istinadın son hissəsi kimi istifadə olunursa, o, artıq vermir. hüceyrənin məzmunu, ancaq ünvanı! Beləliklə, $A$2:INDEX($A$2:$A$100;3) kimi bir düstur çıxışda A2:A4 diapazonuna istinad verəcəkdir.
Siyahının sonunu dinamik şəkildə müəyyən etmək üçün INDEX-in içərisinə daxil etdiyimiz MATCH funksiyası burada işə düşür:
=$A$2:İNDEKS($A$2:$A$100; MATCH(REP("I";255);A2:A100))
Adlandırılmış diapazon yaradın
Hamısını bir bütövlükdə toplamaq qalır. Nişan açın formula (Formullar) Və düyməsini vurun Adı meneceri (Ad Meneceri). Açılan pəncərədə düyməni basın Yaratmaq (yeni), sahəyə diapazonumuzun adını və düsturumuzu daxil edin Silsilə (İstinad):
Klikləmək qalır OK və hazır diapazon istənilən formullarda, açılan siyahılarda və ya diaqramlarda istifadə edilə bilər.
- Cədvəlləri əlaqələndirmək və dəyərləri axtarmaq üçün VLOOKUP funksiyasından istifadə edin
- Avtomatik doldurulan açılan siyahını necə yaratmaq olar
- Böyük miqdarda məlumatı təhlil etmək üçün pivot cədvəlini necə yaratmaq olar