Hesabat və məlumatların emalı ilə məşğul olanlar üçün Excel həyat hikləri

2024 Müəllif: Malcolm Clapton | [email protected]. Son dəyişdirildi: 2024-01-13 00:40

Bu yazıda Mann, İvanov və Ferber Nəşriyyatının baş direktorunun köməkçisi Renat Şaqabutdinov Excel-in bəzi gözəl həyat hiklərini paylaşır. Bu məsləhətlər müxtəlif hesabatların hazırlanması, məlumatların işlənməsi və təqdimatların yaradılması ilə məşğul olan hər kəs üçün faydalı olacaq.

Bu məqalə Excel-də işinizi asanlaşdırmaq üçün sadə üsullardan ibarətdir. Onlar xüsusilə idarəetmə hesabatları ilə məşğul olanlar, 1C-dən yükləmələr və digər hesabatlar əsasında müxtəlif analitik hesabatlar hazırlayanlar, idarəetmə üçün onlardan təqdimatlar və diaqramlar hazırlayanlar üçün faydalıdır. Mən mütləq yenilik kimi görünmürəm - bu və ya digər formada bu üsullar yəqin ki, forumlarda müzakirə olunub və ya məqalələrdə qeyd olunub.

İstədiyiniz dəyərlər cədvəlin birinci sütununda deyilsə, VLOOKUP və HLOOKUP-a sadə alternativlər: LOOKUP, INDEX + SEARCH

VLOOKUP və HLOOKUP funksiyaları yalnız istədiyiniz dəyərlər məlumat əldə etməyi planlaşdırdığınız cədvəlin birinci sütununda və ya sətirində olduqda işləyir.

Əks halda, iki seçim var:

1. LOOKUP funksiyasından istifadə edin.

   Onun aşağıdakı sintaksisi var: LOOKUP (axtaran_dəyər; axtarış_vektoru; nəticə_vektoru). Lakin onun düzgün işləməsi üçün view_vector diapazonunun dəyərləri artan qaydada sıralanmalıdır:

   

2. MATCH və INDEX funksiyalarının birləşməsindən istifadə edin.

   MATCH funksiyası massivdəki elementin sıra nömrəsini qaytarır (onun köməyi ilə siz axtarılan elementin cədvəlin hansı cərgəsində olduğunu tapa bilərsiniz), INDEX funksiyası isə verilmiş nömrəyə malik massiv elementini qaytarır (biz bunu öyrənəcəyik). MATCH funksiyasından istifadə etməklə).

   

   Funksiya sintaksisi:

   • SEARCH (search_value; search_array; match_type) - bizim vəziyyətimiz üçün bizə uyğun gələn "dəqiq uyğunluq" növü lazımdır, o, 0 rəqəminə uyğundur.

   • İNDEKS (massiv; sətir_nömrəsi; [sütun_nömrəsi]). Bu halda, massiv bir sətirdən ibarət olduğu üçün sütun nömrəsini göstərməyə ehtiyac yoxdur.

Siyahıdakı boş xanaları necə tez doldurmaq olar

Tapşırıq sütundakı xanaları yuxarıdakı dəyərlərlə doldurmaqdır (belə ki, mövzu yalnız mövzuya aid kitablar blokunun birinci cərgəsində deyil, cədvəlin hər sətirində olsun):

"Mövzu" sütununu seçin, "Ev" qrupunda lentə vurun, "Tap və Seç" düyməsini → "Hüceyrələr qrupunu seçin" → "Boş hüceyrələr" və formula daxil etməyə başlayın (yəni bərabər qoyun işarələyin) və sadəcə olaraq klaviaturanızda yuxarı oxu klikləməklə yuxarıdakı xanaya baxın. Bundan sonra Ctrl + Enter düyməsini basın. Bundan sonra, alınan məlumatları dəyərlər kimi saxlaya bilərsiniz, çünki düsturlara artıq ehtiyac yoxdur:

Düsturdakı səhvləri necə tapmaq olar

Düsturun ayrıca hissəsinin hesablanması

Mürəkkəb düsturu başa düşmək üçün (burada başqa funksiyalar funksiya arqumentləri kimi istifadə olunur, yəni bəzi funksiyalar digərlərində yuvalanır) və ya ondakı səhvlərin mənbəyini tapmaq üçün çox vaxt onun bir hissəsini hesablamaq lazımdır. İki asan yol var:

1. Düsturun bir hissəsini düz düstur sətrində hesablamaq üçün həmin hissəni seçin və F9 düyməsini basın:

   

   Bu nümunədə SEARCH funksiyası ilə bağlı problem var idi - orada arqumentlər dəyişdirildi. Xatırlamaq lazımdır ki, funksiyanın hissəsinin hesablanmasını ləğv etməsəniz və Enter düyməsini sıxsanız, hesablanmış hissə nömrə olaraq qalacaq.

2. Lentdəki Formulalar qrupunda Düsturu Hesabla düyməsini klikləyin:

   

   Görünən pəncərədə düsturu addım-addım hesablaya və xətanın hansı mərhələdə və hansı funksiyada (əgər varsa) baş verdiyini müəyyən edə bilərsiniz:

   

Düsturun nədən asılı olduğunu və ya istinad etdiyini necə müəyyən etmək olar

Düsturun hansı hüceyrələrdən asılı olduğunu müəyyən etmək üçün lentdəki Formulalar qrupunda Təsir edən hüceyrələr düyməsini klikləyin:

Hesablama nəticəsinin nədən asılı olduğunu göstərən oxlar görünür.

Şəkildə qırmızı rənglə vurğulanan simvol göstərilirsə, düstur digər vərəqlərdəki və ya digər kitablardakı xanalardan asılıdır:

Bunun üzərinə klikləməklə, təsir edən hüceyrələrin və ya diapazonların harada yerləşdiyini dəqiq görə bilərik:

"Hüceyrələrə təsir" düyməsinin yanında eyni şəkildə işləyən "Asılı hüceyrələr" düyməsi var: düsturla aktiv xanadan ondan asılı olan xanalara oxları göstərir.

Eyni blokda yerləşən "Oxları sil" düyməsi bir anda təsir edən hüceyrələrə, asılı xanalara oxları və ya hər iki növ oxu silməyə imkan verir:

Çox sayda vərəqdən hüceyrə dəyərlərinin cəmini (sayı, orta) necə tapmaq olar

Deyək ki, sizdə əlavə etmək, saymaq və ya başqa şəkildə emal etmək istədiyiniz data ilə eyni tipli bir neçə vərəq var:

Bunu etmək üçün, nəticəni görmək istədiyiniz xanaya standart düstur daxil edin, məsələn, SUM (SUM) və emal etməli olduğunuz vərəqlərin siyahısından ilk və son vərəqlərin adını göstərin. iki nöqtə ilə ayrılmış arqument:

Siz "Data1", "Data2", "Data3" vərəqlərindən B3 ünvanlı xanaların cəmini alacaqsınız:

Bu ünvanlama yerləşən vərəqlər üçün işləyir ardıcıl olaraq … Sintaksis aşağıdakı kimidir: = FUNCTION (birinci_siyahı: son_siyahı! Aralıq arayışı).

Şablon ifadələrini avtomatik olaraq necə qurmaq olar

Excel-də mətnlə işləməyin əsas prinsiplərindən və bir neçə sadə funksiyadan istifadə edərək, hesabatlar üçün şablon ifadələr hazırlaya bilərsiniz. Mətnlə işləməyin bir neçə prinsipi:

• Biz mətni & işarəsindən istifadə edərək birləşdiririk (onu CONCATENATE funksiyası ilə əvəz edə bilərsiniz, lakin bunun çox mənası yoxdur).
• Mətn həmişə dırnaq içərisində yazılır, mətni olan xanalara istinadlar həmişə olmadan olur.
• "dırnaq işarələri" xidmət simvolunu əldə etmək üçün 32 arqumenti ilə CHAR funksiyasından istifadə edin.

Düsturlardan istifadə edərək şablon söz yaratmaq nümunəsi:

Nəticə:

Bu zaman CHAR funksiyasına əlavə olaraq (sitatları göstərmək üçün) müsbət satış tendensiyası olub-olmamasından asılı olaraq mətni dəyişməyə imkan verən IF funksiyasından və TEXT funksiyasından istifadə olunur. istənilən formatda nömrə. Onun sintaksisi aşağıda təsvir edilmişdir:

MƏTN (dəyər; format)

Format dırnaq işarələrində göstərilib, sanki siz Hüceyrələri Formatla pəncərəsində fərdi formata daxil olursunuz.

Daha mürəkkəb mətnlər də avtomatlaşdırıla bilər. Mənim praktikamda “GÖSTERİCİ” formatında idarəetmə hesabatlarına uzun, lakin müntəzəm şərhlərin avtomatlaşdırılması var idi, əsasən FAKTOR1-in XX ilə artması/azalması, FAKTOR2-nin artımı/azalması səbəbindən plana nisbətən XX azaldı/bahalandı. YY …” dəyişən amillər siyahısı ilə. Əgər siz tez-tez belə şərhlər yazırsınızsa və onların yazılması prosesini alqoritmləşdirmək olarsa, heç olmasa işinizin bir hissəsini xilas edəcək düstur və ya makro yaratmaq bir dəfə baş sındırmağa dəyər.

Birləşdirildikdən sonra hər bir hüceyrədə məlumatları necə saxlamaq olar

Siz xanaları birləşdirdiyiniz zaman yalnız bir dəyər saxlanılır. Excel hüceyrələri birləşdirməyə çalışarkən bu barədə xəbərdarlıq edir:

