Подскажите, пожалуйста, как написать формулу суммирования по ID по строкам. Есть первый список объектов (вкладов в банках), и для каждого объекта есть несколько показателей (остаток, ставка, сумма процентов и т.д.). При этом каждый показатель ведется не в отдельном столбце, а расположены по строкам. Т.е. строк столько, сколько объектов *(умножить) на количество показателей.
Далее есть второй список других объектов (текущих счетов), у них тоже есть свои показатели (суммы выплаченных процентов, остаток, и т.д.)
Для всех объектов есть идентификаторы (ID). При этом объекты первого списка и второго списка связаны между собой по ID: У объектов первого списка (вкладов) есть отдельный показатель в отдельной строке: ID объекта второго списка (ID текущего счета, куда выплачиваются проценты, генерируемые вкладами).
Задача такая: для объектов второго списка просуммировать определенный показатель объектов первого списка (для этого ID). Перефразируя, нужно для каждого текущего счета просуммировать количество процентов, которые падают на него с соответствующих вкладов (соответствие по ID). Строки могут быть пересортированы потом, поэтому требуется написать устойчивую к сортировке формулу, в которой будет суммирование по имени показателя.
Если бы показатели стояли в столбец, проблем бы вообще не было = суммируем определенный столбец, если в другом столбце стоит нужный ID. Но проблема в том, что эти показатели расположены не по столбцам, а по строкам. Как написать такую формулу?
Прикладываю Excel.
Подскажите, пожалуйста, как написать формулу суммирования по ID по строкам. Есть первый список объектов (вкладов в банках), и для каждого объекта есть несколько показателей (остаток, ставка, сумма процентов и т.д.). При этом каждый показатель ведется не в отдельном столбце, а расположены по строкам. Т.е. строк столько, сколько объектов *(умножить) на количество показателей.
Далее есть второй список других объектов (текущих счетов), у них тоже есть свои показатели (суммы выплаченных процентов, остаток, и т.д.)
Для всех объектов есть идентификаторы (ID). При этом объекты первого списка и второго списка связаны между собой по ID: У объектов первого списка (вкладов) есть отдельный показатель в отдельной строке: ID объекта второго списка (ID текущего счета, куда выплачиваются проценты, генерируемые вкладами).
Задача такая: для объектов второго списка просуммировать определенный показатель объектов первого списка (для этого ID). Перефразируя, нужно для каждого текущего счета просуммировать количество процентов, которые падают на него с соответствующих вкладов (соответствие по ID). Строки могут быть пересортированы потом, поэтому требуется написать устойчивую к сортировке формулу, в которой будет суммирование по имени показателя.
Если бы показатели стояли в столбец, проблем бы вообще не было = суммируем определенный столбец, если в другом столбце стоит нужный ID. Но проблема в том, что эти показатели расположены не по столбцам, а по строкам. Как написать такую формулу?
Спасибо, но это все же не то... В вашей формуле смысл в том, что суммируются строки, номер которых на единицу меньше строки с ID - я этот вариант тоже сам рассматривал, это сделать несложно. Но как я писал выше, строки могут быть очень сильно пересортированы, и например строки с суммами процентов будут идти в самом конце сразу по всем вкладам, а ID - где-то в середине. Да и если новую строку с показателем добавить в середину, то эта формула перестает работать.
В той формуле, которую я ищу, можно оперировать только следующими параметрами : ID и название показателей "Сумма процентов", "ID счета куда выпл. проценты". Как такую формулу написать, которая будет искать строки только с этими именами и сравнивать по ID? Тут не получится предполагать что строка с суммами идет рядом со строкой с "ID счета куда выпл. проценты", они могут быть как угодно пересортированы в списке.
Спасибо, но это все же не то... В вашей формуле смысл в том, что суммируются строки, номер которых на единицу меньше строки с ID - я этот вариант тоже сам рассматривал, это сделать несложно. Но как я писал выше, строки могут быть очень сильно пересортированы, и например строки с суммами процентов будут идти в самом конце сразу по всем вкладам, а ID - где-то в середине. Да и если новую строку с показателем добавить в середину, то эта формула перестает работать.
В той формуле, которую я ищу, можно оперировать только следующими параметрами : ID и название показателей "Сумма процентов", "ID счета куда выпл. проценты". Как такую формулу написать, которая будет искать строки только с этими именами и сравнивать по ID? Тут не получится предполагать что строка с суммами идет рядом со строкой с "ID счета куда выпл. проценты", они могут быть как угодно пересортированы в списке.Serrg
Сообщение отредактировал Serrg - Суббота, 05.12.2015, 19:33
Количество вкладов на один счёт ограничено десятью "СТОЛБЕЦ(A:J)", но можно увеличить
Спасибо, работает правильно, но конечно мудрёно, потом сложно будет вспомнить и понять смысл. А для чего приходится делать СТОЛБЕЦ(A:J)? это чтобы Эксель при расчете генерировал последовательно числа от 1 до 10, т.е. как источник генерации? А на СТРОКА(1:10) я так понимаю будет не перейти, т.к. по строкам уже другая часть массива работает - так?
Не могли бы поподробнее пояснить, вот эта часть формулы что делает?: НАИМЕНЬШИЙ(ЕСЛИ((E$7:E$31="ID счета куда выпл. Проценты")*(F$7:F$31=C2);C$7:C$31);СТОЛБЕЦ(A:Z))
В файле будет около 300 вкладов. И столбцов будет около 1000 (каждый столбец на каждую дату). Интересно на сколько долго все это будет работать?
Количество вкладов на один счёт ограничено десятью "СТОЛБЕЦ(A:J)", но можно увеличить
Спасибо, работает правильно, но конечно мудрёно, потом сложно будет вспомнить и понять смысл. А для чего приходится делать СТОЛБЕЦ(A:J)? это чтобы Эксель при расчете генерировал последовательно числа от 1 до 10, т.е. как источник генерации? А на СТРОКА(1:10) я так понимаю будет не перейти, т.к. по строкам уже другая часть массива работает - так?
Не могли бы поподробнее пояснить, вот эта часть формулы что делает?: НАИМЕНЬШИЙ(ЕСЛИ((E$7:E$31="ID счета куда выпл. Проценты")*(F$7:F$31=C2);C$7:C$31);СТОЛБЕЦ(A:Z))
В файле будет около 300 вкладов. И столбцов будет около 1000 (каждый столбец на каждую дату). Интересно на сколько долго все это будет работать?Serrg
Сообщение отредактировал Serrg - Воскресенье, 06.12.2015, 01:04
Не могли бы поподробнее пояснить, вот эта часть формулы что делает?: НАИМЕНЬШИЙ(ЕСЛИ((E$7:E$31="ID счета куда выпл. Проценты")*(F$7:F$31=C2);C$7:C$31);СТОЛБЕЦ(A:Z))
Создаёт массив (в строку) ID вкладов для конкретного счёта. Можно в строке редактирования выделить эту часть формулы и нажать F9. Будет виден получившийся массив. Если формулу нужно протягивать по столбцам, надо добавить знак $ в соответствующие диапазоны.
Код
=СУММ(ЕСЛИ(($E$7:$E$36="Сумма процентов")*($C$7:$C$36=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(($E$7:$E$36="ID счета куда выпл. Проценты")*(F$7:F$36=$C2);$C$7:$C$36);СТОЛБЕЦ($A:$Z));"#"));F$7:F$36))
Не могли бы поподробнее пояснить, вот эта часть формулы что делает?: НАИМЕНЬШИЙ(ЕСЛИ((E$7:E$31="ID счета куда выпл. Проценты")*(F$7:F$31=C2);C$7:C$31);СТОЛБЕЦ(A:Z))
Создаёт массив (в строку) ID вкладов для конкретного счёта. Можно в строке редактирования выделить эту часть формулы и нажать F9. Будет виден получившийся массив. Если формулу нужно протягивать по столбцам, надо добавить знак $ в соответствующие диапазоны.
Код
=СУММ(ЕСЛИ(($E$7:$E$36="Сумма процентов")*($C$7:$C$36=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(($E$7:$E$36="ID счета куда выпл. Проценты")*(F$7:F$36=$C2);$C$7:$C$36);СТОЛБЕЦ($A:$Z));"#"));F$7:F$36))
Создаёт массив (в строку) ID вкладов для конкретного счёта.
А СТОЛБЕЦ(), а не СТРОКА() нужно для того, чтобы этот массив был другого измерения, по горизонтали а не вертикали? Если сделать СТРОКА(), то не работает.
Т.е. СТОЛБЕЦ() сделан для того, чтобы 2 условия: ($E$7:$E$36="Сумма процентов") $C$7:$C$36=**ID вкладов для этого счета**
Как бы образовывали матрицу, а не линейную строку? и были перпендикулярны друг другу, независимыми. так?
А если потом нужно будет добавить 3-е условие отбора строк, то откуда сможем взять 3-е измерение? Формула в таком виде уже не будет работать? Больше никак нельзя сделать чтобы уйти от конкретного количества столбцов в СТОЛБЕЦ(A:J)?
Создаёт массив (в строку) ID вкладов для конкретного счёта.
А СТОЛБЕЦ(), а не СТРОКА() нужно для того, чтобы этот массив был другого измерения, по горизонтали а не вертикали? Если сделать СТРОКА(), то не работает.
Т.е. СТОЛБЕЦ() сделан для того, чтобы 2 условия: ($E$7:$E$36="Сумма процентов") $C$7:$C$36=**ID вкладов для этого счета**
Как бы образовывали матрицу, а не линейную строку? и были перпендикулярны друг другу, независимыми. так?
А если потом нужно будет добавить 3-е условие отбора строк, то откуда сможем взять 3-е измерение? Формула в таком виде уже не будет работать? Больше никак нельзя сделать чтобы уйти от конкретного количества столбцов в СТОЛБЕЦ(A:J)?Serrg
Сообщение отредактировал Serrg - Воскресенье, 06.12.2015, 14:44
А если потом нужно будет добавить 3-е условие отбора строк, то откуда сможем взять 3-е измерение? Формула в таком виде уже не будет работать? Больше никак нельзя сделать чтобы уйти от конкретного количества столбцов в СТОЛБЕЦ(A:J)?
Если бы Excel предусматривал третье измерение, то формулу можно было бы написать... Даже не представляю, какое ещё можно третье ортогональное условие включить в исходную таблицу... В таком случае просто ввести дополнительный столбец и тогда любое количество условий можно забить в него, а его использовать для выбора суммируемых значений. А вообще проблема возникла из-за избыточности и нерационального расположения исходных данных.
А если потом нужно будет добавить 3-е условие отбора строк, то откуда сможем взять 3-е измерение? Формула в таком виде уже не будет работать? Больше никак нельзя сделать чтобы уйти от конкретного количества столбцов в СТОЛБЕЦ(A:J)?
Если бы Excel предусматривал третье измерение, то формулу можно было бы написать... Даже не представляю, какое ещё можно третье ортогональное условие включить в исходную таблицу... В таком случае просто ввести дополнительный столбец и тогда любое количество условий можно забить в него, а его использовать для выбора суммируемых значений. А вообще проблема возникла из-за избыточности и нерационального расположения исходных данных.Светлый
А вообще проблема возникла из-за избыточности и нерационального расположения исходных данных.
Я уже подумал чтобы для ID куда выплачиваются проценты сделать отдельный столбец просто, а не отдельные строки у каждого вклада.
А как по-вашему лучше расположить данные, если для каждого вклада будет порядка 40 показателей, а всего вкладов будет 100-300, и должна быть удобная возможность их добавлять время от времени. И при этом еще нужно видеть значения на каждую дату - т.е. столбцов будет столько, сколько дней есть за 3 года например - 1000 столбцов.
Я уже по-разному думал располагать - и транспонировать, чтобы вклады шли по столбцам. Но в этом случае не получается сделать удобную фильтрацию - когда хочу посмотреть только по определенным вкладам определенные показатели - поэтому пришлось их по строкам располагать.
А в вашей формуле про СТОЛБЕЦ(), а не СТРОКА() - я правильно написал, чтобы образовывалась двумерная матрица?
А вообще проблема возникла из-за избыточности и нерационального расположения исходных данных.
Я уже подумал чтобы для ID куда выплачиваются проценты сделать отдельный столбец просто, а не отдельные строки у каждого вклада.
А как по-вашему лучше расположить данные, если для каждого вклада будет порядка 40 показателей, а всего вкладов будет 100-300, и должна быть удобная возможность их добавлять время от времени. И при этом еще нужно видеть значения на каждую дату - т.е. столбцов будет столько, сколько дней есть за 3 года например - 1000 столбцов.
Я уже по-разному думал располагать - и транспонировать, чтобы вклады шли по столбцам. Но в этом случае не получается сделать удобную фильтрацию - когда хочу посмотреть только по определенным вкладам определенные показатели - поэтому пришлось их по строкам располагать.Serrg
А в вашей формуле про СТОЛБЕЦ(), а не СТРОКА() - я правильно написал, чтобы образовывалась двумерная матрица?
Да. Я выше написал об этом. Я бы предложил сделать индексный столбец, объединив в нём ID счёта и ID вклада. Или ID счёта и вклада разнести по разным столбцам, при этом можно убрать много строк "ID счета куда выпл. проценты". Формулы упростятся, а быстродействие увеличится.
А в вашей формуле про СТОЛБЕЦ(), а не СТРОКА() - я правильно написал, чтобы образовывалась двумерная матрица?
Да. Я выше написал об этом. Я бы предложил сделать индексный столбец, объединив в нём ID счёта и ID вклада. Или ID счёта и вклада разнести по разным столбцам, при этом можно убрать много строк "ID счета куда выпл. проценты". Формулы упростятся, а быстродействие увеличится.Светлый
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
