Tace a kwance a cikin Excel

Idan kun kasance ba kawai novice mai amfani ba, to, dole ne ku rigaya lura cewa 99% na duk abin da ke cikin Excel an tsara shi don aiki tare da tebur na tsaye, inda sigogi ko halayen (filaye) ke shiga cikin ginshiƙan, kuma bayanai game da abubuwa ko abubuwan suna samuwa. a cikin layi. Teburan pivot, ƙananan jimla, kwafin dabaru tare da danna sau biyu - komai an keɓance shi musamman don wannan tsarin bayanai.

Koyaya, babu ka'idoji ba tare da keɓancewa ba kuma tare da mitar na yau da kullun ana tambayar ni abin da zan yi idan tebur tare da madaidaicin ma'anar fassarar, ko tebur inda layuka da ginshiƙai suna da nauyin ma'ana ɗaya, ya zo cikin aikin:

Kuma idan har yanzu Excel ya san yadda ake warwarewa a kwance (tare da umarnin Bayanai - Tsara - Zaɓuɓɓuka - Tsara ginshiƙai), to, halin da ake ciki tare da tacewa ya fi muni - babu kawai kayan aikin da aka gina don tace ginshiƙai, ba layuka a cikin Excel ba. Don haka, idan aka fuskanci irin wannan aikin, dole ne ku fito da hanyoyin warware matsaloli daban-daban na rikitarwa.

Hanyar 1. Sabon aikin FILTER

Idan kuna kan sabon sigar Excel 2021 ko biyan kuɗi na Excel 365, zaku iya amfani da sabon fasalin da aka gabatar. tace (TACE), wanda zai iya tace bayanan tushen ba kawai ta hanyar layuka ba, har ma da ginshiƙai. Don yin aiki, wannan aikin yana buƙatar madaidaicin madaidaiciyar jeri mai girma-ɗaya, inda kowace ƙima (GASKIYA ko KARYA) ke ƙayyade ko mun nuna ko, akasin haka, ɓoye shafi na gaba a cikin tebur.

Bari mu ƙara layin da ke sama a saman teburinmu kuma mu rubuta matsayin kowane shafi a cikinsa:

• Bari mu ce koyaushe muna son nuna ginshiƙan farko da na ƙarshe (masu kai da jimla), don haka a gare su a cikin sel na farko da na ƙarshe na tsararrun mun saita ƙimar = GASKIYA.
• Don sauran ginshiƙan, abubuwan da ke cikin sel masu dacewa za su zama dabarar da ke duba yanayin da muke buƙata ta amfani da ayyuka И (DA) or OR (OR). Misali, jimlar tana cikin kewayon daga 300 zuwa 500.

Bayan haka, ya rage kawai don amfani da aikin tace don zaɓar ginshiƙai a sama waɗanda tsararrun taimakon mu ke da ƙimar GASKIYA:

Hakazalika, zaku iya tace ginshiƙai ta lissafin da aka bayar. A wannan yanayin, aikin zai taimaka COUNTIF (COUNTIF), wanda ke bincika adadin abubuwan da suka faru na sunan shafi na gaba daga taken tebur a jerin da aka yarda:

Hanyar 2. Pivot tebur maimakon wanda aka saba

A halin yanzu, Excel yana da ginanniyar tacewa a kwance ta ginshiƙai kawai a cikin allunan pivot, don haka idan muka sami nasarar canza teburin mu na asali zuwa teburin pivot, za mu iya amfani da wannan ginanniyar aikin. Don yin wannan, teburin tushen mu dole ne ya cika waɗannan sharuɗɗan:

• sami layin taken "daidai" guda ɗaya ba tare da komai ba kuma da aka haɗa sel - in ba haka ba ba zai yi aiki ba don gina tebur pivot;
• kada ku ƙunshi kwafi a cikin alamun layuka da ginshiƙai - za su "ruguje" a cikin taƙaice cikin jerin ƙima na musamman kawai;
• ya ƙunshi lambobi kawai a cikin kewayon ƙima (a madaidaicin layuka da ginshiƙai), saboda teburin pivot tabbas zai yi amfani da wasu nau'ikan aikin tarawa a gare su ( jimla, matsakaita, da sauransu) kuma wannan ba zai yi aiki tare da rubutu ba.

