*Тайм-коды* *для* *удобства* *навигации* 00:44 Суммирование ячеек по цвету 02:48 Подсчет количества ячеек по цвету 03:34 Изменение функций для расчетов по цвету текста 03:55 Особенности применения функций

Спасибо большое, все работает! Очень полезная формула!

СПАСИБО, все работает. КЛАСС

Дякую вам! Дуже допомогло!

Спасибо, работает как надо)

Благодарю, очень помогли

Подскажите, а если нужно посчитать по цвету и с определенным текстом

Скачать файл с примером не получается - сайт заблокирован. Добавьте пожалуйста новую ссылку готового примера

Благодарю - получилось! Редко когда копируя код сразу все работает, хе-хе

@TutorExcelRu

2 жыл бұрын

Рад помочь 🙂

Очень полезное видео! Большое спасибо!!!👍 Но у меня вопрос: что нужно сделать, чтобы пользовательская функция сохранилась в Excel и при создании новой книги с нуля (не на базе книги, где был создан модуль с пользовательской функцией) уже была среди иных функций).

спасибо, подписался. Жизнь заставила открыть эксель

@TutorExcelRu

3 жыл бұрын

👍

спасибо, прикольно

Есть возможность в гугл таблицах то же самое сделать и поделиться файликом? Очень нужно, а вот как в гугле сделать не знаю

Привет. Я не понял, а что МОДУЛЬ сохраняется только на отдельный файл? А не на весь Excel?

Подскажите пожалуйста, для LibreOffice Calc, что необходимо изменить в 8 и 20 строке макроса. Выдает "ошибка времени выполнения Basic. 449 Аргумент является обязательным"

@TutorExcelRu

Жыл бұрын

К сожалению, для LibreOffice не смогу подсказать, не работал с ним ☹️

Очень простой макрос, но очень полезный

Отличное видео, спасибо и за готовый код. *.xlsm: файлы Excel формата XML, которые также поддерживают макросы Excel *.xlsb: файлы Excel двоичного формата (более старая версия), которые также поддерживают макросы Excel Пришлось чуть-чуть разобраться, что расширение по умолчанию *.xlsx - не сохранит макрос(код). Думаю об этом надо предупредить всех желающих, особенно первый раз использующих. *.xlsm и *.xlsb - расскажите в чём их отличия и сходства? Больше ни какие расширения макросы не поддерживают?

@TutorExcelRu

3 жыл бұрын

Спасибо! Макросы поддерживаются в 2 основных форматах: xlsm и xlsb. При этом xlsb не только поддерживает макросы, но в целом работает побыстрее и размер файла в этом формате занимает меньше места (чем xlsm и xlsx), поэтому в принципе рекомендую использовать этот формат для работы. У xlsb есть определенные недостатки, но большинство пользователей в работе их вероятно даже не заметит (более сложно восстановить файл при повреждении книги и т.д.).

о, супер, надо только обновить, чтобы данные переучивались при изменении ячеек (цвет, число)

Посчитать получилось, но можно ли сделать чтобы допустим, изначально позиции белые, но если кто то цвет меняет, то позиции считаются. Менялись автоматом. Типо 3 зеленых ячейки, если поменял цвет, то две и так далее. Пропуская пустые ячейки? Очень нужно)

@TutorExcelRu

3 жыл бұрын

В этом аспекте как раз есть проблема, при изменении заливки ячейки не меняется содержимое ячейки, поэтому Excel автоматически и не пересчитывает формулы. К сожалению, готового решения здесь нет, есть только меры помогающие обойти проблему. В саму функцию мы уже добавили команду "Application.Volatile True" (чтобы функция пересчитывалась когда меняется какое-либо значение в ячейке на листе). Либо принудительно для пересчета нажимать F9/Shift + F9.

Можно каким-либо образом сделать так, чтобы =суммцвет работал на каждом новом документе exel, не создавая через VBA по новому модуль в текстом. что-то вроде надстройки, макроса, который можно перенести на другой комп

@TutorExcelRu

2 жыл бұрын

Да, можно, через личную книгу макросов. Вот здесь у Николая Павлова хорошо расписано 🙂 www.planetaexcel.ru/techniques/3/5201/

🔥🔥🔥🔥 как хорошо, что я сюда зашёл!!!

@TutorExcelRu

2 жыл бұрын

👍

