Bu yaxınlarda bir dostum mənə verilən sözlər toplusundan ibarət bütün mümkün ifadələrin yaradılmasına kömək etmək xahişi ilə müraciət etdi. Bu cür problemlər onlayn reklam və SEO təşviqi üçün açar sözlər və ifadələrin siyahılarını tərtib edərkən, axtarış sorğusunda sözlərin bütün mümkün dəyişdirilməsindən keçməli olduğunuz zaman yarana bilər:
Riyaziyyatda bu əməliyyat adlanır Kartezyen məhsul. Rəsmi tərif belədir: A və B çoxluqlarının Kartezian hasilatı birinci komponenti A çoxluğuna, ikinci komponenti isə B çoxluğuna aid olan bütün cütlərin çoxluğudur. Bundan başqa, çoxluqların elementləri hər ikisi ola bilər. rəqəmlər və mətn.
İnsan dilinə tərcümə edilərsə, bu o deməkdir ki, əgər A dəstində, məsələn, “ağ” və “qırmızı” sözləri, B dəstində isə “BMW” və “Mercedes” sözləri varsa, onda bu iki çoxluğun Kartezian hasilindən sonra biz get on the output hər iki siyahının sözlərindən ibarət ifadələrin bütün mümkün variantlarının toplusudur:
- ağ bmw
- qırmızı bmw
- ağ mersedes
- qırmızı mercedes
... yəni bizə lazım olan şey. Excel-də bu tapşırığı həll etməyin bir neçə yoluna baxaq.
Metod 1. Düsturlar
Düsturlardan başlayaq. Fərz edək ki, ilkin məlumat kimi bizdə müvafiq olaraq A, B və C sütunlarında orijinal sözlərin üç siyahısı var və hər siyahıdakı elementlərin sayı dəyişə bilər:
Əvvəlcə indeksləri olan üç sütunu, yəni bütün mümkün birləşmələrdə hər siyahıdan sözlərin sıra nömrələrini düzəldək. Birinci sıra vahidlər (E2:G2) əl ilə daxil ediləcək, qalanları üçün isə aşağıdakı düsturdan istifadə edəcəyik:
Burada məntiq sadədir: əgər yuxarıdakı əvvəlki xanadakı indeks artıq siyahının sonuna çatıbsa, yəni funksiya ilə hesablanmış siyahıdakı elementlərin sayına bərabərdirsə. COUNT (COUNTA), sonra nömrələməni yenidən başlatırıq. Əks halda, indeksi 1 artırırıq. Dollar işarələri ($) ilə diapazonların ağıllı şəkildə sabitlənməsinə xüsusi diqqət yetirin ki, formulanı aşağı və sağa köçürə biləsiniz.
İndi hər siyahıdan bizə lazım olan sözlərin sıra nömrələrinə sahib olduğumuz üçün funksiyadan istifadə edərək sözlərin özlərini çıxara bilərik. İNDEKSİ (İNDEKS) üç ayrı sütuna:
Əgər siz əvvəllər işinizdə bu funksiyaya rast gəlməmisinizsə, onda mən sizə ən azı diaqonal olaraq öyrənməyi məsləhət görürəm - bu, bir çox hallarda kömək edir və daha az (və hətta daha çox!) VPR (BAXIN).
Yaxşı, bundan sonra yalnız birləşmə simvolundan (&) istifadə edərək yaranan fraqmentləri sətir-sətir yapışdırmaq qalır:
… və ya (Excel-in ən son versiyası varsa) lazımlı funksiyası ilə COMBINE (TEXT JOIN), müəyyən edilmiş hüceyrələrin bütün məzmununu müəyyən bir ayırıcı xarakter (boşluq) vasitəsilə yapışdıra bilən:
Metod 2. Power Query vasitəsilə
Power Query iki əsas vəzifəni yerinə yetirən Microsoft Excel üçün güclü əlavədir: 1. demək olar ki, istənilən xarici mənbədən məlumatların Excel-ə yüklənməsi və 2. yüklənmiş cədvəllərin hər cür çevrilməsi. Power Query artıq Excel 2016-2019-da qurulub və Excel 2010-2013 üçün o, ayrıca əlavə olaraq quraşdırılıb (onu rəsmi Microsoft veb saytından pulsuz yükləyə bilərsiniz). Əgər siz hələ işinizdə Power Query-dən istifadə etməmisinizsə, o zaman bu barədə düşünməyin vaxtıdır, çünki yuxarıda təsvir edilənlər kimi transformasiyalar orada asanlıqla və təbii şəkildə, sadəcə bir neçə hərəkətlə həyata keçirilir.
Əvvəlcə mənbə siyahılarını Power Query-də ayrı sorğular kimi yükləyək. Bunu etmək üçün hər bir cədvəl üçün aşağıdakı addımları yerinə yetirin:
- Gəlin bir düymə ilə masaları “ağıllı”lara çevirək Cədvəl kimi formatlayın nişanı Əsas səhifə (Ev - Cədvəl kimi format) və ya klaviatura qısa yolu Ctrl+T. Hər cədvələ avtomatik olaraq ad veriləcək Cədvəl 1,2,3…, lakin nişanda istəsəniz dəyişdirilə bilər konstruktor (Dizayn).
- Cədvəldə aktiv xananı təyin etdikdən sonra düyməni basın Masadan (Cədvəldən) nişanı Tarix (Tarix) və ya tabda Güc sorğusu (əgər siz onu Excel 2010-2013 üçün ayrıca əlavə olaraq quraşdırmısınızsa).
- Açılan sorğu redaktoru pəncərəsində əmri seçin Əsas səhifə — Bağlayın və Yükləyin — Bağlayın və Yükləyin… (Ev — Bağla&Yüklə — Bağla&Yüklə..) və sonra seçim Sadəcə əlaqə yaradın (Yalnız əlaqə yaradın). Bu, yüklənmiş cədvəli yaddaşda saxlayacaq və gələcəkdə ona daxil olmağa imkan verəcəkdir.
Hər şeyi düzgün etsəniz, sağ paneldəki çıxış rejimdə üç sorğu olmalıdır Yalnız əlaqə masa adlarımızla:
İndi ilk sorğuya sağ vurun və əmri seçin əlaqə (İstinad)onun yenilənə bilən surətini çıxarmaq və sonra əmr vasitəsilə verilənlərə əlavə sütun əlavə etmək Sütun əlavə etmək ž – Fərdi sütun (Sütun əlavə et - Xüsusi Sütun). Formula daxiletmə pəncərəsində yeni sütunun adını (məsələn, Fragment2) və düstur kimi olduqca sadə ifadəni daxil edin:
=Cədvəl 2
… yəni, başqa sözlə, ikinci sorğunun adı:
Klikdən sonra OK yeni bir sütun görəcəyik, onun hər bir xanasında ikinci cədvəldən ifadələr olan iç içə bir cədvəl olacaq (sözün yanındakı xananın fonunda klikləsəniz, bu cədvəllərin məzmununu görə bilərsiniz. Masa):
Nəticədə sütunun başlığında ikiqat oxları olan düyməni istifadə edərək və işarəni silməklə bu iç-içə cədvəllərin bütün məzmununu genişləndirmək qalır. Orijinal sütun adından prefiks kimi istifadə edin (Prefiks olaraq orijinal sütun adından istifadə edin):
... və biz ilk iki dəstdən elementlərin bütün mümkün birləşmələrini alırıq:
Bundan əlavə, hər şey oxşardır. Formula ilə başqa hesablanmış sütun əlavə edin:
=Cədvəl 3
…, və sonra iç-içə olan cədvəlləri yenidən genişləndirin – və indi bizdə müvafiq olaraq üç dəstdən sözlərin dəyişdirilməsi üçün bütün mümkün variantlar var:
Tutmaqla soldan sağa hər üç sütunu seçmək qalır Ctrl, və əmrdən istifadə edərək boşluqlarla ayrılmış məzmunlarını birləşdirin Sütunları birləşdirin (Sütunları birləşdirin) tabdan Transformasiya (çevir):
Əldə edilən nəticələr artıq tanış olan komanda ilə yenidən vərəqə yüklənə bilər Əsas səhifə — Bağlayın və Yükləyin — Bağlayın və Yükləyin… (Ev — Bağla&Yüklə — Bağla&Yüklə..):
Gələcəkdə mənbə cədvəllərimizdə fraqmentlərlə bir şey dəyişərsə, nəticədə ortaya çıxan cədvələ sağ tıklayarak və əmri seçməklə yaradılan sorğunu yeniləmək kifayətdir. Yeniləyin və Saxlayın (Təzələmək) və ya klaviatura qısa yolunu basmaqla Ctrl+Dayandırmaq+F5.
- Power Query, Power Pivot, Power Map və Power BI nədir və nə üçün onlara Excel istifadəçisi lazımdır
- Power Query-də Gantt Diaqramının yaradılması
- INDEX funksiyasından istifadə etməyin 5 yolu