Счетесли в excel vba

Обновлено: 04.07.2024

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

Пробовал просто ввести в ячейку формулу листа (Счётесли) через макрос, но выдает ошибку "type mismatch", которую не смог устранить.

Формула листа которую хотел применить.

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

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

Подсчитать количество элементов массива, удовлетворяющих условию
Здравствуйте! Мне срочно нужно выполнить работу по информатике (для девушки), а в Паскале не.

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

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

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

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

ActiveCell.FormulaR1C1 = _
"=COUNTIF(Main!R[19464]C[-9]:R[19489]C[-9],Main!R[19471]C[-9])"

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

Файл-оригинал с кодом прикрепил,

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

Есть ли (наверняка ведь есть) аналог формулы листа СЧЁТЕСЛИ в VBA и как его реализовать?

Ай, я вовсе забыл что там пароль стоит.
Отправлю повторно (без пароля).
Проблемный код в первом листе под одноименной кнопкой "Проблемный код".

(Там еще inputbox вылезет, спросит номер последней строки диапазона, но можно просто жжать ОК)

Макрос предназначался чтобы выбрать из данных записи клиента начиная с активной строки до указанной строки (или до конца) и составить из них отделную таблицу в новой книге.

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

=СЧЁТЕСЛИ(где нужно искать;что нужно найти)

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

Имя аргумента

диапазон (обязательный)

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

критерий (обязательный)

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

Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32".

В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.

Примеры

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

Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст "яблоки" (значение ячейки A2) и "апельсины" (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, не равным 75, в ячейках В2–В5. Знак амперсанда (&) объединяет оператор сравнения "<>" (не равно) и значение в ячейке B4, в результате чего получается формула =СЧЁТЕСЛИ(B2:B5;"<>75"). Результат — 3.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 3.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак "*" обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами "ки", в диапазоне A2–A5. Подставочный знак "?" обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;"длинная строка"&"еще одна длинная строка").

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

На вкладке формулы Microsoft Excel мы знаем, чтов категории " Дополнительные функции > " существует функция СЧЁТЕСЛИ, которая подсчитывает количество ячеек в диапазоне, соответствующих заданному условию. Условия для этой функции ограничены текстом или числами. Однако с помощью VBA можно создать функцию для подсчета количества ячеек с другими критериями, такими как цвет ячейки.

Дополнительные сведения

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

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

Ниже приведены действия по созданию функции UDF для подсчета цвета ячеек.

Откройте Microsoft Excel, а затем нажмите клавиши ALT + F11, чтобы открыть окно редактора Visual Basic (VBE).

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

Закройте окно VBE, чтобы вернуться в Excel.

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

В ячейке D3 Запишите функцию:

В аргументе "range_data" выберите ячейку C2 для C51.

В аргументе "критерии" выберите элемент F1.

Нажмите клавишу ВВОД. Результат в ячейке F2 — 6. Это означает, что число ячеек, затененных синим цветом, равно 6.

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

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

Шаг 1: сохранение книги

а. Выберите файл, а затем Сохранить как. (При необходимости нажмите кнопку Обзор ).

б. Выберите надстройку Excel (. xlam) в виде формата и присвойте файлу имя, например каунткколор.

Вы можете сохранить файл надстройки в любом месте. Но для того чтобы она была указана в качестве надстройки в программе Excel, сохраните ее в расположении по умолчанию. В Windows 7 расположением по умолчанию для любой версии Microsoft Excel является: К:\усерс\раддини\аппдата\роаминг\микрософт\аддинс

Шаг 2: Установка надстройки

а. Откройте Microsoft Excel на компьютере, на котором необходимо установить надстройку.

б. Откройте диалоговое окно надстройки, выбрав надстройки Excel для Excel 2013 и более поздних версий на вкладке разработчик . (надстройки в Excel 2010.)

в. В диалоговом окне надстройки нажмите кнопку Обзор.

г. Перейдите к расположению файла, в котором сохраняется файл надстройки (например, на USB-диске или в облачной папке). Выберите файл и нажмите кнопку Открыть.

д. В диалоговом окне надстройки убедитесь, что флажок Надстройка снят. После этого нажмите кнопку ОК.

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

Заявление об отказе от контента решений сообщества

КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НЕ СОДЕРЖАТ НИКАКИХ ПРЕДСТАВЛЕНИЙ О ПРИГОДНОСТИ, НАДЕЖНОСТИ ИЛИ ТОЧНОСТИ ИНФОРМАЦИИ И СВЯЗАННОЙ С НЕЙ ГРАФИКИ. ВСЯ ТАКАЯ ИНФОРМАЦИЯ И СВЯЗАННАЯ С НЕЙ ГРАФИКА ПРЕДОСТАВЛЯЮТСЯ БЕЗ КАКИХ-ЛИБО ГАРАНТИЙ. КОРПОРАЦИЯ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКИ НАСТОЯЩИМ СНИМАТЬИ ВСЕ ГАРАНТИИ И УСЛОВИЯ, ОТНОСЯЩИЕСЯ К ЭТОЙ ИНФОРМАЦИИ И СВЯЗАННЫМ ГРАФИЧЕСКИМ ГРАФИКАМ, ВКЛЮЧАЯ ВСЕ ПОДРАЗУМЕВАЕМЫЕ ГАРАНТИИ И УСЛОВИЯ ДЛЯ ОБЕСПЕЧЕНИЯ СООТВЕТСТВИЯ ТРЕБОВАНИЯМ, ПРИГОДНОСТИ ДЛЯ ОПРЕДЕЛЕННЫХ ЦЕЛЕЙ, ВОРКМАНЛИКЕ УСИЛИЯ, ДОЛЖНОСТИ И НЕНАРУШЕНИЯ ПРАВ. ВЫ ЯВНО СОГЛАСНЫ, ЧТО В СЛУЧАЕ ОТСУТСТВИЯ У КОРПОРАЦИИ МАЙКРОСОФТ И/ИЛИ ЕЕ ПОСТАВЩИКОВ НЕ НЕСЕТ ОТВЕТСТВЕННОСТИ ЗА ЛЮБЫЕ ПРЯМЫЕ, НЕПРЯМЫЕ, ПУНИТИВЕ, СЛУЧАЙНЫЙ, ОСОБЫЙ, КОСВЕННЫЙ УЩЕРБ ИЛИ ЛЮБОЙ УЩЕРБ, ВКЛЮЧАЯ, БЕЗ ОГРАНИЧЕНИЙ, УЩЕРБ ОТ ПОТЕРИ ИСПОЛЬЗОВАНИЯ, ДАННЫХ ИЛИ ПРИБЫЛИ, КОТОРЫЕ СВЯЗАНЫ С ИСПОЛЬЗОВАНИЕМ ИЛИ НЕВОЗМОЖНОСТЬЮ ИСПОЛЬЗОВАТЬ ИНФОРМАЦИЮ И СВЯЗАННУЮ С НИМИ ГРАФИКУ, В ЗАВИСИМОСТИ ОТ ДОГОВОРА, НАРУШЕНИЯМ, НЕБРЕЖНОСТЬЮ, ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТИ ИЛИ ИНЫМ СПОСОБОМ, ДАЖЕ ЕСЛИ У КОРПОРАЦИИ МАЙКРОСОФТ ИЛИ ЕЕ ПОСТАВЩИКА БЫЛО РЕКОМЕНДОВАНО ВЕРОЯТНОСТЬ УЩЕРБА.

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

Для удобства, можно дать им имена, чтобы потом использовать их в формулах и ссылках. Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен - Создать (Formulas - Name Manager - Create) . Также можно превратить таблицы в "умные" с помощью сочетания клавиш Ctrl + T или кнопки Форматировать как таблицу на вкладке Главная (Home - Format as Table) .

Подсчет количества совпадений

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

В английской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2))

Давайте разберем ее поподробнее, ибо в ней скрыто пару неочевидных фишек.

Во-первых, функция СЧЁТЕСЛИ (COUNTIF) . Обычно она подсчитывает количество искомых значений в диапазоне ячеек и используется в следующей конфигурации:

=СЧЁТЕСЛИ( Где_искать ; Что_искать )

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

Во-вторых, функция СУММПРОИЗВ (SUMPRODUCT) здесь выполняет две функции - суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости нажимать сочетание клавиш Ctrl + Shift + Enter . Формула массива необходима, чтобы функция СЧЁТЕСЛИ в режиме с двумя аргументами-диапазонами корректно отработала свою задачу.

Вывод списка совпадений формулой массива

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

В английской версии это будет, соответственно:

Логика работы этой формулы следующая:

фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере до этого, ищет совпадения элементов из первого списка во втором
фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) проверяет, не найдено ли уже текущее совпадение выше
и, наконец, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент

Вывод списка совпадений с помощью слияния запросов Power Query

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

Для начала, нам необходимо загрузить наши таблицы в Power Query. Для этого выделим первый список и на вкладке Данные (в Excel 2016) или на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) жмем кнопку Из таблицы/диапазона (From Table) :

Excel превратит нашу таблицу в "умную" и даст ей типовое имя Таблица1. После чего данные попадут в редактор запросов Power Query. Никаких преобразований с таблицей нам делать не нужно, поэтому можно смело жать в левом верхнем углу кнопку Закрыть и загрузить - Закрыть и загрузить в. (Close & Load To. ) и выбрать в появившемся окне Только создать подключение (Create only connection) :

