Как посчитать ячейки в excel vba - Word и Excel - помощь в работе с программами

Подсчет количества ячеек в диапазоне в зависимости от их содержимого методами Count, CountA и CountBlank объекта WorksheetFunction из кода VBA Excel.

Метод WorksheetFunction.Count

Определение

Определение метода Count объекта WorksheetFunction в VBA Excel:

Метод WorksheetFunction.Count подсчитывает в заданном диапазоне (массиве) количество ячеек (элементов массива), содержащих числа, и возвращает значение типа Double.

Синтаксис

Синтаксис метода Count объекта WorksheetFunction:

WorksheetFunction.Count(Arg1, Arg2, ..., Arg30)

Параметры

Параметры метода Count объекта WorksheetFunction:

Параметр	Описание
Arg1-Arg30	От 1 до 30 аргументов, которые могут содержать различные типы данных или ссылаться на них.

Примечания

Метод WorksheetFunction.Count позволяет получить количество числовых значений в диапазоне ячеек или в массиве.
При подсчете учитываются аргументы, которые являются числами, датами или текстовым представлением чисел.
Логические значения учитываются при подсчете только в том случае, если они введены непосредственно в список аргументов.

Метод WorksheetFunction.CountA

Определение

Определение метода CountA объекта WorksheetFunction в VBA Excel:

WorksheetFunction.CountA — это метод, который подсчитывает в заданном диапазоне количество непустых ячеек, и возвращает значение типа Double.

Синтаксис

Синтаксис метода CountA объекта WorksheetFunction:

WorksheetFunction.CountA(Arg1, Arg2, ..., Arg30)

Параметры

Параметры метода CountA объекта WorksheetFunction:

Параметр	Описание
Arg1-Arg30	От 1 до 30 аргументов, которые могут содержать различные типы данных или ссылаться на них.

Примечания

Метод WorksheetFunction.CountA позволяет получить количество непустых ячеек в заданном диапазоне.
Непустыми являются ячейки, которые содержат любые данные, включая значения ошибок и пустые строки ("").
Тесты показывают, что метод WorksheetFunction.CountA в массиве, созданном путем присвоения ему значений диапазона, содержащего пустые ячейки, все равно считает все элементы массива, как содержащие значения.

Метод WorksheetFunction.CountBlank

Определение

Определение метода CountBlank объекта WorksheetFunction в VBA Excel:

WorksheetFunction.CountBlank — это метод, который подсчитывает в заданном диапазоне количество пустых ячеек, и возвращает значение типа Double.

Синтаксис

Синтаксис метода CountBlank объекта WorksheetFunction:

WorksheetFunction.CountBlank(Arg1)

Параметры

Параметры метода CountBlank объекта WorksheetFunction:

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

Примечания

Метод WorksheetFunction.CountBlank позволяет получить количество пустых ячеек в заданном диапазоне.
Пустыми являются ячейки, которые не содержат никаких данных.
Также подсчитываются, как пустые, ячейки с формулами, которые возвращают пустые строки ("").
Ячейки с нулевыми значениями в подсчете не участвуют.

Примеры

Таблица для строк кода VBA Excel со ссылками на диапазон "A1:C5", а также с массивом его значений в качестве аргументов:

Примеры с WorksheetFunction.Count

Sub Primer1()

Dim n As Double, a() As Variant

n = WorksheetFunction.Count(Range(«A1:C5»))

MsgBox n ‘Результат: 8

a = Range(«A1:C5»)

n = WorksheetFunction.Count(a)

MsgBox n ‘Результат: 8

n = WorksheetFunction.Count(«раз», «два», «три», 1, 2, 3)

MsgBox n ‘Результат: 3

n = WorksheetFunction.Count(«раз», «два», «три», «1», «2», «3», 1, 2, 3)

MsgBox n ‘Результат: 6

n = WorksheetFunction.Count(Empty, Empty, 0, 0, «», «»)

MsgBox n ‘Результат: 4

n = WorksheetFunction.Count(True, False, «True», «False»)

MsgBox n ‘Результат: 2

End Sub

Метод WorksheetFunction.Count можно использовать для подсчета количества числовых значений в массиве, если он создан путем присвоения ему значений диапазона. Тогда логические значения ИСТИНА и ЛОЖЬ, если они встречаются в диапазоне, в подсчете количества числовых значений не участвуют.

Примеры с WorksheetFunction.CountA

Sub Primer2()

Dim n As Double, a() As Variant

n = WorksheetFunction.CountA(Range(«A1:C5»))

MsgBox n ‘Результат: 13

a = Range(«A1:C5»)

n = WorksheetFunction.CountA(a)

MsgBox n ‘Результат: 15

n = WorksheetFunction.CountA(«раз», «два», «три», 1, 2, 3)

MsgBox n ‘Результат: 6

n = WorksheetFunction.CountA(Empty, Empty, 0, 0, «», «»)

MsgBox n ‘Результат: 6

End Sub

Примеры с WorksheetFunction.CountBlank

Sub Primer3()

Dim n As Double, a As Range

n = WorksheetFunction.CountBlank(Range(«A1:C5»))

MsgBox n ‘Результат: 2

Set a = Range(«A1:C5»)

n = WorksheetFunction.CountBlank(a)

MsgBox n ‘Результат: 2

End Sub

Следующая статья по этой теме: VBA Excel. Методы CountIf и CountIfs.

Источник

In this Article

COUNT WorksheetFunction
Assigning a Count result to a Variable
COUNT with a Range Object
COUNT Multiple Range Objects
Using COUNTA
Using COUNTBLANKS
Using the COUNTIF Function
Disadvantages of WorksheetFunction
- Using the Formula Method
- Using the FormulaR1C1 Method

This tutorial will show you how to use the Excel COUNT function in VBA

The VBA COUNT function is used to count the number of cells in your Worksheet that have values in them. It is accessed using the WorksheetFunction method in VBA.

COUNT WorksheetFunction

The WorksheetFunction object can be used to call most of the Excel functions that are available within the Insert Function dialog box in Excel. The COUNT function is one of them.

Sub TestCountFunctino
  Range("D33") = Application.WorksheetFunction.Count(Range("D1:D32"))
End Sub

You are able to have up to 30 arguments in the COUNT function. Each of the arguments must refer to a range of cells.

This example below will count how many cells are populated with values are in cells D1 to D9

Sub TestCount()   
   Range("D10") = Application.WorksheetFunction.Count(Range("D1:D9"))
End Sub

The example below will count how many values are in a range in column D and in a range in column F. If you do not type the Application object, it will be assumed.

Sub TestCountMultiple()
   Range("G8") = WorksheetFunction.Count(Range("G2:G7"), Range("H2:H7"))
End Sub

Assigning a Count result to a Variable

You may want to use the result of your formula elsewhere in code rather than writing it directly back to and Excel Range. If this is the case, you can assign the result to a variable to use later in your code.

Sub AssignCount()
   Dim result As Integer
'Assign the variable   
   result = WorksheetFunction.Count(Range("H2:H11"))
'Show the result
   MsgBox "The number of cells populated with values is " & result
End Sub

COUNT with a Range Object

You can assign a group of cells to the Range object, and then use that Range object with the WorksheetFunction object.

Sub TestCountRange()
   Dim rng As Range
'assign the range of cells
   Set rng = Range("G2:G7")
'use the range in the  formula
   Range("G8") = WorksheetFunction.Count(rng)
'release the range object
  Set rng = Nothing
End Sub

COUNT Multiple Range Objects

Similarly, you can count how many cells are populated with values in multiple Range Objects.

Sub TestCountMultipleRanges() 
   Dim rngA As Range 
   Dim rngB as Range
'assign the range of cells 
   Set rngA = Range("D2:D10") 
   Set rngB = Range("E2:E10")   
'use the range in the formula 
Range("E11") = WorksheetFunction.Count(rngA, rngB)
 'release the range object
  Set rngA = Nothing 
  Set rngB = Nothing
End Sub

Using COUNTA

The count will only count the VALUES in cells, it will not count the cell if the cell has text in it. To count the cells which are populated with any sort of data, we would need to use the COUNTA function.

Sub TestCountA()
   Range("B8) = Application.WorksheetFunction.CountA(Range("B1:B6")) 
End Sub

In the example below, the COUNT function would return a zero as there are no values in column B, while it would return a 4 for column C. The COUNTA function however, would count the cells with Text in them and would return a value of 5 in column B while still returning a value of 4 in column C.

Using COUNTBLANKS

The COUNTBLANKS function will only count the Blank Cells in the Range of cells – ie cells that have no data in them at all.

Sub TestCountBlank()   
   Range("B8) = Application.WorksheetFunction.CountBlanks(Range("B1:B6")) 
End Sub

In the example below, column B has no blank cells while column C has one blank cell.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More

Using the COUNTIF Function

Another worksheet function that can be used is the COUNTIF function.

Sub TestCountIf()
   Range("H14") = WorksheetFunction.CountIf(Range("H2:H10"), ">0")
   Range("H15") = WorksheetFunction.CountIf(Range("H2:H10"), ">100")
   Range("H16") = WorksheetFunction.CountIf(Range("H2:H10"), ">1000")
   Range("H17") = WorksheetFunction.CountIf(Range("H2:H10"), ">10000")
End Sub

The procedure above will only count the cells with values in them if the criteria is matched – greater than 0, greater than 100, greater than 1000 and greater than 10000. You have to put the criteria within quotation marks for the formula to work correctly.

Disadvantages of WorksheetFunction

When you use the WorksheetFunction to count the values in a range in your worksheet, a static value is returned, not a flexible formula. This means that when your figures in Excel change, the value that has been returned by the WorksheetFunction will not change.

In the example above, the procedure TestCount has counted up the cells in column H where a value is present. As you can see in the formula bar, this result is a figure and not a formula.

If any of the values change therefore in the Range(H2:H12), the results in H14 will NOT change.

Instead of using the WorksheetFunction.Count, you can use VBA to apply a Count Function to a cell using the Formula or FormulaR1C1 methods.

Using the Formula Method

The formula method allows you to point specifically to a range of cells eg: H2:H12 as shown below.

Sub TestCountFormula
  Range("H14").Formula = "=Count(H2:H12)"
End Sub

VBA Programming | Code Generator does work for you!

Using the FormulaR1C1 Method

The FromulaR1C1 method is more flexible in that it does not restrict you to a set range of cells. The example below will give us the same answer as the one above.

Sub TestCountFormula()
   Range("H14").Formula = "=Count(R[-9]C:R[-1]C)"
End Sub

However, to make the formula more flexible, we could amend the code to look like this:

Sub TestCountFormula() 
   ActiveCell.FormulaR1C1 = "=Count(R[-11]C:R[-1]C)" 
End Sub

Wherever you are in your worksheet, the formula will then count the values in the 12 cells directly above it and place the answer into your ActiveCell. The Range inside the COUNT function has to be referred to using the Row (R) and Column (C) syntax.

Both these methods enable you to use Dynamic Excel formulas within VBA.

There will now be a formula in H14 instead of a value.

Источник

My first column is an index, with monotonously increasing numbers, which ends at a non predictable point.
Now I want to find out, how many entries this column has. Is there a better way than iterating throug this and watch out for an empty cell?

eli-k

10.7k11 gold badges43 silver badges44 bronze badges

asked Jul 14, 2010 at 9:27

If you want to find the last populated cell in a particular column, the best method is:

Range("A" & Rows.Count).End(xlUp).Row

This code uses the very last cell in the entire column (65536 for Excel 2003, 1048576 in later versions), and then find the first populated cell above it. This has the ability to ignore «breaks» in your data and find the true last row.

answered Oct 6, 2011 at 21:48

MrKowzMrKowz

2713 silver badges3 bronze badges

One way is to: (Assumes index column begins at A1)

MsgBox Range("A1").End(xlDown).Row

Which is looking for the 1st unoccupied cell downwards from A1 and showing you its ordinal row number.

You can select the next empty cell with:

Range("A1").End(xlDown).Offset(1, 0).Select

If you need the end of a dataset (including blanks), try: Range(«A:A»).SpecialCells(xlLastCell).Row

answered Jul 14, 2010 at 10:03

Alex K.Alex K.

170k30 gold badges263 silver badges286 bronze badges

You can also use

Cells.CurrentRegion

to give you a range representing the bounds of your data on the current active sheet

Msdn says on the topic

Returns a Range object that represents
the current region. The current region
is a range bounded by any combination
of blank rows and blank columns.
Read-only.

Then you can determine the column count via

Cells.CurrentRegion.Columns.Count

and the row count via

Cells.CurrentRegion.Rows.Count

answered Jul 14, 2010 at 10:18

almog.orialmog.ori

7,8291 gold badge35 silver badges49 bronze badges

You may also use:

UsedRange.Rows.Count

answered Jul 2, 2011 at 5:38

AMIBAMIB

3,1823 gold badges19 silver badges19 bronze badges

To find the last filled column use the following :

lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Hugo Dozois

8,05712 gold badges53 silver badges58 bronze badges

answered Mar 6, 2013 at 18:13

Источник

VBA Count Function

In Excel, we use the count function to count the number of cells which contains numbers. Same can be done in VBA as well. In VBA, we can use the same function Count to get how many numbers of cells contain numbers. It only counts the cell with numbers. Values other than numbers cannot be counted.

Syntax of Count in Excel VBA

The syntax for the VBA Count function in excel is as follows:

Syntax

How to Use VBA Count in Excel?

We will learn how to use a VBA Count Function with few examples in excel.

You can download this VBA Count Excel Template here – VBA Count Excel Template

Example #1 – VBA Count

For implementing this we have a list of some data in column A. This list contains numbers and texts as shown below. Now we with the help of Count function in VBA we will see, how many cells are having numbers. For this, we have identified a cell at A8 position, where we will see the output of Count Function through VBA.

Step 1: For this, we require a module. Go to Insert menu tab and click on Module option as shown below from the list.

Module

Step 2: After that, we will get the blank window of Module. Now in that write the subcategory of VBA Count. Or choose any other name as per your choice.

Code:

Sub VBACount()

End Sub

Step 3: Select the range of the cell where we want to apply Count function. Here, our output cell is A8 as defined above. So we have selected it as our Range.

Code:

Sub VBACount()

Range("A8").

End Sub

Step 4: Now get the Value command, and it allows us to add the value in it.

Code:

Sub VBACount()

Range("A8").Value =

End Sub

Step 5: Now with the help of Count Function, select the range of the cells from which we want to get the count of a number of cells which contains Numbers only. Here, we have selected the range of cells from A1 to A6.

Code:

Sub VBACount()

Range("A8").Value = "=Count(A1:A6)"

End Sub

Ste 6: Once done then compile the code and run by clicking play button. As we can see below, the count of cells containing numbers is coming as 3. Which means the Count function in VBA has given the count of cells with numbers which are from cell A1 to A3.

Example #2 – VBA Count

In a similar way, we have another set of data. But this data has some dates, number with text along with numbers and text as shown below. We have fixed a cells C12 where we will see the output of Count function through VBA.

Now we will apply the Count function and see if this can Count date and number-text cells or not. We can choose to write the new code again or we can refer the same code which we have seen in example-1 and just change the reference cells.

Step 1: Go to Insert menu tab and click on Module option as shown below from the list.

Code:

Sub VBACount2()

End Sub

Step 2: Select the range of cell where we want to see the output. Here that cell is C12.

Code:

Sub VBACount2()

Range("C12").Value =

End Sub

Step 3: Now use the count function in inverted commas in select the range of those cells which we need to count. Here that range is from cell C1 to C10.

Code:

Sub VBACount2()

Range("C12").Value = "=Count(C1:C10)"

End Sub

Step 4: Now run the above code.

We will see the Count function has returned the count of cells as 6 as shown below. Which means, count function can count cells with Date as well. Here, the values which are highlighted as bold are those values which just got counted through Count function in VBA.

Example #3 – VBA Count

There is another way to use Count Function in VBA. This method involves using Active Cells of the sheet. Here we will use the same data which we have seen in example-1.

Step 1: Open a new module and create the subcategory in the name of VBA Count as shown below.

Code:

Sub VBACount3()

End Sub

Step 2: First, insert the ActiveCell function in VBA. This will help in selecting the range of cells.

Code:

Sub VBACount3()

ActiveCell.

End Sub

Step 3: Now with the function Formula, select the row number and column number which we want to insert in Count function. Here our reference Row is starting from 1 and Column is also 1.

Code:

Sub VBACount3()

ActiveCell.FormulaR1C1 =

End Sub

Step 4: Now insert the Count function under inverted commas as shown below.

Code:

Sub VBACount3()

ActiveCell.FormulaR1C1 = "=COUNT()"

End Sub

Step 5: Select the range of the cells from the point where we are applying the Count function. As we are going up from A8 to A1 so row count will be “-7” and column is first to nothing is mentioned to the row count “-2” from the starting point which is cell A8.

Code:

Sub VBACount3()

ActiveCell.FormulaR1C1 = "=COUNT(R[-7]C:R[-2]C)"

End Sub

Step 6: Now select the range of cell where we want to see the output. Here at this range cell A8, we will see the cursor as well.

Code:

Sub VBACount3()

ActiveCell.FormulaR1C1 = "=COUNT(R[-7]C:R[-2]C)"
Range("B8").Select

End Sub

Step 7: Now run the code. We will see, the count function has returned the same count of number as 3 which we got in example-1.

Pros of VBA Count

It is as easy as applying Count Function in excel.
This is one of the easiest function that could be automated through VBA.
If the process Count is repeating multiple times then automating the same with the help of Count function in VBA is quite a time saving and effort minimizing way.

Things to Remember

While applying the count function in VBA, always quote the function name in inverted commas.
As we use Count in Excel, the same way is also seen while applying Count Function in VBA.
The process of applying VBA Count can be done by recoding a macro as well.
Always save the written code in VBA in Macro enable excel file format to avoid losing code.

Count Function in VBA Excel

VBA COUNT function, one can use to count how many cells have values in it. We must remember that it counts the cells with numbers or text enclosed in double quotes or whose values are typed directly. Those cells with random data that Excel cannot translate do not count.

In VBA, COUNT is a worksheet function that one can apply through the worksheet function class. It is not a built-in function. VBA COUNT function can count all the numerical values from the supplied range.

This article will show you how to use the COUNT function in VBA.

Count Function in VBA Excel
- Examples of VBA Count Function
  - VBA Count Example #1
  - VBA Count Example #2
- Recommended Articles

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkArticle Link to be Hyperlinked
For eg:
Source: VBA Count (wallstreetmojo.com)

Examples of VBA Count Function

Let us take some practical examples of the VBA COUNT function to understand its working.

You can download this VBA Count Function Template here – VBA Count Function Template

VBA Count Example #1

Write the code on your own to apply this COUNT function in Excel VBA.

Step 1: Start the macro by starting the Sub procedure.

Code:

Sub Count_Example1()

End Sub

Step 2: Now, we will store the result of the COUNT function in cell C2. So our code should be as “Range(“C2”).Value =

Code:

Sub Count_Example1()

 Range("C2").Value =

End Sub

Step 3: Since it is not a VBA built-in functionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more, we must apply it through the worksheet function class. So, access through worksheet function class.

Code:

Sub Count_Example1()

Range("C2").Value = Worksheetfunction.Count(

End Sub

Step 4: Now supply the range as A1 to A7.

Code:

Sub Count_Example1()

Range("C2").Value = WorksheetFunction.Count(Range("A1:A7"))

End Sub

These are three simple steps to arrive at the result.

Now, run the code to see the result in cell C2.

As usual, we got the result as 3, like when we applied the VBA COUNT function in a worksheet.

If we observe the formula bar, we have not got the formula. Rather, we just got the result of the formula. Therefore, we must alter our code to apply the formula to cell C2. Below is the code to apply the formula.

Code:

Sub Count_Example1()

    Range("C2").Value = "=Count(A1:A7)"

End Sub

Run this VBA codeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more using the F5 key or manually. Then, this will apply the formula to cell C2.

VBA Count Example #2

Look at one more example with the COUNT function. For this example, look at the below data.

We have a few numerical and non-numerical values from A1 to A11. Therefore, we will apply the code below to count the numerical values from the range A1 to A11.

Code:

Sub Count_Example2()

    Range("C2").Value = "=Count(A1:A11)"

End Sub

When we execute the above code manually or using the F5 key, we get the result as 5.

However, when we closely look at the data from A1 to A11, we have numerical values in cells A1, A2, A3, A4, A5, A7, and A11. So, we have numerical values in 7 cells, but the formula has returned the result as only 5.

The reason for this is when we closely look at each cell in cells A5 and A7, the values are stored as text, not as numbers. So the COUNT function has returned the result as only 5 because it treats those cells formatted or stored as text as non-numerical values and ignores the numerical count.

Like this, we can use the COUNT function in Excel as part of both worksheets and VBA functions to count the numerical values from the list.

VBA Excel. Методы CountIf и CountIfs

Подсчет количества ячеек в диапазоне, соответствующих заданным критериям, методами CountIf и CountIfs объекта WorksheetFunction из кода VBA Excel.

Metod WorksheetFunction.CountIf

Определение

Определение метода CountIf объекта WorksheetFunction в VBA Excel:

Синтаксис

Синтаксис метода CountIf объекта WorksheetFunction:

Параметры

Параметры метода CountIf объекта WorksheetFunction:

Параметр	Описание
Arg1	Диапазон, в котором необходимо подсчитать количество ячеек, соответствующих заданному критерию. Тип данных — Range.
Arg2	Критерий в виде числа, текста, выражения или ссылки на ячейку, определяющий, какие ячейки будут засчитываться. Тип данных — Variant.

Примечания

Метод WorksheetFunction.CountIf позволяет получить количество ячеек, соответствующих заданному критерию, в указанном диапазоне.
Примеры критериев (условий): 25 , «25» , «>50″ , » , «береза» или D5 .
В критериях можно использовать знаки подстановки (спецсимволы): знак вопроса (?) и звездочку (*). Знак вопроса заменяет один любой символ, а звездочка соответствует любой последовательности символов. Чтобы знак вопроса (?) и звездочка (*) обозначали сами себя, перед ними указывается тильда (

Metod WorksheetFunction.CountIfs

Должен отметить, что у меня в VBA Excel 2016 метод WorksheetFunction.CountIfs не работает. При попытке применить данный метод, генерируется ошибка:

Run-time error ‘1004’:
Невозможно получить свойство CountIfs класса WorksheetFunction

Очевидно, метод WorksheetFunction.CountIfs предусмотрен для более новых версий VBA Excel. Статья на сайте разработчиков датирована 2021 годом.

Определение

Определение метода CountIfs объекта WorksheetFunction в VBA Excel:

Синтаксис

Синтаксис метода CountIfs объекта WorksheetFunction:

Источник

Макрос для подсчета ячеек в выделенном диапазоне

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

Известен масштаб: Сторона клетки равна 10 см.

Необходимо посчитать площадь объекта или его отдельной части (комнаты).

подсчет площади объекта в Эксель

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

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

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

Площадь объекта при помощи макроса

Макрос подсчета количества ячеек в выделенном диапазоне выглядит следующим образом:

Sub Счет_ячеек() ‘название макроса

Dim b as integer ‘ вводим переменную b

b=0 ‘присваиваем переменной значение «0»

for each cell in selection ‘записываем цикл подсчета

b=b+1

next

MsgBox «количество ячеек » & b ‘ выводим сообщение с количеством ячеек

End sub ‘конец макроса

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

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

Добавить комментарий Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.

Источник

Подсчет количества ячеек с определенным цветом ячеек с помощью VBA

Аннотация

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

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

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

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

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

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

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

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

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

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

В аргументе «range_data» выберите ячейку C2 –C51.

В аргументе criteria выберите ячейку F1.

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

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

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

Шаг 1. Сохранение книги

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

Б. Выберите формат Add-In Excel (XLAM) и присвойте файлу имя, например CountCcolor.

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

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

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

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

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

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

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

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

Отказ от ответственности за содержимое общедоступных решений

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

Источник

Excel макрос посчитать количество ячеек

Здравствуйте! Я не программист и не студент, задачки придумываю себе сам по мере возникновения идеи или надобности и для самообразования.
Вот возникла надобность в быстром отчете — есть таблица (прилагается), хочу сделать быстрый отчет по подсчету ячеек по нескольким условиям — например: Посчитать количество 725 по полю «модель»; за период от «01.01.2018» до «10.02.2018»; для «Попов». Или Посчитать за определенный период «от» и «до» количество «фб» по полю «Работа» (причем не «замена фб», а именно «фб» — из всего текста надо вычленить «фб», так как может быть «замена впз», а может быть и то и другое, и т.д.).
Вот логику действий понимаю, с помощью формул делаю (при этом торможение Excel наблюдаю), но вот с кодом ВБА у меня огромная проблема. По этому прошу помощи у Вас — знающих и понимающих.
Переключение поля поиска думаю сделать OptionBootton, но если считаете это неудобным, то — форму можно менять на свое усмотрение

Буду очень признателен и заранее благодарю!

Сообщение Здравствуйте! Я не программист и не студент, задачки придумываю себе сам по мере возникновения идеи или надобности и для самообразования.
Вот возникла надобность в быстром отчете — есть таблица (прилагается), хочу сделать быстрый отчет по подсчету ячеек по нескольким условиям — например: Посчитать количество 725 по полю «модель»; за период от «01.01.2018» до «10.02.2018»; для «Попов». Или Посчитать за определенный период «от» и «до» количество «фб» по полю «Работа» (причем не «замена фб», а именно «фб» — из всего текста надо вычленить «фб», так как может быть «замена впз», а может быть и то и другое, и т.д.).
Вот логику действий понимаю, с помощью формул делаю (при этом торможение Excel наблюдаю), но вот с кодом ВБА у меня огромная проблема. По этому прошу помощи у Вас — знающих и понимающих.
Переключение поля поиска думаю сделать OptionBootton, но если считаете это неудобным, то — форму можно менять на свое усмотрение

Буду очень признателен и заранее благодарю!

срочности нет Автор — urlchik
Дата добавления — 19.02.2018 в 12:15

Источник

Как посчитать количество ячеек содержащих определенное значение в Exel

Определенный текст

Для подсчета количества ячеек, содержащих определенный текст, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, TXT представляет собой текст, который должны содержать ячейки, и «*» является подстановочным символом, соответствующим любому количеству символов.

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

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат «а» путем сопоставления содержимого каждой ячейки с шаблоном «*a*», который поставляется в качестве критериев. Символ «*» (звездочка) является подстановочным в Excel, что означает «совпадают с любым количеством символов», так что эта модель будет считать любую ячейку, которая содержит «а» в любом положении. Количество ячеек, которые соответствуют этому шаблону рассчитывается как число.

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

X или Y

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

Когда вы подсчитывать ячейки с критерием «или», вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат «abc» или «def», вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и «abc» и «def».

Решение одной формулой

Для решения одной формулойц, вы можете использовать комбинацию СУММПРОИЗВ с ЕЧИСЛО + НАЙТИ. Формула в ячейке E4 будет:

Эта формула основана на формуле, которая находит текст внутри ячейки:

При заданном диапазоне ячеек, этот фрагмент будет возвращать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для «abc» и еще для «def»), мы получим два массива.

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

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

И, наконец, СУММПРОИЗВ суммирует полученные числа.

Вспомогательный столбец решений

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

СЧЁТЕСЛИ возвращает массив, который содержит два пункта: подсчет для «abc» и подсчет на «def». Чтобы избежать двойного счета, мы складываем элементы, а потом возвращаем результат «истина/ложь» с «>0». Наконец, мы преобразуем значения Истина или Ложь в 1 и 0 с двойным минусом (—).

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

Ошибки

Для подсчета количества ячеек, содержащих ошибки, вы можете использовать функцию ЕОШ, завернутую в функцию СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, в которых вы хотели бы рассчитывать ошибки.

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

СУММПРОИЗВ принимает один или несколько массивов и вычисляет сумму произведений соответствующих чисел. Если только один массив , он просто суммирует элементы в массиве.

Функция ЕОШ вычисляется для каждой ячейки в rng. Результатом является массив со значениями истина / ложь:

(—) — Оператор (называемый двойной одинарный) приводит истинные/ложные значения в 0 и 1. Результирующий массив выглядит так:

СУММПРОИЗВ затем суммирует элементы в этом массиве и возвращает общую сумму, которая, в данном примере, это число 2.

Примечание: ЕОШ подсчитывает все ошибки, кроме # N / A. Если вы хотите, чтобы также рассчитывалось # N / A, используйте функцию ЕОШИБКА вместо ЕОШ.

Вы можете также использовать функцию СУММ для подсчета ошибок. Структура формулы такая же, но она должна быть введена как формула массива (нажмите Ctrl + Shift + Enter, а не просто Enter). После ввода формула будет выглядеть следующим образом:

Пять символов

Для подсчета количества ячеек, содержащих определенное количество символов текста, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, а «?» соответствует любому одному символу.

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

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

Положительные числа

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

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

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют критериям. В этом случае критерии поставляются в виде «> 0», которые оцениваются как «значения больше нуля». Общее количество всех ячеек в диапазоне, которые удовлетворяют этому критерию рассчитывается функцией.

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

Отрицательные числа

=СЧЁТЕСЛИ(rng;»

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

=СЧЁТЕСЛИ(B5:B10;»

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

Функция СЧЁТ является полностью автоматической. Она подсчитывает количество ячеек в диапазоне, содержащих числа и рассчитывает результат.

Нечетные числа

Для подсчета ячеек, которые содержат только нечетные числа, вы можете использовать формулу, основанную на функции СУММПРОИЗВ вместе с функцией ОСТАТ.

В примере, формула в ячейке E4 является:

Эта формула рассчитала 4, так как есть 4 нечетных числа в диапазоне В5: В10 (который назван «rng» в формуле).

Функция СУММПРОИЗВ непосредственно работает с массивами.

Одна вещь, которую вы можете сделать довольно легко с СУММПРОИЗВ это выполнить тест на массив, используя один или несколько критериев, затем подсчитать результаты.

В этом случае, мы проводим тест на нечетное число, который использует функцию ОСТАТ:

ОСТАТ рассчитывает остаток от деления. В этом случае делитель равен 2, поэтому ОСТАТ рассчитывает остаток 1 для любого нечетного числа, а остаток 0 для четных чисел.

В функции СУММПРОИЗВ, этот тест выполняется в каждой ячейке B5: B10, результат представляет собой массив значений истина / ложь:

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

СУММПРОИЗВ затем просто суммирует эти числа и рассчитывает 4.

Текст

Для подсчета количества ячеек, содержащих текст (т.е. не цифры, не ошибки и не пустые), можете использовать функцию СЧЁТЕСЛИ и подстановочные знаки. В общей форме формулы (выше), rng является диапазон ячеек, а «*» является подстановочным знаком, который соответствует любому количеству символов.

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

СЧЁТЕСЛИ подсчитывает количество ячеек, которые соответствуют критериям. В этом случае критерий поставляется в качестве шаблонного символа «*», который совпадает с любым количеством символов текста.

Логические значения истина и ложь не учитываются, как текст
Числа не подсчитываются «*», если они не будут введены в виде текста
Пустая клетка, которая начинается с апострофа ( ‘) будут учитываться.

Вы можете также использовать СУММПРОИЗВ для подсчета текстовых значений наряду с функцией ЕТЕКСТ так:

Двойной дефис принуждает результат ЕТЕКСТ от логического значения ИСТИНА или ЛОЖЬ перейти к 1 и 0. СУММПРОИЗВ затем суммирует эти значения вместе, чтобы получить результат.

Чувствительная к регистру версия

Если вам нужна чувствительная к регистру версия, вы не можете использовать СЧЁТЕСЛИ. Вместо этого вы можете проверить каждую ячейку в диапазоне, используя формулу, основанную на функции НАЙТИ и функции ЕЧИСЛО.

НАЙТИ чувствительна к регистру, и вы должны дать ему диапазон ячеек, а затем использовать СУММПРОИЗВ для подсчета результатов. Формула выглядит следующим образом:

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

Источник

ALFA Пользователь Сообщений: 243 Регистрация: 13.09.2013	Подскажите, как, открыв макросом стороннюю книгу, можно на определенном ее листе посчитать кол-во заполненных ячеек в столбце A.

hk1209 Пользователь Сообщений: 271 Регистрация: 01.01.1970

ALFA Пользователь Сообщений: 243 Регистрация: 13.09.2013	Set wb2 = Workbooks.Open(«C:UsersU_M06TTDesktopExcelКачествоМарт 2014.xls», 0, 1) ‘открыл книгу Далее необходимо посчитать кол-во заполненных ячеек в файле Март 2014.xls sheets(«данные»).range(«A:A») Скажите что еще написать, я в этом не оч силен.

ALFA Пользователь Сообщений: 243 Регистрация: 13.09.2013	А если необходимо открыв книгу, посчитать Кол-во заполненных ячеек, предварительно выбрав лист, как использовать Worksheetfunction.CountA(Columns(1))

Метод WorksheetFunction.Count

Определение

Синтаксис

Параметры

Примечания

Метод WorksheetFunction.CountA

Определение

Синтаксис

Параметры

Примечания

Метод WorksheetFunction.CountBlank

Определение

Синтаксис

Параметры

Примечания

Примеры

Примеры с WorksheetFunction.Count

Примеры с WorksheetFunction.CountA

Примеры с WorksheetFunction.CountBlank

COUNT WorksheetFunction

Assigning a Count result to a Variable

COUNT with a Range Object

COUNT Multiple Range Objects

Using COUNTA

Using COUNTBLANKS

VBA Coding Made Easy

Using the COUNTIF Function

Disadvantages of WorksheetFunction

Using the Formula Method

Using the FormulaR1C1 Method

VBA Count Function

How to Use VBA Count in Excel?

Example #1 – VBA Count

Example #2 – VBA Count

Example #3 – VBA Count

Pros of VBA Count

Things to Remember

Recommended Articles

Count Function in VBA Excel

Table of contents

Examples of VBA Count Function

VBA Count Example #1

VBA Count Example #2

Recommended Articles

VBA Excel. Методы CountIf и CountIfs

Metod WorksheetFunction.CountIf

Определение

Синтаксис

Параметры

Примечания

Metod WorksheetFunction.CountIfs

Определение

Синтаксис

Макрос для подсчета ячеек в выделенном диапазоне

Макрос подсчета количества ячеек в выделенном диапазоне выглядит следующим образом:

Добавить комментарий Отменить ответ

Подсчет количества ячеек с определенным цветом ячеек с помощью VBA

Аннотация

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

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

Отказ от ответственности за содержимое общедоступных решений

Excel макрос посчитать количество ячеек

Как посчитать количество ячеек содержащих определенное значение в Exel

Определенный текст

X или Y

Ошибки

Пять символов

Положительные числа

Отрицательные числа

Нечетные числа

Текст

А вот еще интересные статьи: