Contents
Jiya a gudun marathon 30 Excel yana aiki a cikin kwanaki 30 mun gane nau'ikan kurakurai ta amfani da aikin KUSKURE. TYPE (ERROR TYPE) da kuma tabbatar da cewa zai iya zama da amfani sosai don gyara kurakurai a cikin Excel.
A ranar 18th na marathon, za mu ƙaddamar da nazarin aikin SEARCH (NENE). Yana neman harafi (ko haruffa) a cikin saƙon rubutu kuma yana ba da rahoton inda aka samo shi. Za mu kuma duba yadda za mu magance yanayin da wannan aikin ke jefa kuskure.
Don haka, bari mu dubi ƙa'idar da misalai masu amfani na aikin SEARCH (NENE). Idan kuna da wasu dabaru ko misalai na aiki tare da wannan aikin, da fatan za a raba su a cikin sharhi.
Aiki 18: NEMAN
aiki SEARCH (NENE) yana nemo zaren rubutu a cikin wani saƙon rubutu, kuma idan an same shi, yana ba da rahoton matsayinsa.
Ta yaya zan iya amfani da aikin SEARCH?
aiki SEARCH (NENE) yana neman zaren rubutu a cikin wani saƙon rubutu. Ta iya:
- Nemo zaren rubutu a cikin wani kirtani na rubutu (harka maras ji).
- Yi amfani da haruffan kati a cikin bincikenku.
- Ƙayyade matsayi na farawa a cikin rubutun da aka gani.
SEARCH Syntax
aiki SEARCH (SEARCH) yana da ma'ana mai zuwa:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- sami_rubutu (search_text) shine rubutun da kuke nema.
- cikin_rubutu (text_for_search) - layin rubutu wanda a ciki ake yin binciken.
- farawa_num (start_position) – idan ba a kayyade ba, binciken zai fara daga harafin farko.
Tarko SEARCH (NENE)
aiki SEARCH (NENE) zai dawo da matsayin kirtani mai daidaitawa ta farko, yanayin rashin jin daɗi. Idan kuna buƙatar bincike mai mahimmanci, zaku iya amfani da aikin KA (FIND), wanda zamu hadu daga baya a tseren marathon 30 Excel yana aiki a cikin kwanaki 30.
Misali 1: Neman rubutu a cikin kirtani
Yi amfani da aikin SEARCH (NEMI) don nemo wani rubutu a cikin zaren rubutu. A cikin wannan misali, za mu nemo harafi ɗaya (wanda aka buga a cikin tantanin halitta B5) a cikin rubutun da aka samo a cikin tantanin halitta B2.
=SEARCH(B5,B2)
=ПОИСК(B5;B2)
Idan an samo rubutun, aikin SEARCH (NENE) zai dawo da lambar matsayi na harafin sa na farko a cikin zaren rubutu. Idan ba a samo shi ba, sakamakon zai zama saƙon kuskure #DARAJAR! (#SO).
Idan sakamakon kuskure ne, zaku iya amfani da aikin IFEROR (IFERROR) don haka maimakon aiwatar da aikin SEARCH (NENE) yana nuna saƙon da ya dace. Aiki IFEROR (IFERROR) an gabatar da shi a cikin Excel farawa a cikin sigar 2007. A cikin sigogin da suka gabata, ana iya samun sakamako iri ɗaya ta amfani da IF (IF) tare da ISERROR (EOSHIBKA).
=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")
Misali 2: Amfani da katunan daji tare da SEARCH
Wata hanyar duba sakamakon ta dawo SEARCH (NENE), don kuskure - yi amfani da aikin ISNUMBER (ISNUMBER). Idan an sami kirtani, sakamakon SEARCH (SEARCH) zai zama lamba, wanda ke nufin aiki ISNUMBER (ISNUMBER) zai dawo GASKIYA. Idan ba a samo rubutun ba, to SEARCH (SEARCH) zai ba da rahoton kuskure, kuma ISNUMBER (ISNUMBER) zai dawo KARYA.
A cikin darajar hujja sami_rubutu (search_text) zaka iya amfani da haruffan kati. Alama * (asterik) yana maye gurbin kowane adadin haruffa ko babu, kuma ? (alamar tambaya) tana maye gurbin kowane hali guda ɗaya.
A cikin misalinmu, ana amfani da sifa mai ban sha'awa *, don haka za a sami jimlolin CENTRAL, CENTER, da CENTER a cikin sunayen titi.
=ISNUMBER(SEARCH($E$2,B3))
=ЕЧИСЛО(ПОИСК($E$2;B3))
Misali 3: Ƙayyade matsayin farawa don BINCIKE (NENE)
Idan muka rubuta alamomin ragi biyu (biyu negation) a gaban aikin ISNUMBER (ISNUMBER), zai dawo da kimar 1/0 maimakon GASKIYA/KARYA (GASKIYA/KARYA). Na gaba, aikin SUM (SUM) a cikin tantanin halitta E2 zai ƙidaya jimlar adadin bayanan da aka samo rubutun bincike.
A cikin misali mai zuwa, shafi na B yana nuna:
Sunan birni | Sana'a
Ayyukanmu shine nemo sana'o'in da ke ɗauke da saƙon rubutu da aka shigar a cikin cell E1. Tsarin da ke cikin cell C2 zai kasance:
=--ISNUMBER(SEARCH($E$1,B2))
=--ЕЧИСЛО(ПОИСК($E$1;B2))
Wannan tsari ya samo layuka da ke dauke da kalmar "banki", amma a daya daga cikinsu ana samun wannan kalmar ba da sunan sana'a ba, amma a cikin sunan birni. Wannan bai dace da mu ba!
Kowane sunan birni yana biye da alama | (masanin tsaye), don haka mu, muna amfani da aikin SEARCH (NENE), zamu iya samun matsayin wannan halin. Za a iya bayyana matsayinsa azaman ƙimar hujja farawa_num (start_position) a cikin aikin "babban". SEARCH (NENE). A sakamakon haka, za a yi watsi da sunayen birni ta hanyar binciken.
Yanzu dabarar da aka gwada da kuma gyara za ta ƙidaya waɗannan layin da ke ɗauke da kalmar "banki" da sunan sana'a:
=--ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
=--ЕЧИСЛО(ПОИСК($E$1;B2;ПОИСК("|";B2)))