Auditportal.ru

Аудит Портал
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти или выбрать ячейки по определенным критериям в Excel?

Как в Excel посчитать количество ячеек по цвету ячейки или цвету текста

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

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

Для этих целей необходимо использовать очень простой макрос, а точнее пользовательскую функцию, назовем ее ColorNom, она позволит нам извлечь числовой код цвета заливки и затем по этому коду мы будем считать общее количество ячеек, используя приемы, описанные в статье как считать в Excel ячейки/значения

Итак, давайте приступим. Введите в редактор Visual Basic следующее:

в Excel 2003 нажмите на Сервис, далее Макрос и затем Редактор Visual Basic.

сделать пользовательскую функцию

2007 и 2010 делают это по-разному; 2013 делает это по-другому. Перейдите в раздел «Разработчик», затем выберите Visual Basic

Обратите внимание! Раздел панели инструментов разработчика в Excel 2007 доступен по умолчанию, но в Excel 2010 и 2013 его необходимо активировать. Пользователь, часто работающий с макросами, может найти эту функцию полезной. Чтобы включить панель инструментов разработчика в Excel 2010 или 2013, необходимо выполнить команду Файл | Параметры | Настроить ленту, а затем установить флажок Разработчик с правой стороны.

включение редактора Visual Basic

После открытия редактора Visual Basic вставьте пустой модуль, выбрав меню Insert и затем Module.

вставить модуль

И скопировать туда текст простой функции:

Public Function ColorNom(Cell As Range)
ColorNom = Cell.Interior.ColorIndex
End Function

После этого закройте редактор VisualBasic и можно вернуться к нашему файлу. В любой пустой ячейки введите пользовательскую функцию, которую мы ввели раннее. В нашем случае это функция ColorNom, ее можно вызвать либо через меню Вставка, Функция — категория Определенные пользователем, либо просто можно напечатать ее в самой ячейке =ColorNom( A1 ), где A1 — это наша ячейка, в которой нам необходимо определить индекс цвета.

Пользовательская функция индекс цвета ячейки

После этого легко подсчитать количество ячеек, основываясь на цвете ячейки. Воспользуйтесь нашей статьей о том, как подсчитать количество ячеек/значений в Excel

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

Public Function ColorNom(Cell As Range)
ColorNom = Cell.Font.ColorIndex
End Function

Важно! Вы не сможете находить с помощью данной функции номер цвета ячейки при использовании условного форматирования. Кроме того, при изменении цвета ячейки Excel не пересчитывает значения, необходимо это делать в ручную, нажимая Ctrl+Alt+F9, либо изменения будут происходить при новом открытии данного файла. Это происходит из-за того, что Excel не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.

Читайте так же:
Как извлечь название компании из адреса электронной почты в Excel?

=ColorNom(A1)+Сегодня()*0

Пример подсчета количества значений по цвету цвету заливки ячеек в Excel

Рассмотрим пример со списком фруктов выше. Мы определили код ячейки и отобразили его для каждой ячейки.

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

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

Посчитать кол-во по цвету ячеек

Мы рассчитаем количество с помощью функции CALCULATE.

Аргументы данной функции следующие

=СЧЁТЕСЛИ( диапазон ; критерий )

=СЧЁТЕСЛИ( $B$1:$B$8 ; E2 )

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

Как закрасить ячейку в excel по условию даты

Раскрасить клетки в условиях несложно. Выберите ячейки, столбцы или строки, цвет которых вы хотите изменить. Затем на вкладке Главная перейдите в раздел Стили и выберите Условное форматирование > Условие ячейки. Выберите условие, которое соответствует вашим потребностям.

Как в Excel закрасить строку по условию или формуле?

Вызовите команду меню Условное форматирование/Создать правило/Использовать формулу для определения отформатированных ячеек.

  1. в поле « Форматировать значения, для которых следующая формула является истинной » нужно ввести =$C7=$E$8 (в ячейке Е8 находится значение В работе ). …
  2. нажать кнопку Формат ;
  3. выбрать вкладку Заливка ;

Как закрасить ячейку по формуле?

Как выделить ячейку цветом в Excel с помощью формулы

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

Как закрасить часть ячейки в Excel?

Ниже перечислены шаги, которые необходимо выполнить, чтобы окрасить ячейки в нужный цвет:

  1. Выделить ячейки левой кнопкой мыши;
  2. Кликнуть по выделению правой кнопкой мыши;
  3. В появившемся контекстном меню кликнуть по строчке «Формат ячеек… …
  4. В появившемся окне формы настроек следует выбрать вкладку «Заливка»;

