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

Вход

Регистрация

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

 

= Мир MS Excel/Как программно протянуть формулы при добавлении строк - Мир MS Excel

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

2013
Приветствую гуру и интересующихся этой темой.
Эта тема - продолжение темы "Подсчет суммы в столбце в определенном диапазоне строк.
Очень помог в этом вопросе buchlotnik, Прилагаю таблицу с ЕГО решением вопроса из выше указанной темы. Дальнейшая проработка таблицы заключается в следующем:
Необходимо, что бы при добавлении пользователем строк (после 9-той) автоматически растягивались формулы в столбцах F и G. Это корректно работает при условии, если растягивать формулы вручную. Причем не из любой ячейки в столбце, а именно с той, в которой уже есть результат подсчета (конкретно F7 и G4). И в строке ИТОГО сумму тоже надо корректировать руками. Для меня это не проблема, но боюсь что будет БОЛЬШОЙ проблемой для пользователей. И каждый будет звонить и говорить, что таблица не работает. Можно ли это поправить программно?

Предыстория: Данная таблица для учителей школы, которые составляют поурочное расписание на год. Цель данной таблицы - распространить ее между учителями школы. К сожалению все они, кроме моей жены не работают в excel. Все делают эти планы в Word'e. Отсюда и сроки большие, и много ошибок, т.к. считают часы вручную. Да и жена моя начала использовать Excel только после моих настойчивых рекомендаций. Поэтому задумка заключается в том, чтобы составить шаблон, в который входит шапка и три строки: БЛОК, РАЗДЕЛ, ТЕМА (строки 1-9 и 15) Вставить формулы и раздать пользователям. При добавлении пользователем новой строки (между строками 9 и 15), формулы должны тоже добавляться автоматически.
К сообщению приложен файл: 0053934.xlsx(13Kb)


Сообщение отредактировал JannMichel - Суббота, 10.09.2016, 20:08
 
Ответить
СообщениеПриветствую гуру и интересующихся этой темой.
Эта тема - продолжение темы "Подсчет суммы в столбце в определенном диапазоне строк.
Очень помог в этом вопросе buchlotnik, Прилагаю таблицу с ЕГО решением вопроса из выше указанной темы. Дальнейшая проработка таблицы заключается в следующем:
Необходимо, что бы при добавлении пользователем строк (после 9-той) автоматически растягивались формулы в столбцах F и G. Это корректно работает при условии, если растягивать формулы вручную. Причем не из любой ячейки в столбце, а именно с той, в которой уже есть результат подсчета (конкретно F7 и G4). И в строке ИТОГО сумму тоже надо корректировать руками. Для меня это не проблема, но боюсь что будет БОЛЬШОЙ проблемой для пользователей. И каждый будет звонить и говорить, что таблица не работает. Можно ли это поправить программно?

Предыстория: Данная таблица для учителей школы, которые составляют поурочное расписание на год. Цель данной таблицы - распространить ее между учителями школы. К сожалению все они, кроме моей жены не работают в excel. Все делают эти планы в Word'e. Отсюда и сроки большие, и много ошибок, т.к. считают часы вручную. Да и жена моя начала использовать Excel только после моих настойчивых рекомендаций. Поэтому задумка заключается в том, чтобы составить шаблон, в который входит шапка и три строки: БЛОК, РАЗДЕЛ, ТЕМА (строки 1-9 и 15) Вставить формулы и раздать пользователям. При добавлении пользователем новой строки (между строками 9 и 15), формулы должны тоже добавляться автоматически.

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

2010
Вариант "Умной таблицей", может сгодится в качестве примера. При вставке строк или растягивании за нижний угол формулы прописываются автоматически.
К сообщению приложен файл: 3547040.xlsx(14Kb)
 
Ответить
СообщениеВариант "Умной таблицей", может сгодится в качестве примера. При вставке строк или растягивании за нижний угол формулы прописываются автоматически.

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

2013
gling, да, стало очень удобно. Формулы растягиваются. Итоговая сумма тоже автоматически захватывает добавленные строки.
Добавил я несколько строк, и с определенного момента стал происходить сбой. Со строки 14 формула выдает не правильную сумму, а ниже Н/Д
К сообщению приложен файл: 6097611.xlsx(16Kb)
 
Ответить
Сообщениеgling, да, стало очень удобно. Формулы растягиваются. Итоговая сумма тоже автоматически захватывает добавленные строки.
Добавил я несколько строк, и с определенного момента стал происходить сбой. Со строки 14 формула выдает не правильную сумму, а ниже Н/Д

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

2010
Не придумал как привязаться к последней строке таблиц поэтому написал в формуле с запасом до 1000 строк.
К сообщению приложен файл: 0465587.xlsx(14Kb)
 
Ответить
СообщениеНе придумал как привязаться к последней строке таблиц поэтому написал в формуле с запасом до 1000 строк.

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

2013
gling, таблица все верно считает... до определенного момента. Сначала подумал, что я как то не правильно вводил данные или сбил где-то формулу. Затем снова скачал файл и опять стал добавлять строки. И с ячейки F31 подсчет сбивается.
К сообщению приложен файл: 8428392.xlsx(16Kb)
 
