Sivana tsanganana mitsivalana amin'ny Excel

Raha toa ka tsy mpampiasa vaovao ianao, dia tsy maintsy efa nahatsikaritra ianao fa ny 99% amin'ny zava-drehetra ao amin'ny Excel dia natao hiasa amin'ny latabatra mitsangana, izay misy ny masontsivana na ny toetra (saha) mandeha amin'ny tsanganana, ary misy fampahalalana momba ny zavatra na zava-mitranga. amin'ny andalana. Tabilao pivot, subtotals, fakan-tahaka amin'ny tsindry indroa - ny zava-drehetra dia namboarina manokana ho an'ity format data ity.

Na izany aza, tsy misy fitsipika tsy misy maningana ary amin'ny matetika matetika dia anontaniana aho hoe inona no tokony hatao raha misy latabatra misy orientation semantika marindrano, na latabatra misy andalana sy tsanganana mitovy lanja amin'ny dikany:

Ary raha mbola hain'i Excel ny mandamina marindrano (miaraka amin'ny baiko Data - Sort - Options - Soritana tsanganana), dia ratsy kokoa ny toe-javatra amin'ny sivana - tsy misy fitaovana namboarina ho an'ny sivana tsanganana, fa tsy andalana ao amin'ny Excel. Noho izany, raha miatrika asa toy izany ianao, dia tsy maintsy mitady vahaolana amin'ny fahasarotana samihafa.

Fomba 1. FILTER vaovao

Raha ao amin'ny kinova vaovao an'ny Excel 2021 na famandrihana Excel 365 ianao, dia azonao atao ny manararaotra ilay endri-javatra vaovao. FILTER (FILTER), izay afaka manivana ny angona loharano tsy amin'ny andalana ihany, fa amin'ny tsanganana ihany koa. Mba hiasa, ity asa ity dia mitaky laharan-damba tokana marindrano fanampiny, izay misy ny sanda tsirairay (TRUE na FALSE) no mamaritra raha asehontsika na, mifanohitra amin'izany, manafina ny tsanganana manaraka ao amin'ny tabilao.

Andeha ampio eo ambonin'ny latabatra ity andalana manaraka ity ary soraty ny satan'ny tsanganana tsirairay ao anatiny:

• Andeha atao hoe te-hampiseho foana ny tsanganana voalohany sy farany (lohateny sy tontaliny) isika, ka ho azy ireo ao amin'ny sela voalohany sy farany amin'ny array dia mametraka ny sanda = TRUE.
• Ho an'ireo tsanganana sisa, ny ao anatin'ny sela mifanandrify dia ho raikipohy izay manamarina ny fepetra ilaintsika amin'ny fampiasana fiasa. И (ARY) or OR (OR). Ohatra, ny fitambarany dia eo anelanelan'ny 300 ka hatramin'ny 500.

Aorian'izay dia mijanona fotsiny ny fampiasana ny fiasa FILTER hisafidianana tsanganana eo amboniny izay manana sanda TRUE ny laharan-tsaintsika fanampiny:

Toy izany koa, azonao atao ny manivana tsanganana amin'ny lisitra nomena. Amin'ity tranga ity, ny asa dia hanampy COUNTIF (COUNTIF), izay manamarina ny isan'ny fisehoan'ny anaran'ny tsanganana manaraka avy amin'ny lohatenin'ny latabatra ao amin'ny lisitra navela:

Fomba 2. Pivot latabatra fa tsy ny mahazatra

Amin'izao fotoana izao, Excel dia manana sivana marindrano amin'ny alàlan'ny tsanganana ao amin'ny tabilao pivot ihany, ka raha vitantsika ny mamadika ny latabatra tany am-boalohany ho latabatra pivot, dia afaka mampiasa an'io fiasa namboarina io isika. Mba hanaovana izany, ny latabatra loharano dia tsy maintsy mahafeno ireto fepetra manaraka ireto:

