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

Вход

Регистрация

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

 

= Мир MS Excel/Подсчет суммы в столбце в определенном диапазоне строк. - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 212»
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет суммы в столбце в определенном диапазоне строк. (Формулы/Formulas)
Подсчет суммы в столбце в определенном диапазоне строк.
JannMichel Дата: Пятница, 09.09.2016, 20:39 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

2013
Добрый день, уважаемые знатоки. Прошу помощи.
Жена работает учителем. И помимо разных школьных отчетов, в начале каждого учебного года необходимо составлять учебный план на весь год.
Состоит он:
1. Раздел:
 Тема:
o Урок
o Урок
o Урок …n
 Тема:
o Урок
 Тема:
o Урок
o Урок
 Тема …n
o Урок …n

2. Радел: …n

И дальше все то же самое. Количество ТЕМ в РАЗДЕЛАХ может быть любым. Количество УРОКОВ в ТЕМАХ может быть любым. Количество разделов тоже нормируется школьной программой.
В чем заключается задача….
В строке УРОК в следующем столбце вручную проставляется количество часов по данному уроку. Необходимо, что бы excel посчитал количество часов по УРОКАМ, которые входят в данную ТЕМУ. Т.е. другими словами, сколько бы уроков не было в первой (любой) теме, суммарное значение надо отобразить в строке ТЕМА. Далее суммарное значение часов всех ТЕМ, входящих в данный РАЗДЕЛ, отобразить в строке РАЗДЕЛ. И так по всем разделам.

Живой пример из школьной программы прилагаю.
К сообщению приложен файл: _1_.xlsx(34Kb)
 
Ответить
СообщениеДобрый день, уважаемые знатоки. Прошу помощи.
Жена работает учителем. И помимо разных школьных отчетов, в начале каждого учебного года необходимо составлять учебный план на весь год.
Состоит он:
1. Раздел:
 Тема:
o Урок
o Урок
o Урок …n
 Тема:
o Урок
 Тема:
o Урок
o Урок
 Тема …n
o Урок …n

2. Радел: …n

И дальше все то же самое. Количество ТЕМ в РАЗДЕЛАХ может быть любым. Количество УРОКОВ в ТЕМАХ может быть любым. Количество разделов тоже нормируется школьной программой.
В чем заключается задача….
В строке УРОК в следующем столбце вручную проставляется количество часов по данному уроку. Необходимо, что бы excel посчитал количество часов по УРОКАМ, которые входят в данную ТЕМУ. Т.е. другими словами, сколько бы уроков не было в первой (любой) теме, суммарное значение надо отобразить в строке ТЕМА. Далее суммарное значение часов всех ТЕМ, входящих в данный РАЗДЕЛ, отобразить в строке РАЗДЕЛ. И так по всем разделам.

Живой пример из школьной программы прилагаю.

Автор - JannMichel
Дата добавления - 09.09.2016 в 20:39
buchlotnik Дата: Пятница, 09.09.2016, 22:22 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
навскидку - с костылём в 258 строке
Код
=--ЕЧИСЛО(C6)
Код
=ЕСЛИ(ЕТЕКСТ(B6);СУММ(СМЕЩ(E6;1;;ПОИСКПОЗ("тема";B7:$B$258;0)-1;1));"")
Код
=ЕСЛИ(ЕТЕКСТ(A6);СУММ(СМЕЩ(F6;;;ПОИСКПОЗ("Раздел*";A7:$A$258;0)-1;1));"")
наверняка можно лучше - но не в пятницу вечером B)
К сообщению приложен файл: 9766474.xlsx(42Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Пятница, 09.09.2016, 22:26
 
Ответить
Сообщениенавскидку - с костылём в 258 строке
Код
=--ЕЧИСЛО(C6)
Код
=ЕСЛИ(ЕТЕКСТ(B6);СУММ(СМЕЩ(E6;1;;ПОИСКПОЗ("тема";B7:$B$258;0)-1;1));"")
Код
=ЕСЛИ(ЕТЕКСТ(A6);СУММ(СМЕЩ(F6;;;ПОИСКПОЗ("Раздел*";A7:$A$258;0)-1;1));"")
наверняка можно лучше - но не в пятницу вечером B)

