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