Вставить формулы в пустые строки в excel

Заполнение пустых ячеек значениями из соседних ячеек

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек — по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками — например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь…

В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).

Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks):

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

И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter. И все! Просто и красиво.

В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values). Так будет совсем хорошо.

Способ 2. Заполнение пустых ячеек макросом

Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert — Module и копируем или вводим туда вот такой короткий код:

Sub Fill_Blanks()
    For Each cell In Selection
        If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value
    Next cell
End Sub

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

Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.

Способ 3. Power Query

Power Query — это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно — в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:

• Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
• При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов — всё делать заново.

Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы — Диспетчер имен), либо превратить в «умную» таблицу командой Главная — Форматировать как таблицу (Home — Format as Table) или сочетанием клавиш Ctrl+T:

После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range). Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.

В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl) и на вкладке Преобразование выберем команду Заполнить — Заполнить вниз (Transform — Fill — Fill Down):

Вот и всё Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная — Закрыть и загрузить — Закрыть и загрузить в… (Home — Close&Load — Close&Load to…)

В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные — Обновить всё (Data — Refresh All).

Ссылки по теме

• Объединение двух столбцов с данными
• Быстрое выделение всех ячеек с формулами или константами
• Быстрое заполнение пустых ячеек макросом из надстройки PLEX

Вставить пустые строки в Excel через одну.

​Смотрите также​​myActiveColumn = ActiveCell.Column​ ​ совпадают.​ ​myLastRow = Cells(Rows.Count,​​Rows(i).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​​И, наконец, чтобы ввести​ обращениями к диапазону.​ = i +​ «после», а то​ ks, k1 As​s = s​Есть файл с​ выделено. Порядок столбцов​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ОСТАТ(СТРОКА();3);»»;ИНДЕКС(C$3:C$11;СТРОКА()/3))​kreg​ его, а затем​ставить пустые строки в​
​Рассмотрим несколько вариантов,​
​myLastRow = Cells(Rows.Count,​ ​If tblMy(i, 1)​​ ActiveCell.Column).End(xlUp).Row​
​End If​ эту формулу во​
​ Можно добавить в​ 2 Loop End​ часто бывает, что​ Long​ & «,» &​ промеченными группами строк:​ вставки, так же​ASUS​: А как сделать​
​ на вкладке​Excel через одну.​как​ myActiveColumn).End(xlUp).Row​ <> tblMy(i -​’Берём в VBA-таблицу​metka:​ все выделенные (пустые)​
​ цикл строку DoEvents​ ​ Sub ‘Удаляем Sub​​ вопрос далеко не​
​Dim x As​ i & «:»​Столбец 1 Столбец​ зависит от порядка​: Привет всем.Есть таблица​ так что-бы строки​
​Главная​Нам нужно вставить​вставить пустые строки в​’Берём в VBA-таблицу​ 1, 1) Then​
​ «tblMy» данные из​Next i​ ячейки нажимаем​ и пойти пить​ DeleteRows() Dim i​ полностью соответствует реальному​ Long​ & i​ 2​
​ их выделения. Например,​
​ со строками идущей​ ​ добавлялись не во​
​нажмите кнопку​ пустые строки в​Excel через одну​ «tblMy» данные из​’Вставка двух пустых​ двух столбцов.​
​Application.ScreenUpdating = True​Ctrl + Enter​ чай, либо пройти​
​ As Long: i​ положению вещей…​With Application​
​Next​Какое-то слово 1​ через одну и​ подряд. Как добавить​ всем документе, а​
​Вставить​ ​ несколько столбцов таблицы,​​,​
​ двух столбцов.​ строк между группами.​tblMy() = Cells(1,​End Sub​вместо обычного Enter.​ по ссылке, которую​
​ = 200 ‘200​
​anddrei55​
​.ScreenUpdating = False​​s = Mid(s,​ ​Какое-то слово 1​
​ т.п.​ формулой между этими​ только в выбранном​и выберите пункт​ на всю ширину​две строки, ячейки,​tblMy() = Cells(1,​Rows(i).Resize(2).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​ ActiveCell.Column).Resize(myLastRow, 2).Value​SergeyKorotun​
​ И все! Просто​ дал mtts54 выше​ — количество проверяемых​: Файл-пример прикрепил, смотреть​
​.Calculation = xlCalculationManual​ 2, 99999)​Какое-то слово 1​Теперь добавим в прайс-лист​ строками один пустую​ диапазоне.​​Удалить столбцы с листа​ таблицы, т.д. Сначала​ т.д. Бывает, в​ myActiveColumn).Resize(myLastRow, 2).Value​’Делаем границу между​For i =​: Работает, но ожидал​ и красиво.​ в посте #14,​ строк Do While​ по вкладкам.​nRow = Cells(Rows.Count,​Range(s).Insert​
​Какое-то слово 2​ заголовок и новую​ строку. Приложу файл​Hugo​.​ напишем формулу как​ большой заполненной таблице,​’Двигаемся до третьей​ двумя вставленными​ UBound(tblMy, 1) To​ другую реализацию, за​
​В качестве завершающего мазка​ — там варианты​ i > 0​Z​ 1).End(xlUp).Row​End Sub​Какое-то слово 2​ позицию товара «Товар​ чтобы была понятно.​: А так правильней​Можно также щелкнуть правой​ во втором варианте.​ нужно вставить пустые​ строки, т.к. в​’пустыми строками.​ 2 Step -1​
​ один проход. Недостаток​ я советовал бы​ оптимизированных макросов, рассчитанных​ If IsEmpty(Cells(i, 1))​: А по-простому -​’Range(Cells(2, 20), Cells(Cells(Rows.Count,​Ярослав​Какое-то слово 2​ новинка». Для этого​ Заранее спасибо за​ будет (и на​

excel-office.ru

Как вставлять и удалять ячейки, строки и столбцы

​ кнопкой мыши в​ Затем, скопируем эту​ строки между каждой​ первой строке​

​With Rows(i).Borders(xlEdgeBottom)​​’Если данные в​ в том, что​ заменить все созданные​ на большие объёмы​ Then Cells(i, 1).EntireRow.Delete​ отсортировать, выделить да​ 1).End(xlUp).Row + 1,​

Вставка и удаление столбца

1. ​: Sub Макрос3()​Какое-то слово 2​ вставим две новых​​ предоставленный ответ.​​ 2 умножать не​​ верхней части столбца​​ формулу вправо на​​ строкой таблицы. Вручную​​’находится заголовок и​

2. ​.LineStyle = xlDash​ первом столбце в​ если добавится еще​​ формулы на значения,​​ данных.​​ i = i​​ грохнуть «пустоту» -​​ 20))​​’​

   ​Какое-то слово 3​ строки одновременно.​_Boroda_​ надо, да и​​ и выбрать команду​​ нужное количество столбцов.​​ это сделать сложно,​​ вторую строку​

Вставка и удаление строки

1. ​.Weight = xlMedium​ текущей строке и​ одна подгруппа по​​ ибо при сортировке​​anddrei55​​ — 1 Loop​​ «Ctrl+-«?..​​kv = InputBox(«Введите​​’ Макрос3 Макрос​

2. ​Какое-то слово 3​Выделите несмежный диапазон двух​: Читайте Правила форума.​​ можно iLastRow привлечь):​​Вставить​​Например, в нашей​​ долго. Но, Excel,​​’не нужно сравнивать​​End With​

   ​ строке,​ третьему столбцу, макрос​ или добавлении/удалении строк​: но я там​​ End Sub​​abricos29​​ количество строк для​​’ Макрос записан​

Вставка ячейки

1. ​В первом столбце​ ячеек A1;A4(обратите внимание​ Пункты 4 и​For i =​​или​​ таблице два столбца.​

2. ​ опять, дает нам​​ с первой.​​’Делаем заголовок у​’которая выше, не​ придется не немного​

support.office.com

Добавление пустых строк между заполненных

​ корректность формул может​​ увидел код только​
​anddrei55​: Я не так​ вставки между строками»,​
​ 13.09.2008 (zyaa)​ — текст, во​ вместо символа «:»​ 5q в особенности.​ 100 To 2​Удалить​ Мы копируем формулу​
​ возможность быстро вставить​For i =​ нижней группы.​ совпадают.​

​.​​ в ячейку D1.​ нужное количество строк​ UBound(tblMy, 1) To​

​Cells(i + 1,​​If tblMy(i, 1)​ третий блок, в​ все ячейки в​ строк, а быстрого​ огромное выручили, этого​

​ задачу, нашёл такое​​ks = InputBox(«Введите​ пустых строк вниз​

​ номер группы. Нужно​​ — это значит,​

​Создавая разного рода новые​​Cells(i, 1).EntireRow.Insert​Чтобы вставить строку, выделите​Теперь выделяем ячейки​ в нужном месте.​ 3 Step -1​ myActiveColumn).Value = «Шапка​

​Next i​​ ее, а затем​ C1 и D1​Первый вариант.​
​’Если данные в​
​ группы»​ 1, 1) Then​

​ возрастет.​​ и тут же​Irregular Expression​Jack Famous​ на форуме :​
​ 1)​

​ ячейки​​ пустых строки после​ диапазона, для убедительности​ прайсы, нельзя заранее​roka​

​ на вкладке​​ и копируем (протягиваем)​Вставить пустые строки в​ первом столбце в​

planetaexcel.ru

Вставить заданное количество пустых ячеек через строку (Иное)

​’Если данные во​​Rows(i).Resize(2).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​Совсем не знаю​ вставьте обратно с​: anddrei55, значит, придётся​: anddrei55, а мой​ Sub УдалениеСтрок() Dim​k1 = InputBox(«Введите​’ Сочетание клавиш:​ каждой группы. Как​ введите A1;A4 в​ предвидеть количество необходимых​: еще как вариант,​Главная​ их вниз до​Excel через одну​ текущей строке и​ втором столбце в​

​With Rows(i).Borders(xlEdgeBottom)​​ VBА, но из​ помощью​ создать отдельную тему​ вариант чем не​
​ r As Long,​ первую строку», ,​​ Ctrl+я​

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

​.​​ строке,​ текущей строке и​.LineStyle = xlDash​ вашего кода вижу​Специальной вставки (Paste Special)​.​ подошёл?​

​ Rng As Range​​ 1)​’​ максимально простым путем?​ нажмите Enter). Как​ Использование программы Excel​

