Contents
Layi yana karya a cikin tantanin halitta ɗaya, wanda aka ƙara ta amfani da gajeriyar hanyar madannai alt+Shigar abu ne na kowa da kowa. Wani lokaci masu amfani da kansu ke yin su don ƙara kyau ga dogon rubutu. Wani lokaci ana ƙara irin wannan canja wuri ta atomatik lokacin sauke bayanai daga kowane shirye-shiryen aiki (sannu 1C, SAP, da dai sauransu) Matsalar ita ce cewa ba kawai ku sha'awar irin wannan tebur ba, amma kuyi aiki tare da su - sannan waɗannan haruffa marasa ganuwa na iya zama matsala. Kuma ƙila ba za su zama ba - idan kun san yadda za ku bi da su daidai.
Bari mu dubi wannan batu dalla-dalla.
Cire karya layi ta hanyar maye gurbin
Idan muna bukatar mu kawar da hyphens, to, abu na farko da yakan zo a hankali shine fasahar "nemo da maye gurbin" na gargajiya. Zaɓi rubutun sannan ka kira taga sauyawa tare da gajeriyar hanya ta madannai Ctrl+H ko via Gida - Nemo kuma Zaɓi - Sauya (Gida - Nemo & Zaɓi - Sauya). Ɗayan rashin daidaituwa - ba a bayyana sosai yadda ake shiga cikin babban filin ba Don nemowa (Nemi me) Halin karya layin mu marar ganuwa. alt+Shigar a nan, abin takaici, ba ya aiki, kwafin wannan alamar kai tsaye daga tantanin halitta da liƙa a nan ma ya kasa.
Haɗin kai zai taimaka Ctrl+J – wato madadin alt+Shigar a cikin akwatunan maganganu na Excel ko filayen shigarwa:
Lura cewa bayan kun sanya siginar ƙiftawa a saman filin kuma latsa Ctrl+J – babu abin da zai bayyana a cikin filin kanta. Kada ku ji tsoro - wannan al'ada ne, alamar ba a iya gani 🙂
Zuwa filin kasa Canje (Maye gurbin da) ko dai kada ku shiga wani abu, ko ku shiga sarari (idan muna so ba kawai mu cire sarƙaƙƙiya ba, amma mu maye gurbinsu da sarari don kada layin ya manne tare cikin gaba ɗaya). Kawai danna maɓallin Sauya komai (Maye gurbin Duka) kuma jikokinmu za su bace:
Nuance: bayan yin maye da aka shigar dashi Ctrl+J hali marar ganuwa ya rage a filin Don nemowa kuma yana iya tsoma baki a nan gaba - kar a manta da share shi ta hanyar sanya siginan kwamfuta a cikin wannan filin kuma sau da yawa (don amintacce) danna maɓallan. share и Backspace.
Cire karya layi tare da dabara
Idan kuna buƙatar magance matsalar tare da dabaru, to zaku iya amfani da aikin da aka gina a ciki BUGA (TSARKI), wanda zai iya share rubutun duk haruffan da ba a iya bugawa ba, gami da raunin layin mu mara kyau:
Wannan zaɓi, duk da haka, ba koyaushe ya dace ba, saboda layin bayan wannan aikin ana iya haɗa su tare. Don hana wannan daga faruwa, kuna buƙatar ba kawai cire jigon ba, amma maye gurbin shi da sarari (duba sakin layi na gaba).
Maye gurbin karya layi tare da dabara
Kuma idan kuna so ba kawai don sharewa ba, amma don maye gurbin alt+Shigar akan, alal misali, sarari, sannan wani, ɗan ƙaramin gini mai rikitarwa zai buƙaci:
Don saita saƙar da ba a iya gani muna amfani da aikin SYMBOL (CHAR), wanda ke fitar da harafi ta lambar sa (10). Sannan aikin MUSA (MADAMA) yana nemo jigon mu a cikin bayanan tushen kuma ya maye gurbinsu da kowane rubutu, misali, da sarari.
Rarraba cikin ginshiƙai ta hanyar karya layi
Sanin kayan aiki da yawa kuma mai amfani sosai Rubutu ta ginshiƙai daga tab data (Bayanai - Rubutu zuwa ginshiƙai) Hakanan zai iya aiki mai kyau tare da karya layi da raba rubutu daga tantanin halitta zuwa da yawa, karya shi ta alt+Shigar. Don yin wannan, a mataki na biyu na mayen, kuna buƙatar zaɓar wani bambance-bambancen halin ƙayyadaddun al'ada. Other (Na al'ada) kuma yi amfani da gajeriyar hanyar madannai da muka riga muka sani Ctrl+J a matsayin madadin alt+Shigar:
Idan bayanan ku na iya ƙunsar raguwar layi da yawa a jere, to, zaku iya “rushe” su ta hanyar kunna akwati. Bi da iyakoki a jere a matsayin ɗaya (Yi da masu iyaka a jere a matsayin ɗaya).
Bayan danna kan Next (Na gaba) kuma ta hanyar duk matakai uku na mayen, muna samun sakamakon da ake so:
Lura cewa kafin yin wannan aikin, dole ne a shigar da isassun adadin ginshiƙai a hannun dama na ginshiƙan tsaga don kada rubutun ya sake rubuta ƙimar (farashi) waɗanda ke hannun dama.
Raba cikin layi ta Alt + Shigar ta hanyar Tambayar Wuta
Wani aiki mai ban sha'awa shine raba rubutun multiline daga kowane tantanin halitta ba cikin ginshiƙai ba, amma cikin layi:
Yana ɗaukar lokaci mai tsawo don yin wannan da hannu, yana da wahala tare da ƙididdiga, ba kowa ba ne zai iya rubuta macro. Amma a aikace, wannan matsalar tana faruwa sau da yawa fiye da yadda muke so. Mafi sauƙi kuma mafi sauƙi mafi sauƙi shine amfani da ƙarar Query Query don wannan aikin, wanda aka gina a cikin Excel tun 2016, kuma don nau'in farko na 2010-2013 ana iya sauke shi gaba daya kyauta daga gidan yanar gizon Microsoft.
Don loda bayanan tushen zuwa Tambayoyin Wutar Lantarki, dole ne ka fara canza su zuwa "Table mai wayo" tare da gajeriyar hanyar madannai. Ctrl+T ko kuma ta hanyar maballin Tsara azaman tebur tab Gida (Gida - Tsarin azaman Tebur). Idan saboda wasu dalilai ba ka so ko ba za ka iya amfani da "smart Tables", sa'an nan za ka iya aiki tare da "wawa". A wannan yanayin, kawai zaɓi kewayon asali kuma ba shi suna akan shafin Formulas - Mai sarrafa Suna - Sabo (Formulas - Mai sarrafa Suna - Sabo).
Bayan haka, a kan shafin data (idan kuna da Excel 2016 ko daga baya) ko akan shafin Tambayar .arfi (idan kuna da Excel 2010-2013) zaku iya danna maɓallin Daga tebur / kewayon (Daga Tebur/Range)don loda teburin mu cikin editan Query Query:
Bayan lodawa, zaɓi ginshiƙi tare da rubutun layukan da yawa a cikin sel kuma zaɓi umarni akan Babban shafin Rarraba Rukunin - Ta Delimiter (Gida - Rarraba ginshiƙi - Ta hanyar iyakance):
Mafi mahimmanci, Tambayar Wuta za ta gane ƙa'idar rarraba ta atomatik kuma ta maye gurbin alamar da kanta #(lf) Halin ciyarwar layi marar ganuwa (lf = ciyarwar layi = ciyarwar layi) a cikin filin shigar da raba. Idan ya cancanta, ana iya zaɓar wasu haruffa daga jerin abubuwan da aka saukar a ƙasan taga, idan kun fara duba akwatin. Raba tare da haruffa na musamman (An raba ta haruffa na musamman).
Don haka an raba duk abin da ke cikin layuka, kuma ba ginshiƙai - kar a manta da canza mai zaɓi Rows (Ta hanyar layuka) a cikin ci-gaba zažužžukan.
Duk abin da ya rage shine dannawa OK kuma sami abin da kuke so:
Za a iya sauke teburin da aka gama a baya akan takardar ta amfani da umarnin Rufe kuma ɗauka - Rufe kuma ɗauka a cikin… tab Gida (Gida - Rufe & Load - Rufe & Loda zuwa…).
Yana da mahimmanci a lura cewa lokacin amfani da Query Query, dole ne ku tuna cewa lokacin da bayanan tushen ya canza, ba a sabunta sakamakon ta atomatik ba, saboda. wadannan ba tsari bane. Don sabuntawa, dole ne ku danna dama akan tebur na ƙarshe akan takardar kuma zaɓi umarnin Sabunta & Ajiye (Sake sabuntawa) ko kuma danna maɓallin Ɗaukaka Duk tab data (Bayanai - Refresh Duk).
Macro don rarraba cikin layi ta Alt+Enter
Don kammala hoton, bari mu kuma ambaci maganin matsalar da ta gabata tare da taimakon macro. Bude Editan Kayayyakin Kayayyakin Kayayyakin Kayayyakin Ta amfani da maballin suna iri ɗaya akan shafin developer (Mai haɓakawa) ko gajerun hanyoyin keyboard alt+F11. A cikin taga da ya bayyana, saka sabon tsari ta cikin menu Saka - Module sannan ka kwafi wannan code din can:
Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Rarraba(cell, Chr(10)) 'ƙayyade adadin gutsuttsura cell.Offset(1, 0) Resize(n, 1) .EntireRow.Saka 'saka komai a ƙasan tantanin halitta.Resize(n + 1, 1) = Aikin aiki + 1, 0) 'matsawa zuwa tantanin halitta na gaba Gaba i Ƙarshen Sub
Koma zuwa Excel kuma zaɓi sel tare da rubutun multiline da kuke son raba. Sannan yi amfani da maɓallin Macros tab developer (Developer - Macros) ko gajeriyar hanyar keyboard alt+F8don gudanar da macro da aka ƙirƙira, wanda zai yi muku duk aikin:
Voila! Masu shirye-shirye, a zahiri, malalaci ne kawai waɗanda za su gwammace yin aiki tuƙuru sau ɗaya sannan su yi komai 🙂
- Share rubutu daga takarce da karin haruffa
- Maye gurbin rubutu da cire wuraren da ba sa karyewa tare da aikin SUBSTITUTE
- Yadda ake raba rubutun m zuwa sassa a cikin Excel