Вычисление суммы, количества и среднего по цвету заливки и шрифта в Excel
Вставка
- Опубліковано 20 лип 2021
- Пишем простые макро-функции на VBA для подсчета количества, суммы и среднего арифметического ячеек с заданным цветом заливки и/или шрифта.
Скачать пример www.planetaexcel.ru/technique...
Мои книги planetaexcel.ru/books/
Мои онлайн-курсы www.planetaexcel.ru/learning/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Николай, низкий поклон за Ваш безценный труд на благо всех, кто пользуется Excel!!!
не знаю, все сделала, как на видео, но сумму мне так и не посчитало((((
Подача, как и всегда, на высшем уровне!
Легко и изящно, просто о сложном.
Как гениальный художник, лёгкими мазками и вуаля, результат.
Николай, спасибо!!!
Николай, Вы - восхитетельны! Ваши объяснения - великолепны. Огромное спасибо за Ваш труд. Пусть он приносит Вам не только прибыль, но и удовольствие!
Только недавно появилась задача с суммированием по цвету, и буквально через пару дней появляется данное видео. Большое спасибо, Николай!
Аналогичного контента в UA-cam достаточно много, но только здесь всё изложено по полочкам и грамотно. Например, объявлены переменные, что весьма важно. Спасибо автору канала и подписка.
Спасибо, Николай! Как всегда на высоте!
Николай, Ваши уроки СТОЛЬКО раз меня, выручали! Спасибо огромное!
Крайне благодарю данного мужчина, ваши учения оказались весьма полезны для меня. Преочень благодарен вам
Огромное вам спасибо!!!!! я никак не мог посчитать сумму цветных ячеек. дело в том что в экселе я новичок, а программирование в экселе, для меня вообще что-то запредельное, никогда этого не делал. НО благодаря вашему уроку я не только взял на вооружение этот макрос, но и написал свой, который мне помог посчитать количество цветных ячеек с конкретным текстом. Вы великолепный преподаватель!!!!
Очень познавательно. Один нюанс. Чтобы получить среднее, надо делить не на 7, а на количество ячеек с числами, т.е. 6
дак результат функция и выдала с учетом количества ячеек с числами, 27/6=4,5
Вот это шикарное видео!
Я такими макросами подсчитывал кол-во сотрудников в табеле, считал их выработку и прочие плюшки)
Конечно же, не без помощи знаний Николая👍👍👍
Великолепная подача материала. Особенно радует подробное объяснение работы кода VBA. Спасибо огромное.
Спасибо что вы есть 🙏🏻
Николай, хороший обучающий урок! Хотелось бы чтобы Вы выпустили еще одну книгу с VBA программированием, макросами и функциями, от нуля до продвинутого. С удовольствием приобрел бы данную книгу.
Я в шоке… спасибо! Как давно я искал простой вариант подсчёта/суммирования и так далее ячеек по цвету.
Огромное спасибо!
Отдельное спасибо за готовые шаблоны! Подача материала - по красоте! )
Николай, благодарю! Полезный урок, почерпнул для себя новое!
Как всегда все максимально просто и ясно! СПАСИБО!!!
жду уроки и учебник по VBA вашего авторства, очень нравится подача
Николай, спасибо!! И мне нравится Ваш голос и подача. Супер!!
Понятный и доступный пример. Спасибо за видео!
Спасибо за PLEX. С удовольствием пользуюсь.
Не за что :) Рад, что пригодился :)
PLEX - бомба!
Ну вот как раз завтра, это и пригодится в моей работе 👍
Всю сознательную жизнь работаю в эксель, но это видео - просто пушка! Огонь! Спасибо за видео!
Было бы круто, все эти функции запихнуть в одну функцию с указанием параметра - выбор алгоритма, например, как в функции "промежуточные итоги"
В очередной раз - БЛАГОДАРЮ!
Как всегда СУПЕР!!!
Спасибо, уже использую. Вы молодец.
А еще заставить пересчитать функции автоматически можно добавив в код if intersect, тем самым заставить эксель запускать код при выделении целевых ячеек.
О даа, специально это искал, ибо значение не меняется ладе если меняешь цвет ячейки
Подскажи, куда именно добавить в кол if intersect
Большое спасибо! Попробуем новинку на работе ))
Спасибо огромное!!!! Очень нужное видео!
Обшарил другие каналы, самое лучшее как всегда здесь
Это очень хорошо. Респект
Вижу Николая - ставлю лайк. Потом смотрю ролик.
Спасибо огромное вам!!!!!
Очень красиво
Спасибо!
Спасибо!!!
Огромное спасибо!!!!!!!)
Не совсем корректно называть процедуры с возвращаемым значением макрофункциями. Ведь вы знаете, что макрофункции - это функции листа макросов Excel 4.0, предшественника VBA. Кстати, по ним можно было бы сделать небольшой ликбез. Очень полезная штука.
А за видео, как всегда лайк. Для меня ничего нового, а людям - минус один мартышкин труд. :)
Просто 🔥🔥🔥
Большое спасибо за видеоурок!
Привет из Туркменистана
Здравствуйте, Николай. Огромное спасибо за ролик, очень полезная информация. Но скажите можно ли заставить работать этот макрос при изменении цвета ячеек через условное форматирование? У меня автоматически используется закрашивание в 3 цвета по заданным условиям, ячейки желтого цвета нужно суммировать величины указанные в них, а зеленые и красные просто подсчитывать количество ячеек каждого цвета. Но еще раз скажу все закрашивание не в ручную, а по условному форматированию и файл с общим доступом к книге для нескольких пользователей для одновременной работы. Возможно это реализовать?
Спасибо, но что же все таки делать с условным форматированием ...
Элегантно
Simply clever 🚥
Перевод - просто клёво. :)
Так какой выход, если нужно использовать раскраску форматирования? Не перекрашивать же потом всё вручную? Или писать функцию раскрашивания свою?
Николай, СУПЕР!
Событийный макрос (изменения на листе) на уровне листа вместо Ctrl+Alt+F9 разве не будет работать?
Будет, но это ещё один макрос :)
👍👍👍
Лайк-лайк-лайк!
super
Николай! Конечно благодарности мои вместе со всеми. НО когда ячейки таблицы покрашены условным форматированием, а образец покрашен копией формата цветных ячеек, - НЕ работает. Просто затонированные ячейки считает.
Добрый день Николай. Ваши уроки столько раз меня выручали. Спасибо огромное!
Если можно отправьте мне Ваш почтовый адрес. У меня просьба по поводу экселл таблицу.
Очень полезное видео! Большое спасибо!!!👍 Но у меня вопрос: что нужно сделать, чтобы пользовательская функция сохранилась в Excel и при создании новой книги с нуля (не на базе книги, где был создан модуль с пользовательской функцией) уже была среди иных функций, то есть чтобы не приходилось для каждого нового файла заново прописывать функцию)))
Все получилось спасибо.
Как можно было бы использовать данную функцию формуле =СУММЕСЛИМН, в качестве одного из условий?
Возможно ли прописать функцию, суммирующую значения из премечаний к ячейкам?
Николай, спасибо за великолепный ролик! Эх, я что-то никак не могу воспринять VBA и понимать его, не укладывается он у меня. Вот SQL, R - да, легко могу освоить, а VBA никак не откладывается( поэтому просто скопирую эти колы и буду вставлять их в модуль по необходимости
Например, я не понимаю, почему после строки dim должна быть пустая строка и только после неё for? в современных языках программирования можно и без пустых строк. Просто не понятно, когда строку пропускать, а когда - нет
Николай, спасибо вам за ваши замечательные уроки!!! Подскажите, почему у меня не работают назначенные кнопки макросов? Запускаются только с ленты.
Сам же и разобрался: была запущена запись макроса макрорекодером.
Николай Добрый День. У меня в таблице есть объединенные ячейки, то есть при подсчете количества ячеек с определенным цветом он соответственно считает объединенную ячейку как две разные, и выдает результат на 1 больше. Это можно подправить?
magnific
Николай, спасибо за много полезного и очень полезного. Но свою задачу не смог решить. подскажите как прописать формулу значение которой зависит от двух изменяющихся значений в одних ячейках. пример если А1=15 когда В1 "вакант" необходимо "график 1,2" соответственно если А1=16 когда В1 "вакант" необходимо "график 3,4", но если В1 другое значение необходимо пусто. был бы очень презнателен
VBA, конечно, очень удобный язык, так все хорошо воспринимается в нем. Жаль, что он доступен только в десктопной версии. Теперь нам всем придется учить JavaScript, чтоб то же самое кутить в веб-версии Excel.
Осталось не озвученным, есть ли возможность сделать подобное для условного форматирования и что для этого нужно?
Соглашусь, тоже в свое время очень расстроился что подобные штуки не работают с условным форматированием....
Очень сложно и, в некоторых случаях, невозможно. В VBA нет способа быстро определить цвет ячеек, залитых условным форматированием. Есть свойство Display.Interior.Color, но оно не работает в пользовательских функциях.
@@planetaexcel а функция Excel "Промежуточные.Итоги" не подойдет?
Как показывает практика, обращение к ячейкам в VBA занимает немало времени при обработке больших объёмов данных.
Поэтому, я бы ColorSample.Interior.Color записал в переменную, и обращался бы в цикле к ней.
А на случай, когда в DataRange целый столбец, можно создать новую область для анализа Set NewRange = Intersect(DataRange, DataRange.Parent.UsedRange).
Ну и если идти до конца, нужна защита от "дурака" на случай, если в ColorSample будет передана область не из одной ячейки.
Всё верно, Вадим - согласен по всем пунктам. Но если всё это показывать и нормально рассказывать в ролике, то он будет больше получаса и никто его смотреть не будет :)
И то верно)
Николай, как его можно улучшить, чтобы можно было анализировать таблицы где более 1000 строк?
Как посчитать количество в столбце строк с определенным текстовым содержанием?
Подскажите пожалуйста, что в этой формуле не верно =ЕСЛИ(И([@СТАТУС]="Оплачен";[@ПРОИЗВОДИТЕЛЬ]="ПРОДАВЕЦ1");СЧЁТЕСЛИ(Таблица2[ПРОИЗВОДИТЕЛЬ];"ПРОДАВЕЦ1"))
Задача такая: Есть столбец в таблице со статусом заказа, в другом столбце разные производители. Делаю подсчёт заказов в статусе ="Оплачен" и "Производитель1". Формула не работает. Можно ли формулу СЧЁТЕСЛИ вставлять в другие формулы как в данном примере? Или для этого подсчёта есть более простые варианты?
А есть возможность глянуть решение такой задачи?
У меня есть ячейка с оплатой проведенная или нет(цветом выделяется, если проведенная то зеленая, если нет, то красная. Назовем ее А1). И есть ячейка с % (B1).
Мне надо чтобы в ячейке B1 писались проценты только в том случае, если в А1 цвет зеленый.
Что-то ищу, но пока не нашел решения.
У меня вопрос такой: можно ли ячейку закрасить цветом, из выпадающего списка цветов: создать отдельный диапазон, например, из 5 цветов, а потом щёлкнув на ячейку, выбирать цвет из списка - и ячейка окрасилась. Ну вот надо мне вот так.
По ходу это сделать нельзя. Если можно, ну подскажите.
Спасибо! Подскажите к какому свойству Range обратиться. Мне нужно сравнить цвет ячейки с заданным (без цикла). Если цвет ячейки равен заданному то выполняется = А1+1, если нет то просто =А1. Но все дело в том что цвет ячейки меняется в зависимости от условного форматирования (заданный цвет не меняется). Пробовал как у вас Interior.Color не получается: если цвет ячейки меняется в связи с условным форматированием выдает ИСТИНА, хотя должно быть ЛОЖЬ, т.к цвет ячейки и заданный отличаются.
Очевидно, что нужно использовать условие из условного форматирования. Тогда условием отслеживания у вас будет не цвет, а значение, которое проверяется формулой условного форматирования.
@@Rice0987 ну это понятно, просто формула большая в условном форматировании. думал что можно ее не дублировать 10 раз
@@user-dt4yz4sf2l в некоторых случаях я оставляю формулу условного форматирования в соседней колонке, а условие форматирования проверяю по значению той колонки. Вариантов несколько. Можно выбрать оптимальный.
Подскажите кто-нибудь , что я сделал не так , не считает ячейки по цвету , в ячейке пишет 0 и excel не показывает ошибки в формуле? Вроде сделал всё как в видео и не один раз, но результат один в подсчетах стоит 0.
Здравствуйте. Скажите, а почему со следующим цветом не работает функция, у меня несколько цветов? С одним получилось, а другие цвета начинаешь по такому же принципу делать и вылазит ошибка в виде решетки, не пойму никак в чем дело)
А как в формуле задать функцию раскрашивания ячейки или изменения шрифта? (например: если значение 1ой ячейки x>0, то 2ая ячейка раскрашивается в красный цвет и содержимое отображается жирным курсивом)
Гуглите "условное форматирование в Excel" :)
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ() + сортировка по цвету?
Первый)
Как включить фильтры?
БОГ ЕКСЕЛЬ - ТАК И ЗАПИСАЛ У СЕБЯ ВО ВКЛАДКАХ
Название функции можно только латиницей писать?
Нет, можно и по-русски, без проблем.
у меня выдает #знач! все сделал как на видео, причем на каждую команду одна реакция #знач! Что не так?
Нашел на просторах Ютуба (см. ниже) макрос видит цвета условного форматирования
Плохо, что нет возможности подбирать нужные функции под конкретный "запрос"
Ну, и вместо трёх разноимённых функций можно сделать это опцией в виде третьего аргумента, наподобие aggregate или subtotal.
Это не к вам, а к вашим зрителям. :)
Буду очень признателен, если сможете для чайника написать код. Можно, конечно, попробовать разобраться, но на такие штуки остается только ночь, когда не знаешь, а ночью так тянет спать.... Да и наверняка еще много людей скадет Вам СПАСИБО
@@Sig4childrens весь код Николай оставил в описании. Вам нужно только добавить третью опцию, можно также в виде цифр, как в упомянутых функциях и по оператору select case расписать по номерам 1, 2, 3 по порядку те функции, которые идут одна за другой. Это не сложно, поверьте. 10 минут на выходных - это не долго. :)
@@Rice0987 да, теперь понятно! Спасибо!
@@Sig4childrens Надеюсь, получилось? Рад был помочь.😊
Пишет ошибку ИМЯ ((
Не, это, безусловно, круто. Но вопрос: зачем? А не проще ли использовать фильтр (по цвету) и функцию АГРЕГАТ?
Использовать фильтр (по цвету) можно для одного столбца. А как охватить широкий диапазон?
@@allesgute2510 честно говоря, я смутно представлять вообще для чего эта задача. Кто будет данные отвечать цветом, да ещё в нескольких столбцах, да ещё без условного форматирования, вручную? Это для чего?
@@Croatoan_PL , да я о практическом применении толком и не подумал. Просто заметил, что технически эти способы малость разнятся.
Агрегат работает с УФ?
@@Croatoan_PL Как пример: график работ (план/факт) множество данных, на ячейки ставим условное форматирование (план=факту - ячейка зеленая) по итогу формула считает сумму зеленых ячеек :)
А почему цвета ЛГБТ ???
Не работает, и ошибок не выдает.
а если у меня в ячейках не числа, а буквы, то как сделать подсчет цветных ячеек?
Спасибо!
Спасибо!!!