Автор - buchlotnik
Дата добавления - 09.09.2016 в 22:22
skais675 Дата: Пятница, 09.09.2016, 22:26 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 66
Репутация: 2 ±
Замечаний: 40% ±

Excel 2010
Как Вам это решение?
К сообщению приложен файл: skais675.xlsx(75Kb)


Vlad
 
Ответить
СообщениеКак Вам это решение?

Автор - skais675
Дата добавления - 09.09.2016 в 22:26
gling Дата: Суббота, 10.09.2016, 12:44 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1352
Репутация: 326 ±
Замечаний: 0% ±

2010
Здравствуйте. Немного урезал таблицу, удалил столбцы и ... Посмотрите вариант. Ячейки с разными формулами выделены разным цветом. И костыль- в конце столбца А нужно слово "тема". Строку с этим словом можно скрыть или шрифт сделать не видимым. Еще для чистоты полей нули я бы скрыл, форматом или в Параметрах.
К сообщению приложен файл: 1349105.xlsx(46Kb)
 
Ответить
СообщениеЗдравствуйте. Немного урезал таблицу, удалил столбцы и ... Посмотрите вариант. Ячейки с разными формулами выделены разным цветом. И костыль- в конце столбца А нужно слово "тема". Строку с этим словом можно скрыть или шрифт сделать не видимым. Еще для чистоты полей нули я бы скрыл, форматом или в Параметрах.

Автор - gling
Дата добавления - 10.09.2016 в 12:44
JannMichel Дата: Суббота, 10.09.2016, 14:29 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

2013
Добрый день всем!
gling, Цель данной таблицы - распространить ее между учителями школы. К сожалению все они, кроме моей жены не работают в excel. Все делают эти планы в Word'e. Отсюда и сроки большие, и много ошибок, т.к. считают часы вручную. Да и жена моя начала использовать Excel только после моих настойчивых рекомендаций. Поэтому задумка заключается в том, чтобы составить шаблон, в который входит шапка и три строки: РАЗДЕЛ, ТЕМА, УРОК. Вставить формулы и раздать пользователям. При добавлении пользователем новой строки, формулы должны тоже добавляться автоматически. Это я говорю к тому, что люди не знакомые с excel'ем, не поймут в какую строку вставлять нужную формулу.
buchlotnik, Ваш вариант очень подходит. Все работает. При добавлении новых строк, формулы считаю корректно. Даже скопировал формулы в другую таблицу (таких таблиц, составленных вручную много - по одной на каждый предмет). Растянул формулы - все работает.
Вот только жена внесла коррекцию. Я попытался внести изменения в формулы, но ничего не вышло. Формула выдала ошибку.
Корректировка заключается в следующем: Вместо РАЗДЕЛА надо написать БЛОК. Вместо ТЕМА - РАЗДЕЛ. Прикрепляю шаблон, который будет роздан пользователям. Прошу дополнить его функционально, что бы пользователь мог добавлять строки, и формулы при этом растягивались.
skais675, В Вашем решении я не увидел результатов подсчета количества часов. Возможно, я не совсем доступно объяснил цель задачи.
К сообщению приложен файл: 3302797.xlsx(12Kb)
 
