Rau feem coob ntawm cov neeg siv Excel, thaum lo lus "cov ntaub ntawv lim" los ntawm lawv lub taub hau, tsuas yog cov lim dej qub qub los ntawm tab Cov ntaub ntawv - Lim (Cov ntaub ntawv - Lim):
Cov lim dej zoo li no yog qhov paub zoo, tsis muaj qhov tsis ntseeg, thiab feem ntau nws yuav ua. Txawm li cas los xij, muaj cov xwm txheej thaum koj yuav tsum tau lim los ntawm ntau cov xwm txheej nyuaj hauv ob peb kab ib zaug. Cov lim ib txwm ntawm no tsis yooj yim heev thiab kuv xav tau qee yam muaj zog dua. Cov cuab yeej zoo li no tuaj yeem yog advanced lim, tshwj xeeb tshaj yog nrog me ntsis "ua tiav nrog cov ntaub ntawv" (raws li kev lig kev cai).
Hauv paus
Txhawm rau pib, ntxig ob peb kab nyob saum koj lub rooj ntaub ntawv thiab luam lub rooj header nyob rau ntawd - qhov no yuav yog qhov ntau yam nrog cov xwm txheej (highlighted hauv daj kom meej):
Yuav tsum muaj tsawg kawg yog ib kab khoob ntawm cov qe daj thiab cov lus qub.
Nws yog nyob rau hauv cov qe daj uas koj yuav tsum tau nkag mus rau cov txheej txheem (cov xwm txheej), raws li qhov kev lim dej yuav ua tiav. Piv txwv li, yog tias koj xav tau xaiv txiv tsawb nyob rau hauv Moscow "Auchan" nyob rau hauv lub quarter thib III, ces cov xwm txheej yuav zoo li no:
Txhawm rau lim, xaiv ib lub xov tooj ntawm tes hauv qhov ntau nrog cov ntaub ntawv qhov chaw, qhib lub tab Cov ntaub ntawv Thiab nias qhov Ntxiv rau (Cov ntaub ntawv - Advanced). Nyob rau hauv lub qhov rais uas qhib, ib tug ntau yam nrog cov ntaub ntawv yuav tsum tau cia li nkag mus thiab peb tsuas yog yuav tsum tau qhia kom meej ntau yam ntawm tej yam kev mob, piv txwv li A1:I2:
Thov nco ntsoov tias qhov ntau ntawm cov xwm txheej tsis tuaj yeem faib "nrog cov npoo", piv txwv li koj tsis tuaj yeem xaiv cov kab daj daj ntxiv, vim tias qhov khoob ntawm tes hauv qhov ntau ntawm cov xwm txheej tau pom los ntawm Excel raws li qhov tsis muaj qhov ntsuas, thiab tag nrho cov khoob khoob. kab raws li kev thov kom tso saib tag nrho cov ntaub ntawv indiscriminately.
hloov Luam qhov tshwm sim mus rau lwm qhov chaw yuav tso cai rau koj lim cov npe tsis nyob rau ntawm daim ntawv no (raws li nrog cov lim tsis tu ncua), tab sis txhawm rau tshem tawm cov kab xaiv mus rau lwm qhov, uas tom qab ntawd yuav tsum tau teev nyob rau hauv daim teb. Muab qhov tshwm sim hauv qhov ntau. Hauv qhov no, peb tsis siv qhov haujlwm no, peb tawm mus Lim cov npe nyob rau hauv qhov chaw thiab nias OK. Cov kab uas tau xaiv yuav tshwm rau ntawm daim ntawv:
Ntxiv Macro
"Zoo, qhov yooj yim nyob qhov twg ntawm no?" koj nug thiab koj yuav yog. Tsis tsuas yog koj yuav tsum tau nkag mus rau hauv cov kab mob daj nrog koj txhais tes, tab sis kuj qhib lub dialog box, nkag mus rau thaj tsam ntawd, nias OK. Tu siab, kuv pom zoo! Tab sis "txhua yam hloov pauv thaum lawv tuaj ©" - macro!
Ua haujlwm nrog cov lim dej siab tuaj yeem ua kom nrawm thiab yooj yim siv cov macro yooj yim uas yuav cia li khiav cov lim siab siab thaum nkag mus, piv txwv li hloov cov xov tooj daj. Right-click rau ntawm tab ntawm daim ntawv tam sim no thiab xaiv cov lus txib Cov ntawv nyeem (Code). Nyob rau hauv lub qhov rais uas qhib, luam thiab paste cov cai nram qab no:
Private Sub Worksheet_Change(ByVal Target As Range) Yog Tsis Sib Tsoo(Target, Range("A2:I5")) Tsis muaj dab tsi Tom qab ntawd yuam kev rov pib dua ActiveSheet.ShowAllData Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInRplacee, Criteria :=Range("A1").CurrentRegion End Yog End Sub Cov txheej txheem no yuav cia li khiav thaum ib qho ntawm tes ntawm daim ntawv ua haujlwm tam sim no raug hloov. Yog tias qhov chaw nyob ntawm lub xov tooj hloov pauv poob rau hauv cov xim daj (A2: I5), qhov macro no tshem tawm tag nrho cov ntxaij lim dej (yog tias muaj) thiab rov siv cov lim txuas ntxiv mus rau qhov chaw cov ntaub ntawv pib nrog A7, piv txwv li txhua yam yuav raug lim tam sim, tam sim ntawd. tom qab nkag mus rau qhov xwm txheej tom ntej:
Yog li txhua yam zoo dua, txoj cai? 🙂
Siv cov lus nug nyuaj
Tam sim no hais tias txhua yam yog lim ntawm yoov, peb tuaj yeem nkag mus tob me ntsis rau hauv cov nuances thiab disassemble cov txheej txheem ntawm cov lus nug nyuaj hauv cov lim siab. Ntxiv rau kev nkag mus rau qhov sib tw tiag tiag, koj tuaj yeem siv ntau yam cim cim (* thiab ?) thiab lej lej tsis sib xws hauv ntau yam xwm txheej los siv qhov kwv yees tshawb nrhiav. Cov xwm txheej tsis muaj teeb meem. Kom meej meej, kuv tau sau tag nrho cov kev xaiv ua tau hauv ib lub rooj:
| Kauj | tshwm sim |
| gr* los yog gr | tag nrho cov cell pib nrog GrIe Grpob ntseg, Grqab zib, Granat thiab lwm yam |
| = dos | txhua lub hlwb raws nraim thiab tsuas yog nrog lo lus Nyo hau, piv txwv li kev sib tw |
| *liv* or *liv | cov cell muaj Liv cas underline, ie ОLivuas, Livep, raws liLiv thiab lwm yam |
| = p*v | cov lus pib nrog П thiab xaus nrog В ie Пthawjв, Пetherв thiab lwm yam |
| ib* s | cov lus pib nrog А thiab ntxiv muaj СIe Аplaumсin, Аnanaс, Asai thiab lwm yam |
| =*s | cov lus xaus rau hauv С |
| =???? | tag nrho cov hlwb nrog cov ntawv ntawm 4 cim (cov ntawv lossis cov lej, suav nrog qhov chaw) |
| = m??????n | tag nrho cov hlwb nrog cov ntawv ntawm 8 cim pib nrog М thiab xaus nrog НIe Мandariн, Мntxhov siab vimн thiab lwm yam |
| =*n??a | tag nrho cov lus xaus nrog А, qhov twg yog tsab ntawv thib 4 ntawm qhov kawg НIe Nqajнikа, raws liнozа thiab lwm yam |
| > = e | tag nrho cov lus pib nrog Э, Ю or Я |
| <> *o* | tag nrho cov lus uas tsis muaj ib tsab ntawv О |
| <> *v | tag nrho cov lus tsuas yog cov uas xaus rau hauv HIV (piv txwv li, lim cov poj niam los ntawm lub npe nruab nrab) |
| = | tag nrho cov hlwb khoob |
| <> | tag nrho cov hlwb uas tsis yog khoob |
| > = 5000 | tag nrho cov hlwb nrog tus nqi ntau dua lossis sib npaug rau 5000 |
| 5 lub = 5 | tag nrho cov cell nrog tus nqi 5 |
| >=3/18/2013 | tag nrho cov hlwb nrog hnub tom qab Lub Peb Hlis 18, 2013 (nrog) |
Cov ntsiab lus yooj yim:
- Lub cim * txhais tau tias muaj pes tsawg tus cim, thiab ? - ib yam dab tsi.
- Lub logic hauv kev ua cov ntawv nyeem thiab cov lus nug lej txawv me ntsis. Yog li, piv txwv li, lub xov tooj ntawm tes nrog tus lej 5 tsis txhais tau tias nrhiav txhua tus lej pib nrog tsib, tab sis lub xov tooj ntawm tes nrog tus ntawv B yog sib npaug rau B *, piv txwv li yuav nrhiav cov ntawv pib nrog tsab ntawv B.
- Yog tias cov lus nug tsis pib nrog tus = kos npe, ces koj tuaj yeem ua rau lub hlwb tso * thaum kawg.
- Cov hnub yuav tsum tau nkag rau hauv Teb Chaws Asmeskas hom lub hli-hnub-xyoo thiab dhau ib feem (txawm tias koj muaj Excel thiab cheeb tsam chaw).
Logical connectives THIAB-OR
Cov xwm txheej uas sau nyob rau hauv cov hlwb sib txawv, tab sis nyob rau hauv tib txoj kab, raug suav hais tias yog kev sib cuam tshuam los ntawm tus neeg siv lub tswv yim И (AND):
Cov. lim txiv tsawb rau kuv nyob rau hauv peb lub hlis twg, precisely nyob rau hauv Moscow thiab nyob rau tib lub sij hawm los ntawm Auchan.
Yog tias koj xav tau txuas cov xwm txheej nrog tus neeg teb xov tooj muaj laj thawj OR (LOSSIS), ces lawv tsuas yog yuav tsum tau nkag mus rau hauv cov kab sib txawv. Piv txwv li, yog tias peb xav pom tag nrho cov kev txiav txim ntawm tus thawj tswj hwm Volina rau Moscow txiv duaj thiab tag nrho cov kev txiav txim rau dos nyob rau hauv peb lub hlis twg ntawm Samara, ces qhov no yuav tsum tau teev nyob rau hauv ib tug ntau yam ntawm tej yam kev mob raws li nram no:
Yog tias koj xav tau yuam ob lossis ntau qhov xwm txheej ntawm ib kem, koj tuaj yeem tsuas yog duplicate cov kab ntawv hauv kab hauv cov txheej txheem thiab nkag mus rau qhov thib ob, thib peb, thiab lwm yam hauv qab nws. nqe lus. Yog li, piv txwv li, koj tuaj yeem xaiv txhua qhov kev lag luam txij lub Peb Hlis mus txog Tsib Hlis:
Feem ntau, tom qab "ua tiav nrog cov ntaub ntawv", cov lim siab tshaj tawm los ua cov cuab yeej tsim nyog, hauv qee qhov chaw tsis muaj qhov phem dua li cov autofilter classic.
- Superfilter ntawm macro
- Dab tsi yog macro, qhov twg thiab yuav ua li cas ntxig macro code hauv Visual Basic
- Smart tables hauv Microsoft Excel