Excelda bosh harflarni qanday qilish kerak. Ism va familiyani birlashtirish. Ushbu videoda matnni ustunlarga bo'lish batafsil ko'rsatilgan

Ko'pincha, shartnoma, hisob-faktura, tijorat taklifi yoki boshqa hujjatni tuzishda biz mijozning familiyasi, ismi va otasining ismi bilan ishlaymiz. Ba'zan hujjat matnida mijozga murojaat qilish uchun to'liq ismni ajratish va faqat ismni olish kerak bo'ladi, ba'zan siz familiya va bosh harflarni qoldirib, imzo qo'ygan ustunni yaratishingiz kerak;

Albatta, siz to'liq ismingizdan kerakli ma'lumotlarni chiqaradigan formulani yozishingiz mumkin, lekin hatto tajribali odam ham buni amalga oshirish uchun 5-7 daqiqa kerak bo'ladi. Agar matndagi familiyaning joylashuvini oldindan bilmasangiz nima bo'ladi? VBA-Excel plaginidan tayyor funksiyalardan foydalanish ancha oson va qulayroq.

To'liq ismda familiyani toping

Qo'shimchani o'rnatgandan so'ng, yangi funktsiya mavjud bo'ladi = FAMILASI (FULL IS), bu erda

• To'liq ism - matn yoki to'liq ismni o'z ichiga olgan katakka havola

Ushbu funktsiyaning maqsadi hujayradan faqat familiyani chiqarishdir. Bundan tashqari, buyurtma uning uchun muhim emas. Familiya matnning boshida, oxirida yoki o'rtasida paydo bo'lishi mumkin.

To'liq ismni toping

