Суммирование значений в окрашенных ячейках | Автоматизация в Excel - бесплатные уроки
Отзывы о мастер-классах и тренингах
Смотрю, как некоторые тыкают мышкой в ячейки часами, и вспоминаю себя двухмесячной давности

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

Виталий Билько о практическом тренинге
Файл весит в 5,5 раз меньше, открывается легко (не пересчитываются формулы), работает всё быстро!

Ещё раз спасибо! Прошло немного времени после занятий и вот уже есть первые результаты: используя полученные знания на тренинге, работаю над своим большим файлом. В старом виде писала огромное множество формул для расчётов и всяческих проверок, плюс руками надо было протягивать формулы и глазами смотреть в ячейки проверок (в общем пользователь этого файла не смогла аккуратно "вести" его). Именно из-за этого файла в совокупности с возможностями пользовательницы я и решилась на изучение макросов (надоело за неё работать по понедельникам). Теперь все расчёты и проверки я написала в VBA, благодаря Вам. В результате файл весит в 5,5 раз меньше, открывается легко (не пересчитываются формулы), работает всё быстро! Я просто в восторге!!! Осталось немного доработать и отдать в пользование человеку, который с экселем не очень дружит, но теперь и не надо. Думаю, она тоже будет довольна.

А потом я и своих тяжеловесов "причешу" потихоньку.

Всем, кто только принимает решение о прохождении этого тренинга - конечно же ДА, пройти обязательно!!!

Спасибо ещё раз, Дмитрий!

Татьяна Богославская о практическом тренинге
Теперь нужно все уложить "по полочкам" в своей голове

Вчера состоялся мастер-класс по автоматизации в Excel. Для меня это первый опыт участия в обучающем вебинаре.

Огромное спасибо Дмитрию за его добросовестную работу, терпение к новичкам, таким как я, которые задают множество вопросов, порой даже нелепых.

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

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

Спасибо, Дмитрий, за Ваш труд, умение простым языком донести непростой материал. Я пару раз открыла учебник VBA и закрыла с твердым убеждением, что это не для моего понимания (хотя за плечами два высших).

Больших успехов Вам! Жду новых тем и вебинаров!

Ольга Мельникова о мастер-классе
Казалось, что макросы - это только для программистов, но после вебинара изменила свое мнение

Хочу поблагодарить вас за проведенный вебинар, который состоялся 26.06.2018г. С excel работаю давно, но с макросами никогда не сталкивалась, казалось, что это только для программистов. После просмотра вебинара изменила свое мнение. Понятно, что не зная языка программирования и даже базового английского, сама что-то придумать я не смогу, но на имеющихся ваших примерах, можно существенно облегчить рутинную работу. Главное, что объясняете вы доступно и понятно даже для новичков, а запись помогает закрепить усвоенный материал. Конечно,не все на раз - два получается, но если задаться целью и набраться терпения, результат превосходит все, даже самые смелые, ожидания. Качество записи отличное, плюс xl файл с прописанными макросами. Спасибо вам огромное!

Нина Фролова о мастер-классе
Получаются такие задачи, о которых раньше и не мечтала

Добрый день, Дмитрий! Очень благодарна вам за ваши уроки и вебинары, теперь моя работа происходит на более качественно уровне, получаются такие задачи, о которых раньше и не мечтала, и за все это еще раз огромное Вам спасибо, вебинар по функциям в большей части смотрю в записи, т.к. достаточно поздно и следующий день рабочий, но тем не менее в нем море полезной информации.

Наталья Медведева о мастер-классе

Суммирование значений в окрашенных ячейках

Иногда возникает необходимость произвести вычисления с ячейками рабочего листа, выделенными каким-либо образом: цветом текста, цветом заливки, жирностью шрифта, его размером и т.д. К сожалению, в числе имеющихся формул Excel нет таких, которые позволили бы определить свойства оформления той или иной ячейки. Но эта задача легко решается с помощью средств VBA.


Для просмотра видео необходимо
зарегистрироваться на сайте
Если вы уже зарегистрированы на сайте -
обновите страницу по этой ссылке или кнопкой F5

Комментарии

#1842   Виталий    24.02.2014 в 18:22:45

