Evolcom.ru

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

Как подсчитать количество ячеек / строк до первого пробела в Excel?

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

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

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

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

Итак, давайте начнем. Для этого перейдите в редактор Visual Basic :

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

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

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

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

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

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

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

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

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

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 не считает изменение цвета ячейки редактированием формулы. В связи с этим, если это критично, то можно внести изменение в саму формулу, просто добавив функцию, которая постоянно пересчитывается и при этом не повлияет на определение цвета ячейки. Например, указать функцию определения текущей даты, умноженную на ноль.
В нашем случае функция будет выглядеть следующем образом.

=ЦветНом(A1)+Цвет()*0

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

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

Читайте так же:
Как получить максимальное / минимальное количество видимых ячеек только в Excel?

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

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

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

Мы вычислим количество с помощью функции СЧЁТ

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

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

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

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

Excel Formulas — Подсчитать количество ячеек, содержащих определенный текст

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

«граф

Синтаксис используемой функции

Функция COUNTIF измеряет количество ячеек, которые соответствуют определенным критериям.

Экспликация

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

формула

Как работает данная формула?

В приведенном выше примере вместо интервала определено имя интервала «Invoice_No». Функция COUNTIF подсчитывает ячейки, в которых текст содержит букву «w».

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

«граф

Экспликации

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

формула

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

Как вы можете видеть выше, было определено имя диапазона «Invoice_No», а не диапазон. В этом случае единственным отличием является критериальная переменная.

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

«граф

формула

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

В приведенном выше примере мы использовали имя переменной и критериальную переменную. Подстановочный знак «*» использовался для идентификации «w» в любой позиции текста. С помощью оператора & мы объединили переменную * и переменную критерия D7.

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

Количество нет. ячеек, содержащих определенный текст (с учетом регистра), используя переменную критерия

«граф

Синтаксис используемых функций

Функция SUMPRODUCT используется для умножения соответствующих компонентов в данных массивах и возвращает сумму этих продуктов.
Функция ISNUMBER возвращает True, если значение относится к числу.
Функция FIND используется для поиска одной текстовой строки во второй текстовой строке и возврата номера начальной позиции первой текстовой строки из первого символа второй текстовой строки.

формула

Как действует эта формула

Внутри формулы ISNUMBER (Find (D7, Employee)) работает как -.

Функция FIND возвращает номер позиции, в которой был найден текст (хранится в переменной D7), а функция ISNUMBER преобразует его в True или False и создает массив следующего вида — -.

Поэтому двойное отрицание (-) преобразует True и False в 1 и 0, которые затем можно сложить в SUMPRODUCT для получения результата.

Количество нет. ячеек, не содержит конкретного текста

«граф

Экспликация :

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

формула

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

В приведенном выше примере вместо диапазона было определено имя диапазона «Номер счета-фактуры». Функция COUNTIF подсчитывает ячейки, в которых буква «w» отсутствует в тексте.

Количество нет. ячеек, не содержит конкретного текста, исключая пробел

«граф

Синтаксис применяемых функций.

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

Экспликация

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

формула

Как действует эта формула?

В приведенном выше примере функция COUNTIFS подсчитывает ячейки, которые не содержат «w» ни в одной позиции в диапазоне «Invoice_No» и которые удовлетворяют другому критерию, т.е. ячейка не содержит никаких символов.

голоса
Рейтинг статьи
Читайте так же:
Как посчитать, сколько ячеек содержат определенный текст или значение в Excel?
Ссылка на основную публикацию
Adblock
detector