Excel — подборка ячеек по цвету заливки

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

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

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

Чтоб было понятнее, приведу изображение схожей таблицы. В ней нужно произвести подсчет ячеек с заливкой зеленоватого цвета:

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

Красивое и готовое решение моей задачки я отыскал на веб-сайте http://www.excel-vba.ru/. Даже не одно, а целых два решения, под различные условия. Ниже привожу последовательность шагов, которые привели меня к успеху.

Сходу скажу, что изображения были изготовлены в Excel 2007. В Excel 2010 все несколько по другому, но запутаться нереально, если что.

Режим “Разраб” в Excel

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

Перебегаем в “Запуск — Характеристики Excel” и находим в левом перечне пункт “Надстройки”:

Избираем в основном окне строку “Пакет анализа — VBA” и нажимаем кнопку “Перейти” в самом низу окна. Раскроется очередное окошко со перечнем доступных под Excel расширений (надстроек). Опять избираем в этом перечне “Пакет анализа — VBA” и соглашаемся, что желаем установить его, нажав клавишу “ОК”:

Будет нужно установочный диск с Microsoft Office на нем (либо же подключение к Веб) чтоб программка получила нужные пакеты для установки. Если установка прошла удачно, то в “Ленте” показаться пункт “Разраб” (Excel 2010). Можно перейти в него через эту панель либо же при помощи сочетания кнопок Alt + F11 .

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

Вставка готовых функций в Excel VBA

Но есть готовые решения, которые я вставлю в виде кода при помощи меню “Insert — Module”. Просто берем отсюда код функций и вставляем в собственный Excel. Потом сохраняем файл Excel с поддержкой VBA (макросов) и все готово для предстоящей работы.

Вставленные функции покажутся в перечне формул таблицы:

Ниже представлен готовый код 2-ух функций на VBA, написанных их создателем Дмитрием Щербаковым. 1-ая функция с именованием “CountByInteriorColor” делает подсчет количества ячеек по цвету заливки.

2-ая функция с именованием “SumByInteriorColor” делает подборку ячеек по цвету заливки и суммирует все значения в этих ячейках.

Обе функции имеют однообразный синтаксис и принимают три входных аргумента, 1-ые два из которых неотклонимые, а 3-ий — необязательный:

• rRange — спектр с ячейками для подсчета
• rColorCell — ячейка-образец с цветом заливки
• bSumHide — ИСТИНА либо 1 учитывает сокрытые ячейки; ЛОЖЬ , 0 либо опущен(по умолчанию) — сокрытые ячейки не подсчитываются.

Функция подсчета количества ячеек

Синтаксис данной функции прост:

Функция подсчета суммы ячеек

Синтаксис данной функции последующий:

При вставке пользовательской функции “CountByInteriorColor” и “SumByInteriorColor” можно пользоваться или “Мастером функций”, или произвести указание спектра ячеек и ячейку-критерий вручную.

Описание рабочей формулы

Готовый пример работы функции “CountByInteriorColor” можно поглядеть на рисунке “Табель выходов с зеленоватыми ячейками”. В нем подсчет отработанного времени делается по последующей формуле:

Практически эта формула выходит таковой (смотри строчку №13 на рисунке):

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

TypeScript — размеченные объединения

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких критерий