Ответить
СообщениеДобрый день всем!
gling, Цель данной таблицы - распространить ее между учителями школы. К сожалению все они, кроме моей жены не работают в excel. Все делают эти планы в Word'e. Отсюда и сроки большие, и много ошибок, т.к. считают часы вручную. Да и жена моя начала использовать Excel только после моих настойчивых рекомендаций. Поэтому задумка заключается в том, чтобы составить шаблон, в который входит шапка и три строки: РАЗДЕЛ, ТЕМА, УРОК. Вставить формулы и раздать пользователям. При добавлении пользователем новой строки, формулы должны тоже добавляться автоматически. Это я говорю к тому, что люди не знакомые с excel'ем, не поймут в какую строку вставлять нужную формулу.
buchlotnik, Ваш вариант очень подходит. Все работает. При добавлении новых строк, формулы считаю корректно. Даже скопировал формулы в другую таблицу (таких таблиц, составленных вручную много - по одной на каждый предмет). Растянул формулы - все работает.
Вот только жена внесла коррекцию. Я попытался внести изменения в формулы, но ничего не вышло. Формула выдала ошибку.
Корректировка заключается в следующем: Вместо РАЗДЕЛА надо написать БЛОК. Вместо ТЕМА - РАЗДЕЛ. Прикрепляю шаблон, который будет роздан пользователям. Прошу дополнить его функционально, что бы пользователь мог добавлять строки, и формулы при этом растягивались.
skais675, В Вашем решении я не увидел результатов подсчета количества часов. Возможно, я не совсем доступно объяснил цель задачи.

Автор - JannMichel
Дата добавления - 10.09.2016 в 14:29
buchlotnik Дата: Суббота, 10.09.2016, 14:38 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
JannMichel, вы просто опечатались - "радел" вместо "раздел", а так всё норм поправили yes
К сообщению приложен файл: 5842489.xlsx(13Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
СообщениеJannMichel, вы просто опечатались - "радел" вместо "раздел", а так всё норм поправили yes

Автор - buchlotnik
Дата добавления - 10.09.2016 в 14:38
Nic70y Дата: Суббота, 10.09.2016, 14:46 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
вариант:
Код
=ЕСЛИ(B4="";"";СУММ(E4:ИНДЕКС(E$4:E$10;ПОИСКПОЗ("*";B5:B$10;)+СТРОКА(A1)-1)))
К сообщению приложен файл: 18-5.xlsx(12Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениевариант:
Код
=ЕСЛИ(B4="";"";СУММ(E4:ИНДЕКС(E$4:E$10;ПОИСКПОЗ("*";B5:B$10;)+СТРОКА(A1)-1)))

Автор - Nic70y
Дата добавления - 10.09.2016 в 14:46
skais675 Дата: Суббота, 10.09.2016, 14:54 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 66
Репутация: 2 ±
Замечаний: 40% ±

Excel 2010
JannMichel, Да, получилось так, что я Вас не понял. Мне казалось Вам нужен отчет с 260 строки в Вашем первоначальном файле.
Цитата
Т.е. другими словами, сколько бы уроков не было в первой (любой) теме, суммарное значение надо отобразить в строке ТЕМА. Далее суммарное значение часов всех ТЕМ, входящих в данный РАЗДЕЛ, отобразить в строке РАЗДЕЛ. И так по всем разделам.

Вроде в сводной все отобразил - количество часов в теме и в разделе и всего. Изначально было бы неплохо сказать для чего это все и какова конечная цель.


Vlad
 
Ответить
СообщениеJannMichel, Да, получилось так, что я Вас не понял. Мне казалось Вам нужен отчет с 260 строки в Вашем первоначальном файле.
Цитата
Т.е. другими словами, сколько бы уроков не было в первой (любой) теме, суммарное значение надо отобразить в строке ТЕМА. Далее суммарное значение часов всех ТЕМ, входящих в данный РАЗДЕЛ, отобразить в строке РАЗДЕЛ. И так по всем разделам.

Вроде в сводной все отобразил - количество часов в теме и в разделе и всего. Изначально было бы неплохо сказать для чего это все и какова конечная цель.

Автор - skais675
Дата добавления - 10.09.2016 в 14:54
JannMichel Дата: Суббота, 10.09.2016, 14:59 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

2013
buchlotnik, Спасибо.
Попробовал добавить строки, и выяснилось, что формулы надо растягивать вручную. Причем не из любой ячейки в столбце, а именно с той, в которой уже есть результат подсчета (конкретно F7 и G4). И в строке ИТОГО сумму тоже надо корректировать руками. Для меня это не проблема, но боюсь что будет БОЛЬШОЙ проблемой для пользователей. И каждый будет звонить и говорить, что таблица не работает. Можно ли это поправить программно?
К сообщению приложен файл: 0198974.xlsx(13Kb)
 
Ответить
Сообщениеbuchlotnik, Спасибо.
Попробовал добавить строки, и выяснилось, что формулы надо растягивать вручную. Причем не из любой ячейки в столбце, а именно с той, в которой уже есть результат подсчета (конкретно F7 и G4). И в строке ИТОГО сумму тоже надо корректировать руками. Для меня это не проблема, но боюсь что будет БОЛЬШОЙ проблемой для пользователей. И каждый будет звонить и говорить, что таблица не работает. Можно ли это поправить программно?

Автор - JannMichel
Дата добавления - 10.09.2016 в 14:59
buchlotnik Дата: Суббота, 10.09.2016, 15:01 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
[offtop]
Цитата
Изначально было бы неплохо сказать
см. первый пост ;)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщение[offtop]
Цитата
Изначально было бы неплохо сказать
см. первый пост ;)