=NAME(to'liq ism) nomi uchun shunga o'xshash funktsiya. U qaerda joylashganligidan qat'i nazar, to'liq ismli matndan nom chiqaradi.

To'liq ismda otasining ismini toping

Ushbu seriyadagi oxirgi funktsiya ota ismini chiqarish uchun =PATERNICAL(FULL NAME) funktsiyasidir.

Excelda to'liq ism bilan ishlashga misollar Familiya va bosh harflarni ko'rsatish

Hujjatlarni rasmiylashtirishda keng tarqalgan vazifalardan biri bu familiyaning ismi otasining ismi matnini Familiyasi I.O.ga o'zgartirishdir.

Excel 2007 da matnni ustunlarga ikki, uch yoki undan ortiq hujayralarga qanday ajratish mumkin. Odatda, misol sifatida, ular ma'lumotlar alohida bo'lishi uchun to'liq nomga ega bo'lgan hujayralar bo'linmasidan foydalanadilar.

Excelda matnni ustunlarga qanday ajratish mumkin

Ishda men vaqtni excel jadvalining kataklariga bo'lishim kerak edi, masalan: 0:00-15:00. Va qiymatlar orasida ajratuvchi nuqtali vergul 0:00-15:00; 16:00-17:00; 22:00-24:00. Ushbu ma'lumotlar kun bo'yi jadvalda, hujayralarda ajratuvchi o'rtasida turli xil qiymatlar mavjud. Har bir kun uchun vaqtni bo'lish, vaqtni sana ostidagi ustunga qo'yish, vaqtni boshi va oxiriga bo'lish, farqni hisoblash va barchasini qo'shish kerak edi...

Birinchi bosqich. Vaqtni hujayralarga bo'lish

Yacheykalar diapazonini tanlang, lentada Ma'lumotlar-ustunlar bo'yicha matnni bosing, Matn ustasi paydo bo'ladi.

Biz hech narsani o'zgartirmaymiz, agar kalitingiz o'rnida bo'lsa: ajratuvchi bilan keyingisini bosing.

"Yorliq" belgisini olib tashlang va "boshqa" katagiga belgi qo'ying

va nuqtali vergul [; ]. Keyinroq bosing, garchi u erda ko'rish uchun hech qanday maxsus narsa yo'q, aniqrog'i, tayyor tugmasini bosing. Savol tug'iladi: hujayralar tarkibini almashtirasizmi? Javob ha!

Biz hujayrani [ ; ]. C ustunining o'ng tomonidagi katakchalar soniga sana ko'rsatilgan katak ostiga bo'sh qatorlarni qo'shamiz.

Ikki katak, ikkita qator. Hujayralarni tanlash, nusxalash,

va birinchi katak ostida "Maxsus joylashtirish" - "O'tkazish" ni o'ng tugmasini bosing.

Ma'lumotlar satrdan ustunga o'tkaziladi, qolgan katakchalar uchun ham xuddi shunday qilamiz.

Hujayradagi matnni bo'lish, uni kun bo'yicha, ustunga joylashtirish uchun chiqdi.

Ikkinchi bosqich. Excelda hujayrani boshlanish va tugash vaqtiga qanday ajratish mumkin

Keling, matn ustasidan yana foydalanib, vaqt farqini olamiz. Davom etaylik matnni ajratish boshlanish va tugash vaqtlari uchun hujayralarda. Bu erda ajratuvchi minus belgisidir.

Bir-birining yonidagi katakchalarni nusxalash va joylashtirish orqali ustunni jadvalimizdan chiqaramiz. Biz sehrgar bilan ishlaymiz: Ma'lumotlar-Matn ustunlar bo'yicha, birinchi qadam hech narsani o'zgartirmaydi, ikkinchi bosqichda boshqa [ - ] oynasida biz minus belgisini qo'yamiz, tayyor tugmasini bosing.

Uchinchi bosqich

Biz formulani qo'yamiz: tugash vaqti minus boshlanish vaqti,

hujayra formati Barcha formatlar [h]:mm,

Keling, formulani oxirigacha tortamiz. Vaqtni soatlab oldik. Keyingi katakchaga yozamiz: =K4*60 Enter. Keling, vaqtni daqiqalarda bilib olaylik.

To'g'ri, soniyalar bilan... Men ustunni nusxalash, uni bloknotga joylashtirish, CTRL+H (Topish va almashtirish) tugmalarini bosishdan ko'ra yaxshiroq yo'l topa olmadim, findda yozgan:00

va almashtirishni bo'sh qoldirdi ...

Hammasini almashtirishni bosdim... Matn: 00siz bloknotdan ko'chirildi va jadvalga joylashtirildi.

Men soatlar soni bilan osonroq qildim, uni bloknotga ko'chirdim va jadvalga joylashtirdim. Bitta muammo: 24 soat 24:00:00 ga teng.

Muammo emas, chunki biz Excelda katakchani qanday ajratishni bilamiz, u holda biz bu erda Copy & Paste dan foydalanishimiz mumkin, ammo hiyla bilan - 24:00 dan oldin katakchaga apostrof qo'ying ' -bitta tirnoq "24:00"

va uni hamma joyga yopishtiring. Siz faqat avtosum bilan daqiqalarni qo'shishingiz mumkin, biz daqiqalar yig'indisini 60 ga bo'lish orqali soatlar yig'indisini olamiz va natijada soatlar sonini 24 ga bo'lamiz.

Mening misolimda 31 kundan bir narsa 28 kun ishlagan, 744 soatdan -673 soat va 44640 daqiqadan -40411 daqiqa... Nima bo'lishi muhim emas, asosiysi bu misolda siz Excelda matnni katakchadagi ustunlarga ajratishni o'rgandim, Qanday qilib bloknotni nostandart usulda ishlatish va ularga apostrof qo'shish orqali hujayralar formatini o'zgartirish mumkin?

Ushbu videoda matnni ustunlarga bo'lish batafsil ko'rsatilgan:

Videoni to'liq ekran rejimida ko'rishni tavsiya qilamiz, sifat sozlamalarida 1080 HD ni tanlang, unutmang YouTube kanaliga obuna bo'ling, u erda siz tez-tez chiqadigan juda ko'p qiziqarli videolarni topasiz. Tomosha qilishdan zavqlaning!

Ustunlarda ism, otasining ismi va familiyasi turli kataklarda bo'lishi uchun. Excelda matnni ustunlarga qanday ajratish mumkin?

Shunday qilib, matnni ustunlarga bo'lishning ikki yo'li mavjud.

Matnni ustunlarga ajratishning 1-usuli. Matn ustasi

Ajramoqchi bo'lgan matnli ustunni tanlang. Keyin yuqori panelda Ma'lumotlar - Ustunlar bo'yicha matnni tanlang . Matn ustasi (tahlil qilish) oynasi paydo bo'ladi.

Agar siz matnni bo'shliqlar yoki boshqa belgilar bilan ajratmoqchi bo'lsangiz, "cheklangan" ni tanlang.

Ochilgan menyuda bo'sh joy yoki kerakli belgini tanlang.

Matn formatini tanlang yoki "bajarildi" tugmasini bosing. Matn bo'lingan!

Agar barcha ma'lumotlar bir xil uzunlikda bo'lsa, siz belgilangan kenglik bo'yicha bo'lishingiz mumkin (2-rasmga qarang)

2-usul. Formulalar yordamida Excelda matnni ajratish. PSTR funktsiyasi

Agar siz formula yordamida matnni ustunlarga bo'lishingiz kerak bo'lsa, =PSTR() va =FIND() funksiyalaridan foydalaning.

Topish uchun Familiya formulani B2 katakka kiriting

=PSTR(A2,1,TOPISH(" ",A2,1)-1)

Bu erda Find funktsiyasi bo'sh joy oldidagi belgilar sonini topadi va =PSTR() funktsiyasi bu son belgilarni qaytaradi.

Olish uchun Ism formula murakkablashadi (funksiyalarga nom qayerdan boshlanishini, ya'ni 1-bo'shliq tugashini bilishingiz kerak), shuning uchun C3 katakchani kiriting.

=PSTR(A2,TOPISH(" ";A2,1)+1;TOPISH(" ";A2;TOPISH(" ";A2,1)+1)-TOPISH(" ";A2,1))

Topmoq Familiya hujayraga D3 formulani kiriting

=PSTR(A2,I2+1,50) =PSTR(A2,TOPI(" ",A2,TOPISH(" ",A2,1)+1)+1,50) olamiz

Bu biroz qiyin ko'rinadi, lekin agar qarasangiz, hamma narsa qiyin emasligini ko'rasiz, ayniqsa misol bilan:

Hujayralarni qanday bog'lash mumkin Ismi, otasining ismi, familiyasi

Hujayralardagi ma'lumotlarni birlashtirish uchun = CONCATENATE () funktsiyasidan yoki & (ampersand) - birikma belgisi yordamida tuzilmadan foydalaning, masalan, D3 katakchaga formulani kiriting.

=B2&" "&C2

Bu savol juda tez-tez so'raladi

Excel sizga ustun kataklaridagi matnni bir nechta pastki qatorlarga bo'lish va ularni alohida ustunlarga joylashtirish imkonini beradi. Bunday holda, har bir aniq holat uchun foydalanuvchi tomonidan tanlanadigan buzilish uchun maxsus ajratgich qo'llaniladi.

Masalan, CSV fayl ma'lumotlarini ko'rib chiqing. Undagi har bir pastki qator nuqta-vergul bilan ajratiladi va qo'sh tirnoq ichiga joylashtiriladi. Ularni ish kitobingizning istalgan joyiga joylashtiring:

"To'liq ism" "Tug'ilgan sana" "Yashash joyi";
"Ivanov Ivan Ivanovich";"27.03.1985";"Erkak";"Voronej";"Elektromontyor"
"Petrov Petrovich";"12.03.1976";"Erkak";"Moskva";"Geolog"
"Kuxina Elizaveta Fedorovna";"12/11/1994";"Ayol";"Qozon";"Doktor"

Ma'lumotlarni parchalashdan oldin siz kerakli diapazonni tanlashingiz kerak, so'ngra "Ma'lumotlar" yorlig'i lentasidagi "Ma'lumotlar bilan ishlash" maydonidagi "Ustunlar bo'yicha matn" belgisini bosing.

Ekranda matnni tarqatish ustasi oynasi paydo bo'ladi:

Butun jarayon uch bosqichdan iborat.

Birinchi bosqichda siz bo'linish uchun ma'lumotlar formatini tanlashingiz kerak, ya'ni matnda cheklovchilar mavjudligini yoki har bir kelajakdagi ustunning o'ziga xos sobit kengligi borligini ko'rsating, ya'ni. matn uzunligi. Oxirgi variant ko'rib chiqilmaydi, chunki u birinchisidan farq qilmaydi, keyingi bosqichdan tashqari, siz o'lchagichdagi maydonning kengligini mustaqil ravishda tanlashingiz kerak.

Cheklangan-ni tanlang va Keyingiga bosing.

Keyingi qadam ajratuvchi belgini tanlashdir. Bu har qanday belgilar, jumladan, harflar va raqamlar bo'lishi mumkin.

E'tibor bering, agar ajratuvchilar ketma-ket bo'lsa, unda ularning har biri yangi ustun hosil qiladi, ya'ni. 2 ta bunday belgilar 1 ta bo'sh ustun, 3 - 2 va hokazolarni yaratadi. Ushbu tez-tez zarur bo'lgan harakatdan xalos bo'lish uchun "Ketma-ket ajratuvchilarni bitta deb hisoblash" katagiga belgi qo'ying.

Ustun yaratish uchun pastki qatorda ajratuvchi belgi bo'lmagan ajratuvchi belgi bo'lgan vaziyat yuzaga kelishi mumkin. Bunday hollarda satrlar bitta yoki qo'sh tirnoq ichiga olinishi kerak (bu bizning misol manba ma'lumotlarimizda mavjud) va sehrgar oynasida qaysi chiziq chegaralagichni qo'llashni tanlashingiz kerak.

