Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сумма нескольких строк с другого листа - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма нескольких строк с другого листа (Формулы/Formulas)
Сумма нескольких строк с другого листа
Iren Дата: Вторник, 26.09.2017, 11:22 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

2010
Добрый день!
Прошу помочь с формулой.
:D Учусь преобразовывать обычные формулы в формулы массива и пока не все получается.
Нужно через формулу массива получить аналог следующей формулы (с определенного листа книги сумма трех строк по нужным столбцам):
Код
=ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ("'"&$A4&"'!$A:$Z");ПОИСКПОЗ("01. Текущая деятельность";ДВССЫЛ("'"&$A4&"'!$A:$A");0);ПОИСКПОЗ(L$1;ДВССЫЛ("'"&$A4&"'!$1:$1");0)));0)+ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ("'"&$A4&"'!$A:$Z");ПОИСКПОЗ("02. Инвестиционная деятельность";ДВССЫЛ("'"&$A4&"'!$A:$A");0);ПОИСКПОЗ(L$1;ДВССЫЛ("'"&$A4&"'!$1:$1");0)));0)+ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ("'"&$A4&"'!$A:$Z");ПОИСКПОЗ("03. Финансовая деятельность";ДВССЫЛ("'"&$A4&"'!$A:$A");0);ПОИСКПОЗ(L$1;ДВССЫЛ("'"&$A4&"'!$1:$1");0)));0)


Изменила на:
Код
={СУММ(ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ("'"&$A4&"'!$A:$Z");ПОИСКПОЗ({"01. Текущая деятельность";"02. Инвестиционная деятельность";"03. Финансовая деятельность"};ДВССЫЛ("'"&$A4&"'!$A:$A");0);ПОИСКПОЗ(L$1;ДВССЫЛ("'"&$A4&"'!$1:$1");0)));0))}

Не работает: выводит только значение первой строки нужного столбца, а не сумму по строкам.

Пример во вложении.

Заранее спасибо.
К сообщению приложен файл: 8380999.xlsx (17.1 Kb)


Сообщение отредактировал Iren - Вторник, 26.09.2017, 12:29
 
Ответить
СообщениеДобрый день!
Прошу помочь с формулой.
:D Учусь преобразовывать обычные формулы в формулы массива и пока не все получается.
Нужно через формулу массива получить аналог следующей формулы (с определенного листа книги сумма трех строк по нужным столбцам):
Код
=ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ("'"&$A4&"'!$A:$Z");ПОИСКПОЗ("01. Текущая деятельность";ДВССЫЛ("'"&$A4&"'!$A:$A");0);ПОИСКПОЗ(L$1;ДВССЫЛ("'"&$A4&"'!$1:$1");0)));0)+ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ("'"&$A4&"'!$A:$Z");ПОИСКПОЗ("02. Инвестиционная деятельность";ДВССЫЛ("'"&$A4&"'!$A:$A");0);ПОИСКПОЗ(L$1;ДВССЫЛ("'"&$A4&"'!$1:$1");0)));0)+ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ("'"&$A4&"'!$A:$Z");ПОИСКПОЗ("03. Финансовая деятельность";ДВССЫЛ("'"&$A4&"'!$A:$A");0);ПОИСКПОЗ(L$1;ДВССЫЛ("'"&$A4&"'!$1:$1");0)));0)


Изменила на:
Код
={СУММ(ЕСЛИОШИБКА(СУММ(ИНДЕКС(ДВССЫЛ("'"&$A4&"'!$A:$Z");ПОИСКПОЗ({"01. Текущая деятельность";"02. Инвестиционная деятельность";"03. Финансовая деятельность"};ДВССЫЛ("'"&$A4&"'!$A:$A");0);ПОИСКПОЗ(L$1;ДВССЫЛ("'"&$A4&"'!$1:$1");0)));0))}

Не работает: выводит только значение первой строки нужного столбца, а не сумму по строкам.

Пример во вложении.

