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