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

По сфере деятельности (экономика-финансы), без Excel(я) жить не могу, что на работе, что дома всё в нем, в родном! )

Уровень владения средний, но по сравнению с другими, достаточно высокий. Автоматизация достигается в основном с помощью, формул, связей, выпадающих списков, именованных диапазонов, формул: ЕСЛИ, ВПР, ПОИСКПОЗ, ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ и сводных таблиц. В общем много что сделано, всё работает, можно сказать упр.учет ведется в нем. Актуальность и точность данных выше бухгалтерских. Конечно бывают мелкие недочеты и дублирование вводимой информации…

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

Попытки начать изучать VBA были, но безрезультатно… Сталкивался со многими непонятностями, не понимал структуру и те же самые Dim, Do, которые вводили в ступор.

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

В общем Дмитрий сумел раскрыть часть непонятного и МИФического (VBA в транслите = МИФ :) ) для меня, СПАСИБО!

Дмитрий о практическом тренинге
Я бы дала курсу девиз "теория и практика, практика"

Всем привет!

Мне посчастливилось позаниматься очно, чему я очень рада!

Я - владелец маленького начинающего интернет-магазина. У меня несколько поставщиков, у всех прайсы в экселе с разными данными, и за любое изменение в прайс -листе - следует изменение в парсере, что влетает для меня в копеечку, а "прибыля" в кризис оставляют желать лучшего. А я знаю, что некоторые программисты дерут за изменение/добавление 2-3 строк по 5-15 тыс. В свое время, у меня был опыт программирования, давалось мне это легко. Я сначала искала обучение на php, но было так страшно, отзывы неоднозначные про все эти курсы. И тут приходит суперпредложение от Дмитрия поучаствовать в тренинге. Excel я знаю неплохо, давно знаю сайт Дмитрия, по бесплатным урокам знакома с методикой обучения. Поэтому я подумала, а почему бы мне не делать вообще это через Excel?

Курс замечательный. Я бы дала ему девиз "теория и практика, практика". Еще не закончив его, у меня уже наработки есть по моей работе. В общем я очень довольна, мне очень нравиться, особенно нравиться то, что все получается! А у Дмитрия не возможно что-то не понять! В общем если есть сомнения - не сомневайтесь, для обработки данных в этом курсе есть все.

Евгения Крюкова о практическом тренинге
Много лет я смотрел на VBA и не знал, с какой стороны к нему подойти

Спасибо, Дмитрий! Было интересно и познавательно. Много лет я смотрел на VBA и не знал, с какой стороны к нему подойти. После Ваших заданий, всё стало на свои места, т.к. применение конкретных алгоритмов к конкретным заданиям дает очень многое. Благодаря этому тренингу я знаю куда мне идти и расти дальше. Спасибо.

Виталий Зуев о практическом тренинге
Перестала бояться какой бы то ни было задачи

Очень здорово, что есть бесплатные уроки в формате: "Посмотрел-применил". Тренинг идет, а в голове: "Завтра же на работе сделаю вот этот отчет, как ... задачу". И вперед.

Справочник от Дмитрия отличная подсказка тем, кто знает что хочет получить, но не знает, что писать.

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

Марина Данилушкина о практическом тренинге
Это как экранизация Уокенбаха, но со многими "примочками" и "фишками"

Уважаемый Дмитрий! Благодарю за Ваш тренинг. Он значительно помогает сократить время, чем обучение по книгам. Это как экранизация Уокенбаха, но со многими "примочками" и "фишками", постижение которых в книжном варианте было бы гораздо длиннее. Толково все объясняете-"разжевываете". Теперь макросы не пугают.

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

Желаю успехов! Нужное дело!

Аскар о практическом тренинге

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

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

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

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

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

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

Добрый день.

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

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

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

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

▲ Наверх