Автор - buchlotnik
Дата добавления - 10.09.2016 в 15:01
buchlotnik Дата: Суббота, 10.09.2016, 15:12 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
JannMichel, можно. Но это уже другая тема. В смысле создайте тему "Как программно протянуть формулы при добавлении строк" или типа того. У меня ближайшее время просто руки не дойдут.


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
СообщениеJannMichel, можно. Но это уже другая тема. В смысле создайте тему "Как программно протянуть формулы при добавлении строк" или типа того. У меня ближайшее время просто руки не дойдут.

Автор - buchlotnik
Дата добавления - 10.09.2016 в 15:12
skais675 Дата: Суббота, 10.09.2016, 15:13 | Сообщение № 12
Группа: Пользователи
Ранг: Участник
Сообщений: 66
Репутация: 2 ±
Замечаний: 40% ±

Excel 2010
buchlotnik, Я имел ввиду - учителя потом сдают типа табель или что, что требует от них руководство. А здесь я больше увидел промежуточную цель. Зачастую пользователь пишет о том как он видит. А на мой взляд лучше изъяснить то, что мы хотим или хотят видеть на выходе. Просто хотел помочь, не думаю что на этом JannMichel, остановится. Я бы и первоначальную форму переделал, все зависит от конечной цели.


Vlad
 
Ответить
Сообщениеbuchlotnik, Я имел ввиду - учителя потом сдают типа табель или что, что требует от них руководство. А здесь я больше увидел промежуточную цель. Зачастую пользователь пишет о том как он видит. А на мой взляд лучше изъяснить то, что мы хотим или хотят видеть на выходе. Просто хотел помочь, не думаю что на этом JannMichel, остановится. Я бы и первоначальную форму переделал, все зависит от конечной цели.

Автор - skais675
Дата добавления - 10.09.2016 в 15:13
buchlotnik Дата: Суббота, 10.09.2016, 15:18 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
Я бы и первоначальную форму переделал
я бы тоже, но у самого мама учитель - как спустили, так и будет, бубен в помощь B)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщение
Цитата
Я бы и первоначальную форму переделал
я бы тоже, но у самого мама учитель - как спустили, так и будет, бубен в помощь B)

Автор - buchlotnik
Дата добавления - 10.09.2016 в 15:18
JannMichel Дата: Суббота, 10.09.2016, 15:20 | Сообщение № 14
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

2013
buchlotnik, Благодарю за помощь. Очень сильно облегчили задачу. Еще раз спасибо.
Тему создам.
 
Ответить
Сообщениеbuchlotnik, Благодарю за помощь. Очень сильно облегчили задачу. Еще раз спасибо.
Тему создам.

Автор - JannMichel
Дата добавления - 10.09.2016 в 15:20
JannMichel Дата: Суббота, 10.09.2016, 15:29 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

