ЯК ПЕРАЎТВАРЫЦЬ ЛІК Ў ТЭКСТ І НАЗАД У EXCEL

Адной з частых задач, з якімі сутыкаюцца карыстальнікі праграмы Эксэля, з'яўляецца пераўтварэнні лікавых выразаў у тэкставы фармат і назад. Гэтае пытанне часта прымушае выдаткаваць на рашэнне шмат часу, калі юзэр не ведае выразнага алгарытму дзеянняў. Давайце разбярэмся, як можна вырашыць абедзве задачы рознымі спосабамі.

Канвертацыя лікі ў тэкставы выгляд

Усе вочкі ў Эксэля маюць пэўны фармат, які задае праграме, як ёй разглядаць тое ці іншае выраз. Напрыклад, нават калі ў іх будуць запісаныя лічбы, але фармат выстаўлены тэкставы, то прыкладанне будзе разглядаць іх, як просты тэкст, і не зможа праводзіць з такімі дадзенымі матэматычныя вылічэнні. Для таго, каб Excel ўспрымаў лічбы менавіта як лік, яны павінны быць ўпісаны ў элемент ліста з агульным або лічбавым фарматам.

Для пачатку разгледзім розныя варыянты рашэння задачы канвертацыі лікаў у тэкставы выгляд.

Спосаб 1: фарматаванне праз кантэкстнае меню

Часцей за ўсё карыстальнікі выконваюць фарматаванне лікавых выразаў у тэкставыя праз кантэкстнае меню.

Вылучаем тыя элементы ліста, у якіх трэба пераўтварыць дадзеныя ў тэкст. Як бачым, ва ўкладцы "Галоўная" на панэлі інструментаў у блоку "Лічба" ў адмысловым поле адлюстроўваецца інфармацыя аб тым, што дадзеныя элементы маюць агульны фармат, а значыць, лічбы, запісаныя ў іх, успрымаюцца праграмай, як лік.

Клікаем правай кнопкай мышы па вылучэнню і ў якое адкрылася меню выбіраем пазіцыю "Фармат вочак ...".

У якое адкрылася акне фарматавання пераходзім ва ўкладку "Лічба", Калі яно было адкрыта ў іншым месцы. У блоку налад "Лікавыя фарматы" выбіраем пазіцыю "Тэкставы". Для захавання змяненняў ціснем на кнопку "OK " у ніжняй частцы акна.

Як бачым, пасля дадзеных маніпуляцый ў спецыяльным поле высвятляецца інфармацыя аб тым, што вочкі былі ператвораныя ў тэкставы выгляд.

Але калі мы паспрабуем падлічыць автосумму, то яна адлюструецца ў вочку ніжэй. Гэта азначае, што пераўтварэнне было здзейснена не цалкам. У гэтым і заключаецца адна з фішак Excel. Праграма не дае завяршыць пераўтварэнне дадзеных найбольш інтуітыўна зразумелым спосабам.

Каб завяршыць пераўтварэнне, нам трэба паслядоўна падвойным пстрычкай левай кнопкі мышы змясціць курсор у кожны элемент дыяпазону паасобку і націснуць на клавішу Enter. Каб спрасціць задачу замест двайнога пстрычкі можна выкарыстоўваць націск функцыянальнай клавішы F2.

Пасля выканання дадзенай працэдуры з усімі вочкамі вобласці, дадзеныя ў іх будуць успрымацца праграмай, як тэкставыя выразы, а, такім чынам, і автосумма будзе роўная нулю. Акрамя таго, як бачым, левы верхні кут вочак будзе афарбаваны ў зялёны колер. Гэта таксама з'яўляецца ўскосным прыкметай таго, што элементы, у якіх знаходзяцца лічбы, ператвораныя ў тэкставы варыянт адлюстравання. Хоць гэты прыкмета не заўсёды з'яўляецца абавязковым і ў некаторых выпадках такая пазнака адсутнічае.

Урок: Як змяніць фармат у Excel

Спосаб 2: інструменты на стужцы

Пераўтварыць лік ў тэкставы выгляд можна таксама скарыстаўшыся інструментамі на стужцы, у прыватнасці, выкарыстаўшы поле для паказу фармату, пра які ішла гаворка вышэй.

Вылучаем элементы, дадзеныя ў якіх трэба пераўтварыць у тэкставы выгляд. Знаходзячыся ва ўкладцы "Галоўная" клікаем па піктаграме ў выглядзе трыкутніка справа ад поля, у якім адлюстроўваецца фармат. Яно размешчана ў блоку інструментаў "Лічба".