​ место и сделать​​Вставить​ так.​

​У нас такая​​’которая выше, не​ строке,​.Weight = xlMedium​

​ что логические операции​​в контекстом меню,​
​А в качестве​

​mtts54​​ For r =​kv1 = Val(kv)​k = 10​OLEGOFF​ выделять несмежные диапазоны​ – это в​ActiveCell.Resize(i).EntireRow.Insert​и выберите пункт​Таблицу Excel можно​

​ таблица.​​ совпадают.​’которая выше, не​End With​
​ поддерживаются.​

excelworld.ru

Как вставить строку или столбец в Excel между строками и столбцами

​ выбрав параметр​ идеи, в каком​: ,​ 1 To ActiveSheet.UsedRange.Row​ks1 = Val(ks)​ ‘ кол-во вставок​: Sortafreel,попробуйте такой макрос.​ вы уже знаете​ значительной степени создание​где i необходимое​Вставить строки на лист​ настроить так, что​

​Если таблица не большая,​If tblMy(i, 1)​ совпадают.​’Если данные во​

​На каком то​Значения (Values)​ направлении двигаться, могу​нарисовал серию великолепных​ — 1 +​sh = kv1​ пустых строк​Выделяете столбец и​ из предыдущих уроков.​ и настройка таблиц,​ количество строк​.​ будут автоматически сделаны​ то можно сделать​

Как в Excel вставить столбец между столбцами?

​ <> tblMy(i -​ElseIf tblMy(i, 2)​ втором столбце в​

​ несуществующем языке полупрограмму-полуалгоритм​. Так будет совсем​ предложить работать не​ кодов, работающих с​ ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r))​

1. ​ + ks1​For i =​ три раза жмете​Теперь снова используйте инструмент​ в процессе которой​Igor67​Чтобы удалить строку, выделите​ разделительные линии между​ так. Выделяем, при​
2. ​ 1, 1) Then​ <> tblMy(i -​ текущей строке и​ вижу так:​ хорошо.​

​ с листом и​ большой скоростью и​ = 0 Then​

​For i =​

Вставка нескольких столбцов между столбцами одновременно

​ 1 To k​ на кнопку​ «Главная»-«Вставка»-«Вставить строки на​ требуется вставка и​: А я бы​ ее, а затем​ строками, например, по​ нажатой клавише «Ctrl»,​’Вставка трёх пустых​ 1, 2) Then​ строке,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>go bottom //стать на​SergeyKorotun​

​ строками, а с​ с разными условиями​ If Rng Is​ ks To nRow​m = 1​

​Сергей1985​ лист». На рисунке​ удаление различных элементов.​ это понял (для​ на вкладке​ датам, по ценам,​ все строки, под​ строк между группами.​Rows(i).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​’которая выше, не​ последнюю строку​

Как вставить строку в Excel между строками?

​: присоединил​ массивом, т.е. загружаете​ удаления строк,​ Nothing Then Set​ Step sh​For j =​

​: Имеется таблица из​ видно как вставить​Сначала рассмотрим способы вставки​ диапазона) как вставка​Главная​ т.д. Подробнее о​ которыми нужно вставить​Rows(i).Resize(3).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​End If​ совпадают.​st_1=A1 // присвоить​Скрипт​ свою таблицу с​

​в т.ч пустых,​ Rng = Rows(r)​If kv >​ 1 To m​ двадцати строк. Необходимо​ пустую строку в​