Shuni yodda tutingki, ajratuvchilar va ajratuvchilar ajratilgan ma'lumotlar tarkibidan o'chiriladi.

Yuqoridagi amallarning har birida Excelga ustun kataklaridagi matnni ajratishni tugatishga ruxsat berish uchun Finish tugmasini bosishingiz mumkin. Ammo agar siz butun jarayonni nazorat qilmoqchi bo'lsangiz, "Keyingi" tugmasini bosing.

Yakuniy qadam yangi ustunlarga formatlashni belgilash va ularni qaerga joylashtirishni ko'rsatishdir.

Faqat bir nechta formatlar (ma'lumotlar turlari) taqdim etiladi va tarqatilgan matn faqat joriy varaqda joylashtirilishi mumkin.

Agar sayt materiallari sizga yordam bergan bo'lsa, iltimos, loyihani qo'llab-quvvatlang, shunda biz uni yanada rivojlantirishimiz mumkin.

Sizda fikr bildirish uchun yetarli huquqingiz yo‘q.

Bu erda biz Excel katakchasidagi matnni bir nechta kataklarga qanday ajratishni ko'rib chiqamiz. Masalan, birinchi katakdagi jumla so'zlarga ko'ra boshqa bir nechta katakchalarga, to'liq ism, sana va boshqalarga tarqalishi kerak.
Misol uchun, bizda bitta ustunda familiyalar va ismlar ro'yxati mavjud. Bir ustunga familiyalarni, boshqa ustunga ismlarni yozishimiz kerak. Masalan, ro'yxatdagi odamlarning jinsini aniqlash. Bu haqda ko'proq ma'lumotni "Excel-da ism bo'yicha jinsni aniqlang" maqolasida o'qing.
Matnni yoki butun ustunni ajratmoqchi bo'lgan katakchalarni tanlang.
"Ma'lumotlar bilan ishlash" bo'limidagi "Ma'lumotlar" yorlig'iga o'ting va "Ustunlar bo'yicha matn" funksiyasini tanlang. Ko'rsatilgan "Matn ustasi" oynasida "Manba ma'lumotlar formati" qatorida "Ajratish vositalari bilan" ni belgilang. “Keyingi” tugmasini bosing.

Bu erda biz "Ajratuvchi belgi" - "bo'sh joy" qatorida ko'rsatamiz. Biz "bo'sh joy" qo'yamiz, chunki bizning ro'yxatimizda ism va familiyalar bo'sh joy bilan ajratilgan. Agar ism va familiya (boshqa so'zlar) vergul bilan ajratilgan bo'lsa, biz ajratuvchi "vergul" ni ko'rsatamiz.

Ushbu oynada "Ma'lumotlarni tahlil qilish namunasi" bo'limida siz qanday ustunlarni olishimizni darhol ko'rishingiz mumkin. “Keyingi” tugmasini bosing.
Bu erda, "Joylash:" bo'limida biz yangi ma'lumotlar mos keladigan ustunlar oralig'ini ko'rsatamiz.

Diqqat!

Ushbu diapazondagi ustunlar bo'sh bo'lishi kerak, aks holda ulardagi ma'lumotlar o'chiriladi va yangilari bilan almashtiriladi. Agar bo'sh ustunlar bo'lmasa, ustunlarni oldindan kiritishingiz mumkin. Buni qanday qilish kerak, "Excelda qator, ustunni qanday qo'shish kerak" maqolasiga qarang.
Ustun katakchalari formatini belgilashingiz mumkin. Misol uchun, agar siz ustunni kunlar va oylar, yillarga ajratsangiz, "Ustun ma'lumotlari formati" - "sana" ni belgilashingiz mumkin.
"Finish" tugmasini bosing. Bu shunday chiqdi.
Shunday qilib, siz bitta katakdagi matnni ko'proq ustunlarga bo'lishingiz mumkin. Misol uchun, bu erda biz sanani alohida ustunlarga ajratdik - kun, oy, yil.

Aniqroq bo'lish uchun siz Excel jadvalidagi ba'zi qatorlarni ajratib ko'rsatishingiz va ustunlarni (masalan, jami) boshqa rang bilan, ajratuvchi chiziq bilan ajratib ko'rsatishingiz mumkin. Qo'shimcha ma'lumot uchun "Excel jadvalidagi qatorlarni ajratish chizig'i" maqolasiga qarang.
Hujayrani diagonal bo'lib, har bir qismga matn yozishingiz mumkin. Buni qanday qilishni "Excelda hujayrani qanday ajratish mumkin" maqolasida o'qing.
Hujayralarda nol ko'rinmaydigan jadvalda ishlash qulayroqdir. Qanday qilib ularni olib tashlash, yashirish, almashtirish, maqolani o'qing "

Funktsiya parametr sifatida "Familiya ismi otasining ismi" ko'rinishidagi matn qatorini oladi va ism va otaning ismini kesib, faqat bosh harflarni qoldiradi - "Familiya I. O" shaklida.

Bu funksiya Excel ish varag'i kataklarida UDF (Foydalanuvchi tomonidan belgilangan funksiya) sifatida ishlatilishi mumkin.
(ilovadagi misolga qarang)

Funktsiya xususiyatlarining tavsifi - forumda: programmersforum.ru/showpost.php?p=757147&postcount=6

To'liq ismni qismlarga bo'lishning aniq rasmiy algoritmi yo'q.
Ona tilida so'zlashuvchining nasabnomasi va tilini bilmasdan turib, familiyani berilgan ismdan rasmiy ravishda ajratib bo'lmaydi. Siz faqat turli xil evristikalardan foydalanishingiz mumkin.

Bundan tashqari, turli organlar (turli vaqtlarda) ma'lum bir holatda bosh harflar qanday ko'rinishi kerakligi haqida turli xil qarashlarga ega edi.
Federal migratsiya xizmati hujjatlari boshqa narsa, Markaziy bank registratorlari boshqa va tijorat hujjatlarini tayyorlash boshqa narsa.
Shunday qilib, ba'zi xizmatlar Janubi-Sharqiy Osiyoning 2-3 ta murakkab qisqa nomlarini qisqartirmaslikni talab qiladi. Lekin ba'zilari yo'q.

Ha, bosh harflar o'ngda yoki chapda qaerda bo'lishi kerakligi haqidagi savol bilan - hech qanday noaniqlik yo'q. Yo'q, albatta, rus tilining qoidalari bor... Lekin u yoki bu tuzilmaning biznes aylanma qoidalari ustunlik qiladi (yoki oddiygina hujjatlarni qabul qiluvchi xodimning chap orqa oyog'i xohlaganidek)...

Ko'pchilikda savol tug'ilishi mumkin: bu hayvonot bog'i qayerdan keladi va nima uchun bularning barchasi kerak? Qoidaga ko'ra, bu Don Pedrosning barchasi Rossiya Federatsiyasi yurisdiktsiyasidan tashqarida ilgari berilgan hujjatlar asosida Rossiya pasportini olgan xorijiy fuqarolardir. Huquqiy holatlar boshqacha. Ko'pincha yangi rus pasportiga ona tilidan kirill alifbosiga transliteratsiya yoziladi.
Tataristonda -ogli va -qizi rus pasportlari beriladi. Men bir mijozni uchratdim, uning 2007 yildagi pasportiga ko'ra ismi Mustafo Oleg o'g'li...

Variant solishtiring Matnni umumiy funktsiya bosh harflari(lar satr sifatida , ixtiyoriy ToChap sifatida mantiqiy = noto'g'ri ) Dim sv Variant sifatida , sF String sifatida , sI sifatida String , sO qator sifatida , i As Long , k As Long Application.Volatile True If InStr(s) , "." ) > 0 Yoki Len(Trim$(s)) = 0 Keyin Bosh harflar = s "Ochiq ko'rsatilgan bosh harflar yoki bo'sh satr Chiqish Funktsiya End Agar "Kiritish satrini normallashtiring s = Replace(Application.Trim(s)), Chr( 30), "-" ) s = O'zgartirish(Almashtirish(lar, " -" , "-" ), "- " , "-" ) s = O'zgartirish(Almashtirish(lar, "" " , """ ), " "", """ ) " O "Genri Aleksandr; O" Genri Aleksandr; N" Homo; D" Trevil sv = Split(lar) sI = vbNullString: sO = vbNullString: sF = vbNullString i = UBound (sv) Agar i< 1 Then Инициалы = s: Exit Function Select Case sv(i) Case "оглы" , "кызы" , "заде" "бей, бек, заде, зуль, ибн, кызы, оглы, оль, паша, уль, хан, шах, эд, эль i = i - 1 sО = UCase(Left$(sv(i), 1)) & "." i = i - 1 Case "паша" , "хан" , "шах" , "шейх" i = i - 1 Case Else Select Case Right$(sv(i), 3) Case "вич" , "вна" If i >= 2 Keyin "Ruscha otasining ismi uchun standart tugatish sO = SropWord(sv(i)) Else "Ism turi Bosan Slavich sI = SropWord(sv(i)): sF = sv(0) End If i = i - 1 Case Else k = InStr(sv(i), "-" ) Agar k > 0 bo'lsa, Case Mid$(sv(i), k + 1) "ogly" , "kyzy" , "zade" , "ugli" , "uul" harfini tanlang " , "ool" "Nasabning "-ogly" va "-zade" turidagi varianti Mahmud-ogli sO = UCase(Left$(sv(i), 1)) & i = i - 1 Agar i = 0 boʻlsa." Keyin sI = sO sO = vbNullString End If End ElseIf i > 2 ni tanlang Keyin Case sv(i - 1) Case "ibn" , "ben" , "bin" ni tanlang sO = UCase(Left$(sv(i), 1) ) & "." " Usertal Alisher bin Sulaymon i = i - 2 End Select Else " Ben Eduard sI = UCase(Left$(sv(i), 1)) Agar Len(sv(i)) > 1 Keyin sI = sI & " ." i = i - 1 End If End Select End Tanlash Case sv(0) Case "de" , "del" , "dos" , "sept" , "van" , "von" , "tsu" Agar i > = 2 Keyin sF = sv(0) & " " & StrConv(sv(1), vbProperCase) sI = SropWord(sv(2)) Aks holda "De Nikolai If Len(sI) > 0 Keyin sF = sv(0 ) & " " & StrConv(sv(1), vbProperCase) Else sF = StrConv(sv(0), vbProperCase): sI = SropWord(sv(1)) Agar tugaydi Aks holda, agar Len(sF) = 0 bo'lsa, u holda "Ko'proq familiyani aniqlamadi sF = StrConv(sv(0), vbProperCase) Agar Len(sI) = 0 bo'lsa, sI = SropWord(sv(1)) End If End Tanlash Agar ToChap bo'lsa Bosh harflar = sI & sO & " " & sF Else Bosh harflar = sF & " " & sI & sO End Funktsiya Ommaviy funksiya SropWord(lar Variant sifatida) String sifatida Agar Len(lar) = 1 boʻlsa, SropWord = s Aks holda ss$ = UCase(Left$(s, 1)) ) & "." : k = InStr(s, "-" ) Agar k > 0 bo'lsa, ss$ = ss$ & "-" & Mid$(s, k + 1, 1) & CropWord = ss$ End If End funksiyasi

