Расчет суммы первых значений диапазона
Предположим, что в указанном диапазоне нам нужно сложить не все значения, а только несколько первых (иначе говоря, например, в диапазон входят 10 ячеек, а нам нужно посчитать сумму только первых 5 из них). Для решения этой задачи можно использовать функцию, код которой приведен в листинге 2.63.
Листинг 2.63. Функция dhNSum
Function dhNSum(ByVal intCount As Integer, _
rgValues As Range) As Double
Dim i As Integer
Dim dblSum As Double
If intCount > rgValues.Count Then
' Задано количество элементов большее, чем есть _
в переданном диапазоне
intCount = rgValues.Count
End If
' Расчет суммы первых intCount элементов
For i = 1 To intCount
dblSum = dblSum + rgValues(i)
Next i
' Возврат результата
dhNSum = dblSum
End Function
После выбора данной функции следует указать количество первых значений диапазона, а также сам диапазон (причем можно указать несколько диапазонов – в результате будет рассчитана сумма указанного количества первых значений всех перечисленных диапазонов). Результат вычисления будет показан в активной ячейке.
Поиск последней непустой ячейки диапазона
При работе с большими объемами данных иногда бывает необходимо быстро найти последнюю непустую ячейку какого-либо диапазона (то есть последнюю ячейку, содержащую данные). В этом нам поможет пользовательская функция, для создания которой нужно написать следующий код (листинг 2.64).
Листинг 2.64. Функция dhLastUsedCell
Function dhLastUsedCell(rgRange As Range) As Long
Dim lngCell As Long
' Пойдем по диапазону с конца (тогда первая попавшаяся _
заполненная ячейка и будет искомой)
For lngCell = rgRange.Count To 1 Step -1
If Not IsEmpty(rgRange(lngCell)) Then
' Нашли непустую ячейку
dhLastUsedCell = lngCell
Exit Function
End If
Next lngCell
' Непустую ячейку не нашли
dhLastUsedCell = 0
End Function
После выбора данной функции необходимо указать диапазон, который следует проверить, – в результате порядковый номер последней ячейки этого диапазона, которая содержит данные, будет показан в активной ячейке. Следует подчеркнуть, что функция возвращает порядковый номер ячейки диапазона. Если функция не находит заполненную ячейку, то она возвращает 0.
Поиск последней непустой ячейки столбца
Для быстрого поиска последней непустой ячейки столбца удобно применять пользовательскую функцию dhLastColUsedCell. Для ее создания нужно в стандартном модуле редактора VBA написать следующий код (листинг 2.65).
Листинг 2.65. Функция dhLastColUsedCell
Function dhLastColUsedCell(rgColumn As Range) As Variant
' Вывод значения последней непустой ячейки столбца
dhLastColUsedCell = rgColumn.Parent.Cells(Rows.Count, _
rgColumn.Column).End(xlUp).Value
End Function
Данная функция имеет один аргумент. В качестве аргумента указывается столбец, в котором необходимо найти последнюю непустую ячейку. При этом можно просто указать любую ячейку данного столбца. Содержимое (то есть значение) найденной ячейки будет помещено в ячейку, в которой установлен курсор.
Можно использовать функцию с помощью строки формул. Формула при этом может выглядеть так:
=dhLastColUsedCell(B3)
В данном случае будет найдено и помещено в активную ячейку значение последней непустой ячейки столбца В.
Поиск последней непустой ячейки строки
Чтобы быстро найти последнюю непустую ячейку строки, можно применить пользовательскую функцию dhLastRowUsedCell. Она во многом напоминает рассмотренную выше функцию dhLastColUsedCell. Для создания функции нужно в стандартном модуле редактора VBA написать такой код (листинг 2.66).
Листинг 2.66. Функция dhLastRowUsedCell
Function dhLastRowUsedCell(rgRow As Range) As Variant
' Вывод значения последней непустой ячейки строки
dhLastRowUsedCell = rgRow.Parent.Cells(rgRow.Row, 256). _
End(xlToLeft).Address
End Function
Данная функция использует один аргумент. В качестве аргумента указывается строка, в которой необходимо найти последнюю непустую ячейку. При этом можно просто указать любую ячейку данной строки. Координаты найденной ячейки отобразятся в ячейке, на которой установлен курсор. Порядок использования данной функции такой же, как и рассмотренной выше функции dhLastColUsedCell.
Подсчет количества ячеек в диапазоне, содержащих указанные значения
Трюк, который мы рассмотрим в данном подразделе, позволяет быстро посчитать количество ячеек указанного диапазона, которые расположены в области между заданными значениями. Для решения данной задачи целесообразно использовать функцию dhCountSomeCells, код которой выглядит следующим образом (листинг 2.67).
Листинг 2.67. Функция dhCountSomeCells
Function dhCountSomeCells(rgRange As Range, dblMin As Double, _
dblMax As Double) As Long
' Расчет количества ячеек со значениями от dblMin до dblMax _
с использованием стандартной функции CountIf
With Application.WorksheetFunction
dhCountSomeCells = .CountIf(rgRange, ">=" & dblMin) – _
.CountIf(rgRange, ">" & dblMax)
End With
End Function
Данная функция имеет три аргумента: в качестве первого указывается обрабатываемый диапазон, в качестве второго и третьего – соответственно минимальное и максимальное значения диапазона, которые задают область поиска. Количество ячеек, значения которых попадают в указанный интервал, отображается в активной ячейке.
Для использования функции можно применять формулу, пример которой выглядит следующим образом:
=dhCountSomeCells(A1:G15;10;15)
В данном случае будет определено количество ячеек, находящихся в диапазоне A1:G15 и значения которых располагаются в интервале от 10 до 15 (включительно).
Англоязычный текст – заглавными буквами
С помощью несложного трюка можно быстро преобразовать весь англоязычный текст выделенной ячейки в верхний регистр (написать его заглавными буквами). Решить эту задачу поможет пользовательская функция, код которой приведен в листинге 2.68.
Листинг 2.68. Английский текст – в верхнем регистре
Function dhFormatEnglish(strText As String) As String
Dim i As Integer
Dim strCurChar As String * 1
' Анализируется каждый символ строки strText. Каждый символ _
латинского алфавита преобразуется в верхний регистр
For i = 1 To Len(strText)
strCurChar = Mid(strText, i, 1)
' Код латинских строчных символов лежит в пределах _
от 97 до 122
If Asc(strCurChar) >= 97 And Asc(strCurChar) <= 122 Then
' Переводим символ в верхний регистр
dhFormatEnglish = dhFormatEnglish & UCase(strCurChar)
Else
' Просто добавляем символ в выходную строку
dhFormatEnglish = dhFormatEnglish & strCurChar
End If
Next i
End Function
После выбора функции следует указать ячейку, текст которой должен быть написан заглавными буквами. Результат отобразится в активной ячейке. Если преобразуемый текст является "смешанным", то есть содержит и русские, и английские слова, то преобразованы будут только английские слова, а русские останутся без изменений.
Отображение текста "задом наперед"
Трюк, который мы рассмотрим в данном подразделе, вряд ли будет иметь широкое практическое применение, поскольку он носит скорее развлекательный характер. Однако от этого он не становится менее интересным.
Смысл его заключается в том, чтобы быстро "перевернуть" текст выделенной ячейки (например, вместо Мама мыла раму получится умар алым амаМ). Чтобы получить подобный результат, можно применить пользовательскую функцию или специально созданный макрос. Соответствующий код приведен в листинге 2.69 (этот код следует поместить в стандартный модуль редактора VBA).
Листинг 2.69. Преобразование текста в обратном порядке
Function dhReverseText(strText As String) As String
Dim i As Integer
' Переписываем символы из входной строки в выходную _
в обратном порядке
For i = Len(strText) To 1 Step -1
dhReverseText = dhReverseText & Mid(strText, i, 1)
Next i
End Function
Sub ReverseText()
Dim strText As String
' Ввод строки посредством стандартного окна ввода
strText = InputBox("Введите текст:")
' Реверсия строки и вывод результата
MsgBox dhReverseText(strText), , strText
End Sub
После того как будет написан данный код, создается пользовательская функция dhReverseText (она будет помещена в категорию Определенные пользователем) и макрос ReverseText (он будет доступен в окне выбора макросов).
После выбора функции следует указать ячейку, текст которой необходимо преобразовать, – результат отобразится в активной ячейке. При выполнении данной операции не стоит забывать, что если ячейка содержит не текст либо пуста, то результатом работы функции будет сообщение об ошибке.