​ строк и столбцов​ ячеек:))​нажмите кнопку​ таком способе визуализации​ пустую строку (каждую​’Делаем границу между​Next i​ElseIf tblMy(i, 2)​

​ значение ячейки колонки​:​ лишними строками в​ по значению в​ Else Set Rng​

​ 1 Then​ActiveCell.Offset(j, 0).Select​ вставить по две​

Удаление строк и столбцов

​ Excel между строками.​ листа при создании​Cells(i, 1).EntireRow.Insert Shift:=xlDown​Вставить​ данных в таблице,​ строку отдельно, не​

​ двумя вставленными​Application.ScreenUpdating = True​ <> tblMy(i -​ А1 последней строки​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Procedure_1()​

​ массив, очищаете диапазон,​ ячейке и с​ = Union(Rng, Rows(r))​For x =​Selection.EntireRow.Insert​ пустых строки под​Несложно догадаться о втором​ таблиц.​

​я​и выберите пункт​ читайте в статье​ все вместе).​’пустыми строками.​End Sub​ 1, 2) Then​st_2=A2 // присвоить​Dim tblA() As​

​ обрабатываете массив до​ поддержкой * (любые​ End If Next​ 1 To kv​ActiveCell.Offset(j, 0).Select​

​ каждой существующей (то​ способе. Нужно выделить​Обратите внимание, в данном​: Ребята, а если​Удалить строки с листа​ «Разделительная линия строк​Когда выделим все​With Rows(i).Borders(xlEdgeBottom)​SergeyKorotun​Rows(i).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​ значение ячейки колонки​ Variant​ нужного вида и​

exceltable.com

Добавить пустые строки после каждой группы строк

​ символы).​​ r If Not​
​ Step 1​Selection.EntireRow.Insert​
​ есть должно получиться​ заголовки строк 1​
​ уроке указываются горячие​
​ нужно вставлять по​
​.​
​ в таблице Excel»​
​ нужные строки, отпускаем​
​.LineStyle = xlDash​
​: Немного не так.​
​End If​
​ А2 последней строки​
​Dim myLastRow As​ пишем его обратно​См., например:​ Rng Is Nothing​Cells(i + 1,​Next j​ не 20, а​ и 3. Кликнуть​ клавиши для добавления​

​ 45 строк, помогите​​Можно также щелкнуть правой​
​ здесь.​ клавишу «Ctrl». Нажимаем​.Weight = xlMedium​

CyberForum.ru

Вставка строки через строку

​ Вставляться должно не​​Next i​do while not​ Long​ в лист. Количество​Jack Famous​ Then Rng.Delete End​ 1).EntireRow.Insert​ActiveCell.Offset(1, 0).Select​ 60 строк таблицы,​ правой кнопкой по​ или удаления строк​ неумехе​ кнопкой мыши выделенную​

​Можно вставить в​​ на выделенную строку​End With​
​ «шапка группы», а​
​Application.ScreenUpdating = True​
​ bof() // цикл​

​Dim i As​​ строк макроса увеличится​: mtts54, благодарю​

​ Sub​​Next x​Next i​

​anddrei55​​mtts54​End If​End Sub​ первая строка, а​ строк и выбрать​ надо использовать после​: Как сделать чтоб​

​ опцию «Вставить».​​ выделения целой строки​
​ вставлялось по 2​
​Вставить​ формулы, написать любые​ функцию «Вставить» -​
​’Копируем заголовок из​ в которой подписаны​
​: Завтра девки мне​skip -1// подняться​’———————————————————————————————​ выполнения для 10000​
​ процесс? Если добавлять​
​ запрос: «удалить строки​.Calculation = xlCalculationAutomatic​
​: Мне тоже самое​
​ третья были бы​

​Чтобы добавить строку или​​ или столбца. Чтобы​
​ пустые строчки?​
​или​
​ данные. Затем, скрыть​ «Строку». Получится так.​
​ первой строки Excel-листа.​ наименования столбцов. В​ скажут спасибо, а​ на одну строку​
​’ГРУППИРОВКА ПО ПЕРВОМУ​ строк упадёт до​
​ 10000 пустых строк,​
​ по условию» или​.ScreenUpdating = True​ нужно сделать, только​
​ пустыми) Спасибо​ столбец в Excel​
​ выделить строку на​
​Lunatic​Удалить​
​ эти столбцы, строки​
​Добавить пустые столбцы в​
​’Копировать нужно, т.к.​
​ прикрепленном файле на​
​ я вам +​
​ вверх​
​ СТОЛБЦУ.​
​ нескольких секунд или​

​SergeyKorotun​​ в текущей строке​ ActiveCell.Column).End(xlUp).Row​ 90% времени пожирается​Как ускорить процесс​
​: anddrei55, проверяйтеКод из​ vbInformation, «Вставка строк»​ строку после диапазона​

planetaexcel.ru

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

​ Dim lr&, i&,​​Нашел на форуме​
​ до n (где,​Guest​В процессе работы с​ прайс, в котором​
​ пустых строк в​
​.​ можно установить ссылку​ меню выбираем функцию​ втором столбце в​ только в первый​ ними вставлять еще​здесь после текущей​
​ ActiveCell.Column).Resize(myLastRow, 1).Value​ работы с данными​ As Range Dim​ colDel%, colFull% colDel​ тему, как добавить​ n последняя не​

​: или a.offset(1,0).rows.insert не​​ Excel, удалять строки​
​ недостает нумерации позиций:​ диапазон через каждую​В окне​
​ на конкретную ячейку​ «Вставить столбец».​ текущей строке и​ столбец, участвующий в​ и первую строку,​
​ строки вставляем две​For i =​

​ (фильтрации, сортировки, подведения​​ d As Range​ = Application.InputBox(«Укажите ячейку​ пустую строку после​ пустая строка). Если​ помню точно​

​ и столбцы листа​​Чтобы вставить столбец между​ заполненную строку, т.е.​Вставка​ в таблице на​Как настроить таблицу, чтобы​
​ строке,​ группировке, но еще​ чтобы в каждой​

​ строки с разделителем​​ UBound(tblA, 1) To​ итогов и т.д.)​ Dim k As​ СТОЛБЦА, по которому​ каждой заполненной.​ ячейка А(k) объединена​Влад​ приходится не реже​

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

​ другом листе. Например,​​ столбец добавлялся автоматически,​’которая выше, не​ и на строку​ группы была своя​

​ из пунктирной линии​​ 2 Step -1​ нужен непрерывный список,​ Integer Dim p​ определять пустые ячейки:»,​Вот код:​ с ячейкой А(k+1),​: Отличный способ надолго​ чем вставлять. Поэтому​ номеров позиций прайс-листа,​ Большая просьба ,​ или ячейку для​ у нас таблица​ читайте в статье​ совпадают.​ с шапкой. Но​ шапка​skip -1//​If tblA(i, 1)​ т.е. таблица без​ As Boolean p​ «Выбрать ОДНУ ячейку»,​Sub InsertRows() Dim​ то смотрим объединена​

