Сізді мыналар қызықтыруы мүмкін: Excel бағдарламасындағы REPLACE функциясы.
Excel бағдарламасында табу және ауыстыру
Деректерді табу және ауыстыру процедурасы Excel бағдарламасындағы ең танымал әдістердің бірі болып табылады. Негізгі процедура бір уақытта тек бір мәнді ауыстыруға мүмкіндік береді, бірақ көптеген жолдармен. Онымен қалай тиімді жұмыс істеуге болатынын көрейік.
Жылдам пернелер
Төмендегі пернелер тіркесімдері құралмен жұмысты айтарлықтай жылдамдатады:
- Іздеу диалогтық терезесін іске қосу үшін — Ctrl + F.
- Табу және ауыстыру терезесін іске қосу үшін Ctrl + H пернелер тіркесімін басыңыз.
- Барлық табылған ұяшықтарды таңдау үшін («барлығын табу» түймесін басқаннан кейін) — Ctrl + A.
- Барлық табылған ұяшықтарды жою үшін — Ctrl + Delete.
- Барлық табылған ұяшықтарға бірдей деректерді енгізу үшін — Мәтінді енгізу, Ctrl + Enter.
Gif мысалын қараңыз: мұнда мен әрі қарай өңдеуі бар ұяшықтарды іздеп жатырмын. Ауыстырудан айырмашылығы, табылған ұяшықтарды өңдеу олардың бүкіл мазмұнын жылдам өзгертуге мүмкіндік береді.
Процедура «Найти и заменить» не работает
Мен өзім де осы жағдайда бірнеше рет ессіздікке ұшырадым. Қажетті үлгі деректерде екеніне сенімдісіз және өз көзіңізбен көре аласыз, бірақ Excel іздеу процедурасын орындаған кезде есеп береді:
немесе ауыстыру кезінде:
Сонымен, осы хабарлардың екеуінде де «Параметрлер» түймесін басу туралы кеңес шынымен пайдалы – «әріп-әріп» немесе «бүкіл ұяшық» құсбелгілері сол жерде белсенді болуы мүмкін, бұл Excel бағдарламасының іздеген нәрсені табуға кедергі келтіреді. Excel өз кезегінде соңғы іздеудің конфигурациясын сақтайды.
Қойылмалы таңбалар немесе «жұлдызшаны» қалай табуға болады
Құрғақ ресми Excel анықтамасы былай дейді:
- «*» және «?» қойылмалы таңбаларын пайдалануға болатынын;
- «*» және «» дегеніміз не? олардың жоқтығын қоса алғанда бірнеше таңбаны және кез келген бір таңбаны білдіреді;
- олар сәйкес іздеу процедуралары үшін пайдаланылуы мүмкін.
Анықтама «бүкіл ұяшық» опциясымен бірге бұл таңбалардың қосымша сүзгіге және ұяшықтар тобын іздеу процедурасына жүгінбестен мүмкіндік беретінін айтпайды:
- белгілі бір таңбамен, сөзбен немесе мәтінмен аяқталатын ұяшықтарды табу,
- белгілі бір таңбадан, сөзден немесе мәтіннен басталатын ұяшықтарды табу,
- бос емес ұяшықтарды табыңыз.
Төмендегі мысалда біз барлық екі таңбалы сандарды, содан кейін 7-ден басталатын және аяқталатын сандарды, ең соңында барлық бос емес ұяшықтарды табамыз. Ctrl + A пернелер тіркесімі барлық іздеу нәтижелерін таңдауға көмектесетінін еске саламын.
Сонымен жұлдызшаны қалай табуға болады?
Шынымен, мен ұмыттым. Жұлдызшаны табу үшін іздеу жолағына оның алдына ~ (тильде) белгісін қою керек, ол әдетте Esc пернесінің астында орналасады. Бұл «жұлдызшадан», сондай-ақ сұрақ белгісінен құтылуға және оларды қызметтік таңбалар ретінде қарастырмауға мүмкіндік береді.
Бірнеше мәндерді бірнеше мәндермен ауыстыру
Excel бағдарламасында жаппай ауыстыру – өте кең таралған қажеттілік. Көбінесе сіз жаппай және бір уақытта бірнеше таңбаларды, сөздерді және т.б. ауыстыруды қажет етеді. басқаларға. Сонымен қатар қазіргі уақытта Excel стандартты функционалдығында қарапайым құрал жоқ.
Дегенмен, егер сізге шынымен қажет болса, кез келген мәселені шешуге болады. Немен ауыстырғыңыз келетініне байланысты функциялар тіркесімі, тұрақты өрнектер және ең қиын жағдайларда !SEMTools қондырмасы көмектесе алады.
Бұл тапсырма бір мәнмен ауыстырудан күрделірек. Бір қызығы, «REPLACE» функциясы бұл жерде жарамайды – ол ауыстырылған мәтіннің орнын нақты көрсетуді талап етеді. Бірақ «SUBSTITUTE» функциясы көмектесе алады.
SUBSTITUTE функциясы арқылы жаппай ауыстыру
Күрделі формулада бірнеше шарттарды пайдалану арқылы бір уақытта бірнеше мәндерді ауыстыруға болады. Excel сізге ұя салудың 64 деңгейіне дейін пайдалануға мүмкіндік береді – әрекет еркіндігі жоғары. Мысалы, кириллицаны латынға осылайша түрлендіруге болады:
Бұл жағдайда, егер бос орынды ауыстырылған фрагмент ретінде пайдалансаңыз, функцияны бірнеше таңбаны жою үшін пайдалануға болады – ұяшықтан сандарды осылайша жою жолын қараңыз.
Бірақ шешімнің кемшіліктері де бар:
- SUBSTITUTE функциясы регистрді ескереді, ол бір таңбаны ауыстыруды оның екі нұсқасын – бас және кіші регистрде қолдануға мәжбүр етеді. Кейбір жағдайларда, мысалы, жоғарыдағы суреттегідей, бұл артықшылық.
- Ең көбі 64 ауыстыру өте көп, бірақ әлі де шектеу.
- Ресми түрде, осылайша ауыстыру процедурасы жаппай және бірден орын алады, бірақ мұндай формулаларды жазу ұзақтығы бұл артықшылықты жояды. Олар қайта-қайта қолданылатын жағдайларды қоспағанда.
Бірнеше ауыстыру формуласы бар үлгі файлы
Формулада ауыстырылатын үлгілерді нақты көрсетудің орнына оның ішінде ұяшық сілтемелерін пайдалану мүмкіндігі бар, олардың мәндерін өз қалауыңыз бойынша жазуға болады. Бұл күрделі формуланы өңдеуді қажет етпейтіндіктен уақытты үнемдейді.
Файл сілтеме арқылы қолжетімді, бірақ оны жүктеп алудың қажеті жоқ, төмендегі формуланың мәтінін көшіріп алып, оны A1:B64 ауқымынан басқа кез келген ұяшыққа қойыңыз. Формула C1 ұяшығындағы А бағанындағы мәндерді В бағанындағы қарама-қарсы мәндерге ауыстырады.
Міне, ол (мәтіннің кез келген бөлігін үш рет басу = бүкіл формуланы таңдау): A1-B64 ұяшықтарында көрсетілген ережелерге сәйкес 64 ауыстыруды жасай отырып, D1 ұяшығына сілтеме жасайды. Сонымен қатар, сіз бағандардағы мәндерді жоя аласыз – бұл оның жұмысын бұзбайды.
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(D1;$A$1;$B$1);$A$2;$B$2);$A$3;$B$3);$A$4;$B$4);$A$5;$B$5);$A$6;$B$6);$A$7;$B$7);$A$8;$B$8);$A$9;$B$9);$A$10;$B$10);$A$11;$B$11);$A$12;$B$12);$A$13;$B$13);$A$14;$B$14);$A$15;$B$15);$A$16;$B$16);$A$17;$B$17);$A$18;$B$18);$A$19;$B$19);$A$20;$B$20);$A$21;$B$21);$A$22;$B$22);$A$23;$B$23);$A$24;$B$24);$A$25;$B$25);$A$26;$B$26);$A$27;$B$27);$A$28;$B$28);$A$29;$B$29);$A$30;$B$30);$A$31;$B$31);$A$32;$B$32);$A$33;$B$33);$A$34;$B$34);$A$35;$B$35);$A$36;$B$36);$A$37;$B$37);$A$38;$B$38);$A$39;$B$39);$A$40;$B$40);$A$41;$B$41);$A$42;$B$42);$A$43;$B$43);$A$44;$B$44);$A$45;$B$45);$A$46;$B$46);$A$47;$B$47);$A$48;$B$48);$A$49;$B$49);$A$50;$B$50);$A$51;$B$51);$A$52;$B$52);$A$53;$B$53);$A$54;$B$54);$A$55;$B$55);$A$56;$B$56);$A$57;$B$57);$A$58;$B$58);$A$59;$B$59);$A$60;$B$60);$A$61;$B$61);$A$62;$B$62);$A$63;$B$63);$A$64;$B$64)
Бірнеше мәнді біреуімен ауыстырыңыз
«АЛМАСТЫРУ» функциясын пайдалану
Бірнеше мәндерді бір мәнмен ауыстырған кезде, ұя салудың бірнеше деңгейіне негізделген формулалар механикасы бірнеше мәндерді бірнешемен ауыстырудан ерекшеленбейді. Бұл жай ғана үшінші аргумент (оны немен ауыстыру керек) ұя салудың барлық деңгейлерінде бірдей болады. Айтпақшы, егер сіз оны бос қалдырсаңыз (арасында таңбаларсыз тырнақшалар), онда бұл белгілі бір таңбаларды жояды. Мысал: ұяшықтан сандарды бос орынмен ауыстыру арқылы алып тастаңыз:
=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";"");"0";"")
Тұрақты тіркестерді қолдану
Маңызды: кәдімгі өрнектер Excel бағдарламасына қораптан тыс кірмейді, бірақ төмендегі формулалар !SEMTools қондырмасын орнату арқылы тегін қол жетімді.
Тұрақты өрнектер (RegEx, тұрақты өрнектер) бірнеше таңбаны біреумен ауыстыру қажет болғанда ең қолайлы шешім болып табылады. Барлық осы бірнеше таңбалар әдеттегідей ешқандай бөлгіштерсіз төртбұрышты жақшалар ішінде көрсетілуі керек. Формула мысалдары:
=regexreplace(A1;"\d";"#")
=regexreplace(A1;"\w";"#")
=regexreplace(A1;"а-яА-Я";"#")
=regexreplace(A1;"\s";"_")
Біріншісі барлық сандарды «#» таңбасымен ауыстырады, екіншісі барлық ағылшын әріптерін ауыстырады, ал үшіншісі бас және кіші регистрдегі барлық кириллица таңбаларын ауыстырады. Төртінші кез келген бос орындарды, соның ішінде қойындылар мен жол үзілімдерін астыңғы сызықтармен ауыстырады.
Егер таңбаларды емес, өз кезегінде бірнеше әріптерден, сандардан немесе белгілерден тұратын бірнеше мәндерді ауыстыру қажет болса, синтаксис бұрыннан жақшалар мен тік жолақты «|» пайдалануды болжайды. бөлгіш ретінде.
!SEMTools ішінде жаппай ауыстыру
Excel !SEMTools қондырмасы бірнеше рет басу арқылы барлық деңгейлерде ауыстырулар жасауға мүмкіндік береді:
- таңбалар және олардың комбинациясы,
- тұрақты өрнек үлгілері,
- сөздер,
- тұтас ұяшықтар (белгілі бір дәрежеде VLOOKUP ұқсас).
Бұл ретте процедуралар бастапқы диапазонды өзгертеді, бұл уақытты үнемдейді. Сізге алдымен оны таңдау, тапсырманы шешу, қажетті процедураны шақыру және ауыстырылатын және ауыстырылатын мәндерді сәйкестендіру үшін екі бағанды таңдау керек (егер сіз не өзгерту керектігін білсеңіз, онда мұндай тізімдер бар деп есептеледі).
Мысал: таңбаларды орын алуы бойынша ауыстыру
Ауыстырылған таңбалардың жағдайына қарамастан, орын алуы бойынша әдеттегі ауыстыру процедурасының аналогы. Бір айырмашылығы бар: мұнда ауыстыру ауқымды және сіз «ауыстыру-алмастыру» мән жұптары арқылы қалағаныңызша жолдарды таңдай аласыз.
Төменде жалғыз таңбалары бар мысал берілген, бірақ өрнектер тапсырмаңызға байланысты кез келген нәрсе болуы мүмкін.
Мысалы: Сөздер тізімін басқа сөздер тізімімен ауыстыру
Бұл мысалда сөздер тізімін басқа тізіммен, бұл жағдайда сол сөзбен ауыстыру. Мұнда латын әріптері бар сөздер мен сандарды бір сөзбен ауыстыру арқылы біртектес сөз тіркестерін теру мәселесі шешіледі. Әрі қарай, осы операциядан кейін ең танымал комбинацияларды анықтау үшін бағандағы бірегей мәндерді санауға болады.
!SEMTools 9.18.18 нұсқасынан бастап опция пайда болды: сөздер тізімін ауыстырған кезде бастапқы сөйлемдердегі тыныс белгілерін ескермеңіз, енді сөздердің регистрі сақталады.
