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

Вход

Регистрация

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

 

= Мир MS Excel/Сумма ячеек, связанных определенной последовательностью. - Мир MS Excel

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

Excel 2010
Доброго времени суток.
Суть вопроса состоит в том, что необходимо измерить расстояние от начальной точки до конечной. Участков много, имеются разветвления пути. Необходимо знать расстояние от исходной точки 1 до всех конечных точек разветвлений (см. упрощенный пример). Расчет ячеек G15 и G16 должен производится автоматически, исходя из соединений участков по точкам. Нагляднее это увидеть в примере.
К сообщению приложен файл: 5029672.xlsx (11.4 Kb)
 
Ответить
СообщениеДоброго времени суток.
Суть вопроса состоит в том, что необходимо измерить расстояние от начальной точки до конечной. Участков много, имеются разветвления пути. Необходимо знать расстояние от исходной точки 1 до всех конечных точек разветвлений (см. упрощенный пример). Расчет ячеек G15 и G16 должен производится автоматически, исходя из соединений участков по точкам. Нагляднее это увидеть в примере.
Автор - buka77
Дата добавления - 23.05.2018 в 08:29
Светлый Дата: Среда, 23.05.2018, 09:20 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1868
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
С ограничениями на глубину просмотра и левая может быть только 1 массивная (Ctrl+Shift+Enter) формула:
Код
=СУММПРОИЗВ(C$15:C$20;(B$15:B$20=F15)+(МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20))

Доб.
С учётом левой:
Код
=СУММПРОИЗВ(--(A$15:A$20>=E15);C$15:C$20;(B$15:B$20=F15)+(МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Среда, 23.05.2018, 09:31
 
Ответить
СообщениеС ограничениями на глубину просмотра и левая может быть только 1 массивная (Ctrl+Shift+Enter) формула:
Код
=СУММПРОИЗВ(C$15:C$20;(B$15:B$20=F15)+(МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20))

Доб.
С учётом левой:
Код
=СУММПРОИЗВ(--(A$15:A$20>=E15);C$15:C$20;(B$15:B$20=F15)+(МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20)+(МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=МИН(ЕСЛИ(B$15:B$20=F15;A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20));A$15:A$20))=B$15:B$20))
Автор - Светлый
Дата добавления - 23.05.2018 в 09:20
buka77 Дата: Среда, 23.05.2018, 11:18 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 6 ±
Замечаний: 0% ±

Excel 2010
Светлый, спасибо. Формула работает. Но, как я вижу, ее длина зависит от количества участков. А если их буде 50 или 100? Нет ли чего-нибудь универсального, типа ВПР, ИНДЕКС, ПОИСКПОЗ или еще чего подобного?
 
Ответить
СообщениеСветлый, спасибо. Формула работает. Но, как я вижу, ее длина зависит от количества участков. А если их буде 50 или 100? Нет ли чего-нибудь универсального, типа ВПР, ИНДЕКС, ПОИСКПОЗ или еще чего подобного?
Автор - buka77
Дата добавления - 23.05.2018 в 11:18
anvg Дата: Среда, 23.05.2018, 13:09 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 581
Репутация: 271 ±
Замечаний: 0% ±

2016, 365
Доброе время суток
Цитата buka77, 23.05.2018 в 11:18, в сообщении № 3 ()
А если их буде 50 или 100? Нет ли чего-нибудь универсального

Более универсально на VBA - поищите по фразе "обход дерева в глубину".
 
Ответить
СообщениеДоброе время суток
Цитата buka77, 23.05.2018 в 11:18, в сообщении № 3 ()
А если их буде 50 или 100? Нет ли чего-нибудь универсального

Более универсально на VBA - поищите по фразе "обход дерева в глубину".
Автор - anvg
Дата добавления - 23.05.2018 в 13:09
buka77 Дата: Среда, 23.05.2018, 13:24 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 6 ±
Замечаний: 0% ±

