Отбор уникальных значений с суммированием по соседнему столбцу в EXCEL
Имеется таблица, состоящая из двух столбцов: из столбца с повторяющимися текстовыми значениями и столбца с числами. Создадим таблицу, состоящую только из строк, с уникальными текстовыми значениями. По числовому столбцу произведем суммирование соответствующих значений.
Пусть исходная таблица содержит 2 столбца: текстовый – Список регионов и числовой — Объем продаж . Столбец Список регионов содержит повторяющиеся значения (см. файл примера ). Уникальные значения выделены цветом с помощью Условного форматирования .
Задача
Создадим на основе исходной, таблицу, в которой в столбце с перечнем регионов будут содержаться только уникальные названия регионов (т.е. без повторов), а соответствующие продажи будут просуммированы.
Решение
Создадим Динамические диапазоны : Регионы (названия регионов из столбца А ) и Продажи (объемы продаж из столбца B ).
Если в исходный список будет добавлено новое значение, то оно будет автоматически включено в Динамический диапазон и нижеследующие формулы не придется модифицировать.
Для создания списка уникальных значений введем в ячейку D2 формулу массива : =ЕСЛИОШИБКА(ИНДЕКС(Регионы; ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;Регионы);0));»»)
Не забудьте при вводе вышеуказанной формулы нажать CTRL+SHIFT+ENTER . Затем необходимо скопировать формулу вниз (размерность списка уникальных значений должна совпадать с размерностью исходного списка).
Для подсчета суммарных продаж в столбце E используем мощную функцию СУММПРОИЗВ() : =СУММПРОИЗВ((Регионы=D2)*Продажи)
Отображение нулей в строках, в которых нет регионов, уберем пользовательским форматом # ##0;-# ##0; (см. статью Скрытие значений равных 0 ).
Тестируем
1. Введите в ячейку А11 новый регион — Китай 2. Введите объем продаж — 100 3. Введите в А12 — Китай 4. Введите объем продаж — 500 5. В соседней таблице справа в ячейке D7 будет выведено название региона Китай с суммарным объемом продаж 600
СОВЕТ: Другим подходом к решению этой задачи является использование Сводных таблиц (см. файл примера ).
Суммирование значений с учетом нескольких условий
Предположим, что вам нужно свести значения с более чем одним условием, например суммой продаж продуктов в определенном регионе. Это хороший случай для использования функции СУММЕСС в формуле.
Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).
Вот формула, с помощью которая можно сопровождать эту формулу:
=СУММЕСС(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)
Результат — значение 14 719.
Рассмотрим каждую часть формулы более подробно.
=СУММЕСЛИМН — это арифметическая формула. Она вычисляет числа, которые в этом случае находятся в столбце D. Прежде всего нужно указать расположение чисел.
=СУММЕСЛИМН(D2:D11,
Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Это диапазон ячеок является первым аргументом в этой формуле — первым элементом данных, который требуется функции в качестве входных данных.
Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»). Обратите внимание на запятую между аргументами:
=СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;
Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.
Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.
=СУММЕСЛИМН(D2:D11;A2:A11,»Южный»;C2:C11,»Мясо»)
Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.
На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).
По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.
Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.
Вы можете работать с образцами данных и формулами прямо в этой Excel в Интернете книге. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты.
Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.
Что следует ввести
Функция СУММЕСЛИ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;»> 5″)
Это видео — часть учебного курса Сложение чисел в Excel.
- При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; «Иван»; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны «Иван».
- Если необходимо выполнить суммирование ячеек в соответствии с несколькими условиями, используйте функцию СУММЕСЛИМН.
Важно: Функция СУММЕСЛИ возвращает неверные результаты при использовании для сопоставления строк длиной более 255 символов или строкового #VALUE!.
Синтаксис
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Аргументы функции СУММЕСЛИ описаны ниже.
- Диапазон — обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).
- Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Можно включить подстановочные знаки : вопросительный знак (?) для сопоставления с любым одним символом, звездочка (*) для соответствия любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (~). Например, критерии можно выразить как 32, «>32», B5, «3?», «apple*», «*~?» или TODAY().
Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки («). Если условием является число, использовать кавычки не требуется.
Примеры
Пример 1
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
Стоимость имущества
Повторяющиеся значения в одном столбце + Суммирования
В очередной раз обращаюсь к Вам за помощью в макросе.
Есть макрос, который взят с простора интернета и капельку отредактирован, помогите, пожалуйста, немного дополнить его.
Макрос объединяет и суммирует вроде как нужно, только не хватает выстраивание в горизонт и выделения цветом повторов. Ниже описал как должно получится.
Хотелось бы чтобы в вкладке «База» проверял и выделяла дубликаты автоматический с A по G если есть совпадения в Наименование материалов по № по СМЕТЕ – это для исключения ошибок, так как база заполняется вручную и человек может 2 раза вставить одно и тоже.
Макрос уже работает только допилить нужно немного.
В вкладке «Как нужно1» (пример в самой вкладке) в столбце «№ Линии» нунжно чтобы вставляла все линии в горизонт и разделяла их через запитую (HC5036, LC5001, LC5009, MI4003, и т.д.), где попадается (встречается) с Наименованием материалов.
Заранее спасибо всем кто откликнулся.
Вложения
| Повторяющиеся значения.rar (323.6 Кб, 8 просмотров) |
Лучшие ответы ( 1 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
исключить повторяющиеся значения в столбце
Вопрос технич. характера по структуре SQL-запроса. Нужно удалить все записи с таблицы, в которой.
Нужно удалить повторяющиеся значения в столбце таблицы Excel
Есть два столбца (А и Б).В столбце А-находится список ФИО работников вообще на предприятии,а в.
SQL отобрать все значения не равные предыдущему в одном столбце
Требуется отобрать периоды, в которых значение Value отличается от значения Value в предыдущем.
Как одним запросом изменить два значения в одном столбце
Используя UPDATE, изменить два значения в одном столбце в одном запросе.
539 / 473 / 187
Регистрация: 11.12.2013
Сообщений: 2,477
Сообщение было отмечено olegwolf как решение
Решение
у меня получилось вот так
результат работы макроса (wrk) выводится на листы «Результат 1» и «Результат 2».
Вложения
| Повторяющиеся значения.zip (387.7 Кб, 6 просмотров) |
Регистрация: 06.05.2022
Сообщений: 8
Алекcей, спасибо огромное! Вроде работает норм. Пока не заметил изъян.
Одна просьба даже две, что нужно дописать чтобы расположение текста повторялся как в базе, а то текст в Результате 1 и 2 гуляет влево и вправо в ячейках. Я исправил но после запуска макроса они опять беспорядочно меняются в ячейке.
2 просьба, можно, пожалуйста, дописать чтобы удалял лишний пробел в начале, в конце и в самом тексте.
Часто бывает что при копирования текста с какого-то документа это не заметишь.
Пример: («черточка» это пробел для примера, так как тут в чате он удаляется лишний пробел )
Анкер —
— Анкер
Датчик — температурный