Логическая функция ЕСЛИ в Экселе – одна из самых нужных. Она возвращает итог (значение либо другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Функция имеет последующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. К примеру, A2<100. Если значение в ячейке A2 вправду меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что обозначено в последующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и ворачивается значение из крайнего поля.

значение_если_истина – значение либо формула, которое ворачивается при пришествии обозначенного в первом параметре действия.

значение_если_ложь – это другое значение либо формула, которая ворачивается при невыполнении условия. Данное поле не непременно заполнять. В этом случае при пришествии альтернативного действия функция возвратит значение ЛОЖЬ.

Весьма обычный пример. Необходимо проверить, превосходят ли реализации отдельных продуктов 30 шт. либо нет. Если превосходят, то формула обязана возвратить «Ок», в неприятном случае – «Удалить». Ниже показан расчет с результатом.

Реализации первого продукта равны 75, т.е. условие о том, что оно больше 30, производится. Как следует, функция возвращает то, что обозначено в последующем поле – «Ок». Реализации второго продукта наименее 30, потому условие (>30) не производится и ворачивается другое значение, обозначенное в 3-ем поле. В этом вся сущность функции ЕСЛИ. Протягивая расчет вниз, получаем итог по любому товару.

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

Пока все разумно, но смущают минусы. Разве бывают отрицательные остатки? Нет, естественно. Припасы не могут быть ниже нуля. Чтоб прогноз был корректным, необходимо отрицательные значения поменять нулями. Тут непревзойденно поможет формула ЕСЛИ. Она будет инспектировать приобретенное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в неприятном случае — итог расчета, т.е. некое положительное число. В общем, та же логика, лишь заместо значений используем формулу в качестве условия.

В прогнозе припасов больше нет отрицательных значений, что в целом весьма хорошо.

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

Формула ЕСЛИ в Excel – примеры нескольких критерий

Достаточно нередко количество вероятных критерий не 2 (проверяемое и другое), а 3, 4 и наиболее. В этом случае также можно употреблять функцию ЕСЛИ, но сейчас ее придется вкладывать друг в друга, указывая все условия по очереди. Разглядим последующий пример.

Нескольким менеджерам по продажам необходимо начислить премию в зависимости от выполнения плана продаж. Система мотивации последующая. Если план выполнен наименее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно тут 4 варианта. Чтоб их указать в одной формуле будет нужно последующая логическая структура. Если производится 1-ое условие, то наступает 1-ый вариант, в неприятном случае, если производится 2-ое условие, то наступает 2-ой вариант, в неприятном случае если… и т.д. Количество критерий быть может достаточно огромным. В конце формулы указывается крайний другой вариант, для которого не производится ни одно из перечисленных ранее критерий (как третье поле в обыкновенной формуле ЕСЛИ). В итоге формула имеет последующий вид.

Композиция функций ЕСЛИ работает так, что при выполнении какого-нибудь указанно условия последующие уже не проверяются. Потому принципиально их указать в правильной последовательности. Если б мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не увидел», т.к. они входят в интервал B2<1 который проверился бы первым (если значение наименее 0,9, само собой, оно также меньше и 1). Тогда и у нас вышло бы лишь два вероятных варианта: наименее 1 и другое, т.е. 1 и наиболее.

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

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

Функция Excel ЕСЛИМН

Функция Эксель ЕСЛИ в целом отлично совладевает со своими задачками. Но вариант, когда необходимо записывать длинноватую цепочку критерий не весьма приятный, т.к., во-1-х, написать с первого раза не постоянно выходит (то условие укажешь ошибочно, то скобку не закроешь); во-2-х, разобраться по мере необходимости в таковой формуле быть может тяжело, в особенности, когда критерий много, а сами расчеты сложные.

В MS Excel 2016 возникла функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, лишь заточенная специально для проверки огромного количества критерий. Сейчас не надо 100 раз писать ЕСЛИ и считать открытые скобки. Довольно перечислить условия и в конце закрыть одну скобку.

Работает последующим образом. Возьмем пример выше и воспользуемся новейшей формулой Excel ЕСЛИМН.

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

Стоит направить внимание на последующее. Условия по-прежнему перечисляем в правильном порядке, чтоб не вышло ненадобного перекрытия диапазонов. Крайнее другое условие, в отличие от обыкновенной ЕСЛИ, также обязано быть непременно обозначено. В ЕСЛИ задается лишь другое значение, которое наступает, если не производится ни одно из перечисленных критерий. Тут же необходимо указать само условие, которое в нашем случае было бы B2>=1. Но этого реально избежать, если в поле с условием написать ИСТИНА, указывая тем, что, если не производятся ранее перечисленные условия, наступает ИСТИНА и ворачивается крайнее другое значение.

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

Подсчет ячеек на базе цвета ячеек в Гугл Таблицах

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

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

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

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

Подсчет ячеек на базе цвета фона

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

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

Я попробовал отыскать таковой сценарий в Вебе и, в конце концов, отыскал его в этом блоге.

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

Вот шаги, которые нужно сделать, чтоб поместить этот скрипт в документ Гугл Таблиц:

• В вашем документе Гугл Таблиц нажмите Инструменты
• В показавшихся параметрах щелкните Редактор сценариев. Раскроется редактор скриптов приложений.
• По дефлоту вы будете в окне кода Code.gs. Удалите все, что уже там, и скопируйте и вставьте приведенный выше код.
• Щелкните значок Сохранить на панели инструментов.
• Закройте окно скрипта приложений.

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

Примечание. Если у вас уже есть иной код в окне Code.gs и вы не желаете его попортить, идеальнее всего вставить новейший файл сценария и вставить туда код. Для этого щелкните значок «Плюс» рядом с «Файлы» и изберите «Сценарий». Это вставит новейший файл сценария (дайте ему имя) и скопируйте и вставьте приведенный выше код в этот новейший файл сценария.

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

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

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

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

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

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

Разрешите мне стремительно разъяснить, что тут происходит.

Эта настраиваемая формула, которую мы сделали, воспринимает два аргумента:

• спектр ячеек с цветными ячейками, которые нам необходимо подсчитать
• ячейка с цветом фона, который нужно подсчитать

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

К примеру, вы сможете избрать ячейку A3 заместо C1 в качестве второго аргумента, так как она также имеет этот же цвет.

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

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

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

Как создать так, чтоб формула автоматом обновлялась при изменении

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

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

Даже если я перейду к ячейке с формулой, перейду в режим редактирования и нажму кнопку «Ввод», она все равно не будет пересчитана.

Причина в том, что он не разглядывает это как изменение, влекущее за собой пересчет. Я даже попробовал обновить книжку, но она тоже не сработала (может быть, из-за некий задачи с кешем)

К огорчению, нет автоматического метода создать это, но есть вправду обычный ручной метод создать это.

Итак, вот исправление — перейдите к хоть какой из ячеек в спектре, войдите в режим редактирования (нажмите F2 либо два раза щелкните ячейку), добавьте пробел в конце содержимого ячейки и нажмите Enter. Как вы это сделаете, вы увидите, что формула пересчитывается и дает верный итог.

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

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

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

Ах так вы сможете создавать и употреблять свою свою функцию для подсчета продаж на базе цвета фона в Гугл Таблицах.

Как выделить ячейку цветом в Excel

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

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

Обычная заливка блока

Закрасить один либо несколько блоков в Экселе легко. Поначалу выделите их и на вкладке «Основная» нажмите на стрелку около ведерка с краской, чтоб развернуть перечень. Изберите оттуда пригодный цвет, а если ничего не подойдет, жмите «Остальные цвета».

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

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

Зависимо от введенных данных

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

Текстовых

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

Выделяем данные, с которыми будем работать, в моем случае – это наименования фруктов. Позже жмем «Условное форматирование», которое будем повсевременно употреблять далее. Из перечня кликните по «Сделать правило».

Раскрывается вот такое окно. Вверху избираем тип – «Форматировать лишь ячейки, которые содержат», далее тоже будем отмечать конкретно его. Чуток ниже указываем условия: у нас текст, который содержит определенные слова. В крайнем поле либо нажмите на клавишу и укажите ячейку, либо вчеркните текст.

Отличие в том, что поставив ссылку на ячейку (=$B$4), условие будет изменяться в зависимости от того, что в ней набрано. К примеру, заместо яблока в В4 укажу смородину, соответственно обменяется правило, и будут закрашены блоки с таковым же текстом. А если конкретно в поле вписать яблоко, то искаться будет непосредственно это слово, и оно ни от чего же зависеть не будет.

Далее жмем на клавишу «Формат».

Тут изберите цвет заливки и нажмите «ОК». Для просмотра всех вариантов кликните по кнопочке «Остальные».

Правило сотворено и сохраняем его, нажатием клавиши «ОК».

В итоге, все блоки, в которых был обозначенный текст, закрасились в красноватый.

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

Числовых

Сейчас разберемся с числовыми данными. Давайте числа в столбце D разместим на определенный фон по тому условию, которое зададим.

Выделяем столбец, создаем правило, указываем его тип. Далее прописываем – «Значение» «больше» «15». Крайнее число сможете либо ввести вручную, либо указать адресок ячейки, откуда будут браться данные. Определяемся с заливкой, нажимаем «ОК».

Блоки, где введены числа больше избранного, закрасились.

Давайте для выделенных ячеек укажем еще правила – изберите «Управление правилами».

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

Тут все выбирайте, как я обрисовывала выше, лишь необходимо поменять цвет и поставить условие «меньше либо равно».

Когда все будет готово, жмите «Применить» и «ОК».

Все работает, значения равные и ниже 15 закрашены бледно голубым.

Тут отлично была бы видна и разница меж завезенным и проданным продуктом. Выделяем два столбца и жмем «Правила выделения ячеек» – «Повторяющиеся значения». Изберите пригодный цвет. Опосля этого, если две ячейки рядом будут цветные – означает, те фрукты, которые завезли, все продали.

Давайте на примере крайнего столбца покажу, как создать, чтоб ячейка меняла цвет в зависимости от обозначенного значения. Выделяем их и заходим в «Управление правилами».

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

Избираем нужные пункты в открывшемся окошке. Я залью мрачно зеленоватым все значения, что больше 90. Так как в крайнем поле я указала адресок (=$F$15), то при изменении в ячейке числа 90, к примеру, на 110, правило также обменяется. Сохраните конфигурации, кликнув по кнопочке «ОК».

Создам очередное правило, но в нем подсвечу светло зеленоватым все, что меньше либо приравнивается 90. Не запамятовывайте все сохранять, нажав на кнопки, справа понизу.

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

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

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

Чтоб поглядеть, что Вы подобавляли, изберите спектр и в окне «Управление правилами» будет полный перечень. Используя клавиши вверху их можно добавлять, изменять либо удалять.

Поменять цвет для ячеек, можно и выбрав один из готовых стилей. Находится подходящая клавиша на вкладке «Основная».

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