Mündəricat
Mənbə mətndə açar sözlərin axtarılması verilənlərlə işləyərkən ən çox görülən işlərdən biridir. Aşağıdakı nümunədən istifadə edərək onun həllinə bir neçə yolla baxaq:
Tutaq ki, siz və mən açar sözlər siyahısı - avtomobil markalarının adları - və hər növ ehtiyat hissələrinin böyük bir cədvəli var, burada təsvirlər bəzən bir və ya bir neçə belə markanı ehtiva edə bilər, əgər ehtiyat hissəsi birdən çox uyğun gəlirsə. avtomobil markası. Bizim vəzifəmiz verilmiş ayırıcı simvol (məsələn, vergül) vasitəsilə qonşu xanalarda aşkar edilmiş bütün açar sözləri tapmaq və göstərməkdir.
Metod 1. Power Query
Əlbəttə ki, əvvəlcə klaviatura qısa yolundan istifadə edərək cədvəllərimizi dinamik (“ağıllı”) vəziyyətə gətiririk Ctrl+T və ya əmrlər Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format), onlara ad verin (məsələn Stampsи Ehtiyat hissələr) və tabda seçməklə Power Query redaktoruna bir-bir yükləyin Data – Cədvəldən/Aralıqdan (Məlumat - Cədvəldən/Aralıqdan). Power Query-nin ayrıca əlavə olaraq quraşdırıldığı Excel 2010-2013-ün köhnə versiyaları varsa, istədiyiniz düymə nişanda olacaq Güc sorğusu. Excel 365-in tamamilə yeni bir versiyası varsa, düyməni basın Cədvəldən/Aralıqdan indi oraya zəng etdi Yarpaqları ilə (Cədvəldən).
Power Query-də hər cədvəli yüklədikdən sonra əmrlə Excel-ə qayıdırıq Əsas səhifə — Bağlayın və yükləyin — Bağlayın və yükləyin... — Yalnız əlaqə yaradın (Ev - Bağla və Yüklə - Bağla və Yüklə... - Yalnız əlaqə yarat).
İndi dublikat sorğu yaradaq Ehtiyat hissələrüzərinə sağ klikləyərək seçin Dublikat sorğu (Dublikat sorğu), sonra əldə edilən surət sorğusunun adını dəyişdirin Nəticələri və biz onunla işləməyə davam edəcəyik.
Hərəkətlərin məntiqi belədir:
- Ətraflı sekmesinde Sütun əlavə edilməsi komanda seçin Fərdi sütun (Sütun əlavə et - Fərdi sütun) və formula daxil edin = Brendlər. Tıkladıqdan sonra OK yeni bir sütun alacağıq, burada hər bir xanada açar sözlərimizin siyahısı ilə iç içə bir cədvəl olacaq - avtomobil istehsalçıları:
- Bütün daxili cədvəlləri genişləndirmək üçün əlavə edilmiş sütunun başlığında ikiqat oxları olan düymədən istifadə edin. Eyni zamanda, ehtiyat hissələrinin təsviri olan sətirlər markaların sayına çoxalacaq və biz "ehtiyat hissələri markası" nın bütün mümkün cüt birləşmələrini alacağıq:
- Ətraflı sekmesinde Sütun əlavə edilməsi komanda seçin Şərti sütun (Şərti sütun) və mənbə mətndə (hissə təsviri) açar sözün (markanın) baş verməsini yoxlamaq üçün şərt qoyun:
- Axtarış hərfini həssas etmək üçün düstur çubuğuna üçüncü arqumenti əl ilə əlavə edin Müqayisə edin.OrdinalIgnoreCase baş vermənin yoxlanılması funksiyasına Mətn.Tərkibindədir (düstur çubuğu görünmürsə, o zaman nişanda aktivləşdirilə bilər baxış):
- Yaranan cədvəli filtrləyirik, sonuncu sütunda yalnız birini, yəni uyğunluqları buraxırıq və lazımsız sütunu çıxarırıq. Baş verənlər.
- Eyni təsvirləri komanda ilə qruplaşdırmaq Qrup tərəfindən nişanı Transformasiya (Transform - Qruplaşdır). Toplama əməliyyatı olaraq seçin Bütün xətlər (Bütün sıralar). Çıxışda, bizə lazım olan avtomobil istehsalçılarının markaları da daxil olmaqla, hər bir ehtiyat hissəsi üçün bütün detalları ehtiva edən cədvəllər olan bir sütun alırıq:
- Hər hissə üçün qiymətləri çıxarmaq üçün tabda başqa hesablanmış sütun əlavə edin Sütun əlavə etmək - Fərdi Sütun (Sütun əlavə et - Fərdi sütun) və cədvəldən ibarət düsturdan istifadə edin (onlar sütunumuzda yerləşirlər Ətraflı) və çıxarılan sütunun adı:
- Yaranan sütunun başlığında ikiqat oxlu düyməni sıxırıq və əmri seçirik Dəyərləri çıxarın (Dəyərləri çıxarın)İstədiyiniz hər hansı bir ayırıcı simvol ilə möhür çıxarmaq üçün:
- Lazımsız sütunun çıxarılması Ətraflı.
- Yaranan cədvələ təsvirlərdə heç bir marka tapılmayan ondan yoxa çıxan hissələri əlavə etmək üçün sorğunun birləşdirilməsi prosedurunu yerinə yetiririk. Nəticə orijinal tələbi ilə Ehtiyat hissələr düyməsini Birləşdirmək nişanı Əsas səhifə (Ev - Sorğuları birləşdirin). Bağlantı növü - Xarici Qoşulma Sağ (Sağ xarici birləşmə):
- Qalan yalnız əlavə sütunları silmək və qalanların adını dəyişdirməkdir - və vəzifəmiz həll olunur:
Metod 2. Düsturlar
Əgər sizdə Excel 2016 və ya daha yeni versiya varsa, o zaman problemimiz yeni funksiyadan istifadə etməklə çox yığcam və zərif şəkildə həll edilə bilər. COMBINE (TEXT JOIN):
Bu formulun arxasında duran məntiq sadədir:
- Function AXTAR (TAPA) hissənin cari təsvirində növbə ilə hər bir markanın baş verməsini axtarır və ya markanın tapıldığı simvolun seriya nömrəsini, ya da #VALUE xətasını qaytarır! marka təsvirdə yoxdursa.
- Sonra funksiyadan istifadə edin IF (Əgər) и EOSHIBKA (SƏHV) səhvləri boş mətn sətri “” ilə, simvolların sıra nömrələrini isə brend adlarının özləri ilə əvəz edirik.
- Nəticədə boş xanalar və tapılmış markalar toplusu funksiyadan istifadə edərək verilmiş ayırıcı simvol vasitəsilə tək sətirdə yığılır. COMBINE (TEXT JOIN).
Sürətləndirmə üçün Performans Müqayisəsi və Güc Sorğu Sorğunun Buferləşdirilməsi
Performans testi üçün ilkin məlumat kimi 100 ehtiyat hissələrinin təsviri cədvəlini götürək. Bunun üzərinə aşağıdakı nəticələri alırıq:
- Düsturlar üzrə yenidən hesablama vaxtı (2-ci üsul) – 9 san. ilk dəfə düsturu bütün sütuna kopyaladığınız zaman və 2 san. at təkrar (tamponlama təsir göstərir, yəqin).
- Power Query sorğusunun yeniləmə vaxtı (Metod 1) daha pisdir – 110 saniyə.
Əlbəttə ki, çox şey müəyyən bir PC-nin avadanlıqlarından və Office-in quraşdırılmış versiyasından və yeniləmələrdən asılıdır, lakin ümumi mənzərə, məncə, aydındır.
Power Query sorğusunu sürətləndirmək üçün axtarış cədvəlini bufer edək Stamps, çünki sorğunun icrası prosesində dəyişmir və onu daim yenidən hesablamaq lazım deyil (Power Query de-fakto etdiyi kimi). Bunun üçün funksiyadan istifadə edirik Cədvəl.Bufer daxili Power Query dilindən M.
Bunu etmək üçün bir sorğu açın Nəticələri və tabda baxış Düyməyə bas Qabaqcıl redaktor (Bax - Qabaqcıl Redaktor). Açılan pəncərədə yeni dəyişənli sətir əlavə edin Marki 2, avtomobil istehsalçısı kataloqumuzun bufer versiyası olacaq və bu yeni dəyişəni daha sonra aşağıdakı sorğu əmrində istifadə edin:
Bu cür dəqiqləşdirmədən sonra sorğumuzun yeniləmə sürəti təxminən 7 dəfə - 15 saniyəyə qədər artır. Tamam başqa şey 🙂
- Power Query-də qeyri-səlis mətn axtarışı
- Düsturlarla toplu mətnin dəyişdirilməsi
- List.Accumate funksiyası ilə Power Query-də toplu mətnin dəyişdirilməsi