Заранее спасибо.
Автор - Iren
Дата добавления - 26.09.2017 в 11:22
_Boroda_ Дата: Вторник, 26.09.2017, 11:31 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16881
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Прочитайте Правила форума
Исправьте название темы согласно п.2 Правил форума - Вам обязательно формулой массива и обязательно связкой ИНДЕКС-ПОИСКПОЗ или Вам важен результат?
Приложите файл в формате Excel
Оформите формулу тегами (кнопка fx в режиме правки поста)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПрочитайте Правила форума
Исправьте название темы согласно п.2 Правил форума - Вам обязательно формулой массива и обязательно связкой ИНДЕКС-ПОИСКПОЗ или Вам важен результат?
Приложите файл в формате Excel
Оформите формулу тегами (кнопка fx в режиме правки поста)
Автор - _Boroda_
Дата добавления - 26.09.2017 в 11:31
Iren Дата: Вторник, 26.09.2017, 12:55 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

2010
Мне нужно научиться преобразовывать нагромождение формул в ячейке в более "легкие" для пересчета формулы, т.к. файлы, с которыми имею дело, заполнены похожими длинными формулами, элементы которых повторяют друг друга, отчего при обновлении файла компьютер подвисает на несколько минут.
 
Ответить
СообщениеМне нужно научиться преобразовывать нагромождение формул в ячейке в более "легкие" для пересчета формулы, т.к. файлы, с которыми имею дело, заполнены похожими длинными формулами, элементы которых повторяют друг друга, отчего при обновлении файла компьютер подвисает на несколько минут.
Автор - Iren
Дата добавления - 26.09.2017 в 12:55
Nic70y Дата: Вторник, 26.09.2017, 14:06 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 9126
Репутация: 2415 ±
Замечаний: 0% ±

Excel 2010
Код
=СУММПРОИЗВ((ПСТР(ДВССЫЛ("'"&$A2&"'!A2:A1000");3;1)=".")*ИНДЕКС(ДВССЫЛ("'"&$A2&"'!A2:Y1000");;ПОИСКПОЗ(B$1;ДВССЫЛ("'"&$A2&"'!1:1");)))

ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=СУММПРОИЗВ((ПСТР(ДВССЫЛ("'"&$A2&"'!A2:A1000");3;1)=".")*ИНДЕКС(ДВССЫЛ("'"&$A2&"'!A2:Y1000");;ПОИСКПОЗ(B$1;ДВССЫЛ("'"&$A2&"'!1:1");)))
Автор - Nic70y
Дата добавления - 26.09.2017 в 14:06
_Boroda_ Дата: Вторник, 26.09.2017, 15:06 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16881
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Еще вариант
Код
=СУММ(ДВССЫЛ($A2&"!B2:B999"))/2

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант
Код
=СУММ(ДВССЫЛ($A2&"!B2:B999"))/2
Автор - _Boroda_
Дата добавления - 26.09.2017 в 15:06
Iren Дата: Вторник, 26.09.2017, 15:16 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

2010
Предложенный вариант кажется "легче". Буду иметь ввиду. Спасибо.

Только не всегда можно найти общий для строк знак или словосочетание в ячейке,
Цитата Nic70y, 26.09.2017 в 14:06, в сообщении № 4 ()
ПСТР(ДВССЫЛ("'"&$A2&"'!A2:A1000");3;1)="."


может потребоваться вводить все условия в формулу через ";".
Код
{"01. Текущая деятельность";"02. Инвестиционная деятельность";"03. Финансовая деятельность"}

Можно ли предусмотреть этот случай?
 
Ответить
СообщениеПредложенный вариант кажется "легче". Буду иметь ввиду. Спасибо.

Только не всегда можно найти общий для строк знак или словосочетание в ячейке,
Цитата Nic70y, 26.09.2017 в 14:06, в сообщении № 4 ()
ПСТР(ДВССЫЛ("'"&$A2&"'!A2:A1000");3;1)="."


может потребоваться вводить все условия в формулу через ";".
Код
{"01. Текущая деятельность";"02. Инвестиционная деятельность";"03. Финансовая деятельность"}