Затем повторяем то же самое со вторым диапазоном.

И, наконец, переходим с выявлению совпадений. Для этого на вкладке Данные или на вкладке Power Query находим команду Получить данные - Объединить запросы - Объединить (Get Data - Merge Queries - Merge) :

В открывшемся окне делаем три вещи:

выбираем наши таблицы из выпадающих списков
выделяем столбцы, по которым идет сравнение
выбираем Тип соединения = Внутреннее (Inner Join)

После нажатия на ОК на экране останутся только совпадающие строки:

Ненужный столбец Таблица2 можно правой кнопкой мыши удалить, а заголовок первого столбца переименовать во что-то более понятное (например Совпадения). А затем выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load) :

Если значения в исходных таблицах в будущем будут изменяться, то необходимо не забыть обновить результирующий список совпадений правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 .

Макрос для вывода списка совпадений

Само-собой, для решения задачи поиска совпадений можно воспользоваться и макросом. Для этого нажмите кнопку Visual Basic на вкладке Разработчик (Developer) . Если ее не видно, то отобразить ее можно через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) .

В окне редактора Visual Basic нужно добавить новый пустой модуль через меню Insert - Module и затем скопировать туда код нашего макроса:

Воспользоваться добавленным макросом очень просто. Выделите, удерживая клавишу Ctrl , оба диапазона и запустите макрос кнопкой Макросы на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F8 . Макрос попросит указать ячейку, начиная с которой нужно вывести список совпадений и после нажатия на ОК сделает всю работу:

Более совершенный макрос подобного типа есть, кстати, в моей надстройке PLEX для Microsoft Excel.

массив В3:В99 содержит некий список.
как прописать код в VBA
СЧЁТЕСЛИ(B5:$B$11;$B$11)
при условии что конечный адрес должен быть не конкретным , а динамическим ?

*в файле пример и более подробная информация

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

Написать программу в VBA арифметической прогрессии в динамическом массиве
помогите пожалуйста написать эту программу: Арифметическая прогрессия, предполагаем, что формулу.

VBA суммирование в диапазоне
Как, через Userform можно сделать подсчет количества названий в таблице с диапазоном C4:I12, чтобы.

VBA Сумма чисел в диапазоне
Как сделать так, чтобы он считал сумму выделенного диапазона, но на две строки ниже? Sub Сумма().

Если правильно понял задачу, то так (можно обойтись и без переменной)

P.S. Что ищем оставил в виде константы, т.к. в вопросе этот момент был проигнорирован.

1) а почему мы обращаемся по адресу 1, 1 если ячейка А1 нигде и никак не фигурирует в задании ?

2) xlA1 - в данном случае пояснение, А вообще - что это такое ?

напишите так и оставьте в покое третий и необязательный аргумент, который Вы приняли за комментарий(пояснение)

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

0mega, Без обид, но справка действительно лучше моих об'яснений.

Но если вкратце, то в формуле нам нужна абсолютная ссылка на ячейку, а в ячейке - относительная.

Поэтому, либо самописное преобразование, либо конвертация [1], либо адрес [2]
Т.к. при использовании свойства .Address можно указать нужна ли абсолютная ссылка на строку, столбец и указать стиль ссылки(нотацию) я просто перестраховался и написал .Адрес(СтрокаАбсолютная:=Истина, СтолбецАбсолютный:=Истина, СтильСсылки:=xlA1), где вместо True написал 1, исключительно для ускорения времени. А сейчас вспомнил, что всё это нафиг не нужно и можно использовать просто .Address

Почему Вы решили что с стороны могут быть какие-то обиды?!
Я периодически обращаюсь сюда с вопросами и всегда получаю нужную информацию..
Ваш ник на слуху и Вы часто присутствуете среди отвечающих на мои вопросы.
Спасибо за уделенное время.
Сейчас как раз читаю Help

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

VBA randomiz & rnd() / Рандомные числа в диапазоне [-5 до 5]
Добрый день. Вообще решаю задачу из института. Дан одномерный массив действительных чисел.

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

Задача в VBA в диапазоне В2:Ф21 подсчитать количество элементов (циклы)
В диапазоне В2:Ф21 из вещественных чисел подсчитать количество элементов меньших 20,значения.

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

Счетесли в excel vba

Примеры

Распространенные неполадки

Рекомендации

Дополнительные сведения

Дополнительные сведения

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

Заявление об отказе от контента решений сообщества

Подсчет количества совпадений

Вывод списка совпадений формулой массива

Вывод списка совпадений с помощью слияния запросов Power Query

Макрос для вывода списка совпадений