Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 11.03.2011, 09:34   #1
Severny
Форумчанин
 
Регистрация: 07.10.2010
Сообщений: 144
По умолчанию Сумма из одного столбца с числами в зависимости от интервала дат из другого столбца

Добрый всем день!
В Примере 25 есть таблица. Колонка Т1 представляет собой даты, Т2-числа.
Помогите вычислить сумму из колонки Т2 которая попадает в интервал между двумя красными значениями. Есть еще особенность что значения красным меняются, количество строк в колонках может быть разное и значения колонок не всегда находятся на дной строке (как показано в примере)
Вложения
Тип файла: rar Пример 25.rar (9.2 Кб, 52 просмотров)
Severny вне форума Ответить с цитированием
Старый 11.03.2011, 09:46   #2
vikttur
Участник клуба
 
Регистрация: 16.05.2010
Сообщений: 1,249
По умолчанию

Контрольные даты не обязательно располагать напротив идентичных дат, можно в любом месте
Код:
=СУММ(ИНДЕКС(E26:E36;ПОИСКПОЗ(B15;C13:C23;0)):ИНДЕКС(E26:E36;ПОИСКПОЗ(B19;C13:C23;0)))
vikttur вне форума Ответить с цитированием
Старый 11.03.2011, 10:26   #3
Severny
Форумчанин
 
Регистрация: 07.10.2010
Сообщений: 144
По умолчанию

