0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро объединить строки на основе одного значения столбца, а затем выполнить некоторые вычисления в Excel?

Как быстро объединить строки на основе одного значения столбца, а затем выполнить некоторые вычисления в Excel?

x

x

x

Как быстро объединить строки на основе одного значения столбца, а затем выполнить некоторые вычисления в Excel?

Рассмотрим набор данных с дубликатами в одном столбце. Теперь вы хотите объединить строки, используя столбец A (в котором есть дубликаты), а затем выполнить некоторые вычисления для другого столбца на основе объединенных строк, как показано на скриншоте ниже:

В Excel нет быстрого метода объединения строк на основе одного значения столбца, но здесь я представляю
уловки, которые могут помочь вам объединить повторяющиеся строки, а затем суммировать или выполнять другие вычисления в другом столбце.

Исходные данныеСлияние и сумма
doc объединить столбцы на основе одного столбца 1doc объединить столбцы на основе одного столбца 2

Объедините строки на основе одного столбца, а затем выполните разные операции с другими столбцами с
Расширенные ряды комбинирования хорошая идея3

стрелка синий правый пузырьОбъедините строки на основе одного столбца, затем суммируйте значения с помощью VBA

Существует код VBA, который позволяет легко объединить повторяющиеся строки и затем суммировать значение второго столбца.

Просмотр и редактирование нескольких книг Excel / документов Word с вкладками в Firefox, Chrome, Internet Explore 10!

Вы, вероятно, знакомы с просмотром нескольких веб-страниц в Firefox/Chrome/IE и легким переключением между ними путем нажатия на соответствующие вкладки. То же самое можно сделать с вкладкой Office, которая позволяет просматривать несколько книг Excel или документов Word в одном окне Excel или Word и легко переключаться между ними, щелкая по вкладкам. Нажмите, чтобы получить бесплатную 30-дневную пробную версию Office Tab!

1. нажмите F11 + Alt, чтобы активировать окно Microsoft Visual Basic for Applications, затем нажмите Insert > Modules, затем скопируйте и вставьте следующий код VBA в новое окно Modules.

Читайте так же:
Как в Microsoft Excel отобразить или скрыть нулевые значения в ячейках?

V BA: объединение строк на основе значений одного столбца

2. Нажмите F5 или кнопку Run для запуска VBA, появится диалоговое окно для выбора диапазона данных для работы, см. скриншот:

doc объединить столбцы на основе одного столбца 3

Выбрав рабочую область, нажмите OK. Теперь данные в первом столбце объединены, а значения во втором столбце объединены.

doc объединить столбцы на основе одного столбца 4

Этот код VBA работает при объединении данных на основе первого столбца и суммарного значения во втором столбце.

стрелка синий правый пузырьОбъединяйте строки на основе одного столбца, а затем суммируйте значения с помощью Advanced Combine Rows

Даже если вы не знакомы с кодом VBA, вы можете использовать функцию Kutools for Excel «Расширенное объединение строк» для объединения повторяющихся строк и последующего обобщения значений.

После бесплатной установки Kutools for Excel вам необходимо:

1. Нажмите Cthulz > Слияние и разделение > Расширенные комбинированные строки. См. снимок экрана:

doc расширенный комбайн, ряд 1

2. Затем следует выбрать столбец, по которому будут объединены другие столбцы, затем щелкнуть Primary Key, щелкнуть другой столбец, а затем щелкнуть Calculate.

Совет: Если выбранное поле имеет заголовки, установите флажок Мои данные имеют заголовки и флажок Использовать форматированные значения, чтобы сохранить форматирование после объединения.

doc объединить столбцы на основе одного столбца 6

Затем нажмите OK. Теперь данные были объединены на основе главной колонки, а остальные колонки были обобщены. См. скриншот:

doc объединить столбцы на основе одного столбца 7

стрелка синий правый пузырьОбъединяйте строки на основе одного столбца, а затем выполняйте различные операции с другими столбцами с помощью Advanced Combine Rows.

Расширенное объединение строк является мощным, поскольку оно не только может объединять повторяющиеся строки в одном столбце и затем суммировать другой столбец, но также может объединять повторяющиеся строки в одном столбце и затем выполнять различные операции над другими столбцами.

Предположим, у меня есть диапазон данных, как показано на скриншоте ниже, и я хочу объединить значения в столбце B и значения суммы в столбце C на основе столбца A.
doc объединить столбцы на основе одного столбца 8