Müvafiq olaraq, hər bir hüceyrədən asılı olaraq bir düsturunuz varsa, onları birləşdirdikdən sonra işləməyi dayandıracaq (nümunənin 3-4-cü sətirlərində # N / A xətası):

Hüceyrələri birləşdirmək və hələ də onların hər birindəki məlumatları qorumaq üçün (bəlkə də bu mücərrəd nümunədəki kimi bir düsturunuz var; bəlkə siz xanaları birləşdirmək istəyirsiniz, lakin bütün məlumatları gələcək üçün saxlamaq və ya qəsdən gizlətmək istəyirsiniz), vərəqdəki istənilən xananı birləşdirin., onları seçin və sonra formatlaşdırmanı birləşdirmək lazım olan xanalara köçürmək üçün Format Rəssamı əmrindən istifadə edin:

Çoxlu məlumat mənbələrindən pivot necə qurulacaq

Bir anda bir neçə məlumat mənbəyindən pivot qurmaq lazımdırsa, belə bir seçim olan lentə və ya sürətli giriş panelinə "Pivot Cədvəl və Diaqram Sihirbazı" əlavə etməli olacaqsınız.

Bunu aşağıdakı kimi edə bilərsiniz: "Fayl" → "Seçimlər" → "Tez Giriş Alətlər Paneli" → "Bütün Əmrlər" → "Pivot Cədvəl və Diaqram Sihirbazı" → "Əlavə et":

Bundan sonra, lentdə eyni sehrbazı çağıran müvafiq simvol görünəcək:

Bunun üzərinə kliklədiyiniz zaman bir dialoq qutusu görünür:

Bunun içərisində "Bir neçə konsolidasiya diapazonunda" maddəsini seçməlisiniz və "Sonrakı" düyməsini basın. Növbəti addımda siz "Bir səhifə sahəsi yaradın" və ya "Səhifə sahələri yaradın" seçə bilərsiniz. Məlumat mənbələrinin hər biri üçün müstəqil olaraq bir ad tapmaq istəyirsinizsə, ikinci elementi seçin:

Növbəti pəncərədə pivotun qurulacağı bütün diapazonları əlavə edin və onlara adlar verin:

Bundan sonra, sonuncu dialoq qutusunda pivot cədvəl hesabatının harada yerləşdiriləcəyini təyin edin - mövcud və ya yeni vərəqdə:

Pivot cədvəl hesabatı hazırdır. "Səhifə 1" filtrində zəruri hallarda məlumat mənbələrindən yalnız birini seçə bilərsiniz:

B mətnində A mətninin baş vermə sayını necə hesablamaq olar ("MTS SuperMTS tarifi" - MTS abbreviaturasının iki təkrarlanması)

Bu misalda, A sütununda bir neçə mətn sətri var və bizim vəzifəmiz onların hər birinin E1 xanasında yerləşən axtarış mətninin neçə dəfə olduğunu tapmaqdır:

Bu problemi həll etmək üçün aşağıdakı funksiyalardan ibarət kompleks formuldan istifadə edə bilərsiniz:

1. DLSTR (LEN) - mətnin uzunluğunu hesablayır, yeganə arqument mətndir. Misal: DLSTR ("maşın") = 6.
2. ƏVƏZİNƏ - mətn sətirində konkret mətni digəri ilə əvəz edir. Sintaksis: SUBSTITUTE (mətn; köhnə_mətn; yeni_mətn). Nümunə: Əvəzedici (“avtomobil”; “avtomobil”; “”) = “mobil”.
3. UPPER - sətirdəki bütün simvolları böyük hərflə əvəz edir. Yeganə arqument mətndir. Misal: UPPER (“maşın”) = “CAR”. Bizə hərflərə həssas olmayan axtarışlar etmək üçün bu funksiya lazımdır. Axı, UPPER ("avtomobil") = UPPER ("Maşın")

Müəyyən bir mətn sətirinin digərində baş verməsini tapmaq üçün onun orijinalda olan bütün baş vermələrini silməli və nəticədə yaranan sətirin uzunluğunu orijinal ilə müqayisə etməlisiniz:

DLSTR (“Tarif MTS Super MTS”) - DLSTR (“Tarif Super”) = 6

Və sonra bu fərqi axtardığımız simin uzunluğuna bölün:

6 / DLSTR (“MTS”) = 2

Düz iki dəfə "MTS" xətti orijinal birinə daxil edilir.

Bu alqoritmi düsturların dilində yazmaq qalır (gəlin hadisələri axtardığımız mətni “mətn”lə, baş vermələrinin sayı bizi maraqlandıran “axtarılan” ilə işarə edək):

= (DLSTR (mətn) -LSTR (ƏVƏZİNƏ (YUXARI (mətn); UPPER (axtarış), ""))) / DLSTR (axtarış)

Bizim nümunəmizdə düstur belə görünür:

= (DLSTR (A2) -LSTR (ƏVƏZƏT (YUXARI (A2), YUXARİ ($ E $ 1), ""))) / DLSTR ($ E $ 1)