Qo'shimcha o'lchami

Menda xodimlar ro'yxatidan familiya yoki ism olish kerak bo'lgan yuzta holat bor edi. Shu bilan birga, ro'yxat kamida bir necha yuz kishidan iborat edi va uni qo'lda qilish juda achinarli edi. Excelda ushbu jarayonni optimallashtirish va muammoni bir necha daqiqada hal qilish imkonini beruvchi barcha kerakli vositalar mavjudligi yaxshi. Excel matn formulalari shunga o'xshash muammolarni hal qilish imkonini beradi. Endi men Google-ning birinchi sahifasi tomonidan taqdim etilgan bir nechta aktyorlar misolidan foydalanib, sizga hamma narsani batafsil tushuntiraman.

Excel matn formulalari bizga kerak

Ushbu muammoni hal qilish uchun bizga Excelning o'zi va quyidagi standart matn funktsiyalari kerak:

SEARCH - matndan matn yoki ma'noni topish imkonini beradi

LENGTH - matnli katakdagi belgilar sonini hisoblaydi

RIGHT - matn oxiridan belgilangan belgilar sonini qaytaradi

Bizning ayniqsa aqlli o'quvchimiz muammoimizni qanday hal qilishni allaqachon taxmin qilgan bo'lishi mumkin, ammo men hammaga bu menga qanday kerakligini bosqichma-bosqich tushuntirib beraman :)