У якое адкрылася пераліку варыянтаў фарматавання выбіраем пункт "Тэкставы".

Далей, як і ў папярэднім спосабе, паслядоўна усталёўваем курсор у кожны элемент дыяпазону падвойным пстрычкай левай кнопкі мышы або націскам клавішы F2, А затым пстрыкаем па клавішы Enter.

Дадзеныя ператвараюцца ў тэкставы варыянт.

Спосаб 3: выкарыстанне функцыі

Яшчэ адным варыянтам пераўтварэнні лікавых дадзеных у тэставыя ў Эксэля з'яўляецца прымяненне спецыяльнай функцыі, якая так і называецца - ТЭКСТ. Дадзены спосаб падыдзе, у першую чаргу, калі вы хочаце перанесці колькасці як тэкст у асобны слупок. Акрамя таго, ён дазволіць зэканоміць час на пераўтварэнні, калі аб'ём дадзеных занадта вялікі. Бо, пагадзіцеся, што перещелкивать кожнае вочка ў дыяпазоне, які налічвае сотні ці тысячы радкоў - гэта не самае лепшае выйсце.

Усталёўваем курсор у першы элемент дыяпазону, у якім будзе выводзіцца вынік пераўтварэнні. Пстрыкаем па значку "Уставіць функцыю", Які размешчаны каля радкі формул.

запускаецца акно майстры функцый. У катэгорыі "Тэкставыя" вылучаем пункт "ТЭКСТ". Пасля гэтага клікаем па кнопцы "OK".

Адкрываецца акно аргументаў аператара ТЭКСТ. Дадзеная функцыя мае наступны сінтаксіс:
= ТЭКСТ (значэнне; фармат)
Якое адкрылася акно мае два поля, якія адпавядаюць дадзеным аргументаў: "Значэнне" і "Фармат".
У полі "Значэнне" трэба паказаць пераўтвараць лік або спасылку на вочка, у якой яно знаходзіцца. У нашым выпадку гэта будзе спасылка на першы элемент апрацоўванага лікавага дыяпазону.
У полі "Фармат" трэба паказаць варыянт адлюстравання выніку. Напрыклад, калі мы ўвядзем "0", То тэкставы варыянт на выхадзе будзе адлюстроўвацца без дзесятковых знакаў, нават калі ў зыходніку яны былі. Калі мы занясем "0,0", То вынік будзе адлюстроўвацца з адным дзесятковым знакам, калі "0,00", То з двума, і г.д.
Пасля таго, як усе патрабаваныя параметры ўведзеныя, пстрыкаем па кнопцы "OK".

Як бачым, значэнне першага элемента зададзенага дыяпазону адлюстравалася ў вочку, якую мы выдзелілі яшчэ ў першым пункце дадзенага кіраўніцтва. Для таго, каб перанесці і іншыя значэнні, трэба скапіяваць формулу ў сумежныя элементы ліста. Усталёўваем курсор у ніжні правы кут элемента, які змяшчае формулу. Курсор пераўтворыцца ў маркер запаўнення, які мае выгляд невялікага крыжык. Зацісканы левую кнопку мышы і працягваць па пустых вочках паралельна дыяпазону, у якім знаходзяцца зыходныя дадзеныя.

Цяпер увесь шэраг запоўнены патрабаванымі дадзенымі. Але і гэта яшчэ не ўсё. Па сутнасці, усе элементы новага дыяпазону ўтрымліваюць у сабе формулы. Вылучаем гэтую вобласць і ціснем на значок "Капіяваць", Які размешчаны ва ўкладцы "Галоўная" на стужцы інструментаў групе "Буфер абмену".

Далей, калі мы хочам захаваць абодва дыяпазону (зыходны і ператвораны), не здымаем вылучэнне з вобласці, якая ўтрымлівае формулы. Клікаем па ёй правай кнопкай мышы. Адбываецца запуск кантэкстнага спісу дзеянняў. Выбіраем у ім пазіцыю "Спецыяльная ўстаўка". Сярод варыянтаў дзеянняў у якое адкрылася спісе выбіраем "Значэнні і фарматы лікаў".
Калі ж карыстальнік жадае замяніць дадзеныя зыходнага фармату, то замест названага дзеянні трэба вылучыць менавіта яго і вырабіць ўстаўку тым жа спосабам, які паказаны вышэй.

