Evolcom.ru

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

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

Подсчитайте количество ячеек, в которых текст зачеркнут

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

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

Вот мой сценарий работы:

Результат находится в L20, а управление — в сценарии.

2 ответа

  • Подсчет количества разных ячеек в VBA

Я хочу подсчитать количество различных ячеек, которые выбраны с помощью VBA. Рассмотрим, что мы выбираем пять различных ячеек — D5, C2, E7, A4, B1. Есть ли способ вычислить это количество ячеек? Во-вторых, как я могу получить данные из этих ячеек. Предположим, я хочу хранить его в таблице.

Подскажите, пожалуйста, как подсчитать количество ячеек с текстом и числами (или текст + числа)? Я пробовал =countif(A2:A2000,1=1) (используя один общий критерий, типа 1=1 всегда), но он показывает ноль (0) для текстового столбца.

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

Модификации вашего кода

  1. Теперь используйте getActiveSheet(), чтобы получить лист, с которого вызывается функция, и getDataRange(), чтобы получить весь диапазон листа, на котором находятся данные.
  2. Используйте getFontLines() для всего диапазона вместо итерации ячейки за ячейкой и вызова getFontLine() для каждой ячейки. Это приводит к более эффективному коду (см.: Рекомендации — минимизация обращений к другим службам).
  3. Вызов setValue() был удален. Если вы хотите, чтобы результат этой пользовательской функции был помещен в ячейку L20, просто замените ее содержимое следующей формулой: =fontLine() . Значение, возвращаемое пользовательской функцией, будет установлено в качестве значения ячейки.

Пример выполнения

example of the execution

Оговорки

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

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

Читайте так же:
Как показать или скрыть все объекты (изображения / диаграммы / фигуры / комментарии) в Excel?

Я пытаюсь понять, как создать sql-запрос к DB, который будет выполнять следующую задачу: подсчитать количество доменов, которые существуют в моей PHP-таблице, но не существуют в таблице DB. Структура таблицы.

У меня есть файл Excel, в котором только два столбца имеют значения (A — номер заказа, B — текст/комментарий), и мне нужно выполнить подсчет: 1 — подсчитать количество ячеек в B, которые имеют значения.

Я хочу подсчитать количество различных ячеек, которые выбраны с помощью VBA. Рассмотрим, что мы выбираем пять различных ячеек — D5, C2, E7, A4, B1. Есть ли способ вычислить это количество ячеек?

Как подсчитать количество заполненных ячеек (с комбинацией текст/число или текст+число)? Я пробовал =countif(A2:A2000,1=1) (с общим критерием, например, 1=1 всегда), но он показывает ноль (0).

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

Мне нужно подсчитать количество пустых ячеек и ячеек, содержащих число меньше 60 в столбце O. Каждый раз, когда я пробую CountIf(Range(O:O), ), я получаю значение 1048539. Я ожидаю гораздо большего.

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

Если в формуле SUM (например, SUM(A1:A3, A5)) у меня есть прерывистая строка ячеек из столбца, есть ли формула, которая подскажет мне, что входит в сумму?

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

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

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

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

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

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

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

  • Подсчет и суммирование по цвету при ручном раскрашивании ячеек

Как считать и суммировать по цвету на листе Excel

Предположим, у вас есть таблица заказов компании, в которой ячейки в столбце «Поставка» окрашены в соответствии с их значением: «Срок исполнения через X дней» — оранжевый, «Поставка» — зеленый, «С опозданием» — красный.

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

  1. Откройте книгу Excel и нажмите Alt+F11, чтобы запустить редактор Visual Basic for Applications (VBA).
  2. Правой кнопкой мыши кликните по имени Вашей рабочей книги в области Project – VBAProject, которая находится в левой части экрана, далее в появившемся контекстном меню нажмите Insert > Module.Подсчет и суммирование ячеек по цвету в Excel
  3. Вставьте на свой лист вот такой код:
  1. Сохраните рабочую книгу Excel в формате .xlsm (рабочая книга Excel с поддержкой макросов). Если вы не очень уверенно чувствуете себя с VBA, вы можете прочитать подробные пошаговые инструкции и множество полезных советов в рабочей книге Как вставить и запустить код VBA в рабочей книге Excel.
  2. После того, как вы проделали всю закулисную работу, выделите ячейки, в которые вы хотите вставить результат, и введите в них функцию CountCellsByColor:
Читайте так же:
Как предотвратить или отключить ввод текста в поле со списком в Excel?

CountCellsByColor( диапазон , код_цвета )

В этом примере мы используем формулу =CountCellsByColor(F2:F14,A17), где F2:F14 — диапазон, содержащий цветные ячейки, которые вы хотите подсчитать. В нашем примере ячейка A17 имеет красный цвет заливки.

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

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

Если цветные ячейки содержат числовые данные (например, столбец Qty в нашей таблице), вы можете суммировать значения в соответствии с цветом выбранной ячейки с помощью аналогичной функции SumCellsByColor:

SumCellsByColor( диапазон , код_цвета )

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

Как показано на рисунке ниже, мы используем формулу

Где D2:D14 — диапазон, A17 — ячейка с образцом цвета.

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

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

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

На самом деле это нормальное поведение макросов Excel, сценариев VBA и функций, определяемых пользователем (UDF). Дело в том, что все эти функции вызываются только при изменении данных на листе, но Excel не считает изменение цвета шрифта или заполнение ячеек изменением данных. Поэтому после ручного изменения цвета ячеек просто поместите курсор в любую ячейку и нажмите F2, а затем Enter, после чего сумма и количество будут обновлены. Именно так следует поступать при работе с макросами, с которыми вы столкнетесь далее в этой статье.

Считаем сумму и количество ячеек по цвету во всей книге

Приведенный ниже сценарий был написан в ответ на комментарии читателей (также нашим гуру Excel) и подсчитывает количество ячеек с определенным цветом на всех листах в книге. Итак, вот этот код:

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

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

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

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

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

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

Функции, которые считают количество по цвету:
  • CountCellsByColor( range , color_code ) — подсчитывает ячейки с указанным цветом заливки. В приведенном выше примере мы используем эту формулу для подсчета количества ячеек по их цвету:

Где F2:F14 — выделенная область, A17 — ячейка с требуемым цветом заливки.

Функции, которые суммируют значения по цвету ячейки:
  • SumCellsByColor( range , color_code ) — вычисляет сумму ячеек с указанным цветом заливки.
  • SumCellsByFontColor( range , color_code ) — вычисляет сумму ячеек с заданным цветом шрифта.
Функции, которые возвращают код цвета:
  • GetCellFontColor( ячейка ) – возвращает код цвета шрифта в выбранной ячейке.
  • GetCellColor( ячейка ) – возвращает код цвета заливки в выбранной ячейке.Подсчет и суммирование ячеек по цвету в Excel

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

Как посчитать количество и сумму ячеек по цвету, раскрашенных при помощи условного форматирования

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

Конечно, в Интернете можно найти тонны кода VBA, который пытается это сделать, но весь этот код (по крайней мере, те случаи, которые я нашел) не обрабатывает правила условного форматирования, такие как

  • Формирует все ячейки на основе их значений;
  • Формирует только самые высокие или самые низкие значения;
  • Формирует только значения, которые выше или ниже среднего;
  • Формирует только уникальные значения или дубликаты.
Читайте так же:
Как преобразовать дату в день недели, месяц, название года или число в Excel?

Кроме того, почти все эти коды VBA имеют ряд особенностей и ограничений, из-за которых они могут некорректно работать с какой-либо конкретной книгой или типами данных. Тем не менее, вы можете попытать счастья и поискать идеальное решение в Google, и если вы его найдете, пожалуйста, вернитесь сюда и сообщите нам!

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

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

  1. Добавьте код, приведённый выше, на Ваш лист, как мы делали это в первом примере.
  2. Выберите диапазон (или диапазоны), в которых нужно сосчитать цветные ячейки или просуммировать по цвету, если в них содержатся числовые данные.
  3. Нажмите и удерживайте Ctrl, кликните по одной ячейке нужного цвета, затем отпустите Ctrl.
  4. Нажмите Alt+F8, чтобы открыть список макросов в Вашей рабочей книге.
  5. Выберите макрос SumCountByConditionalFormat и нажмите Run (Выполнить).Подсчет и суммирование ячеек по цвету в ExcelВ результате Вы увидите вот такое сообщение:

Для данного примера мы выбрали колонку Qty и получили следующие цифры:

  • Количество — это количество ячеек искомого цвета; в нашем случае это красноватый цвет, которым выделены ячейки со значением Due.
  • Sum — это сумма значений всех ячеек красного цвета в столбце Quantity, т.е. общее количество предметов со значением Due.
  • Цвет — шестнадцатеричный код цвета выделенной ячейки, в нашем случае D2.

Рабочая книга с примерами для скачивания

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

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