Excel matn funksiyalari: ma’lumotni ajratish, birlashtirish va tozalash

Qaysi funksiya qaysi vazifani hal qiladi

Jadvaldagi «iflos» matnda odatda uchta muammodan biri bo‘ladi: ortiqcha belgilar, bitta katakda bir nechta qiymat yoki bitta qiymat bir nechta katakka tarqalgan. Har biri uchun o‘z vositasi bor:

Tartib muhim: avval tozalash, keyin ajratish yoki birlashtirish. Aks holda bitta ortiqcha bo‘shliq «Aziz» va «Aziz » ni ikki xil qiymatga aylantiradi, qidiruv formulalari va pivot jadvallar ularni moslashtirmay qo‘yadi.

Quyidagi formulalarda argumentlar vergul bilan ajratilgan. Ba’zi mintaqaviy sozlamalarda Excel nuqtali vergulni kutadi: =LEFT(A2;5).

Tozalash: TRIM, CLEAN va SUBSTITUTE

• TRIM satr boshi va oxiridagi bo‘shliqlarni olib tashlaydi, so‘zlar orasidagi takroriy bo‘shliqlarni bittaga qisqartiradi.
• CLEAN chop etilmaydigan belgilarni o‘chiradi: boshqa tizimlardan eksport bilan keladigan qator ko‘chishlari va xizmat kodlari.
• SUBSTITUTE bir bo‘lakni boshqasiga almashtiradi — hamma joyda yoki faqat N-chi uchrashuvda.

Ko‘p uchraydigan tuzoq: saytlardan nusxalangan matnda uzilmas bo‘shliq (160-belgi) bo‘ladi. TRIM uni ko‘rmaydi, shuning uchun avval uni oddiy bo‘shliqqa almashtiring:

=TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

F.I.Sh.ni ajratish

Aytaylik, A ustunida «Karimov Aziz Baxtiyorovich» yozilgan.

TEXTSPLIT (Microsoft 365 va brauzerdagi Excel) satrni ajratuvchi bo‘yicha bo‘ladi va qismlarni qo‘shni kataklarga chiqaradi:

=TEXTSPLIT(TRIM(A2), " ")

Natijada uchta ustun hosil bo‘ladi: familiya, ism, otasining ismi. O‘ng tomondagi kataklar bo‘sh bo‘lishi kerak, aks holda #SPILL! xatosi chiqadi.

Eski versiyalarda LEFT, MID va RIGHT ni belgi o‘rnini qaytaradigan FIND bilan birga ishlating:

Familiya:      =LEFT(A2, FIND(" ", A2) - 1)
Ism:           =MID(A2, FIND(" ", A2) + 1, FIND(" ", A2, FIND(" ", A2) + 1) - FIND(" ", A2) - 1)
Otasining ismi: =RIGHT(A2, LEN(A2) - FIND(" ", A2, FIND(" ", A2) + 1))

Agar F.I.Sh. faqat ikki so‘zdan iborat bo‘lsa, bu formulalar xato beradi, shuning uchun ularni IFERROR ichiga oling yoki bo‘shliqlar sonini oldindan tekshiring.

LEFT, MID va RIGHT qat’iy formatdagi kodlar uchun ham qulay: SKU-0153-RED dan =MID(A2, 5, 4) formulasi 0153 ni qaytaradi.

Telefon raqamlarini bir formatga keltirish

Mijozlar bazasida telefonlar odatda turlicha yozilgan: 90 123-45-67, (90) 1234567, +998901234567. Ularni yagona ko‘rinishga keltirish uchun raqamlardan boshqa hamma narsani olib tashlang, oxirgi 9 ta raqamni oling va mamlakat kodini qo‘shing:

=LET(
  d, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), "-", ""), "(", ""), ")", ""), "+", ""),
  "+998" & RIGHT(d, 9)
)

LET Microsoft 365 da mavjud. Eski versiyalarda SUBSTITUTE zanjirini yordamchi ustunga chiqaring. Keyin uzunlikni tekshiring: =LEN(B2)=13 kiritishda bitta raqami tushib qolgan raqamlarni tezda ko‘rsatadi.

Birlashtirish: TEXTJOIN va &

TEXTJOIN diapazonni ajratuvchi orqali birlashtiradi va bo‘sh kataklarni o‘tkazib yubora oladi:

=TEXTJOIN(" ", TRUE, B2:D2)

Ikkinchi argument TRUE bo‘sh qiymatlarni e’tiborsiz qoldiradi, shuning uchun otasining ismi bo‘lmasa, ikkita bo‘shliq qolmaydi. Ikki-uch qiymat uchun & operatori yetarli: =B2 & " " & LEFT(C2, 1) & "." natijasi «Karimov A.».

TEXTJOIN ro‘yxatni bitta katakka yig‘ish uchun ham qulay — masalan, jamoaning barcha email manzillarini «; » orqali pochta mijoziga qo‘yish uchun.

Flash Fill: formulasiz, namuna bo‘yicha

Flash Fill (Data → Flash Fill yoki Ctrl+E) birinchi bir-ikki katakka nima yozganingizni ko‘radi va shablonni davom ettiradi. «Karimov Aziz Baxtiyorovich» yoniga «Aziz» deb yozing, Ctrl+E ni bosing — Excel butun ustun uchun ismlarni to‘ldiradi.

Qanday tanlash kerak:

• Flash Fill — bir martalik tozalash uchun. Natija statik qiymatlar: manba o‘zgarsa, hech narsa qayta hisoblanmaydi.
• Formulalar — ma’lumot muntazam yangilansa yoki importdan kelsa. Natija avtomatik yangilanadi.
• Flash Fill natijasini nostandart qatorlarda doim tekshiring: Excel shablonni taxmin qiladi va istisnolarda adashishi mumkin.

Ko‘p uchraydigan xatolar

• Tozalashdan oldin ajratish. Ikki karra bo‘shliqlar bo‘sh ustunlar va ma’lumot siljishini keltirib chiqaradi.
• Manbani ustidan yozish. Asl ustunni saqlang va yonida ishlang; qiymatlarga faqat oxirida aylantiring (Copy → Paste Special → Values).
• Telefonlarni son sifatida saqlash. Excel boshidagi «+» ni tashlab yuboradi va uzun sonni eksponensial ko‘rinishda ko‘rsatishi mumkin. Raqamlarni matn sifatida saqlang.
• Aralash ajratuvchilar. Bitta ustunda vergul, nuqtali vergul va slesh. TEXTSPLIT ajratuvchilar massivini qabul qiladi: =TEXTSPLIT(A2, {",", ";", "/"}).