собственно и комментировать тут нечего

#1843   Сергей    24.02.2014 в 18:34:37

Большое спасибо за разъяснение нестандартных подходов по решению задач.

#1844   Виталий    24.02.2014 в 18:41:22

?

#1845   Алексндр Михайлович    24.02.2014 в 19:38:37

Мне сильно понравился урок 13, но это лишь первый шаг к успеху! Как автоматом заставить окрашивать ячейки в НУЖНЫЙ ЦВЕТ? Этого здесь нет.

#1893   Дмитрий Быстров    14.03.2014 в 15:24:26

Добрый день, Александр.

Использование пользовательской функции в ячейке листа (которое показано в этом уроке) применимо лишь для изменения значения ячейки. Свойства оформления (цвет, жирность, размер шрифта и т.п.) таким способом установить не получится.

Для автоматического окрашивания ячейки используйте либо штатный инструмент "Условное форматирование", либо средства автоматизации - в этом случае пишем программку, где указываем примерно такую команду:

Cells(1,1).Interior.Color = vbRed

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

#1848   Murari    24.02.2014 в 20:48:43

есть парочка вопросов:

- если ячейка имеет цвет нестандартный (какойто салотовый, розовый или еще какой то), то надо ниайти (где?) как этот цвет в екселе называеться?

- присоеденяюсь к вопросу Александра

- скажите, пожалуйста, почему не использовать фильтр по цвету, это выглядит намного проще? (имею ввиду в случае если надо какой то текст в соответствии к цвету ячейки)

#1862   olga    25.02.2014 в 04:39:46

согласна с Murari по последнему пункту - фильтр по цвету и обычная автосумма, к тому же работает и по условному форматированию - автоматически промежуточная сумма собирается

#1894   Дмитрий Быстров    14.03.2014 в 15:37:51

Добрый день, Murari

1. Для нестандартных цветов используйте не именованные константы (типа vbRed, vbBlack, vbYellow и т.д.) - эти константы имеются лишь для основных цветов - а функцию RGB (о функции см. подробнее в уроке №9). А для того, чтобы узнать, какие аргументы надо указать в этой функции, можно использовать следующий трюк:

1) окрашиваете ячейку в нужный цвет

2) открываете в меню Цвет заливки - Другие цвета...

3) в открывшейся палитре цветов на вкладке "Спектр" отображаются компоненты цвета: красный, зеленый и синий

2. на вопрос Александра ответил выше

3. если знаете средства решения задачи помимо средств VBA - конечно, можете использовать их. я лишь показываю варианты решения с помощью программирования (ну, и к тому же в большинстве случаев этот способ более гибок - ведь в VBA Вы можете написать различные условия и произвольно их комбинировать)

#1849   Татьяна Мантыкова    24.02.2014 в 21:00:57

креативненько, интересно

нужно попробовать для просчета "на скорую руку"

#1850   Николай    24.02.2014 в 21:18:21

Здорово!

#1851   Александр    24.02.2014 в 21:31:53

вдруг при случае может и понадобится

#1852   Леонид    25.02.2014 в 00:30:40

Интересно. Когда-то нужно было просуммировать по цвету, а найти решение не мог.

#1853   Вячеслав    25.02.2014 в 00:37:23

Очень даже интересно.Для тех,кто работает с таблицами может пригодиться

#1854   Юрий    25.02.2014 в 01:07:36

Cпасибо очень поучительно как и всегда

#1855   Светлана    25.02.2014 в 01:09:27

Спасибо. Полезный урок.

#1858   Владимир    25.02.2014 в 02:22:38

Спасибо. Очень нужный урок. Как и все показанные ранее.

#1860   Михаил    25.02.2014 в 02:45:22

Спасибо! Попробую применить в своих таблицах

#1861   Владимир    25.02.2014 в 03:33:57

Спасибо! Полезный урок.

#1863   Елена    25.02.2014 в 12:16:31

Огромное спасибо! Но чаще, нужна сумма в столбце из ячеек определенного цвета. Так как при внесении изменений в таблицу необходимо постоянно проверять итоги.

#1865   Олег    25.02.2014 в 14:48:09

Большое спасибо за урок. Пиходится заниматься подобными задачами с цветом. Решение полезное к практическому решению.

#1866   Василий    25.02.2014 в 15:26:35

Спасибо! но хочу заметить, ранее уроки были более полезные и появлялись чаще.

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

#1867   Амир    25.02.2014 в 15:38:29

а как все просто и доступно

#1868   Артемий    25.02.2014 в 15:42:00

Огромное спасибо за урок! и такой вопрос: возможно ли написать функцию с привязкой к условному форматированию?

#1870   Марал    25.02.2014 в 15:53:44

Большое спасибо,иногда занимаюсь аналогичными задачами, это решение очень кстати и конечно же приму к сведению! Дмитрий вам хочется пожелать чтобы ваш энтузиазм не иссякал !!!

#1879   Den    02.03.2014 в 15:30:52

Большое спасибо за неординарный подход.

#1881   Ольга    02.03.2014 в 19:04:17

Как всегда, актуально, полезно и просто в Вашем решении, Дмитрий.

Спасибо!

#1896   Татьяна    15.03.2014 в 00:03:49

Подскажите, есть ли решение следующей задачи: Есть большая таблица. С помощью условного форматирования определенные ячейки первого столбца (ненужные) выделены цветом - таких ячеек более 100; нужно удалить ВСЕ строки, содержащие такие ячейки...

#1897   Murari    15.03.2014 в 03:52:07

"Подскажите, есть ли решение следующей задачи: Есть большая таблица. С помощью условного форматирования определенные ячейки первого столбца (ненужные) выделены цветом - таких ячеек более 100; нужно удалить ВСЕ строки, содержащие такие ячейки..."

Татьяна, попробуйте:

Выставляете на этот столбец фильтр, вибираете "фильтр по цвету", выбираете нужный цвет. У вас остануться строки только с выбранным цветом, остальные будут скрыты. Выделяете их (все строки листа), правая кнопка мышки - удалить. Потом снимаете фильтр и имеете таблицу без выделеных цветом ячеек.

#1898   Татьяна    15.03.2014 в 21:58:05

Спасибо, Murari. Вот так заработаюсь, а потом ищу сложное в простом.)

#1899   Дмитрий Быстров    16.03.2014 в 16:37:43

Согласен, простое и красивое решение В большинстве случаев его достаточно и прибегать к средствам VBA нет необходимости. Если же потребуется более сложное условие - например, "если в столбце A заливка красная, и при этом в столбце B шрифт жирный" - тогда все же потребуется использование VBA.

Понятно, что этот мой пример "притянут за уши" и в практике вряд ли где-то действительно понадобится - тем не менее, ситуации бывают разные

#1900   Murari    17.03.2014 в 03:23:43

Да, Дмитрий, бывают иногда и такие сложные случаи, и их тоже не помешает уметь решать

#1933   Анара    11.04.2014 в 11:37:17

Большое спасибо за урок!

#2143   Janis    29.06.2014 в 17:31:03

Spasibo, Dmitrij Gennadjevič za predostavlennij material dlja zanjatij, trenirovok i t.d.

#2165   Татьяна    17.07.2014 в 15:46:00

