Excel, если цвет ячейки затем значение

Обновлено: 05.07.2024

Стив хотел бы создать оператор ЕСЛИ (используя функцию рабочего листа) на основе цвета ячейки. Например, если A1 имеет зеленую заливку, он хочет вернуть слово «идти», если она имеет красную заливку, он хочет вернуть слово «стоп», а если это любой другой цвет, вернуть слово «ни один». . Стив предпочитает не использовать для этого макрос.

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

Выберите ячейку A1.
Нажмите Вставить | Имя | Определять. Excel отобразит диалоговое окно «Определить имя».
Используйте имя, например "mycolor" (без кавычек).
В поле "Относится к" введите в одну строку следующее:
Нажмите "ОК".

Определив это имя, вы можете в любой ячейке ввести следующее:

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

Таким образом, решение заключается в использовании определяемой пользователем функции, которая (по определению) является макросом. Макрос может проверять цвет, которым заполнена ячейка, а затем возвращать значение. Например, следующий пример возвращает одно из трех слов в зависимости от цвета в целевой ячейке:

Этот макрос оценивает значения цветов RGB в ячейке и возвращает строку на основе этих значений. Вы можете использовать функцию в ячейке следующим образом:

Если вы предпочитаете проверять индексные цвета вместо цветов RGB, вам подойдет следующий вариант:

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

Один из способов сделать это — использовать очень простой макрос, который ничего не делает, кроме как возвращает значение индекса цвета:

Теперь на листе вы можете использовать следующее:

В результате отображается значение индекса цвета ячейки B5. Предполагая, что ячейка B5 отформатирована с использованием одного из ожидаемых цветов (красного или зеленого), вы можете снова вставить значение индекса в более ранние макросы, чтобы получить желаемые результаты. Однако вы можете просто пропустить этот шаг и положиться на значение, возвращаемое функцией GetFillColor, для составления формулы ЕСЛИ следующим образом:

Вы должны иметь в виду, что эти функции (независимо от того, смотрите ли вы на значения цвета RGB или значения индекса цвета) проверяют явное форматирование ячейки. Они не учитывают неявное форматирование, например, применяемое посредством условного форматирования.

Другие полезные идеи, формулы и функции по работе с цветами можно найти на этой странице веб-сайта Чипа Пирсона:

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

=ЕСЛИ (цвет ячейки), то?

Вопрос из двух частей:

<р>1. Как структурировать оператор ЕСЛИ на основе определенного цвета ячейки (например, если ячейка желтая, то выполнить функцию x)

<р>2. Как я узнаю, что это за цвет? Есть ссылка на пантон? «Имя» цвета, которое использует Excel?

Гуру форума Регистрация 23.06.2007 Местоположение Лондон, Англия MS-Off Ver office 97 ,2007 Сообщений 19,320

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

"Если не указано иное, все мои комментарии адресованы OP"

Forum Expert Дата регистрации 04-04-2007 Расположение Юго-Восток, США MS-Off Ver XP, 2007, 2010 Сообщений 3,978

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

Если вы можете использовать решение VBA, выполните поиск по форуму, используя такие термины, как: подсчет ячеек по цвету, суммирование ячеек по цвету и т. д.

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

Знаете ли вы . . . ?

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

Forum Expert Дата регистрации 14 февраля 2005 г. Местоположение Вустер, Великобритания MS-Off Ver Excel 2007 (домашняя страница) Сообщений 7 099

Это может помочь?

Блаженны те, кто может отдавать, не помня, и брать, не забывая

Если вы довольны полученной помощью, нажмите значок слева — Спасибо .

Зарегистрированный пользователь Дата присоединения 17.06.2011 Местоположение Мэн MS-Off Ver Excel 2003 Сообщения 7

Мне также может понадобиться помощь, если быть точным, я беру данные и присваиваю им уровень достоверности. Уровень достоверности будет указан с помощью цвета фона ячейки, т. е. зеленая ячейка указывает уровень достоверности +/- 10%, и поэтому мой ожидаемый ранд данных равен X+(X*.1) и X-(X*. 1) и т. д. (X – моя измеренная выборка.

Как мне создать функцию, чтобы ЕСЛИ ячейка одна, она использовала один уровень достоверности, если другая, то другой и т. д.?

Зарегистрированный пользователь Дата присоединения 02-07-2009 Расположение Лондон, Англия MS-Off Ver Office 2010 Сообщения 17

Я знаю, что копаю здесь старый пост, но мне интересно, есть ли в новом Excel 2013 решение этой проблемы. Я вручную раскрашиваю ячейку и хочу, чтобы она показывала значение x (со ссылкой на другую ячейку), если цвет зеленый, и 0, если красный.

Зарегистрированный пользователь Дата присоединения 09-09-2011 Расположение Бангалор, Индия MS-Off Ver Excel 2003 и 2007 Сообщений 19 164

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

Если я помог, не забудьте добавить в мою репутацию (нажмите на звездочку под сообщением)
Не забудьте пометить тему как "Решенную" (Инструменты для работы с нитями->Отметить тему как решенную) )
Используйте теги кода при размещении кода VBA: [code] Ваш код здесь [/code]

В этой статье вы найдете два быстрых способа изменить цвет фона ячеек на основе значения в Excel 2016, 2013 и 2010. Кроме того, вы узнаете, как использовать формулы Excel для изменения цвета пустого поля. ячейки или ячейки с ошибками в формуле.

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

Цвет фона изменится автоматически при изменении значения ячейки

После установки цвет фона не изменится независимо от того, как изменится значение ячейки

Как динамически изменить цвет ячейки в зависимости от значения в Excel

Цвет фона будет меняться в зависимости от значения ячейки.

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

Решение. Вам нужно использовать условное форматирование Excel, чтобы выделить значения больше X, меньше Y или между X и Y.

Предположим, у вас есть список цен на бензин в разных штатах, и вы хотите, чтобы цены выше 3,7 доллара США были окрашены в красный цвет, а цены, равные 3,45 доллара США или меньше, – в зеленый.

Примечание. Снимки экрана для этого примера были сделаны в Excel 2010, однако кнопки, диалоговые окна и настройки одинаковы или почти одинаковы в Excel 2016 и Excel 2013.

Хорошо, вот что нужно сделать шаг за шагом:

Выберите таблицу или диапазон, для которых вы хотите изменить цвет фона ячеек.В этом примере мы выбрали $B$2:$H$10 (имена столбцов и первый столбец с названиями штатов исключены из выбора).
Перейдите на вкладку "Главная", в группу "Стили" и выберите Условное форматирование Новое правило….
В диалоговом окне Новое правило форматирования выберите «Форматировать только те ячейки, которые содержат» в поле «Выбрать тип правила» в верхней части диалогового окна.
В нижней части диалогового окна в разделе "Форматировать только ячейки с разделом" задайте условия правила. Мы выбираем форматировать только ячейки со значением ячейки — больше — 3,7, как вы можете видеть на снимке экрана ниже.

Результат форматирования будет выглядеть следующим образом:

Поскольку нам нужно применить еще одно условие, то есть изменить фон ячеек со значениями, равными или меньшими 3,45, на зеленый цвет, снова нажмите кнопку «Новое правило» и повторите шаги 3–6, установив необходимое условие. Вот предварительный просмотр нашего второго правила условного форматирования:

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

Совет. Вы можете использовать тот же метод для изменения цвета шрифта в зависимости от значения ячейки. Для этого просто перейдите на вкладку Шрифт в диалоговом окне Формат ячеек, которое мы обсуждали на шаге 5, и выберите нужный цвет шрифта.

Как навсегда изменить цвет ячейки на основе ее текущего значения

После установки цвет фона не изменится независимо от того, как содержимое ячейки может измениться в будущем.

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

Решение. Найдите все ячейки с определенным значением или значениями с помощью функции Excel Найти все или надстройки Выбрать специальные ячейки, а затем измените формат найденных ячеек с помощью функция Форматировать ячейки.

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

Найти и выбрать все ячейки, соответствующие определенному условию

Может быть несколько возможных сценариев в зависимости от того, какие значения вы ищете.

Если вам нужно раскрасить ячейки с определенным значением, например 50, 100 или 3.4, перейдите на вкладку Главная, в группу Редактирование и нажмите Найти Выбрать > Найти… .

Введите необходимые значения и нажмите кнопку «Найти все».

Совет. Нажмите кнопку "Параметры" в правой части диалогового окна Найти и заменить, чтобы получить ряд параметров расширенного поиска, например "Учитывать регистр". и "Соответствие всему содержимому ячейки". Вы можете использовать подстановочные знаки, например звездочку (*), чтобы найти любую строку символов, или вопросительный знак (?), чтобы найти любой отдельный символ.

В нашем предыдущем примере, если бы нам нужно было найти все цены на бензин в диапазоне от 3,7 до 3,799, мы бы указали следующие критерии поиска:

Теперь выберите любой из найденных элементов в нижней части диалогового окна Найти и заменить, щелкнув его, а затем нажмите Ctrl + A, чтобы выбрать все найденные элементы. После этого нажмите кнопку Закрыть.

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

