Pivot masa nümunəsini yükləyin. Hesabat və məlumatların işlənməsi üçün Excel həyat hikkələri

Renat ilk dəfə deyil ki, Lifehacker-da qonaq müəllif olur. Biz ondan əvvəl təlim planının necə tərtib ediləcəyinə dair əla material dərc etmişik: və onlayn resurslar, həmçinin təlim planının yaradılması.

Bu məqalədə imkan verən sadə fəndlər var. 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 və onlardan idarəetmə üçün təqdimatlar və diaqramlar hazırlayanlar üçün faydalı olacaqlar. 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.

Axtardığınız dəyərlər cədvəlin birinci sütununda deyilsə, VLOOKUP və GLOOKUP-a sadə alternativlər: LOOKUP, INDEX+MATCH

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 (IF) funksiyasından və sizə imkan verən TEXT funksiyasından istifadə olunur. nömrəni istənilən formatda göstərmək üçün. Onun sintaksisi aşağıda təsvir edilmişdir:

MƏTN ( məna; format)

Format, Hüceyrələri Format pəncərəsində xüsusi format daxil etdiyiniz kimi, dırnaq işarələrində göstərilmişdir.

Daha mürəkkəb mətnlər də avtomatlaşdırıla bilər. Təcrübəmdə “GÖSTERİCİ plana nisbətən XX azaldı/böyüdü, əsasən FACTOR1-in XX-ə artması/azalması, FACTOR2-nin artması/azalması səbəbindən idarəetmə hesabatlarına uzun, lakin müntəzəm şərhlərin avtomatlaşdırılması var idi. YY tərəfindən ..." dəyişən amillər siyahısı ilə. Əgər belə şərhləri tez-tez yazırsansa və onların yazılması prosesini alqoritmləşdirmək olarsa, işin heç olmasa bir hissəsini xilas edəcək düstur və ya makro yaratmaqla bir dəfə çaşıb qalmağa dəyər.

Birləşdikdən sonra məlumatları hər bir xanada necə saxlamaq olar

Hüceyrələri birləşdirərkən yalnız bir dəyər saxlanılır. Hüceyrələri birləşdirməyə çalışarkən Excel bu barədə xəbərdarlıq edir:

Müvafiq olaraq, hər bir xanadan asılı olan düsturunuz varsa, onlar birləşdirildikdən sonra o, işləməyi dayandıracaq (nümunənin 3-4-cü sətirlərində #Yoxdur):

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

Çoxsaylı məlumat mənbələrindən xülasəni necə qurmaq olar

Bir anda bir neçə məlumat mənbəyindən xülasə yaratmaq lazımdırsa, belə bir seçim olan lentə və ya sürətli giriş panelinə Pivot Cədvəl və Pivot Diaqram Sihirbazını ə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ə Pivot Diaqram Sihirbazı" → "Əlavə et":

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

Bunun üzərinə kliklədiyiniz zaman bir dialoq pəncərəsi görünür:

Orada "Bir neçə konsolidasiya diapazonunda" maddəsini seçib "Sonrakı" düyməsini sıxmalısınız. Növbəti paraqrafda siz "Bir səhifə sahəsi yarat" və ya "Səhifə sahələri yaradın" seçə bilərsiniz. Məlumat mənbələrinin hər biri üçün özünüz bir ad tapmaq istəyirsinizsə, ikinci elementi seçin:

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

Bundan sonra, son 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 tarifi SuperMTS" - MTS abbreviaturasının iki təkrarlanması)

Bu nümunədə A sütununda bir neçə mətn sətirləri 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:

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

Xüsusi bir hadisəni tapmaq mətn sətri digərinə, orijinaldakı bütün hadisələri silməli və nəticədə yaranan sətirin uzunluğunu orijinal ilə müqayisə etməlisiniz:

DLSTR (“MTS Super MTS Tarifi”) – DLSTR (“Super Tarif”) = 6

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

6 / DLSTR (“MTS”) = 2

İki dəfə "MTS" sətirinin orijinala daxil olmasıdır.

Bu alqoritmi düsturlar dilində yazmaq qalır (biz hadisələri axtardığımız mətni “mətn”lə, baş vermələrinin sayı ilə maraqlandığımızı isə “arzu olunan” ilə işarə edirik):

=(DLSTR( mətn)-DLSTR(ƏVƏZİNƏ(YUXARI() mətn);YUXARI( arzu olunan);“”)))/DLSTR( arzu olunan)

Bizim nümunəmizdə formula belə görünür:

=(LEN(A2)-UZUN(ƏZƏBƏT(YUXARI(A2),YUXARI($E$1),“”))/LEN($E$1)

Excel iş səhifəsi məlumatları birinci sətirdə sütun başlığı ilə siyahı formatında olmalıdır. Qalan sətirlərdə verilənlər aralığında boş sətir və sütunlar olmadan bir sütunda oxşar obyektlər olmalıdır.

Açıq vərəq pəncərəsində məlumat diapazonunda istənilən xananı seçin.
"Daxil et" sekmesine keçin və "Cədvəllər" qrupunda "Pivot Cədvəl" düyməsi menyusunu genişləndirin.
Komandalar siyahısında "Pivot Cədvəl" maddəsini seçin (şək. 5.59).
"Pivot cədvəlinin yaradılması" pəncərəsində (şək. 5.60), "Cədvəl və ya diapazon" sütununda təhlil edilən məlumatların seçilmiş diapazonunun ünvanı göstərilir.

Aralığı dəyişdirmək lazımdırsa, "Pivot Cədvəli Yarat" pəncərəsini bağlamadan vərəqdə yeni diapazon seçin. İş kitabının başqa vərəqində və ya başqa iş kitabında olan xanalar diapazonu aşağıdakı kimi daxil edilir: ([kitab adı]vərəq adı!aralıq) .

"Pivot Cədvəl hesabatının harada yerləşdiriləcəyini göstərin" qrupunda seçin:
- "Yeni vərəqə" - Pivot Cədvəl hesabatını kitabdakı əlavə vərəqə yerləşdirmək.
- "Mövcud vərəqə" - pivot cədvəl hesabatını mənbə məlumatları ilə eyni vərəqdə yerləşdirmək.

Bu yerləşdirməni seçərkən, hesabatın "Rəsmi" bəndində müstəqil olaraq hesabat üçün xananı göstərməlisiniz.

OK düyməsi ilə pəncərəni bağlayın.
Vərəqdə boş pivot cədvəli (yeni və ya mövcud) layout konturu şəklində görünəcək və hesabat yaratmaq üçün vərəqin sağında “Pivot Cədvəl Sahələrinin Siyahısı” sahəsi açılacaq (şək. 5.61). İçinə daxil edilə bilər dönmə cədvəli yeni sahələr, tərtibat yaradın və hesabatı fərdiləşdirin.

Pivot cədvəli yaratmaq üçün "Sahə siyahısı" pəncərəsində sütunların adlarını yoxlayın.

"Sahə siyahısı" pəncərəsini gizlətmək üçün iş vərəqindəki istənilən boş xananı vurun. Alternativ olaraq, Seçimlər sekmesinde, Göstər/Gizlət qrupunda Sahə Siyahısını söndürün.

Təsəvvür edin ki, qərar qəbul etmək üçün qısa bir analitik hesabat hazırlamaq üçün çoxlu müxtəlif məlumatları ehtiva edən böyük bir cədvələ ehtiyacınız var. Tutaq ki, sizdə Bankın kredit portfeli haqqında məlumatları əks etdirən məlumatlarınız var, burada hər bir borcalan üçün ətraflı məlumat qeyd olunur. Beləliklə, kredit portfelini təhlil etmək üçün Excel-də verilənlər bazasından aşağıdakı cədvəl çıxarılır:

Bu, hər bir fərdi sətirdə bir kredit əməliyyatı haqqında tam məlumat var:

borcalanın adı
sövdələşmə nömrəsi
kreditin verilmə tarixi və ödənilmə tarixi
kredit növü
kredit məbləği

Təbii ki, satış menecerləri öz işlərini bilirlərsə və ciddi işləyirlərsə, onda hər gün bu cədvələ bir neçə onlarla sətir əlavə olunacaq və məsələn, bir ilin və ya ən azı dörddə birinin sonuna qədər masanın ölçüsü dəhşətli olacaq. .

Bununla belə, bu məlumatlarla bağlı hesabatlar yaratmaq zərurəti sizi daha da dəhşətə gətirəcək. Hər ay nə qədər kredit verilirdi? Kreditləşdirmə istiqamətləri kontekstində say və məbləğə görə kredit portfelinin həcmi nə qədərdir? İlk on borcalan? və s.

Bütün suallar düşündüyünüzdən daha asan cavablandırıla bilər. Üç dəqiqə, təxminən üç. Ən heyrətamiz vasitələrdən biri ilə Microsoft Excel - Pivot cədvəlləri.

Get...

Bütün cədvəlimizi məlumatlarla seçin və menyunu vurun Daxil et - Pivot Cədvəl (Daxil et-Pivot Cədvəl).

Pivot cədvəlimizi harada yerləşdirəcəyimizi və məlumat diapazonunu tənzimləyəcəyimizə bənzər bir pəncərə görünəcək.

Varsayılan olaraq, pivot cədvəlini yeni vərəqə qoyun seçilir. (yeni iş vərəqi), lakin, lazım gələrsə, kitabın cari və ya hər hansı digər vərəqini əl ilə seçə bilərik (Mövcud İş Vərəqi). Bunun üçün yeni bir vərəq seçmək daha yaxşıdır - onda pivot cədvəlinin orijinal siyahı ilə "üst-üstə düşməsi" riski yoxdur və biz bir dəstə tsiklik bağlantı alacağıq. düyməni basın tamam və keçək ən maraqlısına - hesabatımızın qurulması mərhələsinə.

Layout ilə işləmək

Düyməni kliklədiyiniz zaman nə görürsünüz tamam pivot cədvəlinin düzülüşü (layout) adlanır:

Onunla işləmək asandır - sütunların (sahələrin) adlarını siçan ilə pəncərədən sürükləmək lazımdır. Pivot Cədvəl Sahə Siyahısı (Pivot Cədvəl Sahə Siyahısı)ərazisində sətirlər (Sətr Etiketləri), sütunlar (Sütun Etiketləri), filtrlər (Hesabat Filtri) və tərtibat məlumatları (Dəyərlər). Yeganə xəbərdarlıq - bunu daha dəqiq edin, qaçırmayın! Get...

Siz sürükləyib buraxdıqca Pivot Cədvəl sizə lazım olan məlumatları əks etdirmək üçün gözlərinizin önündə dəyişməyə başlayacaq. Siyahıdan bizə lazım olan dörd sahənin hamısını köçürdükdən sonra, demək olar ki, hazır hesabat alacaqsınız. Yalnız onu düzgün formatlaşdırmaq qalır.
Menyudan PivotTable formatını seçin PivotTables Alətləri - Dizayn:

Və aşağıdakı mənzərəni əldə edirik:

Hər şey o qədər də mürəkkəb deyil, elə deyilmi?

P.S.

Pivot masaların yeganə dezavantajı olmamasıdır avtomatik yeniləmə(yenidən hesablama) orijinal siyahıdakı məlumatları dəyişdirərkən. Belə bir yenidən hesablama aparmaq üçün pivot cədvəlinə sağ vurun və kontekst menyusundan əmri seçin. təzələmək (təzələmək) .

Microsoft Excel-in bir çox xüsusiyyətlərindən biri onun hesabatları avtomatlaşdırmaq qabiliyyətidir. Məlumatlarınızı daxil etməyi asanlaşdırmaq üçün interaktiv cədvəllər yarada bilərsiniz excel kitabları digər insanlar və siz həmçinin hesabat yaratmağı avtomatlaşdıra bilərsiniz. Bu funksiyaların hər ikisi Visual Basic-də müəyyən bilik tələb edir. Hər iki tapşırığı yerinə yetirmək üçün addımlar aşağıda təsvir edilmişdir.

Addımlar

İnteraktiv cədvəl yaradın

Masanızın necə görünəcəyinə qərar verin. Cədvəl elə yerləşdirilməlidir ki, digər insanlar məlumat daxil etməli olduğunuz sahələri asanlıqla tapa bilsinlər.

Cədvəli üfüqi və ya şaquli olaraq yerləşdirə bilərsiniz. Əksər istifadəçilər onunla işləməyi daha asan hesab edirlər şaquli masalar, xüsusən onları sonra çap etmək istəyirsinizsə.

Cədvəldə mətn etiketləri yaradın. Siz sütun adlarını əlavə etməlisiniz, həmçinin məlumat giriş sahələrini ehtiva edən xanaların soluna etiketlər əlavə etməlisiniz.

Alt və F11 düymələrini eyni anda basın. Microsoft Visual Basic Redaktoru açılır.

"Project-VBA Project" panelinin yuxarı sol küncündə "ThisWorkbook" üzərinə iki dəfə klikləyin. Redaktorun əsas bölməsində mənbə kodu olan bir pəncərə açılacaqdır.

Daxil et menyusundan Prosedur seçin. Prosedur əlavə et informasiya qutusu açılır.

Ad sahəsinə prosedur üçün ad daxil edin. Cədvəl səyahət xərclərini bildirmək üçün istifadə ediləcəksə, prosedura "SumXərclər" kimi mənalı bir ad verin. Dialoq qutusunu bağlamaq üçün OK düyməsini basın.
- Prosedur adında boşluq ola bilməz, lakin siz boşluq əvəzinə alt xətt (_) simvolundan istifadə edə bilərsiniz.
- "Prosedur əlavə et" pəncərəsini bağladıqdan sonra "Public Sub" və prosedurun adını yazan bir sətir görəcəksiniz. Bu xəttin altında boş yer və "End Sub" yazısı olacaq.
Cədvəldəki hər bir giriş sahəsi üçün kodu yazın. Hər bir giriş sahəsi üçün iki sətir kod yazacaqsınız.
- Kodun birinci sətri belə görünür: "Range("cellname").Seçin, burada "cellname" giriş sahəsinin məzmununun saxlanacağı xanadır. Bu, etiketin sağındakı xana olmalıdır; mətn etiketi A2 xanasında yerləşirsə, onda siz daxiletmə sahəsini B2 xanasına yerləşdirməlisiniz (Range("B2").Seçin). Hüceyrə adını dırnaq işarəsinə alın, lakin kod sətirini dırnaq işarəsinə daxil etməyin.
- Kodun ikinci sətri belə görünür: "ActiveCell.Value = InputBox("InputPrompt")", burada "InputPrompt" istifadəçiyə hansı növ məlumatı daxil etməyi təklif edəcək mətndir. Məsələn, məlumat daxiletmə xanası naharın qiymətini hesablamaq üçündürsə, "InputPrompt" sözünü "Tətbiqlər daxil olmaqla naharın ümumi dəyərini daxil edin" ilə əvəz edin. (Soruş mətnini dırnaq işarəsinə alın, lakin siz bütün əmri dırnaq işarəsinə daxil etməlisiniz.)
Hər hesablanmış sahə üçün kod yazın. Siz yuxarıdakı iki sətirdən istifadə edə bilərsiniz, lakin bu dəfə ActiveCell.Value alət ipucunu göstərmək üçün yuxarıda istifadə olunan InputBox funksiyası əvəzinə hesablamanın və ya SUM kimi rəqəmsal funksiyanın nəticəsidir.

İnteraktiv cədvəli saxlamaq üçün kod xətti əlavə edin. Onun formatı: "ActiveWorkbook.SaveAs Filename:="Filename.xls"," burada "Fayl adı" interaktiv elektron cədvəlinizin adıdır. ("Filename.xls" ətrafında sitatları buraxın, lakin bütün sətirdən sitat gətirməyə ehtiyac yoxdur.)
- Əgər sizdə Excel 2007 və ya daha yeni versiya varsa, siz ".xls"i ".xlsx" ilə əvəz edə bilərsiniz, lakin sonra Excel 2003 və ya daha əvvəl quraşdırılmış interaktiv cədvəlinizin bəzi istifadəçiləri xüsusi plagin olmadan cədvəlinizdən istifadə edə bilməyəcəklər. oxumaq üçün.
Alt və Q düymələrini eyni anda basın. Visual Basic Editor pəncərəsi bağlanır.

Alt və F8 düymələrini eyni anda basın. Makroslar informasiya qutusu açılır.

Makroslar siyahısında prosedurunuzun adına klikləyin. Siyahıda yalnız prosedurunuz varsa, o, avtomatik olaraq seçiləcək.

Seçimlər düyməsini basın. Sizdən klaviatura qısa yolu kimi Ctrl düyməsi ilə birlikdə istifadə ediləcək hərfi daxil etməyiniz xahiş olunacaq. İsti düymə kimi istifadə edilməyən hərfi seçin, məsələn, "qeyd" üçün "z".

Makros dialoqunu bağlamaq üçün "OK" düyməsini basın.İndi siz interaktiv cədvəlinizi ondan istifadə edəcəklər arasında paylaya bilərsiniz. Açıldıqdan sonra istifadə edə biləcəklər isti düymə qeydlər əlavə etmək və məlumatları tamamlamaq üçün göstərişlərinizə əməl etmək.

Hesabatların yaradılmasının avtomatlaşdırılması

Hesabatınızı pivot cədvəldə ümumiləşdirin. Pivot Cədvəllər rəqəmləri və trendləri müqayisə etməyə kömək etmək üçün məlumatları ümumiləşdirmək üçün nəzərdə tutulmuşdur. Pivot Cədvəliniz cədvəldəki bəzi məlumatlarla əlaqələndirilməli və ya verilənlər bazasından idxal edilməlidir.

Hesabatı açmaq və bağlamaq üçün Visual Basic skriptini yazın. Skriptiniz aşağıda sadalanan funksiyaları yerinə yetirməlidir. Hər bir funksiyanın təsviri veriləcək və onların icrası üçün kod mötərizədə veriləcəkdir. Kodu yazarkən onu bir blokda yazın, nümunədəki adları öz adlarınızla əvəz edin və nümunədə göstərilən mötərizələri daxil etməyin.
- Cədvəli yalnız oxumaq rejimində açın.
- Verilənləri təzələyin və hesabatı bu nümunədə vaxt damğası olan PDF olaraq saxlayın. Sənədiniz fərqli formatda olacaqsa, ".pdf" nümunəsindəki genişləndirməni sizə lazım olan genişləndirmə ilə dəyişdirin.
- Elektron cədvəli saxlamadan bağlayın, sonra Excel-i bağlayın.
- Excel 2007 və ya sonrakı XML əsaslı formatlarda saxlanılıbsa, cədvəl uzantısı kimi ".xls" əvəzinə ".xlsx" istifadə edin.
Visual Basic-də skripti işlədəcək .bat faylı yazın. Bu, vizual skriptin avtomatik olaraq işə düşməsi üçün edilməlidir. Yarasa faylı olmadan VB skripti əl ilə işlədilməlidir.
- Skriptiniz bu formatda olmalıdır, göstərilən qovluq və fayl adını özünüzlə əvəz edin və mötərizələri daxil etməyin:
Yaranan faylı yaratdıqdan sonra yoxlayacaq bir yarasa faylı yazın. Skriptiniz aşağıdakı funksiyaları yerinə yetirməlidir. Hər bir funksiyadan sonra onu həyata keçirən kod kvadrat mötərizədə veriləcək. Kodu yazarkən onu bir blokda yazın, nümunədəki adları öz adlarınızla əvəz edin və nümunədə göstərilən mötərizələri daxil etməyin.
- Yaranan faylın mövcud olub olmadığını yoxlayın. Qəbul edilmiş faylın formatı PDF deyilsə, nümunədə ".pdf" i istədiyiniz formatla əvəz edin.
- Nəticə fayl/hesabat varsa, onu hesabata ehtiyacı olan insanlara e-poçtla göndərin.
- Nəticə fayl/hesabat göstərilən yerdə mövcud deyilsə, sizə faylın yaradılmasının uğursuz olması barədə mesaj göndərən prosedurunuz olmalıdır. [ Başqa (göndərmək -f [email protected]-t [email protected]-u Hesabat işləmədi -m faylı %rreport% \\pdfloc\-da yoxdur -s serveriniz:port -xu istifadəçi adı -xp parol)]