да, круто... спасибо
Избушка формулистов-3, не для вопросов, но для формульных задач
Избушка формулистов-3, не для вопросов, но для формульных задач
27.12.2022 09:15:07
упс, действительно, АГРЕГАТ( ) с параметром пропуска ошибок решает проблему, об которую я чуть не сломал голову. А я про АГРЕГАТ и забыл... стыдно .(--_--).
Ввиду того, что задача решается намного проще, выкладываю своё хитроумное решение: ФМ: =ВПР(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 вполне подходит.
Изменено: |
|
|
Избушка формулистов-3, не для вопросов, но для формульных задач
Избушка формулистов-3, не для вопросов, но для формульных задач
Избушка формулистов-3, не для вопросов, но для формульных задач
26.12.2022 22:01:16
конечно, пустые ячейки могут быть. В столбце может быть всё, что угодно. Могут быть и ошибки, но они не должны влиять на работу формулы. Но если ошибки располагаются в конце диапазона, формула их захватывать не обязана. Если получится захватить и их, то хорошо, но требование, чтобы это не был перебор всего массива, а использовался быстрый поиск
Изменено: |
|
|
Избушка формулистов-3, не для вопросов, но для формульных задач
26.12.2022 21:22:12
Привет всем любителям поизвращаться с формулами. С трудом, но всё-таки удалось решить довольно избитую, часто встречающуюся задачу - найти нижнюю границу столбца (динамического массива). Сложность заключается в том, что:
- в столбце могут быть как числа, так и текст! - искать последний элемент через быстрый поиск (с ключом 1), но воспользоваться поиском можно только 2 раза - 1 раз для текста и 1 раз для чисел - если столбец пуст, формула должна вернуть #НД() - сделать без вспомогательных ячеек/имён! - разумеется, формула не должна быть летучей - формула массива разрешается, если это не перебор всего диапазона искать, например, по столбцу А:А Периодически сталкивался с этой задачей, но сделать это одной формулой и чтобы она была быстрая, не получалось и всегда мозолили глаза вспомогательные имена или ячейки рядом. Но чувствовал, что выкрутиться можно и, наконец, получилось. Желаю успехов! для меня вся сложность заключалась в том, чтобы вернуть НД(), если столбец пуст. Это ключевое условие для работы динамического массива, т.к. 0 при подстановке в ИНДЕКС() возвратит весь столбец, а это недопустимо.
Изменено: |
|
|
Исправить крокозяблики в содержимом переменной после копирования текста из VBE
11.04.2018 16:15:33
Нет, к сожалению b = StrConv(a, vbUpperCase, 1049) тоже не сработало
Помогло
Всем спасибо |
|||
|
Исправить крокозяблики в содержимом переменной после копирования текста из VBE
Исправить крокозяблики в содержимом переменной после копирования текста из VBE
Исправить крокозяблики в содержимом переменной после копирования текста из VBE
11.04.2018 14:31:44
Да, должно помочь. Есть ссылка и на этом сайте:
Но хочется научиться делать программно. |
|
|
Исправить крокозяблики в содержимом переменной после копирования текста из VBE
Запустить на Win64 GetMem4()
30.03.2018 18:37:33
Да, все предлагаемые к скачиванию версии 32-разрядные, но это же не значит, что их нельзя запускать из 64-разрядного офиса? (я лично ещё пока не знаю, это моё предположение )
Пошла установка x64-го офиса (неанинсталлированный пакет совместимости мешал установщику запуститься). Буду пробовать.... |
|
|
Запустить на Win64 GetMem4()
30.03.2018 18:20:49
sokol92 написал:
Только вопрос, как правильно описать параметры функций, чтобы можно было их вызывать? Ладно, тогда придётся использовать CopyMemory для этих целей. Ситуация ещё усложняется тем, что я не могу попробовать у себя на компе всё это. Поставить одновременно и x86 и x64 версии офиса нельзя. Снёс х86, перезагрузился, а установщик х64 всё равно сообщает, что уже установлена 32-разрядная версия и не хочет устанавливать х64. |
|||
|
Запустить на Win64 GetMem4()
Запустить на Win64 GetMem4()
30.03.2018 00:24:06
Добрый день. Тема избитая, но запустить GetMem на 64-разрядном офисе не получается.
Нужно, чтобы заработала, например, такая конструкция:
|
|||||
|
Оптимизировать формулу подсчёта максимального числа значений в диапазонах
27.03.2018 19:05:22
Сергей, спасибо, то что надо :-) Крутил-крутил этот СМЕЩ() со всех сторон, но так и не смог заставить его выдать массив значений для каждого диапазона.
StepanWolkoff, да, будете удивлены, но это реальная структура данных. Более того, каждая таблица имеет ещё 2 служебные строки, содержащие различные объединённые ячейки, но я их обрезал, поскольку к примеру они дела не имеют. |
|
|
Оптимизировать формулу подсчёта максимального числа значений в диапазонах
27.03.2018 15:12:57
Добрый день.
В примере лежит формула, определяющая максимальное число заполненных ячеек в указанных диапазонах. Мне она не нравится, т.к. вычисляется она не на листе формулой, а из кода в событии Worksheet_Change только для той таблицы, где произошли изменения. Я сделал это 6ю отдельными строчками Application.Count() для 6 диапазонов, результаты записываются в массив, а потом у массива берётся Application.Max(). Cинтаксис получается длинный и некрасивый. Может ли кто подсказать, как можно упростить эту формулу? Приемлемы все варианты: формулой (можно исп. летучие ф-ции), можно из кода через Evaluate(), можно через WorksheetFunction(). В книге созданы имена Склад1 ... Склад6, можно привязываться к ним. |
|
|
Подсветка аргументов UDF при вызове с листа
19.03.2018 21:47:38
Вот KL тоже что-то демонстрировал, но у меня аргументы подсвечивать не начало...
|
|
|
Подсветка аргументов UDF при вызове с листа
19.03.2018 21:43:40
В общем, вот расширенный ответ на эту тему...
Вот кто-то что-то делал... |
|
|
Подсветка аргументов UDF при вызове с листа
Подсветка аргументов UDF при вызове с листа
19.03.2018 19:19:34
Андрей VG, спасибо за подсказку, будем пробовать ;-) |
|
|
Подсветка аргументов UDF при вызове с листа
Проверка ячейки на наличие в ней текста на русском языке
27.11.2017 23:19:47
Как-то столкнулся с подобной задачей... пришёл в голову такой вариант... Исследуемый текст в ячейке А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) Файл-пример прилагается. |
|
|
Формулой проставить единицу (метку) напротив последней имеющейся даты (невыходной день)
Проверка наличия числа в диапазоне.
Расчет времени (суммирование) в интервале
Расчет времени (суммирование) в интервале
09.12.2016 22:45:13
Всем привет! Решил и я тряхнуть стариной и поучаствовать.... размять извилины.... сделал свой вариант, зацените (смеюсь, потому что сам не ожидал, что так лихо получится). Правда, без дополнительного столбца не обошлось, но он не требует распространения. Для любого кол-ва циклов производства и видов остановки он будет всегда одинаков. Его вообще можно создать на каком-нибудь скрытом листе. Речь идёт про столбец А на Листе 2. Стобец В - сугубо информационный, для визуального контроля временных диапазонов, в рассчётах он не используется.
Изменено: |
|
|
Насколько вы любите программу Excel?, Какую роль сыграла эта программа в вашей жизни
27.11.2016 17:35:51
У меня к Еxcel до сих пор противоречивое отношение - то я его люблю, то ненавижу и ругаю самыми последними трёхэтажными матерными словами, обзывая программистов, которые его писали Я, например, до сих пор дома работаю в 2003м и на дух не переношу 2007+ офисы! Просто вызывают отвращение все его новшенства... На работе стоят 2007 и 2010 и сколько уже ни работаю в них, всё равно меня всё бесит! И это не просто, что я не могу привыкнуть... это нечто большее... а ругать его - ой как много там есть за что...
Да, кстати, все конечно будут с меня смеяться, но и операционка дома у меня только Windows XP !
Изменено: |
|
|
Сослать объектную переменную на запущенное приложение экселя в отдельном процессе
01.04.2013 11:50:00
Ну, тогда может возникнуть такая ситуация, когда файл лежит где-то на сервере в расшаренной папке, и открыт по сети на каком-то компьютере. А проверить нужно, открыл ли файл именно на том компьютере, на котором выполняется этот код (а это может быть и не сам файл-сервер). Я думаю, что нужно идти другим путём, без привязки к файловой системе, а к привязке к запущенным процессам экселя и открытым книгам в нём. |
|
|
Сослать объектную переменную на запущенное приложение экселя в отдельном процессе