Filter Advanced ee Excel, tusaalooyin. Sida loo sameeyo filter sare ee Excel iyo sida loo isticmaalo?

Shaqaale badan oo ah ururada kala duwan qaba in bartey la Mircosot Excel shaqeeyaan, in xisaabaadka caadiga ah iyo falanqeeyayaal ayaa waxaa inta badan soo food leh doorashada ee tiro ka mid ah qiyamka soo diyaariyeen ballaaran xogta. Si loo fududeeyo hawshan oo waxaa la abuuray by nidaamka sifeynta ah.

filter Simple iyo sare

In Excel muujinaysaa filter fudud in bilaabmaa ah "Data" tab - "Filter» (Data - Filter in version Ingiriisi barnaamijka) ama isticmaalaya toobiye ku toolbar ah, oo la mid ah masaf koorta-qaabeeya for wareejinta cabitaanno ah ee weelasha la Luqunta cidhiidhi ah.

Xaaladaha intooda badan filter gebi ahaanba waa ikhtiyaarka ugu fiican. Laakiin haddii aad rabto in aad si ay u fuliyaan doorashada ee tiro badan oo ah xaaladaha (iyo xitaa guud ahaan tiirar badan, saf iyo unugyada), kuwo badan baa loo yaabeen sida loo sameeyo filter sare ee Excel. The version Ingiriisi waxa loo yaqaan filter Advanced.

Isticmaalka hore ee filter sare

In Excel, inta badan shaqo ka qaadataa meel la loox. Marka hore, waa ku haboon, iyo marka labaad, in file hal, waxaad badbaadin kartaa warbixin ku saabsan dhowr bog (tabs). miiska ugu weyn ee kor ku xusan, waa la jecel yahay in la abuuro sadarro dhawr ah, ugu korraysa, ee kaas oo la tagay si ay furka, waa in line xogta ku haboon doonaa shuruudaha Excel filter sare. Ka dibna, filter waxay u badan tahay in la is badali doontaa haddii shuruudaha u baahan tahay ka badan, waxaad had iyo jeer is gelin kara meel sax ah xitaa line hal. Si kastaba ha ahaatee, waa la jecel yahay in xaaladaha dhexeeya unugyada iyo unugyada kala duwan oo xog isha ahaa mid isku xigta unactuated.

Sida loo isticmaalo filter sare si Excel, tusaalooyinka hoos ku wada hadleen.

Dalbashada filter a

In khadadka miiska 1 iyo 2 waxaa loogu talagalay kala duwan oo ka mid ah xaaladaha, khadadka 4 ilaa 7 - ee kala duwan oo xog isha.

Si aad u bilaabaan inay galaan line 2 of qiimaha u dhigma, taas oo laga celinayne doonaa by filter sare ee Excel.

Laga bilaabo filter ah oo isticmaalaya saaridda unugyada xogta bilowga ah, markaas waa inaad dooro "Data" tab iyo guji "Advanced» (Data - Advanced, siday u kala horreeyaan).

screen A bandhigayaa kala duwan oo ka mid ah unugyada la doortay ee "Source Range". Sida laga soo xigtay tusaale ahaan kor ku xusan, line qabanayaa qiimaha «ah $ A $ 4: $ F $ 12".

The "xaaladahooda waxeey" Waa buuxin qiimaha «$ A $ 1: $ F $ 2."

Suuqa ayaa sidoo kale waxaa ku jira laba shuruudo:

• shaandhayso liiska ku jira meesha,
• nuqul natiijada meesha ka duwan.

Shardiga ugu horeysay u ogolaanaya formation of natiijada goobta, hoos unugyada kala duwan oo ah isha ugu qaybiyey. Xaalada labaad ee u ogolaanaya in ay curiyaan liiska natiijooyinka band hal, taas oo waa in la cayimay ee ah "Add natiijada si kala duwan." user wuxuu doortaa doorasho ku haboon, tusaale ahaan, uu furmo suuqa ugu horeysay, "Filter Advanced" ee Excel xiran yahay.

Iyada oo ku saleysan xogta la galay, filter soo dhisi doono miiska soo socda.

Marka aad isticmaaleyso erayada "Copy keeni meel kale" qiyamka 4 iyo 5 khadadka waxaa lagu soo bandhigaa kala duwan ee loo dejiyay by user ah. kala duwan ee hore waxba iska bedeli doonin.

Fududaato in la isticmaalo