Цитата:
Сообщение от vikttur Посмотреть сообщение
Контрольные даты не обязательно располагать напротив идентичных дат, можно в любом месте
Код:
=СУММ(ИНДЕКС(E26:E36;ПОИСКПОЗ(B15;C13:C23;0)):ИНДЕКС(E26:E36;ПОИСКПОЗ(B19;C13:C23;0)))
Благодарю!
Есть еще 2 уточняющих вопроса которые я к сожалению забыл озвучить.
Как быть если контрольные даты не из числа дат столбца Т1 (выдает #Н/Д), тоесть суммировать ближайшие к ним?
Если контрольные даты из числа дат столбцов Т1 (как в примере), то как первую контрольную дату включить в интервал а вторую нет(то есть >= и <)?
Severny вне форума Ответить с цитированием
Старый 11.03.2011, 10:34   #4
vikttur
Участник клуба
 
Регистрация: 16.05.2010
Сообщений: 1,249
По умолчанию

1. Для неточного совпадения ПОИСКПОЗ(...;...;1)
2. Во второй ИНДЕКС
Код:
 ПОИСКПОЗ(B19;C13:C23;1)-1
vikttur вне форума Ответить с цитированием
Старый 11.03.2011, 13:56   #5
Severny
Форумчанин
 
Регистрация: 07.10.2010
Сообщений: 144
По умолчанию

Ув. vikttur спасибо Вам за помощь.
Смотрите, как Вы и рекомендовали я в функции ПОИСКПОЗ [тип_сопоставления] я поставил значение 1. Проверяем: Контрольные даты 14 февраля и 18 февраля. При визуальном подсчете должно давать 15, а возвращает 18. Что может быть не так? Это раз. Второе: Если [тип_сопоставления]=1 то массив должен быть упорядочен по возрастанию. В реальности у меня есть разброс, хоть и не большой, но иногда ближайшие даты меняются местами. Как это побороть? (сортировка не подойдет)
И третье: п. 1 и п.2 что я озвучивал выше должны как-то умещаться в одну формулу, потому что как я понимаю если "ПОИСКПОЗ(B19;C13:C23;1)-1" то при неидентичних контрольных татах с датами массива одна нужная дата будет у меня выпадать.
Помогите решить эти вопросы.
Severny вне форума Ответить с цитированием
Старый 11.03.2011, 14:19   #6
vikttur
Участник клуба
 
Регистрация: 16.05.2010
Сообщений: 1,249
По умолчанию

Код:
=СУММ(ИНДЕКС(E26:E36;ПОИСКПОЗ(B15;C13:C23;1)+ЕНД(ПОИСКПОЗ(B15;C13:C23;))):ИНДЕКС(E26:E36;ПОИСКПОЗ(B19;C13:C23;1)))
2. Если без сортировки - только усложняя формулу или применяя VBA.
3. Не понял.
vikttur вне форума Ответить с цитированием
Старый 11.03.2011, 14:28   #7
IgorGO
Новичок
СтарожилДжуниор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

уважаемый, Severny,
тут можно не только вопросы задавать, а еще и анализировать полученные ответы и учиться...
Код:
=СУММПРОИЗВ((R[-5]C[-2]:R[5]C[-2]>=R[-1]C)*(R[-5]C[-2]:R[5]C[-2]<=R[1]C)*R[8]C:R[18]C)
Вложения
Тип файла: rar Книга614.rar (9.6 Кб, 41 просмотров)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO вне форума Ответить с цитированием
Старый 12.03.2011, 10:47   #8
Severny
Форумчанин
 
Регистрация: 07.10.2010
Сообщений: 144
По умолчанию

Цитата:
Сообщение от vikttur Посмотреть сообщение
Код:
=СУММ(ИНДЕКС(E26:E36;ПОИСКПОЗ(B15;C13:C23;1)+ЕНД(ПОИСКПОЗ(B15;C13:C23;))):ИНДЕКС(E26:E36;ПОИСКПОЗ(B19;C13:C23;1)))
...
3. Не понял.
За код спасибо!
По поводу п.3: В сообщении №4 даной темы Вы предложили как не включать в интервал конечную контрольную дату.
"ПОИСКПОЗ(B19;C13:C23;1)-1"
Но этот прием работает когда контрольные даты из числа дат массива. Если же они разные то пропадает одна попадающая в интервал дата. Я это имел ввиду.
Severny вне форума Ответить с цитированием
Старый 12.03.2011, 10:54   #9
Severny
Форумчанин
 
Регистрация: 07.10.2010
Сообщений: 144
По умолчанию

Цитата:
Сообщение от IgorGO Посмотреть сообщение
уважаемый, Severny,
тут можно не только вопросы задавать, а еще и анализировать полученные ответы и учиться...
IgorGO, практически во всех случая я именно этим и занимаюсь, а если я не до конца проанализировал полученый ответ, то Вы уж простите.
Цитата:
Сообщение от IgorGO Посмотреть сообщение
Код:
=СУММПРОИЗВ((R[-5]C[-2]:R[5]C[-2]>=R[-1]C)*(R[-5]C[-2]:R[5]C[-2]<=R[1]C)*R[8]C:R[18]C)
Спасибо за формулу! Как раз ее я и поставил на вооружение своей таблицы, но вот ее логику понять чето не получается, особенно 2 первые части произвидения.
Severny вне форума Ответить с цитированием
Старый 12.03.2011, 11:33   #10
IgorGO
Новичок
СтарожилДжуниор
 
Аватар для IgorGO
 
Регистрация: 05.02.2008
Сообщений: 9,487
По умолчанию

логика в этой формуле практически отсутствует)) но все-таки есть...
в СУММПРОИЗВ умножаются 3 массива.
первый массив получаем сравнением каджого элемента Т1 (каждой даты из Т1) на предмет Т1 >= "начальная датой"
второй массив Т1 <= "конечная дата"
третий массив - таблица Т2 (сами данные) которые нужно просуммировать. Предполагается что Т1 и Т2 имеют одинаковый размер.
в первых двух массивах получим значения ИСТИНА или ЛОЖЬ в зависимости от результатов сравнения, при умножении друг на друга ИСТИНА участвует как 1, ЛОЖЬ как 0.

Все это удобно посмотреть, если запустить пошаговое выполнение формулы - Вы будете видеть промежуточные этапы вычислений
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
IgorGO вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
Удаление строк столбца совпадающих со строками другого столбца ShamanGood Microsoft Office Excel 23 18.09.2010 09:26
Выбор и перенос максимальных значений из столбца с учетом временного интервала. Tidus Microsoft Office Excel 74 10.02.2010 17:18
Выборка чисел по значению другого столбца faker Microsoft Office Excel 4 10.11.2009 14:40
В прямоугольной матрице переставить столбцы так, чтобы сумма элементов столбца росла от первого столбца Aleo13 Помощь студентам 5 15.10.2008 18:34