Сумма ячеек по цвету, шрифту, формату и т.д

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

1. Сохраняем документ с поддержкой макросов, хотя его там и не будет, но в данном случае так надо.

2. Нажатием Ctrl+F3 или на вкладке «Формулы» выбираем «Диспетчер имён», где в строке «Имя» вводим название формулы ЦветЯчейки, а в поле «Диапазон» саму формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;ДВССЫЛ("RC[-1]";0))

=GET.CELL(63;INDIRECT("RC[-1]";0))

Сумма ячеек по цвету, шрифту, формату и т.д Microsoft Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Я не буду грузить читателя разъяснением значений этой формулы, главное, чтобы всё работало, не так ли?)) Отмечу лишь, что 63 это код цвета заливки, заменяя который, можно подсчитывать ячейки по формату, шрифту, выделению курсивом и т.п.

Вот таблица с кодами и их значениями:

Сумма ячеек по цвету, шрифту, формату и т.д Microsoft Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

3. В соседнюю от цветной ячейки вводим «равно» и имя созданной формулы ЦветЯчейки, затем протягиваем её вниз. В результате отображаются коды цветов, которые будут необходимы нам для подсчёта.

Сумма ячеек по цвету, шрифту, формату и т.д Microsoft Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

4. Теперь с помощью формулы =СУММЕСЛИ(B2:B12;10;A2:A12) можно посчитать сумму цветных ячеек, где 10 это код цвета, для жёлтых - 6:

=SUMIF(B2:B12;10;A2:A12)

Сумма ячеек по цвету, шрифту, формату и т.д Microsoft Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Созданная формула =ЦветЯчейки определяет 56 цветов. На некоторые цветовые гаммы он реагирует некорректно, так что любителям 50 оттенков серого придётся воспользоваться хардкорным чёрным или серым))

Вот пример палитры «твёрдых» цветов:

Сумма ячеек по цвету, шрифту, формату и т.д Microsoft Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Следует учесть, что Excel при изменении цвета ячейки автоматически не совершит пересчёт данных, для этого необходимо заново ввести формулу, либо нажать Ctlr+Alt+F9, что гораздо проще и быстрее.


Также на этот случай есть макрос,


1. Нажимаем Alt+F11 и в открывшемся окне вводим следующий текст


Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double

Dim Sum As Double

Application.Volatile True


For Each cell In DataRange

If cell.Interior.Color = ColorSample.Interior.Color Then

Sum = Sum + cell.Value

End If

Next cell

SumByColor = Sum

End Function


2. В разделе «Формулы» - «Вставить функцию» выбираем категорию «Определенные пользователем», где указываем нашу функцию, которой задаём диапазон подсчёта и образец цвета:

Сумма ячеек по цвету, шрифту, формату и т.д Microsoft Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

И получаем результат:

Сумма ячеек по цвету, шрифту, формату и т.д Microsoft Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Чтобы макрос учитывал не цвет заливки фона, а цвет шрифта ячейки, в шестой строке заменяем свойство Interior на Font в обеих частях выражения.


Для подсчёта количества цветных ячеек, а не суммы, заменяем в седьмой строке Sum = Sum + cell.Value на Sum = Sum + 1


Как и в первом варианте, макрос также не может подсчитать сумму ячеек автоматически, поэтому после произведённых изменений нажимаем клавишу F9.


Если значения в Вашей таблице окрашены в различные цвета и нужно их пересчитать по отдельности, то просто замените ячейку с образцом на цвет из таблицы и нажав F9 получите мгновенный результат.

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


Следует учесть, что функция перебирает все, в том числе и пустые ячейки, в DataRange, поэтому задавайте в качестве первого аргумента только диапазон со значениями, а не весь столбец, иначе Excel «зависнет» надолго.


P.S. Уважаемые подписчики, посты про Excel будут выходить по понедельникам

MS, Libreoffice & Google docs

715 постов15K подписчиков

Добавить пост

Правила сообщества

1. Не нарушать правила Пикабу

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях


Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.

