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

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

Пользуясь, случаем, хочу еще раз поблагодарить Вас за предоставленные, неоценимые материалы по работе с Excel, которые в очередной раз помогли выбраться из безвыходной (для меня) проблемы.

А возникла она буквально на днях - 3 и 4 мая. К обеду 4 мая необходимо было снять и обработать сотни электро, тепло, водосчетчиков и отчеты отправить в сбытовые и сетевые организации. В связи с отказом системы АСКУЭ, показания счетчиков пришлось списывать с каждого счетчика на ноутбук с помощью специальной программы, которая не позволяет вывести показания всех счетчиков в отдельную таблицу или распечатать. Пришлось сотни показаний переносить вручную на рабочий ПК для дальнейшей обработки. Пришлось очень сильно напрячься, чтобы уложиться в сроки.

Поэтому пришлось задуматься, как извлечь данные из базы данных этой программы в таблицы Excel. Базы данных оказались формата .mdb, которые не захотели открываться в Access 2013. Вот тогда-то и пришлось обратиться к Вашему материалу «Тренинг Взаимодействие Excel с другими приложениями», Занятие 03, «Работа с Access».

В результате просмотра видео материалов и поиска пароля удалось сделать для меня невозможное, а именно, вытащить данные из БД, не запуская Access. Это для меня оказалось таким шоком, что я долго не мог поверить в такой результат при таких незначительных усилиях. Пусть данные оказались в ужасном состоянии, но с приобретенными навыками автоматизации мне не составило большого труда разобраться с полученными таблицами и привести их в необходимый вид.

Это был мой очередной, очень удачный опыт изучить новую тему по Вашим материалам.

Это в очередной раз доказывает доступность и качество Ваших материалов для новичков.

Огромное спасибо за Ваши труды для облегчения наших проблем.

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

Всем привет!

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

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

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

Евгения Крюкова о практическом тренинге
На тренинг пошла с нулевыми знаниями по программированию...

Дмитрий, большое спасибо за тренинг!

На тренинг пошла с нулевыми знаниями по программированию, так как сама не могла разобраться со структурой написания кода. Где-то после 2 занятия начала потихоньку разбираться и понимать, что к чему. Все уроки, Дмитрий проводил на простом и понятном, даже для начинающих, языке, во время исполнения домашних заданий была хорошая обратная связь с указанием недочетов, но в тоже время с возможностью подумать и исправить данные недочеты своими силами, я думаю, что очень важно, когда ты сам исправляешь ошибки, а не просто смотришь, как это делают другие. Конечно домашки делала я долго, но благодаря этому, начала потихоньку разбираться и теперь смогу применить на практике многие вещи по автоматизации для упрощения и сокращения времени работы.

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

Анна Шайхутдинова о практическом тренинге
Изучала запоем, узнала очень много нового

Спасибо, Дмитрий!

Классный и полезный тренинг, рекомендую всем!

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

Лучшие уроки по содержанию и грамотности, доступности, простоты я не встречала. Спасибо вам. вы умница.

Ольга
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

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

▲ Наверх