Ответить
Сообщениеgling, таблица все верно считает... до определенного момента. Сначала подумал, что я как то не правильно вводил данные или сбил где-то формулу. Затем снова скачал файл и опять стал добавлять строки. И с ячейки F31 подсчет сбивается.

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

Excel 2013
в той теме уже не стал отвечать, т.к. ответ более подходит к этой.
[vba]
Код
Sub Plus_Row()
    Rows([D1048576].End(xlUp).Row).Insert Shift:=xlDown
End Sub
[/vba]
файл перезалил - неправильную формулу написал в E10, исправил
(клятый копипаст)

еще раз заменил файл, выкинул СТРОКА(ссылка), т.к. для умной таблицы это не приемлимо
К сообщению приложен файл: 6526405.xlsm(20Kb)


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


Сообщение отредактировал Nic70y - Суббота, 10.09.2016, 20:34
 
Ответить
Сообщениев той теме уже не стал отвечать, т.к. ответ более подходит к этой.
[vba]
Код
Sub Plus_Row()
    Rows([D1048576].End(xlUp).Row).Insert Shift:=xlDown
End Sub
[/vba]
файл перезалил - неправильную формулу написал в E10, исправил
(клятый копипаст)

еще раз заменил файл, выкинул СТРОКА(ссылка), т.к. для умной таблицы это не приемлимо

Автор - Nic70y
Дата добавления - 10.09.2016 в 20:14
gling Дата: Суббота, 10.09.2016, 20:31 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1352
Репутация: 326 ±
Замечаний: 0% ±

2010
В файле по не известной мне причине формулы идут не по порядку, наверно удаляли строки, но в столбцах выскакивает ярлычек, нажав на который можно в столбце проставить формулы. Установите курсор в строку формул и нажмите Enter, появится ярлычек.
 
Ответить
СообщениеВ файле по не известной мне причине формулы идут не по порядку, наверно удаляли строки, но в столбцах выскакивает ярлычек, нажав на который можно в столбце проставить формулы. Установите курсор в строку формул и нажмите Enter, появится ярлычек.

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

Excel 2013
gling, это "болезнь" умных таблиц, в них не рекомендуется (категорически) делать ссылки на конкретные ячейки/диапазоны, тока на саму таблицу
(не всегда конечно)
[p.s.]сам сделал на 4-ю строку в сумме (шоб не удлинять формулу) надеюсь ее не зацепит[/p.s.]


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


Сообщение отредактировал Nic70y - Суббота, 10.09.2016, 20:38
 
Ответить
Сообщениеgling, это "болезнь" умных таблиц, в них не рекомендуется (категорически) делать ссылки на конкретные ячейки/диапазоны, тока на саму таблицу
(не всегда конечно)
[p.s.]сам сделал на 4-ю строку в сумме (шоб не удлинять формулу) надеюсь ее не зацепит[/p.s.]

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

2013
gling, сделал как Вы описали - все вернулось на место. Благодарю за помощь.
Есть еще вопрос по продолжению. Не замучил еще?
 
Ответить
Сообщениеgling, сделал как Вы описали - все вернулось на место. Благодарю за помощь.
Есть еще вопрос по продолжению. Не замучил еще?

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

2013
Nic70y, Очень интересная кнопка. А можно ее встроить в таблицу gling? И если бы еще их сделать две: одну в столбец БЛОК, что бы сразу вставлялось слово БЛОК, Вторую в столбец РАЗДЕЛ с тем же результатом. В случае с РАЗДЕЛОМ должна добавляться еще одна пустая строка для названия урока. Это возможно? Последнюю табличку сделанную gling прикрепил
К сообщению приложен файл: 9966894.xlsx(14Kb)
 
Ответить
СообщениеNic70y, Очень интересная кнопка. А можно ее встроить в таблицу gling? И если бы еще их сделать две: одну в столбец БЛОК, что бы сразу вставлялось слово БЛОК, Вторую в столбец РАЗДЕЛ с тем же результатом. В случае с РАЗДЕЛОМ должна добавляться еще одна пустая строка для названия урока. Это возможно? Последнюю табличку сделанную gling прикрепил

Автор - JannMichel
Дата добавления - 10.09.2016 в 21:04
krosav4ig Дата: Суббота, 10.09.2016, 21:17 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1362
Репутация: 548 ±
Замечаний: 0% ±