Однако на самом деле нам нужно найти все цены на бензин выше 3,7, и, к сожалению, диалоговое окно Найти и заменить в Excel не позволяет сделать это.

К счастью, есть еще один инструмент, который может справиться с такими сложными условиями. Надстройка «Выбрать специальные ячейки» позволяет найти все значения в указанном диапазоне, например. от -1 до 45, получить максимальное/минимальное значение в столбце, строке или диапазоне, найти ячейки по цвету шрифта, цвету заливки и многому другому.

Вы нажимаете кнопку Выбрать по значению на ленте, а затем указываете критерии поиска на панели надстройки. В нашем примере мы ищем значения больше 3,7. Нажмите кнопку Выбрать, и через секунду вы получите такой результат:

Если вам интересно попробовать надстройку Select Special Cells, вы можете загрузить ознакомительную версию здесь.

Изменить цвет фона выбранных ячеек с помощью диалогового окна «Формат ячеек»

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

Откройте диалоговое окно «Формат ячеек», нажав Ctrl + 1 (вы также можете щелкнуть правой кнопкой мыши любую из выделенных ячеек и выбрать «Формат ячеек…» во всплывающем меню или перейти к Вкладка «Главная» > группа «Ячейки» > Формат > Формат ячеек…) и внесите все необходимые изменения формата. На этот раз мы изменим цвет фона на оранжевый, просто для разнообразия :)

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

Вот результат изменения формата в Excel:

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

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

Как и в предыдущем примере, вы можете изменить цвет фона специальных ячеек двумя способами: динамически и статически.

Используйте формулу Excel для изменения цвета фона специальных ячеек

Цвет ячейки автоматически изменится в зависимости от значения ячейки.

Этот метод обеспечивает решение, которое вам, скорее всего, понадобится в 99 % случаев, т. е. цвет фона ячеек будет меняться в соответствии с заданными вами условиями.

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

На вкладке Главная в группе Стили нажмите Условное форматирование >Новое правило… (см. шаг 2 раздела Как динамически изменить цвет ячейки на основе значения для пошагового руководства).
В диалоговом окне «Новое правило форматирования» выберите параметр «Использовать формулу для определения форматируемых ячеек». Затем введите одну из следующих формул в поле "Форматировать значения, где эта формула верна":
- =IsBlank() — для изменения цвета фона пустых ячеек.
- =IsError() — для изменения цвета фона ячеек с формулами, которые возвращают ошибки.

Статично изменить цвет фона специальных ячеек

После изменения цвет фона останется прежним, независимо от изменений значений ячеек.

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

Помните, что изменения форматирования, сделанные таким образом, сохранятся, даже если ваши пустые ячейки будут заполнены данными или исправлены ошибки в формулах. Конечно, сложно представить навскидку, зачем кому-то может хотеться иметь именно так, может быть, просто в исторических целях :)

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

Как активный пользователь Microsoft Excel, вы знаете, что он имеет множество функций. Некоторые из них мы знаем и любим, другие — полная загадка для рядового пользователя и различные блоги, в том числе и этот, пытаются пролить на них хоть какой-то свет. Но! Есть несколько очень распространенных задач, которые всем нам приходится выполнять ежедневно, и Excel просто не предоставляет никаких функций или инструментов для их автоматизации или упрощения.

Например, если вам нужно проверить 2 рабочих листа на наличие дубликатов или объединить строки из одной или разных электронных таблиц, потребуется множество загадочных формул или макросов, и все равно нет гарантии, что вы получите точные результаты.

По этой причине команда наших лучших разработчиков Excel спроектировала и создала более 60 надстроек, которые мы называем Ultimate Suite for Excel. Эти интеллектуальные инструменты справляются с самыми изнурительными, кропотливыми и подверженными ошибкам задачами в Excel и обеспечивают быстрые, аккуратные и безупречные результаты. Ниже приведен краткий список лишь некоторых задач, с которыми могут помочь надстройки:

Просто попробуйте эти надстройки, и вы увидите, что ваша производительность Excel повысится как минимум на 50%!

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

Возможно, у вас есть цветной набор данных, и вы хотели бы использовать цвет ячейки для работы с формулой Excel. В Excel есть множество удивительных наборов формул для записи и чтения данных из наборов данных. Некоторые из них: COUNT, SUBTOTAL, IF и так далее. Опять же, вы также можете использовать макросы VBA для создания новых формул в соответствии с требованиями, которые вы можете применить для разных цветов ячеек. В этой статье будут объяснены 5 примеров формулы Excel на основе цвета ячейки с соответствующими иллюстрациями.

Загрузить практическую рабочую тетрадь