Можно ли предусмотреть этот случай?
Автор - Iren
Дата добавления - 26.09.2017 в 15:16
Iren Дата: Вторник, 26.09.2017, 15:50 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

2010
Цитата _Boroda_, 26.09.2017 в 15:06, в сообщении № 5 ()
Еще вариант

=СУММ(ДВССЫЛ($A2&"!B2:B999"))/2


Не поняла, как это работает, но, это не то.
Мне нужно собрать информацию с листов, как это сделано в таблице B2:I4 на листе "Общий". Только в этой таблице формула использует в качестве слагаемых одинаковые элементы с разницей только в одном условии. Надо упростить формулу, избавить от повторений. Чтобы условия сложения были прописаны через ";".


Сообщение отредактировал Iren - Вторник, 26.09.2017, 15:51
 
Ответить
Сообщение
Цитата _Boroda_, 26.09.2017 в 15:06, в сообщении № 5 ()
Еще вариант

=СУММ(ДВССЫЛ($A2&"!B2:B999"))/2


Не поняла, как это работает, но, это не то.
Мне нужно собрать информацию с листов, как это сделано в таблице B2:I4 на листе "Общий". Только в этой таблице формула использует в качестве слагаемых одинаковые элементы с разницей только в одном условии. Надо упростить формулу, избавить от повторений. Чтобы условия сложения были прописаны через ";".
Автор - Iren
Дата добавления - 26.09.2017 в 15:50
sboy Дата: Вторник, 26.09.2017, 16:18 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2566
Репутация: 724 ±
Замечаний: 0% ±

Excel 2010
Iren, Добрый день.
Если группы затрат перед названием нумеруются, то в формуле от Nic70y, Кусок с ПСТР можно заменить на проверку первой цифры
Код
=СУММПРОИЗВ((ЕЧИСЛО(--ЛЕВБ(ДВССЫЛ("'"&$A2&"'!A2:A1000");1))*ИНДЕКС(ДВССЫЛ("'"&$A2&"'!A2:Y1000");;ПОИСКПОЗ(B$1;ДВССЫЛ("'"&$A2&"'!1:1");))))

Вариант формулы от _Boroda_, можно вот так изменить, чтоб протягивался
Код
=СУММ(ДВССЫЛ($A2&"!"&АДРЕС(2;СТОЛБЕЦ())&":"&АДРЕС(999;СТОЛБЕЦ())))/2
К сообщению приложен файл: 8380999-1-.xlsx (17.2 Kb)

Яндекс: 410016850021169
 
Ответить
СообщениеIren, Добрый день.
Если группы затрат перед названием нумеруются, то в формуле от Nic70y, Кусок с ПСТР можно заменить на проверку первой цифры
Код
=СУММПРОИЗВ((ЕЧИСЛО(--ЛЕВБ(ДВССЫЛ("'"&$A2&"'!A2:A1000");1))*ИНДЕКС(ДВССЫЛ("'"&$A2&"'!A2:Y1000");;ПОИСКПОЗ(B$1;ДВССЫЛ("'"&$A2&"'!1:1");))))

Вариант формулы от _Boroda_, можно вот так изменить, чтоб протягивался
Код
=СУММ(ДВССЫЛ($A2&"!"&АДРЕС(2;СТОЛБЕЦ())&":"&АДРЕС(999;СТОЛБЕЦ())))/2
Автор - sboy
Дата добавления - 26.09.2017 в 16:18
_Boroda_ Дата: Вторник, 26.09.2017, 17:58 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16881
Репутация: 6593 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Цитата sboy, 26.09.2017 в 16:18, в сообщении № 8 ()
Вариант формулы от _Boroda_, можно вот так изменить, чтоб протягивался

