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

Вход

Регистрация

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

 

= Мир MS Excel/подсчет суммы длин пересечения двух диапозонов - Мир MS Excel

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

Excel 2007
Здравствуйте.Мне нужно подсчитать сумму длин пересечений двух диапазонов. Диапазон в экселе задан в виде двух границ -начало и конец отрезка. и таких отрезков много, а диапазона два.Причем диапазон 1 меньше диапазона 2.В приложенном рисунке все постарался подробно разрисовать.А значения диапазонов в файле экселя.
P.S пытался сам разобраться, но не получается -серого вещества не хватает. Буду весьма благодарен если подскажите как такое можно реализовать в экселе.Заранее огромное спасибо.
К сообщению приложен файл: 8158873.png (55.2 Kb) · 7970675.xlsx (13.7 Kb)

sem95
 
Ответить
СообщениеЗдравствуйте.Мне нужно подсчитать сумму длин пересечений двух диапазонов. Диапазон в экселе задан в виде двух границ -начало и конец отрезка. и таких отрезков много, а диапазона два.Причем диапазон 1 меньше диапазона 2.В приложенном рисунке все постарался подробно разрисовать.А значения диапазонов в файле экселя.
P.S пытался сам разобраться, но не получается -серого вещества не хватает. Буду весьма благодарен если подскажите как такое можно реализовать в экселе.Заранее огромное спасибо.
Автор - sem95
Дата добавления - 08.04.2018 в 20:41
AlexM Дата: Воскресенье, 08.04.2018, 22:16 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
При показанных данных покажите что должно получится. И поясните полученный результат.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеПри показанных данных покажите что должно получится. И поясните полученный результат.
Автор - AlexM
Дата добавления - 08.04.2018 в 22:16
Pelena Дата: Воскресенье, 08.04.2018, 22:30 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 19420
Репутация: 4567 ±
Замечаний: ±

Excel 365 & Mac Excel
Как поняла, с доп. столбцом
Код
=СУММПРОИЗВ(--ТЕКСТ((D3-ТЕКСТ(D3-$B$3:$B$77;"Основной;\0"))-(ТЕКСТ($A$3:$A$77-C3;"Основной;\0")+C3);"Основной;\0"))
К сообщению приложен файл: 5883923.xlsx (15.3 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеКак поняла, с доп. столбцом
Код
=СУММПРОИЗВ(--ТЕКСТ((D3-ТЕКСТ(D3-$B$3:$B$77;"Основной;\0"))-(ТЕКСТ($A$3:$A$77-C3;"Основной;\0")+C3);"Основной;\0"))
Автор - Pelena
Дата добавления - 08.04.2018 в 22:30
sem95 Дата: Воскресенье, 08.04.2018, 22:49 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
AlexM извините поздно увидел, но все равно спасибо что откликнулись) Pelena да все правильно поняли, то что я хотел. Спасибо огромное, снимаю шляпу перед вашей эрудицией))

sem95
 
Ответить
СообщениеAlexM извините поздно увидел, но все равно спасибо что откликнулись) Pelena да все правильно поняли, то что я хотел. Спасибо огромное, снимаю шляпу перед вашей эрудицией))
Автор - sem95
Дата добавления - 08.04.2018 в 22:49
sem95 Дата: Воскресенье, 08.04.2018, 23:30 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Да мне еще помогли c этой задачей на еще одном форуме там другое решение через макросы вот ссылка:
http://forum.msexcel.ru/index.php/topic,11872.0.html
https://www.planetaexcel.ru/forum....8&tags=

sem95

Сообщение отредактировал sem95 - Воскресенье, 08.04.2018, 23:42
 
Ответить
СообщениеДа мне еще помогли c этой задачей на еще одном форуме там другое решение через макросы вот ссылка:
http://forum.msexcel.ru/index.php/topic,11872.0.html
https://www.planetaexcel.ru/forum....8&tags=
Автор - sem95
Дата добавления - 08.04.2018 в 23:30
Светлый Дата: Понедельник, 09.04.2018, 08:40 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1852
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
Pelena, формула нормально отрабатывает, но в исходных данных есть пересекающиеся диапазоны, которые суммируются дважды. Результат неправильный. Строки 104, 115, 119, 126 суммировать не надо.

Программировать проще, чем писать стихи.
 
