змест
Разрывы радкоў у адной ячэйцы, дададзеныя з дапамогай спалучэння клавіш Alt+Уводзіць гэта вельмі звычайная і звыклая рэч. Часам іх робяць самі карыстальнікі, каб надаць прыгажосці доўгаму тэксту. Часам такія перадачы дадаюцца аўтаматычна пры выгрузцы дадзеных з якіх-небудзь працоўных праграм (прывітанне 1С, SAP і г.д.). Праблема ў тым, што тады трэба не толькі любавацца такімі табліцамі, але і працаваць з імі – і тады гэтыя нябачныя перадачы сімвалаў могуць быць праблема. А могуць і не стаць – калі ведаць, як з імі правільна звяртацца.
Давайце разбярэмся ў гэтым пытанні падрабязней.
Выдаленне разрываў радкоў шляхам замены
Калі нам трэба пазбавіцца ад злучкоў, то першае, што звычайна прыходзіць на розум, гэта класічны прыём «знайдзі і замяні». Вылучыце тэкст, а затым выклічце акно замены з дапамогай спалучэння клавіш Ctrl+H або з дапамогай Галоўная – Знайсці і выбраць – Замяніць (Галоўная — Знайсці&Выбраць — Замяніць). Адна неадпаведнасць - не вельмі зразумела, як уводзіць у верхняе поле Для таго, каб знайсці (Знайдзі што) наш нябачны сімвал разрыву радка. Alt+Уводзіць тут, на жаль, ён больш не працуе, скапіяваць гэты сімвал непасрэдна з ячэйкі і ўставіць яго сюды таксама не атрымаецца.
Дапаможа камбінацыя Ctrl+J – гэта альтэрнатыва Alt+Уводзіць у дыялогавых вокнах або палях уводу Excel:
Калі ласка, звярніце ўвагу, што пасля таго, як вы паставіце мігцячы курсор у верхняе поле і націсніце Ctrl+J – у самім полі нічога не з'явіцца. Не палохайцеся - гэта нармальна, сімвал нябачны 🙂
Да ніжняга поля сурагат (Замяніць на) альбо нічога не ўводзіць, альбо праз прабел (калі мы хочам не толькі прыбраць злучкі, але замяніць іх прабелам, каб радкі не зліпаліся ў адно цэлае). Проста націсніце кнопку Замяніць усё (Замяніць усе) і нашы злучкі знікнуць:
Нюанс: пасля выканання замены ўведзена с Ctrl+J нябачны персанаж застаецца ў полі Для таго, каб знайсці і можа перашкодзіць у далейшым – не забудзьцеся выдаліць яго, паставіўшы курсор у гэта поле і некалькі разоў (для надзейнасці) націснуўшы клавішы Выдаляць и вяртанне на адну пазіцыю.
Выдаленне разрываў радкоў з дапамогай формулы
Калі вам трэба вырашыць задачу з дапамогай формул, то вы можаце скарыстацца убудаванай функцыяй PRINT (ЧЫСТЫ), які можа ачысціць тэкст ад усіх недрукаваных сімвалаў, уключаючы нашы злашчасныя разрывы радкоў:
Гэты варыянт, праўда, не заўсёды зручны, бо лініі пасля гэтай аперацыі можна склеіць. Каб гэтага не адбылося, трэба не толькі прыбраць злучок, але і замяніць яго прабелам (гл. наступны пункт).
Замена разрываў радкоў формулай
І калі хочацца не проста выдаліць, а замяніць Alt+Уводзіць на, напрыклад, прасторы, то спатрэбіцца іншая, крыху больш складаная канструкцыя:
Каб усталяваць нябачны злучок, мы выкарыстоўваем функцыю SYMBOL (ЗНАК), які выводзіць сімвал па сваім кодзе (10). А потым функцыя ЗАМЕННІК (ЗАМЕНА) шукае нашы злучкі ў зыходных дадзеных і замяняе іх любым іншым тэкстам, напрыклад, прабелам.
Падзел на слупкі шляхам разрыву радка
Знаёмы многім і вельмі зручны інструмент Тэкст па слупках з таб Дата (Дадзеныя — тэкст у слупкі) можа таксама выдатна працаваць з разрывамі радкоў і разбіваць тэкст з адной ячэйкі на некалькі, разбіваючы яго Alt+Уводзіць. Для гэтага на другім кроку майстра вам трэба выбраць варыянт карыстацкага падзельніка іншае (На заказ) і выкарыстоўваць спалучэнне клавіш, якое мы ўжо ведаем Ctrl+J у якасці альтэрнатывы Alt+Уводзіць:
Калі вашы дадзеныя могуць утрымліваць некалькі разрываў радкоў запар, вы можаце «згарнуць» іх, уключыўшы сцяжок Ставіцеся да паслядоўных раздзяляльнікаў як да аднаго (Расцэньвайце паслядоўныя раздзяляльнікі як адзін).
Пасля націску на кнопку наступны (Далей) і прайшоўшы ўсе тры крокі майстра, мы атрымаем жаданы вынік:
Звярніце ўвагу, што перад выкананнем гэтай аперацыі неабходна ўставіць дастатковую колькасць пустых слупкоў справа ад раздзеленага слупка, каб атрыманы тэкст не перазапісваў значэння (кошты), якія былі справа.
Разбіце на радкі Alt + Enter праз Power Query
Яшчэ адна цікавая задача - разбіць шматрадковы тэкст з кожнай ячэйкі не на слупкі, а на радкі:
Уручную гэта займае шмат часу, складана з формуламі, не кожны можа напісаць макрас. Але на практыцы гэтая праблема сустракаецца часцей, чым хацелася б. Самае простае і лёгкае рашэнне - выкарыстоўваць для гэтай задачы надбудову Power Query, якая ўбудавана ў Excel з 2016 года, а для больш ранніх версій 2010-2013 гадоў яе можна цалкам бясплатна спампаваць з сайта Microsoft.
Каб загрузіць зыходныя дадзеныя ў Power Query, вы павінны спачатку пераўтварыць іх у «разумную табліцу» з дапамогай спалучэння клавіш Ctrl+T або кнопкай Фармат у выглядзе табліцы таб Галоўная (Галоўная — фармат у табліцу). Калі па нейкіх прычынах вы не хочаце ці не можаце выкарыстоўваць «разумныя табліцы», то можаце працаваць з «дурнымі». У гэтым выпадку проста выберыце зыходны дыяпазон і дайце яму імя на ўкладцы Формулы – Дыспетчар імёнаў – Новы (Формулы — Дыспетчар імёнаў — Новы).
Пасля гэтага на табл Дата (калі ў вас Excel 2016 або больш позняй версіі) або на ўкладцы Запыт на харчаванне (калі ў вас Excel 2010-2013), вы можаце націснуць на кнопку З табліцы/дыяпазону (З табліцы/дыяпазону)каб загрузіць нашу табліцу ў рэдактар Power Query:
Пасля загрузкі вылучыце слупок з шматрадковым тэкстам у вочках і абярыце каманду на ўкладцы Галоўнае Раздзяліць слупок – па падзельніку (Галоўная старонка — Раздзяліць слупок — Па раздзяляльніку):
Верагодней за ўсё, Power Query аўтаматычна распазнае прынцып дзялення і падставіць сімвал самастойна #(lf) нябачны сімвал пераводу радка (lf = перавод радка = перавод радка) у поле ўводу падзельніка. Пры неабходнасці іншыя сімвалы можна выбраць з выпадальнага спісу ўнізе акна, калі папярэдне паставіць сцяжок Спліт са спецыяльнымі сімваламі (Падзяляецца спецыяльнымі сімваламі).
Каб усё было падзелена на радкі, а не на слупкі - не забывайце пераключаць селектар Радкі (Па радах) у групе дадатковых параметраў.
Засталося толькі націснуць OK і атрымаць тое, што вы хочаце:
Гатовую табліцу можна выгрузіць назад на ліст з дапамогай каманды Зачыніць і загрузіць – Зачыніць і загрузіць у… таб Галоўная (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у…).
Важна адзначыць, што пры выкарыстанні Power Query вы павінны памятаць, што пры змене зыходных дадзеных вынікі не абнаўляюцца аўтаматычна, т.к. гэта не формулы. Каб абнавіць, трэба пстрыкнуць правай кнопкай мышы на выніковай табліцы на аркушы і выбраць каманду Абнавіць і захаваць (Абнавіць) або націсніце кнопку абнавіць усе таб Дата (Дадзеныя — Абнавіць усе).
Макрас для разбіцця на радкі Alt+Enter
Для паўнаты карціны згадаем яшчэ і рашэнне папярэдняй задачы з дапамогай макраса. Адкрыйце рэдактар Visual Basic з дапамогай аднайменнай кнопкі на ўкладцы распрацоўшчык (Распрацоўшчык) або спалучэнне клавіш Alt+F11. У якое з'явілася акне ўстаўце новы модуль праз меню Устаўка – модуль і скапіруйце туды наступны код:
Sub Split_By_Rows() Dim cell As Range, n As Integer Set cell = ActiveCell For i = 1 To Selection.Rows.Count ar = Split(cell, Chr(10)) 'вызначэнне колькасці фрагментаў cell.Offset(1, 0) ).Resize(n, 1).EntireRow.Insert 'уставіць пустыя радкі ніжэй cell.Resize(n + 1, 1) = WorksheetFunction.Transpose(ar) 'увесці ў іх дадзеныя з масіва Set cell = cell.Offset(n + 1, 0) 'пераход да наступнай ячэйкі Next i End Sub
Вярніцеся ў Excel і вылучыце вочкі з шматрадковым тэкстам, які вы хочаце падзяліць. Затым выкарыстоўвайце кнопку Макрас таб распрацоўшчык (Распрацоўшчык — Макрасы) або спалучэнне клавіш Alt+F8каб запусціць створаны макрас, які зробіць усю працу за вас:
Вуаля! Праграмісты, насамрэч, проста вельмі лянівыя людзі, якія аддадуць перавагу адзін раз папрацаваць, а потым нічога не рабіць 🙂
- Ачыстка тэксту ад смецця і лішніх сімвалаў
- Замена тэксту і выдаленне непарыўных прабелаў з дапамогай функцыі SUBSTITUTE
- Як разбіць ліпкі тэкст на часткі ў Excel