IT Новости из мира ПК
15 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд

Excel vba счетесли

Convert Countif to VBA

I have written the following functions into my (Excel 2010) workbook:

These are used in various subs which return values based on which check boxes are checked, such as:

I figure there has to be a way to use a for loop so I don’t have to use all these different functions each time I need to do an ifcount with a different number of variables, but I’m not very experienced with VBA, so I’m hoping I could get some help. How can I convert this so I’m not using a worksheet function, but rather actual VBA? Any help is appreciated.

After trying out some test code I thought I had it, but now with:

I’m getting an invalid call or procedure. Are the brackets not the correct way to mark as optional?

Final code slightly modified from Dirk’s answer (it wasn’t evaluating each variable if it went past v2). I seem to fail at adding code to this website, so basically what I did was create a new str variable for each «If Len» statement and then changed the evaluation to read:

Thanks for everyone’s suggestions!

Создан 30 апр. 16 2016-04-30 18:56:54 dubyarly

what you are looking for is probably ‘ParamArray a() As Variant’ to enter an unknown amount of variables. or ‘IfsCount(r As Range, v As String, Optional r2 As Range, Optional v2 As String, Optional. )’ to be able to skip parameter. or directly use ‘Application.CountIfs’ – Dirk Reichel 30 апр. 16 2016-04-30 19:09:22

I was about to say the nature of this question is really how to make a passed parameter optional. – Forward Ed 30 апр. 16 2016-04-30 19:11:32

Thanks guys, I had tried this before with the functions but apparently I was doing it wrong. I didn’t have brackets around the optionals in the application line, which threw errors. Seems to be working now, thanks. I would still like to move more away from worksheet functions (most of my code started off as this type, and as I’ve learned better ways to do things I’ve been using it a lot less). Thanks again! – dubyarly 30 апр. 16 2016-04-30 19:38:44

sometimes application function is the way to go because the built in excel function is very efficient. SOMETIMES – Forward Ed 30 апр. 16 2016-04-30 19:49:32

Well I thought I had it working. Added my new line to the original post, I must still be doing something wrong. I did change all references to the various original functions to call the new one instead. – dubyarly 30 апр. 16 2016-04-30 20:05:30

since your function is one line long and all it is doing is calling a function, why not just call the application function directly? why ad the intermediate step? – Forward Ed 30 апр. 16 2016-04-30 20:37:36

The counifs application function can already deal with multiple arguments which you were already supplying uniquely to each of your functions calls. – Forward Ed 30 апр. 16 2016-04-30 20:44:57

Trying to use «Application.WorksheetFunction.CountIfs» without defining the arguments throws «Compile error: Argument not optional». Unless you meant something else? – dubyarly 30 апр. 16 2016-04-30 21:00:38

