Mündəricat
YouTube kanalımda ən çox baxılan videolardan biri Microsoft Excel proqramında Flash Fill haqqında videodur. Bu alətin mahiyyəti ondan ibarətdir ki, mənbə məlumatlarınızı bir şəkildə çevirmək lazımdırsa, o zaman sadəcə qonşu sütunda əldə etmək istədiyiniz nəticəni yazmağa başlamalısınız. Əl ilə yazılmış bir neçə xanadan sonra (adətən 2-3 kifayətdir), Excel sizə lazım olan çevrilmələrin məntiqini “anlayacaq” və sizin üçün bütün monoton işləri tamamlayaraq avtomatik olaraq yazdıqlarınızı davam etdirəcək:
Səmərəliliyin kvintessensi. Hamımızın çox sevdiyi sehrli "bunu düzgün et" düyməsi, elə deyilmi?
Əslində, Power Query-də belə bir alətin analoqu var - orada deyilir Nümunələrdən sütun (Nümunələrdən sütun). Əslində, bu, məlumatlarınızdan tez öyrənə və sonra onu çevirə bilən Power Query-də qurulmuş kiçik süni intellektdir. Həqiqi tapşırıqlarda bizim üçün faydalı ola biləcəyini başa düşmək üçün onun imkanlarını bir neçə praktik ssenaridə daha yaxından nəzərdən keçirək.
Nümunə 1. Mətni yapışdırmaq/kəsmək
Tutaq ki, Excel-də işçilər haqqında məlumatlar olan belə bir "ağıllı" cədvəlimiz var:
Onu standart şəkildə Power Query-ə yükləyin – düymə ilə Cədvəldən/Aralıqdan nişanı Tarix (Məlumat - Cədvəldən/Aralıqdan).
Tutaq ki, hər bir işçi üçün soyadları və baş hərfləri olan bir sütun əlavə etməliyik (birinci işçi üçün İvanov SV və s.). Bu problemi həll etmək üçün iki üsuldan birini istifadə edə bilərsiniz:
- mənbə məlumatları olan sütun başlığına sağ vurun və əmri seçin Nümunələrdən sütun əlavə edin (Nümunələrdən sütun əlavə edin);
- data ilə və nişanda bir və ya bir neçə sütun seçin Sütun əlavə edilməsi komanda seçin Nümunələrdən sütun. Burada, açılan siyahıda bütün və ya yalnız seçilmiş sütunların təhlil edilməsinə ehtiyac olub olmadığını təyin edə bilərsiniz.
Onda hər şey sadədir – sağda görünən sütunda biz istədiyiniz nəticələrin nümunələrini daxil etməyə başlayırıq və Power Query-də qurulmuş süni intellekt bizim transformasiya məntiqimizi anlamağa və özbaşına davam etməyə çalışır:
Yeri gəlmişkən, düzgün variantları bu sütunun istənilən xanasına daxil edə bilərsiniz, yəni mütləq yuxarıdan aşağıya və cərgəyə deyil. Həmçinin, daha sonra başlıq çubuğundakı qeyd xanalarından istifadə edərək, təhlildən sütunları asanlıqla əlavə edə və ya silə bilərsiniz.
Pəncərənin yuxarısındakı düstura diqqət yetirin – bu, bizə lazım olan nəticələri əldə etmək üçün smart Power Query-nin yaratdığı şeydir. Bu, yeri gəlmişkən, bu vasitə ilə əsas fərqdir Dərhal doldurma Excel-də. Ani doldurma “qara qutu” kimi işləyir – onlar bizə çevrilmələrin məntiqini göstərmir, sadəcə olaraq hazır nəticələr verir və biz onları təbii qəbul edirik. Burada hər şey şəffafdır və siz həmişə məlumatlarla nə baş verdiyini tam aydın şəkildə başa düşə bilərsiniz.
Power Query-nin "fikri tutduğunu" görsəniz, düyməni təhlükəsiz basa bilərsiniz OK və ya klaviatura qısa yolu Ctrl+Daxil edin – Power Query tərəfindən icad edilmiş formula ilə fərdi sütun yaradılacaq. Yeri gəlmişkən, daha sonra adi əl ilə yaradılmış sütun kimi asanlıqla redaktə edilə bilər (komanda ilə Sütun əlavə etmək - Fərdi Sütun) addım adının sağındakı dişli çarxı klikləməklə:
Nümunə 2: Cümlələrdə olduğu kimi hal
Mətni olan sütun başlığına sağ klikləsəniz və əmri seçin Transformasiya (çevir), onda reyestri dəyişdirmək üçün məsul olan üç əmri görə bilərsiniz:
Rahat və sərin, lakin bu siyahıda, məsələn, şəxsən mən həmişə daha bir seçimdən məhrum olmuşam - cümlələrdə olduğu kimi, böyük hərf (böyük) hər sözdə ilk hərf deyil, xanada yalnız ilk hərf olur və Bu kiçik (kiçik) hərflərlə göstərildikdə mətnin qalan hissəsi.
Bu çatışmayan xüsusiyyəti süni intellektlə həyata keçirmək asandır Nümunələrdən sütunlar – eyni ruhda davam etmək üçün Power Query üçün bir neçə seçim daxil edin:
Burada bir düstur olaraq Power Query bir sıra funksiyalardan istifadə edir Mətn.Yuxarı и Mətn. Aşağı, mətni müvafiq olaraq böyük və kiçik hərflərə çevirmək və funksiyalar Mətn.Start и Mətn.Orta – soldan və ortadan mətndən alt sətir çıxara bilən LEFT və PSTR Excel funksiyalarının analoqları.
Nümunə 3. Sözlərin dəyişdirilməsi
Bəzən alınan məlumatları emal edərkən xanalardakı sözləri verilmiş ardıcıllıqla yenidən yerləşdirmək zərurəti yaranır. Əlbəttə ki, sütunu ayırıcı ilə ayrı-ayrı söz sütunlarına bölmək və sonra onu müəyyən edilmiş qaydada yenidən yapışdırmaq olar (boşluqlar əlavə etməyi unutmayın), lakin alətin köməyi ilə Nümunələrdən sütun hər şey daha asan olacaq:
Misal 4: Yalnız rəqəmlər
Başqa bir çox vacib vəzifə hüceyrənin içindən yalnız rəqəmləri (rəqəmləri) çıxarmaqdır. Əvvəlki kimi, məlumatları Power Query-ə yüklədikdən sonra nişana keçin Sütun əlavə etmək – Nümunələrdən sütun və bir neçə xananı əl ilə doldurun ki, proqram tam olaraq nə əldə etmək istədiyimizi başa düşsün:
Bingo!
Yenə də Query-nin düsturu düzgün yaratdığına əmin olmaq üçün pəncərənin yuxarısına baxmağa dəyər – bu halda o, funksiyanı ehtiva edir. Mətn. seçin, təxmin etdiyiniz kimi, siyahıya uyğun olaraq verilmiş simvolları mənbə mətndən çıxarır. Sonradan, bu siyahı, əlbəttə ki, zəruri hallarda düstur çubuğunda asanlıqla redaktə edilə bilər.
Misal 5: Yalnız mətn
Əvvəlki nümunəyə bənzər şəkildə, siz çıxara bilərsiniz və əksinə - yalnız mətni, bütün nömrələri, durğu işarələrini və s.
Bu zaman artıq mənaca əks olan funksiyadan istifadə olunur – verilmiş siyahıya uyğun olaraq orijinal sətirdən simvolları silən Text.Remove.
Misal 6: Alfasayısal sıyıqdan məlumatların çıxarılması
Power Query daha çətin hallarda, məsələn, xanadakı alfasayısal sıyıqdan faydalı məlumat çıxarmaq lazım olduqda kömək edə bilər, məsələn, bank çıxarışındakı ödəniş məqsədinin təsvirindən hesab nömrəsini əldə edin:
Nəzərə alın ki, Power Query tərəfindən yaradılan çevirmə düsturu olduqca mürəkkəb ola bilər:
Oxumaq və başa düşmək asanlığı üçün onu pulsuz onlayn xidmətdən istifadə edərək daha sağlam formaya çevirmək olar. Power Query Formatter:
Çox lazımlı şey - yaradıcılara hörmət!
Misal 7: Tarixlərin dəyişdirilməsi
Alət Nümunələrdən sütun tarix və ya datetime sütunlarına da tətbiq oluna bilər. Tarixin ilk rəqəmlərini daxil etdiyiniz zaman Power Query faydalı olaraq bütün mümkün çevirmə seçimlərinin siyahısını göstərəcək:
Beləliklə, orijinal tarixi asanlıqla istənilən ekzotik formata çevirə bilərsiniz, məsələn, "il-ay-gün":
Misal 8: Kateqoriyalara ayırma
Alətdən istifadə etsək Nümunələrdən sütun rəqəmli verilənləri olan bir sütuna fərqli şəkildə işləyir. Güman edək ki, Power Query-ə yüklənmiş işçi test nəticələrimiz var (0-100 diapazonunda şərti ballar) və biz aşağıdakı şərti dərəcəni istifadə edirik:
- Magistrlər – 90-dan yuxarı bal toplayanlar
- Ekspertlər - 70-dən 90-a qədər bal toplayıblar
- İstifadəçilər - 30-dan 70-ə qədər
- Başlayanlar - 30-dan az bal toplayanlar
Siyahıya misallardan bir sütun əlavə etsək və bu dərəcələri əl ilə tənzimləməyə başlasaq, onda çox keçmədən Power Query ideyamızı götürəcək və operatorların bir-birinə daxil olduğu düsturlu bir sütun əlavə edəcək. if Məntiq bizə lazım olana çox oxşar şəkildə həyata keçiriləcək:
Yenə də vəziyyəti sona qədər sıxmaq olmaz, ancaq üzərinə klikləyin OK və sonra artıq düsturdakı hədd dəyərlərini düzəldin - bu şəkildə daha sürətli:
Nəticələr
Şübhəsiz ki, bir vasitə Nümunələrdən sütun "sehrli həb" deyil və gec-tez qeyri-standart vəziyyətlər və ya xüsusən də məlumatlarda "kolxozun" diqqətdən kənarda qaldığı hallar olacaq, Power Query uğursuz olacaq və istədiyimizi həyata keçirə bilməyəcək. bizim üçün düzgün. Bununla belə, köməkçi bir vasitə kimi, çox yaxşıdır. Üstəlik, onun yaratdığı düsturları öyrənməklə siz M dilinin funksiyaları haqqında biliklərinizi genişləndirə bilərsiniz ki, bu da gələcəkdə həmişə faydalı olacaq.
- Power Query-də Daimi İfadələrlə (RegExp) Mətnin təhlili
- Power Query-də qeyri-səlis mətn axtarışı
- Microsoft Excel-də Flash Doldurun