Excel 2010
VBA - это не для моего ума (((
Нашел немного громоздкое решение через вспомогательные ячейки, но это прибавило весу файлу в полмегабайта.


Сообщение отредактировал buka77 - Среда, 23.05.2018, 13:28
 
Ответить
СообщениеVBA - это не для моего ума (((
Нашел немного громоздкое решение через вспомогательные ячейки, но это прибавило весу файлу в полмегабайта.
Автор - buka77
Дата добавления - 23.05.2018 в 13:24
Светлый Дата: Среда, 23.05.2018, 13:34 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1868
Репутация: 536 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата buka77, 23.05.2018 в 11:18, в сообщении № 3 ()
ее длина зависит от количества участков

Сейчас формула рассчитана на 6 участков. Если использовать дополнительные ячейки, то формула упростится, а количество участков можно не ограничивать.
Но в любом случае будет рассчитан только один вариант цепочки из нескольких альтернативных. И не факт, что это будет самый короткий вариант.
Хотя, с дополнительными ячейками можно и более гибко поработать, если на несколько столбцов растянуть расчёт.

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата buka77, 23.05.2018 в 11:18, в сообщении № 3 ()
ее длина зависит от количества участков

Сейчас формула рассчитана на 6 участков. Если использовать дополнительные ячейки, то формула упростится, а количество участков можно не ограничивать.
Но в любом случае будет рассчитан только один вариант цепочки из нескольких альтернативных. И не факт, что это будет самый короткий вариант.
Хотя, с дополнительными ячейками можно и более гибко поработать, если на несколько столбцов растянуть расчёт.
Автор - Светлый
Дата добавления - 23.05.2018 в 13:34
MCH Дата: Среда, 23.05.2018, 14:12 | Сообщение № 7
Группа: Админы
Ранг: Старожил
Сообщений: 2008
Репутация: 752 ±
Замечаний: ±

Судя по описанию нужно найти кратчайший путь от одной точки до другой в графе.
Подойдет алгоритм Дейкстры (или другие аналогичные): http://www.excelworld.ru/forum/3-6656-1
Если нужно, то можно адаптировать решение под текущую задачу
 
Ответить
СообщениеСудя по описанию нужно найти кратчайший путь от одной точки до другой в графе.
Подойдет алгоритм Дейкстры (или другие аналогичные): http://www.excelworld.ru/forum/3-6656-1
Если нужно, то можно адаптировать решение под текущую задачу
Автор - MCH
Дата добавления - 23.05.2018 в 14:12
MCH Дата: Среда, 23.05.2018, 14:26 | Сообщение № 8
Группа: Админы
Ранг: Старожил
Сообщений: 2008
Репутация: 752 ±
Замечаний: ±

Цитата buka77, 23.05.2018 в 08:29, в сообщении № 1 ()
Участков много, имеются разветвления пути. Необходимо знать расстояние от исходной точки 1 до всех конечных точек разветвлений

Можете предоставить пример реальных данных?
 
Ответить
Сообщение
Цитата buka77, 23.05.2018 в 08:29, в сообщении № 1 ()
Участков много, имеются разветвления пути. Необходимо знать расстояние от исходной точки 1 до всех конечных точек разветвлений

Можете предоставить пример реальных данных?
Автор - MCH
Дата добавления - 23.05.2018 в 14:26
buka77 Дата: Среда, 23.05.2018, 16:21 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 43
Репутация: 6 ±
Замечаний: 0% ±

Excel 2010
Цитата MCH, 23.05.2018 в 14:26, в сообщении № 8 ()
Можете предоставить пример реальных данных?

Реальный расчет помимо длин содержит еще кучу данных и расчетов, что может только запутать. Приложенный пример в точности отображает искомую проблему и ее решение (если оно будет проще моего) даст мне необходимую информацию. Спасибо за терпение.
 
Ответить
Сообщение
Цитата MCH, 23.05.2018 в 14:26, в сообщении № 8 ()
Можете предоставить пример реальных данных?

Реальный расчет помимо длин содержит еще кучу данных и расчетов, что может только запутать. Приложенный пример в точности отображает искомую проблему и ее решение (если оно будет проще моего) даст мне необходимую информацию. Спасибо за терпение.
Автор - buka77
Дата добавления - 23.05.2018 в 16:21
MCH Дата: Четверг, 24.05.2018, 08:22 | Сообщение № 10
Группа: Админы
Ранг: Старожил
Сообщений: 2008
Репутация: 752 ±
Замечаний: ±

посмотрите вариант во вложении, возможно сможете приспособить под себя
К сообщению приложен файл: --.xlsb (82.3 Kb)
 
Ответить
Сообщениепосмотрите вариант во вложении, возможно сможете приспособить под себя
Автор - MCH
Дата добавления - 24.05.2018 в 08:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сумма ячеек, связанных определенной последовательностью. (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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