Дмитрий, огромное Вам спасибо за бесплатный мастер-класс, проведенный 15.07.2014. Это было для меня единственным вариантом получения дополнительных знаний, которых не получила из видеоуроков - муж никак не соглашается заплатить за предлагаемые платные мастер-классы и тренинги, даже с учетом скидок((( А я так люблю Excel СПАСИБО!!!

#2355   Антон    23.09.2014 в 19:10:55

Урок познавательный. Всё жду урока более серьёзного. Например, макрос с If-Then-Else, или как с множества разных таблиц на листе посчитать сумму именно в определённых ячейках и т.д.

#3047   Олег Абаевич Черанев    05.02.2016 в 23:38:46

Дмитрий, спасибо за урок. Я его позднова то просмотрел. Однако у меня возник вопрос: как подсчитать залитые ячейки в строках или столбцах таблицы? Спасибо.

#3054   Дмитрий Быстров    18.02.2016 в 16:29:38

Добрый день, Олег Абаевич.

Самое простое, что приходит в голову: запустить в цикле проверку цвета заливки ячеек в некотором диапазоне (можно в строке или в столбце). И если заливка имеется (при необходимости можно проверить ее и на конкретный цвет) - просуммировать такие значения. Сделать это несложно, но потребуются некоторые навыки работы с циклами и условиями.

#3272   Вадим    08.03.2018 в 18:34:52

Добрый день Всем! Просмотрел урок,но это не то что мне нужно. Может кто поможет? У меня есть график выходов на работу. В какие-то дни люди работают в смену день или ночь. Вкакие-то выходят сверхурочно. Всё это я веду в экселе и заливаю ячейки разными цветами. А в конце месяца хотелось бы видеть сколько дней и часов отработал тот или иной работник.

#3273   Олег Ефимов    09.03.2018 в 02:33:23

Вадим, вставь в модуль эту функцию. В ячейке, где должна отобразиться эта сумма (сколько дней и часов отработал тот или иной работник) введи = и найди функцию SumByColor, там надо будет указать аргументы, т.е. диапазон (график работы) и цвет по которому будет идти сравнение.

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

#3275   Олег Ефимов    10.03.2018 в 00:50:54

Или киньте на лист код:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim a As Integer

Dim b As Integer

Dim color As Integer

Dim nocolor As Integer

For a = 10 To 13

For b = 6 To 10

If Cells(a, b).Interior.color = 15773696 Then color = color + Cells(a, b) Else: nocolor = nocolor + Cells(a, b)

Range("L" & a) = color

Range("M" & a) = nocolor

Next b

color = 0

nocolor = 0

Next a

End Sub

Оставлять комментарии и просматривать видеоуроки
могут только зарегистрированные пользователи.
(если ранее Вы уже регистрировались, введите повторно
Ваше имя и тот e-mail, который был указан при регистрации)
Как к Вам обращаться:
Ваш адрес e-mail:
Нажимая на кнопку "Зарегистрироваться", я даю согласие на обработку персональных данных и соглашаюсь c политикой конфиденциальности
Ваши данные строго конфиденциальны, они нигде не публикуются и используются исключительно для информирования вас о новых материалах на сайте, мероприятиях и/или иных услугах данного проекта.
В любой момент времени вы можете отказаться от получения сообщений.
Последние комментарии
Людмила
17.04.2022 в 14:33:48

Добрый день, Дмитрий. Я случайно увидела ваши видео в ютубе. Посмотрела и мне стало интересно изучить макросы. Перешла по ссылке и просмотрела часть бесплатных видео и мне очень понравилась ваша манера преподавать просто без лишних слов. Скажите, пожалуйста, а как сделать выпадающий...

Станислав
16.08.2020 в 23:08:36

Здравствуйте В видео "Автоматизация заполнения бланка документа (платежного поручения)" говориться, что можно скачать файлы с макросами, о которых идет речь. Не могу найти. Подскажите, пожалуйста,где скачать Файл?

Юрий
21.07.2020 в 13:10:25

Благодарю Вас хотя у меня оффис 97 все полчается

Евгения
26.06.2020 в 01:10:51

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

juna
08.05.2020 в 15:02:44

Спасибо большое.Очень полезная информация

Людмила
03.05.2020 в 19:11:12

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

Алексей
25.04.2020 в 14:24:56

Доступно и понятно!Круто!!!

Дмитрий Быстров
15.04.2020 в 11:31:15

Добрый день.

Можно, но этот код будет посложнее, т.к. придется из Excel взаимодействовать с другим приложением - Outlook. А для этого нужно уметь обращаться не только с объектами Excel, но и с объектами Outlook (которые, конечно, сильно отличаются).

Подобные вопросы я рассматривал на специальном тренинге "Взаимодействие Excel с другими приложениями", подробнее о нем и его содержании можно узнать по этой ссылке.

Мария
15.04.2020 в 07:30:51

Дмитрий, доброе утро! А можно добавить еще макрос, чтоб после завершения теста еще и отправлял автоматически через аутлук адресату ответ с тестированием?

людмила
12.04.2020 в 22:31:20

Спасибо, Все, разобралась, внимательно смотреть нужно

▲ Наверх