И вот так еще можно
Код
=СУММ(ДВССЫЛ($A2&"!R2C:R999C";))/2
К сообщению приложен файл: 8380999_1.xlsx (16.9 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Цитата sboy, 26.09.2017 в 16:18, в сообщении № 8 ()
Вариант формулы от _Boroda_, можно вот так изменить, чтоб протягивался

И вот так еще можно
Код
=СУММ(ДВССЫЛ($A2&"!R2C:R999C";))/2
Автор - _Boroda_
Дата добавления - 26.09.2017 в 17:58
Iren Дата: Среда, 27.09.2017, 10:07 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

2010
_Boroda_, sboy, спасибо за варианты, но они не подошли.
Я в приложенном примере не отразила всей структуры файлов, показала самый простой вариант. Поэтому, из-за моей недальновидности, Ваши формулы подходят для данного конкретного примера, но не являются универсальными - для других файлов с похожей структурой ими не воспользоваться:
Цитата _Boroda_, 26.09.2017 в 17:58, в сообщении № 9 ()
Если группы затрат перед названием нумеруются

Пронумерованы также и подстатьи внутри статей; а внутри подстатей свои подстатьи с нумерацией... Оформление файлов не очень удачное - все номера арабскими цифрами, разница только в отступах внутри ячейки перед номером: для одной подгруппировки 3 пробела, для подгруппировки внутри нее 5 пробелов и т.д.

Решение нашла в самом первом предложенном варианте! Еще раз спасибо, Nic70y!
Вот итоговая формула с массивом:
Код
=СУММПРОИЗВ((ДВССЫЛ("'"&$A2&"'!A2:A1000")={"01. Текущая деятельность";"02. Инвестиционная деятельность";"03. Финансовая деятельность"})*ИНДЕКС(ДВССЫЛ("'"&$A2&"'!A2:Y1000");;ПОИСКПОЗ(B$1;ДВССЫЛ("'"&$A2&"'!1:1");)))   


Сообщение отредактировал Iren - Среда, 27.09.2017, 10:10
 
Ответить
Сообщение_Boroda_, sboy, спасибо за варианты, но они не подошли.
Я в приложенном примере не отразила всей структуры файлов, показала самый простой вариант. Поэтому, из-за моей недальновидности, Ваши формулы подходят для данного конкретного примера, но не являются универсальными - для других файлов с похожей структурой ими не воспользоваться:
Цитата _Boroda_, 26.09.2017 в 17:58, в сообщении № 9 ()
Если группы затрат перед названием нумеруются

Пронумерованы также и подстатьи внутри статей; а внутри подстатей свои подстатьи с нумерацией... Оформление файлов не очень удачное - все номера арабскими цифрами, разница только в отступах внутри ячейки перед номером: для одной подгруппировки 3 пробела, для подгруппировки внутри нее 5 пробелов и т.д.

Решение нашла в самом первом предложенном варианте! Еще раз спасибо, Nic70y!
Вот итоговая формула с массивом:
Код
=СУММПРОИЗВ((ДВССЫЛ("'"&$A2&"'!A2:A1000")={"01. Текущая деятельность";"02. Инвестиционная деятельность";"03. Финансовая деятельность"})*ИНДЕКС(ДВССЫЛ("'"&$A2&"'!A2:Y1000");;ПОИСКПОЗ(B$1;ДВССЫЛ("'"&$A2&"'!1:1");)))   
Автор - Iren
Дата добавления - 27.09.2017 в 10:07
Iren Дата: Среда, 27.09.2017, 12:44 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 0 ±
Замечаний: 0% ±

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

Я была уверена, что формулы массива увеличивают производительность, но ошиблась.
Только теперь нашла статью с советом избегать формул массива и пользоваться "обычными" формулами для повышения производительности.

Буду думать дальше. %) Но это уже отдельная тема.
 
Ответить
СообщениеКак ни странно, с новой формулой обновление книг стало еще более медленным. Пришлось вернуть первоначальную формулу с повторами.

Я была уверена, что формулы массива увеличивают производительность, но ошиблась.
Только теперь нашла статью с советом избегать формул массива и пользоваться "обычными" формулами для повышения производительности.

Буду думать дальше. %) Но это уже отдельная тема.
Автор - Iren
Дата добавления - 27.09.2017 в 12:44
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма нескольких строк с другого листа (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!