да, круто... спасибо

упс, действительно, АГРЕГАТ( ) с параметром пропуска ошибок решает проблему, об которую я чуть не сломал голову. А я про АГРЕГАТ и забыл... стыдно .(--_--).
Ввиду того, что задача решается намного проще, выкладываю своё хитроумное решение:

ФМ: =ВПР(0,9;{0;1:1;#Н/Д}*МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(9E+307;A:A);0);ЕСЛИОШИБКА(ПОИСКПОЗ("яяяя";A:A);0));2;1)

Если данных нет, МАКС() выдаёт 0, а нужна любая ошибка. Для этого, результат работы МАКС умножается на такую матрицу:
0  1
1  #Н/Д
Там, где стоит 1, будет результат работы МАКС(). Рассмотрим 2 варианта:

Вариант 1. МАКС() возвращает число, например, 5,
Тогда, матрица будет такой:
0  5
5  #Н/Д
поскольку ВПР ищет 0,9, а 5 - уже больше, чем 0,9, то вернётся результат с первой строки со 2го столбца - то есть, 5.

Вариант 2. МАКС() возвращает 0, тогда умножение нуля на матрицу даст такой результат:
0  0
0  #Н/Д
Поиск в первом столбце числа 0,9 в такой матрице даст уже 2ю строку, и будет выбран результат #Н/Д.

При чём, поначалу я использовал поиск не 0,9, а 1. Но в таком случае, если результат результат работы МАКС() тоже единица, получается сама исходная матрица
0  1
1  #Н/Д
и поиск 1цы даст 2ю строку, а там НД, хотя МАКС() выдал 1цу! Поэтому, нужно искать число, меньшее единицы. Поскольку, результат работы функции МАКС не может быть равен 0,9 (так как поиск позиции всегда выдаёт целые числа), 0,9 вполне подходит.

БМВ, а можно показать мне решение в личку?

Логические значения во внимание не принимаются.

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

Привет всем любителям поизвращаться с формулами. С трудом, но всё-таки удалось решить довольно избитую, часто встречающуюся задачу - найти нижнюю границу столбца (динамического массива). Сложность заключается в том, что:
- в столбце могут быть как числа, так и текст!
- искать последний элемент через быстрый поиск (с ключом 1), но воспользоваться поиском можно только 2 раза - 1 раз для текста и 1 раз для чисел
- если столбец пуст, формула должна вернуть #НД()
- сделать без вспомогательных ячеек/имён!
- разумеется, формула не должна быть летучей
- формула массива разрешается, если это не перебор всего диапазона

искать, например, по столбцу А:А

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

для меня вся сложность заключалась в том, чтобы вернуть НД(), если столбец пуст. Это ключевое условие для работы динамического массива, т.к. 0 при подстановке в ИНДЕКС() возвратит весь столбец, а это недопустимо.

Нет, к сожалению b = StrConv(a, vbUpperCase, 1049) тоже не сработало
Помогло это решение (вторая половина).

Всем спасибо

К сожалению, нет, не помогло  
Переключаться на русскую раскладку ж надо до того, как я скопирую текст в буфер обмена...
Так что, буду рад ещё каким-то предложенным решениям...    

Ммм... да, Дима, так будет даже лучше, поскольку макрос запускается либо по русской, либо по английской клавише, поэтому, переключиться на нужную раскладку будет оптимальное решение.
Спасибо! :-)

Да, должно помочь. Есть ссылка и на этом сайте: https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=8&TID=2826&amp...
Но хочется научиться делать программно.

Иногда в коде некоторые комментарии хочется выделить заглавными буквами, чтобы выглядело как заголовок. Делаю процедуру преобразования текста в верхний регистр, чтобы повесить её на сочетание клавиш (разумеется, для этого нужно будет сперва переключиться в эксель).
Когда был скопирован русский текст, но стояла английская раскладка, переменная a получает крокозяблики. Как исправить?

Да, все предлагаемые к скачиванию версии 32-разрядные, но это же не значит, что их нельзя запускать из 64-разрядного офиса? (я лично ещё пока не знаю, это моё предположение    )
Пошла установка x64-го офиса (неанинсталлированный пакет совместимости мешал установщику запуститься). Буду пробовать....

sokol92 написал:
Отсутствие библиотеки не есть проблема, её можно скачать http://www.dlldownloader.com/msvbvm60-dll/
Только вопрос, как правильно описать параметры функций, чтобы можно было их вызывать?
Ладно, тогда придётся использовать CopyMemory для этих целей.

Ситуация ещё усложняется тем, что я не могу попробовать у себя на компе всё это. Поставить одновременно и x86 и x64 версии офиса нельзя. Снёс х86, перезагрузился, а установщик х64 всё равно сообщает, что уже установлена 32-разрядная версия и не хочет устанавливать х64.

Та искал же ж... по поводу GetMem4 ничего не нашёл ни на этом сайте, ни в интернете

Добрый день. Тема избитая, но запустить GetMem на 64-разрядном офисе не получается.
Нужно, чтобы заработала, например, такая конструкция:Декларации для 32р такие:

Сергей, спасибо, то что надо :-) Крутил-крутил этот СМЕЩ() со всех сторон, но так и не смог заставить его выдать массив значений для каждого диапазона.

 StepanWolkoff,  да, будете удивлены, но это реальная структура данных. Более того, каждая таблица имеет ещё 2 служебные строки, содержащие различные объединённые ячейки, но я их обрезал, поскольку к примеру они дела не имеют.

Добрый день.
В примере лежит формула, определяющая максимальное число заполненных ячеек в указанных диапазонах. Мне она не нравится, т.к. вычисляется она не на листе формулой, а из кода в событии Worksheet_Change только для той таблицы, где произошли изменения.
Я сделал это 6ю отдельными строчками Application.Count() для 6 диапазонов, результаты записываются в массив, а потом у массива берётся Application.Max(). Cинтаксис  получается длинный и некрасивый.
Может ли кто подсказать, как можно упростить эту формулу? Приемлемы все варианты: формулой (можно исп. летучие ф-ции), можно из кода через Evaluate(), можно через WorksheetFunction(). В книге созданы имена Склад1 ... Склад6, можно привязываться к ним.

Вот KL тоже что-то демонстрировал, но у меня аргументы подсвечивать не начало...
http://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=260122&msg=3304746

В общем, вот расширенный ответ на эту тему...
http://qaru.site/questions/66324/how-to-put-a-tooltip-on-a-user-defined-function
Вот кто-то что-то делал... http://www.jkp-ads.com/Articles/RegisterUDF03.asp но тут пол дня надо разбираться, а то и больше... впадло стало заниматься этим вопросом :-)

Попробовал... Но это всё не то.... В окне ввода функции через F(x) описания аргументов появились... Хотелось бы, чтобы в Tooltip к формуле подсвечивались аргументы. Ctrl+Shift+A - это, конечно, хорошо, но тоже чуть не то :-(

https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=8&TID=37718&am...

Андрей VG, спасибо за подсказку, будем пробовать ;-)

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

Как-то столкнулся с подобной задачей... пришёл в голову такой вариант... Исследуемый текст в ячейке А1.
Формула массива: =ИЛИ(ABS(КОДСИМВ(ПСТР(ПРОПИСН(A1);СТРОКА(ИНДЕКС(C:C;1):ИНДЕКС(C:C;ДЛСТР(A1)));1))-{207,5;168})<{16;1})
Вместо столбца C:C можно можно подставить любой другой столбец.

Логика работы формулы такая:
1. Преобразуем все буквы в заглавные для упрощения.
2. Смещаемся на середину кодов заглавных букв русского языка. Поскольку подряд идущих символов 32 (буква Ё размещена в другом месте), то позиция середины - это 16,5. Прибавляем код первого символа минус единицу -- получаем 207,5 (=191 + 16,5).
3. Отнимаем от исследуемых кодов эту цифру (207,5). При этом, для букв от А до П результат будет отрицательный, для букв от Р до Я положительный.
5. Берём модуль и сравниваем результат с размером диапазона, делённым на два (16). Таким образом мы захватываем 32 буквы  (по 16 сверху и снизу от середины 207,5)
6. Букву Ё формула проверит по тому же правилу. Её код 168, кол-во символов - один.
7. Если хоть одно из условий истинно, ИЛИ вернёт ИСТИНУ.

Для украинского языка (для включения букв Ґ, Є, Ї, І) если при этом не исключать из диапазона русские букв буквы Ъ,Э,Ы, константы будут такие:
... -{207,5;165;170;175;178})<{16;1;1;1;1})
В идеале, буквы Ъ, Ы, Э нужно исключать. Исключим их вместе с "Ь", поскольку их коды идут подряд, а "Ь" потом проверим отдельно:
... -{204,5;222,5;165;170;175;178;220})<{13;1;1;1;1;1;1})

Для того, чтобы проверить, есть ли в тексте цифры, формула массива будет такой: =ИЛИ(ABS(КОДСИМВ(ПСТР(ПРОПИСН(A1);СТРОКА(ИНДЕКС(B:B;1):ИНДЕКС(B:B;ДЛСТР(A1)));1))-52,5)<5)

Файл-пример прилагается.

Всем привет  
Мой вариант по быстрее будет    

не знаю на счёт OpenOffice, но в 2010м работает ещё и такая формула    
=ЕСЛИ(ЕЧЁТН(ПОИСКПОЗ(1;ЧАСТОТА(E2;$A$2:$B$24);0));1;0)

ой, там в формуле в ячейках E3:E6 должна стоять абсолютная адресация для диапазона Лист2!$A$1:$A$1439

исправил, перезалил*

Всем привет!   Решил и я тряхнуть стариной и поучаствовать.... размять извилины.... сделал свой вариант, зацените   (смеюсь, потому что сам не ожидал, что так лихо получится). Правда, без дополнительного столбца не обошлось, но он не требует распространения. Для любого кол-ва циклов производства и видов остановки он будет всегда одинаков. Его вообще можно создать на каком-нибудь скрытом листе. Речь идёт про столбец А на Листе 2. Стобец В - сугубо информационный, для визуального контроля временных диапазонов, в рассчётах он не используется.

AlbertSF предлоижил вариант тоже хороший и интересный, но он чуть недоработан. Его формула не учитывает время начала цикла. Например, если начать 1й цикл производства не с 8:00, а с 9:15, то его формула это не отработает.

У меня к Еxcel до сих пор противоречивое отношение - то я его люблю, то ненавижу и ругаю самыми последними трёхэтажными матерными словами, обзывая программистов, которые его писали    Я, например, до сих пор дома работаю в 2003м   и на дух не переношу 2007+ офисы! Просто вызывают отвращение все его новшенства...    На работе стоят 2007 и 2010 и сколько уже ни работаю в них, всё равно меня всё бесит! И это не просто, что я не могу привыкнуть... это нечто большее... а ругать его - ой как много там есть за что...

Да, кстати, все конечно будут с меня смеяться, но и операционка дома у меня только Windows XP !  

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

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

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