Horizontal nga pagsala sa kolum sa Excel

Kung dili ka usa ka bag-o nga tiggamit, nan kinahanglan nimo nga namatikdan nga ang 99% sa tanan sa Excel gidisenyo aron magtrabaho sa mga bertikal nga mga lamesa, diin ang mga parameter o mga kinaiya (mga natad) moagi sa mga kolum, ug ang kasayuran bahin sa mga butang o panghitabo nahimutang. sa mga linya. Pivot tables, subtotals, pagkopya sa mga pormula gamit ang dobleng pag-klik – ang tanan gipahaom ilabina alang niining format sa datos.

Bisan pa, wala’y mga lagda nga wala’y eksepsiyon ug sa usa ka medyo regular nga frequency gipangutana ako kung unsa ang buhaton kung ang usa ka lamesa nga adunay usa ka pinahigda nga semantiko nga oryentasyon, o usa ka lamesa diin ang mga laray ug mga kolum adunay parehas nga gibug-aton sa kahulugan, nakit-an sa trabaho:

Ug kung nahibal-an pa sa Excel kung unsaon paghan-ay ang pinahigda (uban ang command Data - Pagsunud - Mga Opsyon - Pagsunud sa mga kolum), unya ang sitwasyon sa pagsala mas grabe - walay mga built-in nga himan alang sa pagsala sa mga kolum, dili mga laray sa Excel. Mao nga, kung nag-atubang ka sa ingon nga buluhaton, kinahanglan nimo nga maghimo mga solusyon sa lainlaing mga lebel sa pagkakomplikado.

Pamaagi 1. Bag-ong FILTER function

Kung naa ka sa bag-ong bersyon sa Excel 2021 o usa ka suskrisyon sa Excel 365, mahimo nimong pahimuslan ang bag-ong gipaila nga bahin filter (FILTER), nga makasala sa tinubdan nga datos dili lamang sa mga laray, kondili sa mga kolum usab. Aron molihok, kini nga function nanginahanglan usa ka auxiliary horizontal one-dimensional array-row, diin ang matag kantidad (TRUE o FALSE) nagtino kung gipakita ba naton o, sa sukwahi, gitago ang sunod nga kolum sa lamesa.

Atong idugang ang mosunod nga linya sa ibabaw sa atong lamesa ug isulat ang kahimtang sa matag kolum niini:

• Ingnon ta nga gusto namong ipakita kanunay ang una ug kataposang mga kolum (mga ulohan ug mga total), mao nga alang kanila sa una ug kataposang mga selula sa laray atong gibutang ang bili = TINUOD.
• Alang sa nahabilin nga mga kolum, ang sulud sa katugbang nga mga selyula usa ka pormula nga nagsusi sa kondisyon nga kinahanglan namon gamit ang mga function. И (UG) or OR (O). Pananglitan, nga ang kinatibuk-an anaa sa han-ay gikan sa 300 ngadto sa 500.

Human niana, kini nagpabilin lamang sa paggamit sa function filter sa pagpili sa mga kolum sa ibabaw diin ang atong auxiliary array adunay TINUOD nga bili:

Sa susama, mahimo nimong i-filter ang mga kolum pinaagi sa gihatag nga lista. Sa kini nga kaso, ang function makatabang COUNTIF (COUNTIF), nga nagsusi sa gidaghanon sa mga panghitabo sa sunod nga ngalan sa kolum gikan sa ulohan sa lamesa sa gitugot nga listahan:

Pamaagi 2. Pivot table imbes sa naandan

Sa pagkakaron, ang Excel adunay built-in nga pinahigda nga pagsala pinaagi sa mga kolum sa pivot tables lang, mao nga kung atong mabag-o ang atong orihinal nga lamesa ngadto sa pivot table, mahimo natong gamiton kining built-in nga gamit. Aron mahimo kini, ang among gigikanan nga lamesa kinahanglan nga makatagbaw sa mga musunud nga kondisyon:

• adunay usa ka "husto" nga usa ka linya nga linya sa ulohan nga wala’y sulod ug gisagol nga mga selyula - kung dili, dili kini molihok aron magtukod usa ka pivot table;
• ayaw pagbaton og mga duplicate sa mga label sa mga laray ug mga kolum - kini "mahugno" sa summary ngadto sa usa ka lista sa talagsaon nga mga bili;
• naglangkob lamang sa mga numero sa han-ay sa mga bili (sa intersection sa mga laray ug mga kolum), tungod kay ang pivot table siguradong mag-aplay sa usa ka matang sa aggregating function ngadto kanila (sum, average, ug uban pa) ug kini dili molihok uban sa teksto

