Advanced Filter muna Excel: mienzaniso. How kuti yemhando Sefa muna Excel uye kuishandisa?

vashandi vakawanda nemasangano akasiyana-siyana vane kuti zvimwe kushanda Mircosot Excel, kana vanhuwo maakaundendi uye vaongorori tinowanzova nenguva kusarudza vanoverengeka netsika dzinobva akasiyana-siyana mashoko. Kurerutsa basa iri uye akasikwa kubudikidza filtration hurongwa.

Simple uye yemhando firita

In Excel inoratidza Sefa nyore kuti kunotanga ne "Data" Tab - "Filter» (Data - Filter muna Chirungu shanduro chirongwa) kana kushandisa yokudimbudzira pamusoro Toolbar, zvakafanana fanera cône serin'i kuti tama mvura mumidziyo pamwe yakatetepa mitsipa.

Kazhinji mashoko Sefa iri zvachose yakanakisisa kuzvipedza. Asi kana uchida kuita kusarudzwa vakawanda ezvinhu (uye kunyange mhiri vakawanda mbiru, misara uye masero), vakawanda vari kushamisika sei kuti yemhando Sefa muna Excel. The Chirungu shanduro inonzi Advanced firita.

Kushandiswa akura firita wokutanga

In Excel vakawanda basa kunoitika pamwe pamatafura. Chekutanga, zviri nyore, uye chepiri mune rimwe faira, unogona kuponesa mashoko pamapeji akawanda (Tabs). Above huru tafura, zviri zvinodiwa kusika mashoma mitsetse, ari topmost iyo asara kuna chivharo, zviri date mutsetse kuchaita aenderane ezvinhu Excel yemhando firita. Zvadaro, mukuru Sefa zvichida kushandurwa kana ezvinhu anoda zvakawanda, unogona nguva dzose dzinoisa munzvimbo zvakanaka kunyange chimwe mutsetse. Zvisinei, zviri chinodiwa kuti mamiriro pakati masero uye mumasero kunobva Data siyana aiva mumwe musara unactuated.

Sei kushandisa yepamusoro Sefa kuna Excel, mienzaniso inokurukurwa pazasi.

Kuita firita

Tafura mitsetse 1 ne2 zvakagadzirirwa zvakawanda ezvinhu, mitsetse 4 kusvika 7 - kuti kunobva mashoko kwaasingasvikiri.

Kutanga kupinda mutsetse 2 runoenzanirana tsika, izvo zvichaita kuti vamutye nemhaka akura Sefa muna Excel.

Kutanga ari Sefa nokushandisa Sero mutaro kwokutanga data, ipapo unofanira kusarudza "Data" Tab uye baya "Advanced» (Data - Advanced, ukuwo).

A sikirini anoratidza zvakawanda masero yasarudzwa mu "Source Range". Maererano nomuenzaniso apa, mutsetse anotora ukoshi «$ A $ 4: $ F $ 12".

The "Mamiriro ezvinhu Range" anofanira kuzadza tsika «$ A $ 1: $ F $ 2."

The hwindo rinewo maviri ezvinhu:

• dzinobvisa ndandanda panzvimbo;
• kutevedzera mibairo yakasiyana nzvimbo.

Chokutanga ezvinhu achibvumira paumbwe mugumisiro uri panzvimbo, pasi mugove kunobva Range sero. Mamiriro wechipiri unobvumidza kuti vaunze mazita kunoita rimwe bhendi, izvo zvinofanira raanotaura "Wedzerai kwakaita kuti kuwanda." The mushandisi anosarudza mukana chisarudzo Somuenzaniso, hwindo wokutanga, "Advanced Filter" muna Excel rakavharwa.

Zvichienderana nemashoko vakapinda, asi Sefa achaita zvinoumba zvinotevera tafura.

Kana kushandisa mashoko okuti "Copy chaitika imwe nzvimbo" tsika 4 uye 5 Rwonzi akaratidza muna zvakafanorongwa siyana pedyo user. The kwokutanga Range achagara kuchinja.

Nyore