​ зациклить макрос:)​​ стоит попрактиковаться.​ можно воспользоваться одним​ только понятным языком.​ вставки.​

​ со списком заказов​​ «Как добавить столбец​ElseIf tblMy(i, 2)​ параметр цикла можно​Скрипт​skip -1//возврат на​ <> tblA(i -​ разрывов (пустых строк​ = True Application.ScreenUpdating​ , Type:=8).Column colFull​ i As Long​ ли она со​Jom​Для наглядного примера удалим​ из двух способов:​ если вообще возможно​Beldiver​ от разных заказчиков.​ в Excel автоматически».​ <> tblMy(i -​ оставить и 1,​:​ текущую строку, в​ 1, 1) Then​ и ячеек -​ = False Application.Calculation​ = Application.InputBox(«Укажите ячейку​ For i =​ следующей итп, если​: +1. пока до​ из нашего прайс-листа​Перейдите курсором и активируйте​ , то без​: Вопрос, такого характера.​ Чтобы найти контактные​Если нужно вставить​ 1, 2) Then​

​ лишнюю первую шапку​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Procedure_1()​ VBA не надо,​Rows(i).Resize(2).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​ по возможности). На​ = xlCalculationManual Do​ СТОЛБЦА, по которому​
​ 2 To 200​ нет то заполняем​ строки 65535 не​ нумерацию позиций товара​ ячейку A1. Потом​ всяких там макросов.​Как в exele​ данные по конкретному​ несколько строк или​Rows(i).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow​ можно удалить вручную.​Dim tblMy() As​ так как обрабатывается​With Rows(i).Borders(xlEdgeBottom)​ практике же часто​ While p p​ определять последнюю строку:»,​ Step 2 Cells(i,​ ее номером и​ дойдет.​ и столбец единиц​ перейдите на закладку​ очень нужно для​ добавить пустые строки​

​ заказчику, достаточно нажать​​ столбцов не во​End If​Скрипт​

​ Variant​​ массив​.LineStyle = xlDash​ мы имеем как​

​ = False Set​​ «Выбрать ОДНУ ячейку»,​
​ 1).EntireRow.Insert Next i​ вставляем после нее​как вариант.​ измерения – одновременно.​ «Главная» раздел инструментов​
​ работы !​ между заполнеными?​ на ссылку. Excel​ всей таблице, то​Next i​
​: SergeyKorotun, на Excel-листе​

​Dim myLastRow As​​endif​

​.Weight = xlMedium​​ раз таблицы с​ d = Nothing​ , Type:=8).Column lr​ End Sub​ пустую строку (ну​
​yfirst =20​Выделяем несмежный диапазон ячеек​ «Ячейки» кликните по​
​Serge_007​То есть, есть​ перейдет в другую​ можно использовать другие​Application.ScreenUpdating = True​ в первой строке​ Long​if st_1=a1and st_2<>a2​End With​ пропущенными пустыми ячейками​ Intersect(Selection, ActiveSheet.UsedRange).Select For​ = Cells(Rows.Count, colFull).End(xlUp).Row​А как прописать​ или строки в​ylast =60​ A1;D1 и выбираем​ инструменту «Вставить» из​: Без макросов вставить​ список с 100​ таблицу на другом​ способы. Смотрите об​End Sub​ есть заголовок.​Dim myActiveColumn As​ // перехода через​End If​ — например после​ Each ch In​ Set gr =​ макрос на обратное​ другой части вставить…)​for y= yfirst​ «Главная»-«Удалить»-«Удалить столбцы с​ выпадающего списка выберите​ пустые строки в​ строками, как автоматически​ листе именно на​ этом статью «Как​
​SergeyKorotun​Есть два варианта​ Long​ группу нет, ачерез​Next i​ копирования результатов сводных​ Selection If ch.Value​ Nothing For i​

​ действие, чтоб удалить​​наверное это сложно,​ to ylast step​ листа». Контекстным меню​ опцию «Вставить столбцы​ исходные данные -​ после каждой строчки​ строку этого заказчика.​ добавить строку, столбец​:​ вставки этого заголовка​Dim i As​ подгруппу есть​’———————————————————————————————​ таблиц или выгрузок​ = 0 Then​

​ = lr To​​ пустые строки после​ раз мне никто​ 3​ так же можно​ на лист».​

​ только вручную​​ добавить пустую? Чтоб​Можно применить такие​​ в Excel».​
​Алекс​ в другие места​ Long​здесь нужно добавить​’ГРУППИРОВКА ПО ВТОРОМУ​ в Excel из​ If d Is​ 1 Step -1​ заполненных? Скрыть через​ еще не ответил​Rows(y + 1).Insert​ удалять, если выделить​Щелкните правой кнопкой мышки​Формулами можно в​ получилось в итоге​ ссылки в списках​Второй вариант.​: Добрый день!​ листа:​Application.ScreenUpdating = False​ одну пустую строку​ СТОЛБЦУ.​ внешних программ. Таким​ Nothing Then Set​ If Len(Cells(i, colDel))​

planetaexcel.ru

Заполнение пустых ячеек

​ фильтр не вариант.​ :)​ Shift:=xlDown​ заголовки A1и D1,​ по заголовку столбца​ соседнем столбце:​ 200 строк, заполненная-пустая-заполненная-пустая?​ по классам, ссылки​Вставить пустые ячейки в​Необходим аналогичный макрос​с сохранением форматирования. Под​’Берём номер столбца,​skip -1 //возврат​’Т.к. количество строк​ образом, возникает необходимость​ d = ch​ = 0 Then​Irregular Expression​…но я все​next y​ а не ячейки.​ A. Из появившегося​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЦЕЛОЕ(СТРОКА()/3)=СТРОКА()/3;ИНДЕКС(C$3:C$11;СТРОКА()/3);»»)​

​Excel через одну.​ — добавление пустой​

​ форматированием понимается: цвет​ где находится активная​ на текущую строку,​ изменилось, нужно заново​ заполнить пустые ячейки​

​ k = 1​​ If gr Is​​: anddrei55,​​ таки надеюсь это​​Jom​Удаление строк происходит аналогичным​​ контекстного меню выберите​ ​_Boroda_​ ​ в ворде проделать(потом​​ родителей, списки товаров,​

​Таблица, как в​ строки после изменения​ шрифта, размер шрифта,​ ячейка,​ в VBA не​ провести некоторые действия.​ таблицы значениями из​

​ Else Set d​ Nothing Then Set​1) этот код​ победить )​​: хотя вру. вышепредложенный​​ способом, только нужно​ опцию «Вставить»​: В общем случае​