Вы можете скачать учебную тетрадь отсюда.

5 примеров формулы Excel на основе цвета ячейки

Для объяснения методов мы будем использовать следующий красочный набор данных.

Мы видим, что в наборе данных есть два столбца: Имя и Количество. В рядах 3 разных цвета. Мы будем применять различные формулы Excel, такие как СУММЕСЛИ, ПРОМЕЖУТОЧНЫЕ.ИТОГИ, ЕСЛИ и определяемые пользователем функции с использованием макросов VBA в следующих 5 примерах. Итак, без промедления приступим к основному обсуждению.

1. Формула ПРОМЕЖУТОЧНЫХ ИТОГОВ Excel с цветом ячейки

Чтобы применить формулу Excel ПРОМЕЖУТОЧНЫЙ ИТОГ для подсчета и получения суммы значений, отфильтрованных по цвету.

Для этого метода выполните следующие действия.

Шаги:

В ячейку C6 напишите следующую формулу, чтобы получить количество продуктов в списке:

Мы также можем использовать формулу ПРОМЕЖУТОЧНЫХ ИТОГОВ для целей суммирования. Посмотрим.

Чтобы получить сумму количеств продукта, напишите следующую формулу в ячейке C14:

Вы найдете две стрелки в столбцах набора данных.

Нажмите на значок стрелки рядом с названием столбца.
Откроется раскрывающееся меню боковой панели. Оттуда выберите «Фильтровать по цвету».
Теперь выберите цвет, который вы хотите отфильтровать.

Он покажет отфильтрованный набор данных.

Вы можете заметить изменения значений в параметрах "Подсчет по цвету" и "Сумма по цвету" на рисунках ниже.

Результаты показывают количество и сумму только отфильтрованных данных

🔎Как работает формула?

📌 ПРОМЕЖУТОЧНЫЙ ИТОГ принимает два аргумента имя_функции и ссылка1. В имени_функции требуется 102 для подсчета количества данных и 109 для возврата суммы количеств.

📌 В качестве ссылки в обеих формулах используется диапазон величин.

📌 Результат на старте показывает все данные в диапазоне. Однако на последних двух рисунках показан результат только для отфильтрованных ячеек.

2. Формулы Excel СЧЁТЕСЛИ и СУММЕСЛИ по цвету ячейки

2.1 Формула СЧЁТЕСЛИ с цветом ячейки

Теперь, если вы хотите применить формулу СЧЁТЕСЛИ по цвету ячейки, выполните следующие действия.

Шаги:

Появится окно. Напишите имя (в данном случае мы написали NumberColor) в разделе Name:.
В поле Относится к: напишите следующую формулу:

Он будет отображаться в окне "Диспетчер имен".

Если все в порядке, нажмите "Закрыть".

Помимо набора данных возьмите столбец и в ячейке D5 напишите формулу:

Нажмите Enter и перетащите его с помощью значка маркера заполнения на остальные столбцы.

Вы получите код для всех цветов, присутствующих в наборе данных.

В новой ячейке (G5) напишите следующую формулу:

В ячейке G6

В ячейке G7

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

2.2 Формула СУММЕСЛИ с цветом ячейки

Шаги:

Введите следующую формулу в ячейку H5:

Аналогично в ячейке H6,

А в ячейке H7

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

🔎Как работает процесс с формулами?

📌 Здесь формула, использующая функцию GET.CELL, принимает 38, чтобы вернуть цвет кода и ссылку на ячейку, код которой она возвращает.

📌 Определив Name для с помощью формулы GET.CELL, мы можем просто написать имя «NumberColor» с префиксом знака равенства, чтобы получить код цвета указанной ячейки.

📌 Затем, используя цветовые коды, мы применили формулу СЧЁТЕСЛИ и СУММЕСЛИ, чтобы получить количество и сумму диапазона данных с критериями цветового кода.

3. Формула ЕСЛИ в Excel по цвету ячейки

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

Если вы хотите рассчитать общую цену для общего количества этих продуктов, мы можем использовать формулу ЕСЛИ.

Вы можете выполнить шаги по применению IF здесь.

Шаги:

Мы уже создали NumberColor с помощью Define Name и использовали его для поиска цветовых кодов (см. способ 2).
В новом столбце напишите формулу в ячейке E5:

Нажмите Enter.
Перетащите значок маркера заполнения, чтобы получить результат для остальных данных.

Вы можете заметить, что он показал значения только для товаров одного цвета с цветовым кодом 40, а для остальных - ноль (0).

🔎Как работает формула?

📌 Здесь формула ЕСЛИ принимает NumberColor равным 40.