why not skip out the whole ‘Function IfsCount(. ‘ part and instead of calling ‘IfsCount’ later on directly call every time ‘Application.CountIfs(. ‘? (I think that was his question) – Dirk Reichel 30 апр. 16 2016-04-30 21:16:58

exactly dirk, and then you dont need to worry about passing enough variables, just pass them as need from the body of your sub using application.worksheetfunction call, and forget the whole UDF. – Forward Ed 30 апр. 16 2016-04-30 21:19:41

Well if that is the best way to do it then ok, it was just too cluttered for me to read when I first wrote it. – dubyarly 30 апр. 16 2016-04-30 22:59:22

3 ответа

What you are trying, is not possible! It is simply the vba-syntax which doesn’t support something like that.

Also having optional parameters which are not variant it is not possible to detect if they where omitted or not. The only way to do what you are trying may be the evaluate function:

I will toy around with this when I get back to my computer, this may be the way to at least combine the functions. Thanks for the idea. – dubyarly 30 апр. 16 2016-04-30 22:46:19

You are welcome – Dirk Reichel 30 апр. 16 2016-04-30 23:11:15

This seems to be working. It may not be much shorter than the way I had it before, but it’s definitely cleaner and easier to read, I like having it all in one function. Thanks. – dubyarly 01 май. 16 2016-05-01 02:33:04

Читать еще:  Как нарисовать линию в excel

Skip the whole user function and call directly from the body of your if statement. You should also declare what worksheet your are working with I believe.

Создан 30 апр. 16 2016-04-30 21:25:51 Forward Ed

Oh, I did that before and it worked fine, I just wanted to make it cleaner and easier to read, and possibly learn something for other projects. My search functions all used to be index/match based until I learned how to write a proper find sub, so I’m just trying to do the same with other code. – dubyarly 30 апр. 16 2016-04-30 22:39:52

Чтение и запись значения ячейки в VBA

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

Обращение к конкретной ячейке

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

Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:

  • С помощью Range
  • С помощью Cells

Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1

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

Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе

Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале «.» (точку).

Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.

Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.

Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.

Чтение значения из ячейки

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

  • Value2 — базовое значение ячейки, т.е. как оно хранится в самом Excel-е. В связи с чем, например, дата будет прочтена как число от 1 до 2958466, а время будет прочитано как дробное число. Value2 — самый быстрый способ чтения значения, т.к. не происходит никаких преобразований.
  • Value — значение ячейки, приведенное к типу ячейки. Если ячейка хранит дату, будет приведено к типу Date. Если ячейка отформатирована как валюта, будет преобразована к типу Currency (в связи с чем, знаки с 5-го и далее будут усечены).
  • Text — визуальное отображение значения ячейки. Например, если ячейка, содержит дату в виде «число месяц прописью год», то Text (в отличие от Value и Value2) именно в таком виде и вернет значение. Использовать Text нужно осторожно, т.к., если, например, значение не входит в ячейку и отображается в виде «#####» то Text вернет вам не само значение, а эти самые «решетки».

По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.

Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат «14 марта 2001 г.». Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат «Денежный» с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.

При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка «Type mismatch». Как определить тип значения в ячейке, рассказано в следующей статье.

Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.

Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.

Запись значения в ячейку

Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.

Пример 8: Записать в ячейку A1 активного листа значение 123,45

Все три строки запишут в A1 одно и то же значение.

Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года

В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.

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

СЧЕТЕСЛИ внутри, если VBA

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

Читать еще:  Как создать список в excel 2020

Для этого примера это будет выглядеть так:

1 Ответ

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

Поместите следующее в лист модуля.

В образце изображения в F5 as,

Заполняйте как справа, так и снизу.

Вот две формулы array1, которые могут заполнить G4:O4 и E5:E6.

После того, как они были введены правильно, заполните справа и вниз на несколько строк/столбцов больше, чем необходимо немедленно, чтобы автоматически ловить новые записи, как они появляются в Sheet5!C:C.

Этот UDF, вероятно, немного сложнее, чем это должно быть. Утверждение для Each. Next, вероятно, было бы более чем адекватным. Тем не менее, я пытался написать все UDFs таким образом, чтобы они могли получить полные ссылки на столбцы для большей универсальности, не страдая задержкой вычислений.

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

Похожие вопросы:

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

Пожалуйста, помогите завершить цикл VBA внутри цикла Sub fill() Dim FirstDate As Date Dim LastDate As Date FirstDate = Range(A1).Value LastDate = Range(B1).Value For i = 0 To LastDate — FirstDate.

У меня есть код VBA, который вставляет формулу ниже в ячейку. Я хочу объединить A7 и строку EBF в одну строку внутри формулы VLOOKUP, но она, похоже, продолжает давать мне ошибку. Вот что мне нужно.

Я написал эту простую подпроцедуру для Excel VBA. Я намеренно использовал двоеточие (:) для разделения операторов. Почему это не работает? Sub ConditionInsideLoop() cb = 0: cd = 1: For ca = 1 To 5.

Мне нужно передать переменную внутри вызова IE.navigate в excel VBA. Например, мой пример приложения будет выглядеть примерно так. Enter the first name: SACHIN Enter the last name: TENDULKAR Я храню.

У меня есть формула Excel, которую я использую внутри VBA. Однако, когда я его компилирую, VBA выдает мне ошибку. Я отчасти уверен, что это из-за кавычек в моей формуле. Range(E2:E &.

Мне нужно посчитать количество целых чисел в верхней 1/3, середине 1/3 и нижней 1/3 массива (тестовый массив в C6:C28). Но я не могу заставить countif работать. Range(C1).Select.

Я создал приложение VBA в MS Excel 2010. Он имеет одну форму пользователя. Там я хотел бы добавить функцию, чтобы открыть файл (MS Word) для поддержки и FAQ целей. Я не хочу сохранять файл в.

У меня есть таблица, где мне нужно count, если значения равны A OR B OR C в A1:A7, но только если B1:B7 XYZ. Например: A XYZ G AAA B ASO C XYZ D OOO F ASO A ASO В этом примере он должен возвращать 2.

Есть ли конкретный способ поместить инструкции SQL в код VBA? Я хотел бы иметь запрос SQL, помещенный в код VBA, но если я это сделаю, запрос не будет работать. Тот же запрос отлично работает, если.


Вы здесь

Сообщение об ошибке

Функции Excel. Функция СУММЕСЛИ(), СЧЁТЕСЛИ()

В статье «Функции Excel. Функция ЕСЛИ» была рассмотрена работа со встроенной функцией ЕСЛИ в Excel, которая позволяет по условию делать тот, или иной расчет. Но у функции есть некоторые ограничения, а именно, невозможно посчитать, например сумму значений по определенному условию или подсчет кол-ва. Для этого в Excel предусмотрены две условные функции суммирования и подсчета кол-ва: СУММЕСЛИ(), СЧЁТЕСЛИ()/

Синтаксис функции СУММЕСЛИ()

Функция СУММЕСЛИ возвращает сумму значений из указанного диапазона ячеек согласно условия. Функция СУММЕСЛИ имеет следующие аргументы:

СУММЕСЛИ ( диапазон ; критерий ; диапазон_суммирования)

  • Диапазон — указывается диапазон ячеек для проверки;
  • Критерий — в критерии может быть указано число, текст или выражение. Критерий указывается следующим образом: 45, «45», «>45″,» 45″, «текст».
  • Диапазон_суммирования — необязательный аргумент. Если аргумент не указан, то суммирование производится по аргументу «Диапазон», если указан, то суммирование значений будет производиться по ячейкам указанным в этом аргументе.

Пример 1.

Разберем пример на подсчет суммы. Итак, дана матрица 4х4

Необходимо подсчитать сумму значений, которые меньше 6.

Щелкаем по свободной ячейке, добавляем функцию. Функция СУММЕСЛИ находится в категории «Математические», вносим следующие аргументы:

Жмем «ОК» и смотрим. Результат 25.

Пример 2.

Во втором примере я продемонстрирую еще один вариант подсчета.

Необходимо подсчитать, сколько всего было продано молока.

Вставляем функцию и пишем следующее

В данном варианте я поленился писать критерий и сделал ссылку на ячейку G4, если Вам не лень, то пропишите критерий в кавычках “Молоко”. Результат 79.

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

& (амперсанд) – позволяет «склеить» строки. В нашем случае, к знаку неравенства мы присоединяем строку G4. Результат работы функций:

Синтаксис функции СЧЁТЕСЛИ()

Функция СЧЁТЕСЛИ() возвращает количество ячеек(строк) удовлетворяющих указанному критерию.

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

Диапазоны и критерии задаются аналогично функции СУММЕСЛИ.

Примечание: Обратите внимание на написание функции, СЧЁТ пишется именно через Ё, а не через Е. Если написать иначе, функция не будет распознана.

Пример 3

Все та же таблица с наименованием товаров. Необходимо подсчитать кол-во строк «Молоко» в массиве.

В ячейку B14 добавляем функцию СЧЁТЕСЛИ, которая находится в категории «Статистические».

Здесь я так же поленился с вводом критерия Результат следующий:

Вот и все. Функции просты и понятны. Примеры работы в приложенном файле.

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

В этой статье мы сосредоточимся на функции СЧЕТЕСЛИ в Excel, которая предназначена для подсчета ячеек с указанным вами условием. Во-первых, мы кратко рассмотрим синтаксис и общее использование, а затем приведем ряд примеров функции СЧЕТЕСЛИ.

По сути, функция СЧЕТЕСЛИ на английском COUNTIF, идентична во всех версиях Excel, поэтому вы можете использовать примеры из этого руководства в Excel 2016, 2013, 2010 и 2007.

Синтаксис и использование функции СЧЕТЕСЛИ в Excel

Функция СЧЕТЕСЛИ в Excel используется для подсчета ячеек в пределах заданного диапазона, которые соответствуют определенному критерию или условию.

Например, вы можете использовать функцию СЧЕТЕСЛИ, чтобы узнать, сколько ячеек на вашем листе содержит число больше или меньше указанного вами числа. Другое типичное использование функции СЧЕТЕСЛИ в Excel — подсчет ячеек с определенным словом или началом с конкретной буквы (букв).

Синтаксис функции СЧЕТЕСЛИ очень прост:

Как видите, есть только 2 аргумента функции СЧЕТЕСЛИ, оба из которых обязательны:

  • диапазон – определяет одну или несколько ячеек для подсчета. Вы помещаете диапазон в формулу, как обычно, в Excel, например. A1:A20.
  • критерии – определяет условие, которое сообщает функции, которую подсчитывают ячейки. Это может быть число, текстовая строка, ссылка на ячейку или выражение (например, «10», A2, «>=10»).

Вот простейший пример функции СЧЕТЕСЛИ в Excel. Формула =СЧЁТЕСЛИ(C2:C7;»Иванов Иван») подсчитывает, сколько заявок поступало от Иванова Ивана:

Функция СЧЕТЕСЛИ в Excel – Пример использования функции СЧЕТЕСЛИ в Excel

Примечание : Критерий нечувствителен к регистру, что означает, что если вы наберете «иванов иван» в качестве критерия в приведенной выше формуле СЧЕТЕСЛИ, это приведет к такому же результату.

Функция СЧЕТЕСЛИ в Excel – примеры

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

Функция СЧЕТЕСЛИ в Excel для текста и чисел (точное совпадение)

Выше мы рассмотрели пример функции СЧЕТЕСЛИ, которая подсчитывает текстовые значения, соответствующие определенному критерию.

Вместо ввода текста вы можете использовать ссылку на любую ячейку , содержащую это слово или слова, и получить абсолютно одинаковые результаты, например: =СЧЕТЕСЛИ(С1:С7; С2).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ со ссылкой на ячейку

Аналогичные формулы СЧЕТЕСЛИ работают для чисел , также как для текстовых значений.

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел

На изображении выше формула =СЧЁТЕСЛИ(B2:B7;10) учитывает ячейки с количеством 10 в столбце D.

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

Обратите внимание, что в функции СЧЕТЕСЛИ оператор с числом всегда заключен в кавычки, например, =СЧЕТЕСЛИ(B2:B7; «>=10»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел с логическим оператором

Функция СЧЕТЕСЛИ с подстановочными знаками (частичное совпадение)

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

Предположим, у вас есть список цветов, и вы хотите узнать количество цветов, в названии которых содержится слово «синий». Поскольку эти цвета можно написать несколькими разными способами, мы вводим «*синий*» в качестве критериев поиска =СЧЕТЕСЛИ(B2:B8;»*синий*»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с частичным совпадением

Звездочка (*) используется в функции СЧЕТЕСЛИ для поиска ячеек с любой последовательностью ведущих и конечных символов, как показано в приведенном выше примере. Если вам нужно сопоставить какой-либо один символ, введите вместо него знак вопроса (?) , например, =СЧЁТЕСЛИ(A2:A7;»ст?л»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с подстановочным знаком

В данном случае функция СЧЕТЕСЛИ вернет значение 2, так как найдет «стол» и «стул».

Ссылка на основную публикацию