Ja neesat gluži iesācējs, tad noteikti jau esat pamanījis, ka programmā Excel 99% viss ir paredzēts darbam ar vertikālām tabulām, kur parametri vai atribūti (lauki) iet cauri kolonnām un atrodas informācija par objektiem vai notikumiem. rindās. Rakurstabulas, starpsummas, formulu kopēšana ar dubultklikšķi – viss ir īpaši pielāgots šim datu formātam.
Taču nav noteikumu bez izņēmumiem un diezgan regulāri man tiek jautāts, kā rīkoties, ja darbā uzradusies tabula ar horizontālu semantisko orientāciju vai tabula, kurā rindām un kolonnām ir vienāds nozīmes:
Un, ja Excel joprojām zina, kā kārtot horizontāli (ar komandu Dati — Kārtot — Opcijas — Kārtot kolonnas), tad situācija ar filtrēšanu ir sliktāka – vienkārši nav iebūvētu rīku kolonnu, nevis rindu filtrēšanai Excel. Tātad, ja jūs saskaraties ar šādu uzdevumu, jums būs jāizdomā dažādas sarežģītības pakāpes risinājumi.
1. metode. Jauna FILTRA funkcija
Ja izmantojat jauno Excel 2021 versiju vai Excel 365 abonementu, varat izmantot jaunieviestās funkcijas priekšrocības. FILTER (FILTER), kas var filtrēt avota datus ne tikai pēc rindām, bet arī pēc kolonnām. Lai šī funkcija darbotos, ir nepieciešama papildu horizontāla viendimensijas masīva rinda, kur katra vērtība (TRUE vai FALSE) nosaka, vai mēs rādām vai, gluži pretēji, paslēpsim nākamo tabulas kolonnu.
Pievienosim šādu rindiņu virs tabulas un ierakstīsim katras kolonnas statusu tajā:
- Pieņemsim, ka mēs vienmēr vēlamies parādīt pirmo un pēdējo kolonnu (galvenes un kopsummas), tāpēc tām masīva pirmajā un pēdējā šūnā mēs iestatām vērtību = TRUE.
- Pārējām kolonnām atbilstošo šūnu saturs būs formula, kas pārbauda mums nepieciešamo nosacījumu, izmantojot funkcijas И (UN) or OR (OR). Piemēram, ka kopējā summa ir diapazonā no 300 līdz 500.
Pēc tam atliek tikai izmantot funkciju FILTER lai atlasītu kolonnas, virs kurām mūsu papildu masīvam ir TRUE vērtība:
Līdzīgi varat filtrēt kolonnas pēc noteikta saraksta. Šajā gadījumā funkcija palīdzēs COUNTIF (COUNTIF), kas pārbauda nākamās kolonnas nosaukuma atkārtojumu skaitu no tabulas galvenes atļautajā sarakstā:
2. metode. Rakurstabula, nevis parastā
Pašlaik programmā Excel ir iebūvēta horizontālā filtrēšana pēc kolonnām tikai rakurstabulās, tāpēc, ja mums izdodas pārvērst savu sākotnējo tabulu par rakurstabulu, mēs varam izmantot šo iebūvēto funkcionalitāti. Lai to izdarītu, mūsu avota tabulai ir jāatbilst šādiem nosacījumiem:
- jābūt “pareizai” vienas rindiņas galvenes rindai bez tukšām un sapludinātām šūnām – pretējā gadījumā rakurstabulas izveide nedarbosies;
- nesatur dublikātus rindu un kolonnu etiķetēs – tie kopsavilkumā “sabruks” tikai unikālo vērtību sarakstā;
- satur tikai skaitļus vērtību diapazonā (rindu un kolonnu krustpunktā), jo rakurstabula tiem noteikti piemēros kaut kādu apkopošanas funkciju (summa, vidējais utt.), un tas nedarbosies ar tekstu
Ja visi šie nosacījumi ir izpildīti, tad, lai izveidotu rakurstabulu, kas izskatās kā mūsu oriģinālā tabula, tā (sākotnējā) būs jāpaplašina no krustojuma uz plakanu (normalizētu). Un vienkāršākais veids, kā to izdarīt, ir Power Query pievienojumprogramma — jaudīgs datu pārveidošanas rīks, kas ir iebūvēts programmā Excel kopš 2016. gada.
Šie ir:
- Pārveidosim tabulu par “gudru” dinamisku komandu Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu).
- Ielādē Power Query ar komandu Dati — no tabulas/diapazons (dati — no tabulas/diapazons).
- Mēs filtrējam rindu ar summām (kopsavilkumam būs savas kopsummas).
- Ar peles labo pogu noklikšķiniet uz pirmās kolonnas virsraksta un atlasiet Atskrūvēt citas kolonnas (Atcelt citas kolonnas). Visas neatlasītās kolonnas tiek pārvērstas divās – darbinieka vārds un viņa rādītāja vērtība.
- Kolonnas filtrēšana ar summām, kas tika iekļautas kolonnā īpašība.
- Mēs veidojam rakurstabulu saskaņā ar iegūto plakano (normalizēto) tabulu ar komandu Sākums — Aizvērt un ielādēt — Aizvērt un ielādēt… (Sākums — aizvērt un ielādēt — aizvērt un ielādēt...).
Tagad varat izmantot iespēju filtrēt rakurstabulās pieejamās kolonnas — parastās atzīmes nosaukumu un vienumu priekšā. Parakstu filtri (iezīmju filtri) or Filtri pēc vērtības (Vērtību filtri):
Un, protams, mainot datus, jums būs jāatjaunina mūsu vaicājums un kopsavilkums ar īsinājumtaustiņu Ctrl+cits+F5 vai komanda Dati — atsvaidzināt visu (Dati — atsvaidzināt visu).
3. metode. Makro VBA
Visas iepriekšējās metodes, kā var viegli redzēt, nav tieši filtrējošas – mēs neslēpjam kolonnas sākotnējā sarakstā, bet veidojam jaunu tabulu ar noteiktu kolonnu kopu no sākotnējās. Ja ir nepieciešams filtrēt (slēpt) kolonnas avota datos, tad ir nepieciešama principiāli atšķirīga pieeja, proti, makro.
Pieņemsim, ka mēs vēlamies uzreiz filtrēt kolonnas, kurās pārvaldnieka vārds tabulas galvenē atbilst maskai, kas norādīta dzeltenajā šūnā A4, piemēram, sākas ar burtu “A” (tas ir, iegūstiet “Anna” un “Arthur " rezultātā).
Tāpat kā pirmajā metodē, mēs vispirms ieviešam papildu diapazona rindu, kur katrā šūnā mūsu kritērijs tiks pārbaudīts ar formulu un attiecīgi redzamajām un slēptajām kolonnām tiks parādītas loģiskās vērtības TRUE vai FALSE:
Tad pievienosim vienkāršu makro. Ar peles labo pogu noklikšķiniet uz lapas cilnes un atlasiet komandu avots (Avota kods). Atvērtajā logā nokopējiet un ielīmējiet šo VBA kodu:
Privāta apakšlapa = True End If Next cell End If End Sub
Tās loģika ir šāda:
- Kopumā tas ir notikumu apstrādātājs Darba lapa_Mainīt, ti, šis makro tiks automātiski izpildīts, veicot izmaiņas jebkurā pašreizējās lapas šūnā.
- Atsauce uz mainīto šūnu vienmēr būs mainīgajā mērķis.
- Vispirms pārbaudām, vai lietotājs ir mainījis tieši šūnu ar kritēriju (A4) – to dara operators if.
- Tad sākas cikls Katram… lai atkārtotu pelēkās šūnas (D2:O2) ar katras kolonnas indikatora vērtībām TRUE/FALSE.
- Ja nākamās pelēkās šūnas vērtība ir TRUE (true), tad kolonna nav paslēpta, pretējā gadījumā mēs to slēpjam (īpašums Slēptās).
- Dinamiskā masīva funkcijas no Office 365: FILTER, SORT un UNIC
- Rakurstabula ar vairākrindu galveni, izmantojot Power Query
- Kas ir makro, kā tos izveidot un izmantot