Fərz edək ki, sizdə müxtəlif dərəcələrdə “düzgünlük” ilə ilkin məlumatların - məsələn, ünvanlar və ya şirkət adlarının yazıldığı bir siyahı var:
 |  |
Aydın görünür ki, eyni şəhər və ya şirkət burada rəngarəng variantlarda mövcuddur ki, bu da gələcəkdə bu cədvəllərlə işləyərkən çoxlu problemlər yaradacaq. Bir az düşünsəniz, digər sahələrdən də oxşar tapşırıqların çoxlu nümunələri tapa bilərsiniz.
İndi təsəvvür edin ki, bu cür əyri məlumatlar sizə mütəmadi olaraq gəlir, yəni bu, birdəfəlik “əllə düzəldin, unudun” hekayəsi deyil, müntəzəm olaraq və çoxlu sayda hüceyrələrdə olan problemdir.
Nə etməli? Əyri mətni 100500 dəfə “Tap və Dəyişdir” xanası vasitəsilə və ya klikləməklə düzgün mətnlə əl ilə əvəz etməyin. Ctrl+H?
Belə bir vəziyyətdə ağla gələn ilk şey, yanlış və düzgün variantlara uyğun gələn əvvəlcədən tərtib edilmiş arayış kitabına uyğun olaraq kütləvi dəyişdirmə etməkdir - bu kimi:
Təəssüf ki, belə bir vəzifənin aşkar yayılması ilə Microsoft Excel-də onu həll etmək üçün sadə daxili üsullar yoxdur. Başlamaq üçün, VBA və ya Power Query-də makro şəklində "ağır artilleriya" cəlb etmədən bunu düsturlarla necə edəcəyimizi anlayaq.
Case 1. Toplu tam dəyişdirmə
Nisbətən sadə bir vəziyyətdən başlayaq - köhnə əyri mətni yenisi ilə əvəz etməli olduğunuz bir vəziyyət. tam.
Tutaq ki, iki cədvəlimiz var:
Birincisi - şirkətlərin orijinal rəngarəng adları. İkincisi - yazışmaların arayışı. Birinci cədvəldə şirkətin adında sütundan hər hansı bir söz tapsaq Tapmaq, onda siz bu əyri adı tam olaraq düzgün adla əvəz etməlisiniz – sütundan Əvəz ikinci axtarış cədvəli.
Rahatlıq üçün:
- Hər iki cədvəl klaviatura qısa yolundan istifadə edərək dinamikə (“ağıllı”) çevrilir Ctrl+T və ya komanda Daxil et - Cədvəl (Daxil et - Cədvəl).
- Görünən tabda konstruktor (Dizayn) adlı ilk cədvəl Tarix, və ikinci istinad cədvəli - Əvəzedicilər.
Düsturun məntiqini izah etmək üçün bir az uzaqdan gedək.
Nümunə olaraq A2 xanasındakı ilk şirkəti götürərək və qalan şirkətləri müvəqqəti olaraq unudaraq, sütundan hansı variantı təyin etməyə çalışaq. Tapmaq orada görüşür. Bunu etmək üçün vərəqin boş hissəsində istənilən boş xananı seçin və orada funksiyanı daxil edin TAPMAQ (TAPA):
Bu funksiya verilmiş alt sətirin daxil olub-olmadığını müəyyən edir (birinci arqument sütundakı bütün dəyərlərdir Tapmaq) mənbə mətnə (məlumat cədvəlindəki ilk şirkət) və ya mətnin tapıldığı simvolun sıra nömrəsini, ya da alt sətir tapılmadıqda xətanı çıxarmalıdır.
Burada hiylə ondan ibarətdir ki, biz birinci arqument kimi bir yox, bir neçə dəyər göstərdiyimiz üçün bu funksiya da nəticədə bir dəyər deyil, 3 elementdən ibarət massiv qayıdacaq. Dinamik massivləri dəstəkləyən Office 365-in ən son versiyası yoxdursa, bu formula daxil etdikdən və üzərinə kliklədikdən sonra Daxil edin bu massivi birbaşa vərəqdə görəcəksiniz:
Excel-in əvvəlki versiyaları varsa, kliklədikdən sonra Daxil edin biz yalnız nəticə massivindən ilk dəyəri görəcəyik, yəni #VALUE xətası! (#VALUE!).
Qorxmamalısınız 🙂 Əslində, bizim düsturumuz işləyir və düsturlar sətrinə daxil edilmiş funksiyanı seçib düyməni sıxsanız, yenə də bütün nəticələr massivinə baxa bilərsiniz. F9(sadəcə basmağı unutmayın Escdüstura qayıtmaq üçün):
Nəticələrin nəticəsi o deməkdir ki, orijinal əyri şirkət adında (GK Morozko OAO) sütundakı bütün dəyərlərdən Tapmaq yalnız ikincisini tapdı (Morozko), və ardıcıl 4-cü simvoldan başlayaraq.
İndi düsturumuza funksiya əlavə edək VIEW(AXTAR):
Bu funksiyanın üç arqumenti var:
- İstənilən dəyər – istənilən kifayət qədər böyük rəqəmdən istifadə edə bilərsiniz (əsas odur ki, o, mənbə məlumatındakı istənilən mətnin uzunluğunu üstələyir)
- Baxılan_vektor – istədiyimiz dəyəri axtardığımız diapazon və ya massiv. Budur, əvvəllər təqdim edilmiş funksiya TAPMAQ, {#VALUE!:4:#VALUE!} massivini qaytaran
- vektor_nəticələr – uyğun xanada istədiyiniz dəyər tapılarsa, dəyəri qaytarmaq istədiyimiz diapazon. Budur sütundan düzgün adlar Əvəz istinad cədvəlimiz.
Burada əsas və qeyri-aşkar xüsusiyyət funksiyası olmasıdır VIEW dəqiq uyğunluq yoxdursa, həmişə ən yaxın ən kiçik (əvvəlki) dəyəri axtarır. Buna görə də, istənilən böyük rəqəmi (məsələn, 9999) istədiyiniz dəyər kimi göstərərək, məcbur edəcəyik VIEW {#VALUE!:4:#VALUE!} massivində ən yaxın ən kiçik ədədi (4) olan xananı tapın və nəticə vektorundan müvafiq dəyəri, yəni sütundan düzgün şirkət adını qaytarın Əvəz.
İkinci nüans odur ki, texniki cəhətdən bizim düsturumuz massiv düsturudur, çünki funksiya TAPMAQ nəticə olaraq bir deyil, üç dəyərdən ibarət massiv qaytarır. Ancaq funksiyadan bəri VIEW massivləri qutudan kənarda dəstəkləyir, onda biz bu düsturu klassik massiv düsturu kimi daxil etməli deyilik – klaviatura qısa yolundan istifadə etməklə Ctrl+ÜstKrkt+Daxil edin. Sadə biri kifayət edəcək Daxil edin.
Hamısı budur. Ümid edirəm məntiqi başa düşəcəksiniz.
Hazır formulu sütunun ilk B2 xanasına köçürmək qalır Sabit - və vəzifəmiz həll olundu!
Əlbəttə ki, adi (ağıllı deyil) cədvəllərlə bu düstur da əla işləyir (sadəcə açarı unutma F4 və müvafiq bağlantıların düzəldilməsi):
Hal 2. Toplu qismən dəyişdirmə
Bu iş bir az daha mürəkkəbdir. Yenə də iki “ağıllı” masamız var:
Düzəliş edilməli olan əyri yazılmış ünvanları olan ilk cədvəl (mən onu çağırdım Məlumat2). İkinci cədvəl bir istinad kitabıdır, ona görə ünvanın içərisindəki alt sətirin qismən dəyişdirilməsi lazımdır (bu cədvələ zəng etdim) Əvəzetmələr2).
Buradakı əsas fərq ondan ibarətdir ki, siz orijinal məlumatın yalnız bir hissəsini əvəz etməlisiniz – məsələn, ilk ünvanda səhv var “St. Peterburq” sağda “St. Peterburq”, qalan ünvanı (poçt indeksi, küçə, ev) olduğu kimi tərk edin.
Hazır düstur belə görünəcək (qavrayış asanlığı üçün istifadə edərək neçə sətirə böldüm Dayandırmaq+Daxil edin):
Burada əsas işi standart Excel mətn funksiyası yerinə yetirir Substitute (ƏVƏZİNƏ), 3 arqumenti var:
- Mənbə mətni – Ünvan sütunundan ilk əyri ünvan
- Axtardığımız şey - burada funksiya ilə hiylədən istifadə edirik VIEW (AXTAR)sütundan dəyəri çəkmək üçün əvvəlki yoldan Tapmaq, əyri ünvanda fraqment kimi daxil edilir.
- Nə ilə əvəz etmək lazımdır - eyni şəkildə sütundan ona uyğun olan düzgün dəyəri tapırıq Əvəz.
Bu formula ilə daxil edin Ctrl+ÜstKrkt+Daxil edin burada da lazım deyil, baxmayaraq ki, o, əslində massiv formuludur.
Və aydın görünür (əvvəlki şəkildəki #N/A səhvlərinə baxın) belə bir formulun bütün zərifliyinə baxmayaraq, bir neçə çatışmazlıqları var:
- Function SUBSTITUTE hərflərə həssasdır, buna görə də sondan əvvəlki sətirdəki “Spb” əvəzedici cədvəldə tapılmadı. Bu problemi həll etmək üçün ya funksiyadan istifadə edə bilərsiniz ZAMENIT (ƏYİN), və ya əvvəlcədən hər iki cədvəli eyni reyestrə gətirin.
- Mətn əvvəlcə düzgündürsə və ya içindəki əvəz etmək üçün heç bir fraqment yoxdur (sonuncu sətir), onda düsturumuz xəta atır. Bu an funksiyadan istifadə edərək səhvləri tutmaq və əvəz etməklə zərərsizləşdirilə bilər XƏTA (İFRAR):
- Orijinal mətn varsa kataloqdan eyni anda bir neçə fraqment, onda düsturumuz yalnız sonuncunu əvəz edir (8-ci sətirdə, Ligovski «Avenue« olaraq dəyişdirildi "pr-t", Amma "S-Pb" on “St. Peterburq” artıq yox, çünki “S-Pb” kataloqda daha yüksəkdir). Bu problemi öz düsturumuzu yenidən işlətməklə həll etmək olar, lakin artıq sütun boyunca Sabit:
Yerlərdə mükəmməl və çətin deyil, lakin eyni əl ilə dəyişdirmədən daha yaxşıdır, elə deyilmi? 🙂
PS
Növbəti məqalədə biz makrolar və Power Query istifadə edərək belə bir toplu əvəzetmənin necə həyata keçiriləcəyini anlayacağıq.
- Mətni əvəz etmək üçün Əvəz etmə funksiyası necə işləyir
- EXACT funksiyasından istifadə edərək dəqiq mətn uyğunluqlarının tapılması
- Hərflərə həssas axtarış və əvəzləmə (həssas VLOOKUP)