Excel 2007, 2013
как вариант, формула [vba]
Код
=ЕСЛИ([@Блоки]="";"";СУММ([@Урок:Урок]:ИНДЕКС([Урок]:[Урок];СТРОКА()+ПОИСКПОЗ([[#Заголовки];[Блок]];ИНДЕКС([Блоки];СТРОКА()-1):[[#Итоги];[Блоки]];)-1-СТРОКА([#Заголовки]))))
[/vba]
К сообщению приложен файл: 8452831.xlsx(16Kb)


(_)Õvõ(_)

Сообщение отредактировал krosav4ig - Суббота, 10.09.2016, 21:18
 
Ответить
Сообщениекак вариант, формула [vba]
Код
=ЕСЛИ([@Блоки]="";"";СУММ([@Урок:Урок]:ИНДЕКС([Урок]:[Урок];СТРОКА()+ПОИСКПОЗ([[#Заголовки];[Блок]];ИНДЕКС([Блоки];СТРОКА()-1):[[#Итоги];[Блоки]];)-1-СТРОКА([#Заголовки]))))
[/vba]

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

Excel 2013
А можно ее встроить в таблицу gling?
можно
К сообщению приложен файл: 9966894-1-.xlsm(23Kb)


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


Сообщение отредактировал Nic70y - Суббота, 10.09.2016, 21:25
 
Ответить
Сообщение
А можно ее встроить в таблицу gling?
можно

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

2013
Nic70y, Красота. Вставляй данные - нехочу! Просто отлично! Благодарю!
 
Ответить
СообщениеNic70y, Красота. Вставляй данные - нехочу! Просто отлично! Благодарю!

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

2010, 2013, 2016 RUS / ENG
Nic70y, Николай, если последовательно вставить блок, раздел и урок - формула для блока уплывает


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
СообщениеNic70y, Николай, если последовательно вставить блок, раздел и урок - формула для блока уплывает

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

Excel 2013
Михаил, ТС не захотел мои формулы.


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
СообщениеМихаил, ТС не захотел мои формулы.

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

2010, 2013, 2016 RUS / ENG
[offtop]Понял B)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщение[offtop]Понял B)

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

2013
krosav4ig, Ваша табличка сбоев не дает, которые описаны в посте 5 и 7. По крайней мере при первой поверке. Если таких ошибок не будет - это просто чудесно, потому как пользователи, ввиду человеческого фактора, не будут контролировать все цифры, которые выдают формулы. Ошибки неизбежны.
Пожелания:
1) Вы поменяли местами столбцы БЛОК и РАЗДЕЛ. Это принципиально? Возможно ли их вернуть на место для простоты восприятия, чтобы они располагались в строке по возрастанию?
2) И можно ли добавить кнопки, как сделал Nic70y (пост 12). Плюс к этому дополнить третью кнопку надписью УРОК. И что бы при её нажатии добавлялся следующий порядковый номер урока в столбце НОМЕР УРОКА/ЗАНЯТИЯ.


Сообщение отредактировал JannMichel - Суббота, 10.09.2016, 22:53
 
Ответить
Сообщениеkrosav4ig, Ваша табличка сбоев не дает, которые описаны в посте 5 и 7. По крайней мере при первой поверке. Если таких ошибок не будет - это просто чудесно, потому как пользователи, ввиду человеческого фактора, не будут контролировать все цифры, которые выдают формулы. Ошибки неизбежны.
Пожелания:
1) Вы поменяли местами столбцы БЛОК и РАЗДЕЛ. Это принципиально? Возможно ли их вернуть на место для простоты восприятия, чтобы они располагались в строке по возрастанию?
2) И можно ли добавить кнопки, как сделал Nic70y (пост 12). Плюс к этому дополнить третью кнопку надписью УРОК. И что бы при её нажатии добавлялся следующий порядковый номер урока в столбце НОМЕР УРОКА/ЗАНЯТИЯ.

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

2010
Еще вариант.
К сообщению приложен файл: 3302797.xlsm(23Kb)
 
Ответить
СообщениеЕще вариант.

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

2013
ТС не захотел мои формулы.

Nic70y, ТС - это создатель топика? Если да, то дело не в том, что я не захотел Ваши формулы. А в том, что я не разобрался в них. Возможно они даже лучше остальных предложенных. Какие приемы используют Ваши формулы в отличии от других формул, я не знаю. Не специалист. Поэтому и обратился к ВАМ за помощью. Я же оцениваю с точки зрения результата. И Ваша формула считает, и формула предложенная gling считает. Разницу, которую я увидел, это более удобное оформление для восприятия.
 
Ответить
Сообщение
ТС не захотел мои формулы.

Nic70y, ТС - это создатель топика? Если да, то дело не в том, что я не захотел Ваши формулы. А в том, что я не разобрался в них. Возможно они даже лучше остальных предложенных. Какие приемы используют Ваши формулы в отличии от других формул, я не знаю. Не специалист. Поэтому и обратился к ВАМ за помощью. Я же оцениваю с точки зрения результата. И Ваша формула считает, и формула предложенная gling считает. Разницу, которую я увидел, это более удобное оформление для восприятия.

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

2013
gling, Да, то что хотел. Спасибо. Только исправлять приходиться вручную после добавления уроков. Формула сбивается. Но
Установите курсор в строку формул и нажмите Enter, появится ярлычек.
спасает. Ну что же. Если нельзя исправить - будем работать так. Это не всю таблицу ручками перебирать. Огромное спасибо.
 
Ответить
Сообщениеgling, Да, то что хотел. Спасибо. Только исправлять приходиться вручную после добавления уроков. Формула сбивается. Но
Установите курсор в строку формул и нажмите Enter, появится ярлычек.
спасает. Ну что же. Если нельзя исправить - будем работать так. Это не всю таблицу ручками перебирать. Огромное спасибо.

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

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