Қарапайым, бір қарағанда, анық емес шешімі бар мәселе: мәтін жолынан соңғы сөзді шығарып алу. Жақсы, немесе, жалпы жағдайда, соңғы фрагмент, берілген бөлгіш таңбамен (бос орын, үтір және т. берілген таңба, содан кейін оның оң жағындағы барлық таңбаларды шығарып алыңыз.
Дәстүрлі түрде таңдаудың бірнеше жолын қарастырайық: формулалар, макростар және Power Query арқылы.
1-әдіс. Формулалар
Формуланың мәні мен механикасын түсінуді жеңілдету үшін аздап алыстан бастайық. Алдымен, бастапқы мәтіндегі сөздер арасындағы бос орындар санын, мысалы, 20 данаға дейін көбейтейік. Мұны ауыстыру функциясы арқылы жасауға болады. СУБСТИТУТ (АЛМАСТЫРУ) және берілген таңбаны N-рет қайталау функциясы – REPEAT (REPT):
Енді функцияның көмегімен алынған мәтіннің соңынан 20 таңбаны кесіп тастаймыз RIGHT (ДҰРЫС):
Күн жылынып барады, солай ма? Функцияның көмегімен қосымша бос орындарды жою қалады TRIM (ТРИМ) және мәселе шешіледі:
Ағылшын тіліндегі нұсқада формуламыз келесідей болады:
=ТЕКСІРУ(ОҢ(АЛМАСТЫРУ(A1;" «;REPT(» «;20));20))
Негізінде дәл 20 бос орын енгізудің қажеті жоқ екені түсінікті деп үміттенемін – кез келген сан бастапқы мәтіндегі ең ұзын сөздің ұзындығынан көп болса, орындалады.
Ал егер бастапқы мәтінді бос орынмен емес, басқа бөлгіш таңбамен (мысалы, үтірмен) бөлу қажет болса, онда формуламызды сәл түзету қажет болады:
2-әдіс. Макрофункция
Мәтіннен соңғы сөзді немесе фрагментті шығару тапсырмасын макростар арқылы да шешуге болады, атап айтқанда, Visual Basic-те кері іздеу функциясын жазу, ол бізге қажет нәрсені орындайды – қарама-қарсы бағытта жолда берілген ішкі жолды іздеу – аяғынан басына.
Пернелер тіркесімін басыңыз Alt+F11 немесе батырма Visual Basic қойындысы әзірлеуші (Әзірлеуші)макрос редакторын ашу үшін. Содан кейін мәзір арқылы жаңа модуль қосыңыз Кірістіру – модуль және сол жерге келесі кодты көшіріңіз:
Функция LastWord(txt Жол ретінде, Қосымша бөлшектеу Жол ретінде = " ", Қосымша n Бүтін сан = 1) Жол ретінде arFragments = Бөлу(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) Аяқтау функциясы
Енді жұмыс кітабын (макрос қосылған пішімде!) сақтауға және жасалған функцияны келесі синтаксисте пайдалануға болады:
=Соңғы сөз(txt ; бөлу ; n)
қайда
- TXT – бастапқы мәтіні бар ұяшық
- шектеу — бөлгіш таңба (әдепкі — бос орын)
- n – соңынан қандай сөзді алу керек (әдепкі бойынша – соңынан бірінші)
Болашақта бастапқы мәтіндегі кез келген өзгерістер кезінде біздің макрос функция кез келген стандартты Excel парағы функциясы сияқты жылдам қайта есептеледі.
3-әдіс. Power Query
Қуат сұрауы кез келген дерлік көзден Excel бағдарламасына деректерді импорттауға, содан кейін жүктелген деректерді кез келген пішінге түрлендіруге арналған Microsoft корпорациясының тегін қосымшасы. Бұл қондырманың күші мен салқындығы сонша, Microsoft өзінің барлық мүмкіндіктерін Excel 2016 нұсқасына әдепкі бойынша құрастырған. Excel 2010-2013 Power Query үшін мына жерден тегін жүктеп алуға болады.
Power Query көмегімен соңғы сөзді немесе фрагментті берілген бөлгіш арқылы бөлу тапсырмамыз өте оңай шешіледі.
Алдымен, пернелер тіркесімін пайдаланып деректер кестесін смарт кестеге айналдырайық. Ctrl+T немесе командалар Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу):
Содан кейін біз пәрмен арқылы құрылған «ақылды кестені» Power Query-ге жүктейміз Кестеден/ауқымнан (Кестеден/ауқымнан) қойындысы мәліметтер (егер сізде Excel 2016 болса) немесе қойындысында Қуат сұрауы (егер сізде Excel 2010-2013 болса):
Қойындыда ашылатын сұрау өңдегішінің терезесінде трансформация (Трансформация) команда таңдау Бөлінген баған – Бөлгіш бойынша (Бөлінген баған — бөлгіш бойынша) содан кейін бөлгіш таңбаны орнату және опцияны таңдау қалады Ең оң жақ бөлгішбарлық сөздерді емес, соңғысын ғана кесу:
Басқаннан кейін OK соңғы сөз жаңа бағанға бөлінеді. Қажет емес бірінші бағанды оның тақырыбын тінтуірдің оң жақ түймешігімен басып, таңдау арқылы жоюға болады кетіру (Жою). Сондай-ақ кесте тақырыбындағы қалған бағанның атын өзгертуге болады.
Нәтижелерді пәрмен арқылы параққа кері жүктеп салуға болады Үй — Жабу және жүкте — Жабу және жүкте... (Басты бет — Жабу және жүктеу — Жабу және жүктеп салу...):
Нәтижесінде біз мыналарды аламыз:
Бұл сияқты - арзан және көңілді, формулаларсыз және макростарсыз, пернетақтаны ұстамай-ақ дерлік 🙂
Түпнұсқа тізім болашақта өзгерсе, тінтуірдің оң жақ түймешігімен басу немесе пернелер тіркесімін пайдалану жеткілікті болады Ctrl+Alt+F5 сұрауымызды жаңартыңыз.
- Жабысқақ мәтінді бағандарға бөлу
- Мәтінді тұрақты тіркестермен талдау және талдау
- SUBSTITUTE функциясы арқылы мәтіннен бірінші сөздерді шығарып алу