Bils uzdeva šīs nedēļas jautājumu par liekajiem Excel datiem.
Es izveidoju ikmēneša darījumu sarakstu programmā Excel. Mēneša beigās man ir jānovērš liekie dati un jānorāda kopsumma pēc konta koda. Katrs konta kods var notikt vairākas reizes. Tad Bils aprakstīja savu pašreizējo Excel metodoloģiju, kas ir līdzīga 1. metodei, lai izveidotu unikālu kontu kodu sarakstu, plānojot izmantot PMP formulu matricu, lai iegūtu kopsummas. Viņš jautā, vai ir vienkāršāks veids, kā izveidot unikālu kontu kodu sarakstu ar katra konta kopsummu?
Tas ir ideāls svētku jautājums. Būdams Lotus lietotājs 15 gadus, es atzīstu Bila metodi kā klasisko metodi “ātrai un netīrai” datu manipulācijai jau vecajos labajos Lotus 2.1 izlaiduma laikos. Šī ir sezona mūsu svētību skaitīšanai. Padomājot par šo jautājumu, jūs saprotat, ka Microsoft ļaudis gadu gaitā mums patiešām ir piešķīruši vairākus rīkus. Ja izmantojat programmu Excel 97, šī uzdevuma veikšanai ir vismaz piecas metodes, kuras visas ir daudz vienkāršākas nekā Bila aprakstītā klasiskā metode. Šonedēļ es piedāvāšu apmācību par piecām metodēm.
Manai vienkāršotajai datu kopai konta numuri ir A slejā un summas - B slejā. Dati sākas no A2: B100. Sākumā tas netiek kārtots.
1. metode
Lai atrastu atbildi, izmantojiet radošos If paziņojumus kopā ar Īpašo vērtību ielīmēšanu.
Ņemot vērā jaunākos Excel piedāvātos rīkus, es vairs neiesaku šo metodi. Es to izmantoju daudz, pirms parādījās labākas lietas, un joprojām ir situācijas, kad tas noder. Mans alternatīvais nosaukums tam ir "The-Lotus-123-When-You-Neby-In-The-Mood-To-Use- @ DSUM". Šeit ir norādītas darbības.
- Kārtojiet datus pēc A slejas.
- C slejā izgudrojiet formulu, kas saglabās kopējo darbību pēc konta. Šūna C2 ir
=IF(A2=A1,C1+B2,B2). - Izdomājiet formulu D, kas identificēs pēdējo ierakstu konkrētam kontam. Šūna D2 ir
=IF(A2=A3,FALSE,TRUE). - Kopējiet C2: D2 uz visām rindām.
- Kopija C2: D100. Veiciet Edit - PasteSpecial - Vērtības atpakaļ uz C2: D100, lai mainītu formulas uz vērtībām.
- Kārtot pēc D slejas dilstošā secībā.
- Rindām, kuru D slejā ir PATIESA, jums ir unikāls kontu numuru saraksts A un galīgais kopējais darbības rādītājs C.
Pros: Tas ir ātri. Viss, kas jums nepieciešams, ir dedzīga IF paziņojumu rakstīšanas izjūta.
Mīnusi: ir labāki veidi.
2. metode
Izmantojiet datu filtru - papildu filtru, lai iegūtu unikālo kontu sarakstu.
Bila jautājums patiešām bija par to, kā iegūt unikālu kontu numuru sarakstu, lai viņš varētu izmantot PMP formulas, lai iegūtu kopsummas. Šī ir metode, kā iegūt unikālo kontu numuru sarakstu.
- Izcelt A1: A100
- Izvēlnē atlasiet Dati, Filtrs, Papildu filtrs
- Noklikšķiniet uz pogas “Kopēt uz citu vietu”.
- Noklikšķiniet uz izvēles rūtiņas “Tikai unikālie ieraksti”.
- Izvēlieties tukšu darblapas sadaļu, kurā vēlaties parādīt unikālo sarakstu. Ievadiet to laukā "Kopēt uz:". (Ņemiet vērā, ka šis lauks ir pelēks, līdz izvēlaties “Kopēt uz citu vietu”.
- Noklikšķiniet uz Labi. Unikālie kontu numuri parādīsies F1.
- Lai iegūtu rezultātus, ievadiet visas manipulācijas, masīvu formulas utt.
Plusi: ātrāk nekā 1. metode. Šķirošana nav nepieciešama.
Mīnusi: pēc tam nepieciešamās pielāgotās meklētājprogrammas formulas liks jums griezties.
3. metode
Izmantojiet datu konsolidāciju.
Mana dzīves kvalitāte uzlabojās, kad Excel piedāvāja Data Consolidate. Tas bija LIELS! Lai to iestatītu, nepieciešamas 30 sekundes, taču DSUM un citām metodēm tā ir nāve. Jūsu konta numuram jābūt pa kreisi no skaitliskajiem laukiem, kurus vēlaties summēt. Virs katras kolonnas jābūt virsrakstiem. Jums ir jāpiešķir diapazona nosaukums taisnstūrveida šūnu blokam, kas ietver kontu numurus gar kreiso kolonnu un virsrakstus augšpusē. Šajā gadījumā šis diapazons ir A1: B100.
- Izcelt A1: B100
- Piešķiriet diapazona nosaukumu šim apgabalam, noklikšķinot lodziņā Nosaukums (pa kreisi no formulu joslas) un ierakstot nosaukumu, piemēram, "TotalMe". (Alternatīvi izmantojiet Insert - Name).
- Ievietojiet šūnas rādītāju tukšā darblapas sadaļā.
- Pick Data - konsolidēt
- Atsauces laukā ierakstiet diapazona nosaukumu (TotalMe).
- Sadaļā Izmantojiet etiķetes pārbaudiet augšējo rindu un kreiso kolonnu.
- Noklikšķiniet uz Labi
Plusi: Šī ir mana mīļākā metode. Šķirošana nav nepieciešama. Īsceļš ir alt-D N (rangename) alt-T alt-L enter. Tas ir viegli mērogojams. Ja jūsu diapazonā ir 12 ikmēneša slejas, atbildei būs kopsummas par katru mēnesi.
Mīnusi: ja tajā pašā lapā veicat citu datu konsolidāciju, jums ir jāizdzēš vecais diapazona nosaukums laukā Visas atsauces, izmantojot pogu Dzēst. Konta numuram jābūt pa kreisi no skaitliskajiem datiem. Tas ir nedaudz lēnāks nekā pagrieziena tabulas, kas kļūst pamanāms datu kopām ar vairāk nekā 10 000 ierakstiem.
4. metode
Izmantojiet datu starpsummas.
Šī ir forša funkcija. Tā kā iegūtie dati ir dīvaini strādāt, es tos izmantoju retāk nekā Data Consolidate.
- Kārtot pēc A slejas augošā secībā.
- Datu diapazonā atlasiet jebkuru šūnu.
- Izvēlnē izvēlieties Dati - Starpsummas.
- Pēc noklusējuma Excel piedāvā saskaitīt starpību par jūsu datu pēdējo kolonnu. Šajā piemērā tas darbojas, taču, lai izvēlētos pareizos laukus, jums bieži ir jāritina saraksts "Pievienot starpsummu:".
- Noklikšķiniet uz Labi. Katrā konta numura maiņā Excel ievietos jaunu rindu ar kopsummu.
Kad esat iekļāvis starpsummas, zem vārda lodziņa redzēsit nelielu 123. Noklikšķiniet uz 2, lai redzētu tikai vienu rindiņu vienā kontā ar kopsummu. Izlasiet Kopēt Excel starpsummas, lai sniegtu paskaidrojumu par īpašām darbībām, kas nepieciešamas, lai tās kopētu jaunā vietā. Noklikšķiniet uz 3, lai redzētu visas līnijas. Plusi: Forša iezīme. Lieliski piemērots, lai pēc katras sadaļas izdrukātu atskaites ar kopsummu un lapu pārtraukumiem.
Mīnusi: vispirms jāsakārto dati. Lēni, ja ir daudz datu. Jums ir jāizmanto Goto-Special-VisbileCellsOnly, lai iegūtu kopsummas citur. Lai atgrieztos pie sākotnējiem datiem, jums jāizmanto Data-Subtotals-RemoveAll.
5. metode
Izmantojiet rakurstabulu.
Pivot tabulas ir vispusīgākās no visām. Jūsu dati nav jāšķiro. Ciparu kolonnas var būt pa kreisi vai pa labi no konta numura. Konta numurus var viegli samazināt vai samazināt visā lapā.
- Datu diapazonā atlasiet jebkuru šūnu.
- Izvēlnē izvēlieties Dati - rakurstabula.
- 1. solī pieņemiet noklusējumus
- Pārliecinieties, vai datu darbības diapazons 2. darbībā ir pareizs (tas parasti ir)
- Ja izmantojat programmu Excel 2000, 3. darbībā noklikšķiniet uz pogas Izkārtojums. Excel 95 un 97 lietotāji automātiski pāriet uz izkārtojumu kā 3. darbību.
- Izkārtojuma dialoglodziņā velciet pogu Konts no dialoglodziņa labās puses un nometiet to apgabalā Rinda.
- Velciet pogu Daudzums no dialoglodziņa labās puses un nometiet to apgabalā Dati.
- Excel 2000 lietotāji noklikšķiniet uz Labi, Excel 95/97 lietotāji noklikšķiniet uz Tālāk.
- Norādiet, vai vēlaties, lai rezultāti būtu jaunā lapā vai konkrētā esošās lapas sadaļā. Lasiet vairāk par rakurstabulām programmā Excel rakurstabulas papildu triki.
- Rakurstabulas piedāvā neticamu funkcionalitāti un padara šo uzdevumu vienkāršu. Lai kopētu rakurstabulas rezultātus, jums jādara Edit-PasteSpecial-Values, pretējā gadījumā Excel neļaus ievietot rindas utt.
Plusi: ātra, elastīga, jaudīga. Ātri, pat izmantojot daudz datu.
Mīnusi: nedaudz biedējoši.
Tagad Billam ir četras jaunas metodes lieko datu novēršanai. Lai gan šīs metodes nav bijušas pieejamas kopš laika sākuma, gan Lotus, gan Excel ir bijuši lieliski novatori, kas mums sniedz ātrākus veidus, kā paveikt šo ikdienišķo uzdevumu.
