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

Вход

Регистрация

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

 

= Мир MS Excel/Найти диапазон относительно ячейки с форм. и суммировать его - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти диапазон относительно ячейки с форм. и суммировать его (Формулы/Formulas)
Найти диапазон относительно ячейки с форм. и суммировать его
origatosan Дата: Четверг, 14.01.2016, 14:02 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Здраствуйте. Надеюсь вы сможете помочь решить эту задачу :) . Суть заключается в следующем: формула( которую я ищу), вбита в определенную ячейку (F8) и относительно этой ячейки (в которую она вбита), она должна найти в определенном столбце(D) верхнюю границу (ячейку, содержащую текст "S сеч. с запасом") и от нее суммировать ячейки этого же столбца (D) до нижней границы (ячейки, содержащей такой же текст "S сеч. с запасом").

В результате должно получиться, что при вставке этой формулы, сумма выводится автоматически и не зависит от изменения количества позиций (строк).

Изображение и файл прилагаются.
К сообщению приложен файл: 7543384.jpg(31Kb) · 8516082.xls(42Kb)


Жизнь прекрасна

Сообщение отредактировал origatosan - Четверг, 14.01.2016, 14:24
 
Ответить
СообщениеЗдраствуйте. Надеюсь вы сможете помочь решить эту задачу :) . Суть заключается в следующем: формула( которую я ищу), вбита в определенную ячейку (F8) и относительно этой ячейки (в которую она вбита), она должна найти в определенном столбце(D) верхнюю границу (ячейку, содержащую текст "S сеч. с запасом") и от нее суммировать ячейки этого же столбца (D) до нижней границы (ячейки, содержащей такой же текст "S сеч. с запасом").

В результате должно получиться, что при вставке этой формулы, сумма выводится автоматически и не зависит от изменения количества позиций (строк).

Изображение и файл прилагаются.

Автор - origatosan
Дата добавления - 14.01.2016 в 14:02
Udik Дата: Четверг, 14.01.2016, 14:06 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1376
Репутация: 178 ±
Замечаний: 0% ±

Excel 2016 х 64
Изображения прилагаются

Шо, опять!?
К сообщению приложен файл: 5207784.jpg(21Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Четверг, 14.01.2016, 14:19
 
Ответить
Сообщение
Изображения прилагаются

Шо, опять!?

Автор - Udik
Дата добавления - 14.01.2016 в 14:06
origatosan Дата: Четверг, 14.01.2016, 14:21 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Извините за ошибки).
К сообщению приложен файл: _Microsoft_Offi.jpg(39Kb)


Жизнь прекрасна

Сообщение отредактировал origatosan - Четверг, 14.01.2016, 14:25
 
Ответить
СообщениеИзвините за ошибки).

Автор - origatosan
Дата добавления - 14.01.2016 в 14:21
Nic70y Дата: Четверг, 14.01.2016, 14:28 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3924
Репутация: 831 ±
Замечаний: 0% ±

Excel 2013
вбита в определенную ячейку (F8)
нет там ни чего :(
проверяйте:
Код
=СУММ(ИНДЕКС(D:D;ПОИСКПОЗ("S сеч. с запасом";D$1:D$18000;)+1):ИНДЕКС(D$1:D$18000;ПОИСКПОЗ("S сеч. с запасом";ИНДЕКС(D$1:D$18000;ПОИСКПОЗ("S сеч. с запасом";D$1:D$18000;)+1):D$18000;)+ПОИСКПОЗ("S сеч. с запасом";D$1:D$18000;)))
вдруг правильно


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Четверг, 14.01.2016, 14:29
 
Ответить
Сообщение
вбита в определенную ячейку (F8)
нет там ни чего :(
проверяйте:
Код
=СУММ(ИНДЕКС(D:D;ПОИСКПОЗ("S сеч. с запасом";D$1:D$18000;)+1):ИНДЕКС(D$1:D$18000;ПОИСКПОЗ("S сеч. с запасом";ИНДЕКС(D$1:D$18000;ПОИСКПОЗ("S сеч. с запасом";D$1:D$18000;)+1):D$18000;)+ПОИСКПОЗ("S сеч. с запасом";D$1:D$18000;)))
вдруг правильно

Автор - Nic70y
Дата добавления - 14.01.2016 в 14:28
_Boroda_ Дата: Четверг, 14.01.2016, 14:37 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 10334
Репутация: 4357 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=ЕСЛИ(ЕЧИСЛО(D3);"";СУММ(D2:ИНДЕКС(D$1:D2;ПРОСМОТР(;-1/ЕОШ(-D$1:D2);СТРОКА(D$1:D2)))))

Я на своем файле. У Вас пример очень уж усеченный

Еще проще (файл _1)
Код
=ЕСЛИ(ЕЧИСЛО(D3);"";СУММ(D$1:D2)-СУММ(F1:F$1))
К сообщению приложен файл: 1864286.xlsx(11Kb) · 1864286_1.xlsx(11Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=ЕСЛИ(ЕЧИСЛО(D3);"";СУММ(D2:ИНДЕКС(D$1:D2;ПРОСМОТР(;-1/ЕОШ(-D$1:D2);СТРОКА(D$1:D2)))))

Я на своем файле. У Вас пример очень уж усеченный

Еще проще (файл _1)
Код
=ЕСЛИ(ЕЧИСЛО(D3);"";СУММ(D$1:D2)-СУММ(F1:F$1))

Автор - _Boroda_
Дата добавления - 14.01.2016 в 14:37
китин Дата: Четверг, 14.01.2016, 14:37 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4117
Репутация: 636 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
Если числа в столбце D всегда находятся МЕЖДУ ячейками с надписью "S сеч. с запасом" то вполне хватит
Код
=СУММ($D$2:$D$12)

при добавлении строк в этом диапазоне (между строчками с надписью "S сеч. с запасом" формула сама изменит диапазон


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
СообщениеЕсли числа в столбце D всегда находятся МЕЖДУ ячейками с надписью "S сеч. с запасом" то вполне хватит
Код
=СУММ($D$2:$D$12)

при добавлении строк в этом диапазоне (между строчками с надписью "S сеч. с запасом" формула сама изменит диапазон

Автор - китин
Дата добавления - 14.01.2016 в 14:37
origatosan Дата: Четверг, 14.01.2016, 14:49 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Nic70y, Он правильно посчитал, но только сначало. Когда ниже в таблице вбил эту формулу, она выдала то же значение, что и в первый раз. Она у вас считает с самого начала, поэтому определяет только сумму между двумя верхними "S сеч. с запасом". Однако спасибо за попытку). К сожалению исходник я не могу выложить, он весит 5 мб.


Жизнь прекрасна
 
Ответить
СообщениеNic70y, Он правильно посчитал, но только сначало. Когда ниже в таблице вбил эту формулу, она выдала то же значение, что и в первый раз. Она у вас считает с самого начала, поэтому определяет только сумму между двумя верхними "S сеч. с запасом". Однако спасибо за попытку). К сожалению исходник я не могу выложить, он весит 5 мб.

Автор - origatosan
Дата добавления - 14.01.2016 в 14:49
Nic70y Дата: Четверг, 14.01.2016, 14:56 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3924
Репутация: 831 ±
Замечаний: 0% ±

Excel 2013
ну так бы сразу и сказали:
Код
=СУММ(ИНДЕКС(D:D;СУММПРОИЗВ(МАКС((D$1:D8="S сеч. с запасом")*СТРОКА(D$1:D8)))):D8)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениену так бы сразу и сказали:
Код
=СУММ(ИНДЕКС(D:D;СУММПРОИЗВ(МАКС((D$1:D8="S сеч. с запасом")*СТРОКА(D$1:D8)))):D8)

Автор - Nic70y
Дата добавления - 14.01.2016 в 14:56
origatosan Дата: Четверг, 14.01.2016, 14:57 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
_Boroda_, Спасибо большое. Именно то, что надо hands


Жизнь прекрасна
 
Ответить
Сообщение_Boroda_, Спасибо большое. Именно то, что надо hands

Автор - origatosan
Дата добавления - 14.01.2016 в 14:57
origatosan Дата: Четверг, 14.01.2016, 15:04 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Nic70y, Спасибо). Формула считает неверно к сожалению. Сумму меньше истинной почему-то выдает.


Жизнь прекрасна
 
Ответить
СообщениеNic70y, Спасибо). Формула считает неверно к сожалению. Сумму меньше истинной почему-то выдает.

Автор - origatosan
Дата добавления - 14.01.2016 в 15:04
origatosan Дата: Четверг, 14.01.2016, 15:06 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
китин, К сожалению не работает правильно). Но спасибо. Прав оказался _Boroda_


Жизнь прекрасна
 
Ответить
Сообщениекитин, К сожалению не работает правильно). Но спасибо. Прав оказался _Boroda_

Автор - origatosan
Дата добавления - 14.01.2016 в 15:06
Nic70y Дата: Четверг, 14.01.2016, 15:29 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3924
Репутация: 831 ±
Замечаний: 0% ±

Excel 2013
неверно к сожалению
странно, попаразитировал на файле _Boroda_
К сообщению приложен файл: 9934925.xlsx(11Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
неверно к сожалению
странно, попаразитировал на файле _Boroda_

Автор - Nic70y
Дата добавления - 14.01.2016 в 15:29
origatosan Дата: Пятница, 15.01.2016, 06:54 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Nic70y, Твоя формула работает отлично. Однако первая формула _Boroda_ оказалась самой универсальной. Для сравнения результатов прикреплю изображение.

Всем спасибо, мне есть чему поучиться у вас :)
К сообщению приложен файл: 2075047.jpg(33Kb)


Жизнь прекрасна