Ответить
СообщениеPelena, формула нормально отрабатывает, но в исходных данных есть пересекающиеся диапазоны, которые суммируются дважды. Результат неправильный. Строки 104, 115, 119, 126 суммировать не надо.
Автор - Светлый
Дата добавления - 09.04.2018 в 08:40
Pelena Дата: Понедельник, 09.04.2018, 08:57 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19420
Репутация: 4567 ±
Замечаний: ±

Excel 365 & Mac Excel
Ну, во-первых, не факт, что не нужно. Мы ведь не знаем, что это за числа.
А во-вторых, уже столько решений предложили на разных форумах, что переделывать неинтересно :)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеНу, во-первых, не факт, что не нужно. Мы ведь не знаем, что это за числа.
А во-вторых, уже столько решений предложили на разных форумах, что переделывать неинтересно :)
Автор - Pelena
Дата добавления - 09.04.2018 в 08:57
Светлый Дата: Понедельник, 09.04.2018, 11:10 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1852
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
На другие форумы не заглядывал. Предложу ещё и свои варианты. Тоже не учитывают пересечение исходных диапазонов. Массивные:
Код
=СУММ(ЕСЛИ((D3<A$3:A$77)+(C3>B$3:B$77);;ЕСЛИ(D3<B$3:B$77;D3;B$3:B$77)-ЕСЛИ(C3>A$3:A$77;C3;A$3:A$77)))

Код
=СУММ((1-(D3<A$3:A$77)-(C3>B$3:B$77))*(ЕСЛИ(D3<B$3:B$77;D3;B$3:B$77)-ЕСЛИ(C3>A$3:A$77;C3;A$3:A$77)))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеНа другие форумы не заглядывал. Предложу ещё и свои варианты. Тоже не учитывают пересечение исходных диапазонов. Массивные:
Код
=СУММ(ЕСЛИ((D3<A$3:A$77)+(C3>B$3:B$77);;ЕСЛИ(D3<B$3:B$77;D3;B$3:B$77)-ЕСЛИ(C3>A$3:A$77;C3;A$3:A$77)))

Код
=СУММ((1-(D3<A$3:A$77)-(C3>B$3:B$77))*(ЕСЛИ(D3<B$3:B$77;D3;B$3:B$77)-ЕСЛИ(C3>A$3:A$77;C3;A$3:A$77)))
Автор - Светлый
Дата добавления - 09.04.2018 в 11:10
Светлый Дата: Понедельник, 09.04.2018, 11:32 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1852
Репутация: 523 ±
Замечаний: 0% ±

Excel 2013, 2016
Окончательная формула массивная без дополнительных ячеек:
Код
=СУММ((1-(ТРАНСП(D3:D170)<A3:A77)-(ТРАНСП(C3:C170)>B3:B77))*(ЕСЛИ(ТРАНСП(D3:D170)<B3:B77;ТРАНСП(D3:D170);B3:B77)-ЕСЛИ(ТРАНСП(C3:C170)>A3:A77;ТРАНСП(C3:C170);A3:A77)))

Программировать проще, чем писать стихи.
 
Ответить
СообщениеОкончательная формула массивная без дополнительных ячеек:
Код
=СУММ((1-(ТРАНСП(D3:D170)<A3:A77)-(ТРАНСП(C3:C170)>B3:B77))*(ЕСЛИ(ТРАНСП(D3:D170)<B3:B77;ТРАНСП(D3:D170);B3:B77)-ЕСЛИ(ТРАНСП(C3:C170)>A3:A77;ТРАНСП(C3:C170);A3:A77)))
Автор - Светлый
Дата добавления - 09.04.2018 в 11:32
dude Дата: Понедельник, 09.04.2018, 17:37 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 193
Репутация: 28 ±
Замечаний: 0% ±

2016
Код
=СУММПРОИЗВ(ЕСЛИОШИБКА((ЕСЛИ(D3:D170>ТРАНСП(B3:B77);ТРАНСП(B3:B77);D3:D170)-ЕСЛИ(C3:C170>ТРАНСП(A3:A77);C3:C170;ТРАНСП(A3:A77)))^0,5;)^2)
 
Ответить
Сообщение
Код
=СУММПРОИЗВ(ЕСЛИОШИБКА((ЕСЛИ(D3:D170>ТРАНСП(B3:B77);ТРАНСП(B3:B77);D3:D170)-ЕСЛИ(C3:C170>ТРАНСП(A3:A77);C3:C170;ТРАНСП(A3:A77)))^0,5;)^2)
Автор - dude
Дата добавления - 09.04.2018 в 17:37
Мир MS Excel » Вопросы и решения » Вопросы по Excel » подсчет суммы длин пересечения двух диапозонов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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