Matsala mai sauƙi, a kallon farko, matsala tare da warwarewar da ba ta bayyana ba: cire kalma ta ƙarshe daga layin rubutu. To, ko kuma, a cikin yanayin gabaɗaya, guntu na ƙarshe, wanda aka ware ta hanyar da aka ba da hali (sarari, waƙafi, da sauransu) A wasu kalmomi, wajibi ne a aiwatar da bincike na baya (daga ƙarshen zuwa farkon) a cikin kirtani na wani. da aka ba da hali sa'an nan cire duk haruffa zuwa dama ta.
Bari mu dubi al'ada da dama hanyoyin da za a zaba daga: dabara, macros, kuma ta hanyar Power Query.
Hanyar 1. Formules
Domin samun sauƙin fahimtar ainihin ma'anar da makanikai, bari mu fara ɗan nesa daga nesa. Da farko, bari mu ƙara yawan sarari tsakanin kalmomi a cikin rubutun tushen mu zuwa, misali, guda 20. Kuna iya yin wannan tare da aikin maye gurbin. MUSA (MADAMA) da aikin maimaita halin da aka bayar N-times - SARAUNIYA (REPT):
Yanzu mun yanke haruffa 20 daga ƙarshen sakamakon rubutun ta amfani da aikin RIGHT (HAKA):
Ana samun dumi, dama? Ya rage don cire ƙarin sarari ta amfani da aikin TASHIYA (TRIM) kuma za a magance matsalar:
A cikin Ingilishi, tsarin mu zai yi kama da haka:
= KYAU (MAGAJIN (A1;» «; REPT(» «;20));20))
Ina fata a bayyane yake cewa a ka'ida ba lallai ba ne don saka daidaitattun wurare 20 - kowane lamba zai yi, idan dai ya fi tsayin kalma mafi tsawo a cikin rubutun tushe.
Kuma idan rubutun tushen ya buƙaci a raba shi ba ta sarari ba, amma ta wani hali mai raba (misali, ta hanyar waƙafi), to tsarin mu zai buƙaci a ɗan gyara shi:
Hanyar 2. Aikin macro
Hakanan za'a iya warware aikin cire kalma ta ƙarshe ko guntu daga rubutun ta amfani da macros, wato, rubuta aikin bincike na baya a cikin Visual Basic wanda zai yi abin da muke buƙata - bincika wani yanki da aka ba a cikin kirtani a kishiyar shugabanci - daga karshen zuwa farko.
Danna gajeriyar hanyar madannai alt+F11 ko button Kayayyakin aikin Basic tab developer (Mai haɓakawa)don buɗe macro editan. Sannan ƙara sabon tsari ta hanyar menu Saka - Module sannan ka kwafi wannan code din can:
Aiki LastWord(txt As String, Optional delim As String = "", Zabi n As Integer = 1) As String arFragments = Raga (txt, delim) LastWord = arFragments (UBound(arFragments) - n + 1) Ƙarshen Aiki
Yanzu zaku iya ajiye littafin aikin (a cikin tsarin da aka kunna macro!) kuma kuyi amfani da aikin da aka ƙirƙira a cikin mahaɗin da ke biyowa:
=LastWord (txt ; delim ; n)
inda
- txt – cell tare da rubutun tushe
- delim - Separator hali (tsoho - sarari)
- n - wace kalma ya kamata a ciro daga ƙarshe (ta tsohuwa - na farko daga ƙarshe)
Tare da kowane canje-canje a cikin rubutun tushe a nan gaba, aikin macro ɗinmu za a sake ƙididdige shi akan tashi, kamar kowane daidaitaccen aikin takardar Excel.
Hanyar 3. Tambayar Wuta
Tambayar .arfi ƙari ne kyauta daga Microsoft don shigo da bayanai zuwa cikin Excel daga kusan kowane tushe sannan canza bayanan da aka sauke zuwa kowane nau'i. Ƙarfi da sanyin wannan add-in yana da girma sosai cewa Microsoft ya gina duk abubuwan da ke cikin Excel 2016 ta tsohuwa. Domin Excel 2010-2013 Power Query za a iya sauke shi kyauta daga nan.
Ayyukanmu na raba kalma ta ƙarshe ko guntu ta hanyar mai raba da aka bayar ta amfani da Query Query ana warware shi cikin sauƙi.
Da farko, bari mu juya teburin bayananmu zuwa tebur mai wayo ta amfani da gajerun hanyoyin keyboard. Ctrl+T ko umarni Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur):
Sa'an nan kuma mu loda "smart tebur" da aka halitta a cikin Power Query ta amfani da umarnin Daga tebur / kewayon (Daga tebur / kewayon) tab data (idan kuna da Excel 2016) ko akan shafin Tambayar .arfi (idan kuna da Excel 2010-2013):
A cikin taga editan tambaya da ke buɗewa, akan shafin Sake Kama (canza) zabi tawagar Rarraba Rukunin - Ta Delimiter (Shafin Raba - Ta hanyar iyakance) sannan ya rage don saita halin rabuwa kuma zaɓi zaɓi Madaidaicin iyakadon yanke ba duka kalmomi ba, amma kawai na ƙarshe:
Bayan danna kan OK Za a raba kalmar ƙarshe zuwa sabon shafi. Za a iya cire ginshiƙin farko da ba dole ba ta danna maɓallin dama da zaɓi cire (Goge). Hakanan zaka iya sake suna sauran ginshiƙi a cikin taken tebur.
Za a iya loda sakamakon da aka mayar zuwa takardar ta amfani da umarnin Gida - Rufe kuma Loda - Rufe kuma Loda zuwa… (Gida - Rufe & Loda - Kusa & Loda zuwa…):
Kuma a sakamakon haka muna samun:
Kamar wannan - mai arha da farin ciki, ba tare da dabara da macros ba, kusan ba tare da taɓa maballin ba 🙂
Idan ainihin lissafin ya canza a nan gaba, zai isa a danna dama ko amfani da gajeriyar hanyar madannai Ctrl+alt+F5 sabunta bukatar mu.
- Rarraba rubutun m zuwa ginshiƙai
- Tsara da rarraba rubutu tare da maganganu na yau da kullun
- Cire kalmomin farko daga rubutu tare da aikin SUBSTITUTE