Idan duk waɗannan sharuɗɗan sun cika, to, don gina teburin pivot wanda yayi kama da tebur ɗinmu na asali, (na asali) zai buƙaci a faɗaɗa shi daga giciye zuwa wani lebur (al'ada). Kuma hanya mafi sauƙi don yin wannan ita ce tare da Ƙarfin Query, ƙaƙƙarfan kayan aikin canza bayanai da aka gina zuwa Excel tun 2016. 

Wadannan su ne:

1. Bari mu mai da tebur zuwa wani “smart” umarni mai ƙarfi Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur).
2. Ana lodawa cikin Tambayar Wuta tare da umarni Bayanai - Daga Tebur / Range (Bayani - Daga Tebur / Range).
3. Muna tace layin tare da jimlar (takaice zai sami nasa jimlar).
4. Danna dama akan taken shafi na farko kuma zaɓi Cire wasu ginshiƙai (Unpivot Other Columns). Duk ginshiƙan da ba a zaɓa ba an canza su zuwa biyu - sunan ma'aikaci da ƙimar alamarsa.
5. Tace ginshiƙi tare da jimlar da suka shiga ginshiƙi sifa.
6. Muna gina tebur mai pivot bisa ga tebur ɗin da aka samu (al'ada) tare da umarni Gida - Rufe kuma Loda - Rufe kuma Load a… (Gida - Rufe & Loda - Kusa & Loda zuwa…).

Yanzu zaku iya amfani da ikon tace ginshiƙan da ake samu a cikin allunan pivot - alamun bincike na yau da kullun a gaban sunaye da abubuwa. Tace Sa hannu (Tambarin Tace) or Tace da ƙima (Tace masu daraja):

Kuma ba shakka, lokacin canza bayanai, kuna buƙatar sabunta tambayarmu da taƙaitawar tare da gajeriyar hanyar madannai Ctrl+alt+F5 ko tawaga Bayanai - Wartsake Duk (Bayanai - Refresh Duk).

Hanyar 3. Macro a cikin VBA

Duk hanyoyin da suka gabata, kamar yadda zaku iya gani cikin sauƙi, ba daidai suke tacewa ba - ba mu ɓoye ginshiƙai a cikin jerin asali ba, amma ƙirƙirar sabon tebur tare da ginshiƙan da aka ba da su daga asali. Idan ana buƙatar tace (ɓoye) ginshiƙan da ke cikin bayanan tushen, to ana buƙatar wata hanya ta asali ta daban, wato, macro.

A ce muna so mu tace ginshiƙai a kan gardama inda sunan mai sarrafa a cikin teburin tebur ya gamsar da abin rufe fuska da aka ƙayyade a cikin kwayar rawaya A4, alal misali, yana farawa da harafin "A" (wato, samun "Anna" da "Arthur". " saboda). 

Kamar yadda yake a hanya ta farko, mun fara aiwatar da jeri-jeri na taimako, inda a cikin kowane tantanin halitta za a bincika ma'aunin mu ta wata dabara kuma za a nuna ma'auni masu ma'ana GASKIYA ko KARYA don ginshiƙan bayyane da ɓoye, bi da bi:

Sa'an nan kuma bari mu ƙara macro mai sauƙi. Danna-dama akan shafin shafin kuma zaɓi umarni source (Madogararsa). Kwafi da liƙa lambar VBA mai zuwa cikin taga da ke buɗewa:

Mai zaman kansa Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Sai Ga Kowane Tantanin halitta A Range("D2:O2") Idan cell = Gaskiya Sai cell.EntireColumn.Hidden = Ƙarya ta Else.EntireColumn.Hidden = Ƙarshen Gaskiya Idan Ƙarshen cell na gaba Idan Ƙarshen Sub  

Hankalinsa shine kamar haka:

• Gabaɗaya, wannan mai kula da taron ne Takardar Aiki_Chanji, watau wannan macro zai gudana ta atomatik akan kowane canji zuwa kowane tantanin halitta akan takardar yanzu.
• Maganar tantanin halitta da aka canza koyaushe zai kasance cikin mai canzawa Target.
• Da farko, muna duba cewa mai amfani ya canza daidai tantanin halitta tare da ma'auni (A4) - wannan yana yin ta hanyar mai aiki. if.
• Sannan zagayowar zata fara Ga Kowanne… don maimaita sama da sel launin toka (D2: O2) tare da ƙimar masu nuna GASKIYA / KARYA ga kowane shafi.
• Idan darajar tantanin launin toka na gaba shine GASKIYA (gaskiya), to ginshiƙi ba a ɓoye yake ba, in ba haka ba muna ɓoye shi (dukiyoyi). boye).

•  Ayyukan tsararru masu ƙarfi daga Office 365: FILTER, SORT, da UNIC
• Teburin Pivot tare da kan layi mai yawa ta amfani da Query Query
• Menene macros, yadda ake ƙirƙira da amfani da su