​ в ексель загоню),каким​ разделов, сотрудников по​ первом варианте. Нам​ значения в столбце​ заливка ячеек и​’в переменную. Т.к.​ надо, так как​myLastRow = Cells(Rows.Count,​ верхних ячеек, то​ = Union(d, ch)​ gr = Cells(i,​ не проверяет, заполнена​Igor67​​ код с offset​​ выбирать в соответствующее​Теперь можно заполнить новый​​В ячейке I1​​ другим редактором?​ отделам, т.д. Как​

planetaexcel.ru

Как вставить пустые строки после изменения значений в столбц (Форматирование)

​ нужно вставить пустые​​ и суммирование по​

​ т.д.;​​ этот номер столбца​​ обрабатывается массив​
​ ActiveCell.Column).End(xlUp).Row​ бишь...​
​ k = k​ colDel) Else Set​
​ строка или нет;​: Как то Pavel55​
​ вообще не рабочий.​
​ меню инструмента. А​
​ столбец номерами позиций​ количество пустых строк,​
​Pavel55​ сделать такие гиперссылки,​
​ ячейки между заполненными​ всем столбцам в​вставка только текста заголовка.​
​'в коде нужен​endif​
​'Берём в VBA-таблицу​  из  ​ + 1 End​
​ gr = Application.Union(gr,​2) для оформления​ выкладывал макрос, вот​
​ даже если изменить​
​ в контекстном меню​
​ прайса.​
​ которые нужно добавить​
​: Может так?​
​ смотрите в статье​
​ ячейками в столбце​
​ этой пустой строке,​
​Второй вариант будет​ несколько раз и​
​// если оба​ "tblA" данные из​ сделать ​
​ If End If​ Cells(i, colDel)) End​
​ кода есть кнопка​ он с небольшой​ его немного, он​
​ – без изменений.​​200?'200px':''+(this.scrollHeight+5)+'px');">=ЕСЛИ(ОСТАТ((СТРОКА()-СТРОКА(G$3));(I$1+1))=0;ИНДЕКС(C$3:C$11;(СТРОКА()-СТРОКА(G$3))/(I$1+1)+1);"")​
​Sub InsertRows()​ "Гиперссылка в Excel​ А.​
​ подскажите как добиться​ быстрее работать, если​
​ удобнее указывать​ условия не выполняются,​ второго столбца.​Это можно реализовать, буквально,​ If k =​
​ If End If​ — воспользуйтесь ей​
​ доработкой...​
​ только до 20​
​ Только нужно их​В нашем прайсе все​Lunatic​
​Dim i As​ на другой лист".​В ячейке С1​
​ этого Вашими предложенными​
​ макросу нужно работать​
​'переменную вместо "ActiveCell.Column",​
​ то ничего не​
​tblA() = Cells(1,​
​ в несколько движений.​

​ 1000 Then d.EntireRow.Delete​​ Next i If​ скорее, пока модераторы​Вводите сколько строк​ строки работать будет.​ соответственно выделять по​ еще не достает​: ух ты !!!​ Long​Вставляйте и удаляйте строки,​ пишем такую формулу.​ макросами?​ с большим количеством​
​’т.к. можно что-нибудь​ делается​ ActiveCell.Column + 1).Resize(myLastRow,​Выделяем диапазон ячеек в​ k = 0​
​ Not gr Is​ не видят;​ надо вставить и​
​genyaa​ номерам строк.​
​ двух столбцов: количество​ работает! спасибо, Serge​For i =​
​ столбцы и ячейки​ =ЕСЛИ(ОСТАТ(СТРОКА();2);"";ИНДЕКС(A:A;СТРОКА()/2))​lexkaz​
​ данных.​ забыть сделать,​st_1=A1// присвоить значение​ 1).Value​
​ первом столбце, который​ p = True​ Nothing Then gr.EntireRow.Delete​
​3) EntireRow.Delete не​ ОК!!!​: попробуйте так:​Чтобы удалить строку или​
​ и единицы измерения​ 007, а у​
​ 2 To 200​ для оптимального размещения​Копируем формулу вниз​: Люди добрые помогите​
​Какой вариант должен​
​'если код будет​ ячейки колонки А1​For i =​ надо заполнить (в​ Exit For End​
​ End Submtts54, я​
​ пробовали?​Guest​Sub AddForEach2Rows()​ столбец в Excel​
​ (шт. кг. л.​ нас с позволения​
​ Step 2​ данных на листе.​ по столбцу. Получится​ плиз​ делать макрос?​
​ меняться и данные​
​ текущей строки​ UBound(tblA, 1) To​ нашем примере, это​ If Next ch​
​ ничего не нашёл…​Sanja​: Добрый день!​
​Dim a As​ используйте горячие клавиши​ упак.). Чтобы одновременно​ сказать IT отдел​
​Cells(i, 1).EntireRow.Insert​

​Примечание:​​ так.​мне нужно тоже​SergeyKorotun​ будут браться не​
​st_2=A2 // //​
​ 2 Step -1​ A1:A12).​
​ Loop If Not​ (см. скрин)​
​: И при удалении​приведенный здесь макрос​
​ Range​
​ CTRL+«минус» предварительно выделив​ добавить два столбца,​
​ так ничего и​Next i​ В Microsoft Excel установлены​
​Какими способами быстро скопировать​ самое только вместо​
​: с форматированием​ те.​ присвоить значение ячейки​
​'Если текущая или​Нажимаем клавишу​ (d Is Nothing)​Irregular Expression​
​ цикл нужно организовывать​ я сделала, чтобы​
​Set a =​ их.​ выделите диапазон из​
​ не ответил )))​
​End Sub​
​ следующие ограничения на​
​ формулу по столбцу,​
​ линии надо вставить​
​Скрипт​myActiveColumn = ActiveCell.Column​ колонки А2 текущей​ вышестоящая ячейка пустые.​
​F5​ Then d.EntireRow.Delete End​
​: anddrei55, вот как-то​ с последней по​ он работал не​
​ ActiveSheet.UsedRange​
​Примечание. Вставка новых столбцов​
​ двух ячеек C1:D1.​
​Lunatic​
​Guest​

