Бил зададе въпроса тази седмица за излишните данни на Excel.
Изграждам месечен списък с транзакции в Excel. В края на месеца трябва да премахна излишните данни и да измисля общо по код на акаунта. Всеки код на акаунта може да се появи няколко пъти. След това Бил описа своята текуща методология на Excel, която е подобна на метод 1 по-долу, за да излезе с уникален списък с кодове на сметките, като планира да използва матрица от формули за CSE, за да получи общите данни. Той пита, има ли по-лесен начин да стигнете до уникален списък с кодове на сметки с общи суми за всяка сметка?
Това е идеален празничен въпрос. Като потребител на Lotus в продължение на 15 години, аз разпознавам метода на Бил като класическия метод за "бърза и мръсна" манипулация на данни от добрите стари времена на издание на Lotus 2.1. Това е сезон, за да броим нашите благословии. Когато се замислите над този въпрос, разбирате, че хората от Microsoft наистина са ни предоставили редица инструменти през годините. Ако използвате Excel 97, има поне пет метода за изпълнение на тази задача, като всички те са много по-лесни от класическия метод, описан от Бил. Тази седмица ще предложа урок за петте метода.
Моят опростен набор от данни има номера на сметки в колона А и суми в колона Б. Данните работят от A2: B100. Не е сортирано в началото.
Метод 1
За да намерите отговора, използвайте изявления на creative във връзка с Paste Special Values.
Предвид по-новите инструменти, предлагани от Excel, вече не препоръчвам този метод. Използвах това много преди да се появят по-добри неща и все още има ситуации, когато това е полезно. Алтернативното ми име за това е методът „The-Lotus-123-When-You-We-We-Not-In-The-Mood-To-Use- @ DSUM“. Ето стъпките.
- Сортирайте данните по колона А.
- Измислете формула в колона В, която ще поддържа текущо общо по сметка. Клетка С2 е
=IF(A2=A1,C1+B2,B2). - Измислете формула в D, която ще идентифицира последния запис за конкретна сметка. Клетка D2 е
=IF(A2=A3,FALSE,TRUE). - Копирайте C2: D2 до всичките ви редове.
- Копие C2: D100. Направете Edit - PasteSpecial - Стойности обратно на C2: D100, за да промените формулите на стойности.
- Сортирай по колона D низходящо.
- За редовете, които имат ИСТИНА в колона D, имате уникален списък с номера на сметки в A и крайния текущ сбор в C.
Плюсове: Бързо е. Всичко, от което се нуждаете, е остро чувство за писане на IF изявления.
Минуси: Има по-добри начини.
Метод 2
Използвайте Филтър за данни - Разширен филтър, за да получите списъка с уникални акаунти.
Въпросът на Бил наистина беше как да се получи уникален списък с номера на сметки, така че той да може да използва формули за CSE, за да получи общите суми. Това е метод за получаване на списък с уникалните номера на сметки.
- Акцент А1: A100
- От менюто изберете Данни, Филтър, Разширен филтър
- Щракнете върху бутона за избор за „Копиране на друго място“.
- Поставете отметка в квадратчето за „Само уникални записи“.
- Изберете празен раздел от работния лист, където искате да се покаже уникалният списък. Въведете това в полето "Копиране в:". (Обърнете внимание, че това поле е сиво, докато не изберете „Копиране на друго място“.
- Щракнете върху OK. Уникалните номера на сметки ще се появят във F1.
- Въведете всякакви манипулации надолу, формули на масиви и т.н., за да получите резултатите си.
Плюсове: По-бърз от метод 1. Не е необходимо сортиране.
Минуси: Формулите на CSE, необходими след това, ще ви направят главата.
Метод 3
Използвайте Data Consolidate.
Качеството ми на живот се подобри, когато Excel предложи Data Consolidate. Това беше ГОЛЯМО! Настройването му отнема 30 секунди, но е написано смърт за DSUM и други методи. Номерът на акаунта ви трябва да е вляво от числовите полета, които искате да обобщите. Трябва да имате заглавия над всяка колона. Трябва да присвоите име на диапазон на правоъгълния блок от клетки, които включват номерата на сметките в лявата колона и заглавията в горната част. В този случай този диапазон е A1: B100.
- Акцент А1: B100
- Присвойте име на диапазон на тази област, като щракнете в полето за име (вляво от лентата с формули) и напишете име като "TotalMe". (Алтернативно използвайте Insert - Name).
- Поставете показалеца на клетката в празен раздел на работния лист.
- Избор на данни - Консолидиране
- В полето за справка въведете името на диапазона (TotalMe).
- В раздела Използване на етикети В проверете както горния ред, така и лявата колона.
- Щракнете върху OK
Плюсове: Това е любимият ми метод. Не е необходимо сортиране. Преки пътища е alt-D N (име на диапазон) alt-T alt-L enter. Той е лесно мащабируем. Ако вашият диапазон включва 12 месечни колони, отговорът ще има общо за всеки месец.
Минуси: Ако направите друга консолидация на данни на същия лист, трябва да изчистите старото име на диапазона от полето Всички референции, като използвате бутона Изтриване. Номерът на сметката трябва да е вляво от вашите цифрови данни. Това е малко по-бавно от обобщените таблици, което става забележимо за набори от данни с над 10 000 записа.
Метод 4
Използвайте междинни суми за данни.
Това е страхотна функция. Тъй като получените данни са странни за работа, аз ги използвам по-рядко от Data Consolidate.
- Сортирай по колона А възходящо.
- Изберете която и да е клетка в диапазона от данни.
- Изберете Данни - Междинни суми от менюто.
- По подразбиране Excel предлага да обобщи последната колона от вашите данни. Това работи в този пример, но често трябва да превъртате през списъка „Добавяне на междинна сума към:“, за да изберете правилните полета.
- Щракнете върху OK. Excel ще вмъква нов ред при всяка промяна на номера на сметката с общ брой.
След като въведете междинните суми, ще видите малък 123 да се появи под полето за име. Кликнете върху 2, за да видите само един ред на акаунт с общите суми. Прочетете Копиране на междинни суми на Excel за обяснение на специалните стъпки, необходими за копирането им на ново място. Кликнете върху 3, за да видите всички редове. Плюсове: Готина функция. Страхотно за отпечатване на отчети с общи суми и прекъсвания на страници след всеки раздел.
Минуси: Данните първо трябва да бъдат сортирани. Бавно за много данни. Трябва да използвате Goto-Special-VisbileCellsOnly, за да получите сумите на друго място. Трябва да използвате Data-Subtotals-RemoveAll, за да се върнете към първоначалните си данни.
Метод 5
Използвайте обобщена таблица.
Осевите таблици са най-универсалните от всички. Вашите данни не трябва да бъдат сортирани. Цифровите колони могат да бъдат вляво или вдясно от номера на сметката. Можете лесно да накарате номерата на акаунтите да слизат надолу или из цялата страница.
- Изберете която и да е клетка в диапазона от данни.
- Изберете Data - PivotTable от менюто.
- Приемете настройките по подразбиране в стъпка 1
- Уверете се, че диапазонът от данни в стъпка 2 е правилен (обикновено е такъв)
- Ако използвате Excel 2000, щракнете върху бутона Layout на стъпка 3. Потребителите на Excel 95 и 97 автоматично преминават към оформление като стъпка 3.
- В диалоговия прозорец за оформление плъзнете бутона Акаунт от дясната страна на диалоговия прозорец и го пуснете в областта Ред.
- Плъзнете бутона Количество от дясната страна на диалоговия прозорец и го пуснете в областта за данни.
- Потребителите на Excel 2000 щракват OK, потребителите на Excel 95/97 щракват Напред
- Посочете дали искате резултатите в нов лист или в конкретен раздел на съществуващ лист. Прочетете повече за обобщените таблици в разширените трикове на Excel Pivot Table.
- Осевите таблици предлагат невероятна функционалност и правят тази задача бърза. За да копирате резултатите от обобщената таблица, трябва да направите Edit-PasteSpecial-Values, в противен случай Excel няма да ви позволи да вмъквате редове и т.н.
Плюсове: Бърз, гъвкав, мощен. Бързо, дори за много данни.
Минуси: Донякъде плашещо.
Сега Бил има четири нови метода за премахване на излишните данни. Въпреки че тези методи не са били достъпни от началото на времето, както Lotus, така и Excel са страхотни новатори, за да ни предложат по-бързи начини да изпълним тази светска задача.