Сообщение отредактировал origatosan - Пятница, 15.01.2016, 06:56
 
Ответить
СообщениеNic70y, Твоя формула работает отлично. Однако первая формула _Boroda_ оказалась самой универсальной. Для сравнения результатов прикреплю изображение.

Всем спасибо, мне есть чему поучиться у вас :)

Автор - origatosan
Дата добавления - 15.01.2016 в 06:54
origatosan Дата: Пятница, 15.01.2016, 14:37 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 20% ±

Excel 2007
Сегодня получил довольно интересный ответ на мыле касательно моего вопроса. Формула использует массивы и привязана к слову "ветка")). плюс в том, что можно вставлять в любую ячейку в столбце (от строчки тоже вроде не зависит) и считать будет верно, если диапазон ячеек ограничен словом "ветка".

"Для F8
Код
=СУММ (ИНДЕКС (D$1:D100;МАКС ((A$1:ИНДЕКС (A$1:A100;СТРОКА ()-1)="Ветка")*СТРОКА (A$1:ИНДЕКС (A$1:A100;СТРОКА ()-1)))+1):ИНДЕКС (D$1:D100;МИН (ЕСЛИ ((ИНДЕКС (A$1:A100;СТРОКА ()+1):A100="Ветка");1;65000)*(СТРОКА (ИНДЕКС (A$1:A100;СТРОКА ()+1):A100)))-1))


Из формулы удалить все пробелы ПЕРЕД скобками (после имён функций)! Вводить, как формулу массива: в конце нажать не Enter, а сочетание Ctrl+Shift+Enter.
Потом можно копировать эту формулу в любую ячейку столбца F, кроме тех, у которых в столбце А стоит стоит "Ветка". Для нормальной обработки последнего интервала НИЖЕ всех данных в столбце А должно стоять "Ветка"!" (с)Полосатый жираф Алик

После того, как поколдовал легонько (расширил диапазон поиска) вышло вот что:
К сообщению приложен файл: 8241293.xls(63Kb)


Жизнь прекрасна

Сообщение отредактировал Manyasha - Пятница, 15.01.2016, 15:12
 
Ответить
СообщениеСегодня получил довольно интересный ответ на мыле касательно моего вопроса. Формула использует массивы и привязана к слову "ветка")). плюс в том, что можно вставлять в любую ячейку в столбце (от строчки тоже вроде не зависит) и считать будет верно, если диапазон ячеек ограничен словом "ветка".

"Для F8
Код
=СУММ (ИНДЕКС (D$1:D100;МАКС ((A$1:ИНДЕКС (A$1:A100;СТРОКА ()-1)="Ветка")*СТРОКА (A$1:ИНДЕКС (A$1:A100;СТРОКА ()-1)))+1):ИНДЕКС (D$1:D100;МИН (ЕСЛИ ((ИНДЕКС (A$1:A100;СТРОКА ()+1):A100="Ветка");1;65000)*(СТРОКА (ИНДЕКС (A$1:A100;СТРОКА ()+1):A100)))-1))


Из формулы удалить все пробелы ПЕРЕД скобками (после имён функций)! Вводить, как формулу массива: в конце нажать не Enter, а сочетание Ctrl+Shift+Enter.
Потом можно копировать эту формулу в любую ячейку столбца F, кроме тех, у которых в столбце А стоит стоит "Ветка". Для нормальной обработки последнего интервала НИЖЕ всех данных в столбце А должно стоять "Ветка"!" (с)Полосатый жираф Алик

После того, как поколдовал легонько (расширил диапазон поиска) вышло вот что:

Автор - origatosan
Дата добавления - 15.01.2016 в 14:37
_Boroda_ Дата: Пятница, 15.01.2016, 15:31 | Сообщение № 15
Группа: Модераторы
Ранг: Экселист
Сообщений: 10334
Репутация: 4357 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Госпидя! и зачем весь этот геморрой? Все проще. И без массивного ввода
Код
=СУММПРОИЗВ(ИНДЕКС(D$1:D13;ПРОСМОТР(;-1/(A$1:A13="ВЕТКА");СТРОКА(A$1:A13))):ИНДЕКС(D13:D999;ПОИСКПОЗ("ВЕТКА";A13:A999;)-1))
К сообщению приложен файл: 8241293_1.xls(70Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеГоспидя! и зачем весь этот геморрой? Все проще. И без массивного ввода
Код
=СУММПРОИЗВ(ИНДЕКС(D$1:D13;ПРОСМОТР(;-1/(A$1:A13="ВЕТКА");СТРОКА(A$1:A13))):ИНДЕКС(D13:D999;ПОИСКПОЗ("ВЕТКА";A13:A999;)-1))

Автор - _Boroda_
Дата добавления - 15.01.2016 в 15:31
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Найти диапазон относительно ячейки с форм. и суммировать его (Формулы/Formulas)
Страница 1 из 11
Поиск:

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