У любым выпадку, у выбранага дыяпазону будуць устаўленыя дадзеныя ў тэкставым выглядзе. Калі ж вы ўсё ж такі выбралі ўстаўку ў зыходную вобласць, то вочкі, якія змяшчаюць формулы, можна ачысціць. Для гэтага вылучаем іх, клікаем правай кнопкай мышы і выбіраем пазіцыю "Ачысціць зьмест".

На гэтым працэдуру пераўтварэнні можна лічыць скончанай.

Урок: Майстар функцый у Excel

Канвертацыя тэксту ў лік

Зараз давайце разбярэмся, якімі спосабамі можна выканаць зваротную задачу, а менавіта як пераўтварыць тэкст у лік у Excel.

Спосаб 1: пераўтварэнне з дапамогай значка пра памылку

Прасцей і хутчэй за ўсё выканаць канвертаванне тэкставага варыянту з дапамогай адмысловага значка, які паведамляе пра памылку. Гэты значок мае выгляд клічніка, упісанага ў піктаграму ў выглядзе ромба. Ён з'яўляецца пры выдзяленні вочак, якія маюць пазнаку ў левым верхнім куце зялёным колерам, якая абумоўліваецца намі раней. Гэтая пазнака яшчэ не сведчыць аб тым, што дадзеныя знаходзяцца ў вочку абавязкова памылковыя. Але лічбы, размешчаныя ў вочку якая мае тэкставы выгляд, выклікаюць падазрэнні ў праграмы ў тым, што дадзеныя могуць быць унесены некарэктна. Таму на ўсялякі выпадак яна іх пазначае, каб карыстальнік звярнуў увагу. Але, на жаль, такія адзнакі Эксэля выдае не заўсёды нават тады, калі лічбы прадстаўленыя ў тэкставым выглядзе, таму ніжэй апісаны спосаб падыходзіць не для ўсіх выпадкаў.

Вылучаем вочка, у якой утрымліваецца зялёны індыкатар аб магчымай памылцы. Клікаем па якая з'явілася піктаграме.

Адкрываецца спіс дзеянняў. Выбіраем у ім значэнне "Пераўтварыць у лік ".

У вылучаным элеменце дадзеныя тут жа будуць ператвораныя ў лікавы выгляд.

Калі падобных тэкставых значэнняў, якія варта пераўтварыць, не адно, а мноства, то ў гэтым выпадку можна паскорыць працэдуру пераўтварэнні.

Вылучаем увесь дыяпазон, у якім знаходзяцца тэкставыя дадзеныя. Як бачым, піктаграма з'явілася адна для ўсёй вобласці, а не для кожнага вочка паасобку. Пстрыкаем па ёй.

Адкрываецца ўжо знаёмы нам спіс. Як і ў мінулы раз, выбіраем пазіцыю "Пераўтварыць у лік".

Усе дадзеныя масіва будуць ператвораныя ў названы выгляд.

Спосаб 2: канвертацыя пры дапамозе акна фарматавання

Як і для пераўтварэння дадзеных з лікавага віду ў тэкст, у Эксэля існуе магчымасць зваротнага канвертавання праз акно фарматавання.

Вылучаем дыяпазон, які змяшчае лічбы ў тэкставым варыянце. Клікаем правай кнопкай мышы. У кантэкстным меню выбіраем пазіцыю "Фармат вочак ...".

Выконваецца запуск вокны фарматавання. Як і ў папярэдні раз, пераходзім ва ўкладку "Лічба". У групе "Лікавыя фарматы" нам трэба выбраць значэння, якія дазволяць пераўтварыць тэкст у лік. Да іх адносіцца пункты "Агульны" і "Лікавай". Які бы з іх вы не абралі, праграма будзе расцэньваць лічбы, уведзеныя ў вочка, як лікі. Вырабляем выбар і ціснем на кнопку. Калі вы выбралі значэнне "Лікавай", То ў правай частцы акна з'явіцца магчымасць адрэгуляваць ўяўленне колькасці: выставіць колькасць дзесятковых знакаў пасля коскі, усталяваць падзельнікамі паміж разрадамі. Пасля таго, як налада выканана, ціснем на кнопку "OK".

Цяпер, як і ў выпадку пераўтварэння чысла ў тэкст, нам трэба прашчоўкалі усе вочкі, усталяваўшы ў кожную з іх курсор і націснуўшы пасля гэтага клавішу Enter.

Пасля выканання гэтых дзеянняў ўсе значэння абранага дыяпазону пераўтворацца ў патрэбны нам выгляд.