Читайте так же:
Как в Excel вести итог в одной или отдельной ячейке?

После бесплатной установки Kutools for Excel выполните следующие действия.

1. Выберите строку данных и нажмите Cthulz > Объединить и разделить > Расширенная комбинированная строка. См. скриншот:

doc расширенный комбайн, ряд 1

2. В столбце Объединить строки на основе диалога сделайте следующее:

(1) Щелкните столбец, на основе которого вы хотите объединить, затем щелкните Master Key ;

doc объединить столбцы на основе одного столбца 10

Во-вторых, нажмите кнопку Объединить на колонке, в которой данные должны быть объединены, выберите из списка разделитель, который вы хотите использовать;

doc объединить столбцы на основе одного столбца 11

(3) Нажмите Calculate и выберите Sum для столбца, значения которого вы хотите просуммировать.

doc объединить столбцы на основе одного столбца 12

Нажмите Ок. Теперь вы можете увидеть результат, как показано ниже:

doc объединить столбцы на основе одного столбца 13

С помощью расширенной функции объединения строк можно также объединять строки на основе одного столбца, а затем подсчитывать, усреднять и сохранять данные первого или последнего столбца.

Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования

Ранее я описывал, как использовать пользовательскую функцию для вычисления суммы значений в цветных ячейках. Эта функция не работает, если ячейки отформатированы с помощью условного форматирования. Я обещал «доработать» функцию. За два года, прошедшие с момента публикации этого сообщения, я не смог придумать никакого удобоваримого кода ни самостоятельно, ни с помощью информации из Интернета. (Дополнение от 29 марта 2017 года. Мне потребовалось еще пять лет, чтобы написать код; подробнее см. в конце поста). И вот недавно я наткнулся на идею в книге Д. Хоули, Р. Хоули «Excel 2007. Трюки», которая позволяет обойтись вообще без кода.

Рассмотрим список чисел от 1 до 100 (рис. 2). 1; см. также лист Excel «SUMMESLI») [1]. Диапазон имеет условное форматирование, которое отмечает ячейки, содержащие числа больше 10 и меньше или равные 20.

Рисунок 1. Диапазон чисел; условное форматирование выделяет ячейки со значениями от 10 до 20

Читайте так же:
Как быстро извлечь имя файла из полного пути в Excel?

Скачать записку в формате Word, примеры в формате Excel

Теперь необходимо сложить значения ячеек, которые соответствуют заданным критериям. Не имеет значения, какое форматирование вы примените к этим ячейкам, но вы должны знать критерии, по которым ячейки выделяются.

Для того чтобы суммировать диапазон ячеек, которые отвечают одинаковым критериям, можно использовать функцию СУММЕРКИ (рис. 2).

Рис. 2. Суммирование клеток, удовлетворяющих одному и тому же условию

При наличии нескольких условий можно использовать функцию SUMMESLYMN (Рисунок 3).

Рис. 3. Суммирование ячеек, удовлетворяющих нескольким условиям

Если вы хотите подсчитать количество ячеек, удовлетворяющих одному и тому же критерию, вы можете использовать функцию COUNT.

Если вы используете функцию SCAN, вы можете подсчитать количество ячеек, которые соответствуют нескольким критериям.

В Excel есть еще одна функция, позволяющая задать несколько условий. Эта функция входит в набор функций базы данных Excel и называется BDSUMM. Для проверки можно использовать тот же набор чисел в диапазоне A2:A100 (Рисунок 4; см. также лист Excel «BDSUMM»).

Рис. 4: Использование функций базы данных

Выделите ячейки C1:D2 и дайте этой области имя Критерий, введя его в поле Имя слева от строки формул. Теперь выделите ячейку C1 и введите =$$A$1, что является ссылкой на первую ячейку электронной таблицы, содержащую имя базы данных. Введите =$A$1 в ячейку D1, и вы получите две копии заголовка в столбце A. Эти копии будут использоваться в качестве заголовков для условий BDSUM (C1:D2), которые вы назвали Criterion. Введите >10 в ячейку C2. Введите <=20 в ячейку D2. В ячейке, где должен находиться результат, введите следующую формулу:

С помощью функции SCRUT можно определить количество ячеек, которые соответствуют нескольким критериям.

голоса
Рейтинг статьи
Читайте так же:
Как быстро найти и найти круговую ссылку в Excel?
Ссылка на основную публикацию
Adblock
detector