Konsekventa Excel izveidošana, datu darbs, noņemšana

Excel ir diezgan spēcīgs rīks, kas ļauj strādāt ar lieliem datu blokiem, veicot vairākas operācijas ar tiem. Var apgalvot, ka Excel tabulas ir relāciju datu bāzes, ar kurām jūs varat strādāt, nepaliekot programmētāja prasmes. Bet, ja programma tiek izmantota profesionāli, ar pamatfunkcijām nepietiek, lai izveidotu ziņojumus par dažādām struktūrām. Šiem nolūkiem tabulas procesoram ir funkcionālāks un jaudīgāks rīks - kopsavilkuma tabulas.

Kāpēc Excel ir vajadzīgas kopsavilkuma tabulas

Oriģinālā šis termins izklausās pēc šarnīra tabulas, un frāzes tulkojums ir nedaudz miglains: "galds, ko var mainīt, savīt, parādīt dažādās projekcijās". Ko tas nozīmē? Tabulas datu interpretācija - uzdevums nav viegli. Parasti to dara, izmantojot formulas, gala rezultātu saņemšana tabulas beigās.

Ja jums ir jāizveido sarežģīti pārskati, izmantojot filtrus un mainot avota datus pareizajā secībā, izveidojiet to ar standarta Excel līdzekļiem, ja iespējams, tas ir ļoti grūti. Bet, pateicoties konsolidētajām tabulām, šādus pārskatus ir daudz vieglāk izveidot, piemēram, dažādas diagrammas, ļaujot tikai ar peles palīdzību grupēt līnijas un kolonnas, mainīt tās vietās, izmantot dažādas vadības ierīces.

Lielākā daļa Excel parasto lietotāju pat nezina par tik spēcīga rīka klātbūtni. Skaidrības labad mēs sniedzam nelielu un indikatīvu piemēru Excel konsolidēto tabulu "manekenu" izmantošanai ".

Pieņemsim, ka esat uzņēmuma vadītājs un esat atbildīgs par pārdošanas dokumentācijas uzturēšanu. Visi darījumi ir ievadīti tabulā, norādot visu informāciju (pārdevējs, pircējs, daudzums, summa, pārdošanas datums, pircēja adrese utt. D.). Līderis lūdz ziņojumu par pārdošanu pa reģionu.

Kā mums klājas? Mēs izveidojam jaunas tabulas izkārtojumu, kura vāciņā mēs norādām visu pārdoto preču klāstu un rindās - reģionu nosaukumus. Lai to izdarītu, mēs nokopējam no avota tabulas kolonnas ar precēm, izdzēšot dublikātus. Izmantojot īpašu ieliktņa režīmu, mēs šo kolonnu transponējam konsolidētās lapas cepurē. Mēs esam tādā pašā veidā ar reģioniem, bet, tā kā tie atrodas konsolidētajā tabulā vertikāli, mēs vienkārši kopējam bez transportēšanas.

Katrā šūnā mēs apkopojam ieņēmumus, izmantojot summas funkciju. Visbeidzot, pievienojiet galīgos rezultātus apakšā un nosūtiet ziņojumu vadītājam. Līderis lūdz reģionus sakārtot augšpusē un preces - uz līnijām. Jūs izveidojat jaunu ziņojumu 5 minūtēs un saņemat jaunu uzdevumu: sniedziet datus par peļņu, nevis ieņēmumiem, tas ir, ņemot vērā izmaksas. Nu, mēs nedaudz mainām formulu, un atkal rezultāts ir gatavs dažu minūšu laikā. Visbeidzot, ir lūgums sagatavot šādus pārskatus katram pārdevējam, lai novērtētu to efektivitāti. Un tā nav kopsavilkuma tabulas problēma, ja zināt, kur atrodas nepieciešamie sākotnējie dati!

Tikmēr jebkurš Excel lietotājs zina, cik grūti ir veikt šādas manipulācijas avota tabulas ietvaros.

Izveidošana un darbs ar konsolidētām tabulām Excel

