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

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

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

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

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

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

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

Ольга Мельникова о мастер-классе
У меня был шок: я не смогу написать эту программу!

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

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

И вот решилась на участие в тренинге. Впечатлений масса. Материал занятия дается четко, ясно, доступно. Рассматриваются несколько вариантов написания программы с объяснением почему так лучше. Каждый урок заканчивается домашним заданием. Когда Дмитрий выдал задание 1 у меня был шок: я не смогу написать эту программу! Вроде все понятно, но как делать – не знаю. Пересмотрела урок и работа началась.

Задания построены так: часть пунктов - «делай как я», часть «адаптируй информацию урока к выполнению задания» и часть «приложи усилия и найди решение самостоятельно». При такой постановке задачи НЕ выполнить совсем задание просто не возможно. И у меня получилось написать свою первую программу. И это только благодаря Дмитрию, его профессионализму.

Теперь я смотрю на свою работу и вижу, что упростить себе жизнь я смогу, используя материал тренинга. Появились новые идеи. Захотелось разбираться дальше.

Объяснить ученику-трудно, а научить-еще сложнее. У Дмитрия получилось именно научить. Спасибо большое. Успехов Вашему проекту и хороших учеников.

Наталия Суходолова о практическом тренинге
Хочется продолжать углубляться, внедряя полученные навыки в работу

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

Ольга Коноплина о практическом тренинге
Моя работа вышла совершенно на иной уровень

Добрый день Дмитрий

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

Спасибо за знания

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

Хочу поблагодарить вас за проведенный вебинар, который состоялся 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 политикой конфиденциальности
Ваши данные строго конфиденциальны, они нигде не публикуются и используются исключительно для информирования вас о новых материалах на сайте, мероприятиях и/или иных услугах данного проекта.
В любой момент времени вы можете отказаться от получения сообщений.
Последние комментарии
Ольга
11.11.2023 в 11:32:51

Полезно, наглядно, понятно, существенно расширила свои знания благодаря данным урокам - спасибо, Дмитрий)

Дмитрий Быстров
29.01.2023 в 12:26:41

Добрый день, Анатолий.

Подробнее о создании программы для тестирования было рассказано в специальном платном мастер-классе "Создание программы по тестированию". Приобрести его можно по этой ссылке.

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

Ulluauz
29.01.2023 в 11:03:16

Спасибо! Классно. Где можно списать Вашу программу для шаблона? Счетчик времени на тестирование как вставить?

Светлана
23.08.2022 в 13:10:20

Здравствуйте. Подскажите, а можно ли сделать Q-код в таблице, но чтоб там было видно наименование упаковки, вес, дата прихода, наименование контрагента и тд. И при каждом изменении данных, автоматически делался Q-код?

Людмила
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

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

▲ Наверх