The zvinorondedzerwa Nzira hakusi nyore, nokuti kuvandudza kazhinji vanoshandisa VBA zvirongwa mutauro, izvo vakasimudza macro kuti michina yemhando Sefa muna Excel.

Kana mushandisi unoziva VBA, Zvinokurudzirwa kudzidza zvinoverengeka nyaya iyi uye kuti abudirire zvirongwa zvedu. Kana kushandura mitsara masero 2, zvakagoverwa pasi Excel yepamusoro Sefa, kuwanda ezvinhu ichachinja pazviruva itangezve pakarepo uye vakatanga vari yakafanira siyana zvakare ndizvo zvinoumba zvakakodzera ruzivo.

dzakaoma queries

Mukuwedzera kushanda chaizvo zvakafanorongwa tsika yepamusoro Sefa muna Excel, uye anokwanisa kushandisa queries dzakaoma. Ava vakapinda data, apo chikamu vanotaurwa ne wildcards.

The chiratidzo tafura queries dzakaoma dziri pazasi.

Zvinokosha kuziva kuti chiratidzo + kungareva chero nhamba pazvinhu. Ndiko, kana vakapinda ukoshi "n" anodzoka tsika zvose, zvisinei nhamba mabhii mushure tsamba "n."

The chiratidzo "?" Kunoreva mumwe chete hunhu.

Zvisote AND uye OR

Tapota ziva kuti mashoko akapiwa mumwe rwokuyera "siyana ezvinhu" vanoonekwa yakanyorwa boka inonzwisisika opareta (AND). Izvi zvinoreva kuti zvinhu zvakawanda panguva imwe chete kugutsikana.

Kana chete mashoko chakanyorwa imwe mbiru, akura Sefa muna Excel anoziva avo kuyeuka musoro opareta (OR).

Zviri tsika anotora zvinotevera fomu:

pfupiso pamatafura

Imwe nzira kuchenesa umboo nokushandisa murayiro "Insert - Table - Summary tafura» (Insert - Table - PivotTable in Chirungu shanduro).

Pamatafura zvakafanana kushanda pamwe akazvitsaurira pre-akasarudza date zvenguva uye rakasiyana tsika kuti anoongororwa vakazochinja. Kutaura zvazviri, zvinoita sokuti basa donhwe-pasi pamazita ose nemamwe minda (somuenzaniso, mazita vashandi wekambani) uye kuwanda tsika kuti vanopiwa kusarudza yakasiyana nedzimwe munda.

Zvakaipira kushandisa pfupiso pamatafura ndiye kudiwa Chinyorwa zvinhu apo kuchinja yakatsvuka Data pachavo.

mhedziso

Mukugumisa, zvinofanira kucherechedzwa kuti yezvinoreva Sefa muna Microsoft Excel wakakura chaizvo uye siyana. Zvakatikwanira kushandisa fungidziro uye vave ruzivo rwavo pachavo uye unyanzvi.

Roga, iro Sefa zviri nyore kushandisa uye kudzidza, nyore kunzwisisa sei kushandisa yepamusoro Sefa muna Excel, asi rakagadzirirwa mafomu yaida shoma nguva kuti achivhara mashoko mamwe kugadzira. Kazhinji, hazvina kupa basa makuru anoita mashoko nokuda dzose dzevanhu chinhu. Pane chasvika kubatsira yakagozha mazhinji Uropi michina iri Microsoft Excel.

Huge dzakakurumbira macro, akambochera mumutauro VBA. Ivo umhanye vakawanda filters, kufambisa Kusarudza tsika uye goho navo zvakakodzera neeSultan.

Macro zvinobudirira kutsiva maawa akawanda basa iri richiswedera kukwira Muchidimbu, nguva nenguva uye zvimwe nhau, achitsiva-refu kuongorora muhombe yakawanda watinya wechipiri mumwe chete.

Kushandisa macro akaruramiswa uye kugadzikana. Ani naani vasangana kudiwa kushandisa, kana kutsvakwa, nguva dzose kuwana zvinhu zvakakwana kutanga ruzivo uye kuwanikwa dzavo mhinduro dzemibvunzo.