Вы смотрите срез комментариев. Показать все
Автор поста оценил этот комментарий

В word есть такая штука, как рассылка.

В документ можно из таблицы подставить колонки и потом распечатать для каждой строки из таблицы свой документ.


Есть ли в excel что-то подобное?

Например есть список гостей и пригласительный сверстаный в excel. И нужно для каждого гостя распечатать пригласительный.

раскрыть ветку (9)
Автор поста оценил этот комментарий

Попробуйте просто запись макроса. Если разовая задача- то это будет проще, чем разбираться с VBA.

раскрыть ветку (8)
Автор поста оценил этот комментарий

Я через Apache POI делаю сейчас. Получается книга из 1600 листов, приходится бить на несколько файлов.

раскрыть ветку (7)
Автор поста оценил этот комментарий

А зачем эта книга вообще? Задача ведь: изменить ФИО-отправить на печать- выбрать следующего адресата-изменить ФИО и т.д.

Данные у вас и так имеются в отдельной таблице.

Ну или печатайте сначала в ПДФ с названием файла в виде ФИО, если перед печатью надо все проверить. А потом просто скопом отправляйте это барахло на принтер.

По итогу или 10 минут в записи и опробовании макроса и 1600 нажатий клавиш, или еще 20-40 минут на изучение как работают переменные в VBA и как найти кол-во строк - и у вас все генерится по одной кнопке. Это если с VBA вообще не сталкивался.

раскрыть ветку (6)
Автор поста оценил этот комментарий

Документ не для меня, я лишь исполнитель.

раскрыть ветку (5)
Автор поста оценил этот комментарий

Ну если сейчас все работает и всех устраивает -менять только проблем наживёте ))

Но попробуйте просто для себя заколбасить это в екселе без апача. Ессно, поинтересовавшись, а чего с этими 160 страничными доками потом делают.

А то окажется, что печатают этикетки. Где в программах для принтера уже имеются вот эти все функции вытаскивания данных из таблицы.

раскрыть ветку (4)
Автор поста оценил этот комментарий

Вы близки к истине.

Этикетки это word документы. Из них нужно протоколы ОТК сделать по шаблону. Из них и из таблички.


Все это должно было быть автоматизированно, но как обычно на предприятиях бываете, проверка нагрянула а ничего не настроено.

раскрыть ветку (3)
Автор поста оценил этот комментарий

BarTender из ексель- таблицы данные вытягивает. ZebraDesigner тоже.
Или у вас там хитрый принтер этикеток какой?


Но по скудным от вас сообщениям как и чего, даже догадки сложно строить )) Может быть и помог бы чем.

раскрыть ветку (2)
Автор поста оценил этот комментарий

Этикетки это обычный лист А4 в ворд документе. Не пользуются они специализированным софтом.


Каждая этикетка в течении года руками составлялась. И фактически только там ТТХ изделия есть.


Теперь же нужно из таблицы эксель с перечнем изделий и некоторыми параметрами для каждой строки распарить этикетку. И потом для каждой строки сгенерировать заполненный документ на основе данных из таблицы и этикетки.


И я это сделал, но через POI, т.к. мне проще было на scala написать, чем с VBA разбираться.

раскрыть ветку (1)
Автор поста оценил этот комментарий

Реализуйте все в екселе.  Из ворда довольно легко все перекинуть.

Заодно многоязычность можно прикрутить и всякие проверки заполнения.

http://www.responsiblemineralsinitiative.org/media/docs/RMI_...

Вот тут вариант реализации. Покопайтесь, очень интересный опросник. @Veseliy.4el может и на его основе пару постов забабахает. И на все ТТХ сделать один документ с выбором из списка. Однокнопочные сотрудники будут рады.


А уж сгенерировать нечто (в ПДФ-е для распечатки или новый документ) можно или макросом или через VBA. Главное иметь данные в табличном виде, а не разбросанные по вордовским документам. Особенно если там разная верстка и делали это разные люди.

Вы смотрите срез комментариев. Чтобы написать комментарий, перейдите к общему списку