Спосаб 3: канвертацыя пры дапамозе інструментаў на стужцы

Перавесці тэкставыя дадзеныя ў лікавыя можна, скарыстаўшыся адмысловым полем на стужцы інструментаў.

Вылучаем дыяпазон, які павінен падвергнуцца трансфармацыі. Пераходзім ва ўкладку "Галоўная" на стужцы. Клікаем па полі з выбарам фармату ў групе "Лічба". выбіраем пункт "Лікавай" або "Агульны".

Далей прощелкиваем ўжо не раз апісаным намі спосабам кожнае вочка ператваральнай вобласці з ужываннем клавіш F2 і Enter.

Значэння ў дыяпазоне будуць ператвораныя з тэкставых ў лікавыя.

Спосаб 4: прымяненне формулы

Таксама для пераўтварэння тэкставых значэнняў у лікавыя можна выкарыстоўваць спецыяльныя формулы. Разгледзім, як гэта зрабіць на практыцы.

У пустой вочку, размешчанай паралельна першаму элементу дыяпазону, які варта пераўтварыць, ставім знак "роўна" (=) і двайны сімвал "мінус" (-). Далей паказваем адрас першага элемента трансфармуемай дыяпазону. Такім чынам, адбываецца падвойнае множанне на значэнне "-1". Як вядома, множанне "мінус" на "мінус" дае "плюс". Гэта значыць, у мэтавай вочку мы атрымліваем тое ж значэнне, якое было першапачаткова, але ўжо ў лікавым выглядзе. Даная працэдура называецца падвойным бінарным адмаўленнем.

Ціснем на клавішу Enter, Пасля чаго атрымліваем гатовае ператворанае значэнне. Для таго, каб прымяніць дадзеную формулу для ўсіх іншых вочак дыяпазону, выкарыстоўваем маркер запаўнення, які раней быў ужыты намі для функцыі ТЭКСТ.

Цяпер мы маем дыяпазон, які запоўнены значэннямі з формуламі. Вылучаем яго і ціснем на кнопку "Капіяваць" ва ўкладцы "Галоўная" або ўжываем спалучэнне клавіш Ctrl + C.

Вылучаем зыходную вобласць і вырабляем пстрычка па ёй правай кнопкай мышы. У Актываваць кантэкстным спісе пераходзім па пунктах "Спецыяльная ўстаўка" і "Значэнні і фарматы лікаў".

Усе дадзеныя устаўленыя ў патрэбным нам выглядзе. Зараз можна выдаліць транзітны дыяпазон, у якім знаходзіцца формула падвойнага бінарнага адмаўлення. Для гэтага вылучаем дадзеную вобласць, клікам правай кнопкі мышы выклікаем кантэкстнае меню і выбіраем у ім пазіцыю "Ачысціць зьмест".

Дарэчы, для пераўтварэння значэнняў дадзеным метадам зусім не абавязкова выкарыстоўваць выключна падвойнае множанне на "-1". Можна ўжываць любое іншае арыфметычнае дзеянне, якое не вядзе да змены значэнняў (складанне або адніманне нуля, выкананне ўзвядзення ў першую ступень і г.д.)

Урок: Як зрабіць аўтазапаўненне ў Excel

Спосаб 5: прымяненне спецыяльнай ўстаўкі

Наступны спосаб па прынцыпе дзеяння вельмі падобны на папярэдні з той толькі розніцай, што для яго выкарыстання не трэба ствараць дадатковы слупок.

У любую пустую ячэйку на лісце ўпісваем лічбу "1". Затым вылучаем яе і ціснем на знаёмы значок "Капіяваць" на стужцы.

Вылучаем вобласць на лісце, якую варта пераўтварыць. Клікаем па ёй правай кнопкай мышы. У якое адкрылася меню двойчы пераходзім па пункце "Спецыяльная ўстаўка".

У акне спецыяльнай ўстаўкі выстаўляем перамыкач у блоку "Аперацыя" у пазіцыю "Памножыць". Услед за гэтым ціснем на кнопку "OK".

Пасля гэтага дзеяння усе значэння вылучанай вобласці будуць ператвораныя ў лікавыя. Цяпер пры жаданні можна выдаліць лічбу "1", Якую мы выкарыстоўвалі ў мэтах канвертавання.

Спосаб 6: выкарыстанне інструмента "Тэкст слупкамі"

Яшчэ адным варыянтам, пры якім можна пераўтварыць тэкст у лікавы выгляд, з'яўляецца прымяненне інструмента "Тэкст слупкамі". Яго ёсць сэнс выкарыстаць тады, калі замест коскі ў якасці падзельніка дзесятковых знакаў выкарыстоўваецца кропка, а ў якасці падзельніка разрадаў замест прабелу - апостраф. Гэты варыянт ўспрымаецца ў англамоўным Эксэля, як лікавы, але ў рускамоўнай версіі гэтай праграмы ўсе значэння, якія ўтрымліваюць названыя вышэй знакі, ўспрымаюцца як тэкст. Вядома, можна перабіць дадзеныя ўручную, але калі іх шмат, гэта зойме значнае колькасць часу, тым больш што існуе магчымасць значна больш хуткага вырашэння праблемы.

Вылучаем фрагмент ліста, змесціва якога трэба пераўтварыць. Пераходзім ва ўкладку "Дадзеныя". На стужцы інструментаў у блоку "Праца з дадзенымі" клікаем па значку "Тэкст па слупках".

запускаецца майстар тэкстаў. У першым акне звярніце ўвагу, каб перамыкач фармату дадзеных стаяў у пазіцыі "З падзельнікамі". Па змаўчанні ён павінен знаходзіцца ў гэтай пазіцыі, але праверыць стан будзе не лішнім. Затым клікаем па кнопцы "Далей".

У другім акне таксама пакідаем усё без змен і ціснем на кнопку "Далей".

А вось пасля адкрыцця трэцяга акна майстры тэкстаў трэба націснуць на кнопку "Чытаць далей".

Адкрываецца акно дадатковай налады імпарту тэксту. У полі "Падзельнік цэлай і дробавай часткі" усталёўваем кропку, а ў полі "Падзельнік разрадаў" - апостраф. Затым робім адзін пстрычка па кнопцы "OK".

Вяртаемся ў трэцяе акно майстры тэкстаў і ціснем на кнопку "Зроблена".

Як бачым, пасля выканання дадзеных дзеянняў колькасці прынялі звыклы для рускамоўнай версіі фармат, а гэта значыць, што яны адначасова былі ператвораныя з тэкставых дадзеных у лікавыя.

Спосаб 7: прымяненне макрасаў

Калі вам часта даводзіцца ператвараць вялікія вобласці дадзеных з тэкставага фармату ў лікавы, то маецца сэнс у гэтых мэтах запісаць спецыяльны макрас, які будзе выкарыстоўвацца пры неабходнасці. Але для таго, каб гэта выканаць, перш за ўсё, трэба ў сваёй версіі Эксэля ўключыць макрасы і панэль распрацоўніка, калі гэта да гэтага часу не зроблена.

Пераходзім ва ўкладку "Распрацоўнік". Ціснем на значок на стужцы "Visual Basic", Які размешчаны ў групе "Код".

Запускаецца стандартны рэдактар макрасаў. Ўбіваем або капіяваны ў яго наступнае выраз:
Sub Текст_в_число () Selection.NumberFormat = "General" Selection.Value = Selection.Value End Sub
Пасля гэтага зачыняем рэдактар, выканаўшы націск стандартнай кнопкі закрыцця ў верхнім правым куце акна.

Вылучаем фрагмент на лісце, які трэба пераўтварыць. Ціснем на значок "Макрасы", Які размешчаны на ўкладцы "Распрацоўнік" ў групе "Код".

Адкрываецца акно запісаных у вашай версіі праграмы макрасаў. Знаходзім макрас з найменнем "Текст_в_число", Вылучаем яго і ціснем на кнопку "Выканаць".

Як бачым, тут жа адбываецца пераўтварэнне тэкставага выразы ў лікавы фармат.

Урок: Як стварыць макрас ў Эксэля

Як бачым, існуе даволі шмат варыянтаў пераўтварэнні ў Excel лічбаў, якія запісаныя ў лікавым варыянце, у тэкставы фармат і ў адваротным кірунку. Выбар пэўнага спосабу залежыць ад шматлікіх фактараў. Перш за ўсё, гэта пастаноўка задачы. Бо, напрыклад, хутка пераўтварыць тэкставае выраз з замежнымі падзельнікамі ў лікавае можна толькі выкарыстаўшы інструмент "Тэкст слупкамі". Другі фактар, які ўплывае на выбар варыянту - гэта аб'ёмы і частата выконваемых пераўтварэнняў. Напрыклад, калі вы часта карыстаецеся падобныя пераўтварэнні, мае сэнс зрабіць запіс макраса. І трэці фактар - індывідуальнае зручнасць карыстальніка.