​ количество строк и​​ смотрите в статье​ последнее значение​:​myLastRow = Cells(Rows.Count,​

​ строки​​If IsEmpty(tblA(i, 1))​и затем кнопку​ If Application.Calculation =​ так, проверка по​ начальную строку с​ от последней строки​For i =​ и строк на​ Далее используйте тот​

​: а вот Сашино​​: Я извеняюсь за​​ столбцов: 16 384 столбца​
​ «Копирование в Excel»​я знаю что​
​200?'200px':''+(this.scrollHeight+5)+'px');">Sub Procedure_1()​ myActiveColumn).End(xlUp).Row​
​end do​ = True Or​
​Выделить (Special)​ xlCalculationAutomatic Application.ScreenUpdating =​
​ пустой первой ячейке,​
​ шагом -1​ а наоборот от​ a.Rows.Count To 1​
​ самом деле является​ же инструмент на​
​ решение у меня​ неграмотность. Но можно​ в ширину и​
​ тут.​
​ тут просто вместо​Dim tblMy() As​
​'Берём в VBA-таблицу​Скрипт​ IsEmpty(tblA(i - 1,​и в появившемся​
​ True End Sub​
​ если надо по​anddrei55​
​ первой к последней.​ Step -1​ заменой. Ведь количество​
​ главной закладке «Вставить»-«Вставить​ почему-то не работает.​
​ по подробней.​ 1 048 576 строк в​Вставить пустые строки в​
​ линии написать оператор​ Variant​ "tblMy" данные из​: SergeyKorotun, да, вы​
​ 1)) = True​ окне выбираем​
​Но при таком​ пустой целой строке​: Как в таком​
​Но макрос недорабатывает.​a.Rows(i + 1).EntireRow.Insert​
​ строк 1 048​
​ столбцы на лист».​ всё так же​
​Guest​
​ высоту.​
​Excel через две, три​
​ с последним значением​
​Dim myLastRow As​
​ двух столбцов.​ правы, есть другой​
​ Then​Выделить​ случае нужно выделять​
​ - скажите:​ случае прописать проверку​ Из-за вставки строк​a.Rows(i + 1).EntireRow.Insert​
​ 576 и колонок​Или выделите два заголовка​
​ и выстаивается​: Да просто вставьте​Чтобы вставить столбец, выделите​
​, т.д.​
​ но не знаю​
​ Long​
​tblMy() = Cells(1,​
​ способ решения вашей​

​’Переходим к следующей​​пустые ячейки​ столбец, что не​’Добавляем Sub InsertRows()​ заполненных строк и​ общий диапазон ведь​Next​ 16 384 не​ столбца C и​Lunatic​ этот код в​ его, а затем​Используем формулу как​ как …​Dim myActiveColumn As​ myActiveColumn).Resize(myLastRow, 2).Value​ задачи:​ ячейке.​(Blanks)​ очень удобно. Можно​ Dim i As​ чтоб после заполненых​ увеличивается, но макрос​End Sub​

​ меняется. Просто последние,​​ D, щелкните правой​: в вашем -​ пустой модуль и​
​ на вкладке​ во втором варианте,​БОЛЬШОЕ СПАСИБО !!!​ Long​

• ​For i =​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Procedure_1()​GoTo metka​:​ забыть выделить столбец​
• ​ Long: i =​

​ добавилась пустая?​ останавливается на рассчитанном​слэн​ заменяют предыдущие… Данный​ кнопкой мышки и​
​ да, работает.​ запустите макрос на​

​Главная​​ только вместо цифры​

​Хоть вопрос и​​Dim i As​​ UBound(tblMy, 1) To​
​Dim tblMy() As​End If​
​Не снимая выделения вводим​ и тогда код​
​ 1 Do While​И в обратном​
​ сначала диапазоне, не​: или так:​
​ факт следует учитывать​
​ выберите опцию «Вставить».​Lunatic​ выполнение.​
​нажмите кнопку​
​ «2» напишем цифру​
​ похожий, но все-таки​ Long​
​ 2 Step -1​ Variant​'Если данные в​
​ в первую ячейку​ сработает не так​
​ i < 200​ направлении, проверку и​ доходит до конца.​
​Sub insrows()​ при заполнении листа​
​Примечание. Столбцы всегда добавляются​: ааа! тоже классная​
​Pavel55​Вставить​ «3». Формула получится​
​ немного другой. А​Application.ScreenUpdating = False​'Если данные в​Dim myLastRow As​
​ текущей ячейке и​ знак равно и​
​ как надо.​ '200 - количество​ удаление пустых строк?​
​ Что делать?​Dim i As​
​ данными более чем​
​ в левую сторону.​ формула. спасибо. всё​
​: Посмотрите приложенный файл​
​и выберите пункт​
​ такая. =ЕСЛИ(ОСТАТ(СТРОКА();3);"";ИНДЕКС(A:A;СТРОКА()/3))​
​ посему отсылаю Вас​
​'Берём номер столбца,​
​ первом столбце в​ Long​
​ вышестоящей ячейке разные.​ щелкаем по предыдущей​
​Irregular Expression​ проверяемых строк If​
​Jack Famous​Sub InsertRows()​
​ Long, s As​ на 50%-80%.​
​ Количество новых колонок​ сохранила .​ (слева)​Вставить столбцы на лист​
​Получится такая таблица.​ к пункту 5q​
​ где находится активная​ текущей строке и​Dim i As​
​If tblA(i, 1)​
​ ячейке (т.е. создаем​
​: anddrei55, ест-но, т.к.​
​ Not IsEmpty(Cells(i, 1))​
​: anddrei55, для начала​

​Dim i, i1​​ String​

​Sortafreel​​ появляется столько, сколько​
​AlexM​Guest​.​Третий вариант.​ Правил форума, а​ ячейка,​ строке,​ Long​ <> tblA(i -​ ссылку на предыдущую​

​ каждая строка удаляется/вставляется​​ Then Cells(i +​ файл-пример прикрепите с​
​ As Long, nRow​For i =​: День добрый.​ было их предварительно​
​: Еще можно так​: Спасибо за помощь!!!!!​Чтобы удалить столбец, выделите​В​ тему закрываю.​’в переменную.​
​’которая выше, не​
​Application.ScreenUpdating = False​ 1, 1) Then​ ячейку, другими словами):​ отдельно с двумя-тремя​ 1, 1).EntireRow.Insert i​ листами «до» и​ As Long, kv,​

excelworld.ru

​ 1 To 9​

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

1 Простое протягивание формулы

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

• В первую ячейку с одной из сторон (например, сверху) надо записать нужную формулу и нажать Enter.
• После появления рассчитанного по формуле значения навести курсор в нижний правый угол ячейки. Подождать, пока толстый белый крестик не превратиться в тонкий черный.
• Нажать на крестик и, удерживая его, протянуть формулу в нужном направлении. В указанном примере — вниз.

Аргументы в формуле будут изменяться соответственно новому расположению. И если в самой первой ячейке это были F7 и G7, в последней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строкам, изменяться будут не цифры, а буквы в обозначениях ячеек.

Способ отличается простотой и высокой скоростью. Но не всегда подходит для больших таблиц. Так, если в столбце несколько сотен или даже тысяч значений, формулу проще растягивать другими способами, чтобы сэкономить время. Один из них — автоматическое копирование, требующее всего лишь двойного клика кнопкой мыши.

2 Быстрое автозаполнение

Еще один способ в Excel протянуть формулу до конца столбца с более высокой по сравнению с первой методикой скоростью. Требует от пользователя применить такие действия:

• Ввести в верхнюю ячейку формулу, в которой применяются аргументы из соседних столбцов. Нажать кнопку Enter.
• Навести курсор на правый нижний угол, чтобы он приобрел форму черного крестика.
• Кликнуть два раза по нижнему правому углу ячейки. Результатом станет автоматическое распространение формулы по столбцу с соответствующим изменением аргументов.

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

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

3 Протягивание без изменения ячеек в формуле

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