Как быстро закрасить ячейку в Excel?

На вкладке «Главная» в группе «Шрифт» нажмите кнопку запуска диалогового окна «Формат ячеек». Можно также нажать клавиши CTRL+SHIFT+F. В диалоговом окне «Формат ячеек» на вкладке «Заливка» в области «Цвет фона» выберите нужный цвет фона.

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

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

Как выделить строку в Excel цветом по условию?

Как выделять ячейки красным цветом по условию

  1. Выделите диапазон ячеек A2:A15 (то есть список номеров клиентов) и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило». …
  2. В этот раз в поле ввода введите формулу:
  3. Щелкните на кнопку «Формат» и укажите красный цвет на вкладке «Заливка».
Читайте так же:
Как изменить количество листов по умолчанию в Excel?

Как выделить в Экселе строки с определенным словом?

(3) Если вы хотите выделить строки, если ячейки начинаются с определенного текста, вам необходимо ввести = ЛЕВЫЙ (B2,5 $) = «Яблоко»; или чтобы выделить строки, если ячейки заканчиваются определенным текстом, введите = ВПРАВО (B2,5 $) = «Яблоко». 4. Теперь открывается диалоговое окно Формат ячеек.

Как сделать так что бы ячейка меняла цвет?

Нанесение рисунка или процесс заполнения

  1. Выберите ячейку или диапазон ячеек, которые нужно отформатировать.
  2. На вкладке Главная нажмите кнопку диалогового окна Формат ячеек или просто нажмите CTRL+SHIFT+F.
  3. На вкладке Заливка выберите нужный цвет в разделе Цвет фона.

Как выделить ячейки участвующие в формуле?

Выделить ячейки, содержащие формулы, можно с помощью стандартного инструмента EXCEL Выделить группу ячеек…, или из меню: на вкладке Главная, в группе Правка, нажмите стрелку рядом с командой Найти и выбрать, затем выберите в списке команду Формулы.

Как прописать формулу в условном форматировании?

Условное форматирование при помощи формул

  1. Выбрать инструмент: «Главная»-«Стили»-«Условное форматирование»-«Управление правилами».
  2. В появившемся окне «Диспетчер правил условного форматирования» нажать на кнопку «Создать правило».
  3. В списке опций «Выберите тип правила:» выберите опцию «Использовать формулу для определения форматируемых ячеек».

Как разделить ячейку в Excel по диагонали и закрасить?

Диагональное деление ячейки Excel.

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

Как закрасить ячейку в таблице?

Как добавлять и менять цвет заливки

  1. Вы выберите ячейки, в которых вы хотите добавить или изменить цвет заливки.
  2. На вкладке «Таблицы» в области «Стилитаблиц» щелкните стрелку рядом с кнопкой «Заливка».
  3. В меню «Заливка» выберите нужный цвет.

Как разбить ячейку в Excel по диагонали?

Разделить ячейку по диагонали в Excel

  1. Щелкните правой кнопкой мыши указанную ячейку, которую вы разделите по диагонали, и выберите Формат ячеек из контекстного меню. …
  2. В диалоговом окне «Формат ячеек» нажмите, чтобы включить Граница вкладку, щелкните, чтобы выделить в Граница раздел, а затем щелкните OK кнопка.

Закрасить ячейку по условию или формуле

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

Инструкция для Excel 2010

Как это сделать в Excel 2007

ВКЛЮЧИТЕ СУБТИТРЫ!
Выделим ячейки с ценами заказов и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберем «Создать правило».
создаем правило
Выберем четвертый пункт, позволяющий сравнивать текущие значения со средним. Нас интересуют значения выше среднего. Нажав кнопку «Формат», зададим цвет ячеек.
выше среднего
Подтверждаем наш выбор, и ячейки с ценой выше средней окрасились в голубой цвет, привлекая наше внимание к дорогим заказам.
дорогие заказы
Выделим ячейки со статусами заказов и создадим новое правило. На этот раз используем второй вариант, позволяющий проверять содержимое ячейки. Выберем «Текст», «содержит» и введем слово «Выполнен». Зададим зеленый цвет, подтверждаем, и выполненные работы у нас позеленели.
содержит текст
Ну и сделаем еще одно правило, окрашивающее просроченные заказы в красный цвет. Выделяем даты выполнения заказов. При создании правила снова выбираем второй пункт, но на этот раз задаем «Значение ячейки», «меньше», а в следующем поле вводим функцию, возвращающую сегодняшнюю дату.
сравнение дат
«ОК», и мы получили весело разукрашенную таблицу, позволяющую наглядно отслеживать ход выполнения заказов.
таблица
Обратили внимание, что статусы задаются выбором из выпадающего списка значений? Как делать такие списки, мы рассказывали в инструкции «Как в Excel сделать выпадающий список».

