Excel автоматическое добавление строк в таблицу

Автоматическое добавление строк в excel

Здравствуйте,знатоки. Я уверен, для Вас это семечки.
Задача:
1. Две(а точнее одна главная и 12 должны зависеть от нее) одинаковые таблицы на одном листе. Необходимо при добавлении строки в одну таблицу автоматически добавлять строку во вторую таблицу на одном листе. Структура таблиц одинаковая.

Суть таблицы в том, что есть товары в «главной» таблице и они дублируются в остальные, которые идут по месяцам. Разбито для того, чтобы «Главная» таблица собирала данные продаж по месяцам.
Проблема в том, что если в » главную» таблицу добавить новый товар, то все остальные придется добавлять руками. Как это сделать автоматически?
В строке помимо названии товара есть формулы расчёта, как сделать так, чтобы дублировались форму и названия, но не данные продаж?

Прикрепляю пример. Объяснил как смог, сильно камнями не закидывайте. Заранее большое спасибо!

Автоматическое добавление строк в excel
Есть пара задачек: 1. Две одинаковые таблицы на одном листе. Необходимо при добавлении строки в.

Автоматическое добавление заданного количества строк с наполнением
В ступоре, подскажите кто чем может. приложил файл, мне нужно, чтобы при выборе количества в.

Добавление строк в определенные ячейки таблицы Word из Excel
Не получается добавить строки в ячейках таблицы Word. На рис исх. так есть, а нужно рис резулит. .

Как Excel документ, в котором 10000 строк, разбить по 10 строк и сохранить каждые 10 строк в отдельный файл
Здравствуйте. Подскажите как Excel документ в котором 10000 строк разбить по 10 строк и сохранить.

Автоматическое добавление строки в VB эксель
Здравствуйте, помогите пожалуйста не могу решить задачу сам. Задача такая: при появлении в.

Количество таблиц учета становится не определяющим фактором при правильной организации данных.
Посмотрите как ведут реляционные базы данных (структура, связи и зависимости между таблицами), можно двигаться в этом направлении. Тогда отпадет надобность в такой куче столбцов (маржа без скидки, маржа со скидкой, маржа соседей, маржа вчера-сегодня-завтра; варианты возможных выручек), также отпадет привычка вести учет всего движущегося в одном месте (перечень товаров с характеристиками соседствует с расчетом маржинальности, и тут же учет расходов по сотрудникам, в довесок присобачили расходы на оборудование и прочие текущие издержки — «очень удобно»
При таком подходе никакой макрос не справится, каждый день придется его дописывать/переписывать, а особенно если ассортимент товаров будет расти, или поменяются издержки, что-то из этого точно поменяется.

Резюмируя. Отделяйте котлеты от мух, ведите учет по каждому вопросу в своем месте (если надо, то и в 20 таблицах), присмотритесь к структуре реляционных баз данных, а всё что нужно в виде обобщенных данных организуйте сводными таблицами/диаграммами. А при необходимости красивой подачи, даже это можно сделать средствами «простого» Excel

Добавлено через 5 минут

Работа со строкой итогов «умной таблицы» Excel

Очень часто таблицы заканчиваются итоговой результирующей строкой, в которой подбиваются данные по тем или иным столбцам таблицы. При простом вводе данных на лист итоговую строку придётся добавлять вручную, прописывая в ней все формулы. Однако, при работе с «умной таблицей» (далее будем называть ее просто таблицей) строку итогов можно добавить в один клик, а выбор результирующей функции часто не требует ввода формул.

Чтобы добавить строку итогов в таблицу, выберите любую ячейку внутри нее и на появившейся контекстной вкладке » Конструктор » в группе команд » Параметры стилей таблицы » установите галочку напротив » Строка итогов «.

В результате в выбранной таблице появится строка итогов, отделенная от основной таблицы двойной чертой.

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

Доступны следующие варианты:

• Нет — отсутствие итогов, пустая ячейка;
• Среднее — среднее значение по столбцу;
• Количество — количестов непустых ячеек в столбце;
• Количествочисел — количество ячеек с числами в столбце;
• Максимум — максимальное значение в столбце;
• Минимум — минимальное значение в столбце;
• Сумма — сумма по столбцу;
• Стандартноеотклонение — одна из статистически величин, отображающих степень «разброса» значений в столбце;
• Дисперсия — одна из статистически величин, отображающих степень «разброса» значений в столбце;
• Другиефункции — открывает окно «Вставка функции», в котором можно выбрать любую функцию рабочего листа из библиотеки.

Особенности работы со строкой итогов

• В строку итогов можно вводить любые формулы (не обязательно выбирать только варианты из выпадающего списка);
• При выборе варианта из выпадающего списка в ячейку вставляется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ с соответствующим аргументом;
• Если отключить строку итогов, добавить/удалить строки таблицы и снова включить — в строке итогов будут те же формулы, что и до отключения (но соответствующим образом будут изменены ссылки на диапазоны ячеек);
• Чтобы добавить данные в таблицу без отключения строки итогов, нужно вставить новую строку перед ней. Это можно сделать командой «Вставить строку» или нажав Tab, когда активна ячейка последнего столбца последней строки;
• Если сослаться на ячейку строки итогов в формуле, то ссылка примет вид: ИмяТаблицы[[#Итоги];[Название столбца по которому подведен итог]]. Если после этого скрыть строку итогов — такая формула вернет ошибку #ССЫЛКА!

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel — подписывайтесь на наш канал в Telegram Excel Everyday

Много интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) — на нашем канале в Telegram Office Killer

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

Вопросы по другому ПО (кроме Excel) задавайте второму боту — @KillOfBot

Автоматическая нумерация при добавлении строк в MS EXCEL

При добавлении в таблицу новых строк приходится вручную восстанавливать нумерацию строк. Использование таблиц в формате Excel 2007 позволяет автоматизировать этот процесс.

Пусть имеется обычная таблица (диапазон ячеек с данными), состоящая из трех столбцов с заголовками №, Наименование, Цена (в строке 1). Строки таблицы заполнены данными (кроме столбца №, который будет содержать номера строк).

Для организации автоматической нумерации в случае добавления или удаления строк используем следующий алгоритм (см. файл примера ):

• выделим всю таблицу (все 3 столбца вместе с заголовками). Для этого выделите любую ячейку в таблице и нажмите CTRL+A — будет выделена Текущая область;
• через пункт меню Вставка/ Таблицы/ Таблица конвертируем таблицу в формат Excel 2007. В диалоговом окне не забудем поставить галочку «Таблица с заголовками». Нажмите ОК;
• убеждаемся, что включена опция Автозамены «Создавать вычисляемые столбцы, заполнив таблицы формулами» ( Кнопка Офис/ Параметры Excel/ Правописание/ Параметры автозамены…/вкладка Автоформат при вводе );
• под заголовком столбца № (ячейка А2) введем формулу =СТРОКА()-СТРОКА($A$1) и нажмем клавишу ENTER (Ввод): столбец должен автоматически заполниться формулой до конца таблицы.

Как вставить/добавить новые/пустые строки в Excel?

Одними из наиболее часто используемых действий при работе с таблицами в Excel являются добавление и удаление строк вообще и пустых строк в частности. Рассмотрим механизмы добавления и удаления пустых строк в таблицах.

Вставка одной пустой строки в таблицу Excel

Добавить новую строку стандартными средствами Excel можно следующим образом: правой кнопкой мыши кликнуть на номере строки, над которой нужно вставить новую строку и выбрать в контекстном меню пункт «Вставить»

При этом, если какие-то данные в таблице были отформатированы (изменены шрифты, изменены цвета, установлены заливки ячеек), то после добавления новой строки появится кнопка «Параметры добавления», раскрыв которую можно выбрать для новой строки один из трех пунктов «Форматировать как сверху», «Форматировать как снизу» и «Очистить формат».

Вставить пустую строку можно также и через кнопки на панели инструментов. Для этого выделите строку, над которой нужно вставить новую пустую строку. После этого зайдите на вкладку «Главная», в группу «Ячейки» и раскройте меню кнопки «Вставить», выберите пункт «Вставить строки на лист».

Добавление нескольких пустых строк в таблицу Excel

Для того, чтобы вставить несколько новых строк в документ, необходимо выделить строку, выше которой нужно добавить новые строки, и не отпуская левой кнопки мыши выделить столько строк, сколько нужно вставить.

После того как строки выделены, необходимо щелкнуть правой кнопкой мыши на выделенном участке листа и из контекстного меню выбрать пункт «Вставить», либо воспользоваться кнопками во вкладках ленты, аналогично тому, как это описано в предыдущем пункте.

Как вставить/добавить заданное количество пустых/новых строк в определенные места?

Стандартными средствами Excel такую задачу решить можно не всегда, но не будем забывать про то, что существует такой мощный инструмент как VBA, позволяющий автоматизировать повторяющиеся однотипные действия. По инструкции устанавливается в приложение Excel надстройка для вставки пустых строк, которая предоставляет следующие возможности:

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

2. вставка заданного количества пустых строк как выше, так и ниже строк с искомым значением;

3. вставка разного количества пустых строк в зависимости от значения, указанного в заданном столбце;

4. вставка новых строк ниже каждой второй, третьей, пятой, n-ной строки выбранного диапазона (шаг задается пользователем);

5. вставлять пустые строки при каждой смене значения в заданном столбце;

6. вставлять пустые строки между заполненными (после каждой заполненной ячейки в столбце с заданным номером);

7. выбор диапазонов для вставки строк, предусмотрено несколько режимов:

а) используемый диапазон листа — диапазон активного листа, включающий в себя все ячейки, находящиеся между первой заполненной и последней заполненной ячейкой;

б) используемые диапазоны — отличается от предыдущего режима тем, что обработка данных производится в используемом диапазоне каждого листа рабочей книги;

в) диапазон листа — диапазон, который выберет пользователь;

г) диапазоны листов — это одноименные диапазоны на всех листа рабочей книги.

*Внимательно выбирайте диапазон для получения желаемого результата!

8. выбор необходимых условий для значений ячеек.

Для числовых значений: равно/не равно, больше/не больше, меньше/не меньше.

Для текстовых значений: совпадает/не совпадает, содержит/не содержит, начинается/не начинается, заканчивается/не заканчивается.

Программа выполняет проверку всех ячеек заданного диапазона и при выполнении выбранного условия вставляет нужное пользователю количество пустых строк над/под строкой (на выбор), содержащей ячейку с заданным значением. На выбор пользователя представлено множество условий для значений ячеек, с помощью которых можно определить места для вставки новых строк, а также предоставлен выбор диапазонов для обработки данных. Для большей гибкости поиск ячеек с нужными значениями можно осуществлять раздельно, как по текстовым значениям ячеек, так и по числовым. Предусмотрен также поиск как пустых, так и непустых ячеек. Этот макрос позволяет также добавлять новые строки в заданном количестве в каждую n-ую строку выбранного диапазона. Если, к примеру, вставить по две пустые строки ниже каждой второй строки диапазона А2:А24, т ополучим следующий результат:

Вставка разного количества пустых строк

При помощи надстройки можно вставлять изменяющееся количество пустых строк, если количество вставляемых строк указано в отдельном столбце.

Если установить флажок в поле «Разное количество пустых строк для вставки», то поле с количеством строк для вставки изменяется на номер столбца, в котором указаны числовые значения.

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

Удаление пустых строк

Удаление строк в таблицах Excel уже рассматривалось на страницах этого сайта, удаление же пустых строк является частным случаем удаления строк, следует заметить, что удаление пустых строк, как собственно и их вставку, можно производить программно, при помощи макроса (надстройки). Обо всем этом можно прочитать подробнее.

Трюк №37. Как в Excel автоматически добавлять данные в список проверки

Возможность проверки в Excel — это замечательная вещь, но она не умеет выполнять одно важное действие (без следующего трюка): автоматически добавлять новую запись в список, который используется в качестве источника для списка проверки.

Если вы уже работали с проверкой, то знаете, что это весьма искусная возможность. Вероятно, наиболее впечатляющим является ее умение добавлять в любую ячейку список, в котором пользователь может выбирать значения. Было бы здорово, если бы при вводе нового имени в проверенную ячейку Excel автоматически добавлял ее в список. Это возможно благодаря следующему трюку.

Пусть есть список имен в диапазоне А1:А10 (рис. 2.24).

Рис. 2.24. Рабочая книга, настроенная для списка проверки

Эти имена представляют сотрудников компании. Не так уже редко в подобный список добавляются имена новых сотрудников, но сейчас единственный способ сделать это — добавить новые имена в конце списка, а затем выбирать их в списке в проверенной ячейке. Чтобы обойти это ограничение, сделайте следующее. В ячейке А11 введите следующую формулу и скопируйте ее вниз до строки 20 (рис. 2.25) — обратите внимание на относительную ссылку на ячейку А10: =IF(OR($D$1=»»;COUNTIF($A$1:A10;$D$l));»x»;$D$l) , в русской версии Excel: =ЕСЛИ(ИЛИ($D$1=»»;СЧЁТЕСЛИ($А$1:А10;$D$1));»х»;$D$1) .

Рис. 2.25. Список с формулой, добавленной в строки А11:А20

Теперь выберите команду Формулы > Присвоить Имя (Formulas > Define Name) и в поле Имя (Names in workbook) введите MyNames. В поле Формула (Refers to) введите следующую формулу (рис. 2.26), щелкните кнопку Добавить (Add), затем щелкните кнопку ОК: =OFFSET(Sheet1!$А$1;0;0;COUNTIF(Sheet1!$А:$А;»<>х»);1) , в русской версии Excel: =CMEЩ(Sheet1!$А$1;0;0;СЧЕТЕСЛИ(Sheet1!$А:$А;»<>х»);1) .

Рис. 2.26. Превращение списка в динамический список

Выделите ячейку D1 и выберите команду Данные > Проверка (Data > Validation). В поле Тип данных (Allow) выберите пункт Список (List), а в поле Источник (Source) введите =MyNames; удостоверьтесь, что установлен флажок Список допустимых значений (In-Cell). Перейдите на вкладку Сообщение об ошибке (Error Alert) и сбросьте флажок Выводить сообщение об ошибке (Show error alert after invalid data is entered). Щелкните кнопку ОК. Вы увидите результат, как на рис. 2.27.

Рис. 2.27. Список с проверкой, добавленный в ячейку D1

Правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (View Code). Введите код, приведенный в листинге 2.5.

// Листинг 2.5 Private Sub Worksheet_Calculate() On Error Resume Next Application.EnableEvents = False Range(«MyNames») = Range(«MyNames»).Value Application.EnableEvents = True On Error GoTo 0 End Sub

Чтобы вернуться обратно в Excel и сохранить рабочую книгу, закройте окно. Теперь выделите ячейку D1, введите любое имя, не входящее в список, и нажмите клавишу Enter. Снова выделите ячейку D1 и взгляните на список. Теперь в списке должно присутствовать новое имя (рис. 2.28).

Рис. 2.28. Список после добавления новой записи в ячейку D1

Если вы хотите добавить больше 10 новых имен в список, скопируйте формулу ниже строки 20.