contents
Si ou pa byen yon itilizatè inisyasyon, Lè sa a, ou dwe deja remake ke 99% nan tout bagay nan Excel fèt pou travay ak tab vètikal, kote paramèt oswa atribi (jaden) ale nan kolòn yo, ak enfòmasyon sou objè oswa evènman yo sitiye. nan liy yo. Tablo pivote, subtotal, kopye fòmil ak yon doub klike sou - tout bagay se pwepare espesyalman pou fòma done sa a.
Sepandan, pa gen okenn règ san eksepsyon epi ak yon frekans jistis regilye yo mande m kisa pou m fè si yon tab ki gen yon oryantasyon semantik orizontal, oswa yon tab kote ranje ak kolòn gen menm pwa nan siyifikasyon, te jwenn nan travay la:
Men, si Excel toujou konnen ki jan yo sòt orizontal (ak lòd la Done – Triye – Opsyon – Triye kolòn yo), Lè sa a, sitiyasyon an ak filtraj se pi mal - pa gen tou senpleman pa gen zouti entegre pou filtraj kolòn, pa ranje nan Excel. Se konsa, si w ap fè fas ak yon travay konsa, w ap gen pou vini ak solisyon kontournman nan divès degre konpleksite.
Metòd 1. Nouvo fonksyon FILTER
Si w sou nouvo vèsyon Excel 2021 oswa yon abònman Excel 365, ou ka pwofite karakteristik ki fèk prezante a. FILTRE (FILTRE), ki ka filtre done sous yo pa sèlman pa ranje, men tou pa kolòn. Pou travay, fonksyon sa a mande pou yon oksilyè orizontal yon sèl dimansyon etalaj-ranje, kote chak valè (VRÈ oswa FO) detèmine si nou montre oswa, Okontrè, kache pwochen kolòn nan tablo a.
Ann ajoute liy sa a anlè tablo nou an epi ekri estati chak kolòn ladan l:
- Ann di nou toujou vle montre premye ak dènye kolòn yo (tèt ak total), kidonk pou yo nan premye ak dènye selil yo nan etalaj la nou mete valè a = VRE.
- Pou kolòn ki rete yo, sa ki nan selil korespondan yo pral yon fòmil ki tcheke kondisyon nou bezwen lè l sèvi avèk fonksyon. И (AK) or OR (OSWA). Pou egzanp, ke total la se nan seri a soti nan 300 a 500.
Apre sa, li rete sèlman yo sèvi ak fonksyon an FILTRE pou chwazi kolòn ki anwo yo ki etalaj oksilyè nou an gen yon valè VRE:
Menm jan an tou, ou ka filtre kolòn pa yon lis bay yo. Nan ka sa a, fonksyon an ap ede COUNTIF (COUNTIF), ki tcheke kantite ensidan non kolòn kap vini an nan tèt tab la nan lis la pèmèt:
Metòd 2. Pivote tab olye de yon sèl nòmal la
Kounye a, Excel gen filtè orizontal entegre pa kolòn sèlman nan tab pivot, kidonk si nou jere konvèti tab orijinal nou an nan yon tab pivot, nou ka itilize fonksyonalite entegre sa a. Pou fè sa, tab sous nou an dwe satisfè kondisyon sa yo:
- gen yon "kòrèk" liy header yon sèl-liy san selil vid ak fizyone - otreman li pa pral travay yo bati yon tab pivot;
- pa genyen kopi nan etikèt ranje ak kolòn yo - yo pral "efondre" nan rezime a nan yon lis sèlman valè inik;
- gen sèlman nimewo nan seri valè yo (nan entèseksyon ranje ak kolòn), paske tablo pivot la pral definitivman aplike kèk kalite fonksyon agrégasyon yo (som, mwayèn, elatriye) epi sa a pa pral travay ak tèks la.
Si tout kondisyon sa yo satisfè, Lè sa a, yo nan lòd yo bati yon tab pivote ki sanble ak tab orijinal nou an, li (yon orijinal la) ap bezwen yo dwe elaji soti nan crosstab la nan yon sèl plat (nòmalize). Ak fason ki pi fasil pou fè sa se ak Power Query ajoute nan, yon zouti pwisan transfòmasyon done ki te bati nan Excel depi 2016.
Sa yo se:
- Ann konvèti tab la nan yon lòd dinamik "entelijan". Kay - Fòma kòm yon tab (Kay — Fòma kòm Tablo).
- Chaje nan Power Query ak lòd la Done - Soti nan Tablo / Range (Done - Soti nan Tablo / Range).
- Nou filtre liy lan ak total yo (rezime a pral gen total pwòp li yo).
- Dwa-klike sou premye kolòn tit la epi chwazi Defonse lòt kolòn yo (Unpivot Lòt Kolòn). Tout kolòn ki pa chwazi yo konvèti an de - non anplwaye a ak valè endikatè li a.
- Filtre kolòn nan ak total ki te antre nan kolòn nan atribi.
- Nou bati yon tab pivote dapre tab la plat (nòmalize) ki kapab lakòz ak lòd la Kay — Fèmen epi chaje — Fèmen epi chaje nan… (Kay — Fèmen & Chaje — Fèmen & Chaje pou…).
Koulye a, ou ka sèvi ak kapasite pou filtre kolòn ki disponib nan tablo pivot - mak nòmal yo devan non yo ak atik yo. Filtè Siyati (Filtè etikèt) or Filtre pa valè (Filtè Valè):
Ak nan kou, lè w ap chanje done yo, w ap bezwen mete ajou rechèch nou an ak rezime a ak yon chemen kout klavye. Ctrl+Alt+F5 oswa ekip Done - rafrechi tout (Done — rafrechi tout).
Metòd 3. Macro nan VBA
Tout metòd anvan yo, jan ou ka fasilman wè, yo pa egzakteman filtraj - nou pa kache kolòn yo nan lis orijinal la, men nou fòme yon nouvo tab ak yon seri kolòn bay soti nan orijinal la. Si li oblije filtre (kache) kolòn yo nan done sous yo, Lè sa a, yon apwòch fondamantalman diferan nesesè, sètadi, yon macro.
Sipoze nou vle filtre kolòn sou vole kote non manadjè a nan tèt tablo a satisfè mask ki espesifye nan selil jòn A4, pou egzanp, kòmanse ak lèt "A" (ki se, jwenn "Anna" ak "Arthur". ” kòm yon rezilta).
Kòm nan premye metòd la, nou premye aplike yon ranje oksilyè-ranje, kote nan chak selil kritè nou an pral tcheke pa yon fòmil ak valè lojik yo VRE oswa FAL ap parèt pou kolòn vizib ak kache, respektivman:
Lè sa a, kite a ajoute yon makro senp. Dwa-klike sou tab la fèy epi chwazi lòd sous (Kòd sous). Kopi epi kole kòd VBA sa a nan fenèt ki ouvè a:
Private Sub Worksheet_Change(ByVal Target As Range) Si Target.Address = "$A$4" Lè sa a, Pou chak selil ki nan Range ("D2:O2") Si selil = Vrè Lè sa a, cell.EntireColumn.Hidden = Fo Else cell.EntireColumn.Hidden = Vrè Fen Si Pwochen selil Fen Si Fen Sub Lojik li se jan sa a:
- An jeneral, sa a se yon moun kap okipe evènman an Worksheet_Change, sa vle di makro sa a pral otomatikman kouri sou nenpòt chanjman nan nenpòt selil sou fèy aktyèl la.
- Referans nan selil ki chanje a ap toujou nan varyab la sib.
- Premyèman, nou tcheke ke itilizatè a chanje egzakteman selil la ak kritè a (A4) - sa a se fè pa operatè a. if.
- Lè sa a, sik la kòmanse Pou chak… pou repete sou selil gri (D2: O2) ak valè endikatè VRÈ / FO pou chak kolòn.
- Si valè pwochen selil gri a se VRE (vre), Lè sa a, kolòn nan pa kache, otreman nou kache li (pwopriyete Kache).
- Fonksyon etalaj dinamik ki soti nan Office 365: FILTER, SORT, ak UNIC
- Tablo pivote ak header multiliy lè l sèvi avèk Power Query
- Ki sa ki makro, ki jan yo kreye epi sèvi ak yo