2013
skais675, Форму таблицы "спустили сверху", высокое начальство. Она должна быть единая для всех. И менять её нельзя. Но, к сожалению, это начальство не обременило себя трудом разработать автоматизированную систему отчетов. Столбцы РАЗДЕЛ и ТЕМА я уже сам вставлял и цвета им придал. При сдаче отчета эти столбцы надо будет скрывать. Занялся этим после того, как у жены не шли цифры, и она попросила меня помочь ей. А там все в одном цвете и без разделений на разделы и темы - черт ногу сломит. Вот я и задался вопросом о систематизации данных.
 
Ответить
Сообщениеskais675, Форму таблицы "спустили сверху", высокое начальство. Она должна быть единая для всех. И менять её нельзя. Но, к сожалению, это начальство не обременило себя трудом разработать автоматизированную систему отчетов. Столбцы РАЗДЕЛ и ТЕМА я уже сам вставлял и цвета им придал. При сдаче отчета эти столбцы надо будет скрывать. Занялся этим после того, как у жены не шли цифры, и она попросила меня помочь ей. А там все в одном цвете и без разделений на разделы и темы - черт ногу сломит. Вот я и задался вопросом о систематизации данных.

Автор - JannMichel
Дата добавления - 10.09.2016 в 15:29
buchlotnik Дата: Суббота, 10.09.2016, 15:36 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
При сдаче отчета эти столбцы надо будет скрывать.
проще кнопку "выкинуть в Word" примотать, ИМХО. Но это так, на будущее развитие идеи B)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщение
Цитата
При сдаче отчета эти столбцы надо будет скрывать.
проще кнопку "выкинуть в Word" примотать, ИМХО. Но это так, на будущее развитие идеи B)

Автор - buchlotnik
Дата добавления - 10.09.2016 в 15:36
Nic70y Дата: Суббота, 10.09.2016, 15:48 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3493
Репутация: 724 ±
Замечаний: 0% ±

Excel 2013
А если в эту форму запихать умную таблицу?
К сообщению приложен файл: 8474450.xlsx(13Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
СообщениеА если в эту форму запихать умную таблицу?

Автор - Nic70y
Дата добавления - 10.09.2016 в 15:48
skais675 Дата: Суббота, 10.09.2016, 16:00 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 66
Репутация: 2 ±
Замечаний: 40% ±

Excel 2010
JannMichel, Лучше вышлите форму, которую Вы уже сдали начальству. Если не сдавали, тогда ту, которую дало Вам начальство изначально - тогда прояснится.


Vlad
 
Ответить
СообщениеJannMichel, Лучше вышлите форму, которую Вы уже сдали начальству. Если не сдавали, тогда ту, которую дало Вам начальство изначально - тогда прояснится.

Автор - skais675
Дата добавления - 10.09.2016 в 16:00
gling Дата: Суббота, 10.09.2016, 17:11 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 1352
Репутация: 326 ±
Замечаний: 0% ±

2010
Ответ перенес в соседнею тему.


Сообщение отредактировал gling - Суббота, 10.09.2016, 17:17
 
Ответить
СообщениеОтвет перенес в соседнею тему.

Автор - gling
Дата добавления - 10.09.2016 в 17:11
JannMichel Дата: Суббота, 10.09.2016, 19:01 | Сообщение № 20
Группа: Пользователи
Ранг: Участник
Сообщений: 72
Репутация: 0 ±
Замечаний: 0% ±

2013
Nic70y, в предложенном Вами варианте данные в добавленных строках к сожалению тоже не суммируются автоматически. Приходится растягивать вручную
 
Ответить
СообщениеNic70y, в предложенном Вами варианте данные в добавленных строках к сожалению тоже не суммируются автоматически. Приходится растягивать вручную

Автор - JannMichel
Дата добавления - 10.09.2016 в 19:01
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Подсчет суммы в столбце в определенном диапазоне строк. (Формулы/Formulas)
Страница 1 из 212»
Поиск:

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