📌 Если логика верна, она умножит количество на цену за штуку (5). В противном случае будет отображаться 0.

4. Формула СУММЕСЛИМН в Excel по цвету ячейки

Используя цветовой код, мы также можем применить формулу СУММЕСЛИМН.

Для этого вам необходимо выполнить следующие шаги:

Шаги:

После этого нажмите Enter.
Используйте значок маркера заполнения, чтобы перетащить результат для остальных случаев.

🔎Как работает формула?

📌 Формула СУММЕСЛИМН использует диапазон_сумм C5:C10 в качестве абсолютных ссылок для величин. Следовательно, он принимает диапазон цветового кода, который также находится в абсолютной эталонной форме.

📌 Наконец, критерии устанавливаются для первой ячейки столбца цветового кода — D5. В этом случае только столбец находится в форме абсолютной ссылки, а строки — в форме относительной ссылки. Это связано с тем, что он будет перетаскивать значок маркера заполнения для остальной части столбца, изменяя номера строк по мере необходимости.

5. Преобразование макроса Excel VBA в формулу Excel по цвету ячейки

Кроме того, VBA Macro может быть прекрасным инструментом для применения формул Excel по цвету ячейки.

Давайте разделим этот метод на две части для удобства понимания.

Первый подметод будет использовать код для поиска кода цвета, а затем применить его для применения формул СЧЁТЕСЛИ и СУММЕСЛИ

Примечание. Макрос VBA не может распознавать похожие цвета, поэтому мы изменили наш набор данных, включив в него различимые цвета.

Три разных цвета: красный, синий и коричневый. Теперь давайте посмотрим, как мы можем использовать макрос VBA для применения формулы Excel по цвету ячейки.

5.1 Макрос VBA для поиска цветового кода

Чтобы найти код цвета с помощью макроса VBA и применить формулы Excel, необходимо выполнить следующие действия.

Шаги:

Нажмите клавиши ALT+F11 на клавиатуре.
Откроется окно макроса VBA. Выберите свой лист.
На вкладке "Вставка" нажмите "Модуль".

Скопируйте и вставьте следующий код в окно "Общие".

Код:

Сохраните файл с суффиксом книги Excel с поддержкой макросов.
Откройте свой лист и напишите следующую формулу в ячейке D5:

Нажмите Enter и перетащите маркер заполнения, чтобы получить результат для остальных данных.

Теперь в другом столбце в ячейке E5 вы должны написать приведенную ниже формулу:

Нажмите Enter и перетащите результат до конца данных.

Аналогично для применения СУММЕСЛИ запишите приведенную ниже формулу в ячейке F5:

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

🔎Как работает процесс с формулами?

📌 Мы создали ColorIndex, используя код и сохранив аргумент как диапазон данных. Используя это, мы получаем цветовые коды.

📌 Затем мы использовали формулу СЧЕТЕСЛИ, чтобы получить результат подсчета для этого конкретного цветового кода.

📌 Наконец, мы использовали формулу СУММЕСЛИ, чтобы получить сумму на основе цветового кода.

5.2 Макрос VBA для суммирования

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

Шаги:

Вы должны нажать клавиши ALT+F11 на клавиатуре, чтобы открыть окно макроса VBA.
Опять же, вам нужно выбрать свой лист и пункт "Из модуля" на вкладке "Вставка".

Как и в предыдущем подметоде, откроется окно «Общие». Затем просто скопируйте и вставьте следующий код в окно «Общие».

Код:

Затем откройте лист. В ячейке D5 вы должны написать следующую формулу:

Нажмите Enter и перетащите результат с помощью маркера заполнения в конец диапазона данных.

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

🔎Как работает процесс с формулами?

📌 Мы создали формулу с именем SBC с помощью кода, который мы написали в окне «Общие» для этого рабочего листа.

📌 После этого мы использовали формулу с диапазоном данных и критериев в качестве отдельной ячейки величин.

Что нужно помнить

<р>1. Вы должны использовать разные цвета в случае применения макроса VBA.

<р>2. Вы должны сохранить файл Excel с суффиксом .xlsm, если файл содержит коды макросов VBA.

Заключение

В статье объясняется 5 различных методов применения формул Excel, таких как СУММЕСЛИ, ПРОМЕЖУТОЧНЫЕ ИТОГО, СЧЁТЕСЛИ и т. д., в зависимости от цвета ячейки. Кроме того, у вас есть практическая рабочая тетрадь, поэтому вы можете скачать ее и применить любой из методов в соответствии с вашими требованиями. По любым дополнительным вопросам, пожалуйста, пишите в разделе комментариев.

Читайте также: