Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

как формулой задать диапазон переменной длины?

Автор ali, 10.11.2013, 18:23

« назад - далее »

ali

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

Заранее спасибо за совет.
Александр

cheshiki1

пример приложите приближенный к оригиналу.

TimSha

Цитата: ali от 10.11.2013, 18:23
... мне нужно суммировать ячейки в первом столбце от A1 до первой пустой.
Пустая - последняя в поле/столбе или может быть между данными в поле/столбе?
Данные оформить списками/таблицами с пром итогам - пробовали?!
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)

ali

Ситуация примерно такая: на лист в первую колонку загружаются числа (каким-то внешним макросом). Просуммировать требуется числа в этой колонке от A1 до первой встретившейся пустой ячейки.
Например, в ячейках от A1 до A5 - единицы, А6 - пустая, от A7 до A10 - опять единицы. Нужная мне формула для данного случая: =сумм(А1:А5) . В следующий раз в ячейках A1 и A2 - единицы, A3 - пустая, A5 - A10 - опять единицы. Для данного варианта мне нужна формула  =сумм(А1:А2) .

Как записать единую формулу, которая не зависит от длины диапазона до первой пустой ячейки в колонке?

Poltava

Я так понимаю пример приложить религия не позволяет. Ну раз вам лениво то мне и подавно! формула массива =СУММ(ДВССЫЛ("A1:A"&ПОИСКПОЗ(ИСТИНА();(A:A="");0)))
Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

zs


interseptor

А если немного усложнить,
дано: кол-во строк переменное, всегда разное,
необходимо: чтобы формула "произведения по условию" перемножала данные в строке, причем брала в учет только заполненные строки.
пример прилепил, помогите плиз,
спасиба

cheshiki1

interseptor вариант
=СУММПРОИЗВ((СМЕЩ($A$1;;;СЧЁТЗ($A:$A);1)=$N$1)*(СМЕЩ($B$1;;;СЧЁТЗ($A:$A);1)=$O$1)*(СМЕЩ($C$1;;;СЧЁТЗ($A:$A);1)))
такой вариант не рассматриваете?
=СУММЕСЛИМН(C:C;A:A;N1;B:B;O1)

ali

Poltava, спасибо, всё работает.
Не могли бы вы только пояснить формулу: я не понимаю синтаксиса куска с ПоискПоз.
Второй параметр этой функции вроде должен быть тем диапазоном, который просматривается, а тут стоит условие (A:A="").
Как это понимать?

И почему для поиска номера первой пустой ячейки в колонке "A" не работает формула:
=ПОИСКПОЗ("";A1:A9999;0)
(возвращает #Н/Д при наличии пустой ячейки)

interseptor

cheshiki1 спасибо за оперативность, оба варианта хороши.
Мой вариант меня не удовлетворял тем, что 1000 строк считало быстро, а вот 10000 уже несколько секунд, немного в принципе, но заметно дольше (даже когда строки пустые). Хотел чтобы пустые строки не обсчитывало, тем самым экономило время.

Какой из ваших двух вариантов более быстрый будет для обсчета (таких суммарных расчетных ячеек предполагается 1000 штук примерно, в каждой ссылка на 4 или 5 значений в строке)?? или я зря парюсь с "быстродействием"??

Poltava

ЦитироватьНе могли бы вы только пояснить формулу... а тут стоит условие (A:A="")
Это не условие это и есть просматриваеваемый диапазон то есть данное сравнение в формуле масива возвращает МАССИВ {истина;ложьж;истина...} соответственно для каждой ячейки из A:A будет возвращен результат проверки на соответствие ="" и именно в этом получившимя масиве мы и ищем первое удовлетворяющее нашему условию значение то есть первую истину. Надеюсь понятно изложил.
Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

zs

Poltava, Формула не считает, когда А1-нет значения, cheshiki1, результат формул почему-то 0 С уважением, ZS(5)

Poltava

ЦитироватьPoltava, Формула не считает, когда А1-нет значения
Почему не считает прекрасно считает! в А1 нет значения следовательно это ПЕРВАЯ ПУСТАЯ ЯЧЕЙКА и естественно что сумма значений до нее равна нулю! Если нужно иначе то это уже будет формула от ПЕРВОЙ пустой до ВТОРОЙ пустой
Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

cheshiki1

zs формула с ф-цией СМЕЩ не для вашего случая, а СУММЕСЛИМН все верно считает у вас в столбце В нет 1. все условия в формуле должны выполняться для суммирования. в В7 исправьте на 1 - увидите.

zs

Poltava, понял - с изменением содержимого ячеек необходимо менять формулу; cheshiki1 понял формулу - но связи с суммой до пустой ячейки не видно.
С уважением, ZS(5)