Pieņemsim, ka mums ir pārdošanas avota tabula:

Apsveriet, kā izveidot konsolidētu tabulu, pamatojoties uz šiem tabulas datiem. Lai to izdarītu, atlasiet jebkuru šūnu, pēc kuras galvenajā panelī atlasām cilni "INSERT". Parādīsies divas papildu cilnes: "kopsavilkuma tabula" un pa kreisi - "Ieteicamās kopsavilkuma tabulas". Ja esat iesācējs un nesaprotat, kā jūs varat organizēt avota datus, mēs iesakām izmantot otro pogu. Šis ir meistars, kurš jums piedāvās gatavas pārskata iespējas. Jums vienkārši jāizvēlas atbilstoša opcija un jāiegūst pabeigtā konsolidētā tabula. Atliek tikai viņu labot, ja saskaņā ar dažiem kritērijiem viņa tevi neapmierina.

Otrais, galvenais veids, kā izveidot konsolidētās tabulas, ir "no nulles", par kuru mēs nospiežam pirmo pogu.

Atver jaunu logu, kurā ir jāiestata divi parametri: šūnas sākotnējais diapazons un ST atrašanās vieta. Tā kā pirms šarnīra tabulas izveidošanas mēs izvēlējāmies šūnu, diapazons tiks noteikts automātiski, otrā parametra noklusējuma vērtība ir jauna lapa. Tāpēc mēs neko nemainām un noklikšķiniet uz Labi. Tā rezultātā tiks atvērta jauna lapa ar nepilnīgu izkārtojumu.

Lai to konfigurētu, izmantojiet labo paneli ar nosaukumu "Kopsavilkuma tabulas lauks". Šeit, augšējā vienībā, ir uzskaitīts pieejamo lauku saraksts, kas atbilst avota tabulas kolonnai. Jūs varat pievienot laukus divos veidos: uzliekot pārbaudi pretī vēlamajai vērtībai, un tad tas automātiski iekļūs izkārtojumā. Bet tālu no vienmēr līdz vajadzīgajai vietai. Šādos gadījumos jums vienkārši jāvelk lauks nepieciešamajā izkārtojuma stāvoklī. Noņemot pozīciju izkārtojumā, tiek veikta līdzīgā veidā, noņemot atzīmi vai velkot uz labās paneļa.

Tajā ir četri reģioni zemāk. Apsveriet tos sīkāk:

• Vērtību laukums ir ST galvenā daļa, jo tieši šeit tiek norādīts, kā apstrādāt kolonnas un līnijas. Parasti tas ir summācija - šī apkopošanas metode tiks iestatīta pēc noklusējuma, ja vien visas diapazona šūnas pieņem skaitliskās vērtības. Vismaz vienas tukšas šūnas klātbūtnē vai ar datiem formātā, kas atšķiras no skaitliska, šūnu vērtību summas vietā tiks aprēķināts to skaits, tāpēc šis fakts ir jāņem vērā. Mūsu piemērā summēšana tiks veikta katram produktam attiecīgajā reģionā. Papildus summēšanai jūs varat iestatīt jebkuru citu aprēķinu metodi, kurai ir apmēram divi desmiti (aprēķinot vidējo vērtību, dalījumu utt.). Vienkāršākais veids, kā to izdarīt, ir tieši kopsavilkuma tabulā, noklikšķinot uz PKM jebkurai šūnai un izvēloties nepieciešamo aprēķinu metodi;
• Līniju laukums - sastāv no unikālām šūnu vērtībām, kas atrodas kreisajā kolonnā. Ja šeit ievietojat vairākus laukus, kopsavilkuma tabula vairs nebūs līdzena, bet gan daudzlīmeņu;
• Kolonnas laukumā ir arī lauka vērtības, bet jau attiecībā pret sākuma tabulas augšējo līniju;
• Filtra laukums ļauj noteikt ierobežojumus šūnu vērtību aprēķināšanas noteikumiem. Parasti šeit tiek ievietots lauks, saskaņā ar kuru jāfiltrē paraugs, kas iestatīts trīs citos apgabalos.