Читайте так же:
Как импортировать текстовый файл или лист в Excel?

Как это сделать в Excel 2003

условное форматирование 2003

ВКЛЮЧИТЕ СУБТИТРЫ!
«Условное форматирование» в меню «Формат». Тут понадобится немного больше ручной работы. Вот так будут выглядеть настройки для нашей первой задачи – закрасить ячейки со значениями больше средних.

Придется вручную ввести функцию «=СРЗНАЧ()», поставить курсор между скобками, нажать на кнопочку рядом и мышкой указать нужный диапазон.
Но принцип действий тот же самый.
Покоряйте Excel и до новых встреч!

Комментарии:
  1. Svetlana — 27.06.2015 21:28

Наконец-то я узнал, как это делается!

Виктор — 14.04.2016 17:23

Здравствуйте, а можно сделать условное форматирование столбца А с фразами по условию «Текст —- содержит» по нескольким словам, а лучше по столбцу В, состоящего из слов?

salam — 19.05.2016 16:24

Можно ли выделить ячейку B2, если ячейка A2 не пуста?

Федя — 16.11.2016 14:39

Как установить цвет на определенное значение в одной ячейке, например — если я введу 5 — она станет красной, если я введу 4 — она станет зеленой

Оля — 03.05.2017 12:12

Подскажите, пожалуйста, как залить цвет разными оттенками в столбце, если, например, если 100% — зеленый, 95 — зеленый, но светлее, 75 — еще светлее и т.д. Заранее спасибо.

Дмитрий — 20.02.2019 08:02

Ольга — 20.08.2020 10:37