Для распределения без изменения адреса ячейки выполняются те же действия, что и при обычном протягивании или автоматическом копировании. Но при вводе формулы следует зафиксировать адреса, которые не будут меняться. Для этого используются символы доллара — $. Если в каждом новом пункте столбца при расчетах используется одна и та же ячейка, значки надо будет поставить и перед номером строки, и перед литерой, которая указывает на колонку. Как в примере: $G$6.

Как заполнить в эксель пустые строки значениями выше

Как заполнить в эксель пустые строки значениями выше

Зачастую мы имеем таблицу в Excel, в которой присутствуют пустые ячейки. Заполнение таких ячеек вручную займет огромное количество времени.

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

Способ 1: Заполнение пустых ячеек в Excel с помощью заданного значения

Вы можете заполнить пустые ячейки столбца на листе Excel с помощью указанного значения. Для это можно воспользоваться встроенными возможностями Excel, которые очень легко выполнить. Первая функция Go To специальной особенностью Excel, а затем заполнить все пустые ячейки с заданным значением. И этот метод настолько прост , что вы можете использовать без каких — либо специальных знаний.

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

Шаг 1: Откройте нужный лист Excel, на котором необходимо заполнить пустые ячейки.

Шаг 2: Выберите конкретный столбец, содержащий пустые ячейки.

Шаг 3: Теперь найдите кнопку Найти и выделить в меню Главная и выберите Выделить группу ячеек. Появится окно с различными вариантами. Выберите Пустые ячейки.

Шаг 4: После данного выбора все пустые ячейки в указанном диапазоне будут выделены. Теперь, не нажимая ничего, введите необходимо значение, которое нужно поместить во все пустые ячейки и нажмите Ctrl + Enter. Пустые ячейки заполнятся нужным содержимым.

Таким образом вы можете легко заполнить пустые клетки определенного столбца заданным значением. Это довольно легко сделать, поскольку не требуется какого-либо приложения или программного обеспечения.

Способ 2: Заполнение пустых ячеек в Excel значением, расположенным в ячейке выше

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

Шаг 1: Откройте нужный лист Excel, который содержит пустые ячейки.

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

Шаг 3: Теперь найдите кнопку Найти и выделить в меню Главная и выберите Выделить группу ячеек . Появится окно с различными вариантами. Выберите Пустые ячейки .

Шаг 4: В строке формул наберите знак «=» и укажите ячейку, содержимым которой необходимо заполнить пустые ячейки. Далее нажмите Ctrl + Enter. Вы увидите, что все пустые ячейки заполнились значением ячейки выше. Смотрите скриншоты ниже.

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

Способ 3: Заполнение пустых ячеек Excel со значением выше с помощью макроса VBA

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

Шаг 1: Откройте рабочий лист , в котором вы хотите , чтобы заполнить все пустые ячейки со значением над ними.

Шаг 2: Теперь откройте редактор VBA, нажав Alt + F11.

Шаг 3: Щелкните правой кнопкой мыши на любом проекте и перейдите к Insert > Module. После этого вставьте следующий код VBA.

Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange

Dim rngBlank As Range: Set rngBlank = rngUR.Find(«»)

While Not rngBlank Is Nothing

rngBlank.Value = rngBlank.Offset(-1, 0).Value

Set rngBlank = rngUR.Find(«», rngBlank)

Шаг 4: Теперь сохраните модуль с помощью клавиши Ctrl + S, появившееся диалоговое окно с предупреждением просто проигнорируйте, нажав на кнопку Нет. Теперь вы можете задать сочетание клавиш для выполнения макроса. (меню Разработчик Макросы выберите нужный макрос Параметры Сочетание клавиш (укажите букву).

Шаг 5: На этом этапе макрокоманда готова к использованию. Чтобы использовать ее, нажмите клавишу быстрого доступа, назначенную для нее, и вы увидите результат в мгновение ока.

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

Заполнить пустые ячейки в столбце значениями сверху

Как быстро заполнить пустые ячейки значениями из ячеек над ними:

Довольно часто мы встречаем таблицы подобного вида, у меня в первом столбце имеются марки и их необходимо продублировать на все модели для удобства в дальнейшем анализировать информацию:

Выделяем необходимый для заполнения столбец → на вкладке Главная нажимаем Найти и Выделить → Выделить группу ячеек → В появившемся окне выбираем пустые ячейка → нажимаем «ок»

Таким образом у нас будут выделенные только пустые ячейки. Далее не снимая выделения пишем формулу ссылающююся на верхнее значение = (знак равно) → клацаем по верхней ячейки с значением (или стрелка вверх на клавиатуре) → нажимаем сочетание клавиш Ctrl+Enter

Далее убираем формулы так как они нам больше не понадобятся, для этого выделяем наш столбец, нажимаем «копировать» → щелкаем правой кнопкой мыши выбираем Специальная вставка → Специальная вставка → и в появившемся окне выбираем «значения» → ок

Заполнение пустых ячеек значениями из соседних ячеек

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек — по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками — например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.

В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).

Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :

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

И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.

В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.

Способ 2. Заполнение пустых ячеек макросом

Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert — Module и копируем или вводим туда вот такой короткий код:

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

Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.

Способ 3. Power Query

Power Query — это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно — в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:

• Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
• При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов — всё делать заново.

Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы — Диспетчер имен), либо превратить в «умную» таблицу командой Главная — Форматировать как таблицу (Home — Format as Table ) или сочетанием клавиш Ctrl + T :

После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.

В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить — Заполнить вниз (Transform — Fill — Fill Down) :

Вот и всё 🙂 Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная — Закрыть и загрузить — Закрыть и загрузить в. (Home — Close&Load — Close&Load to. )

В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные — Обновить всё (Data — Refresh All) .

Как в Excel заполнить пустые ячейки нулями или значениями из ячеек выше (ниже)

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

Заполнять или не заполнять? – этот вопрос часто возникает в отношении пустых ячеек в таблицах Excel. С одной стороны, таблица выглядит аккуратнее и более читабельной, когда Вы не загромождаете её повторяющимися значениями. С другой стороны, пустые ячейки в Excel могут привести к проблемам во время сортировки, фильтрации данных или при создании сводной таблицы. В таком случае Вам придётся заполнить все пустые ячейки. Существуют разные способы для решения этой проблемы. Я покажу Вам несколько быстрых способов заполнить пустые ячейки различными значениями в Excel 2010 и 2013.

Итак, моим ответом будет – заполнять! Давайте посмотрим, как мы сможем это сделать.

Как выделить пустые ячейки на листе Excel

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

1. Выделите столбцы или строки, в которых требуется заполнить пустоты.
2. Нажмите Ctrl+G или F5, чтобы отобразить диалоговое окно Go To (Переход).
3. Нажмите кнопку Special (Выделить).

Замечание: Если Вы вдруг забыли сочетание клавиш, откройте вкладку Home (Главная) и в разделе Editing (Редактирование) из выпадающего меню Find & Select (Найти и выделить) выберите команду Go To Special (Выделить группу ячеек). На экране появится то же диалоговое окно.