• manana tsipika lohateny tokana "marina" tsy misy sela foana sy mitambatra - raha tsy izany dia tsy hahomby ny fananganana latabatra pivot;
• aza misy dika mitovy amin'ny etikety misy andalana sy tsanganana - "hirodana" ao amin'ny famintinana ho lisitry ny soatoavina tokana ihany;
• tsy misy afa-tsy isa ao amin'ny sanda isan-karazany (eo amin'ny fihaonan'ny andalana sy ny tsanganana), satria ny latabatra pivot dia azo antoka fa hampihatra karazana asa fanangonam-bokatra amin'izy ireo (tombany, salan'isa, sns.) ary tsy mety amin'ny lahatsoratra izany.

Raha feno ireo fepetra rehetra ireo, dia mila mivelatra avy amin'ny crosstab mankany amin'ny fisaka (normalized) mba hananganana latabatra pivot mitovy amin'ny latabatra tany am-boalohany. Ary ny fomba tsotra indrindra hanaovana izany dia miaraka amin'ny Power Query add-in, fitaovana fanovàna data mahery vaika natsangana tao amin'ny Excel nanomboka tamin'ny taona 2016. 

Ireo dia:

1. Andao hamadika ny latabatra ho baiko mavitrika “smart”. Trano - Atao ho latabatra (Horonan-trano - endrika toy ny latabatra).
2. Mampiditra ao amin'ny Power Query miaraka amin'ny baiko Data – Avy amin'ny tabilao / salan'isa (Data – Avy amin'ny tabilao / salan'isa).
3. Manivana ny tsipika miaraka amin'ny totalina izahay (ny famintinana dia hanana ny totaliny manokana).
4. Tsindrio havanana amin'ny lohatenin'ny tsanganana voalohany ary mifidiana Esory ny tsanganana hafa (Esory ny tsanganana hafa). Ny tsanganana rehetra tsy voafantina dia avadika ho roa - ny anaran'ny mpiasa sy ny sandan'ny famantarana azy.
5. Sivana ny tsanganana miaraka amin'ny totalin'ny tsanganana toetra.
6. Manangana latabatra pivot izahay araka ny latabatra fisaka (normalised) miaraka amin'ny baiko Home — Akatona sy Ampidiro — Akatona ary Ampidiro ao… (Ao an-trano — Akatona & Ampidiro — Akatona & Ampidiro amin’ny…).

Azonao atao izao ny mampiasa ny fahafahana manivana ireo tsanganana misy amin'ny tabilao pivot - ireo mari-pamantarana mahazatra eo anoloan'ny anarana sy ny entana. Signature Filters (Sivana marika) or Sivana araka ny sandany (Sivana sanda):

Ary mazava ho azy, rehefa manova ny angona ianao dia mila manavao ny fangatahanay sy ny famintinana amin'ny alàlan'ny hitsin-dàlana fitendry Ctrl+Alt+F5 na ekipa Data - Havaozy daholo (Angona - Havaozy daholo).

Fomba 3. Macro amin'ny VBA

Ny fomba rehetra teo aloha, araka ny hitanao mora foana, dia tsy sivana tsara - tsy manafina ny tsanganana ao amin'ny lisitra tany am-boalohany izahay, fa mamorona latabatra vaovao misy tsanganana nomena avy amin'ny voalohany. Raha ilaina ny manivana (manafina) ny tsanganana ao amin'ny angon-drakitra loharano, dia ilaina ny fomba fiasa hafa, izany hoe ny macro.

Aoka hatao hoe te hanivana tsanganana amin'ny lalitra izay ny anaran'ny mpitantana eo amin'ny lohatenin'ny latabatra mahafa-po ny saron-tava voatondro ao amin'ny sela mavo A4, ohatra, manomboka amin'ny litera "A" (izany hoe, mahazo "Anna" sy "Arthur". " vokatr'izany). 

Toy ny tamin'ny fomba voalohany, dia apetraka aloha ny laharan-tariby fanampiny, izay ao amin'ny sela tsirairay dia hojerena amin'ny alàlan'ny formula ny mari-pamantarana ary ny soatoavina lojika TRUE na FALSE dia haseho ho an'ny tsanganana hita maso sy miafina:

Dia ndao asiana macro tsotra. Tsindrio havanana amin'ny tabilao takelaka ary mifidiana baiko Source (Kaody loharano). Adikao ary apetaho eo amin'ny varavarankely misokatra ity kaody VBA manaraka ity:

Privé Sub Worksheet_Change(ByVal Target as Range) Raha Target.Address = "$A$4" Dia ho an'ny sela tsirairay ao anatin'ny Range("D2:O2") Raha sela = Marina Dia cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End raha manaraka ny cellule farany raha End Sub  

Toy izao ny lojikany:

• Amin'ny ankapobeny, ity dia mpitantana hetsika Worksheet_Change, izany hoe ity macro ity dia mandeha ho azy amin'ny fanovana rehetra amin'ny sela rehetra amin'ny takelaka misy ankehitriny.
• Ny fanondroana ny sela niova dia ho ao amin'ny fari-piainana foana Target.
• Voalohany, jereo fa ny mpampiasa dia nanova tanteraka ny sela miaraka amin'ny fepetra (A4) - izany no ataon'ny mpandraharaha. if.
• Dia manomboka ny tsingerina Isaky ny… hamerenana amin'ny sela volondavenona (D2:O2) miaraka amin'ny sanda famantarana TRUE / FALSE isaky ny tsanganana.
• Raha TRUE (marina) ny sandan'ny sela volondavenona manaraka, dia tsy miafina ny tsanganana, raha tsy izany dia afeninay (fananana) Zavatra tsy).

•  Miasa amin'ny Office 365: FILTER, SORT, ary UNIC
• Tabilao pivot miaraka amin'ny lohamilina maromaro mampiasa Power Query
• Inona ny macros, ny fomba hamoronana sy fampiasana azy ireo