а как сделать зависимость заливки ячейки из «срок исполнения» от статуса? например, срок меньше сегодня, но он выполнен, не надо подкрашивать. чтобы лишние красные сроки не отвлекали. HELP! очень надо (((

Макс — 11.01.2021 21:26

Очень ясно, что здесь происходит. А что, если все было бы наоборот? Как подставить в ячейку определенное значение (текст), если она окрашена в определенный цвет? Вставляем такое условие в формулу.

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Эксель: Счетесли критерий формула. Есть возможность задать определенные условия, называемые критериями. Это происходит благодаря встроенным функциям. Самые распространенные из них СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН. Можно заметить, что их объединяет слово Если. Как они работают, рассмотрим далее.
СЧЕТЕСЛИ.

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

=СЧЁТЕСЛИ(диапазон;критерий)
Здесь мы видим два аргумента. «диапазан»- задает адрес ячеек с которыми необходимо проводить расчеты. «критерий»- задает условие, которое должна содержать ячейка, для того что бы быть включенной в расчет. При этом для указания критерия можно использовать следующие знаки: «» («больше»), «=» («равно»), «<>» («не равно»).
Например, если задать выражение «15000».
После того, как все вышеуказанные манипуляции произведены, нажимаем на кнопку «OK».

Читайте так же:
Как защитить форматирование ячеек, но разрешить ввод данных только в Excel?

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

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

Она также относится к статистической группе операторов. Задачей СЧЁТЕСЛИМН является подсчет ячеек в указанном массиве, которые удовлетворяют определенному набору условий. Именно тот факт, что можно задать не один, а несколько параметров, и отличает этого оператора от предыдущего. Синтаксис следующий:
=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)

«Диапазон условий» идентичен первому аргументу вышеуказанного оператора. То есть, это ссылка на область, в которой будут подсчитаны клетки, удовлетворяющие заданным условиям. Этот оператор позволяет указать несколько таких областей одновременно.

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

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

Но при этом в качестве соответствующих аргументов «Условие» следует указывать разные критерии.
На примере все той же таблицы с недельной выручкой магазинов посмотрим, как это работает. Нам нужно узнать количество дней недели, когда доход во всех указанных торговых точках достигал установленной для них нормы. Нормы выручки следующие:
• Магазин 1 – 14000 рублей;
• Магазин 2 – 15000 рублей;
• Магазин 3 – 24000 рублей;
• Магазин 4 – 11000 рублей;
• Магазин 5 – 32000 рублей.

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

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

3. Вслед за выполнением вышеуказанного алгоритма действий открывается окно аргументов СЧЁТЕСЛИМН.
В поле «Диапазон условия1» следует ввести адрес строки, в которой расположены данные по выручке Магазина 1 за неделю. Для этого ставим курсор в поле и выделяем соответствующую строку в таблице. Координаты отображаются в окне.

Учитывая, что для Магазина 1 дневная норма выручки составляет 14000 рублей, то в поле «Условие 1» вписываем выражение «>14000».
В поля «Диапазон условия2 (3,4,5)» следует внести координаты строк с недельной выручкой соответственно Магазина 2, Магазина 3, Магазина 4 и Магазина 5. Действие выполняем по тому же алгоритму, что и для первого аргумента данной группы.

В поля «Условие2», «Условие3», «Условие4» и «Условие5» вносим соответственно значения «>15000», «>24000», «>11000» и «>32000». Как нетрудно догадаться, эти значения соответствуют интервалу выручки, превышающую норму для соответствующего магазина.
После того, как был произведен ввод всех необходимых данных (всего 10 полей), жмем на кнопку «OK».

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

3. Открывается уже знакомое нам окошко аргументов оператора СЧЁТЕСЛИМН. Ставим курсор в поле «Диапазон условия1» и, произведя зажим левой кнопки мыши, выделяем все ячейки, в которых содержится выручка по дням Магазина 1.
Они расположены в строке, которая так и называется «Магазин 1».

Читайте так же:
Как найти все подчеркнутые тексты в диапазоне или на всем листе в Excel?

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

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

В поле «Диапазон суммирования» нужно указать область, значения которой, отвечающие указанным критериям, будут суммироваться. В нашем случае это значения выручки строки «Магазин1». Выделяем соответствующий массив элементов листа.
После того, как произведено введение всех указанных данных, жмем на кнопку «OK».

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Завершим изучение операторов, которые используют критерии, остановившись на функции СУММЕСЛИМН. Задачей данной математической функции является суммирование значений указанных областей таблицы, отобранных по нескольким параметрам. Синтаксис указанного оператора таков:
=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)
«Диапазон суммирования» — это аргумент, являющийся адресом того массива, ячейки в котором, отвечающие определенному критерию, будут складываться.
«Диапазон условия» — аргумент, представляющий собой массив данных, проверяемый на соответствие условию;
«Условие» — аргумент, представляющий собой критерий отбора для сложения.

Данная функция подразумевает операции сразу с несколькими наборами подобных операторов.
Посмотрим, как данный оператор применим для решения задач в контексте нашей таблицы выручки от реализации в торговых точках. Нам нужно будет подсчитать доход, который принес Магазин 1 за период с 09 по 13 марта 2017 года. При этом при суммировании дохода должны учитываться только те дни, выручка в которых превысила 14000 рублей.
1. Снова выделяем ячейку для вывода итога и нажимаем на пиктограмму «Вставить функцию».

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)

3. Производится запуск окошка аргументов оператора, наименование которого было указано выше.
Устанавливаем курсор в поле «Диапазон суммирования». В отличие от последующих аргументов, этот единственный в своем роде и указывает на тот массив значений, где будет производиться суммирование подошедших под указанные критерии данных.

Затем выделяем область строки «Магазин1», в которой размещены значения выручки по соответствующей торговой точке.
После того, как адрес отобразился в окне, переходим к полю «Диапазон условия1». Тут нам нужно будет отобразить координаты строки с датами. Производим зажим левой кнопки мыши и выделяем все даты в таблице.

Ставим курсор в поле «Условие1». Первым условием является то, что нами будут суммироваться данные не ранее 09 марта. Поэтому вводим значение «>08.03.2017».
Перемещаемся к аргументу «Диапазон условия2».

Тут нужно внести те же координаты, которые были записаны в поле «Диапазон условия1». Делаем это тем же способом, то есть, путем выделения строчки с датами.
Устанавливаем курсор в поле «Условие2».

Вторым условием является то, что дни, за которые будет суммироваться выручка, должны быть не позже 13 марта. Поэтому записываем следующее выражение: «14000».
После выполнения последнего действия нажимаем на кнопку «OK».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector