Mukati
Nezuro mumarathon 30 Excel inoshanda mumazuva makumi matatu takaziva mhando dzezvikanganiso tichishandisa basa ERROR.TYPE (ERROR TYPE) uye ikava nechokwadi chekuti inogona kubatsira zvakanyanya pakugadzirisa zvikanganiso muExcel.
Pazuva remakumi maviri nenhatu remarathon, isu tichapa chidzidzo chebasa racho SEARCH (TSVAGA). Inotsvaga mavara (kana mavara) mukati memutsara wetambo uye inoshuma kwayakawanikwa. Tichatarisawo maitiro ekuita nemamiriro ezvinhu apo basa iri rinokanda kukanganisa.
Saka, ngatitarisei zvakanyanya dzidziso uye mienzaniso inoshanda yebasa racho SEARCH (TSVAGA). Kana iwe uine mamwe matipi kana mienzaniso yekushanda neiri basa, ndapota igovera ivo mune zvakataurwa.
Basa 18: TSVAGA
basa SEARCH (TSVAGA) inotsvaga tambo yemavara mukati meimwe tambo yemavara, kana yawanikwa, inoshuma pairi.
Ndingashandisa sei SEARCH basa?
basa SEARCH (TSVAGA) inotsvaga tambo yemavara mukati meimwe tambo yemavara. Anogona:
- Tsvaga tambo yemavara mukati meimwe tambo yemavara (case insensitive).
- Shandisa mavara emusango mukutsvaga kwako.
- Sarudza nzvimbo yekutanga mune inotaridzwa chinyorwa.
TSVAGA Syntax
basa SEARCH (TSVAGA) ine chirevo chinotevera:
SEARCH(find_text,within_text,[start_num])
ПОИСК(искомый_текст;текст_для_поиска;[нач_позиция])
- find_text (search_text) ndiwo mavara auri kutsvaga.
- mukati_chinyorwa (text_for_search) - tambo yemavara mukati umo kutsvaga kunoitwa.
- start_num (start_position) - kana isina kutaurwa, kutsvaga kuchatanga kubva pamunhu wekutanga.
Misungo TSVAGA (SEARCH)
basa SEARCH (TSVAGA) ichadzosa chinzvimbo chetambo yekutanga inoenderana, isinganzwe. Kana iwe uchida tsvakiridzo yakasarudzika, unogona kushandisa basa racho ZIVA (TSVAGA), iyo yatichasangana gare gare mu marathon 30 Excel inoshanda mumazuva makumi matatu.
Muenzaniso 1: Kutsvaga mavara mutambo
Shandisa basa SEARCH (TSVAGA) kutsvaga mamwe mavara mukati memutsara wetambo. Mumuenzaniso uyu, tichange tichitsvaga munhu mumwechete (akadhindwa muchitokisi B5) mukati metambo yemavara inowanikwa muchitokisi B2.
=SEARCH(B5,B2)
=ПОИСК(B5;B2)
Kana chinyorwa chikawanikwa, basa racho SEARCH (TSVAGA) ichadzosa nhamba yenzvimbo yemavara ayo ekutanga mumutsara wetambo. Kana ikasawanikwa, mhedzisiro ichava meseji yekukanganisa #VALUE! (#SO).
Kana mhedzisiro iri kukanganisa, unogona kushandisa basa IFERROR (IFERROR) kuitira kuti pane kuita basa racho SEARCH (TSVAGA) ratidza meseji inoenderana. Function IFERROR (IFERROR) yakaunzwa muExcel kutanga muvhezheni 2007. Mushanduro dzekutanga, mhinduro imwe chete yaigona kuwanikwa pachishandiswa. IF (IF) pamwe chete ISERROR (EOSHIBKA).
=IFERROR(SEARCH(B5,B2),"Not Found")
=ЕСЛИОШИБКА(ПОИСК(B5;B2);"Not Found")
Muenzaniso 2: Kushandisa makadhi emusango ane SEARCH
Imwe nzira yekutarisa mhinduro yakadzoka SEARCH (TSVAGA), pane kukanganisa - shandisa basa ISNUMBER (ISNUMBER). Kana tambo inowanikwa, mhedzisiro SEARCH (TSVAGA) ichava nhamba, zvinoreva basa ISNUMBER (ISNUMBER) ichadzoka TRUE. Kana chinyorwa chikasawanikwa, saka SEARCH (TSVAGA) ichataura kukanganisa, uye ISNUMBER (ISNUMBER) ichadzorera FALSE.
Mukukosha kwenharo find_text (search_text) unogona kushandisa mavara emusango. Symbol * (asterisk) inotsiva chero nhamba yemavara kana hapana, uye ? (mubvunzo mucherechedzo) inotsiva chero chimiro chimwe chete.
Mumuenzaniso wedu, iyo wildcard character inoshandiswa *, saka mazwi CENTRAL, CENTER, uye CENTER achawanikwa mumazita emigwagwa.
=ISNUMBER(SEARCH($E$2,B3))
=ЕЧИСЛО(ПОИСК($E$2;B3))
Muenzaniso 3: Kuona pekutangira kutsvaga (TSVAGA)
Kana tikanyora minus miviri miviri (double negation) pamberi pechiitwa ISNUMBER (ISNUMBER), ichadzosa kukosha 1/0 pachinzvimbo chekuti CHOKWADI/NHEMA (CHOKWADI/NHEMA). Tevere, basa SUM (SUM) muchitokisi E2 ichaverenga nhamba yose yezvinyorwa apo zvinyorwa zvekutsvaga zvakawanikwa.
Mumuenzaniso unotevera, chikamu B chinoratidza:
Guta zita | Basa
Basa redu nderekutsvaga maprofessionals ane mavara tambo akaiswa muchitokisi E1. Iyo formula musero C2 ingave:
=--ISNUMBER(SEARCH($E$1,B2))
=--ЕЧИСЛО(ПОИСК($E$1;B2))
Iyi fomula yakawana mitsara ine izwi rekuti "bhangi", asi mune rimwe rawo izwi iri hariwanikwi muzita rehunyanzvi, asi muzita reguta. Izvi hazvikodzeri isu!
Zita reguta rimwe nerimwe rinoteverwa nechiratidzo | (vertical bar), saka isu, tichishandisa basa SEARCH (TSVAGA), tinogona kuwana nzvimbo yemunhu uyu. Nzvimbo yaro inogona kutsanangurwa sekukosha kwenharo start_num (start_position) mu "main" basa SEARCH (TSVAGA). Nekuda kweizvozvo, mazita emaguta achafuratirwa nekutsvaga.
Ikozvino iyo yakaedzwa uye yakagadziriswa fomula ichaverenga chete iyo mitsetse ine izwi rekuti "bhangi" muzita rebasa:
=--ISNUMBER(SEARCH($E$1,B2,SEARCH("|",B2)))
=--ЕЧИСЛО(ПОИСК($E$1;B2;ПОИСК("|";B2)))