*Тайм-коды* *для* *удобства* *навигации* 00:44 Суммирование ячеек по цвету 02:48 Подсчет количества ячеек по цвету 03:34 Изменение функций для расчетов по цвету текста 03:55 Особенности применения функций
Отличное видео, спасибо и за готовый код. *.xlsm: файлы Excel формата XML, которые также поддерживают макросы Excel *.xlsb: файлы Excel двоичного формата (более старая версия), которые также поддерживают макросы Excel Пришлось чуть-чуть разобраться, что расширение по умолчанию *.xlsx - не сохранит макрос(код). Думаю об этом надо предупредить всех желающих, особенно первый раз использующих. *.xlsm и *.xlsb - расскажите в чём их отличия и сходства? Больше ни какие расширения макросы не поддерживают?
Спасибо! Макросы поддерживаются в 2 основных форматах: xlsm и xlsb. При этом xlsb не только поддерживает макросы, но в целом работает побыстрее и размер файла в этом формате занимает меньше места (чем xlsm и xlsx), поэтому в принципе рекомендую использовать этот формат для работы. У xlsb есть определенные недостатки, но большинство пользователей в работе их вероятно даже не заметит (более сложно восстановить файл при повреждении книги и т.д.).
Очень полезное видео! Большое спасибо!!!👍 Но у меня вопрос: что нужно сделать, чтобы пользовательская функция сохранилась в Excel и при создании новой книги с нуля (не на базе книги, где был создан модуль с пользовательской функцией) уже была среди иных функций).
Большое спасибо за ваш труд, проделанную работу, очень всё кстати, копировать не стал, а всё просто перепечатал, но было и то что вводило в ступор, это сохранение файла с данным макросом, это не желание работать экселя с макросом вообще его открывать, в настройках безопасности пришлось поковыряться, и конечно охото чтоб вы дописали данный макрос, чтоб он сам пересчитывал новые данные в таблице, чтоб не приходилось тыкать на ячейку вызывать формулу и жать интер, а чтоб он сам это делал автоматом, а так большое ещё раз спасибо.!!!!!!!!! странно первое суммы сам автоматом пересчитывает а вот количество нет, эксель самый последний.
Проблема в том, что при изменении заливки не меняется содержимое ячейки, поэтому автоматом значения не пересчитываются 🙁 Чтобы каждый раз не заходить в ячейку и не нажимать Enter для ускорения работы можно нажать F9 (или Shift + F9), Excel в этом случае пересчитает формулы в книге/листе. Но совсем без активных действий, к сожалению, не обойтись.
01:55 обратите внимание как хорошо показаны пояснения в коде после апострофа! По видео всё повторил: сумма не выдаётся - в ячейке появляется знак решётка. Было бы хорошо выкладывать готовый файл в описании.
Посчитать получилось, но можно ли сделать чтобы допустим, изначально позиции белые, но если кто то цвет меняет, то позиции считаются. Менялись автоматом. Типо 3 зеленых ячейки, если поменял цвет, то две и так далее. Пропуская пустые ячейки? Очень нужно)
В этом аспекте как раз есть проблема, при изменении заливки ячейки не меняется содержимое ячейки, поэтому Excel автоматически и не пересчитывает формулы. К сожалению, готового решения здесь нет, есть только меры помогающие обойти проблему. В саму функцию мы уже добавили команду "Application.Volatile True" (чтобы функция пересчитывалась когда меняется какое-либо значение в ячейке на листе). Либо принудительно для пересчета нажимать F9/Shift + F9.
Можно каким-либо образом сделать так, чтобы =суммцвет работал на каждом новом документе exel, не создавая через VBA по новому модуль в текстом. что-то вроде надстройки, макроса, который можно перенести на другой комп
Чтобы посчитать сумму/счет не по цвету ячейки, а по цвету шрифта, то в коде функции нужно свойство Interior (это как раз заливка ячейки) поменять на Font (это цвет шрифта). Т.е. грубо говоря везде использовать "cell.Font.Color" вместо "cell.Interior.Color".
Подскажите пожалуйста, для LibreOffice Calc, что необходимо изменить в 8 и 20 строке макроса. Выдает "ошибка времени выполнения Basic. 449 Аргумент является обязательным"
Видео действительно полезное. Спасибо автору. Но как быть, если нужно посчитать сумму ячеек, залитых определённым цветом, а там вместо цифр стоят буквы, которые подразумевают некие цифры. Например в графике: стоит Д - значит 13 часов, Н - 15 часов
Думаю это можно будет сделать, но нужно будет дополнительно в пользовательской функции прописывать обработку вот таких букв. Т.е. прямо в коде прописать, что если значение ячейки рано Д, то прибавляем 13 и т.д.
Зависит от деталей вашей задачи, могут разные варианты подойти. Можно просто написать формулу "=СЧЁТЦВЕТ (диапазон; ячейка_с_заливкой) + СЧЁТЦВЕТ (диапазон; прозрачная_ячейка)", в этом случае функция сначала посчитает ячейки с заливкой, потом посчитает ячейки без заливки, а затем сложим полученный результат. Еще как вариант изменить непосредственно код функции, чтобы функция считала по двум цветам - выделенному и прозрачному цветам.
Добрый день. как это сделать в гугл таблицах? из столбца где есть разные данные есть ячейки с Итого по каждому элементу. вот как просуммировать значения в этих ячейках итого? каждая такая ячейка итого выделена цветом? Не нашел в гугл таблицах где можно свою формулу сделать
Как это сделать в гугл таблицах, к сожалению, не смогу подсказать. В принципе пользовательские функции (как из примера в видео) как класс функций в гугл таблицах есть, они пишутся с помощью скриптов, но какой конкретно нужен код не сориентирую.
подскажи что я сделал не правильно , повторил как на видео все , но когда вожу формулу то получаю #ИМЯ?. могу прислать и сам Excel документ что бы вы посмотрели
Обычно такая ошибка возникает, когда в книге отключены макросы и программа не понимает что именно за функция написана в формуле. Попробуйте включить их, если и так не получится, то дальше посмотрим что делать.
Касательно создания модуля, то в самом Visual Basic (Alt + F11 для открытия редактора) есть окно Project Explorer (оно обычно в левом верхнем углу), щелкаете правой кнопкой мыши по окну и выбираете Insert -> Module. Проблем с Excel 2007 по идее быть не должно. Вы в каком формате сохраняете книгу (если не xlsb или xlsm, то работать не будет)? Файл из примера у вас работает?
как сделать так, чтобы макрос считал количество ячеек по цвету и значению? например мне нужно чтобы выводил количество "Б" в зеленых ячейках (чтобы зеленые ячейки без "Б" не считались? )
Попробуйте вот так, возможно поможет: Public Function СЧЁТЦВЕТ(MyRange As Range, MyCell As Range, MyText As String) As Double Dim Count As Double Count = 0 Application.Volatile True For Each cell In MyRange If cell.Interior.Color = MyCell.Interior.Color And InStr(cell.Value, MyText) 0 Then Count = Count + 1 End If Next СЧЁТЦВЕТ = Count End Function
приветствую. а как сделать чтоб при выделении новой ячейки цветом, значение расчета автоматически обновлялось, а то обновляется только при изменении числа в ячейки, а при добавлении новой приходится формулу в ячейке обновлять. Заранее огромное спасибо. подскажешь = лайк и подписка ))
К сожалению, полностью работающего решения нет... Excel не запускает пересчет формул если не меняется содержимое ячейки. А изменение заливки как раз не подходит под это условие, поэтому приходиться изобретать другие способы как то этого обойти. Самый простой - нажимать F9 (или Shift+F9) для принудительного обновления формул. Еще можно зайти в режим редактирования любой ячейки и нажать Enter (чтобы Excel подумал что поменялось содержимое ячейки). Но в любом случае нужны будут действия со стороны пользователя.
Бывает, что при работе с таблицей применяются разные фильтры и на каждом этапе подходящие ячейки закрашивают одним цветом, после чего как раз есть необходимость их просуммировать/посчитать 🙂 Но соглашусь, с условным форматированием тоже очень хочется так делать, но пока увы(
Спасибо за все, все отлично работает. Но у меня одна проблема - когда закрою excell функции СЧЕТЦВЕТ на ячейках показывает #ИМЯ?. Потом я на стоке формулы слово счетцвет заново пищу большими буквами тогда функция заново работает. Пожалуйста если есть возможность дайте совет.
Эта ошибка скорее всего возникает из-за настройки безопасности макросов. При открытии книги макросы могут быть по умолчанию отключены (в этом случае как раз не работают пользовательские функции и вместо них мы видим #ИМЯ?), поэтому каждый раз при открытии Excel они не работают. Как только вы разрешите использование макросов в настройках безопасности (это можно сделать через Параметры -> Центр управления безопасностью), то проблема должна исчезнуть.
Хм... заинтриговали вопросом 🤔 Если не сложно, пришлите мне на почту скрин как конкретно выглядит проблема и сам файл (если есть возможность), я смогу посмотреть.
Как вариант можно попробовать просуммировать весь диапазон через обычный СУММ и вычесть СУММЦВЕТ по диапазону для любой не закрашенной ячейки, тогда в результате получится сумма всех ячеек диапазона, у которых есть цвет. Либо отдельную пользовательскую функцию написать.
Добрый день, воспользовался, все работает, но теперь после каждого входа даже без внесения каких-либо изменений спрашивает "сохранить ли файл" - нажимаешь сохранить вылетает уведомление "будьте внимательны! В документе могу быть персональные данные, которые невозможно удалить с помощью инспектора документов" Как убрать эти всплывающие окна и постоянный запрос на сохранение?
Попробуйте зайти в панели вкладок Файл -> Параметры -> Центр управления безопасностью, далее нажмите кнопку Параметры центра управления безопасностью и затем снимите галочку напротив поля Удалять персональные данные из свойств файла при сохранении, по идее должно помочь.
Здравствуйте. Помогите пожалуйста, как можно в Excel реализовать продвинутую формулу через ИНДЕКС и ПОИСКПОЗ с многократно повторяющейся формулой на 100 строк вниз, и которая при расчетах динамически ссылается на n=20 следующих строк вниз (n- задается в отдельной ячейке)? Привожу пример такой формулы набранной вручную: =(ABS(G3-F3)+ABS(G3-F4)+ABS(G3-F5)+ABS(G3-F6)+ABS(G3-F7)+ABS(G3-F8)+ABS(G3-F9)+ABS(G3-F10)+ABS(G3-F11)+ABS(G3-F12)+ABS(G3-F13)+ABS(G3-F14)+ABS(G3-F15)+ABS(G3-F16)+ABS(G3-F17)+ABS(G3-F18)+ABS(G3-F19)+ABS(G3-F20)+ABS(G3-F21)+ABS(G3-F22))/20
Что-то подобное можно сделать с помощью динамического диапазона, как раз через ИНДЕКС и ПОИСКПОЗ, либо еще можно через СМЕЩ попробовать. Но в любом случае таким образом получится создать только динамический диапазон, который будет зависеть от заданного n, но сделать динамическую формулу, т.е. прописать внутри формулы условно n других формул как у Вас в примере, не получится. Как вариант, можно, например, создать дополнительный столбец, где будет считаться разность столбцов (G3-F3, G3-F4, G3-F5 и т.д.) и уже для этого нового столбца применить динамический диапазон по которому посчитать сумму. В общем тут многое зависит конкретно от Вашей задачи и условий 🙂
А как подсчитать сумму ячеек, если сумма в них меняется в зависимости от результата другой ячейки? Ячейка имеет такую формулу: "=ЕСЛИ(H2="выигрыш";G2-F2;"")&ЕСЛИ(H2="проигрыш";F2;"")&ЕСЛИ(H2="в игре";F2;"")&ЕСЛИ(H2="выкуп";"выкуплено";"")&ЕСЛИ(H2="возврат";F2;"")"
Несколько вариантов в голову приходят: 1) не включен пересчет формул, решается нажатием F9/Shift + F9 2) не совпадает цвет ячеек, они могут выглядеть визуально одинаково, но по RGB палитре отличаются 3) формат отображения ячеек, в ячейке отображается 0, но в действительности там не 0 (например, скрыты знаки после запятой и т.п.) Каждый вариант не слишком вероятен, но все же есть шанс, что поможет 🙂
Идите в панели вкладок в Файл -> Параметры -> Настроить ленту и далее поставьте галочку напротив поля "Разработчик" в правой части окна настройки ленты
Разделитель в формулах задается не через Excel, а через системные настройки операционной системы. Нужно зайти в Панель управления -> Язык и региональные стандарты -> Дополнительные параметры и в поле *Разделитель элементов списка* вместо ";" поставить ",", при этом также надо будет поменять *Разделитель целой и дробной части* с "," на ".", чтобы не было проблем с десятичными числами (так как к примеру, "5,6" это уже не "5 целых 6 десятых", а перечисление чисел 5 и 6). В самом Excel в Файл -> Параметры -> Дополнительно нужно поставить галочку в поле *Использовать системные разделители* , чтобы программа как раз забирала введенные разделители из настроек.
@@TutorExcelRu как уже было сказано, чтобы обновить значения на листе надо нажимать кнопку ctrl+alt+F9. А можно создать "кнопку" на листе для этой функции в модуле? Чтобы не нажимать постоянно ctrl+alt+F9, а просто создать кнопку щелчок на листе. Нажал ее и данные обновились
Тут 2 варианта можно попробовать. В панели вкладок в принципе уже есть такая кнопка в разделе с формулами (возможно придётся ее поискать, не всегда сразу можно заметить). Также можно добавить на лист обычную кнопку, задать по ее нажатию запуск макроса, в котором уже прописать выполнение обновления книги.
@@TutorExcelRu да, вы были правы, я эту кнопку на панели вкладок с формулами так и не нашел, хотя долго и внимательно искал. Сделал вторым способом: создал кнопку щелчок и в Модуле прописал "application.calculateFull". И вуаля, все заработало. Мерси
@@TutorExcelRu в том то и дело что включены. Пробовал установить VBA пакет и пробовал встроенную функцию СЧЁТЗАЛИВКА, сперва работала, потом перестала.
С условным форматированием, к сожалению, все не так просто... Вытащить и проверить условие на цвет ячейки с условным форматированием через VBA уже не получится, так как такой возможности в VBA нет, т.е. функции работают только для ячеек с классическим способом заливки.
Вероятно ячейка с цветом не закреплена в формуле, поэтому при протягивании формула сбивается. Попробуйте закрепить ссылку на ячейку, условно говоря вместо записи вида A4 в формуле прописать $A$4 (т.е. добавить $ к записи строк/столбов), тогда ссылка на ячейку во всех формулах будет одна и та же.
@@TutorExcelRu значке доллара ставлю =СЧЁТЦВЕТ($C$25:$AG$25;$T1), а когда протягиваю на следующую строку то получается вот так =СЧЁТЦВЕТ($C$25:$AG$25;$T2) а по идеи должно быть С26 и Т1
Не страдайте ерундой, гораздо проще и правильнее сразу указывать машиночитаемые признаки в дополнительном столбце. А то потом может понадобится МАКСЦВЕТ, ЕСЛИЦВЕТ, и так далее создавать всю библиотеку функций под неправильный способ обработки данных.
Эти функции на самом деле некий аналог популярных СУММ и СЧЁТ, которые в Excel используется как разные функции, поэтому и в этом примере с подсчетом по цвету они рассмотрены также отдельно. Но, конечно, можно попытаться написать универсальную функцию, где в параметре задавать какую именно операцию нужно сделать 🙂
Подсчет ячеек идёт с помощью пользовательских функций, поэтому нужно чтобы в файле можно было хранить код на VBA. Пересохраните файл в формате .xlsb или .xlsm, там есть поддержка макросов.
@@TutorExcelRu а таким образом выбрал сохранить с макросами. И вроде не было таких вариантов для сохранения. Я завтра ещё посмотрю, сейчас уже отдыхаю в балке. (на севере работаю)
*Тайм-коды* *для* *удобства* *навигации*
00:44 Суммирование ячеек по цвету
02:48 Подсчет количества ячеек по цвету
03:34 Изменение функций для расчетов по цвету текста
03:55 Особенности применения функций
Огромное спасибо!!!!!! ВЫ - ЛУЧШИЙ!!!
Спасибо большое, все работает! Очень полезная формула!
Ты крут.
Спасибище))
Скачать файл с примером не получается - сайт заблокирован. Добавьте пожалуйста новую ссылку готового примера
спасибо, подписался. Жизнь заставила открыть эксель
👍
СПАСИБО, все работает. КЛАСС
Благодарю - получилось! Редко когда копируя код сразу все работает, хе-хе
Рад помочь 🙂
Дякую вам! Дуже допомогло!
Отличное видео, спасибо и за готовый код.
*.xlsm: файлы Excel формата XML, которые также поддерживают макросы Excel
*.xlsb: файлы Excel двоичного формата (более старая версия), которые также поддерживают макросы Excel
Пришлось чуть-чуть разобраться, что расширение по умолчанию *.xlsx - не сохранит макрос(код).
Думаю об этом надо предупредить всех желающих, особенно первый раз использующих.
*.xlsm и *.xlsb - расскажите в чём их отличия и сходства?
Больше ни какие расширения макросы не поддерживают?
Спасибо!
Макросы поддерживаются в 2 основных форматах: xlsm и xlsb.
При этом xlsb не только поддерживает макросы, но в целом работает побыстрее и размер файла в этом формате занимает меньше места (чем xlsm и xlsx), поэтому в принципе рекомендую использовать этот формат для работы.
У xlsb есть определенные недостатки, но большинство пользователей в работе их вероятно даже не заметит (более сложно восстановить файл при повреждении книги и т.д.).
Спасибо, работает как надо)
🔥🔥🔥🔥 как хорошо, что я сюда зашёл!!!
👍
Благодарю, очень помогли
о, супер, надо только обновить, чтобы данные переучивались при изменении ячеек (цвет, число)
Очень полезное видео! Большое спасибо!!!👍 Но у меня вопрос: что нужно сделать, чтобы пользовательская функция сохранилась в Excel и при создании новой книги с нуля (не на базе книги, где был создан модуль с пользовательской функцией) уже была среди иных функций).
Большое спасибо за ваш труд, проделанную работу, очень всё кстати, копировать не стал,
а всё просто перепечатал, но было и то что вводило в ступор, это сохранение файла с данным макросом,
это не желание работать экселя с макросом вообще его открывать, в настройках безопасности пришлось
поковыряться, и конечно охото чтоб вы дописали данный макрос, чтоб он сам пересчитывал новые данные
в таблице, чтоб не приходилось тыкать на ячейку вызывать формулу и жать интер, а чтоб он сам это делал
автоматом, а так большое ещё раз спасибо.!!!!!!!!! странно первое суммы сам автоматом пересчитывает а
вот количество нет, эксель самый последний.
Проблема в том, что при изменении заливки не меняется содержимое ячейки, поэтому автоматом значения не пересчитываются 🙁 Чтобы каждый раз не заходить в ячейку и не нажимать Enter для ускорения работы можно нажать F9 (или Shift + F9), Excel в этом случае пересчитает формулы в книге/листе.
Но совсем без активных действий, к сожалению, не обойтись.
Очень простой макрос, но очень полезный
спасибо, прикольно
01:55 обратите внимание как хорошо показаны пояснения в коде после апострофа!
По видео всё повторил: сумма не выдаётся - в ячейке появляется знак решётка.
Было бы хорошо выкладывать готовый файл в описании.
Да, спасибо, добавил ссылку с файлом в описание.
Продублирую и здесь: bit.ly/3HSifkg 🙂
Подскажите, а если нужно посчитать по цвету и с определенным текстом
Привет. Я не понял, а что МОДУЛЬ сохраняется только на отдельный файл? А не на весь Excel?
Есть возможность в гугл таблицах то же самое сделать и поделиться файликом? Очень нужно, а вот как в гугле сделать не знаю
Видео огонь, формула, что надо, но устарело все. Сайт заблокирован, а если вручную ввожу в гугл таблицах, скрипты не распознает,
Посчитать получилось, но можно ли сделать чтобы допустим, изначально позиции белые, но если кто то цвет меняет, то позиции считаются. Менялись автоматом. Типо 3 зеленых ячейки, если поменял цвет, то две и так далее. Пропуская пустые ячейки?
Очень нужно)
В этом аспекте как раз есть проблема, при изменении заливки ячейки не меняется содержимое ячейки, поэтому Excel автоматически и не пересчитывает формулы.
К сожалению, готового решения здесь нет, есть только меры помогающие обойти проблему.
В саму функцию мы уже добавили команду "Application.Volatile True" (чтобы функция пересчитывалась когда меняется какое-либо значение в ячейке на листе). Либо принудительно для пересчета нажимать F9/Shift + F9.
Можно каким-либо образом сделать так, чтобы =суммцвет работал на каждом новом документе exel, не создавая через VBA по новому модуль в текстом. что-то вроде надстройки, макроса, который можно перенести на другой комп
Да, можно, через личную книгу макросов.
Вот здесь у Николая Павлова хорошо расписано 🙂
www.planetaexcel.ru/techniques/3/5201/
как на английском будет СУММЦВЕТ???
СУММШРИФТ как использовать? второго аргумента пример шрифта как создать?
Чтобы посчитать сумму/счет не по цвету ячейки, а по цвету шрифта, то в коде функции нужно свойство Interior (это как раз заливка ячейки) поменять на Font (это цвет шрифта).
Т.е. грубо говоря везде использовать "cell.Font.Color" вместо "cell.Interior.Color".
Подскажите пожалуйста, для LibreOffice Calc, что необходимо изменить в 8 и 20 строке макроса. Выдает "ошибка времени выполнения Basic. 449 Аргумент является обязательным"
К сожалению, для LibreOffice не смогу подсказать, не работал с ним ☹️
Видео действительно полезное. Спасибо автору. Но как быть, если нужно посчитать сумму ячеек, залитых определённым цветом, а там вместо цифр стоят буквы, которые подразумевают некие цифры. Например в графике: стоит Д - значит 13 часов, Н - 15 часов
Думаю это можно будет сделать, но нужно будет дополнительно в пользовательской функции прописывать обработку вот таких букв.
Т.е. прямо в коде прописать, что если значение ячейки рано Д, то прибавляем 13 и т.д.
@@TutorExcelRu пришлось прибегнуть к хитрости и заставить chatGPT написать макрос для выполнения вышеуказанных вычислений)))
Подскажите пожалуйста, как сделать чтобы вместе с выделенными цветом ячейками считались не выделенные ячейки (прозрачные)?
Зависит от деталей вашей задачи, могут разные варианты подойти.
Можно просто написать формулу "=СЧЁТЦВЕТ (диапазон; ячейка_с_заливкой) + СЧЁТЦВЕТ (диапазон; прозрачная_ячейка)", в этом случае функция сначала посчитает ячейки с заливкой, потом посчитает ячейки без заливки, а затем сложим полученный результат.
Еще как вариант изменить непосредственно код функции, чтобы функция считала по двум цветам - выделенному и прозрачному цветам.
Сайт заблокирован хостинг-провайдером
У меня не появилась функция , с чем может бить связано?
Добрый день. как это сделать в гугл таблицах? из столбца где есть разные данные есть ячейки с Итого по каждому элементу. вот как просуммировать значения в этих ячейках итого? каждая такая ячейка итого выделена цветом?
Не нашел в гугл таблицах где можно свою формулу сделать
Как это сделать в гугл таблицах, к сожалению, не смогу подсказать.
В принципе пользовательские функции (как из примера в видео) как класс функций в гугл таблицах есть, они пишутся с помощью скриптов, но какой конкретно нужен код не сориентирую.
подскажи что я сделал не правильно , повторил как на видео все , но когда вожу формулу то получаю #ИМЯ?. могу прислать и сам Excel документ что бы вы посмотрели
Обычно такая ошибка возникает, когда в книге отключены макросы и программа не понимает что именно за функция написана в формуле.
Попробуйте включить их, если и так не получится, то дальше посмотрим что делать.
может не получаться из за старой версии ексель? у меня 2007
Касательно создания модуля, то в самом Visual Basic (Alt + F11 для открытия редактора) есть окно Project Explorer (оно обычно в левом верхнем углу), щелкаете правой кнопкой мыши по окну и выбираете Insert -> Module.
Проблем с Excel 2007 по идее быть не должно.
Вы в каком формате сохраняете книгу (если не xlsb или xlsm, то работать не будет)?
Файл из примера у вас работает?
@@TutorExcelRu дружище. Нужна помощь небольшая. Не даром. Куда писать ?
как сделать так, чтобы макрос считал количество ячеек по цвету и значению? например мне нужно чтобы выводил количество "Б" в зеленых ячейках (чтобы зеленые ячейки без "Б" не считались?
)
Навскидку можно в функцию добавить аргумент в виде текстовой переменной, а далее уже в теле функции прописать сравнение.
Попробуйте вот так, возможно поможет:
Public Function СЧЁТЦВЕТ(MyRange As Range, MyCell As Range, MyText As String) As Double
Dim Count As Double
Count = 0
Application.Volatile True
For Each cell In MyRange
If cell.Interior.Color = MyCell.Interior.Color And InStr(cell.Value, MyText) 0 Then
Count = Count + 1
End If
Next
СЧЁТЦВЕТ = Count
End Function
приветствую. а как сделать чтоб при выделении новой ячейки цветом, значение расчета автоматически обновлялось, а то обновляется только при изменении числа в ячейки, а при добавлении новой приходится формулу в ячейке обновлять. Заранее огромное спасибо. подскажешь = лайк и подписка ))
К сожалению, полностью работающего решения нет...
Excel не запускает пересчет формул если не меняется содержимое ячейки. А изменение заливки как раз не подходит под это условие, поэтому приходиться изобретать другие способы как то этого обойти.
Самый простой - нажимать F9 (или Shift+F9) для принудительного обновления формул.
Еще можно зайти в режим редактирования любой ячейки и нажать Enter (чтобы Excel подумал что поменялось содержимое ячейки).
Но в любом случае нужны будут действия со стороны пользователя.
@@TutorExcelRu а с умной таблицей как быть, если она постоянно пополняется?
Да, вот только разве в ручную кто то делает распределение цвета. Вся фишка как по мне в уставном форматировании как раз )
Бывает, что при работе с таблицей применяются разные фильтры и на каждом этапе подходящие ячейки закрашивают одним цветом, после чего как раз есть необходимость их просуммировать/посчитать 🙂
Но соглашусь, с условным форматированием тоже очень хочется так делать, но пока увы(
Спасибо за все, все отлично работает. Но у меня одна проблема - когда закрою excell функции СЧЕТЦВЕТ на ячейках показывает #ИМЯ?. Потом я на стоке формулы слово счетцвет заново пищу большими буквами тогда функция заново работает.
Пожалуйста если есть возможность дайте совет.
Эта ошибка скорее всего возникает из-за настройки безопасности макросов.
При открытии книги макросы могут быть по умолчанию отключены (в этом случае как раз не работают пользовательские функции и вместо них мы видим #ИМЯ?), поэтому каждый раз при открытии Excel они не работают.
Как только вы разрешите использование макросов в настройках безопасности (это можно сделать через Параметры -> Центр управления безопасностью), то проблема должна исчезнуть.
Спасибо за Ваш ответ. Попробовал все 5 вариантов которой там было предложено. Но результата нет. Все равно спасибо Вам
Хм... заинтриговали вопросом 🤔
Если не сложно, пришлите мне на почту скрин как конкретно выглядит проблема и сам файл (если есть возможность), я смогу посмотреть.
Спасибо за Ваш ответ. Отправил таблицу почтовый адрес.
Спасибо Иван. Я получил таблицу отправленный обратно. Благодарю, все работает отлично.
Как считать все цвета в диапазоне? Если он разные.
Как вариант можно попробовать просуммировать весь диапазон через обычный СУММ и вычесть СУММЦВЕТ по диапазону для любой не закрашенной ячейки, тогда в результате получится сумма всех ячеек диапазона, у которых есть цвет.
Либо отдельную пользовательскую функцию написать.
как подсчитать если две ячейки объединены в одну? значение дублируется!
Добрый день, воспользовался, все работает, но теперь после каждого входа даже без внесения каких-либо изменений спрашивает "сохранить ли файл" - нажимаешь сохранить вылетает уведомление "будьте внимательны! В документе могу быть персональные данные, которые невозможно удалить с помощью инспектора документов" Как убрать эти всплывающие окна и постоянный запрос на сохранение?
Попробуйте зайти в панели вкладок Файл -> Параметры -> Центр управления безопасностью, далее нажмите кнопку Параметры центра управления безопасностью и затем снимите галочку напротив поля Удалять персональные данные из свойств файла при сохранении, по идее должно помочь.
@@TutorExcelRu спасибо, помогло
Отлично 🙂
Здравствуйте. Помогите пожалуйста, как можно в Excel реализовать продвинутую формулу через ИНДЕКС и ПОИСКПОЗ с многократно повторяющейся формулой на 100 строк вниз, и которая при расчетах динамически ссылается на n=20 следующих строк вниз (n- задается в отдельной ячейке)?
Привожу пример такой формулы набранной вручную:
=(ABS(G3-F3)+ABS(G3-F4)+ABS(G3-F5)+ABS(G3-F6)+ABS(G3-F7)+ABS(G3-F8)+ABS(G3-F9)+ABS(G3-F10)+ABS(G3-F11)+ABS(G3-F12)+ABS(G3-F13)+ABS(G3-F14)+ABS(G3-F15)+ABS(G3-F16)+ABS(G3-F17)+ABS(G3-F18)+ABS(G3-F19)+ABS(G3-F20)+ABS(G3-F21)+ABS(G3-F22))/20
Что-то подобное можно сделать с помощью динамического диапазона, как раз через ИНДЕКС и ПОИСКПОЗ, либо еще можно через СМЕЩ попробовать.
Но в любом случае таким образом получится создать только динамический диапазон, который будет зависеть от заданного n, но сделать динамическую формулу, т.е. прописать внутри формулы условно n других формул как у Вас в примере, не получится.
Как вариант, можно, например, создать дополнительный столбец, где будет считаться разность столбцов (G3-F3, G3-F4, G3-F5 и т.д.) и уже для этого нового столбца применить динамический диапазон по которому посчитать сумму.
В общем тут многое зависит конкретно от Вашей задачи и условий 🙂
@@TutorExcelRu =СРОТКЛ(СМЕЩ(M7;0;0;N$3;1)).
Отлично 👍
А как подсчитать сумму ячеек, если сумма в них меняется в зависимости от результата другой ячейки?
Ячейка имеет такую формулу: "=ЕСЛИ(H2="выигрыш";G2-F2;"")&ЕСЛИ(H2="проигрыш";F2;"")&ЕСЛИ(H2="в игре";F2;"")&ЕСЛИ(H2="выкуп";"выкуплено";"")&ЕСЛИ(H2="возврат";F2;"")"
при подсчёте цветов постоянно пишет 0, что делать в этом случае?
А цвет ячейки именно с помощью заливки задаётся?
Условное форматирование для определения цветов не используете?
@@TutorExcelRu да, с помощью заливки. условное форматирование не использую.
Несколько вариантов в голову приходят:
1) не включен пересчет формул, решается нажатием F9/Shift + F9
2) не совпадает цвет ячеек, они могут выглядеть визуально одинаково, но по RGB палитре отличаются
3) формат отображения ячеек, в ячейке отображается 0, но в действительности там не 0 (например, скрыты знаки после запятой и т.п.)
Каждый вариант не слишком вероятен, но все же есть шанс, что поможет 🙂
Формула очень полезная, но у меня не чего не получилось 🙈
Что конкретно у вас не получилось?
Поделитесь, постараюсь помочь 🙂
У меня нет вкладки "Разработчик" что делать?
Идите в панели вкладок в Файл -> Параметры -> Настроить ленту и далее поставьте галочку напротив поля "Разработчик" в правой части окна настройки ленты
Как сделать, чтобы в самой Функции в скобках между диапазоном и цветом ячейки было через запятую "," а не через точка с запятой ";"?
Разделитель в формулах задается не через Excel, а через системные настройки операционной системы.
Нужно зайти в Панель управления -> Язык и региональные стандарты -> Дополнительные параметры и в поле *Разделитель элементов списка* вместо ";" поставить ",", при этом также надо будет поменять *Разделитель целой и дробной части* с "," на ".", чтобы не было проблем с десятичными числами (так как к примеру, "5,6" это уже не "5 целых 6 десятых", а перечисление чисел 5 и 6).
В самом Excel в Файл -> Параметры -> Дополнительно нужно поставить галочку в поле *Использовать системные разделители* , чтобы программа как раз забирала введенные разделители из настроек.
@@TutorExcelRu а вот он что. Спс за ликбез
@@TutorExcelRu как уже было сказано, чтобы обновить значения на листе надо нажимать кнопку ctrl+alt+F9. А можно создать "кнопку" на листе для этой функции в модуле? Чтобы не нажимать постоянно ctrl+alt+F9, а просто создать кнопку щелчок на листе. Нажал ее и данные обновились
Тут 2 варианта можно попробовать.
В панели вкладок в принципе уже есть такая кнопка в разделе с формулами (возможно придётся ее поискать, не всегда сразу можно заметить).
Также можно добавить на лист обычную кнопку, задать по ее нажатию запуск макроса, в котором уже прописать выполнение обновления книги.
@@TutorExcelRu да, вы были правы, я эту кнопку на панели вкладок с формулами так и не нашел, хотя долго и внимательно искал.
Сделал вторым способом: создал кнопку щелчок и в Модуле прописал "application.calculateFull". И вуаля, все заработало. Мерси
К сожалению не работает:
Для первой формулы - #ИМЯ?
Для второй - #ЗНАЧ!
Проверьте, что в книге обязательно включены макросы, если они отключены то как раз могут возникать подобные ошибки.
@@TutorExcelRu в том то и дело что включены.
Пробовал установить VBA пакет и пробовал встроенную функцию СЧЁТЗАЛИВКА, сперва работала, потом перестала.
А мой исходный файл из примера у Вас работает или тоже выдает ошибку?
@@TutorExcelRu тоже выдаёт ошибку
@@TutorExcelRu в моей работе это самые удобные функции , но никак не могу добиться , чтоб заработали.
почему нельзя это же сделать в условным форматированием
С условным форматированием, к сожалению, все не так просто...
Вытащить и проверить условие на цвет ячейки с условным форматированием через VBA уже не получится, так как такой возможности в VBA нет, т.е. функции работают только для ячеек с классическим способом заливки.
@@TutorExcelRu спасибо большое, и еще один вопрос , как протянуть формулу если строки идут друг за другом и ячейка с цветом всегда одна наверху
когда я протягиваю формулу у меня она сбивается и пишет ошибка
Вероятно ячейка с цветом не закреплена в формуле, поэтому при протягивании формула сбивается.
Попробуйте закрепить ссылку на ячейку, условно говоря вместо записи вида A4 в формуле прописать $A$4 (т.е. добавить $ к записи строк/столбов), тогда ссылка на ячейку во всех формулах будет одна и та же.
@@TutorExcelRu значке доллара ставлю =СЧЁТЦВЕТ($C$25:$AG$25;$T1), а когда протягиваю на следующую строку то получается вот так =СЧЁТЦВЕТ($C$25:$AG$25;$T2) а по идеи должно быть С26 и Т1
не работает в 2016
Проверьте, что у вас обязательно включены макросы, без них как раз может не работать.
@@TutorExcelRu всё было включено.
Спасибо, понятного ничего
Не страдайте ерундой, гораздо проще и правильнее сразу указывать машиночитаемые признаки в дополнительном столбце. А то потом может понадобится МАКСЦВЕТ, ЕСЛИЦВЕТ, и так далее создавать всю библиотеку функций под неправильный способ обработки данных.
Эти функции на самом деле некий аналог популярных СУММ и СЧЁТ, которые в Excel используется как разные функции, поэтому и в этом примере с подсчетом по цвету они рассмотрены также отдельно.
Но, конечно, можно попытаться написать универсальную функцию, где в параметре задавать какую именно операцию нужно сделать 🙂
Пишет что не возможно сохраните без поддержки макросов.
Что это значит?
Подсчет ячеек идёт с помощью пользовательских функций, поэтому нужно чтобы в файле можно было хранить код на VBA.
Пересохраните файл в формате .xlsb или .xlsm, там есть поддержка макросов.
@@TutorExcelRu спасибо. А как это сделать?
Во вкладке Файл выберете команду Сохранить как (либо нажмите F12), а далее в поле Тип файла выберете соответствующий формат (xlsb или xlsm).
@@TutorExcelRu а таким образом выбрал сохранить с макросами.
И вроде не было таких вариантов для сохранения. Я завтра ещё посмотрю, сейчас уже отдыхаю в балке. (на севере работаю)
@@TutorExcelRu нет таких форматов. Это что значит, я не смогу так сделать?