Pateicoties šo apgabalu klātbūtnei, jūs varat konfigurēt paraugu no gandrīz jebkuras sarežģītības sākuma galda, pavadot dažas minūtes.

Lai padarītu to skaidrāku, mēs izskaidrojam iepriekš minēto, izmantojot piemēru, kas aprakstīts iepriekšējā sadaļā.

Tātad, soli -by -step instrukcijas par to, kā izveidot konsolidētu tabulu Excel:

• No kopsavilkuma tabulas lauku saraksta mēs ievelkam lauka "ieņēmumus" vērtību apgabalā;
• Lauka "reģions" (pircēja reģions) tiek ievilkts līnijās;
• Lauka "produkts" ievieto kolonnas apgabalā.

Tikai trīs darbības - un kopsavilkuma tabula, kas piemērota nosūtīšanai varas iestādēm, ir gatava! Tas prasīja apmēram 10 sekundes, lai to apkopotu.

Kā izpildīt citas direktora vēlmes?

Lai gūtu peļņu, nevis ieņēmumu vietā, pietiek ar atbilstošo lauku vērtību apgabalā, nevis ieņēmumu vietā, atbilstošie aprēķini kopsavilkumā tiks automātiski veikti.

Otrais uzdevums tiek veikts arī dažu sekunžu laikā - lai mainītu reģionus un preces, vienkārši velciet tos no viena apgabala uz otru (mainīt vietas).

Lai iegūtu rezultātus katram pārvaldniekam, ir pietiekami, lai lauku "pārvaldnieks" ievietot filtra apgabalā, tad augšpusē parādīsies selektors, kurā jums jānorāda vēlamais uzvārds, lai saņemtu ziņojumu. Ir iespēja apvienot ziņojumu par vairākiem pārdevējiem.

Protams, mēs neesam atnesuši visgrūtāko, bet gan ļoti indikatīvu piemēru, kas ļauj saprast, kā izveidot kopsavilkuma tabulas Excel. Vērtību aprēķināšanas metode šūnās var būt sarežģītāka, jums, iespējams, būs jāizmanto aprēķinātie lauki, kā arī piemērot nosacītu formatējumu - šajā ziņā iespējas šajā sakarā ir iespaidīgas.

Par datu avotu

Mēs jau pieminējām, ka sākotnējiem datiem, ko izmanto, lai pareizi izveidotu konsolidētās tabulas programmā, tiek piemērotas noteiktas prasības. Uzskaitīsim tos:

• Galvenais ierobežojums ir saistīts ar obligātu vārdu klātbūtni virs aprēķinos iesaistītajām kolonnām. Šādi identifikatori ir nepieciešami iegūto ziņojumu veidošanai - pievienojot sākuma tabulai jaunus ierakstus (virknes), formāts nav nepieciešams, lai mainītu ST formātu, un rezultāti tiks automātiski pārrēķināti;
• Pārliecinieties, ka līniju un kolonnu šūnās, kas piedalās paraugā, tiek ieviesti skaitliskie parametri. Ja tie ir tukši vai satur teksta vērtības, šīs līnijas izkrīt no aprēķiniem, kas izkropļos aprēķinu rezultātus;
• Sekojiet līniju formātu un šūnu satura sarakstei. Ja tas tiek definēts kā datums, tad visām kolonnas vērtībām jābūt vienam un tam pašam formātam, pretējā gadījumā filtrēšana un nepareizi aprēķini būs nepareizi.

Ja jūs ievērojat šos noteikumus, tad, veidojot kopsavilkuma tabulas problēmas.

Datu atjaunināšana Excel komplekta tabulā

Lai gan mēs runājām par konsolidēto tabulu automātisko pārrēķināšanu, veicot pielāgojumus avota datiem vai pašas ST struktūrai, faktiski datora resursu saglabāšanas iemeslu dēļ, jums jāveic noteiktas darbības.

Jūs varat atjaunināt datus mūsu ekspozīcijas tabulā Excel divos veidos:

• Noklikšķinot uz pogas Pareizā pele jebkurā šūnā un konteksta izvēlnē atlasot vienumu "Update";
• Izmantojot cilni "Dati" un "Atjaunināt visu" pogu ".

Tabulu kešatmiņa ir īpaši pamatota, ja tās ir lielas, šajā gadījumā programmas ātrums ievērojami palielināsies, it īpaši, ja regulāri un ar augstu frekvenci ievadīsit jaunas līnijas.

Kolonnas vai līnijas pievienošana Excel

Parasti operācija ir triviāla, ja vēlamais parametrs ir avota tabulā. Jums vienkārši jāvelk pareizais lauks pareizajā vietā. Ja sākuma tabulā nav virknes vai kolonnas, pievienojiet to, ja nepieciešams, ievadiet vērtības. Pēc tam noklikšķiniet uz cilnes "Analīze" un atveriet mūsu mainīto tabulu. Atliek tikai atjaunināt datus (skat. Iepriekšējā sadaļa), kā rezultātā lauku saraksts mainīsies ST labajā panelī - tam tiks pievienots jauns parametrs.

Bieži vien kopsavilkuma tabulai nav gluži ērta suga vizualizācijai. Piemēram, kad līnijās ir vairāki lauki. Tad viens no tiem tiek parādīts pilnībā, un otra vērtības ir jānorāda tabulas augšējā daļā. Ja ir maz šādu vērtību (teiksim, mūsu pārdošanas reģionu piemērā tikai 6), tad ir jēga attiecīgajā apgabalā pievienot šarnīra tabulas kolonnu "reģionus", pārsūtot to no līnijas.

Ir viegli sasniegt arī kolonnu sekošanas procedūras maiņu, vienkārši pārsūtot kolonnas virzienu uz vēlamo pozīciju ar peli.

Kopsavilkuma tabulas veidošanās no vairākiem failiem (tabulām) Excel

Tas ir sarežģītāks, bet diezgan pilnīgs uzdevums. Izmantojiet galveno ST, kas jāpievieno ātrās piekļuves panelim.

Tātad, kreisajā pusē, netālu no vienuma “File”, noklikšķiniet uz novirzītās bultiņas, atlasiet vienumu “Citas komandas”. Vidējā panelī tiks atvērts jauns logs, mēs noklikšķinām uz opcijām "Visas komandas". Mēs atrodam sarakstā, kurā tiek atvērts “konsolidēto tabulu meistars” sarakstā, atlasiet to un noklikšķiniet uz pogas “Pievienot”. Augšējā panelī parādīsies atbilstošā ikona.

Mēs palaižam meistaru. Pieņemsim, ka mums jāapvieno dati no divām lapām, maijā un jūnijā. Galvenais nosacījums ir tāds, ka viņiem jāsakrīt pēc struktūras. Ja tā, tad galvenā loga pirmajā logā mēs izvēlamies opciju “vielu tabula” un nākamais parametrs - “Vairāki konsolidācijas diapazoni”.

Otrajā posmā mēs meistaram norādām, ka mums ir nepieciešami vairāki lauki. Trešajā - atlasiet šūnu diapazonu pirmās lapas summēšanai, noklikšķiniet uz "Pievienot" un rīkojieties tāpat otrajā lapā Excel. Tagad jums ir jāpiešķir vārdi diviem laukiem. Mēs to darām vispirms pirmajam laukam (noklikšķiniet uz selektora "1", kolonnā "1 lauks", mēs ievadām "May"), pēc tam pēc analoģijas otro. Šajā gadījumā būs nepieciešams norādīt diapazonu sarakstā atbilstošo līniju, pirmo vai otro.

Pēc noklikšķināšanas uz pogas “Gatavs” tiek atvērts jauns logs, kurā mēs norādām uz “jauno lapu” un vēlreiz nospiediet pogu “Ready”.

