gototopgototop

Материалы Excel

Сейчас на сайте

Сейчас 58 гостей онлайн
Сделать, создать, установить в таблице Excel. Как сложить и удалить дубли в Excel.

Как сложить и удалить дубли в Excel.

      Рассмотрим, как сложить и удалить дубли в Excel. Как выделить дубли в Excel, читайте в статье «Как выделить повторяющиеся значения в Excel» тут. Как удалить повторяющиеся значения, смотрите в статье «Как удалить дубли в Excel».
      Итак, нам нужно перед удалением дублей, их сложить. Рассмотрим несколько способов.   
      Первый способ.
Самый простой способ – это использовать функцию «Консолидация» в Excel. У нас такая таблица.
Нам нужно сложить все числа в столбце В по каждому дублю из столбца А – отдельно все ячейки с цифрой 1, отдельно все ячейки с цифрой 2, т.д. Мы не знаем, сколько и какие у нас дубли в большой таблице.
      На новом листе делаем активной ячейку. Мы, в нашем примере, сделаем активной ячейку Е2 на этом же листе.  На закладке «Данные» в разделе «Работа с данными» нажимаем на кнопку функции «Консолидация». Выйдет диалоговое окно.Заполнили его так:
      В строке «Функция» выбрали функцию «Сумма». Можно выбрать другие действия.
      В строке «Ссылка» поставили диапазон нашей таблицы. У нас указан фиксированный размер диапазона с абсолютными ссылками и названием столбцов. Можно написать диапазон с относительными ссылками.
      Подробнее о видах ссылок читайте в статье «Относительные и абсолютные ссылки в Excel» здесь.
Поставили галочки у слов:
      «подписи верхней строки» – это название столбцов;
      «значения левого столбца» - это значит, что искать дубли в левом первом столбце.
Нажимаем «ОК». Поучилось так.
Сложить и удалить дубли в Excel.Если таблицу консолидации делаем на другом листе, то получится такая таблица.
Когда мы делали консолидацию на другом листе, то в диалоговом окне консолидации в строке «создавать связи с исходными данными» поставили галочку. Теперь, если в столбце В изменятся цифры, то и в этой таблице данные пересчитаются автоматически.  Проверяем – изменим цифру 12 в ячейке В2 основной таблицы на цифру 2. Получилось так.Как работать с таблицей консолидации, как объединить несколько таблиц в одну консолидацией, читайте в статье «Как объединить таблицы в Excel».      
      Второй способ.
Сложить все дубли с помощью формул. Таблица та же.
      Принцип такой. Создаем дополнительный столбец, в ячейках которого будем писать название дубля, который нужно сложить. Мы будем писать дубли в ячейках С2, С3, С4, С5.
А в следующем столбце напишем формулу, которая будет искать  дублирующие данные в первом столбце и складывать цифры по дублям из второго столбца.
      В ячейке D2 пишем такую формулу.  =СУММЕСЛИ(A2:A9;C2;B2:B9)
      В ячейку D3 пишем такую формулу.   =СУММЕСЛИ(A3:A9;C3;B3:B9)
По такому принципу написать формулы в других ячейках столбца D. Получилось так.
Эту же формулу можно написать, вызвав диалоговое окно функции «СУММЕСЛИ». Она расположена на закладке «Формулы» в разделе «Библиотека функций» нажимаем функцию «Математические», выбираем «СУММЕСЛИ». Заполним диалоговое окно так.В строке «Диапазон» указываем диапазон столбца А с дублями.
В строке «Критерий» указываем ячейку столбца С, в которой будем писать название дубля.
В строке «Диапазон суммирования» указываем диапазон столбца В, числа из ячеек которого нужно сложить.
В строке формул сразу видна получившаяся формула.      
      Третий способ.
Создать таблицу с фильтром. Внизу столбца В установить автосумму. Выбирать фильтром нужные дубли. Как сделать сводную таблицу, читайте в статье «Сводные таблицы Excel».
      Но мы сделаем проще и быстрее. Выделяем таблицу, нажимаем сочетание клавиш Ctrl + T (английская на любой раскладке). Получилась такая сводная таблица с фильтрами. Внизу столбца В установили автосумму.
Выберем фильтром дубли 1. Получилось так. 

      Как написать данные одновременно в нескольких ячейках, смотрите в статье "Как заполнить ячейки в Excel одновременно".