если в столбце [А]...

1) только числовые значения, то

=СУММ(B9:ИНДЕКС(B9:B10000;ПОИСКПОЗ(9E+307;$A9:$A10000)))

2) только текстовые значения, то

=СУММ(B9:ИНДЕКС(B9:B10000;ПОИСКПОЗ(ПОВТОР("z";255);$A9:$A10000)))

3) может быть, что угодно, то

=СУММ(B9:ИНДЕКС(B:B;МАКС(ЕСЛИ($A9:$A1000<>"";СТРОКА($A9:$A1000)))))
(формула массива - подтверждается одновременным нажатием CTRL+SHIFT+ENTER)

KL
[MVP - Microsoft Excel]

DeggasadПривет! Это ты говоришь про последнюю пустую строку! А я из условия понял что нужно первую пустую найти!
=СУММ(B9:ИНДЕКС($B$9:$B$5000;СУММПРОИЗВ(ПОИСКПОЗ("";$A$9:$A$5000&"";0))-1))

Привет. В таком случае след. модифицированная формула должна быть несколько быстрее твоего варианта:

=СУММ(B9:ИНДЕКС(B:B;МИН(ЕСЛИ($A9:$A1000="";СТРОКА($A9:$A1000)-1))))
(формула массива - подтверждается одновременным нажатием CTRL+SHIFT+ENTER)

или еще так:

=СУММ(B9:ИНДЕКС(B9:B10000;ПОИСКПОЗ(ИСТИНА;ЕПУСТО(A9:A10000);0)-1))
(формула массива - подтверждается одновременным нажатием CTRL+SHIFT+ENTER)

или так:

=СУММ(B9:ИНДЕКС(B9:B10000;ПОИСКПОЗ(ИСТИНА;A9:A10000="";0)-1))
(формула массива - подтверждается одновременным нажатием CTRL+SHIFT+ENTER)

1) если тебе нужно через VBA, дык ты предупреждай заранее :-(
2) все приведенные формулы основаны на том, что ты сразу используешь диапазон, который никогда не будет заполнен до конца. Отсюда и B9:B10000 в моих примерах. Если данных может быть еще больше, то используй B9:B65535, но при таком кол-ве строк пересчет будет занимать значительное время (особенно с формулой предложенной Deggasad :P).

KL
[MVP - Microsoft Excel]

tuandreevaЗа все примеры спасибо, все работает, но хотелось бы в VBA


KL
[MVP - Microsoft Excel]

tuandreevaне получается, в столбце если в 1 ячейке не цифры, то сумма не получается, а как же быть с проверяемым столбцом А

Можно поподробнее в чем проблема?

См пример.

Deggasad KL (XL)особенно с формулой предложенной Deggasad :P).

KL
[MVP - Microsoft Excel]

:-)


тут бурные авации

Это не наезд :-) Конкатенация - штука до противного медленная :-)

KL
[MVP - Microsoft Excel]

DeggasadА что такое конкатенация? Я конечно догадываюсь, но всё-таки...

Это по-эмигрантски: СЦЕПЛЕНИЕ ТЕКСТА :-)))

KL
[MVP - Microsoft Excel]

Deggasad KL (XL)
Привет. В таком случае след. модифицированная формула должна быть несколько быстрее твоего варианта:

=СУММ(B9:ИНДЕКС(B:B;МИН(ЕСЛИ($A9:$A1000="";СТРОКА($A9:$A1000)-1))))
(формула массива - подтверждается одновременным нажатием CTRL+SHIFT+ENTER)

или еще так:

=СУММ(B9:ИНДЕКС(B9:B10000;ПОИСКПОЗ(ИСТИНА;ЕПУСТО(A9:A10000);0)-1))
(формула массива - подтверждается одновременным нажатием CTRL+SHIFT+ENTER)

или так:

=СУММ(B9:ИНДЕКС(B9:B10000;ПОИСКПОЗ(ИСТИНА;A9:A10000="";0)-1))
(формула массива - подтверждается одновременным нажатием CTRL+SHIFT+ENTER)

2 KL (XL)

Как считаешь удачная ли эта формула для нахождения первого больше нуля знаяения???

=СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;J4:O4>0;0))

Пожалуй, другое мне на ум не приходит :-)

KL
[MVP - Microsoft Excel]

yuniki2) А вот здесь я не могу разобраться - Делаю все по Вашей инструкции - пытаюсь добавить имя в меню "Вставка - Имя -Присвоить" диапазону - не появляется имя диапазона в окошке Именованных диапазонов (Excel2K,2003 - без разницы). У Вас вообще диапазон именуется или нет ?
Ничего, ничего! Это случается когда человек начинает спор о том, что лучше VB или Excel, не имея представления о том, что из себя представляет последний :-)

а) То что вы называете именованным диапазоном, на самом деле является частным случаем именованной формулы .

=A1:A100
={"a":"b":"c"}
={1;2;3}
=$A$4:ИНДЕКС($A$4:$A$65536;ПОИСКПОЗ("яяяяя";$A$3:$A$65536))
и т.д. и т.п. и пр.

б) Имя диапазона в окошке Именованных диапазонов появляется только в случае если именованная формула содержит исключительно ссылку на диапазон. Это вовсе не означает, что имя, не отображаемое в списке Имен, не существует или не может быть использовано. Кстати, для справки именованная формула предложенная Degassad видна из-под меню Вставка-Имя-Присвоить, но можно сделать совсем невидимое имя с помощью проперти Visible.

То, что именованные формулы это нужный и мощный инструмент - неоспоримый факт. Сие относится и к VB и даже к VBA и тут совершенно нет необходимости обращать Папу Римского в католицизм. То что ты пишешь напоминает позицию человека, который однажды утром, проснувшись, понял, что единственное, что помогает от всех болезней - это Аспирин. Ура, товарищи!!!

yunikiНо как-бы то ни было, что проще - использовать Вашу формулу
=Лист1!$A$4:ИНДЕКС(Лист1!$A$4:$A$65536;ПОИСКПОЗ("яяяяя";Лист!$A$3:$A$65536))
или мою
=СУММ(Поле2)
?
Почувствуйте разницу

Шельмуешь, айяяяй!
=СУММ(Поле2) надо сравнивать с =СУММ(Поле2) , где Поле2 содержит именованную формулу, а вот =$A$4:ИНДЕКС($A$4:$A$65536;ПОИСКПОЗ("яяяяя";$A$3:$A$65536)) сравни с кодом в твоей DLL ;-)

yuniki- У Вас задействован в обработке Весь А столбец, в формуле чтобы разобраться надо потрудиться - читать справку в несколько листов по функциям ПОИСКПОЗ, ИНДЕКС. САм диапазон жестко(здесь м.б.возражения, но ,просто,долго объяснять), в отличие от моего , привязан к указанным ячейкам. Короче - как я сказал выше - некрасиво все это.

Это опять-таки от незнания:
а) читать справку по функциям ПОИСКПОЗ, ИНДЕКС тоже, как видно, не всем помогает, но ведь, чтобы установить DLL или пуще того редактировать код в ней, что-нибудь, но прочесть придется ;-)
б) жесткая привязка убирается как минимум с пом. относительных ссылок, но можно и параметры передавать: http://www.jkp-ads.com/Articles/ExcelNames09.htm


yunikiВ моей формуле все ясно даже интуитивно - суммируются значения столбца Поле2, котрый является именованным диапазоном, который динамически изменяет свои размеры(для поддержки чего и устанавливается дополнение к Office - DLL'ка) задействованы в обработке только его ячейки, а не весь столбец A.

Этот аргумент построен на ложной предпосылке - читай выше.

KL
[MVP - Microsoft Excel]

yunikiТак в чем проблема ?
Добавить аддон (DLL'ку) к офису ? Так надо понимать, что если хотите красиво и эффективно работать, то для этого нужно программное обеспечение ПО. (Так , если бы Вы захотели работать с Excel и при этом не устанавливать Excel, то Ваше желание было бы несколько странно)
Знаете , на заре распространения PC, директора считали, что достаточно купить компьютры, а они уже сами будут решать их задачи, потом оказалось, - ан нет, нужно еще ПО покупать, да еще и разбираться - какое именно ПО.
А впрочем, - почему "на заре", где-то до сих пор такое мнение живет, Только сейчас PC неразрывно связывается с установленным на нем Windows и Office, а , разумеется, все вне этого - уже считается за гранью человеческого понимания.
Красноречиво, черт побери! У меня аж слезы наворачиваются. Я уже вижу, как ты рассылаешь свое произведение (читай книгу Excel + отдельно DLL c инструкциями) в другие подразделения компании, поставщикам, клиентам, друзьям, начальнику [желающему открыть твой файл почему-то не в офисе]; и все они, дружно, преодолевая свое незнание, политики безопасности, проблемы совместимости версий, отсутствие администраторских прав, времени и желания, устанавливают твое "универсальное решение", чтобы получить доступ к динамическим диапазонам. Вот поистине "красивое" решение! Или все ранее перечисленное не входило в твои планы?

Мимоходом, мы еще не затрагивали вопрос скорости пересчета и совместимости с различными версиями Excel твоей "чудо-функции" ;-)

KL
[MVP - Microsoft Excel]

Кстати, у Excel, начиная с версии 2002 (XP) существует родное, общедоступное, легко понятное, но редко используемое решение для динамических диапазонов, и имя ему - Данные-Список-Создать список...

Так-что, если речь об этих версиях, то и с именованными формулами связываться необязательно, ну а пресловутую DLL - вообще на свалку истории :-)

KL
[MVP - Microsoft Excel]