Uzmanība. Daudzdimensionālu tabulu saņemšana ir neērta, jo ir grūti orientēties ar lielu skaitu lauku, un jāveic nepieciešamie aprēķini, katru reizi norādot nepieciešamās vērtības no tabulas augšējā laukuma.

Kā noņemt mākslu

Vienkāršākais gadījums - kad jūs izveidojāt konsolidētu tabulu, nosūtīja rezultātus šefpavāram, un jums tas vairs nav vajadzīgs. Ja esat pārliecināts par to, vienkārši atlasiet tabulu un noklikšķiniet uz taustiņa Dzēst. Vienkāršs un efektīvs.

Bet pēkšņi nākotnē var būt nepieciešama galda struktūra? Excel ir iespēja izdzēst tikai rezultātus vai datus no šūnām. Apsveriet, kā tas tiek darīts.

Lai izdzēstu aprēķina rezultātus, mēs veicam šādas darbības:

• Mēs atzīmējam jebkuru ST šūnu;
• Augšējā panelī atlasiet cilni "Analīze";
• Pieejamo darbību sarakstā atlasiet "Clear";
• Noklikšķiniet uz punkta "notīrīt visu".

Bet ko darīt, ja vēlaties saglabāt rezultātus, bet jums pašam nav nepieciešami dati, tas ir, jūs vēlaties atbrīvot tabulu? Šī situācija bieži rodas, ja vadībai ir nepieciešami tikai rezultāti. Darbību algoritms:

• Atkal atlasiet jebkuru šūnu, noklikšķiniet uz cilnes "Analīze";
• Atlasiet izvēlnes vienumu “Darbība”, noklikšķiniet uz “Izvēlieties”, atzīmējiet visu konsolidēto tabulu ar peli;
• Noklikšķiniet uz PCM izvēlētajā apgabalā;
• Konteksta izvēlnē atlasiet vienumu "Copy";
• Mēs ejam uz cilni "Sākums", vēlreiz noklikšķiniet uz PKM un atlasiet "Ievietot";
• Izvēlieties cilni "Ievietot vērtību", tajā mēs atzīmējam parametru "Ievietot kā vērtību".

Tā rezultātā kopsavilkuma tabula tiks izdzēsta, saglabājot rezultātus.

Padoms. Jūs varat paātrināt procedūru, izmantojot galveno kombināciju. Lai atlasītu tabulu, izmantojiet Ctrl + A, lai kopēšanai - Ctrl + C. Pēc tam nospiediet Alt + E, Alt + S, Alt + V un aizpildiet procedūru, nospiežot Enter.

Lai noņemtu konsolidētās tabulas Excel 2007/2010, jums jāizmanto vēl viens algoritms:

• atlasiet ST, kas paredzēta noņemšanai;
• Noklikšķiniet uz cilnes "Parametri";
• "Darbību grupā" atlasiet "tīru" vienumu un pēc tam - "Notīriet visu".

Ja jūsu priekšniekam patīk datu vizualizācija, ir acīmredzams, ka jums būs jāizmanto konsolidētas diagrammas. Tā kā viņi galdā aizņem daudz vietas, pēc lietošanas tie parasti tiek noņemti.

Programmas vecajās versijās jums jāizvēlas diagramma, noklikšķiniet uz cilnes "Analyse", atlasiet datu grupu un noklikšķiniet uz secīgi "tīrs" un "notīriet visu".

Turklāt, ja diagramma ir saistīta ar kopsavilkuma tabulu, pēc tās dzēšanas jūs zaudēsit visus tabulas iestatījumus, tās laukus un formatēšanu.

Versijām, kas vecākas par Excel 2010, jums jāizvēlas diagramma, cilnē "Analysis" atlasiet "darbības" un noklikšķiniet uz "Clean" un "Notīriet visu". Rezultāts būs līdzīgs.

Mēs ceram, ka mūsu nodarbības uz konsolidētajām tabulām ļaus jums atklāt šo pietiekami spēcīgo funkcionalitāti. Ja jums joprojām ir jautājumi, uzdodiet tos komentāros, mēs centīsimies uz tiem atbildēt.