Muammoni hal qilish uchun bizga kerak:

LENGTH funksiyasidan foydalanib, to‘liq nomdagi matn uzunligini hisoblang

SEARCH funksiyasidan foydalanib, familiya boshlanadigan belgi raqamini toping

Familiyaning uzunligini 1 va 2-bandlar natijalari orasidagi farq sifatida hisoblang

O‘RG‘ funksiyasidan foydalanib familiyani va uchinchi xatboshidan familiya uzunligini chiqaring.

Harakat rejasi kabi ko'rinadi. Qani boshladik!

DLST funktsiyasi

Ushbu Excel matn funksiyasi eng oddiylaridan biridir. U faqat hujayrani ko'rsatishi kerak va u undagi belgilar sonini beradi. Misol bilan o'zingiz ko'rishingiz mumkin:

SEARCH funksiyasi

Qidiruv funksiyasi biz uchun belgi yoki matn raqamini qidiradi. Barcha familiyalar turli harflar bilan boshlanishini hisobga olsak, biz otasining ismi va familiyasidan oldin bo'sh joy qidiramiz. Shundan keyingi keyingi belgi otasining ismi yoki familiyasining boshlanishi bo'ladi.

Shuni ta'kidlash kerakki, ushbu funktsiyadagi bo'sh joy yoki qidiruv matni qo'shtirnoq ichida yozilgan. Xususan, u shunday bo'ladi =SEARCH(" "; 'matnli katak').

Familiya ikkinchi bo'shliqdan keyin ekanligini hisobga olsak, biz ikkita qidiruvdan foydalanamiz, ulardan biri quyidagi misolda bo'lgani kabi birinchi bo'sh joyni topish uchun joylashtirilgan:

RIGHT funksiyasi

Biz hammamiz to'liq ismdan familiyani ajratib olishga va muammomizni hal qilishga tayyormiz. Buning uchun biz funktsiya argumentlarida aktyorning to'liq nomi bilan katakchani ko'rsatamiz va birinchi va ikkinchi harakatlar orasidagi farq sifatida belgilar sonini hisoblaymiz. Natijada, biz yechimni birlashtiramiz.

Ilgari biz misol sifatida to'liq nomni uning tarkibiy qismlariga bo'lish imkoniyatini ko'rib chiqdik. Buning uchun biz Excelning ustunlar bo'yicha matni vositasidan foydalandik.

Shubhasiz, bu Excelda juda muhim va foydali vosita bo'lib, ko'plab vazifalarni sezilarli darajada soddalashtiradi. Ammo bu usulning ozgina kamchiliklari bor. Agar, masalan, sizga doimiy ravishda ma'lum bir shaklda ma'lumot yuborilsa va siz uni doimiy ravishda bo'lishingiz kerak bo'lsa, unda bu ma'lum vaqtni oladi, bundan tashqari, agar ma'lumotlar sizga yana yuborilgan bo'lsa, unda siz hamma narsani qilishingiz kerak bo'ladi. yana operatsiyalar.

Agar biz to'liq ismni bo'lish misolini ko'rib chiqsak, oldingi maqolalarda muhokama qilgan va funktsiyasidan foydalangan holda Excel matn formulalari yordamida matnni ajratish mumkin bo'ladi. Bunday holda, ma'lumotni ma'lum bir ustunga kiritish kifoya qiladi va formulalar matnni kerakli tarzda avtomatik ravishda ajratadi. Keling, ushbu misolni ko'rib chiqaylik.

Bizda to'liq ismlar ro'yxati bo'lgan ustun mavjud, bizning vazifamiz familiya, ism va otasining ismini alohida ustunlarga joylashtirishdir.

Keling, harakat rejasini batafsil tavsiflashga harakat qilaylik va muammoni hal qilishni bir necha bosqichlarga bo'ling.

Avvalo, siz uchun tushunarli bo'lishi uchun biz oraliq hisob-kitoblar uchun yordamchi ustunlarni qo'shamiz va oxirida barcha formulalarni birlashtiramiz.

Shunday qilib, 1 va 2-chi bo'shliqlar ustunlarini qo'shamiz. FIND funksiyasidan foydalanib, oldingi maqolada aytib o'tganimizdek, biz birinchi bo'shliqning o'rnini topamiz. Buning uchun "H2" katagiga formulani yozamiz

FIND(" ";A2;1)

Endi biz ikkinchi bo'shliqning tartib raqamini topishimiz kerak. Formula bir xil bo'ladi, lekin bir oz farq bilan. Agar biz bir xil formulani yozsak, funktsiya bizga birinchi bo'shliqni topadi, lekin bizga ikkinchi bo'sh joy kerak. Bu FIND funksiyasidagi uchinchi argumentni - boshlang'ich pozitsiyasini - ya'ni funksiya qidirilayotgan matnni qidiradigan pozitsiyani o'zgartirish kerakligini anglatadi. Biz ikkinchi bo'shliq har qanday holatda birinchi bo'shliqdan keyin ekanligini ko'ramiz va biz birinchi bo'shliqning o'rnini allaqachon topdik, ya'ni birinchi bo'shliq pozitsiyasiga 1 qo'shish orqali biz FIND funksiyasiga bo'sh joyni qidirishni aytamiz. birinchi bo'shliqdan keyingi birinchi harfdan boshlab. Funktsiya quyidagicha ko'rinadi:

Keling, matnning birinchi qismini - Familiyalarni ajratishni boshlaylik

Buning uchun biz funktsiyadan foydalanamiz, sizga ushbu funktsiyaning sintaksisini eslatib o'taman:

PSTR(matn; boshlang'ich_pozitsiya; raqam_belgilar), bu erda

matn to'liq ism, bizning misolimizda bu A2 katak;

start_position - bizning holatlarimizda bu 1, ya'ni birinchi harfdan boshlanadi;

raqam_belgilar - familiya birinchi harfdan boshlab 1-bo'sh joygacha bo'lgan barcha belgilardan iborat ekanligini ko'ramiz. Va biz birinchi bo'shliqning o'rnini allaqachon bilamiz. Bu bo'shliqning o'zi minus 1 belgilar soni bo'ladi.

Formula quyidagicha ko'rinadi:

PSTR(A2 ;1 ;H2-1 )

Keling, matnning ikkinchi qismini - Ismni ajratishni boshlaylik

Yana =PSTR funksiyasidan foydalanamiz (matn; boshlang'ich_pozitsiya; raqam_belgilar), bu erda

matn bir xil to'liq nomli matn, bizning misolimizda bu A2 katak;

initial_position - bizning holatlarimizda Ism birinchi bo'shliqdan keyingi birinchi harf bilan boshlanadi, bu bo'shliqning o'rnini bilib, biz H2+1 olamiz;

raqam_belgilar - belgilar soni, ya'ni nomdagi harflar soni. Bizning ismimiz ikkita bo'shliq orasida ekanligini ko'ramiz, ularning pozitsiyalari bizga ma'lum. Agar birinchi bo'shliqning o'rnini ikkinchi bo'shliqning pozitsiyasidan ayirib tashlasak, farqni olamiz, bu nomdagi belgilar soniga teng bo'ladi, ya'ni I2-H2.

Yakuniy formulani olamiz:

PSTR(A2 ;H2+1 ;I2-H2 )

Keling, matnning uchinchi qismini - otasining ismini ajratishni boshlaylik

Va yana =PSTR funktsiyasi (matn; boshlang'ich_pozitsiya; raqam_belgilar), bu erda

matn bir xil to'liq nomli matn, bizning misolimizda bu A2 katak;

initial_position - Ota ismimiz 2-bo'shliqdan keyin joylashgan, ya'ni boshlang'ich pozitsiya ikkinchi bo'shliqning pozitsiyasiga plyus bitta belgi yoki I2+1 teng bo'ladi;

raqam_belgilar - bizning holatlarimizda otasining ismidan keyin hech qanday belgilar yo'q, shuning uchun biz shunchaki istalgan raqamni olishimiz mumkin, asosiysi bu otasining ismidagi belgilar sonidan kattaroqdir, men katta chegara bilan raqamni oldim - 50

Biz funktsiyani olamiz

PSTR(A2 ;I2+1 ;50 )

Keyin barcha uchta katakchani tanlang va formulalarni pastga torting va kerakli natijani oling. Bu yerda tugatishingiz mumkin yoki matn boʻlinish formulalarining oʻziga boʻshliqlar oʻrni boʻyicha oraliq hisob-kitoblarni yozishingiz mumkin. Buni qilish juda oson. Birinchi bo'shliq uchun hisoblash H2 katakchada ekanligini ko'ramiz - FIND(" ";A2;1) , ikkinchi bo'sh joy uchun esa I2 katakchada - FIND(" ";A2;H2 +1) . Biz I2 katak formulasida H2 borligini ko'ramiz, biz uni formulaning o'ziga o'zgartiramiz va I2 katakchaga o'rnatilgan formulani olamiz.

Biz familiyani tanlash uchun birinchi formulani ko'rib chiqamiz va bu erda H2 yoki I2 qaerda paydo bo'lishini ko'ramiz va ularni Ism va Familiya kabi formulalarga o'zgartiramiz.

• Familiya =PSTR(A2,1,H2 -1) biz =PSTR(A2,1,FIND(" ",A2,1) -1) olamiz
• Nomi =PSTR(A2;H2 +1;I2 -H2) ​​olamiz =PSTR(A2;FIND(" ";A2;1) +1;
  FIND(" ";A2;FIND(" ";A2;1)+1) -FIND(" ";A2;1) )
• Otasining ismi =PSTR(A2;I2 +1;50) biz =PSTR(A2;FIND(" ";A2;FIND(" ";A2;1)+1) +1;50) olamiz

Endi bo'sh joy bilan oraliq pozitsiyani hisoblash xavfsiz tarzda olib tashlanishi mumkin. Bu oddiylik uchun avval oraliq ma'lumotlar qidirilayotganda, so'ngra funksiya bir-birining ichiga joylashtiriladigan usullardan biridir. Qabul qiling, agar siz bir vaqtning o'zida bunday katta formulani yozsangiz, chalkashib ketish va xato qilish oson.

Umid qilamizki, ushbu misol Excel matn funksiyalari matn bilan ishlashda qanchalik foydali ekanligini va ular bir xil turdagi ma'lumotlar formulalari yordamida matnni avtomatik ravishda ajratish imkonini beradi. Agar sizga maqola yoqqan bo'lsa, +1 ni bosganingizdan minnatdor bo'lamiz va menga yoqadi. VKontakte guruhimizga obuna bo'ling va qo'shiling.

2-misol: Excelda formula yordamida matnni ustunlarga qanday ajratish mumkin

Amalda juda tez-tez uchrab turadigan ikkinchi misolni ko'rib chiqaylik. Misol avvalgisiga o'xshaydi, ammo bo'linishi kerak bo'lgan ko'proq ma'lumotlar mavjud. Ushbu misolda men sizga muammoni tezda hal qilish va chalkashmaslik imkonini beradigan texnikani ko'rsataman.

Aytaylik, bizda vergul bilan ajratilgan raqamlar ro'yxati bor, biz matnni har bir raqam alohida katakchada bo'lishi uchun ajratishimiz kerak (bu vergul o'rniga boshqa har qanday belgilar, shu jumladan bo'shliqlar ham bo'lishi mumkin). Ya'ni, biz matnni so'zlarga ajratishimiz kerak.

Eslatib o'tamiz, bu muammoni biz allaqachon ko'rib chiqqan usul yordamida qo'lda (formulalarsiz) hal qilish mumkin. Bizning holatlarimizda buni formulalar yordamida qilishimiz kerak.

Avval biz matnni ajratadigan umumiy bo'linishni topishimiz kerak. Bizning holatda, bu vergul, lekin masalan, birinchi vazifada biz to'liq ismni ajratdik va ajratuvchi bo'sh joy edi. Bizning ikkinchi misolimiz universalroq (katta hajmdagi ma'lumotlar mavjud bo'lganda qulayroqdir), shuning uchun biz nafaqat to'liq ismni alohida katakchalarga, balki butun jumlani - har bir so'zni alohida katakka ajratishimiz mumkin. Aslida, bu savol sharhlarda paydo bo'ldi, shuning uchun ushbu maqolani to'ldirishga qaror qilindi.

Qulaylik uchun biz ushbu ajratgichni qo'shni ustunda ko'rsatamiz, shunda biz uni formulada yozishimiz shart emas, shunchaki katakka murojaat qilishimiz kerak. Bu, shuningdek, hujayralardagi ajratgichni o'zgartirish orqali boshqa muammolarni hal qilish uchun fayldan foydalanishga imkon beradi.

Endi texnikaning asosiy mohiyati.

1-qadam. Yordamchi ustunda FIND funksiyasidan foydalangan holda birinchi ajratgichning o'rnini topamiz. Men funktsiyani batafsil tasvirlab bermayman, chunki biz uni avvalroq ko'rib chiqdik. Keling, formulani D1 ga yozamiz va uni barcha qatorlarga kengaytiramiz

TOPISH(B1;A1;1 )

Ya'ni, biz matnda 1-pozitsiyadan boshlab vergulni qidiramiz

TOPISH($B1 ;$A1;D1+1 )

Birinchidan, formulani o'ngga tortganda, katakcha havolalari siljimasligi uchun kerakli qiymat va matn ustunini tuzatamiz. Buni amalga oshirish uchun siz B va A ustunlaridan oldin dollar yozishingiz kerak - yoki qo'lda yoki A1 va B1 ni tanlang, F4 tugmachasini uch marta bosing, shundan so'ng havolalar nisbiy emas, mutlaq bo'ladi.

Ikkinchidan: biz uchinchi argumentni hisoblaymiz - oldingi ajratuvchining pozitsiyasi sifatida pozitsiyaning boshlanishi (biz uni yuqorida topdik) plyus 1, ya'ni D1 + 1, chunki biz bilamizki, ikkinchi ajratuvchi aniq birinchidan keyin joylashgan. ajratuvchi va biz buni hisobga olishimiz shart emas.

Keling, formulani yozamiz va uni pastga tortamiz.

Qadam 3. Boshqa barcha ajratuvchilarning o'rnini toping. Buning uchun ikkinchi ajratgichni topish formulasini (2-bosqich) kichik chegara bilan alohida ajratilgan qiymatlarning umumiy soniga teng bo'lgan katakchalar soni bo'yicha o'ngga kengaytiring. Keling, barcha ajratuvchi pozitsiyalarni olaylik. Bu erda #Value xatosi qiymatlar tugaganligini va formula endi chegaralagichlarni topa olmasligini bildiradi. Biz quyidagilarni olamiz

Qadam 4. PSTR funksiyasidan foydalanib matndan birinchi raqamni ajrating.

PSTR(A1;1 ;D1-1 )

Bizning boshlang'ich pozitsiyamiz 1, biz belgilar sonini birinchi ajratuvchining pozitsiyasi minus 1 sifatida hisoblaymiz: D1-1 formulani pastga cho'zamiz.

5-qadam. P1 katakchada PSTR funksiyasidan foydalanib ikkinchi so'zni ham topamiz

PSTR($A1;D1+1;E1-D1-1)

Ikkinchi raqamning boshlang'ich pozitsiyasi birinchi verguldan keyin boshlanadi. Biz D1 katakchadagi birinchi vergulning o'rnini olamiz, bittasini qo'shamiz va ikkinchi raqamimizning boshlang'ich pozitsiyasini olamiz.

Belgilar soni uchinchi ajratuvchining joylashuvi va ikkinchi va minus bitta belgi o'rtasidagi farq, ya'ni E1-D1-1

Dastlabki matnning A ustunini formulani o‘ngga tortganda harakatlanmasligi uchun tuzatamiz.

6-qadam. Keling, 5-bosqichda olingan formulani o'ngga va pastga cho'zamiz va matnni alohida katakchalarga olamiz.

Qadam 7. Asosan, bizning muammomiz allaqachon hal qilingan, ammo go'zallik uchun xuddi shu P1 katakchasida biz xatoni ushlaydigan formulani yozamiz, uni bo'sh qiymat bilan almashtiramiz. Shuningdek, siz yordamchi ustunlarni to'sqinlik qilmasligi uchun ularni guruhlashingiz va yig'ishingiz mumkin. Muammoning yakuniy yechimini olamiz

XATO(PSTR($A1,D1+1,E1-D1-1); "")

Eslatma: Biz ajratuvchining birinchi pozitsiyasini va so'zning birinchi bo'linishini boshqalardan farqli ravishda qildik va shuning uchun biz formulani faqat ikkinchi qiymatlardan kengaytira oldik. Muammoni yozishda men muammoni soddalashtirish mumkinligini payqadim. Buning uchun C ustunida birinchi ajratuvchining qiymati uchun 0 ni kiritish kerak edi. Shundan so'ng biz birinchi ajratuvchining qiymatini topamiz

TOP ($B1,$A1,C1+1)

va birinchi matn sifatida

PSTR($A1;C1+1;D1-C1-1)

Shundan so'ng, siz formulani qolgan qiymatlarga darhol kengaytirishingiz mumkin. Men ushbu variantni yuklab olish uchun misol sifatida qoldiraman. Asosan, fayl shablon sifatida ishlatilishi mumkin. "A" ustuniga ma'lumotlarni kiriting, "B" ustunida ajratgichni belgilang, formulalarni kerakli hujayralar soniga cho'zing va natijani oling.

Diqqat! Izohlarda ta'kidlanishicha, bizda matn oxirida chegaralovchi bo'lmagani uchun biz oxirgi ajratuvchidan satr oxirigacha bo'lgan belgilar sonini hisoblamaymiz, shuning uchun oxirgi ajratilgan matn yo'q. Muammoni hal qilish uchun siz birinchi bosqichda manba matni yoniga yordamchi ustun qo'shishingiz mumkin, bu erda siz ushbu matnni ajratuvchi bilan birlashtirasiz. Shunday qilib, biz matn oxirida ajratuvchiga ega bo'lamiz, ya'ni bizning formulalarimiz uning o'rnini hisoblab chiqadi va hamma narsa ishlaydi.

Yoki ikkinchi yechim 3-bosqichda, biz ajratuvchilarning pozitsiyalarini hisoblash uchun formula tuzib, uni to'ldirganimizda. Agar xato bo'lsa, tekshirib ko'ring, keyin aniq katta raqamni ko'rsating, masalan 1000.

XATO (TOPISH($B1,$A1,C1+1),1000)

Office 365 uchun Excel Mac uchun Office 365 Excel 2019 Excel 2016 Mac uchun Excel 2019 Excel 2013 Excel 2010 Excel 2007 Mac uchun Excel 2016 Mac uchun Excel 2011 Excel Online iPad uchun Excel iPhone uchun Excel Android planshetlar uchun Excel Android telefonlar uchun Excel Excel Mobile Excel Starter 2010 kamroq

Aytaylik, siz boshqa ikkita ustunni, ism va familiyani birlashtirib, bitta to'liq nom ustunini yaratmoqchisiz. Ism va familiyani birlashtirish uchun CONCATENATE funksiyasidan yoki ampersand (&) operatoridan foydalaning.

Muhim: Excel 2016Excel Mobile va Excel Online-dagi ushbu funksiya UNION funksiyasi bilan almashtirildi. CONCATENATE funksiyasi hali ham orqaga qarab muvofiqlik uchun mavjud bo'lsa-da, bundan buyon COMBINES dan foydalanish kerak. Buning sababi, CONCATENATE Excelning keyingi versiyalarida mavjud bo'lmasligi mumkin.

Misol

Quyidagilarni bo'sh qog'ozga ko'chiring.

Familiya Vladimir Formula Tavsif (natija) Bo'sh joy bilan ajratilgan yuqoridagi nomlarni birlashtiradi (Belova) Yuqoridagi nomlarni birlashtirib, ularni nuqta-vergul bilan ajratib turadi (Novikov, Pavel) BIRLASHTIRISH(A2;" ";B2)