Kung matuman ang tanan nga mga kondisyon, nan aron makahimo usa ka pivot table nga sama sa among orihinal nga lamesa, kini (ang orihinal) kinahanglan nga mapalapad gikan sa crosstab ngadto sa usa ka patag (normalize). Ug ang pinakasayon ​​nga paagi sa pagbuhat niini mao ang Power Query add-in, usa ka gamhanan nga himan sa pagbag-o sa datos nga gitukod sa Excel sukad sa 2016. 

Kini mao ang:

1. Atong i-convert ang lamesa ngadto sa "smart" dynamic command Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan).
2. Nag-load sa Power Query gamit ang command Data – Gikan sa Talaan / Range (Data – Gikan sa Talaan / Range).
3. Gisala namon ang linya nga adunay mga total (ang summary adunay kaugalingon nga mga total).
4. Pag-right-click sa una nga ulohan sa kolum ug pilia Uncollapse ang ubang mga column (Unpivot Ubang Kolum). Ang tanan nga dili gipili nga mga kolum gibag-o sa duha - ang ngalan sa empleyado ug ang kantidad sa iyang timailhan.
5. Pagsala sa column gamit ang mga total nga nasulod sa column hiyas.
6. Nagtukod kami og pivot table sumala sa resulta nga flat (normalized) nga lamesa nga adunay command Panimalay — Pagsira ug Pag-load — Pagsira ug Pag-load sa… (Balay — Close & Load — Close & Load to…).

Karon mahimo nimong gamiton ang abilidad sa pagsala sa mga kolum nga anaa sa pivot tables - ang naandan nga mga checkmark sa atubangan sa mga ngalan ug mga butang. Mga Filter sa Pirma (Mga Filter sa Label) or Pagsala pinaagi sa bili (Mga Filter sa Bili):

Ug siyempre, kung usbon ang datos, kinahanglan nimo nga i-update ang among pangutana ug ang katingbanan gamit ang usa ka shortcut sa keyboard Ctrl+alt+F5 o team Data - I-refresh ang Tanan (Data — I-refresh Tanan).

Pamaagi 3. Macro sa VBA

Ang tanan nga nangaging mga pamaagi, ingon nga dali nimo makita, dili eksakto nga pagsala - wala namon gitago ang mga kolum sa orihinal nga lista, apan nagporma usa ka bag-ong lamesa nga adunay gihatag nga hugpong sa mga kolum gikan sa orihinal. Kung gikinahanglan nga i-filter (itago) ang mga kolum sa gigikanan nga datos, nan kinahanglan ang usa ka sukaranan nga lahi nga pamaagi, nga mao, usa ka macro.

Ibutang ta nga gusto namong i-filter ang mga kolum sa langaw diin ang ngalan sa manager sa header sa lamesa nagtagbaw sa maskara nga gipiho sa yellow cell A4, pananglitan, nagsugod sa letra nga "A" (nga mao, pagkuha "Anna" ug "Arthur "ingon nga resulta). 

Sama sa una nga pamaagi, una namon nga gipatuman ang usa ka auxiliary range-row, diin sa matag cell ang among kriterya susihon sa usa ka pormula ug ang mga lohikal nga kantidad nga TRUE o FALSE ipakita alang sa makita ug tinago nga mga kolum, matag usa:

Unya magdugang ta ug simple nga macro. Pag-right-click sa tab sa sheet ug pilia ang command tinubdan (Source code). Kopyaha ug idikit ang mosunod nga VBA code sa bintana nga moabli:

Private Sub Worksheet_Change(ByVal Target As Range) Kung Target.Address = "$A$4" Dayon Para sa Matag cell Sa Range("D2:O2") Kung cell = Tinuod Unya cell.EntireColumn.Hidden = False Lain nga cell.EntireColumn.Hidden = Tinuod nga Katapusan Kung Sunod nga cell Katapusan Kung Katapusan Sub  

Ang lohika niini mao ang mosunod:

• Sa kinatibuk-an, kini usa ka tigdumala sa panghitabo Buluhatong_Pagbag-o, ie kini nga macro awtomatikong modagan sa bisan unsang pagbag-o sa bisan unsang cell sa kasamtangan nga sheet.
• Ang reperensiya sa giusab nga selula kanunay anaa sa variable target.
• Una, among gisusi nga ang user nagbag-o sa eksakto nga cell nga adunay kriterya (A4) - kini gihimo sa operator if.
• Unya magsugod ang cycle Alang sa matag… sa pag-uli sa mga gray nga mga selula (D2:O2) nga adunay TINUOD / FALSE indicator values ​​alang sa matag column.
• Kung ang kantidad sa sunod nga gray nga cell TINUOD (tinuod), nan ang kolum wala gitago, kung dili, among itago kini (property Natagoan).

•  Dynamic array functions gikan sa Office 365: FILTER, SORT, ug UNIC
• Pivot table nga adunay multiline header gamit ang Power Query
• Unsa ang mga macro, unsaon paghimo ug paggamit niini