Формула СЧЕТЕСЛИ - проблема с диапазоном

[oleg_vm]

Всех приветствую!
Такая проблема. Необходимо подсчитать количество ячеек, содержащих, например букву О. Причём диапазон представляет из себя ячейки на разных листах с одним и тем же адресом, т.е. Лист1:Лист3!А1. Но при таком задании диапазона формула (которая находится на отдельном листе Лист1 в ячейке А1)
СЧЕТЕСЛИ(Лист2:Лист4!А1;'=O') возвращает ошибку #ЗНАЧ!
Получается формула не работает с несколькими листами?

[Avsha]

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

[oleg_vm]

я немного уточню проблему.
У меня есть несколько листов Excel-анкет шаблона одного вида с оценками разных сотрудников компании. Шаблон представляет из себя матрицу: по строкам - штатные единицы компании, по столбцам - бизнес-процессы компании. На пересечении ставятся оценки О (ответсвенный за процесс) и У (участник процесса), либо ничего не ставится. Как понимаете оценки разные, моя задача - их усреднить.
Метод усредненяя я придумал.
А поскольку матрицы довольно большой размерности, вручную по времени это будет достаточно долго. С помощью формул в принципе понял как сделать, но вот столкнулся, что одна из них (которая мне необходима) не умеет работать с диапазоном, распределённом на нескольких листах.
А как свою функцию создать?

[Avsha]

1. Открываете новую книгу Excel.
2. Сервис / Макрос / Редактор Visual Basic
3. Insert / Module
4. Вставляете функцию в этот модуль:

Текст программы условный для примера !!!
---------------------------------------------------------------------------
Public Function Моя_функция_01(Диапазон_листов As Range, Диапазон_Ячеек As Range)

' Подсчет суммы в диапазоне ячеек для указанных листов
Моя_функция_01 = f(Диапазон_листов, Диапазон_Ячеек)

End Function
---------------------------------------------------------------------------

5. Теперь используете ее как обычную функцию для любой ячейки рабочей книги с требуемыми аргументами
=Моя_функция_01(Лист2:Лист4;G11:H14)
Эту функцию ищите в категории функций "Определенные пользователем"

[pashulka]

oleg_vm Для того, чтобы Вас не мучал вопрос ... почему стандартная функция рабочего листа =СУММЕСЛИ() не возвращает корректный результат, при использовании нескольких листов, я приведу Вам цитату из Help :

Рекомендации по использованию трехмерных ссылок

· Трехмерные ссылки можно использовать для создания ссылок на другие листы, определения имен, а также для создания формул, при помощи следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНА, СТАНДОТКЛОНП, СТАНДОТКЛОНПА, ДИСП, ДИСПА, ДИСПР и ДИСПРА.
· Трехмерные ссылки нельзя использовать в формулах массива.
· Трехмерные ссылки нельзя использовать вместе с оператором пересечения (пробел), а также в формулах, использующих неявное пересечение.

© Microsoft Excel

Примечание : По всей видимости в этой части help наличествует ошибка и пятая функция должна быть СЧЁТЗ

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

[Avsha]

Наверно вы решили уже как-то эту проблему, но вот еще одно простое решение:

Можно считать сумму на каждом листе отдельно функцией СЧЕТЕСЛИ,
а затем сложить эти ячейки - "суммы" функцией СУММ.

[oleg_vm]

для удобства использования и сокращения времени дальнейшей обработки данных пришлось написать необходимую функцию... Работает нормально..
Один момент правда волнует... Функция определяет количество листов в книге, а потом последовательно с каждым из них работает. Каждый лист книги (не считая основного, первого), как я писал выше, соответствует анкете опроса заданной формы, и количество этих листов часто меняется. Так вот, при вставке нового листа, формулы с моей функцией на главной странице автоматически не пересчитываются, а заново "вручную" пересчитывать каждую ячейку, число которых не маленькое достаточно неудобно.
Как этот момент разрулить?

[pashulka]

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

Код: Выделить всё

Function CountList()
Application.Volatile True
…
End Function

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

Код: Выделить всё

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Worksheets("Основной").Calculate
' Or
Worksheets("Основной").Range("A1:L100").Calculate
End Sub

Код: Выделить всё

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Лист1.Calculate
' Or
Лист1.Range("A1:L100").Calculate
End Sub

[Avsha]

Есть такой вариант:

1. Ячейка B3=1, 2, 3 ... и т.д.
2. Описание функции:

Public Function Пример(ChangeFunction As Range)

'Чтобы функция пересчитывалась
begin = ChangeFunction

'Выполнение функции
Пример = Now

End Function

3. Вызов функции: =Пример($B$3)

Меняете значение в одной ячеке B3 и все остальное пересчитывается.
Если поместите в ячейку формулу B3=ТДАТА(), то будет пересчитываться по нажатию F9.

[Naeel Maqsudov]

Еще один вариант
Добавьте в вашу функцию еще один аргумент (просто фиктивный аргумент типа variant)

Далее, вводя формулы (а если формулы введены, то можно автозаменой) в качестве значения этого аргумента укажите ссылку на пустую ячейку, например, Лист1!$a$1.
Теперь, если надо быстро инициировать пересчет Ваших формул, содержащих вызов данной функции, то просто нажмите Delete в этой пустой ячейке

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