Добрый день. как это сделать в гугл таблицах? из столбца где есть разные данные есть ячейки с Итого по каждому элементу. вот как просуммировать значения в этих ячейках итого? каждая такая ячейка итого выделена цветом? Не нашел в гугл таблицах где можно свою формулу сделать

@TutorExcelRu

3 жыл бұрын

Как это сделать в гугл таблицах, к сожалению, не смогу подсказать. В принципе пользовательские функции (как из примера в видео) как класс функций в гугл таблицах есть, они пишутся с помощью скриптов, но какой конкретно нужен код не сориентирую.

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

@TutorExcelRu

2 жыл бұрын

Проблема в том, что при изменении заливки не меняется содержимое ячейки, поэтому автоматом значения не пересчитываются 🙁 Чтобы каждый раз не заходить в ячейку и не нажимать Enter для ускорения работы можно нажать F9 (или Shift + F9), Excel в этом случае пересчитает формулы в книге/листе. Но совсем без активных действий, к сожалению, не обойтись.

Подскажите пожалуйста, как сделать чтобы вместе с выделенными цветом ячейками считались не выделенные ячейки (прозрачные)?

@TutorExcelRu

Жыл бұрын

Зависит от деталей вашей задачи, могут разные варианты подойти. Можно просто написать формулу "=СЧЁТЦВЕТ (диапазон; ячейка_с_заливкой) + СЧЁТЦВЕТ (диапазон; прозрачная_ячейка)", в этом случае функция сначала посчитает ячейки с заливкой, потом посчитает ячейки без заливки, а затем сложим полученный результат. Еще как вариант изменить непосредственно код функции, чтобы функция считала по двум цветам - выделенному и прозрачному цветам.

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

@TutorExcelRu

2 жыл бұрын

К сожалению, полностью работающего решения нет... Excel не запускает пересчет формул если не меняется содержимое ячейки. А изменение заливки как раз не подходит под это условие, поэтому приходиться изобретать другие способы как то этого обойти. Самый простой - нажимать F9 (или Shift+F9) для принудительного обновления формул. Еще можно зайти в режим редактирования любой ячейки и нажать Enter (чтобы Excel подумал что поменялось содержимое ячейки). Но в любом случае нужны будут действия со стороны пользователя.

@ponyvpumponi3120

2 жыл бұрын

@@TutorExcelRu а с умной таблицей как быть, если она постоянно пополняется?

СУММШРИФТ как использовать? второго аргумента пример шрифта как создать?

@TutorExcelRu

3 жыл бұрын

Чтобы посчитать сумму/счет не по цвету ячейки, а по цвету шрифта, то в коде функции нужно свойство Interior (это как раз заливка ячейки) поменять на Font (это цвет шрифта). Т.е. грубо говоря везде использовать "cell.Font.Color" вместо "cell.Interior.Color".

У меня не появилась функция , с чем может бить связано?

подскажи что я сделал не правильно , повторил как на видео все , но когда вожу формулу то получаю #ИМЯ?. могу прислать и сам Excel документ что бы вы посмотрели

@TutorExcelRu

3 жыл бұрын

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

Здравствуйте. Помогите пожалуйста, как можно в 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

@TutorExcelRu

3 жыл бұрын

Что-то подобное можно сделать с помощью динамического диапазона, как раз через ИНДЕКС и ПОИСКПОЗ, либо еще можно через СМЕЩ попробовать. Но в любом случае таким образом получится создать только динамический диапазон, который будет зависеть от заданного n, но сделать динамическую формулу, т.е. прописать внутри формулы условно n других формул как у Вас в примере, не получится. Как вариант, можно, например, создать дополнительный столбец, где будет считаться разность столбцов (G3-F3, G3-F4, G3-F5 и т.д.) и уже для этого нового столбца применить динамический диапазон по которому посчитать сумму. В общем тут многое зависит конкретно от Вашей задачи и условий 🙂

@user-oh9uf3ht3o

3 жыл бұрын

@@TutorExcelRu =СРОТКЛ(СМЕЩ(M7;0;0;N$3;1)).

@TutorExcelRu

3 жыл бұрын

Отлично 👍

01:55 обратите внимание как хорошо показаны пояснения в коде после апострофа! По видео всё повторил: сумма не выдаётся - в ячейке появляется знак решётка. Было бы хорошо выкладывать готовый файл в описании.

@TutorExcelRu

2 жыл бұрын

Да, спасибо, добавил ссылку с файлом в описание. Продублирую и здесь: bit.ly/3HSifkg 🙂

как сделать так, чтобы макрос считал количество ячеек по цвету и значению? например мне нужно чтобы выводил количество "Б" в зеленых ячейках (чтобы зеленые ячейки без "Б" не считались? )

@TutorExcelRu

2 жыл бұрын

Навскидку можно в функцию добавить аргумент в виде текстовой переменной, а далее уже в теле функции прописать сравнение.

@TutorExcelRu

2 жыл бұрын

Попробуйте вот так, возможно поможет: 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

может не получаться из за старой версии ексель? у меня 2007

@TutorExcelRu

3 жыл бұрын

Касательно создания модуля, то в самом Visual Basic (Alt + F11 для открытия редактора) есть окно Project Explorer (оно обычно в левом верхнем углу), щелкаете правой кнопкой мыши по окну и выбираете Insert -> Module. Проблем с Excel 2007 по идее быть не должно. Вы в каком формате сохраняете книгу (если не xlsb или xlsm, то работать не будет)? Файл из примера у вас работает?

@respect_911

3 жыл бұрын

@@TutorExcelRu дружище. Нужна помощь небольшая. Не даром. Куда писать ?

Видео действительно полезное. Спасибо автору. Но как быть, если нужно посчитать сумму ячеек, залитых определённым цветом, а там вместо цифр стоят буквы, которые подразумевают некие цифры. Например в графике: стоит Д - значит 13 часов, Н - 15 часов

@TutorExcelRu

Жыл бұрын

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

@TheMrMozgokrut

Жыл бұрын

@@TutorExcelRu пришлось прибегнуть к хитрости и заставить chatGPT написать макрос для выполнения вышеуказанных вычислений)))

Видео огонь, формула, что надо, но устарело все. Сайт заблокирован, а если вручную ввожу в гугл таблицах, скрипты не распознает,

Как считать все цвета в диапазоне? Если он разные.

@TutorExcelRu

3 жыл бұрын

Как вариант можно попробовать просуммировать весь диапазон через обычный СУММ и вычесть СУММЦВЕТ по диапазону для любой не закрашенной ячейки, тогда в результате получится сумма всех ячеек диапазона, у которых есть цвет. Либо отдельную пользовательскую функцию написать.

Спасибо за все, все отлично работает. Но у меня одна проблема - когда закрою excell функции СЧЕТЦВЕТ на ячейках показывает #ИМЯ?. Потом я на стоке формулы слово счетцвет заново пищу большими буквами тогда функция заново работает. Пожалуйста если есть возможность дайте совет.

@TutorExcelRu

4 жыл бұрын

Эта ошибка скорее всего возникает из-за настройки безопасности макросов. При открытии книги макросы могут быть по умолчанию отключены (в этом случае как раз не работают пользовательские функции и вместо них мы видим #ИМЯ?), поэтому каждый раз при открытии Excel они не работают. Как только вы разрешите использование макросов в настройках безопасности (это можно сделать через Параметры -> Центр управления безопасностью), то проблема должна исчезнуть.

@stariymaster9

4 жыл бұрын

Спасибо за Ваш ответ. Попробовал все 5 вариантов которой там было предложено. Но результата нет. Все равно спасибо Вам

@TutorExcelRu

4 жыл бұрын

Хм... заинтриговали вопросом 🤔 Если не сложно, пришлите мне на почту скрин как конкретно выглядит проблема и сам файл (если есть возможность), я смогу посмотреть.

@stariymaster9

4 жыл бұрын

Спасибо за Ваш ответ. Отправил таблицу почтовый адрес.

@stariymaster9

4 жыл бұрын

Спасибо Иван. Я получил таблицу отправленный обратно. Благодарю, все работает отлично.

Сайт заблокирован хостинг-провайдером

как подсчитать если две ячейки объединены в одну? значение дублируется!

Добрый день, воспользовался, все работает, но теперь после каждого входа даже без внесения каких-либо изменений спрашивает "сохранить ли файл" - нажимаешь сохранить вылетает уведомление "будьте внимательны! В документе могу быть персональные данные, которые невозможно удалить с помощью инспектора документов" Как убрать эти всплывающие окна и постоянный запрос на сохранение?

@TutorExcelRu

3 жыл бұрын

Попробуйте зайти в панели вкладок Файл -> Параметры -> Центр управления безопасностью, далее нажмите кнопку Параметры центра управления безопасностью и затем снимите галочку напротив поля Удалять персональные данные из свойств файла при сохранении, по идее должно помочь.

@romank.2915

3 жыл бұрын

@@TutorExcelRu спасибо, помогло

@TutorExcelRu

3 жыл бұрын

Отлично 🙂

при подсчёте цветов постоянно пишет 0, что делать в этом случае?

@TutorExcelRu

2 жыл бұрын

А цвет ячейки именно с помощью заливки задаётся? Условное форматирование для определения цветов не используете?

@user-wo2kx1ul9y

2 жыл бұрын

@@TutorExcelRu да, с помощью заливки. условное форматирование не использую.

@TutorExcelRu

2 жыл бұрын

Несколько вариантов в голову приходят: 1) не включен пересчет формул, решается нажатием F9/Shift + F9 2) не совпадает цвет ячеек, они могут выглядеть визуально одинаково, но по RGB палитре отличаются 3) формат отображения ячеек, в ячейке отображается 0, но в действительности там не 0 (например, скрыты знаки после запятой и т.п.) Каждый вариант не слишком вероятен, но все же есть шанс, что поможет 🙂

Как сделать, чтобы в самой Функции в скобках между диапазоном и цветом ячейки было через запятую "," а не через точка с запятой ";"?

@TutorExcelRu

3 жыл бұрын

Разделитель в формулах задается не через Excel, а через системные настройки операционной системы. Нужно зайти в Панель управления -> Язык и региональные стандарты -> Дополнительные параметры и в поле *Разделитель элементов списка* вместо ";" поставить ",", при этом также надо будет поменять *Разделитель целой и дробной части* с "," на ".", чтобы не было проблем с десятичными числами (так как к примеру, "5,6" это уже не "5 целых 6 десятых", а перечисление чисел 5 и 6). В самом Excel в Файл -> Параметры -> Дополнительно нужно поставить галочку в поле *Использовать системные разделители* , чтобы программа как раз забирала введенные разделители из настроек.

@14vicente

3 жыл бұрын

@@TutorExcelRu а вот он что. Спс за ликбез

@14vicente

3 жыл бұрын

@@TutorExcelRu как уже было сказано, чтобы обновить значения на листе надо нажимать кнопку ctrl+alt+F9. А можно создать "кнопку" на листе для этой функции в модуле? Чтобы не нажимать постоянно ctrl+alt+F9, а просто создать кнопку щелчок на листе. Нажал ее и данные обновились

@TutorExcelRu

3 жыл бұрын

Тут 2 варианта можно попробовать. В панели вкладок в принципе уже есть такая кнопка в разделе с формулами (возможно придётся ее поискать, не всегда сразу можно заметить). Также можно добавить на лист обычную кнопку, задать по ее нажатию запуск макроса, в котором уже прописать выполнение обновления книги.

@14vicente

3 жыл бұрын

@@TutorExcelRu да, вы были правы, я эту кнопку на панели вкладок с формулами так и не нашел, хотя долго и внимательно искал. Сделал вторым способом: создал кнопку щелчок и в Модуле прописал "application.calculateFull". И вуаля, все заработало. Мерси

У меня нет вкладки "Разработчик" что делать?

@TutorExcelRu

3 жыл бұрын

Идите в панели вкладок в Файл -> Параметры -> Настроить ленту и далее поставьте галочку напротив поля "Разработчик" в правой части окна настройки ленты

Да, вот только разве в ручную кто то делает распределение цвета. Вся фишка как по мне в уставном форматировании как раз )

@TutorExcelRu

2 жыл бұрын

