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

Вход

Регистрация

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

 

= Мир MS Excel/Автоматическое добавление строк с сохранением формул - Мир MS Excel

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

Excel 2013
Доброго всем времени суток!

Прошу помощи!
Как сделать автоматическое добавление строк в таблицу? Так, чтобы сохранялись формулы по строкам и итог по столбцам.
В "примере" заполняются ячейки по столбцам: B,F,G (т.е. при заполнении каждой из них необходимо добавить строку снизу, с сохранением всех формул)
Однако необходимо как-то избежать излишнего добавления строк, например при корректировке данных в ранее заполненных ячейках.

На форуме была такая тема:
http://www.excelworld.ru/forum/2-17111-1
Смог добиться автоматического появления строк.
Однако при добавлении строк, не сохраняются итоговые формулы по столбцам, например в D11 (в диапазон не попадали вновь добавленные строки).
При корректировке данных в ячейках добавлялись лишние строки.

Может можно завязать добавление строк на определенную комбинацию клавиш, или что-то в этом роде?
Заранее благодарен!
К сообщению приложен файл: 2391128.xlsx(13Kb)


Сообщение отредактировал Начинающий - Понедельник, 17.07.2017, 10:06
 
Ответить
СообщениеДоброго всем времени суток!

Прошу помощи!
Как сделать автоматическое добавление строк в таблицу? Так, чтобы сохранялись формулы по строкам и итог по столбцам.
В "примере" заполняются ячейки по столбцам: B,F,G (т.е. при заполнении каждой из них необходимо добавить строку снизу, с сохранением всех формул)
Однако необходимо как-то избежать излишнего добавления строк, например при корректировке данных в ранее заполненных ячейках.

На форуме была такая тема:
http://www.excelworld.ru/forum/2-17111-1
Смог добиться автоматического появления строк.
Однако при добавлении строк, не сохраняются итоговые формулы по столбцам, например в D11 (в диапазон не попадали вновь добавленные строки).
При корректировке данных в ячейках добавлялись лишние строки.

Может можно завязать добавление строк на определенную комбинацию клавиш, или что-то в этом роде?
Заранее благодарен!

Автор - Начинающий
Дата добавления - 17.07.2017 в 10:02
sboy Дата: Понедельник, 17.07.2017, 10:28 | Сообщение № 2
Группа: Проверенные
Ранг: Старожил
Сообщений: 1105
Репутация: 277 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Вариант с кнопкой. вставляет строку ниже активной
К сообщению приложен файл: 2391128.xlsm(19Kb)
 
Ответить
СообщениеДобрый день.
Вариант с кнопкой. вставляет строку ниже активной

Автор - sboy
Дата добавления - 17.07.2017 в 10:28
_Boroda_ Дата: Понедельник, 17.07.2017, 10:36 | Сообщение № 3
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11659
Репутация: 4831 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Если я правильно понял, то еще вариант. Без кнопки
К сообщению приложен файл: 2391128_1.xlsm(22Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕсли я правильно понял, то еще вариант. Без кнопки

Автор - _Boroda_
Дата добавления - 17.07.2017 в 10:36
Начинающий Дата: Понедельник, 17.07.2017, 11:11 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
sboy,
Спасибо, очень интересный вариант!
Остались вопросы:
1. Итоговые формулы по столбцам не изменяются (не охватывают вновь добавленные строки);
2. Автоматически копируются значения в ячейках (с Q и далее)
3. Как переместить кнопку?)

_Boroda_,
Спасибо! Все работает! Я ничего не понял(
Вопрос:
1. При необходимости добавить столбцы в таблицу (в середину или в конец), какую строку кода скопировать?
2. Если есть необходимость ниже таблицы разместить другой расчет, просто данные, это не повлияет на правильность расчетов в таблице?
И, можно как-то объединить идею с кнопкой с вашим расчетом?
К сообщению приложен файл: 2391128_2.xlsm(23Kb)
 
Ответить
Сообщениеsboy,
Спасибо, очень интересный вариант!
Остались вопросы:
1. Итоговые формулы по столбцам не изменяются (не охватывают вновь добавленные строки);
2. Автоматически копируются значения в ячейках (с Q и далее)
3. Как переместить кнопку?)

_Boroda_,
Спасибо! Все работает! Я ничего не понял(
Вопрос:
1. При необходимости добавить столбцы в таблицу (в середину или в конец), какую строку кода скопировать?
2. Если есть необходимость ниже таблицы разместить другой расчет, просто данные, это не повлияет на правильность расчетов в таблице?
И, можно как-то объединить идею с кнопкой с вашим расчетом?

Автор - Начинающий
Дата добавления - 17.07.2017 в 11:11
_Boroda_ Дата: Понедельник, 17.07.2017, 11:14 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11659
Репутация: 4831 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. Никакую. Все само посчитается, главное, чтобы во второй в третьей, это я перепутал строке шапка заполнена была
2. Если Вы не будете ничем заполнять столбец А в таблице ниже, то не повлияет
Объединить с кнопкой можно с несколькими видами результата. Что Вам нужно, чтобы по нажатию кнопки происходило? И нужно ли оставить автоматическое добавление?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. Никакую. Все само посчитается, главное, чтобы во второй в третьей, это я перепутал строке шапка заполнена была
2. Если Вы не будете ничем заполнять столбец А в таблице ниже, то не повлияет
Объединить с кнопкой можно с несколькими видами результата. Что Вам нужно, чтобы по нажатию кнопки происходило? И нужно ли оставить автоматическое добавление?

Автор - _Boroda_
Дата добавления - 17.07.2017 в 11:14
sboy Дата: Понедельник, 17.07.2017, 11:21 | Сообщение № 6
Группа: Проверенные
Ранг: Старожил
Сообщений: 1105
Репутация: 277 ±
Замечаний: 0% ±

Excel 2010
Цитата Начинающий, 17.07.2017 в 11:11, в сообщении № 4 ()
Итоговые формулы по столбцам не изменяются

Вы не правы, диапазон суммирования изменяется
Цитата Начинающий, 17.07.2017 в 11:11, в сообщении № 4 ()
(с Q и далее)

поправил
Цитата Начинающий, 17.07.2017 в 11:11, в сообщении № 4 ()
Как переместить кнопку?)

ПКМ на кнопке, потом можно перемещать
К сообщению приложен файл: 8515092.xlsm(19Kb)
 
Ответить
Сообщение
Цитата Начинающий, 17.07.2017 в 11:11, в сообщении № 4 ()
Итоговые формулы по столбцам не изменяются

Вы не правы, диапазон суммирования изменяется
Цитата Начинающий, 17.07.2017 в 11:11, в сообщении № 4 ()
(с Q и далее)

поправил
Цитата Начинающий, 17.07.2017 в 11:11, в сообщении № 4 ()
Как переместить кнопку?)

ПКМ на кнопке, потом можно перемещать

Автор - sboy
Дата добавления - 17.07.2017 в 11:21
Начинающий Дата: Понедельник, 17.07.2017, 11:34 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_,
Т.е. я правильно понял, что данный код можно использовать в любых таблицах, где:
1. Во 2 строке - шапка.
2. В столбце А после таблицы данных нет?

Мне бы хотелось, чтобы была кнопка, после таблицы (а лучше перемещаемая- простите за наглость), при нажатии на которую после строки (на которой стоит курсор) добавлялась новая строка, с сохранением всех формул (как у вас).
Вне зависимости, последняя это строка или строка в середине таблицы.

И еще один небольшой вопрос: при удалении лишней строки в середине таблицы, нумерация по столбцу А слетает( как решить эту проблемку?


Сообщение отредактировал Начинающий - Понедельник, 17.07.2017, 11:40
 
Ответить
Сообщение_Boroda_,
Т.е. я правильно понял, что данный код можно использовать в любых таблицах, где:
1. Во 2 строке - шапка.
2. В столбце А после таблицы данных нет?

Мне бы хотелось, чтобы была кнопка, после таблицы (а лучше перемещаемая- простите за наглость), при нажатии на которую после строки (на которой стоит курсор) добавлялась новая строка, с сохранением всех формул (как у вас).
Вне зависимости, последняя это строка или строка в середине таблицы.

И еще один небольшой вопрос: при удалении лишней строки в середине таблицы, нумерация по столбцу А слетает( как решить эту проблемку?

Автор - Начинающий
Дата добавления - 17.07.2017 в 11:34
_Boroda_ Дата: Понедельник, 17.07.2017, 11:46 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11659
Репутация: 4831 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. Да. только в третьей, это я перепутал. Но этот параметр задается в макросе
2. Да

Для кнопки код попроще будет
[vba]
Код
Sub VstStr()
    rs_ = Selection(1).Row
    r0_ = 4 ' номер первой строки
    If rs_ < r0_ Then Exit Sub
    r1_ = Range("A" & Rows.Count).End(3).Row
    If rs_ > r1_ Then Exit Sub
    c11_ = Cells(r0_ - 1, Columns.Count).End(1).Column
    Application.ScreenUpdating = 0
    Application.Calculation = xlCalculationManual
    Range("A" & rs_ + 1).EntireRow.Insert
    Range("A" & rs_).Resize(, c11_).Copy Range("A" & rs_ + 1)
    Range("A" & rs_ + 1).Resize(, c11_).SpecialCells(xlCellTypeConstants).ClearContents
    Range("B" & rs_ + 1).Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = 1
End Sub
[/vba]
Сделал закрепление первых 3-х строк, поэтому кнопку можно не двигать вниз - она и так всегда видна будет
А нумерацию - вообще-то, это уже другой вопрос, который нужно задавать в отдельной теме. Правда, в моем файле все равно это уже сделано, иначе неверно проходила бы нумерация после вставки
Там формула
Код
=СУММ(ИНДЕКС(A:A;СТРОКА()-1);1)
К сообщению приложен файл: 7970807.xlsm(24Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. Да. только в третьей, это я перепутал. Но этот параметр задается в макросе
2. Да

Для кнопки код попроще будет
[vba]
Код
Sub VstStr()
    rs_ = Selection(1).Row
    r0_ = 4 ' номер первой строки
    If rs_ < r0_ Then Exit Sub
    r1_ = Range("A" & Rows.Count).End(3).Row
    If rs_ > r1_ Then Exit Sub
    c11_ = Cells(r0_ - 1, Columns.Count).End(1).Column
    Application.ScreenUpdating = 0
    Application.Calculation = xlCalculationManual
    Range("A" & rs_ + 1).EntireRow.Insert
    Range("A" & rs_).Resize(, c11_).Copy Range("A" & rs_ + 1)
    Range("A" & rs_ + 1).Resize(, c11_).SpecialCells(xlCellTypeConstants).ClearContents
    Range("B" & rs_ + 1).Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = 1
End Sub
[/vba]
Сделал закрепление первых 3-х строк, поэтому кнопку можно не двигать вниз - она и так всегда видна будет
А нумерацию - вообще-то, это уже другой вопрос, который нужно задавать в отдельной теме. Правда, в моем файле все равно это уже сделано, иначе неверно проходила бы нумерация после вставки
Там формула
Код
=СУММ(ИНДЕКС(A:A;СТРОКА()-1);1)

Автор - _Boroda_
Дата добавления - 17.07.2017 в 11:46
Начинающий Дата: Понедельник, 17.07.2017, 11:48 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
sboy,

Вы не правы, диапазон суммирования изменяется


Действительно, при заполнении ячеек по D, диапазон меняется.
При заполнении ячеек по Q, U, Y, AC и т.д. диапазон меняется, правда появляется - Несогласующаяся формула.

Не меняется диапазон по нижней строке, столбцам I-P, R, S, T и т.д.
Зависимость я не понял(

Про кнопку спасибо!
 
Ответить
Сообщениеsboy,

Вы не правы, диапазон суммирования изменяется


Действительно, при заполнении ячеек по D, диапазон меняется.
При заполнении ячеек по Q, U, Y, AC и т.д. диапазон меняется, правда появляется - Несогласующаяся формула.

Не меняется диапазон по нижней строке, столбцам I-P, R, S, T и т.д.
Зависимость я не понял(

Про кнопку спасибо!

Автор - Начинающий
Дата добавления - 17.07.2017 в 11:48
AndreTM Дата: Понедельник, 17.07.2017, 14:07 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1762
Репутация: 496 ±
Замечаний: 0% ±

2003 & 2010
Если код кнопки немного поменять - то можно будет и столбец A заполнять ниже первой таблицы. Правда, условия все же остаются - у таблицы должны быть заполнены все заголовки, конец таблицы будет определяться первой "пустой" строкой после неё.

[vba]
Код
Sub VstStr()
    rs_ = Selection(1).Row
    r0_ = 4 ' номер первой строки
    If rs_ < r0_ Then Exit Sub
    set r_ = Cells (r0_, 1).CurrentRegion
    r1_ = r_.Cells(1, 1).Offset(r_.Rows.Count - 1).Row
    ' r1_ = Range("A" & Rows.Count).End(3).Row
    If rs_ > r1_ Then Exit Sub
    cl1_ = r_.Cells(1, 1).Offset(, r_.Columns.Count - 1).Column
    ' c11_ = Cells(r0_ - 1, Columns.Count).End(1).Column
    Application.ScreenUpdating = 0
    Application.Calculation = xlCalculationManual
    Range("A" & rs_ + 1).EntireRow.Insert
    Range("A" & rs_).Resize(, c11_).Copy Range("A" & rs_ + 1)
    Range("A" & rs_ + 1).Resize(, c11_).SpecialCells(xlCellTypeConstants).ClearContents
    Range("B" & rs_ + 1).Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = 1
End Sub
[/vba]


Skype: andre.tm.007
Donate: Qiwi: 9517375010


Сообщение отредактировал AndreTM - Понедельник, 17.07.2017, 14:12
 
Ответить
СообщениеЕсли код кнопки немного поменять - то можно будет и столбец A заполнять ниже первой таблицы. Правда, условия все же остаются - у таблицы должны быть заполнены все заголовки, конец таблицы будет определяться первой "пустой" строкой после неё.

[vba]
Код
Sub VstStr()
    rs_ = Selection(1).Row
    r0_ = 4 ' номер первой строки
    If rs_ < r0_ Then Exit Sub
    set r_ = Cells (r0_, 1).CurrentRegion
    r1_ = r_.Cells(1, 1).Offset(r_.Rows.Count - 1).Row
    ' r1_ = Range("A" & Rows.Count).End(3).Row
    If rs_ > r1_ Then Exit Sub
    cl1_ = r_.Cells(1, 1).Offset(, r_.Columns.Count - 1).Column
    ' c11_ = Cells(r0_ - 1, Columns.Count).End(1).Column
    Application.ScreenUpdating = 0
    Application.Calculation = xlCalculationManual
    Range("A" & rs_ + 1).EntireRow.Insert
    Range("A" & rs_).Resize(, c11_).Copy Range("A" & rs_ + 1)
    Range("A" & rs_ + 1).Resize(, c11_).SpecialCells(xlCellTypeConstants).ClearContents
    Range("B" & rs_ + 1).Select
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = 1
End Sub
[/vba]

Автор - AndreTM
Дата добавления - 17.07.2017 в 14:07
Начинающий Дата: Понедельник, 17.07.2017, 15:18 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Спасибо огромное, уважаемые sboy, _Boroda_, AndreTM!!!

Вроде бы добился чего хотел!

Еще раз огромное спасибо!)


Сообщение отредактировал Начинающий - Понедельник, 17.07.2017, 17:31
 
Ответить
СообщениеСпасибо огромное, уважаемые sboy, _Boroda_, AndreTM!!!

Вроде бы добился чего хотел!

Еще раз огромное спасибо!)

Автор - Начинающий
Дата добавления - 17.07.2017 в 15:18
sboy Дата: Понедельник, 17.07.2017, 16:18 | Сообщение № 12
Группа: Проверенные
Ранг: Старожил
Сообщений: 1105
Репутация: 277 ±
Замечаний: 0% ±

Excel 2010
Начинающий, Вы можете нарисовать любую кнопку, а потом на нее назначить макрос
 
Ответить
СообщениеНачинающий, Вы можете нарисовать любую кнопку, а потом на нее назначить макрос

Автор - sboy
Дата добавления - 17.07.2017 в 16:18
_Boroda_ Дата: Понедельник, 17.07.2017, 16:30 | Сообщение № 13
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11659
Репутация: 4831 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
вообще-то, это уже другой вопрос, который нужно задавать в отдельной теме


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
вообще-то, это уже другой вопрос, который нужно задавать в отдельной теме

Автор - _Boroda_
Дата добавления - 17.07.2017 в 16:30
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматическое добавление строк с сохранением формул (Формулы/Formulas)
Страница 1 из 11
Поиск:

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