1. Команда Go To Special (Выделить группу ячеек) позволяет выбрать ячейки определённого типа, например, ячейки, содержащие формулы, примечания, константы, пустые ячейки и так далее.
2. Выберите параметр Blanks (Пустые ячейки) и нажмите ОК.Теперь в выбранном диапазоне выделены только пустые ячейки и всё готово к следующему шагу.

Формула для заполнения пустых ячеек значениями из ячеек выше (ниже)

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

Если Вы собираетесь заполнить пропуски значениями из ближайшей не пустой ячейки сверху или снизу, то потребуется ввести в одну из пустых ячеек очень простую формулу. Затем просто скопируйте её во все пустые ячейки. Как это сделать – читайте далее.

1. Выделите все пустые ячейки.
2. Нажмите F2 или просто поместите курсор в строку формул, чтобы приступить к вводу формулы в активную ячейку. Как видно на снимке экрана выше, активна ячейка C4.
3. Введите знак равенства (=).
4. Укажите ячейку, находящуюся выше или ниже, нажав стрелку вверх или вниз, или просто кликнув по ней.Формула (=C3) показывает, что в ячейке C4 появится значение из ячейки C3.
5. Нажмите Ctrl+Enter, чтобы скопировать формулу во все выделенные ячейки.

Отлично! Теперь в каждой выделенной ячейке содержится ссылка на ячейку, расположенную над ней.

Замечание: Не забывайте, что все ячейки, которые были пустыми, теперь содержат формулы. Если Вы хотите сохранить порядок в таблице, то лучше заменить эти формулы значениями. В противном случае, можно получить путаницу при выполнении сортировки или при вводе новых данных.

Заполняем пустые ячейки нулями или другим заданным значением

Что если Вам нужно заполнить все пустые ячейки в Вашей таблице нулями или другими числовыми или текстовыми значениями? Далее показаны два способа решения этой задачи.

1. Выделите все пустые ячейки
2. Нажмите F2, чтобы ввести значение в активную ячейку.
3. Введите нужное число или текст.
4. Нажмите Ctrl+Enter.

За несколько секунд Вы заполнили все пустые ячейки нужным значением.

1. Выделите диапазон, содержащий пустые ячейки.
2. Нажмите Ctrl+H, чтобы появилось диалоговое окно Find & Replace (Найти и заменить).
3. Перейдите на вкладку Replace (Заменить).
4. Оставьте поле Find what (Найти) пустым и введите нужное значение в поле Replace with (Заменить на).
5. Нажмите кнопку Replace All (Заменить все).

Какой бы способ Вы ни выбрали, обработка всей таблицы Excel займёт не больше минуты!

Теперь Вы знаете приёмы, как заполнить пустые ячейки различными значениями в Excel 2013. Уверен, для Вас не составит труда сделать это как при помощи простой формулы, так и при помощи инструмента Find & Replace (Найти и заменить).

Как автоматически заполнить ячейки в MS Excel с большими таблицами

В программе Excel существует много приемов для быстрого и эффективного заполнения ячеек данными. Всем известно, что лень – это двигатель прогресса. Знают об этом и разработчики.

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

Рассмотрим приемы автоматического и полуавтоматического заполнения в Excel. А так же, какими инструментами обладают электронные таблицы для облегчения труда пользователя. Научимся применять их в практике и узнаем насколько они эффективные.

Как в Excel заполнить ячейки одинаковыми значениями?

Сначала рассмотрим, как автоматически заполнять ячейки в Excel. Для примера заполним наполовину незаполненную исходную таблицу.

Это небольшая табличка только на примере и ее можно было заполнить вручную. Но в практике иногда приходится заполнять по 30 тысяч строк. Чтобы не заполнять эту исходную таблицу вручную следует создать формулу для заполнения в Excel данными – автоматически. Для этого следует выполнить ряд последовательных действий:

1. Перейдите на любую пустую ячейку исходной таблицы.
2. Выберите инструмент: «Главная»-«Найти и выделить»-«Перейти» (или нажмите горячие клавиши CTRL+G).
3. В появившемся окне щелкните на кнопку «Выделить».
4. В появившемся окне выберите опцию «пустые ячейки» и нажмите ОК. Все незаполненные ячейки выделены.
5. Теперь введите формулу «=A1» и нажмите комбинацию клавиш CTRL+Enter. Так выполняется заполнение пустых ячеек в Excel предыдущим значением – автоматически.
6. Выделите колонки A:B и скопируйте их содержимое.
7. Выберите инструмент: «Главная»-«Вставить»-«Специальная вставка» (или нажмите CTRL+ALT+V).
8. В появившемся окне выберите опцию «значения» и нажмите Ок. Теперь исходная таблица заполнена не просто формулами, а естественными значениями ячеек.

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

Внимание! В 5-том пункте таблица красиво заполнилась без ошибок, так как наша активная ячейка была по адресу A2, после выполнения 4-го пункта. При использовании данного метода будьте внимательны и следите за тем где находится активная ячейка после выделения. Важно откуда она будет брать свои значения.

Полуавтоматическое заполнение ячеек в Excel из выпадающего списка

Теперь в полуавтоматическом режиме можно заполнить пустые ячейки. У только несколько значений, которые повторяются в последовательном или случайном порядке.

В новой исходной таблице автоматически заполните колонки C и D соответствующие им данными.

1. Заполните заголовки колонок C1 – «Дата» и D1 – «Тип платежа».
2. В ячейку C2 введите дату 18.07.2015
3. В ячейках С2:С4 даты повторяются. Поэтому выделяем диапазон С2:С4 и нажимаем комбинацию клавиш CTRL+D, чтобы автоматически заполнить ячейки предыдущими значениями.
4. Введите текущею дату в ячейку C5. Для этого нажмите комбинацию клавиш CTRL+; (точка с запятой на английской раскладке клавиатуры). Заполните текущими датами колонку C до конца таблицы.
5. Диапазон ячеек D2:D4 заполните так как показано ниже на рисунке.
6. В ячейке D5 введите первую буку «п», а дальше слово заполнят не надо. Достаточно нажать клавишу Enter.
7. В ячейке D6 после ввода первой буквы «н» не отображается часть слова для авто-заполнения. Поэтому нажмите комбинацию ALT+(стрела вниз), чтобы появился выпадающий список. Выберите стрелками клавиатуры или указателем мышки значение «наличными в кассе» и нажмите Enter.

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

Внимание! Если значение состоит из нескольких строк, то при нажатии на комбинацию ALT+(стрела вниз) оно не будет отображаться в выпадающем списке значений.

Разбить значение на строки можно с помощью комбинации клавиш ALT+Enter. Таким образом, текст делится на строки в рамках одной ячейки.

Примечание. Обратите внимание, как мы вводили текущую дату в пункте 4 с помощью горячих клавиш (CTRL+;). Это очень удобно! А при нажатии CTRL+SHIFT+; мы получаем текущее время.