Бывает, что при работе с таблицей применяются разные фильтры и на каждом этапе подходящие ячейки закрашивают одним цветом, после чего как раз есть необходимость их просуммировать/посчитать 🙂 Но соглашусь, с условным форматированием тоже очень хочется так делать, но пока увы(

Пишет что не возможно сохраните без поддержки макросов. Что это значит?

@TutorExcelRu

3 жыл бұрын

Подсчет ячеек идёт с помощью пользовательских функций, поэтому нужно чтобы в файле можно было хранить код на VBA. Пересохраните файл в формате .xlsb или .xlsm, там есть поддержка макросов.

@user-dn7ln1ck6j

3 жыл бұрын

@@TutorExcelRu спасибо. А как это сделать?

@TutorExcelRu

3 жыл бұрын

Во вкладке Файл выберете команду Сохранить как (либо нажмите F12), а далее в поле Тип файла выберете соответствующий формат (xlsb или xlsm).

@user-dn7ln1ck6j

3 жыл бұрын

@@TutorExcelRu а таким образом выбрал сохранить с макросами. И вроде не было таких вариантов для сохранения. Я завтра ещё посмотрю, сейчас уже отдыхаю в балке. (на севере работаю)

@user-dn7ln1ck6j

3 жыл бұрын

@@TutorExcelRu нет таких форматов. Это что значит, я не смогу так сделать?

А как подсчитать сумму ячеек, если сумма в них меняется в зависимости от результата другой ячейки? Ячейка имеет такую формулу: "=ЕСЛИ(H2="выигрыш";G2-F2;"")&ЕСЛИ(H2="проигрыш";F2;"")&ЕСЛИ(H2="в игре";F2;"")&ЕСЛИ(H2="выкуп";"выкуплено";"")&ЕСЛИ(H2="возврат";F2;"")"

Формула очень полезная, но у меня не чего не получилось 🙈

@TutorExcelRu

2 жыл бұрын

Что конкретно у вас не получилось? Поделитесь, постараюсь помочь 🙂

почему нельзя это же сделать в условным форматированием

@TutorExcelRu

3 жыл бұрын

С условным форматированием, к сожалению, все не так просто... Вытащить и проверить условие на цвет ячейки с условным форматированием через VBA уже не получится, так как такой возможности в VBA нет, т.е. функции работают только для ячеек с классическим способом заливки.

@user-oh9js9yp1h

3 жыл бұрын

@@TutorExcelRu спасибо большое, и еще один вопрос , как протянуть формулу если строки идут друг за другом и ячейка с цветом всегда одна наверху

@user-oh9js9yp1h

3 жыл бұрын

когда я протягиваю формулу у меня она сбивается и пишет ошибка

@TutorExcelRu

3 жыл бұрын

Вероятно ячейка с цветом не закреплена в формуле, поэтому при протягивании формула сбивается. Попробуйте закрепить ссылку на ячейку, условно говоря вместо записи вида A4 в формуле прописать $A$4 (т.е. добавить $ к записи строк/столбов), тогда ссылка на ячейку во всех формулах будет одна и та же.

@user-oh9js9yp1h

3 жыл бұрын

@@TutorExcelRu значке доллара ставлю =СЧЁТЦВЕТ($C$25:$AG$25;$T1), а когда протягиваю на следующую строку то получается вот так =СЧЁТЦВЕТ($C$25:$AG$25;$T2) а по идеи должно быть С26 и Т1

не работает в 2016

@TutorExcelRu

Жыл бұрын

Проверьте, что у вас обязательно включены макросы, без них как раз может не работать.

@user-iy9xt1jz1n

Жыл бұрын

@@TutorExcelRu всё было включено.

К сожалению не работает: Для первой формулы - #ИМЯ? Для второй - #ЗНАЧ!

@TutorExcelRu

2 жыл бұрын

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

@voyaka205

2 жыл бұрын

@@TutorExcelRu в том то и дело что включены. Пробовал установить VBA пакет и пробовал встроенную функцию СЧЁТЗАЛИВКА, сперва работала, потом перестала.

@TutorExcelRu

2 жыл бұрын

А мой исходный файл из примера у Вас работает или тоже выдает ошибку?

@voyaka205

2 жыл бұрын

@@TutorExcelRu тоже выдаёт ошибку

@voyaka205

2 жыл бұрын

@@TutorExcelRu в моей работе это самые удобные функции , но никак не могу добиться , чтоб заработали.

Не страдайте ерундой, гораздо проще и правильнее сразу указывать машиночитаемые признаки в дополнительном столбце. А то потом может понадобится МАКСЦВЕТ, ЕСЛИЦВЕТ, и так далее создавать всю библиотеку функций под неправильный способ обработки данных.

@TutorExcelRu

3 жыл бұрын

Эти функции на самом деле некий аналог популярных СУММ и СЧЁТ, которые в Excel используется как разные функции, поэтому и в этом примере с подсчетом по цвету они рассмотрены также отдельно. Но, конечно, можно попытаться написать универсальную функцию, где в параметре задавать какую именно операцию нужно сделать 🙂

Спасибо, понятного ничего