Habka tilmaamay ma ku haboon tahay, maxaa yeelay, horumar inta badan isticmaalaan VBA luqadda barnaamijyada, kaas oo kor u macros in qalabayn filter sare ee Excel.

Haddii user uu leeyahay aqoonta VBA, waxaa lagu talinayaa in ay bartaan tiro ka mid ah articles on this topic iyo in ay si guul leh loo fuliyo qorshayaasha our. Haddii aad la beddelo saf oo unugyada 2, qoondeeyay hoos filter Excel sare, kala duwan ee xaaladaha bedeli doonaa goobaha ay si degdeg ah dib oo bilaabay mar kale duwan ku habboon dhisi doono macluumaadka lagama maarmaanka ah.

weydiimo adag

Waxa intaa dheer in la qiimeeyo si hufan loo dejiyay, filter sare ee Excel, shaqada iyo awood u qabashada su'aalo adag. Kuwan waxaa galay xogta, halkaas oo qayb ka mid ah jilayaasha bedelay wildcards.

Shaxda calaamad for su'aalo adag ee hoos ku qoran.

Waxaa xusid mudan og in astaanta * macnaheedu noqon karaa lambarka mid ka mid ah jilayaasha. Taasi waa, marka galay qiimaha "n *" laabtay dhan qiyamka, iyadoo aan loo eegin tirada characters ka dib xarafka "n."

Calaamadda "?" Waxaa loola jeedaa qof ka mid ah oo kaliya.

Xirmooyin IYO iyo OR

Fadlan la soco in macluumaadka ku qoran mid ka mid ah line ee "kala duwan oo ka mid ah xaaladaha" waxaa loo tixgeliyaa diiwaan geliyay farabadan of operator macquul ah (IYO). Taas macnaheedu waa in xaalado dhowr ah waa isku qanacsan.

Haddii xogta la mid ah ayaa laga diiwaan geliyay mid ka mid column, filter sare ee Excel aqoonsan ay operator macquul xiriira (AMA).

Table of qiimaha qaadataa foomka soo socda:

shaxano koobaya

Si kale shaandhayso xogta adigoo isticmaalaya amarka "Insert ah - Table - miiska Kooban» (Insert - Table - PivotTable in version Ingiriisi ah).

miisaskii ay sidoo kale la shaqeeyaan kala duwan oo u gaar ah pre-xog laga soo xulay iyo qiimaha gaarka ah in la danbe la falanqeeyay. Dhab ahaantii, waxa ay u muuqataa sida shuqulka ah liiska hoos-hoos ah beeraha gaar ah (tusaale ahaan, magacyada shaqaalaha shirkadda) oo kala duwan ee qiimaha la siiyo doorashada ee beer gaar ah.

Faa'iida isticmaalka shaxano koobaya waa loo baahan yahay ee hagaajin Buuggan marka la beddelo xogta cayriin naftooda.

gunaanad

Gebogebadii, waxa loo sheegayaa, in baaxadda filter ee Microsoft Excel waa mid aad u ballaadhan oo kala duwan. Waxa ku filan si aad u dalbato male iyo horumarinta aqoontooda iyo xirfadahooda.

keligeed, filter waa fududahay in la isticmaalo oo ay bartaan, fududahay in la fahmo sida loo isticmaalo filter sare ee Excel, laakiin waxaa waxaa loogu talagalay codsiyada baahan qadar yar oo ah waqti si ay u sameeyaan warbixin baarista processing dheeraad ah. Sida caadiga ah, waxa aanu ku siin waayo nooc badan oo data shaqada ay sabab u tahay arrin aadanaha ee caadiga ah. Waxaa u yimid inuu gargaarka ee tiknoolajiyadda macluumaadka processing dheeraad ah oo casri ah iyo sare ee Microsoft Excel.

macros caan weyn, soo saaro Afka VBA. Waxay kuu ogolaanaysa inaad ordi tiro badan oo ah filtarrada, fududeynta qiimaha xulashada iyo iyaga wax soo saarka si ay safafka ku haboon.

Macros si guul bedeli saacado badan oo shaqo ah oo ku saabsan dejinta kooban, wareegsan oo kale warbixinta, bedelida falanqaynta-waqtiga dheer ee xaddi weyn ee ka mid ah oo kaliya labaad click.

Isticmaalka macros xaq iyo raaxo. Cid kasta oo u kala kulantay baahida loo qabo in la isticmaalo, haddii la doonayo, had iyo jeer ka heli doontaa qalab ku filan si ay u horumariyaan aqoonta iyo natiijada ay jawaabaha su'aalaha.